AbdulBazith
5 months ago
141
9
Laravel

groupby and sum the total_amount of same suppliers

Posted 5 months ago by AbdulBazith

guys i have a small problem in my hotelmanagement system. its a erp like software.

i have a table purchase_to_stores with columns

id
supplier_id (fk)
due_date
bill_no
total_amount
payment_status
payment_date

i need to fetch records from this table, with a where condition between two date of due_date and total_amount should be added if supplier_id is same.

that is for one supplier i may bought 2 bill_no. 1 bill for rs 500 and another bill ofr rs 200. so i need to get the output as

the due_date, supplier_name, total_amount must be 700 .. like this

this is my supplier model,

 public function purchasetostore()
    {
        return $this->hasMany('App\PurchaseToStore', 'supplier_id');
    }

this is my PurchaseToStore model

 public function supplier()
    {
        return $this->belongsTo('App\Supplier', 'supplier_id');
    }

my query is

$paymentlists = PurchaseToStore::where('payment_status', '0')->whereBetween('due_date', array($fromdate, $todate))->paginate(50);

and in my view.blade file

 <thead>
                                        <tr>
                                            <th>S.No</th>
                                            <th>Due Date</th>
                                            <th>Supplier</th>
                                            <th>Cheque or Cash</th>
                                            <th>Amount</th>
                                            <th>Signature</th>
                                        </tr>
                                    </thead>


 @foreach($paymentlists as $s_no=>$paymentlist)
                                        <tr>
                                            <td >
                                                {{ $s_no + $paymentlists->firstItem() }}
                    </td>

                                        <td >
                                                {{ \Carbon\Carbon::parse( $paymentlist->due_date)->format('d-m-Y') }}                                        
                                       </td>

                                            <td >
                                                {{ $paymentlist->supplier->sup_name }}
                    </td>

                                            <td >
                        </td>

                                            <td >{{ $paymentlist->total_amount }}</td>
                                            <td> </td>

                                        </tr>

 @endforeach

i can get the output. but if a supplier has two bills. then the two bills are displayed. I need the total amount for same supplier.

what to do kindly some one help

Please sign in or create an account to participate in this conversation.