Is there a Tutorial available for the Automation/Scripts feature? I was hoping to be able to run a SQL Query with the touch of a button, and/or perhaps on Workperiod Close/End. Is this possible when using MSSQL Express (2008)?
You can save your SQL script to a file *.sql and use sqlcmd command to execute script. You can find a lot of information about that from google. For example http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e00c796-5854-4a2c-91d3-7b325420fd14/how-to-create-batch-file-to-execute-sql-scripts?forum=sqlgetstarted
To integrate this script to SambaPOS you can use “Start Process” action. You can execute your command or execute a *.bat file with that action. Finally you can attach this action to a button by using automation commands.
Hi Emre,
I understand the Start Process action.
But How to use the Manage - Automation - Script ?
- What is Handler ?
- I added un new script (Copy/paste) but I can only see the begining of
the page, cannot scroll down through my script.
Thank you,
Marc
Scripting is a customization feature that we used on some of our private projects. It supports a custom programming language but it is undocumented and under development so you can ignore that part. Handler tells SambaPOS at what point it should execute that script.
If you can tell me what you are trying to achieve and what script you are using I can recommend something better.
1 Like
Start Process works great for my purposes. Upon Work Period End, I execute a BAT file which runs 3 SQL scripts which save report data to their own tables and TXT/CSV files and then prints those reports.
@echo off
cls
set SName=localhost\sqlexpress
set DbName=SambaPOS4
D:
CD\
CD D:\Programs\POS\SALES
@echo on
::DAILY
set SQL=Auto_Report.sql
set ERRFILE=_ErrFile_Report.txt
if exist %ERRFILE% del %ERRFILE%
sqlcmd -S %SName% -E -d %DbName% -I -i %SQL% >> %ERRFILE% 2>&1
::@notepad %ERRFILE%
timeout 1
::ZED
set SQL=Auto_ZED.sql
set ERRFILE=_ErrFile_ZED.txt
if exist %ERRFILE% del %ERRFILE%
sqlcmd -S %SName% -E -d %DbName% -I -i %SQL% >> %ERRFILE% 2>&1
::@notepad %ERRFILE%
timeout 1
::CASHOUT
set SQL=Auto_Cashout.sql
set ERRFILE=_ErrFile_Cashout.txt
if exist %ERRFILE% del %ERRFILE%
sqlcmd -S %SName% -E -d %DbName% -I -i %SQL% >> %ERRFILE% 2>&1
::@notepad %ERRFILE%
Here is one of the SQL scripts (Auto_Cashout.sql) …
/*****************************************/
/************* 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 @int_PrintCASHOUT int = 1
declare @BASEPATH varchar(100) = ''
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** USER SECTION (Begin) ***/
/*** set WorkPeriod Begin & End Dates ***/
/*** select Foreign Currency Exchange Rate ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
set @BASEPATH = 'C:\D\Programs\POS\SALES\'
set @int_PrintCASHOUT = 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 = [Rate] FROM [dbo].[TaxTemplates] WHERE [Name] = 'T1'
SELECT @T2 = [Rate] FROM [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_Report 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 **************************************************************/
/******************************************************************************************/
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 --[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
/*****************************************/
/*************** 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 [dbo].[AccountTransactionValues] txval
left join [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%')
UPDATE @tbl_Payout_Tx SET
[Acc_ID_Target] = (
SELECT
txval.[AccountID] as Tx_Acc_ID_Source
FROM [dbo].[AccountTransactionValues] txval
left join [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%')
)
UPDATE @tbl_Payout_Tx SET
[Tx_Acc_Name_Target] = (
SELECT 'Payout ' + [Name]
FROM [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%'
/*****************************************/
/****************** 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
, isnull(sum([Amount]),0)
, isnull(sum([Amount]),0)/@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 [dbo].[AccountTransactionValues] txval
left join [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 [dbo].[AccountTransactionValues] txval
left join [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
, isnull(sum([Total]),0)
, isnull(sum([Total_USD]),0)
,'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 isnull([Total],0) 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 isnull([Total_USD],0) 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 isnull([Total],0) 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 isnull([Total_USD],0) 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 isnull([Total],0) 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 isnull([Total_USD],0) 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'
)
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, '*******************','*******************'
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, ' CASHOUT ',' REPORT '
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, 'Z-num: ' + @var_WorkPeriod_ID,''
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, @var_WorkPeriod_Beg,'Ticket_Beg: '+@var_Ticket_Beg
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, @var_WorkPeriod_End,'Ticket_End: '+@var_Ticket_End
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, '[------------TYPE-]','[----------AMOUNT-]'
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, 'HNL :',(SELECT right(' '+convert(varchar(19),[Total]),19) from @tbl_Cashout WHERE [Total_Type] = 'Cashout Lempira')
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, 'USD :',(SELECT right(' '+convert(varchar(19),[Total_USD]),19) from @tbl_Cashout WHERE [Total_Type] = 'Cashout USD')
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, 'Credit Card :',(SELECT right(' '+convert(varchar(19),[Total]),19) from @tbl_Cashout WHERE [Total_Type] = 'Cashout CC')
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, '-------------------','-------------------'
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, '*** TOTAL in HNL :',(SELECT right(' '+convert(varchar(19),[Total]),19) from @tbl_Cashout WHERE [Total_Type] = 'CASHOUT TOTAL')
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, '*** TOTAL in USD :',(SELECT right(' '+convert(varchar(19),[Total_USD]),19) from @tbl_Cashout WHERE [Total_Type] = 'CASHOUT TOTAL')
INSERT INTO @tbl_Cashout_Report SELECT @int_WorkPeriod_ID, '*******************','*******************'
DELETE FROM [dbo].[CASHOUT] WHERE [WPID]=0
DELETE FROM [dbo].[CASHOUT] WHERE [WPID]=@int_WorkPeriod_ID
INSERT INTO [dbo].[CASHOUT]
SELECT [WPID],[F1],[F2] FROM @tbl_Cashout_Report ORDER BY [ID]
SELECT [F1],[F2] FROM @tbl_Cashout_Report ORDER BY [ID]
DECLARE @outputfile varchar(300), @PATH varchar(200), @FileName varchar(100), @bcpCommand varchar(2000)
SET @PATH = @BASEPATH + 'Cashout\'
SET @FileName = 'C'+@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].[CASHOUT] 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_PrintCASHOUT = 1)
BEGIN
print 'Printing report... ' + CONVERT(VARCHAR(20), GETDATE(),120)
SET @bcpCommand = 'print /D:\\localhost\EPSONReceipt "' + @outputfile + '"'
print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
WAITFOR DELAY '00:00:02'
print 'Printing CUT... ' + CONVERT(VARCHAR(20), GETDATE(),120)
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
WAITFOR DELAY '00:00:02'
print 'Printing Complete... ' + CONVERT(VARCHAR(20), GETDATE(),120)
END
The Cashout Report (C0000250_2014-05-03_07.41.15.txt) even shows that Payouts from Cash drawer in HNL were greater than received Sales that day (the value is negative)…
******************* *******************
CASHOUT REPORT
Z-num: 0000250
2014-05-03 07:41:15 Ticket_Beg: 0012988
2014-05-03 14:24:44 Ticket_End: 0013035
[------------TYPE-] [----------AMOUNT-]
HNL : -1022.00
USD : 108.45
Credit Card : 3960.00
------------------- -------------------
*** TOTAL in HNL : 5107.00
*** TOTAL in USD : 255.35
******************* *******************
You are doing amazing things with SQL @QMcKay and I’m inspired to think about how a custom reporting tool should be implemented. I’ll try to release useful tools for integrating such scripts seamlessly into new reporting system.
1 Like
While we’re on the topic, out of curiosity, what does Use Shell Execute mean?
2 Likes