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

/**
 * CIA Database Access Object. This class provides functionality to
 * load and save CIA sentences.
 */

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

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

	
	/*todo 
	 * load sentences by section [title or order] or by article of a section*/
	
	
	/**
	 * Default constructor
	 */
	public function __construct(MySQL $mysql) {
		$this->mysql = $mysql;
	}

	/**
	 * Load CIA sentence by id
	 *
	 * @param id
	 * @return 
	 */
	public function load($id) {
		$query = "SELECT * FROM CIA WHERE `id`='".$id."'";
		if( $this->mysql->query($query) ) {
			return $this->mysql->fetchArray();
		}else {
			return '';
		}
	}
	

	/**
	 * Load all CIA
	 *
	 * @return array of sentences
	 */
	public function loadAll() {
		$query = "SELECT * FROM CIA ORDER BY `order`,`order2`, `article`, `paragraph`, `sentence`";
		if( $this->mysql->query($query) ) {
			$ret = array();
			while( $row = $this->mysql->fetchArray() ) {
				$ret[ $row['id'] ] = new CIA($row);
			}
			return $ret;
		}else {
			return array();
		}
	}
	
	/**
	 * load the titles 
	 * **/
	public function loadTitles(){
		$agora_id = $_SESSION['JC_MODULE']['AgoraId'] - 1; //see previous CIA
		$query = "SELECT DISTINCT(`order`), `title` FROM CIA WHERE agora_id_start <= $agora_id AND agora_id_end >= $agora_id ORDER BY `order`";
		$ret=array();
		if( $this->mysql->query($query) ) {

			while( $row = $this->mysql->fetchArray() ) {
				$ret[ $row['order'] ] = $row['title'];
			}
			
		}
		return $ret;
	}
	
	
	public function getCategories(){
		$query = "SELECT `category` FROM CIA GROUP BY `category`";
		$ret=array();
		if( $this->mysql->query($query) ) {
			while( $row = $this->mysql->fetchArray() ) {
				$ret[ $row['category'] ] = $row['category'];
			}
				
		}
		return $ret;
		
	}
	/**
	* load the titles2
	* **/
	public function loadTitles2ByOrder($order){
		
		$query = "SELECT DISTINCT(`order2`), `title2` FROM CIA WHERE `order`='$order'";
		$ret=array();
		if( $this->mysql->query($query) ) {
			while( $row = $this->mysql->fetchArray() ) {
				$ret[ $row['order2'] ] = $row['title2'];
			}
		}
		return $ret;
	}
	
	/*OBSEOLETE: WE NEED AGORA ID, SEE BELOW*/
	/**
	 * load the sentences of by the order of a title 
	 * **/
	public function loadByTitleOrder($order){
		$query = "SELECT * FROM CIA WHERE `order` ='".$order."' ORDER BY `order2`,`article`,`paragraph`,`sentence`"; //`abs_order`"; -> will not work for older cia. useful for creating proposal.
		if( $this->mysql->query($query) ) {
			$ret = array();
			while( $row = $this->mysql->fetchArray() ) {
				$ret[ $row['id'] ] = new CIA($row);
			}
			return $ret;
		}else {
			return array();
		}
	}
	
	public function loadByOrderAgora($order,$agora_id){
		$query = "SELECT * FROM CIA WHERE `order` ='".$order."' AND agora_id_start <= '".$agora_id."' AND agora_id_end >= '".$agora_id."' ORDER BY `order2`,`article`,`paragraph`,`sentence`"; //`abs_order`";
		if( $this->mysql->query($query) ) {
			$ret = array();
			while( $row = $this->mysql->fetchArray() ) {
				$ret[ $row['id'] ] = $row;
			}
			return $ret;
		}else {
			return array();
		}
	}
	
	public function update($agora_id){
		
		/*
		
		moved from update 4.
		
		copy current cia to cia2update and replace references to `cia` below
		use temp_ps instead ps, eliminate from where "proposal_id=8 AND `order`=6 ", from update "...=11"
		
		//conflicts??
		INSERT INTO cia2update SELECT * FROM CIA where agora_id_end < $aid
		
		INSERT INTO `cia2update` (`category`, `title`, `order`, `last_change`, `title2`, `order2`, `article`, `article_title`, `paragraph`, `sentence`, `sentence_text`, `agora_id_start`, `agora_id_end`, `history_id`)
		SELECT `category`, `title`, `order`, `last_change`, `title2`, `order2`, `article`, `article_title`, `paragraph`, `sentence`, `sentence_text`,
		1, 1,`sentence_id` FROM `proposals_sentences` WHERE proposal_id=8 AND `order`=6 AND `change_type` != 'Delete'
		
		
		UPDATE `cia2update` SET `agora_id_end`=0 WHERE `id` IN (SELECT sentence_id FROM `proposals_sentences` WHERE (change_type='Edit' OR change_type='Delete') AND  proposal_id=11)
		
		//RENUMBER cia2update
		{determine abs order, keep a counter}
		//check orders for inter-part article numbering continuity -> or exception for order = 1
		*
		//get current
		//remove from them edit, delete
		//increase agora end
		//insert new
		
		//last change
		*/
		
		
		/*
		$q1 = "SELECT `id` FROM proposals WHERE `status`='Agora Accepted' AND agora_id=".$agora_id;
		if( $this->mysql->query($q1) ) {
			$pids = array();
			while( $row = $this->mysql->fetchArray() ) {
				$pids[] = $row;
			}
		}
		
		//conflict management
		$q2 = "SELECT DISTINCT `proposal_id`, `order` FROM proposals_sentences WHERE proposal_id IN (SELECT `id` FROM proposals WHERE `status`='Agora Accepted' AND agora_id=".$agora_id.") ";
		if( $this->mysql->query($q2) ) {
			$po = array();
			while( $row = $this->mysql->fetchArray() ) {
				$po[] = $row;
			}
		}
		
		$q4 = "SELECT * FROM CIA WHERE `order` ='".$order."' AND agora_id_start <= '".$agora_id."' AND agora_id_end >= '".$agora_id."' ORDER BY `abs_order`";
		if( $this->mysql->query($q4) ) {
			$sentences = array();
			while( $row = $this->mysql->fetchArray() ) {
				$sentences[ $row['id'] ] = $row;
			}
		}
		
		$q5 = "SELECT * FROM proposals_sentences WHERE `proposal_id` IN (SELECT `id` FROM proposals WHERE `status`='Agora Accepted' AND agora_id=".$agora_id.") ";
		if( $this->mysql->query($q5) ) {
			$proposal_sentences = array();
			while( $row = $this->mysql->fetchArray() ) {
				$proposal_sentences[] = $row;
			}
		}
		
		$q6 = "SELECT * FROM `amendments` WHERE `proposal_id`='".$proposal_id."' AND `order`=".$order." AND `status`='Accept' ORDER BY `order2`, `article`, `paragraph`, `sentence`, `id`";
		//comment: by design, if multiple amendments exist, the latest will be dispalyed, wich should have the accumulated changes
		if( $this->mysql->query($q6) ) {
			$amendments = array();
			while( $row = $this->mysql->fetchArray() ) {
				$amendments[] = $row;
			}
		}
		*/
		
			
		
		//A. apply conflict solutions
		//add solved = 1 ?
	
		//edit-edit: delete second, set correct text in first
		$q_c_ee1 = "DELETE FROM CIA2update WHERE id IN (SELECT ps2 FROM `conflicts` WHERE conflict_type = 'edit-edit' AND agora_id=$agora_id)";
		$q_c_ee2 = "UPDATE CIA2update, conflicts SET CIA2update.sentence_text = conflicts.solution WHERE CIA2update.id = conflicts.ps1 AND conflicts.conflict_type = 'edit-edit' AND conflicts.agora_id=$agora_id";
		
		$q_c_ed1 = "DELETE FROM CIA2update WHERE id IN (SELECT ps1 FROM `conflicts` WHERE conflict_type = 'edit-delete' AND solution='' AND agora_id=$agora_id)";
		$q_c_ed21 = "DELETE FROM CIA2update WHERE id IN (SELECT ps2 FROM `conflicts` WHERE conflict_type = 'edit-delete' AND solution!='' AND agora_id=$agora_id)";
		$q_c_ed22 = "UPDATE CIA2update,conflicts SET CIA2update.sentence_text = conflicts.solution WHERE CIA2update.id = conflicts.ps1 AND conflicts.conflict_type = 'edit-delete' AND conflicts.solution!='' AND conflicts.agora_id=$agora_id";
		
		$q_c_de1 = "DELETE FROM CIA2update WHERE id IN (SELECT ps2 FROM `conflicts` WHERE conflict_type = 'delete-edit' AND solution='' AND agora_id=$agora_id)";
		$q_c_de21 = "DELETE FROM CIA2update WHERE id IN (SELECT ps1 FROM `conflicts` WHERE conflict_type = 'delete-edit' AND solution!='' AND agora_id=$agora_id)";
		$q_c_de22 = "UPDATE CIA2update,conflicts SET CIA2update.sentence_text = conflicts.solution WHERE CIA2update.id = conflicts.ps2 AND conflicts.conflict_type = 'delete-edit' AND conflicts.solution!='' AND conflicts.agora_id=$agora_id";
		//what if it's new para, not sentence?
		//if 2 sentencse above, must sentence = sentence + 2
		$q_c_nunu1 = "UPDATE CIA2update SET sentence=sentence-1 WHERE id IN (SELECT ps1 FROM `conflicts` WHERE conflict_type = 'new_^-new_^' AND solution='1' AND agora_id=$agora_id)";
		$q_c_nunu2 = "UPDATE CIA2update SET sentence=sentence-1 WHERE id IN (SELECT ps2 FROM `conflicts` WHERE conflict_type = 'new_^-new_^' AND solution='2' AND agora_id=$agora_id)";
		
		$q_c_ndnd1 = "UPDATE CIA2update SET sentence=sentence+1 WHERE id IN (SELECT ps2 FROM `conflicts` WHERE conflict_type = 'new_v-new_v' AND solution='1' AND agora_id=$agora_id)";
		$q_c_ndnd2 = "UPDATE CIA2update SET sentence=sentence+1 WHERE id IN (SELECT ps1 FROM `conflicts` WHERE conflict_type = 'new_v-new_v' AND solution='2' AND agora_id=$agora_id)";
		//what if next sentences should be renumbered?
		
		if(!$this->mysql->query($q_c_ee1)){
			return '$q_c_ee1';
		}
		
		if(!$this->mysql->query($q_c_ee2)){
			return '$q_c_ee2';
		}
		
		if(!$this->mysql->query($q_c_ed1)){
			return '$q_c_ed1';
		}
		
		if(!$this->mysql->query($q_c_ed21)){
			return '$q_c_ed21';
		}
	
		if(!$this->mysql->query($q_c_ed22)){
			return '$q_c_ed22';
		}
		
		if(!$this->mysql->query($q_c_de1)){
			return '$q_c_de1';
		}
		
		if(!$this->mysql->query($q_c_de21)){
			return '$q_c_de21';
		}
		
		if(!$this->mysql->query($q_c_de22)){
			return '$q_c_de22';
		}
		
		if(!$this->mysql->query($q_c_nunu1)){
			return '$q_c_nunu1';
		}
		
		if(!$this->mysql->query($q_c_nunu2)){
			return '$q_c_nunu2';
		}
		
		if(!$this->mysql->query($q_c_ndnd1)){
			return '$q_c_ndnd1';
		}
		
		if(!$this->mysql->query($q_c_ndnd2)){
			return '$q_c_ndnd2';
		}
		
		//B. set corrrect agora_id_start, end
		$query4 = "UPDATE CIA2update SET `agora_id_start`=".$agora_id." , agora_id_end=".$agora_id;
		if(!$this->mysql->query($query4)){
			return '$query4';
		}
		
		
		$query51 = "SELECT MAX(agora_id_end) AS previous_agora FROM CIA";
		if(!$this->mysql->query($query51)){
			return '$query51';
		}
		
		$res = $this->mysql->fetchArray();
		$previous_agora = $res['previous_agora'];
		
		$query5 = "UPDATE CIA SET `agora_id_end` =".$agora_id." 
		WHERE `agora_id_end`=".$previous_agora." AND 
		 `id` NOT IN (SELECT sentence_id FROM CIA2update WHERE change_type='Edit' OR change_type='Delete')";
		if(!$this->mysql->query($query5)){
			return '$query5';
		}
		
		//C. insert new sentences
		$query6 = "INSERT INTO CIA 
		(`abs_order`, `category`, `title`, `order`, `last_change`, `title2`, `order2`, `article`, `article_title`, `paragraph`, `sentence`, `sentence_text`, `agora_id_start`, `agora_id_end`, `history_id`)
		SELECT 
		 `abs_order`, `category`, `title`, `order`, `last_change`, `title2`, `order2`, `article`, `article_title`, `paragraph`, `sentence`, `sentence_text`, `agora_id_start`, `agora_id_end`, `history_id` 
		FROM CIA2update WHERE change_type!='Delete'";
		if(!$this->mysql->query($query6)){
			return '$query6';
		}
		
		//D. last change
		$query71 = "SELECT agora FROM agorae WHERE id=".$agora_id;
		if(!$this->mysql->query($query71)){
			return '$query71';
		}
		$res = $this->mysql->fetchArray();
		$this_agora = $res['agora'];
		 //eeg. Rhein-Neckar, April 2013
		 
		$query72 = "SELECT DISTINCT `order` FROM proposals_sentences WHERE proposal_id IN (SELECT `id` FROM proposals WHERE `status`='Agora Accepted' AND agora_id=".$agora_id.")";
		if(!$this->mysql->query($query72)){
			return '$query72';
		}
		$orders = $this->mysql-fetchArray();
		foreach($orders as $order){
			$query7 = "UPDATE CIA set `last_change`='".$this_agora."' WHERE `agora_id_end`=".$agora_id." AND `order`= $order";
			if(!$this->mysql->query($query7)){
				return '$query7';
			}
		}
		
		//E. RENUMBER
		//php: fix it sentence by sentence... for user errors, eg empty paragraphs, "order2 article"-errors etc
		
		//fix articles numbers for order 1,6,17
		$orders = array(1,6,17);
		foreach($orders as $order){
			$query80 = "SELECT a . * , @rn := @rn +1 AS rn
			FROM ( SELECT DISTINCT order2, article FROM `CIA` 
			WHERE `order` = $order AND agora_id_end = $agora_id ORDER BY order2, article, paragraph, sentence)
			a, (SELECT @rn :=0)r";
			if(!$this->mysql->query($query80)){
				return '$query80';
			}
			$result = array();
			$counter = 0;
			while($row = $this->mysql->fetchArray()){
				$result[$counter] = $row;
				$counter = $counter+1;
			}
			
			for($i=count($result)-1; $i>=0; $i=$i-1){
				$res = $result[$i];
				if ($res['article']!=$res['rn']){
					$q81 = "UPDATE `CIA` SET `article`=".$res['rn']." WHERE `article`=".$res['article']." AND `order`=$order AND `order2`=".$res['order2']." AND agora_id_end=$agora_id";
					if(!$this->mysql->query($q81)){
						return '$q81';
					}
				}
			}	
				
				
		}
		
		
		$query81 = "SELECT DISTINCT `order` FROM proposals_sentences WHERE proposal_id IN (SELECT `id` FROM proposals WHERE `status`='Agora Accepted' AND agora_id=".$agora_id.") ";
		if( $this->mysql->query($query81) ) {
			$orders = array();
			while( $row = $this->mysql->fetchArray() ) {
				$orders[] = $row['order'];
			}
		} 
		else{
			return '$query81';
		}
		
		foreach($orders as $o){
			$query9 = "SET @rownum := 0;
						UPDATE CIA SET `abs_order` = (@rownum:=@rownum+1) WHERE `agora_id_end`=".$agora_id." AND `order`=".$o." ORDER BY `order`,`order2`,`article`,`paragraph`,`sentence`;";		
			if(!$this->mysql->query($query9)){
				return '$query9';
			}
			
		}
		
		//F. set CIA as updated
		$query99 = "UPDATE `agorae` SET `updated` = 1 WHERE `id` = ".$agora_id;
		if(!$this->mysql->query($query99)){
			return '$query99';
		}
		
		return 0;
		
		/*
		 * 
		UPDATE CIA SET abs_order = 
		SELECT @rownum := @rownum +1 AS rn, a . * 
		FROM (
		SELECT sentence_text
		FROM `CIA` 
		WHERE `agora_id_end`= $agora_id
		ORDER BY `order`,`order2`,`article`,`paragraph`,`sentence`
		)a, (SELECT @rownum :=0)r 
		  
		 
		 */
		
	}
	
	public function check_updated($agora_id){
		$query = "SELECT `updated` FROM `agorae` WHERE `id` = ".$agora_id;
		if($this->mysql->query($query)){
			$res = $this->mysql->fetchArray();
			return $res['updated'];
		}
	}
	
	
	/*
	public function update_po($proposal_id,$order){
		//ORIGINAL SENTENCES SHOULD HAVE UPDATED AGORA_ID_END
		//AFTER THIS FUNCTION CALL, UPDATE LAST_CHANGE
		$sentences = $this->loadByTitleOrder($order);
		
		$proposals_sentencesService = $GLOBALS['ClassFactory']->getProposals_sentencesService();
		$amendmentsService = $GLOBALS['ClassFactory']->getAmendmentsService();
		$proposals_sentences = $proposals_sentencesService->loadByProposalId_Order($proposal_id, $order);
		$amendments = $amendmentsService->loadByProposalId_Order($proposal_id, $order, true); //true: accepted only		
		
		
		$abs_order = 1;
		
		foreach ($sentences as $sentence) {
		
			//check if edited text exists
			$later = array();
			$modified = NULL;
			$amendment = NULL;
			foreach($proposals_sentences as $prop_s){
				if ($prop_s['sentence_id'] == $sentence['id']){
					//an edited version of this sentence exists
		
					//simple edited text
					if($prop_s['change_type']=='Edit'){
						$modified = $prop_s;
		
					}
					elseif($prop_s['change_type']=='Delete'){
						$modified = $prop_s;
					}
					elseif($prop_s['change_type']=='New_^'){
						//display the inserted sentence
						$new1 = $prop_s;
						foreach($amendments as $am){
							if($prop_s['id'] == $am['proposals_sentence_id']){
								$new1 = $am;
							}
						}
						//display new^
						
						$new1['id'] = '';
						$new1['history_id'] = 0;
						$new1['agora_id_start'] = $agora_id;
						$new1['agora_id_end'] = $agora_id;
						$new1['abs_order'] = $abs_order;
						$abs_order++;
						disp($new1);
						//$c = new Cia($new1);
						//$this->saveOrUpdate($c);
		
					}
					elseif($prop_s['change_type']=='New_v'){
						//display later
						$later []=$prop_s;
					}
				}
			}
		
			if ($modified!=NULL){
				$edit1 = $modified;
				foreach($amendments as $am){
					if($modified['id'] == $am['proposals_sentence_id']){
						$edit1 = $am;
					}
				}
				//edited: proposal or amendment on
				if ($edit1['change_type']=='Edit'){
					//do nothing for 'Delete'
					$edit1['id'] = '';
					$edit1['history_id'] = $sentence['id'];
					$edit1['agora_id_start'] = $agora_id;;
					$edit1['agora_id_end'] = $agora_id;
					$edit1['abs_order'] = $abs_order;
					$abs_order++;
					disp($edit1);
					//$c = new Cia($edit1);
					//$this->saveOrUpdate($c);
				}

			}
			else{
				//no proposal: original or amendment
				
				$amendment = NULL;
				foreach($amendments as $am){
					if($sentence['id'] == $am['sentence_id'] && $am['proposals_sentence_id'] == -1){
						$amendment = $am;
					}
				}
				if($amendment!=NULL){
					$edit1 = $amendment;
					$edit1['id'] = '';
					$edit1['history_id'] = $sentence['id'];
					$edit1['agora_id_start'] = $agora_id;;
					$edit1['agora_id_end'] = $agora_id;
					$edit1['abs_order'] = $abs_order;
					$abs_order++;
					disp($edit1);
					//$c = new Cia($edit1);
					//$this->saveOrUpdate($c);
				}else{
					//original, check abs_order
					if($sentence['abs_order'] != $abs_order){
						$sentence['abs_order'] = $abs_order;
						$abs_order++;
						$sentence['change_type']=' ';
						disp($sentence);
						//$c = new Cia($sentence);
						//$this->saveOrUpdate($c);
						//if original, must increase agora_id_end, edit last_change -> is done out of this function
						
					}
				}
				
			}
		
			foreach($later as $later_s){
				//display new_v
				$new1 = $later_s;
				
				foreach($amendments as $am){
					if($later_s['id'] == $am['proposals_sentence_id']){
						$new1 = $am;
					}
				}
				//display newv
				
				$new1['id'] = '';
				$new1['history_id'] = 0;
				$new1['agora_id_start'] = $agora_id;
				$new1['agora_id_end'] = $agora_id;
				$new1['abs_order'] = $abs_order;
				$abs_order++;
				disp($new1);
				//$c = new Cia($new1);
				//$this->saveOrUpdate($c);
			}
		
		}
		
	}
	*/
	
	
	/**
	 * Save or update CIA:
	 * only after Agora approval!
	 * @param CIA object with the data
	 * @return boolean true in case of success, false otherwise
	 */
	public function saveOrUpdate(IModel $CIA) {
		$query="";
		$recordID=$CIA->getId();
		if($recordID==NULL)
		{
			$query.="INSERT INTO `CIA` SET ";
		}
		else
		{
			$query.="UPDATE `CIA` SET ";
		}
		$query .="`abs_order` = '".$this->mysql->escape($CIA->getAbs_order())."', ";	
		$query .="`category` = '".$this->mysql->escape($CIA->getCategory())."', ";
		$query .="`title` = '".$this->mysql->escape($CIA->getTitle())."', ";
		$query .="`order` = '".$this->mysql->escape($CIA->getOrder())."', ";
		$query .="`last_change` = '".$this->mysql->escape($CIA->getLast_change())."', ";
		$query .="`title2` = '".$this->mysql->escape($CIA->getTitle2())."', ";
		$query .="`order2` = '".$this->mysql->escape($CIA->getOrder2())."', ";
		$query .="`article` = '".$this->mysql->escape($CIA->getArticle())."', ";
		$query .="`article_title` = '".$this->mysql->escape($CIA->getArticle_title())."', ";
		$query .="`paragraph` = '".$this->mysql->escape($CIA->getParagraph())."', ";
		$query .="`sentence` = '".$this->mysql->escape($CIA->getSentence())."', ";
		$query .="`sentence_text` = '".$this->mysql->escape($CIA->getSentence_text())."', ";
		$query .="`agora_id_start` = '".$this->mysql->escape($CIA->getAgora_id_start())."', ";
		$query .="`agora_id_end` = '".$this->mysql->escape($CIA->getAgora_id_end())."', ";
		$query .="`history_id` = '".$this->mysql->escape($CIA->getHistory_id())."'";
		if($recordID!=NULL)
		{
			$query.=" where `id` = ".$recordID;
		}
		//echo $query;
		return $this->mysql->query($query);
	}
	
	private function disp($a){
		echo $a['abs_order'].' '.$a['order'].' '.$a['order2'].' '.$a['article'].' ('.$a['paragraph'].') '.$a['sentence'].' ['.$a['change_type'].'] : ' ;
		echo $a['sentence_text'].'<br />' ;
	}
}
?>