English Boards > V3 Development
REQUEST: Work Period Report: report number/id and first/last ticket numbers
QMcKay:
Newer version...
--- Code: ---/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** 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
--- End code ---
Navigation
[0] Message Index
[*] Previous page
Go to full version