USP_EVENTSEATING_SAVESEATINGLAYOUT

            CREATE procedure [dbo].[USP_EVENTSEATING_SAVESEATINGLAYOUT]
            (
                @EVENTSEATINGID uniqueidentifier,
                @SECTIONS xml,
                @SUBSECTIONS xml,
                @SEATS xml,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDDATE datetime
            )
            with execute as caller
            as
                set nocount on;

                -- Convert xml to a temp table so it only has to be deserialized once

                declare @SUBSECTIONTEMPTABLE as Table(ID uniqueidentifier,NAME nvarchar(100), SEQUENCE int, EVENTSEATINGSECTIONID uniqueidentifier)

                insert into @SUBSECTIONTEMPTABLE(ID, NAME, SEQUENCE, EVENTSEATINGSECTIONID)(select
                    T.c.value('(ID)[1]','uniqueidentifier'),
                    T.c.value('(NAME)[1]','nvarchar(100)'),
                    T.c.value('(SEQUENCE)[1]','int'),
                    T.c.value('(EVENTSEATINGSECTIONID)[1]','uniqueidentifier')
                from   
                    @SUBSECTIONS.nodes('/SUBSECTIONS/ITEM') T(c))



                -- Save sections

                exec dbo.USP_EVENTSEATING_GETSECTIONS_UPDATEFROMXML @EVENTSEATINGID, @SECTIONS, @CHANGEAGENTID, @CHANGEDDATE;

                -- Save sub-sections

                declare @SECTIONID uniqueidentifier;
                declare    @SECTIONSUBSECTIONS xml;    

                declare SectionsCursor cursor local fast_forward for 
                    select 
                        ID 
                    from 
                        dbo.UFN_EVENTSEATING_GETSECTIONS_FROMITEMLISTXML(@SECTIONS);

                open SectionsCursor;
                fetch next from SectionsCursor into @SECTIONID;

                while @@fetch_status = 0 begin
                    -- Get all of the sub-sections for this section and convert to xml

                    set @SECTIONSUBSECTIONS = (select ID, NAME, SEQUENCE from @SUBSECTIONTEMPTABLE where EVENTSEATINGSECTIONID = @SECTIONID for xml raw('ITEM'),type,elements,root('SUBSECTIONS'),BINARY BASE64)

                    -- Save sub-sections for this section

                    exec dbo.USP_EVENTSEATINGSECTION_GETSUBSECTIONS_UPDATEFROMXML @SECTIONID, @SECTIONSUBSECTIONS, @CHANGEAGENTID, @CHANGEDDATE;

                    fetch next from SectionsCursor into @SECTIONID;
                end

                close SectionsCursor;
                deallocate SectionsCursor;

                -- Save seats    

                if object_id('tempdb..#EVENTSEATING_SAVESEATINGLAYOUT_SEATS') is not null
                    drop table #EVENTSEATING_SAVESEATINGLAYOUT_SEATS;

                select
                    T.c.value('(ID)[1]','uniqueidentifier') ID,
                    T.c.value('(NAME)[1]','nvarchar(100)') NAME,
                    T.c.value('(SEQUENCE)[1]','int') SEQUENCE,
                    T.c.value('(REGISTRANTID)[1]','uniqueidentifier') REGISTRANTID,
                    T.c.value('(EVENTSEATINGSUBSECTIONID)[1]','uniqueidentifier') EVENTSEATINGSUBSECTIONID
                into #EVENTSEATING_SAVESEATINGLAYOUT_SEATS
                from    
                    @SEATS.nodes('/SEATS/ITEM') T(c)


                delete from dbo.[EVENTSEATINGSEAT] 
                where [EVENTSEATINGSEAT].ID in   
                (
                     select ESSEAT.ID 
                        from dbo.[EVENTSEATINGSEAT] ESSEAT
                        inner join dbo.EVENTSEATINGSUBSECTION ESSUB on ESSEAT.EVENTSEATINGSUBSECTIONID = ESSUB.ID
                        inner join dbo.EVENTSEATINGSECTION ESSECTION on ESSUB.EVENTSEATINGSECTIONID = ESSECTION.ID
                        where ESSECTION.EVENTSEATINGID = @EVENTSEATINGID
                     EXCEPT select ID from #EVENTSEATING_SAVESEATINGLAYOUT_SEATS
                );


                update dbo.EVENTSEATINGSEAT
                set REGISTRANTID = null
                from dbo.[EVENTSEATINGSEAT]
                inner join #EVENTSEATING_SAVESEATINGLAYOUT_SEATS SEATS
                    on EVENTSEATINGSEAT.ID = SEATS.ID
                where 
                    EVENTSEATINGSEAT.REGISTRANTID <> SEATS.REGISTRANTID or
                    SEATS.REGISTRANTID is null

                -- Update all fields

                update dbo.EVENTSEATINGSEAT
                set 
                    NAME = SEATS.NAME,
                    SEQUENCE = SEATS.SEQUENCE,
                    REGISTRANTID = SEATS.REGISTRANTID,
                    EVENTSEATINGSUBSECTIONID = SEATS.EVENTSEATINGSUBSECTIONID
                from dbo.[EVENTSEATINGSEAT]
                inner join #EVENTSEATING_SAVESEATINGLAYOUT_SEATS SEATS
                    on EVENTSEATINGSEAT.ID = SEATS.ID
                where 
                    ([EVENTSEATINGSEAT].[NAME] <> SEATS.[NAME]) or   
                    ([EVENTSEATINGSEAT].[REGISTRANTID]<>SEATS.[REGISTRANTID]) or   
                    ([EVENTSEATINGSEAT].[REGISTRANTID] is null and SEATS.[REGISTRANTID] is not null) or   
                    ([EVENTSEATINGSEAT].[REGISTRANTID] is not null and SEATS.[REGISTRANTID] is null) or   
                    ([EVENTSEATINGSEAT].[SEQUENCE]<>SEATS.[SEQUENCE]);

                --Insert new rows

                insert into dbo.[EVENTSEATINGSEAT]   
                 (
                    [EVENTSEATINGSUBSECTIONID],   
                    [ID],  
                    [NAME],  
                    [REGISTRANTID],  
                    [SEQUENCE],      
                    [ADDEDBYID],
                    [CHANGEDBYID],
                    [DATEADDED],
                    [DATECHANGED]
                )
                select 
                    [EVENTSEATINGSUBSECTIONID],
                    [ID],
                    [NAME],
                    [REGISTRANTID],
                    [SEQUENCE],
                    @CHANGEAGENTID,   
                    @CHANGEAGENTID,   
                    @CHANGEDDATE,   
                    @CHANGEDDATE  
                from #EVENTSEATING_SAVESEATINGLAYOUT_SEATS as SEATS  
                where not exists (select ID from dbo.[EVENTSEATINGSEAT] as data where data.ID = SEATS.ID)                  

                -- Do some validation 

                if exists(select NAME from dbo.EVENTSEATINGSECTION where ID = @SECTIONID group by NAME having count(NAME) > 1)
                    raiserror('Duplicate section names have been defined.',13,1);

                if exists(select NAME from dbo.EVENTSEATINGSUBSECTION group by NAME, EVENTSEATINGSECTIONID having count(NAME) > 1)
                    raiserror('Duplicate sub-section names have been defined.  The sub-section name must be unique in each section.',13,1);

                if exists(select NAME from dbo.EVENTSEATINGSEAT group by NAME, EVENTSEATINGSUBSECTIONID having count(NAME) > 1) 
                    raiserror('Duplicate seat names have been defined.  The seat name must be unique in each sub-section.',13,1); 

                if exists(select REGISTRANTID from dbo.EVENTSEATINGSEAT where REGISTRANTID is not null group by REGISTRANTID, EVENTSEATINGSUBSECTIONID having count(REGISTRANTID) > 1) 
                    raiserror('A registrant has been assigned to multiple seats.',13,1);