Result Processing SQL Stored Procedure

USE [dbRegistration]
GO
/****** Object: StoredProcedure [dbo].[sp_ResultProcessing] Script Date: 11/26/2011 10:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author: Update by: Md. Nazim Uddin>
-- Create date: <Date: 14-11-2011>
-- Description: <Process Results of Selected Term>
-- =============================================
ALTER PROCEDURE [dbo].[sp_ResultProcessing]
(
@TermId varchar(50)
)
--exec sp_ResultProcessing 'CSE2011MAR_2K10Y3T2'
AS
BEGIN
-- Declare Variables for internal use
DECLARE
@intErrorCode int,
@CourseId varchar(20),
@TotalMarks Decimal(10,2),
@StudentId int,
@LetterGrade varchar(5),
@GradePoint Decimal(6, 2),
@RegularTerm varchar(30),
@TermType char,
@Count int,
@ProcessingDecision int
SELECT @ProcessingDecision=(SELECT TermClosingStatus FROM T_Term_Setup WHERE TermId=@TermId)
IF(@ProcessingDecision=0)
--------------------------------------------------------------------------------------------------------------------
BEGIN
---------------------------------------------------------------------------------------------------------------------
---- Starting Transaction---------
begin transaction
--------------------------------------------------------------------------------------------------------------------
------- Makeing Term Closed --------
---15-11-2011
UPDATE T_Term_Setup
SET TermClosingStatus=1
WHERE TermId=@TermId
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
---------------------------------------------------------------------------------------------------------------------
--Update Class Test Marks
declare TermResultCursor cursor for
select distinct CourseId
from T_Term_Class_Test_Marks
where TermId = @TermId
open TermResultCursor
fetch next from TermResultCursor into
@CourseId
while @@FETCH_STATUS = 0
begin
declare ClassTestResult cursor for
SELECT StudentId, SUM(ClassTestMarks) ---- make sum of 3/4 class test
FROM dbRegistration.dbo.T_Term_Class_Test_Marks
WHERE CourseId = @CourseId
GROUP BY StudentId
open ClassTestResult
fetch next from ClassTestResult into
@StudentId, @TotalMarks
while @@FETCH_STATUS = 0
begin
UPDATE T_Term_Std_Result_Theory_Details
SET ClassTest=@TotalMarks
WHERE StudentId=@StudentId AND CourseId=@CourseId AND TermId = @TermId
fetch next from ClassTestResult into
@StudentId, @TotalMarks
end
close ClassTestResult
deallocate ClassTestResult
fetch next from TermResultCursor into
@CourseId
end
close TermResultCursor
deallocate TermResultCursor
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
---------------------------------------------------------------------------------------------------------------------
--Read RegularTerm
SELECT @TermType=TermType, @RegularTerm=isnull(RegularTermId,@TermId)
FROM T_Term_Setup
WHERE TermId=@TermId
--Read TotalMarks of Theory
SELECT @TotalMarks=TotalMarks
FROM T_Term_Marks_Distribution
WHERE TermId=@TermId AND CourseType='Theory'
--Update %Marks of Theory
UPDATE T_Term_Std_Result_Theory_Details
SET [%Marks]=(Attendance+SectionA+SectionB+ClassTest+Grace)*100/@TotalMarks
WHERE TermId=@TermId
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
---------------------------------------------------------------------------------------------------------------------
--Read TotalMarks of Sessional
SELECT @TotalMarks=TotalMarks
FROM T_Term_Marks_Distribution
WHERE TermId=@TermId AND CourseType='Sessional'
--Update %Marks of Sessional
UPDATE T_Term_Student_Result_Lab_Details
SET [%Marks]=(LabPerformance+Attendance+Viva+CenteralViva+Quiz+Grace)*100/@TotalMarks
WHERE TermId=@TermId
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
---------------------------------------------------------------------------------------------------------------------
--Student's Theory result with Letter grade
declare TermResultCursor cursor for
select isnull(CourseId,''),
isnull(StudentId,-1),
isnull(CEILING([%Marks]),-1)
from T_Term_Std_Result_Theory_Details
where TermId = @TermId
open TermResultCursor
fetch next from TermResultCursor into
@CourseId, @StudentId, @TotalMarks
while @@FETCH_STATUS = 0
begin
SELECT @LetterGrade=LetterGrade, @GradePoint=GPA
FROM T_Term_Marks_GPA_Setup
WHERE StartMarks<=@TotalMarks AND @TotalMarks<=EndMarks
--Insert into Term_Wise_Course_Result
INSERT INTO T_Term_Wise_Course_Result(TermId, RegularTerm, CourseId, StudentId, LetterGrade, GradePoint)
VALUES(@TermId, @RegularTerm, @CourseId, @StudentId, @LetterGrade, @GradePoint)
--Insert into T_Common_Student_Earned_Credit
INSERT INTO T_Common_Student_Earned_Credit(TermId, RegularTerm, CourseId, StudentId, LetterGrade, GradePoint)
VALUES(@TermId, @RegularTerm, @CourseId, @StudentId, @LetterGrade, @GradePoint)
--If Failed insert or update Common_Backlog_Info
IF(@GradePoint=0)
begin
SELECT @Count=COUNT(*)
FROM T_Common_Backlog_Info
WHERE StudentId=@StudentId AND CourseId=@CourseId
IF(@Count=0)
begin
INSERT T_Common_Backlog_Info(StudentId, CourseId)
VALUES(@StudentId, @CourseId)
end
ELSE
begin
UPDATE T_Common_Backlog_Info SET RegistrationStatus='B'
end
print @Count
print 'Separate'
end
--Delete Course From BackLog If passed.......
IF(@GradePoint>0)
begin
DELETE FROM T_Common_Backlog_Info
WHERE StudentId=@StudentId AND CourseId=@CourseId
end
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
fetch next from TermResultCursor into
@CourseId, @StudentId, @TotalMarks
end
close TermResultCursor
deallocate TermResultCursor
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
---15-11-2011
---------------------------------------------------------------------------------------------------------------------
--Student's Sessional result with Letter grade
declare TermResultCursor cursor for
select isnull(CourseId,''),
isnull(StudentId,-1),
isnull(CEILING([%Marks]),-1)
from T_Term_Student_Result_Lab_Details
where TermId = @TermId
open TermResultCursor
fetch next from TermResultCursor into
@CourseId, @StudentId, @TotalMarks
while @@FETCH_STATUS = 0
begin
SELECT @LetterGrade=LetterGrade, @GradePoint=GPA
FROM T_Term_Marks_GPA_Setup
WHERE StartMarks<=@TotalMarks AND @TotalMarks<=EndMarks
--Insert into Term_Wise_Course_Result
---TermType added 22-11-2011
INSERT INTO T_Term_Wise_Course_Result(TermId, RegularTerm, CourseId, StudentId, LetterGrade, GradePoint, TermType)
VALUES(@TermId, @RegularTerm, @CourseId, @StudentId, @LetterGrade, @GradePoint, @TermType)
--Insert into T_Common_Student_Earned_Credit
INSERT INTO T_Common_Student_Earned_Credit(TermId, RegularTerm, CourseId, StudentId, LetterGrade, GradePoint)
VALUES(@TermId, @RegularTerm, @CourseId, @StudentId, @LetterGrade, @GradePoint)
--If Failed insert or update Common_Backlog_Info
IF(@GradePoint=0)
begin
SELECT @Count=COUNT(*)
FROM T_Common_Backlog_Info
WHERE StudentId=@StudentId AND CourseId=@CourseId
IF(@Count=0)
begin
INSERT T_Common_Backlog_Info(StudentId, CourseId)
VALUES(@StudentId, @CourseId)
end
ELSE
begin
UPDATE T_Common_Backlog_Info SET RegistrationStatus='B'
end
print @Count
print 'Separate'
end
--Delete Course From BackLog If passed.......
IF(@GradePoint>0)
begin
DELETE FROM T_Common_Backlog_Info
WHERE StudentId=@StudentId AND CourseId=@CourseId
end
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
fetch next from TermResultCursor into
@CourseId, @StudentId, @TotalMarks
end
close TermResultCursor
deallocate TermResultCursor
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
---15-11-2011
---------------------------------------------------------------------------------------------------------------------
--Update Result Processing Status [Result Theory]
UPDATE T_Term_Std_Result_Theory_Details
SET ResultProcessingStatus=1
WHERE TermId=@TermId
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
---15-11-2011
---------------------------------------------------------------------------------------------------------------------
--Update Result Processing Status [Result Laboratory]
UPDATE T_Term_Student_Result_Lab_Details
SET ResultPrecessingStatus=1
WHERE TermId=@TermId
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
--- 17-11-2011
--------------------------- Calculation of T_Student_Credit_Status TotalEarnedCredit value ----------------------------
DECLARE
@TotalEarnedCredit decimal(18,2),
@TotalEarnedPoint decimal(18,2),
@TestRecord int,
@StudentCategory int
declare StudentCreditStatus cursor for
select isnull(StudentId,-1)
from T_Term_Std_Course_Reg
where TermId = @TermId
open StudentCreditStatus
fetch next from StudentCreditStatus into
@StudentId
while @@FETCH_STATUS = 0
begin
SELECT @StudentCategory=COUNT(*)from T_Common_Student_Earned_Credit WHERE T_Common_Student_Earned_Credit.LetterGrade!='F' AND T_Common_Student_Earned_Credit.StudentId=@StudentId
if(@StudentCategory>0)
begin
UPDATE T_Student_Credit_Status
SET StudentCategory=2
WHERE StudentId=@StudentId
end
SELECT @TotalEarnedCredit=SUM(T_Common_Course_Information.CourseCredit)
FROM T_Common_Course_Information INNER JOIN
T_Common_Student_Earned_Credit ON T_Common_Course_Information.CourseId = T_Common_Student_Earned_Credit.CourseId
WHERE T_Common_Student_Earned_Credit.StudentId=@StudentId AND T_Common_Student_Earned_Credit.LetterGrade!='F'
SELECT @TotalEarnedPoint = SUM(T_Common_Student_Earned_Credit.GradePoint * T_Common_Course_Information.CourseCredit)
FROM T_Common_Course_Information INNER JOIN
T_Common_Student_Earned_Credit ON T_Common_Course_Information.CourseId=T_Common_Student_Earned_Credit.CourseId
WHERE T_Common_Student_Earned_Credit.StudentId=@StudentId
select @TestRecord = COUNT(*) from T_Student_Credit_Status WHERE StudentId=@StudentId
if(@TestRecord>0)
begin
UPDATE T_Student_Credit_Status
SET TotalEarnedCredit=@TotalEarnedCredit,
TotalEarnedPoint=@TotalEarnedPoint
WHERE StudentId=@StudentId
end
else if(@TestRecord=0)
begin
insert into T_Student_Credit_Status(StudentId,TotalEarnedCredit,TotalEarnedPoint)
values(@StudentId,@TotalEarnedCredit,@TotalEarnedPoint)
end
fetch next from StudentCreditStatus into
@StudentId
end
close StudentCreditStatus
deallocate StudentCreditStatus
-- If Any errors then go for Roll Back Transaction
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEMERROR
----------------------------------------------------------------------------------------------------------------------------
--Commit Transactio if no error
commit transaction
-------------User defined label for exeption handling---------------
PROBLEMERROR:
IF (@intErrorCode <> 0)
BEGIN
--PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
--------------------------------------------------------------------------------------------------------------------
END
---------------------------------------------------------------------------------------------------------------------
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s