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

CREATE FUNCTION [sFee].[fNextInstallmentDetailsGet](@rReceiptBookId int, @rReceiptId bigint)
RETURNS  @List TABLE (
		PendingAmount decimal(12,2),
		CollectionGroupId int,
		StartDate date,
		EndDate date,
		CollectionGroupName nvarchar(100),
		OrderIndex smallint,
		ReceiptId bigint,
		SourceId bigint,
		SourceTypeId smallint
)  
WITH EXECUTE AS CALLER
AS 
BEGIN

	DECLARE @lClassId int
	DECLARE @lAdmissionCategoryId int,@lOrgId int,@lOrgGorupId int
	DECLARE @lSourceId bigint, @lSourceTypeId int, @lBatchId int
	--DECLARE @lConfigValue nvarchar(50)

	SELECT @lSourceId = SourceId, @lSourceTypeId = SourceTypeId FROM sFee.tReceipt WHERE Id = @rReceiptId
	SELECT @lClassId=ClassId,@lAdmissionCategoryId=AdmissionCategoryId,@lOrgId = OrgId, @lBatchId=BatchId FROM sStudent.vStudentAcademic WHERE AcademicId=@lSourceId	
	SELECT @lOrgGorupId = OrgGroupId FROM sSetup.tOrganizationMaster WHERE Id = @lOrgId
	--SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 487 AND SourceTypeId = 2010 AND SourceId = @lOrgGorupId
	
	INSERT INTO @List 
	SELECT TOP 1 *, @lSourceId AS SourceId, @lSourceTypeId AS SourceTypeId FROM
	(
		SELECT 
			SUM(CG.PayableAmount) AS PendingAmount,
			CollectionGroupId,
			StartDate,
			EndDate,
			CollectionGroupName,
			OrderIndex,
			ReceiptId
		FROM
		(SELECT	
			ISNULL(FS.Amount,0) +  ISNULL(FA.VariableAmount,0) -  ISNULL(FA.DiscountAmount,0) -  ISNULL(FA.NotApplicableAmount,0) + ISNULL(FA.AdditionalAmount,0.00)-ISNULL(R.PaidAmount,0)AS PayableAmount,
			FS.CollectionGroupId,
			CGM.StartDate,
			CGM.EndDate,
			CGM.Title AS CollectionGroupName,
			CGM.OrderIndex,
			R.ReceiptId
		 FROM 
			sFee.tFeeMaster FM
			INNER JOIN 
			(
				SELECT 
					FS1.FeeId,
					FS1.Id,
					ISNULL(FS1.Amount,0)AS Amount,
					FS1.BatchId,
					ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
					ISNULL(FS1.CollectionGroupId,0) AS CollectionGroupId
				FROM sFee.tFeeSetup FS1
				WHERE FS1.ClassId=@lClassId AND FS1.BatchId = @lBatchId
					AND FS1.AdmissionCategoryId=@lAdmissionCategoryId AND IsOptional=0
				
				UNION ALL
				
				SELECT 
					FS1.FeeId,
					FS1.Id,
					ISNULL(FS1.Amount,0)AS Amount,
					FS1.BatchId,
					ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
					ISNULL(FS1.CollectionGroupId,0) AS CollectionGroupId
				FROM 
					sFee.tFeeSetup FS1
					INNER JOIN sFee.tFeeOptional FO ON FO.FeeSetupId=FS1.Id AND (FO.SourceId=@lSourceId) AND ((FO.SourceTypeId=@lSourceTypeId) OR (@lSourceTypeId=0))
				WHERE
					FS1.ClassId=@lClassId 
					AND FS1.BatchId =@lBatchId 
					AND FS1.AdmissionCategoryId=@lAdmissionCategoryId 
					AND IsOptional=1
			) FS ON FS.FeeId=FM.Id
			INNER JOIN sFee.tCollectionGroupMaster CGM ON CGM.Id = FS.CollectionGroupId
			INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=FS.ReceiptBookId
			LEFT JOIN
			(
				SELECT 
					ISNULL(SUM(RD.Amount),0) AS PaidAmount,
					RD.FeeSetupId,
					R.Id AS ReceiptId
				FROM
					sFee.tReceipt R 
					INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId = R.Id	
					INNER JOIN sFee.tFeeSetup FSR ON FSR.Id = RD.FeeSetupId 	
				WHERE						
					(R.ReceiptBookId = @rReceiptBookId	OR @rReceiptBookId = 0)	
					AND R.SourceId= @lSourceId
					AND ((R.SourceTypeId=@lSourceTypeId) OR (@lSourceTypeId=0))
					AND R.IsObsolete = 0 	
					AND R.Id = @rReceiptId		 
				GROUP BY FeeSetupId,R.Id
			) R ON R.FeeSetupId = FS.Id 
			LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=FS.Id AND (FA.SourceId=@lSourceId) AND ((FA.SourceTypeId=@lSourceTypeId) OR (@lSourceTypeId=0))			
			WHERE 
				((FS.ReceiptBookId=@rReceiptBookId)OR(@rReceiptBookId=0))
				AND (((ISNULL(FS.Amount,0) +  ISNULL(FA.VariableAmount,0) -  ISNULL(FA.NotApplicableAmount,0) -  ISNULL(FA.DiscountAmount,0) + ISNULL(FA.AdditionalAmount,0.00)-ISNULL(R.PaidAmount,0))>0))
				AND(FS.BatchId=@lBatchId)
				--AND (@lConfigValue = 'True' AND (CAST(sCommon.fGetDate() AS DATE) <= CAST(CGM.EndDate AS DATE)))
				AND CGM.IsObsolete = 0
		) AS CG
		GROUP BY CollectionGroupId,StartDate,EndDate,CollectionGroupName,OrderIndex,ReceiptId
	) AS PendingFee
	ORDER BY PendingFee.StartDate,PendingFee.OrderIndex
		
	RETURN  
END


GO

/*
Deployment script for NetVidyalayaVersionBlank
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO

GO
PRINT N'Altering [sAcademic].[pClassworkGet]...';


GO
ALTER PROCEDURE [sAcademic].[pClassworkGet]

	 @rId bigint=0,	 
	 @rSubjectId int=0,
	 @rClassId int=0,
	 @rEmployeeId bigint=0,
	 @rDivisionid int=0,
	 @rFromDate datetime='',
     @rToDate datetime='',
     @rOrgId int=0,
     --@rBatchId int
     @rOrgGroupBatchId int=0,
    @rUserId bigint =0
	 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 
	IF(@rEmployeeId = -1)
	BEGIN
		SET @rEmployeeId = 0
	END

	if(@rId = 0)
		BEGIN
			SELECT 
			AC.Id,
			AC.EmployeeId,
			AC.ClassId,
			AC.DivisionId,
			AC.SubjectId,
			AC.ClassWorkDate,
			AC.Classwork,
			AC.Homework,
			ISNULL(ClassworkStatusId,1) AS ClassworkStatusId,
			ISNULL(HomeWorkStatusId,1) AS HomeWorkStatusId,
			--AC.HomeWorkEndDate,
			ISNULL(AC.HomeWorkEndDate,'') AS HomeWorkEndDate,
			AC.CreatedDateTime,
			AC.BatchId,
			BM.Title AS Batch,
		    ISNULL(vTSD.SubjectName,'') as SubjectName,
		    ISNULL(EM.FirstName + ' ' + EM.LastName,'') AS TeacherName,
		    ISNULL(vTSD.ClassTitle,'') AS ClassName,
		    ISNULL(vTSD.DivisionTitle,'') AS DivisionName,
		    BM.OrgId,
		    OM.DisplayTitle AS Organization,
		    REPLACE(CONVERT(VARCHAR(20), AC.ClassWorkDate, 103),'/','') +'_'+ CONVERT(nvarchar(10),AC.DivisionId) +'_'+CONVERT(nvarchar(10),AC.SubjectId) AS UniqueId,
			ISNULL(AC.IsSubmitHomework,0) AS IsSubmitHomework,
			AC.Id AS ClassworkId
	FROM 
			sAcademic.tClasswork AC
			INNER JOIN sAcademic.vTeacherSubjectDivision vTSD ON vTSD.SubjectId = AC.SubjectId AND vTSD.DivisionId = AC.DivisionId AND vTSD.ClassId=AC.ClassId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = vTSD.OrgId
			INNER JOIN sSetup.tOrgGroupMaster OGM ON OGM.Id = OM.OrgGroupId
			INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.OrgGroupId = OGM.Id
			INNER JOIN sHrm.tEmployeeMaster EM ON  AC.EmployeeId = EM.Id AND vTSD.EmployeeId = AC.EmployeeId
			INNER JOIN sHrm.tEmployeeJobdetail EJD ON EJD.EmployeeId = EM.Id
			INNER JOIN sSetup.tBatchMaster BM On BM.Id= AC.BatchId AND BM.OrgGroupBatchId = OGBM.Id
			INNER JOIN sSecurity.tUserOrganization U ON U.OrgId = OM.Id 
	WHERE 
	        --((AC.BatchId = @rBatchId) OR (@rBatchId = 0))
			((AC.Id = @rId) OR (@rId = 0))
			AND ((AC.EmployeeId= @rEmployeeId) OR (@rEmployeeId = 0))
			AND	((AC.ClassId = @rClassId) OR (@rClassId = 0))
			AND	((vTSD.OrgId = @rOrgId) OR (@rOrgId = 0))
			AND ((AC.SubjectId =@rSubjectId) OR (@rSubjectId=0))
			AND ((AC.DivisionId =@rDivisionId) OR (@rDivisionId=0))
			AND (AC.ClassWorkDate>=@rFromDate OR ISNULL(@rFromDate,'')='')
			AND (AC.ClassWorkDate<=@rToDate OR ISNULL(@rToDate,'')='')
			AND (OGBM.Id = @rOrgGroupBatchId) 
			AND ((U.UserId = @rUserId)OR(@rUserId=0))
			AND ISNULL(EM.IsOtherUser,0) = 0 
	order by 
			AC.HomeWorkEndDate DESC
		END 
	ELSE
		BEGIN
		SELECT 
			AC.Id,
			AC.EmployeeId,
			AC.ClassId,
			AC.DivisionId,
			AC.SubjectId,
			AC.ClassWorkDate,
			AC.Classwork,
			AC.Homework,
			ISNULL(ClassworkStatusId,1) AS ClassworkStatusId,
			ISNULL(HomeWorkStatusId,1) AS HomeWorkStatusId,
			--AC.HomeWorkEndDate,
			ISNULL(AC.HomeWorkEndDate,'') AS HomeWorkEndDate,
			AC.CreatedDateTime,
			AC.BatchId,
			BM.Title AS Batch,
		    ISNULL(vTSD.SubjectName,'') as SubjectName,
		    ISNULL(EM.FirstName + ' ' + EM.LastName,'') AS TeacherName,
		    ISNULL(vTSD.ClassTitle,'') AS ClassName,
		    ISNULL(vTSD.DivisionTitle,'') AS DivisionName,
		    BM.OrgId,
		    OM.DisplayTitle AS Organization,
		    REPLACE(CONVERT(VARCHAR(20), AC.ClassWorkDate, 103),'/','') +'_'+ CONVERT(nvarchar(10),AC.DivisionId) +'_'+CONVERT(nvarchar(10),AC.SubjectId) AS UniqueId,
			ISNULL(AC.IsSubmitHomework,0) AS IsSubmitHomework,
			AC.Id AS ClassworkId
	FROM 
			sAcademic.tClasswork AC
			INNER JOIN sAcademic.vTeacherSubjectDivision vTSD ON vTSD.SubjectId = AC.SubjectId AND vTSD.DivisionId = AC.DivisionId AND vTSD.ClassId=AC.ClassId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = vTSD.OrgId
			INNER JOIN sSetup.tOrgGroupMaster OGM ON OGM.Id = OM.OrgGroupId
			INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.OrgGroupId = OGM.Id
			INNER JOIN sHrm.tEmployeeMaster EM ON  AC.EmployeeId = EM.Id AND vTSD.EmployeeId = AC.EmployeeId
			INNER JOIN sHrm.tEmployeeJobdetail EJD ON EJD.EmployeeId = EM.Id
			INNER JOIN sSetup.tBatchMaster BM On BM.Id= AC.BatchId AND BM.OrgGroupBatchId = OGBM.Id
			INNER JOIN sSecurity.tUserOrganization U ON U.OrgId = OM.Id 
	WHERE 
	        --((AC.BatchId = @rBatchId) OR (@rBatchId = 0))
			((AC.Id = @rId) OR (@rId = 0))
			AND ((AC.EmployeeId= @rEmployeeId) OR (@rEmployeeId = 0))
			AND	((AC.ClassId = @rClassId) OR (@rClassId = 0))
			AND	((vTSD.OrgId = @rOrgId) OR (@rOrgId = 0))
			AND ((AC.SubjectId =@rSubjectId) OR (@rSubjectId=0))
			AND ((AC.DivisionId =@rDivisionId) OR (@rDivisionId=0))
			AND (AC.ClassWorkDate>=@rFromDate OR ISNULL(@rFromDate,'')='')
			AND (AC.ClassWorkDate<=@rToDate OR ISNULL(@rToDate,'')='')
			AND (OGBM.Id = @rOrgGroupBatchId) 
			AND ((U.UserId = @rUserId)OR(@rUserId=0))
			AND ISNULL(EM.IsOtherUser,0) = 0 
			GROUP BY AC.Id,
			AC.EmployeeId,
			AC.ClassId,
			AC.DivisionId,
			AC.SubjectId,
			AC.ClassWorkDate,
			AC.Classwork,
			AC.Homework,
			ClassworkStatusId,
			HomeWorkStatusId,
			--AC.HomeWorkEndDate,
			AC.HomeWorkEndDate,
			AC.CreatedDateTime,
			AC.BatchId,
			BM.Title,
		  vTSD.SubjectName,
		    EM.FirstName,  EM.LastName,
		    vTSD.ClassTitle,
		   vTSD.DivisionTitle,
		    BM.OrgId,
		    OM.DisplayTitle,
		     AC.ClassWorkDate, AC.DivisionId,AC.SubjectId
			 ,AC.IsSubmitHomework
	order by 
			AC.HomeWorkEndDate DESC
		END
	
END
GO
PRINT N'Altering [sAcademic].[pLessonPlanAdd]...';


GO
 
ALTER PROCEDURE [sAcademic].[pLessonPlanAdd]
	 
        @rId bigint=0,
        @rTopicId int,
        @rDivisionId int, 
        @rNoOfPeriodRequired smallint,
        @rObjective nvarchar(1000),
        @rIndicativeHomework nvarchar(200),
        @rSummary nvarchar(200),
        @rStartDate date='',
        @rEndDate date='',
        @rIsCompleted bit=0,
        @rEmployeeId bigint,
        @rBatchId int,
        @rCreatedUserId bigint,
        @rOrgId smallint,
        @rLearningExperience nvarchar(1000)='',
        @rToolsAndTechniques nvarchar(500)='',
        @rEssentialMaterial nvarchar(500)='',
		@rAssessmentQuestion	nvarchar(1000)='',	
		@rTeacherExample	nvarchar(1000)='',
		@rGuidedPractice	nvarchar(1000)='',
		@rIndependentPractice	nvarchar(1000)	='',
		@rSubTopic nvarchar(1000)='' ,
	    @rSession nvarchar(100)='' ,
		@rTrainingMethod  nvarchar(100)='' ,
		@rPeriodCount int =0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	 
	--IF(@rId = 0)	
	--BEGIN
	--	--If Exists(select Id from sCommunication.tNews where Code=@rCode AND OrgGroupBatchId=@rOrgGroupBatchId)
	--	--	BEGIN
	--	--	select -102 as Result
	--	--	RETURN
	--	--END 
	
	--	If Exists(select Id from sAcademic.tTopicMaster where Number=@rNumber AND OrgId=@rOrgId)
	--		BEGIN
	--		select -103 as Result
	--		RETURN
	--	END 
	--END
	--ELSE
	--BEGIN
	--	--If Exists(select Id from sCommunication.tNews where Code=@rCode AND OrgGroupBatchId=@rOrgGroupBatchId AND Id<>@rId)
	--	--Begin
	--	--	select -102 as Result
	--	--	RETURN
	--	--End
	--	If  Exists(select Id from sAcademic.tTopicMaster where Number=@rNumber AND OrgId=@rOrgId AND Id<>@rId)
	--	Begin
	--		select -103 as Result
	--		RETURN
	--	End

	--END
	
	IF(@rId = 0)	
	BEGIN
		IF EXISTS(SELECT Id FROM sAcademic.tLessonPlan WHERE DivisionId=@rDivisionId AND TopicId = @rTopicId AND StartDate = @rStartDate AND EndDate = @rEndDate)
		BEGIN
			SELECT -101 AS Result
			RETURN
		END 
	END
	ELSE
	BEGIN
		IF  EXISTS(SELECT Id FROM sAcademic.tLessonPlan WHERE DivisionId=@rDivisionId AND TopicId = @rTopicId AND StartDate = @rStartDate AND EndDate = @rEndDate AND Id <> @rId)
		BEGIN
			SELECT -101 AS Result
			RETURN
		END
	END

	DECLARE @lNextNumber BIGINT 

	IF(@rId = 0)	
	BEGIN
		
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAcademic.tLessonPlan', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	INSERT INTO sAcademic.tLessonPlan
		(
			Id,
			TopicId,
			DivisionId,
			NoOfPeriodRequired,
			Objective,
			IndicativeHomework,
			Summary,
			StartDate,
			EndDate,
			IsCompleted,
			EmployeeId,
			BatchId,
			CreatedUserId,
			CreatedDateTime,
			LearningExperience,
			ToolsAndTechniques,
			EssentialMaterial,
			AssessmentQuestion,
			TeacherExample,
			GuidedPractice,
			IndependentPractice,
			SubTopic ,
			[Session] ,
			TrainingMethod ,
			PeriodCount
		)
	VALUES
		(
			@lNextNumber,
			@rTopicId,
			@rDivisionId,
			@rNoOfPeriodRequired,
			@rObjective,
			@rIndicativeHomework,
			@rSummary,
			@rStartDate,
			@rEndDate,
			@rIsCompleted,
			@rEmployeeId,
			@rBatchId,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rLearningExperience,
			@rToolsAndTechniques,
			@rEssentialMaterial,
			@rAssessmentQuestion,
			@rTeacherExample,
			@rGuidedPractice,
			@rIndependentPractice,
			CASE WHEN @rSubTopic = '-1' Then '' ELSE @rSubTopic END ,
			@rSession ,
		    @rTrainingMethod ,
			@rPeriodCount
		)
		    SELECT 1 AS Result , @lNextNumber As Id
	END	 

ELSE
	 BEGIN
	 SET @lNextNumber = @rId
	    UPDATE  sAcademic.tLessonPlan
	    SET
			TopicId=@rTopicId,
			DivisionId=@rDivisionId,
			NoOfPeriodRequired=@rNoOfPeriodRequired,
			Objective=@rObjective,
			IndicativeHomework=@rIndicativeHomework,
			Summary=@rSummary,
			StartDate=@rStartDate,
			EndDate=@rEndDate,
			IsCompleted=@rIsCompleted,
			UpdatedUserId=@rCreatedUserId,
			UpdatedDateTime=sCommon.fGetDate(),
			LearningExperience = @rLearningExperience,
			ToolsAndTechniques = @rToolsAndTechniques,
			EssentialMaterial =@rEssentialMaterial,
			EmployeeId = @rEmployeeId,
			AssessmentQuestion=@rAssessmentQuestion,
			TeacherExample=@rTeacherExample,
			GuidedPractice=@rGuidedPractice,
			IndependentPractice=@rIndependentPractice,
			SubTopic= CASE WHEN @rSubTopic = '-1' Then '' ELSE @rSubTopic END ,
			[Session] = @rSession ,
			TrainingMethod =@rTrainingMethod ,
			PeriodCount =@rPeriodCount
	      WHERE Id = @rId
	      SELECT 11 AS Result, @lNextNumber As Id
	 END  	

END
GO
PRINT N'Altering [sAcademic].[pXMLTopicMasterAdd]...';


GO
ALTER PROCEDURE [sAcademic].[pXMLTopicMasterAdd]
	(
		@rXml  nvarchar(MAX),
		@rOrgId int,
		@rClassId int,
		@rSubjectId int,
		@rCreatedUserId bigint 
		
	)
AS

SET NOCOUNT ON

BEGIN

	DECLARE @lInterrorCode INT,@intDoc INT
	DECLARE @lNextNumber BIGINT			
 
	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXml
	
	BEGIN TRY

	CREATE TABLE #tempTable  
	(
		Id bigint identity,
		Number smallint,
		Title nvarchar(500)
	)

	INSERT INTO #tempTable
	(
		Number,
		Title
	)
	SELECT	
			A.Number,
			A.Title
	FROM OPENXML (@intDoc, '/Table/TopicMaster', 1)
	WITH
	(
			Number nvarchar(100) '@Number',
			Title nvarchar(100) '@Title' 
			 
	)A 
	 
	DECLARE @lCount int,@lId int
	SELECT @lCount = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId=Id FROM #tempTable
	
	WHILE @lId <= @lCount 
	BEGIN

		DECLARE @lNumber smallint, @lTitle nvarchar(500), @lSubjectId Int, @lClassId int, @lOrgId smallint
		
		SELECT @lNumber = Number from #tempTable where  Id = @lId
		SELECT @lTitle = Title from #tempTable where  Id = @lId
		
		If Exists(select Id from sAcademic.tTopicMaster where Title=@lTitle AND OrgId=@rOrgId AND ClassId=@rClassId AND SubjectId=@rSubjectId)
		BEGIN
			select -102 as Result, 'Title Already Exists' AS MSG
			RETURN
		END 
	
		If Exists(select Id from sAcademic.tTopicMaster where Number=@lNumber AND OrgId=@rOrgId AND ClassId=@rClassId AND SubjectId=@rSubjectId)
		BEGIN
			select -103 as Result, 'Number Already Exists' AS MSG
			RETURN
		END 

			EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAcademic.tTopicMaster', 0, 1, 1,@outNextNumber = @lNextNumber output

			print @lNextNumber

			INSERT INTO sAcademic.tTopicMaster
			(		Id,
					Number,
					Title,
					SubjectId,
					ClassID,
					OrgId,
					CreatedUserId,
					CreatedDateTime 
					
			)
			Values
			(	
					@lNextNumber,
					@lNumber,
					@lTitle,
					@rSubjectId,
					@rClassID,
					@rOrgId,
					@rCreatedUserId,
					sCommon.fGetDate()   
			)

		EXEC sp_xml_removedocument @intDoc
	
		SELECT @lNextNumber = MAX(ID) FROM sAcademic.tTopicMaster Where OrgId=@rOrgId
		EXECUTE sCommon.pTableRowIdModify @rOrgId, @rTableTitle = 'sAcademic.tTopicMaster', @rCurrentNumber = @lNextNumber 

		SET @lId=@lId+1
		
	END
	SELECT 1 as Result
	DROP Table #tempTable
	END TRY	

	BEGIN CATCH 
	SELECT -1 AS Result,error_message() AS MSG
	END CATCH
END
GO
PRINT N'Altering [sAccounting].[pRptVoucherEntryGet]...';


GO


ALTER PROCEDURE [sAccounting].[pRptVoucherEntryGet]
	
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rTransactionId bigint,
	 @rAccountingYearId int,
	 @rTransactionDate date,
	 @rOrgGroupId int,
	 @rAccountId int
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN  
	 
  SELECT    
        @rBuilder1 AS Builder1,
		@rBuilder2 AS Builder2,
		T.Id AS TransactionId,
		T.Narration,
		T.TransactionDate,
		T.VoucherNumber,
		TD.Amount,
		TD.Id as TransactionDetailId,
		LM.Title AS Ledger,
		VTM.Title AS VoucherType,
		TD.TransactionTypeId ,
		(CASE WHEN TD.Amount > 0 THEN TD.Amount
		ELSE 0 END) DebitAmount,
		(CASE WHEN TD.Amount < 0 THEN -1 * TD.Amount
		ELSE 0 END) CreditAmount,
		AM.ReportOrgTitle As OrgTitle,
		AM.SmallAddress1 AS SmallAddress1,
		AM.SmallAddress2 AS SmallAddress2,
		T.ChequeNo,
		T.ChequeDate,
		AM.Title as AccountTitle,
		U.DisplayTitle As CreatedUserName

		--sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(TD.Amount,0.00),1,2,@rOrgGroupId) AS AmountInWords
			  
  FROM 
       sAccounting.tTransaction T
       INNER JOIN sAccounting.tTransactionDetail TD ON T.Id=TD.TransactionId
       LEFT JOIN sAccounting.tLedgerMaster LM On LM.Id=TD.LedgerId
       LEFT JOIN sAccounting.tVoucherTypeMaster VTM ON VTM.Id=T.VoucherTypeId
       LEFT JOIN sAccounting.tAccountMaster AM ON AM.Id=T.AccountId
	   LEFT JOIN sSecurity.tUser U ON U.Id = T.CreatedUserId
      
  WHERE 
		(T.Id=@rTransactionId OR @rTransactionId = 0 )  
		AND (T.AccountingYearId=@rAccountingYearId)  AND LM.AccountingYearId = @rAccountingYearId
		ANd (T.TransactionDate=@rTransactionDate OR ISNULL(@rTransactionDate,'')='1900-01-01')     
		AND (T.OrgGroupId=@rOrgGroupId)
		AND (T.AccountId=@rAccountId )  
   Order By T.VoucherTypeId,T.VoucherNumber
END
GO
PRINT N'Altering [sAdmission].[pAdmissionFormGet]...';


GO
 
  

ALTER PROCEDURE [sAdmission].[pAdmissionFormGet]

	 @rAdmissionFormId bigint = 0,
	 @rFormNo int,
	 @rForClassId int,
	 @rBatchId int=0,
	 @rStatusId smallint='',
	 @rOrgId int,
	 @rFirstName nvarchar(100),
	 @rLastName nvarchar(100),
	 @rMeritMarks decimal(10,2),
	 @rFromDate datetime= '1900-01-01',
	 @rToDate datetime = '9990-12-31',
	 @rDepartmentId int=0,
	 @rIsTransportFilter int=2,
	 @rIsHostelFilter int=2,
	 @rDataEntryStatusId smallint=0,
	 @rIsCommonSearch bit=0
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	
	SET @rToDate = DATEADD(d,1,@rToDate)		 

BEGIN 
		--IF @rIsSearch = 1
		BEGIN
		SET @rFirstName = ISNULL(@rFirstName,'')
		SET @rLastName = ISNULL(@rLastName,'')
			SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
			SET @rLastName = sCommon.fSearchStringGet(@rLastName)
		END
	
	SELECT 
			 AF.Id
			,AF.FormNo
			,AF.FirstName
			,AF.MiddleName
			,AF.LastName

			--,AF.FirstName+' '+AF.LastName as Name
			,[sStudent].[fStudentNameGet](AF.FirstName , ISNULL(AF.MiddleName,'') , AF.LastName,OM.StudentNameFormatId ) AS Name

			,AF.ForClassId
			,AF.BatchId
			,CM.Title AS ClassName
			,AF.ForBatch
			,ISNULL(AF.DateOfBirth,'') AS DateOfBirth
			,AF.DateOfBirthInWord
			,AF.StatusId
			,SM.Title as StatusName
			,ISNULL(ASM.Title,'') AS FormStatus
			,AF.StreetOne
			,AF.StreetTwo
			,AF.StreetThree
			,AF.Country
			,AF.State
			,AF.City
			,AF.ZipCode
			,AF.ContactNo
			,AF.ContactEmail
			,AF.Remark
			,AF.PreviousSchool
			,AF.MeritMarks
			,AF.MeritGrade
			,AF.GenderId
			,ISNULL(GM.Title,'') AS Gender
			,ISNULL(AF.ReligionId,0) AS ReligionId
			,AF.FamilyCast
			,AF.FamilySubCast
			,ISNULL(AF.CastCategoryId,0) AS CastCategoryId
			,AF.CreatedDateTime AS AdmissionFormDate
			,ISNULL(AF.StudentId,0) AS StudentId
			,ISNULL(AF.AdmissionDate,'') AS AdmissionDate
			--,AF.StudentPhoto,
			,(CASE WHEN @rIsCommonSearch=0 THEN ISNULL(AF.StudentPhoto,0) ELSE NULL END) AS StudentPhoto
			
			,ISNULL(AFFD.FatherName,'') AS FatherName,
			Isnull(AFFD.FatherDateOfBirth,'') AS FatherDateOfBirth,
			Isnull(AFFD.FatherOccupation,'') AS FatherOccupation,
			Isnull(AFFD.FatherQualification,'') AS FatherQualification,
			Isnull(AFFD.FatherContactNo,'') AS FatherContactNo,
			Isnull(AFFD.FatherMail,'') AS FatherMail,
			Isnull(AFFD.FatherOfficeAddress,'') As FatherOfficeAddress,
			isnull(AFFD.FatherOrganization,'') as FatherOrganization,
			isnull(AFFD.FatherDesignation,'') as FatherDesignation,
			Isnull(AFFD.FatherIncome,'') as FatherIncome,
			Isnull(AFFD.MotherName,'') AS MotherName,
			iSNULL(AFFD.MotherDateOfBirth,'')AS MotherDateOfBirth,
			isnull(AFFD.MotherOccupation,'') AS MotherOccupation,
			isnull(AFFD.MotherQualification,'') AS MotherQualification,
			isnull(AFFD.MotherContactNo,'') as MotherContactNo,
			isnull(AFFD.MotherMail,'') As MotherMail,
			isnull(AFFD.MotherOfficeAddress,'') as MotherOfficeAddress,
			isnull(AFFD.MotherOrganization,'') as MotherOrganization,
			isnull(AFFD.MotherDesignation,'') as MotherDesignation,
			isnull(AFFD.MotherIncome,'') as MotherIncome,
			AF.MotherTongue AS MotherTongue,
		 
			isnull(AFFD.RelationshipName,'') as RelationshipName,
			isnull(AFFD.RelativeName,'')  AS GuardianName ,
			isnull(AFFD.RelativeDateOfBirth ,'')AS GuardianDateOfBirth,
			isnull(AFFD.RelativeOccupation,'') AS GuardianOccupation,
			isnull(AFFD.RelativeQualification,'') AS GuardianQualification,
			isnull(AFFD.RelativeContactNo,'') AS GuardianContactNo,
			isnull(AFFD.RelativeMail,'')  AS GuardianMail,
			isnull(AFFD.RelativeOfficeAddress,'') AS GuardianOfficeAddress,
			isnull(AFFD.RelativeOrganization,'') as RelativeOrganization,
			isnull(AFFD.RelativeDesignation,'') as RelativeDesignation,
			isnull(AFFD.RelativeIncome,'') AS GuardianIncome  ,
			isnull(AF.IsHostelRequired,0) AS IsHostelRequired  ,
			isnull(AF.IsTransportRequired,0) AS IsTransportRequired,
			isnull(AF.BirthPlace,'') AS BirthPlace,
			isnull(AF.BirthTaluka,'') AS BirthTaluka,
			isnull(AF.BirthDistrict,'') AS BirthDistrict,
			isnull(AF.BloodGroupId,0) AS BloodGroupId,
			isnull(AF.AdhaarNumber,'') AS AdhaarNumber,
			isnull(AF.IsPysicalDeformity,0) AS IsPysicalDeformity,
			isnull(AF.PysicalDeformityRemark,'') AS PysicalDeformityRemark,
			isnull(AF.IsMajorIllness,0) AS IsMajorIllness,
			isnull(AF.MajorIllnessRemark,'') AS MajorIllnessRemark,
			isnull(AF.IsAllergy,0) AS IsAllergy,
			isnull(AF.AllergyRemark,'') AS AllergyRemark,
			isnull(AF.BirthState,'') AS BirthState,
			isnull(AF.BirthCountry,'') AS BirthCountry,
			isnull(SA.PreviousStandard,'') As PreviousStandard,
	        isnull(AFFD.LiveWithId,0) AS LiveWithId,
			--isnull(ASB.SiblingName,'') AS SiblingName,
			--isnull(ASB.SiblingRelation,'')AS SiblingRelation,
			
			--isnull(ASB.SiblingStandard,'') AS SiblingStandard,
			--isnull(ASB.SiblingSchool,'')AS SiblingSchool
			
			 ISNULL(AF.CustomField1Answer,'') AS CustomField1Answer,
			 ISNULL(AF.CustomField2Answer,'') AS CustomField2Answer,
			 ISNULL(AF.CustomField3Answer,'') AS CustomField3Answer,
			 SM1.Title AS DataEntryStatus,
			 AF.Nationality,
			 ISNULL(AFFD.FatherAdhaarNumber,'') AS FatherAdhaarNumber,
			 AF.CreatedDateTime AS AdmissionFormDate,
			 ISNULL(AFFD.MotherAadhaarNumber,'') AS MotherAadhaarNumber,
			 ISNULL(AFFD.RelativeAadhaarNumber,'') AS RelativeAadhaarNumber,
			 ISNULL(AFFD.FatherOfficePhone,'') AS FatherOfficePhone,
			 ISNULL(AFFD.MotherOfficePhone,'') AS MotherOfficePhone,
			 CASE WHEN AF.DataEntryModeId = 561 THEN 'Online' ELSE 'Offline' END DataEntryMode
			 
  FROM 
            sAdmission.tAdmissionForm AF
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = AF.OrgId
			INNER JOIN sCommon.tStatusMaster SM ON SM.Id=AF.StatusId
			INNER JOIN sCommon.tStatusMaster SM1 ON SM1.Id=AF.DataEntryStatusId			
			INNER JOIN sSetup.tClassMaster CM ON CM.Id=AF.ForClassId
			LEFT JOIN sCommon.tGenderMaster GM ON GM.Id=AF.GenderId
			LEFT JOIN sAdmission.tAdmissionFormSelected AFS ON AF.Id = AFS.AdmissionFormId
			LEFT JOIN sAdmission.tAdmissionStatusMaster ASM ON ASM.Id = AFS.AdmissionStatusId
			LEFT JOIN sAdmission.tAdmissionFormFamilyDetail AFFD ON AFFD.AdmissionFormId = AF.Id
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = AF.StudentId
			 --LEFT JOIN sAdmission.tAdmissionFormSibling ASB ON ASB.AdmissionFormId = AF.Id
			LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentID=AF.StudentID
  WHERE 
  
      ((AF.Id = @rAdmissionFormId) OR (@rAdmissionFormId = 0))
		AND ((AF.FormNo=@rFormNo) OR (@rFormNo=0))
		AND ((AF.FirstName like @rFirstName) OR (@rFirstName=''))
		AND ((AF.LastName like @rLastName) OR (@rLastName=''))
		--AND ((AF.CreatedDateTime>= @rFromDate) OR (@rFromDate=''))
		AND ((Cast(AF.AdmissionDate as datetime)>= @rFromDate) OR (@rFromDate=''))
		--AND ((AF.AdmissionDate is not null AND Cast(AF.AdmissionDate as datetime)>= @rFromDate) or (AF.AdmissionDate is null AND AF.CreatedDateTime>= @rFromDate) or (@rFromDate=''))
		--AND ((AF.CreatedDateTime < @rToDate) OR (@rToDate=''))
		AND ((Cast(AF.AdmissionDate as datetime)< @rToDate) OR (@rToDate=''))
		--AND ((AF.AdmissionDate is not null AND Cast(AF.AdmissionDate as datetime)< @rToDate) or (AF.AdmissionDate is null AND AF.CreatedDateTime< @rToDate) or (@rToDate=''))
		AND ((AF.StatusId=@rStatusId) OR (@rStatusId=0))
		AND ((AF.ForClassId=@rForClassId) OR (@rForClassId=0)) 
		AND ((AF.BatchId=@rBatchId) OR (@rBatchId=0)) 
		--AND ((AF.MeritMarks=@rMeritMarks) OR (@rMeritMarks=0))
		AND ((AF.StatusId=@rStatusId) OR (@rStatusId = ''))
		AND ((AF.DataEntryStatusId=@rDataEntryStatusId) OR (@rDataEntryStatusId = 0))
		AND ((CM.DepartmentId=@rDepartmentId) OR (@rDepartmentId = 0))
		AND AF.OrgId=@rOrgId
        AND ((ISnull(AF.IsHostelRequired,0)=@rIsHostelFilter ) OR   (ISNULL(@rIsHostelFilter,2)=2) )
	   AND ((ISNUll(AF.IsTransportRequired,0)=@rIsTransportFilter) OR (isnull(@rIsTransportFilter,0) = 2))
	  
 order by AF.FormNo
END
GO
PRINT N'Altering [sAdmission].[pAdmissionGenerateReport]...';


GO

ALTER PROCEDURE [sAdmission].[pAdmissionGenerateReport]
 
	@rId bigint,
	@rClassId int,
	@rClassIds nvarchar(max)='',
	@rColumnName nvarchar(max) = '',
	@rOrderBy nvarchar(max) = 'RollNo',
	@rOrgId smallint,
	@rFormNo [nvarchar](20) = '',
	@rFirstName [nvarchar](50) = '',	
	@rLastName [nvarchar](50) = '',
	@rFatherName nvarchar(50)='',
	@rMotherName nvarchar(50)='',
	@rCountry [nvarchar](50) = '',
	@rState [nvarchar](50) = '',
	@rCity nvarchar(max) = '',
	@rStatusId nvarchar(max) = '',
	@rBloodGroupId nvarchar(max) = '',
	@rGenderId nvarchar(max) = '',
	@rReligionId nvarchar(max) = '',
	@rCastCategoryId nvarchar(max) = '',
	@rMotherTongue nvarchar(max) = '',
	@rNationality nvarchar(max) = '',
	@rFatherQualification nvarchar(max) = '',
	@rMotherQualification nvarchar(max) = '',
	@rFatherOccupation nvarchar(max) = '',
	@rMotherOccupation nvarchar(max) = '',
	@rPreviousSchool nvarchar(max) = '',
	@rDOBFromDate Date = '0001-01-01',
	@rDOBToDate Date = '9999-12-31',
	@rDOAFromDate Date = '0001-01-01',
	@rDOAToDate Date = '9999-12-31',
	@rBatchId int = 0,
	@rFromFormNo nvarchar(20), 
	@rToFormNo nvarchar(20),
	@rContactNo nvarchar(50) = '',
	@rReportName nvarchar(50) ='',
	@rPageIndex int,
	@rCreatedUserId bigint,
	@rIsCommon bit = 0,
	@rIsCommonForOrg bit = 0,
	@rSavedReport nvarchar(max) = ''
	

AS
BEGIN
	 
	SET NOCOUNT ON;

    IF(@rDOBFromDate IS NULL)
		SET @rDOBFromDate='0001-01-01'
	IF(@rDOBToDate IS NULL)
		SET @rDOBToDate='9990-12-31'
	IF(@rDOAFromDate IS NULL)
		SET @rDOAFromDate='0001-01-01'
	IF(@rDOAToDate IS NULL)
		SET @rDOAToDate='9990-12-31'

	If @rDOBToDate <> '9999-12-31'
		SET @rDOBToDate = DATEADD(d,1,@rDOBToDate)
	If @rDOAToDate <> '9999-12-31'
		SET @rDOAToDate = DATEADD(d,1,@rDOAToDate)
		
		IF(@rReportName != '' AND @rId = 0)
		BEGIN
			DECLARE @lNextNumber BIGINT, @query  AS NVARCHAR(MAX), @lColumnValue nvarchar(max) 
			declare @lColumnName nvarchar(max), @lClmNm nvarchar(max)
			declare @lOrderBy nvarchar(max), @lOrdBy nvarchar(max)
			EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAdmission.tAdmissionCustomSearch', 0, 1, 1,@outNextNumber = @lNextNumber output
			--PRINT(@lNextNumber)
			--Removing Table Alias while Inserting in Table
			SET @lColumnName = @rColumnName
			SET @lClmNm = Replace(@lColumnName,'AT.','')
			SET @lClmNm = Replace(@lClmNm,'ACF.','')
			
			SET @lOrderBy = @rOrderBy
			SET @lOrdBy = Replace(@lOrderBy,'AT.','')
			SET @lOrdBy = Replace(@lOrdBy,'ACF.','')
			--print(@lOrdBy)
			--print(@lClmNm)
			INSERT INTO sAdmission.tAdmissionCustomSearch
			(
				Id,Title,ColumnName,OrderBy,OrgId,BatchId, ClassId, FormNo, FromFormNo, ToFormNo,StatusId,ClassIds,FirstName,LastName,FatherName, 
				MotherName, DOBFromDate,DOBToDate,DOAFromDate,DOAToDate,Country,State,City,ContactNo,GenderId,ReligionId,CastCategoryId,
				Nationality,BloodGroupId,MotherTongueId,PreviousSchool,FatherQualification,MotherQualification,FatherOccupation,MotherOccupation,
				IsCommonUsers,IsCommonForOrg, CreatedUserId,CreatedDateTime
			)
			VALUES
			(
				@lNextNumber,@rReportName,@lClmNm,@lOrdBy,@rOrgId,@rBatchId,@rClassId,@rFormNo,@rFromFormNo,@rToFormNo,@rStatusId,@rClassIds,@rFirstName,@rLastName,
				@rFatherName,@rMotherName,@rDOBFromDate,@rDOBToDate,@rDOAFromDate,@rDOAToDate,@rCountry,@rState,
				@rCity,@rContactNo,@rGenderId,@rReligionId,@rCastCategoryId,@rNationality,@rBloodGroupId,@rMotherTongue,
				@rPreviousSchool,@rFatherQualification,@rMotherQualification,@rFatherOccupation,@rMotherOccupation,
				@rIsCommon,@rIsCommonForOrg,@rCreatedUserId,sCommon.fGetDate()
			)
		END
		--IF(@rId > 0)
		--BEGIN
		--	Select @rColumnName=ColumnName,@rOrderBy=OrderBy,@rOrgId=OrgId,@rBatchId=BatchId,@rClassId=ClassId,@rFormNo=FormNo,
		--	@rFromFormNo=FromFormNo,@rToFormNo=ToFormNo,@rStatusId=StatusId,@rClassIds=ClassIds,@rFirstName=FirstName,@rLastName=LastName,
		--	@rFatherName=FatherName,@rMotherName=MotherName,@rDOBFromDate=DOBFromDate,@rDOBToDate=DOBToDate,@rDOAFromDate=DOAFromDate,
		--	@rDOAToDate=DOAToDate,@rCountry=Country,@rState=State,@rCity=City,@rContactNo=ContactNo,@rGenderId=GenderId,@rReligionId=ReligionId,
		--	@rCastCategoryId=CastCategoryId,@rNationality=Nationality,@rBloodGroupId=BloodGroupId,@rMotherTongue=MotherTongueId,@rPreviousSchool=PreviousSchool,
		--	@rFatherQualification=FatherQualification,@rMotherQualification=MotherQualification,@rFatherOccupation=FatherOccupation,@rMotherOccupation=MotherOccupation
		--	FROM sAdmission.tAdmissionCustomSearch WHERE Id=@rId
		--END

		DECLARE @Sql nvarchar(MAX)= ''
		
		SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
		SET @rLastName = sCommon.fSearchStringGet(@rLastName)
		SET @rFatherName = sCommon.fSearchStringGet(@rFatherName)
		SET @rMotherName = sCommon.fSearchStringGet(@rMotherName)
		SET @rCountry = sCommon.fSearchStringGet(@rCountry)
		SET @rState = sCommon.fSearchStringGet(@rState)
		SET @rCity = sCommon.fSearchStringGet(@rCity)
		SET @rNationality = sCommon.fSearchStringGet(@rNationality)
		SET @rMotherTongue = sCommon.fSearchStringGet(@rMotherTongue)
		SET @rPreviousSchool = sCommon.fSearchStringGet(@rPreviousSchool)
		SET @rFatherQualification = sCommon.fSearchStringGet(@rFatherQualification)
		SET @rMotherQualification = sCommon.fSearchStringGet(@rMotherQualification)
		SET @rFatherOccupation = sCommon.fSearchStringGet(@rFatherOccupation)
		SET @rMotherOccupation = sCommon.fSearchStringGet(@rMotherOccupation)
		
		






	SELECT * INTO #MyAdmissionSearchTable 
	FROM
	(SELECT  CASE 
			WHEN AF.BloodGroupId = 1 THEN 'A+'
			WHEN AF.BloodGroupId = 2 THEN 'A-'
			WHEN AF.BloodGroupId = 3 THEN 'B+'
			WHEN AF.BloodGroupId = 4 THEN 'B-'
			WHEN AF.BloodGroupId = 5 THEN 'O+'
			WHEN AF.BloodGroupId = 6 THEN 'O-'
			WHEN AF.BloodGroupId = 7 THEN 'AB+'
			WHEN AF.BloodGroupId = 8 THEN 'AB-'
			WHEN AF.BloodGroupId = 9 THEN '-'
		END AS BloodGroup, 
		CASE 
			WHEN AFD.LiveWithId = 1 THEN 'Both'
			WHEN AFD.LiveWithId = 2 THEN 'Father'
			WHEN AFD.LiveWithId = 3 THEN 'Mother'
			WHEN AFD.LiveWithId = 4 THEN 'Guardian'
		END AS LiveWith,
		CASE 
			WHEN AF.GenderId = 1 THEN 'Male'
			WHEN AF.GenderId = 2 THEN 'Female'
		END AS Gender,
		AF.Id,
		AF.FormNo as FormNo,
		AF.FirstName,
		AF.MiddleName,
		AF.LastName as SurName,
		AF.FirstName + ' '+  AF.LastName AS StudentName,
		AF.DateOfBirth,
		AF.OrgId,
		AF.BatchId,
		AF.StatusId,
		AF.StreetOne,
		AF.StreetTwo,
		AF.StreetThree,
		AF.Country,
		AF.State,
		AF.City,
	   AF.ZipCode,
	   AF.ContactNo,
	   AF.ContactEmail,
	   AF.Remark,
	   AF.PreviousSchool,
	   AF.MeritMarks,
	   AF.MeritGrade,
	   AF.GenderId,
	   AF.ReligionId,
	   AF.FamilyCast,
	   AF.FamilySubCast,
	   AF.CastCategoryId,
	   AF.StudentId,
	   AF.FormPassword,
	   AF.Nationality,
	   AF.MotherTongue,
	   AF.IsPysicalDeformity,
	   AF.PysicalDeformityRemark,
	   AF.IsMajorIllness,
	   AF.MajorIllnessRemark,
	   AF.IsAllergy,
	   AF.AllergyRemark,
	   AF.BirthPlace,
	   AF.BirthTaluka,
	   AF.BirthDistrict,
	   AF.BloodGroupId,
	   AF.AdhaarNumber,
	   AF.BirthState,
	   AF.BirthCountry,
	   AF.OriginalStudentPhoto,
	   AF.OtherInformation as OtherInformation,
	   AF.AdmissionDate AS AdmissionFormDate,
	   CASE WHEN AF.IsHostelRequired = 0 THEN 'No' WHEN AF.IsHostelRequired = 1 THEN 'Yes' END AS HostelRequired,
	   CASE WHEN AF.IsTransportRequired = 0 THEN 'No' WHEN AF.IsTransportRequired = 1 THEN 'Yes' END AS TransportRequired,
	   OM.Title as Organization, 
	   OM.AffiliationId,
	   BM.Title as Batch, 
	   BM.FromDate, 
	   BM.ToDate, 
	   SM.Title as Status,
	   CM.Id as ClassId,
	   CM.Title as Class,
	   RM.Title AS Religion,
	   APA1.AcademicYear as FatherAcademicYear,
	   APA1.AdmissionClass as FatherAdmissionClass,
	   APA1.InstituteName as FatherInstituteName,
	   APA1.InstituteCity as FatherInstituteCity,
	   APA1.InstituteAffiliaton as FatherInstituteAffiliation,
	   APA1.Language as FatherMediumOfStudy,
	   APA2.AcademicYear as MotherAcademicYear,
	   APA2.AdmissionClass as MotherAdmissionClass,
	   APA2.InstituteName as MotherInstituteName,
	   APA2.InstituteCity as MotherInstituteCity,
	   APA2.InstituteAffiliaton as MotherInstituteAffiliation,
	   APA2.Language as MotherMediumOfStudy,
	   APA3.AcademicYear as GuardianAcademicYear, 
	   APA3.AdmissionClass as GuardianAdmissionClass,
	   APA3.InstituteName as GuardianInstituteName,
	   APA3.InstituteCity as GuardianInstituteCity,
	   APA3.InstituteAffiliaton as GuardianInstituteAffiliation,
	   APA3.Language as GuardianMediumOfStudy,
	   RSM.Title,
	   (STUFF((SELECT ',' + ReferenceDetail FROM sAdmission.tAdmissionFormReference WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS ReferenceDetail,
	   (STUFF((SELECT ',' + ReferenceName FROM sAdmission.tAdmissionFormReference WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS ReferenceName,
	   (STUFF((SELECT ',' + ReferenceRelation FROM sAdmission.tAdmissionFormReference WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS ReferenceRelation,
	   (STUFF((SELECT ',' + ReferenceContactNo FROM sAdmission.tAdmissionFormReference WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS ReferenceContactNo,
	   AFSA.AcademicYear as StudentAcademicYear,
	   AFSA.AdmissionClass as StudentAdmissionClass,
	   (STUFF((SELECT ',' + SiblingName FROM sAdmission.tAdmissionFormSibling WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS SiblingName,
	   (STUFF((SELECT ',' + SiblingRelation FROM sAdmission.tAdmissionFormSibling WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS SiblingRelation,
	   (STUFF((SELECT ',' + SiblingStandard FROM sAdmission.tAdmissionFormSibling WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS SiblingStandard,
	   (STUFF((SELECT ',' + SiblingSchool FROM sAdmission.tAdmissionFormSibling WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS SiblingSchool,
	   (STUFF((SELECT ',' + Code FROM sAdmission.tAdmissionFormSibling WHERE AdmissionFormId = AF.Id FOR XML PATH ('')), 1, 1, '')) AS Code,
	   AFSA.InstituteName as StudentInstituteName,
	   AFSA.InstituteCity as StudentInstituteCity,
	   AFSA.InstituteAffiliaton as StudentInstituteAffiliation, 
	   AFSA.Result, 
	   AFSA.Language,
	   AFD.FatherName, 
	   AFD.FatherDateOfBirth,
	   AFD.FatherOccupation,
	   AFD.FatherQualification,
	   AFD.FatherContactNo,
	   AFD.FatherMail,
	   AFD.FatherOfficeAddress,
	   AFD.FatherIncome,
	   AFD.MotherName,
	   AFD.MOtherDateOfBirth,
	   AFD.MotherOccupation,
	   AFD.MotherQualification,
	   AFD.MotherContactNo,
	   AFD.MotherMail,
	   AFD.MotherOfficeAddress, 
	   AFD.MotherIncome,
	   AFD.RelationshipName,
	   AFD.RelativeName,
	   AFD.RelativeDateOfBirth,
	   AFD.RelativeOccupation,
	   AFD.RelativeQualification,
	   AFD.RelativeContactNo,
	   AFD.RelativeMail,
	   AFD.RelativeOfficeAddress,
	   AFD.RelativeIncome,
	   AFD.FatherPhoto,
	   AFD.MotherPhoto,
	   AFD.RelativePhoto,
	   AFD.FatherOrganization,
	   AFD.FatherDesignation,
	   AFD.FatherOfficePhone,
	   AFD.MotherOrganization,
	   AFD.MotherDesignation,
	   AFD.MotherOfficePhone,
	   AFD.RelativeOrganization,
	   AFD.RelativeDesignation,
	   AFD.RelativeOfficePhone,
	   AFD.IsFatherExStudent,
	   AFD.FatherPassOutYear,
	   AFD.IsMotherExStudent,
	   AFD.MotherPassOutYear, 
	   AFD.IsRelativeExStudent,
	   AFD.RelativePassOutYear,
	   AFD.FatherAdhaarNumber,
	   AFD.OriginalFatherPhoto,
	   AFD.OriginalMotherPhoto,
	   AFD.OriginalRelativePhoto,
	   AFD.MotherAadhaarNumber,
	   AFD.RelativeAadhaarNumber,
	   U.LoginTitle As AdmissionHandledBy,
	   SM1.CreatedDateTime AS AdmissionFormConvertedDate
		from sAdmission.tAdmissionForm AF
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = AF.OrgId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id = AF.BatchId
		INNER JOIN  sCommon.tStatusMaster SM ON AF.StatusId = SM.Id
		INNER JOIN sSetup.tClassMaster CM ON CM.Id = AF.ForClassId
		INNER JOIN sSecurity.tUser U ON U.Id = AF.CreatedUserId
		LEFT JOIN  sStudent.tStudentAdmission SA ON SA.AdmissionFormId = AF.Id
		LEFT JOIN  sStudent.tStudentMaster SM1 ON SM1.Id = AF.StudentId AND AF.StatusId=208
		LEFT JOIN sCommon.tReligionMaster RM ON RM.Id = AF.ReligionId
		LEFT JOIN sAdmission.tAdmissionFormParentAcademic APA1 ON APA1.AdmissionFormId = AF.Id AND APA1.RelationShipId = 1
		LEFT JOIN sAdmission.tAdmissionFormParentAcademic APA2 ON APA2.AdmissionFormId = AF.Id AND APA2.RelationShipId = 2
		LEFT JOIN sAdmission.tAdmissionFormParentAcademic APA3 ON APA3.AdmissionFormId = AF.Id AND APA3.RelationShipId = 7
		LEFT JOIN sCommon.tRelationshipMaster RSM ON RSM.Id = APA1.RelationshipId
		LEFT JOIN sCommon.tRelationshipMaster RSM1 ON RSM1.Id = APA2.RelationshipId
		LEFT JOIN sCommon.tRelationshipMaster RSM2 ON RSM2.Id = APA3.RelationshipId
		--LEFT JOIN sAdmission.tAdmissionFormReference AFR ON AFR.AdmissionFormId = AF.Id
		--LEFT JOIN sAdmission.tAdmissionFormSibling AFS ON AFS.AdmissionFormId = AF.Id
		LEFT JOIN sAdmission.tAdmissionFormStudentAcademic AFSA ON AFSA.AdmissionFormId = AF.Id
		LEFT JOIN sAdmission.tAdmissionFormFamilyDetail AFD ON AFD.AdmissionFormId = AF.Id
		WHERE ((AF.OrgId = @rOrgId ) OR (@rOrgId = 0))
				AND ((AF.BatchId = @rBatchId) OR (@rBatchId = 0))
				AND ((AF.ForClassId = @rClassId) OR (@rClassId = 0))
			    AND ((ISNULL(AF.ForClassId,0) IN (SELECT cast(item as int) FROM sCommon.fnsplit(@rClassIds,',')) OR (@rClassIds='')))
				AND ((AF.FormNo = @rFormNo) OR (@rFormNo = ''))
				AND ((AF.FormNo  >= @rFromFormNo) OR (ISNULL(@rFromFormNo,'') = ''))
				AND ((AF.FormNo  <= @rToFormNo) OR (ISNULL(@rToFormNo,'') = ''))
			    AND (AF.StatusId = @rStatusId)
				AND ((AF.FirstName Like @rFirstName) OR (@rFirstName = ''))
				AND ((AF.LastName Like @rLastName)OR(@rLastName = ''))
				AND ((AFD.FatherName like @rFatherName) OR (@rFatherName=''))
				AND ((AFD.MotherName like @rMotherName) OR (@rMotherName=''))
				AND (( AF.DateOfBirth  >= @rDOBFromDate) OR (ISNULL(@rDOBFromDate,'') = ''))
				AND (( AF.DateOfBirth  <= @rDOBToDate) OR (ISNULL(@rDOBToDate,'') = ''))
				--AND (( CAST(AF.CreatedDateTime AS Date)  >= @rDOAFromDate) OR (ISNULL(@rDOAFromDate,'') = ''))
				--AND (( CAST(AF.CreatedDateTime AS Date)  <= @rDOAToDate) OR (ISNULL(@rDOAToDate,'') = ''))
				AND ((Convert(nvarchar(12),AF.CreatedDateTime) >=  @rDOAFromDate) OR (@rDOAFromDate=''))
			    AND ((Convert(nvarchar(12),AF.CreatedDateTime) <=  @rDOAToDate) OR (@rDOAToDate=''))
				AND ((ISNULL(AF.Country,'') Like @rCountry )OR(@rCountry = ''))
				AND ((ISNULL(AF.State,'') Like @rState)OR(@rState = ''))
				AND ((ISNULL(AF.City,'') Like @rCity)OR(@rCity = ''))
				AND ((AF.ContactNo=@rContactNo) OR (@rContactNo=0))
				AND (ISNULL(AF.GenderId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rGenderId,','))OR @rGenderId='') 
				AND (ISNULL(AF.ReligionId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rReligionId,','))OR @rReligionId='')
				AND (ISNULL(AF.CastCategoryId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rCastCategoryId,','))OR @rCastCategoryId='')
				AND ((AF.Nationality Like @rNationality) OR (@rNationality = ''))
				AND (ISNULL(AF.BloodGroupId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rBloodGroupId,','))OR @rBloodGroupId='')
				AND ((AF.MotherTongue Like @rMotherTongue) OR (@rMotherTongue = ''))
				AND ((AF.PreviousSchool Like  @rPreviousSchool) OR (@rPreviousSchool = ''))
				AND ((AFD.FatherQualification Like  @rFatherQualification) OR (@rFatherQualification = ''))
				AND ((AFD.MotherQualification Like @rMotherQualification) OR (@rMotherQualification = ''))
				AND ((AFD.FatherOccupation Like @rFatherOccupation) OR (@rFatherOccupation = ''))
				AND ((AFD.MotherOccupation Like @rMotherOccupation) OR (@rMotherOccupation = '')) 
				) AS AdmissionReportGeneration
				
		  --SELECT * FROM #MyAdmissionSearchTable

		  --AdmissionCriteriaFields
		  DECLARE @cols AS NVARCHAR(MAX),@criteriaquery  AS NVARCHAR(MAX)
			-- CTE for Admission Form Field
			;WITH AdmissionCriteriaFormCTE AS (SELECT  DISTINCT FieldRemark FROM sAdmission.tAdmissionCriteriaForm ACF
			LEFT JOIN sAdmission.tAdmissionFormField AFF ON ACF.Id = AFF.AdmissionCriteriaFormId
			--WHERE (ACF.BatchId = @rBatchId OR @rBatchId = 0) AND (ACF.ClassId = @rClassId OR @rClassId = 0)
			GROUP BY Id,FieldRemark,ClassId,BatchId
			)
			SELECT @cols = STUFF((SELECT  ',' + QUOTENAME(FieldRemark) FROM AdmissionCriteriaFormCTE
			GROUP BY FieldRemark
			ORDER BY FieldRemark FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')
			

			IF object_id('tempdb..##tempAdmissionCriteriaForm') IS NOT NULL 
			BEGIN
			--print('Hi')
			   DROP TABLE ##tempAdmissionCriteriaForm 
			END
			IF(@cols IS NOT NULL)
			BEGIN 
				SET @criteriaquery = 'SELECT DISTINCT AdmissionFormId,' + @cols + ' INTO ##tempAdmissionCriteriaForm FROM 
							 (
								SELECT DISTINCT  AFF.AdmissionFormId,AdmissionFormFieldResponse, FieldRemark
								FROM 
								sAdmission.tAdmissionCriteriaForm ACF
								INNER JOIN sAdmission.tAdmissionFormField AFF ON ACF.Id = AFF.AdmissionCriteriaFormId
								WHERE ((ACF.BatchId = '+CONVERT(nvarchar(10),@rBatchId)+') OR (' +CONVERT(nvarchar(10),@rBatchId)+' = 0)) AND ((ACF.ClassId = '+CONVERT(nvarchar(10),@rClassId)+') OR ('+CONVERT(nvarchar(10),@rClassId)+' = 0))
							) x
							PIVOT
							(
								MAX(AdmissionFormFieldResponse)
								FOR FieldRemark IN (' + @cols + ')
							) p' 
				print(@criteriaquery)
				exec (@criteriaquery)
				
				  --SELECT * FROM #MyAdmissionSearchTable
				--SELECT * FROM ##tempAdmissionCriteriaForm ACF
			END
			IF(@cols IS NULL)
				Declare @lSQLAlt nvarchar(max)=''
				IF(@rOrderBy = '' OR @rOrderBy = NULL)
				BEGIN
					SET @lSQLAlt = @lSQLAlt + 'SELECT ' + @rColumnName + ' FROM #MyAdmissionSearchTable AT '
					print(@lSQLAlt)
					exec(@lSQLAlt)
				END
				ELSE
				BEGIN
				print(@rOrderBy)
				SET @lSQLAlt = @lSQLAlt + 'SELECT ' + @rColumnName + ' FROM #MyAdmissionSearchTable AT GROUP BY ' +@rColumnName+ ',' +@rOrderBy+ ' ORDER BY ' +@rOrderBy+ ';'
				print(@lSQLAlt)
				exec (@lSQLAlt)
				END

		  --
		IF(@cols IS NOT NULL)
		Declare @lSQL nvarchar(max) ='', @lqry nvarchar(max)=''
		IF(@rOrderBy = '' OR @rOrderBy = NULL)
		BEGIN
			--SET @lSQL = @lSQL + 'SELECT ' + @rColumnName + ' FROM #MyAdmissionSearchTable AT'
			SET @lSQL = @lSQL + 'SELECT ' + @rColumnName + ' FROM #MyAdmissionSearchTable AT LEFT JOIN ##tempAdmissionCriteriaForm ACF ON AT.Id = ACF.AdmissionFormId'
			print(@lSQL)
			exec(@lSQL)
		END
		ELSE
		BEGIN
		print(@rOrderBy)
			SET @lSQL = @lSQL + 'SELECT ' + @rColumnName + ' FROM #MyAdmissionSearchTable AT LEFT JOIN ##tempAdmissionCriteriaForm ACF ON AT.Id = ACF.AdmissionFormId ORDER BY ' +@rOrderBy+ ';'
			print(@lSQL)
			exec (@lSQL)
			IF OBJECT_ID(N'dbo.MyTable0', N'U') IS NOT NULL 
			 DROP Table ##tempAdmissionCriteriaForm
		END
		
END
GO
PRINT N'Altering [sAdmission].[pRptAdmissionSummaryGet]...';


GO


ALTER PROCEDURE [sAdmission].[pRptAdmissionSummaryGet]
	
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='',
	 @rOrgGroupBatchId int = 0,
	 @rOrgId int = 0,
	 @rBatchId int,
	 @rDepartmentId int = 0,
 	 @rClassId int,
	 @rDivisionId int,
	 @rStatusId int,
	 @rFromDate date,
	 @rToDate date
AS
 SET NOCOUNT ON
			 

BEGIN 
  SELECT 
		@rBuilder1 AS Builder1,
		@rBuilder2 AS Builder2,
		A.Id AS AcademicId,
		SM.FirstName+' '+SM.MiddleName+' '+SM.LastName AS Name,
		SA.AdmissionDate,
		SM.Code,
		A.RollNo,
		CM.Title+'-'+DM.DisplayTitle As ClassDivision,
		CM.Title As Class,
		A.BatchId as Batch,
		SA.BatchId As AdmissionBatch,
		--AF.ContactNo AS ContactNo
		SC.ParentContactNo AS ContactNo,
		OM.DisplayTitle AS Organization,
		OM.Id AS OrgId,
		CM.Id AS ClassId,
		DM.Id AS DivisionId,
		CM.OrderIndex
  FROM 
		sStudent.tAcademic A 
		INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id
		INNER JOIN sStudent.tStudentAdmission SA ON SA.StudentId=SM.Id
		INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId  				 
		INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=A.OrgId
		INNER JOIN sSetup.tBatchMaster BM ON A.BatchId = BM.Id AND SA.BatchId = BM.Id
		LEFT JOIN sStudent.tStudentCommunication SC ON SM.Id = SC.StudentId
  WHERE 	
		(BM.OrgGroupBatchId = @rOrgGroupBatchId OR @rOrgGroupBatchId = 0)
		AND (OM.Id = @rOrgId OR @rOrgId = 0)
		AND (BM.Id = @rBatchId OR @rBatchId = 0)
		AND ((CM.DepartmentId = @rDepartmentId) OR (@rDepartmentId = 0))
		AND ((CM.Id = @rClassId) OR (@rClassId = 0))
		AND ((DM.Id = @rDivisionId) OR (@rDivisionId = 0))
		AND ((SM.StatusId = @rStatusId) OR (@rStatusId = 0))
		AND ((ISNULL(SA.AdmissionDate,'') >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((ISNULL(SA.AdmissionDate,'') <= @rToDate) OR (ISNULL(@rToDate,'')=''))
	
END
GO
PRINT N'Altering [sAdmission].[pRptStudentNewAdmissionListGet]...';


GO

ALTER PROCEDURE [sAdmission].[pRptStudentNewAdmissionListGet]
	
	 @rBatchId int,
	 @rClassId int,
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='',
	 @rFromDate date,
	 @rToDate date,
	 @rOrgId int = 0,
	 @rDepartmentId int = 0,
	 @rDivisionId int,
	 @rStatusId int
AS
 SET NOCOUNT ON
			 

BEGIN 
  SELECT 
		@rBuilder1 AS Builder1,
		@rBuilder2 AS Builder2,
		SM.FirstName+' '+SM.MiddleName+' '+SM.LastName AS Name,
		SA.AdmissionDate,
		SM.Code,
		A.RollNo,
		CM.Title+'-'+DM.DisplayTitle As ClassDivision,
		CM.Title As Class,
		A.BatchId as Batch,
		SA.BatchId As AdmissionBatch,
		--AF.ContactNo AS ContactNo
		SC.ParentContactNo AS ContactNo 
  FROM 
		sStudent.tAcademic A 
		INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id
		INNER JOIN sStudent.tStudentAdmission SA ON SA.StudentId=SM.Id AND SA.BatchId = A.BatchId
		INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId  				 
		INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId
		--LEFT JOIN sAdmission.tAdmissionForm AF ON AF.StudentId=A.StudentId AND AF.BatchId=A.BatchId AND AF.OrgId=A.OrgId
		LEFT JOIN sStudent.tStudentCommunication SC ON SM.Id = SC.StudentId
  WHERE 	
		A.BatchId=@rBatchId 
		AND ((ISNULL(SA.AdmissionDate,'') >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((ISNULL(SA.AdmissionDate,'') <= @rToDate) OR (ISNULL(@rToDate,'')=''))
		AND ((CM.DepartmentId=@rDepartmentId) OR (@rDepartmentId = 0))
		AND ((CM.Id=@rClassId) OR (@rClassId = 0))
		AND ((DM.Id=@rDivisionId) OR (@rDivisionId = 0))
		AND ((SM.StatusId=@rStatusId) OR (@rStatusId = 0))
END
GO
PRINT N'Altering [sAlert].[pVisitorGatePassGet]...';


GO
 ALTER PROCEDURE [sAlert].[pVisitorGatePassGet]
	
		 @rVisitorLogId int,
	 	 @rOrgId int,
	 	 @rTemplate nvarchar(max),
		 @rSubject nvarchar(100)='',
		 @rIsOutEntry bit = 0
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	IF(@rIsOutEntry=0)
	BEGIN
		SELECT 
				VL.Id,
				VL.VisitorName,
				EC.MobileNumber AS MobileNo,
				VL.VisitorContactNumber AS ContactNumber,
				EC.CompanyEmail AS EmailId,
				replace (
				replace (
				replace(@rTemplate,'[VisitorName]',VL.VisitorName),
				'[NoOfVisitors]',VL.NoOfVisitor),
				'[PurposeOfVisit]',VL.PurposeOfVisit)
				AS MsgText,
				@rSubject AS Subject,
				95 AS ReceiverSourceTypeId,
				EM.Id AS ReceiverSourceId,
				EM.Id AS SourceId,
				95 AS SourceTypeId,
				OM.OrgGroupId,
				VL.OrgId,
				VL.NoOfVisitor AS NoOfVisitors,
				VL.PurposeOfVisit AS PurposeOfVisit,
				ISNULL(EC.WhatsAppContactNo,'') AS WhatsAppContactNo
		FROM 
			sFrontOffice.tVisitorLog VL
			LEFT JOIN [sHrm].[tEmployeeMaster] EM ON VL.WhomToMeet=EM.Id
			LEFT JOIN [sHrm].[tEmployeeCommunications] EC ON EM.Id=EC.EmployeeId
			LEFT JOIN sSetup.tOrganizationMaster OM ON VL.OrgId = OM.Id	
		WHERE
			((VL.OrgId=@rOrgId) OR (@rOrgId=0))
			AND VL.Id = @rVisitorLogId
	END

	ELSE

	BEGIN
		SELECT 
				VL.Id,
				VL.VisitorName,
				VL.VisitorContactNumber AS MobileNo,
				'' AS EmailId,
				replace (
				replace (
				replace(@rTemplate,'[VisitorName]',VL.VisitorName),
				'[NoOfVisitors]',VL.NoOfVisitor),
				'[PurposeOfVisit]',VL.PurposeOfVisit)
				AS MsgText,
				@rSubject AS Subject,
				0 AS ReceiverSourceTypeId,
				0 AS ReceiverSourceId,
				0 AS SourceId,
				0 AS SourceTypeId,
				OM.OrgGroupId,
				VL.OrgId,
				VL.NoOfVisitor AS NoOfVisitors,
				VL.PurposeOfVisit AS PurposeOfVisit,
				ISNULL(VL.VisitorContactNumber,'') AS WhatsAppContactNo
		FROM 
			sFrontOffice.tVisitorLog VL
			--To Send Alert on Visitor Contact No
			--LEFT JOIN [sHrm].[tEmployeeMaster] EM ON VL.WhomToMeet=EM.Id
			--LEFT JOIN [sHrm].[tEmployeeCommunications] EC ON EM.Id=EC.EmployeeId
			LEFT JOIN sSetup.tOrganizationMaster OM ON VL.OrgId = OM.Id	
		WHERE
			((VL.OrgId=@rOrgId) OR (@rOrgId=0))
			AND VL.Id = @rVisitorLogId
		END
END
GO
PRINT N'Altering [sAssessment].[pRptStudentGradeAnalysisSubjectWise]...';


GO
ALTER PROCEDURE [sAssessment].[pRptStudentGradeAnalysisSubjectWise]
	 
	 @rClassId int=0,
	 @rDivisionId int=0,
	 @rExamSubGroupId int=0,
	 @rBuilder1 nvarchar(MAX)='',
	 @rBatchId int,
	 @rClass nvarchar(MAX)='',
	 @rStatusId int = 0,
	 @rClassStatusId int=0,
	 @rGroupId int=0

	 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
   SELECT 
		COUNT(RD.id),
		@rBuilder1 AS Builder1,
		RD.SubjectGrade,
		SM.Title AS SubjectName,
		CS.IsOptional ,
		DM.Id AS DivisionId,
		Dm.DisplayTitle as DivisionName,
		Cm.Id AS ClassId,
		CM.DisplayTitle AS ClassName,
		ISNULL(GD.MarkRange,'') AS MarkRange,
		SM.OrderIndex AS SubjectOrderIndex,
		GM.Title AS Gender,
		GM.Id AS GenderId,
		GD.GradeSetId,
		GD.Minimumscore,

		CASE WHEN @rGroupId=1 THEN DM.Id 
				WHEN @rGroupId=2 THEN CM.Id END AS GroupId,
				
		CASE WHEN @rGroupId=1 THEN CM.DisplayTitle+'/'+DM.DisplayTitle
				WHEN @rGroupId=2 THEN CM.DisplayTitle END AS GroupName,
				
		CASE WHEN @rGroupId=1 THEN Dm.OrderIndex
				WHEN @rGroupId=2  THEN Cm.OrderIndex END AS OrderIndex,
				
		CASE WHEN @rGroupId=1 THEN 'Class/Division'
				WHEN @rGroupId=2  THEN 'Class' END AS GroupTitle,
		@rBatchId AS BatchId
				
  FROM 
		sAssessment.tResultDetail  RD
		Inner join sAssessment.tResult R ON R.Id=Rd.ResultId
		INNER JOIN sStudent.tAcademic A ON R.AcademicId = A.Id 
		INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
		INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId
		INNER JOIN sAssessment.tExamDetail ED ON ED.ID=RD.ExamDetailId
		INNER JOIN sAssessment.tExamMaster EM ON EM.Id=ED.ExamId
		INNER JOIN  sSetup.tClassSubject CS ON CS.Id=ED.ClassSubjectId 
		INNER JOIN sSetup.tSubjectMaster SM ON SM.Id=CS.SubjectId 
		INNER JOIN sStudent.tStudentOther SO ON SO.StudentId = A.StudentId 	
		INNER JOIN sStudent.tStudentMaster STM ON STM.Id = A.StudentId 	
		INNER JOIN sCommon.tGenderMaster GM ON GM.Id=SO.GenderId
		LEFT JOIN  sAssessment.tGradeSetDetail GD ON
		GD.GradeSetId = ED.GradeSetId AND RD.SubjectGrade = GD.GradeCode
   WHERE 
	    EM.ExamSubGroupId=@rExamSubGroupId
		and EM.BatchId=@rBatchId
		and ((CM.Id=@rClassId)  OR (@rClassId=0))
		AND ((DM.ID=@rDivisionId) OR (@rDivisionId=0))
		AND (ISNULL(Cm.Id,0) IN( SELECT item FROM  sCommon.fnsplit(@rClass,','))OR @rClass='')
	    and((STM.StatusId=@rStatusId) or (@rStatusId=0))
		AND ((A.ClassStatusId =  @rClassStatusId) OR (@rClassStatusId=0))
   GROUP BY 
		RD.SubjectGrade,GD.MarkRange,SM.Title,CS.IsOptional ,DM.Id,DM.DisplayTitle,CM.Id,CM.DisplayTitle,SM.OrderIndex,GM.Title,GM.Id,GD.GradeSetId,GD.Minimumscore
		,Dm.OrderIndex,Cm.OrderIndex
END
GO
PRINT N'Altering [sAssetManagement].[pImportAssetAdd]...';


GO
ALTER PROCEDURE [sAssetManagement].[pImportAssetAdd] 
	@rOrgGroupId int,
	@rXmldoc [nvarchar](max),
	@rCustomFieldXML nvarchar(max),
	@rCreatedUserId bigint,
	@rOrgId int,
	@rStoreLocationId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @lCount INT
Declare @lResult int, @lresultCode nvarchar(100)
DECLARE @lInterrorCode INT,@intDoc INT
DECLARE @lInterrorCode1 INT,@intDoc1 INT

Declare 
	@lCode nvarchar(20),
	@lName nvarchar(100) = '',
	@lAssetCategory nvarchar(50) = '',
	@lModelNumber nvarchar(50) = '',
	@lSpecification nvarchar(300) = '',
	@lAccessories nvarchar(300) = '',
	@lIsMaintenance bit = null,
	@lServiceEntry date =  Null,
	@lMaitenancePeriod smallint = null,
	@lAlertPeriod smallint = null,
	@lCurrentDepartment nvarchar(50) ='',
	@lCurrentLocation nvarchar(50) ='',
	@lUsageStatus nvarchar(50) ='',
	@lLastServiceCost decimal(22,6) = null,
	@lTotalServiceCost decimal(22,6)  = null,
	@lLastMaintenanceDate datetime = null,
	@lNextMaintenanceDate datetime = null,
	@lIsCheckRequired bit = null,
	@lCheckFrequency nvarchar(50) = '',
	@lCheckList nvarchar(300) = null,
	@lCheckedLastDate date = null,
	@lCheckedByUserId bigint = null,
	@lCheckedStatus nvarchar(300) =  null,
	@lCheckedRemark nvarchar(200) = null,
	@lIsRenewRequired bit = null,
	@lRenewLastDate date = null,
	@lRenewNextDate date = null,
	@lRenewRemark nvarchar(300) =  null,
	@lRenewCheckList nvarchar(300) = null,
	@lRenewAlertBeforeDays smallint = null,
	@lAssetCategoryId bigint = 0,
	@lCurrentDepartmentId bigint = 0,
	@lCurrentLocationId int = 0,
	@lUsageStatusId bigint = 0,
	@lCheckFrequencyId bigint = 0,
	@lCheckedStatusId bigint = 0,
	@lGoodsReceiptNumber nvarchar (20) = '',
	@lGoodsReceiptDate date = null,
	@lQuantity decimal = 1,
	@lNumber Nvarchar(20) = '',
	--@lAssetCode nvarchar(50) = '',
	@lWarrantyDate date = null,
	@lUniqueId INT=0
	
	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXmlDoc
	EXECUTE @lInterrorCode1 = sp_xml_preparedocument @intDoc1 OUTPUT, @rCustomFieldXML

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

	CREATE TABLE #tempTable  
	(
			Id bigint identity,
			Name nvarchar(100),
			AssetCategory nvarchar(50),
			ModelNumber nvarchar(50),
			Quantity decimal null,
			Specification nvarchar(300),
			Accessories nvarchar(300),
			IsMaintenance bit null,
			ServiceEntry date  Null,
			MaitenancePeriod smallint null,
			AlertPeriod smallint null,
			CurrentDepartment nvarchar(50),
			CurrentLocation nvarchar(50),
			UsageStatus nvarchar(50),
			LastServiceCost decimal(22,6) null,
			TotalServiceCost decimal(22,6) null,
			LastMaintenanceDate datetime Default null,
			NextMaintenanceDate datetime Default null,
			IsCheckRequired bit null,
			CheckFrequency nvarchar(50),
			CheckList nvarchar(300) null,
			CheckedLastDate date Default null,
			IsRenewRequired bit null,
			RenewLastDate date Default null,
			RenewNextDate date Default null,
			RenewRemark nvarchar(300) null,
			RenewCheckList nvarchar(300) null,
			RenewAlertBeforeDays smallint null,
			--AssetCode nvarchar(50) null,
			WarrantyDate date null,
			UniqueId Int
	)
	print('Table Created')
	INSERT INTO #tempTable
	(
			Name,
			AssetCategory,
			ModelNumber,
			Quantity,
			Specification,
			Accessories,
			IsMaintenance,
			ServiceEntry,
			MaitenancePeriod,
			AlertPeriod,
			CurrentDepartment,
			CurrentLocation,
			UsageStatus,
			LastServiceCost,
			TotalServiceCost,
			LastMaintenanceDate,
			NextMaintenanceDate,
			IsCheckRequired,
			CheckFrequency,
			CheckList,
			CheckedLastDate,
			IsRenewRequired,
			RenewLastDate,
			RenewNextDate,
			RenewRemark,
			RenewCheckList,
			RenewAlertBeforeDays,
			--AssetCode,
			WarrantyDate,
			UniqueId
	)

	SELECT *
			FROM OPENXML (@intDoc, '/Table/ImportAsset', 1)
	WITH
	(						
			Name nvarchar(100) '@Name',
			AssetCategory nvarchar(50) '@AssetCategory',
			ModelNumber nvarchar(50) '@ModelNumber',
			Quantity decimal '@Quantity',
			Specification nvarchar(300) '@Specification',
			Accessories nvarchar(300) '@Accessories',
			IsMaintenance bit '@IsMaintenance',
			ServiceEntry date '@ServiceEntry',
			MaitenancePeriod smallint '@MaintenancePeriod',
			AlertPeriod smallint '@AlertPeriod',
			CurrentDepartment nvarchar(50)'@CurrentDepartment',
			CurrentLocation nvarchar(50)'@CurrentLocation',
			UsageStatus nvarchar(50)'@UsageStatus',
			LastServiceCost decimal(22,6) '@LastServiceCost',
			TotalServiceCost decimal(22,6) '@TotalServiceCost',
			LastMaintenanceDate datetime '@LastMaintenanceDate',
			NextMaintenanceDate datetime '@NextMaintenanceDate',
			IsCheckRequired bit '@IsCheckRequired',
			CheckFrequency nvarchar(50)'@CheckFrequency',
			CheckList nvarchar(300) '@CheckList',
			CheckedLastDate date '@CheckedLastDate',
			IsRenewRequired bit '@IsRenewRequired',
			RenewLastDate date '@RenewLastDate',
			RenewNextDate date '@RenewNextDate',
			RenewRemark nvarchar(300) '@RenewRemark',
			RenewCheckList nvarchar(300) '@RenewCheckList',
			RenewAlertBeforeDays smallint '@RenewAlertBeforeDays',
			--AssetCode nvarchar(50) '@AssetCode',
			WarrantyDate date '@WarrantyDate',
			UniqueId INT '@UniqueId'
	) A 
	

	print('Master Insert')

	CREATE TABLE #tempTableCustomField
	(
		Id bigint identity,
		UniqueId Int,
		FieldName nvarchar(500),
		FieldData nvarchar(500)
		--AssetId bigint
	)
	print('Created')
	INSERT INTO #tempTableCustomField
	(
		UniqueId,
		FieldName,
		FieldData
		--AssetId
	)

	SELECT A.UniqueId, A.FieldName,A.FieldData
			FROM OPENXML (@intDoc1, '/Table/ImportCustomField', 1)
	WITH
	(
		UniqueId nvarchar(500) '@UniqueId',
		FieldName nvarchar(500) '@FieldName',
		FieldData nvarchar(500) '@FieldData'				
	) A 

	EXEC sp_xml_removedocument @intDoc

	EXEC sp_xml_removedocument @intDoc1

	print('Custom Add')

	DECLARE @lTotal int = 0, @lCustomUniqueId int = 0
	SELECT @lTotal = COUNT(Id) FROM #tempTable
	SELECT @lCount = 1
	
	WHILE @lCount <= @lTotal 
	BEGIN
	SELECT 
			@lName = Name,
			@lAssetCategory = AssetCategory,
			@lModelNumber = ModelNumber,
			@lQuantity = Quantity,
			@lSpecification = Specification,
			@lAccessories = Accessories,
			@lIsMaintenance = IsMaintenance,
			@lServiceEntry = ServiceEntry,
			@lMaitenancePeriod = MaitenancePeriod,
			@lAlertPeriod = AlertPeriod,
			@lCurrentDepartment = CurrentDepartment,
			@lCurrentLocation = CurrentLocation,
			@lUsageStatus = UsageStatus,
			@lLastServiceCost = LastServiceCost,
			@lTotalServiceCost = TotalServiceCost,
			@lLastMaintenanceDate = LastMaintenanceDate,
			@lNextMaintenanceDate = NextMaintenanceDate,
			@lIsCheckRequired = IsCheckRequired,
			@lCheckFrequency = CheckFrequency,
			@lCheckList = CheckList,
			@lCheckedLastDate = CheckedLastDate,
			@lIsRenewRequired = IsRenewRequired,
			@lRenewLastDate = RenewLastDate,
			@lRenewNextDate = RenewNextDate,
			@lRenewRemark = RenewRemark,
			@lRenewCheckList =RenewCheckList,
			@lRenewAlertBeforeDays = RenewAlertBeforeDays,
			--@lAssetCode = AssetCode,
			@lWarrantyDate = WarrantyDate
			FROM #tempTable WHERE Id = @lCount

			SET @lCustomUniqueId = (Select UniqueId from #tempTable WHERE Id = @lCount)

			SET @lAssetCategoryId = '' SET @lCurrentDepartmentId = '' SET @lCurrentLocationId = 0
			SET @lUsageStatusId = ''  SET @lCheckFrequencyId =''     SET @lCheckedStatusId = ''
			SET @lGoodsReceiptNumber = '' SET @lGoodsReceiptDate = ''

			SELECT @lAssetCategoryId = Id FROM sFixedAsset.tCategoryMaster WHERE  Title = @lAssetCategory 
			SELECT @lCurrentDepartmentId = Id FROM sSetup.tDepartmentMaster WHERE  Title = @lCurrentDepartment AND OrgId = @rOrgId
			SELECT @lCurrentLocationId = Id FROM sFixedAsset.tLocationMaster WHERE  Title = @lCurrentLocation AND OrgId = @rOrgId
			--SELECT @lUsageStatusId = Id FROM sFixedAsset.tCategoryMaster WHERE  Title = @lAssetCategory
			SELECT @lCheckFrequencyId = Id FROM sCommon.tCheckFrequencyMaster WHERE  Title = @lCheckFrequency
			--SELECT @lCheckedStatusId = Id FROM sCommon.tStatusMaster WHERE  Title = @lCheckedStatus
			IF(@lUsageStatus = 'Spare')
			BEGIN
				SET @lUsageStatusId = '121'
				SET @lGoodsReceiptNumber = 'GRN01'
			END
			IF(@lUsageStatus = 'Deployed')
			BEGIN
				SET @lUsageStatusId = '122'
			END
			IF(@lUsageStatus = 'Maintenance')
			BEGIN
				SET @lUsageStatusId = '123'
			END
			IF(@lUsageStatus = 'Retired')
			BEGIN
				SET @lUsageStatusId = '124'
			END
			IF(@lUsageStatus = 'Removed')
			BEGIN
				SET @lUsageStatusId = '125'
			END
			IF(LEN(@lCheckList) = 0)
			BEGIN
				SET @lCheckList = Null
			END
			IF(@lCheckedLastDate = '1900-01-01')
			BEGIN
				SET @lCheckedLastDate = Null
			END
			IF(@lRenewLastDate = '1900-01-01')
			BEGIN
				SET @lRenewLastDate = Null
			END
			IF(@lRenewNextDate = '1900-01-01')
			BEGIN
				SET @lRenewNextDate = Null
			END
			IF(LEN(@lRenewRemark) = 0)
			BEGIN
				SET @lRenewRemark = Null
			END
			IF(LEN(@lRenewCheckList) = 0)
			BEGIN
				SET @lRenewCheckList = Null
			END
			IF(@lRenewAlertBeforeDays = 0)
			BEGIN
				SET @lRenewAlertBeforeDays = Null
			END
			IF(@lWarrantyDate = '1900-01-01')
			BEGIN
				SET @lWarrantyDate = Null
			END

			IF(@lQuantity=0)
			BEGIN
				SET @lQuantity=1
			END
			SET @lGoodsReceiptDate =   GETDATE();
			
			DECLARE @lNextNumber INT
			DECLARE @lTotalRecords Int
			
			DECLARE @loNumber Nvarchar(100)
			
		WHILE @lQuantity > 0
		BEGIN

		print @lQuantity
			EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sAssetManagement.tAssetMaster', 0, 1, 1,@outNextNumber = @lNextNumber output

			EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgId, @rPrefixFunctionalityId = 2011,@rStoreLocationId = @rStoreLocationId, @rNextNumber  = @loNumber OUTPUT

			SELECT @lNumber = ISNULL(max(convert(int,Code)),0) FROM sAssetManagement.tAssetMaster WHERE StoreLocationId = @rStoreLocationId
			SET @lNumber = @lNumber + 1

			INSERT INTO sAssetManagement.tAssetMaster
			(
				Id,
				Code,
				Title,
				Specification,
				Accessories,
				LocationId,
				StatusId,
				VendorId,
				GoodsReceiptNumber,
				GoodsReceiptDate,
				IsInstallationRequired,
				CategoryId,
				DepreciationUptoDate,
				IsFullyDepreciated,
				StandardPrice,
				MinStockRequired,
				MaintenancePeriod,
				IsAmc,
				MaintenanceAlertPeriod,
				AmcVendorId,
				UsageStatusId,
				LastServiceCost,
				TotalServiceCost,
				LastDownTime,
				TotalDownTime,
				NextMaintenanceDate,
				ModelNumber,
				IsCritical,
				IsObsolete,
				CreatedUserId,
				CreatedDateTime,
				UpdatedUserId,
				UpdatedDateTime,
				DepartmentID,
				ServiceEntry,
				OrgId,
				StoreLocationId,
			
				IsCheckRequired,
				CheckFrequencyId,	
				CheckList,	
				CheckedLastDate,	
				IsRenewRequired,
				RenewLastDate,
				RenewNextDate,
				RenewRemark,
				RenewCheckList,
				RenewAlertBeforeDays,
				IsMaintenanceRequired,
				OrgGroupId,
				WarrantyDate,
				AssetCode
		
			)
		VALUES
			(
				@lNextNumber,
				@lNumber,
				@lName,
				@lSpecification,
				@lAccessories,
				@lCurrentLocationId,
				@lUsageStatusId,
				0,
				@lGoodsReceiptNumber,
				@lGoodsReceiptDate,
				0,
				@lAssetCategoryId,
				'1900-01-01',
				1,
				0,
				0,
				@lMaitenancePeriod,
				0,
				@lAlertPeriod,
				0,
				@lUsageStatusId,
				@lLastServiceCost,
				@lTotalServiceCost,
				0,
				0,
				--@lNextMaintenanceDate,
				DATEADD(dd, @lMaitenancePeriod, sCommon.fGetDate()),
				@lModelNumber,
				0,
				0,
				@rCreatedUserId,
				sCommon.fGetDate(),
				@rCreatedUserId,
				sCommon.fGetDate(),
				@lCurrentDepartmentId,
				@lServiceEntry,
				@rOrgId,
				0,

				@lIsCheckRequired,
				@lCheckFrequencyId,	
				@lCheckList,	
				@lCheckedLastDate,	
				@lIsRenewRequired,
				@lRenewLastDate,
				@lRenewNextDate,
				@lRenewRemark,
				@lRenewCheckList,
				@lRenewAlertBeforeDays,
				@lIsMaintenance,
				@rOrgGroupId,
				@lWarrantyDate,
				@loNumber
			)

			--Execute sAssetManagement.pImportAssetCustomFieldAdd @rCustomFieldXML, @lNextNumber, @rCreatedUserId, @lCustomUniqueId
			--UPDATE #tempTableCustomField SET AssetId = @lNextNumber WHERE UniqueId = @lCustomUniqueId

			DECLARE @lCount1 int,@lId1 int
			SELECT @lCount1 = COUNT(Id) FROM #tempTableCustomField WHERE UniqueId = @lCustomUniqueId
			SELECT Top 1 @lId1 = Id FROM #tempTableCustomField WHERE UniqueId = @lCustomUniqueId

			WHILE @lId1 <= @lCount1 
			BEGIN
				DECLARE @lField1 nvarchar(200), @lCustomFieldId int, @lFieldData1 nvarchar(200)
				
				Select @lField1 = FieldName from #tempTableCustomField where Id = @lId1

				Select @lCustomFieldId = Id from sAssetManagement.tAssetCustomFieldMaster where FieldTitle = @lField1
				
				Select @lFieldData1 = FieldData From #tempTableCustomField where Id = @lId1
				
				INSERT INTO [sAssetManagement].[tAssetCustomFieldData]
				(
				    
					AssetId,
					AssetCustomFieldId,
					FieldData,
					CreatedUserId,
					CreatedDateTime,
					UpdatedUserId,
					UpdatedDateTime
				)
				Values
				(
					 @lNextNumber
					,@lCustomFieldId
					,@lFieldData1
					,@rCreatedUserId
					,sCommon.fGetDate()
					,@rCreatedUserId
					,sCommon.fGetDate()
				)		
				SET @lId1=@lId1+1
			END

			SET @lNextNumber = null
			SET @lQuantity = @lQuantity - 1
		END

		
			--EXECUTE [sIp].[pStockItemPlanningAdd] 0,@lNextNumber,0,0,0,0,null,null,@rStoreLocationId,@rOrgGroupId
  	  --EXECUTE sProcurement.pStockBatchAdd  @rOrgGroupId,@rStoreLocationId,@lNextNumber,null,null,0,@rUserId ,0 
		SET @lResult = 1
		SET @lCount = @lCount + 1;
		SET @lQuantity = ISNULL(@lQuantity, 1)
	END
	
	Select @lResult AS Result
END
GO
PRINT N'Altering [sCommunication].[pQueryListGet]...';


GO
ALTER PROCEDURE [sCommunication].[pQueryListGet]
	 
	 
	  @rQueryCategoryId int = 0,
	 --@rTitle nvarchar(100),
	 @rOrgGroupBatchId int,
	 --@rOrgId int,
	 @rOrgGroupId int=0,
	 @rId bigint,
	 @rCreatedUserId bigint=0,
	 @rSyncedQueryId bigint = 0,
	 @rStatusId int = 0,
	 @rCreatedFromDate date=NULL,
	 @rCreatedToDate date=NULL, 
	 @rFromResolutionDate date=NULL,
	 @rToResolutionDate date=NULL,
	 @rFromSourceTypeId smallint=0,  
	 @rTitle nvarchar(100)='',  
	 @rNumber smallint=0,
	 @rQueryStatus varchar(25)=NULL ,
	 @rAssignedToEmployeeId bigint=0,
	 @rSourceId bigint=0,
	 @FromPage varchar(50)='',
	 @rAssignedToId smallint = 0,
	 @rUserSourceTypeId int= 0 
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			
BEGIN 
	  BEGIN
			SET @rTitle = sCommon.fSearchStringGet(@rTitle)
	  END
	  DECLARE @lAcademicId bigint,@lStudentId bigint,@lClassId int,@lDivisionId int,@lOrgId int,@lDepartmentId int,@lEmployeeId bigint
	  IF @rUserSourceTypeId   = 95 -- For Employee Login
	  BEGIN
		SELECT @lDepartmentId=EJD.OrganizationDepartmentId,@lOrgId = EM.OrgId,@lEmployeeId = EM.Id FROM sHrm.tEmployeeMaster EM INNER JOIN sHrm.tEmployeeJobDetail EJD
				 ON EJD.EmployeeId = EM.Id WHERE EM.Id=@rSourceId


		SELECT 
			QM.Id AS QueryId,
			QCM.Id AS QueryCategoryId,
			--VQCM.OrgId As OrgId,
			ISNULL(QCM.Title,'') AS QueryCategoryMaster,
			QM.Title,
			QM.Remark,
			QM.CreatedDateTime,
			QM.StatusId,
			ISNULL(sm.Title,'') AS StatusMaster,
			QM.IsAssigneeView,
			QM.IsUserView,
			QM.CreatedUserId AS QueryCreatedUserId,
			ISNULL(QM.SubjectId,0) AS SubjectId,
			ISNULL(SubM.Title,'') AS SubjectName,
			ISNULL(QM.Number,0) AS Number,
			ISNULL(QM.AssignedToEmployeeId,0) AS AssignedToEmployeeId,
			ISNULL(QM.AssignedToRemark,'') AS AssignedToRemark,
			ISNULL(EM.Code,'') AS AssignedToEmployeeCode,
			ISNULL(U.SourceTypeId,0) AS FromSourceTypeId,
			--ISNULL(QM.FromSourceId,0) AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT Code from sStudent.tStudentMaster SM where SM.Id=U.SourceId)
			   WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT Code from sHrm.tEmployeeMaster EM where EM.Id=U.SourceId AND ISNULL(EM.IsOtherUser,0) = 0)
			   ELSE ''
			END AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT Code from sStudent.tStudentMaster SM where SM.Id=U.SourceId)
			   WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT Code from sHrm.tEmployeeMaster EM where EM.Id=U.SourceId AND ISNULL(EM.IsOtherUser,0) = 0)
			   ELSE ''
			END AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT 
			                                                        DM.Title
			                                                   FROM 
			                                                        [sStudent].[vStudentAcademic] A 
			                                                         INNER JOIN sSetup.tBatchMaster BM On A.BatchId=BM.Id 
			                                                         INNER JOIN sSetup.tDivisionMaster DM On A.DivisionId=DM.Id 
			                                                   WHERE StudentId=U.SourceId and BM.OrgGroupBatchId=@rOrgGroupBatchId
			                                                   )
               WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT 
			                                                        DM.Title
			                                                   FROM 
			                                                        sHrm.tEmployeeJobDetail EJD
			                                                         INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id=EJD.OrganizationDepartmentId
			                                                   WHERE EmployeeId=U.SourceId
			                                                   )			                                                   
			  
			   ELSE ''
			END AS Code,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT 
			                                                        U.DisplayTitle
			                                                   )
               WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT 
			                                                        U.DisplayTitle
			                                                   )			                                                   
			  
			   ELSE ''
			END AS Name,
			ISNULL(QM.TargetResolutionDate,'') AS TargetResolutionDate  ,
			(CASE WHEN QM.StatusId=210 then  U.DisplayTitle ELSE '' END) AS UserName
			  
			
  FROM 
			sCommunication.tQuery QM
		INNER JOIN sCommunication.tQueryCategoryMaster QCM ON QM.QueryCategoryId=QCM.Id
		--INNER JOIN sCommunication.vOrgwiseQueryCategory VQCM ON QM.QueryCategoryId=VQCM.Id
        INNER JOIN sCommon.tStatusMaster SM ON SM.Id=QM.StatusId
        INNER JOIN sSecurity.tUser U ON QM.CreatedUserId = U.Id
        LEFT JOIN sHrm.tEmployeeMaster EM ON QM.AssignedToEmployeeId=EM.Id
        LEFT JOIN sSetup.tSubjectMaster SubM ON SubM.Id = QM.SubjectId
		INNER JOIN 
				(SELECT QCS.QueryCategoryId FROM sCommunication.tQueryCategoryScope QCS
				 WHERE (((QCS.SourceTypeId = 2021 AND QCS.SourceId =@lOrgId) OR
				(QCS.SourceTypeId = 2017 AND QCS.SourceId =@lDepartmentId) OR
				(QCS.SourceTypeId = 95 AND QCS.SourceId =@lEmployeeId )) AND ((QCS.AssigneeToId=3) OR (QCS.AssigneeToId IS NULL) OR (ISNULL(QCS.AssigneeToId,0)=0)))
				 ) D
				ON D.QueryCategoryId= QCM.Id
  WHERE 
			((QCM.Id = @rQueryCategoryId) OR (@rQueryCategoryId = 0))
        AND	(QM.OrgGroupBatchId=@rOrgGroupBatchId) 
        AND ((QM.Id=@rId) OR (@rId=0))
        --AND ((QM.CreatedUserId=@rCreatedUserId) OR (@rCreatedUserId=0))
        AND ( QM.Id > @rSyncedQueryId OR @rSyncedQueryId = 0)
        AND ((QM.StatusId=@rStatusId) OR (@rStatusId=0))
        AND ((convert(date,QM.CreatedDateTime)>=convert(date,@rCreatedFromDate))OR(ISNULL(convert(date,@rCreatedFromDate),'')=''))    
        AND ((convert(date,QM.CreatedDateTime)<=convert(date,@rCreatedToDate))OR(ISNULL(convert(date,@rCreatedToDate),'')=''))
        AND ((convert(date,QM.TargetResolutionDate)>=convert(date,@rFromResolutionDate))OR(ISNULL(convert(date,@rFromResolutionDate),'')=''))    
        AND ((convert(date,QM.TargetResolutionDate)<=convert(date,@rToResolutionDate))OR(ISNULL(convert(date,@rToResolutionDate),'')='')) 
        AND ((QM.FromSourceTypeId=@rFromSourceTypeId) OR (@rFromSourceTypeId=0))
        AND ((QM.Title like '%'+@rTitle+'%') OR (ISNULL(@rTitle,'')=''))
        AND ((QM.Number=@rNumber) OR (@rNumber=0))
        AND ((QM.StatusId=@rStatusId) OR (@rStatusId=0))
		AND ((QCM.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
		AND ISNULL(EM.IsOtherUser,0) = 0
		--AND ((ISNULL(VQCM.AssignedToId,1) <> @rAssignedToId) OR (@rAssignedToId = 0))
		AND QCM.AssignedToId IN (1,3) 
        ORDER BY QM.CreatedDateTime DESC

	  END
	  ELSE IF @rUserSourceTypeId   = 2003 -- For Student Login
	  BEGIN
		
		SELECT @lAcademicId = A.Id,@lStudentId = A.StudentId,@lClassId = A.ClassId, @lDivisionId = A.DivisionId, @lOrgId= A.OrgId, @lDepartmentId=CM.DepartmentId
		FROM sStudent.tAcademic A INNER JOIN sSetup.tBatchMaster BM
		ON A.BatchId = BM.Id 
		INNER JOIN sSetup.tClassMaster CM ON CM.Id = A.ClassId
		WHERE StudentId = @rSourceId AND BM.OrgGroupBatchId = @rOrgGroupBatchId

		SELECT 
			QM.Id AS QueryId,
			QCM.Id AS QueryCategoryId,
			--VQCM.OrgId As OrgId,
			ISNULL(QCM.Title,'') AS QueryCategoryMaster,
			QM.Title,
			QM.Remark,
			QM.CreatedDateTime,
			QM.StatusId,
			ISNULL(sm.Title,'') AS StatusMaster,
			QM.IsAssigneeView,
			QM.IsUserView,
			QM.CreatedUserId AS QueryCreatedUserId,
			ISNULL(QM.SubjectId,0) AS SubjectId,
			ISNULL(SubM.Title,'') AS SubjectName,
			ISNULL(QM.Number,0) AS Number,
			ISNULL(QM.AssignedToEmployeeId,0) AS AssignedToEmployeeId,
			ISNULL(QM.AssignedToRemark,'') AS AssignedToRemark,
			ISNULL(EM.Code,'') AS AssignedToEmployeeCode,
			ISNULL(U.SourceTypeId,0) AS FromSourceTypeId,
			--ISNULL(QM.FromSourceId,0) AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT Code from sStudent.tStudentMaster SM where SM.Id=U.SourceId)
			   WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT Code from sHrm.tEmployeeMaster EM where EM.Id=U.SourceId AND ISNULL(EM.IsOtherUser,0) = 0)
			   ELSE ''
			END AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT Code from sStudent.tStudentMaster SM where SM.Id=U.SourceId)
			   WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT Code from sHrm.tEmployeeMaster EM where EM.Id=U.SourceId AND ISNULL(EM.IsOtherUser,0) = 0)
			   ELSE ''
			END AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT 
			                                                        DM.Title
			                                                   FROM 
			                                                        [sStudent].[vStudentAcademic] A 
			                                                         INNER JOIN sSetup.tBatchMaster BM On A.BatchId=BM.Id 
			                                                         INNER JOIN sSetup.tDivisionMaster DM On A.DivisionId=DM.Id 
			                                                   WHERE StudentId=U.SourceId and BM.OrgGroupBatchId=@rOrgGroupBatchId
			                                                   )
               WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT 
			                                                        DM.Title
			                                                   FROM 
			                                                        sHrm.tEmployeeJobDetail EJD
			                                                         INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id=EJD.OrganizationDepartmentId
			                                                   WHERE EmployeeId=U.SourceId
			                                                   )			                                                   
			  
			   ELSE ''
			END AS Code,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT 
			                                                        U.DisplayTitle
			                                                   )
               WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT 
			                                                        U.DisplayTitle
			                                                   )			                                                   
			  
			   ELSE ''
			END AS Name,
			ISNULL(QM.TargetResolutionDate,'') AS TargetResolutionDate  ,
			(CASE WHEN QM.StatusId=210 then  U.DisplayTitle ELSE '' END) AS UserName
			  
			
  FROM 
			sCommunication.tQuery QM
		INNER JOIN sCommunication.tQueryCategoryMaster QCM ON QM.QueryCategoryId=QCM.Id
		--INNER JOIN sCommunication.vOrgwiseQueryCategory VQCM ON QM.QueryCategoryId=VQCM.Id
        INNER JOIN sCommon.tStatusMaster SM ON SM.Id=QM.StatusId
        INNER JOIN sSecurity.tUser U ON QM.CreatedUserId = U.Id
        LEFT JOIN sHrm.tEmployeeMaster EM ON QM.AssignedToEmployeeId=EM.Id
        LEFT JOIN sSetup.tSubjectMaster SubM ON SubM.Id = QM.SubjectId
		INNER JOIN (SELECT QCS.QueryCategoryId FROM sCommunication.tQueryCategoryScope QCS
            WHERE (((QCS.SourceTypeId = 2021 AND QCS.SourceId =@lOrgId) OR
            (QCS.SourceTypeId = 2017 AND QCS.SourceId =@lDepartmentId) OR
            (QCS.SourceTypeId = 2016 AND QCS.SourceId =@lClassId) OR
            (QCS.SourceTypeId = 2005 AND QCS.SourceId =@lDivisionId) OR
            (QCS.SourceTypeId = 2003 AND QCS.SourceId =@lStudentId) OR
            (QCS.SourceTypeId = 2001 AND QCS.SourceId =@lAcademicId) )
            )AND ((QCS.AssigneeToId=2) OR (QCS.AssigneeToId IS NULL) OR (ISNULL(QCS.AssigneeToId,0)=0))) D
			ON	D.QueryCategoryId= QCM.Id
  WHERE 
			((QCM.Id = @rQueryCategoryId) OR (@rQueryCategoryId = 0))
        AND	(QM.OrgGroupBatchId=@rOrgGroupBatchId) 
        AND ((QM.Id=@rId) OR (@rId=0))
        --AND ((QM.CreatedUserId=@rCreatedUserId) OR (@rCreatedUserId=0))
        AND ( QM.Id > @rSyncedQueryId OR @rSyncedQueryId = 0)
        AND ((QM.StatusId=@rStatusId) OR (@rStatusId=0))
        AND ((convert(date,QM.CreatedDateTime)>=convert(date,@rCreatedFromDate))OR(ISNULL(convert(date,@rCreatedFromDate),'')=''))    
        AND ((convert(date,QM.CreatedDateTime)<=convert(date,@rCreatedToDate))OR(ISNULL(convert(date,@rCreatedToDate),'')=''))
        AND ((convert(date,QM.TargetResolutionDate)>=convert(date,@rFromResolutionDate))OR(ISNULL(convert(date,@rFromResolutionDate),'')=''))    
        AND ((convert(date,QM.TargetResolutionDate)<=convert(date,@rToResolutionDate))OR(ISNULL(convert(date,@rToResolutionDate),'')='')) 
        AND ((QM.FromSourceTypeId=@rFromSourceTypeId) OR (@rFromSourceTypeId=0))
        AND ((QM.Title like '%'+@rTitle+'%') OR (ISNULL(@rTitle,'')=''))
        AND ((QM.Number=@rNumber) OR (@rNumber=0))
        AND ((QM.StatusId=@rStatusId) OR (@rStatusId=0))
		AND ((QCM.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
		AND ISNULL(EM.IsOtherUser,0) = 0
		--AND ((ISNULL(VQCM.AssignedToId,1) <> @rAssignedToId) OR (@rAssignedToId = 0))
		AND QCM.AssignedToId IN (1,2) 
        ORDER BY QM.CreatedDateTime DESC

	  END
	  ELSE --For Other Login
	  BEGIN
			SELECT 
			QM.Id AS QueryId,
			QCM.Id AS QueryCategoryId,
			--VQCM.OrgId As OrgId,
			ISNULL(QCM.Title,'') AS QueryCategoryMaster,
			QM.Title,
			QM.Remark,
			QM.CreatedDateTime,
			QM.StatusId,
			ISNULL(sm.Title,'') AS StatusMaster,
			QM.IsAssigneeView,
			QM.IsUserView,
			QM.CreatedUserId AS QueryCreatedUserId,
			ISNULL(QM.SubjectId,0) AS SubjectId,
			ISNULL(SubM.Title,'') AS SubjectName,
			ISNULL(QM.Number,0) AS Number,
			ISNULL(QM.AssignedToEmployeeId,0) AS AssignedToEmployeeId,
			ISNULL(QM.AssignedToRemark,'') AS AssignedToRemark,
			ISNULL(EM.Code,'') AS AssignedToEmployeeCode,
			ISNULL(U.SourceTypeId,0) AS FromSourceTypeId,
			--ISNULL(QM.FromSourceId,0) AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT Code from sStudent.tStudentMaster SM where SM.Id=U.SourceId)
			   WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT Code from sHrm.tEmployeeMaster EM where EM.Id=U.SourceId AND ISNULL(EM.IsOtherUser,0) = 0)
			   ELSE ''
			END AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT Code from sStudent.tStudentMaster SM where SM.Id=U.SourceId)
			   WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT Code from sHrm.tEmployeeMaster EM where EM.Id=U.SourceId AND ISNULL(EM.IsOtherUser,0) = 0)
			   ELSE ''
			END AS FromSourceId,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT 
			                                                        DM.Title
			                                                   FROM 
			                                                        [sStudent].[vStudentAcademic] A 
			                                                         INNER JOIN sSetup.tBatchMaster BM On A.BatchId=BM.Id 
			                                                         INNER JOIN sSetup.tDivisionMaster DM On A.DivisionId=DM.Id 
			                                                   WHERE StudentId=U.SourceId and BM.OrgGroupBatchId=@rOrgGroupBatchId
			                                                   )
               WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT 
			                                                        DM.Title
			                                                   FROM 
			                                                        sHrm.tEmployeeJobDetail EJD
			                                                         INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id=EJD.OrganizationDepartmentId
			                                                   WHERE EmployeeId=U.SourceId
			                                                   )			                                                   
			  
			   ELSE ''
			END AS Code,
			CASE 
			   WHEN ISNULL(U.SourceTypeId,0) = 2003 THEN (SELECT 
			                                                        U.DisplayTitle
			                                                   )
               WHEN ISNULL(U.SourceTypeId,0) = 95 THEN (SELECT 
			                                                        U.DisplayTitle
			                                                   )			                                                   
			  
			   ELSE ''
			END AS Name,
			ISNULL(QM.TargetResolutionDate,'') AS TargetResolutionDate  ,
			(CASE WHEN QM.StatusId=210 then  U.DisplayTitle ELSE '' END) AS UserName
			  
			
  FROM 
			sCommunication.tQuery QM
		INNER JOIN sCommunication.tQueryCategoryMaster QCM ON QM.QueryCategoryId=QCM.Id
		--INNER JOIN sCommunication.vOrgwiseQueryCategory VQCM ON QM.QueryCategoryId=VQCM.Id
        INNER JOIN sCommon.tStatusMaster SM ON SM.Id=QM.StatusId
        INNER JOIN sSecurity.tUser U ON QM.CreatedUserId = U.Id
        LEFT JOIN sHrm.tEmployeeMaster EM ON QM.AssignedToEmployeeId=EM.Id
        LEFT JOIN sSetup.tSubjectMaster SubM ON SubM.Id = QM.SubjectId
  WHERE 
			((QCM.Id = @rQueryCategoryId) OR (@rQueryCategoryId = 0))
        AND	(QM.OrgGroupBatchId=@rOrgGroupBatchId) 
        AND ((QM.Id=@rId) OR (@rId=0))
        --AND ((QM.CreatedUserId=@rCreatedUserId) OR (@rCreatedUserId=0))
        AND ( QM.Id > @rSyncedQueryId OR @rSyncedQueryId = 0)
        AND ((QM.StatusId=@rStatusId) OR (@rStatusId=0))
        AND ((convert(date,QM.CreatedDateTime)>=convert(date,@rCreatedFromDate))OR(ISNULL(convert(date,@rCreatedFromDate),'')=''))    
        AND ((convert(date,QM.CreatedDateTime)<=convert(date,@rCreatedToDate))OR(ISNULL(convert(date,@rCreatedToDate),'')=''))
        AND ((convert(date,QM.TargetResolutionDate)>=convert(date,@rFromResolutionDate))OR(ISNULL(convert(date,@rFromResolutionDate),'')=''))    
        AND ((convert(date,QM.TargetResolutionDate)<=convert(date,@rToResolutionDate))OR(ISNULL(convert(date,@rToResolutionDate),'')='')) 
        AND ((QM.FromSourceTypeId=@rFromSourceTypeId) OR (@rFromSourceTypeId=0))
        AND ((QM.Title like '%'+@rTitle+'%') OR (ISNULL(@rTitle,'')=''))
        AND ((QM.Number=@rNumber) OR (@rNumber=0))
        AND ((QM.StatusId=@rStatusId) OR (@rStatusId=0))
		AND ((QCM.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
		AND ISNULL(EM.IsOtherUser,0) = 0
		--AND ((ISNULL(QCM.AssignedToId,1) <> @rAssignedToId) OR (@rAssignedToId = 0))
        ORDER BY QM.CreatedDateTime DESC
	  END

        
END
GO
PRINT N'Altering [sFee].[pAccountMappingGet]...';


GO
ALTER PROCEDURE [sFee].[pAccountMappingGet]
				
		   @rBuilder1 nvarchar(max)='',
		   @rBuilder2 nvarchar(max)='', 
		   @rClassId int,
           @rAdmissionCategoryId int,
		   @rBatchId int,
		   @rOrgGroupId int,
		   @rReceiptBookId int,
		   @rPayTypeId int = 0,
		   @rFeeId int = 0,
		   @rFeeHeadId int = 0
           
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN 
	SELECT  
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			ISNULL(FS.Id,0)AS FeeSetupId,
			ISNULL(AM.FeeSetupId,0)AS AMFeeSetupId,
			ISNULL(FS.ClassId,0)AS ClassId,
			ISNULL(CM.Title,'') AS ClassName,
			ISNULL(FS.AdmissionCategoryId,0) AS AdmissionCategoryId,
			ISNULL(FS.BatchId,0)AS BatchId,
			ISNULL(FM.Id,0)AS FeeId,
			ISNULL(FM.Title,'') AS FeeTitle,
			ISNULL(FHM.Id,0) AS FeeHeadId,
			ISNULL(FHM.Title,'') AS FeeHeadName,
			ISNULL(FS.ReceiptBookId,0) AS ReceiptBookId,
			ISNULL(RBM.Title,'') AS ReceiptBookTitle,
			ISNULL(AYM.AccountId,0) AS AccountId,
			ISNULL(AM.CreditLedgerId,0) AS CreditLedgerId,
			ISNULL(AM.DebitLedgerId,0) AS DebitLedgerId,
			ISNULL(AM.AccountingYearId,0) AS AccountingYearId,
			ISNULL(AM.PayTypeId,0) AS AMPayTypeId,
			ISNULL(PTM.Id,0) AS PayTypeId,
			ISNULL(PTM.Title,'') AS PayTypeName
  FROM 
			sFee.tFeeMaster FM
		INNER JOIN sFee.tFeeSetup FS ON FM.Id=FS.FeeId 
		AND (FS.ClassId=@rClassId) AND (FS.AdmissionCategoryId=@rAdmissionCategoryId) AND (FS.BatchId=@rBatchId)
		INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId 
		INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=FS.ReceiptBookId AND (RBM.Id = @rReceiptBookId OR @rReceiptBookId=0)
		INNER JOIN sSetup.tClassMaster CM ON CM.Id=FS.ClassId		
		CROSS JOIN (SELECT PTM.Id,PTM.Title FROM sFinanceCommon.tPayTypeMaster PTM WHERE (PTM.Id = @rPayTypeId OR @rPayTypeId = 0)) PTM
		LEFT JOIN sFee.tAccountMapping AM ON AM.FeeSetupId=FS.Id AND AM.PayTypeId=PTM.Id
		LEFT JOIN sAccounting.tLedgerMaster CLM ON (CLM.Id=AM.CreditLedgerId)  AND CLM.AccountingYearId = AM.AccountingYearId
		LEFT JOIN sAccounting.tLedgerMaster DLM ON (DLM.Id=AM.DebitLedgerId) AND DLM.AccountingYearId = AM.AccountingYearId
		LEFT JOIN sAccounting.tAccountingYearMaster AYM ON AYM.Id=CLM.AccountingYearId AND AYM.Id=DLM.AccountingYearId
WHERE 
		FM.OrgGroupId=@rOrgGroupId	
		AND (FM.Id = @rFeeId OR @rFeeId=0)	
		AND (FHM.Id = @rFeeHeadId OR @rFeeHeadId = 0)
ORDER BY FM.OrderIndex,PTM.Id

END
GO
PRINT N'Altering [sFee].[pFeeAllocationHistoryGet]...';


GO
 
ALTER PROCEDURE [sFee].[pFeeAllocationHistoryGet]

	@rOrgId int,
	@rBatchId bigint,
	@rSourceId bigint,
	@rUserId bigint=0,
	@rFromDate date=NULL,
	@rToDate date=NULL,
	@rHistoryTypeId tinyint=null

	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	SELECT 
	   
	  (SM.FirstName + ' '+ SM.MiddleName + ' '+ SM.LastName) AS StudentName
	  ,ISNULL(SM.Code, '') AS StudentCode
	  ,FAH.SourceId
	  ,FM.Code AS FeeName
      ,FAH.VariableAmount
      ,FAH.DiscountAmount
      ,FAH.AdditionalAmount
      ,FAH.NotApplicableAmount
      ,FAH.Remark
      ,FAH.ReferenceTypeId
      ,FAH.ReferenceSourceId
      ,FAH.UserId
	  ,U.DisplayTitle AS UserName
      ,FAH.LogDateTime
      ,FAH.DiscountRemark
      ,FAH.AdditionalRemark
      ,FAH.NotApplicableRemark
	FROM sFee.tFeeAllocationHistory FAH
	INNER JOIN sStudent.tAcademic A ON FAH.SourceId=A.Id
	INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
	LEFT JOIN sFee.tFeeSetup FS ON FS.Id=FAH.FeeSetupId
	INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId
	INNER JOIN sSecurity.tUser U ON U.Id=FAH.UserId
  Where 
		((FAH.SourceId=@rSourceId )OR(@rSourceId=0))
		AND ((FAH.UserId=@rUserId) OR (@rUserId=0))
		AND SM.OrgId=@rOrgId
		AND A.BatchId=@rBatchId
		AND ((CAST(FAH.LogDateTime AS DATE) >= @rFromDate) OR (ISNULL(@rFromDate,'') = ''))
		AND ((CAST(FAH.LogDateTime AS DATE) <= @rToDate) OR (ISNULL(@rToDate,'') = ''))
		AND ((ISNULL(FAH.HistoryTypeId,1) = @rHistoryTypeId) OR (ISNULL(@rHistoryTypeId, 0) = 0))
	ORDER BY FAH.LogDateTime DESC
END
GO
PRINT N'Altering [sFee].[pOldFeewiseImportAdd]...';


GO
ALTER PROCEDURE [sFee].[pOldFeewiseImportAdd]
	
	@rOldOrgGroupBatchId int,
	@rNewOrgGroupBatchId int,
	@rOldBatchId int,
	@rNewBatchId int,
	@rOldAdmissionCategoryId int,
	@rNewAdmissionCategoryId int,
	@rOldReceiptBookId int,
	@rNewReceiptBookId int,
	@rOldFeeIds nvarchar(max),
	@rNewFeeId int,
	@rOrgId int,
	@rCreatedUserId bigint,
	@rIdNos nvarchar(max)=''
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY 

	DECLARE @lCount int
	-- Old Fee Import Temp Table
	CREATE TABLE #TempOldFeeImport
	(
		[AutoId] int identity,
		[StudentId] bigint NULL ,
		[AcademicId] bigint NULL,
		[PendingAmount] decimal(12,2) NULL
	)
	-- End Old Fee Import Temp Table
	
	
	INSERT INTO #TempOldFeeImport 
	SELECT 
		SA.StudentId,
		SA.AcademicId,
		SUM(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
  FROM 
  
			 sStudent.vStudentAcademic SA
		INNER JOIN 
		(
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName			 
			FROM sFee.tFeeSetup FS1
			INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.ClassId=FS1.ClassId AND FS1.BatchId = SA1.BatchId 
			AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
				AND ((SA1.AdmissionCategoryId=@rOldAdmissionCategoryId) OR (@rOldAdmissionCategoryId=0))
			INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
			INNER JOIN sSetup.tBatchMaster BM ON FS1.BatchId = BM.Id AND BM.OrgGroupBatchId = @rOldOrgGroupBatchId
			WHERE 
			    SA1.OrgGroupBatchId = @rOldOrgGroupBatchId
			    AND ((FS1.AdmissionCategoryId=@rOldAdmissionCategoryId) OR (@rOldAdmissionCategoryId=0))
			    AND ((FS1.ReceiptBookId=@rOldReceiptBookId) OR (@rOldReceiptBookId=0))
				AND (FS1.IsOptional=0)
				AND ((FS1.BatchId=@rOldBatchId) OR (@rOldBatchId=0))
				AND ((SA1.BatchId=@rOldBatchId) OR (@rOldBatchId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND FM.Id IN (SELECT cast(item as int) FROM  sCommon.fnsplit(@rOldFeeIds,','))
				AND (SA1.Code IN (SELECT item from sCommon.fnSplit(@rIdNos, ',')) OR ISNULL(@rIdNos,'')='')
				UNION ALL
				
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName 
				
			FROM sFee.tFeeSetup FS1
		INNER JOIN sFee.tFeeOptional FO
			ON FO.FeeSetupId=FS1.Id
	    INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
		INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=FO.SourceId AND SA1.BatchId = FS1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
			AND ((SA1.AdmissionCategoryId=@rOldAdmissionCategoryId) OR (@rOldAdmissionCategoryId=0))
		INNER JOIN sSetup.tBatchMaster BM ON FS1.BatchId = BM.Id AND BM.OrgGroupBatchId = @rOldOrgGroupBatchId
		WHERE
		        SA1.OrgGroupBatchId = @rOldOrgGroupBatchId
		        AND ((FS1.AdmissionCategoryId=@rOldAdmissionCategoryId) OR (@rOldAdmissionCategoryId=0))
		        AND ((FS1.ReceiptBookId=@rOldReceiptBookId) OR (@rOldReceiptBookId=0))
				AND (FS1.IsOptional=1)
				AND ((FS1.BatchId=@rOldBatchId) OR (@rOldBatchId=0))
				AND ((SA1.BatchId=@rOldBatchId) OR (@rOldBatchId=0))	
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND FM.Id IN (SELECT cast(item as int) FROM  sCommon.fnsplit(@rOldFeeIds,','))
				AND (SA1.Code IN (SELECT item from sCommon.fnSplit(@rIdNos, ',')) OR ISNULL(@rIdNos,'')='')
				
		) FS ON FS.AcademicId=SA.AcademicId
		LEFT JOIN sFee.tFeeAllocation FA
			ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
		LEFT JOIN
		(
		    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 
				INNER JOIN sFee.tFeeSetup FS ON RD.FeeSetupId = FS.Id
				INNER JOIN sFee.tFeeMaster FM ON FS.FeeId = FM.Id
				INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=R.SourceId				
				
			WHERE		
				(R.SourceTypeId = 2001)
				AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND (R.IsObsolete = 0) 
				AND ((SA1.OrgGroupBatchId = @rOldOrgGroupBatchId))
				AND (R.OrgGroupBatchId = @rOldOrgGroupBatchId)
				AND ((R.BatchId=@rOldBatchId) OR (@rOldBatchId=0))
				AND FM.Id IN (SELECT cast(item as int) FROM  sCommon.fnsplit(@rOldFeeIds,','))
				AND (SA1.Code IN (SELECT item from sCommon.fnSplit(@rIdNos, ',')) OR ISNULL(@rIdNos,'')='')
				GROUP BY FeeSetupId,R.SourceId
		) R ON R.FeeSetupId = FS.Id AND SA.AcademicId = R.SourceId
				 
	WHERE	
			SA.OrgGroupBatchId = @rOldOrgGroupBatchId
			AND SA.OrgId = @rOrgId 
			AND SA.BatchId=@rOldBatchId
			AND (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) > 0)
			AND (SA.Code IN (SELECT item from sCommon.fnSplit(@rIdNos, ',')) OR ISNULL(@rIdNos,'')='')
			GROUP BY SA.StudentId,SA.AcademicId
	
	SELECT @lCount = COUNT(AutoId) FROM #TempOldFeeImport
	
	IF EXISTS 
	(
		SELECT 1 FROM sFee.tReceipt R
		INNER JOIN sFee.tReceiptDetail RD ON R.Id = RD.ReceiptId
		INNER JOIN sStudent.vStudentAcademic SA ON R.SourceId = SA.AcademicId AND SA.BatchId = @rNewBatchId
		INNER JOIN sFee.tFeeSetup FS ON FS.Id = RD.FeeSetupId AND FS.ClassId = SA.ClassId AND FS.BatchId = @rNewBatchId
		AND FS.AdmissionCategoryId = @rNewAdmissionCategoryId AND FS.FeeId = @rNewFeeId 
		AND FS.ReceiptBookId = @rNewReceiptBookId
		AND (SA.Code IN (SELECT item from sCommon.fnSplit(@rIdNos, ',')) OR ISNULL(@rIdNos,'')='')
	)
	BEGIN
		SELECT -101 AS Result
		RETURN
	END
	
	BEGIN TRANSACTION
	
	---- Delete Fee Optional
	DELETE FO FROM sFee.tFeeOptional FO 
	INNER JOIN sStudent.tAcademic SA ON FO.SourceId = SA.Id AND SA.BatchId = @rNewBatchId
	INNER JOIN sFee.tFeeSetup FS ON FO.FeeSetupId = FS.Id AND SA.ClassId = FS.ClassId
	INNER JOIN sStudent.vStudentAcademic SA1 ON SA.Id = SA1.AcademicId AND SA1.BatchId = @rNewBatchId
	AND FS.BatchId = @rNewBatchId 
	AND FS.AdmissionCategoryId = @rNewAdmissionCategoryId 
	AND FS.FeeId = @rNewFeeId  
	AND FS.ReceiptBookId = @rNewReceiptBookId
	AND (SA1.Code IN (SELECT item from sCommon.fnSplit(@rIdNos, ',')) OR ISNULL(@rIdNos,'')='')
	INNER JOIN #TempOldFeeImport TOFI ON TOFI.AcademicId = FO.SourceId 

	-- Delete Fee Allocation
	DELETE FA FROM sFee.tFeeAllocation FA 
	INNER JOIN sStudent.tAcademic SA ON FA.SourceId = SA.Id AND SA.BatchId = @rNewBatchId
	INNER JOIN sFee.tFeeSetup FS ON FA.FeeSetupId = FS.Id AND SA.ClassId = FS.ClassId
	INNER JOIN sStudent.vStudentAcademic SA1 ON SA.Id = SA1.AcademicId AND SA1.BatchId = @rNewBatchId
	AND FS.BatchId = @rNewBatchId 
	AND FS.AdmissionCategoryId = @rNewAdmissionCategoryId 
	AND FS.FeeId = @rNewFeeId AND FA.FeeSetupId = FS.Id
	AND FS.ReceiptBookId = @rNewReceiptBookId
	AND (SA1.Code IN (SELECT item from sCommon.fnSplit(@rIdNos, ',')) OR ISNULL(@rIdNos,'')='')
	INNER JOIN #TempOldFeeImport TOFI ON TOFI.AcademicId = FA.SourceId

	WHILE (@lCount > 0)
	BEGIN
		DECLARE @lAcademicId bigint = NULL
		DECLARE @lStudentId bigint = NULL
		DECLARE @lClassId int = NULL
		DECLARE @lFeeSetupId int = NULL
		DECLARE @lPendingAmount decimal(12,2)
		
		SELECT @lStudentId = StudentId,@lPendingAmount=PendingAmount FROM #TempOldFeeImport WHERE AutoId = @lCount
		SELECT @lAcademicId = Id, @lClassId=ClassId FROM  sStudent.tAcademic SA WHERE StudentId = @lStudentId AND BatchId = @rNewBatchId
		SELECT @lFeeSetupId = Id FROM sFee.tFeeSetup WHERE  ClassId = @lClassId AND AdmissionCategoryId = @rNewAdmissionCategoryId AND BatchId=@rNewBatchId AND FeeId = @rNewFeeId AND ReceiptBookId = @rNewReceiptBookId
		
		IF @lAcademicId IS NOT NULL AND @lFeeSetupId IS NOT NULL AND @lClassId IS NOT NULL
		BEGIN
			-- Insert Fee Optional
			INSERT INTO sFee.tFeeOptional
			   (
			    FeeSetupId,
			    SourceTypeId,
			    SourceId,
			    BatchId
			   )
				VALUES
			   (
				@lFeeSetupId,
				2001,
				@lAcademicId,
				@rNewBatchId
			   )
			 
				-- Insert Fee Allocation
				INSERT INTO sFee.tFeeAllocation
			   (
			    SourceTypeId,
			    SourceId,
			    FeeSetupId,
			    VariableAmount,
			    DiscountAmount,
			    AdditionalAmount,
			    NotApplicableAmount,
			    Remark,
			    ReferenceTypeId,
			    ReferenceSourceId,
			    CreatedUserId,
			    CreatedDateTime,
			    UpdatedUserId,
			    UpdatedDateTime
			   )
				VALUES
			   (
				2001,
				@lAcademicId,
				@lFeeSetupId,
				@lPendingAmount,
				0,
				NULL,
				NULL,
				'Old Year Pending Fee',
				NULL,
				NULL,
				@rCreatedUserId,
				sCommon.fGetDate(),
				NULL,
				NULL
			   )
		END
		 
		SET @lCount = @lCount -1
	END
	 
	DROP TABLE #TempOldFeeImport
	SELECT 1 AS Result
	COMMIT TRANSACTION
END TRY

BEGIN CATCH 
DROP TABLE #TempOldFeeImport
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
END CATCH	
END
GO
PRINT N'Altering [sFee].[pRptFeeReceiptDetailsGet]...';


GO
ALTER PROCEDURE [sFee].[pRptFeeReceiptDetailsGet]
						
			@rReportId int=0,
			@rReceiptId bigint = 0,
			@rOrgGroupId int = 0,
			@rReceiptBookId int = 0,
			@rFromReceiptNo int=0,
			@rToReceiptNo int=0,
			@rOrgId int = 0,
			@rBatchId int = 0,
			@rReceiptDate Date='',
			@rClassId int = 0,
			@rOrgIds nvarchar(max) = '',
			@rReceiptPrintGroupId nvarchar(100)='',
			@rDivisionId int = 0
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	
BEGIN 	
	
	DECLARE @lOrgGroupId int = 0
	DECLARE @lAmountInWord nvarchar(500) = ''
	DECLARE @lReceiptAmount Decimal(12,2) = 0.0
	DECLARE @lCurrencyCode nvarchar(20) = ''
	DECLARE @lCurrencyTitle nvarchar(100) = ''
	Declare @lIDConfigValue nvarchar(50)='',@lOrgGroup_Id int=0
	Select @lOrgGroup_Id = OrgGroupId From  sSetup.tOrganizationMaster Where Id = @rOrgId
	 
	SELECT @lIDConfigValue =ItemValue From sConfig.tConfigurationSetup
	WHERE ConfigurationId = 236 AND SourceId = @rOrgGroupId
	
	IF(@rOrgGroupId=0)
	BEGIN
		SELECT @lOrgGroupId = OrgGroupId ,@lReceiptAmount = R.ReceiptAmount  FROM sFee.tReceipt R 
		INNER JOIN sSetup.tOrgGroupBatchMaster OB ON R.OrgGroupBatchId = OB.Id 
		WHERE R.Id = @rReceiptId

		SELECT  @lAmountInWord = sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(@lReceiptAmount,0.00),1,2,@lOrgGroupId)
		SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@lOrgGroupId) 
		SELECT  @lCurrencyTitle = [sFinanceCommon].[fDefaultCurrencyTitleGet](@lOrgGroupId) 
	END
	ELSE
	BEGIN
		SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@rOrgGroupId) 
	END

	IF(@rOrgGroupId=0) SET @rOrgGroupId=@lOrgGroupId

	SELECT 
			FRD.ReceiptId,
			FR.ReceiptNumber,
			FR.ReceiptDate,
			
			SA.AcademicId,
			--SA.FirstName+' '+SA.MiddleName+' '+SA.LastName AS Name,
			SA.Name AS Name,
			SA.Code,
			SA.RollNo,
			SA.ClassId,
			SA.DivisionId,
			SA.ClassName,
			SA.DivisionName,
			SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
			FM.FeeHeadId,
			FHM.Title AS FeeHead,
			FM.Id AS FeeId,
			FM.Title AS FeeName,
			FRD.Amount AS PaidAmount,
			PTM.Id AS PayTypeId,
			PTM.Title AS PayType,
			
			--changes by Jash on 06/11/2020--%
			CASE WHEN ISNULL(FR.ChequeNo,'') != '' THEN FR.ChequeNo ELSE CONVERT(nvarchar(20),ISNULL(FR.PaymentTransactionId,''))  END AS ChequeNo,
			--CASE WHEN ISNULL(FR.ChequeNo,'') != '' THEN FR.ChequeNo
			--	WHEN ISNULL(PT.BankTransactionId,'') = '' THEN  CONVERT(nvarchar(20),FR.PaymentTransactionId)
			--	ELSE CONVERT(nvarchar(20),FR.PaymentTransactionId) +' - '+ CONVERT(nvarchar(20),PT.BankTransactionId) END AS ChequeNo,
			--%


			--ISNULL(FR.ChequeNo,'') AS ChequeNo,
			ISNULL(FR.BankName,'') AS BankName,
			ISNULL(RBM.Address1,'') AS Address1,
			ISNULL(RBM.Address2,'') AS Address2,
			ISNULL(RBM.FullTitle,'') AS FullTitle,
			ISNULL(FR.TotalReceiptAmount,0.00) AS TotalReceiptAmount,
			ISNULL(FR.ReceiptAmount,0.00) AS ReceiptAmount,			
			
			(CASE WHEN @rOrgGroupId = 0 -- For Single Receipt
					THEN '' 
				  WHEN @rOrgGroupId != 0 -- For Bulk Receipt
					THEN sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(FR.ReceiptAmount,0.00),1,2,@rOrgGroupId)
			END) AS ReceiptAmountInWords,
			
			ISNULL(FR.DiscountAmount,0.00) AS DiscountAmount,
			FR.Remark,



			(CASE WHEN  ISNULL(FR.PaymentTransactionId,0) > 0 
			  THEN -- Navrachana
				'Online Generated'
				ELSE ISNULL(U.DisplayTitle,'') END) AS UserName,
			--ISNULL(U.DisplayTitle,'') AS UserName,
			OM.OrgLogo,
			RBM.Remark1,
			RBM.Remark2,			 
			ISNULL(RBM.IsOnPrePrinted,0) AS IsOnPrePrinted,
			ISNULL(RBM.IsSupressLogo,0) AS IsSupressLogo,
			ISNULL(RBM.IsDisplaySchoolName,0) AS IsDisplaySchoolName,
			RBM.ReportId,
			OM.Title AS OrganizationName,
			--(CASE  WHEN (RBM.ReportId = 20775 OR RBM.ReportId = 20777 OR RBM.ReportId = 20874 OR RBM.ReportId =20427 OR RBM.ReportId =20890 OR RBM.ReportId =20901 OR RBM.ReportId =20902) AND ISNULL(RBM.IsMonthDetailOnly,0) = 0  THEN 
			--	[sFee].[fFeeHeadMonthDetailGet](FR.ID,FHM.Id) -- Fee name in detail	
				  
			  
			(CASE WHEN  RBM.ReportId = 20576
			      THEN -- HM patel report
				  [sFee].[fReceiptMonthDetailGet](FR.ID,0)
			      WHEN RBM.ReportId = 30457 
			      THEN
				  [sFee].[fReceiptMonthDetailGet](FR.ID,1)		
			      WHEN ISNULL(RBM.IsMonthDetailOnly,0) = 1  AND ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 1
			      THEN				
				  [sFee].[fFeeHeadMonthNameGet](FR.ID,FHM.Id) -- Head wise Month name in detail
				  WHEN ISNULL(RBM.IsMonthDetailOnly,0) = 1  AND ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 0
			      THEN				
				  [sFee].[fFeeMonthNameGet](FR.ID,FM.Id) -- Fee wise Month name in detail
			ELSE '' END) 
			AS MonthDetail,    

			
			(CASE WHEN ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 1
			      THEN				
					 Stuff((SELECT  N', ' +  FS1.FeeDuration FROM 
					sFee.tReceipt FR1				
					INNER JOIN sFee.tReceiptDetail FRD1	ON FR1.Id=FRD1.ReceiptId AND FR1.SourceTypeId=2001				
					INNER JOIN sFee.tFeeSetup FS1 ON FRD1.FeeSetupId = FS1.Id
					INNER JOIN sFee.tFeeMaster FM1 ON FS1.FeeId=FM1.Id						
					INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id=FM1.FeeHeadId 
					INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId = FR1.SourceId
					Where FM1.FeeHeadId = FM.FeeHeadId AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
					--Where FM1.Id = FM.Id AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
					AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
					Order By FS1.StartDate
					FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'' )    -- Fee Head wise Fee Duration
				  WHEN ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 0
			      THEN
					FS.FeeDuration -- Fee wise Fee Duration
				 ELSE '' END) AS FeeDuration,
			
			--Stuff((SELECT  N', ' +  FS1.FeeDuration FROM 
			--sFee.tReceipt FR1				
			--INNER JOIN sFee.tReceiptDetail FRD1	ON FR1.Id=FRD1.ReceiptId AND FR1.SourceTypeId=2001				
			--INNER JOIN sFee.tFeeSetup FS1 ON FRD1.FeeSetupId = FS1.Id
			--INNER JOIN sFee.tFeeMaster FM1 ON FS1.FeeId=FM1.Id						
			--INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id=FM1.FeeHeadId 
			--INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId = FR1.SourceId
			----Where FM1.FeeHeadId = FM.FeeHeadId AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
			--Where FM1.Id = FM.Id AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
			--AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
			-- Order By FS.StartDate
			--FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'' ) AS FeeDuration,
			
			
			DM.Title AS DepartmentName,
			FM.OrderIndex AS FeeOrderIndex,
			FHM.OrderIndex AS FeeHeadOrderIndex,
			FR.IsObsolete AS IsCancelReceipt,
			@lCurrencyCode AS CurrencyCode,
			@lCurrencyTitle AS CurrencyTitle,
			BM.Title AS Batch,
			FR.ChequeDate AS ChequeDate,
			ISNULL(RBM.IsMonthDetailOnly,0) AS IsMonthDetailOnly,
	        ISNULL(RBM.IsFeeInvoiceHeadwise,0) AS IsFeeInvoiceHeadwise,
	        ISNULL(RBM.GroupTitle,'') AS GroupTitle,
	        ISNULL(RBM.IsSuppressFeeDetail,0) AS IsSuppressFeeDetail,
	        ISNULL(CGM.Title,'') AS CollectionGroupName,
	        ISNULL(FI.InvoiceNumber,0) AS InvoiceNumber,
	        SA.SUID,
	        CASE
				 WHEN RBM.IsOnPrePrinted=1       
				 THEN 2
				 ELSE  ISNULL(RBM.HeaderDisplayId,1)
				END AS HeaderDisplayId,
	        RBM.ReceiptBookHeader,
	        RBM.ServiceTaxPercentage,
	        ISNULL(FR.AmountAfterDiscount,0) AS AmountAfterDiscount,
	        (CASE WHEN  FR.CGSTPercentage IS NULL THEN RBM.CGSTPercentage ELSE FR.CGSTPercentage END) AS CGSTPercentage,
	        (CASE WHEN  FR.SGSTPercentage IS NULL THEN RBM.SGSTPercentage ELSE FR.SGSTPercentage END) AS SGSTPercentage,
	        ISNULL(FR.CGSTAmount,0) AS CGSTAmount,
	        ISNULL(FR.SGSTAmount,0) AS SGSTAmount,
	        ISNULL(OOM.GSTINNo,'') AS GSTINNo,
	        ISNULL(OOM.HSNSACCode,'') AS HSNSACCode,
	        @lIDConfigValue AS ItemValue ,
	        SF.FatherName,
	        SF.MotherName,
	        ISNULL(FA.DiscountAmount,0) AS ExemptionAmount,
			ISNULL(FA.Remark,'') AS ExemptionRemark,
			EI.EmployeeSign,
			ISNULL(SC.PresentCity,'') AS PresentCity,

			(ISNULL((CASE WHEN SC.PresentStreetOne IS NOT NULL AND SC.PresentStreetOne!='' THEN ''+ SC.PresentStreetOne END),'')+
			ISNULL((CASE WHEN SC.PresentStreetTwo IS NOT NULL AND SC.PresentStreetTwo!=''  THEN ', '+ SC.PresentStreetTwo END),'')+
			ISNULL((CASE WHEN SC.PresentStreetThree IS NOT NULL AND SC.PresentStreetThree!=''  THEN ', '+ SC.PresentStreetThree END),'')+
			ISNULL((CASE WHEN SC.PresentCity IS NOT NULL AND SC.PresentCity!=''  THEN ', ' + SC.PresentCity  END),'')+
			ISNULL((CASE WHEN SC.PresentState IS NOT NULL AND SC.PresentState!=''  THEN  ', '+ SC.PresentState END),'')+
			ISNULL((CASE WHEN SC.PresentZipCode IS NOT NULL AND SC.PresentZipCode!=''  THEN  ', '+ SC.PresentZipCode  END),''))
			 AS PresentAdress,
			--ISNULL(SC.PresentStreetOne +','+ SC.PresentStreetTwo +','+ SC.PresentStreetThree +','+ SC.PresentCity +','+ SC.PresentState +'-'+ SC.PresentZipCode ,'') AS PresentAdress,
			SC.ParentContactNo,
			
			
			ISNULL(RBM.IsDisplayPendingFeeAmount,0) AS IsDisplayPendingFeeAmount,
			CASE WHEN @rOrgGroupId = 1134 OR @rOrgGroupId = 1742 THEN
			(CASE WHEN  ISNULL(RBM.IsDisplayPendingFeeAmount,0) = 0 THEN 0 ELSE ISNULL([sFee].[fPendingFeeAmountReceiptBookwiseGet](FR.SourceId,RBM.Id),0) END)
			ELSE 
			(CASE WHEN  ISNULL(RBM.IsDisplayPendingFeeAmount,0) = 0 THEN 0 ELSE ISNULL([sFee].[fPendingFeeAmountReceiptBookwiseGet](FR.SourceId,0),0) END) END AS PendingFeeAmount,
			SA.FirstName,
			SA.LastName,
			ISNULL([sFee].[fFeeAmountSumGet](FR.SourceId),0) AS FeeAmount,
			ISNULL([sFee].[fFeeReceiptDiscountSumGet](FR.SourceId),0) AS ExemptionAmountSum,
			[sFee].[fTransportBusRouteGet](SA.StudentId,FR.OrgGroupBatchId) AS TransportBusRoute,
			ISNULL(RBM.BankAccountNumber,'') AS BankAccountNumber,
			ISNULL(FHM.Number,0) AS FeeHeadNumber,
			ISNULL(FM.Number,0) AS FeeNumber,
			ISNULL(RBM.DigitalSignature,0x) AS DigitalSignature,
			ISNULL(FR.CancelStatusRemark,'') AS CancelStatusRemark,
			FS.Amount AS FeeSetupAmount,
			FR.CreatedDateTime ,
			ISNULL(FR.PaymentTransactionId,0) As PaymentTransactionId,
			SA.MiddleName,
			CONVERT(char(5),FR.CreatedDateTime,108) AS CreatedTime
	FROM 
	
			sFee.tReceipt FR				
			INNER JOIN sFee.tReceiptDetail FRD ON FR.Id=FRD.ReceiptId AND FR.SourceTypeId=2001				
			INNER JOIN sFee.tFeeSetup FS ON FRD.FeeSetupId = FS.Id				
			INNER JOIN sFee.tFeeMaster FM ON FS.FeeId=FM.Id						
			INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
			INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = FR.SourceId
			INNER JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SA.StudentId
			INNER JOIn sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=FR.PayTypeId
			LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
			INNER JOIN sFee.tReceiptBookMaster RBM  ON RBM.Id=FR.ReceiptBookId
			LEFT JOIN sSecurity.tUser U ON U.Id=FR.CreatedUserId
			INNER JOIN sSetup.tDepartmentMaster DM ON SA.DepartmentId =DM.Id
			LEFT JOIN sSetup.tOrganizationMaster OM ON OM.Id=DM.OrgId
			LEFT JOIN sSetup.tOrganizationOtherDetail OOM ON OM.Id=OOM.OrgId	
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = FR.BatchId 
			LEFT JOIN sFee.tCollectionGroupMaster CGM ON CGM.Id=FR.CollectionGroupId 
			LEFT JOIN sFee.tFeeInvoice FI ON FI.Id=FR.FeeInvoiceId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SA.StudentId
			LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId = U.SourceId AND U.SourceTypeId=95
			LEFT JOIN sOp.tPaymentTransaction PT ON PT.Id = FR.PaymentTransactionId
	WHERE 
			((FR.Id=@rReceiptId) OR (@rReceiptId = 0))
			AND ((RBM.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId = 0))
			AND ((FR.ReceiptBookId = @rReceiptBookId) OR (@rReceiptBookId = 0))
			AND ((FR.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo = 0))
			AND ((FR.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo = 0))
			AND ((FR.BatchId = @rBatchId) OR (@rBatchId = 0))
			AND ((BM.OrgId = @rOrgId) OR (@rOrgId = 0))
			AND ((FR.ReceiptDate = @rReceiptDate) OR (ISNULL(@rReceiptDate,'') = ''))
			AND ((SA.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((SA.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
			AND (ISNULL(SA.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')			
			AND ((ISNULL(FR.ReceiptPrintGroupId,'')=@rReceiptPrintGroupId) OR (ISNULL(@rReceiptPrintGroupId,'') = ''))
    ORDER BY 
            FS.StartDate,FHM.OrderIndex,FM.OrderIndex ASC 
END

--BEGIN
--SELECT
-- CAST(0 AS bigint) AS ReceiptId,
-- 0	AS ReceiptNumber,
-- CAST('' AS date) AS ReceiptDate,
-- CAST(0 AS bigint) AS AcademicId,
-- ''	AS Name,
--''	AS Code,
-- 0	AS RollNo,
--0	AS ClassId,
--0	AS DivisionId,
--''	AS ClassName,
--''	AS DivisionName,
--''	AS ClassDivision,
--0	AS FeeHeadId,
--''	AS FeeHead,
--0	AS FeeId,
--''	AS FeeName,
--CAST(0.0 AS decimal(12,2)) AS PaidAmount,
--CAST(0 AS tinyint) AS PayTypeId,
--''	AS PayType,
--''	AS ChequeNo,	
--''	AS BankName,
--''	AS Address1,
--''	AS Address2,
--''	AS FullTitle,
--CAST(0.0 AS decimal(12,2)) AS TotalReceiptAmount,
--CAST(0.0 AS decimal(12,2)) AS ReceiptAmount,					
--''	AS ReceiptAmountInWords,		
--CAST(0.0 AS decimal(12,2)) AS DiscountAmount,
--''	AS Remark,
--''	AS UserName,		
--0x  AS OrgLogo,
--''	AS Remark1,
--''	AS Remark2,			 
--CAST(0 AS bit) AS IsOnPrePrinted,
--CAST(0 AS bit) AS IsSupressLogo,
--CAST(0 AS bit) AS IsDisplaySchoolName,
--0	AS ReportId,
--''	AS OrganizationName,		  
--''	AS MonthDetail,    			
--''	AS FeeDuration,		
--''	AS DepartmentName,
-- 0	AS FeeOrderIndex,
--CAST(0 AS smallint)	AS FeeHeadOrderIndex,
--CAST(0 AS bit) AS IsCancelReceipt,
--''	AS CurrencyCode,
--''	AS CurrencyTitle,
--''	AS Batch,
--CAST('' AS date) AS ChequeDate,
--CAST(0 AS bit) AS IsMonthDetailOnly,
--CAST(0 AS bit) AS IsFeeInvoiceHeadwise,
--''	AS GroupTitle,
--CAST(0 AS bit) AS IsSuppressFeeDetail,
--''	AS CollectionGroupName,
--0	AS InvoiceNumber,
--''	AS SUID,
--CAST(0 AS tinyint) AS HeaderDisplayId,
--0x  AS ReceiptBookHeader,
--CAST(0.0 AS decimal(6,2)) AS ServiceTaxPercentage,
--CAST(0.0 AS decimal(12,2)) AS AmountAfterDiscount,
--CAST(0.0 AS decimal(5,2)) AS CGSTPercentage,
--CAST(0.0 AS decimal(5,2)) AS SGSTPercentage,
--CAST(0.0 AS decimal(12,2)) AS CGSTAmount,
--CAST(0.0 AS decimal(12,2))	AS SGSTAmount,
--''	AS GSTINNo,
--''	AS HSNSACCode,
--''	AS ItemValue ,
--''	AS FatherName,
--''	AS MotherName,
--CAST(0.0 AS decimal(12,2)) AS ExemptionAmount,
--''	AS ExemptionRemark,
--0x AS EmployeeSign,
--''	AS PresentCity,
--''	AS PresentAdress,
--''	AS ParentContactNo,	
--CAST(0 AS bit) AS IsDisplayPendingFeeAmount,
--CAST(0.0 AS decimal(12,0)) AS PendingFeeAmount,
--''	AS FirstName,
--''	AS LastName,
--CAST(0.0 AS decimal(12,0))	AS FeeAmount,
--CAST(0.0 AS decimal(12,0)) AS ExemptionAmountSum,
--''	AS TransportBusRoute,
--''	AS BankAccountNumber,
--0	AS FeeHeadNumber,
--0	AS FeeNumber,
--0x AS DigitalSignature,
--''	AS CancelStatusRemark,
--CAST(0.0 AS decimal(12,2))	AS FeeSetupAmount,
--''	AS CreatedDateTime ,
--CAST(0 AS bigint)	AS PaymentTransactionId,
--''	AS MiddleName,
--'' AS CreatedTime
--END
GO
PRINT N'Altering [sFrontOffice].[pStudentGatePassGet]...';


GO
ALTER PROCEDURE [sFrontOffice].[pStudentGatePassGet]
          
           @rId int,
           @rOrgId int=0,
           @rFromGatePassDate datetime='',
           @rToGatePassDate datetime='',
           @rEntryModeStatusId smallint=0,
           @rCode nvarchar(20)='',
           @rIsPrintView bit=0,
           @rOrgGroupId int=0,
           @rUserId bigint= 0,
		   @rClassId int=0,
		   @rDivisionId int=0
AS
SET NOCOUNT ON
BEGIN
		Declare @GatePassConfig nvarchar(20)=''
		SELECT @GatePassConfig =ItemValue From sConfig.tConfigurationSetup WHERE ConfigurationId = 241 AND SourceId = @rOrgGroupId
		
		SELECT  
			SGP.Id,
			vSA.Code,
			vSA.FirstName+' '+vSA.MiddleName+' '+vSA.LastName AS Name,
			VSA.ClassName+' - '+VSA.DivisionName AS Class,
			SGP.AcademicId,
			ISNULL(SGP.RelationshipId,0) AS RelationshipId,
			RM.Title AS Relationship,
			SGP.Relation,
			SGP.VisitorName,
			ISNULL(SGP.VisitorMobile,0) As VisitorMobile,
			SGP.ReasonOfGatePass,
			(CASE  WHEN @rIsPrintView=1 THEN 
				SGP.VisitorPhoto		
			ELSE ISNULL(SGP.VisitorPhoto, CONVERT(VARBINARY(MAX), 0)) END) AS VisitorPhoto,
			--ISNULL(SGP.VisitorPhoto, CONVERT(VARBINARY(MAX), 0)) AS VisitorPhoto,
			ISNULL(SGP.GatePassDate,'') As GatePassDate,
			ISNULL(SGP.ReturnGatePassDate,'') As ReturnGatePassDate,
			ISNULL(SGP.GatePassTime,'') As GatePassTime,
			ISNULL(SGP.ReturnGatePassTime,'') As ReturnGatePassTime,
			ISNULL(SGP.EntryModeStatusId,0) AS EntryModeStatusId,
			ISNULL(SM.Title,'')  AS EntryModeStatus,
			OM.Id AS OrgId,
			OM.OrgLogo,
			OM.DisplayTitle AS Organization,
			(CASE  WHEN @rIsPrintView=1 THEN 
				SI.StudentPhoto		
			ELSE ISNULL(SI.StudentPhoto, CONVERT(VARBINARY(MAX), 0)) END) AS StudentPhoto,
			
			(CASE  WHEN @rIsPrintView=1 THEN 
				SI.FatherPhoto		
			ELSE ISNULL(SI.FatherPhoto, CONVERT(VARBINARY(MAX), 0)) END) AS FatherPhoto,
			
			(CASE  WHEN @rIsPrintView=1 THEN 
				SI.MotherPhoto		
			ELSE ISNULL(SI.MotherPhoto, CONVERT(VARBINARY(MAX), 0)) END) AS MotherPhoto,
			
			(CASE  WHEN @rIsPrintView=1 THEN 
				SI.RelativePhoto		
			ELSE ISNULL(SI.RelativePhoto, CONVERT(VARBINARY(MAX), 0)) END) AS RelativePhoto,
			
			--ISNULL(SI.StudentPhoto, CONVERT(VARBINARY(MAX), 0)) AS StudentPhoto,
			ISNULL(U.DisplayTitle,'') AS UserName,
			vSA.SUId,
			Remark,
			@GatePassConfig AS ConfigValue,
				
			SF.FatherContactNo,
			SF.MotherContactNo,
			SF.RelativeContactNo,
			SF.FatherName,
			SF.MotherName,
			SF.RelativeName,
			ISNULL(SGP.GatePassNo,0)AS GatePassNo
		FROM 
		    sStudent.vStudentAcademic vSA
			INNER JOIN sFrontOffice.tStudentGatePass SGP ON SGP.AcademicId = vSA.AcademicId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=vSA.OrgId
			INNER JOIN sCommon.tRelationshipMaster RM ON RM.Id=SGP.RelationshipId
			INNER JOIN sCommon.tStatusMaster SM ON SM.Id=SGP.EntryModeStatusId			
			INNER JOIN sSecurity.tUserOrganization UO ON UO.OrgId = SGP.OrgId AND UO.UserId = @rUserId
			LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=vSA.StudentId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SI.StudentId
			LEFT JOIN sSecurity.tUser U	ON U.Id=SGP.CreatedUserId
			
		WHERE 
		  ((SGP.Id=@rId) OR (@rId=0))
			AND ((SGP.OrgId=@rOrgId) OR (@rOrgId=0))
			AND ((SGP.GatePassDate>=@rFromGatePassDate) OR (ISNULL(@rFromGatePassDate,'')=''))
			AND ((SGP.GatePassDate<=@rToGatePassDate) OR (ISNULL(@rToGatePassDate,'')=''))
			AND ((SGP.EntryModeStatusId=@rEntryModeStatusId) OR (@rEntryModeStatusId=0))
			AND ((vSA.Code=@rCode) OR (ISNULL(@rCode,'')=''))
			AND (OM.OrgGroupId=@rOrgGroupId)
			AND ((vSA.ClassId=@rClassId) OR (ISNULL(@rClassId,'')=''))
			AND ((vSA.DivisionId=@rDivisionId) OR (ISNULL(@rDivisionId,'')=''))
			--AND vSA.IsActiveBatch=1
			ORDER BY SGP.GatePassDate,SGP.GatePassTime DESC
			
END
GO
PRINT N'Altering [sHrm].[pImportUser]...';


GO
ALTER PROCEDURE [sHrm].[pImportUser]
    
    @rOrgId int,
	@rXmldoc [nvarchar](max),
	@rCreatedUserId bigint 
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	DECLARE @loXML XML,@lCount int,
			@lUserType nvarchar(100),
			@lEmployeeCode nvarchar(100),
			@lUserName nvarchar(100),
			@lDisplayName nvarchar(100),
			@lPassword nvarchar(500),
			@lRole nvarchar(100),
			@lRoleId bigint
	
	SET @loXML = CAST(@rXmldoc AS XML)
	
	CREATE TABLE #tTable1 (
	Id bigint identity,
	UserType nvarchar(50) collate SQL_Latin1_General_CP1_CI_AS ,
	EmployeeCode nvarchar(100) collate SQL_Latin1_General_CP1_CI_AS ,
	UserName nvarchar(100) collate SQL_Latin1_General_CP1_CI_AS ,
	DisplayName nvarchar(100) collate SQL_Latin1_General_CP1_CI_AS ,
	[Password] nvarchar(500) collate SQL_Latin1_General_CP1_CI_AS ,
	[Role] nvarchar(100) collate SQL_Latin1_General_CP1_CI_AS ,
	RoleId bigint)
	
	INSERT INTO #tTable1(
		UserType,
		EmployeeCode,
		UserName,
		DisplayName, 
		[Password],
		[Role],
		RoleId)      
	SELECT
		 T.c.value('@UserType','nvarchar(50)') AS UserType,
		 T.c.value('@EmployeeCode','nvarchar(100)') AS EmployeeCode,
		 T.c.value('@UserName','nvarchar(100)') AS UserName,
		 T.c.value('@DisplayName','nvarchar(100)') AS DisplayName,
		 T.c.value('@Password','nvarchar(500)') AS [Password],
		 T.c.value('@Role','nvarchar(100)') AS [Role],
		 T.c.value('@RolId','nvarchar(100)') AS RoleId
	FROM @loXML.nodes('/Table/ImportUser')T(c)
  			
BEGIN

	DECLARE @rUserSourceId bigint = NULL, @rUserSourceTypeId bigInt = NULL, @EmployeeId bigint = NULL, @tmp varchar(500),@tmp1 varchar(500)

	SET @tmp = ''
	SET @tmp1 =''
	
	SELECT @tmp = @tmp + T.EmployeeCode  + ', '  from #tTable1 T 
	LEFT JOIN  sHrm.tEmployeeMaster EM ON T.EmployeeCode  = EM.Code 
	AND EM.OrgId = @rOrgId 
	WHERE EM.ID IS NULL AND T.EmployeeCode <> '' AND ISNULL(EM.IsOtherUser,0) = 0 AND upper(T.UserType) = 'EMPLOYEE' 
 
	SELECT @tmp1 = @tmp1 + T.EmployeeCode  + ', '  from #tTable1 T 
	LEFT JOIN  sStudent.tStudentMaster SM ON T.EmployeeCode  = SM.Code 
	AND SM.OrgId = @rOrgId  
	WHERE SM.ID IS NULL AND T.EmployeeCode <> '' AND upper(T.UserType) = 'STUDENT'

	IF @tmp <> ''
	BEGIN
		SELECT -101 AS Result, SUBSTRING(@tmp, 0, LEN(@tmp)) AS ResultDetail
		RETURN
	END

	IF @tmp1 <> ''
	BEGIN
		SELECT -102 AS Result, SUBSTRING(@tmp1, 0, LEN(@tmp1)) AS ResultDetail
		RETURN
	END

	SELECT @lCount =  COUNT(Id) FROM #tTable1
 
	WHILE @lCount > 0 
	BEGIN		
		SELECT         
			@lUserType = UserType ,
			@lEmployeeCode = EmployeeCode,
			@lUserName = UserName,
			@lDisplayName = DisplayName,
			@lPassword = [Password],
			@lRole = [Role],
			@lRoleId = RoleId
		FROM #tTable1 WHERE Id = @lCount    
	     
		IF UPPER(@lUserType) = 'EMPLOYEE'
		BEGIN
		 SELECT @rUserSourceId = Id FROM sHrm.tEmployeeMaster WHERE Code = @lEmployeeCode AND OrgID=@rOrgId AND ISNULL(IsOtherUser,0) = 0
		 SELECT @rUserSourceTypeId = 95
		END

		ELSE IF(UPPER(@lUserType) = 'STUDENT')
		BEGIN
			SELECT @rUserSourceId = Id FROM sStudent.tStudentMaster WHERE Code = @lEmployeeCode AND OrgID=@rOrgId 
			SELECT @rUserSourceTypeId =2003
		END

		ELSE
		BEGIN
			 SELECT @rUserSourceId = 0
			 SELECT @rUserSourceTypeId = 1
		END

		IF EXISTS(SELECT Id FROM sSecurity.tUser WHERE OrgId = @rOrgId AND LoginTitle=@lUserName)							
		BEGIN
			Select -103 As Result, 'User Name Already Exist for Code ' + @lEmployeeCode  As ResultDetail
			return
		END
		IF EXISTS(SELECT Id FROM sSecurity.tUser WHERE OrgId = @rOrgId AND SourceTypeId=@rUserSourceTypeId AND SourceId = @rUserSourceId)							
		BEGIN
			Select -104 As Result, 'User Already Exist for Code ' + @lEmployeeCode As ResultDetail
			return
		END

		--IF EXISTS(SELECT Id FROM sSecurity.tUser WHERE SourceId = @rUserSourceId AND OrgId=@rOrgId AND SourceTypeId=@rUserSourceTypeId AND LoginTitle = @lUserName )
		--BEGIN
		--	UPDATE sSecurity.tUser
		--	SET	
		--		LoginPassword = @lPassword,				
		--		IsObsolete = 0,			
		--		UpdatedUserId = @rCreatedUserId,
		--		UpdatedDateTime = sCommon.fGetDate(),
		--		LoginTitle = @lUserName,
		--		DisplayTitle = @lDisplayName
		--	WHERE 
		--		SourceId = @rUserSourceId AND OrgId=@rOrgId	AND SourceTypeId=@rUserSourceTypeId  AND LoginTitle = @lUserName		
		--END
		--ELSE
		--BEGIN
			DECLARE @lNextNumber BIGINT	
			EXECUTE  sCommon.pTableRowIdGet @rOrgId, 'sSecurity.tUser', 0, 101, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sSecurity.tUser
				(
					Id,		
					DisplayTitle,
					LoginTitle,
					LoginPassword,
					LastLockOutDate,
					NumberOfLogonAttempt,
					IsObsolete,
					CreatedUserId,
					CreatedDateTime,
					UpdatedUserId,
					UpdatedDateTime,
					OrgId,
					SourceId,
					SourceTypeId	
				)
			VALUES
				(
					@lNextNumber,
					@lDisplayName,					
					@lUserName,
					@lPassword,
					sCommon.fGetDate(),
					0,
					0,
					@rCreatedUserId,
					sCommon.fGetDate(),
					@rCreatedUserId,
					sCommon.fGetDate(),
					@rOrgId,
					@rUserSourceId,
					@rUserSourceTypeId
				)
				
			IF NOT EXISTS(Select 1 From [sSecurity].[tUserRole]  WHERE RoleId = @lRoleId AND UserId=@lNextNumber)
			BEGIN
				INSERT INTO 
				[sSecurity].[tUserRole] (RoleId, UserId, IsObsolete, CreatedUserId, CreatedDateTime, UpdatedUserId, UpdatedDateTime, IsPrimary)
				VALUES
				(@lRoleId,@lNextNumber,0,@rCreatedUserId,sCommon.fGetDate(),@rCreatedUserId,sCommon.fGetDate(),1)
			END
		--END
		SET @lCount = @lCount-1
	  END 
	SELECT 1 AS Result, '' AS ResultDetail
 END
END
GO
PRINT N'Altering [sHrm].[pJoiningCertificateGet]...';


GO
ALTER PROCEDURE [sHrm].[pJoiningCertificateGet]  
    
  @rId bigint = 0,  
  @rCode nvarchar(20),  
  @rName nvarchar(200),  
  @rFromNo int,  
  @rToNo int,  
  @rFromDate date,  
  @rToDate date,  
  @rOrgId smallint,  
  --@rBatchId int =0,
  @rIsSearch bit = 1,
  @rDepartmentId int = 0,  
  @rEmployeeId bigint=0
      
 WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON  
      
  
BEGIN   
  IF @rIsSearch = 1  
  BEGIN  
   SET @rCode = sCommon.fSearchStringGet(@rCode)  
   SET @rName = sCommon.fSearchStringGet(@rName)  
  END  
   
 SELECT   
   
   CJ.Id,  
   CJ.EmployeeId,  
   EEM.FirstName+' '+EEM.LastName as Name,  
   CJ.Number,  
   CJ.CertificateDate ,  
   CJ.Remark,  
   isnull(EEM.Designation,0) AS Designation,    
   EEM.Code , 
	CJ.PayScale,
	CJ.DateOfJoining,
	CJ.AgreementPeriod, 
	CJ.CertificateRemark,
	CJ.DepartmentId,
	EEM.DateofJoining AS EmployeeDateofJoining  --added
	--EEM.DepartmentName As Department
	--BM.Title As Batch
     
  FROM   
            sHrm.tCertificateJoining CJ  
       INNER JOIN sHrm.vExtEmployeeMaster EEM ON EEM.EmployeeId=CJ.EmployeeId AND EEM.OrgId=@rOrgId   
       --LEFT JOIN sSetup.tBatchMaster BM ON BM.Id = CI.BatchId
  WHERE   
    
 ((CJ.Id = @rId) OR (@rId = 0))  
 --AND ((BM.Id = @rBatchId) OR (@rBatchId = 0)) 
 AND ((EEM.Code like @rCode) OR (ISNULL(@rCode,'') = ''))  
 AND ((EEM.EmployeeName like @rName) OR (ISNULL(@rName,'') = ''))  
 AND ((CJ.Number >= @rFromNo) OR (@rFromNo=0))  
 AND ((CJ.Number <= @rToNo) OR (@rToNo=0))  
 AND ((CJ.CertificateDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))  
 AND ((CJ.CertificateDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))  
 AND ((EEM.DepartmentId = @rDepartmentId) OR (@rDepartmentId = 0) )	
 ORDER BY CJ.Number  DESC
END
GO
PRINT N'Altering [sHrm].[pRptJoiningCertificateGet]...';


GO
ALTER PROCEDURE [sHrm].[pRptJoiningCertificateGet]  
 
  @rEmployeeId [bigint]= 0, 
  @rBuilder1 nvarchar(max)='',  
  @rBuilder2 nvarchar(max)='',    
  @rOrgId smallint,  
  @rCode nvarchar(20)='',  
  @rFromNo int=0,  
  @rToNo int=0,  
  @rFromDate date='',  
  @rToDate date='',  
  @rName nvarchar(20)=''

WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
  
BEGIN  
DECLARE @lIsOnPrePrinted bit = 0  
  
DECLARE @lConfigValue nvarchar(50)  
DECLARE @lIsDisplayImageHeader bit=0  
  
SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 182 AND SourceTypeId = 1016 AND SourceId = @rOrgId  
   
 IF (@lConfigValue IS NULL) OR (@lConfigValue = 'Default')  
 BEGIN  
 SET @lIsOnPrePrinted = 0  
 SET @lIsDisplayImageHeader = 0  
 END  
 ELSE IF (@lConfigValue = 'Blank')  
 BEGIN  
 SET @lIsOnPrePrinted = 1  
 SET @lIsDisplayImageHeader = 0  
 END  
 ELSE IF (@lConfigValue = 'Image')  
 BEGIN  
 SET @lIsOnPrePrinted = 0  
 SET @lIsDisplayImageHeader = 1  
 END  
   
 SELECT  
   @rBuilder1 AS Builder1,  
   @rBuilder2 AS Builder2,  
   EM.Id AS EmployeeId,
   CJ.Number,
   EM.EmployeeName,
   EM.FirstName,  
   EM.MiddleName,  
   EM.LastName,      
   EM.Code,    
   EM.ShortName,  
   EM.OrgID,  
   EM.DepartmentId,  
   EM.DepartmentName,  
   EM.DesignationId,  
   EM.Designation,  
   EM.EmployeePhoto, 
   EM.DateOfJoining, 
   @lIsOnPrePrinted AS IsOnPrePrinted,  
   @lIsDisplayImageHeader AS IsDisplayImageHeader,  
   OI.CertificateHeaderA4 ,  
   CJ.Remark,  
   ISNULL(CJ.CertificateDate,'') AS CertificateDate,  
   EM.MaritalStatusId,  
   CJ.PayScale,  
   CJ.AgreementPeriod,
   CJ.CertificateRemark,
   OM.Title As OrgTitle,
   CJ.CreatedDateTime As CreatedDateTime,
   U.DisplayTitle As UserDisplayName,
   EDM.Title AS EmployeeDesignation,
   OM.DisplayTitle As ShortTitle,
   DM.Title AS DepartmentName1,
   EM.MobileNumber,
   --EC.PresentStreetOne+'-'+EC.PresentStreetTwo+ '-' +EC.PresentStreetThree AS LocalAddress,
    EC.PresentStreetOne  + CASE WHEN EC.PresentStreetOne = '' OR (EC.PresentStreetTwo = '' AND EC.PresentStreetThree = '') Then '' ELSE '-' END 
   		    + EC.PresentStreetTwo + CASE WHEN EC.PresentStreetTwo = '' OR EC.PresentStreetThree = '' Then '' ELSE '-' END	
			+ EC.PresentStreetThree AS LocalAddress,
   --EC.PresentCity+ '-'+EC.PresentState+'-'+EC.PresentZipCode+'-'+EC.PresentContactNo As LocalAddress1,
	EC.PresentCity  + CASE WHEN EC.PresentCity = '' OR (EC.PresentState = '' AND EC.PresentZipCode = '' AND EC.PresentContactNo = '') Then '' ELSE '-' END 
		+ EC.PresentState + CASE WHEN EC.PresentState = '' OR (EC.PresentZipCode = '' AND EC.PresentContactNo = '') Then '' ELSE '-' END	
		+ EC.PresentZipCode + CASE WHEN EC.PresentZipCode = '' OR EC.PresentContactNo = '' Then '' ELSE '-' END
		+ EC.PresentContactNo AS LocalAddress1,
   ISNULL(EDM1.Title,'Admin') AS UserDesignation,
   --Replace(Replace(RTrim(Ltrim(EC.PresentCity +' '+ EC.PresentState +' '+ EC.PresentZipCode)),'  ',' '),' ','-') AS LocalAddress2
   EC.PresentCity  + CASE WHEN EC.PresentCity = '' OR (EC.PresentState = '' AND EC.PresentZipCode = '') Then '' ELSE '-' END 
   		    + EC.PresentState + CASE WHEN EC.PresentState = '' OR EC.PresentZipCode = '' Then '' ELSE '-' END	
			+ EC.PresentZipCode AS LocalAddress2,
	ISNULL(CJ.DateOfJoining,'') As CertificateDateOfJoining

 FROM   
        
	  sHrm.tCertificateJoining CJ   
      INNER JOIN [sHrm].[vExtEmployeeMaster] EM  ON EM.EmployeeId=CJ.EmployeeId 
      INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId   
      INNER JOIN  sSecurity.tUser U ON U.Id=CJ.CreatedUserId 
	  --AND U.SourceTypeId = 95
      INNER JOIN  sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId=EM.Id
	  LEFT JOIN  sHrm.tEmployeeDesignationMaster EDM ON EDM.Id=EJD.EmployeeDesignationId  --added
	  INNER JOIN  sSetup.tDepartmentMaster DM ON DM.Id=CJ.DepartmentId
	  LEFT JOIN  sHrm.tEmployeeCommunications EC ON EC.EmployeeId=CJ.EmployeeId  --added
	  LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id 
      LEFT JOIN  sHrm.tEmployeeJobDetail EJD1 ON EJD1.EmployeeId=U.SourceId
	  LEFT JOIN  sHrm.tEmployeeDesignationMaster EDM1 ON EDM1.Id=EJD1.EmployeeDesignationId
 WHERE    
 
    ((EM.Code LIKE @rCode)OR (@rCode = '' ))     
    AND ((EM.EmployeeId = @rEmployeeId) OR (@rEmployeeId = 0))    
    AND ((EM.EmployeeName like @rName) OR (ISNULL(@rName,'') = ''))
    AND ((CJ.Number >= @rFromNo) OR (ISNULL(@rFromNo,0)=0))  
    AND ((CJ.Number <= @rToNo) OR (ISNULL(@rToNo,0)=0))  
    AND ((CJ.CertificateDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))  
    AND ((CJ.CertificateDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))    
    AND  EM.OrgId = @rOrgId
END
GO
PRINT N'Altering [sIp].[pImportStockIem]...';


GO
ALTER PROCEDURE [sIp].[pImportStockIem]
	@rOrgId int,
	@rXmldoc [nvarchar](max),
	@rUserId bigint,
	@rStoreLocationId int,
	@rOrgGroupId smallint,
	@rLocationId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	DECLARE @lCount INT
	DECLARE @lInterrorCode INT,@intDoc INT,@lCode nvarchar(20),
	@lName nvarchar(100),@lSubGroup nvarchar(100),
	@lStockItemCategory nvarchar(100),@lIssueUOM nvarchar(50),
	@lSubGroupId int,@lStockItemCategoryId int,@lIssuUOMId int,
	@lResult int,@lresultCode nvarchar(100),@lIsBatchWiseTracking bit,
	@lSafetyStock decimal(12,2),@lStandardDose nvarchar(500),@lReminder nvarchar(500),@lStandardPrice decimal(22,6)
	Declare @lbinCode nvarchar(20),@lVendorCode nvarchar(50),@lVendorCategory nvarchar(50),
	@lBinId int,@lVendorId int, @lVendorCategoryId int

	Declare @loXML XML
    SET @loXML = CAST(@rXmldoc AS XML)

	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXmlDoc

	CREATE TABLE #tTable1 
	(
		Id int identity,Code nvarchar(50),Name nvarchar(100),SubGroup nvarchar(100),
		StockItemCategory nvarchar(100),IssueUOM nvarchar(50),SafetyStock decimal(12,2),
		BinCode nvarchar(50),VendorCode nvarchar(50), IsBatchWiseTracking bit ,StandardPrice decimal(22,6)
	)
	INSERT INTO #tTable1
	Select 
		 T.c.value('@Code','nvarchar(20)') AS Code,
            T.c.value('@Name','nvarchar(100)') AS Name,
            T.c.value('@SubGroup','nvarchar(100)') AS SubGroup,
            T.c.value('@StockItemCategory','Nvarchar(100)') AS StockItemCategory,
            T.c.value('@IssueUOM','nvarchar(50)')   AS IssueUOM,
            T.c.value('@SafetyStock','Decimal(12,2)') AS SafetyStock,
            T.c.value('@BinCode','nvarchar(50)') AS BinCode,
            T.c.value('@VendorCode','nvarchar(50)') AS VendorCode,
			T.c.value('@IsBatchWiseTracking','bit') AS IsBatchWiseTracking,
			T.c.value('@StandardPrice','decimal(22,6)') AS StandardPrice
	
	FROM 
            @loXML.nodes('/Table/ImportStockItem')T(c)
	--INSERT INTO #tTable1
	--(
	--	Code,Name,SubGroup,StockItemCategory,IssueUOM,SafetyStock,BinCode,
	--	VendorCode, IsBatchWiseTracking
	--)      
	--SELECT Code,Name,SubGroup,StockItemCategory,IssueUOM,SafetyStock,BinCode,
	--VendorCode, IsBatchWiseTracking
	--FROM OPENXML (@intDoc, '/Table/ImportStockItem', 1)
	--With
	--(   
	--	Code nvarchar(20) '@Code',Name nvarchar(100) '@Name',
	--	SubGroup nvarchar(100) '@SubGroup',
	--	StockItemCategory nvarchar(100) '@StockItemCategory',
	--	IssueUOM nvarchar(50) '@IssueUOM',
	--	SafetyStock decimal(12,2) '@SafetyStock',
	--	BinCode nvarchar(50) '@BinCode',
	--	VendorCode nvarchar(50) '@VendorCode',
	--	--VendorCategory nvarchar(50) '@VendorCategory',
	--	IsBatchWiseTracking  '@IsBatchWiseTracking'
	--)
	EXEC sp_xml_removedocument @intDoc
	SELECT @lCount = COUNT(Id) FROM #tTable1
	PRINT @lCount
	WHILE @lCount > 0 
	BEGIN
		SELECT @lCode = Code,@lName = [ssetup].[fPortalXMLTagsReplaceReverse](Name),@lSubGroup = SubGroup,
		@lStockItemCategory = StockItemCategory,@lIssueUOM = IssueUOM,
		@lSafetyStock=SafetyStock,@lbinCode=BinCode,
		@lVendorCode=VendorCode, @lIsBatchWiseTracking=IsBatchWiseTracking, @lStandardPrice = StandardPrice
		FROM #tTable1 WHERE Id = @lCount
		
		IF NOT EXISTS(SELECT 1 FROM sIp.tStockItemMaster where Code = @lCode AND StoreLocationId = @rStoreLocationId)           
		BEGIN
			SELECT @lSubGroupId = Id From sIp.tItemSubGroupMaster where Title = @lSubGroup
			SELECT @lStockItemCategoryId= Id From sIp.tStockItemCategoryMaster where Title = @lStockItemCategory AND Storelocationid=@rStoreLocationId
			
			Select @lBinId = Id from sIp.tBinMaster where StoreLocationId =@rStoreLocationId And Code = @lBinCode
			Select @lVendorId = Id from sProcurement.tVendorMaster where LocationId= @rStoreLocationId And Code=@lVendorCode
			--Select @lVendorCategoryId=Id from sProcurement.tVendorCategoryMaster Where OrgGroupId =@rOrgGroupId And Code=@lVendorCategory
			
			SELECT @lIssuUOMId= Id From sFinanceCommon.tStockUomMaster where Title = @lIssueUOM AND OrgGroupId=@rOrgGroupId
			
			if  @lBinId is null	
			BEGIN
				Select -1 As Result
				Return
			END

			if @lVendorId is null
			BEGIN
				Select -2 As Result
				Return
			END
			
			if @lIssuUOMId is null
			BEGIN
				Select -11 As Result
				Return
			END

			IF @lSubGroupId IS NULL
			BEGIN
				Select -3 As Result
				Return
			END

			--Select @lIssuUOMId AS IssuUOMId,@lStockItemCategoryId AS StockItemCategoryId,@lSubGroupId AS SubGroupId
			--SET @lResult = -101
			print @lIssuUOMId
			print @lSubGroupId

			IF(@lSubGroupId > 0  AND @lIssuUOMId>0)
			BEGIN
				DECLARE @lNextNumber INT
				EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sIp.tStockItemMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
				  
				INSERT INTO	sIp.tStockItemMaster
				(
					Id,Code,Title,CreatedUserId,CreatedDateTime,UpdatedUserId,
					UpdatedDateTime,StockItemCategoryId,IsBatchWiseTracking,
					IssueStockUomId,ItemSubGroupId,SafetyStock,VendorId,
					IsObsolete,OrgGroupId,TaxGroupId,Remark,IsBlockedForIssue,
					StoreLocationId,LocationId,StandardDose,BinId,Reminder
				)
				VALUES
				(
					@lNextNumber,@lCode,@lName,@rUserId,sCommon.fGetDate(),
					@rUserId,sCommon.fGetDate(),@lStockItemCategoryId,
					@lIsBatchWiseTracking,@lIssuUOMId,@lSubGroupId,
					@lSafetyStock,@lVendorId,0,@rOrgGroupId,null,null,0,
					@rStoreLocationId,@rLocationId,null,@lBinId,@lReminder
				)
				
				
				EXECUTE [sIp].[pStockItemPlanningAdd] 0,@lNextNumber,0,0,0,@lStandardPrice,null,null,@rStoreLocationId,@rOrgGroupId
  				EXECUTE sProcurement.pStockBatchAdd  @rOrgGroupId,@rStoreLocationId,@lNextNumber,null,null,0,@rUserId ,0 
  	
				--EXECUTE	sIp.pStockItemPurchaseUomAdd @rOrgGroupId,@lNextNumber,@rIssueStockUomId,1,0,@rCreatedUserId	
				--EXEC [sIp].[pItemStandardPriceLogAdd] @lNextNumber, @rStandardPrice, @rCreatedUserId,@rOrgGroupId
 
				SET @lResult = 1
				
				Set @lresultCode= @lCode
				 
			END
		END
		  
		ELSE
		BEGIN
			Set @lResult=-101
			Set @lresultCode= @lCode
		END
		SET @lCount = @lCount - 1
	END
	Select @lResult AS Result,@lresultCode AS Code
END
GO
PRINT N'Altering [sLibrary].[pBookMasterGet]...';


GO

ALTER PROCEDURE [sLibrary].[pBookMasterGet]
	 
	 @rId bigint = 0,
	 @rCode nvarchar(200),
	 @rTitle nvarchar(100),
	 @rOrgId int,
	 @rPublisher nvarchar(200),
	 @rSupplier nvarchar(200),
	 @rAuthor nvarchar(200),
	 @rSubject nvarchar(200),
	 @rLanguage nvarchar(200),
	 @rLocation nvarchar(200),
	 @rEdition nvarchar(50),
	 @rPrice decimal=0,
	 @rCurrency nvarchar(100),
	 @rISBNNumber nvarchar(100),
	 @rBookTag nvarchar(200),
	 @rBookCustomTag nvarchar(200),
	 @rBookAgeGroupId int,
	 @rResourceTypeId int,
	 @rStatusId smallint,
	 @rCreatedUserId bigint,
	 @rLibraryId int,
	 @rBookDepartment nvarchar(150),
	 @rShowWithPhotoId int=0,
	 @rBookGroup1Id int=0,
	 @rBookGroup2Id int=0,
	 @rAccession nvarchar(50),
	 @rOrderIndex int=0
	
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
--DECLARE @lpaging
BEGIN 


	  --BEGIN
			--SET @rCode = sCommon.fSearchStringGet(@rCode)
			SET @rTitle = sCommon.fSearchStringGet(@rTitle)
			SET @rPublisher = sCommon.fSearchStringGet(@rPublisher)
			SET @rSupplier = sCommon.fSearchStringGet(@rSupplier)
			SET @rAuthor = sCommon.fSearchStringGet(@rAuthor)
			SET @rSubject = sCommon.fSearchStringGet(@rSubject)
			SET @rLanguage = sCommon.fSearchStringGet(@rLanguage)
			SET @rLocation = sCommon.fSearchStringGet(@rLocation)
			SET @rEdition = sCommon.fSearchStringGet(@rEdition)
			SET @rCurrency = sCommon.fSearchStringGet(@rCurrency)
			SET @rISBNNumber = sCommon.fSearchStringGet(@rISBNNumber)
			SET @rBookTag = sCommon.fSearchStringGet(@rBookTag)
			SET @rBookCustomTag = sCommon.fSearchStringGet(@rBookCustomTag)
			SET @rAccession = sCommon.fSearchStringGet(@rAccession)
		
	  --END
	 declare @quer nvarchar(max)=''
	declare @lId bigint = 0,@lCode nvarchar(200),@lTitle nvarchar(100),@lOrgId int,
	@lPublisher nvarchar(200),@lSupplier nvarchar(200),@lAuthor nvarchar(200),
	@lSubject nvarchar(200),@lLanguage nvarchar(200),@lLocation nvarchar(200),
	@lEdition nvarchar(50),@lPrice decimal=0,@lCurrency nvarchar(100),
	@lISBNNumber nvarchar(100),@lBookTag nvarchar(200),@lBookCustomTag nvarchar(200),
	@lBookAgeGroupId int,@lResourceTypeId int,@lStatusId smallint,@lCreatedUserId bigint,
	@lLibraryId int,@lBookDepartment nvarchar(150),@lBookGroup1Id int,@lBookGroup2Id int,@lAccession nvarchar(50),
	@lOrderIndex int
	select @lId = isnull(@rId,0),@lCode = isnull(@rCode,''),@lTitle = isnull(@rTitle,''),
	@lOrgId = isnull(@rOrgId,0),@lPublisher = isnull(@rPublisher,''),@lSupplier = isnull(@rSupplier,''),
	@lAuthor = isnull(@rAuthor,''),@lSubject = isnull(@rSubject,''),
	@lLanguage = isnull(@rLanguage,''),@lLocation = isnull(@rLocation,''),
	@lEdition = isnull(@rEdition,''),@lPrice = isnull(@rPrice,0),@lCurrency = isnull(@rCurrency,''),
	@lISBNNumber = isnull(@rISBNNumber,''),@lBookTag = isnull(@rBookTag,''),
	@lBookCustomTag = isnull(@rBookCustomTag,''),@lBookAgeGroupId = isnull(@rBookAgeGroupId,0),
	@lResourceTypeId = isnull(@rResourceTypeId,0),@lStatusId = isnull(@rStatusId,0),@lCreatedUserId = isnull(@rCreatedUserId,0),
	@lLibraryId = isnull(@rLibraryId,0),@lBookDepartment = isnull(@rBookDepartment,''),
	@lBookGroup1Id = isnull(@rBookGroup1Id,0),@lBookGroup2Id = isnull(@rBookGroup2Id,0),@lAccession = ISNULL(@rAccession,''),
	@lOrderIndex=ISNULL(@rOrderIndex,0)

	 
	SELECT @quer='SELECT BM.Code,
	BM.Title,
	ISNULL(BM.Publisher,'''')AS Publisher,
	ISNULL(BM.Supplier,'''')AS Supplier,
	ISNULL(BM.Author,'''')AS Author,
	ISNULL(BM.BookSubject,'''') AS BookSubject,
	ISNULL(BM.BookLanguage,'''') AS BookLanguage,
	ISNULL(BM.Location,'''') AS Location,
	ISNULL(BM.Edition,'''')AS Edition,
	ISNULL(BM.ISBNNumber,'''') AS ISBNNumber,
	ISNULL(BM.Price,0) AS Price,
	ISNULL(BM.Currency,'''') AS Currency,
	ISNULL(BM.BookTag,'''')AS BookTag,
	ISNULL(BM.BookCustomTag,'''') AS BookCustomTag,
	ISNULL(BM.BookDepartment,'''') AS BookDepartment,
	ISNULL(BM.ClassNumber,'''') AS ClassNumber,
	ISNULL(BM.BookVolume,1) AS BookVolume,
	ISNULL(BM.NoOfCopies,1) AS NoOfCopies,
	SM.Title AS StatusName,
	BM.Id,
	ISNULL(AGM.Id,0)AS BookAgeGroupId,
	ISNULL(RTM.Id,0) AS ResourceTypeId,
	BM.CreatedUserId,
	ISNULL(U1.DisplayTitle,'''') AS CreatedUserName ,
	BM.CreatedDateTime,
	ISNULL(BM.UpdatedUserId,0) AS UpdatedUserId,
	ISNULL(U2.DisplayTitle,'''') AS UpdatedUserName ,
	ISNULL(BM.UpdatedDateTime,'''') AS UpdatedDateTime,
	BM.LibraryId,
	ISNULL(LM.Title,'''') AS LibraryName,
	SM.Id AS StatusId,
	BM.ClassificationNo,
	BM.AccessionNo,
	ISNUll(BM.NoOfPages,0) AS NoOfPages,
	 ISNULL(BM.PurchaseDate,''0001/01/01'') AS PurchaseDate,
    ISNUll(BM.BookRemark,'''') AS BookRemark,
    ISNULL(BM.BillNumber,'''') AS BillNumber,
    BM.YearOfPublication,
    ISNULL(BM.BookEntryDate,''0001/01/01'') AS BookEntryDate,
    CASE WHEN ' + convert(varchar,@rShowWithPhotoId) + '= 1 Then ISNULL(BM.BookImage, CONVERT(VARBINARY(MAX), 0)) ELSE BM.BookImage END AS BookPicture,
    ISNULL(BM.BookGroup1Id,0) AS BookGroup1Id,
    ISNULL(BM.BookGroup2Id,0) AS BookGroup2Id,
	ISNULL(BM.OrderIndex,0) AS OrderIndex 
	
	
	
    

	FROM 
         sLibrary.tBookMaster BM
         LEFT JOIN sLibrary.tBookAgeGroupMaster AGM ON BM.BookAgeGroupId=AGM.Id
         INNER JOIN sLibrary.tResourceTypeMaster RTM ON BM.ResourceTypeId=RTM.Id
         INNER JOIN sLibrary.tBookLog BL ON BL.ResourceSourceId = BM.Id AND IsCurrentStatus=1
         INNER JOIN sCommon.tStatusMaster SM ON SM.Id=BL.StatusId 
		 INNER JOIN sLibrary.tLibraryMaster LM ON LM.Id = BM.LibraryId
		 INNER JOIN sSecurity.tUser U1 ON U1.Id = BM.CreatedUserId
		 INNER JOIN sSecurity.tUser U2 ON U2.Id = BM.UpdatedUserId
		  
         
         WHERE ((BM.Id = ' + convert(varchar,@lId) + ') OR (' + convert(varchar,@lId) + ' = 0))
	AND ((BM.Code = ''' + @lCode + ''') OR (''' + @lCode + ''' = ''''))
	AND ((BM.Title like N''' + @lTitle + ''') OR (''' + @lTitle + ''' = '''')) 
	AND ((BM.AccessionNo like ''' + @lAccession + ''') OR (''' + @lAccession + ''' = '''')) 
	AND ((BM.Publisher like N''' + @lPublisher + ''') OR (''' + @lPublisher + ''' = '''')) 
	AND ((BM.Supplier like ''' + @lSupplier + ''') OR (''' + @lSupplier + ''' = '''')) 
	AND ((BM.Author like N''' + @lAuthor + ''') OR (''' + @lAuthor + ''' = '''')) 
	AND ((BM.BookSubject like N''' + @lSubject + ''') OR (''' + @lSubject + ''' = ''''))  
	AND ((BM.BookLanguage like N''' + @lLanguage + ''') OR (''' + @lLanguage + ''' = ''''))  
	AND ((BM.Location like ''' + @lLocation + ''') OR (''' + @lLocation + ''' = ''''))  
	AND ((BM.Edition like ''' + @lEdition + ''') OR (''' + @lEdition + ''' = ''''))  
	AND ((BM.ISBNNumber like ''' + @lISBNNumber + ''') OR (''' + @lISBNNumber + ''' = ''''))  
	AND ((BM.BookTag like ''' + @lBookTag + ''') OR ( ''' + @lBookTag + ''' = ''''))
	AND ((BM.BookCustomTag like ''' + @lBookCustomTag + ''') OR (''' + @lBookCustomTag + ''' = ''''))
	AND ((BM.BookAgeGroupId = ' + convert(varchar,@lBookAgeGroupId) + ') OR(' + convert(varchar,@lBookAgeGroupId) + ' = 0))
	AND ((BM.ResourceTypeId = ' + convert(varchar,@lResourceTypeId) + ') OR (' + convert(varchar,@lResourceTypeId) + ' = 0))
	AND ((BM.BookGroup1Id = ' + convert(varchar,@lBookGroup1Id) + ') OR (' + convert(varchar,@lBookGroup1Id) + ' = 0))
	AND ((BM.BookGroup2Id = ' + convert(varchar,@lBookGroup2Id) + ') OR (' + convert(varchar,@lBookGroup2Id) + ' = 0))
	AND ((BM.Price = ' + convert(varchar,@lPrice) + ') OR (' + convert(varchar,@lPrice) + ' = 0))
	AND ((BM.Currency like ''' + @lCurrency + ''') OR (''' + @lCurrency  + ''' = ''''))
	AND ((BL.StatusId = ' + convert(varchar,@lStatusId) + ') OR (' + convert(varchar,@lStatusId) + '=0))
	AND ((BM.BookDepartment like ''' + @lBookDepartment + ''') OR (''' + @lBookDepartment + ''' = ''''))
	AND (BM.LibraryId = ' + convert(varchar,@lLibraryId) +')
	ORDER BY BM.OrderIndex ASC'
	print(@quer);
	exec (@quer)
	

  --FROM 
  --      sLibrary.tBookMaster BM
  --       LEFT JOIN sLibrary.tBookAgeGroupMaster AGM
  --       ON BM.BookAgeGroupId=AGM.Id
  --       INNER JOIN sLibrary.tResourceTypeMaster RTM
  --       ON BM.ResourceTypeId=RTM.Id
  --       INNER JOIN sLibrary.tBookLog BL
  --       ON BL.ResourceSourceId=BM.Id AND IsCurrentStatus=1
  --       INNER JOIN sCommon.tStatusMaster SM
  --       ON SM.Id=BL.StatusId 
        -- WHERE 
  
      --((BM.Id = @rId) OR (@rId = 0))
      --ANd ((BM.Code = @rCode) OR (ISNULL(@rCode,'') = ''))
      -- AND  ((BM.Title like @rTitle) OR (ISNULL(@rTitle,'') = '')) 
      --  AND  ((BM.Publisher like @rPublisher) OR (ISNULL(@rPublisher,'') = '')) 
      --   AND  ((BM.Supplier like @rSupplier) OR (ISNULL(@rSupplier,'') = '')) 
      --    AND  ((BM.Author like @rAuthor) OR (ISNULL(@rAuthor,'') = '')) 
      --     AND  ((BM.BookSubject like @rSubject) OR (ISNULL(@rSubject,'') = ''))  
      --      AND  ((BM.BookLanguage like @rLanguage) OR (ISNULL(@rLanguage,'') = ''))  
      --       AND  ((BM.Location like @rLocation) OR (ISNULL(@rLocation,'') = ''))  
      --        AND  ((BM.Edition like @rEdition) OR (ISNULL(@rEdition,'') = ''))  
      --         AND  ((BM.ISBNNumber like @rISBNNumber) OR (ISNULL(@rISBNNumber,'') = ''))  
      --          AND  ((BM.BookTag like @rBookTag) OR (ISNULL(@rBookTag,'') = ''))
      --           AND  ((BM.BookCustomTag like @rBookCustomTag) OR (ISNULL(@rBookCustomTag,'') = ''))
      --            AND  ((BM.BookAgeGroupId = @rBookAgeGroupId) OR(@rBookAgeGroupId=0))
      --             AND  ((BM.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId=0))
      --              AND  ((BM.Price = @rPrice) OR (@rPrice=0))
      --               AND  ((BM.Currency like @rCurrency) OR (ISNULL(@rCurrency,'') = ''))
      --                AND  ((BL.StatusId = @rStatusId) OR (@rStatusId=0))
      --                ANd ((BM.BookDepartment like @rBookDepartment) OR (ISNULL(@rBookDepartment,'') = ''))
						--AND ((BL.OrgId=@rOrgId) OR (@rOrgId=0))
	  --AND BM.LibraryId = @rLibraryId
END
GO
PRINT N'Altering [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)
		--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))
		AND (BL.OrgId=@rOrgId)
		AND (BL.BatchId=@rBatchId)
		AND (BL.LibraryId=@rLibraryId)
		AND ISNULL(EM.IsOtherUser,0) = 0
END
END
GO
PRINT N'Altering [sSecurity].[pNewTaskAdd]...';


GO
ALTER PROCEDURE [sSecurity].[pNewTaskAdd]
 
	@rOrgGroupId smallint
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	BEGIN TRANSACTION
	
	DECLARE @lItemValue nvarchar(20) = ''
	SELECT 
		@lItemValue = CASE WHEN CS.Id IS NOT NULL THEN ISNULL(CS.ItemValue,'') ELSE ISNULL(CM.ItemValue,'') END 
	FROM 
		sConfig.tConfigurationMaster CM
		LEFT JOIN sConfig.tConfigurationSetup CS ON CM.Id = CS.ConfigurationId
		AND SourceTypeId = 2010 AND SourceId = @rOrgGroupId WHERE CM.Id = 494
    
 
	 
	IF EXISTS (SELECT 1 FROM sSystem.tDatabaseInfo WHERE ISNULL(HostedAS,'') <> 'Portal' AND Id = 1)
	 
	BEGIN
	DELETE OT FROM 
	sSecurity.tOrganizationTask OT
	INNER JOIN sSecurity.tTask T ON OT.TaskId = T.Id
	 WHERE OT.OrgGroupId=@rOrgGroupId AND T.IsClientSpecific = 0
			
	INSERT INTO sSecurity.tOrganizationTask 
	(
		OrgGroupId, TaskId, AvailablePermission
	)
	SELECT  @rOrgGroupId, 
			T.Id,
			T.AvailablePermission
	FROM
			sSecurity.tTask T INNER JOIN sSecurity.tModulePermission P
			ON T.ModuleId = P.ModuleId
	WHERE 
			P.OrgGroupId = @rOrgGroupId
			--AND T.AvailablePermission != 0
			AND T.IsObsolete = 0
			AND T.IsClientSpecific = 0
			
	END
	
	--DELETE FROM sSecurity.tPermission WHERE OrgGroupId=@rOrgGroupId AND RoleId =sCommon.fGetIntIdForOrg(2,@rOrgGroupId)
	 
	
	IF(@lItemValue='True')
	BEGIN
		DELETE FROM sSecurity.tPermission WHERE OrgGroupId=@rOrgGroupId AND RoleId =sCommon.fGetIntIdForOrg(2,@rOrgGroupId)

		INSERT INTO 
		sSecurity.tPermission
		(
		TaskId, 
		RoleId, 
		Permission, 
		IsObsolete, 
		CreatedUserId, 
		CreatedDateTime, 
		UpdatedUserId, 
		UpdatedDateTime,
		OrgGroupId
		)
		SELECT  
		TaskId,
		sCommon.fGetIntIdForOrg(2,@rOrgGroupId),
		AvailablePermission,
		0,
		1,
		sCommon.fGetDate(),
		1,
		sCommon.fGetDate(),
		@rOrgGroupId
		FROM 
		sSecurity.tOrganizationTask 
		WHERE 
		OrgGroupId = @rOrgGroupId
	END
	ELSE
	BEGIN

		DELETE P FROM sSecurity.tPermission P INNER JOIN sSecurity.tTask T ON P.TaskId = T.Id WHERE P.OrgGroupId=@rOrgGroupId AND P.RoleId =sCommon.fGetIntIdForOrg(2,@rOrgGroupId)
		AND T.IsClientSpecific = 0

		INSERT INTO 
		sSecurity.tPermission
		(
		TaskId, 
		RoleId, 
		Permission, 
		IsObsolete, 
		CreatedUserId, 
		CreatedDateTime, 
		UpdatedUserId, 
		UpdatedDateTime,
		OrgGroupId
		)
		SELECT  
		TaskId,
		sCommon.fGetIntIdForOrg(2,@rOrgGroupId),
		OT.AvailablePermission,
		0,
		1,
		sCommon.fGetDate(),
		1,
		sCommon.fGetDate(),
		@rOrgGroupId
		FROM 
		sSecurity.tOrganizationTask OT
		INNER JOIN sSecurity.tTask T ON T.Id = OT.TaskId
		WHERE 
		OrgGroupId = @rOrgGroupId
		AND T.IsClientSpecific = 0
	END

	
		 
	
	
	--DELETE FROM sSecurity.tPermission WHERE OrgGroupId = @rOrgGroupId

	SELECT 1 AS RESULT
	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	

END
GO
PRINT N'Altering [sStudent].[pDigitalFormChangeListGet]...';


GO

 
ALTER PROCEDURE [sStudent].[pDigitalFormChangeListGet]

	  @rId int=0,
	  @rOrgId smallint=0,
	  @rStudentCode nvarchar(100),
	  @rClassId bigint=0,
	  @rDivisionId bigint=0,
	  @rFormStatusId smallint = 0
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	 
		SELECT 
			(SELECT MAX(AutoId) FROM sStudent.tDigitalFormStudent WHERE DigitalFormId = DF.Id AND AcademicId = DFS.AcademicId) as Id,
			DF.Id as DigitalFormId,
			DF.Code,
			DF.Title,
			SA.StudentId,
			SA.AcademicId,
			SA.Code as StudentCode,
			SA.Name,
			SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
			SA.RollNo,
			SA.Number,
			ISNULL(DFSS.StatusId, 0) AS DigitalFormStatusId,
			ISNULL(SM.Title, '') AS DigitalFormStatus,
			ISNULL(DFSS.ActionDateTime, '') AS FormActionDateTime
		FROM 
			sStudent.tDigitalForm DF
			INNER JOIN (SELECT DigitalFormId,AcademicId FROM sStudent.tDigitalFormStudent GROUP By AcademicId,DigitalFormId) DFS ON DFS.DigitalFormId = DF.Id
			INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = DFS.AcademicId
			LEFT JOIN [sStudent].[tDigitalFormStudentStatus] DFSS ON DFSS.DigitalFormId = DF.Id AND DFSS.AcademicId = SA.AcademicId
			LEFT JOIN sCommon.tStatusMaster SM ON SM.Id = DFSS.StatusId
		WHERE 
			((DF.OrgId = @rOrgId)OR (@rOrgId=0))
			AND ((DF.Id = @rId) OR (@rId = 0))		
			AND ((SA.Code = @rStudentCode) OR (@rStudentCode=''))
			AND ((SA.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((SA.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
			AND ((SA.OrgId = @rOrgId) OR (@rOrgId = 0))
			AND (DFSS.StatusId = @rFormStatusId OR @rFormStatusId = 0)
	   ORDER BY Id Asc
END
GO
PRINT N'Altering [sTally].[pReceiptPostingGet]...';


GO

ALTER PROCEDURE [sTally].[pReceiptPostingGet]

	 @rOrgGroupBatchId int,
	 @rReceiptBookId int,
	 @rReceiptDate Date,
	 @rToReceiptDate Date,
	 @rPayTypeId int = 0,
	 @rCustomNarration nvarchar(max)='',
	 @rIsCustomNarration bit=0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	
	DECLARE @lTallyPostingType nVarchar(100)
	DECLARE @lOrgGroupId INT
	
	SELECT @lOrgGroupId = OrgGroupId from sSetup.tOrgGroupBatchMaster Where Id=@rOrgGroupBatchId
	
	SELECT @lTallyPostingType = CASE WHEN CS.ItemValue IS NULL THEN C.ItemValue ELSE CS.ItemValue END 
		FROM sConfig.tConfigurationMaster C
        INNER JOIN sConfig.tConfigurationLevelMaster CM ON CM.Id = C.ConfigurationLevelId
        LEFT JOIN sConfig.tConfigurationSetup CS ON CS.ConfigurationId = C.Id AND CS.SOURCEID= @lOrgGroupId
        WHERE  C.ItemKey = 'Tally.PostingType'
        
		
        IF @lTallyPostingType = 'FeeHeadWise' 
        BEGIN
			   SELECT	ReceiptDate,
						ISNULL(FHM.Title,'') AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						Case when @rIsCustomNarration=1 then STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),(Select Min(ReceiptNumber) from sFee.tReceipt where   FR.ReceiptDate =ReceiptDate)),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),(Select Max(ReceiptNumber) from sFee.tReceipt Where FR.ReceiptDate = ReceiptDate)),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId AND FHM1.Id=FHM.Id where  FR.ReceiptDate=FR1.ReceiptDate FOR XML PATH('')),1,1,'') else
						 'Receipt generated for Batch : ' + (Select Title from sSetup.tOrgGroupBatchMaster Where Id= @rOrgGroupBatchId) +
						' Receipt Date : ' +  Convert(nvarchar(10),ReceiptDate) + ' and Receipt Number From ' + Convert(nvarchar(10),(Select Min(ReceiptNumber) from sFee.tReceipt where   FR.ReceiptDate =ReceiptDate)) + ' -  ' + 
						Convert(nvarchar(10),(Select Max(ReceiptNumber) from sFee.tReceipt Where FR.ReceiptDate = ReceiptDate)) end as Narration,						
						FR.PayTypeId AS PayTypeId, 
						FHM.Id AS FeeId,
						0 AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					WHERE	FR.ReceiptBookId = @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by ReceiptDate,FHM.Title,PM.Title,FHM.Id,FR.PayTypeId
				Order By ReceiptDate Desc
			END				
       ELSE IF @lTallyPostingType = 'FeeHeadWiseCustom' 
        BEGIN
			   SELECT	ReceiptDate,
						FR.Id AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						Case when @rIsCustomNarration=1 then  STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),(Select Min(ReceiptNumber) from sFee.tReceipt where   FR.ReceiptDate =ReceiptDate)),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),(Select Max(ReceiptNumber) from sFee.tReceipt Where FR.ReceiptDate = ReceiptDate)),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId  where  FR.ReceiptDate=FR1.ReceiptDate AND FR.Id=FR1.Id FOR XML PATH('')),1,1,'') else
						SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'') end as Narration,						
						FR.PayTypeId AS PayTypeId, 
						0 AS FeeId,
						FR.Id AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sStudent.vStudentAcademic SM ON SM.ACademicId = FR.SourceID AND SM.OrgGRoupBatchId = FR.OrgGRoupBatchId 
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sSetup.tOrganizationMaster OM ON  OM.Id=SM.OrgId
					LEFT JOIN sSetup.tOrgSubGroupMaster OSM ON OSM.id=OM.OrgSubGroupId

					WHERE	FR.ReceiptBookId= @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by ReceiptDate,PM.Title,FR.PayTypeId,FR.Id,SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')
				Order By FR.Id,ReceiptDate Desc
			END	

       ELSE IF @lTallyPostingType = 'OrgMainGroupWise' 
        BEGIN
			   SELECT	ReceiptDate,
						SM.AcademicId AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						Case when @rIsCustomNarration=1 then  STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFinanceCommon.tPayTypeMaster PM1 ON PM1.Id = FR1.PayTypeId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId  where  FR.ReceiptDate=FR1.ReceiptDate AND PM.Title=PM1.Title AND SM.AcademicId=SM1.AcademicId AND  FR.PayTypeId=FR1.PayTypeId FOR XML PATH('')),1,1,'') else
						SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'') end as Narration,						
						FR.PayTypeId AS PayTypeId, 
						FR.Id AS FeeId,
						SM.AcademicId AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sStudent.vStudentAcademic SM ON SM.ACademicId = FR.SourceID AND SM.OrgGRoupBatchId = FR.OrgGRoupBatchId 
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sSEtup.tOrganizationMaster OM ON OM.OrgSubGRoupID =  @rReceiptBookId AND OM.ID = SM.OrgId					
					WHERE	FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
							AND FR.PayTypeID = 2
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by ReceiptDate,PM.Title,SM.AcademicId ,FR.PayTypeId,FR.Id,SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')
				Order By FR.Id,ReceiptDate Desc
			END	
	ELSE IF @lTallyPostingType = 'ReceiptWise' 
			BEGIN	
				SELECT	ReceiptDate,						
						ISNULL(SLM.LedgerName,'') AS FeeName,
						PM.Title AS PayType,						
						FR.ReceiptAmount AS Amount,
						Case when @rIsCustomNarration=1 then  STUFF((SELECT ','+  +Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFinanceCommon.tPayTypeMaster PM1 ON PM1.Id = FR1.PayTypeId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId
						LEFT JOIN sTally.tStudentLedgerMapping SLM1 ON SLM1.StudentId = SM1.StudentId AND SLM1.ReceiptBookid = FR1.ReceiptBookId AND SLM.LedgerName=SLM1.LedgerName where  FR1.ReceiptDate=FR.ReceiptDate   AND FR1.PayTypeId=FR.PayTypeId AND  A.StudentId=SM1.StudentId AND FR.ReceiptAmount=FR1.ReceiptAmount FOR XML PATH('')),1,1,'') else
						case When FR.PayTypeId = 1 then
						'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName + '-->' +
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FRD1.Amount,'')) + '-' + isnull(FM1.Title ,'') 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') 
						when FR.PayTypeId <> 1 then 
						'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName + '-->' +
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FRD1.Amount,'')) + '-' + isnull(FM1.Title ,'') 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') + '  Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')  ENd
						end AS Narration,
						--A.Name+', '+ClassName + '-' + DivisionName+', '+A.OrganizationName+', ('+BatchName+')'
						--AS Narration,					
						FR.PayTypeId AS PayTypeId,									
						A.StudentId AS FeeId,
						FRD.ReceiptId					 
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sStudent.vStudentAcademic A ON A.AcademicId = FR.SourceId AND A.OrgGRoupBatchId = FR.OrgGRoupBatchId
					LEFT JOIN sTally.tStudentLedgerMapping SLM ON SLM.StudentId = A.StudentId AND SLM.ReceiptBookid = FR.ReceiptBookId
					WHERE							
							FR.ReceiptBookId = @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate	And FR.ReceiptDate <= @rToReceiptDate
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by 
				ReceiptDate,PM.Title,
				SLM.LedgerName,FR.PayTypeId,A.StudentId,
				FRD.ReceiptId,Isnull(FR.ChequeNo,''),ISNull(FR.BankName,''),
				--A.Name+', '+ClassName + '-' + DivisionName+', '+A.OrganizationName+', ('+BatchName+')',
				'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName,
				FR.ReceiptAmount			
				Order By ReceiptDate desc
			END		
			
	ELSE IF @lTallyPostingType = 'FeeHeadAndReceiptBookWise' 
			BEGIN	
				SELECT	ReceiptDate,						
						ISNULL(SLM.LedgerName,'') AS FeeName,
						PM.Title AS PayType,						
						FR.ReceiptAmount AS Amount,
						Case when @rIsCustomNarration=1 then STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFinanceCommon.tPayTypeMaster PM1 ON PM1.Id = FR1.PayTypeId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId 
						LEFT JOIN sTally.tStudentLedgerMapping SLM1 ON SLM1.StudentId = SM1.StudentId AND SLM1.ReceiptBookid = FR1.ReceiptBookId AND SLM.LedgerName=SLM1.LedgerName where  FR1.ReceiptDate=FR.ReceiptDate AND PM1.Title=PM.Title  AND FR1.PayTypeId=FR.PayTypeId AND A.StudentId=SM1.StudentId AND FR.ReceiptAmount=FR1.ReceiptAmount FOR XML PATH('')),1,1,'') else
						case When FR.PayTypeId = 1 then
						'Receiptno-'  + CONVERT(nvarchar,isnull(FR.ReceiptNumber,'')) + 
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FM1.title,'')) 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId And FRD1.ReceiptId = FRD.ReceiptId 
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') 
						when FR.PayTypeId <> 1 then 
						'Receiptno-'  +  CONVERT(nvarchar,isnull(FR.ReceiptNumber,'')) +
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FM1.title,'')) 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId And FRD1.ReceiptId = FRD.ReceiptId 
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') 
						+ '  Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')  ENd
						end AS Narration,
						FR.PayTypeId AS PayTypeId,									
						A.StudentId AS FeeId,
						FRD.ReceiptId		
									 
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sStudent.vStudentAcademic A ON A.AcademicId = FR.SourceId AND A.OrgGRoupBatchId = FR.OrgGRoupBatchId
					LEFT JOIN sTally.tStudentLedgerMapping SLM ON SLM.StudentId = A.StudentId AND SLM.ReceiptBookid = FR.ReceiptBookId
					WHERE							
							FR.ReceiptBookId = @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate	And FR.ReceiptDate <= @rToReceiptDate
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by 
				ReceiptDate,PM.Title,
				SLM.LedgerName,FR.PayTypeId,A.StudentId,
				FRD.ReceiptId,
				Isnull(FR.ChequeNo,''),ISNull(FR.BankName,''),
				'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName,FR.ReceiptAmount,FR.ReceiptNumber
				--,FHM.Title
				
				Order By ReceiptDate desc	
			END			
			
END
GO
PRINT N'Altering [sTimetable].[pProxySettingAdd]...';


GO
 

ALTER PROCEDURE [sTimetable].[pProxySettingAdd]
		
		@rOrgId int,
		@rId int,
		@rTimeTableId int,
		@rEmployeeId bigint,
		@rDivisionId int,
		@rSubjectId int,
		@rDayId smallint,
		@rLectureNumber smallint,
		@rProxyDate Date,
		@rBatchId int,
		@rTimeTableSchemaId int
		
		
           	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	 
 
BEGIN 

	 --BEGIN TRY	
	 --BEGIN TRANSACTION 
	 
	 -- add divisionid because in same Lecture they want same teacher to take both division proxy - mantisid - 0024866
	--IF NOT EXISTS(Select 1 FROM sTimetable.tProxySetting Where DayId = @rDayId AND EmployeeId = @rEmployeeId AND LectureNumber=@rLectureNumber AND ProxyDate=@rProxyDate And TimeTableSchemaId=@rTimeTableSchemaId)
	IF NOT EXISTS(Select 1 FROM sTimetable.tProxySetting Where DayId = @rDayId AND EmployeeId = @rEmployeeId AND LectureNumber=@rLectureNumber AND ProxyDate=@rProxyDate And TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId = @rDivisionId)
	 
	BEGIN
	DECLARE @lNextNumber BIGINT
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sTimetable.tProxySetting', 0, 1, 1,@outNextNumber = @lNextNumber output
	  
	  BEGIN
		   INSERT INTO sTimetable.tProxySetting		 
		   (
				[Id],
			    TimeTableId
			   ,[EmployeeId]
			   ,[DivisionId]
			   ,[SubjectId]
			   ,[DayId]
			   ,[LectureNumber]
			   ,ProxyDate
			   ,BatchId
			   ,TimeTableSchemaId
            )		 
			VALUES
			(	
				@lNextNumber,
				@rTimeTableId,
				@rEmployeeId,
				@rDivisionId,
				@rSubjectId,
				@rDayId,
				@rLectureNumber,
				@rProxyDate,
				@rBatchId,
				@rTimeTableSchemaId
			)		
			SELECT @lNextNumber AS Result
    	END
      END
    ELSE
    BEGIN       
      SELECT -101 AS Result
    END
	
	--COMMIT TRANSACTION
	--END TRY
	
	--BEGIN CATCH 
	--	ROLLBACK TRANSACTION
	--	SELECT -1 AS Result
	--END CATCH	
	
	
END 	             
	
		
END
GO
PRINT N'Creating [sAcademic].[pTeacherSubjectDivisionCopyToAnotherEmployee]...';


GO


CREATE PROCEDURE [sAcademic].[pTeacherSubjectDivisionCopyToAnotherEmployee]
	
	@rExistingEmployeeId bigint,
	@rNewEmployeeId bigint
	

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	DELETE FROM sAcademic.tTeacherSubject WHERE EmployeeId = @rNewEmployeeId
	DELETE FROM sAcademic.tTeacherSubjectDivision WHERE EmployeeId = @rNewEmployeeId

	INSERT INTO sAcademic.tTeacherSubject
           (
		   EmployeeId
           ,SubjectId
		   )
     SELECT
		    @rNewEmployeeId,
            SubjectId
	
		   FROM sAcademic.tTeacherSubject WHERE EmployeeId = @rExistingEmployeeId

	INSERT INTO sAcademic.tTeacherSubjectDivision
			   (
				EmployeeId
			   ,SubjectId
			   ,DivisionId
			   ,SubjectCordinatorEmployeeId
			   )

		 Select @rNewEmployeeId,
				SubjectId,
				DivisionId,
				SubjectCordinatorEmployeeId

		 FROM sAcademic.tTeacherSubjectDivision WHERE EmployeeId = @rExistingEmployeeId

		 SELECT 1 AS Result
END
GO
PRINT N'Creating [sFee].[pRptFeeReceiptDoubleA5WithNextPendingFeeGet]...';


GO
CREATE PROCEDURE [sFee].[pRptFeeReceiptDoubleA5WithNextPendingFeeGet]			
	@rReportId int=0,
	@rReceiptId bigint = 0,
	@rOrgGroupId int = 0,
	@rReceiptBookId int = 0,
	@rFromReceiptNo int=0,
	@rToReceiptNo int=0,
	@rOrgId int = 0,
	@rBatchId int = 0,
	@rReceiptDate Date='',
	@rClassId int = 0,
	@rOrgIds nvarchar(max) = '',
	@rReceiptPrintGroupId nvarchar(100)='',
	@rDivisionId int = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	
BEGIN 	
	
	DECLARE @lOrgGroupId int = 0
	DECLARE @lAmountInWord nvarchar(500) = ''
	DECLARE @lReceiptAmount Decimal(12,2) = 0.0
	DECLARE @lCurrencyCode nvarchar(20) = ''
	DECLARE @lCurrencyTitle nvarchar(100) = ''
	Declare @lIDConfigValue nvarchar(50)='',@lOrgGroup_Id int=0
	Select @lOrgGroup_Id = OrgGroupId From  sSetup.tOrganizationMaster Where Id = @rOrgId
	 
	SELECT @lIDConfigValue =ItemValue From sConfig.tConfigurationSetup
	WHERE ConfigurationId = 236 AND SourceId = @rOrgGroupId
	
	IF(@rOrgGroupId=0)
	BEGIN
		SELECT @lOrgGroupId = OrgGroupId ,@lReceiptAmount = R.ReceiptAmount  FROM sFee.tReceipt R 
		INNER JOIN sSetup.tOrgGroupBatchMaster OB ON R.OrgGroupBatchId = OB.Id 
		WHERE R.Id = @rReceiptId

		SELECT  @lAmountInWord = sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(@lReceiptAmount,0.00),1,2,@lOrgGroupId)
		SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@lOrgGroupId) 
		SELECT  @lCurrencyTitle = [sFinanceCommon].[fDefaultCurrencyTitleGet](@lOrgGroupId) 
	END
	ELSE
	BEGIN
		SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@rOrgGroupId) 
	END

	IF(@rOrgGroupId=0) SET @rOrgGroupId=@lOrgGroupId

	SELECT 
			FRD.ReceiptId,
			FR.ReceiptNumber,
			FR.ReceiptDate,
			SA.AcademicId,
			SA.Name AS Name,
			SA.Code,
			SA.RollNo,
			SA.ClassId,
			SA.DivisionId,
			SA.ClassName,
			SA.DivisionName,
			SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
			FM.FeeHeadId,
			FHM.Title AS FeeHead,
			FM.Id AS FeeId,
			FM.Title AS FeeName,
			FRD.Amount AS PaidAmount,
			PTM.Id AS PayTypeId,
			PTM.Title AS PayType,
			CASE WHEN ISNULL(FR.ChequeNo,'') != '' THEN FR.ChequeNo ELSE CONVERT(nvarchar(20),ISNULL(FR.PaymentTransactionId,''))  END AS ChequeNo,
			ISNULL(FR.BankName,'') AS BankName,
			ISNULL(RBM.Address1,'') AS Address1,
			ISNULL(RBM.Address2,'') AS Address2,
			ISNULL(RBM.FullTitle,'') AS FullTitle,
			ISNULL(FR.TotalReceiptAmount,0.00) AS TotalReceiptAmount,
			ISNULL(FR.ReceiptAmount,0.00) AS ReceiptAmount,			
			(CASE WHEN @rOrgGroupId = 0 -- For Single Receipt
					THEN '' 
				  WHEN @rOrgGroupId != 0 -- For Bulk Receipt
					THEN sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(FR.ReceiptAmount,0.00),1,2,@rOrgGroupId)
			END) AS ReceiptAmountInWords,
			ISNULL(FR.DiscountAmount,0.00) AS DiscountAmount,
			FR.Remark,
			(CASE WHEN  ISNULL(FR.PaymentTransactionId,0) > 0 
			  THEN -- Navrachana
				'Online Generated'
				ELSE ISNULL(U.DisplayTitle,'') END) AS UserName,
			OM.OrgLogo,
			RBM.Remark1,
			RBM.Remark2,			 
			ISNULL(RBM.IsOnPrePrinted,0) AS IsOnPrePrinted,
			ISNULL(RBM.IsSupressLogo,0) AS IsSupressLogo,
			ISNULL(RBM.IsDisplaySchoolName,0) AS IsDisplaySchorolName,
			RBM.ReportId,
			OM.Title AS OrganizationName,
			(CASE WHEN ISNULL(RBM.IsMonthDetailOnly,0) = 1  AND ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 1
			      THEN [sFee].[fFeeHeadMonthNameGet](FR.ID,FHM.Id) -- Head wise Month name in detail
				  WHEN ISNULL(RBM.IsMonthDetailOnly,0) = 1  AND ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 0
			      THEN [sFee].[fFeeMonthNameGet](FR.ID,FM.Id) -- Fee wise Month name in detail
			ELSE '' END) 
			AS MonthDetail,    
			(CASE WHEN ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 1
			      THEN				
					 Stuff((SELECT  N', ' +  FS1.FeeDuration FROM 
					sFee.tReceipt FR1				
					INNER JOIN sFee.tReceiptDetail FRD1	ON FR1.Id=FRD1.ReceiptId AND FR1.SourceTypeId=2001				
					INNER JOIN sFee.tFeeSetup FS1 ON FRD1.FeeSetupId = FS1.Id
					INNER JOIN sFee.tFeeMaster FM1 ON FS1.FeeId=FM1.Id						
					INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id=FM1.FeeHeadId 
					INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId = FR1.SourceId
					Where FM1.FeeHeadId = FM.FeeHeadId AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
					AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
					Order By FS1.StartDate
					FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'' )    -- Fee Head wise Fee Duration
				  WHEN ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 0
			      THEN
					FS.FeeDuration -- Fee wise Fee Duration
				 ELSE '' END) AS FeeDuration,
			DM.Title AS DepartmentName,
			FM.OrderIndex AS FeeOrderIndex,
			FHM.OrderIndex AS FeeHeadOrderIndex,
			FR.IsObsolete AS IsCancelReceipt,
			@lCurrencyCode AS CurrencyCode,
			@lCurrencyTitle AS CurrencyTitle,
			BM.Title AS Batch,
			FR.ChequeDate AS ChequeDate,
			ISNULL(RBM.IsMonthDetailOnly,0) AS IsMonthDetailOnly,
	        ISNULL(RBM.IsFeeInvoiceHeadwise,0) AS IsFeeInvoiceHeadwise,
	        ISNULL(RBM.GroupTitle,'') AS GroupTitle,
	        ISNULL(RBM.IsSuppressFeeDetail,0) AS IsSuppressFeeDetail,
	        ISNULL(CGM.Title,'') AS CollectionGroupName,
	        ISNULL(FI.InvoiceNumber,0) AS InvoiceNumber,
	        SA.SUID,
	        CASE WHEN RBM.IsOnPrePrinted=1 THEN 2
				 ELSE  ISNULL(RBM.HeaderDisplayId,1)
				END AS HeaderDisplayId,
	        RBM.ReceiptBookHeader,
	        RBM.ServiceTaxPercentage,
	        ISNULL(FR.AmountAfterDiscount,0) AS AmountAfterDiscount,
	        (CASE WHEN FR.CGSTPercentage IS NULL THEN RBM.CGSTPercentage ELSE FR.CGSTPercentage END) AS CGSTPercentage,
	        (CASE WHEN FR.SGSTPercentage IS NULL THEN RBM.SGSTPercentage ELSE FR.SGSTPercentage END) AS SGSTPercentage,
	        ISNULL(FR.CGSTAmount,0) AS CGSTAmount,
	        ISNULL(FR.SGSTAmount,0) AS SGSTAmount,
	        ISNULL(OOM.GSTINNo,'') AS GSTINNo,
	        ISNULL(OOM.HSNSACCode,'') AS HSNSACCode,
	        @lIDConfigValue AS ItemValue,
	        SF.FatherName,
	        SF.MotherName,
	        ISNULL(FA.DiscountAmount,0) AS ExemptionAmount,
			ISNULL(FA.Remark,'') AS ExemptionRemark,
			EI.EmployeeSign,
			ISNULL(SC.PresentCity,'') AS PresentCity,
			(ISNULL((CASE WHEN SC.PresentStreetOne IS NOT NULL AND SC.PresentStreetOne!='' THEN ''+ SC.PresentStreetOne END),'')+
			ISNULL((CASE WHEN SC.PresentStreetTwo IS NOT NULL AND SC.PresentStreetTwo!=''  THEN ', '+ SC.PresentStreetTwo END),'')+
			ISNULL((CASE WHEN SC.PresentStreetThree IS NOT NULL AND SC.PresentStreetThree!=''  THEN ', '+ SC.PresentStreetThree END),'')+
			ISNULL((CASE WHEN SC.PresentCity IS NOT NULL AND SC.PresentCity!=''  THEN ', ' + SC.PresentCity  END),'')+
			ISNULL((CASE WHEN SC.PresentState IS NOT NULL AND SC.PresentState!=''  THEN  ', '+ SC.PresentState END),'')+
			ISNULL((CASE WHEN SC.PresentZipCode IS NOT NULL AND SC.PresentZipCode!=''  THEN  ', '+ SC.PresentZipCode  END),''))
			 AS PresentAdress,
			SC.ParentContactNo,
			ISNULL(RBM.IsDisplayPendingFeeAmount,0) AS IsDisplayPendingFeeAmount,
			CASE WHEN @rOrgGroupId = 1134 OR @rOrgGroupId = 1742 THEN
			(CASE WHEN  ISNULL(RBM.IsDisplayPendingFeeAmount,0) = 0 THEN 0 ELSE ISNULL([sFee].[fPendingFeeAmountReceiptBookwiseGet](FR.SourceId,RBM.Id),0) END)
			ELSE 
			(CASE WHEN  ISNULL(RBM.IsDisplayPendingFeeAmount,0) = 0 THEN 0 ELSE ISNULL([sFee].[fPendingFeeAmountReceiptBookwiseGet](FR.SourceId,0),0) END) END AS PendingFeeAmount,
			SA.FirstName,
			SA.LastName,
			ISNULL([sFee].[fFeeAmountSumGet](FR.SourceId),0) AS FeeAmount,
			ISNULL([sFee].[fFeeReceiptDiscountSumGet](FR.SourceId),0) AS ExemptionAmountSum,
			[sFee].[fTransportBusRouteGet](SA.StudentId,FR.OrgGroupBatchId) AS TransportBusRoute,
			ISNULL(RBM.BankAccountNumber,'') AS BankAccountNumber,
			ISNULL(FHM.Number,0) AS FeeHeadNumber,
			ISNULL(FM.Number,0) AS FeeNumber,
			ISNULL(RBM.DigitalSignature,0x) AS DigitalSignature,
			ISNULL(FR.CancelStatusRemark,'') AS CancelStatusRemark,
			FS.Amount AS FeeSetupAmount,
			FR.CreatedDateTime ,
			ISNULL(FR.PaymentTransactionId,0) As PaymentTransactionId,
			ISNULL(SA.MiddleName, '') AS FatherName,
			CONVERT(char(5),FR.CreatedDateTime,108) AS CreatedTime,
			ISNULL(GM.Title, '') AS Gender,
			NID.PendingAmount AS NextInstallmentAmount,
			NID.CollectionGroupId AS NextInstallmentId,
			NID.StartDate AS NextInstallmentStartDate,
			NID.EndDate AS NextInstallmentEndDate,
			NID.CollectionGroupName AS NextInstallmentName		
	FROM 
			sFee.tReceipt FR				
			INNER JOIN sFee.tReceiptDetail FRD ON FR.Id=FRD.ReceiptId AND FR.SourceTypeId=2001				
			INNER JOIN sFee.tFeeSetup FS ON FRD.FeeSetupId = FS.Id				
			INNER JOIN sFee.tFeeMaster FM ON FS.FeeId=FM.Id						
			INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
			INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = FR.SourceId
			INNER JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SA.StudentId
			INNER JOIn sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=FR.PayTypeId
			LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
			INNER JOIN sFee.tReceiptBookMaster RBM  ON RBM.Id=FR.ReceiptBookId
			LEFT JOIN sSecurity.tUser U ON U.Id=FR.CreatedUserId
			INNER JOIN sSetup.tDepartmentMaster DM ON SA.DepartmentId =DM.Id
			LEFT JOIN sSetup.tOrganizationMaster OM ON OM.Id=DM.OrgId
			LEFT JOIN sSetup.tOrganizationOtherDetail OOM ON OM.Id=OOM.OrgId	
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = FR.BatchId 
			LEFT JOIN sFee.tCollectionGroupMaster CGM ON CGM.Id=FR.CollectionGroupId 
			LEFT JOIN sFee.tFeeInvoice FI ON FI.Id=FR.FeeInvoiceId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SA.StudentId
			LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId = U.SourceId AND U.SourceTypeId=95
			LEFT JOIN sOp.tPaymentTransaction PT ON PT.Id = FR.PaymentTransactionId
			LEFT JOIN sCommon.tGenderMaster GM ON GM.Id = SA.GenderId
			LEFT JOIN (SELECT * FROM [sFee].[fNextInstallmentDetailsGet](@rReceiptBookId, @rReceiptId)) NID ON NID.SourceId = FR.SourceId AND NID.SourceTypeId = FR.SourcetypeId
	WHERE 
			((FR.Id=@rReceiptId) OR (@rReceiptId = 0))
			AND ((RBM.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId = 0))
			AND ((FR.ReceiptBookId = @rReceiptBookId) OR (@rReceiptBookId = 0))
			AND ((FR.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo = 0))
			AND ((FR.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo = 0))
			AND ((FR.BatchId = @rBatchId) OR (@rBatchId = 0))
			AND ((BM.OrgId = @rOrgId) OR (@rOrgId = 0))
			AND ((FR.ReceiptDate = @rReceiptDate) OR (ISNULL(@rReceiptDate,'') = ''))
			AND ((SA.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((SA.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
			AND (ISNULL(SA.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')			
			AND ((ISNULL(FR.ReceiptPrintGroupId,'')=@rReceiptPrintGroupId) OR (ISNULL(@rReceiptPrintGroupId,'') = ''))
    ORDER BY 
            FS.StartDate,FHM.OrderIndex,FM.OrderIndex ASC 
END

--BEGIN
--SELECT
-- CAST(0 AS bigint) AS ReceiptId,
-- 0	AS ReceiptNumber,
-- CAST('' AS date) AS ReceiptDate,
-- CAST(0 AS bigint) AS AcademicId,
-- ''	AS Name,
--''	AS Code,
-- 0	AS RollNo,
--0	AS ClassId,
--0	AS DivisionId,
--''	AS ClassName,
--''	AS DivisionName,
--''	AS ClassDivision,
--0	AS FeeHeadId,
--''	AS FeeHead,
--0	AS FeeId,
--''	AS FeeName,
--CAST(0.0 AS decimal(12,2)) AS PaidAmount,
--CAST(0 AS tinyint) AS PayTypeId,
--''	AS PayType,
--''	AS ChequeNo,	
--''	AS BankName,
--''	AS Address1,
--''	AS Address2,
--''	AS FullTitle,
--CAST(0.0 AS decimal(12,2)) AS TotalReceiptAmount,
--CAST(0.0 AS decimal(12,2)) AS ReceiptAmount,					
--''	AS ReceiptAmountInWords,		
--CAST(0.0 AS decimal(12,2)) AS DiscountAmount,
--''	AS Remark,
--''	AS UserName,		
--0x  AS OrgLogo,
--''	AS Remark1,
--''	AS Remark2,			 
--CAST(0 AS bit) AS IsOnPrePrinted,
--CAST(0 AS bit) AS IsSupressLogo,
--CAST(0 AS bit) AS IsDisplaySchoolName,
--0	AS ReportId,
--''	AS OrganizationName,		  
--''	AS MonthDetail,    			
--''	AS FeeDuration,		
--''	AS DepartmentName,
-- 0	AS FeeOrderIndex,
--CAST(0 AS smallint)	AS FeeHeadOrderIndex,
--CAST(0 AS bit) AS IsCancelReceipt,
--''	AS CurrencyCode,
--''	AS CurrencyTitle,
--''	AS Batch,
--CAST('' AS date) AS ChequeDate,
--CAST(0 AS bit) AS IsMonthDetailOnly,
--CAST(0 AS bit) AS IsFeeInvoiceHeadwise,
--''	AS GroupTitle,
--CAST(0 AS bit) AS IsSuppressFeeDetail,
--''	AS CollectionGroupName,
--0	AS InvoiceNumber,
--''	AS SUID,
--CAST(0 AS tinyint) AS HeaderDisplayId,
--0x  AS ReceiptBookHeader,
--CAST(0.0 AS decimal(6,2)) AS ServiceTaxPercentage,
--CAST(0.0 AS decimal(12,2)) AS AmountAfterDiscount,
--CAST(0.0 AS decimal(5,2)) AS CGSTPercentage,
--CAST(0.0 AS decimal(5,2)) AS SGSTPercentage,
--CAST(0.0 AS decimal(12,2)) AS CGSTAmount,
--CAST(0.0 AS decimal(12,2))	AS SGSTAmount,
--''	AS GSTINNo,
--''	AS HSNSACCode,
--''	AS ItemValue ,
--''	AS FatherName,
--''	AS MotherName,
--CAST(0.0 AS decimal(12,2)) AS ExemptionAmount,
--''	AS ExemptionRemark,
--0x AS EmployeeSign,
--''	AS PresentCity,
--''	AS PresentAdress,
--''	AS ParentContactNo,	
--CAST(0 AS bit) AS IsDisplayPendingFeeAmount,
--CAST(0.0 AS decimal(12,0)) AS PendingFeeAmount,
--''	AS FirstName,
--''	AS LastName,
--CAST(0.0 AS decimal(12,0))	AS FeeAmount,
--CAST(0.0 AS decimal(12,0)) AS ExemptionAmountSum,
--''	AS TransportBusRoute,
--''	AS BankAccountNumber,
--0	AS FeeHeadNumber,
--0	AS FeeNumber,
--0x AS DigitalSignature,
--''	AS CancelStatusRemark,
--CAST(0.0 AS decimal(12,2))	AS FeeSetupAmount,
--CAST('' AS datetime) AS CreatedDateTime ,
--CAST(0 AS bigint)	AS PaymentTransactionId,
--''	AS MiddleName,
--'' AS CreatedTime,
--'' AS Gender,
--CAST(0.0 AS decimal(12,2)) AS NextInstallmentAmount,
--0 AS NextInstallmentId,
--CAST('' AS date) AS NextInstallmentStartDate,
--CAST('' AS date) AS NextInstallmentEndDate,
--'' AS NextInstallmentName		
--END
GO
PRINT N'Creating [sSecurity].[pCloneUserAccessAdd]...';


GO



CREATE PROCEDURE [sSecurity].[pCloneUserAccessAdd]
	
	@rUserId bigint,
	@rCloneUserId bigint
	
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	
BEGIN 
	BEGIN TRY
	
	 BEGIN TRANSACTION	
	 
	 DELETE FROM sSecurity.tUserOrganization WHERE UserId=@rCloneUserId 
	 --AND OrgId=@rOrgId
	 DELETE FROM sSecurity.tUserAccess WHERE UserId=@rCloneUserId

	   INSERT INTO sSecurity.tUserOrganization
		(
			UserId,
			OrgId
		)
		SELECT
		@rCloneUserId,
        OrgId
	
		FROM sSecurity.tUserOrganization UO WHERE UserId = @rUserId

		INSERT INTO sSecurity.tUserAccess
		(
			SourceTypeId,
			SourceId,
			UserId
		)
		SELECT
		SourceTypeId,
		SourceId,
		@rCloneUserId
      
		FROM sSecurity.tUserAccess WHERE UserId = @rUserId 
	
	SELECT 1 AS Result
	
	COMMIT TRANSACTION
	
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO


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