Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentRepairReportHelper.java =================================================================== diff -u -r35786 -r35788 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentRepairReportHelper.java (.../InstrumentRepairReportHelper.java) (revision 35786) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/InstrumentRepairReportHelper.java (.../InstrumentRepairReportHelper.java) (revision 35788) @@ -29,13 +29,7 @@ * @author zc.li 2023-05-04 * */ -public class InstrumentRepairReportHelper { - @Autowired - private ObjectDao objectDao; - @Autowired - private InitDbConnection dbConnection; - @Autowired - protected DateQueryAdapter dateQueryAdapter; +public class InstrumentRepairReportHelper extends ReportHelper{ /** * CDSLQYQYYY-114 获取器械维修记录报表 * @param startTime 查询登记开始时间 Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportHelper.java (revision 35788) @@ -0,0 +1,19 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import org.springframework.beans.factory.annotation.Autowired; + +import com.forgon.databaseadapter.service.DateQueryAdapter; +import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; +import com.forgon.tools.db.InitDbConnection; +import com.forgon.tools.hibernate.ObjectDao; + +public class ReportHelper { + @Autowired + protected DateQueryAdapter dateQueryAdapter; + @Autowired + protected ObjectDao objectDao; + @Autowired + protected InitDbConnection dbConnection; + @Autowired + protected DataIndex dataIndex; +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35784 -r35788 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35784) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35788) @@ -176,6 +176,7 @@ import com.forgon.disinfectsystem.jasperreports.service.dataindex.WorkQualityCollectionDataIndex; import com.forgon.disinfectsystem.jasperreports.util.ForeignTousseApplicationReportHelper; import com.forgon.disinfectsystem.jasperreports.util.InstrumentRepairReportHelper; +import com.forgon.disinfectsystem.jasperreports.util.TousseWorkLoadDataForDLZXYYHelper; import com.forgon.disinfectsystem.packing.service.PackingManager; import com.forgon.disinfectsystem.qualitymonitoring.definition.service.QualityMonitoringDefinitionManager; import com.forgon.disinfectsystem.recyclingapplication.service.InvoicePlanManager; @@ -294,6 +295,8 @@ private InstrumentRepairReportHelper instrumentRepairReportHelper; @Autowired private ForeignTousseApplicationReportHelper foreignTousseApplicationReportHelper; + @Autowired + private TousseWorkLoadDataForDLZXYYHelper tousseWorkLoadDataForDLZXYYHelper; public void setPackingManager(PackingManager packingManager) { this.packingManager = packingManager; } @@ -30095,381 +30098,7 @@ } @Override public List getTousseWorkLoadDataForDLZXYY(String yyyyMM) { - if(StringUtils.isBlank(yyyyMM)){ - return null; - } - String nonWovenStr = "无纺布包装"; - String paperPlasticStr = "纸塑包装"; - String yyyy = yyyyMM.substring(0,4); - String mm = yyyyMM.substring(5); - //本月最后一天 - String lastDay = DateTools.getLastDayOfMonthByDate(yyyy, mm); - //当月总天数 - Integer sumDay = Integer.valueOf(lastDay.substring(lastDay.length()-2)); - - String startDate = yyyyMM + "-01 00:00:00"; - //下个月第一天 - String endDate = DateTools.getFirstDayOfNextMonth(yyyy, mm) + " 00:00:00"; - ReportQueryParams params = new ReportQueryParams(); - params.betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDate),dateQueryAdapter.dateAdapter(endDate)); - params.isGroup = true; - params.haveCombo = false; - int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); - //临床器械组 - String groupDay = dateQueryAdapter.dateConverAdapter3("endDate","dd"); - params.extraSelectColumns = ",tdc.packageType, " + groupDay + " dateTime "; - params.extraGroupBy = " group by tdc.packageType, "+ groupDay;// - params.extraQuery = " and tdc.packageType in ('"+ nonWovenStr +"','"+ paperPlasticStr +"') and tdc.taskGroup='临床器械组' "; - String washMaterialSQL = String.format("select packageType,sum(tl.amount) amount,dateTime from (" - +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) - + ") tl group by packageType,dateTime "); - params.extraGroupBy = ""; - ResultSet washMaterialRs = null; - Map dayMap = new HashMap(); - try { - washMaterialRs = objectDao.executeSql(washMaterialSQL); - while (washMaterialRs.next()) { - Integer amount = washMaterialRs.getInt("amount"); - int day = washMaterialRs.getInt("dateTime"); - String packageType = washMaterialRs.getString("packageType"); - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - if(nonWovenStr.equals(packageType)){ - vo.setNonWovenWashMaterialsNum(amount); - }else if(paperPlasticStr.equals(packageType)){ - vo.setPaperPlasticWashMaterialsNum(amount); - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(washMaterialRs); - } - - params.groupBySql = " group by tdc.packageType,"+groupDay; - - //清洗包数 - String washTousseSQL = String.format("select packageType,sum(tl.amount) amount,dateTime from (" - +dataIndex.getWorkAmountByPackageSQL("清洗数量", params) - + ") tl group by packageType,dateTime "); - params.extraGroupBy = ""; - params.extraQuery = ""; - params.groupBySql = ""; - params.extraSelectColumns = ""; - ResultSet washTousseRs = null; - try { - washTousseRs = objectDao.executeSql(washTousseSQL); - while (washTousseRs.next()) { - Integer amount = washTousseRs.getInt("amount"); - int day = washTousseRs.getInt("dateTime"); - String packageType = washTousseRs.getString("packageType"); - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - if(nonWovenStr.equals(packageType)){ - vo.setNonWovenWashTousseNum(amount); - }else if(paperPlasticStr.equals(packageType)){ - vo.setPaperPlasticWashTousseNum(amount); - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(washTousseRs); - } - params.extraSelectColumns = "," + groupDay + " dateTime "; - params.groupBySql = " group by "+groupDay; - //厂家器械清洗总件数 汇总所有外来器械包的清洗的总件数 - params.extraQuery = " and tdc.tousseType in ('"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"','"+ TousseDefinition.PACKAGE_TYPE_SPLIT +"') "; - String foreignFactoryMaterialsSQL = String.format("select sum(tl.amount) amount,dateTime from (" - +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) - + ") tl group by dateTime "); - ResultSet foreignFactoryMaterialsRs = null; - try { - foreignFactoryMaterialsRs = objectDao.executeSql(foreignFactoryMaterialsSQL); - while (foreignFactoryMaterialsRs.next()) { - Integer amount = foreignFactoryMaterialsRs.getInt("amount"); - int day = foreignFactoryMaterialsRs.getInt("dateTime"); - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - vo.setForeignFactoryMaterialsNum(amount); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(foreignFactoryMaterialsRs); - } - - //环氧刷洗包数 器械包分组为低温灭菌 - params.extraQuery = " and tdc.tousseGroupName='低温灭菌' and tdc.taskGroup='临床器械组' "; - String epoxyWashTousseSQL = String.format("select sum(tl.amount) amount,dateTime from (" - +dataIndex.getWorkAmountByPackageSQL("清洗数量", params) - + ") tl group by dateTime "); - - params.extraQuery = ""; - params.extraSelectColumns = ""; - params.groupBySql = ""; - ResultSet epoxyWashTousseRs = null; - try { - epoxyWashTousseRs = objectDao.executeSql(epoxyWashTousseSQL); - while (epoxyWashTousseRs.next()) { - Integer amount = epoxyWashTousseRs.getInt("amount"); - int day = epoxyWashTousseRs.getInt("dateTime"); - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - vo.setEpoxyWashTousseNum(amount); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(epoxyWashTousseRs); - } - - groupDay = dateQueryAdapter.dateConverAdapter3("i.sendTime","dd"); - params.extraSelectColumns = ","+groupDay +" dateTime,i.settleAccountsDepart "; - params.extraQuery = " and tdc.taskGroup='敷料组' "; - params.extraGroupBy = " i.settleAccountsDepart,"+groupDay; - - String dressingInvoiceSql = String.format("select sum(tl.amount) amount,settleAccountsDepart,dateTime from (" - +dataIndex.getWorkAmountByPackageSQL("发货数量", params) - + ") tl group by settleAccountsDepart,dateTime"); - ResultSet dressingInvoiceRs = null; - try { - dressingInvoiceRs = objectDao.executeSql(dressingInvoiceSql); - while (dressingInvoiceRs.next()) { - Integer amount = dressingInvoiceRs.getInt("amount"); - int day = dressingInvoiceRs.getInt("dateTime"); - String settleAccountsDepart = dressingInvoiceRs.getString("settleAccountsDepart"); - if(StringUtils.isNotBlank(settleAccountsDepart)){ - settleAccountsDepart = settleAccountsDepart.trim(); - } - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - if("介入导管室(南院)".equals(settleAccountsDepart)){ - vo.setJrdgsSouthHospitalInvoiceNum(amount); - }else if("介入导管室(北院)".equals(settleAccountsDepart)){ - vo.setJrdgsNorthHospitalInvoiceNum(amount); - }else if("第一手术室".equals(settleAccountsDepart)){ - vo.setDressingInvoiceNum1(amount); - }else if("第二手术室".equals(settleAccountsDepart)){ - vo.setDressingInvoiceNum2(amount); - }else if("第三手术室".equals(settleAccountsDepart)){ - vo.setDressingInvoiceNum3(amount); - }else{//其他手术室 - vo.setDressingInvoiceOtherNum(MathTools.add(vo.getDressingInvoiceOtherNum(), amount).intValue()); - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(dressingInvoiceRs); - } - params.extraSelectColumns = ","+groupDay +" dateTime,i.settleAccountsDepart "; - params.extraGroupBy = " i.settleAccountsDepart,"+groupDay; - params.extraQuery = " and i.invoicePlanType<>'"+ InvoicePlan.TYPE_PROXYDISINFECTION +"' and tdc.tousseType='"+ TousseDefinition.PACKAGE_TYPE_INSIDE +"' and tdc.taskGroup='手术器械组' and i.settleAccountsDepart in('第一手术室','第二手术室','第三手术室') "; - String handleNumSql = String.format("select sum(tl.amount) amount,settleAccountsDepart,dateTime from (" - +dataIndex.getWorkAmountByPackageSQL("发货数量", params) - + ") tl group by settleAccountsDepart,dateTime"); - ResultSet handleNumRs = null; - try { - handleNumRs = objectDao.executeSql(handleNumSql); - while (handleNumRs.next()) { - Integer amount = handleNumRs.getInt("amount"); - int day = handleNumRs.getInt("dateTime"); - String settleAccountsDepart = handleNumRs.getString("settleAccountsDepart"); - if(StringUtils.isNotBlank(settleAccountsDepart)){ - settleAccountsDepart = settleAccountsDepart.trim(); - } - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - if("第一手术室".equals(settleAccountsDepart)){ - vo.setHandleNum1(amount); - }else if("第二手术室".equals(settleAccountsDepart)){ - vo.setHandleNum2(amount); - }else if("第三手术室".equals(settleAccountsDepart)){ - vo.setHandleNum3(amount); - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(handleNumRs); - } - - params.extraSelectColumns = ","+groupDay +" dateTime,i.depart,tdc.taskGroup "; - params.extraGroupBy = " tdc.taskGroup,i.depart,"+groupDay; - params.extraQuery = " and i.invoicePlanType='"+ InvoicePlan.TYPE_PROXYDISINFECTION - +"' and tdc.tousseType='"+ TousseDefinition.PACKAGE_TYPE_INSIDE - + "' and (tdc.taskGroup='手术室器械组' and i.depart='第一手术室' or" - + " tdc.taskGroup='二手器械组' and i.depart='第二手术室' or" - + " tdc.taskGroup='三手器械组' and i.depart='第三手术室') "; - String proxySql = String.format("select sum(tl.amount) amount,depart,dateTime,taskGroup from (" - +dataIndex.getWorkAmountByPackageSQL("发货数量", params) - + ") tl group by depart,dateTime,taskGroup"); - params.extraSelectColumns = ""; - params.extraGroupBy = ""; - params.extraQuery = ""; - ResultSet proxyRs = null; - try { - proxyRs = objectDao.executeSql(proxySql); - while (proxyRs.next()) { - Integer amount = proxyRs.getInt("amount"); - int day = proxyRs.getInt("dateTime"); - String depart = proxyRs.getString("depart"); - if(StringUtils.isNotBlank(depart)){ - depart = depart.trim(); - } - String taskGroup = proxyRs.getString("taskGroup"); - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - if("第一手术室".equals(depart) && "手术室器械组".equals(taskGroup)){ - vo.setProxySterilizationNum1(MathTools.add(vo.getProxySterilizationNum1(), amount).intValue()); - }else if("第二手术室".equals(depart) && "二手器械组".equals(taskGroup)){ - vo.setProxySterilizationNum2(MathTools.add(vo.getProxySterilizationNum2(), amount).intValue()); - }else if("第三手术室".equals(depart) && "三手器械组".equals(taskGroup)){ - vo.setProxySterilizationNum3(MathTools.add(vo.getProxySterilizationNum3(), amount).intValue()); - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(proxyRs); - } - params.extraSelectColumns = ",case when td.tousseType='" - + TousseDefinition.PACKAGE_TYPE_FOREIGN - + "' then tdc.packageType when td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_SPLIT +"' " - + "then (select tdb.packageType from " - + TousseDefinition.class.getSimpleName() - +" tdb where tdb.id=td.parentID) end packageType," - + groupDay - +" dateTime,i.settleAccountsDepart "; - params.extraGroupBy = " tdc.packageType,i.settleAccountsDepart,"+groupDay+",td.tousseType ,td.parentID"; - params.extraQuery = " and tdc.tousseType in ('"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"','"+ TousseDefinition.PACKAGE_TYPE_SPLIT +"') " - + "and (td.packageType in('硬质容器','"+ nonWovenStr +"','"+ paperPlasticStr +"') and tdc.tousseType='"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"' " - + "or tdc.tousseType='"+ TousseDefinition.PACKAGE_TYPE_SPLIT +"' and td.parentID in " - + "(select id from TousseDefinition tdd where tdd.tousseType='"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"' " - + "and tdd.packageType in('硬质容器','"+ nonWovenStr +"','"+ paperPlasticStr +"'))) " - + "and i.settleAccountsDepart in('第一手术室','第二手术室') "; - String factorySql = String.format("select sum(tl.amount) amount,packageType,settleAccountsDepart,dateTime from (" - +dataIndex.getWorkAmountByPackageSQL("发货数量", params) - + ") tl group by packageType,settleAccountsDepart,dateTime"); - ResultSet factoryRs = null; - try { - factoryRs = objectDao.executeSql(factorySql); - while (factoryRs.next()) { - Integer amount = factoryRs.getInt("amount"); - int day = factoryRs.getInt("dateTime"); - String settleAccountsDepart = factoryRs.getString("settleAccountsDepart"); - if(StringUtils.isNotBlank(settleAccountsDepart)){ - settleAccountsDepart = settleAccountsDepart.trim(); - } - String packageType = factoryRs.getString("packageType"); - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - if("硬质容器".equals(packageType)){ - if("第一手术室".equals(settleAccountsDepart)){ - vo.setHardContainerFactoryTousseNum1(amount); - }else if("第二手术室".equals(settleAccountsDepart)){ - vo.setHardContainerFactoryTousseNum2(amount); - } - }else if(nonWovenStr.equals(packageType)){ - if("第一手术室".equals(settleAccountsDepart)){ - vo.setNonWovenFactoryTousseNum1(amount); - }else if("第二手术室".equals(settleAccountsDepart)){ - vo.setNonWovenFactoryTousseNum2(amount); - } - }else if(paperPlasticStr.equals(packageType)){ - if("第一手术室".equals(settleAccountsDepart)){ - vo.setPaperPlasticFactoryTousseNum1(amount); - }else if("第二手术室".equals(settleAccountsDepart)){ - vo.setPaperPlasticFactoryTousseNum2(amount); - } - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(handleNumRs); - } - //消毒物品发货件数 - params.extraSelectColumns = ",case when td.name like '%%湿化瓶%%' then 1 when td.name like '%%止血带%%' then 2 end goodType ,"+groupDay +" dateTime "; - params.groupBySql = " group by case when td.name like '%%湿化瓶%%' then 1 when td.name like '%%止血带%%' then 2 end,"+groupDay; - params.extraQuery = " and (td.name like '%%湿化瓶%%' or td.name like '%%止血带%%') and tdc.tousseType ='"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"' "; - String disinfectionSql = String.format("select sum(tl.amount) amount,goodType,dateTime from (" - +dataIndex.getWorkAmountByMaterialSQL("发货数量", params, dataSoureOfMaterialsCountOfToussesInReports) - + ") tl group by goodType,dateTime"); - ResultSet disinfectionRs = null; - try { - disinfectionRs = objectDao.executeSql(disinfectionSql); - while (disinfectionRs.next()) { - Integer amount = disinfectionRs.getInt("amount"); - int day = disinfectionRs.getInt("dateTime"); - int goodType = disinfectionRs.getInt("goodType"); - TousseWorkLoadVOForDLZXYY vo = null; - if(dayMap.containsKey(day)){ - vo = dayMap.get(day); - }else{ - vo = new TousseWorkLoadVOForDLZXYY(day + ""); - dayMap.put(day, vo); - } - if(goodType == 1){ - vo.setWettingBottleNum(amount); - }else{ - vo.setTourniquetNum(amount); - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(disinfectionRs); - } - //补充没有数据的天数 - for (int i = 1; i <= sumDay; i++) { - if(!dayMap.containsKey(i)){ - TousseWorkLoadVOForDLZXYY vo = new TousseWorkLoadVOForDLZXYY(i + ""); - dayMap.put(i, vo); - } - } - return Arrays.asList(dayMap.values().toArray()); + return tousseWorkLoadDataForDLZXYYHelper.getTousseWorkLoadDataForDLZXYY(yyyyMM); } @Override public List getInstrumentRepairReportData(String startTime, String endTime, String repairStatus Index: ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml =================================================================== diff -u -r35784 -r35788 --- ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml (.../applicationContext-service.xml) (revision 35784) +++ ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml (.../applicationContext-service.xml) (revision 35788) @@ -779,4 +779,5 @@ + \ No newline at end of file Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/TousseWorkLoadDataForDLZXYYHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/TousseWorkLoadDataForDLZXYYHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/TousseWorkLoadDataForDLZXYYHelper.java (revision 35788) @@ -0,0 +1,408 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.commons.lang.StringUtils; +import com.forgon.disinfectsystem.common.CssdUtils; +import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; +import com.forgon.disinfectsystem.entity.invoicemanager.InvoicePlan; +import com.forgon.disinfectsystem.jasperreports.javabeansource.TousseWorkLoadVOForDLZXYY; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; +import com.forgon.tools.MathTools; +import com.forgon.tools.date.DateTools; +import com.forgon.tools.db.DatabaseUtil; + +import edu.emory.mathcs.backport.java.util.Arrays; +/** + * 定制供应室工作量统计报表 + * @author zc.li 2023-05-04 + * + */ +public class TousseWorkLoadDataForDLZXYYHelper extends ReportHelper { + /** + * 获取定制供应室工作量统计报表 DLZXYY-11 + * @param yyyyMM 年月 如 2022-01 + * @return + */ + public List getTousseWorkLoadDataForDLZXYY(String yyyyMM) { + if(StringUtils.isBlank(yyyyMM)){ + return null; + } + String nonWovenStr = "无纺布包装"; + String paperPlasticStr = "纸塑包装"; + String yyyy = yyyyMM.substring(0,4); + String mm = yyyyMM.substring(5); + //本月最后一天 + String lastDay = DateTools.getLastDayOfMonthByDate(yyyy, mm); + //当月总天数 + Integer sumDay = Integer.valueOf(lastDay.substring(lastDay.length()-2)); + + String startDate = yyyyMM + "-01 00:00:00"; + //下个月第一天 + String endDate = DateTools.getFirstDayOfNextMonth(yyyy, mm) + " 00:00:00"; + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDate),dateQueryAdapter.dateAdapter(endDate)); + params.isGroup = true; + params.haveCombo = false; + int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); + //临床器械组 + String groupDay = dateQueryAdapter.dateConverAdapter3("endDate","dd"); + params.extraSelectColumns = ",tdc.packageType, " + groupDay + " dateTime "; + params.extraGroupBy = " group by tdc.packageType, "+ groupDay;// + params.extraQuery = " and tdc.packageType in ('"+ nonWovenStr +"','"+ paperPlasticStr +"') and tdc.taskGroup='临床器械组' "; + String washMaterialSQL = String.format("select packageType,sum(tl.amount) amount,dateTime from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by packageType,dateTime "); + params.extraGroupBy = ""; + ResultSet washMaterialRs = null; + Map dayMap = new HashMap(); + try { + washMaterialRs = objectDao.executeSql(washMaterialSQL); + while (washMaterialRs.next()) { + Integer amount = washMaterialRs.getInt("amount"); + int day = washMaterialRs.getInt("dateTime"); + String packageType = washMaterialRs.getString("packageType"); + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + if(nonWovenStr.equals(packageType)){ + vo.setNonWovenWashMaterialsNum(amount); + }else if(paperPlasticStr.equals(packageType)){ + vo.setPaperPlasticWashMaterialsNum(amount); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(washMaterialRs); + } + + params.groupBySql = " group by tdc.packageType,"+groupDay; + + //清洗包数 + String washTousseSQL = String.format("select packageType,sum(tl.amount) amount,dateTime from (" + +dataIndex.getWorkAmountByPackageSQL("清洗数量", params) + + ") tl group by packageType,dateTime "); + params.extraGroupBy = ""; + params.extraQuery = ""; + params.groupBySql = ""; + params.extraSelectColumns = ""; + ResultSet washTousseRs = null; + try { + washTousseRs = objectDao.executeSql(washTousseSQL); + while (washTousseRs.next()) { + Integer amount = washTousseRs.getInt("amount"); + int day = washTousseRs.getInt("dateTime"); + String packageType = washTousseRs.getString("packageType"); + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + if(nonWovenStr.equals(packageType)){ + vo.setNonWovenWashTousseNum(amount); + }else if(paperPlasticStr.equals(packageType)){ + vo.setPaperPlasticWashTousseNum(amount); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(washTousseRs); + } + params.extraSelectColumns = "," + groupDay + " dateTime "; + params.groupBySql = " group by "+groupDay; + //厂家器械清洗总件数 汇总所有外来器械包的清洗的总件数 + params.extraQuery = " and tdc.tousseType in ('"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"','"+ TousseDefinition.PACKAGE_TYPE_SPLIT +"') "; + String foreignFactoryMaterialsSQL = String.format("select sum(tl.amount) amount,dateTime from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by dateTime "); + ResultSet foreignFactoryMaterialsRs = null; + try { + foreignFactoryMaterialsRs = objectDao.executeSql(foreignFactoryMaterialsSQL); + while (foreignFactoryMaterialsRs.next()) { + Integer amount = foreignFactoryMaterialsRs.getInt("amount"); + int day = foreignFactoryMaterialsRs.getInt("dateTime"); + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + vo.setForeignFactoryMaterialsNum(amount); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(foreignFactoryMaterialsRs); + } + + //环氧刷洗包数 器械包分组为低温灭菌 + params.extraQuery = " and tdc.tousseGroupName='低温灭菌' and tdc.taskGroup='临床器械组' "; + String epoxyWashTousseSQL = String.format("select sum(tl.amount) amount,dateTime from (" + +dataIndex.getWorkAmountByPackageSQL("清洗数量", params) + + ") tl group by dateTime "); + + params.extraQuery = ""; + params.extraSelectColumns = ""; + params.groupBySql = ""; + ResultSet epoxyWashTousseRs = null; + try { + epoxyWashTousseRs = objectDao.executeSql(epoxyWashTousseSQL); + while (epoxyWashTousseRs.next()) { + Integer amount = epoxyWashTousseRs.getInt("amount"); + int day = epoxyWashTousseRs.getInt("dateTime"); + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + vo.setEpoxyWashTousseNum(amount); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(epoxyWashTousseRs); + } + + groupDay = dateQueryAdapter.dateConverAdapter3("i.sendTime","dd"); + params.extraSelectColumns = ","+groupDay +" dateTime,i.settleAccountsDepart "; + params.extraQuery = " and tdc.taskGroup='敷料组' "; + params.extraGroupBy = " i.settleAccountsDepart,"+groupDay; + + String dressingInvoiceSql = String.format("select sum(tl.amount) amount,settleAccountsDepart,dateTime from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by settleAccountsDepart,dateTime"); + ResultSet dressingInvoiceRs = null; + try { + dressingInvoiceRs = objectDao.executeSql(dressingInvoiceSql); + while (dressingInvoiceRs.next()) { + Integer amount = dressingInvoiceRs.getInt("amount"); + int day = dressingInvoiceRs.getInt("dateTime"); + String settleAccountsDepart = dressingInvoiceRs.getString("settleAccountsDepart"); + if(StringUtils.isNotBlank(settleAccountsDepart)){ + settleAccountsDepart = settleAccountsDepart.trim(); + } + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + if("介入导管室(南院)".equals(settleAccountsDepart)){ + vo.setJrdgsSouthHospitalInvoiceNum(amount); + }else if("介入导管室(北院)".equals(settleAccountsDepart)){ + vo.setJrdgsNorthHospitalInvoiceNum(amount); + }else if("第一手术室".equals(settleAccountsDepart)){ + vo.setDressingInvoiceNum1(amount); + }else if("第二手术室".equals(settleAccountsDepart)){ + vo.setDressingInvoiceNum2(amount); + }else if("第三手术室".equals(settleAccountsDepart)){ + vo.setDressingInvoiceNum3(amount); + }else{//其他手术室 + vo.setDressingInvoiceOtherNum(MathTools.add(vo.getDressingInvoiceOtherNum(), amount).intValue()); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(dressingInvoiceRs); + } + params.extraSelectColumns = ","+groupDay +" dateTime,i.settleAccountsDepart "; + params.extraGroupBy = " i.settleAccountsDepart,"+groupDay; + params.extraQuery = " and i.invoicePlanType<>'"+ InvoicePlan.TYPE_PROXYDISINFECTION +"' and tdc.tousseType='"+ TousseDefinition.PACKAGE_TYPE_INSIDE +"' and tdc.taskGroup='手术器械组' and i.settleAccountsDepart in('第一手术室','第二手术室','第三手术室') "; + String handleNumSql = String.format("select sum(tl.amount) amount,settleAccountsDepart,dateTime from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by settleAccountsDepart,dateTime"); + ResultSet handleNumRs = null; + try { + handleNumRs = objectDao.executeSql(handleNumSql); + while (handleNumRs.next()) { + Integer amount = handleNumRs.getInt("amount"); + int day = handleNumRs.getInt("dateTime"); + String settleAccountsDepart = handleNumRs.getString("settleAccountsDepart"); + if(StringUtils.isNotBlank(settleAccountsDepart)){ + settleAccountsDepart = settleAccountsDepart.trim(); + } + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + if("第一手术室".equals(settleAccountsDepart)){ + vo.setHandleNum1(amount); + }else if("第二手术室".equals(settleAccountsDepart)){ + vo.setHandleNum2(amount); + }else if("第三手术室".equals(settleAccountsDepart)){ + vo.setHandleNum3(amount); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(handleNumRs); + } + + params.extraSelectColumns = ","+groupDay +" dateTime,i.depart,tdc.taskGroup "; + params.extraGroupBy = " tdc.taskGroup,i.depart,"+groupDay; + params.extraQuery = " and i.invoicePlanType='"+ InvoicePlan.TYPE_PROXYDISINFECTION + +"' and tdc.tousseType='"+ TousseDefinition.PACKAGE_TYPE_INSIDE + + "' and (tdc.taskGroup='手术室器械组' and i.depart='第一手术室' or" + + " tdc.taskGroup='二手器械组' and i.depart='第二手术室' or" + + " tdc.taskGroup='三手器械组' and i.depart='第三手术室') "; + String proxySql = String.format("select sum(tl.amount) amount,depart,dateTime,taskGroup from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by depart,dateTime,taskGroup"); + params.extraSelectColumns = ""; + params.extraGroupBy = ""; + params.extraQuery = ""; + ResultSet proxyRs = null; + try { + proxyRs = objectDao.executeSql(proxySql); + while (proxyRs.next()) { + Integer amount = proxyRs.getInt("amount"); + int day = proxyRs.getInt("dateTime"); + String depart = proxyRs.getString("depart"); + if(StringUtils.isNotBlank(depart)){ + depart = depart.trim(); + } + String taskGroup = proxyRs.getString("taskGroup"); + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + if("第一手术室".equals(depart) && "手术室器械组".equals(taskGroup)){ + vo.setProxySterilizationNum1(MathTools.add(vo.getProxySterilizationNum1(), amount).intValue()); + }else if("第二手术室".equals(depart) && "二手器械组".equals(taskGroup)){ + vo.setProxySterilizationNum2(MathTools.add(vo.getProxySterilizationNum2(), amount).intValue()); + }else if("第三手术室".equals(depart) && "三手器械组".equals(taskGroup)){ + vo.setProxySterilizationNum3(MathTools.add(vo.getProxySterilizationNum3(), amount).intValue()); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(proxyRs); + } + params.extraSelectColumns = ",case when td.tousseType='" + + TousseDefinition.PACKAGE_TYPE_FOREIGN + + "' then tdc.packageType when td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_SPLIT +"' " + + "then (select tdb.packageType from " + + TousseDefinition.class.getSimpleName() + +" tdb where tdb.id=td.parentID) end packageType," + + groupDay + +" dateTime,i.settleAccountsDepart "; + params.extraGroupBy = " tdc.packageType,i.settleAccountsDepart,"+groupDay+",td.tousseType ,td.parentID"; + params.extraQuery = " and tdc.tousseType in ('"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"','"+ TousseDefinition.PACKAGE_TYPE_SPLIT +"') " + + "and (td.packageType in('硬质容器','"+ nonWovenStr +"','"+ paperPlasticStr +"') and tdc.tousseType='"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"' " + + "or tdc.tousseType='"+ TousseDefinition.PACKAGE_TYPE_SPLIT +"' and td.parentID in " + + "(select id from TousseDefinition tdd where tdd.tousseType='"+ TousseDefinition.PACKAGE_TYPE_FOREIGN +"' " + + "and tdd.packageType in('硬质容器','"+ nonWovenStr +"','"+ paperPlasticStr +"'))) " + + "and i.settleAccountsDepart in('第一手术室','第二手术室') "; + String factorySql = String.format("select sum(tl.amount) amount,packageType,settleAccountsDepart,dateTime from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by packageType,settleAccountsDepart,dateTime"); + ResultSet factoryRs = null; + try { + factoryRs = objectDao.executeSql(factorySql); + while (factoryRs.next()) { + Integer amount = factoryRs.getInt("amount"); + int day = factoryRs.getInt("dateTime"); + String settleAccountsDepart = factoryRs.getString("settleAccountsDepart"); + if(StringUtils.isNotBlank(settleAccountsDepart)){ + settleAccountsDepart = settleAccountsDepart.trim(); + } + String packageType = factoryRs.getString("packageType"); + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + if("硬质容器".equals(packageType)){ + if("第一手术室".equals(settleAccountsDepart)){ + vo.setHardContainerFactoryTousseNum1(amount); + }else if("第二手术室".equals(settleAccountsDepart)){ + vo.setHardContainerFactoryTousseNum2(amount); + } + }else if(nonWovenStr.equals(packageType)){ + if("第一手术室".equals(settleAccountsDepart)){ + vo.setNonWovenFactoryTousseNum1(amount); + }else if("第二手术室".equals(settleAccountsDepart)){ + vo.setNonWovenFactoryTousseNum2(amount); + } + }else if(paperPlasticStr.equals(packageType)){ + if("第一手术室".equals(settleAccountsDepart)){ + vo.setPaperPlasticFactoryTousseNum1(amount); + }else if("第二手术室".equals(settleAccountsDepart)){ + vo.setPaperPlasticFactoryTousseNum2(amount); + } + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(handleNumRs); + } + //消毒物品发货件数 + params.extraSelectColumns = ",case when td.name like '%%湿化瓶%%' then 1 when td.name like '%%止血带%%' then 2 end goodType ,"+groupDay +" dateTime "; + params.groupBySql = " group by case when td.name like '%%湿化瓶%%' then 1 when td.name like '%%止血带%%' then 2 end,"+groupDay; + params.extraQuery = " and (td.name like '%%湿化瓶%%' or td.name like '%%止血带%%') and tdc.tousseType ='"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"' "; + String disinfectionSql = String.format("select sum(tl.amount) amount,goodType,dateTime from (" + +dataIndex.getWorkAmountByMaterialSQL("发货数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by goodType,dateTime"); + ResultSet disinfectionRs = null; + try { + disinfectionRs = objectDao.executeSql(disinfectionSql); + while (disinfectionRs.next()) { + Integer amount = disinfectionRs.getInt("amount"); + int day = disinfectionRs.getInt("dateTime"); + int goodType = disinfectionRs.getInt("goodType"); + TousseWorkLoadVOForDLZXYY vo = null; + if(dayMap.containsKey(day)){ + vo = dayMap.get(day); + }else{ + vo = new TousseWorkLoadVOForDLZXYY(day + ""); + dayMap.put(day, vo); + } + if(goodType == 1){ + vo.setWettingBottleNum(amount); + }else{ + vo.setTourniquetNum(amount); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(disinfectionRs); + } + //补充没有数据的天数 + for (int i = 1; i <= sumDay; i++) { + if(!dayMap.containsKey(i)){ + TousseWorkLoadVOForDLZXYY vo = new TousseWorkLoadVOForDLZXYY(i + ""); + dayMap.put(i, vo); + } + } + return Arrays.asList(dayMap.values().toArray()); + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ForeignTousseApplicationReportHelper.java =================================================================== diff -u -r35785 -r35788 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ForeignTousseApplicationReportHelper.java (.../ForeignTousseApplicationReportHelper.java) (revision 35785) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ForeignTousseApplicationReportHelper.java (.../ForeignTousseApplicationReportHelper.java) (revision 35788) @@ -38,13 +38,7 @@ import com.forgon.tools.string.StringTools; import com.forgon.tools.util.SqlUtils; -public class ForeignTousseApplicationReportHelper { - @Autowired - private DateQueryAdapter dateQueryAdapter; - @Autowired - private ObjectDao objectDao; - @Autowired - private InitDbConnection dbConnection; +public class ForeignTousseApplicationReportHelper extends ReportHelper { /** * 外来器械申请单统计报表 * @param timeType 时间类型