You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1110 lines
44 KiB
1110 lines
44 KiB
<?php
|
|
// IMPORT DES BIBLIOTHEQUES
|
|
require_once("../../base/php/startSession.php");
|
|
require_once("../../base/php/classDatabase.php");
|
|
require_once("../../base/php/Functions.php" );
|
|
require_once("../../base/php/WebAppLog.php");
|
|
|
|
// DECLARATION DES VARIABLES
|
|
$compress64 = false;
|
|
$database = new Database("iCTI");
|
|
$schema = "rh";
|
|
|
|
// RECUPERATION DES PARAMETRES HTTP
|
|
$softCode = getPOST("softCode");
|
|
$tableName = getPOST("tableName");
|
|
|
|
// ECRITURE DE LA REPONSE HTTP
|
|
$httpString = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>";
|
|
$httpString .= "\n<HTTP_SERVICE>";
|
|
if ($tableName == "t_expert_controle") {
|
|
if ($getOption != "getRefSettings") {
|
|
$httpString .= getRecords_expert_controle();
|
|
}
|
|
else {
|
|
$httpString .= getRecords_expert_controle_refsettings($refPrefix);
|
|
}
|
|
}
|
|
else {
|
|
if ($schema != "" && $tableName != "") {
|
|
$httpString .= getRecords($database, $schema, $tableName);
|
|
}
|
|
}
|
|
$httpString .= "\n</HTTP_SERVICE>";
|
|
if ($compress64) {
|
|
$httpString = compress64($httpString);
|
|
}
|
|
echo "<CTICONTENT>$httpString</CTICONTENT>";
|
|
|
|
// DEFINITIONS DE FONCTION
|
|
|
|
/**
|
|
*
|
|
* @param Database $db
|
|
* @param string $schema
|
|
* @param string $tableName
|
|
* @return string
|
|
*/
|
|
function getRecords($db, $schema, $tableName) {
|
|
$httpString = '';
|
|
$sqlColumnAdd = '';
|
|
$sqlTableJoin = '';
|
|
|
|
|
|
|
|
// Gestion des colonnes supplémentaires.
|
|
switch ($tableName) {
|
|
case 't_rubriques' :
|
|
$tmpRubArray = array(
|
|
"nombre",
|
|
"base",
|
|
"heures_contrat",
|
|
"heures_payees",
|
|
"heures_travaillees",
|
|
"masse_salariale",
|
|
"brut",
|
|
"avantage_nature",
|
|
"frais_imposables",
|
|
"cotisation_salarie",
|
|
"cotisation_patronale",
|
|
"od_net_salarie",
|
|
"od_net_patronale",
|
|
"net_imposable",
|
|
"net_a_payer",
|
|
"nombre_provisions",
|
|
"montant_provisions",
|
|
"masse_salariale_provisionnee"
|
|
);
|
|
|
|
foreach ($tmpRubArray as $ind) {
|
|
$sqlColumnAdd .= '
|
|
,CASE WHEN (p_cumul OR p_detail) AND p_'.$ind.' THEN \'X\' ELSE \'\' END AS p_'.$ind.'
|
|
,CASE WHEN (p_cumul OR p_detail) AND p_'.$ind.' THEN s_'.$ind.' ELSE null END AS s_'.$ind.'
|
|
,CASE WHEN (p_cumul OR p_detail) AND p_'.$ind.' THEN c_'.$ind.' ELSE null END AS c_'.$ind.'
|
|
';
|
|
}
|
|
|
|
$sqlColumnAdd .= '
|
|
,code_calcul
|
|
,unite
|
|
,coefficient
|
|
,coefficient_base
|
|
,coefficient_nombre
|
|
,coefficient_txs
|
|
,coefficient_txp
|
|
,CASE WHEN user_modified THEN \'M\' ELSE \'\' END AS user_modified
|
|
,CASE WHEN p_cumul THEN \'C\' ELSE \'\' END AS p_cumul
|
|
,CASE WHEN p_detail THEN \'D\' ELSE \'\' END AS p_detail
|
|
,CASE WHEN proratiser_conversion THEN \'X\' ELSE \'\' END AS proratiser_conversion
|
|
';
|
|
break;
|
|
case 't_etablissements' :
|
|
$sqlColumnAdd .= ',base_calcul_etp';
|
|
$sqlColumnAdd .= ',planning_code';
|
|
$sqlColumnAdd .= ',finess_id';
|
|
break;
|
|
case 't_entreprises' :
|
|
$sqlColumnAdd .= ',planning_code';
|
|
break;
|
|
case 't_planning_service' :
|
|
$sqlColumnAdd .= ",t_planning_service.service_id, COALESCE(t_services.code || ' ' || t_services.texte,'') AS service_paie_associe_texte";
|
|
$sqlTableJoin = "LEFT JOIN rh.t_services ON $tableName.service_id = t_services.oid";
|
|
break;
|
|
case 't_planning_qualification' :
|
|
$sqlColumnAdd .= ",t_planning_qualification.qualification_id, COALESCE(t_qualifications.code || ' ' || t_qualifications.texte,'') AS qualification_paie_associe_texte";
|
|
$sqlTableJoin = "LEFT JOIN rh.t_qualifications ON $tableName.qualification_id = t_qualifications.oid";
|
|
break;
|
|
case 't_population_regle' :
|
|
$sqlColumnAdd .= ',t_population_regle.oid';
|
|
$sqlColumnAdd .= ',t_population_regle.code';
|
|
$sqlColumnAdd .= ',t_population_regle.texte';
|
|
$sqlColumnAdd .= ',t_population_regle.priorite';
|
|
$sqlColumnAdd .= ',t_population_regle.population_id as population_id';
|
|
$sqlColumnAdd .= ",t_population.code || ' '::text || t_population.texte as population_texte";
|
|
$sqlColumnAdd .= ", CASE WHEN liste_finess <> '' THEN ' Fin:'::text || liste_finess ELSE ''::text END ||
|
|
CASE WHEN liste_etablissement <> '' THEN ' Ets:'::text || liste_etablissement ELSE ''::text END ||
|
|
CASE WHEN liste_service <> '' THEN ' Ser:'::text || liste_service ELSE ''::text END ||
|
|
CASE WHEN liste_specialite <> '' THEN ' Spe:'::text || liste_specialite ELSE ''::text END ||
|
|
CASE WHEN liste_section_analytique <> '' THEN ' Ana:'::text || liste_section_analytique ELSE ''::text END ||
|
|
CASE WHEN liste_section_analytique_paie <> '' THEN ' AnaP:'::text || liste_section_analytique_paie ELSE ''::text END ||
|
|
CASE WHEN liste_qualification <> '' THEN ' Qua:'::text || liste_qualification ELSE ''::text END ||
|
|
CASE WHEN liste_grille <> '' THEN ' Grille:'::text || liste_grille ELSE ''::text END ||
|
|
CASE WHEN liste_code_emploi <> '' THEN ' Emploi:'::text || liste_code_emploi ELSE ''::text END ||
|
|
CASE WHEN liste_categorie_socio_professionnelle <> '' THEN ' Cat:'::text || liste_categorie_socio_professionnelle ELSE ''::text END ||
|
|
CASE WHEN liste_statut <> '' THEN ' Statut:'::text || liste_statut ELSE ''::text END
|
|
as regle_resume";
|
|
$sqlColumnAdd .= ',liste_finess';
|
|
$sqlColumnAdd .= ',liste_etablissement';
|
|
$sqlColumnAdd .= ',liste_service';
|
|
$sqlColumnAdd .= ',liste_specialite';
|
|
$sqlColumnAdd .= ',liste_section_analytique';
|
|
$sqlColumnAdd .= ',liste_section_analytique_paie';
|
|
$sqlColumnAdd .= ',liste_qualification';
|
|
$sqlColumnAdd .= ',liste_grille';
|
|
$sqlColumnAdd .= ',liste_code_emploi';
|
|
$sqlColumnAdd .= ',liste_categorie_socio_professionnelle';
|
|
$sqlColumnAdd .= ',liste_statut';
|
|
$sqlTableJoin = "LEFT JOIN rh.t_population ON t_population_regle.population_id = t_population.oid";
|
|
break;
|
|
}
|
|
|
|
// Gestion de la requête exécuter.
|
|
switch ($tableName) {
|
|
case 't_divers' :
|
|
$sqlcmd = "
|
|
SELECT
|
|
code,
|
|
texte,
|
|
valeur,
|
|
valeur_date,
|
|
description,
|
|
CASE WHEN show_info_module THEN 1 ELSE 0 END AS show_info_module
|
|
FROM $schema.$tableName
|
|
ORDER BY code
|
|
";
|
|
break;
|
|
case 't_transformation' :
|
|
$sqlcmd = "
|
|
SELECT
|
|
t_transformation.oid,
|
|
t_transformation.texte,
|
|
TRIM(
|
|
CASE WHEN t_transformation.date_debut > '00010101' THEN to_char(t_transformation.date_debut,'MM-YYYY') ELSE '' END ||
|
|
CASE WHEN t_transformation.date_debut > '00010101' AND t_transformation.date_fin < '20990101' THEN ' à ' ELSE '' END ||
|
|
CASE WHEN t_transformation.date_fin < '20990101' THEN to_char(t_transformation.date_fin,'MM-YYYY') ELSE '' END
|
|
) AS texte_date,
|
|
TRIM(
|
|
CASE WHEN salarie_id <> -1 THEN ' Salarié ' || p_salaries.nom || ' ' || p_salaries.prenom || ' (' || matricule || ')' ELSE '' END ||
|
|
CASE WHEN from_service_id <> -1 THEN ' Serv ' || t_services_from.texte || ' (' || t_services_from.code || ')' ELSE '' END ||
|
|
CASE WHEN from_qualification_id <> -1 THEN ' Qualif ' || t_qualifications_from.texte || ' (' || t_qualifications_from.code || ')' ELSE '' END
|
|
) AS texte_from,
|
|
TRIM(
|
|
CASE WHEN to_service_id <> -1 AND to_service_id <> from_service_id THEN ' Serv ' || t_services_to.texte || ' (' || t_services_to.code || ')' ELSE '' END ||
|
|
CASE WHEN to_qualification_id <> -1 AND to_qualification_id <> from_qualification_id THEN ' Qualif ' || t_qualifications_to.texte || ' (' || t_qualifications_to.code || ')' ELSE '' END
|
|
) AS texte_to,
|
|
t_transformation.date_debut,
|
|
t_transformation.date_fin,
|
|
t_transformation.salarie_id,
|
|
t_transformation.from_service_id,
|
|
t_transformation.to_service_id,
|
|
t_transformation.from_qualification_id,
|
|
t_transformation.to_qualification_id
|
|
FROM rh.t_transformation
|
|
LEFT JOIN rh.p_salaries ON salarie_id = p_salaries.oid
|
|
LEFT JOIN rh.t_services t_services_from ON from_service_id = t_services_from.oid
|
|
LEFT JOIN rh.t_services t_services_to ON to_service_id = t_services_to.oid
|
|
LEFT JOIN rh.t_qualifications t_qualifications_from ON from_qualification_id = t_qualifications_from.oid
|
|
LEFT JOIN rh.t_qualifications t_qualifications_to ON to_qualification_id = t_qualifications_to.oid
|
|
ORDER BY 4,5
|
|
";
|
|
break;
|
|
case 't_data_profile' :
|
|
$sqlcmd = "
|
|
SELECT oid, code, texte, texte_court,
|
|
TRIM(
|
|
CASE WHEN etablissement_inclus <> '' AND etablissement_inclus IS NOT NULL
|
|
THEN ' Etablissements ' || etablissement_inclus ||
|
|
CASE WHEN etablissement_exclus <> '' AND etablissement_exclus IS NOT NULL THEN ' sauf ' || etablissement_exclus ELSE '' END
|
|
ELSE '' END
|
|
||
|
|
CASE WHEN service_inclus <> '' AND service_inclus IS NOT NULL
|
|
THEN ' Services ' || service_inclus ||
|
|
CASE WHEN service_exclus <> '' AND service_exclus IS NOT NULL THEN ' sauf ' || service_exclus ELSE '' END
|
|
ELSE '' END
|
|
||
|
|
CASE WHEN statut_inclus <> '' AND statut_inclus IS NOT NULL
|
|
THEN ' Statuts ' || statut_inclus ||
|
|
CASE WHEN statut_exclus <> '' AND statut_exclus IS NOT NULL THEN ' sauf ' || statut_exclus ELSE '' END
|
|
ELSE '' END
|
|
) AS regles,
|
|
etablissement_inclus,
|
|
etablissement_exclus,
|
|
service_inclus,
|
|
service_exclus,
|
|
statut_inclus,
|
|
statut_exclus
|
|
FROM rh.t_data_profile
|
|
WHERE oid > 0
|
|
ORDER BY code
|
|
";
|
|
break;
|
|
case 't_population' :
|
|
$sqlcmd = "
|
|
SELECT
|
|
$tableName.oid
|
|
,$tableName.code
|
|
,$tableName.texte
|
|
,$tableName.texte_court
|
|
FROM $schema.$tableName
|
|
WHERE $tableName.oid > 0
|
|
ORDER BY code
|
|
";
|
|
break;
|
|
case 't_population_regle' :
|
|
$sqlcmd = "
|
|
SELECT
|
|
$tableName.oid
|
|
,$tableName.code
|
|
,$tableName.texte
|
|
$sqlColumnAdd
|
|
FROM $schema.$tableName
|
|
$sqlTableJoin
|
|
WHERE $tableName.oid > 0
|
|
ORDER BY code
|
|
";
|
|
break;
|
|
default :
|
|
$sqlcmd = "
|
|
SELECT
|
|
$tableName.oid
|
|
,$tableName.code
|
|
,$tableName.texte
|
|
,$tableName.texte_court
|
|
,$tableName.code_original
|
|
$sqlColumnAdd
|
|
FROM $schema.$tableName
|
|
$sqlTableJoin
|
|
WHERE $tableName.oid > 0
|
|
ORDER BY code
|
|
";
|
|
break;
|
|
}
|
|
|
|
$result = $db->exec($sqlcmd);
|
|
|
|
if ($result != false) {
|
|
// lignes
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$nbref = 0;
|
|
$httpString .= "\r\n<RECORD ";
|
|
$httpString .= 'oid="'.$record['oid'].'" ';
|
|
$httpString .= 'code="'.toHTML(trim($record['code'])).'" ';
|
|
$httpString .= 'texte="'.toHTML(trim($record['texte'])).'" ';
|
|
$httpString .= 'texte_court="'.toHTML(trim($record['texte_court'])).'" ';
|
|
$httpString .= 'code_original="'.toHTML(trim($record['code_original'])).'" ';
|
|
$httpString .= 'code_calcul="'.($tableName == 't_rubriques' ? toHTML(trim($record['code_calcul'])) : '0').'" ';
|
|
$httpString .= 'unite="'.($tableName == 't_rubriques' ? toHTML(trim($record['unite'])) : '0').'" ';
|
|
$httpString .= 'coefficient="'.($tableName == 't_rubriques' ? toHTML(trim($record['coefficient'])) : '0').'" ';
|
|
$httpString .= 'coefficient_base="'.($tableName == 't_rubriques' ? toHTML(trim($record['coefficient_base'])) : '0').'" ';
|
|
$httpString .= 'coefficient_nombre="'.($tableName == 't_rubriques' ? toHTML(trim($record['coefficient_nombre'])) : '0').'" ';
|
|
$httpString .= 'coefficient_txs="'.($tableName == 't_rubriques' ? toHTML(trim($record['coefficient_txs'])) : '0').'" ';
|
|
$httpString .= 'coefficient_txp="'.($tableName == 't_rubriques' ? toHTML(trim($record['coefficient_txp'])) : '0').'" ';
|
|
$httpString .= 'base_calcul_etp="'.($tableName == 't_etablissements' ? toHTML(trim($record['base_calcul_etp'])) : '0').'" ';
|
|
$httpString .= 'code_ets_planning="'.($tableName == 't_etablissements' ? toHTML(trim($record['planning_code'])) : '').'" ';
|
|
$httpString .= 'finess_id="'.($tableName == 't_etablissements' ? toHTML(trim($record['finess_id'])) : '').'" ';
|
|
$httpString .= 'code_ent_planning="'.($tableName == 't_entreprises' ? toHTML(trim($record['planning_code'])) : '').'" ';
|
|
$httpString .= 'valeur="'.toHTML(trim(array_key_exists('valeur', $record) ? $record['valeur'] : '')).'" ';
|
|
$httpString .= 'valeur_date="'.toHTML(trim(array_key_exists('valeur_date', $record) ? $record['valeur_date'] : '')).'" ';
|
|
$httpString .= 'description="'.toHTML(trim(array_key_exists('description', $record) ? $record['description'] : '')).'" ';
|
|
$httpString .= 'show_info_module="'.toHTML(trim(array_key_exists('show_info_module', $record) ? $record['show_info_module']: '')).'" ';
|
|
$httpString .= 'complement="'.toHTML(trim(array_key_exists('complement', $record) ? $record['complement']: '')).'" ';
|
|
$httpString .= 'texte_date="'.($tableName == 't_transformation' ? toHTML(trim($record['texte_date'])) : '').'" ';
|
|
$httpString .= 'texte_from="'.($tableName == 't_transformation' ? toHTML(trim($record['texte_from'])) : '').'" ';
|
|
$httpString .= 'texte_to="'.($tableName == 't_transformation' ? toHTML(trim($record['texte_to'])) : '').'" ';
|
|
$httpString .= 'date_debut="'.($tableName == 't_transformation' ? toHTML(trim($record['date_debut'])) : '').'" ';
|
|
$httpString .= 'date_fin="'.($tableName == 't_transformation' ? toHTML(trim($record['date_fin'])) : '').'" ';
|
|
$httpString .= 'salarie_id="'.($tableName == 't_transformation' ? toHTML(trim($record['salarie_id'])) : '').'" ';
|
|
$httpString .= 'from_service_id="'.($tableName == 't_transformation' ? toHTML(trim($record['from_service_id'])) : '').'" ';
|
|
$httpString .= 'to_service_id="'.($tableName == 't_transformation' ? toHTML(trim($record['to_service_id'])) : '').'" ';
|
|
$httpString .= 'from_qualification_id="'.($tableName == 't_transformation' ? toHTML(trim($record['from_qualification_id'])) : '').'" ';
|
|
$httpString .= 'to_qualification_id ="'.($tableName == 't_transformation' ? toHTML(trim($record['to_qualification_id'])) : '').'" ';
|
|
$httpString .= 'regles="'.($tableName == 't_data_profile' ? toHTML(trim($record['regles'])) : '').'" ';
|
|
$httpString .= 'etablissement_inclus="'.($tableName == 't_data_profile' ? toHTML(trim($record['etablissement_inclus'])) : '').'" ';
|
|
$httpString .= 'etablissement_exclus="'.($tableName == 't_data_profile' ? toHTML(trim($record['etablissement_exclus'])) : '').'" ';
|
|
$httpString .= 'service_inclus="'.($tableName == 't_data_profile' ? toHTML(trim($record['service_inclus'])) : '').'" ';
|
|
$httpString .= 'service_exclus="'.($tableName == 't_data_profile' ? toHTML(trim($record['service_exclus'])) : '').'" ';
|
|
$httpString .= 'statut_inclus="'.($tableName == 't_data_profile' ? toHTML(trim($record['statut_inclus'])) : '').'" ';
|
|
$httpString .= 'statut_exclus="'.($tableName == 't_data_profile' ? toHTML(trim($record['statut_exclus'])) : '').'" ';
|
|
|
|
$httpString .= 'user_modified="'.toHTML(trim(array_key_exists('user_modified', $record) ? $record['user_modified'] : '')).'" ';
|
|
$httpString .= 'p_cumul="'.toHTML(trim(array_key_exists('p_cumul', $record) ? $record['p_cumul'] : '')).'" ';
|
|
$httpString .= 'p_detail="'.toHTML(trim(array_key_exists('p_detail', $record) ? $record['p_detail'] : '')).'" ';
|
|
$httpString .= 'proratiser_conversion="'.toHTML(trim(array_key_exists('proratiser_conversion', $record) ? $record['proratiser_conversion'] : '')).'" ';
|
|
|
|
$tmpRubArray = array(
|
|
"nombre",
|
|
"base",
|
|
"heures_contrat",
|
|
"heures_payees",
|
|
"heures_travaillees",
|
|
"masse_salariale",
|
|
"brut",
|
|
"avantage_nature",
|
|
"frais_imposables",
|
|
"cotisation_salarie",
|
|
"cotisation_patronale",
|
|
"od_net_salarie",
|
|
"od_net_patronale",
|
|
"net_imposable",
|
|
"net_a_payer",
|
|
"nombre_provisions",
|
|
"montant_provisions",
|
|
"masse_salariale_provisionnee"
|
|
);
|
|
|
|
foreach ($tmpRubArray as $ind) {
|
|
$httpString .= 'p_'.$ind.'="'.toHTML(trim($record['p_'.$ind])).'" ';
|
|
$httpString .= 's_'.$ind.'="'.toHTML(trim($record['s_'.$ind])).'" ';
|
|
$httpString .= 'c_'.$ind.'="'.toHTML(trim($record['c_'.$ind])).'" ';
|
|
}
|
|
|
|
$httpString .= 'priorite="' . toHTML(trim(array_key_exists('priorite', $record) ? $record['priorite'] : '')).'" ';
|
|
$httpString .= 'population_id="' . toHTML(trim(array_key_exists('population_id', $record) ? $record['population_id'] : '')).'" ';
|
|
$httpString .= 'population_texte="' . toHTML(trim(array_key_exists('population_texte', $record) ? $record['population_texte'] : '')).'" ';
|
|
$httpString .= 'regle_resume="' . toHTML(trim(array_key_exists('regle_resume', $record) ? $record['regle_resume'] : '')).'" ';
|
|
$httpString .= 'liste_finess="' . toHTML(trim(array_key_exists('liste_finess', $record) ? $record['liste_finess'] : '')).'" ';
|
|
$httpString .= 'liste_etablissement="' . toHTML(trim(array_key_exists('liste_etablissement', $record) ? $record['liste_etablissement'] : '')).'" ';
|
|
$httpString .= 'liste_service="' . toHTML(trim(array_key_exists('liste_service', $record) ? $record['liste_service'] : '')).'" ';
|
|
$httpString .= 'liste_specialite="' . toHTML(trim(array_key_exists('liste_specialite', $record) ? $record['liste_specialite'] : '')).'" ';
|
|
$httpString .= 'liste_section_analytique="' . toHTML(trim(array_key_exists('liste_section_analytique', $record) ? $record['liste_section_analytique'] : '')).'" ';
|
|
$httpString .= 'liste_section_analytique_paie="' . toHTML(trim(array_key_exists('liste_section_analytique_paie', $record) ? $record['liste_section_analytique_paie'] : '')).'" ';
|
|
$httpString .= 'liste_qualification="' . toHTML(trim(array_key_exists('liste_qualification', $record) ? $record['liste_qualification'] : '')).'" ';
|
|
$httpString .= 'liste_grille="' . toHTML(trim(array_key_exists('liste_grille', $record) ? $record['liste_grille'] : '')).'" ';
|
|
$httpString .= 'liste_code_emploi="' . toHTML(trim(array_key_exists('liste_code_emploi', $record) ? $record['liste_code_emploi'] : '')).'" ';
|
|
$httpString .= 'liste_categorie_socio_professionnelle="' . toHTML(trim(array_key_exists('liste_categorie_socio_professionnelle', $record) ? $record['liste_categorie_socio_professionnelle'] : '')).'" ';
|
|
$httpString .= 'liste_statut="' . toHTML(trim(array_key_exists('liste_statut', $record) ? $record['liste_statut'] : '')).'" ';
|
|
|
|
$httpString .= 'service_id ="'.(($schema == 'rh' && $tableName == 't_planning_service') ? toHTML(trim($record['service_id'])) : '').'" ';
|
|
$httpString .= 'service_paie_associe_texte ="'.(($schema == 'rh' && $tableName == 't_planning_service') ? toHTML(trim($record['service_paie_associe_texte'])) : '').'" ';
|
|
$httpString .= 'qualification_id ="'.(($schema == 'rh' && $tableName == 't_planning_qualification') ? toHTML(trim($record['qualification_id'])) : '').'" ';
|
|
$httpString .= 'qualification_paie_associe_texte ="'.(($schema == 'rh' && $tableName == 't_planning_qualification') ? toHTML(trim($record['qualification_paie_associe_texte'])) : '').'" ';
|
|
$httpString .= 'nbref="+'.$nbref.'" ';
|
|
$httpString .= ' />';
|
|
}
|
|
}
|
|
}
|
|
|
|
// Infos complémentaires.
|
|
switch ($tableName) {
|
|
case 't_etablissements' :
|
|
$httpString .= getCombos_rh_finess($db);
|
|
break;
|
|
case 't_transformation' :
|
|
$httpString .= getCombos_rh_transformation($db);
|
|
break;
|
|
case 't_rubriques' :
|
|
$httpString .= get_rh_divers($db);
|
|
break;
|
|
case 't_planning_service' :
|
|
$httpString .= getCombos_rh_planning_service($db);
|
|
break;
|
|
case 't_planning_qualification' :
|
|
$httpString .= getCombos_rh_planning_qualification($db);
|
|
break;
|
|
case 't_population_regle' :
|
|
$httpString .= getCombo_population($db);
|
|
break;
|
|
}
|
|
|
|
return $httpString;
|
|
}
|
|
|
|
function get_rh_divers($db) {
|
|
$httpString = "\n<DIVERS>";
|
|
$sqlcmd = "
|
|
SELECT valeur
|
|
FROM rh.t_divers
|
|
WHERE code ='OPT_STD_HP'";
|
|
$result = $db->exec($sqlcmd);
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$httpString .= "\n<RECORD valeur=\"".toHTML(trim($record["valeur"]))."\" />";
|
|
}
|
|
}
|
|
}
|
|
$httpString .= "\n</DIVERS>";
|
|
|
|
|
|
return $httpString;
|
|
}
|
|
|
|
function getCombos_rh_transformation($db) {
|
|
|
|
$httpString = "";
|
|
|
|
$httpString_sa = "\r\n<COMBO_SAL>";
|
|
$httpString_sa .= "\r\n<RECORD oid=\"-1\" texte=\"Tous les salariés\" />";
|
|
$sqlcmd = "SELECT oid, nom || ' ' || prenom || ' ('::text || matricule || ')'::text AS texte FROM rh.p_salaries WHERE oid >= 0 ORDER BY 2";
|
|
$result = $db->exec($sqlcmd);
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$oid = $record["oid"];
|
|
$texte = toHTML(trim($record["texte"]));
|
|
$httpString_sa .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
}
|
|
}
|
|
}
|
|
$httpString_sa .= "\r\n</COMBO_SAL>";
|
|
|
|
|
|
$httpString_fs = "\r\n<COMBO_FROMSER>";
|
|
$httpString_fs .= "\r\n<RECORD oid=\"-1\" texte=\"Tous les services\" />";
|
|
$httpString_ts = "\r\n<COMBO_TOSER>";
|
|
$httpString_ts .= "\r\n<RECORD oid=\"-1\" texte=\"Pas de changement\" />";
|
|
$sqlcmd = "SELECT oid, texte || ' ('::text || code || ')'::text AS texte FROM rh.t_services WHERE oid >= 0 ORDER BY 2";
|
|
$result = $db->exec($sqlcmd);
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$oid = $record["oid"];
|
|
$texte = toHTML(trim($record["texte"]));
|
|
$httpString_fs .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
$httpString_ts .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
}
|
|
}
|
|
}
|
|
$httpString_fs .= "\r\n</COMBO_FROMSER>";
|
|
$httpString_ts .= "\r\n</COMBO_TOSER>";
|
|
|
|
$httpString_fq = "\r\n<COMBO_FROMQUA>";
|
|
$httpString_fq .= "\r\n<RECORD oid=\"-1\" texte=\"Tous les qualifications\" />";
|
|
$httpString_tq = "\r\n<COMBO_TOQUA>";
|
|
$httpString_tq .= "\r\n<RECORD oid=\"-1\" texte=\"Pas de changement\" />";
|
|
$sqlcmd = "SELECT oid, texte || ' ('::text || code || ')'::text AS texte FROM rh.t_qualifications WHERE oid >= 0 ORDER BY 2";
|
|
$result = $db->exec($sqlcmd);
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$oid = $record["oid"];
|
|
$texte = toHTML(trim($record["texte"]));
|
|
$httpString_fq .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
$httpString_tq .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
}
|
|
}
|
|
}
|
|
$httpString_fq .= "\r\n</COMBO_FROMQUA>";
|
|
$httpString_tq .= "\r\n</COMBO_TOQUA>";
|
|
|
|
$httpString_fd = "\r\n<COMBO_FROMDAT>";
|
|
$httpString_fd .= "\r\n<RECORD oid=\"0001-01-01\" texte=\"Depuis le début\" />";
|
|
$httpString_td = "\r\n<COMBO_TODAT>";
|
|
$httpString_td .= "\r\n<RECORD oid=\"2099-12-31\" texte=\"Jusqu'à la fin\" />";
|
|
$sqlcmd = "SELECT p_calendrier_annee.date_debut, p_calendrier_annee.date_fin, p_calendrier_annee.texte
|
|
FROM rh.p_chiffrier_production
|
|
JOIN base.p_calendrier_mois ON (p_chiffrier_production.mois = p_calendrier_mois.mois)
|
|
JOIN base.p_calendrier_mois p_calendrier_annee ON (p_calendrier_annee.annee = p_calendrier_mois.annee)
|
|
GROUP BY 1,2,3
|
|
ORDER BY 1 DESC";
|
|
$result = $db->exec($sqlcmd);
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$date_debut = $record["date_debut"];
|
|
$date_fin = $record["date_fin"];
|
|
$texte = toHTML(trim($record["texte"]));
|
|
$httpString_fd .= "\r\n<RECORD oid=\"$date_debut\" texte=\"$texte\" />";
|
|
$httpString_td .= "\r\n<RECORD oid=\"$date_fin\" texte=\"$texte\" />";
|
|
}
|
|
}
|
|
}
|
|
$httpString_fd .= "\r\n</COMBO_FROMDAT>";
|
|
$httpString_td .= "\r\n</COMBO_TODAT>";
|
|
|
|
|
|
|
|
return $httpString_sa.$httpString_fs.$httpString_ts.$httpString_fq.$httpString_tq.$httpString_fd.$httpString_td;
|
|
}
|
|
|
|
function getCombos_rh_planning_service($db) {
|
|
|
|
$httpString = "";
|
|
|
|
$httpString = "\r\n<COMBO_SER>";
|
|
$httpString .= "\r\n<RECORD oid=\"0\" texte=\"Aucun\" />";
|
|
$sqlcmd = "SELECT oid, code || ' '::text || texte AS texte FROM rh.t_services WHERE oid > 0 ORDER BY 2";
|
|
$result = $db->exec($sqlcmd);
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$oid = $record["oid"];
|
|
$texte = toHTML(trim($record["texte"]));
|
|
$httpString .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
}
|
|
}
|
|
}
|
|
$httpString .= "\r\n</COMBO_SER>";
|
|
|
|
|
|
return $httpString;
|
|
}
|
|
|
|
function getCombos_rh_planning_qualification($db) {
|
|
|
|
$httpString = "";
|
|
|
|
$httpString = "\r\n<COMBO_QUA>";
|
|
$httpString .= "\r\n<RECORD oid=\"0\" texte=\"Aucun\" />";
|
|
$sqlcmd = "SELECT oid, code || ' '::text || texte AS texte FROM rh.t_qualifications WHERE oid > 0 ORDER BY 2";
|
|
$result = $db->exec($sqlcmd);
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$oid = $record["oid"];
|
|
$texte = toHTML(trim($record["texte"]));
|
|
$httpString .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
}
|
|
}
|
|
}
|
|
$httpString .= "\r\n</COMBO_QUA>";
|
|
|
|
|
|
return $httpString;
|
|
}
|
|
|
|
function getCombos_rh_finess($db) {
|
|
|
|
$httpString = "\r\n<COMBO_FINESS>";
|
|
$httpString .= "\r\n<RECORD oid=\"0\" texte=\"Aucun\" />";
|
|
|
|
$sqlcmd = "SELECT oid, code || ' '::text || texte AS texte FROM base.t_finess WHERE oid > 0 ORDER BY 2 ";
|
|
$result = $db->exec($sqlcmd);
|
|
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordAssoc();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
$oid = $record["oid"];
|
|
$texte = toHTML(trim($record["texte"]));
|
|
$httpString .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
}
|
|
}
|
|
}
|
|
$httpString .= "\r\n</COMBO_FINESS>";
|
|
|
|
return $httpString;
|
|
}
|
|
|
|
function getCombo_population($db) {
|
|
$httpString = "\r\n<COMBO_POP>\r\n<RECORD oid=\"0\" texte=\"Aucune\" />";
|
|
|
|
$sqlcmd = "SELECT oid, code::text || ' '::text || texte::text FROM rh.t_population WHERE oid > 0 ORDER BY code";
|
|
$result = $db->exec($sqlcmd);
|
|
|
|
if ($result != false) {
|
|
// lignes
|
|
$ok = TRUE;
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
$record = $db->nextRecordInto();
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
|
|
$oid = $record[0];
|
|
$texte = toHTML(trim($record[1]));
|
|
|
|
$httpString .= "\r\n<RECORD oid=\"$oid\" texte=\"$texte\" />";
|
|
}
|
|
}
|
|
}
|
|
|
|
$httpString .= "\r\n</COMBO_POP>";
|
|
|
|
return $httpString;
|
|
}
|
|
|
|
|
|
function getRecords_expert_controle() {
|
|
|
|
global $database;
|
|
|
|
$httpString = "";
|
|
$httpString_tmp = "";
|
|
|
|
// Activation module EXPERT
|
|
$sqlcmd = "
|
|
INSERT INTO rh.t_divers (code, texte, valeur, description)
|
|
SELECT
|
|
'EXPERT_ACTIVE',
|
|
'Module EXPERT activ?',
|
|
'0',
|
|
'1=Activ?'
|
|
WHERE 'EXPERT_ACTIVE' NOT IN (SELECT code FROM rh.t_divers);
|
|
INSERT INTO rh.t_divers (code, texte, valeur, description, valeur_date)
|
|
SELECT
|
|
'EXPERT_DATE_DEBUT',
|
|
'Date données à contrôler',
|
|
'20130101',
|
|
'Date de début des données à contrôler',
|
|
'20130101'
|
|
WHERE 'EXPERT_DATE_DEBUT' NOT IN (SELECT code FROM rh.t_divers);";
|
|
$result = $database->exec($sqlcmd);
|
|
|
|
// Correction données antérieures
|
|
$sqlcmd = "UPDATE rh.t_expert_controle_rule
|
|
SET numero = 1
|
|
WHERE numero NOT IN (1,2,3) OR numero IS NULL;
|
|
|
|
UPDATE rh.t_expert_controle
|
|
SET gravite_id = 5
|
|
WHERE gravite_id NOT IN (0,5,9) OR gravite_id IS NULL;";
|
|
|
|
$result = $database->exec($sqlcmd);
|
|
|
|
// Paramétres généraux
|
|
$EXPERT_ACTIVE = "0";
|
|
$EXPERT_DATE_DEBUT = "2013-01-01";
|
|
|
|
$sqlcmd = "SELECT code, valeur, to_char(valeur_date,'DD/MM/YYYY')
|
|
FROM rh.t_divers
|
|
WHERE code LIKE 'EXPERT%'";
|
|
$result = $database->exec($sqlcmd);
|
|
if ($result != false) {
|
|
$ok = TRUE;
|
|
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
|
|
$record = $database->nextRecordInto();
|
|
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
|
|
switch(trim($record[0])) {
|
|
case "EXPERT_ACTIVE" :
|
|
$EXPERT_ACTIVE = trim($record[1]);
|
|
break;
|
|
case "EXPERT_DATE_DEBUT" :
|
|
$EXPERT_DATE_DEBUT = trim($record[2]);
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Chargement des Prestataires RH
|
|
$sqlcmd = "
|
|
(SELECT
|
|
-1 as oid,
|
|
'Tous'::text as texte,
|
|
0 as tri
|
|
UNION
|
|
SELECT
|
|
oid,
|
|
texte,
|
|
1 as tri
|
|
FROM base.t_prestataires
|
|
WHERE 'rh' = ANY (modules)
|
|
)
|
|
ORDER BY tri,texte";
|
|
$result = $database->exec($sqlcmd);
|
|
|
|
if ($result !== false) {
|
|
|
|
while ($row = pg_fetch_array($result)) {
|
|
|
|
$oid = $row['oid'];
|
|
$texte = $row['texte'];
|
|
|
|
$httpString .= "<PRESTATAIRE data=\"$oid\" label=\"$texte\" />" . PHP_EOL;
|
|
}
|
|
}
|
|
|
|
$httpString_tmp .= "<GENERAL_SETTINGS
|
|
EXPERT_ACTIVE=\"$EXPERT_ACTIVE\"
|
|
EXPERT_DATE_DEBUT=\"$EXPERT_DATE_DEBUT\"
|
|
/>";
|
|
|
|
// Liste des controles
|
|
$sqlcmd = "SELECT
|
|
t_expert_controle.oid as controle_id,
|
|
t_expert_controle.code as controle_code,
|
|
t_expert_controle.texte as controle_texte,
|
|
COALESCE(t_expert_controle.texte_court,t_expert_controle.texte) as controle_texte_court,
|
|
COALESCE(t_expert_controle.texte_court,t_expert_controle.texte,t_expert_controle.texte_abrege) as controle_texte_abrege,
|
|
t_expert_controle.description as controle_description,
|
|
t_expert_controle.is_cti as controle_is_cti,
|
|
t_expert_controle.is_hide as controle_is_hide,
|
|
t_expert_controle.gravite_id as controle_gravite,
|
|
t_expert_controle.prestataire_id,
|
|
t_expert_controle.is_active as controle_is_active,
|
|
COALESCE(t_prestataires.texte, 'Tous') as prestataire_texte,
|
|
COALESCE(t_expert_controle_rule_1.oid,0) as rule1_oid,
|
|
COALESCE(t_expert_controle_rule_1.sqlcmd_where) as rule1_sqlcmd_where,
|
|
COALESCE(t_expert_controle_rule_1.sqlcmd_justificatif) as rule1_sqlcmd_justificatif,
|
|
COALESCE(TO_CHAR(t_expert_controle_rule_1.last_execution_date,'DD/MM/YYYY'),'') as rule1_last_execution_date,
|
|
COALESCE(t_expert_controle_rule_1.last_execution_ok) as rule1_last_execution_ok,
|
|
COALESCE(t_expert_controle_rule_1.table_id,0) as rule1_table_id,
|
|
COALESCE(subview.nbref, 0) as nb_ref,
|
|
COALESCE(t_expert_controle.alert_exp,'0') as is_alert
|
|
FROM rh.t_expert_controle
|
|
LEFT JOIN base.t_prestataires ON t_prestataires.oid = t_expert_controle.prestataire_id
|
|
LEFT JOIN rh.t_expert_controle_rule t_expert_controle_rule_1 ON t_expert_controle_rule_1.controle_id = t_expert_controle.oid AND t_expert_controle_rule_1.numero = 1
|
|
LEFT JOIN (SELECT controle_id, count(*) AS nbref FROM rh.p_expert_controle GROUP BY 1) subview ON t_expert_controle.oid = subview.controle_id
|
|
WHERE t_expert_controle.oid > 0 AND is_global <> 1
|
|
ORDER BY t_expert_controle.code";
|
|
$result = $database->exec($sqlcmd);
|
|
|
|
if ($result != false) {
|
|
|
|
// lignes
|
|
$ok = TRUE;
|
|
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
|
|
$record = $database->nextRecordAssoc();
|
|
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
|
|
$oid = $record['controle_id'];
|
|
$code = toHTML(trim($record['controle_code']));
|
|
$texte = toHTML(trim($record['controle_texte']));
|
|
$texte_court = toHTML(trim($record['controle_texte_court']));
|
|
$texte_abrege = toHTML(trim($record['controle_texte_abrege']));
|
|
$description = toHTML(trim($record['controle_description']));
|
|
$is_cti = toHTML(trim($record['controle_is_cti']));
|
|
$is_active = toHTML(trim($record['controle_is_active']));
|
|
$is_hide = toHTML(trim($record['controle_is_hide']));
|
|
$gravite = floatval(trim($record['controle_gravite']));
|
|
$rule_oid1 = floatval(trim($record['rule1_oid']));
|
|
$sqlcmd_where1 = toHTML(trim($record['rule1_sqlcmd_where']));
|
|
$sqlcmd_justificatif1 = toHTML(trim($record['rule1_sqlcmd_justificatif']));
|
|
$table1 = floatval(trim($record['rule1_table_id']));
|
|
$nbref = floatval(trim($record['nb_ref']));
|
|
$last_execution_date = trim($record['rule1_last_execution_date']);
|
|
$last_execution_ok = floatval(trim($record['rule1_last_execution_ok']));
|
|
$prestataire_id = floatval(trim($record['prestataire_id']));
|
|
$prestataire_texte = toHTML(trim($record['prestataire_texte']));
|
|
$is_alert = toHTML(trim($record['is_alert']));
|
|
|
|
if ($last_execution_date == "") {
|
|
$last_execution_date = "jamais exécuté";
|
|
$last_execution_ok = "";
|
|
}
|
|
else {
|
|
if ($last_execution_ok == "1") {
|
|
$last_execution_ok = "OK";
|
|
}
|
|
if ($last_execution_ok == "0") {
|
|
$last_execution_ok = "KO";
|
|
}
|
|
}
|
|
|
|
$complement = "";
|
|
if ($is_cti == "1") {
|
|
$complement = "$complement CTI";
|
|
}
|
|
else {
|
|
$is_cti = "0";
|
|
}
|
|
if ($is_hide == "1") {
|
|
$complement = "$complement Masqu?";
|
|
}
|
|
else {
|
|
$is_hide = "0";
|
|
}
|
|
if ($is_alert == "1") {
|
|
$complement = "$complement Alerte";
|
|
}
|
|
else {
|
|
$is_alert = "0";
|
|
}
|
|
if ($gravite == 0) {
|
|
$gravite_texte = "INFO";
|
|
}
|
|
else {
|
|
if ($gravite == 9) {
|
|
$gravite_texte = "ERREUR";
|
|
}
|
|
else {
|
|
$gravite_texte = "ATT";
|
|
}
|
|
}
|
|
$is_active_texte = $is_active == "1" ? "Actif" : "Inactif";
|
|
|
|
$md5_def = refmd5("$code$texte$texte_court$texte_abrege$description$is_cti$prestataire_id");
|
|
$md5_sql = refmd5("$code$sqlcmd_where1$sqlcmd_justificatif1$table1");
|
|
|
|
$httpString_tmp .= "
|
|
<RECORD
|
|
selected=\"0\"
|
|
oid=\"$oid\"
|
|
code=\"$code\"
|
|
texte=\"$texte\"
|
|
texte_court=\"$texte_court\"
|
|
texte_abrege=\"$texte_abrege\"
|
|
description=\"$description\"
|
|
is_cti=\"$is_cti\"
|
|
is_hide=\"$is_hide\"
|
|
is_active=\"$is_active\"
|
|
is_active_texte=\"$is_active_texte\"
|
|
gravite=\"$gravite\"
|
|
gravite_texte=\"$gravite_texte\"
|
|
complement=\"$complement\"
|
|
rule_oid1=\"$rule_oid1\"
|
|
sqlcmd_where1=\"$sqlcmd_where1\"
|
|
sqlcmd_justificatif1=\"$sqlcmd_justificatif1\"
|
|
table1=\"$table1\"
|
|
prestataire_id=\"$prestataire_id\"
|
|
prestataire_texte=\"$prestataire_texte\"
|
|
nbref=\"$nbref\"
|
|
last_execution_date=\"$last_execution_date\"
|
|
last_execution_ok=\"$last_execution_ok\"
|
|
md5_def=\"$md5_def\"
|
|
md5_sql=\"$md5_sql\"
|
|
is_alert=\"$is_alert\"
|
|
/>";
|
|
|
|
if (strlen($httpString_tmp) > 64000) {
|
|
$httpString .= $httpString_tmp;
|
|
$httpString_tmp = "";
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
$httpString .= $httpString_tmp;
|
|
|
|
// R?f?rence
|
|
$httpString .= getRefSettings("t_expert_controle", $httpString);
|
|
|
|
return $httpString;
|
|
}
|
|
|
|
function getRecords_expert_controle_refsettings($refPrefix) {
|
|
|
|
global $database;
|
|
|
|
$httpString = "";
|
|
$httpString_tmp = "";
|
|
|
|
// Liste des controles
|
|
$sqlcmd = "SELECT
|
|
t_expert_controle.oid AS controle_id,
|
|
t_expert_controle.code AS controle_code,
|
|
t_expert_controle.texte AS controle_texte,
|
|
COALESCE(t_expert_controle.texte_court,t_expert_controle.texte) AS controle_texte_court,
|
|
COALESCE(t_expert_controle.texte_court,t_expert_controle.texte,t_expert_controle.texte_abrege) AS controle_texte_abrege,
|
|
t_expert_controle.description AS controle_description,
|
|
t_expert_controle.is_cti AS controle_is_cti,
|
|
t_expert_controle.is_hide AS controle_is_hide,
|
|
t_expert_controle.gravite_id AS controle_gravite,
|
|
t_expert_controle.prestataire_id,
|
|
COALESCE(t_prestataires.texte, 'Tous') as prestataire_texte,
|
|
COALESCE(t_expert_controle_rule_1.oid,0) AS rule1_oid,
|
|
COALESCE(t_expert_controle_rule_1.sqlcmd_where) AS rule1_sqlcmd_where,
|
|
COALESCE(t_expert_controle_rule_1.sqlcmd_justificatif) AS rule1_sqlcmd_justificatif,
|
|
COALESCE(TO_CHAR(t_expert_controle_rule_1.last_execution_date,'DD/MM/YYYY'),'') AS rule1_last_execution_date,
|
|
COALESCE(t_expert_controle_rule_1.last_execution_ok) AS rule1_last_execution_ok,
|
|
COALESCE(t_expert_controle_rule_1.table_id,0) AS rule1_table_id,
|
|
t_expert_controle.alert_exp as is_alert
|
|
FROM rh.t_expert_controle
|
|
LEFT JOIN base.t_prestataires ON t_prestataires.oid = t_expert_controle.prestataire_id
|
|
LEFT JOIN rh.t_expert_controle_rule t_expert_controle_rule_1 ON t_expert_controle_rule_1.controle_id = t_expert_controle.oid AND t_expert_controle_rule_1.numero = 1
|
|
WHERE t_expert_controle.oid > 0 AND is_global <> 1 AND t_expert_controle.code ILIKE '$refPrefix%'
|
|
ORDER BY t_expert_controle.code";
|
|
$result = $database->exec($sqlcmd);
|
|
|
|
if ($result != false) {
|
|
|
|
// lignes
|
|
$ok = TRUE;
|
|
|
|
while ($ok == TRUE) {
|
|
$ok = FALSE;
|
|
|
|
$record = $database->nextRecordInto();
|
|
|
|
if ($record != FALSE) {
|
|
$ok = TRUE;
|
|
|
|
$oid = $record['controle_id'];
|
|
$code = toHTML(trim($record['controle_code']));
|
|
$texte = toHTML(trim($record['controle_texte']));
|
|
$texte_court = toHTML(trim($record['controle_texte_court']));
|
|
$texte_abrege = toHTML(trim($record['controle_texte_abrege']));
|
|
$description = toHTML(trim($record['controle_description']));
|
|
$is_cti = toHTML(trim($record['controle_is_cti']));
|
|
$is_hide = toHTML(trim($record['controle_is_hide']));
|
|
$gravite = floatval(trim($record['controle_gravite']));
|
|
$rule_oid1 = floatval(trim($record['rule1_oid']));
|
|
$sqlcmd_where1 = toHTML(trim($record['rule1_sqlcmd_where']));
|
|
$sqlcmd_justificatif1 = toHTML(trim($record['rule1_sqlcmd_justificatif']));
|
|
$last_execution_date = toHTML(trim($record['rule1_last_execution_date']));
|
|
$last_execution_ok = toHTML(trim($record['rule1_last_execution_ok']));
|
|
$table1 = floatval(trim($record['rule1_table_id']));
|
|
$prestataire_id = floatval(trim($record['prestataire_id']));
|
|
$prestataire_texte = toHTML(trim($record['prestataire_texte']));
|
|
$is_alert = toHTML(trim($record['is_alert']));
|
|
|
|
if ($last_execution_date == "") {
|
|
$last_execution_ok = "";
|
|
}
|
|
else {
|
|
if ($last_execution_ok == "1") {
|
|
$last_execution_ok = "OK";
|
|
}
|
|
if ($last_execution_ok == "0") {
|
|
$last_execution_ok = "KO";
|
|
}
|
|
}
|
|
|
|
$complement = "";
|
|
if ($is_cti == "1") {
|
|
$complement = "$complement CTI";
|
|
}
|
|
else {
|
|
$is_cti = "0";
|
|
}
|
|
if ($is_hide == "1") {
|
|
$complement = "$complement Masqu?";
|
|
}
|
|
else {
|
|
$is_hide = "0";
|
|
}
|
|
if ($gravite == 0) {
|
|
$gravite_texte = "INFO";
|
|
}
|
|
else {
|
|
if ($gravite == 9) {
|
|
$gravite_texte = "ERREUR";
|
|
}
|
|
else {
|
|
$gravite_texte = "ATT";
|
|
}
|
|
}
|
|
$complement = trim($complement);
|
|
|
|
$md5_def = refmd5("$code$texte$texte_court$texte_abrege$description$is_cti$prestataire_id");
|
|
$md5_sql = refmd5("$code$sqlcmd_where1$sqlcmd_justificatif1$table1");
|
|
|
|
$httpString_tmp .= "
|
|
<RECORD
|
|
selected=\"0\"
|
|
oid=\"$oid\"
|
|
code=\"$code\"
|
|
texte=\"$texte\"
|
|
texte_court=\"$texte_court\"
|
|
texte_abrege=\"$texte_abrege\"
|
|
is_cti=\"$is_cti\"
|
|
is_hide=\"$is_hide\"
|
|
gravite=\"$gravite\"
|
|
gravite_texte=\"$gravite_texte\"
|
|
complement=\"$complement\"
|
|
prestataire_id=\"$prestataire_id\"
|
|
prestataire_texte=\"$prestataire_texte\"
|
|
md5_def=\"$md5_def\"
|
|
md5_sql=\"$md5_sql\"
|
|
is_alert=\"$is_alert\"
|
|
/>";
|
|
|
|
if (strlen($httpString_tmp) > 64000) {
|
|
$httpString .= $httpString_tmp;
|
|
$httpString_tmp = "";
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
$httpString .= $httpString_tmp;
|
|
return $httpString;
|
|
}
|
|
|
|
function refmd5($string) {
|
|
$string = str_replace("\r","",$string);
|
|
$string = str_replace("\n","",$string);
|
|
$string = md5(trim($string));
|
|
return $string;
|
|
}
|
|
|
|
// recherche des listes de r?ference
|
|
function getRefSettings($tableName, $httpString_base) {
|
|
|
|
global $getOption;
|
|
|
|
$httpString = "";
|
|
|
|
$SETTINGS_file = rootDir() . "settings/settings.XML";
|
|
$SETTINGS_xml = simplexml_load_file($SETTINGS_file);
|
|
$REFSETTINGS_URL = getProperty($SETTINGS_xml, "REFSETTINGS", 'URL');
|
|
$REFSETTINGS_PREFIX = getProperty($SETTINGS_xml, "REFSETTINGS", 'PREFIX');
|
|
|
|
if ($REFSETTINGS_URL != "" && extension_loaded('curl')) {
|
|
|
|
// Recherche des indicateurs de l'environnement de r?f?rence
|
|
$REFSETTINGS_table_url = "$REFSETTINGS_URL/modules/rh/php/RH_getTableRecords.php";
|
|
|
|
// Recherche http pour indicateurs
|
|
$ch = curl_init();
|
|
curl_setopt($ch, CURLOPT_URL, $REFSETTINGS_table_url);
|
|
curl_setopt($ch, CURLOPT_POST, 2);
|
|
curl_setopt($ch, CURLOPT_POSTFIELDS, "tableName=$tableName&getOption=getRefSettings&refPrefix=$REFSETTINGS_PREFIX");
|
|
curl_setopt($ch, CURLOPT_HEADER, 0);
|
|
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
|
|
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
|
|
$REFSETTINGS_data = @curl_exec($ch);
|
|
curl_close($ch);
|
|
|
|
if ($REFSETTINGS_data) {
|
|
|
|
if (substr($REFSETTINGS_data, 0, 12) == "<CTICONTENT>") {
|
|
$REFSETTINGS_data = substr($REFSETTINGS_data, 12, strlen($REFSETTINGS_data) - 25);
|
|
}
|
|
|
|
$REFSETTINGS_data = uncompress($REFSETTINGS_data);
|
|
$REFSETTINGS_data = str_replace("<RECORD ","<REFRECORD ",$REFSETTINGS_data);
|
|
$REFSETTINGS_data = str_replace("</RECORD>","</REFRECORD>",$REFSETTINGS_data);
|
|
$REFSETTINGS_xml = simplexml_load_string($REFSETTINGS_data);
|
|
if ($REFSETTINGS_xml) {
|
|
|
|
foreach ($REFSETTINGS_xml->REFRECORD as $recordNode) {
|
|
$md5_def = trim($recordNode["md5_def"]);
|
|
$md5_sql = trim($recordNode["md5_sql"]);
|
|
$recordNode["ok"] = "";
|
|
if (stripos($httpString_base, $md5_def) === false) {
|
|
$recordNode["ok"] = "D?finition";
|
|
}
|
|
if (stripos($httpString_base, $md5_sql) === false) {
|
|
$recordNode["ok"] = "R?gles";
|
|
}
|
|
//if ($recordNode["ok"] != "") {
|
|
$httpString .= $recordNode->asXML();
|
|
//}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return $httpString;
|
|
}
|
|
|
|
|
|
?>
|