<?php
class M_swapbank extends CI_Model {
    
	public $table 		= "at_banksweep";
	public $account 	= "at_compaccountgl";
	public $reconcile 	= "at_bankreconcille";
	
	function count_swapbank_number($bulan, $cek_tahun, $BranchCode){
	    $this->db->select('*');
		$this->db->from($this->table);
		$this->db->where('MONTH(BSweepDate)', $bulan);
		$this->db->where('YEAR(BSweepDate)', $cek_tahun);
		$this->db->where('BranchCode', $BranchCode);
		return $this->db->get()->num_rows();
	}
	
	function get_last_swapbank_number($BranchCode){
		$qty="SELECT MAX(BSweepNumber) as BSweepNumber FROM at_banksweep WHERE BranchCode='$BranchCode'";
		return $this->db->query($qty)->result();
	}
	
	function get_coa_bank_cabang_add($CompanyCode){
	    $jobtitle = $this->session->userdata("jobtitle_status");
		$BranchCode 	= $this->session->userdata('branch');
		$BranchStatus 	= $this->session->userdata('branch_status');
		
		$this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, am_accountgl.CurrencyCode');
		$this->db->from($this->account);
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');		
		$this->db->join('am_branch','at_compaccountgl.BranchCode=am_branch.BranchCode');
		
		if($jobtitle != 0){
				$this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
				$this->db->where('at_compaccountgl.BranchCode', $BranchCode);
				$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		}
		else{
			$this->db->where('at_compaccountgl.BranchCode', $BranchCode);
			$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		}
		
		/* $this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
		$this->db->where('am_branch.BranchStatus', 1);	 */
		$this->db->where('am_accountgl.CurrencyCode', "IDR");
		$this->db->where('am_accountgl.AccountSubTypeID', 7);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.TransStatus', 2);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_coa_bank_cabang($CompanyCode, $replace){
	    $jobtitle = $this->session->userdata("jobtitle_status");
		//$BranchCode 	= $this->session->userdata('branch');
		$BranchStatus 	= $this->session->userdata('branch_status');
		
		$this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, am_accountgl.CurrencyCode');
		$this->db->from($this->account);
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');		
		$this->db->join('am_branch','at_compaccountgl.BranchCode=am_branch.BranchCode');
		
		if($jobtitle != 0){
			$this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
			$this->db->where('at_compaccountgl.BranchCode', $replace);
			$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		}
		else{
			$this->db->where('at_compaccountgl.BranchCode', $replace);
			$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		}
		
		/* $this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
		$this->db->where('am_branch.BranchStatus', 1);	 */
		$this->db->where('am_accountgl.CurrencyCode', "IDR");
		$this->db->where('am_accountgl.AccountSubTypeID', 7);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.TransStatus', 2);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_coa_bank_add($CompanyCode){
	    $jobtitle = $this->session->userdata("jobtitle_status");
		$BranchCode 	= $this->session->userdata('branch');
		$BranchStatus 	= $this->session->userdata('branch_status');
		
		$this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, am_accountgl.CurrencyCode');
		$this->db->from($this->account);
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');		
		$this->db->join('am_branch','at_compaccountgl.BranchCode=am_branch.BranchCode');		
		
		$this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
		//$this->db->where('at_compaccountgl.BranchCode', $BranchCode);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));	
		$this->db->where('am_branch.BranchStatus', 0);
		
		$this->db->where('am_accountgl.CurrencyCode', "IDR");
		$this->db->where('am_accountgl.AccountSubTypeID', 7);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.TransStatus', 2);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
		
	}
	
	function get_coa_bank($CompanyCode, $replace){
	    $jobtitle = $this->session->userdata("jobtitle_status");
		$BranchCode 	= $this->session->userdata('branch');
		$BranchStatus 	= $this->session->userdata('branch_status');
		
		$this->db->select('am_accountgl.AccountNumber, am_accountgl.AccountName, am_accountgl.CurrencyCode');
		$this->db->from($this->account);
		$this->db->join('am_accountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');		
		$this->db->join('am_branch','at_compaccountgl.BranchCode=am_branch.BranchCode');		
		
		//$this->db->where('at_compaccountgl.CompanyCode', $CompanyCode);
		$this->db->where('at_compaccountgl.BranchCode', $replace);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));	
		
		$this->db->where('am_accountgl.CurrencyCode', "IDR");
		$this->db->where('am_accountgl.AccountSubTypeID', 7);
		$this->db->where('am_accountgl.AccountChild', 1);
		$this->db->where('am_accountgl.TransStatus', 2);
		
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
		
	}
	
	function create_sweep_number($date){        
        $branch_code    = $this->session->userdata('branch');
        $month          = date('m', strtotime($date));
        $year           = date('y', strtotime($date));
	
        $sweep_number    = $branch_code."/".$month.$year."SB/";

        $this->db->like('BSweepNumber',$sweep_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  $sweep_number.$number;
    }
	
	function insert_swapbank($data_swepbank){
	   
	   
		$this->db->insert($this->table, $data_swepbank);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function insert_journal_debit_swapbank($journal_debet){
	   
	   
		$this->db->insert('at_bsweepjournal', $journal_debet);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function insert_journal_credit_swapbank($journal_credit){
		$this->db->insert('at_bsweepjournal', $journal_credit);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_swapbank($data_swapbank, $txt_swapbank_no){
		$this->db->where('BSweepNumber', $txt_swapbank_no);
		$this->db->update($this->table, $data_swapbank);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_journal_debit_swapbank($journal_debet, $txt_swapbank_no){
		$this->db->where('BSweepNumber', $txt_swapbank_no);
		$this->db->where('BSweepSaldoCredit', 0);
		$this->db->update('at_bsweepjournal', $journal_debet);
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function update_journal_credit_swapbank($journal_credit, $txt_swapbank_no){
		$this->db->where('BSweepNumber', $txt_swapbank_no);
		$this->db->where('BSweepSaldoDebet', 0);
		$this->db->update('at_bsweepjournal', $journal_credit);
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function cek_trans_status($id){
		$this->db->select('TransStatus');
		$this->db->from('at_banksweep');
		$this->db->where('BSweepNumber', $id);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $TransStatus = $row->TransStatus;
            }
            return $TransStatus;
        }
        else {
            return FALSE;
        }	
	}
	
	function cek_app_mgr($id){
		$this->db->select('AppLevel1Status');
		$this->db->from('at_banksweep');
		$this->db->where('BSweepNumber', $id);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AppLevel1Status = $row->AppLevel1Status;
            }
            return $AppLevel1Status;
        }
        else {
            return FALSE;
        }	
	}
	
	function cek_app_dir($id){
		$this->db->select('AppLevel2Status');
		$this->db->from('at_banksweep');
		$this->db->where('BSweepNumber', $id);
		$query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $AppLevel2Status = $row->AppLevel2Status;
            }
            return $AppLevel2Status;
        }
        else {
            return FALSE;
        }	
	}
	
	function pembatalan_transaksi($id, $data){
		$this->db->where('BSweepNumber', $id);
		$this->db->update($this->table, $data);
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	function suspend($data, $id) {
        $this->db->update($this->table, $data, array('BSweepNumber' => $id));
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return 0;
		}
		else {
			//$this->db->trans_commit();
			return 1;
		}
	}
	
	
	function get_detail($id){
        $query = $this->db->get_where($this->table, array('BSweepNumber' => $id));
		if($query->num_rows()>0){
			return $query->row();
		}
		else{
			return false;
		}      
    }	
	
	function get_branchCode($sweepbank_number){
		$this->db->select('*');
		$this->db->from($this->table);
		$this->db->where('BSweepNumber', $sweepbank_number);
		$query = $this->db->get();
		
		return $query->row();
	}
	
	function get_rec_no($sweepbank_number){
		$this->db->select('*, am_company.CompanyName');
		$this->db->from($this->table);
		$this->db->join('am_branch','am_branch.BranchCode=at_banksweep.BranchCode');
		$this->db->join('am_company','am_company.CompanyCode=am_branch.CompanyCode');
		$this->db->where('BSweepNumber', $sweepbank_number);
		$query = $this->db->get();
		
		return $query->result();
	}
	
	function get_date_journal_sweppbank($sweepbank_number){
		$this->db->select('*, am_company.CompanyName');
		$this->db->from($this->table);
		$this->db->join('am_branch','am_branch.BranchCode=at_banksweep.BranchCode');
		$this->db->join('am_company','am_company.CompanyCode=am_branch.CompanyCode');
		$this->db->where('BSweepNumber', $sweepbank_number);
		$query = $this->db->get();
		
		$result=$query->result();
		return json_encode($result);
	}
	
	function get_all_journal_sweppbank($sweepbank_number, $CompRecpCode){
		$this->db->select('*, am_accountgl.AccountName');
		$this->db->from('at_bsweepjournal');
		$this->db->join('am_accountgl','am_accountgl.AccountNumber=at_bsweepjournal.BSweepAccountNumber');
		$this->db->join('at_compaccountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		//$this->db->where('at_bsweepjournal.BranchCode', $CompRecpCode);
		$this->db->where('at_compaccountgl.BranchCode', $CompRecpCode);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('at_bsweepjournal.BSweepNumber', $sweepbank_number);
		$query = $this->db->get();
		
		$result = $query->result();
		return json_encode($result);
	}
	
	function get_all_journal_sweppbank_trans($sweepbank_number, $CompTransCode){
		$this->db->select('*, am_accountgl.AccountName');
		$this->db->from('at_bsweepjournal');
		$this->db->join('am_accountgl','am_accountgl.AccountNumber=at_bsweepjournal.BSweepAccountNumber');
		$this->db->join('at_compaccountgl','at_compaccountgl.AccountNumber=am_accountgl.AccountNumber');
		//$this->db->where('at_bsweepjournal.BranchCode', $CompRecpCode);
		$this->db->where('at_compaccountgl.BranchCode', $CompTransCode);
		$this->db->where('at_compaccountgl.GLSaldoYears', date('Y'));
		$this->db->where('at_bsweepjournal.BSweepNumber', $sweepbank_number);
		$query = $this->db->get();
		
		$result = $query->result();
		return json_encode($result);
	}
	
	function approval_status($data,$sw_number){
		$this->db->where('BSweepNumber', $sw_number);
		return $this->db->update($this->table, $data);
	}
	
	function cek_app($sw_number){
		$this->db->select('AppLevel1Status, AppLevel2Status');
		$this->db->from('at_banksweep');
		$this->db->where('BSweepNumber', $sw_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($sw_number){
		$this->db->select('AppLevel1Status');
		$this->db->from('at_banksweep');
		$this->db->where('BSweepNumber', $sw_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($sw_number){
		$this->db->select('AppLevel2Status');
		$this->db->from('at_banksweep');
		$this->db->where('BSweepNumber', $sw_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 status_finished($app_finished, $sw_number){
		$this->db->where('BSweepNumber', $sw_number);
		return $this->db->update('at_banksweep', $app_finished);
	}
	
	function get_company($company){
		$this->db->select('BranchCode');
		$this->db->from('am_branch');
		$this->db->where('BranchStatus', 0);
		$this->db->where('CompanyCode', $company);
		$query = $this->db->get();
		
		if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BranchCode = $row->BranchCode;
            }
            return $BranchCode;
        }
        else {
		    return FALSE;
        }
	}

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