[SQL Server] Envoyer Mail ou SMS aux membre d'un groupe AD

CREATE PROCEDURE [dbo].[usp_EnvoieMailSMS]

      @NOM_PROFIL SYSNAME,

      @NOM_GROUP_DESTINATAIRE AS VARCHAR(MAX),

      @OBJET_MAIL AS NVARCHAR(255),

      @CONTENU_MAIL  AS NVARCHAR(MAX)     

AS

/*

Auteur                       : KHARROUBI Zouhaier

Date Modification : 14 Juin 2010

Objectif                : Cette procédure permet l'envois des E-mails ou SMS vers un groupe d'utilisateur

Exemple                      : usp_EnvoisMailSMS 'EnvoiSMS','SMSGroupeAD_1','test envoi SMS', 'Bonjour, voici un test envoi'

                               usp_EnvoisMailSMS 'EnvoiSMS','SMSGroupeAD_2','test envoi SMS', 'Bonjour, voici un test envoi'

                               usp_EnvoisMailSMS 'EnvoiSMS','Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.','test envoi SMS', 'Bonjour, voici un test envoi'

*/

DECLARE @PathAD AS VARCHAR(MAX)

DECLARE @SQL      AS VARCHAR(MAX)

DECLARE @SQL_FINAL      AS VARCHAR(MAX)

DECLARE @NUM_PORT_ADR_MAIL   AS CHAR(25)

DECLARE @LIST_DESTINATAIRE   AS VARCHAR(MAX)

DECLARE @SEPARATEUR AS CHAR(25)

DECLARE @DEBUT_ADRESSE AS CHAR(4)

DECLARE @POSITION_AT AS TINYINT

SET @LIST_DESTINATAIRE=''

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#NUM_PORTABLE_ADR_MAIL]'))

DROP TABLE #NUM_PORTABLE_ADR_MAIL

CREATE TABLE #NUM_PORTABLE_ADR_MAIL

(

       NUM_PORT_ADR_MAIL CHAR(36)

      ,userAccountControl BIGINT

)

 

--Creation/Alimentation Table #ListActiveDirectoryGroups.

 

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListActiveDirectoryGroups]'))

DROP TABLE #ListActiveDirectoryGroups

   CREATETABLE #ListActiveDirectoryGroups

      (

      [NomGroup] [nchar](25) NULL,

      [ADsPath] [nvarchar](350) NULL

      )

 

SET @POSITION_AT = CHARINDEX('@',@NOM_GROUP_DESTINATAIRE,0)

IF @POSITION_AT=0

      --Le destinataire est un groupe ActiveDirectory

      BEGIN

            SET @SQL ='INSERT INTO #ListActiveDirectoryGroups (NomGroup,ADsPath) VALUES ('''+ @NOM_GROUP_DESTINATAIRE +''''

            SET @SQL = @SQL + ',''LDAP://cn=' + @NOM_GROUP_DESTINATAIRE + ',cn=Users,dc=corp,dc=kharroubi,dc=com'')'

            EXECUTE (@SQL )                        

            SELECT

                  @PathAD = REPLACE(ADsPath, 'LDAP://', '')

            FROM

                  #ListActiveDirectoryGroups

            WHERE

                  NomGroup =@NOM_GROUP_DESTINATAIRE

            IF @NOM_PROFIL  = 'EnvoiSMS'

                  BEGIN

                        --Envoyer des SMS

                        IF @NOM_GROUP_DESTINATAIRE='SMSGroupeAD_1'

                             BEGIN

                     --Pour les membres de ce groupe remplacer + par 00

 SET @SQL = 'INSERT INTO #NUM_PORTABLE_ADR_MAIL SELECT REPLACE(USR.MOBILE,''+'',''00''),USR.userAccountControl FROM OpenQuery(SERVEUR_AD, '

                             END

                        ELSE

                             BEGIN

                                   SET @SQL = 'INSERT INTO #NUM_PORTABLE_ADR_MAIL SELECT USR.MOBILE,USR.userAccountControl FROM OpenQuery(SERVEUR_AD, '

                             END

                        SET @SEPARATEUR = '@kharroubi.activmail.net;'

                        SET @DEBUT_ADRESSE = 'SMS.'

                  END

            ELSE

                  BEGIN

                        --Envoyer des Mails

                        SET @SQL = 'INSERT INTO #NUM_PORTABLE_ADR_MAIL SELECT USR.MAIL,USR.userAccountControl FROM OpenQuery(SERVEUR_AD, '

                        SET @SEPARATEUR = ';'

                        SET @DEBUT_ADRESSE = ''

                  END

            SET @SQL_FINAL = @SQL +'''SELECT sAMAccountName, Adspath '

            SET @SQL_FINAL = @SQL_FINAL +'FROM ''''LDAP://DC=corp,DC=kharroubi,DC=com'''' '

            SET @SQL_FINAL = @SQL_FINAL +'where memberOf = '''''+ @PathAD +''''''') AD '

            SET @SQL_FINAL = @SQL_FINAL + 'INNER JOIN ( SELECT ADsPath AS ADsPath,'

            SET @SQL_FINAL = @SQL_FINAL + 'title AS TITRE, givenName AS PRENOM,'

            SET @SQL_FINAL = @SQL_FINAL + 'sn AS NOM, displayName,'

            SET @SQL_FINAL = @SQL_FINAL + 'sAMAccountName AS LOGIN, telephoneNumber AS TELEPHONE,'

            SET @SQL_FINAL = @SQL_FINAL + 'facsimileTelephoneNumber    AS FAX, mobile AS MOBILE, mail AS MAIL,userAccountControl '

            SET @SQL_FINAL = @SQL_FINAL + 'FROM OpenQuery(SERVEUR_AD,'

            SET @SQL_FINAL = @SQL_FINAL +'''SELECT ADsPath, title, displayName, sAMAccountName,'

            SET @SQL_FINAL = @SQL_FINAL +'givenName, telephoneNumber, facsimileTelephoneNumber, sn, mobile, mail,userAccountControl '

            SET @SQL_FINAL = @SQL_FINAL +'FROM ''''LDAP://DC=corp,DC=kharroubi,DC=com'''' '

            SET @SQL_FINAL = @SQL_FINAL +'where objectClass = ''''User'''' AND objectCategory = ''''Person'''''')) USR'

            SET @SQL_FINAL = @SQL_FINAL +' ON AD.Adspath = USR.ADsPath'   

            EXECUTE (@SQL_FINAL)

            --supprimer les NUM_PORTABLE_ADR_MAIL des comptes désactivés

      DELETE FROM  #NUM_PORTABLE_ADR_MAIL WHERE userAccountControl & 2 = 2    

SELECT @LIST_DESTINATAIRE = RTRIM(LTRIM(@LIST_DESTINATAIRE)) + RTRIM(LTRIM(@DEBUT_ADRESSE)) + RTRIM(LTRIM(NUM_PORT_ADR_MAIL)) + RTRIM(LTRIM(@SEPARATEUR))

            FROM(      

            SELECT DISTINCT * FROM #NUM_PORTABLE_ADR_MAIL

            ) T

            WHERE NUM_PORT_ADR_MAIL IS NOT NULL

            SELECT @LIST_DESTINATAIRE=SUBSTRING(@LIST_DESTINATAIRE,1,LEN(@LIST_DESTINATAIRE)-1)         END

ELSE

      --Le destinataire est une liste des adresses ou des numéro

      BEGIN

            SELECT @LIST_DESTINATAIRE=@NOM_GROUP_DESTINATAIRE         

      END

DROP TABLE #NUM_PORTABLE_ADR_MAIL

DROP TABLE #ListActiveDirectoryGroups

EXEC msdb..sp_send_dbmail@profile_name=@NOM_PROFIL, @recipients=@LIST_DESTINATAIRE, @subject=@OBJET_MAIL,@body=@CONTENU_MAIL

Ajouter un Commentaire


Code de sécurité
Rafraîchir