Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/packaging/PackagingRecord.java =================================================================== diff -u -r33957 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/packaging/PackagingRecord.java (.../PackagingRecord.java) (revision 33957) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/packaging/PackagingRecord.java (.../PackagingRecord.java) (revision 37373) @@ -5,6 +5,8 @@ import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; +import javax.persistence.Index; +import javax.persistence.Table; import org.hibernate.annotations.Cache; import org.hibernate.annotations.CacheConcurrencyStrategy; @@ -18,6 +20,8 @@ @Entity @DynamicInsert(false) @DynamicUpdate(true) +@Table(name = "PackagingRecord",indexes = { +@Index(columnList = "orgUnitCoding,entryDate,tousseInstance_Id,wrapper", name = "pr_ouc_ed_ti_wrapper")}) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) public class PackagingRecord { private Long id; @@ -31,6 +35,14 @@ * 供应室 */ private String orgUnitCoding; + /** + * 包装人 + */ + private String wrapper; + /** + * 包装人编码 + */ + private String wrapperCode; @Id @GeneratedValue(strategy = GenerationType.AUTO) public Long getId() { @@ -64,5 +76,21 @@ public void setOrgUnitCoding(String orgUnitCoding) { this.orgUnitCoding = orgUnitCoding; } + + public String getWrapper() { + return wrapper; + } + + public void setWrapper(String wrapper) { + this.wrapper = wrapper; + } + + public String getWrapperCode() { + return wrapperCode; + } + + public void setWrapperCode(String wrapperCode) { + this.wrapperCode = wrapperCode; + } } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/StatisticalWorkloadHelper.java =================================================================== diff -u -r37260 -r37373 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/StatisticalWorkloadHelper.java (.../StatisticalWorkloadHelper.java) (revision 37260) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/StatisticalWorkloadHelper.java (.../StatisticalWorkloadHelper.java) (revision 37373) @@ -133,7 +133,7 @@ if(StringUtils.isNotBlank(instrumentSetTypeIds)){ tousseWhereSql += reportSqlUtil.getInstrumentSetTypeSql(SqlUtils.splitStringToSet(instrumentSetTypeIds, ";",false)); } - ReportQueryParams params = new ReportQueryParams(objectDao); + ReportQueryParams params = new ReportQueryParams(); params.betweenSql = betweenSql; params.querySupplyRoom = querySupplyRoom; params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; @@ -516,11 +516,14 @@ StringBuilder sbr = new StringBuilder(); sbr.append("select sum(ii.amount) amount,sender from ") .append(MaterialInvoiceItem.class.getSimpleName()) + .append(dbConnection.getNoLockSql()) .append(" ii inner join ") .append(MaterialInvoice.class.getSimpleName()) + .append(dbConnection.getNoLockSql()) .append(" i on i.id = ii.materialInvoice_id ") .append("inner join ") .append(MaterialDefinition.class.getSimpleName()) + .append(dbConnection.getNoLockSql()) .append(" d on ii.materialDefinitionId = d.id where i.sendtime ") .append(params.betweenSql) .append(SqlUtils.get_InSql_Extra("i.orgUnitCoding", params.querySupplyRoom)) Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/InvoicePlan.java =================================================================== diff -u -r36614 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/InvoicePlan.java (.../InvoicePlan.java) (revision 36614) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/InvoicePlan.java (.../InvoicePlan.java) (revision 37373) @@ -63,6 +63,7 @@ ,@Index(columnList="includeInvoiceItems",name="includeInvoiceItems_index"),@Index(columnList="urgentLevel_id",name="ip_urgentLevel_id_index") ,@Index(columnList="printTime",name="ip_printTime_index") ,@Index(columnList="settleAccountsDepartCoding",name="ip_settAccDeptCode_index") +,@Index(columnList="prepareRecycleOperator,prepareRecycleDateTime,handleDepartCoding,id",name="ip_pro_prd_hdc_id") }) @Inheritance(strategy = InheritanceType.JOINED) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/recyclingrecord/RecyclingRecord.java =================================================================== diff -u -r32229 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/recyclingrecord/RecyclingRecord.java (.../RecyclingRecord.java) (revision 32229) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/recyclingrecord/RecyclingRecord.java (.../RecyclingRecord.java) (revision 37373) @@ -44,7 +44,9 @@ ,@Index(columnList = "recyclingApplication_id", name = "recyclingApplication_id_index") ,@Index(columnList = "recyclingTime", name = "recyclingTime_index") ,@Index(columnList = "recyclingUser", name = "recyclingUser_index") -,@Index(columnList = "operator", name = "operator_index")}) +,@Index(columnList = "operator", name = "operator_index") +,@Index(columnList = "orgUnitCoding,recyclingTime,id,recyclingUser", name = "rr_ouc_rt_id_ru") +,@Index(columnList = "recyclingTime,id,orgUnitCoding,operator", name = "rr_rt_id_ouc_operator")}) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) public class RecyclingRecord { Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r37314 -r37373 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 37314) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 37373) @@ -151,16 +151,22 @@ * 查询器械清洗数量的sql，统计itemType是材料的类型，并且ClassifyItem的包定义不为空，即器械有所属的包，属于拆包清洗的器械 * @return */ - public static String getWashMaterialAmountSqlFromItemTypeIsMaterial(String extraJoinCondition){ + public static String getWashMaterialAmountSqlFromItemTypeIsMaterial(String extraJoinCondition, String noLockSql){ String sql = String - .format("from WashAndDisinfectRecord wr inner join ClassifyBasket_WashRecord cw on cw.WashAndDisinfectRecord_ID = wr.id " - + "inner join ClassifyBasket cb on cb.id = cw.ClassifyBasket_ID inner join ClassifiedItem ci on ci.classifybasket_id = cb.id " - + "inner join TousseDefinition td on td.id=ci.tousseDefinitionID join TousseDefinition tdc on tdc.id=td.ancestorID %s " + .format("from WashAndDisinfectRecord"+ noLockSql +" wr inner join ClassifyBasket_WashRecord"+ noLockSql +" cw on cw.WashAndDisinfectRecord_ID = wr.id " + + "inner join ClassifyBasket"+ noLockSql +" cb on cb.id = cw.ClassifyBasket_ID inner join ClassifiedItem"+ noLockSql +" ci on ci.classifybasket_id = cb.id " + + "inner join TousseDefinition"+ noLockSql +" td on td.id=ci.tousseDefinitionID join TousseDefinition"+ noLockSql +" tdc on tdc.id=td.ancestorID %s " + "where ci.itemType = '材料' ", extraJoinCondition); return sql; } - /** + * 查询器械清洗数量的sql，统计itemType是材料的类型，并且ClassifyItem的包定义不为空，即器械有所属的包，属于拆包清洗的器械 + * @return + */ + public static String getWashMaterialAmountSqlFromItemTypeIsMaterial(String extraJoinCondition){ + return getWashMaterialAmountSqlFromItemTypeIsMaterial(extraJoinCondition, ""); + } + /** * 查询器械清洗数量的sql，统计itemType是材料的类型，并且ClassifyItem的包定义为空，即单独添加进清洗篮筐的器械， * 例如在包装补充器械时，将器械放入了清洗篮筐，然后登记清洗记录 * @return @@ -231,11 +237,11 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "ri.amount * mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on td.id = mi.tousse_id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+dbConnection.getNoLockSql()+" mi on td.id = mi.tousse_id "; } amountSql = String .format("select sum(%s) amount ,rr.recyclingUser userName " - + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from RecyclingRecord"+dbConnection.getNoLockSql()+" rr inner join RecyclingItem"+dbConnection.getNoLockSql()+" ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition"+dbConnection.getNoLockSql()+" td on ri.tousseDefinitionId = td.id join TousseDefinition"+dbConnection.getNoLockSql()+" tdc on tdc.id=td.ancestorID " + " %s where rr.recyclingTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "rr.recyclingUser") + " %s %s %s %s " @@ -260,7 +266,7 @@ } amountSql = String .format("select sum(%s) amount ,ip.prepareRecycleOperator userName " - + "from invoicePlan ip inner join TousseItem ti on ti.recyclingApplication_ID =ip.id inner join TousseDefinition td on td.id = ti.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from invoicePlan"+ dbConnection.getNoLockSql() +" ip inner join TousseItem"+ dbConnection.getNoLockSql() +" ti on ti.recyclingApplication_ID =ip.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id = ti.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where ip.prepareRecycleDateTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "ip.prepareRecycleOperator") + " %s %s %s %s " @@ -314,7 +320,7 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "ri.amount * mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on td.id = mi.tousse_id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+dbConnection.getNoLockSql()+" mi on td.id = mi.tousse_id "; } if(obj.isGroup){ groupBySql = String.format("%s %s %s %s", @@ -332,7 +338,7 @@ } amountSql = String .format("select sum(%s) amount %s %s %s %s " - + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from RecyclingRecord"+dbConnection.getNoLockSql()+" rr inner join RecyclingItem"+dbConnection.getNoLockSql()+" ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition"+dbConnection.getNoLockSql()+" td on ri.tousseDefinitionId = td.id join TousseDefinition"+dbConnection.getNoLockSql()+" tdc on tdc.id=td.ancestorID " + " %s where rr.recyclingTime %s %s %s and %s %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "rr.operator") + " %s %s %s %s %s %s ", @@ -371,7 +377,7 @@ // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 amountSql += String .format("select %s %s %s %s %s %s %s " - + getWashMaterialAmountSqlFromItemTypeIsMaterial(obj.extraJoinCondition) + + getWashMaterialAmountSqlFromItemTypeIsMaterial(obj.extraJoinCondition,dbConnection.getNoLockSql()) + " and wr.endDate %s %s %s and %s %s %s and wr.washMaterialAmount <> 0 %s %s %s %s %s " + "%s %s ", getDatePeriodSelectSql(obj.monthlyStr), @@ -624,7 +630,7 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on td.id = mi.tousse_id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on td.id = mi.tousse_id "; } if(obj.isGroup){ groupBySql = String.format("%s %s %s %s", @@ -643,7 +649,7 @@ if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select sum(%s) amount %s %s %s " - + "%s from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "%s from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where %s and ti.operationTime %s %s %s and %s %s %s " + "%s %s %s %s %s %s", amountColumnSql, @@ -675,7 +681,7 @@ } amountSql += String .format("select sum(%s) amount %s %s %s " - + "%s from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "%s from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseDefinitionId is not null and ti.comboTousseInstanceId is not null and ti.operationTime %s %s %s and %s %s %s " + "%s %s %s %s %s ", amountColumnSql, @@ -711,11 +717,11 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on td.id = mi.tousse_id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on td.id = mi.tousse_id "; } if(obj.isGroup){ groupBySql = String.format("%s %s %s %s", - obj.selectUserName?",ti.wrapper":"", + obj.selectUserName?",po.wrapper":"", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name" : "", obj.extraGroupBy); @@ -727,63 +733,29 @@ }else{ groupBySql = ""; } - if(!obj.getIsOnlyQueryComboTousse()){ - amountSql = String - .format("select sum(%s) amount %s %s %s " - + "%s from PackagingRecord po join TousseInstance ti on ti.id=po.tousseInstance_id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + " %s where %s and po.entryDate %s %s %s and %s %s %s " - + "%s %s %s %s %s %s", - amountColumnSql, - obj.selectUserName?",wrapper userName":"", - obj.selectTousseType ? ",td.tousseType" : "", - obj.selectTousseName ? ",td.name tousseName" : "", - obj.extraSelectColumns, - joinMaterialInstanceSql, - obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", - obj.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), - SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.depart", obj.recyDepartGroup, " 1=1 "), - obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd, - obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql, - obj.extraQuery, - groupBySql); - } - if(obj.getIsQueryCom()){ - if(!obj.getIsOnlyQueryComboTousse()){ - amountSql += " union all "; - } - if(groupBySql.contains("td.name")){ - groupBySql = groupBySql.replace("td.name", "ti.comboTousseDefinitionName"); - } - amountSql += String - .format("select sum(%s) amount %s %s %s " - + "%s from PackagingRecord po join TousseInstance ti on ti.combotousseinstanceid=po.tousseinstance_id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " - + " %s where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseDefinitionId is not null and ti.comboTousseInstanceId is not null " - + " and po.entryDate %s %s %s and %s %s %s " - + "%s %s %s %s %s ", - amountColumnSql, - obj.selectUserName?",wrapper userName":"", - obj.selectTousseType ? ",'"+TousseDefinition.PACKAGE_TYPE_COMBO + "' tousseType" : "", - obj.selectTousseName ? ",ti.comboTousseDefinitionName tousseName" : "", - obj.extraSelectColumns, - joinMaterialInstanceSql, - obj.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), - SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.depart", obj.recyDepartGroup, " 1=1 "), - obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd, - obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("tdc", "ti"), - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.extraQuery, - groupBySql); - } + amountSql = String + .format("select sum(%s) amount %s %s %s " + + "%s from PackagingRecord"+ dbConnection.getNoLockSql() +" po join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.id=po.tousseInstance_id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + + " %s where po.entryDate %s %s %s and %s %s %s " + + "%s %s %s %s %s %s", + amountColumnSql, + obj.selectUserName?",po.wrapper userName":"", + obj.selectTousseType ? ",td.tousseType" : "", + obj.selectTousseName ? ",td.name tousseName" : "", + obj.extraSelectColumns, + joinMaterialInstanceSql, + obj.betweenSql, + SqlUtils.get_InSql_Extra("po.orgUnitCoding", obj.querySupplyRoom), + SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), + SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.depart", obj.recyDepartGroup, " 1=1 "), + obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd, + obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, + groupBySql); break; case "年度报表中的配包数量（按材料）": @@ -852,14 +824,14 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; - joinMaterialInstanceSql = "inner join MaterialInstance mi on td.id = mi.tousse_id"; + joinMaterialInstanceSql = "inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on td.id = mi.tousse_id"; } if(!obj.getIsOnlyQueryComboTousse()){ //非拆分包 amountSql = String .format("select sum(%s) amount ,pr.inspector userName " - + "from TousseInstance ti inner join PackingRecord pr on pr.id = ti.packingRecord_id inner join TousseDefinition td on ti.tousseDefinition_id=td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join PackingRecord"+ dbConnection.getNoLockSql() +" pr on pr.id = ti.packingRecord_id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id=td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where pr.packTime %s %s " + " and pr.splitTousseName is null" + SqlUtils.getIsNotNullSql(dbConnection, "pr.inspector") @@ -877,8 +849,8 @@ //拆分小包 amountSql += String .format(" union all select sum(%s) amount ,pr.inspector userName" - + " from PackingRecord pr inner join TousseDefinition td on pr.tousseDefinitionId = td.parentID " - + "inner join TousseInstance ti on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + " from PackingRecord"+ dbConnection.getNoLockSql() +" pr inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on pr.tousseDefinitionId = td.parentID " + + "inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where pr.packTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "pr.inspector") + " and td.tousseType='%s' %s %s %s %s " @@ -900,10 +872,10 @@ } amountSql += String .format(" select sum(%s) amount ,pr.inspector userName " - + "from TousseDefinition td join TousseDefinition tdc on tdc.id=td.ancestorID " - + "inner join TousseInstance ti on td.id = ti.tousseDefinition_id " - + "inner join TousseInstance tic on tic.id = ti.comboTousseInstanceId " - + "inner join PackingRecord pr on tic.tousseDefinition_id=pr.tousseDefinitionId and tic.packingRecord_id=pr.id " + + "from TousseDefinition"+ dbConnection.getNoLockSql() +" td join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + + "inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on td.id = ti.tousseDefinition_id " + + "inner join TousseInstance"+ dbConnection.getNoLockSql() +" tic on tic.id = ti.comboTousseInstanceId " + + "inner join PackingRecord"+ dbConnection.getNoLockSql() +" pr on tic.tousseDefinition_id=pr.tousseDefinitionId and tic.packingRecord_id=pr.id " + " %s where pr.packTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "pr.inspector") + " %s %s %s " @@ -929,11 +901,11 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on td.id = mi.tousse_id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on td.id = mi.tousse_id "; } amountSql = String .format("select sum(%s) amount ,pr.wrapper userName " - + "from PackingRecord pr inner join TousseInstance ti on ti.packingRecord_id=pr.id left join PackagingRecord prd on prd.tousseInstance_id=ti.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from PackingRecord"+ dbConnection.getNoLockSql() +" pr inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.packingRecord_id=pr.id left join PackagingRecord"+ dbConnection.getNoLockSql() +" prd on prd.tousseInstance_id=ti.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition tdc"+ dbConnection.getNoLockSql() +" on tdc.id=td.ancestorID " + " %s where prd.id is null and pr.packTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "pr.wrapper") + " %s %s %s %s " @@ -973,12 +945,12 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on td.id = mi.tousse_id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on td.id = mi.tousse_id "; } if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select sum(%s) amount ,ti.reviewer userName " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where %s and ti.reviewTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "ti.reviewer") + " %s %s %s %s " @@ -1000,7 +972,7 @@ } amountSql += String .format("select sum(%s) amount ,ti.reviewer userName " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is not null and ti.reviewTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "ti.reviewer") + " %s %s %s " @@ -1025,16 +997,16 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on mi.tousse_id = td.id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on mi.tousse_id = td.id "; } if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String - .format("select sum(%s) amount ,ti.sterilizationUser userName " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id " + .format("select sum(%s) amount ,sr.sterilizationUser userName " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID join SterilizationRecord"+ dbConnection.getNoLockSql() +" sr on sr.id=ti.sterilizationRecord_id " + " %s where %s and sr.endDate %s %s " - //+ "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 and ti.sterilizationRecord_id is not null" + //+ "and sr.sterilizationUser is not null and %s(sr.sterilizationUser)<>0 and ti.sterilizationRecord_id is not null" + " %s %s %s %s " - + "group by ti.sterilizationUser", + + "group by sr.sterilizationUser", amountColumnSql, joinMaterialInstanceSql, obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", @@ -1051,11 +1023,11 @@ amountSql += " union all "; } amountSql += String - .format("select sum(%s) amount ,ti.sterilizationUser userName " - + "from TousseInstance ti join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + .format("select sum(%s) amount ,sr.sterilizationUser userName " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti join SterilizationRecord"+ dbConnection.getNoLockSql() +" sr on sr.id=ti.sterilizationRecord_id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseDefinitionId is not null and sr.endDate %s %s " - //+ "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 and ti.sterilizationRecord_id is not null" - + " %s %s %s group by ti.sterilizationUser", + //+ "and sr.sterilizationUser is not null and %s(sr.sterilizationUser)<>0 and ti.sterilizationRecord_id is not null" + + " %s %s %s group by sr.sterilizationUser", amountColumnSql, joinMaterialInstanceSql, obj.betweenSql, @@ -1073,12 +1045,12 @@ joinMaterialInstanceSql = ""; }else{ miCountSql = "mi.count"; - joinMaterialInstanceSql = "inner join MaterialInstance mi on mi.tousse_id = td.id"; + joinMaterialInstanceSql = "inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on mi.tousse_id = td.id"; } amountSql = String .format("select sum(%s) amount %s " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id=i.id inner join TousseDefinition td on ii.tousseDefinitionId = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id=i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ii.tousseDefinitionId = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where i.sendTime %s %s %s and %s %s %s " //+ "and i.sender is not null and %s(i.sender)<>0" + " %s %s %s %s %s %s ", @@ -1103,7 +1075,7 @@ queryDisposableGoodAmountSql = " case when ti.disposableGoodAmount is null then 0 else ti.disposableGoodAmount end "; amountSql += String .format(" union all select sum(%s) amount %s " - + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.invoice_id = i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where %s and i.sendTime %s %s %s and %s %s %s " //+ "and i.sender is not null and %s(i.sender)<>0" + " %s %s %s %s %s ", @@ -1201,12 +1173,12 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on mi.tousse_id = td.id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on mi.tousse_id = td.id "; } if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select sum(%s) amount ,i.assistantSender userName " - + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.invoice_id = i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where %s and i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.assistantSender") + " %s %s %s %s " @@ -1226,7 +1198,7 @@ amountSql += " union all "; amountSql += String .format("select sum(ii.amount) amount ,i.assistantSender userName " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id = i.id " + "where ii.tousseType = '一次性物品' and i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.assistantSender") + "group by i.assistantSender", @@ -1242,7 +1214,7 @@ } amountSql += String .format("select sum(%s) amount ,i.assistantSender userName " - + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.invoice_id = i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is not null and i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.assistantSender") + " %s %s %s " @@ -1268,12 +1240,12 @@ joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; - joinMaterialInstanceSql = " inner join MaterialInstance mi on mi.tousse_id = td.id "; + joinMaterialInstanceSql = " inner join MaterialInstance"+ dbConnection.getNoLockSql() +" mi on mi.tousse_id = td.id "; } if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select sum(%s) amount ,i.personInCharge userName " - + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.invoice_id = i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where %s and i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.personInCharge") + "%s %s %s %s " @@ -1293,7 +1265,7 @@ amountSql += " union all "; amountSql += String .format("select sum(ii.amount) amount ,i.personInCharge userName " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id = i.id " + "where ii.tousseType = '一次性物品' and i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.personInCharge") + "group by i.personInCharge", @@ -1309,7 +1281,7 @@ } amountSql += String .format("select sum(%s) amount ,i.personInCharge userName " - + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.invoice_id = i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + " %s where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is not null and i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.personInCharge") + " %s %s %s " @@ -1339,7 +1311,6 @@ */ public String getWorkAmountByPackageSQL(String workType, ReportQueryParams obj) { - String amountSql = ""; String groupBySql = null; switch (workType) { @@ -1359,7 +1330,7 @@ } amountSql = String .format("select sum(item.amount) amount %s %s %s " - + "from InvoicePlan ip inner join TousseItem item on item.recyclingApplication_ID=ip.id inner join TousseDefinition td on td.id=item.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from InvoicePlan"+ dbConnection.getNoLockSql() +" ip inner join TousseItem"+ dbConnection.getNoLockSql() +" item on item.recyclingApplication_ID=ip.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=item.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where ip.applicationTime %s %s %s and %s %s %s " + "and item.tousseType<>'一次性物品' %s %s %s %s %s " + "group by td.tousseType,td.name ", @@ -1397,7 +1368,7 @@ } amountSql = String .format("select sum(ri.amount) amount %s %s %s %s %s" - + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId=td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from RecyclingRecord"+dbConnection.getNoLockSql()+" rr inner join RecyclingItem"+dbConnection.getNoLockSql()+" ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition"+dbConnection.getNoLockSql()+" td on ri.tousseDefinitionId=td.id join TousseDefinition"+dbConnection.getNoLockSql()+" tdc on tdc.id=td.ancestorID " + "where rr.recyclingTime %s %s %s and %s %s %s " + " %s %s %s %s %s %s", obj.queryIntegral?",sum(ri.amount*(case when tdc.integral is null then 1 else tdc.integral end)) integral":"", @@ -1420,8 +1391,8 @@ break; case "预回收数量": amountSql = String - .format("select SUM(ti.prepareRecycleAmount) amount %s %s from invoicePlan ip" - + " inner join TousseItem ti on ti.recyclingApplication_ID =ip.id inner join TousseDefinition td on ti.tousseDefinitionId = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + .format("select SUM(ti.prepareRecycleAmount) amount %s %s from invoicePlan"+dbConnection.getNoLockSql()+" ip" + + " inner join TousseItem"+dbConnection.getNoLockSql()+" ti on ti.recyclingApplication_ID =ip.id inner join TousseDefinition"+dbConnection.getNoLockSql()+" td on ti.tousseDefinitionId = td.id join TousseDefinition"+dbConnection.getNoLockSql()+" tdc on tdc.id=td.ancestorID " + " where ip.prepareRecycleDateTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "ip.prepareRecycleOperator") + " %s %s %s %s " @@ -1453,8 +1424,8 @@ } amountSql = String .format("select sum(ri.amount) amount %s %s %s %s %s" - + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id " - + "inner join TousseDefinition td on ri.tousseDefinitionId=td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + + "from RecyclingRecord"+dbConnection.getNoLockSql()+" rr inner join RecyclingItem"+dbConnection.getNoLockSql()+" ri on ri.recyclingRecord_id = rr.id " + + "inner join TousseDefinition"+dbConnection.getNoLockSql()+" td on ri.tousseDefinitionId=td.id join TousseDefinition"+dbConnection.getNoLockSql()+" tdc on tdc.id=td.ancestorID %s " + "where rr.recyclingTime %s %s %s and %s %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "rr.operator") + " %s %s %s %s %s %s", @@ -1493,7 +1464,7 @@ } amountSql = String.format( "select min(ci.tousseAmountForMaterial) amount %s %s %s %s %s %s " - + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td,TousseDefinition tdc " + + "from WashAndDisinfectRecord"+dbConnection.getNoLockSql()+" wdr,ClassifyBasket_WashRecord"+dbConnection.getNoLockSql()+" cw,ClassifyBasket"+dbConnection.getNoLockSql()+" cb,ClassifiedItem"+dbConnection.getNoLockSql()+" ci,TousseDefinition"+dbConnection.getNoLockSql()+" td,TousseDefinition"+dbConnection.getNoLockSql()+" tdc " + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and tdc.id=td.ancestorID " + "and ci.itemType = '材料' " + "and wdr.endDate %s %s %s and %s %s %s and wdr.washMaterialAmount <> 0 %s %s %s %s %s %s ", @@ -1518,7 +1489,7 @@ amountSql += " union all "; amountSql += String.format("select %s(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount %s %s %s %s %s %s " - + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td,TousseDefinition tdc " + + "from WashAndDisinfectRecord"+dbConnection.getNoLockSql()+" wdr,ClassifyBasket_WashRecord"+dbConnection.getNoLockSql()+" cw,ClassifyBasket"+dbConnection.getNoLockSql()+" cb,ClassifiedItem"+dbConnection.getNoLockSql()+" ci,TousseDefinition"+dbConnection.getNoLockSql()+" td,TousseDefinition"+dbConnection.getNoLockSql()+" tdc " + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and tdc.id=td.ancestorID " + "and wdr.endDate %s %s %s and %s %s %s and ci.itemtype != '材料' and wdr.washMaterialAmount <> 0 %s %s %s %s %s %s ", sum, @@ -1551,7 +1522,7 @@ if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select %s count(*) amount %s %s %s %s %s " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID %s " + "where %s and ti.operationTime %s %s %s and %s %s %s " + "%s %s %s %s %s " + "%s ", @@ -1582,7 +1553,7 @@ } amountSql += String .format("select %s count(*) amount %s %s %s %s %s " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID %s " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.operationTime %s %s %s and %s %s %s " + "%s %s %s %s %s ", getDatePeriodSelectSql(obj.monthlyStr), @@ -1607,72 +1578,43 @@ break; case "打包数量": - selectUserNameSql = ",wrapper userName "; + selectUserNameSql = ",po.wrapper userName "; selectTousseTypeSql = ",td.tousseType tousseType "; selectTousseNameSql = ",td.name tousseName "; - if(!obj.getIsOnlyQueryComboTousse()){ - amountSql = String - .format("select %s count(*) amount %s %s %s %s %s " - + "from PackagingRecord po join TousseInstance ti on ti.id=po.tousseInstance_id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " - + "where %s and po.entryDate %s %s %s and %s %s %s " - + "%s %s %s %s %s " - + "%s ", - getDatePeriodSelectSql(obj.monthlyStr), - obj.queryIntegral?",sum(case when tdc.integral is null then 1 else tdc.integral end) integral":"", - obj.selectUserName ? selectUserNameSql : "", - obj.selectTousseType ? selectTousseTypeSql : "", - obj.selectTousseName ? selectTousseNameSql : "", - obj.extraSelectColumns, - obj.extraJoinCondition, - obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", - obj.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), - SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.depart", obj.recyDepartGroup, " 1=1 "), - obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd, - obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql, - obj.extraQuery, - getMultipleFieldsGroupBySql(obj,",ti.wrapper")); - } - if(obj.getIsQueryCom()){ - if(!obj.getIsOnlyQueryComboTousse()){ - amountSql += " union all "; - } - amountSql += String - .format("select %s count(*) amount %s %s %s %s %s " - + "from PackagingRecord po join TousseInstance ti on ti.id=po.tousseInstance_id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " - + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null " - + "and po.entryDate %s %s %s and %s %s %s " - + "%s %s %s %s %s ", - getDatePeriodSelectSql(obj.monthlyStr), - obj.queryIntegral?",sum(case when tdc.integral is null then 1 else tdc.integral end) integral":"", - obj.selectUserName ? selectUserNameSql : "", - obj.selectTousseType ? selectTousseTypeSql : "", - obj.selectTousseName ? ",td.name tousseName " : "", - obj.extraSelectColumns, - obj.extraJoinCondition, - obj.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), - SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.depart", obj.recyDepartGroup, " 1=1 "), - obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd, - obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("tdc", "ti"), - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.extraQuery, - getMultipleFieldsGroupBySql(obj,",ti.wrapper")); - } + + amountSql = String + .format("select %s count(*) amount %s %s %s %s %s " + + "from PackagingRecord"+ dbConnection.getNoLockSql() +" po join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.id=po.tousseInstance_id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID %s " + + "where po.entryDate %s %s %s and %s %s %s " + + "%s %s %s %s %s " + + "%s ", + getDatePeriodSelectSql(obj.monthlyStr), + obj.queryIntegral?",sum(case when tdc.integral is null then 1 else tdc.integral end) integral":"", + obj.selectUserName ? selectUserNameSql : "", + obj.selectTousseType ? selectTousseTypeSql : "", + obj.selectTousseName ? selectTousseNameSql : "", + obj.extraSelectColumns, + obj.extraJoinCondition, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), + SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.depart", obj.recyDepartGroup, " 1=1 "), + obj.sterilizationModeSqlWithAliasOfTousseDefinitionIsTd, + obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, + getMultipleFieldsGroupBySql(obj,",po.wrapper")); + + break; // TODO:需要将此case与配包数量合并 case "年度报表中的配包数量（按包）": amountSql = String .format("select " + obj.monthlyStr + " monthstr, count(*) amount " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where ti.operationTime %s %s " + "%s %s %s %s " + "group by " @@ -1688,7 +1630,7 @@ case "检查数量": amountSql = String .format("select sum(pr.amount) amount %s %s " - + "from PackingRecord pr inner join TousseDefinition td on td.id=pr.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from PackingRecord"+ dbConnection.getNoLockSql() +" pr inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=pr.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where pr.packTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "pr.inspector") + " %s %s %s %s " @@ -1708,7 +1650,7 @@ case "包装数量": amountSql = String .format("select count(*) amount %s %s " - + "from PackingRecord pr inner join TousseInstance ti on ti.packingRecord_id=pr.id left join PackagingRecord prd on prd.tousseInstance_id=ti.id inner join TousseDefinition td on td.id=pr.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from PackingRecord"+ dbConnection.getNoLockSql() +" pr inner join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.packingRecord_id=pr.id left join PackagingRecord"+ dbConnection.getNoLockSql() +" prd on prd.tousseInstance_id=ti.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=pr.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where prd.id is null and pr.packTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "pr.wrapper") + " %s %s %s %s " @@ -1743,7 +1685,7 @@ if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select count(*) amount %s %s %s %s %s " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where %s and ti.reviewTime %s %s %s and %s %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "ti.reviewer") + " %s %s %s %s %s " @@ -1774,7 +1716,7 @@ } amountSql += String .format("select count(*) amount %s %s %s %s %s " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.reviewTime %s %s %s and %s %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "ti.reviewer") + " %s %s %s %s " @@ -1803,7 +1745,7 @@ case "年度报表中的灭菌记录数量（灭菌次数）": amountSql = String .format("select " + obj.monthlyStr + " monthstr, count(*) amount %s " - + "from Sterilizationrecord sr %s " + + "from Sterilizationrecord"+ dbConnection.getNoLockSql() +" sr %s " + "where sr.endDate %s %s %s " + "and (sr.status = '灭菌完成' or sr.status = '灭菌失败') " + "group by " @@ -1821,15 +1763,15 @@ if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select %s count(*) amount %s %s %s %s %s " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id %s " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID join SterilizationRecord"+ dbConnection.getNoLockSql() +" sr on sr.id=ti.sterilizationRecord_id %s " + "where %s and sr.endDate %s %s %s and %s %s %s " - //+ "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 " + //+ "and sr.sterilizationUser is not null and %s(sr.sterilizationUser)<>0 " //+ "and ti.sterilizationRecord_id is not null" + " %s %s %s %s %s " + "%s ", getDatePeriodSelectSql(obj.monthlyStr), obj.queryIntegral?",sum(case when tdc.integral is null then 1 else tdc.integral end) integral":"", - obj.selectUserName ? ",ti.sterilizationUser userName " : "", + obj.selectUserName ? ",sr.sterilizationUser userName " : "", obj.selectTousseType ? ",td.tousseType tousseType " : "", obj.selectTousseName ? ",td.name tousseName " : "", obj.extraSelectColumns, @@ -1847,22 +1789,22 @@ obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, obj.extraQuery, - getMultipleFieldsGroupBySql(obj,",ti.sterilizationUser")); + getMultipleFieldsGroupBySql(obj,",sr.sterilizationUser")); } if(obj.getIsQueryCom()){ if(!obj.getIsOnlyQueryComboTousse()){ amountSql += " union all "; } amountSql += String .format("select %s count(*) amount %s %s %s %s %s " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id %s " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id = td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID join SterilizationRecord"+ dbConnection.getNoLockSql() +" sr on sr.id=ti.sterilizationRecord_id %s " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and sr.endDate %s %s %s and %s %s %s " - //+ "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 " + //+ "and sr.sterilizationUser is not null and %s(sr.sterilizationUser)<>0 " //+ "and ti.sterilizationRecord_id is not null" + " %s %s %s %s %s %s ", getDatePeriodSelectSql(obj.monthlyStr), obj.queryIntegral?",sum(case when tdc.integral is null then 1 else tdc.integral end) integral":"", - obj.selectUserName ? ",ti.sterilizationUser userName " : "", + obj.selectUserName ? ",sr.sterilizationUser userName " : "", obj.selectTousseType ? ",td.tousseType tousseType " : "", obj.selectTousseName ? ",td.name tousseName " : "", obj.extraSelectColumns, @@ -1879,7 +1821,7 @@ obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql, obj.extraQuery, - getMultipleFieldsGroupBySql(obj,",ti.sterilizationUser")); + getMultipleFieldsGroupBySql(obj,",sr.sterilizationUser")); } break; case "发货数量": @@ -1902,8 +1844,8 @@ if (obj.includeTousses) { amountSql = String .format("select sum(ii.amount) amount %s %s %s %s %s " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " - + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID %s " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id = i.id " + + "inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=ii.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID %s " + "where i.sendTime %s %s %s and %s %s %s " //+ "and i.sender is not null and %s(i.sender)<>0" + " %s %s %s %s %s " @@ -1938,7 +1880,7 @@ } amountSql += String .format("select sum(ii.amount) amount %s %s %s %s %s " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id = i.id " + "where ii.tousseType = '一次性物品' and i.sendTime %s %s %s and %s " //+ "and i.sender is not null and %s(i.sender)<>0 " + " %s", @@ -1977,9 +1919,9 @@ if (obj.includeTousses) { amountSql = String .format("select count(*) amount %s %s %s %s " - + "from Invoice i join TousseInstance ti on ti.invoice_id=i.id " - + "inner join TousseDefinition td on td.id=ti.tousseDefinition_id " - + "join TousseDefinition tdc on tdc.id=td.ancestorID %s " + + "from Invoice"+ dbConnection.getNoLockSql() +" i join TousseInstance"+ dbConnection.getNoLockSql() +" ti on ti.invoice_id=i.id " + + "inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=ti.tousseDefinition_id " + + "join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID %s " + "where ti.urgentLevel_id is not null and i.sendTime %s %s %s and %s %s %s " + " %s %s %s %s %s " + " %s %s ", @@ -2010,8 +1952,8 @@ if (obj.includeTousses){ amountSql = String .format("select sum(ii.amount) amount %s " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " - + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id = i.id " + + "inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=ii.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where i.sendTime %s %s " //+ "and i.sender is not null and %s(i.sender)<>0" + " %s %s %s %s %s " @@ -2037,7 +1979,7 @@ amountSql = String .format("select sum(ii.amount) amount %s " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id = i.id " + "where ii.tousseType = '一次性物品' and i.sendTime %s %s " //+ "and i.sender is not null and %s(i.sender)<>0 " + "group by %s ", @@ -2055,7 +1997,7 @@ if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select sum(ii.amount) amount %s %s " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id = i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=ii.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where %s and i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.assistantSender") + " %s %s %s %s " @@ -2077,7 +2019,7 @@ } amountSql += String .format("select sum(ii.amount) amount %s %s " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem"+ dbConnection.getNoLockSql() +" ii on ii.invoice_id = i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=ii.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where "+ obj.getIsQueryComByPackageSize() +" td.tousseType ='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"' and i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.assistantSender") + " %s %s %s " @@ -2095,7 +2037,7 @@ case "下送数量": amountSql = String .format("select sum(ii.amount) amount %s %s " - + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + + "from Invoice"+ dbConnection.getNoLockSql() +" i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on td.id=ii.tousseDefinitionId join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID " + "where i.sendTime %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "i.personInCharge") + " %s %s %s %s " @@ -2116,7 +2058,7 @@ amountSql = String .format("select count(*) amount %s, td.tousseType, td.name tousseName " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id=td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + + "from TousseInstance"+ dbConnection.getNoLockSql() +" ti inner join TousseDefinition"+ dbConnection.getNoLockSql() +" td on ti.tousseDefinition_id=td.id join TousseDefinition"+ dbConnection.getNoLockSql() +" tdc on tdc.id=td.ancestorID %s " + "where ti.comboTousseInstanceId is null and ti.signedDate %s %s %s and %s %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "ti.signedUser") + " %s %s %s %s %s %s " Index: forgon-tools/src/main/java/com/forgon/Constants.java =================================================================== diff -u -r37008 -r37373 --- forgon-tools/src/main/java/com/forgon/Constants.java (.../Constants.java) (revision 37008) +++ forgon-tools/src/main/java/com/forgon/Constants.java (.../Constants.java) (revision 37373) @@ -28,7 +28,7 @@ "4.9.45","4.9.46","4.9.47","4.9.48","4.9.49","4.9.50","4.9.51","4.9.52","4.9.53","4.9.54","4.9.55","4.9.56","4.9.57","4.9.58","4.9.59","4.9.60","4.9.61","4.9.62", "4.9.63","4.9.64","4.9.65","4.9.66","4.9.67","4.9.68","4.9.69","4.9.70","4.9.71","4.9.72","4.9.73","4.9.74","4.9.75","4.9.76","4.9.77","4.9.78","4.9.79","4.9.80","4.9.81","4.9.82","4.9.83","4.9.84","4.9.85", "4.9.86","4.9.87","4.9.88","4.9.89","4.9.90","4.9.91","4.9.92","4.9.93","4.9.94","4.9.95","4.9.96","4.9.97","4.9.98","4.9.99","5.0.0","5.0.1","5.0.2","5.0.3","5.0.4","5.0.5","5.0.6","5.0.7", - "5.0.8","5.0.9","5.0.10","5.0.11","5.0.12","5.0.13","5.0.14","5.0.15","5.0.16","5.0.17"}; + "5.0.8","5.0.9","5.0.10","5.0.11","5.0.12","5.0.13","5.0.14","5.0.15","5.0.16","5.0.17","5.0.18"}; // 版本列表(4.0版本升级4.1版需要分两步:先从4.0升到4.1.0、然后从4.1.0升级4.1最新版本) /*public final static String[] SOFTWARE_VERSION_ARRAY = new String[] { Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java =================================================================== diff -u -r35589 -r37373 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 35589) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 37373) @@ -213,7 +213,7 @@ public ReportQueryParams(){} public ReportQueryParams(ObjectDao objectDao){ //包定义没有聚合包定义 查询聚合包时也不需要查询 节省资源 提高效率 - if(objectDao.countBySql("select count(*) from " + TousseDefinition.class.getSimpleName() +" where tousseType='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"'") == 0){ + if(this.haveCombo && objectDao.countBySql("select count(*) from " + TousseDefinition.class.getSimpleName() +" where tousseType='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"'") == 0){ this.haveCombo = false; } } Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/Invoice.java =================================================================== diff -u -r36780 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/Invoice.java (.../Invoice.java) (revision 36780) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/Invoice.java (.../Invoice.java) (revision 37373) @@ -55,8 +55,7 @@ ,@Index(columnList = "settleAccountsDepart", name = "iv_sad_index") ,@Index(columnList = "settleAccountsDepartCoding", name = "iv_sad_index") ,@Index(columnList = "signRecordId", name = "iv_srid_index") -//可加速InvoiceItem的发货数量查询 -,@Index(columnList = "sendTime,id,orgUnitCoding,id", name = "iv_sioi_index") +,@Index(columnList = "orgUnitCoding,sendTime,id,sender", name = "i_ouc_st_id_sender") }) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) public class Invoice implements IDAble {// 发货单 Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/washanddisinfectmanager/washanddisinfectrecord/WashAndDisinfectRecord.java =================================================================== diff -u -r36057 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/washanddisinfectmanager/washanddisinfectrecord/WashAndDisinfectRecord.java (.../WashAndDisinfectRecord.java) (revision 36057) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/washanddisinfectmanager/washanddisinfectrecord/WashAndDisinfectRecord.java (.../WashAndDisinfectRecord.java) (revision 37373) @@ -46,6 +46,7 @@ ,@Index(columnList = "startDate", name = "startDate_index") ,@Index(columnList = "endDate", name = "endDate_index") ,@Index(columnList = "washMaterialAmount", name = "washMaterialAmount_index") +,@Index(columnList = "endDate,orgUnitCoding,washMaterialAmount,id,operator", name = "wdfr_ed_ouc_wma_id_op") }) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) public class WashAndDisinfectRecord { Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/InvoiceItem.java =================================================================== diff -u -r36780 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/InvoiceItem.java (.../InvoiceItem.java) (revision 36780) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/invoicemanager/InvoiceItem.java (.../InvoiceItem.java) (revision 37373) @@ -38,8 +38,7 @@ ,@Index(columnList = "expensiveDisposablegoodsId", name = "iim_exDisposablegoodsId_index"),@Index(columnList = "diposable", name = "iim_diposable_index") ,@Index(columnList = "tousseType", name = "iim_tousseType_index"),@Index(columnList = "invoiceID", name = "iim_invoiceID_index"),@Index(columnList = "invoice_id", name = "iim_invoice_id_index") ,@Index(columnList = "invoicePlanID", name = "iim_invoicePlanID_index"),@Index(columnList = "tousseDefinitionId", name = "iim_tousseDefinitionId_index") -//可加速InvoiceItem的发货数量查询 -,@Index(columnList = "tousseDefinitionId,id,invoice_id,amount", name = "iim_tiia_index") +,@Index(columnList = "amount,invoice_id,tousseDefinitionId,materialAmount", name = "ii_amount_ii_tdi_ma") }) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) public class InvoiceItem implements IDAble { Index: ssts-web/src/main/webapp/dataUpdater/sqls/5.0.17_5.0.18.sql =================================================================== diff -u --- ssts-web/src/main/webapp/dataUpdater/sqls/5.0.17_5.0.18.sql (revision 0) +++ ssts-web/src/main/webapp/dataUpdater/sqls/5.0.17_5.0.18.sql (revision 37373) @@ -0,0 +1,3 @@ +update PackagingRecord set Wrapper = (select Wrapper from TousseInstance where id=PackagingRecord.tousseInstance_Id) +,WrapperCode=(select WrapperCode from TousseInstance where id=PackagingRecord.tousseInstance_Id) +where Wrapper is null and tousseInstance_Id in (select id from TousseInstance where Wrapper is not null and Wrapper<>''); \ No newline at end of file Index: ssts-packing/src/main/java/com/forgon/disinfectsystem/packaging/service/PackagingRecordManagerImpl.java =================================================================== diff -u -r33993 -r37373 --- ssts-packing/src/main/java/com/forgon/disinfectsystem/packaging/service/PackagingRecordManagerImpl.java (.../PackagingRecordManagerImpl.java) (revision 33993) +++ ssts-packing/src/main/java/com/forgon/disinfectsystem/packaging/service/PackagingRecordManagerImpl.java (.../PackagingRecordManagerImpl.java) (revision 37373) @@ -40,18 +40,18 @@ if (StringUtils.isNotBlank(searchKeyWord)){ Set searchColumnArray = SqlUtils.splitStringToSet(searchColumns, ";"); if(searchColumnArray.size() == 3){ - whereSql += " and (ti.wrapper like '%"+ searchKeyWord +"%' or ti.tousseName like '%" + whereSql += " and (po.wrapper like '%"+ searchKeyWord +"%' or ti.tousseName like '%" + searchKeyWord +"%' or bd.barcode like '%"+ searchKeyWord +"%') "; }else if(searchColumnArray.contains("barcode") && searchColumnArray.contains("tousseName")){ whereSql += " and (ti.tousseName like '%"+ searchKeyWord +"%' or bd.barcode like '%"+ searchKeyWord +"%') "; }else if(searchColumnArray.contains("barcode") && searchColumnArray.contains("wrapper")){ - whereSql += " and (ti.wrapper like '%"+ searchKeyWord +"%' or bd.barcode like '%"+ searchKeyWord +"%') "; + whereSql += " and (po.wrapper like '%"+ searchKeyWord +"%' or bd.barcode like '%"+ searchKeyWord +"%') "; }else if(searchColumnArray.contains("barcode")){ whereSql += " and bd.barcode like '%"+ searchKeyWord +"%' "; }else if(searchColumnArray.contains("tousseName")){ whereSql += " and ti.tousseName like '%"+ searchKeyWord +"%' "; }else if(searchColumnArray.contains("wrapper")){ - whereSql += " and ti.wrapper like '%"+ searchKeyWord +"%' "; + whereSql += " and po.wrapper like '%"+ searchKeyWord +"%' "; } } result = new JSONObject(); @@ -64,7 +64,7 @@ }else{ orderBy =" order by \"entryDate\" "; } - String queryColumnsSql = "select " + rowIdSql +" po.id \"id\",ti.sterileStartTime \"sterileStartTime\", ti.validUntil \"validUntil\", ti.tousseName \"tousseName\",ti.wrapper \"wrapper\",ti.wrapperCode \"wrapperCode\", po.entryDate \"entryDate\",bd.barcode \"barcode\" "; + String queryColumnsSql = "select " + rowIdSql +" po.id \"id\",ti.sterileStartTime \"sterileStartTime\", ti.validUntil \"validUntil\", ti.tousseName \"tousseName\",po.wrapper \"wrapper\",po.wrapperCode \"wrapperCode\", po.entryDate \"entryDate\",bd.barcode \"barcode\" "; String tableSql = " from PackagingRecord po " + "left join TousseInstance ti on ti.id=po.tousseInstance_id " + "left join barcodeDevice bd on bd.id=ti.id "; @@ -124,6 +124,8 @@ packagingRecord.setEntryDate(new Date()); packagingRecord.setOrgUnitCoding(orgUnitCoding); packagingRecord.setTousseInstance_Id(tousseInstance.getId()); + packagingRecord.setWrapper(packer); + packagingRecord.setWrapperCode(packerBarcode); objectDao.save(packagingRecord); tousseInstance.setWrapper(packer); tousseInstance.setWrapperCode(packerBarcode); Index: forgon-tools/src/main/java/com/forgon/tools/db/InitDbConnection.java =================================================================== diff -u -r14586 -r37373 --- forgon-tools/src/main/java/com/forgon/tools/db/InitDbConnection.java (.../InitDbConnection.java) (revision 14586) +++ forgon-tools/src/main/java/com/forgon/tools/db/InitDbConnection.java (.../InitDbConnection.java) (revision 37373) @@ -11,7 +11,10 @@ private String password; private String database; - + /** + * sqlserver的NOLOCK + */ + private static String NOLOCK = "(NOLOCK)"; public String getDatabase() { return database; } @@ -72,4 +75,16 @@ public boolean isMySQL(){ return DatabaseUtil.isMySQL(database); } + /** + * sqlserver可以通过(NOLOCK)查询被锁住的记录 + * 有助于提高查询性能， 减少等待时间， + * 但使用时需谨慎，由于 "NOLOCK" 查询可能会读取到未提交的更改，因此返回的结果可能正在修改中， 需要查询最新的数据时 不宜使用 + * @return + */ + public String getNoLockSql(){ + if(isSqlServer()){ + return NOLOCK; + } + return ""; + } } Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/sterilizationmanager/sterilizationrecord/SterilizationRecord.java =================================================================== diff -u -r36957 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/sterilizationmanager/sterilizationrecord/SterilizationRecord.java (.../SterilizationRecord.java) (revision 36957) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/sterilizationmanager/sterilizationrecord/SterilizationRecord.java (.../SterilizationRecord.java) (revision 37373) @@ -54,6 +54,7 @@ ,@Index(columnList = "endDate", name = "endDate_index") ,@Index(columnList = "startDate", name = "startDate_index") ,@Index(columnList = "status", name = "status_index") +,@Index(columnList = "endDate,id,orgUnitCoding,sterilizationUser", name = "sr_ed_id_ouc_su") }) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) public class SterilizationRecord { Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/becleanitem/ClassifiedItem.java =================================================================== diff -u -r36614 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/becleanitem/ClassifiedItem.java (.../ClassifiedItem.java) (revision 36614) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/becleanitem/ClassifiedItem.java (.../ClassifiedItem.java) (revision 37373) @@ -35,6 +35,7 @@ ,@Index(columnList = "recyclingRecordId", name = "recyclingRecordId_index") ,@Index(columnList = "invoicePlanID", name = "invoicePlanID_index") ,@Index(columnList = "lastTousseInstanceId", name = "ci_lastTousseInstanceId_index") +,@Index(columnList = "tousseDefinitionID,itemType,classifybasket_id,toussedefinition_id,amount,numOfUnwashedStops,materialAmount", name = "ci_td_itemtype_cb_tdi_anm") }) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) /** Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/recyclingrecord/RecyclingItem.java =================================================================== diff -u -r32229 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/recyclingrecord/RecyclingItem.java (.../RecyclingItem.java) (revision 32229) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/recyclingrecord/RecyclingItem.java (.../RecyclingItem.java) (revision 37373) @@ -41,6 +41,7 @@ @DynamicUpdate(true) @Table(indexes = {@Index(columnList = "tousseDefinitionId", name = "rci_tdid_index") ,@Index(columnList = "recyclingRecord_id", name = "rci_rrid_index") +,@Index(columnList = "tousseDefinitionId,recyclingRecord_id,amount,materialAmount", name = "rci_td_rr_amount_mamount") }) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) public class RecyclingItem { Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java =================================================================== diff -u -r36796 -r37373 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java (.../TousseInstance.java) (revision 36796) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java (.../TousseInstance.java) (revision 37373) @@ -107,8 +107,6 @@ ,@Index(columnList = "urgentLevel_id", name = "tis_urgentLvId_index") ,@Index(columnList = "lastStorageLocationId", name = "tis_lastStorage_id_index") ,@Index(columnList = "settleAccountsDepartCode", name = "tis_settleAccoDeptCode_index") -//效果包含但不限于加速器械包工作量报表查询(提升很大 以20NFYKDXFSDSYY数据库为例子 查询器械包工作量报表中装配工作量21年到23年8月的数据 从26秒变为1s) -,@Index(columnList = "orgUnitCoding,operationTime,tousseDefinition_id,materialAmount,disposableGoodAmount", name = "tis_ootmd_index") //效果包含但不限于加速加急灭菌装载模块加急待装载物品,当时gzykdxfskqyy数据库从10s查询变为0s,删除此索引慎重 ,@Index(columnList = "comboTousseInstanceId,reviewBasket_id,status,orgUnitCoding,proxyDisinfection_id,tousseDefinition_id", name = "tis_crsopt_index") //加速灭菌的发货数量查询和不灭菌的发货数量查询 @@ -117,7 +115,11 @@ ,@Index(columnList = "invoice_id,id,tousseDefinition_id,invoice2_id,materialAmount", name = "tis_iitim_index") //系统告警查询优化 ,@Index(columnList = "location,comboTousseInstanceId,invoice2_id,status,validUntil", name = "tis_lcisv_index") -}) +//报表数据查询优化 +//,@Index(columnList = "orgUnitCoding,comboTousseDefinitionId,comboTousseInstanceId,operationTime,tousseDefinition_id,id,operator,materialAmount,disposableGoodAmount", name = "ti_occotiomd_index") +//,@Index(columnList = "orgUnitCoding,reviewTime,comboTousseInstanceId,comboTousseDefinitionId,reviewer,id,tousseDefinition_id,materialAmount,disposableGoodAmount", name = "ti_ou_rt_ctii_ctd_r_id_td") +,@Index(columnList = "orgUnitCoding,operationTime,operator,reviewTime,reviewer,comboTousseDefinitionId,comboTousseInstanceId,tousseDefinition_id,id,materialAmount,disposableGoodAmount", name = "ouc_ot_o_rt_r_ci_ci_td_m_d") +,@Index(columnList = "sterilizationRecord_id,comboTousseDefinitionId,comboTousseInstanceId,id,tousseDefinition_id,materialAmount,disposableGoodAmount", name = "ti_sr_ctdi_ctii_id_td")}) @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) @JsonFilter("fieldFilter") public class TousseInstance extends BarcodeDevice implements Comparable,IDAble,Cloneable{