<?php

class M_loan_funding extends CI_Model {

    public $table = "at_loanfunding";
    public $payment_journal = "at_loanpayment_journal";
    public $payment = "at_loanpayment";
    public $company = "am_company";
    public $branch = "am_branch";
    public $account = "at_compaccountgl";
    public $acc_gl = "am_accountgl";
    public $employee = "am_employee";
    public $journal = "at_lfjournal";
    public $reconcile = "at_bankreconcille";
    public $period = "at_periodend";
    public $sub_type = "am_accountsubtype";
    public $acc_type = "am_accounttype";
    public $department = "am_department";

    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('lf.LFNumber, lf.LFDate, lf.LFAmount, 
			ag.AccountName as Loan_Account, gl.AccountName as Receipt_Account, lf.LFDesc, lf.TransStatus, 
			created.EmployeeName as created_name');
        $this->db->from($this->table . ' as lf');
        $this->db->join($this->branch . ' as br', 'br.BranchCode=lf.BranchCode', 'INNER');
        $this->db->join($this->acc_gl . ' as ag', 'ag.AccountNumber=lf.LFAccount', 'inner');
        $this->db->join($this->acc_gl . ' as gl', 'gl.AccountNumber=lf.ReceiptAccount', 'inner');
        $this->db->join('am_employee as created', 'created.EmployeeCode=lf.CreatedBy', 'LEFT');

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

        /* ===================================find by branch code========================================================= */
        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where('lf.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('lf.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

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

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

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

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

    function suspend_data($LFNumber, $data_suspend) {
        $this->db->update($this->table, $data_suspend, array('LFNumber' => $LFNumber));
    }

    function get_last_piutang_number($BranchCode) {
        $qty = "SELECT MAX(LFNumber) as LFNumber FROM at_loanfunding WHERE BranchCode='$BranchCode'";
        return $this->db->query($qty)->result();
    }

    //custom data
    function get_company_edit($branch_explode) {
        //$jobtitle = $this->session->userdata("jobtitle");
        $this->db->select('*');
        $this->db->from('am_branch');

        //$this->db->where('CompanyCode', $CompanyCode);
        $this->db->where('BranchCode', $branch_explode);

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

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

    function m_get_loan_account($branch) {
        $this->db->select('ag.AccountNumber,ag.AccountName,ag.CurrencyCode');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->acc_gl . ' as ag', 'ag.AccountNumber=ac.AccountNumber', 'inner');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID=ag.AccountSubTypeID', 'inner');
        $this->db->join($this->acc_type . ' as acc', 'acc.AccountTypeID=sub.AccountTypeID', 'inner');
        $this->db->where_in('acc.AccountTypeName', array('Other Current Liability', 'Long Term Liability'));
        $this->db->where('ac.BranchCode', $branch);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('ac.GLSaldoYears', date('Y'));
        $this->db->where('ac.TransStatus', 2);

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

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

    function m_get_receipt_account($branch, $CurrencyCode) {
        $this->db->select('ag.AccountNumber,ag.AccountName,ag.CurrencyCode');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->acc_gl . ' as ag', 'ag.AccountNumber=ac.AccountNumber', 'inner');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID=ag.AccountSubTypeID', 'inner');
        $this->db->join($this->acc_type . ' as acc', 'acc.AccountTypeID=sub.AccountTypeID', 'inner');
        $this->db->where('ag.CurrencyCode', $CurrencyCode);
        $this->db->where('acc.AccountTypeName', 'Cash / Bank');
        $this->db->where('ac.BranchCode', $branch);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('ac.GLSaldoYears', date('Y'));
        $this->db->where('ac.TransStatus', 2);

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

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

    function get_akun_bank_edit($branch_explode) {
        $company = $this->session->userdata('company');
        $BranchStatus = $this->session->userdata('branch_status');

        $this->db->select('*');
        $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.BranchCode', $branch_explode);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));

        $this->db->where('am_accountgl.AccountSubTypeID', 7);
        $this->db->where('am_accountgl.AccountChild', 1);
        $this->db->where('am_accountgl.CurrencyCode', 'IDR');
        $this->db->where('am_accountgl.TransStatus', 2);

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

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

    function get_karyawan_bycompany_edit($branch_explode) {

        $this->db->select('am_employee.EmployeeCode, am_employee.EmployeeName');
        $this->db->from($this->employee);
        $this->db->join('am_department', 'am_employee.DepartmentCode=am_department.DepartmentCode');
        $this->db->join('am_branch', 'am_employee.BranchCode=am_branch.BranchCode');

        //if($BranchStatus == 1){
        $this->db->where('am_employee.BranchCode', $branch_explode);
        //}
        $this->db->where('am_employee.ResignDate', NULL);
        $query = $this->db->get();

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

    function get_akun_bank($BranchCode) {
        $jobtitle = $this->session->userdata("jobtitle");
        $BranchCode = $this->session->userdata('branch');
        $company = $this->session->userdata('company');
        $BranchStatus = $this->session->userdata('branch_status');

        $this->db->select('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->join('am_accountsubtype', 'am_accountsubtype.AccountSubTypeID=am_accountgl.AccountSubTypeID', 'inner');

        $this->db->where('at_compaccountgl.BranchCode', $BranchCode);
        //$this->db->where('am_branch.BranchStatus', 0);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));

        $this->db->where('am_accountsubtype.AccountTypeName', 'Bank');
        $this->db->where('am_accountgl.CurrencyCode', 'IDR');
        $this->db->where('am_accountgl.AccountChild', 1);
        $this->db->where('at_compaccountgl.TransStatus', 2);

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

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

    function get_coa_piutang_karyawan($com_trnas, $branch_code) {
        $this->db->select('at_compaccountgl.AccountNumber');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber', 'inner');

        $this->db->where('at_compaccountgl.CompanyCode', $com_trnas);
        $this->db->where('at_compaccountgl.BranchCode', $branch_code);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('at_compaccountgl.TransStatus', 2);
        $this->db->like('am_accountgl.AccountName', 'Piutang Karyawan');

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

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

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

    function get_coa_piutang_karyawan_edit($com_trnas) {
        $this->db->select('at_compaccountgl.AccountNumber');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber', 'inner');

        //$this->db->where('at_compaccountgl.CompanyCode', $com_trnas);
        $this->db->where('at_compaccountgl.BranchCode', $com_trnas);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('at_compaccountgl.TransStatus', 2);
        $this->db->like('am_accountgl.AccountName', 'Piutang Karyawan');

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

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

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

    function get_karyawan_bycompany($branch, $company_perusahaan) {
        $jobtitle = $this->session->userdata("jobtitle");
        $BranchStatus = $this->session->userdata('branch_status');

        $this->db->select('am_employee.EmployeeCode, am_employee.EmployeeName');
        $this->db->from($this->employee);
        $this->db->join('am_department', 'am_employee.DepartmentCode=am_department.DepartmentCode');
        $this->db->join('am_branch', 'am_employee.BranchCode=am_branch.BranchCode');
        $this->db->where('am_branch.CompanyCode', $company_perusahaan);
        $this->db->where('am_department.CompanyCode', $company_perusahaan);
        $this->db->where('am_employee.ResignDate', NULL);

        //if($BranchStatus == 1){
        $this->db->where('am_employee.BranchCode', $branch);
        //}

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

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

    // end custom

    function insert_group($data_piutang) {
        $this->db->insert($this->table, $data_piutang);
    }

    function get_expt_piutang($CompanyCode) {
        $this->db->select('ExpCategoryID');
        $this->db->from('sys_expensecategory');
        $this->db->where('ExpCategoryName', 'Piutang Karyawan');
        $this->db->where('CompanyCode', $CompanyCode);
        $query = $this->db->get();

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

            return $ExpCategoryID;
        } else {
            return FALSE;
        }
    }

    function get_account_piutang($CompanyCode, $ExpCategoryID) {
        $this->db->select('AccountNumber');
        $this->db->from('sys_expenseaccount');
        $this->db->join('sys_expensecategory', 'sys_expenseaccount.ExpCategoryID=sys_expensecategory.ExpCategoryID', 'INNER');
        $this->db->where('sys_expenseaccount.ExpCategoryID', $ExpCategoryID);
        $this->db->where('sys_expenseaccount.AccountCode', 'Expense');
        $this->db->where('sys_expensecategory.CompanyCode', $CompanyCode);
        $query = $this->db->get();

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

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

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

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

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

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

    function create_loan_funding_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');
        }

        $jc_number = $branch . "/" . $month . $year . "LF/";

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(LFNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(LFNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('LFNumber', $jc_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;
        }

        $new_jc_number = $jc_number . $number;

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

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

            $new_jc_number = $jc_number . $number;
        }

        return $new_jc_number;
    }

    /* ==============================================================end================================= */

    function update_group($data_loan, $LFNumber) {
        $this->db->where('LFNumber', $LFNumber);
        $this->db->update($this->table, $data_loan);
    }

    function update_journal_debit_piutang($journal_debet, $LFNumber) {
        $this->db->where('LFNumber', $LFNumber);
        $this->db->where('LFSaldoCredit', 0);
        $this->db->update('at_LFjournal', $journal_debet);
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        } else {
            return 1;
        }
    }

    function update_journal_credit_piutang($journal_credit, $LFNumber) {
        $this->db->where('LFNumber', $LFNumber);
        $this->db->where('LFSaldoDebet', 0);
        $this->db->update('at_LFjournal', $journal_credit);
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        } else {
            return 1;
        }
    }

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

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

    function get_date_journal_loan($LFNumber) {
        $this->db->select('gr.*,br.BranchName');
        $this->db->from($this->table . ' as gr');
        $this->db->join($this->branch . ' as br', 'br.BranchCode=gr.BranchCode', 'inner');
        $this->db->where('LFNumber', $LFNumber);
        $query = $this->db->get();

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

    function get_all_journal_loan($LFNumber, $branch) {
        $this->db->select('ag.AccountNumber, ag.AccountName, jr.LFSaldoCredit,jr.AmountCurrency,
						   jr.LFSaldoDebet, jr.LFDesc, jr.LFNumber, jr.LFAccountNumber');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->acc_gl . ' as ag', 'ac.AccountNumber=ag.AccountNumber', 'inner');
        $this->db->join($this->journal . ' as jr', 'ac.AccountNumber=jr.LFAccountNumber', 'inner');
        $this->db->where('ac.BranchCode', $branch);
        $this->db->where('ac.GLSaldoYears', date('Y'));
        $this->db->where('ac.TransStatus', 2);
        $this->db->where('jr.LFNumber', $LFNumber);
        $query = $this->db->get();

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

    function approval_status($data, $sw_number) {
        $this->db->where('LFNumber', $sw_number);
        $this->db->update($this->table, $data);
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        } else {
            return 1;
        }
    }

    function cek_app($piutang_no) {
        $this->db->select('AppLevel1Status, AppLevel2Status');
        $this->db->from('at_loanfunding');
        $this->db->where('LFNumber', $piutang_no);
        $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($piutang_no) {
        $this->db->select('AppLevel1Status');
        $this->db->from('at_loanfunding');
        $this->db->where('LFNumber', $piutang_no);
        $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($piutang_no) {
        $this->db->select('AppLevel2Status');
        $this->db->from('at_loanfunding');
        $this->db->where('LFNumber', $piutang_no);
        $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 status_finished($app_finished, $piutang_no) {
        $this->db->where('LFNumber', $piutang_no);
        $this->db->update('at_loanfunding', $app_finished);
        if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            return 0;
        } else {
            return 1;
        }
    }

    function cek_trans_status($id) {
        $this->db->select('TransStatus');
        $this->db->from('at_loanfunding');
        $this->db->where('LFNumber', $id);
        $query = $this->db->get();

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

    function pembatalan_transaksi($id, $data) {
        $this->db->where('LFNumber', $id);
        return $this->db->update('at_loanfunding', $data);
        /* if ($this->db->trans_status() === FALSE) {
          $this->db->trans_rollback();
          return 0;
          }
          else {
          return 1;
          } */
    }

    function m_delete_journal($LFNumber) {
        $this->db->where('LFNumber', $LFNumber);
        $this->db->delete($this->journal);
    }

    function update_approved_data($data, $LFNumber) {
        $this->db->update($this->table, $data, array('LFNumber' => $LFNumber));
    }

    function update_to_reverse_piutang($LFNumber, $data_loan) {
        $this->db->update($this->table, $data_loan, array("LFNumber" => $LFNumber));
    }

    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_detail_item_print($piutang_no, $BranchCode, $operasi) {
        $this->db->select('gr.*,la.AccountName as LoanPayment,ra.AccountName as Receipt_Account,branch.BranchName,branch.BranchAddress,am_prepared.EmployeeName as nm_prepared,am_dir.EmployeeName as nm_direksi');
        $this->db->from($this->table . ' as gr');
        $this->db->join('at_compaccountgl as ac', 'ac.AccountNumber=gr.LFAccount', 'inner');
        $this->db->join('am_accountgl as la', 'la.AccountNumber = ac.AccountNumber', 'INNER');
        $this->db->join('at_compaccountgl as acc', 'acc.AccountNumber=gr.ReceiptAccount', 'inner');
        $this->db->join('am_accountgl as ra', 'ra.AccountNumber = acc.AccountNumber', 'INNER');
        $this->db->join('am_branch as branch', 'branch.BranchCode = gr.BranchCode', 'INNER');
        $this->db->join('am_employee as am_prepared', 'gr.CreatedBy=am_prepared.EmployeeCode', 'LEFT');
        $this->db->join('am_employee as am_dir', 'gr.AppLevel2Code=am_dir.EmployeeCode', 'LEFT');
        $this->db->where('LFNumber', $piutang_no);
        $this->db->where('gr.BranchCode ' . $operasi, $BranchCode);
        $query = $this->db->get();
        return $query->row();
    }

    function m_cek_loan_payment($LFNumber) {
        $this->db->where('LFNumber', $LFNumber);
        $this->db->where_in('TransStatus', array(1, 2, 5));
        $query = $this->db->get($this->payment)->result_array();
        if (count($query) > 0) {
            return true;
        } else {
            return false;
        }
    }

    function m_cek_loan_payment_journal($LFNumber) {
        $this->db->where('LFNumber', $LFNumber);
        $this->db->where_in('TransStatus', array(1, 2, 5));
        $query = $this->db->get($this->payment_journal)->result_array();
        if (count($query) > 0) {
            return true;
        } else {
            return false;
        }
    }

    function get_no_loan_payment($LFNumber) {
        $this->db->select('LPNumber');
        $this->db->where('LFNumber', $LFNumber);
        $query = $this->db->get($this->payment)->result_array();
        return $query[0]['LPNumber'];
    }

    function get_no_loan_payment_journal($LFNumber) {
        $this->db->select('LJNumber');
        $this->db->where('LFNumber', $LFNumber);
        $query = $this->db->get($this->payment_journal)->result_array();
        return $query[0]['LJNumber'];
    }

    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_all_to_print($data_filter) {
        /* =================================post dari find di kiri==================================================== */
        $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('lf.LFNumber, lf.LFDate, lf.LFAmount, 
			ag.AccountName as Loan_Account, gl.AccountName as Receipt_Account, lf.LFDesc, lf.TransStatus, 
			created.EmployeeName as created_name');
        $this->db->from($this->table . ' as lf');
        $this->db->join($this->branch . ' as br', 'br.BranchCode=lf.BranchCode', 'INNER');
        $this->db->join($this->acc_gl . ' as ag', 'ag.AccountNumber=lf.LFAccount', 'inner');
        $this->db->join($this->acc_gl . ' as gl', 'gl.AccountNumber=lf.ReceiptAccount', 'inner');
        $this->db->join('am_employee as created', 'created.EmployeeCode=lf.CreatedBy', 'LEFT');

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

        /* ===================================find by branch code========================================================= */
        /*
          get data dari  combobox find di view
         */
        if ($BranchCode != "") {
            $this->db->where('lf.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('lf.BranchCode', $sess_branch);
            //-----------------------end-------------------------------------------------//
        }
        /* =======================================================end======================================================== */

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

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

        /* ==============================================sort tabel asc========================================================== */
        $this->db->order_by('lf.TransStatus', 'ASC');
        $this->db->order_by('lf.LFNumber', 'ASC');
        /* ===============================end================================================================================= */
        $query = $this->db->get();
        return $query->result();
    }
    function get_all_to_print_LAMA($sess_branch) {
        $this->db->select('lf.LFDate,lf.LFNumber,
			lf.LFDesc,lf.LFAmount,gl.AccountName as Loan_Account,al.AccountName as Receipt_Account');
        $this->db->from($this->table . ' as lf');
        $this->db->join($this->acc_gl . ' as gl', 'gl.AccountNumber=lf.LFAccount', 'inner');
        $this->db->join($this->acc_gl . ' as al', 'al.AccountNumber=lf.ReceiptAccount', 'inner');
        $this->db->where('lf.BranchCode', $sess_branch);

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

    function insert_transsaction($trans_data) {

        $this->db->trans_start();

        /* ======================= insert group transaction ============================ */
        $this->insert_group($trans_data['insert_group']['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_group($trans_data['update_group']['data'], $trans_data['update_group']['TransNumber']);

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

        /* ===================== 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']);

        $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_piutang($trans_data['reverse_data']['TransNumber'], $trans_data['reverse_data']['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->suspend_data($trans_data['suspend_data']['TransNumber'], $trans_data['suspend_data']['data']);

        $this->db->trans_complete();

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

}
