<?php
//$Id$ 
//gen openMairie le 05/04/2016 16:24

$DEBUG=0;
$serie=15;
$ico="../img/ico_application.png";
$ent = _("application")." -> "._("instruction");
if(!isset($premier)) $premier='';
if(!isset($recherche1)) $recherche1='';
if(!isset($tricolsf)) $tricolsf='';
if(!isset($premiersf)) $premiersf='';
if(!isset($selection)) $selection='';
if(!isset($retourformulaire)) $retourformulaire='';
if (isset($idx) && $idx != ']' && trim($idx) != '') {
    $ent .= "->&nbsp;".$idx."&nbsp;";
}
if (isset($idz) && trim($idz) != '') {
    $ent .= "&nbsp;".strtoupper($idz)."&nbsp;";
}
// FROM 
$table = DB_PREFIXE."instruction
    LEFT JOIN ".DB_PREFIXE."action 
        ON instruction.action=action.action 
    LEFT JOIN ".DB_PREFIXE."etat as etat1 
        ON instruction.archive_etat_pendant_incompletude=etat1.etat 
    LEFT JOIN ".DB_PREFIXE."evenement as evenement2 
        ON instruction.archive_evenement_suivant_tacite=evenement2.evenement 
    LEFT JOIN ".DB_PREFIXE."evenement as evenement3 
        ON instruction.archive_evenement_suivant_tacite_incompletude=evenement3.evenement 
    LEFT JOIN ".DB_PREFIXE."autorite_competente 
        ON instruction.autorite_competente=autorite_competente.autorite_competente 
    LEFT JOIN ".DB_PREFIXE."avis_decision 
        ON instruction.avis_decision=avis_decision.avis_decision 
    LEFT JOIN ".DB_PREFIXE."document_numerise 
        ON instruction.document_numerise=document_numerise.document_numerise 
    LEFT JOIN ".DB_PREFIXE."dossier 
        ON instruction.dossier=dossier.dossier 
    LEFT JOIN ".DB_PREFIXE."etat as etat8 
        ON instruction.etat=etat8.etat 
    LEFT JOIN ".DB_PREFIXE."evenement as evenement9 
        ON instruction.evenement=evenement9.evenement 
    LEFT JOIN ".DB_PREFIXE."signataire_arrete 
        ON instruction.signataire_arrete=signataire_arrete.signataire_arrete ";
// SELECT 
$champAffiche = array(
    'instruction.instruction as "'._("instruction").'"',
    'instruction.destinataire as "'._("destinataire").'"',
    'to_char(instruction.date_evenement ,\'DD/MM/YYYY\') as "'._("date_evenement").'"',
    'evenement9.libelle as "'._("evenement").'"',
    'instruction.lettretype as "'._("lettretype").'"',
    'dossier.annee as "'._("dossier").'"',
    'action.libelle as "'._("action").'"',
    'instruction.delai as "'._("delai").'"',
    'etat8.libelle as "'._("etat").'"',
    'instruction.accord_tacite as "'._("accord_tacite").'"',
    'instruction.delai_notification as "'._("delai_notification").'"',
    'instruction.archive_delai as "'._("archive_delai").'"',
    'to_char(instruction.archive_date_complet ,\'DD/MM/YYYY\') as "'._("archive_date_complet").'"',
    'to_char(instruction.archive_date_rejet ,\'DD/MM/YYYY\') as "'._("archive_date_rejet").'"',
    'to_char(instruction.archive_date_limite ,\'DD/MM/YYYY\') as "'._("archive_date_limite").'"',
    'to_char(instruction.archive_date_notification_delai ,\'DD/MM/YYYY\') as "'._("archive_date_notification_delai").'"',
    'instruction.archive_accord_tacite as "'._("archive_accord_tacite").'"',
    'instruction.archive_etat as "'._("archive_etat").'"',
    'to_char(instruction.archive_date_decision ,\'DD/MM/YYYY\') as "'._("archive_date_decision").'"',
    'instruction.archive_avis as "'._("archive_avis").'"',
    'to_char(instruction.archive_date_validite ,\'DD/MM/YYYY\') as "'._("archive_date_validite").'"',
    'to_char(instruction.archive_date_achevement ,\'DD/MM/YYYY\') as "'._("archive_date_achevement").'"',
    'to_char(instruction.archive_date_chantier ,\'DD/MM/YYYY\') as "'._("archive_date_chantier").'"',
    'to_char(instruction.archive_date_conformite ,\'DD/MM/YYYY\') as "'._("archive_date_conformite").'"',
    'avis_decision.libelle as "'._("avis_decision").'"',
    'to_char(instruction.date_finalisation_courrier ,\'DD/MM/YYYY\') as "'._("date_finalisation_courrier").'"',
    'to_char(instruction.date_envoi_signature ,\'DD/MM/YYYY\') as "'._("date_envoi_signature").'"',
    'to_char(instruction.date_retour_signature ,\'DD/MM/YYYY\') as "'._("date_retour_signature").'"',
    'to_char(instruction.date_envoi_rar ,\'DD/MM/YYYY\') as "'._("date_envoi_rar").'"',
    'to_char(instruction.date_retour_rar ,\'DD/MM/YYYY\') as "'._("date_retour_rar").'"',
    'to_char(instruction.date_envoi_controle_legalite ,\'DD/MM/YYYY\') as "'._("date_envoi_controle_legalite").'"',
    'to_char(instruction.date_retour_controle_legalite ,\'DD/MM/YYYY\') as "'._("date_retour_controle_legalite").'"',
    'signataire_arrete.civilite as "'._("signataire_arrete").'"',
    'instruction.numero_arrete as "'._("numero_arrete").'"',
    'to_char(instruction.archive_date_dernier_depot ,\'DD/MM/YYYY\') as "'._("archive_date_dernier_depot").'"',
    "case instruction.archive_incompletude when 't' then 'Oui' else 'Non' end as \""._("archive_incompletude")."\"",
    'evenement2.libelle as "'._("archive_evenement_suivant_tacite").'"',
    'evenement3.libelle as "'._("archive_evenement_suivant_tacite_incompletude").'"',
    'etat1.libelle as "'._("archive_etat_pendant_incompletude").'"',
    'to_char(instruction.archive_date_limite_incompletude ,\'DD/MM/YYYY\') as "'._("archive_date_limite_incompletude").'"',
    'instruction.archive_delai_incompletude as "'._("archive_delai_incompletude").'"',
    'instruction.code_barres as "'._("code_barres").'"',
    'instruction.om_fichier_instruction as "'._("om_fichier_instruction").'"',
    "case instruction.om_final_instruction when 't' then 'Oui' else 'Non' end as \""._("om_final_instruction")."\"",
    'document_numerise.uid as "'._("document_numerise").'"',
    'instruction.archive_autorite_competente as "'._("archive_autorite_competente").'"',
    'autorite_competente.libelle as "'._("autorite_competente").'"',
    'instruction.duree_validite_parametrage as "'._("duree_validite_parametrage").'"',
    'instruction.duree_validite as "'._("duree_validite").'"',
    "case instruction.archive_incomplet_notifie when 't' then 'Oui' else 'Non' end as \""._("archive_incomplet_notifie")."\"",
    "case instruction.created_by_commune when 't' then 'Oui' else 'Non' end as \""._("created_by_commune")."\"",
    'to_char(instruction.date_depot ,\'DD/MM/YYYY\') as "'._("date_depot").'"',
    );
//
$champNonAffiche = array(
    'instruction.complement_om_html as "'._("complement_om_html").'"',
    'instruction.complement2_om_html as "'._("complement2_om_html").'"',
    'instruction.complement3_om_html as "'._("complement3_om_html").'"',
    'instruction.complement4_om_html as "'._("complement4_om_html").'"',
    'instruction.complement5_om_html as "'._("complement5_om_html").'"',
    'instruction.complement6_om_html as "'._("complement6_om_html").'"',
    'instruction.complement7_om_html as "'._("complement7_om_html").'"',
    'instruction.complement8_om_html as "'._("complement8_om_html").'"',
    'instruction.complement9_om_html as "'._("complement9_om_html").'"',
    'instruction.complement10_om_html as "'._("complement10_om_html").'"',
    'instruction.complement11_om_html as "'._("complement11_om_html").'"',
    'instruction.complement12_om_html as "'._("complement12_om_html").'"',
    'instruction.complement13_om_html as "'._("complement13_om_html").'"',
    'instruction.complement14_om_html as "'._("complement14_om_html").'"',
    'instruction.complement15_om_html as "'._("complement15_om_html").'"',
    'instruction.om_final_instruction_utilisateur as "'._("om_final_instruction_utilisateur").'"',
    );
//
$champRecherche = array(
    'instruction.instruction as "'._("instruction").'"',
    'instruction.destinataire as "'._("destinataire").'"',
    'evenement9.libelle as "'._("evenement").'"',
    'instruction.lettretype as "'._("lettretype").'"',
    'dossier.annee as "'._("dossier").'"',
    'action.libelle as "'._("action").'"',
    'instruction.delai as "'._("delai").'"',
    'etat8.libelle as "'._("etat").'"',
    'instruction.accord_tacite as "'._("accord_tacite").'"',
    'instruction.delai_notification as "'._("delai_notification").'"',
    'instruction.archive_delai as "'._("archive_delai").'"',
    'instruction.archive_accord_tacite as "'._("archive_accord_tacite").'"',
    'instruction.archive_etat as "'._("archive_etat").'"',
    'instruction.archive_avis as "'._("archive_avis").'"',
    'avis_decision.libelle as "'._("avis_decision").'"',
    'signataire_arrete.civilite as "'._("signataire_arrete").'"',
    'instruction.numero_arrete as "'._("numero_arrete").'"',
    'evenement2.libelle as "'._("archive_evenement_suivant_tacite").'"',
    'evenement3.libelle as "'._("archive_evenement_suivant_tacite_incompletude").'"',
    'etat1.libelle as "'._("archive_etat_pendant_incompletude").'"',
    'instruction.archive_delai_incompletude as "'._("archive_delai_incompletude").'"',
    'instruction.code_barres as "'._("code_barres").'"',
    'instruction.om_fichier_instruction as "'._("om_fichier_instruction").'"',
    'document_numerise.uid as "'._("document_numerise").'"',
    'instruction.archive_autorite_competente as "'._("archive_autorite_competente").'"',
    'autorite_competente.libelle as "'._("autorite_competente").'"',
    'instruction.duree_validite_parametrage as "'._("duree_validite_parametrage").'"',
    'instruction.duree_validite as "'._("duree_validite").'"',
    );
$tri="ORDER BY instruction.destinataire ASC NULLS LAST";
$edition="instruction";
/**
 * Gestion de la clause WHERE => $selection
 */
// Filtre listing standard
$selection = "";
// Liste des clés étrangères avec leurs éventuelles surcharges
$foreign_keys_extended = array(
    "action" => array("action", ),
    "etat" => array("etat", ),
    "evenement" => array("evenement", ),
    "autorite_competente" => array("autorite_competente", ),
    "avis_decision" => array("avis_decision", ),
    "document_numerise" => array("document_numerise", ),
    "dossier" => array("dossier", "dossier_instruction", "dossier_instruction_mes_encours", "dossier_instruction_tous_encours", "dossier_instruction_mes_clotures", "dossier_instruction_tous_clotures", ),
    "signataire_arrete" => array("signataire_arrete", ),
);
// Filtre listing sous formulaire - action
if (in_array($retourformulaire, $foreign_keys_extended["action"])) {
    $selection = " WHERE (instruction.action = '".$idx."') ";
}
// Filtre listing sous formulaire - etat
if (in_array($retourformulaire, $foreign_keys_extended["etat"])) {
    $selection = " WHERE (instruction.archive_etat_pendant_incompletude = '".$idx."' OR instruction.etat = '".$idx."') ";
}
// Filtre listing sous formulaire - evenement
if (in_array($retourformulaire, $foreign_keys_extended["evenement"])) {
    $selection = " WHERE (instruction.archive_evenement_suivant_tacite = '".$idx."' OR instruction.archive_evenement_suivant_tacite_incompletude = '".$idx."' OR instruction.evenement = '".$idx."') ";
}
// Filtre listing sous formulaire - autorite_competente
if (in_array($retourformulaire, $foreign_keys_extended["autorite_competente"])) {
    $selection = " WHERE (instruction.autorite_competente = '".$idx."') ";
}
// Filtre listing sous formulaire - avis_decision
if (in_array($retourformulaire, $foreign_keys_extended["avis_decision"])) {
    $selection = " WHERE (instruction.avis_decision = '".$idx."') ";
}
// Filtre listing sous formulaire - document_numerise
if (in_array($retourformulaire, $foreign_keys_extended["document_numerise"])) {
    $selection = " WHERE (instruction.document_numerise = '".$idx."') ";
}
// Filtre listing sous formulaire - dossier
if (in_array($retourformulaire, $foreign_keys_extended["dossier"])) {
    $selection = " WHERE (instruction.dossier = '".$idx."') ";
}
// Filtre listing sous formulaire - signataire_arrete
if (in_array($retourformulaire, $foreign_keys_extended["signataire_arrete"])) {
    $selection = " WHERE (instruction.signataire_arrete = '".$idx."') ";
}

/**
 * Gestion SOUSFORMULAIRE => $sousformulaire
 */
$sousformulaire = array(
    'demande',
);

?>