USP_DATAFORMTEMPLATE_EDIT_CLASS_MEETING_TEMPLATE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CLASS_MEETING_TEMPLATE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEETINGS xml
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    -- build a temporary table containing the values from the XML

    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,
        case when MEETINGS.TIMETABLEDAYPERIODID is null
            then MEETINGS.STARTTIME
            else ''
        end as STARTTIME,
        case when MEETINGS.TIMETABLEDAYPERIODID is null
            then MEETINGS.ENDTIME
            else ''
        end as ENDTIME,
        MEETINGS.CYCLEDAYID,
        MEETINGS.TIMETABLEDAYPERIODID,
        MEETINGS.LENGTHINPERIODS,
        case when CLASSMEETINGTEMPLATE.ID is null then 1 else 0 end as ADDRECORD
    from 
        (select
            T.c.value('(STARTTIME)[1]','char(4)') AS 'STARTTIME',
            T.c.value('(ENDTIME)[1]','char(4)') AS 'ENDTIME',
            T.c.value('(CYCLEDAYID)[1]','uniqueidentifier') AS 'CYCLEDAYID',
            T.c.value('(TIMETABLEDAYPERIODID)[1]','uniqueidentifier') AS 'TIMETABLEDAYPERIODID',
            T.c.value('(LENGTHINPERIODS)[1]','int') AS 'LENGTHINPERIODS'
        from @MEETINGS.nodes('/MEETINGS/ITEM') T(c)) 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 = @ID

    declare @RESETBLOCK bit = 0

    -- 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 = @ID

            except select ID from @TempTbl where ADDRECORD = 0
        )

    if @@ROWCOUNT > 0
        set @RESETBLOCK = 1

    -- Update the entries with valid times from periods if they are specified.

    update @TempTbl set
        STARTTIME = T.STARTTIME,
        ENDTIME = T.ENDTIME
    from @TempTbl MEETINGS
        cross apply dbo.UFN_CLASSMEETINGTEMPLATE_GETPERIODTIME(MEETINGS.TIMETABLEDAYPERIODID, MEETINGS.LENGTHINPERIODS) T
    where MEETINGS.TIMETABLEDAYPERIODID is not null

    if exists(select 1 from @TempTbl where STARTTIME = '' or ENDTIME = '')
    begin
        raiserror('BBERR_CLASSMEETINGTEMPLATE_INVALIDPERIODLENGTH', 13, 1);
        return 1;
    end

    -- 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

    if @@ROWCOUNT > 0 or @RESETBLOCK <> 0
    begin
        update dbo.CLASSMEETINGGROUP
            set PATTERNBLOCKID = null
        from dbo.CLASSMEETINGGROUP
        where CLASSMEETINGGROUP.CLASSID = @ID
            and PATTERNBLOCKID is not null
    end

    exec dbo.USP_CLASSMEETING_CREATEFROMTEMPLATES @ID, @CHANGEAGENTID

return 0;