<?php

class M_expense_cashbon extends CI_Model {

    public $table = "at_expensecashbon";
    public $detail = "at_ebondetail";
    public $journal = "at_ebonjournal";
    public $department = "am_department";
    public $employee = "am_employee";
    public $account = "at_compaccountgl";
    public $reconcile = "at_bankreconcille";
    public $acc_gl = "am_accountgl";
    public $sub_type = "am_accountsubtype";
    public $branch = "am_branch";
    public $period = "at_periodend";

    function get_all_data() {
        $param = $this->input->post('param');

        $CompanyCode = $this->session->userdata('company');
        $BranchCode = $this->session->userdata('branch');
        $BranchStatus = $this->session->userdata('branch_status');
        $JobTitleCode = $this->session->userdata("jobtitle");

        $EBonNumber = isset($param['txt_IDNumber']) ? $param['txt_IDNumber'] : '';
        $EBonMemo = isset($param['txt_Description']) ? $param['txt_Description'] : '';
        $EBonDateFrom = isset($param['txt_DateFrom']) ? $param['txt_DateFrom'] : '';
        $EBonDateTo = isset($param['txt_DateTo']) ? $param['txt_DateTo'] : '';
        $TransStatus = isset($param['chk_TransStatus']) ? $param['chk_TransStatus'] : '';

        $this->db->select('(case 
				when at_expensecashbon.TransStatus = 1 then "On Progress" 
				when at_expensecashbon.TransStatus = 2 then "Finished" 
				when at_expensecashbon.TransStatus = 3 then "Suspended" 
				when at_expensecashbon.TransStatus = 4 then "Need App. Mgr." 
				when at_expensecashbon.TransStatus = 5 then "Need App. Dir." 
				when at_expensecashbon.TransStatus = 6 then "Need App. Dir. & Mgr." 
				else "Unknown" end) as Status,
			am_branch.BranchName, am_department.DepartmentName, am_employee.EmployeeName, at_expensecashbon.EBonNumber,
			at_expensecashbon.EBonDate, at_expensecashbon.EBonNumber, at_ebondetail.EBonAmount, at_expensecashbon.EBonDesc');
        $this->db->from('at_expensecashbon');
        $this->db->join('at_ebondetail', 'at_expensecashbon.EBonNumber=at_ebondetail.EBonNumber', 'INNER');
        $this->db->join('am_branch', 'at_expensecashbon.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_department', 'at_expensecashbon.DepartmentCode=am_department.DepartmentCode', 'INNER');
        $this->db->join('am_employee', 'at_expensecashbon.EmployeeCode=am_employee.EmployeeCode', 'INNER');

        if ($BranchStatus == 1) {
            $this->db->where('at_expensecashbon.BranchCode', $BranchCode);
            $this->db->where('am_branch.CompanyCode', $CompanyCode);
        } else {
            $this->db->where('am_branch.CompanyCode', $CompanyCode);
        }

        if ($EBonNumber != '') {
            $this->db->like('at_expensecashbon.EBonNumber', $EBonNumber);
        }

        if ($EBonMemo != '') {
            $this->db->like('at_ebondetail.EBonMemo', $EBonMemo);
        }

        if ($EBonDateFrom != '' && $EBonDateTo != '' && $EBonDateFrom < $EBonDateTo) {
            $this->db->where('at_expensecashbon.EBonDate >=', date("Y-m-d", strtotime($EBonDateFrom)));
            $this->db->where('at_expensecashbon.EBonDate <=', date("Y-m-d", strtotime($EBonDateTo)));
        }

        if ($TransStatus != '') {
            $this->db->where_in('at_expensecashbon.TransStatus', $TransStatus);
        }

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

        return $query->result();
    }

    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_expensecashbon.EBonNumber, at_expensecashbon.EBonDesc,
						   at_expensecashbon.EBonDate, at_expensecashbon.EBonNumber, at_ebondetail.EBonAmount, at_ebondetail.EBonMemo, created.EmployeeName as nm_createdby,
						   at_expensecashbon.TransStatus');
        $this->db->from('at_expensecashbon');
        $this->db->join('at_ebondetail', 'at_expensecashbon.EBonNumber=at_ebondetail.EBonNumber', 'INNER');
        $this->db->join('am_branch', 'at_expensecashbon.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_department', 'at_expensecashbon.DepartmentCode=am_department.DepartmentCode', 'INNER');
        $this->db->join('am_employee', 'at_expensecashbon.EmployeeCode=am_employee.EmployeeCode', 'INNER');
        $this->db->join('am_employee as created', 'at_expensecashbon.CreatedBy=created.EmployeeCode', 'INNER');

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

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

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

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

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

    function get_account_by_company($company_code) {
        $branch_code = $this->session->userdata('branch');
        $account_no = isset($_POST['AccountNo']) ? mysql_real_escape_string($_POST['AccountNo']) : '';
        $account_name = isset($_POST['AccountName']) ? mysql_real_escape_string($_POST['AccountName']) : '';

        $this->db->select('am_accountgl.AccountNumber,am_accountgl.AccountName');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber = am_accountgl.AccountNumber');
        $this->db->join('am_accountsubtype', 'am_accountsubtype.AccountSubTypeID = am_accountgl.AccountSubTypeID');

        $this->db->where('am_accountgl.TransStatus', 2);
        $this->db->where('am_accountgl.AccountChild !=', 0);
        $this->db->where('at_compaccountgl.CompanyCode', $company_code);
        $this->db->where('at_compaccountgl.BranchCode', $branch_code);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where_in('am_accountsubtype.AccountSubTypeID', array(69, 70, 71, 76));

        if ($account_no != '') {
            $this->db->like('am_accountgl.AccountNumber', $account_no);
        }

        if ($account_name != '') {
            $this->db->like('am_accountgl.AccountName', $account_name);
        }

        $this->db->group_by('am_accountgl.AccountName');

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

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

    function get_department($CompanyCode) {
        $this->db->select('*');
        $this->db->from($this->department);
        $this->db->where('CompanyCode', $CompanyCode);
        $query = $this->db->get();

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

    function get_department_edit($replace_code, $var_Company_code) {
        $this->db->select('*');
        $this->db->from($this->department);
        $this->db->where('CompanyCode', $replace_code);
        $this->db->where('DepartmentCode', $var_Company_code);
        $query = $this->db->get();

        $result = $query->result();
        return json_encode($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_employee_edit($depCode, $branch_replace) {
        $branch = $this->session->userdata('branch');
        $this->db->select('*');
        $this->db->from($this->employee);
        $this->db->where('DepartmentCode ', $depCode);
        $this->db->where('BranchCode ', $branch_replace);
        $this->db->where('ResignDate ', NULL);
        $this->db->order_by('EmployeeCode', 'ASC');
        $query = $this->db->get();

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

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

        $this->db->select('ag.AccountNumber,ag.AccountName,ag.CurrencyCode');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->acc_gl . ' 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('ac.CompanyCode', $CompanyCode);
        $this->db->where('ac.BranchCode', $BranchCode);
        $bank_n_kas = array('Petty Cash', 'Bank');
        $this->db->where_in('sub.AccountTypeName', $bank_n_kas);
        $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 gat_akun_cashbon($CompanyCode){
      $this->db->select('*');
      $this->db->from('am_accountgl');
      $this->db->where('AccountNumber', '1-3-12-000-00');
      $query = $this->db->get();

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

    /* Added by Hasan on 2015/03/23 */

    function get_akun_cashbon($company, $AccountNo, $AccountName, $CurrencyCode) {

        $this->db->select('sys_expensecategory.ExpCategoryID,sys_expensecategory.ExpCategoryName');
        $this->db->from('sys_expensecategory');

        if ($CurrencyCode != null) {

            $this->db->join('sys_expenseaccount', 'sys_expenseaccount.ExpCategoryID=sys_expensecategory.ExpCategoryID', 'INNER');
            $this->db->join('am_accountgl', 'am_accountgl.AccountNumber=sys_expenseaccount.AccountNumber', 'INNER');
            $this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber=sys_expenseaccount.AccountNumber', 'INNER');

            $this->db->where('at_compaccountgl.BranchCode', $this->session->userdata('branch'));
            $this->db->where('am_accountgl.CurrencyCode', $CurrencyCode);
            $this->db->where('at_compaccountgl.TransStatus', 2);
            $this->db->where('sys_expenseaccount.AccountCode', 'expense');
            $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        }

        $this->db->where('sys_expensecategory.CompanyCode', $company);

        if ($AccountNo != '') {
            $this->db->like('sys_expensecategory.ExpCategoryID', $AccountNo, 'before');
        }


        if ($AccountName != '') {
            $this->db->like('sys_expensecategory.ExpCategoryName', $AccountName, 'before');
        }

        $this->db->group_by('sys_expensecategory.ExpCategoryID');
        $query = $this->db->get();

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

    /* End of Added */

    function get_currency_code($AccountNumber) {
        $this->db->select('CurrencyRate');
        $this->db->from('am_accountgl');
        $this->db->join('am_currency', 'am_accountgl.CurrencyCode=am_currency.CurrencyCode');
        $this->db->join('at_compaccountgl', 'am_accountgl.AccountNumber=at_compaccountgl.AccountNumber');
        $this->db->where('at_compaccountgl.AccountNumber', $AccountNumber);
        $query = $this->db->get();

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

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

    function cek_app($cashbon_number) {
        $this->db->select('AppLevel1Status, AppLevel2Status');
        $this->db->from('at_expensecashbon');
        $this->db->where('EBonNumber', $cashbon_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($cashbon_number) {
        $this->db->select('AppLevel1Status');
        $this->db->from('at_expensecashbon');
        $this->db->where('EBonNumber', $cashbon_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($cashbon_number) {
        $this->db->select('AppLevel2Status');
        $this->db->from('at_expensecashbon');
        $this->db->where('EBonNumber', $cashbon_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 insert_expense_cashbon($data_cashbon) {
        $this->db->insert($this->table, $data_cashbon);

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

    function insert_detail_cashbon($detail_cashbon) {
        $this->db->insert($this->detail, $detail_cashbon);

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

    function insert_journal_expense_cashbon_debit($data_journal_debit) {
        $this->db->insert($this->journal, $data_journal_debit);

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

    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_sys_expenses_akun($ExpCategoryID) {
        $this->db->select('ExpCategoryID');
        $this->db->from('sys_expensecategory');
        $this->db->where('ExpCategoryID', $ExpCategoryID);
        $this->db->where('CompanyCode', $this->session->userdata('company'));

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

        if ($query->num_rows() > 0) {
            $data = $query->row();
            $this->db->select('AccountNumber');
            $this->db->from('sys_expenseaccount');
            $this->db->where('ExpCategoryID', $data->ExpCategoryID);

            //get AccountNumber
            $query_account = $this->db->get();

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

    function insert_journal_expense_cashbon_credit($data_journal_credit) {
        //$this->db->trans_begin();
        $this->db->insert($this->journal, $data_journal_credit);

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

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

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

    function get_detail_header($id) {
        $query = $this->db->join('am_department', 'at_expensecashbon.DepartmentCode=am_department.DepartmentCode');
        $query = $this->db->join('am_employee', 'at_expensecashbon.EmployeeCode=am_employee.EmployeeCode');
        $query = $this->db->get_where($this->table, array('EBonNumber' => $id));
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function get_detail_cashbon($id) {
        $query = $this->db->join('at_compaccountgl', 'at_ebondetail.EBonAccountNumber=at_compaccountgl.AccountNumber');
        $query = $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        //$query =  $this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        $query = $this->db->get_where($this->detail, array('EBonNumber' => $id));
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }

    function get_amount_cashbon($id) {
        $this->db->select('EBonAmount');
        $this->db->from('at_ebondetail');
        $this->db->where('EBonNumber', $id);
        $query = $this->db->get();

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

            return $EBonAmount;
        } else {
            return FALSE;
        }
    }

    function get_detail_item($replace_no) {
        $BranchCode = $this->session->userdata('branch');
        $company_code = $this->session->userdata('company');

        $this->db->select(" am_accountgl.AccountNumber, am_accountgl.AccountName as EAccountNumber,EBonAmount,EBonMemo, sys_expensecategory.ExpCategoryID, sys_expensecategory.ExpCategoryName");
        //$this->db->select("am_accountgl.AccountNumber, am_accountgl.AccountName as EAccountNumber,EBonAmount,EBonMemo");
        $this->db->from('at_ebondetail');
        $this->db->join('at_compaccountgl', 'at_ebondetail.EBonAccountNumber=at_compaccountgl.AccountNumber', 'INNER');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber', 'INNER');
        $this->db->join('sys_expenseaccount', 'at_ebondetail.EBonAccountNumber=sys_expenseaccount.AccountNumber and at_ebondetail.ExpCategoryID=sys_expenseaccount.ExpCategoryID', 'INNER');
        $this->db->join('sys_expensecategory', 'sys_expenseaccount.ExpCategoryID=sys_expensecategory.ExpCategoryID', 'INNER');
        $this->db->where('at_ebondetail.EBonNumber', $replace_no);
        $this->db->where('sys_expenseaccount.AccountCode', 'expense');
        $this->db->where('sys_expensecategory.CompanyCode', $company_code);
        $this->db->where('at_compaccountgl.BranchCode', $BranchCode);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        //$this->db->group_by('sys_expenseaccount.AccountNumber');
        $query = $this->db->get();
        $result = $query->result();

        return json_encode($result);
    }

    function update_expense_cashbon($data_cashbon, $EBonNumber) {
        $this->db->where('EBonNumber', $EBonNumber);
        $this->db->update($this->table, $data_cashbon);

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

    function update_detail_cashbon($detail_cashbon, $EBonNumber) {
        $this->db->where('EBonNumber', $EBonNumber);
        $this->db->update($this->detail, $detail_cashbon);

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

    function update_journal_expense_cashbon_debit($data_journal_debit, $EBonNumber, $nilai) {
        $this->db->where('EBonNumber', $EBonNumber);
        $this->db->where('EBonSignStatus', $nilai);
        $this->db->update($this->journal, $data_journal_debit);

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

    function update_journal_expense_cashbon_credit($data_journal_credit, $EBonNumber, $nilai) {
        $this->db->where('EBonNumber', $EBonNumber);
        $this->db->where('EBonSignStatus', $nilai);
        $this->db->update($this->journal, $data_journal_credit);
    }

    function update_trans_status($cashbon_number, $data) {
        $this->db->where('EBonNumber', $cashbon_number);
        $this->db->update($this->table, $data);
    }

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

    function status_finished($app_finished, $cashbon_number) {
        $this->db->where('EBonNumber', $cashbon_number);
        $this->db->update($this->table, $app_finished);
    }

    function get_date_journal_cashbon($cashbon_number) {
        $this->db->select('at_expensecashbon.*, am_branch.BranchName, am_company.CompanyName');
        $this->db->from('at_expensecashbon');
        $this->db->join('am_branch', 'am_branch.BranchCode=at_expensecashbon.BranchCode');
        $this->db->join('am_company', 'am_company.CompanyCode=am_branch.CompanyCode');
        $this->db->where('at_expensecashbon.EBonNumber', $cashbon_number);
        $query = $this->db->get();

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

    function get_cash_bon_grup($cashbon_number) {
        $this->db->select('BranchCode');
        $this->db->from('at_expensecashbon');

        $this->db->where('at_expensecashbon.EBonNumber', $cashbon_number);
        $query = $this->db->get();

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

    function get_all_journal_cashbon($cashbon_number, $branch) {
        $this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, at_ebonjournal.EBonSaldoCredit, 
						   at_ebonjournal.EBonDate, at_ebonjournal.EBonNumber, at_ebonjournal.EBonSaldoDebet,at_ebonjournal.AmountCurrency');
        $this->db->from('at_ebonjournal');
        $this->db->join('at_compaccountgl', 'at_ebonjournal.EBonAccountNumber=at_compaccountgl.AccountNumber');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        $this->db->where('at_compaccountgl.BranchCode', $branch);
        $this->db->where('at_compaccountgl.TransStatus', 2);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('at_ebonjournal.EBonNumber', $cashbon_number);
        $query = $this->db->get();

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

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

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

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

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

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

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

    function create_ebon_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 . "EC/";

        $this->db->_protect_identifiers = false;
        $this->db->select('(CASE 
                              WHEN MAX(CONVERT(SUBSTR(EBonNumber, -3),signed)) IS NULL then 1
                              ELSE MAX(CONVERT(SUBSTR(EBonNumber, -3),signed)) + 1 END) as New_Number');
        $this->db->like('EBonNumber', $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 create_ebon_number($EBonDate){
    // 	$branch     = $this->session->userdata('branch');
    //        $month      = date('m', strtotime($EBonDate));
    //        $year       = date('y', strtotime($EBonDate));
    //        $ebon_number  = $branch."/".$month.$year."EC/";
    //        $this->db->like('EBonNumber', $ebon_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;
    //        }
    //        $new_ebon_number = $ebon_number.$number;
    //        return $new_ebon_number;
    // }
    // function check_code_exist($Number) {
    //        $query = $this->db->get_where($this->table, array('EBonNumber' => $Number), 1, 0);
    //        if ($query->num_rows() > 0) {
    //            return TRUE;
    //        }
    //        else {
    //            return FALSE;
    //        }	
    //    }

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

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

    function update_to_reverse_cashbon($EBonNumber, $data_cashbon) {
        $this->db->update($this->table, $data_cashbon, array("EBonNumber" => $EBonNumber));
    }

    function get_detail_item_print($EBonNumber, $BranchCode, $operasi) {
        $this->db->select($this->table . '.*,am_dpt.DepartmentName, b_transfer.AccountName as Bank_transfer, am_receipt.EmployeeName as Emp_Receipt, branch.BranchName,branch.BranchAddress,am_prepared.EmployeeName as nm_prepared,am_dir.EmployeeName as nm_manager');
        $this->db->from($this->table);
        $this->db->join('am_accountgl as b_transfer', 'b_transfer.AccountNumber = ' . $this->table . '.BankPayment', 'INNER');
        $this->db->join('am_branch as branch', 'branch.BranchCode = ' . $this->table . '.BranchCode', 'INNER');
        $this->db->join('am_employee as am_receipt', $this->table . '.EmployeeCode=am_receipt.EmployeeCode', 'LEFT');
        $this->db->join('am_department as am_dpt', 'am_receipt.DepartmentCode=am_dpt.DepartmentCode', 'LEFT');
        $this->db->join('am_employee as am_prepared', $this->table . '.CreatedBy=am_prepared.EmployeeCode', 'LEFT');
        $this->db->join('am_employee as am_dir', $this->table . '.AppLevel1Code=am_dir.EmployeeCode', 'LEFT');
        $this->db->where('EBonNumber', $EBonNumber);
        $this->db->where($this->table . '.BranchCode ' . $operasi, $BranchCode);
        $query = $this->db->get();
        return $query->row();
    }

    public function m_cek_payment($EBonNumber) {

        $this->db->where('EBonNumber', $EBonNumber);
        $this->db->where_in('TransStatus', array(1,2,4,5,6));
        $query = $this->db->get('at_expensepayment')->result_array();

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

    public function get_no_payment($EBonNumber) {
        $this->db->select('EPayNumber');
        $this->db->where('EBonNumber', $EBonNumber);
        $query = $this->db->get('at_expensepayment')->result_array();


        if (count($query) > 0) {
            return $query[0]['EPayNumber'];
        } 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.EBonDate,cb.EBonNumber,
			cb.EBonDesc,cb.EBonAmount,br.BranchName, dp.DepartmentName,em.EmployeeName, gl.AccountName as Bank_Account');
        $this->db->from($this->table . ' as cb');
        $this->db->join($this->acc_gl . ' as gl', 'gl.AccountNumber=cb.BankPayment', 'inner');
        $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){//Dhidhik, 2 Mei 2016
        /* =================================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('am_branch.BranchName, am_department.DepartmentName, am_employee.EmployeeName, at_expensecashbon.EBonNumber, at_expensecashbon.EBonDesc,
            at_expensecashbon.EBonDate, at_expensecashbon.EBonNumber, at_ebondetail.EBonAmount, at_ebondetail.EBonMemo, created.EmployeeName as nm_createdby,
            at_expensecashbon.TransStatus');
        $this->db->from('at_expensecashbon');
        $this->db->join('at_ebondetail', 'at_expensecashbon.EBonNumber=at_ebondetail.EBonNumber', 'INNER');
        $this->db->join('am_branch', 'at_expensecashbon.BranchCode=am_branch.BranchCode', 'INNER');
        $this->db->join('am_department', 'at_expensecashbon.DepartmentCode=am_department.DepartmentCode', 'INNER');
        $this->db->join('am_employee', 'at_expensecashbon.EmployeeCode=am_employee.EmployeeCode', 'INNER');
        $this->db->join('am_employee as created', 'at_expensecashbon.CreatedBy=created.EmployeeCode', 'INNER');

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

        /* ===================================find by branch code========================================================= */
        if ($BranchCode != "") {
            $this->db->where('at_expensecashbon.BranchCode', $BranchCode);
        } else {
            $this->db->where('at_expensecashbon.BranchCode', $sess_branch);
        }
        /* =======================================================end======================================================== */

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

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

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

    function insert_transsaction($trans_data) {

        $this->db->trans_start();

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

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

        /* ===================== insert journal ================================ */
        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->insert_journal_expense_cashbon_credit($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_expense_cashbon($trans_data['update_group']['data'], $trans_data['update_group']['TransNumber']);

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

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

        /* ===================== insert journal ================================ */
        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->insert_journal_expense_cashbon_credit($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_cashbon($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->update_trans_status($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;
        }
    }

}
