<?php

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

class M_index_biaya_bayar_dimuka extends CI_Model {    
    public $tbl_              = "";
    
    
    public function get_all_biaya_bayar_dimuka_list() {
        
        /* =================================post dari find di kiri==================================================== */
        //$param  = $this->input->post('param');
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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'):'';
        /* =================================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(""
            ."
              at_pinvdetailrecur.PInvDetailID           AS PInvDetailID_PinvRec
            , at_pinvdetailrecur.PInvDate               AS PInvDate_PinvRec
            , at_pinvdetailrecur.PInvNumber             AS PInvNumber_PinvRec
            , at_pinvdetailrecur.ItemCode               AS ItemCode_PinvRec
            , at_pinvdetailrecur.ItemDesc               AS ItemDesc_PinvRec
            , at_pinvdetailrecur.ItemQty                AS ItemQty_PinvRec
            , at_pinvdetailrecur.ItemUnit               AS ItemUnit_PinvRec
            , at_pinvdetailrecur.ItemTotalAmount        AS ItemTotalAmount_PinvRec
            , at_pinvdetailrecur.ItemType               AS ItemType_PinvRec
            , at_pinvdetailrecur.ItemAdvNetDueIn        AS ItemAdvNetDueIn_PinvRec
            , at_pinvdetailrecur.PaymentPeriod          AS PaymentPeriod_PinvRec
            , at_pinvdetailrecur.PaymentDate            AS PaymentDate_PinvRec
            , at_pinvdetailrecur.PaymentAmount          AS PaymentAmount_PinvRec
            , at_pinvdetailrecur.JVoucherBalance        AS JVoucherBalance_PinvRec
            
            , at_purchaseinvoice.BranchCode        AS BranchCode_PI
            , at_purchaseinvoice.VendorCode        AS VendorCode_PI
            , at_purchaseinvoice.TransStatus        AS TransStatus_PI
            
            , am_branch.BranchName 
            , am_vendor.VendorName 
            "
            );
        $this->db->from('at_pinvdetailrecur');
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = at_pinvdetailrecur.PInvNumber","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = at_purchaseinvoice.BranchCode","inner");
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_purchaseinvoice.VendorCode","inner");
        
        
        //==========================================================================================================//
     
        /* ===================================find by $Vendor code========================================================= */
        if ($Vendor != "" && $Vendor != "All" ) {
            $this->db->like('am_vendor.VendorName', $Vendor);
        } 
        /* ==================================end find by $Vendor code===================================================== */
        
        /* ===================================find by item_desc code========================================================= */
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('at_pinvdetailrecur.ItemDesc', $Item);
        } 
        /* ==================================end find by item_desc code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchaseinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,1,LENGTH('".$CompCode."')+1)=concat('".$CompCode."','-')", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,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('at_pinvdetailrecur.PInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_pinvdetailrecur.PInvDate <=', 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('at_purchaseinvoice.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchaseinvoice.TransStatus', array(2, 1));
        }
        /* ==============================================END TRANSTATUS TABLE========================================================== */        
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_pinvdetailrecur.PInvDate', '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;
    }
        
    //get all depreciation
    public function get_all_depreciation_biaya_bayar_dimuka() {
        
        /* =================================post dari find di kiri==================================================== */
        //$param  = $this->input->post('param');
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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'):'';
        /* =================================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(""
                ."                
                at_pinvdetailrecur.PInvDetailID           AS PInvDetailID_PinvRec
                , at_pinvdetailrecur.PInvDate               AS PInvDate_PinvRec
                , at_pinvdetailrecur.PInvNumber             AS PInvNumber_PinvRec
                , at_pinvdetailrecur.ItemCode               AS ItemCode_PinvRec
                , at_pinvdetailrecur.ItemDesc               AS ItemDesc_PinvRec
                , at_pinvdetailrecur.ItemQty                AS ItemQty_PinvRec
                , at_pinvdetailrecur.ItemUnit               AS ItemUnit_PinvRec
                , at_pinvdetailrecur.ItemTotalAmount        AS ItemTotalAmount_PinvRec
                , at_pinvdetailrecur.ItemType               AS ItemType_PinvRec
                , at_pinvdetailrecur.ItemAdvNetDueIn        AS ItemAdvNetDueIn_PinvRec
                , at_pinvdetailrecur.PaymentPeriod          AS PaymentPeriod_PinvRec
                , at_pinvdetailrecur.PaymentDate            AS PaymentDate_PinvRec
                , at_pinvdetailrecur.PaymentAmount          AS PaymentAmount_PinvRec
                , at_pinvdetailrecur.JVoucherBalance        AS JVoucherBalance_PinvRec
                
                , at_purchaseinvoice.BranchCode        AS BranchCode_PI
                , at_purchaseinvoice.VendorCode        AS VendorCode_PI
                
                , am_branch.BranchName 
                , am_vendor.VendorName                

                , at_journalvoucher.BranchCode          AS BranchCode_JV
                , at_journalvoucher.JVchDate            AS JVchDate_JV
                , at_journalvoucher.TransStatus         AS TransStatus_JV
                
                , at_jvchdetail.JVchDebetSaldo          AS JVchDebetSaldo_JV
                , at_jvchdetail.AssetCode               AS AssetCode_JV
                , at_jvchdetail.PeriodEnd               AS PeriodEnd_JV
                "
                );
        
        $this->db->from('at_pinvdetailrecur');
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = at_pinvdetailrecur.PInvNumber","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = at_purchaseinvoice.BranchCode","inner");
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_purchaseinvoice.VendorCode","inner");
        
        $this->db->join("at_jvchdetail", "at_jvchdetail.AssetCode = concat(at_pinvdetailrecur.PInvNumber,',',at_pinvdetailrecur.ItemCode) ","inner");
        $this->db->join("at_journalvoucher", "at_journalvoucher.JVchNumber = at_jvchdetail.JVchNumber","inner");
        //==========================================================================================================//
     
        /* ===================================find by $Vendor code========================================================= */
        if ($Vendor != "" && $Vendor != "All" ) {
            $this->db->like('am_vendor.VendorName', $Vendor);
        } 
        /* ==================================end find by $Vendor code===================================================== */
        
        /* ===================================find by item_desc code========================================================= */
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('at_pinvdetailrecur.ItemDesc', $Item);
        } 
        /* ==================================end find by item_desc code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchaseinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,1,LENGTH('".$CompCode."')+1)=CONCAT('".$CompCode."','-')", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,1,LENGTH('".$sess_company."')+1)=CONCAT('".$sess_company."','-')", null); 
        }
        //========end where sesuai Company========================//
        
         /* ========================Kondisi Transatus================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_journalvoucher.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_journalvoucher.TransStatus', array(2,1));
        }
         /* ========================END Kondisi Transatus================= */

        //========group by =======================================//        
       // $this->db->group_by('at_inventdetail.IventDetailID');
        //========end group by ===================================/
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_journalvoucher.JVchDate', '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 get_all_testSP() {
        $vRst       = TRUE;
        $Test       = ($this->input->post('txt_test')!=null)?$this->input->post('txt_test'):'';
        $this->db->query("CALL sp_tester({$Test},@rstAngk)");
        
        
        
        
        
        $qry_res2   = $this->db->query("select @rstAngk  as results");
        $res        = $qry_res2->row();
        return $res;
    }
}

?>
