如何使用SQL合并多数据库表并基于CodeIgniter实现按日期展示交易记录及客户余额计算

Hey there! Let's walk through how to solve your problem—from merging your database tables into a unified transaction list to displaying it with running balances and totals, exactly like your sample output.

First, we'll use a SQL query to combine invoices, receipts, and credit notes into a single dataset, filter by your date range, and compute the running balance. This is far more efficient than handling the merge in PHP, since databases are optimized for this kind of operation.

Here's the query (adjust table/field names if your schema differs slightly):

WITH combined_transactions AS (
    -- Invoices: Record as debit entries
    SELECT
        'INV' AS type,
        invoice_date AS date,
        description,
        invoice_no AS invoiceno,
        amount AS debit,
        0.00 AS credit
    FROM invoice_details
    WHERE comp_id = ?
      AND invoice_date BETWEEN ? AND ?
    
    UNION ALL
    
    -- Receipts: Record as credit entries (linked to invoices)
    SELECT
        'Receipt' AS type,
        receipt_date AS date,
        '' AS description, -- Add receipt reference here if available
        b.invoice_no AS invoiceno,
        0.00 AS debit,
        b.amount AS credit
    FROM receipt_details a
    LEFT JOIN received_amount_details b ON b.receipt_no = a.rece_No
    WHERE a.comp_id = ?
      AND a.receipt_date BETWEEN ? AND ?
    
    UNION ALL
    
    -- Credit Notes: Record as credit entries (linked to invoices)
    SELECT
        'CreditNote' AS type,
        credit_note_date AS date,
        '' AS description, -- Add note description if your table has it
        invoice_no AS invoiceno,
        0.00 AS debit,
        amount AS credit
    FROM credit_note_details -- Replace with your actual credit note table name
    WHERE comp_id = ?
      AND credit_note_date BETWEEN ? AND ?
),
transactions_with_balance AS (
    -- Calculate running balance using window function
    SELECT
        *,
        SUM(debit - credit) OVER (ORDER BY date, invoiceno) AS balance
    FROM combined_transactions
    ORDER BY date, invoiceno
)
-- Fetch transactions + final totals
SELECT * FROM transactions_with_balance
UNION ALL
SELECT
    'Total' AS type,
    NULL AS date,
    NULL AS description,
    NULL AS invoiceno,
    SUM(debit) AS debit,
    SUM(credit) AS credit,
    SUM(debit - credit) AS balance
FROM combined_transactions;

Key Details: #

• UNION ALL: Stacks the three transaction types into one list with consistent columns.
• Running Balance: The SUM(debit - credit) OVER (...) window function calculates the cumulative balance as we sort transactions by date (and invoice number for ties).
• Parameter Binding: The ? placeholders prevent SQL injection—critical for security.

Replace your existing getSalesWithPayments method (and remove the old getSales/getPayments methods if you don't need them elsewhere) with this:

public function getSalesWithPayments($companyId, $from, $to) {
    $query = $this->db->query("
        WITH combined_transactions AS (
            SELECT
                'INV' AS type,
                invoice_date AS date,
                description,
                invoice_no AS invoiceno,
                amount AS debit,
                0.00 AS credit
            FROM invoice_details
            WHERE comp_id = ?
              AND invoice_date BETWEEN ? AND ?
            
            UNION ALL
            
            SELECT
                'Receipt' AS type,
                receipt_date AS date,
                '' AS description,
                b.invoice_no AS invoiceno,
                0.00 AS debit,
                b.amount AS credit
            FROM receipt_details a
            LEFT JOIN received_amount_details b ON b.receipt_no = a.rece_No
            WHERE a.comp_id = ?
              AND a.receipt_date BETWEEN ? AND ?
            
            UNION ALL
            
            SELECT
                'CreditNote' AS type,
                credit_note_date AS date,
                '' AS description,
                invoice_no AS invoiceno,
                0.00 AS debit,
                amount AS credit
            FROM credit_note_details
            WHERE comp_id = ?
              AND credit_note_date BETWEEN ? AND ?
        ),
        transactions_with_balance AS (
            SELECT
                *,
                SUM(debit - credit) OVER (ORDER BY date, invoiceno) AS balance
            FROM combined_transactions
            ORDER BY date, invoiceno
        )
        SELECT * FROM transactions_with_balance
        UNION ALL
        SELECT
            'Total' AS type,
            NULL AS date,
            NULL AS description,
            NULL AS invoiceno,
            SUM(debit) AS debit,
            SUM(credit) AS credit,
            SUM(debit - credit) AS balance
        FROM combined_transactions;
    ", array($companyId, $from, $to, $companyId, $from, $to, $companyId, $from, $to));
    
    return $query->num_rows() > 0 ? $query->result() : [];
}

Note for Older Databases: #

If your database doesn't support CTEs (like MySQL < 8.0), rewrite the query using nested subqueries instead of WITH clauses.

Update your controller to use CodeIgniter's built-in output class for proper JSON formatting:

public function list_soa(){
    $val = $this->input->post('id');
    $start_date = $this->input->post('from');
    $end_date = $this->input->post('to');
    
    $data['all_soa_details'] = $this->createcompany->getSalesWithPayments($val, $start_date, $end_date);
    
    // Return properly formatted JSON
    $this->output
        ->set_content_type('application/json')
        ->set_output(json_encode($data));
}

Replace your existing AJAX code with this to generate the formatted table matching your sample output:

$('#fileterallsoa').click(function(event) {
    event.preventDefault();
    var getID_comp = $('#getID_comp').val();
    var from = $('#fromdate_soa_view').val();
    var to = $('#todate_soa_view').val();
    
    $.ajax({
        url: base_url + "index.php/welcome/list_soa/",
        type: "POST",
        data: { "id": getID_comp, "from": from, "to": to },
        dataType: "json",
        success: function(response) {
            const transactions = response.all_soa_details;
            let tableHtml = '<table border="1" cellpadding="8" cellspacing="0"><thead><tr>' +
                '<th>Type</th><th>Date</th><th>Description</th><th>Invoice No</th>' +
                '<th>Debit</th><th>Credit</th><th>Balance</th>' +
                '</tr></thead><tbody>';
            
            transactions.forEach(transaction => {
                // Format numbers to 2 decimal places
                const debit = transaction.debit ? parseFloat(transaction.debit).toFixed(2) : '0.00';
                const credit = transaction.credit ? parseFloat(transaction.credit).toFixed(2) : '0.00';
                const balance = transaction.balance ? parseFloat(transaction.balance).toFixed(2) : '0.00';
                
                // Handle total row styling
                if (transaction.type === 'Total') {
                    tableHtml += `<tr style="font-weight: bold;">
                        <td>${transaction.type}</td>
                        <td></td><td></td><td></td>
                        <td>Total debit: ${debit}</td>
                        <td>Total credit: ${credit}</td>
                        <td>Total bal: ${balance}</td>
                    </tr>`;
                } else {
                    tableHtml += `<tr>
                        <td>${transaction.type}</td>
                        <td>${transaction.date}</td>
                        <td>${transaction.description || ''}</td>
                        <td>${transaction.invoiceno}</td>
                        <td>${debit}</td>
                        <td>${credit}</td>
                        <td>${balance}</td>
                    </tr>`;
                }
            });
            
            tableHtml += '</tbody></table>';
            // Replace #soa-table-container with your actual HTML container ID
            $('#soa-table-container').html(tableHtml);
        },
        error: function(xhr, status, error) {
            console.error('Error loading transaction data:', error);
            alert('Failed to load transaction details. Please try again.');
        }
    });
});

Don't forget to add a container in your HTML to hold the table:

<div id="soa-table-container"></div>

• Date Format: Ensure your frontend date inputs send dates in YYYY-MM-DD format (matches SQL date filtering).
• Credit Note Table: Double-check the credit note table/field names in the SQL query—adjust them to match your actual schema.
• Testing: Run the query directly in your database first to verify it returns the correct data before integrating it into CodeIgniter.

内容的提问来源于stack exchange，提问作者Mic wer