<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Report_Stock_Model extends CI_Model {
  
  private $tableName = '';
  
  function __construct() 
  {
    /* Call the Model constructor */
    parent::__construct();
     $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
  }
  
  
  
  
  function get_purches_opening_stock($product_id,$srh_from_date='')
    {
        $this->db->select_sum('pi.quantity');
        $this->db->from('purchase_items pi');
        $this->db->join('purchases p', 'p.id=pi.purchase_id', 'left');
        $this->db->where("pi.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(p.date) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['quantity'])){
            return $result['quantity'] ;
        }else{
            return 0;
        }
    }
   function get_issued_opening_stock($product_id,$srh_from_date='')
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('production_stock_in_out p');
        $this->db->where("p.product_mat_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(p.psio_issue_on) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['quantity'])){
            return $result['quantity'] ;
        }else{
            return 0;
        }
    }
    function get_value_added_grn_opening_stock($product_id,$srh_from_date=''){
        $this->db->select_sum('p.quantity');
        $this->db->from('production_grn_intermediat_product p');
        $this->db->join('production_grn pg','pg.pgrnm_id=p.pgrnm_id','inner');
        
        
        $this->db->where("p.product_mat_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(pg.pgrnm_pick_up_on) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['quantity'])){
            return $result['quantity'] ;
        }else{
            return 0;
        }
        
    }
    
    function get_additionand_deduction_opening_stock($product_id,$srh_from_date=''){
        $this->db->select_sum('p.req_qty');
        $this->db->from('addtional_items p');
        $this->db->where("p.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(p.issue_system_date_time) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['req_qty'])){
            return $result['req_qty'] ;
        }else{
            return 0;
        }
        
    }
    
    function get_convert_product_opening_stock($product_id,$srh_from_date=''){
        $this->db->select_sum('p.pc_qty');
        $this->db->from('production_convert p');
        $this->db->where("p.pc_parduct_mat_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(p.pc_date_time) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['pc_qty'])){
            return $result['pc_qty'] ;
        }else{
            return 0;
        }
        
    }
    
    function get_damage_product_opening_stock($product_id,$srh_from_date=''){
        $this->db->select_sum('pi.pdmgitm_quantity');
       $this->db->from('product_damage_item pi');
        $this->db->join('product_damage p', 'p.pdmg_id=pi.pdmg_id', 'inner');
            $this->db->where("pi.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(p.pdmg_datetime_created) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['pdmgitm_quantity'])){
            return $result['pdmgitm_quantity'] ;
        }else{
            return 0;
        }
        
    }
    
    function get_m_transfered_opening_stock($product_id,$srh_from_date='')
    {
        $this->db->select_sum('pi.quantity');
        $this->db->from('stock_m_transfer_item pi');
        $this->db->join('stock_m_transfer_master p', 'p.stm_id=pi.stm_id', 'inner');
        $this->db->where("pi.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(p.stm_date_time) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['quantity'])){
            return $result['quantity'] ;
        }else{
            return 0;
        }
    }
    
    
    
  
   function get_transactions_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select_sum('ps.quantity');
        $this->db->select_sum('ps.stock_in');
        $this->db->select_sum('ps.stock_out');
        $this->db->select('ps.psio_issue_on');
        $this->db->select('pbi.pbi_ref_no');
        $this->db->select('s.psnm_ref_no');
        $this->db->from('production_stock_in_out ps');
        $this->db->join('production_batch_items pbi', 'pbi.pbi_id=ps.pbi_id', 'inner');
        $this->db->join('production_store_note_master s', 'ps.psnm_id=s.psnm_id', 'left');
        
        $this->db->where("ps.product_mat_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(ps.psio_issue_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(ps.psio_issue_on) >=",$srh_from_date);
		    }
		$this->db->order_by('ps.psio_issue_on','asc');
		
		$this->db->group_by('ps.psio_id');
		//$this->db->group_by('ps.pbi_id');
        $query = $this->db->get();
        return $query->result_array();
    }
  
  
  
  
   function get_purcheses_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        
        $this->db->select('p.reference_no,p.date');
        $this->db->select('pi.*');
       $this->db->from('purchase_items pi');
        $this->db->join('purchases p', 'p.id=pi.purchase_id', 'left');
        $this->db->where("pi.product_id",$product_id); 
		if($srh_to_date){
		        $this->db->where("date(p.date) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(p.date) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        return $query->result_array();
    }
  
  
  
   function get_batch_list($product_id='',$srh_from_date='',$srh_to_date='')
    {
        
        
        $this->db->select('pi.*,p.product_code,p.product_name');
       $this->db->from('production_batch_items pi');
       $this->db->join('product p','pi.pbi_product_id=p.product_id','inner');
       if($product_id){
        $this->db->where("pi.pbi_product_id",$product_id); 
       }
		if($srh_to_date){
		        $this->db->where("date(pi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pi.prodcution_start_on) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        return $query->result_array();
    }
  
  
   function getManufacturedProductList($cat_id=''){
      $this->db->select('pc.cat_name,psc.sub_cat_name,u.unit_code');
      $this->db->select('p.product_code,p.product_name,p.product_id');
      $this->db->from('product p');
      $this->db->join('product_category pc','pc.cat_id=p.cat_id','inner');
      $this->db->join('product_sub_category psc','psc.sub_cat_id=p.sub_cat_id','left');
      $this->db->join('mstr_unit u','u.unit_id=p.product_unit','inner');
      if($cat_id){
        $this->db->where('p.cat_id',$cat_id);  
      }
      $query = $this->db->get();
      return $query->result_array();
  }
  
  function getBatchIngreedientList($batch_id=''){
     $this->db->select('pbsi.*');
     $this->db->select('u.unit_code');
     $this->db->select('p.product_mat_code,p.product_mat_name,p.product_mat_id');
     $this->db->from('production_batch_sub_items pbsi');
     $this->db->join('product_material p','pbsi.pbsi_product_mat_id=p.product_mat_id','inner');
      $this->db->join('mstr_unit u','u.unit_id=p.product_mat_unit','inner');
      $this->db->where('pbsi.pbi_id',$batch_id); 
      $query = $this->db->get();
      return $query->result_array();
  }
  
  
  
  function getBatchAddIngreedientList($batch_id){
       $this->db->select('ai.*');
     $this->db->select('u.unit_code');
     $this->db->select('b.product_cost');
     $this->db->select('p.product_mat_code,p.product_mat_name,p.product_mat_id');
     $this->db->from('addtional_items ai');
     $this->db->join('product_material p','ai.product_id=p.product_mat_id','inner');
      $this->db->join('mstr_unit u','u.unit_id=p.product_mat_unit','inner');
      $this->db->join('batch_product b','b.batch_id=ai.batch_id','inner');
      $this->db->where('ai.pbi_id',$batch_id);
      $this->db->where('ai.product_id NOT IN (select pbsi_product_mat_id from production_batch_sub_items where pbi_id = '.$batch_id.')',NULL,FALSE);
      $query = $this->db->get();
      return $query->result_array();
  }
  
  
  function getAddtionalQty($batch_id,$product_id){
       $this->db->select_sum('ai.req_qty');
       $this->db->select('b.product_cost');
       $this->db->from('addtional_items ai');
       $this->db->join('batch_product b','b.batch_id=ai.batch_id','inner');
       $this->db->where('ai.pbi_id',$batch_id);
       $this->db->where('ai.product_id',$product_id);
        $query = $this->db->get();
        $result= $query->row_array();
      if(isset($result['req_qty'])){
          $data=array(
              'qty'=>$result['req_qty'],
              'cost'=>$result['product_cost']
              );
          return $data;
      }else{
           $data=array(
              'qty'=>0,
              'cost'=>0
              );
          return $data;
      }
  }
  
  
  
  
  function get_batch_ingeedient_list($product_id='',$srh_from_date='',$srh_to_date='')
    {
        
       $this->db->select('p.product_mat_code,p.product_mat_name,p.product_mat_id');
       $this->db->from('production_batch_sub_items pi');
       $this->db->join('product_material p','pi.pbsi_product_mat_id=p.product_mat_id','inner');
       $this->db->join('production_batch_items pbi','pbi.pbi_id=pi.pbi_id','inner');
       if($product_id){
          $this->db->where("pbi.pbi_product_id",$product_id);  
       }
        
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		    $this->db->group_by('pi.pbsi_product_mat_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    
     function getAddtionalQtySummary($product_id,$srh_from_date,$srh_to_date){
       $this->db->select_sum('ai.req_qty');
       $this->db->select('b.product_cost');
       $this->db->from('addtional_items ai');
       $this->db->join('production_batch_items pbi','pbi.pbi_id=ai.pbi_id','inner');
       $this->db->join('batch_product b','b.batch_id=ai.batch_id','inner');
       
       $this->db->where("ai.product_id",$product_id); 
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		    $this->db->group_by('ai.product_id');
        $query = $this->db->get();
        $result= $query->row_array();
      if(isset($result['req_qty'])){
          $data=array(
              'qty'=>$result['req_qty'],
              'cost'=>$result['product_cost']
              );
          return $data;
      }else{
           $data=array(
              'qty'=>0,
              'cost'=>0
              );
          return $data;
      }
  }
  
   function getPBIlQtySummary($product_id,$srh_from_date,$srh_to_date){
       $this->db->select_sum('ai.pbis_qty');
       $this->db->select_sum('ai.pbsi_cost_total');
       $this->db->from('production_batch_sub_items ai');
       $this->db->join('production_batch_items pbi','pbi.pbi_id=ai.pbi_id','inner');
       $this->db->where("ai.pbsi_product_mat_id",$product_id); 
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        $result= $query->row_array();
      if(isset($result['pbis_qty'])){
          
          $data=array(
              'qty'=>$result['pbis_qty'],
              'cost'=>$result['pbsi_cost_total']
              );
          return $data;
      }else{
         $data=array(
              'qty'=>0,
              'cost'=>0
              );
              return $data;
      }
  }
  
  
  
  
  //2022-01-08 implement m.m.sachith eranga
  function getProductList($cat_id=''){
      $this->db->select('pc.cat_name,psc.sub_cat_name,u.unit_code');
      $this->db->select('p.product_mat_code,p.product_mat_name,p.product_mat_id,p.product_mat_alert_qty,p.product_mat_max_qty,p.product_mat_cost,p.product_mat_cost,p.mat_credit_salling_price,p.mat_wholesale_price');
      $this->db->from('product_material p');
      $this->db->join('product_category pc','pc.cat_id=p.mat_cat_id','left');
      $this->db->join('product_sub_category psc','psc.sub_cat_id=p.mat_sub_cat_id','left');
      $this->db->join('mstr_unit u','u.unit_id=p.product_mat_unit','left');
      if($cat_id){
        $this->db->where('p.mat_cat_id',$cat_id);  
      }
      $query = $this->db->get();
      return $query->result_array();
  }
  //2022-02-08 update bin card m.m.sachith eranga 
  function delete_all_recode_by_user_id($user_id)
    {
        $this->db->where('user_id', $user_id);
        return $this->db->delete('bin_card_report_table');
    }
    function get_all_direct_sale_item($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('si.quantity');
        $this->db->select('s.sale_reference_no,s.sale_datetime,s.card_ref_number');
        $this->db->from('sale_items si');
        $this->db->join('sales s', 's.sale_id=si.sale_id', 'left');
        $this->db->where("si.product_id",$product_id); 
        $this->db->where("s.direct_sale",1); 
          if($srh_to_date){
		        $this->db->where("date(s.sale_datetime) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(s.sale_datetime) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        return $query->result_array();
    }
    
    function get_all_loading_item($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('pi.issue_qty');
        $this->db->select('p.loading_ref_no,p.system_date_time,p.remark,m.cus_order_ref_no');
        $this->db->from('customer_order_items pi');
        $this->db->join('marketing_loading_unloading p', 'p.id=pi.delivery_id', 'inner');
        $this->db->join('customer_order_master m', 'm.cus_order_master_id=pi.cus_order_master_id', 'inner');
        $this->db->where("pi.product_id",$product_id); 
        $this->db->where("pi.delivery_status",1); 
          if($srh_to_date){
		        $this->db->where("date(p.system_date_time) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(p.system_date_time) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_all_un_loading_item($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('pi.return_qty');
        $this->db->select('p.loading_ref_no,pi.return_date_time,p.remark');
        $this->db->from('customer_order_return_items pi');
        $this->db->join('marketing_loading_unloading p', 'p.id=pi.delivery_id', 'left');
        $this->db->where("pi.product_id",$product_id); 
        $this->db->where("pi.delivery_status",1); 
          if($srh_to_date){
		        $this->db->where("date(pi.return_date_time) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pi.return_date_time) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_all_damage_item($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('pi.pdmgitm_quantity');
        $this->db->select('p.pdmg_reference_no,p.pdmg_datetime_created,p.pdmg_note');
        $this->db->from('product_damage_item pi');
        $this->db->join('product_damage p', 'p.pdmg_id=pi.pdmg_id', 'left');
        $this->db->where("pi.product_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(p.pdmg_datetime_created) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(p.pdmg_datetime_created) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        return $query->result_array();
    }
    function insert_data_to_bin_reirt_table($data){
        	$this->db->insert('bin_card_report_table',$data);
    }
     function get_bin_card($user_id)
    {
        $this->db->select('pi.*');
        $this->db->from('bin_card_report_table pi');
        $this->db->where('pi.user_id', $user_id);
        $this->db->order_by('pi.date_time', 'asc');
        $query = $this->db->get();
        return $query->result_array();
    }
  
  
 
  function get_damage_product_list($product_id='',$srh_from_date='',$srh_to_date='')
    {
        
       $this->db->select('p.product_code,p.product_name,p.product_id');
       $this->db->select_sum('pbi.defect_qty');
       $this->db->select_sum('pbi.pbi_lost_value');
       $this->db->from('production_batch_items pbi');
       $this->db->join('product p','pbi.pbi_product_id=p.product_id','inner');
       if($product_id){
          $this->db->where("pbi.pbi_product_id",$product_id);  
       }
        
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		    $this->db->group_by('pbi.pbi_product_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    
    
    function get_damage_product_total_by_reason($product_id='',$srh_from_date='',$srh_to_date='',$reason_id='')
    {
       $this->db->select_sum('pbi.defect_qty');
       $this->db->from('production_batch_items pbi');
       if($product_id){
          $this->db->where("pbi.pbi_product_id",$product_id);  
       }
        
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		    $this->db->where("pbi.manufacture_defect_reason_id",$reason_id);  
		    $this->db->group_by('pbi.pbi_product_id');
        $query = $this->db->get();
        $result = $query->row_array();
        if(isset($result['defect_qty'])){
            return $result['defect_qty'];
        }else{
            return 0;
        }
        
    }
  
  function get_damage_product_batch_list($product_id='',$srh_from_date='',$srh_to_date='')
    {
        
       $this->db->select('p.product_code,p.product_name,p.product_id');
       $this->db->select('pbi.defect_qty,pbi.pbi_ref_no,pbi.prodcution_start_on');
       $this->db->select('d.defect_desc');
       $this->db->from('production_batch_items pbi');
       $this->db->join('product p','pbi.pbi_product_id=p.product_id','inner');
       $this->db->join('defect_reasons d','pbi.manufacture_defect_reason_id=d.defect_id','left');
       if($product_id){
          $this->db->where("pbi.pbi_product_id",$product_id);  
       }
        
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        return $query->result_array();
    }
    
     function getAddtionalItemList($product_id='',$srh_from_date,$srh_to_date){
       
       $this->db->select('p.product_mat_code,p.product_mat_name,p.product_mat_id');
       $this->db->from('addtional_items ai');
       $this->db->join('production_batch_items pbi','pbi.pbi_id=ai.pbi_id','inner');
       $this->db->join('batch_product b','b.batch_id=ai.batch_id','inner');
       $this->db->join('product_material p','ai.product_id=p.product_mat_id','inner');
       if($product_id){
       $this->db->where("ai.product_id",$product_id); 
     }
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		    $this->db->group_by('ai.product_id');
        $query = $this->db->get();
        return $query->result_array();
     
  }
 
 
  function get_packing_damage_product_list($product_id='',$srh_from_date='',$srh_to_date='')
    {
        
       $this->db->select('p.product_code,p.product_name,p.product_id');
       $this->db->select_sum('pd.packing_damage');
       $this->db->select('pd.pgrni_unit_cost');
       $this->db->from('production_grn_items pd');
       $this->db->join('production_batch_items pbi','pd.pbi_id = pbi.pbi_id','inner');
       $this->db->join('product p','pbi.pbi_product_id = p.product_id','inner');
       if($product_id){
          $this->db->where("pd.product_id",$product_id);  
       }
        
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		    $this->db->group_by('pd.product_id');
        $query = $this->db->get();
        return $query->result_array();
    }

    
     function get_package_damage_product_total_by_reason($product_id='',$srh_from_date='',$srh_to_date='',$reason_id='')
    {
       $this->db->select_sum('pd.packing_damage');
       $this->db->from('production_grn_items pd');
       $this->db->join('production_batch_items pbi','pd.pbi_id = pbi.pbi_id','inner');
       if($product_id){
          $this->db->where("pd.product_id",$product_id);  
       }
        
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		    $this->db->where("pd.defect_reason",$reason_id);  
		    $this->db->group_by('pd.product_id');
        $query = $this->db->get();
        $result = $query->row_array();
        if(isset($result['packing_damage'])){
            return $result['packing_damage'];
        }else{
            return 0;
        }
    }
    
    
    function get_package_damage_product_batch_list($product_id='',$srh_from_date='',$srh_to_date='')
    {
        
       $this->db->select('p.product_code,p.product_name,p.product_id');
       $this->db->select('pbi.pbi_ref_no,pbi.prodcution_start_on');
       $this->db->select('pd.packing_damage');
       $this->db->select('d.defect_desc');
       $this->db->from('production_grn_items pd');
       $this->db->join('production_batch_items pbi','pd.pbi_id = pbi.pbi_id','inner');
       $this->db->join('product p','pbi.pbi_product_id=p.product_id','inner');
       $this->db->join('defect_reasons d','pd.defect_reason=d.defect_id','left');
       if($product_id){
          $this->db->where("pd.product_id",$product_id);  
       }
   
		if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		$this->db->group_by('pbi.pbi_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    
     function get_value_added_grn_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('ps.quantity');
        $this->db->select('pbi.pbi_ref_no,pbi.prodcution_start_on,pbi.finish_on');
        $this->db->select('psm.pgrnm_ref_no');
        $this->db->from('production_grn_intermediat_product ps');
        $this->db->join('production_batch_items pbi', 'pbi.pbi_id=ps.pbi_id', 'inner');
        $this->db->join('production_grn psm', 'psm.pgrnm_id=ps.pgrnm_id', 'inner');
       
        
        $this->db->where("ps.product_mat_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(pbi.prodcution_start_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pbi.prodcution_start_on) >=",$srh_from_date);
		    }
		$this->db->order_by('pbi.prodcution_start_on','asc');
		
		$this->db->group_by('ps.pgrni_id');
		//$this->db->group_by('ps.pbi_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    
    
    function get_issued_stn_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('ps.psio_issue_on');
        $this->db->select('s.psnm_ref_no,s.psnm_id');
        $this->db->from('production_stock_in_out ps');
        $this->db->join('production_batch_items pbi', 'pbi.pbi_id=ps.pbi_id', 'inner');
        $this->db->join('production_store_note_master s', 'ps.psnm_id=s.psnm_id', 'inner');
        $this->db->where("ps.product_mat_id",$product_id); 
          if($srh_to_date){
		        $this->db->where("date(ps.psio_issue_on) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(ps.psio_issue_on) >=",$srh_from_date);
		    }
		$this->db->order_by('ps.psio_issue_on','asc');
		$this->db->group_by('s.psnm_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_stn_total_issued_items($stn_id,$product_mat_id){
        $this->db->select_sum('ps.quantity');
        $this->db->from('production_stock_in_out ps');
        $this->db->where("ps.product_mat_id",$product_mat_id); 
        $this->db->where("ps.psnm_id",$stn_id);
        $query = $this->db->get();
        $result= $query->row_array();
        if($result['quantity']){
            return $result['quantity'];
        }else{
            return 0;
        }
    }
    function get_addtion_and_deduction_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('ad.issue_system_date_time , ad.req_qty');
        $this->db->select('pbi.pbi_ref_no');
        $this->db->from('addtional_items ad');
        $this->db->join('production_batch_items pbi', 'pbi.pbi_id=ad.pbi_id', 'inner');
       // $this->db->join('production_store_note_master s', 'ps.psnm_id=s.psnm_id', 'inner');
        $this->db->where("ad.product_id",$product_id); 
       // $this->db->where("ps.psio_request_type",2);
          if($srh_to_date){
		        $this->db->where("date(ad.issue_system_date_time) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(ad.issue_system_date_time) >=",$srh_from_date);
		    }
		$this->db->order_by('ad.issue_system_date_time','asc');
        $query = $this->db->get();
        return $query->result_array();
    }
    
     function get_converted_product_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('pc.pc_date_time , pc.pc_qty');
        $this->db->from('production_convert pc');
        $this->db->where("pc.pc_parduct_mat_id",$product_id); 
      
          if($srh_to_date){
		        $this->db->where("date(pc.pc_date_time) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(pc.pc_date_time) >=",$srh_from_date);
		    }
		$this->db->order_by('pc.pc_date_time','asc');
        $query = $this->db->get();
        return $query->result_array();
    }
    
     function get_damage_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        
        $this->db->select('p.pdmg_reference_no,p.pdmg_datetime_created');
        $this->db->select('pi.*');
       $this->db->from('product_damage_item pi');
        $this->db->join('product_damage p', 'p.pdmg_id=pi.pdmg_id', 'inner');
        $this->db->where("pi.product_id",$product_id); 
		if($srh_to_date){
		        $this->db->where("date(p.pdmg_datetime_created) <=",$srh_to_date);
		   }
		    if($srh_from_date){
			    $this->db->where("date(p.pdmg_datetime_created) >=",$srh_from_date);
		    }
        $query = $this->db->get();
        return $query->result_array();
    }
    
    //2023-05-15sachirh eranga for finishproduct
     function getFinishProductProductList($cat_id=''){
      $this->db->select('pc.cat_name,psc.sub_cat_name,u.unit_code');
      $this->db->select('p.product_code,p.product_name,p.product_id,p.product_alert_qty,p.product_max_qty,p.product_cost,p.product_cost,p.credit_salling_price,p.wholesale_price');
      $this->db->from('product p');
      $this->db->join('product_category pc','pc.cat_id=p.cat_id','left');
      $this->db->join('product_sub_category psc','psc.sub_cat_id=p.sub_cat_id','left');
      $this->db->join('mstr_unit u','u.unit_id=p.product_unit','left');
      if($cat_id){
        $this->db->where('p.cat_id',$cat_id);  
      }
      $query = $this->db->get();
      return $query->result_array();
  }
   function get_finish_product_grn_opening_stock($product_id,$srh_from_date='')
    {
        $this->db->select_sum('pi.quantity');
        $this->db->from('production_grn_items pi');
        $this->db->join('production_grn p', 'p.pgrnm_id=pi.pgrnm_id', 'inner');
        $this->db->where("pi.product_id",$product_id); 
        $this->db->where("pi.pgrni_product_type_id",3); 
        $this->db->where("p.pgrnm_pick_up_status",1);
          if($srh_from_date){
		        $this->db->where("date(p.pgrnm_pick_up_on) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['quantity'])){
            return $result['quantity'] ;
        }else{
            return 0;
        }
    }
    
    function get_transfered_opening_stock($product_id,$srh_from_date='')
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('stock_transfer_item p');
        $this->db->join('stock_transfer_master m', 'm.stm_id=p.stm_id', 'inner');
        $this->db->where("p.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(m.stm_date_time) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['quantity'])){
            return $result['quantity'] ;
        }else{
            return 0;
        }
    }
	
	function get_convert_finish_product_opening_stock($product_id,$srh_from_date=''){
        $this->db->select_sum('p.pc_qty');
        $this->db->from('production_convert p');
        $this->db->where("p.pc_prodcut_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(p.pc_date_time) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['pc_qty'])){
            return $result['pc_qty'] ;
        }else{
            return 0;
        }
    }
    
    function get_finish_product_purcheses_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('p.pgrnm_ref_no,p.pgrnm_pick_up_on');
        $this->db->select('pi.quantity,pbi.pbi_ref_no');  
        $this->db->from('production_grn_items pi');
         $this->db->join('production_batch_items pbi', 'pbi.pbi_id=pi.pbi_id', 'inner');
        $this->db->join('production_grn p', 'p.pgrnm_id=pi.pgrnm_id', 'inner');
        $this->db->where("pi.product_id",$product_id); 
        $this->db->where("pi.pgrni_product_type_id",3); 
        $this->db->where("p.pgrnm_pick_up_status",1);
          if($srh_from_date){
		        $this->db->where("date(p.pgrnm_pick_up_on) >=",$srh_from_date);
		   }
        	if($srh_to_date){
		        $this->db->where("date(p.pgrnm_pick_up_on) <=",$srh_to_date);
		   }
        $query = $this->db->get();
        return $query->result_array();
    }
    
    function get_finish_product_transferd_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('p.quantity,pbi.pbi_ref_no');
        $this->db->select('m.stm_date_time,m.stm_to_id,m.stm_no');
        $this->db->from('stock_transfer_item p');
        $this->db->join('stock_transfer_master m', 'm.stm_id=p.stm_id', 'inner');
        $this->db->join('production_batch_items pbi', 'pbi.pbi_id=p.batch_id', 'inner');
        $this->db->where("p.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(m.stm_date_time) >=",$srh_from_date);
		   }
       	if($srh_to_date){
		        $this->db->where("date(m.stm_date_time) <=",$srh_to_date);
		   }
        $query = $this->db->get();
        return $query->result_array();
    }
    
    function get_finish_product_converted_list($product_id,$srh_from_date='',$srh_to_date=''){
        $this->db->select('p.*');
        $this->db->from('production_convert p');
        $this->db->where("p.pc_prodcut_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(p.pc_date_time) >=",$srh_from_date);
		   }
        if($srh_to_date){
		        $this->db->where("date(p.pc_date_time) <=",$srh_to_date);
		   }
        $query = $this->db->get();
        return $query->result_array();
    }
    
    function get_m_product_transferd_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('p.quantity,b.batch_ref_number');
        $this->db->select('m.stm_date_time,m.stm_to_id,m.stm_no');
        $this->db->from('stock_m_transfer_item p');
        $this->db->join('stock_m_transfer_master m', 'm.stm_id=p.stm_id', 'inner');
        $this->db->join('batch_product b', 'b.batch_id=p.batch_id', 'inner');
        $this->db->where("p.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(m.stm_date_time) >=",$srh_from_date);
		   }
       	if($srh_to_date){
		        $this->db->where("date(m.stm_date_time) <=",$srh_to_date);
		   }
        $query = $this->db->get();
        return $query->result_array();
    }
    
    
    function get_fg_grn_opening_stock($product_id,$srh_from_date='')
    {
        $this->db->select_sum('p.product_qty');
        $this->db->from('extra_production_grn_items p');
         $this->db->join('extra_production_grn m', 'm.expgrn_id=p.epgrn_id', 'inner');
        $this->db->where("p.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(m.expgrn_date_time) <=",$srh_from_date);
		   }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['product_qty'])){
            return $result['product_qty'] ;
        }else{
            return 0;
        }
    }
    
    function get_ex_fg_stock_list($product_id,$srh_from_date='',$srh_to_date='')
    {
        $this->db->select('p.product_qty,pbi.pbi_ref_no');
        $this->db->select('m.expgrn_date_time,m.expgrn_ref_no');
        $this->db->from('extra_production_grn_items p');
        $this->db->join('extra_production_grn m', 'm.expgrn_id=p.epgrn_id', 'inner');
        $this->db->join('production_batch_items pbi', 'pbi.pbi_id=p.batch_id', 'inner');
        $this->db->where("p.product_id",$product_id); 
          if($srh_from_date){
		        $this->db->where("date(m.expgrn_date_time) >=",$srh_from_date);
		   }
       	if($srh_to_date){
		        $this->db->where("date(m.expgrn_date_time) <=",$srh_to_date);
		   }
        $query = $this->db->get();
        return $query->result_array();
    }
		
}