<?php

	#########################################################
	/*
	~~~~~~ LIST OF FUNCTIONS ~~~~~~
		getTableList() -- returns an associative array (tableName => tableData, tableData is array(tableCaption, tableDescription, tableIcon)) of tables accessible by current user
		get_table_groups() -- returns an associative array (table_group => tables_array)
		logInMember() -- checks POST login. If not valid, redirects to index.php, else returns TRUE
		getTablePermissions($tn) -- returns an array of permissions allowed for logged member to given table (allowAccess, allowInsert, allowView, allowEdit, allowDelete) -- allowAccess is set to true if any access level is allowed
		get_sql_fields($tn) -- returns the SELECT part of the table view query
		get_sql_from($tn[, true, [, false]]) -- returns the FROM part of the table view query, with full joins (unless third paramaeter is set to true), optionally skipping permissions if true passed as 2nd param.
		get_joined_record($table, $id[, true]) -- returns assoc array of record values for given PK value of given table, with full joins, optionally skipping permissions if true passed as 3rd param.
		get_defaults($table) -- returns assoc array of table fields as array keys and default values (or empty), excluding automatic values as array values
		htmlUserBar() -- returns html code for displaying user login status to be used on top of pages.
		showNotifications($msg, $class) -- returns html code for displaying a notification. If no parameters provided, processes the GET request for possible notifications.
		parseMySQLDate(a, b) -- returns a if valid mysql date, or b if valid mysql date, or today if b is true, or empty if b is false.
		parseCode(code) -- calculates and returns special values to be inserted in automatic fields.
		addFilter(i, filterAnd, filterField, filterOperator, filterValue) -- enforce a filter over data
		clearFilters() -- clear all filters
		loadView($view, $data) -- passes $data to templates/{$view}.php and returns the output
		loadTable($table, $data) -- loads table template, passing $data to it
		filterDropdownBy($filterable, $filterers, $parentFilterers, $parentPKField, $parentCaption, $parentTable, &$filterableCombo) -- applies cascading drop-downs for a lookup field, returns js code to be inserted into the page
		br2nl($text) -- replaces all variations of HTML <br> tags with a new line character
		htmlspecialchars_decode($text) -- inverse of htmlspecialchars()
		entitiesToUTF8($text) -- convert unicode entities (e.g. &#1234;) to actual UTF8 characters, requires multibyte string PHP extension
		func_get_args_byref() -- returns an array of arguments passed to a function, by reference
		permissions_sql($table, $level) -- returns an array containing the FROM and WHERE additions for applying permissions to an SQL query
		error_message($msg[, $back_url]) -- returns html code for a styled error message .. pass explicit false in second param to suppress back button
		toMySQLDate($formattedDate, $sep = datalist_date_separator, $ord = datalist_date_format)
		reIndex(&$arr) -- returns a copy of the given array, with keys replaced by 1-based numeric indices, and values replaced by original keys
		get_embed($provider, $url[, $width, $height, $retrieve]) -- returns embed code for a given url (supported providers: youtube, googlemap)
		check_record_permission($table, $id, $perm = 'view') -- returns true if current user has the specified permission $perm ('view', 'edit' or 'delete') for the given recors, false otherwise
		NavMenus($options) -- returns the HTML code for the top navigation menus. $options is not implemented currently.
		StyleSheet() -- returns the HTML code for included style sheet files to be placed in the <head> section.
		getUploadDir($dir) -- if dir is empty, returns upload dir configured in defaultLang.php, else returns $dir.
		PrepareUploadedFile($FieldName, $MaxSize, $FileTypes='jpg|jpeg|gif|png', $NoRename=false, $dir="") -- validates and moves uploaded file for given $FieldName into the given $dir (or the default one if empty)
		get_home_links($homeLinks, $default_classes, $tgroup) -- process $homeLinks array and return custom links for homepage. Applies $default_classes to links if links have classes defined, and filters links by $tgroup (using '*' matches all table_group values)
		quick_search_html($search_term, $label, $separate_dv = true) -- returns HTML code for the quick search box.
	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	*/

	#########################################################

	function getTableList($skip_authentication = false){
		$arrAccessTables = array();
		$arrTables = array(
			/* 'table_name' => ['table caption', 'homepage description', 'icon', 'table group name'] */   
			'main' => array('Main', '', 'resources/table_icons/appgini-icon.png', 'PARAMETRAGES'),
			'dossier_individuel' => array('GESTION DU PERSONNEL', '', 'resources/table_icons/folder_user.png', 'GESTION DU PERSONNEL'),
			'gestion_salaire' => array('SUIVI DES SALAIRES', '', 'resources/table_icons/coins_in_hand.png', 'GESTION DU PERSONNEL'),
			'gestion_prime' => array('SUIVI DES PRIMES', '', 'resources/table_icons/coin_stack_gold.png', 'GESTION DU PERSONNEL'),
			'paiements' => array('ETAT DE PAIMENTS', '', 'resources/table_icons/coins_in_hand.png', 'GESTION DU PERSONNEL'),
			'etat_paiements' => array('LISTE ETAT PAIEMENT', '', 'resources/table_icons/coins_in_hand.png', 'GESTION DU PERSONNEL'),
			'banques' => array('ETAT DES BANQUES', '', 'resources/table_icons/card_bank.png', 'GESTION DU PERSONNEL'),
			'etat_banques' => array('LISTE ETAT BANQUES', '', 'resources/table_icons/card_bank.png', 'GESTION DU PERSONNEL'),
			'pointages' => array('FICHE DE POINTAGES', '', 'resources/table_icons/session_idle_time.png', 'GESTION DU PERSONNEL'),
			'fiche_pointages' => array('LISTE POINTAGES', '', 'resources/table_icons/session_idle_time.png', 'GESTION DU PERSONNEL'),
			'permissions' => array('NOTE DE SERVICE DE PERMISSIONS', '', 'resources/table_icons/folder_page.png', 'GESTION DU PERSONNEL'),
			'conges' => array('NOTE DE SERVICE DE CONGE', '', 'resources/table_icons/folder_page_white.png', 'GESTION DU PERSONNEL'),
			'demandes_permission' => array('DEMANDES PERMISSION', '', 'resources/table_icons/folder_table.png', 'DECISION MINISTRE'),
			'demandes_conge_m' => array('DEMANDES CONGE', '', 'resources/table_icons/folder_table.png', 'DECISION MINISTRE'),
			'demandes_permissions' => array('DEMANDES PERMISSION', '', 'resources/table_icons/folder_table.png', 'DECISION SG'),
			'demandes_conge_sg' => array('DEMANDES CONGE', '', 'resources/table_icons/folder_table.png', 'DECISION SG'),
			'conges_permissions' => array('RETRAITES', '', 'resources/table_icons/folder_palette.png', 'GESTION DU PERSONNEL'),
			'demandes_conge' => array('CONGES', '', 'resources/table_icons/folder_table.png', 'GESTION DU PERSONNEL'),
			'cursus' => array('CURSUS', '', 'resources/table_icons/books.png', 'GESTION DU PERSONNEL'),
			'formations' => array('FORMATIONS', '', 'resources/table_icons/books.png', 'GESTION DU PERSONNEL'),
			'informations' => array('EXPERIENCES', '', 'resources/table_icons/chart_pie_alternative.png', 'GESTION DU PERSONNEL'),
			'carrieres' => array('CARRIERES ', '', 'resources/table_icons/award_star_gold_3.png', 'GESTION DU PERSONNEL'),
			'demandes' => array('DEMANDES', '', 'resources/table_icons/folder_page.png', 'GESTION DU PERSONNEL'),
			'fonctions' => array('FONCTIONS', '', 'resources/table_icons/folder_edit.png', 'PARAMETRAGES'),
			'directions' => array('DIRECTIONS', '', 'resources/table_icons/folder.png', 'PARAMETRAGES'),
			'services' => array('SERVICES', '', 'resources/table_icons/folders.png', 'PARAMETRAGES'),
			'divisions' => array('DIVISIONS', '', 'resources/table_icons/folders_explorer.png', 'PARAMETRAGES'),
			'les_banques' => array('BANQUES', '', 'resources/table_icons/card_bank.png', 'PARAMETRAGES'),
			'carrieres_option' => array('LISTE CARRIERES', '', 'resources/table_icons/award_star_silver_3.png', 'PARAMETRAGES'),
			'statut' => array('STATUT', '', 'resources/table_icons/folder_star.png', 'PARAMETRAGES'),
			'missions' => array('ORDRE MISSION', '', 'resources/table_icons/folder_edit.png', 'GESTION DU PERSONNEL'),
			'ordres_missions' => array('LISTE ORDRE DE MISSION', '', 'resources/table_icons/folder_edit.png', 'GESTION DU PERSONNEL'),
			'decomptes' => array('DECOMPTES', '', 'resources/table_icons/folder_table.png', 'GESTION DU PERSONNEL'),
			'details_decompte' => array('DETAILS DECOMPTE', '', 'resources/table_icons/folder_error.png', 'GESTION DU PERSONNEL'),
			'incomes' => array('GESTION JOUR CONGE', '', 'resources/table_icons/folder_add.png', 'PARAMETRAGES'),
			'expenses' => array('GESTION DES CONGES & PERMISSIONS', '', 'resources/table_icons/folder_delete.png', 'GESTION DES CONGES'),
			'categorie_prime' => array('GESTION DES PRIMES', '', 'resources/table_icons/folder_heart.png', 'PARAMETRAGES'),
			'curriculum_vitae' => array('CURRICULUM VITAE', '', 'resources/table_icons/client_account_template.png', 'GESTION DU PERSONNEL')
		);
		if($skip_authentication || getLoggedAdmin()) return $arrTables;

		if(is_array($arrTables)){
			foreach($arrTables as $tn => $tc){
				$arrPerm = getTablePermissions($tn);
				if($arrPerm[0]){
					$arrAccessTables[$tn] = $tc;
				}
			}
		}

		return $arrAccessTables;
	}

	#########################################################

	function get_table_groups($skip_authentication = false){
		$tables = getTableList($skip_authentication);
		$all_groups = array('GESTION DU PERSONNEL', 'GESTION DES CONGES', 'DECISION MINISTRE', 'DECISION SG', 'PARAMETRAGES');

		$groups = array();
		foreach($all_groups as $grp){
			foreach($tables as $tn => $td){
				if($td[3] && $td[3] == $grp) $groups[$grp][] = $tn;
				if(!$td[3]) $groups[0][] = $tn;
			}
		}

		return $groups;
	}

	#########################################################

	function getTablePermissions($tn){
		static $table_permissions = array();
		if(isset($table_permissions[$tn])) return $table_permissions[$tn];

		$groupID = getLoggedGroupID();
		$memberID = makeSafe(getLoggedMemberID());
		$res_group = sql("select tableName, allowInsert, allowView, allowEdit, allowDelete from membership_grouppermissions where groupID='{$groupID}'", $eo);
		$res_user = sql("select tableName, allowInsert, allowView, allowEdit, allowDelete from membership_userpermissions where lcase(memberID)='{$memberID}'", $eo);

		while($row = db_fetch_assoc($res_group)){
			$table_permissions[$row['tableName']] = array(
				1 => intval($row['allowInsert']),
				2 => intval($row['allowView']),
				3 => intval($row['allowEdit']),
				4 => intval($row['allowDelete']),
				'insert' => intval($row['allowInsert']),
				'view' => intval($row['allowView']),
				'edit' => intval($row['allowEdit']),
				'delete' => intval($row['allowDelete'])
			);
		}

		// user-specific permissions, if specified, overwrite his group permissions
		while($row = db_fetch_assoc($res_user)){
			$table_permissions[$row['tableName']] = array(
				1 => intval($row['allowInsert']),
				2 => intval($row['allowView']),
				3 => intval($row['allowEdit']),
				4 => intval($row['allowDelete']),
				'insert' => intval($row['allowInsert']),
				'view' => intval($row['allowView']),
				'edit' => intval($row['allowEdit']),
				'delete' => intval($row['allowDelete'])
			);
		}

		// if user has any type of access, set 'access' flag
		foreach($table_permissions as $t => $p){
			$table_permissions[$t]['access'] = $table_permissions[$t][0] = false;

			if($p['insert'] || $p['view'] || $p['edit'] || $p['delete']){
				$table_permissions[$t]['access'] = $table_permissions[$t][0] = true;
			}
		}

		return $table_permissions[$tn];
	}

	#########################################################

	function get_sql_fields($table_name){
		$sql_fields = array(   
			'main' => "`main`.`idmain` as 'idmain', `main`.`main` as 'main'",
			'dossier_individuel' => "`dossier_individuel`.`photo` as 'photo', `dossier_individuel`.`matricule` as 'matricule', `dossier_individuel`.`iddindividuel` as 'iddindividuel', `dossier_individuel`.`nom_prenom` as 'nom_prenom', if(`dossier_individuel`.`date_naissance`,date_format(`dossier_individuel`.`date_naissance`,'%d/%m/%Y'),'') as 'date_naissance', `dossier_individuel`.`matrimoniale` as 'matrimoniale', `dossier_individuel`.`enfants` as 'enfants', `dossier_individuel`.`nni` as 'nni', `dossier_individuel`.`telephone` as 'telephone', `dossier_individuel`.`courriel` as 'courriel', `dossier_individuel`.`postale` as 'postale', if(`dossier_individuel`.`date_recrutement`,date_format(`dossier_individuel`.`date_recrutement`,'%d/%m/%Y'),'') as 'date_recrutement', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service', IF(    CHAR_LENGTH(`divisions1`.`division`), CONCAT_WS('',   `divisions1`.`division`), '') as 'division', IF(    CHAR_LENGTH(`categorie_prime1`.`categorie`), CONCAT_WS('',   `categorie_prime1`.`categorie`), '') as 'categorie', IF(    CHAR_LENGTH(`statut1`.`statut`), CONCAT_WS('',   `statut1`.`statut`), '') as 'statut', REPLACE(FORMAT(`dossier_individuel`.`salaire`, 0), ',', ' ') as 'salaire', IF(    CHAR_LENGTH(`categorie_prime1`.`prime`), CONCAT_WS('',   `categorie_prime1`.`prime`), '') as 'prime_ts', `dossier_individuel`.`compte` as 'compte', IF(    CHAR_LENGTH(`les_banques1`.`banque`), CONCAT_WS('',   `les_banques1`.`banque`), '') as 'banque', `dossier_individuel`.`total_income` as 'total_income', `dossier_individuel`.`total_expense` as 'total_expense', `dossier_individuel`.`available_budget` as 'available_budget', `dossier_individuel`.`observation` as 'observation'",
			'gestion_salaire' => "`gestion_salaire`.`idgestion_salaire` as 'idgestion_salaire', if(`gestion_salaire`.`date_salaire`,date_format(`gestion_salaire`.`date_salaire`,'%d/%m/%Y'),'') as 'date_salaire', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', REPLACE(FORMAT(`gestion_salaire`.`salaire`, 0), ',', ' ') as 'salaire', IF(    CHAR_LENGTH(`dossier_individuel1`.`compte`), CONCAT_WS('',   `dossier_individuel1`.`compte`), '') as 'compte', IF(    CHAR_LENGTH(`les_banques1`.`banque`), CONCAT_WS('',   `les_banques1`.`banque`), '') as 'banque', `gestion_salaire`.`mois` as 'mois', `gestion_salaire`.`annee` as 'annee'",
			'gestion_prime' => "`gestion_prime`.`idgestion_prime` as 'idgestion_prime', if(`gestion_prime`.`date_prime`,date_format(`gestion_prime`.`date_prime`,'%d/%m/%Y'),'') as 'date_prime', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', REPLACE(FORMAT(`gestion_prime`.`prime`, 0), ',', ' ') as 'prime', IF(    CHAR_LENGTH(`dossier_individuel1`.`compte`), CONCAT_WS('',   `dossier_individuel1`.`compte`), '') as 'compte', IF(    CHAR_LENGTH(`les_banques1`.`banque`), CONCAT_WS('',   `les_banques1`.`banque`), '') as 'banque', `gestion_prime`.`mois` as 'mois', `gestion_prime`.`annee` as 'annee'",
			'paiements' => "`paiements`.`id_paiement` as 'id_paiement', if(`paiements`.`date`,date_format(`paiements`.`date`,'%d/%m/%Y'),'') as 'date', `paiements`.`du` as 'du', `paiements`.`au` as 'au', `paiements`.`to_etat_paiements` as 'to_etat_paiements', `paiements`.`directeur` as 'directeur'",
			'etat_paiements' => "`etat_paiements`.`id_etat_paiement` as 'id_etat_paiement', IF(    CHAR_LENGTH(`paiements1`.`to_etat_paiements`), CONCAT_WS('',   `paiements1`.`to_etat_paiements`), '') as 'from_paiements', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`dossier_individuel1`.`prime_ts`), CONCAT_WS('',   `dossier_individuel1`.`prime_ts`), '') as 'prime', `etat_paiements`.`montant` as 'montant', `etat_paiements`.`qte` as 'qte', `etat_paiements`.`total` as 'total', IF(    CHAR_LENGTH(`dossier_individuel1`.`compte`), CONCAT_WS('',   `dossier_individuel1`.`compte`), '') as 'n_compte', IF(    CHAR_LENGTH(`les_banques1`.`banque`), CONCAT_WS('',   `les_banques1`.`banque`), '') as 'banque'",
			'banques' => "`banques`.`id_banque` as 'id_banque', `banques`.`to_etat_banques` as 'to_etat_banques', if(`banques`.`date`,date_format(`banques`.`date`,'%d/%m/%Y'),'') as 'date', IF(    CHAR_LENGTH(`les_banques1`.`banque`), CONCAT_WS('',   `les_banques1`.`banque`), '') as 'banque', `banques`.`directeur` as 'directeur'",
			'etat_banques' => "`etat_banques`.`id_etat_banque` as 'id_etat_banque', IF(    CHAR_LENGTH(`banques1`.`to_etat_banques`), CONCAT_WS('',   `banques1`.`to_etat_banques`), '') as 'from_banque', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`dossier_individuel1`.`salaire`), CONCAT_WS('',   `dossier_individuel1`.`salaire`), '') as 'salaire', `etat_banques`.`montant` as 'montant', REPLACE(FORMAT(`etat_banques`.`qte`, 0), ',', ' ') as 'qte', REPLACE(FORMAT(`etat_banques`.`total`, 0), ',', ' ') as 'total', IF(    CHAR_LENGTH(`dossier_individuel1`.`compte`), CONCAT_WS('',   `dossier_individuel1`.`compte`), '') as 'n_compte', IF(    CHAR_LENGTH(`les_banques1`.`banque`), CONCAT_WS('',   `les_banques1`.`banque`), '') as 'banque'",
			'pointages' => "`pointages`.`id_pointages` as 'id_pointages', `pointages`.`to_fiche_pointage` as 'to_fiche_pointage', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'structure', `pointages`.`mois` as 'mois'",
			'fiche_pointages' => "`fiche_pointages`.`id_fiche_pointage` as 'id_fiche_pointage', IF(    CHAR_LENGTH(`pointages1`.`to_fiche_pointage`), CONCAT_WS('',   `pointages1`.`to_fiche_pointage`), '') as 'from_pointage', if(`fiche_pointages`.`date_fiche_pointage`,date_format(`fiche_pointages`.`date_fiche_pointage`,'%d/%m/%Y'),'') as 'date_fiche_pointage', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom_fp', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction_fp', IF(    CHAR_LENGTH(`dossier_individuel1`.`nni`), CONCAT_WS('',   `dossier_individuel1`.`nni`), '') as 'nni_fp', `fiche_pointages`.`heure_arrive` as 'heure_arrive', `fiche_pointages`.`heure_depart` as 'heure_depart', `fiche_pointages`.`emargement` as 'emargement'",
			'permissions' => "`permissions`.`id_permission` as 'id_permission', `permissions`.`numero` as 'numero', if(`permissions`.`date`,date_format(`permissions`.`date`,'%d/%m/%Y'),'') as 'date', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service', IF(    CHAR_LENGTH(`divisions1`.`division`), CONCAT_WS('',   `divisions1`.`division`), '') as 'division', `permissions`.`num_fiche` as 'num_fiche', `permissions`.`nature` as 'nature', if(`permissions`.`date_debut`,date_format(`permissions`.`date_debut`,'%d/%m/%Y'),'') as 'date_debut', if(`permissions`.`date_fin`,date_format(`permissions`.`date_fin`,'%d/%m/%Y'),'') as 'date_fin', `permissions`.`jours` as 'jours', `permissions`.`directeur` as 'directeur'",
			'conges' => "`conges`.`id_conge` as 'id_conge', `conges`.`numero` as 'numero', if(`conges`.`date`,date_format(`conges`.`date`,'%d/%m/%Y'),'') as 'date', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service', `conges`.`num_fiche` as 'num_fiche', `conges`.`nature` as 'nature', if(`conges`.`date_debut`,date_format(`conges`.`date_debut`,'%d/%m/%Y'),'') as 'date_debut', if(`conges`.`date_fin`,date_format(`conges`.`date_fin`,'%d/%m/%Y'),'') as 'date_fin', `conges`.`jours` as 'jours', `conges`.`directeur` as 'directeur'",
			'demandes_permission' => "`demandes_permission`.`iddemande_permission` as 'iddemande_permission', if(`demandes_permission`.`date_demande_p`,date_format(`demandes_permission`.`date_demande_p`,'%d/%m/%Y'),'') as 'date_demande_p', `demandes_permission`.`numero` as 'numero', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), CONCAT_WS('',   if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), '') as 'date_recrutement', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`divisions1`.`division`), CONCAT_WS('',   `divisions1`.`division`), '') as 'division', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service', if(`demandes_permission`.`depart`,date_format(`demandes_permission`.`depart`,'%d/%m/%Y'),'') as 'depart', if(`demandes_permission`.`retour`,date_format(`demandes_permission`.`retour`,'%d/%m/%Y'),'') as 'retour', `demandes_permission`.`raison` as 'raison', if(`demandes_permission`.`date_dernier_permission`,date_format(`demandes_permission`.`date_dernier_permission`,'%d/%m/%Y'),'') as 'date_dernier_permission', `demandes_permission`.`jours` as 'jours', `demandes_permission`.`adresse_permission` as 'adresse_permission', `demandes_permission`.`justificatif_permission` as 'justificatif_permission', `demandes_permission`.`observation` as 'observation'",
			'demandes_conge_m' => "`demandes_conge_m`.`iddemande_permission` as 'iddemande_permission', if(`demandes_conge_m`.`date_demande_p`,date_format(`demandes_conge_m`.`date_demande_p`,'%d/%m/%Y'),'') as 'date_demande_p', `demandes_conge_m`.`numero` as 'numero', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), CONCAT_WS('',   if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), '') as 'date_recrutement', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`divisions1`.`division`), CONCAT_WS('',   `divisions1`.`division`), '') as 'division', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service', if(`demandes_conge_m`.`depart`,date_format(`demandes_conge_m`.`depart`,'%d/%m/%Y'),'') as 'depart', if(`demandes_conge_m`.`retour`,date_format(`demandes_conge_m`.`retour`,'%d/%m/%Y'),'') as 'retour', `demandes_conge_m`.`raison` as 'raison', if(`demandes_conge_m`.`date_dernier_permission`,date_format(`demandes_conge_m`.`date_dernier_permission`,'%d/%m/%Y'),'') as 'date_dernier_permission', `demandes_conge_m`.`jours` as 'jours', `demandes_conge_m`.`adresse_permission` as 'adresse_permission', `demandes_conge_m`.`justificatif_permission` as 'justificatif_permission', `demandes_conge_m`.`observation` as 'observation'",
			'demandes_permissions' => "`demandes_permissions`.`iddemande_permission` as 'iddemande_permission', if(`demandes_permissions`.`date_demande_p`,date_format(`demandes_permissions`.`date_demande_p`,'%d/%m/%Y'),'') as 'date_demande_p', `demandes_permissions`.`numero` as 'numero', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), CONCAT_WS('',   if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), '') as 'date_recrutement', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`divisions1`.`division`), CONCAT_WS('',   `divisions1`.`division`), '') as 'division', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service', if(`demandes_permissions`.`depart`,date_format(`demandes_permissions`.`depart`,'%d/%m/%Y'),'') as 'depart', if(`demandes_permissions`.`retour`,date_format(`demandes_permissions`.`retour`,'%d/%m/%Y'),'') as 'retour', `demandes_permissions`.`raison` as 'raison', if(`demandes_permissions`.`date_dernier_permission`,date_format(`demandes_permissions`.`date_dernier_permission`,'%d/%m/%Y'),'') as 'date_dernier_permission', `demandes_permissions`.`jours` as 'jours', `demandes_permissions`.`adresse_permission` as 'adresse_permission', `demandes_permissions`.`justificatif_permission` as 'justificatif_permission', `demandes_permissions`.`observation` as 'observation'",
			'demandes_conge_sg' => "`demandes_conge_sg`.`iddemande_permission` as 'iddemande_permission', if(`demandes_conge_sg`.`date_demande_p`,date_format(`demandes_conge_sg`.`date_demande_p`,'%d/%m/%Y'),'') as 'date_demande_p', `demandes_conge_sg`.`numero` as 'numero', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), CONCAT_WS('',   if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), '') as 'date_recrutement', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`divisions1`.`division`), CONCAT_WS('',   `divisions1`.`division`), '') as 'division', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service', if(`demandes_conge_sg`.`depart`,date_format(`demandes_conge_sg`.`depart`,'%d/%m/%Y'),'') as 'depart', if(`demandes_conge_sg`.`retour`,date_format(`demandes_conge_sg`.`retour`,'%d/%m/%Y'),'') as 'retour', `demandes_conge_sg`.`raison` as 'raison', if(`demandes_conge_sg`.`date_dernier_permission`,date_format(`demandes_conge_sg`.`date_dernier_permission`,'%d/%m/%Y'),'') as 'date_dernier_permission', `demandes_conge_sg`.`jours` as 'jours', `demandes_conge_sg`.`adresse_permission` as 'adresse_permission', `demandes_conge_sg`.`justificatif_permission` as 'justificatif_permission', `demandes_conge_sg`.`observation` as 'observation'",
			'conges_permissions' => "`conges_permissions`.`idconge_permission` as 'idconge_permission', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', if(`conges_permissions`.`date_naissance`,date_format(`conges_permissions`.`date_naissance`,'%d/%m/%Y'),'') as 'date_naissance', if(`conges_permissions`.`date_recrutement_cp`,date_format(`conges_permissions`.`date_recrutement_cp`,'%d/%m/%Y'),'') as 'date_recrutement_cp', `conges_permissions`.`age` as 'age', `conges_permissions`.`annees_service` as 'annees_service', `conges_permissions`.`date_depart_conge` as 'date_depart_conge', `conges_permissions`.`date_depart_retraite` as 'date_depart_retraite'",
			'demandes_conge' => "`demandes_conge`.`idemandes_conge` as 'idemandes_conge', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', if(`demandes_conge`.`date_demande_c`,date_format(`demandes_conge`.`date_demande_c`,'%d/%m/%Y'),'') as 'date_demande_c', `demandes_conge`.`numero_conge` as 'numero_conge', if(`demandes_conge`.`date_depart_souhaite`,date_format(`demandes_conge`.`date_depart_souhaite`,'%d/%m/%Y'),'') as 'date_depart_souhaite', if(`demandes_conge`.`date_retour_conge`,date_format(`demandes_conge`.`date_retour_conge`,'%d/%m/%Y'),'') as 'date_retour_conge', `demandes_conge`.`jours_conge` as 'jours_conge', `demandes_conge`.`adresse_conge` as 'adresse_conge', `demandes_conge`.`justificatif_conge` as 'justificatif_conge', `demandes_conge`.`observation` as 'observation'",
			'cursus' => "`cursus`.`idcursus` as 'idcursus', IF(    CHAR_LENGTH(`informations1`.`idinformation`), CONCAT_WS('',   `informations1`.`idinformation`), '') as 'idinformation', `cursus`.`annee` as 'annee', `cursus`.`duree` as 'duree', `cursus`.`emploi_activite` as 'emploi_activite', `cursus`.`employeur` as 'employeur', `cursus`.`observations` as 'observations'",
			'formations' => "`formations`.`idformation` as 'idformation', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', `formations`.`specialisation` as 'specialisation', `formations`.`annee` as 'annee', if(`formations`.`date`,date_format(`formations`.`date`,'%d/%m/%Y'),'') as 'date', `formations`.`organisme` as 'organisme', `formations`.`lieu` as 'lieu', `formations`.`nature` as 'nature', `formations`.`duree` as 'duree', `formations`.`effets` as 'effets', `formations`.`resultat` as 'resultat', `formations`.`r_c_p` as 'r_c_p'",
			'informations' => "`informations`.`idinformation` as 'idinformation', `informations`.`situation` as 'situation', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`, '   '), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', `informations`.`education` as 'education', `informations`.`diplome_primordial` as 'diplome_primordial', if(`informations`.`date_diplome`,date_format(`informations`.`date_diplome`,'%d/%m/%Y'),'') as 'date_diplome', `informations`.`institution` as 'institution', `informations`.`lieu_diplome` as 'lieu_diplome', `informations`.`diplome_certificat` as 'diplome_certificat', `informations`.`specialite` as 'specialite', `informations`.`institution_specialite` as 'institution_specialite', `informations`.`lieu_specialite` as 'lieu_specialite', `informations`.`langues` as 'langues', `informations`.`arabe` as 'arabe', `informations`.`francais` as 'francais', `informations`.`anglais` as 'anglais', `informations`.`autres1` as 'autres1', `informations`.`autres2` as 'autres2', `informations`.`informatique` as 'informatique', `informations`.`utilisation` as 'utilisation', `informations`.`programmation` as 'programmation'",
			'carrieres' => "`carrieres`.`idcarriere` as 'idcarriere', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', if(`carrieres`.`date`,date_format(`carrieres`.`date`,'%d/%m/%Y'),'') as 'date', IF(    CHAR_LENGTH(`carrieres_option1`.`description`), CONCAT_WS('',   `carrieres_option1`.`description`), '') as 'r_n_m_a_p_d', `carrieres`.`statut` as 'statut'",
			'demandes' => "`demandes`.`iddemande` as 'iddemande', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', `demandes`.`numero` as 'numero', if(`demandes`.`date_demande`,date_format(`demandes`.`date_demande`,'%d/%m/%Y'),'') as 'date_demande', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), CONCAT_WS('',   if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), '') as 'recrutement', if(`demandes`.`date_depart_souhaite`,date_format(`demandes`.`date_depart_souhaite`,'%d/%m/%Y'),'') as 'date_depart_souhaite', if(`demandes`.`date_depart_dernier`,date_format(`demandes`.`date_depart_dernier`,'%d/%m/%Y'),'') as 'date_depart_dernier', if(`demandes`.`date_reprise`,date_format(`demandes`.`date_reprise`,'%d/%m/%Y'),'') as 'date_reprise', `demandes`.`raison_permission` as 'raison_permission', `demandes`.`adresse_abscence` as 'adresse_abscence', `demandes`.`nbre_jour` as 'nbre_jour', `demandes`.`pj_conge` as 'pj_conge'",
			'fonctions' => "`fonctions`.`idfonction` as 'idfonction', `fonctions`.`fonction` as 'fonction'",
			'directions' => "`directions`.`iddirection` as 'iddirection', `directions`.`from_services` as 'from_services', `directions`.`from_division` as 'from_division', `directions`.`direction` as 'direction'",
			'services' => "`services`.`idservice` as 'idservice', `services`.`service` as 'service', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'from_direction'",
			'divisions' => "`divisions`.`iddivision` as 'iddivision', `divisions`.`division` as 'division', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'to_services'",
			'les_banques' => "`les_banques`.`id` as 'id', `les_banques`.`banque` as 'banque'",
			'carrieres_option' => "`carrieres_option`.`idoption` as 'idoption', `carrieres_option`.`description` as 'description'",
			'statut' => "`statut`.`idstatut` as 'idstatut', `statut`.`statut` as 'statut'",
			'missions' => "`missions`.`id_mission` as 'id_mission', `missions`.`to_mission` as 'to_mission', if(`missions`.`date`,date_format(`missions`.`date`,'%d/%m/%Y'),'') as 'date', `missions`.`directeur` as 'directeur', `missions`.`destination` as 'destination', `missions`.`motif` as 'motif', if(`missions`.`date_depart`,date_format(`missions`.`date_depart`,'%d/%m/%Y'),'') as 'date_depart', if(`missions`.`date_retour`,date_format(`missions`.`date_retour`,'%d/%m/%Y'),'') as 'date_retour', `missions`.`moyen` as 'moyen'",
			'ordres_missions' => "`ordres_missions`.`id_ordre_mission` as 'id_ordre_mission', IF(    CHAR_LENGTH(`missions1`.`to_mission`), CONCAT_WS('',   `missions1`.`to_mission`), '') as 'from_mission', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service'",
			'decomptes' => "`decomptes`.`iddecompte` as 'iddecompte', if(`decomptes`.`date_demande_c`,date_format(`decomptes`.`date_demande_c`,'%d/%m/%Y'),'') as 'date_demande_c', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`dossier_individuel1`.`nni`), CONCAT_WS('',   `dossier_individuel1`.`nni`), '') as 'nni', IF(    CHAR_LENGTH(`dossier_individuel1`.`compte`) || CHAR_LENGTH(`les_banques1`.`banque`), CONCAT_WS('',   `dossier_individuel1`.`compte`, '   /   ', `les_banques1`.`banque`), '') as 'num_cpte', if(`decomptes`.`date_recrutement`,date_format(`decomptes`.`date_recrutement`,'%d/%m/%Y'),'') as 'date_recrutement', if(`decomptes`.`date_depart`,date_format(`decomptes`.`date_depart`,'%d/%m/%Y'),'') as 'date_depart', `decomptes`.`anciennete` as 'anciennete', REPLACE(FORMAT(`decomptes`.`salaire_m_m`, 0), ',', ' ') as 'salaire_m_m', `decomptes`.`from_details` as 'from_details'",
			'details_decompte' => "`details_decompte`.`iddetail_decompte` as 'iddetail_decompte', `details_decompte`.`tranches` as 'tranches', `details_decompte`.`periodes` as 'periodes', `details_decompte`.`pourcentage` as 'pourcentage', `details_decompte`.`coef` as 'coef', `details_decompte`.`salaire_moyen` as 'salaire_moyen', `details_decompte`.`montant` as 'montant', `details_decompte`.`mois_preavis` as 'mois_preavis', `details_decompte`.`mois_conge` as 'mois_conge', IF(    CHAR_LENGTH(`decomptes1`.`iddecompte`), CONCAT_WS('',   `decomptes1`.`iddecompte`), '') as 'to_decomptes'",
			'incomes' => "`incomes`.`income_id` as 'income_id', if(`incomes`.`date`,date_format(`incomes`.`date`,'%d/%m/%Y'),'') as 'date', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'client', `incomes`.`amount` as 'amount'",
			'expenses' => "`expenses`.`idexpense` as 'idexpense', `expenses`.`type` as 'type', `expenses`.`nature` as 'nature', if(`expenses`.`date_saisie`,date_format(`expenses`.`date_saisie`,'%d/%m/%Y'),'') as 'date_saisie', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'client', IF(    CHAR_LENGTH(if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), CONCAT_WS('',   if(`dossier_individuel1`.`date_recrutement`,date_format(`dossier_individuel1`.`date_recrutement`,'%d/%m/%Y'),'')), '') as 'date_recrutement', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', IF(    CHAR_LENGTH(`divisions1`.`division`), CONCAT_WS('',   `divisions1`.`division`), '') as 'division', IF(    CHAR_LENGTH(`services1`.`service`), CONCAT_WS('',   `services1`.`service`), '') as 'service', IF(    CHAR_LENGTH(`directions1`.`direction`), CONCAT_WS('',   `directions1`.`direction`), '') as 'direction', `expenses`.`amount` as 'amount', if(`expenses`.`date_depart`,date_format(`expenses`.`date_depart`,'%d/%m/%Y'),'') as 'date_depart', if(`expenses`.`date_retour`,date_format(`expenses`.`date_retour`,'%d/%m/%Y'),'') as 'date_retour', if(`expenses`.`date_depart_derniere`,date_format(`expenses`.`date_depart_derniere`,'%d/%m/%Y'),'') as 'date_depart_derniere', `expenses`.`adresse_absence` as 'adresse_absence', `expenses`.`departement` as 'departement', `expenses`.`signataire` as 'signataire'",
			'categorie_prime' => "`categorie_prime`.`id_categorie_prime` as 'id_categorie_prime', `categorie_prime`.`categorie` as 'categorie', REPLACE(FORMAT(`categorie_prime`.`prime`, 0), ',', ' ') as 'prime'",
			'curriculum_vitae' => "`curriculum_vitae`.`idcv` as 'idcv', IF(    CHAR_LENGTH(`dossier_individuel1`.`matricule`), CONCAT_WS('',   `dossier_individuel1`.`matricule`), '') as 'matricule', IF(    CHAR_LENGTH(`dossier_individuel1`.`nom_prenom`), CONCAT_WS('',   `dossier_individuel1`.`nom_prenom`), '') as 'nom_prenom', IF(    CHAR_LENGTH(`fonctions1`.`fonction`), CONCAT_WS('',   `fonctions1`.`fonction`), '') as 'fonction', `curriculum_vitae`.`curriculum_vitae` as 'curriculum_vitae'"
		);

		if(isset($sql_fields[$table_name])){
			return $sql_fields[$table_name];
		}

		return false;
	}

	#########################################################

	function get_sql_from($table_name, $skip_permissions = false, $skip_joins = false) {
		$sql_from = array(   
			'main' => "`main` ",
			'dossier_individuel' => "`dossier_individuel` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel`.`fonction` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel`.`direction` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel`.`service` LEFT JOIN `divisions` as divisions1 ON `divisions1`.`iddivision`=`dossier_individuel`.`division` LEFT JOIN `categorie_prime` as categorie_prime1 ON `categorie_prime1`.`id_categorie_prime`=`dossier_individuel`.`categorie` LEFT JOIN `statut` as statut1 ON `statut1`.`idstatut`=`dossier_individuel`.`statut` LEFT JOIN `les_banques` as les_banques1 ON `les_banques1`.`id`=`dossier_individuel`.`banque` ",
			'gestion_salaire' => "`gestion_salaire` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`gestion_salaire`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `les_banques` as les_banques1 ON `les_banques1`.`id`=`dossier_individuel1`.`banque` ",
			'gestion_prime' => "`gestion_prime` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`gestion_prime`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `les_banques` as les_banques1 ON `les_banques1`.`id`=`dossier_individuel1`.`banque` ",
			'paiements' => "`paiements` ",
			'etat_paiements' => "`etat_paiements` LEFT JOIN `paiements` as paiements1 ON `paiements1`.`id_paiement`=`etat_paiements`.`from_paiements` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`etat_paiements`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `les_banques` as les_banques1 ON `les_banques1`.`id`=`dossier_individuel1`.`banque` ",
			'banques' => "`banques` LEFT JOIN `les_banques` as les_banques1 ON `les_banques1`.`id`=`banques`.`banque` ",
			'etat_banques' => "`etat_banques` LEFT JOIN `banques` as banques1 ON `banques1`.`id_banque`=`etat_banques`.`from_banque` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`etat_banques`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `les_banques` as les_banques1 ON `les_banques1`.`id`=`dossier_individuel1`.`banque` ",
			'pointages' => "`pointages` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`pointages`.`structure` ",
			'fiche_pointages' => "`fiche_pointages` LEFT JOIN `pointages` as pointages1 ON `pointages1`.`id_pointages`=`fiche_pointages`.`from_pointage` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`fiche_pointages`.`nom_prenom_fp` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` ",
			'permissions' => "`permissions` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`permissions`.`nom_prenom` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel1`.`direction` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel1`.`service` LEFT JOIN `divisions` as divisions1 ON `divisions1`.`iddivision`=`dossier_individuel1`.`division` ",
			'conges' => "`conges` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`conges`.`nom_prenom` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel1`.`direction` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel1`.`service` ",
			'demandes_permission' => "`demandes_permission` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`demandes_permission`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `divisions` as divisions1 ON `divisions1`.`iddivision`=`dossier_individuel1`.`division` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel1`.`direction` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel1`.`service` ",
			'demandes_conge_m' => "`demandes_conge_m` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`demandes_conge_m`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `divisions` as divisions1 ON `divisions1`.`iddivision`=`dossier_individuel1`.`division` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel1`.`direction` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel1`.`service` ",
			'demandes_permissions' => "`demandes_permissions` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`demandes_permissions`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `divisions` as divisions1 ON `divisions1`.`iddivision`=`dossier_individuel1`.`division` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel1`.`direction` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel1`.`service` ",
			'demandes_conge_sg' => "`demandes_conge_sg` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`demandes_conge_sg`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `divisions` as divisions1 ON `divisions1`.`iddivision`=`dossier_individuel1`.`division` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel1`.`direction` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel1`.`service` ",
			'conges_permissions' => "`conges_permissions` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`conges_permissions`.`matricule` ",
			'demandes_conge' => "`demandes_conge` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`demandes_conge`.`matricule` ",
			'cursus' => "`cursus` LEFT JOIN `informations` as informations1 ON `informations1`.`idinformation`=`cursus`.`idinformation` ",
			'formations' => "`formations` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`formations`.`matricule` ",
			'informations' => "`informations` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`informations`.`matricule` ",
			'carrieres' => "`carrieres` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`carrieres`.`matricule` LEFT JOIN `carrieres_option` as carrieres_option1 ON `carrieres_option1`.`idoption`=`carrieres`.`r_n_m_a_p_d` ",
			'demandes' => "`demandes` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`demandes`.`matricule` ",
			'fonctions' => "`fonctions` ",
			'directions' => "`directions` ",
			'services' => "`services` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`services`.`from_direction` ",
			'divisions' => "`divisions` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`divisions`.`to_services` ",
			'les_banques' => "`les_banques` ",
			'carrieres_option' => "`carrieres_option` ",
			'statut' => "`statut` ",
			'missions' => "`missions` ",
			'ordres_missions' => "`ordres_missions` LEFT JOIN `missions` as missions1 ON `missions1`.`id_mission`=`ordres_missions`.`from_mission` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`ordres_missions`.`nom_prenom` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel1`.`direction` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel1`.`service` ",
			'decomptes' => "`decomptes` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`decomptes`.`matricule` LEFT JOIN `les_banques` as les_banques1 ON `les_banques1`.`id`=`dossier_individuel1`.`banque` ",
			'details_decompte' => "`details_decompte` LEFT JOIN `decomptes` as decomptes1 ON `decomptes1`.`iddecompte`=`details_decompte`.`to_decomptes` ",
			'incomes' => "`incomes` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`incomes`.`client` ",
			'expenses' => "`expenses` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`expenses`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` LEFT JOIN `divisions` as divisions1 ON `divisions1`.`iddivision`=`dossier_individuel1`.`division` LEFT JOIN `services` as services1 ON `services1`.`idservice`=`dossier_individuel1`.`service` LEFT JOIN `directions` as directions1 ON `directions1`.`iddirection`=`dossier_individuel1`.`direction` ",
			'categorie_prime' => "`categorie_prime` ",
			'curriculum_vitae' => "`curriculum_vitae` LEFT JOIN `dossier_individuel` as dossier_individuel1 ON `dossier_individuel1`.`iddindividuel`=`curriculum_vitae`.`matricule` LEFT JOIN `fonctions` as fonctions1 ON `fonctions1`.`idfonction`=`dossier_individuel1`.`fonction` "
		);

		$pkey = array(   
			'main' => 'idmain',
			'dossier_individuel' => 'iddindividuel',
			'gestion_salaire' => 'idgestion_salaire',
			'gestion_prime' => 'idgestion_prime',
			'paiements' => 'id_paiement',
			'etat_paiements' => 'id_etat_paiement',
			'banques' => 'id_banque',
			'etat_banques' => 'id_etat_banque',
			'pointages' => 'id_pointages',
			'fiche_pointages' => 'id_fiche_pointage',
			'permissions' => 'id_permission',
			'conges' => 'id_conge',
			'demandes_permission' => 'iddemande_permission',
			'demandes_conge_m' => 'iddemande_permission',
			'demandes_permissions' => 'iddemande_permission',
			'demandes_conge_sg' => 'iddemande_permission',
			'conges_permissions' => 'idconge_permission',
			'demandes_conge' => 'idemandes_conge',
			'cursus' => 'idcursus',
			'formations' => 'idformation',
			'informations' => 'idinformation',
			'carrieres' => 'idcarriere',
			'demandes' => 'iddemande',
			'fonctions' => 'idfonction',
			'directions' => 'iddirection',
			'services' => 'idservice',
			'divisions' => 'iddivision',
			'les_banques' => 'id',
			'carrieres_option' => 'idoption',
			'statut' => 'idstatut',
			'missions' => 'id_mission',
			'ordres_missions' => 'id_ordre_mission',
			'decomptes' => 'iddecompte',
			'details_decompte' => 'iddetail_decompte',
			'incomes' => 'income_id',
			'expenses' => 'idexpense',
			'categorie_prime' => 'id_categorie_prime',
			'curriculum_vitae' => 'idcv'
		);

		if(!isset($sql_from[$table_name])) return false;

		$from = ($skip_joins ? "`{$table_name}`" : $sql_from[$table_name]);

		if($skip_permissions) return $from . ' WHERE 1=1';

		// mm: build the query based on current member's permissions
		$perm = getTablePermissions($table_name);
		if($perm[2] == 1){ // view owner only
			$from .= ", membership_userrecords WHERE `{$table_name}`.`{$pkey[$table_name]}`=membership_userrecords.pkValue and membership_userrecords.tableName='{$table_name}' and lcase(membership_userrecords.memberID)='" . getLoggedMemberID() . "'";
		}elseif($perm[2] == 2){ // view group only
			$from .= ", membership_userrecords WHERE `{$table_name}`.`{$pkey[$table_name]}`=membership_userrecords.pkValue and membership_userrecords.tableName='{$table_name}' and membership_userrecords.groupID='" . getLoggedGroupID() . "'";
		}elseif($perm[2] == 3){ // view all
			$from .= ' WHERE 1=1';
		}else{ // view none
			return false;
		}

		return $from;
	}

	#########################################################

	function get_joined_record($table, $id, $skip_permissions = false){
		$sql_fields = get_sql_fields($table);
		$sql_from = get_sql_from($table, $skip_permissions);

		if(!$sql_fields || !$sql_from) return false;

		$pk = getPKFieldName($table);
		if(!$pk) return false;

		$safe_id = makeSafe($id, false);
		$sql = "SELECT {$sql_fields} FROM {$sql_from} AND `{$table}`.`{$pk}`='{$safe_id}'";
		$eo['silentErrors'] = true;
		$res = sql($sql, $eo);
		if($row = db_fetch_assoc($res)) return $row;

		return false;
	}

	#########################################################

	function get_defaults($table){
		/* array of tables and their fields, with default values (or empty), excluding automatic values */
		$defaults = array(
			'main' => array(
				'idmain' => '',
				'main' => ''
			),
			'dossier_individuel' => array(
				'photo' => '',
				'matricule' => '',
				'iddindividuel' => '',
				'nom_prenom' => '',
				'date_naissance' => '1',
				'matrimoniale' => '',
				'enfants' => '',
				'nni' => '',
				'telephone' => '',
				'courriel' => '',
				'postale' => '',
				'date_recrutement' => '1',
				'fonction' => '',
				'direction' => '',
				'service' => '',
				'division' => '',
				'categorie' => '',
				'statut' => '',
				'salaire' => '',
				'prime_ts' => '',
				'compte' => '',
				'banque' => '',
				'total_income' => '0',
				'total_expense' => '0',
				'available_budget' => '0',
				'observation' => ''
			),
			'gestion_salaire' => array(
				'idgestion_salaire' => '',
				'date_salaire' => '1',
				'matricule' => '',
				'nom_prenom' => '',
				'fonction' => '',
				'salaire' => '',
				'compte' => '',
				'banque' => '',
				'mois' => '',
				'annee' => ''
			),
			'gestion_prime' => array(
				'idgestion_prime' => '',
				'date_prime' => '1',
				'matricule' => '',
				'nom_prenom' => '',
				'fonction' => '',
				'prime' => '',
				'compte' => '',
				'banque' => '',
				'mois' => '',
				'annee' => ''
			),
			'paiements' => array(
				'id_paiement' => '',
				'date' => '1',
				'du' => '',
				'au' => '',
				'to_etat_paiements' => '',
				'directeur' => ''
			),
			'etat_paiements' => array(
				'id_etat_paiement' => '',
				'from_paiements' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'fonction' => '',
				'prime' => '',
				'montant' => '0',
				'qte' => '1',
				'total' => '',
				'n_compte' => '',
				'banque' => ''
			),
			'banques' => array(
				'id_banque' => '',
				'to_etat_banques' => '',
				'date' => '1',
				'banque' => '',
				'directeur' => ''
			),
			'etat_banques' => array(
				'id_etat_banque' => '',
				'from_banque' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'fonction' => '',
				'salaire' => '',
				'montant' => '0',
				'qte' => '1',
				'total' => '',
				'n_compte' => '',
				'banque' => ''
			),
			'pointages' => array(
				'id_pointages' => '',
				'to_fiche_pointage' => '',
				'structure' => '',
				'mois' => ''
			),
			'fiche_pointages' => array(
				'id_fiche_pointage' => '',
				'from_pointage' => '',
				'date_fiche_pointage' => '1',
				'nom_prenom_fp' => '',
				'fonction_fp' => '',
				'nni_fp' => '',
				'heure_arrive' => '',
				'heure_depart' => '',
				'emargement' => ''
			),
			'permissions' => array(
				'id_permission' => '',
				'numero' => '',
				'date' => '1',
				'nom_prenom' => '',
				'matricule' => '',
				'fonction' => '',
				'direction' => '',
				'service' => '',
				'division' => '',
				'num_fiche' => '',
				'nature' => '',
				'date_debut' => '1',
				'date_fin' => '1',
				'jours' => '',
				'directeur' => ''
			),
			'conges' => array(
				'id_conge' => '',
				'numero' => '',
				'date' => '1',
				'nom_prenom' => '',
				'matricule' => '',
				'fonction' => '',
				'direction' => '',
				'service' => '',
				'num_fiche' => '',
				'nature' => '',
				'date_debut' => '1',
				'date_fin' => '1',
				'jours' => '',
				'directeur' => ''
			),
			'demandes_permission' => array(
				'iddemande_permission' => '',
				'date_demande_p' => '1',
				'numero' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'date_recrutement' => '',
				'fonction' => '',
				'division' => '',
				'direction' => '',
				'service' => '',
				'depart' => '1',
				'retour' => '1',
				'raison' => '',
				'date_dernier_permission' => '',
				'jours' => '',
				'adresse_permission' => '',
				'justificatif_permission' => '',
				'observation' => ''
			),
			'demandes_conge_m' => array(
				'iddemande_permission' => '',
				'date_demande_p' => '1',
				'numero' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'date_recrutement' => '',
				'fonction' => '',
				'division' => '',
				'direction' => '',
				'service' => '',
				'depart' => '1',
				'retour' => '1',
				'raison' => '',
				'date_dernier_permission' => '',
				'jours' => '',
				'adresse_permission' => '',
				'justificatif_permission' => '',
				'observation' => ''
			),
			'demandes_permissions' => array(
				'iddemande_permission' => '',
				'date_demande_p' => '1',
				'numero' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'date_recrutement' => '',
				'fonction' => '',
				'division' => '',
				'direction' => '',
				'service' => '',
				'depart' => '1',
				'retour' => '1',
				'raison' => '',
				'date_dernier_permission' => '',
				'jours' => '',
				'adresse_permission' => '',
				'justificatif_permission' => '',
				'observation' => ''
			),
			'demandes_conge_sg' => array(
				'iddemande_permission' => '',
				'date_demande_p' => '1',
				'numero' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'date_recrutement' => '',
				'fonction' => '',
				'division' => '',
				'direction' => '',
				'service' => '',
				'depart' => '1',
				'retour' => '1',
				'raison' => '',
				'date_dernier_permission' => '',
				'jours' => '',
				'adresse_permission' => '',
				'justificatif_permission' => '',
				'observation' => ''
			),
			'conges_permissions' => array(
				'idconge_permission' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'date_naissance' => '1',
				'date_recrutement_cp' => '1',
				'age' => '',
				'annees_service' => '',
				'date_depart_conge' => '',
				'date_depart_retraite' => ''
			),
			'demandes_conge' => array(
				'idemandes_conge' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'date_demande_c' => '1',
				'numero_conge' => '',
				'date_depart_souhaite' => '1',
				'date_retour_conge' => '',
				'jours_conge' => '',
				'adresse_conge' => '',
				'justificatif_conge' => '',
				'observation' => ''
			),
			'cursus' => array(
				'idcursus' => '',
				'idinformation' => '',
				'annee' => '',
				'duree' => '',
				'emploi_activite' => '',
				'employeur' => '',
				'observations' => ''
			),
			'formations' => array(
				'idformation' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'specialisation' => '',
				'annee' => '',
				'date' => '1',
				'organisme' => '',
				'lieu' => '',
				'nature' => '',
				'duree' => '',
				'effets' => '',
				'resultat' => '',
				'r_c_p' => ''
			),
			'informations' => array(
				'idinformation' => '',
				'situation' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'education' => '',
				'diplome_primordial' => '',
				'date_diplome' => '',
				'institution' => '',
				'lieu_diplome' => '',
				'diplome_certificat' => '',
				'specialite' => '',
				'institution_specialite' => '',
				'lieu_specialite' => '',
				'langues' => '',
				'arabe' => '',
				'francais' => '',
				'anglais' => '',
				'autres1' => '',
				'autres2' => '',
				'informatique' => '',
				'utilisation' => '',
				'programmation' => ''
			),
			'carrieres' => array(
				'idcarriere' => '',
				'matricule' => '',
				'date' => '1',
				'r_n_m_a_p_d' => '',
				'statut' => ''
			),
			'demandes' => array(
				'iddemande' => '',
				'matricule' => '',
				'numero' => '',
				'date_demande' => '1',
				'nom_prenom' => '',
				'recrutement' => '',
				'date_depart_souhaite' => '1',
				'date_depart_dernier' => '',
				'date_reprise' => '',
				'raison_permission' => '',
				'adresse_abscence' => '',
				'nbre_jour' => '',
				'pj_conge' => ''
			),
			'fonctions' => array(
				'idfonction' => '',
				'fonction' => ''
			),
			'directions' => array(
				'iddirection' => '',
				'from_services' => '',
				'from_division' => '',
				'direction' => ''
			),
			'services' => array(
				'idservice' => '',
				'service' => '',
				'from_direction' => ''
			),
			'divisions' => array(
				'iddivision' => '',
				'division' => '',
				'to_services' => ''
			),
			'les_banques' => array(
				'id' => '',
				'banque' => ''
			),
			'carrieres_option' => array(
				'idoption' => '',
				'description' => ''
			),
			'statut' => array(
				'idstatut' => '',
				'statut' => ''
			),
			'missions' => array(
				'id_mission' => '',
				'to_mission' => '',
				'date' => '1',
				'directeur' => '',
				'destination' => '',
				'motif' => '',
				'date_depart' => '1',
				'date_retour' => '1',
				'moyen' => ''
			),
			'ordres_missions' => array(
				'id_ordre_mission' => '',
				'from_mission' => '',
				'nom_prenom' => '',
				'fonction' => '',
				'direction' => '',
				'service' => ''
			),
			'decomptes' => array(
				'iddecompte' => '',
				'date_demande_c' => '1',
				'matricule' => '',
				'nom_prenom' => '',
				'nni' => '',
				'num_cpte' => '',
				'date_recrutement' => '1',
				'date_depart' => '1',
				'anciennete' => '',
				'salaire_m_m' => '',
				'from_details' => ''
			),
			'details_decompte' => array(
				'iddetail_decompte' => '',
				'tranches' => '',
				'periodes' => '',
				'pourcentage' => '',
				'coef' => '',
				'salaire_moyen' => '',
				'montant' => '',
				'mois_preavis' => '',
				'mois_conge' => '',
				'to_decomptes' => ''
			),
			'incomes' => array(
				'income_id' => '',
				'date' => '1',
				'client' => '',
				'amount' => '0'
			),
			'expenses' => array(
				'idexpense' => '',
				'type' => '',
				'nature' => '',
				'date_saisie' => '1',
				'matricule' => '',
				'client' => '',
				'date_recrutement' => '',
				'fonction' => '',
				'division' => '',
				'service' => '',
				'direction' => '',
				'amount' => '0',
				'date_depart' => '1',
				'date_retour' => '1',
				'date_depart_derniere' => '',
				'adresse_absence' => '',
				'departement' => '',
				'signataire' => ''
			),
			'categorie_prime' => array(
				'id_categorie_prime' => '',
				'categorie' => '',
				'prime' => ''
			),
			'curriculum_vitae' => array(
				'idcv' => '',
				'matricule' => '',
				'nom_prenom' => '',
				'fonction' => '',
				'curriculum_vitae' => ''
			)
		);

		return isset($defaults[$table]) ? $defaults[$table] : array();
	}

	#########################################################

	function logInMember(){
		$redir = 'index.php';
		if($_POST['signIn'] != ''){
			if($_POST['username'] != '' && $_POST['password'] != ''){
				$username = makeSafe(strtolower($_POST['username']));
				$hash = sqlValue("select passMD5 from membership_users where lcase(memberID)='{$username}' and isApproved=1 and isBanned=0");
				$password = $_POST['password'];

				if(password_match($password, $hash)) {
					$_SESSION['memberID'] = $username;
					$_SESSION['memberGroupID'] = sqlValue("SELECT `groupID` FROM `membership_users` WHERE LCASE(`memberID`)='{$username}'");

					if($_POST['rememberMe'] == 1){
						RememberMe::login($username);
					}else{
						RememberMe::delete();
					}

					// harden user's password hash
					password_harden($username, $password, $hash);

					// hook: login_ok
					if(function_exists('login_ok')){
						$args=array();
						if(!$redir=login_ok(getMemberInfo(), $args)){
							$redir='index.php';
						}
					}

					redirect($redir);
					exit;
				}
			}

			// hook: login_failed
			if(function_exists('login_failed')){
				$args=array();
				login_failed(array(
					'username' => $_POST['username'],
					'password' => $_POST['password'],
					'IP' => $_SERVER['REMOTE_ADDR']
					), $args);
			}

			if(!headers_sent()) header('HTTP/1.0 403 Forbidden');
			redirect("index.php?loginFailed=1");
			exit;
		}

		/* check if a rememberMe cookie exists and sign in user if so */
		if(RememberMe::check()) {
			$username = makeSafe(strtolower(RememberMe::user()));
			$_SESSION['memberID'] = $username;
			$_SESSION['memberGroupID'] = sqlValue("SELECT `groupID` FROM `membership_users` WHERE LCASE(`memberID`)='{$username}'");
		}
	}

	#########################################################

	function htmlUserBar(){
		global $adminConfig, $Translation;
		if(!defined('PREPEND_PATH')) define('PREPEND_PATH', '');

		ob_start();
		$home_page = (basename($_SERVER['PHP_SELF'])=='index.php' ? true : false);

		?>
		<nav class="navbar navbar-default navbar-fixed-top hidden-print" role="navigation">
			<div class="navbar-header">
				<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
					<span class="sr-only">Toggle navigation</span>
					<span class="icon-bar"></span>
					<span class="icon-bar"></span>
					<span class="icon-bar"></span>
				</button>
				<!-- application title is obtained from the name besides the yellow database icon in AppGini, use underscores for spaces -->
				<a class="navbar-brand" href="<?php echo PREPEND_PATH; ?>index.php"><i class="glyphicon glyphicon-home"></i> MHUAT</a>
			</div>
			<div class="collapse navbar-collapse">
				<ul class="nav navbar-nav">
					<?php if(!$home_page){ ?>
						<?php echo NavMenus(); ?>
					<?php } ?>
				</ul>

				<?php if(getLoggedAdmin()){ ?>
					<ul class="nav navbar-nav">
						<a href="<?php echo PREPEND_PATH; ?>admin/pageHome.php" class="btn btn-danger navbar-btn hidden-xs" title="<?php echo html_attr($Translation['admin area']); ?>"><i class="glyphicon glyphicon-cog"></i> <?php echo $Translation['admin area']; ?></a>
						<a href="<?php echo PREPEND_PATH; ?>admin/pageHome.php" class="btn btn-danger navbar-btn visible-xs btn-lg" title="<?php echo html_attr($Translation['admin area']); ?>"><i class="glyphicon glyphicon-cog"></i> <?php echo $Translation['admin area']; ?></a>
					</ul>
				<?php } ?>

				<?php if(!$_GET['signIn'] && !$_GET['loginFailed']){ ?>
					<?php if(getLoggedMemberID() == $adminConfig['anonymousMember']){ ?>
						<p class="navbar-text navbar-right">&nbsp;</p>
						<a href="<?php echo PREPEND_PATH; ?>index.php?signIn=1" class="btn btn-success navbar-btn navbar-right"><?php echo $Translation['sign in']; ?></a>
						<p class="navbar-text navbar-right">
							<?php echo $Translation['not signed in']; ?>
						</p>
					<?php }else{ ?>
						<ul class="nav navbar-nav navbar-right hidden-xs" style="min-width: 330px;">
							<a class="btn navbar-btn btn-default" href="<?php echo PREPEND_PATH; ?>index.php?signOut=1"><i class="glyphicon glyphicon-log-out"></i> <?php echo $Translation['sign out']; ?></a>
							<p class="navbar-text">
								<?php echo $Translation['signed as']; ?> <strong><a href="<?php echo PREPEND_PATH; ?>membership_profile.php" class="navbar-link"><?php echo getLoggedMemberID(); ?></a></strong>
							</p>
						</ul>
						<ul class="nav navbar-nav visible-xs">
							<a class="btn navbar-btn btn-default btn-lg visible-xs" href="<?php echo PREPEND_PATH; ?>index.php?signOut=1"><i class="glyphicon glyphicon-log-out"></i> <?php echo $Translation['sign out']; ?></a>
							<p class="navbar-text text-center">
								<?php echo $Translation['signed as']; ?> <strong><a href="<?php echo PREPEND_PATH; ?>membership_profile.php" class="navbar-link"><?php echo getLoggedMemberID(); ?></a></strong>
							</p>
						</ul>
						<script>
							/* periodically check if user is still signed in */
							setInterval(function(){
								$j.ajax({
									url: '<?php echo PREPEND_PATH; ?>ajax_check_login.php',
									success: function(username){
										if(!username.length) window.location = '<?php echo PREPEND_PATH; ?>index.php?signIn=1';
									}
								});
							}, 60000);
						</script>
					<?php } ?>
				<?php } ?>
			</div>
		</nav>
		<?php

		$html = ob_get_contents();
		ob_end_clean();

		return $html;
	}

	#########################################################

	function showNotifications($msg = '', $class = '', $fadeout = true){
		global $Translation;

		$notify_template_no_fadeout = '<div id="%%ID%%" class="alert alert-dismissable %%CLASS%%" style="display: none; padding-top: 6px; padding-bottom: 6px;">' .
					'<button type="button" class="close" data-dismiss="alert" aria-hidden="true">&times;</button>' .
					'%%MSG%%</div>' .
					'<script> jQuery(function(){ /* */ jQuery("#%%ID%%").show("slow"); }); </script>'."\n";
		$notify_template = '<div id="%%ID%%" class="alert %%CLASS%%" style="display: none; padding-top: 6px; padding-bottom: 6px;">%%MSG%%</div>' .
					'<script>' .
						'jQuery(function(){' .
							'jQuery("#%%ID%%").show("slow", function(){' .
								'setTimeout(function(){ /* */ jQuery("#%%ID%%").hide("slow"); }, 4000);' .
							'});' .
						'});' .
					'</script>'."\n";

		if(!$msg){ // if no msg, use url to detect message to display
			if($_REQUEST['record-added-ok'] != ''){
				$msg = $Translation['new record saved'];
				$class = 'alert-success';
			}elseif($_REQUEST['record-added-error'] != ''){
				$msg = $Translation['Couldn\'t save the new record'];
				$class = 'alert-danger';
				$fadeout = false;
			}elseif($_REQUEST['record-updated-ok'] != ''){
				$msg = $Translation['record updated'];
				$class = 'alert-success';
			}elseif($_REQUEST['record-updated-error'] != ''){
				$msg = $Translation['Couldn\'t save changes to the record'];
				$class = 'alert-danger';
				$fadeout = false;
			}elseif($_REQUEST['record-deleted-ok'] != ''){
				$msg = $Translation['The record has been deleted successfully'];
				$class = 'alert-success';
				$fadeout = false;
			}elseif($_REQUEST['record-deleted-error'] != ''){
				$msg = $Translation['Couldn\'t delete this record'];
				$class = 'alert-danger';
				$fadeout = false;
			}else{
				return '';
			}
		}
		$id = 'notification-' . rand();

		$out = ($fadeout ? $notify_template : $notify_template_no_fadeout);
		$out = str_replace('%%ID%%', $id, $out);
		$out = str_replace('%%MSG%%', $msg, $out);
		$out = str_replace('%%CLASS%%', $class, $out);

		return $out;
	}

	#########################################################

	function parseMySQLDate($date, $altDate){
		// is $date valid?
		if(preg_match("/^\d{4}-\d{1,2}-\d{1,2}$/", trim($date))){
			return trim($date);
		}

		if($date != '--' && preg_match("/^\d{4}-\d{1,2}-\d{1,2}$/", trim($altDate))){
			return trim($altDate);
		}

		if($date != '--' && $altDate && intval($altDate)==$altDate){
			return @date('Y-m-d', @time() + ($altDate >= 1 ? $altDate - 1 : $altDate) * 86400);
		}

		return '';
	}

	#########################################################

	function parseCode($code, $isInsert=true, $rawData=false){
		if($isInsert){
			$arrCodes=array(
				'<%%creatorusername%%>' => $_SESSION['memberID'],
				'<%%creatorgroupid%%>' => $_SESSION['memberGroupID'],
				'<%%creatorip%%>' => $_SERVER['REMOTE_ADDR'],
				'<%%creatorgroup%%>' => sqlValue("select name from membership_groups where groupID='{$_SESSION['memberGroupID']}'"),

				'<%%creationdate%%>' => ($rawData ? @date('Y-m-d') : @date('j/n/Y')),
				'<%%creationtime%%>' => ($rawData ? @date('H:i:s') : @date('h:i:s a')),
				'<%%creationdatetime%%>' => ($rawData ? @date('Y-m-d H:i:s') : @date('j/n/Y h:i:s a')),
				'<%%creationtimestamp%%>' => ($rawData ? @date('Y-m-d H:i:s') : @time())
			);
		}else{
			$arrCodes=array(
				'<%%editorusername%%>' => $_SESSION['memberID'],
				'<%%editorgroupid%%>' => $_SESSION['memberGroupID'],
				'<%%editorip%%>' => $_SERVER['REMOTE_ADDR'],
				'<%%editorgroup%%>' => sqlValue("select name from membership_groups where groupID='{$_SESSION['memberGroupID']}'"),

				'<%%editingdate%%>' => ($rawData ? @date('Y-m-d') : @date('j/n/Y')),
				'<%%editingtime%%>' => ($rawData ? @date('H:i:s') : @date('h:i:s a')),
				'<%%editingdatetime%%>' => ($rawData ? @date('Y-m-d H:i:s') : @date('j/n/Y h:i:s a')),
				'<%%editingtimestamp%%>' => ($rawData ? @date('Y-m-d H:i:s') : @time())
			);
		}

		$pc=str_ireplace(array_keys($arrCodes), array_values($arrCodes), $code);

		return $pc;
	}

	#########################################################

	function addFilter($index, $filterAnd, $filterField, $filterOperator, $filterValue){
		// validate input
		if($index < 1 || $index > 80 || !is_int($index)) return false;
		if($filterAnd != 'or')   $filterAnd = 'and';
		$filterField = intval($filterField);

		/* backward compatibility */
		if(in_array($filterOperator, $GLOBALS['filter_operators'])){
			$filterOperator = array_search($filterOperator, $GLOBALS['filter_operators']);
		}

		if(!in_array($filterOperator, array_keys($GLOBALS['filter_operators']))){
			$filterOperator = 'like';
		}

		if(!$filterField){
			$filterOperator = '';
			$filterValue = '';
		}

		$_REQUEST['FilterAnd'][$index] = $filterAnd;
		$_REQUEST['FilterField'][$index] = $filterField;
		$_REQUEST['FilterOperator'][$index] = $filterOperator;
		$_REQUEST['FilterValue'][$index] = $filterValue;

		return true;
	}

	#########################################################

	function clearFilters(){
		for($i=1; $i<=80; $i++){
			addFilter($i, '', 0, '', '');
		}
	}

	#########################################################

	if(!function_exists('str_ireplace')){
		function str_ireplace($search, $replace, $subject){
			$ret=$subject;
			if(is_array($search)){
				for($i=0; $i<count($search); $i++){
					$ret=str_ireplace($search[$i], $replace[$i], $ret);
				}
			}else{
				$ret=preg_replace('/'.preg_quote($search, '/').'/i', $replace, $ret);
			}

			return $ret;
		} 
	} 

	#########################################################

	/**
	* Loads a given view from the templates folder, passing the given data to it
	* @param $view the name of a php file (without extension) to be loaded from the 'templates' folder
	* @param $the_data_to_pass_to_the_view (optional) associative array containing the data to pass to the view
	* @return the output of the parsed view as a string
	*/
	function loadView($view, $the_data_to_pass_to_the_view=false){
		global $Translation;

		$view = dirname(__FILE__)."/templates/$view.php";
		if(!is_file($view)) return false;

		if(is_array($the_data_to_pass_to_the_view)){
			foreach($the_data_to_pass_to_the_view as $k => $v)
				$$k = $v;
		}
		unset($the_data_to_pass_to_the_view, $k, $v);

		ob_start();
		@include($view);
		$out=ob_get_contents();
		ob_end_clean();

		return $out;
	}

	#########################################################

	/**
	* Loads a table template from the templates folder, passing the given data to it
	* @param $table_name the name of the table whose template is to be loaded from the 'templates' folder
	* @param $the_data_to_pass_to_the_table associative array containing the data to pass to the table template
	* @return the output of the parsed table template as a string
	*/
	function loadTable($table_name, $the_data_to_pass_to_the_table = array()){
		$dont_load_header = $the_data_to_pass_to_the_table['dont_load_header'];
		$dont_load_footer = $the_data_to_pass_to_the_table['dont_load_footer'];

		$header = $table = $footer = '';

		if(!$dont_load_header){
			// try to load tablename-header
			if(!($header = loadView("{$table_name}-header", $the_data_to_pass_to_the_table))){
				$header = loadView('table-common-header', $the_data_to_pass_to_the_table);
			}
		}

		$table = loadView($table_name, $the_data_to_pass_to_the_table);

		if(!$dont_load_footer){
			// try to load tablename-footer
			if(!($footer = loadView("{$table_name}-footer", $the_data_to_pass_to_the_table))){
				$footer = loadView('table-common-footer', $the_data_to_pass_to_the_table);
			}
		}

		return "{$header}{$table}{$footer}";
	}

	#########################################################

	function filterDropdownBy($filterable, $filterers, $parentFilterers, $parentPKField, $parentCaption, $parentTable, &$filterableCombo){
		$filterersArray = explode(',', $filterers);
		$parentFilterersArray = explode(',', $parentFilterers);
		$parentFiltererList = '`' . implode('`, `', $parentFilterersArray) . '`';
		$res=sql("SELECT `$parentPKField`, $parentCaption, $parentFiltererList FROM `$parentTable` ORDER BY 2", $eo);
		$filterableData = array();
		while($row=db_fetch_row($res)){
			$filterableData[$row[0]] = $row[1];
			$filtererIndex = 0;
			foreach($filterersArray as $filterer){
				$filterableDataByFilterer[$filterer][$row[$filtererIndex + 2]][$row[0]] = $row[1];
				$filtererIndex++;
			}
			$row[0] = addslashes($row[0]);
			$row[1] = addslashes($row[1]);
			$jsonFilterableData .= "\"{$row[0]}\":\"{$row[1]}\",";
		}
		$jsonFilterableData .= '}';
		$jsonFilterableData = '{'.str_replace(',}', '}', $jsonFilterableData);     
		$filterJS = "\nvar {$filterable}_data = $jsonFilterableData;";

		foreach($filterersArray as $filterer){
			if(is_array($filterableDataByFilterer[$filterer])) foreach($filterableDataByFilterer[$filterer] as $filtererItem => $filterableItem){
				$jsonFilterableDataByFilterer[$filterer] .= '"'.addslashes($filtererItem).'":{';
				foreach($filterableItem as $filterableItemID => $filterableItemData){
					$jsonFilterableDataByFilterer[$filterer] .= '"'.addslashes($filterableItemID).'":"'.addslashes($filterableItemData).'",';
				}
				$jsonFilterableDataByFilterer[$filterer] .= '},';
			}
			$jsonFilterableDataByFilterer[$filterer] .= '}';
			$jsonFilterableDataByFilterer[$filterer] = '{'.str_replace(',}', '}', $jsonFilterableDataByFilterer[$filterer]);

			$filterJS.="\n\n// code for filtering {$filterable} by {$filterer}\n";
			$filterJS.="\nvar {$filterable}_data_by_{$filterer} = {$jsonFilterableDataByFilterer[$filterer]}; ";
			$filterJS.="\nvar selected_{$filterable} = \$j('#{$filterable}').val();";
			$filterJS.="\nvar {$filterable}_change_by_{$filterer} = function(){";
			$filterJS.="\n\t$('{$filterable}').options.length=0;";
			$filterJS.="\n\t$('{$filterable}').options[0] = new Option();";
			$filterJS.="\n\tif(\$j('#{$filterer}').val()){";
			$filterJS.="\n\t\tfor({$filterable}_item in {$filterable}_data_by_{$filterer}[\$j('#{$filterer}').val()]){";
			$filterJS.="\n\t\t\t$('{$filterable}').options[$('{$filterable}').options.length] = new Option(";
			$filterJS.="\n\t\t\t\t{$filterable}_data_by_{$filterer}[\$j('#{$filterer}').val()][{$filterable}_item],";
			$filterJS.="\n\t\t\t\t{$filterable}_item,";
			$filterJS.="\n\t\t\t\t({$filterable}_item == selected_{$filterable} ? true : false),";
			$filterJS.="\n\t\t\t\t({$filterable}_item == selected_{$filterable} ? true : false)";
			$filterJS.="\n\t\t\t);";
			$filterJS.="\n\t\t}";
			$filterJS.="\n\t}else{";
			$filterJS.="\n\t\tfor({$filterable}_item in {$filterable}_data){";
			$filterJS.="\n\t\t\t$('{$filterable}').options[$('{$filterable}').options.length] = new Option(";
			$filterJS.="\n\t\t\t\t{$filterable}_data[{$filterable}_item],";
			$filterJS.="\n\t\t\t\t{$filterable}_item,";
			$filterJS.="\n\t\t\t\t({$filterable}_item == selected_{$filterable} ? true : false),";
			$filterJS.="\n\t\t\t\t({$filterable}_item == selected_{$filterable} ? true : false)";
			$filterJS.="\n\t\t\t);";
			$filterJS.="\n\t\t}";
			$filterJS.="\n\t\tif(selected_{$filterable} && selected_{$filterable} == \$j('#{$filterable}').val()){";
			$filterJS.="\n\t\t\tfor({$filterer}_item in {$filterable}_data_by_{$filterer}){";
			$filterJS.="\n\t\t\t\tfor({$filterable}_item in {$filterable}_data_by_{$filterer}[{$filterer}_item]){";
			$filterJS.="\n\t\t\t\t\tif({$filterable}_item == selected_{$filterable}){";
			$filterJS.="\n\t\t\t\t\t\t$('{$filterer}').value = {$filterer}_item;";
			$filterJS.="\n\t\t\t\t\t\tbreak;";
			$filterJS.="\n\t\t\t\t\t}";
			$filterJS.="\n\t\t\t\t}";
			$filterJS.="\n\t\t\t\tif({$filterable}_item == selected_{$filterable}) break;";
			$filterJS.="\n\t\t\t}";
			$filterJS.="\n\t\t}";
			$filterJS.="\n\t}";
			$filterJS.="\n\t$('{$filterable}').highlight();";
			$filterJS.="\n};";
			$filterJS.="\n$('{$filterer}').observe('change', function(){ /* */ window.setTimeout({$filterable}_change_by_{$filterer}, 25); });";
			$filterJS.="\n";
		}

		$filterableCombo = new Combo;
		$filterableCombo->ListType = 0;
		$filterableCombo->ListItem = array_slice(array_values($filterableData), 0, 10);
		$filterableCombo->ListData = array_slice(array_keys($filterableData), 0, 10);
		$filterableCombo->SelectName = $filterable;
		$filterableCombo->AllowNull = true;

		return $filterJS;
	}

	#########################################################
	function br2nl($text){
		return  preg_replace('/\<br(\s*)?\/?\>/i', "\n", $text);
	}

	#########################################################

	if(!function_exists('htmlspecialchars_decode')){
		function htmlspecialchars_decode($string, $quote_style = ENT_COMPAT){
			return strtr($string, array_flip(get_html_translation_table(HTML_SPECIALCHARS, $quote_style)));
		}
	}

	#########################################################

	function entitiesToUTF8($input){
		return preg_replace_callback('/(&#[0-9]+;)/', '_toUTF8', $input);
	}

	function _toUTF8($m){
		if(function_exists('mb_convert_encoding')){
			return mb_convert_encoding($m[1], "UTF-8", "HTML-ENTITIES");
		}else{
			return $m[1];
		}
	}

	#########################################################

	function func_get_args_byref() {
		if(!function_exists('debug_backtrace')) return false;

		$trace = debug_backtrace();
		return $trace[1]['args'];
	}

	#########################################################

	function permissions_sql($table, $level = 'all'){
		if(!in_array($level, array('user', 'group'))){ $level = 'all'; }
		$perm = getTablePermissions($table);
		$from = '';
		$where = '';
		$pk = getPKFieldName($table);

		if($perm[2] == 1 || ($perm[2] > 1 && $level == 'user')){ // view owner only
			$from = 'membership_userrecords';
			$where = "(`$table`.`$pk`=membership_userrecords.pkValue and membership_userrecords.tableName='$table' and lcase(membership_userrecords.memberID)='".getLoggedMemberID()."')";
		}elseif($perm[2] == 2 || ($perm[2] > 2 && $level == 'group')){ // view group only
			$from = 'membership_userrecords';
			$where = "(`$table`.`$pk`=membership_userrecords.pkValue and membership_userrecords.tableName='$table' and membership_userrecords.groupID='".getLoggedGroupID()."')";
		}elseif($perm[2] == 3){ // view all
			// no further action
		}elseif($perm[2] == 0){ // view none
			return false;
		}

		return array('where' => $where, 'from' => $from, 0 => $where, 1 => $from);
	}

	#########################################################

	function error_message($msg, $back_url = '', $full_page = true){
		$curr_dir = dirname(__FILE__);
		global $Translation;

		ob_start();

		if($full_page) include_once($curr_dir . '/header.php');

		echo '<div class="panel panel-danger">';
			echo '<div class="panel-heading"><h3 class="panel-title">' . $Translation['error:'] . '</h3></div>';
			echo '<div class="panel-body"><p class="text-danger">' . $msg . '</p>';
			if($back_url !== false){ // explicitly passing false suppresses the back link completely
				echo '<div class="text-center">';
				if($back_url){
					echo '<a href="' . $back_url . '" class="btn btn-danger btn-lg vspacer-lg"><i class="glyphicon glyphicon-chevron-left"></i> ' . $Translation['< back'] . '</a>';
				}else{
					echo '<a href="#" class="btn btn-danger btn-lg vspacer-lg" onclick="history.go(-1); return false;"><i class="glyphicon glyphicon-chevron-left"></i> ' . $Translation['< back'] . '</a>';
				}
				echo '</div>';
			}
			echo '</div>';
		echo '</div>';

		if($full_page) include_once($curr_dir . '/footer.php');

		$out = ob_get_contents();
		ob_end_clean();

		return $out;
	}

	#########################################################

	function toMySQLDate($formattedDate, $sep = datalist_date_separator, $ord = datalist_date_format){
		// extract date elements
		$de=explode($sep, $formattedDate);
		$mySQLDate=intval($de[strpos($ord, 'Y')]).'-'.intval($de[strpos($ord, 'm')]).'-'.intval($de[strpos($ord, 'd')]);
		return $mySQLDate;
	}

	#########################################################

	function reIndex(&$arr){
		$i=1;
		foreach($arr as $n=>$v){
			$arr2[$i]=$n;
			$i++;
		}
		return $arr2;
	}

	#########################################################

	function get_embed($provider, $url, $max_width = '', $max_height = '', $retrieve = 'html'){
		global $Translation;
		if(!$url) return '';

		$providers = array(
			'youtube' => array('oembed' => 'http://www.youtube.com/oembed?'),
			'googlemap' => array('oembed' => '', 'regex' => '/^http.*\.google\..*maps/i')
		);

		if(!isset($providers[$provider])){
			return '<div class="text-danger">' . $Translation['invalid provider'] . '</div>';
		}

		if(isset($providers[$provider]['regex']) && !preg_match($providers[$provider]['regex'], $url)){
			return '<div class="text-danger">' . $Translation['invalid url'] . '</div>';
		}

		if($providers[$provider]['oembed']){
			$oembed = $providers[$provider]['oembed'] . 'url=' . urlencode($url) . "&maxwidth={$max_width}&maxheight={$max_height}&format=json";
			$data_json = request_cache($oembed);

			$data = json_decode($data_json, true);
			if($data === null){
				/* an error was returned rather than a json string */
				if($retrieve == 'html') return "<div class=\"text-danger\">{$data_json}\n<!-- {$oembed} --></div>";
				return '';
			}

			return (isset($data[$retrieve]) ? $data[$retrieve] : $data['html']);
		}

		/* special cases (where there is no oEmbed provider) */
		if($provider == 'googlemap') return get_embed_googlemap($url, $max_width, $max_height, $retrieve);

		return '<div class="text-danger">Invalid provider!</div>';
	}

	#########################################################

	function get_embed_googlemap($url, $max_width = '', $max_height = '', $retrieve = 'html'){
		global $Translation;
		$url_parts = parse_url($url);
		$coords_regex = '/-?\d+(\.\d+)?[,+]-?\d+(\.\d+)?(,\d{1,2}z)?/'; /* https://stackoverflow.com/questions/2660201 */

		if(preg_match($coords_regex, $url_parts['path'] . '?' . $url_parts['query'], $m)){
			list($lat, $long, $zoom) = explode(',', $m[0]);
			$zoom = intval($zoom);
			if(!$zoom) $zoom = 10; /* default zoom */
			if(!$max_height) $max_height = 360;
			if(!$max_width) $max_width = 480;

			$api_key = '';
			$embed_url = "https://www.google.com/maps/embed/v1/view?key={$api_key}&center={$lat},{$long}&zoom={$zoom}&maptype=roadmap";
			$thumbnail_url = "https://maps.googleapis.com/maps/api/staticmap?key={$api_key}&center={$lat},{$long}&zoom={$zoom}&maptype=roadmap&size={$max_width}x{$max_height}";

			if($retrieve == 'html'){
				return "<iframe width=\"{$max_width}\" height=\"{$max_height}\" frameborder=\"0\" style=\"border:0\" src=\"{$embed_url}\"></iframe>";
			}else{
				return $thumbnail_url;
			}
		}else{
			return '<div class="text-danger">' . $Translation['cant retrieve coordinates from url'] . '</div>';
		}
	}

	#########################################################

	function request_cache($request, $force_fetch = false){
		$max_cache_lifetime = 7 * 86400; /* max cache lifetime in seconds before refreshing from source */

		/* membership_cache table exists? if not, create it */
		static $cache_table_exists = false;
		if(!$cache_table_exists && !$force_fetch){
			$te = sqlValue("show tables like 'membership_cache'");
			if(!$te){
				if(!sql("CREATE TABLE `membership_cache` (`request` VARCHAR(100) NOT NULL, `request_ts` INT, `response` TEXT NOT NULL, PRIMARY KEY (`request`))", $eo)){
					/* table can't be created, so force fetching request */
					return request_cache($request, true);
				}
			}
			$cache_table_exists = true;
		}

		/* retrieve response from cache if exists */
		if(!$force_fetch){
			$res = sql("select response, request_ts from membership_cache where request='" . md5($request) . "'", $eo);
			if(!$row = db_fetch_array($res)) return request_cache($request, true);

			$response = $row[0];
			$response_ts = $row[1];
			if($response_ts < time() - $max_cache_lifetime) return request_cache($request, true);
		}

		/* if no response in cache, issue a request */
		if(!$response || $force_fetch){
			$response = @file_get_contents($request);
			if($response === false){
				$error = error_get_last();
				$error_message = preg_replace('/.*: (.*)/', '$1', $error['message']);
				return $error_message;
			}elseif($cache_table_exists){
				/* store response in cache */
				$ts = time();
				sql("replace into membership_cache set request='" . md5($request) . "', request_ts='{$ts}', response='" . makeSafe($response, false) . "'", $eo);
			}
		}

		return $response;
	}

	#########################################################

	function check_record_permission($table, $id, $perm = 'view'){
		if($perm != 'edit' && $perm != 'delete') $perm = 'view';

		$perms = getTablePermissions($table);
		if(!$perms[$perm]) return false;

		$safe_id = makeSafe($id);
		$safe_table = makeSafe($table);

		if($perms[$perm] == 1){ // own records only
			$username = getLoggedMemberID();
			$owner = sqlValue("select memberID from membership_userrecords where tableName='{$safe_table}' and pkValue='{$safe_id}'");
			if($owner == $username) return true;
		}elseif($perms[$perm] == 2){ // group records
			$group_id = getLoggedGroupID();
			$owner_group_id = sqlValue("select groupID from membership_userrecords where tableName='{$safe_table}' and pkValue='{$safe_id}'");
			if($owner_group_id == $group_id) return true;
		}elseif($perms[$perm] == 3){ // all records
			return true;
		}

		return false;
	}

	#########################################################

	function NavMenus($options = array()){
		if(!defined('PREPEND_PATH')) define('PREPEND_PATH', '');
		global $Translation;
		$prepend_path = PREPEND_PATH;

		/* default options */
		if(empty($options)){
			$options = array(
				'tabs' => 7
			);
		}

		$table_group_name = array_keys(get_table_groups()); /* 0 => group1, 1 => group2 .. */
		/* if only one group named 'None', set to translation of 'select a table' */
		if((count($table_group_name) == 1 && $table_group_name[0] == 'None') || count($table_group_name) < 1) $table_group_name[0] = $Translation['select a table'];
		$table_group_index = array_flip($table_group_name); /* group1 => 0, group2 => 1 .. */
		$menu = array_fill(0, count($table_group_name), '');

		$t = time();
		$arrTables = getTableList();
		if(is_array($arrTables)){
			foreach($arrTables as $tn => $tc){
				/* ---- list of tables where hide link in nav menu is set ---- */
				$tChkHL = array_search($tn, array('main','gestion_salaire','gestion_prime','demandes_permission','demandes_conge_m','demandes_permissions','demandes_conge_sg','conges_permissions','demandes_conge','demandes'));

				/* ---- list of tables where filter first is set ---- */
				$tChkFF = array_search($tn, array());
				if($tChkFF !== false && $tChkFF !== null){
					$searchFirst = '&Filter_x=1';
				}else{
					$searchFirst = '';
				}

				/* when no groups defined, $table_group_index['None'] is NULL, so $menu_index is still set to 0 */
				$menu_index = intval($table_group_index[$tc[3]]);
				if(!$tChkHL && $tChkHL !== 0) $menu[$menu_index] .= "<li><a href=\"{$prepend_path}{$tn}_view.php?t={$t}{$searchFirst}\"><img src=\"{$prepend_path}" . ($tc[2] ? $tc[2] : 'blank.gif') . "\" height=\"32\"> {$tc[0]}</a></li>";
			}
		}

		// custom nav links, as defined in "hooks/links-navmenu.php" 
		global $navLinks;
		if(is_array($navLinks)){
			$memberInfo = getMemberInfo();
			$links_added = array();
			foreach($navLinks as $link){
				if(!isset($link['url']) || !isset($link['title'])) continue;
				if($memberInfo['admin'] || @in_array($memberInfo['group'], $link['groups']) || @in_array('*', $link['groups'])){
					$menu_index = intval($link['table_group']);
					if(!$links_added[$menu_index]) $menu[$menu_index] .= '<li class="divider"></li>';

					/* add prepend_path to custom links if they aren't absolute links */
					if(!preg_match('/^(http|\/\/)/i', $link['url'])) $link['url'] = $prepend_path . $link['url'];
					if(!preg_match('/^(http|\/\/)/i', $link['icon']) && $link['icon']) $link['icon'] = $prepend_path . $link['icon'];

					$menu[$menu_index] .= "<li><a href=\"{$link['url']}\"><img src=\"" . ($link['icon'] ? $link['icon'] : "{$prepend_path}blank.gif") . "\" height=\"32\"> {$link['title']}</a></li>";
					$links_added[$menu_index]++;
				}
			}
		}

		$menu_wrapper = '';
		for($i = 0; $i < count($menu); $i++){
			$menu_wrapper .= <<<EOT
				<li class="dropdown">
					<a href="#" class="dropdown-toggle" data-toggle="dropdown">{$table_group_name[$i]} <b class="caret"></b></a>
					<ul class="dropdown-menu" role="menu">{$menu[$i]}</ul>
				</li>
EOT;
		}

		return $menu_wrapper;
	}

	#########################################################

	function StyleSheet(){
		if(!defined('PREPEND_PATH')) define('PREPEND_PATH', '');
		$prepend_path = PREPEND_PATH;

		$css_links = <<<EOT

			<link rel="stylesheet" href="{$prepend_path}resources/initializr/css/cerulean.css">
			<link rel="stylesheet" href="{$prepend_path}resources/lightbox/css/lightbox.css" media="screen">
			<link rel="stylesheet" href="{$prepend_path}resources/select2/select2.css" media="screen">
			<link rel="stylesheet" href="{$prepend_path}resources/timepicker/bootstrap-timepicker.min.css" media="screen">
			<link rel="stylesheet" href="{$prepend_path}dynamic.css.php">
EOT;

		return $css_links;
	}

	#########################################################

	function getUploadDir($dir){
		global $Translation;

		if($dir==""){
			$dir=$Translation['ImageFolder'];
		}

		if(substr($dir, -1)!="/"){
			$dir.="/";
		}

		return $dir;
	}

	#########################################################

	function PrepareUploadedFile($FieldName, $MaxSize, $FileTypes = 'jpg|jpeg|gif|png', $NoRename = false, $dir = ''){
		global $Translation;
		$f = $_FILES[$FieldName];
		if($f['error'] == 4 || !$f['name']) return '';

		$dir = getUploadDir($dir);

		/* get php.ini upload_max_filesize in bytes */
		$php_upload_size_limit = trim(ini_get('upload_max_filesize'));
		$last = strtolower($php_upload_size_limit[strlen($php_upload_size_limit) - 1]);
		switch($last){
			case 'g':
				$php_upload_size_limit *= 1024;
			case 'm':
				$php_upload_size_limit *= 1024;
			case 'k':
				$php_upload_size_limit *= 1024;
		}

		$MaxSize = min($MaxSize, $php_upload_size_limit);

		if($f['size'] > $MaxSize || $f['error']){
			echo error_message(str_replace('<MaxSize>', intval($MaxSize / 1024), $Translation['file too large']));
			exit;
		}
		if(!preg_match('/\.(' . $FileTypes . ')$/i', $f['name'], $ft)){
			echo error_message(str_replace('<FileTypes>', str_replace('|', ', ', $FileTypes), $Translation['invalid file type']));
			exit;
		}

		$name = str_replace(' ', '_', $f['name']);
		if(!$NoRename) $name = substr(md5(microtime() . rand(0, 100000)), -17) . $ft[0];

		if(!file_exists($dir)) @mkdir($dir, 0777);

		if(!@move_uploaded_file($f['tmp_name'], $dir . $name)){
			echo error_message("Couldn't save the uploaded file. Try chmoding the upload folder '{$dir}' to 777.");
			exit;
		}

		@chmod($dir . $name, 0666);
		return $name;
	}

	#########################################################

	function get_home_links($homeLinks, $default_classes, $tgroup = ''){
		if(!is_array($homeLinks) || !count($homeLinks)) return '';

		$memberInfo = getMemberInfo();

		ob_start();
		foreach($homeLinks as $link){
			if(!isset($link['url']) || !isset($link['title'])) continue;
			if($tgroup != $link['table_group'] && $tgroup != '*') continue;

			/* fall-back classes if none defined */
			if(!$link['grid_column_classes']) $link['grid_column_classes'] = $default_classes['grid_column'];
			if(!$link['panel_classes']) $link['panel_classes'] = $default_classes['panel'];
			if(!$link['link_classes']) $link['link_classes'] = $default_classes['link'];

			if($memberInfo['admin'] || @in_array($memberInfo['group'], $link['groups']) || @in_array('*', $link['groups'])){
				?>
				<div class="col-xs-12 <?php echo $link['grid_column_classes']; ?>">
					<div class="panel <?php echo $link['panel_classes']; ?>">
						<div class="panel-body">
							<a class="btn btn-block btn-lg <?php echo $link['link_classes']; ?>" title="<?php echo preg_replace("/&amp;(#[0-9]+|[a-z]+);/i", "&$1;", html_attr(strip_tags($link['description']))); ?>" href="<?php echo $link['url']; ?>"><?php echo ($link['icon'] ? '<img src="' . $link['icon'] . '">' : ''); ?><strong><?php echo $link['title']; ?></strong></a>
							<div class="panel-body-description"><?php echo $link['description']; ?></div>
						</div>
					</div>
				</div>
				<?php
			}
		}

		$html = ob_get_contents();
		ob_end_clean();

		return $html;
	}

	#########################################################

	function quick_search_html($search_term, $label, $separate_dv = true){
		global $Translation;

		$safe_search = html_attr($search_term);
		$safe_label = html_attr($label);
		$safe_clear_label = html_attr($Translation['Reset Filters']);

		if($separate_dv){
			$reset_selection = "document.myform.SelectedID.value = '';";
		}else{
			$reset_selection = "document.myform.writeAttribute('novalidate', 'novalidate');";
		}
		$reset_selection .= ' document.myform.NoDV.value=1; return true;';

		$html = <<<EOT
		<div class="input-group" id="quick-search">
			<input type="text" id="SearchString" name="SearchString" value="{$safe_search}" class="form-control" placeholder="{$safe_label}">
			<span class="input-group-btn">
				<button name="Search_x" value="1" id="Search" type="submit" onClick="{$reset_selection}" class="btn btn-default" title="{$safe_label}"><i class="glyphicon glyphicon-search"></i></button>
				<button name="ClearQuickSearch" value="1" id="ClearQuickSearch" type="submit" onClick="\$j('#SearchString').val(''); {$reset_selection}" class="btn btn-default" title="{$safe_clear_label}"><i class="glyphicon glyphicon-remove-circle"></i></button>
			</span>
		</div>
EOT;
		return $html;
	}

	#########################################################

