USP_CLASSMEETING_CREATEFROMTEMPLATES

CREATE procedure dbo.USP_CLASSMEETING_CREATEFROMTEMPLATES
(
    @CLASSID 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 (
        [CLASSMEETINGID] uniqueidentifier,
        [CLASSMEETINGGROUPID] uniqueidentifier,
        [EDUCATIONMEETINGID] uniqueidentifier,
        [STARTDATE] date,
        [ENDDATE] date,
        [STARTTIME] char(4),
        [ENDTIME] char(4),
        ADDRECORD bit)

    insert into @TempTbl
    (
        CLASSMEETINGID,
        CLASSMEETINGGROUPID,
        EDUCATIONMEETINGID,
        STARTDATE,
        ENDDATE,
        STARTTIME,
        ENDTIME,
        ADDRECORD
    )
    select
        coalesce(CLASSMEETINGID, newid()),
        CLASSMEETINGGROUP.ID,
        case when CLASSMEETINGID is not null
            then EDUCATIONMEETINGID
            else newid()
        end,
        SCHEDULEDATE.DATE,
        SCHEDULEDATE.DATE,
        CLASSMEETINGTEMPLATE.STARTTIME,
        CLASSMEETINGTEMPLATE.ENDTIME,
        case when CLASSMEETINGID is null then 1 else 0 end as ADDRECORD
    from dbo.SCHEDULEDATE
        inner join dbo.TIMETABLEDAY on SCHEDULEDATE.TIMETABLEDAYID = TIMETABLEDAY.ID
        inner join dbo.COURSE on SCHEDULEDATE.SCHOOLID = COURSE.SCHOOLID
        inner join dbo.CLASS on COURSE.ID = CLASS.COURSEID
        inner join dbo.CLASSMEETINGGROUP on CLASS.ID = CLASSMEETINGGROUP.CLASSID
            and SCHEDULEDATE.DATE >= CLASSMEETINGGROUP.STARTDATE
            and SCHEDULEDATE.DATE <= CLASSMEETINGGROUP.ENDDATE
        inner join dbo.CLASSMEETINGTEMPLATE on CLASSMEETINGGROUP.ID = CLASSMEETINGTEMPLATE.CLASSMEETINGGROUPID
            and CLASSMEETINGTEMPLATE.CYCLEDAYID = TIMETABLEDAY.CYCLEDAYID
        left join 
            (select EDUCATIONMEETINGID, CLASSMEETING.ID CLASSMEETINGID, CLASSMEETINGGROUPID, STARTDATE, ENDDATE, STARTTIME, ENDTIME
                from dbo.EDUCATIONMEETING
                    left join dbo.CLASSMEETING on EDUCATIONMEETING.ID = CLASSMEETING.EDUCATIONMEETINGID) MEETING on 
            SCHEDULEDATE.DATE = MEETING.STARTDATE and
            SCHEDULEDATE.DATE = MEETING.ENDDATE and
            CLASSMEETINGTEMPLATE.STARTTIME = MEETING.STARTTIME and
            CLASSMEETINGTEMPLATE.ENDTIME = MEETING.ENDTIME and
            CLASSMEETINGGROUP.ID = MEETING.CLASSMEETINGGROUPID
    where SCHEDULEDATE.INSESSION <> 0 
        and CLASS.ID = @CLASSID

    delete from dbo.CLASSMEETING
    from dbo.CLASSMEETING
        inner join dbo.CLASSMEETINGGROUP on CLASSMEETING.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        left join @TempTbl MEETINGS on CLASSMEETING.ID = MEETINGS.CLASSMEETINGID
    where dbo.CLASSMEETINGGROUP.CLASSID = @CLASSID
        and MEETINGS.CLASSMEETINGID is null    

    insert into dbo.EDUCATIONMEETING
    (
        ID,
        STARTDATE,
        ENDDATE,
        STARTTIME,
        ENDTIME,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        EDUCATIONMEETINGID,
        STARTDATE,
        ENDDATE,
        STARTTIME,
        ENDTIME,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @TempTbl
    where ADDRECORD <> 0

    insert into dbo.CLASSMEETING
    (
        ID,
        CLASSMEETINGGROUPID,
        EDUCATIONMEETINGID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        CLASSMEETINGID,
        CLASSMEETINGGROUPID,
        EDUCATIONMEETINGID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @TempTbl
    where ADDRECORD <> 0
end