<?php

/* 	
 * 	Author 	: Tagor
 * 	Tanggal : 06-10-2014	
 * 	Revisi 	: 
 */

class M_payment extends CI_Model {

    public $table = "at_cashpayment";
    public $detail = "at_cpaydetail";
    public $branch = "am_branch";
    public $company = "am_company";
    public $employee = "am_employee";
    public $department = "am_department";
    public $reconcile = "at_bankreconcille";
    public $account = "at_compaccountgl";
    public $acc_gl = "am_accountgl";
    public $sub_type = "am_accountsubtype";
    public $acc_type = "am_accounttype";
    public $period = "at_periodend";

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

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

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

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

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

        /* ==========================================by session======================================================= */
        $sess_company = $this->session->userdata('company');
        $sess_branch = $this->session->userdata('branch');
        $sess_branch_status = $this->session->userdata('branch_status');
        /* =========================================================================================================== */

        $this->db->select('at_cashpayment.BranchCode as cpay_branch,am_accountgl.AccountName,at_cashpayment.CPayNumber,at_cashpayment.CreatedBy,
							at_cashpayment.CPayDate,at_cashpayment.CPayTotalAmount,at_cashpayment.TransStatus as PaymentStatus,
							at_cashpayment.CPayDesc,am_branch.BranchName');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_cashpayment.BranchCode');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.BankPayment');

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

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

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

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

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

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

        /* =================================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_cashpayment.BranchCode as cpay_branch,am_accountgl.AccountName,at_cashpayment.CPayNumber,at_cashpayment.CreatedBy,
							at_cashpayment.CPayDate,at_cashpayment.CPayTotalAmount,at_cashpayment.TransStatus as PaymentStatus,
							at_cashpayment.CPayDesc,am_branch.BranchName');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_cashpayment.BranchCode');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.BankPayment');

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

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

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

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

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

    function get_company_by_branch($sess_branch) {
        $this->db->select('CompanyName,BranchName');
        $this->db->from($this->branch . ' as br');
        $this->db->join($this->company . ' as cp', 'cp.CompanyCode = br.CompanyCode', 'inner');
        $this->db->where('br.BranchCode', $sess_branch);
        $query = $this->db->get();
        return $query->row();
    }

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

        $pay_number = $branch_code . "/" . $month . $year . "PM/";

        $this->db->like('CPayNumber', $pay_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 $pay_number . $number;
    }

    function get_by_payment_number($payment_number) {
        $this->db->select('at_cashpayment.BranchCode as cpay_branch,am_accountgl.AccountName,at_cashpayment.CPayNumber,at_cashpayment.CPayDate,
							at_cashpayment.CPayTotalAmount,at_cashpayment.TransStatus as PaymentStatus,at_cashpayment.CPayDesc,am_branch.BranchName');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_cashpayment.BranchCode');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_cashpayment.BankPayment');
        $this->db->where($this->table . '.CPayNumber', $payment_number);
        $query = $this->db->get();
        return $query->result();
    }

    /* function account_petty_cash($sess_company){
      $this->db->select('am_accountgl.AccountNumber,am_accountgl.AccountName');
      $this->db->from('am_accountgl');
      $this->db->join('at_compaccountgl','at_compaccountgl.AccountNumber = am_accountgl.AccountNumber');
      $this->db->where('at_compaccountgl.CompanyCode', $sess_company);
      $this->db->where('am_accountgl.AccountSubTypeID', 5);
      $this->db->where('am_accountgl.AccountChild', 1);
      $this->db->where('am_accountgl.TransStatus', 2);
      $query = $this->db->get();

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

    function account_petty_cash($sess_company, $sess_branch) {
        $this->db->select('ag.AccountNumber,ag.AccountName,ag.CurrencyCode');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->acc_gl . ' as ag', 'ac.AccountNumber = ag.AccountNumber', 'inner');
        $this->db->join($this->sub_type . ' as sub', 'ag.AccountSubTypeID=sub.AccountSubTypeID', 'inner');
        //if($this->session->userdata('jobtitle') != "DIR"){
        $this->db->where('ac.CompanyCode', $sess_company);
        $this->db->where('ac.BranchCode', $sess_branch);
        //}
        $this->db->where('ac.GLSaldoYears', date('Y'));
        $this->db->where('sub.AccountTypeName', 'Petty Cash');
        $this->db->where('ag.CurrencyCode', 'IDR');
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('ac.TransStatus', 2);
        $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']) : '';
        $where = "((acc.AccountTypeName='COGS / COGM (Cost Of Goods Made)') and (sub.AccountTypeName!='COGS / COGM (Cost Of Goods Made)'))";

        $this->db->select('ag.AccountNumber,ag.AccountName');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->acc_gl . ' as ag', 'ac.AccountNumber = ag.AccountNumber', 'inner');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID = ag.AccountSubTypeID', 'inner');
        $this->db->join($this->acc_type . ' as acc', 'acc.AccountTypeID = sub.AccountTypeID', 'inner');
        $this->db->where('ag.TransStatus', 2);
        $this->db->where('ag.AccountChild !=', 0);
        $this->db->where('ac.CompanyCode', $company_code);
        $this->db->where('ac.BranchCode', $branch_code);
        $this->db->where('ac.GLSaldoYears', date('Y'));
        $this->db->where_in('acc.AccountTypeName', array('Expense', 'Other Expense'));
        $this->db->or_where($where);

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

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

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

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

    function get_department_by_company($company_code) {
        $CodeDepartment = $this->input->post('CodeDepartment');
        $NameDepartment = $this->input->post('NameDepartment');
        if ($CodeDepartment != "" || $NameDepartment != "") {
            $this->db->select('*');
            $this->db->from('am_department');
            $this->db->like('am_department.DepartmentCode', $CodeDepartment);
            $this->db->like('am_department.DepartmentName', $NameDepartment);
            //if($this->session->userdata('jobtitle') != "DIR"){	
            $this->db->where('am_department.CompanyCode', $company_code);
            //}
            $query = $this->db->get();
        } else {
            $this->db->select('*');
            $this->db->from('am_department');
            //if($this->session->userdata('jobtitle') != "DIR"){	
            $this->db->where('am_department.CompanyCode', $company_code);
            //}
            $query = $this->db->get();
        }
        return $query->result();
    }

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

    function get_payment_detail($payment_number) {
        $this->db->_protect_identifiers = false;
        $this->db->select($this->detail . '.CPayAmount,' . $this->detail . '.CPayMemo,am_accountgl.AccountNumber,am_accountgl.AccountName, "0" as RowStatus,
							am_department.DepartmentName,' . $this->detail . '.CPayDetailID,' . $this->detail . '.CPayDeptCode as DepartmentAccountCode');
        $this->db->from($this->detail);
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->detail . '.CPayAccountNumber', 'INNER');
        $this->db->join('am_department', 'am_department.DepartmentCode = ' . $this->detail . '.CPayDeptCode', 'INNER');
        $this->db->where($this->detail . '.CPayNumber', $payment_number);
        $query = $this->db->get();
        $this->db->_protect_identifiers = true;

        return $query->result();
    }

    function get_latest_payment_number($sess_branch) {
        $this->db->select_max('CPayNumber');
        $query = $this->db->get_where($this->table, array('BranchCode' => $sess_branch));
        return $query->result();
    }

    function get_accountnumber($account_name) {
        $query = $this->db->get_where('am_accountgl', array('AccountName' => $account_name));
        return $query->result();
    }

    function get_detail_paymen($id) {
        $this->db->select('at_cpaydetail.CPayAccountNumber');
        $this->db->from('at_cpaydetail');
        $this->db->where($this->detail . '.CPayNumber', $id);
        //$this->db->where($this->detail.'.CPayAccountNumber', $acc);
        $query = $this->db->get();
        return $query->row();
    }

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

        $this->db->select('ac.AccountNumber,ag.AccountName,ag.CurrencyCode');
        $this->db->from($this->account . ' as ac');
        $this->db->join($this->acc_gl . ' as ag', 'ag.AccountNumber=ac.AccountNumber', 'inner');
        $this->db->join($this->branch . ' as br', 'br.BranchCode=ac.BranchCode', 'inner');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID=ag.AccountSubTypeID', 'inner');

        if ($jobtitle == "DIR") {
            $this->db->where('ac.GLSaldoYears', date('Y'));
            $this->db->where('ac.BranchCode', $branch);
        } else {
            if ($BranchStatus == 0) {
                $this->db->where('ac.CompanyCode', $CompanyCode);
                $this->db->where('ac.GLSaldoYears', date('Y'));
                $this->db->where('ac.BranchCode', $branch);
            } else {
                $this->db->where('ac.CompanyCode', $CompanyCode);
                $this->db->where('ac.BranchCode', $branch);
                $this->db->where('ac.GLSaldoYears', date('Y'));
            }
        }

        $this->db->where('ag.CurrencyCode', 'IDR');
        $this->db->where('sub.AccountTypeName', 'Petty Cash');
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('ac.TransStatus', 2);

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

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

    function get_departmentcode($department_name) {
        $query = $this->db->get_where('am_department', array('DepartmentName' => $department_name));
        return $query->result();
    }

    // function check_code_exist($id) {
    // 	$query = $this->db->get_where($this->table, array('CPayNumber' => $id), 1, 0);
    // 	if ($query->num_rows() > 0) {
    // 		return TRUE;
    // 	}
    // 	else {
    // 		return FALSE;
    // 	}	
    // }

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

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

    // Function Insert detail (debet)
    function insert_fromgrid($data_grid) {
        $this->db->insert('at_cpaydetail', $data_grid);

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

    // Function Insert to journal kredit
    function insert_tojournal_kredit($data_journal_debet) {
        $this->db->insert('at_cpayjournal', $data_journal_debet);

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

    // Function Insert to journal
    function insert_tojournal($data_journal_credit) {
        $this->db->insert('at_cpayjournal', $data_journal_credit);

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

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

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

    function update_jurnal_appfinis($stat_appfinished, $id) {
        $this->db->update('at_cpayjournal', $stat_appfinished, array('CPayNumber' => $id));

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

    function update_jurnal_debet($data_journal_debet, $id, $acc) {
        $this->db->update('at_cpayjournal', $data_journal_debet, array('CPayNumber' => $id, 'CPayAccountNumber' => $acc));

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

    function update_jurnal_kredit($data_journal_credit, $id) {
        $this->db->update('at_cpayjournal', $data_journal_credit, array('CPayNumber' => $id, 'StatusNilai' => 0));

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

    function suspend($data, $id) {
        $this->db->update($this->table, $data, array('CPayNumber' => $id));
    }

    function get_branch_byaccnumb($account_number) {
        $this->db->select('at_compaccountgl.*,am_company.CompanyCode,am_branch.CompanyCode,am_branch.BranchCode');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_company', 'am_company.CompanyCode = at_compaccountgl.CompanyCode');
        $this->db->join('am_branch', 'am_branch.CompanyCode = am_company.CompanyCode');
        $this->db->where('at_compaccountgl.AccountNumber', $account_number);
        $query = $this->db->get();
        return $query->result();
    }

    // Function get data to journal
    function get_company_name($company_code) {
        return $this->db->get_where('am_company', array('CompanyCode' => $company_code))->row();
    }

    function get_datatojournal($id) {
        $this->db->select('am_branch.BranchName,am_accountgl.AccountName,at_cashpayment.CPayNumber,at_cashpayment.CPayDate,at_cashpayment.CPayTotalAmount,
							at_cashpayment.CPayDesc, am_company.CompanyName, at_cashpayment.TransStatus as PaymentStatus');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_cashpayment.BranchCode');
        $this->db->join('am_company', 'am_company.CompanyCode=am_branch.CompanyCode');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_cashpayment.BankPayment');
        $this->db->where('at_cashpayment.CPayNumber', $id);
        $query = $this->db->get();
        return $query->result();
    }

    // Function get data to journal detail
    function get_datatojournal_detail($payment_number, $CompanyCode, $id_branch) {
        $this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, at_cpayjournal.CPaySaldoCredit as CreditAmount, 
						   at_cpayjournal.CPaySaldoDebet as DebetAmount');
        $this->db->from('at_cpayjournal');
        $this->db->join('at_compaccountgl', 'at_compaccountgl.AccountNumber=at_cpayjournal.CPayAccountNumber');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');

        if ($this->session->userdata("jobtitle_status") == 0) {
            $this->db->where('at_compaccountgl.BranchCode', $id_branch);
            $this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
        } else {
            $this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
            $this->db->where('at_compaccountgl.BranchCode', $id_branch);
        }

        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('at_cpayjournal.CPayNumber', $payment_number);
        $query = $this->db->get();

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

    function get_department_name($sess_department) {
        $this->db->select('DepartmentName');
        $this->db->from('am_department');
        $this->db->where('am_department.DepartmentCode', $sess_department);
        return $this->db->get()->row();
    }

    function get_all_by_no($payment_number) {
        $this->db->select('TransStatus');
        $this->db->from($this->table);
        $this->db->where('at_cashpayment.CPayNumber', $payment_number);
        $query = $this->db->get();
        return $query->result();
    }

    function get_all_to_print_LAMA($sess_branch) {
        $this->db->select('at_cashpayment.BranchCode as cpay_branch,am_accountgl.AccountName,at_cashpayment.CPayNumber,at_cashpayment.CPayDate,
						at_cashpayment.CPayTotalAmount,at_cashpayment.TransStatus as PaymentStatus,at_cashpayment.CPayDesc,am_branch.BranchName');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_cashpayment.BranchCode');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->table . '.BankPayment');
        $this->db->where($this->table . '.BranchCode', $sess_branch);

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

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

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

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

    function create_jc_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 . "PM/";

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

    /* ==============================funngsi get detail untuk print preview================================= */

    function get_detail_print($CPay_Number) {

        $this->db->select('cp.*, am_acctgl.AccountName, am_br.BranchName, prepared.EmployeeName as nm_prepared, mng.EmployeeName as nm_manager');
        $this->db->from($this->table . ' as cp');
        $this->db->join('at_compaccountgl as at_acctgl', 'at_acctgl.AccountNumber=cp.BankPayment', 'INNER');
        $this->db->join('am_accountgl as am_acctgl', 'at_acctgl.AccountNumber=am_acctgl.AccountNumber', 'INNER');
        $this->db->join('am_branch as am_br', 'am_br.BranchCode=cp.BranchCode', 'INNER');
        $this->db->join('am_employee as prepared', 'prepared.EmployeeCode=cp.CreatedBy', 'LEFT');
        $this->db->join('am_employee as mng', 'mng.EmployeeCode=cp.AppLevel1Code', 'LEFT');
        $this->db->where('CPayNumber', $CPay_Number);
        $this->db->where('(case when am_br.BranchStatus = 0 then at_acctgl.PLStatus in (0, 1) else at_acctgl.PLStatus = 1 end)');
        $this->db->where('at_acctgl.GLSaldoYears', date('Y'));
        $this->db->where('am_acctgl.AccountChild', 1);
        $this->db->where('am_acctgl.TransStatus', 2);
        $query = $this->db->get();

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

    // Daswan get detail item print
    function get_detail_item_print($payment_number) {
        $this->db->select($this->detail . '.CPayAmount,' . $this->detail . '.CPayMemo,am_accountgl.AccountNumber,am_accountgl.AccountName,
							am_department.DepartmentName,' . $this->detail . '.CPayDetailID,' . $this->detail . '.CPayDeptCode as DepartmentAccountCode');
        $this->db->from($this->detail);
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = ' . $this->detail . '.CPayAccountNumber', 'INNER');
        $this->db->join('am_department', 'am_department.DepartmentCode = ' . $this->detail . '.CPayDeptCode', 'INNER');
        $this->db->where($this->detail . '.CPayNumber', $payment_number);
        $query = $this->db->get();
        return $query->result_array();
    }

    function get_data_account($payment_number) {
        $this->db->select("CPayAccountNumber,CPaySignStatus,MONTH(CPayDate) as month_journal,YEAR(CPayDate) as year_journal,
    		(case when CPaySaldoDebet = 0 then CPaySaldoCredit else CPaySaldoDebet end) as total_amount");
        $this->db->from("at_cpayjournal");
        $this->db->where("CpayNumber", $payment_number);
        $query = $this->db->get()->result_array();
        if (count($query) > 0) {
            return $query;
        } else {
            $no_false = 0;  // hasil query 0
            return $no_false;
        }
    }

    function get_AccountBsStatus($acc_number) {
        $this->db->select("am_ast.AccountBSStatus");
        $this->db->from("am_accountgl as am_agl");
        $this->db->join("am_accountsubtype as am_ast", "am_ast.AccountSubTypeID=am_agl.AccountSubTypeID", "INNER");
        $this->db->where("AccountNumber", $acc_number);
        $query = $this->db->get()->result_array();

        return $query[0]['AccountBSStatus'];
    }

    function update_to_reverse_payment($pay_number, $data_cash_payment) {

        $this->db->update($this->table, $data_cash_payment, array("CPayNumber" => $pay_number));
    }

    function m_update_group($data, $pay_number) {
        $this->db->update($this->table, $data, array('CPayNumber' => $pay_number));
    }

    function m_update_detail($detail_data, $id) {
        $this->db->update($this->detail, $detail_data, array('CPayDetailID' => $id));
    }

    function m_update_journal($data, $pay_number, $acc_number) {
        $this->db->update("at_cpayjournal", $data, array('CPayNumber' => $pay_number, 'CPayAccountNumber' => $acc_number));
    }

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

    function m_delete_group($CPayNumber) {
        $this->db->where('CPayNumber', $CPayNumber);
        $this->db->delete($this->table);
    }

    function m_delete_detail($CPayDetailID) {
        $this->db->where('CPayDetailID', $CPayDetailID);
        $this->db->delete($this->detail);
    }

    function m_delete_journal($pay_number) {
        $this->db->where('CpayNumber', $pay_number);
        //$this->db->where('CPayAccountNumber',$acc_number);
        $this->db->delete('at_cpayjournal');
    }

    function m_insert_group($data) {
        $this->db->insert($this->table, $data);
    }

    function m_insert_detail($data) {
        $this->db->insert($this->detail, $data);
    }

    function m_insert_journal($data) {
        $this->db->insert('at_cpayjournal', $data);
    }

    function delete_reconcile($pay_number) {
        $this->db->where('TransNumber', $pay_number);
        $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 insert_transsaction($trans_data) {

        $this->db->trans_start();

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

        for ($x = 0; $x < count($trans_data['insert_detail']['data']); $x++) {
            $this->m_insert_detail($trans_data['insert_detail']['data'][$x]);
        }

        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->m_insert_journal($trans_data['insert_journal']['data'][$x]);
        }

        $this->db->trans_complete();

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

    function update_transsaction($trans_data) {

        $this->db->trans_start();

        $this->m_update_group($trans_data['update_group']['data'], $trans_data['update_group']['TransNumber']);

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

        if ($trans_data['delete_detail']['total'] > 0) {
            for ($x = 0; $x < count($trans_data['delete_detail']['CPayDetailID']); $x++) {
                $this->m_delete_detail($trans_data['delete_detail']['CPayDetailID'][$x]);
            }
        }

        $this->m_delete_journal($trans_data['delete_journal']['TransNumber']);

        for ($x = 0; $x < count($trans_data['insert_journal']['data']); $x++) {
            $this->m_insert_journal($trans_data['insert_journal']['data'][$x]);
        }

        $this->db->trans_complete();

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

    function approve_transsaction($trans_data) {

        $this->db->trans_start();

        $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 suspend_transsaction($trans_data) {

        $this->db->trans_start();

        $this->suspend($trans_data['suspend_data']['data'], $trans_data['suspend_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();

        $this->update_to_reverse_payment($trans_data['reverse_data']['TransNumber'], $trans_data['reverse_data']['data']);
        $this->delete_reconcile($trans_data['delete_reconcile']['TransNumber']);

        $this->db->trans_complete();

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

}
