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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
| USE [wBypass]
GO
/****** Object: StoredProcedure [dbo].[GraphAlarmeByPupitreHebdo] Script Date: 07/03/2019 10:19:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <ES>
-- Create date: <201902>
-- Description: <Graphique des alarmes hebdomadaires>
-- =============================================
ALTER PROCEDURE [dbo].[GraphAlarmeByPupitreHebdo]
@date datetime,
@groupe int
AS
BEGIN
declare
@dateHeure nvarchar(10),
@DateStartWeek nvarchar(10),
@DateEndWeek nvarchar(10);
--@strSQL nvarchar(255);
create table #dateWeek (
dateHeure nvarchar(10) NOT NULL,
dateStart nvarchar(10) NOT NULL,
dateEnd nvarchar(10) NOT NULL,
);
INSERT INTO #dateWeek
EXEC [dbo].[GetFirstDayAndLastDayOfWeek] @date ;
CREATE TABLE #tempRapport (
pupitre NCHAR(10) NULL,
unite NVARCHAR(255) NULL,
NameDay NCHAR(10) NULL,
NbOccurence INT NULL,
OrdreDay INT NULL
);
CREATE TABLE #Temp (
pupitre NCHAR(10) NULL,
unite NVARCHAR(255) NULL,
DayNameWeek NCHAR(10) NULL,
NumDayWeek INT NULL,
NbOccurences INT NULL,
)
DECLARE db_cursor CURSOR FOR
SELECT * from #dateWeek
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @dateHeure,@DateStartWeek,@DateEndWeek
WHILE @@FETCH_STATUS = 0
BEGIN
with temp as (
select
chg.idCh
, unit.Unité as Unité
, unite.Pupitre
, DATEPART(hour, chg.DateHeure) AS Heure
, DATENAME(DW, chg.DateHeure) as DayNameWeek
, DATEPART(weekday, chg.DateHeure) as NumDayWeek
FROM [taChangements] as chg
INNER JOIN [dbo].[taUnit] unit ON chg.[idUni-idCh] = unit.[idUnit]
INNER JOIN [dbo].[taUnités] unite ON unit.[idUU] = unite.[idUnité]
INNER JOIN [dbo].[taGroupes] grp ON unite.[idGrp-idUnité] = grp.idGrp
WHERE (action ='') AND
(cast(convert(varchar(50),chg.DateHeure,103) + ' ' + convert(varchar(50),chg.DateHeure,114) as datetime ) >= cast(@DateStartWeek + ' ' + '00:00:00' as datetime) AND
cast(convert(varchar(50),chg.DateHeure,103) + ' ' + convert(varchar(50),chg.DateHeure,114) as datetime ) <= cast(@DateEndWeek + ' ' + '23:59:59' as datetime))
AND grp.IdGrp = @groupe
GROUP BY
unit.Unité,
unite.Pupitre,
chg.idCh ,
DATEPART(hour,chg.DateHeure), DATENAME(DW, chg.DateHeure),DATEPART(weekday, chg.DateHeure)
)
, temp2 as (
select t.Pupitre, t.Unité, t.DayNameWeek,t.NumDayWeek, count(*) NbVal
from temp t
group by t.Pupitre, t.Unité,t.DayNameWeek,t.NumDayWeek
), temp3 as (
select distinct t2.Pupitre, t2.Unité
from temp2 t2
), temp4 as (
select t3.Pupitre, t3.Unité, LEFT((SELECT ISNULL(Convert(varchar(15),tbis.Unité), '') + '/' AS [text()]
FROM temp3 tbis
WHERE (tbis.Pupitre = t3.Pupitre) order by tbis.Unité FOR XML PATH ('')
)
,LEN(
(SELECT ISNULL(Convert(varchar(15),tbis.Unité), '') + '/' AS [text()]
FROM temp3 tbis
WHERE (tbis.Pupitre = t3.Pupitre) order by tbis.Unité FOR XML PATH ('')
)
)-1) Unite
from temp3 t3
)
INSERT INTO #Temp
select t4.Pupitre, t4.Unite, t2.DayNameWeek,t2.NumDayWeek, sum(t2.NbVal) NbOccurences
from temp4 t4
inner join temp2 t2 on t4.Unité=t2.Unité and t4.Pupitre=t4.Pupitre
group by t4.Pupitre, t4.Unite, t2.DayNameWeek,t2.NumDayWeek
INSERT INTO #tempRapport (pupitre,unite,[NameDay],OrdreDay, [NbOccurence])
SELECT
*
FROM #temp
FETCH NEXT FROM db_cursor
INTO @dateHeure, @DateStartWeek, @DateEndWeek
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
declare @i INT
declare @jour VARCHAR(10)
set @i = 1
WHILE @I <= 7
BEGIN
IF NOT EXISTS (select * from #tempRapport WHERE OrdreDay=@i)
BEGIN
if @i=1
BEGIN
set @jour ='lundi'
END
if @i=2
BEGIN
set @jour ='mardi'
END
if @i=3
BEGIN
set @jour ='mercredi'
END
if @i=4
BEGIN
set @jour ='jeudi'
END
if @i=5
BEGIN
set @jour ='vendredi'
END
if @i=6
BEGIN
set @jour ='samedi'
END
if @i=7
BEGIN
set @jour ='dimanche'
END
INSERT INTO #tempRapport (pupitre,unite, NameDay,NbOccurence,OrdreDay) VALUES ('N/A','N/A',@jour,0,@i)
END
SET @I = @I + 1
END
select pupitre, unite, NameDay, NbOccurence, OrdreDay from #tempRapport
order by OrdreDay
drop table #dateWeek
drop table #tempRapport
drop table #Temp
END |
Fenêtre "Définir les paramètres" qui s'affiche
Répondre avec citation
Partager