Monday, June 2, 2014

sql server send mail ftored Procedure + trigger

send mail stored
-----------------------------
USE [HES_Intranet]
GO
/****** Object:  StoredProcedure [dbo].[EmpWorkFlowSendEmail]    Script Date: 06/03/2014 05:45:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE[dbo].[EmpWorkFlowSendEmail]
@ISEMPMANGER AS BIT =0,
@EMAIL_TYPE AS INT =1 ,
@EMAIL_LANG   AS INT = 1 ,
 @REQ_ID INT  = NULL ,
 @REQUEST_DATE NVARCHAR(50) = N'',
 @WORK_FLOW_NAME NVARCHAR(100) = N'' ,
 @STAGE_NAME_CURRENT NVARCHAR(250) = N'' ,
 @STATUS_NAME_CURRENT NVARCHAR(250)  = N'' ,
 @REQUEST_OWNER_USER_FULL_NAME NVARCHAR(250)  = N'' ,
 @STAGE_NAME_NEXT NVARCHAR(250) = N'' ,
 @WORK_FLOW_TYPE_NAME NVARCHAR(1000) = N''  ,
 @USER_MAIL VARCHAR(200),
 @USER_FULL_NAME NVARCHAR(500) = N'' ,
 @NEXT_STAGE_USERS NVARCHAR(MAX) = N'' ,
 @REQUEST_DETAILS NVARCHAR(MAX) = N''
AS
BEGIN
--PRINT N'@WORK_FLOW_NAME = ' + RTRIM(CAST(@WORK_FLOW_NAME AS NVARCHAR(3000))) + N'.';
-- PRINT N'BODY = ' + RTRIM(CAST(@WORK_FLOW_TYPE_NAME AS NVARCHAR(3000))) + N'.';

SET NOCOUNT ON;
----------------------------------------------------------------------------------------------------------
------------------DEFINITION OF VARIABLES-----------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
DECLARE @EMAILSUBJECT AS  NVARCHAR(MAX)
DECLARE @EMAILBODY AS  NVARCHAR(MAX)
DECLARE @MYLINKUSER AS  NVARCHAR(MAX) =N''
DECLARE @MYLINKMANAGER AS  NVARCHAR(MAX)
DECLARE @MNAGERTRVISIBLE AS  NVARCHAR(50)
DECLARE @ISMANGER AS INT
DECLARE @MANGERDISCRIPTIN AS NVARCHAR(20)
DECLARE @INFO AS  NVARCHAR(MAX)
DECLARE @CURRENTMESSAGE AS NVARCHAR(200)
DECLARE @HLEVEL AS INT
SET @EMAILSUBJECT=@WORK_FLOW_NAME + ' - ' + @WORK_FLOW_TYPE_NAME
SET @MYLINKMANAGER='';
SELECT @EMAILBODY= T.MESSAGEBODY  FROM  WORKFLOW_MAILTEMPLATES T
WHERE
[TYPE]=@EMAIL_TYPE
AND T.LANGUAGE_ID =@EMAIL_LANG
----------------------------------------------------------------------------------------------------------
IF (@ISMANGER=0)
SET @MANGERDISCRIPTIN =N' - '+ N'المكلف'
ELSE
SET @MANGERDISCRIPTIN = N' '
----------------------------------------------------------------------------------------------------------
--------------------------------FILL MESSAGE CONTENT------------------------------------------------------
----------------------------------------------------------------------------------------------------------

 --SET @MYLINK='HTTP://INTRANET.MOWE.GOV.SA/WEB/ADMIN/EMPWORKFLOW/REPORT/REPORT.ASPX?ORDERGID='+ CONVERT(NVARCHAR(50) , @ORDER_GID ) +'&REPORTTYPE='+CONVERT(NVARCHAR(50) , @ORDERTYPEID)+'&EMPID='+CONVERT(NVARCHAR(10) , @EMPID_SUBMITTED );
     --  SET @MYLINKUSER='HTTP://INTRANET.MOWE.GOV.SA/WEB/ADMIN/EMPWORKFLOW/REPORT/REPORT.ASPX?ORDERGID='+ CONVERT(NVARCHAR(50) , @ORDER_GID ) +'&REPORTTYPE='+CONVERT(NVARCHAR(50) , @ORDERTYPEID)+'&EMPID='+CONVERT(NVARCHAR(10) , @EMPID_SUBMITTED );
 SET @MYLINKUSER='';
   --SET @EMAILBODY= REPLACE(@EMAILBODY,'#EMAILSUBJECT#' ,@EMAILSUBJECT);
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#CREATDATE#' ,@CREATEDATE);
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#NOTE#' ,@CURRENTMESSAGE);
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#EMPNAME#' ,(@EMPNAME_SUBMITTED+' '+@SECTIONNAMESUBMITTED));
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#INFO#' ,@INFO);
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#LINK#' ,@MYLINK);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,'ID="MANGERTITELNAME" STYLE="VISIBILITY: VISIBLE"' ,'ID="MANGERTITELNAME" STYLE="VISIBILITY: HIDDEN"');
----------------------------------------------------------------------------------------------------------
-------------------------------SEND EMAIL TO SUBMITTED EMPLOYEE-------------------------------------------
----------------------------------------------------------------------------------------------------------
-- IF(@ISEMPMANGER=0)
-- BEGIN
----------------------------------------------------------------------------------------------------------
-------------------------------SEND EMAIL TO SUBMITTED EMPLOYEE-------------------------------------------
----------------------------------------------------------------------------------------------------------
SET @EMAILSUBJECT=N'' + @WORK_FLOW_NAME + N'  -  '  + N'' + @WORK_FLOW_TYPE_NAME
SET @EMAILBODY= REPLACE(@EMAILBODY,'#link#',@MYLINKUSER);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Emailsubject#'),N'' +@EMAILSUBJECT);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#EMPNAME#'),N'' +@REQUEST_OWNER_USER_FULL_NAME);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#CreatDate#'),N'' +@REQUEST_DATE);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Note#'),N'' +@STATUS_NAME_CURRENT);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Current_stage#'), N'' +@STAGE_NAME_CURRENT);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Next_stage#'), N'' +@STAGE_NAME_NEXT);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Next_Satge_users#'), N'' +@NEXT_STAGE_USERS);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Notified_User_Name#'), N'' +@USER_FULL_NAME);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#info#'), N'' +@REQUEST_DETAILS);
--SET @EMAILBODY=N'هللو'
PRINT N'BODY = ' + RTRIM(CAST(@EMAILBODY AS NVARCHAR(MAX))) + N'.';
PRINT N'SUBJECT  = ' + RTRIM(CAST(@EMAILSUBJECT AS NVARCHAR(3000))) + N'.' ;
EXEC [MSDB].[DBO].SP_SEND_DBMAIL @PROFILE_NAME='HOSPITALMAIL',
@RECIPIENTS=@USER_MAIL,
-- @RECIPIENTS='ENGMOSBAH11@GMAIL.COM' ,
@SUBJECT=@EMAILSUBJECT ,
@BLIND_COPY_RECIPIENTS='ELSAYED.IBRAHIME@GMAIL.COM',
@copy_recipients ='ENG_MOSBAH11@HOTMAIL.COM',
@BODY=@EMAILBODY,
@BODY_FORMAT = 'HTML'

--DECLARE @C1 CURSOR
-- DECLARE @C1_USER_ID INT = NULL
--  DECLARE @C1_USER_FULL_NAME NVARCHAR(1000) = NULL
--  DECLARE @C1_USER_FULL_NAME_EN NVARCHAR(1000) = NULL
--  DECLARE @C1_USER_MAIL NVARCHAR(250) = NULL
-- SET @C1 = CURSOR FORWARD_ONLY STATIC FOR
-- SELECT  USER_ID  ,USER_FULL_NAME , USER_FULL_NAME_EN ,USER_EMAIL
-- FROM @TBL_USERS
-- OPEN @C1
-- FETCH NEXT
-- FROM @C1 INTO @C1_USER_ID ,@C1_USER_FULL_NAME ,@C1_USER_FULL_NAME_EN , @C1_USER_MAIL
-- WHILE (@@FETCH_STATUS = 0)
--  BEGIN

-- FETCH NEXT FROM @C1 INTO @C1_USER_ID ,@C1_USER_FULL_NAME ,@C1_USER_FULL_NAME_EN , @C1_USER_MAIL
--  END
--  CLOSE @C1
--  DEALLOCATE @C1
----------------------------------------------------------------------------------------------------------
-- END
-- ELSE
-- BEGIN
------------------------------------------------------------------------------------------------------------
----------------------------------SEND EMAIL TO MANGER -----------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- IF NOT(@HLEVEL=998 OR @HLEVEL=999 )
-- BEGIN
-- SET @EMAILSUBJECT=@EMAILSUBJECT + N' - ' + @WORK_FLOW_NAME + N'-' + @WORK_FLOW_TYPE_NAME
-- SET @EMAILBODY= REPLACE(@EMAILBODY,'#LINK#',@MYLINKMANAGER);
-- --SET @EMAILBODY= REPLACE(@EMAILBODY,'#MANGERTITELNAME#' ,(N'<TR  CLASS="VIEW" ><TD  ALIGN="RIGHT" BGCOLOR="#FFFFCC" CLASS="STYLE3">' +@MANGERJOBTITEL + @MANGERDISCRIPTIN+ N'</TD><TD   ALIGN="RIGHT" CLASS="STYLE4" WIDTH="120PX">:سعادة </TD></TR>'));
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#EMAILSUBJECT#'),@EMAILSUBJECT);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#EMPNAME#'),@REQUEST_OWNER_USER_FULL_NAME);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#CREATDATE#'),@REQUEST_DATE);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#NOTE#'),@STATUS_NAME_CURRENT);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#INFO#'),N'المرحلة التالية : ' + @STAGE_NAME_NEXT);
-- EXEC [MSDB].[DBO].SP_SEND_DBMAIL @PROFILE_NAME='HOSPITALMAIL',
-- @RECIPIENTS='ENGMOSBAH11@GMAIL.COM' ,
-- @SUBJECT=@EMAILSUBJECT,
-- @BLIND_COPY_RECIPIENTS='ELSAYED.IBRAHIME@GMAIL.COM',
-- @BODY=@EMAILBODY,
-- @BODY_FORMAT = 'HTML'
-- END
------------------------------------------------------------------------------------------------------------
-- END
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------

END

------------------------------------------
trigger code


USE [HES_Intranet]
GO
/****** Object:  Trigger [dbo].[VACATION_REPORTS_APPROVAL_STAGES_SEND_MAIL]    Script Date: 06/03/2014 05:11:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR: <MOSBAH ELSAYED>
-- CREATE DATE: <31-05-2014>
-- DESCRIPTION: <TRIGGER TO SEND MAIL FOR USERS RELATED INSERTED APPROVAL ACTION >
-- =============================================
ALTER TRIGGER [dbo].[VACATION_REPORTS_APPROVAL_STAGES_SEND_MAIL]
   ON [dbo].[VACATION_REPORTS_APPROVAL_STAGES]    AFTER INSERT 
AS 
BEGIN
SET NOCOUNT ON;
-------------------------------------------------------------DECLARE VARIABLES
-------------------------------------------------------------MAIL PASSED PARAMETERS
DECLARE @WORK_FLOW_ID INT = 6
DECLARE @REQ_ID INT 
DECLARE @CURRENT_STAGE_ID INT
DECLARE @CURRENT_STATUS_ID INT
DECLARE @REQUEST_DATE NVARCHAR(50) 

SELECT @REQ_ID = REQUEST_REPORT_NO , @CURRENT_STAGE_ID=REQUEST_STAGE_ID  ,@CURRENT_STATUS_ID=REQUEST_STATUS_ID 
FROM INSERTED

SELECT @REQUEST_DATE =CONVERT(NVARCHAR(50) , R.CREATE_DATE ,120)  
FROM  VACATION_REPORTS_REQUEST R WHERE R.REQUEST_REPORT_NO=@REQ_ID

DECLARE @REQ_CREATED_BY_USER INT =( SELECT R.CREATE_BY_USER_ID FROM VACATION_REPORTS_REQUEST R WHERE R.REQUEST_REPORT_NO=@REQ_ID)
DECLARE @WORK_FLOW_NAME NVARCHAR(100) = (SELECT W.WORK_FLOW_NAME  FROM  DBO.WORK_FLOWS W WHERE W.WORK_FLOW_ID=@WORK_FLOW_ID)
DECLARE @WORK_FLOW_NAME_EN NVARCHAR(100) =(SELECT W.WORK_FLOW_NAME_EN  FROM  DBO.WORK_FLOWS W WHERE W.WORK_FLOW_ID=@WORK_FLOW_ID)

DECLARE @STAGE_NAME_CURRENT NVARCHAR(250) =(SELECT DBO.WORK_FLOWS_STAGES_GET_NAME_BY_STAGE_ID((@CURRENT_STAGE_ID)))
DECLARE @STAGE_NAME_CURRENT_EN NVARCHAR(250) =(SELECT DBO.WORK_FLOWS_STAGES_GET_NAME_EN_BY_STAGE_ID(@CURRENT_STAGE_ID))

DECLARE @STATUS_NAME_CURRENT NVARCHAR(250) =(SELECT S.REQUEST_STATUS_NAME FROM DBO.VACATION_REPORTS_REQUEST_STATUS S 
 WHERE S.REQUEST_STATUS_ID=@CURRENT_STATUS_ID  )
DECLARE @STATUS_NAME_CURRENT_EN NVARCHAR(250) =(SELECT S.REQUEST_STATUS_NAME_EN FROM DBO.VACATION_REPORTS_REQUEST_STATUS S 
 WHERE S.REQUEST_STATUS_ID=@CURRENT_STATUS_ID  )

DECLARE @REQUEST_OWNER_USER_FULL_NAME NVARCHAR(250) = (SELECT USER_FULL_NAME FROM USERS WHERE USER_ID =@REQ_CREATED_BY_USER)
DECLARE @REQUEST_OWNER_USER_FULL_NAME_EN NVARCHAR(250) = (SELECT USER_FULL_NAME_EN FROM USERS WHERE USER_ID =@REQ_CREATED_BY_USER)

DECLARE @STAGE_NAME_NEXT NVARCHAR(250)=N''
DECLARE @STAGE_NAME_NEXT_EN NVARCHAR(250)=N''

DECLARE @WORK_FLOW_TYPE_NAME NVARCHAR(1000)=N''
DECLARE @WORK_FLOW_TYPE_NAME_EN NVARCHAR(1000)=N''
DECLARE @NEXT_STAGE_USERS NVARCHAR(MAX) = N''
DECLARE @NEXT_STAGE_USERS_EN NVARCHAR(MAX) = N''
DECLARE @REQUEST_DETAILS NVARCHAR(MAX) =N''
SELECT @REQUEST_DETAILS= DBO.VACATION_REQUESTS_REQUEST_DETAILS_MAIL_STRING(@REQ_ID)
DECLARE @REQUEST_DETAILS_EN NVARCHAR(MAX) =N''
  SELECT @REQUEST_DETAILS_EN = DBO.VACATION_REQUESTS_REQUEST_DETAILS_MAIL_STRING_EN(@REQ_ID)
-------------------------------------------------------- STORED VARIABLES
DECLARE @TBL_USERS USERS_MAIL_LIST
----------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------ GET ALL REQUEST USERS
INSERT INTO @TBL_USERS(USER_ID ,USER_FULL_NAME  ,USER_FULL_NAME_EN , USER_EMAIL , MOBILE_NO ,MESSAGE_LANGUAGE)  
SELECT  U.USER_ID , U.USER_FULL_NAME , USER_FULL_NAME_EN ,USER_EMAIL  , MOBILE_NO , MESSAGE_LANGUAGE
FROM USERS U
WHERE U.USER_ID IN(
SELECT V.CREATE_BY_USER_ID
FROM VACATION_REPORTS_APPROVAL_STAGES V
WHERE V.REQUEST_REPORT_NO =@REQ_ID
)
------------------------------------------------------------------------------------ GET USERS FOR  NEXT STAGE
DECLARE @C1 CURSOR
DECLARE @C1_STAGE_ID INT = NULL
DECLARE @C1_WORK_FLOW_TYPE_ID INT = NULL 
DECLARE @C1_STAGE_NAME NVARCHAR(500) = NULL 
DECLARE @C1_STAGE_NAME_EN NVARCHAR(500) = NULL 
SET @C1 = CURSOR FORWARD_ONLY STATIC FOR
SELECT  W.TYPE_ID , W.STAGE_ID ,W.STAGE_NAME ,W.STAGE_NAME_EN ,W.TYPE_NAME ,W.TYPE_NAME_EN 
FROM DBO.WORK_FLOWS_LOAD_NEXT_STAGE_FUNCTION(@WORK_FLOW_ID ,@REQ_CREATED_BY_USER,@CURRENT_STAGE_ID) AS W
OPEN @C1
FETCH NEXT
FROM @C1 INTO @C1_WORK_FLOW_TYPE_ID , @C1_STAGE_ID ,@C1_STAGE_NAME , @C1_STAGE_NAME_EN ,@WORK_FLOW_TYPE_NAME ,@WORK_FLOW_TYPE_NAME_EN
WHILE (@@FETCH_STATUS = 0)
 BEGIN
 SET @STAGE_NAME_NEXT = N'' + @STAGE_NAME_NEXT +  N'' + N'' + @C1_STAGE_NAME 
 SET @STAGE_NAME_NEXT_EN = N'' + @STAGE_NAME_NEXT_EN + N'' + N'' + @C1_STAGE_NAME_EN 
 DECLARE @C2 CURSOR
 DECLARE @C2_USER_ID INT = NULL 
 DECLARE @C2_USER_FULL_NAME NVARCHAR(1000) = NULL 
 DECLARE @C2_USER_FULL_NAME_EN NVARCHAR(1000) = NULL 
 DECLARE @C2_USER_MAIL NVARCHAR(250) = NULL 
 DECLARE @C2_MOBILE_NO VARCHAR(15) 
 DECLARE @C2_MESSAGE_LANGUAGE INT
 
 SET @C2 = CURSOR FORWARD_ONLY STATIC FOR
SELECT UU_ID ,UUSER_FULL_NAME,UUSER_FULL_NAME_EN ,UU_USERMAIL ,UU_MOBILE_NO ,UU_MESSAGE_LANGUAGE
FROM [DBO].[WORK_FLOWS_PERMISSION_MANAGERIAL_GET_STAGE_USERS_BY_REQUEST_ID_FUNCTION](@WORK_FLOW_ID,@C1_WORK_FLOW_TYPE_ID,@C1_STAGE_ID ,@REQ_ID)
                OPEN @C2
FETCH NEXT FROM @C2 INTO @C2_USER_ID ,@C2_USER_FULL_NAME ,@C2_USER_FULL_NAME_EN ,@C2_USER_MAIL , @C2_MOBILE_NO ,@C2_MESSAGE_LANGUAGE 
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @TBL_USERS(USER_ID ,USER_FULL_NAME  ,USER_FULL_NAME_EN , USER_EMAIL  , MOBILE_NO ,MESSAGE_LANGUAGE)   
VALUES( @C2_USER_ID ,@C2_USER_FULL_NAME , @C2_USER_FULL_NAME_EN ,@C2_USER_MAIL , @C2_MOBILE_NO ,@C2_MESSAGE_LANGUAGE)
  
  SELECT DISTINCT @NEXT_STAGE_USERS =@NEXT_STAGE_USERS + N'' + USER_FULL_NAME  + N'<BR>' ,
@NEXT_STAGE_USERS_EN =@NEXT_STAGE_USERS_EN + N'' + USER_FULL_NAME_EN  + N'<BR>'
  FROM @TBL_USERS T WHERE T.USER_ID =@C2_USER_ID
 --PRINT N'@XXXXX = ' + RTRIM(CAST(@NEXT_STAGE_USERS AS NVARCHAR(3000))) + N'.';
 
FETCH NEXT FROM @C2 INTO @C2_USER_ID ,@C2_USER_FULL_NAME ,@C2_USER_FULL_NAME_EN ,@C2_USER_MAIL , @C2_MOBILE_NO ,@C2_MESSAGE_LANGUAGE
END
CLOSE @C2
DEALLOCATE @C2
FETCH NEXT FROM @C1 INTO @C1_WORK_FLOW_TYPE_ID , @C1_STAGE_ID ,@C1_STAGE_NAME , @C1_STAGE_NAME_EN ,@WORK_FLOW_TYPE_NAME ,@WORK_FLOW_TYPE_NAME_EN
 END
 CLOSE @C1
 DEALLOCATE @C1 
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------LOOP FOR USERS AND SEND MAIL ACCORDING USER DEFAULT LANG
-- SEND MAIL FOR ALL REQUEST USERS AND NEXT STAGE(S) USERS

 DECLARE @C3 CURSOR
     DECLARE @C3_USER_ID INT = NULL 
 DECLARE @C3_USER_FULL_NAME NVARCHAR(1000) = NULL 
 DECLARE @C3_USER_FULL_NAME_EN NVARCHAR(1000) = NULL 
 DECLARE @C3_USER_MAIL NVARCHAR(250) = NULL 
 DECLARE @C3_MESSAGE_LANGUAGE INT
SET @C3 = CURSOR FORWARD_ONLY STATIC FOR
SELECT  USER_ID  ,USER_FULL_NAME , USER_FULL_NAME_EN ,USER_EMAIL , MESSAGE_LANGUAGE
FROM @TBL_USERS
OPEN @C3
FETCH NEXT
FROM @C3 INTO @C3_USER_ID ,@C3_USER_FULL_NAME ,@C3_USER_FULL_NAME_EN , @C3_USER_MAIL ,@C3_MESSAGE_LANGUAGE
WHILE (@@FETCH_STATUS = 0)
 BEGIN
--  PRINT N'@REQ_ID = ' + RTRIM(CAST(@REQ_ID AS NVARCHAR(3000))) + N'.';
--PRINT N'@REQUEST_DATE = ' + RTRIM(CAST(@REQUEST_DATE AS NVARCHAR(50))) + N'.';
-- PRINT N'@WORK_FLOW_NAME = ' + RTRIM(CAST(@WORK_FLOW_NAME AS NVARCHAR(3000))) + N'.';
-- PRINT N'@@STAGE_NAME_CURRENT = ' + RTRIM(CAST(@STAGE_NAME_CURRENT AS NVARCHAR(3000))) + N'.';
--  PRINT N'@@STATUS_NAME_CURRENT = ' + RTRIM(CAST(@STATUS_NAME_CURRENT AS NVARCHAR(3000))) + N'.';
--   PRINT N'@REQUEST_OWNER_USER_FULL_NAME = ' + RTRIM(CAST(@REQUEST_OWNER_USER_FULL_NAME AS NVARCHAR(3000))) + N'.';
-- PRINT N'@STAGE_NAME_NEXT = ' + RTRIM(CAST(@STAGE_NAME_NEXT AS NVARCHAR(3000))) + N'.';
-- PRINT N'@@WORK_FLOW_TYPE_NAME = ' + RTRIM(CAST(@WORK_FLOW_TYPE_NAME AS NVARCHAR(3000))) + N'.';
-- PRINT N'@@USER_MAIL = ' + RTRIM(CAST(@C3_USER_MAIL AS NVARCHAR(3000))) + N'.';
-- PRINT N'@NEXT_STAGE_USERS = ' + RTRIM(CAST(@NEXT_STAGE_USERS AS NVARCHAR(3000))) + N'.';
IF(@C3_MESSAGE_LANGUAGE =2)
--SEND ENGLISH MAIL
BEGIN
EXEC [DBO].[EMPWORKFLOWSENDEMAIL] 
@ISEMPMANGER = 0,
@EMAIL_TYPE = 1 ,
@EMAIL_LANG = 2 ,
@REQ_ID   = @REQ_ID ,
@REQUEST_DATE =@REQUEST_DATE ,
@WORK_FLOW_NAME =@WORK_FLOW_NAME_EN,
@STAGE_NAME_CURRENT =@STAGE_NAME_CURRENT_EN ,
@STATUS_NAME_CURRENT = @STATUS_NAME_CURRENT_EN,
@REQUEST_OWNER_USER_FULL_NAME  = @REQUEST_OWNER_USER_FULL_NAME_EN ,
@STAGE_NAME_NEXT = @STAGE_NAME_NEXT_EN ,
@WORK_FLOW_TYPE_NAME  = @WORK_FLOW_TYPE_NAME_EN ,
@USER_MAIL = @C3_USER_MAIL ,
@USER_FULL_NAME= @C3_USER_FULL_NAME_EN,
@NEXT_STAGE_USERS = @NEXT_STAGE_USERS_EN ,
@REQUEST_DETAILS = @REQUEST_DETAILS_EN
END
ELSE
BEGIN
EXEC [DBO].[EMPWORKFLOWSENDEMAIL] 
@ISEMPMANGER = 0,
@EMAIL_TYPE = 1 ,
@EMAIL_LANG = 1 ,
@REQ_ID   = @REQ_ID ,
@REQUEST_DATE =@REQUEST_DATE ,
@WORK_FLOW_NAME =@WORK_FLOW_NAME,
@STAGE_NAME_CURRENT =@STAGE_NAME_CURRENT ,
@STATUS_NAME_CURRENT = @STATUS_NAME_CURRENT,
@REQUEST_OWNER_USER_FULL_NAME  = @REQUEST_OWNER_USER_FULL_NAME ,
@STAGE_NAME_NEXT = @STAGE_NAME_NEXT ,
@WORK_FLOW_TYPE_NAME  = @WORK_FLOW_TYPE_NAME ,
@USER_MAIL = @C3_USER_MAIL ,
@USER_FULL_NAME= @C3_USER_FULL_NAME,
@NEXT_STAGE_USERS = @NEXT_STAGE_USERS ,
@REQUEST_DETAILS = @REQUEST_DETAILS
END
 
FETCH NEXT FROM @C3 INTO @C3_USER_ID ,@C3_USER_FULL_NAME ,@C3_USER_FULL_NAME_EN , @C3_USER_MAIL ,@C3_MESSAGE_LANGUAGE
 END
 CLOSE @C3
 DEALLOCATE @C3
 

END


-----------------------------------------------

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)