query

Hello everyone there is this problem i am facing. i have the total cost of a product i am trying to calculate. The product has the same Product_ID but different Stock_ID. I explain. Let say the product is PENCIL, i have two stocks where i bought the first 5pencils at a rate of 100 and the remaining 5pencils at a rate of 120. Now i want to sell out 6pencils at the rate at which i bought each pencils( let say it is a non profit org.). So the first five pencils will cost 500 and the 6th pencil 120(using FIFO or the reverse for LIFO) so that we can have as total 620. what my code does is it uses the rate(100) of the first stock to calculate all which is not what i want please can someone guide me on how to go about this? i have been on this for two weeks now

kfirba

8 years ago

Level 50

@pafait Hey.

We can't really help you without seeing some code. Where are you saving the stock cost? Is it something you need to figure out on the query level (database) or some kind of data manipulation after you retrieve some data from the database?

Share some code so we can understand the context of the question.

pafait

8 years ago

Level 1

Hi i save them in my stock table. that is when entering a new stock i enter its stock_cost to. When i want to carry on a sell i query the stock_table to get the product's rate to calculate the total.here is the code

$sell->category_id = $category_id= $request->category_id;
    $sell->brand_id = $brand_id = $request->brand_id;
    $sell->product_id = $product_id = $request->product_id;
    $sell->buyer_id = $request->buyer_id;
    $sell->stock_id = $stock=$this->getproductstock($product_id);
    $sell->sell_quantity = $qty = $request->sell_quantity;
    $sell->sell_rate = $rate =  $this->check_rate($product_id,$this-
>getproductstock($product_id));
    $sell->sell_total_price = $qty * $this->check_rate($product_id,$this-
>getproductstock($product_id));

and here are my queries

private function check_rate($product_id) {
$stock_rate =DB:: table('tbl_stock')
      ->where('product_id', $product_id)
      ->first();
return $stock_rate->stock_rate;
}

 private function getproductstock($product_id) {
$stock_rate =DB:: table('tbl_stock')
      ->where('product_id', $product_id)
      ->first();
return $stock_rate->stock_id;
}

Please or to participate in this conversation.