<?php

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

class Chartofaccount extends CI_Model {

	public function chartofaccount_all() {
		$this->db->select('*');
		$this->db->select('concat(substr(acctCode,1,4),"-",substr(acctCode,5,3),"-",substr(acctCode,8,2)," [",acctName,"]") as fad', false);
		$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
		// $query = $this->db->query($sql);
		// $this->db->order_by('acctGroupID');
		// requested by aizat to order by acctcode. akmal
		$this->db->order_by('acctCode');
		$query = $this->db->get('comp_chart_of_acct');
		if($query->num_rows() >= 1) {
			return $query->result();
		}
		else {
			return FALSE;
		}
	}

	public function updateOpenngBalance($data, $openingBalanceID, $year) {
		// $res = $this->checkIfExistOpeningBalance($accountID, $data, $year);
		// if ( $res ){
		$companyID = element('compID', $this->session->userdata('logged_in'));
		
		// $this->db->where('accountID', $accountID);
		// $this->db->where('year', $year);
		// $this->db->where('companyID', $companyID);
		$this->db->where('ID', $openingBalanceID);
		$query = $this->db->update('comp_opening_balance', $data);
		// echo '<br>'.$this->db->last_query();
		// var_dump($this->db->affected_rows());
		if($query == TRUE) {
			return TRUE;
		}
		else {
			return FALSE;
		}
		// }
		// else{
		// return $res;
		// }
	
	}

	public function checkIfExistOpeningBalance($accountID, $data, $year) {
		
		$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('accountID', $accountID);
		$this->db->select('*');
		$this->db->from('comp_opening_balance');
		$query = $this->db->get();
		if($query->num_rows() >= 1) {
			return TRUE;
		}
		else {
			$data["year"] = $year;
			$data["companyID"] = element('compID', $this->session->userdata('logged_in'));
			$data['accountID'] = $accountID;
			$this->insertOpenngBalance($data);
			return FALSE;
		}
	}

	public function insertOpenngBalance($data) {
		$query = $this->db->insert('comp_opening_balance', $data);
		if($query === TRUE) {
			return TRUE;
		}
		else {
			return FALSE;
		}
	}

	public function chartofaccount_byAcctCode() {
		$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('acctCode');
		$query = $this->db->get('comp_chart_of_acct');
		if($query->num_rows() >= 1) {
			return $query->result();
		}
		else {
			return FALSE;
		}
	}
	
	public function isShowDropdown($compID) {
		$query1 = $this->db->select('*')
			->from('comp_chart_of_acct')
			->where(array(
			'companyID' => $compID
		))
			->get();
		
		// $query2 = $this->db
		// ->select('*')
		// ->from('comp_journal_detail')
		// ->where(array('companyID'=>$compID))
		// ->get();
		
		// return ($query1->num_rows() && $query2->num_rows())?false:true;
		// change to || condition - akmal
		$chartRow = $query1->num_rows();
		// $jourRow = $query2->num_rows();
		
		// if chart of acct already created, return false. no need to display industry option - akmal
		/*
		 * if($chartRow >= 1 && $jourRow >=1 ){
		 * return false;
		 * }elseif($chartRow >= 1 && $jourRow < 1 ){
		 * return true;
		 * }else return true; //case when both are empty
		 */
		if($chartRow >= 1)
			return false;
		else
			return true;
	}
	
	public function getInfo($id, $acctClass) {
		$query = $this->db->distinct()
			->select('cacc.ID, concat(substr(cacc.acctCode,1,4),"-",substr(cacc.acctCode,5,3),"-",substr(cacc.acctCode,8,2)) as acctCode,' . ' cacc.acctName,accGrp.acctGroupName,accType.acctTypeName, openingBalanceDr,  openingBalanceCr', false)
			->from('comp_chart_of_acct as cacc')
			->join('_ref_acct_group as accGrp', 'accGrp.ID = cacc.acctGroupID', 'left')
			->join('comp_acct_type as accType', 'accType.ID = accGrp.acctTypeID', 'left')
			->where(array(
			'accType.acctClass' => $acctClass, 'cacc.companyID' => $id
		))
			->
		// ->order_by('accType.acctTypeName')
		order_by('cacc.acctCode', 'asc')
			->get();
		
		if($this->db->affected_rows() >= 1) {
			return $query->result();
		}
		else {
			return false;
		}
	}

	public function getChartofAcct($id=0, $acctClass, $year) {
		if ($id == 0) return false; 
		$query = $this->db->distinct()
			->select('cacc.ID, openB.ID as openingBalanceID, concat(substr(cacc.acctCode,1,4),"-",substr(cacc.acctCode,5,3),"-",substr(cacc.acctCode,8,2)) as acctCode,' . ' cacc.acctName,accGrp.acctGroupName,accType.acctTypeName, openB.debit as openingBalanceDr,  openB.credit as openingBalanceCr', false)
			->from('comp_chart_of_acct as cacc')
			->join('_ref_acct_group as accGrp', 'accGrp.ID = cacc.acctGroupID', 'left')
			->join('comp_acct_type as accType', 'accType.ID = accGrp.acctTypeID', 'left')
			->join('comp_opening_balance as openB', 'cacc.ID = openB.accountID AND openB.companyID = ' . $id . ' AND openB.year = ' . $year, 'left')
			->where(array('accType.acctClass' => $acctClass, 'cacc.companyID' => $id))
			->order_by('cacc.acctCode', 'asc')
			->get();
		if($this->db->affected_rows() >= 1) {
			return $query->result();
		}
		else {
			return false;
		}
	}
	
	public function getdata($id) {
		$query = $this->db->select('i.ID as indusID,i.name as indusName,biz.ID as bizID,biz.name as bizName')
			->from('_ref_industry as i')
			->join('_ref_business_type as biz', 'biz.industryID = i.ID', 'left')
			->order_by('biz.ID', 'ASC')
			->get();
		return $query->result();
	}
	
	public function getTypedata($id, $acctClass) {
		$query = $this->db->distinct()
			->select('cacc.ID,cacc.acctCode,cacc.acctName,accGrp.acctGroupName,accType.acctTypeName')
			->from('_ref_chart_of_acct as cacc')
			->join('_ref_acct_group as accGrp', 'accGrp.ID = cacc.acctGroupID', 'left')
			->join('_ref_acct_type as accType', 'accType.ID = accGrp.acctTypeID', 'left')
			->where(array(
			'accType.acctClass' => $acctClass, 'cacc.businessTypeID' => $id
		))
			->get();
		
		if($this->db->affected_rows() >= 1) {
			return $query->result();
		}
		else {
			return false;
		}
	}

	public function getTypeOfBusinessByIndustry($id) {
		$query = $this->db->select('ID, name')
			->from('_ref_business_type')
			->where('industryID', $id)
			->order_by('name', 'ASC')
			->get();
		return $query->result();
	}
	
	/**
	 * CHECK OPENING BALANCE BY COMPANY ID
	 * 
	 * @author NurSalikeen
	 */
	public function checkOpeningBalance($companyId) {
		$query = $this->db->where('companyID', $companyId)
								->get('comp_opening_balance');
		if ($query->num_rows() > 0) {
			return array('result' => 1, 'data' => $query->result());
		}
		else {
			return array('result' => 0);
		}
	}
	
	/**
	 * VERIFY OPENING BALANCE ID BY COMPANY ID
	 *
	 * @author NurSalikeen
	 */
	public function checkOpeningBalanceSetup($companyId) {
		$query = $this->db->select('IFNULL(ROUND(SUM(debit), 2), 0) AS debit, IFNULL(ROUND(SUM(credit), 2), 0) AS credit', false)
								->where('companyID', $companyId)
								->group_by('year')
								->get('comp_opening_balance');
		if ($query->num_rows() > 0) {
			if ($query->result()[0]->debit == $query->result()[0]->credit) {
				return  TRUE;
			}
			else {
				return FALSE;
			}
		}
		else {
			return TRUE;
		}
	}
	
	/**
	 * INSERT OPENING BALANCE DATA
	 * 
	 * @author NurSalikeen
	 */
	public function createOpeningBalance($data) {
		$openingBalanceId = 0;
		if (array_key_exists('openingBalanceId', $data)) {
			$openingBalanceId = $data['openingBalanceId'];
			unset($data['openingBalanceId']);
		}
		
		if ($openingBalanceId != '') {
			$query = $this->db->where('ID', $openingBalanceId)
									->update('comp_opening_balance', $data);
			if ($query) {
				return array('result' => 1, 'openingBalanceId' => $openingBalanceId);
			}
			else {
				return array('result' => 0);
			}
		}
		else {
			$query = $this->db->insert('comp_opening_balance', $data);
			if ($query) {
				return array('result' => 1, 'openingBalanceId' => $this->db->insert_id());
			}
			else {
				return array('result' => 0);
			}
		}
	}
}