Incorrect registration of case if consent form is registered before blood referral form and no pathology referral form exists

[Nicklas Nordborg]

1. The usual order of registration is that blood is registered before consent and results in one 'patient' item and one 'blood' item with the consent information stored as annotations to the blood item.

2. A less common case is that the consent form is registered before the blood referral form. This results in one 'case' item, one 'patient' item and one 'blood' item. The consent information is stored as annotations to both the blood and case items.

The cause of this is that when the consent form is registered first a 'temporary' case is created since there is otherwise no place to store the information. The assumption is that a pathology referral form will be received later and the pathology registration wizard handles this case and link or delete the temporary case as needed.

The blood registration wizard must be fixed to handle this situation in a similar manner. Eg. the case should be deleted so that the irrespective of the order of registration (A or B) the end result is always the same (one patient and one blood).

All currently incorrectly registered 'case' items should be deleted manually. Unfortunately there is no easy way to find which cases that should be deleted. Use the following procedure as a starting point:

• List all sample with type='case' and no parent item.

• For each of the found items, check if a blood item with the same name + '.b' exists or not. If a blood item exists, the case can be deleted. If no blood exists, the case should be kept.

This should be possible to do manually given that the number of cases is not too high, otherwise an external program might be needed to help filtering. By exporting the above list with case names and also a list with all blood sample names, it should be possible to find out which names that are found in both lists (eg. with Excel).

[Nicklas Nordborg]

(In [1788]) References #453: Incorrect registration of case if consent form is registered before blood referral form and no pathology referral form exists

This should fix the problem with the registration wizard. Should verify that everything is ok also by checking on the relevant cases on the production server.

[Nicklas Nordborg]

Here is an outline of a manual procedure for finding out which temporary cases that has or hasn't a related blood item. The procedure requires manual execution of SQL directly on the database so be careful to not destroy anything.

1. Find out the internal ID for the Blood and Case subtypes by logging into BASE using the web interface. Go to Administrate -> Types -> Item subtypes and add the 'ID column to the table. Find the Blood and Case entries and write down their ID values. From now on, we'll use B_ID as the ID of the Blood subtype and C_ID as the ID of the Case subtype.
2. Connect to the database using a SQL tool that you are comfortable with (for example, MySQL workbench).
3. Find out how many temorary cases there are by executing the following SQL:

   select c.id, c.name from `BioMaterials` c 
   where c.subtype_id=C_ID and c.parent_id is null
   

   Verify that the result is the same as doing the same on the web interface. Eg. filter samples on Type=Case and Parent items=<empty>.

4. Find out which cases that have a corresponding blood sample. We do this by a self left join using the case name as a search parameter. The blood name is simply the case name + '.b':

   select c.id, c.name, b.id, b.name from `BioMaterials` c 
   left join `BioMaterials` b 
     on b.name = concat(c.name, '.b') and b.subtype_id=B_ID
   where c.subtype_id=C_ID and c.parent_id is null 
   

   The query should still return the same number of rows, but with two more columns containing the matched blood item (id and name) or null values if there is no matched blood item. Replace the 'left join' with an 'inner join' to only see the cases that also has matching blood item.

From here we can simply use the web interface to manually remove those case items which also have a matching blood item. The case items with a null blood item should be kept.

For the bold, we can issue an SQL that marks the case items for removal (eg. put in the trashcan). They can then easily be checked and deleted from the web interface.

update `BioMaterials` set removed=true where id in (
  select c.id from `BioMaterials` c 
  inner join `BioMaterials` b 
    on b.name = concat(c.name, '.b') and b.subtype_id=B_ID
  where c.subtype_id=C_ID and c.parent_id is null 
)