Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashReportHelper.java
===================================================================
diff -u -r35908 -r35910
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashReportHelper.java	(.../WashReportHelper.java)	(revision 35908)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/WashReportHelper.java	(.../WashReportHelper.java)	(revision 35910)
@@ -1,27 +1,60 @@
 package com.forgon.disinfectsystem.jasperreports.util;
 
 import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
 import java.util.ArrayList;
+import java.util.Calendar;
+import java.util.Date;
+import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.Map.Entry;
+
+import net.sf.json.JSONObject;
+
+import org.apache.commons.collections4.CollectionUtils;
+import org.apache.commons.lang.StringUtils;
+import org.apache.log4j.Logger;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Component;
+
 import com.forgon.databaseadapter.service.DateQueryAdapter;
 import com.forgon.disinfectsystem.jasperreports.javabeansource.CleaningAreaWorkloadForUser;
+import com.forgon.disinfectsystem.jasperreports.javabeansource.MaterialTypeWorkloadReport;
+import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex;
+import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams;
+import com.forgon.disinfectsystem.tousse.materialdefinition.service.MaterialDefinitionManager;
+import com.forgon.systemsetting.model.HttpOption;
+import com.forgon.systemsetting.service.HttpOptionManager;
+import com.forgon.tools.MathTools;
 import com.forgon.tools.db.DatabaseUtil;
 import com.forgon.tools.hibernate.ObjectDao;
 import com.forgon.tools.string.StringTools;
 import com.forgon.tools.util.ForgonDateUtils;
+import com.forgon.tools.util.SqlUtils;
 
 /**
- * 清洗模块的一些报表
+ * 清洗模块的一些报表(超过500行建议新建一个helper)
  * 清洗区人员工作量统计报表
+ * 清洗区材料类型工作量统计报表
+ * 
  */
 @Component
 public class WashReportHelper {
+	private Logger logger = Logger.getLogger(this.getClass());
 	@Autowired
 	private DateQueryAdapter dateQueryAdapter;
 	@Autowired
 	private ObjectDao objectDao;
+	@Autowired
+	private ReportSqlUtil reportSqlUtil;
+	@Autowired
+	private HttpOptionManager httpOptionManager;
+	@Autowired
+	private MaterialDefinitionManager materialDefinitionManager;
 	/**
 	 * 获取清洗区人员工作量统计报表的数据
 	 * @param monthSearch		查询的月份
@@ -89,4 +122,264 @@
 		}
 		return list;
 	}
+	/**
+	 * 获取清洗区材料类型工作量统计报表年份数据
+	 * @param instrumentSetTypesSet 器械包种类id
+	 * @param queryYear 查询年份
+	 * @param querySupplyRoom 供应室编码
+	 * @return
+	 */
+	public List<MaterialTypeWorkloadReport> findMaterialTypeWorkloadReportListByYear(Set<String> instrumentSetTypesSet, String queryYear,String querySupplyRoom){
+		String startYear = queryYear + "-01-01 00:00:00";
+		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+		SimpleDateFormat showSdf = new SimpleDateFormat("yyyy-MM");
+		Date statDate = null;
+		try {
+			statDate = sdf.parse(startYear);
+		} catch (ParseException e) {
+			e.printStackTrace();
+		}
+		Calendar calendar = Calendar.getInstance();
+		if(statDate == null){
+			return null;
+		}
+		calendar.setTime(statDate);
+		calendar.add(Calendar.YEAR, 1);
+		Date endDate = calendar.getTime();
+		List<MaterialTypeWorkloadReport> list = new ArrayList<MaterialTypeWorkloadReport>();
+		Map<String,Integer> materialTypeSecquenceMap = queryMaterialTypeSecquenceMap();
+		String undefinedType = "未设置";
+		while(endDate.after(statDate)){
+			String startDateStr = sdf.format(statDate);
+			String showStartDateStr = showSdf.format(statDate);
+			calendar.setTime(statDate);
+			calendar.add(Calendar.MONTH, 1);
+			statDate = calendar.getTime();
+			String endDateStr = sdf.format(statDate);
+			materialTypeWorkloadReportList(instrumentSetTypesSet, undefinedType, materialTypeSecquenceMap, showStartDateStr,startDateStr,endDateStr,querySupplyRoom,list);
+		}
+		return list;
+	}
+	/**
+	 * 
+	 * @param instrumentSetTypesSet 器械包种类id
+	 * @param month
+	 * @param querySupplyRoom
+	 * @return
+	 */
+	public List<MaterialTypeWorkloadReport> findMaterialTypeWorkloadReportList(Set<String> instrumentSetTypesSet, String month,String querySupplyRoom){
+		
+		month = month + "-01";
+		
+		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+		Date statDate = null;
+		try {
+			statDate = sdf.parse(month);
+		} catch (ParseException e) {
+			e.printStackTrace();
+		}
+		Calendar calendar = Calendar.getInstance();
+		if(statDate == null){
+			return null;
+		}
+		calendar.setTime(statDate);
+		calendar.add(Calendar.MONTH, 1);
+		Date endDate = calendar.getTime();
+		List<MaterialTypeWorkloadReport> list = new ArrayList<MaterialTypeWorkloadReport>();
+		Map<String,Integer> materialTypeSecquenceMap = queryMaterialTypeSecquenceMap();
+		String undefinedType = "未设置";
+		while(endDate.after(statDate)){
+			String startDateStr = sdf.format(statDate);
+			calendar.setTime(statDate);
+			calendar.add(Calendar.DAY_OF_MONTH, 1);
+			statDate = calendar.getTime();
+			String endDateStr = sdf.format(statDate);
+			materialTypeWorkloadReportList(instrumentSetTypesSet, undefinedType, materialTypeSecquenceMap, startDateStr, startDateStr,endDateStr,querySupplyRoom,list);
+		}
+		return list;
+	}
+	/**
+	 * 获取每日清洗数据
+	 * @param instrumentSetTypesSet 器械包种类id
+	 * @param statDate
+	 * @param endDate
+	 * @return
+	 */
+	private void materialTypeWorkloadReportList(Set<String> instrumentSetTypesSet, String UNDEFINED_TYPE, Map<String,Integer> materialTypeSecquenceMap, String showStatDate, String statDate,String endDate,String querySupplyRoom,List<MaterialTypeWorkloadReport> list){
+		ReportQueryParams params = new ReportQueryParams();
+		params.betweenSql = String.format(" between %s and %s ",
+				dateQueryAdapter.dateConverAdapter2(statDate, "yyyy-mm-dd HH24:MI:SS"),
+				dateQueryAdapter.dateConverAdapter2(endDate, "yyyy-mm-dd HH24:MI:SS"));
+		params.querySupplyRoom = querySupplyRoom;
+		
+		String getInstrumentSetTypeSql = CollectionUtils.isEmpty(instrumentSetTypesSet)?"":reportSqlUtil.getInstrumentSetTypeSql(instrumentSetTypesSet);
+		// 按材料类型分类，统计数量
+		String sql = "select t1.type,sum(t1.amount) from (";
+		
+		// 统计整包清洗的器械包、外来器械包、消毒物品、自定义器械包的材料件数
+			   sql += "select md.type as type,sum((ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) * mi.count) as amount from ";
+	           sql += "washanddisinfectrecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,";
+	           sql += "TousseDefinition td,materialinstance mi,materialdefinition md ";
+	           sql += "where wr.id = cw.washanddisinfectrecord_id and cw.classifybasket_id = cb.id ";
+			   sql += "and cb.id = ci.classifybasket_id and ci.toussedefinition_id = td.id and td.id = mi.tousse_id and mi.materialdefinition_id = md.id ";
+			   sql += "and ci.itemType <> '材料' and wr.washMaterialAmount <> 0 ";
+			   sql += "and wr.enddate " + params.betweenSql + getInstrumentSetTypeSql;
+			   sql += SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom) + " group by md.type";
+
+			   sql += " union all ";
+			   
+			   // 统计拆包清洗的材料件数（主要包括拆包清洗的器械包以及外来器械）
+			   sql += "select md.type as type,sum(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) as amount from ";
+			   sql += "washanddisinfectrecord wr ,ClassifyBasket_WashRecord cw ,ClassifyBasket cb, ClassifiedItem ci,TousseDefinition td,MaterialDefinition md ";
+			   sql += "where wr.id = cw.washanddisinfectrecord_id and cw.classifybasket_id = cb.id and cb.id = ci.classifybasket_id ";
+			   sql += "and ci.materialdefinition_id = md.id and ci.itemType = '材料' and ci.toussedefinition_id = td.id and wr.washMaterialAmount <> 0 and wr.enddate " + params.betweenSql + getInstrumentSetTypeSql;
+			   sql += SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom) + " group by md.type";
+			   if(CollectionUtils.isEmpty(instrumentSetTypesSet)){
+				// 统计单独登记清洗的材料数量
+				   sql += " union all ";
+					sql += String
+					.format("select md.type type,sum(wrm.amount) amount "
+							+ DataIndex.getWashMaterialAmountSqlFromTousseTypeIsMaterial("")
+							+ "and wr.endDate %s %s group by md.type ",
+							params.betweenSql,
+							SqlUtils.get_InSql_Extra("wr.orgUnitCoding", params.querySupplyRoom)
+							);
+			   }
+			   sql += ") t1 group by t1.type"; 
+			   
+
+			   String likeSql = "";
+			   if (StringUtils.isNotBlank(querySupplyRoom)){
+				   likeSql =  SqlUtils.get_LikeSql("fd.departcodes", querySupplyRoom.split(","));
+			   }
+			   if(CollectionUtils.isNotEmpty(instrumentSetTypesSet)){
+				   likeSql += getInstrumentSetTypeSql;
+				   params.extraJoinCondition = " join TousseDefinition td on td.id=qmd.TousseDefinitionId ";
+			   }
+			   
+			   
+		params.extraSelectColumns = ",qmd.material";
+		params.extraGroupBy = ",qmd.material";
+		String sql2 = DataIndex.getUnQualifiedMonitorAmountSql("清洗消毒", likeSql,
+						params);
+
+			  
+	   ResultSet rs2 = objectDao.executeSql(sql2);
+	   Map<String, Integer> monitoringMap = new HashMap<String, Integer>();
+		try {
+			Map<String, Integer> nameAndAmount = new HashMap<String, Integer>();
+			StringBuffer materialNames = new StringBuffer();
+			int num = 0;
+			while(rs2.next()){
+				Integer washUnqualifiedAmount = rs2.getInt(1);
+				String materialName = rs2.getString(2);
+				if(StringUtils.isNotBlank(materialName)){
+					if(num == 0){
+						materialNames.append("('").append(materialName);
+						num++;
+					}else{
+						materialNames.append("','").append(materialName);
+					}
+					nameAndAmount.put(materialName, washUnqualifiedAmount);
+				}
+			}
+			Map<String, JSONObject> mdsJson = null;
+			if(num > 0){
+				materialNames.append("')");
+				mdsJson = materialDefinitionManager.getMaterialDefinitionListByNames(materialNames.toString());
+				for(Entry<String,Integer> entry : nameAndAmount.entrySet()){
+                    String materialName = entry.getKey();
+					Integer washUnqualifiedAmount = entry.getValue();
+					JSONObject mdJson = mdsJson.get(materialName);
+					String type = UNDEFINED_TYPE;
+					if(mdJson != null){
+						String typeOfJson = mdJson.optString("type");
+						if(StringUtils.isNotBlank(typeOfJson)){//材料类型未定义的话，设为未设置
+							type = typeOfJson;
+						}
+					}else{
+						logger.debug("材料定义未找到，materialName = " + materialName + ", amount = " + washUnqualifiedAmount);
+					}
+					Integer oldAmount = monitoringMap.get(type);
+					if(oldAmount == null){
+						oldAmount = 0;
+					}
+					monitoringMap.put(type, oldAmount + washUnqualifiedAmount);
+				}
+			}
+		} catch (SQLException e) {
+			e.printStackTrace();
+		} finally {
+			DatabaseUtil.closeResultSetAndStatement(rs2);
+		}
+		Integer totalAmount = 0;
+		Integer totalUnqualifiedAmount = 0;
+		ResultSet rs = objectDao.executeSql(sql);
+		List<MaterialTypeWorkloadReport> tmpList = new ArrayList<MaterialTypeWorkloadReport>();
+		try {
+			while(rs.next()){
+				String materialType = rs.getString(1);
+				Integer amount = rs.getInt(2);
+				MaterialTypeWorkloadReport data = new MaterialTypeWorkloadReport();
+				data.setAmount(amount);
+				data.setColumnNum(1);
+				data.setDayOfMonth(showStatDate);
+				
+				if(StringUtils.isBlank(materialType)){
+					materialType = UNDEFINED_TYPE;
+					data.setColumnNum(materialTypeSecquenceMap.get(UNDEFINED_TYPE));
+				}else{
+					if(!materialTypeSecquenceMap.containsKey(materialType)){
+						materialTypeSecquenceMap.put(materialType, materialTypeSecquenceMap.size() + 1);
+					}
+					data.setColumnNum(materialTypeSecquenceMap.get(materialType));
+				}
+				
+				Integer washUnqualifiedAmount = monitoringMap.get(materialType);
+				if(washUnqualifiedAmount == null){
+					washUnqualifiedAmount = 0;
+				}
+				data.setMaterialType(materialType);
+				totalUnqualifiedAmount += washUnqualifiedAmount;
+				data.setWashUnqualifiedAmount(washUnqualifiedAmount);
+				if (amount != 0){
+					data.setWashUnqualifiedPercentage(MathTools.divide(washUnqualifiedAmount * 100, amount, 4));
+				}
+				else{
+					data.setWashUnqualifiedPercentage(0d);
+				}
+				totalAmount += amount;
+				tmpList.add(data);
+			}
+		} catch (SQLException e) {
+			e.printStackTrace();
+		} finally {
+			DatabaseUtil.closeResultSetAndStatement(rs);
+		}
+		list.addAll(tmpList);
+	}
+	/**
+	 * 获取设置的材料类型和对应的序号。类型有设置序号的 第一优先级；类型为空第二优先级， 也就是未设置列；有类型，但是没序号的第三优先级。
+	 * @return
+	 */
+	private Map<String,Integer> queryMaterialTypeSecquenceMap(){
+		List<HttpOption> hos =  httpOptionManager.getHttpOptionListById(HttpOption.SYSTEMSETTING_MATERIALTYPE);
+		Map<String,Integer> materialTypeSecquenceMap = new HashMap<String,Integer>();
+		// 最大的顺序号，用来设置‘未设置’类型的列号
+		Integer maxSecquence = 1;
+		if(CollectionUtils.isNotEmpty(hos)){
+			for(HttpOption ho : hos){
+				String typeName = ho.getOptionText();
+				Integer sec = ho.getSequence();
+				if(sec > maxSecquence){
+					maxSecquence = sec;
+				}
+				if(StringUtils.isNotBlank(typeName)){
+					materialTypeSecquenceMap.put(typeName, sec);
+				}
+			}
+		}
+		materialTypeSecquenceMap.put("未设置", maxSecquence + 1);
+		return materialTypeSecquenceMap;
+	}
 }