USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP_2
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,    
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(255) = '',
    @MAILDATE datetime = null,    
    @APPEALID uniqueidentifier,
    @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
    @NAMEFORMATPARAMETERID uniqueidentifier = null,
    @HOUSEHOLDINGTYPECODE tinyint = 0,
    @CREATEOUTPUTIDSET bit = 0,
    @OUTPUTIDSETNAME nvarchar(100) = '',
    @OVERWRITEOUTPUTIDSET bit = 0
)
as
begin

    declare    @CURRENTDATE datetime;

    begin try
        set @CURRENTDATE = getDate();

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

        declare @ACTIVE bit = 0;
        declare @OLDAPPEALID uniqueidentifier;
        declare @OLDHOUSEHOLDINGTYPECODE tinyint;
        declare @OLDNAME nvarchar(100);
        declare @OLDDESCRIPTION nvarchar(255);
        declare @OLDMAILDATE datetime;
        declare @OLDADDRESSPROCESSINGOPTIONID uniqueidentifier;
        declare @OLDNAMEFORMATPARAMETERID uniqueidentifier;
        declare @OLDCREATEOUTPUTIDSET bit;
        declare @OLDOUTPUTIDSETNAME nvarchar(100);
        declare @OLDOVERWRITEOUTPUTIDSET bit;

        select
            @OLDAPPEALID = APPEALMAILING.APPEALID,
            @OLDHOUSEHOLDINGTYPECODE = APPEALMAILINGSETUP.HOUSEHOLDINGTYPECODE,
            @ACTIVE = MKTSEGMENTATION.ACTIVE,
            @OLDNAME = MKTSEGMENTATION.NAME,
            @OLDDESCRIPTION = MKTSEGMENTATION.DESCRIPTION,
            @OLDMAILDATE = MKTSEGMENTATION.MAILDATE,
            @OLDADDRESSPROCESSINGOPTIONID = MKTSEGMENTATION.ADDRESSPROCESSINGOPTIONID,
            @OLDNAMEFORMATPARAMETERID = MKTSEGMENTATION.NAMEFORMATPARAMETERID,
            @OLDCREATEOUTPUTIDSET = MKTSEGMENTATION.CREATEOUTPUTIDSET,
            @OLDOUTPUTIDSETNAME = MKTSEGMENTATION.OUTPUTIDSETNAME,
            @OLDOVERWRITEOUTPUTIDSET = MKTSEGMENTATION.OVERWRITEOUTPUTIDSET
        from dbo.APPEALMAILING
        left outer join dbo.APPEALMAILINGSETUP
            on APPEALMAILING.ID = APPEALMAILINGSETUP.ID
        left outer join dbo.MKTSEGMENTATION
            on APPEALMAILING.ID = MKTSEGMENTATION.ID
        where APPEALMAILING.ID = @ID;

        if @ACTIVE = 1 or dbo.UFN_MKTSEGMENTATION_HASACTIVESEGMENTS(@ID) = 1
            begin
                if @OLDAPPEALID <> @APPEALID or @OLDHOUSEHOLDINGTYPECODE <> @HOUSEHOLDINGTYPECODE or @OLDADDRESSPROCESSINGOPTIONID <> @ADDRESSPROCESSINGOPTIONID or @OLDNAMEFORMATPARAMETERID <> @NAMEFORMATPARAMETERID
                    begin
                        declare @DELETEAPPEALINFO bit = case when @APPEALID is null then 0 else 1 end;

                        exec dbo.USP_MKTSEGMENTATIONACTIVATE_ROLLBACK @ID, @CHANGEAGENTID, @DELETEAPPEALINFO, 1;
                    end
            end

        if @OLDNAME <> @NAME or @OLDDESCRIPTION <> @DESCRIPTION or @OLDMAILDATE <> @MAILDATE or @OLDHOUSEHOLDINGTYPECODE <> @HOUSEHOLDINGTYPECODE or @OLDADDRESSPROCESSINGOPTIONID <> @ADDRESSPROCESSINGOPTIONID 
            or @OLDNAMEFORMATPARAMETERID <> @NAMEFORMATPARAMETERID or @OLDCREATEOUTPUTIDSET <> @CREATEOUTPUTIDSET or @OLDOUTPUTIDSETNAME <> @OUTPUTIDSETNAME or @OLDOVERWRITEOUTPUTIDSET <> @OVERWRITEOUTPUTIDSET
            update dbo.[MKTSEGMENTATION] set
                [NAME] = @NAME,
                [DESCRIPTION] = @DESCRIPTION,
                [MAILDATE] = @MAILDATE,    
                [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] = @MAILDATE,
                [HOUSEHOLDINGONERECORDPERHOUSEHOLD] = case @HOUSEHOLDINGTYPECODE when 0 then 1 else 0 end,
                [ADDRESSPROCESSINGOPTIONID] = @ADDRESSPROCESSINGOPTIONID,
                [NAMEFORMATPARAMETERID] = @NAMEFORMATPARAMETERID,
                [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
                [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
                [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
            where ID = @ID;

        if @OLDAPPEALID <> @APPEALID
            begin
                -- Update appeal mailing information...

                update dbo.[APPEALMAILING] set
                    [APPEALID] = @APPEALID,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                where
                    [ID] = @ID;

                -- Update MKTSegmentationActivate appeal information

                declare @APPEALNAME nvarchar(100);
                declare @APPEALDESCRIPTION nvarchar(255);

                select
                    @APPEALNAME = [NAME],
                    @APPEALDESCRIPTION = [DESCRIPTION]
                from dbo.[APPEAL]
                where [ID] = @APPEALID;

                exec dbo.[USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD] @ID, @CHANGEAGENTID, 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0', null, null, @APPEALID, @APPEALNAME, @APPEALDESCRIPTION;                    
            end

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

        -- If not Enterprise, update AppealMailingSetup table

        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') = 0 And @OLDHOUSEHOLDINGTYPECODE <> @HOUSEHOLDINGTYPECODE
            begin
                if exists (select ID from dbo.APPEALMAILINGSETUP where ID = @ID)
                    update dbo.[APPEALMAILINGSETUP] set
                        [HOUSEHOLDINGTYPECODE] = @HOUSEHOLDINGTYPECODE,
                        [CHANGEDBYID] = @CHANGEAGENTID,
                        [DATECHANGED] = @CURRENTDATE                        
                    where
                        [ID] = @ID;         

                else
                    insert into dbo.[APPEALMAILINGSETUP]
                    ([ID], [MAILEXPORTDEFINITIONID], [EMAILEXPORTDEFINITIONID], [HOUSEHOLDINGTYPECODE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
                values
                    (@ID, dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(1, 0), dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(1, 1), @HOUSEHOLDINGTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

            end

    end try

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

    return 0;
end