USP_DATANETTEASER_SENDEMAIL

create procedure dbo.USP_DATANETTEASER_SENDEMAIL
(
  @APPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @EMAILTYPECODE tinyint
)
as
begin
  set nocount on;

  begin try

      declare @MAIL_RECIPIENTS nvarchar(max),
              @MAIL_SUBJECT nvarchar(255),
              @MAIL_BODY nvarchar(max),
              @MAIL_BODYFORMAT nvarchar(20), -- ['TEXT', 'HTML']

              @MAIL_IMPORTANCE nvarchar(6); -- ['Low', 'Normal', 'High']


    /*Have they received this email*/
    if not exists(select top 1 1 from dbo.DATANETTEASEREMAILTRACKING where APPUSERID = @APPUSERID and EMAILTYPECODE = @EMAILTYPECODE)
    begin

              select 
                @MAIL_SUBJECT = [SUBJECT],
                @MAIL_BODY = BODY,
                @MAIL_BODYFORMAT = BODYFORMAT,
                @MAIL_IMPORTANCE = IMPORTANCE
            from dbo.DATANETTEASEREMAILCONTENT
            where EMAILTYPECODE = @EMAILTYPECODE;

            select @MAIL_RECIPIENTS = EMAILADDRESS from dbo.appuser where ID = @APPUSERID;


            if (len(@MAIL_RECIPIENTS) > 0 and len(@MAIL_SUBJECT) > 0)
            begin

                          declare @CURRENTDATE datetime;
                          set @CURRENTDATE = getdate();

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

              exec [dbo].[USP_ALERT_SEND] 
                  @MAIL_RECIPIENTS,
                    @MAIL_SUBJECT,
                    @MAIL_BODY,
                    @MAIL_BODYFORMAT,
                    @MAIL_IMPORTANCE;

              insert into dbo.DATANETTEASEREMAILTRACKING(APPUSERID, EMAILTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              values(@APPUSERID, @EMAILTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

            end
      end

  end try

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


end