CALCUL UNE DIFFÉRENCE DE DATE EN FONCTION DE JOURS FÉRIÉS/TRAVAILLÉS ET DE PLAGE HORAIRE

--====================================================================--
--    Ensemble de fonctions de calculs de date avec jours fériés
--====================================================================--



-----------------------------------------------------------------------
-- FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE
-- calcule la différence en minutes entre deux dates
-- en fonction des horaires de l'ENTREPRISE : 
-- ici : 08h00 - 18h00
--
-- Déduit les nuits entre 18h00 et 08h00 le lendemain
-- ainsi que les week-ends et les jours fériés
-----------------------------------------------------------------------
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE')
	DROP FUNCTION dbo.FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE
GO
CREATE FUNCTION FN_DATEDIFF_SELON_HORAIRES_ENTREPRISE (@Date1 DATETIME, @Date2 DATETIME)
	RETURNS INT AS
	BEGIN
		DECLARE @DateCreCalcul DATETIME
		DECLARE @NbJourneesNonTravaillees INT
		DECLARE @NbNuits INT

		SET @DateCreCalcul = @Date1
		-- Si Date1 inférieure à l'heure d'ouverture
		IF DATEPART(hour, @Date1) < 8
		BEGIN
			SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @Date1) + 8, @Date1)
			SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
		END
		-- Si Date1 supérieure à l'heure de fermeture
		IF DATEPART(hour, @Date1) >= 18 BEGIN
			SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @Date1) + 18, @Date1)
			SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
		END
		-- Si Date1 est un dimanche, on ajoute 1 jour et on commence à l'heure d'ouverture
		IF DatePart(weekday, @Date1) = 7 BEGIN
			SET @DateCreCalcul = DATEADD(day, 1, @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @DateCreCalcul) + 8, @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
		END
		-- Si Date1 est un samedi, on ajoute 2 jours et on commence à l'heure d'ouverture
		IF DatePart(weekday, @Date1) = 6 BEGIN
			SET @DateCreCalcul = DATEADD(day, 2, @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(hour, - DATEPART(hour, @DateCreCalcul) + 8, @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(minute, - DATEPART(minute, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(second, - DATEPART(second, @DateCreCalcul), @DateCreCalcul)
			SET @DateCreCalcul = DATEADD(millisecond, - DATEPART(millisecond, @DateCreCalcul), @DateCreCalcul)
		END

		SET @NbJourneesNonTravaillees = 0	
		SET @NbNuits = DATEDIFF(day, @DateCreCalcul, @Date2)
		-- Si les 2 dates sont à des jours différents, on cherche le nombre de jours NON travaillés entre les 2 dates
			DECLARE @Compteur_NbNuits int;
			SET @Compteur_NbNuits = 0;
			
			-- Dans une boucle, on balaye tous les jours entre les 2 dates pour compter le nombre de jours non travaillés
			WHILE @Compteur_NbNuits < @NbNuits
			BEGIN
				SET @Compteur_NbNuits = @Compteur_NbNuits + 1;
				IF dbo.FN_JourTravailleFerie(DATEADD(day, @NbJourneesNonTravaillees + 1, @DateCreCalcul), 1) <> 'X'
				BEGIN 
					SET @NbJourneesNonTravaillees = @NbJourneesNonTravaillees + 1
				END
			END
		
		-- On calcule la différence en minutes et on retire :
		--  - la durée des des nuits en minutes
		--  - la durée des journées non travaillées en minutes
		RETURN datediff(minute, @DateCreCalcul, @Date2) - 840*@NbNuits - 600*@NbJourneesNonTravaillees
END
GO


-----------------------------------------------------------------------
-- Ensembles de fonctions qui déterminent si un jour est travaillé,
-- week-end ou férié
--
-- Entrée : date à tester,
--			Considérer le samedi comme un jour férié ?
--				Par défaut les samedi sont considérés comme fériés
--				0 : est considéré travaillé
--				1 : est considéré férié
--
-- Sortie :	chaine de caractère
--				X :	 jour travaillé
--				WE : Week-end
--				JF : Jour férié
--
-- Exemples d'appel de la fonction :
--			dbo.FN_JourTravailleFerie(getdate(), 1)
--			dbo.FN_JourTravailleFerie('21/04/2009', 1)
-----------------------------------------------------------------------


-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Mini' AND type = 'FN') DROP FUNCTION Mini
GO
CREATE FUNCTION Mini(@a float, @b float) returns float
AS
BEGIN
	DECLARE @f float
	if @a < @b 
		SET @f = @a
	ELSE
		SET @f = @b
	return @f
END
GO

-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Maxi' AND type = 'FN') DROP FUNCTION Maxi
GO
CREATE FUNCTION Maxi(@a float, @b float) returns float
AS
BEGIN
	DECLARE @f float
	IF @a > @b 
		SET @f = @a
	ELSE
		SET @f = @b
	return @f
END
GO

-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'IsCorrectDate' AND type = 'FN') DROP FUNCTION IsCorrectDate
GO
CREATE FUNCTION IsCorrectDate (@JJ integer, @MM integer, @AAAA integer) returns integer 
AS
BEGIN
	DECLARE @FlagReturn integer
    -- Hors plage
    IF @JJ < 1 Or @JJ > 31 Or @MM < 1 Or @MM > 12 
	  BEGIN
        SET @FlagReturn = 0
      END
    ELSE
    BEGIN
    -- Mois de 30 jours
    IF @JJ = 31 AND (@MM = 4 Or @MM = 6 Or @MM = 9 Or @MM = 11) 
      BEGIN
        SET @FlagReturn = 0
      END
    ELSE
    BEGIN
    -- Mois de février
     IF @MM = 2 
       BEGIN
         IF @JJ <= 28 
           BEGIN
             SET @FlagReturn = 1
           END
         ELSE
           BEGIN
             IF @JJ > 29 
               BEGIN
                 SET @FlagReturn = 0
               END
             ELSE  
               BEGIN
             	 IF Not (@JJ = 29 AND ((@AAAA % 4 = 0 AND @AAAA % 100 <> 0) Or @AAAA % 400 = 0))
             	   BEGIN
                     SET @FlagReturn = 0
                   END
                 ELSE
                   BEGIN
                     SET @FlagReturn = 1
                   END
               END
           END          
       END
       ELSE -- mois autre que fevrier
         BEGIN
         	SET @FlagReturn = 1
         END	
    END
    END
    Return @FlagReturn
END
GO

-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'ConvertDate' AND type = 'FN') DROP FUNCTION ConvertDate
GO
CREATE FUNCTION ConvertDate (@dtDate DateTime) returns varchar(4) 
AS
BEGIN
  DECLARE
  	@iJour integer,
  	@iMois integer,
  	@sJour varchar(2),
  	@sMois varchar(2),
  	@sResult varchar(4)

  SET @iJour = datepart(dd, @dtDate)
  SET @iMois = datepart(mm, @dtDate)

  SET @sJour = CAST(@iJour AS VARCHAR)
  SET @sMois = CAST(@iMois AS VARCHAR)

  IF len(@sJour) = 1 
    BEGIN
      SET @sJour = '0' + @sJour
    END
    
  IF len(@sMois) = 1 
    BEGIN
      SET @sMois = '0' + @sMois
    END
  
  SET @sResult = @sJour + @sMois

  RETURN @sResult
END
GO


-----------------------------------------------------------------------
-- ================================================================= --
-----------------------------------------------------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'FN_JourTravailleFerie' AND type = 'FN') DROP FUNCTION FN_JourTravailleFerie
GO
CREATE FUNCTION FN_JourTravailleFerie (@dtDate DateTime, @iSamediFerie integer = 1) returns varchar(2)
AS
BEGIN

-- Cette fonction indique si le jour passé en argument est férié.
-- Par défaut les samedi sont considérés comme fériés, mais le second
-- paramètre permet de changer cela.

DECLARE 
	@lgA bigint,
	@lgMPaq bigint,
	@lgJPaq bigint,
	@lgTmp1 bigint, 
	@lgTmp2 bigint, 
	@lgTmp3 bigint,
	@stDate varchar(10),
	@dtPaq DateTime,
	@stPaq varchar(10),
	@blFerie integer,
	@stType varchar(2)

 
SET @stType = 'X'
-- Dimanche (et samedi)

IF @iSamediFerie = 1 
  BEGIN
    IF DatePart(weekday, @dtDate) = 7 OR DatePart(weekday, @dtDate) = 6 
      BEGIN
        SET @blFerie = 1
      END
    ELSE
      BEGIN
        SET @blFerie = 0
      END
  END  
ELSE
  BEGIN
    IF DatePart(weekday, @dtDate) = 7 
      BEGIN
        SET @blFerie = 1
      END
    ELSE
      BEGIN
        SET @blFerie =0
      END
  END




IF @blFerie = 1 
  BEGIN
    SET @stType = 'WE'
  END
  
IF @blFerie = 0 
  BEGIN
    SET @stDate = dbo.ConvertDate(@dtDate)
    -- Jours fériés fixes (1er janvier, 1er mai, 8 mai, 14 juillet, ...)
    IF @stDate IN ('0101','0105','0805','1407','1508','0111','1111','2512')
      BEGIN
        SET @blFerie = 1
      END
  END
 
IF @blFerie = 0
  BEGIN
    SET @lgA = Year(@dtDate)
    SET @lgTmp1 = (19 * (@lgA % 19) + 24) % 30
    SET @lgTmp2 = dbo.Mini(@lgTmp1, dbo.Maxi(28, @lgTmp1) - 1)
    SET @lgTmp3 = 28 + @lgTmp2 - ((3 + @lgA - 1900 + ((@lgA - 1900) / 4) + @lgTmp2) % 7)
    SET @lgMPaq = 3 + (@lgTmp3 / 32)
	-- Remarque Le calcul du jour de pâques ne fonctionne pas toujours. L'arrondi est mauvais lorsqu'il y a une division : mettre un TRUNC(ladivision,0) pour garder la partie entière.
	-- http://www.sqlfr.com/codes/ORACLE-SQL-SERVER-2K-FONCTION-RETOURNANT-STATUS-OUVRE_27798.aspx
	-- SET @lgMPaq = 3 + round((@lgTmp3 / 32), 0)
    
    IF (@lgTmp3 < 32) 
    	BEGIN
    	  SET @lgJPaq = @lgTmp3 
    	END
    ELSE 
    	BEGIN
    	  SET @lgJPaq = @lgTmp3 - 31
    	END
    	
    -- Construction de la date du dimanche de Pâques
    SET @stPaq = CAST(@lgJPaq AS varchar) + '/' + CAST(@lgMPaq AS varchar) + '/' + CAST(@lgA AS varchar)
    SET @dtPaq = CAST (@stPaq AS DateTime)
    -- Jours fériés mobiles (lundi de pâques, ascension, lundi de pentecôte)
    -- Pâques et Pentecôte non testés, puisqu'ils tombent tous les deux un dimanche.
    
    IF (@dtDate = (@dtPaq + 1)) Or (@dtDate = (@dtPaq + 39)) Or (@dtDate = (@dtPaq + 50))
      BEGIN
        SET @blFerie = 1
      END
    
  END
 
IF @blFerie = 1 AND @stType = 'X'
  BEGIN
    SET @stType = 'JF'
  END 
 
 RETURN @stType  
 
END 
GO