Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringInspectMonthReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringInspectMonthReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/QualityMonitoringInspectMonthReportHelper.java (revision 35815) @@ -0,0 +1,711 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Collections; +import java.util.Comparator; +import java.util.HashMap; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.Map.Entry; + +import org.apache.commons.collections4.CollectionUtils; +import org.apache.commons.lang.StringUtils; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +import com.forgon.Constants; +import com.forgon.databaseadapter.service.DateQueryAdapter; +import com.forgon.directory.acegi.tools.AcegiHelper; +import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager; +import com.forgon.disinfectsystem.customform.formdefinition.service.FormDefinitionManager; +import com.forgon.disinfectsystem.entity.customform.formdefinition.FormDefinition; +import com.forgon.disinfectsystem.entity.customform.formdefinition.FormDefinitionItem; +import com.forgon.disinfectsystem.entity.customform.formdefinition.FormDefinitionItemOption; +import com.forgon.disinfectsystem.entity.qualitymonitoringmanager.qualitymonitoringconfig.QualityMonitoringDefinition; +import com.forgon.disinfectsystem.jasperreports.javabeansource.QualityMonitoringInspectBean; +import com.forgon.disinfectsystem.jasperreports.javabeansource.QualityMonitoringInspectMonthLineChartBean; +import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; +import com.forgon.disinfectsystem.qualitymonitoring.definition.service.QualityMonitoringDefinitionManager; +import com.forgon.tools.MathTools; +import com.forgon.tools.date.DateTools; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.db.InitDbConnection; +import com.forgon.tools.hibernate.ObjectDao; +import com.forgon.tools.util.SqlUtils; + +import edu.emory.mathcs.backport.java.util.Arrays; +/** + * + * 质量监测项统计月报 + * + */ +@Component +public class QualityMonitoringInspectMonthReportHelper { + @Autowired + protected DateQueryAdapter dateQueryAdapter; + @Autowired + protected ObjectDao objectDao; + @Autowired + protected InitDbConnection dbConnection; + @Autowired + private SupplyRoomConfigManager supplyRoomConfigManager; + @Autowired + private QualityMonitoringDefinitionManager qualityMonitoringDefinitionManager; + @Autowired + private FormDefinitionManager formDefinitionManager; + /** + * 获取质量监测统计项一个月内每天的统计 + * @param queryYear 查询年份 如： 2017 + * @param orgUnitCoding 查询供应室的编码 + * @param queryMonth 查询月份 如： 02 + * @param monitoringType 监测类型 + * @param inspectItem 监测细则 + * @param inspectDetail 监测结果 + * @param type 查询的表格类型 + * @param responsibilityType 责任环节 + * @return + */ + @SuppressWarnings("rawtypes") + public List getQualityMonitoringInspectDayOfMonthParamet(String queryYear,String orgUnitCoding,String queryMonth,String monitoringType,String inspectItem, + String inspectDetail,String type,String responsibilityType){ + List list = new ArrayList(); + if("firstSecondHalfForm".equals(type) || "compareLastForm".equals(type) || "quarterForm".equals(type)){ + // 上下半年对比，或者跟去年对比 或者季度对比的数据 + return getQualityMonitoringInspectCompareParamet(queryYear,orgUnitCoding,monitoringType,inspectItem,inspectDetail,type,responsibilityType); + } + String currentOrgUnitCode = AcegiHelper.getLoginUser().getCurrentOrgUnitCode(); + String fiOrgUnitCodingSql = ""; + if(!supplyRoomConfigManager.isFirstOrSecondSupplyRoomOrgUnit(currentOrgUnitCode)){ + fiOrgUnitCodingSql = SqlUtils.get_InSql_Extra("fi.orgUnitCoding", currentOrgUnitCode); + } + String sql = ""; + List resultNameList = new ArrayList(); + if(StringUtils.isNotBlank(responsibilityType)){ + resultNameList = getResultNameList(monitoringType,inspectItem,null,responsibilityType, null, null); + sql = getQualityMonitoringInspectDayByResponsibilityTypeSql(queryYear,queryMonth,monitoringType,responsibilityType,orgUnitCoding,fiOrgUnitCodingSql); + }else if(StringUtils.isNotBlank(orgUnitCoding) && StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(queryMonth) && StringUtils.isNotBlank(inspectItem)){ + String startTime = queryYear + "-" + queryMonth + "-01 00:00:00"; + String endTime = DateTools.getNextMonth(queryYear,queryMonth) + " 00:00:00"; + + if(StringUtils.isBlank(inspectDetail)){ + resultNameList = getResultNameList(monitoringType,inspectItem,null,null, startTime, endTime); + // 细则没有选，不需要查询监测结果，查询细则 + sql = "select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") +" monthstr,fdi.name,sum(qmd.amount) count " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,FormInstanceItem fiItem,FormDefinitionItem fdi,QualityMonitoringGoods qmd " + + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id and fiItem.formDefinitionItem_id = fdi.id " + + " and qmi.id=qmd.qualityMonitoringInstance_id " + + " and fd.formName = '" + inspectItem + "'" + + " and fd.formType = '" + monitoringType + "'" + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + + fiOrgUnitCodingSql + //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) + + " and (fiItem.id in (select formInstanceItem_id from FormInstanceOptionValue )) " + + " and fdi.isReportDisplay='是' and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startTime) + + "and "+dateQueryAdapter.dateAdapter(endTime) + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") + " ,fdi.name "; + }else{ + resultNameList = getResultNameList(monitoringType,inspectItem,inspectDetail,null, startTime, endTime); + // 查询细则的统计结果 + sql = "select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") +" monthstr,fiOption.answer,sum(qmd.amount) count " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,FormInstanceItem fiItem,FormDefinitionItem fdi,FormInstanceOptionValue fiOption,QualityMonitoringGoods qmd " + + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id and fiItem.formDefinitionItem_id = fdi.id and fiOption.formInstanceItem_id = fiItem.id " + + " and qmi.id=qmd.qualityMonitoringInstance_id " + + " and fd.formName = '" + inspectItem + "'" + + " and fd.formType = '" + monitoringType + "'" + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + + fiOrgUnitCodingSql + //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) + + " and fdi.isReportDisplay='是' and fdi.name = '" + inspectDetail + "'" + + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startTime) + + "and "+dateQueryAdapter.dateAdapter(endTime) + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") + " ,fiOption.answer "; + } + } + if(StringUtils.isNotBlank(sql)){ + ResultSet result = objectDao.executeSql(sql); + if("lineChart".equals(type) || "barChart".equals(type)){ + // 折线图柱状图的bean一样 + list = getQualityMonitoringInspectLineChartBean(resultNameList,result,queryYear,queryMonth); + }else{ + // 一个月中每日的表格数据 + boolean needSum = false; + if(StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(queryMonth) && "form".equals(type)){ + needSum = true; + } + list = getQualityMonitoringInspectBean(resultNameList,result,queryYear,queryMonth, needSum); + } + } + return list; + } + /** + * 获取质量监测统计项月报统计 + * @param queryYear + * @param orgUnitCoding 查询科室编码 + * @param monitoringType + * @param inspectItem + * @param inspectDetail + * @param type + * @return + */ + @SuppressWarnings("rawtypes") + public List getQualityMonitoringInspectMonthParamet(String queryYear,String orgUnitCoding,String monitoringType,String inspectItem,String inspectDetail,String type,String responsibilityType){ + List list = new ArrayList(); + if("firstSecondHalfForm".equals(type) || "compareLastForm".equals(type) || "quarterForm".equals(type)){ + // 上下半年对比，或者跟去年对比 或者季度对比的数据 + return getQualityMonitoringInspectCompareParamet(queryYear,orgUnitCoding,monitoringType,inspectItem,inspectDetail,type,responsibilityType); + } + String currentOrgUnitCode = AcegiHelper.getLoginUser().getCurrentOrgUnitCode(); + String fiOrgUnitCodingSql = ""; + if(!supplyRoomConfigManager.isFirstOrSecondSupplyRoomOrgUnit(currentOrgUnitCode)){ + fiOrgUnitCodingSql = SqlUtils.get_InSql_Extra("fi.orgUnitCoding", currentOrgUnitCode); + } + String sql = ""; + List resultNameList = new ArrayList(); + if(StringUtils.isNotBlank(responsibilityType)){ + resultNameList = getResultNameList(monitoringType,inspectItem,null,responsibilityType, null, null); + sql = getQualityMonitoringInspectMonthByResponsibilityTypeSql(queryYear,monitoringType,responsibilityType,orgUnitCoding,fiOrgUnitCodingSql); + }else if(StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(inspectItem)){ + String startYear = queryYear + "-01-01 00:00:00"; + String nextYear = DataIndex.getNextYear(queryYear) + " 00:00:00"; + if(StringUtils.isBlank(inspectDetail)){ + resultNameList = getResultNameList(monitoringType,inspectItem,null,null, startYear, nextYear); + // 细则没有选，不需要查询监测结果，查询细则 + sql = "select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") +" monthstr,fdi.name,sum(qmd.amount) count " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fd.id = fi.formDefinition_id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " + + " join FormInstanceItem fiItem on fi.id = fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id = fdi.id " + + " join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + //+ " left join FormInstanceOptionValue fiov on fiItem.id = fiov.formInstanceItem_id " + + " where fd.formName = '" + inspectItem + "'" + + " and fd.formType = '" + monitoringType + "'" + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) + + fiOrgUnitCodingSql + //+ " and fiov.id is not null " + + " and fdi.isReportDisplay='是' " + + " and (fiItem.id in (select formInstanceItem_id from FormInstanceOptionValue )) " + //+ " and fiov.id in (select id from FormInstanceOptionValue where formInstanceItem_id = fiItem.id) " + + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startYear) + + "and "+dateQueryAdapter.dateAdapter(nextYear) + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " ,fdi.name "; + }else{ + resultNameList = getResultNameList(monitoringType,inspectItem,inspectDetail,null, startYear, nextYear); + // 查询细则的统计结果 + sql = "select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") +" monthstr,fiOption.answer,sum(qmd.amount) count " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,FormInstanceItem fiItem,FormDefinitionItem fdi,FormInstanceOptionValue fiOption,QualityMonitoringGoods qmd " + + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id and fiItem.formDefinitionItem_id = fdi.id " + + " and fiOption.formInstanceItem_id = fiItem.id and qmi.id=qmd.qualityMonitoringInstance_id " + + " and fd.formName = '" + inspectItem + "'" + + " and fd.formType = '" + monitoringType + "'" + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) + + fiOrgUnitCodingSql + + " and fdi.name = '" + inspectDetail + "'" + + " and fdi.isReportDisplay='是' and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startYear) + + "and "+dateQueryAdapter.dateAdapter(nextYear) + + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " ,fiOption.answer "; + } + } + if(StringUtils.isNotBlank(sql)){ + ResultSet result = objectDao.executeSql(sql); + if("lineChart".equals(type) || "barChart".equals(type)){ + // 折线图柱状图的bean一样 + list = getQualityMonitoringInspectLineChartBean(resultNameList,result,queryYear,null); + }else{ + list = getQualityMonitoringInspectBean(resultNameList,result); + } + } + return list; + } + + /** + * 上下半年对比 或者跟去年对比 或者季度对比的数据 + * @param queryYear + * @param monitoringType + * @param inspectItem + * @param inspectDetail + * @param type + * @return + */ + @SuppressWarnings("rawtypes") + private List getQualityMonitoringInspectCompareParamet(String queryYear,String orgUnitCoding,String monitoringType,String inspectItem, + String inspectDetail,String type,String responsibilityType){ + List list = new ArrayList(); + Map map = new HashMap(); + try{ + if(StringUtils.isNotBlank(queryYear)){ + Map> startAndEndDays = null; + if ( "firstSecondHalfForm".equals(type) ) { + startAndEndDays = DataIndex.getStartAndEndDay(queryYear, "split", null); + }else if("compareLastForm".equals(type)){ + startAndEndDays = DataIndex.getStartAndEndDay(queryYear, "compareToLastYear", null); + }else if("quarterForm".equals(type)){ + startAndEndDays = DataIndex.getStartAndEndDay(queryYear, "quarterForm", null); + } + String currentOrgUnitCode = AcegiHelper.getLoginUser().getCurrentOrgUnitCode(); + String fiOrgUnitCodingSql = ""; + if(!supplyRoomConfigManager.isFirstOrSecondSupplyRoomOrgUnit(currentOrgUnitCode)){ + fiOrgUnitCodingSql = SqlUtils.get_InSql_Extra("fi.orgUnitCoding", currentOrgUnitCode); + } + for(Entry> entry : startAndEndDays.entrySet()){ + String key = entry.getKey(); + String startDay = null; + String endDay = null; + Map innerMap = entry.getValue(); + for(Entry entry2 : innerMap.entrySet()){ + startDay = entry2.getKey(); + endDay = entry2.getValue(); + } + List resultNameList = new ArrayList(); + String sql = ""; + if(StringUtils.isNotBlank(responsibilityType)){ + resultNameList = getResultNameList(monitoringType,inspectItem,null,responsibilityType, startDay, endDay); + sql = " select fd.formName,sum(qmg.amount) from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd" + + " ,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmg " + + " where qmi.id = fi.id and fi.formDefinition_id=fd.id and fd.id=qmdf.id " + + " and qmi.id=qmg.qualityMonitoringInstance_id " + + QualityMonitoringDefinition.getResponsibilityPartSql("qmdf.responsibilityPart", responsibilityType) + + "and fd.formType='" + monitoringType + "'" + + fiOrgUnitCodingSql + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) + + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startDay) + + " and "+dateQueryAdapter.dateAdapter(endDay) + + " group by fd.formName "; + }else if(StringUtils.isBlank(inspectDetail)){ + resultNameList = getResultNameList(monitoringType,inspectItem,null,null, startDay, endDay); + // 细则没有选，不需要查询监测结果，查询细则 + sql = "select " +" fdi.name,sum(qmg.amount) count " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd, QualityMonitoringDefinition qmdf,FormInstanceItem fiItem" + + " ,FormDefinitionItem fdi,QualityMonitoringGoods qmg " + + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id " + + " and fiItem.formDefinitionItem_id = fdi.id and qmi.id=qmg.qualityMonitoringInstance_id " + + " and fd.formName = '" + inspectItem + "'" + + " and fd.formType = '" + monitoringType + "'" + + fiOrgUnitCodingSql + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + //+ " and (fiItem.id in (select formInstanceItem_id from FormInstanceOptionValue)) " + + " and fdi.isReportDisplay='是' and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startDay) + + "and "+dateQueryAdapter.dateAdapter(endDay) + + " group by " + " fdi.name "; + }else{ + resultNameList = getResultNameList(monitoringType,inspectItem,inspectDetail,null, startDay, endDay); + // 查询细则的统计结果 + sql = "select " +" fiOption.answer,sum(qmg.amount) count " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,FormInstanceItem fiItem" + + " ,FormDefinitionItem fdi,FormInstanceOptionValue fiOption,QualityMonitoringGoods qmg " + + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id " + + " and fiItem.formDefinitionItem_id = fdi.id and fiOption.formInstanceItem_id = fiItem.id " + + " and qmi.id=qmg.qualityMonitoringInstance_id " + + " and fd.formName = '" + inspectItem + "'" + + " and fd.formType = '" + monitoringType + "'" + + fiOrgUnitCodingSql + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + + " and fdi.isReportDisplay='是' and fdi.name = '" + inspectDetail + "'" + + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startDay) + + "and "+dateQueryAdapter.dateAdapter(endDay) + + " group by "+ " fiOption.answer "; + } + ResultSet rs = objectDao.executeSql(sql); + // 根据需要统计的名字，初始化统计项 + for(String name : resultNameList){ + QualityMonitoringInspectBean bean = new QualityMonitoringInspectBean(); + bean.setName(name); + if(!map.containsKey(name)){ + map.put(name, bean); + } + } + try { + while (rs.next()) { + String name = rs.getString(1); + int amount = rs.getInt(2); + if(StringUtils.isNotBlank(name) && map.containsKey(name)){ + QualityMonitoringInspectBean bean = map.get(name); + if("上半年".equals(key) || "去年".equals(key)){ + // 上半年或者去年的数据是value1 + bean.setValue1(amount); + }else{ + bean.setValue2(amount); + } + // 设置季度数据的值 + if("一季度".equals(key)){ + bean.setQuarter1(amount); + }else if("二季度".equals(key)){ + bean.setQuarter2(amount); + }else if("三季度".equals(key)){ + bean.setQuarter3(amount); + }else if("四季度".equals(key)){ + bean.setQuarter4(amount); + } + map.put(name, bean); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + } + }catch(Exception e){ + e.printStackTrace(); + } + list = Arrays.asList(map.values().toArray()); + return list; + } + /** + * 通过监测项和细则，获取细则需要统计的结果名称的集合 + * @param inspectItem + * @param inspectDetail + * @param responseType 责任环节 + * @return + */ + private List getResultNameList(String monitoringType,String inspectItem,String inspectDetail,String responseType, String startDateStr, String endDateStr){ + List nameList = new ArrayList(); + if(StringUtils.isNotBlank(responseType)){ + // 责任环节为空时，会获取到全部环节 + List qmdList = qualityMonitoringDefinitionManager.loadFormDefinitionGroupByTupeAndResponsibilityPart(monitoringType,responseType); + if(CollectionUtils.isNotEmpty(qmdList)){ + for(QualityMonitoringDefinition qmd : qmdList){ + if(!nameList.contains(qmd.getFormName())){ + nameList.add(qmd.getFormName()); + } + } + } + return nameList; + } + List fds = formDefinitionManager.getFormDefinitionsByTypeAndName(monitoringType, inspectItem, startDateStr, endDateStr); + @SuppressWarnings("unchecked") + List filterFormDefinitionItem = objectDao.findByHql("select po.items from FormDefinition po where po.version=(select max(version) from FormDefinition where formName='"+ inspectItem +"' and formType='"+ monitoringType +"') "); + Set filterName = new HashSet(); + for (FormDefinitionItem fdi : filterFormDefinitionItem) { + if(!Constants.STR_YES.equals(fdi.getIsReportDisplay())){ + filterName.add(fdi.getName()); + } + } + if(StringUtils.isBlank(inspectDetail)){ + // 获取统计细则的项 + if( fds != null && CollectionUtils.isNotEmpty(fds)){ + for (FormDefinition fd : fds){ + for(FormDefinitionItem item : fd.getItems()){ + if(Constants.STR_YES.equals(item.getIsReportDisplay()) && !filterName.contains(item.getName())){ + if(!nameList.contains(item.getName())){ + nameList.add(item.getName()); + } + } + } + } + } + + }else{ + // 获取统计结果的项 + if( fds != null && CollectionUtils.isNotEmpty(fds)){ + for (FormDefinition fd : fds){ + for(FormDefinitionItem item : fd.getItems()){ + if(inspectDetail.equals(item.getName()) && !filterName.contains(item.getName())){ + String type = item.getType(); + // 只有单选或者多选，才加入统计 + if(FormDefinitionItem.TYPE_CHECK.equals(type) || FormDefinitionItem.TYPE_RADIO.equals(type)){ + for(FormDefinitionItemOption optoin : item.getOptions()){ + if(!nameList.contains(optoin.getValue())){ + nameList.add(optoin.getValue()); + } + } + } + } + } + } + } + } + return nameList; + } + /* + * 获取质量监测查询一个月中每天的sql，按责任环节查询 + */ + private String getQualityMonitoringInspectDayByResponsibilityTypeSql(String queryYear,String queryMonth,String type,String responsibilityType, String orgUnitCoding, String fiOrgUnitCodingSql){ + String sql = ""; + if(StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(type) && StringUtils.isNotBlank(responsibilityType)){ + String startTime = queryYear + "-" + queryMonth + "-01 00:00:00"; + String endTime = DateTools.getNextMonth(queryYear,queryMonth) + " 00:00:00"; + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") + " ,fd.formName,sum(qmg.amount) " + + "from QualityMonitoringInstance qmi,QualityMonitoringDefinition qmdf,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmd ,QualityMonitoringGoods qmg " + + " where qmdf.id=fd.id and qmi.id = fi.id and fi.formDefinition_id=fd.id and fd.id=qmd.id " + + " and qmi.id=qmg.qualityMonitoringInstance_id " + + QualityMonitoringDefinition.getResponsibilityPartSql("qmd.responsibilityPart", responsibilityType) + + " and fd.formType='" + type + "' " + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + + fiOrgUnitCodingSql + //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) + + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startTime) + + " and "+dateQueryAdapter.dateAdapter(endTime) + + " group by " +dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") + " ,fd.formName"; + } + return sql; + } + /** + * 获取折线图javaBean集合 + * @param nameList + * @param resultSet + * @return + */ + private List getQualityMonitoringInspectLineChartBean(List nameList,ResultSet resultSet,String queryYear,String queryMonth){ + List beanList = new ArrayList(); + if(nameList == null || nameList.size() < 1){ + return beanList; + } + Map map = new HashMap(); + try{ + if(resultSet != null){ + while (resultSet.next()) { + String time = resultSet.getString(1); + String type = resultSet.getString(2); + int amount = resultSet.getInt(3); + int time_i = 1; + if(StringUtils.isNumeric(time)){ + time_i = Integer.valueOf(time); + } + String key = type + "-" + time_i; + QualityMonitoringInspectMonthLineChartBean bean = new QualityMonitoringInspectMonthLineChartBean(); + bean.handleType(type); + bean.setAmount(amount); + if(StringUtils.isNotBlank(queryMonth)){ + bean.setMonth(queryYear + "-" + queryMonth + "-" + time_i); + }else{ + bean.setMonth(queryYear + "-" + time_i); + } + map.put(key, bean); + } + } + }catch(Exception e){ + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(resultSet); + } + // 根据需要统计的名字，给没有查到数据置0 + for(String name : nameList){ + String key = ""; + int count = 12; + if(StringUtils.isNotBlank(queryMonth)){ + // 如果查询一个月内每天的数据，count为每个月的天数 + count = DateTools.getDayCountOfMonth(queryYear,queryMonth); + } + for(int i = 1; i <= count; i++ ){ + key = name + "-" + i; + if(!map.containsKey(key)){ + QualityMonitoringInspectMonthLineChartBean bean = new QualityMonitoringInspectMonthLineChartBean(); + bean.handleType(name); + if(StringUtils.isNotBlank(queryMonth)){ + bean.setMonth(queryYear + "-" + queryMonth + "-" + i); + }else{ + bean.setMonth(queryYear + "-" + i); + } + bean.setAmount(0); + beanList.add(bean); + }else{ + QualityMonitoringInspectMonthLineChartBean bean = map.get(key); + beanList.add(bean); + } + } + } +// beanList = (List)Arrays.asList(map.values().toArray()); + return beanList; + } + /** + * 获取表格的javaBean集合 + * @param nameList + * @param resultSet + * @return + */ + private List getQualityMonitoringInspectBean(List nameList,ResultSet resultSet){ + List beanList = new ArrayList(); + if(nameList == null || nameList.size() < 1){ + return beanList; + } + Map map = new HashMap(); + // 根据需要统计的名字，初始化统计项 + for(String name : nameList){ + QualityMonitoringInspectBean bean = new QualityMonitoringInspectBean(); + bean.setName(name); + if(!map.containsKey(name)){ + map.put(name, bean); + } + } + try{ + if(resultSet != null){ + while (resultSet.next()) { + String month = resultSet.getString(1); + String name = resultSet.getString(2); + int amount = resultSet.getInt(3); + if(StringUtils.isNotBlank(name)){ + if(map.containsKey(name)){ + QualityMonitoringInspectBean bean = map.get(name); + setMonthValueToBean(bean,month,amount); + map.put(name,bean); + } + } + } + } + }catch(Exception e){ + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(resultSet); + } + return new ArrayList(map.values()); + } + /** + * 获取表格的javaBean集合,一个月中的每天统计的数据 + * @param nameList + * @param resultSet + * @param queryYear + * @param queryMonth + * @return + */ + private List getQualityMonitoringInspectBean(List nameList,ResultSet resultSet,String queryYear,String queryMonth, boolean needSum){ + List beanList = new ArrayList(); + if(nameList == null || nameList.size() < 1){ + return beanList; + } + if(StringUtils.isBlank(queryYear) || StringUtils.isBlank(queryMonth)){ + return beanList; + } + Map map = new HashMap(); + // 根据需要统计的名字，初始化统计项 + boolean isOracle = dbConnection.isOracle(); + int count = DateTools.getDayCountOfMonth(queryYear,queryMonth); + if(needSum){ + count = count + 1; + } + for(String name : nameList){ + for(int i = 1; i <= count; i++){ + if(needSum && i == count){ + QualityMonitoringInspectBean bean = new QualityMonitoringInspectBean(); + bean.setName(name); + bean.setQmDate("合计"); + String key = name + "合计"; + if(!map.containsKey(key)){ + map.put(key, bean); + } + continue; + } + QualityMonitoringInspectBean bean = new QualityMonitoringInspectBean(); + bean.setName(name); + String key = name + i; + if(i < 10){ + bean.setQmDate(queryYear+ "-" + queryMonth + "-" + "0" + i); + if(isOracle){//oracle返回月份带有0 如01 sqlserver返回不带0 + key = name + "0" + i; + } + }else{ + bean.setQmDate(queryYear+ "-" + queryMonth + "-" + i); + } + if(!map.containsKey(key)){ + map.put(key, bean); + } + } + } + try{ + if(resultSet != null){ + while (resultSet.next()) { + String day = resultSet.getString(1);//日期 + String name = resultSet.getString(2);//监测细测名称 + int amount = resultSet.getInt(3);//对应的数量 + String key = name + day; + if(StringUtils.isNotBlank(key)){ + if(map.containsKey(key)){ + QualityMonitoringInspectBean bean = map.get(key); + bean.setAmount(amount); + map.put(key,bean); + } + if(needSum){ + String key2 = name + "合计"; + QualityMonitoringInspectBean beanSUm = map.get(key2); + beanSUm.setAmount(MathTools.add(beanSUm.getAmount(), amount).intValue()); + map.put(key2,beanSUm); + } + } + } + } + }catch(Exception e){ + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(resultSet); + } + beanList = new ArrayList(map.values()); + Collections.sort(beanList, + new Comparator() { + @Override + public int compare(QualityMonitoringInspectBean o1, + QualityMonitoringInspectBean o2) { + String str1 = o1.getQmDate(); + String str2 = o2.getQmDate(); + return str1.compareTo(str2); + } + }); + return beanList; + } + /** + * 设置质量监测月统计的bean + * @param bean + * @param month + * @param amount + */ + private void setMonthValueToBean(QualityMonitoringInspectBean bean,String month,int amount){ + if(bean == null || StringUtils.isBlank(month) ){ + return ; + } + if(StringUtils.isNumeric(month)){ + int mon = Integer.valueOf(month); + if(mon == 1){ + bean.setJanuaryBHGL(amount); + }else if(mon == 2){ + bean.setFebruaryBHGL(amount); + }else if(mon == 3){ + bean.setMarchBHGL(amount); + }else if(mon == 4){ + bean.setAprilBHGL(amount); + }else if(mon == 5){ + bean.setMayBHGL(amount); + }else if(mon == 6){ + bean.setJuneBHGL(amount); + }else if(mon == 7){ + bean.setJulyBHGL(amount); + }else if(mon == 8){ + bean.setAugustBHGL(amount); + }else if(mon == 9){ + bean.setSeptemberBHGL(amount); + }else if(mon == 10){ + bean.setOctoberBHGL(amount); + }else if(mon == 11){ + bean.setNovemberBHGL(amount); + }else if(mon == 12){ + bean.setDecemberBHGL(amount); + } + } + } + /* + * 获取质量监测一年中每个月的sql，按责任环节查询 + */ + private String getQualityMonitoringInspectMonthByResponsibilityTypeSql(String queryYear,String type,String responsibilityType, String orgUnitCoding, String fiOrgUnitCodingSql){ + String sql = ""; + if(StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(type) && StringUtils.isNotBlank(responsibilityType)){ + String startYear = queryYear + "-01-01 00:00:00"; + String nextYear = DataIndex.getNextYear(queryYear) + " 00:00:00"; + sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " ,fd.formName,sum(qmg.amount) " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd, QualityMonitoringDefinition qmdf,QualityMonitoringDefinition qmd ,QualityMonitoringGoods qmg " + + " where qmdf.id=fd.id and qmi.id = fi.id and fi.formDefinition_id=fd.id and fd.id=qmd.id " + + " and qmi.id=qmg.qualityMonitoringInstance_id " + + QualityMonitoringDefinition.getResponsibilityPartSql("qmd.responsibilityPart", responsibilityType) + + " and fd.formType='" + type + "' " + + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) + + fiOrgUnitCodingSql + //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) + + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startYear) + + " and "+dateQueryAdapter.dateAdapter(nextYear) + + " group by " +dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " ,fd.formName"; + } + return sql; + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35814 -r35815 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35814) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35815) @@ -193,6 +193,7 @@ import com.forgon.disinfectsystem.jasperreports.util.PackingUnqualifieReportHelper; import com.forgon.disinfectsystem.jasperreports.util.QualityControlOfClinicHelper; import com.forgon.disinfectsystem.jasperreports.util.QualityMonitoringHelper; +import com.forgon.disinfectsystem.jasperreports.util.QualityMonitoringInspectMonthReportHelper; import com.forgon.disinfectsystem.jasperreports.util.QualityMonitoringReportTaskGroupReportHelper; import com.forgon.disinfectsystem.jasperreports.util.RealTimeBulletinBoardWorkloadHelper; import com.forgon.disinfectsystem.jasperreports.util.ReportSqlUtil; @@ -381,6 +382,8 @@ private GoodsAmountSummaryByGradeHelper goodsAmountSummaryByGradeHelper; @Autowired private QualityControlOfClinicHelper qualityControlOfClinicHelper; + @Autowired + private QualityMonitoringInspectMonthReportHelper qualityMonitoringInspectMonthReportHelper; public void setPackingManager(PackingManager packingManager) { this.packingManager = packingManager; } @@ -6598,637 +6601,19 @@ } // 获取质量监测统计项月报统计 + @SuppressWarnings("rawtypes") @Override public List getQualityMonitoringInspectMonthParamet(String queryYear,String orgUnitCoding,String monitoringType,String inspectItem,String inspectDetail,String type,String responsibilityType){ - List list = new ArrayList(); - if("firstSecondHalfForm".equals(type) || "compareLastForm".equals(type) || "quarterForm".equals(type)){ - // 上下半年对比，或者跟去年对比 或者季度对比的数据 - return getQualityMonitoringInspectCompareParamet(queryYear,orgUnitCoding,monitoringType,inspectItem,inspectDetail,type,responsibilityType); - } - String currentOrgUnitCode = AcegiHelper.getLoginUser().getCurrentOrgUnitCode(); - String fiOrgUnitCodingSql = ""; - if(!supplyRoomConfigManager.isFirstOrSecondSupplyRoomOrgUnit(currentOrgUnitCode)){ - fiOrgUnitCodingSql = SqlUtils.get_InSql_Extra("fi.orgUnitCoding", currentOrgUnitCode); - } - String sql = ""; - List resultNameList = new ArrayList(); - if(StringUtils.isNotBlank(responsibilityType)){ - resultNameList = getResultNameList(monitoringType,inspectItem,null,responsibilityType, null, null); - sql = getQualityMonitoringInspectMonthByResponsibilityTypeSql(queryYear,monitoringType,responsibilityType,orgUnitCoding,fiOrgUnitCodingSql); - }else if(StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(inspectItem)){ - String startYear = queryYear + "-01-01 00:00:00"; - String nextYear = DataIndex.getNextYear(queryYear) + " 00:00:00"; - if(StringUtils.isBlank(inspectDetail)){ - resultNameList = getResultNameList(monitoringType,inspectItem,null,null, startYear, nextYear); - // 细则没有选，不需要查询监测结果，查询细则 - sql = "select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") +" monthstr,fdi.name,sum(qmd.amount) count " - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fd.id = fi.formDefinition_id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + " join FormInstanceItem fiItem on fi.id = fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id = fdi.id " - + " join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " - //+ " left join FormInstanceOptionValue fiov on fiItem.id = fiov.formInstanceItem_id " - + " where fd.formName = '" + inspectItem + "'" - + " and fd.formType = '" + monitoringType + "'" - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) - + fiOrgUnitCodingSql - //+ " and fiov.id is not null " - + " and fdi.isReportDisplay='是' " - + " and (fiItem.id in (select formInstanceItem_id from FormInstanceOptionValue )) " - //+ " and fiov.id in (select id from FormInstanceOptionValue where formInstanceItem_id = fiItem.id) " - + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startYear) - + "and "+dateQueryAdapter.dateAdapter(nextYear) - + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " ,fdi.name "; - }else{ - resultNameList = getResultNameList(monitoringType,inspectItem,inspectDetail,null, startYear, nextYear); - // 查询细则的统计结果 - sql = "select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") +" monthstr,fiOption.answer,sum(qmd.amount) count " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,FormInstanceItem fiItem,FormDefinitionItem fdi,FormInstanceOptionValue fiOption,QualityMonitoringGoods qmd " - + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id and fiItem.formDefinitionItem_id = fdi.id " - + " and fiOption.formInstanceItem_id = fiItem.id and qmi.id=qmd.qualityMonitoringInstance_id " - + " and fd.formName = '" + inspectItem + "'" - + " and fd.formType = '" + monitoringType + "'" - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) - + fiOrgUnitCodingSql - + " and fdi.name = '" + inspectDetail + "'" - + " and fdi.isReportDisplay='是' and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startYear) - + "and "+dateQueryAdapter.dateAdapter(nextYear) - + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " ,fiOption.answer "; - } - } - if(StringUtils.isNotBlank(sql)){ - ResultSet result = objectDao.executeSql(sql); - if("lineChart".equals(type) || "barChart".equals(type)){ - // 折线图柱状图的bean一样 - list = getQualityMonitoringInspectLineChartBean(resultNameList,result,queryYear,null); - }else{ - list = getQualityMonitoringInspectBean(resultNameList,result); - } - } - return list; + return qualityMonitoringInspectMonthReportHelper.getQualityMonitoringInspectMonthParamet(queryYear, orgUnitCoding, monitoringType, inspectItem, inspectDetail, type, responsibilityType); } + @SuppressWarnings("rawtypes") @Override public List getQualityMonitoringInspectDayOfMonthParamet(String queryYear,String orgUnitCoding,String queryMonth,String monitoringType,String inspectItem, String inspectDetail,String type,String responsibilityType){ - List list = new ArrayList(); - if("firstSecondHalfForm".equals(type) || "compareLastForm".equals(type) || "quarterForm".equals(type)){ - // 上下半年对比，或者跟去年对比 或者季度对比的数据 - return getQualityMonitoringInspectCompareParamet(queryYear,orgUnitCoding,monitoringType,inspectItem,inspectDetail,type,responsibilityType); - } - String currentOrgUnitCode = AcegiHelper.getLoginUser().getCurrentOrgUnitCode(); - String fiOrgUnitCodingSql = ""; - if(!supplyRoomConfigManager.isFirstOrSecondSupplyRoomOrgUnit(currentOrgUnitCode)){ - fiOrgUnitCodingSql = SqlUtils.get_InSql_Extra("fi.orgUnitCoding", currentOrgUnitCode); - } - String sql = ""; - List resultNameList = new ArrayList(); - if(StringUtils.isNotBlank(responsibilityType)){ - resultNameList = getResultNameList(monitoringType,inspectItem,null,responsibilityType, null, null); - sql = getQualityMonitoringInspectDayByResponsibilityTypeSql(queryYear,queryMonth,monitoringType,responsibilityType,orgUnitCoding,fiOrgUnitCodingSql); - }else if(StringUtils.isNotBlank(orgUnitCoding) && StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(queryMonth) && StringUtils.isNotBlank(inspectItem)){ - String startTime = queryYear + "-" + queryMonth + "-01 00:00:00"; - String endTime = DateTools.getNextMonth(queryYear,queryMonth) + " 00:00:00"; - - if(StringUtils.isBlank(inspectDetail)){ - resultNameList = getResultNameList(monitoringType,inspectItem,null,null, startTime, endTime); - // 细则没有选，不需要查询监测结果，查询细则 - sql = "select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") +" monthstr,fdi.name,sum(qmd.amount) count " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,FormInstanceItem fiItem,FormDefinitionItem fdi,QualityMonitoringGoods qmd " - + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id and fiItem.formDefinitionItem_id = fdi.id " - + " and qmi.id=qmd.qualityMonitoringInstance_id " - + " and fd.formName = '" + inspectItem + "'" - + " and fd.formType = '" + monitoringType + "'" - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - + fiOrgUnitCodingSql - //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) - + " and (fiItem.id in (select formInstanceItem_id from FormInstanceOptionValue )) " - + " and fdi.isReportDisplay='是' and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startTime) - + "and "+dateQueryAdapter.dateAdapter(endTime) - + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") + " ,fdi.name "; - }else{ - resultNameList = getResultNameList(monitoringType,inspectItem,inspectDetail,null, startTime, endTime); - // 查询细则的统计结果 - sql = "select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") +" monthstr,fiOption.answer,sum(qmd.amount) count " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,FormInstanceItem fiItem,FormDefinitionItem fdi,FormInstanceOptionValue fiOption,QualityMonitoringGoods qmd " - + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id and fiItem.formDefinitionItem_id = fdi.id and fiOption.formInstanceItem_id = fiItem.id " - + " and qmi.id=qmd.qualityMonitoringInstance_id " - + " and fd.formName = '" + inspectItem + "'" - + " and fd.formType = '" + monitoringType + "'" - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - + fiOrgUnitCodingSql - //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) - + " and fdi.isReportDisplay='是' and fdi.name = '" + inspectDetail + "'" - + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startTime) - + "and "+dateQueryAdapter.dateAdapter(endTime) - + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") + " ,fiOption.answer "; - } - } - if(StringUtils.isNotBlank(sql)){ - ResultSet result = objectDao.executeSql(sql); - if("lineChart".equals(type) || "barChart".equals(type)){ - // 折线图柱状图的bean一样 - list = getQualityMonitoringInspectLineChartBean(resultNameList,result,queryYear,queryMonth); - }else{ - // 一个月中每日的表格数据 - boolean needSum = false; - if(StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(queryMonth) && "form".equals(type)){ - needSum = true; - } - list = getQualityMonitoringInspectBean(resultNameList,result,queryYear,queryMonth, needSum); - } - } - return list; + return qualityMonitoringInspectMonthReportHelper.getQualityMonitoringInspectDayOfMonthParamet(queryYear, orgUnitCoding, queryMonth, monitoringType, inspectItem, inspectDetail, type, responsibilityType); } /** - * 上下半年对比 或者跟去年对比 或者季度对比的数据 - * @param queryYear - * @param monitoringType - * @param inspectItem - * @param inspectDetail - * @param type - * @return - */ - public List getQualityMonitoringInspectCompareParamet(String queryYear,String orgUnitCoding,String monitoringType,String inspectItem, - String inspectDetail,String type,String responsibilityType){ - List list = new ArrayList(); - Map map = new HashMap(); - try{ - if(StringUtils.isNotBlank(queryYear)){ - Map> startAndEndDays = null; - if ( "firstSecondHalfForm".equals(type) ) { - startAndEndDays = DataIndex.getStartAndEndDay(queryYear, "split", null); - }else if("compareLastForm".equals(type)){ - startAndEndDays = DataIndex.getStartAndEndDay(queryYear, "compareToLastYear", null); - }else if("quarterForm".equals(type)){ - startAndEndDays = DataIndex.getStartAndEndDay(queryYear, "quarterForm", null); - } - String currentOrgUnitCode = AcegiHelper.getLoginUser().getCurrentOrgUnitCode(); - String fiOrgUnitCodingSql = ""; - if(!supplyRoomConfigManager.isFirstOrSecondSupplyRoomOrgUnit(currentOrgUnitCode)){ - fiOrgUnitCodingSql = SqlUtils.get_InSql_Extra("fi.orgUnitCoding", currentOrgUnitCode); - } - for(Entry> entry : startAndEndDays.entrySet()){ - String key = entry.getKey(); - String startDay = null; - String endDay = null; - Map innerMap = entry.getValue(); - for(Entry entry2 : innerMap.entrySet()){ - startDay = entry2.getKey(); - endDay = entry2.getValue(); - } - List resultNameList = new ArrayList(); - String sql = ""; - if(StringUtils.isNotBlank(responsibilityType)){ - resultNameList = getResultNameList(monitoringType,inspectItem,null,responsibilityType, startDay, endDay); - sql = " select fd.formName,sum(qmg.amount) from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd" - + " ,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmg " - + " where qmi.id = fi.id and fi.formDefinition_id=fd.id and fd.id=qmdf.id " - + " and qmi.id=qmg.qualityMonitoringInstance_id " - + QualityMonitoringDefinition.getResponsibilityPartSql("qmdf.responsibilityPart", responsibilityType) - + "and fd.formType='" + monitoringType + "'" - + fiOrgUnitCodingSql - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) - + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startDay) - + " and "+dateQueryAdapter.dateAdapter(endDay) - + " group by fd.formName "; - }else if(StringUtils.isBlank(inspectDetail)){ - resultNameList = getResultNameList(monitoringType,inspectItem,null,null, startDay, endDay); - // 细则没有选，不需要查询监测结果，查询细则 - sql = "select " +" fdi.name,sum(qmg.amount) count " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd, QualityMonitoringDefinition qmdf,FormInstanceItem fiItem" - + " ,FormDefinitionItem fdi,QualityMonitoringGoods qmg " - + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id " - + " and fiItem.formDefinitionItem_id = fdi.id and qmi.id=qmg.qualityMonitoringInstance_id " - + " and fd.formName = '" + inspectItem + "'" - + " and fd.formType = '" + monitoringType + "'" - + fiOrgUnitCodingSql - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - //+ " and (fiItem.id in (select formInstanceItem_id from FormInstanceOptionValue)) " - + " and fdi.isReportDisplay='是' and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startDay) - + "and "+dateQueryAdapter.dateAdapter(endDay) - + " group by " + " fdi.name "; - }else{ - resultNameList = getResultNameList(monitoringType,inspectItem,inspectDetail,null, startDay, endDay); - // 查询细则的统计结果 - sql = "select " +" fiOption.answer,sum(qmg.amount) count " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,FormInstanceItem fiItem" - + " ,FormDefinitionItem fdi,FormInstanceOptionValue fiOption,QualityMonitoringGoods qmg " - + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and fi.id = fiItem.formInstance_id " - + " and fiItem.formDefinitionItem_id = fdi.id and fiOption.formInstanceItem_id = fiItem.id " - + " and qmi.id=qmg.qualityMonitoringInstance_id " - + " and fd.formName = '" + inspectItem + "'" - + " and fd.formType = '" + monitoringType + "'" - + fiOrgUnitCodingSql - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - + " and fdi.isReportDisplay='是' and fdi.name = '" + inspectDetail + "'" - + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startDay) - + "and "+dateQueryAdapter.dateAdapter(endDay) - + " group by "+ " fiOption.answer "; - } - ResultSet rs = objectDao.executeSql(sql); - // 根据需要统计的名字，初始化统计项 - for(String name : resultNameList){ - QualityMonitoringInspectBean bean = new QualityMonitoringInspectBean(); - bean.setName(name); - if(!map.containsKey(name)){ - map.put(name, bean); - } - } - try { - while (rs.next()) { - String name = rs.getString(1); - int amount = rs.getInt(2); - if(StringUtils.isNotBlank(name) && map.containsKey(name)){ - QualityMonitoringInspectBean bean = map.get(name); - if("上半年".equals(key) || "去年".equals(key)){ - // 上半年或者去年的数据是value1 - bean.setValue1(amount); - }else{ - bean.setValue2(amount); - } - // 设置季度数据的值 - if("一季度".equals(key)){ - bean.setQuarter1(amount); - }else if("二季度".equals(key)){ - bean.setQuarter2(amount); - }else if("三季度".equals(key)){ - bean.setQuarter3(amount); - }else if("四季度".equals(key)){ - bean.setQuarter4(amount); - } - map.put(name, bean); - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - } - }catch(Exception e){ - e.printStackTrace(); - } - list = Arrays.asList(map.values().toArray()); - return list; - } - /* - * 获取质量监测一年中每个月的sql，按责任环节查询 - */ - private String getQualityMonitoringInspectMonthByResponsibilityTypeSql(String queryYear,String type,String responsibilityType, String orgUnitCoding, String fiOrgUnitCodingSql){ - String sql = ""; - if(StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(type) && StringUtils.isNotBlank(responsibilityType)){ - String startYear = queryYear + "-01-01 00:00:00"; - String nextYear = DataIndex.getNextYear(queryYear) + " 00:00:00"; - sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " ,fd.formName,sum(qmg.amount) " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd, QualityMonitoringDefinition qmdf,QualityMonitoringDefinition qmd ,QualityMonitoringGoods qmg " - + " where qmdf.id=fd.id and qmi.id = fi.id and fi.formDefinition_id=fd.id and fd.id=qmd.id " - + " and qmi.id=qmg.qualityMonitoringInstance_id " - + QualityMonitoringDefinition.getResponsibilityPartSql("qmd.responsibilityPart", responsibilityType) - + " and fd.formType='" + type + "' " - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - + fiOrgUnitCodingSql - //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) - + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startYear) - + " and "+dateQueryAdapter.dateAdapter(nextYear) - + " group by " +dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " ,fd.formName"; - } - return sql; - } - /* - * 获取质量监测查询一个月中每天的sql，按责任环节查询 - */ - private String getQualityMonitoringInspectDayByResponsibilityTypeSql(String queryYear,String queryMonth,String type,String responsibilityType, String orgUnitCoding, String fiOrgUnitCodingSql){ - String sql = ""; - if(StringUtils.isNotBlank(queryYear) && StringUtils.isNotBlank(type) && StringUtils.isNotBlank(responsibilityType)){ - String startTime = queryYear + "-" + queryMonth + "-01 00:00:00"; - String endTime = DateTools.getNextMonth(queryYear,queryMonth) + " 00:00:00"; - sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") + " ,fd.formName,sum(qmg.amount) " - + "from QualityMonitoringInstance qmi,QualityMonitoringDefinition qmdf,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmd ,QualityMonitoringGoods qmg " - + " where qmdf.id=fd.id and qmi.id = fi.id and fi.formDefinition_id=fd.id and fd.id=qmd.id " - + " and qmi.id=qmg.qualityMonitoringInstance_id " - + QualityMonitoringDefinition.getResponsibilityPartSql("qmd.responsibilityPart", responsibilityType) - + " and fd.formType='" + type + "' " - + SqlUtils.getHandleDepartCodingOfQualityMonitoringDefinitionSql(orgUnitCoding) - + fiOrgUnitCodingSql - //+ SqlUtils.get_InSql_Extra("fi.orgUnitCoding", orgUnitCoding) - + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startTime) - + " and "+dateQueryAdapter.dateAdapter(endTime) - + " group by " +dateQueryAdapter.dateConverAdapter3("qmi.dateTime","dd") + " ,fd.formName"; - } - return sql; - } - - /** - * 获取表格的javaBean集合 - * @param nameList - * @param resultSet - * @return - */ - private List getQualityMonitoringInspectBean(List nameList,ResultSet resultSet){ - List beanList = new ArrayList(); - if(nameList == null || nameList.size() < 1){ - return beanList; - } - Map map = new HashMap(); - // 根据需要统计的名字，初始化统计项 - for(String name : nameList){ - QualityMonitoringInspectBean bean = new QualityMonitoringInspectBean(); - bean.setName(name); - if(!map.containsKey(name)){ - map.put(name, bean); - } - } - try{ - if(resultSet != null){ - while (resultSet.next()) { - String month = resultSet.getString(1); - String name = resultSet.getString(2); - int amount = resultSet.getInt(3); - if(StringUtils.isNotBlank(name)){ - if(map.containsKey(name)){ - QualityMonitoringInspectBean bean = map.get(name); - setMonthValueToBean(bean,month,amount); - map.put(name,bean); - } - } - } - } - }catch(Exception e){ - e.printStackTrace(); - }finally{ - DatabaseUtil.closeResultSetAndStatement(resultSet); - } - beanList = Arrays.asList(map.values().toArray()); - return beanList; - } - /** - * 获取表格的javaBean集合,一个月中的每天统计的数据 - * @param nameList - * @param resultSet - * @param queryYear - * @param queryMonth - * @return - */ - private List getQualityMonitoringInspectBean(List nameList,ResultSet resultSet,String queryYear,String queryMonth, boolean needSum){ - List beanList = new ArrayList(); - if(nameList == null || nameList.size() < 1){ - return beanList; - } - if(StringUtils.isBlank(queryYear) || StringUtils.isBlank(queryMonth)){ - return beanList; - } - Map map = new HashMap(); - // 根据需要统计的名字，初始化统计项 - boolean isOracle = dbConnection.isOracle(); - int count = DateTools.getDayCountOfMonth(queryYear,queryMonth); - Map amountMap = new HashMap(); - if(needSum){ - count = count + 1; - } - for(String name : nameList){ - for(int i = 1; i <= count; i++){ - if(needSum && i == count){ - QualityMonitoringInspectBean bean = new QualityMonitoringInspectBean(); - bean.setName(name); - bean.setQmDate("合计"); - String key = name + "合计"; - if(!map.containsKey(key)){ - map.put(key, bean); - } - continue; - } - QualityMonitoringInspectBean bean = new QualityMonitoringInspectBean(); - bean.setName(name); - String key = name + i; - if(i < 10){ - bean.setQmDate(queryYear+ "-" + queryMonth + "-" + "0" + i); - if(isOracle){//oracle返回月份带有0 如01 sqlserver返回不带0 - key = name + "0" + i; - } - }else{ - bean.setQmDate(queryYear+ "-" + queryMonth + "-" + i); - } - if(!map.containsKey(key)){ - map.put(key, bean); - } - } - } - try{ - if(resultSet != null){ - while (resultSet.next()) { - String day = resultSet.getString(1);//日期 - String name = resultSet.getString(2);//监测细测名称 - int amount = resultSet.getInt(3);//对应的数量 - String key = name + day; - if(StringUtils.isNotBlank(key)){ - if(map.containsKey(key)){ - QualityMonitoringInspectBean bean = map.get(key); - bean.setAmount(amount); - map.put(key,bean); - } - if(needSum){ - String key2 = name + "合计"; - QualityMonitoringInspectBean beanSUm = map.get(key2); - beanSUm.setAmount(MathTools.add(beanSUm.getAmount(), amount).intValue()); - map.put(key2,beanSUm); - } - } - } - } - }catch(Exception e){ - e.printStackTrace(); - }finally{ - DatabaseUtil.closeResultSetAndStatement(resultSet); - } - beanList = Arrays.asList(map.values().toArray()); - Collections.sort(beanList, - new Comparator() { - @Override - public int compare(QualityMonitoringInspectBean o1, - QualityMonitoringInspectBean o2) { - String str1 = o1.getQmDate(); - String str2 = o2.getQmDate(); - return str1.compareTo(str2); - } - }); - return beanList; - } - /** - * 获取折线图javaBean集合 - * @param nameList - * @param resultSet - * @return - */ - private List getQualityMonitoringInspectLineChartBean(List nameList,ResultSet resultSet,String queryYear,String queryMonth){ - List beanList = new ArrayList(); - if(nameList == null || nameList.size() < 1){ - return beanList; - } - Map map = new HashMap(); - try{ - if(resultSet != null){ - while (resultSet.next()) { - String time = resultSet.getString(1); - String type = resultSet.getString(2); - int amount = resultSet.getInt(3); - int time_i = 1; - if(StringUtils.isNumeric(time)){ - time_i = Integer.valueOf(time); - } - String key = type + "-" + time_i; - QualityMonitoringInspectMonthLineChartBean bean = new QualityMonitoringInspectMonthLineChartBean(); - bean.handleType(type); - bean.setAmount(amount); - if(StringUtils.isNotBlank(queryMonth)){ - bean.setMonth(queryYear + "-" + queryMonth + "-" + time_i); - }else{ - bean.setMonth(queryYear + "-" + time_i); - } - map.put(key, bean); - } - } - }catch(Exception e){ - e.printStackTrace(); - }finally{ - DatabaseUtil.closeResultSetAndStatement(resultSet); - } - // 根据需要统计的名字，给没有查到数据置0 - for(String name : nameList){ - String key = ""; - int count = 12; - if(StringUtils.isNotBlank(queryMonth)){ - // 如果查询一个月内每天的数据，count为每个月的天数 - count = DateTools.getDayCountOfMonth(queryYear,queryMonth); - } - for(int i = 1; i <= count; i++ ){ - key = name + "-" + i; - if(!map.containsKey(key)){ - QualityMonitoringInspectMonthLineChartBean bean = new QualityMonitoringInspectMonthLineChartBean(); - bean.handleType(name); - if(StringUtils.isNotBlank(queryMonth)){ - bean.setMonth(queryYear + "-" + queryMonth + "-" + i); - }else{ - bean.setMonth(queryYear + "-" + i); - } - bean.setAmount(0); - beanList.add(bean); - }else{ - QualityMonitoringInspectMonthLineChartBean bean = map.get(key); - beanList.add(bean); - } - } - } -// beanList = (List)Arrays.asList(map.values().toArray()); - return beanList; - } - /** - * 设置质量监测月统计的bean - * @param bean - * @param month - * @param amount - */ - private void setMonthValueToBean(QualityMonitoringInspectBean bean,String month,int amount){ - if(bean == null || StringUtils.isBlank(month) ){ - return ; - } - if(StringUtils.isNumeric(month)){ - int mon = Integer.valueOf(month); - if(mon == 1){ - bean.setJanuaryBHGL(amount); - }else if(mon == 2){ - bean.setFebruaryBHGL(amount); - }else if(mon == 3){ - bean.setMarchBHGL(amount); - }else if(mon == 4){ - bean.setAprilBHGL(amount); - }else if(mon == 5){ - bean.setMayBHGL(amount); - }else if(mon == 6){ - bean.setJuneBHGL(amount); - }else if(mon == 7){ - bean.setJulyBHGL(amount); - }else if(mon == 8){ - bean.setAugustBHGL(amount); - }else if(mon == 9){ - bean.setSeptemberBHGL(amount); - }else if(mon == 10){ - bean.setOctoberBHGL(amount); - }else if(mon == 11){ - bean.setNovemberBHGL(amount); - }else if(mon == 12){ - bean.setDecemberBHGL(amount); - } - } - } - /** - * 通过监测项和细则，获取细则需要统计的结果名称的集合 - * @param inspectItem - * @param inspectDetail - * @param responseType 责任环节 - * @return - */ - private List getResultNameList(String monitoringType,String inspectItem,String inspectDetail,String responseType, String startDateStr, String endDateStr){ - List nameList = new ArrayList(); - if(StringUtils.isNotBlank(responseType)){ - // 责任环节为空时，会获取到全部环节 - List qmdList = qualityMonitoringDefinitionManager.loadFormDefinitionGroupByTupeAndResponsibilityPart(monitoringType,responseType); - if(CollectionUtils.isNotEmpty(qmdList)){ - for(QualityMonitoringDefinition qmd : qmdList){ - if(!nameList.contains(qmd.getFormName())){ - nameList.add(qmd.getFormName()); - } - } - } - return nameList; - } - List fds = formDefinitionManager.getFormDefinitionsByTypeAndName(monitoringType, inspectItem, startDateStr, endDateStr); - List filterFormDefinitionItem = objectDao.findByHql("select po.items from FormDefinition po where po.version=(select max(version) from FormDefinition where formName='"+ inspectItem +"' and formType='"+ monitoringType +"') "); - Set filterName = new HashSet(); - for (FormDefinitionItem fdi : filterFormDefinitionItem) { - if(!Constants.STR_YES.equals(fdi.getIsReportDisplay())){ - filterName.add(fdi.getName()); - } - } - if(StringUtils.isBlank(inspectDetail)){ - // 获取统计细则的项 - if( fds != null && CollectionUtils.isNotEmpty(fds)){ - for (FormDefinition fd : fds){ - for(FormDefinitionItem item : fd.getItems()){ - if(Constants.STR_YES.equals(item.getIsReportDisplay()) && !filterName.contains(item.getName())){ - if(!nameList.contains(item.getName())){ - nameList.add(item.getName()); - } - } - } - } - } - - }else{ - // 获取统计结果的项 - if( fds != null && CollectionUtils.isNotEmpty(fds)){ - for (FormDefinition fd : fds){ - for(FormDefinitionItem item : fd.getItems()){ - if(inspectDetail.equals(item.getName()) && !filterName.contains(item.getName())){ - String type = item.getType(); - // 只有单选或者多选，才加入统计 - if(FormDefinitionItem.TYPE_CHECK.equals(type) || FormDefinitionItem.TYPE_RADIO.equals(type)){ - for(FormDefinitionItemOption optoin : item.getOptions()){ - if(!nameList.contains(optoin.getValue())){ - nameList.add(optoin.getValue()); - } - } - } - } - } - } - } - } - return nameList; - } - /** * 获取丢失报损月报数据 */ @Override