Sending Emails with queries - alphagov/notifications-manuals GitHub Wiki

In order to send emails to subsets of our users, developers have to run queries to generate csvs that are then passed on whoever is sending the emails. Notify is then used to send the emails.

Queries used for sending price change emails

Some of these queries use a temporary allowance table. This should be populated with the new allowance values like the following.

INSERT INTO allowance VALUES ('central', 30000), ('nhs_central', 30000), ('local', 10000), ('nhs_local', 10000), ('emergency_service', 10000), ('other', 5000), ('school_or_college', 5000);

WITH users_with_permission AS
(
       SELECT users.id,
              users.NAME,
              users.email_address,
              services.id AS service_id
       FROM   users 
       JOIN   permissions 
       ON     users.id = permissions.user_id
       AND    permissions.permission = 'manage_settings'
       JOIN   services
       ON     services.id = permissions.service_id
       WHERE  users.state = 'active'
       AND    services.active IS true ), 
nhs_gp_live_or_recent_trial AS
(
       SELECT services.id AS service_id,
              services.organisation_type
       FROM   services 
       WHERE  services.organisation_type = 'nhs_gp'
       AND    count_as_live IS true
       AND    restricted IS false
       OR     (
                     organisation_type = 'nhs_gp'
              AND    active IS true
              AND    created_at >= '2023-09-01' ) ),
eligible_users AS
(
                SELECT DISTINCT uwp.email_address,
                                uwp.NAME,
                                nhs_gp.organisation_type
                FROM            users_with_permission uwp
                JOIN            nhs_gp_live_or_recent_trial nhs_gp
                ON              uwp.service_id = nhs_gp.service_id )
SELECT *
FROM   eligible_users
) to '/Users/pea.tyczynska/Downloads/gp_surgeries.csv' delimiter ',' csv header;

WITH users_with_permission AS (
     SELECT u.id, u.name, u.email_address, s.id as service_id, s.name as service_name
     FROM users u
     JOIN permissions p on u.id = p.user_id AND p.permission = 'manage_settings'
     JOIN services s on s.id = p.service_id
     WHERE u.state = 'active'
     AND s.active IS true
 ),
 users_with_more_than_one_service_allowance AS (
     SELECT  * FROM (
     SELECT count(distinct a.allowance) as allowances_count, u.name, u.id from users as u
     JOIN permissions p on u.id = p.user_id
     join services as s on p.service_id = s.id
     join allowance as a on s.organisation_type=a.organisation_type
     where s.organisation_type != 'nhs_gp'
     AND s.count_as_live IS true
     AND s.restricted IS false
     group by u.id
      ) AS allowances WHERE allowances_count > 1
 ),
 eligible_users AS (
     SELECT DISTINCT uwp.id, uwp.email_address, uwp.name
     FROM users_with_permission uwp
     JOIN users_with_more_than_one_service_allowance uwmtosa on uwp.id = uwmtosa.id
     group by
     uwp.id,uwp.email_address, uwp.name, uwmtosa.allowances_count
 )
 SELECT * from eligible_users
) to '/Users/pea.tyczynska/Downloads/multiple_types.csv' delimiter ',' csv header;

WITH users_with_permission AS (
     SELECT DISTINCT u.id, u.name, u.email_address
     FROM users u
     JOIN permissions p on u.id = p.user_id AND p.permission = 'manage_settings'
     JOIN services s on s.id = p.service_id
     WHERE u.state = 'active'
     AND s.active IS true
     AND s.organisation_type != 'nhs_gp'
     AND s.restricted IS true
     AND u.logged_in_at > (now() - interval '6 months')::date
     
 )
 , eligible_users AS (
     SELECT uwp.*
     FROM users_with_permission uwp
     WHERE uwp.id not in  ( SELECT DISTINCT u.id
        FROM users u
        JOIN permissions p on u.id = p.user_id AND p.permission = 'manage_settings'
        JOIN services s on s.id = p.service_id
        WHERE u.state = 'active'
        AND s.active IS true
        AND s.organisation_type != 'nhs_gp'
        AND s.count_as_live IS true
        AND s.restricted IS false
     )
 )
  SELECT * from eligible_users
) to '/Users/pea.tyczynska/Downloads/trial_mode.csv' delimiter ',' csv header;

WITH users_with_permission AS (
     SELECT u.id, u.name, u.email_address, s.id as service_id, s.name as service_name
     FROM users u
     JOIN permissions p on u.id = p.user_id AND p.permission = 'manage_settings'
     JOIN services s on s.id = p.service_id
     WHERE u.state = 'active'
     AND s.active IS true
 ),
 sent_more_than_380k AS(
     select services.id as service_id, services.name as service_name,  coalesce(sum(billable_units ), 0) as units_used from services
     join annual_billing on services.id = annual_billing.service_id
     left outer join ft_billing on services.id = ft_billing.service_id and bst_date >= '2023-04-01'
     where services.active = 't'
     and services.restricted = 'f'
     and services.count_as_live = 't'
     and financial_year_start = 2023
     and (bst_date is null or bst_date >= '2023-04-01')
     and ft_billing.notification_type = 'sms'

     group by(services.id, annual_billing.free_sms_fragment_limit)
     having sum(billable_units) < 380000
     order by services.name
 
 ),
 users_with_one_service_allowance AS (
     SELECT  * FROM (
     SELECT count(distinct a.allowance) as num_allowance, max(a.allowance) as allowance, u.name, u.id from users as u
     JOIN permissions p on u.id = p.user_id
     join services as s on p.service_id = s.id
     join allowance as a on s.organisation_type=a.organisation_type
     where s.organisation_type != 'nhs_gp'
     group by u.id
      ) AS allowances WHERE num_allowance = 1
 ),
 eligible_users AS (
     SELECT DISTINCT uwp.id, uwp.email_address, uwp.name, uwosa.allowance, string_agg(DISTINCT uwp.service_name, ',')
     FROM users_with_permission uwp
     JOIN sent_more_than_380k sent ON uwp.service_id = sent.service_id
     JOIN users_with_one_service_allowance uwosa on uwp.id = uwosa.id
     group by
     uwp.id,uwp.email_address, uwp.name, uwosa.allowance
     ORDER BY uwp.name
 )
 SELECT * from eligible_users
) to '/Users/pea.tyczynska/Downloads/One_type_fewer_than_380k.csv' delimiter ',' csv header;

\copy (
WITH users_with_permission AS (
     SELECT u.id, u.name, u.email_address, s.id as service_id, s.name as service_name
     FROM users u
     JOIN permissions p on u.id = p.user_id AND p.permission = 'manage_settings'
     JOIN services s on s.id = p.service_id
     WHERE u.state = 'active'
     AND s.active IS true
 ),
 sent_more_than_380k AS(
     select services.id as service_id, services.name as service_name,  coalesce(sum(billable_units ), 0) as units_used from services
     join annual_billing on services.id = annual_billing.service_id
     left outer join ft_billing on services.id = ft_billing.service_id and bst_date >= '2023-04-01'
     where services.active = 't'
     and services.restricted = 'f'
     and services.count_as_live = 't'
     and financial_year_start = 2023
     and (bst_date is null or bst_date >= '2023-04-01')
     and ft_billing.notification_type = 'sms'

     group by(services.id, annual_billing.free_sms_fragment_limit)
     having sum(billable_units) >= 380000
     order by services.name
 
 ),
 users_with_one_service_allowance AS (
     SELECT  * FROM (
     SELECT count(distinct a.allowance) as num_allowance, max(a.allowance) as allowance, u.name, u.id from users as u
     JOIN permissions p on u.id = p.user_id
     join services as s on p.service_id = s.id
     join allowance as a on s.organisation_type=a.organisation_type
     where s.organisation_type != 'nhs_gp'
     group by u.id
      ) AS allowances WHERE num_allowance = 1
 ),
 eligible_users AS (
     SELECT DISTINCT uwp.id, uwp.email_address, uwp.name, uwosa.allowance, string_agg(DISTINCT uwp.service_name, ',')
     FROM users_with_permission uwp
     JOIN sent_more_than_380k sent ON uwp.service_id = sent.service_id
     JOIN users_with_one_service_allowance uwosa on uwp.id = uwosa.id
     group by
     uwp.id,uwp.email_address, uwp.name, uwosa.allowance
 )
 SELECT * from eligible_users
) to '/Users/pea.tyczynska/Downloads/One_type_more_than_380k.csv' delimiter ',' csv header;```

```\copy (WITH users_from_organisation AS (
     SELECT DISTINCT u.id, u.name, u.email_address
     FROM users u
     JOIN user_to_organisation uto on u.id = uto.user_id
     JOIN organisation o on o.id = uto.organisation_id
     WHERE u.state = 'active'
     AND o.active IS true
     AND o.organisation_type != 'nhs_gp'

 )
 , eligible_users AS (
     SELECT ufo.*
     FROM users_from_organisation ufo
     WHERE ufo.id not in  ( SELECT DISTINCT u.id
        FROM users u
        JOIN permissions p on u.id = p.user_id AND p.permission = 'manage_settings'
        JOIN services s on s.id = p.service_id
        WHERE u.state = 'active'
        AND s.active IS true
        AND s.organisation_type != 'nhs_gp'
        AND s.count_as_live IS true
     )
 )
  SELECT * from eligible_users

) to '/Users/will.pearson/Downloads/org_users_non_gp.csv' delimiter ',' csv header;```