I’ve just started learning SQL and i’ve realized it will take me at least a week or so in order to pin this down. If before that you happen to have the time please give it a go. Much thanks!
Dear @Jesse, if you reset your computer in the middle of a work period, you will se that none of your tickets nor orders are lost, ergo, data must be stored somewhere… perhaps @emre could help us out here…
Dear @paxi, I will try. As the state of stock prior to the last perido has no use to me, it may take time since i am trying to achieve other reports…
G.
EDIT:
@kendash… as I imagined, orders are there… I know what happend when you looked at the database. I imagine you where working in TRAINING MODE and you opend your SQL and digged into SambaPOS DB, well, you should have looked into training_xxxx DB.
So, the info is there… just waiting for us to use it!!! LOL
here are some screens:
G.
1 Like
Thanks. Do take your time. But do give it a try if you can 
Menu items is not the same thing as inventory item. I know the menu item is there. But I still do not see the inventory. And I do not use training mode because I am not live yet.
Here is an example. I pulled up orders after doing a few quick orders. Yes it shows MenuItemName but that is not inventory… My hamburger had Lettuce, Tomato, Onion, Bread, Pickles, Peppers, and Meat NONE of which is included in Orders Table. My Coke also included a Cup, Lid, Straw which are not included in Orders Table.
So as you can see Orders would not work for figuring stock levels. It might work if your menu item names are the inventory items and you are NOT using recipes… but that is not realistic.
You do not have to be using a Database to store value. Some of it is stored in the database some is not. It could be storing tickets, etc in database but not the actual inventory data until work period close. I do not know all of the storage methods but I do know that it uses SQL and it uses Cache, and some local files for storing values.
Those are receipes, and those include INVENTORY ITEMS. so, we have orders, and we have inventory items… I have to digg a little more… but I think we are close…
Thats why I sayd that I was working on it… I never sayd I have this ready…
LOL
G.
Your not reading what I am saying… I am not going to select hamburger, lettuce, tomato, onion, all as menu items… that would be ridiculous. This is why we have recipe’s. I am pretty sure @emre already explained somewhere that inventory calculations are not done with just SQL.
I just do not think its possible gerlandog. The required data to do that calculation is missing for us.
I did not say that you should add onion bread pickles to the order, I sayd that those items are inventory items wich are in a receipt wich we know is related to a menu item…
so perhaps we could get that data…
G
So in essence what you are saying is you are attempting to do your own calculation for inventory based on orders. I could see how that would work, but couldn’t that be prone to error since it is not really reading the same calculation?.. it would be separate from the actual calculation that is performed in Samba.
I would be interested to see how you get that working. That is a lot of factors to consider to get that running and to get it running parallel to the actual calculation Samba is doing. I am still not convinced you can do it with just SQL however… but I would be interested to see it.
One example of the complexity…
The recipe entry in SQL does not contain the inventory Items it contains the ID… there is a much bigger complexity than just joining tables and running equations on them. I hope you can figure it out though it would be great if it really is possible.
I just do not see it happening with a single SQL script. But like i always say… I am wrong a lot and I am not afraid to admit when I was wrong 
So if you figure out a way I will definitely give you a <3
I am struggling my head too… maybe… and that is just a stupid idea… we could ask @Emre to put togheter a widget that returns real stock, since that is something that apparently most of us are interested in… I dont know… just mumbling.
Thanks
G.
Yeah sorry I was not trying to doubt you or sound negative I was trying to help you sort out the possibility. The structure seems as if it stores key data… but the actual calculation is done with custom code within Samba… which makes sense… that code would be able to pull the values and run its own calculations on it… a SQL script would not.
It sounds like to me what we really need is a custom addon…supported through the Marketplace… it could be part of the post I made btw… This tool request below would feature that.
1 Like
I support the motion!!!
Thanks!!!
G.
@Jesse we don’t calculate inventory from day 1. When a work period ends we store inventory levels in a table and when a report requested we update them with sales made in active work period. I should admit I’ve didn’t reviewed that in detail but at first glance I’ve noticed @gerlandog generates that data from already calculated inventory values of the latest closed work period so he doesn’t really make an inventory calculation. He just sums already calculated values. Yes this report does not contain values from active work period but as OP requested to see that report after work period ends he’ll be able to see correct consumption values.
EDIT: @paxi VERY SORRY for bringing this so off topic I just realized what I had done.
@emre So would you say it may be possible to accomplish a real time stock level based off what we have been discussing? If so I may be interested in it as well. I understand now how he was getting that data… I was confused at first. What I am unsure of and was trying to grasp the logic with @gerlandog is if its possible to derive a live stock report from the SQL.
Or did I read that wrong agian… Did you basically just tell us that calculations are already performed on closed work period data and the calculations are done outside of SQL?
Maybe I should just dive in with the SQL more. I know the system and I know enough to learn how to do the reports I just have not dived in yet. I have been trying to learn through @gerlandog and asking/challenging his findings.
To be honest I love the custom reports…but I think what I am yearning for the most is KPI Dashboard 
SambaPOS uses that technique to retrieve inventory levels when you request an inventory report in the middle of a work period.
- It reads inventory level from latest closed work period. These values are stored in database.
- It calculates inventory changes from purchases and sales made in the actual work period.
- Sums it with previous work period inventory.
What we do on the fly and does not store in database is 2nd. step. So if you don’t need to see inventory in the middle of the work period (in production) you can use custom reports that generates data from PeriodicConsumption table. This will work for some businesses as they plan their purchases out of production time.
As two ideas solves different business needs we can consider both true. The only issue I can see here is lack of inventory calculation function with pure SQL so we can’t prepare a complete inventory solution with custom reports. We read sales, process order tags for possible product connections and add purchases on it. Of course we can’t say “completely impossible with SQL” but it needs a little more processing than just querying.
2 Likes
