dhis2-devs team mailing list archive

[Knut Staring <knutst@xxxxxxxxx>]

Nice, Jason. But did you mean enddate at the end of the first line (just
before "from")?

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

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


-- 
Knut Staring
Dept. of Informatics, University of Oslo
Norway: +4791880522
Skype: knutstar
http://dhis2.org