dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

If you are using Postgres, you could always simply generate the periods
your self with something like..

SELECT startdate::date,(startdate::date + '1 month'::interval - '1
day'::interval)::date as startdate from
(SELECT * FROM generate_series('2015-01-01'::date,'2015-12-31'::date, '1
month') as startdate) as foo



On Mon, Sep 28, 2015 at 2:43 PM, Greg Rowles <greg.rowles@xxxxxxxxx> wrote:

> 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
>



-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049