<?php
$topsloan_ladger_all = array();
		$topsloan_ladger_all["selectList"] = array(
		"subtype" => "sql",
		"sql" => "SELECT
    t.loan_id,
    t.customer_id,
    t.horno,
    t.guarantor_1,
    t.guarantor_2,

    t.full_name,
    t.nic,
    t.phone,
    t.address,

    t.loan_amount,
    t.total_payable,
    t.installment_amount,
    t.payment_interval_days,
    t.start_date,
    t.end_date,
    t.status,
    t.profit_amount,
    t.total_collected,
    t.Old_Loan,
    t.loan_period,

    -- capped number of installments due so far
    LEAST(
        (FLOOR(DATEDIFF(CURDATE(), t.start_date) / t.payment_interval_days) + 1),
        CEIL(t.loan_period / t.payment_interval_days)
    ) * t.installment_amount AS should_pay,

    -- remaining balance
    (t.total_payable - t.total_collected) AS remaining_balance,

    -- arrears amount (capped)
    GREATEST(
        LEAST(
            (FLOOR(DATEDIFF(CURDATE(), t.start_date) / t.payment_interval_days) + 1),
            CEIL(t.loan_period / t.payment_interval_days)
        ) * t.installment_amount - t.total_collected,
        0
    ) AS arrears_amount,

    -- Days left until end
    DATEDIFF(t.end_date, CURDATE()) AS remaining_days,

    -- Remaining installments (rounded)
    GREATEST(
        ROUND((t.total_payable / t.installment_amount)
              - (t.total_collected / t.installment_amount), 0),
        0
    ) AS remaining_installments

FROM (
    SELECT
        l.loan_id,
        l.customer_id,
        l.horno,
        l.guarantor_1,
        l.guarantor_2,

        cus.full_name,
        cus.nic,
        cus.phone,
        cus.address,

        l.loan_amount,
        l.total_payable,
        l.installment_amount,
        l.payment_interval_days,
        l.start_date,
        l.end_date,
        l.status,
        l.profit_amount,
        l.Old_Loan,
        l.loan_period,

        IFNULL(SUM(c.amount), 0) AS total_collected

    FROM loans l
    LEFT JOIN customers cus ON l.customer_id = cus.customer_id
    LEFT JOIN collections c ON l.loan_id = c.loan_id
    WHERE 1    GROUP BY l.loan_id
) AS t

ORDER BY t.loan_id DESC;
"
	);
		$tables_data["Loan Ladger All"][".operations"] = &$topsloan_ladger_all;
?>