gistfile1.txt

<?php
Class Chartfunctions extends CI_Model {
	function getPickupEventsAllShops($startDate,$endDate,$order,$customer,$region){
		$queryStatement = "select s.id, s.Address1, s.City, s.Country, pickups 
		from store s left join 
		(select count(eventid) as pickups, st.Address1 as address, st.City as city, st.Country as country, st.id as storeid 
	    from shoplog s, box b, store st  
	    where eventid = 3 
	    and duration > (select value from graph_settings where id = 2)
		and duration < (select value from graph_settings where id = 3)
	    and b.Macid = s.macid 
	    and b.StoreId = st.Id
	    and st.CustomerId = ".intval($customer)."
	    and logdate >= '".$startDate."' 
	    and logdate < '".$endDate."' group by st.Address1)t on t.storeid =s.id where s.CustomerId = ".intval($customer);
		if($region!=NULL){
			$queryStatement .= " and s.Region = '".$region."'";
		}
	    $queryStatement .= " order by t.pickups ".$order."";
		
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			$check = 0;
			$records = $query -> result();
			foreach($records as $row){
				if($row->pickups!=0){
					$check = 1;
					break;
				}
			}
			if($check==1){
				return $query -> result();
			}
			else{
				return false;
			}
		} else {
			return false;
		}
	}
	
	function getDurationEventsAllShops($startDate,$endDate,$order,$customer,$region){
		$queryStatement = "select s.id, t.durations, s.City, s.Address1
		from store s left join (select StoreId,sum(duration) as durations 
		from shoplog s, box b, store st
		where eventid = 3
		and duration > (select value from graph_settings where id = 2)
		and duration < (select value from graph_settings where id = 3)
		and b.StoreId = st.Id
		and b.Macid = s.macid
		and st.CustomerId = ".intval($customer)."
		and s.logdate > '".$startDate."'
		and s.logdate <= '".$endDate."'
		group by st.Id)t on t.StoreId = s.id where s.CustomerId = ".intval($customer)."";
		if($region!=NULL){
			$queryStatement .= " and s.Region = '".$region."'";
		}	
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			$check = 0;
			$records = $query -> result();
			foreach($records as $row){
				if($row->durations!=0){
					$check = 1;
					break;
				}
			}
			if($check==1){
				return $query -> result();
			}
			else{
				return false;
			}
		} else {
			return false;
		}
	}
	
	function getStatsForPickUpsPerShop($startDate,$endDate,$storeId){
		$query = $this -> db -> query("select count(eventid) as pickups,logdate, st.Address1 as address, st.City as city, st.Country as country, st.id as storeid 
	    from shoplog s, box b, store st  
	    where eventid = 3
	    and duration > (select value from graph_settings where id = 2)
		and duration < (select value from graph_settings where id = 3)
	    and b.Macid = s.macid 
	    and b.StoreId = st.Id
	    and st.id = ".intval($storeId)."
	    and logdate >= '".$startDate."' 
	    and logdate < '".$endDate."' group by logdate");
	    //echo $this -> db ->last_query();
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	
	function getStatsForDurationsPerShop($startDate,$endDate,$storeId){
		$query = $this -> db -> query("select sum(duration) as durations, st.Address1, st.City, st.Country, logdate
		from shoplog s, store st 
		where eventid = 3
		and duration > (select value from graph_settings where id = 2)
		and duration < (select value from graph_settings where id = 3) 
    	and s.macid in (select Macid from box where StoreId = ".intval($storeId).")
    	and st.Id = ".intval($storeId)." 
		and logdate >= '".$startDate."' 
		and logdate < '".$endDate."' group by logdate order by logdate desc");
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	
	function getStatsForDurationsPerModel($startDate,$endDate,$storeId){
		$query = $this -> db -> query("select Phone,StoreId,sum(duration) as durations 
		from shoplog s, box b, store st, unitmapping um
		where eventid = 3
		and duration > (select value from graph_settings where id = 2)
		and duration < (select value from graph_settings where id = 3)
		and b.StoreId = st.Id
		and b.Macid = s.macid
		and um.BoxId = b.Id
		and um.UnitId = s.unit
		and st.Id = ".intval($storeId)."
		and s.logdate > '".$startDate."'
		and s.logdate <= '".$endDate."'
		group by Phone order by Phone desc");
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	
	function getStatsForPickUpsPerModel($startDate,$endDate,$storeId){
		$query = $this -> db -> query("select Phone,StoreId,count(eventid) as pickups 
		from shoplog s, box b, unitmapping um
		where eventid = 3
		and duration > (select value from graph_settings where id = 2)
		and duration < (select value from graph_settings where id = 3)
		and s.Macid in (select Macid from box where StoreId = ".intval($storeId).")
		and um.BoxId = b.Id
		and um.UnitId = s.unit
		and StoreId = ".intval($storeId)."
		and s.logdate > '".$startDate."'
		and s.logdate <= '".$endDate."'
		group by Phone order by Phone desc");
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	
	function getStores($customer, $city, $country, $region){
		$this -> db -> select('Id, Address1,Address2, City, Country, Latitude as lat, Longitude as lon');
		$this -> db -> from('store');
		if($customer != NULL){
			$this -> db -> where('CustomerId = ' . "'" . $customer . "'");
		}
		if($city != NULL){
			$this -> db -> where('City = ' . "'" . $city . "'");
		}
		if($country != NULL){
			$this -> db -> where('Country = ' . "'" . $country . "'");
		}
		if($region != NULL){
			$this -> db -> where('Region = ' . "'" . $region . "'");
		}
		$query = $this -> db -> get();
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	
	function getRegions($customerID){
		$this -> db -> select("Region");
		$this -> db -> from("store");
		$this -> db -> where("CustomerId = '".$customerID."'");
		$this -> db -> where("Region != 'null'");
		$this -> db -> distinct();
		$query = $this -> db -> get();
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	
	function getAlarmStatisticsForStores($startDate,$endDate,$customerID,$region){
		$queryStatement = "SELECT * FROM 
		(SELECT * FROM 
		(SELECT  t.Id as tid, t.macid as tmacid, t.logdate as tlogdate, t.logtime as tlogtime, t.onoff as tonoff, t.eventid as teventid, t.phonenr as tphonenr
		FROM (SELECT * FROM vendotec.shoplog
		where (eventid = 4 or eventid = 3)
		and logdate >= '".$startDate."'
		and logdate <='".$endDate."'
		order by Id desc)t group by t.macid)k RIGHT JOIN box b on b.MacId = tmacid)l, store s where s.Id = l.StoreId";
		if($customerID!=NULL){
			$queryStatement .= " and CustomerId = ".intval($customerID)."";
		}
		if($region!=NULL){
			$queryStatement .= " and Region = ".intval($region)."";
		}
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	
	function getAlarmStatisticsForMAC($startDate,$endDate,$macid){
		$queryStatement = "select * from shoplog
		where macid = '".$macid."'
		and eventid in (1,2,3,4)
		and logdate >= '".$startDate."'
		and logdate <= '".$endDate."'
		order by Id desc limit 1";
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			return $query -> result_array();
		} else {
			return false;
		}
	}
	//MARKED FOR REMOVAL getAlarmsDetailsForShop
	function getAlarmsDetailsForShop($startDate,$endDate,$macid){
		$queryStatement = "select * from shoplog 
		    where (eventid = 3 or eventid = 4 or eventid = 1)
		    and logdate >= '".$startDate."'
		    and logdate <= '".$endDate."'
		    and macid = '".$macid."'
		    order by Id desc";
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
		
	function getAlarmAlert($startDate,$endDate,$check,$id){
		if($check==NULL){
			$queryStatement = "select *, sl.Id as slid from shoplog sl, store s, box b
		    where (eventid = 1)
			and sl.Id NOT IN (select id from graph_alert_view_check)
		    and logdate >= '".$startDate."'
		    and logdate <= '".$endDate."'
			and	sl.macid = b.Macid
			and b.StoreId = s.Id ";
			if($id!=NULL){
				$queryStatement .="and s.CustomerId = ".$id;
			}
		    $queryStatement .=" order by sl.Id desc";
		}
		else if($check==2){ //if check = 2, then $id will contain macid
			$queryStatement = "select * from shoplog
			where macid = '".$id."'
			and eventid = 1
			and logdate <= '".$startDate."'
			and Id NOT IN (select id from graph_alert_view_check)
			order by Id desc limit 1";
		}
		else if($check==3){
			$queryStatement = "select * from shoplog sl, box b
			where sl.macid = '".$id."'
			and b.Macid = sl.macid
			and eventid in (1)
			and logdate >= '".$startDate."'
			and logdate <= '".$endDate."'
			order by sl.Id desc";
		}
		else{
			$queryStatement = "select * from (select * from shoplog 
		    where (eventid = 1)
			and Id NOT IN (select id from graph_alert_view_check)
		    and logdate >= '".$startDate."'
		    and logdate <= '".$endDate."'
		    order by Id desc)t group by t.macid";
		}
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			return $query -> result_array();
		} else {
			return false;
		}
	}
	
	function getLastEvent($endDate,$macid){
		$queryStatement = "select * from shoplog where eventid IN (3,4) ";
		if($endDate != NULL){
    		$queryStatement .= " and logdate <= '".$endDate."'";
		}
		else{
			$queryStatement .= " and logdate <= '2012-07-18'";
		}
		$queryStatement .=" and macid = '".$macid."' order by Id desc LIMIT 1";
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			return $query -> result_array();
		} else {
			return false;
		}
	}
	
	function getMACS($storeid){
		$this -> db -> select("Macid");
		$this -> db -> from("box");
		$this -> db -> where("StoreId = '".$storeid."'");
		$query = $this -> db -> get();
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	
	function getStoreInfo($storeid){
		$this -> db -> select("*");
		$this -> db -> from("store");
		$this -> db -> where("Id = '".$storeid."'");
		$query = $this -> db -> get();
		if($query -> num_rows()==1){
			return $query -> result_array();
		}
		else{
			return false;
		}
	}
	
	function getHelpStatus($startDate,$endDate,$macid){
		$this -> db -> select("*");
		$this -> db -> from("shoplog");
		$this -> db -> where("eventid = 2");
		$this -> db -> where("macid = '".$macid."'");
		$this -> db -> where("logdate >= '".$startDate."'");
		$this -> db -> where("logdate <= '".$endDate."'");
		$this -> db -> where("Id NOT IN (select Id from graph_alert_view_check)");
		$this -> db -> order_by('Id','desc');
		$this -> db -> limit(1);
		$query = $this -> db -> get();
		if($query -> num_rows()==1){
			return $query -> result_array();
		}
		else{
			return false;
		}
	}
	
	function alarmCheck($alarmId){
		$this -> db -> select("id,check");
		$this -> db -> from("graph_alert_view_check");
		$this -> db -> where("id = ".$alarmId);
		$query = $this -> db -> get();
		if($query -> num_rows()==1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	
	function clearAlert($alarmId){
		$data = array(
			'id' => intval($alarmId),
			'check' => 1
		);
		$this -> db -> insert('graph_alert_view_check',$data);
	}
	
	function getAssociatedUsers($custId){
		$this -> db -> select ("");
		$this -> db -> from("users");
		$this -> db -> where("CustId = ".$custId);
		$query = $this -> db -> get();
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	
	function getStoreDetails($storeId){
		$this -> db -> select ("");
		$this -> db -> from("store");
		$this -> db -> where("Id = ".$storeId);
		$query = $this -> db -> get();
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	
	function getBoxDetails($storeId){
		$this -> db -> select ("");
		$this -> db -> from("box");
		$this -> db -> where("StoreId = ".$storeId);
		$query = $this -> db -> get();
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	function getPhonePositions($macid){
		$this -> db -> select ("*");
		$this -> db -> from("box b, unitmapping um");
		$this -> db -> where("b.Id = um.BoxId");
		$this -> db -> where("b.Macid = '".$macid."'");
		$query = $this -> db -> get();
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	function getShopUsers($storeId){
		$this -> db -> select ("");
		$this -> db -> from("users u, boxusermapping bum");
		$this -> db -> where("bum.StoreId = ".$storeId);
		$this -> db -> where("u.Id = bum.ContactId");
		$query = $this -> db -> get();
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	function getBestPositions($macid, $startDate, $endDate){
		$this -> db -> select("Phone,count(eventid) as pickups, b.Id, logdate, s.unit");
		$this -> db -> from("shoplog s, box b, unitmapping um");
		$this -> db -> where("eventid = 3");
		$this -> db -> where("s.Macid = '".$macid."'");
		$this -> db -> where("b.Macid = s.macid");
		$this -> db -> where("um.BoxId = b.Id");
		$this -> db -> where("um.UnitId = s.unit");
		$this -> db -> where("s.logdate > '".$startDate."'");
		$this -> db -> where("s.logdate <= '".$endDate."'");
		$this->db->group_by('Phone');
		$this->db->order_by('logdate','desc');
		$query = $this -> db -> get();
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	function getActionList($macid,$startDate,$endDate){
		$queryStatement = "select* from (select s.Id, s.logdate, s.userid, s.eventid, s.onoff, s.logtime, st.Address1 from shoplog s, box b, store st
		    where (eventid != 3)
			and s.Id NOT IN (select id from graph_alert_view_check)
		    and logdate >= '".$startDate."'
		    and logdate <= '".$endDate."'
			and s.macid = '".$macid."'
			and b.Macid = s.macid
			and st.Id = b.StoreId
		    order by s.Id desc)t left join users u on u.id = t.userid";
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	function getSystemStatus($macid,$startDate,$endDate){
		$queryStatement = "select * from(select * from shoplog sl
		where sl.eventid in (4,16,3)
		and sl.logdate > '".$startDate."'
		and sl.logdate <= '".$endDate."'
		and macid = '".$macid."'
		order by sl.Id desc)t group by t.eventid";
		$query = $this -> db -> query($queryStatement);
		if ($query -> num_rows() >= 1) {
			return $query -> result();
		} else {
			return false;
		}
	}
	function getAlarmHistory($macid,$startDate){
		$this -> db -> select("");
		$this -> db -> from("shoplog");
		$this -> db -> where("macid = '".$macid."'");
		$this -> db -> where("eventid = 1");
		$this -> db -> where("logdate < '".$startDate."'");
		$this->db->order_by('logdate','desc');
		$this -> db -> limit(10);
		$query = $this -> db -> get();
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	function getPositionWithMostAlarms($startDate, $endDate, $macid){
		$queryStatement = "select count(t.Id) as alarms, t.unit as unit from( select * from shoplog
		where macid = '".$macid."'
		and eventid in (1)
		and logdate >= '".$startDate."'
		and logdate <= '".$endDate."'
		order by Id desc)t group by t.unit";
		$query = $this -> db -> query($queryStatement);
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	function getSystemStatusForStore($date,$storeid){
		$queryStatement = "select * from(select sl.userid, sl.Id, sl.onoff, sl.macid from shoplog sl, box b
		where b.Macid = sl.macid
		and b.StoreId = '".$storeid."'
		and eventid in (4)
		and logdate <= '".$date."'
		order by sl.Id desc)t left join users u on t.userid = u.Id group by macid";
		$query = $this -> db -> query($queryStatement);
		if($query -> num_rows()>=1){
			return $query -> result();
		}
		else{
			return false;
		}
	}
	function clearAllAlert($macid,$type){
		$query = $this -> db -> query("SELECT Id FROM shoplog WHERE macid = '".$macid."' AND eventid = ".$type." AND Id not in (select id from graph_alert_view_check)");
		if($query -> num_rows() >= 1){
			$queryResult = $query -> result();
			foreach ($queryResult as $key) {
				$data = array(
					'id' => intval($key->Id),
					'check' => 1
				);
				$this -> db -> insert('graph_alert_view_check',$data);
			}
		}
		else{
			print_r("error");
		}
	}
}