Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/OperationTousseOperationRepartHelper.java =================================================================== diff -u -r37730 -r37737 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/OperationTousseOperationRepartHelper.java (.../OperationTousseOperationRepartHelper.java) (revision 37730) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/OperationTousseOperationRepartHelper.java (.../OperationTousseOperationRepartHelper.java) (revision 37737) @@ -369,20 +369,24 @@ tousseOperationDepart = user.getCurrentOrgUnitName(); } Map tousseOperationSumVoMap = new HashMap(); - setInvocieAmount(betweenSql, "", tousseOperationDepart, tousseOperationSumVoMap); + setInvocieAmount(betweenSql, tousseOperationDepart, tousseOperationSumVoMap); + getPKInvocieAmount(betweenSql, tousseOperationDepart, tousseOperationSumVoMap); if(MapUtils.isEmpty(tousseOperationSumVoMap)){ return new ArrayList(); } + String pkConditionalFiltering = ""; if(StringUtils.isNotBlank(tousseOperationDepart)){ - conditionalFiltering += SqlUtils.getInStringListSql("i.depart", SqlUtils.splitStringToSet(tousseOperationDepart, ",")); + Set tousseOperationDeparts = SqlUtils.splitStringToSet(tousseOperationDepart, ","); + conditionalFiltering += SqlUtils.getInStringListSql("i.depart", tousseOperationDeparts); + pkConditionalFiltering = SqlUtils.getInStringListSql("ti.depart", tousseOperationDeparts); } - setStorageRecordAmount(betweenSql, conditionalFiltering, tousseOperationSumVoMap); - setOutStorageRecordAmount(betweenSql, conditionalFiltering, tousseOperationSumVoMap); - setUseAmount(betweenSql, conditionalFiltering, tousseOperationSumVoMap); - setSecondaryStorageAmount(betweenSql, conditionalFiltering, tousseOperationSumVoMap); - setUsedAndNoOutStorageAmount(betweenSql, conditionalFiltering, tousseOperationSumVoMap); - setNoOperationRecordAmount(betweenSql, conditionalFiltering, tousseOperationSumVoMap); - setInvoicedNoInStorageAmount(betweenSql, conditionalFiltering, tousseOperationSumVoMap); + setStorageRecordAmount(pkConditionalFiltering, betweenSql, conditionalFiltering, tousseOperationSumVoMap); + setOutStorageRecordAmount(pkConditionalFiltering, betweenSql, conditionalFiltering, tousseOperationSumVoMap); + setUseAmount(pkConditionalFiltering, betweenSql, conditionalFiltering, tousseOperationSumVoMap); + setSecondaryStorageAmount(pkConditionalFiltering, betweenSql, conditionalFiltering, tousseOperationSumVoMap); + setUsedAndNoOutStorageAmount(pkConditionalFiltering, betweenSql, conditionalFiltering, tousseOperationSumVoMap); + setNoOperationRecordAmount(pkConditionalFiltering, betweenSql, conditionalFiltering, tousseOperationSumVoMap); + setInvoicedNoInStorageAmount(pkConditionalFiltering, betweenSql, conditionalFiltering, tousseOperationSumVoMap); return calcTousseOperationSumVo(tousseOperationSumVoMap); } /** @@ -392,10 +396,10 @@ * @param tousseOperationDepart 流转科室 * @param tousseOperationSumVoMap 汇总vo的map */ - private void setInvocieAmount(String betweenSql, String orgUnitCoding, String tousseOperationDepart, Map tousseOperationSumVoMap){ + private void setInvocieAmount(String betweenSql, String tousseOperationDepart, Map tousseOperationSumVoMap){ ReportQueryParams params = new ReportQueryParams(objectDao); params.betweenSql = betweenSql; - params.querySupplyRoom = orgUnitCoding; + params.querySupplyRoom = ""; params.includeDisposableGoods = false; params.extraGroupBy = "i.depart"; params.isGroup = true; @@ -431,12 +435,52 @@ } } /** + * 查询聚力康器械包发货包数 + * @param betweenSql + * @param tousseOperationDepart + * @param tousseOperationSumVoMap + */ + private void getPKInvocieAmount(String betweenSql, String tousseOperationDepart, Map tousseOperationSumVoMap){ + String sql = "select count(*) amount,ti.depart from " + + TousseInstance.class.getSimpleName() + + " ti join " + + BarcodeDevice.class.getSimpleName() + + " bd on bd.id=ti.id where bd.barcode like 'PK%' and ti.invoiceSendTime " + + betweenSql; + if(StringUtils.isNotBlank(tousseOperationDepart)){ + sql += SqlUtils.getInStringListSql("ti.depart", SqlUtils.splitStringToSet(tousseOperationDepart, ",")); + } + sql += " group by ti.depart"; + ResultSet rs = null; + try { + rs = objectDao.executeSql(sql); + while (rs.next()) { + String depart = rs.getString("depart"); + if(StringUtils.isBlank(depart)){ + depart = ""; + } + TousseOperationSumVo vo = null; + if(tousseOperationSumVoMap.containsKey(depart)){ + vo = tousseOperationSumVoMap.get(depart); + }else{ + vo = new TousseOperationSumVo(); + tousseOperationSumVoMap.put(depart, vo); + } + vo.setInvocieAmount(MathTools.add(vo.getInvocieAmount(), rs.getInt("amount")).intValue()); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + /** * 获取指定日期发货中有登记使用记录的器械包数 * @param betweenSql 时间过滤 * @param conditionalFiltering 额外的条件过滤 * @param tousseOperationSumVoMap 汇总vo的map */ - private void setUseAmount(String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ + private void setUseAmount(String pkConditionalFiltering, String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ String useSql = "select depart,count(distinct id) amount from (select i.depart,ti.id from " + TousseInstance.class.getSimpleName() +" ti join " @@ -451,6 +495,13 @@ +" i on i.id=ti.invoice2_id where ti.useRecord_id is not null and i.sendTime " + betweenSql + conditionalFiltering + + " union all select ti.depart,ti.id from " + + TousseInstance.class.getSimpleName() + +" ti join " + + BarcodeDevice.class.getSimpleName() + +" bd on bd.id=ti.id where bd.barcode like 'PK%' and ti.useRecord_id is not null and ti.invoiceSendTime " + + betweenSql + + pkConditionalFiltering + ") t group by depart"; ResultSet rs = null; try { @@ -481,7 +532,7 @@ * @param conditionalFiltering 额外的条件过滤 * @param tousseOperationSumVoMap 汇总vo的map */ - private void setOutStorageRecordAmount(String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ + private void setOutStorageRecordAmount(String pkConditionalFiltering, String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ String outStorageRecordSql = "select depart,count(distinct id) amount from (select i.depart,ti.id from " + StorageRecord.class.getSimpleName() + " sr join " @@ -500,6 +551,15 @@ +" i on i.id=ti.invoice2_id where sr.status='取出' and i.sendTime " + betweenSql + conditionalFiltering + + " union all select ti.depart,ti.id from " + + StorageRecord.class.getSimpleName() + + " sr join " + + TousseInstance.class.getSimpleName() + +" ti on ti.id=sr.tousseInstanceId join " + + BarcodeDevice.class.getSimpleName() + + " bd on bd.id=ti.id where bd.barcode like 'PK%' and sr.status='取出' and ti.invoiceSendTime " + + betweenSql + + pkConditionalFiltering + ") t group by depart"; ResultSet rs = null; try { @@ -530,7 +590,7 @@ * @param conditionalFiltering 额外的条件过滤 * @param tousseOperationSumVoMap 汇总vo的map */ - private void setStorageRecordAmount(String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ + private void setStorageRecordAmount(String pkConditionalFiltering, String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ String storageRecordSql = "select depart,count(distinct id) amount from (select i.depart,ti.id from " + StorageRecord.class.getSimpleName() + " sr join " @@ -549,6 +609,15 @@ +" i on i.id=ti.invoice2_id where sr.status='存入' and i.sendTime " + betweenSql + conditionalFiltering + + " union all select ti.depart,ti.id from " + + StorageRecord.class.getSimpleName() + + " sr join " + + TousseInstance.class.getSimpleName() + +" ti on ti.id=sr.tousseInstanceId join " + + BarcodeDevice.class.getSimpleName() + + " bd on bd.id=ti.id where bd.barcode like 'PK%' and sr.status='存入' and ti.invoiceSendTime " + + betweenSql + + pkConditionalFiltering + ") t group by depart"; ResultSet rs = null; try { @@ -617,8 +686,8 @@ * @param conditionalFiltering 额外的条件过滤 * @param tousseOperationSumVoMap 汇总vo的map */ - private void setSecondaryStorageAmount(String betweenSql, String conditionalFiltering , Map tousseOperationSumVoMap){ - String useSql = "select depart,count(distinct srid) - 1 amount from (select i.depart,ti.id,sr.id srid from " + private void setSecondaryStorageAmount(String pkConditionalFiltering, String betweenSql, String conditionalFiltering , Map tousseOperationSumVoMap){ + String sql = "select depart,count(distinct srid) - 1 amount from (select i.depart,ti.id,sr.id srid from " + StorageRecord.class.getSimpleName() + " sr join " + TousseInstance.class.getSimpleName() @@ -636,14 +705,23 @@ +" i on i.id=ti.invoice2_id where i.sendTime " + betweenSql + conditionalFiltering + + " union all select ti.depart,ti.id,sr.id srid from " + + StorageRecord.class.getSimpleName() + + " sr join " + + TousseInstance.class.getSimpleName() + +" ti on ti.id=sr.tousseInstanceId join " + + BarcodeDevice.class.getSimpleName() + +" bd on bd.id=ti.id where bd.barcode like 'PK%' and ti.invoiceSendTime " + + betweenSql + + pkConditionalFiltering + ") t " + " where exists (select 1 from " + StorageRecord.class.getSimpleName() +" sr where sr.status='存入' and sr.tousseInstanceId=t.id group by sr.tousseInstanceId having count(*)>1) " + "group by depart"; ResultSet rs = null; try { - rs = objectDao.executeSql(useSql); + rs = objectDao.executeSql(sql); while (rs.next()) { String depart = rs.getString("depart"); if(StringUtils.isBlank(depart)){ @@ -670,7 +748,7 @@ * @param conditionalFiltering 额外的条件过滤 * @param tousseOperationSumVoMap 汇总vo的map */ - private void setUsedAndNoOutStorageAmount(String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ + private void setUsedAndNoOutStorageAmount(String pkConditionalFiltering, String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ String sql = "select depart,count(distinct id) amount from (select i.depart,ti.id from " + TousseInstance.class.getSimpleName() +" ti join " @@ -685,6 +763,13 @@ +" i on i.id=ti.invoice2_id where ti.useRecord_id is not null and i.sendTime " + betweenSql + conditionalFiltering + + " union all select ti.depart,ti.id from " + + TousseInstance.class.getSimpleName() + +" ti join " + + BarcodeDevice.class.getSimpleName() + +" bd on bd.id=ti.id where bd.barcode like 'PK%' and ti.useRecord_id is not null and ti.invoiceSendTime " + + betweenSql + + pkConditionalFiltering + ") t where not exists (select 1 from " + StorageRecord.class.getSimpleName() +" sr where sr.tousseInstanceId=t.id and sr.operateDetail='手动取出')" @@ -718,7 +803,7 @@ * @param conditionalFiltering 额外的条件过滤 * @param tousseOperationSumVoMap 汇总vo的map */ - private void setInvoicedNoInStorageAmount(String betweenSql,String conditionalFiltering, Map tousseOperationSumVoMap){ + private void setInvoicedNoInStorageAmount(String pkConditionalFiltering, String betweenSql,String conditionalFiltering, Map tousseOperationSumVoMap){ String sql = "select depart,count(distinct id) amount from (select i.depart,ti.id from " + TousseInstance.class.getSimpleName() +" ti join " @@ -733,6 +818,13 @@ +" i on i.id=ti.invoice2_id where ti.useRecord_id is not null and i.sendTime " + betweenSql + conditionalFiltering + + " union all select ti.depart,ti.id from " + + TousseInstance.class.getSimpleName() + +" ti join " + + BarcodeDevice.class.getSimpleName() + +" bd on bd.id=ti.id where bd.barcode like 'PK%' and ti.useRecord_id is not null and ti.invoiceSendTime " + + betweenSql + + pkConditionalFiltering + ") t where not exists (select 1 from " + StorageRecord.class.getSimpleName() +" sr where sr.tousseInstanceId=t.id and sr.status='存入')" @@ -766,7 +858,7 @@ * @param conditionalFiltering 额外的条件过滤 * @param tousseOperationSumVoMap 汇总vo的map */ - private void setNoOperationRecordAmount(String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ + private void setNoOperationRecordAmount(String pkConditionalFiltering, String betweenSql, String conditionalFiltering, Map tousseOperationSumVoMap){ String conditionSql = " (lastManualOpe.nextOperationTime > lastManualOpe.nextOperationLimitTime " + "or (lastManualOpe.nextOperationTime is null and lastManualOpe.nextOperationLimitTime < " + dateQueryAdapter.getTodayAdapter() +")) " @@ -790,6 +882,15 @@ +" i on i.id=ti.invoice2_id where ti.status in ('已发货','已签收') and i.sendTime " + betweenSql + conditionalFiltering + + " union all select ti.depart,ti.id from " + + StorageRecord.class.getSimpleName() + + " sr join " + + TousseInstance.class.getSimpleName() + +" ti on ti.id=sr.tousseInstanceId join " + + BarcodeDevice.class.getSimpleName() + +" bd on bd.id=ti.id where bd.barcode like 'PK%' and ti.status in ('已发货','已签收') and ti.invoiceSendTime " + + betweenSql + + pkConditionalFiltering + ") t1 " + " join (select t.tousseInstanceId,t.nextOperationTime,t.nextOperationLimitTime,t.storageRecordId from " + TousseOperation.class.getSimpleName() +" t where t.id =(select max(tn.id) id from " @@ -837,8 +938,6 @@ StringBuffer sbf = new StringBuffer(); List vos = new ArrayList(); StringBuffer conditionSql = new StringBuffer(); - conditionSql.append("i.sendTime"); - conditionSql.append(betweenSql); if(StringUtils.isNotBlank(barcode)){ conditionSql.append(" and bd.barcode='"); conditionSql.append(barcode).append("' "); @@ -865,7 +964,6 @@ } LoginUserData user = AcegiHelper.getLoginUser(); String currentOrgUnitName = user.getCurrentOrgUnitName(); - conditionSql.append(" and i.depart='"+ currentOrgUnitName +"'"); sbf.append("select * from (select bd.barcode,ti.signedUser,ti.signedDate,td.name,i.sendTime,i.sender" + ",lastIntoSto.time lastIntoStoTime,lastIntoSto.operator lastIntoStoOperator,ti.lastStorageLocationId" + ",lastInvoiceOpe.operator lastInvoiceOpeOperator " @@ -902,8 +1000,10 @@ + TousseOperation.class.getSimpleName() +" t where t.id =(select max(tn.id) id from " + TousseOperation.class.getSimpleName() +" tn where " + "tn.operationType='"+ TousseOperation.OPERATION_TYPE_MANUAL - +"' and tn.tousseInstanceId=t.tousseInstanceId)) lastManualOpe on lastManualOpe.tousseInstanceId=ti.id where " + +"' and tn.tousseInstanceId=t.tousseInstanceId)) lastManualOpe on lastManualOpe.tousseInstanceId=ti.id where i.sendTime " + + betweenSql + conditionSql + + " and i.depart='"+ currentOrgUnitName +"'" + " and ti.invoice2_id is null" + " union all " + "select bd.barcode,ti.signedUser,ti.signedDate,td.name,i.sendTime,i.sender" @@ -942,8 +1042,51 @@ + TousseOperation.class.getSimpleName() +" t where t.id =(select max(tn.id) id from " + TousseOperation.class.getSimpleName() +" tn where " + "tn.operationType='"+ TousseOperation.OPERATION_TYPE_MANUAL - +"' and tn.tousseInstanceId=t.tousseInstanceId)) lastManualOpe on lastManualOpe.tousseInstanceId=ti.id where " + +"' and tn.tousseInstanceId=t.tousseInstanceId)) lastManualOpe on lastManualOpe.tousseInstanceId=ti.id where i.sendTime " + + betweenSql + conditionSql + + " and i.depart='"+ currentOrgUnitName +"'" + //聚力康的器械包 没有发货单 包条码以PK开头 + + " union all " + + "select bd.barcode,ti.signedUser,ti.signedDate,td.name,ti.invoiceSendTime,ti.invoiceSender" + + ",lastIntoSto.time lastIntoStoTime,lastIntoSto.operator lastIntoStoOperator,ti.lastStorageLocationId" + + ",lastInvoiceOpe.operator lastInvoiceOpeOperator " + + ",lastInvoiceOpe.operationTime lastInvoiceOpeOperationTime,ur.enteringDate,ur.operator useRecordOperator " + + ",(select count(*) from " + + TousseOperation.class.getSimpleName() + +" t where t.tousseInstanceId=ti.id and t.operation in('回退','发货')) tousseOperationAmount " + + ",case when (lastManualOpe.nextOperationTime > lastManualOpe.nextOperationLimitTime " + + "or (lastManualOpe.nextOperationTime is null and lastManualOpe.nextOperationLimitTime < "+ todaySql +")) " + + "and ti.status in ('" + TousseInstance.STATUS_SHIPPED + "','" + TousseInstance.STATUS_SIGNED + "') " + + "and lastManualOpe.storageRecordId is null then '异常' else '正常' end status " + + ",(select max(s.wareHouseName) from " + + StorageLocation.class.getSimpleName() + +" s join Storage_TousseInstance st on st.storageLocationId=s.id and st.tousseInstanceId=ti.id ) wareHouseName " + + " ,case when exists (select 1 from StorageRecord s where s.tousseInstanceId=ti.id and s.status='取出') then '有取出记录' else '无取出记录' end storageStatus " + + "from "+ TousseInstance.class.getSimpleName() + +" ti join " + + TousseDefinition.class.getSimpleName() + +" td on td.id=ti.TousseDefinition_id join " + + BarcodeDevice.class.getSimpleName() + +" bd on bd.id=ti.id left join " + + UseRecord.class.getSimpleName() +" ur on ur.id=ti.useRecord_id left join (select sr.tousseInstanceId,sr.time,sr.operator from " + + StorageRecord.class.getSimpleName() + +" sr where sr.id =(select max(sri.id) from " + + StorageRecord.class.getSimpleName() + +" sri where sri.status='存入')) lastIntoSto on lastIntoSto.tousseInstanceId=ti.id " + + "left join (select t.id,t.tousseInstanceId,t.operator,t.operationTime from " + + TousseOperation.class.getSimpleName() + +" t where id =(select max(id) from " + + TousseOperation.class.getSimpleName() + + " where operation='发货')) lastInvoiceOpe on lastInvoiceOpe.tousseInstanceId=ti.id " + + " left join (select t.tousseInstanceId,t.nextOperationTime,t.nextOperationLimitTime,t.storageRecordId from " + + TousseOperation.class.getSimpleName() +" t where t.id =(select max(tn.id) id from " + + TousseOperation.class.getSimpleName() +" tn where " + + "tn.operationType='"+ TousseOperation.OPERATION_TYPE_MANUAL + +"' and tn.tousseInstanceId=t.tousseInstanceId)) lastManualOpe on lastManualOpe.tousseInstanceId=ti.id where bd.barcode like 'PK%' and ti.invoiceSendTime " + + betweenSql + + conditionSql + + " and ti.depart='"+ currentOrgUnitName +"'" + ") t order by sendTime desc"); ResultSet rs = null; try { @@ -958,19 +1101,18 @@ if(lastIntoStoTime != null){ vo.setLastIntoStoTime(df.format(lastIntoStoTime)); } - - Date lastInvoiceOpeOperationTime = rs.getTimestamp("lastInvoiceOpeOperationTime"); - if(lastInvoiceOpeOperationTime != null){ - vo.setLastInvoiceOpeOperationTime(df.format(lastInvoiceOpeOperationTime)); - } - vo.setLastInvoiceOpeOperator(rs.getString("lastInvoiceOpeOperator")); String status = rs.getString("status"); if("异常".equals(status)){ vo.setNoOperationRecord(Constants.STR_YES); }else{ vo.setNoOperationRecord(Constants.STR_NO); } - vo.setTousseOperationAmount(rs.getInt("tousseOperationAmount")); + int tousseOperationAmount = rs.getInt("tousseOperationAmount"); + if(tousseOperationAmount < 1){ + vo.setTousseOperationAmount(""); + }else{ + vo.setTousseOperationAmount(Integer.toString(tousseOperationAmount)); + } vo.setSender(rs.getString("sender")); Date sendTime = rs.getTimestamp("sendTime"); if(sendTime != null){ @@ -997,6 +1139,15 @@ vo.setStorageStatus(""); } } + if(StringUtils.isBlank(vo.getStorageStatus()) || "在库".equals(vo.getStorageStatus())){ + //在库时 不显示最后领出时间和领出人 + }else{ + Date lastInvoiceOpeOperationTime = rs.getTimestamp("lastInvoiceOpeOperationTime"); + if(lastInvoiceOpeOperationTime != null){ + vo.setLastInvoiceOpeOperationTime(df.format(lastInvoiceOpeOperationTime)); + } + vo.setLastInvoiceOpeOperator(rs.getString("lastInvoiceOpeOperator")); + } vo.setUseRecordOperator(rs.getString("useRecordOperator")); if(StringUtils.isNotBlank(vo.getUseRecordEnteringDate())){ vo.setUsed(Constants.STR_YES); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/TousseOperationDetailVo.java =================================================================== diff -u -r37663 -r37737 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/TousseOperationDetailVo.java (.../TousseOperationDetailVo.java) (revision 37663) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/TousseOperationDetailVo.java (.../TousseOperationDetailVo.java) (revision 37737) @@ -70,7 +70,7 @@ /** * 出库后流转次数 */ - private Integer tousseOperationAmount; + private String tousseOperationAmount; /** * 是否领出后无操作记录 */ @@ -98,11 +98,6 @@ this.storageStatus = storageStatus; } public void setWareHouseName(String wareHouseName) { - if(StringUtils.isNotBlank(wareHouseName)){ - this.storageStatus="在库"; - }else{ - this.storageStatus="出库"; - } this.wareHouseName = wareHouseName; } public String getSendTime() { @@ -171,10 +166,10 @@ public void setUseRecordOperator(String useRecordOperator) { this.useRecordOperator = useRecordOperator; } - public Integer getTousseOperationAmount() { + public String getTousseOperationAmount() { return tousseOperationAmount; } - public void setTousseOperationAmount(Integer tousseOperationAmount) { + public void setTousseOperationAmount(String tousseOperationAmount) { this.tousseOperationAmount = tousseOperationAmount; } public String getNoOperationRecord() {