dhis2-devs team mailing list archive

["jason.p.pickering" <jason.p.pickering@xxxxxxxxx>]

Public bug reported:

An additional column needs to be added to the "orgunitgroupmembers"
table in DHIS2 in order to improve compatibility with data imports from
DHIS 1.4. The DHIS 1.4 orgunitgroupmembers contains the following
columns

OrgUnitGroupID  OrgUnitID       Active  LastUserID      LastUpdated

while DHIS 2 orgunitgroupmembers contains these two columns.

orgunitgroupid  orgunitid

During the generation of the exclusive organization group members
tables, DHIS 1.4 seems to take into account whether a particular orgunit
is active in a group, or not. If the orgunit is not active, it is not
included in the exclusive group resource tables.


The following query is meant to return a crosstab listing of orgunits, and the (exclusive and compulsory) groups that they belong to,  from the DHIS 2 database.

SELECT  a.organisationunitid, a.name, a.parentid, a.shortname, a.code, d.name, e.name, h.name, i.name from organisationunit a
JOIN orgunitgroupmembers b on a.organisationunitid = b.organisationunitid
JOIN orgunitgroupsetmembers c on b.orgunitgroupid = c.orgunitgroupid
JOIN orgunitgroup d on b.orgunitgroupid = d.orgunitgroupid
JOIN orgunitgroupset e on c.orgunitgroupsetid = e.orgunitgroupsetid and e.name ~* 'Type'

JOIN orgunitgroupmembers f on a.organisationunitid = f.organisationunitid
JOIN orgunitgroupsetmembers g on f.orgunitgroupid = g.orgunitgroupid
JOIN orgunitgroup h on f.orgunitgroupid = h.orgunitgroupid
JOIN orgunitgroupset i on g.orgunitgroupsetid = i.orgunitgroupsetid and i.name ~* 'Ownership'

and the results of the query for the same orgunit..

3662;"co Kapilamikwa Health Post";3576;"Kapilamikwa HP";"206011";"Unknown Type";"Type";"Government";"Ownership"
3662;"co Kapilamikwa Health Post";3576;"Kapilamikwa HP";"206011";"Unknown Type";"Type";"Unknown Ownership";"Ownership"
3662;"co Kapilamikwa Health Post";3576;"Kapilamikwa HP";"206011";"Health Post";"Type";"Government";"Ownership"
3662;"co Kapilamikwa Health Post";3576;"Kapilamikwa HP";"206011";"Health Post";"Type";"Unknown Ownership";"Ownership"

Both the "Type" and "Ownership" groups are compulsory as well as
exclusive, however since the data was imported from DHIS 1.4, and the
"active" column was not taken into consideration, three of the rows
returned are bogus and should be excluded.

** Affects: dhis2
     Importance: Undecided
         Status: New

-- 
DHIS2 does not import "active"  orgunitgroupmembers column during a DHIS 1.4 import
https://bugs.launchpad.net/bugs/358933
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.

Status in District Health Information Software 2: New

Bug description:
An additional column needs to be added to the "orgunitgroupmembers" table in DHIS2 in order to improve compatibility with data imports from DHIS 1.4. The DHIS 1.4 orgunitgroupmembers contains the following columns

OrgUnitGroupID 	OrgUnitID 	Active 	LastUserID 	LastUpdated

while DHIS 2 orgunitgroupmembers contains these two columns. 

orgunitgroupid  orgunitid

During the generation of the exclusive organization group members tables, DHIS 1.4 seems to take into account whether a particular orgunit is active in a group, or not. If the orgunit is not active, it is not included in the exclusive group resource tables.


The following query is meant to return a crosstab listing of orgunits, and the (exclusive and compulsory) groups that they belong to,  from the DHIS 2 database.

SELECT  a.organisationunitid, a.name, a.parentid, a.shortname, a.code, d.name, e.name, h.name, i.name from organisationunit a
JOIN orgunitgroupmembers b on a.organisationunitid = b.organisationunitid
JOIN orgunitgroupsetmembers c on b.orgunitgroupid = c.orgunitgroupid
JOIN orgunitgroup d on b.orgunitgroupid = d.orgunitgroupid
JOIN orgunitgroupset e on c.orgunitgroupsetid = e.orgunitgroupsetid and e.name ~* 'Type'

JOIN orgunitgroupmembers f on a.organisationunitid = f.organisationunitid
JOIN orgunitgroupsetmembers g on f.orgunitgroupid = g.orgunitgroupid
JOIN orgunitgroup h on f.orgunitgroupid = h.orgunitgroupid
JOIN orgunitgroupset i on g.orgunitgroupsetid = i.orgunitgroupsetid and i.name ~* 'Ownership'

and the results of the query for the same orgunit..

3662;"co Kapilamikwa Health Post";3576;"Kapilamikwa HP";"206011";"Unknown Type";"Type";"Government";"Ownership"
3662;"co Kapilamikwa Health Post";3576;"Kapilamikwa HP";"206011";"Unknown Type";"Type";"Unknown Ownership";"Ownership"
3662;"co Kapilamikwa Health Post";3576;"Kapilamikwa HP";"206011";"Health Post";"Type";"Government";"Ownership"
3662;"co Kapilamikwa Health Post";3576;"Kapilamikwa HP";"206011";"Health Post";"Type";"Unknown Ownership";"Ownership"

Both the "Type" and "Ownership" groups are compulsory as well as exclusive, however since the data was imported from DHIS 1.4, and the "active" column was not taken into consideration, three of the rows returned are bogus and should be excluded.