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 |
Partager