Index: ssts-tousse/src/main/java/com/forgon/disinfectsystem/tousse/toussedefinition/service/TousseInstanceManagerImpl.java =================================================================== diff -u -r38076 -r38121 --- ssts-tousse/src/main/java/com/forgon/disinfectsystem/tousse/toussedefinition/service/TousseInstanceManagerImpl.java (.../TousseInstanceManagerImpl.java) (revision 38076) +++ ssts-tousse/src/main/java/com/forgon/disinfectsystem/tousse/toussedefinition/service/TousseInstanceManagerImpl.java (.../TousseInstanceManagerImpl.java) (revision 38121) @@ -877,19 +877,20 @@ // 根据灭菌方式过滤SZYK-5 String sterilingModeSql = ""; String sterilingModeAncestorSql = ""; - String joinsterilingModeSql = null; if(StringUtils.equals(sterilingMode, "无")){ - joinsterilingModeSql = " left join Sterilisation s on s.sterilisation= tdancestor.sterilingMethod "; sterilingModeSql = " and (ti.sterilingMode is null or ti.sterilingMode = '') "; - sterilingModeAncestorSql = " and ((case when tdancestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else ti.sterilingMode end) is null or (case when tdAncestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else ti.sterilingMode end)='') "; + sterilingModeAncestorSql = " and (case when tdancestor.isTraceable = '无' then " + + " (select max(s.sterilizationMode) from Sterilisation s where s.sterilisation= tdancestor.sterilingMethod) " + + " else ti.sterilingMode end is null or case when tdancestor.isTraceable = '否' " + + " then (select max(s.sterilizationMode) from Sterilisation s where s.sterilisation= tdancestor.sterilingMethod) " + + " else ti.sterilingMode end='') "; }else if(StringUtils.isNotBlank(sterilingMode) && !StringUtils.equals(sterilingMode, "全部")){ - joinsterilingModeSql = " left join Sterilisation s on s.sterilisation= tdancestor.sterilingMethod "; sterilingModeSql = " and ti.sterilingMode = '" + sterilingMode + "' "; - sterilingModeAncestorSql = " and (case when tdancestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else ti.sterilingMode end)='" + sterilingMode + "' "; - }else{ - joinsterilingModeSql = ""; + sterilingModeAncestorSql = " and (case when tdancestor.isTraceable = '"+ Constants.STR_NO +"' then " + + "(select max(s.sterilizationMode) from Sterilisation s where s.sterilisation= tdancestor.sterilingMethod) " + + "else ti.sterilingMode end)='" + sterilingMode + "' "; } - return "select sum(amount) from ((select count(0) amount from " + return "select sum(amount) from ((select count(0) amount from " + "TousseInstance ti join toussedefinition td on ti.tousseDefinition_id=td.id " + "join toussedefinition tdancestor on td.ancestorID=tdancestor.id where ti.comboTousseInstanceId is null " + sterilingModeSql @@ -899,8 +900,7 @@ + " union " - + "(select count(0) amount from TousseInstance ti join toussedefinition td on ti.tousseDefinition_id=td.id join toussedefinition tdancestor on td.ancestorID=tdancestor.id " - + joinsterilingModeSql + + "(select count(distinct tdancestor.id) amount from TousseInstance ti join toussedefinition td on ti.tousseDefinition_id=td.id join toussedefinition tdancestor on td.ancestorID=tdancestor.id " + "where 1=1 and ti.comboTousseInstanceId is null and tdancestor.isTraceable='否'" +keyWordSearchSql +" and ti.status='"+TousseInstance.STATUS_REVIEWED+"' and ti.comboTousseInstanceId is null "+departSearchSql+" and ti.reviewBasket_id is null " @@ -1045,18 +1045,13 @@ sterilingModeSql = " and ti.sterilingMode = '" + sterilingMode + "' "; sterilingModeAncestorSql = " and s.sterilizationMode='" + sterilingMode + "' "; } - - //关联来源科室的sql语句 - String departJoinSql = String.format(" left join %s ou on ou.orgUnitCoding = ti.departCoding ", OrgUnit.class.getSimpleName()); - - return "select * from (select top "+amount+" * from " - + "((select '是' isTraceable,null tdAncestorId,ti.id, ou.name depart,ti.tousseName,b.barcode,ti.reviewTime,ti.sterilingMode,1 amount,ti.isUrgentTousse " + return "select * from (select ROW_NUMBER() OVER (order by ulGrade desc) rowNum,isTraceable,tdAncestorId,id,depart,tousseName,barcode,reviewTime,sterilingMode,amount,isUrgentTousse,ulId,ulGrade,ulName,ulColorCode from " + + "((select '是' isTraceable,null tdAncestorId,ti.id,(select max(ou.name) from OrgUnit ou where ou.orgUnitCoding=ti.departCoding) depart,ti.tousseName,b.barcode,ti.reviewTime,ti.sterilingMode,1 amount,ti.isUrgentTousse " + urgentLevel_SelectSql//加急信息 + "from TousseInstance ti " + " join barcodeDevice b on b.id=ti.id join toussedefinition td on ti.tousseDefinition_id=td.id " + " join toussedefinition tdancestor on td.ancestorID=tdancestor.id " + urgentLevel_JoinSql - + departJoinSql + " where tdancestor.isTraceable='是' "+keyWordSearchSql+" and " + "ti.status='"+TousseInstance.STATUS_REVIEWED+"' and ti.comboTousseInstanceId is null "+departSearchSql+" and ti.reviewBasket_id is null " + sterilingModeSql @@ -1066,8 +1061,7 @@ + " union " + "(select '否' isTraceable, tdancestor.id tdAncestorId,null id,null depart,tdancestor.name tousseName" - //,min(b.barcode) 只是为了分页中的barcode not in - + ",min(b.barcode) barcode,null reviewTime , s.sterilizationMode sterilingMode,count(0) amount " + + ",null barcode,null reviewTime , s.sterilizationMode sterilingMode,count(0) amount " + " , null isUrgentTousse " + notQueryUrgentSql + "from TousseInstance ti " @@ -1077,27 +1071,8 @@ + " where 1=1 and tdancestor.isTraceable='否' "+keyWordSearchSql+" " + sterilingModeAncestorSql + "and ti.status='"+TousseInstance.STATUS_REVIEWED+"' and ti.comboTousseInstanceId is null "+departSearchSql+" and ti.reviewBasket_id is null " - + "and ti.orgunitcoding='"+orgUnitCoding+"' group by tdancestor.id,tdancestor.name,s.sterilizationMode)) tt " + orderSql + ") t " - + "where" - + " t.barcode not in (select top "+start+" barcode from " - + "((select tdancestor.id,depart,ti.tousseName,b.barcode,ti.reviewTime,1 amount from TousseInstance ti " - + "join barcodeDevice b on b.id=ti.id join toussedefinition td on ti.tousseDefinition_id=td.id join " - + "toussedefinition tdancestor on td.ancestorID=tdancestor.id where tdancestor.isTraceable='是' "+keyWordSearchSql+" " - + "and ti.status='"+TousseInstance.STATUS_REVIEWED+"' and ti.comboTousseInstanceId is null "+departSearchSql+" " - + sterilingModeSql - + " and ti.reviewBasket_id is null and ((ti.proxyDisinfection_id is null and ti.orgUnitCoding='"+orgUnitCoding+"') " - + "or (ti.proxyDisinfection_id is not null and ti.proxyDisinfection_id in (select ip.id from InvoicePlan ip where ip.handleDepartCoding='"+orgUnitCoding+"'))))" - + " union (select tdancestor.id,null depart,tdancestor.name " - + "tousseName,min(b.barcode) barcode,null reviewTime,count(0) amount from TousseInstance ti join barcodeDevice " - + "b on b.id=ti.id join toussedefinition td on ti.tousseDefinition_id=td.id join " - + "toussedefinition tdancestor on td.ancestorID=tdancestor.id " - + "left join Sterilisation s on s.sterilisation = tdancestor.sterilingMethod " - + "where 1=1 and tdancestor.isTraceable='否' "+keyWordSearchSql+" " - + sterilingModeAncestorSql - + "and ti.status='"+TousseInstance.STATUS_REVIEWED+"' and ti.comboTousseInstanceId is null "+departSearchSql+" and ti.reviewBasket_id is null " - + "and ((ti.proxyDisinfection_id is null and ti.orgUnitCoding='"+orgUnitCoding+"') " - + "or (ti.proxyDisinfection_id is not null and ti.proxyDisinfection_id in (select ip.id from InvoicePlan ip where ip.handleDepartCoding='"+orgUnitCoding+"'))) group by tdancestor.id,tdancestor.name)) aa " + orderSql + " )" - + orderSql; + + "and ti.orgunitcoding='"+orgUnitCoding+"' group by tdancestor.id,tdancestor.name,s.sterilizationMode)) tt " + + ") t2 where t2.rowNum between "+ (start + 1) +" and "+ amount; } /** @@ -1144,10 +1119,11 @@ }else if(dbConnection.isOracle()){ findSql = getTousseInstanceWithOutBasketByOracle(start,limit,orgUnitCode,departSearchSql,keyWordSearchSql,orderSql,sterilingMode); } - ResultSet rs = objectDao.executeSql(findSql); + ResultSet rs = null; List tousseList = new ArrayList(); Set tdIdisNotTraceable = new HashSet(); try { + rs = objectDao.executeSql(findSql); while(rs.next()){ TousseInstanceVo vo = new TousseInstanceVo(); vo.setId(rs.getLong("id"));