<?php

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

class M_transfer_to_petty_cash extends CI_Model {

    public $table = "at_transfercash";
    public $journal = "at_tcashjournal";
    public $account = "at_compaccountgl";
    public $reconcile = "at_bankreconcille";
    public $acc_gl = "am_accountgl";
    public $branch = "am_branch";
    public $sub_type = "am_accountsubtype";
    public $period = "at_periodend";
    public $department = "am_department";
    public $employee = "am_employee";

    //function get account bank
    function get_transaksi($transfer_kas_no) {
        $this->db->select('at_transfercash.BranchCode as BranchTCash,at_transfercash.TCashDate,
							at_transfercash.TCashNumber, at_transfercash.BankTransfer,
							at_transfercash.BankReceipt, at_transfercash.TCashDesc,at_transfercash.ChequeNumber,
							at_transfercash.TCashDesc, at_transfercash.TransAmount,at_transfercash.CreatedBy,
							at_transfercash.TransStatus as TransCashStatus, at_transfercash.AppLevel1Status, 
							at_transfercash.CurrencyRate, at_transfercash.CurrencyCode, at_transfercash.CurrencyCode_to,em.DepartmentCode');
        $this->db->from('at_transfercash');
        $this->db->join('am_accountgl as account_from', 'account_from.AccountNumber = at_transfercash.BankTransfer');
        $this->db->join('am_accountgl as account_to', 'account_to.AccountNumber = at_transfercash.BankReceipt');
        $this->db->join($this->employee . ' as em', 'em.EmployeeCode=at_transfercash.CreatedBy', 'inner');
        $this->db->where('at_transfercash.TCashNumber', $transfer_kas_no);
        $this->db->order_by('at_transfercash.TCashDate', '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('FromBank.AccountName as BankFrom,ToBank.AccountName as BankTo,at_transfercash.TCashNumber,at_transfercash.TCashDate,
							at_transfercash.TransAmount,at_transfercash.TCashDesc,at_transfercash.TransStatus as TransCashStatus,at_transfercash.AppLevel1Status,am_employee.EmployeeName as Created');
        $this->db->from($this->table);
        $this->db->join('am_accountgl as FromBank', 'FromBank.AccountNumber = at_transfercash.BankTransfer');
        $this->db->join('am_accountgl as ToBank', 'ToBank.AccountNumber = at_transfercash.BankReceipt');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_transfercash.BranchCode');
        $this->db->join('am_employee', $this->table . '.CreatedBy=am_employee.EmployeeCode', 'LEFT');

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

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

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

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

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

    function get_id_journal($tcash_number) {
        $this->db->select('TCashJournalID');
        $this->db->from('at_tcashjournal');
        $this->db->where('TCashNumber', $tcash_number);
        return $this->db->get()->result();
    }

    // function create_paty_cash_number($date){        
    //        $branch_code    = $this->session->userdata('branch');
    //        $month          = date('m', strtotime($date));
    //        $year           = date('y', strtotime($date));
    //        $payment_tcash    = $branch_code."/".$month.$year."TC/";
    //        $this->db->like('TCashNumber',$payment_tcash);
    //        $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_tcash.$number;
    //    }

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

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

    function get_transstatus($tcash_number){
        $this->db->select('TransStatus');
        $this->db->from($this->table);
        $this->db->where('TCashNumber',$tcash_number);

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

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

        $tc_number = $branch . "/" . $month . $year . "TC/";

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

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

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

            $new_tc_number = $tc_number . $number;
        }

        return $new_tc_number;
    }

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

    function get_latest_trans_to_petty_cash_no($sess_branch) {
        $this->db->select_max('TCashNumber');
        $query = $this->db->get_where($this->table, array('BranchCode' => $sess_branch));

        return $query->result();
    }

    function get_accountgl() {
        $this->db->select('*');
        $this->db->from('at_compaccountgl');
        $this->db->join('am_accountgl', 'am_accountgl.AccountNumber = at_compaccountgl.AccountNumber');
        $this->db->join('am_accounttype', 'am_accounttype.AccountTypeID = am_accountgl.AccountTypeID');
        $this->db->like('am_accounttype.AccountTypeName', 'Cash / Bank', 'both');

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

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

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

    function update_group($data_transfer, $TCashNumber) {
        $this->db->update($this->table, $data_transfer, array('TCashNumber' => $TCashNumber));
    }

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

    function get_bank_bycompany($str_replace_branch) {
        $BranchStatus = $this->session->userdata('branch_status');
        $BranchCode = $this->session->userdata('branch');
        $this->db->select('ag.AccountName, ag.AccountNumber, ag.CurrencyCode, br.BranchName');
        $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', 'ac.BranchCode=br.BranchCode', 'inner');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID=ag.AccountSubTypeID', 'inner');

        if ($BranchStatus == 0) {
            $this->db->where('ac.BranchCode', $str_replace_branch);
            $this->db->where('ac.GLSaldoYears', date('Y'));
            //$this->db->where('am_branch.BranchStatus', 0);
        } else {
            //$this->db->where('at_compaccountgl.CompanyCode', $sess_company);
            $this->db->where('ac.BranchCode', $str_replace_branch);
            $this->db->where('ac.GLSaldoYears', date('Y'));
        }

        $this->db->where('sub.AccountTypeName', 'Bank');
        $this->db->where('ag.AccountChild', 1);
        $this->db->where('ac.TransStatus', 2);
        $query = $this->db->get();

        return $query->result();
    }

    function get_petty_cash_bycompany($str_replace_branch, $currency_idr, $currency_code) {

        $BranchStatus = $this->session->userdata('branch_status');
        $BranchCode = $this->session->userdata('branch');
        $this->db->select('ag.AccountName, ag.AccountNumber, ag.CurrencyCode, br.BranchName');
        $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', 'ac.BranchCode=br.BranchCode', 'inner');
        $this->db->join($this->sub_type . ' as sub', 'sub.AccountSubTypeID=ag.AccountSubTypeID', 'inner');

        if ($BranchStatus == 0) {
            $this->db->where('ac.BranchCode', $str_replace_branch);
            $this->db->where('ac.GLSaldoYears', date('Y'));
            //$this->db->where('am_branch.BranchStatus', 0);
        } else {
            //$this->db->where('at_compaccountgl.CompanyCode', $sess_company);
            $this->db->where('ac.BranchCode', $str_replace_branch);
            $this->db->where('ac.GLSaldoYears', date('Y'));
        }

        $arry_currency = array($currency_code);
        $this->db->where_in('ag.CurrencyCode', $arry_currency);

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

        return $query->result();
    }

    function get_datatojournal($transcash_numb) {
        $this->db->select('am_branch.BranchName,at_transfercash.TCashDate, at_transfercash.BranchCode, at_transfercash.TCashNumber,at_transfercash.TCashDesc,
							at_transfercash.TransAmount,at_transfercash.TransStatus as transferStatus');
        $this->db->from($this->table);
        $this->db->join('am_branch', 'am_branch.BranchCode = at_transfercash.BranchCode');
        $this->db->where('at_transfercash.TCashNumber', $transcash_numb);
        $query = $this->db->get();
        return $query->result();
    }

    function get_datatojournal_detail($transcash_numb, $branch_code) {
        $this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, at_tcashjournal.TCashSaldoDebet, 
						   at_tcashjournal.TCashSaldoCredit, at_tcashjournal.TCashDesc, at_tcashjournal.AmountCurrency, at_tcashjournal.TCashNumber');
        $this->db->from('at_compaccountgl');
        $this->db->join('at_tcashjournal', 'at_compaccountgl.AccountNumber=at_tcashjournal.TCashAccountNumber');
        $this->db->join('am_accountgl', 'at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
        //$this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
        $this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
        $this->db->where('at_compaccountgl.TransStatus', 2);
        $this->db->where('at_compaccountgl.BranchCode', $branch_code);
        $this->db->where('at_tcashjournal.TCashNumber', $transcash_numb);

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

        return $query->result();
    }

    function get_all_by_no($trans_number) {
        $this->db->select('FromBank.AccountName as BankFrom,ToBank.AccountName as BankTo,at_transfercash.TCashNumber,at_transfercash.TCashDate,
						at_transfercash.TransAmount,at_transfercash.TCashDesc,at_transfercash.TransStatus as trans_cash_stat,at_transfercash.AppLevel1Status');
        $this->db->from($this->table);
        $this->db->join('am_accountgl as FromBank', 'FromBank.AccountNumber = at_transfercash.BankTransfer');
        $this->db->join('am_accountgl as ToBank', 'ToBank.AccountNumber = at_transfercash.BankReceipt');
        $this->db->where('at_transfercash.TCashNumber', $trans_number);
        $query = $this->db->get();
        return $query->result();
    }

    function get_to_print($trans_number) {
        $this->db->select('at_transfercash.*, FromBank.AccountName as BankFrom,ToBank.AccountName as BankTo,branch.BranchName,branch.BranchAddress, prepared.EmployeeName as nm_prepared, mng.EmployeeName as nm_manager');
        $this->db->from($this->table);
        $this->db->join('am_accountgl as FromBank', 'FromBank.AccountNumber = at_transfercash.BankTransfer', 'INNER');
        $this->db->join('am_accountgl as ToBank', 'ToBank.AccountNumber = at_transfercash.BankReceipt', 'INNER');
        $this->db->join('am_branch as branch', 'branch.BranchCode = at_transfercash.BranchCode', 'INNER');
        $this->db->join('am_employee as prepared', 'prepared.EmployeeCode = at_transfercash.CreatedBy', 'LEFT');
        $this->db->join('am_employee as mng', 'mng.EmployeeCode = at_transfercash.AppLevel1Code', 'LEFT');
        $this->db->where('at_transfercash.TCashNumber', $trans_number);
        $query = $this->db->get();
        return $query->result_array();
    }

    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($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('FromBank.AccountName as BankFrom,ToBank.AccountName as BankTo,at_transfercash.TCashNumber,at_transfercash.TCashDate,
							at_transfercash.TransAmount,at_transfercash.TCashDesc,at_transfercash.TransStatus as TransCashStatus,at_transfercash.AppLevel1Status,am_employee.EmployeeName as Created');
        $this->db->from($this->table);
        $this->db->join('am_accountgl as FromBank', 'FromBank.AccountNumber = at_transfercash.BankTransfer');
        $this->db->join('am_accountgl as ToBank', 'ToBank.AccountNumber = at_transfercash.BankReceipt');
        $this->db->join('am_branch', 'am_branch.BranchCode = at_transfercash.BranchCode');
        $this->db->join('am_employee', $this->table . '.CreatedBy=am_employee.EmployeeCode', 'LEFT');

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

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

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

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

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

    function get_all_to_print_LAMA($sess_branch) {
        $this->db->select('FromBank.AccountName as BankFrom,ToBank.AccountName as BankTo,at_transfercash.TCashNumber,at_transfercash.TCashDate,
							at_transfercash.TransAmount,at_transfercash.TCashDesc,at_transfercash.TransStatus as TransCashStatus,at_transfercash.AppLevel1Status');
        $this->db->from($this->table);
        $this->db->join('am_accountgl as FromBank', 'FromBank.AccountNumber = at_transfercash.BankTransfer');
        $this->db->join('am_accountgl as ToBank', 'ToBank.AccountNumber = at_transfercash.BankReceipt');
        $this->db->where($this->table . '.BranchCode', $sess_branch);

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

    function update_to_reverse($trans_to_petty_cash_no, $data_petty_cash) {
        $this->db->update($this->table, $data_petty_cash, array('TCashNumber' => $trans_to_petty_cash_no));
    }

    function update_approved_data($data_approved, $trans_to_petty_cash_no) {
        $this->db->update($this->table, $data_approved, array('TCashNumber' => $trans_to_petty_cash_no));
    }

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

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

    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_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->update_group($trans_data['update_group']['data'], $trans_data['update_group']['TransNumber']);

        $this->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_data($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($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;
        }
    }

}
