I am currently working on a project relating to a stock management system on laravel 5.6. Basically I have 3 tables in a MySQL database that I will link to my program; parts, order and receive
Parts table looks like
id | brand | model | partcode | partname
1 | Apple | Iphone X | ixmainpcb | PCB
2 | Apple | Iphone X | ixLCD | Main PCB
3 | Apple | Iphone X | ixcamera | PCB
Order table looks like
id |part_id | orderno | orderqty | freeqty | purchaseqty | price | totalqty
1 | 1 | O18001 | 1000 | 50 | 950 | 9500 | 1000
2 | 2 | O18002 | 1500 | 100 | 1400 | 28000 | 1500
3 | 3 | O18003 | 2000 | 100 | 1900 | 38000 | 2000
we have to pay price for the purchaseqty only no need to pay for the freeqty. freeqty is the scheme quantity. price is the total price amount.
Receive looks like
id | order_id |fresh | defect | short | excess | Stock | totalreceive
1 | 1 | 600 | 100 | 300 | 0 | 5 | 700
2 | 2 | 400 | 50 | 1050 | 0 | 130 | 450
3 | 1 | 300 | 0 | 0 | 0 | 300 | 300
At first shipment order_id 1 product is received 700 pieces. It was sold to the customer and now in stock I have only 5 pieces left. At the second shipment my order_id 1 shipment has been completed by receiving the 300 pieces, so defect and short would be 0 in table and stock field will be 300 for third row.
While selling the actual problem arises.
What can I do in the following cases.
Case 1:
Suppose the customer want to buy 50 pieces product which have order_id 1. Then when I sell it the 5 should be deducted from first row and remaining 45 should be deducted from third row so that my new receive table would look like
id | order_id |fresh | defect | short | excess | Stock | totalreceive
1 | 1 | 600 | 100 | 300 | 0 | 0 | 700
2 | 2 | 400 | 50 | 1050 | 0 | 130 | 450
3 | 1 | 300 | 0 | 0 | 0 | 255 | 300
Case 2:
Suppose the customer want to buy 5 (or less than 5) pieces product which have order_id 1. Then when I sell it from the first row so that my new receive table would look like
id | order_id |fresh | defect | short | excess | Stock | totalreceive
1 | 1 | 600 | 100 | 300 | 0 | 0 | 700
2 | 2 | 400 | 50 | 1050 | 0 | 130 | 450
3 | 1 | 300 | 0 | 0 | 0 | 300 | 300
What is the best way\logic to handle these cases. Thanks in advance.
Here is the code of what I have done to store the selling data in database. I don't know whether this logic is correct or not. Any help would be appreciated.
I have passed this value from view using ajax to controller.
$deps = Input::only('id', 'vpoid', 'issueqty', 'vpostock', 'amount', 'discount', 'vat', 'netamount', 'status', 'remarks');
$id =$deps['id'];
$vpoid = $deps['vpoid'];
$issueqty = $deps['issueqty'];
$vpostock = $deps['vpostock'];
$amount = $deps['amount'];
$discount = $deps['discount'];
$vat = $deps['vat'];
$netamount = $deps['netamount'];
$status = $deps['status'];
$remarks = $deps['remarks'];
$newstock = 0;
foreach ($vpoid as $key => $n) {
//For Updating the stock in receive table
$chk = VpoUpload::where("id", "=", $vpoid[$key])->get();
$newstock = ((int)$chk[0]->stockqty) - $issueqty[$key];
DB::table('vpo')->where("id", "=", $vpoid[$key])->update(array(
'stockqty' => $newstock,
)
);
}
for ($i=0; $i < count($receiveqty); $i++) {
$data[] =[
'receiveqty' =>$issueqty[$i],
'amount' => $amount[$i],
'status' => $status[$i],
'remarks' => $remarks[$i],
];
}
$vpouploads = VpoSell::insert($data);
