dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 18174
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2015-02-04 22:42:37 +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-22 18:25:25 +0000
+++ resources/sql/div.sql	2015-02-04 21:42:37 +0000
@@ -1,4 +1,6 @@
 
+-- DATA ELEMENTS
+
 -- Data elements and frequency with average agg operator (higher than yearly negative for data mart performance)
 
 select d.dataelementid, d.name as dataelement, pt.name as periodtype from dataelement d 
@@ -26,13 +28,34 @@
 select count(*) from dataelement de
 where (select count(*) from datavalue dv where de.dataelementid=dv.dataelementid) < 100;
 
--- Duplicate codes
+-- Duplicate data element codes
 
 select code, count(code) as count
 from dataelement
 group by code
 order by count desc;
 
+-- Display overview of data elements and related category option combos
+
+select de.uid as dataelement_uid, de.name as dataelement_name, de.code as dataelement_code, coc.uid as optioncombo_uid, cocn.categoryoptioncomboname as optioncombo_name 
+from _dataelementcategoryoptioncombo dcoc 
+inner join dataelement de on dcoc.dataelementuid=de.uid 
+inner join categoryoptioncombo coc on dcoc.categoryoptioncombouid=coc.uid 
+inner join _categoryoptioncomboname cocn on coc.categoryoptioncomboid=cocn.categoryoptioncomboid 
+order by de.name;
+
+-- (Write) Remove data elements from data sets which are not part of sections
+
+delete from datasetmembers dsm
+where dataelementid not in (
+  select dataelementid from sectiondataelements ds
+  inner join section s on (ds.sectionid=s.sectionid)
+  where s.datasetid=dsm.datasetid)
+and dsm.datasetid=1979200;
+
+
+-- CATEGORIES
+
 -- Exploded category option combo view
 
 select cc.categorycomboid, cc.name as categorycomboname, cn.* from _categoryoptioncomboname cn
@@ -40,15 +63,35 @@
 join categorycombo cc using(categorycomboid)
 order by categorycomboname, categoryoptioncomboname;
 
--- Groups orgunits into groups based on the text match in the where clause for the orgunit group with the given id
-
-insert into orgunitgroupmembers(orgunitgroupid,organisationunitid)
-select 22755 as orgunitgroupid,ou.organisationunitid as organisationunitid from organisationunit ou 
-where lower(name) like '%dispensary%'
-and not exists (
-select orgunitgroupid from orgunitgroupmembers om 
-where ou.organisationunitid=om.organisationunitid
-and om.orgunitgroupid=22755);
+-- Display category option combo identifier and name
+
+select cc.categoryoptioncomboid as id, uid, categoryoptioncomboname as name, code
+from categoryoptioncombo cc
+join _categoryoptioncomboname cn
+on (cc.categoryoptioncomboid=cn.categoryoptioncomboid);
+
+-- Display overview of category option combo
+
+select coc.categoryoptioncomboid as coc_id, coc.uid as coc_uid, co.categoryoptionid as co_id, co.name as co_name, ca.categoryid as ca_id, ca.name as ca_name, cc.categorycomboid as cc_id, cc.name as cc_name
+from categoryoptioncombo coc 
+inner join categoryoptioncombos_categoryoptions coo on coc.categoryoptioncomboid=coo.categoryoptioncomboid
+inner join dataelementcategoryoption co on coo.categoryoptionid=co.categoryoptionid
+inner join categories_categoryoptions cco on co.categoryoptionid=cco.categoryoptionid
+inner join dataelementcategory ca on cco.categoryid=ca.categoryid
+inner join categorycombos_optioncombos ccoc on coc.categoryoptioncomboid=ccoc.categoryoptioncomboid
+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';
+
+
+-- ORGANISATION UNITS
 
 -- Facility overview
 
@@ -63,6 +106,71 @@
 where ous.level=5
 order by province, county, district, ou.name;
 
+-- Turn longitude/latitude around for organisationunit coordinates (adjust the like clause)
+
+update organisationunit set coordinates=regexp_replace(coordinates,'\[(.+?\..+?),(.+?\..+?)\]','[\2,\1]')
+where coordinates like '[0%'
+and featuretype='Point';
+
+-- Fetch longitude/latitude from organisationunit
+
+select name, coordinates, 
+cast( regexp_replace( coordinates, '\[(-?\d+\.?\d*)[,](-?\d+\.?\d*)\]', '\1' ) as double precision ) as longitude,
+cast( regexp_replace( coordinates, '\[(-?\d+\.?\d*)[,](-?\d+\.?\d*)\]', '\2' ) as double precision ) as latitude 
+from organisationunit
+where featuretype='Point';
+
+-- Identify empty groups
+
+select 'Data element group' as type, o.name as name
+from dataelementgroup o
+where not exists (
+  select * from dataelementgroupmembers
+  where dataelementgroupid=o.dataelementgroupid)
+union all
+select 'Indicator group' as type, o.name as name
+from indicatorgroup o
+where not exists (
+  select * from indicatorgroupmembers
+  where indicatorgroupid=o.indicatorgroupid)
+union all
+select 'Organisation unit group' as type, o.name as name
+from orgunitgroup o
+where not exists (
+  select * from orgunitgroupmembers
+  where orgunitgroupid=o.orgunitgroupid)
+order by type,name;
+
+-- Nullify coordinates with longitude outside range (adjust where clause values)
+
+update organisationunit set coordinates=null
+where featuretype='Point'
+and (
+  cast(substring(coordinates from '\[(.+?\..+?),.+?\..+?\]') as double precision) < 32
+  or cast(substring(coordinates from '\[(.+?\..+?),.+?\..+?\]') as double precision) > 43
+);
+
+-- (Write) Replace first digit in invalid uid with letter a
+
+update organisationunit set uid = regexp_replace(uid,'\d','a') where uid SIMILAR TO '[0-9]%';
+
+-- (Write) Insert random org unit codes
+
+create function setrandomcode() returns integer AS $$
+declare ou integer;
+begin
+for ou in select organisationunitid from _orgunitstructure where level=6 loop
+  execute 'update organisationunit set code=(select substring(cast(random() as text),5,6)) where organisationunitid=' || ou;
+end loop;
+return 1;
+end;
+$$ language plpgsql;
+
+select setrandomcode();
+
+
+-- USERS
+
 -- Compare user roles (lists what is in the first role but not in the second)
 
 select authority from userroleauthorities where userroleid=33706 and authority not in (select authority from userroleauthorities where userroleid=21504);
@@ -110,115 +218,16 @@
 inner join userroleauthorities ura on ur.userroleid=ura.userroleid 
 where ura.authority = 'ALL';
 
--- Turn longitude/latitude around for organisationunit coordinates (adjust the like clause)
-
-update organisationunit set coordinates=regexp_replace(coordinates,'\[(.+?\..+?),(.+?\..+?)\]','[\2,\1]')
-where coordinates like '[0%'
-and featuretype='Point';
-
--- Fetch longitude/latitude from organisationunit
-
-select name, coordinates, 
-cast( regexp_replace( coordinates, '\[(-?\d+\.?\d*)[,](-?\d+\.?\d*)\]', '\1' ) as double precision ) as longitude,
-cast( regexp_replace( coordinates, '\[(-?\d+\.?\d*)[,](-?\d+\.?\d*)\]', '\2' ) as double precision ) as latitude 
-from organisationunit
-where featuretype='Point';
-
--- Nullify coordinates with longitude outside range (adjust where clause values)
-
-update organisationunit set coordinates=null
-where featuretype='Point'
-and (
-  cast(substring(coordinates from '\[(.+?\..+?),.+?\..+?\]') as double precision) < 32
-  or cast(substring(coordinates from '\[(.+?\..+?),.+?\..+?\]') as double precision) > 43
-);
-
--- Identify empty groups
-
-select 'Data element group' as type, o.name as name
-from dataelementgroup o
-where not exists (
-  select * from dataelementgroupmembers
-  where dataelementgroupid=o.dataelementgroupid)
-union all
-select 'Indicator group' as type, o.name as name
-from indicatorgroup o
-where not exists (
-  select * from indicatorgroupmembers
-  where indicatorgroupid=o.indicatorgroupid)
-union all
-select 'Organisation unit group' as type, o.name as name
-from orgunitgroup o
-where not exists (
-  select * from orgunitgroupmembers
-  where orgunitgroupid=o.orgunitgroupid)
-order by type,name;
-
--- Display overview of data elements and related category option combos
-
-select de.uid as dataelement_uid, de.name as dataelement_name, de.code as dataelement_code, coc.uid as optioncombo_uid, cocn.categoryoptioncomboname as optioncombo_name 
-from _dataelementcategoryoptioncombo dcoc 
-inner join dataelement de on dcoc.dataelementuid=de.uid 
-inner join categoryoptioncombo coc on dcoc.categoryoptioncombouid=coc.uid 
-inner join _categoryoptioncomboname cocn on coc.categoryoptioncomboid=cocn.categoryoptioncomboid 
-order by de.name;
-
--- Display category option combo identifier and name
-
-select cc.categoryoptioncomboid as id, uid, categoryoptioncomboname as name, code
-from categoryoptioncombo cc
-join _categoryoptioncomboname cn
-on (cc.categoryoptioncomboid=cn.categoryoptioncomboid);
-
--- Display overview of category option combo
-
-select coc.categoryoptioncomboid as coc_id, coc.uid as coc_uid, co.categoryoptionid as co_id, co.name as co_name, ca.categoryid as ca_id, ca.name as ca_name, cc.categorycomboid as cc_id, cc.name as cc_name
-from categoryoptioncombo coc 
-inner join categoryoptioncombos_categoryoptions coo on coc.categoryoptioncomboid=coo.categoryoptioncomboid
-inner join dataelementcategoryoption co on coo.categoryoptionid=co.categoryoptionid
-inner join categories_categoryoptions cco on co.categoryoptionid=cco.categoryoptionid
-inner join dataelementcategory ca on cco.categoryid=ca.categoryid
-inner join categorycombos_optioncombos ccoc on coc.categoryoptioncomboid=ccoc.categoryoptioncomboid
-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 *
-from datavalue dv
-where dv.periodid in (
-  select pe.periodid
-  from period pe
-  where pe.startdate < '1960-01-01'
-  or pe.enddate > '2020-01-01');
-
--- Display events out of reasonable time range
-
-select *
-from programstageinstance psi
-where psi.executiondate < '1960-01-01'
-or psi.executiondate > '2020-01-01';
-
--- Data value exploded view
-
-select de.name as dename, de.uid as deuid, pe.startdate as pestart, pe.enddate as peend, pt.name as ptname, 
-ou.name as ouname, ou.uid as ouuid, coc.uid as cocuid, coc.categoryoptioncomboid as cocid, aoc.uid as aocuid, aoc.categoryoptioncomboid as aocid, dv.value as dvval
-from datavalue dv
-inner join dataelement de on (dv.dataelementid=de.dataelementid)
-inner join period pe on (dv.periodid=pe.periodid)
-inner join periodtype pt on (pe.periodtypeid=pt.periodtypeid)
-inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)
-inner join categoryoptioncombo coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)
-inner join categoryoptioncombo aoc on (dv.attributeoptioncomboid=aoc.categoryoptioncomboid)
-limit 10000;
+-- (Write) MD5 set password to "district" for admin user
+
+update users set password='48e8f1207baef1ef7fe478a57d19f2e5' where username='admin';
+
+-- (Write) Bcrypt set password to "district" for admin user
+
+update users set password='$2a$10$wjLPViry3bkYEcjwGRqnYO1bT2Kl.ZY0kO.fwFDfMX53hitfx5.3C' where username='admin';
+
+
+-- VALIDATION RULES
 
 -- Display validation rules which includes the given data element uid
 
@@ -229,6 +238,49 @@
 where le.expression ~ 'OuudMtJsh2z'
 or re.expression  ~ 'OuudMtJsh2z'
   
+-- (Write) Delete validation rules and clean up expressions
+
+delete from validationrule where name = 'abc';
+delete from expressiondataelement where expressionid not in (
+  select leftexpressionid from validationrule
+  union all
+  select rightexpressionid from validationrule
+);
+delete from expression where expressionid not in (
+  select leftexpressionid from validationrule
+  union all
+  select rightexpressionid from validationrule
+);
+
+-- DASHBOARDS
+
+-- (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);
+  
+  
+-- DATA VALUES
+
+-- Display data out of reasonable time range
+
+select *
+from datavalue dv
+where dv.periodid in (
+  select pe.periodid
+  from period pe
+  where pe.startdate < '1960-01-01'
+  or pe.enddate > '2020-01-01');
+
 -- (Write) Delete all data values for category combo
 
 delete from datavalue where categoryoptioncomboid in (
@@ -246,13 +298,49 @@
   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';
-
--- (Write) Bcrypt set password to "district" for admin user
-
-update users set password='$2a$10$wjLPViry3bkYEcjwGRqnYO1bT2Kl.ZY0kO.fwFDfMX53hitfx5.3C' where username='admin';
+
+-- Data value exploded view
+
+select de.name as dename, de.uid as deuid, pe.startdate as pestart, pe.enddate as peend, pt.name as ptname, 
+ou.name as ouname, ou.uid as ouuid, coc.uid as cocuid, coc.categoryoptioncomboid as cocid, aoc.uid as aocuid, aoc.categoryoptioncomboid as aocid, dv.value as dvval
+from datavalue dv
+inner join dataelement de on (dv.dataelementid=de.dataelementid)
+inner join period pe on (dv.periodid=pe.periodid)
+inner join periodtype pt on (pe.periodtypeid=pt.periodtypeid)
+inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)
+inner join categoryoptioncombo coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)
+inner join categoryoptioncombo aoc on (dv.attributeoptioncomboid=aoc.categoryoptioncomboid)
+limit 10000;
+
+-- (Write) Move startdate and enddate in period to next year
+
+update period set 
+startdate = (startdate + interval '1 year')::date,
+enddate = (enddate + interval '1 year')::date
+where extract(year from startdate) = 2013;
+
+
+-- EVENTS
+
+-- Display events out of reasonable time range
+
+select count(*)
+from programstageinstance psi
+where psi.executiondate < '1960-01-01'
+or psi.executiondate > '2020-01-01';
+
+-- Delete events out of reasonable time range
+
+delete from trackedentitydatavalue tdv
+where tdv.programstageinstanceid in (
+  select psi.programstageinstanceid
+  from programstageinstance psi
+  where psi.executiondate < '1960-01-01'
+  or psi.executiondate > '2020-01-01');
+
+delete from programstageinstance psi
+where psi.executiondate < '1960-01-01'
+or psi.executiondate > '2020-01-01';
 
 -- (Write) Generate random coordinates based on org unit location for events
 
@@ -266,13 +354,6 @@
   from organisationunit ou
   where psi.organisationunitid=ou.organisationunitid );
 
--- (Write) Move startdate and enddate in period to next year
-
-update period set 
-startdate = (startdate + interval '1 year')::date,
-enddate = (enddate + interval '1 year')::date
-where extract(year from startdate) = 2013;
-
 -- (Write) Move programstageinstance and programinstance to next year
 
 update programstageinstance set 
@@ -289,58 +370,4 @@
 created = (created + interval '1 year'),
 lastupdated = (lastupdated + interval '1 year');
 
--- (Write) Replace first digit in invalid uid with letter a
-
-update organisationunit set uid = regexp_replace(uid,'\d','a') where uid SIMILAR TO '[0-9]%';
-
--- (Write) Delete validation rules and clean up expressions
-
-delete from validationrule where name = 'abc';
-delete from expressiondataelement where expressionid not in (
-  select leftexpressionid from validationrule
-  union all
-  select rightexpressionid from validationrule
-);
-delete from expression where expressionid not in (
-  select leftexpressionid from validationrule
-  union all
-  select rightexpressionid from validationrule
-);
-
--- (Write) Insert random org unit codes
-
-create function setrandomcode() returns integer AS $$
-declare ou integer;
-begin
-for ou in select organisationunitid from _orgunitstructure where level=6 loop
-  execute 'update organisationunit set code=(select substring(cast(random() as text),5,6)) where organisationunitid=' || ou;
-end loop;
-return 1;
-end;
-$$ language plpgsql;
-
-select setrandomcode();
-
--- (Write) Remove data elements from data sets which are not part of sections
-
-delete from datasetmembers dsm
-where dataelementid not in (
-  select dataelementid from sectiondataelements ds
-  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);