

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

PRINT N'Altering Table [sIp].[tStoreLocationMaster]...';


GO
ALTER TABLE [sIp].[tStoreLocationMaster]
    ADD [DepositHeadId]        INT      NULL,
        [DepositExpenseTypeId] INT      NULL,
        [ReportId]             SMALLINT NULL;


GO
PRINT N'Altering Table [sProcurement].[tStockIssue]...';


GO
ALTER TABLE [sProcurement].[tStockIssue]
    ADD [PayTypeId]     TINYINT        NULL,
        [ReceiptNumber] INT            NULL,
        [BankName]      NVARCHAR (200) NULL,
        [ChequeNo]      NVARCHAR (40)  NULL,
        [ChequeDate]    DATE           NULL;


GO
PRINT N'Creating Table [sAlert].[tAlertQueueLog]...';


GO
CREATE TABLE [sAlert].[tAlertQueueLog] (
    [AutoId]          BIGINT         IDENTITY (1, 1) NOT NULL,
    [MessageDate]     DATETIME       NOT NULL,
    [QueueMessage]    NVARCHAR (MAX) NOT NULL,
    [EventId]         INT            NOT NULL,
    [AlertModeId]     TINYINT        NOT NULL,
    [OrgId]           SMALLINT       NOT NULL,
    [CreatedUserId]   BIGINT         NOT NULL,
    [CreatedDateTime] DATETIME       NOT NULL
);


GO
PRINT N'Creating Table [sProcurement].[tStockKitDetail]...';


GO
CREATE TABLE [sProcurement].[tStockKitDetail] (
    [StockKitId]  INT             NOT NULL,
    [StockItemId] INT             NOT NULL,
    [Quantity]    DECIMAL (12, 2) NOT NULL
);


GO
PRINT N'Creating Table [sProcurement].[tStockKitIssueDetail]...';


GO
CREATE TABLE [sProcurement].[tStockKitIssueDetail] (
    [StockIssueId] BIGINT NOT NULL,
    [StockKitId]   INT    NOT NULL
);


GO
PRINT N'Creating Table [sProcurement].[tStockKitMaster]...';


GO
CREATE TABLE [sProcurement].[tStockKitMaster] (
    [OrgGroupId]      SMALLINT       NOT NULL,
    [Id]              INT            NOT NULL,
    [Number]          INT            NOT NULL,
    [Code]            NVARCHAR (20)  NOT NULL,
    [Title]           NVARCHAR (100) NOT NULL,
    [CreatedUserId]   BIGINT         NOT NULL,
    [CreatedDateTime] DATETIME       NOT NULL,
    [OrderIndex]      SMALLINT       NOT NULL,
    [IsObsolete]      BIT            NOT NULL,
    [UpdatedUserId]   BIGINT         NULL,
    [UpdatedDateTime] DATETIME       NULL,
    [StoreLocationId] INT            NOT NULL
);


GO


GO
ALTER PROCEDURE [sIp].[pStoreLocationMasterModify]
	@rId [int],
	@rTitle [nvarchar](100),
	@rIsObsolete [bit],
	@rUpdatedUserId [bigint],
	@rCode [nvarchar](100),	
	@rOrgGroupId int,
	@rIsBatchwiseTracking bit = 0,
	@rDepositHeadId int=0,
	@rDepositExpenseTypeId int=0,
	@rReportId smallint=0


WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
IF NOT EXISTS (SELECT 1 FROM sIP.tStoreLocationMaster WHERE UPPER(Title) = UPPER(@rTitle) AND Id  <> @rId AND OrgGroupId = @rOrgGroupId)
BEGIN

	 UPDATE sIP.tStoreLocationMaster
		SET 
			Title = @rTitle,
			IsObsolete = @rIsObsolete,
			UpdatedUserId = @rUpdatedUserId,
			UpdatedDateTime = sCommon.fGetDate(),
		    Code = @rCode,
		    IsBatchwiseTracking = @rIsBatchwiseTracking,
			DepositHeadId=@rDepositHeadId,
			DepositExpenseTypeId=@rDepositExpenseTypeId,
			ReportId=@rReportId
			
	  WHERE Id = @rId AND OrgGroupId = @rOrgGroupId
	  
	  
SELECT 1 AS RESULT
END

ELSE

 BEGIN
	 SELECT -100 AS RESULT
 END
 
END
GO
PRINT N'Altering Procedure [sIp].[pStoreLocationMasterAdd]...';


GO
ALTER PROCEDURE [sIp].[pStoreLocationMasterAdd]
	@rTitle [nvarchar](100),
	@rCreatedUserId [bigint],
	@rIsObsolete [bit],
	@rCode [nvarchar](100),
	@rOrgGroupId int,
	@rIsBatchwiseTracking bit = 0,
	@rDepositHeadId int=0,
	@rDepositExpenseTypeId int=0,
	@rReportId smallint=0
 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN

IF NOT EXISTS (SELECT 1 FROM sIp.tStoreLocationMaster WHERE UPPER(Title) = UPPER(@rTitle)  AND OrgGroupId = @rOrgGroupId )


BEGIN

	DECLARE @StockItemId INT
	DECLARE @lCount INT 
	DECLARE @rStockBatchId INT
	DECLARE @lNextNumber INT
	--EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sIp.tStoreLocationMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sIp.tStoreLocationMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
	

	INSERT INTO sIp.tStoreLocationMaster
		(
			Id,
			Title,
			CreatedUserId,
			CreatedDateTime,
			IsObsolete,
			UpdatedUserId,
			UpdatedDateTime,
			Code,
			LocationId, 
			OrgGroupId,
		 	IsBatchwiseTracking,
			DepositHeadId,
			DepositExpenseTypeId,
			ReportId
		)
	VALUES
		(
			@lNextNumber,
			@rTitle,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rIsObsolete,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rCode,
			@lNextNumber,
			@rOrgGroupId,
			@rIsBatchwiseTracking,
			@rDepositHeadId,
			@rDepositExpenseTypeId,
			@rReportId
			 
		)
		
	  INSERT INTO sSecurity.tUserAccess
           (
           SourceTypeId
           ,SourceId
           ,[UserId]
           
           )
		   SELECT 
		   1014
		   ,@lNextNumber
		   ,@rCreatedUserId		
		
--CREATE TABLE #tmpStockItemMaster
--(
--	Id bigint identity,
--	StockItemId int
--)	 
		 
--INSERT INTO #tmpStockItemMaster
--SELECT Id FROM sIP.tStockItemMaster WHERE LocationId = @rLocationId

--SELECT @lCount =  COUNT(Id) FROM #tmpStockItemMaster

--WHILE @lCount > 0 
-- 	BEGIN

-- 		SELECT  @StockItemId = StockItemId 
-- 	   FROM #tmpStockItemMaster WHERE Id = @lCount	
	
-- 	EXECUTE sProcurement.pStockBatchAdd @rOrgGroupId,@lNextNumber,@StockItemId,null,null,0,@rCreatedUserId ,  @rStockBatchId
--		SET @lCount = @lCount - 1
--	END	
 	
	--EXECUTE	sIp.pStockItemPurchaseUomAdd @rOrgId,@lNextNumber,@rIssueStockUomId,1,0,@rCreatedUserId	
	----EXEC [sIp].[pItemStandardPriceLogAdd] @rOrgId,@lNextNumber, @rStandardPrice, @rCreatedUserId
 --   SELECT @lNextNumber AS RESULT
    
    
 --END
		
	SELECT 1 AS RESULT
END
ELSE
 BEGIN
 SELECT -100 AS RESULT
 END
END
GO
PRINT N'Altering Procedure [sIp].[pStoreLocationMasterGet]...';


GO
ALTER PROCEDURE [sIp].[pStoreLocationMasterGet]
	@rId [int] = 0,
	@rTitle [nvarchar](20) = '',
	@rCode [nvarchar](100) = '',
	@rLocationId [int] = 0,
	@rOrgGroupId int,
	@rIsWildCardSearch [bit] = 1

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN

	IF @rIsWildCardSearch = 1
		BEGIN
			SET @rCode = sCommon.fSearchStringGet(@rCode)
			SET @rTitle = sCommon.fSearchStringGet(@rTitle)
		END
		
	


	 SELECT SLM.Id,
			SLM.Title,
			SLM.CreatedUserId,
			SLM.CreatedDateTime,
			SLM.IsObsolete,
			SLM.UpdatedUserId,
			SLM.UpdatedDateTime,
			SLM.Code,		
			SLM.OrgGroupId,
			OM.Title AS OrgGroupName,
			SLM.Title AS StoreLocationName,
			ISNULL(SLM.IsBatchwiseTracking,0) AS IsBatchwiseTracking,
			Isnull(SLM.DepositHeadId,0) as DepositHeadId,
			Isnull(SLM.DepositExpenseTypeId,0) as DepositExpenseTypeId,
			Isnull(SLM.ReportId,0) as ReportId

	   FROM sIp.tStoreLocationMaster SLM 	   
	   INNER JOIN sSetup.tOrgGroupMaster OM ON OM.Id=SLM.OrgGroupId
	  WHERE ((SLM.Id = @rId) OR (@rId = 0))
	   AND ((SLM.Title LIKE @rTitle) OR (ISNULL(@rTitle, '') = ''))
	   AND ((SLM.Code LIKE @rCode) OR (ISNULL(@rCode, '') = ''))	  
	   AND  (SLM.OrgGroupId = @rOrgGroupId)  
	   --AND SLM.IsObsolete = 0
END



/****** Object:  StoredProcedure [sExamination].[pIssueReturnDataGet]    Script Date: 06/15/2013 16:42:58 ******/
SET ANSI_NULLS ON
GO
PRINT N'Altering Procedure [sProcurement].[pStockIssueStudentAdd_New]...';


GO
/******************************************************************************
	Purpose          : To add records to sProcurement.tStockIssue table
	Created date     : 09 Jan 2012
	Author           : System Generated
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Added @rStockIssueRequestId field
	Test script      : EXECUTE sProcurement.pStockIssueAdd @rNumber = '', @rStockIssueRequestId = 0, @rIssuedByName = '', @rToOrganizationDepartmentId = 0, @rFromStoreLocationId = 0, @rRemark = '', @rCreatedUserId = 0, @rDetail = ''
*******************************************************************************/
ALTER PROCEDURE [sProcurement].[pStockIssueStudentAdd_New]
(
	@rOrgGroupId [int],
	@rNumber						nvarchar(20) = '',
	@rStockIssueRequestId			int=0, 
	@rIssuedByName					nvarchar(100) = '',
	@rToOrganizationDepartmentId	int,
	@rFromStoreLocationId			int,
	@rFirstAidId					int,
	@rCreatedUserId					bigint,
	@rDetail						Nvarchar(Max)='',
	@rOrgId int,
	@rLocationId int,
	@rIssuedToName nvarchar(50)='',
	@rIssueToSourceTypeId int,
	@rIssueToSourceId bigint,
	@rIssueDate datetime = null,
    @rCreatedOnDate datetime = null ,
	@rPayTypeId tinyint=null,
	@rReceiptNumber int=null,
	@rBankName  nvarchar(200)=null,
	@rChequeNo  nvarchar(40)=null,
	@rChequeDate Date=null
)
AS

SET NOCOUNT ON

BEGIN
EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgGroupId, @rPrefixFunctionalityId = 26,@rStoreLocationId = @rFromStoreLocationId, @rNextNumber  = @rNumber OUTPUT

SET @rNumber = ISNULL(@rNumber, '-300')

IF @rNumber = '-300'
BEGIN
 
SELECT -300 AS Result, '' AS Number, 0 AS Id 
RETURN
END
 
	  DECLARE @lNextNumberStockIssue INT
	  DECLARE @loXML XML
		SET @loXML = CAST(@rDetail AS XML)
	  DECLARE @tStockIssueDetail TABLE
		(
			Id Int IDENTITY(1,1),StockItemId Int,IssuedQuantity Decimal(12,2)
		)

	  INSERT INTO @tStockIssueDetail
		SELECT
		T.c.value('@StockItemId','int') AS StockItemId,
		T.c.value('@IssuedQuantity','decimal(12,2)') AS IssuedQuantity 
		FROM @loXML.nodes('/Table/StockIssue')T(c)
	 
	 BEGIN TRANSACTION
	 
	EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sProcurement.tStockIssue',@lNextNumber = @lNextNumberStockIssue output



If @rFirstAidId < 0 
SET @rFirstAidId =  NULL

	INSERT INTO sProcurement.tStockIssue
		(
			Id,
			Number,
			StockIssueRequestId, 
			IssuedByName,
			ToOrganizationDepartmentId,
			FromStoreLocationId,
			FirstAidId,
			Remark,
			CreatedUserId,
			CreatedDateTime,
			OrgGroupId,
			IssueToName,
			IssueToSourceTypeId,
			IssueToSourceId,
			TransactionDate,
			PayTypeId,
			ReceiptNumber,
			BankName,
			ChequeNo,
			ChequeDate
		)
	VALUES
		(
			@lNextNumberStockIssue,
			@rNumber,
		   (CASE WHEN @rStockIssueRequestId = 0 THEN NULL ELSE @rStockIssueRequestId END),	 
			@rIssuedByName,
			@rToOrganizationDepartmentId,
			@rFromStoreLocationId,
			@rFirstAidId,
			'',
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rOrgGroupId,
			@rIssuedToName,
			@rIssueToSourceTypeId,
			@rIssueToSourceId,
			@rIssueDate,
			@rPayTypeId,
			@rReceiptNumber,
			@rBankName,
			@rChequeNo,
			@rChequeDate
		)


	DECLARE @lNextNumberStockIssueDetail BigInt,@lQuantity decimal(12,2),@lStockItemCode nvarchar(50)
		Declare @lCOunt int , @lItemId int,@lIssueQuantity decimal(12,2),
		@lbatchId int,@lIsBatchWise bit,@lBatch nvarchar(300),@lNextNumberStockBatch int,@lNextNumberBatch int,
		@lWeightedAverageCost decimal(12,2),@lInwardValue decimal(12,2),@lStockItemName nvarchar(50),
		@lNextNumberOtherStockBatch bigint
		Select @lCOunt = count(*) from @tStockIssueDetail
		
		IF @lcount = 0
		BEGIN
			ROLLBACK TRANSACTION
			Select -211 As Result,@lStockItemCode AS StockItemCode
			Return
		END
		
		while @lcount>0
		BEGIN
			Select @lItemId = StockItemId , @lIssueQuantity = IssuedQuantity ,@lCOunt=Id
			from @tStockIssueDetail where Id=@lCOunt
			
			Select @lWeightedAverageCost=ISNULL(WeightedAverageCost,0) from sIp.tStockItemPlanning
			Where StockItemId = @lItemId AND StoreLocationId =  @rFromStoreLocationId  
			 
			Select @lQuantity=AvailableQuantity,@lbatchId=Id from sProcurement.tStockBatch where  StockItemId = @lItemId ANd StorelocationId=@rFromStoreLocationId
			  
			if(ISNULL(@lbatchId,0)=0)
			BEGIN
				ROLLBACK TRANSACTION
				Select -102 As Result,@lStockItemCode AS StockItemCode
				Return
			END 
			IF @lQuantity = 0 
			BEGIN
				ROLLBACK TRANSACTION
				Select -103 AS Result,@lStockItemCode AS StockItemCode
				return
			END
			IF @lQuantity < @lIssueQuantity 
			BEGIN
				ROLLBACK TRANSACTION
				Select -101 AS Result,@lStockItemCode AS StockItemCode
				return
			END
			
			IF Exists(Select Id from sProcurement.tStockIssueDetail where StockIssueId = @lNextNumberStockIssue and StockItemId = @lItemId)					
			BEGIN
				Select @lNextNumberStockIssueDetail= Id from sProcurement.tStockIssueDetail
				Where StockIssueId = @lNextNumberStockIssue AND StockIteMid =@lItemId					 
			END	
			ELSE
			BEGIN
				EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sProcurement.tStockIssueDetail', 0, 1, 1,@outNextNumber = @lNextNumberStockIssueDetail output

				INSERT INTO sProcurement.tStockIssueDetail
				(
					Id,StockIssueId,StockItemId,IssuedQuantity,RequestedQuantity,
					StockIssueRequestDetailId,Remark,OrgGroupId
				)
				SELECT @lNextNumberStockIssueDetail,@lNextNumberStockIssue,StockItemId,IssuedQuantity,
				0,NULL,'',@rOrgGroupId
				from @tStockIssueDetail where Id=@lcount	
			END
			EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sProcurement.tStockIssueBatch', 0, 1, 1,@outNextNumber = @lNextNumberBatch output

			INSERT INTO sProcurement.tStockIssueBatch
			(
				Id,StockIssueDetailId,StockBatchId,Quantity,OrgGroupId
			)
		Select @lNextNumberBatch,@lNextNumberStockIssueDetail,@lbatchId,@lIssueQuantity,@rOrgID

		SET @lInwardValue = ISNULL(@lWeightedAverageCost,0) * ISNULL(@lIssueQuantity,0)
		
		Exec sIp.pWeightedAverageCostLogAdd_New @rOrgGroupId =@rOrgGroupId,@rSourceTypeId=51,@rSourceId=@lNextNumberBatch,
			@rItemSourceTypeId = 34,@rItemSourceId=@lItemId,@rQuantity=@lIssueQuantity ,@rValue=@lInwardValue,@rIsReceipt=0,
			@rCreatedUserId = @rCreatedUserId,@rStoreLocationId = @rFromStoreLocationId ,@rLocationId=@rLocationId,
			@rItemBatchId = @lbatchId,@rItemName =@lStockItemName,@rOrgId=@rOrgId
		set @lcount =@lcount-1
END
	--DECLARE @lIntErrorCode INT, @lIntDoc INT
	--EXEC @lIntErrorCode = sp_xml_preparedocument @lIntDoc OUTPUT, @rDetail	
		
	--DECLARE @tStockIssueDetail TABLE
	--(
	--Id						Int IDENTITY(0, 1),
	--StockIssueDetailId		Int, 
	--StockIssueRequestDetailId	Int, 
	--StockIssueId 			Int, 
	--StockItemId				Int, 
	--IssuedQuantity			Decimal(12,2),
	--RequestedQuantity		Decimal(12,2),  
	--Remark					Nvarchar(300)
	--)
	
	--INSERT INTO @tStockIssueDetail  
	--(  
	--	StockIssueId,
	--	StockItemId,
	--	IssuedQuantity,
	--	RequestedQuantity,
	--	StockIssueRequestDetailId, 
	--	Remark
	--)
	--SELECT	@lNextNumberStockIssue, 
	--		StockItemId,
	--		IssuedQuantity,
	--		RequestedQuantity,
	--		(CASE WHEN StockIssueRequestDetailId = 0 THEN NULL ELSE StockIssueRequestDetailId END), 			
	--		Remark
			
	--FROM OPENXML (@lIntDoc, '/Root/StockIssueDetail', 1)  
	--	WITH  
	--	(  
	--		StockItemId				Nvarchar(300)	'@StockItemId', 
	--		IssuedQuantity			Nvarchar(300)	'@IssuedQuantity', 
	--		RequestedQuantity		Nvarchar(300)	'@RequestedQuantity', 
	--		StockIssueRequestDetailId  Nvarchar(300)'@StockIssueRequestDetailId', 
	--		Remark					Nvarchar(300)	'@Remark'
	--	) XmlTable
	
	--DECLARE @lNextNumberStockIssueDetail BigInt
	--EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sProcurement.tStockIssueDetail', 0, 1, 1,@outNextNumber = @lNextNumberStockIssueDetail output
	
	
	--INSERT INTO sProcurement.tStockIssueDetail
	--	(
	--		Id,
	--		StockIssueId,
	--		StockItemId,
	--		IssuedQuantity,
	--		RequestedQuantity,
	--		StockIssueRequestDetailId, 
	--		Remark,
	--		OrgGroupId
	--	)
	--SELECT 	@lNextNumberStockIssueDetail + Id, 
	--		StockIssueId, 
	--		StockItemId, 
	--		IssuedQuantity,
	--		RequestedQuantity, 
	--		StockIssueRequestDetailId, 
	--		Remark,
	--		@rOrgGroupId
	--FROM	@tStockIssueDetail
	
	--UPDATE @tStockIssueDetail SET StockIssueDetailId = @lNextNumberStockIssueDetail + Id 
	--SELECT @lNextNumberStockIssueDetail = MAX(Id) + @lNextNumberStockIssueDetail FROM @tStockIssueDetail
	--EXECUTE sCommon.pTableRowIdModify @rOrgGroupId = @rOrgGroupId, @rTableTitle = 'sProcurement.tStockIssueDetail', @rCurrentNumber = @lNextNumberStockIssueDetail 
	
	--DECLARE @tBatch TABLE
	--(
	--	Id						Int IDENTITY(0, 1), 
	--	StockIssueDetailId	Int,
	--	StockBatchId Int,		
	--	Quantity				Decimal(12,2),
	--	StockItemId				Int				
	--)
	
	--INSERT INTO @tBatch  
	--		(  
	--		StockIssueDetailId,	
	--		StockBatchId, 
	--		Quantity,
	--		StockItemId									
	--		)
	--SELECT	1, 
	--		StockBatchId, 
	--		Quantity,
	--		StockItemId
	--FROM OPENXML (@lIntDoc, '/Root/StockIssueDetail/StockIssueBatch', 1)  
	--	WITH  
	--	(  
	--		StockBatchId 	Nvarchar(200)	'@StockBatchId', 
	--		Quantity					Nvarchar(200)	'@Quantity',
	--		StockItemId					Nvarchar(200)	'@StockItemId' 			
	--	) XmlTable	
		
		
	--DECLARE @lNextNumberBatch BIGINT
	--EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sProcurement.tStockIssueBatch', 0, 1, 1,@outNextNumber = @lNextNumberBatch output
	
	--INSERT INTO sProcurement.tStockIssueBatch
	--	(
	--		Id,
	--		StockIssueDetailId,
	--		StockBatchId,
	--		Quantity,
	--		OrgGroupId
	--	)
	--SELECT 		@lNextNumberBatch + TD.Id, 
	--			STD.StockIssueDetailId, 
	--			StockBatchId, 
	--			Quantity,
	--			@rOrgGroupId
	--FROM		@tStockIssueDetail STD 
	--INNER JOIN	@tBatch TD 
	--			ON TD.StockItemId = STD.StockItemId
				
	--SELECT @lNextNumberBatch = MAX(Id) + @lNextNumberBatch FROM @tBatch
	--EXECUTE sCommon.pTableRowIdModify @rOrgGroupId = @rOrgGroupId, @rTableTitle = 'sProcurement.tStockIssueBatch', @rCurrentNumber = @lNextNumberBatch 
	
	SELECT 1 AS Result, @rNumber AS Number, @lNextNumberStockIssue AS Id 
		COMMIT TRANSACTION
END
GO
PRINT N'Altering Procedure [sProcurement].[pStockIss_Get]...';


GO
ALTER PROCEDURE [sProcurement].[pStockIss_Get]
	@rOrgGroupId int,
	@rNumber [nvarchar](20) = '',
	@rFromDate date='',
	@rToDate date='',
	@rStoreLocationId int=0,
	@rAcademicId bigint=0,
	@rId int=0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN	  
SELECT		
SI.Id AS  StockId,SI.Number,isnull(SI.StockIssueRequestId,0) as StockIssueRequestId,
SI.IssuedByName,Convert(varchar,SI.TransactionDate,103) AS IssuedDate,
SM.FirstName AS StudentName,SID.IssuedQuantity as Quantity,
MS.Title,IG.Title As StockItemName,IG.Id as ItemSubGroupId,SI.ToOrganizationdepartmentid,
MG.Id As MainGroupId,MS.Id As StockItemId,
ISNULL(SI.PayTypeId,14) as PayTypeId,
ISNULL(SI.ReceiptNumber,0) as ReceiptNumber,
ISNULL(SI.BankName,'') as BankName,
ISNULL(SI.ChequeNo,'') as ChequeNo,
ISNULL(SI.ChequeDate,'') as ChequeDate


FROM sProcurement.tStockIssue SI
INNER JOIN sProcurement.tStockIssueDetail SID ON SI.Id=SID.StockIssueId
INNER JOIN sStudent.tAcademic A ON A.Id=SI.IssueToSourceId AND SI.IssueToSourceTypeId=2001
INNER JOIN sStudent.tStudentMaster SM on SM.Id=A.StudentID
INNER JOIN sIp.tStockItemMaster MS ON MS.id=SID.StockItemId
INNER JOIN sIp.tItemSubGroupMaster IG on IG.Id=MS.ItemSubGroupId
INNER JOIN sIp.tItemMainGroupMaster MG on MG.iD=IG.ItemMainGroupId 
 
WHERE 
(( SI.Number like '%' + @rNumber + '%' ) OR (@rNumber = '*'))
AND  SI.OrgGroupId = @rOrgGroupId
AND (( Convert(Date,SI.CreatedDateTime)>= @rFromDate ) OR (ISNULL(@rFromDate,'')=''))
AND (( Convert(Date,SI.CreatedDateTime)<= @rToDate ) OR (ISNULL(@rToDate,'')=''))
AND SI.FromStoreLocationId=@rStoreLocationId 
AND ((SI.IssueToSourceId =@rAcademicId) OR (@rAcademicId=0))
Order by SI.CreatedDateTime desc
END
GO
PRINT N'Altering Procedure [sProcurement].[pStockIssSearch_Get]...';


GO

ALTER PROCEDURE [sProcurement].[pStockIssSearch_Get]
	@rOrgGroupId int,
	@rNumber [nvarchar](20) = '',
	@rFromDate date='',
	@rToDate date='',
	@rStoreLocationId int=0,
	@rAcademicId bigint=0,
	@rId int=0,
	@rPayTypeId int=0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN	  
SELECT		
SI.Id AS  StockId,SI.Number,isnull(SI.StockIssueRequestId,0) as StockIssueRequestId,
SI.IssuedByName,SI.CreatedDateTime AS IssuedDate,
IssueToSourceId

FROM sProcurement.tStockIssue SI
 
 
WHERE ((SI.Number like '%' + @rNumber + '%') OR (@rNumber = '*'))
AND  SI.OrgGroupId = @rOrgGroupId
AND (isnull(PayTypeId,14)=@rPayTypeId OR isnull(@rPayTypeId,0)=0)
AND (( Convert(Date,SI.CreatedDateTime)>= @rFromDate ) OR (ISNULL(@rFromDate,'')=''))
AND (( Convert(Date,SI.CreatedDateTime)<= @rToDate ) OR (ISNULL(@rToDate,'')=''))
AND SI.FromStoreLocationId=@rStoreLocationId 
AND ((SI.IssueToSourceId =@rAcademicId) OR (@rAcademicId=0))
AND SI.IssueToSourceTypeId = 2001
Order by SI.CreatedDateTime desc
END
GO
PRINT N'Altering Procedure [sAlert].[pAbsentStudentGet]...';


GO
ALTER PROCEDURE [sAlert].[pAbsentStudentGet]

	 	 @rBatchId int,
	 	 @rClassId int,
	 	 @rDivisionId int,
	 	 @rOrgId int,
	 	 @rTemplate nvarchar(max),
	 	 @rAttendanceTypeId tinyint,
	 	 @rSubjectId int,
	 	 @rFromDate date,
	 	 @rToDate date,
	 	 @rIsPresent bit,
	 	 @rMailSubject nvarchar(500)=''

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

--SET @rToDate = dateadd(d,1,@rToDate)

	Select
			SC.AcademicId,
			SC.Code,
			SC.Name,
			SC.ParentContactNo AS MobileNo,
			SC.ParentEmail AS EmailId,
			ISNULL(SC.StudentContactNo,'') AS StudentContactNo,
			ISNULL(SC.StudentEmail,'') AS StudentEmail,
			ISNULL(SC.FatherContactNo,'') AS FatherContactNo,
			ISNULL(SC.FatherMail,'') AS FatherMail,
			ISNULL(SC.MotherContactNo,'') AS MotherContactNo,
			ISNULL(SC.MotherMail,'') AS MotherMail,
			CASE WHEN @rIsPresent=0 THEN
			replace (replace (replace (						--add replace
			replace(replace(replace(@rTemplate,'[Name]',SC.Name),
			'[Class]',SC.ClassName),
			'[Division]',SC.DivisionName),
			'[AbsentDates]',Convert(Varchar,SR.AttendanceDate,105)),	--add convert
			'[AbsentReason]',ISNULL(AR.Title,'')),
			'[RollNo]',ISNULL(SC.RollNo,0))								--add
			ELSE
			replace(replace(											--add replace
			replace(replace(replace(@rTemplate,'[Name]',SC.Name),
			'[Class]',SC.ClassName),
			'[Division]',SC.DivisionName),
			'[PresentDates]',Convert(Varchar,SR.AttendanceDate,105)),	--add Convert
			'[RollNo]',ISNULL(SC.RollNo,0))								--add
			END
			 AS MsgText,
			@rMailSubject AS Subject,
			SC.AcademicId AS SourceId,
			2001 AS SourceTypeId,
			SC.OrgId,
			SC.OrgGroupId,
			SC.AcademicId AS ReceiverSourceId,
			2001 AS ReceiverSourceTypeId,
			SC.ClassName AS Class,
			SC.DivisionName AS Division,
			SR.AttendanceDate AS AbsentDates,
			ISNULL(AR.Title,'') AS AbsentReason,
			SR.AttendanceDate AS PresentDates,
			SC.WhatsAppContactNo

	From
		sStudent.vStudentCommunication SC
	INNER JOIN sAttendance.tStudentRegister SR ON SR.AcademicId=SC.AcademicId
	LEFT JOIN sAttendance.tAbsentReasonMaster AR ON SR.AbsentReasonId = AR.Id

	WHERE
			((SC.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SC.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND (SC.OrgId=@rOrgId)
		AND (SC.BatchId=@rBatchId)
		AND (SC.StatusId=1)
		AND ((SR.AttendanceTypeId=@rAttendanceTypeId) OR (@rAttendanceTypeId=0))
			AND ((SR.SubjectId=@rSubjectId) OR (@rSubjectId=0))
			AND ((SR.AttendanceDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
			AND ((SR.AttendanceDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
			AND (SR.IsPresent = @rIsPresent)
		AND	SC.AcademicId NOT IN (SELECT AcademicId FROM sAttendance.tStudentLeaveRequest WHERE LeaveFromDate = @rFromDate AND LeaveToDate=@rToDate)
		AND SC.IsBlockAttendanceAlert=0 AND SC.IsBlockAllAlert=0
   ORDER BY SC.ClassOrderIndex,SC.DivisionOrderIndex,SC.Rollno
END
GO
PRINT N'Altering Procedure [sAssessment].[pMarkEntryConvertForDifferentSubject]...';


GO

ALTER PROCEDURE [sAssessment].[pMarkEntryConvertForDifferentSubject]  
	 	
	 
	@rClassId int, 
	@rDivisionId int,
	@rSourceExamDetailIdList nvarchar(500),
	@rOrgId smallint,
	@rBatchId int,
	@rDestinationExamDetailId  int=0,
	@rBasedOnPresent int = 0 	
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @loDoc INT,@lIsMarksInDecimal bit
Declare @lDecimalFormat nvarchar(10)
select @lDecimalFormat = DecimalFormat from sAssessment.tExamMaster where Id =(Select ExamId  from sAssessment.tExamDetail WHere Id = @rDestinationExamDetailId)
select @lIsMarksInDecimal = IsMarksInDecimal from sAssessment.tExamMaster where Id= (Select ExamId  from sAssessment.tExamDetail WHere Id = @rDestinationExamDetailId)
BEGIN
	 
	 BEGIN	
	 
	 
	--select @lDecimalFormat = DecimalFormat from sAssessment.tExamMaster where Id =@rExamId;
	 
	CREATE Table #MarkEntrySubjectWise_CTE ( AcademicId  bigint,  SourceObtain decimal(5,2),SourceMaximum decimal(5,2),IsPresent int)
 
-- Define the CTE query.
	INSERT INTO #MarkEntrySubjectWise_CTE 
	(
		AcademicId,
	 
		SourceObtain,
		SourceMaximum,
		IsPresent
		--ClassSubjectId
	)
	
   Select 			
		
		R.AcademicId,	
		 	 
		SUM(RD.Obtain)AS SourceObtain,
		SUM(ED.Maximum) AS SourceMaximum,
		(CASE WHEN @rBasedOnPresent = 0 THEN MIN(CONVERT(INT,RD.IsPresent)) ELSE MAX(CONVERT(INT,RD.IsPresent)) END)AS IsPresent
		--ED.ClassSubjectId		
	 
	From 
			sAssessment.tResultDetail RD 
			INNER JOIN sAssessment.tResult R ON R.Id=RD.ResultId  	
			INNER JOIN sStudent.tAcademic A ON A.Id=R.AcademicId 
			INNER JOIN sAssessment.tExamMaster EM ON EM.Id = R.ExamId
			INNER JOIN sAssessment.tExamDetail ED ON RD.ExamDetailId=ED.Id AND ED.ExamId = EM.Id			 			 			  			 			 
			 Where
			 A.ClassId = @rClassId AND A.BatchId=@rbatchId 
			 AND ED.Id IN (SELECT  cast(item as int) AS ExamDetailID FROM  sCommon.fnsplit( REPLACE(REPLACE(@rSourceExamDetailIdList,'(',''),')','') ,','))  
			Group By R.AcademicId 
		 
		--Select * from #MarkEntrySubjectWise_CTE 


	DECLARE @lNextNumber BIGINT 
	DECLARE @lResultId BIGINT
	DECLARE @lCount INT
	DECLARE @lMarkInDecimal bit
	Declare @lExamId BigInt
	DECLARE @Query nvarchar(max)
	DECLARE @lMaxId Bigint
	Declare @lHigh1 decimal(5,2),@lHigh2 decimal(5,2)
	Declare @lCountMark int,@lSum_Highest decimal(5,2),@lSum decimal(5,2)
	Declare @lMaximum decimal(5,2)
	Select @lMaximum = Maximum from sAssessment.tExamDetail where Id = @rDestinationExamDetailId

	DECLARE @lDestinationExamId bigint
	SELECT @lDestinationExamId = ExamId FROM sAssessment.tExamDetail WHERE Id = @rDestinationExamDetailId

	IF NOT EXISTS (SELECT 1 FROM sAssessment.tResult R INNER JOIN sStudent.tAcademic A ON A.Id = R.AcademicId AND A.BatchId=@rBatchId
				AND R.ExamId=@lDestinationExamId AND A.ClassId=@rClassId AND (A.DivisionId=@rDivisionId OR @rDivisionId=0))		
	BEGIN
		 
		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAssessment.tResult', 0, 1, 1,@outNextNumber = @lNextNumber output	
		
		INSERT INTO sAssessment.tResult (Id,ExamId,AcademicId,TotalMarks,TotalObtain,ResultStatus,ResultGrade,ResultGradePoint,
		RankNumber,Percentage,Percentile,ResultRemark,OtherRemark1,UpdatedUserId,UpdatedDateTime) 
		
		SELECT @lNextNumber + ROW_NUMBER() OVER(ORDER BY A.AcademicId) AS Id,@lDestinationExamId,A.AcademicId,0,0,'','',0,0,0,0,'','',1,sCommon.fGetDate()
		From sStudent.vStudentAcademic A
		Where A.ClassId=@rClassId AND (A.DivisionId=@rDivisionId OR @rDivisionId=0) AND A.StatusId=1 AND A.BatchId=@rBatchId
		
		SELECT @lCount = COUNT(1) FROM sStudent.vStudentAcademic WHERE ClassId=@rClassId AND (DivisionId=@rDivisionId OR @rDivisionId =0) AND BatchId=@rBatchId
		SET @lNextNumber  = @lCount + @lNextNumber 
		
		EXECUTE sCommon.pTableRowIdModify @rOrgId, @rTableTitle = 'sAssessment.tResult', @rCurrentNumber = @lNextNumber
			
	END

	IF EXISTS (SELECT 1 FROM sAssessment.tResultDetail RD 
    INNER JOIN sAssessment.tResult R ON R.Id=RD.ResultId AND R.ExamId=@lDestinationExamId
    INNER JOIN sStudent.vStudentAcademic A ON A.AcademicId=R.AcademicId AND A.ClassId = @rClassId AND A.BatchId=@rBatchId
    AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId = 0))
	AND (RD.ExamDetailId = @rDestinationExamDetailId OR @rDestinationExamDetailId = 0)
    )
    BEGIN	
	
	  UPDATE RD SET RD.OBTAIN  =
								(CASE WHEN  (@lIsMarksInDecimal = 1 AND @lDecimalFormat='0.0') THEN
									Round((MC.SourceObtain * ED.Maximum)/MC.SourceMaximum,1) 
								WHEN ( @lIsMarksInDecimal = 1 AND @lDecimalFormat='0.00' ) THEN
									CONVERT(Decimal(5,2),(MC.SourceObtain * ED.Maximum)/MC.SourceMaximum) 
								ELSE			  
									ROUND((MC.SourceObtain * ED.Maximum)/MC.SourceMaximum,0)
								END),RD.IsPresent=MC.IsPresent
	  
	  FROM	  
			sAssessment.tResultDetail RD 
			INNER JOIN sAssessment.tResult R ON RD.ResultId = R.Id 
			INNER JOIN sAssessment.tExamDetail  ED ON RD.ExamDetailId = ED.Id
			INNER JOIN #MarkEntrySubjectWise_CTE MC ON R.AcademicId = MC.AcademicId 
			AND RD.ExamDetailId = @rDestinationExamDetailId 
				
	Select 11 AS Result
	END
	ELSE
	BEGIN

		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAssessment.tResultDetail', 0, 1, 1,@outNextNumber = @lNextNumber output

			INSERT INTO sAssessment.tResultDetail
							 (Id,ResultId,ExamDetailId,IsPresent,Obtain) 
						SELECT
							@lNextNumber  + ROW_number()OVER(Order by R.AcademicId) AS Id,
							R.Id as ResultId,
							@rDestinationExamDetailId AS ExamDetailId,
							--ED1.Id AS ExamDetailId,
							A.IsPresent AS IsPresent,
							--A.Obtain 
							((ED1.Maximum * A.SourceObtain) / A.SourceMaximum) AS Obtain
							from 
					  	 sAssessment.tResult R 
					  	INNER JOIN sAssessment.tExamMaster EM ON EM.Id = R.ExamId
					    INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = R.AcademicId  
						INNER JOIN sAssessment.tExamDetail ED1 ON ED1.ExamId = EM.Id 
						INNER JOIN( 
									SELECT   AcademicId,SourceObtain,SourceMaximum,IsPresent
									FROM #MarkEntrySubjectWise_CTE 
									--group by   AcademicId,
									----ClassSubjectId,
									--SourceObtain,SourceMaximum,IsPresent
								)A   ON R.AcademicId= A.AcademicId  
								--AND A.ClassSubjectId = ED1.ClassSubjectId						
				 		 
						 WHERE SA.ClassId = @rClassId AND  ((SA.DIVISIONID= @rDivisionId ) OR (@rDivisionId = 0))
						AND R.ExamId = @lDestinationExamId AND ED1.Id = @rDestinationExamDetailId
									
		SELECT @lMaxId=MAx(RD.Id)   FROM sAssessment.tResultDetail RD
		INNER JOIN sAssessment.tResult R On R.Id = RD.ResultId
		INNER JOIN sAssessment.tExamMaster EM ON EM.Id =R.ExamId
		INNER JOIN sSetup.tBatchMaster BM ON BM.ID = EM.BatchId 
		WHERE BM.OrgId = @rOrgId
		  
		EXECUTE sCommon.pTableRowIdModify @rOrgId, @rTableTitle = 'sAssessment.tResultDetail', @rCurrentNumber = @lMaxId 
		 Select 1 As Result
	END
END

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


GO

ALTER PROCEDURE [sAssessment].[pMarkEntryConvertForSubject]  
	 	
	 
	@rClassId int, 
	@rDivisionId int,
	@rSourceExamDetailIdList nvarchar(500),
	@rOrgId smallint,
	@rBatchId int,
	@rDestinationExamDetailId  int=0 ,
	@rBasedOnPresent int = 0 	

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @loDoc INT,@lIsMarksInDecimal bit
Declare @lDecimalFormat nvarchar(10)
select @lDecimalFormat = DecimalFormat from sAssessment.tExamMaster where Id =(Select ExamId  from sAssessment.tExamDetail WHere Id = @rDestinationExamDetailId)
select @lIsMarksInDecimal = IsMarksInDecimal from sAssessment.tExamMaster where Id= (Select ExamId  from sAssessment.tExamDetail WHere Id = @rDestinationExamDetailId)
BEGIN
	 
	 BEGIN	
	 
	 
	--select @lDecimalFormat = DecimalFormat from sAssessment.tExamMaster where Id =@rExamId;
	 
	CREATE Table #MarkEntrySubjectWise_CTE ( AcademicId  bigint, ClassSubjectId int,SourceObtain decimal(5,2),SourceMaximum decimal(5,2),IsPresent int)
 
-- Define the CTE query.
	INSERT INTO #MarkEntrySubjectWise_CTE 
	(
		AcademicId,
		ClassSubjectId,
		SourceObtain,
		SourceMaximum,
		IsPresent
	)
	
   Select 			
		
		R.AcademicId,	
		ED.ClassSubjectId,	 
		SUM(RD.Obtain)AS SourceObtain,
		SUM(ED.Maximum) AS SourceMaximum,
		(CASE WHEN @rBasedOnPresent = 0 THEN MIN(CONVERT(INT,RD.IsPresent)) ELSE MAX(CONVERT(INT,RD.IsPresent)) END)AS IsPresent
	 
	From 
			sAssessment.tResultDetail RD 
			INNER JOIN sAssessment.tResult R ON R.Id=RD.ResultId  	
			INNER JOIN sStudent.tAcademic A ON A.Id=R.AcademicId 
			INNER JOIN sAssessment.tExamMaster EM ON EM.Id = R.ExamId
			INNER JOIN sAssessment.tExamDetail ED ON RD.ExamDetailId=ED.Id AND ED.ExamId = EM.Id			 			 			  			 			 
			 Where
			 A.ClassId = @rClassId AND A.BatchId=@rbatchId 
			 AND ED.Id IN (SELECT  cast(item as int) AS ExamDetailID FROM  sCommon.fnsplit( REPLACE(REPLACE(@rSourceExamDetailIdList,'(',''),')','') ,','))  
			Group By R.AcademicId,ED.ClassSubjectId 
		 
		--Select * from #MarkEntrySubjectWise_CTE 
	--Select ExamId  from sAssessment.tExamDetail WHere Id = @rDestinationExamDetailId

	 DECLARE @lNextNumber BIGINT 
	 DECLARE @lResultId BIGINT
	 DECLARE @lCount INT
	 DECLARE @lMarkInDecimal bit
	 Declare @lExamId BigInt
	 DECLARE @Query nvarchar(max)
	 DECLARE @lMaxId Bigint
	 Declare @lHigh1 decimal(5,2),@lHigh2 decimal(5,2)
	 Declare @lCountMark int,@lSum_Highest decimal(5,2),@lSum decimal(5,2)
	 Declare @lMaximum decimal(5,2)
	 Select @lMaximum = Maximum from sAssessment.tExamDetail where Id = @rDestinationExamDetailId

	 DECLARE @lDestinationExamId bigint
	 SELECT @lDestinationExamId = ExamId FROM sAssessment.tExamDetail WHERE Id = @rDestinationExamDetailId

	  IF NOT EXISTS (SELECT 1 FROM sAssessment.tResult R INNER JOIN sStudent.tAcademic A ON A.Id = R.AcademicId AND A.BatchId=@rBatchId
				AND R.ExamId=@lDestinationExamId AND A.ClassId=@rClassId AND (A.DivisionId=@rDivisionId OR @rDivisionId=0))		
	BEGIN
		 
		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAssessment.tResult', 0, 1, 1,@outNextNumber = @lNextNumber output	
		
		INSERT INTO sAssessment.tResult (Id,ExamId,AcademicId,TotalMarks,TotalObtain,ResultStatus,ResultGrade,ResultGradePoint,
		RankNumber,Percentage,Percentile,ResultRemark,OtherRemark1,UpdatedUserId,UpdatedDateTime) 
		
		SELECT @lNextNumber + ROW_NUMBER() OVER(ORDER BY A.AcademicId) AS Id,@lDestinationExamId,A.AcademicId,0,0,'','',0,0,0,0,'','',1,sCommon.fGetDate()
		From sStudent.vStudentAcademic A
		Where A.ClassId=@rClassId AND (A.DivisionId=@rDivisionId OR @rDivisionId=0) AND A.StatusId=1 AND A.BatchId=@rBatchId
		
		SELECT @lCount = COUNT(1) FROM sStudent.vStudentAcademic WHERE ClassId=@rClassId AND (DivisionId=@rDivisionId OR @rDivisionId =0) AND BatchId=@rBatchId
		SET @lNextNumber  = @lCount + @lNextNumber 
		
		EXECUTE sCommon.pTableRowIdModify @rOrgId, @rTableTitle = 'sAssessment.tResult', @rCurrentNumber = @lNextNumber
			
	END

	IF EXISTS (SELECT 1 FROM sAssessment.tResultDetail RD 
    INNER JOIN sAssessment.tResult R ON R.Id=RD.ResultId AND R.ExamId=@lDestinationExamId
    INNER JOIN sStudent.vStudentAcademic A ON A.AcademicId=R.AcademicId AND A.ClassId = @rClassId AND A.BatchId=@rBatchId
    AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId = 0))
	AND (RD.ExamDetailId = @rDestinationExamDetailId OR @rDestinationExamDetailId = 0)
    )
    BEGIN
	  UPDATE RD SET RD.OBTAIN  =
								(CASE WHEN  (@lIsMarksInDecimal = 1 AND @lDecimalFormat='0.0') THEN
									Round((MC.SourceObtain * ED.Maximum)/MC.SourceMaximum,1) 
								WHEN ( @lIsMarksInDecimal = 1 AND @lDecimalFormat='0.00' ) THEN
									CONVERT(Decimal(5,2),(MC.SourceObtain * ED.Maximum)/MC.SourceMaximum) 
								ELSE			  
									ROUND((MC.SourceObtain * ED.Maximum)/MC.SourceMaximum,0)
								END),RD.IsPresent=MC.IsPresent
	  
	   --Convert(Decimal(5,2),(MC.SourceObtain * ED.Maximum)/MC.SourceMaximum)
	  FROM	  
			sAssessment.tResultDetail RD 
			INNER JOIN sAssessment.tResult R ON RD.ResultId = R.Id 
			INNER JOIN sAssessment.tExamDetail  ED ON RD.ExamDetailId = ED.Id
			INNER JOIN #MarkEntrySubjectWise_CTE MC ON R.AcademicId = MC.AcademicId AND ED.ClassSubjectId = MC.ClassSubjectId
			AND RD.ExamDetailId = @rDestinationExamDetailId 
	 
	
	Select 11 AS Result
   END
    ELSE
	BEGIN
		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAssessment.tResultDetail', 0, 1, 1,@outNextNumber = @lNextNumber output
		
		INSERT INTO sAssessment.tResultDetail
							 (Id,ResultId,ExamDetailId,IsPresent,Obtain) 
						SELECT  
							@lNextNumber  + ROW_number()OVER(Order by R.Id) AS Id,
							 
							R.Id as ResultId,
							@rDestinationExamDetailId AS ExamDetailId,
							A.IsPresent AS IsPresent,
							--A.Obtain 
							((ED1.maximum * A.Obtain) / A.Maximum) AS Obtain
							from 
					  	 sAssessment.tResult R 
					  	INNER JOIN sAssessment.tExamMaster EM ON EM.Id = R.ExamId
					    INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = R.AcademicId  
						INNER JOIN sAssessment.tExamDetail ED1 ON ED1.ExamId = EM.Id 
						INNER JOIN( 
									SELECT   AcademicId,ClassSubjectId,SourceObtain AS Obtain,SourceMaximum AS Maximum,IsPresent
									FROM #MarkEntrySubjectWise_CTE 
									--GROUP BY  AcademicId,ClassSubjectId,SourceObtain,SourceMaximum,IsPresent
								)A   ON R.AcademicId= A.AcademicId  AND A.ClassSubjectId = ED1.ClassSubjectId						
				 		 
						 WHERE SA.ClassId = @rClassId AND  ((SA.DIVISIONID= @rDivisionId ) OR (@rDivisionId = 0))
						 AND R.ExamId = @lDestinationExamId AND ED1.Id = @rDestinationExamDetailId
									
		SELECT @lMaxId=MAX(RD.Id)   FROM sAssessment.tResultDetail RD
		INNER JOIN sAssessment.tResult R On R.Id = RD.ResultId
		INNER JOIN sAssessment.tExamMaster EM ON EM.Id =R.ExamId
		INNER JOIN sSetup.tBatchMaster BM ON BM.ID = EM.BatchId 
		WHERE BM.OrgId = @rOrgId
		  
		EXECUTE sCommon.pTableRowIdModify @rOrgId, @rTableTitle = 'sAssessment.tResultDetail', @rCurrentNumber = @lMaxId 
		 Select 1 As Result
		 
   END

   DROP TABLE #MarkEntrySubjectWise_CTE
END

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


GO

ALTER PROCEDURE [sAssessment].[pRptExamAdmitcard]
	  
	  @rBatchId int,
	  @rClassId int,
	  @rDivisionId int,
	 -- @rExamId int=0,
	  @rExamSubGroupId int=0,
	  @rCode nvarchar(20),
	  @rFromRollNo int,
	  @rToRollNo int,
	  @rDepartmentId int,
	  @rSubjectId int=0,
	  @rStatusId int=0,
	  @rClassStatusId int=0
	  
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON 
BEGIN 	
	SELECT 		
	       	ESGM.Title AS ExamName,
	       	ISNULL(ED.DisplayOrder,0) as DisplayOrder,
	        ISNULL(ED.ExamDate,sCommon.fGetDate()) as ExamDate,
	        ISNULL(ED.ExamTime,'') as ExamTime,
	        V.SubjectName AS SubjectName,
	        CM.DisplayTitle+' / '+DM.DisplayTitle AS ClassDivision,
	         CM.DisplayTitle AS ClassName,
	         CM.Id As ClassId,
	         DM.Id AS DiVisionId,
	        V.ClassSubjectId AS ClassSubjectId,
	        SA.Name,
	        SA.RollNo,
	        SA.Code,
	        SF.FatherName,
	        So.DateOfBirth,
	        GM.Title AS Gender,
	        EM.Remark1,
	        EM.Remark2,
	        EM.Remark3,
	        SA.AcademicId,
	        SI.StudentPhoto,
	        SF.MotherName,
	        SR. ExamCenter,
	        SR.ExamSeatNo,
	        V.SubjectCode AS SubjectCode,
		
	         
		(CASE EM.IsDisplayImageHeader WHEN 1 THEN
			OI.CertificateHeaderA4 
			ELSE NULL END)AS  MarksheetHeaderA4 ,
			ISNULL(EM.IsDisplayImageHeader,0) AS IsImageHeader,
			EM.IsOnPrePrinted ,
	        --EM.Id As ExamId,
			EM.IsDisplayPhoto,
			SR.ExamBlock,
			EI.EmployeeSign,
			EM.TeacherSignCaption,
			EM.PrincipalSignCaption,
			EM.ParentSignCaption,
			ISNULL(EM.DisplayTitle,'') AS DisplayTitle ,
			ISNULL(ED.ExamTopic,'') AS ExamTopic,
			ISNULL(ED.ExamLanguage,'') AS ExamLanguage,
			ISNULL(SA.OrganizationName,'') AS OrganizationName,
			ISNULL(SA.SUId,'') AS SUId ,
			EM.IsDisplayPrincipalSign AS IsDisplayPrincipalSign,
			ISNULL(SO.UIDNumber,'') AS UId ,
			CSM.Title As CasteCategory ,
		    ISNULL(SO.AdhaarNumber,'') AS AdhaarNumber,
			ISNULL(SO.FamilySubCast,'') AS FamilySubCast

			FROM
			sStudent.vStudentAcademic SA 
			INNER JOIN sSetup.tClassMaster CM ON SA.ClassId=CM.Id 
			INNER JOIN sSetup.tDivisionMaster DM ON DM.ClassId=CM.Id AND SA.DivisionId=DM.Id
			INNER JOIN sAssessment.tExamMaster EM ON EM.ClassId = CM.Id  ANd Em.BatchId=SA.BatchId 
			INNER JOIN sAssessment.tExamSubGroupMaster	ESGM ON ESGM.Id = EM.ExamSubGroupId
			INNER JOIN sAssessment.tExamGroupMaster EGM ON EGM.Id=ESGM.ExamGroupId
			INNER JOIN sAssessment.tExamDetail ED ON ED.ExamId=EM.Id  
			LEFT JOIN sSetup.vClassSubject V ON V.ClassSubjectId = Ed.ClassSubjectId  
			LEFT JOIN sAssessment.tOptionalSubject OS on OS.AcademicId=SA.AcademicId and OS.ClassSubjectId=V.ClassSubjectId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=SA.StudentId  
			LEFT JOIN sStudent.tStudentOther SO ON So.StudentId=SA.StudentId  
			LEFT JOIN sCommon.tGenderMaster GM ON GM.Id=So.GenderId   
			LEFT JOIN  sStudent.tStudentImages SI ON SI.StudentId=SA.StudentId 
			LEFT JOIN sAssessment.tStudentRemark SR ON SR.ExamId=EM.Id AND SR.AcademicId=SA.AcademicId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
			LEFT JOIN sSetup.tOrgImages OI ON OI.Orgid = OM.Id
			LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId = DM.InchargeEmployeeId
			LEFT JOIN sCommon.tCastCategoryMaster CSM ON CSM.Id=SO.CastCategoryId
	WHERE 
			SA.BatchId = @rBatchId 
		 AND (SA.ClassId=@rClassId OR @rClassId=0) 
		 AND (SA.DivisionId=@rDivisionId OR @rDivisionId=0)
		 AND ((SA.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))
		 AND ((SA.RollNo <= @rToRollNo) OR (@rToRollNo=0))
		 AND ((ESGM.Id = @rExamSubGroupId) OR (@rExamSubGroupId = 0))	
	     AND ((SA.Code=@rCode) OR (@rCode=''))
	     AND (SA.DepartmentId=@rDepartmentId OR @rDepartmentId=0) 
	     AND (V.SubjectId=@rSubjectId OR @rSubjectId=0) 
	    AND ((V.IsOptional = 1 AND  isnull(OS.AcademicId,0) <> 0) OR (V.IsOptional = 0))
		AND ((SA.StatusId=@rStatusId) OR (@rStatusId = 0))
		AND (SA.ClassStatusId=@rClassStatusId OR @rClassStatusId=0)
	
	Order by ED.ExamDate
END
GO
PRINT N'Altering Procedure [sAudit].[pActivityLogGet]...';


GO


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

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


GO
 
ALTER PROCEDURE [sFee].[pApplicablePayTypeGet]
	   @rOrgGroupId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT  FPT.Id as PayTypeId,
			FPT.Title as PayType,
			CASE WHEN APT.PayTypeId Is Null THEN 'False' ELSE 'True' END AS IsObsolete
		
  FROM sFinanceCommon.tPayTypeMaster FPT
	LEFT JOIN sFee.tApplicablePayType APT ON APT.PayTypeId=FPT.Id AND OrgGroupId=@rOrgGroupId
	Where FPT.Id not in (12,13,14)
END
GO
PRINT N'Altering Procedure [sFee].[pFeeCollectionConsolidatedDashboardGet]...';


GO
ALTER PROCEDURE [sFee].[pFeeCollectionConsolidatedDashboardGet]

     @rOrgGroupId smallint=0,
     @rTypeId int=0,
     @rType nvarchar(20)='',
     @rCommonOrgGroupBatchId int=0,
	 @rFromDate date =null,
	 @rToDate date = null,
	 @rReceiptBookId int =0,
	 @rUserId bigint=0,
	 @rOrgId int =0,
	 @rCommonFeeId int =0
	 
	 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 
--If @rFromDate= NULL Set @rFromDate = ''
--  If @rToDate =NULL Set @rToDate = ''
 DECLARE @cols NVARCHAR(MAX),@query  NVARCHAR(MAX),@cols1 NVARCHAR(MAX), @cols2 NVARCHAR(MAX); 
	---Fee Collection Wise  
			IF(@rTypeId=1)
				BEGIN
					SET @cols = STUFF((SELECT ',' +  QUOTENAME(PTM.Title )
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols1 = STUFF((SELECT ', SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) ' + QUOTENAME(PTM.Title) 
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')					

					SET @cols2 = STUFF((SELECT '+ SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) ' 
					FROM sFinanceCommon.tPayTypeMaster PTM
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')	

					set @query = 'SELECT OrgGroupId,OrgGroupName,' + @cols2 + ' AS Total, ' + @cols1 + ' from 
					(
					SELECT  OMM.Id AS OrgGroupId ,OMM.Title AS OrgGroupName,SUM(R.ReceiptAmount) AS Total,PTM.Title AS PayTypeName, COALESCE(SUM(R.ReceiptAmount),0) AS ReceiptAmount FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN  sFinanceCommon.tPayTypeMaster PTM  ON PTM.Id=R.PayTypeId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId	
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id='+CONVERT(nvarchar(20),@rCommonOrgGroupBatchId)+' AND R.SourceTypeId = 2001 
					AND((OMM.Id='+CONVERT(nvarchar(20),@rOrgGroupId)+') OR ('+CONVERT(nvarchar(20),@rOrgGroupId)+'=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= '''+CONVERT(nvarchar(20),@rFromDate)+''') OR ('''+CONVERT(nvarchar(20),@rFromDate)+'''=''1900-01-01''))
					AND (( convert(DATE, R.ReceiptDate,102) <= '''+CONVERT(nvarchar(20),@rToDate)+''') OR ('''+CONVERT(nvarchar(20),@rToDate)+'''=''1900-01-01''))
					AND R.IsObsolete = 0
					And ISNULL(OMM.IsObsolete,0) = 0
					AND BM.IsInContext = 1 
					GROUP BY OMM.Title,OMM.Id,PTM.Title
					) x
					pivot 
					(
					Sum(ReceiptAmount) 
					for PayTypeName in (' + @cols + ')
					) p GROUP BY OrgGroupId,OrgGroupName  ORDER BY OrgGroupId'
					--print(@query)
					execute(@query)
				END
			ELSE IF( @rTypeId=11)
				BEGIN
					SET @cols = STUFF((SELECT ',' +  QUOTENAME(PTM.Title )
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols1 = STUFF((SELECT ', SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) ' + QUOTENAME(PTM.Title) 
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols2 = STUFF((SELECT '+ SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) ' 
					FROM sFinanceCommon.tPayTypeMaster PTM
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					set @query = 'SELECT ReceiptBookId,ReceiptBook,'+@cols2+' AS Total, ' + @cols1 + ' from 
					(
					SELECT RBM.Id AS ReceiptBookId,RBM.Title AS ReceiptBook,SUM(R.ReceiptAmount) AS Total,PTM.Title AS PayTypeName, COALESCE(SUM(R.ReceiptAmount),0) AS ReceiptAmount FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id='+CONVERT(nvarchar(20),@rCommonOrgGroupBatchId)+' AND R.SourceTypeId = 2001 
					AND((OMM.Id='+CONVERT(nvarchar(20),@rOrgGroupId)+') OR ('+CONVERT(nvarchar(20),@rOrgGroupId)+'=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= '''+CONVERT(nvarchar(20),@rFromDate)+''') OR ('''+CONVERT(nvarchar(20),@rFromDate)+'''=''1900-01-01''))
					AND (( convert(DATE, R.ReceiptDate,102) <= '''+CONVERT(nvarchar(20),@rToDate)+''') OR ('''+CONVERT(nvarchar(20),@rToDate)+'''=''1900-01-01''))
					AND R.IsObsolete = 0
					And ISNULL(OMM.IsObsolete,0) = 0
					AND BM.IsInContext = 1
					GROUP BY RBM.Title,RBM.Id,PTM.Title
					) x
					pivot 
					(
					Sum(ReceiptAmount) 
					for PayTypeName in (' + @cols + ')
					) p GROUP BY ReceiptBookId,ReceiptBook ORDER BY ReceiptBookId'					
					--print(@query)
					execute(@query)
				 END
			ELSE IF( @rTypeId=12)
				BEGIN
						SET @cols = STUFF((SELECT ',' +  QUOTENAME(PTM.Title )
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols1 = STUFF((SELECT ', SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) ' + QUOTENAME(PTM.Title) 
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols2 = STUFF((SELECT '+ SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) '
					FROM sFinanceCommon.tPayTypeMaster PTM
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					set @query = 'SELECT UserId,UserName,SourceTypeId,' + @cols2 + ' AS Total, ' + @cols1 + ' from 
					(
					SELECT  U.Id As UserId,U.DisplayTitle AS UserName,ISNULL(U.SourceTypeId,0) AS SourceTypeId,SUM(R.ReceiptAmount) AS Total,PTM.Title AS PayTypeName, COALESCE(SUM(R.ReceiptAmount),0) AS ReceiptAmount FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					LEFT JOIN sSecurity.tUser U ON U.Id=R.CreatedUserId
					WHERE  COGBM.Id='+CONVERT(nvarchar(20),@rCommonOrgGroupBatchId)+' AND R.SourceTypeId = 2001 
					AND((OMM.Id='+CONVERT(nvarchar(20),@rOrgGroupId)+') OR ('+CONVERT(nvarchar(20),@rOrgGroupId)+'=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= '''+CONVERT(nvarchar(20),@rFromDate)+''') OR ('''+CONVERT(nvarchar(20),@rFromDate)+'''=''1900-01-01''))
					AND (( convert(DATE, R.ReceiptDate,102) <= '''+CONVERT(nvarchar(20),@rToDate)+''') OR ('''+CONVERT(nvarchar(20),@rToDate)+'''=''1900-01-01''))
					AND BM.IsInContext = 1
					GROUP BY U.id,U.DisplayTitle,PTM.Title,U.SourceTypeId
					) x
					pivot 
					(
					Sum(ReceiptAmount) 
					for PayTypeName in (' + @cols + ')
					) p GROUP BY UserId,UserName,SourceTypeId ORDER BY UserId'
					print(@query)
					execute(@query)
				END
			ELSE IF (@rTypeId=13)
				BEGIN
					SET @cols = STUFF((SELECT ',' +  QUOTENAME(PTM.Title )
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols1 = STUFF((SELECT ', SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) ' + QUOTENAME(PTM.Title) 
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols2 = STUFF((SELECT '+ SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) '
					FROM sFinanceCommon.tPayTypeMaster PTM
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					set @query = 'SELECT OrgGroupId,OrgGroupName,' + @cols2 + ' AS Total, ' + @cols1 + ' from 
					(
					SELECT  OM.Id AS OrgGroupId ,OM.DisplayTitle AS OrgGroupName,SUM(R.ReceiptAmount) AS Total,PTM.Title AS PayTypeName, COALESCE(SUM(R.ReceiptAmount),0) AS ReceiptAmount FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id='+CONVERT(nvarchar(20),@rCommonOrgGroupBatchId)+' AND R.SourceTypeId = 2001 
					AND((OMM.Id='+CONVERT(nvarchar(20),@rOrgGroupId)+') OR ('+CONVERT(nvarchar(20),@rOrgGroupId)+'=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= '''+CONVERT(nvarchar(20),@rFromDate)+''') OR ('''+CONVERT(nvarchar(20),@rFromDate)+'''=''1900-01-01''))
					AND (( convert(DATE, R.ReceiptDate,102) <= '''+CONVERT(nvarchar(20),@rToDate)+''') OR ('''+CONVERT(nvarchar(20),@rToDate)+'''=''1900-01-01''))
					AND BM.IsInContext = 1
					GROUP BY OM.DisplayTitle,OM.Id,PTM.Title
					) x
					pivot 
					(
					Sum(ReceiptAmount) 
					for PayTypeName in (' + @cols + ')
					) p GROUP BY OrgGroupId,OrgGroupName ORDER BY OrgGroupId '
					print(@query)
					execute(@query)
				END
			ELSE IF (@rTypeId=14)
				BEGIN
					SET @cols = STUFF((SELECT ',' +  QUOTENAME(PTM.Title )
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols1 = STUFF((SELECT ', SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) ' + QUOTENAME(PTM.Title) 
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols2 = STUFF((SELECT '+ SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) '
					FROM sFinanceCommon.tPayTypeMaster PTM
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					set @query = 'SELECT FeeHeadId,FeeHead,' + @cols2 + ' AS Total, ' + @cols1 + ' from 
					(
					SELECT  FHM.Id AS FeeHeadId,FHM.Title AS FeeHead,SUM(RD.Amount) AS total,PTM.Title AS PayTypeName,ISNULL(SUM(RD.Amount),0) AS ReceiptAmount FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFee.tReceiptDetail RD ON R.Id = RD.ReceiptId
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = RD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id='+CONVERT(nvarchar(20),@rCommonOrgGroupBatchId)+' AND R.SourceTypeId = 2001 
					AND((OMM.Id='+CONVERT(nvarchar(20),@rOrgGroupId)+') OR ('+CONVERT(nvarchar(20),@rOrgGroupId)+'=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= '''+CONVERT(nvarchar(20),@rFromDate)+''') OR ('''+CONVERT(nvarchar(20),@rFromDate)+'''=''1900-01-01''))
					AND (( convert(DATE, R.ReceiptDate,102) <= '''+CONVERT(nvarchar(20),@rToDate)+''') OR ('''+CONVERT(nvarchar(20),@rToDate)+'''=''1900-01-01''))
					AND BM.IsInContext = 1
					GROUP BY FHM.Id,FHM.Title,PTM.Id,PTM.Title
					) x
					pivot 
					(
					Sum(ReceiptAmount) 
					for PayTypeName in (' + @cols + ')
					) p GROUP BY FeeHeadId,FeeHead ORDER BY FeeHeadId'
					
					execute(@query)
				END
			ELSE IF (@rTypeId=15)
				BEGIN
					SET @cols = STUFF((SELECT ',' +  QUOTENAME(PTM.Title )
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols1 = STUFF((SELECT ', SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) ' + QUOTENAME(PTM.Title) 
					FROM sFinanceCommon.tPayTypeMaster PTM
					--INNER JOIN  sFee.tReceipt R  ON PTM.Id=R.PayTypeId
					--INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					SET @cols2 = STUFF((SELECT '+ SUM(ISNULL(' + QUOTENAME(PTM.Title) + ', ''0'')) '
					FROM sFinanceCommon.tPayTypeMaster PTM
					GROUP BY PTM.Title
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

					set @query = 'SELECT FeeHeadId,FeeMaster,' + @cols2 + ' AS Total, ' + @cols1 + ' from 
					(
					SELECT FM.Id AS FeeHeadId,FM.Title AS FeeMaster,SUM(RD.Amount) AS total,PTM.Title AS PayTypeName,ISNULL(SUM(RD.Amount),0) AS ReceiptAmount FROM sFee.tReceipt R
						INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
						INNER JOIN sFee.tReceiptDetail RD ON R.Id = RD.ReceiptId
						INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
						INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
						INNER JOIN sFee.tFeeSetup FS ON FS.Id = RD.FeeSetupId
						INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
						INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
						INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
						INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
						INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
						INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
						INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
						WHERE  COGBM.Id='+CONVERT(nvarchar(20),@rCommonOrgGroupBatchId)+' AND R.SourceTypeId = 2001 
						AND((OMM.Id='+CONVERT(nvarchar(20),@rOrgGroupId)+') OR ('+CONVERT(nvarchar(20),@rOrgGroupId)+'=0))
						AND (( convert(DATE, R.ReceiptDate,102) >= '''+CONVERT(nvarchar(20),@rFromDate)+''') OR ('''+CONVERT(nvarchar(20),@rFromDate)+'''=''1900-01-01''))
						AND (( convert(DATE, R.ReceiptDate,102) <= '''+CONVERT(nvarchar(20),@rToDate)+''') OR ('''+CONVERT(nvarchar(20),@rToDate)+'''=''1900-01-01''))
						AND BM.IsInContext = 1
						GROUP BY FM.Id,FM.Title,PTM.Id,PTM.Title
					) x
					pivot 
					(
					Sum(ReceiptAmount) 
					for PayTypeName in (' + @cols + ')
					) p GROUP BY FeeHeadId,FeeMaster  ORDER BY FeeHeadId'
					execute(@query)
				END

			ELSE IF(@rTypeId =21)
				BEGIN
				IF NOT EXISTS(SELECT 1 FROM sFee.tApplicablePayType WHERE OrgGroupId = @rOrgGroupId)
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,R.ReceiptAmount AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id= @rCommonOrgGroupBatchId  AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					AND ((R.ReceiptBookId =@rReceiptBookId )OR(@rReceiptBookId=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND R.IsObsolete = 0
					And ISNULL(OMM.IsObsolete,0) = 0
					AND BM.IsInContext = 1
					ORDER BY R.ReceiptNumber -- Added By Foram - 02/08/2023 - Mantis Issue: 0026499
				END
				ELSE
				BEGIN
				SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,R.ReceiptAmount AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFee.tApplicablePayType PTM ON PTM.PayTypeId=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id= @rCommonOrgGroupBatchId  AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					AND ((R.ReceiptBookId =@rReceiptBookId )OR(@rReceiptBookId=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND R.IsObsolete = 0
					And ISNULL(OMM.IsObsolete,0) = 0
					AND BM.IsInContext = 1
					ORDER BY R.ReceiptNumber -- Added By Foram - 02/08/2023 - Mantis Issue: 0026499
				END
				END
			ELSE IF(@rTypeId =22)
				BEGIN
				IF NOT EXISTS(SELECT 1 FROM sFee.tApplicablePayType WHERE OrgGroupId = @rOrgGroupId)
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,R.ReceiptAmount AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					LEFT JOIN sSecurity.tUser U ON U.Id=R.CreatedUserId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id= @rCommonOrgGroupBatchId  AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					--AND ((U.Id =@rUserId )OR(@rUserId=0))
					AND ((U.Id = CASE WHEN @rUserId = 2003 THEN 0 ELSE @rUserId END)OR(CASE WHEN @rUserId = 2003 THEN 0 ELSE @rUserId END=0))
					AND ((U.SourceTypeId = CASE WHEN @rUserId = 2003 THEN 2003 ELSE 0 END)OR(CASE WHEN @rUserId = 2003 THEN 2003 ELSE 0 END=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND BM.IsInContext = 1
				END
				ELSE
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,R.ReceiptAmount AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFee.tApplicablePayType PTM ON PTM.PayTypeId=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					LEFT JOIN sSecurity.tUser U ON U.Id=R.CreatedUserId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id= @rCommonOrgGroupBatchId  AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					--AND ((U.Id =@rUserId )OR(@rUserId=0))
					AND ((U.Id = CASE WHEN @rUserId = 2003 THEN 0 ELSE @rUserId END)OR(CASE WHEN @rUserId = 2003 THEN 0 ELSE @rUserId END=0))
					AND ((U.SourceTypeId = CASE WHEN @rUserId = 2003 THEN 2003 ELSE 0 END)OR(CASE WHEN @rUserId = 2003 THEN 2003 ELSE 0 END=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND BM.IsInContext = 1
				END
				END
			ELSE IF(@rTypeId =23)
				BEGIN
				IF NOT EXISTS(SELECT 1 FROM sFee.tApplicablePayType WHERE OrgGroupId = @rOrgGroupId)
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,R.ReceiptAmount AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id= @rCommonOrgGroupBatchId  AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					AND ((OM.Id =@rOrgId )OR(@rOrgId=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND BM.IsInContext = 1
				END
				ELSE
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,R.ReceiptAmount AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFee.tApplicablePayType PTM ON PTM.PayTypeId=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id= @rCommonOrgGroupBatchId  AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					AND ((OM.Id =@rOrgId )OR(@rOrgId=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND BM.IsInContext = 1
				END
				END
			ELSE IF(@rTypeId =24)
				BEGIN
				IF NOT EXISTS(SELECT 1 FROM sFee.tApplicablePayType WHERE OrgGroupId = @rOrgGroupId)
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,SUM(RD.Amount) AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFee.tReceiptDetail RD ON R.Id = RD.ReceiptId
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = RD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id= @rCommonOrgGroupBatchId  AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					AND ((FHM.Id =@rCommonFeeId)OR(@rCommonFeeId=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND BM.IsInContext = 1
					GROUP BY  R.ReceiptNumber,R.ReceiptDate,SA.Code, SA.Name 
				END
				ELSE
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,SUM(RD.Amount) AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFee.tReceiptDetail RD ON R.Id = RD.ReceiptId
					INNER JOIN sFee.tApplicablePayType PTM ON PTM.PayTypeId=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = RD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  COGBM.Id= @rCommonOrgGroupBatchId  AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					AND ((FHM.Id =@rCommonFeeId)OR(@rCommonFeeId=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					GROUP BY  R.ReceiptNumber,R.ReceiptDate,SA.Code, SA.Name
				END
				END
			ELSE IF(@rTypeId =25)
				BEGIN
				IF NOT EXISTS(SELECT 1 FROM sFee.tApplicablePayType WHERE OrgGroupId = @rOrgGroupId)
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,SUM(RD.Amount) AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFee.tReceiptDetail RD ON R.Id = RD.ReceiptId
					INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = RD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  
					COGBM.Id=@rCommonOrgGroupBatchId
					AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					AND ((FM.Id =@rCommonFeeId)OR(@rCommonFeeId=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND BM.IsInContext = 1
					GROUP BY  R.ReceiptNumber,R.ReceiptDate,SA.Code, SA.Name 
				END
				ELSE
				BEGIN
					SELECT  R.ReceiptNumber AS ReceiptNumber,R.ReceiptDate AS ReceiptDate,SA.Code AS Code, SA.Name AS Name,SUM(RD.Amount) AS Amount
					FROM sFee.tReceipt R
					INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=R.SourceId 
					INNER JOIN sFee.tReceiptDetail RD ON R.Id = RD.ReceiptId
					INNER JOIN sFee.tApplicablePayType PTM ON PTM.PayTypeId=R.PayTypeId
					INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = RD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
					INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
					INNER JOIN sSetup.tOrgGroupMaster OMM ON OMM.Id = OM.OrgGroupId 
					INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.ID=SA.OrgGroupBatchId
					INNER JOIN sSetup.tCommonOrgGroupBatchMaster COGBM ON COGBM.Id=OGBM.CommonOrgGroupBatchId
					INNER JOIN sSetup.tBatchMaster BM ON SA.BatchId = BM.Id
					WHERE  
					COGBM.Id=@rCommonOrgGroupBatchId
					AND R.SourceTypeId = 2001 					 
					AND((OMM.Id=@rOrgGroupId) OR (@rOrgGroupId=0))
					AND ((FM.Id =@rCommonFeeId)OR(@rCommonFeeId=0))
					AND (( convert(DATE, R.ReceiptDate,102) >= @rFromDate) OR (@rFromDate=''))
					AND (( convert(DATE, R.ReceiptDate,102) <= @rToDate) OR (@rToDate=''))
					AND BM.IsInContext = 1
					GROUP BY  R.ReceiptNumber,R.ReceiptDate,SA.Code, SA.Name
				END
				END
		
	
END
GO
PRINT N'Altering Procedure [sFee].[pFeeSetupApply]...';


GO
 ALTER PROCEDURE [sFee].[pFeeSetupApply]
	 
		   @rOrgId int,
           @rFromClassId int,
           @rFromAdmissionCategoryId int,
           @rFromBatchId int,
           @rToClassId int,
           @rToAdmissionCategoryId int,
           @rToBatchId int,       
           @rCreatedUserId bigint

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION
	
	DECLARE @lNextNumber INT  
	DECLARE @lCountSetup INT
	DECLARE @lNextSetupNumber INT
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFee.tFeeSetup', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	INSERT INTO sFee.tFeeSetup
				SELECT @lNextNumber + ROW_NUMBER() OVER(ORDER BY Id) AS Id,
				@rToClassId,
				@rToAdmissionCategoryId,
				@rToBatchId,
				FeeId,
				IsVariable,
				IsOptional,
				Amount,
				StartDate,
				EndDate,
				ReceiptBookId,
				@rCreatedUserId,
				sCommon.fGetDate(),
				NULL,
				NULL,
				IsServiceTax,
				CollectionGroupId,
				FeeDuration,
				0,
				LevelAmount,
				LateFeeRuleId
				
				FROM sFee.tFeeSetup
				WHERE ClassId = @rFromClassId AND AdmissionCategoryId = @rFromAdmissionCategoryId
				AND BatchId = @rFromBatchId
				
				SELECT @lCountSetup = COUNT(Id)	FROM sFee.tFeeSetup
					WHERE ClassId = @rFromClassId 
					AND AdmissionCategoryId = @rFromAdmissionCategoryId
					AND BatchId = @rFromBatchId
					
				SET @lNextSetupNumber  = ISNULL(@lNextNumber,0) + ISNULL( @lCountSetup ,0)		
					
				EXECUTE sCommon.pTableRowIdModify @rOrgId, @rTableTitle = 'sFee.tFeeSetup', @rCurrentNumber = @lNextSetupNumber 		
				
				SELECT 1 AS Result
				
			COMMIT TRANSACTION			
		END TRY
		
		BEGIN CATCH 
			ROLLBACK TRANSACTION
				SELECT -1 AS Result
		END CATCH
END
GO
PRINT N'Altering Procedure [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,
			--by megha - 0026430
			(CASE WHEN ISNULL(FA.VariableAmount,0) > 0 Then FA.VariableAmount 
			      When ISNULL(FS.Amount,0) > 0 Then FS.Amount END) + ISNULL(FA.AdditionalAmount,0) - ISNULL(FA.DiscountAmount,0) - ISNULL(FA.NotApplicableAmount,0) As TotalPayableAmount
			 
 	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,
-- 0 AS TotalPayableAmount
--END
GO
PRINT N'Altering Procedure [sLibrary].[pRptAccessionRegisterGet]...';


GO

ALTER PROCEDURE [sLibrary].[pRptAccessionRegisterGet]
	 
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='',
	 @rBookSupplier nvarchar(200),
	 @rBookPublisher nvarchar(200),
	 @rBookLanguage nvarchar(200),
	 @rBookTag nvarchar(200),
	 @rBookCustomTag nvarchar(200),
	 @rLibraryId int,
	 @rAccessionNo nvarchar(50)='',
	 @rClassificationNo nvarchar(50)='',
	 @rBookGroup1Id int,
	 @rBookGroup2Id int,
	 @rBookSubjectDrop nvarchar(200)='',
	 @rBookSubjectText nvarchar(200)='',
	 @rFromPurchaseDate date='',
	 @rToPurchaseDate date=''
           
AS
SET NOCOUNT ON
BEGIN
	BEGIN	  
		SET @rBookSupplier = sCommon.fSearchStringGet(@rBookSupplier)
		SET @rBookPublisher = sCommon.fSearchStringGet(@rBookPublisher)
		SET @rBookLanguage = sCommon.fSearchStringGet(@rBookLanguage)
		SET @rBookTag = sCommon.fSearchStringGet(@rBookTag)
		SET @rBookCustomTag = sCommon.fSearchStringGet(@rBookCustomTag)
		SET @rAccessionNo = sCommon.fSearchStringGet(@rAccessionNo)
		SET @rClassificationNo = sCommon.fSearchStringGet(@rClassificationNo)
		SET @rBookSubjectText = sCommon.fSearchStringGet(@rBookSubjectText)
	END
	SELECT   
		@rBuilder1 AS Builder1,
		@rBuilder2 AS Builder2,
		BM.Id AS BookId,
		BM.Code AS BookCode,
		BM.Title AS BookName,
		BM.Author,
		BM.Publisher,
		BM.ClassificationNo,
		BM.BookSubject,
		BM.AccessionNo,
		BM.Edition,
		BM.NoOfPages,
		BM.BookLanguage,
		BM.Supplier,
		BM.PurchaseDate,
		BM.Price,
		BM.ISBNNumber,
		BM.Location,
		BM.BookRemark,
		LM.ReportOrgTitle,
		LM.SmallAddress1,
		LM.SmallAddress2
	FROM  
		sLibrary.tBookMaster BM 
		LEFT JOIN sLibrary.tLibraryMaster LM ON LM.Id=BM.LibraryId
	WHERE 
		BM.LibraryId=@rLibraryId
		AND ((BM.Supplier like @rBookSupplier) OR (@rBookSupplier=''))
		AND ((BM.Publisher like @rBookPublisher) OR (@rBookPublisher=''))
		AND ((BM.BookLanguage like @rBookLanguage) OR (@rBookLanguage=''))
		AND ((BM.BookTag like @rBookTag) OR (@rBookTag=''))
		AND ((BM.BookCustomTag like @rBookCustomTag) OR (@rBookCustomTag=''))
		AND ((BM.AccessionNo like @rAccessionNo) OR (@rAccessionNo=''))
		AND ((BM.ClassificationNo like @rClassificationNo) OR (@rClassificationNo=''))
		AND ((BM.BookGroup1Id = @rBookGroup1Id) OR (@rBookGroup1Id=0))
		AND ((BM.BookGroup2Id = @rBookGroup2Id) OR (@rBookGroup2Id=0))
		AND ((BM.BookSubject = @rBookSubjectDrop) OR (@rBookSubjectDrop=''))
		AND ((BM.BookSubject like @rBookSubjectText) OR (@rBookSubjectText=''))
		AND ((BM.PurchaseDate >= @rFromPurchaseDate) OR (ISNULL(@rFromPurchaseDate,'') = ''))
		AND ((BM.PurchaseDate <= @rToPurchaseDate) OR (ISNULL(@rToPurchaseDate,'') =''))
END
GO
PRINT N'Altering Procedure [sLibrary].[pRptBookListGet]...';


GO
 
ALTER PROCEDURE [sLibrary].[pRptBookListGet]
	 
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='',
	 @rBookSupplier nvarchar(200),
	 @rBookPublisher nvarchar(200),
	 @rBookSubjectText nvarchar(200),
	 @rBookLanguage nvarchar(200),
	 @rBookTag nvarchar(200),
	 @rBookCustomTag nvarchar(200),
	 @rLibraryId int,
	 @rBookDepartment nvarchar(150)='',
	 @rIsDetail bit=0,
	 @rFromPurchaseDate date='',
	 @rToPurchaseDate date='',
	 @rBookGroup1Id int,
	 @rBookGroup2Id int,
	 --@rOrgGroupId int,
	 @rFromCode nvarchar(25) ='',
	 @rToCode nvarchar(25) ='',
	 @rSpecificCode nvarchar(max) ='',
	 @rBookSubjectDrop nvarchar(500)='',
	 @rFromDate date ='',
	 @rToDate date =''
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
		
BEGIN 
	--DECLARE @rFromCodeInt INT, @rToCodeInt INT
	--DECLARE @BookConfig NVARCHAR, @rFromCodeString NVARCHAR, @rToCodeString NVARCHAR
	--SELECT @BookConfig = ItemValue From sConfig.tConfigurationSetup	WHERE ConfigurationId = 260 AND SourceId = @rOrgGroupId
	
	--SET @rFromCodeInt = Convert(Int,ISNULL(@rFromCode,0))
	--SET @rToCodeInt = Convert(Int,ISNULL(@rToCode,0))
	--SET @rFromCodeString = Convert(nvarchar,ISNULL(@rFromCode,''))
	--SET @rToCodeString = Convert(nvarchar,ISNULL(@rToCode,''))
	
	DECLARE @ListofBookCodes TABLE(Code nvarchar(25), ID INT IDENTITY(1,1));
		INSERT INTO @ListofBookCodes
	
	select q2.value from
		(SELECT cast('<x>'+replace(@rSpecificCode,',','</x><x>')+'</x>' as xml) as thexml)q1 CROSS APPLY
		(SELECT x.value('.','nvarchar(max)') as value FROM thexml.nodes('x') as f(x))q2
		

	BEGIN	  
		SET @rBookSupplier = sCommon.fSearchStringGet(@rBookSupplier)
		SET @rBookPublisher = sCommon.fSearchStringGet(@rBookPublisher)
		SET @rBookSubjectText = sCommon.fSearchStringGet(@rBookSubjectText)
		SET @rBookLanguage = sCommon.fSearchStringGet(@rBookLanguage)
		SET @rBookTag = sCommon.fSearchStringGet(@rBookTag)
		SET @rBookCustomTag = sCommon.fSearchStringGet(@rBookCustomTag)
		SET @rBookDepartment = sCommon.fSearchStringGet(@rBookDepartment)
    END

	SELECT 
		@rBuilder1 AS Builder1,
		@rBuilder2 AS Builder2,
		BM.Code,
		BM.Title As BookTitle,
		BM.Publisher,BM.Supplier,
		BM.BookSubject,
		BM.Code AS BookCode,
		BM.BookLanguage,
		BM.BookCustomTag,
		BM.BookCustomTag,
		BM.Location,
		BM.ISBNNumber,
		LM.ReportOrgTitle,
        LM.SmallAddress1,
        LM.SmallAddress2,
        CASE WHEN BM.BookDepartment is null THEN 'Blank'
            WHEN BM.BookDepartment='' THEN 'Blank'  ELSE BM.BookDepartment END AS  BookDepartment,
        BM.NoOfCopies,
        @rIsDetail AS IsDetail,
        LM.Code AS LibraryCode,	       
         LM.Title AS LibraryTitle,
        BM.AccessionNo AS AccessionNo
		
	FROM 
		sLibrary.tBookMaster BM
		LEFT JOIN sLibrary.tLibraryMaster LM ON LM.Id=BM.LibraryId
	WHERE 
		BM.LibraryId=@rLibraryId
		AND ((BM.Supplier like @rBookSupplier) OR (@rBookSupplier=''))
		AND ((BM.Publisher like @rBookPublisher) OR (@rBookPublisher=''))
		AND ((BM.BookSubject like @rBookSubjectText) OR (@rBookSubjectText=''))
		AND ((BM.BookSubject = @rBookSubjectDrop) OR (@rBookSubjectDrop=''))
		AND ((BM.BookLanguage like @rBookLanguage) OR (@rBookLanguage=''))
		AND ((BM.BookTag like @rBookTag) OR (@rBookTag=''))
		AND ((BM.BookCustomTag like @rBookCustomTag) OR (@rBookCustomTag=''))
		AND ((BM.BookDepartment like @rBookDepartment) OR (@rBookDepartment=''))
		AND ((BM.PurchaseDate >= @rFromPurchaseDate) OR (ISNULL(@rFromPurchaseDate,'') = ''))
		AND ((BM.PurchaseDate <= @rToPurchaseDate) OR (ISNULL(@rToPurchaseDate,'') =''))
		AND ((BM.BookGroup1Id = @rBookGroup1Id) OR (@rBookGroup1Id=0))
		AND ((BM.BookGroup2Id = @rBookGroup2Id) OR (@rBookGroup2Id=0))
		AND ((Convert(nvarchar(25),ISNULL(BM.Code,'')) >= @rFromCode) OR (ISNULL(@rFromCode,'') = ''))
		AND ((Convert(nvarchar(25),ISNULL(BM.Code,'')) <= @rToCode) OR (ISNULL(@rToCode,'') = ''))
		AND ((BM.Code IN(SELECT Code FROM @ListofBookCodes)) OR (ISNULL(@rSpecificCode,'') = ''))
		--AND CASE WHEN @BookConfig = 'True' THEN BM.Code >= @rFromCodeString ELSE BM.Code > @rFromCodeInt END AS BM.Code
		--AND CASE WHEN @BookConfig = 'True' THEN BM.Code <= @rToCodeString ELSE BM.Code > @rToCodeInt END AS BM.Code
		AND ((BM.BookEntryDate >= @rFromDate) OR (ISNULL(@rFromDate,'') = ''))
		AND ((BM.BookEntryDate <= @rToDate) OR (ISNULL(@rToDate,'') = ''))
		ORDER BY
		CASE WHEN BM.Code like '%[0-9]%' 
		 THEN Replicate('0', 100 - Len(BM.Code)) + BM.Code
		 ELSE BM.Code  END
		--ORDER BY CONVERT(INT, BM.Code)
		
	END
GO
PRINT N'Altering Procedure [sSecurity].[pPendingLoginStudentdGet]...';


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

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

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


GO
ALTER PROCEDURE [sStudent].[pRptStudentLCVerificationGet]
	 
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rDepartmentId int=0,
	 @rClassId int=0,
	 @rDivisionId int=0,
     @rStrCode nvarchar(20)='',
	 @rStatusId int=0,
	 @rClassStatusId int=0,
	 @rExamId int = 0
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 
	
	SELECT 
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			L.Id,		
			vSA.Code,
			L.AcademicId,
			vSA.FirstName,vSA.MiddleName,vSA.LastName,
			[sStudent].[fStudentNameGet](vSA.FirstName , vSA.MiddleName, vSA.LastName,OM.StudentNameFormatId ) AS Name,
			ISNULL(SF.FatherName,'') As FatherName,
			ISNULL(SF.MotherName,'') As MotherName,
			--ISNULL(CTM.Nationality,'') AS Nationality,		
			--ISNULL(RM.Title,'') AS Religion,
			ISNULL(so.BirthPlace,'') AS BirthPlace,
			ISNULL(SO.BirthTaluka,'') AS BirthTaluka,
			ISNULL(SO.BirthDistrict,'') AS BirthDistrict,
			SO.DateOfBirth,
			ISNULL(SO.BirthDateInWord,'') AS BirthDateInWord,
			L.LCNumber,
			L.LeftDate,
			L.LCReason,
			L.LCProgress,
			L.LCConduct,
			L.LCRemark,
			L.LCDate,
			L.LCBookNumber,
			ISNULL(SO.FamilySubCast,'') AS SubCaste,
			ISNULL(SO.FamilyCast,'') AS Caste,
		    vSA.StatusId,		
		 	OM.OrgLogo,
			OM.Title AS OrgTitle,
			OM.StreetOne+' '+OM.StreetTwo+' '+OM.StreetThree AS OrgAddress,
			OM.PhoneNo,
			vSA.RollNo,
			DM.OrderIndex AS DivisionOrder,
			OM.SmallAddress1,
			OM.SmallAddress2,
			OM.RegistrationDetail,			
			vSA.ClassName AS ClassName,
			vSA.DivisionName AS DivisionName,	
			isnull(MainCastCategoryId,0) AS CastCategoryId,
			SO.AdhaarNumber,
			vSA.SUId,
			CASE WHEN isnull(BM.FromDate,'')>Isnull(SA.AdmissionDate,'') THEN BM.FromDate ELSE SA.AdmissionDate END AS BatchDate,
			SO.BirthState,
			SO.BirthCountry,
			SO.UIDNumber,
			--SA.PreviousStandard 
			L.LCCopy
			,vSA.BatchName AS BatchName,
		    ISNULL(SA.AdmissionNumber,'') AS AdmissionNumber,
			SO.UIDNumber,	
			ISNULL(OM.ReportOrgGroupTitle,'') AS ReportOrgGroupTitle,
			OM.ReportOrgTitle,	
    		ISNULL(CMS.ClassNameInWord,'') AS ClassNameInWord,
			vSA.ClassId AS ClassId,
	    	--EI.EmployeeSign,
		    OM.PrincipalSignature,
			ISNULL(R.Percentage,0) As Percentage,
			ISNULL(SCM.Title,0) AS LeftStandard
	FROM 
			sCertificate.tLeaving L
			INNER JOIN sStudent.vStudentAcademic vSA ON vSA.AcademicId=L.AcademicId	
			LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=vSA.StudentId			
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=vSA.OrgId 
			--INNER JOIN sSecurity.tUser U ON U.Id = L.CreatedUserId
			LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=vSA.StudentId 
			--LEFT JOIN sCommon.tReligionMaster RM ON SO.ReligionId=RM.Id	
			LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SA.AdmissionStandardId
			INNER JOIN sSetup.tDivisionMaster DM ON vSA.DivisionId = DM.Id  
			INNER JOIN sSetup.tClassMaster CMS ON CMS.Id= DM.ClassId 
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=vSA.StudentId
			--LEFT JOIN sCommon.tCountryMaster CTM ON CTM.Id=SO.NationalityId
			--LEFT JOIN sStudent.tAcademicDetail AD ON AD.AcademicId=vSA.AcademicId	
			--LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
			LEFT JOIN sCommon.tCastCategoryMaster CSM ON CSM.Id=SO.CastCategoryId
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id=vSA.BatchId 
			LEFT JOIN sStudent.tStudentAdditional SD ON SD.StudentId=vSA.StudentId
			--LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=SM.Id
			LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=vSA.StudentId
			--LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId = DM.InchargeEmployeeId
			--LEFT JOIN sSetup.tDivisionInchargeEmployee DIE ON vSA.DivisionId = DIE.DivisionId AND DIE.BatchId = vSA.BatchId
			--AND DIE.Number = (SELECT MIN(Number) FROM sSetup.tDivisionInchargeEmployee WHERE  DIE.DivisionId = vSA.DivisionId AND DIE.BatchId = vSA.BatchId)
			--LEFT JOIN sHrm.tEmployeeImages EI1 ON EI1.EmployeeId = DIE.EmployeeId
			--INNER JOIN sSetup.tLanguageMaster LM ON LM.Id = OM.LanguageId
			INNER JOIN sAssessment.tExamMaster EM ON EM.ClassId=vSA.ClassId
            INNER JOIN sAssessment.tResult R ON R.ExamId = EM.Id AND vSA.AcademicId = R.AcademicId
			LEFT JOIN sSetup.tClassMaster SCM ON SCM.Id = SA.LeftStandardId
	Where		
	           ((vSA.DepartmentId=@rDepartmentId) OR(@rDepartmentId=0))
	       AND ((vSA.ClassId=@rClassId) OR(@rClassId=0))	  
	       AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))	
		   --AND ((vSA.Code = @rStrCode) OR (ISNULL(@rStrCode,'') = ''))
		   AND (ISNULL(vSA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rStrCode,',')) OR @rStrCode='')
           AND ((vSA.StatusId=@rStatusId)OR(@rStatusId=0))
		   AND ((vSA.ClassStatusId =  @rClassStatusId) OR (@rClassStatusId=0)) 
		   AND ((R.ExamId = @rExamId) OR (@rExamId=0))				
END
GO
PRINT N'Altering Procedure [sStudent].[pStudentDocumentSubmitGet]...';


GO

ALTER PROCEDURE [sStudent].[pStudentDocumentSubmitGet]
	 
        @rStudentId bigint,
		@rOrgId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
 
	BEGIN
			SELECT DISTINCT Isnull(D.Id,0) AS DocumentId,ISNULL(D.DocumentName,'') AS DocumentName,
			CASE WHEN  S.SubmitDocumentMasterId Is null THEN 'False' ELSE 'True'END AS IsCheck,
			ISNULL(S.DocumentName,'') AS FileName
		 	FROM sCommon.tSubmitDocumentMaster D
			INNER JOIN sCommon.tSubmitDocumentScope DS ON DS.SubmitDocumentId = D.Id
			INNER JOIN sStudent.tAcademic A ON A.ClassId = DS.SourceId AND A.StudentId = @rStudentId
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId AND BM.IsActive = 1
			LEFT Join sStudent.tStudentDocument  S ON S.SubmitDocumentMasterId=D.id AND S.StudentId=@rStudentId
			WHERE D.OrgId=@rOrgId AND SubmitDocumentFunctionalityId='1'

			UNION 

			SELECT DISTINCT Isnull(D.Id,0) AS DocumentId,ISNULL(D.DocumentName,'') AS DocumentName,
			CASE WHEN  S.SubmitDocumentMasterId Is null THEN 'False' ELSE 'True'END AS IsCheck,
			ISNULL(S.DocumentName,'') AS FileName
		 	FROM sCommon.tSubmitDocumentMaster D
			LEFT JOIN sCommon.tSubmitDocumentScope DS ON DS.SubmitDocumentId = D.Id AND DS.SubmitDocumentId is null
			LEFT JOIN sStudent.tAcademic A ON A.ClassId = DS.SourceId AND A.StudentId = @rStudentId
			LEFT JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId AND BM.IsActive = 1
			LEFT Join sStudent.tStudentDocument  S ON S.SubmitDocumentMasterId=D.id AND S.StudentId=@rStudentId
			WHERE D.OrgId=@rOrgId AND SubmitDocumentFunctionalityId='1' AND A.ClassId is not null --AND DS.SubmitDocumentId is null


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


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

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

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


GO

 
ALTER PROCEDURE [sStudent].[pXMLStudentImportAdd]
	
	@rStudentImpXml [nvarchar](max),
	@rToOrgId int,
	@rToBatchId int,
	@rToClassId int,
	@rToDivisionId int,
	@rAdmissionDate date,
	@rCreatedUserId bigint,
	@rIsStudentOldCodeWithOrgId bit=0
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rStudentImpXml
BEGIN

	
	DECLARE @lCount INT
	DECLARE @lNextNumber BIGINT 
	Declare @lExistCode nvarchar(max)
	DECLARE @MaxCode AS bigint
	
 
 
	IF EXISTS(SELECT SM.Code
	from
	(
			SELECT StudentId FROM
			OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
			WITH (
					StudentId bigint)	
	)E
	INNER JOIN sStudent.tStudentMaster SM ON SM.ReferenceSourceId=E.StudentId AND SM.OrgId=@rToOrgId	
	)
	BEGIN
		SELECT @lExistCode  = COALESCE(@lExistCode ,'') + 'Old : '+ CONVERT(VARCHAR(15),E.Code) +' - ' + 'New : '+  SM.Code + ' , ' 
		from
		(
				SELECT StudentId,Code FROM
				OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
				WITH (
						StudentId bigint,
						Code nvarchar(20)
						)
		)E
		INNER JOIN sStudent.tStudentMaster SM ON SM.ReferenceSourceId=E.StudentId AND SM.OrgId=@rToOrgId	
		SELECT @lExistCode
		RETURN
	END
	
	--BEGIN TRY
	--BEGIN TRANSACTION

	EXECUTE sCommon.pTableRowIdGet @rToOrgId, 'sStudent.tStudentMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	SELECT @MaxCode = MAX(Number) FROM sStudent.tStudentMaster WHERE OrgId = @rToOrgId
	--SET @MaxCode = ISNULL(@MaxCode,0) + 1
	
	IF(@rIsStudentOldCodeWithOrgId = 0)
	BEGIN
	SELECT 
			@lNextNumber + ROW_NUMBER() OVER(ORDER BY E.StudentId)AS NewStudentId,
			E.StudentId,
			ISNULL(@MaxCode,0) + ROW_NUMBER() OVER(ORDER BY E.StudentId) AS Code,			
			AdmissionCategoryId
	INTO 
			sStudent.tTempStudentId
	from
	(
			SELECT StudentId,AdmissionCategoryId,Code FROM
			OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
			WITH (
					StudentId bigint,
					AdmissionCategoryId int,
					Code nvarchar(20)
					)	
	)E
	END
	ELSE IF (@rIsStudentOldCodeWithOrgId = 1)
	BEGIN
	SELECT 
			@lNextNumber + ROW_NUMBER() OVER(ORDER BY E.StudentId)AS NewStudentId,
			E.StudentId,
			CONVERT(NVARCHAR(4),LEFT(E.StudentId,4)) + E.Code AS Code,
			AdmissionCategoryId
	INTO 
			sStudent.tTempStudentId
	from
	(
			SELECT StudentId,AdmissionCategoryId,Code FROM
			OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
			WITH (
					StudentId bigint,
					AdmissionCategoryId int,
					Code nvarchar(20)
					)	
	)E
	END

	SELECT @lCount = COUNT(1) FROM OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
				 WITH ( Id int )
	SET @lNextNumber  = @lCount + @lNextNumber 
	
	EXECUTE sCommon.pTableRowIdModify @rToOrgId, @rTableTitle = 'sStudent.tStudentMaster', @rCurrentNumber = @lNextNumber 
		 print 'Academic'
	--Insert Into Student Master
	INSERT INTO sStudent.tStudentMaster
	(
			Id,
			Code,
			Number,
			FirstName,
			MiddleName,
			LastName,
			OrgId,
			StatusId,
			RecordStatusId,
			CreatedUserId,
			CreatedDateTime,
			ReferenceSourceId,
			AdmissionCategoryId,
			RegistrationNumber,
			IsBlackList,
			BlackListReason,
			SUId
		
	)
	SELECT 
			TS.NewStudentId,
			TS.Code,
			CASE WHEN ISNUMERIC(TS.Code) <> 0 THEN TS.Code ELSE TS.NewStudentId END,
			SM.FirstName,
			SM.MiddleName,
			SM.LastName,
			@rToOrgId,
			1,
			502,
			@rCreatedUserId,
			sCommon.fGetDate(),
			TS.StudentId,
			SM.AdmissionCategoryId,
			SM.RegistrationNumber,
			SM.IsBlackList,
			SM.BlackListReason,
			SM.SUId
	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentMaster SM ON SM.ID=TS.StudentId
		
	--Insert Into Academic
		
	EXECUTE sCommon.pTableRowIdGet @rToOrgId, 'sStudent.tAcademic', 0, 1, 1,@outNextNumber = @lNextNumber output


 print 'Academic'
	INSERT INTO sStudent.tAcademic
	(
			Id,
			StudentId,
			ClassId,
			BatchId,
			DivisionId,
			RollNo,
			OrgId,
			CreatedUserId,
			CreatedDateTime,
			AdmissionCategoryId,
			ClassStatusId
	)
	SELECT
			@lNextNumber+ ROW_NUMBER() OVER(ORDER BY StudentId),
			NewStudentId,
			@rToClassId,
			@rToBatchId,
			@rToDivisionId,
			0,
			@rToOrgId,
			@rCreatedUserId,
			sCommon.fGetDate(),
			AdmissionCategoryId,
			1
	FROM 
			sStudent.tTempStudentId

print 'Academic1'		
	SELECT @lCount = COUNT(1) FROM OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
			 WITH ( Id int )
	SET @lNextNumber  = @lCount + @lNextNumber 
	
	EXECUTE sCommon.pTableRowIdModify @rToOrgId, @rTableTitle = 'sStudent.tAcademic', @rCurrentNumber = @lNextNumber 
	
	--Insert Into Student Other
	INSERT INTO sStudent.tStudentOther
	(
		   [StudentId]
		  ,[UIDNumber]
		  ,[DateOfBirth]
		  ,[BirthDateInWord]
		  ,[TransportModeId]
		  ,[GenderId]
		  ,[BloodGroupId]
		  ,[BirthPlace]
		  ,[BirthTaluka]
		  ,[BirthDistrict]
		  ,[FamilyCast]
		  ,[FamilySubCast]
		  ,[CastCategoryId]
		  ,[ReligionId]
		  ,[HouseId]
		  ,[MotherTongueId]
		  ,[NationalityId]
		  ,[Hobby]		   
		  ,[CreatedUserId]
		  ,[CreatedDateTime]
		  ,ReferenceRemark
		  ,AdhaarNumber
		  ,MotherTongue
		  ,QuotaId
		  ,BirthState
		  ,BirthCountry
	)
	SELECT 
		   TS.NewStudentId
		  ,SO.[UIDNumber]
		  ,SO.[DateOfBirth]
		  ,SO.[BirthDateInWord]
		  ,SO.[TransportModeId]
		  ,SO.[GenderId]
		  ,SO.[BloodGroupId]
		  ,SO.[BirthPlace]
		  ,SO.[BirthTaluka]
		  ,SO.[BirthDistrict]
		  ,SO.[FamilyCast]
		  ,SO.[FamilySubCast]
		  ,SO.[CastCategoryId]
		  ,SO.ReligionId		   
		  ,NULL
		  ,so.MotherTongueId
		  ,So.NationalityId		 
		  ,SO.[Hobby] 
		  ,@rCreatedUserId
		  ,sCommon.fGetDate()
		  ,ReferenceRemark
		  ,AdhaarNumber
		  ,MotherTongue
		  ,QuotaId
		  ,BirthState
		  ,BirthCountry
	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=TS.StudentId
	
	--Insert Into Student Admission
	INSERT INTO sStudent.tStudentAdmission
	(
		   [StudentId]
		  ,[PreviousStudentId]
		  ,[AdmissionStandardId]
		  ,[AdmissionDate]
		  ,[AdmissionYear]
		  ,[BatchId]
		  ,[CreatedUserId]
		  ,[CreatedDateTime]
		  ,PreviousSchool
	)
	SELECT 
		   TS.NewStudentId
		  ,TS.StudentId
		  ,@rToClassId
		  ,@rAdmissionDate
		  ,YEAR(GETDATE())
		  ,@rToBatchId
		  ,@rCreatedUserId
		  ,sCommon.fGetDate()
		  ,PreviousSchool
	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentAdmission SA ON SA.StudentId=TS.StudentId
	
	
INSERT INTO [sStudent].[tStudentCommunication]
           ([StudentId]
           ,[PresentStreetOne]
           ,[PresentStreetTwo]
           ,[PresentStreetThree]
           ,[PresentCountry]
           ,[PresentState]
           ,[PresentCity]
           ,[PresentZipCode]
           ,[PresentContactNo]
           ,[PermanentStreetOne]
           ,[PermanentStreetTwo]
           ,[PermanentStreetThree]
           ,[PermanentCountry]
           ,[PermanentState]
           ,[PermanentCity]
           ,[PermanentZipCode]
           ,[PermanentContactNo]
           ,[EmergencyStreetOne]
           ,[EmergencyStreetTwo]
           ,[EmergencyStreetThree]
           ,[EmergencyCountry]
           ,[EmergencyState]
           ,[EmergencyCity]
           ,[EmergencyZipCode]
           ,[EmergencyContactNo]
           ,[ParentContactNo]
           ,[ParentEmail]
           ,[StudentContactNo]
           ,[StudentEmail]
           ,[CreatedUserId]
           ,[CreatedDateTime]  
		   ,PresentDistanceFromSchool
		   ,WhatsAppContactNo
		   )
 
 SELECT 
			TS.NewStudentId
           ,[PresentStreetOne]
           ,[PresentStreetTwo]
           ,[PresentStreetThree]
           ,[PresentCountry]
           ,[PresentState]
           ,[PresentCity]
           ,[PresentZipCode]
           ,[PresentContactNo]
           ,[PermanentStreetOne]
           ,[PermanentStreetTwo]
           ,[PermanentStreetThree]
           ,[PermanentCountry]
           ,[PermanentState]
           ,[PermanentCity]
           ,[PermanentZipCode]
           ,[PermanentContactNo]
           ,[EmergencyStreetOne]
           ,[EmergencyStreetTwo]
           ,[EmergencyStreetThree]
           ,[EmergencyCountry]
           ,[EmergencyState]
           ,[EmergencyCity]
           ,[EmergencyZipCode]
           ,[EmergencyContactNo]
           ,[ParentContactNo]
           ,[ParentEmail]
           ,[StudentContactNo]
           ,[StudentEmail]
           ,@rCreatedUserId
           ,sCommon.fGetDate()
		   ,PresentDistanceFromSchool
		   ,WhatsAppContactNo	
	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN [sStudent].[tStudentCommunication] SA ON SA.StudentId=TS.StudentId          
	
	
	
	INSERT INTO  [sStudent].[tStudentFamily]
           ([StudentId]
           ,[FatherName]
           ,[FatherDateOfBirth]
           ,[FatherOccupation]
           ,[FatherQualification]
           ,[FatherContactNo]
           ,[FatherMail]
           ,[FatherOfficeAddress]
           ,[FatherIncome]
           ,[MotherName]
           ,[MotherDateOfBirth]
           ,[MotherOccupation]
           ,[MotherQualification]
           ,[MotherContactNo]
           ,[MotherMail]
           ,[MotherOfficeAddress]
           ,[MotherIncome]
           ,[RelationshipName]
           ,[RelativeName]
           ,[RelativeDateOfBirth]
           ,[RelativeOccupation]
           ,[RelativeQualification]
           ,[RelativeContactNo]
           ,[RelativeMail]
           ,[RelativeOfficeAddress]
           ,[RelativeIncome]
           ,[CreatedUserId]
           ,[CreatedDateTime]
           ,[FatherPhoto]
           ,[MotherPhoto]
           ,[RelativePhoto]
           ,[MarriageAnniversaryDate]
		   ,LiveWithId
		   ,IsFatherExStudent
		   ,FatherPassOutYear
		   ,IsMotherExStudent
		   ,MotherPassOutYear
		   ,IsRelativeExStudent
		   ,RelativePassOutYear)
     SELECT    
			TS.NewStudentId
           ,[FatherName]
           ,[FatherDateOfBirth]
           ,[FatherOccupation]
           ,[FatherQualification]
           ,[FatherContactNo]
           ,[FatherMail]
           ,[FatherOfficeAddress]
           ,[FatherIncome]
           ,[MotherName]
           ,[MotherDateOfBirth]
           ,[MotherOccupation]
           ,[MotherQualification]
           ,[MotherContactNo]
           ,[MotherMail]
           ,[MotherOfficeAddress]
           ,[MotherIncome]
           ,[RelationshipName]
           ,[RelativeName]
           ,[RelativeDateOfBirth]
           ,[RelativeOccupation]
           ,[RelativeQualification]
           ,[RelativeContactNo]
           ,[RelativeMail]
           ,[RelativeOfficeAddress]
           ,[RelativeIncome]
           ,@rCreatedUserId
           ,sCommon.fGetDate()
           ,[FatherPhoto]
           ,[MotherPhoto]
           ,[RelativePhoto]
           ,[MarriageAnniversaryDate]
		   ,LiveWithId
		   ,IsFatherExStudent
		   ,FatherPassOutYear
		   ,IsMotherExStudent
		   ,MotherPassOutYear
		   ,IsRelativeExStudent
		   ,RelativePassOutYear
   	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentFamily SA ON SA.StudentId=TS.StudentId    


INSERT INTO  [sStudent].[tStudentHealth]
           ([StudentId]
           ,[IdentificationMark1]
           ,[IdentificationMark2]
           ,[IsPysicalDeformity]
           ,[PysicalDeformityRemark]
           ,[IsMajorIllness]
           ,[MajorIllnessRemark]
           ,[IsAllergy]
           ,[AllergyRemark]
           ,[IsPastHistory]
           ,[PastHistoryRemark]
           ,[FamilyDoctorName]
           ,[FamilyDoctorContactNo]
           ,[UpdatedUserId]
           ,[UpdatedDateTime])
SELECT

			TS.NewStudentId
           ,[IdentificationMark1]
           ,[IdentificationMark2]
           ,[IsPysicalDeformity]
           ,[PysicalDeformityRemark]
           ,[IsMajorIllness]
           ,[MajorIllnessRemark]
           ,[IsAllergy]
           ,[AllergyRemark]
           ,[IsPastHistory]
           ,[PastHistoryRemark]
           ,[FamilyDoctorName]
           ,[FamilyDoctorContactNo]
           ,@rCreatedUserId
           ,sCommon.fGetDate()

   	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentHealth SA ON SA.StudentId=TS.StudentId 
		
		
		INSERT INTO  [sStudent].[tStudentImages]
           ([StudentId]
           ,[StudentPhoto]
           ,[FatherPhoto]
           ,[MotherPhoto]
           ,[RelativePhoto]
           ,[UpdatedDateTime])
           
           SELECT           
            TS.NewStudentId
           ,[StudentPhoto]
           ,[FatherPhoto]
           ,[MotherPhoto]
           ,[RelativePhoto]
           ,sCommon.fGetDate()
         	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentImages SA ON SA.StudentId=TS.StudentId    
		
		
		
		INSERT INTO [sStudent].[tStudentAdditional]
           (StudentId,
			BankAccountNumber,
			BankName,
			BankBranch,
			BankIFSCCode,
			Class9BoardRegistrationNumber,
			Class11BoardRegistrationNumber,
			PassportNumber,
			PassportType,
			PassportCountryCode,
			PassportIssueDate,
			PassportExpiryDate,
			BankAccountHolderName
           )
           
           SELECT           
            TS.NewStudentId,
            BankAccountNumber,
            BankName,
			BankBranch,
			BankIFSCCode,
			Class9BoardRegistrationNumber,
			Class11BoardRegistrationNumber,
			PassportNumber,
			PassportType,
			PassportCountryCode,
			PassportIssueDate,
			PassportExpiryDate,
			BankAccountHolderName
         	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentAdditional SA ON SA.StudentId=TS.StudentId        
	    

		---Studen Sibling Detail-
			
		INSERT INTO [sStudent].[tStudentSibling]
           (StudentId,
			SiblingName1,
			SiblingInstitue1,
			SiblingClass1,
			SiblingAge1,
			SiblingRelation1,
			SiblingName2,
			SiblingInstitue2,
			SiblingClass2,
			SiblingAge2,
			SiblingRelation2,
			SiblingName3,
			SiblingInstitue3,
			SiblingClass3,
			SiblingAge3,
			SiblingRelation3

           )
           
           SELECT           
            TS.NewStudentId,
            SiblingName1,
			SiblingInstitue1,
			SiblingClass1,
			SiblingAge1,
			SiblingRelation1,
			SiblingName2,
			SiblingInstitue2,
			SiblingClass2,
			SiblingAge2,
			SiblingRelation2,
			SiblingName3,
			SiblingInstitue3,
			SiblingClass3,
			SiblingAge3,
			SiblingRelation3
         	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN [sStudent].[tStudentSibling] SA ON SA.StudentId=TS.StudentId        

		INSERT INTO  [sStudent].[tCollegeDetail]
            (StudentId,
			AdmissionNumber,
			IsTransfer,
			TransferFrom,
			PassingYear,
			IsTFWS,
			IsCertificateHolder,
			CertificateName,
			SSCBoard,
			SSCSchool,
			SSCPassingYear,
			SSCObtainMarks,
			SSCOutOfMarks,
			SSCPercentage,
			SSCMathMarks,
			SSCMathOutOfMarks,
			HSCBoard,
			HSCSchool,
			HSCPassingYear,
			HSCObtainMarks,
			HSCOutOfMarks,
			HSCPercentage,
			HSCPhysicsMarks,
			HSCPhysicsOutOfMarks,
			HSCChemistryMarks,
			HSCChemistryOutOfMarks,
			HSCMathMarks,
			HSCMathOutOfMarks,
			HSCBiologyMarks,
			HSCBiologyOutOfMarks,
			PCMMarks,
			PCBMarks,
			JEERollNo,
			JEEMeritNo,
			JEEMeritMarks,
			JEEOutOfMarks,
			DiplomaBoard,
			DiplomaCollege,
			DiplomaPassingYear,
			DiplomaObtainMarks,
			DiplomaOutOfMarks,
			DiplomaPercentage,
			ApplicationNo,
			AdmissionRound,
			MaritalStatusId
)
SELECT

			TS.NewStudentId,
            AdmissionNumber,
			IsTransfer,
			TransferFrom,
			PassingYear,
			IsTFWS,
			IsCertificateHolder,
			CertificateName,
			SSCBoard,
			SSCSchool,
			SSCPassingYear,
			SSCObtainMarks,
			SSCOutOfMarks,
			SSCPercentage,
			SSCMathMarks,
			SSCMathOutOfMarks,
			HSCBoard,
			HSCSchool,
			HSCPassingYear,
			HSCObtainMarks,
			HSCOutOfMarks,
			HSCPercentage,
			HSCPhysicsMarks,
			HSCPhysicsOutOfMarks,
			HSCChemistryMarks,
			HSCChemistryOutOfMarks,
			HSCMathMarks,
			HSCMathOutOfMarks,
			HSCBiologyMarks,
			HSCBiologyOutOfMarks,
			PCMMarks,
			PCBMarks,
			JEERollNo,
			JEEMeritNo,
			JEEMeritMarks,
			JEEOutOfMarks,
			DiplomaBoard,
			DiplomaCollege,
			DiplomaPassingYear,
			DiplomaObtainMarks,
			DiplomaOutOfMarks,
			DiplomaPercentage,
			ApplicationNo,
			AdmissionRound,
			MaritalStatusId

   	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tCollegeDetail SA ON SA.StudentId=TS.StudentId 

		-- -Student Custom Tag-
		INSERT INTO [sStudent].[tStudentCustomTag]
           (StudentId,CustomTagId
           )
SELECT

			TS.NewStudentId,CustomTagId
       
   	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentCustomTag SA ON SA.StudentId=TS.StudentId 
		

	    -- for update hostel 
	    -- Start
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sHostel.tAssignRoom A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sHostel.tAssignRoomAsset A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tAttendanceRegister A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sHostel.tChangeRoom A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sHostel.tHostelAdmission A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tHostelDepositAdd A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tHostelDepositAdjustment A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tHostelDepositRefund A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tStudentGatePass A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.WhomToMeetSourceId = T.NewStudentId 
		--FROM sHostel.tVisitorGatePass A JOIN sStudent.tTempStudentId T ON A.WhomToMeetSourceId = T.StudentId AND WhomToMeetSourceTypeId = 2003
		-- End
		
		
		-- for update transport
		-- Start
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sTransport.tAssignRoute A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sTransport.tAttendanceRegister A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		-- End
		
		-- for update Library
		-- Start
		UPDATE A SET A.ReaderSourceId = T.NewStudentId 
		FROM sLibrary.tBookLog A JOIN sStudent.tTempStudentId T ON A.ReaderSourceId = T.StudentId AND A.ReaderSourceTypeId = 2003
		
		UPDATE A SET A.SourceId = T.NewStudentId 
		FROM sLibrary.tLibraryAttendance A JOIN sStudent.tTempStudentId T 
		ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		-- End
		
		-- for update Canteen
		-- Start
		UPDATE A SET A.SourceId = T.NewStudentId 
		FROM sCanteen.tCanteenIssue A JOIN sStudent.tTempStudentId T 
		ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		-- End
	
	DROP TABLE sStudent.tTempStudentId
	
    SELECT 1 AS Result
	
	--COMMIT TRANSACTION
		
	--END TRY
	
	--BEGIN CATCH 
	--	ROLLBACK TRANSACTION
	--	SELECT Error_message()
	--	SELECT -1 AS Result
	--END CATCH	
		
END
GO
PRINT N'Altering Procedure [sWorkflow].[pWorkflowQueuesGet]...';


GO

 
ALTER  PROCEDURE [sWorkflow].[pWorkflowQueuesGet]
			 @rWorkflowFunctionalityId bigint=0,
			 @rFromDate date=null,
			 @rToDate date=null,
			 @rStatusId int=0,
			 @rCode nvarchar(20) ='',
			 @rDepartmentId int=0,
			 @rUserId bigint ,
			 @rApprovalRequestId int = 0
			 

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
Declare @lRoleId int
Select @lRoleId = RoleId from [sSecurity].[tUserRole] Where UserId = @rUserId
--Select @lRoleId
SELECT
	AA.Id AS ApprovalActionId,
	ADM.Id AS DepartmentId,
	WFM.Title AS WorkFlowName,
	WFM.Id AS WorkFlowId,
	AR.Id AS ApprovalRequestId,
	ADM.Title AS DepartmentTitle,
	CONVERT(DATE,AR.CreatedDateTime, 103) AS CreatedDateTime,
	AR.ParticipantSourceTypeId AS ParticipantSourceTypeId,
	CASE WHEN ParticipantSourceTypeId= 2001 THEN SA.AcademicId WHEN ParticipantSourceTypeId= 95 THEN EM.Id END AS SourceId,
	CASE WHEN ParticipantSourceTypeId= 2001 THEN SA.Code WHEN ParticipantSourceTypeId= 95 THEN EM.Code END AS Code ,
	CASE WHEN ParticipantSourceTypeId= 2001 THEN SA.Name WHEN ParticipantSourceTypeId= 95 THEN EM.FirstName +' '+ EM.MiddleName +' ' +EM.LastName END AS Name ,
	AR.Remark AS Remark,
	(CASE ISNULL(asm.Id,'') WHEN '' THEN 3 ELSE asm.Id END) AS statusId,
	(CASE ISNULL(asm.title,'') WHEN '' THEN 'Pending' ELSE asm.title END) AS status,
	ISNULL(AA.ActionRemark, '') AS ActionRemark
			
		
from sWorkflow.tApprovalRequest AR
INNER JOIN sWorkflow.tFunctionalityApprovingDepartment FAD  ON AR.WorkflowFunctionalityId = FAD.WorkflowFunctionalityId 
INNER JOIN sWorkflow.tApprovingDepartmentMaster ADM ON FAD.ApprovingDepartmentId = ADM.Id
INNER JOIN sWorkflow.tFunctionalityApprovingAuthority FAA ON ADM.Id = FAA.FunctionalityApprovingDepartmentId AND AR.WorkflowFunctionalityId = FAA.WorkflowFunctionalityId
INNER JOIN sSecurity.tRole R ON R.Id = FAA.AuthoritySourceId AND FAA.AuthoritySourceTypeId = 2015
LEFT JOIN sWorkflow.tApprovalAction AA ON AA.ApprovalRequestId = AR.Id AND AA.ApprovingDepartmentId = ADM.ID
LEFT JOIN sWorkflow.tActionStatusMaster ASM ON ASM.Id = AA.ActionStatusId
INNER JOIN sWorkflow.tWorkflowFunctionalityMaster WFM ON AR.WorkflowFunctionalityId = WFM.Id
LEFT JOIN  sStudent.vStudentAcademic  SA ON SA.AcademicId = AR.ParticipantSourceId
LEFT JOIN  sHrm.tEmployeeMaster  EM ON EM.Id = AR.ParticipantSourceId
WHERE R.Id = @lRoleId
AND ((WFM.Id = @rWorkflowFunctionalityId) OR (@rWorkflowFunctionalityId =0)) 
AND ((Convert(Date,AR.CreatedDateTime)>= @rFromDate) OR (ISNULL(@rFromDate,'') = ''))
AND ((Convert(Date,AR.CreatedDateTime) <= @rToDate) OR (ISNULL(@rToDate,'') = ''))
AND((ASM.Id = @rStatusId) OR (@rStatusId =0))
AND (((SA.Code = @rCode) OR (@rCode ='')) OR ((EM.Code = @rCode) OR (@rCode = '')))
AND((ADM.Id = @rDepartmentId) OR (@rDepartmentId =0))
AND  ISNULL(EM.IsOtherUser,0) = 0
AND ((AR.Id = @rApprovalRequestId) OR (@rApprovalRequestId = 0))

UNION

SELECT
	AA.Id AS ApprovalActionId,
	ADM.Id AS DepartmentId,
	WFM.Title AS WorkFlowName,
	WFM.Id AS WorkFlowId,
	AR.Id AS ApprovalRequestId,
	ADM.Title AS DepartmentTitle,
	CONVERT(DATE,AR.CreatedDateTime, 103) AS CreatedDateTime,
	AR.ParticipantSourceTypeId AS ParticipantSourceTypeId,
	CASE WHEN ParticipantSourceTypeId= 2001 THEN SA.AcademicId WHEN ParticipantSourceTypeId= 95 THEN EM.Id END AS SourceId,
	CASE WHEN ParticipantSourceTypeId= 2001 THEN SA.Code WHEN ParticipantSourceTypeId= 95 THEN EM.Code END AS Code ,
	CASE WHEN ParticipantSourceTypeId= 2001 THEN SA.Name WHEN ParticipantSourceTypeId= 95 THEN EM.FirstName +' '+ EM.MiddleName +' ' +EM.LastName END AS Name ,
	AR.Remark AS Remark,
	(CASE ISNULL(asm.Id,'') WHEN '' THEN 3 ELSE asm.Id END) AS statusId,
	(CASE ISNULL(asm.title,'') WHEN '' THEN 'Pending' ELSE asm.title END) AS status,
	ISNULL(AA.ActionRemark, '') AS ActionRemark
			
		
from sWorkflow.tApprovalRequest AR
INNER JOIN sWorkflow.tFunctionalityApprovingDepartment FAD  ON AR.WorkflowFunctionalityId = FAD.WorkflowFunctionalityId 
INNER JOIN sWorkflow.tApprovingDepartmentMaster ADM ON FAD.ApprovingDepartmentId = ADM.Id
INNER JOIN sWorkflow.tFunctionalityApprovingAuthority FAA ON ADM.Id = FAA.FunctionalityApprovingDepartmentId AND AR.WorkflowFunctionalityId = FAA.WorkflowFunctionalityId
INNER JOIN sSecurity.tUser U ON U.Id = FAA.AuthoritySourceId AND FAA.AuthoritySourceTypeId = 1019
LEFT JOIN sWorkflow.tApprovalAction AA ON AA.ApprovalRequestId = AR.Id AND AA.ApprovingDepartmentId = ADM.ID
LEFT JOIN sWorkflow.tActionStatusMaster ASM ON ASM.Id = AA.ActionStatusId
INNER JOIN sWorkflow.tWorkflowFunctionalityMaster WFM ON AR.WorkflowFunctionalityId = WFM.Id
LEFT JOIN  sStudent.vStudentAcademic  SA ON SA.AcademicId = AR.ParticipantSourceId
LEFT JOIN  sHrm.tEmployeeMaster  EM ON EM.Id = AR.ParticipantSourceId
WHERE U.Id = @rUserId
AND ((WFM.Id = @rWorkflowFunctionalityId) OR (@rWorkflowFunctionalityId =0)) 
AND ((Convert(Date,AR.CreatedDateTime)>= @rFromDate) OR (ISNULL(@rFromDate,'') = ''))
AND ((Convert(Date,AR.CreatedDateTime) <= @rToDate) OR (ISNULL(@rToDate,'') = ''))
AND((ASM.Id = @rStatusId) OR (@rStatusId =0))
AND (((SA.Code = @rCode) OR (@rCode ='')) OR ((EM.Code = @rCode) OR (@rCode ='')))
AND((ADM.Id = @rDepartmentId) OR (@rDepartmentId =0))
AND  ISNULL(EM.IsOtherUser,0) = 0
AND ((AR.Id = @rApprovalRequestId) OR (@rApprovalRequestId = 0))

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


GO
CREATE PROCEDURE sAlert.pAlertQueueLogAdd
	@rId bigint = 0,
	@rMessageDate datetime = null,
	@rQueueMessage nvarchar(max) = '',
	@rEventId int = 0,
	@rAlertModeId tinyint = 0,
	@rOrgId smallint = 0,
	@rCreatedUserId bigint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	BEGIN TRANSACTION	
		IF(@rId = 0)	
		BEGIN
			INSERT INTO [sAlert].[tAlertQueueLog]
				([MessageDate]
			   ,[QueueMessage]
			   ,[EventId]
			   ,[AlertModeId]
			   ,[OrgId]
			   ,[CreatedUserId]
			   ,[CreatedDateTime])
		   VALUES
				(@rMessageDate
			   ,@rQueueMessage
			   ,@rEventId
			   ,@rAlertModeId
			   ,@rOrgId
			   ,@rCreatedUserId
			   ,sCommon.fGetDate())

			SELECT @@IDENTITY AS Result
		END
		ELSE
		BEGIN
			UPDATE [sAlert].[tAlertQueueLog] SET [QueueMessage] = @rQueueMessage WHERE AutoId = @rId

			SELECT @rId AS Result
		END
	COMMIT TRANSACTION
    END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
END
GO
PRINT N'Creating Procedure [sAlert].[pTransportAttendanceListGet]...';


GO


CREATE PROCEDURE [sAlert].[pTransportAttendanceListGet]	 
	@rBusRouteId int,
	@rRouteMode nvarchar(5),
	@rOrgGroupBatchId int,
	@rOrgGroupId int,
	@rAttendanceDate date,
	@rTemplate nvarchar(max),
	@rMailSubject nvarchar(500),
	@rIsPresent bit = 0,
	@rStudentIds nvarchar(max)
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @lTransportMode nvarchar(10)
IF(UPPER(@rRouteMode) = 'IN')
    SET @lTransportMode = 'Pickup'
ELSE
    SET @lTransportMode = 'Drop'

BEGIN
	SELECT 
		SC.AcademicId,
		SC.Code,
		SC.Name,
		SC.ParentContactNo AS MobileNo,	
		SC.ParentEmail AS EmailId,
		ISNULL(SC.StudentContactNo,'') AS StudentContactNo,
		ISNULL(SC.StudentEmail,'') AS StudentEmail,
		ISNULL(SC.FatherContactNo,'') AS FatherContactNo,
		ISNULL(SC.FatherMail,'') AS FatherMail,
		ISNULL(SC.MotherContactNo,'') AS MotherContactNo,
		ISNULL(SC.MotherMail,'') AS MotherMail,
		CASE WHEN @rIsPresent = 0 THEN --for absent
		REPLACE (REPLACE (			
		REPLACE(REPLACE(REPLACE(@rTemplate,'[Name]',SC.Name),
		'[Class]',SC.ClassName),
		'[Division]',SC.DivisionName),
		'[AbsentDates]',ATR.AttendanceDate),
		'[TransportMode]',@lTransportMode)
		ELSE --for present
		REPLACE(REPLACE (					
		REPLACE(REPLACE(REPLACE(@rTemplate,'[Name]',SC.Name),
		'[Class]',SC.ClassName),
		'[Division]',SC.DivisionName),
		'[PresentDates]',ATR.AttendanceDate),
		'[TransportMode]',@lTransportMode)			
		END
			AS MsgText,
		@rMailSubject AS Subject,
		SC.AcademicId AS SourceId,
		2001 AS SourceTypeId,
		SC.OrgId,
		SC.OrgGroupId,
		SC.AcademicId AS ReceiverSourceId,
		2001 AS ReceiverSourceTypeId,
		SC.ClassName AS Class,
		SC.DivisionName AS Division,
		SC.WhatsAppContactNo
	FROM 
		sStudent.vStudentCommunication SC
		INNER JOIN sTransport.tAssignRoute  AR ON SC.StudentId = AR.SourceId AND AR.SourceTypeId=2003
		INNER JOIN sTransport.tBusRouteDestinationMaster BRDM 
		ON (BRDM.Id = AR.PickupDestinationId AND UPPER(@rRouteMode)='IN')OR(BRDM.Id = AR.DropDestinationId AND UPPER(@rRouteMode)='OUT')
		INNER JOIN sTransport.tBusRouteMaster BR ON BR.Id=BRDM.BusRouteId
		LEFT JOIN sTransport.tAttendanceRegister ATR ON ATR.SourceId=SC.StudentId 
			
	WHERE 
		BRDM.BusRouteId = @rBusRouteId
		AND BR.OrgGroupBatchId=@rOrgGroupBatchId
		AND SC.OrgGroupBatchId=@rOrgGroupBatchId 
		AND BR.OrgGroupId=@rOrgGroupId
		AND ATR.AttendanceDate=@rAttendanceDate
		AND (
        (@rRouteMode = 'IN' AND  ISNULL(ATR.IsPresentPickup, 0) = @rIsPresent) OR
        (@rRouteMode = 'OUT' AND  ISNULL(ATR.IsPresentDrop, 0) = @rIsPresent)
		)
		AND SC.IsActiveBatch = 1
        AND SC.StatusId = 1
		AND SC.StudentId IN( SELECT cast(item as bigint) FROM  sCommon.fnsplit(@rStudentIds,','))

	ORDER BY SC.ClassOrderIndex,SC.DivisionOrderIndex,SC.RollNo
END
GO
PRINT N'Creating Procedure [sCommunication].[pPollListTaskPermissionwiseGet]...';


GO
CREATE PROCEDURE sCommunication.pPollListTaskPermissionwiseGet
	 @rId bigint = 0,
	 @rOrgGroupBatchId int,
	 @rCreatedUserId bigint,
	 @rSourceTypeId smallint,
	 @rCreatedDateTime datetime=''

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	SELECT 
	
			P.Id as PollId,
			P.Title AS PollTitle,
			P.Remark AS PollRemark,
			P.IsOwnAnswer,
			P.IsOpen,
			P.CreatedDateTime AS PollCreatedDateTime,
			ISNULL(PR.CreatedUserId,0) AS PollResultUserId,
			(SELECT  ISNULL(Count(1),0) FROM sCommunication.tPollResult PR WHERE PR.PollId=P.Id) AS TotalPollVote,
			(SELECT U.DisplayTitle FROM sSecurity.tUser U WHERE U.Id = P.CreatedUserId) AS DisplayTitle			
  FROM 
            sCommunication.tPoll P			
            LEFT JOIN sCommunication.tPollResult PR ON P.Id = PR.PollId AND  PR.CreatedUserId = @rCreatedUserId
  WHERE 
  
      ((P.Id = @rId) OR (@rId = 0))
    AND ((P.CreatedUserId = @rCreatedUserId) OR (@rCreatedUserId = 0))
	AND ((P.OrgGroupBatchId=@rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))
	AND ((convert(date,P.CreatedDateTime)=CONVERT(date,@rCreatedDateTime)) OR (ISNull(@rCreatedDateTime,'')=''))

END
GO
PRINT N'Creating Procedure [sFee].[pRptReceiptSummaryUserPayTypeWiseDetailsGet]...';


GO

CREATE PROCEDURE [sFee].[pRptReceiptSummaryUserPayTypeWiseDetailsGet]  
	@rReportId int=0,
	@rReceiptBookId int=0,
	@rAdmissionCategoryId int=0,
	@rClassId int=0,
	@rDivisionId int=0,
	@rFromDate date='',
	@rToDate date='',
	@rFromReceiptNo int=0,
	@rToReceiptNo int=0,  
	@rBatchId int = 0,
	@rUserId bigint=0,
	@rBuilder1 nvarchar(max)=0,
	@rBuilder2 nvarchar(max)=0,
	@rIsDetail bit=0,
	@rIsCancel bit=0,
	@rOrgId int = 0,
	@rOrgGroupBatchId int = 0,
	@rStatusId int=0,
	@rPaytypeId int=0,
	@rCommonBatch nvarchar(100)='',
	@rDepartmentId int=0,
	@rFromRollNo int=0,
	@rToRollNo int=0,
	@rCode nvarchar(20)='',
	@rIsFilter bit=1,
	@rIsHeader bit=0,
	@rCreatedUserName nvarchar(100)='',
	@rFeeHeadId nvarchar(max) = '',
	@rIsDisplayOrgGroup bit=0,
	@rIsDiscount bit = 0,
	@rFeeId nvarchar(max) = '',
	@rFilterId int=1,
	@rCustomGroupId smallint = 0,
	@rOrgGroupId int=0,
	@rFeeStatusId int=0,
    @rBankName nvarchar(100)='',
    @rOrgIds nvarchar(max) = '',
    @rBankNames nvarchar(max) = '',
	@rFromChequeClearDate Date = '',
	@rToChequeClearDate Date = '' ,
	@rClassStatusId int=0 ,
	@rPayTypeIds nvarchar(max) = '',
	@rUserAcessUserId bigint = 0,
	@rReceiptBookIds nvarchar(max) = ''

 WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON  
      
BEGIN   
   SELECT   
		@rBuilder1 AS Builder1,  
		@rBuilder2 AS Builder2,  
		@rIsDetail AS IsDetail,  
		R.IsObsolete AS IsCancel,   
		@rIsFilter As IsFilter,  
		@rIsHeader As IsHeader,  
		@rIsDisplayOrgGroup AS IsDisplayOrgGroup,  
		R.Id AS ReceiptId,  
		R.ReceiptNumber,  
		SA.ClassId,  
		--SA.ClassName,  
		SA.DivisionId,  
		--SA.DivisionName,  
		SA.ClassName+' - '+SA.DivisionName AS ClassDivision,  
		SA.RollNo,  
		SA.Code,  
		SA.Name,  
		ISNULL(PTM.Title,'') AS PayType,    
		CASE WHEN ISNULL(R.ChequeNo,'') != '' THEN R.ChequeNo ELSE CONVERT(nvarchar(20),R.PaymentTransactionId) END AS ChequeNo,
		ISNULL(R.BankName,'') AS BankName,  
		R.ReceiptAmount,  
		R.ReceiptDate,  
		--CASE  WHEN @rReportId = 30611 AND R.ChequeClearDate IS NOT NULL THEN R.ChequeClearDate ELSE R.ReceiptDate END AS ReceiptDate,  
		R.CreatedUserId,  
		ISNULL(U.LoginTitle,'') AS UserName,  
		ISNULL(RBM.Title,'') AS ReceiptBookTitle,  
		ISNULL(RBM.BankName,'') AS ReceiptBookBank,  
		ISNULL(RBM.BankAccountNumber,'') AS BankAccountNumber,  
		ISNULL(R.ReceiptBookId,'0') As ReceiptBookId,  
		ISNULL((CASE @rReportId WHEN 20461 THEN [sFee].[fReceiptFeeDetailGet](R.Id)  --Need to check
		ELSE '' END),'') AS FeeDetail,  
		R.Remark,  
		R.DiscountAmount,  
		ISNULL(RBM.OrderIndex,0) AS ReceiptBookOrderIndex,  
		SA.OrgId,  
		SA.OrganizationName,  
		ISNULL(R.AmountAfterDiscount,0) AS AmountAfterDiscount,  
		ISNULL(R.CGSTPercentage,0) AS CGSTPercentage,  
		ISNULL(R.SGSTPercentage,0) AS SGSTPercentage,  
		ISNULL(R.CGSTAmount,0) AS CGSTAmount,  
		ISNULL(R.SGSTAmount,0) AS SGSTAmount,  
		@rCustomGroupId AS CustomGroupId,  
		SA.OrgGroupBatchId AS BatchId,  
		SA.BatchName,  
		R.ChequeDate,  
		R.ChequeClearDate ,
		SA.GenderId,
		ISNULL(FM.Id,0) AS FeeId,
		FM.Code AS FeeCode,  
		ISNULL(FM.Title,'') AS FeeTitle,
		FM.OrderIndex AS FeeOrderIndex,
		R.CancelStatusRemark AS CancelRemark,
		FM.FeeHeadId,
		FHM.Title AS FeeHeadName,
		RD.Amount AS DetailAmount
	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 sFee.tFeeHeadMaster FHM ON FM.FeeHeadId = FHM.Id  

		  INNER JOIN sStudent.vStudentAcademic SA  
		   ON SA.AcademicId=R.SourceId AND ((SA.OrgId=@rOrgId) OR (@rOrgId=0)) 

		  --According UserAccess
		  INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id =SA.OrgId
		  INNER JOIN sSecurity.tUserOrganization UO ON UO.OrgId=SA.OrgId AND OM.IsObsolete=0 AND UO.UserId=@rUserAcessUserId  

		  INNER JOIN sFinanceCommon.tPayTypeMaster PTM  
		   ON PTM.Id=R.PayTypeId  
		  INNER JOIN sFee.tReceiptBookMaster RBM   
		   ON RBM.Id=R.ReceiptBookId  
		  
		  --According UserAccess
		  INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=RBM.Id AND UA.SourceTypeId=2013 AND RBM.IsObsolete=0 AND UA.UserId =@rUserAcessUserId 


		  LEFT JOIN sSecurity.tUser U  
		   ON U.Id=R.CreatedUserId  
		 LEFT JOIN sHostel.tAssignRoom AR ON  AR.SourceId=SA.StudentId AND AR.SourceTypeId=2003 AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))  AND ((AR.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0) AND (SA.OrgGroupBatchId = AR.OrgGroupBatchId))   
			LEFT JOIN  
		 (SELECT AT.SourceId,B.OrgGroupBatchId FROM sTransport.tAssignRoute AT   
			INNER JOIN sTransport.tBusRouteDestinationMaster BM ON BM.Id=AT.PickupDestinationId   
			INNER JOIN sTransport.tBusRouteMaster B ON B.Id=BM.BusRouteId AND  AT.SourceTypeId=2003 AND ((B.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))) RT  
			ON RT.SourceId = SA.StudentId   
			AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))    
			AND (SA.OrgGroupBatchId = RT.OrgGroupBatchId)  
	WHERE   
		 R.SourceTypeId = 2001  
		 AND ((SA.AdmissionCategoryId = @rAdmissionCategoryId) OR (@rAdmissionCategoryId = 0))  
		 AND ((SA.ClassId = @rClassId) OR (@rClassId = 0))  
		 AND ((SA.DivisionId = @rDivisionId) OR (@rDivisionId = 0))  
		 AND ((R.ReceiptBookId = @rReceiptBookId) OR (@rReceiptBookId = 0))  
		 AND ((R.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo = 0))  
		 AND ((R.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo = 0))  
		 AND ((R.ReceiptDate >= @rFromDate) OR (ISNULL(@rFromDate,'') = ''))  
		 AND ((R.ReceiptDate <= @rToDate) OR (ISNULL(@rToDate,'') = ''))  
		 AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))  
		 AND ((SA.OrgId=@rOrgId) OR (@rOrgId=0))  
		 AND ((R.BatchId=@rBatchId) OR (@rBatchId=0))  
		 AND ((R.CreatedUserId=@rUserId) OR (@rUserId=0))  
		 AND (   
				(@rFeeStatusId = 0) -- All  
				OR (@rFeeStatusId = 1 AND R.IsObsolete = 0) -- Valid  
				OR (@rFeeStatusId = 2 AND R.IsObsolete = 1) -- Cancel  
			)
		 AND (SA.StatusId=@rStatusId OR @rStatusId=0)  
		 AND (SA.ClassStatusId=@rClassStatusId OR @rClassStatusId=0)  
		 AND (R.PayTypeId = @rPaytypeId OR @rPaytypeId=0)  
		 AND ((SA.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))  
		 AND ((SA.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))  
		 AND ((SA.RollNo <= @rToRollNo) OR (@rToRollNo=0))  
		 AND ((SA.Code=@rCode) OR (@rCode=''))  
		 AND ((SA.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))  
		 AND ((ISNULL(U.LoginTitle,'') like @rCreatedUserName) OR (ISNULL(@rCreatedUserName,'')=''))  
		 AND ((@rIsDiscount =1 AND R.DiscountAmount!=0) OR (@rIsDiscount=0))  
		 AND (ISNULL(FM.FeeHeadId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeHeadId,','))OR @rFeeHeadId='')  
		 AND (ISNULL(FM.Id, 0) IN (SELECT CAST(item as int) FROM sCommon.fnsplit(@rFeeId,',')) OR @rFeeId = '')
		  AND (  
		  (@rFilterId = 0) -- All  
			 OR (@rFilterId = 2 AND isnull(AR.SourceId,0)!=0) -- Hostel  
			 OR (@rFilterId = 3 AND isnull(RT.SourceId,0)!=0) -- Transport  
			 OR ((@rFilterId =1) AND ((isnull(AR.SourceId,0)=0) AND (isnull(RT.SourceId,0)=0)))  
			)  
			AND ((RBM.OrgGroupId=@rOrgGroupId) OR (@rOrgGroupId=0))  
		 AND ((UPPER(R.BankName) like UPPER(@rBankName)) OR(ISNULL(@rBankName,'')=''))  
		 AND (ISNULL(SA.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')    
		 AND (ISNULL(R.BankName,'') IN( SELECT cast(item as nvarchar(100)) FROM  sCommon.fnsplit(@rBankNames,',')) OR @rBankNames='')   
		 AND ((R.ChequeClearDate >= @rFromChequeClearDate)OR(ISNULL(@rFromChequeClearDate,'')=''))      
		 AND ((R.ChequeClearDate <= @rToChequeClearDate)OR(ISNULL(@rToChequeClearDate,'')=''))
		 AND (ISNULL(R.PayTypeId,0) IN (SELECT cast(item as tinyint) FROM sCommon.fnsplit(@rPayTypeIds,',')) OR @rPayTypeIds='')
		 AND (ISNULL(R.ReceiptBookId,0) IN (SELECT cast(item as int) FROM sCommon.fnsplit(@rReceiptBookIds,',')) OR @rReceiptBookIds='')
  
		 ORDER BY R.ReceiptNumber
END
GO
PRINT N'Creating Procedure [sHrm].[pRptEmployeeAvailableLeaveWithPay]...';


GO
CREATE PROCEDURE [sHrm].[pRptEmployeeAvailableLeaveWithPay]

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

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

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

END
GO
PRINT N'Creating Procedure [sProcurement].[pRptStockIssueStudentReceipt]...';


GO

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

			(sFinanceCommon.fNumberToWordsWithDecimal(
			((ISNULL(CAST(SIP.WeightedAverageCost AS DECIMAL(18, 2)), '0')) * (ID.IssuedQuantity)),1,2,SI.OrgGroupId)
			) AS AmountInWords,
			ISNULL(SI.PayTypeId,0) as PayTypeId,
			 ISNULL(PTM.Title,'Not Charged') AS PayType,
			ISNULL(SI.ReceiptNumber,0) as ReceiptNumber,
			ISNULL(SI.BankName,'') as BankName,
			ISNULL(SI.ChequeNo,'') as ChequeNo,
			ISNULL(SI.ChequeDate,'') as ChequeDate,
			@lCurrencyCode AS CurrencyCode,
			@lCurrencyTitle AS CurrencyTitle

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


SET ANSI_NULLS ON
GO
PRINT N'Creating Procedure [sProcurement].[pStockIssueDepositAdjustmentAdd]...';


GO

Create PROCEDURE [sProcurement].[pStockIssueDepositAdjustmentAdd]
	
	@rId bigint, 
	@rAdjustmentId bigint,
	@rDepositHeadId int,
	@rAmount decimal(12,2),
	@rAdjustmentDate date,
	@rRemark nvarchar(200),
	@rCreatedUserId bigint,
	@rOrgGroupId smallint,
	@rAcademicId bigint,
	@rFeeDepositExpenseTypeId int,
	@rOrgGroupBatchId int,
	@rReferenceSourceId bigint
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @lStudentId bigint

BEGIN	
BEGIN TRY
BEGIN TRANSACTION	
 DECLARE @rAdjustmentNumber Int
 Select @rAdjustmentNumber=(MAX(AdjustmentNumber) +1) from sFee.tDepositAdjustment
DECLARE @lNextNumber bigint 
IF NOT EXISTS(Select Id From sFee.tDepositAdjustment Where ReferenceSourceId=@rReferenceSourceId)
BEGIN
EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sFee.tDepositAdjustment', 0, 1, 1,@outNextNumber = @lNextNumber output 
	INSERT INTO sFee.tDepositAdjustment
		(
		Id,
		DepositHeadId,
		Amount,
		AdjustmentDate,
		Remark,
		CreatedUserId,
		CreatedDateTime,
		AcademicId,
		FeeDepositExpenseTypeId,
		AdjustmentNumber,
		OrgGroupBatchId,
		ReferenceSourceTypeId,
		ReferenceSourceId
		)
	VALUES
		(
		@lNextNumber,
		@rDepositHeadId,
		@rAmount,
		@rAdjustmentDate,
		@rRemark,
		@rCreatedUserId,
		sCommon.fGetDate(),
		@rAcademicId,
		@rFeeDepositExpenseTypeId,
		@rAdjustmentNumber,
		@rOrgGroupBatchId,
		2039 ,
		@rReferenceSourceId 
		) 
		SELECT @lNextNumber  AS Result   
	END	
ELSE
    BEGIN
    --SET @lNextNumber = @rId
	    UPDATE sFee.tDepositAdjustment
	    SET
	    DepositHeadId=@rDepositHeadId,
		Amount=@rAmount,
		AdjustmentDate=@rAdjustmentDate,
		Remark=@rRemark,
		UpdatedUserId=@rCreatedUserId,
	    UpdatedDateTime=sCommon.fGetDate(),
	    AcademicId=@rAcademicId,
	    FeeDepositExpenseTypeId=@rFeeDepositExpenseTypeId,
		AdjustmentNumber=@rAdjustmentNumber,
		OrgGroupBatchId=@rOrgGroupBatchId,
		ReferenceSourceTypeId=2039,
		ReferenceSourceId=@rReferenceSourceId
	    WHERE ReferenceSourceId = @rAdjustmentId 
	    SELECT 11 AS Result
	 END  
	COMMIT TRANSACTION
		
END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	
END
GO
PRINT N'Creating Procedure [sProcurement].[pStockKitDetailAdd]...';


GO

 CREATE PROCEDURE [sProcurement].[pStockKitDetailAdd]
	@rStockKitId [int],
	@rStockKitDeatilXml [nvarchar](max)
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	DECLARE @loIdoc INT
	DECLARE @lStockKitDetailCurrentId INT
	DECLARE @lCount INT
	DECLARE @lNextNumber int
		
	EXEC sp_xml_preparedocument @loIdoc OUTPUT, @rStockKitDeatilXml
	CREATE TABLE #TempStockKitDetail (StockKitId INT,StockItemId int,Quantity DECIMAL(22, 6));

	BEGIN TRY
	BEGIN TRANSACTION
		INSERT INTO #TempStockKitDetail (StockKitId,StockItemId,Quantity)
		SELECT  
			StockKitId,
			StockItemId,
			Quantity
		FROM OPENXML (@loIdoc, '/StockKit/StockKitDetail', 1)
			WITH ( 
					StockKitId INT, 
					StockItemId INT,
					Quantity DECIMAL(22, 6)
				  )

		DELETE SKD  
		From sProcurement.tStockKitDetail SKD
		Left JOIN #TempStockKitDetail Temp On SKD.StockKitId=Temp.StockKitId AND TEMP.StockItemId=SKD.StockItemId 
		 Where Temp.StockKitId is null AND SKD.StockKitId =@rStockKitId


		UPDATE SKD
		Set SKD.Quantity=Temp.Quantity
		From sProcurement.tStockKitDetail SKD
		Inner Join #TempStockKitDetail Temp  ON Temp.StockKitId=SKD.StockKitId  AND TEMP.StockItemId=SKD.StockItemId 


		INSERT INTO sProcurement.tStockKitDetail
			(
				StockKitId,
				StockItemId,
				Quantity
			)
		SELECT  
			@rStockKitId,
			Temp.StockItemId,
			Temp.Quantity
		FROM #TempStockKitDetail Temp
		Left JOIN sProcurement.tStockKitDetail SKD On SKD.StockKitId=Temp.StockKitId AND TEMP.StockItemId=SKD.StockItemId 
		Where SKD.StockKitId is null

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

END
GO
PRINT N'Creating Procedure [sProcurement].[pStockKitDetailGet]...';


GO
CREATE PROCEDURE [sProcurement].[pStockKitDetailGet]
	
	 @rStockKitId int = 0,
	 @rStockItemId int=0,
	 @rOrgGroupId int=0,
	 @rStoreLocationId int=0,
	 @rStockKitTitle nvarchar(100)='',
	 @rStockKitCode nvarchar(20)=''
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	SET @rStockKitCode = sCommon.fSearchStringGet(@rStockKitCode)
	SET @rStockKitTitle = sCommon.fSearchStringGet(@rStockKitTitle)
END	 

BEGIN 

	SELECT 
	
	SKD.StockKitId AS StockKitId, 
	SKD.StockItemId AS StockItemId,
	SKD.Quantity AS Quantity,
	ESIM.Code AS StockItemCode,
	ESIM.Title AS StockItemName,
	ISUBM.Title AS ItemSubGroup,
	ESIM.ItemSubGroupId as ItemSubGroupId

     
  FROM sProcurement.tStockKitDetail SKD
  INNER JOIN sProcurement.tStockKitMaster SKM ON SKM.Id=SKD.StockKitId
  Inner Join sIp.vExtStockItemMaster ESIM ON ESIM.Id=SKD.StockItemId  
  Inner Join  [sIp].[tItemSubGroupMaster] ISUBM ON ESIM.ItemSubGroupId=ISUBM.Id
             
  WHERE 
	  (SKM.OrgGroupId = @rOrgGroupId or @rOrgGroupId=0)	
	  AND SKM.StoreLocationId=@rStoreLocationId
      AND ((SKM.Id = @rStockKitId) OR (@rStockKitId = 0))
      AND ((SKM.Code like @rStockKitCode) OR (ISNULL(@rStockKitCode,'') = ''))    
      AND ((SKM.Title like @rStockKitCode) OR (ISNULL(@rStockKitCode,'') = ''))  
	  AND (ESIM.Id =@rStockItemId or @rStockItemId=0)
END
GO
PRINT N'Creating Procedure [sProcurement].[pStockKitIssueDetailAdd]...';


GO
create PROCEDURE [sProcurement].[pStockKitIssueDetailAdd]
	@rStockKitIssueDeatilXml [nvarchar](max)
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
BEGIN TRY
	BEGIN TRANSACTION
	DECLARE @loDoc INT
		
	EXEC sp_xml_preparedocument @loDoc OUTPUT, @rStockKitIssueDeatilXml
		INSERT INTO [sProcurement].[tStockKitIssueDetail]
				   ([StockIssueId]
				   ,[StockKitId])
		SELECT
			StockIssueId,
			StockKitId
		FROM OPENXML (@loDoc, '/Table/StockKitDetail', 1)
		WITH ( 
				StockIssueId int,
				StockKitId int
			  ) 



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



END
GO
PRINT N'Creating Procedure [sProcurement].[pStockKitMasterAdd]...';


GO


CREATE PROCEDURE [sProcurement].[pStockKitMasterAdd]
	 
	@rOrgGroupId int,	
	@rId int,
	@rNumber int,
	@rCode nvarchar(20),
	@rTitle nvarchar(100),
	@rCreatedUserId bigint,
	@rOrderIndex smallint,
	@rIsObsolete bit,
	@rStoreLocationId int,
	@rStockKitDeatilXml [nvarchar](max)=''
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	-- For New Entry
	print 1
	IF(@rId = 0)
		BEGIN
			If Exists(select Id from sProcurement.tStockKitMaster where Code=@rCode AND OrgGroupId= @rOrgGroupId AND StoreLocationId=@rStoreLocationId)
			BEGIN
				select -101 as Result
				RETURN
			END 
			If Exists(select Id from sProcurement.tStockKitMaster where Number=@rNumber AND OrgGroupId= @rOrgGroupId AND StoreLocationId=@rStoreLocationId)
			BEGIN
				select -102 as Result
				RETURN
			END
			If Exists(select Id from sProcurement.tStockKitMaster where OrderIndex=@rOrderIndex AND OrgGroupId= @rOrgGroupId AND StoreLocationId=@rStoreLocationId)
			BEGIN
				select -103 as Result
				RETURN
			END
		 
	 
		END
	-- For Update Entry	
	ELSE
		BEGIN
			If  Exists(select Id from sProcurement.tStockKitMaster where Code=@rCode AND Id<>@rId AND OrgGroupId=@rOrgGroupId AND StoreLocationId=@rStoreLocationId)
			Begin
				select -101 as Result
				RETURN
			End
			If  Exists(select Id from sProcurement.tStockKitMaster where Number=@rNumber AND Id<>@rId AND OrgGroupId=@rOrgGroupId AND StoreLocationId=@rStoreLocationId )
			Begin
				select -102 as Result
				RETURN
			End
			If  Exists(select Id from sProcurement.tStockKitMaster where OrderIndex=@rOrderIndex AND Id<>@rId AND OrgGroupId=@rOrgGroupId AND StoreLocationId=@rStoreLocationId )
			Begin
				select -103 as Result
				RETURN
			End
		END
BEGIN TRY
BEGIN TRANSACTION	
	IF(@rId = 0)	
	 BEGIN
		DECLARE @lNextNumber INT     
		EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sProcurement.tStockKitMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
		print @lNextNumber
		INSERT INTO  sProcurement.tStockKitMaster
			(
				 OrgGroupId,
				 Id,
				 Number,
				 Code,
				 Title,
				 CreatedUserId,
				 CreatedDateTime,
				 OrderIndex,
				 IsObsolete,
				 StoreLocationId
			)
		VALUES
			(
				@rOrgGroupId,
				@lNextNumber,
				@rNumber,
				@rCode,
				@rTitle,
				@rCreatedUserId,
				sCommon.fGetDate(),
				@rOrderIndex,
				@rIsObsolete,
				@rStoreLocationId
			)
			EXECUTE [sProcurement].[pStockKitDetailAdd]  @rStockKitId = @lNextNumber,@rStockKitDeatilXml=@rStockKitDeatilXml
			SELECT 1 AS Result       
	END	 
	ELSE
	BEGIN
		 SET @lNextNumber = @rId
			UPDATE sProcurement.tStockKitMaster
			SET
				 Number=@rNumber,
				 Code=@rCode,
				 Title=@rTitle,
				 OrderIndex=@rOrderIndex,
				 IsObsolete=@rIsObsolete,
				 UpdatedUserId=@rCreatedUserId,
				 UpdatedDateTime=sCommon.fGetDate(),
				 StoreLocationId=@rStoreLocationId
	      
			  WHERE Id = @rId 	
			 EXECUTE [sProcurement].[pStockKitDetailAdd]  @rStockKitId = @lNextNumber,@rStockKitDeatilXml=@rStockKitDeatilXml
			  SELECT 11 AS Result    
	END  
	COMMIT TRANSACTION
		
END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Creating Procedure [sProcurement].[pStockKitMasterGet]...';


GO

CREATE PROCEDURE [sProcurement].[pStockKitMasterGet]
	
	 @rId int = 0,
	 @rTitle nvarchar(100)='',
	 @rCode nvarchar(20)='',
	 @rOrgGroupId int,
	 @rStoreLocationId int
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	SET @rCode = sCommon.fSearchStringGet(@rCode)
	SET @rTitle = sCommon.fSearchStringGet(@rTitle)
END	 

BEGIN 

	SELECT 
	
	CTM.Id, 
	CTM.Code,
	CTM.Number,
	CTM.Title,
	CTM.IsObsolete,
	CTM.OrderIndex,
	OrgGroupId,
	CreatedUserId,
	StoreLocationId,
	CreatedDateTime
     
  FROM sProcurement.tStockKitMaster CTM         
             
  WHERE 
	  CTM.OrgGroupId = @rOrgGroupId		
	  AND StoreLocationId=@rStoreLocationId
      AND ((CTM.Id = @rId) OR (@rId = 0))
      AND ((CTM.Code like @rCode) OR (ISNULL(@rCode,'') = ''))    
      AND ((CTM.Title like @rTitle) OR (ISNULL(@rTitle,'') = '')) 
	  Order By CTM.OrderIndex DESC 
	  
	EXECUTE [sProcurement].[pStockKitDetailGet]   @rStockKitId=@rId,@rOrgGroupId =@rOrgGroupId,@rStoreLocationId=@rStoreLocationId,@rStockKitTitle=@rTitle,@rStockKitCode=@rCode
	  
END
GO

PRINT N'Update complete.';


GO

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

