<?php
	class Log{
		// database connection and table name
		private $conn;
		private $table_name = "log";
	 
		// object properties
		public $id;
		public $date_time;
		public $user_name;
		public $staff_id;
		public $page;
		public $action;
	 
		public function __construct($db){
			$this->conn = $db;
		}

		function create(){
			// session_start();
			date_default_timezone_set('Asia/Rangoon'); 
			$query = "INSERT INTO " . $this->table_name . " SET date_time=:date_time, user_name=:user_name, staff_id=:staff_id, page=:page, action=:action";
			$stmt = $this->conn->prepare($query);
		 
			$this->date_time = date("Y-m-d H:i:s");
			$this->staff_id = $_SESSION['userid'];
			$this->user_name = $_SESSION['user'];
			
			$this->date_time=htmlspecialchars(strip_tags($this->date_time));
			$this->user_name=htmlspecialchars(strip_tags($this->user_name));
			$this->staff_id=htmlspecialchars(strip_tags($this->staff_id));
			$this->page=htmlspecialchars(strip_tags($this->page));
			$this->action=htmlspecialchars(strip_tags($this->action));
		 
			$stmt->bindParam(":date_time", $this->date_time);
			$stmt->bindParam(":user_name", $this->user_name);
			$stmt->bindParam(":staff_id", $this->staff_id);
			$stmt->bindParam(":page", $this->page);
			$stmt->bindParam(":action", $this->action);
			
			if($stmt->execute()){
				return true;
			}	 
			return false;
		}

		function getLastLogin(){
			$query = "SELECT * FROM " . $this->table_name . "  ORDER BY id desc limit 0,1";
			$stmt = $this->conn->prepare($query);
			$stmt->execute();
			return $stmt;
		}

		function getCount(){
			date_default_timezone_set('Asia/Rangoon'); 
			$query = "SELECT city.name,tlogin,l30login,tsales,l30sales FROM city 
LEFT JOIN (SELECT city, COUNT(*) AS tlogin
FROM `log`
LEFT JOIN user ON log.staff_id=user.id
WHERE SUBSTRING(log.date_time,1,10)=:date_time
GROUP BY user.city) AS a ON city.name=a.city
LEFT JOIN (SELECT city, COUNT(*) AS l30login
FROM `log`
LEFT JOIN user ON log.staff_id=user.id
WHERE SUBSTRING(log.date_time,1,10)>=DATE_SUB(:date_time, INTERVAL 30 DAY)
GROUP BY user.city) AS b ON city.name=b.city
LEFT JOIN (SELECT city, COUNT(*) AS tsales
FROM sales
WHERE sales_date=:date_time
GROUP BY city) AS c ON city.name=c.city
LEFT JOIN (SELECT city, COUNT(*) AS l30sales
FROM sales
WHERE sales_date>= DATE_SUB(:date_time, INTERVAL 30 DAY)
GROUP BY city) AS d ON city.name=d.city";
			$stmt = $this->conn->prepare($query);
			$this->date_time = date("Y-m-d");
			$this->date_time=htmlspecialchars(strip_tags($this->date_time));
			$stmt->bindParam(":date_time", $this->date_time);
			$stmt->execute();
			return $stmt;
		}

	}
?>