Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java =================================================================== diff -u -r41316 -r41326 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java (.../RealTimeBulletinBoardWorkloadHelper.java) (revision 41316) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java (.../RealTimeBulletinBoardWorkloadHelper.java) (revision 41326) @@ -27,9 +27,11 @@ import com.forgon.directory.model.BarcodeDevice; import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager; +import com.forgon.disinfectsystem.common.CssdUtils; import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoods; import com.forgon.disinfectsystem.entity.basedatamanager.cleanmethod.CleanMethod; import com.forgon.disinfectsystem.entity.basedatamanager.container.Container; +import com.forgon.disinfectsystem.entity.basedatamanager.materialdefinition.MaterialDefinition; import com.forgon.disinfectsystem.entity.basedatamanager.rinser.Rinser; import com.forgon.disinfectsystem.entity.basedatamanager.sterilisation.Sterilisation; import com.forgon.disinfectsystem.entity.basedatamanager.sterilizer.Sterilizer; @@ -42,6 +44,8 @@ import com.forgon.disinfectsystem.entity.foreigntousseapplication.ForeignTousseApplication; import com.forgon.disinfectsystem.entity.invoicemanager.Invoice; import com.forgon.disinfectsystem.entity.invoicemanager.InvoicePlan; +import com.forgon.disinfectsystem.entity.invoicemanager.MaterialInvoice; +import com.forgon.disinfectsystem.entity.invoicemanager.MaterialInvoiceItem; import com.forgon.disinfectsystem.entity.packing.PackingTask; import com.forgon.disinfectsystem.entity.packing.ReviewedBasket; import com.forgon.disinfectsystem.entity.qualitymonitoringmanager.qualitymonitoring.QualityMonitoringInstance; @@ -53,13 +57,14 @@ import com.forgon.disinfectsystem.entity.urgent.UrgentLevel; import com.forgon.disinfectsystem.entity.useRecord.UseRecord; import com.forgon.disinfectsystem.entity.washanddisinfectmanager.washanddisinfectrecord.WashAndDisinfectRecord; +import com.forgon.disinfectsystem.jasperreports.javabeansource.StatisticalWorkload; import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; +import com.forgon.exception.SystemException; import com.forgon.tools.MathTools; import com.forgon.tools.date.DateTools; import com.forgon.tools.db.DatabaseUtil; import com.forgon.tools.json.JSONUtil; import com.forgon.tools.string.StringTools; -import com.forgon.tools.util.ConfigUtils; import com.forgon.tools.util.SqlUtils; /** * 数据看板 @@ -69,6 +74,8 @@ public class RealTimeBulletinBoardWorkloadHelper extends ReportHelper{ @Autowired protected SupplyRoomConfigManager supplyRoomConfigManager; + @Autowired + private ReportSqlUtil reportSqlUtil; /** * 获取数据看板工作量 * @return @@ -1019,12 +1026,17 @@ return packingSql; } /** - * 获取设备使用情况 + * + * @param displayMod 显示模式 为1是,只需要显示当天产生过清洗或灭菌记录的设备 * @return */ - public List getEquipmentData() { + public List getEquipmentData(String displayMod) { + String ymd = DateTools.YMDFORMAT.get().format(new Date()); + String[] dates = supplyRoomConfigManager.getStartDateAndEndDate(null, ymd); + + String displayModBetweenSql = " between " + dateQueryAdapter.dateAdapter(dates[0]) + " and " + dateQueryAdapter.dateAdapter(dates[1]); StringBuffer sbf = new StringBuffer(); - sbf.append("select status,dateStr,name from (select 1 orderNum,case when exists (select wr.id from "); + sbf.append("select status,dateStr,name,runCount from (select 1 orderNum,case when exists (select wr.id from "); sbf.append(WashAndDisinfectRecord.class.getSimpleName()); sbf.append(" wr where wr.rinserId=r.id and wr.washStatus='"); sbf.append(WashAndDisinfectRecord.STATUS_WASHING); @@ -1042,11 +1054,25 @@ sbf.append(CleanMethod.class.getSimpleName()); sbf.append(" c on c.cleanMethod=wr.disinfectProgram where wr.rinserId=r.id and wr.washStatus='"); sbf.append(WashAndDisinfectRecord.STATUS_WASHING); - sbf.append("') dateStr,r.name from "); + sbf.append("') dateStr,r.name "); + sbf.append(",(select count(*) from " + + WashAndDisinfectRecord.class.getSimpleName() + +" wdr11 where wdr11.rinserId=r.id and wdr11.startDate " + + displayModBetweenSql + + ") runCount"); + sbf.append(" from "); sbf.append(Rinser.class.getSimpleName()); sbf.append(" r where r.useStatus='"); sbf.append(Rinser.USESTATUS_IN_USE); - sbf.append("' and r.orgUnitCoding in (select max(orgUnitCoding) from "); + sbf.append("' "); + if("1".equals(displayMod)){ + sbf.append(" and exists (select 1 from "); + sbf.append(WashAndDisinfectRecord.class.getSimpleName()); + sbf.append(" wdr1 where wdr1.rinserId=r.id and wdr1.startDate "); + sbf.append(displayModBetweenSql); + sbf.append(") "); + } + sbf.append(" and r.orgUnitCoding in (select max(orgUnitCoding) from "); sbf.append(SupplyRoomConfig.class.getSimpleName()); sbf.append(" where supplyRoomType=1) union all select 2 orderNum,case when exists (select id from "); sbf.append(SterilizationRecord.class.getSimpleName()); @@ -1066,18 +1092,39 @@ sbf.append(Sterilisation.class.getSimpleName()); sbf.append(" sn on sn.sterilisation=sr.sterilizationType where sr.sterilizer_id=s.id and sr.status='"); sbf.append(SterilizationRecord.STERILIZATION_STATUS_BEGIN); - sbf.append("') dateStr,s.name from "); + sbf.append("') dateStr,s.name "); + sbf.append(",(select 1 from "); + sbf.append(SterilizationRecord.class.getSimpleName()); + sbf.append(" sr11 where sr11.sterilizer_id=s.id and sr11.startDate "); + sbf.append(displayModBetweenSql); + sbf.append(") runCount "); + sbf.append(" from "); sbf.append(Sterilizer.class.getSimpleName()); sbf.append(" s where s.useStatus='"); sbf.append(Sterilizer.USESTATUS_IN_USE); - sbf.append("' and s.orgUnitCoding in (select max(orgUnitCoding) from "); + sbf.append("' "); + if("1".equals(displayMod)){ + sbf.append(" and exists (select 1 from "); + sbf.append(SterilizationRecord.class.getSimpleName()); + sbf.append(" sr1 where sr1.sterilizer_id=s.id and sr1.startDate "); + sbf.append(displayModBetweenSql); + sbf.append(") "); + } + sbf.append(" and s.orgUnitCoding in (select max(orgUnitCoding) from "); sbf.append(SupplyRoomConfig.class.getSimpleName() ); sbf.append(" where supplyRoomType=1) ) t order by orderNum, name "); List arr = new ArrayList(); setEquipmentData(arr, sbf.toString()); return arr; } /** + * 获取设备使用情况 + * @return + */ + public List getEquipmentData() { + return getEquipmentData(null); + } + /** * 查询设置设备使用情况数据 * @param arr 设备数据 * @param sql 查询语句 @@ -1094,7 +1141,8 @@ obj.put("dateStr", sdf.format(date)); } obj.put("name", rs.getString("name")); - obj.put("status", rs.getString("status")); + obj.put("status", rs.getString("status")); + obj.put("runCount", rs.getInt("runCount")); arr.add(obj); } } catch (SQLException e) { @@ -1859,6 +1907,193 @@ return obj.toString(); } /** + * 员工工作量统计数据 + * @param querySupplyRoom 供应室 + * @param link 环节 + * @return + */ + private List getStatisticalWorkloadData(String querySupplyRoom, String link){ + List list = new ArrayList(); + if(StringUtils.isBlank(link)){ + throw new SystemException("环节不能为空"); + } + String[] startDateAndEndDate = supplyRoomConfigManager.getStartDateAndEndDate(null, DateTools.YMDFORMAT.get().format(new Date())); + + String startDateTime = startDateAndEndDate[0]; + String endDateTime = startDateAndEndDate[1]; + + //查询的时间段 + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); + + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = betweenSql; + params.querySupplyRoom = querySupplyRoom; + params.selectUserName = true; + params.isGroup = true; + String statisticTousseWorkLoadIncludeDisposableGoodsAmountStr = CssdUtils.getSystemSetConfigByName("statisticTousseWorkLoadIncludeDisposableGoodsAmount"); + JSONObject statisticTousseWorkLoadIncludeDisposableGoodsAmountObj = null; + if(StringUtils.isNotBlank(statisticTousseWorkLoadIncludeDisposableGoodsAmountStr)){ + statisticTousseWorkLoadIncludeDisposableGoodsAmountObj = JSONObject.fromObject(statisticTousseWorkLoadIncludeDisposableGoodsAmountStr); + } + //按包数量统计的SQL + //回收记录 + String tousseAmountSql = ""; + if("预回收数量".equals(link)){ + tousseAmountSql = String.format("select '预回收数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByPackageSQL("预回收数量", params) + + ") tl group by tl.userName "); + }else if("回收数量".equals(link)){ + tousseAmountSql = String.format("select '回收数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByPackageSQL("回收数量", params) + + ") tl group by tl.userName "); + }else if("清洗数量".equals(link)){ + tousseAmountSql = String.format("select '清洗数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByPackageSQL("清洗数量", params) + + ") tl group by tl.userName "); + }else if("配包数量".equals(link)){ + tousseAmountSql = String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByPackageSQL("配包数量", params) + + ") tl group by tl.userName "); + }else if("审核数量".equals(link)){ + tousseAmountSql = String.format("select '审核数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByPackageSQL("审核数量", params) + + ") tl group by tl.userName "); + }else if("灭菌数量".equals(link)){ + tousseAmountSql = String.format("select '灭菌数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + + ") tl group by tl.userName "); + }else if("发货数量".equals(link)){ + tousseAmountSql = String.format("select '发货数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl group by tl.userName "); + }else{ + throw new SystemException("暂不支持的环节"+link); + } + try (ResultSet rs = objectDao.executeSql(tousseAmountSql)){ + while (rs.next()) { + String type = rs.getString("type"); + String userName = rs.getString("userName"); + int amount = rs.getInt("amount"); + StatisticalWorkload workLoadBean = new StatisticalWorkload(); + workLoadBean.setOperator(userName); + workLoadBean.setAmount(amount); + workLoadBean.setColumnName(type); + workLoadBean.setSequence(1); + list.add(workLoadBean); + } + } catch (Exception e) { + e.printStackTrace(); + } + int dataSoureOfMaterialsCountOfToussesInReports = CssdUtils.getSystemSetConfigByNameInt("dataSoureOfMaterialsCountOfToussesInReports", 3); + // 按材料数量统计的SQL + //回收记录 + String materialAmountSql = ""; + if("预回收数量".equals(link)){ + materialAmountSql = String.format("select '预回收数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("预回收数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by tl.userName "); + }else if("回收数量".equals(link)){ + materialAmountSql = String.format("select '回收数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("回收数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by tl.userName "); + }else if("清洗数量".equals(link)){ + materialAmountSql = String.format("select '清洗数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("清洗数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by tl.userName "); + }else if("配包数量".equals(link)){ + if(statisticTousseWorkLoadIncludeDisposableGoodsAmountObj !=null){ + params.includeDisposableGoodsInTousse = statisticTousseWorkLoadIncludeDisposableGoodsAmountObj.optBoolean("装配", true); + }else{ + params.includeDisposableGoodsInTousse = true; + } + //装配记录（配包数量） + materialAmountSql = String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("配包数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by tl.userName "); + }else if("审核数量".equals(link)){ + if(statisticTousseWorkLoadIncludeDisposableGoodsAmountObj !=null){ + params.includeDisposableGoodsInTousse = statisticTousseWorkLoadIncludeDisposableGoodsAmountObj.optBoolean("审核", true); + }else{ + params.includeDisposableGoodsInTousse = true; + } + materialAmountSql = String.format("select '审核数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("审核数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by tl.userName "); + }else if("灭菌数量".equals(link)){ + if(statisticTousseWorkLoadIncludeDisposableGoodsAmountObj !=null){ + params.includeDisposableGoodsInTousse = statisticTousseWorkLoadIncludeDisposableGoodsAmountObj.optBoolean("灭菌", true); + }else{ + params.includeDisposableGoodsInTousse = true; + } + materialAmountSql = String.format("select '灭菌数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("灭菌数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by tl.userName "); + }else if("发货数量".equals(link)){ + // 包含一次性物品的发货统计 + params.includeDisposableGoods = false; + if(statisticTousseWorkLoadIncludeDisposableGoodsAmountObj !=null){ + params.includeDisposableGoodsInTousse = statisticTousseWorkLoadIncludeDisposableGoodsAmountObj.optBoolean("发货", false); + }else{ + params.includeDisposableGoodsInTousse = false; + } + params.extraSelectColumns = ",i.sender userName"; + params.groupBySql = " group by i.sender " ; + materialAmountSql = String.format("select '发货数量' type,tl.userName,sum(tl.amount) amount from (" + +dataIndex.getWorkAmountByMaterialSQL("发货数量", params, dataSoureOfMaterialsCountOfToussesInReports) + + ") tl group by tl.userName "); + } + try(ResultSet materialAmountRs = objectDao.executeSql(materialAmountSql)) { + while (materialAmountRs.next()) { + String type = materialAmountRs.getString("type"); + String userName = materialAmountRs.getString("userName"); + int amount = materialAmountRs.getInt("amount"); + StatisticalWorkload workLoadBean = new StatisticalWorkload(); + workLoadBean.setOperator(userName); + workLoadBean.setMaterialAmount(amount); + workLoadBean.setColumnName(type); + workLoadBean.setSequence(1); + list.add(workLoadBean); + } + } catch (Exception e) { + e.printStackTrace(); + } + + //材料发货,也计算到发货数量的包数和件数上 CDSLQYQYYY-84 + if ("发货数量".equals(link)) { + StringBuilder sbr = new StringBuilder(); + sbr.append("select sum(ii.amount) amount,sender from ") + .append(MaterialInvoiceItem.class.getSimpleName()) + .append(dbConnection.getNoLockSql()) + .append(" ii inner join ") + .append(MaterialInvoice.class.getSimpleName()) + .append(dbConnection.getNoLockSql()) + .append(" i on i.id = ii.materialInvoice_id ") + .append("inner join ") + .append(MaterialDefinition.class.getSimpleName()) + .append(dbConnection.getNoLockSql()) + .append(" d on ii.materialDefinitionId = d.id where i.sendtime ") + .append(params.betweenSql) + .append(SqlUtils.get_InSql_Extra("i.orgUnitCoding", params.querySupplyRoom)) + .append("group by sender"); + try(ResultSet materialInvoice = objectDao.executeSql(sbr.toString())){ + while (materialInvoice.next()) { + String sender = materialInvoice.getString("sender"); + int amount = materialInvoice.getInt("amount"); + StatisticalWorkload workLoadBean = new StatisticalWorkload(); + workLoadBean.setOperator(sender); + workLoadBean.setAmount(amount); + workLoadBean.setColumnName("发货数量"); + workLoadBean.setSequence(1); + workLoadBean.setMaterialAmount(amount); + list.add(workLoadBean); + } + } catch (Exception e) { + e.printStackTrace(); + } + } + return list; + } + /** * 获取数据看板数据源 * @param requestParameters * @return @@ -1880,7 +2115,8 @@ result = resultObj.toString(); } }else if("deviceusagesummary".equalsIgnoreCase(dataSources)){//设备使用情况汇总 - List list = getEquipmentData(); + String displayMod = requestParameters.get("displayMod"); + List list = getEquipmentData(displayMod); if(CollectionUtils.isNotEmpty(list)){ result = list.toString(); } @@ -1902,6 +2138,9 @@ if(resultObj != null){ result = resultObj.toString(); } + }else if("statisticalWorkload".equalsIgnoreCase(dataSources)){//此数据源用于统计在各个环节的员工工作量的数据并展示在实时数据看板中 + List list = getStatisticalWorkloadData(requestParameters.get("querySupplyRoom"),requestParameters.get("link")); + return JSONArray.fromObject(list).toString(); }else if("urgentgoodsdetails".equalsIgnoreCase(dataSources)){//加急物品明细追溯模式 return getUrgentGoodsDetails(); }else if("urgenttoussehandleinfo".equalsIgnoreCase(dataSources)){