Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/common/CssdUtils.java =================================================================== diff -u -r38132 -r38340 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/common/CssdUtils.java (.../CssdUtils.java) (revision 38132) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/common/CssdUtils.java (.../CssdUtils.java) (revision 38340) @@ -16,6 +16,7 @@ import java.net.URL; import java.net.URLConnection; import java.util.ArrayList; +import java.util.Calendar; import java.util.Collection; import java.util.Date; import java.util.HashMap; @@ -2394,4 +2395,47 @@ } return result.toString(); } + + /** + * 根据config.js的配置对应的模块名,返回对应的查询范围 + * @param functionalModule 功能模块代号,即functionalModule这个key对应的值 + * @return 如果未配置或配置为非法值(合法值为1-2,147,483,648的正整数)时,则一律返回0(即查所有范围),否则返回对应的合法值 + */ + public static int getQueryPeroidOfModule(String functionalModule){ + String defaultQueryTimeOfTheFunctionModule = getSystemSetConfigByName("defaultQueryTimeOfTheFunctionModule"); + if(StringUtils.isNotBlank(functionalModule)){ + try{ + JSONArray defaultQueryTimeOfTheFunctionModuleJsonArray = JSONArray.fromObject(defaultQueryTimeOfTheFunctionModule); + if(CollectionUtils.isNotEmpty(defaultQueryTimeOfTheFunctionModuleJsonArray)){ + for(int i = 0;i < defaultQueryTimeOfTheFunctionModuleJsonArray.size();i++){ + JSONObject jsonObject = defaultQueryTimeOfTheFunctionModuleJsonArray.optJSONObject(i); + if(StringUtils.equalsIgnoreCase(jsonObject.optString("functionalModule"), functionalModule)){ + return jsonObject.optInt("queryPeriod"); + } + } + } + }catch(Exception e){ + e.printStackTrace(); + } + } + return 0; + } + + /** + * 根据查询的天数,返回对应的开始日期与结束日期 + * @param queryPeroid 查询的天数(合法值为1-2,147,483,648的正整数),如为非法值,则返回null + * @return + */ + public static String[] getQueryPeroidOfModule(int queryPeroid){ + Date startDate = null, endDate = null; + if(queryPeroid > 0 && queryPeroid < Integer.MAX_VALUE){ + Calendar cal = Calendar.getInstance(); + cal.add(Calendar.DAY_OF_YEAR, queryPeroid * -1); + startDate = cal.getTime(); + endDate = new Date(); + return new String[]{ForgonDateUtils.safelyFormatDate(startDate, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDD, ""),ForgonDateUtils.safelyFormatDate(endDate, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDD, "")}; + } + return null; + } + } Index: ssts-web/src/main/webapp/disinfectsystem/invoice/invoicePlanExtractedView.js =================================================================== diff -u -r36881 -r38340 --- ssts-web/src/main/webapp/disinfectsystem/invoice/invoicePlanExtractedView.js (.../invoicePlanExtractedView.js) (revision 36881) +++ ssts-web/src/main/webapp/disinfectsystem/invoice/invoicePlanExtractedView.js (.../invoicePlanExtractedView.js) (revision 38340) @@ -139,22 +139,21 @@ function refreshList(isSetDateValue,isLoad){ processListParams(isSetDateValue,isLoad); - var store = grid.getStore(); - store.baseParams['departCode'] = invoiceDepartGroups; - store.baseParams['applyDate'] = selectedApplyDate; - store.baseParams['tousseType'] = selectedTousseType; - store.baseParams['disposableGoodsType'] = selectedDisposableGoodsType; - store.baseParams['sterilingMethods'] = selectedSterilingMethods; - store.baseParams['appFormType'] = selectedAppFormType; - store.baseParams['displayMode'] = selectedDisplayMode; - store.baseParams['tousseGroupIds'] = tousseGroupIds; - store.baseParams['invoiceType'] = invoiceType; - if(sstsConfig.enableInstrumentSetTypeSetting){ - store.baseParams['instrumentSetTypeIds'] = Ext.getCmp('instrumentSetTypeIds').getValue(); - } - //刷新列表界面 - store.load(); - // } + var store = grid.getStore(); + store.baseParams['departCode'] = invoiceDepartGroups; + store.baseParams['applyDate'] = selectedApplyDate; + store.baseParams['tousseType'] = selectedTousseType; + store.baseParams['disposableGoodsType'] = selectedDisposableGoodsType; + store.baseParams['sterilingMethods'] = selectedSterilingMethods; + store.baseParams['appFormType'] = selectedAppFormType; + store.baseParams['displayMode'] = selectedDisplayMode; + store.baseParams['tousseGroupIds'] = tousseGroupIds; + store.baseParams['invoiceType'] = invoiceType; + if(sstsConfig.enableInstrumentSetTypeSetting){ + store.baseParams['instrumentSetTypeIds'] = Ext.getCmp('instrumentSetTypeIds').getValue(); + } + //刷新列表界面 + store.load(); } @@ -333,6 +332,39 @@ }); return applyDateDataArray; } + +/** + * 获取默认开始日期与结束日期,并刷新grid数据 + * @returns + */ +function getDefaultStartDateAndEndDateByDefaultQueryPeriod(){ + Ext.Ajax.request({ + url : WWWROOT + '/disinfectSystem/invoicePlanAction!getDefaultQueryStartDateAndEndDate.do', + params : {}, + success : function(response, options) { + var result = Ext.decode(response.responseText); + if(result && result.success){ + if(result.data){ + var startDate = Ext.getCmp('startDate'); + var endDate = Ext.getCmp('endDate'); + var defaultStartDate = result.data[0]; + var defaultEndDate = result.data[1]; + if(sstsConfig.timeSearchFmt){ + defaultStartDate += " 00:00"; + defaultEndDate += " 23:59"; + } + startDate.setValue(defaultStartDate); + endDate.setValue(defaultEndDate); + } + } + refreshList(); + }, + failure : function(form, action) { + + } + }); +} + var ViewAppGridItem = Ext.data.Record.create([ {name : 'showTousseName'}, {name : 'count'}, @@ -363,6 +395,7 @@ return; } checkedOrgUnitCoding = rs[0].data['departCode']; + //如果是点击的科室列,则不加载待发货列表,点击的为本行的其它列才加载 view_recyclingapplicationStore.load(); @@ -437,7 +470,7 @@ } diposableGooodsStore.on('load', function(){ selectDisposableGoodsTypeOnLoad(); - refreshList(); + //refreshList(); }); diposableGooodsStore.load(); var departGroupStore = new Ext.data.SimpleStore({ @@ -538,6 +571,8 @@ }); + //获取默认开始日期与结束日期,并刷新grid数据 + getDefaultStartDateAndEndDateByDefaultQueryPeriod(); // 右边的明细 var view_rd = new Ext.data.JsonReader( { fields : [ Index: ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/service/InvoicePlanOptimizeManagerImpl.java =================================================================== diff -u -r36938 -r38340 --- ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/service/InvoicePlanOptimizeManagerImpl.java (.../InvoicePlanOptimizeManagerImpl.java) (revision 36938) +++ ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/service/InvoicePlanOptimizeManagerImpl.java (.../InvoicePlanOptimizeManagerImpl.java) (revision 38340) @@ -2,6 +2,7 @@ import java.sql.ResultSet; import java.text.SimpleDateFormat; +import java.time.LocalDateTime; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; @@ -146,26 +147,36 @@ String invoiceOrigin,String tousseDeliverOccasion){ //申请单表的别名 String invoicePlanTableAlias = "ip";//表1的字段的前缀 - //根据条件参与构造查询需要条件语句 - String sqlCondition = + //根据条件参与构造查询需要条件语句(不包括时间条件的过滤) + String commonQueryCondition = buildWaitSendInvoicePlanSqlCondition(filterParams, invoicePlanTableAlias, invoiceOrigin,tousseDeliverOccasion); //发货科室分组(默认为departCoding,如果配置项配置为结算科室时,则值为settleAccountsDepartCoding) - String groupBy = InvoiceUtils.getInvoiceGroupByConfig(); + String departCodingColumnName = InvoiceUtils.getInvoiceGroupByConfig(); + //ZSYY-377【发货计划】增加默认查询时间周期来优化查询速度 将申请时间和回收时间分开成两个条件,对应两条query语句进行union,再进行分组统计 + String[] timeCondition = returnTimeCondition(filterParams.getApplyDate(), invoicePlanTableAlias + "."); - //需要查询的字段 - String queryColumns = groupBy + ",type"; - //查询条件 - String querySql = String.format("select %s,max(ul.grade) grade,count(0) cnt from %s "+ invoicePlanTableAlias +" " + //查询的字段 + String queryColumns = departCodingColumnName + ",type"; + //查询语句,根据ZSYY-377问题进行改造，语句分成内外两层：内层为查询符合条件的申请单明细数据,外层为按科室编码与申请单类型进行分组统计,并依次按加急级别降序、科室顺序号升序及申请时间升序 + //查询明细(如果时间条件存在有多个时,则对多个不同的select语句进行union连接) + String queryInvoicePlanDetailSql = String.format("select %s,grade,t1.id,t1.sequence,applicationTime from %s "+ invoicePlanTableAlias +" " + "left join %s ul on "+ invoicePlanTableAlias +".urgentLevel_id=ul.id " - + "left join (select sequence,orgUnitCoding,id from %s src where src.supplyRoomType = 3) t1 on t1.orgUnitCoding="+ invoicePlanTableAlias +".departCoding" - + "%s group by %s " - + "order by max(case when ul.grade is null then 0 else ul.grade end) desc," - + "min(case when t1.id is null then 99999 else t1.sequence end)," - + "min("+ invoicePlanTableAlias +".applicationTime)", - queryColumns , InvoicePlan.class.getSimpleName(), - UrgentLevel.class.getSimpleName(), SupplyRoomConfig.class.getSimpleName(), - sqlCondition , queryColumns); + + "left join (select sequence,orgUnitCoding,id from %s src where src.supplyRoomType = 3) t1 on t1.orgUnitCoding="+ invoicePlanTableAlias +".departCoding " + + " %s %s", queryColumns,InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName(), SupplyRoomConfig.class.getSimpleName(),commonQueryCondition,"%s"); + List queryInvoicePlanDetailSqlList = new ArrayList(); + //根据时间条件生成对应的一条(或多条查询明细语句,用union连接) + if(timeCondition == null){ + queryInvoicePlanDetailSqlList.add(String.format(queryInvoicePlanDetailSql, "")); + }else{ + for (int i = 0; i < timeCondition.length; i++) { + queryInvoicePlanDetailSqlList.add(String.format(queryInvoicePlanDetailSql, " and " + timeCondition[i])); + } + } + String querySql = String.format("select %s,max(grade) grade,count(0) cnt from (%s) T group by %s " + + "order by max(case when grade is null then 0 else grade end) desc," + + "min(case when id is null then 99999 else sequence end),min(applicationTime)", + queryColumns ,StringUtils.join(queryInvoicePlanDetailSqlList," union ") , queryColumns); Collection invoicePlans = null; logger.debug("getInvoicePlans_NEW querySql=" + querySql); List> list = jdbcTemplate.queryForList(querySql); @@ -176,7 +187,7 @@ List departCodingList = new ArrayList(); Map departCodingGradeMap = null; for (Map map : list) { - String departCode = (String)map.get(groupBy); + String departCode = (String)map.get(departCodingColumnName); if(!departCodingList.contains(departCode)){ departCodingList.add(departCode); } @@ -280,10 +291,9 @@ private String buildWaitSendInvoicePlanSqlCondition(SearchInvoicePlanOrGoodsParams filterParams, String invoicePlanTableAlias, String invoiceOrigin,String tousseDeliverOccasion){ String departCodes = filterParams.getDepartCode(); - String applyDate = filterParams.getApplyDate(); String appFormType = filterParams.getAppFormType(); String where1 = - buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(departCodes, applyDate, appFormType, null , + buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(departCodes, appFormType, null , invoicePlanTableAlias,invoiceOrigin,tousseDeliverOccasion); //表1的字段的前缀 String invoicePlanTableColumnAliasPre1 = (StringUtils.isNotBlank(invoicePlanTableAlias)) ? invoicePlanTableAlias + "." : ""; @@ -335,6 +345,7 @@ @Override public Collection getWaitDeliverGoods(SearchInvoicePlanOrGoodsParams filterParams) { + LocalDateTime localDateTime = LocalDateTime.now(); String getInvoicePlanId = filterParams.getInvoicePlanId(); Long invoicePlanId = null; if(DatabaseUtil.isPoIdValid(getInvoicePlanId)){ @@ -346,11 +357,7 @@ if(DatabaseUtil.isPoIdValid(getWarehouseId)){ warehouseId = Long.parseLong(getWarehouseId); } - String applyDate = filterParams.getApplyDate(); String tousseTypes = filterParams.getTousseType(); - String disposableGoodsType = filterParams.getDisposableGoodsType(); - String sterilingMethods = filterParams.getSterilingMethods(); - String tousseGroupIds = filterParams.getTousseGroupIds(); String appFormType = filterParams.getAppFormType(); Collection result = null; Collection list = new ArrayList(); @@ -379,13 +386,13 @@ //申请单过滤公共条件(按申请单id,申请科室等),用于所有类型场景的申请单过滤条件 String invoicePlanCommonCondition = null; //外来器械包申请单过滤条件(未装配的查申请项、装配过的查包实例)、还需要查出相关的申请单id - String invoicePlanForeignTousseCondition = null; + String invoicePlanForeignTousseConditionExcludingAppDate = null; //需要绑定申请单的各种物品场景的过滤条件(用于外部代理灭菌包、自定义器械包)、还需要查出相关的申请单id - String invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction = null; + String invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate = null; //需要绑定申请单的各种物品场景的过滤条件(用于代理灭菌的包等)、还需要查出相关的申请单id - String invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection = null; + String invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate = null; //公共类型的申请单的过滤条件(一次性物品、器械包、敷料包、消毒物品等)、只统计物品不查申请单. - String invoicePlanCommonGoodsCondition = null; + String invoicePlanCommonGoodsConditionExcludingAppDate = null; if(DatabaseUtil.isPoIdValid(invoicePlanId)){ invoicePlanCommonCondition = String.format(invoicePlanTableAlias +".id=%s", invoicePlanId); @@ -411,6 +418,8 @@ warehouseId = defaultWarehouse.getId(); } } + //ZSYY-377【发货计划】增加默认查询时间周期来优化查询速度 将申请时间和回收时间分开成两个条件,对应两条query语句进行union,再进行分组统计 + String[] timeCondition = returnTimeCondition(filterParams.getApplyDate(), invoicePlanTableAlias + "."); /* * 1.如果appFormType为null,则分开查询外来器械包申请单、需要绑定申请单的各种物品、公共类型的申请单的过滤条件(一次性物品、器械包、敷料包等)、消毒物品的过滤查询等分开查询 @@ -430,43 +439,43 @@ }); if(StringUtils.isNotBlank(appFormType)){ if(InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION.equals(appFormType)){ - invoicePlanForeignTousseCondition = String.format("%s and %s", + invoicePlanForeignTousseConditionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, buildWaitSendInvoicePlanSqlConditionOnlyForeignTousseApplication(null, - applyDate, invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); + invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); }else if(InvoicePlan.TYPE_FOREIGNPROXYDISINFECTION.equals(appFormType) || InvoicePlan.TYPE_CUSTOM_TOUSSE_APPLIACTION_FORM.equals(appFormType)){ - invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction = String.format("%s and %s", + invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction(null, applyDate, + buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction(null, invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); }else if(InvoicePlan.TYPE_PROXYDISINFECTION.equals(appFormType)){ - invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection = String.format("%s and %s", + invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionForProxyDisinfection(null, applyDate, + buildWaitSendInvoicePlanSqlConditionForProxyDisinfection(null, invoicePlanTableAlias, invoiceOrigin, tousseDeliverOccasion)); }else{ - invoicePlanCommonGoodsCondition = String.format("%s and %s", + invoicePlanCommonGoodsConditionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, applyDate, + buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, appFormType , excludeInvoicePlanTypeList , invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); } }else{ - invoicePlanForeignTousseCondition = String.format("%s and %s", + invoicePlanForeignTousseConditionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, buildWaitSendInvoicePlanSqlConditionOnlyForeignTousseApplication(null, - applyDate, invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); - invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction = String.format("%s and %s", + invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); + invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction(null, applyDate, + buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction(null, invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); - invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection = String.format("%s and %s", + invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionForProxyDisinfection(null, applyDate, + buildWaitSendInvoicePlanSqlConditionForProxyDisinfection(null, invoicePlanTableAlias, invoiceOrigin, tousseDeliverOccasion)); - invoicePlanCommonGoodsCondition = String.format("%s and %s", + invoicePlanCommonGoodsConditionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, applyDate, + buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, appFormType , excludeInvoicePlanTypeList , invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); } @@ -479,7 +488,7 @@ includeForeignTousseType = true; } //1.如果存在外来器械单的查询条件时 - if(StringUtils.isNotBlank(invoicePlanForeignTousseCondition) && includeForeignTousseType){ + if(StringUtils.isNotBlank(invoicePlanForeignTousseConditionExcludingAppDate) && includeForeignTousseType){ //申请项的过滤条件 //申请项表的别名 String tousseItemTableAlias = "ti"; @@ -503,8 +512,8 @@ tousseItemWaitSendAmountSumSql = "(recyclingAmount - sendOutAmount)"; } - //查询待发货申请单的申请项 - String queryTousseItemSql = String.format( + //1.先查询待发货申请单的申请项 + String queryTousseItemSqlForUnpackingTousseInstance = String.format( "select "+ invoicePlanTableAlias +".id invoicePlanId," + tousseItemTableAlias +".id tousseItemId," + tousseItemTableAlias +".tousseName," @@ -518,12 +527,13 @@ + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 ", TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , - tousseItemCondition,String.format("%s and %s", invoicePlanForeignTousseCondition, - String.format(invoicePlanTableAlias +".id not in (select foreignTousseApp_id from %s where foreignTousseApp_id is not null)",TousseInstance.class.getSimpleName()))); + tousseItemCondition,String.format("%s and %s", invoicePlanForeignTousseConditionExcludingAppDate, + String.format(invoicePlanTableAlias +".id not in (select foreignTousseApp_id from %s where foreignTousseApp_id is not null)", + TousseInstance.class.getSimpleName()))); - //再查已经装配生成包实例的申请单。由于后面括号里这个问题要求不再把包实例的状态列进条件里,所以不再使用此条件(SZSDSRMYY-46 添加外来器械申请单后，要根据科室供应室设置里的器械包发货时间来决定该发货计划的显示) - queryTousseItemSql += String.format(" union all " - + "select "+ invoicePlanTableAlias +".id invoicePlanId,null tousseItemId," + //2.再查已经装配生成包实例的申请单。由于后面括号里这个问题要求不再把包实例的状态列进条件里,所以不再使用此条件(SZSDSRMYY-46 添加外来器械申请单后，要根据科室供应室设置里的器械包发货时间来决定该发货计划的显示) + //倒数第二个%s点位符用于插入时间过滤条件用,无时间过滤条件时,替换为空字符串，最后一个%s为order by,union语句中只有最后一条语句末尾才可以加order by + String queryTousseItemSqlForPackedTousseInstance = String.format("select "+ invoicePlanTableAlias +".id invoicePlanId,null tousseItemId," + tousseInstanceTableAlias +".tousseName," + "td.tousseType,'%s' diposable,ul.grade," + invoicePlanTableAlias +".applicationTime," @@ -533,11 +543,20 @@ + "join %s "+ invoicePlanTableAlias +" on "+ tousseInstanceTableAlias +".foreignTousseApp_id="+ invoicePlanTableAlias +".id " + "left join %s ul on "+ tousseInstanceTableAlias +".urgentLevel_id=ul.id " + "left join %s td on "+ tousseInstanceTableAlias +".tousseDefinition_id=td.id " - + "where %s and %s order by grade desc,applicationTime", Constants.STR_NO, + + "where %s and %s %s", Constants.STR_NO, TousseInstance.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , - TousseDefinition.class.getSimpleName() ,invoicePlanForeignTousseCondition,tousseInstanceCondition); - - Collection list1 = getWaitDeliverGoodsForForeignTousse(queryTousseItemSql); + TousseDefinition.class.getSimpleName() ,invoicePlanForeignTousseConditionExcludingAppDate,tousseInstanceCondition,"%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format("%s and %s", queryTousseItemSqlForUnpackingTousseInstance,timeCondition[0])); + queryTousseItemSqlList.add(String.format("%s and %s", queryTousseItemSqlForUnpackingTousseInstance,timeCondition[1])); + queryTousseItemSqlList.add(String.format(queryTousseItemSqlForPackedTousseInstance, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryTousseItemSqlForPackedTousseInstance, " and " + timeCondition[1] + " order by grade desc,applicationTime ")); + }else{ + queryTousseItemSqlList.add(queryTousseItemSqlForUnpackingTousseInstance); + queryTousseItemSqlList.add(String.format(queryTousseItemSqlForPackedTousseInstance, "")); + } + Collection list1 = getWaitDeliverGoodsForForeignTousse(StringUtils.join(queryTousseItemSqlList," union ")); if(CollectionUtils.isNotEmpty(list1)){ //外来器械包设置是否追溯为是 for(ApplicationGoodsVo vo : list1){ @@ -548,7 +567,7 @@ } //如果存在需返回申请单id的查询条件时(用于外部代理灭菌单、自定义器械包申请单) - if(StringUtils.isNotBlank(invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction)){ + if(StringUtils.isNotBlank(invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate)){ //申请项的过滤条件 filterParams.setTousseType(tousseTypes); //申请项表的别名 @@ -562,7 +581,7 @@ CssdUtils.buildTousseItemShouldDeliverAmountSql(invoicePlanTableAlias, tousseItemTableAlias, invoiceOrigin, tousseDeliverOccasion); - //查询待发货申请单的申请项 + //查询待发货申请单的申请项(最后一个%s点位符用于插入时间过滤条件用,无时间过滤条件时,替换为空字符串) String queryTousseItemSql = String.format("select "+ invoicePlanTableAlias +".id invoicePlanId," + invoicePlanTableAlias +".type invoicePlanType," + tousseItemTableAlias +".tousseName," @@ -574,12 +593,17 @@ + "from %s "+ tousseItemTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " - + "where %s and %s and " + shouldSendAmountCaseSql +" > 0 " - + "order by ul.grade desc,"+ invoicePlanTableAlias +".applicationTime", + + "where %s and %s and " + shouldSendAmountCaseSql +" > 0 %s ", TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , - invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction,tousseItemCondition); - - Collection list1 = getWaitDeliverGoodsForNeedReturnInvoicePlanId(queryTousseItemSql); + invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate,tousseItemCondition,"%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format(queryTousseItemSql, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryTousseItemSql, " and " + timeCondition[1] + " order by ul.grade desc,"+ invoicePlanTableAlias +".applicationTime")); + }else{ + queryTousseItemSqlList.add(String.format(queryTousseItemSql, "")); + } + Collection list1 = getWaitDeliverGoodsForNeedReturnInvoicePlanId(StringUtils.join(queryTousseItemSqlList," union ")); if(CollectionUtils.isNotEmpty(list1)){ //1.先将所有的器械包设置是否追溯为是,然后第2步再将不追溯的外部代理灭菌包找出来 for(ApplicationGoodsVo vo : list1){ @@ -618,7 +642,7 @@ //代理灭菌单的待发货物品集合 Collection proxyDisinfectionApplicationGoodsVoList = null; //如果存在需返回申请单id的查询条件时(用于代理灭菌单) - if(StringUtils.isNotBlank(invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection)){ + if(StringUtils.isNotBlank(invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate)){ //申请项的过滤条件 //申请项表的别名 String tousseItemTableAlias = "ti"; @@ -631,7 +655,7 @@ CssdUtils.buildTousseItemShouldDeliverAmountSql(invoicePlanTableAlias, tousseItemTableAlias, invoiceOrigin, tousseDeliverOccasion); - //查询待发货申请单的申请项 + //查询待发货申请单的申请项(最后一个%s点位符用于插入时间过滤条件用,无时间过滤条件时,替换为空字符串) String queryTousseItemSql = String.format("select "+ invoicePlanTableAlias +".id invoicePlanId," + invoicePlanTableAlias +".type invoicePlanType," + tousseItemTableAlias +".tousseName," @@ -643,12 +667,17 @@ + "from %s "+ tousseItemTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " - + "where %s and %s and " + shouldSendAmountCaseSql +" > 0 " - + "order by ul.grade desc,"+ invoicePlanTableAlias +".applicationTime", + + "where %s and %s and " + shouldSendAmountCaseSql +" > 0 %s ", TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , - invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection,tousseItemCondition); - - proxyDisinfectionApplicationGoodsVoList = getWaitDeliverGoodsForNeedReturnInvoicePlanId(queryTousseItemSql); + invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate,tousseItemCondition,"%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format(queryTousseItemSql, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryTousseItemSql, " and " + timeCondition[1] + "order by ul.grade desc,"+ invoicePlanTableAlias +".applicationTime")); + }else{ + queryTousseItemSqlList.add(String.format(queryTousseItemSql, "")); + } + proxyDisinfectionApplicationGoodsVoList = getWaitDeliverGoodsForNeedReturnInvoicePlanId(StringUtils.join(queryTousseItemSqlList," union ")); if(CollectionUtils.isNotEmpty(proxyDisinfectionApplicationGoodsVoList)){ //1.先将所有的器械包设置是否追溯为是,然后第2步再将不追溯的外部代理灭菌包找出来 for(ApplicationGoodsVo vo : proxyDisinfectionApplicationGoodsVoList){ @@ -684,7 +713,7 @@ } //如果存在公共物品类型的查询条件时(分开成消毒物品与(器械包、一次性物品、敷料包)) - if(StringUtils.isNotBlank(invoicePlanCommonGoodsCondition)){ + if(StringUtils.isNotBlank(invoicePlanCommonGoodsConditionExcludingAppDate)){ //申请项的过滤条件 //申请项表的别名 @@ -729,25 +758,38 @@ buildWaitSendInvoicePlanSqlConditionOnlyTousseItem(filterParams, tousseItemTableAlias , invoicePlanTableAlias,invoiceOrigin, tousseDeliverOccasion); - + //聚合的主语句 String queryNotDisinfectTousseItemSql = String.format("select disposableGoodsId,tousseName,tousseType,diposable," - + "max(ul.grade) grade,min("+ invoicePlanTableAlias +".applicationTime) applicationTime," - + "dg.ttsName ,sum"+ tousseItemWaitSendAmountSumSql +" amount," + + "max(grade) grade,min(applicationTime) applicationTime," + + "ttsName ,sum(amount) amount," + + "sum(urgentAmount) urgentAmount, " + + "fullCaseApplicationAmount " + + "from (%s) T " + + "group by disposableGoodsId,tousseName,tousseType,diposable,ttsName, fullCaseApplicationAmount order by max(grade) desc ," + + "(case when tousseType='%s' then 1 else 2 end),min(applicationTime)", "%s" ,TousseItem.TYPE_DIPOSABLE_GOODS); + //查询明细的语句 + String queryNotDisinfectTousseItemSqlDetail = String.format("select disposableGoodsId,tousseName,tousseType,diposable," + + "ul.grade grade,"+ invoicePlanTableAlias +".applicationTime," + + "dg.ttsName ,"+ tousseItemWaitSendAmountSumSql +" amount," //+ "sum(case when ("+ tousseItemTableAlias +".urgentAmount is null or "+ tousseItemTableAlias +".urgentAmount <= "+ tousseItemTableAlias +".sendoutAmount) then 0 else ("+ tousseItemTableAlias +".urgentAmount - "+ tousseItemTableAlias +".sendoutAmount) end) urgentAmount " - + "sum(case when ("+ tousseItemTableAlias +".urgentAmount is null) then 0 else ("+ tousseItemTableAlias +".urgentAmount) end) urgentAmount, " + + "(case when ("+ tousseItemTableAlias +".urgentAmount is null) then 0 else ("+ tousseItemTableAlias +".urgentAmount) end) urgentAmount, " + "dg.fullCaseApplicationAmount " + "from %s "+ tousseItemTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " + "left join %s dg on dg.id="+ tousseItemTableAlias +".disposableGoodsId " - + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 " - + "group by disposableGoodsId,tousseName,tousseType,diposable,dg.ttsName, dg.fullCaseApplicationAmount " - + "order by max(ul.grade) desc ," - + "(case when tousseType='%s' then 1 else 2 end)," - + "min("+ invoicePlanTableAlias +".applicationTime)", + + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 %s ", TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , - DisposableGoods.class.getSimpleName() ,invoicePlanCommonGoodsCondition , - tousseItemCommonGoodsExcludeDisinfectGoodsCondition , TousseItem.TYPE_DIPOSABLE_GOODS); + DisposableGoods.class.getSimpleName() ,invoicePlanCommonGoodsConditionExcludingAppDate , + tousseItemCommonGoodsExcludeDisinfectGoodsCondition ,"%s", "%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format(queryNotDisinfectTousseItemSqlDetail, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryNotDisinfectTousseItemSqlDetail, " and " + timeCondition[1])); + }else{ + queryTousseItemSqlList.add(String.format(queryNotDisinfectTousseItemSqlDetail, "", "")); + } + queryNotDisinfectTousseItemSql = String.format(queryNotDisinfectTousseItemSql, StringUtils.join(queryTousseItemSqlList," union ")); commonGoodsButNotDisinfectGoodsApplicationGoodsVoList = getWaitDeliverGoodsForCommonGoodsButNotDisinfectGoods(queryNotDisinfectTousseItemSql); if(CollectionUtils.isNotEmpty(commonGoodsButNotDisinfectGoodsApplicationGoodsVoList) && enableShowToSendGoodsSourcesInfo){ @@ -758,7 +800,7 @@ + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 ", TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), - invoicePlanCommonGoodsCondition , + invoicePlanCommonGoodsConditionExcludingAppDate , tousseItemCommonGoodsExcludeDisinfectGoodsCondition); queryTousseItemInvoicePlanID(queryNotDisinfectTousseItemInvoicePlanIDSql, commonGoodsButNotDisinfectGoodsApplicationGoodsVoList); }else{ @@ -838,22 +880,39 @@ filterParams.setTousseType(TousseDefinition.PACKAGE_TYPE_DISINFECTION); String tousseItemDisinfectGoodsCondition = buildWaitSendInvoicePlanSqlConditionOnlyTousseItem(filterParams , tousseItemTableAlias , invoicePlanTableAlias,invoiceOrigin,tousseDeliverOccasion); - String queryDisinfectTousseItemSql = String.format("select "+ tousseItemTableAlias +".tousseName," - + tousseItemTableAlias +".tousseType,'%s' diposable,max(ul.grade) grade," + //聚合查询主语句 + String queryDisinfectTousseItemSql = String.format("select tousseName," + + "tousseType,'%s' diposable,max(grade) grade," + + "tdId,materialsMD5,materialAmount,isApplyEntireTousse," + + "min(applicationTime) applicationTime," + + "sum(amount) amount," + + "sum(urgentAmount) urgentAmount " + + "from (%s) T " + + "group by tousseName,tousseType,grade,tdId ,materialsMD5,materialAmount,isApplyEntireTousse " + + "order by grade desc ,min(applicationTime)",Constants.STR_NO,"%s"); + //查询明细的语句，where条件的最后一个%s(group by 之前)点位符用于插入时间过滤条件用,无时间过滤条件时,替换为空字符串 + String queryDisinfectTousseItemSqlDetail = String.format("select "+ tousseItemTableAlias +".tousseName," + + tousseItemTableAlias +".tousseType,ul.grade," + "td.id tdId,td.materialsMD5,td.materialAmount,td.isApplyEntireTousse," - + "min("+invoicePlanTableAlias +".applicationTime) applicationTime," - + "sum"+ tousseItemWaitSendAmountSumSql +" amount," - + "sum(case when ("+ tousseItemTableAlias +".urgentAmount is null or "+ tousseItemTableAlias +".urgentAmount <= "+ tousseItemTableAlias +".sendoutAmount) then 0 else ("+ tousseItemTableAlias +".urgentAmount - "+ tousseItemTableAlias +".sendoutAmount) end) urgentAmount " + + invoicePlanTableAlias +".applicationTime," + + ""+ tousseItemWaitSendAmountSumSql +" amount," + + "(case when ("+ tousseItemTableAlias +".urgentAmount is null or "+ tousseItemTableAlias +".urgentAmount <= "+ tousseItemTableAlias +".sendoutAmount) then 0 else ("+ tousseItemTableAlias +".urgentAmount - "+ tousseItemTableAlias +".sendoutAmount) end) urgentAmount " + "from %s "+ tousseItemTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " + "left join %s td on td.id="+ tousseItemTableAlias +".tousseDefinitionId " - + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 " - + "group by "+ tousseItemTableAlias +".tousseName,"+ tousseItemTableAlias +".tousseType,ul.grade,td.id ,td.materialsMD5,td.materialAmount,td.isApplyEntireTousse " - + "order by ul.grade desc ,min(" + invoicePlanTableAlias +".applicationTime)", Constants.STR_NO, + + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 %s ", TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , - TousseDefinition.class.getSimpleName() ,invoicePlanCommonGoodsCondition , - tousseItemDisinfectGoodsCondition); + TousseDefinition.class.getSimpleName() ,invoicePlanCommonGoodsConditionExcludingAppDate , + tousseItemDisinfectGoodsCondition,"%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format(queryDisinfectTousseItemSqlDetail, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryDisinfectTousseItemSqlDetail, " and " + timeCondition[1])); + }else{ + queryTousseItemSqlList.add(String.format(queryDisinfectTousseItemSqlDetail, "")); + } + queryDisinfectTousseItemSql = String.format(queryDisinfectTousseItemSql, StringUtils.join(queryTousseItemSqlList," union ")); Collection list2 = getWaitDeliverGoodsForDisinfectGoods(queryDisinfectTousseItemSql); if(CollectionUtils.isNotEmpty(list2) && enableShowToSendGoodsSourcesInfo){ @@ -864,7 +923,7 @@ + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 ", TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), - invoicePlanCommonGoodsCondition , + invoicePlanCommonGoodsConditionExcludingAppDate , tousseItemDisinfectGoodsCondition); queryTousseItemInvoicePlanID(queryDisinfectTousseItemInvoicePlanIDSql, list2); }else{ @@ -910,7 +969,8 @@ list.addAll(proxyDisinfectionApplicationGoodsVoList); } } - + System.out.println("==========getWaitDeliverGoods====executeSql=====" + (LocalDateTime.now().getSecond() - (localDateTime.getSecond()))); + localDateTime = LocalDateTime.now(); //设置vo的加急对象及重新排序 if(CollectionUtils.isNotEmpty(list)){ List gradeList = new ArrayList(); @@ -981,9 +1041,12 @@ } } } + //addApplicationGoodsVoStatus_NEW(list, orgUnitCoding); addApplicationGoodsVoStatus(list, orgUnitCoding); result = reorderApplicationGoodsVo(list); } + System.out.println("=====getWaitDeliverGoods=====addApplicationGoodsVoStatus=========" + (LocalDateTime.now().getSecond() - (localDateTime.getSecond()))); + localDateTime = LocalDateTime.now(); //下面的代码为之前的业务逻辑先不用,暂时保留 if(false){ @@ -1111,7 +1174,7 @@ }*/ //最后将未阅读的申请单更新为已读状态 invoicePlanCommonCondition = - buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, applyDate, appFormType ,null , null , invoiceOrigin , tousseDeliverOccasion); + buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, appFormType ,null , null , invoiceOrigin , tousseDeliverOccasion); invoicePlanCommonCondition = String.format("%s and readed = %s", invoicePlanCommonCondition , InvoicePlan.UNREAD); String sql = String.format("update %s set readed = %s where %s", InvoicePlan.class.getSimpleName(), InvoicePlan.READED, invoicePlanCommonCondition); Index: ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/action/InvoicePlanAction.java =================================================================== diff -u -r36880 -r38340 --- ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/action/InvoicePlanAction.java (.../InvoicePlanAction.java) (revision 36880) +++ ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/action/InvoicePlanAction.java (.../InvoicePlanAction.java) (revision 38340) @@ -1058,5 +1058,17 @@ } } } - + + /** + * 获取发货计划模块默认的查询开始日期与日期 + */ + public void getDefaultQueryStartDateAndEndDate(){ + String[] defaultQueryStartDateAndEndDateArray = CssdUtils.getQueryPeroidOfModule(CssdUtils.getQueryPeroidOfModule("invoicePlan")); + JSONObject result = JSONUtil.buildJsonObject(true); + if(defaultQueryStartDateAndEndDateArray != null){ + JSONUtil.addDataProperty(result, JSONArray.fromObject(defaultQueryStartDateAndEndDateArray)); + } + StrutsResponseUtils.output(result); + } + } Index: ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/service/InvoicePlanManagerImpl.java =================================================================== diff -u -r38044 -r38340 --- ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/service/InvoicePlanManagerImpl.java (.../InvoicePlanManagerImpl.java) (revision 38044) +++ ssts-recyclingapplication/src/main/java/com/forgon/disinfectsystem/recyclingapplication/service/InvoicePlanManagerImpl.java (.../InvoicePlanManagerImpl.java) (revision 38340) @@ -4,8 +4,10 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; +import java.time.LocalDateTime; import java.util.ArrayList; import java.util.Arrays; +import java.util.Calendar; import java.util.Collection; import java.util.Collections; import java.util.Comparator; @@ -849,6 +851,12 @@ tousseInstanceHql = foreignTousseHql; } } + //zsyy-377 增加对包实例查询的日期范围限制 + String[] defaultQueryStartDateAndEndDateArray = CssdUtils.getQueryPeroidOfModule(CssdUtils.getQueryPeroidOfModule("invoicePlan_inventoryInquiry")); + if(defaultQueryStartDateAndEndDateArray != null){ + tousseInstanceHql = String.format("%s and %s", tousseInstanceHql, + dateQueryAdapter.dateAreaSql("sterileEndTime", defaultQueryStartDateAndEndDateArray[0], defaultQueryStartDateAndEndDateArray[1] + " 23:59:59")); + } if(StringUtils.isNotEmpty(tousseInstanceHql)) { tousseInstanceHql = String.format(" from %s po where %s ", TousseInstance.class.getSimpleName(), tousseInstanceHql); @@ -948,42 +956,51 @@ /** * 根据申请单id集合及是否代理灭菌单查询其对应的包实例是否有已灭菌状态 - * @param invoicePlanIdList - * @param isProxyDisinfection + * @param invoicePlanIdList 申请单id集合 + * @param isProxyDisinfection 是否代理灭菌 * @return */ - protected Map getDepartTousseInstanceStatusMap(Collection invoicePlanIdList,boolean isProxyDisinfection){ + @Deprecated //过期方法,性能太差,更换为getDepartTousseInstanceStatusMap_NEW方法(加了时间范围限制,先用着吧) + private Map getDepartTousseInstanceStatusMap(Collection invoicePlanIdList,boolean isProxyDisinfection){ Map statusMap = new HashMap(); //非代理灭菌单类型的申请单 String sql = String.format("select ti.departCoding, count(0) from %s ti " + "join %s ip on ti.invoicePlanID = ip.id " + "where (1=1) " - + "and ti.orgUnitCoding='%s' " + + "and ti.orgUnitCoding='%s' %s " + "and %s " + "and ti.status in ('%s','%s') " + "group by ti.departCoding having count(0) > 0", TousseInstance.class.getSimpleName(), InvoicePlan.class.getSimpleName(), - AcegiHelper.getCurrentOrgUnitCode(), + AcegiHelper.getCurrentOrgUnitCode(),"%s", SqlUtils.getNonStringFieldInLargeCollectionsPredicate("ip.id", invoicePlanIdList), TousseInstance.STATUS_STERILED, TousseInstance.STATUS_DISINFECTED); //代理灭菌单类型的申请单 if(isProxyDisinfection){ sql = String.format("select ip.departCoding, count(0) from %s ti " + "join %s ip on ti.proxyDisinfection_id = ip.id " + "where (1=1) " - + "and ip.handleDepartCoding='%s' " + + "and ip.handleDepartCoding='%s' %s " + "and %s " + "and ti.status in ('%s','%s') " + "group by ip.departCoding having count(0) > 0", TousseInstance.class.getSimpleName(), InvoicePlan.class.getSimpleName(), - AcegiHelper.getCurrentOrgUnitCode(), + AcegiHelper.getCurrentOrgUnitCode(),"%s", SqlUtils.getNonStringFieldInLargeCollectionsPredicate("ip.id", invoicePlanIdList), TousseInstance.STATUS_STERILED, TousseInstance.STATUS_DISINFECTED); } ResultSet rs = null; try { + //zsyy-377 增加对包实例查询的日期范围限制 + String[] defaultQueryStartDateAndEndDateArray = CssdUtils.getQueryPeroidOfModule(CssdUtils.getQueryPeroidOfModule("invoicePlan_inventoryInquiry")); + if(defaultQueryStartDateAndEndDateArray != null){ + sql = String.format(sql, + " and " + dateQueryAdapter.dateAreaSql("sterileEndTime", defaultQueryStartDateAndEndDateArray[0], defaultQueryStartDateAndEndDateArray[1] + " 23:59:59")); + }else{ + sql = String.format(sql, ""); + } rs = objectDao.executeSql(sql); while(rs.next()){ String departCoding = rs.getString("departCoding"); @@ -998,6 +1015,62 @@ } /** + * 根据申请单id集合及是否代理灭菌单查询其对应的包实例是否有已灭菌状态(先留着,暂时不用) + * @param invoicePlanIdList 申请单id集合 + * @param isProxyDisinfection 是否代理灭菌 + * @return + */ + private Map getDepartTousseInstanceStatusMap_NEW(Collection invoicePlanIdList,boolean isProxyDisinfection){ + Map statusMap = new HashMap(); + if(CollectionUtils.isEmpty(invoicePlanIdList)){ + return statusMap; + } + //非代理灭菌单类型的申请单 + String sql = String.format("select top 1 ti.departCoding from %s ti " + + "where ti.orgUnitCoding='%s' and ti.invoicePlanID=%s and ti.status in ('%s','%s') ", + TousseInstance.class.getSimpleName(),AcegiHelper.getCurrentOrgUnitCode(),"%s", + TousseInstance.STATUS_STERILED, TousseInstance.STATUS_DISINFECTED); + if(DatabaseUtil.isOracle(dbConnection.getDatabase())){ + sql = String.format("select ti.departCoding from %s ti " + + "where ti.orgUnitCoding='%s' and ti.invoicePlanID=%s and ti.status in ('%s','%s') and rownum <= 1 ", + TousseInstance.class.getSimpleName(),AcegiHelper.getCurrentOrgUnitCode(),"%s", + TousseInstance.STATUS_STERILED, TousseInstance.STATUS_DISINFECTED); + } + //代理灭菌单类型的申请单 + if(isProxyDisinfection){ + sql = String.format("select top 1 ip.departCoding from %s ti " + + "join %s ip on ti.proxyDisinfection_id = ip.id " + + "where ip.handleDepartCoding='%s' and ti.proxyDisinfection_id=%s " + + "and ti.status in ('%s','%s') ", + TousseInstance.class.getSimpleName(),InvoicePlan.class.getSimpleName(), + AcegiHelper.getCurrentOrgUnitCode(),"%s", + TousseInstance.STATUS_STERILED, TousseInstance.STATUS_DISINFECTED); + if(DatabaseUtil.isOracle(dbConnection.getDatabase())){ + sql = String.format("select ip.departCoding from %s ti " + + "join %s ip on ti.proxyDisinfection_id = ip.id " + + "where ip.handleDepartCoding='%s' and ti.proxyDisinfection_id=%s " + + "and ti.status in ('%s','%s') and rownum <= 1 ", + TousseInstance.class.getSimpleName(),InvoicePlan.class.getSimpleName(), + AcegiHelper.getCurrentOrgUnitCode(),"%s", + TousseInstance.STATUS_STERILED, TousseInstance.STATUS_DISINFECTED); + } + } + ResultSet rs = null; + try { + rs = objectDao.executeSql(sql); + while(rs.next()){ + String departCoding = rs.getString("departCoding"); + statusMap.put(departCoding, ApplicationGoodsVo.STATUS_STERILED); + } + } catch (Exception e) { + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(rs); + } + return statusMap; + } + + /** * 获取申请单集合的申请物品包定义map<器械包定义id,器械包定义> * @param invoicePlans 申请单集合 * @param filterParams 是否按过滤参数里的条件过滤，具体包括器械包分组、灭菌程序、器械包种类 @@ -1281,6 +1354,70 @@ return invoicePlans; } + /** + * 根据配置拼接处所需的开始时间与结束时间 + * @return + */ + private String getApplicationDateParamByConfig(){ + Date startDate = null, endDate = null; + int lastDays = CssdUtils.getQueryPeroidOfModule("invoicePlan"); + if(lastDays > 0){ + Calendar cal = Calendar.getInstance(); + cal.add(Calendar.DAY_OF_YEAR, lastDays * -1); + startDate = cal.getTime(); + endDate = new Date(); + return ForgonDateUtils.safelyFormatDate(startDate, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS, "") + + Constants.IDS_SEPARATOR + + ForgonDateUtils.safelyFormatDate(endDate, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS, ""); + } + return null; + } + + /** + * 根据所查询的最近的天数得出申请时间查询范围的条件语句(以and开头) + * @param invoicePlanTableAlias 申请单表的别名 + * @param lastDays 最近的天数 + * @return + */ + protected String getApplicationTimeFilter(String invoicePlanTableAlias,String columnName){ + int lastDays = 60; + return getApplicationTimeFilter(invoicePlanTableAlias, columnName, lastDays); + } + + /** + * 根据所查询的最近的天数得出申请时间查询范围的条件语句(以and开头) + * @param invoicePlanTableAlias 申请单表的别名 + * @param columnName 字段名 + * @param lastDays 最近的天数 + * @return + */ + private String getApplicationTimeFilter(String invoicePlanTableAlias,String columnName,int lastDays){ + Date startDate = null, endDate = null; + if(lastDays > 0){ + Calendar cal = Calendar.getInstance(); + cal.add(Calendar.DAY_OF_YEAR, lastDays * -1); + startDate = cal.getTime(); + endDate = new Date(); + } + return getApplicationTimeFilter(invoicePlanTableAlias, columnName, startDate, endDate); + } + + /** + * 根据开始时间与结束时间得出申请时间查询范围的条件语句(以and开头) + * @param invoicePlanTableAlias 申请单表的别名 + * @param columnName 字段名 + * @param startDate 开始时间 + * @param endDate 结束时间 + * @return + */ + private String getApplicationTimeFilter(String invoicePlanTableAlias,String columnName,Date startDate,Date endDate){ + StringBuffer buffer = new StringBuffer(); + if(startDate != null && endDate != null){ + buffer.append(" and ").append(dateQueryAdapter.dateAreaSql(invoicePlanTableAlias + "." + columnName, ForgonDateUtils.safelyFormatDate(startDate, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS, ""), ForgonDateUtils.safelyFormatDate(endDate, ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMMSS, ""))); + } + return buffer.toString(); + } + @Override public Collection getDepartInvoicePlanVoList(SearchInvoicePlanOrGoodsParams filterParams,HttpServletRequest request) { boolean enableUrgentFunction = CssdUtils.getSystemSetConfigByNameBool("enableUrgentFunction",false); @@ -1446,23 +1583,18 @@ String invoicePlanTableAlias = "po"; //申请单过滤公共条件(按申请单id,申请科室等),用于所有类型场景的申请单过滤条件 String invoicePlanCommonCondition = null; - //外来器械包申请单过滤条件(未装配的查申请项、装配过的查包实例)、还需要查出相关的申请单id - String invoicePlanForeignTousseCondition = null; - //需要绑定申请单的各种物品场景的过滤条件(用于外部代理灭菌包、自定义器械包)、还需要查出相关的申请单id - String invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction = null; - //需要绑定申请单的各种物品场景的过滤条件(用于代理灭菌的包等)、还需要查出相关的申请单id - String invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection = null; - //公共类型的申请单的过滤条件(一次性物品、器械包、敷料包、消毒物品等)、只统计物品不查申请单. - String invoicePlanCommonGoodsCondition = null; + //外来器械包申请单过滤条件(未装配的查申请项、装配过的查包实例)、还需要查出相关的申请单id--不包含时间范围(申请时间或回收时间)的范围(zsyy-377) + String invoicePlanForeignTousseConditionExcludingAppDate = null; + //需要绑定申请单的各种物品场景的过滤条件(用于外部代理灭菌包、自定义器械包)、还需要查出相关的申请单id--不包含时间范围(申请时间或回收时间)的范围(zsyy-377) + String invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate = null; + //需要绑定申请单的各种物品场景的过滤条件(用于代理灭菌的包等)、还需要查出相关的申请单id--不包含时间范围(申请时间或回收时间)的范围(zsyy-377) + String invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate = null; + //公共类型的申请单的过滤条件(一次性物品、器械包、敷料包、消毒物品等)、只统计物品不查申请单.--不包含时间范围(申请时间或回收时间)的范围(zsyy-377) + String invoicePlanCommonGoodsConditionExcludingAppDate = null; String departCodes = filterParams.getDepartCode(); - String applyDate = filterParams.getApplyDate(); String tousseTypes = filterParams.getTousseType(); - String disposableGoodsType = filterParams.getDisposableGoodsType(); - String sterilingMethods = filterParams.getSterilingMethods(); - String tousseGroupIds = filterParams.getTousseGroupIds(); String appFormType = filterParams.getAppFormType(); - String instrumentSetTypeIds = filterParams.getInstrumentSetTypeIds(); List orgUnitCodingList = new ArrayList(); if(StringUtils.isNotBlank(departCodes)){ departCodes = departCodes.replace(";", ","); @@ -1478,6 +1610,8 @@ }else{ return result; } + //ZSYY-377【发货计划】增加默认查询时间周期来优化查询速度 将申请时间和回收时间分开成两个条件,对应两条query语句进行union,再进行分组统计 + String[] timeCondition = returnTimeCondition(filterParams.getApplyDate(), invoicePlanTableAlias + "."); /* * 1.如果appFormType为null,则分开查询外来器械包申请单、需要绑定申请单的各种物品、公共类型的申请单的过滤条件(一次性物品、器械包、敷料包等)、消毒物品的过滤查询等分开查询 @@ -1497,56 +1631,57 @@ }); if(StringUtils.isNotBlank(appFormType)){ if(InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION.equals(appFormType)){ - invoicePlanForeignTousseCondition = String.format("%s and %s", - invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionOnlyForeignTousseApplication(null, - applyDate, invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); + invoicePlanForeignTousseConditionExcludingAppDate = String.format("%s and %s", + invoicePlanCommonCondition, + buildWaitSendInvoicePlanSqlConditionOnlyForeignTousseApplication(null, + invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); }else if(InvoicePlan.TYPE_PROXYDISINFECTION.equals(appFormType) || InvoicePlan.TYPE_FOREIGNPROXYDISINFECTION.equals(appFormType) || InvoicePlan.TYPE_CUSTOM_TOUSSE_APPLIACTION_FORM.equals(appFormType)){ - invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction = String.format("%s and %s", + invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction(null, applyDate, + buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction(null, invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); - invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection = String.format("%s and %s", + invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionForProxyDisinfection(null, applyDate, + buildWaitSendInvoicePlanSqlConditionForProxyDisinfection(null, invoicePlanTableAlias, invoiceOrigin, tousseDeliverOccasion)); }else{ - invoicePlanCommonGoodsCondition = String.format("%s and %s", + invoicePlanCommonGoodsConditionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, applyDate, + buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, appFormType , excludeInvoicePlanTypeList , invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); } }else{ - invoicePlanForeignTousseCondition = String.format("%s and %s", + invoicePlanForeignTousseConditionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, buildWaitSendInvoicePlanSqlConditionOnlyForeignTousseApplication(null, - applyDate, invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); - invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction = String.format("%s and %s", + invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); + invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction(null, applyDate, + buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction(null, invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); - invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection = String.format("%s and %s", + invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionForProxyDisinfection(null, applyDate, + buildWaitSendInvoicePlanSqlConditionForProxyDisinfection(null, invoicePlanTableAlias, invoiceOrigin, tousseDeliverOccasion)); - invoicePlanCommonGoodsCondition = String.format("%s and %s", + invoicePlanCommonGoodsConditionExcludingAppDate = String.format("%s and %s", invoicePlanCommonCondition, - buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, applyDate, + buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(orgUnitCoding, appFormType , excludeInvoicePlanTypeList , invoicePlanTableAlias , invoiceOrigin , tousseDeliverOccasion)); } List tousseTypeList = null; if(StringUtils.isNotBlank(tousseTypes)){ tousseTypeList = Arrays.asList(tousseTypes.split(",")); } + //物品类型是否包含有外来器械包 boolean includeForeignTousseType = false; if(CollectionUtils.isEmpty(tousseTypeList) || tousseTypeList.contains(TousseDefinition.PACKAGE_TYPE_FOREIGN)){ includeForeignTousseType = true; } //1.如果存在外来器械单的查询条件时 - if(StringUtils.isNotBlank(invoicePlanForeignTousseCondition) && includeForeignTousseType){ + if(StringUtils.isNotBlank(invoicePlanForeignTousseConditionExcludingAppDate) && includeForeignTousseType){ //申请项的过滤条件 //申请项表的别名 String tousseItemTableAlias = "ti"; @@ -1577,48 +1712,59 @@ }if(StringUtils.equals(SupplyRoomConfig.TOUSSEDELIVEROCCASION_AFTERRECYCLING, config.getTousseDeliverOccasion()) ){ tousseItemWaitSendAmountSumSql = "(recyclingAmount - sendOutAmount)"; } - - //先查未装配生成包实例的申请单 - String queryTousseItemSql = String.format( + + //1.先查询待发货申请单的申请项 + String queryTousseItemSqlForUnpackingTousseInstance = String.format( "select "+ invoicePlanTableAlias +".id invoicePlanId," - + invoicePlanTableAlias +".departCoding," - + tousseItemTableAlias +".id tousseItemId," - + tousseItemTableAlias +".tousseName," - + tousseItemTableAlias +".tousseType," - + tousseItemTableAlias +".diposable," - + invoicePlanTableAlias +".applicationTime," - + tousseItemWaitSendAmountSumSql +" amount " - + "from %s "+ tousseItemTableAlias +" " - + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " - + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 ", - TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), - tousseItemCondition,String.format("%s and %s", invoicePlanForeignTousseCondition, - String.format(invoicePlanTableAlias +".id not in (select foreignTousseApp_id from %s where foreignTousseApp_id is not null)",TousseInstance.class.getSimpleName()))); + + tousseItemTableAlias +".id tousseItemId," + + tousseItemTableAlias +".tousseName," + + tousseItemTableAlias +".tousseType," + + tousseItemTableAlias +".diposable,ul.grade," + + invoicePlanTableAlias +".applicationTime," + + tousseItemWaitSendAmountSumSql +" amount," + + "(case when ("+ tousseItemTableAlias +".urgentAmount is null or "+ tousseItemTableAlias +".urgentAmount <= "+ tousseItemTableAlias +".sendoutAmount) then 0 else ("+ tousseItemTableAlias +".urgentAmount - "+ tousseItemTableAlias +".sendoutAmount) end) urgentAmount " + + "from %s "+ tousseItemTableAlias +" " + + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " + + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 ", + TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , + tousseItemCondition,String.format("%s and %s", invoicePlanForeignTousseConditionExcludingAppDate, + String.format(invoicePlanTableAlias +".id not in (select foreignTousseApp_id from %s where foreignTousseApp_id is not null)", + TousseInstance.class.getSimpleName()))); - //再查已经装配生成包实例的申请单。由于后面括号里这个问题要求不再把包实例的状态列进条件里,所以不再使用此条件(SZSDSRMYY-46 添加外来器械申请单后，要根据科室供应室设置里的器械包发货时间来决定该发货计划的显示) - queryTousseItemSql += String.format(" union all " - + "select "+ invoicePlanTableAlias +".id invoicePlanId," - + invoicePlanTableAlias +".departCoding," - + "null tousseItemId," + //2.再查已经装配生成包实例的申请单。由于后面括号里这个问题要求不再把包实例的状态列进条件里,所以不再使用此条件(SZSDSRMYY-46 添加外来器械申请单后，要根据科室供应室设置里的器械包发货时间来决定该发货计划的显示) + //倒数第二个%s点位符用于插入时间过滤条件用,无时间过滤条件时,替换为空字符串，最后一个%s为order by,union语句中只有最后一条语句末尾才可以加order by + String queryTousseItemSqlForPackedTousseInstance = String.format("select "+ invoicePlanTableAlias +".id invoicePlanId,null tousseItemId," + tousseInstanceTableAlias +".tousseName," - + "td.tousseType,'%s' diposable," + + "td.tousseType,'%s' diposable,ul.grade," + invoicePlanTableAlias +".applicationTime," - + "1 amount " + + "1 amount," + + "(case when "+ tousseInstanceTableAlias +".urgentLevel_id is null then 0 else 1 end) urgentAmount " + "from %s "+ tousseInstanceTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseInstanceTableAlias +".foreignTousseApp_id="+ invoicePlanTableAlias +".id " + + "left join %s ul on "+ tousseInstanceTableAlias +".urgentLevel_id=ul.id " + "left join %s td on "+ tousseInstanceTableAlias +".tousseDefinition_id=td.id " - + "where %s and %s order by applicationTime", Constants.STR_NO, - TousseInstance.class.getSimpleName() , InvoicePlan.class.getSimpleName(), - TousseDefinition.class.getSimpleName() ,invoicePlanForeignTousseCondition,tousseInstanceCondition); - - Collection list1 = getWaitDeliverGoodsForForeignTousse(queryTousseItemSql); + + "where %s and %s %s", Constants.STR_NO, + TousseInstance.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , + TousseDefinition.class.getSimpleName() ,invoicePlanForeignTousseConditionExcludingAppDate,tousseInstanceCondition,"%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format("%s and %s", queryTousseItemSqlForUnpackingTousseInstance,timeCondition[0])); + queryTousseItemSqlList.add(String.format("%s and %s", queryTousseItemSqlForUnpackingTousseInstance,timeCondition[1])); + queryTousseItemSqlList.add(String.format(queryTousseItemSqlForPackedTousseInstance, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryTousseItemSqlForPackedTousseInstance, " and " + timeCondition[1] + " order by grade desc,applicationTime ")); + }else{ + queryTousseItemSqlList.add(queryTousseItemSqlForUnpackingTousseInstance); + queryTousseItemSqlList.add(String.format(queryTousseItemSqlForPackedTousseInstance, "")); + } + Collection list1 = getWaitDeliverGoodsForForeignTousse(StringUtils.join(queryTousseItemSqlList," union ")); if(CollectionUtils.isNotEmpty(list1)){ list.addAll(list1); } } //如果存在需返回申请单id的查询条件时(用于外部代理灭菌单、自定义器械包申请单) - if(StringUtils.isNotBlank(invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction)){ + if(StringUtils.isNotBlank(invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate)){ //申请项的过滤条件 //申请项表的别名 String tousseItemTableAlias = "ti"; @@ -1631,23 +1777,29 @@ CssdUtils.buildTousseItemShouldDeliverAmountSql(invoicePlanTableAlias, tousseItemTableAlias, invoiceOrigin, tousseDeliverOccasion); - //查询待发货申请单的申请项 + //查询待发货申请单的申请项(最后一个%s点位符用于插入时间过滤条件用,无时间过滤条件时,替换为空字符串) String queryTousseItemSql = String.format("select "+ invoicePlanTableAlias +".id invoicePlanId," - + invoicePlanTableAlias +".departCoding," + invoicePlanTableAlias +".type invoicePlanType," + tousseItemTableAlias +".tousseName," + tousseItemTableAlias +".tousseType," - + tousseItemTableAlias +".diposable," + + tousseItemTableAlias +".diposable,ul.grade," + invoicePlanTableAlias +".applicationTime," - + "(" + shouldSendAmountCaseSql +" - "+ tousseItemTableAlias +".sendoutAmount) amount " + + "(" + shouldSendAmountCaseSql +" - "+ tousseItemTableAlias +".sendoutAmount) amount," + + "(case when ("+ tousseItemTableAlias +".urgentAmount is null or "+ tousseItemTableAlias +".urgentAmount <= "+ tousseItemTableAlias +".sendoutAmount) then 0 else ("+ tousseItemTableAlias +".urgentAmount - "+ tousseItemTableAlias +".sendoutAmount) end) urgentAmount " + "from %s "+ tousseItemTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " - + "where %s and %s and " + shouldSendAmountCaseSql +" > 0 " - + "order by "+ invoicePlanTableAlias +".applicationTime", - TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), - invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliaction,tousseItemCondition); - - Collection list1 = getWaitDeliverGoodsForNeedReturnInvoicePlanId(queryTousseItemSql); + + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " + + "where %s and %s and " + shouldSendAmountCaseSql +" > 0 %s ", + TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , + invoicePlanNeedReturnApplicationIdConditionForForeignProxyAndCustomeTousseAppliactionExcludingAppDate,tousseItemCondition,"%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format(queryTousseItemSql, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryTousseItemSql, " and " + timeCondition[1] + " order by ul.grade desc,"+ invoicePlanTableAlias +".applicationTime")); + }else{ + queryTousseItemSqlList.add(String.format(queryTousseItemSql, "")); + } + Collection list1 = getWaitDeliverGoodsForNeedReturnInvoicePlanId(StringUtils.join(queryTousseItemSqlList," union ")); if(CollectionUtils.isNotEmpty(list1)){ list.addAll(list1); } @@ -1656,7 +1808,7 @@ //代理灭菌单的待发货物品集合 Collection proxyDisinfectionApplicationGoodsVoList = null; //如果存在需返回申请单id的查询条件时(用于代理灭菌单) - if(StringUtils.isNotBlank(invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection)){ + if(StringUtils.isNotBlank(invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate)){ //申请项的过滤条件 //申请项表的别名 String tousseItemTableAlias = "ti"; @@ -1669,27 +1821,33 @@ CssdUtils.buildTousseItemShouldDeliverAmountSql(invoicePlanTableAlias, tousseItemTableAlias, invoiceOrigin, tousseDeliverOccasion); - //查询待发货申请单的申请项 + //查询待发货申请单的申请项(最后一个%s点位符用于插入时间过滤条件用,无时间过滤条件时,替换为空字符串) String queryTousseItemSql = String.format("select "+ invoicePlanTableAlias +".id invoicePlanId," - + invoicePlanTableAlias +".departCoding," + invoicePlanTableAlias +".type invoicePlanType," + tousseItemTableAlias +".tousseName," + tousseItemTableAlias +".tousseType," - + tousseItemTableAlias +".diposable," + + tousseItemTableAlias +".diposable,ul.grade," + invoicePlanTableAlias +".applicationTime," - + "(" + shouldSendAmountCaseSql +" - "+ tousseItemTableAlias +".sendoutAmount) amount " + + "(" + shouldSendAmountCaseSql +" - "+ tousseItemTableAlias +".sendoutAmount) amount," + + "(case when ("+ tousseItemTableAlias +".urgentAmount is null or "+ tousseItemTableAlias +".urgentAmount <= "+ tousseItemTableAlias +".sendoutAmount) then 0 else ("+ tousseItemTableAlias +".urgentAmount - "+ tousseItemTableAlias +".sendoutAmount) end) urgentAmount " + "from %s "+ tousseItemTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " - + "where %s and %s and " + shouldSendAmountCaseSql +" > 0 " - + "order by "+ invoicePlanTableAlias +".applicationTime", - TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), - invoicePlanNeedReturnApplicationIdConditionForProxyDisinfection,tousseItemCondition); - - proxyDisinfectionApplicationGoodsVoList = getWaitDeliverGoodsForNeedReturnInvoicePlanId(queryTousseItemSql); + + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " + + "where %s and %s and " + shouldSendAmountCaseSql +" > 0 %s ", + TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , + invoicePlanNeedReturnApplicationIdConditionForProxyDisinfectionExcludingAppDate,tousseItemCondition,"%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format(queryTousseItemSql, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryTousseItemSql, " and " + timeCondition[1] + "order by ul.grade desc,"+ invoicePlanTableAlias +".applicationTime")); + }else{ + queryTousseItemSqlList.add(String.format(queryTousseItemSql, "")); + } + proxyDisinfectionApplicationGoodsVoList = getWaitDeliverGoodsForNeedReturnInvoicePlanId(StringUtils.join(queryTousseItemSqlList," union ")); } //如果存在公共物品类型的查询条件时(分开成消毒物品与(器械包、一次性物品、敷料包)) - if(StringUtils.isNotBlank(invoicePlanCommonGoodsCondition)){ + if(StringUtils.isNotBlank(invoicePlanCommonGoodsConditionExcludingAppDate)){ //器械包、敷料包、一次性物品的待发货物品查询(仅查询通用申请单、器械包申请单、回收申请单、一次性物品申请单里的器械包、敷料包、一次性物品,不需要返回申请单id) Collection commonGoodsButNotDisinfectGoodsApplicationGoodsVoList = null; //申请项的过滤条件 @@ -1734,21 +1892,38 @@ tousseItemTableAlias , invoicePlanTableAlias,invoiceOrigin, tousseDeliverOccasion); + //聚合的主语句 String queryNotDisinfectTousseItemSql = String.format("select disposableGoodsId,tousseName,tousseType,diposable," - + invoicePlanTableAlias +".departCoding," - + "min("+ invoicePlanTableAlias +".applicationTime) applicationTime," - + "dg.ttsName ,sum"+ tousseItemWaitSendAmountSumSql +" amount " + + "max(grade) grade,min(applicationTime) applicationTime," + + "ttsName ,sum(amount) amount," + + "sum(urgentAmount) urgentAmount, " + + "fullCaseApplicationAmount " + + "from (%s) T " + + "group by disposableGoodsId,tousseName,tousseType,diposable,ttsName, fullCaseApplicationAmount order by max(grade) desc ," + + "(case when tousseType='%s' then 1 else 2 end),min(applicationTime)", "%s" ,TousseItem.TYPE_DIPOSABLE_GOODS); + //查询明细的语句 + String queryNotDisinfectTousseItemSqlDetail = String.format("select disposableGoodsId,tousseName,tousseType,diposable," + + "ul.grade grade,"+ invoicePlanTableAlias +".applicationTime," + + "dg.ttsName ,"+ tousseItemWaitSendAmountSumSql +" amount," + //+ "sum(case when ("+ tousseItemTableAlias +".urgentAmount is null or "+ tousseItemTableAlias +".urgentAmount <= "+ tousseItemTableAlias +".sendoutAmount) then 0 else ("+ tousseItemTableAlias +".urgentAmount - "+ tousseItemTableAlias +".sendoutAmount) end) urgentAmount " + + "(case when ("+ tousseItemTableAlias +".urgentAmount is null) then 0 else ("+ tousseItemTableAlias +".urgentAmount) end) urgentAmount, " + + "dg.fullCaseApplicationAmount " + "from %s "+ tousseItemTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " + "left join %s dg on dg.id="+ tousseItemTableAlias +".disposableGoodsId " - + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 " - + "group by disposableGoodsId,tousseName,tousseType,diposable,dg.ttsName,po.departCoding " - + "order by " - + "(case when tousseType='%s' then 1 else 2 end)," - + "min("+ invoicePlanTableAlias +".applicationTime)", - TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), - DisposableGoods.class.getSimpleName() ,invoicePlanCommonGoodsCondition , - tousseItemCommonGoodsExcludeDisinfectGoodsCondition , TousseItem.TYPE_DIPOSABLE_GOODS); + + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 %s ", + TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , + DisposableGoods.class.getSimpleName() ,invoicePlanCommonGoodsConditionExcludingAppDate , + tousseItemCommonGoodsExcludeDisinfectGoodsCondition ,"%s", "%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format(queryNotDisinfectTousseItemSqlDetail, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryNotDisinfectTousseItemSqlDetail, " and " + timeCondition[1])); + }else{ + queryTousseItemSqlList.add(String.format(queryNotDisinfectTousseItemSqlDetail, "", "")); + } + queryNotDisinfectTousseItemSql = String.format(queryNotDisinfectTousseItemSql, StringUtils.join(queryTousseItemSqlList," union ")); commonGoodsButNotDisinfectGoodsApplicationGoodsVoList = getWaitDeliverGoodsForCommonGoodsButNotDisinfectGoods(queryNotDisinfectTousseItemSql); //1.常规物品不为空但代理灭菌单物品为空时 if(CollectionUtils.isNotEmpty(commonGoodsButNotDisinfectGoodsApplicationGoodsVoList) && CollectionUtils.isEmpty(proxyDisinfectionApplicationGoodsVoList)){ @@ -1819,21 +1994,39 @@ filterParams.setTousseType(TousseDefinition.PACKAGE_TYPE_DISINFECTION); String tousseItemDisinfectGoodsCondition = buildWaitSendInvoicePlanSqlConditionOnlyTousseItem(filterParams , tousseItemTableAlias , invoicePlanTableAlias,invoiceOrigin,tousseDeliverOccasion); - String queryDisinfectTousseItemSql = String.format("select "+ tousseItemTableAlias +".tousseName," - + tousseItemTableAlias +".tousseType,'%s' diposable," - + invoicePlanTableAlias +".departCoding," + //聚合查询主语句 + String queryDisinfectTousseItemSql = String.format("select tousseName," + + "tousseType,'%s' diposable,max(grade) grade," + + "tdId,materialsMD5,materialAmount,isApplyEntireTousse," + + "min(applicationTime) applicationTime," + + "sum(amount) amount," + + "sum(urgentAmount) urgentAmount " + + "from (%s) T " + + "group by tousseName,tousseType,grade,tdId ,materialsMD5,materialAmount,isApplyEntireTousse " + + "order by grade desc ,min(applicationTime)",Constants.STR_NO,"%s"); + //查询明细的语句，where条件的最后一个%s(group by 之前)点位符用于插入时间过滤条件用,无时间过滤条件时,替换为空字符串 + String queryDisinfectTousseItemSqlDetail = String.format("select "+ tousseItemTableAlias +".tousseName," + + tousseItemTableAlias +".tousseType,ul.grade," + "td.id tdId,td.materialsMD5,td.materialAmount,td.isApplyEntireTousse," - + "min("+invoicePlanTableAlias +".applicationTime) applicationTime," - + "sum"+ tousseItemWaitSendAmountSumSql +" amount " + + invoicePlanTableAlias +".applicationTime," + + ""+ tousseItemWaitSendAmountSumSql +" amount," + + "(case when ("+ tousseItemTableAlias +".urgentAmount is null or "+ tousseItemTableAlias +".urgentAmount <= "+ tousseItemTableAlias +".sendoutAmount) then 0 else ("+ tousseItemTableAlias +".urgentAmount - "+ tousseItemTableAlias +".sendoutAmount) end) urgentAmount " + "from %s "+ tousseItemTableAlias +" " + "join %s "+ invoicePlanTableAlias +" on "+ tousseItemTableAlias +".recyclingApplication_ID="+ invoicePlanTableAlias +".id " + + "left join %s ul on "+ tousseItemTableAlias +".urgentLevel_id=ul.id " + "left join %s td on td.id="+ tousseItemTableAlias +".tousseDefinitionId " - + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 " - + "group by "+ tousseItemTableAlias +".tousseName,"+ tousseItemTableAlias +".tousseType, td.id ,td.materialsMD5,td.materialAmount,td.isApplyEntireTousse,po.departCoding " - + "order by min(" + invoicePlanTableAlias +".applicationTime)", Constants.STR_NO, - TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), - TousseDefinition.class.getSimpleName() ,invoicePlanCommonGoodsCondition , - tousseItemDisinfectGoodsCondition); + + "where %s and %s and " + tousseItemWaitSendAmountSumSql +" > 0 %s ", + TousseItem.class.getSimpleName() , InvoicePlan.class.getSimpleName(), UrgentLevel.class.getSimpleName() , + TousseDefinition.class.getSimpleName() ,invoicePlanCommonGoodsConditionExcludingAppDate , + tousseItemDisinfectGoodsCondition,"%s"); + List queryTousseItemSqlList = new ArrayList(); + if(timeCondition != null){ + queryTousseItemSqlList.add(String.format(queryDisinfectTousseItemSqlDetail, " and " + timeCondition[0])); + queryTousseItemSqlList.add(String.format(queryDisinfectTousseItemSqlDetail, " and " + timeCondition[1])); + }else{ + queryTousseItemSqlList.add(String.format(queryDisinfectTousseItemSqlDetail, "")); + } + queryDisinfectTousseItemSql = String.format(queryDisinfectTousseItemSql, StringUtils.join(queryTousseItemSqlList," union ")); Collection list2 = getWaitDeliverGoodsForDisinfectGoods(queryDisinfectTousseItemSql); if(CollectionUtils.isNotEmpty(list2)){ list.addAll(list2); @@ -1889,6 +2082,7 @@ for (String departCode : departToGoodsMap.keySet()) { List voList = departToGoodsMap.get(departCode); if(CollectionUtils.isNotEmpty(voList)){ + //addApplicationGoodsVoStatus_NEW(voList, departCode); addApplicationGoodsVoStatus(voList, departCode); for (ApplicationGoodsVo applicationGoodsVo : voList) { if(StringUtils.equals(applicationGoodsVo.getStatus(), ApplicationGoodsVo.STATUS_STERILED)){ @@ -1917,10 +2111,10 @@ * @return String 示例如下: 条件1 and 条件2 。。。 */ protected String buildWaitSendInvoicePlanSqlConditionOnlyForeignTousseApplication( - String departCodes, String applyDate,String alias, + String departCodes, String alias, String invoiceOrigin , String tousseDeliverOccasion){ String commonCondition = buildWaitSendInvoicePlanCommonSqlCondition( - departCodes, applyDate, alias, invoiceOrigin , tousseDeliverOccasion); + departCodes, alias, invoiceOrigin , tousseDeliverOccasion); String tableColumnAliasPre = (StringUtils.isNotBlank(alias)) ? alias + "." : ""; //过滤相关的单类型 String condition = String.format("%s and %s='%s'",commonCondition, @@ -1931,13 +2125,13 @@ /** * 构造发货计划单的通用过滤条件 * @param departCodes 科室编码 - * @param applyDate 申请日期 * @param alias 表的别名(防止多个表是同名字段时语句报错) + * @param invoiceOrigin 发货数量依据 + * @param tousseDeliverOccasion 发货时机 * @return String 示例如下: 条件1 and 条件2 。。。 */ protected String buildWaitSendInvoicePlanCommonSqlCondition( - String departCodes, String applyDate,String alias, - String invoiceOrigin , String tousseDeliverOccasion){ + String departCodes, String alias, String invoiceOrigin , String tousseDeliverOccasion){ //表名的字段的前缀 String tableColumnAliasPre = (StringUtils.isNotBlank(alias)) ? alias + "." : ""; List departCodeList = null; @@ -1952,8 +2146,8 @@ where1 = SqlUtils.getStringFieldInLargeCollectionsPredicate(tableColumnAliasPre + groupBy, departCodeList); } - //条件2.根据申请日期过滤 如果申请时间范围不为空时,参数:applyDate - String where2 = "1=1"; + //条件2.根据申请日期过滤 如果申请时间范围不为空时,参数:applyDate -- ZSYY-377改造后,此时间过滤条件暂时去掉,放到在上一层进行处理 + /*String where2 = "1=1"; if (StringUtils.isNotBlank(applyDate)) { try { where2 = this.setTimeCondition(applyDate,tableColumnAliasPre); @@ -1962,7 +2156,7 @@ logger.error(e, e); throw new RuntimeException(e); } - } + }*/ //条件3.根据发货单状态过滤 String where3 = String.format( @@ -1977,23 +2171,22 @@ String where5 = tableColumnAliasPre +"handleDepartCoding = '" + userDeptCode + "'"; String condition = String - .format(" (%s) and (%s) and (%s) and (%s) and (%s)", - where1, where2, where3, where4, where5); + .format(" (%s) and (%s) and (%s) and (%s)", + where1, where3, where4, where5); return condition; } /** * 构造发货计划单的过滤条件(用于外部代理灭菌单、自定义器械申请单需要返回该单的id的情形),无where开头 * @param departCodes 科室编码 - * @param applyDate 申请日期 * @param alias 表的别名(防止多个表是同名字段时语句报错) * @return String 示例如下: 条件1 and 条件2 。。。 */ protected String buildWaitSendInvoicePlanSqlConditionForForeignProxyAndCustomeTousseAppliaction( - String departCodes, String applyDate,String alias, + String departCodes, String alias, String invoiceOrigin , String tousseDeliverOccasion){ String commonCondition = buildWaitSendInvoicePlanCommonSqlCondition( - departCodes, applyDate, alias, invoiceOrigin , tousseDeliverOccasion); + departCodes, alias, invoiceOrigin , tousseDeliverOccasion); String tableColumnAliasPre = (StringUtils.isNotBlank(alias)) ? alias + "." : ""; //条件8.过滤相关的单类型 String condition = String.format("%s and %s",commonCondition, @@ -2010,10 +2203,10 @@ * @return String 示例如下: 条件1 and 条件2 。。。 */ protected String buildWaitSendInvoicePlanSqlConditionForProxyDisinfection( - String departCodes, String applyDate,String alias, + String departCodes, String alias, String invoiceOrigin , String tousseDeliverOccasion){ String commonCondition = buildWaitSendInvoicePlanCommonSqlCondition( - departCodes, applyDate, alias, invoiceOrigin , tousseDeliverOccasion); + departCodes, alias, invoiceOrigin , tousseDeliverOccasion); String tableColumnAliasPre = (StringUtils.isNotBlank(alias)) ? alias + "." : ""; //条件8.过滤相关的单类型 String condition = String.format("%s and %s",commonCondition, tableColumnAliasPre +"type = '"+ InvoicePlan.TYPE_PROXYDISINFECTION +"'"); @@ -2023,14 +2216,13 @@ /** * 构造发货计划单的过滤条件(仅申请单层面相关,不包含申请项相关的过滤),无where开头 * @param departCodes 科室编码 - * @param applyDate 申请日期 * @param appFormType 申请单类型 * @param excludeInvoicePlanTypeList 要排除的申请单类型集合 * @param alias 表的别名(防止多个表是同名字段时语句报错) * @return String 示例如下: 条件1 and 条件2 。。。 */ protected String buildWaitSendInvoicePlanSqlConditionOnlyInvoicePlan(String departCodes, - String applyDate,String appFormType,List excludeInvoicePlanTypeList, + String appFormType,List excludeInvoicePlanTypeList, String alias,String invoiceOrigin , String tousseDeliverOccasion){ //表2的字段的前缀 String tableColumnAliasPre = (StringUtils.isNotBlank(alias)) ? alias + "." : ""; @@ -2046,8 +2238,8 @@ where1 = SqlUtils.getStringFieldInLargeCollectionsPredicate(tableColumnAliasPre + groupBy, departCodeList); } - //条件2.根据申请日期过滤 如果申请时间范围不为空时,参数:applyDate - String where2 = "1=1"; + //条件2.根据申请日期过滤 如果申请时间范围不为空时,参数:applyDate -- ZSYY-377改造后,此时间过滤条件暂时去掉,放到在上一层进行处理 + /*String where2 = "1=1"; if (StringUtils.isNotBlank(applyDate)) { try { where2 = this.setTimeCondition(applyDate,tableColumnAliasPre); @@ -2056,7 +2248,7 @@ logger.error(e, e); throw new RuntimeException(e); } - } + }*/ //条件3.根据申请单类型过滤,参数:appFormType String where3 = "1=1"; @@ -2145,8 +2337,8 @@ } String condition = String - .format(" (%s) and (%s) and (%s) and (%s) and (%s) and (%s) and (%s) and (%s)", - where1, where2, where3, where5, where6, where7, where8,second_supply_room_sql); + .format(" (%s) and (%s) and (%s) and (%s) and (%s) and (%s) and (%s)", + where1, where3, where5, where6, where7, where8,second_supply_room_sql); return condition; } @@ -2820,7 +3012,13 @@ } return result; } - + + /** + * 性能太差-被addApplicationGoodsVoStatus_NEW方法替代(先用着,加了时间范围过滤) + * @param list + * @param departCoding + */ + @Deprecated protected void addApplicationGoodsVoStatus( Collection list, String departCoding) { if(CollectionUtils.isNotEmpty(list)){ @@ -2835,17 +3033,29 @@ + "join %s td on td.id = ti.tousseDefinition_id " + "where ti.orgUnitCoding='%s' and %s " + "and ti.status in ('%s','%s') " + + "%s"//用于时间范围限制条件的点位符 + "and ( td.limitInvoiceDepart is null or td.limitInvoiceDepart <> '%s' " + "or (td.limitInvoiceDepart = '%s' and (ti.departCoding is null or ti.departCoding = '' or ti.departCoding = '%s'))) " + "group by ti.tousseName having count(0) > 0", TousseInstance.class.getSimpleName(),TousseDefinition.class.getSimpleName(),AcegiHelper.getCurrentOrgUnitCode(), SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.tousseName", tousseNameList), - TousseInstance.STATUS_STERILED,TousseInstance.STATUS_DISINFECTED, + TousseInstance.STATUS_STERILED,TousseInstance.STATUS_DISINFECTED, "%s", Constants.STR_YES, Constants.STR_YES, departCoding == null ? "" : departCoding ); try { + //zsyy-377 增加对包实例查询的日期范围限制 + String[] defaultQueryStartDateAndEndDateArray = CssdUtils.getQueryPeroidOfModule(CssdUtils.getQueryPeroidOfModule("invoicePlan_inventoryInquiry")); + if(defaultQueryStartDateAndEndDateArray != null){ + querySql = String.format(querySql, + " and " + dateQueryAdapter.dateAreaSql("sterileEndTime", defaultQueryStartDateAndEndDateArray[0], defaultQueryStartDateAndEndDateArray[1] + " 23:59:59")); + }else{ + querySql = String.format(querySql, ""); + } + logger.info("*****addApplicationGoodsVoStatus******querySql===" + querySql); + long start = System.currentTimeMillis(); rs = objectDao.executeSql(querySql); + logger.info("*****addApplicationGoodsVoStatus******querySql查询完成，耗时(毫秒)===" + (System.currentTimeMillis() - start)); while(rs.next()){ tousseNameToSteriledAmountMap.put(rs.getString("tousseName"), ((Number)rs.getInt("cnt")).intValue()); @@ -2869,6 +3079,79 @@ } } } + + /** + * 性能优化-ZSyy-377,替代addApplicationGoodsVoStatus方法(包太多时性能也一般,暂时先不用,留着) + * @param list + * @param departCoding + */ + protected void addApplicationGoodsVoStatus_NEW( + Collection list, String departCoding) { + System.out.println("==========addApplicationGoodsVoStatus_NEW=========start==========="); + LocalDateTime localDateTime = LocalDateTime.now(); + if(CollectionUtils.isNotEmpty(list)){ + List tousseNameList = list.stream().filter(vo -> { + return !StringUtils.equals(DisposableGoods.TYPE_NAME, vo.getGoodsType()); + }).map(ApplicationGoodsVo::getName).collect(Collectors.toList()); + //可查找到库存的包名称list + List tousseNameToSteriledAmountList = new ArrayList(); + if(CollectionUtils.isNotEmpty(tousseNameList)){ + ResultSet rs = null; + String querySql = String.format("select top 1 ti.tousseName from %s ti " + + "join %s td on td.id = ti.tousseDefinition_id " + + "where ti.orgUnitCoding='%s' and ti.tousseName='%s' " + + "and ti.status in ('%s','%s') " + + "and ( td.limitInvoiceDepart is null or td.limitInvoiceDepart <> '%s' " + + "or (td.limitInvoiceDepart = '%s' and (ti.departCoding is null or ti.departCoding = '' or ti.departCoding = '%s'))) ", + TousseInstance.class.getSimpleName(),TousseDefinition.class.getSimpleName(),AcegiHelper.getCurrentOrgUnitCode(), + "%s",TousseInstance.STATUS_STERILED,TousseInstance.STATUS_DISINFECTED, + Constants.STR_YES, Constants.STR_YES, departCoding == null ? "" : departCoding + ); + if(DatabaseUtil.isOracle(dbConnection.getDatabase())){ + querySql = String.format("select ti.tousseName from %s ti " + + "join %s td on td.id = ti.tousseDefinition_id " + + "where ti.orgUnitCoding='%s' and ti.tousseName='%s' " + + "and ti.status in ('%s','%s') " + + "and ( td.limitInvoiceDepart is null or td.limitInvoiceDepart <> '%s' " + + "or (td.limitInvoiceDepart = '%s' and (ti.departCoding is null or ti.departCoding = '' or ti.departCoding = '%s'))) and rownum <= 1 ", + TousseInstance.class.getSimpleName(),TousseDefinition.class.getSimpleName(),AcegiHelper.getCurrentOrgUnitCode(), + "%s",TousseInstance.STATUS_STERILED,TousseInstance.STATUS_DISINFECTED, + Constants.STR_YES, Constants.STR_YES, departCoding == null ? "" : departCoding + ); + } + List querySqlList = new ArrayList(); + for(String tousseName : tousseNameList){ + querySqlList.add(String.format(querySql, tousseName)); + } + try { + String executeSql = StringUtils.join(querySqlList," union "); + System.out.println("******************addApplicationGoodsVoStatus_NEW executeSql=" + executeSql); + rs = objectDao.executeSql(executeSql); + while(rs.next()){ + tousseNameToSteriledAmountList.add(rs.getString("tousseName")); + } + } catch (Exception e) { + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + for (ApplicationGoodsVo vo : list) { + if(StringUtils.equals(vo.getGoodsType(), DisposableGoods.TYPE_NAME)){ + vo.setStatus(ApplicationGoodsVo.STATUS_STERILED); + }else{ + if(tousseNameToSteriledAmountList.contains(vo.getName())){ + vo.setStatus(ApplicationGoodsVo.STATUS_STERILED); + }else{ + vo.setStatus(ApplicationGoodsVo.STATUS_UNSTERILED); + } + } + } + System.out.println("==========addApplicationGoodsVoStatus_NEW=========" + (LocalDateTime.now().getSecond() - (localDateTime.getSecond()))); + localDateTime = LocalDateTime.now(); + } + } + @Override public Collection getExpensiveGoodsInvoicePlanVOs( @@ -4916,6 +5199,15 @@ }catch (Exception e) { Log.error("", e); }*/ + }else{ + //申请时间为空时,取限定配置的时间范围(针对zsyy-377的优化,暂不处理) + /*applyDate = getApplicationDateParamByConfig(); + try { + where7 = this.setTimeCondition(applyDate, returnSql); + } + catch (Exception e) { + logger.error(e, e); + }*/ } //如果器械包分组、物品类型、器械包种类三个条件其中一个不为时 @@ -5068,6 +5360,7 @@ * @param tableColumnAliasPre 字段的表别名前缀,包含. * @return */ + @Deprecated //本方面已不再使用,换成下面的方法：returnTimeCondition protected String setTimeCondition(String applyDate,String tableColumnAliasPre){ SimpleDateFormat sdf = new SimpleDateFormat(DateTools.COMMON_DATE_HMS); try { @@ -5102,6 +5395,49 @@ } } + /** + * 根据申请时间返回对应的条件sql,替换上面的setTimeCondition方法 + * @param applyDate 申请时间时间,格式为"开始时间;结束时间" + * @param tableColumnAliasPre 申请单表别名前缀,包含. + * @return + */ + protected String[] returnTimeCondition(String applyDate,String tableColumnAliasPre){ + String[] timeCondition = null; + if(StringUtils.isNotBlank(applyDate)){ + try { + SimpleDateFormat sdf = new SimpleDateFormat(DateTools.COMMON_DATE_HMS); + tableColumnAliasPre = StringUtils.isEmpty(tableColumnAliasPre) ? "" : tableColumnAliasPre; + String[] applyDateArray = StringUtils.split(applyDate, Constants.IDS_SEPARATOR); + //如果convertTime方法转换出错,尝试重新再转换(花都区人民医院要求:发货时间精确到上午和下午的时分,而之前默认的方法是精确到时分秒,所以要尝试再次转换) + //Date out_beginTime = DateTools.convertTime(applyDateArray[0] + " 00:00:00", sdf, DateTools.COMMON_DATE_HM); + Date out_beginTime = ForgonDateUtils.safelyParseDate(applyDateArray[0]); + String timeSearchFmt = CssdUtils.getSystemSetConfigByName("timeSearchFmt"); + DateStrategyContext dateStrategyContext = new DateStrategyContext(); + dateStrategyContext.setDateState(timeSearchFmt); + Date out_endTime = dateStrategyContext.handleTodayEndOfTime(applyDateArray[1], sdf, DateTools.COMMON_DATE_HM); + + //如果配置按申请时间过滤,且有传时间范围参数时,则只按申请时间过滤(NFYY-59) + if(InvoiceUtils.willFilterInvoicePlanByApplicationTime(applyDate)){ + timeCondition = new String[1]; + timeCondition[0] = String.format("("+ tableColumnAliasPre +"applicationTime between %s and %s)", + dateQueryAdapter.dateAdapter(out_beginTime),dateQueryAdapter.dateAdapter(out_endTime)); + }else{ + timeCondition = new String[2]; + timeCondition[0] = String.format("("+ tableColumnAliasPre +"applicationTime between %s and %s)", + dateQueryAdapter.dateAdapter(out_beginTime),dateQueryAdapter.dateAdapter(out_endTime)); + timeCondition[1] = String.format("("+ tableColumnAliasPre +"id in (select recyclingApplication_id from %s where recyclingTime between %s and %s))", + RecyclingRecord.class.getSimpleName(),dateQueryAdapter.dateAdapter(out_beginTime),dateQueryAdapter.dateAdapter(out_endTime)); + } + return timeCondition; + } + catch (Exception e) { + e.printStackTrace(); + logger.error(e); + throw new RuntimeException(e); + } + } + return null; + } /** * 根据时间段和多个orgUnitCoding获取未发货的发货计划单 @@ -5135,7 +5471,7 @@ } String sql = buildSqlForGetInvoicePlanByOrgUnitCodingsAndTousseType(departCodeList, filterParams, null); - String hql = String.format("select po from %s po %s ORDER BY applicationTime", InvoicePlan.class.getSimpleName(),sql); + String hql = String.format("select po from %s po %s ORDER BY applicationTime", InvoicePlan.class.getSimpleName(),sql); if(!needQueryTousseItem){ return objectDao.findByHql(hql); } Index: ssts-webservice/src/main/java/com/forgon/disinfectsystem/webservice/service/ServiceManagerImpl.java =================================================================== diff -u -r38279 -r38340 --- ssts-webservice/src/main/java/com/forgon/disinfectsystem/webservice/service/ServiceManagerImpl.java (.../ServiceManagerImpl.java) (revision 38279) +++ ssts-webservice/src/main/java/com/forgon/disinfectsystem/webservice/service/ServiceManagerImpl.java (.../ServiceManagerImpl.java) (revision 38340) @@ -639,6 +639,11 @@ if(StringUtils.isNotBlank(JavaWebSocketServer.javaWebsocketPort)){ jsonBuilder.key("javaWebsocketPort").value(JavaWebSocketServer.javaWebsocketPort); } + //ZSYY-377 【发货计划】增加默认查询时间周期来优化查询速度 + String[] defaultQueryStartDateAndEndDateArray = CssdUtils.getQueryPeroidOfModule(CssdUtils.getQueryPeroidOfModule("invoicePlan")); + if(defaultQueryStartDateAndEndDateArray != null){ + jsonBuilder.key("defaultQueryStartDateAndEndDateArray").value(JSONArray.fromObject(defaultQueryStartDateAndEndDateArray)); + } jsonBuilder.endObject(); return jsonBuilder.toString(); }