<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/*  
 * Author : Dhidhik / Tanggal : 18-02-2016/ Menambah function check_branch_exist 
 * 
 */
class M_branchinfo extends CI_Model {
    public $table       = 'am_branch';
    
    public $company     = 'am_company';
    public $region      = 'am_region';
    public $city        = 'am_city';
    public $state       = 'am_state';
    public $country     = 'am_country';
            
    function get_branch_info(){
		
		/*------------------------------lemparan dari halaman view-----------------------------*/
		$Name	= ($this->input->post('BranchName')) ? $this->input->post('BranchName') : '';
		$Addres	= ($this->input->post('BranchAddress')) ? $this->input->post('BranchAddress') : '';
		
		$BranchCode		= ($this->input->post('TransNumber')) ? $this->input->post('TransNumber') : '';
		$TransStatus	= ($this->input->post('TransStatus')) ? $this->input->post('TransStatus') : '';
		/*------------------------------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('br.BranchCode, br.CompanyCode, br.RegionCode as br_reg, br.BranchName, br.BranchAddress,
                           br.BranchCity, br.BranchState, br.BranchCountry, br.BranchStatus, br.CustomerStatus,
                           br.TransStatus as StatusBranch, cp.CompanyName, rg.RegionCode, rg.RegionName, 
                           ct.CityCode, ct.CityName, st.StateCode, st.StateName, cy.CountryCode, cy.CountryName, br.TransStatus');        
        $this->db->from($this->table . ' br');
        $this->db->join($this->company . ' cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->region . ' rg', 'rg.RegionCode = br.RegionCode', 'LEFT');
        $this->db->join($this->city . ' ct', 'ct.CityCode = br.BranchCity', 'LEFT');
        $this->db->join($this->state . ' st', 'st.StateCode = br.BranchState', 'LEFT');
        $this->db->join($this->country . ' cy', 'cy.CountryCode = br.BranchCountry', 'LEFT');
		/*
			get data dari  combobox find di view
		*/
        if($BranchCode != ""){
			$this->db->where('br.BranchCode', $BranchCode);
		}
		else{
			/*
				inisialisasi untuk loading pertama page branch info 
			*/
			//----------------kondisi 1 untuk branch-------------------------------------//
			/*
				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
			*/
			if($sess_branch_status == 0){
				$this->db->where('br.CompanyCode', $sess_company);
			}
			else{
				$this->db->where('br.CompanyCode', $sess_company);
				$this->db->where('br.BranchCode', $sess_branch);
			}
			//-----------------------end-------------------------------------------------//
		}
        
		if($Name != ""){$this->db->like('br.BranchName', $Name);}
		if($Addres != ""){$this->db->like('br.BranchAddress', $Addres);}
		
		//status itu mengikuti lempar dari halaman view di by status
		/*
			contoh : 	1.Active		=> status[0]
						2.Finish		=> status[1]
						3.Suspended		=> status[2]
						4.Need App. Mgr => status[3]
		*/
		
        if($TransStatus!=''){ 
			$this->db->where_in('br.TransStatus',$TransStatus); 
		}
		else{
			/*
				trans status 0,1 adalah aktif
			*/
			$this->db->where_in('br.TransStatus',array(0,1)); 
		}
		
        $this->db->order_by('br.BranchStatus', 'Asc');
        $this->db->order_by('br.BranchCode', 'Asc');
		$query  = $this->db->limit($end, $beginning);
        $query 	= $this->db->get();
        
        return $query->result();
    }

	 function get_all_print($CompanyCode,$BranchCode,$sess_branch_status){
        $this->db->select('br.BranchCode, br.CompanyCode, br.RegionCode as br_reg, br.BranchName, br.BranchAddress,
                           br.BranchCity, br.BranchState, br.BranchCountry, br.BranchStatus, br.CustomerStatus,
                           br.TransStatus as StatusBranch, cp.CompanyName, rg.RegionCode, rg.RegionName, 
                           ct.CityCode, ct.CityName, st.StateCode, st.StateName, cy.CountryCode, cy.CountryName, br.TransStatus');        
        $this->db->from($this->table . ' br');
        $this->db->join($this->company . ' cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->region . ' rg', 'rg.RegionCode = br.RegionCode', 'LEFT');
        $this->db->join($this->city . ' ct', 'ct.CityCode = br.BranchCity', 'LEFT');
        $this->db->join($this->state . ' st', 'st.StateCode = br.BranchState', 'LEFT');
        $this->db->join($this->country . ' cy', 'cy.CountryCode = br.BranchCountry', 'LEFT');
		
		$this->db->where('br.BranchCode', $BranchCode);
		$this->db->where('br.CompanyCode', $CompanyCode);
		
        $this->db->order_by('br.BranchCode', 'Asc');
        $query 	= $this->db->get();
        
        return $query->result();
    }
	
    function get_all(){
        $this->db->select('*');
        $this->db->from($this->table . ' br');
        $this->db->join($this->company . ' cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->region . ' rg', 'rg.RegionCode = br.RegionCode', 'LEFT');
        $this->db->join($this->city . ' ct', 'ct.CityCode = br.BranchCity', 'LEFT');
        $this->db->join($this->state . ' st' , 'st.StateCode = br.BranchState', 'LEFT');
        $this->db->join($this->country . ' cy', 'cy.CountryCode = br.BranchCountry', 'LEFT');
        $query 	= $this->db->get();
        
        $result = $query->result();
        return json_encode($result);
    }
    
    function get_detail($branch_code){
        $this->db->select('br.BranchCode, br.BranchName, br.CompanyCode, cp.CompanyName, br.RegionCode as br_reg,
                           br.BranchAddress, br.BranchCity, br.BranchState, br.BranchCountry, br.BranchStatus,
                           br.CustomerStatus, br.TransStatus as StatusBranch, rg.RegionCode, rg.RegionName, 
                           ct.CityCode, ct.CityName, st.StateCode, st.StateName, cy.CountryCode, cy.CountryName');
        $this->db->from($this->table . ' br');
        $this->db->join($this->company .' cp', 'cp.CompanyCode = br.CompanyCode', 'INNER');
        $this->db->join($this->region . ' rg', 'rg.RegionCode = br.RegionCode', 'LEFT');
        $this->db->join($this->city . ' ct', 'ct.CityCode = br.BranchCity', 'LEFT');
        $this->db->join($this->state . ' st', 'st.StateCode = br.BranchState', 'LEFT');
        $this->db->join($this->country . ' cy', 'cy.CountryCode = br.BranchCountry', 'LEFT');
        $this->db->where('br.BranchCode', $branch_code);
        $query = $this->db->get();

        return $query->row();
    }

    function get_all_branch_code(){
        $this->db->select('CompanyCode, BranchCode');
        $this->db->from($this->table);
        $this->db->order_by('CompanyCode', 'Asc');
        $this->db->order_by('BranchCode', 'Asc');
        $query = $this->db->get();
        
        return $query->result();
    }
    
	function get_all_branch_code_company($company_code){
        $this->db->select('CompanyCode, BranchCode');
        $this->db->from($this->table);
		$this->db->where('CompanyCode', $company_code);
        $this->db->order_by('CompanyCode', 'Asc');
        $this->db->order_by('BranchCode', 'Asc');
        $query = $this->db->get();
        
        return $query->result();
    }
	
    function get_branch_by_company_branch($company_code){
        $this->db->select('BranchCode, BranchStatus');
        $this->db->from($this->table);
        $this->db->where('CompanyCode', $company_code);
		$this->db->where('BranchStatus', 1);
        $this->db->order_by('BranchCode', 'Asc');
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function get_branch_by_company_all($company_code){
        $this->db->select('BranchCode, BranchStatus');
        $this->db->from($this->table);
        $this->db->where('CompanyCode', $company_code);
        $this->db->order_by('BranchCode', 'Asc');
        $query = $this->db->get();
        
        return $query->result();
    }
    
    function get_branch_as_ho(){
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('BranchStatus', 0);
        $this->db->order_by('CompanyCode', 'ASC');
        $this->db->order_by('BranchCode', 'ASC');
        $query = $this->db->get();
        
        return $query->result();
    }
    
    function get_code($code){
        $this->db->select_max('BranchCode');
        $this->db->from($this->table);
        $this->db->like('BranchCode', $code);
        $query = $this->db->get();

        return $query->row();
    }
    
    function get_last_branch_code($sess_company, $sess_branch){
        $this->db->select_max('BranchCode');
        $this->db->from($this->table);
        $this->db->where($this->table.'.CompanyCode', $sess_company);
        //$this->db->where($this->table.'.BranchCode', $sess_branch);
        $query	= $this->db->get();

        return $query->result();
    }    
	
    function get_company(){
        $this->db->select('*');
        $this->db->from('am_company');
        $query	= $this->db->get();

        return $query->result();
    }
	
    function get_company_session($sess_company){
        $this->db->select('CompanyCode,CompanyName');
        $this->db->from('am_company');
        $this->db->where('CompanyCode', $sess_company);
        $query = $this->db->get();

        return $query->result();
    }
	
    function get_region(){
        $this->db->select('RegionCode,RegionName');
        $this->db->from('am_region');
        $query = $this->db->get();

        return $query->result();
    }
	
    function get_city(){
        $this->db->select('*');
        $this->db->from('am_city');
        $query = $this->db->get();

        return $query->result();
    }
	
    function get_state(){
        $this->db->select('*');
        $this->db->from('am_state');
        $query = $this->db->get();

        return $query->result();
    }
	
    function get_country(){
        $this->db->select('*');
        $this->db->from('am_country');
        $query = $this->db->get();

        return $query->result();
    }

    function check_exsisting_data($txt_branch_code){
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('BranchCode', $txt_branch_code);
        //$this->db->or_where('BranchName', $txt_branch_name);
        $query = $this->db->get();

        return $query->result();
    }
	
    function insert($data){
        $this->db->insert($this->table, $data);

        if ($this->db->trans_status() === FALSE) {
            return 0;
        }
        else {
            return 1;
        }
    }
	
    function update($data, $branch_code){
        $this->db->update($this->table, $data, array('BranchCode' => $branch_code));

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

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

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

    function get_branch_status_by_code($code) {
        $this->db->select('BranchStatus');
        $this->db->from($this->table);
        $this->db->where('BranchCode', $code); 
        $query = $this->db->get();    
        
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BranchStatus = $row->BranchStatus;
            }    
            return $BranchStatus;
        }
        else {
            return FALSE;
            }  	
	}
	
    function get_address($branch_code){
        $this->db->select('am_branch.BranchCode,am_branch.BranchName,am_branch.BranchAddress,am_branch.BranchCity,am_branch.BranchState,am_branch.BranchCountry,
                           am_city.CityCode,am_city.CityName,am_state.StateCode,am_state.StateName,am_country.CountryCode,am_country.CountryName');
        $this->db->from($this->table);
        $this->db->join('am_city','am_city.CityCode = am_branch.BranchCity', 'LEFT');
        $this->db->join('am_state','am_state.StateCode = am_branch.BranchState', 'LEFT');
        $this->db->join('am_country','am_country.CountryCode = am_branch.BranchCountry', 'LEFT');
        $this->db->where('am_branch.BranchCode',$branch_code);
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $branch_address = $row->BranchAddress.' '.$row->CityName.' '.$row->StateName.' '.$row->CountryName;
            }    

            return $branch_address;
        }
        else {
            return FALSE;
        }  
    }
	
    function get_trans_status_by_code($BranchCode){
        $this->db->select('TransStatus');
        $this->db->from('am_branch');
        $this->db->where('BranchCode', $BranchCode); 
        $query = $this->db->get();
		
        return $query->result();
    }
	
    function all_account_hutang(){
        $this->db->select('AccountNumber');
        $this->db->from('am_accountgl');
        $this->db->like('AccountName', 'hutang'); 
        $query = $this->db->get();
		
        return $query->result();
    }
	
    function all_account_piutang(){
        $this->db->select('AccountNumber');
        $this->db->from('am_accountgl');
        $this->db->like('AccountName', 'piutang'); 
        $query = $this->db->get();
		
        return $query->result();
    }
	
    function get_all_by_no($BranchCode,$hutang,$piutang){
        foreach($hutang as $row_hutang){
            $hutang2[]  = $row_hutang->AccountNumber;
        }

        foreach($piutang as $row_piutang){
            $piutang2[] = $row_piutang->AccountNumber;
        }
		
        $current_year       = date('Y');
        $current_month      = date('m');
        $GlSaldo            = 'GLSaldoBalance'.$current_month;
		
        /* $this->db->select('SUM(at_compaccountgl.GLSaldoBalance1 + at_compaccountgl.GLSaldoBalance2 + at_compaccountgl.GLSaldoBalance3 + at_compaccountgl.GLSaldoBalance4 + 
                                                at_compaccountgl.GLSaldoBalance5 + at_compaccountgl.GLSaldoBalance6 + at_compaccountgl.GLSaldoBalance7 + at_compaccountgl.GLSaldoBalance8 + 
                                                at_compaccountgl.GLSaldoBalance9 + at_compaccountgl.GLSaldoBalance10 + at_compaccountgl.GLSaldoBalance11 + at_compaccountgl.GLSaldoBalance12) as balance'); */
        $this->db->select('SUM('.$GlSaldo.')as balance');
        $this->db->from('at_compaccountgl');
        $this->db->where('at_compaccountgl.GLSaldoYears', $current_year);
        $this->db->where('at_compaccountgl.BranchCode', $BranchCode);
		
        if($hutang2 > 0){
            $this->db->where_in('at_compaccountgl.AccountNumber', $hutang2);
        }
        if($piutang2 > 0){
            $this->db->or_where_in('at_compaccountgl.AccountNumber', $piutang2);
        }

        $query = $this->db->get();
        return $query->result();
    }
	
    function view_detail($branch_code){
        $this->db->select('am_branch.BranchCode,am_branch.CompanyCode,am_branch.RegionCode as br_reg,am_branch.BranchName,am_branch.BranchAddress,
                           am_branch.BranchCity,am_branch.BranchState,am_branch.BranchCountry,am_branch.BranchStatus,am_branch.CustomerStatus,
                           am_branch.TransStatus as StatusBranch,am_company.CompanyName,am_region.RegionCode,am_region.RegionName,am_city.CityCode,
                           am_city.CityName,am_state.StateCode,am_state.StateName,am_country.CountryCode,am_country.CountryName,am_country.CountryName');
        $this->db->from($this->table);
        $this->db->join('am_company','am_company.CompanyCode = am_branch.CompanyCode', 'LEFT');
        $this->db->join('am_region','am_region.RegionCode = am_branch.RegionCode', 'LEFT');
        $this->db->join('am_city','am_city.CityCode = am_branch.BranchCity', 'LEFT');
        $this->db->join('am_state','am_state.StateCode = am_branch.BranchState', 'LEFT');
        $this->db->join('am_country','am_country.CountryCode = am_branch.BranchCountry', 'LEFT');
        $this->db->where('am_branch.BranchCode',$branch_code);
        $query = $this->db->get();

        return $query->row();
    }
	
	function get_branch_by_company_code($company_code){
        $this->db->select('BranchCode, BranchStatus');
        $this->db->from($this->table);
        $this->db->where('CompanyCode', $company_code);
        $this->db->order_by('BranchCode', 'Asc');
        $query = $this->db->get();
        
        return $query->result();
    }
	
	function get_branch_name($code) {
        $this->db->select('BranchName');
        $this->db->from('am_branch');
        $this->db->where('BranchCode', $code); 
        
        $query = $this->db->get();    
        
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $BranchName = $row->BranchName;
            }
            
            return $BranchName;
        }
        else {
            return FALSE;
        }  	
    }
    
    function check_branch_exist($branch_code) {
        $query = $this->db->get_where("am_branch", array('BranchCode' => $branch_code,'TransStatus'=> '1'));		
        if ($query->num_rows() > 0) {
            return TRUE;
        }else {
            return FALSE;
        }	
    }
    
    //========= DHIDHIK, 15 MARET 2016 ========================//
    function insert_array_data_send($dataSend){              
        $this->db->trans_start();
        
        $this->insert($dataSend['data']);
        
        foreach($dataSend['branch_pl_account'] as $key){
            $branch_pl_account = array(
                'CompanyCode'       => $key['CompanyCode'],
                'BranchCode'        => $key['BranchCode'],
                'AccountNumber'     => $key['AccountNumber'],
                'GLSaldoYears'      => $key['GLSaldoYears'],
                'PLStatus'          => $key['PLStatus'],
                'TransStatus'       => $key['TransStatus']
            );
            $this->m_compaccountgl->insert($branch_pl_account);
        }
        $this->m_employee->insert($dataSend['admin_branch']);
        $this->m_changepassword->insert($dataSend['data_user']);
        $this->m_inventories->insert($dataSend['data_item']);
        
        $this->db->trans_complete();
        if($this->db->trans_status() == FALSE){
            $result  = false;
        }else{
            $result  = true;
        }
        return $result;
    }
    
    function update_array_data_send($dataSend){                    
        $this->db->trans_start();
        
        $this->db->update($this->table, $dataSend['data'], array('BranchCode' => $dataSend['branch_code']));
        
        $this->db->trans_complete();
        if($this->db->trans_status() == FALSE){
            $result  = false;
        }else{
            $result  = true;
        }
        return $result;
    }
    
    function suspend_array_data_send($dataSend){                    
        $this->db->trans_start();
                
        $this->db->update($this->table, $dataSend['data'], array('BranchCode' => $dataSend['BranchCode']));
        
        $this->db->trans_complete();
        if($this->db->trans_status() == FALSE){
            $result  = false;
        }else{
            $result  = true;
        }
        return $result;
    }
    //========= END DHIDHIK, 15 MARET 2016 ========================//
}

/* End of file M_branchinfo.php */
/* Location: ./application/models/M_branchinfo.php */