Report stock on a specific (past) day

[nwns]

Is there a way to create find out what was in inventory on a specific date? This is in context of doing the year end bookkeeping, trying to find out what was in stock on December 31st. Yes, I know it is now July and had this ask been made sooner I could have just downloaded a backup of the database and use the stock take system to generate the report, but alas our database backups are not kept this long.

I see there is a reporting function in Inventree, but I am not sure that I have ever got it to work. The documentation suggests that there are Stocktake report types but I don't see it in my 0.7 installation.

Is the information required to accomplish this report in the database (I assume so because the stock items stay around after being sold or counted out, but I thought I would ask those more familiar with Inventree before digging into the code)?

Thanks

[matmair]

@nwns this is generally possible with the current version - I am not sure how long all the stuff was there and when you made the according updates.
My suggestion would be to just filter for the stocktracking entries before your cutoff day, ordering by date and just getting the latest entry. The entry contains the quantity at time of creation.

This can be done with the API, with a plugin or just debugging your instance.
This is only appropriate for more advanced users. I am positive @nwns can do it, this is more a warning for new users without experience with django and/or inventree.

[nwns]

Thank you for the help.

I was concerned about the delete on depleted, but I have many stock items with 0 quantity so I don't know if that is an issue here.

As for the splits, I guess we will see what the accountant comes back with. It is an answer. Another possibility that came up on this end was to do something with the purchase orders and sales orders and work our way backward from today.

So for now, my solution was to login use the ./manage.py shell and write the following tilde separated file (because of course there are commas in the part names).

t = {t.item: t for t in StockItemTracking.objects.filter(date__lt='2022-01-01 00:00-0000')}
for item, tracking in t.items():
    _ = f.write(f"{item.part.full_name}~{tracking.deltas['quantity'] if tracking.deltas and 'quantity' in tracking.deltas else item.quantity}~{item.purchase_price}~{item.purchase_order}~{item.updated}~{item.batch}\n")

Thanks,

[nwns]

Seems my query was missing a few things. Need to order by date to make sure I get the most recent items. StockItemTracking does not have a native sort. Also, stock items that have a customer assigned have been shipped to customer and should not be included as instock.

t = {t.item: t for t in StockItemTracking.objects.filter(date__lt='2022-01-01 00:00-0000').order_by("date")}
for item, tracking in t.items():
  if not item.customer_id:
    _ = f.write(f"{item.part.full_name}\t{tracking.deltas['quantity'] if tracking.deltas and 'quantity' in tracking.deltas else item.quantity}\t{item.purchase_price}\t{item.purchase_order}\t{item.updated}\t{item.batch}\n")

[matmair]

@nwns non of our models have a native sort when called in the shell. I would advise to always add that to requests.
A possible logic problem with your requests: The item could have been in inventory at new-years and been sold later with that check. I would check for that too.

[nwns]

@matmair that is a fair point. Accountant seems happy with these numbers so, I think I am going to leave it and make sure the problem is solved going forward by seeing what I need to do generate a stock report to email out at the start of every month.

[matmair]

@nws my suggestion for that would be to use a scheduled task. Maybe flash out what would be needed for inputs / outputs etc. and create a FR - sounds like a nice feature. Should be rather easy to implement if you want to take a crack at it - I would also be willing to spend some time on it.