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

class Projectmain extends CI_Model {
	
	public function __construct() { parent::__construct(); }
	
	public function getAllProjects($user) {
		$sql = "SELECT ID, title,
							(SELECT COUNT(1)
								FROM user
								WHERE user.projectID = project.ID
								AND (user.validity IS NULL OR user.validity = 0)
							) voters,
							startDT,
							endDT,
							stoppedDT,
							paid,
							launched,
							stopped,
							banner,
							language,
							stopped,
							showResultToVoter
						FROM project
						WHERE (is_delete IS NULL OR is_delete = 0)
						AND user = ".$user."
						ORDER BY title ASC";
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
	}
	
	public function getProject($id) {
		$sql = "SELECT ID, title, startDT, endDT, venue, importfile,
							(SELECT COUNT(1)
								FROM user
								WHERE user.projectID = project.ID
								AND (is_delete IS NULL OR is_delete = 0)
							) voters,
							paid,
							launched,
							banner,
							color,
							language,
							stopped,
							showResultToVoter,
							paidDT,
							stoppedDT
						FROM project
						WHERE (is_delete IS NULL OR is_delete = 0)
						AND id = ".$id;
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
	
	public function getProjectTitle($id) {
		$sql = "SELECT title FROM project WHERE (is_delete IS NULL OR is_delete = 0) AND id = ".$id;
		$q = $this->db->query($sql);		
		return ($q && ($r = $q->result()) && $r[0]) ? $r[0]->title : NULL;
	}
	
	public function getProjectVoters($project) {
		$sql = "SELECT userID, name, noAhli, userPassword
						FROM user
						WHERE (validity IS NULL OR validity = 0)
						AND projectID =  ".$project."
						ORDER BY name ASC";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
	}
	
	public function countProjectVoters($project) {
		$sql = "SELECT COUNT(1) CT
						FROM user
						WHERE (validity IS NULL OR validity = 0)
						AND projectID =  ".$project."
						ORDER BY name ASC";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function checkProjectExists($id) {
		$sql = "SELECT COUNT(1) CT FROM project WHERE ID = ".$id." AND (is_delete IS NULL OR is_delete = 0)";
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function deleteProject($id) {
		$sql = "UPDATE project SET is_delete = 1 WHERE ID = ".$id;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function saveProject($data, $user) {
		$sql = "SELECT COUNT(1) CT FROM project WHERE ID = ".$data['ID'];
		$q = $this->db->query($sql);
		
		if($ct = ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE) {
			$sql = "UPDATE project
								SET
										title = '".(addslashes(trim($data['title'])))."',
										startDT = STR_TO_DATE('".(trim($data['startDT']))."','%d-%m-%Y %h:%i %p'),
										endDT = ".(($data['endDT']) ? "STR_TO_DATE('".(trim($data['endDT']))."','%d-%m-%Y %h:%i %p')" : "NULL").",
										venue = '".(addslashes(trim($data['venue'])))."',
										color = ".(($data['color']) ? "'".$data['color']."'" : "NULL").",
										".((isset($data['language']) && trim($data['language'])) ? " language = '".(addslashes(trim($data['language'])))."', " : NULL)."
										".((isset($data['showResultToVoter'])) ? " showResultToVoter = '".$data['showResultToVoter']."', " : NULL)."
										user = ".$user.",
										updatedTS = NOW(),
										updatedBy = ".$user."
								WHERE ID = ".$data['ID'];
			return ($this->db->query($sql)) ? $data['ID'] : 0;
		} else {
			$sql = "INSERT INTO project (
								title, startDT, endDT, venue, color,
								".((isset($data['language']) && trim($data['language'])) ? " language, " : NULL)."
								".((isset($data['showResultToVoter'])) ? " showResultToVoter, " : NULL)."
								user, createdTS, updatedTS, createdBy, updatedBy
							) VALUES (
								'".(addslashes(trim($data['title'])))."',
								STR_TO_DATE('".(trim($data['startDT']))."','%d-%m-%Y %h:%i %p'),
								".(($data['endDT']) ? "STR_TO_DATE('".(trim($data['endDT']))."','%d-%m-%Y %h:%i %p')" : "NULL").",
								'".(addslashes(trim($data['venue'])))."',
								".(($data['color']) ? "'".$data['color']."'" : "NULL").",
								".((isset($data['language']) && trim($data['language'])) ? " '".(addslashes(trim($data['language'])))."', " : NULL)."
								".((isset($data['showResultToVoter'])) ? " '".$data['showResultToVoter']."', " : NULL)."
								".$user.",
								NOW(),
								NOW(),
								".$user.",
								".$user."
							)";
							
			return ($this->db->query($sql)) ? $this->db->insert_id() : 0;
		}
	}
	
	public function stopProject($id, $user) {
		$sql = "UPDATE project SET stopped = 1, stoppedDT = NOW(), stoppedBy = ".$user." WHERE ID = ".$id;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function setLaunch($id, $value, $user) {
		$sql = "UPDATE project
							SET
									launched = ".$value.",
									stoppedBy = ".(($value) ? $user : "NULL").",
									stoppedDT = ".(($value) ? "NOW()" : "NULL").",
									updatedBy = ".$user.",
									updatedTS = NOW()
							WHERE ID = ".$id;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function saveBanner($id, $file) {
		$sql = "UPDATE project SET banner = '".($file)."' WHERE ID = ".$id;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function resetBanner($id) {
		$sql = "UPDATE project SET banner = NULL WHERE ID = ".$id;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function hasVoterStarted($id) {
		$sql = "SELECT COUNT(1) CT FROM vote WHERE projectID = ".$id;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function isProjectStarted($id) {
		$sql = "SELECT 1 CT
						FROM `project`
						WHERE ID = ".$id."
						AND NOW() >= startDT";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function isProjectEnded($id) {
		$sql = "SELECT 1 CT
						FROM `project`
						WHERE ID = ".$id."
						AND NOW() > endDT";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function isProjectRunning($id) {
		$sql = "SELECT 1 CT
						FROM `project`
						WHERE ID = ".$id."
						AND NOW() BETWEEN startDT AND endDT";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function isProjectLaunched($id) {
		$sql = "SELECT launched FROM `project` WHERE ID = ".$id;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->launch) ? TRUE : FALSE;
	}
	
	public function isProjectStopped($id) {
		$sql = "SELECT stopped FROM `project` WHERE ID = ".$id;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->stopped) ? TRUE : FALSE;
	}
	
	public function isProjectPaid($id) {
		$sql = "SELECT paid FROM `project` WHERE ID = ".$id;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->paid) ? TRUE : FALSE;
	}
	
	public function ObjToArray($item) { return json_decode(json_encode($item),true); }
	
}































