USP_DATAFORMTEMPLATE_ADD_MICROSITEEMAILTEMPLATE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MICROSITEEMAILTEMPLATE
(
    @ID int = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(184) = '',
    @CONTEXTTYPECODE tinyint = 0,
    @CONTEXTID uniqueidentifier = null
)
as

    set nocount on;

    if @ID is null
        set @ID = -1;

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

    declare @CURRENTDATE datetime = getdate();

    declare @DEFAULTEMAILTEMPLATEID int;

    select top 1
        @DEFAULTEMAILTEMPLATEID = EMAILTEMPLATEID
    from dbo.SHOPPINGCARTSETTINGS;

    declare @UserID int = 1;
    declare @ClientsID int;
    declare @Description nvarchar(255);
    declare @ContentHTML nvarchar(255);
    declare @ContentText nvarchar(255);
    declare @RecipientListsXML nvarchar(4000);
    declare @FromAddress nvarchar(255);
    declare @FromDisplayName nvarchar(255);
    declare @Subject nvarchar(255);
    declare @Priority int;
    declare @ReplyAddress nvarchar(255);
    declare @ReturnReceipt bit;
    declare @ReturnReceiptAddress nvarchar(255);
    declare @ForwardDSN bit;
    declare @ForwardDSNAddress nvarchar(255);
    declare @GUID uniqueidentifier = newID();
    declare @Type tinyint;
    declare @DataSourceID int;
    declare @ClientSitesID int;
    declare @AppealID int = null;
    declare @ExportDefinitionID uniqueidentifier = null;

    select
        @ClientsID = EmailTemplate.ClientsID,
        @FromAddress = EmailTemplate.FromAddress,
        @FromDisplayName = EmailTemplate.FromDisplayName,
        @Priority = EmailTemplate.Priority,
        @ReplyAddress = EmailTemplate.ReplyAddress,
        @ReturnReceipt = EmailTemplate.ReturnReceipt,
        @ReturnReceiptAddress = EmailTemplate.ReturnReceiptAddress,
        @ForwardDSN = EmailTemplate.ForwardDSN,
        @ForwardDSNAddress = EmailTemplate.ForwardDSNAddress,
        @Type = EmailTemplate.Type,
        @DataSourceID = EmailTemplate.DataSourceID,
        @ClientSitesID = EmailTemplate.ClientSitesID,
        @AppealID = EmailTemplate.AppealID,
        @ExportDefinitionID = EmailTemplate.ExportDefinitionID,
        @Subject = coalesce(EVENT.NAME, PROGRAM.NAME, MEMBERSHIPPROGRAM.NAME, LEVELPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME, DESIGNATION.VANITYNAME, COMBINATION.NAME, '')
    from dbo.EmailTemplate
    left outer join dbo.EVENT
        on EVENT.ID = @CONTEXTID
    left outer join dbo.PROGRAM
        on PROGRAM.ID = @CONTEXTID
    left outer join dbo.MEMBERSHIPPROGRAM
        on MEMBERSHIPPROGRAM.ID = @CONTEXTID
    left outer join dbo.MEMBERSHIPLEVEL
        on MEMBERSHIPLEVEL.ID = @CONTEXTID
    left outer join dbo.MEMBERSHIPPROGRAM LEVELPROGRAM
        on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = LEVELPROGRAM.ID
    left outer join dbo.DESIGNATION
        on DESIGNATION.ID = @CONTEXTID
    left outer join dbo.COMBINATION
        on COMBINATION.ID = @CONTEXTID
    where EmailTemplate.ID = @DEFAULTEMAILTEMPLATEID;

    begin try
        exec dbo.USP_EMAILTEMPLATE_ADDUPDATE
            @ID output,
            @UserID,
            @ClientsID,
            @Name,
            @Description,
            @ContentHTML,
            null,                -- RecipientsListsXML
            @ContentText,
            @FromAddress,
            @FromDisplayName,
            @Subject,
            @Priority,
            @ReplyAddress,
            @ReturnReceipt,
            @ReturnReceiptAddress,
            @ForwardDSN,
            @ForwardDSNAddress,
            @GUID,
            @Type,
            @DataSourceID,
            @ClientSitesID,
            @AppealID,
            @ExportDefinitionID;

        if @CONTEXTTYPECODE = 0
            insert into dbo.EVENT_MICROSITEEMAILTEMPLATE
                (ID, EVENTID, EMAILTEMPLATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (newID(), @CONTEXTID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        if @CONTEXTTYPECODE = 1
            insert into dbo.PROGRAM_MICROSITEEMAILTEMPLATE
                (ID, PROGRAMID, EMAILTEMPLATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (newID(), @CONTEXTID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        if @CONTEXTTYPECODE = 2
            insert into dbo.MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE
                (ID, MEMBERSHIPPROGRAMID, EMAILTEMPLATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (newID(), @CONTEXTID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        if @CONTEXTTYPECODE = 3
            insert into dbo.MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE
                (ID, MEMBERSHIPLEVELID, EMAILTEMPLATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (newID(), @CONTEXTID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        if @CONTEXTTYPECODE = 4
            insert into dbo.DESIGNATION_MICROSITEEMAILTEMPLATE
                (ID, DESIGNATIONID, EMAILTEMPLATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (newID(), @CONTEXTID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        if @CONTEXTTYPECODE = 5
            insert into dbo.COMBINATION_MICROSITEEMAILTEMPLATE
                (ID, COMBINATIONID, EMAILTEMPLATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (newID(), @CONTEXTID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    end try

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

    return 0;