
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'SubjectScoreUpdate')
DROP Procedure SubjectScoreUpdate
Go

Create Procedure SubjectScoreUpdate 
@AcademicyearId uniqueIdentifier,
@Schoolid uniqueIdentifier,
@progressOid uniqueIdentifier
as 

Declare @progressEntry uniqueIdentifier
Declare POid Cursor for 
select Oid from progressEntry where AcademicYearId = @AcademicyearId and SchoolId = @Schoolid and (oid=@ProgressOid or @ProgressOid is null)

Open POid
FETCH NEXT FROM POid INTO @progressEntry
WHILE @@FETCH_STATUS = 0
 
BEGIN  

	--select @progressEntry
	delete from SubjectScore where ProgressEntry=@progressEntry

	--HighestScore
	insert into SubjectScore(oid,progressEntry,SubjectName,SubjectTeacher,Score,Mark)
	select NEWID(), @progressEntry, SubjectName,b.SubjectTeacher ,0,MAX(TotalMarksEarned) 
	from ProgressEntry a
	join SubjectFinalGrade b on b.ProgressEntry=a.Oid
	where a.GCRecord is null
	and  b.GCRecord is null
	and a.Oid=@progressEntry
	group by SubjectName, SubjectTeacher

	--LowestScore
	 insert into SubjectScore(oid,progressEntry,SubjectName,SubjectTeacher,Score,Mark)
	select NEWID(), @progressEntry, SubjectName,b.SubjectTeacher ,1,Min(TotalMarksEarned) 
	from ProgressEntry a
	join SubjectFinalGrade b on b.ProgressEntry=a.Oid
	where a.GCRecord is null
	and  b.GCRecord is null
	and a.Oid=@progressEntry
	and b.Absent=0
	group by SubjectName, SubjectTeacher

	--Average
	 insert into SubjectScore(oid,progressEntry,SubjectName,SubjectTeacher,Score,Mark)
	select NEWID(), @progressEntry, SubjectName,b.SubjectTeacher ,2,avg(TotalMarksEarned) 
	from ProgressEntry a
	join SubjectFinalGrade b on b.ProgressEntry=a.Oid
	where a.GCRecord is null
	and  b.GCRecord is null
	and a.Oid=@progressEntry
	group by SubjectName, SubjectTeacher


	--NoofStudent
	insert into SubjectScore(oid,progressEntry,SubjectName,SubjectTeacher,Score,Mark)
	select NEWID(), @progressEntry, SubjectName,b.SubjectTeacher ,7,count(b.StudentName) 
	from ProgressEntry a
	join SubjectFinalGrade b on b.ProgressEntry=a.Oid
	join GradeSetting c on c.oid=b.grade 
	where a.GCRecord is null
	and  b.GCRecord is null
	and c.GCRecord is null
	and a.Oid=@progressEntry
	--and isnull(c.ExamResult,0) = 1
	group by SubjectName, SubjectTeacher
	
	
	--NoofPass
	insert into SubjectScore(oid,progressEntry,SubjectName,SubjectTeacher,Score,Mark)
	select NEWID(), @progressEntry, SubjectName,b.SubjectTeacher ,3,count(b.StudentName) 
	from ProgressEntry a
	join SubjectFinalGrade b on b.ProgressEntry=a.Oid
	join GradeSetting c on c.oid=b.grade 
	where a.GCRecord is null
	and  b.GCRecord is null
	and c.GCRecord is null
	and a.Oid=@progressEntry
	and isnull(c.ExamResult,0) = 1
	group by SubjectName, SubjectTeacher

	--NoofFail
	insert into SubjectScore(oid,progressEntry,SubjectName,SubjectTeacher,Score,Mark)
	select NEWID(), @progressEntry,b.SubjectName,b.SubjectTeacher ,4
	,Stucount=(	select count(x.StudentName) 
				from SubjectFinalGrade  x
				join GradeSetting y on y.oid=x.grade 
				where x.GCRecord is null
				and y.GCRecord is null
				and x.ProgressEntry=@progressEntry
				and x.SubjectName= b.SubjectName
				and isnull(y.ExamResult,0) = 0
				)
	from ProgressEntry a
	join SubjectFinalGrade b on b.ProgressEntry=a.Oid
	where a.GCRecord is null
	and  b.GCRecord is null
	and a.Oid=@progressEntry
	group by SubjectName, SubjectTeacher

	--Fail Percentage(No.of Students / Total Students)
	insert into SubjectScore(oid,progressEntry,SubjectName,SubjectTeacher,Score,Mark)
	select NEWID(), @progressEntry,b.SubjectName,b.SubjectTeacher ,5
	,Stucount= Round(100 * (	select count(x.StudentName) 
				from SubjectFinalGrade  x
				join GradeSetting y on y.oid=x.grade 
				where x.GCRecord is null
				and y.GCRecord is null
				and x.ProgressEntry=@progressEntry
				and x.SubjectName= b.SubjectName
				and isnull(y.ExamResult,0) = 0
				) 
	/COUNT(b.studentName),2)			
	from ProgressEntry a
	join SubjectFinalGrade b on b.ProgressEntry=a.Oid
	where a.GCRecord is null
	and  b.GCRecord is null
	and a.Oid=@progressEntry
	group by SubjectName, SubjectTeacher

	----Pass Percentage(No.of Students / Total Students)
	insert into SubjectScore(oid,progressEntry,SubjectName,SubjectTeacher,Score,Mark)
	select NEWID(), @progressEntry,b.SubjectName,b.SubjectTeacher ,6
	,Stucount= round(100 *  (	select count(x.StudentName) 
				from SubjectFinalGrade  x
				join GradeSetting y on y.oid=x.grade 
				where x.GCRecord is null
				and y.GCRecord is null
				and x.ProgressEntry=@progressEntry
				and x.SubjectName= b.SubjectName
				and isnull(y.ExamResult,0) = 1
				) 
	/COUNT(b.studentName),2)			

	from ProgressEntry a
	join SubjectFinalGrade b on b.ProgressEntry=a.Oid
	where a.GCRecord is null
	and  b.GCRecord is null
	and a.Oid=@progressEntry
	group by SubjectName, SubjectTeacher
	
FETCH NEXT FROM POid INTO @progressEntry	
End
Close POid
Deallocate POid

go

--Proc_SubjectScore null


--delete from SubjectScore