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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
|
-- les journées type "ordinaires" sur la période considérée
SELECT DISTINCT 0 AS JRN_TYPE,
ATR_NOM, ATR.ATR_ID,
PJR.PJR_DATE,
JRT_LIBELLE, JRT_DEBUTE, JRT_TERMINE,
CASE
WHEN JTS_UTILISE = 0 THEN 0.0
ELSE ITJ_DEBUT
END AS ITJ_DEBUT,
CASE
WHEN JTS_UTILISE = 0 THEN 24.0
ELSE ITJ_FIN
END AS ITJ_FIN,
CASE
WHEN JTS_UTILISE = 0 THEN 0
ELSE ITJ_CALC_SERVICE
END AS ITJ_CALC_SERVICE
FROM T_ACTEUR_ATR ATR
INNER JOIN TJ_CALENDRIER_PERSONNEL_CAP CAP
ON ATR.ATR_ID = CAP.ATR_ID
INNER JOIN T_JOURNEE_TYPE_JRT JRT
ON CAP.JRT_ID = JRT.JRT_ID
INNER JOIN TJ_PROJECTION_JTS JTS
ON JRT.JRT_ID = JTS.JRT_ID
INNER JOIN T_INTERVALLE_JOURNEE_ITJ ITJ
ON JRT.JRT_ID = ITJ.JRT_ID
-- projection sur l'ensemble du calendrier
INNER JOIN T_PLN_JOUR_PJR PJR
-- limite à la fenêtre de visualisation pour la projection
ON PJR_DATE BETWEEN COALESCE(JRT_DEBUTE, @DATE_DEBUT)
AND COALESCE(JRT_TERMINE, @DATE_FIN )
-- ne pas prendre en compte les journées anormales
WHERE JRT.JRT_ID NOT IN (SELECT JRT_ID
FROM TJ_VALIDE_JTJ)
-- élimination des dates des journées anormales pour les acteurs
AND NOT EXISTS (SELECT *
FROM TJ_CALENDRIER_PERSONNEL_CAP CAP1
INNER JOIN T_JOURNEE_TYPE_JRT JRT1
ON CAP1.JRT_ID = JRT1.JRT_ID
INNER JOIN TJ_VALIDE_JTJ JTJ1
ON JRT1.JRT_ID = JTJ1.JRT_ID
WHERE CAP1.ATR_ID = CAP.ATR_ID
AND JTJ1.PJR_DATE = PJR.PJR_DATE)
-- refiltrage sur la fenêtre de temps
AND PJR_DATE BETWEEN @DATE_DEBUT
AND @DATE_FIN
-- correspondance avec jour de semaine sur projection date
AND JTS.PJS_ID = PJR.PJS_ID
UNION
-- les journées "particulières" sur la période considérée
SELECT DISTINCT 1 AS JRN_TYPE,
ATR_NOM, ATR.ATR_ID,
PJR.PJR_DATE, JRT_LIBELLE,
JRT_DEBUTE, JRT_TERMINE,
ITJ_DEBUT, ITJ_FIN,
ITJ_CALC_SERVICE
FROM T_ACTEUR_ATR ATR
INNER JOIN TJ_CALENDRIER_PERSONNEL_CAP CAP
ON ATR.ATR_ID = CAP.ATR_ID
INNER JOIN T_JOURNEE_TYPE_JRT JRT
ON CAP.JRT_ID = JRT.JRT_ID
INNER JOIN TJ_VALIDE_JTJ JTJ
ON JRT.JRT_ID = JTJ.JRT_ID
INNER JOIN T_INTERVALLE_JOURNEE_ITJ ITJ
ON JRT.JRT_ID = ITJ.JRT_ID
-- projection sur les dates spécifiques du calendrier
INNER JOIN T_PLN_JOUR_PJR PJR
-- limite à la fenêtre de visualisation pour la projection
ON JTJ.PJR_DATE = PJR.PJR_DATE
AND PJR.PJR_DATE BETWEEN COALESCE(JRT_DEBUTE, @DATE_DEBUT )
AND COALESCE(JRT_TERMINE, @DATE_FIN )
UNION
-- le jour du début
SELECT -1 AS JRN_TYPE, ATR_NOM, ATR_PRENOM, ATR.ATR_ID,
PJR_DATE, SOO_LIBELLE, NULL AS JRT_DEBUTE, NULL AS JRT_TERMINE,
dbo.FN_DATETIME_AS_HD2(SOO_DH_DEBUT) AS ITJ_DEBUT,
CASE
WHEN dbo.FN_DATETIME_AS_DATE(SOO_DH_DEBUT) = dbo.FN_DATETIME_AS_DATE(SOO_DH_FIN)
THEN dbo.FN_DATETIME_AS_HD2(SOO_DH_FIN)
ELSE 24.0
END AS ITJ_FIN,
2 AS ITJ_CALC_SERVICE
FROM T_SEQUENCE_OT_OEM_SOO SOO
INNER JOIN T_PLN_JOUR_PJR PJRD
ON dbo.FN_DATETIME_AS_DATE(SOO_DH_DEBUT) = PJRD.PJR_DATE
INNER JOIN T_ACTEUR_ATR ATR
ON SOO.ATR_ID = ATR.ATR_ID
WHERE PJR_DATE = dbo.FN_DATETIME_AS_DATE(SOO_DH_DEBUT)
UNION ALL
-- toutes les dates intermédiaires
SELECT -1 AS JRN_TYPE, ATR_NOM, ATR_PRENOM, ATR.ATR_ID,
PJR_DATE, SOO_LIBELLE, NULL AS JRT_DEBUTE, NULL AS JRT_TERMINE,
0 AS ITJ_DEBUT, 24 AS ITJ_FIN,
2 AS ITJ_CALC_SERVICE
FROM T_SEQUENCE_OT_OEM_SOO SOO
INNER JOIN T_PLN_JOUR_PJR PJRD
ON dbo.FN_DATETIME_AS_DATE(SOO_DH_DEBUT) < PJRD.PJR_DATE
AND dbo.FN_DATETIME_AS_DATE(SOO_DH_FIN) > PJRD.PJR_DATE
INNER JOIN T_ACTEUR_ATR ATR
ON SOO.ATR_ID = ATR.ATR_ID
UNION ALL
-- le jour de fin s'il y en a un
SELECT -1 AS JRN_TYPE, ATR_NOM, ATR_PRENOM, ATR.ATR_ID,
PJR_DATE, SOO_LIBELLE, NULL AS JRT_DEBUTE, NULL AS JRT_TERMINE,
0 AS ITJ_DEBUT,
dbo.FN_DATETIME_AS_HD2(SOO_DH_FIN) AS ITJ_FIN,
2 AS ITJ_CALC_SERVICE
FROM T_SEQUENCE_OT_OEM_SOO SOO
INNER JOIN T_PLN_JOUR_PJR PJRF
ON dbo.FN_DATETIME_AS_DATE(SOO_DH_FIN) = PJRF.PJR_DATE
INNER JOIN T_ACTEUR_ATR ATR
ON SOO.ATR_ID = ATR.ATR_ID
WHERE dbo.FN_DATETIME_AS_DATE(SOO_DH_DEBUT) <> dbo.FN_DATETIME_AS_DATE(SOO_DH_FIN)
AND PJR_DATE = dbo.FN_DATETIME_AS_DATE(SOO_DH_FIN)
ORDER BY ATR.ATR_ID, PJR_DATE, ITJ_DEBUT, ITJ_FIN |
Partager