<?php

class PurchaseSetup {

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

    /*************************************************
    / This function add new Purchase Item
     *************************************************/

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

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

    } // AddPurchaseItem

    /*************************************************
    / This function update Purchase
    *************************************************/
    
    public static function UpdatePurchaseSetup($pid,$data) {
        global $db;
    //  echo $pid;
    //  echo $data;
        $id = $db->query_update(TABLE_PURCHASE,$data,"id='$pid'");
   //   echo $id;
    //  echo $db->last_query;

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

    /*************************************************
    / Get Purchase Setup by ID
    *************************************************/
    
    public static function GetPurchaseSetupById($id) {
        global $db;
        $query = "SELECT * from ".TABLE_PURCHASE." WHERE id ='$id'";
      //	echo $query;
        $rows = $db->query_first($query);
    // 	echo "<pre>";
    //    print_r($rows);
     //   echo "</pre>";
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
    }//PurchaseSetup

    /*************************************************
    / Get Purchase Items
    *************************************************/

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

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

    /*************************************************
    / Check if Purchase Exists
    *************************************************/
    
    public static function CheckPurchase($name, $id) {
        global $db;
        $query = "SELECT * from ".TABLE_PURCHASE." WHERE name ='$name' && id <> '$id'";
        $rows = $db->query_first($query);
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
    }//CheckPurchase
    
    /*************************************************
    / Get Purchase Setup List
    *************************************************/
        
    public static function GetPurchaseSetupList($filter="", $pageno="null", $pagesize="null") {
        global $db;
    	// $query = "SELECT * FROM " . TABLE_PURCHASE . " " . $filter . " ORDER BY create_date DESC";  // Sort by create_date in ascending order
      $query = "SELECT * FROM " . TABLE_PURCHASE . " " . $filter . " ORDER BY create_date DESC";  // Sort by create_date in ascending order
        $rows = $db->fetch_all_array($query,$pageno,$pagesize);
      
    // echo "<script>console.log(" . json_encode($query) . ");</script>";
 //     echo '<pre>';
//print_r($rows);
//echo '</pre>';
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
    }//GetPurchaseSetupList
  
  
  /*************************************************
    / Get Purchase Setup List for purchase code generation
    *************************************************/
        
    public static function GetPurchaseSetupList1($filter="", $pageno="null", $pagesize="null") {
        global $db;
    	// $query = "SELECT * FROM " . TABLE_PURCHASE . " " . $filter . " ORDER BY create_date DESC";  // Sort by create_date in ascending order
      $query = "SELECT * FROM " . TABLE_PURCHASE . " " . $filter;  // Sort by create_date in ascending order
        $rows = $db->fetch_all_array($query,$pageno,$pagesize);
      
     // echo $query;
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
    }//GetPurchaseSetupList
     /*************************************************
    / Purchase Report
    *************************************************/

   public static function GetSupplierPurchaseReport($dateFromFormatted, $dateToFormatted, $supplier_id = 'All', $sales_tax_filter)
{
    global $db;
 
    // Query with joins for purchase items and ingredient details
    $query = "
        SELECT 
            p.id AS purchase_id,
            p.code,
            p.qty,
            p.total_ex_amount,
            p.is_tax,
            p.tax,
            p.tax_price,
            p.total_amount,
            p.description,
            p.create_date,
            p.purchase_invoice_no,
            p.purchase_invoice_date,
            p.delivery_challan_no,
            p.delivery_challan_date,
            p.po_no,
            p.po_no_date,
            s.name AS supplier_name,
            s.id AS supplier_id,
            pi.ing_id,
            pi.qty AS item_qty,
            pi.remaining_qty AS item_remaining_qty,
            pi.quant AS item_quant,
            pi.uom AS item_uom,
            pi.rate AS item_rate,
            pi.tax AS item_tax,
            pi.tax_amount AS item_tax_amount,
            pi.amount AS item_amount,
            i.name AS ingredient_name,
            i.description AS ingredient_description,
            i.unit AS ingredient_unit
        FROM 
            " . TABLE_PURCHASE . " AS p
        LEFT JOIN 
            " . TABLE_SUPPLIERSETUP . " AS s ON p.supplier_id = s.id
        LEFT JOIN 
            " . TABLE_PURCHASE_ITEM . " AS pi ON p.id = pi.purchase_id
        LEFT JOIN 
            " . TABLE_INGSETUP . " AS i ON pi.ing_id = i.id
        WHERE 
            DATE_FORMAT(p.create_date, '%Y-%m-%d') BETWEEN '$dateFromFormatted' AND '$dateToFormatted'";
    
    // Add supplier filter if a specific supplier is selected
    if ($supplier_id !== 'All') {
        $query .= " AND p.supplier_id = '$supplier_id'";
    }
   if ($sales_tax_filter == 'zero') {
    $query .= " AND pi.tax_amount = 0";
} elseif ($sales_tax_filter == 'nonzero') {
    $query .= " AND pi.tax_amount > 0";
}
    

    // Order by supplier name and purchase date
    $query .= " 
        ORDER BY s.name ASC, p.create_date ASC, p.id ASC";

    // Execute the query
    $results = $db->fetch_all_array($query);
 
  
    // Organize results into a structured array with nested items
    $purchases = [];
    foreach ($results as $row) {
        $purchaseId = $row->purchase_id;

        // Initialize the purchase record if not already set
        if (!isset($purchases[$purchaseId])) {
            $purchases[$purchaseId] = [
                'purchase_id' => $row->purchase_id,
                'code' => $row->code,
                'qty' => $row->qty,
                'total_ex_amount' => $row->total_ex_amount,
                'is_tax' => $row->is_tax,
                'tax' => $row->tax,
                'tax_price' => $row->tax_price,
                'total_amount' => $row->total_amount,
                'description' => $row->description,
                'create_date' => $row->create_date,
                'purchase_invoice_no' => $row->purchase_invoice_no,
                'purchase_invoice_date' => $row->purchase_invoice_date,
                'delivery_challan_no' => $row->delivery_challan_no,
                'delivery_challan_date' => $row->delivery_challan_date,
                'po_no' => $row->po_no,
                'po_no_date' => $row->po_no_date,
                'supplier_name' => $row->supplier_name,
                'supplier_id' => $row->supplier_id,
                'items' => []  // Initialize an empty array for items
            ];
        }

        // Add each item under its respective purchase, including ingredient details
        $purchases[$purchaseId]['items'][] = [
            'ing_id' => $row->ing_id,
            'qty' => $row->item_qty,
            'remaining_qty' => $row->item_remaining_qty,
            'quant' => $row->item_quant,
            'uom' => $row->item_uom,
            'rate' => $row->item_rate,
            'tax' => $row->item_tax,
            'tax_amount' => $row->item_tax_amount,
            'amount' => $row->item_amount,
            'ingredient_name' => $row->ingredient_name,
            'ingredient_description' => $row->ingredient_description,
            'ingredient_unit' => $row->ingredient_unit
        ];
    }

    // Return the organized data structure
    return $purchases;
}



    /*************************************************
    / Delete Purchase Items
    *************************************************/

    public static function DeletePurchaseItems($purchase_id) {
        global $db;
        $query = "Delete from " . TABLE_PURCHASE_ITEM . " WHERE purchase_id='$purchase_id'";
        $rows = $db->query_first($query);
        if($db->affected_rows > 0){
            return true;
        }else{
            return false;
        }
    }//DeletePurchaseItems

    /*************************************************
    / Get FIFO Items based on remaining quantity
    *************************************************/
    public static function GetFIFOItems($ingredient_id, $required_qty) {
        global $db;
        $query = "SELECT * FROM ".TABLE_PURCHASE_ITEM." 
                  WHERE ing_id = '$ingredient_id' 
                  AND remaining_qty > 0 
                  ORDER BY purchase_date ASC";
        return $db->fetch_all_array($query);
    }

    /*************************************************
    / Update remaining quantity in purchase_items
    *************************************************/
    public static function UpdateRemainingQty($purchase_item_id, $used_qty) {
        global $db;
        $query = "UPDATE ".TABLE_PURCHASE_ITEM." 
                  SET remaining_qty = remaining_qty - $used_qty 
                  WHERE id = '$purchase_item_id'";
        $db->query($query);
    }

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

?>
