<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
    include('core_manufacture_preparation_model.php');
class Manufacture_Preparation_Model extends Core_Manufacture_Preparation_Model
{
    public function __construct()
    {
        parent::__construct();
         $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
    }
    /* standard function */
    public function runSaveMaster($data){
        return $this->saveMaster($data);
    }
    public function runSaveItems($data){
        return $this->saveItems($data);
    }
    public function runSaveSubItems($data){
        return $this->saveSubItems($data);
    }
    public function runUpdateMasterUsingPrimaryId($id,$data){
        return $this->updateMasterUsingPrimaryId($id,$data);
    }
    public function runUpdateItemsUsingPrimaryId($id,$data){
        return $this->updateItemsUsingPrimaryId($id,$data);
    }
    public function runUpdateSubItemsUsingPrimaryId($id,$data){
        return $this->updateSubItemsUsingPrimaryId($id,$data);
    }
    public function runDeleteMasterUsingPrimaryId($id){
        return $this->deleteMasterUsingPrimaryId($id);
    }
    public function runDeleteItemsUsingPrimaryId($id){
        return $this->deleteItemsUsingPrimaryId($id);
    }
    public function runDeleteSubItemsUsingPrimaryId($id){
        return $this->deleteSubItemsUsingPrimaryId($id);
    }
    public function runSelectMasterUsingPrimaryId($id){
        return $this->selectMasterUsingPrimaryId($id);
    }
    public function runSelectItemsUsingPrimaryId($id){
        return $this->selectItemsUsingPrimaryId($id);
    }
    public function runSelectSubItemsUsingPrimaryId($id){
        return $this->selectSubItemsUsingPrimaryId($id);
    }
     public function runUpdateMasterUsingSecondaryId($id,$data){
        return $this->updateMasterUsingSecondaryId($id,$data);
    }
    public function runUpdateItemsUsingSecondaryId($id,$data){
        return $this->updateItemsUsingSecondaryId($id,$data);
    }
    public function runUpdateSubItemsUsingSecondaryId($id,$data){
        return $this->updateSubItemsUsingSecondaryId($id,$data);
    }
    public function runSelectMasterUsingSecondaryId($id){
        return $this->selectMasterUsingSecondaryId($id);
    }
    public function runSelectItemsUsingSecondaryId($id){
        return $this->selectItemsUsingSecondaryId($id);
    }
    public function runSelectSubItemsUsingSecondaryId($id){
        return $this->selectSubItemsUsingSecondaryId($id);
    }
    public function runGetPendingProduction($id,$data){
       return  $this->getPendingProduction($id,$data);
    }
    private function getPendingProduction($id,$data){
        $this->db->select('p.*');
        $this->db->select('pbi.pbi_ref_no,pbi.pbi_id,pbi.pbi_qty,pbi.defect_qty,pbi.output_qty');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('psnm.psnm_ref_no');
        $this->db->select('psoi.psoi_id');
        $this->db->from('production_batch_items pbi');
        $this->db->join('product p', 'p.product_id = pbi.pbi_product_id', 'inner');
        $this->db->join('production_store_note_item psni', 'psni.pbi_id = pbi.pbi_id', 'inner');
        $this->db->join('production_store_note_master psnm', 'psnm.psnm_id = psni.psnm_id', 'inner');
        $this->db->join('production_stock_out_item psoi', 'psoi.psnm_id = psnm.psnm_id', 'inner');
        $this->db->join('warehouses w', 'w.id = psnm.psnm_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = psnm.psnm_user_id', 'inner');
        $this->db->where("psnm.psnm_warehouse_id",$id);
        $this->db->where("psoi.psoi_status",1);
        $this->db->where("pbi.prodcution_start_status",0);
        $this->db->group_by("pbi.pbi_ref_no","asc");
        $query = $this->db->get();
        return $query->result_array();
    }
    public function runGetOngoingProduction($id,$data){
       return  $this->getOngoingProduction($id,$data);
    }
    private function getOngoingProduction($id,$data){
        $this->db->select('p.*');
        $this->db->select('pbi.pbi_ref_no,pbi.pbi_id,pbi.pbi_qty,pbi.defect_qty,pbi.output_qty,pbi.pbi_required_date,pbi.prodcution_start_status');
        //$this->db->select('w.name AS warehouse_name');
       // $this->db->select('um.user_first_name');
        $this->db->select('mtp.mtp_name');
        $this->db->from('production_batch_items pbi');
        $this->db->join('production_batch_master pbm', 'pbm.pbm_id = pbi.pbm_id', 'inner');
        $this->db->join('product p', 'p.product_id = pbi.pbi_product_id', 'inner');
       //$this->db->join('production_store_note_item psni', 'psni.pbi_id = pbi.pbi_id', 'inner');
       //$this->db->join('production_store_note_master psnm', 'psnm.psnm_id = psni.psnm_id', 'inner');
       // $this->db->join('warehouses w', 'w.id = pbm.pbm_warehouse_id', 'inner');
       // $this->db->join('user um', 'um.user_id = psnm.psnm_user_id', 'inner');
       $this->db->join('production_cost_estimation_item pcei', 'pcei.pcei_id = pbi.pcei_id', 'inner');
       $this->db->join('master_time_profile mtp', 'mtp.mtp_id = pcei.time_profile_id', 'inner');
        $this->db->where("pbm.pbm_warehouse_id",$id);
        $this->db->where("pbi.pbi_pickup_status",1);
        //$this->db->where("pbi.prodcution_start_status",0);
        $this->db->where("pbi.production_end_status",0);
        $this->db->group_by("pbi.pbi_ref_no","asc");
        $query = $this->db->get();
        return $query->result_array();
    }
    public function runGetManufacturedProduction($id,$data){
       return  $this->getManufacturedProduction($id,$data);
    }
    private function getManufacturedProduction($id,$data){
        $this->db->select('p.*');
        $this->db->select('pbi.pbi_ref_no,pbi.pbi_id,pbi.pbi_qty,pbi.defect_qty,pbi.output_qty');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('psnm.psnm_ref_no');
        $this->db->from('production_batch_items pbi');
        $this->db->join('production_batch_master pbm', 'pbm.pbm_id = pbi.pbm_id', 'inner');
        $this->db->join('product p', 'p.product_id = pbi.pbi_product_id', 'inner');
        $this->db->join('production_store_note_item psni', 'psni.pbi_id = pbi.pbi_id', 'inner');
        $this->db->join('production_store_note_master psnm', 'psnm.psnm_id = psni.psnm_id', 'inner');
        $this->db->join('warehouses w', 'w.id = pbm.pbm_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = psnm.psnm_user_id', 'inner');
        $this->db->where("psnm.psnm_warehouse_id",$id);
        $this->db->where("pbi.qc_status",0);
        $this->db->where("pbi.production_end_status",1);
        $this->db->group_by("pbi.pbi_ref_no","asc");
        $query = $this->db->get();
        return $query->result_array();
    }
     public function runGetSumBatchMaterialCost($id){
       return  $this->getSumBatchMaterialCost($id);
    }
    private function getSumBatchMaterialCost($id)
    {
        $this->db->select_sum('psio.cost');
        $this->db->from('production_stock_in_out psio');
        $this->db->where("psio.pbi_id",$id);
        $this->db->where("psio.is_deletded = 0");
        $this->db->where("psio.pick_up_status = 1");
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['cost'])){
            return $result['cost'];
        }else{
            return 0;
        }
    }
    public function runGetSumAdditionalBatchMaterialCost($id){
       return  $this->getSumAdditionalBatchMaterialCost($id);
    }
    private function getSumAdditionalBatchMaterialCost($id)
    {
        $this->db->select_sum('psio.cost');
        $this->db->from('production_stock_in_out psio');
        $this->db->where("psio.pbi_id",$id);
        $this->db->where("psio.is_deletded = 0");
        $this->db->where("psio.pick_up_status = 1");
        $this->db->where("psio.psio_request_type = 2");
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['cost'])){
            return $result['cost'];
        }else{
            return 0;
        }
    }
    public function runGetQCManufacturedProduction($id,$data){
       return  $this->getQCManufacturedProduction($id,$data);
    }
    private function getQCManufacturedProduction($id,$data){
        $this->db->select('p.product_name,p.product_id,p.product_code');
        $this->db->select('pbi.pbi_ref_no,pbi.pbi_id,pbi.pbi_qty,pbi.defect_qty,pbi.output_qty,pbi.qc_on,pbi.pbi_unit_cost,pbi.pbi_lost_value');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('psnm.psnm_ref_no');
        $this->db->from('production_batch_items pbi');
        $this->db->join('production_batch_master pbm', 'pbm.pbm_id = pbi.pbm_id', 'inner');
        $this->db->join('product p', 'p.product_id = pbi.pbi_product_id', 'inner');
        $this->db->join('production_store_note_item psni', 'psni.pbi_id = pbi.pbi_id', 'inner');
        $this->db->join('production_store_note_master psnm', 'psnm.psnm_id = psni.psnm_id', 'inner');
        $this->db->join('warehouses w', 'w.id = pbm.pbm_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = pbi.qc_by', 'inner');
        $this->db->where("psnm.psnm_warehouse_id",$id);
        $this->db->where("pbi.qc_status",1);
        $this->db->where("pbi.finish_status",0);
        $this->db->group_by("pbi.pbi_ref_no","asc");
        $query = $this->db->get();
        return $query->result_array();
    }
    public function runGetBtachProductDetails($id){
       return  $this->getBtachProductDetails($id);
    }
    private function getBtachProductDetails($id){
        $this->db->select('p.product_type_id,p.sub_product_of');
        $this->db->select('pbi.*');
        $this->db->from('production_batch_items pbi');
        $this->db->join('product p', 'p.product_id = pbi.pbi_product_id', 'inner');
        $this->db->where("pbi.pbi_id",$id);
        $query = $this->db->get();
        return $query->row_array();
    }
    public function runGetQCSelectedGTNManufacturedProduction($id,$data){
       return  $this->getQCSelectedGTNManufacturedProduction($id,$data);
    }
    private function getQCSelectedGTNManufacturedProduction($id,$data){
        $this->db->select('p.product_name,p.product_id,p.product_code');
        $this->db->select('pbi.pbi_ref_no,pbi.pbi_id,pbi.pbi_qty,pbi.defect_qty,pbi.output_qty,pbi.qc_on');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('pgi.pgrnp_id,pgi.pgrnp_status,pgi.quantity');
        $this->db->select('pg.pgrnm_status');
        $this->db->from('production_grn_items pgi');
        $this->db->join('production_grn pg', 'pg.pgrnm_id = pgi.pgrnm_id', 'inner');
        $this->db->join('production_batch_items pbi', 'pbi.pbi_id = pgi.pbi_id', 'inner');
        $this->db->join('product p', 'p.product_id = pbi.pbi_product_id', 'inner');
        $this->db->join('warehouses w', 'w.id = pg.pgrnm_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = pbi.qc_by', 'inner');
        $this->db->where("pgi.pgrnm_id",$id);
        $this->db->group_by("pbi.pbi_ref_no","asc");
        $query = $this->db->get();
        return $query->result_array();
    }
    
    
    
     public function runSaveIntermediateItems($data){
       return  $this->saveIntermediateItems($data);
    }
     private function saveIntermediateItems($pd){
        if ($this->db->insert('production_grn_intermediat_product', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
     public function runGetPendingInternalGTN($id){
       return  $this->getPendingInternalGTN($id);
    }
    private function getPendingInternalGTN($id)
    {
        $this->db->select('pg.*');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->from('production_grn pg');
         $this->db->join('warehouses w', 'w.id = pg.pgrnm_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = pg.pgrnm_user_id', 'left');
        $this->db->where("pg.pgrnm_warehouse_id",$id);
        //$this->db->where("pg.pgrnm_status",0);
        $this->db->where("pg.pgrnm_pick_up_status",0);
        $query = $this->db->get();
        return $query->result(); 
    }
    
    public function runGetPendingAdditionalBatchItem($id){
       return  $this->getPendingAdditionalBatchItem($id);
    }
    private function getPendingAdditionalBatchItem($id)
    {
        $this->db->select('pb.*');
        $this->db->select('p.*,u.unit_name,mpt.mpt_name');
        $this->db->from('production_batch_sub_items pb');
        $this->db->join('product_material p', 'p.product_mat_id = pb.pbsi_product_mat_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("pb.pbi_id",$id);
        $query = $this->db->get();
        return $query->result_array(); 
    }
    
    
    public function runGetProductMAterialDetails($id){
       return  $this->getProductMAterialDetails($id);
    }
     private function getProductMAterialDetails($id)
    {
        $this->db->select('p.*');
        $this->db->from('product_material p');
        $this->db->where("p.product_mat_id",$id);
        $query = $this->db->get();
        return $query->row_array(); 
    }
    
    
     public function runSaveBatchSubItemTable($data){
       return  $this->saveBatchSubItemTable($data);
    }
     private function saveBatchSubItemTable($pd){
        if ($this->db->insert('production_batch_sub_items', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    public function runGetProductBatchDetails($id){
       return  $this->getProductBatchDetails($id);
    }
     private function getProductBatchDetails($id)
    {
        $this->db->select('p.*');
         $this->db->select('pm.*');
        $this->db->from('production_batch_items p');
        $this->db->join('production_batch_master pm', 'pm.pbm_id = p.pbm_id', 'inner');
        $this->db->where("p.pbi_id",$id);
        $query = $this->db->get();
        return $query->row_array(); 
    }
     public function runSaveSTNMasterTable($data){
       return  $this->saveSTNMasterTable($data);
    }
     private function saveSTNMasterTable($pd){
        if ($this->db->insert('production_store_note_master', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
     public function runSaveSTNItemTable($data){
       return  $this->saveSTNItemTable($data);
    }
     private function saveSTNItemTable($pd){
        if ($this->db->insert('production_store_note_item', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
     public function runSaveSTNSubItemTable($data){
       return  $this->saveSTNSubItemTable($data);
    }
     private function saveSTNSubItemTable($pd){
        if ($this->db->insert('production_store_note_sub_items', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
    public function runGetIssuedBatchItem($id){
       return  $this->getIssuedBatchItem($id);
    }
    private function getIssuedBatchItem($id)
    {
        $this->db->select('pb.*');
        $this->db->select('p.*,u.unit_name,mpt.mpt_name');
        $this->db->from('production_stock_in_out pb');
        $this->db->join('product_material p', 'p.product_mat_id = pb.product_mat_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("pb.pbi_id",$id);
        $query = $this->db->get();
        return $query->result_array(); 
    }
    
     public function runGetMAterialIssueDetails($id){
       return  $this->getMAterialIssueDetails($id);
    }
     private function getMAterialIssueDetails($id)
    {
        $this->db->select('p.*');
        $this->db->from('production_stock_in_out p');
        $this->db->where("p.psio_id",$id);
        $query = $this->db->get();
        return $query->row_array(); 
    }
    public function runSaveSTNIssueItemTable($data){
       return  $this->saveSTNIssueItemTable($data);
    }
     private function saveSTNIssueItemTable($pd){
        if ($this->db->insert('production_stock_in_out', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
    public function getOngoingProductionNew($id,$time_id,$date){
        $this->db->select('p.*');
        $this->db->select('pbi.pbi_ref_no,pbi.pbi_id,pbi.pbi_qty,pbi.defect_qty,pbi.output_qty,pbi.pbi_required_date,pbi.prodcution_start_status');
        $this->db->select('mtp.mtp_name');
        $this->db->from('production_batch_items pbi');
        $this->db->join('production_batch_master pbm', 'pbm.pbm_id = pbi.pbm_id', 'inner');
        $this->db->join('product p', 'p.product_id = pbi.pbi_product_id', 'inner');
       $this->db->join('production_cost_estimation_item pcei', 'pcei.pcei_id = pbi.pcei_id', 'left');
       $this->db->join('master_time_profile mtp', 'mtp.mtp_id = pcei.time_profile_id', 'left');
        if($date){
            //$this->db->where('DATE(pbi.pbi_required_date)', $date);
        }
        if($time_id){
          //$this->db->where("pcei.time_profile_id",$time_id);  
        }
        $this->db->where("pbi.stock_issue_status",1);
        $this->db->where("pbi.production_end_status",0);
        $this->db->order_by("pcei.time_profile_id","asc");
        $this->db->order_by("pbi.pbi_required_date","asc");
        $query = $this->db->get();
        return $query->result_array();
    }
    
    
      public function update_product_table($id,$data){
        if($id){
        $this->db->where('product_id', $id);
       // $this->db->where('id', $id);
        return $this->db->update('product', $data);
        }
        else return false;
    }
    
    public function getPendingManufactureIngreediantList($id){
        $this->db->select('u.unit_name,mpt.mpt_name,p.product_mat_name,p.product_mat_code');
        $this->db->select_sum('psio.quantity');
        $this->db->from('production_stock_in_out psio');
        $this->db->join('production_batch_items pbi', 'pbi.pbi_id = psio.pbi_id', 'inner');
        $this->db->join('product_material p', 'p.product_mat_id = psio.product_mat_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.stock_issue_status",1);
        $this->db->where("pbi.prodcution_start_status",0);
        $this->db->group_by('psio.product_mat_id');
        $query = $this->db->get();
        return $query->result_array();
    }
    
    
    function get_row_matirial_and_intermediate_product($search_key_val){
		$this->db->select('p.*,u.unit_code,b.batch_id');
		$this->db->from('batch_product b');
		 $this->db->join('product_material p', 'p.product_mat_id = b.product_id', 'inner');
		 $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('b.batch_id');
		$this->db->limit(50);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	
	 public function save_additional_requste_master_table($pd){
        if ($this->db->insert('addtional_items', $pd)) {
            $lst = $this->db->insert_id();
            return $lst;
        } else {
            return false;
        }
    }
    
    function get_product_id($id){
         $this->db->select('p.product_id');
        $this->db->from('batch_product p');
        $this->db->where("p.batch_id",$id);
        $query = $this->db->get();
        $result =  $query->row_array(); 
        if(isset($result['product_id'])){
            return $result['product_id'];
        }else{
            return 0;
        }
    }
    function getAddtionalReqList(){
        $this->db->select('ai.ad_item_id,p.product_mat_name,p.product_mat_code,u.unit_code,pbi.pbi_ref_no,ai.ad_item_id,ai.req_qty,ai.request_on,f.product_name,f.product_code,ai.request_type');
		$this->db->from('addtional_items ai');
		$this->db->join('product_material p', 'p.product_mat_id = ai.product_id', 'inner');
		$this->db->join('mstr_unit u', 'u.unit_id = p.product_mat_unit', 'left');
		$this->db->join('production_batch_items pbi', 'pbi.pbi_id = ai.pbi_id', 'left');
		$this->db->join('product f', 'f.product_id = pbi.pbi_product_id', 'inner');
		$this->db->where("ai.ad_req_issue_status",0);
		$this->db->order_by("p.product_mat_name", "asc");
		$this->db->group_by('ai.ad_item_id');
		$query = $this->db->get();
		return $query->result_array();
    }
    
    function get_pbi_row_matirial_and_intermediate_product($id,$search_key_val){
		$this->db->select('p.*,u.unit_code,b.batch_id');
		$this->db->from('batch_product b');
		 $this->db->join('product_material p', 'p.product_mat_id = b.product_id', 'inner');
		 $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('b.batch_id');
		$this->db->limit(50);
		$query = $this->db->get();
		return $query->result_array();
	}
	function get_transferred_gt_qty($id){
	    $this->db->select_sum('p.quantity');
		$this->db->from('production_grn_items p');
	    $this->db->where("p.pbi_id",$id);
		$query = $this->db->get();
		$result= $query->row_array();
		if(isset($result['quantity'])){
		    return $result['quantity'];
		}else{
		    return 0;
		}
	    
	}
  
}