Index: ssts-recyclingrecord/src/main/java/com/forgon/disinfectsystem/recyclingrecord/service/RecyclingRecordListSqlGenerator.java =================================================================== diff -u -r38176 -r38218 --- ssts-recyclingrecord/src/main/java/com/forgon/disinfectsystem/recyclingrecord/service/RecyclingRecordListSqlGenerator.java (.../RecyclingRecordListSqlGenerator.java) (revision 38176) +++ ssts-recyclingrecord/src/main/java/com/forgon/disinfectsystem/recyclingrecord/service/RecyclingRecordListSqlGenerator.java (.../RecyclingRecordListSqlGenerator.java) (revision 38218) @@ -50,10 +50,23 @@ private ObjectDao objectDao; private Integer pageSize; private Set extraQuerys = null; + /** + * 使用union all 的方式进行过滤条码比较效率 + * 如果回收物品上有记条码则显示此回收物品所在的回收记录（也就是扫码回收的回收记录）,否则显示包含此条码的申请单关联的回收记录 + * 回收物品所在的回收记录（也就是扫码回收的回收记录)的条码过滤 + */ + private String barcodeInRecyclingRecordSql = ""; + /** + * 包含此条码的申请单关联的回收记录 + */ + private String barcodeInInvoicePlan = ""; + private String orgUnitCodingFromSupplyRoomConfig; public RecyclingRecordListSqlGenerator(boolean enableUrgentFunction, InitDbConnection dbConnection,long start,long end,String filterJsonStr, String extraQuery, boolean groupByInvoicePlanId,ObjectDao objectDao, Integer pageSize, Set extraQuerys) { this.pageSize = pageSize; this.objectDao = objectDao; this.dbConnection = dbConnection; + this.orgUnitCodingFromSupplyRoomConfig = AcegiHelper.getLoginUser() + .getOrgUnitCodingFromSupplyRoomConfig(); this.dateQueryAdapter = new DateQueryAdapter(); this.dateQueryAdapter.setDbConnection(dbConnection); this.filterJsonStr = filterJsonStr; @@ -84,6 +97,30 @@ String dateRangeSql = buildDateRangeSql(dateRange); String idCardBarcodeStr = JSONUtil.optString(jsonObj, "idCardBarcode", null); String tousseInstanceBarcodeStr = JSONUtil.optString(jsonObj, "tousseInstanceBarcode", null); + if(StringUtils.isNotBlank(tousseInstanceBarcodeStr)){ + StringBuffer sbf = new StringBuffer(); + sbf.append(" and "+ recyclingRecordPoName +".id in (select recyclingRecordId from "); + sbf.append(ClassifiedItem.class.getSimpleName()); + sbf.append(" ci join "); + sbf.append(BarcodeDevice.class.getSimpleName()); + sbf.append(" bd on bd.id=ci.lastTousseInstanceId where bd.barcode='"); + sbf.append(tousseInstanceBarcodeStr); + sbf.append("') "); + this.barcodeInRecyclingRecordSql = sbf.toString(); + sbf.setLength(0); + sbf.append(" and "); + sbf.append(invoicePlanPoName); + sbf.append(".tousseBarcodes like '%"); + sbf.append(tousseInstanceBarcodeStr); + sbf.append("%' and not exists (select recyclingRecordId from "); + sbf.append(ClassifiedItem.class.getSimpleName()); + sbf.append(" ci join "); + sbf.append(BarcodeDevice.class.getSimpleName()); + sbf.append(" bd on bd.id=ci.lastTousseInstanceId where bd.barcode='"); + sbf.append(tousseInstanceBarcodeStr); + sbf.append("') "); + this.barcodeInInvoicePlan = sbf.toString(); + } String packingSerialNum = JSONUtil.optString(jsonObj, "packingSerialNum", null); String operationRoomStr = JSONUtil.optString(jsonObj, "operationRoom", null); String groupId = JSONUtil.optString(jsonObj, "groupId", null); @@ -94,7 +131,7 @@ map.put(recyclingRecordPoName + ".status", status); map.put(invoicePlanPoName + ".type", appType); return buildRecyclingRecordFilterSql(map) + " and " + dateRangeSql + " and " + buildIdCardBarcodeSql(idCardBarcodeStr) - + " and " + bulidTousseInstanceBarcodeSql(tousseInstanceBarcodeStr) + " and " + buildPackingSerialNumSql(packingSerialNum) + SqlUtils.getWhereSqlByfilterFieldAndStringValue(invoicePlanPoName + ".operationRoom", operationRoomStr) + + " and " + buildPackingSerialNumSql(packingSerialNum) + SqlUtils.getWhereSqlByfilterFieldAndStringValue(invoicePlanPoName + ".operationRoom", operationRoomStr) + " and " + buildGroupIdSql(groupId) + buildOtherSql(jsonObj) + buildInstrumentSetTypeIdsSql(jsonObj); } /** @@ -192,37 +229,6 @@ return " (1=1) "; } } - /** - * 包条码过滤 - * 如果回收物品上有记条码则显示此回收物品所在的回收记录（也就是扫码回收的回收记录）,否则显示包含此条码的申请单关联的回收记录 - * @param tousseInstanceBarcode - * @return - */ - private String bulidTousseInstanceBarcodeSql(String tousseInstanceBarcode){ - if(StringUtils.isNotBlank(tousseInstanceBarcode)){ - StringBuffer sbf = new StringBuffer(); - sbf.append(" ("+ recyclingRecordPoName +".id in (select recyclingRecordId from "); - sbf.append(ClassifiedItem.class.getSimpleName()); - sbf.append(" ci join "); - sbf.append(BarcodeDevice.class.getSimpleName()); - sbf.append(" bd on bd.id=ci.lastTousseInstanceId where bd.barcode='"); - sbf.append(tousseInstanceBarcode); - sbf.append("') or "); - sbf.append(invoicePlanPoName); - sbf.append(".tousseBarcodes like '%"); - sbf.append(tousseInstanceBarcode); - sbf.append("%' and not exists (select recyclingRecordId from "); - sbf.append(ClassifiedItem.class.getSimpleName()); - sbf.append(" ci join "); - sbf.append(BarcodeDevice.class.getSimpleName()); - sbf.append(" bd on bd.id=ci.lastTousseInstanceId where bd.barcode='"); - sbf.append(tousseInstanceBarcode); - sbf.append("')) "); - return sbf.toString(); - }else{ - return " (1=1) "; - } - } private String buildDateRangeSql(JSONObject dateRange){ if(dateRange != null){ @@ -252,50 +258,130 @@ public String getListSql() { String sql = ""; - - String orgUnitCodingFromSupplyRoomConfig = AcegiHelper.getLoginUser() - .getOrgUnitCodingFromSupplyRoomConfig(); String joinSqlOfUrgent = ""; if(this.enableUrgentFunction || extraQuerys != null && extraQuerys.contains("包数")){ joinSqlOfUrgent = " left join UrgentLevel ul on p.urgentLevel_id=ul.id "; } String recyclingAmountNeedConfirmOrder = ""; - if(CssdUtils.getSystemSetConfigByNameBool("confirmRecyclingAmount",false)){ + boolean confirmRecyclingAmount = CssdUtils.getSystemSetConfigByNameBool("confirmRecyclingAmount",false); + if(confirmRecyclingAmount){ recyclingAmountNeedConfirmOrder = "case r.recycleAmountNeedConfirm when '" + Constants.STR_YES + "' then 0 else 1 end,"; } - String tousseMaterialsLostMustConfirmOrder = CssdUtils.getSystemSetConfigByNameBool("tousseMaterialsLostMustConfirm", true)? + boolean tousseMaterialsLostMustConfirm = CssdUtils.getSystemSetConfigByNameBool("tousseMaterialsLostMustConfirm", true); + String tousseMaterialsLostMustConfirmOrder = tousseMaterialsLostMustConfirm? "case when r.status='"+ RecyclingRecord.STATUS_UNCONFIRMED +"' then 0 else 1 end,":""; if (dbConnection.isSqlServer()) { - sql = " SELECT * FROM (SELECT " - + " r.*,p.applicationTime, p.type,p.remark,p.submitTime,p.recyclingStatus,p.specialInfection,p.operationRoom,p.patient,p.hospitalNumber,p.serialNumber,p.applicant "+ extraQuery +" " - + ",ROW_NUMBER() OVER (order by " +tousseMaterialsLostMustConfirmOrder +recyclingAmountNeedConfirmOrder+" r.recyclingTime desc,r.id desc) AS RowNum " - + "FROM " + RecyclingRecord.class.getSimpleName() + " r left join " + InvoicePlan.class.getSimpleName() - + " p on r.recyclingApplication_id = p.id " + joinSqlOfUrgent - + "where r.orgUnitCoding = '" - + orgUnitCodingFromSupplyRoomConfig - + "' and " - + filterSql - + ") AS RowConstrainedResult WHERE RowNum BETWEEN ("+ this.end +" - " + this.pageSize +") + 1 AND "+ this.end + " order by RowNum asc " ; + if(StringUtils.isNotBlank(barcodeInRecyclingRecordSql)){ + sql = " SELECT * FROM (SELECT * " + + ",ROW_NUMBER() OVER (order by " + +(tousseMaterialsLostMustConfirm?"case when t3.status='"+ RecyclingRecord.STATUS_UNCONFIRMED +"' then 0 else 1 end,":"") + +(confirmRecyclingAmount?"case t3.recycleAmountNeedConfirm when '" + Constants.STR_YES + "' then 0 else 1 end,":"") + +" t3.recyclingTime desc,t3.id desc) AS RowNum " + + "from (" + + "SELECT " + + " r.*,p.applicationTime, p.type,p.remark,p.submitTime,p.recyclingStatus,p.specialInfection,p.operationRoom,p.patient,p.hospitalNumber,p.serialNumber,p.applicant "+ extraQuery +" " + + "FROM " + RecyclingRecord.class.getSimpleName() + " r left join " + InvoicePlan.class.getSimpleName() + + " p on r.recyclingApplication_id = p.id " + joinSqlOfUrgent + + "where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql + + barcodeInRecyclingRecordSql + + " union all " + + "SELECT " + + " r.*,p.applicationTime, p.type,p.remark,p.submitTime,p.recyclingStatus,p.specialInfection,p.operationRoom,p.patient,p.hospitalNumber,p.serialNumber,p.applicant "+ extraQuery +" " + + "FROM " + RecyclingRecord.class.getSimpleName() + " r left join " + InvoicePlan.class.getSimpleName() + + " p on r.recyclingApplication_id = p.id " + joinSqlOfUrgent + + "where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql + + barcodeInInvoicePlan + + " ) t3 ) AS RowConstrainedResult WHERE RowNum BETWEEN ("+ this.end +" - " + this.pageSize +") + 1 AND "+ this.end + " order by RowNum asc " ; + }else{ + sql = " SELECT * FROM (SELECT " + + " r.*,p.applicationTime, p.type,p.remark,p.submitTime,p.recyclingStatus,p.specialInfection,p.operationRoom,p.patient,p.hospitalNumber,p.serialNumber,p.applicant "+ extraQuery +" " + + ",ROW_NUMBER() OVER (order by " +tousseMaterialsLostMustConfirmOrder +recyclingAmountNeedConfirmOrder+" r.recyclingTime desc,r.id desc) AS RowNum " + + "FROM " + RecyclingRecord.class.getSimpleName() + " r left join " + InvoicePlan.class.getSimpleName() + + " p on r.recyclingApplication_id = p.id " + joinSqlOfUrgent + + "where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql + + ") AS RowConstrainedResult WHERE RowNum BETWEEN ("+ this.end +" - " + this.pageSize +") + 1 AND "+ this.end + " order by RowNum asc " ; + } + } else if (dbConnection.isOracle()) { - sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT r.*,p.applicationTime,p.type,p.remark,p.submitTime,p.recyclingStatus,p.specialInfection,p.operationRoom,p.patient,p.hospitalNumber,p.serialNumber "+ extraQuery +" FROM " + RecyclingRecord.class.getSimpleName() - + " r left join " + InvoicePlan.class.getSimpleName() + " p on r.recyclingApplication_id = p.id " + joinSqlOfUrgent - + "where r.orgUnitCoding = '" - + orgUnitCodingFromSupplyRoomConfig - + "' and " - + filterSql - + " order by " + tousseMaterialsLostMustConfirmOrder + recyclingAmountNeedConfirmOrder+" r.recyclingTime desc) A WHERE ROWNUM <= " - + end - + ")WHERE RN >= " + start + " order by RN asc "; + if(StringUtils.isNotBlank(barcodeInRecyclingRecordSql)){ + sql = " SELECT * FROM (SELECT * from (SELECT ROWNUM RN,t3.* from (" + + "SELECT r.*,p.applicationTime,p.type,p.remark,p.submitTime,p.recyclingStatus,p.specialInfection,p.operationRoom,p.patient,p.hospitalNumber,p.serialNumber "+ extraQuery +" FROM " + RecyclingRecord.class.getSimpleName() + + " r left join " + InvoicePlan.class.getSimpleName() + " p on r.recyclingApplication_id = p.id " + joinSqlOfUrgent + + "where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql + + barcodeInRecyclingRecordSql + + " union all " + + "SELECT r.*,p.applicationTime,p.type,p.remark,p.submitTime,p.recyclingStatus,p.specialInfection,p.operationRoom,p.patient,p.hospitalNumber,p.serialNumber "+ extraQuery +" FROM " + RecyclingRecord.class.getSimpleName() + + " r left join " + InvoicePlan.class.getSimpleName() + " p on r.recyclingApplication_id = p.id " + joinSqlOfUrgent + + "where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql + + barcodeInInvoicePlan + + ") t3 order by " + +(tousseMaterialsLostMustConfirm?"case when t3.status='"+ RecyclingRecord.STATUS_UNCONFIRMED +"' then 0 else 1 end,":"") + +(confirmRecyclingAmount?"case t3.recycleAmountNeedConfirm when '" + Constants.STR_YES + "' then 0 else 1 end,":"") + +" t3.recyclingTime desc" + + ") A WHERE ROWNUM <= " + + end + + ")WHERE RN >= " + start + " order by RN asc "; + }else{ + sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT r.*,p.applicationTime,p.type,p.remark,p.submitTime,p.recyclingStatus,p.specialInfection,p.operationRoom,p.patient,p.hospitalNumber,p.serialNumber "+ extraQuery +" FROM " + RecyclingRecord.class.getSimpleName() + + " r left join " + InvoicePlan.class.getSimpleName() + " p on r.recyclingApplication_id = p.id " + joinSqlOfUrgent + + "where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql + + " order by " + tousseMaterialsLostMustConfirmOrder + recyclingAmountNeedConfirmOrder+" r.recyclingTime desc) A WHERE ROWNUM <= " + + end + + ")WHERE RN >= " + start + " order by RN asc "; + } + } return sql; } public String getCountSql(){ - String countSql = "select count(*) from RecyclingRecord r left join InvoicePlan p on r.recyclingApplication_id = p.id" - + " where r.orgUnitCoding = '" - + AcegiHelper.getLoginUser().getOrgUnitCodingFromSupplyRoomConfig() - + "' and " - + filterSql; + String countSql = null; + if(StringUtils.isNotBlank(barcodeInRecyclingRecordSql)){ + countSql = " SELECT count(*) " + + "from (" + + "SELECT r.id " + + "FROM " + RecyclingRecord.class.getSimpleName() + " r left join " + InvoicePlan.class.getSimpleName() + + " p on r.recyclingApplication_id = p.id " + + "where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql + + barcodeInRecyclingRecordSql + + " union all " + + "SELECT r.id " + + "FROM " + RecyclingRecord.class.getSimpleName() + " r left join " + InvoicePlan.class.getSimpleName() + + " p on r.recyclingApplication_id = p.id " + + "where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql + + barcodeInInvoicePlan + + " ) t3 "; + }else{ + countSql = "select count(*) from RecyclingRecord r left join InvoicePlan p on r.recyclingApplication_id = p.id" + + " where r.orgUnitCoding = '" + + orgUnitCodingFromSupplyRoomConfig + + "' and " + + filterSql; + } return countSql; } }