Newer
Older
<?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,$no_quote_fields) {
if( $field == 'uid' ) {
throw new Exception ("The function 'update_field' cannot change an id.") ;
}
$array=array();
if( is_array($field) ){
for($i =0; $i<count($field);$i++){
$array[$field[$i]]=$value[$i];
}
}else{
$array=array($field => $value);
}
if ( is_array($id) ){
$GLOBALS['TYPO3_DB']-> exec_UPDATEquery ($table, 'uid IN ('.implode(',',$id).')', $array,$no_quote_fields);
}else{
$GLOBALS['TYPO3_DB']-> exec_UPDATEquery ($table, 'uid='.$id, $array,$no_quote_fields);
}
}
function select_all_in_view($view, $where) {
$queryParts = array(
'SELECT' => '*',
'FROM' => $view,
'WHERE' => $where ,
$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
return ($res);
}
function select_count_in_view($view,$where) {
$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);
function get_project_list_by_group_filter($create_user_id, $user_group_id){
$where="";
$list_project_id_filter_by_group="";
$list_project_id_laboratories="";
if($user_group_id !=""){
#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_id .')',
'GROUPBY' => '',
'ORDERBY' => '',
'LIMIT' => ''
);
$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
while($res_row= $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
if( $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'];
$where .= "uid 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.")";
}
}
$queryParts=array(
'SELECT' => 'uid AS project_id',
'FROM' => 'tx_nG6_project',
'WHERE' => $where,
'GROUPBY' => '',
'ORDERBY' => '',
'LIMIT' => ''
);
$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
if ($list_project_id_laboratories != "") {
$list_project_id_laboratories .=",".$res_row['project_id'];
$list_project_id_laboratories=$res_row['project_id'];
}
}
return ($list_project_id_laboratories);
}
################################
# OBSOLETE / PURGE management
################################
function filter_list_retention_data_info($filter_size, $max_retention_date, $create_user_id, $user_group_id) {
$where = ' DATEDIFF( FROM_UNIXTIME( retention_date ) , now( ) ) <0';
$where .= ' AND purge_demand_id IS NULL ';
$where .= ' AND state IN ( \'stored\', \'extended\') ';
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_id != "") {
$project_list_ids = tx_nG6_db::get_project_list_by_group_filter($create_user_id,$user_group_id);
$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)) {
Gerald Salin
committed
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( "purged" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array()),
"extended" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array()),
"stored" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array())
),
'total_purgeable_size' => $res_row['storage_size'],
'nb_runs' =>0,
if ($res_row['run_id'] != "") {
$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,'10');
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( "purged" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array()),
"extended" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array()),
"stored" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array())
),
'total_purgeable_size' => $res_row['storage_size'],
'nb_runs' =>0,
Gerald Salin
committed
}
if ($res_row['analyze_id'] != ""){
$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'];
}
Gerald Salin
committed
$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( "purged" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array()),
"extended" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array()),
"stored" => array ('nb_run' => 0, 'size_run' => 0, 'run_ids'=> array(),
"nb_analyze"=> 0, "size_analyze"=> 0, 'analysis_ids'=> array())
) ,
'total_purgeable_size' => $res_row['storage_size'],
'nb_runs' =>0,
'nb_analyses' =>0);
$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'], 'user_group_title' => $res_row['user_group_title']));
}
}
return($by_project);
}
/**
* Add purge initial purge demand in db
*
* @param int $project_id project id link to the purge demand
* @param int $total_purgeable_size size of files to purge
* @return New purge id
*/
function add_purge_demand($cruser_id, $project_id, $total_purgeable_size, $all_runs_ids, $all_analyses_ids,$managers_name){
$date= time();
$purge_data = array(
'cruser_id' => $cruser_id,
'project_id' => $project_id,
'analyze_ids' => implode(',',$all_analyses_ids),
'run_ids' => implode(',',$all_runs_ids),
'mail_sent_date' => $date,
'purge_size' => $total_purgeable_size,
'demand_state' => "sent",
'tstamp' => $date,
'crdate' => $date
);
$GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_nG6_purge_demand', $purge_data);
$purge_demand_id = $GLOBALS['TYPO3_DB']->sql_insert_id();
tx_nG6_db::update_field('tx_nG6_run', $all_runs_ids, array('mail_sent_date','purge_demand_id'),array($date,$purge_demand_id));
tx_nG6_db::update_field('tx_nG6_analyze',$all_analyses_ids,array('mail_sent_date','purge_demand_id'),array($date,$purge_demand_id));
foreach($managers_name as $user_name){
$res= tx_nG6_db::select_user_by_username($user_name);
$purge_data_user = array(
'tstamp' => $date,
'crdate' => $date,
'cruser_id' => $cruser_id,
'purge_demand_id' => $purge_demand_id,
'fe_users_id' => $res['uid']
);
$GLOBALS['TYPO3_DB']->exec_INSERTquery('tx_nG6_purge_demand_fe_users', $purge_data_user);
}
return $purge_demand_id;
}
function get_purge_demand_from_id($values){
#select all demand id not processed
$queryParts=array(
'SELECT' => ' uid as demand_id,project_id,purge_size,mail_sent_date,processed_date,demand_state,analyze_ids,run_ids',
'FROM' => 'tx_nG6_purge_demand',
'WHERE' => 'uid IN ('.implode(",",$values).')',
'GROUPBY' => '',
'ORDERBY' => '',
'LIMIT' => ''
);
$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
$result=array();
while($res_row= $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
$result[]=array(
"demand_id"=>$res_row["demand_id"],
"project_id"=>$res_row["project_id"],
"purge_size"=>$res_row["purge_size"],
"mail_sent_date"=>$res_row["mail_sent_date"],
"processed_date"=>$res_row["processed_date"],
"demand_state"=>$res_row["demand_state"],
"analyze_ids"=>$res_row["analyze_ids"],
"run_ids"=>$res_row["run_ids"]
);
}
return($result);
}
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
function get_purge_demand_list($delay_in_days){
#select all demand id not processed
$queryParts=array(
'SELECT' => 'tx_nG6_purge_demand.uid AS demand_id, `tx_nG6_purge_demand`.project_id AS project_id, tx_nG6_project.name as project_name,
`tx_nG6_purge_demand`.purge_size AS purge_size, `tx_nG6_purge_demand`.mail_sent_date, `tx_nG6_purge_demand`.demand_state,
`fe_users`.username, `fe_groups`.title AS user_group_title,
DATEDIFF( FROM_UNIXTIME( `tx_nG6_purge_demand`.mail_sent_date ) , now( ) ) >'.$delay_in_days.' AS delay_excedeed',
'FROM' => '`tx_nG6_purge_demand`
INNER JOIN tx_nG6_project ON tx_nG6_project.uid = tx_nG6_purge_demand.project_id
INNER JOIN `tx_nG6_purge_demand_fe_users` ON `tx_nG6_purge_demand_fe_users`.`purge_demand_id` = tx_nG6_purge_demand.uid
INNER JOIN fe_users ON fe_users.uid = `tx_nG6_purge_demand_fe_users`.`fe_users_id`
INNER JOIN fe_groups ON fe_groups.uid = fe_users.usergroup',
'WHERE' => '`tx_nG6_purge_demand`.processed_date IS NULL',
'GROUPBY' => '',
'ORDERBY' => '',
'LIMIT' => ''
);
$results=array();
$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
while($res_row= $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
if ( array_key_exists($res_row["demand_id"] , $results)){
$results[$res_row["demand_id"]]["users"][]=array("username"=>$res_row["username"], "user_group_title"=>$res_row["user_group_title"]);
} else {
$results[$res_row["demand_id"]]=array("project_id"=>$res_row["project_id"],
"purge_size"=>$res_row["purge_size"],
"mail_sent_date"=>date("d/m/Y",$res_row["mail_sent_date"]),
"project_name"=>$res_row["project_name"],
"demand_state"=>$res_row["demand_state"],
"delay_excedeed"=>$res_row["delay_excedeed"],
"users"=>array(array("username"=>$res_row["username"], "user_group_title"=>$res_row["user_group_title"]))
);
}
}
return ($results);
}
/**
* 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.uid as group_id , 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.uid as group_id , 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,
'group_id' => $res_row['group_id'],
'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 ';
'SELECT' => 'tx_nG6_project.crdate, count(tx_nG6_project.crdate) nb',
'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"]);
}
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") {
.' 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;
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;
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
/**
* 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
*------------------------------------------------------------*/
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
/**
* 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
* @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
'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.hidden AS project_hidden ',
'FROM' => 'tx_nG6_project INNER JOIN fe_rights ON fe_rights.project_id=tx_nG6_project.uid',
'WHERE' => $where,
'GROUPBY' => '',
$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
$project_id = $row['project_id'];
$projects['project_'.$project_id] = array(
'id' => $project_id,
'name' => $row['project_name'],
'description' => $row['project_description']);
}
return $projects;
}
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
/**
* 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'],
'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 ',
'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 ',
// 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(
'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'],