<?php

class SupplierSetup {

	/*************************************************
	/ This function add new Supplier Setup
	*************************************************/
	
	public static function AddSupplierSetup($data) {
		global $db;
				
		$id = $db->query_insert(TABLE_SUPPLIERSETUP, $data);
		if($db->affected_rows > 0){
			//$db->query_insert("vrs_logs", $data);
			return $id;
		}else{
			return false;
		}
	
	} // AddSupplier

	/*************************************************
	/ This function update Supplier
	*************************************************/
	
	public static function UpdateSupplierSetup($pid,$data) {
		global $db;
				
		$id = $db->query_update(TABLE_SUPPLIERSETUP,$data,"id='$pid'");
		
		if($db->affected_rows > 0){
			//$db->query_insert("vrs_logs", $data);
			
			return $id;
		}else{
			return $id;
		}
	
	} // UpdateSupplier


	public static function GetSupplierSetupById($id) {
	
		global $db;
		$query = "SELECT * from ".TABLE_SUPPLIERSETUP." WHERE id ='$id'";
    //  echo ($query);
      
		//print($query);exit;
		$rows = $db->query_first($query);
	
                if($db->affected_rows > 0){
            //      echo "<pre>";   // makes output readable
    //    print_r($rows); 
    //    echo "</pre>";
			
                        return $rows;
                }else{
			
                        return false;
                }
     // print($rows);
	}//Supplier

	public static function GetSupplierSetupByCode($pcode) {
	
		global $db;
		$query = "SELECT * from ".TABLE_SUPPLIERSETUP." WHERE code LIKE '$pcode%'";
		//print($query);exit;
		$rows = $db->query_first($query);
	
                if($db->affected_rows > 0){
			
                        return $rows;
                }else{
			
                        return false;
                }
	}//Supplier


	public static function CheckSupplier($name, $id) {
	
		global $db;
		$query = "SELECT * from ".TABLE_SUPPLIERSETUP." WHERE name ='$name' && id <> '$id'";
		//print($query);exit;
		$rows = $db->query_first($query);
	
                if($db->affected_rows > 0){
			
                        return $rows;
                }else{
			
                        return false;
                }
	}//Supplier
	
	/**********************************************************
	/ This function will return SupplierSetup
	**********************************************************/
		
	public static function GetSupplierSetupList($filter="",$pageno="null",$pagesize="null") {
	
		global $db;
		$query = "SELECT * FROM ".TABLE_SUPPLIERSETUP." ".$filter." ORDER BY name";
      	
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
         // echo "<pre>";
//print_r($rows);
//echo "</pre>";
	
			if($db->affected_rows > 0){
			
                        return $rows;
                }else{
			
                        return false;
                }
	}//SupplierSetup
  
  /**********************************************************
	/ This function will return SupplierSetup
	**********************************************************/
		
	public static function GetSupplierSetupListfilter($filter="",$pageno="null",$pagesize="null") {
	
		global $db;
		$query = "SELECT * from ".TABLE_SUPPLIERSETUP." ". $filter;
		//print($query);exit;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
  
	
			if($db->affected_rows > 0){
			
                        return $rows;
                }else{
			
                        return false;
                }
	}//SupplierSetup
  
 
  
  
    /**********************************************************
	/ This function will return Supplier Opening Balance
	**********************************************************/
  
 public static function GetSupplierLedger($dateFrom, $dateTo, $supplierId = 'All') {
    global $db;

    // Initialize result array
    $result = [
        'opening_balance' => [],
        'ledger' => []
    ];

    // Sanitize supplier condition
    $supplierCondition = ($supplierId !== 'All') ? "AND s.id = '$supplierId'" : "";

    // Opening Balance Query
    $openingBalanceQuery = "
        SELECT 
    s.id AS supplier_id,
    s.name AS supplier_name,
    IFNULL(purchase.total_debit, 0) AS total_debit,
    IFNULL(payment.total_credit, 0) AS total_credit,
    (IFNULL(purchase.total_debit, 0) - IFNULL(payment.total_credit, 0)) AS opening_balance
FROM supplier_setup s
LEFT JOIN (
    SELECT 
        p.supplier_id,
        SUM(p.total_amount) AS total_debit
    FROM purchase p
    WHERE p.create_date < '$dateFrom'
    GROUP BY p.supplier_id
) AS purchase ON s.id = purchase.supplier_id
LEFT JOIN (
    SELECT 
        py.supplier_id,
        SUM(py.amount_payable) AS total_credit -- Use SUM to aggregate payments
    FROM purpayment py
    WHERE py.create_date < '$dateFrom'
    AND py.status <> 'Cancel' -- Filter out cancelled payments
    GROUP BY py.supplier_id
) AS payment ON s.id = payment.supplier_id
WHERE 1=1 $supplierCondition
GROUP BY s.name, s.id;

    ";
  // echo ("Opening Balance Query: " . $openingBalanceQuery);

    // Fetch Opening Balance
    $openingBalanceResult = $db->fetch_all_array($openingBalanceQuery); // Use query_first to fetch single-row result
//echo ("Opening Balance Result: " . print_r($openingBalanceResult, true));
   
    if ($openingBalanceResult && is_array($openingBalanceResult)) {
        $result['opening_balance'] = $openingBalanceResult;
    }
//echo $openingBalanceQuery;
    // Ledger Query
    $ledgerQuery = "
    -- Ledger Query
SELECT 
    s.id AS supplier_id,
    s.name AS supplier_name,
    ledger.date,
    ledger.transaction_type AS source,
    ledger.reference,
    -- Display cheque details only for payments
    CASE 
        WHEN ledger.transaction_type = 'Payment' THEN 
            CONCAT('Cheque: ', ledger.cheque_number_base, ', Date: ', DATE_FORMAT(MIN(ledger.date), '%d-%m-%Y'))
        ELSE 
            ''
    END AS cheque_details,
    -- Show debit only for purchases
    SUM(CASE WHEN ledger.transaction_type = 'Purchase' THEN ledger.debit ELSE 0 END) AS debit,
    -- Show credit only for payments
    SUM(CASE WHEN ledger.transaction_type = 'Payment' THEN ledger.credit ELSE 0 END) AS credit,
    ledger.supplier_payment_type
FROM (
    -- Purchases (debit) without grouping
    SELECT 
        p.supplier_id,
        p.create_date AS date,
        'Purchase' AS transaction_type,
        p.purchase_invoice_no AS reference,
        '' AS cheque_number_base,
        p.total_amount AS debit,
        0 AS credit,
        NULL AS supplier_payment_type
    FROM purchase p
    WHERE p.create_date BETWEEN '$dateFrom 00:00:00' AND '$dateTo 23:59:59' -- Include entries for the selected date

    UNION ALL

    -- Payments (credit) with grouping logic
    SELECT 
        py.supplier_id,
        py.cheque_date AS date,
        'Payment' AS transaction_type,
        py.code AS reference,
        SUBSTRING_INDEX(py.cheque_number, '-', 1) AS cheque_number_base,
        0 AS debit,
        py.amount_payable AS credit,
        py.supplier_payment_type
    FROM purpayment py
    WHERE py.create_date BETWEEN '$dateFrom 00:00:00' AND '$dateTo 23:59:59' -- Include entries for the selected date
    AND py.status <> 'Cancel'
) AS ledger
JOIN supplier_setup s ON ledger.supplier_id = s.id
WHERE 1=1 $supplierCondition
GROUP BY 
    CASE 
        WHEN ledger.transaction_type = 'Payment' THEN ledger.cheque_number_base
        ELSE ledger.reference
    END,
    ledger.transaction_type,
    ledger.supplier_id
ORDER BY 
    ledger.supplier_id,
    ledger.date;



";
//echo $ledgerQuery;
//	error_log("Ledger Query: " . $ledgerQuery);

    // Fetch Ledger Transactions
    $ledgerResult = $db->fetch_all_array($ledgerQuery); // Use fetch_all_array to fetch multiple rows
 //  echo ("Ledger Result: " . print_r($ledgerResult, true));

    if ($ledgerResult && is_array($ledgerResult)) {
        $result['ledger'] = $ledgerResult;
    }

    return $result;
}





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

?>
