Stock-take feature

[sde1000]

A stock-take is a way to record waste across a number of different stock items or stock types in an atomic fashion. We want to take a snapshot of the state at the start of the stock-take, build up a list of record-waste, finish-item and add-item actions, and apply them all in one go. (Or throw them away if we abandon the stock-take.)

A stock-take has a "scope": the set of types of item that we want to check. How should this scope be defined? At the moment, "department" seems to be the obvious selector. Will we ever need others? Eg. "all stock from Milton" or "everything that has been on sale on a particular stock line"? The danger here is in conflating Department as an accounting-level feature (used to split stock into purchase and sales accounts) with Department as a stock-organisation feature.

Do we want to store the scope in the database along with the stock-take? What would we use it for? Deciding whether a stock addition ("discovered stock") is valid? Could be useful. How would we store it? A list of stock types? Could this get large, quickly? (A few thousand stock types; one copy per stock take. But purge that copy when the stock take is completed?)

Let's say we store the scope as a set of departments, for now. Add an accessor method to the stock-take to retrieve the list (or filter another query by the list, probably more usefully), and that makes it easier to change it in the future.

What about whether we stock-take by items or by quantities? Where do we store that? Does it ever make sense to include both methods in a single stock-take? How would we split them up — separate scopes for each method? What if the scopes overlapped? (Main example in our data: snacks — some are on "regular" stock lines and others on "display" or "continuous".) I don't think we can go with different scopes, they will have to be combined.

Ok, having different methods in the database sounds like a bad idea. Let's say that everything is done as stock-take-by-item, and we add some user-interface on top of that to perform stock-take-by-quantity. (Eg. show a list of stocktypes in the UI, with 'expand' toggles to additionally show items.) Does this work? If the total for a stocktype is edited, we can re-write the totals for the underlying items. If the total for an item is edited, we can re-write the total for the stocktype. (Or just change what is reported by the server, calculated from the underlying items.)

Actually, "all stock-types" stored in the database for the stock-take scope sounds like a better idea now: it gives us somewhere to hang our intermediate flags, like whether a particular type is shown expanded.

So stock-take procedure looks like:

• create stock-take table entry

• gradually add stock-types to scope, with flags set per stock-type to show default "expand to items" state. Keep a log of what was added. (We're going to need a stock-take log table? Or should we introduce a general log table now? Perhaps consider the stock-take log like stock item annotations: if a stock item goes away, so should the annotations.)

• start the stock-take: for all stock types in scope, create snapshot of items.

• user adds adjustments: record-waste to reduce or increase levels in particular items with a reason, finish-item to finish an item with a reason, or new-item to create a previously unknown stock item. We're going to need a table of adjustments. Unique by item/type/reason. (Can we enforce absence of other adjustments when finish-item is present? This may be possible with an EXCLUDE clause in the DDL.) new-item should create a stock table entry immediately, which will be unavailable for use until the stock-take is committed.

• when happy, commit the stock-take. Adjustments turn into stockout records or finishing an item with the supplied finish-code. New items are automatically available for use when their linked stock-take is finished.

• if not happy, abandon the stock-take. All related records are deleted, including new items from the stock table.

[sde1000]

Initial version in be48851991a5258eda6f6c894123b46ec90c1b2c

[sde1000]

Some UI tweaks needed:

• Now that adjustments are entered as "adjust total amount to" rather than "adjust by", the entry box can be merged with the qty column and can be pre-filled with the current amount.
• The user is almost always going to want to choose "Gone missing" as the adjustment reason, so maybe hide that column?
• A "revert" button would occasionally be useful. Perhaps hide this button and the adjustment reason drop-down in a collapse element, triggered by a small "More..." button? The detail of the adjustment could be here too.
• I already had to make the check-boxes larger because they are tricky to hit on a touchscreen. Perhaps even more?
• (Possible database change) Entering wine in Bottles instead of ml is fine, but entering soft drink cartons in Pints instead of Litres is silly. Perhaps as well as having sale/pricing quantities in the Unit, have stock-keeping quantities too?

[sde1000]

More tweaks!

• Have a button to collapse the per-stock-type quantities and adjustments when editing or viewing a stock-take; they can get in the way if you're only interested in adjustments per stock item.
• Having a "Name this stock-take" page before creating the stock-take instance would be a better experience than jumping in there with all options available straight away. Usual error here is entering the stock-take name and then forgetting to hit "Update" before moving on to selecting stock types for the scope.

[sde1000]

"Name this stock-take" page added in c0a8d9a54083bc67f94012561e1c5b62fe597fc9

[sde1000]

Stock-keeping quantities added to Unit in 0445ce6e2e5c8aca427f95b1e933f8af5de4d86c