Add office number filter

[subaykan]

We get frequent tickets about search timeouts from VC users searching for particular offices by entering them into the search input, e.g. (742) (796) (704) (743). The current search code behind this is very inefficient, querying many columns for these values, and sometimes timing out.

This can be done more efficiently by adding a filter for the dshsOffice field of referrals and adding a filter to the UI - very similar to the referral reason filter for finding 72hr referrals - which would allow the user to select one or multiple offices to filter their search, and not search for the office number with a wild card query.

Questions: should this only be exposed to Region Admin users?

[keithligon]

The file listing offices has many that have DCFS in the name, such as “Aberdeen DCFS (764)” or “Bellingham DCFS (733)“. However, in the database, I can see office entries both with and without DCFS:

db=# select "dshsOffice", count(*), max("createdAt"), min("createdAt") from "ServiceReferrals" where "isCurrentVersion" group by  "dshsOffice" order by "dshsOffice";
                 dshsOffice                 | count |            max             |            min
--------------------------------------------+-------+----------------------------+----------------------------
 Aberdeen (764)                             |  2212 | 2023-10-23 18:53:14.659+00 | 2016-02-01 21:32:19.071+00
 Aberdeen DCFS (764)                        |    28 | 2020-02-10 23:14:01.822+00 | 2019-04-16 14:34:19.229+00
 Bellingham (733)                           |  1842 | 2023-10-19 22:49:10.194+00 | 2016-07-28 20:35:30.156+00
 Bellingham DCFS (733)                      |    15 | 2020-03-01 08:20:41.663+00 | 2018-10-25 18:32:13.746+00
 Bremerton (751)                            |  3417 | 2023-10-23 14:42:23.076+00 | 2015-09-23 23:03:43.526+00
 Bremerton DCFS (751)                       |   132 | 2020-02-10 04:55:28.504+00 | 2018-02-20 19:17:00.788+00
 </snip>

The newer entries don't include DCFS. Should both a DCFS and non DFCS entry be included as part of the query? Or is there a better source for office names?

[subaykan]

I think we should use the list from the file for displaying the options on the filter, to have a consistent pattern - with names next to checkboxes. For the indexing and the query, let's chat about what it would take to have a mapping of the numbers to the values in the database. The number and the office name get sent in separate fields in the XML, and for some reason they were combined when we ingest the records.