<?php
	// check this file's MD5 to make sure it wasn't called before
	$prevMD5=@implode('', @file(dirname(__FILE__).'/setup.md5'));
	$thisMD5=md5(@implode('', @file("./updateDB.php")));
	if($thisMD5==$prevMD5) {
		$setupAlreadyRun=true;
	}else{
		// set up tables
		if(!isset($silent)) {
			$silent=true;
		}

		// set up tables
		setupTable('main', "create table if not exists `main` (   `idmain` INT unsigned not null auto_increment , primary key (`idmain`), `main` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('dossier_individuel', "create table if not exists `dossier_individuel` (   `iddindividuel` INT unsigned not null auto_increment , primary key (`iddindividuel`), `option_prime` INT unsigned null , `matricule` VARCHAR(255) null , unique `matricule_unique` (`matricule`), `nom_prenom` VARCHAR(255) null , `date_naissance` DATE null , `age` INT null , `matrimoniale` VARCHAR(255) null , `enfants` INT null , `nni` VARCHAR(255) null , unique `nni_unique` (`nni`), `telephone` VARCHAR(255) null , `courriel` VARCHAR(255) null , `postale` VARCHAR(255) null , `date_recrutement` DATE null , `statut` INT unsigned null , `direction` INT unsigned null , `fonction` INT unsigned null , `service` INT unsigned null , `division` INT unsigned null , `categorie` INT unsigned null , `anciennete` INT null , `salaire` DECIMAL(10,0) null default '0' , `prime` INT unsigned null , `qty` INT unsigned null , `total` DECIMAL(10,0) null , `compte` VARCHAR(255) null , `banque` INT unsigned null , `cv` VARCHAR(255) null , `dossier` VARCHAR(255) null , `contrat` VARCHAR(255) null , `photo` VARCHAR(40) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('dossier_individuel', array('option_prime','statut','direction','fonction','service','division','categorie','banque'));
		setupTable('gestion_primes', "create table if not exists `gestion_primes` (   `idgestion_prime` INT unsigned not null auto_increment , primary key (`idgestion_prime`), `echeance` INT unsigned null , `date` DATE null , `mois` VARCHAR(255) null , `signataire` VARCHAR(255) null , `link` VARCHAR(255) null default 'VALIDE' ) CHARSET utf8", $silent);
		setupIndexes('gestion_primes', array('echeance'));
		setupTable('permissions', "create table if not exists `permissions` (   `id_permission` INT(3) unsigned zerofill not null auto_increment , primary key (`id_permission`), `numero` VARCHAR(255) null , `date` DATE null , `nom_prenom` INT unsigned null , `matricule` INT unsigned null , `fonction` INT unsigned null , `direction` INT unsigned null , `service` INT unsigned null , `division` INT unsigned null , `num_fiche` VARCHAR(255) null , `nature` VARCHAR(255) null , `date_debut` DATE null , `date_fin` DATE null , `jours` INT null , `directeur` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('permissions', array('nom_prenom'));
		setupTable('conges', "create table if not exists `conges` (   `id_conge` INT(3) unsigned zerofill not null auto_increment , primary key (`id_conge`), `numero` VARCHAR(255) null , `date` DATE null , `nom_prenom` INT unsigned null , `matricule` INT unsigned null , `fonction` INT unsigned null , `direction` INT unsigned null , `service` INT unsigned null , `num_fiche` VARCHAR(255) null , `nature` VARCHAR(255) null , `date_debut` DATE null , `date_fin` DATE null , `jours` INT null , `directeur` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('conges', array('nom_prenom'));
		setupTable('pointages', "create table if not exists `pointages` (   `id_pointages` INT unsigned not null auto_increment , primary key (`id_pointages`), `to_fiche_pointage` VARCHAR(255) null , `structure` INT unsigned null , `mois` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('pointages', array('structure'));
		setupTable('fiche_pointages', "create table if not exists `fiche_pointages` (   `id_fiche_pointage` INT unsigned not null auto_increment , primary key (`id_fiche_pointage`), `from_pointage` INT unsigned null , `date_fiche_pointage` DATE null , `nom_prenom_fp` INT unsigned null , `fonction_fp` INT unsigned null , `nni_fp` INT unsigned null , `heure_arrive` TIME null , `heure_depart` TIME null , `emargement` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('fiche_pointages', array('from_pointage','nom_prenom_fp'));
		setupTable('missions', "create table if not exists `missions` (   `id_mission` INT unsigned not null auto_increment , primary key (`id_mission`), `to_mission` VARCHAR(255) null , `date` DATE null , `directeur` VARCHAR(255) null , `destination` VARCHAR(255) null , `motif` VARCHAR(255) null , `date_depart` DATE null , `date_retour` DATE null , `moyen` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('ordres_missions', "create table if not exists `ordres_missions` (   `id_ordre_mission` INT unsigned not null auto_increment , primary key (`id_ordre_mission`), `from_mission` INT unsigned null , `nom_prenom` INT unsigned null , `fonction` INT unsigned null , `direction` INT unsigned null , `service` INT unsigned null ) CHARSET utf8", $silent);
		setupIndexes('ordres_missions', array('from_mission','nom_prenom'));
		setupTable('fonctions', "create table if not exists `fonctions` (   `idfonction` INT unsigned not null auto_increment , primary key (`idfonction`), `fonction` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('directions', "create table if not exists `directions` (   `iddirection` INT unsigned not null auto_increment , primary key (`iddirection`), `direction` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('services', "create table if not exists `services` (   `idservice` INT unsigned not null auto_increment , primary key (`idservice`), `direction` INT unsigned null , `service` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('services', array('direction'));
		setupTable('divisions', "create table if not exists `divisions` (   `iddivision` INT unsigned not null auto_increment , primary key (`iddivision`), `service` INT unsigned null , `division` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('divisions', array('service'));
		setupTable('categories', "create table if not exists `categories` (   `idcategorie` INT unsigned not null auto_increment , primary key (`idcategorie`), `categorie` VARCHAR(255) null , `prime` DECIMAL(10,0) null default '0' ) CHARSET utf8", $silent);
		setupTable('statuts', "create table if not exists `statuts` (   `idstatut` INT unsigned not null auto_increment , primary key (`idstatut`), `statut` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('banques', "create table if not exists `banques` (   `idbanque` INT unsigned not null auto_increment , primary key (`idbanque`), `banque` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('decomptes', "create table if not exists `decomptes` (   `iddecompte` INT unsigned not null auto_increment , primary key (`iddecompte`), `date_demande_c` DATE null , `matricule` INT unsigned null , `nom_prenom` INT unsigned null , `nni` INT unsigned null , `num_cpte` INT unsigned null , `date_recrutement` DATE null , `date_depart` DATE null , `anciennete` INT null , `salaire_m_m` DECIMAL(10,0) null , `from_details` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('decomptes', array('matricule'));
		setupTable('details_decompte', "create table if not exists `details_decompte` (   `iddetail_decompte` INT(3) unsigned zerofill not null auto_increment , primary key (`iddetail_decompte`), `tranches` VARCHAR(255) null , `periodes` VARCHAR(255) null , `pourcentage` DECIMAL(10,2) null , `coef` DECIMAL(10,1) null , `salaire_moyen` DECIMAL(10,2) null , `montant` DECIMAL(10,2) null , `mois_preavis` DECIMAL(10,2) null , `mois_conge` DECIMAL(10,2) null , `to_decomptes` INT unsigned null ) CHARSET utf8", $silent);
		setupIndexes('details_decompte', array('to_decomptes'));
		setupTable('informations', "create table if not exists `informations` (   `idinformation` INT unsigned not null auto_increment , primary key (`idinformation`), `situation` VARCHAR(255) null , `matricule` INT unsigned null , `nom_prenom` INT unsigned null , `education` VARCHAR(255) null , `diplome_primordial` VARCHAR(255) null , `date_diplome` DATE null , `institution` VARCHAR(255) null , `lieu_diplome` VARCHAR(255) null , `diplome_certificat` VARCHAR(255) null , `specialite` VARCHAR(255) null , `institution_specialite` VARCHAR(255) null , `lieu_specialite` VARCHAR(255) null , `langues` VARCHAR(255) null , `arabe` BLOB null , `francais` BLOB null , `anglais` BLOB null , `autres1` VARCHAR(255) null , `autres2` BLOB null , `informatique` VARCHAR(255) null , `utilisation` VARCHAR(255) null , `programmation` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('informations', array('matricule'));
		setupTable('formations', "create table if not exists `formations` (   `idformation` INT unsigned not null auto_increment , primary key (`idformation`), `matricule` INT unsigned null , `nom_prenom` INT unsigned null , `specialisation` VARCHAR(255) null , `annee` VARCHAR(255) null , `date` DATE null , `organisme` VARCHAR(255) null , `lieu` VARCHAR(255) null , `nature` VARCHAR(255) null , `duree` INT null , `effets` VARCHAR(255) null , `resultat` VARCHAR(255) null , `r_c_p` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('formations', array('matricule'));
		setupTable('carrieres', "create table if not exists `carrieres` (   `idcarriere` INT unsigned not null auto_increment , primary key (`idcarriere`), `matricule` INT unsigned null , `direction` INT unsigned null , `fonction` INT unsigned null , `division` INT unsigned null , `service` INT unsigned null , `date` DATE null , `r_n_m_a_p_d` VARCHAR(255) null , `statut` VARCHAR(255) not null , `observations` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('carrieres', array('matricule'));
		setupTable('expenses', "create table if not exists `expenses` (   `idexpense` INT(3) unsigned zerofill not null auto_increment , primary key (`idexpense`), `type` VARCHAR(255) null , `nature` VARCHAR(255) null , `date_saisie` DATE null , `matricule` INT unsigned null , `client` INT unsigned null , `date_recrutement` INT unsigned null , `fonction` INT unsigned null , `division` INT unsigned null , `service` INT unsigned null , `direction` INT unsigned null , `amount` INT null default '0' , `date_depart` DATE null , `date_retour` DATE null , `date_depart_derniere` DATE null , `adresse_absence` VARCHAR(255) null , `departement` VARCHAR(255) null , `signataire` VARCHAR(255) null , `observations` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('expenses', array('matricule'));
		setupTable('rythme_prime', "create table if not exists `rythme_prime` (   `idrythme_prime` INT unsigned not null auto_increment , primary key (`idrythme_prime`), `rythme` INT null default '1' ) CHARSET utf8", $silent);


		// save MD5
		if($fp=@fopen(dirname(__FILE__).'/setup.md5', 'w')) {
			fwrite($fp, $thisMD5);
			fclose($fp);
		}
	}


	function setupIndexes($tableName, $arrFields) {
		if(!is_array($arrFields)) {
			return false;
		}

		foreach($arrFields as $fieldName) {
			if(!$res=@db_query("SHOW COLUMNS FROM `$tableName` like '$fieldName'")) {
				continue;
			}
			if(!$row=@db_fetch_assoc($res)) {
				continue;
			}
			if($row['Key']=='') {
				@db_query("ALTER TABLE `$tableName` ADD INDEX `$fieldName` (`$fieldName`)");
			}
		}
	}


	function setupTable($tableName, $createSQL='', $silent=true, $arrAlter='') {
		global $Translation;
		ob_start();

		echo '<div style="padding: 5px; border-bottom:solid 1px silver; font-family: verdana, arial; font-size: 10px;">';

		// is there a table rename query?
		if(is_array($arrAlter)) {
			$matches=array();
			if(preg_match("/ALTER TABLE `(.*)` RENAME `$tableName`/", $arrAlter[0], $matches)) {
				$oldTableName=$matches[1];
			}
		}

		if($res=@db_query("select count(1) from `$tableName`")) { // table already exists
			if($row = @db_fetch_array($res)) {
				echo str_replace("<TableName>", $tableName, str_replace("<NumRecords>", $row[0],$Translation["table exists"]));
				if(is_array($arrAlter)) {
					echo '<br>';
					foreach($arrAlter as $alter) {
						if($alter!='') {
							echo "$alter ... ";
							if(!@db_query($alter)) {
								echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
								echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
							}else{
								echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
							}
						}
					}
				}else{
					echo $Translation["table uptodate"];
				}
			}else{
				echo str_replace("<TableName>", $tableName, $Translation["couldnt count"]);
			}
		}else{ // given tableName doesn't exist

			if($oldTableName!='') { // if we have a table rename query
				if($ro=@db_query("select count(1) from `$oldTableName`")) { // if old table exists, rename it.
					$renameQuery=array_shift($arrAlter); // get and remove rename query

					echo "$renameQuery ... ";
					if(!@db_query($renameQuery)) {
						echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
						echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
					}else{
						echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
					}

					if(is_array($arrAlter)) setupTable($tableName, $createSQL, false, $arrAlter); // execute Alter queries on renamed table ...
				}else{ // if old tableName doesn't exist (nor the new one since we're here), then just create the table.
					setupTable($tableName, $createSQL, false); // no Alter queries passed ...
				}
			}else{ // tableName doesn't exist and no rename, so just create the table
				echo str_replace("<TableName>", $tableName, $Translation["creating table"]);
				if(!@db_query($createSQL)) {
					echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
					echo '<div class="text-danger">' . $Translation['mysql said'] . db_error(db_link()) . '</div>';
				}else{
					echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
				}
			}
		}

		echo "</div>";

		$out=ob_get_contents();
		ob_end_clean();
		if(!$silent) {
			echo $out;
		}
	}
?>