Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r30295 -r30304 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 30295) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 30304) @@ -1715,11 +1715,12 @@ bean.setTitleSort(sourceSortMap.get(title)); // 质量监测不合格数列 String monitoringSql = "select fd.formName,sum(qmd.amount) from QualityMonitoringInstance qi," - + "FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qd,QualityMonitoringGoods qmd " - + "where qi.id = fi.id and fi.formDefinition_id = fd.id and fd.id = qd.id " - + " and qi.id=qmd.qualityMonitoringInstance_id and qd.responsibilitypart = '装配管理' " + + "FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd " + + "where qi.id = fi.id and fi.formDefinition_id = fd.id and fd.id = qmdf.id " + + " and qi.id=qmd.qualityMonitoringInstance_id and qmdf.responsibilitypart = '装配管理' " + "and fd.departcodes like '%"+deptCode+"%' " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", deptCode)+" and fd.formType = '质量监测' and " + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(deptCode) + +" and fd.formType = '质量监测' and " + " qi.datetime between " + dateQueryAdapter.dateAdapter(startDate) + " and " @@ -2218,11 +2219,11 @@ List returnList, String queryDate, String startDate, String endDate, Integer amount) { String monitoringSql = "select fd.formName,sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd,QualityMonitoringDefinition qd,QualityMonitoringGoods qmd " - + " where qi.id = fi.id and fi.formDefinition_id = fd.id and fd.id = qd.id " + + "FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd " + + " where qi.id = fi.id and fi.formDefinition_id = fd.id and fd.id = qmdf.id " + " and qi.id=qmd.qualityMonitoringInstance_id " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", departCoding) - + " and qd.responsibilitypart = '灭菌管理' and fd.formType = '质量监测' " + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(departCoding) + + " and qmdf.responsibilitypart = '灭菌管理' and fd.formType = '质量监测' " + SqlUtils.get_LikeSql("fd.departcodes", departCoding.split(",")) + " and qi.datetime between " @@ -14692,8 +14693,8 @@ + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," + " fdi.name fdiName, fdi.orderNumber orderNum,fdio.value,fiov.id,fi.createUserName " + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + " join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.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 join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + extraJoin + " left join FormInstanceOptionValue fiov on fiov.formInstanceItem_id=fiItem.id " @@ -14705,21 +14706,21 @@ + " qmi.id qmiId,qmi.positionMsg positionMsg," + " fdi.name fdiName, fdi.orderNumber orderNum,fdio.value,fiov.id,fi.createUserName " + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + " join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id" + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.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 " + extraJoin + " and fiov.definitionItemOption_id=fdio.id " + " where (fdi.type = '" + FormDefinitionItem.TYPE_RADIO + "' or fdi.type ='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql - + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null) "; + + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmdf.id is null) "; addMonitoringItemBeanOptionRadioAndCHeckWithoutMaterial(list,sql,isCustomADJAndIsFormNameIsADJ); sql = " select " + dataSql + " dateStr," + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg," + " fdi.name fdiName, fdi.orderNumber orderNum,fiItem.answer amswer,fi.createUserName " + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + " join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " + " join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + extraJoin + " where (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql + tousseNameSql; @@ -14729,11 +14730,11 @@ + " qmi.id qmiId,qmi.positionMsg positionMsg," + " fdi.name fdiName, fdi.orderNumber orderNum,fiItem.answer amswer,fi.createUserName " + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + " join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join FormInstanceItem fiItem on qmi.id=fiItem.formInstance_id join FormDefinitionItem fdi on fiItem.formDefinitionItem_id=fdi.id " + extraJoin + " where (fdi.type != '" + FormDefinitionItem.TYPE_RADIO + "' and fdi.type!='" + FormDefinitionItem.TYPE_CHECK + "')" + " and " + whereSql - + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmd.id is null) "; + + " and qmi.id in ( select qmi.id from QualityMonitoringInstance qmi left join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id where qmdf.id is null) "; addMonitoringItemBeanOptionSingleResultWithoutMaterial(list,sql,isCustomADJAndIsFormNameIsADJ,monitoringType); if(FormDefinition.FOMRTYPE_QUALITYMONITORING.equals(monitoringType)){ @@ -14755,8 +14756,8 @@ sql = " select " + dataSql + " dateStr," + " qmd.tousseName toussName,qmd.material material,qmd.amount amount,qmi.id qmiId,qmi.positionMsg positionMsg,qmi.result,fi.createUserName " + extraQuery - + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + " join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join QualityMonitoringGoods qmd on qmi.id=qmd.qualityMonitoringInstance_id " + extraJoin + " where qmi.type = '" + FormDefinition.FOMRTYPE_PERIODICMONITORING + "'" + " and " + whereSql + tousseNameSql; addMonitoringItemBeanRountineMonitoring(list,sql,isCustomADJAndIsFormNameIsADJ,isQualifiedAmount); @@ -14765,8 +14766,8 @@ Integer qmCount = 0; // 材料或者包数量和 Integer qmTousseAndMaterialAmount = 0; - sql = " select count(0) count,sum(t.aAmount) total from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " - + " join ( select qmi.id qid,sum(qmd.amount) aAmount from QualityMonitoringInstance qmi,QualityMonitoringGoods qmd where qmi.id=qmd.qualityMonitoringInstance_id " + sql = " select count(0) count,sum(t.aAmount) total from QualityMonitoringInstance qmi join FormInstance fi on qmi.id=fi.id join FormDefinition fd on fi.formDefinition_id=fd.id " + + " join QualityMonitoringDefinition qmdf on qmdf.id=fd.id join ( select qmi.id qid,sum(qmd.amount) aAmount from QualityMonitoringInstance qmi,QualityMonitoringGoods qmd where qmi.id=qmd.qualityMonitoringInstance_id " + tousseNameSql + " group by qmi.id ) t on t.qid=qmi.id " + " where " + whereSql; @@ -17323,7 +17324,7 @@ reportParams.monthlyStr = dateQueryAdapter.dateToVarchar2("qi.dateTime"); reportParams.extraSelectColumns = ",src.orgUnitName"; reportParams.extraQuery = " and (src.supplyroomtype = 1 or src.supplyroomtype = 2) "; - reportParams.extraJoinCondition = " inner join SupplyRoomConfig src on src.orgUnitCoding = fi.orgUnitCoding "; + reportParams.extraJoinCondition = " inner join SupplyRoomConfig src on src.orgUnitCoding = qmdf.handleDepartCoding "; reportParams.extraGroupBy = ",src.orgUnitName"; String packingUQSql = DataIndex.getUnQualifiedMonitorAmountSql("装配管理", "", reportParams); @@ -17334,10 +17335,10 @@ // 湿包数量的map，包总数的map跟灭菌总数的一样 Map wetPackUQMap = new HashMap(); String wetPackUQSql = "select " + qmDataSql +" dataStr,sum(qmd.amount) count,src.orgUnitName " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd, " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf , " + " SupplyRoomConfig src,QualityMonitoringGoods qmd " - + " where qmi.id=fi.id and fd.id = fi.formDefinition_id and (src.supplyroomtype = 1 or src.supplyroomtype = 2) " - + " and src.orgUnitCoding = fi.orgUnitCoding and qmi.id=qmd.qualityMonitoringInstance_id " + + " where qmdf.id=fd.id and qmi.id=fi.id and fd.id = fi.formDefinition_id and (src.supplyroomtype = 1 or src.supplyroomtype = 2) " + + " and src.orgUnitCoding = qmdf.handleDepartCoding and qmi.id=qmd.qualityMonitoringInstance_id " + " and ( fd.formName = '" + "湿包"+"' )" + " and qmi.dateTime between " + dateQueryAdapter.dateAdapter(startDay) + "and "+dateQueryAdapter.dateAdapter(endDay) @@ -19697,10 +19698,12 @@ + "from QualityMonitoringInstance qmi " + "join FormInstance fi on fi.id=qmi.id " + "join FormDefinition fd on fd.id=fi.formDefinition_id " + + "join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " + "join QualityMonitoringGoods qmg on qmg.qualityMonitoringInstance_id=fi.id " + "where " + (tousse ? " qmg.tousseName is not null " : " (qmg.tousseName is null or qmg.tousseName='') ") + "and fd.formType = '" + FormDefinition.FOMRTYPE_QUALITYMONITORING+ "' " - + "and fi.orgUnitCoding = '"+departCoding+"' " + SqlUtils.get_InSql("fd.formName", unqualifiedSource) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(departCoding) + + SqlUtils.get_InSql("fd.formName", unqualifiedSource) + " and " + dateQueryAdapter.dateAreaSql("qmi.datetime", queryYear, nextYear) + "group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime", "mm"); @@ -20085,10 +20088,12 @@ + "from QualityMonitoringInstance qmi " + "join FormInstance fi on fi.id=qmi.id " + "join FormDefinition fd on fd.id=fi.formDefinition_id " + + "join QualityMonitoringDefinition qmdf on qmdf.id=fd.id " + "join QualityMonitoringGoods qmg on qmg.qualityMonitoringInstance_id=fi.id " + "where " + (tousse ? " qmg.tousseName is not null " : " (qmg.tousseName is null or qmg.tousseName='') ") + "and fd.formType = '" + FormDefinition.FOMRTYPE_QUALITYMONITORING+ "' " - + "and fi.orgUnitCoding = '"+departmentCode+"' " + SqlUtils.get_InSql("fd.formName", unqualifiedSource) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(departmentCode) + + SqlUtils.get_InSql("fd.formName", unqualifiedSource) + " and " + dateQueryAdapter.dateAreaSql("qmi.datetime", startTime, endTime) + "group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime", format); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfXjjqzyy.java =================================================================== diff -u -r30293 -r30304 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfXjjqzyy.java (.../CustomReportsOfXjjqzyy.java) (revision 30293) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfXjjqzyy.java (.../CustomReportsOfXjjqzyy.java) (revision 30304) @@ -1183,90 +1183,90 @@ addWorkQualityCollectionBean(list,titleName,sql,recAmountMap);*/ // 临床器械清洗不合格率 String sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '临床器械清洗不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); String titleName = "临床器械清洗不合格率"; addWorkQualityCollectionBean(list,titleName,sql,noEqualsDepartCodequwashAmountMap); // 手术器械清洗不合格率 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '手术器械清洗不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "手术器械清洗不合格率"; addWorkQualityCollectionBean(list,titleName,sql,equalsDepartCodequwashAmountMap); // 外来器械清洗不合格率 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '外来医疗器械清洗不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "外来器械清洗不合格率"; addWorkQualityCollectionBean(list,titleName,sql,foreignQuwashAmountMap); // 器械包装不合格率 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '器械包装不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "器械包装不合格率"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 器械包发货不合格 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '器械包发货不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "器械包发货不合格"; addWorkQualityCollectionBean(list,titleName,sql,invoiceAmountMap); // 器械包标签不合格 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '器械包标签不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "器械包标签不合格"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 无菌包内器械功能不全数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '器械功能不全'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "无菌包内器械功能不全数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 湿包发生率 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '湿包登记表'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "湿包发生率"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 灭菌失败率 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '器械灭菌不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "灭菌失败率"; Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java =================================================================== diff -u -r30293 -r30304 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java (.../CustomReportsOfZsyy.java) (revision 30293) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfZsyy.java (.../CustomReportsOfZsyy.java) (revision 30304) @@ -517,30 +517,30 @@ String nextYear = DataIndex.getNextYear(year) + " 00:00:00"; //回收时发现器械缺损不配套例数 String sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName like '%缺损不配套%'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); String titleName = "回收时发现器械缺损不配套例数"; addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); // 回收时发现器械种类数量不相符例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName like '%种类数量不相符%'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "回收时发现器械种类数量不相符例数"; addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); // 应急处理器械次数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName like '%应急处理%'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "应急处理器械次数"; @@ -589,10 +589,10 @@ + " group by " + dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); Map totalmap = getMapBySql(totalSqlOne); sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName like '%普通器械清洗不合格%'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "普通器械清洗不合格率 "; @@ -617,10 +617,10 @@ + ") tl group by monthstr "); totalmap = getMapBySql(totalSqlTwo); sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName like '%专科器械清洗不合格%'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "专科器械清洗不合格率"; @@ -656,110 +656,110 @@ + " group by " + dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); totalmap = getMapBySql(totalSqlThree); sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName like '%手术器械清洗不合格%'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "手术器械清洗不合格率"; addWorkQualityCollectionBean(list,titleName,sql,totalmap); // 无菌包内器械数量不符例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '无菌包内器械数量不符'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "无菌包内器械数量不符例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 无菌包内器械功能不全例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '无菌包内器械功能不全'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "菌包内器械功能不全例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 无菌包内器械种类错误例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '无菌包内器械种类错误'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "无菌包内器械种类错误例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 包内指示卡欠缺例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '包内指示卡欠缺例数'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "包内指示卡欠缺例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 灭菌包包装密闭性能不合格例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '灭菌包包装密闭性能不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "灭菌包包装密闭性能不合格例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 包外明显污迹例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName like '%污迹%'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "包外明显污迹例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 无菌包标识不正确例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '无菌包标识不正确'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "无菌包标识不正确例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 灭菌方式选择不正确例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '灭菌方式选择不正确'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "灭菌方式选择不正确例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 湿包例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '湿包'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "湿包例数"; addWorkQualityCollectionBean(list,titleName,sql,packingAmountMap); // 包内化学指示卡变色不合格例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '包内化学指示卡变色不合格'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "包内化学指示卡变色不合格例数"; @@ -780,30 +780,30 @@ + " group by " + dateQueryAdapter.dateConverAdapter3("iv.sendTime","mm"); totalmap = getMapBySql(totalSql); sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '无菌物品发放不及时'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "无菌物品发放不及时次数"; addWorkQualityCollectionBean(list,titleName,sql,totalmap); // 器械损坏例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '器械损坏'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "器械损坏例数"; addWorkQualityCollectionBean(list,titleName,sql,recAmountMap); // 器械遗失例数 sql = " select " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm") + " monthstr,sum(qmd.amount) amount " - + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd" - + " where qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + + " from QualityMonitoringInstance qmi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd" + + " where qmdf.id=fd.id and qmi.id=fi.id and fi.formDefinition_id=fd.id and qmi.id = qmd.qualityMonitoringInstance_id " + " and fd.formName = '器械遗失'" - + " and fi.orgUnitCoding = '"+querySupplyRoom+"'" + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and (qmi.dateTime between " +dateQueryAdapter.dateAdapter(queryYear)+" and " + dateQueryAdapter.dateAdapter(nextYear) + ")" + " group by " + dateQueryAdapter.dateConverAdapter3("qmi.dateTime","mm"); titleName = "器械遗失例数"; Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r30293 -r30304 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 30293) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 30304) @@ -1985,7 +1985,7 @@ obj.extraSelectColumns, obj.extraJoinCondition, obj.betweenSql, - SqlUtils.get_InSql_Extra("fi.orgUnitCoding", obj.querySupplyRoom), + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(obj.querySupplyRoom), obj.extraQuery, getGroupByDatePeriodSql(obj.monthlyStr), obj.extraGroupBy @@ -2003,7 +2003,7 @@ + "group by " + obj.monthlyStr, obj.betweenSql, - SqlUtils.get_InSql_Extra("fi.orgUnitCoding", obj.querySupplyRoom) + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(obj.querySupplyRoom) ); break; @@ -2034,7 +2034,7 @@ + "and fd.formType = '质量监测'", groupByToken, obj.betweenSql, - SqlUtils.get_InSql_Extra("fi.orgUnitCoding", obj.querySupplyRoom) + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(obj.querySupplyRoom) ); if (StringUtils.isNotBlank(obj.monthlyStr)){ @@ -2065,7 +2065,7 @@ + "where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " + SqlUtils.get_LikeSql("fd.formName", formNames) + "and qi.datetime " + obj.betweenSql - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", obj.querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(obj.querySupplyRoom) + "and fd.formType = '质量监测' " + filterMaterialIsNullSql; if (StringUtils.isNotBlank(obj.monthlyStr)){ @@ -2092,7 +2092,7 @@ + "group by " + obj.monthlyStr, obj.betweenSql, - SqlUtils.get_InSql_Extra("fi.orgUnitCoding", obj.querySupplyRoom) + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(obj.querySupplyRoom) ); return sql; @@ -2117,7 +2117,7 @@ + "group by " + obj.monthlyStr, obj.betweenSql, - SqlUtils.get_InSql_Extra("fi.orgUnitCoding", obj.querySupplyRoom) + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(obj.querySupplyRoom) ); return sql; @@ -2147,9 +2147,7 @@ likeSql, obj.betweenSql, obj.extraQuery, - SqlUtils - .get_InSql_Extra("fi.orgUnitCoding", - obj.querySupplyRoom), + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(obj.querySupplyRoom), getGroupByDatePeriodSql(obj.monthlyStr), getExtraGroupBySql(obj.monthlyStr, obj.extraGroupBy)); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/WorkQualityCollectionDataIndex.java =================================================================== diff -u -r30293 -r30304 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/WorkQualityCollectionDataIndex.java (.../WorkQualityCollectionDataIndex.java) (revision 30293) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/WorkQualityCollectionDataIndex.java (.../WorkQualityCollectionDataIndex.java) (revision 30304) @@ -1264,12 +1264,12 @@ // 清洗不合格量 String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime", "mm") - + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd " - + "where qi.id = fi.id and fi.formDefinition_id = fd.id " + + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi,FormDefinition fd,QualityMonitoringGoods qmd,QualityMonitoringDefinition qmdf " + + "where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id " + " and qi.id=qmd.qualityMonitoringInstance_id " + SqlUtils.get_LikeSql("fd.formName", new String[] { "清洗不合格", "清洗不及格" }) - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and fd.formType = '质量监测' " + " and qi.dateTime >= " + dateQueryAdapter.dateAdapter(queryYear) + " and qi.dateTime <= " @@ -1555,11 +1555,11 @@ } // 清洗不合格数量orgUnitCoding String sql = "select sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd,QualityMonitoringGoods qmd " - + " where qi.id = fi.id and fi.formDefinition_id = fd.id " + + "FormDefinition fd,QualityMonitoringGoods qmd,QualityMonitoringDefinition qmdf " + + " where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id " + " and qi.id=qmd.qualityMonitoringInstance_id" + " and fd.formName like '%器械清洗不合格%' " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and fd.formType = '质量监测' " + " and qi.dateTime >= " + dateQueryAdapter.dateAdapter(startDay) @@ -1637,9 +1637,9 @@ endDay = innerMap.get(keyOfInnerMap); } String sql = "select sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd,QualityMonitoringGoods qmd where qi.id = fi.id " + + "FormDefinition fd,QualityMonitoringGoods qmd,QualityMonitoringDefinition qmdf where qmdf.id=fd.id and qi.id = fi.id " + " and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and fd.formName like '%消毒物品不合格%' and qi.dateTime >= " + dateQueryAdapter.dateAdapter(startDay) + " and qi.dateTime <= " @@ -1701,9 +1701,9 @@ String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm") + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi,FormDefinition fd " - + " ,QualityMonitoringGoods qmd " - + " where qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + " ,QualityMonitoringGoods qmd,QualityMonitoringDefinition qmdf " + + " where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and fd.formName like '%消毒物品不合格%' and qi.dateTime >= " + dateQueryAdapter.dateAdapter(queryYear) + " and qi.dateTime <= " @@ -2162,14 +2162,14 @@ endDay = innerMap.get(keyOfInnerMap); } String sql = "select count(*) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd where qi.id = fi.id and fi.formDefinition_id = fd.id and " + + "FormDefinition fd ,QualityMonitoringDefinition qmdf where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id and " + "fd.formName like '%灭菌失败事件%' " + "and qi.dateTime >= " + dateQueryAdapter.dateAdapter(startDay) + " and qi.dateTime <= " + dateQueryAdapter.dateAdapter(endDay) + " and fd.formType = '质量监测'" - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom); + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom); String totalSql = "select count(*) from sterilizationrecord r ,sterilizer s where r.sterilizer_id = s.id " + " and r.endDate >= " + dateQueryAdapter.dateAdapter(startDay) @@ -2224,14 +2224,14 @@ String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm") + " monthstr, count(*) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd where qi.id = fi.id and fi.formDefinition_id = fd.id " + + "FormDefinition fd ,QualityMonitoringDefinition qmdf where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id " + "and fd.formName like '%灭菌失败事件%' " + "and qi.dateTime >= " + dateQueryAdapter.dateAdapter(queryYear) + " and qi.dateTime <= " + dateQueryAdapter.dateAdapter(nextYear) + " and fd.formType = '质量监测'" - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " group by " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm"); ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); @@ -2536,15 +2536,15 @@ String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm") + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi," - + " FormDefinition fd,QualityMonitoringGoods qmd where qi.id = fi.id and fi.formDefinition_id = fd.id " + + " FormDefinition fd,QualityMonitoringGoods qmd,QualityMonitoringDefinition qmdf where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id " + " and qi.id=qmd.qualityMonitoringInstance_id " + " and fd.formName like '%无菌物品发放错误发生数%' " + "and qi.dateTime >= " + dateQueryAdapter.dateAdapter(queryYear) + " and qi.dateTime <= " + dateQueryAdapter.dateAdapter(nextYear) + " and fd.formType = '质量监测'" - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " group by " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm"); ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); @@ -2704,9 +2704,9 @@ String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm") + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd,QualityMonitoringGoods qmd where qi.id = fi.id and fi.formDefinition_id = fd.id " - + " and qi.id=qmd.qualityMonitoringInstance_id " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + "FormDefinition fd,QualityMonitoringGoods qmd ,QualityMonitoringDefinition qmdf where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id " + + " and qi.id=qmd.qualityMonitoringInstance_id " + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and fd.formName like '%回收器械丢失%' " + "and qi.dateTime >= " + dateQueryAdapter.dateAdapter(queryYear) @@ -2812,9 +2812,9 @@ String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm") + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd,QualityMonitoringGoods qmd " - + " where qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + "FormDefinition fd,QualityMonitoringGoods qmd ,QualityMonitoringDefinition qmdf " + + " where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and fd.formName like '%器械损坏%'" + " and qi.dateTime >= " + dateQueryAdapter.dateAdapter(queryYear) + " and qi.dateTime <= " @@ -3367,16 +3367,16 @@ String queryYear = year + "-01-01 00:00:00"; String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm") - + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi,QualityMonitoringGoods qmd,FormInstance fi,FormDefinition fd," - + " FormInstanceItem fii,FormDefinitionItem fdi where qi.id = fi.id and " + + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi,QualityMonitoringGoods qmd,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf , " + + " FormInstanceItem fii,FormDefinitionItem fdi where qmdf.id=fd.id and qi.id = fi.id and " + " fi.formDefinition_id = fd.id and fi.id = fii.formInstance_id and " + " fii.formDefinitionItem_id = fdi.id and qi.id=qmd.qualityMonitoringInstance_id " + "and fdi.name like '%包内化学指示卡变色不合格%' and qi.dateTime >= " + dateQueryAdapter.dateAdapter(queryYear) + " and qi.dateTime <= " + dateQueryAdapter.dateAdapter(nextYear) + " and fd.formType = '质量监测'" - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " group by " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm"); ResultSet result = objectDao.executeSql(sql); @@ -3510,14 +3510,14 @@ String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm") + " monthstr, sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi," - + " FormDefinition fd,QualityMonitoringGoods qmd " - + " where qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " + + " FormDefinition fd,QualityMonitoringGoods qmd ,QualityMonitoringDefinition qmdf " + + " where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " + " and fd.formName like '%湿包%' and qi.dateTime >= " + dateQueryAdapter.dateAdapter(queryYear) + " and qi.dateTime <= " + dateQueryAdapter.dateAdapter(nextYear) + " and fd.formType = '质量监测'" - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " group by " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm"); ResultSet result = objectDao.executeSql(sql); @@ -3753,7 +3753,7 @@ endDay = innerMap.get(keyOfInnerMap); } String sql = "select count(*) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd, FormInstanceItem fii, FormDefinitionItem fdi where qi.id = fi.id and fi.formDefinition_id = fd.id and " + + "FormDefinition fd ,QualityMonitoringDefinition qmdf , FormInstanceItem fii, FormDefinitionItem fdi where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id and " + " fi.id = fii.formInstance_id and fii.formDefinitionItem_id = fdi.id and " + " fd.formName like '%灭菌失败事件%' and " + " fdi.name = '灭菌方式选择不正确' and fii.answer is not null " @@ -3762,7 +3762,7 @@ + " and qi.dateTime <= " + dateQueryAdapter.dateAdapter(endDay) + " and fd.formType = '质量监测'" - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom); + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom); Integer amount = dataIndex.getAmountBySql(sql); bean.setTitle("灭菌方式选择不正确发生数"); @@ -3876,8 +3876,8 @@ String queryYear = year + "-01-01 00:00:00"; String nextYear = dataIndex.getNextYear(year) + " 00:00:00"; String sql = "select " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm") - + " monthstr, count(*) from QualityMonitoringInstance qi,FormInstance fi,FormDefinition fd," - + "FormInstanceItem fii,FormDefinitionItem fdi where qi.id = fi.id and " + + " monthstr, count(*) from QualityMonitoringInstance qi,FormInstance fi,FormDefinition fd ,QualityMonitoringDefinition qmdf ," + + "FormInstanceItem fii,FormDefinitionItem fdi where qmdf.id=fd.id and qi.id = fi.id and " + "fi.formDefinition_id = fd.id and fi.id = fii.formInstance_id and " + "fii.formDefinitionItem_id = fdi.id " + "and fd.formName like '%灭菌失败事件%' and fdi.name = '灭菌方式选择不正确' " @@ -3886,7 +3886,7 @@ + " and qi.dateTime <= " + dateQueryAdapter.dateAdapter(nextYear) + " and fd.formType = '质量监测'" - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " group by " + dateQueryAdapter.dateConverAdapter3("qi.dateTime","mm"); ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); @@ -4202,15 +4202,15 @@ private String buildQualityMonitoringSQL2(String startDay,String endDay,String querySupplyRoom,String formName){ String sql = "select sum(qmd.amount) from QualityMonitoringInstance qi ,FormInstance fi," - + "FormDefinition fd ,QualityMonitoringGoods qmd " - + " where qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id and " + + "FormDefinition fd ,QualityMonitoringDefinition qmdf ,QualityMonitoringGoods qmd " + + " where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id and " + " fd.formName like '%"+formName+"%' " + "and qi.dateTime >= " + dateQueryAdapter.dateAdapter(startDay) + " and qi.dateTime <= " + dateQueryAdapter.dateAdapter(endDay) + " and fd.formType = '质量监测'" - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom); + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom); return sql; } @@ -4489,11 +4489,11 @@ } public String buildQualityMonitoringSQL(String querySupplyRoom,String startDay,String endDay,String likeValue){ - String sql = "select sum(qmd.amount) from QualityMonitoringInstance qi,FormInstance fi,FormDefinition fd," + String sql = "select sum(qmd.amount) from QualityMonitoringInstance qi,FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf ," + "FormInstanceItem fii,FormDefinitionItem fdi,QualityMonitoringGoods qmd " - + " where qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " + + " where qmdf.id=fd.id and qi.id = fi.id and fi.formDefinition_id = fd.id and qi.id=qmd.qualityMonitoringInstance_id " + " and fi.id = fii.formInstance_id and fii.formDefinitionItem_id = fdi.id " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", querySupplyRoom) + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(querySupplyRoom) + " and fii.answer like '%" + likeValue + "%' and qi.dateTime >= " + dateQueryAdapter.dateAdapter(startDay) + " and qi.dateTime <= " Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfDgsfy.java =================================================================== diff -u -r30293 -r30304 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfDgsfy.java (.../CustomReportsOfDgsfy.java) (revision 30293) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/CustomReportsOfDgsfy.java (.../CustomReportsOfDgsfy.java) (revision 30304) @@ -1229,11 +1229,12 @@ // 质量监测不合格数列 String monitoringSql = "select fd.formName,sum(qmd.amount) from QualityMonitoringInstance qi," - + "FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qd,QualityMonitoringGoods qmd " - + "where qi.id = fi.id and fi.formDefinition_id = fd.id and fd.id = qd.id " - + " and qi.id=qmd.qualityMonitoringInstance_id and qd.responsibilitypart = '装配管理' " + + "FormInstance fi,FormDefinition fd,QualityMonitoringDefinition qmdf,QualityMonitoringGoods qmd " + + "where qi.id = fi.id and fi.formDefinition_id = fd.id and fd.id = qmdf.id " + + " and qi.id=qmd.qualityMonitoringInstance_id and qmdf.responsibilitypart = '装配管理' " + "and fd.departcodes like '%"+deptCode+"%' " - + SqlUtils.get_InSql_Extra("fi.orgUnitCoding", deptCode)+" and fd.formType = '质量监测' and " + + SqlUtils.gethandleDepartCodingOfQualityMonitoringDefinitionSql(deptCode) + +" and fd.formType = '质量监测' and " + " qi.datetime between " + dateQueryAdapter.dateAdapter(startDate) + " and "