USP_WORKFLOWTASK_ASSIGNED_ALERT_SEND

CREATE procedure dbo.USP_WORKFLOWTASK_ASSIGNED_ALERT_SEND 
(
    @WORKFLOWTASKID uniqueidentifier
) 
as 
begin

    declare @ALERTTYPEID uniqueidentifier = '958D5708-39B4-4ECF-8DBD-E7A5CFED90A8'

    if not exists (select top(1) ID from dbo.DATABASEMAILSETTINGS where ENABLED = 1)  
         return 0;

    declare @EMAILBODYCONTEXTLINKPAGEID uniqueidentifier
    declare @EMAILSUBJECT nvarchar(255);  
    declare @EMAILBODY nvarchar(max);  
    declare @EMAILVIEWFORMINSTANCEID uniqueidentifier;  
    declare @BODYLINK nvarchar(2048);  

    select  
        @EMAILSUBJECT = EMAILSUBJECT,  
        @EMAILBODY = EMAILBODY,  
        @EMAILBODYCONTEXTLINKPAGEID = EMAILBODYCONTEXTLINKPAGEID,  
        @EMAILVIEWFORMINSTANCEID = EMAILVIEWFORMINSTANCEID  
    from  
        dbo.ALERTTYPE  
    where  
        ID = @ALERTTYPEID;  

    begin try  
        if @EMAILVIEWFORMINSTANCEID is not null  
            exec dbo.USP_ALERT_PERFORMFIELDSUBSTITUTION @EMAILBODY output, @EMAILSUBJECT output, @EMAILVIEWFORMINSTANCEID, @WORKFLOWTASKID, null;  
    end try  
    begin catch  
        -- do nothing, field substitution failing shouldn't prevent email from being sent  

    end catch  

    if @EMAILBODYCONTEXTLINKPAGEID is not null  
        set @BODYLINK = dbo.UFN_ALERT_GETAPPLICATIONLINK(@EMAILBODYCONTEXTLINKPAGEID, @WORKFLOWTASKID);  

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

    declare @EMAILADDRESSES table (APPUSERID uniqueidentifier, EMAILADDRESS nvarchar(100))


    --If the task has a specific owner then don't email the inbox, instead just email the specific owner.


    declare @OWNER_APPUSERID uniqueidentifier;

    select @OWNER_APPUSERID = OWNER_APPUSERID from dbo.BBWORKFLOWTASK where ID = @WORKFLOWTASKID;

    if @OWNER_APPUSERID is null
        begin
            insert into @EMAILADDRESSES
                select BBWORKFLOWINBOXASSIGNMENT.APPUSERID, APPUSERALERTSETTING.EMAILADDRESS from dbo.BBWORKFLOWTASK
                    inner join dbo.BBWORKFLOWINBOXASSIGNMENT on BBWORKFLOWTASK.BBWORKFLOWINBOXID = BBWORKFLOWINBOXASSIGNMENT.BBWORKFLOWINBOXID
                    inner join dbo.APPUSERALERTTYPESETTING on APPUSERALERTTYPESETTING.APPUSERID = BBWORKFLOWINBOXASSIGNMENT.APPUSERID
                    inner join dbo.APPUSERALERTSETTING on APPUSERALERTTYPESETTING.APPUSERID = APPUSERALERTSETTING.ID
                        and APPUSERALERTTYPESETTING.ALERTTYPEID = @ALERTTYPEID and APPUSERALERTTYPESETTING.ENABLED = 1
                    where BBWORKFLOWTASK.ID = @WORKFLOWTASKID
                    union all
                    select SYSTEMROLEAPPUSER.APPUSERID, APPUSERALERTSETTING.EMAILADDRESS from dbo.BBWORKFLOWTASK
                    inner join dbo.BBWORKFLOWINBOXASSIGNMENT on BBWORKFLOWTASK.BBWORKFLOWINBOXID = BBWORKFLOWINBOXASSIGNMENT.BBWORKFLOWINBOXID
                    inner join SYSTEMROLEAPPUSER on BBWORKFLOWINBOXASSIGNMENT.SYSTEMROLEID = SYSTEMROLEAPPUSER.SYSTEMROLEID
                    inner join dbo.APPUSERALERTTYPESETTING on APPUSERALERTTYPESETTING.APPUSERID = SYSTEMROLEAPPUSER.APPUSERID
                        and APPUSERALERTTYPESETTING.ALERTTYPEID = @ALERTTYPEID and APPUSERALERTTYPESETTING.ENABLED = 1
                    inner join dbo.APPUSERALERTSETTING on APPUSERALERTTYPESETTING.APPUSERID = APPUSERALERTSETTING.ID
                    where BBWORKFLOWTASK.ID = @WORKFLOWTASKID
        end
    else
        begin
            declare @USEREMAILADDRESS nvarchar(100);

            select @USEREMAILADDRESS =  APPUSERALERTSETTING.EMAILADDRESS from dbo.APPUSERALERTSETTING  
                                        inner join dbo.APPUSERALERTTYPESETTING on  APPUSERALERTSETTING.ID = APPUSERALERTTYPESETTING.APPUSERID                                
                                        where 
                                        APPUSERALERTTYPESETTING.APPUSERID = @OWNER_APPUSERID and APPUSERALERTTYPESETTING.ALERTTYPEID = @ALERTTYPEID and APPUSERALERTTYPESETTING.ENABLED = 1;


            if @USEREMAILADDRESS ='' set @USEREMAILADDRESS = null;

            if @USEREMAILADDRESS is not null                
                insert into @EMAILADDRESSES (APPUSERID,EMAILADDRESS) VALUES (@OWNER_APPUSERID,@USEREMAILADDRESS);
            else
                return 0;

        end

    declare @APPUSERID uniqueidentifier
    declare @EMAILADDRESS nvarchar(100)

    declare emailaddress_cursor cursor for select distinct APPUSERID, EMAILADDRESS from @EMAILADDRESSES

    open emailaddress_cursor;

    fetch next from emailaddress_cursor
    into @APPUSERID, @EMAILADDRESS;

    while @@FETCH_STATUS = 0
    begin

        --select @EMAILADDRESS, @EMAILSUBJECT, @EMAILBODY

        exec dbo.USP_ALERT_SEND @EMAILADDRESS, @EMAILSUBJECT, @EMAILBODY, 'TEXT', 'Normal';              

        fetch next from emailaddress_cursor
        into @APPUSERID, @EMAILADDRESS;
    end
    close emailaddress_cursor;
    deallocate emailaddress_cursor;

    return 0;
end