Create data migration scripts

[dougwaldron]

Mapping document: https://docs.google.com/spreadsheets/d/1WTvsZ9yWAT8XPJUiLBUHF6QZxzp4toFt84KDOX5bn0U/edit#gid=0

• LookupCounties
• LookupStates
• LookupBranches
• LookupConcerns
• LookupActionTypes
• Users (& User Roles)
• Complaints
• ComplaintActions
• ComplaintTransitions

Imported from JIRA: [CTS-21] Create data migration scripts (original by Douglas Waldron)

[dougwaldron]

• LookupCounties
• LookupStates
• LookupBranches
• LookupConcerns
• LookupActionTypes
• Complaints
• ComplaintActions
• ComplaintTransitions
• Users

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

[dougwaldron]

Done:

• LookupCounties
• LookupStates

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Counties/States'

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

[dougwaldron]

Done: LookupBranches

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

[dougwaldron]

Done: LookupConcerns

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

[dougwaldron]

Done: LookupActionTypes

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Concerns'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Action Types'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Branches/Districts'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: User Roles'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Users'

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

[dougwaldron]

Done: Users, User Roles

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

[dougwaldron]

What to do with accounts where email is empty, but user account is still active?

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

[dougwaldron]

What to do when caller info has been entered but labeled as anonymous? E.g.,

SELECT DISTINCT CALLER_FIRST_NAME
, CALLER_LAST_NAME
, CALLER_ADDRESS_01
, CALLER_CITY
, CALLER_ZIP
, CALLER_PRIMARY_PHONE
FROM EPD_COMP
WHERE lower (CALLER_FIRST_NAME) LIKE '%anon%'
OR lower (CALLER_LAST_NAME) LIKE '%anon%'
OR lower (CALLER_ADDRESS_01) LIKE '%anon%'
OR lower (CALLER_FIRST_NAME) LIKE '%mous%'
OR lower (CALLER_LAST_NAME) LIKE '%mous%' ;

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Complaint Actions'

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

[dougwaldron]

Questions and answers from Sirisha:

There is a “Resolved” column in the “EPD_COMP” table, but it doesn’t seem to correspond to the “Resolved” checkbox on the Actions tab. How is this column used?

It does correspond to ‘Resolved’ check box in Actions tab. However, there were many discussions about how this ‘Resolved’ field should be used. This was brought in from old client server system and some users thought that this should be auto checked as ‘Yes’ if the complaint is closed (closed date is not null), but nobody ever finalized this.

Similarly, the “CloseDate” column does not match the “Close Date” field.

It does match the ‘closedate’ field in ‘epd_comp’ table

What is the “Refer2” column used for?

This column is not used anywhere in the application. It was just brought in from the old system.

There is a table named COMP_ASSIGN, which seems to show a log of assignment changes. Is this data used anywhere in the application or is it just for auditing? In some complaints – for example Complaint # 82894 – the entries in COMP_ASSIGN do not match what is displayed for the complaint.

This data is used just for auditing and not used anywhere in the application. A database trigger named ‘assign_history’ is fired after each update of ‘epd_comp’ table to populate ‘comp_assign’ table. This functionality was also imported from old system into web based system.

The COMP_APPROVED table seems to track the complaint review/approval process, but what causes a complaint ID to be entered multiple times in this table? For example, complaint # 44592 has eight records in this table, some of which occurred after the complaint was approved.

This data is used just for auditing and not used anywhere in the application. A database trigger named ‘approve_history’ is fired after each update of ‘epd_comp’ table to populate ‘comp_approved’ table. This functionality was also imported from old system into web based system.

What is the APPLICANT_INFORMATION table used for?

This table is used to keep track of approval status of complaints and the data in this table corresponds to ‘Review’ tab from the application.

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

[dougwaldron]

More from Sirisha:

Regarding how we set the ‘Complaint Status’ here are some rules we used.

If Approved By is null

If Accept Date is null -> ‘New Assignment’
If Accept Date is not null and Investigation Date is null -> ‘Accepted’
If Investigation Date is not null -> ‘Investigated’
If Ten Days Follow up is True or Received Date is greater than 14 -> "Ten-Day Rule Violated"

---

If Approved By is not null

If Approval Status is ‘Not Approved’ -> ‘Not Approved’
If Approval Status is ‘Approved’ - > ‘Approved’
If Approval Status is ‘UNDEFINED’ -> ‘Review Pending’

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Complaints (WIP)'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Complaints (WIP)'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Remove anonymity column from data migration'

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

[dougwaldron]

Per Jim Cooley & Bert Langley, do not migrated previously deleted data.

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

[dougwaldron]

Previously deleted complaints:

What does null mean?

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

[dougwaldron]

Transitions:

• New
• Assigned
• SubmittedForReview
• ReturnedByReviewer
• Closed
• Reopened
• Deleted
• Restored

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Complaint Actions - fix deleted boolean'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Complaints - don't import deleted complaints'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Transitions - New'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Manually number State ID's'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Transitions - Assigned'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Improve consistency in created/updated shadow properties'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Remove unneeded set identity_insert statements'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Transitions - Review'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Update Assignment Transition Id'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration: Add Status to Complaint'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Add data migration table counts script'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Fix quote mark bugs; trim text fields'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Update Clear All Tables script'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Update Clear All Tables script'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Update data migration setup scripts'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Fix Complaint data migration output length issue'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Migrate entry date and user with Complaint Action'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration bug fixes'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Data migration bug fixes'

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

[dougwaldron]

File handling for complaints export:

• Save as TSV file with .sql extension
• Find and replace the following (replace with
• "\t" (quote, tab, quote)
• \t" (tab, quote)
• "\t (quote, tab)
• \t (tab)
• );"\n (close paren, semicolon, quote, newline)

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Better handling of tab/new lines in data migration'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Fix bug in Complaint Action data migration'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Fix phone type data migration bug'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Don't migrate county ID = 0'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Specify database in data migration scripts'

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

[dougwaldron]

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Increase space for comments in database'

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

[dougwaldron]

Latest data mapping spreadsheet:
CTS Data Mapping - Sheet1.pdf

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