jquery umalqura Calender with Gregorien Date Selector User Control Example

output :

description :
the article source build on ApI related
http://keith-wood.name/datepick.html

the Sample contains user Control with two textboxes the right one to select Umalqura date , the Second to select Gregorien Date .
when you select date (umalqura or gregorien ) the second date captured automatic using conversion method.

public Properties For User Control :
1- getGregorianDateText
return selected Gregorien date for the calender control.

2- getHijriDateText
get Hijri date using Umalqura Calender user selected .

3- SelectedCalendareDate
get or set the selected date for calender ,

4- SetHijriDate
set hijri date for the calender.
-------------------------------------------------------------
you can download Full Sample using this dropbox Link.
https://www.dropbox.com/sh/h840agq0v6xgnix/AAB2uJ-xLstnTQfzOCZTqOrza?dl=0

sql server Cursor template Example

using cursor in sql server :
cursor used to capture collection of result used after for looping or fetching in sub query .
example :

USE yourdbname
GO
declare cur cursor for
select  user_id from USERS
open cur
declare @id int
fetch next from cur into @id
while (@@FETCH_STATUS = 0)
begin
    print(@id)
    fetch next from cur into @id
end
close cur
deallocate cur
GO

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

to get values from cursor should declare variable or scalar table keep cursor values.

SQL SERVER PAGING OUTPUT DATA TO GRIDVIEW SAMPLE CODE

when you create gridview display multiple rows so you can divide rows query for sql server according your paging selection this will demanded to pass page size and page index to get query result .
this is best for performance to get from database rows needed to show not load all rows every postback for gridview paging.

sql server sample  code:

BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
---------------------------------------------------------------
DECLARE @INTPAGE INT =2 ;
DECLARE @INTPAGESIZE INT =10 ;
DECLARE @REQUEST_REPORT_NO VARCHAR(50) = NULL
   DECLARE @PATIENT_PROFILE_NO VARCHAR(50) = NULL
    DECLARE @REQUEST_STAGE_ID VARCHAR(50) = NULL
    DECLARE @REQUEST_STATUS_ID VARCHAR(50) = NULL
    DECLARE @FROM_DATE DATETIME = NULL
    DECLARE @TO_DATE DATETIME = NULL
   
DECLARE @INTSTARTROW INT;
DECLARE @INTENDROW INT;

SET @INTSTARTROW = (@INTPAGE -1) * @INTPAGESIZE + 1;
SET @INTENDROW = @INTPAGE * @INTPAGESIZE;  
--------------------------------------------------------------

WITH BLOGS AS
(
SELECT VR.*  ,
 ROW_NUMBER() OVER(ORDER BY VR.CREATE_DATE DESC) AS INTROW ,
      COUNT(VR.REQUEST_REPORT_NO) OVER() AS TOTAL_ROWS_COUNT
FROM VACATION_REPORTS_REQUEST VR
RIGHT JOIN WORK_FLOWS_TYPES_STAGES T ON VR.REQUEST_STAGE_ORDER = T.STAGE_ORDER
WHERE
        T.TYPE_ID = [DBO].[WORK_FLOWS_TYPE_GET_BY_USER_ID](6,VR.CREATE_BY_USER_ID)
  AND T.STAGE_ORDER =VR.REQUEST_STAGE_ORDER
  AND ([REQUEST_REPORT_NO] = @REQUEST_REPORT_NO OR @REQUEST_REPORT_NO IS NULL)
  AND  (@PATIENT_PROFILE_NO IS NULL OR VR.PATIENT_PROFILE_NO = @PATIENT_PROFILE_NO)
  AND  (@REQUEST_STAGE_ID IS NULL  OR T.STAGE_ID = @REQUEST_STAGE_ID)
  AND  (@REQUEST_STATUS_ID IS NULL OR VR.REQUEST_STATUS_ID = @REQUEST_STATUS_ID)
  AND (@FROM_DATE IS NULL OR VR.CREATE_DATE > = @FROM_DATE)
  AND (@TO_DATE IS NULL OR VR.CREATE_DATE < = @TO_DATE)
  AND (@FROM_DATE IS NULL OR
(@TO_DATE IS NOT NULL AND @FROM_DATE IS NOT  NULL AND  VR.CREATE_DATE BETWEEN @FROM_DATE AND @TO_DATE)
)
 
  )
 SELECT * FROM BLOGS
WHERE (@INTPAGESIZE =0 OR INTROW BETWEEN @INTSTARTROW AND @INTENDROW)
END

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

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

SQL SERVER Convert DateTime to String and Print Result to output window

DECLARE @REQUEST_DATE NVARCHAR(50)
SELECT @REQUEST_DATE =CONVERT(NVARCHAR(50) , R.CREATE_DATE ,120)

----------------
print result to output window messages

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'.';


-------------------------------------------
CONVERT INT TO NVARCHAR

SELECT @REQUEST_DETAILS =N'الجهة الطالبة : ' + R.INCOME_SIDE_NAME +N'<BR>'
+N'عدد أيام الاجازة' +  RTRIM(CAST(R.VACATION_NO_OF_DAYS AS NVARCHAR(50)))  + N'<BR>'
+N'من تاريخ' + CONVERT(NVARCHAR(50) , R.VACATION_FROM_DATE ,120)   + N'<BR>'
FROM VACATION_REPORTS_REQUEST R
WHERE R.REQUEST_REPORT_NO =@REQUEST_ID