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
|
CREATE FUNCTION dbo.OpenAgendaBis
(
@DateDebut datetime,
@DateFin datetime,
@Intervenant Varchar(50)
)
RETURNS char(8000)
AS
BEGIN
DECLARE @Ret char(8000);
DECLARE Cur CURSOR FOR
SELECT DetailAgenda.[Date], DetailAgenda.NoRDV, Agenda.NoTypeRDV, Agenda.Annotation, Agenda.Ville, Agenda.Client, Agenda.NoCli, Client.NomSociété
FROM DetailAgenda INNER JOIN
DetailAgendaHeure ON DetailAgenda.NoDetailAgenda = DetailAgendaHeure.NoDetailAgendaDate INNER JOIN
Agenda ON DetailAgenda.NoRDV = Agenda.NoRDV INNER JOIN
DetailRDVIntervenant ON Agenda.NoRDV = DetailRDVIntervenant.NoRDV INNER JOIN
Client ON Agenda.NoCli = Client.NoCli
WHERE (DetailAgenda.[Date] BETWEEN @DateDebut AND @DateFin ) AND (DetailRDVIntervenant.NoIntervenant IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@Intervenant,',')))
GROUP BY DetailAgenda.NoRDV, DetailAgenda.[Date], Agenda.NoTypeRDV, Agenda.Annotation, Agenda.Ville, Agenda.Client, Agenda.NoCli, Client.NomSociété
DECLARE @Date DateTime;
DECLARE @NoRDV int;
DECLARE @NoTypeRDV int;
DECLARE @Annotation char(128);
DECLARE @Ville char(30);
DECLARE @Client char(30);
DECLARE @NoCli int;
DECLARE @NomSociete char(30);
DECLARE @NoInter as int;
DECLARE @MinHeure char(30);
DECLARE @MaxHeure char(30);
DECLARE @Heure char(30);
Set @Ret=""
OPEN Cur
FETCH NEXT FROM Cur INTO @Date,@NoRDV,@NoTypeRDV,@Annotation,@Ville,@Client,@NoCli, @NomSociete
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- SET @Annotation=""
-- SET @Ville=""
if @Ville is null
set @Ville=""
if @Annotation is null
set @Annotation=""
SET @Ret=@Ret + CONVERT(Varchar(10), @Date,103) + "|" + RTRIM(CAST(@NoRDV AS Char(10))) + "|" + RTRIM(CAST(@NoTypeRDV AS Char(10))) + "|" + RTRIM(@Annotation) + "|" + RTRIM(@Ville) + "|" + RTRIM(CAST(@NoCli AS Char(10))) + "|" + RTRIM(@NomSociete) + "["
DECLARE CurInter CURSOR FOR SELECT NoIntervenant FROM DetailRDVIntervenant WHERE DetailRDVIntervenant.NoRDV=@NoRDV
OPEN CurInter
FETCH NEXT FROM CurInter Into @NoInter
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Ret = @Ret + RTRIM(CAST(@NoInter AS Char(10))) + "|"
FETCH NEXT FROM CurInter Into @NoInter
END
CLOSE CurInter
DEALLOCATE CurInter
SET @Ret = @Ret + "["
-- SELECT @MinHeure=MIN(Heure) , @MaxHeure=MAX(Heure) FROM DetailAgendaHeure, DetailAgenda WHERE NoDetailAgendaDate=DetailAgenda.NoDetailAgenda AND DetailAgenda.NoRDV=@NoRDV
DECLARE CurHeure CURSOR FOR SELECT Heure FROM DetailAgendaHeure, DetailAgenda WHERE NoDetailAgendaDate=DetailAgenda.NoDetailAgenda AND DetailAgenda.NoRDV=@NoRDV ORDER BY Heure
OPEN CurHeure
FETCH NEXT FROM CurHeure Into @Heure
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Ret = @Ret + RTRIM(@Heure) + "|"
FETCH NEXT FROM CurHeure Into @Heure
END
CLOSE CurHeure
DEALLOCATE CurHeure
-- SET @Ret = @Ret +"[" + RTRIM(@MinHeure) + "|" + RTRIM(@MaxHeure) + "{"
SET @Ret = @Ret + "{"
FETCH NEXT FROM Cur INTO @Date,@NoRDV,@NoTypeRDV,@Annotation,@Ville,@Client,@NoCli, @NomSociete
END
CLOSE Cur
DEALLOCATE Cur;
RETURN (@Ret)
END |
