Transcript Generation Stored Procedure

USE [TEST]
GO
/****** Object: StoredProcedure [dbo].[sp_TranscriptReportDataGeneration] Script Date: 07/25/2012 11:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_TranscriptReportDataGeneration]
(
@StudentId Int
)
-- exec sp_TranscriptReportDataGeneration 1107001
AS
BEGIN
DECLARE @String VARCHAR(100),
@TermId VARCHAR(50),
@CourseId VARCHAR(50),
@LetterGrade VARCHAR(5),
@GP VARCHAR(6),
@STRTPOS INT,
@CREDIT VARCHAR(10),
@NAME VARCHAR(100),
@DEPT VARCHAR(100),
@ExamName varchar(100),
@TargetYear int,
@TargetTerm int,
@AcademicYear int,
@CourseName varchar(50)
SELECT @NAME = StudentsName FROM T_Std_General_Information WHERE StudentId=@StudentId
SELECT @DEPT = departmentName FROM T_Common_Department_Setup WHERE departmentId = (SELECT DepartmentId FROM T_Std_General_Information WHERE StudentId=@StudentId)
declare @TempTab1 table
(
StudentId int,
StudentName varchar(100),
Department varchar(50),
TermIndex varchar(50),
CourseId varchar(50),
Credit decimal(8,2),
Grade varchar(10),
GradePoint decimal(8,2)
)
declare @Tabstring nvarchar(max)
declare @Tabname nvarchar(100)
set @Tabname='tblTempTab1'
set @Tabstring='create table #' + @Tabname
Declare @WhichOne int,
@UpdateCol INT,
@Loop INT
SET @Loop=111;
SET @UpdateCol=8;
WHILE(@Loop != 0)
BEGIN
Declare @Sql nvarchar(max),
@ParameterDefination nvarchar(max)
SET @WhichOne = @UpdateCol;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = 'T_Common_Student_Consolidated_Earned_Credit')
SELECT @CourseName = CAST(name AS varchar(50)) FROM cte WHERE rn = @WhichOne;
set @Sql = 'select @String1 = ' + @CourseName + ' from T_Common_Student_Consolidated_Earned_Credit where StudentId = ' + CAST(@StudentId as varchar(20))
set @ParameterDefination = '@String1 varchar(200) output'
exec sp_EXECUTESQL @Sql, @ParameterDefination ,@String1 = @String output
if(Isnull(@String,'') != '')
BEGIN
DECLARE
@STR VARCHAR(100),
@SUBSTR1 VARCHAR(100),
@SUBSTR2 VARCHAR(100),
@SUBSTR3 VARCHAR(100),
@SUBSTR4 VARCHAR(100)
SET @STR=@String --'T1(R)/CSE2006MAYCSE1101(3.00)/A+/4.00'
SELECT @TermId = LEFT(@STR,PATINDEX('%(%)/%',@STR)-1)
SELECT @SUBSTR1 = RIGHT(@STR,LEN(@STR)-CHARINDEX('/',@STR))
SELECT @CourseId = LEFT(@SUBSTR1,PATINDEX('%(%)/%',@SUBSTR1)-1)
SELECT @SUBSTR2 = RIGHT(@SUBSTR1,LEN(@SUBSTR1)-CHARINDEX('/',@SUBSTR1))
SELECT @CREDIT = SUBSTRING(@SUBSTR1,PATINDEX('%(%',@SUBSTR1)+1,4)
SELECT @LetterGrade = LEFT(@SUBSTR2,PATINDEX('%/%',@SUBSTR2)-1)
SELECT @SUBSTR3 = RIGHT(@SUBSTR2,LEN(@SUBSTR2)-CHARINDEX('/',@SUBSTR2))
SELECT @SUBSTR4 = RIGHT(@SUBSTR3,LEN(@SUBSTR3)-CHARINDEX('/',@SUBSTR3))
SELECT @GP=@SUBSTR4
SELECT @TargetYear=TargetYear,@AcademicYear=AcademicYear FROM T_Term_Setup WHERE TermId=@TermId
SELECT @TargetTerm=TargetTerm FROM T_Term_Course_Offer WHERE TermId=@TermId AND CourseId=@CourseId
select @ExamName= dbo.IntToStr(CONVERT(varchar,@TargetYear))+' Year '+dbo.IntToStr(CONVERT(varchar,@TargetTerm))+' Term Examination '+ CAST(@AcademicYear AS varchar(10))
PRINT @ExamName
insert into @TempTab1
(
StudentId,
StudentName,
Department,
TermIndex,
CourseId,
Credit,
Grade,
GradePoint
)
VALUES(@StudentId,@NAME,@DEPT,@TermId,@CourseId,@CREDIT,@LetterGrade,@GP)
END
SELECT @UpdateCol=@UpdateCol+1
SELECT @Loop=@Loop-1
END
--SELECT * FROM @TempTab1
---------------------------------------------- Index table ----------------------------------------
declare @TempTab2 table
(
TermIndex varchar(100),
TermIdMain varchar(100),
TermTakenCredit decimal(8,2),
TermCompletedCredit decimal(8,2),
TotalTakenCredit decimal(8,2),
TotalCompletedCredit decimal(8,2),
TermEarnedPoint decimal(8,2),
TotalEarnedPoint decimal(8,2)
)
declare @Tab2string nvarchar(max)
declare @Tab2name nvarchar(100)
set @Tab2name='tblTempTab1'
set @Tab2string='create table #' + @Tab2name
Declare @WhichItem int,
@UpdateColumn INT,
@Looping INT,
@QString1 varchar(1000),
@QString varchar(1000),
@TermIndex varchar(500)
SET @Looping=20;
SET @UpdateColumn=2;
DECLARE @TermIdIndex varchar(10),
@TermIdMain varchar(100),
@TermTakenCompletedCredit varchar(100),
@TermTakenCredit varchar(100),
@TermCompletedCredit varchar(100),
@TotalTakenCompletedCredit varchar(100),
@TotalTakenCredit varchar(100),
@TotalCompletedCredit varchar(100),
@TermEarnedPoint varchar(50),
@TotalEarnedPoint varchar(50),
@RawString varchar(50)
WHILE(@Looping != 0)
BEGIN
Declare @SqlStr nvarchar(max),
@ParameterDefn nvarchar(max)
SET @WhichItem = @UpdateColumn;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = 'T_Common_Student_Earned_Credit_Term_Index')
SELECT @TermIndex = CAST(name AS varchar(50)) FROM cte WHERE rn = @WhichItem;
set @SqlStr = 'select @QString1 = ' + @TermIndex + ' from T_Common_Student_Earned_Credit_Term_Index where StudentId = ' + CAST(@StudentId as varchar(20))
set @ParameterDefn = '@QString1 varchar(200) output'
exec sp_EXECUTESQL @SqlStr, @ParameterDefn ,@QString1 = @QString output
if(@QString is not null)
begin
--SELECT @QString -- CSE2012JUL_2K11Y1T1(T1)#8-11#9.00/9.00#9.00/9.00#33.75#33.75#
SELECT @TermIdIndex = SUBSTRING(@QString,PATINDEX('%(%',@QString)+1,((PATINDEX('%)%',@QString)-1)-(PATINDEX('%(%',@QString))))
--SELECT @TermIdIndex
SELECT @TermIdMain = LEFT(@QString,PATINDEX('%(%',@QString)-1)
--SELECT @TermIdMain
--PRINT @QString
SELECT @RawString=RIGHT(@QString,(LEN(@QString)-PATINDEX('%#%',@QString)))
--PRINT @RawString
SELECT @RawString=RIGHT(@RawString,(LEN(@RawString)-PATINDEX('%#%',@RawString)))
--PRINT @RawString
SELECT @TermTakenCompletedCredit=LEFT(@RawString,PATINDEX('%#%',@RawString)-1)
--PRINT @TermTakenCompletedCredit
SELECT @TermTakenCredit=LEFT(@TermTakenCompletedCredit,PATINDEX('%/%',@TermTakenCompletedCredit)-1)
SELECT @TermCompletedCredit=RIGHT(@TermTakenCompletedCredit,PATINDEX('%/%',@TermTakenCompletedCredit)-1)
--PRINT @TermTakenCredit
--PRINT @TermCompletedCredit
SELECT @RawString=RIGHT(@RawString,(LEN(@RawString)-PATINDEX('%#%',@RawString)))
--PRINT @RawString
SELECT @TotalTakenCompletedCredit=LEFT(@RawString,PATINDEX('%#%',@RawString)-1)
--PRINT @TotalTakenCompletedCredit
SELECT @TotalTakenCredit=LEFT(@TotalTakenCompletedCredit,PATINDEX('%/%',@TotalTakenCompletedCredit)-1)
SELECT @TotalCompletedCredit=RIGHT(@TotalTakenCompletedCredit,PATINDEX('%/%',@TotalTakenCompletedCredit)-1)
--PRINT @TotalTakenCredit
--PRINT @TotalCompletedCredit
SELECT @RawString=RIGHT(@RawString,(LEN(@RawString)-PATINDEX('%#%',@RawString)))
--PRINT @RawString
SELECT @TermEarnedPoint=LEFT(@RawString,PATINDEX('%#%',@RawString)-1)
--PRINT @TermEarnedPoint
SELECT @RawString=RIGHT(@RawString,(LEN(@RawString)-PATINDEX('%#%',@RawString)))
--PRINT @RawString
SELECT @TotalEarnedPoint=LEFT(@RawString,PATINDEX('%#%',@RawString)-1)
--PRINT @TotalEarnedPoint
insert into @TempTab2
(
TermIndex,
TermIdMain,
TermTakenCredit,
TermCompletedCredit,
TotalTakenCredit,
TotalCompletedCredit,
TermEarnedPoint,
TotalEarnedPoint
)
VALUES(@TermIdIndex,@TermIdMain,@TermTakenCredit,@TermCompletedCredit,@TotalTakenCredit,@TotalCompletedCredit,@TermEarnedPoint,@TotalEarnedPoint)
end
SELECT @UpdateColumn=@UpdateColumn+1
SELECT @Looping=@Looping-1
END
SELECT DISTINCT TT1.StudentId,
TT1.StudentName,
TT1.Department,
TT1.TermIndex,
TT1.CourseId,
TT1.Credit,
TT1.Grade,
TT1.GradePoint,TT2.TermIdMain,
TT2.TermTakenCredit,
TT2.TermCompletedCredit,
TT2.TotalTakenCredit,
TT2.TotalCompletedCredit,
TT2.TermEarnedPoint,
TT2.TotalEarnedPoint,
TS.TermName,
TS.TargetYear,
TS.TargetTerm,
TS.AcademicSession,
TS.AcademicYear,
(DATENAME(month,CAST(TS.TermOpenDate as DATE))+' '+DATENAME(year,CAST(TS.TermOpenDate as DATE)))as FROMDATE,
(DATENAME(month,CAST(TS.ResultPublication as DATE))+' '+DATENAME(year,CAST(TS.ResultPublication as DATE))) as TODATE ,
TCO.CourseCode,
TCO.CourseTitle
FROM @TempTab1 TT1 inner join
@TempTab2 TT2 on TT1.TermIndex = TT2.TermIndex inner join
T_Term_Setup TS on TS.TermId = TT2.TermIdMain inner join T_Term_Course_Offer TCO on TCO.TermId=TT2.TermIdMain and TCO.CourseId=TT1.CourseId
WHERE StudentId=@StudentId
END
-- exec sp_TranscriptReportDataGeneration 1107003

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