USP_DATAFORMTEMPLATE_EDITLOAD_APPEALMAILINGSETUPLETTER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_APPEALMAILINGSETUPLETTER
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
as
begin

    set nocount on;

    declare @CONSTITUENTRECORDTYPEID uniqueidentifier;
    declare @NETCOMMUNITYDATASOURCEID int = 0;
    declare @BBNCURL nvarchar(1024) = '';
    declare @EMAILMERGEFIELDBBNCINFO xml = '';
    declare @DEFAULTMAILEXPORTDEFINITIONID uniqueidentifier = '5BB7835A-2634-4B01-A4BC-832C87B99EA9';
    declare @DEFAULTEMAILEXPORTDEFINITIONID uniqueidentifier = '29A3B986-62EB-48B8-A93D-BEB43831EF68';
    declare @DEFAULTNETCOMMUNITYDATASOURCEID int = 0;

    select 
        @CONSTITUENTRECORDTYPEID = [ID] 
    from dbo.[RECORDTYPE] 
    where upper([NAME]) = 'CONSTITUENT';

    select
        @DEFAULTNETCOMMUNITYDATASOURCEID = NETCOMMUNITYDATASOURCEID
    from dbo.MKTEXPORTDEFINITION
    where EXPORTDEFINITIONID = @DEFAULTEMAILEXPORTDEFINITIONID;

    select
        convert(bit, 1) as DATALOADED,
        APPEALMAILINGSETUPLETTER.TSLONG,
        @CONSTITUENTRECORDTYPEID as CONSTITUENTRECORDTYPEID,
        (select [NAME] as GROUPNAME from dbo.[UFN_SELECTION_GETGROUPNAMES](@CONSTITUENTRECORDTYPEID) for xml raw('ITEM'),type,elements,root('SELECTIONLISTS'),binary base64) as SELECTIONLISTS,
        APPEALMAILINGSETUPLETTER.NAME,
        APPEALMAILINGSETUPLETTER.CHANNELCODE,
        APPEALMAILINGSETUPLETTER.CHANNELPREFERENCECODE,
        APPEALMAILINGSETUPLETTER.SENDTOOPTIONCODE,
        APPEALMAILINGSETUPLETTER.CONSTITUENTINCLUDECODE,
        APPEALMAILINGSETUPLETTER.CONSIDERREVENUEHISTORY,
        APPEALMAILINGSETUPLETTER.REVENUECRITERIACODE,
        APPEALMAILINGSETUPLETTER.LOWREVENUEAMOUNT,
        APPEALMAILINGSETUPLETTER.HIGHREVENUEAMOUNT,

        coalesce(EMAILPACKAGE.EXPORTDEFINITIONID, @DEFAULTEMAILEXPORTDEFINITIONID) as EMAILEXPORTDEFINITIONID,
        dbo.UFN_BBNC_URL() as BBNCURL,
        dbo.UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED() as NETCOMMUNITYLINKESTABLISHED,
        coalesce(EMAILEXPORTDEFINITION.NETCOMMUNITYDATASOURCEID, @DEFAULTNETCOMMUNITYDATASOURCEID) as NETCOMMUNITYDATASOURCEID,
        (
            select
                ID as FIELDID,
        -- have to determine this in code now

                --(select QUERYFIELD from dbo.MKTEXPORTDEFINITIONOUTPUTFIELD where EXPORTDEFINITIONID = coalesce(EMAILEXPORTDEFINITION.ID, @DEFAULTEMAILEXPORTDEFINITIONID) and NAME = BBDM_Fields.Name) as FIELDVALUE,

        '' as FIELDVALUE,
                Name as FIELDNAME
            from dbo.BBDM_Fields
            where BBDM_Fields.DataSourceID = coalesce(EMAILEXPORTDEFINITION.NETCOMMUNITYDATASOURCEID, @DEFAULTNETCOMMUNITYDATASOURCEID)
            for xml raw('ITEM'),type,elements,root('EMAILMERGEFIELDBBNCINFO'),binary base64
        ) as EMAILMERGEFIELDBBNCINFO,        

        EMAILPACKAGE.NETCOMMUNITYTEMPLATEID,        
        EMAILPACKAGE.UNITCOST as EMAILCOST,
    --    APPEALMAILINGSETUPLETTER.EMAILPACKAGEID,

    --    APPEALMAILINGSETUPLETTER.EMAILSEGMENTID,        

        EmailTemplate.Subject as EMAILSUBJECT,
        EmailTemplate.ContentHTML as EMAILCONTENTHTML,

        coalesce(MAILLETTER.EXPORTDEFINITIONID, @DEFAULTMAILEXPORTDEFINITIONID) as MAILEXPORTDEFINITIONID,
        MAILPACKAGE.UNITCOST as MAILCOST,        
    --    APPEALMAILINGSETUPLETTER.MAILPACKAGEID,

    --    APPEALMAILINGSETUPLETTER.MAILSEGMENTID,

        MAILLETTER.HTMLTEMPLATE as MAILCONTENTHTML,

    --    APPEALMAILINGSETUPLETTER.CANNEDSELECTIONIDSETREGISTERID,

        case
            when SENDTOOPTIONCODE = 1 then
                case
                    when APPEALMAILINGSETUPLETTER.CHANNELCODE = 0 
                        or APPEALMAILINGSETUPLETTER.CHANNELCODE = 1 
                            then dbo.UFN_MKTSEGMENT_GETSELECTIONS_TOITEMLISTXML(APPEALMAILINGSETUPLETTER.EMAILSEGMENTID, @CURRENTAPPUSERID)
                    else dbo.UFN_MKTSEGMENT_GETSELECTIONS_TOITEMLISTXML(APPEALMAILINGSETUPLETTER.MAILSEGMENTID, @CURRENTAPPUSERID)
                end
            else null
        end as SELECTIONS,

        APPEALMAILINGSETUPLETTER.MARGINTOP,
        APPEALMAILINGSETUPLETTER.MARGINBOTTOM,
        APPEALMAILINGSETUPLETTER.MARGINLEFT,
        APPEALMAILINGSETUPLETTER.MARGINRIGHT,
        APPEALMAILINGSETUPLETTER.PAPERSIZECODE,
        APPEALMAILINGSETUPLETTER.PAPERWIDTH,
        APPEALMAILINGSETUPLETTER.PAPERHEIGHT,
        APPEALMAILINGSETUPLETTER.MKTASKLADDERID

    from dbo.APPEALMAILINGSETUPLETTER
    left join dbo.MKTPACKAGE MAILPACKAGE
        on APPEALMAILINGSETUPLETTER.MAILPACKAGEID = MAILPACKAGE.ID
    left join dbo.LETTERCODE MAILLETTER
        on MAILPACKAGE.LETTERCODEID = MAILLETTER.ID
    left join dbo.MKTPACKAGE EMAILPACKAGE
        on APPEALMAILINGSETUPLETTER.EMAILPACKAGEID = EMAILPACKAGE.ID
    left join dbo.EmailTemplate
        on EMAILPACKAGE.NETCOMMUNITYTEMPLATEID = EmailTemplate.ID
    left join dbo.MKTEXPORTDEFINITION EMAILEXPORTDEFINITION
        on EMAILPACKAGE.EXPORTDEFINITIONID = EMAILEXPORTDEFINITION.EXPORTDEFINITIONID
    where APPEALMAILINGSETUPLETTER.ID = @ID;

    return 0;
end