------------------------------------------------------------ revno: 8746 committer: Lars Helge Øverland <[email protected]> branch nick: dhis2 timestamp: Mon 2012-10-29 11:01:44 +0100 message: User overview script 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 2012-09-13 14:34:32 +0000 +++ resources/sql/div.sql 2012-10-29 10:01:44 +0000 @@ -73,3 +73,20 @@ -- Compare user roles select authority from userroleauthorities where userroleid=33706 and authority not in (select authority from userroleauthorities where userroleid=21504); + +-- User overview (Postgres only) + +select u.username, u.lastlogin, ui.surname, ui.firstname, ui.email, ui.phonenumber, ui.jobtitle, ( + select array_to_string( array( + select name from userrole ur + join userrolemembers urm using(userroleid) + where urm.userid=u.userid), ', ' ) + ) as userroles, ( + select array_to_string( array( + select name from organisationunit ou + join usermembership um using(organisationunitid) + where um.userinfoid=ui.userinfoid), ', ' ) + ) as orgunits +from users u +join userinfo ui on u.userid=ui.userinfoid +order by u.username;
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
