USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP_MINIMAL_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP_MINIMAL_2
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(255),
    @MAILDATE datetime,
    @APPEALID uniqueidentifier,
    @TASKS xml
)
as
begin
    declare @CURRENTDATE datetime = getDate();

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

    declare @TaskTempTbl table (
            [TASKID] uniqueidentifier, [SUBJECT] nvarchar(100), [NOTES] nvarchar(256), [STATUSCODE] tinyint,
            [DATEDUE] UDT_FUZZYDATE, [DATECOMPLETED] datetime, [OWNERID] uniqueidentifier
    );
    declare @RemindersTempTbl table ([TASKID] uniqueidentifier, [NAME] nvarchar(100),[SENDDATE] datetime, [SENT] bit);

    begin try
        update dbo.MKTSEGMENTATION set
            MKTSEGMENTATION.NAME = @NAME,
            MKTSEGMENTATION.DESCRIPTION = @DESCRIPTION,
            MKTSEGMENTATION.MAILDATE = @MAILDATE
        where MKTSEGMENTATION.ID = @ID;

        update dbo.APPEALMAILING set
            APPEALID = @APPEALID
        where ID = @ID;

        --Because the appeal mailing name uniqueness depends on the appeal, we need to check for name uniqueness after the appeal mailing is created.
        if dbo.[UFN_MKTSEGMENTATION_NAMEEXISTS](@NAME, 0, 1) = 1
            begin
                --Name is not unique, throw error.
                raiserror('BBERR_MKTSEGMENTATION_VALIDNAME', 13, 1);
            end

        insert into @TaskTempTbl
            select
                T.c.value('(TASKID)[1]', 'uniqueidentifier') as 'TASKID',
                T.c.value('(SUBJECT)[1]', 'nvarchar(100)') as 'SUBJECT',
                T.c.value('(NOTES)[1]', 'nvarchar(256)') as 'NOTES',
                T.c.value('(STATUSCODE)[1]', 'tinyint') as 'STATUSCODE',
                T.c.value('(DATEDUE)[1]', 'UDT_FUZZYDATE') as 'DATEDUE',
                T.c.value('(DATECOMPLETED)[1]', 'datetime') as 'DATECOMPLETED',
                T.c.value('(OWNERID)[1]', 'uniqueidentifier') as 'OWNERID'
            from @TASKS.nodes('/TASKS/ITEM') T(c);

        if @@ROWCOUNT > 0
            insert into @RemindersTempTbl
                select
                  t.c.value('(TASKID)[1]', 'uniqueidentifier') as 'TASKID',
                  t.c.value('(NAME)[1]', 'nvarchar(max)') as 'NAME',
                  t.c.value('(SENDDATE)[1]', 'datetime') as 'SENDDATE',
                  t.c.value('(SENT)[1]', 'bit') as 'SENT'
                  from @TASKS.nodes('/TASKS/ITEM/REMINDERS/ITEM') t(c);

        -- delete any tasks removed from the mailing        
        declare @TASKID uniqueidentifier;
        declare DELETETASKCURSOR cursor local fast_forward for
            select 
                APPEALMAILINGTASK.ID
            from dbo.APPEALMAILINGTASK
            left outer join @TaskTempTbl tasks
                on APPEALMAILINGTASK.ID = tasks.TASKID
            where APPEALMAILINGTASK.SEGMENTATIONID = @ID
                and tasks.TASKID is null;

        open DELETETASKCURSOR;
        fetch next from DELETETASKCURSOR into @TASKID;

        while (@@FETCH_STATUS = 0)
            begin
                exec dbo.USP_APPEALMAILINGTASK_DELETEBYID_WITHCHANGEAGENTID @TASKID, @CHANGEAGENTID;

                fetch next from DELETETASKCURSOR into @TASKID;
            end

        close DELETETASKCURSOR;
        deallocate DELETETASKCURSOR;

        declare @SUBJECT nvarchar(100);
        declare @NOTES nvarchar(255);
        declare @STATUSCODE tinyint;
        declare @DATEDUE dbo.UDT_FUZZYDATE;
        declare @DATECOMPLETED datetime;
        declare @OWNERID uniqueidentifier;
        declare @REMINDERS xml;

        declare TASKCURSOR cursor local fast_forward for
            select
                TASKID,
                SUBJECT,
                NOTES,
                STATUSCODE,
                DATEDUE,
                DATECOMPLETED,
                OWNERID
            from @TaskTempTbl;

        open TASKCURSOR;
        fetch next from TASKCURSOR into @TASKID, @SUBJECT, @NOTES, @STATUSCODE, @DATEDUE, @DATECOMPLETED, @OWNERID;

        while (@@FETCH_STATUS = 0)
            begin
                set @REMINDERS = (
                    select                         
                        REMINDERS.NAME as NAME,
                        REMINDERS.SENDDATE as SENDDATE,
                        coalesce(REMINDERS.SENT, 0) as SENT
                    from @RemindersTempTbl as REMINDERS
                    where REMINDERS.TASKID = @TASKID
                    for xml raw('ITEM'),type,elements,root('REMINDERS'),BINARY BASE64);

                if exists (select ID from dbo.APPEALMAILINGTASK where ID = @TASKID)
                    exec dbo.USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGTASK_2 @TASKID, @SUBJECT, @NOTES, @DATEDUE, @DATECOMPLETED, @STATUSCODE, @OWNERID, @CHANGEAGENTID, @CURRENTAPPUSERID, @REMINDERS;            
                else
                    exec dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGTASK @TASKID output,@ID,@SUBJECT,@NOTES,@DATEDUE,null,@STATUSCODE,@OWNERID,null,@CURRENTAPPUSERID,null,@REMINDERS;

                fetch next from TASKCURSOR into @TASKID, @SUBJECT, @NOTES, @STATUSCODE, @DATEDUE, @DATECOMPLETED, @OWNERID;
            end

        close TASKCURSOR;
        deallocate TASKCURSOR;
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end