<?php
defined('BASEPATH') OR exit('No direct script access allowed');
defined('DIR_SEPARATOR') || define('DIR_SEPARATOR', '/');

class Trackermain extends CI_Model {
	
	public function __construct() { parent::__construct(); }
	
	public function saveTracker($trk) {
		$sql = "SELECT COUNT(*) CT
						FROM tracker_main
						WHERE ip = '".(($trk['ip']) ? $trk['ip'] : 0)."'
						AND TIMESTAMPDIFF(MINUTE,datetime,NOW()) < 30
						AND LOWER(path) = '".(strtolower($trk['path']))."'";
		$sql .= ($trk['param']) ? " AND LOWER(param) = '".(strtolower($trk['param']))."'" : NULL;
			echo $sql;
		
		$q = $this->db->query($sql);
		if($q && ($r = $q->result()) && $r[0] && $r[0]->CT == 0) {
			
				
			$sql = "INSERT INTO tracker_main ( ";		
				$keys = array_keys($trk);		
				foreach($keys as $k=>$key) { $sql .= ($k) ? ", tracker_main.".$key : "tracker_main.".$key; }		
			$sql .= ", datetime) VALUES (";
				$vals = array_values($trk);		
				foreach($vals as $v=>$val) { $sql .= ($v) ? ", ".(($val) ? "'".$val."'" : (($val===0)?"0":"NULL")) : (($val) ? "'".$val."'" : (($value===0)?"0":"NULL")); }
			$sql .= ", NOW())";
			
		
			echo $sql;
			return ($this->db->query($sql)) ? TRUE : FALSE;
		} 
		return TRUE;
	}
	
	/**
	* MAIN TRACKER
	*/
	public function getMainTracker($year = NULL, $month = NULL, $day = NULL) {
		$year = ($year) ? ((strlen($year) < 4) ? (sprintf("%04d", $year)) : $year) : NULL;
		$month = ($month) ? ((strlen($month) < 2) ? (sprintf("%02d", $month)) : $month) : NULL;
		$day = ($day) ? ((strlen($day) < 2) ? (sprintf("%02d", $day)) : $day) : NULL;
		
		$f = ($year) ? (($month) ? (($day) ? "H" : "d") : "m") : "Y";
		$l = ($year) ? (($month) ? (($day) ? 24 : 31) : 12) : 0;
		
		$sql = "SELECT DATE_FORMAT(datetime, '%".$f."') trkItem, COUNT(1) trkCount
						FROM tracker_main
						WHERE 1 = 1
						".(($year) ? " AND DATE_FORMAT(datetime, '%Y') = '".$year."' " : NULL)."
						".(($month) ? " AND DATE_FORMAT(datetime, '%m') = '".$month."' " : NULL)."
						".(($day) ? " AND DATE_FORMAT(datetime, '%d') = '".$day."' " : NULL)."
						GROUP BY DATE_FORMAT(datetime, '%".$f."')";
						
		$q = $this->db->query($sql);
		$return = NULL;
		if($result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL) {
			foreach($result as $key=>$value) {
				$return[$value['trkItem']] = $value['trkCount'];
			}
			
			if($l) {
				if($return) {
					for($i = 1; $i <= $l; $i++) {
						$h = (strlen($i) < (($f=='Y') ? 4 : 2)) ? (sprintf("%0".(($f=='Y') ? 4 : 2)."d", $i)) : $i;
						if(!isset($return[$h])) $return[$h] = 0;
					}
				}
			}
			ksort($return);
		}
		return $return;
	}

	/**
	* LOCATION TRACKER
	*/
	public function getLocTracker($year = NULL, $month = NULL, $day = NULL, $loc = NULL) {
		$year = ($year) ? ((strlen($year) < 4) ? (sprintf("%04d", $year)) : $year) : NULL;
		$month = ($month) ? ((strlen($month) < 2) ? (sprintf("%02d", $month)) : $month) : NULL;
		$day = ($day) ? ((strlen($day) < 2) ? (sprintf("%02d", $day)) : $day) : NULL;
		
		$sql = "SELECT IFNULL(city, 'NA') trkItem, COUNT(1) trkCount
						FROM tracker_main
						WHERE 1 = 1
						".(($year) ? " AND DATE_FORMAT(datetime, '%Y') = '".$year."' " : NULL)."
						".(($month) ? " AND DATE_FORMAT(datetime, '%m') = '".$month."' " : NULL)."
						".(($day) ? " AND DATE_FORMAT(datetime, '%d') = '".$day."' " : NULL)."
						".(($loc) ? " AND UPPER(city) = '".(strtoupper($loc))."' " : NULL)."
						GROUP BY IFNULL(city, 'NA')
						ORDER BY trkCount DESC, trkItem ASC
						LIMIT 0,5";
						
		$q = $this->db->query($sql);
		$return = NULL;
		if($result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL) {
			foreach($result as $key=>$value) {
				$return[$value['trkItem']] = $value['trkCount'];
			}
			asort($return);
		}
		return $return;
	}
	
	/**
	* TIME TRACKER
	*/
	public function getTimeTracker($year = NULL, $month = NULL, $day = NULL) {
		$year = ($year) ? ((strlen($year) < 4) ? (sprintf("%04d", $year)) : $year) : NULL;
		$month = ($month) ? ((strlen($month) < 2) ? (sprintf("%02d", $month)) : $month) : NULL;
		$day = ($day) ? ((strlen($day) < 2) ? (sprintf("%02d", $day)) : $day) : NULL;
		
		$sql = "SELECT DATE_FORMAT(tracker_main.datetime,'%h:%i %p') trkItem, COUNT(1) trkCount
						FROM tracker_main
						WHERE 1 = 1
						".(($year) ? " AND DATE_FORMAT(datetime, '%Y') = '".$year."' " : NULL)."
						".(($month) ? " AND DATE_FORMAT(datetime, '%m') = '".$month."' " : NULL)."
						".(($day) ? " AND DATE_FORMAT(datetime, '%d') = '".$day."' " : NULL)."
						GROUP BY DATE_FORMAT(tracker_main.datetime,'%h:%i %p')
						ORDER BY trkCount DESC, trkItem ASC
						LIMIT 0,5";
						
		$q = $this->db->query($sql);
		$return = NULL;
		if($result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL) {
			foreach($result as $key=>$value) {
				$return[$value['trkItem']] = $value['trkCount'];
			}
			asort($return);
		}
		return $return;
	}

	/**
	* STATE TRACKER
	*/
	public function getStateTracker($year = NULL, $month = NULL, $day = NULL, $state = NULL) {
		$year = ($year) ? ((strlen($year) < 4) ? (sprintf("%04d", $year)) : $year) : NULL;
		$month = ($month) ? ((strlen($month) < 2) ? (sprintf("%02d", $month)) : $month) : NULL;
		$day = ($day) ? ((strlen($day) < 2) ? (sprintf("%02d", $day)) : $day) : NULL;
		
		
		$sql = "SELECT IFNULL(UPPER(state), 'NA') trkItem, COUNT(1) trkCount
						FROM tracker_main
						WHERE 1 = 1
						".(($year) ? " AND DATE_FORMAT(datetime, '%Y') = '".$year."' " : NULL)."
						".(($month) ? " AND DATE_FORMAT(datetime, '%m') = '".$month."' " : NULL)."
						".(($day) ? " AND DATE_FORMAT(datetime, '%d') = '".$day."' " : NULL)."
						".(($state) ? " AND UPPER(state) = '".(strtoupper($state))."' " : NULL)."
						GROUP BY IFNULL(UPPER(state), 'NA')
						ORDER BY trkCount DESC, trkItem ASC";
						
		$q = $this->db->query($sql);
		$return = NULL;
		if($list = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL) {
			$result = NULL;
			foreach($list as $key=>$value) {
				$result[$value['trkItem']] = $value['trkCount'];
			}
		
			$colors = array('#E5E5E5','#2C3E50','#CC9999','#ffd633','#39ac39','#99004d');				
			if($states = $this->getStates(121)) {
				foreach($states as $state) {
					$return["MY.".$state['short']]['value'] = (isset($result[strtoupper($state['name'])])) ? $result[strtoupper($state['name'])] : 0;
					$return["MY.".$state['short']]['tooltip']['content'] = $state['name']."<br />".$return["MY.".$state['short']]['value'];
					$return["MY.".$state['short']]['name'] = $state['name'];
					if($return["MY.".$state['short']]['value'] == 0) $return["MY.".$state['short']]['attrs']['fill'] = $colors[0];
					if($return["MY.".$state['short']]['value'] > 0 && $return["MY.".$state['short']]['value'] <= 5) $return["MY.".$state['short']]['attrs']['fill'] = $colors[1];
					if($return["MY.".$state['short']]['value'] > 5 && $return["MY.".$state['short']]['value'] <= 10) $return["MY.".$state['short']]['attrs']['fill'] = $colors[2];
					if($return["MY.".$state['short']]['value'] > 10 && $return["MY.".$state['short']]['value'] <= 15) $return["MY.".$state['short']]['attrs']['fill'] = $colors[3];
					if($return["MY.".$state['short']]['value'] > 15 && $return["MY.".$state['short']]['value'] <= 20) $return["MY.".$state['short']]['attrs']['fill'] = $colors[4];
					if($return["MY.".$state['short']]['value'] > 20) $return["MY.".$state['short']]['attrs']['fill'] = $colors[5];
					
				}
			}
		}
		return $return;
	}
	
	/**
	* MOST VISITED TRACKER
	*/
	public function getVisitedContentTracker($type = 'ARTICLE', $year = NULL, $month = NULL, $day = NULL) {
		$year = ($year) ? ((strlen($year) < 4) ? (sprintf("%04d", $year)) : $year) : NULL;
		$month = ($month) ? ((strlen($month) < 2) ? (sprintf("%02d", $month)) : $month) : NULL;
		$day = ($day) ? ((strlen($day) < 2) ? (sprintf("%02d", $day)) : $day) : NULL;
		
		if(strtoupper($type) == 'SECTION') {
			$sql = "SELECT IFNULL(path, 'home/index/') trkItem, COUNT(1) trkCount
							FROM tracker_main
							WHERE UPPER(path) NOT IN ('PAGE/VIEW/','ARTICLE/VIEW/')
							".(($year) ? " AND DATE_FORMAT(datetime, '%Y') = '".$year."' " : NULL)."
							".(($month) ? " AND DATE_FORMAT(datetime, '%m') = '".$month."' " : NULL)."
							".(($day) ? " AND DATE_FORMAT(datetime, '%d') = '".$day."' " : NULL)."
							GROUP BY IFNULL(path, 'home/index/')
							ORDER BY trkCount DESC, trkItem ASC
							LIMIT 0,10";
		} else {			
			$sql = "SELECT IFNULL(param, 0) trkItem, COUNT(1) trkCount
							FROM tracker_main
							WHERE UPPER(path) = '".(strtoupper($type))."/VIEW/'
							".(($year) ? " AND DATE_FORMAT(datetime, '%Y') = '".$year."' " : NULL)."
							".(($month) ? " AND DATE_FORMAT(datetime, '%m') = '".$month."' " : NULL)."
							".(($day) ? " AND DATE_FORMAT(datetime, '%d') = '".$day."' " : NULL)."
							GROUP BY IFNULL(param, 0)
							ORDER BY trkCount DESC, trkItem ASC
							LIMIT 0,10";
		}
						
		$q = $this->db->query($sql);
		$return = NULL;
		if($result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL) {
			foreach($result as $key=>$value) {
				$return[$key] = $value;
				if(strtoupper($type) != 'SECTION') {
					$return[$key]['trkTitle'] = ($type == 'ARTICLE') ? $this->getArticleTitle($value['trkItem'], 30) : $this->getPageTitle($value['trkItem'], 30);
				} else {
					$return[$key]['trkTitle'] = $value['trkItem'];
				}
			}
		}
		return $return;
	}
	
	/**
	* SUMMARIES
	*/
	public function getSummaries($type) {
		$sql = "SELECT status, COUNT(1) CT
						FROM ".(strtolower($type))."_main
						GROUP BY status
						ORDER BY status";
		$q = $this->db->query($sql);
		$return = NULL;
		if($list = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL) {
			$result = NULL;
			foreach($list as $key=>$value) {
				$result[strtoupper($value['status'])] = $value['CT'];
			}
			
			$status = array('DRAFT','PUBLISHED','DELETED');
			foreach($status as $sts) {
				$return[$sts] = (isset($result[$sts])) ? $result[$sts] : 0;
			}
		}
		return $return;
	}
	
	
	
	
	/**
	* HELPER
	*/
	public function getPageTitle($id, $length = NULL) {
		$sql = "SELECT title FROM page_main WHERE ID = '".$id."' LIMIT 0,1";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->title) ? (($length) ? substr($r[0]->title,0,$length) : $r[0]->title) : NULL;
	}
	
	public function getArticleTitle($id, $length = NULL) {
		$sql = "SELECT title FROM article_main WHERE ID = '".$id."' LIMIT 0,1";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->title) ? (($length) ? substr($r[0]->title,0,$length) : $r[0]->title) : NULL;
	}
	
	public function getStates($country = 121) {
		$sql = "SELECT name, short, code
						FROM _sys_state
						WHERE 1 = 1";
		$sql .= ($country) ? " AND country = ".$country : NULL;
		$sql .= " ORDER BY name ASC";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r) ? $this->ObjToArray($r) : NULL;
	}
	
	public function getCountryName($short) {
		$sql = "SELECT name FROM _sys_country WHERE UPPER(code) = '".(strtoupper($short))."' LIMIT 0,1";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->name) ? $r[0]->name : NULL;
	}
	
	public function ObjToArray($obj) {
		return ($obj) ? json_decode(json_encode($obj),true) : NULL;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
?>