<?php

class M_payment_karyawan extends CI_Model {

    public $table = "at_employeeinstallment";
    public $detail = "at_einsdetail";
    public $journal = "at_einsjournal";
    public $table_piutang = "at_employeedebt";
    public $table_journal = "at_employeeinstallment_journal";
    public $company = "am_company";
    public $branch = "am_branch";
    public $account = "at_compaccountgl";
    public $am_account = "am_accountgl";
    public $employee = "am_employee";
    public $reconcile = "at_bankreconcille";
    public $acc_type = 'am_accounttype';
    public $sub_type = "am_accountsubtype";
    public $period = "at_periodend";
    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('at_employeeinstallment.EInsDate, at_employeeinstallment.EInsNumber, am_employee.EmployeeName, am_employee.EmployeeCode, am_company.CompanyName, at_employeeinstallment.EInsAmount,
		                   at_employeeinstallment.EInsDesc, at_employeeinstallment.TransStatus, at_einsdetail.PaymentPeriod, created_by.EmployeeName as Created');
        $this->db->from($this->table);
        $this->db->join('at_einsdetail', 'at_einsdetail.EInsNumber=at_employeeinstallment.EInsNumber', 'INNER');
        $this->db->join('am_employee', 'at_employeeinstallment.EmplTransCode=am_employee.EmployeeCode', 'INNER');
        $this->db->join('am_company', 'at_employeeinstallment.CompRecpCode=am_company.CompanyCode', 'INNER');
        $this->db->join('am_branch', 'at_employeeinstallment.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_employee as created_by', 'at_employeeinstallment.CreatedBy=created_by.EmployeeCode', 'LEFT');

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

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

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

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

        $this->db->where('Payment_Type', 0);
        /* =======================================================end======================================================== */

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

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

    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 get_last_payment_number($BranchCode) {
        $qty = "SELECT MAX(EInsNumber) as EInsNumber FROM at_employeeinstallment WHERE BranchCode='$BranchCode'";
        return $this->db->query($qty)->result();
    }

    // function create_payment_karyawan_number($date){        
    //        $branch_code    = $this->session->userdata('branch');
    //        $month          = date('m', strtotime($date));
    //        $year           = date('y', strtotime($date));
    //        $payment_karyawan_number    = $branch_code."/".$month.$year."EI/";
    //        $this->db->like('EInsNumber',$payment_karyawan_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  $payment_karyawan_number.$number;
    //    }

    /* ======================penomeran transaksi new======================================================= */
    function check_code_exist_1($AInsNumber) {
        $query = $this->db->get_where($this->table, array('EInsNumber' => $AInsNumber), 1, 0);

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

    function create_payment_karyawan_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 . "EI/";

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

    //custom data
    function get_company($CompanyCode) {
        $this->db->select('*');
        $this->db->from($this->company);
        //$this->db->where('CompanyCode', $CompanyCode);
        $query = $this->db->get();

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

    function get_akun_bank($ComCode) {
        $this->db->select('ac.AccountNumber,ag.AccountName,ag.CurrencyCode');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->am_account . ' as ag', 'ac.AccountNumber=ag.AccountNumber', 'inner');
        $this->db->join($this->branch . ' as br', 'ac.BranchCode=br.BranchCode', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'ag.AccountSubTypeID=sub.AccountSubTypeID', 'inner');
        $this->db->where_in('sub.AccountTypeName', array('Bank'));
        $this->db->where('ac.BranchCode', $ComCode);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('ag.CurrencyCode', 'IDR');
        $this->db->where('ac.TransStatus', 2);
        $this->db->where('ac.GLSaldoYears', date('Y'));
        $this->db->where('br.BranchStatus', 0);
        $query = $this->db->get();

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

    function update_is_lock($data_islock_status, $EDebtNumber) {
        $this->db->update('at_employeedebt', $data_islock_status, array('EDebtNumber' => $EDebtNumber));
    }

    function get_piutang($BranchCode) {
        $CompanyCode = $this->session->userdata('company');
        $BranchStatus = $this->session->userdata('branch_status');

        $this->db->select('at_employeedebt.CompTransCode, am_branch.BranchName, at_employeedebt.BranchCode, am_accountgl.AccountName, at_employeedebt.EDebtNumber, am_employee.EmployeeName, at_employeedebt.EmplRecpCode,at_employeedebt.EDebtDesc,at_employeedebt.EDebtAmount, at_employeedebt.EdebtIsLock,at_employeedebt.JVoucherBalance');
        $this->db->from($this->table_piutang);
        $this->db->join('am_employee', 'at_employeedebt.EmplRecpCode=am_employee.EmployeeCode');
        $this->db->join('am_accountgl', 'at_employeedebt.BankTransfer=am_accountgl.AccountNumber');
        $this->db->join('am_branch', 'am_branch.BranchCode=at_employeedebt.BranchCode');
        //$this->db->join('at_employeeinstallment','at_employeeinstallment.EDebtNumber = at_employeedebt.EDebtNumber');
        $this->db->where_in('at_employeedebt.TransStatus', array(1));
        //if($this->session->userdata("jobtitle") != "DIR"){
        //if($BranchStatus == 1){
        $this->db->where('at_employeedebt.BranchCode', $BranchCode);
        //}

        $this->db->where('at_employeedebt.CompTransCode', $CompanyCode);
        //}
        $query = $this->db->get();

        return $query->result();
    }

    function get_akun_bank_edit($ComCode) {
        // $jobtitle = $this->session->userdata("jobtitle");
        // $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', $ComCode);			
        // $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        // $this->db->where('am_accountgl.CurrencyCode', 'IDR');
        // $this->db->where('am_accountgl.AccountTypeName', 'Bank');
        // $this->db->where('am_accountgl.AccountChild', 1);
        // $this->db->where('am_accountgl.TransStatus', 2);
        // $query = $this->db->get();
        // $result=$query->result();
        // return json_encode($result);

        $this->db->select('ac.AccountNumber,ag.AccountName,ag.CurrencyCode');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->am_account . ' as ag', 'ac.AccountNumber=ag.AccountNumber', 'inner');
        $this->db->join($this->branch . ' as br', 'ac.BranchCode=br.BranchCode', 'INNER');
        $this->db->join($this->sub_type . ' as sub', 'ag.AccountSubTypeID=sub.AccountSubTypeID', 'inner');
        $this->db->where_in('sub.AccountTypeName', array('Bank'));
        $this->db->where('ac.BranchCode', $ComCode);
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('ag.CurrencyCode', 'IDR');
        $this->db->where('ac.TransStatus', 2);
        $this->db->where('ac.GLSaldoYears', date('Y'));
        // $this->db->where('br.BranchStatus', 0);
        $query = $this->db->get();

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

    function get_sisa_piutang($EDebtNumber) {
        $this->db->select('SUM(EInsAmount) as total_payment');
        $this->db->from('at_employeeinstallment');
        $this->db->where('at_employeeinstallment.EDebtNumber', $EDebtNumber);
        $this->db->where('at_employeeinstallment.TransStatus !=', 3);
        $query = $this->db->get();
        return $query->row();
    }

    function get_payment_periode($id, $uri7) {
        $this->db->select("*,
							(case when at_employeeinstallment.TransStatus = 4 then 'Not Yet Paid'
                            when at_employeeinstallment.TransStatus = 2 then 'Paid'
                            end) as Status
							");
        $this->db->from($this->detail);
        $this->db->join('at_employeeinstallment', 'at_employeeinstallment.EInsNumber=at_einsdetail.EInsNumber');
        $this->db->where('at_einsdetail.EDebtNumber', $id);
        $this->db->where('at_einsdetail.PaymentPeriod <=', $uri7);
        $this->db->where('at_employeeinstallment.TransStatus !=', 3);
        $query = $this->db->get();
        $result = $query->result();
        return json_encode($result);
    }

    function get_payment_periode_cancel($payment_before) {
        $this->db->select("*,
							(case when at_employeeinstallment.TransStatus = 4 then 'Not Yet Paid'
                            when at_employeeinstallment.TransStatus = 2 then 'Paid'
                            end) as Status
							");
        $this->db->from($this->detail);
        $this->db->join('at_employeeinstallment', 'at_employeeinstallment.EInsNumber=at_einsdetail.EInsNumber');
        $this->db->where('at_einsdetail.EInsNumber', $payment_before);
        $this->db->where('at_employeeinstallment.TransStatus', 3);
        $query = $this->db->get();
        $result = $query->result();
        return json_encode($result);
    }

    function get_no_payment_before($piutang_no) {
        $this->db->select('EInsNumber');
        $this->db->from($this->table);
        $this->db->where('EDebtNumber', $piutang_no);
        $query = $this->db->get();

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

            return $EInsNumber;
        } else {
            return FALSE;
        }
    }

    // end custom

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

        $this->db->where('at_compaccountgl.CompanyCode', $company);
        $this->db->where('at_compaccountgl.BranchCode', $branch_code);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('am_accountgl.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 cek_payment_data($EDebtNumber) {
        $this->db->select('EDebtNumber');
        $this->db->from('at_employeeinstallment');
        $this->db->where('EDebtNumber', $EDebtNumber);
        $this->db->where('TransStatus !=', 3);
        $query = $this->db->get();

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

            return $EDebtNumber;
        } else {
            return FALSE;
        }
    }

    function get_max_period($EDebtNumber) {
        $this->db->select_max('PaymentPeriod');
        $this->db->from('at_einsdetail');
        $this->db->join('at_employeeinstallment', 'at_employeeinstallment.EInsNumber = at_einsdetail.EInsNumber');
        $this->db->where('at_employeeinstallment.EDebtNumber', $EDebtNumber);
        $this->db->where('at_employeeinstallment.TransStatus !=', 3);
        $query = $this->db->get();

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

            return $PaymentPeriod;
        } else {
            return 0;
        }
    }

    function get_payment_balance($EDebtNumber) {
        $this->db->select_min('PaymentBalance');
        $this->db->from('at_einsdetail');
        $this->db->join('at_employeeinstallment', 'at_employeeinstallment.EInsNumber = at_einsdetail.EInsNumber');
        $this->db->where('at_employeeinstallment.EDebtNumber', $EDebtNumber);
        $this->db->where('at_employeeinstallment.TransStatus !=', 3);
        $query = $this->db->get();

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

            return $PaymentBalance;
        } else {
            return FALSE;
        }
    }

    function get_history_payment($piutang_num) {
        $this->db->select('at_einsdetail.LoanPayment, at_einsdetail.PaymentPeriod, at_einsdetail.PaymentPerPeriod, at_einsdetail.PaymentBalance, at_einsdetail.EInsNumber');
        $this->db->from('at_einsdetail');
        $this->db->join('at_employeeinstallment', 'at_employeeinstallment.EInsNumber = at_einsdetail.EInsNumber');
        $this->db->where('at_employeeinstallment.EDebtNumber', $piutang_num);
        $this->db->where('at_employeeinstallment.TransStatus !=', 3);
        return $this->db->get()->result();
    }

    function get_total_payment_period($EDebtNumber) {
        $this->db->select('sum(PaymentPerPeriod) as total_payment_period');
        $this->db->from('at_einsdetail');
        $this->db->join('at_employeeinstallment', 'at_employeeinstallment.EInsNumber = at_einsdetail.EInsNumber');
        $this->db->where('at_employeeinstallment.EDebtNumber', $EDebtNumber);
        $this->db->where('at_employeeinstallment.TransStatus !=', 3);
        $query = $this->db->get();

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

            return $total_payment_period;
        } else {
            return FALSE;
        }
    }

    function get_piutang_no_detail($id) {
        $this->db->select('EDebtNumber');
        $this->db->from($this->table);
        $this->db->where('EInsNumber', $id);
        $query = $this->db->get();

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

            return $EDebtNumber;
        } else {
            return FALSE;
        }
    }

    function get_total_piutang_terakhir($id) {
        $this->db->select('LoanPayment');
        $this->db->from('at_einsdetail');
        $this->db->where('EInsNumber', $id);
        $query = $this->db->get();

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

            return $LoanPayment;
        } else {
            return FALSE;
        }
    }

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

    function update_payment_karyawan($data_payment, $EInsNumber) {
        $this->db->where('EInsNumber', $EInsNumber);
        return $this->db->update($this->table, $data_payment);
    }

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

    function insert_journal($journal_debet) {
        $this->db->insert('at_einsjournal', $journal_debet);
    }

    function get_expt_payment() {
        $this->db->select('ExpCategoryID');
        $this->db->from('sys_expensedetail');
        $this->db->where('ExpDetailName', 'Pembayaran Piutang');
        $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_payment($ExpCategoryID) {
        $this->db->select('AccountNumber');
        $this->db->from('sys_expenseaccount');
        $this->db->join('sys_expensedetail', 'sys_expenseaccount.ExpCategoryID=sys_expensedetail.ExpCategoryID', 'INNER');
        $this->db->where('sys_expenseaccount.ExpCategoryID', $ExpCategoryID);
        $this->db->where('sys_expenseaccount.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 insert_journal_credit_payment($journal_credit) {
        $this->db->insert('at_einsjournal', $journal_credit);
    }

    function get_detail($id) {
        /* $query = $this->db->get_where($this->table, array('EInsNumber' => $id));
          $query = $this->db->join('am_employee','at_employeeinstallment.EmplTransCode=am_employee.EmployeeCode');
          $query = $this->db->join('am_company','at_employeeinstallment.CompRecpCode=am_company.CompanyCode'); */

        $this->db->select($this->table . '.*, at_employeeinstallment.BankReceipt,at_einsdetail.PaymentPerPeriod,at_einsdetail.PaymentBalance,at_einsdetail.PaymentPeriod,' . $this->table . '.TransStatus, at_employeedebt.TransStatus as transstatus_debt,em.DepartmentCode,am_employee.EmployeeName');
        $this->db->from($this->table);
        $this->db->join('am_employee', 'at_employeeinstallment.EmplTransCode=am_employee.EmployeeCode');
        $this->db->join('at_einsdetail', 'at_employeeinstallment.EInsNumber=at_einsdetail.EInsNumber');
        $this->db->join('am_company', 'at_employeeinstallment.CompRecpCode=am_company.CompanyCode');
        $this->db->join('at_employeedebt', 'at_employeedebt.EDebtNumber=at_employeeinstallment.EDebtNumber');
        $this->db->join($this->employee . ' as em', 'em.EmployeeCode=at_employeeinstallment.CreatedBy', 'inner');
        $this->db->where('at_employeeinstallment.EInsNumber', $id);
        $query = $this->db->get();

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

    function get_date_journal_payment($payment_number) {
        $this->db->select('*, am_branch.BranchName, am_company.CompanyName');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode=at_employeeinstallment.BranchCode');
        $this->db->join('am_company', 'am_company.CompanyCode=am_branch.CompanyCode');
        $this->db->where('EInsNumber', $payment_number);
        $query = $this->db->get();

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

    function get_all_journal_payment($payment_number, $branch_explode) {
        $this->db->select('am_accountgl.AccountName, am_accountgl.AccountNumber, at_einsjournal.EInsSaldoDebet, 
						   at_einsjournal.EInsSaldoCredit, at_einsjournal.EInsDesc, at_einsjournal.EInsNumber, at_einsjournal.EInsAccountNumber');
        $this->db->from('at_compaccountgl');
        $this->db->join('at_einsjournal', 'at_compaccountgl.AccountNumber=at_einsjournal.EInsAccountNumber');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        $this->db->where('at_compaccountgl.BranchCode', $branch_explode);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('at_einsjournal.EInsNumber', $payment_number);
        $query = $this->db->get();

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

    function cek_approval_payment($payment_no) {
        $this->db->select('AppLevel1Status');
        $this->db->from($this->table);
        $this->db->where('EInsNumber', $payment_no);
        $query = $this->db->get();

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

            return $AppLevel1Status;
        } else {
            return FALSE;
        }
    }

    function suspend_data($payment_no, $data) {
        $this->db->where('EInsNumber', $payment_no);
        $this->db->update($this->table, $data);
    }

    function approval_status($data, $pay_number) {
        $this->db->where('EInsNumber', $pay_number);
        $this->db->update($this->table, $data);

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

    function update_status_piutang($EDebtNumber, $status_piutang) {
        $this->db->where('EDebtNumber', $EDebtNumber);
        $this->db->update($this->table_piutang, $status_piutang);
    }

    function update_group($data_group, $txt_payment_no) {
        $this->db->where('EInsNumber', $txt_payment_no);
        $this->db->update($this->table, $data_group);
    }

    function update_detail($detail_data, $txt_payment_no) {
        $this->db->where('EInsNumber', $txt_payment_no);
        $this->db->update($this->detail, $detail_data);
    }

    function update_payment_jurnal_debet($journal_debet, $txt_payment_no) {
        $this->db->where('EInsNumber', $txt_payment_no);
        $this->db->where('EInsSignStatus', 0);
        $this->db->update('at_einsjournal', $journal_debet);

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

    function update_payment_jurnal_credit($journal_credit, $txt_payment_no) {
        $this->db->where('EInsNumber', $txt_payment_no);
        $this->db->where('EInsSignStatus', 1);
        $this->db->update('at_einsjournal', $journal_credit);

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

    function get_adebt_number($id) {
        $this->db->select('EDebtNumber');
        $this->db->from('at_employeeinstallment');
        $this->db->where('EInsNumber', $id);
        $query = $this->db->get();

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

            return $EDebtNumber;
        } else {
            return FALSE;
        }
    }

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

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

    function get_piutang_number($txt_payment_no) {
        $this->db->select('dt.EDebtNumber,dt.PaymentPeriod');
        $this->db->from($this->detail . ' as dt');
        $this->db->where('dt.EInsNumber', $txt_payment_no);

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

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

    function m_cek_status_piutang($piutang_no) {
        $this->db->select('TransStatus');
        $this->db->from('at_employeedebt');
        $this->db->where('EDebtNumber', $piutang_no);

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

        if ($query[0]['TransStatus'] == 1) {
            return true;
        } else {
            return false;
        }
    }

    function m_cek_status_period_piutang($piutang_no, $periode) {
        $this->db->select('gr.EdebtIsLock');
        $this->db->from($this->table_piutang . ' as gr');
        $this->db->join($this->detail . ' as dt', 'dt.EDebtNumber=gr.EDebtNumber', 'inner');
        $this->db->where('gr.EDebtNumber', $piutang_no);
        $this->db->where('dt.PaymentPeriod', $periode);

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

        if ($query[0]['EdebtIsLock'] == 0) {
            return true;
        } else {
            return false;
        }
    }

    function update_to_reverse_payment($number_payment, $data_reversed_payment) {
        $this->db->where('EInsNumber', $number_payment);
        $this->db->update($this->table, $data_reversed_payment);
    }

    function update_to_reverse_piutang($number_piutang, $data_reversed_piutang) {
        $this->db->where('EDebtNumber', $number_piutang);
        $this->db->update('at_employeedebt', $data_reversed_piutang);
    }

    function update_piutang_to_finished($data_piutang_finished, $no_piutang_karyawan) {
        $this->db->update('at_employeedebt', $data_piutang_finished, array('EDebtNumber' => $no_piutang_karyawan));
    }

    function get_detail_item_print($payment_no) {
        $this->db->select($this->table . '.*,b_receipt.AccountName as Bank_receipt,branch.BranchName,branch.BranchAddress,am_prepared.EmployeeName as nm_prepared,am_mgr.EmployeeName as nm_manager, em_transfer.EmployeeName as nm_employee');
        $this->db->from($this->table);
        $this->db->join('am_accountgl as b_receipt', 'b_receipt.AccountNumber = ' . $this->table . '.BankReceipt', 'INNER');
        $this->db->join('am_branch as branch', 'branch.BranchCode = ' . $this->table . '.BranchCode', 'INNER');
        $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->join('am_employee as em_transfer', $this->table . '.EmplTransCode=em_transfer.EmployeeCode', 'LEFT');
        $this->db->where('EInsNumber', $payment_no);
        $query = $this->db->get();
        return $query->row();
    }

    function m_get_total_piutang($EDebtNumber) {
        $this->db->select('EDebtAmount,JVoucherBalance');
        $this->db->from('at_employeedebt');
        $this->db->where('EDebtNumber', $EDebtNumber);

        $query = $this->db->get()->result_array();
        if (count($query) > 0) {
            return $query[0];
        } else {
            return false;
        }
    }

    function m_get_total_payment($EDebtNumber) {

        $TS = array(2, 4);

        $this->db->select('sum(EInsAmount) as total_payment');
        $this->db->from($this->table);
        $this->db->where('EDebtNumber', $EDebtNumber);
        $this->db->where_in('TransStatus', $TS);

        $query = $this->db->get()->result_array();
        if (count($query) > 0) {
            return $query[0]['total_payment'];
        } 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_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('at_employeeinstallment.EInsDate, at_employeeinstallment.EInsNumber, am_employee.EmployeeName, am_employee.EmployeeCode, am_company.CompanyName, at_employeeinstallment.EInsAmount,
		                   at_employeeinstallment.EInsDesc, at_employeeinstallment.TransStatus, at_einsdetail.PaymentPeriod, created_by.EmployeeName as Created');
        $this->db->from($this->table);
        $this->db->join('at_einsdetail', 'at_einsdetail.EInsNumber=at_employeeinstallment.EInsNumber', 'INNER');
        $this->db->join('am_employee', 'at_employeeinstallment.EmplTransCode=am_employee.EmployeeCode', 'INNER');
        $this->db->join('am_company', 'at_employeeinstallment.CompRecpCode=am_company.CompanyCode', 'INNER');
        $this->db->join('am_branch', 'at_employeeinstallment.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_employee as created_by', 'at_employeeinstallment.CreatedBy=created_by.EmployeeCode', 'LEFT');

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

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

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

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

        $this->db->where('Payment_Type', 0);
        /* =======================================================end======================================================== */

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

    function get_all_to_print_LAMA($sess_branch) {
        $this->db->select('at_employeeinstallment.EInsDate,at_employeeinstallment.EInsNumber,
			at_employeeinstallment.EInsAmount,at_employeeinstallment.EInsDesc,am_employee.EmployeeName,am_company.CompanyName');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_employeeinstallment.BranchCode');
        $this->db->join('am_employee', 'am_employee.EmployeeCode = ' . $this->table . '.EmplTransCode');
        $this->db->join('am_company', 'am_company.CompanyCode=at_employeeinstallment.CompRecpCode');
        $this->db->where($this->table . '.BranchCode', $sess_branch);
        $this->db->where('at_employeeinstallment.Payment_Type', 0);

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

    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_max_period_finished($no_piutang) {
        $this->db->select('max(PaymentPeriod) as period');
        $this->db->from($this->detail . ' as dt');
        $this->db->join($this->table . ' as gr', 'gr.EInsNumber=dt.EInsNumber', 'inner');
        $this->db->where('dt.EDebtNumber', $no_piutang);
        $this->db->where_not_in('gr.TransStatus', array(3));

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

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

    function check_payment_journal($no_piutang) {
        $this->db->select('EInjNumber');
        $this->db->from($this->table_journal);
        $this->db->where('EDebtNumber', $no_piutang);

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

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

    function get_no_payment_journal($no_piutang) {
        $this->db->select('EInjNumber');
        $this->db->from($this->table_journal);
        $this->db->where('EDebtNumber', $no_piutang);

        $query = $this->db->get()->result_array();
        return $query[0]['EInjNumber'];
    }

    function get_max_no_payment($no_piutang) {
        $this->db->select('EInsNumber');
        $this->db->from($this->table);
        $this->db->where('EDebtNumber', $no_piutang);
        $this->db->limit(1);
        $this->db->order_by('CreatedAt', 'DESC');

        $query = $this->db->get()->result_array();
        return $query[0]['EInsNumber'];
    }

    function insert_transsaction($trans_data) {

        $this->db->trans_start();

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

        /* =========================== update is lock piutang ========================= */
        $this->update_is_lock($trans_data['islock_status']['data'], $trans_data['islock_status']['TransNumber']);
        /* =========================== end update is lock piutang ===================== */

        /* =========================== insert detail ============================ */
        $this->insert_detail($trans_data['insert_detail']['data']);
        /* =========================== end insert detail ========================== */

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

        /* ======================= update detail ============================== */
        for ($x = 0; $x < count($trans_data['update_detail']['data']); $x++) {
            $this->update_detail($trans_data['update_detail']['data'][$x], $trans_data['update_detail']['TransNumber'][$x]);
        }

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

        /* =========================================== unlock piutang afiliasi ==================================================== */
        $this->update_is_lock($trans_data['islock_status']['data'], $trans_data['islock_status']['TransNumber']);

        if ($trans_data['update_piutang']['total'] > 0) {
            $this->update_status_piutang($trans_data['update_piutang']['TransNumber'], $trans_data['update_piutang']['data']);
        }

        $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_payment']['TransNumber'], $trans_data['reverse_payment']['data']);

        /* ====================================================== reverse piutang ==================================================== */
        $this->update_to_reverse_piutang($trans_data['reverse_piutang']['TransNumber'], $trans_data['reverse_piutang']['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']);

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

        $this->db->trans_complete();

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

}
