Admin Reports from JSON

[aarontitus]

Description

Gina needs reports on a regular basis that I need to run.
Feature: Create /admin/reports, with a list of reports that Gina (or any admin) can click and run. Create a JSON file I can update to create arbitrary csv outputs that will download as files:

{
  "reports": [
    {
      "name": "Recent Misplaced Worksites",
      "description": "A list of worksites that are probably misplaced, and the likely incident in which they belong.",
      "filename": "{todays_date}_misplaced_worksites.csv",
      "sql": "CALL wrong_incident(null); WITH q AS(SELECT incident_id, worksite_id, incident_name, date_trunc('day', worksite_created_at) - date_trunc('day', incident_start_at) AS time_after_incident, 'https://www.crisiscleanup.org/incident/' || incident_id || '/work/' || worksite_id AS url, incident_id_contained, incident_name_contained, date_trunc('day', worksite_created_at) - date_trunc('day', incident_contained_start_at) AS time_after_contained_incident FROM temp_ww_locations WHERE incident_id_contained IS NOT NULL ORDER BY incident_id, worksite_id) SELECT 'current_incident_id', 'worksite_id', 'current_incident_name', 'time_after_incident', 'url', 'incident_id_contained', 'move_to_this_incident', 'time_after_contained_incident' UNION ALL SELECT incident_id::text, worksite_id::text, incident_name, time_after_incident::text, url, incident_id_contained::text, incident_name_contained, time_after_contained_incident::text FROM q WHERE time_after_incident + INTERVAL '2 months' > time_after_contained_incident AND time_after_contained_incident < INTERVAL '2 months' ORDER BY 1 DESC, 6;",
      "sql_post": "DROP TABLE IF EXISTS temp_ww_locations;",
      "speed": "fast",
      "inputs": null
    },
    {
      "name": "List of Organizations and Primary Contacts",
      "description": "A list of all organizations and their primary contacts for a specified incident.",
      "filename": "{todays_date}_{incident_short_name}_primary_contacts.csv",
      "sql": "WITH q AS(SELECT DISTINCT(organization_id) AS organization_id, COUNT(DISTINCT(incident_id)) AS incident_count FROM organization_organizations_incidents WHERE approved_by IS NOT NULL GROUP BY organization_id) SELECT ooi.incident_id, ooi.organization_id, oo.name, llt.text AS access_level, q.incident_count, COUNT(ww.id) AS cases_reported, r.claimed_count, uu.first_name || ' ' || uu.last_name AS primary_contact, uu.email, uu.mobile, uu.current_sign_in_at AT TIME ZONE 'America/Chicago' AS last_sign_in FROM organization_organizations_incidents AS ooi LEFT JOIN organization_organizations AS oo ON ooi.organization_id = oo.id LEFT JOIN organization_organizations_roles AS oor ON ooi.organization_id = oor.organization_id LEFT JOIN organization_roles AS orr ON oor.org_role_id = orr.id LEFT JOIN language_localizations AS ll ON orr.name_t = ll.group_label LEFT JOIN language_localizations_text AS llt ON ll.id = llt.localization_id LEFT JOIN q ON ooi.organization_id = q.organization_id LEFT JOIN worksite_worksites AS ww ON ooi.incident_id = ww.incident_id LEFT JOIN user_users AS uu ON ooi.organization_id = uu.organization_id LEFT JOIN (SELECT work_type_claimed_by, COUNT(DISTINCT(worksite_id)) AS claimed_count FROM worksite_worksites_work_types_statuses_phases WHERE incident_id = {incident_id} AND invalidated_at IS NULL AND work_type_claimed_by IS NOT NULL GROUP BY work_type_claimed_by) AS r ON r.work_type_claimed_by = ooi.organization_id WHERE ooi.incident_id = {incident_id} AND ooi.approved_by IS NOT NULL AND ooi.invalidated_at IS NULL AND llt.language_id = 2 AND ww.reported_by = ooi.organization_id AND oor.invalidated_at IS NULL AND uu.id IN(SELECT DISTINCT(user_id) FROM user_users_roles WHERE user_role_id = 3 OR user_role_id = 4) GROUP BY ooi.incident_id, ooi.organization_id, oo.name, llt.text, q.incident_count, r.claimed_count, uu.last_name, uu.first_name, uu.email, uu.mobile, uu.current_sign_in_at ORDER BY oo.name, uu.last_name, uu.first_name;",
      "sql_post": null,
      "speed": "fast",
      "inputs": [
        {
          "name": "incident_id",
          "type": "integer"
        }
      ]
    },
    {
      "name": "Recent Phone Volunteers",
      "description": "Contact information and stats for all volunteers who answered a call in the number of months specified.",
      "filename": "{todays_date}_FILENAME.csv",
      "sql": "WITH q AS(SELECT COUNT(pds.id) AS call_count, uu.first_name, uu.last_name, uu.email, uu.mobile, oo.name AS organization_name, ll1.name_t AS primary_language, ll2.name_t AS secondary_language, (uu.last_login AT TIME ZONE 'America/Chicago')::date AS last_login_central_time FROM user_users AS uu LEFT JOIN phone_dnis_statuses AS pds ON uu.id = pds.created_by LEFT JOIN organization_organizations AS oo ON uu.organization_id = oo.id LEFT JOIN language_languages AS ll1 ON uu.primary_language_id = ll1.id LEFT JOIN language_languages AS ll2 ON uu.secondary_language_id = ll2.id WHERE uu.id IN(SELECT DISTINCT(created_by) FROM phone_dnis_statuses WHERE created_at >= NOW() - INTERVAL '{months} months' --WHERE created_at >= '2024-05-01') AND pds.created_at >= NOW() - INTERVAL '{months} months' --AND pds.created_at >= '2024-05-01' GROUP BY uu.first_name, uu.last_name, uu.email, uu.mobile, oo.name, ll1.name_t, ll2.name_t, uu.last_login ORDER BY 6, 3, 2 --ORDER BY oo.name, uu.last_name, uu.first_name;) SELECT 'Call Count', 'First Name',  'Last Name', 'Email', 'Mobile', 'Organization', 'Primary Language', 'Secondary Language', 'Last Login' UNION ALL SELECT q.call_count::text, q.first_name, q.last_name, q.email, q.mobile, q.organization_name, q.primary_language, q.secondary_language, q.last_login_central_time::text FROM q;",
      "sql_post": null,
      "speed": "fast",
      "inputs": [
        {
          "name": "months",
          "type": "integer"
        }
      ]
    },
    {
      "name": "NAME",
      "description": "DESCRIPTION",
      "filename": "{todays_date}_FILENAME.csv",
      "sql": "SQL",
      "sql_post": "SQL",
      "speed": "SPEED",
      "inputs": [
        {
          "name": "INPUT",
          "type": "TYPE"
        }
      ]
    }
  ]
}

• name: The name of the report
• description: A description of the report.
• filename: The filename of the resulting csv output
• sql: The raw SQL that will run to generate the output
• sql_post: Raw sql that needs to run AFTER the output generates, e.g. cleaning up any temporary tables
• speed: "fast" or "slow." If "fast," just run the report immediately and download it. If "slow," then hand it off to a worker and download the finished CSV file from "Downloads."
• order: The order the report should appear on the screen.
• inputs: an optional list of inputs that will be replaced in the SQL. If the input is "months is an integer, then replace {months} in the "sql" object with whatever integer the user inputs.

Download the column names as the first row in the CSV file

Screenshots

Steps to Reproduce

Tasks