<?php

class M_expensecashbon_payment extends CI_Model {

    public $table = "at_expensepayment";
    public $detail = "at_epaydetail";
    public $journal = "at_eprealjournal";
    public $crv_table = "at_epaymentreal";
    public $account = "at_compaccountgl";
    public $acc_gl = "am_accountgl";
    public $employee = "am_employee";
    public $branch = "am_branch";
    public $department = "am_department";
    public $tbl_cashbon = "at_expensecashbon";
    public $reconcile = "at_bankreconcille";
    public $period = "at_periodend";

    function get_all() {
        /* =========================lemparan parah dari custom.js untuk searching_data_transaksi===================== */
        $param = $this->input->post('param');
        /* ====================================end=================================================================== */

        /* =================================post dari find di kiri==================================================== */
        $Number = isset($param['txt_IDNumber']) ? $param['txt_IDNumber'] : '';
        $Desc = isset($param['txt_Description']) ? $param['txt_Description'] : '';

        $DateFrom = isset($param['txt_DateFrom']) ? $param['txt_DateFrom'] : '';
        $DateTo = isset($param['txt_DateTo']) ? $param['txt_DateTo'] : '';
        $TransStatus = isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';
        $BranchCode = isset($param['cmb_branch']) ? $param['cmb_branch'] : '';
        /* ============================================end============================================================ */

        /* ======================================scrol view 1-150======================================= */
        $page = ($this->input->post('page')) ? intval($this->input->post('page')) : 1;
        $rows = ($this->input->post('rows')) ? intval($this->input->post('rows')) : 150;

        $beginning = ($page - 1) * $rows;
        $end = $page * $rows;
        /* ============================================================================================== */

        /* ==========================================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('am_branch.BranchName, am_department.DepartmentName, am_employee.EmployeeName, at_expensepayment.EPayNumber,created.EmployeeName as nm_createdby,
						   at_expensepayment.EPayDate, sum(at_epaydetail.ExpenseAmount) as amountPayment, at_expensepayment.EPayDesc, at_expensepayment.TransStatus, at_expensecashbon.EBonAmount');
        $this->db->from('at_expensepayment');
        $this->db->join('at_epaydetail', 'at_expensepayment.EPayNumber=at_epaydetail.EPayNumber', 'INNER');
        $this->db->join('am_branch', 'at_expensepayment.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_department', 'at_expensepayment.DepartmentCode=am_department.DepartmentCode', 'INNER');
        $this->db->join('am_employee', 'at_expensepayment.EmployeeCode=am_employee.EmployeeCode', 'INNER');
        $this->db->join('at_expensecashbon', 'at_expensepayment.EBonNumber=at_expensecashbon.EBonNumber', 'INNER');
        $this->db->join('am_employee as created', 'at_expensepayment.CreatedBy=created.EmployeeCode', 'INNER');

        /* ===================================find by number dan desc========================================================= */
        if ($Number != "") {
            $this->db->like('at_expensepayment.EPayNumber', $Number);
        }
        if ($Desc != "") {
            $this->db->like('at_expensepayment.EPayDesc', $Desc);
        }
        /* =============================================end=================================================================== */

        /* ===================================find by branch code========================================================= */
        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where('at_expensepayment.BranchCode', $BranchCode);
        } else {
            /*
              inisialisasi untuk loading pertama page branch info
             */
            //----------------kondisi by company-------------------------------------//
            /*
              branch status 0 = HO
              branch status 1 = depo/branch
              jangan lupa sesion data di atas untuk setiap kondisi
              jangan lupa tambahin case when trans status
             */
            $this->db->where('at_expensepayment.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

        /* =====================================================find by date================================================== */
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('at_expensepayment.EPayDate >=', date("Y-m-d", strtotime($DateFrom)));
                $this->db->where('at_expensepayment.EPayDate <=', date("Y-m-d", strtotime($DateTo)));
            }
        }
        /* ======================================================end=========================================================== */

        /* =====================================================find by status================================================== */
        if ($TransStatus != '') {
            $this->db->where_in('at_expensepayment.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_expensepayment.TransStatus', array(0, 1, 4, 5, 6, 7, 8));
        }
        /* =======================================================end======================================================== */

        /* ============================================== group By ========================================================== */
        $this->db->group_by('at_expensepayment.EPayNumber');

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by($this->table . '.TransStatus', 'ASC');
        $this->db->order_by($this->table . '.EPayNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->limit($end, $beginning);
        $query = $this->db->get();
        return $query->result();
    }

    function count_payment_cashbon_number($bulan, $cek_tahun, $BranchCode) {
        $this->db->select('*');
        $this->db->from('at_expensepayment');
        $this->db->where('MONTH(EPayDate)', $bulan);
        $this->db->where('YEAR(EPayDate)', $cek_tahun);
        $this->db->where('BranchCode', $BranchCode);
        return $this->db->get()->num_rows();
    }

    function create_po_number($date) {
        $branch_code = $this->session->userdata('branch');
        $month = date('m', strtotime($date));
        $year = date('y', strtotime($date));

        $exp_number = $branch_code . "/" . $month . $year . "EP/";

        $this->db->like('EPayNumber', $exp_number);
        $num = $this->db->get($this->table)->num_rows() + 1;
        $long = strlen($num);

        if ($long == 1) {
            $number = '00' . $num;
        } elseif ($long == 2) {
            $number = '0' . $num;
        } else {
            $number = $num;
        }

        return $pord_number . $number;
    }

    function get_list_cashbon($EmpCode) {
        $this->db->select('*, at_ebondetail.EBonAccountNumber,at_ebondetail.ExpCategoryID,ag.AccountName');
        $this->db->from('at_expensecashbon');
        $this->db->join($this->acc_gl . ' as ag', 'ag.AccountNumber=at_expensecashbon.BankPayment', 'inner');
        $this->db->join('at_ebondetail', 'at_expensecashbon.EBonNumber=at_ebondetail.EBonNumber', 'INNER');
        $this->db->where('at_expensecashbon.EmployeeCode', $EmpCode);
        $this->db->where('at_expensecashbon.TransStatus', 1);
        //$this->db->where('at_expensecashbon.TransStatus', 0);
        /* $this->db->where('at_expensecashbon.AppLevel1Status', 1);
          $this->db->where('at_expensecashbon.AppLevel2Status', 1); */
        $this->db->order_by('at_expensecashbon.EBonNumber', 'ASC');

        $query = $this->db->get();
        return $query->result();
    }

    function get_list_expense_group_1($replace_akun) {
        $company_code = $this->session->userdata('company');
        $this->db->select('*');
        $this->db->from('sys_expensecategory');
        $this->db->join('sys_expenseaccount', 'sys_expensecategory.ExpCategoryID=sys_expenseaccount.ExpCategoryID', 'INNER');
        $this->db->where('sys_expensecategory.CompanyCode', $company_code);
        $this->db->where('sys_expenseaccount.AccountNumber', $replace_akun);

        $this->db->order_by('sys_expensecategory.ExpCategoryID', 'ASC');
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($result);
    }

    function get_list_expense_group($ExpCategoryID) {
        $company_code = $this->session->userdata('company');
        $this->db->select('ExpCategoryID,ExpCategoryName');
        $this->db->from('sys_expensecategory');
        $this->db->where('sys_expensecategory.ExpCategoryID', $ExpCategoryID);

        $this->db->order_by('sys_expensecategory.ExpCategoryID', 'ASC');
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($result);
    }

    function get_list_item_expense_group($Groupid) {
        $this->db->select('*');
        $this->db->from('sys_expensedetail');
        $this->db->where('ExpCategoryID', $Groupid);
        $this->db->order_by('ExpDetailID', 'ASC');
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($result);
    }

    function get_am_akun_gl($replace_akun, $brannch) {
        $this->db->select('am_accountgl.*');
        $this->db->from('am_accountgl');
        $this->db->join('at_compaccountgl', 'am_accountgl.AccountNumber=at_compaccountgl.AccountNumber');
        $this->db->where('am_accountgl.AccountNumber', $replace_akun);
        $this->db->where('at_compaccountgl.BranchCode', $brannch);
        $this->db->where('at_compaccountgl.TransStatus', 2);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $query = $this->db->get();

        $result = $query->row();
        return $result;
    }

    function insert_payment_cashbon($data_payment) {
        $this->db->insert($this->table, $data_payment);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function insert_detail_payment_cashbon($detail_payment) {
        $this->db->insert($this->detail, $detail_payment);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function get_bank_id($AccountNumber) {
        $this->db->select('BankID');
        $this->db->from('am_accountgl');
        $this->db->join('at_compaccountgl', 'am_accountgl.AccountNumber=am_accountgl.AccountNumber', 'INNER');
        $this->db->where('am_accountgl.AccountNumber', $AccountNumber);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BankID = $row->BankID;
            }

            return $BankID;
        } else {
            return FALSE;
        }
    }

    function get_bank_code_masuk($bank_id) {
        $this->db->select('BankInCode');
        $this->db->from('am_bank');
        $this->db->where('BankID', $bank_id);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BankInCode = $row->BankInCode;
            }

            return $BankInCode;
        } else {
            return FALSE;
        }
    }

    function get_akun($CompanyCode, $replace_akun, $currency_code, $cpv_crv) {
        $jobtitle = $this->session->userdata("jobtitle");
        $BranchCode = $this->session->userdata('branch');
        $BranchStatus = $this->session->userdata('branch_status');

        $this->db->select('at_compaccountgl.BranchCode, am_accountgl.AccountNumber,  am_accountgl.AccountName , am_accountgl.CurrencyCode');
        $this->db->from($this->account);
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        $this->db->join('am_branch', 'at_compaccountgl.BranchCode=am_branch.BranchCode');

        $this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
        $this->db->where('at_compaccountgl.BranchCode', $BranchCode);

        if ($cpv_crv == "CPV") {
            $bank_n_kas = array(5, 7);
            $this->db->where_in('am_accountgl.AccountSubTypeID', $bank_n_kas);
        } else {
            $this->db->where('am_accountgl.AccountNumber', $replace_akun);
        }
        //		
        //$a = array(5,6);

        $this->db->where('am_accountgl.AccountChild', 1);
        $this->db->where('am_accountgl.CurrencyCode', $currency_code);
        $this->db->where('at_compaccountgl.TransStatus', 2);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));

        $query = $this->db->get();

        $result = $query->result();
        return $result;
    }

    function get_bank_code_keluar($bank_id) {
        $this->db->select('BankOutCode');
        $this->db->from('am_bank');
        $this->db->where('BankID', $bank_id);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BankOutCode = $row->BankOutCode;
            }

            return $BankOutCode;
        } else {
            return FALSE;
        }
    }

    function count_crv_number($bulan, $cek_tahun) {
        $this->db->select('*');
        $this->db->from('at_epaymentreal');
        $this->db->where('MONTH(EPayDate)', $bulan);
        $this->db->where('YEAR(EPayDate)', $cek_tahun);
        $this->db->where('EPRealTransType', 1);
        return $this->db->get()->num_rows();
    }

    function get_last_crv_number() {
        $qty = "SELECT MAX(EPRealNumber) as EPRealNumber FROM at_epaymentreal WHERE EPRealTransType ='1'";
        return $this->db->query($qty)->result();
    }

    function count_cpv_number($bulan, $cek_tahun) {
        $this->db->select('*');
        $this->db->from('at_epaymentreal');
        $this->db->where('MONTH(EPayDate)', $bulan);
        $this->db->where('YEAR(EPayDate)', $cek_tahun);
        $this->db->where('EPRealTransType', 2);
        return $this->db->get()->num_rows();
    }

    function get_last_cpv_number() {
        $qty = "SELECT MAX(EPRealNumber) as EPRealNumber FROM at_epaymentreal WHERE EPRealTransType ='2'";
        return $this->db->query($qty)->result();
    }

    function insert_detail_crv($detail_ppaycrv) {
        $this->db->trans_begin();
        $this->db->insert('at_epaymentreal', $detail_ppaycrv);

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        } else {
            $this->db->trans_commit();
            return 1;
        }
    }

    function insert_detail_cpv($detail_ppaycpv) {
        $this->db->trans_begin();
        $this->db->insert('at_epaymentreal', $detail_ppaycpv);

        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        } else {
            $this->db->trans_commit();
            return 1;
        }
    }

    function insert_detail_cpvr($detail_ppaycpv) {
        $this->db->insert('at_epaymentreal', $detail_ppaycpv);
    }

    function get_account_exp_payable($expCategoryID/* , $curren_code */) {
        $this->db->select('sys_expa.AccountNumber');
        $this->db->from('sys_expenseaccount as sys_expa');

        $this->db->join('at_compaccountgl as cg', 'cg.AccountNumber = sys_expa.AccountNumber', 'INNER');
        $this->db->join('am_accountgl as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join('am_branch as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');
        $this->db->where('cg.GLSaldoYears', date('Y'));

        /* if($curren_code == 'IDR'){
          $this->db->where('ag.CurrencyCode', 'IDR');
          }
          else{
          $this->db->where('ag.CurrencyCode', $curren_code);
          } */

        $this->db->where('sys_expa.ExpCategoryID', $expCategoryID);
        $this->db->where('sys_expa.AccountCode', 'Payable');

        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AccountNumber = $row->AccountNumber;
            }

            return $AccountNumber;
        } else {
            return FALSE;
        }
    }

    function get_account_exp_relize($expCategoryID/* , $curren_code */) {
        $this->db->select('sys_expa.AccountNumber');
        $this->db->from('sys_expenseaccount as sys_expa');

        $this->db->join('at_compaccountgl as cg', 'cg.AccountNumber = sys_expa.AccountNumber', 'INNER');
        $this->db->join('am_accountgl as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join('am_branch as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');
        $this->db->where('cg.GLSaldoYears', date('Y'));

        /* if($curren_code == 'IDR'){
          $this->db->where('ag.CurrencyCode', 'IDR');
          }
          else{
          $this->db->where('ag.CurrencyCode', $curren_code);
          } */

        $this->db->where('sys_expa.ExpCategoryID', $expCategoryID);
        $this->db->where('sys_expa.AccountCode', 'Realized');

        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AccountNumber = $row->AccountNumber;
            }

            return $AccountNumber;
        } else {
            return FALSE;
        }
    }

    function get_account_advance_cashbon($expCategoryID) {
        $this->db->select('sys_expa.AccountNumber');
        $this->db->from('sys_expenseaccount as sys_expa');

        $this->db->join('at_compaccountgl as cg', 'cg.AccountNumber = sys_expa.AccountNumber', 'INNER');
        $this->db->join('am_accountgl as ag', 'ag.AccountNumber = cg.AccountNumber', 'INNER');
        $this->db->join('am_branch as br', 'br.BranchCode = cg.BranchCode', 'INNER');
        $this->db->where('cg.CompanyCode', $this->session->userdata('company'));
        $this->db->where('cg.BranchCode', $this->session->userdata('branch'));
        $this->db->where('(case when br.BranchStatus = 0 then cg.PLStatus in (0, 1) else cg.PLStatus = 1 end)');
        $this->db->where('cg.GLSaldoYears', date('Y'));

        /* if($curren_code == 'IDR'){
          $this->db->where('ag.CurrencyCode', 'IDR');
          }
          else{
          $this->db->where('ag.CurrencyCode', $curren_code);
          } */

        $this->db->where('sys_expa.ExpCategoryID', $expCategoryID);
        $this->db->where('sys_expa.AccountCode', 'Expense');

        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AccountNumber = $row->AccountNumber;
            }

            return $AccountNumber;
        } else {
            return FALSE;
        }
    }

    /* function get_account_advance_cashbon($expCategoryID){
      $company = $this->session->userdata('company');
      $this->db->select('sys_expenseaccount.AccountNumber');
      //$this->db->from('at_compaccountgl');
      $this->db->from('sys_expenseaccount');
      $this->db->join('sys_expensecategory, sys_expensecategory.ExpCategoryID = sys_expenseaccount.ExpCategoryID');
      $this->db->where('sys_expenseaccount.ExpCategoryID', $expCategoryID);
      $this->db->where('sys_expenseaccount.AccountCode', 'Expense');
      $this->db->where('sys_expenseaccount.CompanyCode',$company);
      $query = $this->db->get();

      if ($query->num_rows() > 0) {
      foreach ($query->result() as $row) {
      $AccountNumber = $row->AccountNumber;
      }

      return $AccountNumber;
      }
      else {
      return FALSE;
      }
      } */

    function get_account_bank_payment($EBonNumber) {
        $this->db->select('BankPayment');
        $this->db->from('at_expensecashbon');
        $this->db->where('EBonNumber', $EBonNumber);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BankPayment = $row->BankPayment;
            }

            return $BankPayment;
        } else {
            return FALSE;
        }
    }

    function insert_journal_expense_payment_debit($data_journal_exp_debit) {
        // $this->db->trans_begin();
        $this->db->insert('at_eprealjournal', $data_journal_exp_debit);

        // if ($this->db->trans_status() === FALSE) {
        // 	$this->db->trans_rollback();
        // 	return 0;
        // }
        // else {
        // 	$this->db->trans_commit();
        // 	return 1;
        // }
    }

    function insert_journal_expense_payment_credit($data_journal_exp_credit) {
        $this->db->insert('at_eprealjournal', $data_journal_exp_credit);

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function insert_journal_expense_crv_debit($data_journal_exp_crv_debit) {
        $this->db->insert('at_eprealjournal', $data_journal_exp_crv_debit);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function insert_journal($data_journal) {
        $this->db->insert('at_eprealjournal', $data_journal);
    }

    function insert_journal_bank_crv_debit($data_journal_bank_crv_debit) {
        $this->db->insert('at_eprealjournal', $data_journal_bank_crv_debit);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function insert_journal_expense_crv_credit($data_journal_crv_credit) {
        $this->db->insert('at_eprealjournal', $data_journal_crv_credit);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function insert_journal_expense_relize_credit($data_journal_relize_credit) {
        $this->db->insert('at_eprealjournal', $data_journal_relize_credit);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function insert_journal_expense_cpv_debit($data_journal_exp_cpv_debit) {
        $this->db->insert('at_eprealjournal', $data_journal_exp_cpv_debit);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function insert_journal_bank_cpv_credit($data_journal_bank_cpv_credit) {
        $this->db->insert('at_eprealjournal', $data_journal_bank_cpv_credit);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function insert_journal_expense_cpv_credit($data_journal_cpv_credit) {
        $this->db->insert('at_eprealjournal', $data_journal_cpv_credit);

        // if ($this->db->trans_status() === FALSE) {
        // 	return 0;
        // }
        // else {
        // 	return 1;
        // }
    }

    function get_detail($id) {
        $this->db->select('gr.*,ep.BankReceipt');
        $this->db->from($this->table . ' as gr');
        $this->db->join('at_epaymentreal as ep', 'gr.EPayNumber = ep.EPayNumber', 'INNER');
        $this->db->join($this->employee . ' as em', 'em.EmployeeCode=gr.CreatedBy', 'inner');
        $this->db->where('gr.EPayNumber', $id);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function get_cashbon_number($id) {
        $this->db->select('EBonNumber');
        $this->db->from('at_expensepayment');
        $this->db->where('EPayNumber', $id);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $EBonNumber = $row->EBonNumber;
            }

            return $EBonNumber;
        } else {
            return FALSE;
        }
    }

    function get_amount_cashbon($ebonNumber) {
        $this->db->select('EBonAmount,CurrencyRate');
        $this->db->from('at_expensecashbon');
        $this->db->where('EBonNumber', $ebonNumber);
        $query = $this->db->get()->result_array();

        if (count($query) > 0) {
            return $query[0];
        } else {
            return FALSE;
        }
    }

    function get_date_cashbon($ebonNumber) {
        $this->db->select('EBonDate');
        $this->db->from('at_expensecashbon');
        $this->db->where('EBonNumber', $ebonNumber);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $EBonDate = $row->EBonDate;
            }

            return $EBonDate;
        } else {
            return FALSE;
        }
    }

    function get_rate_cashbon($ebonNumber) {
        $this->db->select('CurrencyRate');
        $this->db->from('at_expensecashbon');
        $this->db->where('EBonNumber', $ebonNumber);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $CurrencyRate = $row->CurrencyRate;
            }

            return $CurrencyRate;
        } else {
            return FALSE;
        }
    }

    function get_bank_cashbon($ebonNumber) {
        $this->db->select('BankPayment');
        $this->db->from('at_expensecashbon');
        $this->db->where('EBonNumber', $ebonNumber);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BankPayment = $row->BankPayment;
            }

            return $BankPayment;
        } else {
            return FALSE;
        }
    }

    function get_amount_expense_payment($id) {
        $this->db->select('sum(ExpenseAmount) as ExpenseAmount');
        $this->db->from('at_epaydetail');
        $this->db->where('EPayNumber', $id);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $ExpenseAmount = $row->ExpenseAmount;
            }

            return $ExpenseAmount;
        } else {
            return FALSE;
        }
    }

    function get_detail_item($number) {
        $this->db->_protect_identifiers = FALSE;
        $this->db->select("ExpCategoryID as ExpDetailID, ExpCategoryName as ExpDetailName, ExpenseDesc as ExpDetailDesc, ExpenseAmount as ItemAmount, EPayDetailID");
        $query = $this->db->get_where($this->detail, array('EPayNumber' => $number));
        $this->db->_protect_identifiers = TRUE;
        $result = $query->result();
        return json_encode($result);
    }

    function get_detail_actual($number) {
        $this->db->select("
							BankReceipt as AccountNumber, EPRealDate  as CrCvDate, 
							EPRealAmount  as AmountCRV, at_expensepayment.EPayAmount, 
							at_epaymentreal.EPRealTransType, at_expensecashbon.EBonAmount
						");
        $this->db->_protect_identifiers = FALSE;
        $this->db->join('at_expensepayment', 'at_epaymentreal.EPayNumber=at_expensepayment.EPayNumber');
        $this->db->join('at_expensecashbon', 'at_expensepayment.EBonNumber=at_expensecashbon.EBonNumber');
        //$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        $query = $this->db->get_where('at_epaymentreal', array('at_epaymentreal.EPayNumber' => $number));
        $this->db->_protect_identifiers = TRUE;
        return $query->result();
    }

    function get_acc_name($acc_no) {
        $this->db->select('AccountName');
        $this->db->from('am_accountgl');
        $this->db->where('AccountNumber', $acc_no);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AccountName = $row->AccountName;
            }

            return $AccountName;
        } else {
            return FALSE;
        }
    }

    function get_crv_number_detail($EPayNumber) {
        $this->db->select('EPRealNumber');
        $this->db->from('at_epaymentreal');
        $this->db->where('EPayNumber', $EPayNumber);
        $this->db->where('EPRealTransType', 1);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $EPRealNumber = $row->EPRealNumber;
            }

            return $EPRealNumber;
        } else {
            return FALSE;
        }
    }

    function get_cpv_number_detail($EPayNumber) {
        $this->db->select('EPRealNumber');
        $this->db->from('at_epaymentreal');
        $this->db->where('EPayNumber', $EPayNumber);
        $this->db->where('EPRealTransType', 2);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $EPRealNumber = $row->EPRealNumber;
            }

            return $EPRealNumber;
        } else {
            return FALSE;
        }
    }

    function get_journal($EPayNumber) {
        $this->db->select('EPRealAccountNumber');
        $this->db->from('at_eprealjournal');
        $this->db->where('EPRealNumber_BOut', $EPayNumber);
        //$this->db->where('EPRealAccountNumber', $account_exp_relize);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $EPRealAccountNumber = $row->EPRealAccountNumber;
            }

            return $EPRealAccountNumber;
        } else {
            return FALSE;
        }
    }

    function update_payment_cashbon($data, $id) {
        $this->db->update($this->table, $data, array('EPayNumber' => $id));

        // if ($this->db->trans_status() == FALSE) {
        //     return 0;
        // }
        // else {
        //     return 1;
        // }
    }

    function update_detail_payment_cashbon($data, $id) {
        $this->db->update($this->detail, $data, array('EPayDetailID' => $id));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_detail_crv($data, $id) {
        $this->db->update('at_epaymentreal', $data, array('EPayNumber' => $id));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_expense_crv_debit($data_journal_exp_crv_debit, $id, $account_exp_payable) {
        $this->db->update('at_eprealjournal', $data_journal_exp_crv_debit, array('EPRealNumber_BOut' => $id, 'EPRealAccountNumber' => $account_exp_payable));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_bank_crv_debit($data_journal_bank_crv_debit, $id, $AccountNumber) {
        $this->db->update('at_eprealjournal', $data_journal_bank_crv_debit, array('EPRealNumber_BOut' => $id, 'EPRealAccountNumber' => $AccountNumber));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_expense_crv_credit($data_journal_crv_credit, $id, $account_adv_cashbon) {
        $this->db->update('at_eprealjournal', $data_journal_crv_credit, array('EPRealNumber_BOut' => $id, 'EPRealAccountNumber' => $account_adv_cashbon));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_detail_cpv($data, $id) {
        $this->db->update('at_epaymentreal', $data, array('EPayNumber' => $id));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_expense_cpv_debit($data, $id, $account_exp_payable) {
        $this->db->update('at_eprealjournal', $data, array('EPRealNumber_BOut' => $id, 'EPRealAccountNumber' => $account_exp_payable));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_bank_cpv_credit($data, $id, $AccountNumber) {
        $this->db->update('at_eprealjournal', $data, array('EPRealNumber_BOut' => $id, 'EPRealAccountNumber' => $AccountNumber));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_expense_cpv_credit($data, $id, $account_adv_cashbon) {
        $this->db->update('at_eprealjournal', $data, array('EPRealNumber_BOut' => $id, 'EPRealAccountNumber' => $account_adv_cashbon));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_expense_payment_debit($data, $id) {
        $this->db->update('at_eprealjournal', $data, array('EPRealNumber_BOut' => $id));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_expense_payment_credit($data, $id) {
        $this->db->update('at_eprealjournal', $data, array('EPRealNumber_BOut' => $id));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_relize_credit($data, $id, $account_exp_relize) {
        $this->db->update('at_eprealjournal', $data, array('EPRealNumber_BOut' => $id, 'EPRealAccountNumber' => $account_exp_relize));

        if ($this->db->trans_status() === FALSE) {
            return 0;
        } else {
            return 1;
        }
    }

    function get_status_journal($EPayNumber) {
        $this->db->select('EPRealTransType');
        $this->db->from('at_epaymentreal');
        $this->db->where('EPayNumber', $EPayNumber);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $EPRealTransType = $row->EPRealTransType;
            }

            return $EPRealTransType;
        } else {
            return FALSE;
        }
    }

    function get_actual_number($payment_number) {
        $this->db->select('EPRealNumber');
        $this->db->from('at_epaymentreal');
        $this->db->where('EPayNumber', $payment_number);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $EPRealNumber = $row->EPRealNumber;
            }

            return $EPRealNumber;
        } else {
            return FALSE;
        }
    }

    function get_branch_code($payment_number) {
        $this->db->select('BranchCode');
        $this->db->from('at_expensepayment');
        $this->db->where('EPayNumber', $payment_number);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BranchCode = $row->BranchCode;
            }

            return $BranchCode;
        } else {
            return FALSE;
        }
    }

    function get_date_journal_payment($payment_number) {
        $this->db->select('*');
        $this->db->from('at_expensepayment');
        $this->db->where('EPayNumber', $payment_number);
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($result);
    }

    function get_detail_payment_description($payment_number) {
        $this->db->select('ExpenseDesc');
        $this->db->from('at_epaydetail');
        $this->db->where('EPayNumber', $payment_number);
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($result);
    }

    function get_journal_payment($get_journal_payment, $BranchCode) {
        $this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, at_eprealjournal.EPRealSaldoCredit, 
						   at_eprealjournal.EPRealSaldoDebet, at_eprealjournal.EPRealNumber,at_eprealjournal.AmountCurrency');
        $this->db->from('at_compaccountgl');
        $this->db->join('at_eprealjournal', 'at_compaccountgl.AccountNumber=at_eprealjournal.EPRealAccountNumber');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        $this->db->where('at_compaccountgl.BranchCode', $BranchCode);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('at_compaccountgl.TransStatus', 2);
        $this->db->where('at_eprealjournal.EPRealNumber_BOut', $get_journal_payment);
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($result);
    }

    function update_status_cashbon($EBonNumber, $status_cashbon) {
        $this->db->where('EBonNumber', $EBonNumber);
        $this->db->update('at_expensecashbon', $status_cashbon);
    }

    function update_status_payment($EPayNumber, $status_payment) {
        $this->db->where('EPayNumber', $EPayNumber);
        return $this->db->update('at_expensepayment', $status_payment);
    }

    function status_finished($app_finished, $payment_number) {
        $this->db->where('EPayNumber', $payment_number);
        return $this->db->update('at_expensepayment', $app_finished);
    }

    function delete_payment_cashbon($EPayNumber) {
        $this->db->where('EPayNumber', $EPayNumber);
        return $this->db->delete($this->table);
    }

    function delete_detail_payment_cashbon($EPayNumber) {
        $this->db->where('EPayNumber', $EPayNumber);
        return $this->db->delete($this->detail);
    }

    function delete_detail_crv($EPayNumber) {
        $this->db->where('EPayNumber', $EPayNumber);
        $this->db->delete('at_epaymentreal');
    }

    function delete_journal_expense_crv($EPayNumber) {
        $this->db->where('EPRealNumber_BOut', $EPayNumber);
        $this->db->delete('at_eprealjournal');
    }

    function delete_detail_cpv($EPayNumber) {
        $this->db->where('EPayNumber', $EPayNumber);
        $this->db->delete('at_epaymentreal');
    }

    function delete_journal_cpv($EPayNumber) {
        $this->db->where('EPRealNumber_BOut', $EPayNumber);
        $this->db->delete('at_eprealjournal');
    }

    function delete_journal_payment($EPayNumber) {
        $this->db->where('EPRealNumber_BOut', $EPayNumber);
        $this->db->delete('at_eprealjournal');
    }

    function update_trans_status($payment_number, $data) {
        $this->db->where('EPayNumber', $payment_number);
        $this->db->update('at_expensepayment', $data);
    }

    function cek_status_by_number_payment($payment_number) {
        $this->db->select('TransStatus');
        $this->db->from('at_expensepayment');
        $this->db->where('EPayNumber', $payment_number);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $TransStatus = $row->TransStatus;
            }

            return $TransStatus;
        } else {
            return FALSE;
        }
    }

    function approval_status($data, $payment_number) {
        $this->db->where('EPayNumber', $payment_number);
        $this->db->update($this->table, $data);
    }

    function cek_app($payment_number) {
        $this->db->select('AppLevel1Status, AppLevel2Status');
        $this->db->from('at_expensepayment');
        $this->db->where('EPayNumber', $payment_number);
        $this->db->where('AppLevel1Status', 1);
        $this->db->where('AppLevel2Status', 1);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $app = 1;
            }
            return $app;
        } else {
            $app = 0;
            return $app;
        }
    }

    function cek_status_app_mgr($payment_number) {
        $this->db->select('AppLevel1Status');
        $this->db->from('at_expensepayment');
        $this->db->where('EPayNumber', $payment_number);
        $this->db->where('AppLevel1Status', 1);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AppLevel1Status = $row->AppLevel1Status;
            }
            return $AppLevel1Status;
        } else {
            return FALSE;
        }
    }

    function cek_status_app_dir($payment_number) {
        $this->db->select('AppLevel2Status');
        $this->db->from('at_expensepayment');
        $this->db->where('EPayNumber', $payment_number);
        $this->db->where('AppLevel2Status', 1);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AppLevel2Status = $row->AppLevel2Status;
            }
            return $AppLevel2Status;
        } else {
            return FALSE;
        }
    }

    function get_detail_pembiayaan($payment_number) {
        $this->db->select('*');
        $this->db->from('at_epaydetail');
        $this->db->where('EPayNumber', $payment_number);
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($result);
    }

    function get_at_expensepayment($payment_number) {
        $this->db->select('*');
        $this->db->from('at_expensepayment');
        $this->db->where('EPayNumber', $payment_number);
        $query = $this->db->get();

        $result = $query->row();
        return $result;
    }

    /* ======================penomeran transaksi new======================================================= */

    function check_code_exist($EPayNumber) {
        $query = $this->db->get_where($this->table, array('EPayNumber' => $EPayNumber), 1, 0);

        if ($query->num_rows() > 0) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function create_epay_number($date) {
        $branch = $this->session->userdata('branch');

        if ($date != '') {
            $month = date('m', strtotime($date));
            $year = date('y', strtotime($date));
        } else {
            $month = date('m');
            $year = date('y');
        }

        $pa_number = $branch . "/" . $month . $year . "EP/";

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(EPayNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(EPayNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('EPayNumber', $pa_number);
        $query = $this->db->get($this->table);
        $this->db->_protect_identifiers = true;

        $num = $query->row()->New_Number;
        $long = strlen($num);

        if ($long == 1) {
            $number = '00' . $num;
        } elseif ($long == 2) {
            $number = '0' . $num;
        } else {
            $number = $num;
        }

        $pa_new_number = $pa_number . $number;

        while ($this->check_code_exist($pa_new_number)) {
            $num++;
            $long = strlen($num);

            if ($long == 1) {
                $number = '00' . $num;
            } elseif ($long == 2) {
                $number = '0' . $num;
            } else {
                $number = $num;
            }

            $pa_new_number = $pa_number . $number;
        }

        return $pa_new_number;
    }

    function cek_kode($crv_number) {
        $query = $this->db->get_where($this->crv_table, array('EPRealNumber' => $crv_number), 1, 0);

        if ($query->num_rows() > 0) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function create_crv_number($date, $bank_code_masuk) {
        $branch = $this->session->userdata('branch');

        if ($date != '') {
            $month = date('m', strtotime($date));
            $year = date('y', strtotime($date));
        } else {
            $month = date('m');
            $year = date('y');
        }

        $pa_number = $branch . "/" . $month . $year . "ER/" . $bank_code_masuk;

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(EPRealNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(EPRealNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('EPRealNumber', $pa_number);
        $query = $this->db->get($this->crv_table);
        $this->db->_protect_identifiers = true;

        $num = $query->row()->New_Number;
        $long = strlen($num);

        if ($long == 1) {
            $number = '00' . $num;
        } elseif ($long == 2) {
            $number = '0' . $num;
        } else {
            $number = $num;
        }

        $pa_new_number = $pa_number . $number;

        /* ============= cek new number epreal ========================== */
        $check_new_number = $this->cek_kode($pa_new_number);

        if ($check_new_number == true) {

            $num++;
            $long = strlen($num);

            if ($long == 1) {
                $number = '00' . $num;
            } elseif ($long == 2) {
                $number = '0' . $num;
            } else {
                $number = $num;
            }

            $pa_new_number = $pa_number . $number;
        }

        return $pa_new_number;
    }

    // function create_crv_number($bank_code_masuk){
    //        $branch_code    = $this->session->userdata('branch');
    //        $month	= date('m');
    //        $year	= date('y');
    //        $crv_number    = $branch_code . "/" . $month . $year . "ER/".$bank_code_masuk;
    //        $this->db->like('EPRealNumber', $crv_number);
    //        $num    = $this->db->get($this->crv_table)->num_rows() + 1;
    //        $long   = strlen($num);
    //        if($long == 1){
    //            $number = '00'. $num;
    //        }
    //        elseif($long == 2){
    //            $number = '0'. $num;
    //        }
    //        else{
    //            $number = $num;
    //        }
    //        $new_crv_number = $crv_number . $number;
    //        while($this->cek_kode($new_crv_number)){
    //            $num++;
    //            $long = strlen($num);
    //            if($long == 1){
    //                $number = '00' . $num;
    //            }
    //            elseif($long == 2){
    //                $number = '0' . $num;
    //            }
    //            else{
    //                $number = $num;
    //            }
    //            $new_crv_number = $crv_number . $number;		
    //        }
    //        return $new_crv_number;
    //    }

    function get_detail_item_for_payment($ExpDetailID) {
        $this->db->_protect_identifiers = false;
        $this->db->select("*, '0' as ItemAmount");
        $this->db->where_in('ExpDetailID', $ExpDetailID);
        $query = $this->db->get('sys_expensedetail');
        $this->db->_protect_identifiers = true;

        return $query->result();
    }

    function get_employee($depCode) {
        $branch = $this->session->userdata('branch');
        $this->db->select('*');
        $this->db->from($this->employee);
        $this->db->where('DepartmentCode', $depCode);
        $this->db->where('BranchCode', $branch);
        $this->db->order_by('EmployeeCode', 'ASC');
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($result);
    }

    function get_data_cashbon($no_piutang) {
        $this->db->select('cb.EmployeeCode,cb.TransStatus,cb.EBonLockStatus');
        $this->db->from($this->tbl_cashbon . ' as cb');
        $this->db->where('cb.EBonNumber', $no_piutang);

        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            $row = $query->row();

            return $row;
        } else {
            return false;
        }
    }

    function get_detail_item_print($payment_no) {
        $this->db->select($this->table . '.*, ');
        $this->db->from($this->table);
        $this->db->join('');
        $this->db->join('am_employee as am_prepared', $this->table . '.CreatedBy=am_prepared.EmployeeCode', 'LEFT');
        $this->db->join('am_employee as am_mgr', $this->table . '.AppLevel1Code=am_mgr.EmployeeCode', 'LEFT');

        $this->db->where('EPayNumber', $payment_no);
        $query = $this->db->get();
        return $query->row();
    }

    function m_cek_status_cashbon($no_cashbon) {
        $this->db->where('EBonNumber', $no_cashbon);
        $this->db->where('TransStatus', 1);

        $query = $this->db->get($this->tbl_cashbon)->result_array();

        if (count($query) > 0) {
            return true;
        } else {
            return false;
        }
    }

    function m_get_total_cashbon($no_cashbon) {
        $this->db->select('EBonAmount,JVoucherBalance');
        $this->db->where('EBonNumber', $no_cashbon);
        $this->db->where('TransStatus', 1);

        $query = $this->db->get($this->tbl_cashbon)->result_array();

        if (count($query) > 0) {
            return $query[0];
        } else {
            return false;
        }
    }

    function update_is_lock($data_cashbon, $no_cashbon) {
        $this->db->update($this->tbl_cashbon, $data_cashbon, array('EBonNumber' => $no_cashbon));
    }

    function update_approved_data($data_approval, $txt_payment_no) {
        $this->db->update($this->table, $data_approval, array('EPayNumber' => $txt_payment_no));
    }

    function update_to_reverse_payment($txt_payment_no, $data_reversed_payment) {
        $this->db->update($this->table, $data_reversed_payment, array('EPayNumber' => $txt_payment_no));
    }

    function update_to_reverse_cashbon($no_cashbon, $data_reversed_cashbon) {
        $this->db->update($this->tbl_cashbon, $data_reversed_cashbon, array('EBonNumber' => $no_cashbon));
    }

    function get_to_print_detail($EPayNumber) {

        $this->db->select($this->detail . '.ExpCategoryID,' . $this->detail . '.ExpCategoryName,' . $this->detail . '.ExpenseDesc,' . $this->detail . '.ExpenseAmount');
        $this->db->from($this->detail);
        $this->db->where($this->detail . '.EPayNumber', $EPayNumber);

        $query = $this->db->get();
        return $query->result_array();
    }

    function get_detail_to_print($EPayNumber) {
        $this->db->select('am_branch.BranchName,am_department.DepartmentName,emp_bon.EmployeeName,' . $this->table . '.EPayNumber,' . $this->table . '.EPayDate,' . $this->table . '.EPayDesc,
							emp_mng.EmployeeName as nm_manager,emp_prepared.EmployeeName as nm_prepared,' . $this->table . '.EBonNumber,' . $this->tbl_cashbon . '.EBonAmount,' . $this->table . '.CurrencyCode,
							' . $this->table . '.EPayRate as pay_rate,' . $this->tbl_cashbon . '.CurrencyRate as bon_rate,' . $this->table . '.EPayAmount, am_branch.BranchAddress,' . $this->table . '.TransStatus');
        $this->db->from($this->table);
        $this->db->join($this->tbl_cashbon, $this->tbl_cashbon . '.EBonNumber=' . $this->table . '.EBonNumber', 'INNER');
        $this->db->join('am_branch', 'am_branch.BranchCode=' . $this->table . '.BranchCode', 'INNER');
        $this->db->join('am_department', 'am_department.DepartmentCode=' . $this->table . '.DepartmentCode', 'INNER');
        $this->db->join('am_employee as emp_bon', 'emp_bon.EmployeeCode=' . $this->table . '.EmployeeCode', 'INNER');
        $this->db->join('am_employee as emp_mng', 'emp_mng.EmployeeCode=' . $this->table . '.AppLevel1Code', 'LEFT');
        $this->db->join('am_employee as emp_prepared', 'emp_prepared.EmployeeCode=' . $this->table . '.CreatedBy', 'LEFT');
        $this->db->where('EPayNumber', $EPayNumber);

        $query = $this->db->get()->result_array();

        if (count($query) > 0) {
            return $query[0];
        } else {
            return false;
        }
    }

    function delete_reconcile($TransNumber) {
        $this->db->where('TransNumber', $TransNumber);
        $this->db->delete($this->reconcile);
    }

    function check_period_end($periodend) {
        $this->db->select('PeriodEnd');
        $this->db->from($this->period);
        $this->db->where('PeriodEnd', $periodend);

        $query = $this->db->get()->result_array();

        if (count($query) > 0) {
            return true;
        } else {
            return false;
        }
    }

    function get_company_by_branch($sess_branch) {
        $this->db->select('CompanyName,BranchName');
        $this->db->from('am_company');
        $this->db->join('am_branch', 'am_branch.CompanyCode = am_company.CompanyCode', 'inner');
        // $this->db->join($this->table,$this->table.'.BranchCode = am_branch.BranchCode','inner');
        $this->db->where('am_branch.BranchCode', $sess_branch);
        $query = $this->db->get();
        return $query->row();
    }

    function get_all_to_print_LAMA($sess_branch) {
        $this->db->select('cb.EPayDate,cb.EPayNumber,
			cb.EPayDesc,cb.EPayAmount,br.BranchName, dp.DepartmentName,em.EmployeeName');
        $this->db->from($this->table . ' as cb');
        $this->db->join($this->department . ' as dp', 'dp.DepartmentCode=cb.DepartmentCode', 'inner');
        $this->db->join($this->employee . ' as em', 'em.EmployeeCode=cb.EmployeeCode', 'inner');
        $this->db->join($this->branch . ' as br', 'br.BranchCode=cb.BranchCode', 'inner');
        $this->db->where('cb.BranchCode', $sess_branch);

        $query = $this->db->get();
        return $query->result();
    }

    function get_all_to_print($data_filter) {
        /* =================================post dari find di kiri==================================================== */
        $CompanyCode = $data_filter['cmb_company'];
        $BranchCode = $data_filter['cmb_branch'];
        $Number = $data_filter['txt_IDNumber'];
        $Desc = $data_filter['txt_Description'];
        $DateFrom = $data_filter['txt_DateFrom'];
        $DateTo = $data_filter['txt_DateTo'];
        $TransStatus = $data_filter['chk_TransStatus'];
        /* ============================================end============================================================ */
        
        /* ==========================================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('am_branch.BranchName, am_department.DepartmentName, am_employee.EmployeeName, at_expensepayment.EPayNumber,created.EmployeeName as nm_createdby,
						   at_expensepayment.EPayDate, sum(at_epaydetail.ExpenseAmount) as amountPayment, at_expensepayment.EPayDesc, at_expensepayment.TransStatus, at_expensecashbon.EBonAmount');
        $this->db->from('at_expensepayment');
        $this->db->join('at_epaydetail', 'at_expensepayment.EPayNumber=at_epaydetail.EPayNumber', 'INNER');
        $this->db->join('am_branch', 'at_expensepayment.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_department', 'at_expensepayment.DepartmentCode=am_department.DepartmentCode', 'INNER');
        $this->db->join('am_employee', 'at_expensepayment.EmployeeCode=am_employee.EmployeeCode', 'INNER');
        $this->db->join('at_expensecashbon', 'at_expensepayment.EBonNumber=at_expensecashbon.EBonNumber', 'INNER');
        $this->db->join('am_employee as created', 'at_expensepayment.CreatedBy=created.EmployeeCode', 'INNER');

        /* ===================================find by number dan desc========================================================= */
        if ($Number != "") {
            $this->db->like('at_expensepayment.EPayNumber', $Number);
        }
        if ($Desc != "") {
            $this->db->like('at_expensepayment.EPayDesc', $Desc);
        }
        /* =============================================end=================================================================== */

        /* ===================================find by branch code========================================================= */
        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where('at_expensepayment.BranchCode', $BranchCode);
        } else {
            /*
              inisialisasi untuk loading pertama page branch info
             */
            //----------------kondisi by company-------------------------------------//
            /*
              branch status 0 = HO
              branch status 1 = depo/branch
              jangan lupa sesion data di atas untuk setiap kondisi
              jangan lupa tambahin case when trans status
             */
            $this->db->where('at_expensepayment.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

        /* =====================================================find by date================================================== */
        if ((($DateFrom != '') && ($DateTo != ''))) {
            if ($DateFrom <= $DateTo) {
                $this->db->where('at_expensepayment.EPayDate >=', date("Y-m-d", strtotime($DateFrom)));
                $this->db->where('at_expensepayment.EPayDate <=', date("Y-m-d", strtotime($DateTo)));
            }
        }
        /* ======================================================end=========================================================== */

        /* =====================================================find by status================================================== */
        if (!empty($TransStatus)) {
            $this->db->where_in('at_expensepayment.TransStatus', $TransStatus);
        } else {
            /*
              trans status 0,1 adalah aktif
             */
            $this->db->where_in('at_expensepayment.TransStatus', array(0, 1, 4, 5, 6, 7, 8));
        }
        /* =======================================================end======================================================== */

        /* ============================================== group By ========================================================== */
        $this->db->group_by('at_expensepayment.EPayNumber');

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by($this->table . '.TransStatus', 'ASC');
        $this->db->order_by($this->table . '.EPayNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->get();
        return $query->result();
    }

    function insert_transsaction($trans_data) {

        $this->db->trans_start();

        /* ======================= insert group transaction ============================ */
        $this->insert_payment_cashbon($trans_data['insert_group']['data']);

        for ($x = 0; $x < count($trans_data['insert_detail']['data']); $x++) {
            /* ===================== insert detail ================================ */
            $this->insert_detail_payment_cashbon($this->trans_data['insert_detail']['data'][$x]);
            /* ===================== end insert journal =============================== */
        }

        $this->insert_detail_cpvr($trans_data['insert_detail_cpvr']['data']);

        /* ===================== insert journal ================================ */
        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->insert_journal($trans_data['insert_journal']['data'][$x]);
        }
        /* ===================== end insert journal =============================== */

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function update_transsaction($trans_data) {

        $this->db->trans_start();

        /* ====================== update group ==================== */
        $this->update_payment_cashbon($trans_data['update_group']['data'], $trans_data['update_group']['TransNumber']);

        /* ========================= delete journal =============================== */
        $this->delete_journal_expense_crv($trans_data['delete_journal']['TransNumber']);

        /* ========================= delete detail CPV/CPR =============================== */
        $this->delete_detail_cpv($trans_data['delete_detail_cpv']['TransNumber']);

        for ($x = 0; $x < count($trans_data['update_detail']['data']); $x++) {
            $this->update_detail_payment_cashbon($trans_data['update_detail']['data'][$x], $trans_data['update_detail']['EPayDetailID'][$x]);
        }

        $this->insert_detail_cpvr($trans_data['insert_detail_cpvr']['data']);

        /* ===================== insert journal ================================ */
        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->insert_journal($trans_data['insert_journal']['data'][$x]);
        }
        /* ===================== end insert journal =============================== */

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function approve_transsaction($trans_data) {

        $this->db->trans_start();

        /* ============================================= approval transaction =================================================== */
        $this->update_approved_data($trans_data['approve_data']['data'], $trans_data['approve_data']['TransNumber']);

        /* ============================================= update islock cashbon =================================================== */
        $this->update_is_lock($trans_data['update_is_lock']['data'], $trans_data['update_is_lock']['TransNumber']);

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function reverse_transsaction($trans_data) {
        $this->db->trans_start();

        /* ====================================================== reverse payment ==================================================== */
        $this->update_to_reverse_payment($trans_data['reverse_data']['TransNumber'], $trans_data['reverse_data']['data']);

        /* ====================================================== reverse payment ==================================================== */
        $this->update_to_reverse_cashbon($trans_data['update_to_reverse_cashbon']['TransNumber'], $trans_data['update_to_reverse_cashbon']['data']);

        /* ====================================================== delete reconcile ============================================= */
        $this->delete_reconcile($trans_data['delete_reconcile']['TransNumber']);

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

    function suspend_transsaction($trans_data) {

        $this->db->trans_start();

        /* ===================================== suspend payment ============================================ */
        $this->update_trans_status($trans_data['suspend_data']['TransNumber'], $trans_data['suspend_data']['data']);

        /* ===================================== unlock cashbon ============================================ */
        $this->update_status_cashbon($trans_data['update_status_cashbon']['TransNumber'], $trans_data['update_status_cashbon']['data']);

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return false;
        } else {
            return true;
        }
    }

}
