Разработка подсистемы учета успеваемости студентов Байкальского государственного университета экономики и права

and e.Course = @Course and e.NumTerm = @NumTerm

UNION

--

-- Гос экзамены

--

SELECT [CodExType]=CASE WHEN ISNULL(tf.CodExType,0)=0 THEN 1 ELSE tf.CodExType END

, 0 AS [ExLot],

CodSub=CASE WHEN ISNULL(tf.CodSub,0)=0 THEN 841 ELSE tf.CodSub END,

1 AS [CodSubType],CodKaf, 20 AS Lec, 0 AS Sem, 216 AS Sam, 6.0 AS ECTScredits

FROM [dbo].TermFlow tf

inner join St

udyPlans p on tf.CodPlan=p.CodPlan

WHERE tf.CodPerType=7

AND ((@CodPlan IS NULL) OR (tf.CodPlan = @CodPlan)) AND

tf.Course=@Course and tf.NumTerm=@NumTerm

UNION

-- =============================================

-- Практики 1.5 кредита в неделю (для преддипломной - 5 кредитов)

-- =============================================

SELECT [CodExType]=CASE WHEN ISNULL(tf.CodExType,0)=0 THEN 1 ELSE tf.CodExType END,

0 AS [ExLot],

CodSub=CASE WHEN ISNULL(tf.CodSub,0)=0 THEN -1 ELSE tf.CodSub END,

1 AS [CodSubType],CodKaf, 0 AS Lec, 0 AS Sem, WorkLng*48 AS Sam,

ECTScredits=CASE WHEN NameSub LIKE 'преддипломная практика%' THEN 5 ELSE WorkLng*1.5 END

FROM [dbo].TermFlow tf

inner join StudyPlans p on tf.CodPlan=p.CodPlan

left join Subs s on tf.CodSub=s.CodSub

left join ExTypes e on tf.CodExType=e.CodExType

WHERE tf.CodPerType=4 AND

tf.Course=@Course and tf.NumTerm=@NumTerm

AND ((@CodPlan IS NULL) OR (tf.CodPlan = @CodPlan))

open EL

select @CodOrganization = @COrganization, @CodPrep = NULL

fetch next from EL into @CodExType, @ExLot, @CodSub, @CodSubType, @CodKaf, @Lec, @Sem, @Sam, @ECTScredits

while (@@fetch_status <> -1)

begin

IF (@@fetch_status <> -2)

begin

execute dbo.e_DetectCodVar @CodSub, @CodSubType, @CodOrganization, @CodKaf, @Lec, @Sem, @Sam, @ECTScredits, @CodVar output

if @CodVar > 0 and not exists(select * from dbo.ExList e, dbo.HoursVars h where e.CodVar = h.CodVar and

e.CodGrup = @CodGrup and e.Course = @Course and e.NumTerm = @NumTerm and e.CodExType = @CodExType and h.CodSub = @CodSub)

insert dbo.ExList values (@CodGrup, @Course, @NumTerm, @CodExType, @CodPrep, @ExLot, @CodVar)

end

fetch next from EL into @CodExType, @ExLot, @CodSub, @CodSubType, @CodKaf, @Lec, @Sem, @Sam, @ECTScredits

end

deallocate EL

CREATE PROCEDURE [dbo].[Web_GetEstimations] (@CodStudent int)

AS

/*

Возвращает оценки по студенту

Зубков Андрей

19.09.2007

exec Web_GetEstimations 8000

*/

select Course, NumTerm, NameSub, ExType, ECTSCredits,Mark=CASE WHEN (m.CodExType=2 and Mark>2)

THEN 'зачтено' ELSE CASE WHEN Mark=5 THEN 'отлично'

WHEN Mark=4 THEN 'хорошо'

WHEN Mark=3 THEN 'удовл.' END END+'('+convert(varchar,ECTSMark)+')'

from analysis.s_GetMarks(@CodStudent) m

left join Subs s on m.CodSub=s.CodSub

left join ExTypes et on m.CodExType=et.CodExType

where isnull(Mark,0)>2-- is not null --alt 18.01.2008 только положительные оценки

order by Course, NumTerm, NameSub

/*

Ситник Игорь

01.12.2006

Функция возвращает список всех оценок студента из БД Analysis

(а то у студентов возникают вопросы о неправильном подсчете + здесь уже есть дипломное проектирование)

alt 07 2007 Добавлено разделение на предметы обычные и по выбору (для того, чтобы выбрать оценку и нагрузку только по одному из них)

*/

CREATE FUNCTION [dbo].[s_GetMarks](@CodStudent int=0)

RETURNS

@Marks TABLE(Course tinyint,NumTerm tinyint, CodSub int,CodSubType tinyint, CodExType tinyint, Mark tinyint, ECTSMark smallint,ECTSCredits float, CodPrep int, InReit bit)

BEGIN

declare @CodPlan int

select top 1 @CodPlan=CodPlan from dbo.StudyMarks WHERE CodStudent =@CodStudent

declare @ExLot Table (Course tinyint, NumTerm tinyint, CodSub int, CodSubType tinyint, CodExType tinyint,

Mark int, ECTSMark int, ECTSCredits int, CodPrep int, InReit bit)

insert into @Exlot

select Course=IsNull(se.Course,sm.CourseSub), NumTerm=IsNULL(se.NumTerm,sm.NumTerm),

CodSub=IsNULL(sm.CodSub,se.CodSub), CodSubType=IsNULL(sm.CodSubType,se.CodSubType),

CodExType=IsNULL(se.CodExType,sm.CodExType), sm.Mark, sm.ECTSMark, ECTSCredits=IsNULL(se.ECTSCredits,0), sm.CodPrep, sm.InReit

from

(select * from dbo.ECTS_studyExams where CodPlan=@CodPlan AND CodSubType not in (3,4)) se

JOIN (select * from dbo.StudyMarks where CodStudent=@CodStudent) sm

on se.CodPlan=sm.CodPlan and (se.CodSub=sm.CodSub or sm.CodSub=-1)

and (se.CodSubType=sm.CodSubType or sm.CodSubType=2) and se.Course=sm.CourseSub

and se.NumTerm=sm.NumTerm and se.CodExType=sm.CodExType

where isnull(exlot,0)=1

insert into @Marks

select Course=IsNull(se.Course,sm.CourseSub), NumTerm=IsNULL(se.NumTerm,sm.NumTerm),

CodSub=IsNULL(sm.CodSub,se.CodSub), CodSubType=IsNULL(sm.CodSubType,se.CodSubType),

CodExType=IsNULL(se.CodExType,sm.CodExType), sm.Mark, sm.ECTSMark, ECTSCredits=IsNULL(se.ECTSCredits,0), sm.CodPrep, sm.InReit

--s.CodStudent, M=sum(IsNULL(sm.Mark,0)),R=case when sum(se.ECTSCredits)=0 then 0 else sum(IsNULL(ECTSMark,0)*sу.ECTSCredits)/sum(se.ECTSCredits) end

from

(select * from dbo.ECTS_studyExams where CodPlan=@CodPlan AND CodSubType not in (3,4)) se

FULL JOIN (select * from dbo.StudyMarks where CodStudent=@CodStudent) sm

on se.CodPlan=sm.CodPlan and (se.CodSub=sm.CodSub or sm.CodSub=-1) --case when sm.CodSub in (688,689) then -1 else sm.CodSub end--academia.e_GetCodSub(sm.CodSub)

and (se.CodSubType=sm.CodSubType or sm.CodSubType=2) and se.Course=sm.CourseSub

and se.NumTerm=sm.NumTerm and se.CodExType=sm.CodExType

where isnull(exlot,0)<>1

union

select * from @ExLot

return

END

/************************************************

Предназначена для работы с оценкой (в частности, вызывается в коде upExam - update на qExam)

@CodOp = 1 - удаление

@CodOp = 0 - добавление и модификация

************************************************/

CREATE PROCEDURE [dbo].[e_UpdateExam]

(

@CodOp tinyint,

@CodStudent int,

@CodKaf int,

@CodSub int,

@CodSubType tinyint,

@Course tinyint,

@NumTerm tinyint,

@CodExType tinyint,

@Mark tinyint,

@CodECTSMark smallint,

@CodPrep int,

@DateExam smalldatetime,

@CodVUZ tinyint,

@CodVar int,

@ECTScretits float

)as

BEGIN

--begin tran

--if not @Mark in (1,2,3,4,5) return

--Удаление

IF @CodOp=1

begin

delete from Exam

where

CodStudent = @CodStudent and

Course =@Course and

NumTerm = @NumTerm and

CodExType = @CodExType and

Mark = @Mark and

CodVar = @CodVar

-- if @@error <> 0 rollback tran else commit tran

return

end

declare

@CodOrganization tinyint,

@CodVarNEW int,

@Lec smallint,

@Sem smallint,

@Sam smallint

-- @err varchar(500)

select @CodOrganization = CodOrganization,

@Lec = Lec, @Sem = Sem, @Sam = Sam

 Скачать реферат