Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java =================================================================== diff -u -r38737 -r38741 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java (.../RealTimeBulletinBoardWorkloadHelper.java) (revision 38737) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java (.../RealTimeBulletinBoardWorkloadHelper.java) (revision 38741) @@ -2,6 +2,7 @@ import java.sql.ResultSet; import java.sql.SQLException; +import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; @@ -44,6 +45,7 @@ import com.forgon.disinfectsystem.entity.urgent.UrgentLevel; import com.forgon.disinfectsystem.entity.washanddisinfectmanager.washanddisinfectrecord.WashAndDisinfectRecord; import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; +import com.forgon.tools.date.DateTools; import com.forgon.tools.db.DatabaseUtil; import com.forgon.tools.util.SqlUtils; /** @@ -61,7 +63,7 @@ public JSONObject getRealTimeBulletinBoardWorkloadData() { //获取当天开始时间 SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); - SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); + DateFormat sdf = DateTools.YMDFORMAT.get(); Date today = new Date(); String dateStr = sdf.format(today); String startDate = ""; @@ -89,6 +91,7 @@ params.querySupplyRoom = handleDepartCoding; String sql = ""; String toBeBetweenSql = ""; + String dateType = null;//时间过滤类型 Integer dashboardsQueryCycle = null; if(systemParamsObj.getDashboardsQueryCycle() == null){ dashboardsQueryCycle = 7; @@ -141,7 +144,7 @@ sql += " union all " + inventoryEdAmountSql; //外来器械当天待清洗器械总包数 String foreignToBeWashAmountSql = " select 'foreignTobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeWashAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql, true) + ") tb "; + + getToBeWashAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql, true,dateType) + ") tb "; sql += " union all " + foreignToBeWashAmountSql; //外来器械当天已清洗器械总包数 foreignParams.extraQuery = " and ci.isSencondWashForForeignTousse=0 "; @@ -152,7 +155,7 @@ foreignParams.extraQuery = ""; //当天待清洗器械总包数 String toBeWashAmountSql = " select 'tobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeWashAmountSql(handleDepartCoding, "", toBeBetweenSql, false) + ") tb "; + + getToBeWashAmountSql(handleDepartCoding, "", toBeBetweenSql, false,dateType) + ") tb "; sql += " union all " + toBeWashAmountSql; //当天已清洗器械总包数 String washedAmountSql = String.format("select 'washedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -161,7 +164,7 @@ sql += " union all " + washedAmountSql; //外来器械当天待装配的总包数 String foreignToBePackAmountSql = " select 'foreignToBePackAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBePackAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb "; + + getToBePackAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql,dateType) + ") tb "; sql += " union all " + foreignToBePackAmountSql; //外来器械当天已装配总包数 String foreignPackedAmountSql = String.format("select 'foreignPackedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -170,7 +173,7 @@ sql += " union all " + foreignPackedAmountSql; //当天待装配的总包数 String toBePackAmountSql = " select 'toBePackAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBePackAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb "; + + getToBePackAmountSql(handleDepartCoding, "", toBeBetweenSql,dateType) + ") tb "; sql += " union all " + toBePackAmountSql; //当天已装配总包数 String packedAmountSql = String.format("select 'packedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -184,7 +187,7 @@ sql += " union all " + reviewedAmountSql; //外来器械当天待灭菌的总包数 String foreignToBeSterilizationAmountSql = " select 'foreignToBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeSterilizationAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb "; + + getToBeSterilizationAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql,dateType) + ") tb "; sql += " union all " + foreignToBeSterilizationAmountSql; //外来器械当天已灭菌的总包数 String foreignSterilizationedAmountSql = String.format("select 'foreignSterilizationedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -193,7 +196,7 @@ sql += " union all " + foreignSterilizationedAmountSql; //当天待灭菌的总包数 String toBeSterilizationAmountSql = " select 'toBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeSterilizationAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb "; + + getToBeSterilizationAmountSql(handleDepartCoding, "", toBeBetweenSql,dateType) + ") tb "; sql += " union all " + toBeSterilizationAmountSql; //当天已灭菌的总包数 String sterilizationedAmountSql = String.format("select 'sterilizationedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -202,7 +205,7 @@ sql += " union all " + sterilizationedAmountSql; //外来器械当天待发货的总包数 String foreignToBeInvoiceAmountSql = " select 'foreignToBeInvoiceAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeInvoiceAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb "; + + getToBeInvoiceAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql,dateType) + ") tb "; sql += " union all " + foreignToBeInvoiceAmountSql; //外来器械当天已发货的总包数 String foreignInvoicedAmountSql = String.format("select 'foreignInvoicedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -211,7 +214,7 @@ sql += " union all " + foreignInvoicedAmountSql; //当天待发货的总包数 String toBeInvoiceAmountSql = " select 'toBeInvoiceAmount' type,sum(amount) amount,sum(urgentAmount) urgentAmount from ( " - + getToBeInvoiceAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb "; + + getToBeInvoiceAmountSql(handleDepartCoding, "", toBeBetweenSql,dateType) + ") tb "; sql += " union all " + toBeInvoiceAmountSql; //当天已发货的总包数 String invoicedAmountSql = String.format("select 'invoicedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -220,7 +223,7 @@ sql += " union all " + invoicedAmountSql; //外来器械当天待签收的包数 String foreignToBeSignAmountSql = " select 'foreignToBeSignAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeSignAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb "; + + getToBeSignAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql,dateType) + ") tb "; sql += " union all " + foreignToBeSignAmountSql; //外来器械当天已签收的包数 String foreignSignedAmountSql = String.format("select 'foreignSignedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -317,9 +320,19 @@ * @param handleDepartCoding 一级供应室编码 * @param tousseTypesql 包类型过滤 * @param toBeBetweenSql 时间过滤 + * @param dateType 时间过滤的类型 * @return */ - private String getToBeSignAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){ + private String getToBeSignAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql,String dateType){ + String betweenSql = null; + if("applicationTime".equals(dateType)){ + betweenSql = " and exists (select 1 from invoicePlan ip where ip.id=td.invoicePlanID and " + + "ip.applicationTime " + + toBeBetweenSql + +") "; + }else{ + betweenSql = SqlUtils.buildFieldNameBetweenSql("i.sendTime", toBeBetweenSql); + } String sql = " select 1 amount from " + TousseInstance.class.getSimpleName() +" ti join " @@ -330,7 +343,7 @@ + "where ti.status='" + TousseInstance.STATUS_SHIPPED +"' " - + SqlUtils.buildFieldNameBetweenSql("i.sendTime", toBeBetweenSql) + + betweenSql + tousseTypesql; return sql; } @@ -339,16 +352,25 @@ * @param handleDepartCoding 一级供应室编码 * @param tousseTypesql 包类型过滤 * @param toBeBetweenSql 时间过滤 + * @param dateType 时间过滤的类型 * @return */ - private String getToBeWashAmountSql(String handleDepartCoding, String tousseTypeSql, String toBeBetweenSql, boolean excludeSecondaryData){ + private String getToBeWashAmountSql(String handleDepartCoding, String tousseTypeSql, String toBeBetweenSql, boolean excludeSecondaryData, String dateType){ String excludeSecondaryDataSql = null; if(excludeSecondaryData){ excludeSecondaryDataSql = " and rr.recyclingTimes is null "; }else{ excludeSecondaryDataSql = ""; } - String betweenSql = SqlUtils.buildFieldNameBetweenSql("rr.recyclingTime", toBeBetweenSql); + String betweenSql = null; + if("applicationTime".equals(dateType)){ + betweenSql = " and exists (select 1 from invoicePlan ip where ip.id=td.invoicePlanID and " + + "ip.applicationTime " + + toBeBetweenSql + +") "; + }else{ + betweenSql = SqlUtils.buildFieldNameBetweenSql("rr.recyclingTime", toBeBetweenSql); + } String sql = " select min(ci.tousseAmountForMaterial) amount from " + ClassifyBasket.class.getSimpleName() +" cb," @@ -426,9 +448,19 @@ * @param handleDepartCoding 一级供应室编码 * @param tousseTypesql 包类型过滤 * @param toBeBetweenSql 时间过滤 + * @param dateType 时间过滤的类型 * @return */ - private String getToBeInvoiceAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){ + private String getToBeInvoiceAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql, String dateType){ + String betweenSql = null; + if("applicationTime".equals(dateType)){ + betweenSql = " and exists (select 1 from invoicePlan ip where ip.id=td.invoicePlanID and " + + "ip.applicationTime " + + toBeBetweenSql + +") "; + }else{ + betweenSql = SqlUtils.buildFieldNameBetweenSql("po.applicationTime", toBeBetweenSql); + } String amountSql = "((case when (po.type='" + InvoicePlan.TYPE_BORROWINGSINGLE +"' or po.type='" @@ -456,7 +488,7 @@ + InvoicePlan.STATUS_END +"')) and (po.committedStatus = 1) and po.handleDepartCoding = '" + handleDepartCoding +"' "; - commonFilterSql += tousseTypesql + SqlUtils.buildFieldNameBetweenSql("po.applicationTime", toBeBetweenSql); + commonFilterSql += tousseTypesql + betweenSql; //外来器械申请单 String sql = " select (amount - sendOutAmount) amount" + ",(case when (ti.urgentAmount is null or ti.urgentAmount <= ti.sendoutAmount) then 0 else (ti.urgentAmount - ti.sendoutAmount) end) urgentAmount from " @@ -873,9 +905,19 @@ * @param handleDepartCoding 一级供应室编码 * @param tousseTypesql 包类型过滤 * @param toBeBetweenSql 时间过滤 + * @param dateType 时间过滤的类型 * @return */ - private String getToBeSterilizationAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){ + private String getToBeSterilizationAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql, String dateType){ + String betweenSql = null; + if("applicationTime".equals(dateType)){ + betweenSql = " and exists (select 1 from invoicePlan ip where ip.id=td.invoicePlanID and " + + "ip.applicationTime " + + toBeBetweenSql + +") "; + }else{ + betweenSql = SqlUtils.buildFieldNameBetweenSql("ti.operationTime", toBeBetweenSql); + } String sql = "select 1 amount from " + ReviewedBasket.class.getSimpleName() +" po join " @@ -922,7 +964,7 @@ +" pd on pd.id=ti.proxyDisinfection_id where tdancestor.isTraceable='是' and ti.status='" + TousseInstance.STATUS_REVIEWED +"' " - + SqlUtils.buildFieldNameBetweenSql("ti.operationTime", toBeBetweenSql) + + betweenSql + tousseTypesql + "and ti.comboTousseInstanceId is null and ti.reviewBasket_id is null and (pd.id is null and ti.orgunitcoding='" +handleDepartCoding @@ -936,9 +978,19 @@ * @param handleDepartCoding 一级供应室编码 * @param tousseTypesql 包类型过滤 * @param toBeBetweenSql 时间过滤 + * @param dateType 时间过滤的类型 * @return */ - private String getToBePackAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){ + private String getToBePackAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql, String dateType){ + String betweenSql = null; + if("applicationTime".equals(dateType)){ + betweenSql = " and exists (select 1 from invoicePlan ip where ip.id=td.invoicePlanID and " + + "ip.applicationTime " + + toBeBetweenSql + +") "; + }else{ + betweenSql = SqlUtils.buildFieldNameBetweenSql("po.startTime", toBeBetweenSql); + } String packingSql = " select po.unPackAmount amount from " + PackingTask.class.getSimpleName() +" po join " @@ -949,7 +1001,7 @@ + handleDepartCoding +"' " + tousseTypesql - + SqlUtils.buildFieldNameBetweenSql("po.startTime", toBeBetweenSql) + + betweenSql + "and (tdc.hidePackingTask is null or tdc.hidePackingTask<>'是') "; return packingSql; } @@ -1043,7 +1095,7 @@ public JSONObject getTousseSummary(){ //获取当天开始时间 SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); - SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); + DateFormat sdf = DateTools.YMDFORMAT.get(); Date today = new Date(); String dateStr = sdf.format(today); String startDate = ""; @@ -1068,6 +1120,7 @@ }else{ dashboardsQueryCycle = systemParamsObj.getDashboardsQueryCycle(); } + String dateType = null;//时间过滤类型 if(dashboardsQueryCycle != 0){ Calendar calendar = Calendar.getInstance(); calendar.setTime(today); @@ -1098,7 +1151,7 @@ sql += " union all " + inventoryEdAmountSql; //当天待清洗器械总包数 String toBeWashAmountSql = " select 'tobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeWashAmountSql(handleDepartCoding, "", toBeBetweenSql, false) + ") tb "; + + getToBeWashAmountSql(handleDepartCoding, "", toBeBetweenSql, false,null) + ") tb "; sql += " union all " + toBeWashAmountSql; //当天已清洗器械总包数 String washedAmountSql = String.format("select 'washedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1107,7 +1160,7 @@ sql += " union all " + washedAmountSql; //当天待装配的总包数 String toBePackAmountSql = " select 'toBePackAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBePackAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb "; + + getToBePackAmountSql(handleDepartCoding, "", toBeBetweenSql,null) + ") tb "; sql += " union all " + toBePackAmountSql; //当天已装配总包数 String packedAmountSql = String.format("select 'packedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1121,7 +1174,7 @@ sql += " union all " + reviewedAmountSql; //当天待灭菌的总包数 String toBeSterilizationAmountSql = " select 'toBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeSterilizationAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb "; + + getToBeSterilizationAmountSql(handleDepartCoding, "", toBeBetweenSql,null) + ") tb "; sql += " union all " + toBeSterilizationAmountSql; //当天已灭菌的总包数 String sterilizationedAmountSql = String.format("select 'sterilizationedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1130,7 +1183,7 @@ sql += " union all " + sterilizationedAmountSql; //当天待发货的总包数 String toBeInvoiceAmountSql = " select 'toBeInvoiceAmount' type,sum(amount) amount,sum(urgentAmount) urgentAmount from ( " - + getToBeInvoiceAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb "; + + getToBeInvoiceAmountSql(handleDepartCoding, "", toBeBetweenSql,dateType) + ") tb "; sql += " union all " + toBeInvoiceAmountSql; //当天已发货的总包数 String invoicedAmountSql = String.format("select 'invoicedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1160,14 +1213,128 @@ } return obj; } + public JSONObject getForeignTousseSummaryByInvoicePlan() { + //获取当天开始时间 + SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); + DateFormat sdf = DateTools.YMDFORMAT.get(); + Date today = new Date(); + String dateStr = sdf.format(today); + String startDate = ""; + if(StringUtils.isNotBlank(systemParamsObj.getDayStartTime())){ + startDate += dateStr + " " + systemParamsObj.getDayStartTime() + ":00"; + }else{ + startDate += dateStr + " 00:00:00"; + } + startDate = "2024-09-01 00:00:00"; + String endDate = dateStr + " 23:59:59"; + String betweenSql = " between "+ dateQueryAdapter.dateAdapter(startDate) + " and " + + dateQueryAdapter.dateAdapter(endDate); + String handleDepartCoding = supplyRoomConfigManager.getFirstSupplyRoomConfig().getOrgUnitCoding(); + String tousseType = "外来器械包,外来器械拆分小包"; + String tousseTypesql = SqlUtils.getWhereSqlByfilterFieldAndStringValueAndSeparator("td.tousseType", tousseType, ","); + ReportQueryParams foreignParams = new ReportQueryParams(); + foreignParams.haveCombo = false; + foreignParams.betweenSql = betweenSql; + foreignParams.querySupplyRoom = handleDepartCoding; + foreignParams.tousseTypes = tousseType; + foreignParams.tousseTypeAndPackageSizeSql = tousseTypesql; + + //外来器械申请数量 + String foreignApplyAmountSql = String.format("select 'foreignApplyAmount' type,sum(amount) amount,0 urgentAmount from (" + +dataIndex.getWorkAmountByPackageSQL("申请数量", foreignParams) + + ") tl"); + String sql = foreignApplyAmountSql; + //外来器械当天待回收的包数量 + String foreignToBeRecycledAmountSql = "select 'foreignToBeRecycledAmount' type,sum(amount) amount,0 urgentAmount from ( " + + getToBeRecycledAmountSql(handleDepartCoding, tousseTypesql, betweenSql, true) + ") tb "; + sql += " union all " + foreignToBeRecycledAmountSql; + //外来器械当天已回收包数量 + foreignParams.extraQuery = " and rr.recyclingTimes is null " + + "and exists (select 1 from invoicePlan ip where ip.id=td.invoicePlanID and ip.applicationTime " + + betweenSql + + ")"; + foreignParams.betweenSql = ""; + String foreignRecycledAmountSql = String.format("select 'foreignRecycledAmount' type,sum(amount) amount,0 urgentAmount from (" + +dataIndex.getWorkAmountByPackageSQL("回收数量", foreignParams) + + ") tl"); + sql += " union all " + foreignRecycledAmountSql; + String dateType = "applicationTime";//时间过滤类型 过滤申请单的申请时间 + //外来器械当天待清洗器械总包数 + String foreignToBeWashAmountSql = " select 'foreignTobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( " + + getToBeWashAmountSql(handleDepartCoding, tousseTypesql, betweenSql, true,dateType) + ") tb "; + sql += " union all " + foreignToBeWashAmountSql; + //外来器械当天已清洗器械总包数 + foreignParams.extraQuery = " and ci.isSencondWashForForeignTousse=0 and exists (select 1 from invoicePlan ip where ip.id=td.invoicePlanID and " + + "ip.applicationTime " + + betweenSql + +") "; + String foreignWashedAmountSql = String.format("select 'foreignWashedAmount' type,sum(amount) amount,0 urgentAmount from (" + +dataIndex.getWorkAmountByPackageSQL("清洗数量", foreignParams) + + ") tl "); + sql += " union all " + foreignWashedAmountSql; + //中秋收假回来从这里开始做 + //外来器械当天待装配的总包数 + String foreignToBePackAmountSql = " select 'foreignToBePackAmount' type,sum(amount) amount,0 urgentAmount from ( " + + getToBePackAmountSql(handleDepartCoding, tousseTypesql, betweenSql,dateType) + ") tb "; + sql += " union all " + foreignToBePackAmountSql; + //外来器械当天已装配总包数 + foreignParams.extraQuery = " and exists (select 1 from invoicePlan ip where ip.id=td.invoicePlanID and " + + "ip.applicationTime " + + betweenSql + +") "; + String foreignPackedAmountSql = String.format("select 'foreignPackedAmount' type,sum(amount) amount,0 urgentAmount from (" + +dataIndex.getWorkAmountByPackageSQL("配包数量", foreignParams) + + ") tl "); + sql += " union all " + foreignPackedAmountSql; + //外来器械当天待灭菌的总包数 + String foreignToBeSterilizationAmountSql = " select 'foreignToBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( " + + getToBeSterilizationAmountSql(handleDepartCoding, tousseTypesql, betweenSql,dateType) + ") tb "; + sql += " union all " + foreignToBeSterilizationAmountSql; + //外来器械当天已灭菌的总包数 + String foreignSterilizationedAmountSql = String.format("select 'foreignSterilizationedAmount' type,sum(amount) amount,0 urgentAmount from (" + +dataIndex.getWorkAmountByPackageSQL("灭菌数量", foreignParams) + + ") tl "); + sql += " union all " + foreignSterilizationedAmountSql; + //外来器械当天待发货的总包数 + String foreignToBeInvoiceAmountSql = " select 'foreignToBeInvoiceAmount' type,sum(amount) amount,0 urgentAmount from ( " + + getToBeInvoiceAmountSql(handleDepartCoding, tousseTypesql, betweenSql,dateType) + ") tb "; + sql += " union all " + foreignToBeInvoiceAmountSql; + //外来器械当天已发货的总包数 + String foreignInvoicedAmountSql = String.format("select 'foreignInvoicedAmount' type,sum(amount) amount,0 urgentAmount from (" + +dataIndex.getWorkAmountByPackageSQL("发货数量", foreignParams) + + ") tl "); + sql += " union all " + foreignInvoicedAmountSql; + //外来器械当天待签收的包数 + String foreignToBeSignAmountSql = " select 'foreignToBeSignAmount' type,sum(amount) amount,0 urgentAmount from ( " + + getToBeSignAmountSql(handleDepartCoding, tousseTypesql, betweenSql,dateType) + ") tb "; + sql += " union all " + foreignToBeSignAmountSql; + //外来器械当天已签收的包数 + String foreignSignedAmountSql = String.format("select 'foreignSignedAmount' type,sum(amount) amount,0 urgentAmount from (" + +dataIndex.getWorkAmountByPackageSQL("签收数量", foreignParams) + + ") tl "); + sql += " union all " + foreignSignedAmountSql; + ResultSet rs = null; + JSONObject obj = new JSONObject(); + try { + rs = objectDao.executeSql(sql); + while (rs.next()) { + obj.put(rs.getString("type"), rs.getInt("amount")); + } + } catch (Exception e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + return obj; + } /** * 获取外来器械包汇总 * @return */ public JSONObject getForeignTousseSummary() { //获取当天开始时间 SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); - SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); + DateFormat sdf = DateTools.YMDFORMAT.get(); Date today = new Date(); String dateStr = sdf.format(today); String startDate = ""; @@ -1188,7 +1355,7 @@ foreignParams.querySupplyRoom = handleDepartCoding; foreignParams.tousseTypes = tousseType; foreignParams.tousseTypeAndPackageSizeSql = tousseTypesql; - + String dateType = null;//时间过滤类型 String sql = ""; String toBeBetweenSql = ""; Integer dashboardsQueryCycle = null; @@ -1224,7 +1391,7 @@ foreignParams.extraQuery = ""; //外来器械当天待清洗器械总包数 String foreignToBeWashAmountSql = " select 'foreignTobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeWashAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql, true) + ") tb "; + + getToBeWashAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql, true,dateType) + ") tb "; sql += " union all " + foreignToBeWashAmountSql; //外来器械当天已清洗器械总包数 foreignParams.extraQuery = " and ci.isSencondWashForForeignTousse=0 "; @@ -1235,7 +1402,7 @@ foreignParams.extraQuery = ""; //外来器械当天待装配的总包数 String foreignToBePackAmountSql = " select 'foreignToBePackAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBePackAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb "; + + getToBePackAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql,dateType) + ") tb "; sql += " union all " + foreignToBePackAmountSql; //外来器械当天已装配总包数 String foreignPackedAmountSql = String.format("select 'foreignPackedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1244,7 +1411,7 @@ sql += " union all " + foreignPackedAmountSql; //外来器械当天待灭菌的总包数 String foreignToBeSterilizationAmountSql = " select 'foreignToBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeSterilizationAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb "; + + getToBeSterilizationAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql,dateType) + ") tb "; sql += " union all " + foreignToBeSterilizationAmountSql; //外来器械当天已灭菌的总包数 String foreignSterilizationedAmountSql = String.format("select 'foreignSterilizationedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1253,7 +1420,7 @@ sql += " union all " + foreignSterilizationedAmountSql; //外来器械当天待发货的总包数 String foreignToBeInvoiceAmountSql = " select 'foreignToBeInvoiceAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeInvoiceAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb "; + + getToBeInvoiceAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql,dateType) + ") tb "; sql += " union all " + foreignToBeInvoiceAmountSql; //外来器械当天已发货的总包数 String foreignInvoicedAmountSql = String.format("select 'foreignInvoicedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1262,7 +1429,7 @@ sql += " union all " + foreignInvoicedAmountSql; //外来器械当天待签收的包数 String foreignToBeSignAmountSql = " select 'foreignToBeSignAmount' type,sum(amount) amount,0 urgentAmount from ( " - + getToBeSignAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb "; + + getToBeSignAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql,dateType) + ") tb "; sql += " union all " + foreignToBeSignAmountSql; //外来器械当天已签收的包数 String foreignSignedAmountSql = String.format("select 'foreignSignedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1294,7 +1461,7 @@ Integer dashboardsQueryCycle = null; //获取当天开始时间 SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); - SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); + DateFormat sdf = DateTools.YMDFORMAT.get(); Date today = new Date(); if(systemParamsObj.getDashboardsQueryCycle() == null){ dashboardsQueryCycle = 7; @@ -1339,7 +1506,7 @@ //获取当天开始时间 JSONObject obj = new JSONObject(); SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); - SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); + DateFormat sdf = DateTools.YMDFORMAT.get(); Date today = new Date(); String dateStr = sdf.format(today); String startDate = ""; @@ -1397,7 +1564,7 @@ */ public String getUrgentGoodsDetails(){ SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); - SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); + DateFormat sdf = DateTools.YMDFORMAT.get(); Date today = new Date(); String dateStr = sdf.format(today); String startDate = ""; @@ -1450,12 +1617,15 @@ + "' then 1 else 0 end) steMount" + ",sum(case when ti.invoice_id is not null then 1 else 0 end) invoiceAmount from " + TousseInstance.class.getSimpleName() - + " ti where ti.urgentlevel_id is not null " + + " ti join " + + TousseDefinition.class.getSimpleName() + + " td on td.id=ti.tousseDefinition_id " + + " where ti.urgentlevel_id is not null " + "and exists (select 1 from " + InvoicePlan.class.getSimpleName() + " ip where ip.applicationTime " + betweenSql - +" and ip.handleDepartCoding = '" + +" and td.invoicePlanID=ip.id and ip.handleDepartCoding = '" + handleDepartCoding +"') group by ti.tousseName "; ResultSet rsOfTousseInstanceAmount = null; @@ -1514,7 +1684,7 @@ public String getUrgentToussehandleInfo(){ //获取当天开始时间 SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); - SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); + DateFormat sdf = DateTools.YMDFORMAT.get(); Date today = new Date(); String dateStr = sdf.format(today); String startDate = ""; @@ -1539,6 +1709,7 @@ }else{ dashboardsQueryCycle = systemParamsObj.getDashboardsQueryCycle(); } + String dateType = null;//时间过滤类型 if(dashboardsQueryCycle != 0){ Calendar calendar = Calendar.getInstance(); calendar.setTime(today); @@ -1550,7 +1721,7 @@ } //当天待发货的总包数 String toBeInvoiceAmountSql = " select 'toBeInvoiceAmount' type,sum(amount) amount,sum(urgentAmount) urgentAmount from ( " - + getToBeInvoiceAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb "; + + getToBeInvoiceAmountSql(handleDepartCoding, "", toBeBetweenSql,dateType) + ") tb "; sql = toBeInvoiceAmountSql; //当天已发货的总包数 String invoicedAmountSql = String.format("select 'invoicedAmount' type,sum(amount) amount,0 urgentAmount from (" @@ -1586,6 +1757,7 @@ */ public String getDataByDataSources(Map requestParameters) { String dataSources = requestParameters.get("dataSources"); + dataSources = "foreigntoussesummary_specifyperiod"; if(StringUtils.isBlank(dataSources)){ return null; } @@ -1611,7 +1783,7 @@ result = resultArr.toString(); } }else if("foreigntoussesummary_specifyperiod".equalsIgnoreCase(dataSources)){//查询指定周期外来器械申请单数据汇总 - JSONObject foreignTousseSummaryObj = getForeignTousseSummary(); + JSONObject foreignTousseSummaryObj = getForeignTousseSummaryByInvoicePlan(); JSONObject foreignTousseSummary_SpecifyPeriodObj = getForeignTousseSummary_SpecifyPeriod(); foreignTousseSummaryObj.put("invoicePlanAmount", foreignTousseSummary_SpecifyPeriodObj.optInt("invoicePlanAmount")); foreignTousseSummaryObj.put("recyAmount", foreignTousseSummary_SpecifyPeriodObj.optInt("recyAmount")); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r38737 -r38741 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 38737) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 38741) @@ -1386,14 +1386,14 @@ amountSql = String .format("select sum(ri.amount) amount %s %s %s %s %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 " - + "where rr.recyclingTime %s %s %s and %s %s %s " + + "where %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":"", obj.selectUserName ? ",rr.recyclingUser userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName " : "", obj.extraSelectColumns, - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" rr.recyclingTime " + obj.betweenSql, SqlUtils.get_InSql_Extra("rr.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("rr.depart", obj.applicationDepart), SqlUtils.getStringFieldInLargeCollectionsPredicate("rr.depart", obj.recyDepartGroup, " 1=1 "), @@ -1484,14 +1484,14 @@ + "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 ", + + "and %s %s %s and %s %s %s and wdr.washMaterialAmount <> 0 %s %s %s %s %s %s ", obj.queryIntegral?",sum(ci.tousseAmountForMaterial*(case when tdc.integral is null then 1 else tdc.integral end)) integral":"", obj.selectUserName?", case when (min(wdr.washPersonInCharge) is null or min(wdr.washPersonInCharge) = '' ) then min(wdr.operator) else min(wdr.washPersonInCharge) end userName":"", obj.selectTousseType ? ",min(td.tousseType) tousseType" : "", obj.selectTousseName ? ",min(td.name) tousseName" : "", obj.extraSelectColumns, obj.extraSelectColumns1, - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" wdr.endDate " + obj.betweenSql, SqlUtils.get_InSql_Extra("wdr.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ci.orgUnitName", obj.applicationDepart), SqlUtils.getStringFieldInLargeCollectionsPredicate("ci.orgUnitName", obj.recyDepartGroup, " 1=1 "), @@ -1508,15 +1508,15 @@ 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"+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 ", + + "and %s %s %s and %s %s %s and ci.itemtype != '材料' and wdr.washMaterialAmount <> 0 %s %s %s %s %s %s ", sum, obj.queryIntegral?",(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*(case when tdc.integral is null then 1 else tdc.integral end) integral":"", obj.selectUserName?", case when (wdr.washPersonInCharge is null or wdr.washPersonInCharge = '') then wdr.operator else wdr.washPersonInCharge end userName":"", obj.selectTousseType ? ",ci.itemType tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", obj.extraSelectColumns, obj.extraSelectColumns2, - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" wdr.endDate " + obj.betweenSql, SqlUtils.get_InSql_Extra("wdr.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ci.orgUnitName", obj.applicationDepart), SqlUtils.getStringFieldInLargeCollectionsPredicate("ci.orgUnitName", obj.recyDepartGroup, " 1=1 "), @@ -1540,7 +1540,7 @@ amountSql = String .format("select %s count(*) amount %s %s %s %s %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 " + + "where %s and %s %s %s and %s %s %s " + "%s %s %s %s %s " + "%s ", getDatePeriodSelectSql(obj.monthlyStr), @@ -1551,7 +1551,7 @@ obj.extraSelectColumns, obj.extraJoinCondition, obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" ti.operationTime " + 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 "), @@ -1571,7 +1571,7 @@ amountSql += String .format("select %s count(*) amount %s %s %s %s %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 " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and %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":"", @@ -1580,7 +1580,7 @@ obj.selectTousseName ? ",td.name tousseName " : "", obj.extraSelectColumns, obj.extraJoinCondition, - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" ti.operationTime " + 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 "), @@ -1781,7 +1781,7 @@ amountSql = String .format("select %s count(*) amount %s %s %s %s %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 " + + "where %s and %s %s %s and %s %s %s " //+ "and sr.sterilizationUser is not null and %s(sr.sterilizationUser)<>0 " //+ "and ti.sterilizationRecord_id is not null" + " %s %s %s %s %s " @@ -1794,7 +1794,7 @@ obj.extraSelectColumns, obj.extraJoinCondition, obj.haveCombo?"ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null":"1=1", - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" sr.endDate " + obj.betweenSql, SqlUtils.get_InSql_Extra("sr.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.depart", obj.recyDepartGroup, " 1=1 "), @@ -1815,7 +1815,7 @@ amountSql += String .format("select %s count(*) amount %s %s %s %s %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 " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and %s %s %s and %s %s %s " //+ "and sr.sterilizationUser is not null and %s(sr.sterilizationUser)<>0 " //+ "and ti.sterilizationRecord_id is not null" + " %s %s %s %s %s %s ", @@ -1826,7 +1826,7 @@ obj.selectTousseName ? ",td.name tousseName " : "", obj.extraSelectColumns, obj.extraJoinCondition, - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" sr.endDate " + obj.betweenSql, SqlUtils.get_InSql_Extra("sr.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("ti.depart", obj.applicationDepart), SqlUtils.getStringFieldInLargeCollectionsPredicate("ti.depart", obj.recyDepartGroup, " 1=1 "), @@ -1863,7 +1863,7 @@ .format("select sum(ii.amount) amount %s %s %s %s %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 " + + "where %s %s %s and %s %s %s " //+ "and i.sender is not null and %s(i.sender)<>0" + " %s %s %s %s %s " + " %s %s ", @@ -1873,7 +1873,7 @@ obj.selectTousseName ? ",td.name tousseName" : "", obj.extraSelectColumns, obj.extraJoinCondition, - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" i.sendTime " + obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("i.depart",obj.applicationDepart), @@ -1898,15 +1898,15 @@ amountSql += String .format("select sum(ii.amount) amount %s %s %s %s %s " + "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 " + + "where ii.tousseType = '一次性物品' and %s %s %s and %s " //+ "and i.sender is not null and %s(i.sender)<>0 " + " %s", obj.queryIntegral?",0 integral":"", obj.selectUserName ? ",i.sender userName" : "", obj.selectTousseType ? ",ii.tousseType" : "", obj.selectTousseName ? "ii.tousseName" : "", obj.extraSelectColumns, - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" i.sendTime " + obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), SqlUtils.get_InSql_Extra("i.depart",obj.applicationDepart), @@ -2076,13 +2076,13 @@ amountSql = String .format("select count(*) amount %s, td.tousseType, td.name tousseName " + "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 " + + "where ti.comboTousseInstanceId is null and %s %s %s and %s %s %s " + SqlUtils.getIsNotNullSql(dbConnection, "ti.signedUser") + " %s %s %s %s %s %s " + "group by td.tousseType,td.name", obj.extraSelectColumns, obj.extraJoinCondition, - obj.betweenSql, + StringUtils.isBlank(obj.betweenSql)?" 1=1 ":" ti.signedDate " + 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 "),