dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #19294
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 8347: Aggregation engine improvements (WIP).
------------------------------------------------------------
revno: 8347
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2012-10-03 09:46:58 +0700
message:
Aggregation engine improvements (WIP).
modified:
dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java
dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/startup/TableAlteror.java
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addCaseAggregation.vm
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/javascript/caseaggregation.js
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/updateCaseAggregation.vm
--
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-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java 2012-09-19 09:30:47 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java 2012-10-03 02:46:58 +0000
@@ -219,7 +219,7 @@
Period period )
{
String sql = convertCondition( aggregationCondition, orgunit, period );
-System.out.println("\n\n === \n " + sql );
+
Collection<Integer> patientIds = aggregationConditionStore.executeSQL( sql );
if ( patientIds == null )
@@ -605,12 +605,12 @@
if ( info[0].equalsIgnoreCase( OBJECT_PATIENT ) )
{
- condition = getConditionForPatient( orgunitId, startDate, endDate );
+ condition = getConditionForPatient( orgunitId, operator, startDate, endDate );
}
else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROPERTY ) )
{
String propertyName = info[1];
- condition = getConditionForPatientProperty( propertyName, startDate, endDate );
+ condition = getConditionForPatientProperty( propertyName, operator, startDate, endDate );
}
else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_ATTRIBUTE ) )
@@ -652,7 +652,7 @@
else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_PROPERTY ) )
{
- condition = getConditionForProgramProperty( startDate, endDate, info[1] );
+ condition = getConditionForProgramProperty( operator, startDate, endDate, info[1] );
}
else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM ) )
{
@@ -729,39 +729,23 @@
int dataElementId, int orgunitId, String startDate, String endDate )
{
String sql = "SELECT distinct(pi.patientid) ";
- String condition = "pi.patientid";
+ String from = "FROM programstageinstance as psi "
+ + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid ";
+
+ String condition = "pi.patientid ";
if ( operator.equals( AGGRERATION_SUM ) )
{
- sql = "SELECT pi.patientid ";
- condition = "psi.programstageinstanceid";
+ sql = "SELECT psi.programstageinstanceid ";
+ condition = "psi.programstageinstanceid ";
}
- sql += "FROM programstageinstance as psi "
- + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid "
+ sql += from
+ "LEFT OUTER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
- + "WHERE psi.executionDate >= '"
- + startDate
- + "' AND psi.executionDate <= '"
- + endDate
- + "' "
- + "AND pd.value IS NULL AND "
- + condition
- + " NOT IN ( "
- + "SELECT "
- + condition
- + " FROM programstageinstance as psi "
- + "INNER JOIN programinstance as pgi ON pi.programinstanceid = psi.programinstanceid "
- + "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
- + "WHERE psi.organisationunitid = "
- + orgunitId
- + " AND pgi.programid = "
- + programId
- + "AND psi.executionDate >= '"
- + startDate
- + "' AND psi.executionDate <= '"
- + endDate
- + "' "
+ + "WHERE psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' "
+ + "AND pd.value IS NULL AND " + condition + " NOT IN ( " + "SELECT " + condition + from
+ + "WHERE psi.organisationunitid = " + orgunitId + " AND pi.programid = " + programId + " "
+ + "AND psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' "
+ "AND pd.dataelementid = " + dataElementId + " ";
if ( !programStageId.equals( IN_CONDITION_GET_ALL ) )
@@ -776,17 +760,19 @@
int dataElementId, int orgunitId, String startDate, String endDate )
{
String sql = "SELECT distinct(pi.patientid) ";
+ String from = "FROM programstageinstance as psi "
+ + "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
+ + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid ";
if ( operator.equals( AGGRERATION_SUM ) )
{
- sql = "SELECT pi.patientid ";
+ sql = "SELECT psi.programstageinstanceid ";
+ from = "FROM programstageinstance as psi "
+ + "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid ";
}
- sql += "FROM programstageinstance as psi "
- + " INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
- + " INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid "
- + " WHERE pd.dataelementid=" + dataElementId + " AND psi.organisationunitid=" + orgunitId
- + " AND psi.executionDate>='" + startDate +"' AND psi.executionDate <= '" + endDate + "'";
+ sql += from + " WHERE pd.dataelementid=" + dataElementId + " AND psi.organisationunitid=" + orgunitId
+ + " AND psi.executionDate>='" + startDate + "' AND psi.executionDate <= '" + endDate + "'";
if ( !programStageId.equals( IN_CONDITION_GET_ALL ) )
{
@@ -799,36 +785,57 @@
private String getConditionForPatientAttribute( int attributeId, String operator )
{
String sql = "SELECT distinct(pi.patientid) ";
+ String from = "FROM patientattributevalue pi ";
if ( operator.equals( AGGRERATION_SUM ) )
{
- sql = "SELECT pi.patientid ";
+ sql = "SELECT psi.programstageinstanceid ";
+ from = "FROM programstageinstance psi inner join programinstance pi "
+ + "on psi.programinstanceid=pi.programinstanceid " + "inner join patientattributevalue pav "
+ + "on pav.patientid=pi.patientid ";
}
- return sql + "FROM patientattributevalue pi "
- + "WHERE pi.patientattributeid=" + attributeId + " AND pi.value ";
+
+ return sql + from + "WHERE patientattributeid=" + attributeId + " AND value ";
}
- private String getConditionForPatient( int orgunitId, String startDate, String endDate )
+ private String getConditionForPatient( int orgunitId, String operator, String startDate, String endDate )
{
- return "SELECT pi.patientid FROM patient p INNER JOIN programinstance pi ON p.patientid=pi.patientid "
- + "WHERE p.organisationunitid=" + orgunitId + " AND p.registrationdate>= '" + startDate + "' "
- + "AND p.registrationdate <= '" + endDate + "' ";
+ String sql = "SELECT pi.patientid ";
+ String from = "FROM patient pi ";
+ String where = "WHERE pi.organisationunitid=" + orgunitId + " AND pi.registrationdate>= '" + startDate + "' "
+ + "AND pi.registrationdate <= '" + endDate + "'";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT psi.programstageinstanceid ";
+ from = "FROM programstageinstance psi inner join programinstance pi "
+ + "on psi.programinstanceid=pi.programinstanceid " + "inner join patient p on p.patientid=pi.patientid ";
+ where = "WHERE p.organisationunitid=" + orgunitId + " AND p.registrationdate>= '" + startDate + "' "
+ + "AND p.registrationdate <= '" + endDate + "'";
+ }
+
+ return sql + from + where;
}
- private String getConditionForPatientProperty( String propertyName, String startDate,
+ private String getConditionForPatientProperty( String propertyName, String operator, String startDate,
String endDate )
{
- String sql = "SELECT distinct(pi.patientid) ";
-
- sql += "FROM patient pi WHERE ";
+ String sql = "SELECT distinct(pi.patientid) FROM patient pi WHERE ";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT psi.programstageinstanceid " + "FROM programstageinstance psi inner join programinstance pi "
+ + "on psi.programinstanceid=pi.programinstanceid "
+ + "inner join patient p on p.patientid=pi.patientid WHERE ";
+ }
if ( propertyName.equals( PROPERTY_AGE ) )
{
- sql += "DATE('" + startDate + "') - DATE(pi.birthdate) ";
+ sql += "DATE('" + startDate + "') - DATE(birthdate) ";
}
else
{
- sql += " pi." + propertyName + " ";
+ sql += propertyName + " ";
}
return sql;
@@ -838,42 +845,47 @@
String endDate )
{
String sql = "SELECT distinct(pi.patientid) ";
-
+ String from = "FROM programinstance pi INNER JOIN programstageinstance psi "
+ + "ON psi.programinstanceid=pi.programinstanceid ";
if ( operator.equals( AGGRERATION_SUM ) )
{
- sql = "SELECT pi.patientid ";
+ sql = "SELECT psi.programstageinstance ";
+ from = "FROM programstageinstance psi ";
}
- sql += "FROM patient as p "
- + "INNER JOIN programinstance pi ON p.patientid=pi.patientid "
- + "INNER JOIN programstageinstance psi ON psi.programinstanceid=pi.programinstanceid WHERE "
- + propertyName;
+ sql += from + "WHERE executionDate<='" + startDate + "' and executionDate>='" + endDate + "' and "
+ + propertyName;
return sql;
}
- private String getConditionForProgramProperty( String startDate, String endDate, String property )
+ private String getConditionForProgramProperty( String operator, String startDate, String endDate, String property )
{
- return "SELECT pi.patientid FROM programinstance as pi "
- + "WHERE pi.enrollmentdate>='" + startDate + "' "
- + "AND pi.enrollmentdate<='" + endDate + "' AND " + property;
+ String sql = "SELECT pi.patientid FROM programinstance as pi ";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT psi.programstageinstanceid FROM programinstance as pi "
+ + "INNER JOIN programstageinstance psi ON psi.programinstanceid=pi.programinstanceid ";
+ }
+
+ return sql + "WHERE pi.enrollmentdate>='" + startDate + "' " + "AND pi.enrollmentdate<='" + endDate + "' AND "
+ + property;
}
private String getConditionForProgram( String programId, String operator, int orgunitId, String startDate,
String endDate )
{
- String select = "SELECT distinct(pi.patientid) ";
+ String sql = "SELECT distinct(pi.patientid) FROM programinstance as pi ";
if ( operator.equals( AGGRERATION_SUM ) )
{
- select = "SELECT pi.patientid ";
+ sql = "SELECT psi.programstageinstanceid FROM programinstance as pi "
+ + "INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstanceid ";
}
- return select + "FROM programinstance as pi "
- + "INNER JOIN programstageinstance psi ON pi.programinstanceid = psi.programinstanceid "
- + "WHERE pi.programid=" + programId + " "
- + "AND psi.organisationunitid = " + orgunitId + " AND pi.enrollmentdate >= '" + startDate
- + "' AND pi.enrollmentdate <= '" + endDate + "' ";
+ return sql + "WHERE pi.programid=" + programId + " " + "AND psi.organisationunitid = " + orgunitId
+ + " AND pi.enrollmentdate >= '" + startDate + "' AND pi.enrollmentdate <= '" + endDate + "' ";
}
private String getConditionForProgramStage( String programStageId, String operator, int orgunitId,
@@ -883,7 +895,7 @@
if ( operator.equals( AGGRERATION_SUM ) )
{
- select = "SELECT pi.patientid ";
+ select = "SELECT psi.programstageinstanceid ";
}
return select + "FROM programinstance as pi INNER JOIN programstageinstance psi "
@@ -899,7 +911,7 @@
if ( operator.equals( AGGRERATION_SUM ) )
{
- select = "SELECT pi.patientid ";
+ select = "SELECT psi.programstageinstanceid ";
}
select += "FROM programstageinstance as psi "
@@ -926,7 +938,7 @@
if ( operator.equals( AGGRERATION_SUM ) )
{
- select = "SELECT pi.patientid ";
+ select = "SELECT psi.programstageinstanceid ";
}
return select + "FROM programinstance as pi INNER JOIN programstageinstance psi "
@@ -947,13 +959,13 @@
{
if ( operators.get( index ).equalsIgnoreCase( OPERATOR_AND ) )
{
- if ( aggregateOperator.equals( AGGRERATION_SUM ) )
+ if ( aggregateOperator.equals( AGGRERATION_COUNT ) )
{
sql += " AND pi.patientid IN ( " + conditions.get( index + 1 );
}
else
{
- sql += " AND pi.patientid IN ( " + conditions.get( index + 1 );
+ sql += " AND psi.programstageinstanceid IN ( " + conditions.get( index + 1 );
}
sqlAnd += ")";
}
=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/startup/TableAlteror.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/startup/TableAlteror.java 2012-10-01 05:30:38 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/startup/TableAlteror.java 2012-10-03 02:46:58 +0000
@@ -119,22 +119,27 @@
updateMultiOrgunitTabularReportTable();
updateProgramStageTabularReportTable();
moveStoredByFormStageInstanceToDataValue();
-
+
executeSql( "ALTER TABLE patientattribute DROP COLUMN inheritable" );
executeSql( "ALTER TABLE programstageinstance DROP COLUMN stageInProgram" );
-
+
updateRelationshipIdentifiers();
updateRelationshipAttributes();
-
+
executeSql( "UPDATE programstage SET reportDateDescription='Report date' WHERE reportDateDescription is null" );
-
+
executeSql( "CREATE INDEX programstageinstance_executiondate ON programstageinstance (executiondate)" );
-
+
executeSql( "UPDATE programstage SET autoGenerateEvent=true WHERE autoGenerateEvent is null" );
-
+
executeSql( "UPDATE program SET generatedByEnrollmentDate=false WHERE generatedByEnrollmentDate is null" );
-
+
executeSql( "ALTER TABLE programstage DROP COLUMN stageinprogram" );
+
+ executeSql( "CREATE INDEX index_patientdatavalue ON patientdatavalue( programstageinstanceid, dataelementid, value, timestamp )" );
+
+ executeSql( "CREATE INDEX index_programinstance ON programinstance( programinstanceid )" );
+
}
// -------------------------------------------------------------------------
@@ -331,7 +336,8 @@
while ( resultSet.next() )
{
- executeSql( "INSERT into program_patientIdentifierTypes( programid, patientidentifiertypeid) values (" + resultSet.getString( 1 ) + "," + resultSet.getString( 2 ) + ")");
+ executeSql( "INSERT into program_patientIdentifierTypes( programid, patientidentifiertypeid) values ("
+ + resultSet.getString( 1 ) + "," + resultSet.getString( 2 ) + ")" );
}
executeSql( "ALTER TABLE patientidentifiertype DROP COLUMN programid" );
@@ -343,9 +349,9 @@
finally
{
holder.close();
- }
+ }
}
-
+
private void updateRelationshipAttributes()
{
StatementHolder holder = statementManager.getHolder();
@@ -359,7 +365,8 @@
while ( resultSet.next() )
{
- executeSql( "INSERT into program_patientAttributes( programid, patientattributeid) values (" + resultSet.getString( 1 ) + "," + resultSet.getString( 2 ) + ")");
+ executeSql( "INSERT into program_patientAttributes( programid, patientattributeid) values ("
+ + resultSet.getString( 1 ) + "," + resultSet.getString( 2 ) + ")" );
}
executeSql( "ALTER TABLE patientattribute DROP COLUMN programid" );
@@ -371,9 +378,9 @@
finally
{
holder.close();
- }
+ }
}
-
+
private int executeSql( String sql )
{
try
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addCaseAggregation.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addCaseAggregation.vm 2012-10-02 05:09:19 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addCaseAggregation.vm 2012-10-03 02:46:58 +0000
@@ -64,7 +64,7 @@
<select id="programId" name="programId" onChange="getParams();">
<option value="">[$i18n.getString('please_select')]</option>
#foreach( $program in $programs )
- <option value="$program.id" title='$program.name' type='$program.type'>$encoder.htmlEncode( $program.name )</option>
+ <option value="$program.id" title='$program.name' programType='$program.type'>$encoder.htmlEncode( $program.name )</option>
#end
</select>
</td>
@@ -75,7 +75,7 @@
<label for="programStage">$i18n.getString( "program_stage" )</label>
</td>
<td>
- <select id="programStageId" name="programStageId" onChange="getPatientDataElements();" ondblclick="insertProgramStage(this);"></select>
+ <select id="programStageId" name="programStageId" onChange="getPatientDataElements();"></select>
</td>
</tr>
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm 2012-08-14 07:54:32 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm 2012-10-03 02:46:58 +0000
@@ -63,11 +63,11 @@
</select>
</td>
<td>
- <select multiple id="programStageProperty" size="10" name="programStageProperty" ondblclick="insertInfo(this, true);" disabled >
+ <select multiple id="programStageProperty" size="10" name="programStageProperty" ondblclick="insertInfo(this, true);" disabled >
<option value="[PS:*]">$i18n.getString( "visit_selected_program_stage" )</option>
- <option value="[PS:*.COUNT]">$i18n.getString( "visit_selected_program_stage_x_th_time" )</option>
- <option value="[PS:*] AND [PSP:DATE@executionDate#-DATE@dueDate#]">$i18n.getString( "report_date" ) - $i18n.getString( "due_date" )</option>
- <option value="[PS:*] AND [PC:DATE@executionDate#-DATE@birthDate#]">$i18n.getString( "report_date" ) - $i18n.getString( "date_of_birth" )</option>
+ <option value="[PS:*.COUNT]" name="multiProgram">$i18n.getString( "visit_selected_program_stage_x_th_time" )</option>
+ <option value="[PS:*] AND [PSP:DATE@executionDate#-DATE@dueDate#]" name="multiProgram">$i18n.getString( "report_date" ) - $i18n.getString( "due_date" )</option>
+ <option value="[PS:*] AND [PC:DATE@executionDate#-DATE@birthDate#]" name="multiProgram">$i18n.getString( "report_date" ) - $i18n.getString( "date_of_birth" )</option>
</select>
</select>
</td>
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/javascript/caseaggregation.js'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/javascript/caseaggregation.js 2012-07-24 07:55:07 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/javascript/caseaggregation.js 2012-10-03 02:46:58 +0000
@@ -105,6 +105,10 @@
disable('programStageProperty');
}
+ if(jQuery("#programStageId").attr("programType")==3){
+ jQuery("[name=multiProgram]").remove();
+ }
+
jQuery.getJSON( 'getParamsByProgram.action',{ programId:programId }
,function( json )
{
@@ -179,6 +183,7 @@
{
dataElements.append( "<option value='" + json.dataElements[i].id + "' title='" + json.dataElements[i].name + "' suggested='" + json.dataElements[i].optionset + "'>" + json.dataElements[i].name + "</option>" );
}
+
});
}
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/updateCaseAggregation.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/updateCaseAggregation.vm 2012-10-02 05:09:19 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/updateCaseAggregation.vm 2012-10-03 02:46:58 +0000
@@ -61,7 +61,7 @@
<select id="programId" name="programId" onChange="getParams();">
<option value="">[$i18n.getString('please_select')]</option>
#foreach( $program in $programs )
- <option value="$program.id" title='$program.name'>$encoder.htmlEncode( $program.name )</option>
+ <option value="$program.id" title='$program.name' programType='$program.type'>$encoder.htmlEncode( $program.name )</option>
#end
</select>
</td>
@@ -72,7 +72,7 @@
<label for="programStage">$i18n.getString( "program_stage" )</label>
</td>
<td>
- <select id="programStageId" name="programStageId" onChange="getPatientDataElements();" ondblclick="insertProgramStage(this);"></select>
+ <select id="programStageId" name="programStageId" onChange="getPatientDataElements();"></select>
</td>
</tr>
