<?php

class Invoice {

	/*************************************************
	/ This function add new Invoice
	*************************************************/
	
	public static function AddInvoice($data) {
		global $db;
				
		$id = $db->query_insert(TABLE_INVOICE, $data);
		if($db->affected_rows > 0){
			//$db->query_insert("vrs_logs", $data);
			return $id;
		}else{
			return false;
		}
	
	} // AddInvoice

	/*************************************************
	/ This function add new Invoice Product
	 *************************************************/

	public static function AddInvoiceProduct($data) {
		global $db;

		$id = $db->query_insert(TABLE_INVOICE_PRODUCT, $data);
		if($db->affected_rows > 0){
			//$db->query_insert("vrs_logs", $data);
			return $id;
		}else{
			return false;
		}

	} // AddInvoiceProduct


	/*************************************************
	/ This function update Invoice
	 *************************************************/

	public static function UpdateInvoiceSetup($pid,$data) {
		global $db;

		$id = $db->query_update(TABLE_INVOICE,$data,"id='$pid'");

		if($db->affected_rows > 0){
			//$db->query_insert("vrs_logs", $data);

			return $id;
		}else{
			return $id;
		}

	} // UpdateInvoice


	public static function GetInvoiceSetupById($id) {
	
		global $db;
		$query = "SELECT * from ".TABLE_INVOICE." WHERE id ='$id'";
		//print($query);exit;
		$rows = $db->query_first($query);
	
                if($db->affected_rows > 0){
			
                        return $rows;
                }else{
			
                        return false;
                }
	}//Invoice

	public static function GetInvoiceSetupByNumber($number) {

		global $db;
		$query = "SELECT * from ".TABLE_INVOICE." WHERE code ='$number'";
		//print($query);exit;
		$rows = $db->query_first($query);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	/**********************************************************
	/ This function will return Invoice
	 **********************************************************/

	public static function GetInvoiceList($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT * from ".TABLE_INVOICE." ". $filter;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	public static function GetInvoiceListJoinCustomer($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT *,".TABLE_INVOICE.".id AS invoiceid  from ".TABLE_INVOICE." ". $filter;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	/**********************************************************
	/ This function will return Invoice
	 **********************************************************/

	public static function GetInvoiceReportList($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT *, cs.category_id AS cscategory_id, inv.code AS code, cs.id AS custid, inv.status AS status from ".TABLE_INVOICE." AS inv ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	/**********************************************************
	/ This function will return Invoice
	 **********************************************************/

	public static function GetInvoiceCustomerReportList($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT 'invoice' AS nameTable, inv.id, inv.description, inv.code AS code , inv.customer_id, inv.discount, inv.discount_price, inv.total_amount, inv.payment_due,
		inv.sales_person_id, inv.status AS status, inv.create_date, inv.validate_date, inv.datetime, invPr.invoice_id,
		invPr.product_id, invPr.product_qty, invPr.unit_cost_price, invPr.unit_distributor_price, invPr.unit_sale_price,
		'' AS payment_type, '' AS cheque_bank_name, '' AS cheque_number,
		'' AS cheque_payee_name, '' AS cheque_date, '' AS bank_name, '' AS bank_account_number,
		'' AS party_name, '' AS from_bank_name from ".TABLE_INVOICE." AS inv ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
		//print($rows);exit;
		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	public static function GetInvoiceCustomerReportList123($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT 'invoice' AS nameTable, inv.id, inv.description, inv.code AS code , inv.customer_id, 
		inv.discount, inv.discount_price, inv.add_discount, inv.add_discount_price, inv.total_amount, inv.payment_due,
		inv.sales_person_id, inv.status AS status, inv.create_date, inv.validate_date, inv.datetime, invPr.invoice_id,
		invPr.product_id, invPr.product_qty, invPr.unit_cost_price, invPr.unit_distributor_price, invPr.unit_sale_price,
		'' AS payment_type, '' AS cheque_bank_name, '' AS cheque_number,
		'' AS cheque_payee_name, '' AS cheque_date, '' AS bank_name, '' AS bank_account_number,
		'' AS party_name, '' AS from_bank_name from ".TABLE_INVOICE." AS inv ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
		//print($rows);exit;

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	/**********************************************************
	/ This function will return Invoice
	 **********************************************************/

	public static function GetInvoiceCustomerReportListMonthlySale($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT inv.code AS code, inv.customer_id, inv.discount_price, inv.total_amount, 
		inv.status AS status, inv.create_date, invPr.invoice_id,
		invPr.product_id, sum(invPr.product_qty) AS product_qty from ".TABLE_INVOICE." AS inv ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
		//print($rows);exit;
		if($db->affected_rows > 0){
			return $rows;
		}else{
			return false;
		}
	}//Invoice

	public static function GetInvoiceCustomerReportListMonthlyPayment($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT pay.code AS code, pay.customer_id, pay.amount, pay.payment_type, 
		pay.status AS status, pay.create_date from ".TABLE_PAYMENT." AS pay ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
		//print($rows);exit;
		if($db->affected_rows > 0){
			return $rows;
		}else{
			return false;
		}
	}//Invoice

	/**********************************************************
	/ This function will return Invoice
	 **********************************************************/

	public static function GetInvoiceCustomerReportListMonthlySaleCustomers($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT inv.code AS code, inv.customer_id, sum(inv.discount_price) AS discount_price, 
		sum(inv.total_amount) AS total_amount, count(inv.customer_id) AS total_invoices,
		inv.status AS status  from ".TABLE_INVOICE." AS inv ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
		//print($rows);exit;
		if($db->affected_rows > 0){
			return $rows;
		}else{
			return false;
		}
	}//Invoice

	public static function GetInvoiceCustomerReportListMonthlyPaymentCustomers($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT pay.code AS code, pay.customer_id,
		sum(pay.amount) AS total_amount, count(pay.customer_id) AS total_invoices,
		pay.status AS status  from ".TABLE_PAYMENT." AS pay ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
		//print($rows);exit;
		if($db->affected_rows > 0){
			return $rows;
		}else{
			return false;
		}
	}//Invoice

	/**********************************************************
	/ This function will return Invoice
	 **********************************************************/

	public static function GetInvoicePaymentReportList($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT *, inv.create_date AS invoice_create_date, inv.code AS invoicecode from ".TABLE_INVOICE." AS inv ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	public static function GetInvoicePaymentReport($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT *, inv.create_date AS invoice_create_date, inv.code AS invoicecode,
					pay.create_date AS payment_create_date, pay.code AS paymentcode from ".TABLE_CUSTOMERSETUP." AS cus JOIN ".TABLE_INVOICE." AS inv ON cus.id = inv.customer_id ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	/**********************************************************
	/ This function will return Invoice
	 **********************************************************/

	public static function GetInvoicePaymentReportLists($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT 'invoice' AS nameTable, inv.create_date AS create_date, inv.code AS code,
 		inv.customer_id AS customer_id, '' AS invoice_reference_code, '' AS payment_type, '' AS amount,
 		'' AS cheque_bank_name, '' AS cheque_number, '' AS cheque_payee_name, '' AS cheque_date,
 		'' AS bank_name, '' AS bank_account_number, '' AS party_name, '' AS from_bank_name,
 		inv.discount, inv.discount_price, inv.total_amount, inv.payment_due, inv.sales_person_id, inv.status
 		from ".TABLE_INVOICE."  AS inv ". $filter;
			//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	public static function GetInvoicePaymentReportLists123($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT 'invoice' AS nameTable, inv.create_date AS create_date, inv.code AS code,
 		inv.customer_id AS customer_id, '' AS invoice_reference_code, '' AS payment_type, '' AS amount,
 		'' AS cheque_bank_name, '' AS cheque_number, '' AS cheque_payee_name, '' AS cheque_date,
 		'' AS bank_name, '' AS bank_account_number, '' AS party_name, '' AS from_bank_name,
		'' AS withholding_tax, '' AS sales_tax, '' AS goods_return, '' AS shelf_rent, '' AS builty_charges, '' AS other_expense,
 		inv.discount, inv.discount_price, inv.total_amount, inv.payment_due, inv.sales_person_id, inv.status, '' AS description
 		from ".TABLE_INVOICE."  AS inv ". $filter;
			//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice
 public static function GetInvoicePaymentReportLists123categorywise($filter="", $pageno="null", $pagesize="null") {
    global $db;
    $query = "SELECT 
                'invoice' AS nameTable, 
                inv.create_date AS create_date, 
                inv.code AS code,
                inv.customer_id AS customer_id, 
                '' AS invoice_reference_code, 
                '' AS payment_type, 
                '' AS amount,
                '' AS cheque_bank_name, 
                '' AS cheque_number, 
                '' AS cheque_payee_name, 
                '' AS cheque_date,
                '' AS bank_name, 
                '' AS bank_account_number, 
                '' AS party_name, 
                '' AS from_bank_name,
                '' AS withholding_tax, 
                '' AS sales_tax, 
                '' AS goods_return, 
                '' AS shelf_rent, 
                '' AS builty_charges, 
                '' AS other_expense,
                inv.discount, 
                inv.discount_price, 
                inv.total_amount, 
                inv.payment_due, 
                inv.sales_person_id, 
                inv.status, 
                '' AS description
            FROM " . TABLE_INVOICE . " AS inv " . $filter . " 
            
            UNION ALL

            SELECT 
                'payment' AS nameTable, 
                pay.create_date AS create_date, 
                pay.code AS code,
                pay.customer_id AS customer_id, 
                pay.invoice_reference_code, 
                pay.payment_type, 
                pay.amount,
                pay.cheque_bank_name, 
                pay.cheque_number, 
                pay.cheque_payee_name, 
                pay.cheque_date,
                pay.bank_name, 
                pay.bank_account_number, 
                pay.party_name, 
                pay.from_bank_name,
                pay.withholding_tax, 
                pay.sales_tax, 
                pay.goods_return, 
                pay.shelf_rent, 
                pay.builty_charges, 
                pay.other_expense,
                '', '', '', '', '', '', pay.description
            FROM " . TABLE_PAYMENT . " AS pay " . $filter . "
            
            ORDER BY customer_id, create_date";

    $rows = $db->fetch_all_array($query, $pageno, $pagesize);

    if ($db->affected_rows > 0) {
        return $rows;
    } else {
        return false;
    }
}



	public static function GetInvoicePaymentReportListsForOwner($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT 'invoice' AS nameTable, inv.create_date AS create_date,
 		inv.customer_id AS customer_id, '' AS amount,
 		inv.discount, inv.discount_price, inv.total_amount, inv.payment_due, inv.status
 		from ".TABLE_INVOICE."  AS inv ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	public static function GetInvoicePaymentReportListsForOwner123($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT 'invoice' AS nameTable, inv.create_date AS create_date,
 		inv.customer_id AS customer_id, '' AS amount,
 		inv.discount, inv.discount_price, inv.total_amount, inv.payment_due, inv.status,
		'' AS withholding_tax, '' AS sales_tax, '' AS goods_return, '' AS shelf_rent, '' AS builty_charges, '' AS other_expense
 		from ".TABLE_INVOICE."  AS inv ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);

		if($db->affected_rows > 0){

			return $rows;
		}else{

			return false;
		}
	}//Invoice

	/**********************************************************
	/ This function will return Invoice Products
	 **********************************************************/

	public static function GetInvoiceProducts($filter="",$pageno="null",$pagesize="null") {

		global $db;
		$query = "SELECT * from ".TABLE_INVOICE_PRODUCT." ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
		//print($rows);
		if($db->affected_rows > 0){
			return $rows;
		}else{
			return false;
		}
	}//InvoiceProducts

	/**********************************************************
	/ This function will delete the Invoice Products
	 **********************************************************/

	public static function DeleteInvoiceProducts($invoice_id) {
		global $db;
		$query = "Delete from " . TABLE_INVOICE_PRODUCT . "  WHERE invoice_id='$invoice_id'";
		$rows = $db->query_first($query);
		if($db->affected_rows > 0){
			return true;
		}else{
			return false;
		}
	}//DeleteInvoiceProducts
  
 
  public static function getLedgerReport($dateFrom, $dateTo, $catType)
{
    global $db;

    // Category Filter Logic
    $catCondition = $catType;

    // ================= Opening Balance Query ==================
    $openingBalanceQuery = "
        SELECT 
            cs.id AS customer_id,
            cs.name AS customer_name,
            IFNULL(CEIL(SUM(p.total_payment)), 0) AS total_payment,
            CEIL((IFNULL(SUM(i.total_invoice), 0) + IFNULL(cs.balance, 0))) AS total_invoice,
            CEIL((IFNULL(SUM(p.total_payment), 0) 
             - (IFNULL(SUM(i.total_invoice), 0) + IFNULL(cs.balance, 0)))) AS opening_balance
        FROM 
            customer_setup cs
        LEFT JOIN (
            SELECT 
                customer_id,
                CEIL(SUM(amount)) AS total_payment
            FROM 
                payment
            WHERE 
                DATE(create_date) < '$dateFrom'
                AND status <> 'Cancel'
            GROUP BY 
                customer_id
        ) p ON cs.id = p.customer_id
        LEFT JOIN (
            SELECT 
                customer_id,
                CEIL(SUM(total_amount)) AS total_invoice
            FROM 
                invoice
            WHERE 
                DATE(create_date) < '$dateFrom'
                AND status NOT IN ('Draft', 'Cancel')
            GROUP BY 
                customer_id
        ) i ON cs.id = i.customer_id
        WHERE 
            cs.status = 'active'
            $catCondition
        GROUP BY 
            cs.id
        ORDER BY 
            cs.name ASC
    ";

    // ================= Transaction Query ==================
    $transactionQuery = "
        SELECT 
            t.customer_id,
            cs.name AS customer_name,
            t.create_date,
            t.reference_code,
            t.transaction_type,
            CEIL(t.debit) AS debit,
            CEIL(t.credit) AS credit,
            t.status,
            t.payment_type,
            t.from_bank_name,
            t.cheque_number,
            t.cheque_date
        FROM (
            SELECT 
                customer_id,
                create_date,
                code AS reference_code,
                'Invoice' AS transaction_type,
                CEIL(total_amount) AS debit,
                0 AS credit,
                status,
                '' AS payment_type,
                '' AS from_bank_name,
                '' AS cheque_number,
                '' AS cheque_date
            FROM 
                invoice
            WHERE 
                DATE(create_date) BETWEEN '$dateFrom' AND '$dateTo'
                AND status NOT IN ('Draft', 'Cancel')

            UNION ALL

            SELECT 
                customer_id,
                create_date,
                code AS reference_code,
                'Payment' AS transaction_type,
                0 AS debit,
                CEIL(amount) AS credit,
                status,
                payment_type,
                from_bank_name,
                cheque_number,
                cheque_date
            FROM 
                payment
            WHERE 
                DATE(create_date) BETWEEN '$dateFrom' AND '$dateTo'
                AND status <> 'Cancel'
        ) t
        JOIN 
            customer_setup cs ON cs.id = t.customer_id
        WHERE 
            cs.status = 'active'
            $catCondition
        ORDER BY 
            cs.name ASC, t.create_date
    ";
//echo $openingBalanceQuery;
    // ================= Execute Queries ==================
    $openingBalanceResult = $db->fetch_all_array($openingBalanceQuery);
    $transactionResult = $db->fetch_all_array($transactionQuery);

    // ================= Return Combined Data ==================
    return [
        'opening_balance' => $openingBalanceResult,
        'transactions' => $transactionResult
    ];
}

  
 public static function GetInvoiceAgingReport($filter)
{
    global $db;

    $query = "
        SELECT 
            cs.id AS Customer_ID,
            cs.name AS Customer_Name,
            inv.id AS Invoice_ID,
            inv.create_date AS Invoice_Date,
            inv.total_amount AS Invoice_Amount,
            IFNULL(SUM(p.amount), 0) AS Payment_Received,
            (inv.total_amount - IFNULL(SUM(p.amount), 0)) AS Balance,
            CASE 
                WHEN DATEDIFF(CURDATE(), inv.create_date) BETWEEN 0 AND 15 THEN '0-15 Days'
                WHEN DATEDIFF(CURDATE(), inv.create_date) BETWEEN 16 AND 30 THEN '16-30 Days'
                WHEN DATEDIFF(CURDATE(), inv.create_date) BETWEEN 31 AND 60 THEN '31-60 Days'
                WHEN DATEDIFF(CURDATE(), inv.create_date) BETWEEN 61 AND 90 THEN '61-90 Days'
                ELSE '90+ Days'
            END AS Aging_Slab
        FROM 
            ".TABLE_INVOICE." inv
        LEFT JOIN 
            ".TABLE_PAYMENT." p ON CAST(inv.id AS CHAR) = p.invoice_reference_code
        JOIN 
            customer_setup cs ON inv.customer_id = cs.id
        WHERE 
            inv.status NOT IN ('Paid', 'Cancel')
            $filter
        GROUP BY 
            inv.id, Aging_Slab
        ORDER BY 
            cs.name ASC, Aging_Slab
    ";
echo $query;
    $rows = $db->fetch_all_array($query);

    if ($db->affected_rows > 0) {
        return $rows;
    } else {
        return false;
    }
}
  
  /**********************************************************
 * This function returns each customer's latest invoice
 * Columns: S.No, Date, Invoice #, Customer, Status, Price, Sub Total, Days Passed
 **********************************************************/
public static function GetCustomerLastInvoiceList($filter = "")
{
    global $db;

    $query = "
        SELECT 
            inv.id,
            inv.create_date AS date,
            inv.code AS invoice_no,
            cust.name AS customer_name,
            inv.status,
            inv.total_amount AS price,
            (inv.total_amount + inv.discount_price + inv.add_discount_price) AS sub_total,
            DATEDIFF(NOW(), inv.create_date) AS days_passed
        FROM " . TABLE_INVOICE . " AS inv
        INNER JOIN " . TABLE_CUSTOMERSETUP . " AS cust 
            ON inv.customer_id = cust.id
        INNER JOIN (
            SELECT 
                customer_id, 
                MAX(create_date) AS last_date
            FROM " . TABLE_INVOICE . "
            GROUP BY customer_id
        ) AS latest
            ON inv.customer_id = latest.customer_id 
            AND inv.create_date = latest.last_date
        " . $filter . "
        ORDER BY cust.name ASC
    ";

    $rows = $db->fetch_all_array($query);
    if ($db->affected_rows > 0) {
        // Add S.No numbering for convenience
        $count = 1;
        foreach ($rows as &$r) {
            $r->sno = $count++;
        }
        return $rows;
    } else {
        return false;
    }
}

/**********************************************************
/ Customer Sales Segregation Report
/ Buckets: 10k, 20k, 30k, 50k, 100k, 200k, 300k, 400k, 500k, Above
/ NOTE: $filter MUST include JOIN customer + WHERE + GROUP BY inv.customer_id
 **********************************************************/
public static function GetCustomerSalesSegregationReport($filter="",$pageno="null",$pagesize="null") {

	global $db;

	$query = "
		SELECT
			t.*,
			CASE
				WHEN t.total_amount <= 10000 THEN 'Up to 10,000'
				WHEN t.total_amount <= 20000 THEN '10,001 to 20,000'
				WHEN t.total_amount <= 30000 THEN '20,001 to 30,000'
				WHEN t.total_amount <= 50000 THEN '30,001 to 50,000'
				WHEN t.total_amount <= 100000 THEN '50,001 to 100,000'
				WHEN t.total_amount <= 200000 THEN '100,001 to 200,000'
				WHEN t.total_amount <= 300000 THEN '200,001 to 300,000'
				WHEN t.total_amount <= 400000 THEN '300,001 to 400,000'
				WHEN t.total_amount <= 500000 THEN '400,001 to 500,000'
				ELSE 'Above 500,000'
			END AS sales_slab,
			CASE
				WHEN t.total_amount <= 10000 THEN 1
				WHEN t.total_amount <= 20000 THEN 2
				WHEN t.total_amount <= 30000 THEN 3
				WHEN t.total_amount <= 50000 THEN 4
				WHEN t.total_amount <= 100000 THEN 5
				WHEN t.total_amount <= 200000 THEN 6
				WHEN t.total_amount <= 300000 THEN 7
				WHEN t.total_amount <= 400000 THEN 8
				WHEN t.total_amount <= 500000 THEN 9
				ELSE 10
			END AS slab_order
		FROM (
			SELECT
				inv.customer_id,
				cust.name AS customer_name,
				cust.location,
				cust.contact,
				cust.gst,
				cust.ntn,
				SUM(inv.discount_price) AS discount_price,
				SUM(inv.total_amount) AS total_amount,
				COUNT(inv.id) AS total_invoices
			FROM ".TABLE_INVOICE." AS inv
			".$filter."
		) AS t
		ORDER BY slab_order, t.total_amount DESC, t.customer_name
	";

	$rows = $db->fetch_all_array($query,$pageno,$pagesize);

	if($db->affected_rows > 0){
		return $rows;
	}else{
		return false;
	}
}//GetCustomerSalesSegregationReport
  
  
  
/**********************************************************
 * Follow Up Report
 * Customers whose last invoice is >= X days old
 * Columns: Customer, Contact, Balance, Total Pending/Partial Invoices, Days Passed
 **********************************************************/
public static function GetCustomerFollowUpReport($days = 7, $filter = "", $pageno="null", $pagesize="null")
{
    global $db;

    $days = (int)$days;
    if ($days <= 0) { $days = 7; }

    $query = "
        SELECT
            cust.id AS customer_id,
            cust.name AS customer_name,
            cust.contact AS customer_contact,

            latest_inv.last_invoice_date,
            DATEDIFF(CURDATE(), DATE(latest_inv.last_invoice_date)) AS days_since_last_invoice,

            IFNULL(inv_sum.total_invoiced, 0) AS total_invoiced,
            IFNULL(pay_sum.total_received, 0) AS total_received,

            (IFNULL(inv_sum.total_invoiced, 0) - IFNULL(pay_sum.total_received, 0)) AS balance,

            IFNULL(pending_sum.total_invoices, 0) AS total_invoices

        FROM " . TABLE_CUSTOMERSETUP . " AS cust

        /* Latest invoice date per customer */
        INNER JOIN (
            SELECT 
                customer_id,
                MAX(create_date) AS last_invoice_date
            FROM " . TABLE_INVOICE . "
            WHERE status NOT IN ('Draft', 'Cancel')
            GROUP BY customer_id
        ) AS latest_inv
            ON latest_inv.customer_id = cust.id

        /* Sum of all invoices per customer */
        LEFT JOIN (
            SELECT
                customer_id,
                SUM(total_amount) AS total_invoiced
            FROM " . TABLE_INVOICE . "
            WHERE status NOT IN ('Draft', 'Cancel')
            GROUP BY customer_id
        ) AS inv_sum
            ON inv_sum.customer_id = cust.id

        /* Sum of all received payments per customer */
        LEFT JOIN (
            SELECT
                customer_id,
                SUM(amount) AS total_received
            FROM " . TABLE_PAYMENT . "
            WHERE status <> 'Cancel'
            GROUP BY customer_id
        ) AS pay_sum
            ON pay_sum.customer_id = cust.id

        /* Count of Pending + Partial Paid invoices */
        LEFT JOIN (
            SELECT
                customer_id,
                COUNT(id) AS total_invoices
            FROM " . TABLE_INVOICE . "
            WHERE status IN ('Pending', 'Partial Paid', 'Partial paid')
            GROUP BY customer_id
        ) AS pending_sum
            ON pending_sum.customer_id = cust.id

        WHERE
            cust.status = 'active'
            AND DATEDIFF(CURDATE(), DATE(latest_inv.last_invoice_date)) >= $days
            $filter

        ORDER BY days_since_last_invoice DESC, cust.name ASC
    ";

    $rows = $db->fetch_all_array($query, $pageno, $pagesize);

    if ($db->affected_rows > 0) {
        return $rows;
    } else {
        return false;
    }
}
  
  /**********************************************************
 * Customer Pending Balance Report (High to Low)
 * Columns: Customer, Pending Invoices, Last Payment Amount, Days Since Last Payment, Balance
 **********************************************************/
public static function GetCustomerPendingBalanceReport($filter = "", $pageno="null", $pagesize="null")
{
    global $db;

    $query = "
        SELECT
            cust.id AS customer_id,
            cust.name AS customer_name,

            IFNULL(pending_sum.pending_invoices, 0) AS pending_invoices,

            IFNULL(last_pay.last_payment_amount, 0) AS last_payment_amount,
            CASE 
    WHEN last_pay.last_payment_date IS NULL THEN 0
    ELSE DATEDIFF(CURDATE(), DATE(last_pay.last_payment_date))
END AS days_since_last_payment,

            IFNULL(inv_sum.total_invoiced, 0) AS total_invoiced,
            IFNULL(pay_sum.total_received, 0) AS total_received,

            (IFNULL(inv_sum.total_invoiced, 0) - IFNULL(pay_sum.total_received, 0)) AS balance

        FROM " . TABLE_CUSTOMERSETUP . " AS cust

        /* total invoiced */
        LEFT JOIN (
            SELECT customer_id, SUM(total_amount) AS total_invoiced
            FROM " . TABLE_INVOICE . "
            WHERE status NOT IN ('Draft', 'Cancel')
            GROUP BY customer_id
        ) AS inv_sum ON inv_sum.customer_id = cust.id

        /* total received */
        LEFT JOIN (
            SELECT customer_id, SUM(amount) AS total_received
            FROM " . TABLE_PAYMENT . "
            WHERE status <> 'Cancel'
            GROUP BY customer_id
        ) AS pay_sum ON pay_sum.customer_id = cust.id

        /* pending invoices count */
        LEFT JOIN (
            SELECT customer_id, COUNT(id) AS pending_invoices
            FROM " . TABLE_INVOICE . "
            WHERE status IN ('Pending', 'Partial Paid', 'Partial paid')
            GROUP BY customer_id
        ) AS pending_sum ON pending_sum.customer_id = cust.id

        /* last payment amount + date */
        LEFT JOIN (
            SELECT p1.customer_id,
                   p1.amount AS last_payment_amount,
                   p1.create_date AS last_payment_date
            FROM " . TABLE_PAYMENT . " p1
            INNER JOIN (
                SELECT customer_id, MAX(create_date) AS max_date
                FROM " . TABLE_PAYMENT . "
                WHERE status <> 'Cancel'
                GROUP BY customer_id
            ) p2 ON p1.customer_id = p2.customer_id AND p1.create_date = p2.max_date
            WHERE p1.status <> 'Cancel'
        ) AS last_pay ON last_pay.customer_id = cust.id

        WHERE cust.status = 'active'
        $filter

        HAVING balance > 0

        ORDER BY balance DESC, cust.name ASC
    ";

    $rows = $db->fetch_all_array($query, $pageno, $pagesize);

    if ($db->affected_rows > 0) {
        return $rows;
    } else {
        return false;
    }
}
  
  /**********************************************************
 * Multiple Pending Invoices Report
 * Customers with more than 1 pending invoice
 * Columns: Customer Code, Customer Name, Contact, Oldest Pending Invoice Date,
 *          Pending Invoices Count, Received Against Pending, Remaining Balance
 **********************************************************/
public static function GetCustomerMultiplePendingInvoicesReport($minPending = 2, $filter = "", $pageno="null", $pagesize="null")
{
    global $db;

    $minPending = (int)$minPending;
    if ($minPending <= 1) { $minPending = 2; }

    $query = "
        SELECT 
            cust.id AS customer_id,
            cust.code AS customer_code,
            cust.name AS customer_name,
            cust.contact AS customer_contact,

            pend.oldest_pending_date,
            pend.pending_invoices,
            IFNULL(pend.total_pending_amount, 0) AS total_pending_amount,

            IFNULL(pay.total_received_against_pending, 0) AS total_received_against_pending,

            (IFNULL(pend.total_pending_amount, 0) - IFNULL(pay.total_received_against_pending, 0)) AS remaining_balance

        FROM " . TABLE_CUSTOMERSETUP . " AS cust

        /* Pending Invoice Summary */
        INNER JOIN (
            SELECT 
                customer_id,
                MIN(DATE(create_date)) AS oldest_pending_date,
                COUNT(id) AS pending_invoices,
                SUM(total_amount) AS total_pending_amount
            FROM " . TABLE_INVOICE . "
            WHERE status IN ('Pending', 'Partial Paid', 'Partial paid')
            GROUP BY customer_id
        ) AS pend ON pend.customer_id = cust.id

        /* Payments received against ONLY those pending invoices */
        LEFT JOIN (
            SELECT 
                inv.customer_id,
                SUM(p.amount) AS total_received_against_pending
            FROM " . TABLE_INVOICE . " inv
            LEFT JOIN " . TABLE_PAYMENT . " p 
                ON p.invoice_reference_code = CAST(inv.id AS CHAR)
                AND p.status <> 'Cancel'
            WHERE inv.status IN ('Pending', 'Partial Paid', 'Partial paid')
            GROUP BY inv.customer_id
        ) AS pay ON pay.customer_id = cust.id

        WHERE cust.status = 'active'
        $filter

        HAVING pend.pending_invoices >= $minPending
        ORDER BY remaining_balance DESC, pend.pending_invoices DESC, cust.name ASC
    ";

    $rows = $db->fetch_all_array($query, $pageno, $pagesize);

    if ($db->affected_rows > 0) {
        return $rows;
    } else {
        return false;
    }
}

/**********************************************************
 * Customer Monthly Comparison Report
 * Month-wise invoice count + Total Invoices + Avg Order Value + Avg Repeat Days + Total Amount
 **********************************************************/
public static function GetCustomerMonthlyComparisonReport($fromDate, $toDate, $filter = "", $pageno="null", $pagesize="null")
{
    global $db;

    // Normalize: convert to YYYY-MM-DD
    $fromDate = date("Y-m-01", strtotime($fromDate)); // first day of start month
    $toDate   = date("Y-m-t", strtotime($toDate));    // last day of end month

    // Build month list between range
    $months = [];
    $start = new DateTime($fromDate);
    $end   = new DateTime(date("Y-m-01", strtotime($toDate)));
    $end->modify('+1 month');

    $period = new DatePeriod($start, new DateInterval('P1M'), $end);
    foreach ($period as $dt) {
        $months[] = $dt->format("Y-m"); // like 2026-01
    }

    // Build dynamic SELECT for month-wise invoice counts
    $monthSelect = "";
    foreach ($months as $m) {
        $col = str_replace("-", "_", $m); // 2026_01
        $monthSelect .= "SUM(CASE WHEN DATE_FORMAT(inv.create_date,'%Y-%m') = '$m' THEN 1 ELSE 0 END) AS cnt_$col, ";
    }

    // Avg repeat days per customer within selected date range (NO window function needed)
    $avgRepeatSubQuery = "
        SELECT 
            t.customer_id,
            ROUND(AVG(t.diff_days), 2) AS avg_repeat_days
        FROM (
            SELECT 
                i1.customer_id,
                DATEDIFF(DATE(i1.create_date), (
                    SELECT MAX(DATE(i2.create_date))
                    FROM " . TABLE_INVOICE . " i2
                    WHERE i2.customer_id = i1.customer_id
                      AND DATE(i2.create_date) < DATE(i1.create_date)
                      AND DATE(i2.create_date) BETWEEN '$fromDate' AND '$toDate'
                      AND i2.status NOT IN ('Draft','Cancel')
                )) AS diff_days
            FROM " . TABLE_INVOICE . " i1
            WHERE DATE(i1.create_date) BETWEEN '$fromDate' AND '$toDate'
              AND i1.status NOT IN ('Draft','Cancel')
        ) t
        WHERE t.diff_days IS NOT NULL AND t.diff_days > 0
        GROUP BY t.customer_id
    ";

    $query = "
        SELECT
            cust.id AS customer_id,
            cust.name AS customer_name,

            $monthSelect

            COUNT(inv.id) AS total_invoices,

            SUM(inv.total_amount) AS total_amount,

            CASE 
                WHEN COUNT(inv.id) > 0 THEN ROUND(SUM(inv.total_amount) / COUNT(inv.id), 2)
                ELSE 0
            END AS avg_order_value,

            IFNULL(rep.avg_repeat_days, 0) AS avg_repeat_days

        FROM " . TABLE_CUSTOMERSETUP . " cust

        LEFT JOIN " . TABLE_INVOICE . " inv
            ON inv.customer_id = cust.id
            AND inv.status NOT IN ('Draft','Cancel')
            AND DATE(inv.create_date) BETWEEN '$fromDate' AND '$toDate'

        LEFT JOIN ( $avgRepeatSubQuery ) rep
            ON rep.customer_id = cust.id

        WHERE cust.status = 'active'
        $filter

        GROUP BY cust.id
        ORDER BY total_invoices DESC, cust.name ASC
    ";

    $rows = $db->fetch_all_array($query, $pageno, $pagesize);

    if ($db->affected_rows > 0) {
        return [
            "months" => $months,
            "rows"   => $rows
        ];
    } else {
        return false;
    }
}
  
  public static function GetInvoiceProductQtyMap($invoiceId)
{
    global $db;
    $map = array();

    $rows = $db->fetch_all_array("
        SELECT product_id, SUM(product_qty) AS qty
        FROM " . TABLE_INVOICE_PRODUCT . "
        WHERE invoice_id = '" . (int)$invoiceId . "'
        GROUP BY product_id
    ");

    if ($rows && $db->affected_rows > 0) {
        foreach ($rows as $row) {
            $map[(int)$row->product_id] = (int)$row->qty;
        }
    }

    return $map;
}

public static function GetInvoiceStockUsageQtyMap($invoiceId)
{
    global $db;
    $map = array();

    $rows = $db->fetch_all_array("
        SELECT product_id, SUM(qty_used) AS qty
        FROM " . TABLE_INVOICE_STOCK_USAGE . "
        WHERE invoice_id = '" . (int)$invoiceId . "'
        GROUP BY product_id
    ");

    if ($rows && $db->affected_rows > 0) {
        foreach ($rows as $row) {
            $map[(int)$row->product_id] = (int)$row->qty;
        }
    }

    return $map;
}

public static function GetProductRemainingStock($productId)
{
    global $db;

    $row = $db->query_first("
        SELECT SUM(remaining_qty) AS rem
        FROM " . TABLE_STOCK . "
        WHERE product_id = '" . (int)$productId . "'
          AND remaining_qty > 0
    ");

    return ($row && isset($row->rem) && $row->rem !== null) ? (int)$row->rem : 0;
}

public static function FallbackReAddMissingStock($productId, $qtyToAdd)
{
    global $db;

    $productId = (int)$productId;
    $qtyToAdd  = (int)$qtyToAdd;

    if ($productId <= 0 || $qtyToAdd <= 0) {
        return true;
    }

    // use latest stock row for this product
    $stockRow = $db->query_first("
        SELECT *
        FROM " . TABLE_STOCK . "
        WHERE product_id = '" . $productId . "'
        ORDER BY datetime DESC, id DESC
        LIMIT 1
    ");

    if ($stockRow && !empty($stockRow->id)) {
        $newRemaining = (int)$stockRow->remaining_qty + $qtyToAdd;
        $newDeducted  = (int)$stockRow->deducted_qty - $qtyToAdd;

        if ($newDeducted < 0) {
            $newDeducted = 0;
        }

        $ok = $db->query_update(TABLE_STOCK, array(
            'remaining_qty' => $newRemaining,
            'deducted_qty'  => $newDeducted
        ), "id='" . (int)$stockRow->id . "'");

        return $ok ? true : false;
    }

    // if no stock row exists at all, insert fallback row
    $insertData = array(
        'product_id'       => $productId,
        'purchase_id'      => 0,
        'purchase_item_id' => 0,
        'received_qty'     => $qtyToAdd,
        'remaining_qty'    => $qtyToAdd,
        'deducted_qty'     => 0,
        'unit_cost'        => 0,
        'datetime'         => 'NOW()'
    );

    $newId = $db->query_insert(TABLE_STOCK, $insertData);
    return $newId ? true : false;
}
  
/**********************************************************
 * Customer Balance Summary Report
 * One row per customer:
 * Customer Name, Last Invoice Date, Last Payment Date, Last Payment Amount, Remaining Balance
 * Cutoff: up to $dateTo (Date To)
 * Filters: $catCondition (same pattern as existing report) + optional customerId
 **********************************************************/
public static function GetCustomerBalanceSummaryReport($dateTo, $catCondition = "", $customerId = "All")
{
    global $db;

    $dateTo = date("Y-m-d", strtotime($dateTo));
    $customerCondition = ($customerId !== "All" && $customerId != "") ? " AND cs.id = '$customerId' " : "";

    $query = "
        SELECT
            cs.id AS customer_id,
            cs.name AS customer_name,

            inv.last_invoice_date,
            pay.last_payment_date,
            pay.last_payment_amount,

            /* Customer Owes = (Invoices + opening balance) - Payments */
            (IFNULL(inv.total_invoice_amount,0) + IFNULL(cs.balance,0) - IFNULL(pay.total_payment_amount,0)) AS remaining_balance

        FROM customer_setup cs

        LEFT JOIN (
            SELECT
                customer_id,
                SUM(total_amount) AS total_invoice_amount,
                MAX(DATE(create_date)) AS last_invoice_date
            FROM invoice
            WHERE status NOT IN ('Draft','Cancel')
              AND DATE(create_date) <= '$dateTo'
            GROUP BY customer_id
        ) inv ON inv.customer_id = cs.id

        LEFT JOIN (
            SELECT
                p.customer_id,
                SUM(p.amount) AS total_payment_amount,
                MAX(DATE(p.create_date)) AS last_payment_date,
                (
                    SELECT p2.amount
                    FROM payment p2
                    WHERE p2.customer_id = p.customer_id
                      AND p2.status <> 'Cancel'
                      AND DATE(p2.create_date) <= '$dateTo'
                    ORDER BY p2.create_date DESC, p2.id DESC
                    LIMIT 1
                ) AS last_payment_amount
            FROM payment p
            WHERE p.status <> 'Cancel'
              AND DATE(p.create_date) <= '$dateTo'
            GROUP BY p.customer_id
        ) pay ON pay.customer_id = cs.id

        WHERE cs.status = 'active'
        $catCondition
        $customerCondition
        
        HAVING remaining_balance > 0
        ORDER BY remaining_balance DESC, cs.name ASC
    ";

    $rows = $db->fetch_all_array($query);
    if ($db->affected_rows > 0) {
        return $rows;
    }
    return false;
}




	public static function GetNumRows() { global $db; return $db->GetNumRows(); }
	public static function GetNumberOfPages() { global $db; return $db->GetNumberOfPages(); }
		
	
}

?>
