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

class Homedashboard extends general_model {

	function __construct() {
		parent::__construct();
	}
	
	/**
	* @title : getInProgress
	* @param : NULL
	* @description : Count number of all reviews
	* @author : ZUL ZOLKAFFLY
	*	@create : 30/08/2016 (ZUL ZOLKAFFLY)
	*/
	public function getTotalReview($methodID = NULL) {
		if($methodID) {
			return ($rs = $this->get_byFilter('_ref_review',array('COUNT(*) CT'), array('is_delete'=>0, 'methodID'=>$methodID))) ? $rs[0]->CT : 0;
		} else {
			return ($rs = $this->get_byFilter('_ref_review',array('COUNT(*) CT'), array('is_delete'=>0))) ? $rs[0]->CT : 0;
		}
	}
	
	/**
	* @title : getTotalApplications
	* @param : [OPTIONAL] INTEGER $companyID DEFAULT null
	* @description : Count number of all applications
	* @author : ZUL ZOLKAFFLY
	*	@create : 30/08/2016 (ZUL ZOLKAFFLY)
	*/
	public function getTotalApplications($countOnly = TRUE, $companyID = NULL) {
		$sql = "SELECT ".(($countOnly) ? "COUNT(*) CT" : "ID, companyName, companyNo")."
						FROM company_main
						WHERE (is_delete IS NULL OR is_delete = 0)
						".(($companyID) ? "AND ID = ".$companyID : NULL)."
						ORDER BY companyName ASC";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result())) ? (($countOnly) ? (($r[0]) ? $r[0]->CT : 0) : $this->ObjToArray($r)) : NULL;
	}
	
	/**
	* @title : getNonCompliance
	* @param : [OPTIONAL] INTEGER $companyID DEFAULT null
	* @description : Count companies with "TIDAK MEMATUHI" status
	* @author : ZUL ZOLKAFFLY
	*	@create : 30/08/2016 (ZUL ZOLKAFFLY)
	*/
	public function getNonCompliance($companyID = NULL, $list = FALSE) {
		// TIDAK MEMATUHI : IF EXISTS ANY REVIEW WITH STATUS "TIDAK MEMATUHI" WILL BE CONSIDERED "TIDAK MEMATUHI"
		$sql = ($list) ? "SELECT DISTINCT a.ID, a.companyName, a.companyNo" : "SELECT COUNT(DISTINCT b.companyID) CT";
		$sql .= "	FROM company_main a, review b
						WHERE b.companyID = a.ID
						".(($companyID) ? " a.ID = ".$companyID." " : NULL)."
						AND b.status = 'TIDAK MEMATUHI'
						AND (a.is_delete IS NULL OR a.is_delete = 0)
						AND NOT EXISTS (
							SELECT c.companyID
							FROM review c
							WHERE c.companyID = b.companyID
							AND c.status = 'SEDANG DIPROSES'
						)";
						
		$query = $this->db->query($sql);
		if($list) {
			return ($query && ($result = $query->result())) ? $this->ObjToArray($result) : NULL;
		} else {
			return ($query && ($result = $query->result()) && $result[0]) ? $result[0]->CT : 0;
		}
	}
	
	/**
	* @title : getCompliance
	* @param : [OPTIONAL] INTEGER $companyID DEFAULT null
	* @description : Count companies with "MEMATUHI" status
	* @author : ZUL ZOLKAFFLY
	*	@create : 30/08/2016 (ZUL ZOLKAFFLY)
	*/
	public function getCompliance($companyID = NULL, $list = FALSE) {
		$total = $this->getTotalReview();
		// MEMATUHI : MUST ONLY HAVE STATUS MEMATUHI, IF EXISTS ANY REVIEW WITH STATUS "TIDAK MEMATUHI" OR "SEDANG DIPROSES" WILL NOT BE CONSIDERED "MEMATUHI"
		$sql = ($list) ? "SELECT DISTINCT a.ID, a.companyName, a.companyNo, COUNT(*) CT" : "SELECT DISTINCT b.companyID, COUNT(*) CT";
		$sql .= "	FROM company_main a, review b
						WHERE b.companyID = a.ID
						".(($companyID) ? " a.ID = ".$companyID." " : NULL)."
						AND b.status = 'MEMATUHI'
						AND (a.is_delete IS NULL OR a.is_delete = 0)
						AND NOT EXISTS (
							SELECT c.companyID
							FROM review c
							WHERE c.companyID = b.companyID
							AND c.status IN ('SEDANG DIPROSES','BELUM SELESAI', 'TIDAK MEMATUHI')
						)";
		$sql .= ($list) ? " GROUP BY a.ID, a.companyName, a.companyNo" : " GROUP BY companyID";
						
		$query = $this->db->query($sql);
		$result = ($query && ($res = $query->result()) && $res) ? $res : NULL;
		
		$return = NULL;
		$ct = 0;
		$r = 0;
		if($result) {
			foreach($result as $key=>$value) {
				if($value->CT == $total) {
					if($list) {
						$return[$r++] = $value;
					} else {
						$ct++;
					}
				}
			}
		}
		
		return ($list) ? $this->ObjToArray($return) : $ct;
	}
	
	/**
	* @title : getInProgress
	* @param : [OPTIONAL] INTEGER $companyID DEFAULT null
	* @description : Count companies with "SEDANG DIPROSES" status
	* @author : ZUL ZOLKAFFLY
	*	@create : 30/08/2016 (ZUL ZOLKAFFLY)
	*/
	public function getInProgress($companyID = NULL, $list = FALSE) {
		$return = NULL;
		$ct1 = 0;
		// SEDANG DIPROSES : ALL COMPANIES WITH ANY REVIEW WITH STATUS "SEDANG DIPROSES" + ALL COMPANIES HAVE NO REVIEW RECORD YET
		$sql = ($list) ? 
						"SELECT DISTINCT company_main.ID, company_main.companyName, company_main.companyNo
							FROM company_main, review
							WHERE companyID = company_main.ID
							".(($companyID) ? " AND company_main.ID = ".$companyID." " : NULL)."
							AND review.status = 'SEDANG DIPROSES'
							AND (company_main.is_delete IS NULL OR company_main.is_delete = 0)
							UNION
							SELECT DISTINCT company_main.ID, company_main.companyName, company_main.companyNo
							FROM company_main
							WHERE (is_delete IS NULL OR is_delete = 0)
							".(($companyID) ? " AND company_main.ID = ".$companyID." " : NULL)."
							AND ID NOT IN ( SELECT DISTINCT companyID FROM review WHERE (is_delete IS NULL OR is_delete = 0))" :
						"SELECT SUM(CT) CT FROM (
							SELECT COUNT(DISTINCT companyID) CT
							FROM company_main, review
							WHERE companyID = company_main.ID
							".(($companyID) ? " AND company_main.ID = ".$companyID." " : NULL)."
							AND review.status = 'SEDANG DIPROSES'
							AND (company_main.is_delete IS NULL OR company_main.is_delete = 0)
							UNION
							SELECT COUNT(DISTINCT ID) CT
							FROM company_main
							WHERE (is_delete IS NULL OR is_delete = 0)
							".(($companyID) ? " AND company_main.ID = ".$companyID." " : NULL)."
							AND ID NOT IN ( SELECT DISTINCT companyID FROM review WHERE (is_delete IS NULL OR is_delete = 0))
						) a";
						
		$query = $this->db->query($sql);
		if($list) {
			$return = ($query && ($result = $query->result())) ? $result : 0;
		} else {
			$ct1 = ($query && ($result = $query->result()) && $result[0]) ? $result[0]->CT : 0;
		}
		
		
		// MEMATUHI : BUT NOT FINISH ALL REVIEWS YET		
		$ct2 = 0;
		$total = $this->getTotalReview();
		$sql = ($list) ? " SELECT DISTINCT a.ID, a.companyName, a.companyNo, COUNT( * ) CT " : " SELECT DISTINCT companyID, COUNT(*) CT ";
		$sql .= " FROM company_main a, review b
						WHERE b.companyID = a.ID
						".(($companyID) ? " a.ID = ".$companyID." " : NULL)."
						AND b.status = 'MEMATUHI'
						AND (a.is_delete IS NULL OR a.is_delete = 0)
						AND NOT EXISTS (
							SELECT c.companyID
							FROM review c
							WHERE c.companyID = b.companyID
							AND c.status IN ('SEDANG DIPROSES','BELUM SELESAI', 'TIDAK MEMATUHI')
						) ";
		$sql .= ($list) ? " GROUP BY a.ID, a.companyName, a.companyNo" : " GROUP BY companyID";
						
		$query = $this->db->query($sql);
		$r = count($return);
		if($result = (($query && ($res = $query->result()) && $res) ? $res : NULL)) {
			foreach($result as $key=>$value) {
				if($value->CT != $total) {
					if($list) {
						$return[$r++] = $value;
					} else {
						$ct2++;
					}
				}
			}
		}
		return ($list) ? $this->ObjToArray($return) : ($ct1+$ct2);
	}
	
	/**
	* @title : getCompByStateAll
	* @param : [OPTIONAL] BOOLEAN $mapael, [OPTIONAL] INTEGER $companyID DEFAULT null
	* @description : Get list of companies by state (mainly used by MAPAEL)
	* @author : ZUL ZOLKAFFLY
	*	@create : 30/08/2016 (ZUL ZOLKAFFLY)
	*/
	public function getCompByStateAll($mapael = FALSE, $companyID = NULL) {
		$states = $this->ObjToArray($this->general_model->get_all('_ref_state',NULL,NULL,NULL,array("fldname")));
		if($states) {
			$return = NULL;
			if(!$mapael) {
				foreach($states as $state) {
					$sql = "SELECT COUNT(*) CT
									FROM company_address, company_main
									WHERE companyID = company_main.ID
									".(($companyID) ? " AND company_main.ID = ".$companyID." " : NULL)."
									AND stateID = ".$state['fldid']."
									AND type = 'PERM'
									AND company_main.is_delete  = 0
									AND company_address.is_delete  = 0";
					$query = $this->db->query($sql);
					$return[$state['fldname']] = ($query && ($result = $query->result()) && $result[0]) ? $result[0]->CT : 0;
				}
				// COMPANY WITH NO ADDRESS
				$sql = "SELECT COUNT(*) CT FROM company_main
								WHERE is_delete = 0
								AND NOT EXISTS (
									SELECT 1 FROM company_address
									WHERE companyID = company_main.ID
								)";
				$query = $this->db->query($sql);
				$return['TIDAK DIKETAHUI'] = ($query && ($result = $query->result()) && $result[0]) ? $result[0]->CT : 0;
						
			} else {
				$colors = array('#E5E5E5','#2C3E50','#CC9999','#ffd633','#39ac39','#99004d');
				foreach($states as $state) {
					$sql = "SELECT COUNT(*) CT
									FROM company_address, company_main
									WHERE companyID = company_main.ID
									".(($companyID) ? " AND company_main.ID = ".$companyID." " : NULL)."
									AND stateID = ".$state['fldid']."
									AND type = 'PERM'
									AND company_main.is_delete  = 0
									AND company_address.is_delete  = 0";
					$query = $this->db->query($sql);
					$return["MY.".$state['fldshort']]['value'] = ($query && ($result = $query->result()) && $result[0]) ? $result[0]->CT : 0;
					$return["MY.".$state['fldshort']]['tooltip']['content'] = $state['fldname']."<br />".$return["MY.".$state['fldshort']]['value'];
					if($return["MY.".$state['fldshort']]['value'] == 0) $return["MY.".$state['fldshort']]['attrs']['fill'] = $colors[0];
					if($return["MY.".$state['fldshort']]['value'] > 0 && $return["MY.".$state['fldshort']]['value'] <= 5) $return["MY.".$state['fldshort']]['attrs']['fill'] = $colors[1];
					if($return["MY.".$state['fldshort']]['value'] > 5 && $return["MY.".$state['fldshort']]['value'] <= 10) $return["MY.".$state['fldshort']]['attrs']['fill'] = $colors[2];
					if($return["MY.".$state['fldshort']]['value'] > 10 && $return["MY.".$state['fldshort']]['value'] <= 15) $return["MY.".$state['fldshort']]['attrs']['fill'] = $colors[3];
					if($return["MY.".$state['fldshort']]['value'] > 15 && $return["MY.".$state['fldshort']]['value'] <= 20) $return["MY.".$state['fldshort']]['attrs']['fill'] = $colors[4];
					if($return["MY.".$state['fldshort']]['value'] > 20) $return["MY.".$state['fldshort']]['attrs']['fill'] = $colors[5];
					
				}
			}
			return $return;
		} else {
			return NULL;
		}
	}
	
	/**
	* @title : getCompByStateTopFive
	* @param : [OPTIONAL] INTEGER $companyID DEFAULT null
	* @description : Get top 5 list of companies by state
	* @author : ZUL ZOLKAFFLY
	*	@create : 30/08/2016 (ZUL ZOLKAFFLY)
	*/
	public function getCompByStateTopFive($companyID = NULL) {
		$sql = "SELECT fldname stateName, COUNT(*) CT
						FROM _ref_state, company_address, company_main
						WHERE stateID = fldid
						AND companyID = company_main.ID
						".(($companyID) ? " AND company_main.ID = ".$companyID." " : NULL)."
						AND type = 'PERM'
						AND company_main.is_delete  = 0
						AND company_address.is_delete  = 0
						GROUP BY fldname
						ORDER BY CT DESC
						LIMIT 0, 5";
						
		$query = $this->db->query($sql);
		$rs = ($query && ($result = $query->result())) ? $this->ObjToArray($result) : NULL;
		$return = NULL;
		if($rs) {
			foreach($rs as $key=>$value) {
				$return[$value['stateName']] = $value['CT'];
			}
		}
		return $return;
	}
	
	/**
	* @title : getMarkDist
	* @param : NULL
	* @description : Get distribution of marks
	* @author : ZUL ZOLKAFFLY
	*	@create : 01/09/2016 (ZUL ZOLKAFFLY)
	*/
	public function getMarkDist() {
		// PREPARE GRADE LIST
		$sql = "SELECT * FROM _ref_grade ORDER BY grade ASC";
		$q = $this->db->query($sql);
		$result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
		$grades = NULL;
		if($result) {
			foreach($result as $key=>$value) {
				$grades[$value['grade']]['min'] = $value['min'];
				$grades[$value['grade']]['max'] = $value['max'];
				$grades[$value['grade']]['total'] = 0;
			}
		}
		
		// PREPARE COMPANY LIST
		$sql = "SELECT ID, (SELECT SUM( mark ) FROM review b WHERE b.companyID = a.ID ) mark FROM company_main a WHERE (a.is_delete IS NULL OR a.is_delete = 0)";
		$q = $this->db->query($sql);
		$result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
		$companies = NULL;
		if($result) {
			foreach($result as $key=>$value) {
				$companies[$value['ID']] = $value['mark'];
			}
		}
		
		if ($companies && $grades) {
			foreach($companies as $key=>$value) {
				foreach($grades as $g=>$d) {
					if($value >= $d['min'] && $value <= $d['max']) {
						$grades[$g]['total'] += 1;
					}
				}
			}
		}
		
		return $grades;
	}
	
	public function getTopCompPerMethod() {
		$sql = "SELECT
									c.methodID methodID,
									c.methodCode code,
									c.methodDesc method,
									a.ID companyID,
									a.companyName,
									a.companyNo,
									(SELECT SUM( mark )
											FROM review b, _ref_review d
											WHERE b.ref_reviewID = d.ID
											AND b.companyID = a.ID
											AND d.methodID = c.methodID ) mark
								FROM company_main a, _ref_method c
								WHERE (a.is_delete IS NULL OR a.is_delete = 0)
								AND methodScoring != 'SCORETENNULL'
								ORDER BY code, mark DESC";
		$q = $this->db->query($sql);
		
		$data = NULL;
		$result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
		if($result) {		
			foreach($result as $key=>$value) {
				$data[$value['code']]['name'] = $value['method'];
				$data[$value['code']]['total'] = (($t = $this->getTotalReview($value['methodID'])) ? ($t*5) : 0);
				
				if($value['mark'] !== NULL) {
					$c = (isset($data[$value['code']]['comp'])) ? count($data[$value['code']]['comp']) : 0;
					if($c < 10) {
						$data[$value['code']]['comp'][$c]['name'] = $value['companyName'];	
						$data[$value['code']]['comp'][$c]['no'] = $value['companyNo'];	
						$data[$value['code']]['comp'][$c]['mark'] = $value['mark'];
					}
				}
			}
		}
		return $data;
	}
	
	
	
	
	/**
	* @title : ObjToArray
	* @param : STDCLASS $obj, [OPTIONAL] BOOLEAN $single
	* @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;
	}
}


















