USE [AcademyXSchool_127]
GO
/****** Object:  StoredProcedure [dbo].[StudentFeeDetail]    Script Date: 10/15/2020 11:25:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER Procedure [dbo].[StudentFeeDetail]
@AcademicyearId uniqueIdentifier,
@Schoolid uniqueIdentifier,
@StudentId uniqueIdentifier,
@ReceiptOId uniqueIdentifier
as

--Delete from StudentFeeDetails where AcademicYearId = @AcademicyearId and SchoolId = @Schoolid and StudentName=@StudentId
Delete from StudentFeeDetails where AcademicYearId = @AcademicyearId and SchoolId = @Schoolid and (@StudentId is null or StudentName=@StudentId)
-- Get the Fee from yearsettings 

Declare @ChallanType int 
--select @ChallanType=ChallanType from settings where SchoolId=@Schoolid and GCRecord is null
select @ChallanType=isnull(ChallanType,0) from YearlySettings with (nolock) where AcademicYearId = @AcademicyearId and  SchoolId=@Schoolid and GCRecord is null

DECLARE @CustomerId Int
select @CustomerId=CID from School where Oid=@Schoolid


DECLARE @Fee TABLE (FeeOid uniqueIdentifier)

Insert into @Fee
select TextBookFee from YearlySettings  with (nolock) where AcademicYearId=@AcademicyearId and SchoolId= @Schoolid 
and GCRecord is null and TextBookFee is not null
Union all
select NoteBookFee from YearlySettings  with (nolock) where AcademicYearId=@AcademicyearId and SchoolId= @Schoolid 
and GCRecord is null and NoteBookFee is not null
Union all
select UniformFee from YearlySettings  with (nolock) where AcademicYearId=@AcademicyearId and SchoolId= @Schoolid 
and GCRecord is null and UniformFee is not null
Union all
select StationaryFee from YearlySettings  with (nolock) where AcademicYearId=@AcademicyearId and SchoolId= @Schoolid 
and GCRecord is null and StationaryFee is not null
Union all
select TransportFee from YearlySettings  with (nolock) where AcademicYearId=@AcademicyearId and SchoolId= @Schoolid 
and GCRecord is null and TransportFee is not null
Union all
select TransportDeposit from YearlySettings  with (nolock) where AcademicYearId=@AcademicyearId and SchoolId= @Schoolid 
and GCRecord is null and TransportDeposit is not null
Union All
select HostelFee from YearlySettings  with (nolock) where AcademicYearId=@AcademicyearId and SchoolId= @Schoolid 
and GCRecord is null and HostelFee is not null
Union All
select MessBillFee from YearlySettings  with (nolock) where AcademicYearId=@AcademicyearId and SchoolId= @Schoolid 
and GCRecord is null and MessBillFee is not null


--select * from @Fee

insert into StudentFeeDetails(Oid,AcademicYearId,SchoolId,StudentName,FeeName,FeeIndex,FeeType,FeeAmount,busStop) 
select NEWID(),@AcademicyearId,@Schoolid,StudentName,FeeName,FeeIndex,FeeType,FeeAmount,busStop from 
(
	select  distinct StudentName,FeeName,FeeIndex,FeeType,FeeAmount,busStop
	from
	(
	-- Optional Strcture
	-- SectionGroup =0 (1-10th)
	select a.Oid as StudentName,b.FeeName,b.FeeIndex,b.FeeType,'OptionalFeeStrcture' as FType,Amount as FeeAmount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join FeeStructure b  with (nolock) on b.ClassName=a.ClassName and a.AcademicYearId=b.AcademicYearId 
						and a.SchoolId=b.SchoolId 
						
	join ClassMaster c  with (nolock) on c.Oid=a.ClassName 
	join FeeMaster d  with (nolock) on	d.Oid=b.FeeName 
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and b.GCRecord is null
	and a.GCRecord is null 
	and b.OptionalSubject=a.OptionalSubjectName 
	and c.IsSectionGroupAvailable=0
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid
	and (select shortdesc from constantinfo  with (nolock) where oid=d.Applicablefor )<>(select case when Academicyearjoined=@AcademicyearId then 'O' else 'N' end 
																		  from StudentMaster  with (nolock) where Oid=a.StudentName)
																		  
	and (select shortdesc from constantinfo  with (nolock) where oid=d.GenderGroup)<>(	select case when r.ShortDesc='M' then 'G' else 'B' end 
																		from StudentMaster p  with (nolock) 
																		join StudentApplication q  with (nolock) on q.Oid=p.ApplicationNo
																		join ConstantInfo r   with (nolock) on q.Gender=r.Oid
																		where p.Oid=a.StudentName)
	and d.Oid not in (select * from @Fee ) 
	and (@StudentId is null or a.oid=@StudentId)
	and d.FeeUnder not in (select Oid from ConstantInfo  with (nolock) where constantName=52 and ShortDesc='H')
	
	---- SectionGroup=1 (11th, 12th)
	Union All 


	select a.Oid,b.FeeName,b.FeeIndex,b.FeeType,'OptionalFeeStrcture' as FType,b.Amount as FeeAmount	,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join FeeStructure b  with (nolock) on b.ClassName=a.ClassName and a.AcademicYearId=b.AcademicYearId 
						and a.SchoolId=b.SchoolId 
						and a.SectionGroup=b.SectionGroup
	join ClassMaster c  with (nolock) on c.Oid=a.ClassName 
	join FeeMaster d  with (nolock) on	d.Oid=b.FeeName 
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and b.GCRecord is null
	and a.GCRecord is null 
	and b.OptionalSubject=a.OptionalSubjectName
	and c.IsSectionGroupAvailable=1
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid
	and (select shortdesc from constantinfo  with (nolock) where oid=d.Applicablefor )<>(select case when Academicyearjoined=@AcademicyearId then 'O' else 'N' end 
																		  from StudentMaster  with (nolock) where Oid=a.StudentName)
																		  
	and (select shortdesc from constantinfo  with (nolock) where oid=d.GenderGroup)<>(	select case when r.ShortDesc='M' then 'G' else 'B' end 
																		from StudentMaster p  with (nolock) 
																		join StudentApplication q  with (nolock) on q.Oid=p.ApplicationNo
																		join ConstantInfo r   with (nolock) on q.Gender=r.Oid
																		where p.Oid=a.StudentName)
	and d.Oid not in (select * from @Fee )
	and (@StudentId is null or a.oid=@StudentId)
	and d.FeeUnder  not in (select Oid from ConstantInfo  with (nolock) where constantName=52 and ShortDesc='H')
		 
	Union All 

	-- FeeStrcture
	-- SectionGroup =0 (1-10th)
	select a.Oid as StudentName,b.FeeName,b.FeeIndex,b.FeeType,'FeeStrcture' as FType,b.Amount as FeeAmount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join FeeStructure b  with (nolock) on b.ClassName=a.ClassName and a.AcademicYearId=b.AcademicYearId 
						and a.SchoolId=b.SchoolId 
						
	join ClassMaster c  with (nolock) on c.Oid=a.ClassName 
	join FeeMaster d  with (nolock) on	d.Oid=b.FeeName 
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and b.GCRecord is null
	and a.GCRecord is null 
	and b.OptionalSubject is null 
	and c.IsSectionGroupAvailable=0
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid
	and (select shortdesc from constantinfo  with (nolock) where oid=d.Applicablefor )<>(select case when Academicyearjoined=@AcademicyearId then 'O' else 'N' end 
																		  from StudentMaster  with (nolock) where Oid=a.StudentName)
																		  
	and (select shortdesc from constantinfo  with (nolock) where oid=d.GenderGroup)<>(	select case when r.ShortDesc='M' then 'G' else 'B' end 
																		from StudentMaster p  with (nolock) 
																		join StudentApplication q  with (nolock) on q.Oid=p.ApplicationNo
																		join ConstantInfo r   with (nolock) on q.Gender=r.Oid
																		where p.Oid=a.StudentName)
	and d.Oid not in (select * from @Fee ) 
	and (@StudentId is null or a.oid=@StudentId)
	and d.FeeUnder  not in (select Oid from ConstantInfo  with (nolock) where constantName=52 and ShortDesc='H')
	
	
	---- SectionGroup=1 (11th, 12th)
	Union All 


	select a.Oid,b.FeeName,b.FeeIndex,b.FeeType,'FeeStrcture' as FType,b.Amount as FeeAmount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join FeeStructure b  with (nolock) on b.ClassName=a.ClassName and a.AcademicYearId=b.AcademicYearId 
						and a.SchoolId=b.SchoolId 
						and a.SectionGroup=b.SectionGroup
	join ClassMaster c  with (nolock) on c.Oid=a.ClassName 
	join FeeMaster d  with (nolock) on	d.Oid=b.FeeName 
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and b.GCRecord is null
	and a.GCRecord is null 
	and b.OptionalSubject is null 
	and c.IsSectionGroupAvailable=1
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid
	and (select shortdesc from constantinfo  with (nolock) where oid=d.Applicablefor )<>(select case when Academicyearjoined=@AcademicyearId then 'O' else 'N' end 
																		  from StudentMaster  with (nolock) where Oid=a.StudentName)
																		  
	and (select shortdesc from constantinfo  with (nolock) where oid=d.GenderGroup)<>(	select case when r.ShortDesc='M' then 'G' else 'B' end 
																		from StudentMaster p  with (nolock) 
																		join StudentApplication q  with (nolock) on q.Oid=p.ApplicationNo
																		join ConstantInfo r  with (nolock)  on q.Gender=r.Oid
	and d.Oid not in (select * from @Fee ) 																	where p.Oid=a.StudentName)
	and (@StudentId is null or a.oid=@StudentId)
	and d.FeeUnder  not in (select Oid from ConstantInfo  with (nolock) where constantName=52 and ShortDesc='H')
	
	Union All
	select a.Oid as StudentName,b.FeeName,b.FeeIndex,b.FeeType,'ExtraFee' as FType,b.FeeAmount as FeeAmount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join ExtraFees b  with (nolock) on b.StudentName=a.oid 
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and b.GCRecord is null
	and a.GCRecord is null 
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid	
	and (@StudentId is null or a.oid=@StudentId)

	
-- Transport Fee

	Union All
	--Transport Fee - Term  - Stoped 
	select a.Oid as StudentName,d.TransportFee as FeeName
	,f.Oid as FeeIndex,e.FeeType
	,FType='Transport Fee '  
	,(case when c.PickupPoint=c.DropPoint then BothRate
		   when Not PickupPoint IS NULL  then PickupRate
		   else DropRate
		   end ) as FeeAmount
	,busStop=(	select top 1 description from erpmaster 
				where oid= (case when c.PickupPoint=c.DropPoint then PickupPoint
				when Not PickupPoint IS NULL  then PickupPoint
				else DropPoint end )	)
				
	from fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join TransportEntryMaster b with (nolock) on b.StudentName=a.oid 
	join TransportEntryDetail c with (nolock) on c.StudentName=b.oid 
	join YearlySettings d with (nolock) on d.AcademicYearId=a.AcademicYearId and d.SchoolId=a.SchoolId
	join TransportFeeStructure e with (nolock) on e.AcademicYearId=a.AcademicYearId and e.SchoolId=a.SchoolId
	join ConstantInfo f with (nolock) on  f.constantName=121 and  f.constantName=e.FeeType 
	join TransportFeeStructureDetail e1 with (nolock) on e1.TransportFeeStructure=e.Oid and e1.FeeIndex=f.oid
										and e1.BusStopName=(case when c.PickupPoint=c.DropPoint then PickupPoint
															when Not PickupPoint IS NULL  then PickupPoint
															else DropPoint
															end )					
	where a.Status in (select oid from ConstantInfo where constantName=34)
	---and a.Transport=1 
	and a.GCRecord is null
	and b.GCRecord is null 
	and c.GCRecord is null 
	and d.GCRecord is null 
	and e.GCRecord is null 
	and f.GCRecord is null 
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid
	and f.IndexValue between ( select indexvalue from ConstantInfo where oid=c.feeindexfrom ) and (select indexvalue from ConstantInfo where oid= c.feeindexto )
	and c.Status in (select oid from ConstantInfo where constantName=120 and ShortDesc<>'A')	
	
	
	--Transport Fee - Term  - Active 
	
	Union All
	
	select a.Oid as StudentName,d.TransportFee as FeeName
	,f.Oid as FeeIndex,e.FeeType
	,FType='Transport Fee  ' 
	,(case when c.PickupPoint=c.DropPoint then BothRate
		   when Not PickupPoint IS NULL  then PickupRate
		   else DropRate
		   end ) as FeeAmount
	,busStop=(	select top 1 description from erpmaster 
				where oid= (case when c.PickupPoint=c.DropPoint then PickupPoint
				when Not PickupPoint IS NULL  then PickupPoint
				else DropPoint end )	)		   
	from fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join TransportEntryMaster b with (nolock) on b.StudentName=a.oid 
	join TransportEntryDetail c with (nolock) on c.StudentName=b.oid 
	join YearlySettings d with (nolock) on d.AcademicYearId=a.AcademicYearId and d.SchoolId=a.SchoolId
	join TransportFeeStructure e with (nolock) on e.AcademicYearId=a.AcademicYearId and e.SchoolId=a.SchoolId
	join ConstantInfo f with (nolock) on f.constantName=121 and  f.constantName=e.FeeType 
	join TransportFeeStructureDetail e1 with (nolock) on e1.TransportFeeStructure=e.Oid and e1.FeeIndex=f.oid
										and e1.BusStopName=(case when c.PickupPoint=c.DropPoint then PickupPoint
															when Not PickupPoint IS NULL  then PickupPoint
															else DropPoint
															end )					
	where a.Status in (select oid from ConstantInfo where constantName=34)
	--and a.Transport=1 
	and a.GCRecord is null
	and b.GCRecord is null 
	and c.GCRecord is null 
	and d.GCRecord is null 
	and e.GCRecord is null 
	and f.GCRecord is null 
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid
	and f.IndexValue >= isnull(( select indexvalue from ConstantInfo where oid=c.feeindexfrom ) ,1)
	and c.Status in (select oid from ConstantInfo where constantName=120 and ShortDesc='A')	
	
	--TranportFee  - Month - Stoped
	Union All
	select distinct a.Oid as StudentName,d.TransportFee as FeeName
	,f.Oid as FeeIndex,e.FeeType,'Transport Fee' as FType
	,(case when c.PickupPoint=c.DropPoint then BothRate
		   when Not PickupPoint IS NULL  then PickupRate
		   else DropRate
		   end ) as FeeAmount
	,busStop=(	select description from erpmaster 
				where oid= (case when c.PickupPoint=c.DropPoint then PickupPoint
				when Not PickupPoint IS NULL  then PickupPoint
				else DropPoint end )	)
	from fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join TransportEntryMaster b with (nolock) on b.StudentName=a.oid 
	join TransportEntryDetail c with (nolock) on c.StudentName=b.oid 
	join YearlySettings d with (nolock) on d.AcademicYearId=a.AcademicYearId and d.SchoolId=a.SchoolId
	join TransportFeeStructure e with (nolock) on e.AcademicYearId=a.AcademicYearId and e.SchoolId=a.SchoolId
	join ConstantInfo f with (nolock) on f.constantName=50 and  f.constantName=e.FeeType
	join TransportFeeStructureDetail e1 with (nolock) on e1.TransportFeeStructure=e.Oid and e1.FeeIndex=f.oid
										and e1.BusStopName=(case when c.PickupPoint=c.DropPoint then PickupPoint
															when Not PickupPoint IS NULL   then PickupPoint
															else DropPoint
															end )			
	where a.Status in (select oid from ConstantInfo where constantName=34)
	and a.Transport=1
	and a.GCRecord is null
	and b.GCRecord is null 
	and c.GCRecord is null 
	and d.GCRecord is null 
	and e.GCRecord is null 
	and f.GCRecord is null 
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid	
	and f.IndexValue between ( select indexvalue from ConstantInfo where oid=c.feeindexfrom ) and (select indexvalue from ConstantInfo where oid= c.feeindexto )
	and c.Status in (select oid from ConstantInfo where constantName=120 and ShortDesc<>'A')


	--TranportFee  - Month - Active
	Union All
	select distinct a.Oid as StudentName,d.TransportFee as FeeName
	,f.Oid as FeeIndex,e.FeeType,'Transport Fee' as FType
	,(case when c.PickupPoint=c.DropPoint then BothRate
		   when Not PickupPoint IS NULL  then PickupRate
		   else DropRate
		   end ) as FeeAmount
	,busStop=(	select description from erpmaster 
				where oid= (case when c.PickupPoint=c.DropPoint then PickupPoint
				when Not PickupPoint IS NULL  then PickupPoint
				else DropPoint end )	)
	from fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock)  
	join TransportEntryMaster b with (nolock) on b.StudentName=a.oid 
	join TransportEntryDetail c with (nolock) on c.StudentName=b.oid 
	join YearlySettings d with (nolock) on d.AcademicYearId=a.AcademicYearId and d.SchoolId=a.SchoolId
	join TransportFeeStructure e with (nolock) on e.AcademicYearId=a.AcademicYearId and e.SchoolId=a.SchoolId
	join ConstantInfo f with (nolock) on f.constantName=50 and  f.constantName=e.FeeType
	join TransportFeeStructureDetail e1 with (nolock) on e1.TransportFeeStructure=e.Oid and e1.FeeIndex=f.oid
										and e1.BusStopName=(case when c.PickupPoint=c.DropPoint then PickupPoint
															when Not PickupPoint IS NULL   then PickupPoint
															else DropPoint
															end )			
	where a.Status in (select oid from ConstantInfo where constantName=34)
	and a.Transport=1
	and a.GCRecord is null
	and b.GCRecord is null 
	and c.GCRecord is null 
	and d.GCRecord is null 
	and e.GCRecord is null 
	and f.GCRecord is null 
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid	
	and f.IndexValue >= isnull(( select indexvalue from ConstantInfo where oid=c.feeindexfrom ) ,1)
	and c.Status in (select oid from ConstantInfo where constantName=120 and ShortDesc='A')

	-- Trasport Deposit
	Union All
	select a.Oid as StudentName
	,FeeName=d.TransportDeposit
	,FeeIndex=(select Oid from ConstantInfo  with (nolock) where constantName=121 and Description='Term1')
	,FeeType=121,'Transport Deposit Fee' as FType,c.Deposit as FeeAmount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join TransportEntryMaster b  with (nolock) on b.StudentName=a.oid 
	join TransportEntryDetail c  with (nolock) on c.StudentName=b.oid 
	join YearlySettings d  with (nolock) on d.AcademicYearId=a.AcademicYearId and d.SchoolId=a.SchoolId
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and b.GCRecord is null
	and a.GCRecord is null 
	and c.GCRecord is null 
	and c.Deposit>0
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid	
	and (@StudentId is null or a.oid=@StudentId)

	-- Inventory
	Union All
	select a.Oid as StudentName
	,FeeName=(select case	when f.ItemType=0 then d.TextBookFee
							when f.ItemType=1 then d.NoteBookFee
							when f.ItemType=2 then d.UniformFee
							when f.ItemType=3 then d.StationaryFee
			end )
	,FeeIndex=(select Oid from ConstantInfo  with (nolock) where constantName=121 and Description='Term1')
	,FeeType=121,'Inventory' as FType,(c.Quantity*c.Rate) as FeeAmount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join StudentItemMaster b  with (nolock) on b.StudentName=a.oid 
	join StudentItemDetail c  with (nolock) on c.StudentItemMaster=b.Oid
	join YearlySettings d  with (nolock) on d.AcademicYearId=a.AcademicYearId and d.SchoolId=a.SchoolId
	join ClassItems e  with (nolock) on e.Oid=c.ItemName
	join ItemMaster f  with (nolock) on f.Oid=e.ItemName
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and b.GCRecord is null
	and a.GCRecord is null 
	and c.GCRecord is null
	and d.GCRecord is null
	and f.GCRecord is null
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid	
	and (@StudentId is null or a.oid=@StudentId)
	
	-- Areare Fees
	Union All
	select a.Oid as StudentName
	,FeeName=b.ArrearFeeName 
	,FeeIndex=(select Oid from ConstantInfo  with (nolock) where constantName=121 and Description='Term1')
	,FeeType=121,'Areare' as FType,b.ArrearAmount as FeeAmount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join FeeArrearDetail b  with (nolock) on b.StudentName=a.oid 
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and b.GCRecord is null
	and a.GCRecord is null 
	and a.AcademicYearId=@AcademicyearId
	and b.AcademicYearId=a.AcademicYearId
	and a.SchoolId=@Schoolid	
	and (@StudentId is null or a.oid=@StudentId)

	-- Hostel (with Hostel Fee)
	Union All
	select a.Oid as StudentName
	,FeeName=c.FeeName
	,FeeIndex=c.FeeIndex
	,FeeType=b.FeeType,'Hostel' as FType,c.FeeAmount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 
	join Hostel b  with (nolock) on b.StudentName=a.oid 
	join HostelFees c  with (nolock) on c.StudentName=b.Oid
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	and a.Hostel=1
	and b.GCRecord is null
	and a.GCRecord is null 
	and c.GCRecord is null 
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid
	and (@StudentId is null or a.oid=@StudentId)	
	
	-- Hostel (without  Hostel Fee/ with feestructure )
	Union All
	select a.Oid as StudentName
	,FeeName=b.FeeName
	,FeeIndex=b.FeeIndex
	,FeeType=b.FeeType,'Hostel Fee' as FType,b.Amount,busStop=''
	from   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   a  --with (nolock) 	
	join FeeStructure b  with (nolock) on b.ClassName=a.ClassName and a.AcademicYearId=b.AcademicYearId 
						and a.SchoolId=b.SchoolId 
	join Hostel c  with (nolock) on c.StudentName=a.oid 
	join hosteldetail d with (nolock) on d.MasterId=c.Oid and c.status=d.status 
	join FeeMaster e  with (nolock) on	e.Oid=b.FeeName and e.SchoolId=a.SchoolId 
	join ConstantInfo f with (nolock) on f.constantName=d.feetype  and f.oid=b.feeindex
	where a.Status in (select oid from ConstantInfo  with (nolock) where constantName=34)
	--and a.Hostel=1
	and b.GCRecord is null
	and a.GCRecord is null 
	and c.GCRecord is null 
	and a.AcademicYearId=@AcademicyearId
	and a.SchoolId=@Schoolid
	and e.Oid not in (select * from @Fee ) 
	and (@StudentId is null or a.oid=@StudentId)
	and e.FeeUnder in (select Oid from ConstantInfo  with (nolock) where constantName=52 and ShortDesc='H')
	and f.IndexValue >= isnull(( select indexvalue from ConstantInfo where oid=d.feeindexfrom ) ,1)
	and ( f.IndexValue <= isnull(( select indexvalue from ConstantInfo where oid=d.FeeIndexTo ) ,1) or d.FeeIndexTo is null)
	and c.status in(select Oid from ConstantInfo  with (nolock) where constantName=34 and ShortDesc not in('R')) -- R- cancel 
	
	)x	
	
	except 
	select  StudentName,FeeName,FeeIndex,FeeType,FeeAmount,BusStop from StudentFeeDetails with (nolock) 
  )y
  where (@StudentId is null or StudentName=@StudentId)

	
Update StudentFeeDetails set CollectOn = e.CollectOn
from StudentFeeDetails a with (nolock) 
join FeeMaster e  with (nolock) on e.Oid=a.FeeName
where  a.AcademicYearId = @AcademicyearId and a.SchoolId = @Schoolid
/** NEW ADD **/
and (a.StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

	
--Update Exclustion	
update StudentFeeDetails set ExclusionAmount = (Select Sum(ExclusionAmount) 
												from StudentFeeExclusion  with (nolock) 
												where cancel = 0 
												and StudentName = a.StudentName 
												and  FeeIndex= a.FeeIndex 
												and FeeName= a.FeeName 
												and AcademicYearId = a.AcademicYearId 
												and SchoolId = a.SchoolId
												and GCRecord is null
												)  
from StudentFeeDetails a  with (nolock) 
where AcademicYearId = @AcademicyearId 
and SchoolId = @Schoolid
and (@StudentId is null or StudentName=@StudentId)


update StudentFeeDetails set ExclusionAmount = 0 where ExclusionAmount is null  
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

--Update RefundAmount
update StudentFeeDetails set RefundAmount = (Select Sum(a.RefundAmount) 
											from FeeRefundDetail a  with (nolock) 
											join FeeRefund b  with (nolock) on a.MasterId = b.Oid 
											join FeeReceiptDetail c  with (nolock) on a.FeeReceiptNo = c.Oid  
											where b.cancel = 0 
											and  b.FeeWiseBreakup=1  
											and b.StudentName = x.StudentName  
											and  c.FeeIndex= x.FeeIndex  
											and c.FeeName= x.FeeName  
											and b.AcademicYearId = x.AcademicYearId  
											and b.SchoolId = x.SchoolId
											and a.GCRecord is null 
											and b.GCRecord is null 
											--and c.GCRecord is null 
											)   
from StudentFeeDetails x with (nolock) 
where AcademicYearId = @AcademicyearId 
and SchoolId = @Schoolid
and (@StudentId is null or StudentName=@StudentId)


update StudentFeeDetails set RefundAmount = 0 where RefundAmount is null  
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/ 
 
--  Update PaidAmount
update StudentFeeDetails set PaidAmount = (	select Sum(Payable) 
											from FeeReceiptDetail a  with (nolock) 
											join FeeReceipt b  with (nolock) on a.ReceiptNo = b.Oid  
											where b.AcademicYearId = x.AcademicYearId 
											and b.SchoolId = x.SchoolId 
											and b.Cancel =0
											and a.FeeIndex = x.FeeIndex 
											And a.FeeName = x.FeeName 
											And b.StudentName = x.StudentName 
											--And a.gcrecord is null
											and b.gcrecord is null
											--and (b.oid<>@ReceiptOId or @ReceiptOId=null)
											 ) 
from StudentFeeDetails x  with (nolock) 
where AcademicYearId = @AcademicyearId 
and SchoolId = @Schoolid
and (@StudentId is null or StudentName=@StudentId)

--update StudentFeeDetails set PaidAmount = 0 where PaidAmount is null   

--  Update Challan PaidAmount
update StudentFeeDetails set CPaidAmount = (select isnull(Sum(Payable) ,0)
											from Challandetail a  with (nolock) 
											join Challan b  with (nolock) on a.ChallanNo = b.Oid  
											where b.AcademicYearId = x.AcademicYearId 
											and b.SchoolId = x.SchoolId 
											and b.Cancel =0
											and a.FeeIndex = x.FeeIndex 
											And a.FeeName = x.FeeName 
											And b.StudentName = x.StudentName 
											And a.gcrecord is null
											and b.gcrecord is null 
											) 
from StudentFeeDetails x  with (nolock) 
where AcademicYearId = @AcademicyearId 
and SchoolId = @Schoolid
and (@StudentId is null or StudentName=@StudentId)

--update StudentFeeDetails set CPaidAmount = 0 where CPaidAmount is null   


--Update ConcessionAmount
Update StudentFeeDetails set ConcessionAmt=(case d.ShortDesc when 'P' then (e.FeeAmount * b.ConcessionPercentage/100)  else b.ConcessionAmount end ) 
from StudentFeeDetails e  with (nolock) 
JOIN StudentFeeConcession a  with (nolock) on  e.StudentName=a.StudentName 
join ConcessionSetting b  with (nolock) on b.Oid=a.ConcessionName 
						and e.FeeName=b.FeeName 
						and e.FeeIndex=b.FeeIndex  
						and e.FeeType=b.FeeType 
join  ConcessionMaster c  with (nolock) on c.Oid=b.ConcessionName 
						and c.AcademicYearId=e.AcademicYearId 
						and c.SchoolId=e.SchoolId 
join ConstantInfo d  with (nolock) on d.Oid=c.ConcessionType 
where Cancel=0 
and e.AcademicYearId = @AcademicyearId 
and e.SchoolId = @Schoolid
and (@StudentId is null or E.StudentName=@StudentId)
and a.GCRecord is null 
and b.GCRecord is null 
and c.GCRecord is null 
and d.GCRecord is null 

 

update StudentFeeDetails set ConcessionAmt = 0 where ConcessionAmt is null 
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

--Update NetAmount
update StudentFeeDetails set NetAmount = (FeeAmount - (ConcessionAmt+ExclusionAmount))  
/** NEW ADD **/
where (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/
 
update StudentFeeDetails set NetAmount = 0 where ExclusionAmount is null   
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

--Update BalanceAmount
update StudentFeeDetails set PaidAmount=0 where PaidAmount is null
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

update StudentFeeDetails set cPaidAmount=0 where CPaidAmount is null
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/


update StudentFeeDetails set BalanceAmount = (NetAmount - (PaidAmount-RefundAmount)) where @ChallanType<>2
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

update StudentFeeDetails set BalanceAmount = (CPaidAmount - (PaidAmount-RefundAmount)) where @ChallanType = 2
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/


--update StudentFeeDetails set CBalanceAmount = (NetAmount - CPaidAmount) where @ChallanType=2
update StudentFeeDetails set CBalanceAmount = (NetAmount - CPaidAmount) where @ChallanType=2
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/
  

update StudentFeeDetails set BalanceAmount = 0 where RefundAmount is null
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

update StudentFeeDetails set CBalanceAmount=0 where CBalanceAmount is null
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

--update StudentFeeDetails set CBalanceAmount = 0 where RefundAmount is null   

Update StudentFeeDetails set ClosingAmount=BalanceAmount
from StudentFeeDetails a  with (nolock) join   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   b  --with (nolock) 
on b.Oid=a.StudentName join ConstantInfo c  with (nolock) on c.Oid=b.Status
where ShortDesc Not in ('A','N','T')
/** NEW ADD **/
and a.AcademicYearId = @AcademicyearId and a.SchoolId = @Schoolid
and (a.StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/


--Update BalanceAmount  Cancel/Completed/Discontinue
Update StudentFeeDetails set BalanceAmount=0 
from StudentFeeDetails a  with (nolock) join   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   b  --with (nolock)
 on b.Oid=a.StudentName join ConstantInfo c  with (nolock) on c.Oid=b.Status
where ShortDesc Not in ('A','N','T')
/** NEW ADD **/
and a.AcademicYearId = @AcademicyearId and a.SchoolId = @Schoolid
and (a.StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

Delete from StudentFeeDetails where FeeAmount = 0 and AcademicYearId = @AcademicyearId and SchoolId = @Schoolid
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

--RTE Student Remove
if @CustomerId=140 -- Milton

	Delete from studentfeedetails Where studentname in ( select oid from studentsection where RTE=1  And AcademicYearId = @AcademicyearId And SchoolId = @Schoolid )
	and PaidAmount=0 and CPaidAmount=0 
	And AcademicYearId = @AcademicyearId 
	And SchoolId = @Schoolid
	and FeeName not in (select TransportFee from YearlySettings where AcademicYearId=@AcademicyearId and SchoolId=@Schoolid)
else	
	Delete 
	from studentfeedetails Where studentname in ( select oid from studentsection where RTE=1  And AcademicYearId = @AcademicyearId And SchoolId = @Schoolid )
	and PaidAmount=0 and CPaidAmount=0 
	And AcademicYearId = @AcademicyearId 
	And SchoolId = @Schoolid
	and FeeName not in (select oid from feemaster where isnull(RTE,0)=1 and SchoolId=@Schoolid)



----Delete HostelFee, if Hostel is untick in   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   and also Hostel Entry not found
--delete from StudentFeeDetails 
--where Oid in ( select a.Oid
--    from StudentFeeDetails a with (nolock) 
--    join   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   b  --with (nolock)
--     on b.Oid=a.StudentName
--    join FeeMaster e  with (nolock) on e.Oid=a.FeeName
--    where b.Hostel=0
--    and e.FeeUnder=(select Oid from ConstantInfo  with (nolock) where constantName=52 and ShortDesc='H')
--    and b.oid not in (select StudentName from Hostel  with (nolock) where GCRecord is null)
--	--and a.paidmount=0
--     )
     
----Delete HostelFee, if Hostel is untick in   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   and also Hostel Entry found with Cancel/discontinue
--delete from StudentFeeDetails 
--where Oid in ( select a.Oid
--    from StudentFeeDetails a with (nolock) 
--    join   fnStudentSection(@AcademicyearId,@Schoolid,@studentid)   b -- with (nolock) 
--    on b.Oid=a.StudentName
--    join FeeMaster e  with (nolock) on e.Oid=a.FeeName
--    where b.Hostel=0
--    and e.FeeUnder=(select Oid from ConstantInfo  with (nolock) where constantName=52 and ShortDesc='H')
--    and b.oid in (select StudentName from Hostel  with (nolock) where GCRecord is null and status in(select Oid from ConstantInfo  with (nolock) where constantName=34 and ShortDesc in('R','D')))
--	--and a.paidmount=0
--     )

---DueDate
Update StudentFeeDetails set Duedate = b.DueDate , FeeDate = b.DueDate
from StudentFeeDetails a with (nolock) 
join FeeCollectOnFinalDate b  with (nolock) on a.FeeIndex = b.FeeIndex 
						    and a.FeeName = b.FeeName
						    and a.AcademicYearId=b.AcademicYearId
						    and a.SchoolId = b.SchoolId
where b.GCRecord is null 
/** NEW ADD **/
and (a.StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

----ExtendedDueDate
UPDATE    StudentFeeDetails SET  DueDate = b.ExtendedDueDate
FROM         StudentFeeDetails  with (nolock) 
JOIN StudentFeeFinalDate AS b  with (nolock) ON b.StudentName = StudentFeeDetails.StudentName 
							AND b.AcademicYearId = StudentFeeDetails.AcademicYearId 
							AND b.SchoolId = StudentFeeDetails.SchoolId 
JOIN  FeeCollectOnFinalDate AS c  with (nolock) ON c.FeeIndex = StudentFeeDetails.FeeIndex 
							AND c.FeeName = StudentFeeDetails.FeeName 
							AND c.Oid = b.FeeName 
							AND c.AcademicYearId = StudentFeeDetails.AcademicYearId 
							AND c.SchoolId = StudentFeeDetails.SchoolId
where b.GCRecord is null 							
and c.GCRecord is null 
/** NEW ADD **/
and (StudentFeeDetails.StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/
							


UPDATE StudentFeeDetails SET ConcessionName = ConcessionSetting.ConcessionName FROM  ConcessionSetting  with (nolock) INNER JOIN 
StudentFeeConcession  with (nolock) ON ConcessionSetting.Oid = StudentFeeConcession.ConcessionName INNER JOIN 
StudentFeeDetails  with (nolock) ON ConcessionSetting.FeeName = StudentFeeDetails.FeeName AND StudentFeeConcession.StudentName = StudentFeeDetails.StudentName AND  
ConcessionSetting.FeeIndex = StudentFeeDetails.FeeIndex
/** NEW ADD **/
and (StudentFeeDetails.StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

--COMPLETED %
update StudentFeeDetails set Percentage =  CONVERT(DECIMAL(16,1),((NetAmount - BalanceAmount) / NetAmount)*100)  where NetAmount >0 and @ChallanType<>2 and AcademicYearId = @AcademicyearId and SchoolId = @Schoolid
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

update StudentFeeDetails set ChalanPercentage =  CONVERT(DECIMAL(16,1),((NetAmount - CBalanceAmount) / NetAmount)*100)  where NetAmount >0 and @ChallanType=2 and AcademicYearId = @AcademicyearId and SchoolId = @Schoolid
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

update StudentFeeDetails set Percentage = 100 from StudentFeeDetails  with (nolock) where NetAmount =0 and @ChallanType<>2 and AcademicYearId = @AcademicyearId and SchoolId = @Schoolid
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/

update StudentFeeDetails set ChalanPercentage = 100 from StudentFeeDetails  with (nolock) where NetAmount =0 and @ChallanType=2 and AcademicYearId = @AcademicyearId and SchoolId = @Schoolid
/** NEW ADD **/
and (StudentName = @StudentId or @StudentId is null)
/** END NEW ADD **/


go

--select * from School
--exec [StudentFeeDetail] '22EEF921-80E4-4173-942E-5C89F91BBD37','08ACDB07-FBA6-4B07-AD14-C63F991E6ED0','51b83996-092b-4edf-81ef-847a346d4d96',null

--select * from StudentFeeDetails where StudentName='51b83996-092b-4edf-81ef-847a346d4d96'

--select * from FeeMaster '536D4A2E-C0A9-4C8C-94D7-037D41297BCC'

--select * from FeeArrearDetail where ArrearFeeName='536D4A2E-C0A9-4C8C-94D7-037D41297BCC' and studentname ='536D4A2E-C0A9-4C8C-94D7-037D41297BCC'