USP_REPORT_ADDRESSVALIDATION

CREATE procedure dbo.USP_REPORT_ADDRESSVALIDATION
(
    @BATCHID uniqueidentifier = null,
    @CHANGED int = null,
    @REPORTUSERID nvarchar(128) = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
    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?CONSTITUENTID=' + convert(nvarchar(36),BATCH.CONSTITUENTID) as CONSTITUENTID,
    CONSTITUENT.NAME,
    CONSTITUENT.LOOKUPID,
    BATCH.ADDRESSBLOCK,
    BATCH.CITY,
    dbo.UFN_STATE_GETDESCRIPTION(BATCH.STATEID) as STATE,
    BATCH.POSTCODE,

    BATCH.NEWADDRESSBLOCK,
    BATCH.NEWCITY,
    dbo.UFN_STATE_GETDESCRIPTION(BATCH.NEWSTATEID) as NEWSTATE,
    BATCH.NEWPOSTCODE,    

    BATCH.VALIDATIONMESSAGE,
    BATCH.ADDRESSCHANGED,
    dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESS.ADDRESSTYPECODEID) as ADDRESSTYPECODE,
    ADDRESSVALIDATION.DATELASTRUN,
    (select count(ID) from dbo.BATCHADDRESSVALIDATION where BATCHID = @BATCHID) as ADDRESSESPROCESSED,
    (select count(ID) from dbo.BATCHADDRESSVALIDATION where BATCHID = @BATCHID and ADDRESSCHANGED = 1) as ADDRESSESUPDATED

from dbo.BATCHADDRESSVALIDATION BATCH
inner join dbo.ADDRESSVALIDATION on ADDRESSVALIDATION.BATCHID = BATCH.BATCHID
inner join dbo.DATATUNEUP DATA on DATA.ID = ADDRESSVALIDATION.DATATUNEUPID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = BATCH.CONSTITUENTID
left join dbo.ADDRESS on ADDRESS.ID = BATCH.ADDRESSID
where BATCH.BATCHID = @BATCHID and
    (convert(int, ADDRESSCHANGED) = @CHANGED or @CHANGED 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;