Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportManagerImpl.java
===================================================================
diff -u -r27498 -r28253
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportManagerImpl.java	(.../ReportManagerImpl.java)	(revision 27498)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportManagerImpl.java	(.../ReportManagerImpl.java)	(revision 28253)
@@ -7,6 +7,7 @@
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
+import java.util.Map.Entry;
 import java.util.TreeMap;
 
 import net.sf.json.JSONArray;
@@ -27,6 +28,7 @@
 import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex;
 import com.forgon.disinfectsystem.reportforms.constant.ReportConstant;
 import com.forgon.disinfectsystem.reportforms.util.MapKeyComparator;
+import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams;
 import com.forgon.systemsetting.service.HttpOptionManager;
 import com.forgon.tools.db.DatabaseUtil;
 import com.forgon.tools.db.InitDbConnection;
@@ -51,6 +53,11 @@
 	
 	private DateQueryAdapter dateQueryAdapter;
 
+	private DataIndex dataIndex;
+	
+	public void setDataIndex(DataIndex dataIndex) {
+		this.dataIndex = dataIndex;
+	}
 	public void setGoodsOptionManager(GoodsOptionManager goodsOptionManager) {
 		this.goodsOptionManager = goodsOptionManager;
 	}
@@ -102,15 +109,29 @@
 		String queryReportDateType = null;
 		String queryBeginDateExpress = null;
 		String queryEndDateExpress = null;
+		String[] monthArr = null;
+		List<String> dayList = new ArrayList<String>();
+		String wrRendDate = null;
+		String iSendTime = null;
+		String tiSterileEndTime = null;
+		Map<String, String> yearMonthMap = new HashMap<String, String>();//月份对应的年月map,用于报表显示
 		if(reportDate.length() == 4){
+			monthArr = new String[]{"01","02","03","04","05","06","07","08","09","10","11","12"};//月份,不同的数据库月份可能不一样
+			for (int i = 0; i < monthArr.length; i++) {
+				yearMonthMap.put(monthArr[i], reportDate + "-" + monthArr[i]);
+			}
 			queryReportDateType = ReportConstant.REPORT_DATE_TYPE_MONTH;
 			if(dbConnection.isSqlServer()){
 				queryBeginDateExpress = "'" + reportDate + "-01-01'";
 				queryEndDateExpress = "'" + reportDate + "-12-31 23:59:59'";
+				tiSterileEndTime = dateQueryAdapter.dateConverAdapter3("ti.sterileEndTime","mm");
 			} else if(dbConnection.isOracle()){
 				queryBeginDateExpress = "to_date('" + reportDate + "-01-01' , 'yyyy-MM-dd')";
 				queryEndDateExpress = "to_date('" + reportDate + "-12-31 23:59:59' , 'yyyy-MM-dd hh24:mi:ss')";
+				tiSterileEndTime = dateQueryAdapter.dateConverAdapter3(dateQueryAdapter.stringFieldToDate("ti.sterileEndTime"),"mm");
 			}
+			wrRendDate = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm");
+			iSendTime = dateQueryAdapter.dateConverAdapter3("i.sendTime","mm");
 		} else {
 			queryReportDateType = ReportConstant.REPORT_DATE_TYPE_DAY;
 			//判断年份与月份
@@ -127,13 +148,27 @@
 			} else if(month == 4 || month == 6 || month == 9 || month == 11){
 				lastDayInMonth = 30;
 			}
+			String reportDateStr = reportDate + "-";
+			for (int i = 1; i <= lastDayInMonth; i++) {
+				String day = null;
+				if(i < 10){
+					day = "0" + i;
+				}else{
+					day = i + "";
+				}
+				dayList.add(reportDateStr + day);
+			}
 			if(dbConnection.isSqlServer()){
 				queryBeginDateExpress = "'" + reportDate + "-01'";
 				queryEndDateExpress = "'" + reportDate + "-"+ lastDayInMonth +" 23:59:59'";
+				tiSterileEndTime = dateQueryAdapter.dateConverAdapter(dateQueryAdapter.stringFieldToDate("ti.sterileEndTime"),"yyyy-MM-dd");
 			} else if(dbConnection.isOracle()){
 				queryBeginDateExpress = "to_date('" + reportDate + "-01' , 'yyyy-MM-dd')";
 				queryEndDateExpress = "to_date('" + reportDate + "-"+ lastDayInMonth +" 23:59:59' , 'yyyy-MM-dd hh24:mi:ss')";
+				tiSterileEndTime = dateQueryAdapter.dateConverAdapter(dateQueryAdapter.stringFieldToDate("ti.sterileEndTime"),"yyyy-MM-dd");
 			}
+			wrRendDate = dateQueryAdapter.dateConverAdapter("wr.endDate","yyyy-MM-dd");
+			iSendTime = dateQueryAdapter.dateConverAdapter("i.sendTime","yyyy-MM-dd");
 		}
 		List<Map<String , Object>> datas = new ArrayList<Map<String , Object>>();
 		//是否启用年度消毒中心指标工作量报表用特殊的定制显示(东莞市中医院)
@@ -183,53 +218,153 @@
 
 			//器械包发放数量
 		}else{
-			//1.处理器械总件数、2.处理外来手术器械总件数
-			String querySql = "select tedd.dateNum "+ ReportConstant.CSSD_WORKLOAD_REPORTDATE[0] +", " + 
-					" sum(generalMaterialAmount + operateMaterialAmount + foreignMaterialAmount) "+ ReportConstant.CSSD_WORKLOAD_PROCESSEDTOUSSEAMOUNT[0] +
-					" ,sum(foreignMaterialAmount) "+ ReportConstant.CSSD_WORKLOAD_PROCESSEDFOREIGNOPERATIONTOUSSEAMOUNT[0] +  
-					" from tempEveryDateData tedd  " + 
-					" left join WashAndDisinfectRecord w on w.endDate >= tedd.beginTime and w.endDate <= tedd.endTime and w.orgUnitCoding = '"+ departCoding +"' " + 
-					" where tedd.beginTime >= "+ queryBeginDateExpress + " and tedd.endTime <= "+ queryEndDateExpress +
-					" and tedd.type='"+ queryReportDateType +"' " + 
-					" group by tedd.dateNum order by tedd.dateNum";
-			//key为报表结果的日期,value为各指标的值
-			Map<String,Map<String,Object>> result = countCssdWorkloadQuota(querySql , ReportConstant.CSSD_WORKLOAD_PROCESSEDTOUSSEAMOUNT[0] , ReportConstant.CSSD_WORKLOAD_PROCESSEDFOREIGNOPERATIONTOUSSEAMOUNT[0]);
-			mergeQuotaMapToDataList(result , datas);
-
-			//3.消毒物品数量
-			querySql = "select tedd.dateNum "+ ReportConstant.CSSD_WORKLOAD_REPORTDATE[0] +", " + 
-					"  count(titd.operationTime) "+ ReportConstant.CSSD_WORKLOAD_DISINFECTIONTOUSSEAMOUNT[0] +
-					" from tempEveryDateData tedd  " + 
-					" left join (select ti.operationTime,ti.orgUnitCoding from TousseInstance ti join TousseDefinition td on td.id=ti.tousseDefinition_id "
-					+ "where td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"' and ti.toussefixedBarcode<>1) " + 
-					" titd on titd.operationTime >= tedd.beginTime and titd.operationTime <= tedd.endTime and titd.orgUnitCoding = '"+ departCoding +"' " + 
-					" where tedd.beginTime >= "+ queryBeginDateExpress + " and tedd.endTime <= "+ queryEndDateExpress +
-					" and tedd.type='"+ queryReportDateType +"' " + 
-					" group by tedd.dateNum order by tedd.dateNum";
-			//key为报表结果的日期,value为各指标的值
-			result = countCssdWorkloadQuota(querySql , ReportConstant.CSSD_WORKLOAD_DISINFECTIONTOUSSEAMOUNT[0]);
-			mergeQuotaMapToDataList(result , datas);
+			Map<String,Map<String,Object>> result = null;
+			// 处理器械总件数
+			String betweenSql = " between " + queryBeginDateExpress + " and " + queryEndDateExpress + " ";
+			ReportQueryParams params = new ReportQueryParams();
+			//数据库计算字符串长度的函数名
+			int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3);
+			String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection);
+			params.betweenSql = betweenSql;
+			params.querySupplyRoom = departCoding;
+			params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = "";
+			params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = "";
+			params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = "";
+			params.tousseTypeAndPackageSizeSql = "";
+			params.sqlLengthFunctionName = sqlLengthFunctionName;
+			params.monthlyStr = wrRendDate;
+			String sql = String.format("select sum(tl.amount) amount, monthstr  from ("
+					+dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports)
+					+ ") tl group by monthstr order by monthstr ");
+		    ResultSet totalTousseRs = null;
+			try {
+				totalTousseRs = objectDao.executeSql(sql);
+				Map<String, Integer> totalTousseRsResult = new HashMap<String, Integer>();
+				while (totalTousseRs.next()) {
+					Integer totalForeignTousseAmount = totalTousseRs.getInt("amount");
+					String monthstr = totalTousseRs.getString("monthstr");
+					totalTousseRsResult.put(monthstr, totalForeignTousseAmount);					
+				}
+				Map<String,Map<String,Object>> totalTousseMap = new TreeMap<String, Map<String,Object>>(new MapKeyComparator());
+				supplementMonthsWithoutData(dayList, yearMonthMap, totalTousseRsResult, totalTousseMap, ReportConstant.CSSD_WORKLOAD_PROCESSEDTOUSSEAMOUNT[0]);
+				mergeQuotaMapToDataList(totalTousseMap , datas);
+			} catch (SQLException e) {
+				e.printStackTrace();
+			}finally {
+				DatabaseUtil.closeResultSetAndStatement(totalTousseRs);
+			}
+			// 处理外来器械总件数
+			params.tousseTypes = TousseDefinition.PACKAGE_TYPE_FOREIGN;
+		    params.tousseTypeAndPackageSizeSql = DataIndex.getTousseTypesAndPackageSizesFilterSQL(TousseDefinition.PACKAGE_TYPE_FOREIGN, null);
+		    sql = String.format("select sum(tl.amount) amount, monthstr from ("
+					+dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports)
+					+ ") tl group by monthstr order by monthstr");
+		    ResultSet totalForeignTousseRs = null;
+			try {
+				totalForeignTousseRs = objectDao.executeSql(sql);
+				Map<String, Integer> totalForgonTousseRsResult = new HashMap<String, Integer>();
+				while (totalForeignTousseRs.next()) {
+					Integer totalForeignTousseAmount = totalForeignTousseRs.getInt("amount");
+					String monthstr = totalForeignTousseRs.getString("monthstr");
+					totalForgonTousseRsResult.put(monthstr, totalForeignTousseAmount);	
+				}
+				Map<String,Map<String,Object>> totalForgonTousseMap = new TreeMap<String, Map<String,Object>>(new MapKeyComparator());
+				supplementMonthsWithoutData(dayList, yearMonthMap, totalForgonTousseRsResult, totalForgonTousseMap, ReportConstant.CSSD_WORKLOAD_PROCESSEDFOREIGNOPERATIONTOUSSEAMOUNT[0]);
+				mergeQuotaMapToDataList(totalForgonTousseMap , datas);
+			} catch (SQLException e) {
+				e.printStackTrace();
+			}finally {
+				DatabaseUtil.closeResultSetAndStatement(totalForeignTousseRs);
+			}
+			// 清空参数
+		    params.tousseTypes = "";
+		    params.tousseTypeAndPackageSizeSql = "";
+			//消毒物品数量
+			params.tousseTypeAndPackageSizeSql = DataIndex
+					.getTousseTypesAndPackageSizesFilterSQL(
+							TousseDefinition.PACKAGE_TYPE_DISINFECTION, null);
+			params.extraSelectColumns = "," + iSendTime + "monthstr";
+			params.extraGroupBy = "";
+			params.groupBySql = " group by " + iSendTime;
+			sql = String.format("select sum(tl.amount) amount,monthstr from ("
+					+dataIndex.getWorkAmountByMaterialSQL("发货数量", params, dataSoureOfMaterialsCountOfToussesInReports)
+					+ ") tl group by monthstr order by monthstr");
+		    ResultSet disinfectionRs = null;
+			try {
+				disinfectionRs = objectDao.executeSql(sql);
+				Map<String, Integer> disinfectionRsResult = new HashMap<String, Integer>();
+				while (disinfectionRs.next()) {
+					Integer disinfectionAmount = disinfectionRs.getInt("amount");
+					String month = disinfectionRs.getString("monthstr");
+					disinfectionRsResult.put(month, disinfectionAmount);	
+				}
+				Map<String,Map<String,Object>> disinfectionMap = new TreeMap<String, Map<String,Object>>(new MapKeyComparator());
+				supplementMonthsWithoutData(dayList, yearMonthMap, disinfectionRsResult, disinfectionMap, ReportConstant.CSSD_WORKLOAD_DISINFECTIONTOUSSEAMOUNT[0]);
+				mergeQuotaMapToDataList(disinfectionMap , datas);
+			} catch (SQLException e) {
+				e.printStackTrace();
+			}finally {
+				DatabaseUtil.closeResultSetAndStatement(disinfectionRs);
+			}
+			//4.灭菌无菌包数
+			Map<String, Integer> sterileResultMap = new HashMap<String, Integer>();
 			
-			//4.灭菌无菌包数、5.灭菌外来手术器械包数
-			querySql = "select tedd.dateNum "+ ReportConstant.CSSD_WORKLOAD_REPORTDATE[0] +", " + 
-					" sum(sr.amount) "+ ReportConstant.CSSD_WORKLOAD_STERILEDTOUSSEAMOUNT[0] +",sum(sr.foreignTousseAmount) "+ ReportConstant.CSSD_WORKLOAD_STERILEDFOREIGNTOUSSEAMOUNT[0] +
-					" from tempEveryDateData tedd  " + 
-					" left join SterilizationRecord sr on sr.status = '"+ SterilizationRecord.STERILIZATION_STATUS_END +"'  " + 
-					" and sr.orgUnitCoding = '"+ departCoding +"' " + 
-					" and sr.endDate >=  tedd.beginTime and sr.endDate <= tedd.endTime" + 
-					" where tedd.beginTime >= "+ queryBeginDateExpress + " and tedd.endTime <= "+ queryEndDateExpress +
-					" and tedd.type='"+ queryReportDateType +"' " + 
-					" group by tedd.dateNum order by tedd.dateNum";
-			//key为报表结果的日期,value为各指标的值
-			result = countCssdWorkloadQuota(querySql , ReportConstant.CSSD_WORKLOAD_STERILEDTOUSSEAMOUNT[0] , ReportConstant.CSSD_WORKLOAD_STERILEDFOREIGNTOUSSEAMOUNT[0]);
-			mergeQuotaMapToDataList(result , datas);
-			
+			params.monthlyStr = tiSterileEndTime;
+			params.extraSelectColumns = "";
+			params.groupBySql = "";
+			// 灭菌无菌包数
+			params.tousseTypeAndPackageSizeSql = "";
+			String sterileSql = String.format("select sum(tl.amount) amount,monthstr from ("
+					+dataIndex.getWorkAmountByPackageSQL("灭菌数量", params)
+					+ ") tl group by monthstr order by monthstr");
+		    ResultSet sterileRs = null;
+			try {
+				sterileRs = objectDao.executeSql(sterileSql);
+				while (sterileRs.next()) {
+					Integer sterilizaAmount = sterileRs.getInt("amount");
+					String month = sterileRs.getString("monthstr");
+					sterileResultMap.put(month, sterilizaAmount);
+				}
+				Map<String,Map<String,Object>> sterileMap = new TreeMap<String, Map<String,Object>>(new MapKeyComparator());
+				supplementMonthsWithoutData(dayList, yearMonthMap, sterileResultMap, sterileMap, ReportConstant.CSSD_WORKLOAD_STERILEDTOUSSEAMOUNT[0]);
+				mergeQuotaMapToDataList(sterileMap , datas);
+			} catch (SQLException e) {
+				e.printStackTrace();
+			}finally {
+				DatabaseUtil.closeResultSetAndStatement(sterileRs);
+			}
+			//5.灭菌外来手术器械包数
+			params.tousseTypeAndPackageSizeSql = DataIndex
+					.getTousseTypesAndPackageSizesFilterSQL(
+							TousseDefinition.PACKAGE_TYPE_FOREIGN + ","
+									+ TousseDefinition.PACKAGE_TYPE_SPLIT, null);
+			String sterileForeignSql = String.format("select sum(tl.amount) amount,monthstr from ("
+					+dataIndex.getWorkAmountByPackageSQL("灭菌数量", params)
+					+ ") tl group by monthstr order by monthstr");
+		    
+		    ResultSet sterileForeignRs = null;
+			try {
+				Map<String, Integer> sterileForeignResultMap = new HashMap<String, Integer>();//灭菌外来手术器械包数
+				sterileForeignRs = objectDao.executeSql(sterileForeignSql);
+				while (sterileForeignRs.next()) {
+					Integer sterilizaForeignAmount = sterileForeignRs.getInt("amount");
+					String month = sterileForeignRs.getString("monthstr");
+					sterileForeignResultMap.put(month, sterilizaForeignAmount);
+				}
+				Map<String,Map<String,Object>> sterileForeignMap = new TreeMap<String, Map<String,Object>>(new MapKeyComparator());
+				supplementMonthsWithoutData(dayList, yearMonthMap, sterileForeignResultMap, sterileForeignMap, ReportConstant.CSSD_WORKLOAD_STERILEDFOREIGNTOUSSEAMOUNT[0]);
+				mergeQuotaMapToDataList(sterileForeignMap , datas);
+			} catch (SQLException e) {
+				e.printStackTrace();
+			}finally {
+				DatabaseUtil.closeResultSetAndStatement(sterileForeignRs);
+			}
 			//6.灭菌炉分组(动态查询)
 			List<String> sterialGroupList = getAllSterilerGroup();
 			if(CollectionUtils.isNotEmpty(sterialGroupList)){
 				for (String sterialGroup : sterialGroupList) {
 					String qutoaName = ReportConstant.CSSD_WORKLOAD_STERILERPOWEREDAMOUNT[0] + "_" + sterialGroup;
-					querySql = "select tedd.dateNum "+ ReportConstant.CSSD_WORKLOAD_REPORTDATE[0] +", " + 
+					String querySql = "select tedd.dateNum "+ ReportConstant.CSSD_WORKLOAD_REPORTDATE[0] +", " + 
 							" count(tmp.id) "+ qutoaName +" " + 
 							" from tempEveryDateData tedd  " + 
 							" left join (select sr.id , sr.endDate from SterilizationRecord sr,Sterilizer s where  " + 
@@ -246,18 +381,33 @@
 			}
 
 			//7.器械包发放数量
-			querySql = "select tedd.dateNum "+ ReportConstant.CSSD_WORKLOAD_REPORTDATE[0] +", " + 
-					" count(tmp.id) "+ ReportConstant.CSSD_WORKLOAD_TOUSSEINVOICEDAMOUNT[0] +
-					" from tempEveryDateData tedd  " + 
-					" left join (select i.id,i.sendTime from invoice i join tousseinstance t " +  
-					" on t.invoice_id = i.id and i.orgUnitCoding = '"+ departCoding +"') tmp on tmp.sendTime >=  tedd.beginTime and tmp.sendTime <= tedd.endTime " + 
-					" where tedd.beginTime >= "+ queryBeginDateExpress + " and tedd.endTime <= "+ queryEndDateExpress +
-					" and tedd.type='"+ queryReportDateType +"' " + 
-					" group by tedd.dateNum order by tedd.dateNum";
-			//key为报表结果的日期,value为各指标的值
-			result = countCssdWorkloadQuota(querySql , ReportConstant.CSSD_WORKLOAD_TOUSSEINVOICEDAMOUNT[0]);
-			mergeQuotaMapToDataList(result , datas);
-
+			// 清空过滤条件
+			params.tousseTypeAndPackageSizeSql = "";
+			params.includeTousses = true;
+			params.includeDisposableGoods = false;
+			params.extraSelectColumns = "," + iSendTime + " monthStr ";
+			params.isGroup = true;
+			params.extraGroupBy = iSendTime;
+			String tousseSendSql = String.format("select sum(tl.amount) amount,monthStr from ("
+						+dataIndex.getWorkAmountByPackageSQL("发货数量", params)
+						+ ") tl group by monthStr order by monthstr");
+			ResultSet tousseInvoiceSendRs = null;
+			try {
+				tousseInvoiceSendRs = objectDao.executeSql(tousseSendSql);
+				Map<String, Integer> tousseInvoiceSendRsResult = new HashMap<String, Integer>();
+				while (tousseInvoiceSendRs.next()) {
+					Integer tousseInvoiceSendAmount = tousseInvoiceSendRs.getInt("amount");
+					String month = tousseInvoiceSendRs.getString("monthStr");
+					tousseInvoiceSendRsResult.put(month, tousseInvoiceSendAmount);
+				}
+				Map<String,Map<String,Object>> tousseInvoiceSendMap = new TreeMap<String, Map<String,Object>>(new MapKeyComparator());
+				supplementMonthsWithoutData(dayList, yearMonthMap, tousseInvoiceSendRsResult, tousseInvoiceSendMap, ReportConstant.CSSD_WORKLOAD_TOUSSEINVOICEDAMOUNT[0]);
+				mergeQuotaMapToDataList(tousseInvoiceSendMap , datas);
+			} catch (SQLException e) {
+				e.printStackTrace();
+			}finally {
+				DatabaseUtil.closeResultSetAndStatement(tousseInvoiceSendRs);
+			}
 			// 是否添加腔镜器械，下收下送次数统计(中大六院)
 			boolean isYearWorkloadReportAddEndoscopic = CssdUtils.getSystemSetConfigByNameBool("isYearWorkloadReportAddEndoscopic");
 			if(isYearWorkloadReportAddEndoscopic){
@@ -268,7 +418,42 @@
 		}
 		return datas;
 	}
-	
+	/**
+	 * 给无数据的月份补0
+	 * @param yearMonthMap 年月map
+	 * @param resultSetMap resultSet的结果map
+	 * @param bigMap 外层大的map
+	 * @param columnName 列名
+	 */
+	private void supplementMonthsWithoutData(List<String> dayList, Map<String, String> yearMonthMap, Map<String, Integer> resultSetMap, Map<String,Map<String,Object>> bigMap, String columnName){
+		if(CollectionUtils.isNotEmpty(dayList)){
+			for (int i = 0; i < dayList.size(); i++) {
+				String dayStr = dayList.get(i);
+				Integer amount = null;
+				if(resultSetMap.containsKey(dayStr)){
+					amount = resultSetMap.get(dayStr);
+				}else{
+					amount = 0;
+				}
+				Map<String,Object> quotaMap = new HashMap<String, Object>();
+				quotaMap.put(columnName, amount);
+				bigMap.put(dayStr, quotaMap);
+			}
+		}else{
+			for (Entry<String, String> entry : yearMonthMap.entrySet()) {
+				String month = entry.getKey();
+				Integer amount = null;
+				if(resultSetMap.containsKey(month)){
+					amount = resultSetMap.get(month);
+				}else{
+					amount = 0;
+				}
+				Map<String,Object> quotaMap = new HashMap<String, Object>();
+				quotaMap.put(columnName, amount);
+				bigMap.put(entry.getValue(), quotaMap);
+			}
+		}
+	}
 	public Map<String,Map<String,Object>> countCssdWorkloadQuota(String querySql , String ... columnName){
 		ResultSet rs = null;
 		Map<String,Map<String,Object>> result = null;