calculation of units in dozen

Question

there are some Product whose quantity vary from one product to other in one carton
like in the quantity of three products in one carton given below

SKU     QUANTIT_SPECIFICATION (IN ONE CARTON)
4545     7 DOZENS
3455     9 DOZENS
7856     2 DOZENS

GIVEN BELOW THE INTERFACE THROUGH STOCK KEEPER WILL ENTER STOCK QUANTITY

SKU      NAME            U1 - U2 - U3      
4545     DISPRINE         2 -  3 - 4
3455     strepsils        0 -  4 - 5
7856     Detoll           9 -  4 - 0

WHERE

u1 is the number of carton 
u2 is the number of dozens
u3 is the number of pisces

note user can enter quantity in any format
for example user enter quantity for 4545 like this

SKU      NAME            U1 - U2 - U3      
4545     DISPRINE         0 -  0 - 84

or user can enter quantiy as

SKU      NAME            U1 - U2 - U3      
4545     DISPRINE         0 -  7 - 0

or user can enter as

SKU      NAME            U1 - U2 - U3      
4545     DISPRINE         1 -  0 - 0

in all cases user want to enter the one carton of 4545 BUTT when audit officer wants to audit he just generate report of how much stock available the report must follow same quantity format like u1 u2 u3 but in report order always follow from u1 to u3

in stock available items of 4545 sku are 99 then report must be like this

SKU      NAME            U1 - U2 - U3      
4545     DISPRINE         1 -  1 - 3

HOW TO IMPLEMENT THIS IN DATABASE

is in sku table specification of how much dozens a carton can contain mention like this

sku(id, name , quantity_specification)

so that when stock keeper enter quantity software logic should be like this

first get the specification then match it with u1 u2 u3 multiply/ subtract and commit it to warehouse table 

any other shortcut to implement this

give mechanism for this

Answer 1

Quantity Calculation

You already have a carton specification table that lists how many dozens make up a carton.

You know that a dozen is 12 items.

The mechanism is pretty straightforward.

Carton Specification Table

Let's define your carton specification table.

Carton Specification
--------------------
Carton Specification ID
SKU
Carton quantity (in dozens)

Stock Quantity Table

Let's assume we have a stock quantity table like this.

Stock Quantity
--------------
Stock Quantity ID
SKU
Quantity (in units)

Calculations

From one of your examples, we read a row in the stock quantity table for SKU 4545, and we get the quantity. 99 units.

Now, we read the row of your carton specification table for SKU 4545, and we get the carton quantity. 7 dozen. (7 * 12) = 84. Therefore, we have 84 units in a carton.

Divide 84 into 99 (99 / 84). The answer is 1. We have one carton of units.

Now, multiply 1 times 84 and subtract that amount from 99 (99 - (84 * 1)). The answer is 15.

Divide 12 into 15 (15 / 12). The answer is 1. We have one dozen units.

Nove, multiply 1 times 12 and subtract that amount from 15 (15 - (12 * 1)). The answer is 3.

Therefore, we have 1 carton, 1 dozen, and 3 units of SKU 4545, which is what you would display.