Index: ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/service/SystemWarningManagerImpl.java =================================================================== diff -u -r38723 -r39374 --- ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/service/SystemWarningManagerImpl.java (.../SystemWarningManagerImpl.java) (revision 38723) +++ ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/service/SystemWarningManagerImpl.java (.../SystemWarningManagerImpl.java) (revision 39374) @@ -695,24 +695,44 @@ private String loadExpiredTousseAmountSql(String orgUnitCoding, int roomType) { String expiredTousseAmountSql = ""; if(roomType == 1 || roomType == 2){ - //供应室处理的已灭菌的已经过期器械包实例数量 - String sql1 = getExpiredToussesCount(true, orgUnitCoding, true); - //发货到供应室的已发货已签收的已经过期包实例数量(一次发货) - String sql2 = getExpiredToussesCount(false, orgUnitCoding, true); - //发货到供应室的已发货已签收的已经过期包实例数量(二次发货) - String sql3 = getExpiredToussesCount(false, orgUnitCoding, false); - expiredTousseAmountSql = String.format("select sum(valid.B) from (%s union all %s union all %s ) valid ", sql1, sql2, sql3); + expiredTousseAmountSql = buildExpiredToussesCountSql(true, orgUnitCoding); }else{ - //临床科室的已发货已签收的已经过期包实例数量(一次发货) - String sql1 = getExpiredToussesCount(false, orgUnitCoding, true); - //临床科室的已发货已签收的已经过期包实例数量(二次发货) - String sql2 = getExpiredToussesCount(false, orgUnitCoding, false); - expiredTousseAmountSql = String.format("select sum(valid.B) from (%s union all %s ) valid ", sql1, sql2); + expiredTousseAmountSql = buildExpiredToussesCountSql(false, orgUnitCoding); } return expiredTousseAmountSql; } /** + * 构建当前登录科室查询过期物品的语句 + * @param orgUnitCoding + * @return + */ + private String buildExpiredToussesCountSql(boolean isFirstOrSecondSupplyRoom, String orgUnitCoding) { + String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()); + String sql = ""; + //当前登录科室为供应室时，查询供应室已经灭菌但是没有发货的即将过期器械包 + String firstOrSecondSupplyRoomSql = ""; + if(isFirstOrSecondSupplyRoom){ + firstOrSecondSupplyRoomSql = String.format("or (po.orgUnitCoding='%s' and po.status='%s')", orgUnitCoding, TousseInstance.STATUS_STERILED); + } + //当前登录科室为临床科室时，查询临床科室已发货、已签收的即将过期器械包 + String tousseStatusSql = String.format(" and ((po.status in ('%s','%s') and case when po.invoice2_id is not null then po.location_2 else po.location end ='%s') %s)", + TousseInstance.STATUS_SHIPPED, TousseInstance.STATUS_SIGNED, + orgUnitCoding, firstOrSecondSupplyRoomSql); + + sql = "select count(1) B from " + TousseInstance.class.getSimpleName() + " po join " + TousseDefinition.class.getSimpleName() + " td on po.tousseDefinition_id = td.id " + + " where po.validUntil < " + + dateQueryAdapter.dateAdapter(date) + + tousseStatusSql + + " and not exists (select 1 from " + TousseDefinition.class.getSimpleName() + + " tdAcestor where td.ancestorID = tdAcestor.id and tdAcestor.warningType in ('" + TousseDefinition.WARNING_TYPE_NEVERWARN + "','" + TousseDefinition.WARNING_TYPE_TOUSSE + "')) " + // 聚合包内的普通包实例不显示出来 + + " and po.comboTousseInstanceId is null "; + return sql; + + } + + /** * 返回查询即将过期的器械包数量的语句 * @param orgUnitCoding * @param roomType @@ -721,24 +741,57 @@ private String loadWarningTousseAmountSql(String orgUnitCoding, int roomType) { String warningTousseAmountSql = ""; if(roomType == 1 || roomType == 2){ - //供应室处理的已灭菌的即将过期器械包实例数量 - String sql1 = getWarningTousseGoodsSQL(true, orgUnitCoding, true); - //发货到供应室的已发货已签收的即将过期包实例数量(一次发货) - String sql2 = getWarningTousseGoodsSQL(false, orgUnitCoding, true); - //发货到供应室的已发货已签收的即将过期包实例数量(二次发货) - String sql3 = getWarningTousseGoodsSQL(false, orgUnitCoding, false); - warningTousseAmountSql = String.format("select sum(warn.A) from (%s union all %s union all %s ) warn ", sql1, sql2, sql3); + warningTousseAmountSql = buildWarningTousseGoodsSQL(true, orgUnitCoding); }else{ - //临床科室的已发货已签收的即将过期包实例数量(一次发货) - String sql2 = getWarningTousseGoodsSQL(false, orgUnitCoding, true); - //临床科室的已发货已签收的即将过期包实例数量(二次发货) - String sql3 = getWarningTousseGoodsSQL(false, orgUnitCoding, false); - warningTousseAmountSql = String.format("select sum(warn.A) from (%s union all %s ) warn ", sql2, sql3); + warningTousseAmountSql = buildWarningTousseGoodsSQL(false, orgUnitCoding); } return warningTousseAmountSql; } /** + * 构建当前登录科室告警器械包数量的查询语句 + * @param isFirstOrSecondSupplyRoom + * @param orgUnitCoding + * @return + */ + private String buildWarningTousseGoodsSQL(boolean isFirstOrSecondSupplyRoom, String orgUnitCoding) { + + //已过失效期的时间(即当前时间(含时分秒)) + String nowDateForValidUtil = ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS.format(new Date()); + //已过预警期但未过失效期的时间(即当前时间(含时分秒)) + String nowDateForWarningUtil = nowDateForValidUtil; + //如果配置项预警时间只需要到天,不用到时分秒时 + if(CssdUtils.getSystemSetConfigByNameBool("warningUtilOnlyConsiderDate")){ + nowDateForWarningUtil = ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDD.format(new Date()); + } + + //当前登录科室为供应室时，查询供应室已经灭菌但是没有发货的即将过期器械包 + String firstOrSecondSupplyRoomSql = ""; + if(isFirstOrSecondSupplyRoom){ + firstOrSecondSupplyRoomSql = String.format(" or (po.orgUnitCoding='%s' and po.status='%s') ", orgUnitCoding, TousseInstance.STATUS_STERILED); + } + + //当前登录科室为临床科室时，查询临床科室已发货、已签收的即将过期器械包 + String tousseStatusSql = String.format(" and ((po.status in ('%s','%s') and case when po.invoice2_id is not null then po.location_2 else po.location end = '%s') %s) ", + TousseInstance.STATUS_STERILED, TousseInstance.STATUS_SIGNED, + orgUnitCoding, firstOrSecondSupplyRoomSql); + + String sql = null; + sql = "select count(1) A from " + TousseInstance.class.getSimpleName() + " po join " + TousseDefinition.class.getSimpleName() + " td on po.tousseDefinition_id = td.id " + + " where po.validUntil > " + + dateQueryAdapter.dateAdapter(nowDateForValidUtil) + + " and po.warningUntil < " + + dateQueryAdapter.dateAdapter(nowDateForWarningUtil) + + tousseStatusSql + + "and not exists (select 1 from " + TousseDefinition.class.getSimpleName() + + " tdAcestor where tdAcestor.id = td.ancestorID and tdAcestor.warningType in ('" + TousseDefinition.WARNING_TYPE_NEVERWARN + "','" + TousseDefinition.WARNING_TYPE_DISABLED_TOUSSE + "'))" + //聚合包内的普通包实例不显示出来 + + " and po.comboTousseInstanceId is null " + ; + return sql; + } + + /** * 小于最小库存的一次性物品 * @param orgUnitCode * @return Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java =================================================================== diff -u -r39254 -r39374 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java (.../TousseInstance.java) (revision 39254) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java (.../TousseInstance.java) (revision 39374) @@ -115,12 +115,14 @@ //加速包件混合的发货物品查询 ,@Index(columnList = "invoice_id,id,tousseDefinition_id,invoice2_id,materialAmount", name = "tis_iitim_index") //系统告警查询优化 -,@Index(columnList = "location,comboTousseInstanceId,invoice2_id,status,validUntil", name = "tis_lcisv_index") +//,@Index(columnList = "location,comboTousseInstanceId,invoice2_id,status,validUntil", name = "tis_lcisv_index") //报表数据查询优化 //,@Index(columnList = "orgUnitCoding,comboTousseDefinitionId,comboTousseInstanceId,operationTime,tousseDefinition_id,id,operator,materialAmount,disposableGoodAmount", name = "ti_occotiomd_index") //,@Index(columnList = "orgUnitCoding,reviewTime,comboTousseInstanceId,comboTousseDefinitionId,reviewer,id,tousseDefinition_id,materialAmount,disposableGoodAmount", name = "ti_ou_rt_ctii_ctd_r_id_td") ,@Index(columnList = "orgUnitCoding,operationTime,operator,reviewTime,reviewer,comboTousseDefinitionId,comboTousseInstanceId,tousseDefinition_id,id,materialAmount,disposableGoodAmount", name = "ouc_ot_o_rt_r_ci_ci_td_m_d") -,@Index(columnList = "sterilizationRecord_id,comboTousseDefinitionId,comboTousseInstanceId,id,tousseDefinition_id,materialAmount,disposableGoodAmount", name = "ti_sr_ctdi_ctii_id_td")}) +,@Index(columnList = "sterilizationRecord_id,comboTousseDefinitionId,comboTousseInstanceId,id,tousseDefinition_id,materialAmount,disposableGoodAmount", name = "ti_sr_ctdi_ctii_id_td") +//系统告警查询优化 +,@Index(columnList = "comboTousseInstanceId, validUntil, warningUntil, invoice2_id, location, location_2, orgUnitCoding, status, tousseDefinition_id", name = "tis_warning_index")}) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) @JsonFilter("fieldFilter") public class TousseInstance extends BarcodeDevice implements Comparable,IDAble,Cloneable{ Index: ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/action/TousseInstanceAction.java =================================================================== diff -u -r39366 -r39374 --- ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/action/TousseInstanceAction.java (.../TousseInstanceAction.java) (revision 39366) +++ ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/action/TousseInstanceAction.java (.../TousseInstanceAction.java) (revision 39374) @@ -1296,35 +1296,18 @@ public void loadAllDepartmentTousse() { String type = StrutsParamUtils.getPraramValue("type", ""); - - String sql = "select po.location,count(*) amount from tousseinstance po "; - sql += " WHERE " - + SqlBuilder.build_IN_Statement("po.status", SqlBuilder.IN, - TousseInstance.STATUS_SHIPPED, - TousseInstance.STATUS_SIGNED); - Date now = new Date(); - if (type.equals(SystemWarningItemVO.WARNING_TYPE_DISABLED_TOUSSE)) {// 已过期器械包 - sql += " and po.validUntil < " + dateQueryAdapter.dateAdapter(now); - } else {// 即将过期器械包 - sql += " and po.validUntil > " + dateQueryAdapter.dateAdapter(now) - + " and po.warningUntil < " - + dateQueryAdapter.dateAdapter(now); - } - -// sql += " and po.departmentStock_id is not null " -// + " group by po.locationForDisplay,po.location"; - sql += " group by po.location"; - + String sql = buildLoadAllDepartmentTousseAmountSql(type, now); + ResultSet result = objectDao.executeSql(sql); List list = new ArrayList(); Set orgUnitCodeSet = new HashSet(); if (result != null) { try { while (result.next()) { - String departmentCode = result.getString("location"); - String count = result.getString("amount"); + String departmentCode = result.getString("departmentCode"); + String count = result.getString("totalAmount"); JSONObject obj = new JSONObject(); obj.put("department", departmentCode); obj.put("departmentCode", departmentCode); @@ -1360,6 +1343,27 @@ } + private String buildLoadAllDepartmentTousseAmountSql(String type, Date now) { + String sql = " select departmentCode, count(1) totalAmount from (select case when po.invoice2_id is null then po.location else po.location_2 end departmentCode from tousseinstance po join TousseDefinition td on td.id = po.tousseDefinition_id WHERE " + + SqlBuilder.build_IN_Statement("po.status", SqlBuilder.IN, + TousseInstance.STATUS_SHIPPED, + TousseInstance.STATUS_SIGNED); + + if (type.equals(SystemWarningItemVO.WARNING_TYPE_DISABLED_TOUSSE)) {// 已过期器械包 + sql += " and po.validUntil < " + dateQueryAdapter.dateAdapter(now); + sql += " AND not exists (select 1 from TousseDefinition atd where ( warningType in ('" + TousseDefinition.WARNING_TYPE_NEVERWARN + "', '" + TousseDefinition.WARNING_TYPE_TOUSSE + "')) and atd.id = td.ancestorID) "; + } else {// 即将过期器械包 + sql += " and po.validUntil > " + dateQueryAdapter.dateAdapter(now) + + " and po.warningUntil < " + + dateQueryAdapter.dateAdapter(now); + sql += " AND not exists (select 1 from TousseDefinition atd where ( warningType in ('" + TousseDefinition.WARNING_TYPE_NEVERWARN + "', '" + TousseDefinition.WARNING_TYPE_DISABLED_TOUSSE + "')) and atd.id = td.ancestorID) "; + } + sql += " and po.comboTousseInstanceId is null"; + sql += ") rs group by departmentCode "; + + return sql; + } + public void setBarcodeTableManager(BarcodeTableManager barcodeTableManager) { this.barcodeTableManager = barcodeTableManager; } Index: ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/dwr/table/TousseInstanceTableManager.java =================================================================== diff -u -r38980 -r39374 --- ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/dwr/table/TousseInstanceTableManager.java (.../TousseInstanceTableManager.java) (revision 38980) +++ ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/dwr/table/TousseInstanceTableManager.java (.../TousseInstanceTableManager.java) (revision 39374) @@ -33,6 +33,7 @@ import com.fasterxml.jackson.databind.ObjectMapper; import com.forgon.Constants; import com.forgon.component.grid.GridManager; +import com.forgon.component.grid.vo.GridVo; import com.forgon.databaseadapter.service.DateQueryAdapter; import com.forgon.directory.acegi.tools.AcegiHelper; import com.forgon.directory.model.BarcodeDevice; @@ -1593,6 +1594,7 @@ // return "删除成功"; } + @SuppressWarnings("unchecked") public String findWarningTousseGoodsTableList( Map> parameterMap) { Map sqlWhereParamMap = gridManager @@ -1614,8 +1616,7 @@ //或状态为已灭菌时,当前用户所属科室装配的包 sqlBuilder.append(" or (orgUnitCoding ='"+queryDepartCode+"'"); - sqlBuilder.append(" and " + SqlBuilder.build_IN_Statement("status", SqlBuilder.IN, - TousseInstance.STATUS_STERILED) + "))"); + sqlBuilder.append(" and status = '" + TousseInstance.STATUS_STERILED + "'))"); //告警类型 String type = sqlWhereParamMap.get("type"); @@ -1639,7 +1640,7 @@ }else{ setQueryTime(sqlBuilder, "po.validUntil", startTime, endTime); } - sqlBuilder.append(" AND not exists (select 1 from TousseDefinition td where ( warningType in ('" + TousseDefinition.WARNING_TYPE_NEVERWARN + "', '" + TousseDefinition.WARNING_TYPE_TOUSSE + "')) and td.ancestorID = po.tousseDefinition.ancestorID) "); + sqlBuilder.append(" AND not exists (select 1 from TousseDefinition td where ( warningType in ('" + TousseDefinition.WARNING_TYPE_NEVERWARN + "', '" + TousseDefinition.WARNING_TYPE_TOUSSE + "')) and td.id = po.tousseDefinition.ancestorID) "); break; case SystemWarningItemVO.WARNING_TYPE_TOUSSE: if(StringUtils.isBlank(startTime) @@ -1649,12 +1650,13 @@ }else{ setQueryTime(sqlBuilder, "po.warningUntil", startTime, endTime); } - sqlBuilder.append(" AND not exists (select 1 from TousseDefinition td where ( warningType in ('" + TousseDefinition.WARNING_TYPE_NEVERWARN + "', '" + TousseDefinition.WARNING_TYPE_DISABLED_TOUSSE + "')) and td.ancestorID = po.tousseDefinition.ancestorID) "); + sqlBuilder.append(" AND not exists (select 1 from TousseDefinition td where ( warningType in ('" + TousseDefinition.WARNING_TYPE_NEVERWARN + "', '" + TousseDefinition.WARNING_TYPE_DISABLED_TOUSSE + "')) and td.id = po.tousseDefinition.ancestorID) "); break; default: //默认显示已过期和即将过期的所有器械包 if(StringUtils.isBlank(startTime) || StringUtils.isBlank(endTime)){ sqlBuilder.append(" AND po.warningUntil < " + dateQueryAdapter.dateAdapter(nowDateForWarningUtil)); + sqlBuilder.append(" AND po.validUntil is not null "); } else { sqlBuilder.append(String.format(" AND( %s between %s AND %s or %s between %s AND %s )", "po.validUntil", @@ -1674,10 +1676,52 @@ // 聚合包内的普通包实例不显示出来 sqlBuilder.append(" and po.comboTousseInstanceId is null ");//用于查询数量的sql语句条件(聚合包内的普通包实例不显示出来) String sql = sqlBuilder.toString(); - String jsonResult = gridManager.renderGrid(parameterMap, - TousseInstance.class.getSimpleName(), sql, + + //根据初始sql和map里面的参数生成最终sql语句 + GridVo gridVo = gridManager.getGridVoByParameter(parameterMap,TousseInstance.class.getSimpleName(), sql); + int start = gridVo.getStart()==0?0:gridVo.getStart(); + int pageSize = gridVo.getPageSize()==0?20:gridVo.getPageSize(); + String orderStr = gridVo.getOrderStr()==null?"":gridVo.getOrderStr(); + sql = gridVo.getSql()==null?sql:gridVo.getSql(); + + //开始查询 + int totalResults = objectDao.countObjectBySql(TousseInstance.class.getSimpleName(), sql); + List list = new ArrayList(); + if(totalResults > 0){ + //list = objectDao.findBySql(TousseInstance.class.getSimpleName(), sql, start,pageSize, orderStr); + //排序语句 + String orderby = buildOrderby(orderStr); + //先查ID + String idHql = String.format("select po.id from %s po %s %s ", TousseInstance.class.getSimpleName(), sql, orderby); + List idList = objectDao.findByHql(idHql, start,pageSize); + //根据ID查询 + String hql = String.format("select po from %s po where %s ", + TousseInstance.class.getSimpleName(), + SqlUtils.getNonStringFieldInLargeCollectionsPredicate("po.id", idList)); + List dbList = objectDao.findByHql(hql); + //再根据id顺序排序 + if(CollectionUtils.isNotEmpty(idList)){ + for (Long id : idList) { + for (TousseInstance ti : dbList) { + if(ti.getId().longValue() == id.longValue()){ + list.add(ti); + break; + } + } + } + } + } + Map map = new HashMap(); + map.put("totalResults", totalResults); + map.put("list", list); + map.put("selectSqlWhereCondition", sql); + + String selectSqlWhereCondition = (String) map + .get("selectSqlWhereCondition"); + String result = gridManager.converJsonString(totalResults, list, selectSqlWhereCondition, TousseInstance.filterProperties); - return updateReturnInfo(jsonResult); + + return updateReturnInfo(result); } /**