That’s a bit of a tall order.
Here is a short version…
SELECT
tkt.[TicketNumber]
,tkt.[Date]
,tkt.[TotalAmount]
,tkt.[TransactionDocument_Id]
,tx.[Amount]
,tx.[Amount]/@dec_XR
,tx.[Name]
FROM [SambaPOS3].[dbo].[Tickets] tkt
left join [SambaPOS3].[dbo].[AccountTransactions] tx
on tkt.TransactionDocument_Id = tx.AccountTransactionDocumentId
WHERE tkt.[Date] >= @dat_WorkPeriod_Beg AND tkt.[Date] <= @dat_WorkPeriod_End
What follows is the long version of what works for me and my accountant, though this is specific to my setup, including Tax, Exchange, etc. Plus, I’ve created tables in the DB where I store this information, and modifications need to be made to the SQLexpress installation in order for the tables to be written out to text and subsequently printed (to enable EXEC master..xp_cmdshell). You’ll need to know SQL fairly well, and make various modifications for your needs.
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** WORK PERIOD SUMMARY ***/
/*** ***/
/*** see USER SECTION (after declarations) to set Date for Report ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
/*****************************************/
/************* DO NOT TOUCH **************/
/***** Declarations & Initialization *****/
/*****************************************/
declare @int_WorkPeriod_ID int = 0
declare @var_WorkPeriod_ID varchar(7) = '0'
declare @dat_WorkPeriod_Beg Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_Beg varchar(19) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_End varchar(19) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End_Check Datetime = '9999-12-31 23:59:59'
declare @int_Ticket_Beg int = 0
declare @var_Ticket_Beg varchar(6) = '0'
declare @int_Ticket_End int = 0
declare @var_Ticket_End varchar(6) = '0'
declare @int_N int = 0
declare @dec_XR money = 1
declare @T1 money = 1
declare @T2 money = 1
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** USER SECTION (Begin) ***/
/*** set WorkPeriod Begin & End Dates ***/
/*** select Foreign Currency Exchange Rate ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
-- TODAY
set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 120)
--set @dat_WorkPeriod_Beg = '2013-11-20'
set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
-- OTHER DAY
--set @dat_WorkPeriod_Beg = '2014-01-24'
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
--set @dat_WorkPeriod_End = '2014-01-25 00:17:40.347'
-- ForeignCurrency ExchangeRate
SELECT @dec_XR = [ExchangeRate] FROM [SambaPOS3].[dbo].[ForeignCurrencies] WHERE [Name] = 'USD'
-- Taxes
SELECT @T1 = [Rate] FROM [SambaPOS3].[dbo].[TaxTemplates] WHERE [Name] = 'T1'
SELECT @T2 = [Rate] FROM [SambaPOS3].[dbo].[TaxTemplates] WHERE [Name] = 'T2'
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** USER SECTION (End) ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
/*****************************************/
/************* DO NOT TOUCH **************/
/** Declarations for (in-memory) Tables **/
/*****************************************/
declare @tbl_Transactions table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[TicketNumber] int
,[Date] Datetime
,[TotalAmount] money
,[TransactionDocument_Id] int
,[Amount] money
,[Amount_USD] money
,[Name] varchar(255)
)
declare @tbl_Tx table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Date] Datetime
,[Ticket] int
,[T1] money
,[T2] money
,[Tax] money
,[Amount] money
,[TotalAmount] money
)
declare @tbl_Tx_Z table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Date] Datetime
,[Ticket] int
,[T1] money
,[T2] money
,[Tax] money
,[Amount] money
,[TotalAmount] money
)
declare @tbl_Report table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Z_num] int
,[WP_Beg] Datetime
,[WP_End] Datetime
,[Ticket_Beg] int
,[Ticket_End] int
,[Total] money
,[Total_USD] money
,[Total_Type] varchar(255)
)
declare @tbl_Sales table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Z_num] int
,[WP_Beg] Datetime
,[WP_End] Datetime
,[Ticket_Beg] int
,[Ticket_End] int
,[Total] money
,[Total_USD] money
,[Total_Type] varchar(255)
)
declare @tbl_ZED table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[WPID] int
,[F1] varchar(255)
,[F2] varchar(255)
)
declare @tbl_Cashout table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Z_num] int
,[WP_Beg] Datetime
,[WP_End] Datetime
,[Ticket_Beg] int
,[Ticket_End] int
,[Total] money
,[Total_USD] money
,[Total_Type] varchar(255)
)
declare @tbl_Incomes table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Z_num] int
,[WP_Beg] Datetime
,[WP_End] Datetime
,[Ticket_Beg] int
,[Ticket_End] int
,[Total] money
,[Total_USD] money
,[Total_Type] varchar(255)
)
declare @tbl_Payout_Tx table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[TxDocID] int
,[Acc_ID_Source] int
,[Acc_ID_Target] int
,[Tx_Date] Datetime
,[Tx_Val] money
,[Tx_Val_USD] money
,[Tx_Name] varchar(255)
,[Tx_Acc_Name_Source] varchar(255)
,[Tx_Acc_Name_Target] varchar(255)
)
declare @tbl_Payout table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Z_num] int
,[WP_Beg] Datetime
,[WP_End] Datetime
,[Ticket_Beg] int
,[Ticket_End] int
,[Total] money
,[Total_USD] money
,[Total_Type] varchar(255)
)
/*****************************************/
/************* CREATE REPORT *************/
/*****************************************/
-- date constrained
select @int_WorkPeriod_ID = [Id] FROM [SambaPOS3].[dbo].[WorkPeriods] where [StartDate] >= @dat_WorkPeriod_Beg and [EndDate] <= @dat_WorkPeriod_End
-- latest ZED
select @int_WorkPeriod_ID = max([Id]) FROM [SambaPOS3].[dbo].[WorkPeriods] WHERE [StartDate] != [EndDate]
-- get dates
select @dat_WorkPeriod_Beg = [StartDate] FROM [SambaPOS3].[dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID
select @dat_WorkPeriod_End_Check = [EndDate] FROM [SambaPOS3].[dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID
-- check end date: if the workperiod is still open, end date will be the same as beg date, so change end to be 9999-12-31
select @dat_WorkPeriod_End =
case @dat_WorkPeriod_End_Check
when @dat_WorkPeriod_Beg then @dat_WorkPeriod_End
else @dat_WorkPeriod_End_Check
end
set @var_WorkPeriod_ID = right('0000000'+convert(varchar(7),@int_WorkPeriod_ID),7)
set @var_WorkPeriod_Beg = convert(varchar(19),@dat_WorkPeriod_Beg, 120)
set @var_WorkPeriod_End = convert(varchar(19),@dat_WorkPeriod_End, 120)
print @int_WorkPeriod_ID
print @dat_WorkPeriod_Beg
print @dat_WorkPeriod_End
print @dat_WorkPeriod_End_Check
/*****************************************/
/************* Transactions **************/
/*****************************************/
INSERT INTO @tbl_Transactions
SELECT
tkt.[TicketNumber]
,tkt.[Date]
,tkt.[TotalAmount]
,tkt.[TransactionDocument_Id]
,tx.[Amount]
,tx.[Amount]/@dec_XR
,tx.[Name]
FROM [SambaPOS3].[dbo].[Tickets] tkt
left join [SambaPOS3].[dbo].[AccountTransactions] tx
on tkt.TransactionDocument_Id = tx.AccountTransactionDocumentId
WHERE tkt.[Date] >= @dat_WorkPeriod_Beg AND tkt.[Date] <= @dat_WorkPeriod_End
SELECT
@int_Ticket_Beg = min([TicketNumber])
,@int_Ticket_End = max([TicketNumber])
FROM @tbl_Transactions
set @var_Ticket_Beg = right('000000'+convert(varchar(6),@int_Ticket_Beg),6)
set @var_Ticket_End = right('000000'+convert(varchar(6),@int_Ticket_End),6)
print @int_Ticket_Beg
print @int_Ticket_End
/*****************************************/
/******************* Tx ******************/
/*****************************************/
INSERT INTO @tbl_Tx
SELECT DISTINCT
[Date] --[Date] Datetime
,[TicketNumber] --[Ticket] int
,0 --[T1] money
,0 --[T2] money
,0 --[Tax] money
,0 --[Amount] money
,0 --[TotalAmount] money
FROM @tbl_Transactions
WHERE coalesce([TotalAmount],0) > 0
ORDER BY [TicketNumber]
set @int_N = @int_Ticket_Beg
WHILE @int_N <= @int_Ticket_End
BEGIN
UPDATE @tbl_Tx SET
[TotalAmount] = (SELECT CASE WHEN EXISTS (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
THEN (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
ELSE (SELECT 0)
END)
, [Amount] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
ELSE (SELECT 0)
END)
, [T1] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
ELSE (SELECT 0)
END)
, [T2] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
ELSE (SELECT 0)
END)
, [Tax] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
ELSE (SELECT 0)
END)
+ (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
ELSE (SELECT 0)
END)
WHERE [Ticket] = @int_N
set @int_N = @int_N + 1
END
--select * from @tbl_Transactions
--select * from @tbl_Tx
/*****************************************/
/*************** PAYOUT Tx ***************/
/*****************************************/
INSERT INTO @tbl_Payout_Tx
SELECT
txval.AccountTransactionDocumentId
, txval.[AccountID] as Tx_Acc_ID_Source
, 0 as Tx_Acc_ID_Target
, txval.[Date] as Tx_Date
, txval.[Credit] as Tx_Value
, txval.[Credit]/@dec_XR as Tx_Value_USD
, 'Payout ' + txval.[Name] as Tx_Name
, 'Payout ' +
CASE ac.[Name]
WHEN 'Cash' then 'Drawer' + ' (' + ac.[Name] + ')'
WHEN 'Cash USD' then 'Drawer USD' + ' (' + ac.[Name] + ')'
ELSE ac.[Name] + ' (' + ac.[Name] + ')'
END as Tx_Acc_Name_Source
, '' as Tx_Acc_Name_Target
FROM [SambaPOS3].[dbo].[AccountTransactionValues] txval
left join [SambaPOS3].[dbo].[Accounts] ac
on txval.[AccountId] = ac.[Id]
where txval.[Credit] > 0
and txval.[Date] >= @dat_WorkPeriod_Beg and txval.[Date] <= @dat_WorkPeriod_End
and (txval.[Name] like '%Expense%' or txval.[Name] like '%Supplier%')
-- and NOT
--(
-- txval.[Name] like '%Sale%'
--or txval.[Name] like '%T1%'
--or txval.[Name] like '%T2%'
--or txval.[Name] like '%Payment%'
--or txval.[Name] like '%Change%'
--or txval.[Name] like '%Customer%'
--)
UPDATE @tbl_Payout_Tx SET
[Acc_ID_Target] = (
SELECT
txval.[AccountID] as Tx_Acc_ID_Source
FROM [SambaPOS3].[dbo].[AccountTransactionValues] txval
left join [SambaPOS3].[dbo].[Accounts] ac
on txval.[AccountId] = ac.[Id]
where txval.[Debit] > 0
and txval.AccountTransactionDocumentId = [TxDocID]
and (txval.[Name] like '%Expense%' or txval.[Name] like '%Supplier%')
--and NOT
--(
-- txval.[Name] like '%Sale%'
--or txval.[Name] like '%T1%'
--or txval.[Name] like '%T2%'
--or txval.[Name] like '%Payment%'
--or txval.[Name] like '%Change%'
--or txval.[Name] like '%Customer%'
--)
)
UPDATE @tbl_Payout_Tx SET
[Tx_Acc_Name_Target] = (
SELECT 'Payout ' + [Name]
FROM [SambaPOS3].[dbo].[Accounts] ac
WHERE ac.Id = [Acc_ID_Target]
)
UPDATE @tbl_Payout_Tx SET
[Tx_Val] = [Tx_Val] * @dec_XR
,[Tx_Val_USD] = [Tx_Val_USD] * @dec_XR
WHERE [Tx_Name] like '%USD%' and [Tx_Name] not like '%Wallet%USD%' and [Tx_Name] not like '%Drawer%USD%'
--UPDATE @tbl_Payout_Tx SET
-- [Tx_Val] = [Tx_Val] * @dec_XR
-- ,[Tx_Val_USD] = [Tx_Val_USD] * @dec_XR
-- WHERE ([Tx_Name] like '%Wallet%USD%' or [Tx_Name] like '%Drawer%USD%')
-- and [Tx_Acc_Name_Target] not like '%Provision%'
-- and [Tx_Acc_Name_Target] not like '%Accountant%'
--UPDATE @tbl_Payout_Tx SET
-- [Tx_Val] = [Tx_Val] * @dec_XR
-- ,[Tx_Val_USD] = [Tx_Val_USD] * @dec_XR
-- WHERE ([Tx_Name] like '%Wallet%USD%' or [Tx_Name] like '%Drawer%USD%')
-- and [Tx_Acc_Name_Target] like '%Provision%'
-- --and [Tx_Acc_Name_Target] like '%Accountant%'
--SELECT * FROM @tbl_Payout_Tx
/*****************************************/
/****************** SALES ****************/
/*****************************************/
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, sum([Amount])/@dec_XR
, 'Tax Base'
FROM @tbl_Transactions
WHERE [Name] like '%T1%'
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, sum([Amount])/@dec_XR
, 'Tax Alcohol'
FROM @tbl_Transactions
WHERE [Name] like '%T2%'
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, sum([Amount])/@dec_XR
, 'Tax Total'
FROM @tbl_Transactions
WHERE [Name] like '%T1%' or [Name] like '%T2%'
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, sum([Amount])/@dec_XR
, 'Sales Total Tax Excluded'
FROM @tbl_Transactions
WHERE [Name] like '%Sale%'
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, sum([Amount])/@dec_XR
, 'SALES TOTAL'
FROM @tbl_Transactions
WHERE [Name] like '%Sale%' or [Name] like '%T1%' or [Name] like '%T2%'
/*****************************************/
/****************** PAYOUT ***************/
/*****************************************/
INSERT INTO @tbl_Payout
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum(Tx_Val)*-1
, (sum([Tx_Val_USD])*-1)--/@dec_XR
, [Tx_Acc_Name_Source]
FROM @tbl_Payout_Tx
GROUP BY [Tx_Acc_Name_Source]
INSERT INTO @tbl_Payout
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Total])--sum(Tx_Val)*-1
, sum([Total_USD])--(sum(Tx_Val)*-1)/@dec_XR
,'PAYOUT TOTAL'
FROM @tbl_Payout
/*****************************************/
/***************** INCOMES ***************/
/*****************************************/
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum(
CASE [Name]
WHEN 'Payment Transaction [Cash]' THEN [Amount]
WHEN 'Change Transaction [Cash]' THEN -1*[Amount]
ELSE 0 END
)
, sum(
CASE [Name]
WHEN 'Payment Transaction [Cash]' THEN [Amount]
WHEN 'Change Transaction [Cash]' THEN -1*[Amount]
ELSE 0 END
)/@dec_XR
,'Cash'
FROM @tbl_Transactions
WHERE [Name] = 'Payment Transaction [Cash]' or [Name] = 'Change Transaction [Cash]'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum(
CASE [Name]
WHEN 'Payment Transaction [Cash USD]' THEN [Amount]
WHEN 'Change Transaction [Cash USD]' THEN -1*[Amount]
ELSE 0 END
)
, sum(
CASE [Name]
WHEN 'Payment Transaction [Cash USD]' THEN [Amount]
WHEN 'Change Transaction [Cash USD]' THEN -1*[Amount]
ELSE 0 END
)/@dec_XR
,'Cash USD'
FROM @tbl_Transactions
WHERE [Name] = 'Payment Transaction [Cash USD]' or [Name] = 'Change Transaction [Cash USD]'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, sum([Amount])/@dec_XR
,'Credit Card'
FROM @tbl_Transactions
WHERE [Name] = 'Payment Transaction [Credit Card]'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, isnull(sum([Amount]),0)
, isnull(sum([Amount]),0)/@dec_XR
,'Customer Account'
FROM @tbl_Transactions
WHERE [Name] like '%Customer Account Transaction%'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, isnull(sum([Debit]),0)
, isnull(sum([Debit]),0)/@dec_XR
,'Customer Account Cash Payments'
FROM [SambaPOS3].[dbo].[AccountTransactionValues] txval
left join [SambaPOS3].[dbo].[AccountTransactions] tx
on [AccountTransactionId] = txval.[Id]
where [Debit] > 0
and [Date] >= @dat_WorkPeriod_Beg
and [Date] <= @dat_WorkPeriod_End
and txval.[Name] like 'Cash Payment%'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, isnull(sum([Debit]),0)
, isnull(sum([Debit]),0)/@dec_XR
,'Customer Account CC Payments'
FROM [SambaPOS3].[dbo].[AccountTransactionValues] txval
left join [SambaPOS3].[dbo].[AccountTransactions] tx
on [AccountTransactionId] = txval.[Id]
where [Debit] > 0
and [Date] >= @dat_WorkPeriod_Beg
and [Date] <= @dat_WorkPeriod_End
and txval.[Name] like 'Credit Card Payment%'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Total])
, sum([Total_USD])
,'INCOME TOTAL'
FROM @tbl_Incomes
/*****************************************/
/***************** CASHOUT ***************/
/*****************************************/
INSERT INTO @tbl_Cashout
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, (SELECT [Total] from @tbl_Incomes WHERE [Total_Type] = 'Cash')
+ (select case when exists (SELECT [Total] from @tbl_Incomes WHERE [Total_Type] = 'Customer Account Cash Payments')
then (SELECT [Total] from @tbl_Incomes WHERE [Total_Type] = 'Customer Account Cash Payments')
else (select 0)
end)
+ (select case when exists (SELECT [Total] from @tbl_Payout WHERE [Total_Type] = 'Payout Drawer (Cash)')
then (SELECT [Total] from @tbl_Payout WHERE [Total_Type] = 'Payout Drawer (Cash)')
else (select 0)
end)
, (SELECT [Total_USD] from @tbl_Incomes WHERE [Total_Type] = 'Cash')
+ (select case when exists (SELECT [Total_USD] from @tbl_Incomes WHERE [Total_Type] = 'Customer Account Cash Payments')
then (SELECT [Total_USD] from @tbl_Incomes WHERE [Total_Type] = 'Customer Account Cash Payments')
else (select 0)
end)
+ (select case when exists (SELECT [Total_USD] from @tbl_Payout WHERE [Total_Type] = 'Payout Drawer (Cash)')
then (SELECT [Total_USD] from @tbl_Payout WHERE [Total_Type] = 'Payout Drawer (Cash)')
else (select 0)
end)
,'Cashout Lempira'
)
INSERT INTO @tbl_Cashout
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, (SELECT [Total] from @tbl_Incomes WHERE [Total_Type] = 'Cash USD')
+ (select case when exists (SELECT 1 from @tbl_Payout WHERE [Total_Type] = 'Payout Drawer USD (Cash USD)')
then (SELECT [Total] from @tbl_Payout WHERE [Total_Type] = 'Payout Drawer USD (Cash USD)')
else (select 0)
end)
, (SELECT [Total_USD] from @tbl_Incomes WHERE [Total_Type] = 'Cash USD')
+ (select case when exists (SELECT 1 from @tbl_Payout WHERE [Total_Type] = 'Payout Drawer USD (Cash USD)')
then (SELECT [Total_USD] from @tbl_Payout WHERE [Total_Type] = 'Payout Drawer USD (Cash USD)')
else (select 0)
end)
,'Cashout USD'
)
INSERT INTO @tbl_Cashout
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, (SELECT [Total] from @tbl_Incomes WHERE [Total_Type] = 'Credit Card')
+ (select case when exists (SELECT [Total] from @tbl_Incomes WHERE [Total_Type] = 'Customer Account CC Payments')
then (SELECT [Total] from @tbl_Incomes WHERE [Total_Type] = 'Customer Account CC Payments')
else (select 0)
end)
, (SELECT [Total_USD] from @tbl_Incomes WHERE [Total_Type] = 'Credit Card')
+ (select case when exists (SELECT [Total_USD] from @tbl_Incomes WHERE [Total_Type] = 'Customer Account CC Payments')
then (SELECT [Total_USD] from @tbl_Incomes WHERE [Total_Type] = 'Customer Account CC Payments')
else (select 0)
end)
,'Cashout CC'
)
INSERT INTO @tbl_Cashout
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Total])
, sum([Total_USD])
,'CASHOUT TOTAL'
FROM @tbl_Cashout
/*****************************************/
/****************** REPORT ***************/
/*****************************************/
INSERT INTO @tbl_Report
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, null
, null
,'*---------- SALES'
)
INSERT INTO @tbl_Report
select
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
FROM @tbl_Sales ORDER BY [ID]
INSERT INTO @tbl_Report
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, null
, null
,'*-------- INCOMES'
)
INSERT INTO @tbl_Report
select
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
FROM @tbl_Incomes ORDER BY [ID]
INSERT INTO @tbl_Report
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, null
, null
,'*-------- PAYOUTS'
)
INSERT INTO @tbl_Report
select
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
FROM @tbl_Payout ORDER BY [ID]
INSERT INTO @tbl_Report
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, null
, null
,'*-------- CASHOUT'
)
INSERT INTO @tbl_Report
select
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
FROM @tbl_Cashout ORDER BY [ID]
INSERT INTO @tbl_Report
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, null
, null
,'*------------ END'
)
DELETE FROM [SambaPOS3].[dbo].[REPORT] WHERE [Z_num] = 0
DELETE FROM [SambaPOS3].[dbo].[REPORT] WHERE [Z_num] = @int_WorkPeriod_ID
INSERT INTO [SambaPOS3].[dbo].[REPORT]
SELECT
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
FROM @tbl_Report ORDER BY [ID]
SELECT
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
,[Total_USD]
,[Total_Type]
FROM @tbl_Report ORDER BY [ID]
--SELECT [F1],[F2] FROM @tbl_ZED
DELETE FROM [SambaPOS3].[dbo].[Payout_Tx] WHERE WPID = 0
DELETE FROM [SambaPOS3].[dbo].[Payout_Tx] WHERE WPID = @int_WorkPeriod_ID
INSERT INTO [SambaPOS3].[dbo].[Payout_Tx]
SELECT
@int_WorkPeriod_ID as WPID
,[TxDocID]
,[Acc_ID_Source]
,[Acc_ID_Target]
,[Tx_Date]
,[Tx_Val]
,[Tx_Val_USD]
,[Tx_Name]
,[Tx_Acc_Name_Source]
,[Tx_Acc_Name_Target]
FROM @tbl_Payout_Tx
SELECT
[WPID]
-- ,[TxDocID]
-- ,[Acc_ID_Source]
-- ,[Acc_ID_Target]
, [Tx_Date] as [Date]
,[Tx_Val] as [Lempira]
,[Tx_Val_USD] as [USD]
,[Tx_Name] as [Description]
,[Tx_Acc_Name_Source] as [Source_Account]
,[Tx_Acc_Name_Target] as [Target_Account]
FROM [SambaPOS3].[dbo].[Payout_Tx]
WHERE [WPID] = @int_WorkPeriod_ID
UNION
SELECT WPID, @dat_WorkPeriod_Beg, SUM([Tx_Val]), SUM([Tx_Val_USD]) , 'PAYOUT TOTAL------------------','SOURCE--------------','TARGET--------------'
FROM [SambaPOS3].[dbo].[Payout_Tx] WHERE [WPID] = @int_WorkPeriod_ID GROUP BY WPID
DECLARE @outputfile varchar(200), @PATH varchar(100), @FileName varchar(100), @qry varchar(500), @bcpCommand varchar(2000)
SET @qry = 'SELECT [Z_num],[WP_Beg],[WP_End],[Ticket_Beg],[Ticket_End],[Total],[Total_USD],[Total_Type] FROM [SambaPOS3].[dbo].[REPORT] WHERE Z_Num='+@var_WorkPeriod_ID+' ORDER BY [ID]'
SET @PATH = 'D:\Programs\POS\SALES\Report\'
--SET @PATH = 'ZED\'
SET @FileName = 'R'+@var_WorkPeriod_ID+'_'+@var_WorkPeriod_Beg+'.txt'
SET @FileName = REPLACE(@FileName,'/','-')
SET @FileName = REPLACE(@FileName,' ','_')
SET @FileName = REPLACE(@FileName,':','.')
SET @outputfile = @PATH + @FileName
SET @bcpCommand = 'bcp '
SET @bcpCommand = @bcpCommand + '"' + @qry + '"'
SET @bcpCommand = @bcpCommand + ' queryout "' + @outputfile + '"'
SET @bcpCommand = @bcpCommand + ' -S localhost\SQLEXPRESS'
SET @bcpCommand = @bcpCommand + ' -T'
SET @bcpCommand = @bcpCommand + ' -c -t ","'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand