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

English Boards > V3 Development

(1/2) > >>

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: ---/*******************************************************/
/* */
/****************** 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]

--- End code ---

Navigation

[0] Message Index

[#] Next page

Go to full version