------------------------------------------------------------ revno: 13463 committer: Lars Helge Øverland <[email protected]> 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;
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
