Disabling Accounts of Non-Scottish Renal Patients

This assumes that all Scottish Units have a Code which starts with “S”. Need to confirm this.

This also needs testing to make sure that a locked account cannot be unlocked by a user doing their own password reset. Setting the deleted flag may be safer - if harder to reverse via the UI.

UPDATE pv_user SET locked = 1
WHERE
pv_user.id IN (
-- Patient
SELECT A.id
FROM
	pv_user A
INNER JOIN pv_user_group_role B ON a.id = B.user_id
WHERE
	B.role_id = 1
)
AND
pv_user.id NOT IN (
-- Scottish Patient
SELECT X.id
FROM
	pv_user X
INNER JOIN pv_user_group_role Y ON X.id = Y.user_id
INNER JOIN pv_group Z ON Y.group_id = Z.id
WHERE
	-- Patient
	Y.role_id = 1 AND
	-- Unit
	Z.type_id = 1 AND
	Z.code LIKE 'S%'
)