USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUPLETTER

Add an appeal mailing setup letter.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CONSTITUENTRECORDTYPEID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@CHANNELCODE	tinyint	IN
@MAILEXPORTDEFINITIONID	uniqueidentifier	IN
@MAILCONTENTHTML	nvarchar(max)	IN
@EMAILEXPORTDEFINITIONID	uniqueidentifier	IN
@NETCOMMUNITYTEMPLATEID	int	IN
@NETCOMMUNITYDATASOURCEID	int	IN
@EMAILCONTENTHTML	nvarchar(max)	IN
@SENDTOOPTIONCODE	tinyint	IN
@CONSTITUENTINCLUDECODE	tinyint	IN
@CONSIDERREVENUEHISTORY	bit	IN
@REVENUECRITERIACODE	tinyint	IN
@LOWREVENUEAMOUNT	money	IN
@HIGHREVENUEAMOUNT	money	IN
@SELECTIONS	xml	IN
@CHANNELPREFERENCECODE	tinyint	IN
@MAILCOST	money	IN
@EMAILCOST	money	IN
@MARGINTOP	decimal(18, 0)	IN
@MARGINBOTTOM	decimal(18, 0)	IN
@MARGINLEFT	decimal(18, 0)	IN
@MARGINRIGHT	decimal(18, 0)	IN
@PAPERSIZECODE	tinyint	IN
@PAPERWIDTH	decimal(18, 0)	IN
@PAPERHEIGHT	decimal(18, 0)	IN
@MKTASKLADDERID	uniqueidentifier	IN
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUPLETTER
(
    @ID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CONSTITUENTRECORDTYPEID uniqueidentifier = null,
    @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
    set nocount on;

    if @ID is null
        set @ID = newid()

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

    declare @CURRENTDATE datetime = getDate();

    begin try        
        declare @EMAILMKTLETTERID uniqueidentifier;
        declare @EMAILPACKAGEID as uniqueidentifier;
        declare @EMAILSEGMENTID as uniqueidentifier;
        declare @MAILMKTLETTERID uniqueidentifier;
        declare @MAILPACKAGEID as uniqueidentifier;
        declare @MAILSEGMENTID as uniqueidentifier;

        declare @EXCLUDECONSTITSBASEDONPREFERENCE bit;
        declare @INCLUDECONSTITSWITHOUTPREFERENCE bit;

        declare @IDSETREGISTERID uniqueidentifier = null;

        declare @LETTERNAME nvarchar(100);
        declare @LETTERDESCRIPTION nvarchar(255);

        declare @PACKAGENAME nvarchar(100);
        declare @PACKAGEDESCRIPTION nvarchar(255);

        -- Setup selection

        if @SENDTOOPTIONCODE = 0
            -- Using canned include options, build a selection for the given values

            begin                                
                exec dbo.USP_APPEALMAILINGSETUPLETTER_BUILDCANNEDSELECTION
                    @IDSETREGISTERID output,
                    @CURRENTAPPUSERID,
                    @CHANGEAGENTID,
                    @CONSTITUENTRECORDTYPEID,
                    @CONSTITUENTINCLUDECODE,
                    @CONSIDERREVENUEHISTORY,
                    @REVENUECRITERIACODE,
                    @LOWREVENUEAMOUNT,
                    @HIGHREVENUEAMOUNT;

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

                        @IDSETREGISTERID as SELECTIONID,
                        'Canned Options Selection' as SELECTIONLABEL
                    for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64
                );
            end

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

        -- If sending email or both

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

                exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTLETTERCODE @EMAILMKTLETTERID output, 1, @CHANGEAGENTID, @LETTERNAME, @LETTERDESCRIPTION, @EMAILEXPORTDEFINITIONID, 0, '', null;

                -- Associate the email template with the LETTERCODE

                update dbo.LETTERCODE set
                    NETCOMMUNITYTEMPLATEID = @NETCOMMUNITYTEMPLATEID,
                    NETCOMMUNITYDATASOURCEID = @NETCOMMUNITYDATASOURCEID,
                    ISSYSTEM = 1
                where ID = @EMAILMKTLETTERID;

                -- Save the package

                set @PACKAGENAME = dbo.UFN_MKTPACKAGE_GETUNIQUENAME(left('Email Package: ' + @NAME, 100));
                set @PACKAGEDESCRIPTION = 'Automatically generated email package for ''' + @NAME + ''' appeal mailing setup 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 = @EMAILMKTLETTERID,
                    NETCOMMUNITYTEMPLATEID = @NETCOMMUNITYTEMPLATEID,
                    NETCOMMUNITYDATASOURCEID = @NETCOMMUNITYDATASOURCEID,
                    ISSYSTEM = 1
                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 = left('Mail Letter: ' + @NAME, 100);
                set @LETTERDESCRIPTION = 'Automatically generated mail letter for ''' + @NAME + ''' appeal mailing setup letter';

                --Save the letter

                exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTLETTERCODE @MAILMKTLETTERID output, 1, @CHANGEAGENTID, @LETTERNAME, @LETTERDESCRIPTION, @MAILEXPORTDEFINITIONID, 0, '', null;

                update dbo.LETTERCODE set
                    HTMLTEMPLATE = @MAILCONTENTHTML,
                    ISSYSTEM = 1
                where ID = @MAILMKTLETTERID;

                --Save the package

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

                exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTPACKAGE_MAIL @MAILPACKAGEID output, @CHANGEAGENTID, @PACKAGENAME, @PACKAGEDESCRIPTION, '', @MAILCOST, 0, null, null, @MAILMKTLETTERID, @MAILEXPORTDEFINITIONID, '255', null, '', null, @CURRENTAPPUSERID;

                update dbo.MKTPACKAGE set
                    ISSYSTEM = 1
                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

        insert into dbo.APPEALMAILINGSETUPLETTER 
            (ID, APPEALMAILINGSETUPID, SEQUENCE, NAME, CHANNELCODE, CHANNELPREFERENCECODE, SENDTOOPTIONCODE, CONSTITUENTINCLUDECODE, CONSIDERREVENUEHISTORY, REVENUECRITERIACODE, LOWREVENUEAMOUNT, HIGHREVENUEAMOUNT, MARGINTOP, MARGINBOTTOM, MARGINLEFT, MARGINRIGHT, PAPERSIZECODE, PAPERWIDTH, PAPERHEIGHT, MAILPACKAGEID, EMAILPACKAGEID, MAILSEGMENTID, EMAILSEGMENTID, CANNEDSELECTIONIDSETREGISTERID, MKTASKLADDERID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values
            (@ID, null, 1, @NAME, @CHANNELCODE, @CHANNELPREFERENCECODE, @SENDTOOPTIONCODE, @CONSTITUENTINCLUDECODE, @CONSIDERREVENUEHISTORY, @REVENUECRITERIACODE, @LOWREVENUEAMOUNT, @HIGHREVENUEAMOUNT, @MARGINTOP, @MARGINBOTTOM, @MARGINLEFT, @MARGINRIGHT, @PAPERSIZECODE, @PAPERWIDTH, @PAPERHEIGHT, @MAILPACKAGEID, @EMAILPACKAGEID, @MAILSEGMENTID, @EMAILSEGMENTID, @IDSETREGISTERID, @MKTASKLADDERID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        --exec dbo.USP_APPEALMAILINGSETUPLETTER_GETSELECTIONS_ADDFROMXML @ID, @SELECTIONS, @CHANGEAGENTID, @CURRENTDATE;


    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0;