dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #14708
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 5078: Add a formula for the number of visits ( Aggregation Query Build module)
------------------------------------------------------------
revno: 5078
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2011-11-02 14:04:50 +0700
message:
Add a formula for the number of visits ( Aggregation Query Build module)
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionStore.java
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/jdbc/JdbcCaseAggregationConditionStore.java
dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResultDetails.vm
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/resources/org/hisp/dhis/patient/i18n_module.properties
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/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-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionStore.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionStore.java 2011-06-24 07:55:22 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionStore.java 2011-11-02 07:04:50 +0000
@@ -28,6 +28,7 @@
package org.hisp.dhis.caseaggregation;
import java.util.Collection;
+import java.util.List;
import org.hisp.dhis.common.GenericStore;
import org.hisp.dhis.dataelement.DataElement;
@@ -47,5 +48,5 @@
Collection<CaseAggregationCondition> get( DataElement dataElement );
- Collection<Integer> executeSQL( String sql );
+ List<Integer> executeSQL( String sql );
}
=== 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 2011-10-26 03:40:58 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java 2011-11-02 07:04:50 +0000
@@ -85,7 +85,7 @@
private final String INVALID_CONDITION = "Invalid condition";
- private final String NUMBER_PATIENTS_REGISTERED = "The number of beneficiaries registered";
+ private final String NUMBER_PATIENTS_REGISTERED = "Registered Beneficiaries No";
// -------------------------------------------------------------------------
// Dependencies
@@ -221,7 +221,8 @@
{
Collection<PatientDataValue> result = new HashSet<PatientDataValue>();
- String sql = createSQL( aggregationCondition.getAggregationExpression(), orgunit, period );
+ String sql = createSQL( aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(),
+ orgunit, period );
Collection<DataElement> dataElements = getDataElementsInCondition( aggregationCondition
.getAggregationExpression() );
@@ -248,7 +249,8 @@
{
Collection<Patient> result = new HashSet<Patient>();
- String sql = createSQL( aggregationCondition.getAggregationExpression(), orgunit, period );
+ String sql = createSQL( aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(),
+ orgunit, period );
Collection<Integer> patientIds = aggregationConditionStore.executeSQL( sql );
@@ -417,7 +419,7 @@
Period period )
{
// Get operators between ( )
- Pattern patternOperator = Pattern.compile( "(\\)\\s*(OR|AND))" );
+ Pattern patternOperator = Pattern.compile( "(\\)\\s*(OR|AND)\\s*\\( )" );
Matcher matcherOperator = patternOperator.matcher( aggregationCondition.getAggregationExpression() );
@@ -430,26 +432,21 @@
List<String> subSQL = new ArrayList<String>();
- String[] conditions = aggregationCondition.getAggregationExpression().split( "(\\)\\s*(OR|AND))" );
-
+ String[] conditions = aggregationCondition.getAggregationExpression().split( "(\\)\\s*(OR|AND)\\s*\\()" );
+
// Create SQL statement for the first condition
- String condition = conditions[0];
-
- String sql = createSQL( condition, orgunit, period );
-
- if ( operators.size() > 0 )
- {
- sql = "SELECT distinct(p.patientid) FROM patient as p where p.patientid in " + sql + ")";
- }
-
+ String condition = conditions[0].replace( "(", "" ).replace( ")", "" );
+
+ String sql = createSQL( condition, aggregationCondition.getOperator(), orgunit, period );
+
subSQL.add( sql );
// Create SQL statement for others
for ( int index = 1; index < conditions.length; index++ )
{
- condition = conditions[index];
-
- sql = "(" + createSQL( condition, orgunit, period ) + ")";
+ condition = conditions[index].replace( "(", "" ).replace( ")", "" );
+
+ sql = "(" + createSQL( condition, aggregationCondition.getOperator(), orgunit, period ) + ")";
subSQL.add( sql );
}
@@ -457,7 +454,7 @@
return getSQL( subSQL, operators );
}
- private String createSQL( String aggregationExpression, OrganisationUnit orgunit, Period period )
+ private String createSQL( String aggregationExpression, String operator, OrganisationUnit orgunit, Period period )
{
int orgunitId = orgunit.getId();
String startDate = DateUtils.getMediumDateString( period.getStartDate() );
@@ -495,6 +492,7 @@
List<String> subConditions = new ArrayList<String>();
Matcher matcherCondition = patternCondition.matcher( expression[i] );
+
String condition = "";
while ( matcherCondition.find() )
@@ -507,18 +505,18 @@
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, orgunitId, startDate, endDate );
+ condition = getConditionForPatientProperty( propertyName, operator, orgunitId, startDate, endDate );
}
else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_ATTRIBUTE ) )
{
int attributeId = Integer.parseInt( info[1] );
- condition = getConditionForPatientAttribute( attributeId, orgunitId, startDate, endDate );
+ condition = getConditionForPatientAttribute( attributeId, operator, orgunitId, startDate, endDate );
}
else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_DATAELEMENT ) )
{
@@ -532,14 +530,14 @@
if ( valueToCompare.equalsIgnoreCase( IS_NULL ) )
{
- condition = getConditionForNotDataElement( programStageId, dataElementId, optionComboId,
- orgunitId, startDate, endDate );
+ condition = getConditionForNotDataElement( programStageId, operator, dataElementId,
+ optionComboId, orgunitId, startDate, endDate );
expression[i] = expression[i].replace( valueToCompare, "" );
}
else
{
- condition = getConditionForDataElement( programStageId, dataElementId, optionComboId,
+ condition = getConditionForDataElement( programStageId, operator, dataElementId, optionComboId,
orgunitId, startDate, endDate );
if ( !expression[i].contains( "+" ) )
{
@@ -558,7 +556,7 @@
}
else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM ) )
{
- condition = getConditionForProgram( info[1], orgunitId, startDate, endDate );
+ condition = getConditionForProgram( info[1], operator, orgunitId, startDate, endDate );
}
// -------------------------------------------------------------
@@ -602,45 +600,43 @@
return getSQL( conditions, operators );
}
- private String getConditionForNotDataElement( int programStageId, int dataElementId, int optionComboId,
- int orgunitId, String startDate, String endDate )
+ private String getConditionForNotDataElement( int programStageId, String operator, int dataElementId,
+ int optionComboId, int orgunitId, String startDate, String endDate )
{
- return "SELECT pi.patientid FROM programstageinstance as psi "
+ String sql = "SELECT distinct(pi.patient) ";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT pi.patient ";
+ }
+
+ return sql + "FROM programstageinstance as psi "
+ "INNER JOIN programstage as ps ON psi.programstageid = ps.programstageid "
+ "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid "
+ "LEFT OUTER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
- + "WHERE psi.executionDate >= '"
- + startDate
- + "' AND psi.executionDate <= '"
- + endDate
- + "' "
+ + "WHERE psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' "
+ "AND pd.value IS NULL AND pi.patientid NOT IN ( "
+ "SELECT distinct(pi.patientid) FROM programstageinstance as psi "
+ "INNER JOIN programstage as ps ON psi.programstageid = ps.programstageid "
+ "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid "
+ "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
- + "WHERE pd.organisationunitid = "
- + orgunitId
- + " AND ps.programstageid = "
- + programStageId
- + " "
- + "AND psi.executionDate >= '"
- + startDate
- + "' AND psi.executionDate <= '"
- + endDate
- + "' "
- + "AND pd.dataelementid = "
- + dataElementId
- + " "
- + "AND pd.categoryoptioncomboid = "
- + optionComboId
+ + "WHERE pd.organisationunitid = " + orgunitId + " AND ps.programstageid = " + programStageId + " "
+ + "AND psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' "
+ + "AND pd.dataelementid = " + dataElementId + " " + "AND pd.categoryoptioncomboid = " + optionComboId
+ " ) ";
}
- private String getConditionForDataElement( int programStageId, int dataElementId, int optionComboId, int orgunitId,
- String startDate, String endDate )
+ private String getConditionForDataElement( int programStageId, String operator, int dataElementId,
+ int optionComboId, int orgunitId, String startDate, String endDate )
{
- return "SELECT pi.patientid FROM programstageinstance as psi "
+ String sql = "SELECT distinct(pi.patientid) ";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT pi.patientid ";
+ }
+
+ return sql + "FROM programstageinstance as psi "
+ "INNER JOIN programstage as ps ON psi.programstageid = ps.programstageid "
+ "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
+ "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid "
@@ -649,9 +645,17 @@
+ "AND psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' ";
}
- private String getConditionForPatientAttribute( int attributeId, int orgunitId, String startDate, String endDate )
+ private String getConditionForPatientAttribute( int attributeId, String operator, int orgunitId, String startDate,
+ String endDate )
{
- return "SELECT pi.patientid FROM programstageinstance as psi "
+ String sql = "SELECT distinct(pi.patient) ";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT pi.patient ";
+ }
+
+ return sql + "FROM programstageinstance as psi "
+ "INNER JOIN programstage as ps ON psi.programstageid = ps.programstageid "
+ "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "
+ "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid "
@@ -661,17 +665,32 @@
+ "AND pav.value ";
}
- private String getConditionForPatient( int orgunitId, String startDate, String endDate )
+ private String getConditionForPatient( int orgunitId, String operator, String startDate, String endDate )
{
- String sql = "SELECT p.patientid FROM patient as p WHERE p.organisationunitid = " + orgunitId + " "
- + "AND p.registrationdate >= '" + startDate + "' AND p.registrationdate <= '" + endDate + "' ";
+ String sql = "SELECT distinct(p.patient) ";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT p.patient ";
+ }
+
+ sql = "FROM patient as p WHERE p.organisationunitid = " + orgunitId + " " + "AND p.registrationdate >= '"
+ + startDate + "' AND p.registrationdate <= '" + endDate + "' ";
return sql;
}
- private String getConditionForPatientProperty( String propertyName, int orgunitId, String startDate, String endDate )
+ private String getConditionForPatientProperty( String propertyName, String operator, int orgunitId,
+ String startDate, String endDate )
{
- String sql = "SELECT p.patientid FROM programstageinstance as psi INNER JOIN programstage as ps "
+ String sql = "SELECT distinct(p.patient) ";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT p.patient ";
+ }
+
+ sql = "FROM programstageinstance as psi INNER JOIN programstage as ps "
+ "ON psi.programstageid = ps.programstageid INNER JOIN patientdatavalue as pd ON "
+ "psi.programstageinstanceid = pd.programstageinstanceid INNER JOIN programinstance as pi ON "
+ "psi.programinstanceid = pi.programinstanceid INNER JOIN patient as p ON "
@@ -693,15 +712,23 @@
private String getConditionForProgramProperty( int orgunitId, String startDate, String endDate )
{
- return "SELECT p.patientid FROM programstageinstance as psi "
+ return "FROM programstageinstance as psi "
+ "INNER JOIN programinstance as pi ON psi.programinstanceid = pi.programinstanceid "
+ "INNER JOIN patient as p ON p.patientid = pi.patientid WHERE p.organisationunitid = " + orgunitId + " "
+ "AND psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' AND ";
}
- private String getConditionForProgram( String programId, int orgunitId, String startDate, String endDate )
+ private String getConditionForProgram( String programId, String operator, int orgunitId, String startDate,
+ String endDate )
{
- return "SELECT p.patientid FROM programstageinstance as psi "
+ String sql = "SELECT distinct(p.patient) ";
+
+ if ( operator.equals( AGGRERATION_SUM ) )
+ {
+ sql = "SELECT p.patient ";
+ }
+
+ return sql + "FROM programstageinstance as psi "
+ "INNER JOIN programinstance as pi ON psi.programinstanceid = pi.programinstanceid "
+ "INNER JOIN patient as p ON p.patientid = pi.patientid " + "WHERE pi.programid=" + programId + " "
+ "AND p.organisationunitid = " + orgunitId + " " + "AND pi.enrollmentdate >= '" + startDate
=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionStore.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionStore.java 2011-10-05 09:19:52 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionStore.java 2011-11-02 07:04:50 +0000
@@ -29,8 +29,10 @@
import java.sql.ResultSet;
import java.sql.Statement;
+import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
+import java.util.List;
import org.amplecode.quick.StatementHolder;
import org.amplecode.quick.StatementManager;
@@ -70,11 +72,11 @@
// -------------------------------------------------------------------------
@Override
- public Collection<Integer> executeSQL( String sql )
+ public List<Integer> executeSQL( String sql )
{
StatementHolder holder = statementManager.getHolder();
- Collection<Integer> patientIds = new HashSet<Integer>();
+ List<Integer> patientIds = new ArrayList<Integer>();
try
{
@@ -94,6 +96,7 @@
}
catch ( Exception ex )
{
+ ex.printStackTrace();
return null;
}
finally
=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResultDetails.vm'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResultDetails.vm 2011-07-27 04:18:51 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResultDetails.vm 2011-11-02 07:04:50 +0000
@@ -12,7 +12,7 @@
#end
<tr>
<td>$!patient.getFullName()</td>
- <td>$!patient.gender</td>
+ <td>$i18n.getString($!patient.gender)</td>
<td>$format.formatDate($!patient.birthDate)</td>
<td>$!patient.getAge()</td>
</tr>
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/resources/org/hisp/dhis/patient/i18n_module.properties'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/resources/org/hisp/dhis/patient/i18n_module.properties 2011-10-27 03:12:50 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/resources/org/hisp/dhis/patient/i18n_module.properties 2011-11-02 07:04:50 +0000
@@ -383,5 +383,5 @@
test_condition = Test condition
run_success = Run successful
run_fail = Run failed
-patients_registered = The number of beneficiaries registered
+number_of_patients_registered = Registered Beneficiaries No
irregular_encounter = Irregular encounter
\ No newline at end of file
=== 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 2011-10-26 03:40:58 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addCaseAggregation.vm 2011-11-02 07:04:50 +0000
@@ -48,7 +48,11 @@
</tr>
<tr>
- <input type="radio" id="operator" name="operator" value="COUNT" checked style='display:none;'/>
+ <td width="20em"><label for="operator">$i18n.getString( "operator" )</label></td>
+ <td>
+ <input type="radio" id="operator" name="operator" value="COUNT" checked >$i18n.getString('count')
+ <input type="radio" id="operator" name="operator" value="SUM"> $i18n.getString('sum')
+ </td>
</tr>
<tr>
<td colspan="2"><p></p></td>
@@ -80,10 +84,10 @@
</tr>
<tr>
<td>
- <select id="program" name="program" style="min-width:20em" onChange="getProgramStages();">
+ <select id="program" name="program" style="width:20em" onChange="getProgramStages();">
<option value="0">[$i18n.getString('please_select')]</option>
#foreach( $program in $programs )
- <option value="$program.id">$encoder.htmlEncode( $program.name )</option>
+ <option value="$program.id" title='$program.name'>$encoder.htmlEncode( $program.name )</option>
#end
</select>
</td>
@@ -96,7 +100,7 @@
</tr>
<tr>
<td>
- <select id="programStage" name="programStage" style="min-width:20em" onChange="getPrgramStageDataElements();">
+ <select id="programStage" name="programStage" style="width:20em" onChange="getPrgramStageDataElements();">
</select>
</td>
</tr>
@@ -151,7 +155,7 @@
<table>
<tr>
<td>
- <select id="caseProperty" name="caseProperty" size="8" ondblclick="insertInfo(this);" style="width:18em; height:16.5em" >
+ <select id="caseProperty" name="caseProperty" size="4" ondblclick="insertInfo(this);" style="width:18em; height:16.5em" >
#foreach( $program in $programs )
<option value="[PG:$program.id]">$i18n.getString('program'): $encoder.htmlEncode( $program.name )</option>
#end
=== 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 2011-10-26 03:40:58 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/updateCaseAggregation.vm 2011-11-02 07:04:50 +0000
@@ -50,7 +50,11 @@
</td>
</tr>
<tr>
- <input type="radio" id="operator" name="operator" value="COUNT" checked style='display:none;'/>
+ <td width="20em"><label for="operator">$i18n.getString( "operator" )</label></td>
+ <td>
+ <input type="radio" id="operator" name="operator" value="COUNT" #if($caseAggregation.operator=="COUNT") checked #end > $i18n.getString('count')
+ <input type="radio" id="operator" name="operator" value="SUM" #if($caseAggregation.operator=="SUM") checked #end > $i18n.getString('sum')
+ </td>
</tr>
<tr>
<td colspan="2"><p></p></td>
@@ -74,9 +78,7 @@
<td>
<fieldset style="border: 1px solid #3f5d8e; ">
<legend>$i18n.getString( "program_stage_de" )</legend>
-
<table>
-
<tr>
<td>
<label for="program">$i18n.getString( "program" )</label>
@@ -124,9 +126,7 @@
<td>
<fieldset style="border: 1px solid #3f5d8e; ">
<legend>$i18n.getString( "case_attributes" )</legend>
-
<table>
-
<tr>
<td>
<select id="caseProperty" name="caseProperty" size="5" ondblclick="insertInfo(this);" style="width:20em; height:14.5em" >
@@ -147,12 +147,10 @@
<td>
<fieldset style="border: 1px solid #3f5d8e; ">
<legend>$i18n.getString( "program_properties" )</legend>
-
<table>
-
<tr>
<td>
- <select id="caseProperty" name="caseProperty" size="5" ondblclick="insertInfo(this);" style="width:15em; height:14.5em" >
+ <select id="caseProperty" name="caseProperty" size="5" ondblclick="insertInfo(this);" style="min-width:15em; height:16.5em" >
#foreach( $program in $programs )
<option value="[PG:$program.id]">$i18n.getString('program'): $encoder.htmlEncode( $program.name )</option>
#end
