USP_REPORT_EMAILFINDER

CREATE procedure dbo.USP_REPORT_EMAILFINDER
(
    @BATCHID uniqueidentifier,
    @OPTOUT tinyint = null,
    @REPORTUSERID nvarchar(128) = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
begin
    set nocount on;

    declare @CURRENTAPPUSERID uniqueidentifier;
    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;
    declare @APPUSER_IN_NONSITEROLE bit;
    declare @APPUSER_IN_NOSITEROLE bit;

    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

    select 
        'http://www.blackbaud.com?CONSTITUENTID=' + convert(nvarchar(36), CONSTITUENT.ID) as CONSTITUENTID,
        CONSTITUENT.NAME,
        ADDRESS.ADDRESSBLOCK,
        ADDRESS.CITY,
        dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
        ADDRESS.POSTCODE,
        dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESS.ADDRESSTYPECODEID) as ADDRESSTYPE,
        BATCHEMAILFINDER.EMAILADDRESS,
        BATCHEMAILFINDER.OPTOUT,
        EMAILFINDER.DATELASTRUN,
        (select count(ID) from dbo.BATCHEMAILFINDER where BATCHID = @BATCHID) as PROCESSEDCOUNT,
        (select count(ID) from dbo.BATCHEMAILFINDER where BATCHID = @BATCHID and OPTOUT = 1) as OPTOUTCOUNT
    from 
        dbo.BATCHEMAILFINDER
    inner join
        dbo.EMAILFINDER on EMAILFINDER.ID = BATCHEMAILFINDER.EMAILFINDERID
    inner join
        dbo.ADDRESS on ADDRESS.ID = BATCHEMAILFINDER.ADDRESSID
    inner join
        dbo.CONSTITUENT on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
    where 
        BATCHEMAILFINDER.BATCHID = @BATCHID
        and (BATCHEMAILFINDER.OPTOUT = @OPTOUT or @OPTOUT is null)
        and (@ISADMIN = 1 or 
            (
                (@APPUSER_IN_NONRACROLE = 1 or
                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                and
                (@APPUSER_IN_NONSITEROLE = 1 or
                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
            ))
    order by 
        CONSTITUENT.NAME;

end