<?php

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

class M_index_account_payables extends CI_Model {    
    public $tbl_              = "";
    
    //get all outstanding invoice payables
    public function pre_get_all_outstanding_invoices_payables() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
//        $NoInvoices     = ($this->input->post('txt_noInvoices')!=null)?$this->input->post('txt_noInvoices'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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');
        /* =========================================================================================================== */
  
//CASS BANK = SIGN STSTUS=1, UANGNG MUKA SIGN STSUS=1, BEBAN PEMBULATAN  SIGN =0 (selalu ambil amount curency)
//Balance   = subtype(like "uang muka pembelian%"), sign status 0 dikurangi sign status 1, type(pinv,ppay,pret), transtatus(1,2)
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("
            at_purchaseinvoice.VendorCode as code
        ");
        
        $this->db->from('av_all_transaction_for_report avtrans_1');
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = avtrans_1.Journal_Number ","inner");
        $this->db->join("am_vendor as amvendor_1", "amvendor_1.VendorCode = at_purchaseinvoice.VendorCode","inner");
        $this->db->where_in('avtrans_1.trans_type', array('pinv','ppay'));
        $this->db->where_in('avtrans_1.TransStatus', array(1,2));
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
//        if ($NoInvoices != "" && $NoInvoices != "All" ) {
//            $this->db->like('at_invoicepayment.InvoiceNumber', $NoInvoices);
//        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by VendorName code========================================================= */
        if ($Vendor != "" && $Vendor != null ) {
            $this->db->like('amvendor_1.VendorName', $Vendor);
        } 
        /* ==================================end find by VendorName code===================================================== */
        
        /* ===================================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 =============//
        if ($DateTo != '') {
            $this->db->where('avtrans_1.Journal_Date <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        /* ========================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));
//        }
        
        $this->db->group_by('at_purchaseinvoice.VendorCode');
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('amvendor_1.VendorName', 'ASC');
//            $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);
//        $this->db->group_by('avtrans_1.Journal_Number', 'paydet_1.PInvNumber');
        $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);
        return $result;
    }
    public function get_all_outstanding_invoices_payables() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
//        $NoInvoices     = ($this->input->post('txt_noInvoices')!=null)?$this->input->post('txt_noInvoices'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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');
        /* =========================================================================================================== */
  
//CASS BANK = SIGN STSTUS=1, UANGNG MUKA SIGN STSUS=1, BEBAN PEMBULATAN  SIGN =0 (selalu ambil amount curency)
//Balance   = subtype(like "uang muka pembelian%"), sign status 0 dikurangi sign status 1, type(pinv,ppay,pret), transtatus(1,2)
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("
            IF(
              #TOTAL_AMOUNT, YANG ELSE ADALAH PAYMENT AMOUNT INVOICE
              paydet_1.PInvNumber IS NULL,
              #ini invoice
              (
                IF(
                  avtrans_1.CurrencyCode = 'IDR',
                  #'PInvNumber ada dan IDR'
                  (SELECT 
                    SUM(avtrans_2.Amount_Currency) 
                  FROM
                    av_all_transaction_for_report AS avtrans_2 
                  WHERE avtrans_2.Journal_Number = avtrans_1.Journal_Number 
                    AND avtrans_2.Sign_Status = '0' 
                    AND (
                      avtrans_2.Acc_Type_Name = 'Account Payable' 
                      AND avtrans_2.Sub_Type_Name <> 'Hutang Pajak' 
                      AND avtrans_2.Sub_Type_Name <> 'Hutang PPn Fiscal'
                    )),
                  #'PInvNumber ada dan USD'
                  (SELECT 
                    SUM(avtrans_3.Amount_Currency) 
                  FROM
                    av_all_transaction_for_report AS avtrans_3 
                  WHERE avtrans_3.Journal_Number = avtrans_1.Journal_Number 
                    AND avtrans_3.Sign_Status = '0' 
                    AND (
                      (
                        avtrans_3.Acc_Type_Name = 'Account Payable' 
                        AND avtrans_3.Sub_Type_Name <> 'Hutang Pajak' 
                        AND avtrans_3.Sub_Type_Name <> 'Hutang PPn Fiscal'
                      )
                    ))
                )
              ),
              #ini PAYMENT
              (
                IF(
                  paydet_1.PPayFiscalStatus = '0',
                  #'PAYMENT Invoice' 
                  (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.InvoiceNumber = paydet_1.PInvNumber
                    AND avtrans_2.TransStatus = '2' 
                    AND avtrans_2.Trans_Type = 'PPay' 
                    AND (
                        (
                            avtrans_2.Acc_Type_Name = 'Account Payable'
                            AND avtrans_2.Sign_Status = '1'
                        )
                        OR (
                            avtrans_2.Acc_Type_Name = 'Other Current Liability'
                            AND avtrans_2.Sign_Status = '1'
                        )
                        OR (
                            avtrans_2.Acc_Type_Name = 'Long Term Liability'
                            AND avtrans_2.Sign_Status = '1'
                        )
                    )),
                  #'PAYMENT Bukan Invoice'
                  NULL
                )
              )
            ) AS totalAmount,
            IF(
              #TOTAL_FISCAL
              paydet_1.PInvNumber IS NULL,
              #ini invoice
              IF(
                avtrans_1.CurrencyCode = 'IDR',
                '',
                (SELECT 
                  SUM(avtrans_3.Saldo_Credit) 
                FROM
                  av_all_transaction_for_report AS avtrans_3 
                WHERE avtrans_3.Journal_Number = avtrans_1.Journal_Number 
                  AND avtrans_3.Sign_Status = '0' 
                  AND (
                    avtrans_3.Acc_Type_Name = 'Account Payable' 
                    AND avtrans_3.Sub_Type_Name = 'Hutang PPn Fiscal'
                  ))
              ),
              #ini ELSE FISCAL
              ''
            ) AS totalFiscal,
            IF(
              #PAYMENT_FISCAL
              paydet_1.PPayFiscalStatus = '1',
              #exe kalo payment Fiscal
              (SELECT 
                #SUM(avtrans_2.Amount_Currency) 
                SUM(avtrans_2.Saldo_Credit) 
              FROM
                av_all_transaction_for_report AS avtrans_2 
              WHERE avtrans_2.Journal_Number = avtrans_1.Journal_Number 
                AND avtrans_2.TransStatus = '2' 
                AND avtrans_2.Trans_Type = 'PPay' 
                AND (
                  (
                    avtrans_2.Acc_Type_Name = 'Cash / Bank' 
                    AND avtrans_2.Sign_Status = '1'
                  ) 
                  OR (
                    avtrans_2.Acc_Type_Name = 'Other Current Asset' 
                    AND avtrans_2.Sub_Type_Name  LIKE 'Uang Muka Pembelian%' 
                    AND avtrans_2.Sign_Status = '1'
                  ) 
                  OR (
                    avtrans_2.Acc_Type_Name = 'Other Expense' 
                    AND avtrans_2.Account_Name = 'Beban Pembulatan' 
                    AND avtrans_2.Sign_Status = '1'
                    #AND avtrans_2.Sub_Type_Name = 'Beban Pembulatan' 
                    #AND avtrans_2.Sign_Status = '0'
                  ) 
                  OR (
                    avtrans_2.Acc_Type_Name = 'Revenue' 
                    AND avtrans_2.Sub_Type_Name LIKE 'Retur Pembelian%' 
                    AND avtrans_2.Sign_Status = '1'
                  )
                )),
              #ini Else payment fiscal
              ''
            ) AS PaymentFiscal,
            
                (SELECT SUM(dp.SaldoDPBalance - dp.DPUsed)
              FROM (at_pordetaildp AS dp)
              WHERE dp.VendorCode =  amvendor_1.VendorCode 
              AND (dp.SaldoDPBalance - dp.DPUsed) >  0) AS totalBalances,
              
            paydet_1.PInvNumber,
            paydet_1.PPayFiscalStatus,
            avtrans_1.InvoiceNumber,
            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,
            amvendor_1.VendorCode,
            amvendor_1.VendorName,
            am_currency.CurrencySymbol,
            avtrans_1.CompanyCode,
            at_purchaseinvoice.BranchCode
        ");
        
        $this->db->from('av_all_transaction_for_report avtrans_1');
        $this->db->join("at_ppaydetail AS paydet_1", "paydet_1.PPayNumber = avtrans_1.Journal_Number ","left");
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = avtrans_1.Journal_Number "
                . "|| at_purchaseinvoice.PInvNumber = paydet_1.PInvNumber","inner");
        $this->db->join("at_purchasepayment", "at_purchasepayment.PPayNumber = paydet_1.PPayNumber","left");
        $this->db->join("am_vendor as amvendor_1", "amvendor_1.VendorCode = at_purchaseinvoice.VendorCode  ||  amvendor_1.VendorCode = at_purchasepayment.VendorCode","left");
        $this->db->join("am_currency", "am_currency.CurrencyCode = amvendor_1.CurrencyCode "
                . "and am_currency.CompanyCode = avtrans_1.CompanyCode","left");
        $this->db->where_in('avtrans_1.trans_type', array('pinv','ppay'));
        $this->db->where_in('avtrans_1.TransStatus', array(1,2));
        
        $this->db->where_in('at_purchaseinvoice.VendorCode', $PreData);
//        $this->db->where("(
//            avtrans_1.Acc_Type_Name <> 'Other Current Asset' 
//            AND (
//                avtrans_1.Sub_Type_Name <> 'Uang Muka Pembelian' 
//                OR avtrans_1.Sub_Type_Name <> 'Uang Muka Pembelian Expense'
//            ) 
//            AND avtrans_1.Sign_Status <> 0
//        ) ", null); 
//    #ini PAYMENT
//              (
//                IF(
//                  paydet_1.PPayFiscalStatus = '0',
//                  #'PAYMENT Invoice' 
//                  (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.TransStatus = '2' 
//                    AND avtrans_2.Trans_Type = 'PPay' 
//                    AND (
//                      (
//                        avtrans_2.Acc_Type_Name = 'Cash / Bank' 
//                        AND avtrans_2.Sign_Status = '1'
//                      ) 
//                      OR (
//                        avtrans_2.Acc_Type_Name = 'Other Current Asset' 
//                        AND avtrans_2.Sub_Type_Name LIKE 'Uang Muka Pembelian%' 
//                        AND avtrans_2.Sign_Status = '1'
//                      ) 
//                      OR (
//                        avtrans_2.Acc_Type_Name = 'Other Expense' 
//                        AND avtrans_2.Sub_Type_Name = 'Beban Pembulatan' 
//                        AND avtrans_2.Sign_Status = '0'
//                      ) 
//                      OR (
//                        avtrans_2.Acc_Type_Name = 'Revenue' 
//                        AND avtrans_2.Sub_Type_Name LIKE 'Retur Pembelian%' 
//                        AND avtrans_2.Sign_Status = '1'
//                      )
//                    )),
//                  #'PAYMENT Bukan Invoice'
//                  NULL
//                )
//              )
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
//        if ($NoInvoices != "" && $NoInvoices != "All" ) {
//            $this->db->like('at_invoicepayment.InvoiceNumber', $NoInvoices);
//        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by VendorName code========================================================= */
        if ($Vendor != "" && $Vendor != "All" ) {
            $this->db->like('amvendor_1.VendorName', $Vendor);
        } 
        /* ==================================end find by VendorName code===================================================== */
        
        /* ===================================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 =============//
        if ($DateTo != '') {
            $this->db->where('avtrans_1.Journal_Date <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        /* ========================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));
//        }
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('amvendor_1.VendorName', 'ASC');
//            $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', 'paydet_1.PInvNumber');
        $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);
        return $result;
    }
    public function get_all_outstanding_invoices_payables_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'):'';
        $NoInvoices     = ($this->input->post('txt_noInvoices')!=null)?$this->input->post('txt_noInvoices'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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_invoicepayment.VendorCode                AS VendorCode_IP
                , at_invoicepayment.InvoiceNumber		AS InvoiceNumber_IP
                , at_invoicepayment.InvoiceDate                 AS InvoiceDate_IP
                , at_invoicepayment.InvoiceDate                 AS DueDate_IP
                , at_invoicepayment.InvoiceDate                 AS AgeDueDate_IP
                , at_invoicepayment.InvoiceCurrCode		AS InvoiceCurrCode_IP
                , at_invoicepayment.InvoiceCurrRate		AS InvoiceCurrRate_IP
                , at_invoicepayment.InvoiceFiscalRate           AS InvoiceFiscalRate_IP	
                , at_invoicepayment.InvoiceAmount		AS InvoiceAmount_IP
                , at_invoicepayment.InvoiceType                 AS InvoiceType_IP
                , am_vendor.VendorName                      AS VendorName_IP
                
                , at_purchaseinvoice.PInvNumber		AS PInvNumber_PI
                , at_purchaseinvoice.TermsPaymentID	AS TermsPaymentID_PI
                , at_purchaseinvoice.PInvPPn10		AS PInvPPn10_PI
                , at_purchaseinvoice.PInvTotalAmount	AS PInvTotalAmount_PI
                , at_purchaseinvoice.TransStatus        AS TransStatus_PI
                , am_termspayment.NetDueIn		AS NetDueIn_PI

                , at_ppaydetail.PPayNumber		AS PPayNumber_PPay
                , at_ppaydetail.PPayDetailID            AS PPayDetailID_PPay
                , (at_ppaydetail.PPayRounded + at_ppaydetail.PPayDiscount + at_ppaydetail.PPayDPUsed +at_ppaydetail.PPayAmount)		AS NominalAmountPayDet_PPay                
                , at_purchasepayment.PPayDate              AS PPayDate_PPay
                , at_purchasepayment.TransStatus           AS TransStatus_PPay

                , at_pretdetail.PRetNumber		AS PRetNumber_PRet
                , at_pretdetail.PRetDetailID            AS PRetDetailID_PRet
                , at_pretdetail.ItemTotalAmount         AS ItemTotalAmount_PRet
                , at_purchasereturn.PRetDate		AS PRetDate_PRet
                , at_purchasereturn.TransStatus		AS TransStatus_PRet"
                );
        
        $this->db->from('at_invoicepayment');
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_invoicepayment.VendorCode","inner");
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = at_invoicepayment.InvoiceNumber","left");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_purchaseinvoice.TermsPaymentID","left");
        $this->db->join("at_ppaydetail", "at_ppaydetail.PInvNumber = at_purchaseinvoice.PInvNumber","left");
        $this->db->join("at_purchasepayment", "at_purchasepayment.PPayNumber = at_ppaydetail.PPayNumber "
                . "AND (at_purchasepayment.TransStatus='2')","left");
        $this->db->join("at_pretdetail", "at_pretdetail.PInvNumber = at_purchaseinvoice.PInvNumber","left");
        $this->db->join("at_purchasereturn", "at_purchasereturn.PRetNumber = at_pretdetail.PRetNumber AND at_purchasereturn.TransStatus='2'","left");
        $this->db->where("(at_invoicepayment.InvoiceType=0 OR at_invoicepayment.InvoiceType=1)", null,false);
        $this->db->where("at_invoicepayment.InvoiceOwing>0", null,false);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('at_invoicepayment.InvoiceNumber', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by VendorName code========================================================= */
        if ($Vendor != "" && $Vendor != "All" ) {
            $this->db->like('am_vendor.VendorName', $Vendor);
        } 
        /* ==================================end find by VendorName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicepayment.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicepayment.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicepayment.InvoiceDate', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        
        $query = $this->db->get();
        //echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        return $result;
    }
    
    public function pre_get_all_detail_amount_invoices_payables_outstanding() {
        $VendorCode     = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        
        $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("
            avtrans_1.Journal_Number as code
        ");
        
        $this->db->from('av_all_transaction_for_report avtrans_1');
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = avtrans_1.Journal_Number","inner");
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_purchaseinvoice.VendorCode","inner");
        $this->db->join("am_branch", "am_vendor.BranchCode=am_branch.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->where_in("avtrans_1.trans_type", array('pinv'));
        $this->db->where_in("avtrans_1.TransStatus", array(1));
        $this->db->where("am_vendor.VendorCode", $VendorCode);
        //==========================================================================================================//
     
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('avtrans_1.Journal_Date <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        $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();
//        echo "<hr>".$this->db->last_query()."<hr>";
        $this->db->_protect_identifiers = TRUE;
        $result = $query->result();
        return $result;
    }
    public function get_all_detail_amount_invoices_payables_outstanding() {
        $VendorCode     = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $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(avtrans_1.CurrencyCode = 'IDR',
                #'PInvNumber ada dan IDR'
                (SELECT 
                  SUM(avtrans_2.Amount_Currency) 
                FROM
                  av_all_transaction_for_report AS avtrans_2 
                WHERE avtrans_2.Journal_Number = avtrans_1.Journal_Number 
                  AND avtrans_2.Sign_Status = '0' 
                  AND (
                    avtrans_2.Acc_Type_Name = 'Account Payable' 
                    AND avtrans_2.Sub_Type_Name <> 'Hutang Pajak' 
                    AND avtrans_2.Sub_Type_Name <> 'Hutang PPn Fiscal'
                  )),
                #'PInvNumber ada dan USD'
                (SELECT 
                  SUM(avtrans_3.Amount_Currency) 
                FROM
                  av_all_transaction_for_report AS avtrans_3 
                WHERE avtrans_3.Journal_Number = avtrans_1.Journal_Number 
                  AND avtrans_3.Sign_Status = '0' 
                  AND (
                    (
                      avtrans_3.Acc_Type_Name = 'Account Payable' 
                      AND avtrans_3.Sub_Type_Name <> 'Hutang Pajak' 
                      AND avtrans_3.Sub_Type_Name <> 'Hutang PPn Fiscal'
                    )
                  ))
              )
            ) AS totalAmount,
          
            IF(#TOTAL_FISCAL
              avtrans_1.CurrencyCode = 'IDR',
              '0',
              (SELECT 
                SUM(
                  avtrans_3.Saldo_Credit
                )
              FROM
                av_all_transaction_for_report AS avtrans_3 
              WHERE avtrans_3.Journal_Number = avtrans_1.Journal_Number 
                AND avtrans_3.Sign_Status = '0' 
                AND (
                  avtrans_3.Acc_Type_Name = 'Account Payable' 
                  AND avtrans_3.Sub_Type_Name = 'Hutang PPn Fiscal'
                )
              )
            ) AS totalFiscal, 
              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.Sign_Status,
              avtrans_1.TransStatus,
              am_vendor.VendorCode,
              am_vendor.VendorName,
              am_branch.BranchName,
              am_company.CompanyName,
              am_currency.CurrencySymbol
        ");
        
        $this->db->from('av_all_transaction_for_report avtrans_1');
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = avtrans_1.Journal_Number","inner");
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_purchaseinvoice.VendorCode","inner");
        $this->db->join("am_branch", "am_vendor.BranchCode=am_branch.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("am_currency", "am_currency.CurrencyCode = am_vendor.CurrencyCode "
                . "and am_currency.CompanyCode = avtrans_1.CompanyCode","left");
        $this->db->where_in("avtrans_1.trans_type", array('pinv'));
        $this->db->where_in("avtrans_1.TransStatus", array(1));
        $this->db->where("am_vendor.VendorCode", $VendorCode);
        
        $this->db->where_in('avtrans_1.Journal_Number', $PreData);
        //==========================================================================================================//
     
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('avtrans_1.Journal_Date <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        $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========================================================== */        
        
        $query = $this->db->get();
//        echo "<hr>".$this->db->last_query()."<hr>";
        $this->db->_protect_identifiers = TRUE;
        $result = $query->result();
        return $result;
    }
    
    public function pre_get_all_detail_payment_invoices_payables_outstanding() {
        $VendorCode     = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $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_purchaseinvoice.PInvNumber as code
        ");
        $this->db->from('at_purchaseinvoice');
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_purchaseinvoice.VendorCode","inner");
        $this->db->join("am_branch", "am_vendor.BranchCode=am_branch.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("at_ppaydetail", "at_purchaseinvoice.PInvNumber = at_ppaydetail.PInvNumber","left");
        $this->db->join("at_purchasepayment", "at_purchasepayment.PPayNumber = at_ppaydetail.PPayNumber AND (at_purchasepayment.TransStatus = '1' OR at_purchasepayment.TransStatus = '2') ","left");
//       $this->db->join("at_purchaseorder", "at_purchaseorder.POrdNumber = at_ppaydetail.PInvNumber AND (at_purchaseorder.TransStatus = '1' OR at_purchaseorder.TransStatus = '2') ","left");
        $this->db->where_in("at_purchaseinvoice.TransStatus", array(1));
        $this->db->where("am_vendor.VendorCode", $VendorCode);
        //==========================================================================================================//
     
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_purchaseinvoice.PInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        $this->db->group_by("at_purchaseinvoice.PInvNumber");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchaseinvoice.PInvDate', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        $this->db->limit($Pages['limit'], $limitStart);
        
        $query = $this->db->get();
//        echo "<hr>".$this->db->last_query()."<hr>";
        $this->db->_protect_identifiers = TRUE;
        $result = $query->result();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        return $result;
    }
    public function get_all_detail_payment_invoices_payables_outstanding() {
        $VendorCode     = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $DateTo         = ($this->input->post('txt_DateTo')!=null)?$this->input->post('txt_DateTo'):'';
        $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("
            am_branch.BranchName,
            am_company.CompanyName,
            am_vendor.VendorCode,
            am_vendor.VendorName,
            am_vendor.CurrencyCode,
            at_purchaseinvoice.PInvNumber,
            at_purchaseinvoice.PInvDate,
            IF(am_vendor.CurrencyCode<>'IDR',
                (at_purchaseinvoice.PInvTotalInvoice - at_purchaseinvoice.PInvPPn10),
                at_purchaseinvoice.PInvTotalInvoice
            )             
             as PInvTotalAmount,
            IF(am_vendor.CurrencyCode = 'IDR',
                0
                ,
                (at_purchaseinvoice.PInvPPn10 * at_purchaseinvoice.FiscalRate)                
            )AS fiscalInvoice,
            at_purchaseinvoice.TransStatus,
            at_purchasepayment.PPayDate,
            at_purchasepayment.PPayNumber,
            at_purchasepayment.BankCurrCode,
            at_ppaydetail.PPayTotalAmnt,
            at_ppaydetail.PPayCurrency,
            at_ppaydetail.PPayCurrRate,
            at_ppaydetail.PPayFiscalStatus,
            at_ppaydetail.POrdNumber,
            at_purchasepayment.TransStatus AS TransStatus_Pay,
            am_currency.CurrencySymbol
        ");
        /*
         * IF(am_vendor.CurrencyCode<>'IDR',
                IF(at_purchasepayment.BankCurrCode<>'IDR',
                    at_ppaydetail.PPayTotalAmnt
                    ,
                    at_ppaydetail.PPayTotalAmnt / at_ppaydetail.PPayCurrRate
                )
               ,               
                IF(at_purchasepayment.BankCurrCode<>'IDR',
                    at_ppaydetail.PPayTotalAmnt * at_ppaydetail.PPayCurrRate
                    ,
                    at_ppaydetail.PPayTotalAmnt 
                )
            )PPayTotalAmnt,
         */
        $this->db->from('at_purchaseinvoice');
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_purchaseinvoice.VendorCode","inner");
        $this->db->join("am_branch", "am_vendor.BranchCode=am_branch.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("at_ppaydetail", "at_purchaseinvoice.PInvNumber = at_ppaydetail.PInvNumber","left");
        $this->db->join("at_purchasepayment", "at_purchasepayment.PPayNumber = at_ppaydetail.PPayNumber AND (at_purchasepayment.TransStatus = '1' OR at_purchasepayment.TransStatus = '2') ","left");
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_purchaseinvoice.CurrencyCode "
                . "and am_currency.CompanyCode = am_branch.CompanyCode","inner");
//       $this->db->join("at_purchaseorder", "at_purchaseorder.POrdNumber = at_ppaydetail.PInvNumber AND (at_purchaseorder.TransStatus = '1' OR at_purchaseorder.TransStatus = '2') ","left");
        $this->db->where_in("at_purchaseinvoice.TransStatus", array(1));
        $this->db->where("am_vendor.VendorCode", $VendorCode);
        
        $this->db->where_in('at_purchaseinvoice.PInvNumber', $PreData);
        //==========================================================================================================//
     
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_purchaseinvoice.PInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_purchaseinvoice.PInvDate', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        
        $query = $this->db->get();
//        echo "<hr>".$this->db->last_query()."<hr>";
        $this->db->_protect_identifiers = TRUE;
        $result = $query->result();
        return $result;
    }
    //end get all outstanding invoice payables
    
    //get all aging receivable detail
    public function pre_get_all_aging_pay_detail() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $NoInvoices     = ($this->input->post('txt_noInvoices')!=null)?$this->input->post('txt_noInvoices'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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_invoicepayment.VendorCode                AS code
                "
                );
        
        $this->db->from('at_invoicepayment');
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_invoicepayment.VendorCode","inner");
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = at_invoicepayment.InvoiceNumber","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_purchaseinvoice.TermsPaymentID","inner");
        $this->db->where("at_invoicepayment.InvoiceType", 0);
        $this->db->where("at_invoicepayment.InvoiceOwing>0", null,false);
        $this->db->where("at_invoicepayment.PPnFiscalStatus", 0);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('at_invoicepayment.InvoiceNumber', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by VendorName code========================================================= */
        if ($Vendor != "" && $Vendor != "All" ) {
            $this->db->like('am_vendor.VendorName', $Vendor);
        } 
        /* ==================================end find by VendorName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicepayment.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicepayment.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by("at_invoicepayment.VendorCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicepayment.InvoiceDate', '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_aging_pay_detail() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $NoInvoices     = ($this->input->post('txt_noInvoices')!=null)?$this->input->post('txt_noInvoices'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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_invoicepayment.VendorCode                AS VendorCode_IP
                , at_invoicepayment.InvoiceNumber		AS InvoiceNumber_IP
                , at_invoicepayment.InvoiceDate                 AS InvoiceDate_IP
                , at_invoicepayment.InvoiceDate                 AS DueDate_IP
                , at_invoicepayment.InvoiceDate                 AS AgeDueDate_IP
                , at_invoicepayment.InvoiceCurrCode		AS InvoiceCurrCode_IP
                , at_invoicepayment.InvoiceCurrRate		AS InvoiceCurrRate_IP
                , at_invoicepayment.InvoiceFiscalRate           AS InvoiceFiscalRate_IP	
                , sum(at_invoicepayment.InvoiceAmount)		AS InvoiceAmount_IP 
                , sum(at_invoicepayment.PaymentAmount) 
                    + sum(at_invoicepayment.PaymentDiscount) 
                    + sum(at_invoicepayment.PaymentDPUsed)
                    + sum(at_invoicepayment.PaymentRounded)	AS PaymentTotal_IP  
                , sum(at_invoicepayment.InvoiceOwing)		AS InvoiceOwing_IP  
                , at_invoicepayment.InvoiceType                 AS InvoiceType_IP
                , am_vendor.VendorName                      AS VendorName_IP
                , am_termspayment.NetDueIn                    AS NetDueIn_PI
                , am_currency.CurrencySymbol
                "
                );
        
        $this->db->from('at_invoicepayment');
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_invoicepayment.VendorCode","inner");
        $this->db->join("at_purchaseinvoice", "at_purchaseinvoice.PInvNumber = at_invoicepayment.InvoiceNumber "
                . " AND at_purchaseinvoice.TransStatus = '1' ","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_purchaseinvoice.TermsPaymentID","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = at_invoicepayment.BranchCode","inner");
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_invoicepayment.InvoiceCurrCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");
        
        $this->db->where_in('at_invoicepayment.VendorCode', $PreData);
        $this->db->where("at_invoicepayment.InvoiceType", 0);
        $this->db->where("at_invoicepayment.PPnFiscalStatus", 0);
        $this->db->where("at_invoicepayment.InvoiceOwing>0", null,false);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('at_invoicepayment.InvoiceNumber', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by VendorName code========================================================= */
        if ($Vendor != "" && $Vendor != "All" ) {
            $this->db->like('am_vendor.VendorName', $Vendor);
        } 
        /* ==================================end find by VendorName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicepayment.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicepayment.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by("at_invoicepayment.InvoiceNumber");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicepayment.InvoiceDate', '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 aging receivable detail
    
    //get all aging receivable detail
    public function pre_get_all_vendor_list() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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_invoicepayment.VendorCode          AS code
            ");
        
        $this->db->from('at_invoicepayment');
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_invoicepayment.VendorCode","inner");
        $this->db->where("at_invoicepayment.InvoiceType", 0);
        $this->db->where("at_invoicepayment.InvoiceOwing>0", null,false);
        //==========================================================================================================//
     
        /* ===================================find by VendorName code========================================================= */
        if ($Vendor != "" && $Vendor != "All" ) {
            $this->db->like('am_vendor.VendorName', $Vendor);
        } 
        /* ==================================end find by VendorName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicepayment.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicepayment.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by("at_invoicepayment.VendorCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicepayment.InvoiceDate', '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_vendor_list() {
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Vendor       = ($this->input->post('txt_vendor')!=null)?$this->input->post('txt_vendor'):'';
        $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_invoicepayment.VendorCode          AS VendorCode_IP
                , at_invoicepayment.InvoiceCurrCode     AS InvoiceCurrCode_IP
                , at_invoicepayment.InvoiceCurrRate     AS InvoiceCurrRate_IP
                , at_invoicepayment.InvoiceType         AS InvoiceType_IP
                , am_vendor.VendorName                  AS VendorName_IP
                , am_vendor.OutStanding                 AS OutStanding_IP
                , am_vendor.OutStandingFiscal           AS OutStandingFiscal_IP
                , am_currency.CurrencySymbol
                "
                );
        
        $this->db->from('at_invoicepayment');
        $this->db->join("am_vendor", "am_vendor.VendorCode = at_invoicepayment.VendorCode","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = at_invoicepayment.BranchCode","inner");
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_invoicepayment.InvoiceCurrCode"
                . " AND am_currency.CompanyCode=am_branch.CompanyCode","inner");
        $this->db->where("at_invoicepayment.InvoiceType", 0);
        $this->db->where("at_invoicepayment.InvoiceOwing>0", null,false);
        $this->db->where_in('at_invoicepayment.VendorCode ', $PreData);
        //==========================================================================================================//
     
        /* ===================================find by VendorName code========================================================= */
        if ($Vendor != "" && $Vendor != "All" ) {
            $this->db->like('am_vendor.VendorName', $Vendor);
        } 
        /* ==================================end find by VendorName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicepayment.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicepayment.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicepayment.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by("at_invoicepayment.VendorCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicepayment.InvoiceDate', '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 aging receivable detail
}

?>
