Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java =================================================================== diff -u -r35983 -r36121 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java (.../RealTimeBulletinBoardWorkloadHelper.java) (revision 35983) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java (.../RealTimeBulletinBoardWorkloadHelper.java) (revision 36121) @@ -8,6 +8,7 @@ import java.util.Date; import java.util.List; +import net.sf.json.JSONArray; import net.sf.json.JSONObject; import org.apache.commons.lang.StringUtils; @@ -17,8 +18,10 @@ import com.forgon.directory.model.BarcodeDevice; import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager; 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.rinser.Rinser; +import com.forgon.disinfectsystem.entity.basedatamanager.sterilisation.Sterilisation; import com.forgon.disinfectsystem.entity.basedatamanager.sterilizer.Sterilizer; import com.forgon.disinfectsystem.entity.basedatamanager.supplyroomconfig.SupplyRoomConfig; import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; @@ -33,6 +36,7 @@ import com.forgon.disinfectsystem.entity.sterilizationmanager.proxydisinfection.ProxyDisinfection; import com.forgon.disinfectsystem.entity.sterilizationmanager.sterilizationrecord.SterilizationRecord; import com.forgon.disinfectsystem.entity.tousseitem.TousseItem; +import com.forgon.disinfectsystem.entity.urgent.UrgentLevel; import com.forgon.disinfectsystem.entity.washanddisinfectmanager.washanddisinfectrecord.WashAndDisinfectRecord; import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; import com.forgon.tools.db.DatabaseUtil; @@ -243,6 +247,26 @@ } finally { DatabaseUtil.closeResultSetAndStatement(rs); } + ResultSet rs2 = null; + JSONArray arr = new JSONArray(); + try { + String toBeInvoiceUrgentSumSql = " select sum(urgentAmount) urgentAmount,name,tousseName,colorCode from ( " + + getToBeInvoiceUrgentSumSql(handleDepartCoding, "", toBeBetweenSql) + ") tb group by name,tousseName,grade,colorCode order by grade desc "; + rs2= objectDao.executeSql(toBeInvoiceUrgentSumSql); + while (rs2.next()) { + JSONObject urgentItem = new JSONObject(); + urgentItem.put("urgentAmount", rs2.getInt("urgentAmount")); + urgentItem.put("name", rs2.getString("name")); + urgentItem.put("tousseName", rs2.getString("tousseName")); + urgentItem.put("colorCode", rs2.getString("colorCode")); + arr.add(urgentItem); + } + } catch (Exception e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs2); + } + obj.put("toBeInvoiceUrgentSum", arr); return obj; } /** @@ -621,6 +645,223 @@ return sql; } /** + * 统计当天待发货的各个包各个加急等级的加急包总数 + * @param handleDepartCoding 一级供应室编码 + * @param tousseTypesql 包类型过滤 + * @param toBeBetweenSql 时间过滤 + * @return + */ + private String getToBeInvoiceUrgentSumSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){ + String amountSql = "((case when (po.type='" + + InvoicePlan.TYPE_BORROWINGSINGLE + +"' or po.type='" + + InvoicePlan.TYPE_PROXYDISINFECTION + +"' or po.type='" + + InvoicePlan.TYPE_GOODS_RESERVATION_FORM + + "') then ti.amount else case when ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_INSIDE + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_DISINFECTION + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_CUSTOM + +"' or ti.tousseType = '" + +TousseDefinition.PACKAGE_TYPE_FOREIGN + +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 " + + " then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount " + + " end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = 3 " + + " then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end " + + " else (case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end ) " + + " end else ti.amount end end) - ti.sendoutAmount) "; + String commonFilterSql = " and ((po.deliverStatus='" + + InvoicePlan.DELIVERSTATUS_AWAITDELIVER + +"' or po.deliverStatus='" + + InvoicePlan.DELIVERSTATUS_PARTDELIVERED +"') and (po.endStatus is null or po.endStatus!='" + + InvoicePlan.STATUS_END +"')) and (po.committedStatus = 1) and po.handleDepartCoding = '" + + handleDepartCoding + +"' "; + commonFilterSql += tousseTypesql + SqlUtils.buildFieldNameBetweenSql("po.applicationTime", toBeBetweenSql); + //外来器械申请单 + String sql = " select (case when (ti.urgentAmount is null or ti.urgentAmount <= ti.sendoutAmount) then 0 else (ti.urgentAmount - ti.sendoutAmount) end) urgentAmount,ul.name,ul.colorCode,ul.grade,td.name tousseName from " + + TousseItem.class.getSimpleName() + +" ti left join " + + TousseDefinition.class.getSimpleName() + +" td on ti.tousseDefinitionId=td.id " + + " join " + + UrgentLevel.class.getSimpleName() + +" ul on ul.id=ti.urgentLevel_id " + + " join " + + InvoicePlan.class.getSimpleName() + +" po on ti.recyclingApplication_ID=po.id where ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) and " + + amountSql + + " > 0 and (( ti.tousseType IN ('" + + TousseDefinition.PACKAGE_TYPE_SPLIT + +"','" + + TousseDefinition.PACKAGE_TYPE_FOREIGN + +"') )) " + + commonFilterSql + + "and po.type='" + + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + +"' and po.id not in (select foreignTousseApp_id from TousseInstance where foreignTousseApp_id is not null) " + + "and (amount - sendOutAmount) > 0 union all select (case when tis.urgentLevel_id is null then 0 else 1 end) urgentAmount,ul.name,ul.colorCode,ul.grade,td.name tousseName from " + + TousseInstance.class.getSimpleName() + +" tis join "+ UrgentLevel.class.getSimpleName() +" ul on ul.id=tis.urgentLevel_id join " + + InvoicePlan.class.getSimpleName() + +" po on tis.foreignTousseApp_id=po.id left join " + + TousseDefinition.class.getSimpleName() + +" td on tis.tousseDefinition_id=td.id where 1=1 " + + commonFilterSql + + "and po.type='" + + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + + "' and (( tis.status IN ('" + + TousseInstance.STATUS_PACKED + +"','" + + TousseInstance.STATUS_REVIEWED + +"','" + + TousseInstance.STATUS_STERILING + +"','" + + TousseInstance.STATUS_DELAY_CONFIRM + +"','" + + TousseInstance.STATUS_STERILED + +"') )) "; + //外部器械包代理灭菌申请单','自定义器械包申请单 , 代理灭菌单 + sql += " union all select (case when (ti.urgentAmount is null or ti.urgentAmount <= ti.sendoutAmount) then 0 else (ti.urgentAmount - ti.sendoutAmount) end) urgentAmount,ul.name,ul.colorCode,ul.grade,td.name tousseName from " + + TousseItem.class.getSimpleName() + +" ti left join " + + TousseDefinition.class.getSimpleName() + +" td on ti.tousseDefinitionId=td.id " + + " join " + + UrgentLevel.class.getSimpleName() + +" ul on ul.id=ti.urgentLevel_id " + + "join " + + InvoicePlan.class.getSimpleName() + +" po on ti.recyclingApplication_ID=po.id where 1=1" + + commonFilterSql + + "and (( po.type IN ('" + + InvoicePlan.TYPE_FOREIGNPROXYDISINFECTION + +"','"+ InvoicePlan.TYPE_CUSTOM_TOUSSE_APPLIACTION_FORM + +"') )) and ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) and " + + amountSql + + "> 0 and (( ti.tousseType IN ('" + + TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY + +"','" + + TousseDefinition.PACKAGE_TYPE_SPLIT + +"','" + + TousseDefinition.PACKAGE_TYPE_CUSTOM + +"','" + + TousseDefinition.PACKAGE_TYPE_DRESSING + +"','" + + TousseDefinition.PACKAGE_TYPE_INSIDE + +"','" + + DisposableGoods.TYPE_NAME + +"','" + + TousseDefinition.PACKAGE_TYPE_FOREIGN + +"','" + + TousseDefinition.PACKAGE_TYPE_DISINFECTION + +"') )) and (case when (po.type='" + + InvoicePlan.TYPE_BORROWINGSINGLE + +"' or po.type='" + + InvoicePlan.TYPE_PROXYDISINFECTION + +"' or po.type='" + + InvoicePlan.TYPE_GOODS_RESERVATION_FORM + +"') then ti.amount else case when ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_INSIDE + + "' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_DISINFECTION + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_CUSTOM + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_FOREIGN + +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 then case when ti.invoiceAmountMode = 1 " + + "then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount end when ti.invoiceAmountMode = 2 " + + "then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = 3 then case when ti.recyclingAmount is null " + + "then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end else (case when ti.recyclingAmount is not null then ti.recyclingAmount " + + "when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end )" + + " end else ti.amount end end) > 0 "; + //'敷料包','器械包' + sql += " union all select sum(case when (ti.urgentAmount is null) then 0 else (ti.urgentAmount) end) urgentAmount,ul.name,ul.colorCode,ul.grade,ti.tousseName from " + + TousseItem.class.getSimpleName() + +" ti " + + " join " + + UrgentLevel.class.getSimpleName() + +" ul on ul.id=ti.urgentLevel_id " + + "join " + + InvoicePlan.class.getSimpleName() + +" po on ti.recyclingApplication_ID=po.id join " + + TousseDefinition.class.getSimpleName() + +" td on td.id=ti.tousseDefinitionId where ((po.type <> '" + + InvoicePlan.TYPE_BORROWINGSINGLE + +"' or (po.type = '" + + InvoicePlan.TYPE_BORROWINGSINGLE + +"' and (po.borrowConfirmStatus is null or po.borrowConfirmStatus <> '待审核')))) " + + commonFilterSql + + " and ( po.type NOT IN ('" + + InvoicePlan.TYPE_QUALITY_MONITOR_RECYCLE_APPLICATION + +"','" + + InvoicePlan.TYPE_SECOND_SUPPLY_ROOM + +"','" + + InvoicePlan.TYPE_EXPENSIVEGOODS_FORM + +"','" + + InvoicePlan.TYPE_OPERATION_RESERVATION_APPLICATION + +"','" + + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION + +"','" + + InvoicePlan.TYPE_PROXYDISINFECTION + +"','" + + InvoicePlan.TYPE_FOREIGNPROXYDISINFECTION + +"','" + + InvoicePlan.TYPE_CUSTOM_TOUSSE_APPLIACTION_FORM + +"') ) and (1=1) and ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) " + + "and ((case when (po.type='" + + InvoicePlan.TYPE_BORROWINGSINGLE + +"' or po.type='" + + InvoicePlan.TYPE_PROXYDISINFECTION + +"' or po.type='" + + InvoicePlan.TYPE_GOODS_RESERVATION_FORM + +"') then ti.amount else case when ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_INSIDE + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_DISINFECTION + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_CUSTOM + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_FOREIGN + +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when " + + "ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount " + + "else ti.recyclingAmount end when ti.invoiceAmountMode = 3 then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else " + + "ti.amount end else ti.amount end else (case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount " + + "else ti.amount end ) end else ti.amount end end) - ti.sendOutAmount) > 0 " + + "and (( ti.tousseType IN ('" + + TousseDefinition.PACKAGE_TYPE_DRESSING + +"','" + + TousseDefinition.PACKAGE_TYPE_INSIDE + +"') )) and ((case when ti.tousseType='" + + DisposableGoods.TYPE_NAME + +"' then case when (ti.transferScale is not null and ti.transferScale > 0) then ti.amount/ti.transferScale else ti.amount" + + " end else (case when (po.type='" + + InvoicePlan.TYPE_BORROWINGSINGLE + +"' or po.type='" + + InvoicePlan.TYPE_PROXYDISINFECTION + +"' or po.type='" + + InvoicePlan.TYPE_GOODS_RESERVATION_FORM + +"') then ti.amount else case when ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_INSIDE + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_DISINFECTION + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_CUSTOM + +"' or ti.tousseType = '" + + TousseDefinition.PACKAGE_TYPE_FOREIGN + +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 " + + "then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else " + + "ti.recyclingAmount end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = " + + "3 then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end else " + + "(case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end ) " + + "end else ti.amount end end) end) - (case when (ti.transferScale is not null and ti.transferScale > 0) then ti.sendOutAmount/ti.transferScale else ti.sendOutAmount end)) " + + "> 0 group by tousseName,ul.name,ul.colorCode,ul.grade "; + + return sql; + } + /** * 获取当天待灭菌的总包数sql * @param handleDepartCoding 一级供应室编码 * @param tousseTypesql 包类型过滤 @@ -709,39 +950,54 @@ * @return */ public List getEquipmentData() { - String sql = " select status,dateStr,name from (select 1 orderNum,case when exists (select wr.id from " - + WashAndDisinfectRecord.class.getSimpleName() - +" wr where wr.rinserId=r.id and wr.washStatus='" - + WashAndDisinfectRecord.STATUS_WASHING - +"' ) then '使用中' else '空闲' end status,(select max(wr.endDate) from " - + WashAndDisinfectRecord.class.getSimpleName() - + " wr where wr.rinserId=r.id and wr.washStatus='" - + WashAndDisinfectRecord.STATUS_WASHING - +"') dateStr,r.name from " - + Rinser.class.getSimpleName() - +" r where r.useStatus='" - + Rinser.USESTATUS_IN_USE - +"' and r.orgUnitCoding in (select max(orgUnitCoding) from " - + SupplyRoomConfig.class.getSimpleName() - +" where supplyRoomType=1) " - + "union all select 2 orderNum,case when exists (select id from " - + SterilizationRecord.class.getSimpleName() - +" sr where sr.sterilizer_id=s.id and sr.status='" - + SterilizationRecord.STERILIZATION_STATUS_BEGIN - +"' ) then '使用中' else '空闲' end status , (select max(sr.endDate) from " - + SterilizationRecord.class.getSimpleName() - +" sr where sr.sterilizer_id=s.id and sr.status='" - + SterilizationRecord.STERILIZATION_STATUS_BEGIN - +"') dateStr,s.name from " - + Sterilizer.class.getSimpleName() - +" s where s.useStatus='" - + Sterilizer.USESTATUS_IN_USE - +"' and s.orgUnitCoding in (select max(orgUnitCoding) from " - + SupplyRoomConfig.class.getSimpleName() - +" where supplyRoomType=1) " - + ") t order by orderNum, name "; + StringBuffer sbf = new StringBuffer(); + sbf.append("select status,dateStr,name 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); + sbf.append("' ) then '使用中' else '空闲' end status,(select max(case when wr.endDate is null then "); + if(dbConnection.isOracle()){ + sbf.append("ADD_MONTHS(wr.startDate, c.cleanMinutes)"); + }else{ + sbf.append("DATEADD(minute, c.cleanMinutes, wr.startDate)"); + } + sbf.append(" else wr.endDate end) from "); + sbf.append(WashAndDisinfectRecord.class.getSimpleName()); + sbf.append(" wr join "); + 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(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(SupplyRoomConfig.class.getSimpleName()); + sbf.append(" where supplyRoomType=1) union all select 2 orderNum,case when exists (select id from "); + sbf.append(SterilizationRecord.class.getSimpleName()); + sbf.append(" sr where sr.sterilizer_id=s.id and sr.status='"); + sbf.append(SterilizationRecord.STERILIZATION_STATUS_BEGIN); + sbf.append("' ) then '使用中' else '空闲' end status , (select max(case when sn.useTime is not null then "); + if(dbConnection.isOracle()){ + sbf.append("ADD_MONTHS(sr.startDate, sn.useTime)"); + }else{ + sbf.append("DATEADD(minute, sn.useTime, sr.startDate)"); + } + sbf.append(" else null end) from "); + sbf.append(SterilizationRecord.class.getSimpleName()); + sbf.append(" sr join "); + 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(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(SupplyRoomConfig.class.getSimpleName() ); + sbf.append(" where supplyRoomType=1) ) t order by orderNum, name "); List arr = new ArrayList(); - setEquipmentData(arr, sql); + setEquipmentData(arr, sbf.toString()); return arr; } /**