Simple Stock Consumption

I want to have a simple report at the end of the day, where i want to see the stock consumed in the specified period. How do i go about constructing that? I want something similar to what’s currently displayed in the warehouse.

If you take a look at this:

you will se that there is all you need to get that info, just modifying a little that SQL will give you the report you are asking for!!!

G.

Thanks. I’ve copied and pasted the the text in the template but its showing a different result as to the one shown in your thread. I’ll read more on how to use the custom reporting feature and then will hopefully get there.

That report does not show stock consumed @gerlandog

I do not think that data is accessible it is calculated on the fly and not stored unless I am understanding it wrong…

Guys!!!, please read the post…

@paxi sayed AT THE END OF THE DAY (so that means that if he closes the work period, the data IS THERE)
and I wrote just modifying a little that SQL will give you the report you are asking for!!!

The data is the column [Consumption] but I dont show it directly, in my SQL its participating in a sum… just put it alone and you get what you want…

Thanks!!!

G.

Sorry, perhaps i’m a n00b, but in your tutorials its not clear where the “Report Template” is and where the script is.

I asume you have installed the CUSTOM REPORT MODULE. If so, this would be a good place to start:

G.

Yes i have sir! slowly been figuring it out and playing around with your scripts … i think i’ll get somewhere in an hour or two :smile:

hmmm … it’s just showing the same stock consumption regardless of the period selected … i think the stock consumption that it is showing is based on the current period … how do i go about fixing it?

Here, you will have to work with this part of the SQL

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] 

Hope this helps!!

G.

hmmm … i’ll have to enter dates in there somewhere? like this into the equation below:

So this: === ‘{Start}’ and Date < ‘{End}’

INTO

This == WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions]

all the info we can retrieve by SQL is within work periods, so, the SQL y passed to you gets the STOCK from within the LAST work peridod. If you want to retrieve stock from another work period you should change that SELECT in the WHERE clause.

In the table [PeriodicConsumptions] you have ALL the work periods.

So my guess is that if you want something form a determinate date you should JOIN the table with [PeriodicConsumptions] filtered by date, and then retrive the data form [PeriodicconsumptionItems].

G.

1 Like

Sorry @gerlandog my sql knowledge is definitely lacking :stuck_out_tongue: I have learned a lot from you lately. I was confusing the real time consumption with @period close. So apparently the @period close info is available but real time is not?

Real time info should be available too, but not as simple as retrieving [Consumption] column from a table. I think, and thats from what I learned in the last months from @Emre, that if we use the formula ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]) from the LAST WORK PERIOD and to that we add and substract the orders and canceled orders from the CURRENT WORK PERIOD we may have what I call ON THE FLY STOCK.

What do you think @Emre, am I going to achieve the impossible? LOL!!!

G.

So the orders contain all of the products and the correct portions etc? Or are you tagging that info?

They should, otherwise how in hell SAMBAPOS knows what to charge in tickets, to which table at what time, which was canceled…? LOL!!!

as a matter of fact, SmabaPOS´s INVENTORY REPORT does show you real time stock…

G.

Well it does not always list all products into a menu item… I have several products that do not appear in an order but are understood part of the recipe. Hamburger for example… the bun does not appear in the order… but its part of recipe and is tracked by the on the fly calculation.

In order to have a stock, you must have receipes and recipes are related to products wich are in the menu. If one item can be meassured on the fly why would the others not? so my educated guess is yes, all the recipes should…

G.

I guess it depends on what exactly is included in an Order and when the inventory for a recipe is calculated… if its calculated with the Order or if its a separate calculation we do not see. Wont hurt to try! I will do some digging around in the database and see what i can find.

Im not seeing any inventory attached to Orders… ill keep looking… actualy there it is… your right it might work. EDIT: WAIT nm that was just order tags

I looked at all fields for Orders and there are no inventory products linked.

I cannot find any evidence that inventory is stored until finalized work period. Orders does not contain inventory. I am still looking to make sure I didnt miss something. I am almost certain I did not miss anything.

I went through just about every Table that had anything to do with inventory… and there is no inventory movement stored anywhere until close of work period. So it makes sense to me that inside work period inventory is calculated within Samba and not SQL.

I would love a report that shows this. It just seems almost impossible. I will not say for sure because you might just find a way… your good at that.