dhis2-devs team mailing list archive

[Greg Rowles <greg.rowles@xxxxxxxxx>]

I guess there's no short way of explaining this.

I'm using a SQL query (that allows for variables) to create a count of
orgunits (coming from a specific ou-level) per orgunit groupset. I've set
up an example on the dhis2 demo site here:
https://apps.dhis2.org/demo/api/sqlViews/mLsbp3ds7pF/data?var=idlevel:idlevel4&var=parentidlevel:uidlevel1&var=ougroupset:Facility%20Type&var=ou:ImspTQPwCqd&var=pe:2015

This lists and counts the orgunit-groups inside groupset [Facility Type]
for the year 2015 summarised up to the national level
(parentidlevel:uidlevel1) under region ou:ImspTQPwCqd. It shows a break
down across 3 columns
 - open (those in operation in the year),
 - created (opened in the year)
 - closed (in the year)

The raw query looks like this:

SELECT yperiod as pe, ouuid as ou, OUgroup, Sum(Open) as Open,
Sum(OpenedNew) as Created, Sum(Closing) as Closed FROM ( SELECT
"P".periodid, "P".startdate, "P".enddate, to_char("P".startdate, 'YYYY') as
yPeriod, "P".periodtypeid, "ST".organisationunitid, "${parentidlevel}" as
ouuid, "ST"."${ougroupset}" as OUgroup, "O".uid, "O".name,
CAST("O".openingdate as DATE) as ValidFrom, CAST(coalesce("O".closeddate,
'9999-12-31') as DATE) as ValidTo, ( SELECT CASE WHEN
CAST(coalesce("O".closeddate, '9999-12-31') as DATE) > CAST("P".startdate
as DATE) THEN 1 ELSE 0 END AS Result ) as Open, ( SELECT CASE WHEN
CAST("O".openingdate as DATE) >= CAST("P".startdate as DATE) AND
CAST("O".openingdate as DATE) <= CAST("P".enddate as DATE) THEN 1 ELSE 0
END AS Result ) as OpenedNew, ( SELECT CASE WHEN
CAST(coalesce("O".closeddate, '9999-12-31') as DATE) >= CAST("P".startdate
as DATE) AND CAST(coalesce("O".closeddate, '9999-12-31') as DATE) <=
CAST("P".enddate as DATE) THEN 1 ELSE 0 END AS Result ) as Closing FROM
public.period "P", public._organisationunitgroupsetstructure "ST",
public.organisationunit "O", public._orgunitstructure "S" WHERE
"O".organisationunitid = "ST".organisationunitid AND "O".organisationunitid
= "${idlevel}" AND "P".periodtypeid = 8 and CAST("P".startdate as DATE) <=
current_date ) as foo WHERE ouuid = '${ou}' AND yperiod = '${pe}' GROUP BY
yperiod, ouuid, OUgroup ORDER BY yperiod, OUgroup;


Period table is included to simplify the open and close date criteria (AND
"P".periodtypeid = 8) but these dates could probably be parsed as
additional var options. Will add dummy data and then delete...

Regards,
Greg



On Mon, Sep 28, 2015 at 2:22 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> I am not really sure why this would be needed. Could you explain more why
> you need this?
>
> The period formats are well documented (
> https://www.dhis2.org/doc/snapshot/en/developer/html/ch01s04.html), so
> there is no need to have them stored by the server. The client can simply
> create the periods as they need them. If they already exist, nothing will
> happen, otherwise, they will by dynamically created when data is imported.
>
> Regards,
> Jason
>
>
> On Mon, Sep 28, 2015 at 2:19 PM, Greg Rowles <greg.rowles@xxxxxxxxx>
> wrote:
>
>> Hi Jason
>>
>> Yeah, its for a "non-standard" DHIS2 instance. Our national data
>> dictionary doesn't host actual data only master meta-data. Any suggestions?
>> I don't see any options for periods under meta-data export either...
>>
>> Greg
>>
>>
>> On Mon, Sep 28, 2015 at 2:17 PM, Jason Pickering <
>> jason.p.pickering@xxxxxxxxx> wrote:
>>
>>> Hi Greg,
>>>
>>> There is no need to do this, as they will be added when data actually
>>> exists for that period.
>>>
>>> I suppose you could add a dummy record and delete it, and that would
>>> have the same affect as creating the period.
>>>
>>> Regards,
>>> Jason
>>>
>>>
>>> On Mon, Sep 28, 2015 at 2:10 PM, Greg Rowles <greg.rowles@xxxxxxxxx>
>>> wrote:
>>>
>>>> Hi Devs
>>>>
>>>> Is there an API call to add missing period records? E.g. we have no
>>>> yearly (periodtypeid: 8) records in an instance which has restricted
>>>> access. Only API calls are supported...
>>>>
>>>> Regards,
>>>> Greg
>>>>
>>>> --
>>>> *Health Information Systems Program - South Africa*
>>>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- - -
>>>> - - *
>>>> Mobile  :    073 246 2992
>>>> Landline:   021 554 3130
>>>> Fax:          086 733 8432
>>>> Skype:      gregory_rowles
>>>>
>>>> _______________________________________________
>>>> Mailing list: https://launchpad.net/~dhis2-devs
>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>>> More help   : https://help.launchpad.net/ListHelp
>>>>
>>>>
>>>
>>>
>>> --
>>> Jason P. Pickering
>>> email: jason.p.pickering@xxxxxxxxx
>>> tel:+46764147049
>>>
>>
>>
>>
>> --
>> *Health Information Systems Program - South Africa*
>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- - - -
>> - *
>> Mobile  :    073 246 2992
>> Landline:   021 554 3130
>> Fax:          086 733 8432
>> Skype:      gregory_rowles
>>
>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049
>



-- 
*Health Information Systems Program - South Africa*
*- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- - - - - *
Mobile  :    073 246 2992
Landline:   021 554 3130
Fax:          086 733 8432
Skype:      gregory_rowles