Update identifier assignment

mahalakshme commented 3 weeks ago

https://avni.freshdesk.com/a/tickets/3618

Issue:

Identifier Prefix used in CSV upload in 2021 was by mistake assigned to another user rammus@jscs. Client has replied to change the user's prefix to RR from RM. But already 70 unused identifiers with RM prefix is sitting in rammus mobile app.

Need:

Made it product card for the below reasons:

The above to do it right, might need some product coding context.
since it has already got delayed a lot when the ticket was assigned to other team.

AC:

Update prefix of identifiers of enrolments created by rammus@jscs to RR from RM
The above user on fresh sync need to receive identifiers with RR prefix.
Identifier prefix and identifier start, end have been updated with RR prefix from RM by the client

Technical suggestions:

We might be in need to ask the user to sync before doing the needed updates and the user should not enrol any individuals until the above updates are done. After the updates for prefix are done, then user need to fresh sync.
Updates of prefix seem to be required in identifier_user_assignment, identifier_assignment, and program_enrolment - observations

himeshr commented 2 weeks ago

Assuming, that user identifier prefix is already update from "RM" to "RR".. Ran the above sql correction on prerelease db.

himeshr commented 2 weeks ago

Fast-sync db exists, which could interfere with the corrections, so archived it on s3. Catchment_id: 52b40a21-4ff3-409f-9d78-b1c1b455cd4d

mahalakshme commented 2 weeks ago

@himeshr have added comments in the script

himeshr commented 2 weeks ago

@himeshr have added comments in the script

Responded to them

mahalakshme commented 2 weeks ago

@himeshr have responded to them. we can get on a call if you think I am missing something.

mahalakshme commented 2 weeks ago

@himeshr if you think syncing in the mobile app will help in testing - you can itself do the needful and run this on prod. You can coordinate with the user for the same. Let me know if you think otherwise.

himeshr commented 2 weeks ago

Validated on pre-release that the data-fix is effective and fresh sync completes successfully. Also, enroling new user to sickle-cell program, had the new RR series identifier used for the user.


set role jscs;

select pe.id, i.first_name, i.address_id, observations ->> '8f852186-ccf1-4898-b581-99b37bcae2f1'
FROM program_enrolment pe
         join identifier_assignment ia on ia.program_enrolment_id = pe.id
join individual i on i.id = pe.individual_id
where ia.assigned_to_user_id = 8722
  and pe.organisation_id = 21
  and observations ->> '8f852186-ccf1-4898-b581-99b37bcae2f1' like 'RR%'
  and pe.last_modified_date_time > current_date;
-- id,first_name,address_id,?column?
-- "414702","adhraj yadav","2679","RR00317"

select program_enrolment_id, identifier
from identifier_assignment
where assigned_to_user_id = 8722
  and identifier like 'RR%'
  and organisation_id = 21
  and program_enrolment_id is not null
  and last_modified_date_time > current_date;
-- program_enrolment_id,identifier
-- "414702","RR00317"

himeshr commented 2 weeks ago

jscsIdentifierCorrectionProdSql.txt

himeshr commented 2 weeks ago

Verified the fix after syncing user on prod apk.

avniproject / avni-server