=== modified file 'local/in/dhis-in-api/src/main/java/org/hisp/dhis/coldchain/model/Model.java' --- local/in/dhis-in-api/src/main/java/org/hisp/dhis/coldchain/model/Model.java 2013-09-30 12:03:10 +0000 +++ local/in/dhis-in-api/src/main/java/org/hisp/dhis/coldchain/model/Model.java 2013-12-30 09:54:47 +0000 @@ -9,7 +9,7 @@ */ private static final long serialVersionUID = -6551567526188061690L; - public static final String DEFAULT_CCEMFOLDER = "CCEMIMAGES"; + public static final String DEFAULT_CCEMFOLDER = "images"; public static String PREFIX_MODEL_NAME = "modelname"; public static final String NAME_OWNERSHIP_GROUP_SET = "Ownership"; === modified file 'local/in/dhis-in-services/dhis-in-service-coldchain/src/main/java/org/hisp/dhis/coldchain/reports/DefaultCCEMReportManager.java' --- local/in/dhis-in-services/dhis-in-service-coldchain/src/main/java/org/hisp/dhis/coldchain/reports/DefaultCCEMReportManager.java 2013-12-30 05:54:06 +0000 +++ local/in/dhis-in-services/dhis-in-service-coldchain/src/main/java/org/hisp/dhis/coldchain/reports/DefaultCCEMReportManager.java 2013-12-30 09:54:47 +0000 @@ -227,7 +227,7 @@ } public Map getSumOfEquipmentDatabyInventoryType( String orgUnitIdsByComma, - Integer inventoryTypeId, Integer inventoryTypeAttributeId, Double factor ) + Integer equipmentTypeId, Integer equipmentTypeAttributeId, Double factor ) { Map equipmentSumByInventoryTypeMap = new HashMap(); @@ -243,8 +243,8 @@ + ") FROM equipmentattributevalue " + " INNER JOIN equipment on equipmentattributevalue.equipmentid = equipment.equipmentid " + " WHERE " + " equipment.working = 1 AND " + " equipment.equipmenttypeid = " - + inventoryTypeId + " AND " + " equipment.organisationunitid in (" + orgUnitIdsByComma - + ") AND " + " equipmentattributevalue.equipmenttypeattributeid = " + inventoryTypeAttributeId + " " + + equipmentTypeId + " AND " + " equipment.organisationunitid in (" + orgUnitIdsByComma + + ") AND " + " equipmentattributevalue.equipmenttypeattributeid = " + equipmentTypeAttributeId + " " + " GROUP BY organisationunitid"; } @@ -254,8 +254,8 @@ + " ) FROM equipmentattributevalue " + " INNER JOIN equipment on equipmentattributevalue.equipmentid = equipment.equipmentid " + " WHERE " + " equipment.working = TRUE AND " + " equipment.equipmenttypeid = " - + inventoryTypeId + " AND " + " equipment.organisationunitid in (" + orgUnitIdsByComma - + ") AND " + " equipmentattributevalue.equipmenttypeattributeid = " + inventoryTypeAttributeId + " " + + equipmentTypeId + " AND " + " equipment.organisationunitid in (" + orgUnitIdsByComma + + ") AND " + " equipmentattributevalue.equipmenttypeattributeid = " + equipmentTypeAttributeId + " " + " GROUP BY organisationunitid"; } @@ -353,17 +353,16 @@ return catalogSumByEquipmentDataMap; } - public Map getFacilityWiseEquipmentRoutineData( String orgUnitIdsByComma, String periodIdsByComma, - String dataElementIdsByComma, String optComboIdsByComma ) + public Map getFacilityWiseEquipmentRoutineData( String orgUnitIdsByComma, String periodIdsByComma, String dataElementIdsByComma, String optComboIdsByComma ) { Map equipmentDataValueMap = new HashMap(); try { - String query = "SELECT equipmentinstance.organisationunitid, dataelementid, periodid, value FROM equipmentdatavalue " - + "INNER JOIN equipmentinstance " - + " ON equipmentinstance.equipmentinstanceid = equipmentdatavalue.equipmentinstanceid " + String query = "SELECT equipment.organisationunitid, dataelementid, periodid, value FROM equipmentdatavalue " + + "INNER JOIN equipment " + + " ON equipment.equipmentid = equipmentdatavalue.equipmentid " + " WHERE " - + " equipmentinstance.organisationunitid IN (" + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ") AND " + " dataelementid IN (" @@ -992,18 +991,17 @@ return grandTotal; } - public Map getCatalogDatavalueId( String orgUnitIdsByComma, Integer inventoryTypeId, - Integer catalogTypeAttributeId ) + public Map getCatalogDatavalueId( String orgUnitIdsByComma, Integer equipmentTypeId, Integer modelTypeAttributeId ) { Map CatalogTypeAttributeValueMap = new HashMap(); try { - String query = "SELECT catalogdatavalue.catalogid, catalogdatavalue.value FROM catalogdatavalue " - + " INNER JOIN equipmentinstance on catalogdatavalue.catalogid = equipmentinstance.catalogid " - + " WHERE " + " equipmentinstance.inventorytypeid = " + inventoryTypeId + " AND " - + " catalogdatavalue.catalogtypeattributeid = " + catalogTypeAttributeId + " AND " - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ")" - + " GROUP BY catalogdatavalue.value"; + String query = "SELECT modelattributevalue.catalogid, modelattributevalue.value FROM modelattributevalue " + + " INNER JOIN equipment on modelattributevalue.modelid = equipment.modelid " + + " WHERE " + " equipment.equipmenttypeid = " + equipmentTypeId + " AND " + + " modelattributevalue.modeltypeattributeid = " + modelTypeAttributeId + " AND " + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ")" + + " GROUP BY modelattributevalue.value"; SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); while ( rs.next() ) @@ -1047,17 +1045,16 @@ return ModelNameList; } - public String getEquipmentValue( String catalogTypeAttributeValue, Integer catalogid, String euipmentValue, - String orgUnitIdsByComma, Integer inventoryTypeId ) + public String getEquipmentValue( String catalogTypeAttributeValue, Integer modelId, String euipmentValue, String orgUnitIdsByComma, Integer equipmentTypeId ) { String EquipmentValue = null; try { - String query = "SELECT COUNT(*) FROM equipment " - + "INNER JOIN equipmentinstance on equipment.equipmentinstanceid =equipmentinstance.equipmentinstanceid" - + " WHERE " + " equipmentinstance.catalogid = " + catalogid + " AND " + " equipment.value like '%" - + euipmentValue + "%' AND " + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma - + ") AND " + " equipmentinstance.inventorytypeid = " + inventoryTypeId + ""; + String query = "SELECT COUNT(*) FROM equipmentattributevalue " + + "INNER JOIN equipment on equipmentattributevalue.equipmentid =equipment.equipmentid" + + " WHERE " + " equipment.modelid = " + modelId + " AND " + " equipmentattributevalue.value like '%" + + euipmentValue + "%' AND " + " equipment.organisationunitid IN (" + orgUnitIdsByComma + + ") AND " + " equipment.equipmenttypeid = " + equipmentTypeId + ""; SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); while ( rs.next() ) @@ -1341,30 +1338,29 @@ } - public List getEquipmentValueAndData( Integer catalogTypeAttributeId, String orgUnitIdsByComma, - Integer inventoryTypeId ) + public List getEquipmentValueAndData( Integer modelTypeAttributeId, String orgUnitIdsByComma,Integer equipmentTypeId ) { List values = new ArrayList(); try { - String query = "SELECT equipmentinstance.organisationunitid ,catalogdatavalue.value , count(*) ,equipment.value ,catalogdatavalue.catalogid FROM catalogdatavalue " - + "INNER JOIN equipmentinstance ON catalogdatavalue.catalogid = equipmentinstance.catalogid " - + "INNER JOIN equipment ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid " + String query = "SELECT equipment.organisationunitid ,modelattributevalue.value , count(*) ,equipmentattributevalue.value ,modelattributevalue.modelid FROM modelattributevalue " + + "INNER JOIN equipment ON modelattributevalue.modelid = equipment.modelid " + + "INNER JOIN equipmentattributevalue ON equipmentattributevalue.equipmentid = equipment.equipmentid " + " WHERE " - + " equipmentinstance.inventorytypeid =" - + inventoryTypeId - + " AND " - + " catalogdatavalue.catalogtypeattributeid = " - + catalogTypeAttributeId - + " AND " - + " equipment.value = " + + " equipment.equipmenttypeid =" + + equipmentTypeId + + " AND " + + " modelattributevalue.modeltypeattributeid = " + + modelTypeAttributeId + + " AND " + + " equipmentattributevalue.value = " + "'Not working'" - + " OR equipment.value = " + + " OR equipmentattributevalue.value = " + "'Working but needs maintenance'" + " AND " - + " equipmentinstance.organisationunitid IN (" - + orgUnitIdsByComma + ") " + " GROUP BY equipmentinstance.organisationunitid"; + + " equipment.organisationunitid IN (" + + orgUnitIdsByComma + ") " + " GROUP BY equipment.organisationunitid"; SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); while ( rs.next() ) @@ -1382,17 +1378,17 @@ return values; } - public List equipmentCatalogies( String orgUnitIdsByComma, Integer inventoryTypeId ) + public List equipmentCatalogies( String orgUnitIdsByComma, Integer equipmentTypeId ) { List catalogIds = new ArrayList(); try { - String query = "SELECT equipmentinstance.catalogid FROM equipmentinstance" - + " INNER JOIN equipment ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid " - + " WHERE equipmentinstance.inventorytypeid = " + inventoryTypeId + " AND " - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ") " - + " AND equipment.value = 'Not working' OR equipment.value = 'Working but needs maintenance'" - + " AND equipmentinstance.catalogid IS NOT NULL" + " GROUP BY equipmentinstance.catalogid"; + String query = "SELECT equipment.modelid FROM equipment" + + " INNER JOIN equipmentattributevalue ON equipmentattributevalue.equipmentid = equipment.equipmentid " + + " WHERE equipment.equipmenttypeid = " + equipmentTypeId + " AND " + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ") " + + " AND equipmentattributevalue.value = 'Not working' OR equipment.value = 'Working but needs maintenance'" + + " AND equipment.modelid IS NOT NULL" + " GROUP BY equipment.modelid"; SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); while ( rs != null && rs.next() ) @@ -1408,18 +1404,17 @@ return catalogIds; } - public Map equipmentCatalogyValues( String orgUnitIdsByComma, Integer inventoryTypeId, - Integer inventoryTypeAttributeId ) + public Map equipmentCatalogyValues( String orgUnitIdsByComma, Integer equipmentTypeId, Integer equipmentTypeAttributeId ) { Map catalogValues = new HashMap(); try { - String query = "SELECT equipmentinstance.catalogid , equipment.value FROM equipment" - + " INNER JOIN equipmentinstance ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid" - + " WHERE equipmentinstance.inventorytypeid = " + inventoryTypeId - + " AND equipment.inventorytypeattributeid = " + inventoryTypeAttributeId - + " AND equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ") " - + " GROUP BY equipmentinstance.catalogid"; + String query = "SELECT equipment.modelid , equipmentattributevalue.value FROM equipmentattributevalue" + + " INNER JOIN equipment ON equipmentattributevalue.equipmentid = equipment.equipmentid" + + " WHERE equipment.equipmenttypeid = " + equipmentTypeId + + " AND equipmentattributevalue.equipmenttypeattributeid = " + equipmentTypeAttributeId + + " AND equipment.organisationunitid IN (" + orgUnitIdsByComma + ") " + + " GROUP BY equipment.modelid"; SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); while ( rs != null && rs.next() ) @@ -1435,21 +1430,21 @@ return catalogValues; } - public Map equipmentOrgUnit( String orgUnitIdsByComma, Integer inventoryTypeId ) + public Map equipmentOrgUnit( String orgUnitIdsByComma, Integer equipmentTypeId ) { Map catalogs = new HashMap(); try { - String query = "SELECT equipmentinstance.catalogid, equipmentinstance.organisationunitid FROM equipmentinstance" - + " INNER JOIN equipment ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid " - + " WHERE equipmentinstance.inventorytypeid = " - + inventoryTypeId + String query = "SELECT equipment.modelid, equipment.organisationunitid FROM equipment" + + " INNER JOIN equipmentattributevalue ON equipmentattributevalue.equipmentid = equipment.equipmentid " + + " WHERE equipment.equipmenttypeid = " + + equipmentTypeId + " AND " - + " equipmentinstance.organisationunitid IN (" + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ") " - + " AND equipment.value = 'Not working' OR equipment.value = 'Working but needs maintenance'" - + " AND equipmentinstance.catalogid IS NOT NULL" + " GROUP BY equipmentinstance.catalogid"; + + " AND equipmentattributevalue.value = 'Not working' OR equipmentattributevalue.value = 'Working but needs maintenance'" + + " AND equipment.modelid IS NOT NULL" + " GROUP BY equipment.modelid"; SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); while ( rs.next() ) @@ -1465,18 +1460,17 @@ return catalogs; } - public Map getEquipmentNameWithOrgUnit( Integer inventoryTypeId, Integer catalogTypeAttributeId, - String orgUnitIds ) + public Map getEquipmentNameWithOrgUnit( Integer equipmentTypeId, Integer modelTypeAttributeId, String orgUnitIds ) { Map equipmentMap = new HashMap(); try { - String query = "SELECT catalogdatavalue.value ,equipmentinstance.organisationunitid FROM catalogdatavalue " - + " INNER JOIN equipmentinstance on catalogdatavalue.catalogid = equipmentinstance.catalogid " - + " INNER JOIN equipment on equipmentinstance.equipmentinstanceid = equipment.equipmentinstanceid " - + " WHERE " + " equipmentinstance.inventorytypeid = " + inventoryTypeId + " AND " - + " catalogdatavalue.catalogtypeattributeid = " + catalogTypeAttributeId + " AND " - + " equipmentinstance.organisationunitid IN (" + orgUnitIds + ") ORDER BY catalogdatavalue.value"; + String query = "SELECT modelattributevalue.value ,equipment.organisationunitid FROM modelattributevalue " + + " INNER JOIN equipment on modelattributevalue.modelid = equipment.modelid " + + " INNER JOIN equipmentattributevalue on equipment.equipmentid = equipmentattributevalue.equipmentid " + + " WHERE " + " equipment.equipmenttypeid = " + equipmentTypeId + " AND " + + " modelattributevalue.modeltypeattributeid = " + modelTypeAttributeId + " AND " + + " equipment.organisationunitid IN (" + orgUnitIds + ") ORDER BY modelattributevalue.value"; SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); String orgUnitIdByComma = null; @@ -1525,22 +1519,21 @@ return totalValue; } - public Map getTotalColdRoomValue( Integer inventorytypeid, String orgUnitIdByComma, - String inventoryTypeAttributeId, String equipmentValue ) + public Map getTotalColdRoomValue( Integer equipmentTypeId, String orgUnitIdByComma, String equipmentTypeAttributeId, String equipmentValue ) { Map countMap = new HashMap(); try { - String query = "SELECT equipment.inventorytypeattributeid ,equipment.value FROM equipmentinstance" - + " INNER JOIN equipment ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid" - + " WHERE " + " equipmentinstance.inventorytypeid = " + inventorytypeid - + " AND equipmentinstance.organisationunitid IN (" + orgUnitIdByComma - + ") AND equipment.inventorytypeattributeid IN (" + inventoryTypeAttributeId + ")"; + String query = "SELECT equipmentattributevalue.equipmenttypeattributeid ,equipmentattributevalue.value FROM equipment" + + " INNER JOIN equipmentattributevalue ON equipmentattributevalue.equipmentid = equipment.equipmentid" + + " WHERE " + " equipment.equipmenttypeid = " + equipmentTypeId + + " AND equipment.organisationunitid IN (" + orgUnitIdByComma + + ") AND equipmentattributevalue.equipmenttypeattributeid IN (" + equipmentTypeAttributeId + ")"; if ( equipmentValue != null ) { - query = query + " AND equipment.value like '%" + equipmentValue + "%'"; + query = query + " AND equipmentattributevalue.value like '%" + equipmentValue + "%'"; } SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); @@ -1560,8 +1553,7 @@ return countMap; } - public Map getModelNameAndCountForColdBox( Integer catalogTypeAttributeId, - Integer inventoryTypeId, String workingStatus, String orgUnitIdsByComma ) + public Map getModelNameAndCountForColdBox( Integer modelTypeAttributeId, Integer equipmentTypeId, String workingStatus, String orgUnitIdsByComma ) { Map EquipmentValue = new HashMap(); @@ -1582,25 +1574,25 @@ workingStatus = "FALSE"; } - query = "SELECT catalogdatavalue.value, COUNT(*) FROM catalogdatavalue " - + " INNER JOIN equipmentinstance ON catalogdatavalue.catalogid = equipmentinstance.catalogid " - + " WHERE " + " equipmentinstance.inventorytypeid =" + inventoryTypeId + " AND " - + " equipmentinstance.working = " + workingStatus + " AND " - + " catalogdatavalue.catalogtypeattributeid = " + catalogTypeAttributeId + " AND " - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ")" - + " GROUP BY catalogdatavalue.value "; + query = "SELECT modelattributevalue.value, COUNT(*) FROM modelattributevalue " + + " INNER JOIN equipment ON modelattributevalue.modelid = equipment.modelid " + + " WHERE " + " equipment.equipmenttypeid =" + equipmentTypeId + " AND " + + " equipment.working = " + workingStatus + " AND " + + " modelattributevalue.modeltypeattributeid = " + modelTypeAttributeId + " AND " + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ")" + + " GROUP BY modelattributevalue.value "; //System.out.println( " Query " + query ); } else if ( dataBaseInfo.getType().equalsIgnoreCase( "mysql" ) ) { - query = "SELECT catalogdatavalue.value, COUNT(*) FROM catalogdatavalue " - + " INNER JOIN equipmentinstance ON catalogdatavalue.catalogid = equipmentinstance.catalogid " - + " WHERE " + " equipmentinstance.inventorytypeid =" + inventoryTypeId + " AND " - + " equipmentinstance.working = " + workingStatus + " AND " - + " catalogdatavalue.catalogtypeattributeid = " + catalogTypeAttributeId + " AND " - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ")" - + " GROUP BY catalogdatavalue.value "; + query = "SELECT modelattributevalue.value, COUNT(*) FROM modelattributevalue " + + " INNER JOIN equipment ON modelattributevalue.modelid = equipment.modelid " + + " WHERE " + " equipment.equipmenttypeid =" + equipmentTypeId + " AND " + + " equipment.working = " + workingStatus + " AND " + + " modelattributevalue.modeltypeattributeid = " + modelTypeAttributeId + " AND " + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ")" + + " GROUP BY modelattributevalue.value "; } System.out.println( " Query " + query ); @@ -1630,23 +1622,22 @@ return EquipmentValue; } - public String getModelNameAndCountForQuantityOfColdbox( Integer inventoryTypeId, String catalogValue, - String orgUnitIdsByComma ) + public String getModelNameAndCountForQuantityOfColdbox( Integer equipmentTypeId, String modelValue, String orgUnitIdsByComma ) { String EquipmentValue = null; try { - String query = "SELECT MIN(CAST(equipment.value AS SIGNED)),MAx(CAST(equipment.value AS SIGNED)),AVG(CAST(equipment.value AS SIGNED)) FROM catalogdatavalue " - + "INNER JOIN equipmentinstance ON catalogdatavalue.catalogid = equipmentinstance.catalogid " - + " INNER JOIN equipment ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid" + String query = "SELECT MIN(CAST(equipmentattributevalue.value AS SIGNED)),MAx(CAST(equipmentattributevalue.value AS SIGNED)),AVG(CAST(equipmentattributevalue.value AS SIGNED)) FROM catalogdatavalue " + + "INNER JOIN equipment ON modelattributevalue.modelid = equipment.modelid " + + " INNER JOIN equipmentattributevalue ON equipmentattributevalue.equipmentid = equipment.equipmentid" + " WHERE " - + " equipmentinstance.inventorytypeid =" - + inventoryTypeId + + " equipment.equipmenttypeid =" + + equipmentTypeId + " AND " - + " catalogdatavalue.value like '%" - + catalogValue + + " modelattributevalue.value like '%" + + modelValue + "%' AND" - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ")" + " "; + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ")" + " "; SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); while ( rs.next() ) @@ -1661,8 +1652,8 @@ return EquipmentValue; } - public Map getSumOfEquipmentAndCatalogValue( Integer inventoryTypeId, - Integer inventoryTypeAttributeId, Integer catalogTypeAttributeId, String orgUnitIdsByComma ) + + public Map getSumOfEquipmentAndCatalogValue( Integer equipmentTypeId, Integer equipmentTypeAttributeId, Integer modelTypeAttributeId, String orgUnitIdsByComma ) { Map catalogSumAndValue = new HashMap(); @@ -1673,28 +1664,28 @@ String query = ""; if ( dataBaseInfo.getType().equalsIgnoreCase( "postgresql" ) ) { - query = "SELECT catalogdatavalue.value, SUM( cast( equipment.value as numeric ) ) FROM equipment" - + " INNER JOIN equipmentinstance ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid " - + " INNER JOIN catalog ON catalog.catalogid = equipmentinstance.catalogid " - + " INNER JOIN catalogdatavalue ON catalogdatavalue.catalogid = equipmentinstance.catalogid" - + " WHERE " + " equipmentinstance.inventorytypeid =" + inventoryTypeId + " AND " - + " equipment.inventorytypeattributeid = " + inventoryTypeAttributeId + " AND" - + " catalogdatavalue.catalogtypeattributeid = " + catalogTypeAttributeId + " AND" - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ")" - + " GROUP BY catalogdatavalue.value"; + query = "SELECT modelattributevalue.value, SUM( cast( equipmentattributevalue.value as numeric ) ) FROM equipmentattributevalue" + + " INNER JOIN equipment ON equipmentattributevalue.equipmentid = equipment.equipmentid " + + " INNER JOIN model ON model.modelid = equipment.modelid " + + " INNER JOIN modelattributevalue ON modelattributevalue.modelid = equipment.modelid" + + " WHERE " + " equipment.equipmenttypeid =" + equipmentTypeId + " AND " + + " equipmentattributevalue.equipmenttypeattributeid = " + equipmentTypeAttributeId + " AND" + + " modelattributevalue.modeltypeattributeid = " + modelTypeAttributeId + " AND" + + " equipmentattributevalue.organisationunitid IN (" + orgUnitIdsByComma + ")" + + " GROUP BY modelattributevalue.value"; } else if ( dataBaseInfo.getType().equalsIgnoreCase( "mysql" ) ) { - query = "SELECT catalogdatavalue.value, SUM(equipment.value) FROM equipment" - + " INNER JOIN equipmentinstance ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid " - + " INNER JOIN catalog ON catalog.catalogid = equipmentinstance.catalogid " - + " INNER JOIN catalogdatavalue ON catalogdatavalue.catalogid = equipmentinstance.catalogid" - + " WHERE " + " equipmentinstance.inventorytypeid =" + inventoryTypeId + " AND " - + " equipment.inventorytypeattributeid = " + inventoryTypeAttributeId + " AND" - + " catalogdatavalue.catalogtypeattributeid = " + catalogTypeAttributeId + " AND" - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ")" - + " GROUP BY catalogdatavalue.value"; + query = "SELECT modelattributevalue.value, SUM(equipmentattributevalue.value) FROM equipmentattributevalue" + + " INNER JOIN equipment ON equipmentattributevalue.equipmentid = equipment.equipmentid " + + " INNER JOIN model ON model.modelid = equipmentinstance.modelid " + + " INNER JOIN modelattributevalue ON modelattributevalue.modelid = equipment.modelid" + + " WHERE " + " equipment.equipmenttypeid =" + equipmentTypeId + " AND " + + " equipmentattributevalue.equipmenttypeattributeid = " + equipmentTypeAttributeId + " AND" + + " modelattributevalue.modeltypeattributeid = " + modelTypeAttributeId + " AND" + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ")" + + " GROUP BY modelattributevalue.value"; } /* @@ -1725,7 +1716,7 @@ } - public Map getSumOfColdBoxByOrgUnitGroup( Integer coldBoxInventoryTypeId, Integer qtyPresent_qtyWorking_inventoryTypeAttributeId, Integer modelNameCatalogTypeAttributeId, String orgUnitIdsByComma, Integer orgUnitGroupId ) + public Map getSumOfColdBoxByOrgUnitGroup( Integer coldBoxEquipmentTypeId, Integer qtyPresent_qtyWorking_equipmentTypeAttributeId, Integer modelNameModelTypeAttributeId, String orgUnitIdsByComma, Integer orgUnitGroupId ) { Map catalogSumAndValue = new HashMap(); @@ -1736,28 +1727,28 @@ String query = ""; if ( dataBaseInfo.getType().equalsIgnoreCase( "postgresql" ) ) { - query = "SELECT catalogdatavalue.value, SUM( cast( equipment.value as numeric ) ) FROM equipment" - + " INNER JOIN equipmentinstance ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid " - + " INNER JOIN catalog ON catalog.catalogid = equipmentinstance.catalogid " - + " INNER JOIN catalogdatavalue ON catalogdatavalue.catalogid = equipmentinstance.catalogid" - + " WHERE " + " equipmentinstance.inventorytypeid =" + coldBoxInventoryTypeId + " AND " - + " equipment.inventorytypeattributeid = " + qtyPresent_qtyWorking_inventoryTypeAttributeId + " AND" - + " catalogdatavalue.catalogtypeattributeid = " + modelNameCatalogTypeAttributeId + " AND" - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ")" - + " GROUP BY catalogdatavalue.value"; + query = "SELECT modelattributevalue.value, SUM( cast( equipmentattributevalue.value as numeric ) ) FROM equipmentattributevalue" + + " INNER JOIN equipment ON equipmentattributevalue.equipmentid = equipment.equipmentid " + + " INNER JOIN model ON model.modelid = equipmentinstance.modelid " + + " INNER JOIN modelattributevalue ON modelattributevalue.modelid = equipment.modelid" + + " WHERE " + " equipment.equipmenttypeid =" + coldBoxEquipmentTypeId + " AND " + + " equipmentattributevalue.equipmenttypeattributeid = " + qtyPresent_qtyWorking_equipmentTypeAttributeId + " AND" + + " modelattributevalue.modeltypeattributeid = " + modelNameModelTypeAttributeId + " AND" + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ")" + + " GROUP BY modelattributevalue.value"; } else if ( dataBaseInfo.getType().equalsIgnoreCase( "mysql" ) ) { - query = "SELECT catalogdatavalue.value, SUM(equipment.value) FROM equipment" - + " INNER JOIN equipmentinstance ON equipment.equipmentinstanceid = equipmentinstance.equipmentinstanceid " - + " INNER JOIN catalog ON catalog.catalogid = equipmentinstance.catalogid " - + " INNER JOIN catalogdatavalue ON catalogdatavalue.catalogid = equipmentinstance.catalogid" - + " WHERE " + " equipmentinstance.inventorytypeid =" + coldBoxInventoryTypeId + " AND " - + " equipment.inventorytypeattributeid = " + qtyPresent_qtyWorking_inventoryTypeAttributeId + " AND" - + " catalogdatavalue.catalogtypeattributeid = " + modelNameCatalogTypeAttributeId + " AND" - + " equipmentinstance.organisationunitid IN (" + orgUnitIdsByComma + ")" - + " GROUP BY catalogdatavalue.value"; + query = "SELECT modelattributevalue.value, SUM(equipmentattributevalue.value) FROM equipmentattributevalue" + + " INNER JOIN equipment ON equipmentattributevalue.equipmentid = equipment.equipmentid " + + " INNER JOIN model ON model.modelid = equipmentinstance.modelid " + + " INNER JOIN modelattributevalue ON modelattributevalue.modelid = equipment.modelid" + + " WHERE " + " equipment.equipmenttypeid =" + coldBoxEquipmentTypeId + " AND " + + " equipmentattributevalue.equipmenttypeattributeid = " + qtyPresent_qtyWorking_equipmentTypeAttributeId + " AND" + + " modelattributevalue.modeltypeattributeid = " + modelNameModelTypeAttributeId + " AND" + + " equipment.organisationunitid IN (" + orgUnitIdsByComma + ")" + + " GROUP BY modelattributevalue.value"; } SqlRowSet rs = jdbcTemplate.queryForRowSet( query ); === modified file 'local/in/dhis-web-coldchain/src/main/java/org/hisp/dhis/coldchain/reports/action/GenerateCCEIReportAction.java' --- local/in/dhis-web-coldchain/src/main/java/org/hisp/dhis/coldchain/reports/action/GenerateCCEIReportAction.java 2013-12-30 05:54:06 +0000 +++ local/in/dhis-web-coldchain/src/main/java/org/hisp/dhis/coldchain/reports/action/GenerateCCEIReportAction.java 2013-12-30 09:54:47 +0000 @@ -4912,10 +4912,10 @@ CCEMReportDesign ccemReportDesign = reportDesignList.get( 0 ); String ccemCellContent = ccemSettingsMap.get( ccemReportDesign.getContent() ); - Integer inventoryTypeId = Integer.parseInt( ccemCellContent.split( ":" )[0] ); - Integer qtyPresentInventoryTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[1] ); - Integer qtyNotWorkingInventoryTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[2] ); - Integer catalogTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[3] ); + Integer equipmentTypeId = Integer.parseInt( ccemCellContent.split( ":" )[0] ); + Integer qtyPresentEquipmentTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[1] ); + Integer qtyNotWorkingEquipmentTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[2] ); + Integer modelTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[3] ); List tableData = new ArrayList(); @@ -4955,8 +4955,8 @@ String orgUnitidsByComma = ccemReportManager.getOrgunitIdsByComma( selOrgUnitList, orgUnitGrpIdList ); - Map qtyPresentMap = ccemReportManager.getSumOfColdBoxByOrgUnitGroup( inventoryTypeId, qtyPresentInventoryTypeAttributeId, catalogTypeAttributeId, orgUnitidsByComma, orgUnitGroup.getId() ); - Map qtyNotWorkingMap = ccemReportManager.getSumOfColdBoxByOrgUnitGroup( inventoryTypeId, qtyNotWorkingInventoryTypeAttributeId, catalogTypeAttributeId, orgUnitidsByComma, orgUnitGroup.getId() ); + Map qtyPresentMap = ccemReportManager.getSumOfColdBoxByOrgUnitGroup( equipmentTypeId, qtyPresentEquipmentTypeAttributeId, modelTypeAttributeId, orgUnitidsByComma, orgUnitGroup.getId() ); + Map qtyNotWorkingMap = ccemReportManager.getSumOfColdBoxByOrgUnitGroup( equipmentTypeId, qtyNotWorkingEquipmentTypeAttributeId, modelTypeAttributeId, orgUnitidsByComma, orgUnitGroup.getId() ); if( qtyPresentMap != null ) { @@ -4969,7 +4969,7 @@ } } - List modelsList = ccemReportManager.getModelName( inventoryTypeId, catalogTypeAttributeId, orgUnitIdsByComma ); + List modelsList = ccemReportManager.getModelName( equipmentTypeId, modelTypeAttributeId, orgUnitIdsByComma ); for( String model : modelsList ) { Integer total = 0; @@ -5018,20 +5018,20 @@ { CCEMReportDesign ccemReportDesign = reportDesignList.get( 0 ); String ccemCellContent = ccemSettingsMap.get( ccemReportDesign.getContent() ); - Integer inventoryTypeId = Integer.parseInt( ccemCellContent.split( ":" )[0] ); - Integer qtyPresentInventoryTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[1] ); - Integer qtyNotWorkingInventoryTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[2] ); - Integer catalogTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[3] ); + Integer equipmentTypeId = Integer.parseInt( ccemCellContent.split( ":" )[0] ); + Integer qtyPresentEquipmentTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[1] ); + Integer qtyNotWorkingEquipmentTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[2] ); + Integer modelTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[3] ); - Map qtyPresentMap = ccemReportManager.getSumOfEquipmentAndCatalogValue( inventoryTypeId, qtyPresentInventoryTypeAttributeId, catalogTypeAttributeId, orgUnitIdsByComma ); - Map qtyNotWorkingMap = ccemReportManager.getSumOfEquipmentAndCatalogValue( inventoryTypeId, qtyNotWorkingInventoryTypeAttributeId, catalogTypeAttributeId, orgUnitIdsByComma ); + Map qtyPresentMap = ccemReportManager.getSumOfEquipmentAndCatalogValue( equipmentTypeId, qtyPresentEquipmentTypeAttributeId, modelTypeAttributeId, orgUnitIdsByComma ); + Map qtyNotWorkingMap = ccemReportManager.getSumOfEquipmentAndCatalogValue( equipmentTypeId, qtyNotWorkingEquipmentTypeAttributeId, modelTypeAttributeId, orgUnitIdsByComma ); //Map workingEquipmentMap = (ccemReportManager.getModelNameAndCountForColdBox( catalogTypeAttributeId, inventoryTypeId, 1 + "", orgUnitIdsByComma )); //Map notWorkingEquipmentMap = (ccemReportManager.getModelNameAndCountForColdBox( catalogTypeAttributeId, inventoryTypeId, 0 + "", orgUnitIdsByComma )); DefaultCategoryDataset dataset = new DefaultCategoryDataset(); - List modelsList = ccemReportManager.getModelName( inventoryTypeId, catalogTypeAttributeId, orgUnitIdsByComma ); + List modelsList = ccemReportManager.getModelName( equipmentTypeId, modelTypeAttributeId, orgUnitIdsByComma ); for ( String model : modelsList ) { if ( qtyPresentMap.containsKey( model ) ) @@ -5062,11 +5062,12 @@ hash.put( "reportName", "Quantity of Cold Boxes/carriers" ); CCEMReportDesign ccemReportDesign = reportDesignList.get( 0 ); String ccemCellContent = ccemSettingsMap.get( ccemReportDesign.getContent() ); - Integer inventoryTypeId = Integer.parseInt( ccemCellContent.split( ":" )[0] ); - Integer inventoryTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[1] ); - Integer catalogTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[2] ); + + Integer equipmentTypeId = Integer.parseInt( ccemCellContent.split( ":" )[0] ); + Integer equipmentTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[1] ); + Integer modelTypeAttributeId = Integer.parseInt( ccemCellContent.split( ":" )[2] ); - Map quantityMap = ccemReportManager.getSumOfEquipmentAndCatalogValue( inventoryTypeId, inventoryTypeAttributeId, catalogTypeAttributeId, orgUnitIdsByComma ); + Map quantityMap = ccemReportManager.getSumOfEquipmentAndCatalogValue( equipmentTypeId, equipmentTypeAttributeId, modelTypeAttributeId, orgUnitIdsByComma ); DefaultCategoryDataset dataset = new DefaultCategoryDataset(); for ( String catalogValue : quantityMap.keySet() ) { === modified file 'local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/ShowImageAction.java' --- local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/ShowImageAction.java 2013-09-05 11:11:36 +0000 +++ local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/ShowImageAction.java 2013-12-30 09:54:47 +0000 @@ -59,12 +59,12 @@ if ( model.getModelImage() != null ) { - modelImage = System.getenv( "DHIS2_HOME" ) + File.separator + Model.DEFAULT_CCEMFOLDER + File.separator + model.getModelImage(); + modelImage = System.getenv( "DHIS2_HOME" ) + File.separator + "ccei" + File.separator + Model.DEFAULT_CCEMFOLDER + File.separator + model.getModelImage(); } else if ( model.getModelType().getModelTypeImage() != null ) { - modelImage = System.getenv( "DHIS2_HOME" ) + File.separator + Model.DEFAULT_CCEMFOLDER + File.separator + model.getModelType().getModelTypeImage(); + modelImage = System.getenv( "DHIS2_HOME" ) + File.separator + "ccei" + File.separator + Model.DEFAULT_CCEMFOLDER + File.separator + model.getModelType().getModelTypeImage(); } else === modified file 'local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/ShowModelTypeImageAction.java' --- local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/ShowModelTypeImageAction.java 2013-09-05 11:11:36 +0000 +++ local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/ShowModelTypeImageAction.java 2013-12-30 09:54:47 +0000 @@ -59,7 +59,7 @@ if ( modelType.getModelTypeImage() != null ) { - modelImage = System.getenv( "DHIS2_HOME" ) + File.separator + Model.DEFAULT_CCEMFOLDER + File.separator + modelType.getModelTypeImage(); + modelImage = System.getenv( "DHIS2_HOME" ) + File.separator + "ccei" + File.separator + Model.DEFAULT_CCEMFOLDER + File.separator + modelType.getModelTypeImage(); } else === modified file 'local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/UploadModelTypeImageAction.java' --- local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/UploadModelTypeImageAction.java 2013-09-05 11:11:36 +0000 +++ local/in/dhis-web-maintenance-ccem/src/main/java/org/hisp/dhis/ccem/model/action/UploadModelTypeImageAction.java 2013-12-30 09:54:47 +0000 @@ -90,7 +90,7 @@ modelTypeService.updateModelType( modelType ); - String outputFilePath = System.getenv( "DHIS2_HOME" ) + File.separator + Model.DEFAULT_CCEMFOLDER; + String outputFilePath = System.getenv( "DHIS2_HOME" ) + File.separator + "ccei" + File.separator + Model.DEFAULT_CCEMFOLDER; File newdir = new File( outputFilePath ); if( !newdir.exists() )