USP_REPORT_PHONEFINDERUPDATE

create procedure dbo.USP_REPORT_PHONEFINDERUPDATE
(
    @BATCHID uniqueidentifier,
    @REPORTUSERID nvarchar(128) = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
as
    set nocount on;

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

    declare @TOTALCOUNT integer;
    declare @VERIFIEDCOUNT integer;
    declare @PHONEFORMAT nvarchar(20);

    select @PHONEFORMAT = dbo.UFN_PHONEFORMATCODE_GETFORMAT(COUNTRY.[PHONEFORMATCODE])
    from dbo.COUNTRY where COUNTRY.[ID] = dbo.UFN_COUNTRY_GETDEFAULT();

    /* Select out counts separately so that they aren't calculated for every row: */
    select
        @TOTALCOUNT = count(BATCHPHONEFINDER.[ID]),
        @VERIFIEDCOUNT =
        (
            select count(BATCHPHONEFINDER.[ID])
            from dbo.BATCHPHONEFINDER
            where BATCHPHONEFINDER.[BATCHID] = @BATCHID and
                BATCHPHONEFINDER.[PHONEVERIFIED] = 1
        )
    from
        dbo.BATCHPHONEFINDER
    where
        BATCHPHONEFINDER.[BATCHID] = @BATCHID;

    select 
        N'http://www.blackbaud.com?CONSTITUENTID=' + convert(nvarchar(36), BATCHPHONEFINDER.[CONSTITUENTID]) as [CONSTITUENTID],
        CONSTITUENT.[NAME] as [CONSTITUENTNAME],
        coalesce(PHONE.[NUMBER], N'N/A') as [PREVIOUSPHONENUMBER],
        BATCHPHONEFINDER.[PHONENUMBER] as [UPDATEDPHONENUMBER],
        case when BATCHPHONEFINDER.[DONOTCALLSTATUS] in (N'', N'H', N'D') then 0 else 1 end as [DONOTCALL],
        BATCHPHONEFINDER.[PHONEVERIFIED],
        @TOTALCOUNT as [TOTALCOUNT],
        @VERIFIEDCOUNT as [VERIFIEDCOUNT],
        PHONEFINDERGETPROCESS.[DATELASTRUN],
        @PHONEFORMAT as [PHONEFORMAT]
    from
        dbo.BATCHPHONEFINDER
        inner join dbo.PHONEFINDERGETPROCESS on PHONEFINDERGETPROCESS.[PHONEFINDERID] = BATCHPHONEFINDER.[PHONEFINDERID]
        inner join dbo.CONSTITUENT on CONSTITUENT.[ID] = BATCHPHONEFINDER.[CONSTITUENTID]
        left outer join dbo.PHONE on PHONE.[CONSTITUENTID] = BATCHPHONEFINDER.[CONSTITUENTID]
    where
        BATCHPHONEFINDER.[BATCHID] = @BATCHID and
        (PHONE.[ISPRIMARY] = 1 or PHONE.[ID] 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];

    return 0;