<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
class Production_Process_Model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
    }
    function save_product($pd){
        if ($this->db->insert('product', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    public function update_product_code($product_id){
        if($product_id){
		$data = array(
            'product_code' => "PD" . sprintf("%06d", $product_id)
        );
        $this->db->where('product_id', $product_id);
        $this->db->update('product', $data);
        return $product_id;
        }
        else return false;
    }
    function getProducts($start, $length, $search_key_val){
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name,mpt.mpt_name');
        $this->db->from('product p');
        $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        if ($search_key_val) {
             $this->db->where("p.product_status = 1 AND p.product_type_id != 1 AND  p.product_code LIKE '$search_key_val%'");
		     $this->db->or_where("p.product_status = 1 AND p.product_type_id != 1 AND p.product_name LIKE '%$search_key_val%'");
        }else{
            $this->db->or_where("p.product_status = 1 AND p.product_type_id != 1 ");
        }
		$this->db->order_by("p.product_id", "desc");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function get_row_matirial_and_intermediate_product($search_key_val){
		$this->db->select('p.*,u.unit_code');
		$this->db->from('product_material p');
		 $this->db->join('mstr_unit u', 'u.unit_id = p.product_mat_unit', 'left');
	    $this->db->where("p.product_mat_status = 1  AND  p.product_mat_code LIKE '$search_key_val%'");
		$this->db->or_where("p.product_mat_status = 1  AND p.product_mat_name LIKE '%$search_key_val%'");
		$this->db->order_by("p.product_mat_name", "asc");
		$this->db->group_by('p.product_mat_id');
		$this->db->limit(50);
		$query = $this->db->get();
		return $query->result_array();
	}
	function get_row_final_and_intermediate_product($search_key_val){
		$this->db->select('p.*');
		$this->db->from('product p');
	    $this->db->where("p.product_status = 1 AND p.product_type_id != 1 AND  p.product_code LIKE '$search_key_val%'");
		$this->db->or_where("p.product_status = 1 AND p.product_type_id != 1 AND p.product_name LIKE '%$search_key_val%'");
		$this->db->order_by("p.product_name", "asc");
		$this->db->group_by('p.product_id');
		$this->db->limit(50);
		$query = $this->db->get();
		return $query->result_array();
	}
	 function getProductsBlock($start, $length, $search_key_val){
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name,u.unit_code,mpt.mpt_name,pbm.product_block_code,pbm.pbm_id,pbm.batch_qty');
        $this->db->from('production_block_master pbm');
        $this->db->join('product p', 'pbm.product_id = p.product_id', 'left');
        $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        if ($search_key_val) {
             $this->db->where("pbm.is_deleted = 0 AND p.product_status = 1 AND p.product_type_id != 1 AND  p.product_code LIKE '$search_key_val%'");
		     $this->db->or_where("pbm.is_deleted = 0 AND p.product_status = 1 AND p.product_type_id != 1 AND p.product_name LIKE '%$search_key_val%'");
		     $this->db->or_where("pbm.is_deleted = 0 AND p.product_status = 1 AND p.product_type_id != 1 AND pbm.product_block_code LIKE '%$search_key_val%'");
        }else{
            $this->db->or_where("pbm.is_deleted = 0 AND p.product_status = 1 AND p.product_type_id != 1 ");
        }
		$this->db->order_by("pbm.pbm_id", "desc");
		$this->db->group_by("pbm.pbm_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function check_aldary_in_the_block($product_id,$main_product_id){
        $this->db->from('production_block_item p');
        $this->db->where("p.product_id",$product_id);
        $this->db->where("p.pdm_id",$main_product_id);
        $id = $this->db->get()->num_rows();
        if($id>0){return 1;}else{return 0;}
    }
     function save_broduct_block_item($pd)
    {
        if ($this->db->insert('production_block_item', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    function get_product_block_sub_item_list($product_id){
        $this->db->select('p.* ,u.unit_name,u.unit_code,mpt.mpt_name,pbi.product_qty,pbi.pdi_id,pbi.base_item,muc.conversion_unit_id,muc.math_operation,muc.conversion_value,pbi.is_deleted');
        $this->db->from('production_block_item pbi');
        $this->db->join('product_material p', 'pbi.product_id = p.product_mat_id', 'inner');
        $this->db->join('mstr_unit_conversion muc', 'p.product_mat_unit = muc.base_unit_id', 'inner');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_mat_unit', 'inner');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_mat_type_id', 'inner');
        $this->db->where("pbi.is_deleted = 0");
        $this->db->where("pbi.pdm_id",$product_id);
        $this->db->order_by("pbi.base_item", "desc");
        $this->db->order_by("p.product_mat_name", "asc");
        $query = $this->db->get();
		return $query->result_array();
    }
     function get_product_block_sub_item_list_with_delete($product_id){
        $this->db->select('p.* ,u.unit_name,u.unit_code,mpt.mpt_name,pbi.product_qty,pbi.pdi_id,pbi.base_item,muc.conversion_unit_id,muc.math_operation,muc.conversion_value,pbi.is_deleted');
        $this->db->from('production_block_item pbi');
        $this->db->join('product_material p', 'pbi.product_id = p.product_mat_id', 'inner');
        $this->db->join('mstr_unit_conversion muc', 'p.product_mat_unit = muc.base_unit_id', 'inner');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_mat_unit', 'inner');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_mat_type_id', 'inner');
        //$this->db->where("pbi.is_deleted = 0");
        $this->db->where("pbi.pdm_id",$product_id);
        $this->db->order_by("pbi.base_item", "desc");
        $this->db->order_by("p.product_mat_name", "asc");
        $query = $this->db->get();
		return $query->result_array();
    }
    function get_block_details($product_id){
        $this->db->select('p.* ,u.unit_name,mpt.mpt_name,pbm.batch_qty,pbm.product_block_code');
        $this->db->from('production_block_master pbm');
        $this->db->join('product p', 'p.product_id = pbm.product_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        $this->db->where("pbm.is_deleted = 0");
        $this->db->where("pbm.pbm_id",$product_id);
        $query = $this->db->get();
		return $query->row_array();
    }
     public function update_block_batch_qty($id,$data)
    {
        if($id){
        $this->db->where('pbm_id', $id);
        return $this->db->update('production_block_master', $data);
        }
        else return false;
    }
    public function update_production_block_item_table($id,$data)
    {
        if($id){
        $this->db->where('pdi_id', $id);
        return $this->db->update('production_block_item', $data);
        }
        else return false;
    }
    function insert_bulk_request_master_table($pd)
    {
        if ($this->db->insert('bulk_requste_master', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
     function save_bulk_requste_master_table($pd)
    {
        if ($this->db->insert('bulk_request_items', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
    function get_product_list_with_brm_id($id){
        $this->db->select('p.* ,u.unit_name,mpt.mpt_name,bri.request_qty,bri.bri_id,bri.approved_qty,bri.bri_note');
        $this->db->from('bulk_request_items bri');
        $this->db->join('product p', 'bri.product_id = p.product_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        $this->db->where("bri.is_deleted = 0");
        $this->db->where("bri.brm_id",$id);
        $query = $this->db->get();
		return $query->result_array();
    }
     public function update_bulk_request_item_table($id,$data)
    {
        if($id){
        $this->db->where('bri_id', $id);
        return $this->db->update('bulk_request_items', $data);
        }
        else return false;
    }
     function getPendingBulkRequest($start, $length, $search_key_val)
    {
        $this->db->select('brm.*,w.name');
        $this->db->from('bulk_requste_master brm');
        $this->db->join('warehouses w', 'w.id = brm.brn_to_warehouse_id', 'left');
        if ($search_key_val) {
             $this->db->where("brm.is_deleted = 0  AND brm.brm_approval_status = 0  AND  brm.brm_ref_no LIKE '$search_key_val%'");
             $this->db->where("brm.is_deleted = 0  AND brm_approval_status = 0  AND  brm.brm_manual_ref_no LIKE '$search_key_val%'");
		   
        }else{
            $this->db->where("brm.is_deleted = 0 AND brm_approval_status = 0");
        }
		$this->db->order_by("brm.brm_id", "desc");
		$this->db->group_by("brm.brm_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function getBulkRequestDetails($id)
    {
        $this->db->select('brm.*,w.name');
        $this->db->from('bulk_requste_master brm');
        $this->db->join('warehouses w', 'w.id = brm.brn_to_warehouse_id', 'left');
        $this->db->where("brm.brm_id",$id);
        $query = $this->db->get();
        return $query->row_array();
    }
    
    public function update_bulk_request_master_table($id,$data)
    {
        if($id){
        $this->db->where('brm_id', $id);
        return $this->db->update('bulk_requste_master', $data);
        }
        else return false;
    }
     function getApprovedBulkRequest($start, $length, $search_key_val)
    {
        $this->db->select('brm.*,w.name');
        $this->db->from('bulk_requste_master brm');
        $this->db->join('warehouses w', 'w.id = brm.brn_to_warehouse_id', 'left');
        if ($search_key_val) {
             $this->db->where("brm.is_deleted = 0  AND brm.brm_approval_status = 1  AND brm.pickup_status = 0 AND  brm.brm_ref_no LIKE '$search_key_val%'");
             $this->db->where("brm.is_deleted = 0  AND brm_approval_status = 1  AND brm.pickup_status = 0 AND  brm.brm_manual_ref_no LIKE '$search_key_val%'");
		   
        }else{
            $this->db->where("brm.is_deleted = 0 AND brm_approval_status = 1 AND brm.pickup_status = 0");
        }
		$this->db->order_by("brm.brm_id", "desc");
		$this->db->group_by("brm.brm_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function get_product_blocks($id)
    {
        $this->db->select('pbm.*');
        $this->db->from('production_block_master pbm');
        $this->db->where("pbm.product_id",$id);
        $this->db->where("pbm.pbm_approval_status",1);
        $this->db->where("pbm.is_deleted",0);
         $this->db->where("pbm.pdm_status",1);
        $query = $this->db->get();
        return $query->result_array();
    }
     function bulk_request_item_estimation_table($pd)
    {
        if ($this->db->insert('bulk_request_item_estimation', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    function get_total_estimated_qty($id){
        $this->db->select_sum('brim.estimated_qty');
        $this->db->from('bulk_request_item_estimation brim');
        $this->db->where("brim.bri_id",$id);
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['estimated_qty'])){
          return $result['estimated_qty'];  
        }else{
            return 0;
        }
    }
    function get_allocated_batch_block_Estimaed_item($id){
        $this->db->select('brim.*,pbm.product_block_code,pbm.batch_qty');
        $this->db->from('bulk_request_item_estimation brim');
        $this->db->join('production_block_master pbm','pbm.pbm_id=brim.batch_block_id','inner');
        $this->db->where("brim.bri_id",$id);
        $query = $this->db->get();
        return $query->result_array();
    }
    function delete_estimate_block_alocation($id){
        
        if($id>0){
            $this->db->where("bri_id", $id);
            return $this->db->delete("bulk_request_item_estimation");
        }
    }
     function save_bulk_requste_cost_estimation_master_table($pd)
    {
        if ($this->db->insert('bulk_requste_cost_estimation_master', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
     function save_bulk_requste_cost_estimation_items_table($pd)
    {
        if ($this->db->insert('bulk_requste_cost_estimation_items', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
    function get_sub_item_list($id){
        $this->db->select('pbi.*,p.product_cost');
        $this->db->from('production_block_item pbi');
        $this->db->join('product p','pbi.product_id=p.product_id','inner');
        $this->db->where("pbi.pdm_id",$id);
        $query = $this->db->get();
        return $query->result_array();
        
    }
    
    function save_bulk_requste_cost_estimation_sub_items_table($pd)
    {
        if ($this->db->insert('bulk_requste_cost_estimation_sub_items', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
    function get_estimated_item_list($id){
        $this->db->select('p.*');
        $this->db->from('bulk_requste_cost_estimation_items p');
        $this->db->where("p.brcem_id",$id);
        $query = $this->db->get();
        return $query->result_array();
    }
    
     function get_total_estimated_item_cost($id){
        $this->db->select_sum('s.item_sub_total');
        $this->db->from('bulk_requste_cost_estimation_sub_items s');
        $this->db->where("s.brcei_id",$id);
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['item_sub_total'])){
          return $result['item_sub_total'];  
        }else{
            return 0;
        }
    }
    public function update_bulk_requste_cost_estimation_items_table($id,$data){
        if($id){
        $this->db->where('brcei_id', $id);
        return $this->db->update('bulk_requste_cost_estimation_items', $data);
        }
        else return false;
    }
     public function update_bulk_requste_cost_estimation_master_table($id,$data){
        if($id){
        $this->db->where('brcem_id', $id);
        return $this->db->update('bulk_requste_cost_estimation_master', $data);
        }
        else return false;
    }
    function update_estimated_item_cost($id){
        $total_cost_value=0;
        $result=$this->get_estimated_item_list($id);
        foreach($result as $r){
            $sub_total=$this->get_total_estimated_item_cost($r['brcei_id']);
            $total_cost_value+=$sub_total;
            $unit_estimated_cost=$sub_total/$r['batch_qty'];
            $data=array('estimate_cost_value'=>$sub_total,'unit_estimated_cost'=>$unit_estimated_cost);
            $this->update_bulk_requste_cost_estimation_items_table($r['brcei_id'],$data);
        }
      $data2=array('total_cost_estimation_value'=>$total_cost_value);  
     return $this->update_bulk_requste_cost_estimation_master_table($id,$data2);   
    }
    
    function getCompletedEstimation($start, $length, $search_key_val)
    {
        $this->db->select('b.*,w.name');
        $this->db->from('bulk_requste_cost_estimation_master b');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        if ($search_key_val) {
             $this->db->where("b.is_deleted = 0  AND b.brcem_status = 1  AND   b.brcem_refarance_no LIKE '$search_key_val%'");
            // $this->db->where("b.is_deleted = 0  AND b.brcem_status = 1  AND   b.brcem_refarance_no LIKE '$search_key_val%'");
		   
        }else{
            $this->db->where("b.is_deleted = 0 AND b.brcem_status = 1");
        }
		$this->db->order_by("b.brcem_id", "desc");
		$this->db->group_by("b.brcem_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
     function getEstimateDetails($id)
    {
        $this->db->select('b.*,w.name');
        $this->db->from('bulk_requste_cost_estimation_master b');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->where("b.brcem_id",$id);
        $query = $this->db->get();
        return $query->row_array();
    }
    
     function get_product_list_only($id){
        $this->db->select('b.*,p.product_code,p.product_name');
        $this->db->from('bulk_requste_cost_estimation_items b');
        $this->db->join('product p','b.product_id=p.product_id','inner');
        $this->db->where("b.brcem_id",$id);
        $this->db->group_by('b.product_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_related_batch_using_product_id($id,$product_id=''){
        $this->db->select('b.*,p.product_code,p.product_name');
        $this->db->from('bulk_requste_cost_estimation_items b');
        $this->db->join('product p','b.product_id=p.product_id','inner');
        $this->db->where("b.brcem_id",$id);
        if($product_id){
             $this->db->where('b.product_id',$product_id);
        }
        $query = $this->db->get();
        return $query->result_array();
    }
   function  get_estimate_raw_matirial_list($id){
        $this->db->select('b.*,p.product_code,p.product_name');
        $this->db->from('bulk_requste_cost_estimation_sub_items b');
        $this->db->join('product p','b.item_id=p.product_id','inner');
        $this->db->where("b.brcei_id",$id);
        $query = $this->db->get();
        return $query->result_array();
   }
   function get_raw_matiarial_andintermediate_product_list_only($id){
        $this->db->select('b.*,p.product_code,p.product_name');
        $this->db->from('bulk_requste_cost_estimation_sub_items b');
        $this->db->join('bulk_requste_cost_estimation_items bi','b.brcei_id=bi.brcei_id','inner');
        $this->db->join('product p','b.item_id=p.product_id','left');
        $this->db->where("bi.brcem_id",$id);
        $this->db->group_by('b.item_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_raw_matiarial_and_intermediate_product_total_qty($id,$product_id=''){
        $this->db->select_sum('b.item_qty');
        $this->db->from('bulk_requste_cost_estimation_sub_items b');
        $this->db->join('bulk_requste_cost_estimation_items bi','b.brcei_id=bi.brcei_id','inner');
        $this->db->where("bi.brcem_id",$id);
        if($product_id){
          $this->db->where("b.item_id",$product_id);  
        }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['item_qty'])){
            return $result['item_qty'];
        }else{
            return 0;
        }
    }
     function get_raw_matiarial_and_intermediate_product_total_cost($id,$product_id=''){
        $this->db->select_sum('b.item_sub_total');
        $this->db->from('bulk_requste_cost_estimation_sub_items b');
        $this->db->join('bulk_requste_cost_estimation_items bi','b.brcei_id=bi.brcei_id','inner');
        $this->db->where("bi.brcem_id",$id);
        if($product_id){
          $this->db->where("b.item_id",$product_id);  
        }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['item_sub_total'])){
            return $result['item_sub_total'];
        }else{
            return 0;
        }
     }
      function getUpcomingProductionsBatch($start, $length, $search_key_val)
    {
        $this->db->select('b.*,w.name,p.product_code,p.product_name');
        $this->db->from('bulk_requste_cost_estimation_items b');
        $this->db->join('bulk_requste_cost_estimation_master bm', 'bm.brcem_id = b.brcem_id', 'left');
        $this->db->join('warehouses w', 'w.id = bm.warehouse_id', 'left');
        $this->db->join('product p', 'p.product_id = b.product_id', 'left');
        if ($search_key_val) {
             $this->db->where("b.is_deleted = 0 AND bm.brcem_approval_status = 1 AND b.pickup_status = 0 AND b.individual_approval = 1  AND  b.batch_refarance_no LIKE '$search_key_val%'");
             //$this->db->where("b.is_deleted = 0  AND b.individual_approval_by = 0  AND  b.batch_refarance_no LIKE '$search_key_val%'");
        }else{
            $this->db->where("b.is_deleted = 0 AND bm.brcem_approval_status = 1 AND b.pickup_status = 0  AND b.individual_approval = 1");
        }
		$this->db->order_by("b.brcei_id", "asc");
		$this->db->group_by("b.brcei_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function getProductionBatchDetails($id)
    {
        $this->db->select('bi.*,w.name,bm.warehouse_id,bm.brm_id,p.product_code,p.product_name');
        $this->db->from('bulk_requste_cost_estimation_items bi');
        $this->db->join('bulk_requste_cost_estimation_master bm', 'bm.brcem_id = bi.brcem_id', 'left');
        $this->db->join('warehouses w', 'w.id = bm.warehouse_id', 'left');
        $this->db->join('product p', 'p.product_id = bi.product_id', 'left');
        $this->db->where("bi.brcei_id",$id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_product_list_with_batch_id($id){
        $this->db->select('p.* ,u.unit_name,mpt.mpt_name,b.*');
        $this->db->from('bulk_requste_cost_estimation_sub_items b');
        $this->db->join('product p', 'p.product_id = b.item_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        $this->db->where("b.brcei_id",$id);
        $query = $this->db->get();
		return $query->result_array();
    }
    function get_total_issued_qty($id){
        $this->db->select_sum('b.psi_item_qty');
        $this->db->from('production_stock_issue b');
        $this->db->where("b.brcesi_id",$id);
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['psi_item_qty'])){
          return $result['psi_item_qty'];  
        }else{
            return 0;
        }
    }
    function get_allocated_batch_item($id){
        $this->db->select('b.*');
        $this->db->from('batch_product b');
        $this->db->where("b.product_id",$id);
        $this->db->where("b.batch_status",1);
        $this->db->where("b.is_deleted",0);
        $query = $this->db->get();
        return $query->result_array();
    }
     function bulk_production_stock_issue_table($pd){
        if ($this->db->insert('production_stock_issue', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    function get_batch_actual_cost($id){
        $this->db->select('b.product_cost');
        $this->db->from('batch_product b');
        $this->db->where("b.batch_id",$id);
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['product_cost'])){
          return $result['product_cost'];  
        }else{
            return 0;
        }
    }
     function get_production_batch_issue_items($id){
        $this->db->select('b.*,bt.batch_ref_number');
        $this->db->from('production_stock_issue b');
         //$this->db->join('product p', 'p.product_id = b.psi_item_id', 'left');
         $this->db->join('batch_product bt', 'bt.batch_id = b.batch_id', 'inner');
        $this->db->where("b.brcesi_id",$id);
        $this->db->group_by('b.psi_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_total_production_issue_qty($id){
        $this->db->select_sum('b.psi_item_qty');
        $this->db->from('production_stock_issue b');
        $this->db->where("b.brcesi_id",$id);
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['psi_item_qty'])){
          return $result['psi_item_qty'];  
        }else{
            return 0;
        }
    }
    function get_list_estimation_subb_item_list_by_item_id($id){
        $this->db->select('p.* ,u.unit_name,mpt.mpt_name,bri.*');
        $this->db->from('bulk_requste_cost_estimation_sub_items bri');
        $this->db->join('product p', 'bri.item_id = p.product_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        //$this->db->where("bri.is_deleted = 0");
        $this->db->where("bri.brcei_id",$id);
        $query = $this->db->get();
		return $query->result_array();
    }
    function getInProductionsBatch($start, $length, $search_key_val){
        $this->db->select('b.*,w.name,p.product_code,p.product_name');
        $this->db->from('bulk_requste_cost_estimation_items b');
        $this->db->join('bulk_requste_cost_estimation_master bm', 'bm.brcem_id = b.brcem_id', 'left');
        $this->db->join('warehouses w', 'w.id = bm.warehouse_id', 'left');
        $this->db->join('product p', 'p.product_id = b.product_id', 'left');
        if ($search_key_val) {
             $this->db->where("b.is_deleted = 0 AND bm.brcem_approval_status = 1 AND b.pickup_status = 1 AND b.individual_approval = 1 AND b.production_complete_status = 0  AND  b.batch_refarance_no LIKE '$search_key_val%'");
             //$this->db->where("b.is_deleted = 0  AND b.individual_approval_by = 0  AND  b.batch_refarance_no LIKE '$search_key_val%'");
        }else{
            $this->db->where("b.is_deleted = 0 AND bm.brcem_approval_status = 1 AND b.pickup_status = 1  AND b.individual_approval = 1 AND b.production_complete_status = 0");
        }
		$this->db->order_by("b.brcei_id", "asc");
		$this->db->group_by("b.brcei_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function get_manufacured_batch_actual_material_cost($id){
        $this->db->select_sum('b.psi_total_cost');
        $this->db->from('production_stock_issue b');
        $this->db->where("b.brcei_id",$id);
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['psi_total_cost'])){
          return $result['psi_total_cost'];  
        }else{
            return 0;
        }
    }
     function save_manufactured_finish_product($pd)
    {
        if ($this->db->insert('manufactured_products', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
     function getCompletedBatch($start, $length, $search_key_val){
        $this->db->select('b.*,w.name,p.product_code,p.product_name,bi.batch_refarance_no,bm.brm_ref_no');
        $this->db->from('manufactured_products b');
        $this->db->join('bulk_requste_cost_estimation_items bi', 'bi.brcei_id = b.batch_id', 'left');
        $this->db->join('bulk_requste_master bm', 'bm.brm_id = b.bulk_requste_id', 'left');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('product p', 'p.product_id = b.product_id', 'left');
        if ($search_key_val) {
             $this->db->where("bi.batch_refarance_no LIKE '$search_key_val%'");
             $this->db->or_where("p.product_code LIKE '$search_key_val%'");
             $this->db->or_where("p.product_name LIKE '$search_key_val%'");
             $this->db->or_where("bm.brm_ref_no LIKE '$search_key_val%'");
             //$this->db->where("b.is_deleted = 0  AND b.individual_approval_by = 0  AND  b.batch_refarance_no LIKE '$search_key_val%'");
        }else{
            //$this->db->where("b.is_deleted = 0 AND bm.brcem_approval_status = 1 AND b.pickup_status = 1  AND b.individual_approval = 1 AND b.production_complete_status = 0");
        }
		$this->db->order_by("b.mp_id", "desc");
		$this->db->group_by("b.mp_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    /*function getPendingqualityCheck($start, $length, $search_key_val)
    {
        $this->db->select('b.*,w.name,p.product_code,p.product_name,bi.batch_refarance_no,bm.brm_ref_no');
        $this->db->from('manufactured_products b');
        $this->db->join('bulk_requste_cost_estimation_items bi', 'bi.brcei_id = b.batch_id', 'left');
        $this->db->join('bulk_requste_master bm', 'bm.brm_id = b.bulk_requste_id', 'left');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('product p', 'p.product_id = b.product_id', 'left');
        if ($search_key_val) {
             $this->db->where("b.quality_control_status = 0 AND bi.batch_refarance_no LIKE '%$search_key_val%'");
             $this->db->or_where("b.quality_control_status = 0 AND p.product_code LIKE '%$search_key_val%'");
             $this->db->or_where("b.quality_control_status = 0 AND p.product_name LIKE '%$search_key_val%'");
             $this->db->or_where("b.quality_control_status = 0 AND bm.brm_ref_no LIKE '%$search_key_val%'");
             //$this->db->where("b.is_deleted = 0  AND b.individual_approval_by = 0  AND  b.batch_refarance_no LIKE '$search_key_val%'");
        }else{
            $this->db->where("b.quality_control_status = 0 ");
        }
		$this->db->order_by("b.mp_id", "asc");
		$this->db->group_by("b.mp_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }*/
    function getPendingqualityCheck(){
        $this->db->select('b.*,w.name,p.product_code,p.product_name,bi.batch_refarance_no,bm.brm_ref_no');
        $this->db->from('manufactured_products b');
        $this->db->join('bulk_requste_cost_estimation_items bi', 'bi.brcei_id = b.batch_id', 'left');
        $this->db->join('bulk_requste_master bm', 'bm.brm_id = b.bulk_requste_id', 'left');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('product p', 'p.product_id = b.product_id', 'left');
        $this->db->where("b.quality_control_status = 0 ");
		$this->db->order_by("b.mp_id", "asc");
		$this->db->group_by("b.mp_id");
        $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
    }
     function save_manufactured_defect_product_table($pd){
        if ($this->db->insert('manufactured_defect_product', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
     function get_total_manufacture_defect_qty($id){
        $this->db->select_sum('b.mdp_qty');
        $this->db->from('manufactured_defect_product b');
        $this->db->where("b.mdp_batch_id",$id);
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['mdp_qty'])){
          return $result['mdp_qty'];  
        }else{
            return 0;
        }
    }
    public function update_manufactured_products_table($id,$data){
        if($id){
        $this->db->where('mp_id', $id);
        return $this->db->update('manufactured_products', $data);
        }
        else return false;
    }
    function getReadyForGRN($start, $length, $search_key_val){
        $this->db->select('b.*,w.name,p.product_code,p.product_name,bi.batch_refarance_no,bm.brm_ref_no');
        $this->db->from('manufactured_products b');
        $this->db->join('bulk_requste_cost_estimation_items bi', 'bi.brcei_id = b.batch_id', 'left');
        $this->db->join('bulk_requste_master bm', 'bm.brm_id = b.bulk_requste_id', 'left');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('product p', 'p.product_id = b.product_id', 'left');
        if ($search_key_val) {
             $this->db->where("b.quality_control_status = 0 AND b.grn_status = 0 AND bi.batch_refarance_no LIKE '%$search_key_val%'");
             $this->db->or_where("b.quality_control_status = 0 AND b.grn_status = 0 AND p.product_code LIKE '%$search_key_val%'");
             $this->db->or_where("b.quality_control_status = 0 AND b.grn_status = 0 AND p.product_name LIKE '%$search_key_val%'");
             $this->db->or_where("b.quality_control_status = 0 AND b.grn_status = 0 AND bm.brm_ref_no LIKE '%$search_key_val%'");
             //$this->db->where("b.is_deleted = 0  AND b.individual_approval_by = 0  AND  b.batch_refarance_no LIKE '$search_key_val%'");
        }else{
            $this->db->where("b.quality_control_status = 1 AND b.grn_status = 0");
        }
		$this->db->order_by("b.mp_id", "asc");
		$this->db->group_by("b.mp_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    function getReadyForAutoGRN(){
        $this->db->select('b.*,p.product_code,p.product_name,bi.batch_refarance_no');
        $this->db->from('manufactured_products b');
        $this->db->join('bulk_requste_cost_estimation_items bi', 'bi.brcei_id = b.batch_id', 'left');
        $this->db->join('product p', 'p.product_id = b.product_id', 'left');
        $this->db->where("b.quality_control_status = 1 AND b.grn_status = 0");
		$this->db->order_by("b.mp_id", "asc");
		$this->db->group_by("b.mp_id");
		$this->db->limit(100);
        $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
    }
     function save_purchases_table($pd){
        if ($this->db->insert('purchases', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
     function save_purchases_item_table($pd){
        if ($this->db->insert('purchase_items', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
     public function update_purchases_table($id,$data){
        if($id){
        $this->db->where('id', $id);
        return $this->db->update('purchases', $data);
        }
        else return false;
    }
     function getProductsBlockList($start, $length, $search_key_val){
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name,u.unit_code,mpt.mpt_name,pbm.product_block_code,pbm.pbm_id,pbm.batch_qty');
        $this->db->from('product p');
        $this->db->join('production_block_master pbm', 'p.product_id = pbm.product_id', 'left');
        $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        if ($search_key_val) {
             $this->db->where("p.product_status = 1 AND  p.product_code LIKE '$search_key_val%'");
		     $this->db->or_where("p.product_status = 1 AND  p.product_name LIKE '%$search_key_val%'");
		     $this->db->or_where("p.product_status = 1 AND  pbm.product_block_code LIKE '%$search_key_val%'");
        }else{
            $this->db->or_where("p.product_status = 1");
        }
		$this->db->order_by("p.product_name", "asc");
		$this->db->group_by("p.product_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
     function getProductsBlockCount($id){
           $this->db->select('b.pbm_id');
        $this->db->from('production_block_master b');
        $this->db->where("b.product_id",$id);
        $query = $this->db->get();
        return $query->num_rows();
    }
    
     function getProductsMold($start, $length, $search_key_val,$id){
        $this->db->select('p.* , c.cat_name , s.sub_cat_name, u.unit_name,u.unit_code,mpt.mpt_name,pbm.product_block_code,pbm.pbm_id,pbm.batch_qty');
        $this->db->from('production_block_master pbm');
        $this->db->join('product p', 'pbm.product_id = p.product_id', 'left');
        $this->db->join('product_category c', 'c.cat_id = p.cat_id', 'left');
        $this->db->join('product_sub_category s', 's.sub_cat_id = p.sub_cat_id', 'left');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'left');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'left');
        if ($search_key_val) {
             $this->db->where("pbm.product_id = '$id' AND pbm.is_deleted = 0 AND p.product_status = 1 AND p.product_type_id != 1 AND  p.product_code LIKE '$search_key_val%'");
		     $this->db->or_where("pbm.product_id = '$id' AND pbm.is_deleted = 0 AND p.product_status = 1 AND p.product_type_id != 1 AND p.product_name LIKE '%$search_key_val%'");
		     $this->db->or_where("pbm.product_id = '$id' AND pbm.is_deleted = 0 AND p.product_status = 1 AND p.product_type_id != 1 AND pbm.product_block_code LIKE '%$search_key_val%'");
        }else{
            $this->db->or_where("pbm.product_id = '$id' AND pbm.is_deleted = 0 AND p.product_status = 1 AND p.product_type_id != 1 ");
        }
		$this->db->order_by("pbm.pbm_id", "desc");
		$this->db->group_by("pbm.pbm_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    
     public function update_block_item_table_using_secondary_id($id,$data)
    {
        if($id){
        $this->db->where('pdm_id', $id);
        return $this->db->update('production_block_item', $data);
        }
        else return false;
    }
    
    
     function get_pdm_id_using_pdi_id($id){
        $this->db->select('b.pdm_id');
        $this->db->from('production_block_item b');
        $this->db->where("b.pdi_id",$id);
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['pdm_id'])){
            return $result['pdm_id'];
        }else{
            return false;
        }
    }
    
    function get_service_center_list(){
        $this->db->select('msc.*');
        $this->db->from('grouup_order_item goi');
        $this->db->join('master_service_center msc', 'msc.msc_code = goi.service_center_code', 'inner');
        $this->db->group_by('goi.service_center_code');
        $this->db->order_by("msc.msc_id", "asc");
        $query = $this->db->get();
		return $query->result_array();
    }
    
     function get_service_center_order_list(){
        $this->db->select('goi.*,p.product_code,p.product_name,p.product_status');
        $this->db->select_sum('goi.requested_qty');
        $this->db->from('grouup_order_item goi');
        $this->db->join('master_service_center msc', 'msc.msc_code = goi.service_center_code', 'inner');
        $this->db->join('product p', 'p.product_id = goi.product_id', 'inner');
        //$this->db->group_by('goi.goi_id');
        $this->db->group_by('goi.product_id');
        $this->db->order_by("msc.msc_id", "asc");
        $query = $this->db->get();
		return $query->result();
    }
    
     public function update_group_item($id,$d,$product_id,$data){
        if($id){
        $this->db->where('go_id', $id);
        $this->db->where('product_id', $product_id);
        $this->db->where('service_center_code', $d);
        return $this->db->update('grouup_order_item', $data);
        }
        else return false;
    }
    
    public function get_service_center_allocated_qty($id,$sc_code,$product_id){
        
        $this->db->select('goi.allocated_qty');
        $this->db->from('grouup_order_item goi');
        $this->db->where('goi.product_id', $product_id);
        $this->db->where('goi.go_id', $id);
        $this->db->where('goi.service_center_code', $sc_code);
        $query = $this->db->get();
		$retul=$query->row_array();
		if(isset($retul['allocated_qty'])){
		    return $retul['allocated_qty'];
		}else{
		    return 0;
		}
    }
    
    function get_service_center_order_list_final($id){
        $this->db->select('goi.*,p.product_code,p.product_name,p.product_status');
        $this->db->select_sum('goi.allocated_qty');
        $this->db->from('grouup_order_item goi');
        //$this->db->join('master_service_center msc', 'msc.msc_code = goi.service_center_code', 'inner');
        $this->db->join('product p', 'p.product_id = goi.product_id', 'inner');
        $this->db->where('goi.go_id', $id);
        $this->db->group_by('goi.product_id');
        $query = $this->db->get();
		return $query->result_array();
    }
    
     function delete_bulk_order_master_table_by_primary_id($id){
        if($id>0){
            $this->db->where("brm_id", $id);
            return $this->db->delete("bulk_requste_master");
        }else{
            return false;
        }
    }
    function delete_bulk_order_item_table_by_secondory_id($id){
        if($id>0){
            $this->db->where("brm_id", $id);
            return $this->db->delete("bulk_request_items");
        }else{
            return false;
        }
    }
    
     function getCompletedBulkRequest($start, $length, $search_key_val)
    {
        $this->db->select('brm.*,w.name');
        $this->db->from('bulk_requste_master brm');
        $this->db->join('warehouses w', 'w.id = brm.brn_to_warehouse_id', 'left');
        if ($search_key_val) {
             $this->db->where("brm.is_deleted = 0  AND brm.brm_approval_status = 1  AND  brm.brm_ref_no LIKE '$search_key_val%'");
             $this->db->where("brm.is_deleted = 0  AND brm_approval_status = 1  AND  brm.brm_manual_ref_no LIKE '$search_key_val%'");
		   
        }else{
            $this->db->where("brm.is_deleted = 0 AND brm_approval_status = 1");
        }
		$this->db->order_by("brm.brm_id", "desc");
		$this->db->group_by("brm.brm_id");
        if ($start != '' && $length != '') {
            $this->db->limit($length, $start);
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->result();
            } else {
                return false;
            }
        } else {
            $query = $this->db->get();
            if ($query->num_rows() > 0) {
                return $query->num_rows();
            } else {
                return false;
            }
        }
    }
    
    
  
}