=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java 2013-04-28 08:54:29 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationCondition.java 2013-05-02 03:31:45 +0000 @@ -46,17 +46,17 @@ public static final String SEPARATOR_OBJECT = ":"; public static final String AGGRERATION_COUNT = "COUNT"; - + public static final String AGGRERATION_SUM = "times"; - + public static final String AGGRERATION_SUM_VALUE = "sum"; - + public static final String AGGRERATION_AVG_VALUE = "avg"; - + public static final String AGGRERATION_AVG_MIN = "min"; - + public static final String AGGRERATION_AVG_MAX = "max"; - + public static final String OPERATOR_AND = "AND"; public static final String OPERATOR_OR = "OR"; @@ -74,21 +74,24 @@ public static String OBJECT_PATIENT = "PT"; public static String OBJECT_PROGRAM_STAGE = "PS"; - + public static String OBJECT_PROGRAM_STAGE_PROPERTY = "PSP"; - + public static String OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY = "PC"; - + public static String OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE = "PSIC"; - + public static String OBJECT_PROGRAM_PROPERTY_INCIDENT_DATE = "dateOfIncident"; + public static String OBJECT_PROGRAM_PROPERTY_ENROLLEMENT_DATE = "enrollmentDate"; - public static final String regExp = "\\[(" + OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE + "|" + OBJECT_PATIENT + "|" + OBJECT_PROGRAM + "|" + OBJECT_PROGRAM_STAGE + "|" - + OBJECT_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY + "|" - + OBJECT_PROGRAM_STAGE_DATAELEMENT + "|" + OBJECT_PATIENT_ATTRIBUTE + "|" + OBJECT_PATIENT_PROPERTY + "|" - + OBJECT_PROGRAM_PROPERTY + ")" + SEPARATOR_OBJECT + "([a-zA-Z0-9@#\\- ]+[" + SEPARATOR_ID + "[a-zA-Z0-9]*]*)" - + "\\]"; + public static String AUTO_STORED_BY = "DHIS-SYSTEM"; + + public static final String regExp = "\\[(" + OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE + "|" + OBJECT_PATIENT + "|" + + OBJECT_PROGRAM + "|" + OBJECT_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PROGRAM_STAGE + "|" + + OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY + "|" + OBJECT_PROGRAM_STAGE_DATAELEMENT + "|" + + OBJECT_PATIENT_ATTRIBUTE + "|" + OBJECT_PATIENT_PROPERTY + "|" + OBJECT_PROGRAM_PROPERTY + ")" + + SEPARATOR_OBJECT + "([a-zA-Z0-9@#\\- ]+[" + SEPARATOR_ID + "[a-zA-Z0-9]*]*)" + "\\]"; // ------------------------------------------------------------------------- // Fields @@ -101,7 +104,7 @@ private DataElement aggregationDataElement; private DataElementCategoryOptionCombo optionCombo; - + private DataElement deSum; // ------------------------------------------------------------------------- @@ -123,9 +126,8 @@ this.optionCombo = optionCombo; } - - public CaseAggregationCondition( String name, String operator, String aggregationExpression, DataElement aggregationDataElement, - DataElementCategoryOptionCombo optionCombo, DataElement deSum ) + public CaseAggregationCondition( String name, String operator, String aggregationExpression, + DataElement aggregationDataElement, DataElementCategoryOptionCombo optionCombo, DataElement deSum ) { this.name = name; this.operator = operator; === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionService.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionService.java 2013-04-28 08:54:29 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionService.java 2013-05-02 03:31:45 +0000 @@ -30,9 +30,11 @@ import java.util.Collection; import java.util.List; +import org.hisp.dhis.common.Grid; import org.hisp.dhis.dataelement.DataElement; import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; -import org.hisp.dhis.organisationunit.OrganisationUnit; +import org.hisp.dhis.i18n.I18n; +import org.hisp.dhis.i18n.I18nFormat; import org.hisp.dhis.patient.Patient; import org.hisp.dhis.patient.PatientAttribute; import org.hisp.dhis.patientdatavalue.PatientDataValue; @@ -52,34 +54,42 @@ void updateCaseAggregationCondition( CaseAggregationCondition caseAggregationCondition ); void deleteCaseAggregationCondition( CaseAggregationCondition caseAggregationCondition ); - + CaseAggregationCondition getCaseAggregationCondition( int id ); - - Collection getAllCaseAggregationCondition( ); - + + Collection getAllCaseAggregationCondition(); + Collection getCaseAggregationCondition( DataElement dataElement ); - - CaseAggregationCondition getCaseAggregationCondition( DataElement dataElement, DataElementCategoryOptionCombo optionCombo ); - + + CaseAggregationCondition getCaseAggregationCondition( DataElement dataElement, + DataElementCategoryOptionCombo optionCombo ); + Collection getCaseAggregationCondition( Collection dataElements ); - - Double getAggregateValue( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period ); - - Collection getPatientDataValues( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period ); - - Collection getPatients( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period ); - - Collection getProgramStageInstances( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, Period period ); - + + Grid getAggregateValue( CaseAggregationCondition aggregationCondition, Collection orgunitIds, + Period period, I18nFormat format, I18n i18n ); + + void insertAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, + Period period ); + + Collection getPatientDataValues( CaseAggregationCondition aggregationCondition, + Collection orgunitIds, Period period ); + + Collection getPatients( CaseAggregationCondition aggregationCondition, Collection orgunitIds, + Period period ); + + Collection getProgramStageInstances( CaseAggregationCondition aggregationCondition, + Collection orgunitIds, Period period ); + Collection getDataElementsInCondition( String aggregationExpression ); - + Collection getProgramsInCondition( String aggregationExpression ); - + Collection getPatientAttributesInCondition( String aggregationExpression ); - + String getConditionDescription( String condition ); - + void aggregate( List caseAggregateSchedules, String taskStrategy ); - + boolean hasOrgunitProgramStageCompleted( String expresstion ); } === modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionManager.java' --- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionManager.java 2013-04-28 08:54:29 +0000 +++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/CaseAggregationConditionManager.java 2013-05-02 03:31:45 +0000 @@ -27,10 +27,14 @@ package org.hisp.dhis.caseaggregation; +import java.util.Collection; import java.util.List; import java.util.concurrent.ConcurrentLinkedQueue; import java.util.concurrent.Future; +import org.hisp.dhis.common.Grid; +import org.hisp.dhis.i18n.I18n; +import org.hisp.dhis.i18n.I18nFormat; import org.hisp.dhis.period.Period; /** @@ -45,36 +49,48 @@ * datasets which have data elements defined in the formulas * * @param caseAggregateSchedule - * @param taskStrategy Specify how to get period list based on period type of each - * dataset. There are four options, include last month, last 3 month, - * last 6 month and last 12 month + * @param taskStrategy Specify how to get period list based on period type + * of each dataset. There are four options, include last month, last + * 3 month, last 6 month and last 12 month */ Future aggregate( ConcurrentLinkedQueue caseAggregateSchedule, String taskStrategy ); /** - * Return a data value aggregated of query builder formula - * - * @param caseExpression The query builder expression - * @param operator There are six operators, includes Number of persons, - * Number of visits, Sum, Average, Minimum and Maximum of data - * element values. - * @param deSumId The id of aggregate data element which used for aggregate - * data values for operator Sum, Average, Minimum and Maximum of data - * element values. This fill is null for other operators. - * @param orgunitId The id of organisation unit where to aggregate data - * value - * @param period The date range for aggregate data value - */ - Double getAggregateValue( String caseExpression, String operator, Integer deSumId, - Integer orgunitId, Period period ); - - /** - * Return standard SQL from query builder formula - * - * @param caseExpression The query builder expression - * @param operator There are six operators, includes Number of persons, - * Number of visits, Sum, Average, Minimum and Maximum of data - * element values. + * Return a data value table aggregated of a query builder formula + * + * @param caseAggregationCondition The query builder expression + * @param orgunitIds The ids of organisation unit where to aggregate data + * value + * @param period The date range for aggregate data value + * @param format + * @param i18n + */ + Grid getAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, + Period period, I18nFormat format, I18n i18n ); + + /** + * Insert data values into database directly + * + * @param caseAggregationCondition The query builder expression + * @param orgunitIds The ids of organisation unit where to aggregate data + * value + * @param period The date range for aggregate data value + */ + void insertAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, + Period period ); + + /** + * Return standard SQL from query builder formula + * + * @param isInsert Insert aggregate result into database directly + * @param caseExpression The query builder expression + * @param operator There are six operators, includes Number of persons, + * Number of visits, Sum, Average, Minimum and Maximum of data + * element values. + * @param aggregateDeId The id of aggregate data element + * @param aggregateDeName The name of aggregate data element + * @param optionComboId The id of category option combo + * @param optionComboName The name of category option combo * @param deSumId The id of aggregate data element which used for aggregate * data values for operator Sum, Average, Minimum and Maximum of data * element values. This fill is null for other operators. @@ -83,8 +99,9 @@ * @param startDate Start date * @param endDate End date */ - String parseExpressionToSql( String aggregationExpression, String operator, Integer deSumId, - Integer orgunitId, String startDate, String endDate ); + String parseExpressionToSql( boolean isInsert, String caseExpression, String operator, Integer aggregateDeId, + String aggregateDeName, Integer optionComboId, String optionComboName, Integer deSumId, + Collection orgunitIds, Period period ); boolean hasOrgunitProgramStageCompleted( String expresstion ); } === 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 2013-04-29 12:08:00 +0000 +++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/DefaultCaseAggregationConditionService.java 2013-05-02 03:31:45 +0000 @@ -28,6 +28,7 @@ package org.hisp.dhis.caseaggregation; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.AGGRERATION_SUM; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT_ATTRIBUTE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM; @@ -35,8 +36,6 @@ import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM_STAGE_DATAELEMENT; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_ID; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_OBJECT; -import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE; - import static org.hisp.dhis.i18n.I18nUtils.i18n; import java.util.ArrayList; @@ -48,11 +47,13 @@ import java.util.regex.Matcher; import java.util.regex.Pattern; +import org.hisp.dhis.common.Grid; import org.hisp.dhis.dataelement.DataElement; import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; import org.hisp.dhis.dataelement.DataElementService; +import org.hisp.dhis.i18n.I18n; +import org.hisp.dhis.i18n.I18nFormat; import org.hisp.dhis.i18n.I18nService; -import org.hisp.dhis.organisationunit.OrganisationUnit; import org.hisp.dhis.patient.Patient; import org.hisp.dhis.patient.PatientAttribute; import org.hisp.dhis.patient.PatientAttributeService; @@ -211,23 +212,25 @@ } @Override - public Double getAggregateValue( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, - Period period ) - { - DataElement deSum = aggregationCondition.getDeSum(); - Integer deSumId = (deSum == null) ? null : deSum.getId(); - - return aggregationConditionManager.getAggregateValue( aggregationCondition.getAggregationExpression(), - aggregationCondition.getOperator(), deSumId, orgunit.getId(), period ); - } - + public Grid getAggregateValue( CaseAggregationCondition aggregationCondition, Collection orgunitIds, + Period period, I18nFormat format, I18n i18n ) + { + return aggregationConditionManager.getAggregateValue( aggregationCondition, orgunitIds, period, format, i18n ); + } + + @Override + public void insertAggregateValue( CaseAggregationCondition caseAggregationCondition, + Collection orgunitIds, Period period ) + { + aggregationConditionManager.insertAggregateValue( caseAggregationCondition, orgunitIds, period); + } + @Override public Collection getPatientDataValues( CaseAggregationCondition aggregationCondition, - OrganisationUnit orgunit, Period period ) + Collection orgunitIds, Period period ) { // get params - int orgunitId = orgunit.getId(); String startDate = DateUtils.getMediumDateString( period.getStartDate() ); String endDate = DateUtils.getMediumDateString( period.getEndDate() ); DataElement deSum = aggregationCondition.getDeSum(); @@ -235,9 +238,10 @@ Collection result = new HashSet(); - String sql = aggregationConditionManager.parseExpressionToSql( aggregationCondition.getAggregationExpression(), - aggregationCondition.getOperator(), deSumId, orgunitId, startDate, endDate ); +// String sql = aggregationConditionManager.parseExpressionToSql( aggregationCondition.getAggregationExpression(), +// aggregationCondition.getOperator(), deSumId, orgunitIds, startDate, endDate ); + String sql = ""; Collection dataElements = getDataElementsInCondition( aggregationCondition .getAggregationExpression() ); @@ -259,17 +263,18 @@ return result; } - public Collection getPatients( CaseAggregationCondition aggregationCondition, OrganisationUnit orgunit, - Period period ) + public Collection getPatients( CaseAggregationCondition aggregationCondition, + Collection orgunitIds, Period period ) { DataElement deSum = aggregationCondition.getDeSum(); Integer deSumId = (deSum == null) ? null : deSum.getId(); - String sql = aggregationConditionManager - .parseExpressionToSql( aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(), - deSumId, orgunit.getId(), DateUtils.getMediumDateString( period.getStartDate() ), - DateUtils.getMediumDateString( period.getEndDate() ) ); +// String sql = aggregationConditionManager +// .parseExpressionToSql( aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(), +// deSumId, orgunitIds, DateUtils.getMediumDateString( period.getStartDate() ), +// DateUtils.getMediumDateString( period.getEndDate() ) ); + String sql = ""; Collection result = new HashSet(); Collection patientIds = aggregationConditionManager.executeSQL( sql ); @@ -286,12 +291,11 @@ } public Collection getProgramStageInstances( CaseAggregationCondition aggregationCondition, - OrganisationUnit orgunit, Period period ) + Collection orgunitIds, Period period ) { Collection result = new HashSet(); // get params - int orgunitId = orgunit.getId(); String startDate = DateUtils.getMediumDateString( period.getStartDate() ); String endDate = DateUtils.getMediumDateString( period.getEndDate() ); @@ -307,8 +311,9 @@ DataElement deSum = aggregationCondition.getDeSum(); Integer deSumId = (deSum == null) ? null : deSum.getId(); - sql = aggregationConditionManager.parseExpressionToSql( aggregationCondition.getAggregationExpression(), - aggregationCondition.getOperator(), deSumId, orgunitId, startDate, endDate ); +// sql = aggregationConditionManager.parseExpressionToSql( aggregationCondition.getAggregationExpression(), +// aggregationCondition.getOperator(), deSumId, orgunitIds, startDate, endDate ); + sql = ""; } else { @@ -330,12 +335,14 @@ DataElement deSum = aggregationCondition.getDeSum(); Integer deSumId = (deSum == null) ? null : deSum.getId(); - String conditionSql = aggregationConditionManager.parseExpressionToSql( - aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(), deSumId, - orgunit.getId(), DateUtils.getMediumDateString( period.getStartDate() ), - DateUtils.getMediumDateString( period.getEndDate() ) ); - - sql += conditionSql + " ) "; +// String conditionSql = aggregationConditionManager.parseExpressionToSql( +// aggregationCondition.getAggregationExpression(), aggregationCondition.getOperator(), deSumId, +// orgunitIds, DateUtils.getMediumDateString( period.getStartDate() ), +// DateUtils.getMediumDateString( period.getEndDate() ) ); +// +// sql += conditionSql + " ) "; + + sql = ""; } } @@ -428,8 +435,8 @@ } matcher.appendReplacement( description, "[" + programDes + "]" ); } - else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE ) - || info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) ) + else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE ) + || info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) ) { int objectId = Integer.parseInt( ids[0] ); ProgramStage programStage = programStageService.getProgramStage( objectId ); @@ -440,8 +447,8 @@ } String count = (ids.length == 2) ? SEPARATOR_ID + ids[1] : ""; - matcher.appendReplacement( description, "[" + info[0] + SEPARATOR_OBJECT - + programStage.getDisplayName() + count + "]" ); + matcher.appendReplacement( description, + "[" + info[0] + SEPARATOR_OBJECT + programStage.getDisplayName() + count + "]" ); } } @@ -584,4 +591,5 @@ { return patientIds.size(); } + } === modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java' --- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java 2013-04-29 12:08:00 +0000 +++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java 2013-05-02 03:31:45 +0000 @@ -27,7 +27,7 @@ package org.hisp.dhis.caseaggregation.jdbc; -import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.AGGRERATION_COUNT; +import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT_ATTRIBUTE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY; @@ -37,8 +37,6 @@ import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM_STAGE; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM_STAGE_DATAELEMENT; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_PROGRAM_STAGE_PROPERTY; -import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE; -import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.OPERATOR_AND; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_ID; import static org.hisp.dhis.caseaggregation.CaseAggregationCondition.SEPARATOR_OBJECT; import static org.hisp.dhis.patient.scheduling.CaseAggregateConditionSchedulingManager.TASK_AGGREGATE_QUERY_BUILDER_LAST_12_MONTH; @@ -48,7 +46,6 @@ import java.sql.ResultSet; import java.sql.SQLException; -import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; import java.util.Date; @@ -62,14 +59,16 @@ import org.hisp.dhis.caseaggregation.CaseAggregateSchedule; import org.hisp.dhis.caseaggregation.CaseAggregationCondition; import org.hisp.dhis.caseaggregation.CaseAggregationConditionManager; -import org.hisp.dhis.dataelement.DataElement; -import org.hisp.dhis.dataelement.DataElementService; -import org.hisp.dhis.jdbc.StatementBuilder; +import org.hisp.dhis.common.Grid; +import org.hisp.dhis.common.GridHeader; +import org.hisp.dhis.i18n.I18n; +import org.hisp.dhis.i18n.I18nFormat; import org.hisp.dhis.period.CalendarPeriodType; import org.hisp.dhis.period.Period; import org.hisp.dhis.period.PeriodType; +import org.hisp.dhis.system.grid.ListGrid; import org.hisp.dhis.system.util.DateUtils; -import org.nfunk.jep.JEP; +import org.hisp.dhis.system.util.TextUtils; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.rowset.SqlRowSet; @@ -106,20 +105,6 @@ this.jdbcTemplate = jdbcTemplate; } - private StatementBuilder statementBuilder; - - public void setStatementBuilder( StatementBuilder statementBuilder ) - { - this.statementBuilder = statementBuilder; - } - - public DataElementService dataElementService; - - public void setDataElementService( DataElementService dataElementService ) - { - this.dataElementService = dataElementService; - } - // ------------------------------------------------------------------------- // Implementation Methods // ------------------------------------------------------------------------- @@ -167,87 +152,187 @@ return null; } - public Double getAggregateValue( String caseExpression, String operator, Integer deSumId, Integer orgunitId, - Period period ) - { - String startDate = DateUtils.getMediumDateString( period.getStartDate() ); - String endDate = DateUtils.getMediumDateString( period.getEndDate() ); + public Grid getAggregateValue( CaseAggregationCondition caseAggregationCondition, Collection orgunitIds, + Period period, I18nFormat format, I18n i18n ) + { + Collection _orgunitIds = getServiceOrgunit( DateUtils.getMediumDateString( period.getStartDate() ), + DateUtils.getMediumDateString( period.getEndDate() ) ); + orgunitIds.retainAll( _orgunitIds ); + + if ( orgunitIds.size() > 0 ) + { + Grid grid = new ListGrid(); + grid.setTitle( caseAggregationCondition.getDisplayName() ); + grid.setSubtitle( format.formatPeriod( period ) ); + + grid.addHeader( new GridHeader( i18n.getString( "dataelementid" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "categoryoptioncomboid" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "periodid" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "organisationunitid" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "comment" ), true, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "dataelementname" ), false, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "categoryoptioncomboname" ), false, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "organisationunitname" ), false, true ) ); + grid.addHeader( new GridHeader( i18n.getString( "value" ), false, true ) ); + + Integer deSumId = (caseAggregationCondition.getDeSum() == null) ? null : caseAggregationCondition + .getDeSum().getId(); + String sql = parseExpressionToSql( false, caseAggregationCondition.getAggregationExpression(), + caseAggregationCondition.getOperator(), caseAggregationCondition.getAggregationDataElement().getId(), + caseAggregationCondition.getAggregationDataElement().getDisplayName(), caseAggregationCondition + .getOptionCombo().getId(), caseAggregationCondition.getOptionCombo().getDisplayName(), deSumId, + orgunitIds, period ); + + SqlRowSet rs = jdbcTemplate.queryForRowSet( sql ); + grid.addRows( rs ); + + return grid; + } + + return null; + } + + /** + * Insert data elements into database directly + * + */ + public void insertAggregateValue( CaseAggregationCondition caseAggregationCondition, + Collection orgunitIds, Period period ) + { + Integer deSumId = (caseAggregationCondition.getDeSum() == null) ? null : caseAggregationCondition.getDeSum() + .getId(); + + insertAggregateValue( caseAggregationCondition.getAggregationExpression(), + caseAggregationCondition.getOperator(), caseAggregationCondition.getAggregationDataElement().getId(), + caseAggregationCondition.getOptionCombo().getId(), deSumId, orgunitIds, period ); + } + + private void insertAggregateValue( String expression, String operator, Integer dataElementId, + Integer optionComboId, Integer deSumId, Collection orgunitIds, Period period ) + { + // Delete all data value from this period which created from DHIS-system + // after to run Aggregate Query Builder + + String periodtypeSql = "select periodtypeid from periodtype where name='" + period.getPeriodType().getName() + + "'"; + int periodTypeId = jdbcTemplate.queryForObject( periodtypeSql, Integer.class ); + + String periodSql = "select periodid from period where periodtypeid=" + periodTypeId + " and startdate='" + + DateUtils.getMediumDateString( period.getStartDate() ) + "' and enddate='" + + DateUtils.getMediumDateString( period.getEndDate() ) + "'"; + + SqlRowSet rs = jdbcTemplate.queryForRowSet( periodSql ); + + int periodid = 0; + if ( rs.next() ) + { + periodid = rs.getInt( "periodid" ); + } + + if ( periodid == 0 ) + { + String insertSql = "insert into period (periodtypeid,startdate,enddate) " + " VALUES " + "(" + + period.getPeriodType().getId() + ",'" + DateUtils.getMediumDateString( period.getStartDate() ) + + "','" + DateUtils.getMediumDateString( period.getEndDate() ) + "' )"; + jdbcTemplate.execute( insertSql ); + + period.setId( jdbcTemplate.queryForObject( insertSql, Integer.class ) ); + } + else + { + period.setId( periodid ); + + String deleteDataValueSql = "delete from datavalue where dataelementid=" + dataElementId + + " and categoryoptioncomboid=" + optionComboId + " and sourceid in (" + + TextUtils.getCommaDelimitedString( orgunitIds ) + ") and periodid=" + periodid + ""; + + jdbcTemplate.execute( deleteDataValueSql ); + } + + // insert data elements into database directly + + String sql = parseExpressionToSql( true, expression, operator, dataElementId, "dataelementname", optionComboId, + "optionComboname", deSumId, orgunitIds, period ); + + jdbcTemplate.execute( sql ); + } + + public String parseExpressionToSql( boolean isInsert, String caseExpression, String operator, + Integer aggregateDeId, String aggregateDeName, Integer optionComboId, String optionComboName, Integer deSumId, + Collection orgunitIds, Period period ) + { + String sql = "SELECT '" + aggregateDeId + "' as dataelementid, '" + optionComboId + + "' as categoryoptioncomboid, ou.organisationunitid as sourceid, '" + period.getId() + "' as periodid,'" + + CaseAggregationCondition.AUTO_STORED_BY + "' as comment, "; + + if ( isInsert ) + { + sql = "INSERT INTO datavalue (dataelementid, categoryoptioncomboid, sourceid, periodid, comment, value)" + + sql; + } + else + { + sql += "'" + period.getIsoDate() + "' as periodIsoDate,'" + aggregateDeName + "' as dataelementname, '" + + optionComboName + "' as categoryoptioncomboname, " + "ou.name as organisationunitname, "; + } if ( operator.equals( CaseAggregationCondition.AGGRERATION_COUNT ) || operator.equals( CaseAggregationCondition.AGGRERATION_SUM ) ) { - String sql = parseExpressionToSql( caseExpression, operator, deSumId, orgunitId, startDate, endDate ); - Collection ids = this.executeSQL( sql ); - if ( hasOrgunitProgramStageCompleted( caseExpression ) ) { - return (ids == null || ids.size() == 0) ? null : ids.iterator().next() + 0.0; + sql += createSQL( caseExpression, operator, orgunitIds, + DateUtils.getMediumDateString( period.getStartDate() ), + DateUtils.getMediumDateString( period.getEndDate() ) ); } else { - return (ids == null || ids.size() == 0) ? null : ids.size() + 0.0; - } - } - - String sql = "SELECT " + operator + "( cast( pdv.value as DOUBLE PRECISION ) ) "; - sql += "FROM patientdatavalue pdv "; - sql += " INNER JOIN programstageinstance psi "; - sql += " ON psi.programstageinstanceid = pdv.programstageinstanceid "; - sql += "WHERE executiondate >='" + DateUtils.getMediumDateString( period.getStartDate() ) + "' "; - sql += " AND executiondate <='" + DateUtils.getMediumDateString( period.getEndDate() ) - + "' AND pdv.dataelementid=" + deSumId; - - if ( caseExpression != null && !caseExpression.isEmpty() ) - { - sql = sql + " AND pdv.programstageinstanceid in ( " - + parseExpressionToSql( caseExpression, operator, deSumId, orgunitId, startDate, endDate ) + " ) "; - } -System.out.println("\n " + sql + " \n "); - Collection ids = this.executeSQL( sql ); - return (ids == null) ? null : ids.iterator().next() + 0.0; - } - - public String parseExpressionToSql( String aggregationExpression, String operator, Integer deSumId, - Integer orgunitId, String startDate, String endDate ) - { - // Get operators between ( ) - Pattern patternOperator = Pattern.compile( "(\\)\\s*(OR|AND)\\s*\\( )" ); - - Matcher matcherOperator = patternOperator.matcher( aggregationExpression ); - - List operators = new ArrayList(); - - while ( matcherOperator.find() ) - { - operators.add( matcherOperator.group( 2 ) ); - } - - List subSQL = new ArrayList(); - - String[] conditions = aggregationExpression.split( "(\\)\\s*(OR|AND)\\s*\\()" ); - - // Create SQL statement for the first condition - String condition = conditions[0].replace( "(", "" ).replace( ")", "" ); - - String sql = createSQL( condition, operator, orgunitId, startDate, endDate ); - - subSQL.add( sql ); - - // Create SQL statement for others - for ( int index = 1; index < conditions.length; index++ ) - { - condition = conditions[index].replace( "(", "" ).replace( ")", "" ); - - sql = "(" + createSQL( condition, operator, orgunitId, startDate, endDate ) + ")"; - - subSQL.add( sql ); - } - - sql = getSQL( operator, subSQL, operators ).replace( IN_CONDITION_START_SIGN, "(" ).replaceAll( - IN_CONDITION_END_SIGN, ")" ); - - sql.replaceAll( "COMBINE", " " ); - + if ( operator.equals( CaseAggregationCondition.AGGRERATION_COUNT ) ) + { + sql += operator + " (distinct(pi.patientid) ) as value "; + } + else + { + sql += operator + " (distinct(psi.programinstanceid ) ) as value "; + } + + sql += "FROM programstageinstance as psi " + + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid " + + "INNER JOIN organisationunit ou on ou.organisationunitid=psi.organisationunitid " + + "INNER JOIN patient p on p.patientid=pi.patientid WHERE " + + createSQL( caseExpression, operator, orgunitIds, + DateUtils.getMediumDateString( period.getStartDate() ), + DateUtils.getMediumDateString( period.getEndDate() ) ); + + sql += "GROUP BY ou.organisationunitid, ou.name"; + } + } + else + { + sql += " " + operator + "( cast( pdv.value as DOUBLE PRECISION ) ) "; + sql += "FROM patientdatavalue pdv "; + sql += " INNER JOIN programstageinstance psi "; + sql += " ON psi.programstageinstanceid = pdv.programstageinstanceid "; + sql += " INNER JOIN organisationunit ou "; + sql += " ON ou.organisationunitid=psi.organisationunitid "; + sql += "WHERE executiondate >='" + DateUtils.getMediumDateString( period.getStartDate() ) + "' "; + sql += " AND executiondate <='" + DateUtils.getMediumDateString( period.getEndDate() ) + + "' AND pdv.dataelementid=" + deSumId; + + if ( caseExpression != null && !caseExpression.isEmpty() ) + { + sql += " AND " + + createSQL( caseExpression, operator, orgunitIds, + DateUtils.getMediumDateString( period.getStartDate() ), + DateUtils.getMediumDateString( period.getEndDate() ) ); + } + + sql += "GROUP BY ou.organisationunitid, ou.name"; + + } + + sql = sql.replaceAll( "COMBINE", "" ); + System.out.println( "\n\n ==== \n " + sql ); return sql; } @@ -263,13 +348,12 @@ private void runAggregate( Collection orgunitIds, CaseAggregateSchedule dataSet, Collection periods ) { String sql = "select caseaggregationconditionid, aggregationdataelementid, optioncomboid, " - + " cagg.aggregationexpression as caseexpression, cagg.\"operator\" as caseoperator, cagg.desum as desumid " + + " cagg.aggregationexpression as caseexpression, cagg.operator as caseoperator, cagg.desum as desumid " + " from caseaggregationcondition cagg inner join datasetmembers dm " - + " on cagg.aggregationdataelementid=dm.dataelementid " + " inner join dataset ds " - + " on ds.datasetid = dm.datasetid " + " inner join periodtype pt " - + " on pt.periodtypeid=ds.periodtypeid " + " inner join dataelement de " - + " on de.dataelementid=dm.dataelementid " + " where ds.datasetid = " - + dataSet.getDataSetId(); + + " on cagg.aggregationdataelementid=dm.dataelementid inner join dataset ds " + + " on ds.datasetid = dm.datasetid inner join periodtype pt " + + " on pt.periodtypeid=ds.periodtypeid inner join dataelement de " + + " on de.dataelementid=dm.dataelementid where ds.datasetid = " + dataSet.getDataSetId(); SqlRowSet rs = jdbcTemplate.queryForRowSet( sql ); @@ -305,64 +389,8 @@ // Aggregation // --------------------------------------------------------------------- - for ( Integer orgunitId : orgunitIds ) - { - String dataValueSql = "select * from datavalue where dataelementid=" + dataelementId - + " and categoryoptioncomboid=" + optionComboId + " and sourceid=" + orgunitId - + " and periodid=" + period.getId() + ""; - - boolean hasValue = jdbcTemplate.queryForRowSet( dataValueSql ).next(); - - Double resultValue = getAggregateValue( caseExpression, caseOperator, deSumId, orgunitId, period ); - - if ( resultValue != null && resultValue != 0 ) - { - // ----------------------------------------------------- - // Add dataValue - // ----------------------------------------------------- - - if ( !hasValue ) - { - String insertValueSql = "INSERT INTO datavalue ( dataelementid, periodid, sourceid, categoryoptioncomboid, value, storedby, lastupdated, followup ) " - + "VALUES ( " - + dataelementId - + ", " - + period.getId() - + ", " - + orgunitId - + ", " - + optionComboId - + ", " - + resultValue - + ", '" - + STORED_BY_DHIS_SYSTEM - + "', '" - + DateUtils.getMediumDateString( new Date() ) + "', false )"; - jdbcTemplate.execute( insertValueSql ); - } - - // ----------------------------------------------------- - // Update dataValue - // ----------------------------------------------------- - else - { - sql = "UPDATE datavalue" + " SET value='" + resultValue + "',lastupdated='" + new Date() - + "' where dataelementId=" + dataelementId + " and periodid=" + period.getId() - + " and sourceid=" + orgunitId + " and categoryoptioncomboid=" + optionComboId - + " and storedby='" + STORED_BY_DHIS_SYSTEM + "'"; - jdbcTemplate.execute( sql ); - } - } - - // --------------------------------------------------------- - // Delete dataValue - // --------------------------------------------------------- - else if ( hasValue ) - { - String deleteSql = "DELETE from datavalue where dataelementid=dataelementid and periodid=periodid and sourceid=sourceid and categoryoptioncomboid=categoryoptioncomboid"; - jdbcTemplate.execute( deleteSql ); - } - } + insertAggregateValue( caseExpression, caseOperator, dataelementId, optionComboId, deSumId, orgunitIds, + period ); } } @@ -449,33 +477,23 @@ * Number of visits, Sum, Average, Minimum and Maximum of data * element values. * @param deType Aggregate Data element type - * @param orgunitId The id of organisation unit where to aggregate data + * @param orgunitIds The ids of organisation units where to aggregate data * value * @param startDate Start date * @param endDate End date */ - private String createSQL( String caseExpression, String operator, int orgunitId, String startDate, String endDate ) + private String createSQL( String caseExpression, String operator, Collection orgunitIds, String startDate, + String endDate ) { boolean orgunitCompletedProgramStage = false; + StringBuffer sqlResult = new StringBuffer(); + String sqlOrgunitCompleted = ""; - // --------------------------------------------------------------------- - // get operators - // --------------------------------------------------------------------- - - Pattern patternOperator = Pattern.compile( "(AND|OR)" ); - - Matcher matcherOperator = patternOperator.matcher( caseExpression ); - - List operators = new ArrayList(); - - while ( matcherOperator.find() ) - { - operators.add( matcherOperator.group() ); - } - - String[] expression = caseExpression.split( "(AND|OR|COMBINE)" ); + String[] expression = caseExpression.split( "(AND|OR)" ); + caseExpression = caseExpression.replaceAll( "AND", " ) AND " ); + caseExpression = caseExpression.replaceAll( "OR", " ) OR " ); // --------------------------------------------------------------------- // parse expressions @@ -483,255 +501,153 @@ Pattern patternCondition = Pattern.compile( CaseAggregationCondition.regExp ); - List conditions = new ArrayList(); - double value = 0.0; - - for ( int i = 0; i < expression.length; i++ ) + Matcher matcherCondition = patternCondition.matcher( caseExpression ); + + String condition = ""; + + int index = 0; + while ( matcherCondition.find() ) { - String subExp = expression[i]; - - List subConditions = new ArrayList(); - - Matcher matcherCondition = patternCondition.matcher( expression[i] ); - - String condition = ""; - - while ( matcherCondition.find() ) - { - String match = matcherCondition.group(); - - subExp = subExp.replace( match, "~" ); - match = match.replaceAll( "[\\[\\]]", "" ); - - String[] info = match.split( SEPARATOR_OBJECT ); - - if ( info[0].equalsIgnoreCase( OBJECT_PATIENT ) ) - { - condition = getConditionForPatient( orgunitId, operator, startDate, endDate ); - } - else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROPERTY ) ) - { - String propertyName = info[1]; - condition = getConditionForPatientProperty( propertyName, operator, startDate, endDate ); - } - else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_ATTRIBUTE ) ) - { - int attributeId = Integer.parseInt( info[1] ); - condition = getConditionForPatientAttribute( attributeId, operator ); - } - else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_DATAELEMENT ) ) - { - String[] ids = info[1].split( SEPARATOR_ID ); - - int programId = Integer.parseInt( ids[0] ); - String programStageId = ids[1]; - int dataElementId = Integer.parseInt( ids[2] ); - DataElement dataElement = dataElementService.getDataElement( dataElementId ); - - String valueToCompare = expression[i].replace( "[" + match + "]", "" ).trim(); - - if ( valueToCompare.equalsIgnoreCase( IS_NULL ) ) - { - condition = getConditionForNotDataElement( programId, programStageId, operator, dataElementId, - orgunitId, startDate, endDate ); - - expression[i] = expression[i].replace( valueToCompare, "" ); - } - else - { - condition = getConditionForDataElement( programId, programStageId, operator, dataElementId, - orgunitId, startDate, endDate ); - - if ( !expression[i].contains( "+" ) ) - { - if ( dataElement.getType().equals( DataElement.VALUE_TYPE_INT ) ) - { - condition += " AND cast( pd.value as " + statementBuilder.getDoubleColumnType() + ") "; - } - else - { - condition += " AND pd.value "; - } - } - else - { - subConditions.add( condition ); - } - } - } - - else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_PROPERTY ) ) - { - condition = getConditionForProgramProperty( operator, startDate, endDate, info[1] ); - } - else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM ) ) - { - String[] ids = info[1].split( SEPARATOR_ID ); - condition = getConditionForProgram( ids[0], operator, orgunitId, startDate, endDate ); - if ( ids.length > 1 ) - { - condition += ids[1]; - } - } - else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE ) ) - { - String[] ids = info[1].split( SEPARATOR_ID ); - if ( ids.length == 2 && ids[1].equals( IN_CONDITION_COUNT_X_TIMES ) ) - { - condition = getConditionForCountProgramStage( ids[0], operator, orgunitId, startDate, endDate ); - } - else - { - condition = getConditionForProgramStage( ids[0], operator, orgunitId, startDate, endDate ); - } - } - else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_PROPERTY ) ) - { - condition = getConditionForProgramStageProperty( info[1], operator, orgunitId, startDate, endDate ); - } - else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY ) ) - { - condition = getConditionForPatientProgramStageProperty( info[1], operator, startDate, endDate ); - } - else if ( info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) ) - { - sqlOrgunitCompleted += getConditionForOrgunitProgramStageCompleted( info[1], operator, orgunitId, - startDate, endDate, orgunitCompletedProgramStage ); - orgunitCompletedProgramStage = true; - } - // ------------------------------------------------------------- - // Replacing the operand with 1 in order to later be able to - // verify - // that the formula is mathematically valid - // ------------------------------------------------------------- - - if ( expression[i].contains( "+" ) ) - { - Collection patientIds = executeSQL( condition ); - value = patientIds.size(); - - subExp = subExp.replace( "~", value + "" ); - } - - condition = expression[i].replace( match, condition ).replaceAll( "[\\[\\]]", "" ); - } - - if ( expression[i].contains( "+" ) ) - { - final JEP parser = new JEP(); - - parser.parseExpression( subExp ); - - String _subExp = (parser.getValue() == 1.0) ? " AND 1 = 1 " : " AND 0 = 1 "; - - int noPlus = expression[i].split( "\\+" ).length - 1; - List subOperators = new ArrayList(); - for ( int j = 0; j < noPlus; j++ ) - { - subOperators.add( "AND" ); - } - - condition = getSQL( operator, subConditions, subOperators ) + _subExp; - } - - condition = condition.trim(); - if ( !condition.isEmpty() ) - { - conditions.add( condition ); - } + String match = matcherCondition.group(); + + match = match.replaceAll( "[\\[\\]]", "" ); + + String[] info = match.split( SEPARATOR_OBJECT ); + if ( info[0].equalsIgnoreCase( OBJECT_PATIENT ) ) + { + condition = getConditionForPatient( orgunitIds, operator, startDate, endDate ); + } + else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROPERTY ) ) + { + String propertyName = info[1]; + condition = getConditionForPatientProperty( propertyName, operator, startDate, endDate ); + } + else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_ATTRIBUTE ) ) + { + int attributeId = Integer.parseInt( info[1] ); + condition = getConditionForPatientAttribute( attributeId, orgunitIds ); + condition += ")"; + } + else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_DATAELEMENT ) ) + { + String[] ids = info[1].split( SEPARATOR_ID ); + + int programId = Integer.parseInt( ids[0] ); + String programStageId = ids[1]; + int dataElementId = Integer.parseInt( ids[2] ); + + String compareValue = expression[index].replace( "[" + match + "]", "" ).trim(); + + boolean isExist = compareValue.equals( IS_NULL ) ? false : true; + condition = getConditionForDataElement( isExist, programId, programStageId, dataElementId, orgunitIds, + startDate, endDate ); + } + + else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_PROPERTY ) ) + { + condition = getConditionForProgramProperty( operator, startDate, endDate, info[1] ); + } + else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM ) ) + { + String[] ids = info[1].split( SEPARATOR_ID ); + condition = getConditionForProgram( ids[0], operator, orgunitIds, startDate, endDate ); + if ( ids.length > 1 ) + { + condition += ids[1]; + } + } + else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE ) ) + { + String[] ids = info[1].split( SEPARATOR_ID ); + if ( ids.length == 2 && ids[1].equals( IN_CONDITION_COUNT_X_TIMES ) ) + { + condition = getConditionForCountProgramStage( ids[0], operator, orgunitIds, startDate, endDate ); + } + else + { + condition = getConditionForProgramStage( ids[0], operator, orgunitIds, startDate, endDate ); + } + } + else if ( info[0].equalsIgnoreCase( OBJECT_PROGRAM_STAGE_PROPERTY ) ) + { + condition = getConditionForProgramStageProperty( info[1], operator, orgunitIds, startDate, endDate ); + } + else if ( info[0].equalsIgnoreCase( OBJECT_PATIENT_PROGRAM_STAGE_PROPERTY ) ) + { + condition = getConditionForPatientProgramStageProperty( info[1], operator, startDate, endDate ); + } + else if ( info[0].equalsIgnoreCase( OBJECT_ORGUNIT_COMPLETE_PROGRAM_STAGE ) ) + { + sqlOrgunitCompleted += getConditionForOrgunitProgramStageCompleted( info[1], operator, orgunitIds, + startDate, endDate, orgunitCompletedProgramStage ); + orgunitCompletedProgramStage = true; + } + + matcherCondition.appendReplacement( sqlResult, condition ); + + index++; } + matcherCondition.appendTail( sqlResult ); + if ( !sqlOrgunitCompleted.isEmpty() ) { - conditions.add( sqlOrgunitCompleted ); + sqlOrgunitCompleted = sqlOrgunitCompleted.substring( 0, sqlOrgunitCompleted.length() - 2 ); } - return getSQL( operator, conditions, operators ); + sqlResult.append( sqlOrgunitCompleted ); + + String sql = sqlResult.toString(); + + sql = sql.replaceAll( IN_CONDITION_START_SIGN, "(" ); + sql = sql.replaceAll( IN_CONDITION_END_SIGN, ")" ); + sql = sql.replaceAll( IS_NULL, " " ); + + return sql + " ) "; } /** * Return standard SQL of the expression to compare data value as null * */ - private String getConditionForNotDataElement( int programId, String programStageId, String operator, - int dataElementId, int orgunitId, String startDate, String endDate ) - { - String sql = "SELECT distinct(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_COUNT ) ) - { - sql = "SELECT psi.programstageinstanceid "; - condition = "psi.programstageinstanceid "; - } - - 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 - + "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 ) ) - { - sql += " AND psi.programstageid = " + programStageId; - } - - return sql + " ) "; - } - - /** - * Return standard SQL of a data element expression. E.g [DE:1.2.3] - * - */ - private String getConditionForDataElement( int programId, String programStageId, String operator, - 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_COUNT ) ) - { - sql = "SELECT psi.programstageinstanceid "; - from = "FROM programstageinstance as psi " - + "INNER JOIN patientdatavalue as pd ON psi.programstageinstanceid = pd.programstageinstanceid "; - } - - 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 ) ) - { - sql += " AND psi.programstageid = " + programStageId; - } - - return sql; - } - - /** - * Return standard SQL of a patient-attribute expression. E.g [CA:1] - * - */ - private String getConditionForPatientAttribute( int attributeId, String operator ) - { - String sql = "SELECT distinct(pi.patientid) "; - String from = "FROM patientattributevalue pi "; - - if ( !operator.equals( AGGRERATION_COUNT ) ) - { - 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 + "WHERE patientattributeid=" + attributeId + " AND value "; + private String getConditionForDataElement( boolean isExist, int programId, String programStageId, + int dataElementId, Collection orgunitIds, String startDate, String endDate ) + { + String keyExist = (isExist == true) ? "EXISTS" : "NOT EXISTS"; + + String sql = " " + keyExist + " ( SELECT * " + + "FROM patientdatavalue _pdv inner join programstageinstance _psi " + + "ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN programinstance _pi " + + "ON _pi.programinstanceid=_psi.programinstanceid " + + "WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=" + dataElementId + + " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") " + + "AND _pi.programid = " + programId + " AND psi.executionDate>='" + startDate + + "' AND psi.executionDate <= '" + endDate + "' "; + + if ( !programStageId.equals( IN_CONDITION_GET_ALL ) ) + { + sql += " AND _psi.programstageid = " + programStageId; + } + + if ( isExist ) + { + sql += " AND _pdv.value "; + } + + return sql; + } + + /** + * Return standard SQL of a dynamic patient-attribute expression. E.g [CA:1] + * + */ + private String getConditionForPatientAttribute( int attributeId, Collection orgunitIds ) + { + String sql = " EXISTS ( SELECT * " + "FROM patientattributevalue _pav " + + "WHERE _pav.patientid = pi.patientid " + "and _pav.patientattributeid=" + attributeId + + " AND p.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND _pav.value "; + + return sql; } /** @@ -739,24 +655,14 @@ * of person registration * */ - private String getConditionForPatient( int orgunitId, String operator, String startDate, String endDate ) + private String getConditionForPatient( Collection orgunitIds, String operator, String startDate, + String 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_COUNT ) ) - { - 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; + String sql = " EXISTS ( SELECT * " + "FROM patient _p " + "WHERE _p.patientid = pi.patientid " + + "AND _p.registrationdate>='" + startDate + "' AND _p.registrationdate<='" + endDate + "' " + + "AND p.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") "; + + return sql; } /** @@ -767,14 +673,7 @@ private String getConditionForPatientProperty( String propertyName, String operator, String startDate, String endDate ) { - String sql = "SELECT distinct(pi.patientid) FROM patient pi WHERE "; - - if ( !operator.equals( AGGRERATION_COUNT ) ) - { - 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 "; - } + String sql = " EXISTS (SELECT * FROM patient _p WHERE _p.patientid = pi.patientid AND "; if ( propertyName.equals( PROPERTY_AGE ) ) { @@ -796,19 +695,9 @@ private String getConditionForPatientProgramStageProperty( String propertyName, String operator, String startDate, 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_COUNT ) ) - { - sql = "SELECT psi.programstageinstance "; - from = "FROM programstageinstance psi "; - } - - from += "inner join patient p on p.patientid=pi.patientid "; - - sql += from + "WHERE executionDate>='" + startDate + "' and executionDate<='" + endDate + "' and " - + propertyName; + String sql = " EXISTS ( SELECT * from programstageinstance _psi " + + "WHERE _psi.programstageinstanceid=psi.programstageinstanceid AND _psi.executionDate>='" + startDate + + "' and _psi.executionDate<='" + endDate + "' and " + propertyName; return sql; } @@ -821,16 +710,17 @@ */ private String getConditionForProgramProperty( String operator, String startDate, String endDate, String property ) { - String sql = "SELECT pi.patientid FROM programinstance as pi "; - - if ( !operator.equals( AGGRERATION_COUNT ) ) - { - 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; + String sql = " EXISTS ( SELECT * FROM programinstance as _pi WHERE psi.programinstanceid=_pi.programsinstanceid AND " + + "_pi.enrollmentdate>='" + + startDate + + "' " + + "AND _pi.enrollmentdate<='" + + endDate + + "' AND " + + property + + " "; + + return sql; } /** @@ -838,20 +728,16 @@ * program. E.g [PG:1] * */ - private String getConditionForProgram( String programId, String operator, int orgunitId, String startDate, - String endDate ) + private String getConditionForProgram( String programId, String operator, Collection orgunitIds, + String startDate, String endDate ) { - String sql = "SELECT distinct(pi.patientid) FROM programinstance as pi " - + "inner join patient psi on psi.patientid=pi.patientid "; - - if ( !operator.equals( AGGRERATION_COUNT ) ) - { - sql = "SELECT psi.programstageinstanceid FROM programinstance as pi " - + "INNER JOIN programstageinstance psi ON pi.programinstanceid=psi.programinstanceid "; - } - - return sql + "WHERE pi.programid=" + programId + " " + " AND psi.organisationunitid = " + orgunitId - + " AND pi.enrollmentdate >= '" + startDate + "' AND pi.enrollmentdate <= '" + endDate + "' "; + String sql = " EXISTS ( SELECT * FROM programinstance as _pi " + + "inner join programstageinstance _psi on _pi.programinstanceid=_psi.programinstanceid " + + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _pi.programid=" + programId + " " + + " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + + ") AND _pi.enrollmentdate >= '" + startDate + "' AND _pi.enrollmentdate <= '" + endDate + "' "; + + return sql; } /** @@ -859,20 +745,16 @@ * [PS:1] * */ - private String getConditionForProgramStage( String programStageId, String operator, int orgunitId, + private String getConditionForProgramStage( String programStageId, String operator, Collection orgunitIds, String startDate, String endDate ) { - String select = "SELECT distinct(pi.patientid) "; - - if ( !operator.equals( AGGRERATION_COUNT ) ) - { - select = "SELECT psi.programstageinstanceid "; - } - - return select + "FROM programinstance as pi INNER JOIN programstageinstance psi " - + "ON pi.programinstanceid = psi.programinstanceid WHERE psi.programstageid=" + programStageId + " " - + "AND psi.executiondate >= '" + startDate + "' AND psi.executiondate <= '" + endDate - + "' AND psi.organisationunitid = " + orgunitId + " "; + String sql = " EXISTS ( SELECT * FROM programinstance as _pi INNER JOIN programstageinstance _psi " + + "ON _pi.programinstanceid = _psi.programinstanceid WHERE _psi.programstageinstanceid=psi.programstageinstanceid " + + "AND _psi.programstageid=" + programStageId + " AND _psi.executiondate >= '" + startDate + + "' AND _psi.executiondate <= '" + endDate + "' AND _psi.organisationunitid in (" + + TextUtils.getCommaDelimitedString( orgunitIds ) + ") "; + + return sql; } /** @@ -881,30 +763,18 @@ * trimester. * */ - private String getConditionForCountProgramStage( String programStageId, String operator, int orgunitId, - String startDate, String endDate ) + private String getConditionForCountProgramStage( String programStageId, String operator, + Collection orgunitIds, String startDate, String endDate ) { - String select = "SELECT distinct(pi.patientid) "; - - if ( !operator.equals( AGGRERATION_COUNT ) ) - { - select = "SELECT psi.programstageinstanceid "; - } - - select += "FROM programstageinstance as psi " - + "INNER JOIN programinstance as pi ON pi.programinstanceid = psi.programinstanceid " - + "WHERE psi.organisationunitid = " + orgunitId + " and psi.programstageid = " + programStageId + " " - + "AND psi.executionDate >= '" + startDate + "' AND psi.executionDate <= '" + endDate + "' " - + "GROUP BY psi.programinstanceid "; - - if ( operator.equals( AGGRERATION_COUNT ) ) - { - select += ",pi.patientid "; - } - - select += "HAVING count(psi.programstageinstanceid) "; - - return select; + String sql = " EXISTS ( SELECT * " + "FROM programstageinstance as _psi " + + "INNER JOIN programinstance as _pi ON psi.programinstanceid=psi.programinstanceid " + + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.organisationunitid in (" + + TextUtils.getCommaDelimitedString( orgunitIds ) + ") and _psi.programstageid = " + programStageId + " " + + "AND _psi.executionDate >= '" + startDate + "' AND _psi.executionDate <= '" + endDate + "' " + + "GROUP BY _psi.programinstanceid,_psi.programstageinstanceid " + + "HAVING count(_psi.programstageinstanceid) "; + + return sql; } @@ -913,20 +783,15 @@ * and due-date. E.g [PSP:DATE@executionDate#-DATE@dueDate#] * */ - private String getConditionForProgramStageProperty( String property, String operator, int orgunitId, - String startDate, String endDate ) + private String getConditionForProgramStageProperty( String property, String operator, + Collection orgunitIds, String startDate, String endDate ) { - String select = "SELECT distinct(pi.patientid) "; - - if ( !operator.equals( AGGRERATION_COUNT ) ) - { - select = "SELECT psi.programstageinstanceid "; - } - - return select + "FROM programinstance as pi INNER JOIN programstageinstance psi " - + "ON pi.programinstanceid = psi.programinstanceid WHERE " + " psi.executiondate >= '" + startDate - + "' AND psi.executiondate <= '" + endDate + "' AND psi.organisationunitid = " + orgunitId + " AND " - + property; + String sql = " EXISTS ( SELECT * FROM programstageinstance _psi " + + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.executiondate >= '" + startDate + + "' AND _psi.executiondate <= '" + endDate + "' AND _psi.organisationunitid in (" + + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND " + property + " "; + + return sql; } /** @@ -936,64 +801,25 @@ * @flag True if there are many stages in the expression * */ - private String getConditionForOrgunitProgramStageCompleted( String programStageId, String operator, int orgunitId, - String startDate, String endDate, boolean flag ) + private String getConditionForOrgunitProgramStageCompleted( String programStageId, String operator, + Collection orgunitIds, String startDate, String endDate, boolean flag ) { String sql = ""; if ( !flag ) { - sql = "SELECT '1' FROM organisationunit ou WHERE ou.organisationunitid=" + orgunitId + " "; + sql = " '1' FROM organisationunit ou WHERE ou.organisationunitid in (" + + TextUtils.getCommaDelimitedString( orgunitIds ) + ") "; } - - sql += " AND EXISTS ( SELECT programstageinstanceid FROM programstageinstance psi " - + " WHERE psi.organisationunitid=ou.organisationunitid AND psi.programstageid = " + programStageId - + " AND psi.completed=true AND psi.executiondate >= '" + startDate + "' AND psi.executiondate <= '" + + sql += " AND EXISTS ( SELECT programstageinstanceid FROM programstageinstance _psi " + + " WHERE _psi.organisationunitid=ou.organisationunitid AND _psi.programstageid = " + programStageId + + " AND _psi.completed=true AND _psi.executiondate >= '" + startDate + "' AND _psi.executiondate <= '" + endDate + "' ) "; return sql; } /** - * Return standard SQL by combining all sub-expressions of an aggregate - * query builder formula. - * - */ - private String getSQL( String aggregateOperator, List conditions, List operators ) - { - String sql = conditions.get( 0 ); - - String sqlAnd = ""; - - int index = 0; - - for ( index = 0; index < operators.size(); index++ ) - { - if ( operators.get( index ).equalsIgnoreCase( OPERATOR_AND ) ) - { - if ( aggregateOperator.equals( AGGRERATION_COUNT ) ) - { - sql += " AND pi.patientid IN ( " + conditions.get( index + 1 ); - } - else - { - sql += " AND psi.programstageinstanceid IN ( " + conditions.get( index + 1 ); - } - sqlAnd += ")"; - } - else - { - sql += sqlAnd; - sql += " UNION ( " + conditions.get( index + 1 ) + " ) "; - sqlAnd = ""; - } - } - - sql += sqlAnd; - - return sql; - } - - /** * Return the Ids of organisation units which patients registered or events * happened. * @@ -1039,4 +865,5 @@ return false; } + } === modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml' --- dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml 2013-04-17 07:58:14 +0000 +++ dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml 2013-05-02 03:31:45 +0000 @@ -15,8 +15,6 @@ - - mapStatusValues = new HashMap(); - - public Map getMapStatusValues() - { - return mapStatusValues; - } - - private Map> mapDataValues = new HashMap>(); - - public Map> getMapDataValues() - { - return mapDataValues; - } - - private Map mapCaseAggCondition = new HashMap(); - - public Map getMapCaseAggCondition() - { - return mapCaseAggCondition; - } - - private Collection orgunits = new HashSet(); - - public Collection getOrgunits() - { - return orgunits; - } - - private List periods = new ArrayList(); - - public List getPeriods() - { - return periods; + private List grids = new ArrayList(); + + public List getGrids() + { + return grids; } // ------------------------------------------------------------------------- @@ -221,7 +194,7 @@ CalendarPeriodType periodType = (CalendarPeriodType) PeriodType.getPeriodTypeByName( selectedDataSet .getPeriodType().getName() ); - + List periods = new ArrayList(); periods.addAll( periodType.generatePeriods( sDate, eDate ) ); // --------------------------------------------------------------------- @@ -256,74 +229,29 @@ // --------------------------------------------------------------------- // Aggregation // --------------------------------------------------------------------- - for ( Integer orgUnitId : orgunitIds ) + // for ( Integer orgUnitId : orgunitIds ) + // { + // OrganisationUnit orgUnit = + // organisationUnitService.getOrganisationUnit( orgUnitId ); + + for ( CaseAggregationCondition condition : aggregationConditions ) { - OrganisationUnit orgUnit = organisationUnitService.getOrganisationUnit( orgUnitId ); + DataElement dElement = condition.getAggregationDataElement(); + DataElementCategoryOptionCombo optionCombo = condition.getOptionCombo(); - for ( CaseAggregationCondition condition : aggregationConditions ) + for ( Period period : periods ) { - DataElement dElement = condition.getAggregationDataElement(); - DataElementCategoryOptionCombo optionCombo = condition.getOptionCombo(); - - for ( Period period : periods ) - { - Double resultValue = aggregationConditionService.getAggregateValue( condition, orgUnit, period ); - DataValue dataValue = dataValueService.getDataValue( orgUnit, dElement, period, optionCombo ); - - String key = orgUnitId + "-" + format.formatPeriod( period ); - String keyStatus = key + "-" + dElement.getId(); - - if ( resultValue != null && resultValue != 0 ) - { - if ( dataValue == null ) - { - dataValue = new DataValue( dElement, period, orgUnit, "" + resultValue, "", new Date(), - null, optionCombo ); - mapStatusValues.put( keyStatus, i18n.getString( ADD_STATUS ) ); - if ( autoSave ) - { - dataValueService.addDataValue( dataValue ); - } - } - else - { - dataValue.setValue( "" + resultValue ); - dataValue.setTimestamp( new Date() ); - mapStatusValues.put( keyStatus, i18n.getString( UPDATE_STATUS ) ); - if ( autoSave ) - { - dataValueService.updateDataValue( dataValue ); - } - } - mapCaseAggCondition.put( dataValue, condition ); - } - else if ( dataValue != null ) - { - mapStatusValues.put( keyStatus, i18n.getString( DELETE_STATUS ) ); - if ( autoSave ) - { - dataValueService.deleteDataValue( dataValue ); - } - } - - if ( dataValue != null ) - { - Set dataValues = null; - if ( mapDataValues.containsKey( key ) ) - { - dataValues = mapDataValues.get( key ); - } - else - { - dataValues = new HashSet(); - } - - dataValues.add( dataValue ); - mapDataValues.put( key, dataValues ); - orgunits.add( orgUnit ); - } + if ( autoSave ) + { + aggregationConditionService.insertAggregateValue( condition, orgunitIds, period ); + } + else + { + grids.add( aggregationConditionService.getAggregateValue( condition, orgunitIds, period, format, + i18n ) ); } } + // } } return SUCCESS; === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultDetailsAction.java' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultDetailsAction.java 2013-02-07 14:28:18 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/CaseAggregationResultDetailsAction.java 2013-05-02 03:31:45 +0000 @@ -152,7 +152,7 @@ public String execute() throws Exception { - OrganisationUnit orgunit = organisationUnitService.getOrganisationUnit( orgunitId ); +// OrganisationUnit orgunit = organisationUnitService.getOrganisationUnit( orgunitId ); PeriodType periodType = periodService.getPeriodTypeByName( periodTypeName ); Period period = periodType.createPeriod( format.parseDate( startDate ) ); @@ -164,7 +164,8 @@ { mapPatients = new HashMap>(); - Collection patients = aggregationConditionService.getPatients( aggCondition, orgunit, period ); + Collection patients = null; +// Collection patients = aggregationConditionService.getPatients( aggCondition, orgunit, period ); for ( Patient patient : patients ) { @@ -186,8 +187,9 @@ { mapEvents = new HashMap>(); - Collection programStageInstances = aggregationConditionService - .getProgramStageInstances( aggCondition, orgunit, period ); + Collection programStageInstances = null; +// Collection programStageInstances = aggregationConditionService +// .getProgramStageInstances( aggCondition, orgunit, period ); for ( ProgramStageInstance programStageInstance : programStageInstances ) { === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/SaveAggregateDataValueAction.java' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/SaveAggregateDataValueAction.java 2012-02-27 05:35:23 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/caseaggregation/SaveAggregateDataValueAction.java 2013-05-02 03:31:45 +0000 @@ -41,7 +41,6 @@ import org.hisp.dhis.organisationunit.OrganisationUnit; import org.hisp.dhis.organisationunit.OrganisationUnitService; import org.hisp.dhis.period.Period; -import org.hisp.dhis.period.PeriodService; import org.hisp.dhis.period.PeriodType; import org.hisp.dhis.user.CurrentUserService; @@ -82,13 +81,6 @@ this.categoryService = categoryService; } - private PeriodService periodService; - - public void setPeriodService( PeriodService periodService ) - { - this.periodService = periodService; - } - private DataValueService dataValueService; public void setDataValueService( DataValueService dataValueService ) @@ -102,14 +94,7 @@ { this.currentUserService = currentUserService; } - - private I18nFormat format; - - public void setFormat( I18nFormat format ) - { - this.format = format; - } - + // ------------------------------------------------------------------------- // Input // ------------------------------------------------------------------------- @@ -135,17 +120,14 @@ // ----------------------------------------------------------------- // Get params // ----------------------------------------------------------------- + String[] info = aggregateValue.split( SEPERATE_SIGN ); int dataElementId = Integer.parseInt( info[0] ); int optionComboId = Integer.parseInt( info[1] ); - - String periodTypeName = info[2]; - String startDate = info[3]; - - int orgunitId = Integer.parseInt( info[4] ); - - String resultValue = info[5]; + String periodIsoId = info[2]; + int orgunitId = Integer.parseInt( info[3] ); + String resultValue = info[4]; // ----------------------------------------------------------------- // Create objects @@ -154,9 +136,8 @@ DataElement dataElement = dataElementService.getDataElement( dataElementId ); DataElementCategoryOptionCombo optionCombo = categoryService .getDataElementCategoryOptionCombo( optionComboId ); - - PeriodType periodType = periodService.getPeriodTypeByName( periodTypeName ); - Period period = periodType.createPeriod( format.parseDate( startDate ) ); + + Period period = PeriodType.getPeriodFromIsoString( periodIsoId ); OrganisationUnit orgunit = organisationUnitService.getOrganisationUnit( orgunitId ); === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/META-INF/dhis/beans.xml' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/META-INF/dhis/beans.xml 2013-04-28 14:35:49 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/META-INF/dhis/beans.xml 2013-05-02 03:31:45 +0000 @@ -326,7 +326,6 @@ - === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/org/hisp/dhis/caseentry/i18n_module.properties' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/org/hisp/dhis/caseentry/i18n_module.properties 2013-04-30 12:08:42 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/resources/org/hisp/dhis/caseentry/i18n_module.properties 2013-05-02 03:31:45 +0000 @@ -636,4 +636,7 @@ program_stage_completeness_form = Program stage completeness number_of_events = Number of events followup = Follow-up -filter_on_section = Filter on section \ No newline at end of file +filter_on_section = Filter on section +dataelementname = Data element +categoryoptioncomboname = Category option combo +organisationunitname = Organisation unit \ No newline at end of file === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResult.vm' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResult.vm 2013-04-10 04:39:05 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/caseAggregationResult.vm 2013-05-02 03:31:45 +0000 @@ -1,72 +1,64 @@

$i18n.getString( "case_aggregation_result" )

-#if($orgunits.size()>0) -
+
-
-

- - #set( $index = 0 ) - #foreach( $orgunit in $orgunits) -

$orgunit.name

- #foreach( $period in $periods) - #set( $index = $index + 1 ) - #set( $periodInfo = $orgunit.id + '-' + $index ) - #set( $periodButton = $periodInfo + "-button") - #set( $periodDiv = $periodInfo + "-div") - #set( $key = $orgunit.id + '-' + $format.formatPeriod($period) ) - - #if( $!mapDataValues.get( $key ) ) -

$format.formatPeriod( $period ) -

- - #set( $dataValues = $mapDataValues.get( $key ) ) - - - - - - - #set( $mark = false ) - #set($no = 1) - - - - - - - - - #foreach( $dataValue in $dataValues) - #set( $statusKey = $key + '-' + $dataValue.dataElement.id ) - - - - - - - - - #set( $mark = !$mark ) - #set ($no = $no + 1) +
+ +#set($divIdx=0) +#set($hashValues=false) +#foreach( $grid in $grids ) + #if($grid.getRows().size() > 0) + #set($hashValues=true) + #set($divIdx=$divIdx+1) +

+ $!encoder.htmlEncode( $grid.title ) - $!encoder.htmlEncode( $grid.subtitle ) +

+ + +
+
+ + + + + #foreach( $header in $grid.getVisibleHeaders() ) + + #end + + + + #foreach( $row in $grid.getRows() ) + + #foreach( $col in $row ) + #if($velocityCount==1) + #set($dataelementid=$col) + #elseif($velocityCount==2) + #set($categoryoptioncomboid=$col) + #elseif($velocityCount==3) + #set($periodid=$col) + #elseif($velocityCount==4) + #set($organisationunitid=$col) + #elseif($velocityCount==$row.size()) + #set($value=$col) #end - - + #set($item=$dataelementid + '_' + $categoryoptioncomboid + '_' + $periodid + '_' + $organisationunitid + '_' + $value) + #end + + + + + #foreach( $col in $row ) + #if($velocityCount>6) $col #end + #end + #end - #end + + #end - -

$i18n.getString('no_aggregate_date_values_are_added_or_update_in_other_facilities')

-
-#else +#end + +#if($hashValues==false)

$i18n.getString('no_aggregate_date_values_are_added_or_update_in_all_facilities')

#end === modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/caseagg.js' --- dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/caseagg.js 2013-03-11 13:09:11 +0000 +++ dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/javascript/caseagg.js 2013-05-02 03:31:45 +0000 @@ -100,7 +100,7 @@ function toggleResult( id ) { - $( "#" + id + "-div" ).slideToggle( "fast" ); + $( "#div-" + id ).slideToggle( "fast" ); } function saveAggregateDataValues( isSaveAll ) @@ -147,5 +147,5 @@ function toogleAllCheckBoxes( tableDiv, checked ) { - jQuery("#" + tableDiv + " input[name=aggregateValues]").attr( 'checked', checked ); + jQuery("#div-" + tableDiv + " input[name=aggregateValues]").attr( 'checked', checked ); } \ 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/caseAggregationForm.vm' --- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm 2013-04-29 12:08:00 +0000 +++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/caseAggregationForm.vm 2013-05-02 03:31:45 +0000 @@ -96,7 +96,7 @@     - +     @@ -107,7 +107,7 @@     - +