<?php
/**
 * Project class
 * 
 * Povides methods related to project module
 * 
 * @package linea21\modules\project
 * @author $Author$ - linea21 <info@linea21.com>
 * @version $Id$ 
 * @access public
 * @license http://opensource.org/licenses/gpl-3.0.html
 * Project Management
 */

class project {
  /* @param
   * */
  var $TDB_PROJECT = T_PROJECT; // nom de la table.
  var $ID;
  var $PARENT_ID;
  var $SCALE_ID;
  var $PRIORITY_ID;
  var $LEVEL_ID;
  var $WORKSHOP_ID;
  var $NAME;
  var $GOAL;
  var $OPGOAL;
  var $DESCRIPTION;
  var $BODY;
  var $BUDGET;
  var $BUDGET_COMMENT;
  var $COORDINATOR;
  var $MANAGER;
  var $FINALITIES;
  var $ELECTED;
  var $TEAM;
  var $PARTNERS;
  var $TARGETS;
  var $ACHIEVEMENT;
  var $BEGIN_DATE;
  var $ESTIMATED_DATE;
  var $END_DATE;
  var $COMPLETED;
  var $COMPLETED_SYNC;
  var $CAL_COMMENT;
  var $IMPACT;
  var $SDI;
  var $COMMENT;
  var $PUBLISHED_DATE;
  var $POSTED_BY;
  var $DATE_CREA;
  var $STATUT;
  var $LAST_MODIFY;
  protected $dispatcher = null;

  public function __construct()
  {
    $this->dispatcher = $GLOBALS['dispatcher'];
  }

  public function __call($method, $arguments)
  {
    $event = $this->dispatcher->notifyUntil(new sfEvent($this, 'project.extensible_function', array(
      'method'    => $method,
      'arguments' => $arguments
    )));
    if (!$event->isProcessed())
    {
      throw new Exception(sprintf('Call to undefined method %s::%s.', get_class($this), $method));
    }

    return $event->getReturnValue();
  }

  /**
   * project::_getDate()
   * date courante et ajout
   *
   * @access private
   * @param int $m chiffre des mois
   * @param int $d chiffre des jours
   * @param int $y chiffre des années
   * @return string $format_date
   * @return
   */
  function _getDate($m = 0, $d = 0, $y = 0)
  {
    $format_date = mktime(date("H"), date("i"), date("s"), date("m") + $m, date("d") + $d, date("Y") + $y);
    $format_date = date('Y-m-d H:i:s', $format_date);

    return $format_date;
  }
  
  /**
   * project::getCompletion()
   * get completion based on done tasks
   *
   * @access public
   * @param integer $id
   * @param object $sql_object
   * @param string 
   * @return integer
   */
  public function getCompletion($id, $sql_object, $format = 'percent') {
      
      global $l21auth;
      
      $r = $sql_object->DBSelect(SQL_get_project_tasks_count($id)); // we get all tasks
      
      if($r[0]['count'] == 0) return 0;
      
      $d = $sql_object->DBSelect(SQL_get_project_tasks_count($id, array('done'))); // we get all done tasks
      if(!is_array($d)) $done = 0; else $done = $d[0]['count'];
      
      $value = round($done * 100 / $r[0]['count'], 0);
      
      // round value based on PROJECT_STEP
      if($format != 'percent') $value = round($value / PROJECT_STEP) * PROJECT_STEP;
      
      return $value;
  }
  

  /**
   * project::CheckDataIntegrity()
   * Vérification intégrité des données
   *
   * @access public
   * @param array $table : contient les composants Nécessaires d'un projet
   * @return boolean true
   * si verifié, sinon string 'message d'erreur'
   */

  function CheckDataIntegrity($table, $sql_object)
  {
  	// Filter data event + return value
  	$r = $this->dispatcher->filter(new sfEvent($this, 'project.before_datacheck', array('data' => $table)), $table);
  	$table = $r->getReturnValue();

    if (strlen($table[0]) < 3) return _t('project','no_name');
    if (strlen($table[1]) < 3) return _t('project','no_description');
    if ($table[5] <= 0 || !is_numeric($table[5])) return _t('project','no_priority');
    if ($table[4] <= 0 || !is_numeric($table[4])) return _t('project','no_scale');
    if ($table[11] <= 0 || !is_numeric($table[11])) return _t('project','no_level');

    if(!empty($table[6])) {
    	$r = checkdate_validity($table[6], _t('project','begin_date_err'));
    	if(is_string($r)) return $r;
    }
    if(!empty($table[7])) {
    	$r = checkdate_validity($table[7], _t('project','estimated_date_err'));
    	if(is_string($r)) return $r;
    }
    if(!empty($table[8])) {
    	$r = checkdate_validity($table[8], _t('project','end_date_err'));
    	if(is_string($r)) return $r;
    }
    
    // compare Start date and estimated date if provided
    if(!empty($table[6]) && !empty($table[7])) {
    	$start_date = formatDate($table[6], true);
    	$est_date = formatDate($table[7], true);
    	if (strtotime($start_date) > strtotime($est_date)) return _t('project','inconsistent_date');
    }
    // compare Start date and end date if provided
    if(!empty($table[6]) && !empty($table[8])) {
    	$start_date = formatDate($table[6], true);
    	$end_date = formatDate($table[8], true);
    	if (strtotime($start_date) > strtotime($end_date)) return _t('project','inconsistent_date');
    }

    if(!is_numeric($table[14]) || $table[14]>100 || $table[14]<0 ) return _t('project','progression_error');
    if(!empty($table[15]) && !is_numeric($table[15])) return _t('project','budget_integer');
    
    $table[18] = trim($table[18]);
    if(!empty($table[18])) return $this->CheckUsersIntegrity($table[18], $sql_object);

    // Notify the beginning of the current method
    $this->dispatcher->notify(new sfEvent($this, 'project.after_datacheck', array('data' => $table)));

    return true;
  }
  
  /**
   * workshop::CheckUsersIntegrity()
   * Vérification de l'existence des utilisateurs (responsables) fournis en paramètres
   * et des permissions ('A' ou 'O') sur le module 'projet'
   *
   * @access public
   * @param string $logins
   * @param integer $id
   * @param object $sql_object
   * @return string $result
   */
  function CheckUsersIntegrity($logins, $sql_object)
  {
  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.check_users_integrity', array('logins' => $logins)));
  
  	$logins = $sql_object->DBescape($logins);
  	
  	if (substr($logins, -1) == ',') {
  		$logins = substr($logins, 0, -1);
  	}
  	$users = @explode(',', $logins);
  
  	foreach ($users as $user) {
  		
  		$user = trim($user);
  		
  		// we get the user id from login
  		$r = $sql_object->DBSelect(SQL_get_UserInfo($user));
  		// if user does not exist, we throw an error
  		if(!isset($r[0]['user_id'])) return sprintf(_t('project','incorrect_user'), $user);
  		
  		$r = $sql_object->DBSelect( SQL_Get_UserInfo4Auth($r[0]['user_login'], $r[0]['user_password']));
  		
  		// if user has no 'project' permission, we throw an error
  		if(!isset($r[0]['user_id']) || ($r[0]['rights_project'] != 'A' && $r[0]['rights_project'] != 'O')) return sprintf(_t('project','incorrect_userperm'), $user);  		
  	}
  
  	return true;
  }

  /**
   * project::AddProject()
   * Ajout d'un projet
   *
   * @access public
   * @param array $array : contient les composants d'un projet
   * @param object $sql_object
   * @return integer $last_id
   */
  function AddProject($array, $sql_object)
  {
  	global $l21auth;
  	
  	// Filter data event + return value
  	$r = $this->dispatcher->filter(new sfEvent($this, 'project.before_add', array('data' => $array)), $array);
  	$array = $r->getReturnValue();
  	
  	// image 64 encoded conversion if enabled before escaping content
  	$array[1] = convertBase64Images($array[1], 'project-');
  	$array[2] = convertBase64Images($array[2], 'project-');
  	$array['goal'] = convertBase64Images($array['goal'], 'project-');
  	$array['opgoal'] = convertBase64Images($array['opgoal'], 'project-');
  	$array['budget_comment'] = convertBase64Images($array['budget_comment'], 'project-');
  	$array['cal_comment'] = convertBase64Images($array['cal_comment'], 'project-');
  	$array['comment'] = convertBase64Images($array['comment'], 'project-');
  	$array['team'] = convertBase64Images($array['team'], 'project-');
  	

    $array=$sql_object->DBescape($array);

    if ($array[0] != '') {
      $this->NAME = strip_input(trim($array[0]), true);
    }
    if ($array[1] != '') {
      $this->DESCRIPTION = strip_input(trim($array[1]), true);
    }
    if ($array[2] != '') {
      $this->BODY = strip_input(trim($array[2]), true);
    }
    if (is_numeric($array[3]) && $array[3] != -1) {
      $this->PARENT_ID = $array[3];
    } else {
      $this->PARENT_ID = 0;
    }
    if (is_numeric($array[4])) {
      $this->SCALE_ID = $array[4];
    }
    if (is_numeric($array[5])) {
      $this->PRIORITY_ID = $array[5];
    }

    $this->ACHIEVEMENT = strip_input(trim($array['achievement']), true);
    if($this->ACHIEVEMENT == 0) $this->ACHIEVEMENT = ''; // if select box is passing O, we store an empty string

    if ($array[6] != '') {
      $this->BEGIN_DATE = formatDate($array[6], true);
    } else {
      $this->BEGIN_DATE = '0001-01-01';
    }
    if ($array[7] != '') {
      $this->ESTIMATED_DATE = formatDate($array[7], true);
    } else {
      $this->ESTIMATED_DATE = '0001-01-01';
    }
    if ($array[8] != '') {
      $this->END_DATE = formatDate($array[8], true);
    } else {
      $this->END_DATE = '0001-01-01';
    }
    if (is_numeric($array[9])) {
      $this->POSTED_BY = $array[9];
    }
    if ($array[10] != '') {
      $this->STATUT = strtoupper($array[10]);
    }
    if (is_numeric($array[11]) && $array[11] != -1) {
      $this->LEVEL_ID = $array[11];
    } else {
      $this->LEVEL_ID = 0;
    }
    if (is_numeric($array[12]) && $array[12] != -1) {
      $this->WORKSHOP_ID = $array[12];
    } else {
      $this->WORKSHOP_ID = 0;
    }
    $this->SDI = $array[13];
    if ($this->STATUT == 'P') {
      $this->PUBLISHED_DATE = $this->_getDate();
    } else {
      $this->PUBLISHED_DATE = '0001-01-01';
    }
    
    if(isset($array['impacts'])) {
       $this->IMPACT  =  $sql_object->DBescape(serialize($array['impacts']));
    } else $this->IMPACT = '';
    
    $this->GOAL = strip_input(trim($array['goal']), true);
    $this->OPGOAL = strip_input(trim($array['opgoal']), true);
    $this->BUDGET_COMMENT = strip_input(trim($array['budget_comment']), true);
    $this->CAL_COMMENT = strip_input(trim($array['cal_comment']), true);
    $this->COMMENT = strip_input(trim($array['comment']), true);
    $this->ELECTED = strip_input(trim($array['elected']), true);
    $this->TEAM = strip_input(trim($array['team']), true);

     $this->COMPLETED = $array[14];
     $this->COMPLETED_SYNC = $array['progression_sync'];

     if(!empty($array[15])) {
     	$this->BUDGET = str_replace(' ', '', $array[15]);
     } else {
     	$this->BUDGET = "NULL";
     }

     $this->COORDINATOR = $array[16];

     $this->PARTNERS = $array[17];
     
     $this->TARGETS = $array['targets'];
     
     $this->MANAGER = $array[18];
     
     $this->FINALITIES = $array['finalities'];

    $query = "INSERT INTO " . $this->TDB_PROJECT . " (project_name, project_goal, project_opgoal, project_description, project_body, ".
    "project_elected, project_team, project_achievement, " .
    "project_budget, project_budget_comment, project_comment, " .
    "project_parent_id, project_priority_id, project_scale_id, " .
    "project_begin_date, project_estimated_date, project_end_date, project_cal_comment, project_impact, ".
    "project_completed, project_completed_sync, project_level_id, project_workshop_id, ".
    "project_posted_by, project_published_date, " .
    "project_statut, project_date_crea) " .
    "VALUES('" . $this->NAME . "', '" . $this->GOAL . "', '" . $this->OPGOAL . "', '" . $this->DESCRIPTION . "', '" . $this->BODY .
    "', '" . $this->ELECTED . "', '" . $this->TEAM . "', '" . $this->ACHIEVEMENT .
    "', " . $this->BUDGET . ", '" . $this->BUDGET_COMMENT . "', '" . $this->COMMENT ."', " . $this->PARENT_ID . ", " . $this->PRIORITY_ID . ", " . $this->SCALE_ID .
    ", '" . $this->BEGIN_DATE . "', '" . $this->ESTIMATED_DATE . "', '" . $this->END_DATE . "', '" . $this->CAL_COMMENT . "', '" . $this->IMPACT .
    "', " . $this->COMPLETED . ", '" . $this->COMPLETED_SYNC . "', " . $this->LEVEL_ID . ", " . $this->WORKSHOP_ID .
    ", " . $this->POSTED_BY . ", '" . $this->PUBLISHED_DATE . "' , '" . $this->STATUT .
    "', now());";

    $this->ID = $sql_object->DBInsert ($query, 1);
    $this->_addSDI($sql_object);
    $this->addActors($this->ID, $this->COORDINATOR, 'coordinator');
    $this->addActors($this->ID, $this->PARTNERS, 'partner');
    $this->addActors($this->ID, $this->TARGETS, 'target');
    $this->addManagers($this->ID, $this->MANAGER);
    $this->addFinalities($this->ID, $this->FINALITIES);
    
    // we log add action
    if(is_numeric($this->ID))	logfile(LOG_MAINFILE, array('[action] add project', 'ID='.$this->ID, 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));
    
    
    // Notify the end of the current method
    $this->dispatcher->notify(new sfEvent($this, 'project.after_add', array('data' => $array, 'id' => $this->ID)));

    return $this->ID;
  }

  /**
   * project::modifyManagers()
   * Ajout d'un ou plusieurs manager(s) au projet
   *
   * @access private
   * @param int $id identifiant du projet
   * @param string $content manager(s) séparés par des virgules
   * @return integer $last_id
   */
  private function modifyManagers($id, $content) {
  
  	global $sql_object;
  
  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.modify_manager', array('id' => $id, 'content' => $content)));
  
  	$query = "DELETE FROM " . J_PROJECT_MANAGER . " WHERE jpm_project_id='".$id."';";
  	$result = $sql_object->DBQuery($query);
  	 
  	$result = $this->addManagers($id, $content);
  
  	return $result;
  }

  /**
   * project::modifyActors()
   * Ajout d'un ou plusieurs acteurs au projet
   *
   * @access private
   * @param int $id identifiant du projet
   * @param string $content acteurs séparés par des virgules
   * @param string $type
   * @return integer $last_id
   */
  private function modifyActors($id, $content, $type) {

  	global $sql_object;
  	 
  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.modify_actor', array('id' => $id, 'content' => $content, 'type' => $type)));
  	 
  	$query = "DELETE FROM " . J_PROJECT_ACTOR . " WHERE jpa_project_id='".$id."' AND jpa_type='" . $type . "';";
  	$result = $sql_object->DBQuery($query);
  	
  	if(!empty($content)) {
  		$result = $this->addActors($id, $content, $type);
  	}
  
  	return $result;
  }
  
  /**
   * project::modify_actor_lite()
   * Ability to inline edit actor 
   *
   * @access public
   * @param int $id actor's identifier
   * @param string $name actor's new name
   * @param object $sql_object
   * @return boolean $r
   */
  public function modify_actor_lite($id, $name, $sql_object) {
      
      global $l21auth;
      
      // Notify the beginning of the current method
      $this->dispatcher->notify(new sfEvent($this, 'project.modify_actor_lite', array('id' => $id, 'name' => $name)));
      
      $name  = $sql_object->DBEscape(strip_tags($name));
      
      $q = "UPDATE ". T_ACTOR ." SET actor_name = '" . trim($name). "' WHERE  actor_id = ".$id.";";
      
      $r = $sql_object->DBQuery($q);
      
      // we log modify_actor_lite action
      if($r)	logfile(LOG_MAINFILE, array('[action] modify actor lite', 'ID='.$id, 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));
      
      
      return $r;
  }
  
  /**
   * project::addFinalities()
   * Ajout d'un ou plusieurs finalité(s) au projet
   *
   * @access private
   * @param int $id identifiant du projet
   * @param array $finalities tableau de finalités
   * @return bool true
   */
  private function addFinalities($id, $finalities)
  {
  	global $sql_object;
  
  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.add_finality', array('id' => $id, 'finalities' => $finalities)));
  
  	if(empty($finalities)) return true;
  	 
    foreach ($finalities as $finality) {
    	
      $q = "INSERT INTO " . J_PROJECT_FINALITY. " (jpf_project_id, jpf_finality_id, jpf_value) VALUES(".$id.",".$finality.", 'Y');";
      $last_id = $sql_object->DBInsert ($q, 1);
      
    }
  
  	return true;
  }
  
  
  /**
   * project::modifyManagers()
   * Modification d'une ou plusieurs finalité(s) au projet
   *
   * @access private
   * @param int $id identifiant du projet
   * @param array $finalities tableau de finalités
   * @return bool true
   */
  private function modifyFinalities($id, $finalities) {
  
  	global $sql_object;
  
  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.modify_finality', array('id' => $id, 'finalities' => $finalities)));
  
  	$q = "DELETE FROM " . J_PROJECT_FINALITY . " WHERE jpf_project_id='".$id."';";
  	$result = $sql_object->DBQuery($q);
  
  	$result = $this->addFinalities($id, $finalities);
  
  	return $result;
  }
  
  /**
   * project::addManagers()
   * Ajout d'un ou plusieurs manager(s) au projet
   *
   * @access private
   * @param int $id identifiant du projet
   * @param string $content utilisateurs séparés par des virgules
   * @return integer $last_id
   */
  private function addManagers($id, $content)
  {
  	global $sql_object;
  	 
  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.add_manager', array('id' => $id, 'content' => $content)));
  
  	if(empty($content)) return true;
  	
  	// we remove latest comma
  	if (substr($content, -1) == ',') {
  		$content = substr($content, 0, -1);
  	}
  	// we explode string to array
  	$array = @explode(',', $content);
  	$array = array_map('trim', $array);
  	$array = array_unique($array, SORT_REGULAR); // remove duplicate if needed
  	$array = $sql_object->DBEscape($array);
  	
  	if(count($array) > 0) {
	  	for ($i = 0;$i < count($array);$i++) {
	  		
	  		// we get the user id
	  		$q = "SELECT user_id FROM " . T_USER . " WHERE lower(user_login) = '" . strtolower($array[$i]) . "';";
	  		$data = $sql_object->DBSelect($q);
	  		
	  		$user_id = $data[0]['user_id'];
	
	  		// finally we insert tuple in table
	  		$q = "INSERT INTO " . J_PROJECT_MANAGER . " (jpm_project_id, jpm_manager_id) VALUES(" . $id . ", " . $user_id . ");";
	  
	  		$last_id = $sql_object->DBInsert ($q, 1);
	  	}
  	}
  
  	return $last_id;
  }

  /**
   * project::addActors()
   * Ajout d'un ou plusieurs acteurs au projet
   *
   * @access private
   * @param int $id identifiant du projet
   * @param string $content acteurs séparés par des virgules
   * @param string $type
   * @return integer $last_id
   */
  private function addActors($id, $content, $type)
  {
  	global $sql_object;
  	
  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.add_actor', array('id' => $id, 'content' => $content, 'type' => $type)));

  	if(empty($content)) return true;
  	
  	// we remove latest comma
  	if (substr($content, -1) == ',') {
  		$content = substr($content, 0, -1);
  	}
  	// we explode string to array
  	$array = @explode(',', $content);
  	$array = array_map('trim', $array);
  	$array = $sql_object->DBEscape($array);
  	
  	if(count($array) > 0) {
	  	for ($i = 0;$i < count($array);$i++) {
	  		// we check if actor already exists
	  		$q = "SELECT actor_id FROM " . T_ACTOR . " WHERE lower(actor_name) = '" . strtolower($array[$i]) . "';";
	  		$data = $sql_object->DBSelect($q);
	
	  		// if already exists, we retrieve id
	  		if(isset($data[0]['actor_id'])) {
	  			$actor_id = $data[0]['actor_id'];
	  		} else {
	  			// else we create it and retrieve id
	  			$q = "INSERT INTO " . T_ACTOR . " (actor_name) VALUES('" . $array[$i] . "');";
	  			$last_id = $sql_object->DBInsert ($q, 1);
	  			$actor_id = $last_id;
	  		}
	  		// finally we insert tuple in table
	  		$q = "INSERT INTO " . J_PROJECT_ACTOR . " (jpa_project_id, jpa_actor_id, jpa_type) VALUES(" . $id . ", " . $actor_id . ",'" . $type . "');";
	
	  		$last_id = $sql_object->DBInsert ($q, 1);
	  	}
  	}
  
  	return $last_id;
  }

  /**
   * project::DeleteProject()
   * suppression d'un projet
   *
   * @access public
   * @param int $ID : identifiant du projet
   * @param object $sql_object
   * @return bool $result
   */

  function DeleteProject($ID, $sql_object)
  {
  	global $l21auth;
  	
  	// Notify the end of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.delete', array('id' => $ID)));

    $this->ID = $ID;

    $query = "UPDATE " . $this->TDB_PROJECT . " set project_statut='E', project_last_modify=NOW() WHERE project_id='" . $this->ID . "';";
    $result = $sql_object->DBQuery($query);
    if($result) {
      $query = "DELETE FROM " . J_PROJECT_SDI . " WHERE jps_project_id='".$this->ID."';";
      $res = $sql_object->DBQuery($query);
    }
    if($result) {
    	$query = "DELETE FROM " . J_PROJECT_ACTOR . " WHERE jpa_project_id='".$this->ID."';";
    	$res = $sql_object->DBQuery($query);
    }
    if($result) {
    	$query = "DELETE FROM " . J_PROJECT_MANAGER . " WHERE jpm_project_id='".$this->ID."';";
    	$res = $sql_object->DBQuery($query);
    }
    if($result) {
    	$query = "DELETE FROM " . J_PROJECT_FINALITY . " WHERE jpf_project_id='".$this->ID."';";
    	$res = $sql_object->DBQuery($query);
    }
    if($result) {
      $query = "UPDATE " . $this->TDB_PROJECT . " set project_parent_id=0 WHERE project_parent_id='" . $this->ID . "';";
      $result = $sql_object->DBQuery($query);
    }
    
    // we log delete action
    if($result)	logfile(LOG_MAINFILE, array('[action] delete project', 'ID='.$ID, 'by_id='.$l21auth->GetSessionElement('id'), 'by_login='.$l21auth->GetSessionElement('login')));
     
    return $result;
  }

  /**
   * project::ModifyProject()
   * modification d'un projet
   *
   * @access public
   * @param integer $ID : identifiant du projet
   * @param object $sql_object
   * @param array $array : contient les composants d'un projet
   * @return boolean $result
   */
  function ModifyProject($ID, $array, $sql_object)
  {

  	// Filter data event + return value
  	$r = $this->dispatcher->filter(new sfEvent($this, 'project.before_modify', array('data' => $array, 'id' => $ID)), $array);
  	$array = $r->getReturnValue();
  	
  	// image 64 encoded conversion if enabled before escaping content
  	$array[1] = convertBase64Images($array[1], 'project-');
  	$array[2] = convertBase64Images($array[2], 'project-');
  	$array['goal'] = convertBase64Images($array['goal'], 'project-');
  	$array['opgoal'] = convertBase64Images($array['opgoal'], 'project-');
  	$array['budget_comment'] = convertBase64Images($array['budget_comment'], 'project-');
  	$array['cal_comment'] = convertBase64Images($array['cal_comment'], 'project-');
  	$array['comment'] = convertBase64Images($array['comment'], 'project-');
  	$array['team'] = convertBase64Images($array['team'], 'project-');

    $array=$sql_object->DBescape($array);

    if (is_numeric($ID)) {
      $this->ID = $ID;
    }

    if ($array[0] != '') {
      $this->NAME = strip_input(trim($array[0]), true);
    }
    if ($array[1] != '') {
      $this->DESCRIPTION = strip_input(trim($array[1]), true);
    }
    if ($array[2] != '') {
      $this->BODY = strip_input(trim($array[2]), true);
    }
    if (is_numeric($array[3]) && $array[3] != -1) {
      $this->PARENT_ID = $array[3];
    } else {
      $this->PARENT_ID = 0;
    }
    if (is_numeric($array[4])) {
      $this->SCALE_ID = $array[4];
    }
    if (is_numeric($array[5])) {
      $this->PRIORITY_ID = $array[5];
    }

    $this->ACHIEVEMENT = strip_input(trim($array['achievement']), true);
    if($this->ACHIEVEMENT == 0) $this->ACHIEVEMENT = ''; // if select box is passing O, we store an empty string

    if ($array[6] != '') {
      $this->BEGIN_DATE = formatDate($array[6], true);
    } else {
      $this->BEGIN_DATE = '0001-01-01';
    }
    if ($array[7] != '') {
      $this->ESTIMATED_DATE = formatDate($array[7], true);
    } else {
      $this->ESTIMATED_DATE = '0001-01-01';
    }
    if ($array[8] != '') {
      $this->END_DATE = formatDate($array[8], true);
    } else {
      $this->END_DATE = '0001-01-01';
    }
    if ($array[9] != '') {
      $this->STATUT = strtoupper($array[9]);
    }
    if (is_numeric($array[11]) && $array[11] != -1) {
      $this->LEVEL_ID = $array[11];
    } else {
      $this->LEVEL_ID = 0;
    }
    if (is_numeric($array[12]) && $array[12] != -1) {
      $this->WORKSHOP_ID = $array[12];
    } else {
      $this->WORKSHOP_ID = 0;
    }
    $this->SDI = $array[13];
    if ($this->STATUT == 'P') {
      $this->PUBLISHED_DATE = $this->_getDate();
    } else {
      $this->PUBLISHED_DATE = '0001-01-01';
    }

    $this->COMPLETED = $array[14];
    
    $this->COMPLETED_SYNC = $array['progression_sync'];

    if(!empty($array[15])) {
    	$this->BUDGET = str_replace(' ', '', $array[15]);
    } else {
    	$this->BUDGET = "NULL";
    }
    
    if(isset($array['impacts'])) {
        $this->IMPACT  =  $sql_object->DBescape(serialize($array['impacts']));
    } else $this->IMPACT = '';

    $this->GOAL = strip_input(trim($array['goal']), true);
    $this->OPGOAL = strip_input(trim($array['opgoal']), true);
    $this->BUDGET_COMMENT = strip_input(trim($array['budget_comment']), true);
    $this->CAL_COMMENT = strip_input(trim($array['cal_comment']), true);
    $this->COMMENT = strip_input(trim($array['comment']), true);
    $this->ELECTED = strip_input(trim($array['elected']), true);
    $this->TEAM = strip_input(trim($array['team']), true);
    
    $this->COORDINATOR = $array[16];
     
    $this->PARTNERS = $array[17];
    
    $this->TARGETS = $array['targets'];
    
    $this->MANAGER = $array[18];
    
    $this->FINALITIES = $array['finalities'];
     
    $this->modifyActors($this->ID, $this->COORDINATOR, 'coordinator');
    $this->modifyActors($this->ID, $this->PARTNERS, 'partner');
    $this->modifyActors($this->ID, $this->TARGETS, 'target');
    $this->modifyManagers($this->ID, $this->MANAGER);
    $this->modifyFinalities($this->ID, $this->FINALITIES);

    $mask = $this->_HavePublishedDate($array[10]);

    $query = "UPDATE  " . $this->TDB_PROJECT . " SET project_name='" . $this->NAME . "', project_goal='". $this->GOAL ."', project_opgoal='". $this->OPGOAL ."', project_description='" . $this->DESCRIPTION. "', project_body='" . $this->BODY . "', project_budget=" . $this->BUDGET . ", project_budget_comment='". $this->BUDGET_COMMENT ."', project_elected='". $this->ELECTED . "', project_team='". $this->TEAM  . "', project_achievement = '" . $this->ACHIEVEMENT . "', project_parent_id='" . $this->PARENT_ID . "', project_scale_id='" . $this->SCALE_ID . "', project_level_id='" . $this->LEVEL_ID ."', project_comment='". $this->COMMENT ."', project_workshop_id='" . $this->WORKSHOP_ID ."', project_priority_id='" . $this->PRIORITY_ID . "', project_begin_date='" . $this->BEGIN_DATE . "', project_estimated_date='" . $this->ESTIMATED_DATE . "', project_end_date='".$this->END_DATE."', project_cal_comment='". $this->CAL_COMMENT ."', project_impact='". $this->IMPACT ."', project_completed=".$this->COMPLETED.", project_completed_sync='".$this->COMPLETED_SYNC."', project_statut='" . $this->STATUT . "', project_last_modify=NOW() " . $mask . " WHERE project_id='" . $this->ID . "';";
    $result = $sql_object->DBQuery($query);
    $result = $this->_modifySDI($sql_object);

    // Notify the end of the current method
    $this->dispatcher->notify(new sfEvent($this, 'project.after_modify', array('data' => $array, 'id' => $this->ID)));

    return $result;
  }

  /**
   * project::changeRanges()
   * changes Project range
   *
   * @access public
   * @param array : Id (key) and ranges (value)
   * @param object $sql_object
   * @return bool $result
   */

  function changeRanges($array, $sql_object)
  {

    foreach ($array as $key => $value) {
      $query = "UPDATE " . $this->TDB_PROJECT . " set project_range='".$value."' WHERE project_id='" . $key . "';";
      $result = $sql_object->DBQuery($query);
    }

    // Notify the beginning of the current method
    $this->dispatcher->notify(new sfEvent($this, 'project.change_ranges', array('data' => $array)));

    return $result;
  }
  
  
  /**
   * project::changeIndicatorsRanges()
   * changes indicators project range
   *
   * @access public
   * @param integer $id
   * @param array : Id (key) and ranges (value)
   * @param object $sql_object
   * @return bool $result
   */
  
  function changeIndicatorsRanges($id, $array, $sql_object)
  {
   
      if(!is_integer($id)) die('no way');

      $sql_object->DBEscape($array);
      
      foreach ($array as $key => $value) {
          $query = "UPDATE " . $this->TDB_PROJECT . " set project_indic_order='".serialize($array)."' WHERE project_id=" . $id . ";";
          $result = $sql_object->DBQuery($query);
      }
      
      // Notify the beginning of the current method
      $this->dispatcher->notify(new sfEvent($this, 'project.change_indic_ranges', array('data' => $array, 'id' => $id)));
      
      return $result;
  }

  /**
   * project::_addSDI()
   * associate indicators to project
   *
   * @access private
   */
  function _addSDI($sql_object) {

  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.add_sdi', array('data' => $this->SDI)));
		
  	$result = true;
    foreach ($this->SDI as &$value) {
      $query = "INSERT INTO ". J_PROJECT_SDI . " VALUES(".$this->ID.", ".$value.");";
      $result = $sql_object->DBQuery($query);
    }
    return $result;
  }

  /**
   * project::_modifySDI()
   * associate indicators to project
   *
   * @access private
   */
  function _modifySDI($sql_object) {

  	// Notify the beginning of the current method
  	$this->dispatcher->notify(new sfEvent($this, 'project.modify_sdi', array('data' => $this->SDI)));

    $query = "DELETE FROM " . J_PROJECT_SDI . " WHERE jps_project_id='".$this->ID."';";
    $result = $sql_object->DBQuery($query);
    if(count($this->SDI)>0) {
      $result = $this->_addSDI($sql_object);
    }

    return $result;
  }

  /**
   * project::_HavePublishedDate()
   * Détermine la date de publication a inserer dans la bdd
   *
   * @access private
   * @param string $current_status : statut actuel de l'actualité
   * @return string $sql_mask
   */
  function _HavePublishedDate($current_status)
  {
    switch ($this->STATUT) {
      case 'P':
        if ($current_status == 'D') $sql_mask = ", project_published_date= NOW()";
        else $sql_mask = '';
        break;
      case 'D':
        $sql_mask = ", project_published_date= '0001-01-01'";
        break;
      default:
        $sql_mask = '';
    }
    return $sql_mask;
  }

    /**
     * @param $sql_object
     * @param $id
     * @param $status
     * @return array
     */
    public function api_list($sql_object, $id, $status) {

        // return ['error' => 1, 'message' => 'not yet implemented !'];

        $a = ['count' => 0, 'results' => []];
        $unique = [];
        $counter = 0;

        ($status == 'all') ? $status = 'all' : $status = 'public';

        $data = $sql_object->DBSelect(SQL_getProjectsList(0, 999, $status));

        if(is_array($data)) {

            foreach ($data as $record) {
                // be sure we don't have duplicates !
                if (!in_array($record['project_id'], $unique)) {
                    array_push($unique, $record['project_id']);
                    $tmpArray = [];
                    $tmpArray['project_id'] = $record['project_id'];
                    $tmpArray['project_name'] = $record['project_name'];
                    $tmpArray['project_level_name'] = $record['level_name'];
                    $tmpArray['project_level_id'] = $record['project_level_id'];
                    $tmpArray['project_status'] = $record['project_statut'];
                    $counter++;
                    array_push($a['results'], $tmpArray);
                }
            }
            $a['count'] = $counter;
        }

        return $a;

    }

    /**
     * @param $sql_object
     * @param $id
     * @return array
     */
    public function api_info($sql_object, $id, $status) {

        // return ['error' => 1, 'message' => 'not yet implemented !'];

        $excludes = ['project_comment' => null, 'parent_name' => null, 'scale_denomination' => null, 'workshop_denomination' => null, 'level_name' => null, 'level_label' => null, 'project_indic_order' => null, 'project_date_crea_display' => null, 'project_last_modify_display' => null, 'project_published_date_display' => null, 'project_published_date_display_long' => null, 'project_posted_by' => null, 'user_login' => null, 'project_statut' => null];

        ($status == 'all') ? $status = array('P', 'D', 'AA', 'PA') : $status = array('P');

        $data = $sql_object->DBSelect(SQL_getoneCompleteProject ($id, $status));

        // return no record message if empty or if status is not public
        if(!is_array($data)) return ['error' => 1, 'message' => _t('api', 'no_record')];

        $r = $data[0];
        // we format fields before returning values
        if(!empty($r['project_impact'])) $r['project_impact'] = unserialize($r['project_impact']);

        // we remove unwanted fields before returning values
        $r = array_diff_key($r, $excludes);

        // we format date
        $r['project_begin_date_display'] = formatDate($r['project_begin_date_display'], true);
        $r['project_estimated_date_display'] = formatDate($r['project_estimated_date_display'], true);
        $r['project_end_date_display'] = formatDate($r['project_end_date_display'], true);

        // we fetch associated target
        $target = $sql_object->DBSelect(SQL_getAssociatedActors($id, 'target'));
        if(!is_array($target)) $target = [];
        $target = $this->ghost_fields($target, array('jpa_id'));

        // we fetch associated indicators
        $indicators = $sql_object->DBSelect(SQL_getProjectSdi($id));
        if(!is_array($indicators)) $indicators = [];

        // we fetch associated coordinator
        $coordinator = $sql_object->DBSelect(SQL_getAssociatedActors($id, 'coordinator'));
        if(!is_array($coordinator)) $coordinator = [];
        $coordinator = $this->ghost_fields($coordinator, array('jpa_id'));

        // we fetch associated managers
        $managers = $sql_object->DBSelect(SQL_getAssociatedManagers($id));
        if(!is_array($managers)) $managers = [];
        $managers = $this->ghost_fields($managers, array('profile_email', 'jpm_id', 'user_id'));

        // we fetch associated partner
        $partner = $sql_object->DBSelect(SQL_getAssociatedActors($id, 'partner'));
        if(!is_array($partner)) $partner = [];
        $partner = $this->ghost_fields($partner, array('jpa_id'));

        // we fetch associated task
        $tasks = $sql_object->DBSelect(SQL_get_project_tasks($id, $status = false));
        if(!is_array($tasks)) $tasks = [];
        $tasks = $this->ghost_fields($tasks, array('user_id', 'user_login', 'task_reminder_lastdate_display', 'task_project_id', 'task_reminder_freq', 'task_reminder_lastdate'));

        $otag = new tag();
        $tags = $otag->getTags('project', $id, false);

        $r = array_merge($r, ['project_target' => $target, 'project_tags' => $tags, 'project_indicators' => $indicators, 'project_coordinators' => $coordinator, 'project_managers' => $managers, 'project_partners' => $partner, 'project_tasks' => $tasks]);

        return $r;

    }


    /**
     * @param $dataset
     * @param $fields
     * @return array
     */
    private function ghost_fields($dataset, $fields) {

        $cleanArray = [];

        if(count($dataset) == 0) return [];

        foreach($dataset as $rec) {

            foreach($fields as $v) {
                if(isset($rec[$v])) unset($rec[$v]);
            }
            // print_r($rec);
            array_push($cleanArray, $rec);
        }
        return $cleanArray;
    }

}
?>