Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 6 Next »

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
        -- Wales
        Z.code IN ('RMW51', 'RRBBV', 'RKHA4', 'RKGA1', 'RQBAU', 'RQHC7', 'NHSWLS')
        OR
        -- Northern Ireland
        Z.code IN ('24027', '2020', '11023', '33020', '7021', '45021')
        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
        -- GOSH
        Z.code = 'RP4'
        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
        -- Ipswich
        Z.code = 'RGQ02'
        OR
        -- MSE
        Z.code IN ('RAJ', 'RAJ01', 'RQ8L0', 'RDDH0')
        OR
        -- Nottingham
        Z.code IN ('RCSLB', 'RX1CC')
        OR
        -- Bristol
        Z.code = 'REE01'
        OR
        -- Gloucester
        z.code = 'RH641'
        OR
        -- Preston
        Z.code = 'RMF01'
        OR
        -- IBD
        Z.code = 'SALIBD'
        OR
        -- Diabetes
        Z.code = 'DSF01'
)
AND
-- Exclude Paeds
age(now(), date_of_birth) >= '18 years'
)

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')
)

  • No labels