School onboarding report refresh

Description

There is no current connection from Google datastudio to the management information database so a manual extract needs to be performed and the resulting CSV extract uploaded in order to create a data refresh.

Caused by

A request to bring the Schools Onboarding dashboard up to date

Pre-requisits

You will need to have set up the Cloud Foundry command line
You will also need to sign in to Cloud foundry

Process

Run the following CLI command to target the PRODUCTION space;

cf target -o dfe -s early-careers-framework-prod

Run following CLI command to perform the extract and create a prod.csv file;

cf conduit ecf-postgres-production -- psql -c "\copy (SELECT s.name, s.urn, ne.sent_at, ne2.opened_at, ne.notify_status, (icp.id IS NOT NULL) AS induction_tutor_nominated, icp.created_at AS tutor_nominated_time, (u.current_sign_in_at IS NOT NULL) AS induction_tutor_signed_in, sc.induction_programme_choice, sc.created_at AS programme_chosen_time, (p.id IS NOT NULL) AS in_partnership, p.created_at AS partnership_time, p.challenge_reason AS partnership_challenge_reason, p.challenged_at AS partnership_challenge_time, lp.name AS lead_provider, dp.name AS delivery_partner, cip.name AS chosen_cip, sc.updated_at AS cip_chosen_time, (SELECT count(ectp.id) FROM early_career_teacher_profiles ectp WHERE ectp.school_id = s.id) AS ect_count, (SELECT count(mp.id) FROM mentor_profiles mp WHERE mp.school_id = s.id) AS mentor_count FROM schools s LEFT OUTER JOIN nomination_emails ne on s.id = ne.school_id LEFT OUTER JOIN nomination_emails ne2 on s.id = ne2.school_id LEFT OUTER JOIN induction_coordinator_profiles_schools icps on s.id = icps.school_id LEFT OUTER JOIN induction_coordinator_profiles icp on icps.induction_coordinator_profile_id = icp.id LEFT OUTER JOIN users u on icp.user_id = u.id LEFT OUTER JOIN school_cohorts sc on s.id = sc.school_id LEFT OUTER JOIN partnerships p on s.id = p.school_id LEFT OUTER JOIN core_induction_programmes cip on sc.core_induction_programme_id = cip.id LEFT OUTER JOIN lead_providers lp on p.lead_provider_id = lp.id LEFT OUTER JOIN delivery_partners dp on p.delivery_partner_id = dp.id WHERE s.school_status_code IN (1, 3) AND s.school_type_code IN (1, 2, 3, 5, 6, 7, 8, 12, 14, 15, 18, 28, 31, 32, 33, 34, 35, 36, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48) AND s.administrative_district_code ILIKE 'E%' AND (ne.id ISNULL OR ne.id IN (SELECT id FROM nomination_emails n WHERE s.id = n.school_id ORDER BY sent_at DESC LIMIT 1)) AND (ne2.id ISNULL OR ne2.id IN (SELECT id FROM nomination_emails n WHERE s.id = n.school_id ORDER BY opened_at ASC LIMIT 1))) to 'prod.csv' csv header"

Send the generated prod.csv file to our performance analyst.

If you do not have the conduit plug-in installed then it can be added like this:

cf install-plugin conduit