<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
include('core_production_estimate_model.php');
class Production_Estimate_Model extends Core_Production_Estimate_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 runUpdateMaster($id, $data)
    {
        return $this->updateMaster($id, $data);
    }
    public function runUpdateItems($id, $data)
    {
        return $this->updateItems($id, $data);
    }
    public function runUpdateSubItems($id, $data)
    {
        return $this->updateSubItems($id, $data);
    }
    public function runDeleteMaster($id)
    {
        return $this->deleteMaster($id);
    }
    public function runDeleteItems($id)
    {
        return $this->deleteItems($id);
    }
    public function runDeleteSubItems($id)
    {
        return $this->deleteSubItems($id);
    }
    public function runSelectMaster($id)
    {
        return $this->selectMaster($id);
    }
    public function runSelectItems($id)
    {
        return $this->selectItems($id);
    }
    public function runSelectSubItems($id)
    {
        return $this->selectSubItems($id);
    }
    /*customizable function*/
    /*Save functions*/
    /*update  functions*/
    public function loadUpdateRequsteMaster($id, $data)
    {
        return $this->updateRequsteMaster($id, $data);
    }
    private function updateRequsteMaster($id, $data)
    {
        if ($id > 0) {
            $this->db->where('brm_id', $id);
            return $this->db->update('bulk_requste_master', $data);
        } else {
            return false;
        }
    }
    /*delete functions*/
    public function deleteSubItemsWithItem($id)
    {
        $staus = $this->deleteItems($id);
        $staus = $this->deleteSubItemByItemId($id);
        return $staus;
    }
    private function deleteSubItemByItemId($id)
    {
        if ($id > 0) {
            $this->db->where('pcei_id', $id);
            return $this->db->delete('production_cost_estimation_sub_item');
        } else {
            return false;
        }
    }
    /*select functions*/
    public function loadGetApprovedRequestListServerSide($start, $length, $search_key_val)
    {
        return $this->getApprovedRequestListServerSide($start, $length, $search_key_val);
    }
    private function getApprovedRequestListServerSide($start, $length, $search_key_val)
    {
        $this->db->select('p.*');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('ua.user_first_name AS approval_by');
        $this->db->from('bulk_requste_master p');
        $this->db->join('warehouses w', 'w.id = p.brn_to_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = p.added_user_id', 'inner');
        $this->db->join('user ua', 'ua.user_id = p.approval_by', 'left');
        if ($search_key_val) {
            $this->db->where("p.is_deleted = 0 AND p.pickup_status= 0 AND p.brm_approval_status = 1 AND p.brm_ref_no LIKE '$search_key_val%'");
            $this->db->or_where("p.is_deleted = 0 AND p.pickup_status = 0 AND p.brm_approval_status = 1 AND p.brm_manual_ref_no LIKE '%$search_key_val%'");
        } else {
            $this->db->where("p.is_deleted = 0 AND p.pickup_status = 0 AND p.brm_approval_status = 1");
        }
        $this->db->order_by("p.brm_id", "asc");
        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 loadGetRequestAllDetails($id)
    {
        return $this->getRequestAllDetails($id);
    }
    private function getRequestAllDetails($id)
    {
        $this->db->select('p.*');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('um.user_first_name AS approval_by');
        $this->db->from('bulk_requste_master p');
        $this->db->join('warehouses w', 'w.id = p.brn_to_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = p.added_user_id', 'inner');
        $this->db->join('user ua', 'ua.user_id = p.approval_by', 'left');
        $this->db->where('p.brm_id', $id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row_array();
        } else {
            return false;
        }
    }
    public function loadPendingEstimateListServerSide($start, $length, $search_key_val)
    {
        return $this->getPendingEstimateListServerSide($start, $length, $search_key_val);
    }
    private function getPendingEstimateListServerSide($start, $length, $search_key_val)
    {
        $this->db->select('p.*');
        $this->db->select('bm.brm_ref_no,bm.brm_id');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('uu.user_first_name AS modify_by');
        $this->db->from('production_cost_estimation_master p');
        $this->db->join('bulk_requste_master bm', 'bm.brm_id = p.pcem_request_id', 'inner');
        $this->db->join('warehouses w', 'w.id = p.pcem_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = p.pcem_user', 'inner');
        $this->db->join('user uu', 'uu.user_id = p.last_modify_by', 'left');
        if ($search_key_val) {
            $this->db->where("p.is_deleted = 0  AND p.pcem_approval_status = 0 AND p.pcem_id_ref_no LIKE '$search_key_val%'");
            $this->db->or_where("p.is_deleted = 0 AND  p.pcem_approval_status = 0 AND bm.brm_ref_no LIKE '%$search_key_val%'");
        } else {
            $this->db->where("p.is_deleted = 0 AND  p.pcem_approval_status = 0");
        }
        $this->db->order_by("p.pcem_id", "asc");
        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 loadGetEstimateAllDetails($id)
    {
        return $this->getEstimateAllDetails($id);
    }
    private function getEstimateAllDetails($id)
    {
        $this->db->select('p.*');
        $this->db->select('bm.brm_ref_no,bm.brm_id');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('uu.user_first_name AS modify_by');
        $this->db->from('production_cost_estimation_master p');
        $this->db->join('bulk_requste_master bm', 'bm.brm_id = p.pcem_request_id', 'inner');
        $this->db->join('warehouses w', 'w.id = p.pcem_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = p.pcem_user', 'inner');
        $this->db->join('user uu', 'uu.user_id = p.last_modify_by', 'left');
        $this->db->where('p.pcem_id', $id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row_array();
        } else {
            return false;
        }
    }
    public function loadGetEstimateItemAllDetails($id)
    {
        return $this->getEstimateItemAllDetails($id);
    }
    private function getEstimateItemAllDetails($id)
    {
        $this->db->select('pi.*');
        $this->db->select('p.product_name,p.product_code');
        $this->db->select('pbm.product_block_code,pbm.batch_qty');
        $this->db->from('production_cost_estimation_item pi');
        $this->db->join('product p', 'p.product_id = pi.product_id', 'inner');
        $this->db->join('production_block_master pbm', 'pbm.pbm_id = pi.product_block_id', 'inner');
        $this->db->where('pi.pcem_id', $id);
        $query = $this->db->get();
        return $query->result_array();
    }
    public function loadGetEstimateSubItemAllDetails($id)
    {
        return $this->getEstimateSubItemAllDetails($id);
    }
    private function getEstimateSubItemAllDetails($id)
    {
        $this->db->select('pi.final_output_qty');
        $this->db->select('psi.*');
        $this->db->select('pm.product_mat_name,pm.product_mat_code');
        $this->db->select('p.product_name,p.product_code');
        $this->db->from('production_cost_estimation_sub_item psi');
        $this->db->join('product_material pm', 'pm.product_mat_id = psi.product_mat_id', 'inner');
        $this->db->join('production_cost_estimation_item pi', 'pi.pcei_id = psi.pcei_id', 'inner');
        $this->db->join('product p', 'p.product_id = pi.product_id', 'inner');
        $this->db->where('pi.pcem_id', $id);
        $query = $this->db->get();
        return $query->result_array();
    }
    public function loadUpdatePendingEstimate($id, $data)
    {
        return $this->getUpdatePendingEstimate($id, $data);
    }
    private function getUpdatePendingEstimate($id, $data)
    {
        $this->db->select('p.* ,u.unit_name,mpt.mpt_name,bri.request_qty,bri.bri_id,bri.approved_qty,pcem.pcem_id,pcei.pcei_id');
        $this->db->from('bulk_request_items bri');
        $this->db->join('product p', 'bri.product_id = p.product_id', 'left');
        $this->db->join('production_cost_estimation_item pcei', 'bri.bri_id=pcei.order_request_item_id', 'left');
        $this->db->join('production_cost_estimation_master pcem', 'bri.brm_id = pcem.pcem_request_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);
        $this->db->group_by("bri.bri_id");
        $query = $this->db->get();
        return $query->result_array();
    }
    public function loadTotalAllocatedEstiateQty($id, $signature)
    {
        $mf_sign = 125564;
        if ($mf_sign == $signature) {
            return $this->getTotalAllocatedEstiateQty($id);
        } else {
            return false;
        }
    }
    private function getTotalAllocatedEstiateQty($id)
    {
        $this->db->select_sum('ei.allocated_qty');
        $this->db->from('production_cost_estimation_item ei');
        $this->db->where("ei.order_request_item_id", $id);
        $query  = $this->db->get();
        $result = $query->row_array();
        if (isset($result['allocated_qty'])) {
            return $result['allocated_qty'];
        } else {
            return 0;
        }
    }
    public function loadAllocatedEstimationItem($id, $signature)
    {
        $mf_sign = 125565;
        if ($mf_sign == $signature) {
            return $this->getAllocatedEstimationItem($id);
        } else {
            return false;
        }
    }
    private function getAllocatedEstimationItem($id)
    {
        $this->db->select('pc.*,pbm.product_block_code,pbm.batch_qty,mtp.mtp_name');
        $this->db->from('production_cost_estimation_item pc');
        $this->db->join('production_block_master pbm', 'pbm.pbm_id=pc.product_block_id', 'inner');
        $this->db->join('master_time_profile mtp', 'mtp.mtp_id=pc.time_profile_id', 'inner');
        $this->db->where("pc.order_request_item_id", $id);
        $query = $this->db->get();
        return $query->result_array();
    }
    public function loadProductBlock($id, $signature)
    {
        $mf_sign = 125566;
        if ($mf_sign == $signature) {
            return $this->getProductBlock($id);
        } else {
            return false;
        }
    }
    private function getProductBlock($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();
    }
    public function loadProductBlockDetail($id, $signature)
    {
        $mf_sign = 125567;
        if ($mf_sign == $signature) {
            return $this->getProductBlockDetails($id);
        } else {
            return false;
        }
    }
    private function getProductBlockDetails($id)
    {
        $this->db->select('pbm.*');
        $this->db->from('production_block_master pbm');
        $this->db->where("pbm.pbm_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->row_array();
    }
    public function loadProductBlockItemList($id, $signature)
    {
        $mf_sign = 125568;
        if ($mf_sign == $signature) {
            return $this->getProductBlockItemList($id);
        } else {
            return false;
        }
    }
    private function getProductBlockItemList($id)
    {
        $this->db->select('pdi.*,p.product_mat_cost');
        $this->db->from('production_block_item pdi');
        $this->db->join('product_material p', 'p.product_mat_id=pdi.product_id', 'inner');
        $this->db->where("pdi.pdm_id", $id);
        $this->db->where("pdi.is_deleted", 0);
        $this->db->where("pdi.pdi_status", 1);
        $query = $this->db->get();
        return $query->result_array();
    }
    public function runSelectOrderRequestByMasterId($id)
    {
        return $this->selectOrderRequestByMasterId($id);
    }
    private function selectOrderRequestByMasterId($id)
    {
        $this->db->select('bri.*');
        $this->db->from('bulk_request_items bri');
        $this->db->where("bri.is_deleted = 0");
        $this->db->where("bri.brm_id", $id);
        $query = $this->db->get();
        return $query->result_array();
    }
    public function runSelectSumEstimatedItemByMasterId($id)
    {
        return $this->selectSumEstimatedItemByMasterId($id);
    }
    private function selectSumEstimatedItemByMasterId($id)
    {
        $this->db->select_sum('pcei.pcei_cost');
        $this->db->from('production_cost_estimation_item pcei');
        $this->db->where("pcei.is_deleted = 0");
        $this->db->where("pcei.pcem_id", $id);
        $query  = $this->db->get();
        $result = $query->row_array();
        if (isset($result['pcei_cost'])) {
            return $result['pcei_cost'];
        } else {
            return 0;
        }
    }
    public function loadComplededEstimateListServerSide($start, $length, $search_key_val)
    {
        return $this->getCompletedEstimateListServerSide($start, $length, $search_key_val);
    }
    private function getCompletedEstimateListServerSide($start, $length, $search_key_val)
    {
        $this->db->select('p.*');
        $this->db->select('bm.brm_ref_no,bm.brm_id');
        $this->db->select('w.name AS warehouse_name');
        $this->db->select('um.user_first_name');
        $this->db->select('uu.user_first_name AS modify_by');
        $this->db->from('production_cost_estimation_master p');
        $this->db->join('bulk_requste_master bm', 'bm.brm_id = p.pcem_request_id', 'inner');
        $this->db->join('warehouses w', 'w.id = p.pcem_warehouse_id', 'inner');
        $this->db->join('user um', 'um.user_id = p.pcem_user', 'inner');
        $this->db->join('user uu', 'uu.user_id = p.last_modify_by', 'left');
        if ($search_key_val) {
            $this->db->where("p.is_deleted = 0 AND p.pcem_pickup_status = 0  AND p.pcem_approval_status = 1 AND p.pcem_id_ref_no LIKE '$search_key_val%'");
            $this->db->or_where("p.is_deleted = 0 AND p.pcem_pickup_status = 0  AND  p.pcem_approval_status = 1 AND bm.brm_ref_no LIKE '%$search_key_val%'");
        } else {
            $this->db->where("p.is_deleted = 0 AND p.pcem_pickup_status = 0  AND  p.pcem_approval_status = 1");
        }
        $this->db->order_by("p.pcem_id", "asc");
        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 loadTimeProfile($signature)
    {
        $mf_sign = 125567;
        if ($mf_sign == $signature) {
            return $this->getTimeProfile();
        } else {
            return false;
        }
    }
    private function getTimeProfile()
    {
        $this->db->select('mtp.*');
        $this->db->from('master_time_profile mtp');
        $this->db->where("mtp.mtp_status", 1);
        $query = $this->db->get();
        return $query->result_array();
    }
    //update 2022-06-03
    public function getEstimatedItem($id, $data)
    {
        $this->db->select('p.product_name,p.product_code,pcei.* ,u.unit_name,mpt.mpt_name,mtp.mtp_name');
        $this->db->from('production_cost_estimation_item pcei');
        $this->db->join('product p', 'pcei.product_id = p.product_id', 'inner');
        $this->db->join('mstr_unit u', 'u.unit_id = p.product_unit', 'inner');
        $this->db->join('master_product_type mpt', 'mpt.mpt_id = p.product_type_id', 'inner');
        $this->db->join('master_time_profile mtp', 'mtp.mtp_id=pcei.time_profile_id', 'inner');
        $this->db->where("pcei.is_deleted = 0");
        $this->db->where("pcei.pcem_id", $id);
        $this->db->group_by("pcei.pcei_id");
        $this->db->order_by("pcei.time_profile_id", 'asc');
        $query = $this->db->get();
        return $query->result_array();
    }
    public function getEstimatedEngrediantsItem($param_data)
    {
        $this->db->select('p.product_mat_name,p.product_mat_code,pcesi.* ,u.unit_name,u.unit_code');
        $this->db->from('production_cost_estimation_sub_item pcesi');
        $this->db->join('production_cost_estimation_item pcei', 'pcesi.pcei_id = pcei.pcei_id', 'inner');
        $this->db->join('product_material p', 'pcesi.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("pcesi.is_deleted = 0");
        $this->db->where("pcei.pcem_id", $param_data['id']);
        $this->db->group_by("pcesi.pcesi_id");
        $query = $this->db->get();
        return $query->result_array();
    }
    public function getEstimatedEngrediantsSingleItems($param_data)
    {
        $this->db->select('p.product_mat_name,p.product_mat_code,pcesi.* ,u.unit_name');
        $this->db->from('production_cost_estimation_sub_item pcesi');
        $this->db->join('production_cost_estimation_item pcei', 'pcesi.pcei_id = pcei.pcei_id', 'inner');
        $this->db->join('product_material p', 'pcesi.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("pcesi.is_deleted = 0");
        $this->db->where("pcei.pcem_id", $param_data['id']);
        $this->db->group_by("pcesi.product_mat_id");
        $query = $this->db->get();
        return $query->result_array();
    }
    function defect_list($defect_type_id = 1){
        $this->db->where('defect_type_id',$defect_type_id);
        $this->db->where('status',1);
        $res = $this->db->get('defect_reasons');
        return $res->result();
    }
    
    function delete_estimate_master_table_by_primary_id($id){
        if($id>0){
            $this->db->where("pcem_id", $id);
            return $this->db->delete("production_cost_estimation_master");
        }else{
            return false;
        }
    }
    function delete_estimate_item_table_by_secondory_id($id){
        if($id>0){
            $this->db->where("pcem_id", $id);
            return $this->db->delete("production_cost_estimation_item");
        }else{
            return false;
        }
    }
}