USP_DATAFORMTEMPLATE_EDIT_EVENTAPPEALS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_EVENTAPPEALS
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @APPEALS xml,
    @CURRENTAPPUSERID uniqueidentifier = null
) as
    set nocount on;

    declare @APPEALS_TABLE table (
        APPEALID uniqueidentifier,
        USERHASACCESS bit
    );

    insert into @APPEALS_TABLE 
        select
            T.appeals.value('(APPEALID)[1]', 'uniqueidentifier') as APPEALID,
            T.appeals.value('(USERHASACCESS)[1]', 'bit') as USERHASACCESS
        from
            @APPEALS.nodes('APPEALS/ITEM') T(appeals);


    -- Verify the user has access to the added appeals
    declare @DATAFORMINSTANCEID uniqueidentifier;
    set @DATAFORMINSTANCEID = '4C220F26-7903-4EA6-B9AB-8609CA77EB76';
    declare @ISADMIN bit;
    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

    if (@ISADMIN = 0)
        begin

            declare @EXISTING_APPEALS table (
                APPEALID uniqueidentifier
            );

            insert into @EXISTING_APPEALS
                select
                    APPEALID
                from
                    dbo.EVENTAPPEAL
                where
                    EVENTID = @ID;

            declare @EXISTINGCOUNT int = 0;
            declare @APPEALCOUNT int = 0;
            declare @JOINCOUNT int = 0;
            declare @CHECKSECURITY bit = 0;

            select @EXISTINGCOUNT = count(APPEALID) from @EXISTING_APPEALS;
            select @APPEALCOUNT = count(APPEALID) from @APPEALS_TABLE;
            if @EXISTINGCOUNT = @APPEALCOUNT
                begin
                    select @JOINCOUNT = count(A.APPEALID) from @APPEALS_TABLE A inner join @EXISTING_APPEALS E on E.APPEALID = A.APPEALID;
                    if @JOINCOUNT != @EXISTINGCOUNT
                        set @CHECKSECURITY = 1;
                end
            else if not(@EXISTINGCOUNT > @APPEALCOUNT) -- this assumes that the UIModel code will stop the rights restricted user from deleting an appeal they don't have rights to
                set @CHECKSECURITY = 1;

            if @CHECKSECURITY = 1
                begin
                    select 
                        top(1) APPEAL.ID
                    from 
                        dbo.APPEAL
                    inner join 
                        @APPEALS_TABLE A on A.APPEALID = APPEAL.ID
                    left join
                        @EXISTING_APPEALS E on E.APPEALID = A.APPEALID
                    where
                        dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, @DATAFORMINSTANCEID, APPEAL.SITEID) = 1 and
                        E.APPEALID is null

                    if @@ROWCOUNT = 0
                        begin
                            raiserror('BBERR_APPEAL_NOACCESS',13,1);
                            return 0;
                        end
                end
        end

    declare @CURRENCYMATCHTEST integer;
    declare @EVENTBASECURRENCYID uniqueidentifier;

    select
        @EVENTBASECURRENCYID = BASECURRENCYID
    from
        dbo.EVENT
    where
        ID = @ID;

    select 
        @CURRENCYMATCHTEST = sum(case when @EVENTBASECURRENCYID <> APPEAL.BASECURRENCYID then 1 else 0 end)
    from 
        @APPEALS_TABLE APPEALSFROMXML
    left join dbo.APPEAL on APPEAL.ID = APPEALSFROMXML.APPEALID;

    if @CURRENCYMATCHTEST > 0
        begin
            raiserror('BBERR_EVENTTOAPPEALBASEMISMATCH', 13, 1);
            return 1;
        end

    exec dbo.USP_EVENT_GETAPPEALS_UPDATEFROMXML @ID, @APPEALS, @CHANGEAGENTID;