<?php

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

class M_index_purchase extends CI_Model {    
    public $tbl_pr              = "at_purchaserequisition";
    
    //get all purchase by departement
    public function pre_get_all_purchase_req_by_dept() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Departement    = ($this->input->post('txt_depart')!=null)?$this->input->post('txt_depart'):'';
//        $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(""
            ."at_purchaserequisition.DepartmentCode as code"
        );        
        $this->db->from('at_purchaserequisition');
        $this->db->join("at_preqdetail", "at_purchaserequisition.PReqNumber = at_preqdetail.PReqNumber","inner");
        $this->db->join('am_department', 'am_department.DepartmentCode = at_purchaserequisition.DepartmentCode',"inner");
        
        //==========================================================================================================//
        
        //jika DepartementName All ato terisi
        if ($Departement != "" && $Departement != "All" ) {
            $this->db->like('am_department.DepartmentDesc', $Departement);
        }

        //jika ItemName All ato terisi
//        if ($Item != "" && $Item != "All" ) {
//            $this->db->like('at_preqdetail.ItemDesc', $Item);
//        }

        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchaserequisition.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchaserequisition.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchaserequisition.BranchCode', $BranchCode);
//            $this->db->where('compaccBankPayment.BranchCode', $BranchCode);
//            $this->db->where('compaccBankReceipt.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchaserequisition.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaserequisition.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaserequisition.PReqDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaserequisition.PReqDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        $this->db->group_by("at_purchaserequisition.DepartmentCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_pr . '.PReqDate', '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();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        return $result;
    }
    public function get_all_purchase_req_by_dept() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Departement    = ($this->input->post('txt_depart')!=null)?$this->input->post('txt_depart'):'';
//        $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(""
                ."at_purchaserequisition.BranchCode     as BranchCode_PR
                , at_purchaserequisition.DepartmentCode as DepartmentCode_PR
                , at_purchaserequisition.PReqDate       as PReqDate_PR
                , at_purchaserequisition.PReqNumber     as PReqNumber_PR
                , at_purchaserequisition.TransStatus    as TransStatus_PR
                , am_department.DepartmentDesc          as DepartmentDesc_PR
                
                , at_preqdetail.PReqDetailID    as PReqDetailID_PRDet
                , at_preqdetail.ItemCode        as ItemCode_PRDet
                , at_preqdetail.ItemDesc        as ItemDesc_PRDet
                , at_preqdetail.ItemUnit        as ItemUnit_PRDet
                , at_preqdetail.ItemQty         as ItemQty_PRDet
                , at_preqdetail.ItemQtyPO       as ItemQtyPO_PRDet
                , at_preqdetail.ItemQtyRI       as ItemQtyRI_PRDet
                , at_preqdetail.ItemQtySpd      as ItemQtySpd_PRDet
                , at_preqdetail.ItemSpdStatus   as ItemSpdStatus_PRDet
                
                , at_purchaseorder.POrdDate     as POrdDate_PO
                , at_purchaseorder.POrdNumber   as POrdNumber_PO
                , at_purchaseorder.TransStatus  as TransStatus_PO
                
                , at_porddetail.POrdDetailID    as POrdDetailID_PODet
                , at_porddetail.ItemQty         as ItemQty_PODet
                , at_porddetail.ItemQtySpd      as ItemQtySpd_PODet
                
                , at_receiveitem.PRecDate       as PRecDate_RI
                , at_receiveitem.PRecNumber     as PRecNumber_RI
                , at_receiveitem.TransStatus    as TransStatus_RI
                
                , at_precdetail.PRecDetailID    as PRecDetailID_RIDet
                , at_precdetail.ItemQty         as ItemQty_RIDet"
                );
        
        $this->db->from('at_purchaserequisition');
        $this->db->join("at_preqdetail", "at_purchaserequisition.PReqNumber = at_preqdetail.PReqNumber","inner");
        $this->db->join('am_department', 'am_department.DepartmentCode = at_purchaserequisition.DepartmentCode',"inner");
        
        $this->db->join('at_purchaseorder', 'at_purchaserequisition.PReqNumber = at_purchaseorder.PReqNumber '
                . "and (at_purchaseorder.TransStatus='0' or at_purchaseorder.TransStatus='1' or at_purchaseorder.TransStatus='2'"
                . "  or at_purchaseorder.TransStatus='4' or at_purchaseorder.TransStatus='5' or at_purchaseorder.TransStatus='6'"
                . " or at_purchaseorder.TransStatus='7' ) ","left");//relasi dengan SO
        
        $this->db->join('at_porddetail', "at_porddetail.POrdNumber = at_purchaseorder.POrdNumber "
                . " AND at_porddetail.PReqNumber = at_purchaserequisition.PReqNumber "
                . "and at_porddetail.PReqDetailID = at_preqdetail.PReqDetailID ","left");//lerasi dengan SOdetail
                //. "and at_sorddetail.ItemSpdStatus='0' 
        
        $this->db->join('at_receiveitem', 'at_receiveitem.POrdNumber = at_purchaseorder.POrdNumber '
                . "and (at_receiveitem.TransStatus='1' or at_receiveitem.TransStatus='2') ","left");//relasi dengan DO
        
        $this->db->join("at_precdetail","at_precdetail.PRecNumber = at_receiveitem.PRecNumber "
                . " AND at_precdetail.POrdDetailID = at_porddetail.POrdDetailID","left");
        //==========================================================================================================//
         $this->db->where_in('at_purchaserequisition.DepartmentCode', $PreData);
         
        //jika DepartementName All ato terisi
        if ($Departement != "" && $Departement != "All" ) {
            $this->db->like('am_department.DepartmentDesc', $Departement);
        }

//        //jika ItemName All ato terisi
//        if ($Item != "" && $Item != "All" ) {
//            $this->db->like('at_preqdetail.ItemDesc', $Item);
//        }

        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchaserequisition.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchaserequisition.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchaserequisition.BranchCode', $BranchCode);
//            $this->db->where('compaccBankPayment.BranchCode', $BranchCode);
//            $this->db->where('compaccBankReceipt.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchaserequisition.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaserequisition.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaserequisition.PReqDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaserequisition.PReqDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_pr . '.PReqDate', '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;
    }
    //end get all purchase by departement
    
    //get all purchase by Item
    public function pre_get_all_purchase_req_by_item() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $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(""
            ."at_preqdetail.ItemCode as code"
        );        
        $this->db->from('at_purchaserequisition');
        $this->db->join("at_preqdetail", "at_purchaserequisition.PReqNumber = at_preqdetail.PReqNumber","inner");
        //==========================================================================================================//
        
        //jika ItemName All ato terisi
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('at_preqdetail.ItemDesc', $Item);
        }

        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchaserequisition.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchaserequisition.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchaserequisition.BranchCode', $BranchCode);
//            $this->db->where('compaccBankPayment.BranchCode', $BranchCode);
//            $this->db->where('compaccBankReceipt.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchaserequisition.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaserequisition.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaserequisition.PReqDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaserequisition.PReqDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        $this->db->group_by("at_preqdetail.ItemCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_pr . '.PReqDate', '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();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        return $result;
    }
    public function get_all_purchase_req_by_item() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $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(""
                ."at_purchaserequisition.BranchCode     as BranchCode_PR
                , at_purchaserequisition.DepartmentCode as DepartmentCode_PR
                , at_purchaserequisition.PReqDate       as PReqDate_PR
                , at_purchaserequisition.PReqNumber     as PReqNumber_PR
                , at_purchaserequisition.TransStatus    as TransStatus_PR
                , am_department.DepartmentDesc          as DepartmentDesc_PR
                
                , at_preqdetail.PReqDetailID    as PReqDetailID_PRDet
                , at_preqdetail.ItemCode        as ItemCode_PRDet
                , at_preqdetail.ItemDesc        as ItemDesc_PRDet
                , at_preqdetail.ItemUnit        as ItemUnit_PRDet
                , at_preqdetail.ItemQty         as ItemQty_PRDet
                , at_preqdetail.ItemQtyPO       as ItemQtyPO_PRDet
                , at_preqdetail.ItemQtyRI       as ItemQtyRI_PRDet
                , at_preqdetail.ItemQtySpd      as ItemQtySpd_PRDet
                , at_preqdetail.ItemSpdStatus   as ItemSpdStatus_PRDet
                
                , at_purchaseorder.POrdDate     as POrdDate_PO
                , at_purchaseorder.POrdNumber   as POrdNumber_PO
                , at_purchaseorder.TransStatus  as TransStatus_PO
                
                , at_porddetail.POrdDetailID    as POrdDetailID_PODet
                , at_porddetail.ItemQty         as ItemQty_PODet
                , at_porddetail.ItemQtySpd      as ItemQtySpd_PODet
                
                , at_receiveitem.PRecDate       as PRecDate_RI
                , at_receiveitem.PRecNumber     as PRecNumber_RI
                , at_receiveitem.TransStatus    as TransStatus_RI
                
                , at_precdetail.PRecDetailID    as PRecDetailID_RIDet
                , at_precdetail.ItemQty         as ItemQty_RIDet"
                );
        
        $this->db->from('at_purchaserequisition');
        $this->db->join("at_preqdetail", "at_purchaserequisition.PReqNumber = at_preqdetail.PReqNumber","inner");
        $this->db->join('am_department', 'am_department.DepartmentCode = at_purchaserequisition.DepartmentCode',"inner");
        
        $this->db->join('at_purchaseorder', 'at_purchaserequisition.PReqNumber = at_purchaseorder.PReqNumber '
                . "and (at_purchaseorder.TransStatus='0' or at_purchaseorder.TransStatus='1' or at_purchaseorder.TransStatus='2'"
                . "  or at_purchaseorder.TransStatus='4' or at_purchaseorder.TransStatus='5' or at_purchaseorder.TransStatus='6'"
                . " or at_purchaseorder.TransStatus='7' ) ","left");//relasi dengan SO
        
        $this->db->join('at_porddetail', "at_porddetail.POrdNumber = at_purchaseorder.POrdNumber "
                . " AND at_porddetail.PReqNumber = at_purchaserequisition.PReqNumber "
                . "and at_porddetail.PReqDetailID = at_preqdetail.PReqDetailID ","left");//lerasi dengan SOdetail
                //. "and at_sorddetail.ItemSpdStatus='0' 
        
        $this->db->join('at_receiveitem', 'at_receiveitem.POrdNumber = at_purchaseorder.POrdNumber '
                . "and (at_receiveitem.TransStatus='1' or at_receiveitem.TransStatus='2') ","left");//relasi dengan DO
        
        $this->db->join("at_precdetail","at_precdetail.PRecNumber = at_receiveitem.PRecNumber "
                . " AND at_precdetail.POrdDetailID = at_porddetail.POrdDetailID","left");
        //==========================================================================================================//
        $this->db->where_in('at_preqdetail.ItemCode', $PreData);
         
        //jika ItemName All ato terisi
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('at_preqdetail.ItemDesc', $Item);
        }

        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchaserequisition.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchaserequisition.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchaserequisition.BranchCode', $BranchCode);
//            $this->db->where('compaccBankPayment.BranchCode', $BranchCode);
//            $this->db->where('compaccBankReceipt.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchaserequisition.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaserequisition.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaserequisition.PReqDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaserequisition.PReqDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_pr . '.PReqDate', '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;
    }
    //end get all purchase by Item
    
    //get all purchase invoice by vendor 5B790570
    public function pre_get_all_purchase_invoices() {       
        /* =================================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'):'';
        $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->_protect_identifiers = FALSE;
        $this->db->select("  
          am_vendor.VendorCode as code
        "); 
        
        $this->db->from('av_all_transaction_for_report AS avtrans_1');
        $this->db->join('at_purchasereturn','at_purchasereturn.PRetNumber = avtrans_1.Journal_Number',"LEFT");
        $this->db->join('at_purchaseinvoice','at_purchaseinvoice.PInvNumber = avtrans_1.Journal_Number || at_purchaseinvoice.PInvNumber = at_purchasereturn.PInvNumber',"LEFT");
        $this->db->join('am_vendor','am_vendor.VendorCode = at_purchaseinvoice.VendorCode OR am_vendor.VendorCode = at_purchasereturn.VendorCode ',"LEFT");
        $this->db->join("am_currency", "am_currency.CurrencyCode = am_vendor.CurrencyCode "
                . " and am_currency.CompanyCode = avtrans_1.CompanyCode","LEFT");        
        $this->db->where_in('avtrans_1.trans_type', array('PRet', 'pinv'));
        
        if ($Vendor != '') {
            $this->db->like('am_vendor.VendorName', $Vendor);
        }
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('avtrans_1.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('avtrans_1.TransStatus', array(1,2));
        }
        //==== Customer jika tidak kosong
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('avtrans_1.Branch_Code', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(avtrans_1.Branch_Code,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(avtrans_1.Branch_Code,1,LENGTH('".$sess_company."'))='".$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('avtrans_1.Journal_Date >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('avtrans_1.Journal_Date <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("am_vendor.VendorCode");    
//        $this->db->group_by('avtrans_1.Journal_Number');
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('avtrans_1.Journal_Date', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */    
        $this->db->limit($Pages['limit'], $limitStart);
        
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;	
//        echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        return $result;
    }
    public function get_all_purchase_invoices() {  
        $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'):'';
        $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->_protect_identifiers = FALSE;
        $this->db->select("  
          IF(
            at_purchasereturn.PInvNumber IS NULL,
            #ini invoice
            (
              IF(
                avtrans_1.CurrencyCode = 'IDR',
                #'PInvNumber ada dan IDR'
                (SELECT 
                  SUM(avtrans_2.Amount_Currency) 
                FROM
                  av_all_transaction_for_report AS avtrans_2 
                WHERE avtrans_2.Journal_Number = avtrans_1.Journal_Number 
                  AND avtrans_2.Sign_Status = '0' 
                  AND (
                     (
                      avtrans_2.Acc_Type_Name = 'Account Payable' 
                      AND avtrans_2.Sub_Type_Name <> 'Hutang Pajak' 
                      AND avtrans_2.Sub_Type_Name <> 'Hutang PPn Fiscal'
                    )
                  )),
                #'PInvNumber ada dan USD'
                (SELECT 
                  SUM(avtrans_3.Amount_Currency) 
                FROM
                  av_all_transaction_for_report AS avtrans_3 
                WHERE avtrans_3.Journal_Number = avtrans_1.Journal_Number 
                  AND avtrans_3.Sign_Status = '0' 
                  AND (
                     (
                      avtrans_3.Acc_Type_Name = 'Account Payable' 
                      AND avtrans_3.Sub_Type_Name <> 'Hutang Pajak' 
                      AND avtrans_3.Sub_Type_Name <> 'Hutang PPn Fiscal'
                    )
                  ))
              )
            ),
            #ini Return
            (
              IF(
                avtrans_1.CurrencyCode = 'IDR',
                #'return dan IDR'
                (SELECT 
                  SUM(avtrans_2.Amount_Currency) 
                FROM
                  av_all_transaction_for_report AS avtrans_2 
                WHERE avtrans_2.Journal_Number = avtrans_1.Journal_Number 
                  AND avtrans_2.Sign_Status = '0' 
                  AND (
                    avtrans_2.Acc_Type_Name = 'Other Current Asset' 

                  )),
                #'return dan USD'
                (SELECT 
                  SUM(avtrans_3.Amount_Currency) 
                FROM
                  av_all_transaction_for_report AS avtrans_3 
                WHERE avtrans_3.Journal_Number = avtrans_1.Journal_Number 
                  AND avtrans_3.Sign_Status = '0' 
                  AND (
                    avtrans_3.Acc_Type_Name = 'Other Current Asset' 

                  ))
              )
            )
          ) AS totalAmount,  
          IF(
            at_purchasereturn.PInvNumber IS NULL,
            #ini invoice
            IF(
              avtrans_1.CurrencyCode = 'IDR',
              '',
              (SELECT 
                SUM(
                  avtrans_3.Saldo_Credit
                ) 
              FROM
                av_all_transaction_for_report AS avtrans_3 
              WHERE avtrans_3.Journal_Number = avtrans_1.Journal_Number 
                AND avtrans_3.Sign_Status = '0' 
                AND (
                  avtrans_3.Acc_Type_Name = 'Account Payable' 
                  AND avtrans_3.Sub_Type_Name = 'Hutang PPn Fiscal'
                ))
            ),
            #ini Return
            ''
          ) AS totalFiscal,
          at_purchasereturn.PInvNumber,
          avtrans_1.Journal_Date,
          avtrans_1.Journal_Number,
          avtrans_1.Sub_Type_Name,
          avtrans_1.Acc_Type_Name,
          avtrans_1.Amount_Currency,
          avtrans_1.CurrencyCode ,
          avtrans_1.TransStatus,
          am_vendor.VendorCode,
          am_vendor.VendorName,
          am_currency.CurrencySymbol
            "
        ); 
        
        $this->db->from('av_all_transaction_for_report AS avtrans_1');
        $this->db->join('at_purchasereturn','at_purchasereturn.PRetNumber = avtrans_1.Journal_Number',"LEFT");
        $this->db->join('at_purchaseinvoice','at_purchaseinvoice.PInvNumber = avtrans_1.Journal_Number || at_purchaseinvoice.PInvNumber = at_purchasereturn.PInvNumber',"LEFT");
        $this->db->join('am_vendor','am_vendor.VendorCode = at_purchaseinvoice.VendorCode',"LEFT");
        $this->db->join("am_currency", "am_currency.CurrencyCode = am_vendor.CurrencyCode "
                . " and am_currency.CompanyCode = avtrans_1.CompanyCode","LEFT");
        
        $this->db->where_in('am_vendor.VendorCode', $PreData);
        $this->db->where_in('avtrans_1.trans_type', array('PRet', 'pinv'));
        //==== Vendor jika tidak kosong
        if ($Vendor != '') {
            $this->db->like('am_vendor.VendorName', $Vendor);
        }
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('avtrans_1.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('avtrans_1.TransStatus', array(1,2));
        }
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('avtrans_1.Branch_Code', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(avtrans_1.Branch_Code,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(avtrans_1.Branch_Code,1,LENGTH('".$sess_company."'))='".$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('avtrans_1.Journal_Date >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('avtrans_1.Journal_Date <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('avtrans_1.Journal_Date', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        $this->db->group_by('avtrans_1.Journal_Number');
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;	
//        echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        //print_r($result);
        return $result;
    }
    public function get_all_purchase_invoices_LAMA() {       
        /* =================================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'):'';
        $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_purchaseinvoice.PInvNumber as PInvNumber_PI"
            . ", at_purchaseinvoice.BranchCode       as BranchCode_PI"
            . ", at_purchaseinvoice.VendorCode       as VendorCode_PI"
            . ", at_purchaseinvoice.PInvDate         as PInvDate_PI"
            . ", at_purchaseinvoice.PInvDesc         as PInvDesc_PI"
            . ", at_purchaseinvoice.PInvTotalAmount  as PInvTotalAmount_PI"
            . ", at_purchaseinvoice.TransStatus      as TransStatus_PI"
            . ", am_vendor.VendorName               as VendorName_PI"
            . ", at_purchasereturn.PRetNumber               as PRetNumber_PI"
            //. ", at_purchasereturn.CurrencyCode       as CurrencyCode_PI"
            //. ", at_purchasereturn.CurrencyRate       as CurrencyRate_PI"
            . ", (select sum(at_pinvdetail.ReturnQty * at_pinvdetail.ItemPrice) from  at_pinvdetail where at_pinvdetail.PInvNumber=at_purchaseinvoice.PInvNumber group by at_pinvdetail.PInvNumber)        as Return_PI"
            
            . ", at_ppaydetail.PPayDetailID     as PPayDetailID_PPDet"
            . ", at_ppaydetail.PPayNumber       as PPayNumber_PPDet"
            //. ", at_ppaydetail.PInvNumber       as PInvNumber_PPDet"
            //. ", at_ppaydetail.PInvDate         as PInvDate_PPDet"
            //. ", at_ppaydetail.PInvAmount       as PInvAmount_PPDet"
            . ", at_ppaydetail.PPayDiscount     as PPayDiscount_PPDet"
            . ", at_ppaydetail.PPayDPUsed       as PPayDPUsed_PPDet"
            . ", (at_ppaydetail.PPayAmount+ at_ppaydetail.PPayDPUsed + at_ppaydetail.PPayRounded)     as PPayAmount_PPDet"
            . ", at_ppaydetail.PPayRounded      as PPayRounded_PPDet"
                
            . ", at_purchasepayment.PPayDate           as PPayDate_PP"
            . ", at_purchasepayment.PPayNumber         as PPayNumber_PP"
            . ", at_purchasepayment.PInvTotalAmount    as PInvTotalAmount_PP"  
            . ", at_purchasepayment.BankCurrRate       as CurrencyRate_PP"    
            . ", at_purchasepayment.TransStatus        as TransStatus_PP"); 
        
        $this->db->from('at_purchaseinvoice');
        $this->db->join('am_vendor', 'am_vendor.VendorCode = at_purchaseinvoice.VendorCode',"inner");
        
        $this->db->join("at_purchasereturn", "at_purchasereturn.PInvNumber = at_purchaseinvoice.PInvNumber ","left");
        
        $this->db->join("at_ppaydetail", "at_ppaydetail.PInvNumber = at_purchaseinvoice.PInvNumber "
                . "and (at_purchaseinvoice.TransStatus='1' or at_purchaseinvoice.TransStatus='2') ","left");
        
        $this->db->join("at_purchasepayment", "at_purchasepayment.PPayNumber = at_ppaydetail.PPayNumber "
                . " and at_purchasepayment.BranchCode = at_purchaseinvoice.BranchCode "
                . " and at_purchasepayment.TransStatus = '2' ","left");
        
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        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));
        }
        //==== Customer jika tidak kosong
        if ($Vendor != '') {
            $this->db->like('am_vendor.VendorName', $Vendor);
        }
        /* ===================================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."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaseinvoice.PInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaseinvoice.PInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchaseinvoice.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);
        //print_r($result);
        return $result;
    }
    //end get all purchase invoice by vendor 5B790570
    
    //get all purchase amount item
    public function pre_get_all_purchase_amount_item() {    
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $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'):'';
        $ItemType       = ($this->input->post('chk_itemType')!=null)?$this->input->post('chk_itemType'):'';
        $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(""
            ." at_pinvdetail.ItemCode as code"
        ); 
        
        $this->db->from('at_purchaseinvoice');       
        $this->db->join('at_pinvdetail', 'at_pinvdetail.PInvNumber = at_purchaseinvoice.PInvNumber '
                . " and (at_purchaseinvoice.TransStatus='1' OR at_purchaseinvoice.TransStatus='2' )","inner");
        $this->db->join('am_item', 'am_item.ItemCode = at_pinvdetail.ItemCode',"inner");
        /* ========================jika item tidak kosong ================= */
        if ($Item != '') {
            $this->db->like('at_pinvdetail.ItemDesc', $Item);
        } 
        /* ========================jika item tidak kosong ================= */
        
        /* ========================Transtatus================= */
        if ($TransStatus != '') {
            $this->db->where_in('am_item.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('am_item.TransStatus', array(1));
        }
        /* ========================end Transtatus================= */
        
        /* ========================Item Type================= */
        if ($ItemType != '') {
            $this->db->where_in('at_pinvdetail.ItemType', $ItemType);
        } else {
            $this->db->where_in('at_pinvdetail.ItemType', array(0,1,2));
        }
        /* ========================end Item Type================= */

        /* ===================================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."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaseinvoice.PInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaseinvoice.PInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        $this->db->group_by("at_pinvdetail.ItemCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchaseinvoice.PInvDate', '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();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        //print_r($result);
        return $result;
    }
    public function get_all_purchase_amount_item() {    
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $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'):'';
        $ItemType       = ($this->input->post('chk_itemType')!=null)?$this->input->post('chk_itemType'):'';
        $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(""
            ."   at_purchaseinvoice.TransStatus"
            . ", at_purchaseinvoice.PInvDate"
            . ", at_purchaseinvoice.CurrencyCode" //
            . ", at_purchaseinvoice.CurrencyRate" //
            . ", am_currency.CurrencySymbol" //
                
            ." , at_pinvdetail.PInvDetailID"
            ." , at_pinvdetail.PInvNumber"
            ." , at_pinvdetail.ItemCode"
            ." , at_pinvdetail.ItemDesc"
            ." , at_pinvdetail.ItemQty"
            ." , at_pinvdetail.ItemUnit"
            ." , at_pinvdetail.ItemAmount"
            ." , at_pinvdetail.ReturnQty"      //
            //." , at_pinvdetail.ItemPrice"
			." , (at_pinvdetail.ItemAmount / at_pinvdetail.ItemQty)as ItemPrice"
            . ", at_pinvdetail.ItemType"      //
            . ", (select sum((at_pinvdetail.ReturnQty * at_pinvdetail.ItemPrice) ) from  at_pinvdetail where at_pinvdetail.PInvNumber=at_purchaseinvoice.PInvNumber group by at_pinvdetail.PInvNumber)    as totalReturn"
            ." , at_pinvdetail.ItemCategoryCode"
            ." , at_pinvdetail.ItemCategoryID"
            . ", am_item.TransStatus as itemTransStatus"); 
        
        $this->db->from('at_purchaseinvoice');       
        $this->db->join('at_pinvdetail', 'at_pinvdetail.PInvNumber = at_purchaseinvoice.PInvNumber '
                . " and (at_purchaseinvoice.TransStatus='1' OR at_purchaseinvoice.TransStatus='2' )","inner");
        $this->db->join('am_item', 'am_item.ItemCode = at_pinvdetail.ItemCode',"inner");
        $this->db->join('am_branch', 'am_branch.BranchCode = at_purchaseinvoice.BranchCode',"inner");
        $this->db->join('am_currency', 'am_currency.CurrencyCode = at_purchaseinvoice.CurrencyCode '
                . ' and am_currency.CompanyCode = am_branch.CompanyCode',"inner");
        $this->db->where_in('at_pinvdetail.ItemCode', $PreData);

        /* ========================jika item tidak kosong ================= */
        if ($Item != '') {
            $this->db->like('at_pinvdetail.ItemDesc', $Item);
        } 
        /* ========================jika item tidak kosong ================= */
        
        /* ========================Transtatus================= */
        if ($TransStatus != '') {
            $this->db->where_in('am_item.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('am_item.TransStatus', array(1));
        }
        /* ========================end Transtatus================= */
        
        /* ========================Item Type================= */
        if ($ItemType != '') {
            $this->db->where_in('at_pinvdetail.ItemType', $ItemType);
        } else {
            $this->db->where_in('at_pinvdetail.ItemType', array(0,1,2));
        }
        /* ========================end Item Type================= */

        /* ===================================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."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaseinvoice.PInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaseinvoice.PInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchaseinvoice.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);
        //print_r($result);
        return $result;
    }
    //end get all purchase amount item
    
    //get all purchase item category
    public function pre_get_all_purchase_amount_item_category() {        
        $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'):'';
        $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'):'';
        $ItemType       = ($this->input->post('chk_itemType')!=null)?$this->input->post('chk_itemType'):'';
        $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(""
            ." at_pinvdetail.ItemCategoryID as code"
        );         
        $this->db->from('at_purchaseinvoice');       
        $this->db->join('at_pinvdetail', 'at_pinvdetail.PInvNumber = at_purchaseinvoice.PInvNumber '
                . " and (at_purchaseinvoice.TransStatus='1' OR at_purchaseinvoice.TransStatus='2' )","inner");
        $this->db->join('am_itemcategory', "am_itemcategory.ItemCategoryID = at_pinvdetail.ItemCategoryID ","inner");
        
        /* ===================================find by itemCategory code========================================================= */
        if ($Category != "" && $Category != "All" ) {
            $this->db->like('am_itemcategory.ItemCategoryName', $Category);
        } 
        /* ==================================end find by item category code===================================================== */
        
        /* ========================transtatus================= */
        if ($TransStatus != '') {
            $this->db->where_in('am_itemcategory.TransStatus', $TransStatus);
        } else {
            $this->db->where_in('am_itemcategory.TransStatus', array(1));
        }
        /* ========================end transtatus================= */

        /* ========================Item Type================= */
        if ($ItemType != '') {
            $this->db->where_in('at_pinvdetail.ItemType', $ItemType);
        } else {
            $this->db->where_in('at_pinvdetail.ItemType', array(0,1,2));
        }
        /* ========================end Item Type================= */
        /* ===================================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."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaseinvoice.PInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaseinvoice.PInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_pinvdetail.ItemCategoryID");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchaseinvoice.PInvDate', '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();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        //print_r($result);
        return $result;
    }
    public function get_all_purchase_amount_item_category() {      
        $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'):'';
        $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'):'';
        $ItemType       = ($this->input->post('chk_itemType')!=null)?$this->input->post('chk_itemType'):'';
        $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(""
            ."   at_purchaseinvoice.TransStatus"
            . ", at_purchaseinvoice.PInvDate"
            . ", at_purchaseinvoice.CurrencyCode" //
            . ", at_purchaseinvoice.CurrencyRate" //
            . ", am_currency.CurrencySymbol" //
                
            ." , at_pinvdetail.PInvDetailID"
            ." , at_pinvdetail.PInvNumber"
            ." , at_pinvdetail.ItemCode"
            ." , at_pinvdetail.ItemDesc"
            ." , at_pinvdetail.ItemQty"
            ." , at_pinvdetail.ItemUnit"
            ." , at_pinvdetail.ItemAmount"
            ." , at_pinvdetail.ReturnQty"      //
            //." , at_pinvdetail.ItemPrice"  //
            ." , (at_pinvdetail.ItemAmount / at_pinvdetail.ItemQty)as ItemPrice"
			//. ", (select sum((a.QtyBookReturn * a.ItemPrice)+(a.QtyBookReturn * a.ItemPrice) ) from  at_sinvdetail a where a.SInvNumber=at_salesinvoice.SInvNumber and a.ItemCode=at_sinvdetail.ItemCode group by at_sinvdetail.SInvNumber)           as totalReturn"
            ." , at_pinvdetail.ItemCategoryCode"
            ." , at_pinvdetail.ItemCategoryID"
            . ", am_itemcategory.ItemCategoryName"
            . ", am_itemcategory.TransStatus as itemTransStatus"); 
        
        $this->db->from('at_purchaseinvoice');       
        $this->db->join('at_pinvdetail', 'at_pinvdetail.PInvNumber = at_purchaseinvoice.PInvNumber '
                . " and (at_purchaseinvoice.TransStatus='1' OR at_purchaseinvoice.TransStatus='2' )","inner");
        $this->db->join('am_itemcategory', "am_itemcategory.ItemCategoryID = at_pinvdetail.ItemCategoryID ","inner");
        $this->db->join('am_branch', 'am_branch.BranchCode = at_purchaseinvoice.BranchCode',"inner");
        $this->db->join('am_currency', 'am_currency.CurrencyCode = at_purchaseinvoice.CurrencyCode '
                . ' and am_currency.CompanyCode = am_branch.CompanyCode',"inner");
        
        $this->db->where_in('at_pinvdetail.ItemCategoryID', $PreData);
        /* ===================================find by itemCategory code========================================================= */
        if ($Category != "" && $Category != "All" ) {
            $this->db->like('am_itemcategory.ItemCategoryName', $Category);
        } 
        /* ==================================end find by item category code===================================================== */
        /* ========================transtatus================= */
        if ($TransStatus != '') {
            $this->db->where_in('am_itemcategory.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('am_itemcategory.TransStatus', array(1));
        }
        /* ========================end transtatus================= */

        /* ========================Item Type================= */
        if ($ItemType != '') {
            $this->db->where_in('at_pinvdetail.ItemType', $ItemType);
        } else {
            $this->db->where_in('at_pinvdetail.ItemType', array(0,1,2));
        }
        /* ========================end Item Type================= */
        
        /* ===================================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."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchaseinvoice.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchaseinvoice.PInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchaseinvoice.PInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchaseinvoice.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);
        //print_r($result);
        return $result;
    }
    //end get all purchase item category
        
    //get all purchase return by vendor
    public function pre_get_all_purchase_return_by_vendor() {        
        $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'):'';
        
        $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("
            at_purchasereturn.VendorCode         as code
        "); 
        
        $this->db->from("at_purchasereturn");
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_purchasereturn.VendorCode","inner");        
        $this->db->join("at_pretdetail", "at_pretdetail.PRetNumber = at_purchasereturn.PRetNumber ","inner");
        
        //==== Customer jika tidak kosong
        if ($Vendor != '') {
            $this->db->like('am_vendor.VendorName', $Vendor);
        }//==== End Customer jika tidak kosong
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchasereturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchasereturn.TransStatus', array(2));
        }
        //==== Item jika tidak kosong
//        if ($Item != '') {
//            $this->db->like('at_pretdetail.ItemDesc', $Item);
//        }//==== End Item jika tidak kosong
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchasereturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchasereturn.PRetDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchasereturn.PRetDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_purchasereturn.VendorCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchasereturn.PRetDate', '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();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        //print_r($result);
        return $result;
    }
    public function get_all_purchase_return_by_vendor() { 
        $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'):'';
        
        $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("
                    at_purchasereturn.BranchCode           as BranchCode_PRet
                    , at_purchasereturn.VendorCode         as VendorCode_PRet
                    , at_purchasereturn.PRetDate           as PRetDate_PRet
                    , at_purchasereturn.PRetNumber         as PRetNumber_PRet
                    , at_purchasereturn.PInvNumber         as PInvNumber_PRet
                    , at_purchasereturn.PRetDesc           as PRetDesc_PRet
                    , at_purchasereturn.CurrencyCode       as CurrencyCode_PRet
                    , at_purchasereturn.CurrencyRate       as CurrencyRate_PRet
                    , at_purchasereturn.FiscalRate         as FiscalRate_PRet
                    , at_purchasereturn.PRetSubTotal       as PRetSubTotal_PRet
                    , at_purchasereturn.PRetPPn10          as PRetPPn10_PRet
                    , at_purchasereturn.PRetPPh22          as PRetPPh22_PRet
                    , at_purchasereturn.PRetDiscount       as PRetDiscount_PRet
                    , at_purchasereturn.PRetTotalAmount    as PRetTotalAmount_PRet
                    , at_purchasereturn.TransStatus        as TransStatus_PRet
                    , am_vendor.VendorName                 as VendorName_PRet
                    , am_currency.CurrencySymbol
                    
                    , at_pretdetail.PRetDetailID    as PRetDetailID_PRetDet
                    , at_pretdetail.PRetNumber      as PRetNumber_PRetDet
                    , at_pretdetail.PInvNumber      as PInvNumber_PRetDet
                    , at_pretdetail.PInvDetailID    as PInvDetailID_PRetDet
                    , at_pretdetail.ItemCode        as ItemCode_PRetDet
                    , at_pretdetail.ItemDesc        as ItemDesc_PRetDet
                    , at_pretdetail.ItemQty         as ItemQty_PRetDet"
                    //.", at_sretdetail.ItemQtyRet      as ItemQtyRet_PRetDet"
                    .", at_pretdetail.InvoiceQty      as InvoiceQty_PRetDet
                    , at_pretdetail.ItemUnit        as ItemUnit_PRetDet
                    , at_pretdetail.ItemPrice       as ItemPrice_PRetDet
                    , at_pretdetail.ItemAmount      as ItemAmount_PRetDet
                    , (at_pretdetail.ItemTaxPPn10 + at_pretdetail.ItemTaxPPh22) as ItemAmountTax_PRetDet
                    , at_pretdetail.ItemDiscPcnt    as ItemDiscPcnt_PRetDet
                    , at_pretdetail.ItemTotalAmount as ItemTotalAmount_PRetDet
                "); 
        
        $this->db->from("at_purchasereturn");
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_purchasereturn.VendorCode","inner");        
        $this->db->join("at_pretdetail", "at_pretdetail.PRetNumber = at_purchasereturn.PRetNumber ","inner");
        $this->db->join('am_branch', 'am_branch.BranchCode = at_purchasereturn.BranchCode',"inner");
        $this->db->join('am_currency', 'am_currency.CurrencyCode = at_purchasereturn.CurrencyCode '
                . ' and am_currency.CompanyCode = am_branch.CompanyCode',"inner");
        $this->db->where_in('at_purchasereturn.VendorCode', $PreData);
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */ 
        //==== Vendor jika tidak kosong
        if ($Vendor != '') {
            $this->db->like('am_vendor.VendorName', $Vendor);
        }//==== End Vendor jika tidak kosong
        
        if ($TransStatus != '') {
            $this->db->where_in('at_purchasereturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchasereturn.TransStatus', array(2));
        }
       
        //==== Item jika tidak kosong
//        if ($Item != '') {
//            $this->db->like('at_pretdetail.ItemDesc', $Item);
//        }//==== End Item jika tidak kosong
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchasereturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchasereturn.PRetDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchasereturn.PRetDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchasereturn.PRetDate', '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);
        //print_r($result);
        return $result;
    }
    //end get all purchase return by vendor
    
    //get all purchase return by item
    public function pre_get_all_purchase_return_by_item() {        
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $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("
            at_pretdetail.ItemCode         as code
        "); 
        
        $this->db->from("at_purchasereturn");       
        $this->db->join("at_pretdetail", "at_pretdetail.PRetNumber = at_purchasereturn.PRetNumber ","inner");
        
        //==== Item jika tidak kosong
        if ($Item != '') {
            $this->db->like('at_pretdetail.ItemDesc', $Item);
        }//==== End Item jika tidak kosong
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchasereturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchasereturn.TransStatus', array(2));
        }
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchasereturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchasereturn.PRetDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchasereturn.PRetDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_pretdetail.ItemCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchasereturn.PRetDate', '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();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        //print_r($result);
        return $result;
    }
    public function get_all_purchase_return_by_item() { 
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $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("
                    at_purchasereturn.BranchCode           as BranchCode_PRet
                    , at_purchasereturn.VendorCode         as VendorCode_PRet
                    , at_purchasereturn.PRetDate           as PRetDate_PRet
                    , at_purchasereturn.PRetNumber         as PRetNumber_PRet
                    , at_purchasereturn.PInvNumber         as PInvNumber_PRet
                    , at_purchasereturn.PRetDesc           as PRetDesc_PRet
                    , at_purchasereturn.CurrencyCode       as CurrencyCode_PRet
                    , at_purchasereturn.CurrencyRate       as CurrencyRate_PRet
                    , am_currency.CurrencySymbol
                    , at_purchasereturn.FiscalRate         as FiscalRate_PRet
                    , at_purchasereturn.PRetSubTotal       as PRetSubTotal_PRet
                    , at_purchasereturn.PRetPPn10          as PRetPPn10_PRet
                    , at_purchasereturn.PRetPPh22          as PRetPPh22_PRet
                    , at_purchasereturn.PRetDiscount       as PRetDiscount_PRet
                    , at_purchasereturn.PRetTotalAmount    as PRetTotalAmount_PRet
                    , at_purchasereturn.TransStatus        as TransStatus_PRet
                    
                    , at_pretdetail.PRetDetailID    as PRetDetailID_PRetDet
                    , at_pretdetail.PRetNumber      as PRetNumber_PRetDet
                    , at_pretdetail.PInvNumber      as PInvNumber_PRetDet
                    , at_pretdetail.PInvDetailID    as PInvDetailID_PRetDet
                    , at_pretdetail.ItemCode        as ItemCode_PRetDet
                    , at_pretdetail.ItemDesc        as ItemDesc_PRetDet
                    , at_pretdetail.ItemQty         as ItemQty_PRetDet"
                    //.", at_sretdetail.ItemQtyRet      as ItemQtyRet_PRetDet"
                    .", at_pretdetail.InvoiceQty      as InvoiceQty_PRetDet
                    , at_pretdetail.ItemUnit        as ItemUnit_PRetDet"
//                    .", at_pretdetail.ItemPrice       as ItemPrice_PRetDet"
                    .",(at_pretdetail.ItemAmount / at_pretdetail.ItemQty) as ItemPrice_PRetDet     "
                    .", at_pretdetail.ItemAmount      as ItemAmount_PRetDet
                    , (at_pretdetail.ItemTaxPPn10 + at_pretdetail.ItemTaxPPh22) as ItemAmountTax_PRetDet
                    , at_pretdetail.ItemDiscPcnt    as ItemDiscPcnt_PRetDet
                    , at_pretdetail.ItemTotalAmount as ItemTotalAmount_PRetDet
                "); 
        
        $this->db->from("at_purchasereturn");        
        $this->db->join("at_pretdetail", "at_pretdetail.PRetNumber = at_purchasereturn.PRetNumber ","inner");      
        $this->db->join("am_branch", "am_branch.BranchCode = at_purchasereturn.BranchCode ","inner");       
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_purchasereturn.CurrencyCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->where_in('at_pretdetail.ItemCode', $PreData);
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */ 
        
        //==== Item jika tidak kosong
        if ($Item != '') {
            $this->db->like('at_pretdetail.ItemDesc', $Item);
        }//==== End Item jika tidak kosong
        
        if ($TransStatus != '') {
            $this->db->where_in('at_purchasereturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchasereturn.TransStatus', array(2));
        }
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchasereturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchasereturn.PRetDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchasereturn.PRetDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchasereturn.PRetDate', '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);
        //print_r($result);
        return $result;
    }
    //end get all purchase return by item
    
    //get all purchase return by category
    public function pre_get_all_purchase_return_by_item_category() { 
        $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_item_ctgry')!=null)?$this->input->post('txt_item_ctgry'):'';
        $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("
                    at_pretdetail.ItemCategoryID     as code
                "); 
        
        $this->db->from("at_purchasereturn");
        $this->db->join("at_pretdetail", "at_pretdetail.PRetNumber = at_purchasereturn.PRetNumber ","inner");
        $this->db->join("am_itemcategory", "am_itemcategory.ItemCategoryID = at_pretdetail.ItemCategoryID","inner");        
        
        //==== Item jika tidak kosong
        if ($Category != '') {
            $this->db->like('at_pretdetail.ItemDesc', $Category);
        }//==== End Item jika tidak kosong
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchasereturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchasereturn.TransStatus', array(2));
        }
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchasereturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchasereturn.PRetDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchasereturn.PRetDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_pretdetail.ItemCategoryID");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchasereturn.PRetDate', '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_purchase_return_by_item_category() {     
        $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_item_ctgry')!=null)?$this->input->post('txt_item_ctgry'):'';
        $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("
                    at_purchasereturn.BranchCode           as BranchCode_PRet
                    , at_purchasereturn.VendorCode         as VendorCode_PRet
                    , at_purchasereturn.PRetDate           as PRetDate_PRet
                    , at_purchasereturn.PRetNumber         as PRetNumber_PRet
                    , at_purchasereturn.PInvNumber         as PInvNumber_PRet
                    , at_purchasereturn.PRetDesc           as PRetDesc_PRet
                    , at_purchasereturn.CurrencyCode       as CurrencyCode_PRet
                    , at_purchasereturn.CurrencyRate       as CurrencyRate_PRet
                    , am_currency.CurrencySymbol
                    , at_purchasereturn.FiscalRate         as FiscalRate_PRet
                    , at_purchasereturn.PRetSubTotal       as PRetSubTotal_PRet
                    , at_purchasereturn.PRetPPn10          as PRetPPn10_PRet
                    , at_purchasereturn.PRetPPh22          as PRetPPh22_PRet
                    , at_purchasereturn.PRetDiscount       as PRetDiscount_PRet
                    , at_purchasereturn.PRetTotalAmount    as PRetTotalAmount_PRet
                    , at_purchasereturn.TransStatus        as TransStatus_PRet"
                    //, am_vendor.VendorName                 as VendorName_PRet
                    
                    .", at_pretdetail.PRetDetailID  as PRetDetailID_PRetDet
                    , at_pretdetail.PRetNumber      as PRetNumber_PRetDet
                    , at_pretdetail.PInvNumber      as PInvNumber_PRetDet
                    , at_pretdetail.PInvDetailID    as PInvDetailID_PRetDet
                    , at_pretdetail.ItemCode        as ItemCode_PRetDet
                    , at_pretdetail.ItemDesc        as ItemDesc_PRetDet
                    , at_pretdetail.ItemQty         as ItemQty_PRetDet"
                    //.", at_sretdetail.ItemQtyRet      as ItemQtyRet_PRetDet"
                    .", at_pretdetail.InvoiceQty    as InvoiceQty_PRetDet
                    , at_pretdetail.ItemUnit        as ItemUnit_PRetDet"
//                    .", at_pretdetail.ItemPrice       as ItemPrice_PRetDet"
                    .",(at_pretdetail.ItemAmount / at_pretdetail.ItemQty) as ItemPrice_PRetDet     "
                    .", at_pretdetail.ItemAmount      as ItemAmount_PRetDet
                    , at_pretdetail.ItemDiscPcnt    as ItemDiscPcnt_PRetDet
                    , at_pretdetail.ItemCategoryID     as ItemCategoryID_PRetDet
                    , am_itemcategory.ItemCategoryName as ItemCategoryName_PRetDet
                "); 
        
        $this->db->from("at_purchasereturn");
        $this->db->join("at_pretdetail", "at_pretdetail.PRetNumber = at_purchasereturn.PRetNumber ","inner");
        $this->db->join("am_itemcategory", "am_itemcategory.ItemCategoryID = at_pretdetail.ItemCategoryID","inner");      
        $this->db->join("am_branch", "am_branch.BranchCode = at_purchasereturn.BranchCode ","inner");       
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_purchasereturn.CurrencyCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");       
        
        $this->db->where_in('at_pretdetail.ItemCategoryID', $PreData);

        //==== category jika tidak kosong
        if ($Category != '') {
            $this->db->like('at_pretdetail.ItemDesc', $Category);
        }//==== End category jika tidak kosong
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_purchasereturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_purchasereturn.TransStatus', array(2));
        }
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_purchasereturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_purchasereturn.BranchCode,1,LENGTH('".$sess_company."'))='".$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_purchasereturn.PRetDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_purchasereturn.PRetDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchasereturn.PRetDate', '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();
        return $result;
    }
    //end get all purchase return by category
}
?>