<?php
 
class Batch_Process_Model extends CI_Model {
  
  
  function __construct() 
  {
    parent::__construct();
     //$this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
  }
  
      public function getpurchases()
  {
     $this->db->select("p.product_name,p.product_code");
     $this->db->select("spl.supp_company_name");
     $this->db->select("si.*");
     $this->db->select("s.reference_no,s.supp_invocie_no,s.date");
     $this->db->from("purchase_items si");
     $this->db->join("product p", "p.product_id = si.product_id", "inner"); 
     $this->db->join("purchases s", "s.id = si.purchase_id", "inner"); 
     $this->db->join("supplier spl", "spl.supp_id = s.supplier_id", "inner");
     $this->db->where("si.is_batch_created",0);
     $this->db->order_by("s.reference_no", "desc");
     $query = $this->db->get();  
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
  }
  	function save_batch($data_item)
	{
		$result=$this->db->insert('batch_product',$data_item);
		return $result;
	}
	public function update_product($id)
	{
	    if($id>0){
	        	$data = array(
			'is_batch_created' => 1
		);	
		$this->db->where('id', $id);
		$this->db->update('purchase_items', $data);
	    }else{
	        return false;
	    }
	}
	 public function get_batch_list_by_product_id($id){
     $this->db->select("si.*");
     $this->db->from("batch_product si");
     $this->db->where("si.product_id",$id);
     $this->db->where("si.is_deleted",0);
     $query = $this->db->get();  
     return $query->result();
    }
  	public function update_batch($id,$data)
	{
	    if($id>0){
		$this->db->where('batch_id', $id);
		return $this->db->update('batch_product', $data);
	    }else{
	        return false;
	    }
	}
    public function get_last_batch_id_using_cost($product_id,$cost)
	{
	    $this->db->select("b.batch_id");
        $this->db->from("batch_product b");
        $this->db->where("b.product_id",$product_id);
        $this->db->where("b.product_cost",$cost);
        $this->db->where("b.is_deleted",0);
        //$this->db->where("b.batch_status",1);   
        $this->db->limit(1);
        $query = $this->db->get();  
        $result = $query->row_array();
        if(isset($result['batch_id'])){
            return $result['batch_id'];
        }else{
            return false;
        }
	}
	function insert_last_batch_id($data_item)
	{
		$result=$this->db->insert('btatch_prroduct_grn',$data_item);
		return $result;
	}
	public function update_grn_item_table($id,$data)
	{
	    if($id>0){
		$this->db->where('id', $id);
		return $this->db->update('purchase_items', $data);
	    }else{
	        return false;
	    }
	}
	public function get_product_batch_details($id){
     $this->db->select("p.product_name,p.product_code");
     $this->db->select("si.*");
     $this->db->from("batch_product si");
     $this->db->join("product p", "p.product_id = si.product_id", "left"); 
     $this->db->where("si.batch_id",$id);
     $query = $this->db->get();  
     return $query->row_array();
  }
  public function auto_create_grn_batch($id){
   $grn_product_list=$this->get_grn_item_list($id);
   foreach($grn_product_list as $row){
       $final_calculated_cost_f_p=0;
       $product_price=$row['retail_price'];
       $cost_price=$row['unit_price'];
       $product_id=$row['product_id'];
       $match_batch_id=$this->get_last_batch_id_using_cost_retail($product_id,$cost_price,$product_price);
         if($row['is_new_batch']==1){
           $match_batch_id=false;
            }
         if($match_batch_id!=false){
             $batch_data=$this->get_batch_details($match_batch_id);
             $old_batch_cost=0;
             if(isset($batch_data['product_cost'])){
             $old_batch_cost=$batch_data['product_cost'];
             }
             $stock = $this->Stock_Counter_Model->get_stock_balance_batch('',$row['product_id'],$match_batch_id);
             $total_old_stock_value=$old_batch_cost*$stock;
             $total_new_stock_value=$row['unit_price']*$row['quantity'];
             
             if(($stock+$row['quantity']) == 0){
                 $new_cost = $old_batch_cost;
             }else{
                 $new_cost = ($total_old_stock_value+$total_new_stock_value)/($stock+$row['quantity']);
             }
             
             
             $data=array(
			'grn_item_id'=>$row['id'],
			'product_id'=>$product_id,
			'batch_id'=>$match_batch_id,
			'user_id'=>$this->session->userdata('ss_user_id'),
			'added_date_time'=>date("Y-m-d H:i:s"),
			'bpg_qty'=>$row['quantity'],
			'calculated_cost'=>$new_cost
		    );
		    $final_calculated_cost_f_p=$new_cost;
		    $insert_result=$this->insert_last_batch_id($data);
		    $result=$this->update_product($row['id']);
		    $batch_data=array(
		            'product_cost'=>$new_cost,
		            );
		    $this->update_batch_product_table($batch_data,$match_batch_id);
         }else{
             $this->load->model('Common_Model');
             $batch_code=$this->Common_Model->gen_ref_number('batch_id','batch_product','BTC');
             if($row['is_new_batch']==1){
                $batch_code=$row['manufacture_batch_re_no'];
            }
             $data=array(
			'grn_item_id'=>$row['id'],
			'product_id'=>$product_id,
			'batch_ref_number'=>$batch_code,	
			'product_cost'=>$cost_price,
			'product_price'=>$product_price,
			'credit_salling_price'=>$product_price,
			'wholesale_price'=>$product_price,	
			'retail_cash'=>$product_price,
			'retail_credit'=>$product_price,
			'retail_wholesale'=>$product_price,
			'user_id'=>$this->session->userdata('ss_user_id'),
		    );
		    $result=$this->save_batch($data);
		    $last_batch_id=$this->db->insert_id();
		    if($result==1){
                $result=$this->update_product($row['id']);
                $data=array(
			    'grn_item_id'=>$row['id'],
			    'product_id'=>$product_id,
			    'batch_id'=>$last_batch_id,
			    'user_id'=>$this->session->userdata('ss_user_id'),
			    'added_date_time'=>date("Y-m-d H:i:s"),
			    'bpg_qty'=>$row['quantity'],
			    'calculated_cost'=>$cost_price
		        );
		        $insert_result=$this->insert_last_batch_id($data);
            }
            $final_calculated_cost_f_p=$cost_price;
        }
        
        $data=array(
		'product_mat_price'=>$product_price,
		'product_mat_cost'=>$final_calculated_cost_f_p,
		 );
		 $insert_result=$this->update_main_product_material_table($data,$product_id);
   } //foreach end  
  }//function end
  
  private function get_grn_item_list($id=''){
     $this->db->select("i.*");
     $this->db->from("purchase_items i");
     if($id){
       $this->db->where("i.purchase_id",$id);  
     }
     $this->db->where("i.is_batch_created",0);
     $query = $this->db->get();  
     return $query->result_array();
  }
   public function get_last_batch_id_using_cost_retail($product_id,$cost,$product_price)
	{
	    $this->db->select("b.batch_id");
        $this->db->from("batch_product b");
        $this->db->where("b.product_id",$product_id);
        //$this->db->where("b.product_cost",$cost);
        //$this->db->where("b.retail_cash",$product_price);
        $this->db->where("b.is_deleted",0);
        $this->db->where("b.batch_status",1);   
        $this->db->limit(1);
        $query = $this->db->get();  
        $result = $query->row_array();
        if(isset($result['batch_id'])){
            return $result['batch_id'];
        }else{
            return false;
        }
	}
  	function update_main_product_material_table($data,$id)
	{
	 if($id>0){
		$this->db->where('product_mat_id', $id);
		return $this->db->update('product_material', $data);
	    }else{
	        return false;
	    }
	}
	function add_manual_batch_grn(){
	   $grn_product_list=$this->get_grn_item_list();
	   foreach($grn_product_list as $row){
       $product_price=$row['retail_price'];
       $cost_price=$row['unit_price'];
       $product_id=$row['product_id'];
       $match_batch_id=$this->get_last_batch_id_using_cost_retail_manual($product_id,$cost_price,$product_price);
         if($match_batch_id!=false){
             $data=array(
			'grn_item_id'=>$row['id'],
			'product_id'=>$product_id,
			'batch_id'=>$match_batch_id,
			'user_id'=>$this->session->userdata('ss_user_id'),
			'added_date_time'=>date("Y-m-d H:i:s"),
		    );
		    $insert_result=$this->insert_last_batch_id($data);
		    $result=$this->update_product($row['id']);
         }
	   }
	    
	}
	 public function get_last_batch_id_using_cost_retail_manual($product_id,$cost,$product_price){
	    $this->db->select("b.batch_id");
        $this->db->from("batch_product b");
        $this->db->where("b.product_id",$product_id);
        $this->db->where("b.product_cost",$cost);
        //$this->db->where("b.retail_cash",$product_price);
        $this->db->where("b.is_deleted",0);
        //$this->db->where("b.batch_status",1);   
        $this->db->limit(1);
        $query = $this->db->get();  
        $result = $query->row_array();
        if(isset($result['batch_id'])){
            return $result['batch_id'];
        }else{
            return false;
        }
	}
	 public function get_all_batch_list($product_id)
	{
	    $this->db->select("b.*");
        $this->db->from("batch_product b");
        $this->db->where("b.product_id",$product_id);
        $query = $this->db->get();  
        return $query->result_array();
       
	}
	 public function get_all_product_details($product_id)
	{
	    $this->db->select("b.*");
        $this->db->from("product b");
        $this->db->where("b.product_id",$product_id);
        $query = $this->db->get();  
        return $query->row_array();
	}
  	function save_batch_qty_adjesment_table($data_item)
	{
		$result=$this->db->insert('batch_stock_adjesment',$data_item);
		return $result;
	}
	public function get_product_list_for_update($start,$limit)
	{
	    $this->db->select("b.*");
        $this->db->from("product b");
        $query = $this->db->get();  
        //$this->db->limit($limit, $start);
        return $query->result_array();
	}
    //update 2021-11-23 automatic batch enable disable function with grn approval	
	public function auto_enable_disable_zero_batch_qty($grn_id){
	   $grn_product_list=$this->get_grn_all_product_list($grn_id);
       foreach($grn_product_list as $row){
       $product_id=$row['product_id']; 
       $batch_product_list=$this->get_all_batch_list($product_id);
       foreach($batch_product_list as $row2){
           $reamining_qty = $this->Stock_Counter_Model->get_stock_balance_batch('',$product_id,$row2['batch_id']);
           if($reamining_qty>0 && $row2['batch_status']==0){
                $data=array(
		            'batch_status'=>1,
		            );
		        $insert_result=$this->update_batch_product_table($data,$row2['batch_id']);
           }
           if($reamining_qty<=0 && $row2['batch_status']==1){
                $data=array(
		            'batch_status'=>0,
		            );
		        $insert_result=$this->update_batch_product_table($data,$row2['batch_id']);
           }
       }
       }
	}
	private function get_grn_all_product_list($id=''){
     $this->db->select("i.*");
     $this->db->from("purchase_items i");
     if($id){
       $this->db->where("i.purchase_id",$id);  
     }
     $query = $this->db->get();  
     return $query->result_array();
    }
    function update_batch_product_table($data,$id)
	{
	 if($id>0){
		$this->db->where('batch_id', $id);
		return $this->db->update('batch_product', $data);
	    }else{
	        return false;
	    }
	}
	
	
	 public function get_final_product_batch_list_by_product_id($id){
     $this->db->select("si.*");
     $this->db->from("production_batch_items si");
     $this->db->where("si.pbi_product_id",$id);
     $this->db->where("si.finish_status",1);
     $this->db->where("si.stock_clean",0);
     $query = $this->db->get();  
     return $query->result();
    }
    
    public function get_final_product_all_limited_batch_list_by_product_id($id){
     $this->db->select("si.*");
     $this->db->from("production_batch_items si");
     $this->db->where("si.pbi_product_id",$id);
     $this->db->where("si.qc_status",1);
     $this->db->order_by("si.pbi_id", "desc");
     $this->db->limit(25);
     $query = $this->db->get();  
     return $query->result();
    }
    
    function update_production_batch_items($data,$id)
	{
	 if($id>0){
		$this->db->where('pbi_id', $id);
		return $this->db->update('production_batch_items', $data);
	    }else{
	        return false;
	    }
	}

    public function get_final_value_added_product_batch_list_by_product_id($id){
     $this->db->select("si.*");
     $this->db->from("batch_final_product si");
     $this->db->where("si.bfp_product_id",$id);
     $this->db->where("si.bfp_status",1);
     $query = $this->db->get();  
     return $query->result();
    }
    
    public function get_batch_details($id)
	{
	    $this->db->select("b.*");
        $this->db->from("batch_product b");
        $this->db->where("b.batch_id",$id);
        $query = $this->db->get();  
        return $query->row_array();
	}
	
	public function update_batch_table($id,$data)
	{
	    if($id>0){
		$this->db->where('batch_id', $id);
		$this->db->update('batch_product', $data);
	    }else{
	        return false;
	    }
	}


}