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

class Voting_main extends CI_Model {
	
	public function __construct() { parent::__construct(); }
	
	public function getProject($project) {
		$sql = "SELECT ID, title, startDT, endDT, venue, banner, color, language, showResultToVoter
						FROM project
						WHERE (is_delete IS NULL OR is_delete = 0)
						AND id = ".$project;
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
	
	public function getVoter($project, $voter) {
		$sql = "SELECT userID, paid, name, userPassword, status, validity, start_time, end_time,
							companyID,
							(SELECT companyName FROM company WHERE company.companyID = user.companyID) companyName
						FROM user
						WHERE projectID = ".$project."
						AND UPPER(noAhli) = '".(strtoupper($voter))."'";
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;						
	}
	
	public function getVoterUserID($project, $voter) {
		$sql = "SELECT userID FROM user WHERE projectID = ".$project." AND UPPER(noAhli) = '".(strtoupper($voter))."'";
		$q = $this->db->query($sql);		
		return ($q && ($r = $q->result()) && $r[0] &&  $r[0]->userID) ? $r[0]->userID : NULL;			
	}
	
	public function getVoteSummary($project, $user) {
		$sql = "SELECT positionName, candidateName, candidate_picture, company.companyName
						FROM position, candidate, vote, company
						WHERE position.positionID = candidate.positionID
						AND candidate.candidateID = vote.candidateID
						AND candidate.companyID = company.companyID
						AND vote.projectID = ".$project."
						AND vote.userID = ".$user."
						ORDER BY position.ordering ASC, candidateName ASC";
		$q = $this->db->query($sql);
		$return = NULL;
		if($result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL) {
			foreach($result as $key=>$value) {
				$return[$value['positionName']][$value['candidateName']] = array('picture' => $value['candidate_picture'], 'companyName' => $value['companyName']);

			}
		}
		return $return;
	}
	
	public function getVoteResults($project) {						
		$sql = "SELECT
							position.positionID, position.positionName, position.elected, position.ordering,
							candidate.candidateID, candidate.candidateName, candidate.candidate_picture,
							(
								SELECT COUNT(1)
								FROM vote, user
								WHERE vote.userID = user.userID
								AND vote.positionID = position.positionID
								AND vote.candidateID = candidate.candidateID
								AND vote.projectID = position.projectID
								AND user.status = 1
							) CT
						FROM position, candidate
						WHERE position.positionID = candidate.positionID
						AND position.projectID = ".$project."
						GROUP BY position.positionID, candidate.candidateID
						ORDER BY position.ordering ASC, CT DESC, candidate.candidateName ASC";
		$q = $this->db->query($sql);
		$return = NULL;
		if($result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL) {
			foreach($result as $key=>$value) {
				if(!isset($return[$value['positionID']])) {
					$return[$value['positionID']]['name'] = $value['positionName'];
					$return[$value['positionID']]['limit'] = $value['elected'];
				}
				$c = isset($return[$value['positionID']]['candidates']) ? count($return[$value['positionID']]['candidates']) : 0;
				$return[$value['positionID']]['candidates'][$c]['ID'] = $value['candidateID'];
				$return[$value['positionID']]['candidates'][$c]['name'] = $value['candidateName'];
				$return[$value['positionID']]['candidates'][$c]['picture'] = $value['candidate_picture'];
				$return[$value['positionID']]['candidates'][$c]['total'] = $value['CT'];
			}
		}
		return $return;
	}
	
	public function getPositionByID($project, $position) {
		$sql = "SELECT positionName, elected, ordering FROM position WHERE projectID = ".$project." AND positionID = ".$position;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
	
	public function getPositionByOrder($project, $order) {
		$sql = "SELECT positionID, positionName, elected FROM position WHERE projectID = ".$project." AND ordering = ".$order;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
	
	public function getPositionCandidates($project, $position) {
		$sql = "SELECT candidateID, candidateName, candidate_picture,
							companyID,
							(SELECT companyName FROM company WHERE company.companyID = candidate.companyID) companyName
						FROM candidate
						WHERE projectID = ".$project."
						AND positionID = ".$position;
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;	
	}
		
	public function getCandidateByID($project, $candidate) {
		$sql = "SELECT candidateName, candidate_picture FROM candidate WHERE projectID = ".$project." AND candidateID = ".$candidate;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
	
	public function getNextPositionOrder($projectID, $positionID) {
		$sql = "SELECT MIN(a.ordering) ordering
						FROM position a
						WHERE a.projectID = ".$projectID."
						AND a.positionID != ".$positionID."
						AND a.ordering > (
							SELECT b.ordering
							FROM position b
							WHERE b.projectID = ".$projectID."
							AND b.positionID = ".$positionID."
						)
						LIMIT 0,1";
		$q = $this->db->query($sql);		
		return ($q && ($r = $q->result()) && $r[0] &&  $r[0]->ordering) ? $r[0]->ordering : 0;			
	}
	
	public function getPrevPositionOrder($projectID, $positionID) {
		$sql = "SELECT MAX(a.ordering) ordering
						FROM position a
						WHERE a.projectID = ".$projectID."
						AND a.positionID != ".$positionID."
						AND a.ordering < (
							SELECT b.ordering
							FROM position b
							WHERE b.projectID = ".$projectID."
							AND b.positionID = ".$positionID."
						)
						LIMIT 0,1";
		$q = $this->db->query($sql);		
		return ($q && ($r = $q->result()) && $r[0] &&  $r[0]->ordering) ? $r[0]->ordering : 0;			
	}
	
	public function saveVote($project, $user, $position, $candidate) {
		$sql = "INSERT INTO vote (
							projectID, userID, positionID, candidateID, votedDT
						) VALUES (
							".$project.",
							".$user.",
							".$position.",
							".$candidate.",
							NOW()
						)";
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function clearVoting($project, $user) {
		$sql = "UPDATE user
							SET
								start_time = NULL,
								end_time = NULL,
								status = 0
						WHERE projectID = ".$project."
						AND userID = ".$user;
		if ($this->db->query($sql)) {
			$sql = "DELETE FROM vote WHERE projectID = ".$project." AND userID = ".$user;
			return ($this->db->query($sql)) ? TRUE : FALSE;
		} else {
			return FALSE;
		}
	}	
		
	public function clearVoterPositionVote($project, $user, $position) {
		$sql = "DELETE FROM vote WHERE projectID = ".$project." AND userID = ".$user." AND positionID = ".$position;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function startVoting($project, $user) {
		$sql = "UPDATE user SET start_time = NOW() WHERE projectID = ".$project." AND userID = ".$user;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}	
		
	public function endVoting($project, $user) {
		$sql = "UPDATE user SET end_time = NOW() WHERE projectID = ".$project." AND userID = ".$user;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}	
	
	public function confirmVoting($project, $user) {
		$sql = "UPDATE user SET status = 1 WHERE projectID = ".$project." AND userID = ".$user;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}	
	
	public function showResultToVoter($project) {
		$sql = "SELECT showResultToVoter FROM project WHERE (is_delete IS NULL OR is_delete = 0) AND id = ".$project;
		$q = $this->db->query($sql);		
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->showResultToVoter) ? TRUE : FALSE;
	}
	
	public function hasVoterStarted($project) {
		$sql = "SELECT COUNT(1) CT FROM vote WHERE projectID = ".$project;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function isProjectPaid($project) {
		$sql = "SELECT paid FROM project WHERE ID = ".$project;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->paid) ? TRUE : FALSE;
	}
		
	public function isProjectStarted($project) {
		$sql = "SELECT 1 CT
						FROM project
						WHERE ID = ".$project."
						AND NOW() >= startDT";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function isProjectEnded($project) {
		$sql = "SELECT 1 CT
						FROM project
						WHERE ID = ".$project."
						AND NOW() > endDT";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function isProjectRunning($project) {
		$sql = "SELECT 1 CT
						FROM project
						WHERE ID = ".$project."
						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($project) {
		$sql = "SELECT launched FROM project WHERE ID = ".$project;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->launch) ? TRUE : FALSE;
	}
	
	public function isProjectStopped($project) {
		$sql = "SELECT stopped FROM project WHERE ID = ".$project;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->stopped) ? TRUE : FALSE;
	}
	
	public function isProjectSetupComplete($project) {
		$voter = $this->countProjectVoter($project);
		$position = $this->countProjectPosition($project);
		$candidate = $this->countProjectCandidate($project);
		
		return (!$voter || !$position || !$candidate) ? FALSE : TRUE;
	}
	
	public function countProjectVoter($project) {
		$sql = "SELECT COUNT(1) CT FROM user WHERE projectID = ".$project." AND (validity IS NULL OR validity = 0)";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $r[0]->CT : 0;
	}
	
	public function countProjectPosition($project) {
		$sql = "SELECT COUNT(1) CT FROM position WHERE projectID = ".$project;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $r[0]->CT : 0;
	}
	
	public function countProjectCandidate($project) {
		$sql = "SELECT COUNT(1) CT FROM candidate WHERE projectID = ".$project;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $r[0]->CT : 0;
	}
	
	public function ObjToArray($item) { return json_decode(json_encode($item),true); }
	
}































