<?php
//$Id$ 
//gen openMairie le 01/03/2022 16:34

include "../gen/sql/pgsql/habilitation_tiers_consulte.inc.php";

$ent = __("parametrage")." -> ".__("gestion des consultations")." -> ".__("habilitation des tiers consultés");

// SELECT 
$champAffiche = array(
    'habilitation_tiers_consulte.habilitation_tiers_consulte as "'.__("habilitation_tiers_consulte").'"',
    'type_habilitation_tiers_consulte.libelle as "'.__("type_habilitation_tiers_consulte").'"',
    'to_char(habilitation_tiers_consulte.om_validite_debut ,\'DD/MM/YYYY\') as "'.__("om_validite_debut").'"',
    'to_char(habilitation_tiers_consulte.om_validite_fin ,\'DD/MM/YYYY\') as "'.__("om_validite_fin").'"',
    'tiers_consulte.libelle as "'.__("tiers_consulte").'"',
    'habilitation_tiers_consulte.division_territoriales as "'.__("division_territoriales").'"',
    );

$champNonAffiche = array(
    'habilitation_tiers_consulte.division_territoire_intervention as "'.__("division_territoire_intervention").'"',
    'habilitation_tiers_consulte.texte_agrement as "'.__("texte_agrement").'"',
    );
    
 // Recherche avancée
 $advsearch_fields= array(
    //
    'habilitation_tiers_consulte' => array(
        'colonne' => 'habilitation_tiers_consulte',
        'table' => 'habilitation_tiers_consulte',
        'libelle' => __("habilitation_tiers_consulte"),
        'type' => 'text',
        'taille' => '',
        'max' => ''
    )
    ,
    "type_habilitation_tiers_consulte" => array(
        'colonne' => "type_habilitation_tiers_consulte",
        'table' => 'type_habilitation_tiers_consulte',
        'libelle' => __("type_habilitation_tiers_consulte"),
        'type' => 'select',
        'taille' => '',
        'max' => '',
    )
    ,
    "om_validite_debut" => array(
        'colonne' => "om_validite_debut",
        'table' => 'habilitation_tiers_consulte',
        'libelle' => __("om_validite_debut"),
        'type' => 'date',
        'taille' => '',
        'max' => '',
    )
    ,
    'om_validite_fin' => array(
        'colonne' => 'om_validite_fin',
        'table' => 'habilitation_tiers_consulte',
        'libelle' => __("om_validite_fin"),
        'type' => 'date',
        'taille' => '',
        'max' => '',
    )
    ,
    'division_territoriales' => array(
        'colonne' => 'division_territoriales',
        'table' => 'habilitation_tiers_consulte',
        'libelle' => __("division_territoriales"),
        'type' => 'text',
        'taille' => '',
        'max' => '',
    )
    
);

// advsearch -> options
$options[] =  array(
    'type' => 'search',
    'display' => true,
    'advanced' => $advsearch_fields,
    'absolute_object' => 'habilitation_tiers_consulte',
    'export' => array("csv"),
);

if ($f->get_submitted_get_value('mode') === 'export_csv') {

    $table .= 
        "
        -- liste des specialite_tiers_consulte
        LEFT JOIN
            ".DB_PREFIXE."categorie_tiers_consulte
            ON 
            categorie_tiers_consulte.categorie_tiers_consulte = tiers_consulte.tiers_consulte
        LEFT JOIN
            (SELECT
                habilitation_tiers_consulte,
                ARRAY_TO_STRING(ARRAY_AGG(CONCAT_WS(' - ',specialite_tiers_consulte.specialite_tiers_consulte, specialite_tiers_consulte.code, specialite_tiers_consulte.libelle) ORDER BY specialite_tiers_consulte.specialite_tiers_consulte ASC), ', ') AS specialite_tiers_consulte
            FROM
                ".DB_PREFIXE."lien_habilitation_tiers_consulte_specialite_tiers_consulte
                INNER JOIN ".DB_PREFIXE."specialite_tiers_consulte
                    ON lien_habilitation_tiers_consulte_specialite_tiers_consulte.specialite_tiers_consulte = specialite_tiers_consulte.specialite_tiers_consulte
            GROUP BY
                habilitation_tiers_consulte) AS specialite_tiers_consulte_liste
            ON
                specialite_tiers_consulte_liste.habilitation_tiers_consulte = habilitation_tiers_consulte.habilitation_tiers_consulte
        -- liste des om_utilisateur
        LEFT JOIN
            (SELECT
                tiers_consulte,
                ARRAY_TO_STRING(ARRAY_AGG(om_utilisateur.login ORDER BY lien_om_utilisateur_tiers_consulte.om_utilisateur ASC), ', ') AS om_utilisateur
            FROM
                ".DB_PREFIXE."lien_om_utilisateur_tiers_consulte
                INNER JOIN ".DB_PREFIXE."om_utilisateur
                    ON lien_om_utilisateur_tiers_consulte.om_utilisateur = om_utilisateur.om_utilisateur
            GROUP BY
                tiers_consulte) AS om_utilisateur_liste
            ON
                om_utilisateur_liste.tiers_consulte = tiers_consulte.tiers_consulte
        ";

    $champAffiche_habilitation_tiers_consulte = array(
        'habilitation_tiers_consulte.habilitation_tiers_consulte as "'.__("habilitation_tiers_consulte").'"',
        'type_habilitation_tiers_consulte.libelle as "'.__("type_habilitation_tiers_consulte").'"',
        'specialite_tiers_consulte_liste.specialite_tiers_consulte as "'.__("specialite_tiers_consulte").'"',
        'to_char(habilitation_tiers_consulte.om_validite_debut ,\'DD/MM/YYYY\') as "'.__("om_validite_debut").'"',
        'to_char(habilitation_tiers_consulte.om_validite_fin ,\'DD/MM/YYYY\') as "'.__("om_validite_fin").'"',
        'tiers_consulte.tiers_consulte as "'.__("tiers_consulte").'"',
        'categorie_tiers_consulte.libelle as "'.__("categorie_tiers_consulte").'"',
        'tiers_consulte.abrege as "'.__("abrege").'"',
        'tiers_consulte.libelle as "'.__("libelle").'"',
    );
    
    $champAffiche = array_merge($champAffiche, $champAffiche_habilitation_tiers_consulte);
    $champAffiche[] = 'om_utilisateur_liste.om_utilisateur as '.__("om_utilisateur");
}