USP_COMMUNICATIONS_CREATEORUPDATEMAILING_2

CREATE procedure dbo.USP_COMMUNICATIONS_CREATEORUPDATEMAILING_2
(
    @SEGMENTATIONID uniqueidentifier output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,    
    @COMMUNICATIONTYPECODE tinyint = 0,
    @MAILTYPECODE tinyint = 0,    
    @MAILINGNAME nvarchar(100) = '',
    @MAILINGDESCRIPTION nvarchar(100) = '',
    @MAILDATE datetime = null,
    @APPEALID uniqueidentifier = null,        
    @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
    @NAMEFORMATPARAMETERID uniqueidentifier = null,
    @HOUSEHOLDINGTYPECODE tinyint = 0,
    @MAILEXPORTDEFINITIONID uniqueidentifier = null,
    @EMAILEXPORTDEFINITIONID uniqueidentifier = null,
    @LETTERS xml = null,
    @CREATEOUTPUTIDSET bit = 0,
    @OUTPUTIDSETNAME nvarchar(100) = '',
    @OVERWRITEOUTPUTIDSET bit = 0,    
    @PACKAGESITEID uniqueidentifier = null, -- if set, overrides the appeal's site

    @SEGMENTATIONMAILTYPECODE tinyint = 0,
    @OPTIONEXCLUDEDSOLICITCODESXML xml = null
)
as
begin

    -- @COMMINICATIONTYPECODE - enum from MKTSegmentation table and CommunicationLetter table

    --        0 - Direct marketing effort

    --        1 - Appeal mailing

    --        2 - Event invitation

    --        3 - Acknowledgement

    --        4 - Reminders


    -- @MAILTYPECODE pertains to the MailTypeCode field in dbo.MAILPREFERENCE for constituent communication preferences

    --        0 - Revenue Acknowledgements

    --        1 - Appeals

    --        2 - Events

    --        3 - General Correspondence

    --        4 - Reminders

    --        5 - Receipts

    --        6 - Planned Gift Acknowledgements

    --        7 - Tribute Acknowledgements

    --        8 - Stewardship

    --        etc...    This code should not need to change as new types are introduced...


    -- @CHANNELCODE                0 - Mail, 1 - Email, 2 - Mail and Email

    -- @CHANNELPREFERENCECODE    0 - Email, 1 - Mail


    set nocount on;

    declare @CURRENTDATE datetime = getDate();

    if @SEGMENTATIONID is null
        set @SEGMENTATIONID = newID();

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

    begin try
        declare @ACTIVE bit;
        declare @APPEALNAME nvarchar(100) = null;
        declare @APPEALDESCRIPTION nvarchar(255) = null;
        declare @SITEID uniqueidentifier;
        declare @SEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
        declare @SEGMENTATIONEXPORTPROCESSID uniqueidentifier;    
        --declare @EXCLUSIONSCHANGED bit;

        declare @OLDEXCLUDEDECEASED as bit;
        declare @OLDEXCLUDEINACTIVE as bit;
        declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE as tinyint = 0;    -- Default = Process seasonal addresses as of today


        if @COMMUNICATIONTYPECODE = 1
            set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = 1;                -- Process seasonal addresses as of specific date (Mail date)


        select 
            @ACTIVE = [MKTSEGMENTATION].ACTIVE,
            @SEGMENTATIONACTIVATEPROCESSID = [MKTSEGMENTATIONACTIVATEPROCESS].[ID],
            @SEGMENTATIONEXPORTPROCESSID = [MKTSEGMENTATIONEXPORTPROCESS].[ID],
            @OLDEXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE]
        from dbo.[MKTSEGMENTATION]
        inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] 
            on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
        inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] 
            on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
        left join dbo.[BUSINESSPROCESSCOMMPREF] 
            on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
        where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

        if @PACKAGESITEID is null -- Retrieve the appeal information...

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

        -- Rollback active mailings to be re-activated

        if @ACTIVE = 1 or dbo.UFN_MKTSEGMENTATION_HASACTIVESEGMENTS(@SEGMENTATIONID) = 1
            begin
                declare @DELETEAPPEALINFO bit = case when @APPEALID is null then 0 else 1 end;

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

        -- Create or update the base mailing

        if @COMMUNICATIONTYPECODE = 2 or @COMMUNICATIONTYPECODE = 4
          set @MAILINGNAME = dbo.UFN_MKTSEGMENTATION_GETUNIQUENAME(@SEGMENTATIONID, @MAILINGNAME, default);

        declare @INCLUDEONERECORDPERHOUSEHOLD bit = case @HOUSEHOLDINGTYPECODE when 0 then 1 else 0 end;

        if not exists (select 1 from dbo.MKTSEGMENTATION where ID = @SEGMENTATIONID)
            -- Insert the base mailing...

            insert into dbo.[MKTSEGMENTATION]
            (
                [ID],
                [MAILINGTYPECODE],
                [COMMUNICATIONTYPECODE],
                [NAME],
                [DESCRIPTION],
                [MAILDATE],
                [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
                [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
                [SITEID],
                [HOUSEHOLDINGTYPECODE],
                [HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
                [HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
                [HOUSEHOLDINGONERECORDPERHOUSEHOLD],
                [USEADDRESSPROCESSING],
                [ADDRESSPROCESSINGOPTIONID],
                [NAMEFORMATPARAMETERID],
                [RUNACTIVATEANDEXPORT],
                [CREATEOUTPUTIDSET],
                [OUTPUTIDSETNAME],
                [OVERWRITEOUTPUTIDSET],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
            )
            values
            (
                @SEGMENTATIONID,
                @SEGMENTATIONMAILTYPECODE,
                @COMMUNICATIONTYPECODE,        
                @MAILINGNAME,
                @MAILINGDESCRIPTION,
                @MAILDATE,
                @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
                @MAILDATE,
                @SITEID,
                1,                                                                            -- HOUSEHOLDINGTYPECODE - Individuals and Orgs

                0,                                                                            -- HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD

                1,                                                                            -- HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS

                @INCLUDEONERECORDPERHOUSEHOLD,                                                -- HOUSEHOLDINGONERECORDPERHOUSEHOLD

                1,
                @ADDRESSPROCESSINGOPTIONID,
                @NAMEFORMATPARAMETERID,
                1,                                                                            -- RunActivateAndExport

                @CREATEOUTPUTIDSET,
                @OUTPUTIDSETNAME,
                @OVERWRITEOUTPUTIDSET,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

        else
            update dbo.[MKTSEGMENTATION] set
                [NAME] = @MAILINGNAME,
                [DESCRIPTION] = @MAILINGDESCRIPTION,
                [MAILDATE] = @MAILDATE,    
                [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] = @MAILDATE,
                [SITEID] = @SITEID,
--        --        [HOUSEHOLDINGTYPECODE] = @HOUSEHOLDINGTYPECODE,

--        --        [HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD] = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,

--        --        [HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS] = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,

                [HOUSEHOLDINGONERECORDPERHOUSEHOLD] = @INCLUDEONERECORDPERHOUSEHOLD,                                -- HOUSEHOLDINGONERECORDPERHOUSEHOLD

                [USEADDRESSPROCESSING] = 1,
                [ADDRESSPROCESSINGOPTIONID] = @ADDRESSPROCESSINGOPTIONID,
                [NAMEFORMATPARAMETERID] = @NAMEFORMATPARAMETERID,
                [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
                [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
                [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
            where 
                [ID] = @SEGMENTATIONID;    

        -- This offers exclusion from entire mailing, not per letter

        -- Save the excluded selections

        --exec dbo.USP_MKTSEGMENTATIONFILTERSELECTION_GETEXCLUDESELECTIONS_UPDATEFROMXML @SEGMENTATIONID, @EXCLUDESELECTIONS, @CHANGEAGENTID, @CURRENTDATE;


        -- Create budget information if it doesn't exist...

        --    Setting the actual values will be handled by the Revenue Calculator

        if not exists(select 1 from dbo.[MKTSEGMENTATIONBUDGET] where [ID] = @SEGMENTATIONID)
            insert into dbo.[MKTSEGMENTATIONBUDGET] 
                ([ID],[BUDGETAMOUNT], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
            values 
                (@SEGMENTATIONID, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        -- Add this mailing to the PreActivationProcess table

        if not exists(select 1 from dbo.[MKTMAILINGPREACTIVATIONPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)
            insert into dbo.[MKTMAILINGPREACTIVATIONPROCESS]
            (
                [ID],
                [SEGMENTATIONID],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
            ) 
            values
            (
                newid(),
                @SEGMENTATIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

        -- Add the mailing to the SegmentCalculationProcess table

        if not exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)
            exec dbo.[USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;

        -- Add the mailing to the SegmentRefreshProcess table 

        --        Appeal Mailings only

        if @COMMUNICATIONTYPECODE = 1
            if not exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENTREFRESHPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)
                exec dbo.[USP_MKTSEGMENTATIONSEGMENTREFRESHPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;

        -- Add the mailing to the ActivationProcess table...

        --if not exists(select 1 from dbo.[MKTSEGMENTATIONACTIVATEPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)

        if @SEGMENTATIONACTIVATEPROCESSID is null
            begin
                exec dbo.[USP_MKTSEGMENTATIONACTIVATEPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;

                -- Retrieve the activation process ID

                select
                    @SEGMENTATIONACTIVATEPROCESSID = ID
                from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
                where SEGMENTATIONID = @SEGMENTATIONID;
            end

        -- Add the effort to the EffortExclusionsProcess table 

        exec dbo.[USP_MKTSEGMENTATION_EFFORTEXCLUSIONSPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;

        if not exists(select 1 from dbo.[BUSINESSPROCESSCOMMPREF] where [BUSINESSPROCESSPARAMETERSETID] = @SEGMENTATIONACTIVATEPROCESSID)
            exec dbo.[USP_BUSINESSPROCESSCOMMPREF_ADD]
                null,
                @CHANGEAGENTID,
                '22C3D75C-A956-4BFC-A5FD-4B866BAEF509',
                @SEGMENTATIONACTIVATEPROCESSID,
                0,                                            -- DATETYPECODE - today    

                null,
                1,                                            -- ExcludeDeceased - Always exclude deceased

                0,                                            -- ExcludeInactive - Filter should occur at the letter level

                @OPTIONEXCLUDEDSOLICITCODESXML,                -- Exclusions - Solicit Code Exclusions must happen at the letter level    for appeal mailings        

                @CURRENTAPPUSERID;

        else
            begin            
                --set @EXCLUSIONSCHANGED = dbo.[UFN_MKTSEGMENTATION_COMPARECOMMPREFS](0, null, 1, 1, @EXCLUSIONS, @OLDEXCLUSIONDATETYPECODE, @OLDEXCLUSIONASOFDATE, @OLDEXCLUDEDECEASED, @OLDEXCLUDEINACTIVE, @OLDEXCLUSIONS);


                --if @EXCLUSIONSCHANGED = 1

                    exec dbo.[USP_BUSINESSPROCESSCOMMPREF_UPDATE]
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @BUSINESSPROCESSCATALOGID = '116332AF-BB79-4608-9709-4203BD2BA318',
                        @BUSINESSPROCESSPARAMETERSETID = @SEGMENTATIONACTIVATEPROCESSID,
                        @EXCLUSIONDATETYPECODE = 0,
                        @EXCLUSIONASOFDATE = null,
                        @EXCLUDEDECEASED = 1,
                        @EXCLUDEINACTIVE = 1,
                        @EXCLUSIONS = @OPTIONEXCLUDEDSOLICITCODESXML,
                        @CURRENTAPPUSERID = @CURRENTAPPUSERID;

            end

        -- Create or update the mailing export process...        

        if @SEGMENTATIONEXPORTPROCESSID is null
            begin
                set @SEGMENTATIONEXPORTPROCESSID = newid();

                insert into dbo.[MKTSEGMENTATIONEXPORTPROCESS] (
                    [ID],
                    [SEGMENTATIONID],
                    [DESCRIPTION],
                    [MAILEXPORTDEFINITIONID],
                    [EMAILEXPORTDEFINITIONID],
                    [PHONEEXPORTDEFINITIONID],
                    [ADDEDBYID],
                    [CHANGEDBYID],
                    [DATEADDED],
                    [DATECHANGED]
                ) values (
                    @SEGMENTATIONEXPORTPROCESSID,
                    @SEGMENTATIONID,
                    '',
                    @MAILEXPORTDEFINITIONID,                                
                    @EMAILEXPORTDEFINITIONID,                    
                    null,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                )
            end

        exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @SEGMENTATIONID;

        -- Save the activation source's information

        --    If the appeal fields do not apply to a particular mailing, 

        --        simply send in null for the Appeal field arguments

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

        exec dbo.USP_COMMUNICATIONS_CREATEORUPDATE_LETTERS @SEGMENTATIONID, @CURRENTAPPUSERID, @CHANGEAGENTID, @MAILTYPECODE, @MAILEXPORTDEFINITIONID, @EMAILEXPORTDEFINITIONID, @LETTERS;

    end try

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

    return 0;
end