Index: forgon-core/src/main/java/com/forgon/databaseadapter/service/DateQueryAdapter.java =================================================================== diff -u -r17243 -r17308 --- forgon-core/src/main/java/com/forgon/databaseadapter/service/DateQueryAdapter.java (.../DateQueryAdapter.java) (revision 17243) +++ forgon-core/src/main/java/com/forgon/databaseadapter/service/DateQueryAdapter.java (.../DateQueryAdapter.java) (revision 17308) @@ -154,8 +154,24 @@ } return ""; } - /** + * 时间类型转换成字符串，只取年月日 + * @param property + * @return + */ + public String dateToVarchar1(String property) { + if (StringUtils.isNotBlank(property)) { + if (dbConnection.isSqlServer()) { + return "CONVERT(varchar(10),"+property+",120)"; + } else if (dbConnection.isOracle()) { + return "to_char("+property+",'yyyy-mm-dd')"; + } else if (dbConnection.isMySQL()) { + return property ; + } + } + return ""; + } + /** * 替换sql查询条件的日期 * * @param date Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r17255 -r17308 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 17255) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 17308) @@ -11979,63 +11979,119 @@ if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate) && StringUtils.isNotBlank(formName)) { - String sql = buildQuerySQL(startDate, endDate, querySupplyRoom, +// String querySql = buildQuerySQL(startDate, endDate, querySupplyRoom, +// formName, materialName, tousseName, monitoringType); + String whereSql = buildWhereSQL(startDate, endDate, querySupplyRoom, formName, materialName, tousseName, monitoringType); + String dataSql = dateQueryAdapter.dateToVarchar1("qmi.dateTime"); + //CONVERT(varchar(10), qmi.dateTime, 120) to_char(qmi.dateTime, 'yyyy-MM-dd') + String sql = " select " + dataSql + " dateStr," + + " qmi.tousseName toussName,qmi.material material,qmi.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," + + " fdi.name fdiName, fdi.orderNumber orderNum,fdio.value,fiov.id " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " join FormDefinitionItemOption fdio on fdi.id=fdio.formDefinitionItem_id left join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " + + " and fiov.definitionItemOption_id=fdio.id " + + " where (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql; + addMonitoringItemBeanOptionRadioAndCHeck(list,sql); - List qmInstanceList = objectDao.findBySql(QualityMonitoringInstance.class.getSimpleName(), sql); - for (QualityMonitoringInstance qmInstance : qmInstanceList) { - - String dateStr = ""; - if(qmInstance.getDateTime() != null){ - dateStr = Constants.SIMPLEDATEFORMAT_YYYYMMDD.format(qmInstance.getDateTime()); + sql = " select " + dataSql + " dateStr," + + " qmi.tousseName toussName,qmi.material material,qmi.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," + + " fdi.name fdiName, fdi.orderNumber orderNum,fiItem.answer amswer " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " where (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql; + addMonitoringItemBeanOptionSingleResult(list,sql); + + if(FormDefinition.FOMRTYPE_QUALITYMONITORING.equals(monitoringType)){ + sql = " select " + dataSql + " dateStr," + + " qmi.tousseName toussName,qmi.material material,qmi.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " left join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id" + + " where qmi.id not in (select formInstance_id from FormInstanceItem) " + + " and qmi.type = '" + FormDefinition.FOMRTYPE_QUALITYMONITORING + "' " + + " and " + whereSql; + addMonitoringItemBeanQualityMonitoring(list,sql); + } + if (FormDefinition.FOMRTYPE_PERIODICMONITORING.equals(monitoringType)) { + sql = " select " + dataSql + " dateStr," + + " qmi.tousseName toussName,qmi.material material,qmi.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg,qmi.result " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " where qmi.type = '" + FormDefinition.FOMRTYPE_PERIODICMONITORING + "'" + " and " + whereSql; + addMonitoringItemBeanRountineMonitoring(list,sql); + } + // 条数 + Integer qmCount = 0; + // 材料或者包数量和 + Integer qmTousseAndMaterialAmount = 0; + sql = " select count(0),sum(qmi.amount) from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " where " + whereSql; + ResultSet rs = objectDao.executeSql(sql); + try { + while (rs.next()) { + qmCount = rs.getInt(1); + qmTousseAndMaterialAmount = rs.getInt(2); } - List instanceItems = qmInstance.getItems(); - if(instanceItems != null){ - for (FormInstanceItem formInstanceItem : instanceItems) { - FormDefinitionItem definitionItem = formInstanceItem.getDefinitionItem(); - if (FormDefinitionItem.TYPE_RADIO.equals(definitionItem - .getType()) - || FormDefinitionItem.TYPE_CHECK - .equals(definitionItem.getType())) { - List options = definitionItem - .getOptions(); - if (options != null) { - for (FormDefinitionItemOption option : options) { - String questionName = definitionItem.getName(); - String optionStr = option.getValue(); - String answer = formInstanceItem.isChecked(option) ? "√" : ""; - newMonitoringItem(list, qmInstance,dateStr, definitionItem.getOrderNumber(), questionName,optionStr, answer); - } - } - } else { - newMonitoringItem(list, qmInstance, dateStr,definitionItem.getOrderNumber(), - "-",definitionItem.getName(),formInstanceItem.getAnswer()); - } - } - } - if("质量监测".equals(monitoringType) && CollectionUtils.isEmpty(instanceItems)){ - newMonitoringItem(list, qmInstance, dateStr,1,"-","-",""); - } - // - if ("定期监测".equals(monitoringType)) { - newMonitoringItem(list, qmInstance, dateStr,100,"-","监测结果",qmInstance.getResult()); - } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); } + +// List qmInstanceList = objectDao.findBySql(QualityMonitoringInstance.class.getSimpleName(), querySql); +// for (QualityMonitoringInstance qmInstance : qmInstanceList) { +// String dateStr = ""; +// if(qmInstance.getDateTime() != null){ +// dateStr = Constants.SIMPLEDATEFORMAT_YYYYMMDD.format(qmInstance.getDateTime()); +// } +// List instanceItems = qmInstance.getItems(); +// if(instanceItems != null){ +// for (FormInstanceItem formInstanceItem : instanceItems) { +// FormDefinitionItem definitionItem = formInstanceItem.getDefinitionItem(); +// if (FormDefinitionItem.TYPE_RADIO.equals(definitionItem.getType()) +// || FormDefinitionItem.TYPE_CHECK +// .equals(definitionItem.getType())) { +// List options = definitionItem +// .getOptions(); +// if (options != null) { +// for (FormDefinitionItemOption option : options) { +// String questionName = definitionItem.getName(); +// String optionStr = option.getValue(); +// String answer = formInstanceItem.isChecked(option) ? "√" : ""; +// newMonitoringItem(list, qmInstance,dateStr, definitionItem.getOrderNumber(), questionName,optionStr, answer); +// } +// } +// } else { +// newMonitoringItem(list, qmInstance, dateStr,definitionItem.getOrderNumber(), +// "-",definitionItem.getName(),formInstanceItem.getAnswer()); +// } +// } +// } +// if("质量监测".equals(monitoringType) && CollectionUtils.isEmpty(instanceItems)){ +// newMonitoringItem(list, qmInstance, dateStr,1,"-","-",""); +// } +// // +// if ("定期监测".equals(monitoringType)) { +// newMonitoringItem(list, qmInstance, dateStr,100,"-","监测结果",qmInstance.getResult()); +// } +// } // 质量检测加数量汇总 if ("质量监测".equals(monitoringType)) { Integer totalAmount = 0; Integer totalItem = 0; Integer orderNumber = 1; String option = ""; String questionName = ""; - for (QualityMonitoringInstance qualityMonitoring2 : qmInstanceList) { - Integer amount = qualityMonitoring2.getAmount(); - if(amount == null){ - amount = 0; - } - totalAmount += amount; - totalItem++; - } +// for (QualityMonitoringInstance qualityMonitoring2 : qmInstanceList) { +// Integer amount = qualityMonitoring2.getAmount(); +// if(amount == null){ +// amount = 0; +// } +// totalAmount += amount; +// totalItem++; +// } + totalAmount = qmTousseAndMaterialAmount; + totalItem = qmCount; if (list != null && list.size() > 0) { orderNumber = list.get(0).getOrderNumber(); option = list.get(0).getOption(); @@ -12112,14 +12168,16 @@ Integer orderNumber = 1; String option = ""; String questionName = ""; - for (QualityMonitoringInstance qualityMonitoring2 : qmInstanceList) { - Integer amount = qualityMonitoring2.getAmount(); - if(amount == null){ - amount = 0; - } - totalAmount += amount; - totalItem++; - } +// for (QualityMonitoringInstance qualityMonitoring2 : qmInstanceList) { +// Integer amount = qualityMonitoring2.getAmount(); +// if(amount == null){ +// amount = 0; +// } +// totalAmount += amount; +// totalItem++; +// } + totalAmount = qmTousseAndMaterialAmount; + totalItem = qmCount; if (list != null && list.size() > 0) { orderNumber = list.get(0).getOrderNumber(); option = list.get(0).getOption(); @@ -12761,7 +12819,146 @@ bean.setAnswer(answer); list.add(bean); } - + private void newMonitoringItem(List list, + Integer amount,String tousseName,String materialName, + Long qmiId,String positoinMsg,String dateStr, + Integer orderNum,String questionName,String option,String answer) { + MonitoringItemBean bean = new MonitoringItemBean(); + bean.setAmount(amount); + bean.setDateTime(dateStr); + String goodsName = tousseName; + if (StringUtils.isBlank(goodsName)) { + goodsName = materialName; + } + bean.setGoodsName(goodsName + "%&" + qmiId); + bean.setPositionMsg(positoinMsg); + bean.setOrderNumber(orderNum); + bean.setOption(option);// 表头第三列 + bean.setQuestionName(questionName);// 表头第二列 + bean.setAnswer(answer); + list.add(bean); + } + /** + * 添加质量监测记录的bean 单选和多选的结果 + * @param list + * @param sql + */ + private void addMonitoringItemBeanOptionRadioAndCHeck(List list,String sql){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = objectDao.executeSql(sql); + try { + while (rs.next()) { + String dataStr = rs.getString(1); + String tousseName = rs.getString(2); + String materialName = rs.getString(3); + int amount = rs.getInt(4); + Long qmiId = rs.getLong(5); + String positionMsg = rs.getString(6); + String questionName = rs.getString(7); + int orderNum = rs.getInt(8); + String optionStr = rs.getString(9); + Long fiovId = rs.getLong(10); + String answer = (fiovId > 0) ? "√" : ""; + + newMonitoringItem(list, amount,tousseName,materialName,qmiId,positionMsg, + dataStr, orderNum, questionName,optionStr, answer); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 添加质量监测记录的bean 单一的结果 + * @param list + * @param sql + */ + private void addMonitoringItemBeanOptionSingleResult(List list,String sql){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = objectDao.executeSql(sql); + try { + while (rs.next()) { + String dataStr = rs.getString(1); + String tousseName = rs.getString(2); + String materialName = rs.getString(3); + int amount = rs.getInt(4); + Long qmiId = rs.getLong(5); + String positionMsg = rs.getString(6); + String questionName = rs.getString(7); + int orderNum = rs.getInt(8); + String answer = rs.getString(9); + + newMonitoringItem(list, amount,tousseName,materialName,qmiId,positionMsg, + dataStr, orderNum, "-",questionName, answer); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 添加质量监测记录的bean 没有监测细则的记录 + * @param list + * @param sql + */ + private void addMonitoringItemBeanQualityMonitoring(List list,String sql){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = objectDao.executeSql(sql); + try { + while (rs.next()) { + String dataStr = rs.getString(1); + String tousseName = rs.getString(2); + String materialName = rs.getString(3); + int amount = rs.getInt(4); + Long qmiId = rs.getLong(5); + String positionMsg = rs.getString(6); + + newMonitoringItem(list, amount,tousseName,materialName,qmiId,positionMsg, + dataStr, 1, "-","-", ""); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** + * 添加质量监测记录的bean 定期监测的记录 + * @param list + * @param sql + */ + private void addMonitoringItemBeanRountineMonitoring(List list,String sql){ + if(StringUtils.isBlank(sql)){ + return ; + } + ResultSet rs = objectDao.executeSql(sql); + try { + while (rs.next()) { + String dataStr = rs.getString(1); + String tousseName = rs.getString(2); + String materialName = rs.getString(3); + int amount = rs.getInt(4); + Long qmiId = rs.getLong(5); + String positionMsg = rs.getString(6); + String result = rs.getString(7); + + newMonitoringItem(list, amount,tousseName,materialName,qmiId,positionMsg, + dataStr, 100, "-","监测结果", result); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } private String buildQuerySQL(String startDate, String endDate, String querySupplyRoom, String formName, String materialName, String tousseName, String monitoringType) { @@ -12808,6 +13005,69 @@ sql += " order by " + dateQueryAdapter.dateConverAdapter("po.dateTime","yyyy-MM-dd")+",po.tousseName,po.material"; return sql; } + /** + * 创建质量监测报表查询条件的sql + * @param startDate + * @param endDate + * @param querySupplyRoom + * @param formName + * @param materialName + * @param tousseName + * @param monitoringType + * @return + */ + private String buildWhereSQL(String startDate, String endDate, + String querySupplyRoom, String formName, String materialName, + String tousseName, String monitoringType) { +// select CONVERT(varchar(10), qmi.dateTime, 120) daty,fiOption.answer,sum(qmi.amount) count +// from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,FormInstanceItem fiItem,FormDefinitionItem fdi,FormInstanceOptionValue fiOption +// where 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 fd.formName = '器械清洗不合格' and fd.formType = '质量监测' and fdi.name = '评估方法' +// and qmi.dateTime between convert(datetime,'2016-11-01 00:00:00',120) +// and convert(datetime,'2017-02-01 00:00:00',120) group by CONVERT(varchar(10), qmi.dateTime, 120),fiOption.answer + + String sql = " qmi.dateTime between " + + dateQueryAdapter.dateAdapter(startDate+" 00:00:00") + + " and " + "" + + dateQueryAdapter.dateAdapter(endDate+" 23:59:59") + + " and qmi.type = '" + monitoringType + + "' and qmi.name = '" + formName + "'" + + " and fd.orgUnitCoding = '" + querySupplyRoom + "'"; + String materialSql = ""; + String tousseSql = ""; + if( StringUtils.isNotBlank(materialName)){ + // 如果字符串中有"[",进行转译 + if(materialName.contains("[")){ + int index = materialName.indexOf("["); + materialName = materialName.substring(0, index) + "\\" + materialName.substring(index, materialName.length()); + materialSql = " qmi.material like '%" + materialName + "%'" + " escape '\\' "; + }else{ + materialSql = " qmi.material like '%" + materialName + "%'"; + } + } + if( StringUtils.isNotBlank(tousseName)){ + // 如果字符串中有"[",进行转译 + if(tousseName.contains("[")){ + int index = tousseName.indexOf("["); + tousseName = tousseName.substring(0, index) + "\\" + tousseName.substring(index, materialName.length()); + tousseSql = " qmi.tousseName like '%" + tousseName + "%'" + " escape '\\' "; + }else{ + tousseSql = " qmi.tousseName like '%" + tousseName + "%'"; + } + } + // 如果都有材料和器械包的名字，用or条件 + String tsql = ""; + if(StringUtils.isNotBlank(materialSql) && StringUtils.isNotBlank(tousseSql)){ + tsql = " and ( " + materialSql + " or " + tousseSql + " ) "; + }else if(StringUtils.isNotBlank(materialSql)){ + tsql = " and " + materialSql; + }else if(StringUtils.isNotBlank(tousseSql)){ + tsql = " and " + tousseSql; + } + sql += tsql; + return sql; + } private List> getBeanMap(List beanList){ if(beanList == null)