<?php

/* 	
 * 	Author 	: Dhidhik
 * 	Tanggal : 29-10-2015	
 * 	Revisi 	: 
 */

class M_index_sales extends CI_Model {    
    public $tbl_sq              = "at_salesquotation";
    public $dtl_sq_detail       = "at_squodetail";
    public $tbl_sinv            = "at_salesinvoice";
    public $dtl_sinv_detail     = "at_sinvdetail";
    public $tbl_sord            = "at_salesorder";
    public $tbl_sls_ret         = "at_salesreturn";
    public $tbl_do              = "at_deliveryorder";
    
    //get all sales quotation by customer
    public function pre_get_all_sq_by_customer() {        
        /* =================================post dari find di kiri==================================================== */
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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'];
//        $limitEnd       = ($Pages['limit'] * $Pages['pages'])-1;
        /* =================================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_salesquotation.CustomerCode as code"//as di buat nama nya jadi 'code' utk parameter pre array utk search
        );
        
        $this->db->from('at_salesquotation');
        $this->db->join("at_squodetail", "at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber "
                . "and at_squodetail.ItemSpdStatus='0'","inner");
        $this->db->join('am_customer', 'at_salesquotation.CustomerCode = am_customer.CustomerCode',"inner");
        
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        }
                
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesquotation.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesquotation.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesquotation.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_salesquotation.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesquotation.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_salesquotation.SQuoDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesquotation.SQuoDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_salesquotation.CustomerCode");
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by("at_salesquotation.CustomerCode", '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_sq_by_customer() {
        
        /* =================================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'):'';
        $CustomerCode   = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $ItemCode       = ($this->input->post('txt_item')!=null)?$this->input->post('txt_item'):'';
        $DateFrom       = ($this->input->post('txt_DateFrom')!=null)?$this->input->post('txt_DateFrom'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $TransStatus    = ($this->input->post('chk_TransStatus')!=null)?$this->input->post('chk_TransStatus'):'';
        $txtTable       = ($this->input->post('txtTable')!=null)?$this->input->post('txtTable'):'';
        $txtField       = ($this->input->post('txtField')!=null)?$this->input->post('txtField'):'';
        $typeShorting   = ($this->input->post('typeShorting')!=null)?$this->input->post('typeShorting'):'';
        
        $PreData        = ($this->input->post('predata')!=[null])?$this->input->post('predata'):'';
        /* =================================END post dari find di kiri==================================================== */
           
        /* ==========================================by session======================================================= */
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */
    
        $this->db->select("distinct(at_salesquotation.SQuoNumber)SQuoNumber_SQ"
                . ", at_salesquotation.BranchCode   as BranchCode_SQ"
                . ", at_salesquotation.CustomerCode as CustomerCode_SQ"
                . ", at_salesquotation.SQuoDate     as SQuoDate_SQ"
                . ", at_salesquotation.CustPODate   as CustPODate_SQ"
                . ", at_salesquotation.SQuoDesc     as SQuoDesc_SQ"
                . ", at_salesquotation.TransStatus  as TransStatus_SQ"
                . ", am_customer.CustomerName       as CustomerName_SQ"
                
                . ", at_squodetail.SQuoDetailID     as SQuoDetailID_SQuoDet"
                . ", at_squodetail.ItemCode         as ItemCode_SQuoDet"
                . ", at_squodetail.ItemDesc         as ItemDesc_SQuoDet"
                . ", at_squodetail.ItemUnit         as ItemUnit_SQuoDet"
                . ", at_squodetail.ItemQty          as ItemQty_SQuoDet"
                . ", at_squodetail.ItemQtySO        as ItemQtySO_SQuoDet"
                . ", at_squodetail.ItemQtyDO        as ItemQtyDO_SQuoDet"
                . ", at_squodetail.ItemQtySpd       as ItemQtySpd_SQuoDet"
                . ", at_squodetail.ItemSpdStatus    as ItemSpdStatus_SQuoDet"
                
                . ", at_salesorder.SOrdNumber       as SOrdNumber_SO"
                . ", at_salesorder.SOrdDate         as SOrdDate_SO"
                . ", at_salesorder.SQuoNumber       as SQuoNumber_SO"
                . ", at_salesorder.TransStatus      as TransStatus_SO"
                
                . ", at_sorddetail.SOrdDetailID     as SOrdDetailID_SODet"
                . ", at_sorddetail.SQuoDetailID     as SQuoDetailID_SODet"
                . ", at_sorddetail.SQuoNumber       as SQuoNumber_SODet"
                . ", at_sorddetail.SOrdNumber       as SOrdNumber_SODet"
                . ", at_sorddetail.ItemQty          as ItemQty_SODet"
                . ", at_sorddetail.ItemQtySpd       as ItemQtySpdTotal_SODet"
                . ", at_sorddetail.ItemQtySpd       as ItemQtySpd_SODet"
                . ", at_sorddetail.ItemSpdStatus    as ItemSpdStatus_SODet"
                
                . ", at_deliveryorder.DOrdNumber    as DOrdNumber_DO"
                . ", at_deliveryorder.DOrdDate      as DOrdDate_DO"
                . ", at_deliveryorder.SOrdNumber    as SOrdNumber_DO"
                . ", at_deliveryorder.TransStatus   as TransStatus_DO"
                
                . ", at_dorddetail.DOrdDetailID     as DOrdDetailID_DODet"
                . ", at_dorddetail.SOrdDetailID     as SOrdDetailID_DODet"
                . ", at_dorddetail.SOrdNumber       as SOrdNumber_DODet"
                . ", at_dorddetail.DOrdNumber       as DOrdNumber_DODet"
                . ", at_dorddetail.ItemQty          as ItemQty_DODet"
                );
        
        $this->db->from('at_salesquotation');
        $this->db->join("at_squodetail", "at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber "
                . "and at_squodetail.ItemSpdStatus='0'","inner");
        $this->db->join('am_customer', 'at_salesquotation.CustomerCode = am_customer.CustomerCode',"inner");
        
        $this->db->join('at_salesorder', 'at_salesquotation.SQuoNumber = at_salesorder.SQuoNumber '
                . "and (at_salesorder.TransStatus='0' or at_salesorder.TransStatus='1' or at_salesorder.TransStatus='2'"
                . "  or at_salesorder.TransStatus='4' or at_salesorder.TransStatus='5' or at_salesorder.TransStatus='6'"
                . " or at_salesorder.TransStatus='7' ) ","left");//relasi dengan SO
        
        $this->db->join('at_sorddetail', " at_salesorder.SOrdNumber = at_sorddetail.SOrdNumber "
                . " AND at_salesquotation.SQuoNumber = at_sorddetail.SQuoNumber "
                . "and at_squodetail.SQuoDetailID = at_sorddetail.SQuoDetailID ","left");//lerasi dengan SOdetail
                //. "and at_sorddetail.ItemSpdStatus='0' 
        
        $this->db->join('at_deliveryorder', 'at_sorddetail.SOrdNumber = at_deliveryorder.SOrdNumber '
                . "and (at_deliveryorder.TransStatus='1' or at_deliveryorder.TransStatus='2') ","left");//relasi dengan DO
        
        $this->db->join("at_dorddetail","at_deliveryorder.DOrdNumber   = at_dorddetail.DOrdNumber "
                . " AND at_sorddetail.SOrdDetailID = at_dorddetail.SOrdDetailID","left");//lerasi dengan DOdetail
//                "at_dorddetail","at_sorddetail.SOrdDetailID = at_dorddetail.SOrdNumber "
//                . "and at_sorddetail.SQuoDetailID = at_dorddetail.SOrdDetailID "
//                . 
        
        $this->db->where_in('at_salesquotation.CustomerCode', $PreData);
        
        //jika CustomerCode All ato terisi
        if ($CustomerCode != "" && $CustomerCode != "All" ) {
            $this->db->like('am_customer.CustomerName', $CustomerCode);
        }
        
        //jika itemCode All ato terisi
        if ($ItemCode != "" && $ItemCode != "All" ) {
            $this->db->like('at_squodetail.ItemDesc', $ItemCode);
        }
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesquotation.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesquotation.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesquotation.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_salesquotation.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesquotation.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_salesquotation.SQuoDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesquotation.SQuoDate <=', 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_sq . '.SQuoNumber', '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 sales quotation by customer
    
    //get all sales quotation by item
    public function pre_get_all_sq_by_item() {        
        /* =================================post dari find di kiri==================================================== */
        $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'];
//        $limitEnd       = ($Pages['limit'] * $Pages['pages'])-1;
        /* =================================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_squodetail.ItemCode as code"//as di buat nama nya jadi 'code' utk parameter pre array utk search
        );
        
        $this->db->from('at_salesquotation');
        $this->db->join("at_squodetail", "at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber "
                . "and at_squodetail.ItemSpdStatus='0'","inner");
        $this->db->join('am_customer', 'at_salesquotation.CustomerCode = am_customer.CustomerCode',"inner");
        
        if ($Item != "" && $Item != "All" ) {
            $this->db->like('at_squodetail.ItemDesc', $Item);
        }
                
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesquotation.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesquotation.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesquotation.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_salesquotation.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesquotation.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_salesquotation.SQuoDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesquotation.SQuoDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_squodetail.ItemCode");
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by("at_squodetail.ItemCode", '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_sq_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'):'';
//        $CustomerCode   = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $ItemCode       = ($this->input->post('txt_item')!=null)?$this->input->post('txt_item'):'';
        $DateFrom       = ($this->input->post('txt_DateFrom')!=null)?$this->input->post('txt_DateFrom'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $TransStatus    = ($this->input->post('chk_TransStatus')!=null)?$this->input->post('chk_TransStatus'):'';
        $txtTable       = ($this->input->post('txtTable')!=null)?$this->input->post('txtTable'):'';
        $txtField       = ($this->input->post('txtField')!=null)?$this->input->post('txtField'):'';
        $typeShorting   = ($this->input->post('typeShorting')!=null)?$this->input->post('typeShorting'):'';
        
        $PreData        = ($this->input->post('predata')!=[null])?$this->input->post('predata'):'';
        /* =================================END post dari find di kiri==================================================== */
           
        /* ==========================================by session======================================================= */
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */
    
        $this->db->select("distinct(at_salesquotation.SQuoNumber)SQuoNumber_SQ"
                . ", at_salesquotation.BranchCode   as BranchCode_SQ"
                . ", at_salesquotation.CustomerCode as CustomerCode_SQ"
                . ", at_salesquotation.SQuoDate     as SQuoDate_SQ"
                . ", at_salesquotation.CustPODate   as CustPODate_SQ"
                . ", at_salesquotation.SQuoDesc     as SQuoDesc_SQ"
                . ", at_salesquotation.TransStatus  as TransStatus_SQ"
                . ", am_customer.CustomerName       as CustomerName_SQ"
                
                . ", at_squodetail.SQuoDetailID     as SQuoDetailID_SQuoDet"
                . ", at_squodetail.ItemCode         as ItemCode_SQuoDet"
                . ", at_squodetail.ItemDesc         as ItemDesc_SQuoDet"
                . ", at_squodetail.ItemUnit         as ItemUnit_SQuoDet"
                . ", at_squodetail.ItemQty          as ItemQty_SQuoDet"
                . ", at_squodetail.ItemQtySO        as ItemQtySO_SQuoDet"
                . ", at_squodetail.ItemQtyDO        as ItemQtyDO_SQuoDet"
                . ", at_squodetail.ItemQtySpd       as ItemQtySpd_SQuoDet"
                . ", at_squodetail.ItemSpdStatus    as ItemSpdStatus_SQuoDet"
                
                . ", at_salesorder.SOrdNumber       as SOrdNumber_SO"
                . ", at_salesorder.SOrdDate         as SOrdDate_SO"
                . ", at_salesorder.SQuoNumber       as SQuoNumber_SO"
                . ", at_salesorder.TransStatus      as TransStatus_SO"
                
                . ", at_sorddetail.SOrdDetailID     as SOrdDetailID_SODet"
                . ", at_sorddetail.SQuoDetailID     as SQuoDetailID_SODet"
                . ", at_sorddetail.SQuoNumber       as SQuoNumber_SODet"
                . ", at_sorddetail.SOrdNumber       as SOrdNumber_SODet"
                . ", at_sorddetail.ItemQty          as ItemQty_SODet"
                . ", at_sorddetail.ItemQtySpd       as ItemQtySpdTotal_SODet"
                . ", at_sorddetail.ItemQtySpd       as ItemQtySpd_SODet"
                . ", at_sorddetail.ItemSpdStatus    as ItemSpdStatus_SODet"
                
                . ", at_deliveryorder.DOrdNumber    as DOrdNumber_DO"
                . ", at_deliveryorder.DOrdDate      as DOrdDate_DO"
                . ", at_deliveryorder.SOrdNumber    as SOrdNumber_DO"
                . ", at_deliveryorder.TransStatus   as TransStatus_DO"
                
                . ", at_dorddetail.DOrdDetailID     as DOrdDetailID_DODet"
                . ", at_dorddetail.SOrdDetailID     as SOrdDetailID_DODet"
                . ", at_dorddetail.SOrdNumber       as SOrdNumber_DODet"
                . ", at_dorddetail.DOrdNumber       as DOrdNumber_DODet"
                . ", at_dorddetail.ItemQty          as ItemQty_DODet"
                );
        
        $this->db->from('at_salesquotation');
        $this->db->join("at_squodetail", "at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber "
                . "and at_squodetail.ItemSpdStatus='0'","inner");
        $this->db->join('am_customer', 'at_salesquotation.CustomerCode = am_customer.CustomerCode',"inner");
        
        $this->db->join('at_salesorder', 'at_salesquotation.SQuoNumber = at_salesorder.SQuoNumber '
                . "and (at_salesorder.TransStatus='0' or at_salesorder.TransStatus='1' or at_salesorder.TransStatus='2'"
                . "  or at_salesorder.TransStatus='4' or at_salesorder.TransStatus='5' or at_salesorder.TransStatus='6'"
                . " or at_salesorder.TransStatus='7' ) ","left");//relasi dengan SO
        
        $this->db->join('at_sorddetail', " at_salesorder.SOrdNumber = at_sorddetail.SOrdNumber "
                . " AND at_salesquotation.SQuoNumber = at_sorddetail.SQuoNumber "
                . "and at_squodetail.SQuoDetailID = at_sorddetail.SQuoDetailID ","left");//lerasi dengan SOdetail
                //. "and at_sorddetail.ItemSpdStatus='0' 
        
        $this->db->join('at_deliveryorder', 'at_sorddetail.SOrdNumber = at_deliveryorder.SOrdNumber '
                . "and (at_deliveryorder.TransStatus='1' or at_deliveryorder.TransStatus='2') ","left");//relasi dengan DO
        
        $this->db->join("at_dorddetail","at_deliveryorder.DOrdNumber   = at_dorddetail.DOrdNumber "
                . " AND at_sorddetail.SOrdDetailID = at_dorddetail.SOrdDetailID","left");//lerasi dengan DOdetail
//                "at_dorddetail","at_sorddetail.SOrdDetailID = at_dorddetail.SOrdNumber "
//                . "and at_sorddetail.SQuoDetailID = at_dorddetail.SOrdDetailID "
//                . 
        
        $this->db->where_in('at_squodetail.ItemCode', $PreData);
                
        //jika itemCode All ato terisi
        if ($ItemCode != "" && $ItemCode != "All" ) {
            $this->db->like('at_squodetail.ItemDesc', $ItemCode);
        }
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesquotation.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesquotation.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesquotation.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_salesquotation.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesquotation.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_salesquotation.SQuoDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesquotation.SQuoDate <=', 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_squodetail.ItemCode", '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 sales quotation by item
    
    //get all sales invoice
    public function pre_get_all_sales_invoice() {  
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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_customer.CustomerCode as code
        "); 
        
        $this->db->from('av_all_transaction_for_report as avtrans_1');
        $this->db->join('at_salesreturn', 'at_salesreturn.SRetNumber = avtrans_1.Journal_Number',"left");        
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = avtrans_1.Journal_Number "
                . "|| at_salesinvoice.SInvNumber = at_salesreturn.SInvNumber ","left");        
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","left");
        
        $this->db->where_in('avtrans_1.trans_type', array('sRet', 'sinv'));
        //==== Customer jika tidak kosong
        if ($Customer != '') {
            $this->db->like('am_customer.CustomerName', $Customer);
        }//==== end Customer jika tidak kosong
        
        /* ========================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=============//
        
        $this->db->group_by("am_customer.CustomerCode");
//        $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();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        //print_r($result);
        return $result;
    }
    public function get_all_sales_invoice() {  
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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_salesreturn.SInvNumber IS NULL,
            (
              IF(
                avtrans_1.CurrencyCode <> '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 Receivable' 
                      AND avtrans_2.Sub_Type_Name <> 'Piutang Pajak' 
                      AND avtrans_2.Sub_Type_Name <> 'Piutang PPn Fiscal'
                      #AND avtrans_2.Sub_Type_Name <> 'Piutang Dagang'
                    )
                  )), 
                (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 Receivable' 
                      AND avtrans_3.Sub_Type_Name <> 'Piutang Pajak' 
                      AND avtrans_3.Sub_Type_Name <> 'Piutang PPn Fiscal'
                      #AND avtrans_3.Sub_Type_Name <> 'Piutang Dagang'
                    )
                  ))
              )
            ), 
            (
              IF(
                avtrans_1.CurrencyCode = '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 
                #WHERE avtrans_2.Journal_Number = at_salesreturn.SInvNumber
                  AND avtrans_2.Sign_Status = '0' 
                  AND (
                    #avtrans_2.Acc_Type_Name = 'Account Payable' 
                    #AND avtrans_2.Sub_Type_Name = 'Uang Muka Penjualan'
                    #avtrans_2.Acc_Type_Name = 'Revenue'
                    #AND avtrans_2.Sub_Type_Name = 'Retur Penjualan'                    
                    avtrans_2.Acc_Type_Name = 'Account Payable' 
                    AND avtrans_2.Account_Name LIKE 'Uang Muka Penjualan%'
                  )), 
                (SELECT 
                  SUM(avtrans_3.Amount_Currency) 
                FROM
                  av_all_transaction_for_report AS avtrans_3 
                WHERE avtrans_3.Journal_Number = avtrans_1.Journal_Number 
                #WHERE avtrans_3.Journal_Number = at_salesreturn.SInvNumber
                  AND avtrans_3.Sign_Status = '0' 
                  AND (
                    #avtrans_3.Acc_Type_Name = 'Account Payable'
                    #AND avtrans_3.Sub_Type_Name = 'Uang Muka Penjualan'  
                    #avtrans_3.Acc_Type_Name = 'Revenue'
                    #AND avtrans_3.Sub_Type_Name = 'Retur Penjualan'
                    avtrans_3.Acc_Type_Name = 'Account Payable' 
                    AND avtrans_3.Account_Name LIKE 'Uang Muka Penjualan%'
                  )
                )
              )
            )
          ) AS totalAmount,
          IF(
            at_salesreturn.SInvNumber IS NULL, 
            IF(
              avtrans_1.CurrencyCode = 'IDR',
              '',
              (SELECT 
                SUM(avtrans_3.Saldo_Debet)
                #SUM(avtrans_3.Saldo_Credit)
                #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 Receivable' 
                  AND avtrans_3.Sub_Type_Name = 'Piutang PPn Fiscal'
                  #AND avtrans_3.Sub_Type_Name <> 'Piutang Dagang'
                ))
            ), 
            ''
          ) AS totalFiscal,
          at_salesreturn.SInvNumber,
          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_customer.CustomerCode,
          am_customer.CustomerName,
          am_currency.CurrencySymbol
        "); 
        
        $this->db->from('av_all_transaction_for_report as avtrans_1');
        $this->db->join('at_salesreturn', 'at_salesreturn.SRetNumber = avtrans_1.Journal_Number',"left");        
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = avtrans_1.Journal_Number "
                . "|| at_salesinvoice.SInvNumber = at_salesreturn.SInvNumber ","left");
        
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","left");
        $this->db->join("am_currency", "am_currency.CurrencyCode = am_customer.CurrencyCode "
                . "and am_currency.CompanyCode = avtrans_1.CompanyCode","left");
        
        $this->db->where_in('avtrans_1.trans_type', array('sRet', 'sinv'));
        $this->db->where_in('am_customer.CustomerCode', $PreData);

        
        //==== Customer jika tidak kosong
        if ($Customer != '') {
            $this->db->like('am_customer.CustomerName', $Customer);
        }
        /* ========================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_sales_invoice_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'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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_salesinvoice.SInvNumber as SInvNumber_SI"
            . ", at_salesinvoice.BranchCode         as BranchCode_SI"
            . ", at_salesinvoice.CustomerCode       as CustomerCode_SI"
            . ", at_salesinvoice.SInvDate           as SInvDate_SI"
            . ", at_salesinvoice.SInvDesc           as SInvDesc_SI"
            . ", at_salesinvoice.SInvTotalAmount    as SInvTotalAmount_SI"
            . ", at_salesinvoice.TransStatus        as TransStatus_SI"
            . ", am_customer.CustomerName           as CustomerName_SI"
            //. ", at_salesinvoice.CurrencyCode       as CurrencyCode_SI"
            //. ", at_salesinvoice.CurrencyRate       as CurrencyRate_SI"
            . ", (select sum(at_sinvdetail.QtyReturn * at_sinvdetail.ItemPrice) from  at_sinvdetail where at_sinvdetail.SInvNumber=at_salesinvoice.SInvNumber group by at_sinvdetail.SInvNumber)           as QtyReturn_SI"
            . ", (select sum(at_sinvdetail.QtyBookReturn * at_sinvdetail.ItemPrice) from  at_sinvdetail where at_sinvdetail.SInvNumber=at_salesinvoice.SInvNumber group by at_sinvdetail.SInvNumber)           as QtyBookReturn_SI"
            
                
            . ", at_srecdetail.SRecDetailID     as SRecDetailID_SRDet"
            . ", at_srecdetail.SrecNumber       as SrecNumber_SRDet"
            //. ", at_srecdetail.SInvDetailID     as SInvDetailID_SRDet"
            //. ", at_srecdetail.SInvNumber       as SInvNumber_SRDet"
            //. ", at_srecdetail.SInvDate         as SInvDate_SRDet"
            //. ", at_srecdetail.SInvAmount       as SInvAmount_SRDet"
            . ", at_srecdetail.SRecDiscount     as SRecDiscount_SRDet"
            . ", at_srecdetail.SRecDPUsed       as SRecDPUsed_SRDet"
            . ", (at_srecdetail.SRecAmount+ at_srecdetail.SRecDPUsed + at_srecdetail.SrecRounded)     as SRecAmount_SRDet"
            . ", at_srecdetail.SrecRounded      as SrecRounded_SRDet"
            . ", at_srecdetail.SRecCurrency      as SRecCurrency_SRDet"
                
            . ", at_salesreceipt.SRecDate           as SRecDate_SR"
            . ", at_salesreceipt.SRecNumber         as SRecNumber_SR"
            . ", at_salesreceipt.SInvTotalAmount    as SInvTotalAmount_SR"  
            . ", at_salesreceipt.CurrencyRate       as CurrencyRate_SR"    
            . ", at_salesreceipt.TransStatus        as TransStatus_SR"   
            . ", am_currency.CurrencySymbol"
                ); 
        
        $this->db->from('at_salesinvoice');
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesinvoice.CustomerCode',"inner");
        
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber "
                . "and (at_salesinvoice.TransStatus='1' or at_salesinvoice.TransStatus='2') ","left");
        
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . " and at_salesreceipt.BranchCode = at_salesinvoice.BranchCode "
                . " and at_salesreceipt.TransStatus = '2' ","left");
        
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_salesreceipt.CurrencyCode "
                . " and am_currency.CompanyCode = '".$CompCode."'","left");
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesinvoice.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesinvoice.TransStatus', array(2));
        }
        //==== Customer jika tidak kosong
        if ($Customer != '') {
            $this->db->like('am_customer.CustomerName', $Customer);
        }
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesinvoice.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesinvoice.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_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesinvoice.SInvDate <=', 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_sinv . '.SInvDate', '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 sales invoice
    
    //get all sales amount item    
    public function pre_get_all_sales_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'):'';
        $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_sinvdetail.ItemCode as code"
        ); 
        
        $this->db->from('at_salesinvoice');       
        $this->db->join('at_sinvdetail', 'at_sinvdetail.SInvNumber = at_salesinvoice.SInvNumber '
                . " and (at_salesinvoice.TransStatus='1' OR at_salesinvoice.TransStatus='2' )","inner");
        $this->db->join('am_item', 'am_item.ItemCode = at_sinvdetail.ItemCode',"inner");
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode',"inner");
        $this->db->join('am_currency', 'am_currency.CurrencyCode = at_salesinvoice.CurrencyCode '
                . ' and am_currency.CompanyCode = am_branch.CompanyCode',"inner");
        /* ========================jika item tidak kosong ================= */
        if ($Item != '') {
            $this->db->like('at_sinvdetail.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================= */

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesinvoice.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesinvoice.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_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_sinvdetail.ItemCode");
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_sinv . '.SInvDate', '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_sales_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'):'';
        $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_salesinvoice.TransStatus"
            . ", at_salesinvoice.SInvDate"
            . ", am_currency.CurrencySymbol" //
            . ", at_salesinvoice.CurrencyCode" //
            . ", at_salesinvoice.CurrencyRate" //
            ." , at_sinvdetail.SInvDetailID"
            ." , at_sinvdetail.SInvNumber"
            ." , at_sinvdetail.ItemCode"
            ." , at_sinvdetail.ItemDesc"
            ." , at_sinvdetail.ItemQty"
            ." , at_sinvdetail.ItemUnit"
            ." , at_sinvdetail.ItemAmount"
            ." , at_sinvdetail.QtyReturn"      //
            ." , at_sinvdetail.QtyBookReturn"  //
            //." , at_sinvdetail.ItemPrice"      //
            ." , (select ItemPriceCogs from at_dorddetail where at_dorddetail.DOrdNumber = at_salesinvoice.DOrdNumber and at_dorddetail.ItemCode =at_sinvdetail.ItemCode) as ItemPriceCogs"      //
            ." , (at_sinvdetail.ItemAmount / at_sinvdetail.ItemQty)as ItemPrice"      //
//            . ", (select sum((at_sinvdetail.QtyReturn * at_sinvdetail.ItemPrice) ) from  at_sinvdetail where at_sinvdetail.SInvNumber=at_salesinvoice.SInvNumber group by at_sinvdetail.SInvNumber)           as totalReturn"
//            . ", (select sum((at_sinvdetail.QtyBookReturn * at_sinvdetail.ItemPrice) ) from  at_sinvdetail where at_sinvdetail.SInvNumber=at_salesinvoice.SInvNumber group by at_sinvdetail.SInvNumber)       as totalReturnNotApp"
            . ", (select sum(at_sretdetail.ItemAmount * at_salesreturn.CurrencyRate) from  at_sretdetail "
                . " inner join at_salesreturn on (at_sretdetail.SInvNumber = at_salesreturn.SInvNumber "
                . " AND at_sretdetail.SRetNumber = at_salesreturn.SRetNumber and at_salesreturn.TransStatus='2') "
                . " where at_sretdetail.ItemCode = at_sinvdetail.ItemCode AND at_sretdetail.SInvNumber = at_sinvdetail.SInvNumber"
                . " group by at_sretdetail.SInvNumber) as totalReturn"
                
            . ", (select sum(at_sretdetail.ItemAmount * at_salesreturn.CurrencyRate) from  at_sretdetail "
                . " inner join at_salesreturn on (at_sretdetail.SInvNumber = at_salesreturn.SInvNumber "
                . " AND at_sretdetail.SRetNumber = at_salesreturn.SRetNumber "
                . " and (at_salesreturn.TransStatus='4' OR at_salesreturn.TransStatus='5' OR at_salesreturn.TransStatus='6')) "
                . " where at_sretdetail.ItemCode = at_sinvdetail.ItemCode AND at_sretdetail.SInvNumber = at_sinvdetail.SInvNumber"
                . " group by at_sretdetail.SInvNumber) as totalReturnNotApp"
            ." , at_sinvdetail.ItemCategoryCode"
            ." , at_sinvdetail.ItemCategoryID"
            . ", am_item.TransStatus as itemTransStatus"); 
        
        $this->db->from('at_salesinvoice');       
        $this->db->join('at_sinvdetail', 'at_sinvdetail.SInvNumber = at_salesinvoice.SInvNumber '
                . " and (at_salesinvoice.TransStatus='1' OR at_salesinvoice.TransStatus='2' )","inner");
        $this->db->join('am_item', 'am_item.ItemCode = at_sinvdetail.ItemCode',"inner");
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode',"inner");
        $this->db->join('am_currency', 'am_currency.CurrencyCode = at_salesinvoice.CurrencyCode '
                . ' and am_currency.CompanyCode = am_branch.CompanyCode',"inner");
        
        $this->db->where_in('at_sinvdetail.ItemCode', $PreData);
        /* ========================jika item tidak kosong ================= */
        if ($Item != '') {
            $this->db->like('at_sinvdetail.ItemDesc', $Item);
        } 
        /* ========================jika item tidak kosong ================= */
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        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));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesinvoice.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesinvoice.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_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesinvoice.SInvDate <=', 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_sinv . '.SInvDate', '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 sales amount item
    
    //get all salesamount item category
    public function pre_get_all_sales_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'):'';
        $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'];
//        $limitEnd       = ($Pages['limit'] * $Pages['pages'])-1;
        
        /* =================================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_sinvdetail.ItemCategoryID as code"); 
        
        $this->db->from('at_salesinvoice');       
        $this->db->join('at_sinvdetail', 'at_sinvdetail.SInvNumber = at_salesinvoice.SInvNumber '
                . " and (at_salesinvoice.TransStatus='1' OR at_salesinvoice.TransStatus='2' )","inner");
        $this->db->join('am_itemcategory', "am_itemcategory.ItemCategoryID = at_sinvdetail.ItemCategoryID ","inner");
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode',"inner");
        $this->db->join('am_currency', 'am_currency.CurrencyCode = at_salesinvoice.CurrencyCode '
                . ' and am_currency.CompanyCode = am_branch.CompanyCode',"inner");
        /* ===================================find by itemCategory code========================================================= */
        if ($Category != "" && $Category != "All" ) {
            $this->db->like('am_itemcategory.ItemCategoryName', $Category);
        } 
        /* ==================================end find by item category code===================================================== */
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        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));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesinvoice.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesinvoice.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_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by('at_sinvdetail.ItemCategoryID');
        /* ==============================================SHORTING TABLE========================================================== */
//            $this->db->order_by('at_sinvdetail.ItemCategoryCode', 'ASC');
            
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_sinvdetail.ItemCategoryCode', '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_sales_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'):'';
        $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_salesinvoice.TransStatus"
            . ", at_salesinvoice.SInvDate"
            . ", am_currency.CurrencySymbol" //
            . ", at_salesinvoice.CurrencyCode" //
            . ", at_salesinvoice.CurrencyRate" //
            ." , at_sinvdetail.SInvDetailID"
            ." , at_sinvdetail.SInvNumber"
            ." , at_sinvdetail.ItemCode"
            ." , at_sinvdetail.ItemDesc"
            ." , at_sinvdetail.ItemQty"
            ." , at_sinvdetail.ItemUnit"
            ." , at_sinvdetail.ItemAmount"
            ." , at_sinvdetail.QtyReturn"      //
            ." , at_sinvdetail.QtyBookReturn"  //
//            ." , at_sinvdetail.ItemPrice"  //
            ." , (select ItemPriceCogs from at_dorddetail where at_dorddetail.DOrdNumber = at_salesinvoice.DOrdNumber and at_dorddetail.ItemCode =at_sinvdetail.ItemCode) as ItemPriceCogs"      //
            ." , (at_sinvdetail.ItemAmount / at_sinvdetail.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"
            . ", (select sum(at_sretdetail.ItemAmount * at_salesreturn.CurrencyRate) from  at_sretdetail "
                . " inner join at_salesreturn on (at_sretdetail.SInvNumber = at_salesreturn.SInvNumber "
                . " AND at_sretdetail.SRetNumber = at_salesreturn.SRetNumber and at_salesreturn.TransStatus='2') "
                . " where at_sretdetail.ItemCode = at_sinvdetail.ItemCode AND at_sretdetail.SInvNumber = at_sinvdetail.SInvNumber"
                . " group by at_sretdetail.SInvNumber) as totalReturn"
                
            . ", (select sum(at_sretdetail.ItemAmount * at_salesreturn.CurrencyRate) from  at_sretdetail "
                . " inner join at_salesreturn on (at_sretdetail.SInvNumber = at_salesreturn.SInvNumber "
                . " AND at_sretdetail.SRetNumber = at_salesreturn.SRetNumber "
                . " and (at_salesreturn.TransStatus='4' OR at_salesreturn.TransStatus='5' OR at_salesreturn.TransStatus='6')) "
                . " where at_sretdetail.ItemCode = at_sinvdetail.ItemCode AND at_sretdetail.SInvNumber = at_sinvdetail.SInvNumber"
                . " group by at_sretdetail.SInvNumber) as totalReturnNotApp"
                
            ." , at_sinvdetail.ItemCategoryCode"
            ." , at_sinvdetail.ItemCategoryID"
            . ", am_itemcategory.ItemCategoryName"
            . ", am_itemcategory.TransStatus as itemTransStatus"); 
        
        $this->db->from('at_salesinvoice');       
        $this->db->join('at_sinvdetail', 'at_sinvdetail.SInvNumber = at_salesinvoice.SInvNumber '
                . " and (at_salesinvoice.TransStatus='1' OR at_salesinvoice.TransStatus='2' )","inner");
        $this->db->join('am_itemcategory', "am_itemcategory.ItemCategoryID = at_sinvdetail.ItemCategoryID ","inner");
        $this->db->join('am_branch', 'am_branch.BranchCode = at_salesinvoice.BranchCode',"inner");
        $this->db->join('am_currency', 'am_currency.CurrencyCode = at_salesinvoice.CurrencyCode '
                . ' and am_currency.CompanyCode = am_branch.CompanyCode',"inner");
        
        $this->db->where_in('at_sinvdetail.ItemCategoryID', $PreData);
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        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));
        }

        /* ===================================find by itemCategory code========================================================= */
        if ($Category != "" && $Category != "All" ) {
            $this->db->like('am_itemcategory.ItemCategoryName', $Category);
        } 
        /* ==================================end find by item category code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesinvoice.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesinvoice.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_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesinvoice.SInvDate <=', 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_sinv . '.SInvDate', '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 salesamount item category
    
    //get all sales order for DP
    public function pre_get_all_sales_order_for_dp() {  
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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_salesorder.CustomerCode      as code"
                );         
        $this->db->from('at_salesorder');       
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesorder.CustomerCode',"inner");
        $this->db->join('at_srecdetail', "at_srecdetail.SInvNumber = at_salesorder.SOrdNumber ","left");
        $this->db->join('at_salesreceipt', "at_salesreceipt.SRecNumber = at_srecdetail.SRecNumber "
                . " and (at_salesreceipt.TransStatus='1' or at_salesreceipt.TransStatus='2' )","left");
        $this->db->where('at_salesorder.SOrdDPStatus', 1);
        
        /* ===================================find by customer name========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by customer name ===================================================== */
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesorder.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesorder.TransStatus', array(0,2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesorder.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesorder.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesorder.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_salesorder.SOrdDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesorder.SOrdDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_salesorder.CustomerCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_sord . '.SOrdDate', '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_sales_order_for_dp() {        
        /* =================================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'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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_salesorder.BranchCode          as BranchCode_SO"
            .", at_salesorder.CustomerCode      as CustomerCode_SO"
            .", at_salesorder.SOrdDate          as SOrdDate_SO"
            .", at_salesorder.SOrdNumber        as SOrdNumber_SO"
            .", at_salesorder.SOrdTotalDP       as SOrdTotalDP_SO"
            .", at_salesorder.SRecTotalDP       as SRecTotalDP_SO"
            .", at_salesorder.TransStatus       as TransStatus_SO"
            .", am_customer.CustomerName        as CustomerName_SO"
                
            .", at_srecdetail.SRecDetailID      as SRecDetailID_SRDet"
            .", at_srecdetail.SrecNumber        as SrecNumber_SRDet"
            .", at_srecdetail.SInvNumber        as SInvNumber_SRDet"
            .", at_srecdetail.SInvAmount        as SInvAmount_SRDet"
            .", at_srecdetail.SRecAmount        as SRecAmount_SRDet"
            .", at_srecdetail.SrecRounded       as SrecRounded_SRDet"
            .", at_srecdetail.DPStatus          as DPStatus_SRDet"
                
            .", at_salesreceipt.SRecDate        as SRecDate_SR"
            .", at_salesreceipt.SRecNumber      as SRecNumber_SR"
            .", at_salesreceipt.SRecDesc        as SRecDesc_SR"
            .", at_salesreceipt.TransStatus     as TransStatus_SR"); 
        
        $this->db->from('at_salesorder');       
        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesorder.CustomerCode',"inner");
        $this->db->join('at_srecdetail', "at_srecdetail.SInvNumber = at_salesorder.SOrdNumber ","left");
        $this->db->join('at_salesreceipt', "at_salesreceipt.SRecNumber = at_srecdetail.SRecNumber "
                . " and (at_salesreceipt.TransStatus='1' or at_salesreceipt.TransStatus='2' )","left");
        $this->db->where('at_salesorder.SOrdDPStatus', 1);        
        $this->db->where_in('at_salesorder.CustomerCode', $PreData);
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesorder.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesorder.TransStatus', array(0,2));
        }

        /* ===================================find by customer name========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by customer name ===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesorder.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesorder.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesorder.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_salesorder.SOrdDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesorder.SOrdDate <=', 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_sord . '.SOrdDate', '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 sales order for DP
    
    //get all sales quotation by salesman
    public function pre_get_all_sq_by_salesman() {  
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Sales          = ($this->input->post('txt_sales')!=null)?$this->input->post('txt_sales'):'';
        $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_salesquotation.SalesmanCode as code"
                );
        
        $this->db->from('at_salesquotation');
        $this->db->join("at_squodetail", "at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber "
                . "and at_squodetail.ItemSpdStatus='0'","inner");
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesquotation.SalesmanCode',"inner");
        
        $this->db->join('at_salesorder', 'at_salesquotation.SQuoNumber = at_salesorder.SQuoNumber '
                . "and (at_salesorder.TransStatus='2' or at_salesorder.TransStatus='1' ) ","left");//relasi dengan SO
        
        $this->db->join('at_sorddetail', " at_salesorder.SOrdNumber = at_sorddetail.SOrdNumber "
                . " AND at_salesquotation.SQuoNumber = at_sorddetail.SQuoNumber "
                . "and at_squodetail.SQuoDetailID = at_sorddetail.SQuoDetailID ","left");//lerasi dengan SOdetail
                //. "and at_sorddetail.ItemSpdStatus='0' 
        
        $this->db->join('at_deliveryorder', 'at_sorddetail.SOrdNumber = at_deliveryorder.SOrdNumber '
                . "and at_deliveryorder.TransStatus='2' ","left");//relasi dengan DO
        
        $this->db->join("at_dorddetail","at_deliveryorder.DOrdNumber   = at_dorddetail.DOrdNumber "
                . " AND at_sorddetail.SOrdDetailID = at_dorddetail.SOrdDetailID","left");//lerasi dengan DOdetail
//                "at_dorddetail","at_sorddetail.SOrdDetailID = at_dorddetail.SOrdNumber "
//                . "and at_sorddetail.SQuoDetailID = at_dorddetail.SOrdDetailID "
//                . 
        
        if ($Sales != '') {
            $this->db->like('am_employee.EmployeeName', $Sales);
        } 
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesquotation.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesquotation.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesquotation.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_salesquotation.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesquotation.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_salesquotation.SQuoDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesquotation.SQuoDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_salesquotation.SalesmanCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_sq . '.SQuoNumber', '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_sq_by_salesman() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Sales          = ($this->input->post('txt_sales')!=null)?$this->input->post('txt_sales'):'';
        $DateFrom       = ($this->input->post('txt_DateFrom')!=null)?$this->input->post('txt_DateFrom'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $TransStatus    = ($this->input->post('chk_TransStatus')!=null)?$this->input->post('chk_TransStatus'):'';
        $txtTable       = ($this->input->post('txtTable')!=null)?$this->input->post('txtTable'):'';
        $txtField       = ($this->input->post('txtField')!=null)?$this->input->post('txtField'):'';
        $typeShorting   = ($this->input->post('typeShorting')!=null)?$this->input->post('typeShorting'):'';
        
        $PreData        = ($this->input->post('predata')!=[null])?$this->input->post('predata'):'';
        /* =================================END post dari find di kiri==================================================== */
           
        /* ==========================================by session======================================================= */
        $sess_company       = $this->session->userdata('company');
        $sess_branch        = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */
    
        $this->db->select("distinct(at_salesquotation.SQuoNumber)SQuoNumber_SQ"
                . ", at_salesquotation.BranchCode   as BranchCode_SQ"
                . ", at_salesquotation.CustomerCode as CustomerCode_SQ"
                . ", at_salesquotation.SQuoDate     as SQuoDate_SQ"
                . ", at_salesquotation.CustPODate   as CustPODate_SQ"
                . ", at_salesquotation.SQuoDesc     as SQuoDesc_SQ"
                . ", at_salesquotation.TransStatus  as TransStatus_SQ"
                . ", at_salesquotation.SalesmanCode as SalesmanCode_SQ"
                . ", am_employee.EmployeeName       as SalesmanName_SQ"
                
                . ", at_squodetail.SQuoDetailID     as SQuoDetailID_SQuoDet"
                . ", at_squodetail.ItemCode         as ItemCode_SQuoDet"
                . ", at_squodetail.ItemDesc         as ItemDesc_SQuoDet"
                . ", at_squodetail.ItemUnit         as ItemUnit_SQuoDet"
                . ", at_squodetail.ItemQty          as ItemQty_SQuoDet"
                . ", at_squodetail.ItemQtySO        as ItemQtySO_SQuoDet"
                . ", at_squodetail.ItemQtyDO        as ItemQtyDO_SQuoDet"
                . ", at_squodetail.ItemQtySpd       as ItemQtySpd_SQuoDet"
                . ", at_squodetail.ItemSpdStatus    as ItemSpdStatus_SQuoDet"
                
                . ", at_salesorder.SOrdNumber       as SOrdNumber_SO"
                . ", at_salesorder.SOrdDate         as SOrdDate_SO"
                . ", at_salesorder.SQuoNumber       as SQuoNumber_SO"
                . ", at_salesorder.TransStatus      as TransStatus_SO"
                
                . ", at_sorddetail.SOrdDetailID     as SOrdDetailID_SODet"
                . ", at_sorddetail.SQuoDetailID     as SQuoDetailID_SODet"
                . ", at_sorddetail.SQuoNumber       as SQuoNumber_SODet"
                . ", at_sorddetail.SOrdNumber       as SOrdNumber_SODet"
                . ", at_sorddetail.ItemQty          as ItemQty_SODet"
                . ", at_sorddetail.ItemQtySpd       as ItemQtySpdTotal_SODet"
                . ", at_sorddetail.ItemQtySpd       as ItemQtySpd_SODet"
                . ", at_sorddetail.ItemSpdStatus    as ItemSpdStatus_SODet"
                
                . ", at_deliveryorder.DOrdNumber    as DOrdNumber_DO"
                . ", at_deliveryorder.DOrdDate      as DOrdDate_DO"
                . ", at_deliveryorder.SOrdNumber    as SOrdNumber_DO"
                . ", at_deliveryorder.TransStatus   as TransStatus_DO"
                
                . ", at_dorddetail.DOrdDetailID     as DOrdDetailID_DODet"
                . ", at_dorddetail.SOrdDetailID     as SOrdDetailID_DODet"
                . ", at_dorddetail.SOrdNumber       as SOrdNumber_DODet"
                . ", at_dorddetail.DOrdNumber       as DOrdNumber_DODet"
                . ", at_dorddetail.ItemQty          as ItemQty_DODet"
                );
        
        $this->db->from('at_salesquotation');
        $this->db->join("at_squodetail", "at_salesquotation.SQuoNumber = at_squodetail.SQuoNumber "
                . "and at_squodetail.ItemSpdStatus='0'","inner");
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_salesquotation.SalesmanCode',"inner");
        
        $this->db->join('at_salesorder', 'at_salesquotation.SQuoNumber = at_salesorder.SQuoNumber '
                . "and (at_salesorder.TransStatus='2' or at_salesorder.TransStatus='1' ) ","left");//relasi dengan SO
        
        $this->db->join('at_sorddetail', " at_salesorder.SOrdNumber = at_sorddetail.SOrdNumber "
                . " AND at_salesquotation.SQuoNumber = at_sorddetail.SQuoNumber "
                . "and at_squodetail.SQuoDetailID = at_sorddetail.SQuoDetailID ","left");//lerasi dengan SOdetail
                //. "and at_sorddetail.ItemSpdStatus='0' 
        
        $this->db->join('at_deliveryorder', 'at_sorddetail.SOrdNumber = at_deliveryorder.SOrdNumber '
                . "and at_deliveryorder.TransStatus='2' ","left");//relasi dengan DO
        
        $this->db->join("at_dorddetail","at_deliveryorder.DOrdNumber   = at_dorddetail.DOrdNumber "
                . " AND at_sorddetail.SOrdDetailID = at_dorddetail.SOrdDetailID","left");//lerasi dengan DOdetail
//                "at_dorddetail","at_sorddetail.SOrdDetailID = at_dorddetail.SOrdNumber "
//                . "and at_sorddetail.SQuoDetailID = at_dorddetail.SOrdDetailID "
//                . 
        
        $this->db->where_in('at_salesquotation.SalesmanCode', $PreData);
        
        if ($Sales != '') {
            $this->db->like('am_employee.EmployeeName', $Sales);
        } 
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesquotation.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesquotation.TransStatus', array(2));
        }

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesquotation.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_salesquotation.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesquotation.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_salesquotation.SQuoDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesquotation.SQuoDate <=', 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_sq . '.SQuoNumber', '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 sales quotation by salesman
    
    //get all sales invoice by salesman
    public function pre_get_all_sales_invoice_by_salesman() {  
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):$this->session->userdata('company');
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Sales          = ($this->input->post('txt_sales')!=null)?$this->input->post('txt_sales'):'';
        $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(""
            . "at_sinvdetail.SalesmanCode     as code"
            ); 
        
        $this->db->from('at_salesinvoice');
        $this->db->join('at_sinvdetail', 'at_sinvdetail.SInvNumber = at_salesinvoice.SInvNumber',"inner");
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_sinvdetail.SalesmanCode',"inner");        
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber "
                . "and (at_salesinvoice.TransStatus='1' or at_salesinvoice.TransStatus='2') "
                . "and (at_srecdetail.FiscalStatus='0')","left");        
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . " and at_salesreceipt.BranchCode = at_salesinvoice.BranchCode "
                . " and at_salesreceipt.TransStatus = '2' "
                . " and at_salesreceipt.PPnFiscalStatus='0'","left");
        
        if ($Sales != "" && $Sales != "All" ) {
            $this->db->like('am_employee.EmployeeName', $Sales);
        } 
        /* ==================================end find by sales name code===================================================== */
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesinvoice.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesinvoice.TransStatus', array(2));
        }

        /* ===================================find by sales name code========================================================= */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesinvoice.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesinvoice.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_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        $this->db->group_by("at_sinvdetail.SalesmanCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_sinv . '.SInvDate', '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();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        //print_r($result);
        return $result;
    }
    public function get_all_sales_invoice_by_salesman(){   
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):$this->session->userdata('company');
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Sales          = ($this->input->post('txt_sales')!=null)?$this->input->post('txt_sales'):'';
        $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("at_salesinvoice.SInvNumber as SInvNumber_SI"
            . ", at_salesinvoice.BranchCode         as BranchCode_SI"
            . ", at_salesinvoice.CustomerCode       as CustomerCode_SI"
            . ", at_salesinvoice.SInvDate           as SInvDate_SI"
            . ", at_salesinvoice.SInvDesc           as SInvDesc_SI"
            . ", at_salesinvoice.TransStatus        as TransStatus_SI"
            . ", (at_salesinvoice.SInvSubTotal - SInvDiscount + SInvEstFreight)   as SInvTotalAmount_SI"
//            . ", at_salesinvoice.SInvTotalAmount    as SInvTotalAmount_SI"
//            . ", am_customer.CustomerName           as CustomerName_SI"
            . ", am_currency.CurrencySymbol         as CurrencySymbol_SI"
                
            . ", at_sinvdetail.SalesmanCode     as SalesmanCode_SInvDet"
            . ", am_employee.EmployeeName       as SalesmanName_SInvDet"
                
            . ", (select sum(at_salesreturn.SRetSubTotal) from  at_salesreturn where at_salesreturn.SInvNumber=at_salesinvoice.SInvNumber and at_salesreturn.TransStatus='2' group by at_salesreturn.SInvNumber)           as QtyReturn_SI"
            . ", (select sum(at_salesreturn.SRetSubTotal) from  at_salesreturn where at_salesreturn.SInvNumber=at_salesinvoice.SInvNumber and at_salesreturn.TransStatus IN ('4','5','6')  group by at_salesreturn.SInvNumber)           as QtyBookReturn_SI"
                
//            . ", (select sum(at_sinvdetail.QtyReturn * at_sinvdetail.ItemPrice) from  at_sinvdetail where at_sinvdetail.SInvNumber=at_salesinvoice.SInvNumber group by at_sinvdetail.SInvNumber)           as QtyReturn_SI"
//            . ", (select sum(at_sinvdetail.QtyBookReturn * at_sinvdetail.ItemPrice) from  at_sinvdetail where at_sinvdetail.SInvNumber=at_salesinvoice.SInvNumber group by at_sinvdetail.SInvNumber)           as QtyBookReturn_SI"
                
            . ", at_srecdetail.SRecDetailID     as SRecDetailID_SRDet"
            . ", at_srecdetail.SrecNumber       as SrecNumber_SRDet"
//            . ", at_srecdetail.SRecDiscount     as SRecDiscount_SRDet"
            . ", IF(at_salesreceipt.CurrencyCode = 'IDR'"
                . ","#CustomerIDR 
                . "     IF(at_salesreceipt.BankCurrCode = 'IDR'"
                . "     ,"#BankIDR
                . "         (at_srecdetail.SRecDiscount)"
                . "     ,"#BankNotIDR 
                . "         (at_srecdetail.SRecDiscount * at_salesreceipt.BankCurrRate)"
                . "     )"
                . ","#CustomerNotIDR
                . "     IF(at_salesreceipt.BankCurrCode = 'IDR'"
                . "     ,"#BankIDR
                . "         (at_srecdetail.SRecDiscount / at_salesreceipt.BankCurrRate)"
                . "     ,"#BankNotIDR 
                . "         (at_srecdetail.SRecDiscount)"
                . "     )"
            . ")  as SRecDiscount_SRDet"
                
//            . ", at_srecdetail.SRecDPUsed       as SRecDPUsed_SRDet"//            
            . ", at_srecdetail.SRecDPUsed as SRecDPUsed_SRDet"
                
//            . ", (at_srecdetail.SRecAmount + at_srecdetail.SRecDPUsed + at_srecdetail.SrecRounded)     as SRecAmount_SRDet"
            . ", IF(at_salesreceipt.CurrencyCode = 'IDR'"
                . ","#CustomerIDR 
                . "     IF(at_salesreceipt.BankCurrCode = 'IDR'"
                . "     ,"#BankIDR
                . "         (at_srecdetail.SRecAmount +  at_srecdetail.SrecRounded)"
                . "     ,"#BankNotIDR 
                . "         ((at_srecdetail.SRecAmount * at_salesreceipt.BankCurrRate) + (at_srecdetail.SrecRounded * at_salesreceipt.BankCurrRate))"
                . "     )"
                . ","#CustomerNotIDR
                . "     IF(at_salesreceipt.BankCurrCode = 'IDR'"
                . "     ,"#BankIDR
                . "         ((at_srecdetail.SRecAmount / at_salesreceipt.BankCurrRate) + (at_srecdetail.SrecRounded / at_salesreceipt.BankCurrRate))"
                . "     ,"#BankNotIDR 
                . "         (at_srecdetail.SRecAmount + at_srecdetail.SrecRounded)"
                . "     )"
                . ") as SRecAmount_SRDet"
            . ", at_srecdetail.SrecRounded      as SrecRounded_SRDet"
                 
            . ", at_salesreceipt.SRecDate           as SRecDate_SR"
            . ", at_salesreceipt.SRecNumber         as SRecNumber_SR"
            . ", at_salesreceipt.SInvTotalAmount    as SInvTotalAmount_SR"  
            . ", at_salesreceipt.CurrencyRate       as CurrencyRate_SR"    
            . ", at_salesreceipt.TransStatus        as TransStatus_SR"
            . ", am_curr2.CurrencySymbol            as CurrencySymbol_SR"); 
        
        $this->db->from('at_salesinvoice');
//        $this->db->join('am_customer', 'am_customer.CustomerCode = at_salesinvoice.CustomerCode',"inner");
        $this->db->join('at_sinvdetail', 'at_sinvdetail.SInvNumber = at_salesinvoice.SInvNumber',"inner");
        $this->db->join('am_employee', 'am_employee.EmployeeCode = at_sinvdetail.SalesmanCode',"inner");
        
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber "
                . "and (at_salesinvoice.TransStatus='1' or at_salesinvoice.TransStatus='2') "
                . "and (at_srecdetail.FiscalStatus='0')","left");
        
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . " and at_salesreceipt.BranchCode = at_salesinvoice.BranchCode "
                . " and at_salesreceipt.TransStatus = '2' "
                . " and at_salesreceipt.PPnFiscalStatus='0'","left");
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_salesinvoice.CurrencyCode "
                . "and (am_currency.CompanyCode='".$CompCode."')","left");
        $this->db->join("am_currency as am_curr2", "am_curr2.CurrencyCode = at_salesreceipt.CurrencyCode "
                . "and (am_curr2.CompanyCode='".$CompCode."')","left");
        
        
        $this->db->where_in('at_sinvdetail.SalesmanCode', $PreData);

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

        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesinvoice.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesinvoice.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesinvoice.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_salesinvoice.SInvDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesinvoice.SInvDate <=', 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_sinv . '.SInvDate', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        
        $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;
    }
    
    //get all sales return by customer
    public function pre_get_all_sales_return_by_customer() {  
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
//        $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_salesreturn.CustomerCode       as code
                ");         
        $this->db->from("at_salesreturn");
        $this->db->join("am_customer", "at_salesreturn.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_sretdetail", "at_sretdetail.SRetNumber = at_salesreturn.SRetNumber ","inner");       
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesreturn.BranchCode ","inner");       
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_salesreturn.CurrencyCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");
        
        //==== Customer jika tidak kosong
        if ($Customer != '') {
            $this->db->like('am_customer.CustomerName', $Customer);
        }//==== End Customer jika tidak kosong
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesreturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesreturn.TransStatus', array(2));
        }
        //==== Item jika tidak kosong
//        if ($Item != '') {
//            $this->db->like('at_sretdetail.ItemDesc', $Item);
//        }//==== End Item jika tidak kosong
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesreturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesreturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesreturn.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_salesreturn.SRetdate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesreturn.SRetdate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_salesreturn.CustomerCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_sls_ret . '.SRetdate', '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_sales_return_by_customer() { 
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
//        $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_salesreturn.BranchCode           as BranchCode_SRet
                    , at_salesreturn.CustomerCode       as CustomerCode_SRet
                    , at_salesreturn.SRetdate           as SRetdate_SRet
                    , at_salesreturn.SRetNumber         as SRetNumber_SRet
                    , at_salesreturn.SInvNumber         as SInvNumber_SRet
                    , at_salesreturn.SRetDesc           as SRetDesc_SRet
                    , am_currency.CurrencySymbol
                    , at_salesreturn.CurrencyCode       as CurrencyCode_SRet
                    , at_salesreturn.CurrencyRate       as CurrencyRate_SRet
                    , at_salesreturn.FiscalRate         as FiscalRate_SRet
                    , at_salesreturn.SRetSubTotal       as SRetSubTotal_SRet
                    , at_salesreturn.SRetDiscPcnt       as SRetDiscPcnt_SRet
                    , at_salesreturn.SRetDiscount       as SRetDiscount_SRet
                    , at_salesreturn.SRetTotalAmount    as SRetTotalAmount_SRet
                    , at_salesreturn.SRetPPNOut         as SRetPPNOut_SRet
                    , at_salesreturn.TransStatus        as TransStatus_SRet
                    , am_customer.CustomerName          as CustomerName_SRet
                    
                    , at_sretdetail.SRetDetailID    as SRetDetailID_SRetDet
                    , at_sretdetail.SRetDate        as SRetDate_SRetDet
                    , at_sretdetail.SRetNumber      as SRetNumber_SRetDet
                    , at_sretdetail.SInvNumber      as SInvNumber_SRetDet
                    , at_sretdetail.SInvDetailID    as SInvDetailID_SRetDet
                    , at_sretdetail.ItemCode        as ItemCode_SRetDet
                    , at_sretdetail.ItemDesc        as ItemDesc_SRetDet
                    , at_sretdetail.ItemQty         as ItemQty_SRetDet
                    , at_sretdetail.ItemQtyRet      as ItemQtyRet_SRetDet
                    , at_sretdetail.InvoiceQty      as InvoiceQty_SRetDet
                    , at_sretdetail.ItemUnit        as ItemUnit_SRetDet"
//                    .", at_sretdetail.ItemPrice       as ItemPrice_SRetDet"
//        var amountBeforeDisc = ((dtResults.ItemPrice_SRetDet*1)*(dtResults.ItemQty_SRetDet*1)*(dtResults.ItemDiscPcnt_SRetDet*1)/100)*1;
//                ((dtResults.ItemAmount_SRetDet*1)+ amountBeforeDisc )/(dtResults.ItemQty_SRetDet*1),
                    .", (at_sretdetail.ItemAmount / at_sretdetail.ItemQty)       as ItemPrice_SRetDet"
                    .", at_sretdetail.ItemPriceCogs   as ItemPriceCogs_SRetDet
                    , at_sretdetail.ItemAmountCogs  as ItemAmountCogs_SRetDet
                    , at_sretdetail.ItemAmount      as ItemAmount_SRetDet
                    , at_sretdetail.ItemDiscPcnt    as ItemDiscPcnt_SRetDet
                    , at_sretdetail.AmountTax       as AmountTax_SRetDet
                    , at_sretdetail.ItemTotalAmount as ItemTotalAmount_SRetDet
                "); 
        
        $this->db->from("at_salesreturn");
        $this->db->join("am_customer", "at_salesreturn.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_sretdetail", "at_sretdetail.SRetNumber = at_salesreturn.SRetNumber ","inner");       
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesreturn.BranchCode ","inner");       
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_salesreturn.CurrencyCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");
        
        $this->db->where_in('at_salesreturn.CustomerCode', $PreData);

        //==== Customer jika tidak kosong
        if ($Customer != '') {
            $this->db->like('am_customer.CustomerName', $Customer);
        }//==== End Customer jika tidak kosong
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_salesreturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesreturn.TransStatus', array(2));
        }
        //==== Item jika tidak kosong
//        if ($Item != '') {
//            $this->db->like('at_sretdetail.ItemDesc', $Item);
//        }//==== End Item jika tidak kosong
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesreturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesreturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesreturn.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_salesreturn.SRetdate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesreturn.SRetdate <=', 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_sls_ret . '.SRetdate', '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 sales return by customer
    
    //get all sales return by item
    public function pre_get_all_sales_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_sretdetail.ItemCode       as code
        ");         
        $this->db->from("at_salesreturn");
        $this->db->join("am_customer", "at_salesreturn.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_sretdetail", "at_sretdetail.SRetNumber = at_salesreturn.SRetNumber ","inner");       
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesreturn.BranchCode ","inner");       
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_salesreturn.CurrencyCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");
        
        //==== Item jika tidak kosong
        if ($Item != '') {
            $this->db->like('at_sretdetail.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_salesreturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesreturn.TransStatus', array(2));
        }
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesreturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesreturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesreturn.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_salesreturn.SRetdate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesreturn.SRetdate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_sretdetail.ItemCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_sls_ret . '.SRetdate', '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_sales_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_salesreturn.BranchCode           as BranchCode_SRet
                    , at_salesreturn.CustomerCode       as CustomerCode_SRet
                    , at_salesreturn.SRetdate           as SRetdate_SRet
                    , at_salesreturn.SRetNumber         as SRetNumber_SRet
                    , at_salesreturn.SInvNumber         as SInvNumber_SRet
                    , at_salesreturn.SRetDesc           as SRetDesc_SRet
                    , am_currency.CurrencySymbol
                    , at_salesreturn.CurrencyCode       as CurrencyCode_SRet
                    , at_salesreturn.CurrencyRate       as CurrencyRate_SRet
                    , at_salesreturn.FiscalRate         as FiscalRate_SRet
                    , at_salesreturn.SRetSubTotal       as SRetSubTotal_SRet
                    , at_salesreturn.SRetDiscPcnt       as SRetDiscPcnt_SRet
                    , at_salesreturn.SRetDiscount       as SRetDiscount_SRet
                    , at_salesreturn.SRetTotalAmount    as SRetTotalAmount_SRet
                    , at_salesreturn.SRetPPNOut         as SRetPPNOut_SRet
                    , at_salesreturn.TransStatus        as TransStatus_SRet
                    , am_customer.CustomerName          as CustomerName_SRet
                    
                    , at_sretdetail.SRetDetailID    as SRetDetailID_SRetDet
                    , at_sretdetail.SRetDate        as SRetDate_SRetDet
                    , at_sretdetail.SRetNumber      as SRetNumber_SRetDet
                    , at_sretdetail.SInvNumber      as SInvNumber_SRetDet
                    , at_sretdetail.SInvDetailID    as SInvDetailID_SRetDet
                    , at_sretdetail.ItemCode        as ItemCode_SRetDet
                    , at_sretdetail.ItemDesc        as ItemDesc_SRetDet
                    , at_sretdetail.ItemQty         as ItemQty_SRetDet
                    , at_sretdetail.ItemQtyRet      as ItemQtyRet_SRetDet
                    , at_sretdetail.InvoiceQty      as InvoiceQty_SRetDet
                    , at_sretdetail.ItemUnit        as ItemUnit_SRetDet"
//                    .", at_sretdetail.ItemPrice       as ItemPrice_SRetDet"
//        var amountBeforeDisc = ((dtResults.ItemPrice_SRetDet*1)*(dtResults.ItemQty_SRetDet*1)*(dtResults.ItemDiscPcnt_SRetDet*1)/100)*1;
//                ((dtResults.ItemAmount_SRetDet*1)+ amountBeforeDisc )/(dtResults.ItemQty_SRetDet*1),
                    .", (at_sretdetail.ItemAmount / at_sretdetail.ItemQty)       as ItemPrice_SRetDet"
                    .", at_sretdetail.ItemPriceCogs   as ItemPriceCogs_SRetDet
                    , at_sretdetail.ItemAmountCogs  as ItemAmountCogs_SRetDet
                    , at_sretdetail.ItemAmount      as ItemAmount_SRetDet
                    , at_sretdetail.ItemDiscPcnt    as ItemDiscPcnt_SRetDet
                    , at_sretdetail.AmountTax       as AmountTax_SRetDet
                    , at_sretdetail.ItemTotalAmount as ItemTotalAmount_SRetDet
                "); 
        
        $this->db->from("at_salesreturn");
        $this->db->join("am_customer", "at_salesreturn.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_sretdetail", "at_sretdetail.SRetNumber = at_salesreturn.SRetNumber ","inner");       
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesreturn.BranchCode ","inner");       
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_salesreturn.CurrencyCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");
        
        $this->db->where_in('at_sretdetail.ItemCode', $PreData);
        //==== Item jika tidak kosong
        if ($Item != '') {
            $this->db->like('at_sretdetail.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_salesreturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesreturn.TransStatus', array(2));
        }
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesreturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesreturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesreturn.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_salesreturn.SRetdate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesreturn.SRetdate <=', 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_sls_ret . '.SRetdate', '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 sales return by item
    
    //get all sales return by category
    public function pre_get_all_sales_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_category')!=null)?$this->input->post('txt_category'):'';
        $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_sretdetail.ItemCategoryID      as code 
                "); 
        
        $this->db->from("at_salesreturn");
        $this->db->join("am_customer", "at_salesreturn.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_sretdetail", "at_sretdetail.SRetNumber = at_salesreturn.SRetNumber","inner");  
        $this->db->join("am_itemcategory", "am_itemcategory.ItemCategoryID = at_sretdetail.ItemCategoryID","inner"); 
        
        //==== $Category jika tidak kosong
        if ($Category != '') {
            $this->db->like('am_itemcategory.ItemCategoryName', $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_salesreturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesreturn.TransStatus', array(2));
        }
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesreturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesreturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesreturn.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_salesreturn.SRetdate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesreturn.SRetdate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_sretdetail.ItemCategoryID");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_sls_ret . '.SRetdate', '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_sales_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_category')!=null)?$this->input->post('txt_category'):'';
        $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_salesreturn.BranchCode           as BranchCode_SRet
                    , at_salesreturn.CustomerCode       as CustomerCode_SRet
                    , at_salesreturn.SRetdate           as SRetdate_SRet
                    , at_salesreturn.SRetNumber         as SRetNumber_SRet
                    , at_salesreturn.SInvNumber         as SInvNumber_SRet
                    , at_salesreturn.SRetDesc           as SRetDesc_SRet
                    , am_currency.CurrencySymbol
                    , at_salesreturn.CurrencyCode       as CurrencyCode_SRet
                    , at_salesreturn.CurrencyRate       as CurrencyRate_SRet
                    , at_salesreturn.FiscalRate         as FiscalRate_SRet
                    , at_salesreturn.SRetSubTotal       as SRetSubTotal_SRet
                    , at_salesreturn.SRetDiscPcnt       as SRetDiscPcnt_SRet
                    , at_salesreturn.SRetDiscount       as SRetDiscount_SRet
                    , at_salesreturn.SRetTotalAmount    as SRetTotalAmount_SRet
                    , at_salesreturn.TransStatus        as TransStatus_SRet
                    , am_customer.CustomerName          as CustomerName_SRet
                    
                    , at_sretdetail.SRetDetailID    as SRetDetailID_SRetDet
                    , at_sretdetail.SRetDate        as SRetDate_SRetDet
                    , at_sretdetail.SRetNumber      as SRetNumber_SRetDet
                    , at_sretdetail.SInvNumber      as SInvNumber_SRetDet
                    , at_sretdetail.SInvDetailID    as SInvDetailID_SRetDet
                    , at_sretdetail.ItemCode        as ItemCode_SRetDet
                    , at_sretdetail.ItemDesc        as ItemDesc_SRetDet
                    , at_sretdetail.ItemQty         as ItemQty_SRetDet
                    , at_sretdetail.ItemQtyRet      as ItemQtyRet_SRetDet
                    , at_sretdetail.InvoiceQty      as InvoiceQty_SRetDet
                    , at_sretdetail.ItemUnit        as ItemUnit_SRetDet"
//                    .", at_sretdetail.ItemPrice       as ItemPrice_SRetDet"                    
                    .", (at_sretdetail.ItemAmount / at_sretdetail.ItemQty)       as ItemPrice_SRetDet"
                    .", at_sretdetail.ItemPriceCogs   as ItemPriceCogs_SRetDet
                    , at_sretdetail.ItemAmountCogs  as ItemAmountCogs_SRetDet
                    , at_sretdetail.ItemAmount      as ItemAmount_SRetDet
                    , at_sretdetail.ItemDiscPcnt    as ItemDiscPcnt_SRetDet 
                    , at_sretdetail.ItemCategoryID      as ItemCategoryID_SRetDet 
                    , am_itemcategory.ItemCategoryName  as ItemCategoryName_SRetDet 
                "); 
        
        $this->db->from("at_salesreturn");
        $this->db->join("am_customer", "at_salesreturn.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_sretdetail", "at_sretdetail.SRetNumber = at_salesreturn.SRetNumber","inner");  
        $this->db->join("am_itemcategory", "am_itemcategory.ItemCategoryID = at_sretdetail.ItemCategoryID","inner");     
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesreturn.BranchCode ","inner");       
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_salesreturn.CurrencyCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");
        
        $this->db->where_in('at_sretdetail.ItemCategoryID', $PreData);

        //==== $Category jika tidak kosong
        if ($Category != '') {
            $this->db->like('am_itemcategory.ItemCategoryName', $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_salesreturn.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_salesreturn.TransStatus', array(2));
        }
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_salesreturn.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_salesreturn.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_salesreturn.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_salesreturn.SRetdate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_salesreturn.SRetdate <=', 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_sls_ret . '.SRetdate', '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 sales return by category
        
    public function pre_get_all_sales_do_history() {        
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $DONumber       = ($this->input->post('txt_dono')!=null)?$this->input->post('txt_dono'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $Item           = ($this->input->post('txt_item')!=null)?$this->input->post('txt_item'):'';
        $Driver         = ($this->input->post('txt_driver')!=null)?$this->input->post('txt_driver'):'';
        $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_deliveryorder.CustomerCode     as code
        ");         
        $this->db->from("at_deliveryorder");
        $this->db->join("am_customer", "at_deliveryorder.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_dorddetail", "at_dorddetail.DOrdNumber = at_deliveryorder.DOrdNumber","inner");  
        
        //==== $Category jika tidak kosong
        if ($Customer != '') {
            $this->db->like('am_customer.CustomerName', $Customer);
        }//==== End $Category jika tidak kosong
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_deliveryorder.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_deliveryorder.TransStatus', array(2));
        }
        //==== Do Number jika tidak kosong
        if ($DONumber != '') {
            $this->db->like('at_deliveryorder.DOrdNumber', $DONumber);
        }//==== End DO Number jika tidak kosong
        
        //==== item jika tidak kosong
        if ($Item != '') {
            $this->db->like('at_dorddetail.ItemCode', $Item);
            $this->db->or_like('at_dorddetail.ItemDesc', $Item);
        }//==== End item jika tidak kosong
        
        //==== driver jika tidak kosong
        if ($Driver != '') {
            $this->db->like('at_deliveryorder.DriverName', $Driver);
            $this->db->or_like('at_deliveryorder.LicencePlate', $Driver);
        }//==== End driver jika tidak kosong
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_deliveryorder.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_deliveryorder.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_deliveryorder.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_deliveryorder.DOrdDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_deliveryorder.DOrdDate <=', date("Y-m-d", strtotime($DateTo))); //change
            }
        }
        //=====================END cek data tanggal jika tanggal awal lebih dari tanggal akhir=============//
        
        $this->db->group_by("at_deliveryorder.CustomerCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by($this->tbl_do . '.DOrdDate', '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_sales_do_history() {        
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $DONumber       = ($this->input->post('txt_dono')!=null)?$this->input->post('txt_dono'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $Item           = ($this->input->post('txt_item')!=null)?$this->input->post('txt_item'):'';
        $Driver         = ($this->input->post('txt_driver')!=null)?$this->input->post('txt_driver'):'';
        $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_deliveryorder.BranchCode       as BranchCode_DO
                , at_deliveryorder.CustomerCode     as CustomerCode_DO
                , at_deliveryorder.DOrdDate         as DOrdDate_DO
                , at_deliveryorder.DOrdNumber       as DOrdNumber_DO
                , at_deliveryorder.DOrdTotalAmount  as DOrdTotalAmount_DO
                , at_deliveryorder.TransStatus      as TransStatus_DO
                , at_deliveryorder.DriverName       as DriverName_DO
                , at_deliveryorder.LicencePlate     as LicencePlate_DO
                , am_customer.CustomerName          as CustomerName_DO
                
                , at_dorddetail.DOrdDetailID        as DOrdDetailID_DODet
                , at_dorddetail.DOrdNumber          as DOrdNumber_DODet
                , at_dorddetail.ItemCode            as ItemCode_DODet
                , at_dorddetail.ItemDesc            as ItemDesc_DODet
                , at_dorddetail.ItemQty             as ItemQty_DODet"
//                ." , at_dorddetail.ItemQtySI           as ItemQtySI_DODet"
                ." , at_dorddetail.ItemUnit            as ItemUnit_DODet
                , at_dorddetail.ItemPrice           as ItemPrice_DODet
                , at_dorddetail.ItemDiscPcnt        as ItemDiscPcnt_DODet
                , at_dorddetail.ItemPriceCogs       as ItemPriceCogs_DODet
                , at_dorddetail.ItemAmount          as ItemAmount_DODet
                , (SELECT at_sinvdetail.ItemQty FROM at_sinvdetail
                    INNER JOIN at_salesinvoice 
                    ON (at_sinvdetail.SInvNumber = at_salesinvoice.SInvNumber)
                    WHERE at_sinvdetail.ItemCode = at_dorddetail.ItemCode
                    AND at_salesinvoice.DOrdNumber = at_dorddetail.DOrdNumber ) AS ItemQtySI_DODet
                "); 
        
        $this->db->from("at_deliveryorder");
        $this->db->join("am_customer", "at_deliveryorder.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_dorddetail", "at_dorddetail.DOrdNumber = at_deliveryorder.DOrdNumber","inner");          
        $this->db->where_in('at_deliveryorder.CustomerCode', $PreData);
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_deliveryorder.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_deliveryorder.TransStatus', array(2));
        }
        //==== Do Number jika tidak kosong
        if ($DONumber != '') {
            $this->db->like('at_deliveryorder.DOrdNumber', $DONumber);
        }//==== End DO Number jika tidak kosong
        
        //==== $Category jika tidak kosong
        if ($Customer != '') {
            $this->db->like('am_customer.CustomerName', $Customer);
        }//==== End $Category jika tidak kosong
        
        //==== item jika tidak kosong
        if ($Item != '') {
            $this->db->like('at_dorddetail.ItemCode', $Item);
            $this->db->or_like('at_dorddetail.ItemDesc', $Item);
        }//==== End item jika tidak kosong
        
        //==== driver jika tidak kosong
        if ($Driver != '') {
            $this->db->like('at_deliveryorder.DriverName', $Driver);
            $this->db->or_like('at_deliveryorder.LicencePlate', $Driver);
        }//==== End driver jika tidak kosong
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_deliveryorder.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_deliveryorder.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_deliveryorder.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_deliveryorder.DOrdDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_deliveryorder.DOrdDate <=', 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_do . '.DOrdDate', '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;
    }
    public function get_all_sales_do_history_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'):'';
        $Customer       = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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_deliveryorder.BranchCode       as BranchCode_DO
                , at_deliveryorder.CustomerCode     as CustomerCode_DO
                , at_deliveryorder.DOrdDate         as DOrdDate_DO
                , at_deliveryorder.DOrdNumber       as DOrdNumber_DO
                , at_deliveryorder.DOrdTotalAmount  as DOrdTotalAmount_DO
                , at_deliveryorder.TransStatus      as TransStatus_DO
                , am_customer.CustomerName          as CustomerName_DO
                
                , at_dorddetail.DOrdDetailID        as DOrdDetailID_DODet
                , at_dorddetail.DOrdNumber          as DOrdNumber_DODet
                , at_dorddetail.ItemCode            as ItemCode_DODet
                , at_dorddetail.ItemDesc            as ItemDesc_DODet
                , at_dorddetail.ItemQty             as ItemQty_DODet
                , at_dorddetail.ItemQtySI           as ItemQtySI_DODet
                , at_dorddetail.ItemUnit            as ItemUnit_DODet
                , at_dorddetail.ItemPrice           as ItemPrice_DODet
                , at_dorddetail.ItemDiscPcnt        as ItemDiscPcnt_DODet
                , at_dorddetail.ItemPriceCogs       as ItemPriceCogs_DODet
                , at_dorddetail.ItemAmount          as ItemAmount_DODet
                "); 
        
        $this->db->from("at_deliveryorder");
        $this->db->join("am_customer", "at_deliveryorder.CustomerCode = am_customer.CustomerCode","inner");        
        $this->db->join("at_dorddetail", "at_dorddetail.DOrdNumber = at_deliveryorder.DOrdNumber","inner");  
        
        /* ========================jika kondisi tidak sama dengan kosong di find lakukan proses di if================= */
        if ($TransStatus != '') {
            $this->db->where_in('at_deliveryorder.TransStatus', $TransStatus);
        } else {
            /*default adalah 2,6. Finished dan Need Approval*/
            $this->db->where_in('at_deliveryorder.TransStatus', array(2));
        }
        //==== $Category jika tidak kosong
        if ($Customer != '') {
            $this->db->like('am_customer.CustomerName', $Customer);
        }//==== End $Category jika tidak kosong
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_deliveryorder.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_deliveryorder.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_deliveryorder.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_deliveryorder.DOrdDate >=', date("Y-m-d", strtotime($DateFrom))); //change
                $this->db->where('at_deliveryorder.DOrdDate <=', 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_do . '.DOrdDate', '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;
    }
    //============= Function Pendukung ======================================//
    public function dateSqlToIndo($txtDate) {
        $indoDate ="-";
        if($txtDate!=null && $txtDate!=""){
            $arDate     = explode("-", $txtDate);
            $indoDate   = $arDate[2]."-".$arDate[1]."-".$arDate[0];
        }
        return $indoDate;
    }
    
    function get_customer_by_branch($BranchCode){
        $this->db->select('BranchCode,CustomerCode,CustomerName');
        $this->db->from("am_customer");
        $this->db->where('BranchCode', $BranchCode);
        $this->db->where('am_customer.TransStatus', 1);
		
        $this->db->order_by('CustomerCode', 'ASC');                
        $query = $this->db->get();        
        return $query->result();
    }
    function get_customer_by_company($CompanyCode){
        $this->db->select('BranchCode,CustomerCode,CustomerName');
        $this->db->from("am_customer");
        $this->db->where("SUBSTRING(am_customer.BranchCode,1,(LENGTH('".$CompanyCode."')+1))=concat('".$CompanyCode."','-')", null);
        $this->db->where('am_customer.TransStatus', 1);
		
        $this->db->order_by('CustomerCode', 'ASC');                
        $query = $this->db->get();        
        return $query->result();
    }
    
    function get_item_by_branch($BranchCode){
        $this->db->select('am_itemprice.BranchCode,am_item.ItemCode,am_item.ItemDesc');
        $this->db->from("am_item");
        $this->db->join("am_itemprice","am_itemprice.ItemCode=am_item.ItemCode", "inner");
        $this->db->where('am_item.TransStatus', 1);		
        $this->db->where('am_itemprice.BranchCode', $BranchCode);		
        $this->db->order_by('am_item.ItemCode', 'ASC');                
        $query = $this->db->get();        
        return $query->result();
    }
    function get_item_by_company($CompanyCode){
        $this->db->select('am_itemprice.BranchCode,am_item.ItemCode,am_item.ItemDesc');
        $this->db->from("am_item");
        $this->db->join("am_itemprice","am_itemprice.ItemCode=am_item.ItemCode "
                . "AND SUBSTRING(am_itemprice.BranchCode,1,(LENGTH('".$CompanyCode."')+1))=concat('".$CompanyCode."','-')", "inner");
        $this->db->where('am_item.TransStatus', 1);		
        $this->db->order_by('am_item.ItemCode', 'ASC');                
        $query = $this->db->get();        
        return $query->result();
    }
    //============= End Function Pendukung ======================================//
}