Work Period Report Document ID

JohnS · May 26, 2014, 12:46pm

After speaking with a couple of clients today, they both have requested if the WPR could have a unique Document ID.
This has been a request from their accountants/auditors. Neither could give a specific answer on why this is needed. I believe it is because Z-Tape printouts from cash registers have this.

Is it possible to add “Document ID :” to the WPR and use the ID from the database for each Work Period. Obviously when the WPR covers multiple work periods we would need to show the range of ID’s like 56-62 for example for a report covering a week, etc.

QMcKay · May 26, 2014, 2:11pm

I’ll second this request. It’s something I asked for in V3. Please also include Start/End Ticket Number. My accountant would not let me use SambaPOS until these tracking controls were in place, so I ended up making my own Z-Report.

File: Z0000250_2014-05-03_07.41.15.txt

On the event Work Period Ended, I fire a SQL script using the Start Process Action to execute a BAT file that runs the SQL, which stores the Z-Report in a DB Table, saves it to a TXT file, and subsequently prints it to the Receipt printer…

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** ZED REPORT                                                                         ***/
/***                                                                                    ***/
/*** see USER SECTION (after declarations) to set Date for Report                       ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/

/*****************************************/
/************* 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 @int_PrintZED int = 1

declare @BASEPATH varchar(100) = ''

/******************************************************************************************/
/******************************************************************************************/
/***                                                                                    ***/
/*** USER SECTION (Begin)                                                               ***/
/*** set WorkPeriod Begin & End Dates                                                   ***/
/*** select Foreign Currency Exchange Rate                                              ***/
/***                                                                                    ***/
/******************************************************************************************/
/******************************************************************************************/

set @BASEPATH = 'D:\Programs\POS\SALES\'
set @int_PrintZED = 1

-- 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 = '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'

-- 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_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
   ,[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_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 --[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)
, [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

--select * from @tbl_Transactions
--select * from @tbl_Tx

INSERT INTO @tbl_Tx_Z
SELECT
    [Date]
   ,[Ticket]
   ,[T1]
   ,[T2]
   ,[T3]
   ,[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, '      Business     ','   Registration    '
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '        Name       ','     xxxxxxxxx     '
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'Z-num:      ' + @var_WorkPeriod_ID,' '
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)+']('+(SELECT right('        '+convert(varchar(10),sum([T1])/@T1*100),8) FROM @tbl_Tx_Z)+'):', (SELECT right('                   '+convert(varchar(19),sum([T1])),19) FROM @tbl_Tx_Z)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T2['+substring(convert(varchar(5),@T2),1,4)+']('+(SELECT right('        '+convert(varchar(10),sum([T2])/@T2*100),8) FROM @tbl_Tx_Z)+'):', (SELECT right('                   '+convert(varchar(19),sum([T2])),19) FROM @tbl_Tx_Z)
-- T3 taxes INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T3['+substring(convert(varchar(5),@T3),1,4)+']('+(SELECT right('        '+convert(varchar(10),sum([T3])/@T3*100),8) FROM @tbl_Tx_Z)+'):', (SELECT right('                   '+convert(varchar(19),sum([T3])),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([T1])/@T1*100)+(sum([T2])/@T2*100)),19) FROM @tbl_Tx_Z)
-- T3 taxes INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '  Sales (Tax Excl):', (SELECT right('                   '+convert(varchar(19),(sum([T1])/@T1*100)+(sum([T2])/@T2*100)+(sum([T3])/@T3*100)),19) FROM @tbl_Tx_Z)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '       Sales Total:', (SELECT right('                   '+convert(varchar(19),(sum([Tax]))+(sum([T1])/@T1*100)+(sum([T2])/@T2*100)),19) FROM @tbl_Tx_Z)
-- T3 taxes INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '       Sales Total:', (SELECT right('                   '+convert(varchar(19),(sum([Tax]))+(sum([T1])/@T1*100)+(sum([T2])/@T2*100)+(sum([T3])/@T3*100)),19) FROM @tbl_Tx_Z)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '\\\\\\\\\\\\\\\\\\\','///////////////////'

DELETE FROM [dbo].[ZED] WHERE [WPID]=0
DELETE FROM [dbo].[ZED] WHERE [WPID]=@int_WorkPeriod_ID
INSERT INTO [dbo].[ZED]
SELECT [WPID],[F1],[F2] FROM @tbl_ZED ORDER BY [ID]

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

DECLARE @outputfile varchar(200), @PATH varchar(100), @FileName varchar(100), @bcpCommand varchar(2000)
SET @PATH = @BASEPATH + '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 [SambaPOS4].[dbo].[ZED] WHERE [WPID]='+@var_WorkPeriod_ID+'"'
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

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 = @BASEPATH + '!cut.txt'
SET @bcpCommand = 'print /D:\\localhost\EPSONReceipt ' + @outputfile
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
END