=== modified file 'local/in/dhis-in-api/src/main/java/org/hisp/dhis/coldchain/aggregation/CCEIAggregationService.java' --- local/in/dhis-in-api/src/main/java/org/hisp/dhis/coldchain/aggregation/CCEIAggregationService.java 2014-01-09 12:43:23 +0000 +++ local/in/dhis-in-api/src/main/java/org/hisp/dhis/coldchain/aggregation/CCEIAggregationService.java 2014-01-12 16:56:05 +0000 @@ -12,6 +12,10 @@ { String ID = CCEIAggregationService.class.getName(); + String getQueryForRefrigeratorCountByTemperatureAlarm( Integer equipmentTypeId, Integer dataElementId ); + + String getQueryForRefrigeratorTemperatureAlarmByFacilty( Integer equipmentTypeId, String dataElementIds ); + String getQueryForRefrigeratorWorkingStatus( Integer equipmentTypeId, Integer modelTypeAttributeId, String modelName, String workingStatus ); String getQueryForRefrigeratorUtilization( Integer equipmentTypeId, Integer modelTypeAttributeId, String modelName, String utilization ); @@ -22,5 +26,9 @@ Map calculateRefrigeratorUtilization( Period period, DataElement dataElement, Set orgUnits, String query ); + Map calculateRefrigeratorTemperatureAlarmByFacilty( Period period, DataElement dataElement, Set orgUnits, String query ); + + Map calculateRefrigeratorCountByTemperatureAlarm( Period period, DataElement dataElement, Set orgUnits, String query ); + String importData( Map aggregationResultMap, Period period ); } === modified file 'local/in/dhis-in-api/src/main/java/org/hisp/dhis/lookup/Lookup.java' --- local/in/dhis-in-api/src/main/java/org/hisp/dhis/lookup/Lookup.java 2014-01-09 12:43:23 +0000 +++ local/in/dhis-in-api/src/main/java/org/hisp/dhis/lookup/Lookup.java 2014-01-12 16:56:05 +0000 @@ -18,17 +18,24 @@ public static final String CCEI_AGG_TYPE_REF_WORKING_STATUS_BY_TYPE = "REF WORKING STATUS BY TYPE"; public static final String CCEI_AGG_TYPE_REF_TEMP_ALARMS = "REF TEMP ALARMS"; + public static final String CCEI_NO_OF_REF_WITH_HIGHTEMP_ALARM = "NO_OF_REF_WITH_HIGHTEMP_ALARM"; + public static final String CCEI_NO_OF_REF_WITH_LOWTEMP_ALARM = "NO_OF_REF_WITH_LOWTEMP_ALARM"; + public static final String CCEI_FACILITY_WITH_HIGHTEMP_ALARM = "FACILITY_WITH_HIGHTEMP_ALARM"; + public static final String CCEI_FACILITY_WITH_LOWTEMP_ALARM = "FACILITY_WITH_LOWTEMP_ALARM"; + public static final String CCEI_FACILITY_WITH_TEMP_ALARM = "FACILITY_WITH_TEMP_ALARM"; + public static final String CCEI_WORKING_STATUS_OPTIONSET = "WORKING_STATUS_OPTIONSET"; + public static final String CCEI_UTILIZATION_OPTIONSET = "UTILIZATION_OPTIONSET"; public static final String CCEI_EQUIPMENTTYPE_MODELTYPEATTRIBUTE = "EQUIPMENTTYPE_MODELTYPEATTRIBUTE"; public static final String CCEI_MODEL_MODELTYPEATTRIBUTE = "MODEL_MODELTYPEATTRIBUTE"; - public static final String CCEI_UTILIZATION_OPTIONSET = "UTILIZATION_OPTIONSET"; public static final String CCEI_REF_EQUIPMENTTYPE_ID = "REF_EQUIPMENTTYPE_ID"; public static final String ORGUNITID_BY_COMMA = "ORGUNITID_BY_COMMA"; public static final String CURRENT_PERIOD_ENDDATE = "CURRENT_PERIOD_ENDDATE"; + public static final String PERIODID = "PERIODID"; private String type; === modified file 'local/in/dhis-in-services/dhis-in-service-coldchain/src/main/java/org/hisp/dhis/coldchain/aggregation/DefaultCCEIAggregationService.java' --- local/in/dhis-in-services/dhis-in-service-coldchain/src/main/java/org/hisp/dhis/coldchain/aggregation/DefaultCCEIAggregationService.java 2014-01-09 12:43:23 +0000 +++ local/in/dhis-in-services/dhis-in-service-coldchain/src/main/java/org/hisp/dhis/coldchain/aggregation/DefaultCCEIAggregationService.java 2014-01-12 16:56:05 +0000 @@ -83,7 +83,99 @@ // ------------------------------------------------------------------------- // // ------------------------------------------------------------------------- - + + public String getQueryForRefrigeratorCountByTemperatureAlarm( Integer equipmentTypeId, Integer dataElementId ) + { + String query = "SELECT e.organisationunitid, COUNT(*) FROM equipmentdatavalue edv " + + " INNER JOIN equipment e ON edv.equipmentid = e.equipmentid " + + " WHERE " + + " e.organisationunitid IN ( "+ Lookup.ORGUNITID_BY_COMMA +") AND " + + " e.equipmenttypeid = "+ equipmentTypeId +" AND " + + " edv.periodid = " + Lookup.PERIODID + " AND " + + " edv.dataelementid = " + dataElementId + " AND " + + " CAST( edv.value AS NUMERIC) >= 1 " + + "GROUP BY e.organisationunitid"; + return query; + } + + public Map calculateRefrigeratorCountByTemperatureAlarm( Period period, DataElement dataElement, Set orgUnits, String query ) + { + Map aggregationResultMap = new HashMap(); + + try + { + Collection orgUnitIds = new ArrayList( getIdentifiers( OrganisationUnit.class, orgUnits ) ); + String orgUnitIdsByComma = getCommaDelimitedString( orgUnitIds ); + + query = query.replace( Lookup.ORGUNITID_BY_COMMA, orgUnitIdsByComma ); + + query = query.replace( Lookup.PERIODID, period.getId()+"" ); + + System.out.println( query ); + + SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); + while ( rs.next() ) + { + Integer orgUnitId = rs.getInt( 1 ); + Integer countValue = rs.getInt( 2 ); + aggregationResultMap.put( orgUnitId+":"+dataElement.getId(), countValue ); + } + } + catch( Exception e ) + { + System.out.println("Exception :"+ e.getMessage() ); + } + + return aggregationResultMap; + } + + public String getQueryForRefrigeratorTemperatureAlarmByFacilty( Integer equipmentTypeId, String dataElementIds ) + { + String query = "SELECT e.organisationunitid, SUM( CAST( edv.value as numeric) ) FROM equipmentdatavalue edv " + + " INNER JOIN equipment e ON edv.equipmentid = e.equipmentid " + + " WHERE " + + " e.organisationunitid IN ( "+ Lookup.ORGUNITID_BY_COMMA +") AND " + + " e.equipmenttypeid = "+ equipmentTypeId +" AND " + + " edv.periodid = " + Lookup.PERIODID + " AND " + + " edv.dataelementid in (" + dataElementIds +" ) " + + " GROUP BY e.organisationunitid"; + return query; + } + + public Map calculateRefrigeratorTemperatureAlarmByFacilty( Period period, DataElement dataElement, Set orgUnits, String query ) + { + Map aggregationResultMap = new HashMap(); + + try + { + Collection orgUnitIds = new ArrayList( getIdentifiers( OrganisationUnit.class, orgUnits ) ); + String orgUnitIdsByComma = getCommaDelimitedString( orgUnitIds ); + + query = query.replace( Lookup.ORGUNITID_BY_COMMA, orgUnitIdsByComma ); + + query = query.replace( Lookup.PERIODID, period.getId()+"" ); + + System.out.println( query ); + + SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); + while ( rs.next() ) + { + Integer orgUnitId = rs.getInt( 1 ); + Integer sumValue = rs.getInt( 2 ); + if( sumValue >= 1 ) + { + aggregationResultMap.put( orgUnitId+":"+dataElement.getId(), 1 ); + } + } + } + catch( Exception e ) + { + System.out.println("Exception :"+ e.getMessage() ); + } + + return aggregationResultMap; + } + public String getQueryForRefrigeratorWorkingStatus( Integer equipmentTypeId, Integer modelTypeAttributeId, String modelName, String workingStatus ) { String query = "SELECT equipment.organisationunitid,COUNT(*) FROM modelattributevalue " + === modified file 'local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/AddAggregationQueryAction.java' --- local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/AddAggregationQueryAction.java 2014-01-10 07:08:30 +0000 +++ local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/AddAggregationQueryAction.java 2014-01-12 16:56:05 +0000 @@ -198,7 +198,35 @@ aggregationConditionService.addCaseAggregationCondition( condition ); } - + else if( aggType.equals( Lookup.CCEI_AGG_TYPE_REF_TEMP_ALARMS ) ) + { + HttpServletRequest request = ServletActionContext.getRequest(); + + String refTempAlarm = request.getParameter( Lookup.CCEI_AGG_TYPE_REF_TEMP_ALARMS ); + + Lookup lookup = lookupService.getLookupByName( Lookup.CCEI_REF_EQUIPMENTTYPE_ID ); + + Integer equipmentTypeId = Integer.parseInt( lookup.getValue() ); + + lookup = lookupService.getLookupByName( refTempAlarm ); + + if( lookup.getName().equals( Lookup.CCEI_NO_OF_REF_WITH_HIGHTEMP_ALARM) || lookup.getName().equals( Lookup.CCEI_NO_OF_REF_WITH_LOWTEMP_ALARM) ) + { + String query = cceiAggregationService.getQueryForRefrigeratorCountByTemperatureAlarm( equipmentTypeId, Integer.parseInt(lookup.getValue() ) ); + + CaseAggregationCondition condition = new CaseAggregationCondition( dataElement.getName(), lookup.getName(), query, dataElement, dataElementCategoryService.getDefaultDataElementCategoryOptionCombo() ); + + aggregationConditionService.addCaseAggregationCondition( condition ); + } + else if( lookup.getName().equals( Lookup.CCEI_FACILITY_WITH_HIGHTEMP_ALARM) || lookup.getName().equals( Lookup.CCEI_FACILITY_WITH_LOWTEMP_ALARM ) || lookup.getName().equals( Lookup.CCEI_FACILITY_WITH_TEMP_ALARM ) ) + { + String query = cceiAggregationService.getQueryForRefrigeratorTemperatureAlarmByFacilty( equipmentTypeId, lookup.getValue() ); + + CaseAggregationCondition condition = new CaseAggregationCondition( dataElement.getName(), lookup.getName(), query, dataElement, dataElementCategoryService.getDefaultDataElementCategoryOptionCombo() ); + + aggregationConditionService.addCaseAggregationCondition( condition ); + } + } return SUCCESS; } === modified file 'local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/GetAggregationParameterAction.java' --- local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/GetAggregationParameterAction.java 2014-01-06 06:13:15 +0000 +++ local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/GetAggregationParameterAction.java 2014-01-12 16:56:05 +0000 @@ -5,12 +5,13 @@ import java.util.List; import java.util.Map; -import org.hisp.dhis.coldchain.equipment.EquipmentTypeAttribute; import org.hisp.dhis.coldchain.equipment.EquipmentTypeAttributeService; import org.hisp.dhis.coldchain.model.ModelAttributeValue; import org.hisp.dhis.coldchain.model.ModelAttributeValueService; import org.hisp.dhis.coldchain.model.ModelTypeAttribute; import org.hisp.dhis.coldchain.model.ModelTypeAttributeService; +import org.hisp.dhis.dataelement.DataElement; +import org.hisp.dhis.dataelement.DataElementService; import org.hisp.dhis.lookup.Lookup; import org.hisp.dhis.lookup.LookupService; import org.hisp.dhis.option.OptionService; @@ -59,7 +60,7 @@ this.equipmentTypeAttributeService = equipmentTypeAttributeService; } - // ------------------------------------------------------------------------- + // ------------------------------------------------------------------------- // Input/ Output // ------------------------------------------------------------------------- private String aggTypeId; @@ -166,7 +167,18 @@ } */ } + else if( aggTypeId.equals( Lookup.CCEI_AGG_TYPE_REF_TEMP_ALARMS ) ) + { + List lookUpParamOptions = new ArrayList(); + lookUpParamOptions.add( Lookup.CCEI_NO_OF_REF_WITH_HIGHTEMP_ALARM ); + lookUpParamOptions.add( Lookup.CCEI_NO_OF_REF_WITH_LOWTEMP_ALARM ); + lookUpParamOptions.add( Lookup.CCEI_FACILITY_WITH_HIGHTEMP_ALARM ); + lookUpParamOptions.add( Lookup.CCEI_FACILITY_WITH_LOWTEMP_ALARM ); + lookUpParamOptions.add( Lookup.CCEI_FACILITY_WITH_TEMP_ALARM ); + + lookUpParamMap.put( Lookup.CCEI_AGG_TYPE_REF_TEMP_ALARMS, lookUpParamOptions ); + } return SUCCESS; } === modified file 'local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/RunAggregationQueryAction.java' --- local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/RunAggregationQueryAction.java 2014-01-10 09:18:34 +0000 +++ local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/aggregation/action/RunAggregationQueryAction.java 2014-01-12 16:56:05 +0000 @@ -136,7 +136,15 @@ { aggregationResultMap.putAll( cceiAggregationService.calculateRefrigeratorUtilization( period, dataElement, orgUnitList, condition.getAggregationExpression() ) ); } - + else if( condition.getOperator().equals( Lookup.CCEI_NO_OF_REF_WITH_HIGHTEMP_ALARM) || condition.getOperator().equals( Lookup.CCEI_NO_OF_REF_WITH_LOWTEMP_ALARM) ) + { + aggregationResultMap.putAll( cceiAggregationService.calculateRefrigeratorCountByTemperatureAlarm(period, dataElement, orgUnitList, condition.getAggregationExpression() ) ); + } + else if( condition.getOperator().equals( Lookup.CCEI_FACILITY_WITH_HIGHTEMP_ALARM) || condition.getOperator().equals( Lookup.CCEI_FACILITY_WITH_LOWTEMP_ALARM ) || condition.getOperator().equals( Lookup.CCEI_FACILITY_WITH_TEMP_ALARM ) ) + { + aggregationResultMap.putAll( cceiAggregationService.calculateRefrigeratorTemperatureAlarmByFacilty(period, dataElement, orgUnitList, condition.getAggregationExpression() ) ); + } + dataElements.add( dataElement ); }