| 1 |
<?php |
| 2 |
// Filtre des requêtes de group pour les reqmo |
| 3 |
include ('../sql/pgsql/filter_group_reqmo.inc.php'); |
| 4 |
|
| 5 |
// Libellé de la requête |
| 6 |
$reqmo['libelle']=_("Liste des dossiers DIA"); |
| 7 |
|
| 8 |
// Choix des champs à afficher |
| 9 |
$reqmo['reference_dossier']='checked'; |
| 10 |
$reqmo['date_depot']='checked'; |
| 11 |
$reqmo['demandeur']='checked'; |
| 12 |
$reqmo['terrain_references_cadastrales']='checked'; |
| 13 |
$reqmo['terrain_adresse_voie_numero']='checked'; |
| 14 |
$reqmo['terrain_adresse_voie']='checked'; |
| 15 |
$reqmo['terrain_adresse_lieu_dit']='checked'; |
| 16 |
$reqmo['terrain_adresse_localite']='checked'; |
| 17 |
$reqmo['terrain_adresse_code_postal']='checked'; |
| 18 |
$reqmo['terrain_adresse_bp']='checked'; |
| 19 |
$reqmo['terrain_adresse_cedex']='checked'; |
| 20 |
$reqmo['dia_mod_cess_prix_vente']='checked'; |
| 21 |
$reqmo['dia_acquereur_nom_prenom']='checked'; |
| 22 |
$reqmo['commune_res_acquereur']='checked'; |
| 23 |
$reqmo['etat']='checked'; |
| 24 |
$reqmo['description_du_bien']='checked'; |
| 25 |
$reqmo['dia_su_co_sol']='checked'; |
| 26 |
$reqmo['dia_su_util_hab']='checked'; |
| 27 |
$reqmo['usage']='checked'; |
| 28 |
$reqmo['nb_niv']='checked'; |
| 29 |
$reqmo['nb_appart']='checked'; |
| 30 |
$reqmo['nb_autre_loc']='checked'; |
| 31 |
$reqmo['dia_vente_lot_volume']='checked'; |
| 32 |
$reqmo['vente_lot_volume_prec']='checked'; |
| 33 |
$reqmo['dia_bat_copro']='checked'; |
| 34 |
$reqmo['bat_copro_prec']='checked'; |
| 35 |
$reqmo['dia_indivi_quote_part']='checked'; |
| 36 |
$reqmo['dia_mod_cess_prix_vente_num']='checked'; |
| 37 |
$reqmo['dia_mod_cess_prix_vente_mob']='checked'; |
| 38 |
$reqmo['dia_mod_cess_prix_vente_cheptel']='checked'; |
| 39 |
$reqmo['dia_mod_cess_prix_vente_recol']='checked'; |
| 40 |
$reqmo['dia_mod_cess_prix_vente_autre']='checked'; |
| 41 |
$reqmo['dia_mod_cess_commi_mnt']='checked'; |
| 42 |
$reqmo['dia_esti_prix_france_dom']='checked'; |
| 43 |
$reqmo['dia_prop_collectivite']='checked'; |
| 44 |
|
| 45 |
//Choix des critères de tri |
| 46 |
$reqmo['date_decision_debut'] = "../../...."; |
| 47 |
$reqmo['date_decision_fin'] = "../../...."; |
| 48 |
|
| 49 |
//Type attendu pour les données |
| 50 |
$reqmo['type']['date_decision_debut'] = 'date'; |
| 51 |
$reqmo['type']['date_decision_fin'] = 'date'; |
| 52 |
$reqmo['type']['tri'] = 'string'; |
| 53 |
|
| 54 |
// Critères obligatoires |
| 55 |
$reqmo['required'] = array( |
| 56 |
'date_decision_debut' => false, |
| 57 |
'date_decision_fin' => false, |
| 58 |
); |
| 59 |
|
| 60 |
// Critères par défaut |
| 61 |
$date_defaut_debut = '01/01/1900'; |
| 62 |
$date_defaut_fin = '01/01/2100'; |
| 63 |
$reqmo['default'] = array( |
| 64 |
'date_decision_debut' => $date_defaut_debut, |
| 65 |
'date_decision_fin' => $date_defaut_fin, |
| 66 |
); |
| 67 |
|
| 68 |
// Conditions à supprimer |
| 69 |
$reqmo['conditions_to_delete'] = array( |
| 70 |
"AND dossier.date_decision >= '".$date_defaut_debut."' AND dossier.date_decision <= '".$date_defaut_fin."'", |
| 71 |
); |
| 72 |
|
| 73 |
// Affiche le champs de la collectivité si l'utilisateur connecté est lié à la |
| 74 |
// collectivité de niveau 2 |
| 75 |
$reqmo_select_om_collective = ''; |
| 76 |
if ($_SESSION['niveau'] == '2') { |
| 77 |
// |
| 78 |
$reqmo['om_collectivite']='checked'; |
| 79 |
$reqmo_select_om_collective = ",[om_collectivite.libelle as om_collectivite]"; |
| 80 |
} |
| 81 |
|
| 82 |
// Traductions des champs |
| 83 |
_("reference_dossier"); |
| 84 |
_("date_depot"); |
| 85 |
_("demandeur"); |
| 86 |
_("terrain_references_cadastrales"); |
| 87 |
_("terrain_adresse_voie_numero"); |
| 88 |
_("terrain_adresse_voie"); |
| 89 |
_("terrain_adresse_lieu_dit"); |
| 90 |
_("terrain_adresse_localite"); |
| 91 |
_("terrain_adresse_code_postal"); |
| 92 |
_("terrain_adresse_bp"); |
| 93 |
_("terrain_adresse_cedex"); |
| 94 |
_("dia_mod_cess_prix_vente"); |
| 95 |
_("dia_acquereur_nom_prenom"); |
| 96 |
_("commune_res_acquereur"); |
| 97 |
_("etat"); |
| 98 |
_("description_du_bien"); |
| 99 |
_("om_collectivite"); |
| 100 |
_("usage"); |
| 101 |
_("nb_niv"); |
| 102 |
_("nb_appart"); |
| 103 |
_("nb_autre_loc"); |
| 104 |
_("dia_vente_lot_volume"); |
| 105 |
_("vente_lot_volume_prec"); |
| 106 |
_("dia_bat_copro"); |
| 107 |
_("bat_copro_prec"); |
| 108 |
_("dia_indivi_quote_part"); |
| 109 |
|
| 110 |
// Requête à effectuer |
| 111 |
$reqmo['sql'] = " |
| 112 |
SELECT |
| 113 |
[dossier.dossier_libelle as reference_dossier], |
| 114 |
[to_char(dossier.date_depot, 'DD/MM/YYYY') as date_depot], |
| 115 |
[CASE WHEN petitionnaire_principal.qualite='particulier' THEN |
| 116 |
TRIM(CONCAT(petitionnaire_principal.particulier_nom, ' ', petitionnaire_principal.particulier_prenom)) |
| 117 |
ELSE |
| 118 |
TRIM(CONCAT(petitionnaire_principal.personne_morale_raison_sociale, ' ', petitionnaire_principal.personne_morale_denomination)) |
| 119 |
END as demandeur], |
| 120 |
[CONCAT_WS( |
| 121 |
' / ', |
| 122 |
CASE WHEN donnees_techniques.dia_imm_non_bati IS TRUE |
| 123 |
THEN TRIM('".__("dia_imm_non_bati")."') |
| 124 |
ELSE NULL |
| 125 |
END, |
| 126 |
CASE WHEN donnees_techniques.dia_imm_bati_terr_propr IS TRUE |
| 127 |
THEN TRIM('".__("dia_imm_bati_terr_propr")."') |
| 128 |
ELSE NULL |
| 129 |
END, |
| 130 |
CASE WHEN donnees_techniques.dia_imm_bati_terr_autr IS TRUE |
| 131 |
THEN TRIM('".__("dia_imm_bati_terr_autr")."') |
| 132 |
ELSE NULL |
| 133 |
END, |
| 134 |
CASE WHEN donnees_techniques.dia_bati_vend_tot IS TRUE |
| 135 |
THEN TRIM('".__("dia_bati_vend_tot")."') |
| 136 |
ELSE NULL |
| 137 |
END |
| 138 |
) as description_du_bien], |
| 139 |
[CASE WHEN donnees_techniques.dia_su_co_sol_num IS NOT NULL |
| 140 |
THEN donnees_techniques.dia_su_co_sol_num::text |
| 141 |
ELSE donnees_techniques.dia_su_co_sol |
| 142 |
END as dia_su_co_sol], |
| 143 |
[CASE WHEN donnees_techniques.dia_su_util_hab_num IS NOT NULL |
| 144 |
THEN donnees_techniques.dia_su_util_hab_num::text |
| 145 |
ELSE donnees_techniques.dia_su_util_hab |
| 146 |
END as dia_su_co_sol], |
| 147 |
[CONCAT_WS( |
| 148 |
' / ', |
| 149 |
CASE WHEN donnees_techniques.dia_us_hab IS TRUE |
| 150 |
THEN TRIM('".__("dia_us_hab")."') |
| 151 |
ELSE NULL |
| 152 |
END, |
| 153 |
CASE WHEN donnees_techniques.dia_us_pro IS TRUE |
| 154 |
THEN TRIM('".__("dia_us_pro")."') |
| 155 |
ELSE NULL |
| 156 |
END, |
| 157 |
CASE WHEN donnees_techniques.dia_us_mixte IS TRUE |
| 158 |
THEN TRIM('".__("dia_us_mixte")."') |
| 159 |
ELSE NULL |
| 160 |
END, |
| 161 |
CASE WHEN donnees_techniques.dia_us_comm IS TRUE |
| 162 |
THEN TRIM('".__("dia_us_comm")."') |
| 163 |
ELSE NULL |
| 164 |
END, |
| 165 |
CASE WHEN donnees_techniques.dia_us_agr IS TRUE |
| 166 |
THEN TRIM('".__("dia_us_agr")."') |
| 167 |
ELSE NULL |
| 168 |
END, |
| 169 |
CASE WHEN donnees_techniques.dia_us_autre IS TRUE |
| 170 |
THEN TRIM('".__("dia_us_autre")."') |
| 171 |
ELSE NULL |
| 172 |
END |
| 173 |
) as usage], |
| 174 |
[donnees_techniques.dia_nb_niv as nb_niv], |
| 175 |
[donnees_techniques.dia_nb_appart as nb_appart], |
| 176 |
[donnees_techniques.dia_nb_autre_loc as nb_autre_loc], |
| 177 |
[CASE WHEN donnees_techniques.dia_vente_lot_volume IS TRUE |
| 178 |
THEN 'Oui' |
| 179 |
ELSE 'Non' |
| 180 |
END as dia_vente_lot_volume], |
| 181 |
[donnees_techniques.dia_vente_lot_volume_txt as vente_lot_volume_prec], |
| 182 |
[CASE WHEN donnees_techniques.dia_bat_copro IS TRUE |
| 183 |
THEN 'Oui' |
| 184 |
ELSE 'Non' |
| 185 |
END as dia_bat_copro], |
| 186 |
[donnees_techniques.dia_bat_copro_desc as bat_copro_prec], |
| 187 |
[donnees_techniques.dia_indivi_quote_part as dia_indivi_quote_part], |
| 188 |
[dossier.terrain_references_cadastrales as terrain_references_cadastrales], |
| 189 |
[dossier.terrain_adresse_voie_numero as terrain_adresse_voie_numero], |
| 190 |
[dossier.terrain_adresse_voie as terrain_adresse_voie], |
| 191 |
[dossier.terrain_adresse_lieu_dit as terrain_adresse_lieu_dit], |
| 192 |
[dossier.terrain_adresse_localite as terrain_adresse_localite], |
| 193 |
[dossier.terrain_adresse_code_postal as terrain_adresse_code_postal], |
| 194 |
[CASE WHEN dossier.terrain_adresse_bp IS NULL THEN '' ELSE CONCAT('BP ', dossier.terrain_adresse_bp) END as terrain_adresse_bp], |
| 195 |
[CASE WHEN dossier.terrain_adresse_cedex IS NULL THEN '' ELSE CONCAT('CEDEX ', dossier.terrain_adresse_cedex) END as terrain_adresse_cedex], |
| 196 |
[donnees_techniques.dia_mod_cess_prix_vente as dia_mod_cess_prix_vente], |
| 197 |
[donnees_techniques.dia_mod_cess_prix_vente_num as dia_mod_cess_prix_vente_num], |
| 198 |
[CASE WHEN donnees_techniques.dia_mod_cess_prix_vente_mob_num IS NOT NULL |
| 199 |
THEN donnees_techniques.dia_mod_cess_prix_vente_mob_num::text |
| 200 |
ELSE donnees_techniques.dia_mod_cess_prix_vente_mob |
| 201 |
END as dia_mod_cess_prix_vente_mob], |
| 202 |
[CASE WHEN donnees_techniques.dia_mod_cess_prix_vente_cheptel_num IS NOT NULL |
| 203 |
THEN donnees_techniques.dia_mod_cess_prix_vente_cheptel_num::text |
| 204 |
ELSE donnees_techniques.dia_mod_cess_prix_vente_cheptel |
| 205 |
END as dia_mod_cess_prix_vente_cheptel], |
| 206 |
[CASE WHEN donnees_techniques.dia_mod_cess_prix_vente_recol_num IS NOT NULL |
| 207 |
THEN donnees_techniques.dia_mod_cess_prix_vente_recol_num::text |
| 208 |
ELSE donnees_techniques.dia_mod_cess_prix_vente_recol |
| 209 |
END as dia_mod_cess_prix_vente_recol], |
| 210 |
[CASE WHEN donnees_techniques.dia_mod_cess_prix_vente_autre_num IS NOT NULL |
| 211 |
THEN donnees_techniques.dia_mod_cess_prix_vente_autre_num::text |
| 212 |
ELSE donnees_techniques.dia_mod_cess_prix_vente_autre |
| 213 |
END as dia_mod_cess_prix_vente_autre], |
| 214 |
[donnees_techniques.dia_mod_cess_commi_mnt as dia_mod_cess_commi_mnt], |
| 215 |
[donnees_techniques.dia_esti_prix_france_dom as dia_esti_prix_france_dom], |
| 216 |
[donnees_techniques.dia_prop_collectivite as dia_prop_collectivite], |
| 217 |
[donnees_techniques.dia_acquereur_nom_prenom as dia_acquereur_nom_prenom], |
| 218 |
[donnees_techniques.dia_acquereur_adr_localite as commune_res_acquereur], |
| 219 |
[dossier.etat as etat] |
| 220 |
".$reqmo_select_om_collective." |
| 221 |
FROM ".DB_PREFIXE."dossier |
| 222 |
INNER JOIN ".DB_PREFIXE."dossier_instruction_type |
| 223 |
ON dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type |
| 224 |
INNER JOIN ".DB_PREFIXE."donnees_techniques ON donnees_techniques.dossier_instruction = dossier.dossier |
| 225 |
INNER JOIN ".DB_PREFIXE."avis_decision ON dossier.avis_decision = avis_decision.avis_decision |
| 226 |
INNER JOIN ".DB_PREFIXE."dossier_autorisation |
| 227 |
ON dossier.dossier_autorisation = dossier_autorisation.dossier_autorisation |
| 228 |
INNER JOIN ".DB_PREFIXE."dossier_autorisation_type_detaille |
| 229 |
ON dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille |
| 230 |
INNER JOIN ".DB_PREFIXE."om_collectivite |
| 231 |
ON om_collectivite.om_collectivite = dossier.om_collectivite |
| 232 |
LEFT JOIN ( |
| 233 |
SELECT * |
| 234 |
FROM ".DB_PREFIXE."lien_dossier_demandeur |
| 235 |
INNER JOIN ".DB_PREFIXE."demandeur |
| 236 |
ON demandeur.demandeur = lien_dossier_demandeur.demandeur |
| 237 |
WHERE lien_dossier_demandeur.petitionnaire_principal IS TRUE |
| 238 |
AND demandeur.type_demandeur = 'petitionnaire' |
| 239 |
) as petitionnaire_principal |
| 240 |
ON petitionnaire_principal.dossier = dossier.dossier |
| 241 |
INNER JOIN ".DB_PREFIXE."dossier_autorisation_type |
| 242 |
ON dossier_autorisation_type_detaille.dossier_autorisation_type = dossier_autorisation_type.dossier_autorisation_type |
| 243 |
AND LOWER(dossier_autorisation_type.affichage_form) = 'ads' |
| 244 |
INNER JOIN ".DB_PREFIXE."groupe |
| 245 |
ON dossier_autorisation_type.groupe = groupe.groupe |
| 246 |
".$selection." |
| 247 |
WHERE dossier.om_collectivite IN (<idx_collectivite>) |
| 248 |
AND dossier_autorisation_type_detaille.dossier_autorisation_type_detaille IN (<id_datd_filtre_reqmo_dossier_dia>) |
| 249 |
AND dossier.date_decision >= '[date_decision_debut]' |
| 250 |
AND dossier.date_decision <= '[date_decision_fin]'". |
| 251 |
$sqlFiltreSD. |
| 252 |
"ORDER BY dossier.dossier ASC"; |
| 253 |
?> |
Parent Directory
| 
Revision Log