Hello,
I am trying to make an android widget for samba. I got this done through another pos by querying the reports data from ms sql by using php.
Only problem Im having is finding the sales reports in the database. I found each sale individually but not the sales totals percentages etc. Are they not stored on the database at all?
They are not stored in the DB, they are calculated on-the-fly.
Yeah I figured. @QMcKay
BTW you could still accomplish what you want. You could use the PHP to generate reports by running queries similar to how Custom Reports works. Might be little more complicated and involved to build it but technically its possible.
Yup thats exactly what I will be doing. The total is going to be easy but the individual item percentage sales are going to be a little more complicated.
Hey do you happen to have any sql querys that I could use for some more advanced reports with php. I will be looking into creating more myself I was hoping you would have some advanced queries that you use for the samba program.
I got the total sales by getting the sum of the Amount column in the payements table which was easy but was looking to accomplish a little more like mybe top 3 items sold, how many orders and money was voided, how much money total is in open tables, and how many tables are open and how many tables were closed.
@QMcKay is a SQL pro maybe he could give you some resources to look into. You should maybe look at the Custom Reports forum section and look at some of the work @gerlandog has done.
All of my reporting is done in SQL, necessitated early on before Custom Reporting was implemented.
This script is solely for summarizing Sales, Payouts (Purchases and Expenses), and Cashout (drawer money count). It subsequently inserts the data into some pre-made tables in the DB so I can look up the data later on, and it also prints the data to TXT file and receipt printer. I won’t try to explain the way it works, but it may give you a head-start on some tables to query and how I chose to aggregate the data.
Auto_Report.zip (4.6 KB)
It doesn’t do the specific things you’re asking for, like Item Sales counts and Void counts, but I’m not interested in that - there are built-in reports for that already.
Also, please keep in mind that the script was developed well over a year ago (V4 wasn’t even invented yet), when I had little understanding of the data and table relations within the DB. Over time, new bits of code and changes have been “patched in” in order to come up with desired values. It could probably use a re-write at this point, since I’m sure there are inefficiencies within it, and given that I understand the DB layout and data much, much better than I did when I first began writing the script.
I don’t think @emre has the SQL for the built-in reports - I remember he mentioned they are created using some type of relational tool - though I could be wrong, or maybe the tool will reveal the SQL script somewhat like how MS Access does.
OK thank you all I needed was a place to start. @QMcKay