Add Settled by Terminal to Work Period Report

[na1978]

@emre

Thanks for confirming that it work. I will give it a try.

It seems very involved. Is there an easier way to track by Terminal? I think, tracking payments by Terminal should be part of Work Period Report.

Best Regards,

[emre]

Since you’ll have two different payment types it will appear separately in Work period report. I’ve not tried it but if you don’t want separate payment accounts under accounting screen you can try choosing same cash account for payment types.

[na1978]

We have 3 different payments. Cash, credit card, Gift cards. Would this work?

[gerlandog]

I have one cash acc, one credit card acc and one customer acc and 2 terminals or venues and in the reports they appear sepparated!!

G.

[na1978]

@gerlandog

The issue is if you have multiple cashiers cashing tables between two terminals. They appear separate by cashier, but we need to track payments by terminal so we can count tills.

[gerlandog]

As @emre wrote, use sepparate accounts…

G.

[Jesse]

More than likely what you are wanting to do will not show up in reports yet until the expanded report abilities are released in a future release.

What gerlandog is trying to say is you can build your accounts page to basically give you what you want with a live glance without having to run a report. You can create two Cash accounts, Two credit accounts, two gift accounts, and you can make it operate from each individual terminal. Both sets of accounts would show up on the accounts screen it would all feed into the single receivables but for till counting purposes it would work.

[na1978]

@emre, @Jesse, @gerlandog

Thank you for your help. I will experiment with different accounts.

[na1978]

@emre, @Jesse, @gerlandog, @QMcKay

Is there a way to run SQL query against SambaPOS DB to retrieve Total Amount Settled by Terminal.

Thanks guys,

[Jesse]

You can run any report you want from SQL

[na1978]

@kendash,

I am aware I can run any SQL query. LOL. I was wondering if anyone can help with building the query. It seems that dbo.Payments table does not track terminal where payment is made.

@emre,

Is it possible to track Terminal where payment is made?

Thank you,

[emre]

OK. It is becoming hard to both doing development and answering questions so sorry for my late replies. I’ll show you an alternative solution for tracking payments by till.

I’ve added two tills

I’ve added two ticket tags. Till 1 Cash and Till 2 Cash. Tagging Type should be Price. Since I don’t want to see buttons for these tags I won’t create mappings.

If you need to handle more payment types you can add more tags. (eg. Till 1 CC, Till 2 CC)

I’ll create an action to update tags.

For each payment type for each till I’ll add a rule.

This one will update Cash for Till 1

I’m mapping this rule to Till 1 terminal.

We’ll need another one for Till 2

… and that rule should be mapped to Till 2 Terminal.

Till 1’s Local Settings should appear like this and Till 2 should be selected for other terminal.

Now I’ll settle two $5 ticket’s from Till 1 and one $15 ticket from Till 2.

That will appear on Work Period Report like this.

[na1978]

@emre

Implemented this today and it works great. Once again, thank you very much. It makes our job much easier.

Additionally, it would be very nice if we can have Terminal name with dbo.Payments table. I plan to use Custom Reports in the feature to pull this report.

Best Regards,

[emre]

I’ve added terminal Id to tickets, orders and payments. It will be available for next (4.1.49) update.

[na1978]

Thanks you. I look forward.

[na1978]

@emre,

I will use Custom SQL module to query for Payments made on each terminal. I checked Payments table using MS SQL Server Management Studio, but TerminalID is showing a number. I thought it should show Terminal Name, so I can query payments make to that Terminal.

Can you assist with writing an SQL query to pull payments made on each terminal? It should be helpful for anyone how is facing this issue. I am sure SambaPOS is used with multiple tills.

or

Can you include the above request on Work Period Report.

Best Regards

[QMcKay]

Use a subquery to pull the Terminal Name…

use [SambaPOS4]
SELECT [Id]
      ,[TicketId]
      ,[PaymentTypeId]
      ,[Name]
      ,[Date]
      ,[AccountTransactionId]
      ,[Amount]
      ,[AccountTransaction_Id]
      ,[AccountTransaction_AccountTransactionDocumentId]
      ,[UserId]
      ,[TenderedAmount]
      ,[DepartmentId]
      ,[TerminalId]
      , (select [Name] from [Terminals] where [Id]=[TerminalId]) as TerminalName
  FROM [Payments]

The subquery is the last line:

(select [Name] from [Terminals] where [Id]=[TerminalId]) as TerminalName

[na1978]

@QMcKay

Thank you so much for offering help with my SQL queries.

I just pick up some books on SQL and started learning, hopefully one day can pay back the community.
Here is what I am trying to accomplish. Total all Payment Types made on each Terminal.

Sample Report

Terminal Name: REGISTER

Payment Type: Total:

Cash Total Cash tendered on REGISTER
Credit Card Total Credit Card tendered on REGISTER
Gift Card Total Gift Card tendered on REGISTER

Terminal Name: BAR

Payment Type: Total:
Cash Total Cash tendered on BAR
Credit Card Total Credit Card tendered on BAR
Gift Card Total Gift Card tendered on BAR

*Skip Payment Type with Zero balance

[na1978]

@QMcKay or anyone good with SQL queries,

Can you please help with above query. I will be eternally thankful.

[Jesse]

You can learn a lot through microsoft management studio. It has a query builder tool and while its not exactly the same… it would give you a good understanding of the structure… then you can pickup basics by looking through scripts already posted on these forums.