USP_EMAIL_SENDTOAPPUSER

CREATE procedure dbo.USP_EMAIL_SENDTOAPPUSER 
    @APPUSERID uniqueidentifier,
    @EMAILSUBJECT nvarchar(255),
    @EMAILBODY nvarchar(max),
    @MAIL_IMPORTANCE nvarchar(6) = 'Normal', -- ['Low', 'Normal', 'High']

    @LOOKUP_APPUSERID_BY_CONSTITUENTID uniqueidentifier = null,
    @APPLICATIONLINK_PAGE_CONTEXTRECORDID nvarchar(255) = null,    -- A record id to use in building a page link

    @APPLICATIONLINK_PAGE_ID uniqueidentifier = null,    -- The ID of a page to build the link for.

    @NOSEND_REASONCODE int = null output,
    @USEALERTEMAILADDRESS bit = 1,
    @MAIL_BODYFORMAT nvarchar(20) = 'TEXT' -- ['TEXT', 'HTML']

    /*

    NOSENDREASONCODE:
    1=dbmail not configured
    2=no email address

    */


 as 

 /*
 Sends an email to the given APPUSERID or to the APPUSERID linked to the giving Constituent ID
 */

    -- return if alerts are not enabled

    if not exists (select top(1) ID from dbo.DATABASEMAILSETTINGS where ENABLED = 1)
    BEGIN
        set @NOSEND_REASONCODE=1;
        return 0;
    END

    if (@APPUSERID is null) or @APPUSERID='00000000-0000-0000-0000-000000000000'
    BEGIN

        if (@LOOKUP_APPUSERID_BY_CONSTITUENTID is null) or (@LOOKUP_APPUSERID_BY_CONSTITUENTID='00000000-0000-0000-0000-000000000000')
        BEGIN
            RAISERROR('dbo.USP_EMAIL_SENDTOAPPUSER requires a non null parameter value for @APPUSERID or @LOOKUP_APPUSERID_BY_CONSTITUENTID.  Both were null or an empty guid.',16,10)
            return 500;
        END

        select @APPUSERID=[ID] from dbo.APPUSER where CONSTITUENTID=@LOOKUP_APPUSERID_BY_CONSTITUENTID;

    END

    declare @EMAILADDRESS nvarchar(100);

    if @USEALERTEMAILADDRESS = 1
    begin
        set @EMAILADDRESS = dbo.UFN_APPUSER_GETALERTEMAILADDRESS(@APPUSERID);
    end
    else
    begin
        select @EMAILADDRESS = EMAILADDRESS from dbo.APPUSER where ID = @APPUSERID;
    end;

    -- return if the app user doesn't have an email address

    if (@EMAILADDRESS is null) or (@EMAILADDRESS=N'')
    BEGIN
        set @NOSEND_REASONCODE=2;
        return 0;
    END

    if @MAIL_IMPORTANCE is null
        set @MAIL_IMPORTANCE='Normal';

    if @MAIL_IMPORTANCE not in('Low', 'Normal', 'High')
        set @MAIL_IMPORTANCE='Normal';

    declare @BODYLINK nvarchar(2048);

    if @APPLICATIONLINK_PAGE_ID is not null
    BEGIN

    -- unfortunately the signature of UFN_ALERT_GETAPPLICATIONLINK requires a GUID id.

    -- eventually this will need to be deprecated with a version that takes a string id, for now do the conversion internal to this procedure

    -- so that later the signature for this procedure will not have to change when we deprecate UFN_ALERT_GETAPPLICATIONLINK with a new version that takes a proper string context record ID 


    declare @RECORDID uniqueidentifier;
    set @RECORDID = CAST(@APPLICATIONLINK_PAGE_CONTEXTRECORDID as uniqueidentifier);

        set @BODYLINK = dbo.UFN_ALERT_GETAPPLICATIONLINK(@APPLICATIONLINK_PAGE_ID,@RECORDID);

        if @BODYLINK is null
            set @BODYLINK='';

        set @EMAILSUBJECT = replace(@EMAILSUBJECT, '<<APPLICATIONLINK>>', @BODYLINK);

        set @EMAILBODY = replace(@EMAILBODY, '<<APPLICATIONLINK>>', @BODYLINK);

    END

    exec dbo.USP_ALERT_SEND @EMAILADDRESS, @EMAILSUBJECT, @EMAILBODY, @MAIL_BODYFORMAT, @MAIL_IMPORTANCE;

    return 0;