Add filter to only show published cases on /legal/

[lbeaufort]

Salient is working on a new version of SMUR, the internal MUR management software. Users of this software need to be able to publish/unpublish a case (MUR/ADR/AF). Currently, when a user unpublishes a MUR, a hard delete is done in postgres. However, in order for the celery task to be able to detect an unpublished case and reload elasticsearch to reflect the change, we need to add an is_published flag to the fecmur.case table, and only show published cases. Salient will update the is_published and pg_update date.

Completion criteria
DB changes

• Add a new migration file
• Update "case" table to add is_published boolean column (Salient will update this data. Please make sure Salient updates the pg_update field when they publish/unpublish the case.)
• Update fecmur.cases_with_parsed_case_serial_numbers_vw to include the column so we can detect when cases are unpublished

Python changes:

• Update queries to only show published (celery task shouldn't need changes - it only looks at the pg_update field)
• Double-check subtable behavior (including af_case table) for published/unpublished cases
• Double-check elasticsearch behavior when we unpublish a case - does the reload remove it?
• Add an automated test to make sure unpublishing/publishing a case will result in hiding/showing a case

[pkfec]

Spoke with Srini(Salient PM) about the legal system data transfers. Had a brief discussion about adding the new column is_publishedon Oracle case table in fecmur schema.

[pkfec]

Thanks @fecjjeng for adding published_flag boolean column to case table. And the default value of published_flagcolumn set to true. In a new migration file fecmur.cases_with_parsed_case_serial_numbers_vw view is altered to include the published_flag column.

Modified the ALL_CASES and SINGLE_CASE sql's in current_cases.py to get the cases with published_flag set to true.

Will continue to test once Salient inserts test data to dev db.

[pkfec]

ToDo##
Test on dev space:

1. manual deploy feature branch to dev space
2. on dev db, Choose a case_no (example : 6260) from case table and update pg_date and published_flg = false
3. refresh_cases task will be initiated. monitor the celery worker logs .
4. search for MUR case# 6260 here. Case shouldnt appear : https://fec-dev-proxy.app.cloud.gov/data/legal/search/enforcement/?case_no=6260

[pkfec]

Tested published/unpublished cases on my local env:

1. loaded current mur 7278 onto cfdm_test db
2. verified that 7278 existing on my local ES service
3. on cfdm_test db, updated the two columns pg_date and processed_flg=false (which means that the case is deleted from the DB). This will initiate the celery task.
4. modified the existing load_cases function to check if processed_flg=false and delete from ES service
5. refresh the local ES service to make sure the mur 7278 is deleted.
6. reload the deleted mur 7278 again to see how load_cases function handles this case.

@lbeaufort really appreciate your help testing the published/unpublished cases with me.