<?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/>.
*/

/**
 * Agorae Database Access Object. This class provides functionality to
* load and save Agorae title.
*/

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

class DelegatesDao implements IDao {
	/** MySQL object */
	private $mysql;

	/**
	 * Default constructor
	 */
	public function __construct(MySQL $mysql) {
		$this->mysql = $mysql;
	}

	/**
	 * Load by id
	 *
	 * @param id
	 * @return object
	 */
	public function load($uid) {
		$query = "SELECT * FROM `voting_delegates` WHERE `uid`='".$uid."' AND agora_id=".$_SESSION['JC_MODULE']['AgoraId'];
		if( $this->mysql->query($query) ) {
			return new Delegates($this->mysql->fetchArray());
		}else {
			return array();
		}
	}

	/**
	 */
	public function loadAll($agora_id = null) {
        if($agora_id == null){
            $agora_id = $_SESSION['JC_MODULE']['AgoraId'];
        }
		$query = "SELECT * FROM `voting_delegates` WHERE `agora_id`=".$agora_id." ORDER BY bodyCode";
		if( $this->mysql->query($query) ) {
			$ret = array();
			while( $row = $this->mysql->fetchArray() ) {
				$row['edit']= "<a href='edit_delegate.php?uid={$row['uid']}&agora_id={$agora_id}'>Edit</a>";
				$ret[ ] = $row;
			}
			return $ret;
		}else {
			return array();
		}
	}
	
	public function getBodyCodes(){
		$query = "SELECT bodyCode FROM `voting_numberofvotes` WHERE agora_id=".$_SESSION['JC_MODULE']['AgoraId'];
		if( $this->mysql->query($query) ) {
			$ret = array(' '=>'   ');
			while( $row = $this->mysql->fetchArray() ) {
				$ret[$row['bodyCode']] = $row['bodyCode'];
			}
			return $ret;
		}else {
			return array();
		}
	}

	public function getBodyNames(){
		$aid = $_SESSION['JC_MODULE']['AgoraId'];
		$query = "SELECT b.`BodyName` , b.`BodyCode` 
							FROM ab.`bodies` b
							WHERE b.`BodyCode` 
							IN (SELECT DISTINCT  `BodyCode` 
									FROM statutoryvote.`voting_delegates` 
									WHERE  (`status` =  'delegate' OR `status` =  'envoy')
									AND agora_id =".$aid." )
							ORDER BY  `BodyName` ";
		if( $this->mysql->query($query) ) {
			$ret = array(' '=>'   ');
			while( $row = $this->mysql->fetchArray() ) {
				$ret[$row['BodyCode']] = $row['BodyName'];
			}
			return $ret;
		}else {
			return array();
		}
	}
	
	public function getBodyName($bodyCode){
		$query = 'SELECT `BodyName` FROM ab.`bodies` WHERE `BodyCode`="'.$bodyCode.'"';
		if( $this->mysql->query($query) ) {
			return $this->mysql->fetchArray();
		}else {
			return array();
		}
	}
	
	public function getDelegatesByBody($bodyCode){
	
		$query = "SELECT * FROM `voting_delegates` WHERE `bodyCode`='".$bodyCode."' AND agora_id=".$_SESSION['JC_MODULE']['AgoraId'];
		if( $this->mysql->query($query) ) {
			$ret = array();
			while( $row = $this->mysql->fetchArray() ) {
				$ret[$row['uid']] = $row;
			}
			return $ret;
		}else {
			return array();
		}
	}
	
	public function import($filename, $agora_id, $prefix){
        $query1 = 'DELETE FROM `voting_delegates` WHERE `agora_id`='.$agora_id;
        if(!$this->mysql->query($query1)){
            echo $query1;
            return false;
        }
        /*
        //requires special permissions and won't run...
        $query2 = "LOAD DATA INFILE '$filename'
         INTO TABLE `delegates_import`
         FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"'
         LINES TERMINATED BY '\n'";
         */

        //sql add delegates import or create new data.
        //"ID", "Intranet username", "First name", "Family name", "Body code", "Participant type"
        $query = "INSERT INTO `voting_delegates` (`agora_id`, `uid`, `firstName`, `lastName`, `participantId`, `bodyCode`, `status`, `registered`, `departed`) VALUES ";
        $i=0;
        //$data = array();
        if (($handle = fopen($filename, "r")) !== FALSE) {
            while (($row = fgetcsv($handle, 1000, ";")) !== FALSE) {
                //$data[] = $row;
                if($i==0){
                    //header
                    $i = $i+1;
                    continue;
                }

                if ($i != 1){ //no comma for first record
                    $query .= ', ';
                }
                $participantId = $this->mysql->escape($row[0]);
                $uid = $this->mysql->escape($row[1]);
                $firstName = $this->mysql->escape($row[2]);
                $lastName = $this->mysql->escape($row[3]);
                $bodyCode = $this->mysql->escape($row[4]);
                $status = $this->mysql->escape($row[5]);
                if($uid == ''){
                    $uid = mb_strtolower(trim($firstName.'.'.$lastName),'UTF-8');
                }

                $query .= "($agora_id, '$uid', '$firstName', '$lastName', '$participantId', '$bodyCode', '$status', 0, 0)";
                $i = $i+1;
            }
            fclose($handle);
        }
        //print_r($data);

        if(!$this->mysql->query($query)){
            echo $query;
            return false;
        }

        if($prefix){
            //add leading zero:
            $query2 = "UPDATE `voting_delegates` SET `participantId` = CONCAT('$prefix-', LPAD(`participantId`, 4, '0')) WHERE `agora_id`=$agora_id";
            if(!$this->mysql->query($query2)){
                echo $query2;
                return false;
            }
        }

        return $i;
	}
	
	public function activate($agora_id){
		$query = 'UPDATE `voting_delegates` SET `registered`=1 WHERE `agora_id`='.$agora_id;
		return $this->mysql->query($query);
	}

	public function deactivate($agora_id){
		$query = 'UPDATE `voting_delegates` SET `registered`=0 WHERE `agora_id`='.$agora_id;
		return $this->mysql->query($query);
	}
	
    public function createNotificationsAll(){
        $nd = $GLOBALS['ClassFactory']->getNotificationsDao();

        $data = $this->getOpenItems();

        foreach($data as $item){
            $nd->create($item["pid"], $item["type"], $item["p_open"], $item["p_close"]);
        }
    }

    public function createNotifications($uid){
        $data = $this->getOpenItems();

        $aid = $_SESSION['JC_MODULE']['AgoraId'];
        $query ="INSERT INTO notifications (`agora_id`,`uid`,`pid`,`type`,`open`,`close`) VALUES ";
        $first = true;
        foreach($data as $item){
            if ($first){
                $first = false;
            }else{
                $query .= ", ";
            }
            $query .= "($aid, '$uid', {$item["pid"]}, '{$item["type"]}', '{$item["p_open"]}', '{$item["p_close"]}')";
        }

        $this->mysql->query($query);
    }

    private function getOpenItems(){
        $aid = $_SESSION['JC_MODULE']['AgoraId'];

        $data = array();
        $type = 'Proposal';
        //get proposals_management -> loadVote
        $query = 'SELECT p.id AS pid , pm.open AS p_open, pm.close AS p_close
        FROM `proposals_management` AS `pm` , proposals AS `p`
        WHERE p.id = pm.proposal_id AND p.status >= 8 AND pm.session_type = "Plenary" AND pm.close > NOW() AND p.agora_id='.$aid; //8=Plenary

        $this->mysql->query($query);
        while( $row = $this->mysql->fetchArray() ) {
            $row['type'] = $type;
            $data[] = $row;
        }

        //get all rollcalls, polls, elections, rankedVotes
        $type = 'Rollcall';
        $query = "SELECT `id` AS `pid`, `open` AS `p_open`, `close` AS `p_close` FROM `rollcalls` WHERE `close` > NOW() AND `agora_id`=".$aid;
        $this->mysql->query($query);
        while( $row = $this->mysql->fetchArray() ) {
            $row['type'] = $type;
            $data[] = $row;
        }

        $type = 'Poll';
        $query = "SELECT `id` AS `pid`, `open` AS `p_open`, `close` AS `p_close` FROM `polls` WHERE `close` > NOW() AND `agora_id`=".$aid;
        $this->mysql->query($query);
        while( $row = $this->mysql->fetchArray() ) {
            $row['type'] = $type;
            $data[] = $row;
        }


        $type = 'Election';
        $query = "SELECT `id` AS `pid`, `open` AS `p_open`, `close` AS `p_close` FROM `elections` WHERE `close` > NOW() AND `agora_id`=".$aid;
        $this->mysql->query($query);
        while( $row = $this->mysql->fetchArray() ) {
            $row['type'] = $type;
            $data[] = $row;
        }

        $type = 'RankedVote';
        $query = "SELECT `id` AS `pid`, `open` AS `p_open`, `close` AS `p_close` FROM `rankedVote` WHERE `close` > NOW() AND `agora_id`=".$aid;
        $this->mysql->query($query);
        while( $row = $this->mysql->fetchArray() ) {
            $row['type'] = $type;
            $data[] = $row;
        }

        return $data;
    }

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

		$query="";
		$recordID=$delegate->getUid();
		if($recordID==NULL)
		{
			$query.="INSERT INTO `voting_delegates` SET ";
		}
		else
		{
			$query.="UPDATE `voting_delegates` SET ";
		}
        $query .="`agora_id` = '".$this->mysql->escape($delegate->getAgora_id())."', ";
		$query .="`firstName` = '".$this->mysql->escape($delegate->getFirstName())."', ";
		$query .="`lastName` = '".$this->mysql->escape($delegate->getLastName())."', ";
		$query .="`participantId` = '".$this->mysql->escape($delegate->getParticipantId())."', ";
		$query .="`bodyCode` = '".$this->mysql->escape($delegate->getBodyCode())."', ";
		$query .="`status` = '".$this->mysql->escape($delegate->getStatus())."', ";
		$query .="`registered` = '".$this->mysql->escape($delegate->getRegistered())."', ";
		$query .="`departed` = '".$this->mysql->escape($delegate->getDeparted())."'";
		//modified: auto by mysql
		if($recordID!=NULL)
		{
			$query.=" where `uid` = '".$recordID."' AND agora_id=".$_SESSION['JC_MODULE']['AgoraId'];
		}
		echo($query);
		return $this->mysql->query($query);
	}

    public function insert(IModel $delegate) {

        $query ="INSERT INTO `voting_delegates` SET ";
        $query .="`agora_id` = '".$this->mysql->escape($delegate->getAgora_id())."', ";
        $query .="`uid` = '".$this->mysql->escape($delegate->getUid())."', ";
        $query .="`firstName` = '".$this->mysql->escape($delegate->getFirstName())."', ";
        $query .="`lastName` = '".$this->mysql->escape($delegate->getLastName())."', ";
        $query .="`participantId` = '".$this->mysql->escape($delegate->getParticipantId())."', ";
        $query .="`bodyCode` = '".$this->mysql->escape($delegate->getBodyCode())."', ";
        $query .="`status` = '".$this->mysql->escape($delegate->getStatus())."', ";
        $query .="`registered` = '".$this->mysql->escape($delegate->getRegistered())."', ";
        $query .="`departed` = '".$this->mysql->escape($delegate->getDeparted())."'";
        //modified: auto by mysql

        return $this->mysql->query($query);
    }


}
?>