USP_CLASSMEETINGTEMPLATE_CREATEFROMBLOCK

create procedure dbo.USP_CLASSMEETINGTEMPLATE_CREATEFROMBLOCK
(
    @CLASSID uniqueidentifier,
    @PATTERNBLOCKID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @TempTbl table (
        [ID] uniqueidentifier,
        [CLASSMEETINGGROUPID] uniqueidentifier,
        [STARTTIME] char(4),
        [ENDTIME] char(4),
        [CYCLEDAYID] uniqueidentifier,
        [TIMETABLEDAYPERIODID] uniqueidentifier,
        [LENGTHINPERIODS] int,
        ADDRECORD bit)

    insert into @TempTbl
    (
        ID,
        CLASSMEETINGGROUPID,
        STARTTIME,
        ENDTIME,
        CYCLEDAYID,
        TIMETABLEDAYPERIODID,
        LENGTHINPERIODS,
        ADDRECORD
    )
    select
        coalesce(CLASSMEETINGTEMPLATE.ID, newid()),
        CLASSMEETINGGROUP.ID,
        MEETINGS.STARTTIME,
        MEETINGS.ENDTIME,
        MEETINGS.CYCLEDAYID,
        MEETINGS.TIMETABLEDAYPERIODID,
        MEETINGS.LENGTHINPERIODS,
        case when CLASSMEETINGTEMPLATE.ID is null then 1 else 0 end as ADDRECORD
    from 
        (
            select
                TIMETABLEDAY.CYCLEDAYID,
                PATTERNBLOCKMEETING.STARTTIME,
                PATTERNBLOCKMEETING.ENDTIME,
                PATTERNBLOCKMEETING.TIMETABLEDAYPERIODID,
                PATTERNBLOCKMEETING.LENGTHINPERIODS
            from dbo.PATTERNBLOCKMEETING
                inner join dbo.TIMETABLEDAY on PATTERNBLOCKMEETING.TIMETABLEDAYID = TIMETABLEDAY.ID
            where PATTERNBLOCKMEETING.PATTERNBLOCKID = @PATTERNBLOCKID
        ) MEETINGS
        cross join dbo.CLASSMEETINGGROUP
        left join CLASSMEETINGTEMPLATE on
            CLASSMEETINGGROUP.ID = CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID and
            (
                (MEETINGS.STARTTIME is null and CLASSMEETINGTEMPLATE.STARTTIME is null) or
                (MEETINGS.STARTTIME = CLASSMEETINGTEMPLATE.STARTTIME)
            ) and
            (
                (MEETINGS.ENDTIME is null and CLASSMEETINGTEMPLATE.ENDTIME is null) or
                (MEETINGS.ENDTIME = CLASSMEETINGTEMPLATE.ENDTIME)
            ) and 
            MEETINGS.CYCLEDAYID = CLASSMEETINGTEMPLATE.CYCLEDAYID and
            (
                (MEETINGS.TIMETABLEDAYPERIODID is null and CLASSMEETINGTEMPLATE.TIMETABLEDAYPERIODID is null) or
                (MEETINGS.TIMETABLEDAYPERIODID = CLASSMEETINGTEMPLATE.TIMETABLEDAYPERIODID)
            ) and
            (
                (MEETINGS.LENGTHINPERIODS is null and CLASSMEETINGTEMPLATE.LENGTHINPERIODS is null) or
                (MEETINGS.LENGTHINPERIODS = CLASSMEETINGTEMPLATE.LENGTHINPERIODS)
            )
    where CLASSMEETINGGROUP.CLASSID = @CLASSID

    -- Remove any old entries

    delete from dbo.CLASSMEETINGTEMPLATE where CLASSMEETINGTEMPLATE.ID in
        (
            select CLASSMEETINGTEMPLATE.ID
            from dbo.CLASSMEETINGGROUP
                inner join dbo.CLASSMEETINGTEMPLATE on CLASSMEETINGGROUP.ID = CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID
            where dbo.CLASSMEETINGGROUP.CLASSID = @CLASSID

            except select ID from @TempTbl where ADDRECORD = 0
        )

    -- Add any new entries

    insert into dbo.CLASSMEETINGTEMPLATE
    (
        ID,
        CLASSMEETINGGROUPID,
        STARTTIME,
        ENDTIME,
        CYCLEDAYID,
        TIMETABLEDAYPERIODID,
        LENGTHINPERIODS,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED        
    )
    select
        ID,
        CLASSMEETINGGROUPID,
        STARTTIME,
        ENDTIME,
        CYCLEDAYID,
        TIMETABLEDAYPERIODID,
        LENGTHINPERIODS,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @TempTbl
    where ADDRECORD <> 0
end