REQUEST: Work Period Report: report number/id and first/last ticket numbers

[QMcKay]

This is a rather urgent request.

My accountant is requiring the Work Period Report contain a control number, for example, the ID of the report which increments whenever a new report is generated (or End Work Period).  He also wants the beginning and ending ticket numbers that span the period of the report.  It is necessary for the numbers to be part of the print out - I am not allowed to write it on afterwards.  It would be nice to have this information near the top of the report, under the user/restaurant details.

We are accustomed to having a Z-report in North America, which contains an incremental control number or "Z-number".

If I knew how and where a report is stored in the SQL DB, I could essentially create this myself.  Can you please either implement this feature request, or point me in the correct direction for pulling the reports out of the DB (SQL Query).

Thanks!

[emre]

I hope to develop a report customization feature for such needs in the future. For a quick solution I can include workperiod id as report ID and ticket number range on "General Info" section of the report. Let me know if it solves your issue. I don't want to include them on header because header part is common for all reports and generating a specific header for WPR needs a little extra work.

[QMcKay]

I would prefer the numbers appear near the top of the report, since the pertinent accounting info is in the first section of the report, and I don't need to give the accountant any of the following sections.  But anything is better than nothing right now, and I'll take what I can get.

Thanks Emre - I look forward to the next release!

P.S.  When you start and end a work period, you can enter a comment - where is this located? I don't see it on the report?

Regards,
QMcKay

[kylemclean]

Hi Emre and QMcKay

I think this is quite an important feature in many countries.
Can't you include the current date and time when printing accounts screens as well?
It would make it easier to keep track.

Regards,
Kyle

[QMcKay]

Here's a script to obtain Sales & Tax totals based on a WorkPeriod date range.  It includes the WorkPeriod ID (Z_num), Start/End Dates, and Beginning/Ending Ticket Numbers and Transaction Totals.  This script was tested in SQL Express 2008 R2.  It uses in-memory tables to store summary data,  and is non-destructive.

In my case, I have 2 different Tax-rates, and they are named "T12" and "T15".   You will need to modify it to suit your needs.

Code: [Select]

/*******************************************************/
/*                                                     */
/****************** Work Period Summary ****************/
/*                                                     */
/*******************************************************/

/*****************************************/
/************* DO NOT TOUCH **************/
/***** Declarations & Initialization *****/
/*****************************************/

declare @int_WorkPeriod_ID int
declare @dat_WorkPeriod_Beg Datetime
declare @dat_WorkPeriod_End Datetime
declare @int_Ticket_Beg int
declare @int_Ticket_End int
declare @int_RR int
declare @tbl_Transactions table
(
[ID]  INT IDENTITY(1,1) NOT NULL
   ,[TicketNumber] int
   ,[Date] Datetime
   ,[TotalAmount] money
   ,[TransactionDocument_Id] int
   ,[Amount] money
   ,[Name] varchar(255)
)

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_Type] varchar(255)
)

set @int_RR = 0
set @int_WorkPeriod_ID = 0
set @dat_WorkPeriod_Beg = '9999-12-31 23:59:59'
set @dat_WorkPeriod_End = '9999-12-31 23:59:59'
set @int_Ticket_Beg = 0
set @int_Ticket_End = 0




/*****************************************/
/************* USER SECTION **************/
/***** set Begin & End Dates here ********/
/*****************************************/

set @dat_WorkPeriod_Beg = '2013-10-13 00:00:00'
set @dat_WorkPeriod_End = '2013-10-14 00:00:00'




/*****************************************/
/************* DO NOT TOUCH **************/
/************* CREATE REPORT *************/
/*****************************************/

select @int_WorkPeriod_ID = [Id] FROM [SambaPOS3].[dbo].[WorkPeriods] where [StartDate] >= @dat_WorkPeriod_Beg and [EndDate] <= @dat_WorkPeriod_End
select @dat_WorkPeriod_Beg = [StartDate], @dat_WorkPeriod_End = [EndDate] FROM [SambaPOS3].[dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID

print @int_WorkPeriod_ID
print @dat_WorkPeriod_Beg
print @dat_WorkPeriod_End

INSERT INTO @tbl_Transactions
SELECT
   tkt.[TicketNumber]
  ,tkt.[Date]
  ,tkt.[TotalAmount]
  ,tkt.[TransactionDocument_Id]

  ,tx.[Amount]
  ,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

print @int_Ticket_Beg
print @int_Ticket_End

INSERT INTO @tbl_Report
SELECT
  @int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, 'Sales Total'
FROM @tbl_Transactions
WHERE [Name] like '%Sale%' or [Name] like '%T12%' or [Name] like '%T15%'

INSERT INTO @tbl_Report
SELECT
  @int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, 'Sales Total Tax Excluded'
FROM @tbl_Transactions
WHERE [Name] like '%Sale%'

INSERT INTO @tbl_Report
SELECT
  @int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, 'Tax Total'
FROM @tbl_Transactions
WHERE [Name] like '%T12%' or [Name] like '%T15%'

INSERT INTO @tbl_Report
SELECT
  @int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, 'Tax T12'
FROM @tbl_Transactions
WHERE [Name] like '%T12%'

INSERT INTO @tbl_Report
SELECT
  @int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
, 'Tax T15'
FROM @tbl_Transactions
WHERE [Name] like '%T15%'



/*****************************************/
/*************** SHOW REPORT *************/
/*****************************************/

SELECT * FROM @tbl_Report ORDER BY [ID]

SELECT * FROM @tbl_Transactions ORDER BY [ID]


[QMcKay]

Newer version...

Code: [Select]

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** DAILY ZED                                                                          ***/
/***                                                                                    ***/
/*** 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(6) = '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 @int_PrintZED int = 1
declare @int_SaveZED int = 1


/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** USER SECTION (Begin)                                                               ***/
/*** set WorkPeriod Begin & End Dates                                                   ***/
/*** select Foreign Currency Exchange Rate                                              ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/
set @int_SaveZED = 0
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 = '2013-10-24'
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
--set @dat_WorkPeriod_End = '2013-10-24 23:23:07.937'

-- ForeignCurrency ExchangeRate
SELECT @dec_XR = [ExchangeRate] FROM [SambaPOS3].[dbo].[ForeignCurrencies] WHERE [Name] = 'USD'

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** 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
   ,[T12] money
   ,[T15] money
   ,[Tax] money
   ,[Amount] money
   ,[TotalAmount] money
)

declare @tbl_Tx_Z table
(
[ID]  INT IDENTITY(1,1) NOT NULL
   ,[Date] Datetime
   ,[Ticket] int
   ,[T12] money
   ,[T15] 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 *************/
/*****************************************/

select @int_WorkPeriod_ID = [Id] FROM [SambaPOS3].[dbo].[WorkPeriods] where [StartDate] >= @dat_WorkPeriod_Beg and [EndDate] <= @dat_WorkPeriod_End
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

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('000000'+convert(varchar(6),@int_WorkPeriod_ID),6)
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 --[T12] money
   ,0 --[T15] 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)
, [T12]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T12%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T12%')
ELSE (SELECT 0)
END)
, [T15]         = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T15%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T15%')
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 'T12%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T12%')
ELSE (SELECT 0)
END)
                + (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T15%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T15%')
ELSE (SELECT 0)
END)
WHERE [Ticket] = @int_N
set @int_N = @int_N + 1
END


INSERT INTO @tbl_Tx_Z
SELECT
    [Date]
   ,[Ticket]
   ,[T12]
   ,[T15]
   ,[Tax]
   ,[Amount]
   ,[TotalAmount]
FROM @tbl_Tx
ORDER BY [Ticket]


INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '///////////////////','\\\\\\\\\\\\\\\\\\\'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '   The Best Cafe   ','  Other Operating  '
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '     & Wine Bar    ','       Name        '
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'Z-num:       ' + @var_WorkPeriod_ID,' RTN: xxxxxxxxxxxxx'
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, 'Tax T12 ('+(SELECT right('        '+convert(varchar(10),sum([T12])/12*100),8) FROM @tbl_Tx_Z)+'):', (SELECT right('                   '+convert(varchar(19),sum([T12])),19) FROM @tbl_Tx_Z)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'Tax T15 ('+(SELECT right('        '+convert(varchar(10),sum([T15])/15*100),8) FROM @tbl_Tx_Z)+'):', (SELECT right('                   '+convert(varchar(19),sum([T15])),19) FROM @tbl_Tx_Z)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '         Tax Total:', (SELECT right('                   '+convert(varchar(19),sum([Tax])),19) FROM @tbl_Tx_Z)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '  Sales (Tax Excl):', (SELECT right('                   '+convert(varchar(19),sum([Amount])),19) FROM @tbl_Tx_Z)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '       Sales Total:', (SELECT right('                   '+convert(varchar(19),sum([TotalAmount])),19) FROM @tbl_Tx_Z)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '\\\\\\\\\\\\\\\\\\\','///////////////////'

SELECT [F1],[F2] FROM @tbl_ZED ORDER BY [ID]

IF (@int_SaveZED = 1)
BEGIN
DELETE FROM [SambaPOS3].[dbo].[ZED] WHERE [WPID]=@int_WorkPeriod_ID
INSERT INTO [SambaPOS3].[dbo].[ZED]
SELECT [WPID],[F1],[F2] FROM @tbl_ZED ORDER BY [ID]

DECLARE @outputfile varchar(200), @PATH varchar(100), @FileName varchar(100), @bcpCommand varchar(2000)
SET @PATH = 'D:\Programs\POS\SALES\ZED\'
SET @FileName = 'Z'+@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 + '"SELECT [F1],[F2] FROM [SambaPOS3].[dbo].[ZED] WHERE [WPID]='+@var_WorkPeriod_ID+'"'
SET @bcpCommand = @bcpCommand + ' queryout "' + @outputfile + '"'
SET @bcpCommand = @bcpCommand + ' -S localhost\SQLEXPRESS'
SET @bcpCommand = @bcpCommand + ' -U USERNAME -P PASSWORD'
SET @bcpCommand = @bcpCommand + ' -c -t " "'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
END


IF (@int_PrintZED = 1)
BEGIN
SET @bcpCommand = 'print /D:\\localhost\EPSONReceipt "' + @outputfile + '"'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
SET @outputfile = CHAR(27)+CHAR(105) -- ESC i
SET @outputfile = CHAR(29)+CHAR(86)+CHAR(66)+CHAR(0) -- GS V m n
SET @outputfile = @PATH + 'cut.txt'
SET @bcpCommand = 'print /D:\\localhost\EPSONReceipt ' + @outputfile
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
END