I am trying to get the exact payment amount by users. This would calculated as
Payment Amount - Change Issued
I got this from the work period report but the change issue is not minus/deducted
@{REPORT PAYMENT DETAILS:P.User,P.Amount.Sum::{0}:,}
[Settled by $1:1, 1, 1]
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(PU=$1)}
Total Income|{REPORT PAYMENT DETAILS:P.Amount.Sum:(PU=$1)}
That does not include change. It should be the amount minus change. If you paid $50 and ticket was $30 it will show $30 payment. It won’t if you are using a calculation to do something with change.
Hi Jesse,
Thank you for your reply, I did not do any calculations however the values are showing includes the change amount. I have gone through and complete a few test and same result. My version is 5.5.4
The Amount field corresponds to the Amount column in the database and has no bearing on the tendered amount.
Here is a $50 cash payment towards a $30 ticket, as in Jesse’s example:
using your report shows the following:
Perhaps you could provide some more details. Clear your database and accept some payments. See what the report shows.
@Memo, so I have cleared the database, ran a ticket with a total of $750 and Tender $1000, here is the result
Report
My other test would be to reset samba completely to the test database and try again. I will update you.
Do you have any Triggers on insert for this Tickets table?
This will be nearly impossible for community on this forum to debug without access to database. Id recommend you contact support@sambapos.com and provide them with a copy of the database
Can you show
A screenshot of that ticket
Hi @Jesse I have found what is causing the issue
Support had sent this linked instructions for currency
https://kb.sambapos.com/en/2-4-3-adding-different-currencies-and-foreign-exchange-working-system/
When implemented I received this issue.
Database:
These test were done using the default database and settings from a fresh installation. The only things I have setup is the currency and change payment type based on these instructions:
https://kb.sambapos.com/en/2-4-3-adding-different-currencies-and-foreign-exchange-working-system/
Can anyone replicate the issue.
@Posflow here is a copy of the database I am currently using to test this issue
SambaPOS5_202301012353SQM.zip (604.2 KB)
I threw together something that may work. Unfortunately, percentages of sales isn’t displayed because of the differing exchange rates. It may be possible but I just didn’t put in any effort to do so.
I’m making an assumption that the change payment DateTime will always match the payment DateTime. That’s the only thing that I can see that would link a change payment to a payment. There is a reference to the document id, but with multiple payments per ticket this isn’t a unique reference.
I made some assumptions on rounding for display (2 decimal places).
I’ve tested this with a handful of payments and it looks okay to me. But be sure to thoroughly test on your end.
You’ll need to add a script:
Handler name: @@PaymentTotalsByUser
Script body:
DECLARE @UserName NVARCHAR(255) = N'@1';
DECLARE @StartDate NVARCHAR(25) = N'{Start}';
DECLARE @EndDate NVARCHAR(25) = N'{End}';
DECLARE @UserId INT;
SELECT @UserId = Id
FROM dbo.Users
WHERE Name = @UserName;
IF (@UserId IS NOT NULL)
BEGIN
SELECT p.Name AS PaymentType,
CONVERT(DECIMAL(16, 2), ROUND(SUM((p.Amount - ISNULL(cp.Amount, 0)) / p.ExchangeRate), 2)) AS Total
FROM dbo.Payments p
LEFT JOIN dbo.ChangePayments cp
ON cp.AccountTransaction_AccountTransactionDocumentId = p.AccountTransaction_AccountTransactionDocumentId
AND p.Date = cp.Date
WHERE p.Date
BETWEEN @StartDate AND @EndDate
AND @UserId = @UserId
GROUP BY p.Name;
END;
Report:
@{REPORT PAYMENT DETAILS:P.User:::,}
[Settled by $1:1, 1, 1]
@@PaymentTotalsByUser:$1
@Memo thank you for this script, I have test the script and this is my result:
what about the TicketId would that be unique to the transactions?
p.Date = cp.Date does nothing for me because the date field represents Date and Time(so goes down to seconds). It work whenever I remove this part. Presents the same results.
All users are getting the same total.
I am testing with few charges for 1 ticket.
I have a question is this the way sambapos operates for multicurrency.
TicketId is not a unique reference for a payment, unfortunately.
Not sure what you mean about the date join. But for me I’m getting an error in the report viewer. Let me play around on my end and I’ll get back to you.
But I just tested a payment and the change payment date doesn’t match the payment date. Now I’m not sure if one can link a change payment to a payment.
I have a question is this the way sambapos operates for multicurrency.
I gave up a couple years ago after fighting with rounding, inverse this, decimal places that. I didn’t even get to the change payment stuff and this would have drove me absolutely batty.
Yeah, I think this is going to be a no-go, sorry.
The only way I can see this working is adding a column PaymentId to the change payments table and with an FK to the payments table. For that, you’d have to make a request for the devs to add that functionality.
@Memo thank you for trying to helping me. I am creating and testing a sql query, will share shortly.
Thank you
@Memo I have created this script using the one you have sent as a template.
DECLARE @UserName NVARCHAR(255) = N'@1';
DECLARE @StartDate NVARCHAR(25) = N'{Start}';
DECLARE @EndDate NVARCHAR(25) = N'{End}';
DECLARE @UserId INT;
SELECT @UserId = Id
FROM dbo.Users
WHERE Name = @UserName;
IF (@UserId IS NOT NULL)
BEGIN
SELECT pp.Name AS PaymentType,
CONVERT(DECIMAL(16, 2), ROUND(SUM((pp.TenderedAmt - ISNULL(cpp.changeAmt, 0))), 2)) AS Total
FROM (SELECT p.UserId, p.Name, sum(p.Amount) as TenderedAmt FROM dbo.Payments p WHERE p.Date BETWEEN @StartDate AND @EndDate AND @UserId = @UserId GROUP BY p.Name, p.UserId) pp
LEFT JOIN (SELECT cp.UserId, cp.Name, sum(ISNULL(cp.Amount,0)) as changeAmt FROM dbo.ChangePayments cp WHERE cp.Date BETWEEN @StartDate AND @EndDate AND @UserId = @UserId GROUP BY cp.Name, cp.UserId) cpp
ON pp.Name = cpp.Name
GROUP BY pp.Name;
END;
My Issue now is that all users showing the same amount. This was also happening with the original script you have sent. How can I fix this?
Just a thought, could a some form of a Payment Description be added at the time of the payment, and filtered that way?
@Bob_be I think that could work, only if the system is not using that field already.
so could say something like P12, P = Payment and 12 = Ticket Number.