gaepdit / complaint-tracking

Complaint Tracking System
The Unlicense
1 stars 3 forks source link

CSV data export #263

Closed dougwaldron closed 6 years ago

dougwaldron commented 6 years ago

A common (monthly?) GORA request follows. Sent by Laura Williams (formatting approximates original):

With respect to the report below, there are some changes that will have to be made given the nature of the information included in the request and exemptions from disclosure under GORA. We get such requests relatively frequently and when Sirisha was the person who handled them for us, we would send her the following email with instructions:

I have a request for updated copy of the CTS database. Preparation will be as before in Access— redacting certain records that are not subject to disclosure; requestor has agreed to pay charges, estimated as usual at 2 hours, and allowing 7 business days for receipt.

PROCESS:

Note certain fields are NOT subject to disclosure and must be redacted. The process we use to do that is detailed below.

1. Closed Records

Get all the records from ‘Complaint Tracking System’ (CTS) database where ‘approved date’ is not null and with phone number and email address fields redacted/removed. Except Phone Number and Email Address fields, this extract will have all the other fields from CTS database’s complaint table.

2. Open Records

Get all the records from CTS database where ‘approved_date’ is null and with phone number and email address fields redacted/removed. This pull will include only fields from ‘Caller’ and ‘Complaint’ sections of attached complaint summary pdf file.

3. “Complaint Actions”

will be provided ONLY for CLOSED records.

Both closed and open records will be extracted into Microsoft Access database as separate tables.

Thanks as always for your assistance.

Imported from JIRA: [CTS-295] CSV data export (original by Douglas Waldron)

dougwaldron commented 6 years ago

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Add queries for GORA request response'

by 557058:69608212-b764-4434-86ee-a573217c2d6e

dougwaldron commented 6 years ago

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Redact phone numbers, email addresses from GORA responses'

by 557058:69608212-b764-4434-86ee-a573217c2d6e

dougwaldron commented 6 years ago

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Add views for GORA requests'

by 557058:69608212-b764-4434-86ee-a573217c2d6e

dougwaldron commented 6 years ago

https://blog.stephencleary.com/2016/11/streaming-zip-on-aspnet-core.html

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

https://www.ryadel.com/en/create-zip-file-archive-programmatically-actionresult-asp-net-core-mvc-c-sharp/

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

Needs interim page with explanation, etc.

Still getting line breaks in Nature of Complaint converting to new rows in Excel

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Implement data exporter'

by 557058:69608212-b764-4434-86ee-a573217c2d6e

dougwaldron commented 6 years ago

Bad line breaks occur when field length exceeds 32,767 characters. Will need to limit exported field length (and add disclaimer?).

And here's an annoyance to remember: CSV code injection. I small risk, since all data is entered by EPD employees, but still...

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

And for some reason, the CSV files sometimes end up with a bunch of null characters (\x00) tacked on at the end!

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

The null characters were caused by this: https://stackoverflow.com/a/27269316/212978

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 6 years ago

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Finish data exporter'

by 557058:69608212-b764-4434-86ee-a573217c2d6e

dougwaldron commented 6 years ago

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Catch timeout errors in data exporter'

by 557058:69608212-b764-4434-86ee-a573217c2d6e