[Branch ~dhis2-devs-core/dhis2/trunk] Rev 1706: Made the get objective complete registrations sql query rely on operands rather than dataelements.

DHIS_2_Users · 27 March 2010 23:01

Merge authors:
Lars <Lars@Livingroom>

···

------------------------------------------------------------
revno: 1706 [merge]
committer: Lars <Lars@Livingroom>
branch nick: trunk
timestamp: Sat 2010-03-27 23:59:06 +0100
message:
  Made the get objective complete registrations sql query rely on operands rather than dataelements.
modified:
  dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java
  dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java

--
lp:dhis2

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription\.

jason · 28 March 2010 06:14

Lars Livingroom?

It would be very useful to persist this in the form of a data element in the datavalue table.

I created a data element, Data elements submitted (HIA2) where HIA2 was the dataset. I then inserted the number of data element submitted for this dataset directly into the datavalue table with a similar SQL statement. I then created an indicator (Percentage of data elements submitted (HIA2)), which was the number submitted divided by the number that should have been submitted.

After that, all of the standard operations (report tables, datamart, etc) are available to the user.

Maybe a blueprint?

Regards,
Jason

···

On Sun, Mar 28, 2010 at 1:01 AM, noreply@launchpad.net wrote:

Merge authors:

Lars Lars@Livingroom

revno: 1706 [merge]

committer: Lars Lars@Livingroom

branch nick: trunk

timestamp: Sat 2010-03-27 23:59:06 +0100

message:

Made the get objective complete registrations sql query rely on operands rather than dataelements.

modified:

dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java

dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java

–

lp:dhis2

https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.

To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription.

=== modified file ‘dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java’

— dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java 2010-03-22 12:22:45 +0000

+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java 2010-03-27 20:17:48 +0000

@@ -252,7 +252,7 @@
 {

     final Period period = periodService.getPeriod( periodId );
   Date deadline = getConfiguration().getDeadline( period );
   Date deadline = getConfiguration() != null ? getConfiguration().getDeadline( period ) : null;



   final DataSet dataSet = dataSetService.getDataSet( dataSetId );
=== modified file ‘dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java’

— dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java 2010-03-19 13:36:48 +0000

+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java 2010-03-27 22:59:06 +0000

@@ -108,17 +108,15 @@
     final String deadlineCriteria = deadline != null ? "AND lastupdated < '" + DateUtils.getMediumDateString( deadline ) + "' " : "";



     final String sql =
       "SELECT COUNT(sourceid) FROM ( " +
           "SELECT sourceid, count(DISTINCT dataelementid) AS no " +
       "SELECT COUNT(completed) FROM ( " +
           "SELECT COUNT(sourceid) AS sources " +

           "FROM datavalue " +
           "JOIN dataelementoperand USING (dataelementid, categoryoptioncomboid) " +
           "JOIN datasetoperands USING (dataelementoperandid) " +

           "WHERE periodid = " + periodId + " " + deadlineCriteria +

           "AND sourceid IN (" + childrenIds + ") " +
           "AND dataelementid IN ( " +
               "SELECT dataelementid " +
               "FROM compulsorydatasetmembers " +
               "WHERE datasetid = " + dataSetId + " ) " +
           "GROUP BY sourceid ) AS completed " +
       "WHERE [completed.no](http://completed.no) = " + compulsoryElements;
           "AND datasetid = " + dataSetId + ") AS completed " +
       "WHERE completed.sources = " + compulsoryElements;



   return statementManager.getHolder().queryForInteger( sql );
}
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:+260968395190

Lars · 28 March 2010 12:04

Lars Livingroom?

Yeah my laptop crashed… again…

It would be very useful to persist this in the form of a data element in the datavalue table.

I created a data element, Data elements submitted (HIA2) where HIA2 was the dataset. I then inserted the number of data element submitted for this dataset directly into the datavalue table with a similar SQL statement. I then created an indicator (Percentage of data elements submitted (HIA2)), which was the number submitted divided by the number that should have been submitted.

After that, all of the standard operations (report tables, datamart, etc) are available to the user.

Maybe a blueprint?

Regards,
Jason

The idea of using data completeness in indicators and report tables is good and something we have discussed before. We already have functionality for using dataset completeness (the subjective variant based on complete registrations from the data entry screen) in the ‘dataset report table’. Will include this here too (the objective variant based on compulsory dataelement+categoryoptioncombos).

Doing like you did seems like a nice way of getting ad-hoc completeness data. To make it part of the system / indicators I think we should separate a bit between raw/registered data and completeness data. But I am open for suggestions…

Lars

···

2010/3/28 Jason Pickering jason.p.pickering@gmail.com

On Sun, Mar 28, 2010 at 1:01 AM, noreply@launchpad.net wrote:

Merge authors:

Lars Lars@Livingroom

revno: 1706 [merge]

committer: Lars Lars@Livingroom

branch nick: trunk

timestamp: Sat 2010-03-27 23:59:06 +0100

message:

Made the get objective complete registrations sql query rely on operands rather than dataelements.

modified:

dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java

dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java

–

lp:dhis2

https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.

To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription.

=== modified file ‘dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java’

— dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java 2010-03-22 12:22:45 +0000

+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java 2010-03-27 20:17:48 +0000

@@ -252,7 +252,7 @@
 {

     final Period period = periodService.getPeriod( periodId );
   Date deadline = getConfiguration().getDeadline( period );
   Date deadline = getConfiguration() != null ? getConfiguration().getDeadline( period ) : null;



   final DataSet dataSet = dataSetService.getDataSet( dataSetId );
=== modified file ‘dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java’

— dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java 2010-03-19 13:36:48 +0000

+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java 2010-03-27 22:59:06 +0000

@@ -108,17 +108,15 @@
     final String deadlineCriteria = deadline != null ? "AND lastupdated < '" + DateUtils.getMediumDateString( deadline ) + "' " : "";



     final String sql =
       "SELECT COUNT(sourceid) FROM ( " +
           "SELECT sourceid, count(DISTINCT dataelementid) AS no " +
       "SELECT COUNT(completed) FROM ( " +
           "SELECT COUNT(sourceid) AS sources " +

           "FROM datavalue " +
           "JOIN dataelementoperand USING (dataelementid, categoryoptioncomboid) " +
           "JOIN datasetoperands USING (dataelementoperandid) " +

           "WHERE periodid = " + periodId + " " + deadlineCriteria +

           "AND sourceid IN (" + childrenIds + ") " +
           "AND dataelementid IN ( " +
               "SELECT dataelementid " +
               "FROM compulsorydatasetmembers " +
               "WHERE datasetid = " + dataSetId + " ) " +
           "GROUP BY sourceid ) AS completed " +
       "WHERE [completed.no](http://completed.no) = " + compulsoryElements;
           "AND datasetid = " + dataSetId + ") AS completed " +
       "WHERE completed.sources = " + compulsoryElements;



   return statementManager.getHolder().queryForInteger( sql );
}
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:+260968395190

jason · 28 March 2010 15:39

OK, here is my suggestion.

If we had another way to look at data completeness, maybe this would satisfy our requirements here.

Percentage of data elements submitted, regardless of whether they were compulsory or not, as a function or orgunit, period and dataset
Percentage of orgunits submitting anything (regardless of whether they have pressed the completed button or not.

Personally, I like the idea of persisting the data in the normal way, as it makes the inclusion of these indicators easily available for other purposes. I am not really sure I understand the need for separation of the data from the data value table. Maybe this needs to be explored further. Having everything available only though the report tables is not bad, but if we want to combine report completeness with other indicators (which is a good thing), then it becomes perhaps more complicated? Not really sure. As long as we can create a single report table, with both report completeness indicators, as well as normal indicators, this would be OK with me.

Regards,
JPP

P.S. Sorry about your laptop. My son spilled a glass of water on mine, so I feel your pain.

···

2010/3/28 Lars Helge Øverland larshelge@gmail.com

2010/3/28 Jason Pickering jason.p.pickering@gmail.com

Lars Livingroom?

Yeah my laptop crashed… again…

It would be very useful to persist this in the form of a data element in the datavalue table.

I created a data element, Data elements submitted (HIA2) where HIA2 was the dataset. I then inserted the number of data element submitted for this dataset directly into the datavalue table with a similar SQL statement. I then created an indicator (Percentage of data elements submitted (HIA2)), which was the number submitted divided by the number that should have been submitted.

After that, all of the standard operations (report tables, datamart, etc) are available to the user.

Maybe a blueprint?

Regards,
Jason

The idea of using data completeness in indicators and report tables is good and something we have discussed before. We already have functionality for using dataset completeness (the subjective variant based on complete registrations from the data entry screen) in the ‘dataset report table’. Will include this here too (the objective variant based on compulsory dataelement+categoryoptioncombos).

Doing like you did seems like a nice way of getting ad-hoc completeness data. To make it part of the system / indicators I think we should separate a bit between raw/registered data and completeness data. But I am open for suggestions…

Lars

On Sun, Mar 28, 2010 at 1:01 AM, noreply@launchpad.net wrote:

Merge authors:

Lars Lars@Livingroom

revno: 1706 [merge]

committer: Lars Lars@Livingroom

branch nick: trunk

timestamp: Sat 2010-03-27 23:59:06 +0100

message:

Made the get objective complete registrations sql query rely on operands rather than dataelements.

modified:

dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java

dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java

–

lp:dhis2

https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.

To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription.

=== modified file ‘dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java’

— dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java 2010-03-22 12:22:45 +0000

+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/impl/AbstractDataSetCompletenessService.java 2010-03-27 20:17:48 +0000

@@ -252,7 +252,7 @@
 {

     final Period period = periodService.getPeriod( periodId );
   Date deadline = getConfiguration().getDeadline( period );
   Date deadline = getConfiguration() != null ? getConfiguration().getDeadline( period ) : null;



   final DataSet dataSet = dataSetService.getDataSet( dataSetId );
=== modified file ‘dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java’

— dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java 2010-03-19 13:36:48 +0000

+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/completeness/jdbc/JDBCDataSetCompletenessStore.java 2010-03-27 22:59:06 +0000

@@ -108,17 +108,15 @@
     final String deadlineCriteria = deadline != null ? "AND lastupdated < '" + DateUtils.getMediumDateString( deadline ) + "' " : "";



     final String sql =
       "SELECT COUNT(sourceid) FROM ( " +
           "SELECT sourceid, count(DISTINCT dataelementid) AS no " +
       "SELECT COUNT(completed) FROM ( " +
           "SELECT COUNT(sourceid) AS sources " +

           "FROM datavalue " +
           "JOIN dataelementoperand USING (dataelementid, categoryoptioncomboid) " +
           "JOIN datasetoperands USING (dataelementoperandid) " +

           "WHERE periodid = " + periodId + " " + deadlineCriteria +

           "AND sourceid IN (" + childrenIds + ") " +
           "AND dataelementid IN ( " +
               "SELECT dataelementid " +
               "FROM compulsorydatasetmembers " +
               "WHERE datasetid = " + dataSetId + " ) " +
           "GROUP BY sourceid ) AS completed " +
       "WHERE [completed.no](http://completed.no) = " + compulsoryElements;
           "AND datasetid = " + dataSetId + ") AS completed " +
       "WHERE completed.sources = " + compulsoryElements;



   return statementManager.getHolder().queryForInteger( sql );
}
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:+260968395190

–

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