<?php

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

class M_index_account_receivables extends CI_Model {    
    public $tbl_              = "";
    
    //get all outstanding invoice
    public function pre_get_all_outstanding_invoices() {    
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $NoInvoices     = ($this->input->post('txt_noInvoices')!=null)?$this->input->post('txt_noInvoices'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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(" 
            amcustomer_1.CustomerCode
        ");
        
        $this->db->from('av_all_transaction_for_report AS avtrans_1');
        $this->db->join("at_srecdetail AS recdet_1", "recdet_1.SRecNumber = avtrans_1.Journal_Number ","left");
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = avtrans_1.Journal_Number"
                . " or at_salesinvoice.SInvNumber = recdet_1.SInvNumber","inner");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = recdet_1.SRecNumber","left");
        $this->db->join("am_customer AS amcustomer_1", "amcustomer_1.CustomerCode = at_salesinvoice.CustomerCode"
                . " or amcustomer_1.CustomerCode = at_salesreceipt.CustomerCode","left");
        
        $this->db->where_in("avtrans_1.trans_type", array('SInv', 'SRec'));
        $this->db->where_in("avtrans_1.TransStatus", array(1, 2));
        //==========================================================================================================//
     
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != null ) {
            $this->db->like('amcustomer_1.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName code===================================================== */
        
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('avtrans_1.Journal_Number', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber 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 ============//
           
//        $this->db->group_by("avtrans_1.Journal_Number");
        $this->db->group_by("amcustomer_1.CustomerCode");
        /* ==============================================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");  
        $this->db->limit($Pages['limit'], $limitStart);
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
//        echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        return $result;
    }
    public function get_all_outstanding_invoices() {      
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $NoInvoices     = ($this->input->post('txt_noInvoices')!=null)?$this->input->post('txt_noInvoices'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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(
              #TOTAL_AMOUNT, YANG ELSE ADALAH PAYMENT AMOUNT INVOICE
              recdet_1.SInvNumber IS NULL,
              #ini invoice
              (
                IF(
                  avtrans_1.CurrencyCode = 'IDR',
                  #'SInvNumber 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 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'
                    )),
                  #'SInvNumber 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 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'
                      )
                    ))
                )
              ),
              #ini PAYMENT
              (
                IF(
                  recdet_1.FiscalStatus = '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 = 'SRec' 
                    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 Penjualan%' 
                        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 Penjualan%' 
                        AND avtrans_2.Sign_Status = '1'
                      )
                    OR (
                      avtrans_2.Acc_Type_Name = 'Account Receivable' 
                      AND avtrans_2.Sub_Type_Name LIKE 'Piutang%' 
                      AND avtrans_2.Sign_Status = '1'
                    )
                    )),
                  #'PAYMENT Bukan Invoice'
                  NULL
                )
              )
            ) AS totalAmount,
            IF(
              #TOTAL_FISCAL
              recdet_1.SInvNumber IS NULL,
              #ini invoice
              IF(
                avtrans_1.CurrencyCode = 'IDR',
                '',
                (SELECT 
                  #SUM(avtrans_3.Saldo_Credit) 
                  SUM(avtrans_3.Saldo_Debet) 
                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'
                  ))
              ),
              #ini ELSE FISCAL
              ''
            ) AS totalFiscal,
            IF(
              #PAYMENT_FISCAL
              recdet_1.FiscalStatus = '1',
              #exe kalo payment Fiscal
              (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 = 'SRec' 
                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 Penjualan%' 
                    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 Penjualan%' 
                    AND avtrans_2.Sign_Status = '1'
                  )
                )),
              #ini Else payment fiscal
              ''
            ) AS PaymentFiscal,
            
                (SELECT SUM(dp.SaldoDPBalance - dp.DPUsed)
              FROM (at_sorddetaildp AS dp)
              WHERE dp.CustomerCode =  amcustomer_1.CustomerCode 
              AND (dp.SaldoDPBalance - dp.DPUsed) >  0) AS totalBalances,
            
            recdet_1.SInvNumber,
            recdet_1.FiscalStatus,
            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,
            amcustomer_1.CustomerCode,
            amcustomer_1.CustomerName ,
            am_currency.CurrencySymbol,
            avtrans_1.CompanyCode,
            at_salesinvoice.BranchCode"
        );
        
        $this->db->from('av_all_transaction_for_report AS avtrans_1');
        $this->db->join("at_srecdetail AS recdet_1", "recdet_1.SRecNumber = avtrans_1.Journal_Number ","left");
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = avtrans_1.Journal_Number"
                . " or at_salesinvoice.SInvNumber = recdet_1.SInvNumber","inner");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = recdet_1.SRecNumber","left");
        $this->db->join("am_customer AS amcustomer_1", "amcustomer_1.CustomerCode = at_salesinvoice.CustomerCode"
                . " or amcustomer_1.CustomerCode = at_salesreceipt.CustomerCode","left");
        $this->db->join("am_currency", "am_currency.CurrencyCode = amcustomer_1.CurrencyCode "
                . "and am_currency.CompanyCode = avtrans_1.CompanyCode","left");
        
        $this->db->where_in("avtrans_1.trans_type", array('SInv', 'SRec'));
        $this->db->where_in("avtrans_1.TransStatus", array(1, 2));
        
        $this->db->where_in('amcustomer_1.CustomerCode', $PreData);
        //==========================================================================================================//
     
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != null ) {
            $this->db->like('amcustomer_1.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName code===================================================== */
        
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('avtrans_1.Journal_Number', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber 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 ============//
           
        $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->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();
        return $result;
    }
    
    public function get_all_outstanding_invoices_LAMA2() {
        
        /* =================================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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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->_protect_identifiers = FALSE;
        $this->db->select(" 
            IF(
              #TOTAL_AMOUNT, YANG ELSE ADALAH PAYMENT AMOUNT INVOICE
              recdet_1.SInvNumber IS NULL,
              #ini invoice
              (
                IF(
                  avtrans_1.CurrencyCode = 'IDR',
                  #'SInvNumber 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 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'
                    )),
                  #'SInvNumber 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 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'
                      )
                    ))
                )
              ),
              #ini PAYMENT
              (
                IF(
                  recdet_1.FiscalStatus = '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 = 'SRec' 
                    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 Penjualan%' 
                        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 Penjualan%' 
                        AND avtrans_2.Sign_Status = '1'
                      )
                    OR (
                      avtrans_2.Acc_Type_Name = 'Account Receivable' 
                      AND avtrans_2.Sub_Type_Name LIKE 'Piutang%' 
                      AND avtrans_2.Sign_Status = '1'
                    )
                    )),
                  #'PAYMENT Bukan Invoice'
                  NULL
                )
              )
            ) AS totalAmount,
            IF(
              #TOTAL_FISCAL
              recdet_1.SInvNumber IS NULL,
              #ini invoice
              IF(
                avtrans_1.CurrencyCode = 'IDR',
                '',
                (SELECT 
                  #SUM(avtrans_3.Saldo_Credit) 
                  SUM(avtrans_3.Saldo_Debet) 
                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'
                  ))
              ),
              #ini ELSE FISCAL
              ''
            ) AS totalFiscal,
            IF(
              #PAYMENT_FISCAL
              recdet_1.FiscalStatus = '1',
              #exe kalo payment Fiscal
              (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 = 'SRec' 
                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 Penjualan%' 
                    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 Penjualan%' 
                    AND avtrans_2.Sign_Status = '1'
                  )
                )),
              #ini Else payment fiscal
              ''
            ) AS PaymentFiscal,
            
                (SELECT SUM(dp.SaldoDPBalance - dp.DPUsed)
              FROM (at_sorddetaildp AS dp)
              WHERE dp.CustomerCode =  amcustomer_1.CustomerCode 
              AND (dp.SaldoDPBalance - dp.DPUsed) >  0) AS totalBalances,

            (#TOTAL BALANCE DENGAN SIGN_STATUS = 0
            SELECT 
              SUM(avtrans_3.Amount_Currency) 
            FROM
              av_all_transaction_for_report avtrans_3 
              LEFT JOIN at_salesinvoice 
                ON (
                  at_salesinvoice.SInvNumber = avtrans_3.Journal_Number 
                  AND (
                    at_salesinvoice.TransStatus = '1' 
                    OR at_salesinvoice.TransStatus = '2'
                  )
                ) 
              LEFT JOIN at_salesorder 
                ON (
                  at_salesorder.SOrdNumber = avtrans_3.Journal_Number 
                  AND (
                    at_salesorder.TransStatus = '1' 
                    OR at_salesorder.TransStatus = '2'
                  )
                ) 
              LEFT JOIN at_salesreceipt 
                ON (
                  at_salesreceipt.SRecNumber = avtrans_3.Journal_Number 
                  AND (
                    at_salesreceipt.TransStatus = '2'
                  )
                ) 
              LEFT JOIN at_salesreturn 
                ON (
                  at_salesreturn.SRetNumber = avtrans_3.Journal_Number 
                  AND (at_salesreturn.TransStatus = '2')
                ) 
              LEFT JOIN am_customer amcustomer_2 
                ON (
                  amcustomer_2.CustomerCode = at_salesinvoice.CustomerCode 
                  OR amcustomer_2.CustomerCode = at_salesorder.CustomerCode 
                  OR amcustomer_2.CustomerCode = at_salesreceipt.CustomerCode 
                  OR amcustomer_2.CustomerCode = at_salesreturn.CustomerCode
                ) 
            WHERE amcustomer_2.CustomerCode = amcustomer_1.CustomerCode 
                AND avtrans_3.Sub_Type_Name LIKE 'Kewajiban Lancar Lainnya%' 
                AND avtrans_3.Account_Name LIKE 'Uang Muka Penjualan%' 
              AND avtrans_3.Trans_Type IN ('SInv', 'SRec', 'SRet') 
              AND avtrans_3.TransStatus IN ('1', '2') 
              AND avtrans_3.Sign_Status = '0'
              GROUP BY amcustomer_2.CustomerCode ) AS totalBalances_0,
            (#TOTAL BALANCE DENGAN SIGN_STATUS = 1
            SELECT 
              SUM(avtrans_3.Amount_Currency) 
            FROM
              av_all_transaction_for_report avtrans_3 
              LEFT JOIN at_salesinvoice 
                ON (
                  at_salesinvoice.SInvNumber = avtrans_3.Journal_Number 
                  AND (
                    at_salesinvoice.TransStatus = '1' 
                    OR at_salesinvoice.TransStatus = '2'
                  )
                ) 
              LEFT JOIN at_salesorder 
                ON (
                  at_salesorder.SOrdNumber = avtrans_3.Journal_Number 
                  AND (
                    at_salesorder.TransStatus = '1' 
                    OR at_salesorder.TransStatus = '2'
                  )
                ) 
              LEFT JOIN at_salesreceipt 
                ON (
                  at_salesreceipt.SRecNumber = avtrans_3.Journal_Number 
                  AND (
                    at_salesreceipt.TransStatus = '2'
                  )
                ) 
              LEFT JOIN at_salesreturn 
                ON (
                  at_salesreturn.SRetNumber = avtrans_3.Journal_Number 
                  AND (at_salesreturn.TransStatus = '2')
                ) 
              LEFT JOIN am_customer amcustomer_2 
                ON (
                  amcustomer_2.CustomerCode = at_salesinvoice.CustomerCode 
                  OR amcustomer_2.CustomerCode = at_salesorder.CustomerCode 
                  OR amcustomer_2.CustomerCode = at_salesreceipt.CustomerCode 
                  OR amcustomer_2.CustomerCode = at_salesreturn.CustomerCode
                ) 
            WHERE amcustomer_2.CustomerCode = amcustomer_1.CustomerCode 
                AND avtrans_3.Sub_Type_Name LIKE 'Kewajiban Lancar Lainnya%' 
                AND avtrans_3.Account_Name LIKE 'Uang Muka Penjualan%' 
              AND avtrans_3.Trans_Type IN ('SInv', 'SRec', 'SRet') 
              AND avtrans_3.TransStatus IN ('1', '2') 
              AND avtrans_3.Sign_Status = '1'
              GROUP BY amcustomer_2.CustomerCode ) AS totalBalances_1,
            recdet_1.SInvNumber,
            recdet_1.FiscalStatus,
            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,
            amcustomer_1.CustomerCode,
            amcustomer_1.CustomerName 
            ,am_currency.CurrencySymbol"
        );
        
        $this->db->from('av_all_transaction_for_report AS avtrans_1');
        $this->db->join("at_srecdetail AS recdet_1", "recdet_1.SRecNumber = avtrans_1.Journal_Number ","left");
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = avtrans_1.Journal_Number"
                . " or at_salesinvoice.SInvNumber = recdet_1.SInvNumber","inner");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = recdet_1.SRecNumber","left");
        $this->db->join("am_customer AS amcustomer_1", "amcustomer_1.CustomerCode = at_salesinvoice.CustomerCode"
                . " or amcustomer_1.CustomerCode = at_salesreceipt.CustomerCode","left");
        $this->db->join("am_currency", "am_currency.CurrencyCode = amcustomer_1.CurrencyCode "
                . "and am_currency.CompanyCode = avtrans_1.CompanyCode","left");
        
        $this->db->where_in("avtrans_1.trans_type", array('SInv', 'SRec'));
        $this->db->where_in("avtrans_1.TransStatus", array(1, 2));
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('avtrans_1.Journal_Number', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('amcustomer_1.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName 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 ============//
           
        $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->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);
        return $result;
    }
    public function get_all_outstanding_invoices_LAMA() {
        
        /* =================================post dari find di kiri==================================================== */
        //$param  = $this->input->post('param');
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $NoInvoices     = ($this->input->post('txt_noInvoices')!=null)?$this->input->post('txt_noInvoices'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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_invoicereceipt.CustomerCode                AS CustomerCode_IR
                , at_invoicereceipt.InvoiceNumber		AS InvoiceNumber_IR
                , at_invoicereceipt.InvoiceDate                 AS InvoiceDate_IR
                , at_invoicereceipt.InvoiceDate                 AS DueDate_IR
                , at_invoicereceipt.InvoiceDate                 AS AgeDueDate_IR
                , at_invoicereceipt.InvoiceCurrCode		AS InvoiceCurrCode_IR
                , at_invoicereceipt.InvoiceCurrRate		AS InvoiceCurrRate_IR
                , at_invoicereceipt.InvoiceFiscalRate           AS InvoiceFiscalRate_IR	
                , at_invoicereceipt.InvoiceAmount		AS InvoiceAmount_IR
                , at_invoicereceipt.InvoiceType                 AS InvoiceType_IR
                , at_invoicereceipt.DPStatus                    AS DPStatus_IR
                , am_customer.CustomerName                      AS CustomerName_IR
                
                , at_salesinvoice.SInvNumber		AS SInvNumber_SI
                , at_salesinvoice.TermsPaymentID	AS TermsPaymentID_SI
                , at_salesinvoice.SInvPPNOut		AS SInvPPNOut_SI
                , at_salesinvoice.SInvTotalAmount	AS SInvTotalAmount_SI
                , at_salesinvoice.TransStatus           AS TransStatus_SI
                , am_termspayment.NetDueIn		AS NetDueIn_SI

                , at_srecdetail.SrecNumber		AS SrecNumber_SRec
                , at_srecdetail.SRecDetailID            AS SRecDetailID_SRec
                , (at_srecdetail.SrecRounded + at_srecdetail.SRecDiscount + at_srecdetail.SRecDPUsed +at_srecdetail.SRecAmount)		AS NominalAmountRecDet_SRec                
                , at_salesreceipt.SRecDate              AS SRecDate_SRec
                , at_salesreceipt.TransStatus           AS TransStatus_SRec

                , at_sretdetail.SRetNumber		AS SRetNumber_SRet
                , at_sretdetail.SRetDetailID            AS SRetDetailID_SRet
                , at_sretdetail.SRetDate		AS SRetDate_SRet
                , at_sretdetail.ItemTotalAmount         AS ItemTotalAmount_SRet
                , at_salesreturn.TransStatus		AS TransStatus_SRet"
                );
        
        $this->db->from('at_invoicereceipt');
        $this->db->join("am_customer", "am_customer.CustomerCode = at_invoicereceipt.CustomerCode","inner");
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = at_invoicereceipt.InvoiceNumber","left");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","left");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","left");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . "AND (at_salesreceipt.TransStatus='2')","left");
        $this->db->join("at_sretdetail", "at_sretdetail.SInvNumber = at_salesinvoice.SInvNumber","left");
        $this->db->join("at_salesreturn", "at_salesreturn.SRetNumber = at_sretdetail.SRetNumber AND at_salesreturn.TransStatus='2'","left");
        $this->db->where("(at_invoicereceipt.InvoiceType=0 OR at_invoicereceipt.InvoiceType=1)", null,false);
        $this->db->where("at_invoicereceipt.InvoiceOwing>0", null,false);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('at_invoicereceipt.InvoiceNumber', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicereceipt.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicereceipt.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicereceipt.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicereceipt.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicereceipt.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 outstanding invoice
    
    //get all aging receivable detail
    public function pre_get_all_aging_recv_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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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_invoicereceipt.CustomerCode                AS code
                ");
        
        $this->db->from('at_invoicereceipt');
        $this->db->join("am_customer", "am_customer.CustomerCode = at_invoicereceipt.CustomerCode","inner");
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = at_invoicereceipt.InvoiceNumber","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->where("at_invoicereceipt.InvoiceType", 0);
        $this->db->where("at_invoicereceipt.DPStatus", 0);
        $this->db->where("at_invoicereceipt.InvoiceOwing>0", null,false);
        $this->db->where("at_invoicereceipt.PPnFiscalStatus", 0);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != null ) {
            $this->db->like('at_invoicereceipt.InvoiceNumber', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != null ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicereceipt.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicereceipt.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicereceipt.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicereceipt.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
//        $this->db->group_by("at_invoicereceipt.InvoiceNumber");
        $this->db->group_by("at_invoicereceipt.CustomerCode");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicereceipt.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_recv_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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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_invoicereceipt.CustomerCode                AS CustomerCode_IR
                , at_invoicereceipt.InvoiceNumber		AS InvoiceNumber_IR
                , at_invoicereceipt.InvoiceDate                 AS InvoiceDate_IR
                , at_invoicereceipt.InvoiceDate                 AS DueDate_IR
                , at_invoicereceipt.InvoiceDate                 AS AgeDueDate_IR
                , at_invoicereceipt.InvoiceCurrCode		AS InvoiceCurrCode_IR
                , at_invoicereceipt.InvoiceCurrRate		AS InvoiceCurrRate_IR
                , at_invoicereceipt.InvoiceFiscalRate           AS InvoiceFiscalRate_IR	
                , sum(at_invoicereceipt.InvoiceAmount)		AS InvoiceAmount_IR 
                , sum(at_invoicereceipt.ReceiptAmount) 
                    + sum(at_invoicereceipt.ReceiptDiscount) 
                    + sum(at_invoicereceipt.ReceiptDPUsed)
                    + sum(at_invoicereceipt.ReceiptRounded)	AS ReceiptTotal_IR  
                , sum(at_invoicereceipt.InvoiceOwing)		AS InvoiceOwing_IR  
                , at_invoicereceipt.InvoiceType                 AS InvoiceType_IR
                , at_invoicereceipt.DPStatus                    AS DPStatus_IR
                , am_customer.CustomerName                      AS CustomerName_IR
                , am_termspayment.NetDueIn                    AS NetDueIn_SI
                , am_currency.CurrencySymbol"
                );
        
        $this->db->from('at_invoicereceipt');
        $this->db->join("am_customer", "am_customer.CustomerCode = at_invoicereceipt.CustomerCode","inner");
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = at_invoicereceipt.InvoiceNumber","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("am_branch", "am_branch.BranchCode = at_invoicereceipt.BranchCode","inner");
        $this->db->join("am_currency", "am_currency.CurrencyCode = at_invoicereceipt.InvoiceCurrCode "
                . " and am_currency.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->where("at_invoicereceipt.InvoiceType", 0);
        $this->db->where("at_invoicereceipt.DPStatus", 0);
        $this->db->where("at_invoicereceipt.InvoiceOwing>0", null,false);
        $this->db->where("at_invoicereceipt.PPnFiscalStatus", 0);
        $this->db->where_in('at_invoicereceipt.CustomerCode', $PreData);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('at_invoicereceipt.InvoiceNumber', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicereceipt.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicereceipt.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicereceipt.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicereceipt.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by("at_invoicereceipt.InvoiceNumber");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicereceipt.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();
        return $result;
    }    
    //end get all aging receivable detail
    
    public function get_all_customer_list() {        
        /* =================================post dari find di kiri==================================================== */
        //$param  = $this->input->post('param');
        $CompCode       = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $BranchCode     = ($this->input->post('cmb_branch')!=null)?$this->input->post('cmb_branch'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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'):'';
        /* =================================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');
        /* =========================================================================================================== */
        $strSql = ""
            ." (SELECT SUM(at_invoicereceipt.InvoiceOwing)
                FROM at_invoicereceipt
                WHERE at_invoicereceipt.CustomerCode = am_customer.CustomerCode
                AND at_invoicereceipt.InvoiceOwing>0
                AND at_invoicereceipt.PPnFiscalStatus = '1'";
        $DateTo!=""&&$DateTo!=null?$strSql .= " AND at_invoicereceipt.InvoiceDate <= '".date("Y-m-d", strtotime($DateTo))."'":"";
        $strSql .= " ) AS totalTax
            , (SELECT SUM(at_sorddetaildp.SaldoDPBalance - at_sorddetaildp.DPUsed)
                FROM at_sorddetaildp
                WHERE at_sorddetaildp.CustomerCode =  am_customer.CustomerCode 
                AND (at_sorddetaildp.SaldoDPBalance - at_sorddetaildp.DPUsed) >  0 ";
        $DateTo!=""&&$DateTo!=null?$strSql .= " AND at_sorddetaildp.PaymentDate <= '".date("Y-m-d", strtotime($DateTo))."'":"";
        $strSql .= " ) AS totalBalance
            , am_customer.CustomerCode
            , am_customer.CustomerName
            , am_customer.CurrencyCode
            , am_customer.BranchCode
            ";
        $this->db->select($strSql);
        
        $this->db->from('am_customer');
        //==========================================================================================================//
     
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('am_customer.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(am_customer.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(am_customer.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
                
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('am_customer.CurrencyCode', 'ASC');
        }else{
            $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
        }
        /* ==============================================END SHORTING TABLE========================================================== */        
        
        $query = $this->db->get();
//        echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        //$data = array('datefrom'=>$DateFrom,'dateend'=>$DateTo,"data"=>$result);
        return $result;
    }
    public function get_all_customer_list_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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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_invoicereceipt.CustomerCode                AS CustomerCode_IR
                , at_invoicereceipt.InvoiceCurrCode		AS InvoiceCurrCode_IR
                , at_invoicereceipt.InvoiceCurrRate		AS InvoiceCurrRate_IR
                , at_invoicereceipt.InvoiceType 		AS InvoiceType_IR
                , at_invoicereceipt.DPStatus    		AS DPStatus_IR
                , am_customer.CustomerName                      AS CustomerName_IR
                , am_customer.OutStanding                       AS OutStanding_IR
                , am_customer.OutStandingFiscal                 AS OutStandingFiscal_IR
                "
                );
        
        $this->db->from('at_invoicereceipt');
        $this->db->join("am_customer", "am_customer.CustomerCode = at_invoicereceipt.CustomerCode","inner");
        $this->db->where("at_invoicereceipt.InvoiceType", 0);
        $this->db->where("at_invoicereceipt.DPStatus", 0);
        $this->db->where("at_invoicereceipt.InvoiceOwing>0", null,false);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
        if ($NoInvoices != "" && $NoInvoices != "All" ) {
            $this->db->like('at_invoicereceipt.InvoiceNumber', $NoInvoices);
        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName code===================================================== */
        
        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "" && $BranchCode != "All" ) {
            $this->db->where('at_invoicereceipt.BranchCode', $BranchCode);
        } 
        /* ==================================end find by branch code===================================================== */
        
        //========where sesuai Company========================//
        if ($CompCode != "" && $CompCode != "All" ) {
            $this->db->where("SUBSTRING(at_invoicereceipt.BranchCode,1,LENGTH('".$CompCode."'))='".$CompCode."'", null); 
        } else {
            $this->db->where("SUBSTRING(at_invoicereceipt.BranchCode,1,LENGTH('".$sess_company."'))='".$sess_company."'", null); 
        }
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_invoicereceipt.InvoiceDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by("at_invoicereceipt.InvoiceNumber");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_invoicereceipt.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;
    }
        
    //get all Customer Aging Ranking Unsettled
    public function pre_get_all_cust_aging_ranking_unsettled() {
        $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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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_salesinvoice.CustomerCode
        ");
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesinvoice.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->where('at_salesinvoice.TransStatus', 1);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
//        if ($NoInvoices != "" && $NoInvoices != null ) {
//            $this->db->like('at_salesinvoice.SInvNumber', $NoInvoices);
//        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName 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); 
//        }
        $this->db->where('am_branch.CompanyCode', $CompCode);
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
//        $this->db->group_by('at_salesinvoice.SInvNumber');
        $this->db->group_by('at_salesinvoice.CustomerCode');
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.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();
        return $result;
    }
    public function get_all_cust_aging_ranking_unsettled() {
        $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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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
                , at_salesinvoice.BranchCode
                , am_branch.BranchName
                , am_company.CompanyCode
                , am_company.CompanyName
                , at_salesinvoice.SInvDate
                , at_salesinvoice.SInvDate AS DueDate
                , at_salesinvoice.SInvDate AS AgeDueDate
                , at_salesinvoice.TransStatus AS TransStatus_SI
                , am_termspayment.NetDueIn
                , at_salesinvoice.CustomerCode
                , am_customer.CustomerName
                , '' AS SRecDetailID
                , '' AS SrecNumber
                , '' AS SRecDate
                , '' AS TransStatus_SR
                , '' AS FinishDue
                ,( SELECT CONCAT( b.SRecDetailID,'_', b.SrecNumber,'_', a.SRecDate,'_', a.TransStatus )  
                        FROM at_salesreceipt a 
                        INNER JOIN at_srecdetail b ON a.SRecNumber = b.SrecNumber 
                        WHERE b.SInvNumber = at_salesinvoice.SInvNumber
                        ORDER BY b.SRecDetailID DESC LIMIT 0,1 
                   ) AS lastDateRec
                "
                );
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesinvoice.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","left");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . "AND (at_salesreceipt.TransStatus='2')","left");
        $this->db->where('at_salesinvoice.TransStatus', 1);
        $this->db->where_in('at_salesinvoice.CustomerCode', $PreData);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
//        if ($NoInvoices != "" && $NoInvoices != "All" ) {
//            $this->db->like('at_salesinvoice.SInvNumber', $NoInvoices);
//        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName 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); 
//        }
        $this->db->where('am_branch.CompanyCode', $CompCode);
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by('at_salesinvoice.SInvNumber');
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.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);
        return $result;
    }
                
    public function get_all_detail_cust_aging_ranking_unsettled() {
        
        /* =================================post dari find di kiri==================================================== */
        $CompanyCode   = ($this->input->post('companyCode')!=null)?$this->input->post('companyCode'):'';
        $CustomerCode   = ($this->input->post('customerCode')!=null)?$this->input->post('customerCode'):'';
        $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->_protect_identifiers = FALSE;
        $this->db->select(""
                ."at_salesinvoice.SInvNumber
                , at_salesinvoice.SInvDate
                , at_salesinvoice.SInvDate AS DueDate
                , at_salesinvoice.SInvDate AS AgeDueDate
                , at_salesinvoice.SInvTotalAmount AS SInvTotalAmount_SI
                , at_salesinvoice.TransStatus AS TransStatus_SI 
                , am_termspayment.NetDueIn
                , curr_cust.CurrencySymbol AS CurrencySymbol_Cust
                , at_salesinvoice.CustomerCode
                , at_srecdetail.SRecDetailID
                , at_srecdetail.SrecNumber"
                
                . ", 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.SRecOwing as SRecOwing_SRDet"                    
                . ", at_srecdetail.SRecCurrency     as SRecCurrency_SRDet"                    
                .", at_salesreceipt.SInvTotalAmount as SInvTotalAmount_SR   "                              
                .", at_salesreceipt.CurrencyRate as CurrencyRate_SR   "               
                .", curr_sr.CurrencySymbol as CurrencySymbol_SR
                    
                , at_salesreceipt.SRecDate 
                , at_salesreceipt.TransStatus AS TransStatus_SR
                , '' AS FinishDue
                "
                );
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_currency curr_cust", "curr_cust.CurrencyCode = am_customer.CurrencyCode and curr_cust.CompanyCode='$CompanyCode'","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","left");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . "AND (at_salesreceipt.TransStatus='2')","left");
        $this->db->join("am_currency curr_sr", "curr_sr.CurrencyCode = at_salesreceipt.CurrencyCode and curr_sr.CompanyCode='$CompanyCode'","left");
        $this->db->where('at_salesinvoice.TransStatus', 1);
        $this->db->where('at_salesinvoice.CustomerCode', $CustomerCode);
        //==========================================================================================================//
     
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.SInvNumber', 'ASC');
            $this->db->order_by('at_srecdetail.SRecDetailID', 'ASC');
        }else{
            if($txtField=="SInvNumber"){
                $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
                $this->db->order_by('at_srecdetail.SRecDetailID', '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);
        return $result;
    }
    //end get all Customer Aging Ranking Unsettled
        
    
    //get all Customer Aging Ranking Settled
    public function pre_get_all_cust_aging_ranking_settled() {
        $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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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_salesinvoice.CustomerCode
        ");
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesinvoice.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","inner");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . "AND (at_salesreceipt.TransStatus='2')","inner");        
        $this->db->where('at_salesinvoice.TransStatus', 2);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
//        if ($NoInvoices != "" && $NoInvoices != "All" ) {
//            $this->db->like('at_salesinvoice.SInvNumber', $NoInvoices);
//        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName 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); 
        }
        $this->db->where('am_branch.CompanyCode', $CompCode);
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
//        $this->db->group_by('at_salesinvoice.SInvNumber');
        $this->db->group_by('at_salesinvoice.CustomerCode');
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.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();
        return $result;
    }
    public function get_all_cust_aging_ranking_settled() {
        $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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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.SInvNumber
                , at_salesinvoice.BranchCode
                , am_branch.BranchName
                , am_company.CompanyCode
                , am_company.CompanyName
                , at_salesinvoice.SInvDate
                , at_salesinvoice.SInvDate AS DueDate
                , at_salesinvoice.SInvDate AS AgeDueDate
                , at_salesinvoice.TransStatus AS TransStatus_SI
                , am_termspayment.NetDueIn
                , at_salesinvoice.CustomerCode
                , am_customer.CustomerName
                , concat('') AS SRecDetailID
                , concat('') AS SrecNumber
                , concat('') AS SRecDate
                , concat('') AS TransStatus_SR
                , concat('') AS FinishDue
                ,( SELECT CONCAT( b.SRecDetailID,concat('_'), b.SrecNumber,concat('_'), a.SRecDate,concat('_'), a.TransStatus )  
                        FROM at_salesreceipt a 
                        INNER JOIN at_srecdetail b ON a.SRecNumber = b.SrecNumber 
                        WHERE b.SInvNumber = at_salesinvoice.SInvNumber
                        ORDER BY b.SRecDetailID DESC LIMIT 1 
                   ) AS lastDateRec
                "
                );
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesinvoice.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","inner");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . "AND (at_salesreceipt.TransStatus='2')","inner");        
        $this->db->where('at_salesinvoice.TransStatus', 2);
        $this->db->where_in('at_salesinvoice.CustomerCode', $PreData);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
//        if ($NoInvoices != "" && $NoInvoices != "All" ) {
//            $this->db->like('at_salesinvoice.SInvNumber', $NoInvoices);
//        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != null ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName 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); 
        }
        $this->db->where('am_branch.CompanyCode', $CompCode);
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by('at_salesinvoice.SInvNumber');
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.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);
        return $result;
    }    
    public function pre_get_all_detail_cust_aging_ranking_settled() {
        $CompanyCode   = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $CustomerCode   = ($this->input->post('customerCode')!=null)?$this->input->post('customerCode'):'';
        $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_salesinvoice.SInvNumber as code"
        );
        $this->db->from('at_salesinvoice');
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_currency curr_cust", "curr_cust.CurrencyCode = am_customer.CurrencyCode and curr_cust.CompanyCode='$CompanyCode'","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","inner");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . "AND at_salesreceipt.PPnFiscalStatus='0' AND (at_salesreceipt.TransStatus='2')","inner");
        $this->db->join("am_currency curr_sr", "curr_sr.CurrencyCode = at_salesreceipt.BankCurrCode and curr_sr.CompanyCode='$CompanyCode'","left");
        $this->db->where('at_salesinvoice.TransStatus', 2);
        $this->db->where('at_salesinvoice.CustomerCode', $CustomerCode);
        //==========================================================================================================//
        
        $this->db->group_by("at_salesinvoice.SInvNumber");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.SInvNumber', 'ASC');
            $this->db->order_by('at_srecdetail.SRecDetailID', 'ASC');
        }else{
            if($txtField=="SInvNumber"){
                $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
                $this->db->order_by('at_srecdetail.SRecDetailID', 'ASC');
            }else{
                $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
            }            
        }
        /* ==============================================END SHORTING TABLE========================================================== */    
        $this->db->limit($Pages['limit'], $limitStart);    
        
        $query = $this->db->get();
        $this->db->_protect_identifiers = TRUE;
        //echo "<hr>".$this->db->last_query()."<hr>";
        $result = $query->result();
        return $result;
    }
    public function get_all_detail_cust_aging_ranking_settled() {
        $CompanyCode   = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $CustomerCode   = ($this->input->post('customerCode')!=null)?$this->input->post('customerCode'):'';
        $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
//            , at_salesinvoice.SInvDate
//            , at_salesinvoice.SInvDate AS DueDate
//            , at_salesinvoice.SInvDate AS AgeDueDate
//            , at_salesinvoice.TransStatus AS TransStatus_SI
//            , am_termspayment.NetDueIn
//            , at_salesinvoice.CustomerCode
//            , at_srecdetail.SRecDetailID
//            , at_srecdetail.SrecNumber
//            , at_salesreceipt.SRecTotalOwing 
//            , at_salesreceipt.SRecDate 
//            , at_salesreceipt.TransStatus AS TransStatus_SR
//            , '' AS FinishDue
//            "
//        );
//        
//        $this->db->from('at_salesinvoice');
//        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
//        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
//        $this->db->join("am_currency curr_cust", "curr_cust.CurrencyCode = am_customer.CurrencyCode and curr_cust.CompanyCode='$CompanyCode'","inner");
//        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","inner");
//        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
//                . "AND (at_salesreceipt.TransStatus='2')","inner");
//        $this->db->join("am_currency curr_sr", "curr_sr.CurrencyCode = at_salesreceipt.CurrencyCode and curr_sr.CompanyCode='$CompanyCode'","left");
//        $this->db->where('at_salesinvoice.TransStatus', 2);
//        $this->db->where('at_salesinvoice.CustomerCode', $CustomerCode);
        
        
        $this->db->select(""
            ."at_salesinvoice.SInvNumber
            , at_salesinvoice.SInvDate
            , at_salesinvoice.SInvDate AS DueDate
            , at_salesinvoice.SInvDate AS AgeDueDate
            , (at_salesinvoice.SInvTotalAmount - at_salesinvoice.SInvPPNOut) AS SInvTotalAmount_SI
            , at_salesinvoice.TransStatus AS TransStatus_SI 
            , am_termspayment.NetDueIn
            , curr_cust.CurrencySymbol AS CurrencySymbol_Cust
            , at_salesinvoice.CustomerCode
            , at_srecdetail.SRecDetailID
            , at_srecdetail.SrecNumber"

            . ", 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.SRecOwing as SRecOwing_SRDet"                    
            . ", at_srecdetail.SRecCurrency     as SRecCurrency_SRDet"                    
            .", at_salesreceipt.SInvTotalAmount as SInvTotalAmount_SR   "                              
            .", at_salesreceipt.CurrencyRate as CurrencyRate_SR   "                               
            .", at_salesreceipt.BankCurrRate as BankCurrRate_SR   "               
            .", curr_sr.CurrencySymbol as CurrencySymbol_SR

            , at_salesreceipt.SRecDate 
            , at_salesreceipt.TransStatus AS TransStatus_SR
            , '' AS FinishDue
            "
        );
        $this->db->from('at_salesinvoice');
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_currency curr_cust", "curr_cust.CurrencyCode = am_customer.CurrencyCode and curr_cust.CompanyCode='$CompanyCode'","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","inner");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . "AND at_salesreceipt.PPnFiscalStatus='0' AND (at_salesreceipt.TransStatus='2')","inner");
        $this->db->join("am_currency curr_sr", "curr_sr.CurrencyCode = at_salesreceipt.BankCurrCode and curr_sr.CompanyCode='$CompanyCode'","left");
//        $this->db->where("at_invoicereceipt.PPnFiscalStatus", 0);
        $this->db->where('at_salesinvoice.TransStatus', 2);
        $this->db->where('at_salesinvoice.CustomerCode', $CustomerCode);
        $this->db->where_in('at_salesinvoice.SInvNumber', $PreData);
        //==========================================================================================================//
     
        
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.SInvNumber', 'ASC');
            $this->db->order_by('at_srecdetail.SRecDetailID', 'ASC');
        }else{
            if($txtField=="SInvNumber"){
                $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
                $this->db->order_by('at_srecdetail.SRecDetailID', '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);
        return $result;
    }
    //end get all Customer Aging Ranking Settled
        
    //get all Customer Aging Ranking All (Settled & Unsettled)
    public function pre_get_all_cust_aging_ranking_all() {
        $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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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_salesinvoice.CustomerCode"
        );
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesinvoice.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->where_in('at_salesinvoice.TransStatus', array(1,2));
        //==========================================================================================================//
     
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName code===================================================== */
        
        /* ===================================find by InvoiceNumber code========================================================= */
//        if ($NoInvoices != "" && $NoInvoices != "All" ) {
//            $this->db->like('at_salesinvoice.SInvNumber', $NoInvoices);
//        } 
        /* ==================================end find by InvoiceNumber 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); 
//        }
        $this->db->where('am_branch.CompanyCode', $CompCode);
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
//        $this->db->group_by('at_salesinvoice.SInvNumber');
        $this->db->group_by('at_salesinvoice.CustomerCode');
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.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);
        return $result;
    }
    public function get_all_cust_aging_ranking_all() {
        $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'):'';
        $Customer       = ($this->input->post('txt_cust')!=null)?$this->input->post('txt_cust'):'';
        $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
                , at_salesinvoice.BranchCode
                , am_branch.BranchName
                , am_company.CompanyCode
                , am_company.CompanyName
                , at_salesinvoice.SInvDate
                , at_salesinvoice.SInvDate AS DueDate
                , at_salesinvoice.SInvDate AS AgeDueDate
                , at_salesinvoice.TransStatus AS TransStatus_SI
                , am_termspayment.NetDueIn
                , at_salesinvoice.CustomerCode
                , am_customer.CustomerName
                , '' AS SRecDetailID
                , '' AS SrecNumber
                , '' AS SRecDate
                , '' AS TransStatus_SR
                , '' AS FinishDue
                ,( SELECT CONCAT( b.SRecDetailID,'_', b.SrecNumber,'_', a.SRecDate,'_', a.TransStatus )  
                        FROM at_salesreceipt a 
                        INNER JOIN at_srecdetail b ON a.SRecNumber = b.SrecNumber 
                        WHERE b.SInvNumber = at_salesinvoice.SInvNumber
                        ORDER BY b.SRecDetailID DESC LIMIT 0,1 
                   ) AS lastDateRec
                "
                );
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_branch", "am_branch.BranchCode = at_salesinvoice.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","left");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . "AND (at_salesreceipt.TransStatus='2')","left");
        $this->db->where_in('at_salesinvoice.TransStatus', array(1,2));
        $this->db->where_in('at_salesinvoice.CustomerCode', $PreData);
        //==========================================================================================================//
     
        /* ===================================find by InvoiceNumber code========================================================= */
//        if ($NoInvoices != "" && $NoInvoices != "All" ) {
//            $this->db->like('at_salesinvoice.SInvNumber', $NoInvoices);
//        } 
        /* ==================================end find by InvoiceNumber code===================================================== */
        
        /* ===================================find by CustomerName code========================================================= */
        if ($Customer != "" && $Customer != "All" ) {
            $this->db->like('am_customer.CustomerName', $Customer);
        } 
        /* ==================================end find by CustomerName 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); 
//        }
        $this->db->where('am_branch.CompanyCode', $CompCode);
        //========end where sesuai Company========================//
        
        //=====================cek data tanggal =============//
        if ($DateTo != '') {
            $this->db->where('at_salesinvoice.SInvDate <=', date("Y-m-d", strtotime($DateTo))); //change
        }
        //=====================END cek data tanggal ============//
        
        $this->db->group_by('at_salesinvoice.SInvNumber');
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.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);
        return $result;
    }
                
    public function pre_get_all_detail_cust_aging_ranking_all() {
        $CompanyCode   = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $CustomerCode   = ($this->input->post('customerCode')!=null)?$this->input->post('customerCode'):'';
        $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_salesinvoice.SInvNumber as code"
        );
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_currency curr_cust", "curr_cust.CurrencyCode = am_customer.CurrencyCode and curr_cust.CompanyCode='$CompanyCode'","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","left");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . " AND (at_salesreceipt.TransStatus='2')","left");
        $this->db->where_in('at_salesinvoice.TransStatus', array(1,2));
        $this->db->where('at_salesinvoice.CustomerCode', $CustomerCode);
        //==========================================================================================================//
             
        $this->db->group_by("at_salesinvoice.SInvNumber");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.SInvNumber', 'ASC');
            $this->db->order_by('at_srecdetail.SRecDetailID', 'ASC');
        }else{
            if($txtField=="SInvNumber"){
                $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
                $this->db->order_by('at_srecdetail.SRecDetailID', '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);
        return $result;
    }
    public function get_all_detail_cust_aging_ranking_all() {
        $CompanyCode   = ($this->input->post('cmb_company')!=null)?$this->input->post('cmb_company'):'';
        $CustomerCode   = ($this->input->post('customerCode')!=null)?$this->input->post('customerCode'):'';
        $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
//                , at_salesinvoice.SInvDate
//                , at_salesinvoice.SInvDate AS DueDate
//                , at_salesinvoice.SInvDate AS AgeDueDate
//                , at_salesinvoice.TransStatus AS TransStatus_SI
//                , am_termspayment.NetDueIn
//                , at_salesinvoice.CustomerCode
//                , at_srecdetail.SRecDetailID
//                , at_srecdetail.SrecNumber
//                , at_salesreceipt.SRecTotalOwing 
//                , at_salesreceipt.SRecDate 
//                , at_salesreceipt.TransStatus AS TransStatus_SR
//                , '' AS FinishDue
//                "
//                );
//        
//        $this->db->from('at_salesinvoice');
//        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
//        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","left");
//        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
//                . "AND (at_salesreceipt.TransStatus='2')","left");
//        $this->db->where_in('at_salesinvoice.TransStatus', array(1,2));
//        $this->db->where('at_salesinvoice.CustomerCode', $CustomerCode);

        
        $this->db->select(""
            ."at_salesinvoice.SInvNumber
            , at_salesinvoice.SInvDate
            , at_salesinvoice.SInvDate AS DueDate
            , at_salesinvoice.SInvDate AS AgeDueDate
            , (at_salesinvoice.SInvTotalAmount - at_salesinvoice.SInvPPNOut) AS SInvTotalAmount_SI
            , at_salesinvoice.TransStatus AS TransStatus_SI 
            , am_termspayment.NetDueIn
            , curr_cust.CurrencySymbol AS CurrencySymbol_Cust
            , at_salesinvoice.CustomerCode
            , at_srecdetail.SRecDetailID
            , at_srecdetail.SrecNumber"

            . ", 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.SRecOwing as SRecOwing_SRDet"                    
            . ", at_srecdetail.SRecCurrency     as SRecCurrency_SRDet"                    
            .", at_salesreceipt.SInvTotalAmount as SInvTotalAmount_SR   "                              
            .", at_salesreceipt.CurrencyRate as CurrencyRate_SR   "                            
            .", at_salesreceipt.BankCurrRate as BankCurrRate_SR   "                  
            .", curr_sr.CurrencySymbol as CurrencySymbol_SR
            , at_salesreceipt.PPnFiscalStatus
            , at_salesreceipt.SRecDate 
            , at_salesreceipt.TransStatus AS TransStatus_SR
            , '' AS FinishDue
            "
        );
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_termspayment", "am_termspayment.TermspaymentID = at_salesinvoice.TermsPaymentID","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_currency curr_cust", "curr_cust.CurrencyCode = am_customer.CurrencyCode and curr_cust.CompanyCode='$CompanyCode'","inner");
        $this->db->join("at_srecdetail", "at_srecdetail.SInvNumber = at_salesinvoice.SInvNumber","left");
        $this->db->join("at_salesreceipt", "at_salesreceipt.SRecNumber = at_srecdetail.SrecNumber "
                . " AND (at_salesreceipt.TransStatus='2')","left");
        $this->db->join("am_currency curr_sr", "curr_sr.CurrencyCode = at_salesreceipt.CurrencyCode and curr_sr.CompanyCode='$CompanyCode'","left");
        $this->db->where_in('at_salesinvoice.TransStatus', array(1,2));
        $this->db->where('at_salesinvoice.CustomerCode', $CustomerCode);
        //==========================================================================================================//
        $this->db->where_in('at_salesinvoice.SInvNumber', $PreData);
             
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.SInvNumber', 'ASC');
            $this->db->order_by('at_srecdetail.SRecDetailID', 'ASC');
        }else{
            if($txtField=="SInvNumber"){
                $this->db->order_by($txtTable . '.'.$txtField, $typeShorting);
                $this->db->order_by('at_srecdetail.SRecDetailID', '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);
        return $result;
    }
    //end get all Customer Aging Ranking All (Settled & Unsettled)
    
    public function get_all_detail_amount_invoices_outstanding() {
        
        /* =================================post dari find di kiri==================================================== */
        //$param  = $this->input->post('param');
        $CustomerCode     = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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->_protect_identifiers = FALSE;
        $this->db->select(" 
            (
              IF(
                avtrans_1.CurrencyCode = 'IDR',
                #'SInvNumber 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 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'
                  )),
                #'SInvNumber 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 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'
                    )
                  ))
              )
            ) AS totalAmount,
            IF(
              #TOTAL_FISCAL
              avtrans_1.CurrencyCode = 'IDR',
              '0',
              (SELECT 
                #SUM(avtrans_3.Saldo_Credit) 
                SUM(avtrans_3.Saldo_Debet) 
              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,
            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_customer.CustomerCode,
            am_customer.CustomerName,
            am_branch.BranchName,
            am_company.CompanyName
        ");
        
        $this->db->from('av_all_transaction_for_report avtrans_1');
        $this->db->join("at_salesinvoice", "at_salesinvoice.SInvNumber = avtrans_1.Journal_Number","inner");
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_branch", "am_customer.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('sinv'));
        $this->db->where_in("avtrans_1.TransStatus", array(1));
        $this->db->where("am_customer.CustomerCode", $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========================================================== */        
        
        $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 pre_get_all_detail_payment_invoices_receivables_outstanding() {
        $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'):'';
        $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_salesinvoice.SInvNumber as code
        ");
        
        $this->db->from('at_salesinvoice');
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_branch", "am_customer.BranchCode=am_branch.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("at_srecdetail", "at_salesinvoice.SInvNumber = at_srecdetail.SInvNumber","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_in("at_salesinvoice.TransStatus", array(1));
        $this->db->where("am_customer.CustomerCode", $CustomerCode);
        //==========================================================================================================//
     
        /* ===================================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========================//
        
        $this->db->group_by("at_salesinvoice.SInvNumber");
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.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>";
        $this->db->_protect_identifiers = TRUE;
        $result = $query->result();
        return $result;
    }
    public function get_all_detail_payment_invoices_receivables_outstanding() {
        $CustomerCode     = ($this->input->post('txt_customer')!=null)?$this->input->post('txt_customer'):'';
        $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_customer.CustomerCode,
            am_customer.CustomerName,
            am_customer.CurrencyCode,
            at_salesinvoice.SInvNumber,
            at_salesinvoice.SInvDate,
            IF(
              am_customer.CurrencyCode <> 'IDR',
              (
                at_salesinvoice.SInvTotalAmount- at_salesinvoice.SInvPPnOut
              ),
              at_salesinvoice.SInvTotalAmount
            ) AS SInvTotalAmount,
            IF(
              am_customer.CurrencyCode = 'IDR',
              0,
              (
                at_salesinvoice.SInvPPnOut * at_salesinvoice.FiscalRate
              )
            ) AS fiscalInvoice,
            at_salesinvoice.TransStatus,
            at_salesreceipt.SRecDate,
            at_salesreceipt.SRecNumber,
            at_salesreceipt.BankCurrCode,
            at_srecdetail.SRecTotalAmnt + at_srecdetail.SRecRounded AS SRecTotalAmnt,
            at_srecdetail.SRecCurrency,
            at_srecdetail.SRecCurrRate,
            at_srecdetail.FiscalStatus,
            at_srecdetail.SOrdNumber,
            at_salesreceipt.TransStatus AS TransStatus_Rec ,
            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_salesinvoice');
        $this->db->join("am_customer", "am_customer.CustomerCode = at_salesinvoice.CustomerCode","inner");
        $this->db->join("am_branch", "am_customer.BranchCode=am_branch.BranchCode","inner");
        $this->db->join("am_company", "am_company.CompanyCode = am_branch.CompanyCode","inner");
        $this->db->join("at_srecdetail", "at_salesinvoice.SInvNumber = at_srecdetail.SInvNumber","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->join("am_currency", "am_currency.CurrencyCode = am_customer.CurrencyCode "
                . "and am_currency.CompanyCode = am_branch.CompanyCode","left");
        $this->db->where_in("at_salesinvoice.TransStatus", array(1));
        $this->db->where("am_customer.CustomerCode", $CustomerCode);
        
        $this->db->where_in('at_salesinvoice.SInvNumber', $PreData);
        //==========================================================================================================//
     
        /* ==============================================SHORTING TABLE========================================================== */
        if($txtTable=="" && $txtField==""){
            $this->db->order_by('at_salesinvoice.SInvDate', '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;
    }
}

?>
