More Documentation for Account Receipt & Statements

Another late night at 6.30am but now have a pretty good room account system in place…
Still a few bits to work out.

Could do with some more info on account based template tags, either tags are basic or info if limited on forum.

Could really do with being able to dray entity info for related transaction documents.
Want to be able to put guest name etc on the receipt. Given entity and account are interlinked this must be possible.

Also would be nice to be able to set/add the description of the account transaction - something like ticket number…

Another one is there is Print Account Transactions which is pretty much a statement, but cant see a way to recreate this either through template or report in a way which could represent a tidy invoice of account for room bill.

Was allot of work, should have tried the pre-order method that is always recommended against :frowning:

Some pics of progress so far;




Looking back at it there isn’t that much worth to show for last 12 hours of tinkering but accounts side is first for me…
And first half was trying to work out the flow and getting work period report sorted.

1 Like

Hi @JTRTech,

Good work so far!
I am looking for a similar thing, I would like to be able to generate a bill that I can give to customers on check out. The custom reports module is new to me and I really struggle to get anything to work properly!
If you do figure out how to display account transactions in a nice format please share on here. As I would really like to be able to print out an invoice for the room bills.

Thanks,
Chris

Great work @JTRTech. Hotel & Room related workflows is not something most people here familiar with so if you can help us to get more info about your context we may have better idea about how to improve SambaPOS. For example you can start from teaching us and show examples about how account statements should expected to appear

1 Like

I am going to try and alter it a bit, ideally would prefer to have room entities but am unsure on the ability to change accounts.
As account format I understand can be defined with Account Name Template so am going to try and see if I can have room entities but have the account name change between bookings.
Does that throw up any red flags with you?

Encase that fails what would be nice would be an option to set the sort order of an entity grid/automatic layout, we can filter by state but if I end up sticking with the booking entity option it doesnt keep them in room number order which would be preferable so would be good to be able to have sorting options ideally of {ENTITY DATA:Rooms:Room Number} dont think the entity type definition would be needed as is entity screen specific. AND/OR by state.
Can imagine to ability to sort entity grid by state could come in handy if not on this but on others.
However thinking about it this wort order might also need to be able to be define in form of a C,S string like order groups as states ordering is unlikely to ever be in wanted order alphabetically.

Hotel type bills would ideally work similarly to how a pre-order setup would be on samba (tbh I did try preorder but couldnt get report to work right) but saying that now I have better understanding of report tags I could probably switch over, would be allot simpler set.

A Hotel would obviously work like a multiday ticket where a print bill would be their invoice.
I tried the account setup as struggled getting preorders to work with reports but realise now that its obviously because the report is looking at that work period only so would need to change that section of the code to not be work period defined, maybe with SQL.

Thinking about it now actually (if posible) ticket close would be a handy filter where it would report on only ticket that are still open as (room balance) might give it a go in a min.

@emre I really need to get this system sorted, was droped on me very last minute, they want for weekend.
I know preorder is not a recommended way but would be easiest to still have useable room bills.

Any chance you could help me with the reporting to show tickets that are still open which i can list as the ‘room ledger’/balance account.

Sales will count on the day ordered and payments on the day paid.
The variance in sales and payments will be the difference between the balance brought forward and balance carried forward on the open/room bills.

Last resort I can see is to have two reports printed on work period close, the work period report and a room balance report which could work well but cant work out how to set report dates through this action.
Would presumably be with parameters but how to set report date range within the report?

@QMcKay - know I have asked allot of you recently but I imagine SQL could possibly bypass the report date filer and allow reporting of unpaid tickets (still open) by entity? Am stressing out a bit as have spent far to long just experimenting on posible solutions and not actually making progress which is useable.

Start from here.

http://sambapos.com/wiki/doku.php/creating_custom_reports_with_sql

Try this

 Select * from Tickets

Learn how SQL Where works to filter records.

http://www.w3schools.com/sql/sql_where.asp

Select * from Tickets where IsClosed = 0 

Learn how to use field names instead of *

Select Id,TicketNumber from Tickets where IsClosed = 0 

Check how SQL Like keyword works to make partial searches.

http://www.w3schools.com/sql/sql_like.asp

Check how SQL Join works to create reports from two tables.

http://www.w3schools.com/sql/sql_join.asp

Check this topic for related discussions and samples.

2 Likes

The possibilities are endless when you use SQL. Almost all my reporting is done with SQL rather than Custom Reports… this is because I am very familiar with SQL, so I really have not taken the time learn the Reporting that Emre built into SambaPOS… and (unfortunately?), I don’t intend to learn Custom Reporting, since I can do everything I want in SQL much quicker. I realize this is not the case for everyone and the tools that Emre has created for Reports is fantastic, so I am not detracting from that fact.

It is difficult to help you with the SQL unless you are extremely specific on what you want to see. It is not difficult to script/code, but since there is so much flexibility when it comes to provisioning data from the DB, the details need to be very clear. Emre has already hinted with some good links on exactly how this is done.

The first question is which detail is the main facet that you want to see. It sounds like you need [AccountTransactionValues] since this is where the “money” is stored. This detail is important, because we need to know where to start, so we can determine the JOIN properly. That is, we don’t join Transactions to Tickets, instead we join Tickets to Transactions… the order of the join can make a difference in many cases, especially when we start to think about whether we want an Outer Join as opposed to an Inner Join. If not done properly, you will miss some data.

Once we have that information, we start at the inside and work outward. We continue to join tables that contain data we need, and then possibly wrap that in an outer query to provide summary data (working outward).

You can see that our base element contains good information, but it is difficult to read. So we join other tables to get readable [Name] values instead of [Id] values. We built our join, but lets make it look nicer by omitting [Id] columns from the base, and “replace” them with the [Name] values from our joined tables…

USE [SambaPOS5JV]
GO

SELECT
 tv.[Id]
,tv.[Name] as [TxValName]

--,tv.[AccountTransactionId]
,t.[Name] as [TxName]

--,tv.[AccountTransactionDocumentId]
,d.[Name] as [DocName]

--,tv.[AccountTypeId]
,at.[Name] as [AccountType]

--,tv.[AccountId]
,a.[Name] as [Account]

--,tv.[AccountTransactionTypeId]
,tt.[Name] as [TxType]

,tv.[Date]
,tv.[Debit]
,tv.[Credit]
,tv.[Exchange]

FROM [dbo].[AccountTransactionValues] tv
LEFT JOIN [AccountTransactions] t on t.[Id] = tv.[AccountTransactionId]
LEFT JOIN [AccountTransactionDocuments] d on d.[Id] = tv.[AccountTransactionDocumentId]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]

Notice we commented out the [Id] values from the base, and replaced them with the [Name] value of the joined tables, so now our result looks much better…

Ok, great. But what about the Ticket and the Entity?

To get Ticket (if any) and Entity (if any), we join in [Tickets], [TicketEntities], [Entities], and optionally [EntityTypes] …

,tkt.[Name] as [TktName]
,tkt.[Id] as [TktID]
,tkt.[TicketNumber] as [TktNum]
,et.[Name] as [EntityType]
,e.[Name] as [Entity]

FROM [dbo].[AccountTransactionValues] tv
LEFT JOIN [AccountTransactions] tx on tx.[Id] = tv.[AccountTransactionId]
LEFT JOIN [AccountTransactionDocuments] d on d.[Id] = tv.[AccountTransactionDocumentId]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]
-- Ticket and Entity data
LEFT JOIN [Tickets] tkt on tkt.[TransactionDocument_Id] = d.[Id]
LEFT JOIN [TicketEntities] te on te.Ticket_Id = tkt.[Id]
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId]
LEFT JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]

Great, now we have related Tickets, Entities, and Accounts…

Notice we may have a lot of NULL data. This is because nothing was matched in the join to that particular table. Not every Transaction has a Ticket, so it won’t have a TicketEntity, or Entity either. So now we start filtering in the WHERE clause…

WHERE 1=1
AND tkt.[Id] is not null -- only grab transactions that relate to a ticket
AND et.[Id] is not null  -- only grab transactions that have an entity assigned

Now we have a lot less data to look at… we’re getting there. Let’s see which Tickets are still open …

SELECT
-- tkt.[Name] as [TktName]
 tkt.[Id] as [TktID]
,tkt.[TicketNumber] as [TktNum]
,tkt.[IsClosed]

,et.[Name] as [EntityType]
,e.[Name] as [Entity]

--,tv.[AccountTypeId]
,at.[Name] as [AccountType]
--,tv.[AccountId]
,a.[Name] as [Account]

, tv.[Id]
,tv.[Name] as [TxValName]

--,tv.[AccountTransactionId]
,tx.[Name] as [TxName]

--,tv.[AccountTransactionDocumentId]
,d.[Name] as [DocName]


--,tv.[AccountTransactionTypeId]
,tt.[Name] as [TxType]

,tv.[Date]
,tv.[Debit]
,tv.[Credit]
,tv.[Exchange]


FROM [dbo].[AccountTransactionValues] tv
LEFT JOIN [AccountTransactions] tx on tx.[Id] = tv.[AccountTransactionId]
LEFT JOIN [AccountTransactionDocuments] d on d.[Id] = tv.[AccountTransactionDocumentId]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]
-- Ticket and Entity data
LEFT JOIN [Tickets] tkt on tkt.[TransactionDocument_Id] = d.[Id]
LEFT JOIN [TicketEntities] te on te.Ticket_Id = tkt.[Id]
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId]
LEFT JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]

WHERE 1=1
AND tkt.[Id] is not null -- only grab transactions that relate to a ticket
AND et.[Id] is not null  -- only grab tickets that have an entity assigned
AND tkt.[IsClosed] = 0   -- only grab tickets that are not closed

If we are starting to get useful data returned, now we may want to summarize it. For that, we need to wrap the whole thing in an outer Aggregate Query to sum values and give some “balances”.

1 Like

Hi @QMcKay
I need a similar thing and hope you can help.
I have my hotel rooms set up as customer accounts for guests to charge to their rooms. I would like to be able to print out a summary of how much they owe for when they checkout.
I think using SQL may be the way to go to get the reports I want, as I’m not getting anywhere with custom reports. I would like to be able to generate a report that looks something similar to the below:

This is just a simple excel doc where we copy the info from samba to excel manually. I want to be able to generate something similar from samba or sql.

I only have 11 rooms so don’t mind setting up a separate report for each room if needed, I was thinking in order to get the correct report I would select the date range that the guest stayed from and to, and samba/sql could pull up the tickets from within that date range, giving me a total, with a summary of each days spending.

I have no previous experience of using sql for reporting purposes and don’t even know where to start, so you will need to keep it simple with any instructions!!

Thanks in advance

Chris

@chris89
You might have more luck following this thread;


Still waiting on a final solution.