multiply the qty and rate from two tables with same product_id in laravel

@snapey please do u have any idea

What if date range is 20-7-19 to 24-7-19? Now you have two prices? Which do you multiply the stock by?

@snapey thank you for your response.

the latest date rate must be taken that is when from_date and to_date is given then within the given date, the latest inserted records rate must be taken and it should be multiplied

@snapey

the latest rate must be taken. so that the multiplication can be done.

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();

@snapey thank you for the response.

with the query which you gave i can get the latest rate record from the db.

but the process of multiplication etc where should i do?? whether i can perform in my controller itself or in my blade file i need to do that??

the qty must be picked for specific product from open_stock table. the rate for the same product with latest rate must be picked from ProductRate table. both should be multiplied. how??

Kindly reply for this also please.

iam not familiar with joins. so getting confused

You can use the result collection as a lookup table.

eg, in blade

<td>{{ $product->id }}</td>
<td>{{ $product->name }} </td>
<td>{{ $product->openingstock->open_qty }}</td>
<td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg }}</td>
<td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg * $product->openingstock->open_qty  }} </td>

@snapey iam trying this.

can you suggest any idea for my another thread

https://laracasts.com/discuss/channels/laravel/display-attendance-report-in-laravel-blade-file

please.

@snapey thank you for your response.

i tired like this but showing error

in my controller

$latestPrices = ProductRate::where ('hotel_id',Auth::user()->hotel_id)->whereBetween('d_date', [$request->from_date, $request->to_date])->whereIn('id',
        DB::table('product_rate')
            ->selectRaw('max(id) as id')
            ->groupBy('product_id')
            ->pluck('id')
        )->get();

and in my blade file

<td>{{ $product->id }}</td>
<td>{{ $product->name }} </td>
<td>{{ $product->openstock->open_qty }}</td>
<td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg }}</td>
<td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg * $product->openingstock->open_qty  }} </td>

the error is

Property [open_qty] does not exist on this collection instance. (View: C:\xampp\htdocs\Laravel\hotel_sri_ram_prasad\resources\views\Stock\manage-opening-stock.blade.php)

actually my relation between stock and product table is

//in my product model

 public function openstock()
    {
        return $this->hasMany('App\Stock', 'product_id');
    }


// in my stock model

public function product()
    {
        return $this->belongsTo('App\Product', 'product_id');
    }

i tired forloop like below

 @foreach($latestprices as $prices)
<tr>
    <td>{{ $product->id }}</td>
    <td>{{ $product->name }} </td>
    <td>{{ $product->openstock->open_qty }}</td>
    <td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg }}</td>
    <td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg * $product->openingstock->open_qty  }} </td>
</tr>

@endforeach

the same error occurs

and when i comment <td>{{ $product->openstock->open_qty }}</td> then the error is

Call to undefined method Illuminate\Database\Query\Builder::firstWhere() (View: C:\xampp\htdocs\Laravel\hotel_sri_ram_prasad\resources\views\Stock\manage-opening-stock.blade.php)

   <td>{{ $product->id }}</td>
   <td>{{ $product->product_name }} </td>

when i use just the above two lines, only 357 th id record is displayed , 357 Garlic like this only displayed many times

Whats the problem kindly suggest please

why do you have a foreach loop on latestprices?

You should be looping over your products

@snapey but

this is my exact query,

in my controller

$latestPrices = ProductRate::where ('hotel_id',Auth::user()->hotel_id)->whereBetween('d_date', [$request->from_date, $request->to_date])->whereIn('id',
        DB::table('product_rate')
            ->selectRaw('max(id) as id')
            ->groupBy('product_id')
            ->pluck('id')
        )->get();


    return view('Stock.manage-opening-stock')->withLatestprices($latestPrices);

in my blade

@foreach($products as $product)
<tr>
    <td>{{ $product->id }}</td>
    <td>{{ $product->name }} </td>
    <td>{{ $product->openstock->open_qty }}</td>
    <td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg }}</td>
    <td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg * $product->openingstock->open_qty  }} </td>
</tr>

@endforeach

this shows error Undefined variable: product actually latestprice is the variable passing from controller. then how i can loop product.

i cant understand.

with what should i make loop?? why??

no...

to give this table

s.no        productName     open_stock      rate/kg     total
1           tomato              100             11          1100
2           potato              250             35          8750

Total                                                       9850

you must get all products, with the stock numbers and loop over them.

You also get the latest prices as a separate collection

You then multiply the item by the price within the view

So you need to pass two collections to the view. 1. Products (with stock) and 2. latest prices

(and remove the date range filter from the latest prices - these are the latest prices. They should not be constrained by the date)

@snapey

then do i need to write two foreach in blade file

now i have changed y code like this

in my controller

$latestPrices = ProductRate::where ('hotel_id',Auth::user()->hotel_id)->whereBetween('d_date', [$request->from_date, $request->to_date])->whereIn('id',
        DB::table('product_rate')
            ->selectRaw('max(id) as id')
            ->groupBy('product_id')
            ->pluck('id')
        )->get();


 $products=OpeningStock::where ('hotel_id',Auth::user()->hotel_id)->get();

    return view('Stock.manage-opening-stock')->withLatestprices($latestPrices)->withProducts($products);

in my blade how to loop through.

@foreach($products as $product)
<tr>
    <td>{{ $product->id }}</td>
    <td>{{ $product->name }} </td>
    <td>{{ $product->openstock->open_qty }}</td>
    <td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg }}</td>
    <td>{{ $prices->firstWhere('product_id',$product->id)->rate_per_kg * $product->openingstock->open_qty  }} </td>
</tr>
@endforeach

showing error Undefined variable: prices (View:

where should i use $price

actually i cant understand where to use these variables.

and why i have given date range is because, the purchase is done daily. so the rate varies daily. so with given date the latest rate of the product must be picked and should be multiplied.

for July month if we need to check the stock rate means so i have given date range

Sorry, change view to use $latestPrices not $prices

come on.... think!

This should be easy to follow.

@snapey

ahh snapey very sorry. actually i confused whether need to pass Latestprice in loop and that in array u have given as prices, i thought like that. very sorry. very very sorry.

another big problem is

now everthing is fine but showing error in Method firstWhere does not exist. (View:

when i checked in google mine

version is Laravel Framework 5.4.36

but The firstWhere method was added in Laravel 5.5.23:

now what to do?? can i upgrade it or is there any solution??

and kindly say that howyou guys knowing all these functions in laravel. almost 1 and half years over for me and faced 4 projects alone. still cant learn laravel. how you guys knowing about all the functions and joins etc??

is there any specific materials to learn.

ouch. Why such an old version?

@snapey

that without my knowledge started the work. at that time i too dont know what to do so.

@snapey

is there any solution for this?? plz

@snapey ???

try

    <td>{{ $latestPrices->where('product_id',$product->id)->first()->rate_per_kg }}</td>
    <td>{{ $latestPrices->where('product_id',$product->id)->first()->rate_per_kg * $product->openingstock->open_qty  }} </td>

@snapey

i tried like this

actually

my controller

// this collection contains the data of OpeningStock tables (id,open_date,product_id,open_qty)

 $products=OpeningStock::where ('hotel_id',Auth::user()->hotel_id)->get(); 

and this
// has the collection details of ProductRate  table
$latestPrices = ProductRate::where ('hotel_id',Auth::user()->hotel_id)->whereBetween('d_date', [$request->from_date, $request->to_date])->whereIn('id',
        DB::table('product_rate')
            ->selectRaw('max(id) as id')
            ->groupBy('product_id')
            ->pluck('id')
        )->get();

in my view i have given

   @php ($s_no = 1)
  @foreach($products as $product)
  <tr>
      <td>{{ $s_no }}</td>
      <td>{{ $product->open_date }}</td>
      <td>{{ $product->id }}</td>
      <td>{{ $product->product->product_name }} </td>

      <td>{{ $product->open_qty }}</td>
      <td>{{ $latestprices->where('product_id',$product->id)->first()->rate_per_kg }}
      </td>
      <td>{{ $latestprices->where('product_id',$product->id)->first()->rate_per_kg * $product->openingstock->open_qty  }}
      </td>
  </tr>
  @php ($s_no++)
  @endforeach

but showing error Trying to get property 'open_qty' of non-object (View:

rate_per_kg is the correct field name then why it showing error??

my table product_rate with columns

id
r_date
product_id
rate_per_kg

db structure below

id      r_date          product_id      rate_per_kg
1       21-07-2019          1               10
2       22-07-2019          1               11
3       21-07-2019          2               35

Refer: https://imgur.com/12VSkYt

then whats the problem?? Kindly help without hesitation please.

in the example I gave, I expected you would looping over products and having nested child opening_stock

So, the way you have it where $products is actually opening_stock then this line needs to change

      <td>{{ $latestprices->where('product_id',$product->id)->first()->rate_per_kg * $product->openingstock->open_qty  }}

to

      <td>{{ $latestprices->where('product_id',$product->id)->first()->rate_per_kg * $product->open_qty  }}

@snapey actually the problem is in <td>{{ $latestprices->where('product_id',$product->id)->first()->rate_per_kg }} </td> line

 @php ($s_no = 1)
  @foreach($products as $product)
  <tr>
      <td>{{ $s_no }}</td>
      <td>{{ $product->open_date }}</td>
      <td>{{ $product->id }}</td>
      <td>{{ $product->product->product_name }} </td>

      <td>{{ $product->open_qty }}</td>
      <td>{{ $latestprices->where('product_id',$product->id)->first()->rate_per_kg }}
      </td>// this line itself the error is there. it showing error. rate_per_kg is the error.
      <td>{{ $latestprices->where('product_id',$product->id)->first()->rate_per_kg * $product->openingstock->open_qty  }}
      </td>
  </tr>
  @php ($s_no++)
  @endforeach

the below is the error.

Trying to get property 'rate_per_kg' of non-object (View: 

why this problem @snapey

@snapey

if possible kindly response to this thread also

https://laracasts.com/discuss/channels/laravel/calculating-attendance-total-present-and-absent-laravel

This problem is again because you are not looping over the products, You are instead looping over opening_stock

@snapey then if i loop my products means it works??

because if i loop my products means, how i will get the open_qty from OpenStock??

You seem to have lost the ability to think for yourself?

Whilst this doesn't help, this thread is actually pretty entertaining to read. Feel sorry for @snapey :)

@snapey very sorry very very sorry.

let me try @snapey

@snapey ya i found the mistake.

the problem is some product doesnt have rate_per_kg in the rate table. thats the issue. sowhat i did is

//used isset to check whether it is there or not. sothat it worked fine. is this right format.
 @isset($latestprices->where('product_id',$openstock->product->id)->first()->rate_per_kg)
  <td>{{ $latestprices->where('product_id',$product->id)->first()->rate_per_kg }}
      </td>

is this format right?? but this working