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
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:
"monthsis 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