class.tx_nG6_db.php

<?php
/***************************************************************
*  Copyright notice
*
*  (c) 2009 PF bioinformatique de Toulouse <>
*  All rights reserved
*
*  This script is part of the TYPO3 project. The TYPO3 project 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 2 of the License, or
*  (at your option) any later version.
*
*  The GNU General Public License can be found at
*  http://www.gnu.org/copyleft/gpl.html.
*
*  This script 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.
*
*  This copyright notice MUST APPEAR in all copies of the script!
***************************************************************/

require_once(t3lib_extMgm::extPath('nG6').'/lib/class.tx_nG6_utils.php');

/**
 * Class_nG6_db' for the 'nG6' extension.
 * This class is in charge of all communication with the database
 *
 * @author	PF bioinformatique de Toulouse <>
 */
class tx_nG6_db {
	
	/*
	 * Global functions
	*------------------------------------------------------------*/
	
	/**
	* Update the specified field of the table
	 *
	* @param	string  $table the entity to change
	* @param	string  $id     the element id to update
	* @param	string  $field  the field to change
	* @param	string  $value  the new value
	*/
	function update_field($table, $id, $field, $value) {
		if( $field == 'uid' ) {
			throw new Exception ("The function 'update_field' cannot change an id.") ;
		}	
		$GLOBALS['TYPO3_DB']-> exec_UPDATEquery ($table, 'uid='.$id, array($field => $value));
	}
	
	/**
	 * Update run and analyze for purge managment
	 * 
	 * @param	tab	$all_runs_ids	the entire runs ids to change
	 * @param	tab	$all_analyses_ids	the entire analyses ids to change
	 * @param	string	$field	the field to change
	 * @param	string	$value	the new value
	 */
	 
	function update_purge_date( $all_runs_ids,  $all_analyses_ids, $field, $value){
				
				foreach(explode(",", $all_runs_ids) as $run_id){
					tx_nG6_db::update_field('tx_nG6_run', $run_id, $field, $value);
				}
				
				foreach(explode(",", $all_analyses_ids) as $analyse_id){
					tx_nG6_db::update_field('tx_nG6_analyze',$analyse_id,$field,$value);
				}
	}
	

	function select_all_in_view($view, $where) {
		$queryParts = array(
					'SELECT' 	=> '*',
					'FROM' 		=> $view,
					'WHERE' 	=> $where ,
					'GROUPBY' 	=> '',
					'ORDERBY' 	=> '',
					'LIMIT' 	=> ''
			);	
			$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
			return ($res);
	}
	
	function select_count_in_view($view,$where) {
			#select count All runs per project
		$queryParts = array(
					'SELECT' 	=> 'project_id , count(*) AS count',
					'FROM' 		=> $view ,
					'WHERE' 	=> $where,
					'GROUPBY' 	=> 'project_id',
					'ORDERBY' 	=> '',
					'LIMIT' 	=> ''
			);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
    	return ($res);

	}


	function get_project_list_by_group_filter($create_user_id, $user_group){
		
		$where="";
		$list_project_id_filter_by_group="";
		$list_project_id_laboratories="";
		if($user_group !=""){
			#select all project_id with group OR/AND without group
			$queryParts=array(
				'SELECT' => 'project_id',
				'FROM' => 'tx_nG6_view_project_user',
				'WHERE' => 'user_group IN ( '.$user_group .')',
				'GROUPBY' 	=> '',
				'ORDERBY' 	=> '',
				'LIMIT' 	=> ''
			);
		
			$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
			while($res_row= $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
				if(!isset( $list_project_id_filter_by_group)){
					$list_project_id_filter_by_group = $res_row['project_id'];
				}else{
					$list_project_id_filter_by_group .= ",".$res_row['project_id'];
				}
			}
			error_log($list_project_id_filter_by_group,3,"/save/ng6-test/src/nG6/ui/nG6/pi6/test.log");
			$where .= "tx_nG6_view_project_user.project_id NOT IN (".$list_project_id_filter_by_group.")";
		}
			
		if($create_user_id != ""){
			if($where != ""){
				$where .= "AND cruser_id IN (".$create_user_id.")";
			}else{
				$where .= "cruser_id IN (".$create_user_id.")";
			}
		}
		
		error_log($where,3,"/save/ng6-test/src/nG6/ui/nG6/pi6/test.log");
		$queryParts=array(
			'SELECT' => 'uid AS project_id',
			'FROM' => 'tx_nG6_project'
			.' INNER JOIN tx_nG6_view_project_user ON tx_nG6_view_project_user.project_id = uid',
			'WHERE' => $where,
			'GROUPBY' 	=> '',
			'ORDERBY' 	=> '',
			'LIMIT' 	=> ''
		);
		
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			if(!isset ($list_project_id_laboratories)){
				$list_project_id_laboratories=$res_row['project_id'];
			}else{
				$list_project_id_laboratories[$res_row['project_id']] .=",".$res_row['project_id'];
				
			}
		}
		
    	return ($list_project_id_laboratories);
		
	}
	function select_obsolete_list_retention_data_info() {
		$where =  ' DATEDIFF( FROM_UNIXTIME( retention_date ) , now( ) ) <0';
		return (tx_nG6_db::select_list_retention_data_info($where));
	}
	function filter_list_retention_data_info($filter_size, $max_retention_date, $create_user_id, $user_group) {
		$where =  ' DATEDIFF( FROM_UNIXTIME( retention_date ) , now( ) ) <0';
		if ($max_retention_date != "") {
			$where = ' DATEDIFF( FROM_UNIXTIME( retention_date ) , STR_TO_DATE( "'.$max_retention_date.'", "%d/%m/%Y" ) ) <0';
		}
		if ($filter_size != "") {
			$where .=" AND storage_size > $filter_size";
		}
		$project_list_ids = "";
		if ($create_user_id != "" or $user_group != "") {
			$project_list_ids = tx_nG6_db::get_project_list_by_group_filter($create_user_id,$user_group);
			$where .=" AND project_id in (" . $project_list_ids.")" ;
		}
		
		return (tx_nG6_db::select_list_retention_data_info($where));
	}
	
	function select_a_project_retention_data_info($project_id) {
		
		$where =" project_id = " . $project_id ;
		return(tx_nG6_db::select_list_retention_data_info($where));
		
	}
	
	function select_list_retention_data_info($where) {
		$by_project=array();
		
		$res = tx_nG6_db::select_all_in_view( "tx_nG6_view_project_run" , $where);
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			if (!isset ($by_project[$res_row['project_id']])) {
				$by_project[$res_row['project_id']]= array(
						'project_id'			=> $res_row['project_id'],
						'project_name'			=> $res_row['project_name'],
						'state'					=> array( $res_row['state']  => array ('nb_run' => 1, 'size_run' => $res_row['storage_size']+$res_row['purged_size'], 'run_ids'=> $res_row['run_id'])),
						'total_purgeable_size'	=>  $res_row['storage_size'],
						'nb_runs'				=>0,
						'nb_analyses'				=>0);
			}
			else
			{
				if (!isset ($by_project[$res_row['project_id']]['state'][$res_row['state']])) {
					$by_project[$res_row['project_id']]['state'][$res_row['state']]  = array ('nb_run' => 1, 'size_run' =>$res_row['storage_size']+$res_row['purged_size'], 'run_ids'=>$res_row['run_id']);
					$by_project[$res_row['project_id']]['total_purgeable_size'] += $res_row['storage_size'];
				} else {
					$by_project[$res_row['project_id']]['state'][$res_row['state']]['nb_run']+=1;
					$by_project[$res_row['project_id']]['state'][$res_row['state']]['size_run']+=$res_row['storage_size']+$res_row['purged_size'];
					$by_project[$res_row['project_id']]['state'][$res_row['state']]['run_ids'].=",".$res_row['run_id'];
					$by_project[$res_row['project_id']]['total_purgeable_size'] += $res_row['storage_size'];
				}
			}
		}
		
		
		$res = tx_nG6_db::select_all_in_view("tx_nG6_view_project_run_analyze",$where);
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			if (!isset ($by_project[$res_row['project_id']])) {
				
    			$by_project[$res_row['project_id']]= array(
    					'project_id'		=> $res_row['project_id'],
    					'project_name'	=> $res_row['project_name'],
    					'state'				=> array($res_row['state'] => array("nb_analyze"=> 1, 
																						"size_analyze"=> $res_row['storage_size']+$res_row['purged_size'], 
																						'analysis_ids'=> $res_row['analyze_id'])),
						'total_purgeable_size'	=>  $res_row['storage_size'],
						'nb_runs'				=>0,
						'nb_analyses'				=>0);
				
			}else{
			
			
			if (!isset ($by_project[$res_row['project_id']]['state'][$res_row['state']]['analysis_ids'])){
				if (!isset($by_project[$res_row['project_id']]['state'][$res_row['state']])){
					$by_project[$res_row['project_id']]['state'][$res_row['state']] = array("nb_analyze"=>1, "size_analyze"=>$res_row['storage_size']+$res_row['purged_size'],'analysis_ids'=>$res_row['analyze_id']);
				}else{
					$by_project[$res_row['project_id']]['state'][$res_row['state']] = array_merge($by_project[$res_row['project_id']]['state'][$res_row['state']],array("nb_analyze"=>1, "size_analyze"=>$res_row['storage_size']+$res_row['purged_size'],'analysis_ids'=>$res_row['analyze_id']));
				}
				$by_project[$res_row['project_id']]['total_purgeable_size'] += $res_row['storage_size'];
			} else {				
				$by_project[$res_row['project_id']]['state'][$res_row['state']]['nb_analyze'] += 1;
				$by_project[$res_row['project_id']]['state'][$res_row['state']]['size_analyze'] += $res_row['storage_size']+$res_row['purged_size'];
				$by_project[$res_row['project_id']]['state'][$res_row['state']]['analysis_ids'].=",".$res_row['analyze_id'];
				$by_project[$res_row['project_id']]['total_purgeable_size'] += $res_row['storage_size'];
			}
			}
		}		
		
		
		$res = tx_nG6_db::select_all_in_view("tx_nG6_view_project_analyze", $where);
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			if (!isset ($by_project[$res_row['project_id']])) {
    			$by_project[$res_row['project_id']]= array(
    					'project_id'		=> $res_row['project_id'],
    					'project_name'		=> $res_row['project_name'],
    					'state'				=> array(),
    					'total_purgeable_size'	=>  0,
    					'nb_runs'				=>0,
						'nb_analyses'				=>0);
			}
			if (!isset ($by_project[$res_row['project_id']]['state'][$res_row['state']]['analysis_ids'])){
				if (!isset($by_project[$res_row['project_id']]['state'][$res_row['state']])){
					$by_project[$res_row['project_id']]['state'][$res_row['state']] = array("nb_analyze"=>1, "size_analyze"=>$res_row['storage_size']+$res_row['purged_size'],'analysis_ids'=>$res_row['analyze_id']);
				}else{
					$by_project[$res_row['project_id']]['state'][$res_row['state']] = array_merge($by_project[$res_row['project_id']]['state'][$res_row['state']],array("nb_analyze"=>1, "size_analyze"=>$res_row['storage_size']+$res_row['purged_size'],'analysis_ids'=>$res_row['analyze_id']));
				}
				$by_project[$res_row['project_id']]['total_purgeable_size'] += $res_row['storage_size'];
			}
			else
			{
				$by_project[$res_row['project_id']]['state'][$res_row['state']]['nb_analyze'] += 1;
				$by_project[$res_row['project_id']]['state'][$res_row['state']]['size_analyze'] += $res_row['storage_size']+$res_row['purged_size'];
				$by_project[$res_row['project_id']]['state'][$res_row['state']]['analysis_ids'].=",".$res_row['analyze_id'];
				$by_project[$res_row['project_id']]['total_purgeable_size'] += $res_row['storage_size'];
			}
		}
		
		$res = tx_nG6_db::select_count_in_view("tx_nG6_view_project_run","");
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			if (isset ($by_project[$res_row['project_id']])) {
				$by_project[$res_row['project_id']]["nb_runs"]=$res_row['count'];
			}
		}
		
		$res = tx_nG6_db::select_count_in_view("tx_nG6_view_project_run_analyze","");
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			if (isset ($by_project[$res_row['project_id']])) {
				$by_project[$res_row['project_id']]["nb_analyses"]=$res_row['count'];
			}
		}

		$res = tx_nG6_db::select_count_in_view("tx_nG6_view_project_analyze","");
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			if (isset ($by_project[$res_row['project_id']])) {
				if (!isset ($by_project[$res_row['project_id']]["nb_analyses"])) {
					$by_project[$res_row['project_id']]["nb_analyses"]=$res_row['count'];
				}else{
					$by_project[$res_row['project_id']]["nb_analyses"]+=$res_row['count'];
				}
			}
		}
		
		#retrieve user per project
		$res = tx_nG6_db::select_all_in_view("tx_nG6_view_project_user", "");
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			if(isset($by_project[$res_row['project_id']])){
				if (!isset ($by_project[$res_row['project_id']]["users"])) {
					$by_project[$res_row['project_id']]["users"]=array();
				}
				array_push($by_project[$res_row['project_id']]["users"],array ('user_name' => $res_row['user_name'], 'email' =>$res_row['email'], 'groups' =>$res_row['user_group'], 'right_level_label' =>$res_row['right_level_label']));
			}
		}
		
		return($by_project);
	}

		
    /**
     * Select all project for the current user
     *
     * @param	string	$by	by can group, organism, location
     * @param	string	$role	role can be create_user, manager
     * @return	table with all projects
     */
    function select_projects_repartition($role, $by='title') {
    	
    	if ($role == "create_user") {
    		$queryParts = array(
    				'SELECT' 	=> 'tx_nG6_project.uid as pid, fe_groups.title as title, fe_groups.tx_nG6_organism as organism, fe_groups.tx_nG6_location as location',
    				'FROM' 		=> 'tx_nG6_project '
    				.' INNER JOIN fe_users ON tx_nG6_project.cruser_id=fe_users.uid '
    				.' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ',
    				'WHERE' 	=> "",
    				'GROUPBY' 	=> '',
    				'ORDERBY' 	=> '',
    				'LIMIT' 	=> ''
    		);
    		//$where = "tx_nG6_project.cruser_id="
    	} else if ($role == "manager") {
	    	$queryParts = array(
    				'SELECT' 	=> 'tx_nG6_project.uid as pid, fe_groups.title as title, fe_groups.tx_nG6_organism as organism, fe_groups.tx_nG6_location as location',
	    			'FROM' 		=> 'tx_nG6_project '
	    			.' INNER JOIN fe_rights ON tx_nG6_project.uid=fe_rights.project_id '
	    			.' INNER JOIN fe_users ON fe_rights.fe_user_id=fe_users.uid '
	    			.' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ',
	    			'WHERE' 	=> "fe_rights.right_id=1",
	    			'GROUPBY' 	=> '',
	    			'ORDERBY' 	=> '',
	    			'LIMIT' 	=> ''
	    	);
    	}
    	$by_title = array() ;
    	$by_organism = array();
    	$by_location = array();
    	$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
    	while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
    		if (!isset ($by_title[$res_row['title']])) {
    			$by_title[$res_row['title']] = array(
    					'count'			=> 1,
    					'organism'		=> $res_row['organism'],
    					'location'		=> $res_row['location']);
    		} else {
    			$by_title[$res_row['title']]['count'] += 1;
    		}
    		
    		if (!isset ($by_organism[$res_row['organism']])) {
    			$by_organism[$res_row['organism']] = array(
    					'count'			=> 1);
    		} else {
    			$by_organism[$res_row['organism']]['count'] += 1;
    		}
    		
    		if (!isset ($by_location[$res_row['location']])) {
    			$by_location[$res_row['location']] = array(
    					'count'			=> 1);
    		} else {
    			$by_location[$res_row['location']]['count'] += 1;
    		}
    	}
    	if ($by=='title') {
    		return ($by_title);
    	} else if ($by=='organism') {
    		return ($by_organism);
    	} else if ($by=='location') {
    		return ($by_location);
    	}
    }

    function select_projects_evolution($values, $by, $role, $cumulate) {
		if ($by == "organism" || $by == "location") {
			$by = "tx_nG6_".$by;
		}
	
		$from = "" ;
		$where = "fe_groups.".$by." IN ('".implode("', '", $values)."')" ;
    	if ($role == "create_user") {
    		$from = 'tx_nG6_project '
	    			.' INNER JOIN fe_users ON tx_nG6_project.cruser_id=fe_users.uid '
	    			.' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ' ;
    	} else if ($role == "manager") {
    		$from = 'tx_nG6_project '
    				.' INNER JOIN fe_rights ON tx_nG6_project.uid=fe_rights.project_id '
    				.' INNER JOIN fe_users ON fe_rights.fe_user_id=fe_users.uid '
    				.' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ';
    		$where .= " AND fe_rights.right_id=1" ;
    	}
    	$queryParts = array(
    			'SELECT' 	=> 'tx_nG6_project.crdate, count(tx_nG6_project.crdate) nb',
    			'FROM' 		=> $from,
    			'WHERE' 	=> $where,
    			'GROUPBY' 	=> 'tx_nG6_project.crdate',
    			'ORDERBY' 	=> 'tx_nG6_project.crdate',
    			'LIMIT' 	=> ''
    	);
    	$results = array();
    	$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
    	while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
    		if ($cumulate && count($results) > 0) {
    			$results[] = array($res_row["crdate"], $res_row["nb"] + $results[count($results)-1][1]);
    		} else {
    			$results[] = array($res_row["crdate"], $res_row["nb"]);
    		}
    	}

    	return $results;
    }

    function select_projects_distribution($values, $by, $role) {
		$from = "" ;
		$where = "" ;
		if ($by == "organism" || $by == "location") {
			$by = "tx_nG6_".$by;
		}
    	if ($role == "create_user") {
    		$from = 'tx_nG6_project '
	    			.' INNER JOIN fe_users ON tx_nG6_project.cruser_id=fe_users.uid '
	    			.' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ' ;
    	} else if ($role == "manager") {
    		$from = 'tx_nG6_project '
    				.' INNER JOIN fe_rights ON tx_nG6_project.uid=fe_rights.project_id '
    				.' INNER JOIN fe_users ON fe_rights.fe_user_id=fe_users.uid '
    				.' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ';
    		$where = "fe_rights.right_id=1" ;
    	}
    	$queryParts = array(
    			'SELECT' 	=> 'fe_groups.'.$by.', count(fe_groups.'.$by.') nb',
    			'FROM' 		=> $from,
    			'WHERE' 	=> $where,
    			'GROUPBY' 	=> 'fe_groups.'.$by,
    			'ORDERBY' 	=> '',
    			'LIMIT' 	=> ''
    	);
    	$results = array();
    	$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
    	$others = 0 ;
    	while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
    		if( in_array($res_row[$by], $values) ) {
    			$results[] = array($res_row[$by], $res_row["nb"]);
    		} else {
    			$others += $res_row["nb"] ;
    		}
    	}
    	if ($others != 0) { 
    		$results[] = array("OTHERS", $others);
    	}
    	return $results;
    }
    
    function select_storage_distribution($values, $by, $role, $get_analyzes, $octet) {
    	if ($by == "organism" || $by == "location") {
    		$by = "tx_nG6_".$by;
    	}
    	$storage_unit = "full_seq_size";
    	if ($octet) {
    		$storage_unit = "storage_size";
    	}
    	$from = "" ;
    	$where = "" ;
    	if ($role == "create_user") {
    		$from = 'tx_nG6_project '
    		.' INNER JOIN tx_nG6_project_run ON tx_nG6_project_run.project_id= tx_nG6_project.uid'
    		.' INNER JOIN tx_nG6_run ON tx_nG6_project_run.run_id= tx_nG6_run.uid'
    		.' INNER JOIN fe_users ON tx_nG6_project.cruser_id=fe_users.uid '
    		.' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ' ;
    	} else if ($role == "manager") {
    		$from = 'tx_nG6_project '
    		.' INNER JOIN tx_nG6_project_run ON tx_nG6_project_run.project_id= tx_nG6_project.uid'
    		.' INNER JOIN tx_nG6_run ON tx_nG6_project_run.run_id= tx_nG6_run.uid'
    		.' INNER JOIN fe_rights ON tx_nG6_project.uid=fe_rights.project_id '
    		.' INNER JOIN fe_users ON fe_rights.fe_user_id=fe_users.uid '
    		.' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ';
    		$where = "fe_rights.right_id=1" ;
    	}
    	$queryParts = array(
    			'SELECT' 	=> 'tx_nG6_run.uid, tx_nG6_run.'.$storage_unit.', fe_groups.'.$by,
    			'FROM' 		=> $from,
    			'WHERE' 	=> $where,
    			'GROUPBY' 	=> '',
    			'ORDERBY' 	=> '',
    			'LIMIT' 	=> ''
    	);
    	
    	$results = array();
    	$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
    	$others = 0 ;
    	while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			
    		$analyses_size = 0;
    		if ($get_analyzes && $octet) {
    			$run_analysis = tx_nG6_db::get_run_analysis($res_row["uid"]);
    			foreach($run_analysis as $analyse_id => $analyze_values) {
    				$analyses_size += intval($analyze_values["storage_size"]);
    			}
    		}
    		if( in_array($res_row[$by], $values) ) {
    			
    			if( array_key_exists( $res_row[$by], $results) ) {
    				$results[$res_row[$by]] += $res_row[$storage_unit] + $analyses_size;
    			} else {
    				$results[$res_row[$by]] = $res_row[$storage_unit] + $analyses_size;
    			}
    		} else {
    			$others += $res_row[$storage_unit] + $analyses_size;
    		}
    	}
    	if ($others != 0) {
    		$results["OTHERS"] = $others;
    	}
    	$final_results = array();
    	foreach($results as $group_key => $group_values) {
    		$final_results[] = array( $group_key, $group_values );
    	}
    	return $final_results;
    }
    
	function select_storage_evolution($values, $by, $role, $get_analyzes, $octet, $cumulate) {
        if ($by == "organism" || $by == "location") {
                $by = "tx_nG6_".$by;
        }
        $storage_unit = "full_seq_size";
        if ($octet) {
        	$storage_unit = "storage_size";
        }
        $from = "" ;
        $where = "fe_groups.".$by." IN ('".implode("', '", $values)."')" ;
       
        if ($role == "create_user") {
                $from = 'tx_nG6_project '
                .' INNER JOIN tx_nG6_project_run ON tx_nG6_project_run.project_id= tx_nG6_project.uid'
                .' INNER JOIN tx_nG6_run ON tx_nG6_project_run.run_id= tx_nG6_run.uid'
                .' INNER JOIN fe_users ON tx_nG6_project.cruser_id=fe_users.uid '
                .' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ' ;
        } else if ($role == "manager") {
                $from = 'tx_nG6_project '
                .' INNER JOIN tx_nG6_project_run ON tx_nG6_project_run.project_id= tx_nG6_project.uid'
                .' INNER JOIN tx_nG6_run ON tx_nG6_project_run.run_id= tx_nG6_run.uid'
                .' INNER JOIN fe_rights ON tx_nG6_project.uid=fe_rights.project_id '
                .' INNER JOIN fe_users ON fe_rights.fe_user_id=fe_users.uid '
                .' INNER JOIN fe_groups ON fe_groups.uid=fe_users.usergroup ';
                $where .= " AND fe_rights.right_id=1" ;
        }
        $queryParts = array(
                        'SELECT'        => 'tx_nG6_run.uid, tx_nG6_run.crdate, tx_nG6_run.'.$storage_unit.', tx_nG6_run.purged_size, fe_groups.'.$by,
                        'FROM'          => $from,
                        'WHERE'         => $where,
                        'GROUPBY'       => '',
                        'ORDERBY'       => 'tx_nG6_run.crdate',
                        'LIMIT'         => ''
        );

        $results = array();
        $res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
        while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
                $analyses_size = 0;
                $analyses_size_purged = 0;
                if ($get_analyzes && $octet) {
                        $run_analysis = tx_nG6_db::get_run_analysis($res_row["uid"]);
                        foreach($run_analysis as $analyse_id => $analyze_values) {
                                $analyses_size += intval($analyze_values["storage_size"]);
                                $analyses_size_purged += intval($analyze_values["purged_size"]) + intval($analyze_values["storage_size"]);
                        }
                }

                if( array_key_exists( $res_row["crdate"], $results) ) {
                        $results[$res_row["crdate"]][0] += $res_row[$storage_unit] + $analyses_size;
                        $results[$res_row["crdate"]][1] += $res_row["purged_size"] + $analyses_size_purged;
                } else {
                        $results[$res_row["crdate"]] = array($res_row[$storage_unit] + $analyses_size, $res_row["purged_size"] + $analyses_size_purged);
                }
        }
        
        $final_results = array();
        foreach($results as $group_key => $group_values) {
                if ($cumulate && count($final_results) > 0) {
                        $final_results[] = array( $group_key, $group_values[0] + $final_results[count($final_results)-1][1],$group_values[0] + $group_values[1] + $final_results[count($final_results)-1][2]);
                } else {
                        $final_results[] = array( $group_key, $group_values[0], $group_values[0] + $group_values[1] );
                }

        }
        return $final_results;
    }

	/**
	 * Add a comment to the comment table
	 * @param unknown $table_name
	 * @param unknown $id_label
	 * @param unknown $element_id
	 * @param unknown $cruser_id
	 * @param unknown $new_comment
	 */
    function add_comment($table_name, $id_label, $id, $cruser_id, $new_comment){
    	$new_comment = htmlentities($new_comment, ENT_QUOTES | ENT_COMPAT | ENT_HTML5);
    	$comment_data = array(
    	 		'cruser_id'			=>	$cruser_id,
    	 		'comment'			=>	$new_comment,
    	 		'tstamp'			=> 	time(),
    	 		'crdate'			=> 	time()
    	);
    	$GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_nG6_comment', $comment_data);
    	$comment_id =  $GLOBALS['TYPO3_DB']->sql_insert_id();
    	
    	$comment_link_data = array(
    	 		'cruser_id'		=>	$cruser_id,
    	 		$id_label		=>	$id,
    			'comment_id'	=> 	$comment_id,
    	 		'tstamp'		=> 	time(),
    	 		'crdate'		=> 	time()
    	);
    	
    	$GLOBALS['TYPO3_DB']->exec_INSERTquery($table_name, $comment_link_data);
    	return $GLOBALS['TYPO3_DB']->sql_insert_id();
    }

    /**
     * Retrieve all comments from a given project, run or analysis
     * @param unknown $table_name
     * @param unknown $id_label
     * @param unknown $uid
     * @return multitype:multitype:unknown
     */
    function get_all_comments($table_name, $id_label, $id){
    	$comments = array();
    	$temp = array();
    	$queryParts = array(
    			'SELECT' 	=> 'tx_nG6_comment.uid AS comment_id, '.
    			'tx_nG6_comment.comment AS comment, '.
    			'tx_nG6_comment.cruser_id AS cruser_id, '.
    			'tx_nG6_comment.crdate AS comment_crdate ',
    			'FROM' 		=> $table_name .' INNER JOIN tx_nG6_comment ON '. $table_name . '.comment_id=tx_nG6_comment.uid',
    			'WHERE' 	=> $table_name . '.' . $id_label . '=' . $id,
    			'ORDERBY' 	=> 'comment_id',
    	);
    	 
    	$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
    	while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
    		$comment_id = $row['comment_id'];
    		if (!isset ($temp['comment_'.$comment_id])) {
    			$temp['comment_'.$comment_id] = '';
    			$user_info = tx_nG6_db::get_user_informations($row['cruser_id']);
    			$comments[] = array(
    					'id'			=> $comment_id,
    					'comment'		=> html_entity_decode($row['comment'], ENT_QUOTES | ENT_COMPAT | ENT_HTML5),
    					'date'			=> date( 'd.M.Y-H:i' ,$row['comment_crdate']),
    					'cruser_info'	=> $user_info,
    			);
    		}
    	}
    	return $comments;
    }
    
    /**
     * Delete a given commentary from the database
     * @param unknown $table_name
     * @param unknown $id_label
     * @param unknown $id
     * @param unknown $comment_id
     */
    function delete_comment($table_name, $id_label, $id, $comment_id){
    	
    	$res = 0 ;
    	//from association table
    	$where = $table_name.'.'.$id_label.'='.$id.' AND '.$table_name.'.comment_id='.$comment_id ;
    	$res = $GLOBALS['TYPO3_DB']-> exec_DELETEquery ($table_name, $where);
    	
    	//from comment table
    	$where = 'tx_nG6_comment.uid='.$comment_id ;
    	$res = $GLOBALS['TYPO3_DB']-> exec_DELETEquery ('tx_nG6_comment', $where);
    	if ($res != 1) {
    		$res = 1 ;
    	}
    	return $res ;
    }
    
    /**
     * Update an existing comment
     * @param unknown $comment_id
     * @param unknown $new_comment
     */
    function update_comment($comment_id, $new_comment){
    	return $GLOBALS['TYPO3_DB']-> exec_UPDATEquery ('tx_nG6_comment', 'uid='.$comment_id, array('comment' => $new_comment));
    }
    
    
	/*
	 * Project functions
	 *------------------------------------------------------------*/

    /**
     * Add a comment to the project described by project_id
     * @param unknown $project_id
     * @param unknown $cruser_id
     * @param unknown $new_comment
     */
    function add_project_comment($project_id, $cruser_id, $new_comment){
    	return tx_nG6_db::add_comment('tx_nG6_project_comment', 'project_id', $project_id, $cruser_id, $new_comment);
    }
    
    /**
     * Retrieve all comments from a given project
     * @param unknown $project_id
     */
    function get_all_project_comments($project_id){
    	return tx_nG6_db::get_all_comments('tx_nG6_project_comment', 'project_id', $project_id);
    }
    
    /**
     * Delete a given project comment
     * @param unknown $project_id
     * @param unknown $comment_id
     */
    function delete_project_comment($project_id, $comment_id){
    	return tx_nG6_db::delete_comment('tx_nG6_project_comment', 'project_id', $project_id, $comment_id);
    }
    
	/**
	 * Select all project for the current user
	 * 
	 * @param	string	$user_id	the user id
	 * @return	table with all projects
	 */
	function select_all_user_projects($user_id, $orderby='', $limit='') {		
		$projects = array();
		
		// If the user is not logged on display demonstration project
		if ($user_id == null) { $where = 'tx_nG6_project.public=0'; } 
		else { $where = 'fe_rights.fe_user_id='.$user_id; }
		
		//First, get the analyzis project-scale
		$queryParts = array(
			'SELECT' => 'tx_nG6_project.uid AS project_id, '.
						'tx_nG6_project.name AS project_name, '.
						'tx_nG6_project.description AS project_description, '.
						'tx_nG6_project.public AS project_public, '.
						'tx_nG6_project.crdate AS project_crdate, '.
						'tx_nG6_project.hidden AS project_hidden ',
			'FROM' => 'tx_nG6_project INNER JOIN fe_rights ON fe_rights.project_id=tx_nG6_project.uid',
			'WHERE' => $where,
			'GROUPBY' => '',
			'ORDERBY' => $orderby,
			'LIMIT' => $limit
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);		
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$project_id = $row['project_id'];
			if (!isset ($projects['project_'.$project_id])) {		
				$projects['project_'.$project_id] = array(
					'id'			=> $project_id,				
					'name'			=> $row['project_name'],
					'hidden'		=> $row['project_hidden'],
					'date'			=> $row['project_crdate'],
					'public'		=> $row['project_public'],
					'description'	=> $row['project_description']);
			}
						
		}		
		return $projects;
	}

	/**
	* Select all project for the current user
	*
	* @param	string	$user_id	the user id
	* @return	table with all projects
	*/
	function get_user_projects($user_id, $orderby='', $limit='') {
		$projects = array();
	
		// If the user is not logged on display demonstration project
		if ($user_id == null) {
			$where = 'tx_nG6_project.public=0';
		}
		else { 
				$where = 'fe_rights.fe_user_id='.$user_id.' AND ((fe_rights.right_id<>2 AND tx_nG6_project.hidden=0) '
				.'OR fe_rights.right_id=2)' ;
		}
	
		//First, get the analyzis project-scale
		$queryParts = array(
				'SELECT' => 'tx_nG6_project.uid AS project_id, '.
							'tx_nG6_project.name AS project_name, '.
							'tx_nG6_project.description AS project_description, '.
							'tx_nG6_project.public AS project_public, '.
							'tx_nG6_project.crdate AS project_crdate, '.
							'tx_nG6_project.hidden AS project_hidden ',
				'FROM' => 'tx_nG6_project INNER JOIN fe_rights ON fe_rights.project_id=tx_nG6_project.uid',
				'WHERE' => $where,
				'GROUPBY' => '',
				'ORDERBY' => $orderby,
				'LIMIT' => $limit
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$project_id = $row['project_id'];
			if (!isset ($projects['project_'.$project_id])) {
				$projects['project_'.$project_id] = array(
						'id'			=> $project_id,				
						'name'			=> $row['project_name'],
						'hidden'		=> $row['project_hidden'],
						'date'			=> $row['project_crdate'],
						'public'		=> $row['project_public'],
						'description'	=> $row['project_description']);
			}
	
		}
		return $projects;
	}

	/**
	 * Select a run from the database
	 *
	 * @param	string	$project_id	the project id to return
	 * @return	hash table with all project information
	 */
	function select_project($project_id) {
		$queryParts = array(
			'SELECT' => '*',
			'FROM' => 'tx_nG6_project',
			'WHERE' => 'tx_nG6_project.uid = '.$project_id,
			'GROUPBY' => '',
			'ORDERBY' => '',
			'LIMIT' => ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		$vals = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res);
		return array('id'			=> $vals['uid'],				
					 'name'			=> $vals['name'],
					 'hidden'		=> $vals['hidden'],
					 'date'			=> $vals['crdate'],
					 'public'		=> $vals['public'],
					 'description'	=> $vals['description']);
	}

	/**
	 * Select all runs linked to the specified project
	 *
	 * @param	string	$project_id	the project id
	 * @return	hash table with all runs information
	 */
function get_project_runs($project_id, $orderby='', $limit='') {
    	
		// First select all runs from the database
		$queryParts = Array( 
			'SELECT' => 'tx_nG6_run.uid AS run_id,'.
					 	'tx_nG6_run.directory AS run_directory,'.
					 	'tx_nG6_run.species AS run_species,'.
						'tx_nG6_run.description AS run_description, '.
						'tx_nG6_run.data_nature AS run_data_nature, '.
						'tx_nG6_run.sequencer AS run_sequencer, '.
						'tx_nG6_run.type AS run_type, '.
						'tx_nG6_run.name AS run_name, '.
						'tx_nG6_run.nb_sequences AS run_nb_sequences, '.
						'tx_nG6_run.full_seq_size AS run_full_seq_size, '.
						'tx_nG6_run.date AS run_date, '.
						'tx_nG6_run.hidden AS run_hidden, '.
						'tx_nG6_run.data_state AS run_data_state, '.
						'tx_nG6_run.retention_date AS run_retention_date, '.
						'tx_nG6_run.purged_date AS run_purged_date, '.
						'tx_nG6_run.purged_size AS run_purged_size, '.
						'tx_nG6_run.mail_sent_date AS run_mail_sent_date, '.
						'tx_nG6_project.uid AS project_id, '.
						'tx_nG6_project.name AS project_name ',
			'FROM' => 'tx_nG6_project '.
  						'INNER JOIN tx_nG6_project_run ON  tx_nG6_project.uid=tx_nG6_project_run.project_id '.
  						'INNER JOIN tx_nG6_run ON tx_nG6_project_run.run_id=tx_nG6_run.uid ',
			'WHERE' => 'tx_nG6_project.uid='.$project_id,
			'GROUPBY' => '',
			'ORDERBY' => $orderby,
			'LIMIT' 	=> $limit
		);

	
		// Then create the result hash table
		$results = array();
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$run_id = $row['run_id'];				
			if (!isset($results['run_'.$run_id])) {				
				$results['run_'.$run_id] = array( 
					'id'			=> $run_id,
					'directory'		=> $row['run_directory'], 
					'name' 			=> $row['run_name'],
					'project_name' 	=> $row['project_name'],
					'hidden'		=> $row['run_hidden'],
					'species' 		=> $row['run_species'],
					'nb_sequences' 	=> $row['run_nb_sequences'],
					'full_seq_size' => $row['run_full_seq_size'],
					'project_id' 	=> $row['project_id'],
					'date'			=> $row['run_date'],
					'data_nature'	=> $row['run_data_nature'],
					'sequencer'		=> $row['run_sequencer'],
					'type'			=> $row['run_type'],
					'description' 	=> $row['run_description'],
					'data_state' 	=> $row['run_data_state'],
					'retention_date' 	=> $row['run_retention_date'],
					'purged_date' 	=> $row['run_purged_date'],
					'purged_size' 	=> $row['run_purged_size'],
					'mail_sent_date' 	=> $row['run_mail_sent_date'],
				);
			}						
		}
		return $results;
    }
    /**
     * Select all runs linked to the specified project for the user
     *
     * @param	string	$user_id	the user id
     * @param	string	$project_id	the project id
     * @return	hash table with all runs information
     */
     function get_user_project_runs($user_id, $project_id, $orderby='', $limit='') {
    	// If the user is not logged on display demonstration project
	    if ($user_id == null) {
	    	$where = 'tx_nG6_project.uid='.$project_id.' AND tx_nG6_project.public=0 AND tx_nG6_run.hidden=0';
	    }
	    else {
	    	$where = 'fe_users.uid='.$user_id.' AND '.'tx_nG6_project.uid='.$project_id.
	    		' AND ((fe_rights.right_id<>2 AND tx_nG6_run.hidden=0) OR fe_rights.right_id=2)' ;
	    }
    	
	    // First select all runs from the database
	    $queryParts = Array(
		    'SELECT' => 'tx_nG6_run.uid AS run_id,'.
			    'tx_nG6_run.directory AS run_directory,'.
			    'tx_nG6_run.species AS run_species,'.
			    'tx_nG6_run.description AS run_description, '.
			    'tx_nG6_run.data_nature AS run_data_nature, '.
			    'tx_nG6_run.sequencer AS run_sequencer, '.
			    'tx_nG6_run.type AS run_type, '.
			    'tx_nG6_run.name AS run_name, '.
			    'tx_nG6_run.nb_sequences AS run_nb_sequences, '.
			    'tx_nG6_run.full_seq_size AS run_full_seq_size, '.
			    'tx_nG6_run.date AS run_date, '.
			    'tx_nG6_run.hidden AS run_hidden, '.
			    'tx_nG6_run.data_state AS run_data_state, '.
				'tx_nG6_run.retention_date AS run_retention_date, '.
				'tx_nG6_run.purged_date AS run_purged_date, '.
				'tx_nG6_run.purged_size AS run_purged_size, '.
				'tx_nG6_run.mail_sent_date AS run_mail_sent_date, '.
			    'tx_nG6_project.uid AS project_id, '.
			    'tx_nG6_project.name AS project_name ',
		    'FROM' => 'tx_nG6_run '.
			    'INNER JOIN tx_nG6_project_run ON tx_nG6_run.uid=tx_nG6_project_run.run_id '.
			    'INNER JOIN tx_nG6_project ON tx_nG6_project_run.project_id=tx_nG6_project.uid '.
			    'INNER JOIN fe_rights ON tx_nG6_project.uid=fe_rights.project_id '.
			    'INNER JOIN fe_users ON fe_rights.fe_user_id=fe_users.uid ',
		    'WHERE' => $where,
		    'GROUPBY' => '',
		    'ORDERBY' => $orderby,
		    'LIMIT' 	=> $limit
	    );
    
	    // Then create the result hash table
	    $results = array();
	    $res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
	    while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
	    	$run_id = $row['run_id'];
	    	if (!isset($results['run_'.$run_id])) {
			    $results['run_'.$run_id] = array(
			    'id'			=> $run_id,
			    'directory'		=> $row['run_directory'],
			    'name' 			=> $row['run_name'],
			    'project_name' 	=> $row['project_name'],
			    'hidden'		=> $row['run_hidden'],
			    'data_state' 	=> $row['run_data_state'],
				'retention_date' 	=> $row['run_retention_date'],
				'purged_date' 	=> $row['run_purged_date'],
				'purged_size' 	=> $row['run_purged_size'],
				'mail_sent_date' 	=> $row['run_mail_sent_date'],
			    'species' 		=> $row['run_species'],
			    'nb_sequences' 	=> $row['run_nb_sequences'],
			    'full_seq_size' => $row['run_full_seq_size'],
			    'project_id' 	=> $row['project_id'],
			    'date'			=> $row['run_date'],
			    'data_nature'	=> $row['run_data_nature'],
			    'sequencer'		=> $row['run_sequencer'],
			    'type'			=> $row['run_type'],
			    'description' 	=> $row['run_description']
			    				);
		    }
	    }
	    return $results;
    }

	/**
	 * Select all analysis linked to the specified project
	 *
	 * @param	string	$project_id	the project id
	 * @return	hash table with all analysis information
	 */
    function get_project_analysis($project_id, $orderby='', $limit='') {
    	
    	// First select all analysis from the database    	
    	$queryParts = array( 
			'SELECT' => 'tx_nG6_analyze.uid AS analyze_id,'. 
						'tx_nG6_analyze.directory AS analyze_directory,'. 
						'tx_nG6_analyze.name AS analyze_name, '.
						'tx_nG6_analyze.class AS analyze_class, '.
						'tx_nG6_analyze.date AS analyze_date, '.
						'tx_nG6_analyze.software AS analyze_software, '.
						'tx_nG6_analyze.version AS analyze_version, '.
						'tx_nG6_analyze.data_state AS analyze_data_state, '.
						'tx_nG6_analyze.retention_date AS analyze_retention_date, '.
						'tx_nG6_analyze.purged_date AS analyze_purged_date, '.
						'tx_nG6_analyze.purged_size AS analyze_purged_size, '.
						'tx_nG6_analyze.mail_sent_date AS analyze_mail_sent_date, '.
						'tx_nG6_analyze.params AS analyze_params, '.
						'tx_nG6_analyze.description AS analyze_description, '.
    					'tx_nG6_analyze.is_editable AS analyze_is_editable, '.
						'tx_nG6_analyze.parent_uid AS analyze_parent_uid, '.
						'tx_nG6_analyze.hidden AS analyze_hidden ',
			'FROM' => 'tx_nG6_project '.
						'INNER JOIN tx_nG6_project_analyze ON tx_nG6_project.uid = tx_nG6_project_analyze.project_id '.
						'INNER JOIN tx_nG6_analyze ON tx_nG6_analyze.uid=tx_nG6_project_analyze.analyze_id ',
			'WHERE' => 'tx_nG6_project.uid='.$project_id,
			'GROUPBY' => '',
			'ORDERBY' => $orderby,
			'LIMIT' => $limit
		);
		// Then create the result hash table
		$results = array();
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$analyze_id = $row['analyze_id'];
			if (!isset ($results['analyse_'.$analyze_id])) {				
				$results['analyse_'.$analyze_id] = array( 
					'directory'		=> $row['analyze_directory'], 
					'name' 			=> $row['analyze_name'],
					'class'			=> $row['analyze_class'],
					'id' 			=> $row['analyze_id'],
					'hidden' 		=> $row['analyze_hidden'],
					'params' 		=> $row['analyze_params'],
					'software' 		=> $row['analyze_software'],
					'version' 		=> $row['analyze_version'],
					'data_state' 		=> $row['analyze_data_state'],
					'retention_date' 		=> $row['analyze_retention_date'],
					'purged_date' 		=> $row['analyze_purged_date'],
					'purged_size' 		=> $row['analyze_purged_size'],
					'mail_sent_date' 		=> $row['analyze_mail_sent_date'],
					'date'			=> $row['analyze_date'],
					'description' 	=> $row['analyze_description'],
					'is_editable' 	=> $row['analyze_is_editable'],
					'parent_id'		=> $row['analyze_parent_uid']	
				);
			}						
		}
		return $results;
    }

    /**
    * Select all analysis linked to the specified project for the user
    *
    * @param	string	$user_id	the user id
    * @param	string	$project_id	the project id
    * @return	hash table with all analysis information
    */
    function get_user_project_analysis($user_id, $project_id, $orderby='', $limit='') {
    	// If the user is not logged on display demonstration project
    	if ($user_id == null) {
    		$where = 'tx_nG6_project.uid='.$project_id.' AND tx_nG6_project.public=0 AND tx_nG6_run.hidden=0';
    	}
    	else {
    		$where = 'fe_users.uid='.$user_id.' AND tx_nG6_project.uid='.$project_id.
    					' AND ((fe_rights.right_id<>2 AND tx_nG6_analyze.hidden=0) OR fe_rights.right_id=2)' ;
    	}
    	
    	// First select all analysis from the database
    	$queryParts = array(
    			'SELECT' => 'tx_nG6_analyze.uid AS analyze_id,'. 
    						'tx_nG6_analyze.directory AS analyze_directory,'. 
    						'tx_nG6_analyze.name AS analyze_name, '.
    						'tx_nG6_analyze.class AS analyze_class, '.
    						'tx_nG6_analyze.date AS analyze_date, '.
    						'tx_nG6_analyze.software AS analyze_software, '.
    						'tx_nG6_analyze.version AS analyze_version, '.
    						'tx_nG6_analyze.data_state AS analyze_data_state, '.
							'tx_nG6_analyze.retention_date AS analyze_retention_date, '.
							'tx_nG6_analyze.purged_date AS analyze_purged_date, '.
							'tx_nG6_analyze.purged_size AS analyze_purged_size, '.
							'tx_nG6_analyze.mail_sent_date AS analyze_mail_sent_date, '.
    						'tx_nG6_analyze.params AS analyze_params, '.
    						'tx_nG6_analyze.description AS analyze_description, '.
    						'tx_nG6_analyze.is_editable AS analyze_is_editable, '.
    						'tx_nG6_analyze.parent_uid AS analyze_parent_uid, '.
    						'tx_nG6_analyze.hidden AS analyze_hidden ',
    			'FROM' => 'tx_nG6_analyze '.
    						'INNER JOIN tx_nG6_project_analyze ON tx_nG6_analyze.uid = tx_nG6_project_analyze.analyze_id '.
    						'INNER JOIN tx_nG6_project ON tx_nG6_project_analyze.project_id=tx_nG6_project.uid '.
							'INNER JOIN fe_rights ON tx_nG6_project.uid=fe_rights.project_id '.
					  		'INNER JOIN fe_users ON fe_rights.fe_user_id=fe_users.uid ',
    			'WHERE' => $where,
    			'GROUPBY' => '',
    			'ORDERBY' => $orderby,
    			'LIMIT' => $limit
    	);
    	// Then create the result hash table
    	$results = array();
    	$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
    	while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
    		$analyze_id = $row['analyze_id'];
    		if (!isset ($results['analyse_'.$analyze_id])) {
    			$results['analyse_'.$analyze_id] = array(
    					'directory'		=> $row['analyze_directory'], 
    					'name' 			=> $row['analyze_name'],
    					'class'			=> $row['analyze_class'],
    					'id' 			=> $row['analyze_id'],
    					'hidden' 		=> $row['analyze_hidden'],
    					'params' 		=> $row['analyze_params'],
    					'software' 		=> $row['analyze_software'],
    					'version' 		=> $row['analyze_version'],
    					'date'			=> $row['analyze_date'],
    					'data_state' 		=> $row['analyze_data_state'],
						'retention_date' 		=> $row['analyze_retention_date'],
						'purged_date' 		=> $row['analyze_purged_date'],
						'purged_size' 		=> $row['analyze_purged_size'],
						'mail_sent_date' 		=> $row['analyze_mail_sent_date'],
    					'description' 	=> $row['analyze_description'],
    					'is_editable' 	=> $row['analyze_is_editable'],
    					'parent_id'		=> $row['analyze_parent_uid']	
    			);
    		}
    	}
    	return $results;
    }

    /**
     * Hide a project and sublevels (runs & analyzes) datas from database project
     * 
     * @param	string	$p_id	the project id to hide
     */
	function hide_project($p_id) {
		
		// First change all project runs
		$project_runs = tx_nG6_db::get_project_runs($p_id);
		foreach($project_runs as $run_id => $run_values) {
			tx_nG6_db::hide_run($run_values['id']);
		}

		// Then all project analysis
    	$project_analysis = tx_nG6_db::get_project_analysis($p_id);
		foreach($project_analysis as $analysis_id => $analysis_values) {  
			tx_nG6_db::hide_analysis($analysis_values['id']);
		}
		tx_nG6_db::unpublish_project($p_id);
		// Finaly change the project itself
		$GLOBALS['TYPO3_DB']-> exec_UPDATEquery ('tx_nG6_project', 'uid='.$p_id, array('hidden' => '1'));
	}

    /**
     * Unhide a project and sublevels (runs & analyzes) datas
     * 
     * @param	string	$p_id	the project id to hide
     */
	function unhide_project($p_id) {
		
		// First change all project runs
		$project_runs = tx_nG6_db::get_project_runs($p_id);
		foreach($project_runs as $run_id => $run_values) {
			tx_nG6_db::unhide_run($run_values['id']);
		}

		// Then all project analysis
    	$project_analysis = tx_nG6_db::get_project_analysis($p_id);
		foreach($project_analysis as $analysis_id => $analysis_values) {  
			tx_nG6_db::unhide_analysis($analysis_values['id']);
		}
		
		// Finaly change the project itself
		$GLOBALS['TYPO3_DB']-> exec_UPDATEquery ('tx_nG6_project', 'uid='.$p_id, array('hidden' => '0'));
	}

    /**
     * Publish the project
     * 
     * @param	string	$p_id	the project id to publish
     */
	function publish_project($p_id) {
		// First unhide the project
		tx_nG6_db::unhide_project($p_id);
		// The set the project as visible
		$GLOBALS['TYPO3_DB']-> exec_UPDATEquery ('tx_nG6_project', 'uid='.$p_id, array('public' => '0'));
	}

    /**
     * Unpublish the project
     * 
     * @param	string	$p_id	the project id to publish
     */
	function unpublish_project($p_id) {
		$GLOBALS['TYPO3_DB']-> exec_UPDATEquery ('tx_nG6_project', 'uid='.$p_id, array('public' => '1'));
	}

    /**
     * Delete a project and sublevels (runs & analyzes) datas from database project
     * 
     * @param string $p_id			the project id
     * @param string $user_login	the user login
     * @param string $user_pwd		the user password
     * @param string $data_folder	the data folder
     */
	function delete_project($p_id, $user_login, $user_pwd, $data_folder) {
		
		$res = 0;
		// All runs
		$project_runs = tx_nG6_db::get_project_runs($p_id);
		foreach($project_runs as $run_id => $run_values) {
			if ($run_values['id'] != 'undefined' && $run_values['id'] != '') {
				//delete the run sublevels
				$res = tx_nG6_db::delete_run($run_values['id'], $user_login, $user_pwd, $data_folder);
				if ($res != 0) { break; }				
			}
		}
		
		// All project_analysis
		if ($res == 0) {
			$project_analysis = tx_nG6_db::get_project_analysis($p_id);
			foreach($project_analysis as $analyse_id => $analyze_values) {  
				if ($analyze_values['id'] != 'undefined' && $analyze_values['id'] != '') {
					// delete table 'analyze' entries
					$res = tx_nG6_db::delete_analysis($analyze_values['id'], $user_login, $user_pwd, $data_folder);
					if ($res != 0) { break; }
				}
			}
		}
		
		//all project comments
		$project_comments = tx_nG6_db::get_all_project_comments($p_id);
		foreach($project_comments as $id => $values ){
			tx_nG6_db::delete_project_comment($p_id, $values['id']);
		}
		
		// Delete DB project entry
		if ($res == 0) {
			$table='tx_nG6_project';
			$where='tx_nG6_project.uid='.$p_id;
			$res1 = $GLOBALS['TYPO3_DB']-> exec_DELETEquery ($table, $where);
			if ($res1 != 1) {$res = 1;}
		}
		
		// Delete right associated to the project
		if ($res == 0) {
			$table='fe_rights';
			$where='fe_rights.project_id='.$p_id;
			$res1 = $GLOBALS['TYPO3_DB']-> exec_DELETEquery ($table, $where);
			if ($res1 != 1) {$res = 1;}
		}
		
		return $res;
	}

    /**
     * Return the size of the project
     * 
     * @param	string	$p_id	the project id to return the size
	 * @param	string	$data_folder	the data folder
	 * @param	boolean $get_analyzes	get analyzes size ? 
     */
	function get_project_size($p_id, $get_analyzes=false) {
		
		$full_size = 0;
		// All runs
		$project_runs = tx_nG6_db::get_project_runs($p_id);
		foreach($project_runs as $run_id => $run_values) {
			$full_size += tx_nG6_db::get_run_size($run_values['id'], $get_analyzes);
		}
		if ($get_analyzes) {
			// All analysis
			$project_analysis = tx_nG6_db::get_project_analysis($p_id);
			foreach($project_analysis as $analyse_id => $analyze_values) {  
				$full_size += tx_nG6_db::get_analysis_size($analyze_values['id']);
			}
		}
		return $full_size;
	}


	/*
	 * Run functions
	 *------------------------------------------------------------*/
	
	/**
     * Add a comment to the run described by run_id
     * @param unknown $run_id
     * @param unknown $cruser_id
     * @param unknown $new_comment
     */
    function add_run_comment($run_id, $cruser_id, $new_comment){
    	return tx_nG6_db::add_comment('tx_nG6_run_comment', 'run_id', $run_id, $cruser_id, $new_comment);
    }
    
    /**
     * Retrieve all comments from a given run
     * @param unknown $run_id
     */
    function get_all_run_comments($run_id){
    	return tx_nG6_db::get_all_comments('tx_nG6_run_comment', 'run_id', $run_id);
    }
    
    /**
     * Delete a given run comment
     * @param unknown $run_id
     * @param unknown $comment_id
     */
    function delete_run_comment($run_id, $comment_id){
    	return tx_nG6_db::delete_comment('tx_nG6_run_comment', 'run_id', $run_id, $comment_id);
    }
	
	/**
	 * Select all run for the specified user
	 * 
	 * @param	string	$user_id	the user id
	 * @return	table with all projects
	 */
	function select_all_user_runs($user_id, $orderby='', $limit='') {

		// If the user is not logged on display demonstration project
		if ($user_id == null) { $where = 'tx_nG6_project.public=0'; } 
		else { $where = 'fe_rights.fe_user_id='.$user_id; }
		
     	// Execute the request
		$queryParts = Array( 
			'SELECT' => 'tx_nG6_run.uid AS run_id,'.
					 	'tx_nG6_run.directory AS run_directory,'.
					 	'tx_nG6_run.species AS run_species,'.
						'tx_nG6_run.description AS run_description, '.
						'tx_nG6_run.data_nature AS run_data_nature, '. 
						'tx_nG6_run.sequencer AS run_sequencer, '.
						'tx_nG6_run.type AS run_type, '.
						'tx_nG6_run.name AS run_name, '.
						'tx_nG6_run.nb_sequences AS run_nb_sequences, '.
						'tx_nG6_run.full_seq_size AS run_full_seq_size, '.
						'tx_nG6_run.date AS run_date, '.
						'tx_nG6_run.hidden AS run_hidden, '.
						'tx_nG6_run.data_state AS run_data_state, '.
						'tx_nG6_run.retention_date AS run_retention_date, '.
						'tx_nG6_run.purged_date AS run_purged_date, '.
						'tx_nG6_run.purged_size AS run_purged_size, '.
						'tx_nG6_run.mail_sent_date AS run_mail_sent_date, '.
						'tx_nG6_project.uid AS project_id, '.
						'tx_nG6_project.name AS project_name ',
			'FROM' => 'fe_rights '.
						'INNER JOIN tx_nG6_project ON tx_nG6_project.uid=fe_rights.project_id '.
						'INNER JOIN tx_nG6_project_run ON tx_nG6_project.uid=tx_nG6_project_run.project_id '.
						'INNER JOIN tx_nG6_run ON tx_nG6_project_run.run_id=tx_nG6_run.uid ',
			'WHERE' => $where,
			'GROUPBY' => '',
			'ORDERBY' => $orderby,
			'LIMIT' 	=> $limit
		); 
		// Then create the result hash table
		$results = array();
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$run_id = $row['run_id'];	
			if (!isset($results['run_'.$run_id])) {				
				$results['run_'.$run_id] = array(
					'id'			=> $run_id,  
					'directory'		=> $row['run_directory'], 
					'name' 			=> $row['run_name'],
					'project_name' 	=> $row['project_name'],
					'project_id' 	=> $row['project_id'],
					'hidden'		=> $row['run_hidden'],