<?php
/**
 * themes to levels migration happened in 1.9 version
 * this file poupalte the new table l21_level from l21_sd_theme and l21_theme tables
 */
// we get old Finalities / SDTheme data
// we ignore errors, so it will return false if the table does not exists
$query = "SELECT * from l21_sd_theme ORDER BY sdtheme_range ASC, sdtheme_id ASC;";
$sdthemes = $sql_object->DBSelect ( $query, 'ASSOC', true );

// we increment of 100 to prevent an id to override an other when updating
// we also increment all dependent tables : project / news / workshop / publication, indicator / directory /yp
$update = "UPDATE l21_theme  SET theme_id = (100 + theme_id)";
$result = $sql_object->DBQuery ( $update );
$update = "UPDATE l21_project  SET project_level_id = (100 + project_level_id)";
$result = $sql_object->DBQuery ( $update );
$update = "UPDATE l21_news  SET news_level = (100 + news_level)";
$result = $sql_object->DBQuery ( $update );
$update = "UPDATE l21_workshop  SET workshop_level = (100 + workshop_level)";
$result = $sql_object->DBQuery ( $update );
$update = "UPDATE l21_publication  SET publi_level = (100 + publi_level)";
$result = $sql_object->DBQuery ( $update );
$update = "UPDATE l21_sdi_info  SET sdii_level = (100 + sdii_level)";
$result = $sql_object->DBQuery ( $update );
$update = "UPDATE l21_yellowpages  SET yellowp_level = (100 + yellowp_level)";
$result = $sql_object->DBQuery ( $update );

// we get old themes data
// we ignore errors, so it will return false if the table does not exists
$query = "SELECT * FROM l21_theme AS T LEFT OUTER JOIN " . T_SD_THEME . " AS SDT ON SDT.sdtheme_id=T.theme_sd_theme ORDER BY sdtheme_range ASC, sdtheme_name ASC, theme_name ASC;";
$themes = $sql_object->DBSelect ( $query, 'ASSOC', true );

$ids = array (); // sdthemes -> new level ids
                
// Select from l21_level to see if it is populated already
$query = 'SELECT * FROM l21_level;';
$levels = $sql_object->DBSelect ( $query, 'ASSOC', false );

// If l21_sd_theme and l21_theme still exists
// we execute the migration
if ($sdthemes !== false && $themes !== false) {
	
	// we test if table level is still the old one (< v 1.8)
	if (isset ( $levels [0] ['level_visual_identity'] )) {
		$sql_object->DBQuery ( 'DROP TABLE l21_level;' );
		$sql_object->DBQuery ( "CREATE TABLE `l21_level` (  `level_id` tinyint(4) unsigned NOT NULL AUTO_INCREMENT,  `level_name` varchar(200) NOT NULL,  `level_code` varchar(100) NOT NULL,  `level_label` varchar(255) NOT NULL,  `level_parent` tinyint(3) unsigned NOT NULL DEFAULT '0',  `level_desc_project` text NOT NULL, `level_desc_dashboard` text NOT NULL,  `level_desc_publication` text NOT NULL,  `level_desc_news` text NOT NULL,  `level_desc_workshop` text NOT NULL,  `level_range` tinyint(4) unsigned NOT NULL DEFAULT '0',  `level_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `level_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `level_status` enum('P','D','E') NOT NULL DEFAULT 'P',  PRIMARY KEY (`level_id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;" );
	}
	
	// level table is still empty. We have to migrate
	// ELSE WE DON'T DO ANYTHING
	if (! isset ( $levels [0] ['level_name'] )) {
		if ($verbose) echo "<p>   ... Starting migration from l21_sdtheme / l21_theme tables to l21_level</p>";
		
		// we populate the new table with old Finalities / SDTheme data from l21_sd_theme
		for($i = 0; $i < count ( $sdthemes ); $i ++) {
			$sdthemes [$i] = $sql_object->DBescape ( $sdthemes [$i] );
			$q = "INSERT INTO l21_level(level_name, level_label, level_parent, level_desc_project, level_desc_dashboard,	level_desc_publication,	level_desc_news,	level_desc_workshop, level_range,	level_date_crea, level_last_modify , level_status) VALUES('" . $sdthemes [$i] ['sdtheme_name'] . "', 'Finalité', 0, '" . $sdthemes [$i] ['sdtheme_description'] . "', '" . $sdthemes [$i] ['sdtheme_description'] . "', '" . $sdthemes [$i] ['sdtheme_description'] . "', '" . $sdthemes [$i] ['sdtheme_description'] . "', '" . $sdthemes [$i] ['sdtheme_description'] . "', " . $sdthemes [$i] ['sdtheme_range'] . ", '" . $sdthemes [$i] ['sdtheme_date_crea'] . "', '" . $sdthemes [$i] ['sdtheme_last_modify'] . "', 'P')";
			logfile ( '../logs/themes-to-levels.migration.log', array ('db-operation',	$q ));
			$last_id = $sql_object->DBInsert ( $q, true );
			
			if ($verbose) echo "<p>   ... inserting Finality " . $sdthemes [$i] ['sdtheme_name'] . " in l21_level (new ID : " . $last_id . " / old ID " . $sdthemes [$i] ['sdtheme_id'] . ") </p>";
				
			// IDs correspondance
			$ids [$sdthemes [$i] ['sdtheme_id']] = $last_id;
		}
		
		// we populate the new table with old themes data from l21_theme
		// at the same time we update dependencies
		for($i = 0; $i < count ( $themes ); $i ++) {
			
			$themes [$i] = $sql_object->DBescape ( $themes [$i] );
			$q = "INSERT INTO l21_level(level_name, level_label, level_parent, level_desc_project, level_desc_dashboard,	level_desc_publication,	level_desc_news,	level_desc_workshop, level_range,	level_date_crea, level_last_modify , level_status) VALUES('" . $themes [$i] ['theme_name'] . "', 'Orientation', " . $ids [$themes [$i] ['theme_sd_theme']] . ", '" . $themes [$i] ['theme_desc_project'] . "', '" . $themes [$i] ['theme_desc_dashboard'] . "', '" . $themes [$i] ['theme_desc_publication'] . "', '" . $themes [$i] ['theme_desc_news'] . "', '" . $themes [$i] ['theme_desc_workshop'] . "', " . $themes [$i] ['theme_range_project'] . ", '" . $themes [$i] ['theme_date_crea'] . "', '" . $themes [$i] ['theme_last_modify'] . "', '" . $themes [$i] ['theme_statut'] . "')";
			logfile ( '../logs/themes-to-levels.migration.log', array ('db-operation',	$q ));
			$last_id = $sql_object->DBInsert ( $q, true );
			
			if ($verbose) echo "<p>   ... operating on Theme " . $themes [$i] ['theme_name'] . " (new ID : " . $last_id . " old ID : " . $themes [$i] ['theme_id'] . ") </p>";
				
			// we finally update all others content : project / news / workshop / publication, indicator / directory / yellowpages
			$q = sprintf ( "UPDATE l21_project set project_level_id=%s WHERE project_level_id=%s;", $last_id, $themes [$i] ['theme_id'] );
			$sql_object->DBQuery ( $q );
			logfile ( '../logs/themes-to-levels.migration.log', array ('db-operation',	$q ));
			
			// if($verbose) echo "<p style=\"color:blue\">".$q."</p>";
			
			// we finally update all others content : project / news / workshop / publication, indicator / directory / yellowpages
			$q = sprintf ( "UPDATE l21_news set news_level=%s WHERE news_level=%s;", $last_id, $themes [$i] ['theme_id'] );
			$sql_object->DBQuery ( $q );
			logfile ( '../logs/themes-to-levels.migration.log', array ('db-operation',	$q ));
			
			// we finally update all others content : project / news / workshop / publication, indicator / directory / yellowpages
			$q = sprintf ( "UPDATE l21_workshop set workshop_level=%s WHERE workshop_level=%s;", $last_id, $themes [$i] ['theme_id'] );
			$sql_object->DBQuery ( $q );
			logfile ( '../logs/themes-to-levels.migration.log', array ('db-operation',	$q ));
			
			// we finally update all others content : project / news / workshop / publication, indicator / directory / yellowpages
			$q = sprintf ( "UPDATE l21_publication set publi_level=%s WHERE publi_level=%s;", $last_id, $themes [$i] ['theme_id'] );
			$sql_object->DBQuery ( $q );
			logfile ( '../logs/themes-to-levels.migration.log', array ('db-operation',	$q ));
			
			// we finally update all others content : project / news / workshop / publication, indicator / directory / yellowpages
			$q = sprintf ( "UPDATE l21_sdi_info set sdii_level=%s WHERE sdii_level=%s;", $last_id, $themes [$i] ['theme_id'] );
			$sql_object->DBQuery ( $q );
			logfile ( '../logs/themes-to-levels.migration.log', array ('db-operation',	$q ));
			
			// we finally update all others content : project / news / workshop / publication, indicator / directory / yellowpages
			$q = sprintf ( "UPDATE l21_yellowpages set yellowp_level=%s WHERE yellowp_level=%s;", $last_id, $themes [$i] ['theme_id'] );
			$sql_object->DBQuery ( $q );
			logfile ( '../logs/themes-to-levels.migration.log', array ('db-operation',	$q ));
		}
	} else {
		if ($verbose) echo "<p style=\"font-weight:bold\">   ... We don't do anything. Update is already done (`l21_level` table is populated) !</p>";
	}
}
// we finally restor original values to theme_id
$update = "UPDATE l21_theme  SET theme_id = (theme_id - 100)";
$result = $sql_object->DBQuery ( $update );


// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// UPDATE FROM VERSION 1.8
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@unlink('../class/class.theme.php');
@SureRemoveDir('../class/system/pChart/', true);
@SureRemoveDir('../class/system/tcpdf/', true);
@SureRemoveDir('../theme/', true);
@unlink('../dashboard/rap.php');
@unlink('../export/phpsql.process.php');
@unlink('../languages/fr/lang_theme.utf-8.php');
@unlink('../languages/es/lang_theme.utf-8.php');
@unlink('../languages/en/lang_theme.utf-8.php');
@unlink('../languages/it/lang_theme.utf-8.php');
@SureRemoveDir('../lib/js/jquery-ui/', true);
@SureRemoveDir('../lib/js/jquery-ui-multiselect/', true);
@SureRemoveDir('../lib/js/jquery.datePicker.l10n/', true);
@SureRemoveDir('../lib/js/jquery.textboxList/', true);
@SureRemoveDir('../lib/js/nicEdit/', true);
@SureRemoveDir('../lib/js/timeline/', true);
@SureRemoveDir('../lib/vendor/jQuery-File-Upload/', true);
@unlink('../project/timeline-xml.php');
@unlink('../lib/js/jquery.min.js');
@unlink('../lib/js/timeline.js');
// @unlink('../install/scripts_sql/patches/patch_1.2.mysql.en.sql');
// @unlink('../install/scripts_sql/patches/patch_1.2.mysql.es.sql');
// @unlink('../install/scripts_sql/patches/patch_1.2.mysql.fr.sql');
// @unlink('../install/scripts_sql/patches/patch_1.2.mysql.it.sql');


// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// Generics actions : Finally we update config.ini and add new constants if necessary
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

// we parse the current config.ini file (before update)
$config_ini = parse_ini_file("../config/config.ini");

// we parse the new ini file to check if keys exists
$default_ini = parse_ini_file("../config/config.ini.default");

// we switch to new admin template
$config_ini['THEME_ADMIN'] = '2015';

// we convert from bytes to MB
$config_ini['MEDIA_ALLOWED_SIZE'] = ''. number_format($config_ini['MEDIA_ALLOWED_SIZE'] / 1048576, 0). '';


$l21config->setParams($config_ini);

// we add MOD_LEVEL constant if needed
if(!isset($config_ini['MOD_LEVEL'])) $l21config->addParams(array(array('MODULES_SETTINGS', 'MOD_LEVEL', 1)));

// we add LOG_MAINFILE constant if needed
if(!isset($config_ini['LOG_MAINFILE'])) $l21config->addParams(array(array('LOGS_SETTINGS', 'LOG_MAINFILE', "../logs/main.log")));

// we add ANIMATE_PROGRESS constant if needed
if(!isset($config_ini['ANIMATE_PROGRESS'])) $l21config->addParams(array(array('MISC_HIDDEN_SETTINGS', 'ANIMATE_PROGRESS', 1)));

// we add CLIENT_CHARTS constant if needed
if(!isset($config_ini['CLIENT_CHARTS'])) $l21config->addParams(array(array('SYSTEM_SETTINGS', 'CLIENT_CHARTS', 1)));

// we add SEARCH_MODE constant if needed
if(!isset($config_ini['SEARCH_MODE'])) $l21config->addParams(array(array('MISC_HIDDEN_SETTINGS', 'SEARCH_MODE', "IN BOOLEAN MODE")));

// we add THUMBNAIL_SIZE constant if needed
if(!isset($config_ini['THUMBNAIL_SIZE'])) $l21config->addParams(array(array('MISC_HIDDEN_SETTINGS', 'THUMBNAIL_SIZE', "180x134")));

// we add BREADCRUMB_CONTAINER constant if needed
if(!isset($config_ini['BREADCRUMB_CONTAINER'])) $l21config->addParams(array(array('MISC_HIDDEN_SETTINGS', 'BREADCRUMB_CONTAINER', "id=main-container")));

// we delete MOD_THEME constant
if(isset($config_ini['MOD_THEME'])) $l21config->rmParams(array(array('MODULES_SETTINGS', 'MOD_THEME')));

// we delete TIMELINE_DISPLAY constant
if(isset($config_ini['TIMELINE_DISPLAY'])) $l21config->rmParams(array(array('SYSTEM_SETTINGS', 'TIMELINE_DISPLAY')));

// we delete TIMELINE_HEIGHTconstant
if(isset($config_ini['TIMELINE_DISPLAY'])) $l21config->rmParams(array(array('MISC_HIDDEN_SETTINGS', 'TIMELINE_HEIGHT')));

// we write the new file
if($l21config->writeReleaseParams()) {
	echo "<p><strong>Writing new configuration file : /config/config.ini</strong></p>";
} else {
	echo "<p><strong>Problem writing new configuration file : /config/config.ini</strong></p>";
}

?>