<?php
/**
 * Copyright 2009, 2010 AEGEE-Europe
 *
 * This file is part of AEGEE-Europe Online Membership System.
 *
 * AEGEE-Europe Online Membership System is free software: you can
 * redistribute it and/or modify it under the terms of the GNU General
 * Public License as published by the Free Software Foundation, either
 * version 3 of the License, or (at your option) any later version.
 *
 * AEGEE-Europe Online Membership System is distributed in the hope
 * that it will be useful, but WITHOUT ANY WARRANTY; without even the
 * implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
 * PURPOSE.  See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with AEGEE-Europe Online Membership System.  If not, see
 * <http://www.gnu.org/licenses/>.
 */

/**
 * AttendanceStatistics Database Access Object. This class provides functionality to
 * load Attendance Statistics.
 */

require_once(FILESYSTEMROOTPATH."include/classes/IDao.php");

class AttendanceStatisticsDao implements IDao {
	/** MySQL object */
	private $mysql;	
	
	/**
	 * Default constructor
	 */
	public function __construct(MySQL $mysql) {
		$this->mysql = $mysql;
	}

	/**
	 * Load AttendanceStatistics sentence by id
	 *
	 * @param id
	 * @return 
	 */
	public function load($id) {
		
	}
	
	public function contactAntennaeComing() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT count(DISTINCT vd.`BodyCode`) total
							FROM `voting_delegates` vd
							LEFT JOIN ab.`bodies` b ON vd.`BodyCode`=b.`BodyCode`
							WHERE vd.`status`='envoy' AND b.BodyStatus='CA'
							AND vd.`agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			$row = $this->mysql->fetchArray();
			return $row['total'];
		}else{
			 return false;
		}
	}
	
	public function contactAntennaeRegistered() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT count(DISTINCT vd.`BodyCode`) total
							FROM `voting_delegates` vd
							LEFT JOIN ab.`bodies` b ON vd.`BodyCode`=b.`BodyCode`
							WHERE vd.`status` =  'envoy' AND b.BodyStatus='CA' AND `registered`=1
							AND vd.`agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			$row = $this->mysql->fetchArray();
			return $row['total'];
		}else{
			 return false;
		}
	}
	
	public function antennaeComing() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT count(DISTINCT vd.`BodyCode`) total
							FROM `voting_delegates` vd
							LEFT JOIN ab.`bodies` b ON vd.`BodyCode`=b.`BodyCode`
							WHERE vd.`status` =  'delegate' AND b.BodyStatus='A'
							AND vd.`agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			$row = $this->mysql->fetchArray();
			return $row['total'];
		}else{
			 return false;
		}
	}
	
	public function antennaeRegistered() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT count(DISTINCT vd.`BodyCode`) total
							FROM `voting_delegates` vd
							LEFT JOIN ab.`bodies` b ON vd.`BodyCode`=b.`BodyCode`
							WHERE vd.`status` =  'delegate' AND b.BodyStatus='A' AND `registered`=1
							AND vd.`agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			$row = $this->mysql->fetchArray();
			return $row['total'];
		}else{
			 return false;
		}
	}
	
	public function antennaeStillHere() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT count(DISTINCT vd.`BodyCode`) total
							FROM `voting_delegates` vd
							LEFT JOIN ab.`bodies` b ON vd.`BodyCode`=b.`BodyCode`
							WHERE vd.`status` =  'delegate' AND b.BodyStatus='A' AND `registered`=1 AND `departed`=0
							AND vd.`agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			$row = $this->mysql->fetchArray();
			return $row['total'];
		}else{
			 return false;
		}
	}
	
	public function delegatesComing() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT count(1) total
							FROM `voting_delegates` vd
							WHERE vd.`status` =  'delegate'
							AND vd.`agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			$row = $this->mysql->fetchArray();
			return $row['total'];
		}else{
			 return false;
		}
	}
	
	public function delegatesRegistered() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT count(1) total
							FROM `voting_delegates` vd
							WHERE vd.`status` =  'delegate' AND vd.`registered`=1
							AND vd.`agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			$row = $this->mysql->fetchArray();
			return $row['total'];
		}else{
			 return false;
		}
	}
	
	public function delegatesDeparted() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT count(1) total
							FROM `voting_delegates` vd
							WHERE vd.`status` =  'delegate' AND vd.`departed`=1
							AND vd.`agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			$row = $this->mysql->fetchArray();
			return $row['total'];
		}else{
			 return false;
		}
	}
	
	public function insidePlenaryDelegates() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$result = array();
		$query = "SELECT b.`BodyName`, concat(vd.`firstName`, ' ', vd.`LastName`) participant,
							(SELECT numberOfVotes FROM `voting_numberofvotes` WHERE `agora_id`=".$aid." AND BodyCode=b.BodyCode ) votes
							FROM `attendance_current` ac
							INNER JOIN `voting_delegates` vd ON vd.`participantId`=ac.`participantId`
							INNER JOIN ab.bodies b ON vd.BodyCode=b.BodyCode
							WHERE b.`BodyStatus`='A'
							AND vd.`agora_id`=".$aid." 
							ORDER BY b.`BodyName`";
		if( $this->mysql->query($query) ) {
			while( $row = $this->mysql->fetchArray() ) {
				$result[ $row['BodyName'] ] = $row;
			}
		}
		return $result;
	}
	
	public function insidePlenaryEnvoys() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$result = array();
		$query = "SELECT b.`BodyName`, concat(vd.`firstName`, ' ', vd.`LastName`) participant
							FROM `attendance_current` ac
							INNER JOIN `voting_delegates` vd ON vd.`participantId`=ac.`participantId`
							INNER JOIN ab.bodies b ON vd.BodyCode=b.BodyCode
							WHERE b.`BodyStatus`='CA'
							AND vd.`agora_id`=".$aid." 
							ORDER BY b.`BodyName`";
		if( $this->mysql->query($query) ) {
			while( $row = $this->mysql->fetchArray() ) {
				$result[ $row['BodyName'] ] = $row;
			}
		}
		return $result;
	}
	
	public function insidePlenarySummary() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$result = array();
		$query = "SELECT b.`BodyStatus`, count(1) amount
							FROM ab.bodies b
							WHERE b.`BodyStatus`<>'-' and b.`BodyCode` IN (
								SELECT DISTINCT vd.bodyCode
								FROM `attendance_current` ac
								INNER JOIN `voting_delegates` vd ON vd.`participantId`=ac.`participantId` AND vd.`agora_id`=".$aid."
								)
							GROUP BY b.`BodyStatus`";
		if( $this->mysql->query($query) ) {
			while( $row = $this->mysql->fetchArray() ) {
				if( $row['BodyStatus']=='A' ) $row['BodyStatus'] = 'Antennae';
				elseif( $row['BodyStatus']=='CA' ) $row['BodyStatus'] = 'Contact Antennae';
				elseif( $row['BodyStatus']=='S' ) $row['BodyStatus'] = 'Committees';
				elseif( $row['BodyStatus']=='T' ) $row['BodyStatus'] = 'Working Groups';
				$result[ ] = $row;
			}
		}
		return $result;
	}
	
	public function aegeeWGsTotal() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$result = 0;
		$query = "SELECT `aegeeWGs` FROM body_totals WHERE `agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			while( $row = $this->mysql->fetchArray() ) {
				$result = $row['aegeeWGs'];
			}
		}
		return $result;
	}
	
	public function antennaeTotal() {
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$result = 0;
		$query = "SELECT `antennae` FROM body_totals WHERE `agora_id`=".$aid;
		if( $this->mysql->query($query) ) {
			while( $row = $this->mysql->fetchArray() ) {
				$result = $row['antennae'];
			}
		}
		return $result;
	}
	
	public function getAggregatedAttendanceStatistics(){
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "	SELECT bh.BodyName,
							CEILING( SUM( plenary_seconds )* 100/ ( SELECT SUM( TIMESTAMPDIFF( SECOND, `open`, `close` ) ) FROM statutoryvote.plenaries WHERE `agoraId`=".$aid." AND fuzzy=0) ) percent
					FROM (
						/* att_sums - max attendance per local per plenary */
						SELECT MAX(att_sums.total_seconds) plenary_seconds, att_sums.plenary_id, bodyCode
						FROM (
							/* att_all - all attendance in sums per delegate */
							SELECT SUM(att_all.seconds) as total_seconds, att_all.plenary_id, att_all.uid
							FROM (
					
								/* entered [before] started, exited [before] finished */
								SELECT TIMESTAMPDIFF(SECOND, p.`open`, al.`exit`) as seconds, p.`id` as plenary_id, al.`uid`
								FROM statutoryvote.`agorae` a
								INNER JOIN statutoryvote.`plenaries` p ON p.`agoraId` = a.`id`
								INNER JOIN statutoryvote.`attendance_log` al ON al.`agora_id` = a.`id`
								WHERE p.`agoraId`=".$aid." AND p.`fuzzy`=0
								AND al.`enter`<=p.`open` AND al.`exit`<=p.`close`
								AND TIMESTAMPDIFF(SECOND, p.`open`, al.`exit`) > 0
					
								UNION
					
								/* entered [after] started, exited [before] finished */
								SELECT TIMESTAMPDIFF(SECOND, al.`enter`, al.`exit`) as seconds, p.`id` as plenary_id, al.`uid`
								FROM statutoryvote.`agorae` a
								INNER JOIN statutoryvote.`plenaries` p ON p.`agoraId` = a.`id`
								INNER JOIN statutoryvote.`attendance_log` al ON al.`agora_id` = a.`id`
								WHERE p.`agoraId`=".$aid." AND p.`fuzzy`=0
								AND al.`enter`>p.`open` AND al.`exit`<=p.`close`
								AND TIMESTAMPDIFF(SECOND, al.`enter`, al.`exit`) > 0
					
								UNION
					
								/* entered [after] started, exited [after] finished */
								SELECT TIMESTAMPDIFF(SECOND, al.`enter`, p.`close`) as seconds, p.`id` as plenary_id, al.`uid`
								FROM statutoryvote.`agorae` a
								INNER JOIN statutoryvote.`plenaries` p ON p.`agoraId` = a.`id`
								INNER JOIN statutoryvote.`attendance_log` al ON al.`agora_id` = a.`id`
								WHERE p.`agoraId`=".$aid." AND p.`fuzzy`=0
								AND al.`enter`>p.`open` AND al.`exit`>p.`close`
								AND TIMESTAMPDIFF(SECOND, al.`enter`, p.`close`) > 0
					
								UNION
					
								/* entered [before] started, exited [after] finished */
								SELECT TIMESTAMPDIFF(SECOND, p.`open`, p.`close`) as seconds, p.`id` as plenary_id, al.`uid`
								FROM statutoryvote.`agorae` a
								INNER JOIN statutoryvote.`plenaries` p ON p.`agoraId` = a.`id`
								INNER JOIN statutoryvote.`attendance_log` al ON al.`agora_id` = a.`id`
								WHERE p.`agoraId`=".$aid." AND p.`fuzzy`=0
								AND al.`enter`<=p.`open` AND al.`exit`>p.`close`
								AND TIMESTAMPDIFF(SECOND, p.`open`, p.`close`) > 0
					
							) att_all
							GROUP BY att_all.plenary_id, att_all.uid
						) att_sums
						INNER JOIN statutoryvote.voting_delegates vd ON att_sums.`uid` = vd.`uid`
						WHERE vd.`agora_id` = ".$aid."
						GROUP BY att_sums.`plenary_id`, vd.`bodyCode`
					) attendance
					INNER JOIN ab.`bodies_historic` bh ON attendance.`bodyCode` = bh.`BodyCode`
					WHERE bh.BodyCategory IN ('Locals', 'Contacts')
					GROUP BY attendance.`bodyCode`
					ORDER BY `percent` DESC, bh.BodyName
		";
		$result = [];
		if( $this->mysql->query($query) ) {
			while( $row = $this->mysql->fetchArray() ) {
				$result[] = $row;
			}
		}
		return $result;
	}
	public function getFuzzyPlenaries(){
		$result = NULL;
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		
		$query = "	SELECT CONCAT('Plenary ', number, ' - ', name, ' plenary on ', DATE_FORMAT(`open`, '%d/%m/%Y'), ', started at ', TIME(`open`), ' and finished at ', TIME(`close`)) plenary
					FROM plenaries  WHERE fuzzy=1 AND agoraId=".$aid;
		
		if( $this->mysql->query($query) ) {
			$result = "<ul>";
			while( $row = $this->mysql->fetchArray() ) {
				$result .= "<li>".$row['plenary']."</li>";
			}
			$result .= "</ul>";
			}
		return $result;
	}
	
	/**
	 * Load all AttendanceStatistics
	 *
	 * @return array of sentences
	 */
	public function loadAll() {
	}

	/**
	 * Save or update AttendanceStatistics:
	 * @param AttendanceStatistics object with the data
	 * @return boolean true in case of success, false otherwise
	 */
	public function saveOrUpdate(IModel $AttendanceStatistics) {
		
	}
}
?>