<?php
// Filtre des requêtes de group pour les reqmo
include ('../sql/pgsql/filter_group_reqmo.inc.php');

//Libellé de la requête
$reqmo['libelle']=_("Liste simplifiee des dossiers");

//Choix des champs à afficher
$reqmo['reference_dossier']='checked';
$reqmo['coordonnees_petitionnaire_principal']='checked';
$reqmo['localisation']='checked';
$reqmo['shon']='checked';
$reqmo['libelle_destination']='checked';
$reqmo['date_depot']='checked';

//Choix des critères de tri
$reqmo['dossier_autorisation_type']= "select dossier_autorisation_type, dossier_autorisation_type.code from ".DB_PREFIXE."dossier_autorisation_type inner join ".DB_PREFIXE."groupe ON dossier_autorisation_type.groupe = groupe.groupe ".$selection." AND LOWER(dossier_autorisation_type.affichage_form) = 'ads' order by code";
$reqmo['date_depot_debut'] = "../../....";
$reqmo['date_depot_fin'] = "../../....";
//Type attendu pour les données
$reqmo['type']['dossier_autorisation_type'] = 'integer';
$reqmo['type']['date_depot_debut'] = 'date';
$reqmo['type']['date_depot_fin'] = 'date';
$reqmo['type']['tri'] = 'string';
//
$reqmo['tri']= array('dossier.date_depot', 'dossier.annee', 'dossier.version');

//Traduction des champs
_("reference_dossier");
_("date_depot");
_("coordonnees_petitionnaire_principal");
_("localisation");
_("shon");
_("libelle_destination");

//Requête à effectuer
$reqmo['sql']="SELECT [dossier.dossier_libelle as reference_dossier], 
[to_char(dossier.date_depot ,'DD/MM/YYYY') as date_depot],
[CONCAT(
CASE 
    WHEN demandeur.qualite='particulier' THEN 
        TRIM(CONCAT(demandeur.particulier_nom, ' ', demandeur.particulier_prenom)) 
    ELSE 
        TRIM(CONCAT(demandeur.personne_morale_raison_sociale, ' ', demandeur.personne_morale_denomination)) 
END, ' ',demandeur.numero, ' ', demandeur.voie, ' ',
demandeur.complement, ' ', demandeur.lieu_dit, ' ', 
demandeur.code_postal, ' ', demandeur.localite, ' ', CASE 
    WHEN demandeur.bp IS NULL THEN 
        '' 
    ELSE 
        CONCAT('BP ', demandeur.bp) 
END, ' ', CASE 
    WHEN demandeur.cedex IS NULL THEN 
        '' 
    ELSE 
        CONCAT('CEDEX ', demandeur.cedex) 
END, '
', demandeur.pays) as coordonnees_petitionnaire_principal],
[CONCAT(
dossier.terrain_adresse_voie_numero, ' ', dossier.terrain_adresse_voie, ' ', 
dossier.terrain_adresse_lieu_dit, ' ', 
dossier.terrain_adresse_code_postal, ' ', dossier.terrain_adresse_localite, ' ', CASE 
    WHEN dossier.terrain_adresse_bp IS NULL THEN 
        ''
    ELSE 
        CONCAT('BP ', dossier.terrain_adresse_bp)
END, ' ',CASE 
    WHEN dossier.terrain_adresse_cedex IS NULL THEN 
        ''
    ELSE 
        CONCAT('CEDEX ', dossier.terrain_adresse_cedex)
END, ' ',
arrondissement.libelle) as localisation],
[CASE WHEN su2_avt_shon1 IS NOT NULL
    OR su2_avt_shon2 IS NOT NULL
    OR su2_avt_shon3 IS NOT NULL
    OR su2_avt_shon4 IS NOT NULL
    OR su2_avt_shon5 IS NOT NULL
    OR su2_avt_shon6 IS NOT NULL
    OR su2_avt_shon7 IS NOT NULL
    OR su2_avt_shon8 IS NOT NULL
    OR su2_avt_shon9 IS NOT NULL
    OR su2_avt_shon10 IS NOT NULL
    OR su2_avt_shon11 IS NOT NULL
    OR su2_avt_shon12 IS NOT NULL
    OR su2_avt_shon13 IS NOT NULL
    OR su2_avt_shon14 IS NOT NULL
    OR su2_avt_shon15 IS NOT NULL
    OR su2_avt_shon16 IS NOT NULL
    OR su2_avt_shon17 IS NOT NULL
    OR su2_avt_shon18 IS NOT NULL
    OR su2_avt_shon19 IS NOT NULL
    OR su2_avt_shon20 IS NOT NULL
    OR su2_cstr_shon1 IS NOT NULL
    OR su2_cstr_shon2 IS NOT NULL
    OR su2_cstr_shon3 IS NOT NULL
    OR su2_cstr_shon4 IS NOT NULL
    OR su2_cstr_shon5 IS NOT NULL
    OR su2_cstr_shon6 IS NOT NULL
    OR su2_cstr_shon7 IS NOT NULL
    OR su2_cstr_shon8 IS NOT NULL
    OR su2_cstr_shon9 IS NOT NULL
    OR su2_cstr_shon10 IS NOT NULL
    OR su2_cstr_shon11 IS NOT NULL
    OR su2_cstr_shon12 IS NOT NULL
    OR su2_cstr_shon13 IS NOT NULL
    OR su2_cstr_shon14 IS NOT NULL
    OR su2_cstr_shon15 IS NOT NULL
    OR su2_cstr_shon16 IS NOT NULL
    OR su2_cstr_shon17 IS NOT NULL
    OR su2_cstr_shon18 IS NOT NULL
    OR su2_cstr_shon19 IS NOT NULL
    OR su2_cstr_shon20 IS NOT NULL
    OR su2_chge_shon1 IS NOT NULL
    OR su2_chge_shon2 IS NOT NULL
    OR su2_chge_shon3 IS NOT NULL
    OR su2_chge_shon4 IS NOT NULL
    OR su2_chge_shon5 IS NOT NULL
    OR su2_chge_shon6 IS NOT NULL
    OR su2_chge_shon7 IS NOT NULL
    OR su2_chge_shon8 IS NOT NULL
    OR su2_chge_shon9 IS NOT NULL
    OR su2_chge_shon10 IS NOT NULL
    OR su2_chge_shon11 IS NOT NULL
    OR su2_chge_shon12 IS NOT NULL
    OR su2_chge_shon13 IS NOT NULL
    OR su2_chge_shon14 IS NOT NULL
    OR su2_chge_shon15 IS NOT NULL
    OR su2_chge_shon16 IS NOT NULL
    OR su2_chge_shon17 IS NOT NULL
    OR su2_chge_shon18 IS NOT NULL
    OR su2_chge_shon19 IS NOT NULL
    OR su2_chge_shon20 IS NOT NULL
    OR su2_demo_shon1 IS NOT NULL
    OR su2_demo_shon2 IS NOT NULL
    OR su2_demo_shon3 IS NOT NULL
    OR su2_demo_shon4 IS NOT NULL
    OR su2_demo_shon5 IS NOT NULL
    OR su2_demo_shon6 IS NOT NULL
    OR su2_demo_shon7 IS NOT NULL
    OR su2_demo_shon8 IS NOT NULL
    OR su2_demo_shon9 IS NOT NULL
    OR su2_demo_shon10 IS NOT NULL
    OR su2_demo_shon11 IS NOT NULL
    OR su2_demo_shon12 IS NOT NULL
    OR su2_demo_shon13 IS NOT NULL
    OR su2_demo_shon14 IS NOT NULL
    OR su2_demo_shon15 IS NOT NULL
    OR su2_demo_shon16 IS NOT NULL
    OR su2_demo_shon17 IS NOT NULL
    OR su2_demo_shon18 IS NOT NULL
    OR su2_demo_shon19 IS NOT NULL
    OR su2_demo_shon20 IS NOT NULL
    OR su2_sup_shon1 IS NOT NULL
    OR su2_sup_shon2 IS NOT NULL
    OR su2_sup_shon3 IS NOT NULL
    OR su2_sup_shon4 IS NOT NULL
    OR su2_sup_shon5 IS NOT NULL
    OR su2_sup_shon6 IS NOT NULL
    OR su2_sup_shon7 IS NOT NULL
    OR su2_sup_shon8 IS NOT NULL
    OR su2_sup_shon9 IS NOT NULL
    OR su2_sup_shon10 IS NOT NULL
    OR su2_sup_shon11 IS NOT NULL
    OR su2_sup_shon12 IS NOT NULL
    OR su2_sup_shon13 IS NOT NULL
    OR su2_sup_shon14 IS NOT NULL
    OR su2_sup_shon15 IS NOT NULL
    OR su2_sup_shon16 IS NOT NULL
    OR su2_sup_shon17 IS NOT NULL
    OR su2_sup_shon18 IS NOT NULL
    OR su2_sup_shon19 IS NOT NULL
    OR su2_sup_shon20 IS NOT NULL
    THEN donnees_techniques.su2_tot_shon2
    ELSE donnees_techniques.su_tot_shon2
END as shon],
[CASE WHEN su2_avt_shon1 IS NOT NULL
    OR su2_avt_shon2 IS NOT NULL
    OR su2_avt_shon3 IS NOT NULL
    OR su2_avt_shon4 IS NOT NULL
    OR su2_avt_shon5 IS NOT NULL
    OR su2_avt_shon6 IS NOT NULL
    OR su2_avt_shon7 IS NOT NULL
    OR su2_avt_shon8 IS NOT NULL
    OR su2_avt_shon9 IS NOT NULL
    OR su2_avt_shon10 IS NOT NULL
    OR su2_avt_shon11 IS NOT NULL
    OR su2_avt_shon12 IS NOT NULL
    OR su2_avt_shon13 IS NOT NULL
    OR su2_avt_shon14 IS NOT NULL
    OR su2_avt_shon15 IS NOT NULL
    OR su2_avt_shon16 IS NOT NULL
    OR su2_avt_shon17 IS NOT NULL
    OR su2_avt_shon18 IS NOT NULL
    OR su2_avt_shon19 IS NOT NULL
    OR su2_avt_shon20 IS NOT NULL
    OR su2_cstr_shon1 IS NOT NULL
    OR su2_cstr_shon2 IS NOT NULL
    OR su2_cstr_shon3 IS NOT NULL
    OR su2_cstr_shon4 IS NOT NULL
    OR su2_cstr_shon5 IS NOT NULL
    OR su2_cstr_shon6 IS NOT NULL
    OR su2_cstr_shon7 IS NOT NULL
    OR su2_cstr_shon8 IS NOT NULL
    OR su2_cstr_shon9 IS NOT NULL
    OR su2_cstr_shon10 IS NOT NULL
    OR su2_cstr_shon11 IS NOT NULL
    OR su2_cstr_shon12 IS NOT NULL
    OR su2_cstr_shon13 IS NOT NULL
    OR su2_cstr_shon14 IS NOT NULL
    OR su2_cstr_shon15 IS NOT NULL
    OR su2_cstr_shon16 IS NOT NULL
    OR su2_cstr_shon17 IS NOT NULL
    OR su2_cstr_shon18 IS NOT NULL
    OR su2_cstr_shon19 IS NOT NULL
    OR su2_cstr_shon20 IS NOT NULL
    OR su2_chge_shon1 IS NOT NULL
    OR su2_chge_shon2 IS NOT NULL
    OR su2_chge_shon3 IS NOT NULL
    OR su2_chge_shon4 IS NOT NULL
    OR su2_chge_shon5 IS NOT NULL
    OR su2_chge_shon6 IS NOT NULL
    OR su2_chge_shon7 IS NOT NULL
    OR su2_chge_shon8 IS NOT NULL
    OR su2_chge_shon9 IS NOT NULL
    OR su2_chge_shon10 IS NOT NULL
    OR su2_chge_shon11 IS NOT NULL
    OR su2_chge_shon12 IS NOT NULL
    OR su2_chge_shon13 IS NOT NULL
    OR su2_chge_shon14 IS NOT NULL
    OR su2_chge_shon15 IS NOT NULL
    OR su2_chge_shon16 IS NOT NULL
    OR su2_chge_shon17 IS NOT NULL
    OR su2_chge_shon18 IS NOT NULL
    OR su2_chge_shon19 IS NOT NULL
    OR su2_chge_shon20 IS NOT NULL
    OR su2_demo_shon1 IS NOT NULL
    OR su2_demo_shon2 IS NOT NULL
    OR su2_demo_shon3 IS NOT NULL
    OR su2_demo_shon4 IS NOT NULL
    OR su2_demo_shon5 IS NOT NULL
    OR su2_demo_shon6 IS NOT NULL
    OR su2_demo_shon7 IS NOT NULL
    OR su2_demo_shon8 IS NOT NULL
    OR su2_demo_shon9 IS NOT NULL
    OR su2_demo_shon10 IS NOT NULL
    OR su2_demo_shon11 IS NOT NULL
    OR su2_demo_shon12 IS NOT NULL
    OR su2_demo_shon13 IS NOT NULL
    OR su2_demo_shon14 IS NOT NULL
    OR su2_demo_shon15 IS NOT NULL
    OR su2_demo_shon16 IS NOT NULL
    OR su2_demo_shon17 IS NOT NULL
    OR su2_demo_shon18 IS NOT NULL
    OR su2_demo_shon19 IS NOT NULL
    OR su2_demo_shon20 IS NOT NULL
    OR su2_sup_shon1 IS NOT NULL
    OR su2_sup_shon2 IS NOT NULL
    OR su2_sup_shon3 IS NOT NULL
    OR su2_sup_shon4 IS NOT NULL
    OR su2_sup_shon5 IS NOT NULL
    OR su2_sup_shon6 IS NOT NULL
    OR su2_sup_shon7 IS NOT NULL
    OR su2_sup_shon8 IS NOT NULL
    OR su2_sup_shon9 IS NOT NULL
    OR su2_sup_shon10 IS NOT NULL
    OR su2_sup_shon11 IS NOT NULL
    OR su2_sup_shon12 IS NOT NULL
    OR su2_sup_shon13 IS NOT NULL
    OR su2_sup_shon14 IS NOT NULL
    OR su2_sup_shon15 IS NOT NULL
    OR su2_sup_shon16 IS NOT NULL
    OR su2_sup_shon17 IS NOT NULL
    OR su2_sup_shon18 IS NOT NULL
    OR su2_sup_shon19 IS NOT NULL
    OR su2_sup_shon20 IS NOT NULL
    THEN
        REGEXP_REPLACE(CONCAT(
            CASE WHEN donnees_techniques.su2_cstr_shon1 IS NULL
                THEN ''
                ELSE 'Exploitation agricole / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon2 IS NULL
                THEN ''
                ELSE 'Exploitation forestière / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon3 IS NULL
                THEN ''
                ELSE 'Logement / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon4 IS NULL
                THEN ''
                ELSE 'Hébergement / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon5 IS NULL
                THEN ''
                ELSE 'Artisanat et commerce de détail / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon6 IS NULL
                THEN ''
                ELSE 'Restauration / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon7 IS NULL
                THEN ''
                ELSE 'Commerce de gros / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon8 IS NULL
                THEN ''
                ELSE 'Activités de services où s''effectue l''accueil d''une clientèle / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon9 IS NULL
                THEN ''
                ELSE 'Hébergement hôtelier et touristique / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon10 IS NULL
                THEN ''
                ELSE 'Cinéma / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon11 IS NULL
                THEN ''
                ELSE 'Locaux et bureaux accueillant du public des administrations publiques et assimilés / ' 
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon12 IS NULL
                THEN ''
                ELSE 'Locaux techniques et industriels des administrations publiques et assimilés / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon13 IS NULL
                THEN ''
                ELSE 'Établissements d''enseignement, de santé et d''action sociale / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon14 IS NULL
                THEN ''
                ELSE 'Salles d''art et de spectacles / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon15 IS NULL
                THEN ''
                ELSE 'Équipements sportifs / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon16 IS NULL
                THEN ''
                ELSE 'Autres équipements recevant du public / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon17 IS NULL
                THEN ''
                ELSE 'Industrie / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon18 IS NULL
                THEN ''
                ELSE 'Entrepôt / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon19 IS NULL
                THEN ''
                ELSE 'Bureau / '
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon20 IS NULL
                THEN ''
                ELSE 'Centre de congrès et d''exposition'
            END
        ), ' / $', '')
    ELSE
        REGEXP_REPLACE(CONCAT(
            CASE
                WHEN donnees_techniques.su_cstr_shon1 IS NULL
                THEN ''
                ELSE 'Habitation / '
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon2 IS NULL
                THEN ''
                ELSE 'Hébergement hôtelier / '
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon3 IS NULL
                THEN ''
                ELSE 'Bureaux / '
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon4 IS NULL
                THEN ''
                ELSE 'Commerce / '
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon5 IS NULL
                THEN ''
                ELSE 'Artisanat / '
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon6 IS NULL
                THEN ''
                ELSE 'Industrie / '
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon7 IS NULL
                THEN ''
                ELSE 'Exploitation agricole ou forestière / '
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon8 IS NULL
                THEN ''
                ELSE 'Entrepôt / '
            END, 
            CASE
                WHEN donnees_techniques.su_cstr_shon9 IS NULL
                THEN ''
                ELSE 'Service public ou d''intérêt collectif'
            END
        ), ' / $', '')
END as libelle_destination]
FROM ".DB_PREFIXE."dossier
LEFT JOIN ".DB_PREFIXE."lien_dossier_demandeur
ON lien_dossier_demandeur.dossier = dossier.dossier AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
LEFT JOIN ".DB_PREFIXE."demandeur
ON demandeur.demandeur = lien_dossier_demandeur.demandeur
LEFT JOIN ".DB_PREFIXE."arrondissement
ON arrondissement.code_postal = dossier.terrain_adresse_code_postal
LEFT JOIN ".DB_PREFIXE."dossier_instruction_type
ON dossier_instruction_type.dossier_instruction_type = dossier.dossier_instruction_type
LEFT JOIN ".DB_PREFIXE."dossier_autorisation_type_detaille
ON dossier_autorisation_type_detaille.dossier_autorisation_type_detaille = dossier_instruction_type.dossier_autorisation_type_detaille
LEFT JOIN ".DB_PREFIXE."dossier_autorisation_type
ON dossier_autorisation_type.dossier_autorisation_type = dossier_autorisation_type_detaille.dossier_autorisation_type
LEFT JOIN ".DB_PREFIXE."donnees_techniques
ON donnees_techniques.dossier_instruction = dossier.dossier
WHERE
    dossier.om_collectivite IN (<idx_collectivite>)
    AND dossier_autorisation_type.dossier_autorisation_type = '[dossier_autorisation_type]' AND 
    dossier.date_depot >=  '[date_depot_debut]' AND
    dossier.date_depot <=  '[date_depot_fin]'
ORDER BY [tri], dossier.dossier";
?>