Level 122
i will have a think
Jun 28, 2019
5
Level 5
multiply product qty with rate with maximum rate in the list in relationship
guys i have a issue in multiplying the product quantity picked from sales table and multiply it with rate from purchase table.
i have purchases tables with column
id
product_id
date
qty
rate_per_kg
and this is my sales table with columns
sold_date
product_id
qty
but i dont have any relationship between sales and purchases table.
but both purchases and sales table have relationship with products table.
this is my model for Purchase
public function product()
{
return $this->belongsTo('App\Product', 'product_id');
}
this is my Sales model
public function product()
{
return $this->belongsTo('App\Product', 'product_id');
}
this is my Product model
public function purchase()
{
return $this->hasMany('App\Purchase', 'product_id');
}
public function sale()
{
return $this->hasMany('App\Sales', 'product_id');
}
my product table entry
id product_name
01 Onion
my purchase entry
id product_id date qty rate_per_kg
01 01 31-05-2019 50 32
whats my doubt is, when i enter a sales entry, like this sales entry
sold_date product_id qty
1-06-2019 01 30
1-06-2019 01 10
2-06-2019 01 5
i should get a report like when i search from date:1-06-2019 to date:2-06-2019
when i give the from and to date it must fetch records from sales table by grouping the product name total the quantity and then fetch the max(rate_per_kg) that is fetch the maximum rate_per_kg of that specific quantity for the given from and to date from purchase table and multiply it with the quantity in sales table and display the records as report as give below format.
report
productName sold rate_per_kg totalAmt
Onion 45 32 1440
How to perform this??
Kindly some one help please
Level 22
I think the overall idea is a bit flawed here, what should happen when you have purchased 25kg for 1$ and after that you made a bigger purchase of 50kg for $0.8 and a customer comes along and purchases 55kg of that product?
Then you would sell him 25kg for $1 and 30kg for $0.8 based on the cost price totaling $49 making it $0.89 per kg. Just taking the maximum price would give you $55 a huge (unacceptable) difference in my opinion if this is going to be used for a real product in production.
But to answer your question;
If you would really like to do this by using the models then I can write that out for you, but personally I always tuck away queries for reports in their own classes making it easier to work with as the reports stack up, plus I make sure I don't dirty my models with possible reporting stuff and on top of that its usually much faster.
As an example;
<?php
namespace App\Queries\Reporting\Sales;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
class TotalsOverCustomPeriod
{
public static function query(Carbon $from, Carbon $till)
{
return DB::table('sales')
->join('products', 'products.id', '=', 'sales.product_id')
->join('purchases', 'purchases.product_id', '=','products.id')
->whereBetween('sales.sold_date', [$from, $till])
->groupBy('products.id')
->select([
'products.name',
DB::raw('SUM(sales.quantity) AS total_quantity_sold'),
DB::raw('MAX(purchases.price) AS highest_purchase_price'),
DB::raw('SUM(sales.quantity)*MAX(purchases.price) AS total_price'),
]);
}
}
And use it like so;
$from = (new \Carbon\Carbon)->subWeek(1);
$till = new \Carbon\Carbon();
$report = \App\Queries\Reporting\Sales\TotalsOverCustomPeriod::query($from, $till)->get();
Level 5
@dirkzz thank you for your response. sorry for the delay.
actually your question is right if there is two rates. what i need to do is to pick the latest rate thats it.
Kindly if possible share some code. iam so confused. plz.
this is related link for that,
and if possible, plz can you guess this also
Level 122
Answered in other thread
Level 122
Assuming that more recent prices have a higher ID
The following eloquent query gets the most recent price
You could use this result set as a collection from which you can lookup the price when you want to multiply with quantity.
$latestPrices = ProductRate::whereIn('id',
DB::table('product_rate')
->selectRaw('max(id) as id')
->groupBy('product_id')
->pluck('id')
)->get();
Please or to participate in this conversation.