sql/pgsql/dossier_detaillee_detail.reqmo_pilot.inc.php

<?php
//Libellé de la requête
$reqmo['libelle']=_("Liste detaillee des dossiers + detail");

//Choix des champs à afficher
$reqmo['reference_dossier']='checked';
$reqmo['date_depot']='checked';
$reqmo['date_ouverture_chantier']='checked';
$reqmo['date_demande']='checked';
$reqmo['date_achevement']='checked';
$reqmo['date_prevue_recevabilite']='checked';
$reqmo['destination_surfaces']='checked';
$reqmo['coordonnees_petitionnaire_principal']='checked';
$reqmo['adresse_terrain']='checked';
$reqmo['reference_cadastrale']='checked';
$reqmo['date_decision']='checked';
$reqmo['shon']='checked';
$reqmo['architecte']='checked';
$reqmo['affectation_surface']='checked';
$reqmo['nature_travaux']='checked';
$reqmo['nature_financement']='checked';
$reqmo['nombre_logements']='checked';
$reqmo['autorite_competente']='checked';
$reqmo['decision']='checked';

//Choix des critères de tri
$reqmo['date_decision_debut'] = "../../....";
$reqmo['date_decision_fin'] = "../../....";
//Type attendu pour les données
$reqmo['type']['date_decision_debut'] = 'date';
$reqmo['type']['date_decision_fin'] = 'date';
$reqmo['type']['tri'] = 'string';
//
$reqmo['tri']= array('dossier.date_decision', 'dossier.date_depot');

//Traduction des champs
_("reference_dossier");
_("date_depot");
_("date_ouverture_chantier");
_("date_demande");
_("date_achevement");
_("date_prevue_recevabilite");
_("destination_surfaces");
_("coordonnees_petitionnaire_principal");
_("adresse_terrain");
_("reference_cadastrale");
_("date_decision");
_("shon");
_("architecte");
_("affectation_surface");
_("nature_travaux");
_("nature_financement");
_("nombre_logements");
_("autorite_competente");
_("decision");

//Requête à effectuer
$reqmo['sql'] = "SELECT 
[dossier.dossier_libelle as reference_dossier],
[to_char(dossier.date_depot ,'DD/MM/YYYY') as date_depot],
[to_char(doc.date_depot ,'DD/MM/YYYY') as date_ouverture_chantier],
[to_char(dossier.date_demande ,'DD/MM/YYYY') as date_demande],
[to_char(daact.date_achevement ,'DD/MM/YYYY') as date_achevement],
[to_char(daact.date_conformite ,'DD/MM/YYYY') as date_prevue_recevabilite],
[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 CONCAT ('Exploitation agricole - ', donnees_techniques.su2_cstr_shon1, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon2 IS NULL
                THEN ''
                ELSE CONCAT ('Exploitation forestière - ', donnees_techniques.su2_cstr_shon2, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon3 IS NULL
                THEN ''
                ELSE CONCAT ('Logement - ', donnees_techniques.su2_cstr_shon3, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon4 IS NULL
                THEN ''
                ELSE CONCAT ('Hébergement - ', donnees_techniques.su2_cstr_shon4, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon5 IS NULL
                THEN ''
                ELSE CONCAT ('Artisanat et commerce de détail - ', donnees_techniques.su2_cstr_shon5, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon6 IS NULL
                THEN ''
                ELSE CONCAT ('Restauration - ', donnees_techniques.su2_cstr_shon6, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon7 IS NULL
                THEN ''
                ELSE CONCAT ('Commerce de gros - ', donnees_techniques.su2_cstr_shon7, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon8 IS NULL
                THEN ''
                ELSE CONCAT ('Activités de services où s''effectue l''accueil d''une clientèle - ', donnees_techniques.su2_cstr_shon8, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon9 IS NULL
                THEN ''
                ELSE CONCAT ('Hébergement hôtelier et touristique - ', donnees_techniques.su2_cstr_shon9, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon10 IS NULL
                THEN ''
                ELSE CONCAT ('Cinéma - ', donnees_techniques.su2_cstr_shon10, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon11 IS NULL
                THEN ''
                ELSE CONCAT ('Locaux et bureaux accueillant du public des administrations publiques et assimilés - ', donnees_techniques.su2_cstr_shon11, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon12 IS NULL
                THEN ''
                ELSE CONCAT ('Locaux techniques et industriels des administrations publiques et assimilés - ', donnees_techniques.su2_cstr_shon12, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon13 IS NULL
                THEN ''
                ELSE CONCAT ('Établissements d''enseignement, de santé et d''action sociale - ', donnees_techniques.su2_cstr_shon13, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon14 IS NULL
                THEN ''
                ELSE CONCAT ('Salles d''art et de spectacles - ', donnees_techniques.su2_cstr_shon14, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon15 IS NULL
                THEN ''
                ELSE CONCAT ('Équipements sportifs - ', donnees_techniques.su2_cstr_shon15, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon16 IS NULL
                THEN ''
                ELSE CONCAT ('Autres équipements recevant du public - ', donnees_techniques.su2_cstr_shon16, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon17 IS NULL
                THEN ''
                ELSE CONCAT ('Industrie - ', donnees_techniques.su2_cstr_shon17, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon18 IS NULL
                THEN ''
                ELSE CONCAT ('Entrepôt - ', donnees_techniques.su2_cstr_shon18, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon19 IS NULL
                THEN ''
                ELSE CONCAT ('Bureau - ', donnees_techniques.su2_cstr_shon19, ' m² / ')
            END,
            CASE WHEN donnees_techniques.su2_cstr_shon20 IS NULL
                THEN ''
                ELSE CONCAT ('Centre de congrès et d''exposition - ', donnees_techniques.su2_cstr_shon20, ' m²')
            END
        ), ' / $', '')
    ELSE
        REGEXP_REPLACE(CONCAT(
            CASE
                WHEN donnees_techniques.su_cstr_shon1 IS NULL
                THEN ''
                ELSE CONCAT('Habitation - ', donnees_techniques.su_cstr_shon1, ' m² / ')
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon2 IS NULL
                THEN ''
                ELSE CONCAT('Hébergement hôtelier - ', donnees_techniques.su_cstr_shon2, ' m² / ')
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon3 IS NULL
                THEN ''
                ELSE CONCAT('Bureaux - ', donnees_techniques.su_cstr_shon3, ' m² / ')
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon4 IS NULL
                THEN ''
                ELSE CONCAT('Commerce - ', donnees_techniques.su_cstr_shon4, ' m² / ')
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon5 IS NULL
                THEN ''
                ELSE CONCAT('Artisanat - ', donnees_techniques.su_cstr_shon5, ' m² / ')
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon6 IS NULL
                THEN ''
                ELSE CONCAT('Industrie - ', donnees_techniques.su_cstr_shon6, ' m² / ')
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon7 IS NULL
                THEN ''
                ELSE CONCAT('Exploitation agricole ou forestière - ', donnees_techniques.su_cstr_shon7, ' m² / ')
            END,
            CASE
                WHEN donnees_techniques.su_cstr_shon8 IS NULL
                THEN ''
                ELSE CONCAT('Entrepôt - ', donnees_techniques.su_cstr_shon8, ' m² / ')
            END, 
            CASE
                WHEN donnees_techniques.su_cstr_shon9 IS NULL
                THEN ''
                ELSE CONCAT('Service public ou d''intérêt collectif - ', donnees_techniques.su_cstr_shon9, ' m²')
            END
        ), ' / $', '')
END as destination_surfaces],
[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 adresse_terrain],
[dossier.terrain_references_cadastrales as reference_cadastrale],
[to_char(dossier.date_decision ,'DD/MM/YYYY') as date_decision],
[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],
[CONCAT(architecte.prenom, ' ', architecte.nom) as architecte],
[REGEXP_REPLACE(CONCAT(
CASE 
    WHEN donnees_techniques.co_sp_transport IS TRUE THEN
        CONCAT('Transport / ') 
    ELSE 
        ''
END,
CASE 
    WHEN donnees_techniques.co_sp_enseign IS TRUE THEN
        CONCAT('Enseignement et recherche / ') 
    ELSE 
        ''
END,
CASE 
    WHEN donnees_techniques.co_sp_act_soc IS TRUE THEN
        CONCAT('Action sociale / ') 
    ELSE 
        ''
END,
CASE 
    WHEN donnees_techniques.co_sp_transport IS TRUE THEN
        CONCAT('Ouvrage spécial / ') 
    ELSE 
        ''
END,
CASE 
    WHEN donnees_techniques.co_sp_sante IS TRUE THEN
        CONCAT('Santé / ') 
    ELSE 
        ''
END,
CASE 
    WHEN donnees_techniques.co_sp_culture IS TRUE THEN
        CONCAT('Culture et loisir / ') 
    ELSE 
        ''
END), ' / $', '') as affectation_surface],
[CASE 
    WHEN donnees_techniques.co_cstr_nouv IS TRUE THEN
        'Nouvelle construction'
    WHEN donnees_techniques.co_cstr_exist IS TRUE THEN
        'Travaux ou changement de destination sur une construction existante'
    WHEN donnees_techniques.co_cloture IS TRUE THEN
        'Travaux clôturés'
END as nature_travaux],
[REGEXP_REPLACE(CONCAT(
CASE 
    WHEN donnees_techniques.co_fin_lls_nb IS NULL THEN
        '' 
    ELSE 
        CONCAT('Logement Locatif Social / ')
END,
CASE 
    WHEN donnees_techniques.co_fin_aa_nb IS NULL THEN
        ''
    ELSE 
        CONCAT('Accession Sociale (hors prêt à taux zéro) / ') 
END,
CASE 
    WHEN donnees_techniques.co_fin_ptz_nb IS NULL THEN
        ''
    ELSE 
        CONCAT('Prêt à taux zéro / ') 
END,
CASE 
    WHEN donnees_techniques.co_fin_autr_nb IS NULL THEN
        '' 
    ELSE 
        CONCAT('Autres financements')
END), ' / $', '') as nature_financement],
[donnees_techniques.co_tot_log_nb as nombre_logements],
[autorite_competente.libelle as autorite_competente],
[avis_decision.libelle as decision]
FROM ".DB_PREFIXE."dossier
LEFT JOIN ".DB_PREFIXE."dossier_instruction_type
ON dossier_instruction_type.dossier_instruction_type = dossier.dossier_instruction_type
LEFT JOIN ".DB_PREFIXE."dossier_autorisation
ON dossier_autorisation.dossier_autorisation = dossier.dossier_autorisation
LEFT JOIN ".DB_PREFIXE."dossier as doc
ON doc.dossier_autorisation = dossier_autorisation.dossier_autorisation AND doc.dossier_instruction_type = (SELECT dossier_instruction_type FROM ".DB_PREFIXE."dossier_instruction_type as dit WHERE dit.code = 'DOC' AND dit.dossier_autorisation_type_detaille = dossier_instruction_type.dossier_autorisation_type_detaille) 
LEFT JOIN ".DB_PREFIXE."dossier as daact
ON daact.dossier_autorisation = dossier_autorisation.dossier_autorisation AND daact.dossier_instruction_type = (SELECT dossier_instruction_type FROM ".DB_PREFIXE."dossier_instruction_type as dit WHERE dit.code = 'DAACT' AND dit.dossier_autorisation_type_detaille = dossier_instruction_type.dossier_autorisation_type_detaille) 
LEFT JOIN ".DB_PREFIXE."donnees_techniques
ON donnees_techniques.dossier_instruction = dossier.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."architecte
ON architecte.architecte = donnees_techniques.architecte
LEFT JOIN ".DB_PREFIXE."autorite_competente
ON autorite_competente.autorite_competente = dossier.autorite_competente
LEFT JOIN ".DB_PREFIXE."avis_decision
ON avis_decision.avis_decision = dossier.avis_decision
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
WHERE dossier_autorisation_type.code = 'CU' AND 
    dossier.date_decision >=  '[date_decision_debut]' AND
    dossier.date_decision <=  '[date_decision_fin]'
ORDER BY [tri], dossier.dossier";
?>
1	mbroquet	3730	<?php
2			//Libellé de la requête
3			$reqmo['libelle']=_("Liste detaillee des dossiers + detail");
4
5			//Choix des champs à afficher
6			$reqmo['reference_dossier']='checked';
7			$reqmo['date_depot']='checked';
8			$reqmo['date_ouverture_chantier']='checked';
9			$reqmo['date_demande']='checked';
10			$reqmo['date_achevement']='checked';
11			$reqmo['date_prevue_recevabilite']='checked';
12			$reqmo['destination_surfaces']='checked';
13			$reqmo['coordonnees_petitionnaire_principal']='checked';
14			$reqmo['adresse_terrain']='checked';
15			$reqmo['reference_cadastrale']='checked';
16			$reqmo['date_decision']='checked';
17			$reqmo['shon']='checked';
18			$reqmo['architecte']='checked';
19			$reqmo['affectation_surface']='checked';
20			$reqmo['nature_travaux']='checked';
21			$reqmo['nature_financement']='checked';
22			$reqmo['nombre_logements']='checked';
23			$reqmo['autorite_competente']='checked';
24			$reqmo['decision']='checked';
25
26			//Choix des critères de tri
27			$reqmo['date_decision_debut'] = "../../....";
28			$reqmo['date_decision_fin'] = "../../....";
29			//Type attendu pour les données
30			$reqmo['type']['date_decision_debut'] = 'date';
31			$reqmo['type']['date_decision_fin'] = 'date';
32			$reqmo['type']['tri'] = 'string';
33			//
34			$reqmo['tri']= array('dossier.date_decision', 'dossier.date_depot');
35
36			//Traduction des champs
37			_("reference_dossier");
38			_("date_depot");
39			_("date_ouverture_chantier");
40			_("date_demande");
41			_("date_achevement");
42			_("date_prevue_recevabilite");
43			_("destination_surfaces");
44			_("coordonnees_petitionnaire_principal");
45			_("adresse_terrain");
46			_("reference_cadastrale");
47			_("date_decision");
48			_("shon");
49			_("architecte");
50			_("affectation_surface");
51			_("nature_travaux");
52			_("nature_financement");
53			_("nombre_logements");
54			_("autorite_competente");
55			_("decision");
56
57			//Requête à effectuer
58			$reqmo['sql'] = "SELECT
59			[dossier.dossier_libelle as reference_dossier],
60			[to_char(dossier.date_depot ,'DD/MM/YYYY') as date_depot],
61			[to_char(doc.date_depot ,'DD/MM/YYYY') as date_ouverture_chantier],
62			[to_char(dossier.date_demande ,'DD/MM/YYYY') as date_demande],
63			[to_char(daact.date_achevement ,'DD/MM/YYYY') as date_achevement],
64			[to_char(daact.date_conformite ,'DD/MM/YYYY') as date_prevue_recevabilite],
65	softime	4678	[CASE WHEN su2_avt_shon1 IS NOT NULL
66			OR su2_avt_shon2 IS NOT NULL
67			OR su2_avt_shon3 IS NOT NULL
68			OR su2_avt_shon4 IS NOT NULL
69			OR su2_avt_shon5 IS NOT NULL
70			OR su2_avt_shon6 IS NOT NULL
71			OR su2_avt_shon7 IS NOT NULL
72			OR su2_avt_shon8 IS NOT NULL
73			OR su2_avt_shon9 IS NOT NULL
74			OR su2_avt_shon10 IS NOT NULL
75			OR su2_avt_shon11 IS NOT NULL
76			OR su2_avt_shon12 IS NOT NULL
77			OR su2_avt_shon13 IS NOT NULL
78			OR su2_avt_shon14 IS NOT NULL
79			OR su2_avt_shon15 IS NOT NULL
80			OR su2_avt_shon16 IS NOT NULL
81			OR su2_avt_shon17 IS NOT NULL
82			OR su2_avt_shon18 IS NOT NULL
83			OR su2_avt_shon19 IS NOT NULL
84			OR su2_avt_shon20 IS NOT NULL
85			OR su2_cstr_shon1 IS NOT NULL
86			OR su2_cstr_shon2 IS NOT NULL
87			OR su2_cstr_shon3 IS NOT NULL
88			OR su2_cstr_shon4 IS NOT NULL
89			OR su2_cstr_shon5 IS NOT NULL
90			OR su2_cstr_shon6 IS NOT NULL
91			OR su2_cstr_shon7 IS NOT NULL
92			OR su2_cstr_shon8 IS NOT NULL
93			OR su2_cstr_shon9 IS NOT NULL
94			OR su2_cstr_shon10 IS NOT NULL
95			OR su2_cstr_shon11 IS NOT NULL
96			OR su2_cstr_shon12 IS NOT NULL
97			OR su2_cstr_shon13 IS NOT NULL
98			OR su2_cstr_shon14 IS NOT NULL
99			OR su2_cstr_shon15 IS NOT NULL
100			OR su2_cstr_shon16 IS NOT NULL
101			OR su2_cstr_shon17 IS NOT NULL
102			OR su2_cstr_shon18 IS NOT NULL
103			OR su2_cstr_shon19 IS NOT NULL
104			OR su2_cstr_shon20 IS NOT NULL
105			OR su2_chge_shon1 IS NOT NULL
106			OR su2_chge_shon2 IS NOT NULL
107			OR su2_chge_shon3 IS NOT NULL
108			OR su2_chge_shon4 IS NOT NULL
109			OR su2_chge_shon5 IS NOT NULL
110			OR su2_chge_shon6 IS NOT NULL
111			OR su2_chge_shon7 IS NOT NULL
112			OR su2_chge_shon8 IS NOT NULL
113			OR su2_chge_shon9 IS NOT NULL
114			OR su2_chge_shon10 IS NOT NULL
115			OR su2_chge_shon11 IS NOT NULL
116			OR su2_chge_shon12 IS NOT NULL
117			OR su2_chge_shon13 IS NOT NULL
118			OR su2_chge_shon14 IS NOT NULL
119			OR su2_chge_shon15 IS NOT NULL
120			OR su2_chge_shon16 IS NOT NULL
121			OR su2_chge_shon17 IS NOT NULL
122			OR su2_chge_shon18 IS NOT NULL
123			OR su2_chge_shon19 IS NOT NULL
124			OR su2_chge_shon20 IS NOT NULL
125			OR su2_demo_shon1 IS NOT NULL
126			OR su2_demo_shon2 IS NOT NULL
127			OR su2_demo_shon3 IS NOT NULL
128			OR su2_demo_shon4 IS NOT NULL
129			OR su2_demo_shon5 IS NOT NULL
130			OR su2_demo_shon6 IS NOT NULL
131			OR su2_demo_shon7 IS NOT NULL
132			OR su2_demo_shon8 IS NOT NULL
133			OR su2_demo_shon9 IS NOT NULL
134			OR su2_demo_shon10 IS NOT NULL
135			OR su2_demo_shon11 IS NOT NULL
136			OR su2_demo_shon12 IS NOT NULL
137			OR su2_demo_shon13 IS NOT NULL
138			OR su2_demo_shon14 IS NOT NULL
139			OR su2_demo_shon15 IS NOT NULL
140			OR su2_demo_shon16 IS NOT NULL
141			OR su2_demo_shon17 IS NOT NULL
142			OR su2_demo_shon18 IS NOT NULL
143			OR su2_demo_shon19 IS NOT NULL
144			OR su2_demo_shon20 IS NOT NULL
145			OR su2_sup_shon1 IS NOT NULL
146			OR su2_sup_shon2 IS NOT NULL
147			OR su2_sup_shon3 IS NOT NULL
148			OR su2_sup_shon4 IS NOT NULL
149			OR su2_sup_shon5 IS NOT NULL
150			OR su2_sup_shon6 IS NOT NULL
151			OR su2_sup_shon7 IS NOT NULL
152			OR su2_sup_shon8 IS NOT NULL
153			OR su2_sup_shon9 IS NOT NULL
154			OR su2_sup_shon10 IS NOT NULL
155			OR su2_sup_shon11 IS NOT NULL
156			OR su2_sup_shon12 IS NOT NULL
157			OR su2_sup_shon13 IS NOT NULL
158			OR su2_sup_shon14 IS NOT NULL
159			OR su2_sup_shon15 IS NOT NULL
160			OR su2_sup_shon16 IS NOT NULL
161			OR su2_sup_shon17 IS NOT NULL
162			OR su2_sup_shon18 IS NOT NULL
163			OR su2_sup_shon19 IS NOT NULL
164			OR su2_sup_shon20 IS NOT NULL
165			THEN
166			REGEXP_REPLACE(CONCAT(
167			CASE WHEN donnees_techniques.su2_cstr_shon1 IS NULL
168			THEN ''
169			ELSE CONCAT ('Exploitation agricole - ', donnees_techniques.su2_cstr_shon1, ' m² / ')
170			END,
171			CASE WHEN donnees_techniques.su2_cstr_shon2 IS NULL
172			THEN ''
173			ELSE CONCAT ('Exploitation forestière - ', donnees_techniques.su2_cstr_shon2, ' m² / ')
174			END,
175			CASE WHEN donnees_techniques.su2_cstr_shon3 IS NULL
176			THEN ''
177			ELSE CONCAT ('Logement - ', donnees_techniques.su2_cstr_shon3, ' m² / ')
178			END,
179			CASE WHEN donnees_techniques.su2_cstr_shon4 IS NULL
180			THEN ''
181			ELSE CONCAT ('Hébergement - ', donnees_techniques.su2_cstr_shon4, ' m² / ')
182			END,
183			CASE WHEN donnees_techniques.su2_cstr_shon5 IS NULL
184			THEN ''
185			ELSE CONCAT ('Artisanat et commerce de détail - ', donnees_techniques.su2_cstr_shon5, ' m² / ')
186			END,
187			CASE WHEN donnees_techniques.su2_cstr_shon6 IS NULL
188			THEN ''
189			ELSE CONCAT ('Restauration - ', donnees_techniques.su2_cstr_shon6, ' m² / ')
190			END,
191			CASE WHEN donnees_techniques.su2_cstr_shon7 IS NULL
192			THEN ''
193			ELSE CONCAT ('Commerce de gros - ', donnees_techniques.su2_cstr_shon7, ' m² / ')
194			END,
195			CASE WHEN donnees_techniques.su2_cstr_shon8 IS NULL
196			THEN ''
197			ELSE CONCAT ('Activités de services où s''effectue l''accueil d''une clientèle - ', donnees_techniques.su2_cstr_shon8, ' m² / ')
198			END,
199			CASE WHEN donnees_techniques.su2_cstr_shon9 IS NULL
200			THEN ''
201			ELSE CONCAT ('Hébergement hôtelier et touristique - ', donnees_techniques.su2_cstr_shon9, ' m² / ')
202			END,
203			CASE WHEN donnees_techniques.su2_cstr_shon10 IS NULL
204			THEN ''
205			ELSE CONCAT ('Cinéma - ', donnees_techniques.su2_cstr_shon10, ' m² / ')
206			END,
207			CASE WHEN donnees_techniques.su2_cstr_shon11 IS NULL
208			THEN ''
209			ELSE CONCAT ('Locaux et bureaux accueillant du public des administrations publiques et assimilés - ', donnees_techniques.su2_cstr_shon11, ' m² / ')
210			END,
211			CASE WHEN donnees_techniques.su2_cstr_shon12 IS NULL
212			THEN ''
213			ELSE CONCAT ('Locaux techniques et industriels des administrations publiques et assimilés - ', donnees_techniques.su2_cstr_shon12, ' m² / ')
214			END,
215			CASE WHEN donnees_techniques.su2_cstr_shon13 IS NULL
216			THEN ''
217			ELSE CONCAT ('Établissements d''enseignement, de santé et d''action sociale - ', donnees_techniques.su2_cstr_shon13, ' m² / ')
218			END,
219			CASE WHEN donnees_techniques.su2_cstr_shon14 IS NULL
220			THEN ''
221			ELSE CONCAT ('Salles d''art et de spectacles - ', donnees_techniques.su2_cstr_shon14, ' m² / ')
222			END,
223			CASE WHEN donnees_techniques.su2_cstr_shon15 IS NULL
224			THEN ''
225			ELSE CONCAT ('Équipements sportifs - ', donnees_techniques.su2_cstr_shon15, ' m² / ')
226			END,
227			CASE WHEN donnees_techniques.su2_cstr_shon16 IS NULL
228			THEN ''
229			ELSE CONCAT ('Autres équipements recevant du public - ', donnees_techniques.su2_cstr_shon16, ' m² / ')
230			END,
231			CASE WHEN donnees_techniques.su2_cstr_shon17 IS NULL
232			THEN ''
233			ELSE CONCAT ('Industrie - ', donnees_techniques.su2_cstr_shon17, ' m² / ')
234			END,
235			CASE WHEN donnees_techniques.su2_cstr_shon18 IS NULL
236			THEN ''
237			ELSE CONCAT ('Entrepôt - ', donnees_techniques.su2_cstr_shon18, ' m² / ')
238			END,
239			CASE WHEN donnees_techniques.su2_cstr_shon19 IS NULL
240			THEN ''
241			ELSE CONCAT ('Bureau - ', donnees_techniques.su2_cstr_shon19, ' m² / ')
242			END,
243			CASE WHEN donnees_techniques.su2_cstr_shon20 IS NULL
244			THEN ''
245			ELSE CONCAT ('Centre de congrès et d''exposition - ', donnees_techniques.su2_cstr_shon20, ' m²')
246			END
247			), ' / $', '')
248			ELSE
249			REGEXP_REPLACE(CONCAT(
250			CASE
251			WHEN donnees_techniques.su_cstr_shon1 IS NULL
252			THEN ''
253			ELSE CONCAT('Habitation - ', donnees_techniques.su_cstr_shon1, ' m² / ')
254			END,
255			CASE
256			WHEN donnees_techniques.su_cstr_shon2 IS NULL
257			THEN ''
258			ELSE CONCAT('Hébergement hôtelier - ', donnees_techniques.su_cstr_shon2, ' m² / ')
259			END,
260			CASE
261			WHEN donnees_techniques.su_cstr_shon3 IS NULL
262			THEN ''
263			ELSE CONCAT('Bureaux - ', donnees_techniques.su_cstr_shon3, ' m² / ')
264			END,
265			CASE
266			WHEN donnees_techniques.su_cstr_shon4 IS NULL
267			THEN ''
268			ELSE CONCAT('Commerce - ', donnees_techniques.su_cstr_shon4, ' m² / ')
269			END,
270			CASE
271			WHEN donnees_techniques.su_cstr_shon5 IS NULL
272			THEN ''
273			ELSE CONCAT('Artisanat - ', donnees_techniques.su_cstr_shon5, ' m² / ')
274			END,
275			CASE
276			WHEN donnees_techniques.su_cstr_shon6 IS NULL
277			THEN ''
278			ELSE CONCAT('Industrie - ', donnees_techniques.su_cstr_shon6, ' m² / ')
279			END,
280			CASE
281			WHEN donnees_techniques.su_cstr_shon7 IS NULL
282			THEN ''
283			ELSE CONCAT('Exploitation agricole ou forestière - ', donnees_techniques.su_cstr_shon7, ' m² / ')
284			END,
285			CASE
286			WHEN donnees_techniques.su_cstr_shon8 IS NULL
287			THEN ''
288			ELSE CONCAT('Entrepôt - ', donnees_techniques.su_cstr_shon8, ' m² / ')
289			END,
290			CASE
291			WHEN donnees_techniques.su_cstr_shon9 IS NULL
292			THEN ''
293			ELSE CONCAT('Service public ou d''intérêt collectif - ', donnees_techniques.su_cstr_shon9, ' m²')
294			END
295			), ' / $', '')
296			END as destination_surfaces],
297	mbroquet	3730	[CONCAT(
298			CASE
299			WHEN demandeur.qualite='particulier' THEN
300			TRIM(CONCAT(demandeur.particulier_nom, ' ', demandeur.particulier_prenom))
301			ELSE
302			TRIM(CONCAT(demandeur.personne_morale_raison_sociale, ' ', demandeur.personne_morale_denomination))
303			END, '
304			',demandeur.numero, ' ', demandeur.voie, ' ',
305			demandeur.complement, ' ', demandeur.lieu_dit, ' ',
306			demandeur.code_postal, ' ', demandeur.localite, ' ', CASE
307			WHEN demandeur.bp IS NULL THEN
308			''
309			ELSE
310			CONCAT('BP ', demandeur.bp)
311			END, ' ', CASE
312			WHEN demandeur.cedex IS NULL THEN
313			''
314			ELSE
315			CONCAT('CEDEX ', demandeur.cedex)
316			END, '
317			', demandeur.pays) as coordonnees_petitionnaire_principal],
318			[CONCAT(
319			dossier.terrain_adresse_voie_numero, ' ', dossier.terrain_adresse_voie, ' ',
320			dossier.terrain_adresse_lieu_dit, ' ',
321			dossier.terrain_adresse_code_postal, ' ', dossier.terrain_adresse_localite, ' ', CASE
322			WHEN dossier.terrain_adresse_bp IS NULL THEN
323			''
324			ELSE
325			CONCAT('BP ', dossier.terrain_adresse_bp)
326			END, ' ',CASE
327			WHEN dossier.terrain_adresse_cedex IS NULL THEN
328			''
329			ELSE
330			CONCAT('CEDEX ', dossier.terrain_adresse_cedex)
331			END, ' ',
332			arrondissement.libelle) as adresse_terrain],
333			[dossier.terrain_references_cadastrales as reference_cadastrale],
334			[to_char(dossier.date_decision ,'DD/MM/YYYY') as date_decision],
335	softime	4678	[CASE WHEN su2_avt_shon1 IS NOT NULL
336			OR su2_avt_shon2 IS NOT NULL
337			OR su2_avt_shon3 IS NOT NULL
338			OR su2_avt_shon4 IS NOT NULL
339			OR su2_avt_shon5 IS NOT NULL
340			OR su2_avt_shon6 IS NOT NULL
341			OR su2_avt_shon7 IS NOT NULL
342			OR su2_avt_shon8 IS NOT NULL
343			OR su2_avt_shon9 IS NOT NULL
344			OR su2_avt_shon10 IS NOT NULL
345			OR su2_avt_shon11 IS NOT NULL
346			OR su2_avt_shon12 IS NOT NULL
347			OR su2_avt_shon13 IS NOT NULL
348			OR su2_avt_shon14 IS NOT NULL
349			OR su2_avt_shon15 IS NOT NULL
350			OR su2_avt_shon16 IS NOT NULL
351			OR su2_avt_shon17 IS NOT NULL
352			OR su2_avt_shon18 IS NOT NULL
353			OR su2_avt_shon19 IS NOT NULL
354			OR su2_avt_shon20 IS NOT NULL
355			OR su2_cstr_shon1 IS NOT NULL
356			OR su2_cstr_shon2 IS NOT NULL
357			OR su2_cstr_shon3 IS NOT NULL
358			OR su2_cstr_shon4 IS NOT NULL
359			OR su2_cstr_shon5 IS NOT NULL
360			OR su2_cstr_shon6 IS NOT NULL
361			OR su2_cstr_shon7 IS NOT NULL
362			OR su2_cstr_shon8 IS NOT NULL
363			OR su2_cstr_shon9 IS NOT NULL
364			OR su2_cstr_shon10 IS NOT NULL
365			OR su2_cstr_shon11 IS NOT NULL
366			OR su2_cstr_shon12 IS NOT NULL
367			OR su2_cstr_shon13 IS NOT NULL
368			OR su2_cstr_shon14 IS NOT NULL
369			OR su2_cstr_shon15 IS NOT NULL
370			OR su2_cstr_shon16 IS NOT NULL
371			OR su2_cstr_shon17 IS NOT NULL
372			OR su2_cstr_shon18 IS NOT NULL
373			OR su2_cstr_shon19 IS NOT NULL
374			OR su2_cstr_shon20 IS NOT NULL
375			OR su2_chge_shon1 IS NOT NULL
376			OR su2_chge_shon2 IS NOT NULL
377			OR su2_chge_shon3 IS NOT NULL
378			OR su2_chge_shon4 IS NOT NULL
379			OR su2_chge_shon5 IS NOT NULL
380			OR su2_chge_shon6 IS NOT NULL
381			OR su2_chge_shon7 IS NOT NULL
382			OR su2_chge_shon8 IS NOT NULL
383			OR su2_chge_shon9 IS NOT NULL
384			OR su2_chge_shon10 IS NOT NULL
385			OR su2_chge_shon11 IS NOT NULL
386			OR su2_chge_shon12 IS NOT NULL
387			OR su2_chge_shon13 IS NOT NULL
388			OR su2_chge_shon14 IS NOT NULL
389			OR su2_chge_shon15 IS NOT NULL
390			OR su2_chge_shon16 IS NOT NULL
391			OR su2_chge_shon17 IS NOT NULL
392			OR su2_chge_shon18 IS NOT NULL
393			OR su2_chge_shon19 IS NOT NULL
394			OR su2_chge_shon20 IS NOT NULL
395			OR su2_demo_shon1 IS NOT NULL
396			OR su2_demo_shon2 IS NOT NULL
397			OR su2_demo_shon3 IS NOT NULL
398			OR su2_demo_shon4 IS NOT NULL
399			OR su2_demo_shon5 IS NOT NULL
400			OR su2_demo_shon6 IS NOT NULL
401			OR su2_demo_shon7 IS NOT NULL
402			OR su2_demo_shon8 IS NOT NULL
403			OR su2_demo_shon9 IS NOT NULL
404			OR su2_demo_shon10 IS NOT NULL
405			OR su2_demo_shon11 IS NOT NULL
406			OR su2_demo_shon12 IS NOT NULL
407			OR su2_demo_shon13 IS NOT NULL
408			OR su2_demo_shon14 IS NOT NULL
409			OR su2_demo_shon15 IS NOT NULL
410			OR su2_demo_shon16 IS NOT NULL
411			OR su2_demo_shon17 IS NOT NULL
412			OR su2_demo_shon18 IS NOT NULL
413			OR su2_demo_shon19 IS NOT NULL
414			OR su2_demo_shon20 IS NOT NULL
415			OR su2_sup_shon1 IS NOT NULL
416			OR su2_sup_shon2 IS NOT NULL
417			OR su2_sup_shon3 IS NOT NULL
418			OR su2_sup_shon4 IS NOT NULL
419			OR su2_sup_shon5 IS NOT NULL
420			OR su2_sup_shon6 IS NOT NULL
421			OR su2_sup_shon7 IS NOT NULL
422			OR su2_sup_shon8 IS NOT NULL
423			OR su2_sup_shon9 IS NOT NULL
424			OR su2_sup_shon10 IS NOT NULL
425			OR su2_sup_shon11 IS NOT NULL
426			OR su2_sup_shon12 IS NOT NULL
427			OR su2_sup_shon13 IS NOT NULL
428			OR su2_sup_shon14 IS NOT NULL
429			OR su2_sup_shon15 IS NOT NULL
430			OR su2_sup_shon16 IS NOT NULL
431			OR su2_sup_shon17 IS NOT NULL
432			OR su2_sup_shon18 IS NOT NULL
433			OR su2_sup_shon19 IS NOT NULL
434			OR su2_sup_shon20 IS NOT NULL
435			THEN donnees_techniques.su2_tot_shon2
436			ELSE donnees_techniques.su_tot_shon2
437			END as shon],
438	mbroquet	3730	[CONCAT(architecte.prenom, ' ', architecte.nom) as architecte],
439			[REGEXP_REPLACE(CONCAT(
440			CASE
441			WHEN donnees_techniques.co_sp_transport IS TRUE THEN
442			CONCAT('Transport / ')
443			ELSE
444			''
445			END,
446			CASE
447			WHEN donnees_techniques.co_sp_enseign IS TRUE THEN
448			CONCAT('Enseignement et recherche / ')
449			ELSE
450			''
451			END,
452			CASE
453			WHEN donnees_techniques.co_sp_act_soc IS TRUE THEN
454			CONCAT('Action sociale / ')
455			ELSE
456			''
457			END,
458			CASE
459			WHEN donnees_techniques.co_sp_transport IS TRUE THEN
460			CONCAT('Ouvrage spécial / ')
461			ELSE
462			''
463			END,
464			CASE
465			WHEN donnees_techniques.co_sp_sante IS TRUE THEN
466			CONCAT('Santé / ')
467			ELSE
468			''
469			END,
470			CASE
471			WHEN donnees_techniques.co_sp_culture IS TRUE THEN
472			CONCAT('Culture et loisir / ')
473			ELSE
474			''
475			END), ' / $', '') as affectation_surface],
476			[CASE
477			WHEN donnees_techniques.co_cstr_nouv IS TRUE THEN
478			'Nouvelle construction'
479			WHEN donnees_techniques.co_cstr_exist IS TRUE THEN
480			'Travaux ou changement de destination sur une construction existante'
481			WHEN donnees_techniques.co_cloture IS TRUE THEN
482			'Travaux clôturés'
483			END as nature_travaux],
484			[REGEXP_REPLACE(CONCAT(
485			CASE
486			WHEN donnees_techniques.co_fin_lls_nb IS NULL THEN
487			''
488			ELSE
489			CONCAT('Logement Locatif Social / ')
490			END,
491			CASE
492			WHEN donnees_techniques.co_fin_aa_nb IS NULL THEN
493			''
494			ELSE
495			CONCAT('Accession Sociale (hors prêt à taux zéro) / ')
496			END,
497			CASE
498			WHEN donnees_techniques.co_fin_ptz_nb IS NULL THEN
499			''
500			ELSE
501			CONCAT('Prêt à taux zéro / ')
502			END,
503			CASE
504			WHEN donnees_techniques.co_fin_autr_nb IS NULL THEN
505			''
506			ELSE
507			CONCAT('Autres financements')
508			END), ' / $', '') as nature_financement],
509			[donnees_techniques.co_tot_log_nb as nombre_logements],
510			[autorite_competente.libelle as autorite_competente],
511			[avis_decision.libelle as decision]
512			FROM ".DB_PREFIXE."dossier
513			LEFT JOIN ".DB_PREFIXE."dossier_instruction_type
514			ON dossier_instruction_type.dossier_instruction_type = dossier.dossier_instruction_type
515			LEFT JOIN ".DB_PREFIXE."dossier_autorisation
516			ON dossier_autorisation.dossier_autorisation = dossier.dossier_autorisation
517			LEFT JOIN ".DB_PREFIXE."dossier as doc
518			ON doc.dossier_autorisation = dossier_autorisation.dossier_autorisation AND doc.dossier_instruction_type = (SELECT dossier_instruction_type FROM ".DB_PREFIXE."dossier_instruction_type as dit WHERE dit.code = 'DOC' AND dit.dossier_autorisation_type_detaille = dossier_instruction_type.dossier_autorisation_type_detaille)
519			LEFT JOIN ".DB_PREFIXE."dossier as daact
520			ON daact.dossier_autorisation = dossier_autorisation.dossier_autorisation AND daact.dossier_instruction_type = (SELECT dossier_instruction_type FROM ".DB_PREFIXE."dossier_instruction_type as dit WHERE dit.code = 'DAACT' AND dit.dossier_autorisation_type_detaille = dossier_instruction_type.dossier_autorisation_type_detaille)
521			LEFT JOIN ".DB_PREFIXE."donnees_techniques
522			ON donnees_techniques.dossier_instruction = dossier.dossier
523			LEFT JOIN ".DB_PREFIXE."lien_dossier_demandeur
524			ON lien_dossier_demandeur.dossier = dossier.dossier AND lien_dossier_demandeur.petitionnaire_principal IS TRUE
525			LEFT JOIN ".DB_PREFIXE."demandeur
526			ON demandeur.demandeur = lien_dossier_demandeur.demandeur
527			LEFT JOIN ".DB_PREFIXE."arrondissement
528			ON arrondissement.code_postal = dossier.terrain_adresse_code_postal
529			LEFT JOIN ".DB_PREFIXE."architecte
530			ON architecte.architecte = donnees_techniques.architecte
531			LEFT JOIN ".DB_PREFIXE."autorite_competente
532			ON autorite_competente.autorite_competente = dossier.autorite_competente
533			LEFT JOIN ".DB_PREFIXE."avis_decision
534			ON avis_decision.avis_decision = dossier.avis_decision
535			LEFT JOIN ".DB_PREFIXE."dossier_autorisation_type_detaille
536			ON dossier_autorisation_type_detaille.dossier_autorisation_type_detaille = dossier_instruction_type.dossier_autorisation_type_detaille
537			LEFT JOIN ".DB_PREFIXE."dossier_autorisation_type
538			ON dossier_autorisation_type.dossier_autorisation_type = dossier_autorisation_type_detaille.dossier_autorisation_type
539			WHERE dossier_autorisation_type.code = 'CU' AND
540			dossier.date_decision >= '[date_decision_debut]' AND
541			dossier.date_decision <= '[date_decision_fin]'
542			ORDER BY [tri], dossier.dossier";
543			?>