FFE - JS task to query DB for directory structure and queue the Python task

[as1729]

Story: #3803

Requirements

1. Add a module in the javascript code arpa_reporter/services/arpaExporter.js that queries the database for upload metadata and location within the full-file-export zipfile.
2. The module must also dump the results of the query into a CSV file which is uploaded to S3 in the following location: ARPAExport/${organizationId}/FullFileExport_Metadata_MMDDYYYY.HH.MM.SS.csv

CSV headers should be as follows:

upload_id, filename_in_zip, directory_location, agency_name, ec_code, reporting_period_name, validity
...
...

3. The module must then kick-off a Python task that handles the zipping of the upload-files and subsequent uploading of the zip-file to S3.

Message to the python task must be as follows:

{
  s3: {
    bucket: 'arpa-audit-reports',
    zip_key: 'ARPAExport/{orgId}/archive.zip',
    metadata_key: 'ARPAExport/{orgId}/FullFileExport_Metadata_MMDDYYYY.HH.MM.SS.csv',
  },
  organization_id: {orgId},
  user_email: [email protected]
}

Step 1: Perform this database Query

/*
This query does the following:
1. Identify the validity of all the uploads for a given tenant.
2. For valid uploads check whether the upload belongs in the final treasury export or not.
3. Organize all uploads into a directory structure outlined in ticket 3803.
*/

WITH
	uploads_for_treasury_export AS (
		SELECT DISTINCT
			ON (u.reporting_period_id, u.agency_id, u.ec_code) u.id,
			u.created_at
		FROM
			uploads u
		WHERE
			u.tenant_id = 1
			AND u.validated_at IS NOT NULL
		ORDER BY
			u.reporting_period_id,
			u.agency_id,
			u.ec_code,
			u.created_at DESC
	)
SELECT
        u1.upload_id,
	SPLIT_PART(u1.filename, '.xlsm', 1) || '--' || u1.id || '.xlsm' AS filename_in_zip,
	CASE
		WHEN u1.invalidated_at IS NOT NULL THEN '/' || rp.name || '/Not Final Treasury/Invalid files/' || SPLIT_PART(u1.filename, '.xlsm', 1) || '--' || u1.id || '.xlsm'
		WHEN ue.id IS NOT NULL THEN '/' || rp.name || '/Final Treasury/' || SPLIT_PART(u1.filename, '.xlsm', 1) || '--' || u1.id || '.xlsm'
		WHEN u1.validated_at IS NOT NULL
		AND ue.id IS NULL THEN '/' || rp.name || '/Not Final Treasury/Valid files/' || SPLIT_PART(u1.filename, '.xlsm', 1) || '--' || u1.id || '.xlsm'
		ELSE NULL
	END AS directory_location,
	a.name AS agency_name,
	'EC' || u1.ec_code AS ec_code,
	rp.name AS reporting_period_name,
	CASE
		WHEN u1.invalidated_at IS NOT NULL THEN 'Invalidated at ' || invalidated_at || ' by ' || ui.email
		WHEN u1.validated_at IS NOT NULL THEN 'Validated at ' || validated_at || ' by ' || uv.email
		ELSE NULL
	END AS validity
FROM
	uploads u1
	LEFT JOIN uploads_for_treasury_export ue ON ue.id = u1.id
	LEFT JOIN users uv ON uv.id = u1.validated_by
	LEFT JOIN users ui ON ui.id = u1.invalidated_by
	JOIN reporting_periods rp ON rp.id = u1.reporting_period_id
	JOIN agencies a ON a.id = u1.agency_id
WHERE
	u1.tenant_id = 1
ORDER BY
	rp.id,
	ue.id,
	u1.validated_at ASC

Step 2: Kick-off the Python task by putting a message in an SQS queue.

• QueueName: process.env.ARPA_FULL_FILE_EXPORT_PY_SQS_QUEUE_URL
• MessageBody: JSON.stringify(...SEE REQUIREMENTS SECTION...)
• Example Code can be found here: https://github.com/usdigitalresponse/usdr-gost/blob/main/packages/server/src/arpa_reporter/routes/exports.js#L59-L77

Code Location

• https://github.com/usdigitalresponse/usdr-gost/tree/main/packages/server/src/arpa_reporter/services
  • Create a new file that is titled: fullFileExport.js in the services folder. This file will contain all the business logic outlined in the steps below.