Disabling Accounts of PV Patients

Bulk Lock Script

UPDATE pv_user
  SET locked = True, deleted = True
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 (
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
  	(
      	-- Scotland
		Z.code LIKE 'S%'
      	OR
      	-- Edinburgh
      	Z.code IN ('EDINHF1', 'RSC02')
      	OR
        -- Northern Ireland
        Z.code IN ('24027', '2020', '11023', '33020', '7021', '45021', '48021', '45020')
        OR
      	-- Birmingham Paeds
      	Z.code = 'RQ3'
      	OR
      	-- Bristol Paeds
      	Z.code = 'RA723'
      	OR
      	-- Leeds Paeds
      	Z.code = '99RQR13'
      	OR
      	-- Liverpool Paeds
      	Z.code = 'RBS25'
      	-- OR
      	-- Evelina (RJ122) Not on PV
      	OR
      	-- Manchester Paeds
      	Z.code = 'RW3RM'
      	-- OR
      	-- Newcastle Paeds (RTDO2) Not on PV
      	OR
      	-- Nottingham Paeds
      	Z.code = '99RCSLB'
      	OR
      	-- Southampton Paeds
      	Z.code = '99RHM01'
      	OR
      	-- Nottingham
      	Z.code IN ('RCSLB', 'RX1CC')
      	OR
      	-- IBD
      	Z.code = 'SALIBD'
      	OR
      	-- Diabetes
      	Z.code = 'DSF01'
)
)
AND
-- Exclude Paeds
age(now(), date_of_birth) >= '18 years'
AND
-- Get a Count of how many locked
locked = False;

Example Unlock Script

UPDATE pv_user
  SET locked = False, deleted = False
WHERE
	pv_user.id IN
(
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 IN ('24027', '2020', '11023', '33020', '7021', '45021', '48021', '45020')
)