Add County, Chapter Code and Chapter Name (by county) to the database to permit parsing and distribution of requests by Chapter #277

jim-mcgowan · 2019-02-19T19:23:52Z

Add counties to the database as first-class objects.

Chapter Codes/Chapter Names represent a collections of counties.

Store requests at the county level, even though the UI will be adding/subtracting requests at the Chapter level. This will give us flexibility for future finer-grained control of contacts.

See the attached spreadsheet for a sample listing of the counties by region (MOAK). For each County, the State, Region Code, Region Name, Chapter Code and Chapter Name are provided.

jim-mcgowan · 2019-02-19T19:24:24Z

MO-AK getasmokealarm Counties.xlsx

Counties no longer related directly to region to allow more fine grained control of which section of redcross they relate to. They now link to chapters, which should link to regions. This commit is only for the migration, with further commits related to the code to follow. Issue #277: Add County, Chapter Code and Chapter Name

This also required updating the code that assigns requests to regions to look at a counties chapters region code, and eagerly loading that information from the database. Issue #277: Add County, Chapter Code and Chapter Name

In order for requests to be linked through counties, to chapters, to regions so that we can do more granular searching later on, we need to actually save the country we find to the database instead of just the region code. Issue #277: Add County, Chapter Code and Chapter Name

Counties no longer related directly to region to allow more fine grained control of which section of redcross they relate to. They now link to chapters, which should link to regions. This commit is only for the migration, with further commits related to the code to follow. Issue #277: Add County, Chapter Code and Chapter Name

This also required updating the code that assigns requests to regions to look at a counties chapters region code, and eagerly loading that information from the database. Issue #277: Add County, Chapter Code and Chapter Name

In order for requests to be linked through counties, to chapters, to regions so that we can do more granular searching later on, we need to actually save the country we find to the database instead of just the region code. Issue #277: Add County, Chapter Code and Chapter Name

With requests needing to be linked through to regions via counties and chapters, we need a script to go back through and correctly populate all of the requests currently in the database. Issue #277: Add County, Chapter Code and Chapter Name

Also derive region information from the county to prepare for removing region from requests table. Clean up the table a little bit, and add a necessary relation so that sequelize makes the join correctly. Issue #277: Add County, Chapter Code and Chapter Name

frankduncan · 2019-02-21T18:38:54Z

I am also adding county and chapter to the request page and csv output.

This required also updated json2csv in order to query into sub objects. Issue #277: Add County, Chapter Code and Chapter Name

Because the region is now accessible through the county, we don't want to cache the information anymore in the request, because what regions make up requests may change as redcross changes. This commit refactors that field completely out. Issue #277: Add County, Chapter Code and Chapter Name

Issue #277: Add County, Chapter Code and Chapter Name

jim-mcgowan · 2019-02-22T00:04:59Z

Here is a spreadsheet listing all getasmokealarm counties. The first tab is a single list of ALL counties. The remaining tabs list counties by Region. Note that several counties are a part of Regions in adjoining states.
getasmokealarm Counties.xlsx

kfogel · 2019-02-22T04:47:21Z

@frankduncan: Official scope expansion here: let's get all this new data in -- I realize this affects the hours estimate, and that's okay. We do it now, and never have to do it again.

frankduncan · 2019-02-22T11:57:55Z

@kfogel Another potential additional feature would be a read only page of the current county/chapter/region configuration, accessible only for admins.

Counties no longer related directly to region to allow more fine grained control of which section of redcross they relate to. They now link to chapters, which should link to regions. This commit is only for the migration, with further commits related to the code to follow. Issue #277: Add County, Chapter Code and Chapter Name

This also required updating the code that assigns requests to regions to look at a counties chapters region code, and eagerly loading that information from the database. Issue #277: Add County, Chapter Code and Chapter Name

In order for requests to be linked through counties, to chapters, to regions so that we can do more granular searching later on, we need to actually save the country we find to the database instead of just the region code. Issue #277: Add County, Chapter Code and Chapter Name

With requests needing to be linked through to regions via counties and chapters, we need a script to go back through and correctly populate all of the requests currently in the database. Issue #277: Add County, Chapter Code and Chapter Name

Also derive region information from the county to prepare for removing region from requests table. Clean up the table a little bit, and add a necessary relation so that sequelize makes the join correctly. Issue #277: Add County, Chapter Code and Chapter Name

This required also updated json2csv in order to query into sub objects. Issue #277: Add County, Chapter Code and Chapter Name

Because the region is now accessible through the county, we don't want to cache the information anymore in the request, because what regions make up requests may change as redcross changes. This commit refactors that field completely out. Issue #277: Add County, Chapter Code and Chapter Name

Issue #277: Add County, Chapter Code and Chapter Name

Counties no longer related directly to region to allow more fine grained control of which section of redcross they relate to. They now link to chapters, which should link to regions. This commit is only for the migration, with further commits related to the code to follow. Issue #277: Add County, Chapter Code and Chapter Name

This also required updating the code that assigns requests to regions to look at a counties chapters region code, and eagerly loading that information from the database. Issue #277: Add County, Chapter Code and Chapter Name

This is basically a duplication of the normal requests page with the query hard locked to requests with no linked county in order to double check that the system is assigning things correctly. Try to reuse the view code as much as possible with a basic switch flag. Issue #277: Add County, Chapter Code and Chapter Name

frankduncan · 2019-02-24T12:58:20Z

@OhMcGoo When displaying the chapters on the requests list, do you want the chapter name, or the chapter code?

jim-mcgowan · 2019-02-24T16:22:57Z

Chapter name. Most staff and volunteers don't know their Chapter codes.

kfogel · 2019-02-25T16:15:03Z

@frankduncan Re this:

@kfogel Another potential additional feature would be a read only page of the current county/chapter/region configuration, accessible only for admins.

Would that be easier to do as a page or as a CSV export? It might actually be more useful as an export anyway, now that I think about it. Would that be pretty easy to do? (It shouldn't delay our rollout today though, of course.)

frankduncan · 2019-02-25T16:36:49Z

Would that be easier to do as a page or as a CSV export? It might actually be more useful as an export anyway, now that I think about it. Would that be pretty easy to do? (It shouldn't delay our rollout today though, of course.)

@kfogel Pretty equivalent, since I was planning to do a simple noninteractive table.

kfogel · 2019-02-25T16:47:08Z

@frankduncan and I discussed this in Zulip:

Superusers will be able to export that data. (The only reason to not make it available to nonsuper-users is not that they shouldn't have access to the data, but rather that they are unlikely to need the data very so instead keeping the UI clean and simple takes precedence. There's no point offering a button/link that someone isn't likely to need anyway; it would just be distracting. The superusers are accustomed to more fruit salad on their screens, and they're more likely to want this anyway.)

We will do it as a CSV export. That way we avoid UI issues, and it's probably more useful as a CSV anyway, in the sense that if someone really needs to check the region/chapter/county correspondences, the best way to do it is programmatically anyway. IOW, the more urgent or thorough the use of this data, the more likely the user would want the data in spreadsheet format.

jim-mcgowan · 2019-02-25T16:55:29Z

@kfogel, @frankduncan: I think ALL users will need to be able to export data.