

INSERT INTO [sSystem].[tVersionUpdate] ([VersionNumber],[VersionId],[Status],[UpdateScript],[StartDateTime],[EndDateTime],[OrgGroupId])
VALUES('23.08.28',230828,'BEGIN','Script',sCommon.fGetDate(),sCommon.fGetDate(),0)
GO




GO
PRINT N'Altering Table [sAcademic].[tOnlineMeetingAccount]...';


GO
ALTER TABLE [sAcademic].[tOnlineMeetingAccount] ALTER COLUMN [JWTToken] NVARCHAR (MAX) NULL;


GO
ALTER TABLE [sAcademic].[tOnlineMeetingAccount]
    ADD [OAuthAccountId]    NVARCHAR (MAX) NULL,
        [OAuthClientId]     NVARCHAR (MAX) NULL,
        [OAuthClientSecret] NVARCHAR (MAX) NULL;


GO
PRINT N'Altering Table [sAcademic].[tOnlineMeetingSettings]...';


GO
ALTER TABLE [sAcademic].[tOnlineMeetingSettings] ALTER COLUMN [JWTToken] NVARCHAR (MAX) NULL;


GO
ALTER TABLE [sAcademic].[tOnlineMeetingSettings]
    ADD [OAuthAccountId]    NVARCHAR (MAX) NULL,
        [OAuthClientId]     NVARCHAR (MAX) NULL,
        [OAuthClientSecret] NVARCHAR (MAX) NULL;


GO
PRINT N'Altering Table [sBiometric].[tShiftMasterCloud]...';


GO
ALTER TABLE [sBiometric].[tShiftMasterCloud]
    ADD [IsStopInTimeAlertTime] BIT      NULL,
        [StopInTimeAlertTime]   DATETIME NULL;


GO
PRINT N'Altering Table [sCanteen].[tCanteenIssue]...';


GO
ALTER TABLE [sCanteen].[tCanteenIssue]
    ADD [PayTypeId]  TINYINT        NULL,
        [ChequeNo]   NVARCHAR (40)  NULL,
        [BankName]   NVARCHAR (200) NULL,
        [ChequeDate] DATE           NULL;


GO
PRINT N'Altering Table [sLibrary].[tNewspaperLog]...';


GO
ALTER TABLE [sLibrary].[tNewspaperLog]
    ADD [NewspaperAmount] DECIMAL (12, 2) NULL;


GO
PRINT N'Altering Table [sSetup].[tOrgGroupMaster]...';


GO
ALTER TABLE [sSetup].[tOrgGroupMaster]
    ADD [ChannelPartner] NVARCHAR (200) NULL,
        [BrandConfig]    NVARCHAR (MAX) NULL;


GO
PRINT N'Creating Table [sAudit].[tStockOperationLog]...';


GO
CREATE TABLE [sAudit].[tStockOperationLog] (
    [AutoId]          BIGINT         IDENTITY (1, 1) NOT NULL,
    [ScreenId]        SMALLINT       NOT NULL,
    [OperationId]     SMALLINT       NOT NULL,
    [SourceTypeId]    SMALLINT       NOT NULL,
    [SourceId]        BIGINT         NOT NULL,
    [OperationRemark] NVARCHAR (MAX) NOT NULL,
    [CreatedUserId]   BIGINT         NOT NULL,
    [CreatedDateTime] DATETIME       NOT NULL,
    CONSTRAINT [PK_tStockOperationLog] PRIMARY KEY CLUSTERED ([AutoId] ASC)
);


GO

PRINT N'Altering Procedure [sAcademic].[pMeetingAccountAdd]...';


GO

 
ALTER PROCEDURE [sAcademic].[pMeetingAccountAdd]
	 
        @rId bigint = 0,
		@rEmployeeId bigint=0,
		@rMeetingProviderId int,
		@rAccountName nvarchar(200)='',
		@rAccountEmail nvarchar(200)='',
		@rAccountPassword nvarchar(200)='',
		@rAPIKey nvarchar(100)='',
		@rAPISecret nvarchar(100)='',
		@rWebAPIKey nvarchar(100)='',
		@rWebAPISecret nvarchar(100)='',
		@rCreatedUserId bigint,
		@rOrgGroupId Smallint,
		@rJWTToken nvarchar(MAX)='',
		@rIsObsolete  bit =0,
		@rOAuthAccountId nvarchar(max)='',
		@rOAuthClientId nvarchar(max)='',
		@rOAuthClientSecret nvarchar(max)=''

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
	 
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 
	IF EXISTS(SELECT 1 FROM sAcademic.tOnlineMeetingAccount WHERE AccountName = @rAccountName AND Id != @rId AND OrgGroupId = @rOrgGroupId)
		BEGIN
			SELECT -202 As Result
		END	
	ELSE
	BEGIN
	IF(@rId = 0)	
	BEGIN
		
	 
	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sAcademic.tOnlineMeetingAccount', 0, 1, 1,@outNextNumber = @lNextNumber output
	--Print @lNextNumber

	INSERT INTO sAcademic.tOnlineMeetingAccount
		(
			Id
           ,MeetingProviderId
           ,EmployeeId
           ,AccountName
           ,AccountEmailId
           ,AccountPassword
           ,APIKey
           ,APISecret
           ,WebAPIKey
           ,WebAPISecret
           ,CreatedUserId
           ,CreatedDateTime
		   ,OrgGroupId
		   ,JWTToken
		   ,IsObsolete
		   ,OAuthAccountId
		   ,OAuthClientId
		   ,OAuthClientSecret
		)
	VALUES
		(
			@lNextNumber,
			@rMeetingProviderId,
			@rEmployeeId,
			@rAccountName,
			@rAccountEmail,
			@rAccountPassword,
			@rAPIKey,
			@rAPISecret,
			@rWebAPIKey,
			@rWebAPISecret,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rOrgGroupId,
			@rJWTToken,
			@rIsObsolete,
			@rOAuthAccountId,
		    @rOAuthClientId,
		    @rOAuthClientSecret
		)
		    SELECT @lNextNumber AS Result
	END	 
	ELSE
	 BEGIN
	 SET @lNextNumber = @rId

	    UPDATE sAcademic.tOnlineMeetingAccount
	    SET
        	Id = @rId 
           ,EmployeeId = @rEmployeeId
           ,AccountName = @rAccountName
           ,AccountEmailId = @rAccountEmail
           ,AccountPassword = @rAccountPassword
           ,APIKey = @rAPIKey
           ,APISecret = @rAPISecret
		   ,WebAPIKey = @rWebAPIKey
           ,WebAPISecret = @rWebAPISecret
		   ,UpdatedUserId = @rCreatedUserId
		   ,UpdatedDateTime = sCommon.fGetDate()
		   ,OrgGroupId = @rOrgGroupId
		   ,JWTToken = @rJWTToken
		   ,IsObsolete = @rIsObsolete
		   ,OAuthAccountId = @rOAuthAccountId
		   ,OAuthClientId = @rOAuthClientId
		   ,OAuthClientSecret = @rOAuthClientSecret
	     WHERE Id = @rId
	      
		DECLARE @tOnlineLearningAccount TABLE  
		(  
		Id INT IDENTITY(1, 1),  
		OnlineLearningId BIGINT
		)   

		INSERT INTO @tOnlineLearningAccount
		SELECT Id
		FROM 
		 sAcademic.tOnlineLearning 
		WHERE MeetingAccountId = @rId AND IsCompleted = 0
		
		DECLARE @lCount int=1
		WHILE(@lCount <= (SELECT count(*) FROM @tOnlineLearningAccount))                
		BEGIN
			UPDATE sAcademic.tOnlineMeetingSettings 
			SET 
			AccountEmailId = OMA.AccountEmailId
		   ,AccountPassword = OMA.AccountPassword
		   ,APIKey = OMA.APIKey
		   ,APISecret = OMA.APISecret
		   ,WebAPIKey = OMA.WebAPIKey
		   ,WebAPISecret = OMA.WebAPISecret
		   ,MeetingProviderId = OMA.MeetingProviderId
		   ,JWTToken = OMA.JWTToken
		   ,OAuthAccountId = @rOAuthAccountId
		   ,OAuthClientId = @rOAuthClientId
		   ,OAuthClientSecret = @rOAuthClientSecret
		   FROM
			sAcademic.tOnlineMeetingAccount OMA
		   WHERE 
			OnlineLearningId = (SELECT OnlineLearningId FROM @tOnlineLearningAccount WHERE Id = @lCount)
			AND OMA.Id = @rId

			SET @lCount = @lCount + 1
		END
	      SELECT -100 AS Result
		--COMMIT TRANSACTION
	 END  	
	END
	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering Procedure [sAcademic].[pMeetingAccountGet]...';


GO
 
 
ALTER PROCEDURE [sAcademic].[pMeetingAccountGet]

	 @rId bigint = 0,
	 @rTitle nvarchar(100) ='',
	 @rEmployeeId bigint = 0,
	 @rOrgGroupId smallint,
	 @rMeetingProviderId int = 0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	if(@rId = 0)
	BEGIN 
	
	SELECT 
			OMA.Id,
			OMA.AccountName,
			OMA.AccountEmailId,
			ISNULL(EM.EmployeeName,'') as EmployeeName,
			DM.Title as MeetingProvider,
			OMA.WebAPIKey,
			OMA.WebAPISecret,
			ISNULL(OMA.IsObsolete,0) as IsObsolete,
			ISNULL(OMA.OAuthAccountId,'')AS OAuthAccountId,
			ISNULL(OMA.OAuthClientId,'') AS OAuthClientId,
			ISNULL(OMA.OAuthClientSecret,'') AS OAuthClientSecret
	  FROM 
			sAcademic.tOnlineMeetingAccount OMA
			LEFT JOIN sHrm.vExtEmployee EM ON EM.EmployeeId = OMA.EmployeeId 
			INNER JOIN sCommon.tDataMaster DM ON OMA.MeetingProviderId = DM.Id AND DM.DataTypeId = 8
	  WHERE 
  
				((OMA.AccountName like @rTitle) OR (ISNULL(@rTitle,'') = ''))
				AND OMA.OrgGroupId = @rOrgGroupId
				AND ((OMA.MeetingProviderId = @rMeetingProviderId) OR (ISNULL(@rMeetingProviderId,0) = 0))
	 
	END
	ELSE
	BEGIN
		SELECT 
			OMA.Id,
			OMA.EmployeeId as EmployeeId,
			EM.EmployeeCode as EmployeeCode,
			OMA.AccountName as AccountName,
			OMA.AccountEmailId as AccountEmailId,
			OMA.AccountPassword as AccountPassword,
			OMA.APIKey as APIKey,
			OMA.APISecret as APISecret,
			OMA.WebAPIKey as WebAPIKey,
			OMA.WebAPISecret as WebAPISecret,
			OMA.MeetingProviderId as MeetingProviderId,
			DM.Title as MeetingProvider,
			OMA.JWTToken as JWTToken,
			ISNULL(OMA.IsObsolete,0) as IsObsolete,
			ISNULL(OMA.OAuthAccountId,'')AS OAuthAccountId,
			ISNULL(OMA.OAuthClientId,'') AS OAuthClientId,
			ISNULL(OMA.OAuthClientSecret,'') AS OAuthClientSecret
	  FROM 
			sAcademic.tOnlineMeetingAccount OMA
			INNER JOIN sCommon.tDataMaster DM ON OMA.MeetingProviderId = DM.Id AND DM.DataTypeId = 8
			LEFT JOIN sHrm.vExtEmployee EM ON EM.EmployeeId = OMA.EmployeeId
	  WHERE 
			(OMA.Id = @rId) AND OMA.OrgGroupId = @rOrgGroupId
			AND ((OMA.MeetingProviderId = @rMeetingProviderId) OR (ISNULL(@rMeetingProviderId,0) = 0))
	END
END
GO
PRINT N'Altering Procedure [sAcademic].[pOnlineLearningAdd]...';


GO
 
ALTER PROCEDURE [sAcademic].[pOnlineLearningAdd]
	 
        @rId bigint = 0,
		@rLearningTypeId int,
		@rClassId int = 0,
        @rSubjectId int,
		@rTopicId int = 0,
		@rTeacherId bigint,
		@rStartDate date,
		@rEndDate date,
		@rStartTime datetime,
		@rEndTime datetime,
		@rTitle nvarchar(100) = '',
		@rDescription nvarchar(max) = '',
		@rLecturePath nvarchar(max) = '',
		@rIsDisplayMeetingId bit =0,
		@rMeetingId nvarchar(50) = '',
		@rMeetingPass nvarchar(50) = '',
		@rIsTrackAttendance bit =0,
		@rIsAssignment bit=0,
		@rIsResource bit=0,
		@rIsRefrence bit=0,
		@rIsCompleted bit = 0,
		@rIsMeetingStarted bit = 0,
		@rCreatedUserId bigint,
		@rBatchId int,
		@rOrgId int,
		@rTempOnlineLearningId nvarchar(100) = 0,
		@rIsSubmitAssignment bit=0,
		@rIsTakeTest bit=0,
		@rTestId int=0,
		@rIsOnParticipantVideo bit=0,
		@rIsOnParticipantAudio bit=0,
		@rIsOnHostVideo bit=0,
		@rIsOnHostAudio bit=0,
		@rIsOnParticipantScreenShare bit=0,
		@rIsOnHostscreenShare bit=0,
		@rMeetingAccountId int = 0,
		@rReferenceId bigint = 0,
		@rIsMeetingCreated bit=0,
		@rAssignmentRemark nvarchar(500) = '',
		@rIsTrackAssignmentView bit = 0,
		@rCoTeacherEmployeeId1 bigint =0,
		@rCoTeacherEmployeeId2 bigint =0,
		@rSubmissionEndDate DateTime = '',
		@rIsRecording bit = 0
			
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	 
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 

	 DECLARE @lAccountEmailId NVARCHAR(200),@lAccountPassword NVARCHAR(200),@lAPIKey NVARCHAR(100),@lAPISecret NVARCHAR(100),@lWebAPIKey NVARCHAR(100),
	         @lWebAPISecret NVARCHAR(100),@lMeetingProviderId NVARCHAR(100),@lJWTToken NVARCHAR(MAX),@lOAuthAccountId NVARCHAR(MAX), @lOAuthClientId NVARCHAR(MAX),@lOAuthClientSecret NVARCHAR(MAX)

	SELECT @lAccountEmailId = AccountEmailId,@lAccountPassword = AccountPassword,@lAPIKey = APIKey,@lAPISecret = APISecret,@lWebAPIKey = WebAPIKey,@lWebAPISecret = WebAPISecret,@lMeetingProviderId = MeetingProviderId,
	@lJWTToken = JWTToken, @lOAuthAccountId = OAuthAccountId , @lOAuthClientId = OAuthClientId , @lOAuthClientSecret = OAuthClientSecret
	    FROM sAcademic.tOnlineMeetingAccount
		WHERE Id = @rMeetingAccountId

	IF(@rId = 0)	
	BEGIN
		
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAcademic.tOnlineLearning', 0, 1, 1,@outNextNumber = @lNextNumber output
	Print @lNextNumber
	INSERT INTO sAcademic.tOnlineLearning
		(
			Id
           ,BatchId
           ,StartDate
           ,StartTime
           ,EndDate
           ,EndTime
           ,ClassId
           ,SubjectId
           ,TeacherEmployeeId
           ,Title
           ,Description
           ,LearningTypeId
           ,IsTrackAttendance
           ,TopicId
           ,LecturePath
           ,IsDisplayMeetingId
           ,MeetingId
           ,MeetingPassword
           ,IsAssignment
           ,IsReference
           ,IsResource
           ,IsCompleted
           ,IsMeetingStarted
           ,CreatedUserId
           ,CreatedDateTime
		   ,IsSubmitAssignment
		   ,IsTakeTest
		   ,TestId
		   ,MeetingAccountId
		   ,IsMeetingCreated
		   ,AssignmentRemark
		   ,IsTrackAssignmentView
		   ,CoTeacherEmployeeId1
		   ,CoTeacherEmployeeId2
		   ,AssignmentSubmissionEndDate
		   ,IsRecording
		)
	VALUES
		(
			@lNextNumber,
			@rBatchId,
			@rStartDate,
			@rStartTime,
			@rEndDate, 
			@rEndTime,
			@rClassId,
			@rSubjectId,
			@rTeacherId,
			@rTitle,
			@rDescription,
			@rLearningTypeId,
			@rIsTrackAttendance,
			@rTopicId,
			@rLecturePath,
			@rIsDisplayMeetingId,
			@rMeetingId,
			@rMeetingPass,
			@rIsAssignment,
			@rIsRefrence,
			@rIsResource,
			@rIsCompleted,
			@rIsMeetingStarted,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rIsSubmitAssignment,
			@rIsTakeTest,
			@rTestId,
			@rMeetingAccountId,
			@rIsMeetingCreated,
			@rAssignmentRemark,
			@rIsTrackAssignmentView,
			@rCoTeacherEmployeeId1,
			@rCoTeacherEmployeeId2,
			@rSubmissionEndDate,
			@rIsRecording
		)
		    
		IF(@rLearningTypeId = 2)
		BEGIN
		INSERT INTO sAcademic.tOnlineMeetingSettings
		(
			OnlineLearningId,
			IsOnParticipantVideo,
			IsOnParticipantAudio,
			IsOnHostVideo,
			IsOnHostAudio,
			IsOnParticipantScreenShare,
			IsOnHostscreenShare,
			AccountEmailId,
			AccountPassword,
			APIKey,
			APISecret,
			WebAPIKey,
			WebAPISecret,
			MeetingProviderId,
			JWTToken,
			OAuthAccountId,
			OAuthClientId,
			OAuthClientSecret
		)
		VALUES
		(
			@lNextNumber,
			@rIsOnParticipantVideo,
			@rIsOnParticipantAudio,
			@rIsOnHostVideo ,
			@rIsOnHostAudio ,
			@rIsOnParticipantScreenShare,
			@rIsOnHostscreenShare,
			@lAccountEmailId,
			@lAccountPassword,
			@lAPIKey,
			@lAPISecret,
			@lWebAPIKey,
			@lWebAPISecret,
			@lMeetingProviderId,
			@lJWTToken,
			@lOAuthAccountId,
			@lOAuthClientId,
			@lOAuthClientSecret
		)
		END
		IF(@rReferenceId = 0)
		BEGIN
		   IF EXISTS (SELECT 1 FROM sAcademic.tTempOnlineLearningScope WHERE TempOnlineLearningId = @rTempOnlineLearningId)
		   BEGIN
		   print 2
				IF EXISTS(SELECT 1 FROM sAcademic.tOnlineLearningScope WHERE Convert(NVARCHAR(MAX),OnlineLearningId) = @rTempOnlineLearningId)
				BEGIN
					DELETE FROM sAcademic.tOnlineLearningScope WHERE OnlineLearningId = @rTempOnlineLearningId
				END
				INSERT INTO sAcademic.tOnlineLearningScope
				SELECT @lNextNumber,SourceTypeId,SourceId
				FROM sAcademic.tTempOnlineLearningScope 
				WHERE TempOnlineLearningId = @rTempOnlineLearningId
		
				DELETE FROM sAcademic.tTempOnlineLearningScope WHERE TempOnlineLearningId = @rTempOnlineLearningId
			--COMMIT TRANSACTION 
			END     
		END
		ELSE
		BEGIN 
		   IF EXISTS (SELECT 1 FROM sAcademic.tOnlineLearningScope WHERE OnlineLearningId = @rReferenceId)
		   BEGIN
				INSERT INTO sAcademic.tOnlineLearningScope
				SELECT @lNextNumber,SourceTypeId,SourceId
				FROM sAcademic.tOnlineLearningScope 
				WHERE OnlineLearningId = @rReferenceId
		
			--COMMIT TRANSACTION 
			END    
		END
		SELECT @lNextNumber AS Result   
	END	 

ELSE
	 BEGIN
	 SET @lNextNumber = @rId
	    UPDATE sAcademic.tOnlineLearning
	    SET
			 
           StartDate = @rStartDate
           ,StartTime = @rStartTime
           ,EndDate = @rEndDate
           ,EndTime = @rEndTime
           ,ClassId = @rClassId
           ,SubjectId = @rSubjectId
           ,TeacherEmployeeId = @rTeacherId
           ,Title = @rTitle
           ,Description = @rDescription
           ,LearningTypeId = @rLearningTypeId
           ,IsTrackAttendance = @rIsTrackAttendance
           ,TopicId = @rTopicId
           ,LecturePath = @rLecturePath
           ,IsDisplayMeetingId = @rIsDisplayMeetingId
           ,MeetingId = @rMeetingId
           ,MeetingPassword = @rMeetingPass
           ,IsAssignment = @rIsAssignment
           ,IsReference = @rIsRefrence
           ,IsResource = @rIsResource
           ,IsCompleted = @rIsCompleted
		   ,UpdatedUserId=@rCreatedUserId
		   ,UpdatedDateTime=sCommon.fGetDate()
		   ,IsSubmitAssignment=@rIsSubmitAssignment
		   ,IsTakeTest=@rIsTakeTest
		   ,TestId=@rTestId
		   ,MeetingAccountId = @rMeetingAccountId
		   ,IsMeetingCreated = @rIsMeetingCreated
		   ,AssignmentRemark = @rAssignmentRemark
		   ,IsTrackAssignmentView = @rIsTrackAssignmentView
		   ,CoTeacherEmployeeId1 = @rCoTeacherEmployeeId1
		   ,CoTeacherEmployeeId2 = @rCoTeacherEmployeeId2
		   ,AssignmentSubmissionEndDate = @rSubmissionEndDate
		   ,IsRecording = @rIsRecording
	      WHERE Id = @rId
	      
		  IF(@rLearningTypeId = 2)
		  BEGIN
		  IF EXISTS (SELECT 1 FROM sAcademic.tOnlineMeetingSettings WHERE OnlineLearningId = @rId)
		  BEGIN
	      UPDATE sAcademic.tOnlineMeetingSettings
		  SET
			IsOnParticipantVideo = @rIsOnParticipantVideo,
			IsOnParticipantAudio = @rIsOnParticipantAudio,
			IsOnHostVideo = @rIsOnHostVideo,
			IsOnHostAudio = @rIsOnHostAudio,
			IsOnParticipantScreenShare = @rIsOnParticipantScreenShare,
			IsOnHostscreenShare = @rIsOnHostscreenShare,
			AccountEmailId = @lAccountEmailId,
			AccountPassword = @lAccountPassword,
			APIKey = @lAPIKey,
			APISecret = @lAPISecret,
			WebAPIKey = @lWebAPIKey,
			WebAPISecret = @lWebAPISecret,
			MeetingProviderId = @lMeetingProviderId,
			JWTToken = @lJWTToken,
			OAuthAccountId = @lOAuthAccountId,
			OAuthClientId = @lOAuthClientId,
			OAuthClientSecret = @lOAuthClientSecret
		  WHERE OnlineLearningId = @rId
	      END
		  ELSE
		  BEGIN
			INSERT INTO sAcademic.tOnlineMeetingSettings
			(
				OnlineLearningId,
				IsOnParticipantVideo,
				IsOnParticipantAudio,
				IsOnHostVideo,
				IsOnHostAudio,
				IsOnParticipantScreenShare,
				IsOnHostscreenShare,
				AccountEmailId,
				AccountPassword,
				APIKey,
				APISecret,
				WebAPIKey,
				WebAPISecret,
				MeetingProviderId,
				JWTToken,
				OAuthAccountId,
				OAuthClientId,
				OAuthClientSecret
			)
			VALUES
			(
				@rId,
				@rIsOnParticipantVideo,
				@rIsOnParticipantAudio,
				@rIsOnHostVideo ,
				@rIsOnHostAudio ,
				@rIsOnParticipantScreenShare,
				@rIsOnHostscreenShare,
				@lAccountEmailId,
				@lAccountPassword,
				@lAPIKey,
				@lAPISecret,
				@lWebAPIKey,
				@lWebAPISecret,
				@lMeetingProviderId,
				@lJWTToken,
				@lOAuthAccountId,
				@lOAuthClientId,
				@lOAuthClientSecret
			)
		  END
		  END
		  IF EXISTS (SELECT 1 FROM sAcademic.tTempOnlineLearningScope WHERE TempOnlineLearningId = @rTempOnlineLearningId)
		   BEGIN
				IF EXISTS(SELECT 1 FROM sAcademic.tOnlineLearningScope WHERE OnlineLearningId = @rTempOnlineLearningId)
				BEGIN
					DELETE FROM sAcademic.tOnlineLearningScope WHERE OnlineLearningId = @rTempOnlineLearningId
				END
				INSERT INTO sAcademic.tOnlineLearningScope
				SELECT @lNextNumber,SourceTypeId,SourceId
				FROM sAcademic.tTempOnlineLearningScope 
				WHERE TempOnlineLearningId = @rTempOnlineLearningId
		
				DELETE FROM sAcademic.tTempOnlineLearningScope WHERE TempOnlineLearningId = @rTempOnlineLearningId
			--COMMIT TRANSACTION 
			END  
	      SELECT 1 AS Result
	
		--COMMIT TRANSACTION
	 END  	

	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering Procedure [sAcademic].[pOnlineLearningMobilelectureAdd]...';


GO
 
ALTER PROCEDURE [sAcademic].[pOnlineLearningMobilelectureAdd]
	 
        @rId bigint = 0,
		@rLearningTypeId int,
		@rClassId int = 0,
        @rSubjectId int,
		@rTopicId int = 0,
		@rTeacherId bigint,
		@rStartDate date,
		@rEndDate date,
		@rStartTime datetime,
		@rEndTime datetime,
		@rTitle nvarchar(100) = '',
		@rDescription nvarchar(max) = '',
		@rLecturePath nvarchar(max) = '',
		@rIsDisplayMeetingId bit =0,
		@rMeetingId nvarchar(50) = '',
		@rMeetingPass nvarchar(50) = '',
		@rIsTrackAttendance bit =0,
		@rIsAssignment bit=0,
		@rIsResource bit=0,
		@rIsRefrence bit=0,
		@rIsCompleted bit = 0,
		@rIsMeetingStarted bit = 0,
		@rCreatedUserId bigint,
		@rBatchId int,
		@rOrgId int,
		--@rTempOnlineLearningId nvarchar(100) = 0,
		@rIsSubmitAssignment bit=0,
		@rIsTakeTest bit=0,
		@rTestId int=0,
		@rIsOnParticipantVideo bit=0,
		@rIsOnParticipantAudio bit=0,
		@rIsOnHostVideo bit=0,
		@rIsOnHostAudio bit=0,
		@rIsOnParticipantScreenShare bit=0,
		@rIsOnHostscreenShare bit=0,
		@rMeetingAccountId int = 0,
		@rReferenceId bigint = 0,
		@rIsMeetingCreated bit=0,
		@rAssignmentRemark nvarchar(500) = '',
		@rIsTrackAssignmentView bit = 0,
		@rCoTeacherEmployeeId1 bigint =0,
		@rCoTeacherEmployeeId2 bigint =0,
		--@rAssignToXML nvarchar(Max) = ''
		@rSubmissionEndDate DateTime = '',
		@rIsRecording bit = 0
			
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	 
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 

	 DECLARE @lAccountEmailId NVARCHAR(200),@lAccountPassword NVARCHAR(200),@lAPIKey NVARCHAR(100),@lAPISecret NVARCHAR(100),@lWebAPIKey NVARCHAR(100),
	         @lWebAPISecret NVARCHAR(100),@lMeetingProviderId NVARCHAR(100),@lJWTToken NVARCHAR(MAX),@lOAuthAccountId NVARCHAR(MAX) ,@lOAuthClientId NVARCHAR(MAX),@lOAuthClientSecret NVARCHAR(MAX)

	SELECT @lAccountEmailId = AccountEmailId,@lAccountPassword = AccountPassword,@lAPIKey = APIKey,@lAPISecret = APISecret,@lWebAPIKey = WebAPIKey,@lWebAPISecret = WebAPISecret,@lMeetingProviderId = MeetingProviderId
	,@lJWTToken = JWTToken , @lOAuthAccountId = OAuthAccountId , @lOAuthClientId = OAuthClientId, @lOAuthClientSecret = OAuthClientSecret
	    FROM sAcademic.tOnlineMeetingAccount
		WHERE Id = @rMeetingAccountId



	IF(@rId = 0)	
	BEGIN
		
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAcademic.tOnlineLearning', 0, 1, 1,@outNextNumber = @lNextNumber output
	Print @lNextNumber
	
	INSERT INTO sAcademic.tOnlineLearning
		(
			Id
           ,BatchId
           ,StartDate
           ,StartTime
           ,EndDate
           ,EndTime
           ,ClassId
           ,SubjectId
           ,TeacherEmployeeId
           ,Title
           ,Description
           ,LearningTypeId
           ,IsTrackAttendance
           ,TopicId
           ,LecturePath
           ,IsDisplayMeetingId
           ,MeetingId
           ,MeetingPassword
           ,IsAssignment
           ,IsReference
           ,IsResource
           ,IsCompleted
           ,IsMeetingStarted
           ,CreatedUserId
           ,CreatedDateTime
		   ,IsSubmitAssignment
		   ,IsTakeTest
		   ,TestId
		   ,MeetingAccountId
		   ,IsMeetingCreated
		   ,AssignmentRemark
		   ,IsTrackAssignmentView
		   ,CoTeacherEmployeeId1
		   ,CoTeacherEmployeeId2
		   ,AssignmentSubmissionEndDate
		   ,IsRecording
		)
	VALUES
		(
			@lNextNumber,
			@rBatchId,
			@rStartDate,
			@rStartTime,
			@rEndDate, 
			@rEndTime,
			@rClassId,
			@rSubjectId,
			@rTeacherId,
			@rTitle,
			@rDescription,
			@rLearningTypeId,
			@rIsTrackAttendance,
			@rTopicId,
			@rLecturePath,
			@rIsDisplayMeetingId,
			@rMeetingId,
			@rMeetingPass,
			@rIsAssignment,
			@rIsRefrence,
			@rIsResource,
			@rIsCompleted,
			@rIsMeetingStarted,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rIsSubmitAssignment,
			@rIsTakeTest,
			@rTestId,
			@rMeetingAccountId,
			@rIsMeetingCreated,
			@rAssignmentRemark,
			@rIsTrackAssignmentView,
			@rCoTeacherEmployeeId1,
			@rCoTeacherEmployeeId2,
			@rSubmissionEndDate,
			@rIsRecording
		)
		    
		IF(@rLearningTypeId = 2)
		BEGIN
		INSERT INTO sAcademic.tOnlineMeetingSettings
		(
			OnlineLearningId,
			IsOnParticipantVideo,
			IsOnParticipantAudio,
			IsOnHostVideo,
			IsOnHostAudio,
			IsOnParticipantScreenShare,
			IsOnHostscreenShare,
			AccountEmailId,
			AccountPassword,
			APIKey,
			APISecret,
			WebAPIKey,
			WebAPISecret,
			MeetingProviderId,
			JWTToken,
			OAuthAccountId,
			OAuthClientId,
			OAuthClientSecret
		)
		VALUES
		(
			@lNextNumber,
			@rIsOnParticipantVideo,
			@rIsOnParticipantAudio,
			@rIsOnHostVideo ,
			@rIsOnHostAudio ,
			@rIsOnParticipantScreenShare,
			@rIsOnHostscreenShare,
			@lAccountEmailId,
			@lAccountPassword,
			@lAPIKey,
			@lAPISecret,
			@lWebAPIKey,
			@lWebAPISecret,
			@lMeetingProviderId,
			@lJWTToken,
			@lOAuthAccountId,
			@lOAuthClientId,
			@lOAuthClientSecret
		)
		END
		
		--COMMIT TRANSACTION 
		SELECT @lNextNumber AS Result   
	END	 

ELSE
	 BEGIN
	 SET @lNextNumber = @rId
	    UPDATE sAcademic.tOnlineLearning
	    SET
			 
           StartDate = @rStartDate
           ,StartTime = @rStartTime
           ,EndDate = @rEndDate
           ,EndTime = @rEndTime
           ,ClassId = @rClassId
           ,SubjectId = @rSubjectId
           ,TeacherEmployeeId = @rTeacherId
           ,Title = @rTitle
           ,Description = @rDescription
           ,LearningTypeId = @rLearningTypeId
           ,IsTrackAttendance = @rIsTrackAttendance
           ,TopicId = @rTopicId
           ,LecturePath = @rLecturePath
           ,IsDisplayMeetingId = @rIsDisplayMeetingId
           ,MeetingId = @rMeetingId
           ,MeetingPassword = @rMeetingPass
           ,IsAssignment = @rIsAssignment
           ,IsReference = @rIsRefrence
           ,IsResource = @rIsResource
           ,IsCompleted = @rIsCompleted
		   ,UpdatedUserId=@rCreatedUserId
		   ,UpdatedDateTime=sCommon.fGetDate()
		   ,IsSubmitAssignment=@rIsSubmitAssignment
		   ,IsTakeTest=@rIsTakeTest
		   ,TestId=@rTestId
		   ,MeetingAccountId = @rMeetingAccountId
		   ,IsMeetingCreated = @rIsMeetingCreated
		   ,AssignmentRemark = @rAssignmentRemark
		   ,IsTrackAssignmentView = @rIsTrackAssignmentView
		   ,CoTeacherEmployeeId1 = @rCoTeacherEmployeeId1
		   ,CoTeacherEmployeeId2 = @rCoTeacherEmployeeId2
		   ,AssignmentSubmissionEndDate = @rSubmissionEndDate
		   ,IsRecording = @rIsRecording
	      WHERE Id = @rId
	      
		  IF(@rLearningTypeId = 2)
		  BEGIN
		  IF EXISTS (SELECT 1 FROM sAcademic.tOnlineMeetingSettings WHERE OnlineLearningId = @rId)
		  BEGIN
	      UPDATE sAcademic.tOnlineMeetingSettings
		  SET
			IsOnParticipantVideo = @rIsOnParticipantVideo,
			IsOnParticipantAudio = @rIsOnParticipantAudio,
			IsOnHostVideo = @rIsOnHostVideo,
			IsOnHostAudio = @rIsOnHostAudio,
			IsOnParticipantScreenShare = @rIsOnParticipantScreenShare,
			IsOnHostscreenShare = @rIsOnHostscreenShare,
			AccountEmailId = @lAccountEmailId,
			AccountPassword = @lAccountPassword,
			APIKey = @lAPIKey,
			APISecret = @lAPISecret,
			WebAPIKey = @lWebAPIKey,
			WebAPISecret = @lWebAPISecret,
			MeetingProviderId = @lMeetingProviderId,
			JWTToken = @lJWTToken,
			OAuthAccountId = @lOAuthAccountId,
			OAuthClientId = @lOAuthClientId,
			OAuthClientSecret = @lOAuthClientSecret
		  WHERE OnlineLearningId = @rId
	      END
		  ELSE
		  BEGIN
			INSERT INTO sAcademic.tOnlineMeetingSettings
			(
				OnlineLearningId,
				IsOnParticipantVideo,
				IsOnParticipantAudio,
				IsOnHostVideo,
				IsOnHostAudio,
				IsOnParticipantScreenShare,
				IsOnHostscreenShare,
				AccountEmailId,
				AccountPassword,
				APIKey,
				APISecret,
				WebAPIKey,
				WebAPISecret,
				MeetingProviderId,
				JWTToken,
				OAuthAccountId,
				OAuthClientId,
				OAuthClientSecret
			)
			VALUES
			(
				@rId,
				@rIsOnParticipantVideo,
				@rIsOnParticipantAudio,
				@rIsOnHostVideo ,
				@rIsOnHostAudio ,
				@rIsOnParticipantScreenShare,
				@rIsOnHostscreenShare,
				@lAccountEmailId,
				@lAccountPassword,
				@lAPIKey,
				@lAPISecret,
				@lWebAPIKey,
				@lWebAPISecret,
				@lMeetingProviderId,
				@lJWTToken,
				@lOAuthAccountId,
				@lOAuthClientId,
				@lOAuthClientSecret
			)
		  END
		  END

	      SELECT 1 AS Result
	
		--COMMIT TRANSACTION
	 END 		
	 
	
	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
END
GO
PRINT N'Altering Procedure [sAcademic].[pOnlineLearningScheduleCopy]...';


GO
 

ALTER PROCEDURE [sAcademic].[pOnlineLearningScheduleCopy]
	 
        @rId bigint,
		@rAssignToStartDate DateTime,
		@rAssignToEndDate DateTime,
		@rCreatedUserId bigint,
		@rOrgId int
			
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	 
	 BEGIN TRANSACTION	

	 DECLARE @lNextNumber BIGINT 

	 EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAcademic.tOnlineLearning', 0, 1, 1,@outNextNumber = @lNextNumber output
	 Print @lNextNumber
	 
	 INSERT INTO sAcademic.tOnlineLearning
		(
			Id
           ,BatchId
           ,StartDate
           ,StartTime
           ,EndDate
           ,EndTime
           ,ClassId
           ,SubjectId
           ,TeacherEmployeeId
           ,Title
           ,Description
           ,LearningTypeId
           ,IsTrackAttendance
           ,TopicId
           ,LecturePath
           ,IsDisplayMeetingId
           ,MeetingId
           ,MeetingPassword
		   ,IsCompleted
           ,CreatedUserId
           ,CreatedDateTime
		   ,MeetingAccountId
		   ,CoTeacherEmployeeId1
		   ,CoTeacherEmployeeId2
		)
	 SELECT 
			@lNextNumber,
			OL.BatchId,
			@rAssignToStartDate,
			OL.StartTime,
			@rAssignToEndDate, 
			OL.EndTime,
			OL.ClassId,
			OL.SubjectId,
			OL.TeacherEmployeeId,
			OL.Title,
			OL.Description,
			OL.LearningTypeId,
			OL.IsTrackAttendance,
			OL.TopicId,
			OL.LecturePath,
			OL.IsDisplayMeetingId,
			'',
			'',
			0,
			@rCreatedUserId,
			sCommon.fGetDate(),
			OL.MeetingAccountId,
			OL.CoTeacherEmployeeId1,
			OL.CoTeacherEmployeeId2
	  FROM sAcademic.tOnlineLearning OL WHERE Id = @rId

	  INSERT INTO sAcademic.tOnlineMeetingSettings
		(
			OnlineLearningId,
			IsOnParticipantVideo,
			IsOnParticipantAudio,
			IsOnHostVideo,
			IsOnHostAudio,
			IsOnParticipantScreenShare,
			IsOnHostscreenShare,
			AccountEmailId,
			AccountPassword,
			APIKey,
			APISecret,
			WebAPIKey,
			WebAPISecret,
			MeetingProviderId,
			JWTToken,
			OAuthAccountId,
			OAuthClientId,
			OAuthClientSecret
		)
		SELECT
			@lNextNumber,
			OMS.IsOnParticipantVideo,
			OMS.IsOnParticipantAudio,
			OMS.IsOnHostVideo ,
			OMS.IsOnHostAudio ,
			OMS.IsOnParticipantScreenShare,
			OMS.IsOnHostscreenShare,
			OMS.AccountEmailId,
			OMS.AccountPassword,
			OMS.APIKey,
			OMS.APISecret,
			OMS.WebAPIKey,
			OMS.WebAPISecret,
			OMS.MeetingProviderId,
			OMS.JWTToken,
			OMS.OAuthAccountId,
			OMS.OAuthClientId,
			OMS.OAuthClientSecret
		FROM 
		sAcademic.tOnlineMeetingSettings OMS
		WHERE OnlineLearningId = @rId

		 IF EXISTS (SELECT 1 FROM sAcademic.tOnlineLearningScope WHERE OnlineLearningId = @rId)
		   BEGIN
				INSERT INTO sAcademic.tOnlineLearningScope
				SELECT @lNextNumber,SourceTypeId,SourceId
				FROM sAcademic.tOnlineLearningScope 
				WHERE OnlineLearningId = @rId
		
			END    

	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering Procedure [sBiometric].[pShiftMasterCloudAdd]...';


GO

 
ALTER PROCEDURE [sBiometric].[pShiftMasterCloudAdd]
@rShiftCode int = 0,
@rShiftName nvarchar(50),
@rStartTime DateTime = null,
@rEndTime DateTime = null,
@rGraceBefore int = 0,
@rGraceAfter int = 0,
@rShiftDur nvarchar(16) = NULL,
@rMinHrsHalfDay  nvarchar(16) = null,
@rMinHrsFullDay nvarchar(16) = null,
@rAlterGraceTime int = 0,
@IsOutTimeAlertTime bit = 0,
@OutTimeAlertTime DateTime = null,
@rOrgGroupId int = 0,
@rLastOutAlertBasedOnId int=1,
@rIsStopInTimeAlertTime bit=0,
@rStopInTimeAlertTime DateTime=null

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	IF(@rShiftcode = 0)	
		BEGIN
			If Exists(select ShiftCode from sBiometric.tShiftMasterCloud where ShiftName=@rShiftName and OrgGroupId = @rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
			END 
		END
	ELSE
		BEGIN
			If  Exists(select ShiftCode from sBiometric.tShiftMasterCloud where ShiftName=@rShiftName AND Shiftcode<>@rShiftcode and OrgGroupId = @rOrgGroupId)
			Begin
				select -103 as Result
				RETURN
			End
		END	
	
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 
	 IF EXISTS (SELECT 1 FROM sBiometric.tShiftMasterCloud)
	 BEGIN
		SELECT @lNextNumber = Max(ISNULL(ShiftCode,0)) + 1 FROM sBiometric.tShiftMasterCloud
	 END
	 ELSE
	 BEGIN
		SET @lNextNumber = 1
	 END
	 
	IF(@rShiftcode = 0)	
	BEGIN
	Insert into sBiometric.tShiftMasterCloud 
     (ShiftCode
     , ShiftName
     , StartTime
     , EndTime
     , GraceBefore
     , GraceAfter
     , ShiftDur
     , MinHrsHalfDay
     , MinHrsFullDay    
     , AlterGraceTime
     , IsOutTimeAlertTime
     , OutTimeAlertTime
     , OrgGroupId
	 ,LastOutAlertBasedOnId
	 ,IsStopInTimeAlertTime
	 ,StopInTimeAlertTime)
     Values
     (@lNextNumber
     ,@rShiftName
     ,@rStartTime
     ,@rEndTime
     ,@rGraceBefore
     ,@rGraceAfter
     ,@rShiftDur
     ,@rMinHrsHalfDay
     ,@rMinHrsFullDay
     ,@rAlterGraceTime
     ,@IsOutTimeAlertTime
     ,@OutTimeAlertTime
     ,@rOrgGroupId
     ,@rLastOutAlertBasedOnId
	 ,@rIsStopInTimeAlertTime 
	 ,@rStopInTimeAlertTime 
	 )
     
     SELECT @lNextNumber AS Result
	
		--COMMIT TRANSACTION         
	END	 

ELSE
	 BEGIN
	    UPDATE sBiometric.tShiftMasterCloud
	    SET
			ShiftName=@rShiftName,
			StartTime=@rStartTime,
			EndTime=@rEndTime,
			GraceBefore=@rGraceBefore,
			shiftDur=@rShiftDur,
			MinHrsHalfDay=@rMinHrsHalfDay,
			MinHrsFullDay=@rMinHrsFullDay,
			AlterGraceTime=@rAlterGraceTime,
		    IsOutTimeAlertTime = @IsOutTimeAlertTime,
			OutTimeAlertTime = @OutTimeAlertTime,
			LastOutAlertBasedOnId=@rLastOutAlertBasedOnId,
			IsStopInTimeAlertTime=@rIsStopInTimeAlertTime,
			StopInTimeAlertTime=@rStopInTimeAlertTime
		
	      WHERE ShiftCode = @rShiftcode and OrgGroupId = @rOrgGroupId
	      
	      SELECT @lNextNumber AS Result
	
		--COMMIT TRANSACTION
	 END  	

	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	     
     
END
GO
PRINT N'Altering Procedure [sBiometric].[pShiftMasterCloudGet]...';


GO

ALTER PROCEDURE [sBiometric].[pShiftMasterCloudGet]
@rShiftCode int = 0,
@rShiftName nvarchar(50) = '',
@rOrgGroupId int = 0

AS
BEGIN

	select [ShiftCode]
      ,[ShiftName]
      ,[StartTime]
      ,[EndTime]
      ,[GraceBefore]
      ,[GraceAfter]
      ,[ShiftDur]
      ,[MinHrsHalfDay]
      ,[MinHrsFullDay]
      ,[RecessDur]
      ,[AlterGraceTime]
      ,[SMSScheduleTime]
      ,[ShiftGroupCode]
      ,[IsOutTimeAlertTime]
      ,[OutTimeAlertTime]
      ,[OrgGroupId]
      ,isnull([LastOutAlertBasedOnId],1) as LastOutAlertBasedOnId
	  ,Isnull(IsStopInTimeAlertTime,0) as IsStopInTimeAlertTime
	 ,StopInTimeAlertTime 
	   from sBiometric.tShiftMasterCloud
	where 
	((ShiftCode = @rShiftCode) OR (@rShiftCode=0))     
	AND ((ShiftName = @rShiftName) OR (@rShiftName=''))     
	AND ((OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))     
END
GO
PRINT N'Altering Procedure [sCanteen].[pCanteenIssueAdd]...';


GO
ALTER PROCEDURE [sCanteen].[pCanteenIssueAdd]
	  
	  
        @rId bigint,
        @rCanteenId int,
		@rSourceTypeId smallint,
		@rSourceId bigint,
		@rIssueDate date,
		@rTotalAmount decimal(12,2),
		@rOrgGroupId int,
		@rCreatedUserId bigint,
		@rOtherName nvarchar(100),
		@rCanteenIssueXml nvarchar(max),
		@rStudentDailySpendLimit decimal(12,2)=0,
		--@rIdOut [bigint] OUTPUT
		--add by dhruvik for 25772 mantis issue
		@rPayTypeId tinyint,		--add
		@rChequeNo nvarchar(40),	--add
		@rBankName nvarchar(200),	--add
		@rChequeDate date			--add
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
	
	--IF(@rId = 0)	
	--BEGIN
		
	--	If Exists(select 1 from sCanteen.tCanteenIssue where CanteenId=@rCanteenId AND Id=@rId)
	--		BEGIN
	--		select -103 as Result
	--		RETURN
	--	END 
	--END
	--ELSE
	--BEGIN
		
	--	If  Exists(select 1 from sCanteen.tCanteenIssue where CanteenId=@rCanteenId AND Id<>@rId)
	--	Begin
	--		select -103 as Result
	--		RETURN
	--	End

	--END
	IF (@rSourceTypeId=2003 AND @rStudentDailySpendLimit > 0 AND @rId = 0)
	BEGIN
	DECLARE @lSpendAmount Decimal(12,2)
	SELECT @lSpendAmount = ISNUll(SUM(TotalAmount),0) from sCanteen.tCanteenIssue where CanteenId=@rCanteenId AND SourceId=@rSourceId AND @rSourceTypeId=2003 AND IssueDate = @rIssueDate
	
	if (@lSpendAmount + @rTotalAmount > @rStudentDailySpendLimit )
	BEGIN
	SELECT -101 as Result 
	RETURN
	END
	END
	
	BEGIN TRANSACTION	
	
	 DECLARE @lNextNumber bigint

		IF(@rId = 0)	
	BEGIN
	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sCanteen.tCanteenIssue', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	INSERT INTO sCanteen.tCanteenIssue
		(
			CanteenId,
			Id,	
			SourceTypeId,
			SourceId,
			IssueDate,
			TotalAmount,
			CreatedUserId,
			CreatedDateTime,
			OtherName,
			--add by dhruvik for 25772 mantis issue
			PayTypeId,		--add
			ChequeNo,		--add
			BankName,		--add
			ChequeDate		--add
			
		)
	VALUES
		(
		    @rCanteenId,
			@lNextNumber,
			@rSourceTypeId,
			@rSourceId,
			@rIssueDate,
			@rTotalAmount,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rOtherName,
			--add by dhruvik for 25772 mantis issue
			@rPayTypeId,		--add
			@rChequeNo,			--add
			@rBankName,			--add
			@rChequeDate		--add
		)
		
		--SELECT 1 AS Result
		  SELECT @lNextNumber AS Result
		 END 
	ELSE
	 BEGIN
	 SET @lNextNumber = @rId
	    UPDATE  sCanteen.tCanteenIssue
	    SET
			TotalAmount=@rTotalAmount,
			--add by dhruvik for 25772 mantis issue
			PayTypeId=@rPayTypeId,		--add
			ChequeNo=@rChequeNo,		--add
			BankName=@rBankName,		--add
			ChequeDate=@rChequeDate		--add
	      
	      WHERE Id = @rId
	      SELECT 11 AS Result
	 END  	
		
	EXEC [sCanteen].[pCanteenIssueDetailAdd] @lNextNumber,@rCanteenIssueXml
	
	
	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	
END
GO
PRINT N'Altering Procedure [sCanteen].[pCanteenIssueMasterGet]...';


GO
ALTER PROCEDURE [sCanteen].[pCanteenIssueMasterGet]
	
	@rId bigint = 0,
	@rCanteenId int ,
	@rFromDate date,
	@rToDate date,
	@rSUID nvarchar(50)=''

	--@rCode nvarchar(max)
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	 SELECT 
	        CI.Id,
			CI.CanteenId,
			CI.SourceTypeId,
			CI.SourceId,
			CI.IssueDate,
			CI.TotalAmount,
			CI.CreatedDateTime,
			(CASE WHEN CI.SourceTypeId=2003 THEN  isnull(SMast.Code,'')
			WHEN  CI.SourceTypeId=95  THEN isnull(EM.Code, '') ElSE ''
			 END) AS Code,
			 (CASE WHEN CI.SourceTypeId=2003 THEN  isnull(SMast.Name,'')
			WHEN  CI.SourceTypeId=95  THEN isnull(EM.FirstName +' ' + EM.MiddleName + ' ' + EM.LastName ,'') ElSE CI.OtherName
			 END) AS Name,
			 ISNULL(CI.OtherName,'') AS OtherName,
			 (CASE WHEN CI.SourceTypeId=2003 THEN  isnull(SMast.OrgId,'')
			WHEN  CI.SourceTypeId=95  THEN isnull(EM.OrgId, '') ElSE ''
			 END) AS OrgId,
			  (CASE WHEN CI.SourceTypeId=2003 THEN  isnull(SMast.SUId,'')
			 ElSE ''
			 END) AS SUId,
			 --add by dhruvik for 25772 mantis issue
			 ISNULL(CI.PayTypeId,0) as PayTypeId,			--add
			 ISNULL(CI.ChequeNo,'') as ChequeNo,			--add
			 ISNULL(CI.BankName,'') as BankName,			--add
			 ISNULL(CI.ChequeDate,'') as ChequeDate			--add
		
	   FROM sCanteen.tCanteenIssue CI
	   LEFT JOIN  sStudent.vStudentLastAcademic SMast ON SMast.StudentId = CI.SourceId AND CI.SourceTypeId = 2003  
       LEFT JOIN sHrm.tEmployeeMaster EM ON EM.Id = CI.SourceId AND CI.SourceTypeId = 95
     
      
	  
	  WHERE ((CI.Id = @rId) OR (@rId = 0))
		  AND CI.CanteenId = @rCanteenId
		  AND ((CI.IssueDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		 AND ((CI.IssueDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
		 AND ((SMast.SUID = @rSUID) OR (ISNULL(@rSUID,'')=''))
	 --  AND ((CI.IssueDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
	 AND ISNULL(EM.IsOtherUser,0) = 0 
order by CI.IssueDate desc
	  --EXEC [sCanteen].[pCanteenIssueDetailGet] @rId,@rCanteenId
END
GO
PRINT N'Altering Procedure [sLibrary].[pNewspaperLogAdd]...';


GO


ALTER PROCEDURE [sLibrary].[pNewspaperLogAdd]
	
	@rId int,
	@rNewspaperId Int,
	@rNewsPaperDate DateTime,
	@rNoOfCopy Smallint,
	@rLibraryId int,
	@rCreatedUserId bigint,
	@rNewspaperAmount decimal(12,2)
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

IF(@rId = 0)
    BEGIN
    
	INSERT INTO [sLibrary].[tNewspaperLog]
           ([NewspaperId]
           ,[NewspaperDate]
           ,[NoOfCopy]
           ,[CreatedUserId]
           ,[CreatedDateTime]
		   ,[NewspaperAmount])
     VALUES
           (@rNewspaperId
           ,@rNewspaperDate
           ,@rNoOfCopy
           ,@rCreatedUserId
           ,sCommon.fGetDate(),
		   @rNewspaperAmount)
	SELECT 1 AS Result
	End
	ELSE 
	BEGIN
	 
	UPDATE [sLibrary].[tNewspaperLog]
		SET [NewspaperId] = @rNewspaperId
		  ,[NewspaperDate] = @rNewspaperDate
		  ,[NoOfCopy] = @rNoOfCopy
		  ,[NewspaperAmount] = @rNewspaperAmount
	WHERE  AutoId = @rId
		 SELECT 11 AS Result
	END
end
GO
PRINT N'Altering Procedure [sLibrary].[pNewspaperLogGet]...';


GO

ALTER PROCEDURE [sLibrary].[pNewspaperLogGet]
	 @rId bigint = 0,
	 @rNewspaperId int,
	 @rFromNewspaperDate Date,
	 @rToNewspaperDate Date,
	 @rLibraryId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	
		SELECT NL.AutoId AS Id
		  ,NM.Title AS NewsPaperName
		  ,NM.Id AS NewsPaperId
		  ,NM.Language AS NewsPaperLanguage
		  ,NM.LibraryId AS LibraryId
		  ,LM.Title AS LibraryName
		  ,isnull(NM.NewsTypeId,0) AS NewsTypeId
		  ,NM.CreatedUserId AS CreatedUserId
		  ,NM.CreatedDateTime AS CreatedDateTime
		  ,NL.NewsPaperDate AS NewsPaperDate
		  ,NL.NoOfCopy AS NoOfCopy,
		   isnull(NL.NewspaperAmount,0) AS NewspaperAmount
		 
		  
	  FROM [sLibrary].[tNewspaperLog] NL 
	  inner Join [sLibrary].[tNewspaperMaster] NM On NL.NewspaperId=NM.Id
	  inner join [sLibrary].[tLibraryMaster] LM ON LM.Id=NM.LibraryId
	  where 
	  (LM.Id=@rLibraryId)
		AND ((NL.AutoId=@rId) OR (@rId=0))
		AND ((NL.NewspaperId=@rNewspaperId) OR (@rNewspaperId=0))
		AND (NL.NewspaperDate>=@rFromNewspaperDate OR ISNULL(@rFromNewspaperDate,'')='')
		AND (NL.NewspaperDate<=@rToNewspaperDate OR ISNULL(@rToNewspaperDate,'')='')
		--AND ((NM.CreatedUserId=@rCreatedUserId) OR (@rCreatedUserId=0))
	--order By NL.NewsPaperDate
END
GO
PRINT N'Altering Procedure [sSetup].[pOrgGroupMasterAdd]...';


GO
ALTER PROCEDURE [sSetup].[pOrgGroupMasterAdd]
	 
	@rId int,
	@rCode nvarchar(20),
	@rTitle nvarchar(100),
	@rCRMLicenceNo int,
	@rPunchLine nvarchar(200),
	@rOrgGroupBatchName nvarchar(20),
	@rFromDate date,
	@rToDate date,
	@rIsWindowSupport bit=0,
	@rIsSync bit = 0,
	@rChannelPartner nvarchar(200),		--add for 26991 mantis
	@rBrandConfig nvarchar(max)			--add for 26991 mantis
	
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	 
	IF(@rId=0)
		BEGIN
		IF EXISTS(SELECT 1 FROM sSetup.tOrgGroupMaster WHERE Code=@rCode)
		BEGIN
			SELECT -100 AS Result
			RETURN
		END
	END
	
	BEGIN TRANSACTION	
		IF(@rId=0)
		BEGIN
		
			DECLARE @lOrgGroupId smallint
			DECLARE @lOrgGroupBatchId int
			SET @lOrgGroupId=@rCode
			DECLARE @lCreatedUserId Int
			SET @lCreatedUserId = 1
			DECLARE @lCreatedDateTime DATETIME
			SET @lCreatedDateTime =  sCommon.fGetDate()
			
	
			
			INSERT INTO sSetup.tOrgGroupMaster
			(
				Id,
				Code,
				Title,
				PunchLine,
				CRMLicenceNo,
				IsWindowSupport,
				ChannelPartner,				--add for 26991 mantis
				BrandConfig					--add for 26991 mantis
			)
			VALUES
			(
				@rCode,
				@rCode,
				@rTitle,
				@rPunchLine,
				@rCRMLicenceNo,
				@rIsWindowSupport,
				@rChannelPartner,			--add for 26991 mantis
				@rBrandConfig				--add for 26991 mantis
			)
			
	 IF ( @rIsSync = 0)
 BEGIN
			
			SELECT @lOrgGroupBatchId=ISNULL(MIN(Id),(Convert(nvarchar(10),Convert(nvarchar(10),@lOrgGroupId))+'00000')+1) 
			FROM sSetup.tOrgGroupBatchMaster WHERE OrgGroupId=@lOrgGroupId
			
					 INSERT INTO [sSetup].[tOrgGroupBatchMaster]
					   ([Id]
					   ,[Code]
					   ,[Title]
					   ,[OrgGroupId]
					   ,[IsHistory]
					   ,[IsActive]
					   ,[IsObsolete]
					   ,[OrderIndex]
					   ,[FromDate]
					   ,[ToDate]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime])
			     
				  SELECT @lOrgGroupBatchId
					   ,@rOrgGroupBatchName
					   ,@rOrgGroupBatchName
					   ,@lOrgGroupId
					   ,0
					   ,1
					   ,0
					   ,1
					   ,@rFromDate
					   ,@rToDate
					  ,@lCreatedUserId
					  ,@lCreatedDateTime
					  ,@lCreatedUserId
					  ,@lCreatedDateTime
 END
			
			 INSERT INTO [sSecurity].[tRole]
				   ([Id]
				   ,[Title]
				   ,[Remark]
				   ,[IsObsolete]
				   ,[CreatedUserId]
				   ,[CreatedDateTime]
				   ,[UpdatedUserId]
				   ,[UpdatedDateTime]
				   ,[IsSystem]
				   ,[OrgGroupId])
			SELECT
				   sCommon.fGetIntIdForOrg(Id,@lOrgGroupId)
				  ,[Title]
				  ,[Remark]
				  ,[IsObsolete]
				  ,@lCreatedUserId
				  ,@lCreatedDateTime
				  ,@lCreatedUserId
				  ,@lCreatedDateTime
				  ,[IsSystem]
				  ,@lOrgGroupId
			  FROM [sSecurity].[tRole] WHere OrgGroupId=1          

			INSERT INTO [sFinanceCommon].[tStockUomMaster]
					   ([Id]
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId])
				 SELECT
						 sCommon.fGetIntIdForOrg(6,@lOrgGroupId)
						,'Nos'
						,'Nos'
						,0
						,@lCreatedUserId
						,@lCreatedDateTime
						,@lCreatedUserId
						,@lCreatedDateTime
						,@lOrgGroupId
				 --FROM [sFinanceCommon].[tStockUomMaster] WHERE OrgGroupId=1
	
	
	
				INSERT INTO [sStudent].[tAdmissionCategoryMaster]
					   ([Id]
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId])
				 SELECT
						 sCommon.fGetIntIdForOrg(1,@lOrgGroupId)
						,'General'
						,'General'
						,0
						,@lCreatedUserId
						,@lCreatedDateTime
						,@lCreatedUserId
						,@lCreatedDateTime
						,@lOrgGroupId
				 --FROM [sStudent].[tAdmissionCategoryMaster]
				 -- WHERE OrgGroupId=1

				
				
				INSERT INTO [sCommon].[tTableRowId]
					   ([OrgId]
					   ,[TableTitle]
					   ,[CurrentNumber]
					   ,[Seed]
					   ,[Increment])
				SELECT
					   @lOrgGroupId
					  ,[TableTitle]
					  
					  ,CASE 
				  WHEN IdDataType = 'int' THEN sCommon.fGetIntIdForOrg(ISNULL(CurrentNumber,0),@lOrgGroupId) 
				  WHEN IdDataType = 'bigint' THEN sCommon.fGetbigIntIdForOrg(ISNULL(CurrentNumber,0),@lOrgGroupId )
					END 
					
					  ,CASE 
				  WHEN IdDataType = 'int' THEN sCommon.fGetIntIdForOrg(ISNULL(CurrentNumber,0),@lOrgGroupId) 
				  WHEN IdDataType = 'bigint' THEN sCommon.fGetbigIntIdForOrg(ISNULL(CurrentNumber,0),@lOrgGroupId )
					END  
					   ,1
				  FROM [sCommon].[tTableRowIdMaster] where 
				  (tableType = 'Non Static' or tableType = '100Static' )
				  AND iddatatype <> '' AND IdScope = 'OrgGroup'

				
			--		INSERT INTO [sCommon].[tTableRowId]
			--           ([OrgId]
			--           ,[TableTitle]
			--           ,[CurrentNumber]
			--           ,[Seed]
			--           ,[Increment])
			--SELECT
			--		   0
			--		  ,[TableTitle]		  
			--		  ,CurrentNumber		
			--		  ,CurrentNumber
			--		  ,1
			--	  FROM [sCommon].[tTableRowIdMaster] where 
			--	  (tableType =   '100StaticCommon') 
			--	  AND IdScope = 'OrgGroup' 
			
			
			
				INSERT INTO sCommunication.tCircularTypeMaster
					   ([Id]
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId]
					   ,HighlightColor)
				 SELECT
						 sCommon.fGetIntIdForOrg(1,@lOrgGroupId)
						,'Circular'
						,'Circular'
						,0
						,@lCreatedUserId
						,@lCreatedDateTime
						,@lCreatedUserId
						,@lCreatedDateTime
						,@lOrgGroupId
						,'#0000CC'
						
				INSERT INTO sCommunication.tCircularTypeMaster
					   ([Id]
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId]
					   ,HighlightColor)
				 SELECT
						 sCommon.fGetIntIdForOrg(2,@lOrgGroupId)
						,'Alert'
						,'Alert'
						,0
						,@lCreatedUserId
						,@lCreatedDateTime
						,@lCreatedUserId
						,@lCreatedDateTime
						,@lOrgGroupId
						,'#006600'



			INSERT INTO sCommon.tReligionMaster
					   ([Id]
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId]
					    )
				 SELECT						
						sCommon.fGetIntIdForOrg(ID,@lOrgGroupId)
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,@lOrgGroupId
					   FROM sCommon.tReligionMaster WHERE OrgGroupId = 1
						

			INSERT INTO [sFrontOffice].[tVisitorTypeMaster]
					   (
						[Id]
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId]
					   )
				 SELECT
					  
					   sCommon.fGetIntIdForOrg(ID,@lOrgGroupId)
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,@lOrgGroupId
					   FROM [sFrontOffice].[tVisitorTypeMaster] WHERE OrgGroupId = 1
					   
					   
 			
				INSERT INTO sAdmission.tInquiryStatusMaster
					   ([Id]
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId]
					   ,StatusId
					   )

				 SELECT
						 sCommon.fGetIntIdForOrg(Id,@lOrgGroupId)
						,Title
						,Title
						,0
						,1
						,sCommon.fGetDate()
						,NULL
						,NULL
						,@lOrgGroupId
						,Id	
						FROM sCommon.tStatusMaster SM
						where statustypeId = 104

				UPDATE ISM  
						SET ISM.HighlightColor = ISM1.HighlightColor ,
							ISM.InquiryMainStatusId = ISM1.InquiryMainStatusId
						FROM sAdmission.tInquiryStatusMaster ISM  
						INNER JOIN sAdmission.tInquiryStatusMaster ISM1 ON ISM.StatusId = ISM1.StatusId AND ISM1.OrgGroupId = 1
						INNER JOIN sCommon.tStatusMaster SM ON SM.Id = ISM1.StatusId 
						where SM.statustypeId = 104 AND ISM.OrgGroupId = @lOrgGroupId
						
				 Insert Into sCommunication.tFeedbackAnswerTypeMaster
				(
						Id,
						Code,
						Title,
						IsObsolete,
						OrgGroupId,
						CreatedUserId,
						CreatedDateTime,
						UpdatedUserId,
						UpdatedDateTime,
						FeedBackAnswerGroupMasterId
				)
				Values
				(
						sCommon.fGetIntIdForOrg(1,@lOrgGroupId),
						'Numeric',
						'Numeric',
						0,
						@lOrgGroupId,
						@lCreatedUserId,
						@lCreatedDateTime,
						NULL,
						NULL,
						2
				)
						
				Insert Into sCommunication.tFeedbackAnswerTypeMaster
				(
						Id,
						Code,
						Title,
						IsObsolete,
						OrgGroupId,
						CreatedUserId,
						CreatedDateTime,
						UpdatedUserId,
						UpdatedDateTime,
						FeedBackAnswerGroupMasterId
				)
				Values
				(
						sCommon.fGetIntIdForOrg(2,@lOrgGroupId),
						'Free Text',
						'Free Text',
						0,
						@lOrgGroupId,
						@lCreatedUserId,
						@lCreatedDateTime,
						NULL,
						NULL,
						3
				)
						
	DECLARE @lMaxId int
	SET @lMaxId = sCommon.fGetIntIdForOrg(213,@lOrgGroupId)					
	EXECUTE sCommon.pTableRowIdModify @lOrgGroupId, @rTableTitle = 'sAdmission.tInquiryStatusMaster', @rCurrentNumber = @lMaxId
						
 
					   
		END
		ELSE
		BEGIN
			UPDATE sSetup.tOrgGroupMaster
			SET
				Title=@rTitle,
				PunchLine=@rPunchLine,
				CRMLicenceNo=@rCRMLicenceNo,
				IsWindowSupport=@rIsWindowSupport,
				ChannelPartner=@rChannelPartner,				--add for 26991 mantis
				BrandConfig=@rBrandConfig						--add for 26991 mantis
				
			  WHERE Id = @rId 
	    END
	      
	  
	  
	  
	  IF NOT EXISTS(SELECT 1 FROM sCommon.tCastCategoryMaster WHERE OrgGroupId = @rCode)
BEGIN				
				INSERT INTO sCommon.tCastCategoryMaster
					   ([Id]
					   ,[Number]
					   ,[Code]
					   ,[Title]
					   ,[IsObsolete]
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId]
					   , MainCastCategoryId 
					   ,OrderIndex)
				 SELECT
						 sCommon.fGetIntIdForOrg(Id,@rCode)
						,Id	
						,Code
						,Title
						,0
						,1
						,sCommon.fGetDate()
						,1
						,sCommon.fGetDate()
						,@rCode
						,Id
						,Id						
						FROM sCommon.tMainCastCategoryMaster
						 
						
	DECLARE @lMaxCatId int
	SET @lMaxCatId = sCommon.fGetIntIdForOrg(100,@rCode)					
	EXECUTE sCommon.pTableRowIdModify @rCode, @rTableTitle = 'sCommon.tCastCategoryMaster', @rCurrentNumber = @lMaxCatId


			
			
END
	   

IF NOT EXISTS(SELECT 1 FROM sSetup.tBatchGroupMaster WHERE OrgGroupId = @rCode)
BEGIN				
				INSERT INTO sSetup.tBatchGroupMaster
					   ([Id]
					   ,[Number]
					   ,[Code]
					   ,[Title]					  
					   ,[CreatedUserId]
					   ,[CreatedDateTime]
					   ,[UpdatedUserId]
					   ,[UpdatedDateTime]
					   ,[OrgGroupId]					   
					   ,OrderIndex)
				 SELECT
						 sCommon.fGetIntIdForOrg(1,@rCode)
						,1	
						,'Common'
						,'Common'					
						,1
						,sCommon.fGetDate()
						,1
						,sCommon.fGetDate()
						,@rCode						 
						,1						
						 
						 
END						
	 


			
			

	      
	      SELECT @rCode AS Result	
		COMMIT TRANSACTION
		
		--  exec  [sCommon].[pOrganizationSetupAdd] @rId
		
	 
		
END
GO
PRINT N'Altering Procedure [sSetup].[pOrgGroupMasterGet]...';


GO
ALTER PROCEDURE [sSetup].[pOrgGroupMasterGet]
	 
	 @rId int,
	 @rTitle nvarchar(100),
	 @rCode nvarchar(20),
	 @rStatus nvarchar(5)
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	BEGIN
		SET @rTitle = sCommon.fSearchStringGet(@rTitle)
		SET @rCode = sCommon.fSearchStringGet(@rCode)
		
		DECLARE @lIsObsolete bit = 0
			If(@rStatus='1')
		SET @lIsObsolete = 0
	else If(@rStatus='2')
		SET @lIsObsolete = 1
		--SET @rStatus = sCommon.fSearchStringGet(@rStatus)
	END
	SELECT 
		OB.Id,
		OB.Code,
		OB.Title,
		OB.CRMLicenceNo,
		ISNULL(OB.PunchLine,'') AS PunchLine,
		ISNULL(OB.IsWindowSupport,0) AS IsWindowSupport,
		ISNULL(OB.IsObsolete,0) AS IsObsolete,
		ISNULL(OB.ChannelPartner,'') AS ChannelPartner,			--add for 26991 mantis
		ISNULL(OB.BrandConfig,'') AS BrandConfig				--add for 26991 mantis
		
  FROM 
        sSetup.tOrgGroupMaster OB
  WHERE 
		((OB.Id=@rId) OR (@rId=0))
	AND (OB.Id!=1)
	AND ((OB.Code like @rCode) OR (ISNULL(@rCode,'')='')) 
	AND ((OB.Title like @rTitle) OR (ISNULL(@rTitle,'')='')) 
	AND ((ISNULL(OB.IsObsolete,0) = @lIsObsolete)OR( @rStatus=0))	
	--AND((ISNULL(OB.IsObsolete,0)=@rStatus))
END
GO
PRINT N'Altering Procedure [sAlert].[pIssueBookAllGet]...';


GO
ALTER PROCEDURE [sAlert].[pIssueBookAllGet]
	 
	 @rSourceTypeId smallint,
	 @rFromDate date,
	 @rToDate date,
	 @rOrgId smallint,
	 @rBatchId int,
	 @rDepartmentId int = 0,
	 @rClassId int = 0,
	 @rDivisionId int = 0,
	 @rTemplate nvarchar(max),	 	
	 @rMailSubject nvarchar(max)=''
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			
BEGIN 	
		SELECT distinct
			SC.OrgGroupId,
			SC.OrgId,
			SC.AcademicId,
			SC.Code,
			SC.Name AS Name,
			SC.ParentContactNo AS MobileNo,	
			SC.ParentEmail AS EmailId,
			SC.WhatsAppContactNo,
			replace (
			replace (
			replace(@rTemplate,'[Name]',SC.Name),			
			'[BookCode - BookName - IssueDate]',(SELECT STUFF ((SELECT distinct ',' +' ' + BM1.Code +' - '+BM1.Title+' - '+ CONVERT(nvarchar(100), BL1.LogDate, 103)
					From sLibrary.tBookLog BL1
					INNER JOIN sLibrary.tBookMaster BM1 ON BM1.Id=BL1.ResourceSourceId
					INNER JOIN sStudent.vStudentCommunication SC1 ON SC1.StudentId = BL1.ReaderSourceId AND SC1.BatchId = BL1.BatchId  AND BL1.ReaderSourceTypeId=2003
					INNER JOIN sSetup.tBatchMaster BMM1 ON BMM1.Id = BL1.BatchId 
					INNER JOIN sSetup.tOrganizationMaster OM1 ON OM1.Id = BL1.OrgId
					INNER JOIN sLibrary.tLibraryMaster LM1 ON LM.Id=BL1.LibraryId
					WHERE
							(BL1.ReaderSourceTypeId=@rSourceTypeId OR @rSourceTypeId=0)
						AND ((BL1.LogDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
						AND ((BL1.LogDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
						AND (BL1.OrgId = @rOrgId)
						AND (BL1.BatchId = @rBatchId)
						AND (BL1.ReaderSourceTypeId != 0)
						AND ((SC1.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
						AND ((SC1.ClassId=@rClassId) OR (@rClassId=0))
						AND ((SC1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
						--AND BL1.IsCurrentStatus=1 
						AND BL1.StatusId = 523
						AND BL1.ReaderSourceId=BL.ReaderSourceId	
						AND (SC.StatusId=1) AND SC.IsBlockAllAlert=0	
                    FOR XML PATH('')),1,1,''))),
			'[LibraryName]',LM.Title)
			 AS Template,
			replace (
			replace (
			replace(@rTemplate,'[Name]',SC.Name),			
			'[BookCode - BookName - IssueDate]',(SELECT STUFF ((SELECT distinct ',' +' ' + BM1.Code +' - '+BM1.Title+' - '+ CONVERT(nvarchar(100), BL1.LogDate, 103)
					From sLibrary.tBookLog BL1
					INNER JOIN sLibrary.tBookMaster BM1 ON BM1.Id=BL1.ResourceSourceId AND BL1.ReaderSourceTypeId=2003
					AND BL1.ReaderSourceId=BL.ReaderSourceId  AND BL1.ReaderSourceId = SC.StudentId AND BL1.BatchId =SC.BatchId  
					AND BMM.Id = BL1.BatchId  AND BL1.OrgId = OM.Id AND  BL1.LibraryId =LM.Id
					AND (SC.StatusId=1) AND SC.IsBlockAllAlert=0
					AND BL1.StatusId = 523
					--AND BL1.IsCurrentStatus=1 		
                    FOR XML PATH('')),1,1,'') )),
			'[LibraryName]',LM.Title)
			 AS MsgText,
			'2003' AS SourceTypeId,
			SC.StudentId AS SourceId,
			@rMailSubject AS Subject,
			2003 AS ReceiverSourceTypeId,
			SC.StudentId AS ReceiverSourceId,
			LM.Title AS LibraryName
	From 
		sLibrary.tBookLog BL
		INNER JOIN sLibrary.tBookMaster BM ON BM.Id=BL.ResourceSourceId
		INNER JOIN sStudent.vStudentCommunication SC ON SC.StudentId = BL.ReaderSourceId AND SC.BatchId = BL.BatchId  AND BL.ReaderSourceTypeId=2003
		INNER JOIN sSetup.tBatchMaster BMM ON BMM.Id = BL.BatchId 
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = BL.OrgId
		INNER JOIN sCommon.tStatusMaster SM ON SM.Id=BL.StatusId
		INNER JOIN sLibrary.tLibraryMaster LM ON LM.Id=BL.LibraryId
	WHERE
		(BL.ReaderSourceTypeId=@rSourceTypeId OR @rSourceTypeId=0)
		AND ((BL.LogDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((BL.LogDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
				AND (BL.OrgId = @rOrgId OR @rOrgId=0)
		AND (BL.BatchId = @rBatchId OR @rBatchId = 0)
		AND (BL.ReaderSourceTypeId != 0)
		AND ((SC.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND ((SC.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SC.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND (SC.StatusId=1) AND SC.IsBlockAllAlert=0	
		AND BL.StatusId = 523
		--AND BL.IsCurrentStatus=1 

	UNION 

	SELECT distinct
			OM.OrgGroupId,
			EC.OrgId,
			EC.EmployeeId As AcademicId,
			EC.Code,
			EC.EmployeeName AS Name,
			EC.MobileNumber AS MobileNo,	
			EC.CompanyEmail AS EmailId,
			EC.WhatsAppContactNo,
			replace (
			replace (
			replace(@rTemplate,'[Name]',EC.EmployeeName),			
			'[BookCode - BookName - IssueDate]',(SELECT STUFF ((SELECT distinct ',' +' ' + BM1.Code +' - '+BM1.Title+' - '+ CONVERT(nvarchar(100), BL1.LogDate, 103)
						From sLibrary.tBookLog BL1
							INNER JOIN sLibrary.tBookMaster BM1 ON BM1.Id=BL1.ResourceSourceId AND BL1.ReaderSourceTypeId=95
							AND BL1.ReaderSourceId=BL.ReaderSourceId  AND BL1.ReaderSourceId = EC.EmployeeId 
							AND BL1.OrgId = OM.Id AND  BL1.LibraryId =LM.Id AND ODM.Id = EC.DepartmentId
							AND ISNULL(EC.IsOtherUser,0) = 0  AND EC.StatusId = 7
							--AND BL1.IsCurrentStatus=1
							AND BL1.StatusId = 523
                    FOR XML PATH('')),1,1,'') )),
			'[LibraryName]',LM.Title)
			 AS Template,
			replace (
			replace (
			replace(@rTemplate,'[Name]',EC.EmployeeName),			
			'[BookCode - BookName - IssueDate]',(SELECT STUFF ((SELECT distinct ',' +' ' + BM1.Code +' - '+BM1.Title+' - '+ CONVERT(nvarchar(100), BL1.LogDate, 103)
						From sLibrary.tBookLog BL1
							INNER JOIN sLibrary.tBookMaster BM1 ON BM1.Id=BL1.ResourceSourceId AND BL1.ReaderSourceTypeId=95
							AND BL1.ReaderSourceId=BL.ReaderSourceId  AND BL1.ReaderSourceId = EC.EmployeeId 
							AND BL1.OrgId = OM.Id AND  BL1.LibraryId =LM.Id AND ODM.Id = EC.DepartmentId
							AND ISNULL(EC.IsOtherUser,0) = 0  AND EC.StatusId = 7
							AND BL1.StatusId = 523
							--AND BL1.IsCurrentStatus=1	
                    FOR XML PATH('')),1,1,'') )),
			'[LibraryName]',LM.Title)
			 AS MsgText,
			'95' AS SourceTypeId,
			EC.EmployeeId AS SourceId,
			@rMailSubject AS Subject,
			95 AS ReceiverSourceTypeId,
			EC.EmployeeId AS ReceiverSourceId,
			LM.Title AS LibraryName
	         
	FROM 
		sLibrary.tBookLog BL
		INNER JOIN sLibrary.tBookMaster BM ON BM.Id=BL.ResourceSourceId
		INNER JOIN sHrm.vEmployeeCommunication EC ON EC.EmployeeId=BL.ReaderSourceId AND BL.ReaderSourceTypeId=95 
		INNER JOIN sSetup.tDepartmentMaster ODM ON ODM.Id = EC.DepartmentId
		INNER JOIN [sSetup].[tOrganizationMaster] OM ON OM.Id = EC.OrgId
		INNER JOIN sCommon.tStatusMaster SM ON SM.Id=BL.StatusId
		INNER JOIN sLibrary.tLibraryMaster LM ON LM.Id=BL.LibraryId

	WHERE 

		 (BL.OrgId = @rOrgId OR @rOrgId=0)
		AND (BL.BatchId = @rBatchId OR @rBatchId = 0)
		AND ((EC.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND ((BL.LogDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((BL.LogDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
		AND (BL.ReaderSourceTypeId=@rSourceTypeId OR @rSourceTypeId=0)
		AND ISNULL(EC.IsOtherUser,0) = 0  AND EC.StatusId = 7	
		AND BL.StatusId = 523
		--AND BL.IsCurrentStatus=1 
		 
END
GO
PRINT N'Altering Procedure [sAlert].[pNotificationLogAdd]...';


GO
ALTER PROCEDURE [sAlert].[pNotificationLogAdd]
	
	@rXML nvarchar(max),
	@rOrgGroupId smallint
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	
BEGIN
BEGIN TRY
	
	BEGIN TRANSACTION
	 
	SET @rXML=REPLACE(@rXML,'&','AND')
	DECLARE @lCount INT
	DECLARE @lInterrorCode INT,
			@intDoc INT
	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXML
	
	DECLARE @lNextNumber BIGINT  
	DECLARE @lFromNextNumber BIGINT  
	
	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sAlert.tNotificationLog', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	SET @lFromNextNumber=@lNextNumber-1
	
	INSERT INTO sAlert.tNotificationLog
		(
			Id,
			OSTypeId,
			SourceTypeId,
			SourceId,
			EventId,
			DocumentId,
			MessageTitle,
			NotificationMessage,
			APIResponse,
			StatusCode,
			CreatedUserId,
			CreatedDateTime,
			IsViewed
		)
	SELECT
			@lFromNextNumber + ROW_NUMBER() OVER(ORDER BY SourceId),
			OSTypeId,
			SourceTypeId,
			SourceId,
			EventId,
			DocumentId,
			MessageTitle,
			NotificationMessage,
			APIResponse,
			StatusCode,
			CreatedUserId,
			sCommon.fGetDate(),
			(CASE WHEN OSTypeId = 3 THEN 0 ELSE NULL END)IsViewed
			
	FROM OPENXML (@intDoc, '/Table/NotificationLog', 1)
	WITH
	(
			OSTypeId tinyint 'OSTypeId',
			SourceTypeId smallint 'SourceTypeId',
			SourceId bigint 'SourceId',
			EventId int 'EventId',
			DocumentId bigint 'DocumentId',
			MessageTitle nvarchar(200) 'MessageTitle',
			NotificationMessage nvarchar(max) 'NotificationMessage',
			APIResponse nvarchar(500) 'APIResponse',
			StatusCode nvarchar(50) 'StatusCode',
			CreatedUserId bigint 'CreatedUserId'
	)
	
		SELECT @lCount = COUNT(1) FROM OPENXML (@intDoc, '/Table/NotificationLog', 1)
			WITH (SourceId bigint)
				 
		SET @lNextNumber  = @lCount + @lFromNextNumber 
		
		EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sAlert.tNotificationLog', @rCurrentNumber = @lNextNumber 
		
		EXEC sp_xml_removedocument @intDoc
		
		SELECT 1 AS Result,@lFromNextNumber+1 AS FromId,@lNextNumber AS ToId
		
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Altering Procedure [sAlert].[pStudentBiometricFirstInTimeGet]...';


GO

ALTER PROCEDURE [sAlert].[pStudentBiometricFirstInTimeGet]
	@rTemplate nvarchar(max)='',
	@rSubject nvarchar(100)='',
	@rOrgId int,
	@rIntervalTime int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY	
		BEGIN TRANSACTION 
		declare @lDateTimeNow datetime=[sCommon].[fGetDate]()
			if object_id('tempdb..#tTable') is not null
			BEGIN
				DROP TABLE #tTable
			END
			CREATE TABLE #tTable(
					SourceId bigint NOT NULL,
					In_Out_Time [datetime] NOT NULL,
					SourceTypeId int NOT NULL,
					CreatedDateTime [datetime] NOT NULL,
				)
			insert into #tTable
			select CDT.SourceId,min(CDT.In_Out_Time),CDT.SourceTypeId,min(CDT.CreatedDateTime) 
			from [sBiometric].[tCloudTransactionData] CDT
			inner join sBiometric.tShiftMasterCloud SMC ON SMC.ShiftCode=CDT.ShiftCode
			WHERE CDT.Attn_Dt=CONVERT(date,@lDateTimeNow)  AND CDT.SourceTypeId=2003
				AND (SMC.IsStopInTimeAlertTime=0 OR (SMC.IsStopInTimeAlertTime=1 
				AND CAST(SMC.StopInTimeAlertTime as Time)>=CAST(DATEADD(minute,(@rIntervalTime),[sCommon].[fGetDate]()) AS Time)
				AND  CAST(SMC.StopInTimeAlertTime as Time)>=CAST(CDT.In_Out_Time AS Time)))
			 Group by CDT.SourceId,CDT.SourceTypeId
	
			--select * from #tTable
			SELECT 
				SC.ParentContactNo AS MobileNo,	
				SC.ParentEmail AS EmailId,
				replace (
				replace (
				replace (
				replace(@rTemplate,'[StudentName]',SC.Name),
				'[TimeStamp]',ISNULL(Convert(varchar,temp.In_Out_Time,108),'')),
				'[PresentTime]',ISNULL(Convert(varchar,temp.In_Out_Time,108),'')),
				'[PresentDate]',ISNULL(Convert(varchar,[sCommon].[fGetDate](),103),''))
				 AS MsgText,
				@rSubject AS Subject,
				2003 AS ReceiverSourceTypeId,
				SC.StudentId AS ReceiverSourceId,
				SC.Name AS StudentName,
				SC.Code AS Code,
				ISNULL(SC.WhatsAppContactNo,'') AS WhatsAppContactNo,
				2003 AS SourceTypeId,
				SC.StudentId AS SourceId,
				SC.OrgId,
				OM.OrgGroupId,
				ISNULL(Convert(varchar,temp.In_Out_Time,108),'') as Time,
				OM.DisplayTitle AS OrganizationTitle


			FROM 
				#tTable temp
				Inner join sStudent.vStudentCommunication SC ON temp.SourceId=SC.StudentId AND SC.IsActiveBatch=1
				INNER JOIN sSetup.tOrganizationMaster OM ON SC.OrgID = OM.Id
			WHERE
				temp.CreatedDateTime >= DATEADD(minute,-(@rIntervalTime),@lDateTimeNow)
				AND SC.OrgID =@rOrgId
				AND SC.StatusId = 1
			DROP TABLE #tTable
		COMMIT TRANSACTION
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH

END
GO
PRINT N'Altering Procedure [sAssessment].[pStudentwiseOptionalSubjectListGet]...';


GO
ALTER PROCEDURE [sAssessment].[pStudentwiseOptionalSubjectListGet]
                  
    @rDivisionId bigint,
    @rClassId bigint,
    @rSubjectId bigint ,
    @rBatchId int,
    @rBuilder1 nvarchar(max)='',
	@rBuilder2 nvarchar(max)='',
	@rClassStatusId int=0,
	@rStatusId int=1,
    @rViewMode smallint = 0,  -- 0 for Vertical and 1 for Horizontal
	@rSubjects nvarchar(max)=''
AS
SET NOCOUNT ON

BEGIN

	IF(@rViewMode = 1)
	BEGIN
		SELECT
			SA.AcademicId,
			CS.Id as ClassSubjectId,
			SA.Name as StudentName,
			CS.SubjectId, 
			SA.Code,
			SA.RollNo,
			SA.DivisionId,
			SA.ClassName,
			SA.DivisionName
			INTO #TempTable 
			FROM
			(
					SELECT * FROM sStudent.vStudentAcademic 
					where (DivisionId = @rDivisionId OR @rDivisionId = 0)  AND BatchId = @rBatchId
					AND (ClassStatusId = @rClassStatusId OR @rClassStatusId = 0) 
					AND (StatusId = @rStatusId OR @rStatusId = 0)) SA  
					CROSS JOIN
					(select * FROM sSetup.tClassSubject B   
					WHERE  B.IsOptional = 1 AND (B.ClassId = @rClassId OR @rClassId = 0)  
					AND  (B.Id = @rSubjectId OR @rSubjectId = 0) 
			) AS CS

		SELECT DISTINCT
			SS.AcademicId,
			Stuff ((select ',' + Convert(nvarchar(20),OS.ClassSubjectId) FROM sAssessment.tOptionalSubject OS WHERE AcademicId = SS.AcademicId FOR XML PATH('') ),1,1,'') AS ClassSubjectId,
			SS.StudentName,
			SS.Code,
			SS.RollNo,
			@rBuilder1 AS ClassName,
			@rBuilder2 AS DivisionName,
			SS.DivisionId,
			SS.ClassName AS ClsName,
			SS.DivisionName AS DivName,
			@rSubjects
			FROM
			#TempTable AS SS
			INNER JOIN sSetup.tSubjectMaster SM ON SS.SubjectId = SM.Id 
			LEFT JOIN sAssessment.tOptionalSubject OS ON OS.AcademicId = SS.AcademicId AND OS.ClassSubjectId = SS.ClassSubjectId

			DROP TABLE #TempTable
	END
	ELSE
	BEGIN
		SELECT 
		SS.AcademicId,
		SS.ClassSubjectId,
		SS.StudentName,
		SS.SubjectId,
		SM.Code as SubjectName,
		SS.Code,
		SS.RollNo,
		@rBuilder1 AS ClassName,
		@rBuilder2 AS DivisionName,
		SS.DivisionId,
		SS.ClassName AS ClsName,
		SS.DivisionName AS DivName,
		CASE WHEN isnull(OS.ClassSubjectId,0) <> 0 THEN 'true' ELSE 'false' END AS Selected
		FROM
		(SELECT
		SA.AcademicId,
		CS.Id as ClassSubjectId,
		SA.Name as StudentName,
		CS.SubjectId, 
		SA.Code,
		SA.RollNo,
		SA.DivisionId,
		SA.ClassName,
		SA.DivisionName
		FROM
		 (select * FROM sStudent.vStudentAcademic where (DivisionId=@rDivisionId OR @rDivisionId = 0)  AND BatchId=@rBatchId AND (ClassStatusId=@rClassStatusId OR @rClassStatusId=0) AND (StatusId=@rStatusId OR @rStatusId=0))SA  
		 CROSS JOIN    (select * FROM sSetup.tClassSubject B   WHERE  B.IsOptional = 1 AND (B.ClassId = @rClassId OR @rClassId = 0)  AND  (B.Id= @rSubjectID OR @rSubjectId= 0) 
		) AS CS 
		 ) AS SS
		 INNER JOIN sSetup.tSubjectMaster SM ON SS.SubjectId = SM.Id 
		 LEFT JOIN sAssessment.tOptionalSubject OS ON OS.AcademicId = SS.AcademicId AND OS.ClassSubjectId = SS.ClassSubjectId

		ORDER BY SS.RollNo,SM.OrderIndex
	   END
END
GO
PRINT N'Altering Procedure [sAttendance].[pStudentLeaveRequestGet]...';


GO
ALTER PROCEDURE [sAttendance].[pStudentLeaveRequestGet] 
	 
	 	@rAcademicId bigint,
        @rLeaveFromDate date='',
        @rLeaveToDate date='' ,
        @rOrgID int=0,
        @rBatchId int=0
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	 
	Select
		SL.AcademicId,
		ISNULL(SL.LeaveFromDate,'')AS LeaveFromDate,
		ISNULL(SL.LeaveToDate,'')AS LeaveToDate,
		SL.LeaveReason,
		SM.Code As IdNo,
		SM.FirstName+' ' +SM.MiddleName+' ' +SM.LastName As Name,
		ISNULL(SL.StatusId,0) AS StatusId,  
      ISNULL(S.Title,'Pending') AS StatusName,  
      ISNULL(SL.StatusReason,'') AS  StatusReason
		
	from sAttendance.tStudentLeaveRequest SL
	INNER JOIN sStudent.tAcademic A ON A.Id=SL.AcademicId
	INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
	LEFT JOIN sCommon.tStatusMaster S ON S.Id = SL.StatusId 
	Where ((AcademicId=@rAcademicId) OR (@rAcademicId=0))
	AND ((LeaveFromDate >= @rLeaveFromDate) OR (ISNULL(@rLeaveFromDate,'')=''))
	AND ((LeaveToDate <= @rLeaveToDate) OR (ISNULL(@rLeaveToDate,'')=''))
	AND ((SM.OrgId=@rOrgID)OR(@rOrgID=0))
	AND ((A.BatchId=@rBatchId)OR(@rBatchId=0))
	
END
GO
PRINT N'Altering Procedure [sAttendance].[pStudentRegisterXMLAdd]...';


GO
ALTER PROCEDURE [sAttendance].[pStudentRegisterXMLAdd]
	(
		@rXmlDoc nvarchar(MAX)
	)
AS

SET NOCOUNT ON

BEGIN
SET @rXmlDoc=REPLACE(@rXmlDoc,'&','AND')
DECLARE @lInterrorCode INT,
			@intDoc INT
BEGIN TRY
BEGIN TRANSACTION	
	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXmlDoc	
	-----------------------
	DELETE SA FROM sAttendance.tStudentRegister SA INNER JOIN 
	(SELECT AcademicId,AttendanceDate FROM OPENXML (@intDoc, '/Table/StudentAttendace', 1)
	WITH
	(		
		AcademicId BigINT '@Id',
		AttendanceDate DateTime '@AttendanceDate'
	))A ON  SA.AcademicId = A.AcademicId AND SA.AttendanceDate=A.AttendanceDate AND SA.AttendanceTypeId = 1
		
	
	INSERT INTO sAttendance.tStudentRegister
	(
		AcademicId,
		AttendanceDate,
		IsPresent,
		AttendanceTypeId,
		CreatedUserId,
		CreatedDateTime	
	)
	SELECT
		AcademicId,
		AttendanceDate,
		IsPresent,
		1,
		3,		--they want Admin in created user id so put 3 insted of 1 because 1 stands for SysSuperAdmin and 3 stands for Admin. Mantis id 26812
		sCommon.fGetDate()	
		
	FROM OPENXML (@intDoc, '/Table/StudentAttendace', 1)
	WITH
	(		
		AcademicId BigINT '@Id',
		AttendanceDate DateTime '@AttendanceDate',
		IsPresent Smallint '@Present'
	)A
	
	
	EXEC sp_xml_removedocument @intDoc
	COMMIT TRANSACTION
	SELECT 1
	END TRY
	BEGIN CATCH
	SELECT -1
	END CATCH

END
GO
PRINT N'Altering Procedure [sAttendance].[pUpdateResultAttendance]...';


GO

ALTER PROCEDURE [sAttendance].[pUpdateResultAttendance]
	 	 
		 @rClassId int=0,
		 @rDivisionId int=0,
		 @rStudentCode nvarchar(20)='',	
	 	 @rAttendanceTypeId tinyint=1,	 	 
		 @rFromAttendanceDate date='',
		 @rToAttendanceDate date='',
	 	 @rBatchId int,
	 	 @rOrgId int,
	 	 @rExamId int,
	 	 @rExamSubGroupId int=0
	  
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
 
 

BEGIN 
 
		
	CREATE TABLE #tResultAttendance
	 (
	 ExamId int,  
	 AcademicId  bigint,	 
	 PresentCount int,
	 AbsentCount int 
	 )

   INSERT INTO #tResultAttendance
   (ExamId,AcademicId,PresentCount,AbsentCount)
   	SELECT
   	EM.Id, 
   	SA.Id,
	   ISNULL((SELECT count(AcademicId) FROM sAttendance.tStudentRegister SR	 
		WHERE		 
		 SR.AttendanceDate >= @rFromAttendanceDate
		AND SR.AttendanceDate <= @rToAttendanceDate
		AND 
		SR.AcademicId = SA.Id
		AND SR.IsPresent = 1
		AND SR.AttendanceTypeId = @rAttendanceTypeId				
		) ,0)
		 AS PresentCount
		 , 
	   ISNULL((SELECT count(AcademicId) FROM sAttendance.tStudentRegister SR	 
		WHERE		 
		 SR.AttendanceDate >= @rFromAttendanceDate
		AND SR.AttendanceDate <= @rToAttendanceDate		
		AND 
		SR.IsPresent = 0
		AND SR.AttendanceTypeId = @rAttendanceTypeId
		AND SR.AcademicId = SA.Id		
		),0)
		 AS AbsentCount 		
  FROM 
		 sStudent.tAcademic SA 
		 INNER JOIN sStudent.tStudentMaster SM ON SM.Id=SA.StudentId
		 INNER JOIN sAssessment.tExamMaster EM ON SA.BatchId = EM.BatchId 
		 AND SA.ClassId = EM.ClassId
		 AND EM.ExamSubGroupId = 	@rExamSubGroupId
		 AND EM.BatchId = @rBatchId	 
		 INNER JOIN sAssessment.tExamStatus ES ON ES.ExamId = EM.Id AND ES.DivisionId = SA.DivisionId 
         WHERE
   --       ((SM.Code=@rStudentCode) OR (@rStudentCode=''))         
		 --AND 
		 ((SA.ClassId=@rClassId) OR (@rClassId=0))
	     AND ((SA.DivisionId=@rDivisionId) OR (@rDivisionId=0)) 
         AND (SA.OrgId = @rOrgId)
         AND (SA.BatchId = @rBatchId)
		 --AND ES.StatusId <> 504 -- commented by sarangi if result is generated then attendance not Updated - mantisid - 0026854
        
  
 -- Select * from #tResultAttendance
    INSERT INTO sAssessment.tStudentRemark
    (ExamId,AcademicId,PresentDays,TotalDays)
    SELECT RA.ExamId,RA.AcademicId,CONVERT(decimal(6, 2),PresentCount),CONVERT(decimal(6, 2),AbsentCount) + CONVERT(decimal(6, 2),PresentCount)
    --,SR.AcademicId,SR.ExamId
    FROM #tResultAttendance RA LEFT JOIN sAssessment.tStudentRemark SR
    ON RA.AcademicId = SR.AcademicId AND RA.ExamId = SR.ExamId
    WHERE SR.AcademicId IS NULL   AND SR.ExamId IS NULL
    
   
    
    UPDATE SR
    SET PresentDays = CONVERT(decimal(6, 2),RA.PresentCount),
         TotalDays = CONVERT(decimal(6, 2),RA.PresentCount) + CONVERT(decimal(6, 2),RA.AbsentCount)
    FROM sAssessment.tStudentRemark SR INNER JOIN
    #tResultAttendance RA 
    ON  SR.ExamId = RA.ExamId AND SR.AcademicId = RA.AcademicId
    --INNER JOIN sAssessment.tExamMaster EM ON EM.Id = SR.ExamId
    --WHERE EM.ExamSubGroupId = @rExamSubGroupId
 
 --Select * from sAssessment.tStudentRemark Order by UpdatedDateTime desc
 SELECT 1 AS RESULT
END
GO
PRINT N'Altering Procedure [sAudit].[pActivityLogGet]...';


GO


ALTER PROCEDURE [sAudit].[pActivityLogGet]
	@rOrgGroupId int,
	@rOrgId int,
	@rUserName nvarchar(100),
	@rScreenName nvarchar(100),
	@rActivity nvarchar(500),
	@rFromDate date,
	@rToDate date,
	@rOSTypeId tinyint=0,
	@rIsHistory bit = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
SET @rScreenName = sCommon.fSearchStringGet(@rScreenName)
SET @rActivity = sCommon.fSearchStringGet(@rActivity)
SET @rToDate = DATEADD(day,1,@rToDate)

	IF(@rIsHistory = 1)
	BEGIN
       SELECT 
            AL.OrgGroupId,
            AL.OrgId,
            OM.Title AS OrganizationName,
            AL.UserId,
			U.DisplayTitle AS UserName,
			U.LoginTitle,
			U.SessionId,
			S.Title AS ScreenName,
			AL.AuditDate,
			(CASE WHEN AL.ScreenId=126
				  THEN AL.Activity		
			      WHEN AL.ScreenId=20878
				  THEN AL.Activity		
				  WHEN AL.ScreenId=30179
				  THEN AL.Activity
				  WHEN AL.ScreenId=20196
				  THEN AL.Activity
				  WHEN AL.ScreenId=30788
				  THEN AL.Activity
				  WHEN AL.ScreenId=30780
				  THEN AL.Activity
				  WHEN AL.ScreenId=30790
				  THEN AL.Activity
				  WHEN AL.ScreenId=20745
				  THEN AL.Activity
				  WHEN AL.ScreenId=31257
				  THEN AL.Activity
			 ELSE SUBSTRING(AL.Activity, CHARINDEX(' ', AL.Activity)+1, LEN(AL.Activity)-(CHARINDEX(' ', AL.Activity)-1))  END)AS  Activity,			
			ISNULL(AL.RecordSourceId,0) AS RecordSourceId,
			ISNULL(AL.RecordDetail,'') AS RecordDetail,
			ISNULL(OS.Title,'') AS OSType
			
       FROM 
		   sAudit.tActivityLogHistory AL
		   INNER JOIN sSecurity.tUser U ON U.Id = AL.UserId
		   INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = AL.OrgId
		   INNER JOIN sSecurity.tScreen S ON S.Id = AL.ScreenId
		   LEFT JOIN sAlert.tOSType OS ON OS.Id = AL.OSTypeId
       WHERE
		   AL.OrgGroupId = @rOrgGroupId
		   AND ((AL.OrgId = @rOrgId) OR (@rOrgId=0))
		   AND ((U.DisplayTitle = @rUserName) OR (@rUserName = ''))
		   AND ((S.Title LIKE @rScreenName) OR (@rScreenName = ''))
		   AND ((AL.Activity LIKE @rActivity) OR (@rActivity = ''))
		   AND (AL.AuditDate >= @rFromDate)
		   AND (AL.AuditDate <= @rToDate)
		   AND ((AL.OSTypeId = @rOSTypeId) OR (@rOSTypeId=0))
       ORDER BY 
			AL.AuditDate DESC
	END
	ELSE
	BEGIN
		SELECT 
            AL.OrgGroupId,
            AL.OrgId,
            OM.Title AS OrganizationName,
            AL.UserId,
			U.DisplayTitle AS UserName,
			U.LoginTitle,
			U.SessionId,
			S.Title AS ScreenName,
			AL.AuditDate,
			(CASE WHEN AL.ScreenId=126
				  THEN AL.Activity		
			      WHEN AL.ScreenId=20878
				  THEN AL.Activity		
				  WHEN AL.ScreenId=30179
				  THEN AL.Activity
				  WHEN AL.ScreenId=20196
				  THEN AL.Activity
				  WHEN AL.ScreenId=30788
				  THEN AL.Activity
				  WHEN AL.ScreenId=30780
				  THEN AL.Activity
				  WHEN AL.ScreenId=30790
				  THEN AL.Activity
				  WHEN AL.ScreenId=20884
				  THEN AL.Activity
				  WHEN AL.ScreenId=20745
				  THEN AL.Activity
				  WHEN AL.ScreenId=31257
				  THEN AL.Activity
			 ELSE SUBSTRING(AL.Activity, CHARINDEX(' ', AL.Activity)+1, LEN(AL.Activity)-(CHARINDEX(' ', AL.Activity)-1))  END) AS  Activity,			
			ISNULL(AL.RecordSourceId,0) AS RecordSourceId,
			ISNULL(AL.RecordDetail,'') AS RecordDetail,
			ISNULL(OS.Title,'') AS OSType
			
       FROM 
		   sAudit.tActivityLog AL
		   INNER JOIN sSecurity.tUser U ON U.Id = AL.UserId
		   INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = AL.OrgId
		   INNER JOIN sSecurity.tScreen S ON S.Id = AL.ScreenId
		   LEFT JOIN sAlert.tOSType OS ON OS.Id = AL.OSTypeId
       WHERE
		   AL.OrgGroupId = @rOrgGroupId
		   AND ((AL.OrgId = @rOrgId) OR (@rOrgId=0))
		   AND ((U.DisplayTitle = @rUserName) OR (@rUserName = ''))
		   AND ((S.Title LIKE @rScreenName) OR (@rScreenName = ''))
		   AND ((AL.Activity LIKE @rActivity) OR (@rActivity = ''))
		   AND (AL.AuditDate >= @rFromDate)
		   AND (AL.AuditDate <= @rToDate)
		   AND ((AL.OSTypeId = @rOSTypeId) OR (@rOSTypeId=0))
       ORDER BY 
			AL.AuditDate DESC
	END
END
GO
PRINT N'Altering Procedure [sCertificate].[pLeavingBulkGet]...';


GO
ALTER PROCEDURE [sCertificate].[pLeavingBulkGet]
	 
	 @rClassID int,
	 @rDivisionId int,
	 @rOrgId int,
	 @rBatchId int,
	 @rStatusId smallint=0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	
	SELECT 
			SA.AcademicId,
			ISNULL(SA.RollNo,0) AS RollNo,
			SA.Code,
			SA.Name,
			SA.Number,
			ISNULL(L.Id,0) AS Id,
			ISNULL(L.LCNumber,0) AS LCNumber,
			ISNULL(L.LCDate,'') AS LCDate,
			ISNULL(L.LCRemark,'')AS LCRemark,
			ISNULL(L.LastExamName,'')AS LastExamName,
			ISNULL(L.LastExamResult,'')AS LastExamResult,
			ISNULL(L.RepeatDetail,'')AS RepeatDetail,
			--ISNULL(L.SubjectStudied,'')AS SubjectStudied,
			ISNULL(L.PromoteToClass,'')AS PromoteToClass,
			ISNULL(L.ExtraCurricularActivities,'')As ExtraCurricularActivities,
			ISNULL(L.NCCScoutDetail,'')AS NCCScoutDetail,
			ISNULL(L.TermsInInstitute,'')As TermsInInstitute,
			ISNULL(L.FeeDetail,'')AS FeeDetail,
			ISNULL(L.FeeConcessionDetail,'')AS FeeConcessionDetail,
			ISNULL(AD.TotalDaysAnnual,0)AS TotalDays,
			ISNULL(AD.PresentDaysAnnual,0)AS PresentDays,
			ISNULL(StuA.ProofOfDOB,'')AS ProofOfDOB,
			CASE WHEN EXISTS(SELECT 1 FROM sSetup.tSubjectMaster S  INNER JOIN sSetup.tClassSubject CS ON  S.Id = CS.SubjectId AND CS.ClassId=@rClassId	
								WHERE S.OrgId=@rOrgId AND CS.IsOptional=0) THEN
			reverse(STUFF(reverse(isnull([sCommon].[fXMLTagsReplaceReverse](STUFF((SELECT ',' + S.Title from sSetup.tSubjectMaster S  
						   INNER JOIN sSetup.tClassSubject CS ON  S.Id = CS.SubjectId AND CS.ClassId=@rClassId	
						   WHERE S.OrgId=@rOrgId AND CS.IsOptional=0 FOR XML PATH('')), 1, 1, '')),'') +',' +
			isnull([sCommon].[fXMLTagsReplaceReverse](STUFF((SELECT ',' + S.Title FROM sAssessment.tOptionalSubject OS 
						   INNER JOIN sSetup.tClassSubject CS ON CS.Id = OS.ClassSubjectId 
						   INNER JOIN sSetup.tSubjectMaster S ON  S.Id = CS.SubjectId 
						   WHERE OS.AcademicId = SA.AcademicId FOR XML PATH('')), 1, 1, '')),'') 
						   ), 1, 1, '')) 
						   
			ELSE

			STUFF(reverse(reverse(isnull([sCommon].[fXMLTagsReplaceReverse](STUFF((SELECT ',' + S.Title from sSetup.tSubjectMaster S  
						   INNER JOIN sSetup.tClassSubject CS ON  S.Id = CS.SubjectId AND CS.ClassId=@rClassId	
						   WHERE S.OrgId=@rOrgId AND CS.IsOptional=0 FOR XML PATH('')), 1, 1, '')),'') +',' +
			isnull([sCommon].[fXMLTagsReplaceReverse](STUFF((SELECT ',' + S.Title FROM sAssessment.tOptionalSubject OS 
						   INNER JOIN sSetup.tClassSubject CS ON CS.Id = OS.ClassSubjectId 
						   INNER JOIN sSetup.tSubjectMaster S ON  S.Id = CS.SubjectId 
						   WHERE OS.AcademicId = SA.AcademicId FOR XML PATH('')), 1, 1, '')),'') 
						   )), 1, 1, '') END AS SubjectStudied,
			(SA.ClassName +'-'+SA.DivisionName)AS ClassDivision


			
			
  FROM 
			[sStudent].[vStudentAcademic]  SA 
			LEFT OUTER JOIN sCertificate.tLeaving L ON L.AcademicId=SA.AcademicId
			LEFT JOIN sStudent.tAcademicDetail AD ON SA.AcademicId=AD.AcademicId
			LEFT JOIN sStudent.tStudentAdmission StuA ON StuA.StudentId = SA.StudentId
  WHERE 
			(SA.ClassId=@rClassId OR @rClassId=0)
			AND (SA.DivisionId=@rDivisionId OR @rDivisionId=0)
			AND SA.BatchId=@rBatchId
			AND SA.OrgID=@rOrgId 
			AND L.Id IS NULL
			AND((SA.StatusId=@rStatusId)OR(@rStatusId=0))
			--order by
			--CASE @rOrderBy WHEN 2 THEN (CASE ISNUMERIC(SA.Code) WHEN 1 THEN REPLICATE(0 , 100 - LEN(SA.Code)) + SA.Code ELSE SA.Code  END) END,
			--CASE @rOrderBy WHEN 1 THEN SA.RollNo END,
			--CASE @rOrderBy WHEN 0 THEN SA.RollNo END
END
GO
PRINT N'Altering Procedure [sCommunication].[pMyBookGet]...';


GO
ALTER PROCEDURE [sCommunication].[pMyBookGet]
	 
	 @rReaderSourceTypeId smallint,
	 @rReaderSourceId bigint,
	 @rBatchId int,
	 @rStatusId int=0,
	 @rLibraryId int=0
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 



	select 
		BL.Id AS BookLogId,
		BM.Title AS BookTitle,
		BL.LogDate AS IssueDate,
		--BL.IsCurrentStatus AS IsReturnPending,
		BL.StatusId AS StatusId,
		CASE WHEN SM.Title = 'Available' THEN 'Return' ELSE SM.Title END AS StatusName,
		--due to status not display properly
		--(CASE WHEN (ISNULL(BL.ReturnDate,'')!='' OR BL.BookActionStatusId=1) THEN 'Return'
		--	 WHEN BL.BookActionStatusId=2 THEN 'Release'
		--	 ELSE SM.Title END) AS StatusName,
		CASE WHEN BL.IsCurrentStatus = 1 THEN 'Pending' ELSE 'Return' END AS IsReturnPending,
		BL.DueDate,
		BL.LibraryId ,
		ISNULL(BM.Author,'')AS Author
	--CASE WHEN BL.IsCurrentStatus = 0 THEN BL.DueDate ELSE '' END AS DueDate

	  from 
		  sLibrary.tBookLog BL 
		  INNER JOIN sLibrary.tBookMaster BM
		  ON BL.ResourceSourceId = BM.Id AND BL.ResourceSourceTypeId = 2009  
		  LEFT JOIN sCommon.tStatusMaster SM ON SM.Id = BL.StatusId AND SM.statusTypeId = 106
	  WHERE 
		  --BL.statusId = 523
		  BL.ReaderSourceId = @rReaderSourceId
		  AND BL.ReaderSourceTypeId = @rReaderSourceTypeId
		  AND (BL.StatusId = @rStatusId OR @rStatusId=0)
		  AND (BL.LibraryId= @rLibraryId OR @rLibraryId=0)
		  AND (SM.Id!=521 OR ISNULL(BL.BookActionStatusId,0)!=0)
		  AND (BL.BatchId = @rBatchId OR @rBatchId = 0)
	  
	  ORDER BY BL.LogDate DESC,BL.CreatedDateTime DESC

END
GO
PRINT N'Altering Procedure [sHrm].[pRptEmployeeAvailableLeaveWithPay]...';


GO
ALTER PROCEDURE [sHrm].[pRptEmployeeAvailableLeaveWithPay]

		@rBuilder1 nvarchar(max)='',
		@rBuilder2 nvarchar(max)='',
		@rDepartmentId int,
		@rEmployeeGradeId int,
		@rEmployeeStatusId int=0,
		@rEmployeeCode nvarchar (200),
		@rEmployeeName nvarchar(200),
		@rBatchId int,
		@rOrgId int,
		@rLeaveTypeIds nvarchar(50) = '',
		@rEmployeeId bigint=0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	SELECT
		@rBuilder1 AS Builder1,
		@rBuilder2 AS Builder2,
		EM.Code AS EmployeeCode,
		EM.EmployeeName,
		EM.EmployeeId,
		EM.DepartmentName,
		EM.EmployeeGrade,
		ISNULL(ESO.BasicPay,0) AS BasicPay,
		LTM.Id AS LeaveTypeId,
		LTM.Title AS LeaveType,
		(SELECT	(ISNULL(LB.BalanceLeave,0) - ISNULL(SUM(NoOfLeave),0)) AS AvailableBalance
		 FROM sHrm.tLeaveBalance LB
			  LEFT JOIN sHrm.tLeave L ON LB.EmployeeId=L.EmployeeId AND LB.BatchId = L.BatchId AND LB.LeaveTypeId = L.LeaveTypeId AND L.StatusId = 550
		 WHERE LB.BatchId = @rBatchId AND LB.EmployeeId=EM.EmployeeId AND LB.LeaveTypeId=LTM.Id
		 GROUP BY LB.BalanceLeave) AS AvailableBalance
	FROM
		sHrm.vEmployeeCommonDetail EM
		LEFT JOIN sHrm.tEmployeeStatutoryAndOther ESO ON ESO.EmployeeId=EM.EmployeeId
		LEFT JOIN sHrm.tLeaveTypeMaster LTM ON LTM.OrgId=EM.OrgId AND LTM.IsObsolete = 0 AND IsLWP=0
	WHERE
		EM.OrgID = @rOrgId
		AND ((EM.EmployeeGradeId=@rEmployeeGradeId) OR (@rEmployeeGradeId=0))
		AND ((EM.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND ((EM.Code like @rEmployeeCode) OR (@rEmployeeCode=''))
		AND ((EM.EmployeeName like @rEmployeeName) OR (@rEmployeeName=''))
		AND ((EM.EmployeeId=@rEmployeeId) OR (@rEmployeeId=0))
		AND ((EM.EmployeeStatusId=@rEmployeeStatusId) OR (@rEmployeeStatusId=0))
		AND ((EM.DepartmentId= @rDepartmentId ) OR (@rDepartmentId= 0 ))
		AND (ISNULL(LTM.Id,0) IN (SELECT cast(item as int) FROM sCommon.fnsplit(@rLeaveTypeIds,',')) OR ISNULL(@rLeaveTypeIds, '')='')
	ORDER BY EM.Code

END
GO
PRINT N'Altering Procedure [sHrm].[pRptEmployeeIcardGet]...';


GO

ALTER PROCEDURE [sHrm].[pRptEmployeeIcardGet]
	@rBuilder1 nvarchar(max)='',
	@rBuilder2 nvarchar(max)='',
	@rReportId smallint,
	@rOrgId smallint,
	@rStrEmployeeId nvarchar(max),
	@rEmployeeTypeId [int],
	@rDepartmentId [int]= 0,
	@rGradeId [int]= 0,
	@rDesignationId [int]= 0,
	@rGenderId [int]= 0,		
	@rEmployeeId [bigint]= 0,
	@rAgeFrom nvarchar(10),
	@rAgeTo nvarchar(10),
	@rEmployeeCode nvarchar(50)='',
	@rEmployeeGroupId smallint,
	@rEmployeeStatusId smallint,
	@rIsShowBarcode bit=0,
	@rStrCode nvarchar(max)='',
	@rBatchName nvarchar(100) = ''
	
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 
	SELECT		
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			ECD.EmployeeId,
	        ECD.Code,	      	
			ECD.EmployeeName, 	 								 
			ECD.DepartmentId,  			
			ECD.DepartmentName,
		    ECD.MobileNumber,
		    ECD.EmployeeDesignation,
		    ECD.PhoneNoCompany,
		    ECD.BloodGroup,
		    ECD.DateOfBirth,
			EI.EmployeePhoto,
		    OM.Title AS OrgName,
		    OM.PrincipalSignature,
			ISNULL(ICO.ICardOrgTitle,'') AS ICardOrgTitle,
			ISNULL(ICO.ICardAddress1,'') AS ICardAddress1,
			ISNULL(ICO.ICardAddress2,'') AS ICardAddress2,
			ISNULL(ICO.ICardAddress3,'') AS ICardAddress3,	
		    OM.OrgLogo AS ICardLogo,
			ICO.ICardBackground,	
			ICO.ICardBackBackground,						
			ISNULL(ICO.LabelColor,'Black') AS LabelColor,
			ISNULL(ICO.DetailColor,'Black') AS DetailColor,
			ISNULL(ICO.HeaderColor,'Black') AS HeaderColor,
			ISNULL(EC.PresentStreetOne,'') AS PresentStreetOne,
			ISNULL(EC.PresentStreetTwo,'') AS PresentStreetTwo,
			ISNULL(EC.PresentStreetThree,'') AS PresentStreetThree,
			ISNULL(EC.PresentCity,'') AS PresentCity,
			ISNULL(EC.PresentState,'') AS PresentState,
			ISNULL(EC.PresentCountry,'') AS PresentCountry,
			ISNULL(EC.PresentZipCode,'') AS PresentZipCode,
			ECD.DateofJoining,
			EI.EmployeeSign,
			@rIsShowBarcode As IsShowBarcode,
			ISNULL(EC.EmergencyStreetOne,'') AS EmergencyStreetOne,
			ISNULL(EC.EmergencyStreetTwo,'') AS EmergencyStreetTwo,
			ISNULL(EC.EmergencyStreetThree,'') AS EmergencyStreetThree,
			ISNULL(EC.EmergencyCity,'') AS EmergencyCity,
			ISNULL(EC.EmergencyState,'') AS EmergencyState,
			ISNULL(EC.EmergencyCountry,'') AS EmergencyCountry,
			ISNULL(EC.EmergencyZipCode,'') AS EmergencyZipCode,
			ISNULL(PanNumber,'') AS PanNumber,
			EC.EmergencyContactNo AS EmergencyContactNo,
			EPD.Qualification AS Qualification,
			--ISNULL(EM.MiddleName,'')AS FatherName,
			ISNULL(ECD.MiddleName,'')AS FatherName,
			ECD.GenderTitle,
			ISNULL(ECD.FirstName,'')AS FirstName,
			ISNULL(ECD.LastName,'')AS SurName,
			(CASE WHEN RelationshipId = 2 THEN EFD.MobileNo ELSE '' END )AS FatherContactNo,
			ISNULL(@rBatchName,'') AS BatchName,
			ISNULL(EC.PresentContactNo,'') AS PresentContactNo
		FROM 
			sHrm.vEmployeeCommonDetail ECD
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=ECD.OrgID
			LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId=ECD.EmployeeId
			LEFT JOIN sHrm.tEmployeeICardOrg ICO ON ECD.OrgId = ICO.OrgId
			LEFT JOIN sHrm.tEmployeeCommunications EC ON ECD.EmployeeId = EC.EmployeeId
			LEFT JOIN sHrm.tEmployeeStatutoryAndOther EO ON EO.EmployeeId = ECD.EmployeeId
			LEFT JOIN sHrm.tEmployeePersonalDetail EPD ON EPD.EmployeeId = EO.EmployeeId
			LEFT JOIN sHrm.tEmployeeFamilyDetails EFD on ECD.EmployeeId=EFD.EmployeeId AND EFD.RelationshipId = 2
			--LEFT JOIN sCommon.tRelationshipMaster RM on EFD.RelationshipId=RM.Id AND RM.Id=2
			--LEFT JOIN sHrm.tEmployeeMaster EM on EM.Id = ECD.EmployeeId
			
		WHERE
		((ECD.Code LIKE @rEmployeeCode)OR @rEmployeeCode = '' )
	   AND (ISNULL(ECD.EmployeeId,0) NOT IN( SELECT cast(item as bigint) FROM  sCommon.fnsplit(@rStrEmployeeId,',')) OR @rStrEmployeeId='') 
	   AND (ISNULL(ECD.Code,'') IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rStrCode,',')) OR @rStrCode='')   
	   AND (ECD.EmployeeTypeId = @rEmployeeTypeId OR @rEmployeeTypeId = 0)
	   AND (ECD.OrganizationDepartmentId = @rDepartmentId OR @rDepartmentId = 0)
	   AND (ECD.EmployeeStatusId = @rEmployeeStatusId OR @rEmployeeStatusId = 0)
	   AND (ECD.EmployeeGroupId=@rEmployeeGroupId OR @rEmployeeGroupId=0)
	   AND (ECD.EmployeeGradeId = @rGradeId OR @rGradeId = 0)
	   AND (ECD.EmployeeDesignationId = @rDesignationId OR @rDesignationId = 0)	 	   
	   AND (ECD.GenderId = @rGenderId OR @rGenderId = 0)  	    
	   AND (ECD.EmployeeId = @rEmployeeId OR @rEmployeeId = 0)	   
	   --AND ((DATEDIFF(yy,ECD.DateOfBirth,GETDATE()) <= @rAgeTo) OR (ISNULL(@rAgeTo,0)=0))
	   --AND ((DATEDIFF(yy,ECD.DateOfBirth,GETDATE()) >= @rAgeFrom)OR (ISNULL(@rAgeFrom,0)=0))
	   AND  ECD.OrgId = @rOrgId
	   AND ISNULL(ECD.IsOtherUser,0)=0
	  
END
GO
PRINT N'Altering Procedure [sLibrary].[pReaderBookLogListGet]...';


GO

ALTER PROCEDURE [sLibrary].[pReaderBookLogListGet]
	 @rReaderSourceTypeId smallint=0,
	 @rReaderSourceId bigint,
	 @rOrgId smallint,
	 @rBatchId int,
	 @rLibraryId int
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 	
If(@rReaderSourceTypeId=2003)
BEGIN
	SELECT 
			BL.Id AS BookLogId,
			BM.Code AS ResourceCode,
			BM.Title AS ResourceName,
			BL.LogDate,
			BL.DueDate,
			IS1.NoOfBookIssuable,
			BL.StatusId,
			BM.Id As BookId
	FROM 
			sLibrary.tBookLog BL
			INNER JOIN sLibrary.tBookMaster BM ON BM.Id=BL.ResourceSourceId
			INNER JOIN sStudent.vStudentLastAcademic SA ON SA.StudentId=BL.ReaderSourceId 
			INNER JOIN sLibrary.tIssueSetting IS1 ON IS1.SourceId=SA.ClassId AND IS1.SourceTypeId=2005 AND IS1.LibraryId=@rLibraryId
	WHERE 
			((BL.ReaderSourceTypeId=@rReaderSourceTypeId) OR (@rReaderSourceTypeId=0))
		AND (BL.ReaderSourceId=@rReaderSourceId)
		AND BL.IsCurrentStatus=1
		AND ((BL.StatusId=522) OR (BL.StatusId=523))
		--AND (BL.OrgId=@rOrgId)
		--AND (BL.BatchId=@rBatchId)
		-- because of pAssignBooksBulkReturnGet sp (due date and Book Code not display) - done by sarangi - mantisid - 0026852
		AND (SA.OrgId=@rOrgId)
		AND (SA.BatchId=@rBatchId)
		--AND  SA.IsActiveBatch = 1
		AND (BL.LibraryId=@rLibraryId)
END
ELSE If(@rReaderSourceTypeId=95)
BEGIN
	SELECT 
			BL.Id AS BookLogId,
			BM.Code AS ResourceCode,
			BM.Title AS ResourceName,
			BL.LogDate,
			BL.DueDate,
			IS1.NoOfBookIssuable,
			BL.StatusId,
			BM.Id As BookId   
	FROM 
			sLibrary.tBookLog BL
			INNER JOIN sLibrary.tBookMaster BM ON BM.Id=BL.ResourceSourceId
			INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id=BL.ReaderSourceId	
			INNER JOIN sHrm.tEmployeeJobDetail EJD ON EM.Id = EJD.EmployeeId
			INNER JOIN sSetup.tDepartmentMaster ODM ON ODM.Id = EJD.OrganizationDepartmentId
			INNER JOIN sLibrary.tIssueSetting IS1 ON IS1.SourceId=ODM.Id AND IS1.SourceTypeId=2004 AND IS1.LibraryId=@rLibraryId
	WHERE 
			((BL.ReaderSourceTypeId=@rReaderSourceTypeId) OR (@rReaderSourceTypeId=0))
		AND (BL.ReaderSourceId=@rReaderSourceId)
		AND BL.IsCurrentStatus=1
		AND ((BL.StatusId=522) OR (BL.StatusId=523))
		-- because of pAssignBooksBulkReturnGet sp (due date and Book Code not display) - done by sarangi - mantisid - 0026852
		--AND (BL.OrgId=@rOrgId)
		AND (EM.OrgId=@rOrgId)
		AND (BL.BatchId=@rBatchId)
		AND (BL.LibraryId=@rLibraryId)
		AND ISNULL(EM.IsOtherUser,0) = 0
END
END
GO
PRINT N'Altering Procedure [sProcurement].[pRptStockIssueStudentReceipt]...';


GO

 
ALTER PROCEDURE [sProcurement].[pRptStockIssueStudentReceipt]
 
	@rId int = 0,
	@rStoreLocationId int = 0,
	@rMainGroupId int=0,
	@rSubGroupId int=0,
	@rItemCode nvarchar(50)='',
	@rItemName nvarchar(50)='',
	@rOrgGroupId int=0,
	@rBuilder1 nvarchar(max)='',
	@rBuilder2 nvarchar(max)='',
	@rReportId int=0,
	@rStockItemCategoryId int=0,
	@rFromDate date='',
	@rTodate date='',
	@rToDepartmentId int =0,
	@rNumber nvarchar(10)='',
	@rAcademicId bigint = 0,
	@rOrgId smallint = 0,
	@rBatchId int = 0
	
WITH EXECUTE AS CALLER
AS
BEGIN
	
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	Declare @lIDConfigValue nvarchar(50)=''
	DECLARE @lCurrencyCode nvarchar(20) = ''
	DECLARE @lCurrencyTitle nvarchar(100) = ''
	SELECT @lIDConfigValue =ItemValue From sConfig.tConfigurationSetup
	WHERE ConfigurationId = 236 AND SourceId = @rOrgGroupId
	SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@rOrgGroupId) 
	SELECT  @lCurrencyTitle = [sFinanceCommon].[fDefaultCurrencyTitleGet](@rOrgGroupId) 
       SELECT  
			@rBuilder1 AS Builder1,
			SIM.Code AS ItemNumber,
			SIM.Title AS ItemName,		
			SI.IssuedByName,	
			SI.Number, 	
			SI.CreatedDateTime AS IssuedDate,
			ID.IssuedQuantity,
			ID.RequestedQuantity,
			SM.FirstName +' '+SM.MiddleName As StudentName,
			Sm.Code AS StudentCode,
			SG.Code AS ItemSubGroup,
			SG.Id AS ItemSubGroupId,
			OM.DisplayTitle,
			BM.Title AS BatchTitle,
			CM.Title +'-'+ DM.TItle As Class,
			(SELECT ISNULL(SUM(AvailableQuantity),0) FROM sProcurement.tStockBatch SB 
		    WHERE SB.StockItemId = SIM.Id 
		    AND ((SB.StorelocationId = @rStoreLocationId) OR (@rStoreLocationId = 0))
			) AS Quantity ,
			A.OrgId,
			A.BatchId,
			ISNULL(CAST(SIP.WeightedAverageCost AS DECIMAL(18, 2)), '0') AS WeightedAverageCost,
			@lIDConfigValue AS IdNoLabel,
			((ISNULL(CAST(SIP.WeightedAverageCost AS DECIMAL(18, 2)), '0')) * (ID.IssuedQuantity)) AS Amount,
			replace(
			(sFinanceCommon.fNumberToWordsWithDecimal(
			((ISNULL(CAST(SIP.WeightedAverageCost AS DECIMAL(18, 2)), '0')) * (ID.IssuedQuantity)),1,2,SI.OrgGroupId)
			),'Rupees Rupees',[sFinanceCommon].[fDefaultCurrencyMinorTitleGet](OM.OrgGroupId)) AS AmountInWords,
			ISNULL(SI.PayTypeId,0) as PayTypeId,
			 ISNULL(PTM.Title,'Not Charged') AS PayType,
			ISNULL(SI.ReceiptNumber,0) as ReceiptNumber,
			ISNULL(SI.BankName,'') as BankName,
			ISNULL(SI.ChequeNo,'') as ChequeNo,
			ISNULL(SI.ChequeDate,'') as ChequeDate,
			@lCurrencyCode AS CurrencyCode,
			@lCurrencyTitle AS CurrencyTitle

		FROM  
			sProcurement.tStockIssue SI		
			INNER JOIN sProcurement.tStockIssueDetail ID ON SI.Id = ID.StockIssueId
			INNER JOIN sIp.tStockItemMaster SIM ON ID.StockItemId  = SIM.Id 
			LEFT JOIN sIp.tStockItemPlanning SIP ON SIP.StockItemId=SIM.Id AND SIP.StoreLocationId=@rStoreLocationId
			INNER JOIN sStudent.tAcademic A on A.Id=IssueToSourceId
			INNER JOIN sStudent.tStudentMaster SM ON SM.Id = A.StudentID
			INNER JOIN sIp.tItemSubGroupMaster SG ON SG.Id=SIM.ItemSubGroupId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=A.OrgId
			INNER JOIN sSetup.tClassMaster CM on CM.id =A.ClassId 
			INNER JOIN sSetup.tDivisionMaster DM on DM.Id=A.DivisionId
			INNER JOIn sSetup.tBatchMaster BM ON BM.Id = A.BatchId
			Left JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=SI.PayTypeId 
		WHERE 
			 ((SI.Id = @rId) OR (@rId=0))
			 AND SI.FromStoreLocationId =@rStoreLocationId
			 AND ((SIM.ItemSubGroupId= @rSubGroupId) OR (@rSubGroupId=0))
			 AND ((SIM.Code = @rItemCode) OR (@rItemCode=''))	
			 AND ((SIM.Title = @rItemName) OR (@rItemName=''))
			 AND ((convert(Date,ISNULL(SI.CreatedDateTime,'')) >= @rFromDate) OR (convert(date,ISNULL(@rFromDate,''))=''))
			 AND ((convert(date,ISNULL(SI.CreatedDateTime,'')) <= @rToDate) OR (convert(date,ISNULL(@rToDate,''))=''))
			 AND SI.OrgGroupId = @rOrgGroupId
			 AND ((SI.ToOrganizationDepartmentId=@rToDepartmentId) OR (@rToDepartmentId=0))
			 AND ((SI.Number like @rNumber) OR  (ISNULL(@rNumber,'')= ''))
			 AND ((SI.IssueToSourceId= @rAcademicId) OR (ISNULL(@rAcademicId,0)=0))
			 AND SI.IssueToSourceTypeId = 2001
			 AND ((A.OrgId = @rOrgId) OR (ISNULL(@rOrgId,0) = 0))
			 --AND ((A.BatchId = @rBatchId) OR (ISNULL(@rBatchId,0) = 0))
END


SET ANSI_NULLS ON
GO
PRINT N'Altering Procedure [sSecurity].[pPendingLoginStudentdGet]...';


GO

ALTER PROCEDURE [sSecurity].[pPendingLoginStudentdGet]
	
	@rOrgId smallint,
	@rBatchId int,
	@rIsPendingLogin bit,
	@rIsCreatedLogin bit,
	@rClassId int = 0,
	@rDivisionId int = 0,
	@rNotRegister bit = 0
	--@rRollNos nvarchar(max) = NULL,
	--@rMultiCode nvarchar(max)=''

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	SELECT 
	-- TOP 100
			ISNULL(U.Id,0) AS Id,
			SC.StudentId,
			SC.Code,
			SC.Name,
			SC.ClassName + '-' + SC.DivisionName AS ClassName,
			SC.RollNo,
			ISNULL(SC.ParentContactNo,'') AS MobileNo,
			ISNULL(SC.ParentEmail,'') AS EmailId,
			U.LoginTitle AS UserName,
			U.LoginPassword,
			SC.SUId,
			SM.Title AS Status,
			(CASE WHEN A.UserId IS NULL THEN 'Not Register' ELSE 'Register' END) AS UserRegisterStatus,
			ISNULL(SC.WhatsAppContactNo,'') AS WhatsAppContactNo
	FROM 
			sStudent.vStudentCommunication SC
			LEFT JOIN sSecurity.tUser U ON U.SourceId = SC.StudentId AND U.SourceTypeId=2003
			INNER JOIN sStudent.tStatusMaster SM ON SM.Id = SC.StatusId 
			LEFT JOIN 
			(
				SELECT DISTINCT UserId FROM sCommunication.tRegisteredUsers
			) AS A ON A.UserId = U.Id
	WHERE 
			((U.LoginPassWord is Null AND @rIsPendingLogin=1) OR (U.LoginPassWord is NOT Null AND @rIsCreatedLogin=1))
			 AND	(SC.OrgId = @rOrgId)
			 AND	(SC.BatchId = @rBatchId) 
			 AND	((SC.ClassId = @rClassId) OR(@rClassId=0))
			 AND	((SC.DivisionId = @rDivisionId) OR(@rDivisionId=0))
			 AND SC.StatusId = 1
			 AND ((A.UserId IS NULL AND @rNotRegister = 1) OR (@rNotRegister = 0))
			 --AND (ISNULL(SC.RollNo,0) IN( SELECT cast(item as Int) FROM  sCommon.fnsplit(@rRollNos,',')) OR @rRollNos IS NULL)
			 --AND (ISNULL(SC.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
	Order By SC.DivisionOrderIndex,SC.RollNo
	
END
GO
PRINT N'Altering Procedure [sStudent].[pChangeClassGet]...';


GO
ALTER PROCEDURE [sStudent].[pChangeClassGet]
	 
	 @rClassID int,
	 @rDivisionId int,
	 @rStrIDNo nvarchar(500),
	 @rOrgId int,
	 @rBatchId int
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 

		DECLARE @ListofIDs TABLE(IDs nvarchar(20), ID INT IDENTITY(1,1));
		INSERT INTO @ListofIDs

		select q2.value from
		(SELECT cast('<x>'+replace(@rStrIDNo,',','</x><x>')+'</x>' as xml) as thexml)q1 CROSS APPLY
		(SELECT x.value('.','nvarchar(20)') as value FROM thexml.nodes('x') as f(x))q2
		
		
	 --IF(@rIsSearch=1)
		--BEGIN
			--SET @rGRNo = sCommon.fSearchStringGet(@rGRNo)
			--SET @rLastName = sCommon.fSearchStringGet(@rLastName)
			--SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
		--END
	
	SELECT 
			SN.AcademicId,
			--A.StudentId,
			--SO.GenderId,
			SN.ClassId,
			SN.DivisionId,
			SN.RollNo AS RollNo,
			SN.FirstName,
			SN.MiddleName,
			SN.LastName,
			SN.FirstName + ' ' + SN.MiddleName + ' ' + SN.LastName AS Name,
			SN.Number,
			SN.Code
      
  FROM 
			[sStudent].[vStudentName] SN
           --sStudent.tAcademic A
           --INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId 
           ----INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=A.StudentId  
  WHERE 
				(SN.ClassId=@rClassId OR @rClassId=0)
			AND (SN.DivisionId=@rDivisionId OR @rDivisionId=0)
			AND ((SN.Code IN(SELECT IDs FROM @ListofIDs)) OR (ISNULL(@rStrIDNo,'') = ''))
			AND SN.OrgID=@rOrgId 
			AND SN.BatchID=@rBatchId

END
GO
PRINT N'Altering Procedure [sStudent].[pRptStudentRegisterWithAddressGet]...';


GO
ALTER PROCEDURE [sStudent].[pRptStudentRegisterWithAddressGet]
	 
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rOrgId smallint,
	 @rBatchId int,
	 @rStrCode nvarchar(max),
	 @rClassId int,
	 @rDivisionId int,
	 @rStudentId bigint,
	 @rFromRollNo int,
	 @rToRollNo int,
	 @rStatusId int,
	 @rClassStatusId int,
	 @rDateOfBirth int,
	 @rDepartmentId int
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 

	SELECT 
		 
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			SD.RollNo,
			SD.Code,
			SD.FirstName,
			SD.MiddleName,			
			SD.LastName,
			SD.Name,	
			SD.DateOfBirth,
			SD.Religion,
			SD.FamilyCast,
			CCM.Title AS FamilySubCast,
			SF.MotherName,
			SF.FatherName,
			SD.ParentContactNo AS ContactNo,
			SF.FatherOccupation,
			SD.PresentStreetOne + ' ' + SD.PresentStreetTwo +' ' +SD.PresentStreetThree AS Address,
			
			SD.AcademicId,
			SD.StudentId, 
			SD.ClassId,
			SD.DivisionId,
			SD.ClassDivision,
			  
			SD.ClassName,
			SD.DivisionName,
			 
			OM.Title AS OrgName,
			SD.DivisionOrder,
			SD.Number AS StudentNumber,
			SD.Batch,                       
			
			SD.ClassStatusId,
			SD.ClassStatusName,
			ISNULL(SD.AdmissionDate,'') As AdmissionDate,
			ISNULL(SD.Gender,'') AS Gender,
			ISNULL(SD.StudentContactNo,'') AS StudentContactNo,
			ISNULL(SD.BirthPlace,'') AS BirthPlace				--add for 26920 Mantis
			
      
	FROM 
			sStudent.vStudentAllDetail SD
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SD.OrgId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=SD.StudentId
			--INNER  JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SD.StudentId
			--INNER JOIN sStudent.tStudentOther SO ON SO.StudentId = SD.StudentId
			LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id = SD.CastCategoryId
			INNER JOIN sSetup.tBatchClass BC ON BC.ClassID =  SD.ClassId and BC.BatchId= @rBatchId 
			
	WHERE 
			SD.OrgId=@rOrgId
		AND	SD.BatchId = @rBatchId 
		AND (ISNULL(SD.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rStrCode,',')) OR @rStrCode='') 
		AND ((SD.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SD.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((SD.StudentId=@rStudentId) OR (@rStudentId=0))
		AND ((SD.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))
		AND ((SD.RollNo <= @rToRollNo) OR (@rToRollNo=0))
		AND ((SD.StatusId=@rStatusId)OR(@rStatusId=0))
		AND ((SD.ClassStatusId=@rClassStatusId)OR(@rClassStatusId=0))
		AND ((MONTH(SD.DateOfBirth)=@rDateOfBirth OR @rDateOfBirth=0))
	    AND ((SD.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
END
GO
PRINT N'Altering Procedure [sStudent].[pStudentDetailUpdateGet]...';


GO
ALTER PROCEDURE [sStudent].[pStudentDetailUpdateGet]
	 
	 @rCategoryId int,
	 @rSubCategory nvarchar(100),
	 @rOrgId int,
	 @rClassId int,
	 @rDivisionId int,
	 @rBatchId int,
	 --@rIsBlank bit,
	 @rStatusId int,
	 @rOrderBy int,
	 @rFromCode nvarchar(20)='',
	 @rToCode nvarchar(20)='',
	  @rMultiCode nvarchar(max)=''
	 
AS
SET NOCOUNT ON

BEGIN
	 	DECLARE @lDataTypeId nvarchar(1)
		SELECT @lDataTypeId=DataTypeId FROM sCommon.tUpdateFieldMaster WHERE Field=@rSubCategory
		 
		 
		 IF @rCategoryId=1 
		 BEGIN-- Communication
			IF @rSubCategory ='Present Address'
			BEGIN
				if @rOrderBY=1
				BEGIN
				Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
						PresentStreetOne,
						PresentStreetTwo,
						PresentStreetThree,
						PresentCountry,
						PresentState,
						PresentCity,
						PresentZipCode,
						PresentContactNo
				FROM 
				sStudent.vStudentAcademic SA
				LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
				Where SA.OrgId=@rOrgId
				AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
			    AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
				
				ORder BY SA.RollNo
				END
				Else
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
						PresentStreetOne,
						PresentStreetTwo,
						PresentStreetThree,
						PresentCountry,
						PresentState,
						PresentCity,
						PresentZipCode,
						PresentContactNo
				FROM 
				sStudent.vStudentAcademic SA
				LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
				Where SA.OrgId=@rOrgId
				AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
			    AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
				
				ORder BY SA.Number		--add
				END	
			END
			IF @rSubCategory ='Permanent Address'
			BEGIN
				if @rOrderBY=1
				BEGIN
				Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
						PermanentStreetOne As PresentStreetOne,
						PermanentStreetTwo AS PresentStreetTwo,
						PermanentStreetThree AS PresentStreetThree,
						PermanentCountry AS PresentCountry,
						PermanentState AS PresentState,
						PermanentCity As PresentCity,
						PermanentZipCode As PresentZipCode,
						PermanentContactNo AS PresentContactNo
				FROM 
				sStudent.vStudentAcademic SA
				LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
				Where SA.OrgId=@rOrgId
				AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
				
				Order by Sa.RollNo
				END
				ELSE
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
						PermanentStreetOne As PresentStreetOne,
						PermanentStreetTwo AS PresentStreetTwo,
						PermanentStreetThree AS PresentStreetThree,
						PermanentCountry AS PresentCountry,
						PermanentState AS PresentState,
						PermanentCity As PresentCity,
						PermanentZipCode As PresentZipCode,
						PermanentContactNo AS PresentContactNo
				FROM 
				sStudent.vStudentAcademic SA
				LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
				Where SA.OrgId=@rOrgId
				AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId ))
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				 AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
				 
				Order By SA.Number		--add
				END
			END
			IF @rSubCategory ='Emergency Address'
			BEGIN
				if @rOrderBY=1
				BEGIN
				Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
						EmergencyStreetOne AS PresentStreetOne,
						EmergencyStreetTwo As PresentStreetTwo, 
						EmergencyStreetThree As PresentStreetThree,
						EmergencyCountry As PresentCountry,
						EmergencyState As PresentState,
						EmergencyCity AS PresentCity,
						EmergencyZipCode As PresentZipCode,
						EmergencyContactNo  AS PresentContactNo
				FROM 
				sStudent.vStudentAcademic SA
				LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
				Where SA.OrgId=@rOrgId
				AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
				
				Order BY SA.RollNo
				END
				Else
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
						EmergencyStreetOne AS PresentStreetOne,
						EmergencyStreetTwo As PresentStreetTwo, 
						EmergencyStreetThree As PresentStreetThree,
						EmergencyCountry As PresentCountry,
						EmergencyState As PresentState,
						EmergencyCity AS PresentCity,
						EmergencyZipCode As PresentZipCode,
						EmergencyContactNo  AS PresentContactNo
				FROM 
				sStudent.vStudentAcademic SA
				LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
				Where SA.OrgId=@rOrgId
				AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
				
				Order By SA.Number		--add
				END
			END
		END
		IF @rCategoryId= 4 
		BEGIN --Family
			IF @rSubCategory ='Father'
				BEGIN
				if @rOrderBY=1
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
							FatherName,FatherDateOfBirth,FatherOccupation,FatherQualification,FatherContactNo,FatherMail,
							FatherOfficeAddress,FatherIncome,FatherOfficeContactNo
					FROM 
					sStudent.vStudentAcademic SA
					LEFT JOIN sStudent.tStudentFamily SC ON SC.StudentId=SA.StudentId
					Where SA.OrgId=@rOrgId
				AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				
				ORder BY SA.RollNo
				END
				Else
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
							FatherName,FatherDateOfBirth,FatherOccupation,FatherQualification,FatherContactNo,FatherMail,
							FatherOfficeAddress,FatherIncome,FatherOfficeContactNo
					FROM 
					sStudent.vStudentAcademic SA
					LEFT JOIN sStudent.tStudentFamily SC ON SC.StudentId=SA.StudentId
					Where SA.OrgId=@rOrgId
				AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				
				Order By SA.Number	--add
				END
			END
			IF @rSubCategory ='Mother'
				BEGIN
				if @rOrderBY=1
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
							MotherName,MotherDateOfBirth,MotherOccupation,MotherQualification,MotherContactNo,MotherMail,
							MotherOfficeAddress,MotherIncome,MotherOfficeContactNo
					FROM 
					sStudent.vStudentAcademic SA
					LEFT JOIN sStudent.tStudentFamily SC ON SC.StudentId=SA.StudentId
					Where SA.OrgId=@rOrgId
					AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				
				Order By SA.ROllNO
				END
				Else
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
							MotherName,MotherDateOfBirth,MotherOccupation,MotherQualification,MotherContactNo,MotherMail,
							MotherOfficeAddress,MotherIncome,MotherOfficeContactNo
					FROM 
					sStudent.vStudentAcademic SA
					LEFT JOIN sStudent.tStudentFamily SC ON SC.StudentId=SA.StudentId
					Where SA.OrgId=@rOrgId
					AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				
				Order By SA.Number	--add
				END
			END
			IF @rSubCategory ='Relative'
				BEGIN
				if @rOrderBY=1
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
							RelativeName,RelativeDateOfBirth,RelativeOccupation,RelativeQualification,RelativeContactNo,RelativeMail,
							RelativeOfficeAddress,RelativeIncome,RelationshipName
					FROM 
					sStudent.vStudentAcademic SA
					LEFT JOIN sStudent.tStudentFamily SC ON SC.StudentId=SA.StudentId
					Where SA.OrgId=@rOrgId
					AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
				
				Order By SA.ROllNo
				END
				Else
				BEGIN
					Select  SA.StudentId,SA.AcademicId,SA.Name,SA.ClassName+' - '+SA.DivisionName AS ClassDivision,SA.RollNo,SA.Code,SA.Number,
							RelativeName,RelativeDateOfBirth,RelativeOccupation,RelativeQualification,RelativeContactNo,RelativeMail,
							RelativeOfficeAddress,RelativeIncome,RelationshipName
					FROM 
					sStudent.vStudentAcademic SA
					LEFT JOIN sStudent.tStudentFamily SC ON SC.StudentId=SA.StudentId
					Where SA.OrgId=@rOrgId
					AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
				AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
				AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
				AND ((SA.BatchId= @rBatchId )) 
				AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			    AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
			    
				Order By SA.Number		--add
				END
			END
			
		END			
		IF @rCategoryId= 10 
		BEGIN
			IF @rSubCategory ='Academic'
			BEGIN
			
				if @rOrderBY=1
				 BEGIN
					 Select 
						SA.StudentId,SA.Code,SA.FirstName,SA.MiddleName,SA.LastName,SA.ClassName,SA.ClassId,SA.DivisionID,SA.Number,
						SA.DivisionName,convert(date,SO.DateOfBirth) As DateOfBirth,AD.AdmissionDate,AD.AdmissionStandardId,SA.Name,Sa.RollNo,SO.GenderId
					From  
						 sStudent.vStudentAcademic SA
						INNER JOIN sStudent.tStudentOther SO 
									ON SO.StudentId=SA.StudentId
						LEFT JOIN sStudent.tStudentAdmission AD
									ON AD.StudentID = SA.StudentId  
					Where SA.OrgId=@rOrgId
						AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
					AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
					AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
					AND ((SA.BatchId= @rBatchId )) 
					AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			        AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
			        
					Order by RollNo
				END
				else
				BEGIN
				Select 
						SA.StudentId,SA.Code,SA.FirstName,SA.MiddleName,SA.LastName,SA.ClassName,SA.ClassId,SA.DivisionID,SA.Number,
						SA.DivisionName,convert(date,SO.DateOfBirth) DateOfBirth,AD.AdmissionDate,AD.AdmissionStandardId,SA.Name,Sa.RollNo,SO.GenderId
					From  
						 sStudent.vStudentAcademic SA
						INNER JOIN sStudent.tStudentOther SO 
									ON SO.StudentId=SA.StudentId
						LEFT JOIN sStudent.tStudentAdmission AD
									ON AD.StudentID = SA.StudentId  
					Where SA.OrgId=@rOrgId
						AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
					AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
					AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
					AND ((SA.BatchId= @rBatchId ))
					AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			        AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
					 
					Order by SA.Number--add
				END
			END
			
			IF @rSubCategory ='Main Group'
			BEGIN
				if @rOrderBY=1
				 BEGIN
					 Select 
						SA.StudentId,SA.Code,SA.FirstName,SA.MiddleName,SA.LastName,SA.ClassName,SA.ClassId,SA.DivisionID,SA.Number,
						SA.DivisionName,AD.Id,ISNULl(AD.Code,'') AS groupCode,ISNULl(AD.Title,'') AS Title,SA.Name,Sa.RollNo 
					From  
						 sStudent.vStudentAcademic SA
						
						LEFT JOIN sStudent.tStudentGroupMaster AD
									ON AD.Id = SA.StudentGroupId  
					Where SA.OrgId=@rOrgId
						AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
					AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
					AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
					AND ((SA.BatchId= @rBatchId )) 
					AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			        AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
					
					Order by RollNo
				END
				else
				BEGIN
				Select 
						SA.StudentId,SA.Code,SA.FirstName,SA.MiddleName,SA.LastName,SA.ClassName,SA.ClassId,SA.DivisionID,SA.Number,
						SA.DivisionName,AD.Id,ISNULl(AD.Code,'') AS groupCode,ISNULl(AD.Title,'') As Title,SA.Name,Sa.RollNo 
					From  
						 sStudent.vStudentAcademic SA
						
						LEFT JOIN sStudent.tStudentGroupMaster AD
									ON AD.Id = SA.StudentGroupId  
					Where SA.OrgId=@rOrgId
						AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
					AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
					AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
					AND ((SA.BatchId= @rBatchId )) 
					AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			        AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
					
					Order by SA.Number		--add
				END
			END
			
			IF @rSubCategory ='Other Group'
			BEGIN
				if @rOrderBY=1
				 BEGIN
					 Select 
						SA.StudentId,SA.Code,SA.FirstName,SA.MiddleName,SA.LastName,SA.ClassName,SA.ClassId,SA.DivisionID,SA.Number,
						SA.DivisionName,AD.Id,ISNULl(AD.Code,'') As groupCode,ISNULl(AD.Title,'') AS Title,SA.Name,Sa.RollNo 
					From  
						 sStudent.vStudentAcademic SA
						
						LEFT JOIN sStudent.tStudentOtherGroupMaster AD
									ON AD.Id = SA.StudentOtherGroupId  
					Where SA.OrgId=@rOrgId
						AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
					AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
					AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
					AND ((SA.BatchId= @rBatchId )) 
					AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			        AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
					
					Order by RollNo
				END
				else
				BEGIN
				Select 
						SA.StudentId,SA.Code,SA.FirstName,SA.MiddleName,SA.LastName,SA.ClassName,SA.ClassId,SA.DivisionID,SA.Number,
						SA.DivisionName,Ad.Id,ISNULl(AD.Code,'') As groupCode,ISNULl(AD.Title,'') As Title,SA.Name,Sa.RollNo 
					From  
						 sStudent.vStudentAcademic SA
						
						LEFT JOIN sStudent.tStudentOtherGroupMaster AD
									ON AD.Id = SA.StudentOtherGroupId  
					Where SA.OrgId=@rOrgId
						AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
					AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
					AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
					AND ((SA.BatchId= @rBatchId )) 
					AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			        AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
					
					Order by SA.Number	--add
				END
			END
			
			IF @rSubCategory ='Demographic'
			BEGIN
				if @rOrderBY=1
				 BEGIN
					 Select 
						SA.StudentId,SA.Code,SA.Name,Sa.RollNo,SO.ReligionId,SO.FamilyCast,SO.FamilySubCast,SO.CastCategoryId,SO.NationalityId,SA.Number
					From  
						 sStudent.vStudentAcademic SA
						LEFT JOIN sStudent.tStudentOther SO 
									ON SO.StudentId=SA.StudentId
					Where SA.OrgId=@rOrgId
						AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
					AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
					AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
					AND ((SA.BatchId= @rBatchId )) 
					AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			        AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
					
					Order by RollNo
				END
				else
				BEGIN
				Select 
						SA.StudentId,SA.Code,SA.Name,Sa.RollNo,SO.ReligionId,SO.FamilyCast,SO.FamilySubCast,SO.CastCategoryId,SO.NationalityId,SA.Number
					From  
						 sStudent.vStudentAcademic SA
						LEFT JOIN sStudent.tStudentOther SO 
									ON SO.StudentId=SA.StudentId
					Where SA.OrgId=@rOrgId
						AND ((SA.ClassId=@rClassId) OR (@rClassId =0))
					AND ((SA.DivisionId=@rDivisionId) OR  (@rDivisionId=0))
					AND ((SA.StatusId= @rStatusId ) OR  (@rStatusId  =0))
					AND ((SA.BatchId= @rBatchId )) 
					AND((SA.Code >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))  
			        AND((SA.Code <= @rToCode) OR (ISNULL(@rToCode,'') = '')) 
					
					
					Order by SA.Number		--add
				END
			END
			
		END
	 
		   
END
GO
PRINT N'Altering Procedure [sStudent].[pStudentDocumentClassWiseGet]...';


GO
ALTER PROCEDURE [sStudent].[pStudentDocumentClassWiseGet]
	@rClassId int=0,
	@rDivisionId int=0,
	@rRollNo int=NULL,
	@rOrgId int,
	@rCode nvarchar(20),
	@rStatusId smallint=0,
	@rFirstName nvarchar(100)='',
	@rLastName nvarchar(100)='',
	@rBatchId int,
    @rSUId nvarchar(50)='',
    @rUserId bigint=0,
    @rMultiCode nvarchar(max)='',
	@rDocumentId int=0
	
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	 
		BEGIN
			SET @rCode= sCommon.fSearchStringGet(@rCode)
			SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
			SET @rLastName = sCommon.fSearchStringGet(@rLastName)
		END
	IF(@rRollNo IS NULL)
	BEGIN
	SELECT 
			SA.AcademicId,
			SA.StudentId,
			SA.FirstName,
			SA.LastName,
			SA.Name,
			SA.Code,
			SA.ClassId,
			SA.ClassName,
			SA.DivisionId,
			SA.DivisionName,
			ISNULL(SA.Number,0) AS Number,
			SA.RollNo,
			SA.StatusId,
			SA.StatusName,
			SA.OrgId,
			SA.SUId,
			--SD.DocumentName
			--ISNULL(STUFF(
			-- (SELECT DISTINCT ',' + SDM.DocumentName
			--  FROM sCommon.tSubmitDocumentMaster SDM
			--  INNER JOIN  sStudent.tStudentDocument SD ON SDM.Id=SD.SubmitDocumentMasterId
			--  INNER JOIN sCommon.tSubmitDocumentScope DS ON DS.SubmitDocumentId=SDM.Id AND DS.SourceId = @rClassId
			--  where SD.StudentId=SA.StudentId AND SDM.SubmitDocumentFunctionalityId = 1 AND ((SDM.Id=@rDocumentId)OR(@rDocumentId=0))
			--  FOR XML PATH (''))
			--  , 1, 1, ''),'')  AS DocumentName,
			  ISNULL(sStudent.fStudentDocumentClasswiseget(@rDocumentId,SA.AcademicId),'') AS DocumentName
			
  FROM 
          sStudent.vStudentAcademic SA
		  --LEFT JOIN sStudent.tStudentDocument SD ON SD.StudentId=SA.StudentId
		  INNER JOIN sSecurity.tUserAccess U ON U.SourceId = SA.DivisionId AND U.SourceTypeId=2005
             
  WHERE 
 
		((SA.ClassId=@rClassId)OR(@rClassId=0))
		AND((SA.DivisionId=@rDivisionId)OR(@rDivisionId=0))
		AND((SA.Code like @rCode) OR (ISNULL(@rCode,'') = '')) 
		AND((SA.FirstName like @rFirstName) OR (ISNULL(@rFirstName,'') = '')) 
		AND((SA.LastName like @rLastName) OR (ISNULL(@rLastName,'') = '')) 
		AND((SA.StatusId=@rStatusId)OR(@rStatusId=0))	
		AND(SA.OrgId=@rOrgId)
		AND(SA.BatchId=@rBatchId)
		AND((SA.SUID = @rSUId) OR (ISNULL(@rSUId,'') = '')) 
		--AND((SA.Number=@rNumber)OR(@rNumber=0))	
		AND U.UserId = @rUserId
		AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
--ORDER BY CASE WHEN @rOrderByTypeId = 1 then SA.RollNo END,
--		 CASE WHEN @rOrderByTypeId = 2 then (CASE ISNUMERIC(SA.Code) WHEN 1 THEN REPLICATE(0 , 100 - LEN(SA.Code)) + SA.Code ELSE SA.Code  END) END,
			ORDER BY SA.ClassId,SA.DivisionId,SA.RollNo
END
ELSE
BEGIN
	SELECT 
			SA.AcademicId,
			SA.StudentId,
			SA.FirstName,
			SA.LastName,
			SA.Name,
			SA.Code,
			SA.ClassId,
			SA.ClassName,
			SA.DivisionId,
			SA.DivisionName,
			SA.Number,
			SA.RollNo,
			SA.StatusId,
			SA.StatusName,
			SA.OrgId,
			SA.SUId,
			--SD.DocumentName
			ISNULL(sStudent.fStudentDocumentClasswiseget(@rDocumentId,SA.AcademicId),'') AS DocumentName
			
			
  FROM 
          sStudent.vStudentAcademic SA
		  --LEFT JOIN sStudent.tStudentDocument SD ON SD.StudentId=SA.StudentId
          INNER JOIN sSecurity.tUserAccess U ON U.SourceId = SA.DivisionId AND U.SourceTypeId=2005
  WHERE 
 
		((SA.ClassId=@rClassId)OR(@rClassId=0))
		AND((SA.DivisionId=@rDivisionId)OR(@rDivisionId=0))
		AND((SA.Code like @rCode) OR (ISNULL(@rCode,'') = '')) 
		AND((SA.FirstName like @rFirstName) OR (ISNULL(@rFirstName,'') = '')) 
		AND((SA.LastName like @rLastName) OR (ISNULL(@rLastName,'') = '')) 
		AND(SA.RollNo=@rRollNo)	
		AND((SA.StatusId=@rStatusId)OR(@rStatusId=0))	
		AND(SA.OrgId=@rOrgId)
		AND(SA.BatchId=@rBatchId)
		AND((SA.SUID = @rSUId) OR (ISNULL(@rSUId,'') = '')) 
		AND U.UserId = @rUserId
		AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR @rMultiCode='')
		--AND((SA.Number=@rNumber)OR(@rNumber=0))	

--ORDER BY CASE WHEN @rOrderByTypeId = 1 then SA.RollNo END,
--		 CASE WHEN @rOrderByTypeId = 2 then (CASE ISNUMERIC(SA.Code) WHEN 1 THEN REPLICATE(0 , 100 - LEN(SA.Code)) + SA.Code ELSE SA.Code  END) END,
		 ORDER BY SA.ClassId,SA.DivisionId,SA.RollNo
END
  
	
END
GO
PRINT N'Altering Procedure [sStudent].[pStudentVerificationGet]...';


GO
ALTER PROCEDURE [sStudent].[pStudentVerificationGet]
	 
	@rClassId int=0,
	@rDivisionId int=0,
	@rRollNo int=0,
	@rOrgId int,
	--@rNumber bigint,
	@rCode nvarchar(20) = '',
	@rStatusId smallint=0,
	@rFirstName nvarchar(100)='',
	@rLastName nvarchar(100)='',
	@rBatchId int = 0,
	@rOrgGroupBatchId int = 0,
	@rDepartmentId int,
	@rOrgGroupId smallint,
	@rToRollNo int=0,
	@rFromCode nvarchar(20)='',
	@rToCode nvarchar(20)='',
	@rSpecificCode nvarchar(max)=''
	
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON			 

BEGIN 	 

	BEGIN
		SET @rCode= sCommon.fSearchStringGet(@rCode)
		SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
		SET @rLastName = sCommon.fSearchStringGet(@rLastName)
	END
 
	SELECT 
			SA.AcademicId,
			SA.StudentId,
			SA.ClassId,
			Sa.MiddleName,
			ISNULL(SA.AdmissionDate,'')As AdmissionDate,
			SA.PreviousSchool,
			ISNULL(SA.DateOfBirth,'') As DateOfBirth,
			SA.BirthPlace,
			SA.Gender,
			SA.FamilyCast,
			SA.CastCatagory AS CasteCategory,
			SA.MotherTongue,
			SA.Nationality,
			Sa.FatherName,
			SA.FatherContactNo,
			SA.Mothername,
			SA.MotherContactNo,
			SA.PresentStreetOne,
			SA.PresentStreetTwo,
			SA.PresentStreetThree,
			ISNULL(SA.StudentContactNo,'')As StudentContactNo,
			SA.UIDNumber,
			SA.AdhaarNumber,
			SA.BankBranch,
			SA.BankAccountNumber,
			SA.BankName,
			SA.BankIFSCCode,
			SA.DepartmentId,
			SA.FirstName,
			SA.LastName,
			SA.Name,
			SA.Code,
			SA.ADMISSIONStandardId, 
			SA.DivisionId,
			SA.DivisionName,
			ISNULL(SA.Number,0) AS Number,
			SA.RollNo,
			SA.StatusId,
			--SA.StatusName,
			SA.OrgId,
			--SA.Age,
			ISNULL(SA.AdmissionCategoryId,0) AS AdmissionCategoryId,
			SA.Religion,
			SA.StudentEmail,
			SA.Name,
			(select  Title  from sSetup.tClassMaster where OrgId=@rOrgId AND id=SA.ADMISSIONStandardId  ) As AdmissionStandard,
			SA.ClassName,
			SA.BirthState,
			SA.BirthCountry,
			SA.BirthTaluka,
			SA.BirthDistrict,
			SA.BankAccountHolderName
  FROM 
            sStudent.vStudentVerification SA  
  WHERE 
			((SA.ClassId=@rClassId)OR(@rClassId=0))
			AND((SA.DivisionId=@rDivisionId)OR(@rDivisionId=0))
			AND((SA.Code like @rCode) OR (ISNULL(@rCode,'') = '')) 
			AND((SA.Code >= @rFromCode) AND (SA.Code <= @rToCode) OR (ISNULL(@rFromCode,'') = '' OR ISNULL(@rToCode,'') = '')) 
			AND(SA.Code IN (SELECT CONVERT(nvarchar,item) from sCommon.fnSplit(@rSpecificCode,',')) OR ISNULL(@rSpecificCode,'') = '') 
			AND((SA.FirstName like @rFirstName) OR (ISNULL(@rFirstName,'') = '')) 
			AND((SA.LastName like @rLastName) OR (ISNULL(@rLastName,'') = '')) 
			AND((SA.StatusId=@rStatusId)OR(@rStatusId=0))	
			AND(SA.OrgId=@rOrgId OR @rOrgId = 0)
			AND (OrgGroupBatchId = @rOrgGroupBatchId	or @rOrgGroupBatchId = 0)	
			AND(SA.BatchId=@rBatchId or @rBatchId = 0)
			AND(SA.DepartmentId=@rDepartmentId or @rDepartmentId = 0)
			and ((sa.RollNO>=@rRollNO)OR(@rRollNo=0))
			and ((sa.RollNO<=@rToRollNo)OR(@rToRollNo=0))
			--AND((SA.Number=@rNumber)OR(@rNumber=0))	

			ORDER BY SA.ClassId,SA.DivisionId,SA.RollNo

--ORDER by CASE WHEN @rOrderByTypeId = 1 then SA.FirstName 
--			  WHEN @rOrderByTypeId = 2 then SA.LastName
--			  WHEN @rOrderByTypeId = 4 then (CASE IsNumeric (SA.Code) WHEN 1 THEN Replicate (0, 100 - Len (SA.Code)) + SA.Code  ELSE SA.Code  END) END,		--add
--		CASE WHEN @rOrderByTypeId = 3 then SA.RollNo END,
--		CASE WHEN @rOrderByTypeId = 5 then SA.AdmissionDate END  
--			  ,SA.ClassId, SA.DivisionId,SA.RollNo
 
END
GO
PRINT N'Altering Procedure [sStudent].[pSUIDGenerationGet]...';


GO
ALTER PROCEDURE [sStudent].[pSUIDGenerationGet]
	 
	 @rOrgGroupId smallint,
	 @rOrgId smallint,
	 @rDepartmentId int,
	 @rClassID int,
	 @rDivisionId int,
	 @rRecordStatusId smallint,
	 @rBatchId int=0,
	 @rOrderByTypeId smallint = 0	 

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	SELECT 
		vSA.StudentId,
		vSA.OrgId,
		vSA.OrganizationName,
		VSA.DepartmentName,
		vSA.ClassName+' - '+vSA.DivisionName AS ClassDivisionName,
		vSA.RollNo,
		vSA.Code,
		ISNULL(vSA.SUId,'') AS SUID,
		vSA.Name,
		SA.AdmissionDate,
		GM.Title As Gender,
		(Select CM.Title from sSetup.tClassMaster CM WHere CM.Id=SA.AdmissionStandardId) AS AdmissionStandard,
		vSA.FirstName,
		vSA.LastName,
		SA.AdmissionDate,
		vSA.ClassID,
		vSA.AcademicId,
		vSA.DivisionId,
		vSA.Number
			
	FROM 
		sStudent.vStudentAcademic vSA
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = vSA.OrgId
		INNER JOIN sStudent.tStudentAdmission SA ON SA.StudentId=vSA.StudentId
		INNER JOIN sStudent.tStudentOther SO ON So.StudentId=vSa.StudentId
		INNER JOIN sCommon.tGenderMaster GM ON Gm.Id=SO.GenderID
	WHERE 
		(OM.OrgGroupId = @rOrgGroupId)
		AND (vSA.OrgId=@rOrgId)
		AND (vSA.BatchId=@rBatchId)
		AND ((vSA.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((vSA.RecordStatusId=@rRecordStatusId) OR (@rRecordStatusId=0))
	ORDER BY OM.OrderIndex,vSA.DivisionOrder,vSA.RollNo
	--ORDER by CASE WHEN @rOrderByTypeId = 1 then vSA.FirstName
	--			  WHEN @rOrderByTypeId = 2 then vSA.LastName
	--			  WHEN @rOrderByTypeId = 4 then (CASE ISNUMERIC(vSA.Code) WHEN 1 THEN REPLICATE(0 , 100 - LEN(vSA.Code)) + vSA.Code ELSE vSA.Code  END) END,	--add inner case
	--	CASE WHEN @rOrderByTypeId = 3 then vSA.RollNo END,
	--	CASE WHEN @rOrderByTypeId = 5 then SA.AdmissionDate END
	--		  ,OM.OrderIndex,vSA.DivisionOrder,vSA.RollNo
END
GO
PRINT N'Altering Procedure [sTool].[pDeleteStaticData]...';


GO
 
ALTER PROCEDURE [sTool].[pDeleteStaticData]
 
	 
AS
SET NOCOUNT ON
BEGIN 
 

DELETE FROM sAlert.tEventMaster
DELETE FROM sCommon.tAutoCompleteMaster
DELETE FROM sCommon.tCommonMaster
DELETE FROM sCommon.tDepends
DELETE FROM sCommon.tModuleMaster
DELETE FROM sCommon.tPrefixFunctionality
DELETE FROM sCommon.tRefferedTableForObsoleteCheck
DELETE FROM sCommon.tRefferedTableWithoutForeignKey
DELETE FROM sCommon.tReport
DELETE FROM sCommon.tReportFormatGroupMaster
DELETE FROM sCommon.tReportGroupMaster
DELETE FROM sCommon.tSourceScreenMaster
DELETE FROM sCommon.tSourceTypeMaster
DELETE FROM sCommon.tStatusMaster
DELETE FROM sCommon.tStatusTypeMaster
DELETE FROM sCommon.tTableRowIdMaster
DELETE FROM sCommon.tUpdateFieldMaster
DELETE FROM sCommon.tValidationType
DELETE FROM sConfig.tColorLegendMaster
DELETE FROM sConfig.tConfigurationMaster
DELETE FROM sConfig.tConfigurationValue
DELETE FROM sSecurity.tMenu
DELETE FROM sSecurity.tResourceTypeMaster
DELETE FROM sSecurity.tScreen
DELETE FROM sSecurity.tScreenGroupMaster
DELETE FROM sSecurity.tScreenTypeMaster
DELETE FROM sSecurity.tTask
DELETE FROM sSystem.tTable
DELETE FROM sTool.tObjects
DELETE FROM sCommon.tReportTypeMaster


DELETE FROM sSetup.tAffiliationClassGroup
DELETE FROM sSetup.tAffiliationTypeMaster
DELETE FROM sSetup.tAffiliationMaster	
DELETE FROM sSetup.tStreamMaster

-- temp insert
DELETE FROM sAttendance.tAttendanceForMaster
DELETE FROM sEExam.tEntryModeMaster
DELETE FROM sSetup.tTermScopeMaster	 
 
DELETE FROM sConfig.tConfigurationLevelMaster

DELETE FROM sAdmission.tInquiryModeMaster

DELETE FROM sFee.tFeeMainGroupMaster

DELETE FROM sPayroll.tPayElementDefault

DELETE FROM sSecurity.tScreenGroupMapping
DELETE FROM sAssessment.tCCEExamMaster
DELETE FROM sSetup.tTermPatternMaster


DELETE FROM sAdmission.tAdmissionStatusMaster
DELETE FROM sAssessment.tCCERptGroup
DELETE FROM sHrm.tEmployeeICardTemplate
DELETE FROM sSecurity.tDashletMaster
DELETE FROM sStudent.tICardTemplate
DELETE FROM sSystem.tCustomQuery

DELETE FROM sCommon.tRangeGroupMaster

DELETE FROM sHostel.tLogStatusMaster

DELETE FROM sSetup.tStudentNameFormatMaster

DELETE FROM sCommon.tMainCastCategoryMaster

DELETE FROM sOp.tFunctionalityMaster
DELETE FROM sOp.tPaymentGatewayMaster

DELETE FROM sOp.tPayTypeMaster
DELETE FROM sOp.tTransactionStatusMaster
--DELETE FROM sPayroll.tForm16B
DELETE FROM sStudent.tBatchStatusMaster
DELETE FROM [sFinanceCommon].[tPayTypeMaster]

DELETE FROM sAttendance.tAttendanceTagMaster
DELETE FROM sCommon.tLiveWithMaster
DELETE FROM sConfig.tDisplayFieldMaster

DELETE FROM sActivity.tEventMainTypeMaster
DELETE FROM sAlert.tOSType

DELETE FROM sCommon.tCalculationTypeMaster
DELETE FROM sFee.tApplayAsMaster
DELETE FROM sSecurity.tScreenDependency
DELETE FROM sStudent.tSUIdFieldMaster

DELETE FROM [sAlert].[tAlertModeMaster]
DELETE FROM sCommon.tReportRemarkGroupMaster
DELETE FROM sStudent.tEnrollmentStatusMaster
DELETE FROM [sSetup].[tClassGroupMaster]

DELETE FROM sCommon.tTemplateSourceMaster
DELETE FROM sErp.tCustomerStatusMaster
DELETE FROM sErp.tInstallationTypeMaster
DELETE FROM sRecruitment.tRecruitmentStatusMaster

DELETE FROM sAssessment.tStaticCCECSMaster
DELETE FROM sAssessment.tStaticCCECSGroupMaster

DELETE FROM siTax.tStaticIncomeTaxGroup
DELETE FROM siTax.tStaticIncomeTaxSubGroup

DELETE FROM sCommon.tImportFieldMaster
DELETE FROM sCRM.tHostingTypeMaster
DELETE FROM sHrm.tMainLeaveTypeMaster

DELETE FROM sAppraisal.tConductIndicatorMaster
DELETE FROM sHrm.tTravelPeriodMaster
DELETE FROM sLoan.tInsterestTypeMaster

DELETE FROM sAppraisal.tBHRatingStatus
DELETE FROM sAppraisal.tConductIndicatorMaster
DELETE FROM sAppraisal.tKPAMeasureMaster
DELETE FROM sAppraisal.tKPATypeMaster
DELETE FROM sAppraisal.tRatingStatus
DELETE FROM sCommon.tHeaderDisplayMaster
DELETE FROM sCommon.tModuleGroupMaster
DELETE FROM sHrm.tLeaveTransactionMaster
DELETE FROM sCommon.tCheckFrequencyMaster

DELETE FROM sErp.tLeadAffiliationMaster
DELETE FROM sErp.tLeadSourceMaster
DELETE FROM sErp.tLeadStatusMaster

DELETE FROM sSystem.tStaticVersion

DELETE FROM sCommunication.tFeedBackAnswerGroupMaster

DELETE FROM sCommon.tSubmitDocumentFunctionalityMaster
DELETE FROM sSetup.tMandatoryFeild
DELETE FROM sSetup.tMandatoryFeildFunctionalityMaster

DELETE FROM sSystem.tTimeZone
DELETE FROM sTool.tTestCaseTypeMaster

DELETE FROM sAlumni.tEmployeementType

DELETE FROM sErp.tCampusTypeMaster

DELETE FROM sAlert.tMailTypeMaster
DELETE FROM sAlert.tSMSVendorMaster
DELETE FROM sCommon.tWeekOffRules

DELETE FROM sSecurity.tScreenHelp
DELETE FROM sSystem.tSettingInfo

DELETE FROM sProcurement.tOtherLocationSourceTypeMaster

DELETE FROM sCommon.tFieldTypeMaster
DELETE FROM sErp.tLeadInfrastructureMaster

DELETE FROM sHostel.tHostelEventMainTypeMaster

DELETE FROM sCertificate.tCertificateTypeMaster	
DELETE FROM sWorkflow.tActionStatusMaster
DELETE FROM sWorkflow.tApprovingDepartmentMaster
DELETE FROM sWorkflow.tWorkflowFunctionalityMaster

DELETE FROM sAcademic.tHomeworkCheckStatusMaster
DELETE FROM sAssessment.tExamTypeMaster

DELETE FROM sErp.tMarketingActivityMaster
DELETE FROM sErp.tMarketingSubActivityMaster
DELETE FROM sSetup.tCommonOrgGroupBatchMaster
DELETE FROM sWorkflow.tWorkflowFunctionalityMaster

DELETE FROM sAudit.tOperationMaster

DELETE FROM sAdmission.tInquiryMainStatusMaster
DELETE FROM sCertificate.tPrintTemplateFunctionalityMaster
DELETE FROM sErp.tTripLogStatusMaster

DELETE FROM sErp.tFollowupTypeMaster
DELETE FROM sErp.tSupportCategoryMaster
DELETE FROM sErp.tSupportPriorityMaster
DELETE FROM sErp.tSupportTicketStatusMaster


DELETE FROM sErp.tPlugInMaster
DELETE FROM sErp.tTagMaster

DELETE FROM sErp.tIssueTypeMaster

DELETE FROM sErp.tScreenFunctionalityMaster

DELETE FROM sHrm.tChangeTypeMaster


DELETE FROM sCommon.tDataMaster
DELETE FROM sCommon.tDataTypeMaster

DELETE FROM siTax.tStaticIncomeTaxSubSubGroup

DELETE FROM sCommunication.tStudentDocumentTypeMaster

DELETE FROM sCommon.tUploadFunctionalityMaster
DELETE FROM sCommunication.tEmployeeDocumentTypeMaster

DELETE FROM sErp.tCustomerMessageTypeMaster

DELETE FROM sActivity.tActivityMainTypeMaster
DELETE FROM sSecurity.tTraceabilityMaster
DELETE FROM sSecurity.tTraceabilityTrack

DELETE FROM sSecurity.tScreenScopeDetails
DELETE FROM sBiometric.tEmployeeConfigurationMaster
DELETE FROM sSetup.tEmployeeNameFormatMaster
DELETE FROM sStudent.tOnlineRegistrationDisplayFieldMaster

DELETE FROM sSetup.tFormTypeMaster
DELETE FROM sSetup.tFormButtonMaster

DELETE FROM sSecurity.tSysSuperScreen
DELETE FROM sAppraisal.tKPAStatus

END
GO
PRINT N'Creating Procedure [sAlert].[pAlertStatusUpdate]...';


GO
CREATE PROCEDURE sAlert.pAlertStatusUpdate
	 
	@rOrgId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN	
BEGIN TRY
BEGIN TRANSACTION	
 
    BEGIN 
		IF OBJECT_ID('tempdb..#TempAlertLog') IS NOT NULL DROP TABLE #TempAlertLog
		IF OBJECT_ID('tempdb..#TempNotificationLog') IS NOT NULL DROP TABLE #TempNotificationLog

		IF EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE name = 'NVQueue')
		BEGIN

			CREATE TABLE #TempAlertLog
			(
				Id bigint IDENTITY,
				AlertLogId bigint,
				AlertModeId tinyint
			)
		
			INSERT INTO #TempAlertLog SELECT Id, AlertModeId FROM [sAlert].[tAlertLog] WHERE StatusId = 0
		
			DECLARE @lCount int = (SELECT COUNT(Id) FROM #TempAlertLog)

			IF (@lCount > 0)
			BEGIN
				WHILE(@lCount > 0)
				BEGIN
					DECLARE @lId bigint = (SELECT AlertLogId FROM #TempAlertLog WHERE Id = @lCount)
					DECLARE @lAlertModeId int = (SELECT AlertModeId FROM #TempAlertLog WHERE Id = @lCount)

					IF EXISTS(SELECT 1 FROM [NVqueue].[dbo].[QueueDetailLog] WHERE AlertLogId = @lId) 
					BEGIN 
						DECLARE @lResponse int=0,@lResponseText nvarchar(100)=0
						
						SELECT @lResponse = CASE WHEN ISNULL(IsError, 0) = 1 THEN -1 ELSE ISNULL(IsSuccess, 0) END 
						FROM [NVqueue].[dbo].[QueueDetailLog] WHERE AlertLogId = @lId AND @lAlertModeId IN (1, 4)
						
						UPDATE [sAlert].[tAlertLog] SET StatusId = @lResponse WHERE Id = @lId AND AlertModeId IN (1, 4)


						SELECT 
							@lResponse = CASE WHEN ISNULL(IsError, 0) = 1 THEN -1 ELSE ISNULL(IsSuccess, 0) END,
							@lResponseText = ISNULL(Response, '')
						FROM [NVqueue].[dbo].[QueueDetailLog] 
						WHERE AlertLogId = @lId AND @lAlertModeId = 2
						
						UPDATE [sAlert].[tAlertLog] 
						SET StatusId = CASE WHEN @lResponse = -1 THEN -1 ELSE (SELECT Id FROM sAlert.tSMSStatusMaster WHERE Code = @lResponseText) END
						WHERE Id = @lId AND AlertModeId = 2
					END

					SET @lCount = @lCount - 1
				END
			END

			CREATE TABLE #TempNotificationLog
			(
				Id bigint IDENTITY,
				AlertLogId bigint
			)
		
			INSERT INTO #TempNotificationLog SELECT Id FROM [sAlert].[tNotificationLog] WHERE ISNULL(APIResponse, '') = ''
		
			DECLARE @lCount1 int = (SELECT COUNT(Id) FROM #TempNotificationLog)

			IF (@lCount1 > 0)
			BEGIN
				WHILE(@lCount1 > 0)
				BEGIN
					DECLARE @lId1 bigint = (SELECT AlertLogId FROM #TempNotificationLog WHERE Id = @lCount1)
					IF EXISTS(SELECT 1 FROM [NVqueue].[dbo].[QueueDetailLog] WHERE AlertLogId = @lId1) 
					BEGIN 
						DECLARE @lAPIResponse nvarchar(500) = ''
						SELECT @lAPIResponse = ISNULL(Response, '') 
						FROM [NVqueue].[dbo].[QueueDetailLog] WHERE AlertLogId = @lId1 AND IsSuccess = 1

						UPDATE [sAlert].[tNotificationLog] SET APIResponse = @lAPIResponse WHERE Id = @lId1 
					END
					SET @lCount1 = @lCount1 - 1
				END
			END
		END

		DROP TABLE #TempAlertLog
		DROP TABLE #TempNotificationLog		 
		SELECT 1 As Result
	END  
	
	COMMIT TRANSACTION
	
END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
END
GO
PRINT N'Creating Procedure [sAssessment].[pRptResultListConsolidatedMarkRangeWiseGet]...';


GO
CREATE PROCEDURE [sAssessment].[pRptResultListConsolidatedMarkRangeWiseGet]
	 
	 @rBuilder1 nvarchar(max)='', 
	 @rBuilder2 nvarchar(max)='', 
	 @rBatchId int = 0,
	 @rClassId int=0,
	 @rDivisionId int=0,
	 @rExamId int,
	 @rCode nvarchar(20)='',
	 @rFromRollNo int,
	 @rToRollNo int,
	 --@rFromRank int=0,
	 --@rToRank int=0,
	 --@rGrade nvarchar(20)='',
	 --@rResultId bigint=0,
	 @rStatusId int=0,
	 @rAcademicId bigint=0,
	 @rFilterId int=0,
	 @rOrgGroupBatchId int=0,
	 @rClassStatusId int=0
	 		 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN

SELECT 
	@rBuilder1 AS Builder1,
	@rBuilder2 AS Builder2,
	ESGM.Title AS ExamName,
	TSD.EmployeeId,
	EmpM.FirstName,
	EmpM.MiddleName,
	EmpM.LastName,
	EmpM.FirstName + ' ' + EmpM.MiddleName + ' ' + EmpM.LastName AS EmployeeName,
	EmpM.Code AS EmployeeCode,
	SM.Id AS SubjectId,
	SM.Code AS SubjectCode,
	SM.Title AS SubjectName,
	DM.Id AS DivisionId,
	DM.DisplayTitle AS DivisionName,
	DM.OrderIndex AS DivisionOrder,
	CM.Id AS ClassId,
	CM.DisplayTitle AS ClassName,
	CM.OrderIndex AS ClassOrder,
	(CM.DisplayTitle + ' - ' + DM.DisplayTitle) AS ClassDivision,
	A.Id As AcademicId,
	EM.Id AS ExamId,
	RD.Obtain AS SubjectObtain,
	ED.Maximum AS SubjectMaximum,
	CONVERT(Int,ROUND((ISNULL(RD.Obtain, 0) * 100) / ED.Maximum, 0)) AS SubjectPercentage,
	RD.SubjectGrade,
	BM.Title AS BatchName

	FROM 
		sAssessment.tResultDetail RD
		INNER JOIN sAssessment.tResult R ON R.Id = RD.ResultId
		INNER JOIN sAssessment.tExamDetail ED ON ED.Id=RD.ExamDetailId
		INNER JOIN sAssessment.tExamMaster EM ON EM.Id=ED.ExamId		
		INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.EXAMSUBGROUPID
		INNER JOIN sSetup.tClassSubject CS ON CS.Id=ED.ClassSubjectId
		INNER JOIN sSetup.tSubjectMaster SM ON SM.Id=CS.SubjectId					 
		INNER JOIN sStudent.tAcademic A ON A.Id=R.AcademicId	
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId		
		INNER JOIN sStudent.tStudentMaster StuM ON StuM.Id=A.StudentId			
		INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
		INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId			
		--INNER JOIN sSetup.tOrganizationMaster OM ON StuM.OrgId = OM.Id			
		INNER JOIN sAcademic.tTeacherSubjectDivision TSD ON SM.Id = TSD.SubjectId AND DM.Id = TSD.DivisionId
		INNER JOIN sHrm.tEmployeeMaster EmpM ON TSD.EmployeeId = EmpM.Id 			
		LEFT JOIN sHostel.tAssignRoom ARO ON  ARO.SourceId=StuM.Id AND ARO.SourceTypeId=2003 AND ((BM.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))  AND ((ARO.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0) AND (BM.OrgGroupBatchId = ARO.OrgGroupBatchId)) 
		LEFT JOIN
		(SELECT AT.SourceId,B.OrgGroupBatchId FROM sTransport.tAssignRoute AT 
		INNER JOIN sTransport.tBusRouteDestinationMaster BM ON BM.Id=AT.PickupDestinationId 
		INNER JOIN sTransport.tBusRouteMaster B ON B.Id=BM.BusRouteId AND  AT.SourceTypeId=2003 AND ((B.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))) RT ON RT.SourceId = StuM.Id 
			
	WHERE EM.Id = @rExamId
		AND ((EM.BatchId = @rBatchId) OR  (@rBatchId = 0))
		AND ((A.ClassId=@rClassId) OR (@rClassId=0)) 
		AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((A.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))
		AND ((A.RollNo <= @rToRollNo) OR (@rToRollNo=0))
		--AND ((R.RankNumber >= @rFromRank) OR (@rFromRank=0))
		--AND ((R.RankNumber <= @rToRank) OR (@rToRank=0))
		--AND ((R.ResultGrade = @rGrade) OR (@rGrade=''))
		AND ((StuM.Code=@rCode) OR (@rCode=''))
		--AND ((R.Id = @rResultId) OR (@rResultId = 0))
		AND ((StuM.StatusId=@rStatusId)OR(@rStatusId=0))
		
		 AND (
	 (@rFilterId = 0) -- All
     OR (@rFilterId = 2 AND isnull(ARO.SourceId,0)!=0) -- Hostel
     OR (@rFilterId = 3 AND isnull(RT.SourceId,0)!=0) -- Transport
     OR ((@rFilterId =1) AND ((isnull(ARO.SourceId,0)=0) AND (isnull(RT.SourceId,0)=0)))
    ) 
	AND ((A.ClassStatusId =  @rClassStatusId) OR (@rClassStatusId=0))
END
GO
PRINT N'Creating Procedure [sAssessment].[pRptResultListConsolidatedWithMarks2Get]...';


GO

CREATE PROCEDURE [sAssessment].[pRptResultListConsolidatedWithMarks2Get]
	 
	 @rBuilder1 nvarchar(max)='', 
	 @rBuilder2 nvarchar(max)='', 
	 @rBatchId int = 0,
	 @rClassId int=0,
	 @rDivisionId int=0,
	 @rExamId int,
	 @rCode nvarchar(20)='',
	 @rFromRollNo int,
	 @rToRollNo int,
	 @rFromRank int=0,
	 @rToRank int=0,
	 @rGrade nvarchar(20)='',
	 @rResultId bigint=0,
	 @rStatusId int=0,
	 @rAcademicId bigint=0,
	 @rFilterId int=0,
	 @rOrgGroupBatchId int=0,
	 @rClassStatusId int=0
	 
	
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	--Select
	--	'' AS AcademicIdText,
	--	'' AS ExamIdText,
	--	'' AS IsDisplayMonthlyAttendanceText,
	--	'' AS AnnualExam,
	--	'' AS Builder1,
	--	'' Code,
	--	'' AS Name,
	--	0 AS RollNo,
	--	Convert(BigInt,0) AcademicId,			
	--	'' ResultStatus,
	--	'' AS Class,
	--	'' AS Division,
	--	Convert(Decimal,0) TermTotalMark,
	--	Convert(Decimal,0) TermTotalObtain,
	--	0 AS TermRankNumber,
	--	Convert(Decimal,0) TermPercentage,
	--	'' AS TermGrade,
	--	Convert(Decimal,0) TermPercentile,			
	--	Convert(BigInt,0) ResultDetailId,		
	--	Convert(Bit,0) IsDisplayInMarksheet,
	--	Convert(Bit,0) IsShowSubjectGrade,
	--	Convert(Bit,0) IsIncludeInTotal,
	--	Convert(Decimal,0) AS SubjectObtain,
	--	Convert(Decimal,0) AS SubjectMinimum,
	--	Convert(Decimal,0) AS SubjectMaximum,			
	--	'' AS SubjectCode,
	--	'' AS SubjectName,
	--	0 AS SubjectOrderIndex,
	--	Convert(BigInt,0) AS ResultId,
	--	0 AS SubjectId,			 	
	--	Convert(Bit,0) IsMarksInDecimal,
			
	--	'' AS Remark1,
	--	'' AS Remark2,
	--	'' AS Remark3,
	--	'' AS DisplayTitle,
	--	Convert(Bit,0) IsRankNo, 
	--	Convert(Bit,0) IsGrade,
	--	Convert(Bit,0) IsPercentile,
	--	Convert(Bit,0) IsPercentage,
	--	Convert(Bit,0) IsSubjectPercentile,
	--	Convert(Bit,0) IsProcessResult,			 
	--	Convert(Bit,0) IsNegativeMarks,
	--	Convert(Bit,0) IsIncludeCoScholastic,
	--	Convert(Bit,0) IsOnPrePrinted,
	--	Convert(Bit,0) IsDisplayPhoto,
	--	Convert(Bit,0) IsDisplayBirthDate,
	--	Convert(Bit,0) IsLockForEdit,
	--	Convert(Bit,0) IsDisplaySchoolName,			
	--	Convert(Bit,0) IsDisplayAttendance,
	--	Convert(Bit,0) IsDisplayHeightWeight,
	--	Convert(Bit,0) IsResultStatus,
	--	'' AS FooterAdjustmentText,
	--	'' AS DetailAdjustmentText,
	--	0 AS FooterAdjustment,
	--	0 AS DetailAdjustment,		 		 
	--	0 AS TermScopeId,		 
	--	Convert(Bit,0) IsPresent,
	--	'' AS GradeString,
	--	Convert(Bit,0) IsDisplayGradeString,
	--	convert(Decimal,0) HighestDivisionMark,
	--	''  AS HighestDivisionGrade,			
	--	'' SmallAddress1,
	--	Convert(Bit,0) IsDisplayParentName,			 
	--	Convert(Bit,0) AS IsShowSubjectMark,			
	--	Convert(Bit,0) IsDisplayTeacherSign,
	--	Convert(Bit,0) IsDisplayPrincipalSign,		 
	--	Convert(Bit,0) AS IsDisplayMonthlyAttendance,			 
	--	Convert(Bit,0) AS IsImageHeader,			 
	--	0 AS ExamOrderIndex,
	--	0 AS AnnualExamId,
	--	'' AS DecimalFormat,
	--	0 TermExamId,
	--	'' AS ExamName, 			 
	--	'' AS FatherName,
	--	'' AS MotherName,
	--	CONVERT(bit,0) AS IsAbsentRemark,
	--	'' AS AbsentRemark
	

 
DECLARE @GradeString nvarchar(1000),
@GradeSetId int 
 
SELECT @GradeSetId = GradesetId FROM sAssessment.tExamMaster WHERE Id = @rExamId
SELECT @GradeString = [sAssessment].[fGradeStringGet](@GradeSetId)  

 DECLARE @lAbsentRemarkConfig nvarchar(20) = '' , @lOrgId int
 SELECT @lOrgId=OrgId FROM sSetup.tBatchMaster WHERE Id=@rBatchId
 SELECT @lAbsentRemarkConfig =ItemValue From sConfig.tConfigurationSetup WHERE ConfigurationId = 515 AND SourceTypeId = 1016 AND SourceId = @lOrgId

CREATE TABLE #TempExam
( 
	ExamOrderIndex int,
	TermExamId int,
	AnnualExamId int, 
	Exam nvarchar(50) 
)


INSERT INTO #TempExam
(
	 ExamOrderIndex,
	 TermExamId,
	 AnnualExamId,
	 Exam 	
)
 
SELECT EC.ExamOrder,
EM.Id AS TermExamId,
@rExamId AS AnnualExamId,
ESGM.Code AS Exam
  FROM [sAssessment].[tExamMaster] EM 
		INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.ID = EM.ExamSubGroupId 
		INNER JOIN sAssessment.tExamCombine EC ON EC.SubExamId=EM.Id AND EC.ExamId =@rExamId  AND (EM.ClassId=@rClassId) AND EM.BatchId =@rBatchId
	
	SELECT 
			Convert(nvarchar(20),A.Id) AS AcademicIdText,
			Convert(nvarchar(20),@rExamId) AS AnnualExamIdText,
			Convert(nvarchar(20),ISNULL(EM.IsDisplayMonthlyAttendance,0)) AS IsDisplayMonthlyAttendanceText,
			ESGM.Title AS AnnualExam,
			 @rBuilder1 AS Builder1,
			StuM.Code,
			[sStudent].[fStudentNameGet](stum.FirstName , StuM.MiddleName, StuM.LastName,OM.StudentNameFormatId ) AS Name,
			A.RollNo,
			R.AcademicId,			
			R.ResultStatus,
			CM.DisplayTitle AS Class,
			DM.DisplayTitle AS Division,
			R.TotalMarks AS TermTotalMark,
			R.TotalObtain AS TermTotalObtain,
			R.RankNumber AS TermRankNumber,
			R.Percentage AS TermPercentage,
			R.ResultGrade AS TermGrade,
			R.Percentile AS TermPercentile,			
			RD.Id AS ResultDetailId,		
			ED.IsDisplayInMarksheet,
			ED.IsShowSubjectGrade,
			ED.IsIncludeInTotal,
			RD.Obtain AS SubjectObtain,
			ED.Minimum AS SubjectMinimum,
			ED.Maximum AS SubjectMaximum,			
			SM.Code AS SubjectCode,
			SM.Title AS SubjectName,
			(CASE ISNULL(ED.DisplayOrder,0)
			WHEN 0 THEN CS.OrderIndex
			ELSE ED.DisplayOrder END) AS SubjectOrderIndex,
			R.Id AS ResultId,
			CS.Id AS SubjectId,			 	
			EM.IsMarksInDecimal,			
			ISNULL(EM.Remark1,'') AS Remark1,
			ISNULL(EM.Remark2,'') AS Remark2,
			ISNULL(EM.Remark3,'') AS Remark3,
			EM.DisplayTitle,
			EM.IsRankNo, 
			EM.IsGrade,
			EM.IsPercentile,
			EM.IsPercentage,
			EM.IsSubjectPercentile,
			EM.IsProcessResult,			 
			EM.IsNegativeMarks,
			EM.IsIncludeCoScholastic,
			EM.IsOnPrePrinted,
			EM.IsDisplayPhoto,
			EM.IsDisplayBirthDate,
			EM.IsLockForEdit,
			EM.IsDisplaySchoolName,			
			EM.IsDisplayAttendance,
			EM.IsDisplayHeightWeight,
			EM.IsResultStatus,
			REPLICATE(',',EM.FooterAdjustment) AS FooterAdjustmentText,
			REPLICATE(',',EM.DetailAdjustment) AS DetailAdjustmentText,
			EM.FooterAdjustment,
			EM.DetailAdjustment,		 		 
			EM.TermScopeId,		 
			RD.IsPresent,
			@GradeString AS GradeString,
			EM.IsDisplayGradeString,
			(CASE EM.IsDisplayHighestDivisionMark
			WHEN 1 THEN [sAssessment].[fGetHighestDivisionMark](A.DivisionId ,RD.ExamDetailId)
			ELSE 0 END) AS HighestDivisionMark,
			(CASE EM.IsDisplayHighestDivisionGrade
			WHEN 1 THEN [sAssessment].[fGetHighestDivisionGrade] (A.DivisionId ,RD.ExamDetailId)
			ELSE '' END) AS HighestDivisionGrade,			
			OM.SmallAddress1,
			EM.IsDisplayParentName,			 
			ISNULL(ED.IsShowSubjectMark,1) AS IsShowSubjectMark,			
			EM.IsDisplayTeacherSign,
			Em.IsDisplayPrincipalSign,		 
			ISNULL(EM.IsDisplayMonthlyAttendance,0) AS IsDisplayMonthlyAttendance,			 
			ISNULL(EM.IsDisplayImageHeader,0) AS IsImageHeader,			 
			EC.ExamOrderIndex,
			EC.AnnualExamId ,
			EM.DecimalFormat,
			EC.TermExamId,
			EC.Exam AS ExamName, 						 
			SF.FatherName,
			SF.MotherName,
			CONVERT(bit,@lAbsentRemarkConfig) AS IsAbsentRemark,
			RD.AbsentRemark
						
	FROM 
			sAssessment.tResultDetail RD
			INNER JOIN sAssessment.tResult R ON R.Id = RD.ResultId
			INNER JOIN sAssessment.tExamDetail ED ON ED.Id=RD.ExamDetailId
			INNER JOIN sAssessment.tExamMaster EM ON EM.Id=ED.ExamId		
			INNER JOIN #TempExam EC ON EC.TermExamId = EM.Id 	
			INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.ExamSubGroupId
			INNER JOIN sSetup.tClassSubject CS ON CS.Id=ED.ClassSubjectId
			INNER JOIN sSetup.tSubjectMaster SM ON SM.Id=CS.SubjectId					 
			INNER JOIN sStudent.tAcademic A ON A.Id=R.AcademicId	
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId		
			INNER JOIN sStudent.tStudentMaster StuM ON StuM.Id=A.StudentId			
			INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
			INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId			
			INNER JOIN sSetup.tOrganizationMaster OM ON StuM.OrgId = OM.Id			
						
			LEFT JOIN sHostel.tAssignRoom ARO ON  ARO.SourceId=StuM.Id AND ARO.SourceTypeId=2003 AND ((BM.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))  AND ((ARO.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0) AND (BM.OrgGroupBatchId = ARO.OrgGroupBatchId)) 
		    LEFT JOIN
			(SELECT AT.SourceId,B.OrgGroupBatchId FROM sTransport.tAssignRoute AT 
			INNER JOIN sTransport.tBusRouteDestinationMaster BM ON BM.Id=AT.PickupDestinationId 
			INNER JOIN sTransport.tBusRouteMaster B ON B.Id=BM.BusRouteId AND  AT.SourceTypeId=2003 AND ((B.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))) RT ON RT.SourceId = StuM.Id 
		
			LEFT JOIN sAssessment.tStudentRemark SR ON R.AcademicId = SR.AcademicId AND  SR.ExamId=EC.AnnualExamId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = StuM.Id
			
	WHERE 
			
		((EM.BatchId = @rBatchId) OR  (@rBatchId = 0))
		AND ((A.ClassId=@rClassId) OR (@rClassId=0)) 
		AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((A.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))
		AND ((A.RollNo <= @rToRollNo) OR (@rToRollNo=0))
		AND ((R.RankNumber >= @rFromRank) OR (@rFromRank=0))
		AND ((R.RankNumber <= @rToRank) OR (@rToRank=0))
		AND ((R.ResultGrade = @rGrade) OR (@rGrade=''))
		AND ((StuM.Code=@rCode) OR (@rCode=''))
		AND ((R.Id = @rResultId) OR (@rResultId = 0))
		AND ((StuM.StatusId=@rStatusId)OR(@rStatusId=0))
		
		 AND (
	 (@rFilterId = 0) -- All
     OR (@rFilterId = 2 AND isnull(ARO.SourceId,0)!=0) -- Hostel
     OR (@rFilterId = 3 AND isnull(RT.SourceId,0)!=0) -- Transport
     OR ((@rFilterId =1) AND ((isnull(ARO.SourceId,0)=0) AND (isnull(RT.SourceId,0)=0)))
    ) 
	AND ((A.ClassStatusId =  @rClassStatusId) OR (@rClassStatusId=0))
		
	 drop table #TempExam
	 
END
GO
PRINT N'Creating Procedure [sAttendance].[pRptStudentwiseAbsentDaysDetailsGet]...';


GO

Create PROCEDURE [sAttendance].[pRptStudentwiseAbsentDaysDetailsGet]
	 
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rOrgId smallint,
	 @rBatchId int,
	 @rClassId int,
	 @rDivisionId int,
	 @rAttendanceTypeId tinyint,
	 @rSubjectId int,
	 @rFromDate date,
	 @rToDate date,
	 @rStudentCode nvarchar(20),
	 @rDepartmentId int
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
 
	SELECT 
			DISTINCT(SA.AcademicId),
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			SA.ClassId,
			SA.DivisionId,
			SA.ClassName,
			SA.DivisionName,
			SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
			SA.Code,
			SA.RollNo,			
			SA.Name,			
			sAttendance.fAttendanceGet(SA.AcademicId,@rAttendanceTypeId,@rSubjectId,@rFromDate,@rToDate) AS AttendanceDate,
			AbsentCount=(SELECT COUNT(1) FROM sAttendance.tStudentRegister SR1 
				WHERE SR1.AcademicId=SA.AcademicId 
				AND ((SR1.AttendanceTypeId=@rAttendanceTypeId) OR (@rAttendanceTypeId=0))
				AND ((SR1.SubjectId=@rSubjectId) OR (@rSubjectId=0))
				AND ((SR1.AttendanceDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
				AND ((SR1.AttendanceDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
				AND SR1.IsPresent=0),
		   SA.DivisionOrder,
		   SC.ParentContactNo
	FROM 
			sStudent.vStudentAcademic SA
		INNER JOIN sAttendance.tStudentRegister SR ON SR.AcademicId=SA.AcademicId
		INNER JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
	WHERE 
			(SA.OrgId=@rOrgId)
		AND	(SA.BatchId = @rBatchId) 
		AND ((SA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((SR.AttendanceTypeId=@rAttendanceTypeId) OR (@rAttendanceTypeId=0))
		AND ((SR.SubjectId=@rSubjectId) OR (@rSubjectId=0))
		AND ((SR.AttendanceDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((SR.AttendanceDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
		AND (SR.IsPresent = 0)
		AND (SA.StatusId != 3)
		AND ((SA.Code=@rStudentCode) OR (@rStudentCode=''))
		AND ((SA.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		GROUP BY SA.AcademicId,SA.ClassId,SA.DivisionId,SA.ClassName,SA.DivisionName,SA.Code,SA.RollNo,SA.Name,SA.DivisionOrder,SC.ParentContactNo
END
GO
PRINT N'Creating Procedure [sAudit].[pStockOperationLogAdd]...';


GO
CREATE PROCEDURE [sAudit].[pStockOperationLogAdd]
	
	@rScreenId int,
	@rSourceId int,
	@rSourceTypeId smallint,
	@rOperationId tinyint,
	@rOperationRemark nvarchar(max),
	@rCreatedUserId bigint,
	@rCreatedDateTime datetime
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	
	INSERT INTO sAudit.tStockOperationLog
		(
			ScreenId,
			OperationId,
			SourceTypeId,			
			SourceId,
			OperationRemark,
			CreatedUserId,
			CreatedDateTime
		)
	VALUES
		(
			@rScreenId,
			@rOperationId,
			@rSourceTypeId,
			@rSourceId,
			@rOperationRemark,
			@rCreatedUserId,
			@rCreatedDateTime
		)
		
END
GO
PRINT N'Creating Procedure [sAudit].[pStockOperationLogGet]...';


GO
CREATE PROCEDURE [sAudit].[pStockOperationLogGet]
	
	@rScreenId int,
	@rItemCode nvarchar(max),
	@rFromDate date,
	@rToDate date
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	SET @rItemCode = sCommon.fSearchStringGet(@rItemCode)
	Select 
		OL.OperationRemark,
		U.DisplayTitle AS UserName,
		OL.CreatedDateTime,
		SIM.Code,
		SLM.Title
	From
		sAudit.tStockOperationLog OL
		--not used in this issue 26757 but added because in future want logs in other stock screens.
		--INNER JOIN sAudit.tOperationMaster OM ON OM.Id = OL.OperationId
		INNER JOIN sSecurity.tUser U ON U.Id = OL.CreatedUserId
		INNER JOIN sIp.tStockItemMaster SIM ON SIM.Id=OL.SourceId
		INNER JOIN sIp.tStoreLocationMaster SLM ON SLM.Id=SIM.StoreLocationId
	Where
		((OL.ScreenId = @rScreenId) OR (@rScreenId = 0)) 
		AND ((SIM.Code like @rItemCode) OR (ISNULL(@rItemCode,'')=''))
		AND (CONVERT(Date,OL.CreatedDateTime) >= @rFromDate)
		AND (CONVERT(Date,OL.CreatedDateTime) <= @rToDate)
	ORDER BY
		OL.CreatedDateTime DESC
END
GO
PRINT N'Creating Procedure [sFee].[pRptFeeCertificateWithAllFeeDetailsGet]...';


GO

CREATE PROCEDURE [sFee].[pRptFeeCertificateWithAllFeeDetailsGet]  
  
	@rAdmissionCategoryId int,  
	@rClassId int,  
	@rDivisionId int,   
	@rBatchId int,    
	@rBuilder1 nvarchar(max),  
	@rBuilder2 nvarchar(max),   
	@rOrgId int,  
	@rOrgGroupBatchId int,  
	@rStatusId int,  
	@rCommonBatch nvarchar(100),  
	@rDepartmentId int,  
	@rFromRollNo int,  
    @rToRollNo int,  
    @rCode nvarchar(20),  
    @rIsDetail bit=0,  
	@rIsFilter bit,  
	@rIsHeader bit,  
	@rFeeHeadId nvarchar(max) = '',  
	@rIsDisplayOrgGroup bit=0,  
	@rFeeId nvarchar(max) = '',  
	@rFilterId int=0,
	@rOrgIds nvarchar(max) = '',
	@rClassStatusId int=0	  
  
WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON  
BEGIN       
BEGIN
	CREATE TABLE #FeeSetup (
		FeeId INT,
		Id INT,
		Amount DECIMAL(12, 2),
		ClassId INT,
		AdmissionCategoryId INT,
		AcademicId BIGINT,
		BatchId INT
	);
	
	INSERT INTO #FeeSetup (FeeId, Id, Amount, ClassId, AdmissionCategoryId, AcademicId, BatchId)
		SELECT 
			FS1.FeeId,
			FS1.Id,
			ISNULL(FS1.Amount, 0) AS Amount,
			FS1.ClassId,
			FS1.AdmissionCategoryId,
			0 AS AcademicId,
			FS1.BatchId
		FROM sFee.tFeeSetup FS1
		WHERE FS1.BatchId = @rBatchId AND IsOptional = 0 AND (FS1.AdmissionCategoryId=@rAdmissionCategoryId OR @rAdmissionCategoryId=0)
		UNION ALL
		SELECT 
			FS1.FeeId,
			FS1.Id,
			ISNULL(FS1.Amount, 0) AS Amount,
			FS1.ClassId,
			FS1.AdmissionCategoryId,
			FO.SourceId AS AcademicId,
			FS1.BatchId
		FROM sFee.tFeeSetup FS1
		INNER JOIN sFee.tFeeOptional FO ON FO.FeeSetupId = FS1.Id AND FO.SourceTypeId = 2001
		WHERE FS1.BatchId = @rBatchId AND IsOptional = 1 AND (FS1.AdmissionCategoryId=@rAdmissionCategoryId OR @rAdmissionCategoryId=0);

	CREATE TABLE #ReceiptAmount (
    PaidAmount DECIMAL(12, 2),
    FeeSetupId INT,
    SourceId BIGINT
	);

	INSERT INTO #ReceiptAmount (PaidAmount, FeeSetupId, SourceId)
		SELECT ISNULL(SUM(RD.Amount), 0) AS PaidAmount, RD.FeeSetupId, R.SourceId
		FROM sFee.tReceipt R
		INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId = R.Id 		
		WHERE R.SourceTypeId = 2001 AND R.IsObsolete = 0 AND R.BatchId = @rBatchId
		GROUP BY R.SourceId, FeeSetupId;

END	  
BEGIN
	SELECT   
		@rBuilder1 AS Builder1,  
		@rBuilder2 AS Builder2,   
		@rIsDetail AS IsDetail,  
		@rIsFilter As IsFilter,  
		@rIsHeader As IsHeader,  
		@rIsDisplayOrgGroup AS IsDisplayOrgGroup,   
		SA.ClassId,  
		SA.ClassName,
		SA.ClassOrderIndex,  
		SA.DivisionId,  
		SA.DivisionName,
		SA.DivisionOrder AS DivisionOrderIndex,
		SA.ClassName+' - '+SA.DivisionName AS ClassDivision,  
		SA.RollNo,  
		SA.AcademicId,  
		SA.FirstName,  
		SA.MiddleName,  
		SA.LastName,  
		SA.Code,  
		SA.Name,
		SA.OrgId, 
		ISNULL(SA.SUID,'') AS SUID,
		ISNULL(SA.BatchName,'') BatchName,
		ISNULL(SA.GenderId,0) AS GenderId,
		DM.Id AS DepartmentId,  
		DM.Title AS DepartmentName,  
		ISNULL(DM.OrderIndex,0) as DepartmentOrderIndex,   
		ISNULL(FM.Id,0) AS FeeId,  
		ISNULL(FM.Title,'') AS Fee,
		FM.Code AS FeeCode,  
		FM.OrderIndex AS FeeOrderIndex,
		ISNULL(FM.FeeHeadId,0) AS FeeHeadId,  
		ISNULL(FHM.Title,'') AS FeeHead,  
		ISNULL(FHM.Code,'') AS FeeHeadCode,  
		ISNULL(FHM.OrderIndex,0) AS FeeHeadOrderIndex,  	   
		ISNULL(R.PaidAmount,0.00) AS PaidAmount,
		ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00)AS PendingAmount,
		ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00) AS TotalAmount

	FROM 
		sStudent.vStudentAcademic SA
		INNER JOIN #FeeSetup FS ON SA.ClassId = FS.ClassId AND SA.BatchId = FS.BatchId AND (SA.AcademicId = FS.AcademicId OR FS.AcademicId = 0)
		LEFT JOIN #ReceiptAmount R ON SA.AcademicId = R.SourceId AND R.FeeSetupId = FS.Id	 
		INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId  
		INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId   
		INNER JOIN sSetup.tDepartmentMaster DM ON SA.DepartmentId=DM.Id  
		INNER JOIN sSetup.tOrganizationMaster O ON O.Id=sa.OrgId   
		LEFT JOIN sHostel.tAssignRoom AR ON  AR.SourceId=SA.StudentId AND AR.SourceTypeId=2003 AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))  AND ((AR.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))   
		LEFT JOIN  
		(SELECT AT.SourceId FROM sTransport.tAssignRoute AT   
			INNER JOIN sTransport.tBusRouteDestinationMaster BM ON BM.Id=AT.PickupDestinationId  
			INNER JOIN sTransport.tBusRouteMaster B ON B.Id=BM.BusRouteId AND  AT.SourceTypeId=2003 AND ((B.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))) RT  
			ON RT.SourceId = SA.StudentId AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))  
		LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
	WHERE    
		((SA.AdmissionCategoryId = @rAdmissionCategoryId) OR (@rAdmissionCategoryId = 0))  
		AND ((SA.ClassId = @rClassId) OR (@rClassId = 0))  
		AND ((SA.DivisionId = @rDivisionId) OR (@rDivisionId = 0))    
		AND (SA.OrgGroupBatchId = @rOrgGroupBatchId)  
		AND ((SA.OrgId=@rOrgId) OR (@rOrgId=0))        
		AND (SA.StatusId = @rStatusId OR @rStatusId=0)  
		AND ((SA.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))  
		AND ((SA.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))  
		AND ((SA.RollNo <= @rToRollNo) OR (@rToRollNo=0))  
		AND ((SA.Code=@rCode) OR (@rCode=''))  
		AND ((SA.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))    
		AND (ISNULL(FM.FeeHeadId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeHeadId,','))OR @rFeeHeadId='')  
		AND (ISNULL(FM.Id,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeId,','))OR @rFeeId='')   
		AND (  
		(@rFilterId = 0) -- All  
			OR (@rFilterId = 2 AND isnull(AR.SourceId,0)!=0) -- Hostel  
			OR (@rFilterId = 3 AND isnull(RT.SourceId,0)!=0) -- Transport  
			OR ((@rFilterId =1) AND ((isnull(AR.SourceId,0)=0) AND (isnull(RT.SourceId,0)=0)))  
		)  
		AND (ISNULL(SA.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
  		AND ((SA.ClassStatusId=@rClassStatusId) OR (@rClassStatusId=0))  
	ORDER By SA.ClassId,SA.AcademicId
END

DROP TABLE #ReceiptAmount
DROP TABLE #FeeSetup

--BEGIN -- For DataSet Drag & Drop
--	SELECT
--	'' AS Builder1,
--	'' AS Builder2,
--	CONVERT(bit,0) AS IsDetail,
--	CONVERT(bit,0) AS IsFilter,
--	CONVERT(bit,0) AS IsHeader,
--	CONVERT(bit,0) AS IsDisplayOrgGroup,
--	0 AS ClassId,
--	'' AS ClassName,
--	0 AS ClassOrderIndex,
--	0 AS DivisionId,
--	'' AS DivisionName,
--  0 AS DivisionOrderIndex,
--	'' AS ClassDivision,
--	0 AS RollNo,
--	CONVERT(BigInt,0) AS AcademicId,
--	'' AS FirstName,
--	'' AS MiddleName,
--	'' AS LastName,
--	'' AS Code,
--	'' AS Name,
--	0 AS OrgId,
--	'' AS SUID,
--	'' AS BatchName,
--	0 AS GenderId,
--	0 AS DepartmentId,
--	'' AS DepartmentName,
--	0 AS DepartmentOrderIndex,
--	0 AS FeeId,
--	'' AS Fee,
--	'' AS FeeCode,
--	0 AS FeeOrderIndex,
--	0 AS FeeHeadId,
--	'' AS FeeHead,
--	'' AS FeeHeadCode,
--	0 AS FeeHeadOrderIndex,
--	CONVERT(DECIMAL,0) AS PaidAmount,
--	CONVERT(DECIMAL,0) AS PendingAmount,
--	CONVERT(DECIMAL,0) AS TotalAmount
--END

END
GO
PRINT N'Creating Procedure [sHrm].[pEmployeeReplacementDeleteAndTransfer]...';


GO
CREATE PROCEDURE [sHrm].[pEmployeeReplacementDeleteAndTransfer]
	 
        @rExistingEmployeeId BIGINT,
		@rNewEmployeeId BIGINT,
		@rId NVARCHAR(500)
	

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	IF OBJECT_ID('tempdb..#tmpSplitTable') IS NOT NULL DROP TABLE #tmpSplitTable

	CREATE Table #tmpSplitTable
	(
		Id NVARCHAR(150)
	)

	INSERT INTO #tmpSplitTable
	SELECT CAST(item AS NVARCHAR(20)) AS Id FROM  sCommon.fnsplit(@rId,',')

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Assign Teacher')
	BEGIN
		INSERT INTO sTimetable.tAssignTeacher
		Select TimeTableSchemaId, @rNewEmployeeId from sTimetable.tAssignTeacher WHERE EmployeeId = @rExistingEmployeeId

		delete from sTimetable.tAssignTeacher where EmployeeId = @rExistingEmployeeId
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Teacher Subject Division')
	BEGIN
		INSERT INTO sAcademic.tTeacherSubject
		Select @rNewEmployeeId,SubjectId from sAcademic.tTeacherSubject where EmployeeId  = @rExistingEmployeeId

		delete from sAcademic.tTeacherSubject where EmployeeId = @rExistingEmployeeId

		Insert into sAcademic.tTeacherSubjectDivision 
		select @rNewEmployeeId, SubjectId, DivisionId,SubjectCordinatorEmployeeId from sAcademic.tTeacherSubjectDivision  
		where EmployeeId = @rExistingEmployeeId

		delete from sAcademic.tTeacherSubjectDivision where EmployeeId = @rExistingEmployeeId
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Time Table')
	BEGIN
		Update sTimetable.tTimeTable
		Set EmployeeId = @rNewEmployeeId from sTimetable.tTimeTable where EmployeeId  = @rExistingEmployeeId
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Lesson Plan')
	BEGIN
		Update sAcademic.tLessonPlan
		Set EmployeeId = @rNewEmployeeId from sAcademic.tLessonPlan where EmployeeId  = @rExistingEmployeeId
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Teacher work load')
	BEGIN
		INSERT INTO sTimetable.tTeacherWorkLoad
		Select @rNewEmployeeId,BatchId,DivisionId,SubjectId,NoOfLecture,CreatedUserId,CreatedDateTime,TimeTableSchemaId
		,LectureLength from sTimetable.tTeacherWorkLoad where EmployeeId  = @rExistingEmployeeId

		delete from sTimetable.tTeacherWorkLoad where EmployeeId = @rExistingEmployeeId
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='User Access')
	BEGIN
		declare @lExistingUserId bigint= (select Id from sSecurity.tUser where SourceId = @rExistingEmployeeId AND SourceTypeId = 95)
		declare @lNewUserId bigint= (select Id from sSecurity.tUser where SourceId = @rNewEmployeeId AND SourceTypeId = 95)
		Insert into sSecurity.tUserOrganization 
		select OrgId, @lNewUserId from sSecurity.tUserOrganization WHERE UserId=@lExistingUserId

		delete from sSecurity.tUserOrganization  WHERE UserId=@lExistingUserId

		INSERT INTO sSecurity.tUserAccess
		SELECT SourceTypeId, SourceId, @lNewUserId FROM sSecurity.tUserAccess WHERE UserId=@lExistingUserId

		delete from sSecurity.tUserAccess  WHERE UserId=@lExistingUserId
	END

	Drop Table #tmpSplitTable
END
GO
PRINT N'Creating Procedure [sHrm].[pEmployeeReplacementGet]...';


GO
CREATE PROCEDURE [sHrm].[pEmployeeReplacementGet]
	 
        @rEmployeeId bigint
	

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	Declare @lTeacherSubjectId bigint = 0 , @lTeacherDivisionId bigint = 0
	IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL DROP TABLE #tmpTable
	CREATE Table #tmpTable 
	(
		Id bigint IDENTITY,
		Title nvarchar(50)
	)

	IF EXISTS(select top 1 1 from sTimetable.tAssignTeacher where EmployeeId = @rEmployeeId)
	BEGIN
		Insert INTO #tmpTable Values
		(
			'Assign Teacher'
		)
	END


	set @lTeacherSubjectId = (select top 1 1 from sAcademic.tTeacherSubject where EmployeeId = @rEmployeeId)
	SET @lTeacherDivisionId = (select top 1 1 from sAcademic.tTeacherSubjectDivision where EmployeeId = @rEmployeeId)
	IF ((@lTeacherSubjectId = 1) OR (@lTeacherDivisionId = 1))
	BEGIN
		Insert INTO #tmpTable Values
		(
			'Teacher Subject Division'
		)
	END

	IF EXISTS (select top 1 1 from sTimetable.tTimeTable where EmployeeId = @rEmployeeId)
	BEGIN
		Insert INTO #tmpTable Values
		(
			'Time Table'
		)
	END

	IF EXISTS (select top 1 1 from sTimetable.tTeacherWorkLoad where EmployeeId = @rEmployeeId)
	BEGIN
		Insert INTO #tmpTable Values
		(
			'Teacher Work Load'
		)
	END

	IF EXISTS (select top 1 1 from sAcademic.tLessonPlan where EmployeeId = @rEmployeeId)
	BEGIN
		Insert INTO #tmpTable Values
		(
			'Lesson Plan'
		)
	END

	declare @lUserId bigint= (select top 1 1 from sSecurity.tUser where SourceId = @rEmployeeId AND SourceTypeId = 95)
	declare @lUserOrganizationId int ,@lUserAccessId int
	SET @lUserOrganizationId = (SELECT Top 1 1 FROM sSecurity.tUserOrganization WHERE UserId=@lUserId)
	SET @lUserAccessId= (SELECT top 1 1 FROM sSecurity.tUserAccess WHERE UserId=@lUserId)
	IF ((@lUserOrganizationId = 1) OR (@lUserAccessId = 1))
	BEGIN
		Insert INTO #tmpTable Values
		(
			'User Access'
		)
	END

	select * from #tmpTable

	Drop Table #tmpTable
END
GO
PRINT N'Creating Procedure [sHrm].[pEmployeeReplacementTransfer]...';


GO
CREATE PROCEDURE [sHrm].[pEmployeeReplacementTransfer]
	 
        @rExistingEmployeeId BIGINT,
		@rNewEmployeeId BIGINT,
		@rId NVARCHAR(max),
		@rOrgId int
	

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	IF OBJECT_ID('tempdb..#tmpSplitTable') IS NOT NULL DROP TABLE #tmpSplitTable

	CREATE Table #tmpSplitTable
	(
		Id NVARCHAR(max)
	)
	INSERT INTO #tmpSplitTable
	SELECT CAST(item AS NVARCHAR(max)) AS Id FROM  sCommon.fnsplit(@rId,',')

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Assign Teacher')
	BEGIN
		INSERT INTO sTimetable.tAssignTeacher
		Select TimeTableSchemaId, @rNewEmployeeId from sTimetable.tAssignTeacher WHERE EmployeeId = @rExistingEmployeeId
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Teacher Subject Division')
	BEGIN
		INSERT INTO sAcademic.tTeacherSubject
		Select @rNewEmployeeId,SubjectId from sAcademic.tTeacherSubject where EmployeeId  = @rExistingEmployeeId

		Insert into sAcademic.tTeacherSubjectDivision 
		select @rNewEmployeeId, SubjectId, DivisionId,SubjectCordinatorEmployeeId from sAcademic.tTeacherSubjectDivision  where EmployeeId = @rExistingEmployeeId
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Teacher work load')
	BEGIN
		INSERT INTO sTimetable.tTeacherWorkLoad
		Select @rNewEmployeeId,BatchId,DivisionId,SubjectId,NoOfLecture,CreatedUserId,CreatedDateTime,TimeTableSchemaId
		,LectureLength from sTimetable.tTeacherWorkLoad where EmployeeId  = @rExistingEmployeeId

	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='User Access')
	BEGIN
		declare @lExistingUserId bigint= (select Id from sSecurity.tUser where SourceId = @rExistingEmployeeId AND SourceTypeId = 95)
		declare @lNewUserId bigint= (select Id from sSecurity.tUser where SourceId = @rNewEmployeeId AND SourceTypeId = 95)
		Insert into sSecurity.tUserOrganization 
		select OrgId, @lNewUserId from sSecurity.tUserOrganization WHERE UserId=@lExistingUserId

		INSERT INTO sSecurity.tUserAccess
		SELECT SourceTypeId, SourceId, @lNewUserId FROM sSecurity.tUserAccess WHERE UserId=@lExistingUserId
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Lesson Plan')
	BEGIN
		declare @lCount int,@lNextNumber BIGINT ,@lId int
		Create table #tmpLessonPlan
		(
			Id bigint IDENTITY,
			TopicId int,
			DivisionId int, 
			NoOfPeriodRequired smallint,
			Objective nvarchar(1000),
			IndicativeHomework nvarchar(200),
			Summary nvarchar(200),
			StartDate date,
			EndDate date,
			IsCompleted bit,
			EmployeeId bigint,
			BatchId int,
			CreatedUserId bigint,
			OrgId smallint,
			LearningExperience nvarchar(1000),
			ToolsAndTechniques nvarchar(500),
			EssentialMaterial nvarchar(500),
			AssessmentQuestion	nvarchar(1000),	
			TeacherExample	nvarchar(1000),
			GuidedPractice	nvarchar(1000),
			IndependentPractice	nvarchar(1000),
			SubTopic nvarchar(1000),
			Session nvarchar(100) 
		)

		insert into #tmpLessonPlan
		select * from sAcademic.tLessonPlan where EmployeeId  = @rExistingEmployeeId

		Set @lCount = (Select Count(Id) from #tmpLessonPlan)
		WHILE @lCount > 0
		BEGIN
			EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAcademic.tLessonPlan', 0, 1, 1,@outNextNumber = @lNextNumber output

			Set @lId = (Select Id from #tmpLessonPlan where Id = @lCount)

			INSERT INTO sAcademic.tLessonPlan
			select @lNextNumber, TopicId,DivisionId,NoOfPeriodRequired,Objective,IndicativeHomework,Summary,StartDate,EndDate,IsCompleted
			,@rNewEmployeeId,BatchId,CreatedUserId,CreatedDateTime,LearningExperience,ToolsAndTechniques,EssentialMaterial,AssessmentQuestion,
			TeacherExample,GuidedPractice,IndependentPractice,SubTopic ,[Session] ,TrainingMethod ,PeriodCount,Remark
			from #tmpLessonPlan  where Id  = @lId
			
			set @lCount = @lCount-1
		END

		drop table #tmpLessonPlan
	END

	IF EXISTS (SELECT 1 from #tmpSplitTable WHERE Id ='Time Table')
	BEGIN
		declare @lCount1 int,@lNextNumber1 BIGINT ,@lId1 int

		Create Table #tmpTimeTable 
		(
				[Id] int identity
			   ,[TimeTableSchemaId] int
			   ,[EmployeeId] bigint
			   ,[DivisionId] int
			   ,[SubjectId] int
			   ,[DayId] smallint
			   ,[LectureNumber] smallint
			   ,[LockNo] bigint
			   ,[LockColour] varchar(50)
		)

		insert into #tmpTimeTable
		select [TimeTableSchemaId],@rNewEmployeeId,[DivisionId],[SubjectId],[DayId],[LectureNumber],[LockNo],[LockColour]
		from sTimetable.tTimeTable  where EmployeeId  = @rExistingEmployeeId

		Set @lCount1 = (Select Count(Id) from #tmpTimeTable)

		WHILE @lCount1 > 0
		BEGIN
			EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sTimetable.tTimeTable', 0, 1, 1,@outNextNumber = @lNextNumber1 output

			Set @lId1 = (Select Id from #tmpTimeTable where Id = @lCount1)

			INSERT INTO sTimetable.tTimeTable
			select @lNextNumber1,[TimeTableSchemaId],EmployeeId,[DivisionId],[SubjectId],[DayId],[LectureNumber],[LockNo],[LockColour]
			from #tmpTimeTable  where Id = @lId1
			set @lCount1 = @lCount1 -  1
		END

		drop table #tmpTimeTable
	END
	Drop Table #tmpSplitTable
END
GO
PRINT N'Creating Procedure [sStudent].[pAlphaNumericOrder]...';


GO
CREATE PROCEDURE sStudent.pAlphaNumericOrder
	 
	 @rOrgId smallint = 0

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	IF OBJECT_ID('tempdb..#tmpStudent') IS NOT NULL DROP TABLE #tmpStudent

	CREATE TABLE #tmpStudent
	(
		Id bigint IDENTITY,
		Code nvarchar(20),
		OrgId smallint,
		StudentId bigint
	)
	
	INSERT INTO #tmpStudent
	SELECT Code,OrgId, Id  As StudentId from sStudent.tStudentMaster
	where (OrgId = @rOrgId OR @rOrgId = 0)
	ORDER BY CASE IsNumeric (Code) WHEN 1 THEN Replicate (0, 100 - Len (Code)) + Code  ELSE Code  END

	Update SM SET SM.Number = T.Id
	from #tmpStudent T INNER JOIN sStudent.tStudentMaster SM ON SM.Id = T.StudentId AND SM.Code = T.Code AND SM.OrgId = T.OrgId
	where (SM.OrgId = @rOrgId OR @rOrgId = 0)

	Drop Table #tmpStudent
END
GO
PRINT N'Creating Procedure [sStudent].[pRptStudentBMIListGet]...';


GO
CREATE PROCEDURE [sStudent].[pRptStudentBMIListGet]
	@rBuilder1 nvarchar(max)='',
	@rBuilder2 nvarchar(max)='',
	@rOrgId smallint=0,
	@rBatchId int=0,
	@rDepartmentId int=0,
	@rStrCode nvarchar(max)='',
	@rClassId int=0,
	@rDivisionId int=0,
	@rStudentId bigint=0,
	@rStatusId int=0,
	@rClassStatusId int=0,
	@rFromRollNo int=0,
	@rToRollNo int=0,
	@rHouseGroupId int=0


	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON


BEGIN

	SELECT
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			SA.AcademicId,
			SA.StudentId,
			SA.Code,
			SA.RollNo AS RollNo,
			SA.Name,
			SA.ClassId,
			SA.ClassName,
			SA.DivisionId,
			SA.DivisionName,
			SA.StatusName,
			SA.DivisionOrder,
			SA.Number AS StudentNumber,
			ISNULL(HM.Title,'') AS HouseName,
			SA.ClassStatusName,
			ISNULL(AD.HeightAnnual,0) AS Height,
			ISNULL(AD.WeightAnnual,0) AS Weight,
			CASE
				WHEN ISNULL(AD.HeightAnnual,0)=0 THEN 0
				ELSE CONVERT(decimal(10,2),(ISNULL(AD.WeightAnnual,0)/POWER((ISNULL(AD.HeightAnnual,0)/100),2))) END AS BMI

	FROM
		 	sStudent.vStudentAcademic SA
			LEFT JOIN sStudent.tStudentOther SO ON SA.StudentId = SO.StudentId
		 	LEFT JOIN sActivity.tHouseMaster HM ON SO.HouseId = HM.Id
		 	INNER JOIN sSetup.tBatchClass BC ON BC.ClassID =  SA.ClassId and BC.BatchId= @rBatchId
			LEFT JOIN sStudent.tAcademicDetail AD ON AD.AcademicId=SA.AcademicId

	WHERE
			SA.OrgId=@rOrgId
		AND	SA.BatchId = @rBatchId
		AND ((SA.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rStrCode,',')) OR @rStrCode='')
		AND ((SA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((SA.StudentId=@rStudentId) OR (@rStudentId=0))
		AND ((SA.StatusId=@rStatusId)OR(@rStatusId=0))
		AND ((SA.ClassStatusId=@rClassStatusId)OR(@rClassStatusId=0))
		AND ((SA.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))
		AND ((SA.RollNo <= @rToRollNo) OR (@rToRollNo=0))
		AND ((SO.HouseId=@rHouseGroupId)OR(@rHouseGroupId=0))

END
GO

INSERT INTO [sSystem].[tVersionUpdate] ([VersionNumber],[VersionId],[Status],[UpdateScript],[StartDateTime],[EndDateTime],[OrgGroupId])
VALUES('23.08.28',230828,'END','Script',sCommon.fGetDate(),sCommon.fGetDate(),0)
GO

