
/****** Object:  StoredProcedure [sHrm].[pEmployeeDataModify]    Script Date: 08/10/2015 11:21:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [sHrm].[pEmployeeDataModify]	 
	@rCategoryId int,
	@rXML nvarchar(max),
	@rSubCategoryField nvarchar(100),
	@rOrgId int = 0
		
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
DECLARE @lInterrorCode INT
Declare @Sql nvarchar(max) 
DECLARE @loDoc INT

--BEGIN TRY
--BEGIN TRANSACTION

EXEC sp_xml_preparedocument @loDoc OUTPUT,@rXML
BEGIN
	
	IF(@rSubCategoryField = 'Gender' OR @rSubCategoryField = 'Religion' OR @rSubCategoryField = 'BloodGroup'
	OR @rSubCategoryField = 'MaritalStatus' OR @rSubCategoryField = 'OrganizationDepartment' OR @rSubCategoryField = 'EmployeeType'
			OR @rSubCategoryField = 'EmployeeGroup' OR @rSubCategoryField = 'EmployeeGrade' OR @rSubCategoryField = 'Relationship' OR  @rSubCategoryField = 'EmployeeDesignation'
			OR  @rSubCategoryField = 'Payroll' OR  @rSubCategoryField = 'PayrollGroup'
			)
	BEGIN
		SET @rSubCategoryField = @rSubCategoryField + 'Id'
	END
	--ELSE IF (@rSubCategoryField = 'MaritalStatusId' OR @rSubCategoryField = 'OrganizationDepartmentId' OR @rSubCategoryField = 'EmployeeTypeId'
	--		OR @rSubCategoryField = 'EmployeeGroupId' OR @rSubCategoryField = 'EmployeeGradeId' OR @rSubCategoryField = 'RelationshipId'  )
	--	BEGIN
	--		SET @rSubCategoryField = @rSubCategoryField 
	--	END
			
	CREATE TABLE ListofData(Id bigint,EmployeeId bigint,SubCategory nvarchar(100),UpdatedUserId bigint,IsExist bit);
	IF(@rCategoryId= 1)
		BEGIN
		insert into ListofData
		select 0,E.EmployeeId,E.SubCategory,E.UpdatedUserId,0
		from
		(
				SELECT EmployeeId,SubCategory,UpdatedUserId FROM
				OPENXML (@loDoc, '/Employees/Employees', 2)
				WITH (
						EmployeeId BIGINT, 
						SubCategory nvarchar(100),
						UpdatedUserId bigint)	
						)E 	
		UPDATE ListofData SET IsExist=1 WHERE EmployeeId NOT IN(SELECT EmployeeId FROM sHrm.tEmployeeCommunications) 
		
		set @sql='INSERT INTO sHrm.tEmployeeCommunications 
					(EmployeeId,'+@rSubCategoryField+',CreatedUserId,CreatedDateTime)
					SELECT
						LD.EmployeeId,Ld.SubCategory,LD.UpdatedUserId,sCommon.fGetDate()
					FROM
						ListofData LD WHERE LD.IsExist=1'
		Exec sp_executesql @sql
		set @sql='UPDATE sHrm.tEmployeeCommunications set '+@rSubCategoryField+' = LD.SubCategory,
					UpdatedUserId=LD.UpdatedUserId,
					UpdatedDateTime=sCommon.fGetDate()  
					from sHrm.tEmployeeCommunications EC 
					INNER JOIN ListofData LD ON EC.EmployeeId=LD.EmployeeId AND IsExist=0'
		
		Exec sp_executesql @sql
		
		
		
		Drop Table ListofData
			 SELECT 1 AS StatusId
	    END
	    
	Else IF(@rCategoryId= 2)
		BEGIN
		insert into ListofData
		select 0,E.EmployeeId,E.SubCategory,E.UpdatedUserId,0
		from
		(
				SELECT EmployeeId,SubCategory,UpdatedUserId FROM
				OPENXML (@loDoc, '/Employees/Employees', 2)
				WITH (
						EmployeeId BIGINT, 
						SubCategory nvarchar(100),
						UpdatedUserId bigint)	
						)E 	
		UPDATE ListofData SET IsExist=1 WHERE EmployeeId NOT IN(SELECT EmployeeId FROM sHrm.tEmployeePersonalDetail) 
		
		IF(@rSubCategoryField = 'DateOfBirth')
		BEGIN			
					
			 				
			set @sql='UPDATE sHrm.tEmployeePersonalDetail set '+@rSubCategoryField+' = LD.SubCategory,
						UpdatedUserId=LD.UpdatedUserId,
						UpdatedDateTime=sCommon.fGetDate()  
						from sHrm.tEmployeePersonalDetail EPD 
						INNER JOIN ListofData LD ON EPD.EmployeeId=LD.EmployeeId'			
			Exec sp_executesql @sql
			set @sql='UPDATE sHrm.tEmployeePersonalDetail SET '+@rSubCategoryField+' = null WHERE DateOfBirth=''1900-01-01'''
			Exec sp_executesql @sql
		END
		ELSE
		BEGIN
			
			 				
			set @sql='UPDATE sHrm.tEmployeePersonalDetail set '+@rSubCategoryField+' = LD.SubCategory,
						UpdatedUserId=LD.UpdatedUserId,
						UpdatedDateTime=sCommon.fGetDate()  
						from sHrm.tEmployeePersonalDetail EPD 
						INNER JOIN ListofData LD ON EPD.EmployeeId=LD.EmployeeId'
			
			Exec sp_executesql @sql
		END
		Drop Table ListofData
			 SELECT 1 AS StatusId
	    END	
	    
	Else IF(@rCategoryId= 3)
		BEGIN
		insert into ListofData
		select 0,E.EmployeeId,E.SubCategory,E.UpdatedUserId,0
		from
		(
				SELECT EmployeeId,SubCategory,UpdatedUserId FROM
				OPENXML (@loDoc, '/Employees/Employees', 2)
				WITH (
						EmployeeId BIGINT, 
						SubCategory nvarchar(100),
						UpdatedUserId bigint)	
						)E 	
	
		set @sql='UPDATE sHrm.tEmployeeJobDetail set '+@rSubCategoryField+' = LD.SubCategory,
					UpdatedUserId=LD.UpdatedUserId,
					UpdatedDateTime=sCommon.fGetDate()  
					from sHrm.tEmployeeJobDetail EJ 
					INNER JOIN ListofData LD ON EJ.EmployeeId=LD.EmployeeId'
		
		Exec sp_executesql @sql
		Drop Table ListofData
			 SELECT 1 AS StatusId
	    END	
  Else IF(@rCategoryId= 4)
	BEGIN
		insert into ListofData
		select 0,E.EmployeeId,E.SubCategory,E.UpdatedUserId,0
		from
		(
				SELECT EmployeeId,SubCategory,UpdatedUserId FROM
				OPENXML (@loDoc, '/Employees/Employees', 2)
				WITH (
						EmployeeId BIGINT, 
						SubCategory nvarchar(100),
						UpdatedUserId bigint)	
						)E 	
		UPDATE ListofData SET IsExist=1 WHERE EmployeeId NOT IN(SELECT EmployeeId FROM sHrm.tEmployeeMaster) 
		
		
		set @sql='UPDATE sHrm.tEmployeeMaster set '+@rSubCategoryField+' = LD.SubCategory,
					UpdatedUserId=LD.UpdatedUserId,
					UpdatedDateTime=sCommon.fGetDate()  
					from sHrm.tEmployeeMaster HEM 
					INNER JOIN ListofData LD ON HEM.Id=LD.EmployeeId AND IsExist=0'
		
		Exec sp_executesql @sql
		Drop Table ListofData
			SELECT 1 AS StatusId
	    END
	    
	    Else IF(@rCategoryId= 5)
		BEGIN
		insert into ListofData
		select 0,E.EmployeeId,E.SubCategory,E.UpdatedUserId,0
		from
		(
				SELECT EmployeeId,SubCategory,UpdatedUserId FROM
				OPENXML (@loDoc, '/Employees/Employees', 2)
				WITH (
						EmployeeId BIGINT, 
						SubCategory nvarchar(100),
						UpdatedUserId bigint)	
						)E 	
						
		
		set @sql='UPDATE sHrm.tEmployeeStatutoryAndOther set '+@rSubCategoryField+' = LD.SubCategory,
					UpdatedUserId=LD.UpdatedUserId,
					UpdatedDateTime=sCommon.fGetDate()  
					from sHrm.tEmployeeStatutoryAndOther ESO 
					INNER JOIN ListofData LD ON ESO.EmployeeId=LD.EmployeeId'
		
		Exec sp_executesql @sql
		Drop Table ListofData
			 SELECT 1 AS StatusId
	    END	
	    
	    
END 
	--   COMMIT TRANSACTION
		
	--END TRY
	
	--BEGIN CATCH 
	--	ROLLBACK TRANSACTION
	--	SELECT -1 AS Result
	--END CATCH


GO
ALTER PROCEDURE [sSetup].[pBatchMasterAdd]
	 
	@rId int,
	@rCode nvarchar(20),
	@rTitle nvarchar(100),
	@rOrgId int,
	@rIsHistory bit,
	@rIsObsolete bit,
	@rOrderIndex smallint,
	@rFromDate date,
	@rToDate date,
	@rCreatedUserId bigint,
	@rUpdatedUserId bigint,
	@rOrgGroupBatchId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	IF(@rId = 0)	
	BEGIN
	DECLARE @lBatchCount int,@lBatchCountActive int
	DECLARE @lTermPattern int
	DECLARE @lIsInContext bit,@lIsActive bit
	
	SELECT @lBatchCountActive = ISNULL(COUNT(ID),0) FROM sSetup.tBatchMaster WHERE OrgId = @rOrgId
	
	SELECT @lBatchCount = ISNULL(COUNT(ID),0) FROM sSetup.tBatchMaster WHERE OrgGroupBatchId = @rOrgGroupBatchId AND OrgId = @rOrgId
	SELECT @lTermPattern = TermPatternId FROM sSetup.tOrganizationMaster WHERE  Id = @rOrgId
	
	IF (@lBatchCount >= @lTermPattern)
	BEGIN
	SELECT -101 AS Result
	RETURN
	END
	
	IF (@lBatchCountActive = 0)
	SET @lIsActive = 1
	ELSE
	SET @lIsActive = 0
	
	IF (@lTermPattern = 1)
		SET @lIsInContext = 1
	ELSE IF (@lTermPattern = 2 AND  @lBatchCount = 0)
		SET @lIsInContext = 1
	END
	--BEGIN
	--		IF EXISTS(SELECT 1 FROM sSetup.tBatchMaster WHERE OrgId=@rOrgId AND OrgGroupBatchId=@rOrgGroupBatchId)
	--		BEGIN
	--			SELECT -100 AS Result
	--			RETURN
	--		END
	--END
	
	 BEGIN TRANSACTION	
 IF(@rId = 0)	
BEGIN

	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sSetup.tBatchMaster', 0, 1, 1,@outNextNumber = @lNextNumber output

	INSERT INTO sSetup.tBatchMaster
		(
			Id,
			Code,
			Title,
			OrgId,
			IsHistory,
			IsObsolete,
			OrderIndex,
			FromDate,
			ToDate,
			CreatedUserId,
			CreatedDateTime,
			OrgGroupBatchId,
			IsInContext,
			IsActive
		)
	VALUES
		(
			@lNextNumber,
			@rCode,
			@rTitle,
			@rOrgId,
			@rIsHistory,
			@rIsObsolete,
			@rOrderIndex,
			@rFromDate,
			@rToDate,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rOrgGroupBatchId,
			@lIsInContext,
			@lIsActive
		)
		             
	END	 
	 ELSE
	 BEGIN
	 SET @lNextNumber = @rId
	    UPDATE  sSetup.tBatchMaster
	    SET
			Code=@rCode,
			Title=@rTitle,
			OrgId=@rOrgId,
			--IsHistory=@rIsHistory,
			IsObsolete=@rIsObsolete,
			OrderIndex=@rOrderIndex,
			FromDate=@rFromDate,
			ToDate=@rToDate,
			UpdatedUserId=@rUpdatedUserId,
			UpdatedDateTime=sCommon.fGetDate()
			
	      WHERE Id = @rId 	   
	 END  
		SELECT @lNextNumber AS Result
	
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END


GO
ALTER PROCEDURE [sSetup].[pBatchMasterGet]
	 @rId int = 0,
	 @rCode nvarchar(20),
	 @rTitle nvarchar(100),
	 @rOrgId int,
	 @rIsSearch bit=0,
	 @rOrgGroupBatchId int=0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	 IF(@rIsSearch=1)
		BEGIN
			SET @rCode = sCommon.fSearchStringGet(@rCode)
			SET @rTitle = sCommon.fSearchStringGet(@rTitle)
		END
	
	SELECT 
	
			BM.Id,
			BM.Code,
			BM.Title,
			BM.OrgId,
			BM.IsHistory,
			BM.IsObsolete,
			ISNULL(BM.IsActive,0) AS IsActive,
			BM.OrderIndex,
			BM.FromDate,
			BM.ToDate,
			BM.CreatedUserId,
			BM.CreatedDateTime,
			BM.UpdatedUserId,
			BM.UpdatedDateTime,
			ISNULL(BM.OrgGroupBatchId,0) AS OrgGroupBatchId,
			ISNULL(BM.IsInContext,0) AS IsInContext,
			OGBM.Title AS OrgGroupBatchTitle,
			OM.DisplayTitle AS OrganizationName,
			OGBM.Id AS OrgGroupBatchId,
			OGBM.OrgGroupId
			
			
  FROM 
            sSetup.tBatchMaster BM
            INNER JOIN sSetup.tOrgGroupBatchMaster OGBM
				ON OGBM.Id = BM.OrgGroupBatchId
			INNER JOIN sSetup.tOrganizationMaster OM
				ON OM.Id = BM.OrgId
  WHERE 
  
    ((BM.Id = @rId) OR (@rId = 0))
    AND ((BM.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))  
	AND ((BM.Code like @rCode) OR (ISNULL(@rCode,'') = '')) 
	AND ((BM.Title like @rTitle) OR (ISNULL(@rTitle,'') = '')) 
	AND ((BM.OrgId = @rOrgId) OR (@rOrgId = 0))  
	--AND(BM.OrgId=@rOrgId)

ORDER BY OM.Id, BM.OrderIndex ASC
END

GO

 
/****** Object:  StoredProcedure [sCommon].[pOrganizationSetupAdd]    Script Date: 08/10/2015 11:24:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [sCommon].[pOrganizationSetupAdd]
    @rOrgId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

SET @rOrgId = @rOrgId
DECLARE @lCreatedDateTime DATETIME
SET @lCreatedDateTime =  GETDATE()

DECLARE @lCreatedUserId Int
SET @lCreatedUserId = 1
DECLARE @lOrgGroupId smallint
DECLARE @lOrgGroupBatchId int 
BEGIN TRY
BEGIN TRANSACTION


 SELECT @lOrgGroupId=OrgGroupId from sSetup.tOrganizationMaster where Id=@rOrgId
 
 SELECT @lOrgGroupBatchId=ISNULL(MIN(Id),(Convert(nvarchar(10),Convert(nvarchar(10),@lOrgGroupId))+'00000')+1) 
	FROM sSetup.tOrgGroupBatchMaster WHERE OrgGroupId=@lOrgGroupId
 
 
 INSERT INTO [sCommon].[tTableRowId]
           ([OrgId]
           ,[TableTitle]
           ,[CurrentNumber]
           ,[Seed]
           ,[Increment])
SELECT
		   @rOrgId
		  ,[TableTitle]
		  
		  ,CASE 
      WHEN IdDataType = 'int' THEN sCommon.fGetIntIdForOrg(ISNULL(CurrentNumber,0),@rOrgId) 
      WHEN IdDataType = 'bigint' THEN sCommon.fGetbigIntIdForOrg(ISNULL(CurrentNumber,0),@rOrgId )
		END 
		
		  ,CASE 
      WHEN IdDataType = 'int' THEN sCommon.fGetIntIdForOrg(ISNULL(CurrentNumber,0),@rOrgId) 
      WHEN IdDataType = 'bigint' THEN sCommon.fGetbigIntIdForOrg(ISNULL(CurrentNumber,0),@rOrgId )
		END  
		   ,1
	  FROM [sCommon].[tTableRowIdMaster] where 
	  (tableType = 'Non Static' or tableType = '100Static' )
	  AND iddatatype <> '' AND IdScope <> 'OrgGroup'
	  

 
 INSERT INTO [sSetup].[tBatchMaster]
           ([Id]
           ,[Code]
           ,[Title]
           ,[OrgId]
           ,[IsHistory]
           ,[IsActive]
           ,[IsObsolete]
           ,[OrderIndex]
           ,[FromDate]
           ,[ToDate]
           ,[OrgGroupBatchId]
           ,[CreatedUserId]
           ,[CreatedDateTime]
           ,[UpdatedUserId]
           ,[UpdatedDateTime]
           ,IsInContext)
     
      SELECT sCommon.fGetIntIdForOrg(1,@rOrgId)
           ,Code
           ,Title
           ,@rOrgId
           ,0
           ,1
           ,0
           ,1
           ,FromDate
           ,ToDate
           ,@lOrgGroupBatchId
		  ,@lCreatedUserId
		  ,@lCreatedDateTime
		  ,@lCreatedUserId
		  ,@lCreatedDateTime
		  ,1
           FROM [sSetup].[tOrgGroupBatchMaster] WHERE  Id =  @lOrgGroupBatchId

 IF (  @lOrgGroupId = @rOrgId)
 BEGIN
	  INSERT INTO sSecurity.tUser 
		   ([Id]
           ,[DisplayTitle]
           ,[LoginTitle]
           ,[LoginPassword]
           ,[LastLockOutDate]
           ,[NumberOfLogonAttempt]
           ,[IsObsolete]
           ,[CreatedUserId]
           ,[CreatedDateTime]
           ,[UpdatedUserId]
           ,[UpdatedDateTime]
           --,[EmployeeId]
           ,[SessionId]
           ,[OrgId]
           ,[IsCommon])		  
	SELECT 
		   sCommon.fGetBigIntIdForOrg(Id,@rOrgId)
		  ,[DisplayTitle]
		  ,[LoginTitle]
		  ,[LoginPassword]
		  ,@lCreatedDateTime
		  ,[NumberOfLogonAttempt]
		  ,[IsObsolete]
		  ,@lCreatedUserId
		  ,@lCreatedDateTime
		  ,@lCreatedUserId
		  ,@lCreatedDateTime
		  --,sCommon.fGetIntIdForOrg(1,@rOrgId)
		  ,[SessionId]
		  ,@rOrgId
		  ,1
	  FROM [sSecurity].[tUser] WHERE Id < 4 AND OrgId=1
	  
	  
	  --OrgId is not used
INSERT INTO [sSecurity].[tUserRole]
           (
            [RoleId]
           ,[UserId]
           ,[IsObsolete]
           ,[IsPrimary]
           ,[CreatedUserId]
           ,[CreatedDateTime]
           ,[UpdatedUserId]
           ,[UpdatedDateTime]
           )
SELECT 
		   sCommon.fGetIntIdForOrg(2,@lOrgGroupId)
		  ,sCommon.fGetBigIntIdForOrg(3,@lOrgGroupId)
		  ,0
		  ,1
		  ,@lCreatedUserId
		  ,@lCreatedDateTime
		  ,@lCreatedUserId
		  ,@lCreatedDateTime
	  
END
  
	
 
 

	  --OrgId is not used
INSERT INTO sSecurity.tUserOrganization
           (
            [OrgId]
           ,[UserId]
           
           )
SELECT 
		   @rOrgId
		  ,sCommon.fGetBigIntIdForOrg(3,@lOrgGroupId)
		   


INSERT INTO [sConfig].[tColorLegendDetail]
           ([OrgId]
           ,[Id]
           ,[Title]
           ,[ColorLegendId]
           ,[configSettingId])
	SELECT
		   @rOrgId
		  ,sCommon.fGetIntIdForOrg(Id,@rOrgId)
		  ,[Title]
		  ,[ColorLegendId]
		  ,[configSettingId]
	  FROM [sConfig].[tColorLegendDetail] WHERE OrgId=1
	  
	  
 







INSERT INTO [sHrm].[tEmployeeTypeMaster]
           ([OrgId]
           ,[Id]
           ,[Code]
           ,[Title]
           ,[IsObsolete]
           ,[CreatedUserId]
           ,[CreatedDateTime]
           ,[UpdatedUserId]
           ,[UpdatedDateTime])			
	 SELECT
			@rOrgId
		   ,sCommon.fGetIntIdForOrg(Id,@rOrgId)
		   ,[Code]
		   ,[Title]
           ,[IsObsolete]
		   ,@lCreatedUserId
		   ,@lCreatedDateTime
		   ,@lCreatedUserId
		   ,@lCreatedDateTime
     FROM [sHrm].[tEmployeeTypeMaster] WHERE OrgId=1
     

           
  -- INSERT INTO [sPayroll].[tPayElement]
  --         (
		--			[ID]
		--		   ,[Code]
		--		   ,[Title]
		--		   ,[PayElementTypeId]
		--		   ,[PaidCategoryId]
		--		   ,[IsLwp]
		--		   ,[IsEmployeeSpecific]
		--		   ,[IsDepend]
		--		   ,[IsAvailDuringProbation]
		--		   ,[IsIncludeInPaySlip]
		--		   ,[SortOrder]
		--		   ,[IsObsolete]
		--		   ,[OrgId]
		--		   ,[CreatedUserId]
		--		   ,[CreatedDateTime]
		--		   ,[UpdatedUserId]
		--		   ,[UpdatedDateTime]
  --         )     
  --         SELECT  sCommon.fGetIntIdForOrg(ID,@rOrgId)
		--		  ,[Code]
		--		  ,[Title]
		--		  ,[PayElementTypeId]
		--		  ,[PaidCategoryId]
		--		  ,[IsLwp]
		--		  ,[IsEmployeeSpecific]
		--		  ,[IsDepend]
		--		  ,[IsAvailDuringProbation]
		--		  ,[IsIncludeInPaySlip]
		--		  ,[SortOrder]
		--		  ,[IsObsolete]
		--		  ,@rOrgId
		--		  ,@lCreatedUserId
		--		  ,@lCreatedDateTime
		--		  ,@lCreatedUserId
		--		  ,@lCreatedDateTime
    
  --FROM  [sPayroll].[tPayElement] Where OrgId = 1 
  --AND Id < 101
           
  INSERT INTO [sSetup].[tDepartmentMaster]
           (
					[ID]
				   ,[Code]
				   ,[Title]
				   ,[DisplayTitle]
				   ,[OrgId]
				   ,[IsObsolete]
				   ,[CreatedUserId]
				   ,[CreatedDateTime]
				   ,[UpdatedUserId]
				   ,[UpdatedDateTime]
           )     
           SELECT  sCommon.fGetIntIdForOrg(1,@rOrgId)
				  ,'Common'
				  ,'Common'
				  ,'Common'
				  ,@rOrgId
				  ,0
				  ,@lCreatedUserId
				  ,@lCreatedDateTime
				  ,@lCreatedUserId
				  ,@lCreatedDateTime
    
  --FROM  [sSetup].[tDepartmentMaster] Where OrgId = 1 
  
  
   --INSERT INTO [sIp].[tStoreLocationMaster]
 --         ([OrgId]
 --          ,[Id]
 --          ,[Code]
 --          ,[Title]
 --          ,[IsObsolete]
 --          ,[CreatedUserId]
 --          ,[CreatedDateTime]
 --          ,[UpdatedUserId]
 --          ,[UpdatedDateTime]
 --          ,[LocationId]
 --          ,[IsShowDependent])
	--SELECT
	--   @rOrgId
 --     ,sCommon.fGetIntIdForOrg(Id,@rOrgId)
 --     ,[Code]
 --     ,[Title]
 --     ,[IsObsolete]
 --     ,@lCreatedUserId
	--  ,@lCreatedDateTime
	--  ,@lCreatedUserId
	--  ,@lCreatedDateTime
 --     ,sCommon.fGetIntIdForOrg(LocationId,@rOrgId)
 --     ,[IsShowDependent]
 -- FROM [sIp].[tStoreLocationMaster] WHERE OrgId =1
  
  
  
--  INSERT INTO  [sCommon].[tCountryMaster]
--           ([OrgId]
--           ,[Title]
--           ,[Nationality]
--           ,[IsObsolete]
--           ,[CreatedUserId]
--           ,[CreatedDateTime]
--           ,[UpdatedUserId]
--           ,[UpdatedDateTime]
--           ,[Id]
--           ,[IsShow])
--     SELECT
--            @rOrgId
--           ,[Title]
--           ,[Nationality]
--           ,[IsObsolete]
--		  ,@lCreatedUserId
--		  ,@lCreatedDateTime
--		  ,@lCreatedUserId
--		  ,@lCreatedDateTime
--           ,sCommon.fGetIntIdForOrg(Id,@rOrgId)
--           ,[IsShow]
--FROM [sCommon].[tCountryMaster] WHERE  OrgId = 1



 --INSERT INTO [sCommon].[tLocationMaster]
 --          ([Id]
 --          ,[OrgId]
 --          ,[Code]
 --          ,[Title]
 --          ,[StreetOne]
 --          ,[StreetTwo]
 --          ,[StreetThree]
 --          ,[CityName]
 --          ,[StateName]
 --          ,[CountryName]
 --          ,[ZipCode]
 --          ,[PhoneNo]
 --          ,[FaxNo]
 --          ,[NameOfOccupier]
 --          ,[AddressOfOccupier]
 --          ,[SmallAddress1]
 --          ,[SmallAddress2]
 --          ,[RegistrationNumber]
 --          ,[LicenceNumber]
 --          ,[EsicNumber]
 --          ,[EsicAddress1]
 --          ,[EsicAddress2]
 --          ,[NatureOfIndustry]
 --          ,[ByProduct]
 --          ,[DangerousOperation]
 --          ,[NatureOfTest]
 --          ,[NatureOfTestNonHazardous]
 --          ,[RowMaterialUsed]
 --          ,[HazardousProcess]
 --          ,[Cmo]
 --          ,[NicNumber]
 --          ,[IsObsolete]
 --          ,[CreatedUserId]
 --          ,[CreatedDateTime]
 --          ,[UpdatedUserId]
 --          ,[UpdatedDateTime]
 --          ,[CountryId]
 --          ,[StateId])
           
 -- SELECT sCommon.fGetIntIdForOrg(Id,@rOrgId)
 --     ,@rOrgId
 --     ,[Code]
 --     ,[Title]
 --     ,[StreetOne]
 --     ,[StreetTwo]
 --     ,[StreetThree]
 --     ,[CityName]
 --     ,[StateName]
 --     ,[CountryName]
 --     ,[ZipCode]
 --     ,[PhoneNo]
 --     ,[FaxNo]
 --     ,[NameOfOccupier]
 --     ,[AddressOfOccupier]
 --     ,[SmallAddress1]
 --     ,[SmallAddress2]
 --     ,[RegistrationNumber]
 --     ,[LicenceNumber]
 --     ,[EsicNumber]
 --     ,[EsicAddress1]
 --     ,[EsicAddress2]
 --     ,[NatureOfIndustry]
 --     ,[ByProduct]
 --     ,[DangerousOperation]
 --     ,[NatureOfTest]
 --     ,[NatureOfTestNonHazardous]
 --     ,[RowMaterialUsed]
 --     ,[HazardousProcess]
 --     ,[Cmo]
 --     ,[NicNumber]
 --     ,[IsObsolete]
 --   ,@lCreatedUserId
 -- ,@lCreatedDateTime
 -- ,@lCreatedUserId
 -- ,@lCreatedDateTime
 --     ,sCommon.fGetIntIdForOrg(CountryId,@rOrgId) 
 --     ,sCommon.fGetIntIdForOrg(StateId,@rOrgId) 
 -- FROM [sCommon].[tLocationMaster]  where OrgId=1   	




--INSERT INTO  [sCommon].[tStateMaster]
--           ([OrgId]
--           ,[CountryId]
--           ,[Title]
--           ,[CreatedUserId]
--           ,[CreatedDateTime]
--           ,[UpdatedUserId]
--           ,[UpdatedDateTime]
--           ,[IsObsolete]
--           ,[Id]
--           ,[IsShow])
--     sELECT
--			@rOrgId
--           ,sCommon.fGetIntIdForOrg(CountryId,@rOrgId)
--           ,[Title]
--		   ,@lCreatedUserId
--		   ,@lCreatedDateTime
--		   ,@lCreatedUserId
--		   ,@lCreatedDateTime
--           ,[IsObsolete]
--           ,sCommon.fGetIntIdForOrg(Id,@rOrgId)
--           ,[IsShow] 
--FROM [sCommon].[tStateMaster] wHERe OrgId = 1

--INSERT INTO sSecurity.tModulePermission
--           ([OrgId]
--           ,[ModuleId]
--			)
--			Values
--			(
--		   @rOrgId
--		   ,1
--		   )
 
 --INSERT INTO [sSecurity].[tPermission]
 --          ([TaskId]
 --          ,[RoleId]
 --          ,[Permission]
 --          ,[CreatedUserId]
 --          ,[CreatedDateTime]
 --          ,[UpdatedUserId]
 --          ,[UpdatedDateTime]
 --          ,[IsObsolete]
 --          ,[OrgId])
	--SELECT
	--      [TaskId]
 --          ,sCommon.fGetIntIdForOrg(RoleId,@rOrgId)
 --          ,[Permission]
 --          ,@lCreatedUserId
	--	   ,@lCreatedDateTime
	--	   ,@lCreatedUserId
	--       ,@lCreatedDateTime
 --          ,[IsObsolete]
 --          ,@rOrgId
	--  FROM [sSecurity].[tPermission] WHERE RoleId = 2 AND OrgId = 1
	
	
	--INSERT INTO [sCommon].[tReligionMaster]
--           ([Id]
--           ,[Code]
--           ,[Title]
--           ,[IsObsolete]
--           ,[CreatedUserId]
--           ,[CreatedDateTime]
--           ,[UpdatedUserId]
--           ,[UpdatedDateTime]
--           ,[OrgGroupId])
--     SELECT      
--			 sCommon.fGetIntIdForOrg(Id,@rOrgId)
--			,[Code]
--			,[Title]
--            ,[IsObsolete]
--			,@lCreatedUserId
--		    ,@lCreatedDateTime
--		    ,@lCreatedUserId
--		    ,@lCreatedDateTime
--		    ,@rOrgId
--     FROM [sCommon].[tReligionMaster] WHERE OrgId=1

 --INSERT INTO [sSecurity].[tUserLocation]
 --          ([StoreLocationId]
 --          ,[UserId]
 --          ,[CreatedUserId]
 --          ,[CreatedDateTime])
           
 --  select 
	--sCommon.fGetIntIdForOrg(Id,@rOrgId),
	--sCommon.fGetBigIntIdForOrg(3,@rOrgId)
	--,@lCreatedUserId
	--,@lCreatedDateTime
 --from sIp.tStoreLocationMaster where OrgId=1   
 
 --INSERT INTO sAccounting.tLedgerGroupMaster
--           (Id,
--           Code,
--           Title,
--           IsObsolete,
--           LedgerMainGroupId,
--           AccountingYearId,
--           CreatedUserId,
--           CreatedDateTime,
--           UpdatedUserId,
--           UpdatedDateTime,
--           OrgId
--           )
--	SELECT
--		   sCommon.fGetIntIdForOrg(Id,@rOrgId),
--		   Code,
--		   Title,
--		   IsObsolete,
--		   sCommon.fGetIntIdForOrg(LedgerMainGroupId,@rOrgId),
--		   sCommon.fGetIntIdForOrg(AccountingYearId,@rOrgId),
--		   @lCreatedUserId,
--		   @lCreatedDateTime,
--		   @lCreatedUserId,
--		   @lCreatedDateTime,
--		   @rOrgId
--	  FROM [sAccounting].[tLedgerGroupMaster] WHERE AccountingYearId=sCommon.fGetIntIdForOrg(AccountingYearId,@rOrgId)  
    
--INSERT INTO sAccounting.tLedgerMaster
--           (Id,
--           Code,
--           Title,
--           IsObsolete,
--           LedgerGroupId,
--           OpeningBalance,
--           LedgerTypeId,
--           AccountingYearId,
--           CreatedUserId,
--           CreatedDateTime,
--           UpdatedUserId,
--           UpdatedDateTime
--           )
--	SELECT
--		   sCommon.fGetIntIdForOrg(Id,@rOrgId),
--		   Code,
--		   Title,
--		   IsObsolete,
--		   sCommon.fGetIntIdForOrg(LedgerGroupId,@rOrgId),
--		   OpeningBalance,
--		   sCommon.fGetIntIdForOrg(LedgerTypeId,@rOrgId),
--		   sCommon.fGetIntIdForOrg(AccountingYearId,@rOrgId),
--		   @lCreatedUserId,
--		   @lCreatedDateTime,
--		   @lCreatedUserId,
--		   @lCreatedDateTime
--	  FROM [sAccounting].[tLedgerMaster] WHERE AccountingYearId=sCommon.fGetIntIdForOrg(AccountingYearId,@rOrgId)
	  
--INSERT INTO [sCommon].[tFilePath]
--           ([Id]
--           ,[SourceTypeId]
--           ,[FilePath]
--           ,[OrgId])
-- SELECT sCommon.fGetIntIdForOrg(Id,@rOrgId)
--      ,[SourceTypeId]
--      ,[FilePath]
--      ,@rOrgId
--  FROM [sCommon].[tFilePath] WHERE OrgId =1   
           
--      UPDATE [sCommon].[tFilePath] SET FilePath = REPLACE (Filepath, '/1/', '/'+CONVERT(Nvarchar(100),@rOrgId)+'/') where OrgId = @rOrgId 

--INSERT INTO sConfig.tCultureConfiguration
--           (
--			OrgId,
--			DateSeparator,
--			Culture,
--			LongDatePattern,
--			LongTimePattern,
--			NumberGroupSeparator,
--			NumberNegativePattern,
--			ShortDatePattern,
--			ShortTimePattern,
--			NumberGroupSizes,
--			UpdatedUserId,
--			UpdatedDateTime
--           )
     
--      SELECT 
--            @rOrgId,
--            DateSeparator,
--			Culture,
--			LongDatePattern,
--			LongTimePattern,
--			NumberGroupSeparator,
--			NumberNegativePattern,
--			ShortDatePattern,
--			ShortTimePattern,
--			NumberGroupSizes,
--		    @lCreatedUserId,
--		    @lCreatedDateTime
--           FROM sConfig.tCultureConfiguration WHERE  OrgId =  1

COMMIT TRANSACTION	 
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_MESSAGE()
END CATCH
END

GO

ALTER PROCEDURE [sTransport].[pRptRoutewiseStudentListGet]
	 
	
	 
	 @rOrgGroupBatchId int,
	 @rOrgId int,
	 @rDepartmentId int,
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rClassId int,
	 @rDivisionId int,
	 @rRouteId int,
	 @rDestinationId int
	 
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 

	SELECT 
	        @rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			AR.SourceId,
			SM.Code,
			SM.FirstName+' '+SM.LastName AS Name,
			CM.DisplayTitle AS Class,
			DM.DisplayTitle AS Division,
			BRDM.Title AS DestinationPoint,
			BRM.Title AS BusRoute,
			A.RollNo,
			VM.Title AS Vehicle,
			VM.NoOfSeats,
			BRM.OrderIndex AS RouteOrder,
			BRDM.OrderIndex AS PickupOrder,
			DM.OrderIndex AS DivisionOrderIndex,
			OM.Id AS OrgId,
			OM.DisplayTitle AS OrganizationName,
			DMast.Id AS DepartmentId,
			DMast.Title AS DepartmentName,
			SC.ParentContactNo AS ParentContactNo,
			ISNULL(SC.PermanentStreetOne,'')+' '+ISNULL(SC.PresentStreetTwo,'')+' '+ISNULL(SC.PresentStreetThree,'')+' '+ISNULL(SC.PresentCity,'')+' '+ISNULL(SC.PresentState,'')+' '+ISNULL(SC.PresentZipCode,'') AS PresentAddress,
			ISNULL(VM.RegistrationNumber,'') AS RegistrationNumber,
			ISNULL(BRM.DriverName,'') AS DriverName,
			ISNULL(BRM.DriverMobileNo,'') AS DriverMobileNo,
			ISNULL(BRM.ConductorName,'') AS ConductorName,
			ISNULL(BRM.ConductorMobileNo,'') AS ConductorMobileNo
	FROM 
			sTransport.tAssignRoute AR
			INNER JOIN sStudent.tAcademic A ON A.StudentId=AR.SourceId
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId 
			INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id AND SM.StatusId=1
			INNER JOIN sStudent.tStudentCommunication SC ON A.StudentId=SC.StudentId AND SM.StatusId=1
			INNER JOIN sSetup.tClassMaster CM ON A.ClassId=CM.Id
			INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=A.OrgId
			INNER JOIN sSetup.tDepartmentMaster DMast ON DMast.Id=CM.DepartmentId
			INNER JOIN sTransport.tBusRouteDestinationMaster BRDM ON BRDM.Id=AR.PickupDestinationId
			INNER JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BRDM.BusRouteId
			LEFT JOIN sTransport.tVehicleMaster VM ON VM.Id=BRM.VehicleId
			
	WHERE 
			--((AR.SourceId=@rAcademicId) OR (@rAcademicId=0))
			(BRM.OrgGroupBatchId=@rOrgGroupBatchId)
			AND BM.OrgGroupBatchId = @rOrgGroupBatchId
			AND ((OM.Id=@rOrgId) OR (@rOrgId=0))
			AND ((DMast.Id=@rDepartmentId) OR (@rDepartmentId=0))
			AND ((CM.Id=@rClassId) OR (@rClassId=0))
			AND ((DM.Id=@rDivisionId) OR (@rDivisionId=0))
			AND ((BRM.Id=@rRouteId) OR (@rRouteId=0))
			AND ((BRDM.Id=@rDestinationId) OR (@rDestinationId=0))
	END

GO

 
ALTER PROCEDURE [sCommon].[pEmployeeSearchGet]
	@rOrgId [int],
	@rEmployeeCode [nvarchar](20) = '',
	@rFirstName [nvarchar](30) = '',
	@rMobileNumber [nvarchar](20) = '',
	@rDOB [datetime] = '1/1/1900',
	--@rEmployeeLocationId int,
	@rLastName [nvarchar](30) = '',
	@rParticipantSourceTypeId int = 0,
	@rDepartmentId int = 0,
	@rEmployeeId bigint = 0,
	@rStatusId smallint
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
 --SET @rEmployeeCode = sCommon.fSearchStringGet(@rEmployeeCode)
IF(@rParticipantSourceTypeId = 95 OR @rParticipantSourceTypeId = 0 )
BEGIN
SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
SET @rLastName = sCommon.fSearchStringGet(@rLastName)
 SET @rMobileNumber = sCommon.fSearchStringGet(@rMobileNumber) 
 
 	 SELECT OrgID,
			Id,
			EmployeeId,
			Code,
			FirstName,
			MiddleName,
			LastName,
		    EmployeeName,	 			
			GenderId,
			ISNULL(DateOfBirth,'1900/01/01') AS DateOfBirth,
			ISNULL(DateOfJoining,'1900/01/01') AS DateOfJoining,
			'' AS Gender,
			--PermanentAddressId,
			--PresentAddressId AS CommunicationAddressId,
			--PresentContactId AS CommunicationContactId,
			StatusId,
			EM.IsObsolete,
			PhoneNoCompany,
			CompanyEmail,
			ExtensionNo,			
			DepartmentId,  
			DepartmentCode,  
			DepartmentName,
		    ISNULL (MobileNumber,0) AS MobileNumber,
			--EmployeeJobTitleId,
			--EmployeeTypeId,
			--EmployeeJobTitle,
			--EmployeeLocationId,
			ISNULL(DATEDIFF(yy,DateOfBirth,GETDATE()),0) AS Age,
			DesignationId,
			--ProcessTypeId,
			ParticipantSourceTypeId
			
 
	 FROM sHrm.vExtEmployeeMaster EM
	 
		 WHERE 
		    
		      ((EM.Code = @rEmployeeCode) OR (@rEmployeeCode = ''))
			  AND ((EM.FirstName Like  @rFirstName)  OR (ISNULL(@rFirstName,'') = ''))
			  AND ((EM.LastName Like  @rLastName)  OR (ISNULL(@rLastName,'') = ''))
			  AND ((EM.MobileNumber Like @rMobileNumber) OR(ISNULL(@rMobileNumber,'') = ''))				 
			  AND EM.IsObsolete = 0
			  --AND EM.EmployeeGroupId=1
			  AND EM.OrgId = @rOrgId 
			  --AND ((EM.EmployeeLocationId = @rEmployeeLocationId) OR (ISNULL(@rEmployeeLocationId,0) = 0) )		  			  
			  AND ((EM.DepartmentId = @rDepartmentId) OR (@rDepartmentId = 0) )	
			  AND ((EM.StatusId=@rStatusId) OR (@rStatusId=0))	 
		END
		
   ELSE IF(@rParticipantSourceTypeId = 1001) 
	  BEGIN
	  SELECT
		 OrgId,
	     DependentId,
	     EmployeeCode AS Code,
	     EmployeeId AS Id,
	     Name AS EmployeeName,
	     DateOfBirth,
	     AGE,
	     0 AS GenderId,
	     DepartmentId,
	     DepartmentName,
	     0 AS MobileNumber,
	     DesignationId,
	     '' AS FirstName,
	     '' AS LastName,
	     1001 AS ParticipantSourceTypeId
	     
	     FROM sHrm.vExtEmployeeFamilyDetail  Where 
	      (EmployeeCode Like @rEmployeeCode) AND DependentId = @rEmployeeId 
			   	AND OrgId = @rOrgId 
			 
			   	--AND EmployeeLocationId = @rEmployeeLocationId 
	      
	  END	
		
 
END

GO
 
ALTER PROCEDURE [sFee].[pRptFeePendingDetailListGet]
		@rReportId int=0,
		@rReceiptBookId int,
		@rAdmissionCategoryId int,
		@rClassId int,
		@rDivisionId int,
		@rUptoDate date,
		@rBatchId int,
		@rUserId bigint,
		@rBuilder1 nvarchar(max),
		@rBuilder2 nvarchar(max),
		@rIsDetail bit,
		@rOrgId int,
		@rOrgGroupBatchId int,
		@rStatusId int,
		@rCommonBatch nvarchar(100),
		@rDepartmentId int=0,
		@rFromRollNo int=0,
	    @rToRollNo int=0,
	    @rCode nvarchar(20)='',
	    @rIsFilter bit=0,
		@rIsHeader bit=0,		
		@rCreatedUserName nvarchar(20) = ''
	    
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN 	
	SELECT 
		@rBuilder1 AS Builder1,
		@rBuilder2 AS Builder2,
		@rIsDetail AS IsDetail,
		@rIsFilter As IsFilter,
		@rIsHeader As IsHeader,
		SA.AcademicId,
		SA.RollNo,
		SA.Name,
		SA.Code,
		SA.ClassId,
		SA.DivisionId,
		SA.ClassName,
		SA.DivisionName,
		SA.DivisionOrder,
		SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
		FS.Id AS FeeSetupId,
		
		ISNULL(FS.IsVariable,0)AS IsVariable,
		ISNULL(FS.Amount,0)AS Amount,
		ISNULL(FS.StartDate,'1900-01-01')AS StartDate,
		ISNULL(FS.EndDate,'1900-01-01')AS EndDate,
		ISNULL(FA.VariableAmount,0.00)AS FAAmount,
		ISNULL(FA.DiscountAmount,0.00)AS DiscountAmount,
		ISNULL(FA.AdditionalAmount,0.00)AS AdditionalAmount,
		ISNULL(R.PaidAmount,0) AS PaidAmount,
		ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00)AS PendingAmount,
		ISNULL(FA.Remark,'') AS Remark,
		FS.ReceiptBookId,
		FS.FeeName,
		SA.OrgId,
		O.Title AS OrgName,
		O.OrderIndex AS OrgOrderIndex,
		ISNULL(RBM.Title,'') AS ReceiptBookTitle,
		ISNULL(RBM.OrderIndex,0) AS ReceiptOrderIndex,
		SA.OrgGroupBatchId,
		SA.DepartmentId,
		SA.DepartmentName
  FROM 
  
			 sStudent.vStudentAcademic SA
		INNER JOIN 
		(
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName
			FROM sFee.tFeeSetup FS1
			INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.ClassId=FS1.ClassId AND FS1.BatchId = SA1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
				AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
			INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
			INNER JOIN sSetup.tBatchMaster BM ON FS1.BatchId = BM.Id AND BM.OrgGroupBatchId = @rOrgGroupBatchId
			WHERE 
			    SA1.OrgGroupBatchId = @rOrgGroupBatchId
				AND	((FS1.ClassId=@rClassId) OR (@rClassId=0))				
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=0)
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
				UNION ALL
				
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName
			FROM sFee.tFeeSetup FS1
		INNER JOIN sFee.tFeeOptional FO
			ON FO.FeeSetupId=FS1.Id
	    INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
		INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=FO.SourceId AND SA1.BatchId = FS1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
			AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
			AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
		INNER JOIN sSetup.tBatchMaster BM ON FS1.BatchId = BM.Id AND BM.OrgGroupBatchId = @rOrgGroupBatchId
		WHERE
		        SA1.OrgGroupBatchId = @rOrgGroupBatchId
				AND ((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=1)
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
		) FS ON FS.AcademicId=SA.AcademicId
		LEFT JOIN sFee.tFeeAllocation FA
			ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
		LEFT JOIN
		(
		    SELECT 
					ISNULL(SUM(RD.Amount),0) AS PaidAmount,RD.FeeSetupId ,R.SourceId
		    FROM
				sFee.tReceipt R 
				INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId = R.Id 
				INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=R.SourceId				
				AND (SA1.ClassId=@rClassId OR @rClassId=0) 
				AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND (SA1.DivisionId=@rDivisionId OR @rDivisionId=0)
				
			WHERE		
				(R.SourceTypeId = 2001)
				AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND (R.IsObsolete = 0) 
				AND ((SA1.OrgGroupBatchId = @rOrgGroupBatchId))
				AND (R.OrgGroupBatchId = @rOrgGroupBatchId)
				-- OR (@rOrgGroupBatchId = @rOrgGroupBatchId))
				AND ((R.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((R.CreatedUserId=@rUserId) OR (@rUserId=0))
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				GROUP BY FeeSetupId,R.SourceId
				
				
		
		) R ON R.FeeSetupId = FS.Id AND SA.AcademicId = R.SourceId
		 INNER JOIN sSetup.tOrganizationMaster O ON
		 SA.OrgId = O.Id
		 INNER JOIN sFee.tReceiptBookMaster RBM 
			ON FS.ReceiptBookId=RBM.Id
		
	
	WHERE	
	
			 SA.OrgGroupBatchId = @rOrgGroupBatchId
			AND ((SA.BatchId=@rBatchId) OR (@rBatchId=0)) 
			AND (SA.StatusId = @rStatusId OR @rStatusId=0)
			AND ((FS.StartDate <= @rUptoDate) OR (ISNULL(@rUptoDate,'') = ''))
			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(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00) -ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00) != 0)			
			Order by SA.RollNo
				
				
 
END

GO


ALTER PROCEDURE [sFee].[pRptFeePendingAllBatchGet]
		@rReportId int=0,
		@rReceiptBookId int=0,
		@rAdmissionCategoryId int=0,
		@rClassId int=0,
		@rDivisionId int=0,
		@rUptoDate date='',
		@rBatchId int='',
		@rUserId bigint=0,
		@rBuilder1 nvarchar(max)='',	
		@rBuilder2 nvarchar(max)='',
		@rIsDetail bit=0,
		@rOrgId int=0,
		@rOrgGroupBatchId int=0,
		@rStatusId int=0,
		@rCommonBatch nvarchar(100)='',
		@rDepartmentId int=0,
		@rFromRollNo int=0,
	    @rToRollNo int=0,
	    @rCode nvarchar(20)='',
	    @rIsFilter bit=0,
		@rIsHeader bit=0,		
		@rCreatedUserName nvarchar(20) = ''
	    
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN 	
	
	DECLARE @p as TempPendingFee
	
	INSERT INTO  @p 
	 
	SELECT 
		--@rBuilder1 AS Builder1,
		--@rBuilder2 AS Builder2,
		--@rIsDetail AS IsDetail,
		--@rIsFilter As IsFilter,
		--@rIsHeader As IsHeader,
		SA.AcademicId,
		SA.RollNo,
		SA.Name,
		SA.Code,
		SA.ClassId,
		SA.DivisionId,
		SA.ClassName,
		SA.DivisionName,
		SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
		FS.Id AS FeeSetupId,		
		ISNULL(FS.IsVariable,0)AS IsVariable,
		ISNULL(FS.Amount,0)AS Amount,
		ISNULL(FS.StartDate,'1900-01-01')AS StartDate,
		ISNULL(FS.EndDate,'1900-01-01')AS EndDate,
		ISNULL(FA.VariableAmount,0.00)AS FAAmount,
		ISNULL(FA.DiscountAmount,0.00)AS DiscountAmount,
		ISNULL(FA.AdditionalAmount,0.00)AS AdditionalAmount,
		ISNULL(R.PaidAmount,0) AS PaidAmount,
		ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00)AS PendingAmount,
		ISNULL(FA.Remark,'') AS Remark,
		FS.ReceiptBookId,
		FS.FeeName,
		FS.FeeCode,
		SA.OrgId,
		O.Title AS OrgName,
		O.OrderIndex AS OrgOrderIndex,
		ISNULL(RBM.Title,'') AS ReceiptBookTitle,
		ISNULL(RBM.OrderIndex,0) AS ReceiptOrderIndex,
		SA.BatchId,
		BM.Title AS BatchName
			
  FROM 
  
			 sStudent.vStudentAcademic SA
		INNER JOIN 
		(
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName,
				FM.Code AS FeeCode
			FROM sFee.tFeeSetup FS1
			INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.ClassId=FS1.ClassId AND FS1.BatchId = SA1.BatchId 
				AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId				
				AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				
			INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
			WHERE 
			    ((SA1.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))
				AND	((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=0)
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
				UNION ALL
				
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName,
				FM.Code AS FeeCode
			FROM sFee.tFeeSetup FS1
		INNER JOIN sFee.tFeeOptional FO
			ON FO.FeeSetupId=FS1.Id
	    INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
		INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=FO.SourceId AND SA1.BatchId = FS1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
			AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
			AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
		WHERE
		        ((SA1.OrgGroupBatchId = @rOrgGroupBatchId)  OR (@rOrgGroupBatchId=0))
				AND ((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=1)
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
		) FS ON FS.AcademicId=SA.AcademicId
		LEFT JOIN sFee.tFeeAllocation FA
			ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
		LEFT JOIN
		(
		    SELECT 
					ISNULL(SUM(RD.Amount),0) AS PaidAmount,RD.FeeSetupId ,R.SourceId
		    FROM
				sFee.tReceipt R 
				INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId = R.Id 
				INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=R.SourceId				
				AND (SA1.ClassId=@rClassId OR @rClassId=0) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND (SA1.DivisionId=@rDivisionId OR @rDivisionId=0)
				
			WHERE		
				(R.SourceTypeId = 2001)
				AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND (R.IsObsolete = 0) 
				AND ((SA1.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))
				AND ((R.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((R.CreatedUserId=@rUserId) OR (@rUserId=0))
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				GROUP BY FeeSetupId,R.SourceId
				
				
		
		) R ON R.FeeSetupId = FS.Id AND SA.AcademicId = R.SourceId
		 INNER JOIN sSetup.tOrganizationMaster O ON
		 SA.OrgId = O.Id
		 INNER JOIN sFee.tReceiptBookMaster RBM 
			ON FS.ReceiptBookId=RBM.Id
		 INNER JOIN sSetup.tBatchMaster BM 
			ON BM.Id=SA.BatchId	
		 
		
	WHERE	
			((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))
			AND ((SA.BatchId=@rBatchId) OR (@rBatchId=0)) 
			AND (SA.StatusId = @rStatusId OR @rStatusId=0)
			AND ((FS.StartDate <= @rUptoDate) OR (ISNULL(@rUptoDate,'') = ''))
			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(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00) -ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00) != 0)			
			Order by SA.RollNo

			SELECT
			OrgId,			 
			ReceiptBookId,
			AcademicId,
			DivisionId,
			ClassDivision,
			RollNo, 
			Name,
			Code,
			ReceiptBookTitle,
			OrgName,
			SUM(PendingAmount) AS PendingAmount, 
			[sFee].[fPendingFeeNamesGet](AcademicId,ReceiptBookId,	@p) AS PendingFeeName,
			ReceiptOrderIndex,
			BatchId,
			BatchName
			
			FROM @p 
			
			GROUP By ReceiptBookId,OrgId,AcademicId,DivisionId,BatchId,
			ClassDivision,RollNo, Name,Code,ReceiptBookTitle,OrgName,ReceiptOrderIndex,BatchName
			ORDER BY AcademicId
 
END

GO


ALTER PROCEDURE [sFee].[pRptFeePendingDetailWithScholarshipListGet]
		@rReportId int,
		@rReceiptBookId int,
		@rAdmissionCategoryId int,
		@rClassId int,
		@rDivisionId int,
		@rUptoDate date,
		@rBatchId int,
		@rUserId bigint,
		@rBuilder1 nvarchar(max),
		@rBuilder2 nvarchar(max),
		@rOrgId int,
		@rOrgGroupBatchId int,
		@rStatusId int,
		@rCommonBatch nvarchar(100),
		@rDepartmentId int,
		@rFromRollNo int,
	    @rToRollNo int,
	    @rCode nvarchar(20),
	    @rIsFilter bit,
		@rIsHeader bit,
		@rCreatedUserName nvarchar(100)=''
	    
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN 	

	DECLARE @p as TempPendingFee
	
	INSERT INTO  @p 
	 
	SELECT 
		SA.AcademicId,
		SA.RollNo,
		SA.Name,
		SA.Code,
		SA.ClassId,
		SA.DivisionId,
		SA.ClassName,
		SA.DivisionName,
		SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
		0 AS FeeSetupId,		
		0 AS IsVariable,
		0 AS Amount,
		'1900-01-01' AS StartDate,
		'1900-01-01' AS EndDate,
	    0.00 AS FAAmount,
		0.00 AS DiscountAmount,
		0.00 AS AdditionalAmount,
		0 AS PaidAmount,
		SUM(ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00)) AS PendingAmount,
		'' AS Remark,
		0 ReceiptBookId,
		'' AS FeeName,
		'' AS FeeCode,
		SA.OrgId,
		O.Title AS OrgName,
		O.OrderIndex AS OrgOrderIndex,
		'' AS ReceiptBookTitle,
		0 AS ReceiptOrderIndex,
		0 AS BatchId,
		'' AS BatchName
			
  FROM 
  
			 sStudent.vStudentAcademic SA
		INNER JOIN 
		(
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName,
				FM.Code AS FeeCode
			FROM sFee.tFeeSetup FS1
			INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.ClassId=FS1.ClassId AND FS1.BatchId = SA1.BatchId 
				AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
				AND ((SA1.ClassId=@rClassId) OR (@rClassId=0))  AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
			INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
			WHERE 
			     (SA1.OrgGroupBatchId = @rOrgGroupBatchId)
				AND	((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=0)
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
				UNION ALL
				
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName,
				FM.Code AS FeeCode
			FROM sFee.tFeeSetup FS1
		INNER JOIN sFee.tFeeOptional FO
			ON FO.FeeSetupId=FS1.Id
	    INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
		INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=FO.SourceId AND SA1.BatchId = FS1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
			AND ((SA1.ClassId=@rClassId) OR (@rClassId=0))   AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
			AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
		WHERE
		       (SA1.OrgGroupBatchId = @rOrgGroupBatchId)
				AND ((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=1)
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
		) FS ON FS.AcademicId=SA.AcademicId
		LEFT JOIN sFee.tFeeAllocation FA
			ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
		LEFT JOIN
		(
		    SELECT 
					ISNULL(SUM(RD.Amount),0) AS PaidAmount,RD.FeeSetupId ,R.SourceId
		    FROM
				sFee.tReceipt R 
				INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId = R.Id 
				INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=R.SourceId				
				AND (SA1.ClassId=@rClassId OR @rClassId=0) 
				AND (SA1.DivisionId=@rDivisionId OR @rDivisionId=0)
				
			WHERE		
				(R.SourceTypeId = 2001)
				AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND (R.IsObsolete = 0) 
				AND ((SA1.OrgGroupBatchId = @rOrgGroupBatchId)  )
				AND ((R.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((R.CreatedUserId=@rUserId) OR (@rUserId=0))
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				GROUP BY FeeSetupId,R.SourceId
				
				
		
		) R ON R.FeeSetupId = FS.Id AND SA.AcademicId = R.SourceId
		 INNER JOIN sSetup.tOrganizationMaster O ON
		 SA.OrgId = O.Id
		 INNER JOIN sFee.tReceiptBookMaster RBM 
			ON FS.ReceiptBookId=RBM.Id
	   
		 
		
	WHERE	
			(SA.OrgGroupBatchId = @rOrgGroupBatchId)
			AND ((SA.BatchId=@rBatchId) OR (@rBatchId=0)) 
			AND (SA.StatusId = @rStatusId OR @rStatusId=0)
			AND ((FS.StartDate <= @rUptoDate) OR (ISNULL(@rUptoDate,'') = ''))
			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(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00) -ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00) != 0)	
			GROUP BY 
			SA.AcademicId,
			SA.RollNo,
			SA.Name,
			SA.Code,
			SA.ClassId,
			SA.DivisionId,
			SA.ClassName,
			SA.DivisionName,
			SA.ClassName,
			SA.DivisionName,
			OrgId,
			O.Title,
			O.OrderIndex,
			BatchName
				
			Order by SA.RollNo

			SELECT
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			@rIsFilter AS IsFilter, 
			OrgId,			 
			p.AcademicId,
			DivisionId,
			ClassDivision,
			RollNo, 
			Name,
			Code,
			OrgName,
			ISNULL(SUM(PendingAmount),0) AS PendingAmount, 
			ISNULL(SUM(SS.ScholarshipPaid),0) AS ScholarshipPaid,
			ISNULL(SUM(SS.EligibleAmount),0) AS EligibleAmount,
			ISNULL(SS.ReferenceRemark,'')   AS ReferenceRemark
			--,
			--CASE ISNULL(SS.AcademicId,0) WHEN 0 THEN 0 ELSE 1 END AS IsScholarshipEligible		
			FROM @p p 
			
		LEFT JOIN				
		(SELECT  SE.AcademicId,Sum(PaidAmount) AS ScholarshipPaid,SUM(SE.EligibleAmount) AS EligibleAmount, SO.ReferenceRemark FROM 
		sScholarship.tScholarshipEligibleStudent SE
		LEFT JOIN sScholarship.tScholarshipStudentPaid SP on SE.AcademicId = SP.AcademicId
		INNER JOIN sStudent.vStudentAcademic SA1  ON SE.AcademicId = SA1.AcademicId 
		INNER JOIN sStudent.tStudentOther SO ON SA1.StudentId = SO.StudentId
		WHERE
		    (SA1.OrgGroupBatchId = @rOrgGroupBatchId)
		 	AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
		 	AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
		 	AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
		 	AND ((SA1.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = @rOrgGroupBatchId))
		 	AND ((SA1.ClassId=@rClassId) OR (@rClassId=0))
		 	 AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		 	GROUP BY SE.AcademicId,ReferenceRemark
		 ) SS ON p.AcademicId=SS.AcademicId
			GROUP By 
			OrgId,			
			p.AcademicId,
			--SS.AcademicId,
			DivisionId,
			ClassDivision,
			RollNo, 
			Name,
			Code,
			OrgName,
			ReferenceRemark
			ORDER BY AcademicId
END

GO

ALTER PROCEDURE [sFee].[pRptFeePendingWithFeeNameGet]
		@rReportId int=0,
		@rReceiptBookId int,
		@rAdmissionCategoryId int,
		@rClassId int,
		@rDivisionId int,
		@rUptoDate date,
		@rBatchId int,
		@rUserId bigint,
		@rBuilder1 nvarchar(max),
		@rBuilder2 nvarchar(max),
		@rIsDetail bit,
		@rOrgId int,
		@rOrgGroupBatchId int,
		@rStatusId int,
		@rCommonBatch nvarchar(100),
		@rDepartmentId int=0,
		@rFromRollNo int=0,
	    @rToRollNo int=0,
	    @rCode nvarchar(20)='',
	    @rIsFilter bit=0,
		@rIsHeader bit=0,		
		@rCreatedUserName nvarchar(20) = ''
	    
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN 	


--CREATE TABLE #TempStudent
--	(
--	AcademicId bigint,
--	ClassId int,
--	BatchId int,
--	a
--	)
--	SELECT AcademicId

 
	--CREATE TYPE  TempPendingFee AS TABLE
	--(
	--AcademicId bigint
	--,
	--RollNo int,
	--Name nvarchar(200),
	--Code nvarchar(20),
	--ClassId int,
	--DivisionId int,
	--ClassName nvarchar(100),
	--DivisionName nvarchar(100),
	--ClassDivision nvarchar(100),
	--FeeSetupId int,
	--IsVariable int,
	--Amount Decimal(12,2),
	--StartDate date,
	--EndDate date,
	--FAAmount Decimal(12,2),
	--DiscountAmount Decimal(12,2),
	--AdditionalAmount Decimal(12,2),
	--PaidAmount Decimal(12,2),
	--PendingAmount Decimal(12,2),
	--Remark nvarchar(200),
	--ReceiptBookId int,
	--FeeName nvarchar(100),
	--FeeCode nvarchar(100),
	--OrgId int,
	--OrgName nvarchar(200),
	--OrgOrderIndex int,
	--ReceiptBookTitle nvarchar(200),
	--ReceiptOrderIndex int	
	--)
	
	DECLARE @p as TempPendingFee
	
	INSERT INTO  @p 
	 
	SELECT 
		--@rBuilder1 AS Builder1,
		--@rBuilder2 AS Builder2,
		--@rIsDetail AS IsDetail,
		--@rIsFilter As IsFilter,
		--@rIsHeader As IsHeader,
		SA.AcademicId
		,
		SA.RollNo,
		SA.Name,
		SA.Code,
		SA.ClassId,
		SA.DivisionId,
		SA.ClassName,
		SA.DivisionName,
		SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
		FS.Id AS FeeSetupId,		
		ISNULL(FS.IsVariable,0)AS IsVariable,
		ISNULL(FS.Amount,0)AS Amount,
		ISNULL(FS.StartDate,'1900-01-01')AS StartDate,
		ISNULL(FS.EndDate,'1900-01-01')AS EndDate,
		ISNULL(FA.VariableAmount,0.00)AS FAAmount,
		ISNULL(FA.DiscountAmount,0.00)AS DiscountAmount,
		ISNULL(FA.AdditionalAmount,0.00)AS AdditionalAmount,
		ISNULL(R.PaidAmount,0) AS PaidAmount,
		ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00)AS PendingAmount,
		ISNULL(FA.Remark,'') AS Remark,
		FS.ReceiptBookId,
		FS.FeeName,
		FS.FeeCode,
		SA.OrgId,
		O.Title AS OrgName,
		O.OrderIndex AS OrgOrderIndex,
		ISNULL(RBM.Title,'') AS ReceiptBookTitle,
		ISNULL(RBM.OrderIndex,0) AS ReceiptOrderIndex,
		0 AS BatchId,
		'' AS BatchName
			
  FROM 
  
			 sStudent.vStudentAcademic SA
		INNER JOIN 
		(
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName,
				FM.Code AS FeeCode
			FROM sFee.tFeeSetup FS1
			INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.ClassId=FS1.ClassId AND FS1.BatchId = SA1.BatchId 
				AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
				AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
			INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
			WHERE 
			     (SA1.OrgGroupBatchId = @rOrgGroupBatchId)
				AND	((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=0)
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
				UNION ALL
				
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName,
				FM.Code AS FeeCode
			FROM sFee.tFeeSetup FS1
		INNER JOIN sFee.tFeeOptional FO
			ON FO.FeeSetupId=FS1.Id
	    INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
		INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=FO.SourceId AND SA1.BatchId = FS1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
			AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0)) 
			AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
		WHERE
		       (SA1.OrgGroupBatchId = @rOrgGroupBatchId)
				AND ((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=1)
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
		) FS ON FS.AcademicId=SA.AcademicId
		LEFT JOIN sFee.tFeeAllocation FA
			ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
		LEFT JOIN
		(
		    SELECT 
					ISNULL(SUM(RD.Amount),0) AS PaidAmount,RD.FeeSetupId ,R.SourceId
		    FROM
				sFee.tReceipt R 
				INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId = R.Id 
				INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=R.SourceId				
				AND (SA1.ClassId=@rClassId OR @rClassId=0) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))  
				AND (SA1.DivisionId=@rDivisionId OR @rDivisionId=0)
				
			WHERE		
				(R.SourceTypeId = 2001)
				AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND (R.IsObsolete = 0) 
				AND ((SA1.OrgGroupBatchId = @rOrgGroupBatchId)  )
				AND ((R.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((R.CreatedUserId=@rUserId) OR (@rUserId=0))
				AND ((SA1.BatchName = @rCommonBatch) OR (@rCommonBatch = ''))
				GROUP BY FeeSetupId,R.SourceId
				
				
		
		) R ON R.FeeSetupId = FS.Id AND SA.AcademicId = R.SourceId
		 INNER JOIN sSetup.tOrganizationMaster O ON
		 SA.OrgId = O.Id
		 INNER JOIN sFee.tReceiptBookMaster RBM 
			ON FS.ReceiptBookId=RBM.Id
		 
		
	WHERE	
			(SA.OrgGroupBatchId = @rOrgGroupBatchId)
			AND ((SA.BatchId=@rBatchId) OR (@rBatchId=0)) 
			AND (SA.StatusId = @rStatusId OR @rStatusId=0)
			AND ((FS.StartDate <= @rUptoDate) OR (ISNULL(@rUptoDate,'') = ''))
			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(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00) -ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00) != 0)			
			Order by SA.RollNo

			SELECT
			OrgId,			 
			ReceiptBookId,
			AcademicId,
			DivisionId,
			ClassDivision,RollNo, Name,Code,ReceiptBookTitle,OrgName,
			SUM(PendingAmount) AS PendingAmount, 
			[sFee].[fPendingFeeNamesGet](AcademicId,ReceiptBookId,	@p) AS PendingFeeName,
			ReceiptOrderIndex
			FROM @p 
			GROUP By ReceiptBookId,OrgId,AcademicId,DivisionId,
			ClassDivision,RollNo, Name,Code,
			ReceiptBookTitle,OrgName,ReceiptOrderIndex	
			ORDER BY AcademicId
 
END

GO

ALTER PROCEDURE [sStudent].[pAcademicAdd]
	 
	@rAcademicId bigint,
	@rStudentId bigint,
	@rClassId int,
	@rDivisionId int,
	@rRollNo int,
	@rOrgId int,
	@rBatchId int,
	--@rTransportModeId int = null,
 --   @rPickupBusRouteDestinationId int = null,
 --   @rDropBusRouteDestinationId int = null,
	@rCreatedUserId bigint,
	@rUpdatedUserId bigint
	
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	SET @rRollNo = ISNULL(@rRollNo,0)
	
	 BEGIN TRANSACTION	
 IF NOT EXISTS(Select Id from sStudent.tAcademic where StudentId=@rStudentId AND BatchId=@rBatchId)
BEGIN
	
	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sStudent.tAcademic', 0, 1, 1,@outNextNumber = @lNextNumber output



--IF ISNULL(@rRollNo,0) <> 0
--BEGIN
--IF EXISTS(Select Id from sStudent.tAcademic where RollNo = @rRollNo AND DivisionId = @rDivisionId )
--BEGIN
--SET @rRollNo = 0
--END
--END
--ELSE
--BEGIN
--  SELECT @rRollNo =  (ISNULL(max(RollNo),0) + 1) FROM  sStudent.tAcademic where DivisionId=@rDivisionId AND BatchId=@rBatchId
--END
 
	INSERT INTO sStudent.tAcademic
		(
			Id,
			StudentId,
			ClassId,
			DivisionId,
			RollNo,
			OrgId,
			BatchId,
			--TransportModeId,
			--PickupBusRouteDestinationId,
			--DropBusRouteDestinationId,
			CreatedUserId,
			CreatedDateTime
			,IsCurrent
		)
	VALUES
		(
			@lNextNumber,
			@rStudentId,
			@rClassId,
			@rDivisionId,
			@rRollNo,
			@rOrgId,
			@rBatchId,
			--@rTransportModeId,
			--@rPickupBusRouteDestinationId,
			--@rDropBusRouteDestinationId,
			@rCreatedUserId,
			sCommon.fGetDate()
			,1
		)		
        UPDATE sStudent.tAcademic SET IsCurrent = 0 WHERE StudentId = @rStudentId AND OrgId = @rOrgId AND BatchId <>  @rBatchId        
	END	 
	 ELSE
	 BEGIN
	 SET @lNextNumber = @rAcademicId
	 
	 
--if ISNULL(@rRollNo,0) <> 0
--BEGIN
--IF EXISTS(Select Id from sStudent.tAcademic where RollNo = @rRollNo AND DivisionId = @rDivisionId AND Id <> @rAcademicId )
--BEGIN
--SET @rRollNo = 0
--END
--END
	 
	    UPDATE sStudent.tAcademic
	    SET
			ClassId=@rClassId,
			DivisionId=@rDivisionId,
			RollNo=@rRollNo,
			OrgId=@rOrgId,
			BatchId=@rBatchId,
			--TransportModeId=@rTransportModeId,
			--PickupBusRouteDestinationId=@rPickupBusRouteDestinationId,
			--DropBusRouteDestinationId=@rDropBusRouteDestinationId,
			UpdatedUserId=@rUpdatedUserId,
			UpdatedDateTime=sCommon.fGetDate() 
			
	      WHERE 
	       --Id = @rAcademicId  AND 
	       StudentId=@rStudentId  AND 
	       BatchId = @rBatchId
	      
	 END  			
		COMMIT TRANSACTION
		SELECT @lNextNumber AS Result
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END

 


 

