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
| CREATE FUNCTION calcul_delai
(
-- Add the parameters for the function here
@DHDebut DateTime,
@Delai Int
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @DateFin DateTime
DECLARE @JourSuiv BIT
SET @DateFin = @DHDebut
-- Foncton
-- Placement de l'horaire dans la journée
IF DATEPART(hour, @DHDebut) < 9
SET @DateFin = DATEADD(hour, -DATEPART(hour,@DHDebut)+9, DateFin)
ELSE IF DATEPART(hour, @DHDebut) > 18
BEGIN
SET @DateFin = DATEADD(hour, -DATEPART(hour,@DHDebut)+9, DateFin)
WHILE --APPEL A JOUR_SUIVANT= false
BEGIN
SET @DateFin = DATEADD(day, DATEPART(day,@DHDebut)+1, DateFin)
-- APPEL A JOUR_SUIVANT
END
END
ELSE IF ((DATEPART(hour, @DHDebut) > 12) & (DATEPART(hour, @DHDebut) < 14))
SET @DateFin = DATEADD(hour, -DATEPART(hour,@DHDebut)+14, DateFin)
-- Calcul de la première journée si même jour que la demande
IF (DATEPART(hour, @DateFin) >= 9 & DATEPART(hour, @DateFin) <= 12)
BEGIN
SET @Delai = @Delai - (12 - DATEPART(hour,@DateFin))
SET @DateFin = DATEADD(hour, -DATEPART(hour,@DateFin)+12, DateFin)
IF @Delai != 0 & @Delai >= 4
BEGIN
SET @Delai = @Delai - (18 - DATEPART(hour,@DateFin))
SET @DateFin = DATEADD(hour, -DATEPART(hour,@DateFin)+18, DateFin)
END
END
ELSE IF (DATEPART(hour, @DateFin) >= 14 & DATEPART(hour, @DateFin) <= 19)
BEGIN
SET @Delai = @Delai - (18 - DATEPART(hour,@DateFin))
SET @DateFin = DATEADD(hour, -DATEPART(hour,@DateFin)+18, DateFin)
END
WHILE @Delai != 0
IF -- APPEL A JOUR_SUIVANT = true
SET @DateFin = DATEADD(day, DATEPART(day,@DateFin)+1, DateFin)
SET @DateFin = DATEADD(hour, -DATEPART(hour,@DateFin)+9, DateFin)
IF @Delai <= 3
BEGIN
SET @DateFin = DATEADD(hour, @Delai, DateFin)
SET @Delai = 0
BREAK
END
ELSE IF @Delai <= 7
BEGIN
SET @DateFin = DATEADD(hour, @Delai + 2, DateFin) -- @Delai+2 pour la pause du midi
SET @Delai = 0
BREAK
END
ELSE
BEGIN
SET @DateFin = DATEADD(hour, 9, DateFin) -- 7h + 2h de pause
SET @Delai = @Delai - 7
CONTINUE
END
ELSE
BEGIN
SET @DateFin = DATEADD(day, DATEPART(day,@DateFin)+1, DateFin)
SET @DateFin = DATEADD(hour, -DATEPART(hour,@DateFin)+9, DateFin)
CONTINUE
END
-- Return the result of the function
RETURN @DateFin
END
GO |
Partager