dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 13463
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Sun 2013-12-29 16:55:10 +0100
message:
  Script for generating random longitude andlatitude for events based on facility location
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	2013-12-29 15:07:20 +0000
+++ resources/sql/div.sql	2013-12-29 15:55:10 +0000
@@ -113,8 +113,8 @@
 -- Fetch longitude/latitude from organisationunit
 
 select name, coordinates, 
-regexp_replace(coordinates, '\[(-?\d+\.?\d*)[,](-?\d+\.?\d*)\]', '\1') as longitude,
-regexp_replace(coordinates, '\[(-?\d+\.?\d*)[,](-?\d+\.?\d*)\]', '\2') as latitude 
+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';
 
@@ -163,7 +163,7 @@
 join _categoryoptioncomboname cn
 on (cc.categoryoptioncomboid=cn.categoryoptioncomboid);
 
--- Populate dashboards for all users (7666 is userinfoid for target dashboard, replace with preferred id)
+-- (Write) Populate dashboards for all users (7666 is userinfoid for target dashboard, replace with preferred id)
 
 insert into usersetting (userinfoid, name, value)
 select userinfoid, 'dashboardConfig', (
@@ -177,11 +177,23 @@
   from usersetting
   where name='dashboardConfig')
   
--- Reset password to "district" for account with given username
+-- (Write) Reset password to "district" for account with given username
 
 update users set password='48e8f1207baef1ef7fe478a57d19f2e5' where username='admin';
 
--- Insert random org unit codes
+-- (Write) Generate random coordinates based on org unit location for events
+
+update programstageinstance psi
+set longitude = (
+  select ( cast( regexp_replace( coordinates, '\[(-?\d+\.?\d*)[,](-?\d+\.?\d*)\]', '\1' ) as double precision ) + ( random() / 10 ) )
+  from organisationunit ou
+  where psi.organisationunitid=ou.organisationunitid ),
+latitude = (
+  select ( cast( regexp_replace( coordinates, '\[(-?\d+\.?\d*)[,](-?\d+\.?\d*)\]', '\2' ) as double precision ) + ( random() / 10 ) )
+  from organisationunit ou
+  where psi.organisationunitid=ou.organisationunitid );
+
+-- (Write) Insert random org unit codes
 
 create function setrandomcode() returns integer AS $$
 declare ou integer;