如何使用Laravel 9 Eloquent从日记账数据生成试算平衡表

模型关联设置 #

首先在Voucher模型中定义与Account模型的双向关联，分别对应借方和贷方科目：

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Voucher extends Model
{
    protected $table = 'vouchers';
    protected $fillable = ['voucher_date', 'debit', 'credit', 'amount'];

    // 关联借方科目
    public function debitAccount()
    {
        return $this->belongsTo(Account::class, 'debit');
    }

    // 关联贷方科目
    public function creditAccount()
    {
        return $this->belongsTo(Account::class, 'credit');
    }
}

Account模型保持基础定义即可：

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Account extends Model
{
    protected $table = 'accounts';
    protected $fillable = ['name', 'desc', 'status'];
}

方案一：遍历凭证拆分条目 #

通过遍历所有凭证，将每笔凭证拆分为借方、贷方两个独立条目，再整理格式并计算汇总：

use App\Models\Voucher;

public function getTrialBalance()
{
    $vouchers = Voucher::with(['debitAccount', 'creditAccount'])->get();
    
    $items = [];
    $totalDebit = 0;
    $totalCredit = 0;

    foreach ($vouchers as $voucher) {
        // 添加借方条目
        $debitAmt = $voucher->amount;
        $items[] = [
            'date' => $voucher->voucher_date,
            'account' => $voucher->debitAccount->name,
            'debit' => number_format($debitAmt, 2),
            'credit' => number_format(0, 2)
        ];
        $totalDebit += $debitAmt;

        // 添加贷方条目
        $creditAmt = $voucher->amount;
        $items[] = [
            'date' => $voucher->voucher_date,
            'account' => $voucher->creditAccount->name,
            'debit' => number_format(0, 2),
            'credit' => number_format($creditAmt, 2)
        ];
        $totalCredit += $creditAmt;
    }

    // 按日期倒序排序
    usort($items, fn($a, $b) => strtotime($b['date']) - strtotime($a['date']));

    // 添加汇总行
    $items[] = ['date' => '', 'account' => '', 'debit' => '', 'credit' => ''];
    $items[] = [
        'date' => '',
        'account' => 'Balance',
        'debit' => number_format($totalDebit, 2),
        'credit' => number_format($totalCredit, 2)
    ];

    return $items;
}

方案二：用UNION查询合并条目 #

通过Eloquent的UNION查询，直接从数据库层面合并借方和贷方数据，减少内存处理：

use App\Models\Voucher;
use Illuminate\Support\Facades\DB;

public function getTrialBalance()
{
    // 查询借方条目
    $debitQuery = Voucher::select(
        'voucher_date as date',
        'accounts.name as account',
        DB::raw('amount as debit'),
        DB::raw('0 as credit')
    )->join('accounts', 'vouchers.debit', '=', 'accounts.id')
     ->where('accounts.status', 1);

    // 查询贷方条目
    $creditQuery = Voucher::select(
        'voucher_date as date',
        'accounts.name as account',
        DB::raw('0 as debit'),
        DB::raw('amount as credit')
    )->join('accounts', 'vouchers.credit', '=', 'accounts.id')
     ->where('accounts.status', 1);

    // 合并查询并排序
    $items = $debitQuery->union($creditQuery)
                        ->orderBy('date', 'desc')
                        ->get()
                        ->map(fn($item) => [
                            'date' => $item->date,
                            'account' => $item->account,
                            'debit' => number_format($item->debit, 2),
                            'credit' => number_format($item->credit, 2)
                        ]);

    // 计算汇总
    $totalDebit = $items->sum(fn($i) => (float)$i['debit']);
    $totalCredit = $items->sum(fn($i) => (float)$i['credit']);

    // 添加汇总行
    $items->push(['date' => '', 'account' => '', 'debit' => '', 'credit' => '']);
    $items->push([
        'date' => '',
        'account' => 'Balance',
        'debit' => number_format($totalDebit, 2),
        'credit' => number_format($totalCredit, 2)
    ]);

    return $items;
}

视图展示 #

将处理好的数据在Blade视图中渲染成表格：

<table border="1" cellpadding="8">
    <thead>
        <tr>
            <th>日期</th>
            <th>科目</th>
            <th style="text-align: right">借方</th>
            <th style="text-align: right">贷方</th>
        </tr>
    </thead>
    <tbody>
        @foreach($trialBalance as $item)
        <tr>
            <td>{{ $item['date'] }}</td>
            <td>{{ $item['account'] }}</td>
            <td style="text-align: right">{{ $item['debit'] }}</td>
            <td style="text-align: right">{{ $item['credit'] }}</td>
        </tr>
        @endforeach
    </tbody>
</table>

内容的提问来源于stack exchange，提问作者Maji Mazuri