Customer Sales and payment Statement

I have three tables Customers,payment,loading which are related to customers.id, so i want to make the customer debitor statement. show how he buy and make a payment.

My controller Code:

						$data=DB::table('customers')

											->selectRaw('order_no,payments.created_at as paydate,loadings.created_at as invoicedate,
											SUM(loadings.amount) as total_debit,
											 customers.name,Transaction_Type,Transaction_Number')
												 ->leftjoin('payments','payments.sid','=','customers.id')
												->leftjoin('loadings', 'customers.id','=','loadings.customer_id')
												->groupBy('order_no')
														 ->get();

									 $data2=DB::table('customers')
									     ->select('payments.created_at as paydate','payments.amount as total_credit',
									 					 'customers.name','Transaction_Number')
									 						 ->leftjoin('payments','payments.sid','=','customers.id')
									 								 ->get();

my blade view

            <div style="display:none">
          @foreach($data as $dt)
            {{$totalInv=$dt->total_debit}}
          @endforeach
          @foreach($data as $dt)
          {{$totalpay=$dt->total_credit}}

          @endforeach

        </div>

    <table id="example2" class="table table-bordered table-striped display">
          <thead>
        <tr>
          <th>Customer Name</th>
          <th>Transaction_Type</th>
          <th>Transaction_Number</th>
          <th>Transaction Date</th>
          <th> Transaction Amount - Credit </th>
          <th> Transaction Amount - Debit </th>
          <th>Balance</th>
        </tr>
      </thead>

      <tbody>
        @foreach($data as $dt)
        <tr>

          <td>{{$dt->name }}</td>
          <td>Invoice</td>
          <td>{{$dt->order_no}}</td>
          <td>  {{$dt->invoicedate}}</td>
          <td>0</td>

          <td>  {{number_format($dt->total_debit,2)}}</td>
          <td>{{$totalInv-0}}</td>
        </tr>
        @endforeach

                  @foreach($data2 as $dt)
              <tr>
                <td>{{$dt->name }}</td>

              <td>payment</td>
              <td>  {{$dt->Transaction_Number}}</td>
              <td>  {{$dt->paydate}}</td>

              <td> {{number_format($dt->total_credit,2)}}</td>
              <td> 0</td>
              <td> {{$totalInv-$totalpay }}</td>

             </tr>
         @endforeach

        </tbody>
      </table>

a4ashraf

4 years ago

Best Answer

Level 33

@emfinanga

your table looks like the wrong accounting

if you manage the customer ledger then it should be proper customer_ledger

it should be separate from your other table

here is an example of the table schema

Schema::create('customer_ledger', function (Blueprint $table) {

            $table->id();
            $table->string('description');
            $table->float('debit', 8, 2);
            $table->float('credit', 8, 2);
            $table->float('balance', 8, 2);
            $table->timestamps();

}

1 like

melx

4 years ago

Level 4

Thanks for your solution Sir

Please or to participate in this conversation.