dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 18109
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2015-01-22 19:25:25 +0100
message:
  Scripts
modified:
  resources/sql/div.sql


--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription

=== modified file 'resources/sql/div.sql'
--- resources/sql/div.sql	2015-01-21 20:27:48 +0000
+++ resources/sql/div.sql	2015-01-22 18:25:25 +0000
@@ -182,6 +182,14 @@
 inner join categorycombo cc on ccoc.categorycomboid=cc.categorycomboid
 where coc.categoryoptioncomboid=2118430;
 
+-- Get category option combos linked to category option
+
+select coc.categoryoptioncomboid as coc_id, coc.uid as coc_uid, co.categoryoptionid as co_id, co.name as co_name
+from categoryoptioncombo coc 
+inner join categoryoptioncombos_categoryoptions coo on coc.categoryoptioncomboid=coo.categoryoptioncomboid
+inner join dataelementcategoryoption co on coo.categoryoptionid=co.categoryoptionid
+where co.uid='LPeJEUjotaB';
+
 -- Display data out of reasonable time range
 
 select *
@@ -229,6 +237,15 @@
 on (cc.categoryoptioncomboid=co.categoryoptioncomboid)
 where categorycomboid=12414 );
 
+-- (Write) Delete all data values for an attribute category option
+
+delete from datavalue dv
+where dv.attributeoptioncomboid in (
+  select coc.categoryoptioncomboid from categoryoptioncombo coc
+  inner join categoryoptioncombos_categoryoptions coo on coc.categoryoptioncomboid=coo.categoryoptioncomboid
+  inner join dataelementcategoryoption co on coo.categoryoptionid=co.categoryoptionid
+  where co.uid='LPeJEUjotaB');
+
 -- (Write) MD5 set password to "district" for admin user
 
 update users set password='48e8f1207baef1ef7fe478a57d19f2e5' where username='admin';
@@ -312,3 +329,18 @@
   inner join section s on (ds.sectionid=s.sectionid)
   where s.datasetid=dsm.datasetid)
 and dsm.datasetid=1979200;
+
+-- (Write) Remove orphaned dashboard items
+
+delete from dashboarditem di 
+where di.dashboarditemid not in (
+  select dashboarditemid from dashboard_items)
+and di.dashboarditemid not in (
+  select dashboarditemid from dashboarditem_reports)
+and di.dashboarditemid not in (
+  select dashboarditemid from dashboarditem_reporttables)
+and di.dashboarditemid not in (
+  select dashboarditemid from dashboarditem_resources)
+and di.dashboarditemid not in (
+  select dashboarditemid from dashboarditem_users);
+