<?php
$topsrecovery = array();
		$topsrecovery["insert"] = array(
		"subtype" => "sql",
		"sql" => "INSERT INTO `Loan Ladger` (

)
VALUES
(

)"
	);
				$topsrecovery["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,

    /* new installment (rounded) */
    t.new_installment_amount,

    t.payment_interval_days,
    t.start_date,
    t.end_date,
    t.status,
    t.profit_amount,
    t.total_collected,
    t.loan_period,

    /* should pay */
    (
        (FLOOR(DATEDIFF(CURDATE(), t.start_date) / t.payment_interval_days) + 1)
        * t.new_installment_amount
    ) AS should_pay,

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

    /* arrears */
    GREATEST(
        (
            (FLOOR(DATEDIFF(CURDATE(), t.start_date) / t.payment_interval_days) + 1)
            * t.new_installment_amount
        ) - t.total_collected,
        0
    ) AS arrears_amount,

    /* remaining days */
    DATEDIFF(t.end_date, CURDATE()) AS remaining_days,

    /* remaining installments */
    GREATEST(
        CEIL(
            (t.total_payable - t.total_collected + t.profit_amount)
            / t.new_installment_amount
        ),
        0
    ) AS remaining_installments,

    /* new loan amount */
    (t.total_payable - t.total_collected + t.profit_amount) AS new_loan_amount

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.payment_interval_days,
        l.start_date,
        l.end_date,
        l.status,
        l.loan_period,
        l.profit_amount,

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

        /* new installment = rounded to 2 decimals */
        ROUND(
            (l.total_payable - IFNULL(SUM(c.amount), 0) + l.profit_amount)
            / (l.loan_period / l.payment_interval_days),
            2
        ) AS new_installment_amount

    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 l.status = 'active' or l.status = 'Recovered'
    GROUP BY l.loan_id
) AS t

WHERE DATEDIFF(t.end_date, CURDATE()) <= 0
  AND (t.total_payable - t.total_collected) > 0

ORDER BY t.loan_id DESC;

"
	);
		$tables_data["recovery"][".operations"] = &$topsrecovery;
?>