USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUPLETTER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUPLETTER
(
    @ID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CONSTITUENTRECORDTYPEID uniqueidentifier,
    @NAME nvarchar(100),
    @CHANNELCODE tinyint,
    @MAILEXPORTDEFINITIONID uniqueidentifier,
    @MAILCONTENTHTML nvarchar(max),
    @EMAILEXPORTDEFINITIONID uniqueidentifier,
    @NETCOMMUNITYTEMPLATEID int,
    @NETCOMMUNITYDATASOURCEID int,
    @EMAILCONTENTHTML nvarchar(max),
    @SENDTOOPTIONCODE tinyint,
    @CONSTITUENTINCLUDECODE tinyint,
    @CONSIDERREVENUEHISTORY bit,
    @REVENUECRITERIACODE tinyint,
    @LOWREVENUEAMOUNT money,
    @HIGHREVENUEAMOUNT money,
    @SELECTIONS xml,
    @CHANNELPREFERENCECODE tinyint,
    @MAILCOST money,
    @EMAILCOST money,
    @MARGINTOP decimal,
    @MARGINBOTTOM decimal,
    @MARGINLEFT decimal,
    @MARGINRIGHT decimal,
    @PAPERSIZECODE tinyint,
    @PAPERWIDTH decimal,
    @PAPERHEIGHT decimal,
    @MKTASKLADDERID uniqueidentifier
)
as
begin    
    declare @CURRENTDATE datetime = getDate();
    declare @SEGMENTATIONID uniqueidentifier;
    declare @ACTIVE bit;
    declare @APPEALID uniqueidentifier;
    declare @CANNEDSELECTIONIDSETREGISTERID uniqueidentifier;
    declare @OLDSENDTOOPTIONCODE tinyint;
    declare @OLDCONSTITUENTINCLUDECODE tinyint;
    declare @OLDCONSIDERREVENUEHISTORY bit;
    declare @OLDREVENUECRITERIACODE tinyint;
    declare @OLDLOWREVENUEAMOUNT money;
    declare @OLDHIGHREVENUEAMOUNT money;
    declare @OLDMKTASKLADDERID uniqueidentifier;
    declare @MAILPACKAGEID uniqueidentifier;
    declare @MAILSEGMENTID uniqueidentifier;
    declare @MAILLETTERCODEID uniqueidentifier;
    declare @EMAILPACKAGEID uniqueidentifier;
    declare @EMAILSEGMENTID uniqueidentifier;
    declare @EMAILLETTERCODEID uniqueidentifier;
    declare @SEGMENTATIONSEGMENTID uniqueidentifier;
    declare @EXCLUDECONSTITSBASEDONPREFERENCE bit;
    declare @INCLUDECONSTITSWITHOUTPREFERENCE bit;
    declare @LETTERNAME nvarchar(100);
    declare @LETTERDESCRIPTION nvarchar(255);
    declare @PACKAGENAME nvarchar(100);
    declare @PACKAGEDESCRIPTION nvarchar(255);

    begin try

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

        select
            @SEGMENTATIONID = APPEALMAILINGSETUPLETTER.APPEALMAILINGSETUPID,
            @ACTIVE = coalesce(MKTSEGMENTATION.ACTIVE, 0),
            @APPEALID = APPEALMAILING.APPEALID,
            @CANNEDSELECTIONIDSETREGISTERID = APPEALMAILINGSETUPLETTER.CANNEDSELECTIONIDSETREGISTERID,
            @MAILPACKAGEID = APPEALMAILINGSETUPLETTER.MAILPACKAGEID,
            @MAILSEGMENTID = APPEALMAILINGSETUPLETTER.MAILSEGMENTID,
            @MAILLETTERCODEID = MAILPACKAGE.LETTERCODEID,
            @EMAILPACKAGEID = APPEALMAILINGSETUPLETTER.EMAILPACKAGEID,
            @EMAILSEGMENTID = APPEALMAILINGSETUPLETTER.EMAILSEGMENTID,
            @EMAILLETTERCODEID = EMAILPACKAGE.LETTERCODEID,
            @OLDSENDTOOPTIONCODE = APPEALMAILINGSETUPLETTER.SENDTOOPTIONCODE,
            @OLDCONSTITUENTINCLUDECODE = APPEALMAILINGSETUPLETTER.CONSTITUENTINCLUDECODE,
            @OLDCONSIDERREVENUEHISTORY = APPEALMAILINGSETUPLETTER.CONSIDERREVENUEHISTORY,
            @OLDLOWREVENUEAMOUNT = APPEALMAILINGSETUPLETTER.LOWREVENUEAMOUNT,
            @OLDHIGHREVENUEAMOUNT = APPEALMAILINGSETUPLETTER.HIGHREVENUEAMOUNT,
            @OLDMKTASKLADDERID = APPEALMAILINGSETUPLETTER.MKTASKLADDERID    
        from dbo.APPEALMAILINGSETUPLETTER
        left join dbo.APPEALMAILING
            on APPEALMAILINGSETUPLETTER.APPEALMAILINGSETUPID = APPEALMAILING.ID
        left join dbo.MKTSEGMENTATION
            on APPEALMAILING.ID = MKTSEGMENTATION.ID
        left join dbo.MKTPACKAGE MAILPACKAGE
            on APPEALMAILINGSETUPLETTER.MAILPACKAGEID = MAILPACKAGE.ID
        left join dbo.MKTPACKAGE EMAILPACKAGE
            on APPEALMAILINGSETUPLETTER.EMAILPACKAGEID = EMAILPACKAGE.ID

        where APPEALMAILINGSETUPLETTER.ID = @ID;

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

        -- Delete segment and segmentation segments for Channels no longer included in the mailing

        if @CHANNELCODE = 1 and @MAILPACKAGEID is not null and @MAILSEGMENTID is not null
            begin        
                set @SEGMENTATIONSEGMENTID = null;

                select
                    @SEGMENTATIONSEGMENTID = ID
                from dbo.MKTSEGMENTATIONSEGMENT
                where SEGMENTID = @MAILSEGMENTID
                    and PACKAGEID = @MAILPACKAGEID;

                update dbo.APPEALMAILINGSETUPLETTER set
                    MAILPACKAGEID = null,
                    MAILSEGMENTID = null
                where ID = @ID;

                if @SEGMENTATIONSEGMENTID is not null
                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_DELETE @SEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                update dbo.MKTSEGMENT set
                    ISSYSTEM = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @MAILSEGMENTID;
                exec dbo.USP_MKTSEGMENT_DELETE @MAILSEGMENTID, @CHANGEAGENTID, @CURRENTAPPUSERID;

                update dbo.MKTPACKAGE set
                    ISSYSTEM = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @MAILPACKAGEID;
                exec dbo.USP_MKTPACKAGE_DELETE @MAILPACKAGEID, @CHANGEAGENTID;

                update dbo.LETTERCODE set
                    ISSYSTEM = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @MAILLETTERCODEID;
                exec dbo.USP_LETTERCODE_DELETE @MAILLETTERCODEID, @CHANGEAGENTID;

                set @MAILSEGMENTID = null;
                set @MAILPACKAGEID = null;
                set @MAILLETTERCODEID = null;
            end

        if @CHANNELCODE = 2 and @EMAILPACKAGEID is not null and @EMAILSEGMENTID is not null
            begin    
                set @SEGMENTATIONSEGMENTID = null;

                select
                    @SEGMENTATIONSEGMENTID = ID
                from dbo.MKTSEGMENTATIONSEGMENT
                where SEGMENTID = @EMAILSEGMENTID;

                update dbo.APPEALMAILINGSETUPLETTER set
                    EMAILPACKAGEID = null,
                    EMAILSEGMENTID = null
                where ID = @ID;

                if @SEGMENTATIONSEGMENTID is not null
                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_DELETE @SEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                update dbo.MKTSEGMENT set
                    ISSYSTEM = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @EMAILSEGMENTID;
                exec dbo.USP_MKTSEGMENT_DELETE @EMAILSEGMENTID, @CHANGEAGENTID, @CURRENTAPPUSERID;

                update dbo.MKTPACKAGE set
                    ISSYSTEM = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @EMAILPACKAGEID;
                exec dbo.USP_MKTPACKAGE_DELETE @EMAILPACKAGEID, @CHANGEAGENTID;

                update dbo.LETTERCODE set
                    ISSYSTEM = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @EMAILLETTERCODEID;
                exec dbo.USP_LETTERCODE_DELETE @EMAILLETTERCODEID, @CHANGEAGENTID;

                set @EMAILPACKAGEID = null;
                set @EMAILSEGMENTID = null;
                set @EMAILLETTERCODEID = null;
            end

        -- Using canned include options

        if @SENDTOOPTIONCODE = 0 
            begin

                if @SENDTOOPTIONCODE <> @OLDSENDTOOPTIONCODE
                    or @CONSTITUENTINCLUDECODE <> @OLDCONSTITUENTINCLUDECODE
                    or @CONSIDERREVENUEHISTORY <> @OLDCONSIDERREVENUEHISTORY
                    or @REVENUECRITERIACODE <> @OLDREVENUECRITERIACODE
                    or @LOWREVENUEAMOUNT <> @OLDLOWREVENUEAMOUNT
                    or @HIGHREVENUEAMOUNT <> @OLDHIGHREVENUEAMOUNT
                    or @CANNEDSELECTIONIDSETREGISTERID is null

                    exec dbo.USP_APPEALMAILINGSETUPLETTER_CREATEORUPDATECANNEDSELECTION
                        @CANNEDSELECTIONIDSETREGISTERID output,
                        @CURRENTAPPUSERID,
                        @CHANGEAGENTID,
                        @CONSTITUENTRECORDTYPEID,
                        @CONSTITUENTINCLUDECODE,
                        @CONSIDERREVENUEHISTORY,
                        @REVENUECRITERIACODE,
                        @LOWREVENUEAMOUNT,
                        @HIGHREVENUEAMOUNT;

                set @SELECTIONS = (
                    select
                        --newID() as ID,

                        @CANNEDSELECTIONIDSETREGISTERID as SELECTIONID,
                        'Canned Options Selection' as NAME
                    for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64
                );
            end    
        else
            if @CANNEDSELECTIONIDSETREGISTERID is not null
                begin                    
                    update dbo.APPEALMAILINGSETUPLETTER set
                        CANNEDSELECTIONIDSETREGISTERID = null
                    where ID = @ID;

                    -- Delete the segment selection records associated with the IDSetRegister

                    delete dbo.MKTSEGMENTSELECTION where SEGMENTID in (@EMAILSEGMENTID, @MAILSEGMENTID) and SELECTIONID = @CANNEDSELECTIONIDSETREGISTERID;

                    exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @CANNEDSELECTIONIDSETREGISTERID, @CHANGEAGENTID;

                    set @CANNEDSELECTIONIDSETREGISTERID = null;
                end

        set @EXCLUDECONSTITSBASEDONPREFERENCE =
            case @CHANNELCODE 
                when 0 then 1 
                else 0 
            end;

        -- If sending email or both

        if @CHANNELCODE <> 2
            begin            
                set @LETTERNAME = dbo.UFN_MKTPACKAGE_GETUNIQUENAME(left('Email Letter: ' + @NAME, 100));
                set @LETTERDESCRIPTION = 'Automatically generated email letter for ''' + @NAME + ''' appeal mailing letter'

                -- JNA Hack passing both new and old export definition ID's, which in my case are the same

                if @EMAILLETTERCODEID is null
                    exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTLETTERCODE @EMAILLETTERCODEID output, 1, @CHANGEAGENTID, @LETTERNAME, @LETTERDESCRIPTION, @EMAILEXPORTDEFINITIONID, 0, '', null, @EMAILEXPORTDEFINITIONID;
                else
                    exec dbo.USP_DATAFORMTEMPLATE_EDIT_MKTLETTERCODE @EMAILLETTERCODEID, @CHANGEAGENTID, @LETTERNAME, @LETTERDESCRIPTION, null, '', '', @EMAILEXPORTDEFINITIONID, 0, 0, @EMAILEXPORTDEFINITIONID;

                -- Associate the email template with the LETTERCODE

                update dbo.LETTERCODE set
                    NETCOMMUNITYTEMPLATEID = @NETCOMMUNITYTEMPLATEID,
                    NETCOMMUNITYDATASOURCEID = @NETCOMMUNITYDATASOURCEID,
                    ISSYSTEM = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @EMAILLETTERCODEID;

                -- Save the package

                set @PACKAGENAME = dbo.UFN_MKTPACKAGE_GETUNIQUENAME(left('Email Package: ' + @NAME, 100));
                set @PACKAGEDESCRIPTION = 'Automatically generated email package for ''' + @NAME + ''' appeal mailing letter';

                exec dbo.USP_MKTPACKAGE_EMAIL_SAVE @EMAILPACKAGEID output, @CHANGEAGENTID, @PACKAGENAME, @PACKAGEDESCRIPTION, '', null, @EMAILCOST, 0, null, @NETCOMMUNITYTEMPLATEID, @NETCOMMUNITYDATASOURCEID, @EMAILEXPORTDEFINITIONID, null, '', null, @CURRENTAPPUSERID;

                -- Associate the email template with the package

                update dbo.MKTPACKAGE set
                    LETTERCODEID = @EMAILLETTERCODEID,
                    NETCOMMUNITYTEMPLATEID = @NETCOMMUNITYTEMPLATEID,
                    NETCOMMUNITYDATASOURCEID = @NETCOMMUNITYDATASOURCEID,
                    ISSYSTEM = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @EMAILPACKAGEID;

                -- Create the segment

                set @INCLUDECONSTITSWITHOUTPREFERENCE =
                        case @CHANNELCODE
                            when 0 then
                                case @CHANNELPREFERENCECODE
                                    when 0 then 1
                                    else 0
                                end
                            when 1 then 1
                            else 0
                        end;

                exec dbo.[USP_COMMUNICATIONS_CREATEORUPDATESEGMENT_2]
                    @EMAILSEGMENTID output,
                    @CURRENTAPPUSERID,
                    @CHANGEAGENTID,
                    @NAME,
                    @SELECTIONS,
                    1,                                                        -- MailTypeCode 1 - Appeals

                    1,                                                        -- DeliveryMethodCode 1 - Email

                    @EXCLUDECONSTITSBASEDONPREFERENCE,
                    @INCLUDECONSTITSWITHOUTPREFERENCE;
            end

        -- If sending mail or both

        if @CHANNELCODE <> 1
            begin
                set @LETTERNAME = dbo.UFN_MKTPACKAGE_GETUNIQUENAME(left('Mail Letter: ' + @NAME, 100));
                set @LETTERDESCRIPTION = 'Automatically generated mail letter for ''' + @NAME + ''' appeal mailing letter';

                -- JNA Hack passing null for old export definition ID

                if @MAILLETTERCODEID is null
                    exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTLETTERCODE @MAILLETTERCODEID output, 1, @CHANGEAGENTID, @LETTERNAME, @LETTERDESCRIPTION, null, 1, '', null, @MAILEXPORTDEFINITIONID;
                else
                    exec dbo.USP_DATAFORMTEMPLATE_EDIT_MKTLETTERCODE @MAILLETTERCODEID, @CHANGEAGENTID, @LETTERNAME, @LETTERDESCRIPTION, null, '', '', null, 0, 0, @MAILEXPORTDEFINITIONID;

                update dbo.LETTERCODE set
                    HTMLTEMPLATE = @MAILCONTENTHTML,
                    ISSYSTEM = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @MAILLETTERCODEID;

                --Save the package

                set @PACKAGENAME = dbo.UFN_MKTPACKAGE_GETUNIQUENAME(left('Mail Package: ' + @LETTERNAME, 100));
                set @PACKAGEDESCRIPTION = 'Automatically generated mail package for ''' + @NAME + ''' appeal mailing letter';

                if @MAILPACKAGEID is null
                    exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTPACKAGE_MAIL @MAILPACKAGEID output, @CHANGEAGENTID, @PACKAGENAME, @PACKAGEDESCRIPTION, '', @MAILCOST, 0, null, null, @MAILLETTERCODEID, @MAILEXPORTDEFINITIONID, '255', null, '', null, @CURRENTAPPUSERID;
                else
                    exec dbo.USP_DATAFORMTEMPLATE_EDIT_MKTPACKAGE_MAIL @MAILPACKAGEID, @CHANGEAGENTID, @PACKAGENAME, @PACKAGEDESCRIPTION, '', null, @MAILCOST, 0, null, @MAILLETTERCODEID, @MAILEXPORTDEFINITIONID, null, '', null;

                update dbo.MKTPACKAGE set
                    ISSYSTEM = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @MAILPACKAGEID;

                set @INCLUDECONSTITSWITHOUTPREFERENCE = 
                        case @CHANNELCODE 
                            when 0 then 
                                case @CHANNELPREFERENCECODE 
                                    when 1 then 1 
                                    else 0 
                                end 
                            when 2 then 1 
                            else 0 
                        end;

                -- Create or update the segment

                exec dbo.[USP_COMMUNICATIONS_CREATEORUPDATESEGMENT_2]
                    @MAILSEGMENTID output,
                    @CURRENTAPPUSERID,
                    @CHANGEAGENTID,
                    @NAME,
                    @SELECTIONS,
                    1,                                                    -- MailTypeCode 1 - Appeals

                    0,                                                    -- DeliveryMethodCode 0 - Mail

                    @EXCLUDECONSTITSBASEDONPREFERENCE,
                    @INCLUDECONSTITSWITHOUTPREFERENCE;                                
            end

        update dbo.APPEALMAILINGSETUPLETTER set
            NAME = @NAME,
            CHANNELCODE = @CHANNELCODE,
            CHANNELPREFERENCECODE = @CHANNELPREFERENCECODE,
            SENDTOOPTIONCODE = @SENDTOOPTIONCODE, 
            CONSTITUENTINCLUDECODE = @CONSTITUENTINCLUDECODE, 
            CONSIDERREVENUEHISTORY = @CONSIDERREVENUEHISTORY, 
            REVENUECRITERIACODE = @REVENUECRITERIACODE, 
            LOWREVENUEAMOUNT = @LOWREVENUEAMOUNT, 
            HIGHREVENUEAMOUNT = @HIGHREVENUEAMOUNT, 
            MAILPACKAGEID = @MAILPACKAGEID, 
            EMAILPACKAGEID = @EMAILPACKAGEID, 
            MAILSEGMENTID = @MAILSEGMENTID, 
            EMAILSEGMENTID = @EMAILSEGMENTID, 
            CANNEDSELECTIONIDSETREGISTERID = @CANNEDSELECTIONIDSETREGISTERID,
            MARGINTOP = @MARGINTOP, 
            MARGINBOTTOM = @MARGINBOTTOM, 
            MARGINLEFT = @MARGINLEFT, 
            MARGINRIGHT = @MARGINRIGHT, 
            PAPERSIZECODE = @PAPERSIZECODE, 
            PAPERWIDTH = @PAPERWIDTH, 
            PAPERHEIGHT = @PAPERHEIGHT,
            MKTASKLADDERID = @MKTASKLADDERID,
            CHANGEDBYID = @CHANGEAGENTID, 
            DATECHANGED = @CURRENTDATE

        where ID = @ID;

        -- if the letter has already been linked to an appeal mailing

        --    update the segmentation segments for the mailing

        if @SEGMENTATIONID is not null
            if (@MKTASKLADDERID is null and @OLDMKTASKLADDERID is not null)
                or (@MKTASKLADDERID is not null and @OLDMKTASKLADDERID is null)
                or (@MKTASKLADDERID is not null and @OLDMKTASKLADDERID is not null and @MKTASKLADDERID <> @OLDMKTASKLADDERID)
                update dbo.MKTSEGMENTATIONSEGMENT set
                    ASKLADDERID = @MKTASKLADDERID
                where SEGMENTATIONID = @SEGMENTATIONID
                    and (
                        (PACKAGEID = @MAILPACKAGEID and SEGMENTID = @MAILSEGMENTID)
                        or (PACKAGEID = @EMAILPACKAGEID and SEGMENTID = @EMAILSEGMENTID)
                    )

    end try

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

    return 0;

end