1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| SELECT CONCAT(user_nom, user_prenom) as 'Délégué', maj_num_dos as "Dossier", maj_nom as "Nom", maj_prenom as "Prénom", maj_pts_logement as "Logement", maj_pts_personne as "Personne", maj_pts_gestion as "Gestion", maj_pts_temps as "Temps", maj_pts_new_dossier as "Nouveau", maj_pts_hors_norme as "Hors Norme", SUM(maj_pts_logement+maj_pts_personne+maj_pts_gestion+maj_pts_temps+maj_pts_new_dossier+maj_pts_hors_norme) as "Cumul des points", tb_type_mesure.libelle as "Mesure", tb_type_habitat.libelle as "Type Habitat"
FROM t_users_dossiers
LEFT OUTER JOIN t_users ON t_users_dossiers.id_user = t_users.id_user
LEFT OUTER JOIN maj_etat_civil ON t_users_dossiers.id_maj = maj_etat_civil.id_maj
LEFT OUTER JOIN maj_mesures ON maj_etat_civil.id_maj=maj_mesures.id_maj
LEFT OUTER JOIN tb_type_mesure ON maj_mesures.mesure_type_id=tb_type_mesure.id_mesure
LEFT OUTER JOIN maj_adresses ON t_users_dossiers.id_maj = maj_adresses.id_maj
LEFT OUTER JOIN tb_type_habitat ON maj_adresses.id_type_habitat=tb_type_habitat.id_habitat
WHERE user_fonction="2"
AND (CURDATE() BETWEEN gest_date_debut AND gest_date_fin
OR CURDATE()>=gest_date_debut AND gest_date_fin IS NULL)
AND t_users_dossiers.id_trt BETWEEN 1 AND 4
GROUP BY maj_etat_civil.id_maj
UNION ALL
SELECT CONCAT(user_nom, user_prenom) as 'Délégué', maj_num_dos as "Dossier", maj_nom as "Nom", maj_prenom as "Prénom", maj_pts_logement as "Logement", maj_pts_personne as "Personne", maj_pts_gestion as "Gestion", maj_pts_temps as "Temps", maj_pts_new_dossier as "Nouveau", maj_pts_hors_norme as "Hors Norme", SUM(maj_pts_logement+maj_pts_personne+maj_pts_gestion+maj_pts_temps+maj_pts_new_dossier+maj_pts_hors_norme) as "Cumul des points", tb_type_mesure.libelle as "Mesure", tb_type_habitat.libelle as "Type Habitat"
FROM t_users_dossiers
LEFT OUTER JOIN t_users ON t_users_dossiers.id_user = t_users.id_user
LEFT OUTER JOIN maj_etat_civil ON t_users_dossiers.id_maj = maj_etat_civil.id_maj
LEFT OUTER JOIN maj_mesures ON maj_etat_civil.id_maj=maj_mesures.id_maj
LEFT OUTER JOIN tb_type_mesure ON maj_mesures.mesure_type_id=tb_type_mesure.id_mesure
LEFT OUTER JOIN maj_adresses ON t_users_dossiers.id_maj = maj_adresses.id_maj
LEFT OUTER JOIN tb_type_habitat ON maj_adresses.id_type_habitat=tb_type_habitat.id_habitat
WHERE user_fonction="2"
AND (CURDATE() BETWEEN gest_date_debut AND gest_date_fin
OR CURDATE()>=gest_date_debut AND gest_date_fin IS NULL)
AND t_users_dossiers.id_trt BETWEEN 1 AND 4 |
Partager