Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/basedatamanager/orgunitgoodswarnstock/service/OrgUnitGoodsWarnStockManager.java =================================================================== diff -u --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/basedatamanager/orgunitgoodswarnstock/service/OrgUnitGoodsWarnStockManager.java (revision 0) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/basedatamanager/orgunitgoodswarnstock/service/OrgUnitGoodsWarnStockManager.java (revision 32160) @@ -0,0 +1,42 @@ +package com.forgon.disinfectsystem.basedatamanager.orgunitgoodswarnstock.service; + +import java.io.OutputStream; + +import net.sf.json.JSONArray; + +import com.forgon.disinfectsystem.entity.goodsstock.OrgUnitGoodsWarnStock; +import com.forgon.tools.hibernate.BasePoManager; + +/** + * 组织机构器械包预警库存 + * @author ZhouPeiMian + * @since 2021-09-22 + */ +public interface OrgUnitGoodsWarnStockManager extends BasePoManager { + + /** + * 批量保存组织机构的器械包预警库存 + * @param goodsWarnStockJSONArr + */ + public void batchSaveOrgUnitGoodsWarnStock(JSONArray goodsWarnStockJSONArr); + + /** + * 器械包库存列表导出功能 + * @param sql + * @param outputStream + */ + public void exportGoodsStock(String sql, OutputStream outputStream); + + /** + * 根据科室编码加载器械包预警库存信息 + * @param orgUnitCode + * @return + */ + public JSONArray loadGoodsWarnStockInfo(String orgUnitCode); + + /** + * 根据设置了统计的器械包名，查询器械包信息 + */ + public JSONArray loadTousseInfoByGoodsOption(String orgUnitCode, String tousseDefinitionId); + +} Index: ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/dwr/table/TousseGoodsStockTableManager.java =================================================================== diff -u -r30024 -r32160 --- ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/dwr/table/TousseGoodsStockTableManager.java (.../TousseGoodsStockTableManager.java) (revision 30024) +++ ssts-packing/src/main/java/com/forgon/disinfectsystem/packing/dwr/table/TousseGoodsStockTableManager.java (.../TousseGoodsStockTableManager.java) (revision 32160) @@ -15,7 +15,9 @@ import com.forgon.disinfectsystem.basedatamanager.reportoption.GoodsOptionManager; import com.forgon.disinfectsystem.entity.basedatamanager.reportoption.GoodsOption; import com.forgon.disinfectsystem.entity.goodsstock.GoodsStock; +import com.forgon.disinfectsystem.entity.goodsstock.OrgUnitGoodsWarnStock; import com.forgon.disinfectsystem.tousse.toussedefinition.service.TousseInstanceManager; +import com.forgon.disinfectsystem.vo.GoodsStockVo; import com.forgon.tools.SqlBuilder; import com.forgon.tools.db.DatabaseUtil; import com.forgon.tools.hibernate.ObjectDao; @@ -64,7 +66,7 @@ Map sqlWhereParamMap = gridManager .getParamFromView(parameterMap); StringBuilder sqlBuilder = new StringBuilder(); - sqlBuilder.append("(select name , orgUnitCode, orgUnitName , sum(amount) amount from GoodsStock "); + sqlBuilder.append("(select vw.tousseDefinitionId, vw.name , vw.orgUnitCode, vw.orgUnitName, vw.amount, s.warningStock from (select tousseDefinitionId, name , orgUnitCode, orgUnitName , sum(amount) amount from GoodsStock "); sqlBuilder.append(" WHERE 1=1 and amount > 0 and goodsType='"+GoodsStock.TYPE_TOUSSE +"' and tousseDefinitionId is not null"); @@ -73,18 +75,24 @@ if (StringUtils.isNotBlank(tousseName)) { sqlBuilder.append(" and name='" + tousseName + "'"); } + + //科室编码 + String orgUnitCode = sqlWhereParamMap.get("orgUnitCode"); + if(StringUtils.isBlank(orgUnitCode)){ + orgUnitCode = AcegiHelper.getCurrentOrgUnitCode(); + } //查看是否设置了统计的器械包名 - GoodsOption goodsOption = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEGOODSSTOCK, AcegiHelper.getCurrentOrgUnitCode()); + GoodsOption goodsOption = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEGOODSSTOCK, orgUnitCode); if (goodsOption != null && StringUtils.isNotBlank(goodsOption.getValue())){ String[] goods = goodsOption.getValue().split(";"); String names = SqlBuilder.build_IN_Statement(" name ", SqlBuilder.IN, goods); sqlBuilder.append(" and ").append(names); } - sqlBuilder.append(" and orgUnitCode='" + AcegiHelper.getCurrentOrgUnitCode() + "'"); //只查用户当前科室的物品的库存 + sqlBuilder.append(" and orgUnitCode='" + orgUnitCode + "'"); //只查用户当前科室的物品的库存 - sqlBuilder.append(" group by name , orgUnitCode, orgUnitName ) vw"); + sqlBuilder.append(" group by tousseDefinitionId, name , orgUnitCode, orgUnitName ) vw"); // int start = 0; // int pageSize = 20; @@ -108,34 +116,42 @@ // pageSize = Integer.valueOf(pageSizeStr.get(0)); // } // } - + sqlBuilder.append(" left join " + OrgUnitGoodsWarnStock.class.getSimpleName() + " s on s.tousseDefinitionId = vw.tousseDefinitionId and vw.orgUnitCode = s.orgUnitCode ) rs "); + //器械包名称 + String stockFilter = sqlWhereParamMap.get("stockFilter"); + if (StringUtils.isNotBlank(stockFilter)) { + if(StringUtils.equals("库存数量≤设置最低库存的器械包", stockFilter)){ + sqlBuilder.append(" where rs.warningStock is not null and rs.amount <= rs.warningStock "); + } + } String sql = sqlBuilder.toString(); int totalResults = objectDao.countBySql("select count(0) from " + sql); - List goodsStockList = new ArrayList(); + List goodsStockVoList = new ArrayList(); ResultSet rs = objectDao.executeSql("select * from " + sql + " order by name"); try { while(rs.next()){ - GoodsStock goodsStock = new GoodsStock(); - goodsStock.setAmount(rs.getInt("amount")); - goodsStock.setName(rs.getString("name")); - goodsStock.setOrgUnitCode(rs.getString("orgUnitCode")); - goodsStock.setOrgUnitName(rs.getString("orgUnitName")); - goodsStockList.add(goodsStock); + GoodsStockVo goodsStockVo = new GoodsStockVo(); + goodsStockVo.setAmount(rs.getInt("amount")); + goodsStockVo.setName(rs.getString("name")); + goodsStockVo.setOrgUnitCode(rs.getString("orgUnitCode")); + goodsStockVo.setOrgUnitName(rs.getString("orgUnitName")); + goodsStockVo.setMinWarnStock(rs.getInt("warningStock")); + goodsStockVoList.add(goodsStockVo); } } catch (SQLException e) { e.printStackTrace(); }finally { DatabaseUtil.closeResultSetAndStatement(rs); } - ResultSet rs2 = objectDao.executeSql("select '','',sum(vw.amount) amount from " + sql); //汇总数量 + ResultSet rs2 = objectDao.executeSql("select '','',sum(rs.amount) amount from " + sql); //汇总数量 try { rs2.next(); - GoodsStock goodsStock = new GoodsStock(); - goodsStock.setAmount(rs2.getInt("amount")); - goodsStock.setName("总数"); - goodsStock.setOrgUnitName(""); - goodsStockList.add(goodsStock); + GoodsStockVo goodsStockVo = new GoodsStockVo(); + goodsStockVo.setAmount(rs2.getInt("amount")); + goodsStockVo.setName("总数"); + goodsStockVo.setOrgUnitName(""); + goodsStockVoList.add(goodsStockVo); } catch (SQLException e) { e.printStackTrace(); }finally { @@ -147,7 +163,7 @@ // map.put("totalResults", totalResults); // map.put("list", goodsStockList); // map.put("selectSqlWhereCondition", sql); - String json = gridManager.converJsonString(totalResults, goodsStockList, sql , new String[]{}); + String json = gridManager.converJsonString(totalResults, goodsStockVoList, sql , new String[]{}); return json; } Index: ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/action/SystemWarningAction.java =================================================================== diff -u -r30948 -r32160 --- ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/action/SystemWarningAction.java (.../SystemWarningAction.java) (revision 30948) +++ ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/action/SystemWarningAction.java (.../SystemWarningAction.java) (revision 32160) @@ -330,4 +330,22 @@ } } + /** + * 器械包库存告警FSSDERMYY-12 + */ + public void getTousseGoodsStockAlarmWarning(){ + try { + //告警类型 + String orgUnitCode = StrutsParamUtils.getPraramValue("orgUnitCode", null); + if(StringUtils.isBlank(orgUnitCode)){ + orgUnitCode = AcegiHelper.getCurrentOrgUnitCode(); + } + JSONArray message = systemWarningManager.getTousseGoodsStockAlarmWarning(orgUnitCode); + StrutsResponseUtils.output(true, message); + } catch (Exception e) { + e.printStackTrace(); + StrutsResponseUtils.output(false, e.getMessage()); + } + } + } Index: ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/service/SystemWarningManagerImpl.java =================================================================== diff -u -r31144 -r32160 --- ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/service/SystemWarningManagerImpl.java (.../SystemWarningManagerImpl.java) (revision 31144) +++ ssts-web/src/main/java/com/forgon/disinfectsystem/systemwarning/service/SystemWarningManagerImpl.java (.../SystemWarningManagerImpl.java) (revision 32160) @@ -4,6 +4,7 @@ import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; +import java.util.Arrays; import java.util.Calendar; import java.util.Comparator; import java.util.Date; @@ -23,6 +24,7 @@ import com.forgon.directory.acegi.tools.AcegiHelper; import com.forgon.directory.model.OrgUnit; import com.forgon.directory.vo.LoginUserData; +import com.forgon.disinfectsystem.basedatamanager.reportoption.GoodsOptionManager; import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager; import com.forgon.disinfectsystem.basedatamanager.warehouse.service.WareHouseManager; import com.forgon.disinfectsystem.common.CssdUtils; @@ -33,6 +35,7 @@ import com.forgon.disinfectsystem.entity.assestmanagement.ExpensiveGoods; import com.forgon.disinfectsystem.entity.assestmanagement.ExpensiveGoodsInstance; import com.forgon.disinfectsystem.entity.basedatamanager.materialdefinition.MaterialDefinition; +import com.forgon.disinfectsystem.entity.basedatamanager.reportoption.GoodsOption; import com.forgon.disinfectsystem.entity.basedatamanager.sterilizer.Sterilizer; import com.forgon.disinfectsystem.entity.basedatamanager.supplier.RegistrationCertification; import com.forgon.disinfectsystem.entity.basedatamanager.supplier.Supplier; @@ -44,12 +47,15 @@ import com.forgon.disinfectsystem.entity.customform.forminstance.FormInstance; import com.forgon.disinfectsystem.entity.devicemaintenance.DeviceMaintenanceCusD; import com.forgon.disinfectsystem.entity.devicemaintenance.DeviceMaintenanceCusI; +import com.forgon.disinfectsystem.entity.goodsstock.GoodsStock; +import com.forgon.disinfectsystem.entity.goodsstock.OrgUnitGoodsWarnStock; import com.forgon.disinfectsystem.entity.recall.RecallRecord; import com.forgon.disinfectsystem.entity.recall.RecallRecordItem; import com.forgon.disinfectsystem.entity.systemwarning.SystemWarningItemVO; import com.forgon.disinfectsystem.entity.systemwarning.SystemWarningVo; import com.forgon.disinfectsystem.jasperreports.service.dataindex.DataIndex; import com.forgon.disinfectsystem.sterilizationmanager.sterilizationrecord.service.SterilizationRecordManager; +import com.forgon.disinfectsystem.vo.GoodsStockVo; import com.forgon.disinfectsystem.washanddisinfectmanager.washanddisinfectrecord.service.WashAndDisinfectRecordManager; import com.forgon.keyvalue.service.KeyValueManager; import com.forgon.log.model.Log; @@ -77,6 +83,8 @@ private DateQueryAdapter dateQueryAdapter; private OperationManager operationManager; + + private GoodsOptionManager goodsOptionManager; private static final String SSTS_SYSTEMWARNING_DIPOSABLEGOODS = "SSTS_SystemWarning_DiposableGoods"; @@ -108,6 +116,10 @@ private WashAndDisinfectRecordManager washAndDisinfectRecordManager; + public void setGoodsOptionManager(GoodsOptionManager goodsOptionManager) { + this.goodsOptionManager = goodsOptionManager; + } + public void setWashAndDisinfectRecordManager( WashAndDisinfectRecordManager washAndDisinfectRecordManager) { this.washAndDisinfectRecordManager = washAndDisinfectRecordManager; @@ -1021,4 +1033,128 @@ return message; } + @Override + public JSONArray getTousseGoodsStockAlarmWarning(String orgUnitCode) { + + List goodsStockVoList = getWarningGoodsStockVo(orgUnitCode); + if(CollectionUtils.isEmpty(goodsStockVoList)){ + return null; + } + JSONArray message = new JSONArray(); + for (GoodsStockVo goodsStockVo : goodsStockVoList) { + //xx科室xx物品库存为xx，低于（等于）最小库存xx，请提前做好备货！ + String orgUnitName = goodsStockVo.getOrgUnitName(); + String tousseName = goodsStockVo.getName(); + Integer amount = goodsStockVo.getAmount(); + Integer minWarnStock = goodsStockVo.getMinWarnStock(); + String less = amount < minWarnStock ? "低于" : "等于"; + String msg = String.format("%s%s库存为%s，%s最小库存%s，请提前做好备货！", + orgUnitName, tousseName, amount,less, minWarnStock); + JSONObject json = new JSONObject(); + json.put("message", msg); + json.put("orgUnitName", orgUnitName); + json.put("orgUnitCode", goodsStockVo.getOrgUnitCode()); + json.put("tousseDefinitionId", goodsStockVo.getTousseDefinitionId()); + json.put("tousseDefinitionName", goodsStockVo.getName()); + message.add(json); + } + return message; + } + + private List getWarningGoodsStockVo(String orgUnitCode) { + + List goodsStockVoList = new ArrayList(); + boolean isFirstSupplyRoom = supplyRoomConfigManager.isFirstSupplyRoomOrgUnit(orgUnitCode); + StringBuilder sqlBuilder = new StringBuilder(); + sqlBuilder.append("(select vw.tousseDefinitionId, vw.name , vw.orgUnitCode, vw.orgUnitName, vw.amount, s.warningStock from "); + sqlBuilder.append("(select tousseDefinitionId, name , orgUnitCode, orgUnitName, sum(amount) amount from GoodsStock "); + sqlBuilder.append(" WHERE 1=1 and amount > 0 and goodsType='"+GoodsStock.TYPE_TOUSSE + +"' and tousseDefinitionId is not null"); + if(!isFirstSupplyRoom){ + sqlBuilder.append(" and orgUnitCode='" + orgUnitCode + "'"); //默认只查用户当前科室的物品的库存 + //查看是否设置了统计的器械包名 + GoodsOption goodsOption = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEGOODSSTOCK, orgUnitCode); + if (goodsOption != null && StringUtils.isNotBlank(goodsOption.getValue())){ + String[] goods = goodsOption.getValue().split(";"); + String names = SqlBuilder.build_IN_Statement(" name ", SqlBuilder.IN, goods); + sqlBuilder.append(" and ").append(names); + } + } + + sqlBuilder.append(" group by tousseDefinitionId, name, orgUnitCode, orgUnitName ) vw"); + sqlBuilder.append(" left join " + OrgUnitGoodsWarnStock.class.getSimpleName() + " s on s.tousseDefinitionId = vw.tousseDefinitionId and vw.orgUnitCode = s.orgUnitCode ) rs "); + sqlBuilder.append(" where rs.warningStock is not null and rs.warningStock >= rs.amount "); + + ResultSet rs = objectDao.executeSql("select * from " + sqlBuilder.toString() + " order by name"); + try { + while(rs.next()){ + GoodsStockVo goodsStockVo = new GoodsStockVo(); + goodsStockVo.setAmount(rs.getInt("amount")); + goodsStockVo.setName(rs.getString("name")); + goodsStockVo.setTousseDefinitionId(rs.getLong("tousseDefinitionId")); + goodsStockVo.setOrgUnitCode(rs.getString("orgUnitCode")); + goodsStockVo.setOrgUnitName(rs.getString("orgUnitName")); + goodsStockVo.setMinWarnStock(rs.getInt("warningStock")); + goodsStockVoList.add(goodsStockVo); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + + // 一级供应室用户需要根据临床科室过滤调“设置统计的器械包”中不存在的库存 + if(isFirstSupplyRoom){ + return filterGoodsStockVoList(goodsStockVoList); + } + + return goodsStockVoList; + } + + private List filterGoodsStockVoList(List goodsStockVoList) { + if(CollectionUtils.isEmpty(goodsStockVoList)){ + return goodsStockVoList; + } + List goodsOptionList = (List) objectDao.findByProperty(GoodsOption.class.getSimpleName(), "model", GoodsOption.MODEL_TOUSSEGOODSSTOCK); + Map orgGoodsOptionMap = new HashMap(); + if(CollectionUtils.isNotEmpty(goodsOptionList)){ + for (GoodsOption goodsOption : goodsOptionList) { + orgGoodsOptionMap.put(goodsOption.getCode(), goodsOption); + } + } + Map> goodsStockVoMap = new HashMap>(); + for (int i=0;i voList = goodsStockVoMap.get(orgUnitCode); + if(voList == null){ + voList = new ArrayList(); + } + voList.add(goodsStockVo); + goodsStockVoMap.put(orgUnitCode, goodsStockVoList); + } + + List result = new ArrayList(); + for (String orgUnitCode : goodsStockVoMap.keySet()) { + GoodsOption goodsOption = orgGoodsOptionMap.get(orgUnitCode); + List stockVoList = goodsStockVoMap.get(orgUnitCode); + if(goodsOption == null || StringUtils.isBlank(goodsOption.getValue())){ + // 没有设置过滤条件 + result.addAll(stockVoList); + continue; + } + String[] goods = goodsOption.getValue().split(";"); + List goodList = Arrays.asList(goods); + if(CollectionUtils.isNotEmpty(stockVoList)){ + for (GoodsStockVo vo : stockVoList) { + if(goodList.contains(vo.getName())){ + result.add(vo); + } + } + } + } + + return result; + } + } Index: ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-disinfectsystem-service.xml =================================================================== diff -u -r31729 -r32160 --- ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-disinfectsystem-service.xml (.../applicationContext-disinfectsystem-service.xml) (revision 31729) +++ ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-disinfectsystem-service.xml (.../applicationContext-disinfectsystem-service.xml) (revision 32160) @@ -213,6 +213,16 @@ + + + + + + + + + implements OrgUnitGoodsWarnStockManager { + + private GoodsOptionManager goodsOptionManager; + + private SqlFunctionsAdapter sqlFunctionsAdapter; + + public void setSqlFunctionsAdapter(SqlFunctionsAdapter sqlFunctionsAdapter) { + this.sqlFunctionsAdapter = sqlFunctionsAdapter; + } + + public void setGoodsOptionManager(GoodsOptionManager goodsOptionManager) { + this.goodsOptionManager = goodsOptionManager; + } + + @Override + public void batchSaveOrgUnitGoodsWarnStock(JSONArray goodsWarnStockJSONArr) { + if(goodsWarnStockJSONArr == null || goodsWarnStockJSONArr.size() == 0){ + throw new SystemException("参数异常！"); + } + List orgUnitGoodsWarnStockList = new ArrayList(); + List orgUnitGoodsWarnStockDBList = new ArrayList(); + List delOrgUnitGoodsWarnStockDBList = new ArrayList(); + for (Object object : goodsWarnStockJSONArr) { + JSONObject jsonObj = (JSONObject) object; + String orgUnitCode = jsonObj.optString("orgUnitCode"); + Long tousseDefinitionId = jsonObj.optLong("tousseDefinitionID"); + Integer warningStock = jsonObj.optInt("warningStock"); + if(StringUtils.isBlank(orgUnitCode) || !DatabaseUtil.isPoIdValid(tousseDefinitionId)){ + throw new SystemException("参数异常！"); + } + OrgUnitGoodsWarnStock warnStock = new OrgUnitGoodsWarnStock(); + warnStock.setOrgUnitCode(orgUnitCode); + warnStock.setTousseDefinitionId(tousseDefinitionId); + warnStock.setWarningStock(warningStock); + orgUnitGoodsWarnStockList.add(warnStock); + } + if(CollectionUtils.isNotEmpty(orgUnitGoodsWarnStockList)){ + for (OrgUnitGoodsWarnStock orgUnitGoodsWarnStock : orgUnitGoodsWarnStockList) { + String hql = String.format("select po from %s po where po.orgUnitCode = %s and po.tousseDefinitionId = %s", + OrgUnitGoodsWarnStock.class.getSimpleName(), + orgUnitGoodsWarnStock.getOrgUnitCode(), + orgUnitGoodsWarnStock.getTousseDefinitionId()); + List tempOrgUnitGoodsWarnStockList = objectDao.findByHql(hql); + if(CollectionUtils.isEmpty(tempOrgUnitGoodsWarnStockList)){ + orgUnitGoodsWarnStockDBList.add(orgUnitGoodsWarnStock); + }else{ + if(orgUnitGoodsWarnStock.getWarningStock() == null || + orgUnitGoodsWarnStock.getWarningStock().intValue() == 0){ + delOrgUnitGoodsWarnStockDBList.addAll(tempOrgUnitGoodsWarnStockList); + }else{ + for (OrgUnitGoodsWarnStock orgUnitGoodsWarnStock2 : tempOrgUnitGoodsWarnStockList) { + orgUnitGoodsWarnStock2.setWarningStock(orgUnitGoodsWarnStock.getWarningStock()); + } + orgUnitGoodsWarnStockDBList.addAll(tempOrgUnitGoodsWarnStockList); + } + } + } + } + + // 修改或者新增科室的器械包预警库存 + if(CollectionUtils.isNotEmpty(orgUnitGoodsWarnStockDBList)){ + objectDao.batchSaveOrUpdate(orgUnitGoodsWarnStockDBList); + } + + // 删除科室的器械包预警库存 + if(CollectionUtils.isNotEmpty(delOrgUnitGoodsWarnStockDBList)){ + objectDao.deleteAll(delOrgUnitGoodsWarnStockDBList); + } + + } + + @Override + public void exportGoodsStock(String sql, OutputStream outputStream) { + + List goodsStockVoList = getGoodsStockVoList(sql); + if(CollectionUtils.isNotEmpty(goodsStockVoList)){ + createExportFileByJxl(goodsStockVoList, outputStream); + } + } + + /** + * 基础jxl组件的导出下载实现方法 + */ + private void createExportFileByJxl(List goodsStockVoList, + OutputStream ops){ + WritableWorkbook book = null; + try { + // 打开文件 + book = Workbook.createWorkbook(ops); + if(goodsStockVoList!=null && !goodsStockVoList.isEmpty()){ + int length=goodsStockVoList.size();//查询结果记录数 + int sheetSize=60000; + int sheetNum=1; + if(length%sheetSize>0){ + sheetNum=length/sheetSize+1; + }else{ + sheetNum=length/sheetSize; + } + //需导出的列头定义(与器械包库存页面列表保持一致) + String ss[] = {"器械包名称","所属科室/仓库","数量","最小库存"}; + Label label; + //循环sheet数量 + for(int kk=0;kk getGoodsStockVoList(String sql) { + List goodsStockVoList = new ArrayList(); + ResultSet rs = objectDao.executeSql("select * from " + sql + " order by name"); + try { + while(rs.next()){ + GoodsStockVo goodsStockVo = new GoodsStockVo(); + goodsStockVo.setAmount(rs.getInt("amount")); + goodsStockVo.setName(rs.getString("name")); + goodsStockVo.setOrgUnitCode(rs.getString("orgUnitCode")); + goodsStockVo.setOrgUnitName(rs.getString("orgUnitName")); + goodsStockVo.setMinWarnStock(rs.getInt("warningStock")); + goodsStockVoList.add(goodsStockVo); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + + ResultSet rs2 = objectDao.executeSql("select '','',sum(rs.amount) amount from " + sql); //汇总数量 + try { + rs2.next(); + GoodsStockVo goodsStockVo = new GoodsStockVo(); + goodsStockVo.setAmount(rs2.getInt("amount")); + goodsStockVo.setName("总数"); + goodsStockVo.setOrgUnitName(""); + goodsStockVoList.add(goodsStockVo); + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs2); + } + return goodsStockVoList; + } + + @Override + public JSONArray loadGoodsWarnStockInfo(String orgUnitCode) { + + JSONArray result = new JSONArray(); + if(StringUtils.isBlank(orgUnitCode)){ + return result; + } + + String sql = String.format("select td.name, td.id, s.warningStock, s.orgUnitCode from %s s " + + "join %s td on td.id = s.tousseDefinitionId where s.orgUnitCode = '%s'", + OrgUnitGoodsWarnStock.class.getSimpleName(), + TousseDefinition.class.getSimpleName(), + orgUnitCode); + + ResultSet rs = null; + + try { + rs = objectDao.executeSql(sql); + while(rs.next()){ + JSONObject json = new JSONObject(); + json.put("name", rs.getString("name")); + json.put("id", rs.getString("id")); + json.put("warnStock", rs.getInt("warningStock")); + json.put("orgUnitCode", rs.getString("orgUnitCode")); + result.add(json); + } + } catch (Exception e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + + return result; + } + + @Override + public JSONArray loadTousseInfoByGoodsOption(String orgUnitCode, String tousseDefinitionId) { + + JSONArray result = new JSONArray(); + if(StringUtils.isBlank(orgUnitCode)){ + orgUnitCode = AcegiHelper.getCurrentOrgUnitCode(); + } + //查看是否设置了统计的器械包名 + GoodsOption goodsOption = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEGOODSSTOCK, orgUnitCode); + if(goodsOption == null || StringUtils.isBlank(goodsOption.getValue())){ + return result; + } + + StringBuffer sqlBuilder = new StringBuffer("select po from " + TousseDefinition.class.getSimpleName() + " po where po.forDisplay = 1 "); + sqlBuilder.append(" and po.ancestorID in (select tousseDefinitionId from " + CssdHandleTousses.class.getSimpleName() + " where orgUnitCode is not null) "); + String[] goods = null; + if (goodsOption != null && StringUtils.isNotBlank(goodsOption.getValue())){ + goods = goodsOption.getValue().split(";"); + String names = SqlBuilder.build_IN_Statement(" name ", SqlBuilder.IN, goods); + sqlBuilder.append(" and ").append(names); + } + + if(DatabaseUtil.isPoIdValid(tousseDefinitionId)){ + sqlBuilder.append(" and id = " + tousseDefinitionId); + } + + if(goods == null){ + String orderBy = String.format(" order by %s, po.name ",sqlFunctionsAdapter.strlen("po.name")); + sqlBuilder.append(orderBy); + } + + List list = objectDao.findByHql(sqlBuilder.toString()); + if(CollectionUtils.isEmpty(list)){ + return result; + } + + // 关联科室库存 + result = setGoodsWarnStock(orgUnitCode, list, result); + + // 根据“统计的器械包”排序 + result = sortResultByGoodsOptionValues(goods, result); + + return result; + + } + + private JSONArray setGoodsWarnStock(String orgUnitCode, List list, JSONArray result) { + Map toussStockMap = new HashMap(); + List orgUnitGoodsWarnStockList = this.getByProperty("orgUnitCode", orgUnitCode); + if(CollectionUtils.isNotEmpty(orgUnitGoodsWarnStockList)){ + for (OrgUnitGoodsWarnStock orgUnitGoodsWarnStock : orgUnitGoodsWarnStockList) { + toussStockMap.put(orgUnitGoodsWarnStock.getTousseDefinitionId(), orgUnitGoodsWarnStock); + } + } + + for (TousseDefinition tousseDefinition : list) { + Integer warningStock = 0; + Long warningStockId = 0l; + OrgUnitGoodsWarnStock stock = toussStockMap.get(tousseDefinition.getId()); + if(stock != null){ + warningStock = stock.getWarningStock(); + warningStockId = stock.getId(); + } + JSONObject json = new JSONObject(); + json.put("tousseDefinitionID", tousseDefinition.getId()); + json.put("name", tousseDefinition.getName()); + json.put("tousseType", tousseDefinition.getTousseType()); + json.put("warnStockID", warningStockId); + json.put("warnStock", warningStock); + result.add(json); + } + return result; + } + + private JSONArray sortResultByGoodsOptionValues(String[] goods, + JSONArray result) { + if(goods != null && goods.length > 0){ + JSONArray jsonArr = new JSONArray(); + for (int i = 0; i < goods.length; i++) { + String tousseName = goods[i]; + for(int j=0; j, Preparable { + + private OrgUnitGoodsWarnStock orgUnitGoodsWarnStock; + + private OrgUnitGoodsWarnStockManager orgUnitGoodsWarnStockManager; + + private GoodsOptionManager goodsOptionManager; + + public void setGoodsOptionManager(GoodsOptionManager goodsOptionManager) { + this.goodsOptionManager = goodsOptionManager; + } + + public void setOrgUnitGoodsWarnStockManager( + OrgUnitGoodsWarnStockManager orgUnitGoodsWarnStockManager) { + this.orgUnitGoodsWarnStockManager = orgUnitGoodsWarnStockManager; + } + + @Override + public void prepare() throws Exception { + String id = StrutsParamUtils.getPraramValue("id", ""); + if(DatabaseUtil.isPoIdValid(id)){ + orgUnitGoodsWarnStock = orgUnitGoodsWarnStockManager.get(id); + }else{ + orgUnitGoodsWarnStock = new OrgUnitGoodsWarnStock(); + } + } + + @Override + public OrgUnitGoodsWarnStock getModel() { + return orgUnitGoodsWarnStock; + } + + /** + * 保存科室器械包的预警库存 + */ + public void batchSaveOrgUnitGoodsWarnStock(){ + /*[{ + "orgUnitCode":1, + "tousseDefinitionId":1, + "warningStock":100 + }, + { + "orgUnitCode":1, + "tousseDefinitionId":2, + "warningStock":100 + }]*/ + String goodsWarnStockJSONArrStr = StrutsParamUtils.getPraramValue("goodsWarnStockArray", ""); + JSONObject jsonObj = JSONUtil.buildJsonObject(true, "保存成功！"); + try { + JSONArray goodsWarnStockJSONArr = JSONArray.fromObject(goodsWarnStockJSONArrStr); + orgUnitGoodsWarnStockManager.batchSaveOrgUnitGoodsWarnStock(goodsWarnStockJSONArr); + } catch (Exception e) { + e.printStackTrace(); + jsonObj = JSONUtil.buildJsonObject(false, "保存失败：" + e.getMessage()); + } + StrutsResponseUtils.output(jsonObj); + } + + /** + * 导出科室器械包预警库存信息 + */ + public void exportOrgUnitGoodsStockInfo() { + HttpServletResponse response = StrutsParamUtils.getResponse(); + try { + String fileName = "器械包库存.xls"; + ServletOutputStream servletOutputStream = response + .getOutputStream(); + response.setContentType("application/octet-stream"); + response.addHeader("Content-Disposition", "attachment;filename=" + + new String(fileName.getBytes("GBK"), "ISO8859_1")); + + + StringBuilder sqlBuilder = new StringBuilder(); + sqlBuilder.append("(select vw.tousseDefinitionId, vw.name , vw.orgUnitCode, vw.orgUnitName, vw.amount, s.warningStock from (select tousseDefinitionId, name , orgUnitCode, orgUnitName , sum(amount) amount from GoodsStock "); + sqlBuilder.append(" WHERE 1=1 and amount > 0 and goodsType='"+GoodsStock.TYPE_TOUSSE + +"' and tousseDefinitionId is not null"); + + //器械包名称 + String tousseName = StrutsParamUtils.getPraramValue("tousseName", ""); + if (StringUtils.isNotBlank(tousseName)) { + sqlBuilder.append(" and name='" + tousseName + "'"); + } + + //科室编码 + String orgUnitCode = StrutsParamUtils.getPraramValue("orgUnitCode",""); + if(StringUtils.isBlank(orgUnitCode)){ + orgUnitCode = AcegiHelper.getCurrentOrgUnitCode(); + } + + //查看是否设置了统计的器械包名 + GoodsOption goodsOption = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEGOODSSTOCK, orgUnitCode); + if (goodsOption != null && StringUtils.isNotBlank(goodsOption.getValue())){ + String[] goods = goodsOption.getValue().split(";"); + String names = SqlBuilder.build_IN_Statement(" name ", SqlBuilder.IN, goods); + sqlBuilder.append(" and ").append(names); + } + + sqlBuilder.append(" and orgUnitCode='" + orgUnitCode + "'"); //只查用户当前科室的物品的库存 + + sqlBuilder.append(" group by tousseDefinitionId, name , orgUnitCode, orgUnitName ) vw"); + + sqlBuilder.append(" left join " + OrgUnitGoodsWarnStock.class.getSimpleName() + " s on s.tousseDefinitionId = vw.tousseDefinitionId and vw.orgUnitCode = s.orgUnitCode ) rs "); + //器械包名称 + String stockFilter = StrutsParamUtils.getPraramValue("stockFilter", ""); + if (StringUtils.isNotBlank(stockFilter)) { + if(StringUtils.equals("库存数量≤设置最低库存的器械包", stockFilter)){ + sqlBuilder.append(" where rs.warningStock is not null and rs.amount <= rs.warningStock "); + } + } + String sql = sqlBuilder.toString(); + + orgUnitGoodsWarnStockManager.exportGoodsStock(sql, response.getOutputStream()); + servletOutputStream.flush(); + + } catch (Exception e) { + e.printStackTrace(); + } finally { + try { + response.getOutputStream().close(); + } catch (IOException e) { + e.printStackTrace(); + } + } + } + + /** + * 加载科室器械包预警库存 + */ + public void loadGoodsStockInfo() { + String orgUnitCode = StrutsParamUtils.getPraramValue("orgUnitCode", ""); + try { + JSONArray result = orgUnitGoodsWarnStockManager.loadGoodsWarnStockInfo(orgUnitCode); + StrutsResponseUtils.output(true, result); + } catch (Exception e) { + e.printStackTrace(); + StrutsResponseUtils.output(false, "查询失败：" + e.getMessage()); + } + } + + /** + * 根据设置了统计的器械包名，加载科室器械包预警库存，没有设置最低库存的，只显示器械包名称 + */ + public void loadTousseInfoByGoodsOption(){ + String orgUnitCode = StrutsParamUtils.getPraramValue("orgUnitCode", ""); + String tousseDefinitionId = StrutsParamUtils.getPraramValue("tousseDefinitionID", ""); + try { + JSONArray result = orgUnitGoodsWarnStockManager.loadTousseInfoByGoodsOption(orgUnitCode, tousseDefinitionId); + StrutsResponseUtils.output(true, result); + } catch (Exception e) { + e.printStackTrace(); + StrutsResponseUtils.output(false, "查询失败：" + e.getMessage()); + } + } + +}