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

)
VALUES
(

)"
	);
				$topsstatistics["selectList"] = array(
		"subtype" => "sql",
		"sql" => "SELECT
customer_id,
CONCAT(c.full_name, \" / \", c.nic, \" / \", c.phone) AS Name,
(
        SELECT COUNT(*) 
        FROM loans l1
        WHERE l1.customer_id = c.customer_id 
          AND l1.status = 'active'
    ) AS active_loan_holder,
(
        SELECT COUNT(*) 
        FROM loans l2
        WHERE l2.guarantor_1 = c.customer_id 
          AND l2.status = 'active'
    ) AS active_guarantor_1,
(
        SELECT COUNT(*) 
        FROM loans l3
        WHERE l3.guarantor_2 = c.customer_id 
          AND l3.status = 'active'
    ) AS active_guarantor_2,
(
        SELECT COUNT(*) 
        FROM loans l
        LEFT JOIN (
            SELECT loan_id, SUM(amount) AS total_collected
            FROM collections
            GROUP BY loan_id
        ) csum ON csum.loan_id = l.loan_id

        WHERE l.status = 'active'
        
          /* Check borrower or guarantor involvement */
          AND (
                l.customer_id = c.customer_id
                OR l.guarantor_1 = c.customer_id
                OR l.guarantor_2 = c.customer_id
              )

          /* Arrears calculation with 1st installment due on start date */
          AND (
                (
                    (FLOOR(DATEDIFF(CURDATE(), l.start_date) / l.payment_interval_days) + 1)
                    * l.installment_amount
                ) 
                >
                IFNULL(csum.total_collected, 0)
              )
    ) AS arrears_involved,
full_name AS full_name
FROM customers AS c
ORDER BY full_name
"
	);
		$tables_data["Statistics"][".operations"] = &$topsstatistics;
?>