Loading missing period records through webAPI?

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

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

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

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

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

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

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

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

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