| 1 |
nmeucci |
4202 |
<?php |
| 2 |
|
|
|
| 3 |
|
|
// Libellé de la requête |
| 4 |
|
|
$reqmo['libelle']=_("Statistiques évoluées destinées à permettre la refacturation du service mutualisé d'instruction aux communes concernées"); |
| 5 |
|
|
|
| 6 |
|
|
// Choix des critères de tri |
| 7 |
|
|
$reqmo['date_depot_debut'] = "../../...."; |
| 8 |
|
|
$reqmo['date_depot_fin'] = "../../...."; |
| 9 |
|
|
$reqmo['date_decision_debut'] = "../../...."; |
| 10 |
|
|
$reqmo['date_decision_fin'] = "../../...."; |
| 11 |
|
|
|
| 12 |
|
|
// Type attendu pour les données |
| 13 |
|
|
$reqmo['type']['date_depot_debut'] = 'date'; |
| 14 |
|
|
$reqmo['type']['date_depot_fin'] = 'date'; |
| 15 |
|
|
$reqmo['type']['date_decision_debut'] = 'date'; |
| 16 |
|
|
$reqmo['type']['date_decision_fin'] = 'date'; |
| 17 |
|
|
$reqmo['type']['tri'] = 'string'; |
| 18 |
|
|
|
| 19 |
|
|
// Critères obligatoires |
| 20 |
|
|
$reqmo['required'] = array( |
| 21 |
|
|
'date_depot_debut' => false, |
| 22 |
|
|
'date_depot_fin' => false, |
| 23 |
|
|
'date_decision_debut' => false, |
| 24 |
|
|
'date_decision_fin' => false, |
| 25 |
|
|
); |
| 26 |
|
|
|
| 27 |
|
|
// Critères par défaut |
| 28 |
|
|
$date_defaut_debut = '01/01/1900'; |
| 29 |
|
|
$date_defaut_fin = '01/01/2100'; |
| 30 |
|
|
$reqmo['default'] = array( |
| 31 |
|
|
'date_depot_debut' => $date_defaut_debut, |
| 32 |
|
|
'date_depot_fin' => $date_defaut_fin, |
| 33 |
|
|
'date_decision_debut' => $date_defaut_debut, |
| 34 |
|
|
'date_decision_fin' => $date_defaut_fin, |
| 35 |
|
|
); |
| 36 |
|
|
|
| 37 |
|
|
// Conditions à supprimer |
| 38 |
|
|
$reqmo['conditions_to_delete'] = array( |
| 39 |
|
|
"AND dossier.date_depot >= '".$date_defaut_debut."' AND dossier.date_depot <= '".$date_defaut_fin."'", |
| 40 |
|
|
"AND dossier.date_decision >= '".$date_defaut_debut."' AND dossier.date_decision <= '".$date_defaut_fin."'", |
| 41 |
|
|
); |
| 42 |
|
|
|
| 43 |
|
|
// Tri |
| 44 |
|
|
$reqmo['tri']= array('dossier.dossier', 'dossier.date_depot'); |
| 45 |
|
|
|
| 46 |
|
|
// Requête |
| 47 |
|
|
$reqmo['sql']=" |
| 48 |
|
|
SELECT |
| 49 |
|
|
dossier.dossier as \"référence dossier instruction\", |
| 50 |
|
|
dossier_autorisation.dossier_autorisation as \"référence dossier autorisation\", |
| 51 |
|
|
om_collectivite.libelle as \"commune\", |
| 52 |
|
|
division_dossier.libelle as \"division dossier\", |
| 53 |
|
|
dossier_autorisation_type_detaille.code as \"code type DA détaillé\", |
| 54 |
|
|
dossier_autorisation_type_detaille.libelle as \"libellé type DA détaillé\", |
| 55 |
|
|
dossier_instruction_type.code as \"code type DI\", |
| 56 |
|
|
dossier_instruction_type.libelle as \"libellé type DI\", |
| 57 |
|
|
instructeur.instructeur as \"identifiant instructeur\", |
| 58 |
|
|
instructeur.nom as \"nom instructeur\", |
| 59 |
|
|
division_instructeur.libelle as \"division instructeur\", |
| 60 |
|
|
direction_instructeur.libelle as \"direction instructeur\", |
| 61 |
|
|
to_char(dossier.date_depot ,'DD/MM/YYYY') as \"date dépôt initial\", |
| 62 |
|
|
to_char(dossier.date_limite ,'DD/MM/YYYY') as \"date limite instruction\", |
| 63 |
|
|
to_char(dossier.date_decision ,'DD/MM/YYYY') as \"date décision\", |
| 64 |
|
|
etat.libelle as \"état DI\", |
| 65 |
|
|
count_instruction.nb_instruction as \"total instructions\", |
| 66 |
|
|
count_consultation.nb_consultation as \"total consultations\", |
| 67 |
|
|
dossier.tax_mtn_part_commu as \"simulation taxes part communale\", |
| 68 |
|
|
dossier.tax_mtn_part_depart as \"simulation taxes part départementale\", |
| 69 |
|
|
dossier.tax_mtn_total as \"simulation taxes total\" |
| 70 |
|
|
|
| 71 |
|
|
FROM ".DB_PREFIXE."dossier |
| 72 |
|
|
LEFT JOIN ".DB_PREFIXE."dossier_autorisation |
| 73 |
|
|
ON dossier_autorisation.dossier_autorisation = dossier.dossier_autorisation |
| 74 |
|
|
LEFT JOIN ".DB_PREFIXE."dossier_autorisation_type_detaille |
| 75 |
|
|
ON dossier_autorisation.dossier_autorisation_type_detaille = dossier_autorisation_type_detaille.dossier_autorisation_type_detaille |
| 76 |
|
|
LEFT JOIN ".DB_PREFIXE."dossier_instruction_type |
| 77 |
|
|
ON dossier.dossier_instruction_type = dossier_instruction_type.dossier_instruction_type |
| 78 |
|
|
LEFT JOIN ".DB_PREFIXE."instructeur |
| 79 |
|
|
ON instructeur.instructeur = dossier.instructeur |
| 80 |
|
|
LEFT JOIN ".DB_PREFIXE."division as division_instructeur |
| 81 |
|
|
ON division_instructeur.division = instructeur.division |
| 82 |
|
|
LEFT JOIN ".DB_PREFIXE."division as division_dossier |
| 83 |
|
|
ON division_dossier.division = dossier.division |
| 84 |
|
|
LEFT JOIN ".DB_PREFIXE."direction as direction_instructeur |
| 85 |
|
|
ON direction_instructeur.direction = division_instructeur.direction |
| 86 |
|
|
LEFT JOIN ".DB_PREFIXE."direction as direction_dossier |
| 87 |
|
|
ON direction_dossier.direction = division_dossier.direction |
| 88 |
|
|
LEFT JOIN ".DB_PREFIXE."etat |
| 89 |
|
|
ON dossier.etat = etat.etat |
| 90 |
|
|
LEFT JOIN ".DB_PREFIXE."om_collectivite |
| 91 |
|
|
ON dossier.om_collectivite = om_collectivite.om_collectivite |
| 92 |
|
|
LEFT JOIN ( |
| 93 |
|
|
SELECT dossier, count(*) AS nb_instruction |
| 94 |
|
|
FROM ".DB_PREFIXE."instruction |
| 95 |
|
|
GROUP BY dossier |
| 96 |
|
|
) count_instruction ON count_instruction.dossier = dossier.dossier |
| 97 |
|
|
LEFT JOIN ( |
| 98 |
|
|
SELECT dossier, count(*) AS nb_consultation |
| 99 |
|
|
FROM ".DB_PREFIXE."consultation |
| 100 |
|
|
GROUP BY dossier |
| 101 |
|
|
) count_consultation ON count_consultation.dossier = dossier.dossier |
| 102 |
|
|
|
| 103 |
|
|
WHERE |
| 104 |
|
|
dossier.om_collectivite IN (<idx_collectivite>) |
| 105 |
|
|
AND dossier.date_depot >= '[date_depot_debut]' AND dossier.date_depot <= '[date_depot_fin]' |
| 106 |
|
|
AND dossier.date_decision >= '[date_decision_debut]' AND dossier.date_decision <= '[date_decision_fin]' |
| 107 |
|
|
|
| 108 |
|
|
ORDER BY [tri], dossier.dossier"; |
| 109 |
|
|
|
| 110 |
|
|
?> |
Parent Directory
| 
Revision Log