Loading missing period records through webAPI?

Support - Assistance technique
1

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

2

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@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

3

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@gmail.com 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@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

4

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@gmail.com 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@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:10 PM, Greg Rowles greg.rowles@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

5

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@gmail.com 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@gmail.com 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

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Sep 28, 2015 at 2:17 PM, Jason Pickering jason.p.pickering@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:10 PM, Greg Rowles greg.rowles@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

6

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@gmail.com 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@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:19 PM, Greg Rowles greg.rowles@gmail.com 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

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Sep 28, 2015 at 2:17 PM, Jason Pickering jason.p.pickering@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:10 PM, Greg Rowles greg.rowles@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

7

Will give it a try… many thanks!

···

On Mon, Sep 28, 2015 at 2:53 PM, Jason Pickering jason.p.pickering@gmail.com 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@gmail.com 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

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Sep 28, 2015 at 2:22 PM, Jason Pickering jason.p.pickering@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:19 PM, Greg Rowles greg.rowles@gmail.com 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

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Sep 28, 2015 at 2:17 PM, Jason Pickering jason.p.pickering@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:10 PM, Greg Rowles greg.rowles@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

8

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@gmail.com 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

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

On Mon, Sep 28, 2015 at 2:43 PM, Greg Rowles greg.rowles@gmail.com 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

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Sep 28, 2015 at 2:22 PM, Jason Pickering jason.p.pickering@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:19 PM, Greg Rowles greg.rowles@gmail.com 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

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Sep 28, 2015 at 2:17 PM, Jason Pickering jason.p.pickering@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:10 PM, Greg Rowles greg.rowles@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

9

Yeah, of course

SELECT startdate::date,(startdate::date + ‘1 month’::interval - ‘1 day’::interval)::date as enddate from

(SELECT * FROM generate_series(‘2015-01-01’::date,‘2015-12-31’::date, ‘1 month’) as startdate) as foo

:slight_smile:

···

On Mon, Sep 28, 2015 at 3:02 PM, Knut Staring knutst@gmail.com wrote:

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@gmail.com 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

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

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

On Mon, Sep 28, 2015 at 2:43 PM, Greg Rowles greg.rowles@gmail.com 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

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Sep 28, 2015 at 2:22 PM, Jason Pickering jason.p.pickering@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:19 PM, Greg Rowles greg.rowles@gmail.com 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

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Sep 28, 2015 at 2:17 PM, Jason Pickering jason.p.pickering@gmail.com 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

–
Health Information Systems Program - South Africa

**- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - - - ****- - - - - **

Mobile : 073 246 2992
Landline: 021 554 3130
Fax: 086 733 8432
Skype: gregory_rowles

On Mon, Sep 28, 2015 at 2:10 PM, Greg Rowles greg.rowles@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049