The best way to create customer statement (Paid and balance amounts)

I want to create customer statement and send to my customers, So I have these invoices

Order Invoices: All orders customer have made

class OrderInvoice extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['order_id','invoice_no','reference_no','issue_date','due_date','sub_total','discount','total_amount','balance','invoice_status','customer_id'];

    public function order()
    {
        return $this->belongsTo(Order::class);
    }

    public function orderInvoiceReceipts()
    {
        return $this->hasMany(OrderInvoiceReceipt::class);
    }

    public function customer() :BelongsTo
    {
        return $this->belongsTo(Customer::class);
    }
}

2.Sales Invoice: all sales customer have made

class SalesInvoice extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['sold_inventory_id','customer_id','invoice_no','reference_no','issue_date','sub_total','vat','discount','total_amount','balance','invoice_status'];

    public function soldInventory()
    {
        return $this->belongsTo(SoldInventory::class);
    }

    public function salesInvoiceReceipts()
    {
        return $this->hasMany(SalesInvoiceReceipt::class);
    }

    public function customer() :BelongsTo
    {
        return $this->belongsTo(Customer::class);
    }
}

3.OrderInvoiceReceipts: all orders customer has made payments

class OrderInvoiceReceipt extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['order_invoice_id','company_account_id','reference_no','receipt_no','paid_amount','balance','paid_date','status','description'];

    public function orderInvoice()
    {
        return $this->belongsTo(OrderInvoice::class);
    }

    public function companyAccount(): BelongsTo
    {
        return $this->belongsTo(CompanyAccount::class);
    }

    public function companyAccountTransaction(): HasOne
    {
        return $this->hasOne(CompanyAccountTransaction::class);
    }

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }
}

4.SalesInvoiceReceipts: all sales customer has made payments

class SalesInvoiceReceipt extends Model
{
    use HasFactory, CreatedUpdatedBy, softDeletes;

    protected $fillable = ['sales_invoice_id','company_account_id','customer_id','receipt_no','reference_no','paid_amount','paid_date','balance','status','description'];

    public function companyAccount()
    {
        return $this->belongsTo(CompanyAccount::class);
    }

    public function salesInvoice()
    {
        return $this->belongsTo(SalesInvoice::class);
    }

    public function companyAccountTransaction(): HasOne
    {
        return $this->hasOne(CompanyAccountTransaction::class);
    }

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }
}

In my system, Orders and Sales are different, Orders are the things company will manufacture and has special invoice while sales are the raw materials company is selling. So I want to create customer statement, In order to send customer full report of the balances that the company is owed to the customer with detailed report. for instance I want to read all orderInvoices and salesInvoices that has balances and also want to read all orderInvoiceReceipts and SalesInvoiceReceipts of these oderInvoice and salesInvoices respectively, I want to read Date, Reference no/ InvoiceNo/ Receipt No, Description ( to tell weather this is order or sales), total Amount charged if it is orderInvoice or salesInvoice, payments (orderInvoiceReceipts/SalesInvoiceReceipts) and Balance. From date to Date, if there is balance before from_date, I want to forward and add it. I am using Laravel and livewire

LaryAI

9 months ago

Level 58

Certainly! Here’s a structured approach to generate a customer statement in Laravel (with Livewire), including all invoices (orders/sales), receipts, balances, and a running total, with support for a date range and opening balance.

1. Define the Statement Query Logic

You want to:

Get all OrderInvoices and SalesInvoices for a customer within a date range, with their receipts.
Get all OrderInvoiceReceipts and SalesInvoiceReceipts for that customer within the same range.
Calculate the opening balance (before from_date).
Show a running balance.

2. Sample Livewire Component Method

public function getCustomerStatement($customerId, $fromDate, $toDate)
{
    // 1. Calculate opening balance (before fromDate)
    $orderInvoicesBefore = OrderInvoice::where('customer_id', $customerId)
        ->where('issue_date', '<', $fromDate)
        ->sum('total_amount');

    $salesInvoicesBefore = SalesInvoice::where('customer_id', $customerId)
        ->where('issue_date', '<', $fromDate)
        ->sum('total_amount');

    $orderReceiptsBefore = OrderInvoiceReceipt::whereHas('orderInvoice', function($q) use ($customerId) {
            $q->where('customer_id', $customerId);
        })
        ->where('paid_date', '<', $fromDate)
        ->sum('paid_amount');

    $salesReceiptsBefore = SalesInvoiceReceipt::where('customer_id', $customerId)
        ->where('paid_date', '<', $fromDate)
        ->sum('paid_amount');

    $openingBalance = ($orderInvoicesBefore + $salesInvoicesBefore) - ($orderReceiptsBefore + $salesReceiptsBefore);

    // 2. Get all relevant transactions within date range
    $orderInvoices = OrderInvoice::where('customer_id', $customerId)
        ->whereBetween('issue_date', [$fromDate, $toDate])
        ->get();

    $salesInvoices = SalesInvoice::where('customer_id', $customerId)
        ->whereBetween('issue_date', [$fromDate, $toDate])
        ->get();

    $orderReceipts = OrderInvoiceReceipt::whereHas('orderInvoice', function($q) use ($customerId) {
            $q->where('customer_id', $customerId);
        })
        ->whereBetween('paid_date', [$fromDate, $toDate])
        ->get();

    $salesReceipts = SalesInvoiceReceipt::where('customer_id', $customerId)
        ->whereBetween('paid_date', [$fromDate, $toDate])
        ->get();

    // 3. Merge all transactions into a single collection
    $transactions = collect();

    foreach ($orderInvoices as $invoice) {
        $transactions->push([
            'date' => $invoice->issue_date,
            'reference' => $invoice->invoice_no,
            'type' => 'Order Invoice',
            'description' => 'Order Invoice',
            'amount' => $invoice->total_amount,
            'payment' => 0,
        ]);
    }

    foreach ($salesInvoices as $invoice) {
        $transactions->push([
            'date' => $invoice->issue_date,
            'reference' => $invoice->invoice_no,
            'type' => 'Sales Invoice',
            'description' => 'Sales Invoice',
            'amount' => $invoice->total_amount,
            'payment' => 0,
        ]);
    }

    foreach ($orderReceipts as $receipt) {
        $transactions->push([
            'date' => $receipt->paid_date,
            'reference' => $receipt->receipt_no,
            'type' => 'Order Receipt',
            'description' => 'Order Payment',
            'amount' => 0,
            'payment' => $receipt->paid_amount,
        ]);
    }

    foreach ($salesReceipts as $receipt) {
        $transactions->push([
            'date' => $receipt->paid_date,
            'reference' => $receipt->receipt_no,
            'type' => 'Sales Receipt',
            'description' => 'Sales Payment',
            'amount' => 0,
            'payment' => $receipt->paid_amount,
        ]);
    }

    // 4. Sort by date, then by type (so invoices come before receipts on same day)
    $transactions = $transactions->sortBy([
        ['date', 'asc'],
        ['type', 'asc'],
    ])->values();

    // 5. Calculate running balance
    $balance = $openingBalance;
    $statement = [];

    // Add opening balance row
    $statement[] = [
        'date' => $fromDate,
        'reference' => '',
        'type' => 'Opening Balance',
        'description' => 'Balance Forwarded',
        'amount' => '',
        'payment' => '',
        'balance' => $openingBalance,
    ];

    foreach ($transactions as $row) {
        $balance += $row['amount'];
        $balance -= $row['payment'];
        $statement[] = array_merge($row, ['balance' => $balance]);
    }

    return $statement;
}

3. How to Use in Livewire/View

You can call this method in your Livewire component and pass the result to your view for display as a table.

public $customerId, $fromDate, $toDate, $statement;

public function mount($customerId)
{
    $this->customerId = $customerId;
    $this->fromDate = now()->subMonth()->toDateString();
    $this->toDate = now()->toDateString();
    $this->loadStatement();
}

public function loadStatement()
{
    $this->statement = $this->getCustomerStatement($this->customerId, $this->fromDate, $this->toDate);
}

And in your Blade view:

<table>
    <thead>
        <tr>
            <th>Date</th>
            <th>Reference</th>
            <th>Description</th>
            <th>Amount</th>
            <th>Payment</th>
            <th>Balance</th>
        </tr>
    </thead>
    <tbody>
        @foreach($statement as $row)
            <tr>
                <td>{{ $row['date'] }}</td>
                <td>{{ $row['reference'] }}</td>
                <td>{{ $row['description'] }}</td>
                <td>{{ $row['amount'] ?: '' }}</td>
                <td>{{ $row['payment'] ?: '' }}</td>
                <td>{{ $row['balance'] }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

4. Notes

You can add more fields (like invoice status, etc.) as needed.
This approach is flexible: you can add filters, export to PDF, etc.
For performance, consider eager loading relationships if you have a lot of data.

Summary:
This solution gives you a full, date-ranged customer statement with opening balance, all invoices, all receipts, and a running balance, ready to display or send to your customers.

Please or to participate in this conversation.