SORMAS-Project

SORMAS-Project copied to clipboard

Create automated test to verify sample timestamp from Quarantine Order popup

[]

Reference defect: https://github.com/hzi-braunschweig/SORMAS-Project/issues/10241 Aim: Create test to validate that set collected date and time and disease name are present in Sample field when creating a new Quarantine Order. 

rdutu-vg

Mobile app synchronization is being failed.

[{"_id":"6369724e8041c95dfb251510","body":"There was another recent report w.r.t. to the app sync: #10231","issue_id":1663844247063,"origin_id":1232754865,"user_origin_id":20031875,"create_time":1661941474,"update_time":1661941474,"id":1667854926131,"updated_at":"2022-11-07T21:02:06.131000Z","created_at":"2022-11-07T21:02:06.131000Z"},{"_id":"6369724e8041c95dfb251511","body":"@zohaibhassan122 Can you please check whether this error persists in the current development version, i.e. 1.76.0? Preferably on a server with no custom user roles created, because there's a known synchronisation issue that we're currently taking care of that involves those.","issue_id":1663844247063,"origin_id":1246669626,"user_origin_id":23701005,"create_time":1663157371,"update_time":1663157371,"id":1667854926134,"updated_at":"2022-11-07T21:02:06.133000Z","created_at":"2022-11-07T21:02:06.133000Z"},{"_id":"6369724e8041c95dfb251512","body":"@MateStrysewske These logs were obtained from a production server ( v1.73.3) that is suppose to host only an applroved version. The server has only default user roles.\r\n- Can there not be another quick solution rather than waiting until 1.76 is approved?","issue_id":1663844247063,"origin_id":1252287694,"user_origin_id":12996724,"create_time":1663677144,"update_time":1663677144,"id":1667854926138,"updated_at":"2022-11-07T21:02:06.137000Z","created_at":"2022-11-07T21:02:06.137000Z"},{"_id":"6369724e8041c95dfb251513","body":"The log provided seem to stem from another problem, which is triggered when changing the language. It is fixed by #10132\r\nThe problem reported in this ticket is extremely likely the one fixed by #10231.\r\n\r\nWe should backport both fixes to 1.75.2.\r\n\r\nCommits to bring into 1.75.2:\r\n* https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/pull\/10290\/commits\/5de4bab22d1d5541cce09c8740877aa6feee913f\r\n* https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/pull\/10221\/commits\/1b9586281660eed7c99478eef9f40580c807eef5","issue_id":1663844247063,"origin_id":1253312663,"user_origin_id":23217632,"create_time":1663745209,"update_time":1663745612,"id":1667854926141,"updated_at":"2022-11-07T21:02:06.140000Z","created_at":"2022-11-07T21:02:06.140000Z"},{"_id":"6369724e8041c95dfb251514","body":"I discussed with @MateStrysewske that we maybe would reopen #10132 and #10231 to go into a 1.75.x version. That means that this ticket will then be closed as duplicate.","issue_id":1663844247063,"origin_id":1253362645,"user_origin_id":24678567,"create_time":1663748218,"update_time":1663748218,"id":1667854926144,"updated_at":"2022-11-07T21:02:06.144000Z","created_at":"2022-11-07T21:02:06.144000Z"}]

### Bug Description Whenever I try to sync the data from the mobile app, it's getting failed. I have attached the logs for refreence. ### Expected Behavior The data should...

SORMAS-ZohaibHassan

Improve performance of getAllAfter queries into DTOs [8]

[{"_id":"6369828aea01ec786e8b0b31","body":"Hi there, I am not sure if this is related but since recently we are having issues to get all tasks created within the last 24 hours via **\/tasks\/all\/{since}** No matter the size of the response e.g. even if only 1 task will be returned matching the time criteria. No response even for 5 minutes. The highest possible timespan that we got working on some instances is last 4 hours. Even that one takes a couple of minutes to respond. Let us know if you need more info around the issue and keep us posted about the outcome here.","issue_id":1663844247069,"origin_id":1141970785,"user_origin_id":90610296,"create_time":1653993818,"update_time":1653993818,"id":1667859082486,"updated_at":"2022-11-07T22:11:22.486000Z","created_at":"2022-11-07T22:11:22.486000Z"},{"_id":"6369828aea01ec786e8b0b32","body":"> Hi there, I am not sure if this is related but since recently we are having issues to get all tasks created within the last 24 hours via **\/tasks\/all\/{since}**\r\n\r\nIdentifying the changed entities is what we want to improve. That includes also tasks.","issue_id":1663844247069,"origin_id":1142133231,"user_origin_id":24678567,"create_time":1654003491,"update_time":1654003501,"id":1667859082505,"updated_at":"2022-11-07T22:11:22.504000Z","created_at":"2022-11-07T22:11:22.504000Z"},{"_id":"6369828aea01ec786e8b0b33","body":"Perfect! Are there already some concrete plans when can this one be expected to make its way to production?","issue_id":1663844247069,"origin_id":1143242350,"user_origin_id":90610296,"create_time":1654070086,"update_time":1654070086,"id":1667859082511,"updated_at":"2022-11-07T22:11:22.511000Z","created_at":"2022-11-07T22:11:22.511000Z"},{"_id":"6369828aea01ec786e8b0b34","body":"Discussed current status and next steps with @HolgerReiseVSys and @syntakker. Results:\r\n\r\n1. Update feature branch to current development branch\r\n2. @syntakker will start with performance tests on cases, contacts, immunizations, tasks, samples as prechecks\r\n3. Integrate CaseService.getAllActiveCasesAfter as all the other sibling methods.\r\n4. Refactor `BaseAdoService.getAll` to `getList` to be reusable with other filters, make it protected or leave public?\r\n5. Indizes for all entities that implement AdoServiceWithUserFilter","issue_id":1663844247069,"origin_id":1237815815,"user_origin_id":24678567,"create_time":1662452387,"update_time":1662469485,"id":1667859082531,"updated_at":"2022-11-07T22:11:22.530000Z","created_at":"2022-11-07T22:11:22.530000Z"},{"_id":"6369828aea01ec786e8b0b35","body":"Helper used to create sort indices: [IndexBuilderHelper.xlsx](https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/files\/9507751\/IndexBuilderHelper.xlsx)\r\n","issue_id":1663844247069,"origin_id":1239582641,"user_origin_id":24678567,"create_time":1662566187,"update_time":1662566187,"id":1667859082535,"updated_at":"2022-11-07T22:11:22.535000Z","created_at":"2022-11-07T22:11:22.535000Z"},{"_id":"6369828aea01ec786e8b0b36","body":"**Preliminary analysis of performance before and after the changes introduced in PR #10282**\r\n\r\n(tested on dafd47df2f756277323e15d28a3c6f678b1864e6)\r\n\r\nThe analysis was conducted by measuring the runtime of REST calls to the respective endpoints with batch size 1000.\r\nA substantial improvement can be observed for all entities with long response times:\r\n```\r\ncases (1000) .................00:05:28 -> 00:01:31\r\ncontacts (1000) ..............00:01:07 -> 00:00:30\r\nimmunizations (1000) .........00:01:16 -> 00:00:12\r\nsamples (1000) ...............00:01:41 -> 00:00:20\r\ntasks (1000) .................00:01:06 -> 00:00:18\r\nvisits (1000) ................00:00:27 -> 00:00:04\r\n```\r\nAn exception is pathogen test with no improvement \/ slightly worse performance:\r\n```\r\npathogentests (1000) .........00:01:15 -> 00:01:26\r\n```\r\nThe results based on the dataset of the initial analysis (see issue description). Further measurements will be performed as needed (e.g., retesting of individual endpoints, larger batch sizes etc.).\r\n\r\nAnalysis data:\r\n[performance_9320.zip](https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/files\/9562743\/performance_9320.zip)\r\n\r\n<details><summary>Detailed analysis<\/summary>\r\n\r\n**Full list of REST calls**\r\nPerformance of REST calls (`...\/<entities>\/all\/`) before the refacoring:\r\n```\r\nactions (1000) ...............00:00:00\r\nadditionaltests (1000) .......00:00:01\r\naggregatereports (1000) ......00:00:00\r\nareas (1000) .................00:00:00\r\ncampaigns (1000) .............00:00:00\r\ncases (1000) .................00:05:28\r\nclinicalvisits (1000) ........00:00:00\r\ncommunities (1000) ...........00:00:00\r\ncontacts (1000) ..............00:01:07\r\ncontinents (1000) ............00:00:00\r\ncountries (1000) .............00:00:01\r\ncustomizableenumvalues (1000) 00:00:00\r\ndiseaseconfigurations (1000) .00:00:00\r\ndistricts (1000) .............00:00:00\r\neventparticipants (1000) .....00:00:07\r\nevents (1000) ................00:00:04\r\nfeatureconfigurations (1000) .00:00:00\r\nimmunizations (1000) .........00:01:16\r\npathogentests (1000) .........00:01:15\r\npersons (1000) ...............00:00:07\r\npointsofentry (1000) .........00:00:00\r\nprescriptions (1000) .........00:00:00\r\nregions (1000) ...............00:00:00\r\nsamples (1000) ...............00:01:41\r\nsharerequests (1000) .........00:00:00\r\nsubcontinents (1000) .........00:00:00\r\ntasks (1000) .................00:01:06\r\ntreatments (1000) ............00:00:00\r\nusers (1000) .................00:00:00\r\nuserroles (1000) .............00:00:00\r\nvisits (1000) ................00:00:27\r\nweeklyreports (1000) .........00:00:01\r\n```\r\n... and after:\r\n```\r\nactions (1000) ...............00:00:01\r\nadditionaltests (1000) .......00:00:01\r\naggregatereports (1000) ......00:00:00\r\nareas (1000) .................00:00:00\r\ncampaigns (1000) .............00:00:01\r\ncases (1000) .................00:01:31\r\nclinicalvisits (1000) ........00:00:00\r\ncommunities (1000) ...........00:00:00\r\ncontacts (1000) ..............00:00:30\r\ncontinents (1000) ............00:00:00\r\ncountries (1000) .............00:00:00\r\ncustomizableenumvalues (1000) 00:00:00\r\ndiseaseconfigurations (1000) .00:00:00\r\ndistricts (1000) .............00:00:00\r\neventparticipants (1000) .....00:00:05\r\nevents (1000) ................00:00:02\r\nfeatureconfigurations (1000) .00:00:00\r\nimmunizations (1000) .........00:00:12\r\npathogentests (1000) .........00:01:26\r\npersons (1000) ...............00:00:08\r\npointsofentry (1000) .........00:00:00\r\nprescriptions (1000) .........00:00:01\r\nregions (1000) ...............00:00:00\r\nsamples (1000) ...............00:00:20\r\nsharerequests (1000) .........00:00:00\r\nsubcontinents (1000) .........00:00:00\r\ntasks (1000) .................00:00:18\r\ntreatments (1000) ............00:00:01\r\nusers (1000) .................00:00:00\r\nuserroles (1000) .............00:00:00\r\nvisits (1000) ................00:00:04\r\nweeklyreports (1000) .........00:00:00\r\n```\r\n\r\n**Execution details for selected entities**\r\n\r\n**Cases**\r\nbefore:\r\n\r\n\r\nafter:\r\n\r\n\r\nHere the calls to `CaseService.inJurisdictionOrOwned` per selected case have been dropped, roughly accounting for the measurde difference.\r\n\r\n**Contacts**\r\nbefore:\r\n\r\n\r\nafter:\r\n\r\n\r\nAs for cases, a heavy reduction of calls to `ContactService.inJurisdictionOrOwned`.\r\n\r\n**Immunizations**\r\nbefore:\r\n\r\n\r\nafter:\r\n\r\n\r\nHere the main culprits were calls to `ImmunizationService.inJurisdictionOrOwned` and `FeatureConfigurationFacadeEjb.isPropertyValueTrue`\r\n\r\n**Tasks**\r\nbefore:\r\n\r\n\r\nafter:\r\n\r\n\r\nHere, calls to `TaskService.inJurisdictionOrOwned` on a per object basis accounted for most of the time spent.\r\n<\/details>\r\n\r\n","issue_id":1663844247069,"origin_id":1246234606,"user_origin_id":5616564,"create_time":1663131513,"update_time":1663131859,"id":1667859082538,"updated_at":"2022-11-07T22:11:22.538000Z","created_at":"2022-11-07T22:11:22.538000Z"},{"_id":"6369828aea01ec786e8b0b37","body":"Changes on performance tooling still open","issue_id":1663844247069,"origin_id":1253368566,"user_origin_id":24678567,"create_time":1663748512,"update_time":1663748512,"id":1667859082542,"updated_at":"2022-11-07T22:11:22.542000Z","created_at":"2022-11-07T22:11:22.542000Z"}]

### Problem Description As shown by the following analysis, many `getAllAfter` methods show an inperformant pattern: 1. The `"Entity"Service.getAllAfter` method takes some seconds. As shown in https://github.com/hzi-braunschweig/SORMAS-Project/issues/8946#issuecomment-1129937176, this can be...

StefanKock

Description and default value for user role properties

[]

### Problem Description When creating a new user role the meaning of the checkboxes "Has optional health facility" and "Port health user" are not clear to users.  ### Proposed...

MartinWahnschaffe

ConfigFacade: Unit test for URL validation with placeholders

[]

### Problem Description #9091 showed that the current expected URL patterns are not appropriatly unit tested. ### Proposed Solution Amend/adjust the existing unit test `ConfigFacadeEjbTest.testValidateExternalUrls()` ### Possible Alternatives ### Additional...

StefanKock

user role_ incorrect error message if user do not have the right SORMAS_UI

[]

### Bug Description If a user do not have the right SORMAS_UI and tries to log in an error message appears then an error message appears prompting the user to...

SahaLinaPrueger

Error in directories and when opening entries for specific user right combinations

[{"_id":"636974f54267bf3cd4520b45","body":"#10362 documents a very similar error. This behaviour can probably be observed for multiple directories and might be connected to the missing user right to view existing persons.","issue_id":1663844247115,"origin_id":1253366934,"user_origin_id":23701005,"create_time":1663748423,"update_time":1663748423,"id":1667855605646,"updated_at":"2022-11-07T21:13:25.646000Z","created_at":"2022-11-07T21:13:25.646000Z"},{"_id":"636974f54267bf3cd4520b46","body":"#10409 very likely describes the root of this problem.","issue_id":1663844247115,"origin_id":1253391013,"user_origin_id":23701005,"create_time":1663749739,"update_time":1663749739,"id":1667855605649,"updated_at":"2022-11-07T21:13:25.649000Z","created_at":"2022-11-07T21:13:25.649000Z"},{"_id":"636974f54267bf3cd4520b47","body":"Solution as discussed with @MartinWahnschaffe: \r\nCASE_VIEW, CONTACT_VIEW, TRAVEL_ENTRY_VIEW, EVENTPARTICIPANT_VIEW, IMMUNIZATION_VIEW is dependent on PERSON_VIEW. \r\nCASE_EDIT, CONTACT_EDIT, TRAVEL_ENTRY_EDIT, EVENTPARTICIPANT_EDIT, IMMUNIZATION_EDIT is dependent on PERSON_EDIT. \r\nCASE_DELETE, CONTACT_DELETE, TRAVEL_ENTRY_DELETE, EVENTPARTICIPANT_DELETE, IMMUNIZATION_DELETE is dependent on PERSON_DELETE. ","issue_id":1663844247115,"origin_id":1253517109,"user_origin_id":100844689,"create_time":1663756535,"update_time":1664288696,"id":1667855605652,"updated_at":"2022-11-07T21:13:25.652000Z","created_at":"2022-11-07T21:13:25.652000Z"},{"_id":"636974f54267bf3cd4520b48","body":"Validated ticket on local setup using the latest sormas version 1.76-snapshot and while checking the code alongside the comment from this ticket.","issue_id":1663844247115,"origin_id":1275155545,"user_origin_id":70562142,"create_time":1665515535,"update_time":1665515535,"id":1667855605658,"updated_at":"2022-11-07T21:13:25.658000Z","created_at":"2022-11-07T21:13:25.658000Z"}]

### Bug Description If an admin checks the right VIEW_CASE and looks at the currently description he will find, that a user with this right is "able to view existing...

SahaLinaPrueger

Access forbidden message pops up when opening person directory

[{"_id":"6369740dea01ec786e8ae6fa","body":"@SahaLinaPrueger Please add the user role of the user you produced this message with to the issue description, or if it's a custom user role, please add a screenshot of the user role screen so that we can reproduce it on our systems.","issue_id":1663844247117,"origin_id":1246618029,"user_origin_id":23701005,"create_time":1663154325,"update_time":1663154325,"id":1667855373115,"updated_at":"2022-11-07T21:09:33.115000Z","created_at":"2022-11-07T21:09:33.115000Z"},{"_id":"6369740dea01ec786e8ae6fb","body":"@MateStrysewske Sry there was something wrong with the file, now it is attached. It is the same role as in the issue #10352 mentioned. There are screenshots.","issue_id":1663844247117,"origin_id":1246744171,"user_origin_id":100844689,"create_time":1663161079,"update_time":1663161104,"id":1667855373118,"updated_at":"2022-11-07T21:09:33.118000Z","created_at":"2022-11-07T21:09:33.118000Z"},{"_id":"6369740dea01ec786e8ae6fc","body":"The forbidden error occurred on the dashboard page but sometimes the tooltip is kept and displayed on other pages to after navigating from the crashed dashboard page.\r\n\r\nDashboard rights now require corresponding main entity view rights:\r\n\r\n`DASHBOARD_SURVEILLANCE_VIEW` --> `CASE_VIEW`\r\n`DASHBOARD_CONTACT_VIEW` --> `CONTACT_VIEW`\r\n`DASHBOARD_CAMPAIGNS_VIEW` --> `CAMPAIGN_VIEW`\r\n","issue_id":1663844247117,"origin_id":1259687046,"user_origin_id":62599627,"create_time":1664293059,"update_time":1664293059,"id":1667855373122,"updated_at":"2022-11-07T21:09:33.122000Z","created_at":"2022-11-07T21:09:33.122000Z"},{"_id":"6369740dea01ec786e8ae6fd","body":"Reopened due to the error message still appearing in tooltip and pop-up when switching between Contact person and contact list.\r\nBehavior was checked on test.de Version: 1.76.0-SNAPSHOT ([e37915d](https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/commits\/e37915d907dbb6fd18a243ce6d056635ea2f8cd2))(using user CustomRole (user role: ViewCase2 - see [test-deUserRoles.xlsx](https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/files\/9682705\/test-deUserRoles.xlsx))\r\n- user role has all the user rights described in the ticket + CASE_VIEW, CONTACT_VIEW and CAMPAIGN_VIEW, required by other user rights (see previous comment).\r\n\r\nSteps to reproduce:\r\n1. Go to Contacts\r\n2. Open a contact (blank page)\r\n3. Open contact person tab (unable to edit)\r\n4. Return to Contact tab.\r\nResult: A problem has occurred error message is displayed.\r\n\r\n\r\n5. Dismiss message and go back to Contact person.\r\n6. Hold for a few seconds anywhere on the page\r\nResult: error message is displayed in tooltip.\r\n\r\n\r\n\r\n\r\n\r\n\r\n","issue_id":1663844247117,"origin_id":1263338443,"user_origin_id":111864167,"create_time":1664530297,"update_time":1664530784,"id":1667855373125,"updated_at":"2022-11-07T21:09:33.125000Z","created_at":"2022-11-07T21:09:33.125000Z"},{"_id":"6369740dea01ec786e8ae6fe","body":"Due the last modification, the Contact page is not blank anymore, so the error does not appear.\r\nThe bug does not reproduce.","issue_id":1663844247117,"origin_id":1269455592,"user_origin_id":112615459,"create_time":1665040089,"update_time":1665040089,"id":1667855373128,"updated_at":"2022-11-07T21:09:33.128000Z","created_at":"2022-11-07T21:09:33.128000Z"},{"_id":"6369740dea01ec786e8ae6ff","body":"Validated on **test-de** version 1.76.0-SNAPSHOT([4f461c7](https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/commits\/4f461c742b060453663fda307e89a4855d64317d))\r\n- Checked behavior on the specified user rights configuration and other similar user roles\/user rights.\r\n- Checked viewing and editing contacts, cases, samples & tasks are according to set user permissions.","issue_id":1663844247117,"origin_id":1274332100,"user_origin_id":111864167,"create_time":1665478088,"update_time":1665478088,"id":1667855373132,"updated_at":"2022-11-07T21:09:33.131000Z","created_at":"2022-11-07T21:09:33.131000Z"}]

### Bug Description Error message: _Access to the specified resource has been forbidden. Please contact your supervisor or administrator and inform them about it. Please reload the page to see...

SahaLinaPrueger

Remove unnecessary queries for embedded entities [3]

[]

### Problem Description When saving a core dto, like a CaseDataDto, the system calls the fromDto method for all sub entities (e.g. SymptomsDto), which executes a service.getByUuid methode to get...

MartinWahnschaffe

Create a script to update and migrate script_occupation type values

[{"_id":"63698351ea01ec786e8b0cf3","body":"@SahaLinaPrueger I have extracted the todos from the document and restructured them a bit.\r\n\r\n@markusmann-vg The script can be created and executed independently, so the hotfix does not have to wait for it.","issue_id":1663844247124,"origin_id":1250671872,"user_origin_id":23217632,"create_time":1663572997,"update_time":1663572997,"id":1667859281556,"updated_at":"2022-11-07T22:14:41.555000Z","created_at":"2022-11-07T22:14:41.555000Z"},{"_id":"63698351ea01ec786e8b0cf4","body":"Script to be reviewed by @MartinWahnschaffe:\r\n\r\nThe following script is only relevant for systems that use German as system language:\r\n\r\n**Update: This is now outdated. Final script is further down.**\r\n\r\n<details>\r\n```\r\n-- Step 1: Insert values used in Germany into database if they have not been used in this particular database yet\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'FARMER', 'Selbstst\u00e4ndige*r Landwirt*in bzw. Genossenschaftsbauer\/b\u00e4uerin', '[{\"languageCode\":\"en\",\"value\":\"Farmer\"},{\"languageCode\":\"de-DE\",\"value\":\"Selbstst\u00e4ndige*r Landwirt*in bzw. Genossenschaftsbauer\/b\u00e4uerin\"}]' \r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'FARMER');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'RELIGIOUS_LEADER', 'Geistliche*r', '[{\"languageCode\":\"en\",\"value\":\"Religious leader\"},{\"languageCode\":\"de-DE\",\"value\":\"Geistliche*r\"}]'\r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'RELIGIOUS_LEADER');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'HOUSEWIFE', 'Hausfrau\/-mann', '[{\"languageCode\":\"en\",\"value\":\"Housewife\"},{\"languageCode\":\"de-DE\",\"value\":\"Hausfrau\/-mann\"}]'\r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'HOUSEWIFE');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'PUPIL_STUDENT', 'Ausbildung', '[{\"languageCode\":\"en\",\"value\":\"Pupil \/ student\"},{\"languageCode\":\"de-DE\",\"value\":\"Ausbildung\"}]'\r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'PUPIL_STUDENT');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'WORKING_WITH_ANIMALS', 'Arbeiten mit Tieren', '[{\"languageCode\":\"en\",\"value\":\"Working with animals\"},{\"languageCode\":\"de-DE\",\"value\":\"Arbeiten mit Tieren\"}]'\r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'WORKING_WITH_ANIMALS');\r\n\r\n-- Step 2: Insert new values used in Germany into database that have not existed or were misused before\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'CIVIL_SERVANT', 'Beamt*in, Richter*in, Berufssoldat*in', '[{\"languageCode\":\"en\",\"value\":\"Civil servant\"},{\"languageCode\":\"de-DE\",\"value\":\"Beamt*in, Richter*in, Berufssoldat*in\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'EMPLOYEE', 'Angestellte*r', '[{\"languageCode\":\"en\",\"value\":\"Employee\"},{\"languageCode\":\"de-DE\",\"value\":\"Angestellte*r\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'WORKER', 'Arbeiter*in', '[{\"languageCode\":\"en\",\"value\":\"Worker\"},{\"languageCode\":\"de-DE\",\"value\":\"Arbeiter*in\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'ACADEMIC', 'Akademiker*in in freiem Beruf', '[{\"languageCode\":\"en\",\"value\":\"Academic\"},{\"languageCode\":\"de-DE\",\"value\":\"Akademiker*in in freiem Beruf\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'ASSISTING_FAMILY_MEMBER', 'Mithelfende*r Angeh\u00f6rige*r', '[{\"languageCode\":\"en\",\"value\":\"Assisting family member\"},{\"languageCode\":\"de-DE\",\"value\":\"Mithelfende*r Angeh\u00f6rige*r\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'NOT_SPECIFIED', 'Keine Angabe', '[{\"languageCode\":\"en\",\"value\":\"Not specified\"},{\"languageCode\":\"de-DE\",\"value\":\"Keine Angabe\"}]');\r\n\r\n-- Step 3: Migrate occupation type entries in person table to conform to the corrected translations and meanings\r\nUPDATE person SET occupationtype = 'CIVIL_SERVANT' WHERE occupationtype = 'CHILD';\r\nUPDATE person SET occupationtype = 'EMPLOYEE' WHERE occupationtype = 'HUNTER_MEAT_TRADER';\r\nUPDATE person SET occupationtype = 'WORKER' WHERE occupationtype = 'MINER';\r\nUPDATE person SET occupationtype = 'ACADEMIC' WHERE occupationtype = 'BUSINESSMAN_WOMAN';\r\nUPDATE person SET occupationtype = 'BUSINESSMAN_WOMAN' WHERE occupationtype = 'BUTCHER';\r\nUPDATE person SET occupationtype = 'ASSISTING_FAMILY_MEMBER' WHERE occupationtype = 'TRANSPORTER';\r\nUPDATE person SET occupationtype = 'NOT_SPECIFIED' WHERE occupationtype = 'TRADITIONAL_SPIRITUAL_HEALER';\r\n\r\n-- Step 4: Delete occupation types that were previously misused and are no longer needed\r\nDELETE FROM customizableenumvalue WHERE datatype = 'OCCUPATION_TYPE' AND (value = 'CHILD' OR value = 'HUNTER_MEAT_TRADER' OR value = 'MINER' OR value = 'BUTCHER' OR value = 'BUSINESSMAN_WOMAN' OR value = 'TRANSPORTER' OR value = 'TRADITIONAL_SPIRITUAL_HEALER');\r\n```\r\n<\/details>\r\nAfterwards a server restart is needed!","issue_id":1663844247124,"origin_id":1253661010,"user_origin_id":23701005,"create_time":1663764415,"update_time":1665991081,"id":1667859281560,"updated_at":"2022-11-07T22:14:41.559000Z","created_at":"2022-11-07T22:14:41.559000Z"},{"_id":"63698351ea01ec786e8b0cf5","body":"@MateStrysewske Script looks correct and worked as expected on my local system.","issue_id":1663844247124,"origin_id":1257631431,"user_origin_id":23217632,"create_time":1664178417,"update_time":1664178417,"id":1667859281563,"updated_at":"2022-11-07T22:14:41.563000Z","created_at":"2022-11-07T22:14:41.563000Z"},{"_id":"63698351ea01ec786e8b0cf6","body":"@MartinWahnschaffe Which release notes should it be part of?","issue_id":1663844247124,"origin_id":1257828684,"user_origin_id":23701005,"create_time":1664188344,"update_time":1664188344,"id":1667859281566,"updated_at":"2022-11-07T22:14:41.565000Z","created_at":"2022-11-07T22:14:41.565000Z"},{"_id":"63698351ea01ec786e8b0cf7","body":"I think you can put it into the notes of the upcoming release 1.76 release","issue_id":1663844247124,"origin_id":1259070166,"user_origin_id":23217632,"create_time":1664262073,"update_time":1664262073,"id":1667859281568,"updated_at":"2022-11-07T22:14:41.567000Z","created_at":"2022-11-07T22:14:41.567000Z"},{"_id":"63698351ea01ec786e8b0cf8","body":"@MartinWahnschaffe @MateStrysewske I think this should go into 1.75 because that's the version where the occupation types where moved to the customizable enums","issue_id":1663844247124,"origin_id":1259110570,"user_origin_id":70317594,"create_time":1664264436,"update_time":1664264436,"id":1667859281570,"updated_at":"2022-11-07T22:14:41.570000Z","created_at":"2022-11-07T22:14:41.570000Z"},{"_id":"63698351ea01ec786e8b0cf9","body":"Hm, then we should mention it in the existing release notes and also in the upcoming notes of the hotfix.","issue_id":1663844247124,"origin_id":1259169976,"user_origin_id":23217632,"create_time":1664267580,"update_time":1664267580,"id":1667859281573,"updated_at":"2022-11-07T22:14:41.572000Z","created_at":"2022-11-07T22:14:41.572000Z"},{"_id":"63698351ea01ec786e8b0cfa","body":"I run a test on https:\/\/survnet-import-test.sormas.netzlink.com\/\r\nFor the test I have created different test cases on version 1.74.3. The instance was raised to 1.75.4 and the script was executed. After that, for example, the case with the occupation type \"child\" is displayed as follows:\r\n\r\n\r\n**customizable_enum_values.csv**\r\nIn the column \"Caption\" there are German or English entries mixed.\r\nThe entries in the column \"translation\" are partly missing\r\n\r\n\r\n**Drop-down menu**\r\nIt looks to me like the menus are unchanged.\r\n\r\n\r\n\r\n@MateStrysewske @MartinWahnschaffe \r\nIs there a dependency on the ticket https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/issues\/5015 ?\r\nDo the two solutions need to be better aligned or does the test need to be adjusted? ","issue_id":1663844247124,"origin_id":1276380186,"user_origin_id":101708230,"create_time":1665589081,"update_time":1665589530,"id":1667859281576,"updated_at":"2022-11-07T22:14:41.575000Z","created_at":"2022-11-07T22:14:41.575000Z"},{"_id":"63698351ea01ec786e8b0cfb","body":"Hi @KernB. Yes there is a dependency on #5015, but since it's part of 1.75, updating to this version and then executing this script should work fine. \r\n\r\nWhere did you take those screenshots? I just had a look at https:\/\/survnet-import-test.sormas.netzlink.com\/ and there the drop-down looks like this:\r\n\r\n\r\n\r\n\r\nSo we definitely have a mistake in the script, because the caption should always be in the system language of the server. In this case we should have provided the script with German captions. I will take care of updating this.\r\n\r\nBesides that the entries on my screenshot looks fine to me. What I see on the system doesn't match your customizable_enum_values.csv, though.\r\n\r\n1. Based on the csv WORKING_WITH_ANIMALS was added. Other entries like FARMER and RELIGIOUS_LEADER were already there. In the drop-down of my screenshot all those values seem to be new.\r\n2. CIVIL_SERVANT, EMPLOYEE, etc. were added\r\n3. CHILD, HUNTER_MEAT_EATER, etc. were removed","issue_id":1663844247124,"origin_id":1277179340,"user_origin_id":23217632,"create_time":1665647328,"update_time":1665649878,"id":1667859281580,"updated_at":"2022-11-07T22:14:41.579000Z","created_at":"2022-11-07T22:14:41.579000Z"},{"_id":"63698351ea01ec786e8b0cfc","body":"I have updated the script above to contain the captions in the right language.","issue_id":1663844247124,"origin_id":1277194816,"user_origin_id":23217632,"create_time":1665648039,"update_time":1665648039,"id":1667859281582,"updated_at":"2022-11-07T22:14:41.582000Z","created_at":"2022-11-07T22:14:41.582000Z"},{"_id":"63698351ea01ec786e8b0cfd","body":"When doing the migration (step 3):\r\n```\r\nUPDATE person SET occupationtype = 'BUSINESSMAN_WOMAN' WHERE occupationtype = 'BUTCHER';\r\nUPDATE person SET occupationtype = 'ACADEMIC' WHERE occupationtype = 'BUSINESSMAN_WOMAN';\r\n```\r\nShould the order of these lines not be reversed? Otherwise, every current `BUTCHER` value will turn into `ACADEMIC`...","issue_id":1663844247124,"origin_id":1277367851,"user_origin_id":67001822,"create_time":1665655764,"update_time":1665655764,"id":1667859281584,"updated_at":"2022-11-07T22:14:41.584000Z","created_at":"2022-11-07T22:14:41.584000Z"},{"_id":"63698351ea01ec786e8b0cfe","body":"Value | German Caption |\r\n-- | -- |\r\nCHILD | Beamt*in, Richter*in, Berufssoldat*in |\r\nHUNTER_MEAT_TRADER | Angestellte*r |\r\nMINER | Arbeiter*in |\r\nBUTCHER | Selbstst\u00e4ndige*r |\r\nBUSINESSMAN_WOMAN | Akademiker*in in freiem Beruf |\r\nTRANSPORTER | Mithelfende*r Angeh\u00f6rige*r |\r\nTRADITIONAL_SPIRITUAL_HEALER | Keine Angabe |\r\n\r\n@MartinWahnschaffe @FredrikSchaeferVitagroup @KernB I think the problem is that I missed an additional value for \"Selbstst\u00e4ndige*r\". We should be fine by just adding `SELF_EMPLOYED` and then migrating `BUTCHER` to that value. In this way, `BUSINESSMAN_WOMAN` and `BUTCHER` are disentangled.\r\n\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'SELF_EMPLOYED', 'Self-employed', '[{\"languageCode\":\"de-DE\",\"value\":\"Selbstst\u00e4ndige*r\"}]');\r\n\r\nUPDATE person SET occupationtype = 'SELF_EMPLOYED' WHERE occupationtype = 'BUTCHER';","issue_id":1663844247124,"origin_id":1279142610,"user_origin_id":70317594,"create_time":1665760580,"update_time":1665760580,"id":1667859281587,"updated_at":"2022-11-07T22:14:41.586000Z","created_at":"2022-11-07T22:14:41.586000Z"},{"_id":"63698351ea01ec786e8b0cff","body":"@kwa20 Thanks, that makes a lot more sense.\r\n\r\n@KernB This is the updated script:\r\n\r\n```\r\n-- Step 1: Insert values used in Germany into database if they have not been used in this particular database yet\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'FARMER', 'Selbstst\u00e4ndige*r Landwirt*in bzw. Genossenschaftsbauer\/b\u00e4uerin', '[{\"languageCode\":\"en\",\"value\":\"Farmer\"},{\"languageCode\":\"de-DE\",\"value\":\"Selbstst\u00e4ndige*r Landwirt*in bzw. Genossenschaftsbauer\/b\u00e4uerin\"}]' \r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'FARMER');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'RELIGIOUS_LEADER', 'Geistliche*r', '[{\"languageCode\":\"en\",\"value\":\"Religious leader\"},{\"languageCode\":\"de-DE\",\"value\":\"Geistliche*r\"}]'\r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'RELIGIOUS_LEADER');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'HOUSEWIFE', 'Hausfrau\/-mann', '[{\"languageCode\":\"en\",\"value\":\"Housewife\"},{\"languageCode\":\"de-DE\",\"value\":\"Hausfrau\/-mann\"}]'\r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'HOUSEWIFE');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'PUPIL_STUDENT', 'Ausbildung', '[{\"languageCode\":\"en\",\"value\":\"Pupil \/ student\"},{\"languageCode\":\"de-DE\",\"value\":\"Ausbildung\"}]'\r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'PUPIL_STUDENT');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nSELECT nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'WORKING_WITH_ANIMALS', 'Arbeiten mit Tieren', '[{\"languageCode\":\"en\",\"value\":\"Working with animals\"},{\"languageCode\":\"de-DE\",\"value\":\"Arbeiten mit Tieren\"}]'\r\nWHERE NOT EXISTS (SELECT value FROM customizableenumvalue WHERE value = 'WORKING_WITH_ANIMALS');\r\n\r\n-- Step 2: Insert new values used in Germany into database that have not existed or were misused before\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'CIVIL_SERVANT', 'Beamt*in, Richter*in, Berufssoldat*in', '[{\"languageCode\":\"en\",\"value\":\"Civil servant\"},{\"languageCode\":\"de-DE\",\"value\":\"Beamt*in, Richter*in, Berufssoldat*in\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'EMPLOYEE', 'Angestellte*r', '[{\"languageCode\":\"en\",\"value\":\"Employee\"},{\"languageCode\":\"de-DE\",\"value\":\"Angestellte*r\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'WORKER', 'Arbeiter*in', '[{\"languageCode\":\"en\",\"value\":\"Worker\"},{\"languageCode\":\"de-DE\",\"value\":\"Arbeiter*in\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'SELF_EMPLOYED', 'Selbstst\u00e4ndige*r', '[{\"languageCode\":\"en\",\"value\":\"Self-employed\"},{\"languageCode\":\"de-DE\",\"value\":\"Selbstst\u00e4ndige*r\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'ACADEMIC', 'Akademiker*in in freiem Beruf', '[{\"languageCode\":\"en\",\"value\":\"Academic\"},{\"languageCode\":\"de-DE\",\"value\":\"Akademiker*in in freiem Beruf\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'ASSISTING_FAMILY_MEMBER', 'Mithelfende*r Angeh\u00f6rige*r', '[{\"languageCode\":\"en\",\"value\":\"Assisting family member\"},{\"languageCode\":\"de-DE\",\"value\":\"Mithelfende*r Angeh\u00f6rige*r\"}]');\r\nINSERT INTO customizableenumvalue (id, uuid, creationdate, changedate, datatype, value, caption, translations)\r\nVALUES\r\n (nextval('entity_seq'), generate_base32_uuid(), now(), now(), 'OCCUPATION_TYPE', 'NOT_SPECIFIED', 'Keine Angabe', '[{\"languageCode\":\"en\",\"value\":\"Not specified\"},{\"languageCode\":\"de-DE\",\"value\":\"Keine Angabe\"}]');\r\n\r\n-- Step 3: Migrate occupation type entries in person table to conform to the corrected translations and meanings\r\nUPDATE person SET occupationtype = 'CIVIL_SERVANT' WHERE occupationtype = 'CHILD';\r\nUPDATE person SET occupationtype = 'EMPLOYEE' WHERE occupationtype = 'HUNTER_MEAT_TRADER';\r\nUPDATE person SET occupationtype = 'WORKER' WHERE occupationtype = 'MINER';\r\nUPDATE person SET occupationtype = 'ACADEMIC' WHERE occupationtype = 'BUSINESSMAN_WOMAN';\r\nUPDATE person SET occupationtype = 'SELF_EMPLOYED' WHERE occupationtype = 'BUTCHER';\r\nUPDATE person SET occupationtype = 'ASSISTING_FAMILY_MEMBER' WHERE occupationtype = 'TRANSPORTER';\r\nUPDATE person SET occupationtype = 'NOT_SPECIFIED' WHERE occupationtype = 'TRADITIONAL_SPIRITUAL_HEALER';\r\n\r\n-- Step 4: Delete occupation types that were previously misused and are no longer needed\r\nDELETE FROM customizableenumvalue WHERE datatype = 'OCCUPATION_TYPE' AND (value = 'CHILD' OR value = 'HUNTER_MEAT_TRADER' OR value = 'MINER' OR value = 'BUTCHER' OR value = 'BUSINESSMAN_WOMAN' OR value = 'TRANSPORTER' OR value = 'TRADITIONAL_SPIRITUAL_HEALER');\r\n```\r\n\r\nThe server needs to be restarted.","issue_id":1663844247124,"origin_id":1280399199,"user_origin_id":23217632,"create_time":1665991100,"update_time":1665991100,"id":1667859281600,"updated_at":"2022-11-07T22:14:41.600000Z","created_at":"2022-11-07T22:14:41.600000Z"},{"_id":"63698351ea01ec786e8b0d00","body":"@MartinWahnschaffe and @kwa20 one question about this: If the user setting is set to German, will all instances then have the same values displayed on UI after the script was executed?\r\n\r\nBackground of the question: On our test-instances the values are not identical (could be because the script was not executed on all instances, i am not sure about this) and if the case\/contact is shared via S2S and one value is selected, that does not exist on the other instance -> this breaks the whole sync of S2S","issue_id":1663844247124,"origin_id":1290894929,"user_origin_id":100844689,"create_time":1666718623,"update_time":1666718715,"id":1667859281618,"updated_at":"2022-11-07T22:14:41.618000Z","created_at":"2022-11-07T22:14:41.618000Z"},{"_id":"63698351ea01ec786e8b0d01","body":"There is a very minimal set of default occupation types (HEALTHCARE_WORKER and three others). All other occupation types are only present if they had been assigned to a person before the migration to the new customizable occupation types was done.\r\nSee https:\/\/github.com\/hzi-braunschweig\/SORMAS-Project\/issues\/5015#issuecomment-1180078511\r\n\r\nWith the script that was created in this issue the list of occupation types that will definitely be on the systems is a lot bigger, but there can still be occupation types present on one system and not present on another.\r\n\r\nThis will definitely cause problems for SORMAS-2-SORMAS that we need to take care of. \r\nFrom my point of view it would be best to create an issue that is about discussing how to deal with customizable enum values when syncing data:\r\n\r\nA. Reject the data\r\nB. Accept the data and automatically create an entry in the customizable enums tables - this value would then also be available for other data objects\r\nC. Make the whole customizable enum logic able to deal with values that are not defined in the database.\r\n\r\nBesides that I'm wondering whether we should define a full set of occupation types for Germany and reset any different occupation types assigned to persons or migrate those. \r\n\r\n@MateStrysewske ","issue_id":1663844247124,"origin_id":1293393146,"user_origin_id":23217632,"create_time":1666870468,"update_time":1666870468,"id":1667859281622,"updated_at":"2022-11-07T22:14:41.621000Z","created_at":"2022-11-07T22:14:41.621000Z"}]

### Problem Description SORMAS Version 1.75 moves the occupation type values into the customizable enum value table. Previously, original values have also been misused for other values by translating them...

SahaLinaPrueger