USP_CLASSMEETINGTEMPLATE_MOVE_GETNEWPERIODVALUES

Used to return valid valid template data given loosely defined period parameters

Parameters

Parameter	Parameter Type	Mode
@CLASSID	uniqueidentifier	IN
@STARTTIME	UDT_HOURMINUTE	IN
@ENDTIME	UDT_HOURMINUTE	IN
@CYCLEDAYID	uniqueidentifier	IN
@TIMETABLEDAYPERIODID	uniqueidentifier	INOUT
@LENGTHINPERIODS	int	INOUT
@STARTTIME_NEW	UDT_HOURMINUTE	INOUT
@ENDTIME_NEW	UDT_HOURMINUTE	INOUT

Definition

Copy



CREATE procedure dbo.USP_CLASSMEETINGTEMPLATE_MOVE_GETNEWPERIODVALUES
(
    @CLASSID uniqueidentifier,
    @STARTTIME dbo.UDT_HOURMINUTE,
    @ENDTIME dbo.UDT_HOURMINUTE,
    @CYCLEDAYID uniqueidentifier,
    @TIMETABLEDAYPERIODID uniqueidentifier output,
    @LENGTHINPERIODS int output,
    @STARTTIME_NEW dbo.UDT_HOURMINUTE output,
    @ENDTIME_NEW dbo.UDT_HOURMINUTE output
)
as
begin
    set nocount on

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @TIMETABLEID uniqueidentifier
    select @TIMETABLEID = (select TIMETABLE.ID
        from dbo.TIMETABLE
            inner join dbo.SESSION on TIMETABLE.ID = SESSION.TIMETABLEID
        where SESSION.ID = dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, CLASS.STARTDATE, CLASS.ENDDATE))
    from dbo.CLASS 
        inner join dbo.COURSE on COURSE.ID = CLASS.COURSEID 
        where CLASS.ID = @CLASSID

    declare @TIMETABLEDAYID uniqueidentifier
    select @TIMETABLEDAYID = ID 
        from TIMETABLEDAY where CYCLEDAYID = @CYCLEDAYID and TIMETABLEID = @TIMETABLEID

    declare @TIMEDIFF int
    set @TIMEDIFF = (DATEDIFF(MINUTE, dbo.UFN_HOURMINUTE_DISPLAYTIME(@STARTTIME), dbo.UFN_HOURMINUTE_DISPLAYTIME(@ENDTIME)))

    select top 1 @TIMETABLEDAYPERIODID = ID, @STARTTIME_NEW = STARTTIME
        from dbo.UFN_TIMETABLEDAY_GETTIMETABLEDAYPERIODS(@TIMETABLEDAYID)
        order by ABS(DATEDIFF(MINUTE,
            cast(dbo.UFN_HOURMINUTE_DISPLAYTIME(STARTTIME) as time),
            cast(dbo.UFN_HOURMINUTE_DISPLAYTIME(@STARTTIME) as time)))

    select top 1 @ENDTIME_NEW = ENDTIME
        from dbo.UFN_TIMETABLEDAY_GETTIMETABLEDAYPERIODS(@TIMETABLEDAYID)
        where ENDTIME > @STARTTIME_NEW
        order by ABS(DATEDIFF(MINUTE,
            cast(dbo.UFN_HOURMINUTE_DISPLAYTIME(ENDTIME) as time),
            DATEADD(MINUTE,@TIMEDIFF,cast(dbo.UFN_HOURMINUTE_DISPLAYTIME(@STARTTIME_NEW) as time))))

    declare @Temp table(ID uniqueidentifier, STARTTIME dbo.UDT_HOURMINUTE, ENDTIME dbo.UDT_HOURMINUTE, ROWNUMBER int)
    insert into @Temp
    select
        ID,
        STARTTIME,
        ENDTIME,
        (ROW_NUMBER() over (order by STARTTIME)) 
    from dbo.UFN_TIMETABLEDAY_GETTIMETABLEDAYPERIODS(@TIMETABLEDAYID)

    select @LENGTHINPERIODS = ((select ROWNUMBER from @Temp where ENDTIME = @ENDTIME_NEW) 
                            - (select ROWNUMBER from @Temp where STARTTIME = @STARTTIME_NEW)) 
                            + 1
    return 0
end