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

use nG6\Controller\tx_nG6_db;
/**
 * Class_nG6_db' for the 'nG6' extension.
 * Allows to migrate usergroups system to (users) rights system.
 *
 * @author	PF bioinformatique de Toulouse <>
 */
class tx_nG6_upgrade {
	
	/**
	 * Main function for upgrade.
	 * 
	 * @param string $version the version 
	 */
	function upgrade($version, $data_storage){
		// version 1.2 : users rights' system 
		if($version == '1.2'){
			tx_nG6_upgrade::upgrade_1_2();
			return "Upgrade successfuly ended to version 1.2!";
		} else if($version == '2.0'){
			tx_nG6_upgrade::upgrade_2_0($data_storage);
			return "Upgrade successfuly ended to version 2.0!";
		} else {
			return "Upgrading to version " . $version . " not suported!";
		}
	}
	
	
	/*
	 * version 2.0
	 *------------------------------------------------------------*/
	
	/**
	 * Main function to upgrade to version 2.0.
	 */
	function upgrade_2_0($data_storage){
		// Update cruser_id of tx_nG6_project
		$queryUpdateCruseridProject = "
				UPDATE tx_nG6_project p 
				SET p.cruser_id = (
				                SELECT u.uid FROM fe_users u , fe_rights r 
				                WHERE r.fe_user_id = u.uid 
				                    AND r.right_id = 2 
				                    AND r.project_id = p.uid 
				                HAVING MIN(u.tstamp)
				                ) 
				WHERE p.cruser_id = 0" ;
		
		// Update cruser_id for tx_nG6_run table
		$queryUpdateCruseridRun = "
				UPDATE tx_nG6_run ru
				SET ru.cruser_id = (
						SELECT p.cruser_id FROM tx_nG6_project p , tx_nG6_project_run pr
						WHERE p.uid = pr.project_id
						AND pr.run_id=ru.uid
					)
				WHERE ru.cruser_id = 0	";
		
		// Update cruser_id for tx_nG6_analysis linked to a run
		$queryUpdateCruseridAnalysisRun = "
				UPDATE tx_nG6_analyze a
				SET a.cruser_id = (
						SELECT r.cruser_id FROM tx_nG6_run r , tx_nG6_run_analyze ra
						WHERE a.uid = ra.analyze_id
						AND ra.run_id = r.uid
					)
				WHERE a.cruser_id = 0";
		
		// Update cruser_id for tx_nG6_analysis linked to a project
		$queryUpdateCruseridAnalysisProject = "
				UPDATE tx_nG6_analyze a
				SET a.cruser_id = (
					SELECT p.cruser_id FROM tx_nG6_project p , tx_nG6_project_analyze pa
					WHERE a.uid = pa.analyze_id
					AND pa.project_id = p.uid
					)
				WHERE a.cruser_id = 0";
		
		// Alter table fe_groups and add two columns tx_nG6_location and tx_nG6_organism
		$queryAlterFeGroups = "
				ALTER TABLE fe_groups ADD tx_nG6_organism VARCHAR( 50 ) NOT NULL AFTER title ,
				ADD tx_nG6_location VARCHAR( 50 ) NOT NULL AFTER tx_nG6_organism
			" ;
		
		// Alter tx_nG6_analyze and add is_editable
		$queryAlterTxNG6Analayze = "
				ALTER TABLE tx_nG6_analyze 
				ADD is_editable boolean NOT NULL DEFAULT FALSE AFTER params 
			";		
		
		// Alter tx_nG6_analyze and add storage_size
		$queryAlterTxNG6AnalayzeSsize = "
		ALTER TABLE tx_nG6_analyze
		ADD storage_size bigint(20) NOT NULL DEFAULT '0' AFTER is_editable
		";
		
		// Alter tx_nG6_run and add storage_size
		$queryAlterTxNG6Run = "
		ALTER TABLE tx_nG6_run
		ADD storage_size bigint(20) NOT NULL DEFAULT '0' AFTER sequencer
		";
		
		/*
		 * Execute all queries
		 */
		$GLOBALS['TYPO3_DB']->sql_query($queryUpdateCruseridProject);
		$GLOBALS['TYPO3_DB']->sql_query($queryUpdateCruseridRun);
		$GLOBALS['TYPO3_DB']->sql_query($queryUpdateCruseridAnalysisRun);
		$GLOBALS['TYPO3_DB']->sql_query($queryUpdateCruseridAnalysisProject);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterFeGroups);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6Analayze);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6AnalayzeSsize);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6Run);
		
		// finaly update storage fields
		tx_nG6_upgrade::set_storage_size($data_storage);
		
		// add comment support
		tx_nG6_upgrade::add_comment_support();
		
		// add sample support
		tx_nG6_upgrade::upgrade_samples();
	}
	
	function set_storage_size($data_storage) {
		// update run storage size
		$queryParts = array(
				'SELECT' 	=> 'tx_nG6_run.uid as uid, tx_nG6_run.directory as directory',
				'FROM' 		=> 'tx_nG6_run ',
				'WHERE' 	=> "",
				'GROUPBY' 	=> '',
				'ORDERBY' 	=> '',
				'LIMIT' 	=> ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		$current_size = 0;
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$current_size = tx_nG6_upgrade::get_directory_size($data_storage.$res_row['directory']);
			$GLOBALS['TYPO3_DB']-> exec_UPDATEquery('tx_nG6_run', 'uid='.$res_row['uid'], array('storage_size' => strval($current_size)));
		}
		
		// update analysis storage size
		$queryParts = array(
				'SELECT' 	=> 'tx_nG6_analyze.uid as uid, tx_nG6_analyze.directory as directory',
				'FROM' 		=> 'tx_nG6_analyze ',
				'WHERE' 	=> "",
				'GROUPBY' 	=> '',
				'ORDERBY' 	=> '',
				'LIMIT' 	=> ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		$current_size = 0;
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$current_size = tx_nG6_upgrade::get_directory_size($data_storage.$res_row['directory']);
			$GLOBALS['TYPO3_DB']-> exec_UPDATEquery('tx_nG6_analyze', 'uid='.$res_row['uid'], array('storage_size' => strval($current_size)));
		}
		
	}
	
	function get_directory_size($data_folder) {
		$full_size = 0;
		foreach(scandir($data_folder) as $file) {
			if (is_file($data_folder."/".$file)) {
				$full_size += filesize($data_folder."/".$file);
			}
		}
		return $full_size;
	}
	
	function add_comment_support(){
	
		// Create comment table
		$queryCreatecomment = "
			CREATE TABLE IF NOT EXISTS `tx_nG6_comment` (
			`uid` int(11) NOT NULL AUTO_INCREMENT,
			`pid` int(11) NOT NULL DEFAULT '0',
			`tstamp` int(11) NOT NULL DEFAULT '0',
			`crdate` int(11) NOT NULL DEFAULT '0',
			`cruser_id` int(11) NOT NULL DEFAULT '0',
			`deleted` tinyint(4) NOT NULL DEFAULT '0',
			`hidden` tinyint(4) NOT NULL DEFAULT '0',
			`comment` text NOT NULL,
			PRIMARY KEY (`uid`),
			KEY `parent` (`pid`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
		";
	
		// Create project_comment table
		$queryCreateProjectcomment = "
				CREATE TABLE IF NOT EXISTS `tx_nG6_project_comment` (
				`uid` int(11) NOT NULL AUTO_INCREMENT,
				`pid` int(11) NOT NULL DEFAULT '0',
				`tstamp` int(11) NOT NULL DEFAULT '0',
				`crdate` int(11) NOT NULL DEFAULT '0',
				`cruser_id` int(11) NOT NULL DEFAULT '0',
				`deleted` tinyint(4) NOT NULL DEFAULT '0',
				`hidden` tinyint(4) NOT NULL DEFAULT '0',
				`project_id` int(11) NOT NULL DEFAULT '0',
				`comment_id` int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (`uid`),
				KEY `parent` (`pid`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
		";
	
		// Create run_comment table
		$queryCreateRuncomment = "
				CREATE TABLE IF NOT EXISTS `tx_nG6_run_comment` (
				`uid` int(11) NOT NULL AUTO_INCREMENT,
				`pid` int(11) NOT NULL DEFAULT '0',
				`tstamp` int(11) NOT NULL DEFAULT '0',
				`crdate` int(11) NOT NULL DEFAULT '0',
				`cruser_id` int(11) NOT NULL DEFAULT '0',
				`deleted` tinyint(4) NOT NULL DEFAULT '0',
				`hidden` tinyint(4) NOT NULL DEFAULT '0',
				`run_id` int(11) NOT NULL DEFAULT '0',
				`comment_id` int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (`uid`),
				KEY `parent` (`pid`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
		";
	
		// Create analyze_comment table
		$queryCreateAnalyzecomment = "
				CREATE TABLE IF NOT EXISTS `tx_nG6_analyze_comment` (
				`uid` int(11) NOT NULL AUTO_INCREMENT,
				`pid` int(11) NOT NULL DEFAULT '0',
				`tstamp` int(11) NOT NULL DEFAULT '0',
				`crdate` int(11) NOT NULL DEFAULT '0',
				`cruser_id` int(11) NOT NULL DEFAULT '0',
				`deleted` tinyint(4) NOT NULL DEFAULT '0',
				`hidden` tinyint(4) NOT NULL DEFAULT '0',
				`analyze_id` int(11) NOT NULL DEFAULT '0',
				`comment_id` int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (`uid`),
				KEY `parent` (`pid`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
		";
	
		/*
		 * Execute all queries
		*/
		$GLOBALS['TYPO3_DB']->sql_query($queryCreatecomment);
		$GLOBALS['TYPO3_DB']->sql_query($queryCreateProjectcomment);
		$GLOBALS['TYPO3_DB']->sql_query($queryCreateRuncomment);
		$GLOBALS['TYPO3_DB']->sql_query($queryCreateAnalyzecomment);
	}
	
	
	
	function upgrade_samples(){
		
		// Alter tx_nG6_sample change mid to sample_id
		$queryAlterTxNG6SampleMID = "
				ALTER TABLE tx_nG6_sample 
				CHANGE mid sample_id VARCHAR(255) NOT NULL DEFAULT ''
		";
		
		// Alter tx_nG6_sample description to name
		$queryAlterTxNG6SampleDescription = "
				ALTER TABLE tx_nG6_sample
				CHANGE description  name VARCHAR(255) NOT NULL DEFAULT ''
		";
		
		// Add column description, reads1, reads2, type, insert_size, species, nb_sequences
		$queryAlterTxNG6SampleAddColumns = "
				ALTER TABLE tx_nG6_sample
				ADD `description` varchar(255) NOT NULL DEFAULT '' AFTER name ,
				ADD `reads1` text AFTER description,
				ADD `reads2` text AFTER reads1,
				ADD `type` varchar(255) NOT NULL DEFAULT '' AFTER reads2,
				ADD `insert_size` int(11) NOT NULL DEFAULT '0' AFTER type,
				ADD `species` varchar(255) NOT NULL DEFAULT '' AFTER insert_size,
				ADD `nb_sequences` int(11) NOT NULL DEFAULT '0' AFTER species,
				ADD `full_seq_size` int(11) NOT NULL DEFAULT '0' AFTER nb_sequences
		";

		/*
		 * Execute all queries
		*/
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6SampleMID);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6SampleDescription);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6SampleAddColumns);
	}
	
	/*
	 * version 1.2
	 *------------------------------------------------------------*/
	
	/**
	 * Main function to upgrade to version 1.2.
	 */
	function upgrade_1_2(){

		$queryParts = array(
			'SELECT' => 'uid, fe_group',
			'FROM' => 'tx_nG6_project',
			'WHERE' => '',
			'GROUPBY' => '',
			'ORDERBY' => '',
			'LIMIT' => ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$project_id = $row['uid'];
			$current_group = $row['fe_group'];
			// users in the current group
			$group_users = tx_nG6_upgrade::get_all_users_in_group($current_group);

			// set current group users' right to "member"
			foreach($group_users as $id => $user_id){
				tx_nG6_upgrade::assign_right($user_id, $project_id, 0);
			}
			//assign rights to the super groups users for the specified project id
			tx_nG6_upgrade::assign_super_groups_rights($current_group, $project_id);
		}
		
	}
	
	
	
	/**
	 * Assign the 'administrator' right to all users found in the supergroups of 'basegroup'.
	 * 
	 * @param int $base_group	the base group id
	 * @param int $project_id	the project id
	 */
	function assign_super_groups_rights($base_group, $project_id){		
		$super_groups_ids = tx_nG6_upgrade::get_all_super_groups_of($base_group);
		foreach($super_groups_ids as $id => $group_id){
			$group_users = tx_nG6_upgrade::get_all_users_in_group($group_id);
			// super users = admin
			foreach($group_users as $id => $user_id){
				tx_nG6_upgrade::assign_right($user_id, $project_id, 2);
			}
		}
	}
	
	
	/**
	 * Assign a user right to a specific project.
	 * 
	 * @param int $user_id		the user id
	 * @param int $project_id	the project id
	 * @param int $right		the right id (0=member, 1=manager, 2=admininistrator)
	 */
	function assign_right($user_id, $project_id, $right){
		$assign_right = Array(
    		'fe_user_id' 	=> $user_id,
    		'project_id' 	=> $project_id,
    		'right_id'		=> $right
		);
		$GLOBALS['TYPO3_DB']->exec_INSERTquery('fe_rights', $assign_right);
	}	
	
	
	/**
	 * Return all users id from a usergroup. 
	 * 
	 * @param int $group_id the usergroup
	 */
	function get_all_users_in_group($group_id){
		$users = array();
		$queryParts = array(
			'SELECT' => 'uid',
			'FROM' => 'fe_users',
			'WHERE' => 'usergroup='.$group_id,
			'GROUPBY' => '',
			'ORDERBY' => '',
			'LIMIT' => ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {			
			$users[] = $row['uid'];
		}
		return $users;
	}
	
	
	
    /**
    * Return the supergroups list of the specified group.
    * 
    * @param string	$group	the group
    * @return array
    */ 
	function get_super_groups_of ($group) {
		// First get the super group of the given group
		$queryParts = array(
			'SELECT' => 'uid, subgroup',
			'FROM' => 'fe_groups',
			'WHERE' => '',
			'GROUPBY' => '',
			'ORDERBY' => '',
			'LIMIT' => ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		$reslist = array();
		$i = 0;
		
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {			
			if (in_array($group, \TYPO3\CMS\Core\Utility\GeneralUtility::intExplode(',',$row['subgroup']))) {
				$reslist[$i] = $row['uid'];
				$i++;
			}
		}
 		return $reslist;
	}
    
	/**
     * Return the recursive supergroups list of the specified group
     *
     * @param string $group the group id
     * @return array
     */
    function get_all_super_groups_of ($group) {
    	// return the recursive list of all $group super groups
    	$first_list = tx_nG6_upgrade::get_super_groups_of($group);
    	$super_list = $first_list;    	
    	foreach ($first_list as $first_list_group_id) {
    		$next_list = tx_nG6_upgrade::get_all_super_groups_of($first_list_group_id, $level);
    		$super_list = array_merge($super_list,$next_list);
    	}
    	return $super_list;
    }
	
}

if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/nG6/class.tx_nG6_upgrade.php']) {
    include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/nG6/class.tx_nG6_upgrade.php']);
}

?>