<?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('entete', "create table if not exists `entete` (   `identete` INT unsigned not null auto_increment , primary key (`identete`), `link_in` INT(4) unsigned zerofill null , `nom` VARCHAR(80) null , `raison_sociale` VARCHAR(255) null , `description` VARCHAR(255) null , `adresse` VARCHAR(80) null , `telephone` VARCHAR(80) null , `whatsapp` VARCHAR(80) null , `email` VARCHAR(80) null , `site_web` VARCHAR(80) null , `bp` VARCHAR(80) null , `footer` VARCHAR(255) null , `footer_1` VARCHAR(255) null , `footer_2` VARCHAR(255) null , `footer_3` TEXT null , `logo` VARCHAR(80) null , `logop` VARCHAR(80) null , `notice` VARCHAR(80) null , `devise` VARCHAR(80) null , `signataire` VARCHAR(80) null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `entete` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `entete` ADD `agent` VARCHAR(80) null "));
		setupTable('personnels', "create table if not exists `personnels` (   `idpersonnel` INT(3) unsigned zerofill not null auto_increment , primary key (`idpersonnel`), `matricule` VARCHAR(80) null , `date_recrutement` DATE null , `nom` VARCHAR(80) not null , `prenom` VARCHAR(80) not null , `nationalite` INT unsigned null , `cin` VARCHAR(80) not null , unique `cin_unique` (`cin`), `adresse` VARCHAR(80) null , `telephone` VARCHAR(80) not null , `whatsapp` VARCHAR(80) null , `email` VARCHAR(80) null , `agence` INT unsigned null , `fonction` INT unsigned null , `service` INT unsigned null , `poste` INT unsigned null , `photo` VARCHAR(40) null , `cv` VARCHAR(80) null , `dossier` VARCHAR(80) null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `personnels` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `personnels` ADD `agent` VARCHAR(80) null "));
		setupIndexes('personnels', array('nationalite','agence','fonction','service','poste'));
		setupTable('clients', "create table if not exists `clients` (   `idclient` INT(3) unsigned zerofill not null auto_increment , primary key (`idclient`), `matricule` VARCHAR(80) null , `agent` VARCHAR(80) null , `date_en` DATETIME null , `type_client` INT unsigned not null , `raison_sociale` VARCHAR(255) null , `personne_contact` VARCHAR(255) null , `email_pc` VARCHAR(80) null , `phone_pc` VARCHAR(80) null , `ninea` VARCHAR(255) null , unique `ninea_unique` (`ninea`), `rc` VARCHAR(80) null , unique `rc_unique` (`rc`), `genre` VARCHAR(80) null , `prenom` VARCHAR(80) null , `nom` VARCHAR(80) null , `cin` VARCHAR(80) null , unique `cin_unique` (`cin`), `agence` INT unsigned null , `nationalite` INT unsigned null , `categorie` INT unsigned null , `fonction` INT unsigned null , `activite` INT unsigned null , `date_naissance` DATETIME null , `age` INT null , `passport` VARCHAR(80) not null , unique `passport_unique` (`passport`), `delivre_le` DATETIME not null , `expire_le` DATETIME not null , `lieu` INT unsigned not null , `validite` VARCHAR(80) null , `adresse` VARCHAR(80) not null , `telephone` VARCHAR(80) not null , `whatsapp` VARCHAR(80) not null , `urgence` VARCHAR(80) not null , `email` VARCHAR(80) not null , `compte` VARCHAR(80) null , `banque` INT unsigned null , `carte_fidelite` INT unsigned null , `num_carte` INT unsigned null , unique `num_carte_unique` (`num_carte`), `signature` VARCHAR(80) null , `photo` VARCHAR(40) null , `dossier` VARCHAR(80) null , `observations` TEXT null , `groupe` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `clients` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `clients` ADD `agent_1` VARCHAR(80) null ","ALTER TABLE `clients` DROP `agent_1`"));
		setupIndexes('clients', array('type_client','agence','nationalite','categorie','fonction','activite','lieu','banque','carte_fidelite'));
		setupTable('suivi_client', "create table if not exists `suivi_client` (   `idsuivi_client` INT unsigned not null auto_increment , primary key (`idsuivi_client`), `num_dossier` VARCHAR(80) null , unique `num_dossier_unique` (`num_dossier`), `nom` INT(3) unsigned zerofill not null , `prenom` INT(3) unsigned zerofill null , `raison_sociale` INT(3) unsigned zerofill null , `telephone` INT(3) unsigned zerofill null , `email` INT(3) unsigned zerofill null , `whatsapp` INT(3) unsigned zerofill null , `service_demande` INT unsigned not null , `date_demande` DATE null , `statut_service` VARCHAR(80) not null , `num_visa` VARCHAR(80) null , `date_debut_visa` DATE null , `date_fin_visa` DATE null , `type_de_visa` INT unsigned null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `suivi_client` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `suivi_client` ADD `agent` VARCHAR(80) null "));
		setupIndexes('suivi_client', array('nom','service_demande','type_de_visa'));
		setupTable('rendez_vous', "create table if not exists `rendez_vous` (   `idrv` INT unsigned not null auto_increment , primary key (`idrv`), `objet` VARCHAR(255) null , `annee` INT unsigned null , `mois` BLOB null , `jour` BLOB null , `heure` TIME null , `rvs` TEXT null , `observations` TEXT null , `cree_le` VARCHAR(200) null , `modifie_le` VARCHAR(200) null , `statut` VARCHAR(80) not null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `rendez_vous` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `rendez_vous` ADD `agent` VARCHAR(80) null "));
		setupIndexes('rendez_vous', array('annee'));
		setupTable('partenaires', "create table if not exists `partenaires` (   `idpartenaires` INT unsigned not null auto_increment , primary key (`idpartenaires`), `date` DATE null , `nom_prenom` INT(3) unsigned zerofill null , `fonction` INT(3) unsigned zerofill null , `adresse` INT(3) unsigned zerofill null , `telephone` INT(3) unsigned zerofill null , `email` INT(3) unsigned zerofill null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `partenaires` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `partenaires` ADD `agent` VARCHAR(80) null "));
		setupIndexes('partenaires', array('nom_prenom'));
		setupTable('packages', "create table if not exists `packages` (   `idpackage` INT unsigned not null auto_increment , primary key (`idpackage`), `nom` VARCHAR(80) null , `prix` DECIMAL(10,0) null default '0' , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `packages` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `packages` ADD `agent` VARCHAR(80) null "));
		setupTable('oumrah', "create table if not exists `oumrah` (   `idoumrah` INT unsigned not null auto_increment , primary key (`idoumrah`), `Pelerinage` INT unsigned null , `Prenom` VARCHAR(255) null , `Nom` VARCHAR(255) null , `Datedenaissance` DATE null , `Lieudenaissance` VARCHAR(255) null , `Adresse` VARCHAR(255) null , `Sexe` VARCHAR(255) null , `Numropasseport` VARCHAR(255) null , unique `Numropasseport_unique` (`Numropasseport`), `Remarque` VARCHAR(255) null , `Tlphoneplerin` VARCHAR(255) null , `Tlphonefamille` VARCHAR(255) null , `Personnecontacter` VARCHAR(255) null , `Squence` VARCHAR(255) null , unique `Squence_unique` (`Squence`), `Profession` VARCHAR(255) null , `Ethnie` VARCHAR(255) null , `Region` VARCHAR(255) null , `total_entree` DECIMAL(10,0) null default '0' , `total_sortie` DECIMAL(10,0) null default '0' , `solde` DECIMAL(10,0) null default '0' , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `oumrah` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `oumrah` ADD `agent` VARCHAR(80) null "));
		setupIndexes('oumrah', array('Pelerinage'));
		setupTable('hajj_oumrah', "create table if not exists `hajj_oumrah` (   `idhajjoumrah` INT unsigned not null auto_increment , primary key (`idhajjoumrah`), `Pelerinage` VARCHAR(80) null , `Prenom` VARCHAR(255) null , `Nom` VARCHAR(255) null , `Datedenaissance` DATE null , `Lieudenaissance` VARCHAR(255) null , `Adresse` VARCHAR(255) null , `Sexe` VARCHAR(255) null , `Numropasseport` VARCHAR(255) null , unique `Numropasseport_unique` (`Numropasseport`), `Remarque` VARCHAR(255) null , `Tlphoneplerin` VARCHAR(255) null , `Tlphonefamille` VARCHAR(255) null , `Personnecontacter` VARCHAR(255) null , `Squence` VARCHAR(255) null , unique `Squence_unique` (`Squence`), `Profession` VARCHAR(255) null , `Ethnie` VARCHAR(255) null , `Region` VARCHAR(255) null , `total_entree` DECIMAL(10,0) null default '0' , `total_sortie` DECIMAL(10,0) null default '0' , `solde` DECIMAL(10,0) null default '0' , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `hajj_oumrah` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `hajj_oumrah` ADD `agent` VARCHAR(80) null "));
		setupTable('souscriptions', "create table if not exists `souscriptions` (   `idsouscription` INT unsigned not null auto_increment , primary key (`idsouscription`), `date_enr` DATE null , `pelerin` INT unsigned null , `oumrah` INT unsigned null , `package` INT unsigned null , `prix` DECIMAL(10,0) null default '0' , `remise` DECIMAL(10,0) null default '0' , `net_a_payer` DECIMAL(10,0) null default '0' , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `souscriptions` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `souscriptions` ADD `agent` VARCHAR(80) null "));
		setupIndexes('souscriptions', array('pelerin','oumrah','package'));
		setupTable('entrees', "create table if not exists `entrees` (   `identrees` INT unsigned not null auto_increment , primary key (`identrees`), `date_entree` DATE null , `client` INT unsigned null , `oumrah` INT unsigned null , `description` INT unsigned null , `montant` DECIMAL(10,0) null default '0' , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `entrees` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `entrees` ADD `agent` VARCHAR(80) null "));
		setupIndexes('entrees', array('client','oumrah','description'));
		setupTable('sorties', "create table if not exists `sorties` (   `idsorties` INT unsigned not null auto_increment , primary key (`idsorties`), `date_sortie` DATE null , `client` INT unsigned null , `oumrah` INT unsigned null , `montant` DECIMAL(10,0) null default '0' , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `sorties` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `sorties` ADD `agent` VARCHAR(80) null "));
		setupIndexes('sorties', array('client','oumrah'));
		setupTable('agences', "create table if not exists `agences` (   `idagence` INT unsigned not null auto_increment , primary key (`idagence`), `agence` VARCHAR(80) null , unique `agence_unique` (`agence`), `adresse` VARCHAR(80) null , `responsable` VARCHAR(80) not null , `telephone` VARCHAR(80) not null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `agences` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `agences` ADD `agent` VARCHAR(80) null "));
		setupTable('gestion_transactions', "create table if not exists `gestion_transactions` (   `idgtransaction` INT(3) unsigned zerofill not null auto_increment , primary key (`idgtransaction`), `link_in` VARCHAR(80) null , `agent_init` VARCHAR(2) not null , `agent` VARCHAR(80) null , `matricule` INT(3) unsigned zerofill null , `numero` VARCHAR(80) null , unique `numero_unique` (`numero`), `date` DATE null , `avec_entete` INT unsigned not null , `rs` INT unsigned null , `type` VARCHAR(80) not null , `nom_prenom` INT(3) unsigned zerofill not null , `adclient` INT(3) unsigned zerofill null , `telclient` INT(3) unsigned zerofill null , `emclient` INT(3) unsigned zerofill null , `raison_sociale` INT(3) unsigned zerofill null , `termes` TEXT null , `fpdf` VARCHAR(80) null , `fzip` VARCHAR(80) null , `devise` INT unsigned null , `logo` INT unsigned null , `description` INT unsigned null , `adresse` INT unsigned null , `telephone` INT unsigned null , `whatsapp` INT unsigned null , `email` INT unsigned null , `site_web` INT unsigned null , `bp` INT unsigned null , `footer_3` INT unsigned null , `notice` INT unsigned null , `signataire` INT unsigned null , `groupe` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `gestion_transactions` ADD `groupe` VARCHAR(80) null "));
		setupIndexes('gestion_transactions', array('avec_entete','nom_prenom'));
		setupTable('billeteries', "create table if not exists `billeteries` (   `idbilleterie` INT(3) unsigned zerofill not null auto_increment , primary key (`idbilleterie`), `link_out` INT(3) unsigned zerofill null , `facture` VARCHAR(80) null , unique `facture_unique` (`facture`), `date` DATE null , `service` INT unsigned not null , `passager` INT(3) unsigned zerofill not null , `gds` INT unsigned not null , `compagnie` INT unsigned not null , `parcours` INT unsigned not null , `nature_transaction` INT unsigned null , `billet` VARCHAR(80) null , `dossier` VARCHAR(80) null , `prix_achat` DECIMAL(10,0) null default '0' , `prix_vente` DECIMAL(10,0) null default '0' , `qts` INT null default '1' , `commission` DECIMAL(10,0) null default '0' , `frais_service` DECIMAL(10,0) null default '0' , `frais_assurance` DECIMAL(10,0) null default '0' , `mtva` FLOAT(10,2) null default '0' , `tva` FLOAT(10,0) null default '0' , `tht` DECIMAL(10,0) null default '0' , `net_compagnie` DECIMAL(10,0) null default '0' , `net_a_payer` DECIMAL(10,0) null default '0' , `account` DECIMAL(10,0) null default '0' , `reste_a_payer` DECIMAL(10,0) null default '0' , `agent` VARCHAR(80) null , `groupe` VARCHAR(80) null , `observations` TEXT null ) CHARSET utf8", $silent, array( "ALTER TABLE billeteries ADD `field27` VARCHAR(40)","ALTER TABLE `billeteries` CHANGE `field27` `agent` VARCHAR(80) null "," ALTER TABLE `billeteries` CHANGE `agent` `agent` VARCHAR(80) null ","ALTER TABLE billeteries ADD `field28` VARCHAR(40)","ALTER TABLE `billeteries` CHANGE `field28` `groupe` VARCHAR(80) null "," ALTER TABLE `billeteries` CHANGE `groupe` `groupe` VARCHAR(80) null "));
		setupIndexes('billeteries', array('link_out','service','passager','gds','compagnie','parcours','nature_transaction'));
		setupTable('gestion_changes', "create table if not exists `gestion_changes` (   `idgchange` INT(3) unsigned zerofill not null auto_increment , primary key (`idgchange`), `link_in` VARCHAR(80) null , `agent_init` VARCHAR(2) not null , `agent` VARCHAR(80) null , `matricule` INT(3) unsigned zerofill null , `numero` VARCHAR(80) null , unique `numero_unique` (`numero`), `date` DATE null , `avec_entete` INT unsigned not null , `rs` INT unsigned null , `type` VARCHAR(80) null , `nom_prenom` INT(3) unsigned zerofill not null , `nationalite` INT(3) unsigned zerofill null , `profession` INT(3) unsigned zerofill null , `adclient` INT(3) unsigned zerofill null , `telclient` INT(3) unsigned zerofill null , `emclient` INT(3) unsigned zerofill null , `raison_sociale` INT(3) unsigned zerofill null , `termes` TEXT null , `fpdf` VARCHAR(80) null , `fzip` VARCHAR(80) null , `devise` INT unsigned null , `logo` INT unsigned null , `description` INT unsigned null , `adresse` INT unsigned null , `telephone` INT unsigned null , `whatsapp` INT unsigned null , `email` INT unsigned null , `site_web` INT unsigned null , `bp` INT unsigned null , `footer_3` INT unsigned null , `notice` INT unsigned null , `signataire` INT unsigned null , `groupe` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `gestion_changes` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `gestion_changes` ADD `agent_1` VARCHAR(80) null ","ALTER TABLE `gestion_changes` DROP `agent_1`"));
		setupIndexes('gestion_changes', array('avec_entete','nom_prenom'));
		setupTable('changes', "create table if not exists `changes` (   `idchange` INT(3) unsigned zerofill not null auto_increment , primary key (`idchange`), `link_out` INT(3) unsigned zerofill null , `facture` VARCHAR(80) null , unique `facture_unique` (`facture`), `date` DATE null , `nature_transaction` VARCHAR(255) null , `billet` VARCHAR(80) null , `dossier` VARCHAR(80) null , `prix_achat` DECIMAL(10,0) null default '0' , `mtva` FLOAT(10,2) null default '0' , `net_a_payer` DECIMAL(10,0) null default '0' , `net_compagnie` DECIMAL(10,0) null default '0' , `prix_vente` DECIMAL(10,0) null default '0' , `qts` INT null default '1' , `commission` DECIMAL(10,0) null default '0' , `frais_service` DECIMAL(10,0) null default '0' , `frais_assurance` DECIMAL(10,0) null default '0' , `tva` FLOAT(10,0) null default '0' , `tht` DECIMAL(10,0) null default '0' , `account` DECIMAL(10,0) null default '0' , `reste_a_payer` DECIMAL(10,0) null default '0' , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `changes` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `changes` ADD `agent` VARCHAR(80) null "));
		setupIndexes('changes', array('link_out'));
		setupTable('partages', "create table if not exists `partages` (   `idpartage` INT unsigned not null auto_increment , primary key (`idpartage`), `agent` VARCHAR(80) null , `date` DATE null , `statut` INT unsigned null , `client` INT(3) unsigned zerofill null , `image` VARCHAR(40) null , `word` VARCHAR(80) null , `document` VARCHAR(80) null , `compresse` VARCHAR(80) null , `commentaire` TEXT null , `groupe` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `partages` ADD `groupe` VARCHAR(80) null "));
		setupIndexes('partages', array('statut','client'));
		setupTable('type_compagnie', "create table if not exists `type_compagnie` (   `idtypecompagnie` INT unsigned not null auto_increment , primary key (`idtypecompagnie`), `type_compangie` VARCHAR(80) null , unique `type_compangie_unique` (`type_compangie`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('compagnies', "create table if not exists `compagnies` (   `idcompagnie` INT unsigned not null auto_increment , primary key (`idcompagnie`), `nom_compagnie` VARCHAR(80) null , unique `nom_compagnie_unique` (`nom_compagnie`), `adresse` VARCHAR(80) null , `contact` VARCHAR(80) null , `type` INT unsigned null , `observations` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('compagnies', array('type'));
		setupTable('produits_services', "create table if not exists `produits_services` (   `idps` INT unsigned not null auto_increment , primary key (`idps`), `produit_service` VARCHAR(80) null , unique `produit_service_unique` (`produit_service`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('banques', "create table if not exists `banques` (   `idbanque` INT unsigned not null auto_increment , primary key (`idbanque`), `banque` VARCHAR(80) null , unique `banque_unique` (`banque`), `adresse` VARCHAR(80) null , `telephone` VARCHAR(80) null , `email` VARCHAR(80) null , `contact` VARCHAR(80) null , `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('pays', "create table if not exists `pays` (   `idpays` INT unsigned not null auto_increment , primary key (`idpays`), `pays` VARCHAR(80) null , unique `pays_unique` (`pays`)) CHARSET utf8", $silent);
		setupTable('villes', "create table if not exists `villes` (   `idville` INT unsigned not null auto_increment , primary key (`idville`), `ville` VARCHAR(80) null , unique `ville_unique` (`ville`)) CHARSET utf8", $silent);
		setupTable('items', "create table if not exists `items` (   `iditem` INT unsigned not null auto_increment , primary key (`iditem`), `item` VARCHAR(80) null , unique `item_unique` (`item`), `observation` TEXT null ) CHARSET utf8", $silent);
		setupTable('type_client', "create table if not exists `type_client` (   `idtype_client` INT unsigned not null auto_increment , primary key (`idtype_client`), `type_client` VARCHAR(80) null , unique `type_client_unique` (`type_client`), `observation` TEXT null ) CHARSET utf8", $silent);
		setupTable('fonctions', "create table if not exists `fonctions` (   `idfonction` INT unsigned not null auto_increment , primary key (`idfonction`), `fonction` VARCHAR(80) null , unique `fonction_unique` (`fonction`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('activites', "create table if not exists `activites` (   `idactivite` INT unsigned not null auto_increment , primary key (`idactivite`), `activite` VARCHAR(80) null , unique `activite_unique` (`activite`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('postes', "create table if not exists `postes` (   `idposte` INT unsigned not null auto_increment , primary key (`idposte`), `poste` VARCHAR(80) null , unique `poste_unique` (`poste`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('annees', "create table if not exists `annees` (   `idannee` INT unsigned not null auto_increment , primary key (`idannee`), `annee` INT null , unique `annee_unique` (`annee`)) CHARSET utf8", $silent);
		setupTable('gds', "create table if not exists `gds` (   `idgds` INT unsigned not null auto_increment , primary key (`idgds`), `gds` VARCHAR(80) null , unique `gds_unique` (`gds`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('objets', "create table if not exists `objets` (   `idobjet` INT unsigned not null auto_increment , primary key (`idobjet`), `objet` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('type_rapport', "create table if not exists `type_rapport` (   `idtrpt` INT unsigned not null auto_increment , primary key (`idtrpt`), `type_rapport` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('fidelite', "create table if not exists `fidelite` (   `idfidelite` INT unsigned not null auto_increment , primary key (`idfidelite`), `carte_fidelite` VARCHAR(80) null , unique `carte_fidelite_unique` (`carte_fidelite`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('pointages', "create table if not exists `pointages` (   `idpointage` INT unsigned not null auto_increment , primary key (`idpointage`), `date` DATE null , `nom_prenom` INT(3) unsigned zerofill null , `agence` INT(3) unsigned zerofill null , `fonction` INT(3) unsigned zerofill null , `arrivee` TIME null , `depart` TIME null , `obs` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `pointages` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `pointages` ADD `agent` VARCHAR(80) null "));
		setupIndexes('pointages', array('nom_prenom'));
		setupTable('note_de_service', "create table if not exists `note_de_service` (   `idnote_service` INT unsigned not null auto_increment , primary key (`idnote_service`), `date` DATE null , `objet` VARCHAR(80) not null , `texte` TEXT not null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `note_de_service` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `note_de_service` ADD `agent` VARCHAR(80) null "));
		setupTable('redactions', "create table if not exists `redactions` (   `idredaction` INT unsigned not null auto_increment , primary key (`idredaction`), `date` DATE null , `objet` INT unsigned null , `agent` VARCHAR(80) null , `redaction` LONGTEXT null , `obs` TEXT null , `groupe` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `redactions` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `redactions` ADD `agent_1` VARCHAR(80) null ","ALTER TABLE `redactions` DROP `agent_1`"));
		setupIndexes('redactions', array('objet'));
		setupTable('rapports_activite', "create table if not exists `rapports_activite` (   `idrapport` INT unsigned not null auto_increment , primary key (`idrapport`), `date` DATE null , `nom_prenom` VARCHAR(40) null , `objet` INT unsigned null , `type_rapport` INT unsigned null , `rapport` LONGTEXT null , `obs` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `rapports_activite` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `rapports_activite` ADD `agent` VARCHAR(80) null "));
		setupIndexes('rapports_activite', array('objet','type_rapport'));
		setupTable('gestion_comptes', "create table if not exists `gestion_comptes` (   `idgestioncompte` INT unsigned not null auto_increment , primary key (`idgestioncompte`), `nom_compte` VARCHAR(255) null , unique `nom_compte_unique` (`nom_compte`), `num_compte` INT(4) null , unique `num_compte_unique` (`num_compte`), `banque` INT unsigned null , `type_compte` INT unsigned null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `gestion_comptes` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `gestion_comptes` ADD `agent` VARCHAR(80) null "));
		setupIndexes('gestion_comptes', array('banque','type_compte'));
		setupTable('gestion_comptes_1', "create table if not exists `gestion_comptes_1` (   `idgestioncompte` INT unsigned not null auto_increment , primary key (`idgestioncompte`), `nom_compte` VARCHAR(255) null , unique `nom_compte_unique` (`nom_compte`), `num_compte` INT(4) null , unique `num_compte_unique` (`num_compte`), `banque` INT unsigned null , `type_compte` INT unsigned null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `gestion_comptes_1` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `gestion_comptes_1` ADD `agent` VARCHAR(80) null "));
		setupIndexes('gestion_comptes_1', array('banque','type_compte'));
		setupTable('type_comptes', "create table if not exists `type_comptes` (   `idtypecompte` INT unsigned not null auto_increment , primary key (`idtypecompte`), `type_compte` VARCHAR(80) null , unique `type_compte_unique` (`type_compte`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('journal', "create table if not exists `journal` (   `idjournal` INT unsigned not null auto_increment , primary key (`idjournal`), `date` DATE null , `libelle` VARCHAR(80) null , `compte_debit` INT unsigned null , `compte_credit` INT unsigned null , `tht` DECIMAL(10,0) null default '0' , `tva` FLOAT(10,2) null default '0' , `tva2` FLOAT(10,0) null default '0' , `ttc` DECIMAL(10,0) null default '0' , `remarques` VARCHAR(80) null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `journal` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `journal` ADD `agent` VARCHAR(80) null "));
		setupIndexes('journal', array('compte_debit','compte_credit'));
		setupTable('liens', "create table if not exists `liens` (   `idlien` INT unsigned not null auto_increment , primary key (`idlien`), `logo` VARCHAR(80) null , `description` VARCHAR(255) null , `lien` VARCHAR(80) null , unique `lien_unique` (`lien`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('courrier_entrant', "create table if not exists `courrier_entrant` (   `idcentrant` INT unsigned not null auto_increment , primary key (`idcentrant`), `date` DATE null , `expediteur` VARCHAR(80) null , `objet` VARCHAR(80) null , `destinataire` VARCHAR(80) null , `piece_jointe` VARCHAR(80) null , `statut` VARCHAR(80) not null , `remarques` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `courrier_entrant` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `courrier_entrant` ADD `agent` VARCHAR(80) null "));
		setupTable('courrier_sortant', "create table if not exists `courrier_sortant` (   `idcsortant` INT unsigned not null auto_increment , primary key (`idcsortant`), `date` DATE null , `expediteur` VARCHAR(80) null , `objet` VARCHAR(80) null , `destinataire` VARCHAR(80) null , `piece_jointe` VARCHAR(80) null , `statut` VARCHAR(80) not null , `remarques` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `courrier_sortant` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `courrier_sortant` ADD `agent` VARCHAR(80) null "));
		setupTable('vitae', "create table if not exists `vitae` (   `idvitae` INT unsigned not null auto_increment , primary key (`idvitae`), `date` DATE null , `candidat` VARCHAR(80) null , `domaine` VARCHAR(80) null , `dossier` VARCHAR(80) null , `statut` VARCHAR(80) not null , `photo` VARCHAR(40) null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `vitae` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `vitae` ADD `agent` VARCHAR(80) null "));
		setupTable('reunions', "create table if not exists `reunions` (   `idreunion` INT(3) unsigned zerofill not null auto_increment , primary key (`idreunion`), `link_in` VARCHAR(80) null , `avec_entete` INT unsigned not null , `ordre_jour` VARCHAR(255) null , `date` DATE null , `temps` VARCHAR(80) null , `duree` VARCHAR(80) null , `lieu` VARCHAR(80) null , `objectif` TEXT null , `fpdf` VARCHAR(80) null , `fzip` VARCHAR(80) null , `rs` INT unsigned null , `devise` INT unsigned null , `logo` INT unsigned null , `description` INT unsigned null , `adresse` INT unsigned null , `telephone` INT unsigned null , `whatsapp` INT unsigned null , `email` INT unsigned null , `site_web` INT unsigned null , `bp` INT unsigned null , `footer_3` INT unsigned null , `notice` INT unsigned null , `signataire` INT unsigned null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `reunions` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `reunions` ADD `agent` VARCHAR(80) null "));
		setupIndexes('reunions', array('avec_entete'));
		setupTable('item_reunions', "create table if not exists `item_reunions` (   `iditemreunion` INT(3) unsigned zerofill not null auto_increment , primary key (`iditemreunion`), `link_out` INT(3) unsigned zerofill null , `activite` VARCHAR(80) null , `temps` VARCHAR(80) null , `responsable` VARCHAR(80) null , `objectif` VARCHAR(80) null , `observations` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('item_reunions', array('link_out'));
		setupTable('statut_partage', "create table if not exists `statut_partage` (   `idstatutpartage` INT unsigned not null auto_increment , primary key (`idstatutpartage`), `statut` VARCHAR(80) null , unique `statut_unique` (`statut`), `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('chequiers', "create table if not exists `chequiers` (   `idchequier` INT unsigned not null auto_increment , primary key (`idchequier`), `date` DATE null , `lot_num` VARCHAR(80) null , unique `lot_num_unique` (`lot_num`), `lot_qts` INT null , `compte` INT unsigned null , `banque` INT unsigned null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `chequiers` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `chequiers` ADD `agent` VARCHAR(80) null "));
		setupIndexes('chequiers', array('compte'));
		setupTable('cheques', "create table if not exists `cheques` (   `idcheque` INT unsigned not null auto_increment , primary key (`idcheque`), `num` VARCHAR(80) not null , unique `num_unique` (`num`), `chequier` INT unsigned null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `cheques` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `cheques` ADD `agent` VARCHAR(80) null "));
		setupIndexes('cheques', array('chequier'));
		setupTable('gestion_banques', "create table if not exists `gestion_banques` (   `idgestion_banque` INT unsigned not null auto_increment , primary key (`idgestion_banque`), `date` DATE null , `mouvement` VARCHAR(80) not null , `libelle` VARCHAR(80) null , `compte_emetteur` INT unsigned null , `numero` INT unsigned null , `banque` INT unsigned null , `compte_beneficiaire` INT unsigned null , `numero_cb` INT unsigned null , `banque_cb` INT unsigned null , `cheque_mv` INT unsigned null , `cheque_ce` VARCHAR(80) null , unique `cheque_ce_unique` (`cheque_ce`), `depense` DECIMAL(10,0) null default '0' , `recette` DECIMAL(10,0) null default '0' , `solde` DECIMAL(10,0) null default '0' , `cheque` VARCHAR(80) null , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `gestion_banques` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `gestion_banques` ADD `agent` VARCHAR(80) null "));
		setupIndexes('gestion_banques', array('compte_emetteur','compte_beneficiaire','cheque_mv'));
		setupTable('suivi_banques', "create table if not exists `suivi_banques` (   `idsb` INT unsigned not null auto_increment , primary key (`idsb`), `date` DATE null , `mouvement` VARCHAR(80) null , `description` INT unsigned null , `reference` VARCHAR(80) null , unique `reference_unique` (`reference`), `debits` DECIMAL(10,0) null default '0' , `credits` DECIMAL(10,0) null default '0' , `balance` DECIMAL(10,0) null default '0' , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `suivi_banques` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `suivi_banques` ADD `agent` VARCHAR(80) null "));
		setupIndexes('suivi_banques', array('description'));
		setupTable('charges_fixe', "create table if not exists `charges_fixe` (   `idcf` INT unsigned not null auto_increment , primary key (`idcf`), `date` DATE null , `libelle` INT unsigned null , `mouvement` VARCHAR(80) null , `piece` VARCHAR(80) null , `beneficiaire` VARCHAR(80) null , `montant` DECIMAL(10,0) null default '0' , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `charges_fixe` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `charges_fixe` ADD `agent` VARCHAR(80) null "));
		setupIndexes('charges_fixe', array('libelle'));
		setupTable('caisse', "create table if not exists `caisse` (   `idcaisse` INT unsigned not null auto_increment , primary key (`idcaisse`), `date` DATE null , `mouvement` VARCHAR(80) not null , `libelle` VARCHAR(80) null , `recette` DECIMAL(10,0) null default '0' , `depense` DECIMAL(10,0) null default '0' , `solde` DECIMAL(10,0) null default '0' , `observations` TEXT null , `groupe` VARCHAR(80) null , `agent` VARCHAR(80) null ) CHARSET utf8", $silent, array( "ALTER TABLE `caisse` ADD `groupe` VARCHAR(80) null ","ALTER TABLE `caisse` ADD `agent` VARCHAR(80) null "));
		setupTable('type_visa', "create table if not exists `type_visa` (   `idtype_visa` INT unsigned not null auto_increment , primary key (`idtype_visa`), `type_visa` VARCHAR(80) null ) CHARSET utf8", $silent);
		setupTable('libelle_charges_fixe', "create table if not exists `libelle_charges_fixe` (   `idlcf` INT unsigned not null auto_increment , primary key (`idlcf`), `libelle` VARCHAR(80) null ) CHARSET utf8", $silent);
		setupTable('desc_suivi_banque', "create table if not exists `desc_suivi_banque` (   `iddsb` INT unsigned not null auto_increment , primary key (`iddsb`), `description` VARCHAR(255) null ) 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;
		}
	}
?>