Using Custom Reports and the above SQL (slightly modified) you can accomplish this:
Output:
Template:
[Z Report:50, 50]
@@Zreport
SQL script:
This is slightly modified for Date selection in the Reports module so that it uses parameters for {Start} and {End}.
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** ZED REPORT ***/
/*** ***/
/*** see USER SECTION (after declarations) to set: ***/
/*** Date for Report ***/
/*** Tax Rates via Template Names ***/
/*** Foreign Currency Exchange (optional) ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
/*****************************************/
/************* DO NOT TOUCH **************/
/***** Declarations & Initialization *****/
/*****************************************/
--USE [SambaPOS4]
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(7) = '0'
declare @int_Ticket_End int = 0
declare @var_Ticket_End varchar(7) = '0'
declare @int_N int = 0
declare @dec_XR money = 1
declare @T1 money = 1
declare @T2 money = 1
declare @T3 money = 1
declare @T1_TTL_c varchar(19) = ''
declare @T2_TTL_c varchar(19) = ''
declare @T3_TTL_c varchar(19) = ''
declare @Tax_TTL_c varchar(19) = ''
declare @SalesTaxExcl_TTL_c varchar(19) = ''
declare @SalesNoTax_TTL_c varchar(19) = ''
declare @Sales_TTL_c varchar(19) = ''
declare @T1_TTL money = 0
declare @T2_TTL money = 0
declare @T3_TTL money = 0
declare @Tax_TTL money = 0
declare @SalesTaxExcl_TTL money = 0
declare @SalesNoTax_TTL money = 0
declare @Sales_TTL money = 0
declare @T1_taxout money = 0
declare @T2_taxout money = 0
declare @T3_taxout money = 0
declare @T1_taxout_c varchar(10) = ''
declare @T2_taxout_c varchar(10) = ''
declare @T3_taxout_c varchar(10) = ''
declare @int_PrintZED int = 0
declare @BASEPATH varchar(100) = ''
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** USER SECTION (Begin) ***/
/*** set WorkPeriod Begin & End Dates ***/
/*** select Tax Rates via Template Names ***/
/*** select Foreign Currency Exchange Rate ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
--set @BASEPATH = 'C:\D\Programs\POS\SALES\'
set @int_PrintZED = 0
-- TODAY
--set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 120)
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
-- OTHER DAY
set @dat_WorkPeriod_Beg = '{Start}'
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
set @dat_WorkPeriod_End = '{End}'
set @dat_WorkPeriod_End = dateadd(Second,1,@dat_WorkPeriod_End)
-- DATE CONSTRAINED
select @int_WorkPeriod_ID = [Id] FROM [dbo].[WorkPeriods] where [StartDate] >= @dat_WorkPeriod_Beg and [EndDate] <= @dat_WorkPeriod_End
-- LATEST CLOSED WorkPeriod
--select @int_WorkPeriod_ID = max([Id]) FROM [dbo].[WorkPeriods] WHERE [StartDate] != [EndDate]
--select @dat_WorkPeriod_Beg = [StartDate] FROM [dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID
--select @dat_WorkPeriod_End_Check = [EndDate] FROM [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
-- ForeignCurrency ExchangeRate
--SELECT @dec_XR = [ExchangeRate] FROM [dbo].[ForeignCurrencies] WHERE [Name] = 'USD'
-- Taxes
SELECT @T1 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T1'
SELECT @T2 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T2'
SELECT @T3 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T3'
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** 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_Sales_Tx table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Date] Datetime
,[Ticket] int
,[T1] money
,[T2] money
,[T3] money
,[Tax] money
,[Amount] money
,[Tip] money
,[GCsale] money
,[TotalAmount] money
)
declare @tbl_Tx_Z table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Date] Datetime
,[Ticket] int
,[T1] money
,[T2] money
,[T3] money
,[Tax] money
,[Amount] money
,[TotalAmount] money
)
declare @tbl_ZED table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[WPID] int
,[F1] varchar(255)
,[F2] varchar(255)
)
/******************************************************************************************/
/************* CREATE REPORT **************************************************************/
/******************************************************************************************/
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 [dbo].[Tickets] tkt
left join [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('0000000'+convert(varchar(7),@int_Ticket_Beg),7)
set @var_Ticket_End = right('0000000'+convert(varchar(7),@int_Ticket_End),7)
print @int_Ticket_Beg
print @int_Ticket_End
/*****************************************/
/******************* Tx ******************/
/*****************************************/
INSERT INTO @tbl_Sales_Tx
SELECT DISTINCT
[Date] --[Date] Datetime
,[TicketNumber] --[Ticket] int
,0 --[T1] money
,0 --[T2] money
,0 --[T3] money
,0 --[Tax] money
,0 --[Amount] money
,0 --[Tip] money
,0 --[GCsale] 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_Sales_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)
, [GCsale] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'GC Purchase%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'GC Purchase%')
ELSE (SELECT 0)
END)
, [Tip] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Tip%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Tip%')
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)
, [T3] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
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)
+ (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
ELSE (SELECT 0)
END)
WHERE [Ticket] = @int_N
set @int_N = @int_N + 1
END
update @tbl_Sales_Tx set [TotalAmount] = [TotalAmount] - ([Tip] + [GCsale]) where [TotalAmount] > 0
--select * from @tbl_Transactions
--select * from @tbl_Sales_Tx
INSERT INTO @tbl_Tx_Z
SELECT
[Date]
,[Ticket]
,[T1]
,[T2]
,[T3]
,[Tax]
,[Amount]
,[TotalAmount]
FROM @tbl_Sales_Tx
ORDER BY [Ticket]
--select * from @tbl_Tx_Z
set @T1_taxout = (select isnull(sum([T1]),0)/@T1*100 from @tbl_Tx_Z)
set @T1_taxout_c = right(' '+convert(varchar(10),@T1_taxout),8)
set @T2_taxout = (select isnull(sum([T2]),0)/@T2*100 from @tbl_Tx_Z)
set @T2_taxout_c = right(' '+convert(varchar(10),@T2_taxout),8)
set @T3_taxout = (select isnull(sum([T3]),0)/@T3*100 from @tbl_Tx_Z)
set @T3_taxout_c = right(' '+convert(varchar(10),@T3_taxout),8)
set @T1_TTL = (SELECT isnull(sum([T1]),0) FROM @tbl_Tx_Z)
set @T1_TTL_c = right(' '+convert(varchar(19),@T1_TTL),19)
set @T2_TTL = (SELECT isnull(sum([T2]),0) FROM @tbl_Tx_Z)
set @T2_TTL_c = right(' '+convert(varchar(19),@T2_TTL),19)
set @T3_TTL = (SELECT isnull(sum([T3]),0) FROM @tbl_Tx_Z)
set @T3_TTL_c = right(' '+convert(varchar(19),@T3_TTL),19)
set @Tax_TTL = (SELECT isnull(sum([Tax]),0) FROM @tbl_Tx_Z)
set @Tax_TTL_c = right(' '+convert(varchar(19),@Tax_TTL),19)
set @SalesTaxExcl_TTL = @T1_taxout + @T2_taxout + @T3_taxout
set @SalesTaxExcl_TTL_c = right(' '+convert(varchar(19),@SalesTaxExcl_TTL),19)
set @SalesNoTax_TTL = (SELECT isnull(sum([TotalAmount]),0)-(@SalesTaxExcl_TTL + @Tax_TTL) FROM @tbl_Tx_Z)
set @SalesNoTax_TTL_c = right(' '+convert(varchar(19),@SalesNoTax_TTL),19)
set @Sales_TTL = (SELECT isnull(sum([Amount]),0)+isnull(sum([Tax]),0) FROM @tbl_Tx_Z)
set @Sales_TTL_c = right(' '+convert(varchar(19),@Sales_TTL),19)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '///////////////////','\\\\\\\\\\\\\\\\\\\'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Your Business ',' Your Corporation '
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'Z-num: ' + @var_WorkPeriod_ID,'REG: #############'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, @var_WorkPeriod_Beg,'Ticket_Beg: '+@var_Ticket_Beg
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, @var_WorkPeriod_End,'Ticket_End: '+@var_Ticket_End
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '[------------TYPE-]','[----------AMOUNT-]'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T1['+substring(convert(varchar(5),@T1),1,4)+']('+@T1_taxout_c+'):', @T1_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T2['+substring(convert(varchar(5),@T2),1,4)+']('+@T2_taxout_c+'):', @T2_TTL_c
--T3 INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T3['+substring(convert(varchar(5),@T3),1,4)+']('+@T3_taxout_c+'):', @T3_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Tax Total:', @Tax_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Sales (Tax Excl):', @SalesTaxExcl_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Sales (NonTaxed):', @SalesNoTax_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Sales Total:', @Sales_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '\\\\\\\\\\\\\\\\\\\','///////////////////'
SELECT [F1],[F2] FROM @tbl_ZED ORDER BY [ID]
