<?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 list for status report
**********************************************************/
public static function GetInvoiceReportList($filter = "", $pageno = "null", $pagesize = "null")
{
    global $db;

    $query = "
        SELECT 
            -- invoice core
            inv.id               AS invoice_id,
            inv.code,
            inv.customer_id,

            -- discounts
            inv.discount         AS invoice_discount,
            inv.discount_price   AS invoice_discount_price,

            -- tax flags & amounts (ALIased to avoid collision)
            inv.is_tax           AS invoice_is_tax,
            inv.tax              AS invoice_tax_rate,
            inv.tax_price        AS invoice_tax_amount,
            inv.adv_tax          AS invoice_adv_tax_rate,
            inv.adv_tax_price    AS invoice_adv_tax_amount,

            -- totals & dues
            inv.total_amount     AS invoice_total_amount,
            inv.payment_due      AS invoice_payment_due,

            -- meta
            inv.sales_person_id,
            inv.status           AS invoice_status,
            inv.create_date,
            inv.validate_date,
            inv.datetime         AS invoice_datetime,
            inv.po_number,

            -- customer
            cs.name              AS customer_name,
            cs.category_id       AS cscategory_id,
            cs.ntn               AS customer_ntn,
            cs.strn              AS customer_strn,

            -- >>> payments: ONLY payments tagged to THIS invoice
            COALESCE((
                SELECT SUM(p.amount)
                FROM " . TABLE_PAYMENT . " AS p
                WHERE p.invoice_reference_code = inv.id
                  AND p.status <> 'Cancel'
            ), 0) AS payment_received

        FROM " . TABLE_INVOICE . " AS inv
        INNER JOIN " . TABLE_CUSTOMERSETUP . " AS cs 
                ON inv.customer_id = cs.id
        " . $filter . "
    ";

    // echo $query; // debug if needed
    $rows = $db->fetch_all_array($query, $pageno, $pagesize);
    return ($db->affected_rows > 0) ? $rows : false;
}




	/**********************************************************
	/ 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

	/**********************************************************
	/ 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 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

	/**********************************************************
	/ 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 GetInvoiceProductByInvoiceIdAndProductId($invoice_id, $product_id) {
        global $db;

        // Using the constant TABLE_INVOICE_PRODUCT to reference the invoice product table
        $sql = "SELECT * FROM " . TABLE_INVOICE_PRODUCT . " 
                WHERE invoice_id = :invoice_id AND product_id = :product_id";
        
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':invoice_id', $invoice_id, PDO::PARAM_INT);
        $stmt->bindParam(':product_id', $product_id, PDO::PARAM_INT);
        $stmt->execute();

        // Fetch and return the invoice product record
        return $stmt->fetch(PDO::FETCH_OBJ);
    }
  
  public static function GetInvoicePaymentReportListsForOwner123($filter = "", $pageno = "null", $pagesize = "null") {

    global $db;
    
    // Adjusted the query to include `adv_tax`, `adv_tax_price`, and `po_number` for the new web app
    $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, inv.adv_tax, inv.adv_tax_price, inv.po_number
        from ".TABLE_INVOICE." AS inv ". $filter;
    
    // Execute the query and fetch the results
    $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
 // Invoice
  
/**********************************************************
 * Detailed Sales Report (Invoice + Product level)
 * Uses: invoice, invoice_product, customer_setup, customer_category, product_setup
 **********************************************************/
public static function GetInvoiceSalesDetailReport($filter = "", $pageno = "null", $pagesize = "null")
{
    global $db;

    $query = "
        SELECT 
            -- Invoice core
            inv.id                    AS invoice_id,
            inv.code                  AS invoice_code,
            inv.customer_id           AS customer_id,
            inv.create_date           AS create_date,
            inv.po_number             AS po_number,
            inv.discount              AS invoice_discount,
            inv.discount_price        AS invoice_discount_amount,
            inv.is_tax                AS invoice_is_tax,
            inv.tax                   AS invoice_tax,
            inv.tax_price             AS invoice_tax_amount,
            inv.adv_tax               AS invoice_adv_tax_rate,
            inv.adv_tax_price         AS invoice_adv_tax_amount,
            inv.total_amount          AS invoice_total_amount,
            inv.payment_due           AS invoice_payment_due,
            inv.status                AS invoice_status,

            -- Customer
            cs.name                   AS customer_name,
            cs.category_id            AS customer_category_id,
            cs.advance_tax            AS customer_adv_tax_enabled,
            cs.advance_tax_perc       AS customer_adv_tax_perc,

            -- Customer Category
            cc.name                   AS customer_category_name,

            -- Invoice Product
            invPr.id                  AS invoice_product_id,
            invPr.product_id          AS product_id,
            invPr.product_qty         AS product_qty,
            invPr.unit_sale_price     AS unit_sale_price,
            invPr.carton_piece        AS carton_piece,

            -- Product
            ps.name                   AS product_name,
            ps.weight                 AS product_weight,
            ps.unit                   AS product_unit

        FROM " . TABLE_INVOICE . " AS inv
        INNER JOIN " . TABLE_INVOICE_PRODUCT . " AS invPr 
            ON inv.id = invPr.invoice_id
        INNER JOIN " . TABLE_CUSTOMERSETUP . " AS cs 
            ON inv.customer_id = cs.id
        LEFT JOIN customer_category AS cc 
            ON cs.category_id = cc.id
        INNER JOIN " . TABLE_PRODUCTSETUP . " AS ps 
            ON invPr.product_id = ps.id
        " . $filter;

    // echo $query; exit;

    $rows = $db->fetch_all_array($query, $pageno, $pagesize);
    if ($db->affected_rows > 0) {
        return $rows;
    } else {
        return false;
    }
  
 
}
  
/**********************************************************
 * Product Wise Sales Report
 * Uses: invoice, invoice_product, product_setup
 * Shows product sold quantity in pieces and cartons
 **********************************************************/
public static function GetProductWiseSalesReport($filter = "", $pageno = "null", $pagesize = "null")
{
    global $db;

    $query = "
        SELECT 
            invPr.product_id,

            ps.code AS product_code,
            ps.name AS product_name,
            ps.weight AS product_weight,
            ps.unit AS product_unit,

            COALESCE(NULLIF(invPr.carton_piece, 0), NULLIF(ps.carton_pieces, 0), 1) AS carton_piece,

            SUM(invPr.product_qty) AS total_pieces,

            FLOOR(
                SUM(invPr.product_qty) / COALESCE(NULLIF(invPr.carton_piece, 0), NULLIF(ps.carton_pieces, 0), 1)
            ) AS full_cartons,

            MOD(
                SUM(invPr.product_qty),
                COALESCE(NULLIF(invPr.carton_piece, 0), NULLIF(ps.carton_pieces, 0), 1)
            ) AS loose_pieces,

            ROUND(
                SUM(invPr.product_qty) / COALESCE(NULLIF(invPr.carton_piece, 0), NULLIF(ps.carton_pieces, 0), 1),
                2
            ) AS carton_decimal,

            SUM(invPr.product_qty * invPr.unit_sale_price) AS gross_sale_amount

        FROM " . TABLE_INVOICE . " AS inv
        INNER JOIN " . TABLE_INVOICE_PRODUCT . " AS invPr 
            ON inv.id = invPr.invoice_id
        INNER JOIN " . TABLE_PRODUCTSETUP . " AS ps 
            ON invPr.product_id = ps.id

        " . $filter;

    // echo $query; exit;

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

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

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

  
  

  
  
}

?>
