Script for Exports issue caused by removal of Other Assement Date

Purpose:

This script will help fixing the data which caused export issues in Release 1.5.1.

Steps to reproduce the problem :

1) On any server running older version of EndTB, choose a patient and add X-Ray and Radiology form.

2) Under 'Other Radiological Tests', fill values for 'Xray, Date of Other Test' (only date field and leave other two fields empty in this section) and then save the form.

3) Upgrade to 1.5.1

4) New exports show up as failed in UI after the upgrade (as mentioned in 3rd step).

5) Run the script attached to the mail

6) Exports should run now successfully.

The link to concept 'Xray, Date of Other Test' was removed in Release 1.5.1 . This problem happens only if data before the upgrade had only 'Xray, Date of Other Test' filled but other two Radiological Tests fields were left empty. Hence this error will not be there in any fresh 1.5.1 installations.

Sql Query

Below is the sql query to be run on Bahmni openmrs mysql database.

Query

UPDATE obs SET voided = 1
WHERE 
	concept_id = 1607 AND 
	obs_id IN (SELECT childCounts.obs_id FROM
            		(
						SELECT po.obs_id, COUNT(co.obs_id) AS count
            			FROM obs AS po
            			LEFT OUTER JOIN obs AS co ON co.obs_group_id = po.obs_id AND co.voided = 0
            			WHERE po.concept_id = 1607 AND po.voided = 0
            			GROUP BY po.obs_id
					) AS childCounts WHERE childCounts.count = 0
			  )

On this page: