idea and help needed

[Obodo]

pls i am looking for the best way to get this done: i want to be able to deal with both retail and wholesales from one spot. in my frontend store, a customer can decide to chose buying just an item or the whole pack. e.g i want to buy noodles, when i type noodles, it will give me an option to buy either 1 or 2 or the whole carton or also i can buy a carton and some pieces. meanwhile any item bought from the front end should be deducted from the quantity in stock (e.g a cartoon of noodle contains 15 pieces and i have 30 cartons of noodle in stock. if a carton and 2 pieces are bought, it should deduct the item i.e 30 cartons minus 1 carton and 2 pieces = 28 cartons and 13 pieces as the remain stock on noodles)

thanks

[requinix]

"Best" is extremely subjective.

What ideas have you come up with so far?

[Psycho]

A potential problem is that such a system could never really "know" how many cartons you have vs. loose items unless the person fulfilling the order explicitly enters the number of cartons vs/ loose items. If that is not important, than I would define each product with a column to identify the "bulk quantity". Store the inventory in the total number of items (regardless of cartons). Then, whenever calculating a transaction just divide by the bulk quantity. The whole divisor would be the number of whole cartons and the remainder would be the individual units.

[Obodo]

46 minutes ago, Psycho said:

A potential problem is that such a system could never really "know" how many cartons you have vs. loose items unless the person fulfilling the order explicitly enters the number of cartons vs/ loose items. If that is not important, than I would define each product with a column to identify the "bulk quantity". Store the inventory in the total number of items (regardless of cartons). Then, whenever calculating a transaction just divide by the bulk quantity. The whole divisor would be the number of whole cartons and the remainder would be the individual units.

My thoughts are almost like yours.

[Obodo]

21 hours ago, requinix said:

"Best" is extremely subjective.

What ideas have you come up with so far?

What I have in mind is to convert all the cartons to pieces. But I envisage a problem of conflict somewhere. I have not been able to put the problem to perspective though. I want to do the thinking and computation first before I start codifying it.

[requinix]

Can you create cartons from individual pieces?

If so then the system only needs to recognize that a "carton" is 15 pieces. Sure you'll display it different for the user, but ultimately it doesn't really matter because the person is getting 15.

If not then you need two inventory items, cartons and pieces, a mechanism that can "break down" a carton into the individual pieces, and a system that will understand that it can do the breakdown (based on arithmetic) and deduct from either/both inventory counts as necessary.

[mac_gyver]

to define what quantities are/are-not available, for each item, you need an item_carton table - id, item_id, quantity, any other useful status/flag columns. there can be cases where there is more than one carton size for an item, i.e. 15, 25, either concurrently or as time passes. there can also be cases where a unit item cannot be bought, only a full carton. you would assume that unit items can be bought, unless overridden by an entry in this table. you would also need a status flag, somewhere, indicating if the available quantity is limited to the stock on hand, i.e. more of the item cannot be obtained. if an item has an entry with this flag set, you could query to get the available quantity and display it on the item add to cart page as the maximum quantity available. for your example, there would be a row with the noodle item_id and quantity 15 to indicate that a carton quantity of 15 is available for that item. the lack of a row indicating that unit items cannot be bought would indicate that unit items are available.

when you search and list items on the add to cart page, you would left join the item table with the item_carton table to determine what can/can-not be bought for each item. for your example, you would list that single items and cartons with quantity 15 can be bought.

you would need an inventory table that would have a row inserted for every transaction that affects the inventory of an item. to handle both carton and unit items, in addition to item_id and quantity columns, you would have an item_carton_id column (an id from the above item_carton table.) if this column contains a value, it indicates the row is for a carton and the quantity is the number of cartons. if this column does not contain a value, the quantity is the number of unit items. for your example, when the 30 cartons were received, a row with the noodle item_id, 30 for the quantity, and the corresponding item_carton_id from the item_carton table would be inserted.

when the cart is finalized and converted to an order, you would insert a row into an order table, with the unique/one time order information,  establishing an order id, and insert row(s) for the carton/unit items into an order_item table. the order_item table would have an item_carton_id column, the same as defined for the inventory table (you will end up doing a UNION query between the two tables to determine the current carton/unit quantity of item(s)).

to determine if a full case needs to be opened and broken into unit items, you would then query to find the current unit quantity for the item id that was just inserted. if it is a negative value, you would then insert a row into the inventory table that deducts enough full case(s) and insert a row into the inventory table that adds that many case quantity of unit items.

for your example, when the order is submitted, you would insert a row for one carton and two unit items into the order_item table. since there are initially no unit items in the inventory, the above logic would get a negative 2, determine that this requires one carton to be broken into unit items, insert a row to deduct one full carton from the inventory, and insert a row with 15 unit items into the inventory table.

Edited November 1, 2021 by mac_gyver

[Obodo]

2 hours ago, mac_gyver said:

to define what quantities are/are-not available, for each item, you need an item_carton table - id, item_id, quantity, any other useful status/flag columns. there can be cases where there is more than one carton size for an item, i.e. 15, 25, either concurrently or as time passes. there can also be cases where a unit item cannot be bought, only a full carton. you would assume that unit items can be bought, unless overridden by an entry in this table. you would also need a status flag, somewhere, indicating if the available quantity is limited to the stock on hand, i.e. more of the item cannot be obtained. if an item has an entry with this flag set, you could query to get the available quantity and display it on the item add to cart page as the maximum quantity available. for your example, there would be a row with the noodle item_id and quantity 15 to indicate that a carton quantity of 15 is available for that item. the lack of a row indicating that unit items cannot be bought would indicate that unit items are available.

when you search and list items on the add to cart page, you would left join the item table with the item_carton table to determine what can/can-not be bought for each item. for your example, you would list that single items and cartons with quantity 15 can be bought.

you would need an inventory table that would have a row inserted for every transaction that affects the inventory of an item. to handle both carton and unit items, in addition to item_id and quantity columns, you would have an item_carton_id column (an id from the above item_carton table.) if this column contains a value, it indicates the row is for a carton and the quantity is the number of cartons. if this column does not contain a value, the quantity is the number of unit items. for your example, when the 30 cartons were received, a row with the noodle item_id, 30 for the quantity, and the corresponding item_carton_id from the item_carton table would be inserted.

when the cart is finalized and converted to an order, you would insert a row into an order table, with the unique/one time order information,  establishing an order id, and insert row(s) for the carton/unit items into an order_item table. the order_item table would have an item_carton_id column, the same as defined for the inventory table (you will end up doing a UNION query between the two tables to determine the current carton/unit quantity of item(s)).

to determine if a full case needs to be opened and broken into unit items, you would then query to find the current unit quantity for the item id that was just inserted. if it is a negative value, you would then insert a row into the inventory table that deducts enough full case(s) and insert a row into the inventory table that adds that many case quantity of unit items.

for your example, when the order is submitted, you would insert a row for one carton and two unit items into the order_item table. since there are initially no unit items in the inventory, the above logic would get a negative 2, determine that this requires one carton to be broken into unit items, insert a row to deduct one full carton from the inventory, and insert a row with 15 unit items into the inventory table.

WOAH! I'm lost here! Will need to read and reread. Thanks

[Psycho]

7 hours ago, mac_gyver said:

to determine if a full case needs to be opened and broken into unit items, you would then query to find the current unit quantity for the item id that was just inserted. if it is a negative value, you would then insert a row into the inventory table that deducts enough full case(s) and insert a row into the inventory table that adds that many case quantity of unit items.

The challenge with programatically determining when cases are broken down or not is that it won't always match reality. A person fulfilling an order may break down a case to fulfill an order for individual units even when there were sufficient quantities of individual units on hand (e.g. one or more individual units may be damaged requiring another case to be opened). If the system must explicitly monitor physical cases vs assuming cases based on total quantity divided by case size, then the fulfillment process should be included in the application workflow where the person fulfilling the order confirms the programatically determined quantities of cases\single units or can edit based on what they actually execute. Otherwise, the actual inventory and the application inventory will get out of alignment.

The OP needs to determine what should happen in a situation such as: There are no physical cases in the actual inventory, but there are 20 loose units (a case holds 15). Should the system allow a user to purchase a case and the fulfillment would simply send 15 loose units or can a user only purchase a case when there is an actual physical unopened case?

[ginerjm]

It seems from this discussion that the problem is not the coding of the app, but the handling of the inventory.  You need to address that issur with those in control of that facet. Without that your app would appear to be a terrible expense despite the best effort you put into it.  If those individuals cannot promise you a sound inventory number for your app to rely on, I'd back away rather than be blamed for the future entanglements you will appear to be responsible for.

[Obodo]

15 hours ago, ginerjm said:

It seems from this discussion that the problem is not the coding of the app, but the handling of the inventory.  You need to address that issur with those in control of that facet. Without that your app would appear to be a terrible expense despite the best effort you put into it.  If those individuals cannot promise you a sound inventory number for your app to rely on, I'd back away rather than be blamed for the future entanglements you will appear to be responsible for.

Exactly! Inventory is the main headache

[ginerjm]

Inventory CONTROL is the headache.  Not the app's problem since if control is managed then your app simply has to do and record the math. 

So - how is your organization going to manage that?  Setting rules on how the people in the warehouse(?) gather items for shipping, how they open cases up when they don't have to, how they properly record what they actually do package for shipping.  The two ends of this transaction: (a) your ordering system (that relies on proper inventory counts) and (b) the shipping/delivery system (that performs proper recording of its activity) must always agree or your app will appear to be at fault. 

[Obodo]

On 11/1/2021 at 9:07 AM, mac_gyver said:

to define what quantities are/are-not available, for each item, you need an item_carton table - id, item_id, quantity, any other useful status/flag columns. there can be cases where there is more than one carton size for an item, i.e. 15, 25, either concurrently or as time passes. there can also be cases where a unit item cannot be bought, only a full carton. you would assume that unit items can be bought, unless overridden by an entry in this table. you would also need a status flag, somewhere, indicating if the available quantity is limited to the stock on hand, i.e. more of the item cannot be obtained. if an item has an entry with this flag set, you could query to get the available quantity and display it on the item add to cart page as the maximum quantity available. for your example, there would be a row with the noodle item_id and quantity 15 to indicate that a carton quantity of 15 is available for that item. the lack of a row indicating that unit items cannot be bought would indicate that unit items are available.

when you search and list items on the add to cart page, you would left join the item table with the item_carton table to determine what can/can-not be bought for each item. for your example, you would list that single items and cartons with quantity 15 can be bought.

you would need an inventory table that would have a row inserted for every transaction that affects the inventory of an item. to handle both carton and unit items, in addition to item_id and quantity columns, you would have an item_carton_id column (an id from the above item_carton table.) if this column contains a value, it indicates the row is for a carton and the quantity is the number of cartons. if this column does not contain a value, the quantity is the number of unit items. for your example, when the 30 cartons were received, a row with the noodle item_id, 30 for the quantity, and the corresponding item_carton_id from the item_carton table would be inserted.

when the cart is finalized and converted to an order, you would insert a row into an order table, with the unique/one time order information,  establishing an order id, and insert row(s) for the carton/unit items into an order_item table. the order_item table would have an item_carton_id column, the same as defined for the inventory table (you will end up doing a UNION query between the two tables to determine the current carton/unit quantity of item(s)).

to determine if a full case needs to be opened and broken into unit items, you would then query to find the current unit quantity for the item id that was just inserted. if it is a negative value, you would then insert a row into the inventory table that deducts enough full case(s) and insert a row into the inventory table that adds that many case quantity of unit items.

for your example, when the order is submitted, you would insert a row for one carton and two unit items into the order_item table. since there are initially no unit items in the inventory, the above logic would get a negative 2, determine that this requires one carton to be broken into unit items, insert a row to deduct one full carton from the inventory, and insert a row with 15 unit items into the inventory table.

WOAH! I'm lost here! Will need to read and reread. Thanks

[Obodo]

I'm thinking aloud. Is there a way I could use a select to get 2 values from the database? What I mean is I will create a select field where when I select wholesale it will bring out the price per carton and the quantity available and if I select retail, it will bring the price per unit and the quantity available for the item. The values will be from the database of course

I'm thinking jquery will be able to do such.

[gizmola]

Yes certainly.  You use ajax.  Ideally you have a rest api created that supports this.  

You would create your server api that takes the request parameters and returns the appropriate data.  This is a generalized concept, but in your case, clearly the parameter is "buyertype" with either "retail" or "wholesale".  Based on that, the rest api will return the price per item or price per carton, as well as the appropriate quantity available.

You can use jquery to make these calls, or use the more modern technique of using fetch with ES6 promises.  Be aware that jquery has a lot of wrappers around ajax that you can use, like jquery.get(), jquery.post() and jquery.getJSON().

• Jquery Ajax
• Javascript Fetch

My advice if you are doing new code that isn't heavily invested in jquery, is to use fetch().  In all cases return and work with JSON if you can.  It's a much better match for javascript UI.  It's ok, to use post or get methods to send the data, but return your results in JSON.

 

[gizmola]

Some things in general that are unusual about your system and design:

• Usually a carton of items vs single items have different inventory numbers.
  • The inventory system might have a BOM feature that reflects the Carton y contains some # of inventory item x.  Thus inventory of item x would always be ((#y * quantity/per/carton) + quantity of x (loose/individual boxes)).   You may or may not want to break open a carton in order to sell some# of individual item x, but that is a business rule.  Otherwise, the items are separate for the point of inventory and sales.
• Usually a buyer is getting a price based on their status as a wholesale buyer vs. a direct-to-consumer.  It's an odd design to pick from one or the other, because wholesale buyers are typically buying in quantity and getting a deal. 

I'm not sure what you are trying to achieve here, but often upsell can be achieved in a different manner, via messaging the customer to incent them to buy more to receive discounts of some sort.  Without understand the functional design/use cases/user stories, it's not possible to suggest specific design solutions.