Index: ssts-tousse/src/main/java/com/forgon/disinfectsystem/tousse/toussedefinition/service/TousseInstanceManagerImpl.java =================================================================== diff -u -r36516 -r36529 --- ssts-tousse/src/main/java/com/forgon/disinfectsystem/tousse/toussedefinition/service/TousseInstanceManagerImpl.java (.../TousseInstanceManagerImpl.java) (revision 36516) +++ ssts-tousse/src/main/java/com/forgon/disinfectsystem/tousse/toussedefinition/service/TousseInstanceManagerImpl.java (.../TousseInstanceManagerImpl.java) (revision 36529) @@ -939,11 +939,12 @@ sterilingModeAncestorSql = " and s.sterilizationMode='" + sterilingMode + "' "; } - return "select v1.* from (select rownum rn, v.* from ((select '是' isTraceable,tdancestor.id tdAncestorId,ti.id,depart,ti.tousseName," + return "select v1.* from (select rownum rn, v.* from ((select '是' isTraceable,tdancestor.id tdAncestorId,ti.id, ou.name depart,ti.tousseName," + "b.barcode,ti.reviewTime, ti.sterilingMode,1 amount " + 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 " + + " left join OrgUnit ou on ou.orgUnitCoding = ti.departCoding " +urgentLevel_JoinSql + " where ti.comboTousseInstanceId is null and tdancestor.isTraceable='是' "+keyWordSearchSql+" " + sterilingModeSql @@ -994,14 +995,18 @@ 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,tdancestor.id tdAncestorId,ti.id,depart,ti.tousseName,b.barcode,ti.reviewTime,ti.sterilingMode,1 amount " + + "((select '是' isTraceable,tdancestor.id tdAncestorId,ti.id, ou.name depart,ti.tousseName,b.barcode,ti.reviewTime,ti.sterilingMode,1 amount " + 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 @@ -1222,10 +1227,11 @@ } else if(dbConnection.isOracle()){ firstBarcodeSql = "(select barcode from "+ TousseInstance.class.getSimpleName() +" ti1 join "+ BarcodeDevice.class.getSimpleName() +" b1 on b1.id=ti1.id where ti1.tousseFixedBarcode=1 and ti1.tousseDefinition_id=tdAncestor.id and rownum <= 1) fixedBarcode "; } - String querySql = "select b.barcode,tdAncestor.name definitionName,po.tousseName,po.depart,po.departCoding,po.sterilingMode,tdAncestor.isTraceable,po.isUrgentTousse,po.orgUnitCoding,po.orgUnitName,po.reviewTime,po.sterilingType,po.validUntil,po.taskGroup, po.reviewer, po.operator,po.tousseDefinition_id as tdId," + String querySql = "select b.barcode,tdAncestor.name definitionName,po.tousseName, ou.name depart,po.departCoding,po.sterilingMode,tdAncestor.isTraceable,po.isUrgentTousse,po.orgUnitCoding,po.orgUnitName,po.reviewTime,po.sterilingType,po.validUntil,po.taskGroup, po.reviewer, po.operator,po.tousseDefinition_id as tdId," + firstBarcodeSql + "from " + TousseInstance.class.getSimpleName() + " po join " + BarcodeDevice.class.getSimpleName() + " b on po.id=b.id " - + "join "+ TousseDefinition.class.getSimpleName() +" td on po.tousseDefinition_id=td.id join "+ TousseDefinition.class.getSimpleName() +" tdAncestor on td.ancestorId=tdAncestor.id " + sql; + + "join "+ TousseDefinition.class.getSimpleName() +" td on po.tousseDefinition_id=td.id join "+ TousseDefinition.class.getSimpleName() +" tdAncestor on td.ancestorId=tdAncestor.id " + + "left join " + OrgUnit.class.getSimpleName() + " ou on ou.orgUnitCoding = po.departCoding " + sql; if(StringUtils.isNumeric(start) && StringUtils.isNumeric(limit)){ String defaultOrderColumnSql = StringUtils.isBlank(orderColumnSqlByConfig) ? "barcode desc" : orderColumnSqlByConfig; querySql = querySql + " order by " + defaultOrderColumnSql; @@ -1572,12 +1578,15 @@ } else if(dbConnection.isOracle()){ queryFixedBarcodeSql = ",(select barcode from "+ TousseInstance.class.getSimpleName() +" ti1 join "+ BarcodeDevice.class.getSimpleName() +" b1 on b1.id=ti1.id where ti1.tousseFixedBarcode=1 and ti1.tousseDefinition_id=tdAncestor.id and rownum <= 1) fixedBarcode "; } + //关联查询来源科室的sql语句 + String departJoinSql = String.format(" left join %s ou on ou.orgUnitCoding = t.departCoding ", OrgUnit.class.getSimpleName()); //1、普通器械包、消毒物品、敷料包 - String sql1 = "select distinct t.id id, t.depart depart, t.departCoding departCoding, t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType," + String sql1 = "select distinct t.id id, ou.name depart, t.departCoding departCoding, t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType," + "(case when tdAncestor.isTraceable = '否' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + "t.orgUnitCoding, t.orgUnitName, t.foreignProxyItem_Id, t.proxyDisinfection_Id,t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable " + queryFixedBarcodeSql + sqlSelect_UrgentLevel + " from tousseinstance t" + sqlJoin_UrgentLevel + + departJoinSql + " , barcodedevice b , cssdHandleTousses tt , TousseDefinition td , TousseDefinition tdAncestor left join Sterilisation s on s.sterilisation = tdAncestor.sterilingMethod " + "where t.id = b.id and tt.tousseDefinitionId=td.ancestorID and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id " + "and t.reviewBasket_id =" + reviewedBasket.getId() @@ -1598,10 +1607,11 @@ JSONObject foreignTousseHandleDepartAndTaskGroupJsonobject = supplyRoomConfig.getOneForeignTousseHandleDepartAndTaskGroupByDepartCode(AcegiHelper.getCurrentOrgUnitCode()); //2、外来器械包、外来器械拆分小包 if(foreignTousseHandleDepartAndTaskGroupJsonobject != null && StringTools.equals(taskGroup, foreignTousseHandleDepartAndTaskGroupJsonobject.optString("taskGroup"))){ - String sql2 = "select t.id id, t.depart depart, t.departCoding departCoding, t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType, t.sterilingMode," + String sql2 = "select t.id id, ou.name depart, t.departCoding departCoding, t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType, t.sterilingMode," + "t.orgUnitCoding, t.orgUnitName, t.foreignProxyItem_Id, t.proxyDisinfection_Id,t.reviewTime,t.isUrgentTousse, '"+ Constants.STR_YES +"' isTraceable " + ",null fixedBarcode " + sqlSelect_UrgentLevel + " from tousseinstance t " + sqlJoin_UrgentLevel + + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGN+"','"+TousseDefinition.PACKAGE_TYPE_SPLIT+"') and t.reviewBasket_id =" + reviewedBasket.getId() @@ -1612,10 +1622,10 @@ } //3、自定义器械包 if(taskGroup.equals(supplyRoomConfig.getCustomTousseDefaultTaskGroup())){ - String sql3 = "select t.id id, t.depart depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType, t.sterilingMode," + String sql3 = "select t.id id, ou.name depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType, t.sterilingMode," + "t.orgUnitCoding, t.orgUnitName, t.foreignProxyItem_Id, t.proxyDisinfection_Id,t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable " + ",null fixedBarcode " + sqlSelect_UrgentLevel - + " from tousseinstance t" + sqlJoin_UrgentLevel + + " from tousseinstance t" + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_CUSTOM+"') and t.reviewBasket_id =" + reviewedBasket.getId() @@ -1625,11 +1635,11 @@ buildTousseSimpleVOs(vos, sql3, enableUrgentFunction); } //4、外部代理灭菌 - String sql4 = "select t.id id, t.depart depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType," + String sql4 = "select t.id id, ou.name depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType," + "(case when tdAncestor.isTraceable = '否' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + "t.orgUnitCoding, t.orgUnitName, t.foreignProxyItem_Id, t.proxyDisinfection_Id,t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable " + ",null fixedBarcode "+ sqlSelect_UrgentLevel - + "from tousseinstance t"+ sqlJoin_UrgentLevel + + "from tousseinstance t"+ sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td , TousseDefinition tdAncestor left join Sterilisation s on s.sterilisation = tdAncestor.sterilingMethod " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY+"') and t.reviewBasket_id =" + reviewedBasket.getId() @@ -1639,10 +1649,10 @@ buildTousseSimpleVOs(vos, sql4, enableUrgentFunction); }else{ //2、外来器械包、外来器械拆分小包 - String sql2 = "select t.id id, t.depart depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType, t.sterilingMode," + String sql2 = "select t.id id, ou.name depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType, t.sterilingMode," + "t.orgUnitCoding, t.orgUnitName, t.foreignProxyItem_Id, t.proxyDisinfection_Id,t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable " + ",null fixedBarcode " + sqlSelect_UrgentLevel - + "from tousseinstance t " + sqlJoin_UrgentLevel + + "from tousseinstance t " + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGN+"','"+TousseDefinition.PACKAGE_TYPE_SPLIT+"') and t.reviewBasket_id =" + reviewedBasket.getId() @@ -1651,10 +1661,10 @@ + " and t.status <> '" + TousseInstance.STATUS_DISCARD + "'"; buildTousseSimpleVOs(vos, sql2, enableUrgentFunction); //3、自定义器械包 - String sql3 = "select t.id id, t.depart depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType, t.sterilingMode," + String sql3 = "select t.id id, ou.name depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType, t.sterilingMode," + "t.orgUnitCoding, t.orgUnitName, t.foreignProxyItem_Id, t.proxyDisinfection_Id,t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable " + ",null fixedBarcode " + sqlSelect_UrgentLevel - + " from tousseinstance t " + sqlJoin_UrgentLevel + + " from tousseinstance t " + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_CUSTOM+"') and t.reviewBasket_id =" + reviewedBasket.getId() @@ -1669,11 +1679,11 @@ } else if(dbConnection.isOracle()){ firstBarcodeSql = "(select barcode from TousseInstance ti1 join barcodeDevice b1 on b1.id=ti1.id where ti1.tousseFixedBarcode=1 and ti1.tousseDefinition_id=tdAncestor.id and rownum <= 1) fixedBarcode "; } - String sql4 = "select t.id id, t.depart depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType," + String sql4 = "select t.id id, ou.name depart, t.departCoding departCoding,t.tousseName name, td.tousseType, t.tousseName, b.barcode, t.validUntil, t.sterilingType," + "(case when tdAncestor.isTraceable = '否' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + "t.orgUnitCoding, t.orgUnitName, t.foreignProxyItem_Id, t.proxyDisinfection_Id,t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable," + firstBarcodeSql+ sqlSelect_UrgentLevel - + "from tousseinstance t"+ sqlJoin_UrgentLevel + + "from tousseinstance t"+ sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td , TousseDefinition tdAncestor left join Sterilisation s on s.sterilisation = tdAncestor.sterilingMethod " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY+"') and t.reviewBasket_id =" + reviewedBasket.getId() @@ -1712,13 +1722,14 @@ //关联加急对象的sql语句 String sqlJoin_UrgentLevel = String.format(" left join %s ul on t.urgentLevel_id = ul.id ", UrgentLevel.class.getSimpleName()); String sqlSelect_UrgentLevel = String.format(" ,ul.colorCode ulColorCode ,ul.grade ulGrade, ul.name ulName , ul.id ulId "); - + //关联来源科室的sql语句 + String orgUnitJoinSql = String.format(" left join %s ou on ou.orgUnitCoding = t.departCoding ", OrgUnit.class.getSimpleName()); //1、普通器械包、消毒物品、敷料包 String sql1 = "select distinct t.reviewBasket_id,tdAncestor.id tdId," - + " t.depart depart, td.tousseType, td.includeImplant, t.tousseName, b.barcode," + + " ou.name depart, td.tousseType, td.includeImplant, t.tousseName, b.barcode," + " t.proxyDisinfection_Id,t.isUrgentTousse,tdAncestor.isTraceable,t.SterilingMode " + sqlSelect_UrgentLevel - + "from tousseinstance t" + sqlJoin_UrgentLevel + + "from tousseinstance t" + sqlJoin_UrgentLevel + orgUnitJoinSql + " , barcodedevice b , TousseDefinition td , TousseDefinition tdAncestor " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id " + " and t.reviewBasket_id "+ reviewedBasketIdsSql +" and t.reviewBasket_id is not null " @@ -1734,10 +1745,10 @@ //2、外来器械包、外来器械拆分小包 String sql2 = "select t.reviewBasket_id,td.id tdId," - + " t.depart depart, td.tousseType, td.includeImplant, t.tousseName, b.barcode," + + " ou.name depart, td.tousseType, td.includeImplant, t.tousseName, b.barcode," + " t.proxyDisinfection_Id,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable " + ",t.SterilingMode " + sqlSelect_UrgentLevel - + "from tousseinstance t"+ sqlJoin_UrgentLevel + + "from tousseinstance t"+ sqlJoin_UrgentLevel + orgUnitJoinSql + ", barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGN+"','"+TousseDefinition.PACKAGE_TYPE_SPLIT+"') " +" and t.reviewBasket_id "+ reviewedBasketIdsSql +" and t.reviewBasket_id is not null " @@ -1747,10 +1758,10 @@ buildTousseSimpleVOsForGetAllTousseInstanceVOsInReviewedBasket(vos, sql2, enableUrgentFunction); //3、自定义器械包 String sql3 = "select t.reviewBasket_id,td.id tdId," - + " t.depart depart,td.tousseType, td.includeImplant, t.tousseName, b.barcode," + + " ou.name depart,td.tousseType, td.includeImplant, t.tousseName, b.barcode," + " t.proxyDisinfection_Id,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable " + ",t.SterilingMode " + sqlSelect_UrgentLevel - + "from tousseinstance t" + sqlJoin_UrgentLevel + + "from tousseinstance t" + sqlJoin_UrgentLevel + orgUnitJoinSql + ", barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_CUSTOM+"') " +" and t.reviewBasket_id "+ reviewedBasketIdsSql +" and t.reviewBasket_id is not null " @@ -1760,10 +1771,10 @@ buildTousseSimpleVOsForGetAllTousseInstanceVOsInReviewedBasket(vos, sql3, enableUrgentFunction); //4、外部代理灭菌 String sql4 = "select t.reviewBasket_id,tdAncestor.id tdId," - + " t.depart depart, td.tousseType, td.includeImplant, t.tousseName, b.barcode," + + " ou.name depart, td.tousseType, td.includeImplant, t.tousseName, b.barcode," + " t.proxyDisinfection_Id,t.isUrgentTousse,tdAncestor.isTraceable,t.SterilingMode " + sqlSelect_UrgentLevel - + "from tousseinstance t" + sqlJoin_UrgentLevel + + "from tousseinstance t" + sqlJoin_UrgentLevel + orgUnitJoinSql + " , barcodedevice b , TousseDefinition td , TousseDefinition tdAncestor " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY+"') " +" and t.reviewBasket_id "+ reviewedBasketIdsSql +" and t.reviewBasket_id is not null " @@ -1820,12 +1831,13 @@ //关联加急对象的sql语句 String sqlJoin_UrgentLevel = String.format(" left join %s ul on t.urgentLevel_id = ul.id ", UrgentLevel.class.getSimpleName()); String sqlSelect_UrgentLevel = String.format(" ,ul.colorCode ulColorCode ,ul.grade ulGrade, ul.name ulName , ul.id ulId "); - + //关联来源科室的sql语句 + String departJoinSql = String.format(" left join %s ou on ou.orgUnitCoding = t.departCoding ", OrgUnit.class.getSimpleName()); //1、普通器械包、消毒物品、敷料包 - String sql = "select * from (select distinct t.reviewBasket_id,t.id id, t.depart depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, (case when tdAncestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + String sql = "select * from (select distinct t.reviewBasket_id,t.id id, ou.name depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, (case when tdAncestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + " t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable, tdAncestor.id tdAncestorId " + sqlSelect_UrgentLevel - + " from tousseinstance t" + sqlJoin_UrgentLevel + + " from tousseinstance t" + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , cssdHandleTousses tt , TousseDefinition td , TousseDefinition tdAncestor left join Sterilisation s on s.sterilisation = tdAncestor.sterilingMethod " + "where t.id = b.id and tt.tousseDefinitionId=td.ancestorID and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id " + "and t.reviewBasket_id " + reviewedBasketSql @@ -1845,10 +1857,10 @@ JSONObject foreignTousseHandleDepartAndTaskGroupJsonobject = supplyRoomConfig.getOneForeignTousseHandleDepartAndTaskGroupByDepartCode(AcegiHelper.getCurrentOrgUnitCode()); //2、外来器械包、外来器械拆分小包 if(foreignTousseHandleDepartAndTaskGroupJsonobject != null && StringTools.equals(taskGroup, foreignTousseHandleDepartAndTaskGroupJsonobject.optString("taskGroup"))){ - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + " t.reviewTime,t.isUrgentTousse, '"+ Constants.STR_YES +"' isTraceable, null tdAncestorId " + sqlSelect_UrgentLevel - + " from tousseinstance t " + sqlJoin_UrgentLevel + + " from tousseinstance t " + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGN+"','"+TousseDefinition.PACKAGE_TYPE_SPLIT+"') and t.reviewBasket_id " + reviewedBasketSql @@ -1859,10 +1871,10 @@ } //3、自定义器械包 if(taskGroup.equals(supplyRoomConfig.getCustomTousseDefaultTaskGroup())){ - sql += " union all select reviewBasket_id,t.id id, t.depart depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select reviewBasket_id,t.id id, ou.name depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + " t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable, null tdAncestorId " + sqlSelect_UrgentLevel - + " from tousseinstance t" + sqlJoin_UrgentLevel + + " from tousseinstance t" + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_CUSTOM+"') and t.reviewBasket_id " + reviewedBasketSql @@ -1872,10 +1884,10 @@ + sterilizerNameAndSterileFrequencySql; } //4、外部代理灭菌 - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart,t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, (case when tdAncestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart,t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, (case when tdAncestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + " t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable, tdAncestor.id tdAncestorId " + sqlSelect_UrgentLevel - + "from tousseinstance t"+ sqlJoin_UrgentLevel + + "from tousseinstance t"+ sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td , TousseDefinition tdAncestor left join Sterilisation s on s.sterilisation = tdAncestor.sterilingMethod " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY+"') and t.reviewBasket_id " + reviewedBasketSql @@ -1885,10 +1897,10 @@ + sterilizerNameAndSterileFrequencySql; }else{ //2、外来器械包、外来器械拆分小包 - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + "t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable, null tdAncestorId " + sqlSelect_UrgentLevel - + "from tousseinstance t " + sqlJoin_UrgentLevel + + "from tousseinstance t " + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGN+"','"+TousseDefinition.PACKAGE_TYPE_SPLIT+"') and t.reviewBasket_id " + reviewedBasketSql @@ -1897,10 +1909,10 @@ + sterilingModeSql + sterilizerNameAndSterileFrequencySql; //3、自定义器械包 - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart, t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + "t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable, null tdAncestorId " + sqlSelect_UrgentLevel - + " from tousseinstance t " + sqlJoin_UrgentLevel + + " from tousseinstance t " + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_CUSTOM+"') and t.reviewBasket_id " + reviewedBasketSql @@ -1909,10 +1921,10 @@ + sterilingModeSql + sterilizerNameAndSterileFrequencySql; //4、外部代理灭菌 - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart,t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, (case when tdAncestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart,t.tousseName name, td.includeImplant, b.barcode, t.sterilingType, (case when tdAncestor.isTraceable = '"+ Constants.STR_NO +"' then s.sterilizationMode else t.sterilingMode end) sterilingMode," + "t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable, tdAncestor.id tdAncestorId " + sqlSelect_UrgentLevel - + "from tousseinstance t"+ sqlJoin_UrgentLevel + + "from tousseinstance t"+ sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td , TousseDefinition tdAncestor left join Sterilisation s on s.sterilisation = tdAncestor.sterilingMethod " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY+"') and t.reviewBasket_id " + reviewedBasketSql @@ -2399,12 +2411,13 @@ if(dbConnection.isOracle()){ queryFixedBarcode = "(select barcode from TousseInstance ti1 join barcodeDevice b1 on ti1.id=b1.id where ti1.tousseFixedBarcode=1 and ti1.tousseDefinition_id=tdancestor.id and rownum <= 1) fixedBarcode"; } - String returnSql = "select ti.reviewBasket_id,ti.tousseName,ti.departCoding,ti.depart,b.barcode,td.isTraceable,"+ queryFixedBarcode +", " + String returnSql = "select ti.reviewBasket_id,ti.tousseName,ti.departCoding,ou.name depart,b.barcode,td.isTraceable,"+ queryFixedBarcode +", " + "(case when tdAncestor.isTraceable = '否' then s.sterilizationMode else ti.sterilingMode end) sterilingMode " + "from TousseInstance ti join barcodeDevice b on ti.id=b.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 " + + "left join OrgUnit ou on ou.orgUnitCoding = ti.departCoding " + "where " + SqlUtils.getStringFieldInLargeCollectionsPredicate("b.barcode", tousseInstanceBarcodeList) +" and reviewBasket_id is not null " + " and tousseFixedBarcode <> 1 "; @@ -7576,11 +7589,13 @@ sqlSelect_UrgentLevel = String.format(" ,ul.colorCode ulColorCode ,ul.grade ulGrade, ul.name ulName , ul.id ulId "); sqlJoin_UrgentLevel = String.format(" left join %s ul on ti.urgentLevel_id = ul.id ", UrgentLevel.class.getSimpleName()); } - String sql = "select * from ((select tdancestor.id,ti.depart,ti.tousseName,ti.sterilingType,ti.sterilingMode,ti.taskGroup," + //关联来源科室的join语句 + String departJoinSql = String.format(" left join %s ou on ou.orgUnitCoding = ti.departCoding ", OrgUnit.class.getSimpleName()); + String sql = "select * from ((select tdancestor.id,ou.name depart,ti.tousseName,ti.sterilingType,ti.sterilingMode,ti.taskGroup," + "IsUrgentTousse,b.barcode,ti.reviewTime,1 amount, td.includeImplant " + sqlSelect_UrgentLevel + " from "+ TousseInstance.class.getSimpleName() +" ti " - + sqlJoin_UrgentLevel + + sqlJoin_UrgentLevel + departJoinSql + " join "+ BarcodeDevice.class.getSimpleName() +" b on b.id=ti.id " + "join "+ TousseDefinition.class.getSimpleName() +" td on ti.tousseDefinition_id=td.id join "+ TousseDefinition.class.getSimpleName() +" tdancestor on " + "td.ancestorID=tdancestor.id left join "+ InvoicePlan.class.getSimpleName() +" pd on pd.id=ti.proxyDisinfection_id where tdancestor.isTraceable='"+ Constants.STR_YES +"' and ti.status='"+ TousseInstance.STATUS_REVIEWED +"' and ti.comboTousseInstanceId is null " @@ -7856,24 +7871,27 @@ SqlUtils.getNonStringFieldInLargeCollectionsPredicate("ti.wareHouseId", wareHouseIds), SqlUtils.getNonStringFieldInLargeCollectionsPredicate("ti.wareHouseId2", wareHouseIds)); } - sql = String.format("select ti.id,ti.signedDate,ti.signedUser,bd.barcode,ti.validUntil, ti.invoiceSender, ti.invoiceSendTime, ti.depart from %s ti " + sql = String.format("select ti.id,ti.signedDate,ti.signedUser,bd.barcode,ti.validUntil, ti.invoiceSender, ti.invoiceSendTime, ou.name depart from %s ti " + "inner join %s td on td.id =ti.tousseDefinition_id " + "inner join %s bd on bd.id=ti.id " + + "left join %s ou on ou.orgUnitCoding = ti.departCoding " + "where tousseFixedBarcode=0 %s %s" + "and td.name='%s' and comboTousseInstanceId is null " + "and (ti.status='%s' or ti.status='%s') ", TousseInstance.class.getSimpleName(), TousseDefinition.class.getSimpleName(), BarcodeDevice.class.getSimpleName(), + OrgUnit.class.getSimpleName(), wareHouseIdSql, tiDateSql, tousseName, TousseInstance.STATUS_DISINFECTED, TousseInstance.STATUS_STERILED); }else{ - sql = String.format("select ti.id,ti.signedDate,ti.signedUser,bd.barcode,ti.validUntil, ti.invoiceSender, ti.invoiceSendTime, ti.depart from %s ti " + sql = String.format("select ti.id,ti.signedDate,ti.signedUser,bd.barcode,ti.validUntil, ti.invoiceSender, ti.invoiceSendTime, ou.name depart from %s ti " + "inner join %s i on i.id=ti.invoice_id inner join %s td on td.id =ti.tousseDefinition_id " + "inner join %s ds on ds.tousseDefinition_id=ti.tousseDefinition_id inner join %s bd on bd.id=ti.id " + + "left join %s ou on ou.orgUnitCoding = ti.departCoding " + "where tousseFixedBarcode=0 and i.departCoding=ds.departCoding and i.departCoding='%s' " + "and ( td.isCommonTousse is null or td.isCommonTousse !=1) and comboTousseInstanceId is null " + "and ti.status='%s' and td.name='%s' %s", @@ -7882,6 +7900,7 @@ TousseDefinition.class.getSimpleName(), DepartmentStock.class.getSimpleName(), BarcodeDevice.class.getSimpleName(), + OrgUnit.class.getSimpleName(), departCode,status,tousseName, tiDateSql); } @@ -8938,12 +8957,14 @@ } else if(dbConnection.isOracle()){ queryFixedBarcodeSql = ",(select barcode from "+ TousseInstance.class.getSimpleName() +" ti1 join "+ BarcodeDevice.class.getSimpleName() +" b1 on b1.id=ti1.id where ti1.tousseFixedBarcode=1 and ti1.tousseDefinition_id=tdAncestor.id and rownum <= 1) fixedBarcode "; } + //关联来源科室的sql语句 + String departJoinSql = String.format(" left join %s ou on ou.orgUnitCoding = t.departCoding ", OrgUnit.class.getSimpleName()); //1、普通器械包、消毒物品、敷料包 - String sql = "select * from (select distinct t.reviewBasket_id,t.id id, t.depart depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + String sql = "select * from (select distinct t.reviewBasket_id,t.id id, ou.name depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + " t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable " + ", tt.taskGroup, t.operator, t.reviewer " + queryFixedBarcodeSql + sqlSelect_UrgentLevel - + " from tousseinstance t" + sqlJoin_UrgentLevel + + " from tousseinstance t" + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , cssdHandleTousses tt , TousseDefinition td , TousseDefinition tdAncestor " + "where t.id = b.id and tt.tousseDefinitionId=td.ancestorID and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id " + "and t.reviewBasket_id " + reviewedBasketSql @@ -8963,11 +8984,11 @@ JSONObject foreignTousseHandleDepartAndTaskGroupJsonobject = supplyRoomConfig.getOneForeignTousseHandleDepartAndTaskGroupByDepartCode(AcegiHelper.getCurrentOrgUnitCode()); //2、外来器械包、外来器械拆分小包 if(foreignTousseHandleDepartAndTaskGroupJsonobject != null && taskGroupNameList.contains(foreignTousseHandleDepartAndTaskGroupJsonobject.optString("taskGroup"))){ - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + " t.reviewTime,t.isUrgentTousse, '"+ Constants.STR_YES +"' isTraceable " + ", t.taskGroup, t.operator, t.reviewer" + ",null fixedBarcode " + sqlSelect_UrgentLevel - + " from tousseinstance t " + sqlJoin_UrgentLevel + + " from tousseinstance t " + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGN+"','"+TousseDefinition.PACKAGE_TYPE_SPLIT+"') and t.reviewBasket_id " + reviewedBasketSql @@ -8978,11 +8999,11 @@ } //3、自定义器械包 if(taskGroupNameList.contains(supplyRoomConfig.getCustomTousseDefaultTaskGroup())){ - sql += " union all select reviewBasket_id,t.id id, t.depart depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select reviewBasket_id,t.id id, ou.name depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + " t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable " + ", t.taskGroup, t.operator, t.reviewer" + ",null fixedBarcode " + sqlSelect_UrgentLevel - + " from tousseinstance t" + sqlJoin_UrgentLevel + + " from tousseinstance t" + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_CUSTOM+"') and t.reviewBasket_id " + reviewedBasketSql @@ -8992,11 +9013,11 @@ + (StringUtils.isNotBlank(sterilingTypeList) ? String.format(" and t.sterilingType in (%s) ", sterilingTypeList) : ""); } //4、外部代理灭菌 - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart,t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart,t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + " t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable " + ", t.taskGroup, t.operator, t.reviewer" + ",null fixedBarcode "+ sqlSelect_UrgentLevel - + "from tousseinstance t"+ sqlJoin_UrgentLevel + + "from tousseinstance t"+ sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td , TousseDefinition tdAncestor " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY+"') and t.reviewBasket_id " + reviewedBasketSql @@ -9006,11 +9027,11 @@ + (StringUtils.isNotBlank(sterilingTypeList) ? String.format(" and t.sterilingType in (%s) ", sterilingTypeList) : ""); }else{ //2、外来器械包、外来器械拆分小包 - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + "t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable " + ", t.taskGroup, t.operator, t.reviewer" + ",null fixedBarcode " + sqlSelect_UrgentLevel - + "from tousseinstance t " + sqlJoin_UrgentLevel + + "from tousseinstance t " + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGN+"','"+TousseDefinition.PACKAGE_TYPE_SPLIT+"') and t.reviewBasket_id " + reviewedBasketSql @@ -9019,11 +9040,11 @@ + " and t.status <> '" + TousseInstance.STATUS_DISCARD + "'" + (StringUtils.isNotBlank(sterilingTypeList) ? String.format(" and t.sterilingType in (%s) ", sterilingTypeList) : ""); //3、自定义器械包 - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart, t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + "t.reviewTime,t.isUrgentTousse , '"+ Constants.STR_YES +"' isTraceable " + ", t.taskGroup, t.operator, t.reviewer" + ",null fixedBarcode " + sqlSelect_UrgentLevel - + " from tousseinstance t " + sqlJoin_UrgentLevel + + " from tousseinstance t " + sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_CUSTOM+"') and t.reviewBasket_id " + reviewedBasketSql @@ -9038,11 +9059,11 @@ } else if(dbConnection.isOracle()){ firstBarcodeSql = "(select barcode from TousseInstance ti1 join barcodeDevice b1 on b1.id=ti1.id where ti1.tousseFixedBarcode=1 and ti1.tousseDefinition_id=tdAncestor.id and rownum <= 1) fixedBarcode "; } - sql += " union all select t.reviewBasket_id,t.id id, t.depart depart,t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + sql += " union all select t.reviewBasket_id,t.id id, ou.name depart,t.tousseName, td.name, td.includeImplant, b.barcode, t.sterilingType, t.sterilingMode," + "t.reviewTime,t.isUrgentTousse,tdAncestor.isTraceable," + " t.taskGroup, t.operator, t.reviewer," + firstBarcodeSql+ sqlSelect_UrgentLevel - + "from tousseinstance t"+ sqlJoin_UrgentLevel + + "from tousseinstance t"+ sqlJoin_UrgentLevel + departJoinSql + " , barcodedevice b , TousseDefinition td , TousseDefinition tdAncestor " + "where t.id = b.id and t.tousseDefinition_id=td.id and td.ancestorID=tdAncestor.id and td.tousseType in ('"+TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY+"') and t.reviewBasket_id " + reviewedBasketSql @@ -9515,27 +9536,30 @@ SqlUtils.getNonStringFieldInLargeCollectionsPredicate("ti.wareHouseId", wareHouseIds), SqlUtils.getNonStringFieldInLargeCollectionsPredicate("ti.wareHouseId2", wareHouseIds)); } - sql = String.format("select ti.id,ti.signedDate,ti.signedUser,bd.barcode,ti.validUntil, ti.invoiceSender, ti.invoiceSendTime, ti.depart " + sql = String.format("select ti.id,ti.signedDate,ti.signedUser,bd.barcode,ti.validUntil, ti.invoiceSender, ti.invoiceSendTime, ou.name depart " + "from %s ti " + "inner join %s td on td.id =ti.tousseDefinition_id " + "inner join %s bd on bd.id=ti.id " + + "left join %s ou on ou.orgUnitCoding = ti.departCoding " + "where tousseFixedBarcode=0 %s " + "and td.name='%s' " //+ "and comboTousseInstanceId is null " + "and (ti.status='%s' or ti.status='%s' or ti.status='%s') ", TousseInstance.class.getSimpleName(), TousseDefinition.class.getSimpleName(), BarcodeDevice.class.getSimpleName(), + OrgUnit.class.getSimpleName(), wareHouseIdSql, tousseName, TousseInstance.STATUS_STERILED, TousseInstance.STATUS_DISINFECTED, TousseInstance.STATUS_SHIPPED); }else{ - sql = String.format("select ti.id,ti.signedDate,ti.signedUser,bd.barcode,ti.validUntil, ti.invoiceSender, ti.invoiceSendTime, ti.depart " + sql = String.format("select ti.id,ti.signedDate,ti.signedUser,bd.barcode,ti.validUntil, ti.invoiceSender, ti.invoiceSendTime, ou.name depart " + "from %s ti " + "inner join %s i on i.id = ti.invoice_id " + "inner join %s td on td.id =ti.tousseDefinition_id " + "inner join %s ancestor on ancestor.id = td.ancestorID " + "inner join %s gs on gs.tousseDefinitionId = ancestor.id " + "inner join %s bd on bd.id=ti.id " + + "left join %s ou on ou.orgUnitCoding = ti.departCoding " + "where tousseFixedBarcode=0 and i.departCoding=gs.orgUnitCode and i.departCoding='%s' " + "and ( td.isCommonTousse is null or td.isCommonTousse !=1) " //+ "and comboTousseInstanceId is null " @@ -9546,6 +9570,7 @@ TousseDefinition.class.getSimpleName(), GoodsStock.class.getSimpleName(), BarcodeDevice.class.getSimpleName(), + OrgUnit.class.getSimpleName(), departCode, tousseName, TousseInstance.STATUS_SHIPPED, TousseInstance.STATUS_SIGNED); } ResultSet rs = null;