Report on patrons with an address that matches an address alerti

address_alert_query.sql

WITH all_user_addresses AS (
SELECT au.id as au_id, false as mailing, false as billing, aua.*
FROM actor.usr au
JOIN actor.usr_address aua ON (au.id = aua.usr)
UNION
SELECT au.id as au_id, true as mailing, false as billing, aua.*
FROM actor.usr au
JOIN actor.usr_address aua ON (au.mailing_address = aua.id)
UNION
SELECT au.id as au_id, false as mailing, true as billing, aua.*
FROM actor.usr au
JOIN actor.usr_address aua ON (au.billing_address = aua.id)
)
SELECT DISTINCT au.id, au.family_name, au.first_given_name, pgt.name as profile, aaua.street1, aaua.street2, aaua.city, (actor.address_alert_matches(au.home_ou, aaua.street1, aaua.street2, aaua.city, aaua.county, aaua.state, aaua.country, aaua.post_code, aaua.mailing, aaua.billing)).alert_message
FROM actor.usr au
JOIN permission.grp_tree pgt ON au.profile = pgt.id
JOIN all_user_addresses AS aaua ON (aaua.au_id = au.id)
WHERE au.home_ou IN (select id from actor.org_unit_descendants(22))
AND EXISTS (SELECT 1 FROM actor.address_alert_matches(au.home_ou, aaua.street1, aaua.street2, aaua.city, aaua.county, aaua.state, aaua.country, aaua.post_code, aaua.mailing, aaua.billing))
ORDER BY pgt.name, alert_message, aaua.city, aaua.street1, aaua.street2, au.id;