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

/**
* MODEL FOR APPLY
* @author : Zul Zolkaffly
* @last-update : Zul Zolkaffly (26/08/2016)
*/
class Applymain extends CI_Model {
	
	public function getOpeningBalanceList($companyID, $class = NULL) {		
		$sql = "SELECT
							comp_acct_type.ID acctTypeID,
							_ref_acct_group.parentID,
							comp_chart_of_acct.acctGroupID,
							_ref_acct_group.acctGroupName,
							_ref_acct_group.orderNo,
							comp_chart_of_acct.ID acctID,
							comp_chart_of_acct.acctCode,
							comp_chart_of_acct.acctName
						FROM _ref_acct_group, comp_acct_type, comp_chart_of_acct
						WHERE comp_acct_type.ID = _ref_acct_group.acctTypeID
						AND comp_chart_of_acct.acctGroupID = _ref_acct_group.ID
						".(($class !== NULL) ? " AND acctClass = ".$class." " : NULL)."
						AND is_delete = 0
						AND companyID = ".$companyID."
						ORDER BY orderNo, acctTypeID, acctGroupID, acctCode";
		$rs = $this->db->query($sql);
		return ($rs && ($res = $rs->result())) ? $this->ObjToArray($res) : NULL;
	}
	
	public function getParents($companyID, $years, $class = NULL) {
		$sql = "SELECT ID, acctGroupName, orderNo
						FROM _ref_acct_group
						WHERE ID IN (
							SELECT DISTINCT _ref_acct_group.parentID
							FROM _ref_acct_group, comp_acct_type
							WHERE comp_acct_type.ID = _ref_acct_group.acctTypeID
							".(($class !== NULL) ? " AND acctClass = ".$class." " : NULL)."
						)
						AND (parentID IS NULL OR parentID = 0)
						ORDER BY orderNo";
		$query = $this->db->query($sql);
		$result = ($query && ($res = $query->result())) ? $this->ObjToArray($res) : NULL;
		$return = NULL;
		if($result) {
			$years = ($years) ? $years : (($yrs = $this->ObjToArray($this->getFinancialYears($companyID))) ? $yrs : array(date("Y"), date("Y")-1));
			
			foreach($result as $key=>$value) {
				$return[$value['ID']]['name'] = $value['acctGroupName'];
				$return[$value['ID']]['calc'] = $this->getParCalcMethod($value['ID']);
				$return[$value['ID']]['section'] = $this->getSection($value['ID'],$class);
				
				if($sections = $this->getSection($value['ID'],$class)) {
					foreach($sections as $index=>$item) {
						$return[$value['ID']]['section'][$index] = $item;
						
						if($years) {
							foreach($years as $y=>$year) {
								$return[$value['ID']]['section'][$index]['total'][$year] = ($item['calc']) ? $this->calculateTotal($companyID, $year, $item['calc'], $class) : 0;
							}
						}
					}
				}
			}
		}
		return $return;
	}
	
	public function getSection($id, $class = NULL) {
		$sql = "SELECT a.ID, acctGroupName, orderNo
						FROM _ref_acct_group a, comp_acct_type b
						WHERE b.ID = a.acctTypeID
						".(($class !== NULL) ? " AND b.acctClass = ".$class." " : NULL)."
						AND a.parentID =".$id."
						AND EXISTS ( SELECT 1 FROM _ref_acct_group c WHERE c.parentID = a.ID )
						ORDER BY orderNo";
		$query = $this->db->query($sql);
		$result = ($query && ($res = $query->result())) ? $this->ObjToArray($res) : NULL;
		$return = NULL;
		if($result) {
			foreach($result as $key=>$value) {
				$return[$value['ID']]['name'] = $value['acctGroupName'];
				$return[$value['ID']]['calc'] = $this->getParCalcMethod($value['ID']);
			}
		}
		return $return;
	}
	
	public function getParCalcMethod($id) {
		$sql = "SELECT calcDesc FROM _ref_coa_par_calc WHERE parentID = ".$id;
		$query = $this->db->query($sql);
		return ($query && ($res = $query->result()) && $res[0]) ? $res[0]->calcDesc : NULL;
	}
	
	public function getChildCalcMethod($companyID, $acctID) {
		$sql = "SELECT *
						FROM _ref_coa_child_calc, _ref_chart_of_acct, comp_chart_of_acct
						WHERE _ref_coa_child_calc.acctID = _ref_chart_of_acct.ID
						AND _ref_chart_of_acct.acctCode = comp_chart_of_acct.acctCode
						AND companyID = ".$companyID."
						AND comp_chart_of_acct.ID = ".$acctID;
		$query = $this->db->query($sql);
		return ($query && ($res = $query->result()) && $res[0]) ? $res[0]->calcDesc : NULL;
	}
	
	public function ParseParenthesis($method) {			
		$arr = NULL;
		$regex = '#\((([^()]+|(?R))*)\)#';
		
		if (preg_match_all($regex, $method ,$arr)) {
			return $arr[0];
		} else {
			return NULL;
		}
	}
	
	public function calculateTotal($companyID, $year, $method) {
		if(!$method) return 0;
		$add = function($a,$b) { return ($a+$b); };
		$sub = function($a,$b) { return ($a-$b); };
		$times = function($a,$b) { return ($a*$b); };
		$divide = function($a,$b) { return ($a/(($b)?$b:(($a)?$a:1))); };
		
		if(strpos($method, "(") !== FALSE) {
			$total = NULL;
			if($stack = preg_split('/ *([\/]) */',$method)) {
				foreach($stack as $key=>$element) {
					if(strpos($element, "(") !== FALSE && $arr = $this->ParseParenthesis($element)) {
						foreach($arr as $val) {							
							if(substr_count($val, "(") == 1) {
								$opr = str_replace("(","",str_replace(")","",$val));
								$total[$key] = $this->calculateTotal($companyID, $year, $opr);
							} elseif(substr_count($val, "(") == 0) {
								$total[$key] = $this->calculateTotal($companyID, $year, $val);
							}
						}
					} else {
						$total[$key] = $this->getSumofAccount($companyID, $year, $element);
					}
				}
			}
			
			$return = 0;
			if($total) {
				foreach($total as $key=>$value) {
					$return = ($key) ? ($return/(($value) ? $value : (($return)?$return:1))) : $value;
				}
			}			
			return $return;
		} else {
			if($stack = preg_split('/ *([+\-\/\*]) */',$method,-1,PREG_SPLIT_DELIM_CAPTURE)) {
				$next = NULL;
				$prev = NULL;
				$func = NULL;
				$total = 0;
				
				$check = array_intersect(array("+","-","/","*"),$stack);
				if(!empty($check)) {
					
					foreach($stack as $element) {
						if(in_array($element, array("+","-","*","/"))) {
							switch($element) {
								case "+" : $func = "add"; break;
								case "-" : $func = "sub"; break;
								case "*" : $func = "times"; break;
								case "/" : $func = "divide"; break;
								default: break;
							}
						} else {
							$next = ($prev === NULL) ? NULL : $this->getSumofAccount($companyID, $year, $element);
							$prev = ($next === NULL) ? $this->getSumofAccount($companyID, $year, $element) : $prev;
						}
						
						if($next !== NULL && $prev !== NULL && $func !== NULL) {			
							$total = $$func($prev, $next);
							
							$prev = $total;
							$next = NULL;
							$func = NULL;
						}
					}
				} else {
					$total = $this->getSumofAccount($companyID, $year, $method);
				}
				
				return $total;
			} else {
				return $this->getSumofAccount($companyID, $year, $method);
			}
		}
		
	}
	
	public function getSumofAccount($companyID, $year, $accountID) {
		$type = substr(strtoupper(trim($accountID)),0,1);
		$val = substr(strtoupper(trim($accountID)),1);
		if($type == "I") {
			$sql = "SELECT SUM(debit-credit) TOT
							FROM comp_opening_balance a
							WHERE companyID = ".$companyID."
							AND year = ".$year."
							AND accountID IN (
								SELECT comp_chart_of_acct.ID acctID
								FROM _ref_chart_of_acct, comp_chart_of_acct
								WHERE _ref_chart_of_acct.acctCode = comp_chart_of_acct.acctCode								
								AND companyID = ".$companyID."
								AND _ref_chart_of_acct.ID = ".$val."
							)
							AND createdTS = (
								SELECT MAX(createdTS)
								FROM comp_opening_balance
								WHERE companyID = ".$companyID."
								AND accountID = a.accountID
								AND year = ".$year."
							)";
		} else {
			$sql = "SELECT SUM(debit-credit) TOT
							FROM comp_opening_balance a
							WHERE companyID = ".$companyID."
							AND year = ".$year."
							AND accountID IN (
							SELECT comp_chart_of_acct.ID acctID
							FROM _ref_acct_group, comp_acct_type, comp_chart_of_acct
							WHERE comp_acct_type.ID = _ref_acct_group.acctTypeID
							AND comp_chart_of_acct.acctGroupID = _ref_acct_group.ID
							AND is_delete = 0
							AND companyID = ".$companyID."
							AND comp_chart_of_acct.acctGroupID = ".$val."
							)
							AND createdTS = (
								SELECT MAX(createdTS)
								FROM comp_opening_balance
								WHERE companyID = ".$companyID."
								AND accountID = a.accountID
								AND year = ".$year."
							)";
		}
		$query = $this->db->query($sql);
		return ($query && ($res = $query->result()) && $res[0]) ? (($res[0]->TOT > 0) ? $res[0]->TOT : -1 * $res[0]->TOT) : NULL;
	}
	
	public function getListRatio($companyID, $years = NULL, $class=NULL, $listonly = FALSE) {
		$sql = "SELECT comp_acct_type.ID as ratioID, comp_acct_type.acctTypeName as ratioName, _ref_acct_group.ID as itemID, _ref_acct_group.acctGroupName as itemName
						FROM comp_acct_type, _ref_acct_group
						WHERE _ref_acct_group.acctTypeID = comp_acct_type.ID
						AND comp_acct_type.ID IN (6,7)";
		$query = $this->db->query($sql);
		$result = ($query && ($res = $query->result())) ? $this->ObjToArray($res) : NULL;
		$return = NULL;
		
		if($result) {
			$years = ($years) ? $years : (($yrs = $this->ObjToArray($this->getFinancialYears($companyID))) ? $yrs : array(date("Y"), date("Y")-1));
			if($listonly) {
				$return = $result;
				foreach($result as $key=>$value) {
					$calc = $this->getParCalcMethod($value['itemID']);
					$return[$key] = $value;
					if($years) {
						foreach($years as $y=>$year) {
							$val = $this->calculateTotal($companyID, $year, $calc, $class);
							$val = ($value['ratioName'] != "Income Ratio") ? number_format((float)round($val,2), 2, '.', '') : round($val*100,2);
							$return[$key]['data'][$year] = ($calc) ? $val : NULL;
						}
					}
				}
			} else {
				foreach($result as $key=>$value) {
					$calc = $this->getParCalcMethod($value['itemID']);
					
					$return[$value['ratioID']]['name'] = $value['ratioName'];
					$return[$value['ratioID']]['details'][$value['itemID']]['name'] = $value['itemName'];
					$return[$value['ratioID']]['details'][$value['itemID']]['calc'] = $calc;
					
					if($years) {
						foreach($years as $y=>$year) {
							$val = $this->calculateTotal($companyID, $year, $calc, $class);
							$val = ($value['ratioName'] != "Income Ratio") ? number_format((float)round($val,2), 2, '.', '') : round($val*100,2);
							$return[$value['ratioID']]['details'][$value['itemID']]['data'][$year] = ($calc) ? $val : NULL;
						}
					}
				}
			}
		}
		
		return $return;
	}
	
	public function getCOAParentDetail($id) {
		$sql = "SELECT acctGroupName FROM _ref_acct_group WHERE ID = ".$id;
		$query = $this->db->query($sql);
		$result = ($query && ($res = $query->result()) && $res[0]) ? $this->ObjToArray($res[0]) : NULL;
		
		$return = NULL;
		if($result) {
			$return['name'] = $result['acctGroupName'];
		}
		
		return $return;
	}
	
	public function updateOpeningBalance($data) {		
		$sql = "SELECT COUNT(*) CT
						FROM comp_opening_balance
						WHERE companyID = ".$data['companyID']."
						AND accountID = ".$data['accountID']."
						AND year = ".$data['year'];
		$q = $this->db->query($sql);
		if($q && ($r = $q->result()) && $r[0] && $r[0]->CT) {
			$sql = "UPDATE comp_opening_balance
							SET debit = ".$data['debit'].",
									credit = ".$data['credit']."
							WHERE companyID = ".$data['companyID']."
							AND accountID = ".$data['accountID']."
							AND year = ".$data['year'];
		} else {
			$sql = "INSERT INTO comp_opening_balance (
								companyID, accountID, year, debit, credit
							) VALUES (
								".$data['companyID'].", ".$data['accountID'].", ".$data['year'].", ".$data['debit'].", ".$data['credit']."
							)";
		}
		
		return ($result = $this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function listChartOfAccount($companyID, $class) {
		if ($companyID == 0) return false; 
		
		$sql = "SELECT DISTINCT comp_chart_of_acct.ID, comp_chart_of_acct.acctName, _ref_acct_group.acctGroupName, comp_acct_type.acctTypeName
						FROM comp_chart_of_acct, _ref_acct_group, comp_acct_type
						WHERE _ref_acct_group.ID = comp_chart_of_acct.acctGroupID
						AND comp_acct_type.ID = _ref_acct_group.acctTypeID
						AND comp_acct_type.acctClass = ".$class."
						AND comp_chart_of_acct.companyID = ".$companyID."
						ORDER BY comp_chart_of_acct.acctCode";
						
		$query = $this->db->query($sql);
		
		if($query && $query->num_rows() >= 1) {
			return $query->result();
		} else {
			$sql = "INSERT INTO `comp_chart_of_acct` ( companyID, `acctGroupID`, `acctCode`, `acctName`)
							SELECT DISTINCT  ".$companyID.", `cacc`.`acctGroupID` ,  `cacc`.`acctCode` ,  `cacc`.`acctName` 
							FROM comp_chart_of_acct, _ref_acct_group, comp_acct_type
							WHERE _ref_acct_group.ID = comp_chart_of_acct.acctGroupID
							AND comp_acct_type.ID = _ref_acct_group.acctTypeID
							AND comp_acct_type.acctClass = ".$class."
							AND _ref_acct_group.businessTypeID = 2
							ORDER BY comp_chart_of_acct.acctCode";
							
			$this->db->query($sql);
			
			return $this->listChartOfAccount($companyID, $class);
		}
	}
	
	public function checkOpeningBalance($companyID, $year) {
		if ($companyID == 0) return FALSE; 
		$sql = "SELECT COUNT(*) CT FROM `comp_opening_balance` WHERE `companyID` = ".$companyID;
		$rs = $this->db->query($sql);
		return ($rs && ($result = $rs->result()) && $result[0]->CT) ? TRUE : FALSE;
	}
	
	public function getOpeningBalance($companyID, $account, $year) {
		if ($companyID == 0) return NULL; 
		$sql = "SELECT COUNT(*) CT FROM `comp_opening_balance` WHERE `companyID` = ".$companyID;
		$rs = $this->db->query($sql);
		if($rs && ($result = $rs->result()) && $result[0]->CT) {				
			$sql = "SELECT DISTINCT ID as openingBalanceID, IFNULL(debit,0) as openingBalanceDr,  IFNULL(credit,0) as openingBalanceCr
							FROM comp_opening_balance
							WHERE companyID = ".$companyID."
							AND accountID = ".$account."
							AND year = ".$year."
							AND createdTS = (
								SELECT MAX(createdTS)
								FROM comp_opening_balance
								WHERE companyID = ".$companyID."
								AND accountID = ".$account."
								AND year = ".$year."
							)";
			$query = $this->db->query($sql);
								
			return ($query && $query->num_rows()) ? $query->result() : NULL;
		} else {
			return NULL;
		}
	}
		
	public function getFinancialYears($companyID) {
		$sql = "SELECT COUNT(*) CT FROM `comp_opening_balance` WHERE `companyID` = ".$companyID;
		$rs = $this->db->query($sql);
		if($rs && ($result = $rs->result()) && $result[0]->CT) { 
			$sql = "SELECT MAX(`year`) year FROM `comp_opening_balance` WHERE `companyID` = ".$companyID;
			$rs = $this->db->query($sql);			
			if($rs && ($result = $rs->result()) && $result[0]->year) { 
				$years[0] = $result[0]->year;
				$years[1] = $years[0]-1;
			} else {
				$years = array(date("Y"), (date("Y")-1));
			}			
		} else {
			$years = array(date("Y"), (date("Y")-1));
		}
				
		return $years;
	}
	
	public function getCompanyTitle($companyID) {
		$sql = "SELECT CONCAT(companyName,' (',companyNo,')') companyName FROM company_main WHERE ID = ".$companyID;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $r[0] : NULL;
	}
	
	// REPAIR DATA : DELETE DUPLICATE OPENING BALANCE RECORD, DELETE OLD RECORDS
	public function repairDuplicateOBRecord($companyID) {
		$sql = "SELECT ID FROM  `comp_opening_balance` a
						WHERE  a.`companyID` = ".$companyID."
						AND a.`createdTS` < (
							SELECT MAX(b.`createdTS`)
							FROM  `comp_opening_balance` b
							WHERE  b.`companyID` = a.`companyID`
							AND  b.`year` = a.`year`
							AND b.`accountID` = a.`accountID`
							AND b.`year` = a.`year`
						)";
		$q = $this->db->query($sql);
		if($q && ($r = $q->result())) {
			$result = $this->ObjToArray($r);
			foreach($result as $item) {
				$sql = "DELETE FROM comp_opening_balance WHERE ID = ".$item['ID'];
				$this->db->query($sql);
			}
		}
	}	
	
	// REPAIR DATA : DELETE OPENING BALANCE RECORD WITH ACCOUNTID 0
	public function repairInvalidOBRecord($companyID) {
		$sql = "DELETE FROM comp_opening_balance WHERE companyID = ".$companyID." AND (accountID IS NULL OR accountID = 0)";
		$this->db->query($sql);
	}
	
	// REPAIR DATA : DELETE OPENING BALANCE RECORD WITH BOTH DEBIT AND CREDIT 0
	public function repairAllZeroAmountOBRecord($companyID) {
		$sql = "DELETE FROM comp_opening_balance
						WHERE companyID = ".$companyID."
						AND (debit IS NULL OR debit = 0)
						AND (credit IS NULL OR credit = 0)";
		$this->db->query($sql);
	}
	
	// REPAIR DATA : DELETE FINANCIAL YEAR IF OPENING BALANCE FOR THAT PARTICULAR YEAR DOES NOT EXIST
	public function repairFinYearNoOBRecord($companyID) {
		$sql = "UPDATE comp_financial_year a
						SET is_delete = 1
						WHERE a.companyID = ".$companyID."
						AND a.financialYear NOT IN (
							SELECT DISTINCT year
							FROM comp_opening_balance b
							WHERE b.companyID = a.companyID
						)";
		$this->db->query($sql);
	}
	
	/**
	* @title : OBJTOARRAY
	* @description : CONVERT FROM STDCLASS OBJECT TO ARRAY
	* @author : ZUL ZOLKAFFLY
	*	@create : 15/08/2016 (ZUL ZOLKAFFLY)
	*/
	private function ObjToArray($obj,$single=false) {
		return (!empty($obj)) ? json_decode(json_encode(($single)?$obj[0]:$obj),true) : NULL;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

}
