<?php

/* 	
 * 	Author 	: Dhidhik
 * 	Tanggal : 12-12-2015	
 * 	Revisi 	: 
 */

class M_index_inventory extends CI_Model {    
    public $tbl_              = "";
    
    
    public function pre_get_all_inventory_qty_list() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Category       = ($this->input->post('txt_itemCat')!=null)?$this->input->post('txt_itemCat'):'';
        $Item           = ($this->input->post('txt_item')!=null)?$this->input->post('txt_item'):'';
        $DateFrom       = ($this->input->post('txt_DateFrom')!=null)?$this->input->post('txt_DateFrom'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $TransStatus    = ($this->input->post('chk_TransStatus')!=null)?$this->input->post('chk_TransStatus'):'';
        $txtTable       = ($this->input->post('txtTable')!=null)?$this->input->post('txtTable'):'';
        $txtField       = ($this->input->post('txtField')!=null)?$this->input->post('txtField'):'';
        $typeShorting   = ($this->input->post('typeShorting')!=null)?$this->input->post('typeShorting'):'';
        
        $Pages          = ($this->input->post('pages')!=null)?$this->input->post('pages'):'';
        $limitStart     = ($Pages['limit'] * $Pages['pages']) - $Pages['limit'];
        /* =================================END post dari find di kiri==================================================== */
           
        /* ==========================================by session======================================================= */
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */
    
        $this->db->select(""
            ."
                av_item_trans_app_report.item_code           AS code
            "
            );        
        $this->db->from('av_item_trans_app_report');
        $this->db->join("am_itemcategory", "am_itemcategory.ItemCategoryID = av_item_trans_app_report.item_category","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = av_item_trans_app_report.branch_code","inner");
        
        $this->db->where("(av_item_trans_app_report.trans_type='Receive Item' or av_item_trans_app_report.trans_type='Adjustment' "
                . "or av_item_trans_app_report.trans_type='Initial Amount' or av_item_trans_app_report.trans_type='Roll Over' "
                . "or av_item_trans_app_report.trans_type='Delivery Order' or av_item_trans_app_report.trans_type='Job Costing' "
                . " or av_item_trans_app_report.trans_type='Purchase Return'  or av_item_trans_app_report.trans_type='Sales Return' )", null,false);
        
        //==========================================================================================================//
     
        /* ===================================find by ItemCategoryName code========================================================= */
        if ($Category != "" && $Category != "All" ) {
            $this->db->like('am_itemcategory.ItemCategoryName', $Category);
        } 
        /* ==================================end find by ItemCategoryName code===================================================== */
        
        /* ===================================find by item_desc code========================================================= */
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('av_item_trans_app_report.item_desc', $Item);
        } 
        /* ==================================end find by item_desc code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('av_item_trans_app_report.branch_code', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(av_item_trans_app_report.branch_code,1,LENGTH('".$CompCode."')+1)=concat('".$CompCode."','-')", null); 
        } else {
            $this->db->where("SUBSTRING(av_item_trans_app_report.branch_code,1,LENGTH('".$sess_company."')+1)=concat('".$sess_company."','-')", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('av_item_trans_app_report.trans_date >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('av_item_trans_app_report.trans_date <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ============================================== TRANSTATUS TABLE========================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('av_item_trans_app_report.trans_status', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('av_item_trans_app_report.trans_status', array(2, 1));
        }
        /* ==============================================END TRANSTATUS TABLE========================================================== */        
        
        $this->db->group_by("av_item_trans_app_report.item_code");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('av_item_trans_app_report.trans_date', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        
        $this->db->limit($Pages['limit'], $limitStart);
        $query = $this->db->get();
        //echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        return $result;
    }
    public function get_all_inventory_qty_list() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Category       = ($this->input->post('txt_itemCat')!=null)?$this->input->post('txt_itemCat'):'';
        $Item           = ($this->input->post('txt_item')!=null)?$this->input->post('txt_item'):'';
        $DateFrom       = ($this->input->post('txt_DateFrom')!=null)?$this->input->post('txt_DateFrom'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $TransStatus    = ($this->input->post('chk_TransStatus')!=null)?$this->input->post('chk_TransStatus'):'';
        $txtTable       = ($this->input->post('txtTable')!=null)?$this->input->post('txtTable'):'';
        $txtField       = ($this->input->post('txtField')!=null)?$this->input->post('txtField'):'';
        $typeShorting   = ($this->input->post('typeShorting')!=null)?$this->input->post('typeShorting'):'';
        
        $PreData        = ($this->input->post('predata')!=[null])?$this->input->post('predata'):'';
        /* =================================END post dari find di kiri==================================================== */
           
        /* ==========================================by session======================================================= */
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */
    
        $this->db->select(""
            ."
            distinct(av_item_trans_app_report.no_transaksi)          AS no_transaksi_av
            , av_item_trans_app_report.item_code           AS item_code_av
            , av_item_trans_app_report.item_desc           AS item_desc_av
            , av_item_trans_app_report.item_qty            AS item_qty_av
            , av_item_trans_app_report.item_unit           AS item_unit_av
            , av_item_trans_app_report.item_category       AS item_category_av
            , av_item_trans_app_report.trans_status        AS trans_status_av
            , av_item_trans_app_report.trans_date          AS trans_date_av
            , av_item_trans_app_report.branch_code         AS branch_code_av
            , av_item_trans_app_report.company_code        AS company_code_av
            , av_item_trans_app_report.trans_type          AS trans_type_av
            , am_itemcategory.ItemCategoryName      AS ItemCategoryName_av
            , am_branch.BranchName                  AS BranchName_av
            "
            );        
        $this->db->from('av_item_trans_app_report');
        $this->db->join("am_itemcategory", "am_itemcategory.ItemCategoryID = av_item_trans_app_report.item_category","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = av_item_trans_app_report.branch_code","inner");
        
        $this->db->where("(av_item_trans_app_report.trans_type='Receive Item' or av_item_trans_app_report.trans_type='Adjustment' "
                . "or av_item_trans_app_report.trans_type='Initial Amount' or av_item_trans_app_report.trans_type='Roll Over' "
                . "or av_item_trans_app_report.trans_type='Delivery Order' or av_item_trans_app_report.trans_type='Job Costing' "
                . " or av_item_trans_app_report.trans_type='Purchase Return'  or av_item_trans_app_report.trans_type='Sales Return' )", null,false);
        
        $this->db->where_in('av_item_trans_app_report.item_code', $PreData);
        //==========================================================================================================//
     
        /* ===================================find by ItemCategoryName code========================================================= */
        if ($Category != "" && $Category != "All" ) {
            $this->db->like('am_itemcategory.ItemCategoryName', $Category);
        } 
        /* ==================================end find by ItemCategoryName code===================================================== */
        
        /* ===================================find by item_desc code========================================================= */
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('av_item_trans_app_report.item_desc', $Item);
        } 
        /* ==================================end find by item_desc code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('av_item_trans_app_report.branch_code', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(av_item_trans_app_report.branch_code,1,LENGTH('".$CompCode."')+1)=concat('".$CompCode."','-')", null); 
        } else {
            $this->db->where("SUBSTRING(av_item_trans_app_report.branch_code,1,LENGTH('".$sess_company."')+1)=concat('".$sess_company."','-')", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('av_item_trans_app_report.trans_date >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('av_item_trans_app_report.trans_date <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ============================================== TRANSTATUS TABLE========================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('av_item_trans_app_report.trans_status', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('av_item_trans_app_report.trans_status', array(2, 1));
        }
        /* ==============================================END TRANSTATUS TABLE========================================================== */        
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('av_item_trans_app_report.trans_date', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        
        $query = $this->db->get();
        //echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        return $result;
    }
    
    public function pre_get_all_inventory_qty_cogs_list() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Category       = ($this->input->post('txt_itemCat')!=null)?$this->input->post('txt_itemCat'):'';
        $Item           = ($this->input->post('txt_item')!=null)?$this->input->post('txt_item'):'';
        $DateFrom       = ($this->input->post('txt_DateFrom')!=null)?$this->input->post('txt_DateFrom'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $TransStatus    = ($this->input->post('chk_TransStatus')!=null)?$this->input->post('chk_TransStatus'):'';
        $txtTable       = ($this->input->post('txtTable')!=null)?$this->input->post('txtTable'):'';
        $txtField       = ($this->input->post('txtField')!=null)?$this->input->post('txtField'):'';
        $typeShorting   = ($this->input->post('typeShorting')!=null)?$this->input->post('typeShorting'):'';
        
        $Pages          = ($this->input->post('pages')!=null)?$this->input->post('pages'):'';
        $limitStart     = ($Pages['limit'] * $Pages['pages']) - $Pages['limit'];
        /* =================================END post dari find di kiri==================================================== */
           
        /* ==========================================by session======================================================= */
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */
    
        $this->db->select(""
            ."
                av_item_trans_app_report.item_code           AS code
            "
            );        
        $this->db->from('av_item_trans_app_report');
        $this->db->join("am_itemcategory", "am_itemcategory.ItemCategoryID = av_item_trans_app_report.item_category","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = av_item_trans_app_report.branch_code","inner");
        $this->db->join("at_inventories", "at_inventories.ItemCode = av_item_trans_app_report.item_code "
                . "and at_inventories.BranchCode = av_item_trans_app_report.branch_code","inner");
        
        $this->db->where("(av_item_trans_app_report.trans_type='Receive Item' or av_item_trans_app_report.trans_type='Adjustment' "
                . "or av_item_trans_app_report.trans_type='Initial Amount' or av_item_trans_app_report.trans_type='Roll Over' "
                . "or av_item_trans_app_report.trans_type='Delivery Order' or av_item_trans_app_report.trans_type='Job Costing' "
                . " or av_item_trans_app_report.trans_type='Purchase Return'  or av_item_trans_app_report.trans_type='Sales Return' )", null,false);
        
        //==========================================================================================================//
     
        /* ===================================find by ItemCategoryName code========================================================= */
        if ($Category != "" && $Category != "All" ) {
            $this->db->like('am_itemcategory.ItemCategoryName', $Category);
        } 
        /* ==================================end find by ItemCategoryName code===================================================== */
        
        /* ===================================find by item_desc code========================================================= */
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('av_item_trans_app_report.item_desc', $Item);
        } 
        /* ==================================end find by item_desc code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('av_item_trans_app_report.branch_code', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(av_item_trans_app_report.branch_code,1,LENGTH('".$CompCode."')+1)=concat('".$CompCode."','-')", null); 
        } else {
            $this->db->where("SUBSTRING(av_item_trans_app_report.branch_code,1,LENGTH('".$sess_company."')+1)=concat('".$sess_company."','-')", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('av_item_trans_app_report.trans_date >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('av_item_trans_app_report.trans_date <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ============================================== TRANSTATUS TABLE========================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('av_item_trans_app_report.trans_status', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('av_item_trans_app_report.trans_status', array(2, 1));
        }
        /* ==============================================END TRANSTATUS TABLE========================================================== */        
        
        $this->db->group_by("av_item_trans_app_report.item_code");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('av_item_trans_app_report.trans_date', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        
        $this->db->limit($Pages['limit'], $limitStart);
        $query = $this->db->get();
        //echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        return $result;
    }
    public function get_all_inventory_qty_cogs_list() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Category       = ($this->input->post('txt_itemCat')!=null)?$this->input->post('txt_itemCat'):'';
        $Item           = ($this->input->post('txt_item')!=null)?$this->input->post('txt_item'):'';
        $DateFrom       = ($this->input->post('txt_DateFrom')!=null)?$this->input->post('txt_DateFrom'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $TransStatus    = ($this->input->post('chk_TransStatus')!=null)?$this->input->post('chk_TransStatus'):'';
        $txtTable       = ($this->input->post('txtTable')!=null)?$this->input->post('txtTable'):'';
        $txtField       = ($this->input->post('txtField')!=null)?$this->input->post('txtField'):'';
        $typeShorting   = ($this->input->post('typeShorting')!=null)?$this->input->post('typeShorting'):'';
        
        $PreData        = ($this->input->post('predata')!=[null])?$this->input->post('predata'):'';
        /* =================================END post dari find di kiri==================================================== */
           
        /* ==========================================by session======================================================= */
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */
    
        $this->db->select("distinct(av_item_trans_app_report.no_transaksi)          AS no_transaksi_av
            , av_item_trans_app_report.item_code           AS item_code_av
            , av_item_trans_app_report.item_desc           AS item_desc_av
            , av_item_trans_app_report.item_qty            AS item_qty_av
            , av_item_trans_app_report.item_unit           AS item_unit_av
            , av_item_trans_app_report.item_category       AS item_category_av
            , av_item_trans_app_report.trans_status        AS trans_status_av
            , av_item_trans_app_report.trans_date          AS trans_date_av
            , av_item_trans_app_report.branch_code         AS branch_code_av
            , av_item_trans_app_report.company_code        AS company_code_av
            , av_item_trans_app_report.trans_type          AS trans_type_av
            , am_itemcategory.ItemCategoryName      AS ItemCategoryName_av
            , am_branch.BranchName                  AS BranchName_av
            , at_inventories.ItemValue
            , at_inventories.ItemAverage
            ");        
        $this->db->from('av_item_trans_app_report');
        $this->db->join("am_itemcategory", "am_itemcategory.ItemCategoryID = av_item_trans_app_report.item_category","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = av_item_trans_app_report.branch_code","inner");
        $this->db->join("at_inventories", "at_inventories.ItemCode = av_item_trans_app_report.item_code "
                . "and at_inventories.BranchCode = av_item_trans_app_report.branch_code","inner");
        
        $this->db->where("(av_item_trans_app_report.trans_type='Receive Item' or av_item_trans_app_report.trans_type='Adjustment' "
                . "or av_item_trans_app_report.trans_type='Initial Amount' or av_item_trans_app_report.trans_type='Roll Over' "
                . "or av_item_trans_app_report.trans_type='Delivery Order' or av_item_trans_app_report.trans_type='Job Costing' "
                . " or av_item_trans_app_report.trans_type='Purchase Return'  or av_item_trans_app_report.trans_type='Sales Return' )", null,false);
        
        $this->db->where_in('av_item_trans_app_report.item_code', $PreData);
        //==========================================================================================================//
     
        /* ===================================find by ItemCategoryName code========================================================= */
        if ($Category != "" && $Category != "All" ) {
            $this->db->like('am_itemcategory.ItemCategoryName', $Category);
        } 
        /* ==================================end find by ItemCategoryName code===================================================== */
        
        /* ===================================find by item_desc code========================================================= */
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('av_item_trans_app_report.item_desc', $Item);
        } 
        /* ==================================end find by item_desc code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('av_item_trans_app_report.branch_code', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(av_item_trans_app_report.branch_code,1,LENGTH('".$CompCode."')+1)=concat('".$CompCode."','-')", null); 
        } else {
            $this->db->where("SUBSTRING(av_item_trans_app_report.branch_code,1,LENGTH('".$sess_company."')+1)=concat('".$sess_company."','-')", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('av_item_trans_app_report.trans_date >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('av_item_trans_app_report.trans_date <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ============================================== TRANSTATUS TABLE========================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('av_item_trans_app_report.trans_status', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('av_item_trans_app_report.trans_status', array(2, 1));
        }
        /* ==============================================END TRANSTATUS TABLE========================================================== */        
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('av_item_trans_app_report.trans_date', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        
        $query = $this->db->get();
        //echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        return $result;
    }
    
    //======== function pendukung =======================//
    
    function get_items($companyCode, $branchCode){
        $this->db->select('at_inventories.ItemCode,am_item.ItemDesc');
        $this->db->from('at_inventories');
        $this->db->join('am_item','am_item.ItemCode = at_inventories.ItemCode');
        if($branchCode!="All"&&$branchCode!=""&&$branchCode!=null){
            $this->db->where('at_inventories.BranchCode', $branchCode );
        }
        if ($companyCode != "" && $companyCode != "All" ) {
            $this->db->where("SUBSTRING(at_inventories.BranchCode,1,LENGTH('".$companyCode."')+1)=concat('".$companyCode."','-')", null); 
        } else {
            $this->db->where("SUBSTRING(at_inventories.BranchCode,1,LENGTH('".$sess_company."')+1)=concat('".$sess_company."','-')", null); 
        }
                
        $query = $this->db->get();

        return $query->result();
    }
}

?>
