Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (revision 25133) @@ -0,0 +1,59 @@ +package com.forgon.disinfectsystem.reportforms.vo; + + +/** + * @author Terry 2018-12-10 下午15:54 + * 报表查询参数，代表查询报表的条件 + * + */ +public class ReportQueryParams { + public String querySupplyRoom = ""; + + /** + * 查询的时间段 + */ + public String betweenSql = ""; + + /** + * 是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) + */ + public String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + + /** + * 任务组的过滤 + */ + public String taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + + /** + * 器械包分组的过滤 + */ + public String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + + /** + * 按器械包类型和器械包大小过滤 + */ + public String tousseTypeAndPackageSizeSql = ""; + + /** + * SQL计算长度的函数的名字 + */ + public String sqlLengthFunctionName = ""; + + public String monthlyStr = ""; + + public String tousseTypes = ""; + + public String extraSql = ""; + + /** + * 是否包含器械包，默认为True + */ + public boolean includeTousses = true; + + /** + * 是否包含一次性物品，默认为false + */ + public boolean includeDisposableGoods = false; + + +} Index: ssts-web/src/test/java/test/forgon/disinfectsystem/reports/dataindex/DataIndexTests.java =================================================================== diff -u -r25126 -r25133 --- ssts-web/src/test/java/test/forgon/disinfectsystem/reports/dataindex/DataIndexTests.java (.../DataIndexTests.java) (revision 25126) +++ ssts-web/src/test/java/test/forgon/disinfectsystem/reports/dataindex/DataIndexTests.java (.../DataIndexTests.java) (revision 25133) @@ -1,21 +1,15 @@ package test.forgon.disinfectsystem.reports.dataindex; import static org.testng.Assert.assertEquals; -import static org.testng.Assert.assertFalse; import static org.testng.Assert.assertNotNull; import static org.testng.Assert.assertTrue; import java.sql.ResultSet; -import java.sql.SQLException; -import java.text.SimpleDateFormat; import java.util.ArrayList; -import java.util.Collection; import java.util.Date; import java.util.HashMap; -import java.util.HashSet; import java.util.List; import java.util.Map; -import java.util.Set; import javax.servlet.http.HttpSession; @@ -29,41 +23,26 @@ import test.forgon.disinfectsystem.AbstractCSSDTest; import com.forgon.databaseadapter.service.DateQueryAdapter; -import com.forgon.directory.model.BarcodeDevice; -import com.forgon.directory.model.OrgUnit; import com.forgon.disinfectsystem.diposablegoods.service.DiposableGoodsManager; -import com.forgon.disinfectsystem.entity.assestmanagement.DiposableGoodsInstance; -import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoodsBatchStock; -import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoodsIdentification; -import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoodsStock; -import com.forgon.disinfectsystem.entity.basedatamanager.foreigntoussedefinition.ForeignTousseDefinition; -import com.forgon.disinfectsystem.entity.basedatamanager.materialdefinition.MaterialDefinition; -import com.forgon.disinfectsystem.entity.basedatamanager.materialinstance.MaterialInstance; import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseInstance; import com.forgon.disinfectsystem.entity.becleanitem.ClassifyBasket; -import com.forgon.disinfectsystem.entity.foreigntousseapplication.ForeignTousseApplication; import com.forgon.disinfectsystem.entity.invoicemanager.InvoicePlan; import com.forgon.disinfectsystem.entity.packing.PackingRecord; import com.forgon.disinfectsystem.entity.packing.PackingTask; -import com.forgon.disinfectsystem.entity.recyclingapplication.RecyclingApplication; import com.forgon.disinfectsystem.entity.recyclingrecord.RecyclingRecord; import com.forgon.disinfectsystem.entity.tousseitem.TousseItem; import com.forgon.disinfectsystem.entity.washanddisinfectmanager.washanddisinfectrecord.WashAndDisinfectRecord; import com.forgon.disinfectsystem.entity.washanddisinfectmanager.washanddisinfectrecord.WashAndDisinfectRecordMaterial; import com.forgon.disinfectsystem.exception.RecyclingRecordException; import com.forgon.disinfectsystem.foreigntousseapplication.service.ForeignTousseApplicationManager; -import com.forgon.disinfectsystem.jasperreports.javabeansource.StatisticalWorkload; import com.forgon.disinfectsystem.jasperreports.service.DataIndex; import com.forgon.disinfectsystem.packing.dwr.table.PackingTableManager; import com.forgon.disinfectsystem.recyclingapplication.service.InvoicePlanManager; import com.forgon.disinfectsystem.recyclingapplication.service.RecyclingApplicationManager; -import com.forgon.disinfectsystem.vo.TousseItemVo; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; import com.forgon.disinfectsystem.washanddisinfectmanager.washanddisinfectrecord.service.WashAndDisinfectRecordManager; -import com.forgon.serialnumber.model.SerialNum; import com.forgon.serialnumber.service.SerialNumManager; -import com.forgon.tools.GB2Alpha; -import com.forgon.tools.GB2WB; import com.forgon.tools.date.DateTools; import com.forgon.tools.db.DatabaseUtil; import com.forgon.tools.db.InitDbConnection; @@ -120,17 +99,42 @@ String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection);; String tousseTypeSqlWithAliasOfTousseDefinitionIsTi = ""; + //查询的时间段 + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); + + //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) + String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = DataIndex.getIsDisableIDCardFilterSQL(isDisableIDCard); + + //任务组的过滤 + String taskGroupSqlWithAliasOfTousseDefinitionIsTd = DataIndex.getTaskGroupFilterSQL(supplyRoomConfigManager,taskGroup); + + //器械包分组的过滤 + String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = DataIndex.getTousseGroupFilterSQL(tousseGroup); + + + //按器械包类型和器械包大小过滤 + String tousseTypeSql = DataIndex.getTousseTypesFilterSQL(tousseTypes); + + String packageSizeSql = DataIndex.getPackageSizeFilterSQL(null); + + String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = betweenSql; + params.querySupplyRoom = querySupplyRoom; + params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = taskGroupSqlWithAliasOfTousseDefinitionIsTd; + params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = tousseGroupSqlWithAliasOfTousseDefinitionIsTd; + params.tousseTypeAndPackageSizeSql = tousseTypeAndPackageSizeSql; + params.sqlLengthFunctionName = sqlLengthFunctionName; + ResultSet rs = null; try { prepareData(); // 清洗数量按包 - String washAmountByPackage = "select '清洗数量' type,t1.userName userName,sum(t1.tousseAmount) amount from (" - + DataIndex.getWashAmountByPackageSQL( - supplyRoomConfigManager, dateQueryAdapter, - querySupplyRoom, startDateTime, endDateTime, - tousseTypes, null, null, null, packageSizes) - + ") t1 group by t1.userName "; + String washAmountByPackage = String.format("select '清洗数量' type,tl.userName userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("清洗数量", params) + + ") tl group by tl.userName "); rs = objectDao.executeSql(washAmountByPackage); assertNotNull(rs); @@ -141,18 +145,11 @@ assertEquals("孙清洗", rs.getString("userName")); assertEquals(10, rs.getInt("amount")); + String washAmountByMaterial = String.format("select 'wash-material' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + + ") tl group by tl.userName "); - String washAmountByMaterial = "select 'wash-material' as type,ti.userName userName,sum(ti.materialCount) amount from ("; - - washAmountByMaterial += DataIndex.getWashAmountByMaterialSQL(supplyRoomConfigManager, - dateQueryAdapter, querySupplyRoom, startDateTime, - endDateTime, tousseTypes, isDisableIDCard, taskGroup, - tousseGroup, null); - - washAmountByMaterial += String - .format(") ti where 1=1 %s %s group by ti.userName ", - DataIndex.getAndSql("ti.userName", operator), - tousseTypeSqlWithAliasOfTousseDefinitionIsTi); + rs = objectDao.executeSql(washAmountByMaterial); assertNotNull(rs); @@ -164,16 +161,14 @@ // 测试过滤任务组 - washAmountByMaterial = "select 'wash-material' as type,ti.userName,sum(ti.materialCount) amount from ("; - washAmountByMaterial += DataIndex.getWashAmountByMaterialSQL(supplyRoomConfigManager, - dateQueryAdapter, querySupplyRoom, startDateTime, - endDateTime, tousseTypes, isDisableIDCard, Constants.TASKGROUP_LINCHUANGQIXIEZU, - tousseGroup, null); + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = DataIndex + .getTaskGroupFilterSQL(supplyRoomConfigManager, + Constants.TASKGROUP_LINCHUANGQIXIEZU); - washAmountByMaterial += String - .format(") ti where 1=1 %s %s group by ti.userName ", - DataIndex.getAndSql("ti.userName", operator), - tousseTypeSqlWithAliasOfTousseDefinitionIsTi); + washAmountByMaterial = String.format("select 'wash-material' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + + ") tl group by tl.userName "); + rs = objectDao.executeSql(washAmountByMaterial); assertNotNull(rs); @@ -182,15 +177,30 @@ assertEquals("孙清洗", rs.getString("userName")); assertEquals(2 * 15 , rs.getInt("amount")); - // 配包数量 - String packingAmountByMaterial = "select '配包数量' type,t1.userName userName,sum(amount) amount from ("; - packingAmountByMaterial += DataIndex.getPackingAmountByMaterialSQL( - supplyRoomConfigManager, dateQueryAdapter, querySupplyRoom, - startDateTime, endDateTime, tousseTypes, null, null, null, - packageSizes, sqlLengthFunctionName); - packingAmountByMaterial += ") t1 group by t1.userName "; + String packingAmountByMaterial = String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("配包数量", params) + + ") tl group by tl.userName "); + + rs = objectDao.executeSql(packingAmountByMaterial); + assertNotNull(rs); + assertTrue(rs.next()); + + assertEquals("配包数量", rs.getString("type")); + assertEquals("cssduser1", rs.getString("userName")); + + // 临床任务组，只有Test2个缝合包 + assertEquals(15 * 2, rs.getInt("amount")); + + // 清空任务组的过滤 + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + + // 配包数量 + packingAmountByMaterial = String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("配包数量", params) + + ") tl group by tl.userName "); + rs = objectDao.executeSql(packingAmountByMaterial); assertNotNull(rs); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/DataIndex.java =================================================================== diff -u -r25123 -r25133 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/DataIndex.java (.../DataIndex.java) (revision 25123) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/DataIndex.java (.../DataIndex.java) (revision 25133) @@ -11,10 +11,10 @@ import org.apache.commons.lang.StringUtils; import com.forgon.Constants; -import com.forgon.databaseadapter.service.DateQueryAdapter; import com.forgon.directory.acegi.tools.AcegiHelper; import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager; import com.forgon.disinfectsystem.entity.basedatamanager.supplyroomconfig.SupplyRoomConfig; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; import com.forgon.tools.string.StringTools; import com.forgon.tools.util.SqlUtils; @@ -118,71 +118,26 @@ return tousseTypeSql; } - /** - * 获取按包数量统计清洗数量的SQL，作为子查询，可被多个报表调用 - * @param querySupplyRoom 供应室的编码 - * @param startDateTime 查询的开始时间，精确到秒 - * @param endDateTime 查询的结束时间，精确到秒 - * @param isDisableIDCard 是否禁用身份牌 - * @param taskGroup 任务组 - * @param tousseGroup 器械包的分组 - * @param packageSizes 器械包的大小 - * @return Select查询，结果集包含4列：userName（工作人员姓名），materialCount（所清洗的无菌包的包数量），包名称，包类型 - */ - public static String getWashAmountByPackageSQL(SupplyRoomConfigManager supplyRoomConfigManager,DateQueryAdapter dateQueryAdapter,String querySupplyRoom, - String startDateTime, String endDateTime, String tousseTypes, String isDisableIDCard, - String taskGroup, String tousseGroup, String packageSizes) { - - //查询的时间段 - String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); - - //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) - String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = getIsDisableIDCardFilterSQL(isDisableIDCard); - - //任务组的过滤 - String taskGroupSqlWithAliasOfTousseDefinitionIsTd = getTaskGroupFilterSQL(supplyRoomConfigManager, taskGroup); - - //器械包分组的过滤 - String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = getTousseGroupFilterSQL(tousseGroup); + public static String getTousseTypesAndPackageSizesFilterSQL(String tousseTypes, String packageSizes){ //按器械包类型和器械包大小过滤 - String tousseTypeSql = getTousseTypesFilterSQL(tousseTypes); - - String packageSizeSql = getPackageSizeFilterSQL(packageSizes); - + String tousseTypeSql = DataIndex.getTousseTypesFilterSQL(tousseTypes); + String packageSizeSql = " 1=1 "; + if (StringTools.isNotBlank(packageSizes) && !packageSizes.contains("全部")) { + String[] packageSizeArr = packageSizes.split(","); + List packageSizeList = new LinkedList(); + for (String packageSize : packageSizeArr) { + packageSizeList.add(packageSize.trim()); + } + packageSizeSql = SqlUtils.getStringFieldInLargeCollectionsPredicate("td.packageSize", packageSizeList); + } String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); - - String tousseAmountSql = String.format( - "select case when (min(cb.personInCharge) is null or min(cb.personInCharge) = '') then min(wdr.operator) else min(cb.personInCharge) end userName," - + "min(ci.tousseNameForMaterial) tousseName,min(ci.tousseAmountForMaterial) tousseAmount,min(td.tousseType) tousseType " - + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td " - + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID " - + "and ci.itemType = '材料' " - + "and wdr.orgUnitCoding = '%s' and wdr.endDate %s and wdr.washMaterialAmount <> 0 %s %s %s %s " - + "group by ci.tousseDefinitionID,ci.recyclingRecordId", querySupplyRoom,betweenSql, - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseTypeAndPackageSizeSql); - - - tousseAmountSql += " union all "; - tousseAmountSql += String.format("select case when (cb.personInCharge is null or cb.personInCharge = '') then wdr.operator else cb.personInCharge end userName," - + "ci.name tousseName,ci.amount tousseAmount,ci.itemType tousseType " - + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td " - + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID " - + "and wdr.orgUnitCoding = '%s' and ci.itemtype != '材料' and wdr.endDate %s and wdr.washMaterialAmount <> 0 %s %s %s %s ", - querySupplyRoom, betweenSql, - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseTypeAndPackageSizeSql); - return tousseAmountSql; + return tousseTypeAndPackageSizeSql; } - /** * 获取按材料统计清洗数量的SQL，可作为子查询，被多个报表调用 * + * @param reportType 报表类型，如果为年度统计报表，则按月分组汇总数量 * @param querySupplyRoom 供应室的编码 * @param startDateTime 查询的开始时间，精确到秒 * @param endDateTime 查询的结束时间，精确到秒 @@ -191,296 +146,861 @@ * @param tousseGroup 器械包的分组 * @param packageSizes 器械包的大小 * - * @return Select查询，结果集包含4列：userName（工作人员姓名），materialCount（所清洗的无菌包的包数量），tousseName（包名称），tousseType（包类型） + * @return Select查询，结果集包含4列：userName（工作人员姓名），materialCount（所清洗的无菌包的材料数量），tousseName（包名称），tousseType（包类型） */ - public static String getWashAmountByMaterialSQL(SupplyRoomConfigManager supplyRoomConfigManager, DateQueryAdapter dateQueryAdapter,String querySupplyRoom, - String startDateTime, String endDateTime, String tousseTypes, String isDisableIDCard, - String taskGroup, String tousseGroup, String packageSizes) { - - //查询的时间段 - String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); - - //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) - String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = getIsDisableIDCardFilterSQL(isDisableIDCard); - - //任务组的过滤 - String taskGroupSqlWithAliasOfTousseDefinitionIsTd = getTaskGroupFilterSQL(supplyRoomConfigManager, taskGroup); - - //器械包分组的过滤 - String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = getTousseGroupFilterSQL(tousseGroup); - - - //按器械包类型和器械包大小过滤 - String tousseTypeSql = getTousseTypesFilterSQL(tousseTypes); - - String packageSizeSql = getPackageSizeFilterSQL(packageSizes); - - String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); - - // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 - String washByMaterialAmountSQL = String - .format("select case when (cb.personInCharge is null or cb.personInCharge = '') then wr.operator else cb.personInCharge end userName,ci.amount materialCount,ci.tousseNameForMaterial tousseName,td.tousseType tousseType " - + "from WashAndDisinfectRecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td " - + "where cw.WashAndDisinfectRecord_ID = wr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID " - + "and ci.itemType = '材料' and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s ", - querySupplyRoom, betweenSql, - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseTypeAndPackageSizeSql); - - // 统计清洗项的itemType不为材料的物品，包括整包清洗的器械包 - washByMaterialAmountSQL += " union all "; - washByMaterialAmountSQL += String - .format("select case when (cb.personInCharge is null or cb.personInCharge = '') then wr.operator else cb.personInCharge end userName,(ci.amount*mi.count) materialCount,ci.name tousseName,td.tousseType tousseType " - + "from WashAndDisinfectRecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td,MaterialInstance mi " - + "where cw.WashAndDisinfectRecord_ID = wr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id = ci.toussedefinition_id and mi.tousse_id = td.id " - + "and ci.itemType <> '材料' and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s ", - querySupplyRoom, betweenSql, - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseTypeAndPackageSizeSql); - - // 判断是否需要统计单独清洗的材料的数量 - if ("全部".equals(tousseTypes) || StringUtils.isBlank(tousseTypes) - || tousseTypes.indexOf("材料") >= 0) { - washByMaterialAmountSQL += " union all "; - washByMaterialAmountSQL += String - .format("select wr.operator userName,wrm.amount materialCount,md.name tousseName,'材料' tousseType " - + "from WashAndDisinfectRecord wr, WashRecord_WashMaterial wm,WashAndDisinfectRecordMaterial wrm,MaterialDefinition md " - + "where wm.WashAndDisinfectRecord_ID = wr.id and wrm.id = wm.WashAndDisinfectMaterial_ID and wrm.materialDefinition_id = md.id " - + "and wr.orgUnitCoding = '%s' and wr.endDate %s", - querySupplyRoom, betweenSql); - } - - return washByMaterialAmountSQL; - } +// public static String getWashAmountByMaterialSQL(String reportType, SupplyRoomConfigManager supplyRoomConfigManager, DateQueryAdapter dateQueryAdapter,String querySupplyRoom, +// String startDateTime, String endDateTime, String tousseTypes, String isDisableIDCard, +// String taskGroup, String tousseGroup, String packageSizes) { +// +// //查询的时间段 +// String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); +// +// //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) +// String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = getIsDisableIDCardFilterSQL(isDisableIDCard); +// +// //任务组的过滤 +// String taskGroupSqlWithAliasOfTousseDefinitionIsTd = getTaskGroupFilterSQL(supplyRoomConfigManager, taskGroup); +// +// //器械包分组的过滤 +// String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = getTousseGroupFilterSQL(tousseGroup); +// +// +// //按器械包类型和器械包大小过滤 +// String tousseTypeSql = getTousseTypesFilterSQL(tousseTypes); +// +// String packageSizeSql = getPackageSizeFilterSQL(packageSizes); +// +// String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); +// +// +// String washByMaterialAmountSQL = ""; +// +// if ("年度报表".equals(reportType)) { +// // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 +// washByMaterialAmountSQL += String +// .format("select " + dateQueryAdapter.dateConverAdapter3("wr.endDate","mm") + " monthstr,sum(ci.amount) materialCount " +// + getWashMaterialAmountSqlFromItemTypeIsMaterial() +// + " and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s group by " +// + dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"), +// querySupplyRoom, betweenSql, +// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, +// taskGroupSqlWithAliasOfTousseDefinitionIsTd, +// tousseGroupSqlWithAliasOfTousseDefinitionIsTd, +// tousseTypeAndPackageSizeSql); +// +// // 统计清洗项的itemType不为材料的物品，包括整包清洗的器械包 +// washByMaterialAmountSQL += " union all "; +// washByMaterialAmountSQL += String +// .format("select " + dateQueryAdapter.dateConverAdapter3("wr.endDate","mm") + " monthstr,sum(ci.amount*mi.count) materialCount " +// + getWashMaterialAmountSqlFromItemTypeIsNotMaterial() +// + " and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s group by " +// + dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"), +// querySupplyRoom, betweenSql, +// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, +// taskGroupSqlWithAliasOfTousseDefinitionIsTd, +// tousseGroupSqlWithAliasOfTousseDefinitionIsTd, +// tousseTypeAndPackageSizeSql); +// +// // 统计单独清洗的材料的数量 +// washByMaterialAmountSQL += " union all "; +// washByMaterialAmountSQL += String +// .format("select " + dateQueryAdapter.dateConverAdapter3("wr.endDate","mm") + " monthstr,sum(wrm.amount) materialCount " +// + getWashMaterialAmountSqlFromTousseTypeIsMaterial() +// + "and wr.orgUnitCoding = '%s' and wr.endDate %s group by " +// + dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"), +// querySupplyRoom, betweenSql); +// } +// else{ +// // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 +// washByMaterialAmountSQL += String +// .format("select case when (cb.personInCharge is null or cb.personInCharge = '') then wr.operator else cb.personInCharge end userName,ci.amount materialCount,ci.tousseNameForMaterial tousseName,td.tousseType tousseType " +// + getWashMaterialAmountSqlFromItemTypeIsMaterial() +// + " and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s ", +// querySupplyRoom, betweenSql, +// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, +// taskGroupSqlWithAliasOfTousseDefinitionIsTd, +// tousseGroupSqlWithAliasOfTousseDefinitionIsTd, +// tousseTypeAndPackageSizeSql); +// +// // 统计清洗项的itemType不为材料的物品，包括整包清洗的器械包 +// washByMaterialAmountSQL += " union all "; +// washByMaterialAmountSQL += String +// .format("select case when (cb.personInCharge is null or cb.personInCharge = '') then wr.operator else cb.personInCharge end userName,(ci.amount*mi.count) materialCount,ci.name tousseName,td.tousseType tousseType " +// + getWashMaterialAmountSqlFromItemTypeIsNotMaterial() +// + " and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s ", +// querySupplyRoom, betweenSql, +// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, +// taskGroupSqlWithAliasOfTousseDefinitionIsTd, +// tousseGroupSqlWithAliasOfTousseDefinitionIsTd, +// tousseTypeAndPackageSizeSql); +// +// // 判断是否需要统计单独清洗的材料的数量 +// if ("全部".equals(tousseTypes) || StringUtils.isBlank(tousseTypes) +// || tousseTypes.indexOf("材料") >= 0) { +// washByMaterialAmountSQL += " union all "; +// washByMaterialAmountSQL += String +// .format("select wr.operator userName,wrm.amount materialCount,md.name tousseName,'材料' tousseType " +// + getWashMaterialAmountSqlFromTousseTypeIsMaterial() +// + "and wr.orgUnitCoding = '%s' and wr.endDate %s", +// querySupplyRoom, betweenSql); +// } +// } +// return washByMaterialAmountSQL; +// } /** - * 获取按包统计装配数量的SQL，可作为子查询，被多个报表调用 - * @param querySupplyRoom - * @param startDateTime - * @param endDateTime - * @param tousseTypes - * @param isDisableIDCard - * @param taskGroup - * @param tousseGroup - * @param packageSizes - * @return 返回数据集包括4列：userName（装配人）、tousseName（包名称）、amount（装配数量）、tousseType（包类型） + * 查询材料清洗数量的sql，统计itemType是材料的类型 + * @return */ - public static String getPackingAmountByPackageSQL(SupplyRoomConfigManager supplyRoomConfigManager,DateQueryAdapter dateQueryAdapter,String querySupplyRoom, - String startDateTime, String endDateTime, String tousseTypes, String isDisableIDCard, - String taskGroup, String tousseGroup, String packageSizes, String sqlLengthFunctionName) { - - //查询的时间段 - String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); - - //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) - String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = getIsDisableIDCardFilterSQL(isDisableIDCard); - - //任务组的过滤 - String taskGroupSqlWithAliasOfTousseDefinitionIsTd = getTaskGroupFilterSQL(supplyRoomConfigManager,taskGroup); - - //器械包分组的过滤 - String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = getTousseGroupFilterSQL(tousseGroup); + private static String getWashMaterialAmountSqlFromItemTypeIsMaterial(){ + String sql = "from WashAndDisinfectRecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td " + + "where cw.WashAndDisinfectRecord_ID = wr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID " + + "and ci.itemType = '材料'"; + return sql; + } - - //按器械包类型和器械包大小过滤 - String tousseTypeSql = getTousseTypesFilterSQL(tousseTypes); - - String packageSizeSql = getPackageSizeFilterSQL(packageSizes); - - String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); - - //装配数量（按包统计） - String packingAmountSql = String.format("select ti.operator userName,td.name tousseName,count(*) amount,td.tousseType from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " - + "where ti.orgUnitCoding = '%s' " - + "and ti.operationTime %s and ti.operator is not null and %s(ti.operator)<>0 %s %s %s %s " - + "group by ti.operator,td.tousseType,td.name ", - querySupplyRoom, betweenSql, sqlLengthFunctionName, - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseTypeAndPackageSizeSql); - -// packingAmountSql += " union all "; - // TODO:统计没经过系统装配产生的包实例的数量 - - return packingAmountSql; + /** + * 查询材料清洗数量的sql，统计itemType为不是材料的类型 + * @return + */ + private static String getWashMaterialAmountSqlFromItemTypeIsNotMaterial(){ + String sql = "from WashAndDisinfectRecord wr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td,MaterialInstance mi " + + "where cw.WashAndDisinfectRecord_ID = wr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id = ci.toussedefinition_id and mi.tousse_id = td.id " + + "and ci.itemType <> '材料'"; + return sql; } + /** + * 查询单独登记的材料的清洗数量的sql，器械包类型为材料 + * @return + */ + private static String getWashMaterialAmountSqlFromTousseTypeIsMaterial(){ + String sql = "from WashAndDisinfectRecord wr, WashRecord_WashMaterial wm,WashAndDisinfectRecordMaterial wrm,MaterialDefinition md " + + "where wm.WashAndDisinfectRecord_ID = wr.id and wrm.id = wm.WashAndDisinfectMaterial_ID and wrm.materialDefinition_id = md.id "; + return sql; + } - /** - * 获取按材料统计装配数量的SQL，可作为子查询，被多个报表调用 - * @param querySupplyRoom - * @param startDateTime - * @param endDateTime - * @param tousseTypes - * @param isDisableIDCard - * @param taskGroup - * @param tousseGroup - * @param packageSizes - * @return 返回数据集包括2列：userName（装配人）、amount（装配数量） + * 获取按材料统计各环节工作量的SQL，可作为子查询，被多个报表调用 + * @param obj 参数对象文件，包含所属供应室、查询开始时间、查询结束时间、器械包类型等多个参数 + * @param workType 工作量的名字 + * @return 返回数据集包括4列：userName（姓名）、tousseName（包名称）、amount（包数量）、tousseType（包类型） */ - public static String getPackingAmountByMaterialSQL(SupplyRoomConfigManager supplyRoomConfigManager,DateQueryAdapter dateQueryAdapter,String querySupplyRoom, - String startDateTime, String endDateTime, String tousseTypes, String isDisableIDCard, - String taskGroup, String tousseGroup, String packageSizes, String sqlLengthFunctionName) { + public static String getWorkAmountByMaterialSQL(String workType, ReportQueryParams obj) { - //查询的时间段 - String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); + String amountSql = ""; - //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) - String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = getIsDisableIDCardFilterSQL(isDisableIDCard); + switch (workType) { - //任务组的过滤 - String taskGroupSqlWithAliasOfTousseDefinitionIsTd = getTaskGroupFilterSQL(supplyRoomConfigManager,taskGroup); + case "回收数量": + amountSql = String + .format("select sum(ri.amount * mi.count) amount ,rr.recyclingUser userName, td.tousseType,td.name tousseName " + + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where rr.orgUnitCoding = '%s' and rr.recyclingTime %s " + + "and rr.recyclingUser is not null and %s(rr.recyclingUser)<>0 %s %s %s %s " + + "group by rr.recyclingUser,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "年度报表中的回收数量（按材料）": + amountSql = String + .format("select " + obj.monthlyStr + " monthstr, sum(ri.amount * mi.count) amount " + + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where rr.orgUnitCoding = '%s' and rr.recyclingTime %s " + + "and rr.recyclingUser is not null and %s(rr.recyclingUser)<>0 %s %s %s %s " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + break; - //器械包分组的过滤 - String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = getTousseGroupFilterSQL(tousseGroup); - - //按器械包类型和器械包大小过滤 - String tousseTypeSql = getTousseTypesFilterSQL(tousseTypes); + case "清点数量": + amountSql = String + .format("select sum(ri.amount * mi.count) amount ,rr.operator userName, td.tousseType,td.name tousseName " + + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where rr.orgUnitCoding = '%s' and rr.recyclingTime %s " + + "and rr.operator is not null and %s(rr.operator)<>0 %s %s %s %s " + + "group by rr.operator,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + - String packageSizeSql = getPackageSizeFilterSQL(packageSizes); - - String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); + case "年度报表中的清洗数量（按材料）": + // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 + amountSql += String + .format("select " + obj.monthlyStr + " monthstr,sum(ci.amount) materialCount " + + getWashMaterialAmountSqlFromItemTypeIsMaterial() + + " and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + // 统计清洗项的itemType不为材料的物品，包括整包清洗的器械包 + amountSql += " union all "; + amountSql += String + .format("select " + obj.monthlyStr + " monthstr,sum(ci.amount*mi.count) materialCount " + + getWashMaterialAmountSqlFromItemTypeIsNotMaterial() + + " and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + // 判断是否需要统计单独清洗的材料的数量 + if ("全部".equals(obj.tousseTypes) + || StringUtils.isBlank(obj.tousseTypes) + || obj.tousseTypes.indexOf("材料") >= 0) { + amountSql += " union all "; + amountSql += String.format("select " + obj.monthlyStr + + " monthstr,sum(wrm.amount) materialCount " + + getWashMaterialAmountSqlFromTousseTypeIsMaterial() + + "and wr.orgUnitCoding = '%s' and wr.endDate %s " + + "group by " + obj.monthlyStr, obj.querySupplyRoom, + obj.betweenSql); + } + break; + + case "清洗数量": + + // 统计清洗项的itemType为材料的物品，包括拆包清洗的器械包以及外来器械包 + amountSql += String + .format("select case when (cb.personInCharge is null or cb.personInCharge = '') then wr.operator else cb.personInCharge end userName,ci.amount amount,ci.tousseNameForMaterial tousseName,td.tousseType tousseType " + + getWashMaterialAmountSqlFromItemTypeIsMaterial() + + " and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s ", + obj.querySupplyRoom, + obj.betweenSql, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + // 统计清洗项的itemType不为材料的物品，包括整包清洗的器械包 + amountSql += " union all "; + amountSql += String + .format("select case when (cb.personInCharge is null or cb.personInCharge = '') then wr.operator else cb.personInCharge end userName,(ci.amount*mi.count) amount,ci.name tousseName,td.tousseType tousseType " + + getWashMaterialAmountSqlFromItemTypeIsNotMaterial() + + " and wr.orgUnitCoding = '%s' and wr.endDate %s and wr.washMaterialAmount <> 0 %s %s %s %s ", + obj.querySupplyRoom, + obj.betweenSql, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + // 判断是否需要统计单独清洗的材料的数量 + if ("全部".equals(obj.tousseTypes) || StringUtils.isBlank(obj.tousseTypes) + || obj.tousseTypes.indexOf("材料") >= 0) { + amountSql += " union all "; + amountSql += String + .format("select wr.operator userName,wrm.amount amount,md.name tousseName,'材料' tousseType " + + getWashMaterialAmountSqlFromTousseTypeIsMaterial() + + "and wr.orgUnitCoding = '%s' and wr.endDate %s", + obj.querySupplyRoom, obj.betweenSql); + } + + break; - // 统计通过系统装配的包的数量 - String packingAmountByMaterial1 = String - .format("select ti.operator userName,sum(mi.count) amount from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " - + "inner join MaterialInstance mi on td.id = mi.tousse_id where ti.orgUnitCoding = '%s'" + - " and ti.operationTime %s and ti.operator is not null and %s(ti.operator)<>0 %s %s %s %s" - + "group by ti.operator ", querySupplyRoom, betweenSql, - sqlLengthFunctionName, - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseTypeAndPackageSizeSql); -// packingAmountByMaterial1 += " union all "; - // TODO:统计没经过系统装配产生的包实例的数量 - - -// // 统计非外来器械拆分小包的装配数量 -// String packingAmountByMaterial1 = String -// .format("select pr.packer userName,sum(pr.amount * ms.count) amount from PackingRecord pr inner join tousseDefinition td on pr.tousseDefinitionId = td.id " -// + "inner join materialInstance ms on td.id = ms.tousse_id where splitTousseName is null and pr.orgUnitCoding = '%s'" + -// " and pr.packTime %s and pr.packer is not null and %s(pr.packer)<>0 %s %s %s %s" -// + "group by pr.packer ", querySupplyRoom, betweenSql, -// sqlLengthFunctionName, -// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, -// taskGroupSqlWithAliasOfTousseDefinitionIsTd, -// tousseGroupSqlWithAliasOfTousseDefinitionIsTd, -// tousseTypeAndPackageSizeSql); - -// packingAmountByMaterial1 += " union all "; -// -// // 统计外来器械拆分小包的装配数量 -// packingAmountByMaterial1 += String -// .format("select pr.packer userName,sum(pr.amount * ms.count) amount from PackingRecord pr inner join tousseDefinition td on pr.tousseDefinitionId = td.id " -// + "inner join materialInstance ms on td.id = ms.tousse_id where splitTousseName is not null and pr.orgUnitCoding = '%s'" + -// " and pr.packTime %s and pr.packer is not null and %s(pr.packer)<>0 %s %s %s %s" -// + "group by pr.packer ", querySupplyRoom, betweenSql, -// sqlLengthFunctionName, -// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, -// taskGroupSqlWithAliasOfTousseDefinitionIsTd, -// tousseGroupSqlWithAliasOfTousseDefinitionIsTd, -// tousseTypeAndPackageSizeSql); - - - - - return packingAmountByMaterial1; - } + case "配包数量": + amountSql = String + .format("select sum(mi.count) amount ,ti.operator userName, td.tousseType,td.name tousseName " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where ti.orgUnitCoding = '%s' and ti.operationTime %s " + + "and ti.operator is not null and %s(ti.operator)<>0 %s %s %s %s " + + "group by ti.operator,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "年度报表中的配包数量（按材料）": + amountSql = String + .format("select " + obj.monthlyStr + " monthstr, sum(mi.count) amount " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where ti.orgUnitCoding = '%s' and ti.operationTime %s " + + "and ti.operator is not null and %s(ti.operator)<>0 %s %s %s %s " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "检查数量": + amountSql = String + .format("select sum(mi.count) amount ,pr.inspector userName, td.tousseType,td.name tousseName " + + "from PackingRecord pr inner join TousseDefinition td on pr.tousseDefinitionId = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where pr.orgUnitCoding = '%s' and pr.packTime %s " + + "and pr.inspector is not null and %s(pr.inspector)<>0 %s %s %s %s " + + "group by pr.inspector,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; - /** - * 获取按包统计审核数量的SQL - * @param querySupplyRoom - * @param betweenSql - * @param sqlLengthFunctionName - * @param extraAndQuery - * @return - */ - // TODO:需要改造为传入原始的查询参数 - public static String getReviewAmountByPackageSQL(String querySupplyRoom, - String betweenSql, String sqlLengthFunctionName, String extraAndQuery) { - String tousseAmountSql = String.format("select ti.reviewer userName,count(*) amount,td.name tousseName,td.tousseType from tousseInstance ti join TousseDefinition td on td.id=ti.tousseDefinition_id " - + "where ti.orgUnitCoding = '%s' and ti.reviewTime %s and %s(ti.reviewer) <> 0 %s " - + "group by ti.reviewer,td.name,td.tousseType ", querySupplyRoom,betweenSql,sqlLengthFunctionName,extraAndQuery); - return tousseAmountSql; + case "包装数量": + amountSql = String + .format("select sum(mi.count) amount ,pr.wrapper userName, td.tousseType,td.name tousseName " + + "from PackingRecord pr inner join TousseDefinition td on pr.tousseDefinitionId = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where pr.orgUnitCoding = '%s' and pr.packTime %s " + + "and pr.wrapper is not null and %s(pr.wrapper)<>0 %s %s %s %s " + + "group by pr.wrapper,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "审核数量": + amountSql = String + .format("select sum(mi.count) amount ,ti.reviewer userName, td.tousseType,td.name tousseName " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where ti.orgUnitCoding = '%s' and ti.reviewTime %s " + + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s %s " + + "group by ti.reviewer,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + case "灭菌数量": + amountSql = String + .format("select sum(mi.count) amount ,ti.sterilizationUser userName, td.tousseType,td.name tousseName " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id inner join MaterialInstance mi on mi.tousse_id = td.id " + + "where ti.orgUnitCoding = '%s' and ti.sterileEndTime %s " + + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 %s %s %s %s " + + "group by ti.sterilizationUser,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + case "发货数量": + amountSql = String + .format("select sum(mi.count) amount ,i.sender userName, td.tousseType,td.name tousseName " + + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id inner join MaterialInstance mi on mi.tousse_id = td.id " + + "where i.orgUnitCoding = '%s' and i.sendTime %s " + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s " + + "group by i.sender,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + // 一次性物品的发货统计 + // TODO:原来的代码做了一次性物品的数量统计，导致发货的器械件数很大，应该单独做一列，列出一次性物品的数量 +// if (obj.includeDisposableGoods){ +// amountSql += " union all "; +// amountSql = String +// .format("select sum(ii.amount) amount ,i.sender userName, ii.tousseType tousseType,ii.tousseName tousseName " +// + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " +// + "where i.orgUnitCoding = '%s' and ii.tousseType = '一次性物品' and i.sendTime %s " +// + "and i.sender is not null and %s(i.sender)<>0 " +// + "group by i.sender,ii.tousseType,ii.tousseName ", +// obj.querySupplyRoom, +// obj.betweenSql, +// obj.sqlLengthFunctionName); + +// } + + break; + + case "年度报表中的发货数量（按材料）": + amountSql = String + .format("select " + obj.monthlyStr + " monthstr, sum(mi.count) amount " + + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id inner join MaterialInstance mi on mi.tousse_id = td.id " + + "where i.orgUnitCoding = '%s' and i.sendTime %s " + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + // 一次性物品的发货统计 + if (obj.includeDisposableGoods) { + amountSql += " union all "; + amountSql = String + .format("select " + obj.monthlyStr + " monthstr, sum(ii.amount) amount " + + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "where i.orgUnitCoding = '%s' and ii.tousseType = '一次性物品' and i.sendTime %s " + + "and i.sender is not null and %s(i.sender)<>0 " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName); + + } + + break; + + + case "核对数量": + amountSql = String + .format("select sum(mi.count) amount ,i.assistantSender userName, td.tousseType,td.name tousseName " + + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id inner join MaterialInstance mi on mi.tousse_id = td.id " + + "where i.orgUnitCoding = '%s' and i.sendTime %s " + + "and i.assistantSender is not null and %s(i.assistantSender)<>0 %s %s %s %s " + + "group by i.assistantSender,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + // 一次性物品的发货核对统计 + if (obj.includeDisposableGoods){ + amountSql += " union all "; + amountSql = String + .format("select sum(ii.amount) amount ,i.assistantSender userName, ii.tousseType tousseType,ii.tousseName tousseName " + + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "where i.orgUnitCoding = '%s' and ii.tousseType = '一次性物品' and i.sendTime %s " + + "and i.assistantSender is not null and %s(i.assistantSender)<>0 " + + "group by i.assistantSender,ii.tousseType,ii.tousseName ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName); + + } + + break; + + case "下送数量": + amountSql = String + .format("select sum(mi.count) amount ,i.personInCharge userName, td.tousseType,td.name tousseName " + + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id inner join MaterialInstance mi on mi.tousse_id = td.id " + + "where i.orgUnitCoding = '%s' and i.sendTime %s " + + "and i.personInCharge is not null and %s(i.personInCharge)<>0 %s %s %s %s " + + "group by i.personInCharge,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + // 一次性物品的发货下送数量统计 + if (obj.includeDisposableGoods){ + amountSql += " union all "; + amountSql = String + .format("select sum(ii.amount) amount ,i.personInCharge userName, ii.tousseType tousseType,ii.tousseName tousseName " + + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "where i.orgUnitCoding = '%s' and ii.tousseType = '一次性物品' and i.sendTime %s " + + "and i.personInCharge is not null and %s(i.personInCharge)<>0 " + + "group by i.personInCharge,ii.tousseType,ii.tousseName ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName); + + } + + break; + + } + return amountSql; } /** - * 获取按包统计灭菌数量的SQL，可作为子查询，被多个报表调用 - * @param querySupplyRoom - * @param startDateTime - * @param endDateTime - * @param tousseTypes - * @param isDisableIDCard - * @param taskGroup - * @param tousseGroup - * @param packageSizes + * 获取按包统计各环节工作量的SQL，可作为子查询，被多个报表调用 + * @param obj 参数对象文件，包含所属供应室、查询开始时间、查询结束时间、器械包类型等多个参数 + * @param workType 工作量的名字 * @return 返回数据集包括4列：userName（姓名）、tousseName（包名称）、amount（包数量）、tousseType（包类型） */ - public static String getWorkAmountByPackageSQL(String workType, SupplyRoomConfigManager supplyRoomConfigManager,DateQueryAdapter dateQueryAdapter,String querySupplyRoom, - String startDateTime, String endDateTime, String tousseTypes, String isDisableIDCard, - String taskGroup, String tousseGroup, String packageSizes, String sqlLengthFunctionName) { + public static String getWorkAmountByPackageSQL(String workType, ReportQueryParams obj) { - //查询的时间段 - String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); + String amountSql = ""; + switch (workType) { - //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) - String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = getIsDisableIDCardFilterSQL(isDisableIDCard); + case "申请数量": + amountSql = String + .format("select sum(item.amount) amount ,td.tousseType,td.name tousseName " + + "from InvoicePlan ip inner join TousseItem item on item.recyclingApplication_ID=ip.id inner join TousseDefinition td on td.id=item.tousseDefinitionId " + + "where ip.handleDepartCoding = '%s' and ip.applicationTime %s " + + "and item.tousseType<>'一次性物品' %s %s %s %s " + + "group by td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; - //任务组的过滤 - String taskGroupSqlWithAliasOfTousseDefinitionIsTd = getTaskGroupFilterSQL(supplyRoomConfigManager,taskGroup); - //器械包分组的过滤 - String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = getTousseGroupFilterSQL(tousseGroup); - + case "回收数量": + amountSql = String + .format("select sum(ri.amount) amount ,rr.recyclingUser userName, td.tousseType,td.name tousseName " + + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId=td.id " + + "where rr.orgUnitCoding = '%s' and rr.recyclingTime %s " + + "and rr.recyclingUser is not null and %s(rr.recyclingUser)<>0 %s %s %s %s " + + "group by rr.recyclingUser,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + + case "清点数量": + amountSql = String + .format("select sum(ri.amount) amount ,rr.operator userName, td.tousseType,td.name tousseName " + + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId=td.id " + + "where rr.orgUnitCoding = '%s' and rr.recyclingTime %s " + + "and rr.operator is not null and %s(rr.operator)<>0 %s %s %s %s " + + "group by rr.operator,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "清洗数量": + amountSql = String.format( + "select case when (min(cb.personInCharge) is null or min(cb.personInCharge) = '') then min(wdr.operator) else min(cb.personInCharge) end userName," + + "min(ci.tousseNameForMaterial) tousseName,min(ci.tousseAmountForMaterial) amount,min(td.tousseType) tousseType " + + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td " + + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID " + + "and ci.itemType = '材料' " + + "and wdr.orgUnitCoding = '%s' and wdr.endDate %s and wdr.washMaterialAmount <> 0 %s %s %s %s " + + "group by ci.tousseDefinitionID,ci.recyclingRecordId", + obj.querySupplyRoom, + obj.betweenSql, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + + + amountSql += " union all "; + amountSql += String.format("select case when (cb.personInCharge is null or cb.personInCharge = '') then wdr.operator else cb.personInCharge end userName," + + "ci.name tousseName,ci.amount amount,ci.itemType tousseType " + + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td " + + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID " + + "and wdr.orgUnitCoding = '%s' and ci.itemtype != '材料' and wdr.endDate %s and wdr.washMaterialAmount <> 0 %s %s %s %s ", + obj.querySupplyRoom, + obj.betweenSql, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + - //按器械包类型和器械包大小过滤 - String tousseTypeSql = getTousseTypesFilterSQL(tousseTypes); + case "配包数量": + amountSql = String + .format("select count(*) amount ,ti.operator userName, td.tousseType,td.name tousseName " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "where ti.orgUnitCoding = '%s' and ti.operationTime %s " + + "and ti.operator is not null and %s(ti.operator)<>0 %s %s %s %s " + + "group by ti.operator,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "年度报表中的配包数量（按包）": + amountSql = String + .format("select " + obj.monthlyStr + " monthstr, count(*) amount " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "where ti.orgUnitCoding = '%s' and ti.operationTime %s " + + "and ti.operator is not null and %s(ti.operator)<>0 %s %s %s %s " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "检查数量": + amountSql = String + .format("select sum(pr.amount) amount ,pr.inspector userName, td.tousseType,td.name tousseName " + + "from PackingRecord pr inner join TousseDefinition td on td.id=pr.tousseDefinitionId " + + "where pr.orgUnitCoding = '%s' and pr.packTime %s " + + "and pr.inspector is not null and %s(pr.inspector)<>0 %s %s %s %s " + + "group by pr.inspector,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + + case "包装数量": + amountSql = String + .format("select sum(pr.amount) amount ,pr.wrapper userName, td.tousseType,td.name tousseName " + + "from PackingRecord pr inner join TousseDefinition td on td.id=pr.tousseDefinitionId " + + "where pr.orgUnitCoding = '%s' and pr.packTime %s " + + "and pr.wrapper is not null and %s(pr.wrapper)<>0 %s %s %s %s " + + "group by pr.wrapper,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; - String packageSizeSql = getPackageSizeFilterSQL(packageSizes); - - String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); + + case "审核数量": + amountSql = String + .format("select count(*) amount ,ti.reviewer userName, td.tousseType,td.name tousseName " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "where ti.orgUnitCoding = '%s' and ti.reviewTime %s " + + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s %s " + + "group by ti.reviewer,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; - String amountSql = ""; - switch (workType) { + case "灭菌数量": - // 灭菌数量（按包统计） amountSql = String - .format("select count(*) amount ,ti.sterilizationUser userName, td.tousseType,td.name tousseName from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " - + "where ti.orgUnitCoding = '%s' " - + "and ti.sterileEndTime %s and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 %s %s %s %s " + .format("select count(*) amount ,ti.sterilizationUser userName, td.tousseType,td.name tousseName " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "where ti.orgUnitCoding = '%s' and ti.sterileEndTime %s " + + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 %s %s %s %s " + "group by ti.sterilizationUser,td.tousseType,td.name ", - querySupplyRoom, betweenSql, sqlLengthFunctionName, - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseTypeAndPackageSizeSql); + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); break; + + case "年度报表中的灭菌记录数量（灭菌次数）": + amountSql = String + .format("select " + obj.monthlyStr + " monthstr, count(*) amount " + + "from Sterilizationrecord sr " + + "where sr.orgUnitCoding = '%s' and sr.endDate %s " + + "and (sr.status = '灭菌完成' or sr.status = '灭菌失败') " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql); + break; + + case "年度报表中的灭菌数量（按包）": + amountSql = String + .format("select " + obj.monthlyStr + " monthstr,count(*) amount " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "where ti.orgUnitCoding = '%s' and ti.sterileEndTime %s " + + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 %s %s %s %s " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + case "发货数量": amountSql = String - .format("select sum(ii.amount) amount ,i.sender userName, td.tousseType,td.name tousseName from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " - + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId where i.orgUnitCoding = '%s' " - + "and i.sendTime %s and i.sender is not null and %s(i.sender)<>0 %s %s %s %s " - + "group by i.sender,td.tousseType,td.name ", - querySupplyRoom, betweenSql, sqlLengthFunctionName, - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseTypeAndPackageSizeSql); + .format("select sum(ii.amount) amount ,i.sender userName, td.tousseType, td.name tousseName " + + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId " + + "where i.orgUnitCoding = '%s' and i.sendTime %s " + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s " + + "group by i.sender,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); break; + + case "年度报表中的发货数量（按包）": + + // 包含器械包 + if (obj.includeTousses){ + + amountSql = String + .format("select " + obj.monthlyStr + " monthstr,sum(ii.amount) amount " + + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId " + + "where i.orgUnitCoding = '%s' and i.sendTime %s " + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s %s " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql, + obj.extraSql); // extraSql用来做部门过滤 + } + // 包含一次性物品 + if (obj.includeDisposableGoods){ + + if (obj.includeTousses){ + amountSql += " union all "; + } + + amountSql = String + .format("select " + obj.monthlyStr + " monthstr, sum(ii.amount) amount " + + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + + "where i.orgUnitCoding = '%s' and ii.tousseType = '一次性物品' and i.sendTime %s " + + "and i.sender is not null and %s(i.sender)<>0 " + + "group by " + + obj.monthlyStr, + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName); + + } + + break; + + case "核对数量": + amountSql = String + .format("select sum(ii.amount) amount ,i.assistantSender userName, td.tousseType, td.name tousseName " + + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId " + + "where i.orgUnitCoding = '%s' and i.sendTime %s " + + "and i.assistantSender is not null and %s(i.assistantSender)<>0 %s %s %s %s " + + "group by i.assistantSender,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "下送数量": + amountSql = String + .format("select sum(ii.amount) amount ,i.personInCharge userName, td.tousseType, td.name tousseName " + + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId " + + "where i.orgUnitCoding = '%s' and i.sendTime %s " + + "and i.personInCharge is not null and %s(i.personInCharge)<>0 %s %s %s %s " + + "group by i.personInCharge,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + case "签收数量": + amountSql = String + .format("select count(*) amount ,ti.signedUser userName, td.tousseType, td.name tousseName " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id=td.id " + + "where ti.orgUnitCoding = '%s' and ti.signedDate %s " + + "and ti.signedUser is not null and %s(ti.signedUser)<>0 %s %s %s %s " + + "group by ti.signedUser,td.tousseType,td.name ", + obj.querySupplyRoom, + obj.betweenSql, + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + break; + + } - - - return amountSql; } Index: ssts-web/src/test/java/test/forgon/disinfectsystem/utils/SstsUtils.java =================================================================== diff -u --- ssts-web/src/test/java/test/forgon/disinfectsystem/utils/SstsUtils.java (revision 0) +++ ssts-web/src/test/java/test/forgon/disinfectsystem/utils/SstsUtils.java (revision 25133) @@ -0,0 +1,86 @@ +package test.forgon.disinfectsystem.utils; + +import java.util.Date; + +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +import net.sf.json.JSONArray; +import net.sf.json.JSONObject; +import test.forgon.constant.Constants; +import test.forgon.disinfectsystem.AbstractCSSDTest; + +import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; +import com.forgon.disinfectsystem.entity.recyclingrecord.RecyclingRecord; +import com.forgon.disinfectsystem.exception.RecyclingRecordException; +import com.forgon.disinfectsystem.packing.dwr.table.PackingTableManager; +import com.forgon.disinfectsystem.packing.service.PackingManager; +import com.forgon.disinfectsystem.recyclingrecord.service.RecyclingRecordManager; +import com.forgon.disinfectsystem.tousse.toussedefinition.service.TousseDefinitionManager; +import com.forgon.disinfectsystem.washanddisinfectmanager.washanddisinfectrecord.service.WashAndDisinfectRecordManager; + +/** + * @author Terry + * + * 消毒追溯系统的单元测试工具类，提供直接的接口api，用于创建各种单据。 + * + * 输入参数为用户能看懂的数据，让调用者不需要了解内部实现的逻辑 + * + */ +@Component +public class SstsUtils{ + @Autowired + private PackingManager packingManager; + @Autowired + private RecyclingRecordManager recyclingRecordManager; + @Autowired + private WashAndDisinfectRecordManager washAndDisinfectRecordManager; + @Autowired + private PackingTableManager packingTableManagerTX; + + private TousseDefinitionManager tousseDefinitionManager; + + + /** + * 创建回收单 + * @param depart + * @param departCoding + * @param cssdOrgUnitCoding + * @param operator + * @param recyclingUser + * @param tousseName + * @param amount + * @param basketBarcode + * @param basketId + * @return + */ + public RecyclingRecord createRecyclingRecord(String recyclingDateTime, String depart, + String departCoding, String cssdOrgUnitCoding, String operator, + String recyclingUser, String tousseName, int amount, + String basketBarcode, String basketId) { + // 创建回收记录1 + RecyclingRecord record1 = new RecyclingRecord(); + record1.setRecyclingTime(new Date()); + record1.setDepart("测试内科"); + record1.setDepartCode("testNeike"); + record1.setOperator("王伟"); + record1.setOrgUnitCoding(Constants.ORG_UNIT_CODE_CSSD); + record1.setRecyclingUser("张雄"); + + JSONArray jsonArray = new JSONArray(); + TousseDefinition td_Test开胸包 = tousseDefinitionManager.getTousseDefinitionByName("Test开胸包"); +// jsonArray.add(addRecyclingItem("019000001", "0", "Test开胸包",td_Test开胸包.getId(), 5, "器械包", "Test开胸包", +// 5, "019000001")); + JSONObject params = new JSONObject(); + params.put("tousseJson", jsonArray); + params.put("errors",""); + params.put("damages",""); + try { + recyclingRecordManager.save(record1,params); + } catch (RecyclingRecordException e) { + e.printStackTrace(); + } +// objectDao.flush(); + return record1; + } +} Index: ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java =================================================================== diff -u -r25122 -r25133 --- ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java (.../TousseInstance.java) (revision 25122) +++ ssts-basedata/src/main/java/com/forgon/disinfectsystem/entity/basedatamanager/toussedefinition/TousseInstance.java (.../TousseInstance.java) (revision 25133) @@ -421,7 +421,12 @@ /** * 统计数量，如果该器械包按包统计则为1，如果按材料统计则将材料数量加起来 + * + * 该属性设计不合理，应该逐步去掉。因为有的报表要按包统计，有的要按材料数量统计。 + * 不能去设置某个包只按包或者材料数量统计。Terry Kwan 2018-12-11 + * */ + @Deprecated private Integer statisticsAmount; /** Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r25123 -r25133 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 25123) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 25133) @@ -133,6 +133,7 @@ import com.forgon.disinfectsystem.foreigntousseapplication.service.ForeignTousseApplicationManager; import com.forgon.disinfectsystem.jasperreports.javabeansource.*; import com.forgon.disinfectsystem.qualitymonitoring.definition.service.QualityMonitoringDefinitionManager; +import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams; import com.forgon.disinfectsystem.tousse.materialdefinition.service.MaterialDefinitionManager; import com.forgon.disinfectsystem.tousse.materialmanager.service.MaterialCheckManager; import com.forgon.disinfectsystem.tousse.materialmanager.service.MaterialEntryItemManager; @@ -2948,22 +2949,55 @@ if(params == null){ return list; } + + String queryYear = year + "-01-01 00:00:00"; + String nextYear = getNextYear(year) + " 00:00:00"; + + //查询的时间段 + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(queryYear),dateQueryAdapter.dateAdapter(nextYear)); + //数据库计算字符串长度的函数名 + String sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + + ReportQueryParams reportParams = new ReportQueryParams(); + reportParams.betweenSql = betweenSql; + reportParams.querySupplyRoom = querySupplyRoom; + reportParams.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + reportParams.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + reportParams.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + reportParams.tousseTypeAndPackageSizeSql = ""; + reportParams.sqlLengthFunctionName = sqlLengthFunctionName; + + // 回收总数的Map - Map recAmountMap = getRecyclingAmountMap(querySupplyRoom,year); + Map recAmountMap = getRecyclingAmountMap(reportParams); // 清洗总数的Map - Map washAmountMap = getWashAmountMap(querySupplyRoom,year); + Map washAmountMap = getWashAmountMap(reportParams); // 装配总数的Map - Map packAmountMap = getPackAmountMap(querySupplyRoom,year,""); + Map packAmountMap = getPackAmountMap(reportParams); + + // 装配消毒物品总数的Map - Map packDisinfectAmountMap = getPackAmountMap(querySupplyRoom,year,TousseDefinition.PACKAGE_TYPE_DISINFECTION); + reportParams.tousseTypes = TousseDefinition.PACKAGE_TYPE_DISINFECTION; + reportParams.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_DISINFECTION, null); + + Map packDisinfectAmountMap = getPackAmountMap(reportParams); + + reportParams.tousseTypes = ""; + reportParams.tousseTypeAndPackageSizeSql = ""; + + // 灭菌总数的Map - Map sterilizationAmountMap = getSterilizationAmountMap(querySupplyRoom,year); +// Map sterilizationAmountMap = getSterilizationAmountMap(reportParams); // 灭菌记录条数的Map - Map sterilizationRecordAmountMap = getSterilizationRecordAmountMap(querySupplyRoom,year,"4#"); + Map sterilizationRecordAmountMap = getSterilizationRecordAmountMap(reportParams); // 发货数量的Map - Map invoiceAmountMap = getInvoiceAmountMap(querySupplyRoom,year,""); + Map invoiceAmountMap = getInvoiceAmountMap(reportParams,""); + + // TODO:此处手术室的名称为硬编码，要修改才行 // 手术室发货数量的Map - Map invoiceOperateAmountMap = getInvoiceAmountMap(querySupplyRoom,year,"手术室"); + Map invoiceOperateAmountMap = getInvoiceAmountMap(reportParams,"手术室"); // 中大附一的特殊配置 String isZSYYWorkQualityCollectionStr = params.get("isZSYYWorkQualityCollection"); if("true".equals(isZSYYWorkQualityCollectionStr)){ @@ -2978,25 +3012,25 @@ if(false == isHidePartOfWorkQualityCollection){ // 处理器械总件数 - getWashMaterialAmountAllYear(year, list,querySupplyRoom); + getWashMaterialAmountAllYear(list,reportParams); // 处理敷料总件数 - getDressingAmountAllYear(year, list,querySupplyRoom); + getDressingAmountAllYear(list,reportParams); // 外来器械清洗总件数 - getWashForeignMaterialAmountAllYear(year, list,querySupplyRoom); + getWashForeignMaterialAmountAllYear(list,reportParams); // 消毒物品工作量 - getDisinfectGoodsQuantityAllYear(year, list,querySupplyRoom); + getDisinfectGoodsQuantityAllYear(list,reportParams); // CSSD包装物品总包数 - getPackingAmountAllYear(year, list,querySupplyRoom); + getPackingAmountAllYear(list,reportParams); // CSSD包装物品总件数 - getPackingTousseMaterialAmountAllYear(year, list,querySupplyRoom); + getPackingTousseMaterialAmountAllYear(list,reportParams); // 灭菌器使用炉次 getCountFrequencyOfMonthAllYear(year, list,querySupplyRoom); // 灭菌物品总包数（普通灭菌、代理灭菌、外部代理灭菌）的统计，全年数据. getSterilizationAmountAllYear(year, list,querySupplyRoom); // 发放无菌物品总包数（器械包） - getSendTousseAmountAllYear(year, list,querySupplyRoom); + getSendTousseAmountAllYear(list,reportParams); // 发放无菌物品总件数（一次性物品） - getSendDiposableGoodsAmountAllYear(year, list,querySupplyRoom); + getSendDiposableGoodsAmountAllYear(list,reportParams); } // 器械清洗不合格数 getMaterialWashUnqualifiedAmountAllYear(year, list,querySupplyRoom,washAmountMap,isHidePartOfWorkQualityCollection); @@ -3358,18 +3392,15 @@ * @param year * @return */ - private Map getRecyclingAmountMap(String querySupplyRoom,String year){ + private Map getRecyclingAmountMap(ReportQueryParams reportParams){ Map map = new HashMap(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String startDay = dateQueryAdapter.dateAdapter(queryYear); - String endDay = dateQueryAdapter.dateAdapter(nextYear); - String sql = " select " + dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm") + " monthstr,sum(tdc.amount*ri.amount) from RecyclingRecord rr join RecyclingItem ri on rr.id=ri.recyclingRecord_id join TousseDefinition td on td.id=ri.tousseDefinitionId " - + " join (select td.id tid,td.name tdName, sum(mi.count) amount from MaterialInstance mi, tousseDefinition td where mi.tousse_id=td.id and td.forDisplay=1 group by td.id,td.name) tdc " - + " on tdc.tid=td.id" - + " where rr.orgUnitCoding = '" + querySupplyRoom + "'" - + " and rr.recyclingTime between " + startDay +" and " + endDay - + " group by " + dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); + + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("rr.recyclingTime","mm"); + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByMaterialSQL("年度报表中的回收数量（按材料）", + reportParams) + ") tl group by monthstr "; + map = getMapBySql(sql); return map; } @@ -3379,18 +3410,15 @@ * @param year * @return */ - private Map getWashAmountMap(String querySupplyRoom,String year){ + private Map getWashAmountMap(ReportQueryParams reportParams){ Map map = new HashMap(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql2 = "select "+ dateQueryAdapter.dateConverAdapter3("r.endDate","mm") - + " monthstr, sum(r.washmaterialamount) from washanddisinfectrecord r " - + "where r.orgUnitCoding = '"+querySupplyRoom+"' and r.endDate >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and r.endDate <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " group by " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm"); - map = getMapBySql(sql2); + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); + String sql = "select monthstr,sum(tl.materialCount) from (" + + DataIndex.getWorkAmountByMaterialSQL("年度报表中的清洗数量（按材料）", + reportParams) + ") tl group by monthstr "; + + map = getMapBySql(sql); return map; } /** @@ -3399,29 +3427,15 @@ * @param year * @return */ - private Map getPackAmountMap(String querySupplyRoom,String year,String tousseType){ + private Map getPackAmountMap(ReportQueryParams reportParams){ Map map = new HashMap(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql2 = "select " + dateQueryAdapter.dateConverAdapter3("p.packTime","mm") - + " monthstr, sum(p.amount) from packingrecord p" - + " where p.orgUnitCoding = '"+querySupplyRoom+"' and p.packTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and p.packTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " group by " + dateQueryAdapter.dateConverAdapter3("p.packTime","mm"); - if(StringUtils.isNotBlank(tousseType)){ - sql2 = "select " + dateQueryAdapter.dateConverAdapter3("p.packTime","mm") - + " monthstr, sum(p.amount) from packingrecord p,TousseDefinition td " - + " where p.tousseDefinitionId = td.id and td.tousseType = '" + tousseType + "'" - + " and p.orgUnitCoding = '"+querySupplyRoom+"' and p.packTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and p.packTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " group by " + dateQueryAdapter.dateConverAdapter3("p.packTime","mm"); - } - map = getMapBySql(sql2); + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("ti.operationTime","mm"); + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByPackageSQL("年度报表中的配包数量（按包）", + reportParams) + ") tl group by monthstr "; + + map = getMapBySql(sql); return map; } /** @@ -3430,19 +3444,16 @@ * @param year * @return */ - private Map getSterilizationAmountMap(String querySupplyRoom,String year){ + private Map getSterilizationAmountMap(ReportQueryParams reportParams){ Map map = new HashMap(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql2 = "select " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm") - + " monthstr, sum(r.amount) from sterilizationrecord r where r.endDate >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and r.endDate <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " and (r.status = '灭菌完成' or r.status = '灭菌失败') " - + " and r.orgUnitCoding = '"+querySupplyRoom+"'" - + " group by " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm"); - map = getMapBySql(sql2); + + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("ti.sterileEndTime","mm"); + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByPackageSQL("年度报表中的灭菌数量（按包）", + reportParams) + ") tl group by monthstr "; + + map = getMapBySql(sql); return map; } /** @@ -3451,33 +3462,15 @@ * @param year * @return */ - private Map getSterilizationRecordAmountMap(String querySupplyRoom,String year,String sterilizerNameNotContain){ + private Map getSterilizationRecordAmountMap(ReportQueryParams reportParams){ Map map = new HashMap(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sterilizerNameSql = ""; - String sql2 = "select " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm") - + " monthstr, count(*) from sterilizationrecord r where r.endDate >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and r.endDate <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " and (r.status = '灭菌完成' or r.status = '灭菌失败') " - + " and r.orgUnitCoding = '"+querySupplyRoom+"'" - + " group by " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm"); - if(StringUtils.isNotBlank(sterilizerNameNotContain)){ - sql2 = "select " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm") - + " monthstr, count(*) from sterilizationrecord r ,sterilizer s where r.sterilizer_id = s.id " - + " and r.endDate >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and r.endDate <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " and (r.status = '灭菌完成' or r.status = '灭菌失败') " - + " and r.orgUnitCoding = '"+querySupplyRoom+"'" - + " and s.name not like '%" + sterilizerNameNotContain + "%' " - + " group by " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm"); - } - map = getMapBySql(sql2); + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("sr.endDate","mm"); + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByPackageSQL("年度报表中的灭菌记录数量（灭菌次数）", + reportParams) + ") tl group by monthstr "; + + map = getMapBySql(sql); return map; } /** @@ -3487,24 +3480,21 @@ * @param depart * @return */ - private Map getInvoiceAmountMap(String querySupplyRoom,String year,String depart){ + private Map getInvoiceAmountMap(ReportQueryParams reportParams, String depart){ Map map = new HashMap(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String departSql = ""; - if(StringUtils.isNotBlank(depart)){ - departSql = " and i.depart = '" + depart + "'"; + + if (StringUtils.isNotBlank(depart)){ + reportParams.extraSql = " and i.depart = '" + depart + "'"; } - String sql2 = "select " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm") - + " monthstr, count(*) from invoice i,tousseinstance t where i.id=t.invoice_id " - + " and i.sendTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and i.sendTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - + departSql - + " group by " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); - map = getMapBySql(sql2); + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByPackageSQL("年度报表中的发货数量（按包）", + reportParams) + ") tl group by monthstr "; + + reportParams.extraSql = ""; + + map = getMapBySql(sql); return map; } /** @@ -3547,15 +3537,20 @@ startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } - String sql = "select sum(ti.statisticsAmount) from tousseinstance ti, invoice i,invoiceplan ip , TousseDefinition td" - + " where ti.invoice_id = i.id and i.invoiceplan_id = ip.id and ti.tousseDefinition_id=td.id" - //+ " and ip.type = '消毒物品申请单' " //因为消毒物品不只是消毒物品申请单里有,所以这里去掉单类型的条件过滤,在下面一行增加包类型为消毒物品 - + " and td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"' " - + " and i.sendTime >= " - + dateQueryAdapter.dateAdapter(startDay) - + " and i.sendTime <= " - + dateQueryAdapter.dateAdapter(endDay) - + " and ti.status = '"+ TousseInstance.STATUS_SHIPPED +"'"; + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateAdapter(startDay), + dateQueryAdapter.dateAdapter(endDay)); + params.querySupplyRoom = querySupplyRoom; + params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + + params.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_DISINFECTION, null); + + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("配包数量", params) + + ") tl "); bean.setTitle("消毒物品工作量统计"); bean.setRowNum(list.size()+1);; @@ -3578,23 +3573,20 @@ } // 消毒物品工作量统计 全年数据 private void getDisinfectGoodsQuantityAllYear( - String year, - List list,String querySupplyRoom) throws ParseException, + List list,ReportQueryParams reportParams) throws ParseException, SQLException { WorkQualityCollection bean = new WorkQualityCollection(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql = "select " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm") - + " monthstr, sum(ti.statisticsAmount) from tousseinstance ti, invoice i,invoiceplan ip , TousseDefinition td" - + " where ti.invoice_id = i.id and i.invoiceplan_id = ip.id and ti.tousseDefinition_id=td.id" - //+ " and ip.type = '消毒物品申请单' " //因为消毒物品不只是消毒物品申请单里有,所以这里去掉单类型的条件过滤,在下面一行增加包类型为消毒物品 - + " and td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"' " - + " and i.sendTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and i.sendTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " and ti.status = '"+ TousseInstance.STATUS_SHIPPED +"'" - + " group by " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); + + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); + reportParams.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_DISINFECTION, null); + + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByMaterialSQL("年度报表中的发货数量（按材料）", + reportParams) + ") tl group by monthstr "; + ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("消毒物品工作量统计"); @@ -4038,11 +4030,19 @@ startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } - String sql = "select sum(r.foreignMaterialAmount) from washanddisinfectrecord r " - + "where r.orgUnitCoding = '"+querySupplyRoom+"' and r.endDate >= " - + dateQueryAdapter.dateAdapter(startDay) - + " and r.endDate <= " - + dateQueryAdapter.dateAdapter(endDay); + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateAdapter(startDay), + dateQueryAdapter.dateAdapter(endDay)); + params.querySupplyRoom = querySupplyRoom; + + params.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_FOREIGN, null); + + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + + ") tl "); bean.setRowNum(list.size()+1); bean.setTitle("外来器械清洗总件数"); @@ -4065,18 +4065,20 @@ } // 外来器械清洗总件数 全年统计 private void getWashForeignMaterialAmountAllYear( - String year, - List list,String querySupplyRoom) throws SQLException { + List list,ReportQueryParams reportParams) throws SQLException { WorkQualityCollection bean = new WorkQualityCollection(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql = "select " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm") - + " monthstr, sum(r.foreignMaterialAmount) from washanddisinfectrecord r " - + "where r.orgUnitCoding = '"+querySupplyRoom+"' and r.endDate >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and r.endDate <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " group by " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm"); + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); + + reportParams.tousseTypes = TousseDefinition.PACKAGE_TYPE_FOREIGN; + reportParams.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_FOREIGN, null); + + String sql = "select monthstr,sum(tl.materialCount) from (" + + DataIndex.getWorkAmountByMaterialSQL("年度报表中的清洗数量（按材料）", + reportParams) + ") tl group by monthstr "; + ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("外来器械清洗总件数"); @@ -6042,24 +6044,20 @@ startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } -// String sql = "select sum(i.tousseamount) from invoice i,invoiceplan p where i.invoiceplan_id = p.id" -// + " and i.orgUnitCoding = '"+querySupplyRoom+"'" -// + " and p.type != '" -// + InvoicePlan.TYPE_DIPOSABLE_GOODS_APPLICATION_FORM -// + "' and i.sendTime >= " -// + dateQueryAdapter.dateAdapter(startDay) -// + " and i.sendTime <= " -// + dateQueryAdapter.dateAdapter(endDay) -// + " and i.status != '" + Invoice.STATUS_UNDELIVERED + "'"; - String sql = "select sum(i.tousseAmount) from invoice i " - + "where i.orgUnitCoding = '"+querySupplyRoom+"'" - + " and i.sendTime >= " - + dateQueryAdapter.dateAdapter(startDay) - + " and i.sendTime <= " - + dateQueryAdapter.dateAdapter(endDay) - + " and i.status != '" + Invoice.STATUS_UNDELIVERED + "' and i.tousseAmount>0 "; + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateAdapter(startDay), + dateQueryAdapter.dateAdapter(endDay)); + params.querySupplyRoom = querySupplyRoom; + params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + params.includeDisposableGoods = false; + + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl "); + bean.setTitle("发放无菌物品总包数（器械包）"); bean.setRowNum(list.size()+1); Integer amount = 0; @@ -6088,32 +6086,17 @@ * @throws SQLException */ private void getSendTousseAmountAllYear( - String year, - List list,String querySupplyRoom) throws SQLException { + List list,ReportQueryParams reportParams) throws SQLException { WorkQualityCollection bean = new WorkQualityCollection(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; -// String sql = "select " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm") -// + " monthstr, sum(i.tousseamount) from invoice i,invoiceplan p where i.invoiceplan_id = p.id" -// + " and i.orgUnitCoding = '"+querySupplyRoom+"'" -// + " and p.type != '" -// + InvoicePlan.TYPE_DIPOSABLE_GOODS_APPLICATION_FORM -// + "' and i.sendTime >= " -// + dateQueryAdapter.dateAdapter(queryYear) -// + " and i.sendTime <= " -// + dateQueryAdapter.dateAdapter(nextYear) -// + " and i.status != '" + Invoice.STATUS_UNDELIVERED + "'" -// + " group by " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); - String sql = "select " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm") - + " monthstr, sum(i.tousseAmount) from invoice i " - + "where i.orgUnitCoding = '"+querySupplyRoom+"'" - + " and i.sendTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and i.sendTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " and i.status != '" + Invoice.STATUS_UNDELIVERED + "' and i.tousseAmount>0 " - + " group by " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); + reportParams.tousseTypeAndPackageSizeSql = ""; + reportParams.includeDisposableGoods = false; + + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByPackageSQL("年度报表中的发货数量（按包）", + reportParams) + ") tl group by monthstr "; + ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("发放无菌物品总包数（器械包）"); @@ -6156,24 +6139,19 @@ startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } -// String sql = "select sum(i.diposablegoodsamount) from invoice i,invoiceplan p where i.invoiceplan_id = p.id" -// + " and i.orgUnitCoding = '"+querySupplyRoom+"'" -// + " and p.type = '" -// + InvoicePlan.TYPE_DIPOSABLE_GOODS_APPLICATION_FORM -// + "' and i.sendTime >= " -// + dateQueryAdapter.dateAdapter(startDay) -// + " and i.sendTime <= " -// + dateQueryAdapter.dateAdapter(endDay) -// + " and i.status != '" + Invoice.STATUS_UNDELIVERED + "'"; + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateAdapter(startDay), + dateQueryAdapter.dateAdapter(endDay)); + params.querySupplyRoom = querySupplyRoom; + params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + params.includeTousses = false; + params.includeDisposableGoods = true; + + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("发货数量", params) + + ") tl "); - String sql = "select sum(i.diposablegoodsamount) from invoice i " - + "where i.orgUnitCoding = '"+querySupplyRoom+"'" - + " and i.sendTime >= " - + dateQueryAdapter.dateAdapter(startDay) - + " and i.sendTime <= " - + dateQueryAdapter.dateAdapter(endDay) - + " and i.status != '" + Invoice.STATUS_UNDELIVERED + "' and i.diposablegoodsamount>0 "; - bean.setTitle("发放无菌物品总件数（一次性物品）"); bean.setRowNum(list.size()+1); Integer amount = 0; @@ -6202,32 +6180,18 @@ * @throws SQLException */ private void getSendDiposableGoodsAmountAllYear( - String year, - List list,String querySupplyRoom) throws SQLException { + List list,ReportQueryParams reportParams) throws SQLException { WorkQualityCollection bean = new WorkQualityCollection(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; -// String sql = "select " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm") -// + " monthstr, sum(i.diposablegoodsamount) from invoice i,invoiceplan p where i.invoiceplan_id = p.id" -// + " and i.orgUnitCoding = '"+querySupplyRoom+"'" -// + " and p.type = '" -// + InvoicePlan.TYPE_DIPOSABLE_GOODS_APPLICATION_FORM -// + "' and i.sendTime >= " -// + dateQueryAdapter.dateAdapter(queryYear) -// + " and i.sendTime <= " -// + dateQueryAdapter.dateAdapter(nextYear) -// + " and i.status != '" + Invoice.STATUS_UNDELIVERED + "'" -// + " group by " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); - String sql = "select " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm") - + " monthstr, sum(i.diposableGoodsAmount) from invoice i " - + "where i.orgUnitCoding = '"+querySupplyRoom+"'" - + " and i.sendTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and i.sendTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " and i.status != '" + Invoice.STATUS_UNDELIVERED + "' and i.diposableGoodsAmount>0 " - + " group by " + dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("i.sendTime","mm"); + reportParams.tousseTypeAndPackageSizeSql = ""; + reportParams.includeTousses = false; + reportParams.includeDisposableGoods = true; + + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByPackageSQL("年度报表中的发货数量（按包）", + reportParams) + ") tl group by monthstr "; + ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("发放无菌物品总件数（一次性物品）"); @@ -6413,16 +6377,22 @@ startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } - String sql = "select sum(t.statisticsamount) from tousseinstance t,toussedefinition d where " - + "t.toussedefinition_id = d.id and t.operationTime >= " - + dateQueryAdapter.dateAdapter(startDay) - + " and t.operationTime <= " - + dateQueryAdapter.dateAdapter(endDay) - + " and d.toussetype = '" - + TousseDefinition.PACKAGE_TYPE_DRESSING + "'"; - if (StringUtils.isNotBlank(querySupplyRoom)) { - sql += " and t.orgunitcoding = '" + querySupplyRoom + "'"; - } + + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateAdapter(startDay), + dateQueryAdapter.dateAdapter(endDay)); + params.querySupplyRoom = querySupplyRoom; + params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + + params.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_DRESSING, null); + + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("配包数量", params) + + ") tl "); + ResultSet rs = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("处理敷料总件数"); @@ -6444,22 +6414,19 @@ } // 处理敷料总件数 全年数据的获取 private void getDressingAmountAllYear( - String year, - List list,String querySupplyRoom) throws SQLException { + List list,ReportQueryParams reportParams) throws SQLException { WorkQualityCollection bean = new WorkQualityCollection(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql = "select " + dateQueryAdapter.dateConverAdapter3("t.operationTime","mm") - + " monthstr, sum(t.statisticsamount) from tousseinstance t,toussedefinition d where " - + "t.toussedefinition_id = d.id and t.operationTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and t.operationTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - +" and t.orgUnitCoding = '" + querySupplyRoom + "'" - + " and d.toussetype = '" - + TousseDefinition.PACKAGE_TYPE_DRESSING + "'" - + " group by " + dateQueryAdapter.dateConverAdapter3("t.operationTime","mm"); + + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("ti.operationTime","mm"); + reportParams.tousseTypeAndPackageSizeSql = DataIndex + .getTousseTypesAndPackageSizesFilterSQL( + TousseDefinition.PACKAGE_TYPE_DRESSING, null); + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByMaterialSQL("年度报表中的配包数量（按材料）", + reportParams) + ") tl group by monthstr "; + ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("处理敷料总件数"); @@ -6619,13 +6586,16 @@ startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } - String sql = "select count(*) from tousseinstance t where t.operationTime >= " - + dateQueryAdapter.dateAdapter(startDay) - + " and t.operationTime <= " - + dateQueryAdapter.dateAdapter(endDay); - if (StringUtils.isNotBlank(querySupplyRoom)) { - sql += " and t.orgunitcoding = '" + querySupplyRoom + "'"; - } + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateAdapter(startDay), + dateQueryAdapter.dateAdapter(endDay)); + params.querySupplyRoom = querySupplyRoom; + params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("配包数量", params) + + ") tl "); bean.setRowNum(list.size()+1); bean.setTitle("CSSD包装物品总包数"); @@ -6648,18 +6618,17 @@ } // CSSD包装物品总包数 全年数量 private void getPackingAmountAllYear( - String year, - List list,String querySupplyRoom) throws SQLException { + List list,ReportQueryParams reportParams) throws SQLException { WorkQualityCollection bean = new WorkQualityCollection(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql = "select " + dateQueryAdapter.dateConverAdapter3("t.operationTime","mm") - + " monthstr, count(*) from tousseinstance t where t.operationTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and t.operationTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - +" and t.orgUnitCoding = '" + querySupplyRoom + "'" - + " group by " + dateQueryAdapter.dateConverAdapter3("t.operationTime","mm"); + + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("ti.operationTime","mm"); + reportParams.tousseTypeAndPackageSizeSql = ""; + + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByPackageSQL("年度报表中的配包数量（按包）", + reportParams) + ") tl group by monthstr "; + ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("CSSD包装物品总包数"); @@ -6696,15 +6665,16 @@ startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } - String sql = "select sum(mi.count) from tousseinstance t,TousseDefinition td,MaterialInstance mi " - +" where t.tousseDefinition_id = td.id " - +" and td.id = mi.tousse_id and t.operationTime >= " - + dateQueryAdapter.dateAdapter(startDay) - + " and t.operationTime <= " - + dateQueryAdapter.dateAdapter(endDay); - if (StringUtils.isNotBlank(querySupplyRoom)) { - sql += " and t.orgunitcoding = '" + querySupplyRoom + "'"; - } + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateAdapter(startDay), + dateQueryAdapter.dateAdapter(endDay)); + params.querySupplyRoom = querySupplyRoom; + params.sqlLengthFunctionName = DatabaseUtil.getSqlLengthFunctionName(dbConnection); + + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("配包数量", params) + + ") tl "); bean.setRowNum(list.size()+1); bean.setTitle("CSSD包装物品总件数"); @@ -6727,20 +6697,17 @@ } // CSSD包装物品总件数 全能数量 private void getPackingTousseMaterialAmountAllYear( - String year, - List list,String querySupplyRoom) throws SQLException { + List list,ReportQueryParams reportParams) throws SQLException { WorkQualityCollection bean = new WorkQualityCollection(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql ="select " + dateQueryAdapter.dateConverAdapter3("t.operationTime","mm") - + " monthstr, sum(mi.count) from tousseinstance t,TousseDefinition td,MaterialInstance mi " - +" where t.tousseDefinition_id = td.id " - +" and t.orgUnitCoding = '" + querySupplyRoom + "'" - +" and td.id = mi.tousse_id and t.operationTime >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and t.operationTime <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " group by " + dateQueryAdapter.dateConverAdapter3("t.operationTime","mm"); + + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("ti.operationTime","mm"); + reportParams.tousseTypeAndPackageSizeSql = ""; + + String sql = "select monthstr,sum(tl.amount) from (" + + DataIndex.getWorkAmountByMaterialSQL("年度报表中的配包数量（按材料）", + reportParams) + ") tl group by monthstr "; + ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("CSSD包装物品总件数"); @@ -6868,12 +6835,18 @@ startDay = keyOfInnerMap; endDay = innerMap.get(keyOfInnerMap); } - String sql = "select sum(r.generalMaterialAmount+r.operateMaterialAmount+r.foreignMaterialAmount) from washanddisinfectrecord r " - + "where r.orgUnitCoding = '"+querySupplyRoom+"' and r.endDate >= " - + dateQueryAdapter.dateAdapter(startDay) - + " and r.endDate <= " - + dateQueryAdapter.dateAdapter(endDay); + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = String.format(" between %s and %s ", + dateQueryAdapter.dateAdapter(startDay), + dateQueryAdapter.dateAdapter(endDay)); + params.querySupplyRoom = querySupplyRoom; + + + String sql = String.format("select sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + + ") tl "); + bean.setRowNum(list.size()+1); bean.setTitle("处理器械总件数"); Integer amount = 0; @@ -6895,18 +6868,15 @@ } // 工作量持续报表 获取处理器械总件数 private void getWashMaterialAmountAllYear( - String year, - List list,String querySupplyRoom) throws SQLException { + List list,ReportQueryParams reportParams) throws SQLException { WorkQualityCollection bean = new WorkQualityCollection(); - String queryYear = year + "-01-01 00:00:00"; - String nextYear = getNextYear(year) + " 00:00:00"; - String sql = "select " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm") - + " monthstr, sum(r.generalMaterialAmount+r.operateMaterialAmount+r.foreignMaterialAmount) from washanddisinfectrecord r " - + "where r.orgUnitCoding = '"+querySupplyRoom+"' and r.endDate >= " - + dateQueryAdapter.dateAdapter(queryYear) - + " and r.endDate <= " - + dateQueryAdapter.dateAdapter(nextYear) - + " group by " + dateQueryAdapter.dateConverAdapter3("r.endDate","mm"); + + reportParams.monthlyStr = dateQueryAdapter.dateConverAdapter3("wr.endDate","mm"); + + String sql = "select monthstr,sum(tl.materialCount) from (" + + DataIndex.getWorkAmountByMaterialSQL("年度报表中的清洗数量（按材料）", + reportParams) + ") tl group by monthstr "; + ResultSet result = objectDao.executeSql(sql); bean.setRowNum(list.size()+1); bean.setTitle("处理器械总件数"); @@ -9757,7 +9727,7 @@ /** * 获取“消毒供应中心员工工作量统计报表”的数据.. * @param startTime 统计的开始时间（精确到天如：2017-01-01） - * @param endTime 统计的结束时间（精确到天如：2017-01-01） + * @param endTime 统计的结束时间（精确到天如：2017-01-31） * @param querySupplyRoom 被统计消毒供应中心的科室编码（包括一级或者二级供应中心） * @return */ @@ -9830,106 +9800,97 @@ } String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); + + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = betweenSql; + params.querySupplyRoom = querySupplyRoom; + params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; + params.tousseTypeAndPackageSizeSql = tousseTypeAndPackageSizeSql; + params.sqlLengthFunctionName = sqlLengthFunctionName; + params.tousseTypes = tousseTypes; + + + //按包数量统计的SQL //回收记录 - String tousseAmountSql = String.format("select '回收数量' type,rr.recyclingUser userName,sum(ti.amount) amount from RecyclingRecord rr,RecyclingItem ti,TousseDefinition td " - + "where ti.recyclingRecord_id = rr.id and ti.tousseDefinitionId=td.id " - + "and rr.orgUnitCoding = '%s' and rr.recyclingTime %s and %s(rr.recyclingUser) <> 0 %s " - + "group by rr.recyclingUser ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + String tousseAmountSql = String.format("select '回收数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("回收数量", params) + + ") tl group by tl.userName "); tousseAmountSql += " union all "; //清点记录 - tousseAmountSql += String.format("select '清点数量' type,rr.operator userName,sum(ti.amount) amount from RecyclingRecord rr,RecyclingItem ti,TousseDefinition td " - + "where ti.recyclingRecord_id = rr.id and ti.tousseDefinitionId=td.id " - + "and rr.orgUnitCoding = '%s' and rr.recyclingTime %s and %s(rr.operator) <> 0 %s " - + "group by rr.operator ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + tousseAmountSql += String.format("select '清点数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("清点数量", params) + + ") tl group by tl.userName "); tousseAmountSql += " union all "; //清洗数量 - tousseAmountSql += "select '清洗数量' type,t1.userName userName,sum(t1.tousseAmount) amount from ("; - tousseAmountSql += DataIndex.getWashAmountByPackageSQL(supplyRoomConfigManager, dateQueryAdapter, querySupplyRoom, - startDateTime, endDateTime, tousseTypes, null ,null,null,packageSizes); + tousseAmountSql += String.format("select '清洗数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("清洗数量", params) + + ") tl group by tl.userName "); - - tousseAmountSql += ") t1 group by t1.userName "; - - tousseAmountSql += " union all "; //装配数量（按包统计） - String packingAmountByPackageSql = String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount from (" - +DataIndex.getPackingAmountByPackageSQL(supplyRoomConfigManager,dateQueryAdapter,querySupplyRoom, - startDateTime, endDateTime, tousseTypes, null, - null, null, packageSizes, sqlLengthFunctionName) + tousseAmountSql += String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("配包数量", params) + ") tl group by tl.userName "); - tousseAmountSql += packingAmountByPackageSql; - - //包装检查数量 + //检查数量 if(showInspectorField){ //中山六院的项目开启了 tousseAmountSql += " union all "; //检查数量 - tousseAmountSql += String.format("select '检查数量' type,pr.inspector userName,sum(pr.amount) amount from PackingRecord pr join TousseDefinition td on td.id=pr.tousseDefinitionId " - + "where pr.orgUnitCoding= '%s' and pr.packTime %s and %s(pr.inspector)<>0 %s " - + "group by pr.inspector ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + tousseAmountSql += String.format("select '检查数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("检查数量", params) + + ") tl group by tl.userName "); } - //装配记录（包装数量） + //包装数量 if (showWrapperField) { tousseAmountSql += " union all "; - tousseAmountSql += String.format("select '包装数量' type,pr.wrapper userName,sum(pr.amount) amount from PackingRecord pr join TousseDefinition td on td.id=pr.tousseDefinitionId " - + "where pr.orgUnitCoding = '%s' and pr.packTime %s and pr.wrapper is not null and %s(pr.wrapper)<>0 %s " - + "group by pr.wrapper ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + tousseAmountSql += String.format("select '包装数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("包装数量", params) + + ") tl group by tl.userName "); } tousseAmountSql += " union all "; //审核记录 -// tousseAmountSql += String.format("select '审核数量' type,ti.reviewer userName,count(*) amount from tousseInstance ti join TousseDefinition td on td.id=ti.tousseDefinition_id " -// + "where ti.orgUnitCoding = '%s' and ti.reviewTime %s and %s(ti.reviewer) <> 0 %s " -// + "group by ti.reviewer ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); - String reviewSql = String.format("select '审核数量' type,tl.userName,sum(tl.amount) amount from (") - + DataIndex.getReviewAmountByPackageSQL(querySupplyRoom, betweenSql, sqlLengthFunctionName, tousseTypeAndPackageSizeSql); - reviewSql += ") tl group by tl.userName "; - tousseAmountSql += reviewSql; + tousseAmountSql += String.format("select '审核数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("审核数量", params) + + ") tl group by tl.userName "); + tousseAmountSql += " union all "; //灭菌记录 - String sql = String.format("select '灭菌数量' type,tl.userName,sum(tl.amount) amount from (" - +DataIndex.getWorkAmountByPackageSQL("灭菌数量", supplyRoomConfigManager,dateQueryAdapter,querySupplyRoom, - startDateTime, endDateTime, tousseTypes, null, - null, null, packageSizes, sqlLengthFunctionName) + tousseAmountSql += String.format("select '灭菌数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + ") tl group by tl.userName "); - tousseAmountSql += sql; - tousseAmountSql += " union all "; //发货记录 - sql = String.format("select '发货数量' type,tl.userName,sum(tl.amount) amount from (" - +DataIndex.getWorkAmountByPackageSQL("发货数量", supplyRoomConfigManager,dateQueryAdapter,querySupplyRoom, - startDateTime, endDateTime, tousseTypes, null, - null, null, packageSizes, sqlLengthFunctionName) + tousseAmountSql += String.format("select '发货数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("发货数量", params) + ") tl group by tl.userName "); - tousseAmountSql += sql; tousseAmountSql += " union all "; //核对记录 - tousseAmountSql += String.format("select '核对数量' type,i.assistantSender userName,sum(ii.amount) amount from Invoice i, InvoiceItem ii,TousseDefinition td " - + "where ii.invoice_id = i.id and td.id=ii.tousseDefinitionId " - + "and i.orgUnitCoding= '%s' and i.sendTime %s and %s(i.assistantSender) <> 0 %s " - + "group by i.assistantSender ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + tousseAmountSql += String.format("select '核对数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("核对数量", params) + + ") tl group by tl.userName "); //发货下送责任人 if(showInvoicePersonInCharge){ tousseAmountSql += " union all "; //下送数量 - tousseAmountSql += String.format("select '下送数量' type,i.personInCharge userName,sum(ii.amount) amount from Invoice i, InvoiceItem ii,TousseDefinition td " - + "where ii.invoice_id = i.id and td.id=ii.tousseDefinitionId " - + "and i.orgUnitCoding= '%s' and i.sendTime %s and %s(i.personInCharge) <> 0 %s " - + "group by i.personInCharge ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + tousseAmountSql += String.format("select '下送数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByPackageSQL("下送数量", params) + + ") tl group by tl.userName "); } @@ -9967,118 +9928,84 @@ // 按材料数量统计的SQL //回收记录 - String materialAmountSql = String.format("select '回收数量' type,rr.recyclingUser userName,sum(ti.amount * ms.count) amount from RecyclingRecord rr,RecyclingItem ti,tousseDefinition td,materialInstance ms " - + "where ti.recyclingRecord_id = rr.id and ti.tousseDefinitionId = td.id and td.id = ms.tousse_id and rr.orgUnitCoding = '%s' and rr.recyclingTime %s and %s(rr.recyclingUser) <> 0 %s " - + "group by rr.recyclingUser ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + String materialAmountSql = String.format("select '回收数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("回收数量", params) + + ") tl group by tl.userName "); materialAmountSql += " union all "; //清点记录 - materialAmountSql += String.format("select '清点数量' type,rr.operator userName,sum(ti.amount * ms.count) amount from RecyclingRecord rr,RecyclingItem ti,tousseDefinition td,materialInstance ms " - + "where ti.recyclingRecord_id = rr.id and ti.tousseDefinitionId = td.id and td.id = ms.tousse_id and rr.orgUnitCoding = '%s' and rr.recyclingTime %s and %s(rr.operator) <> 0 %s " - + "group by rr.operator ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + materialAmountSql += String.format("select '清点数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("清点数量", params) + + ") tl group by tl.userName "); materialAmountSql += " union all "; //清洗记录 - String washAmountSql = "select '清洗数量' type,t1.userName userName,sum(t1.materialCount) amount from ("; - washAmountSql += DataIndex.getWashAmountByMaterialSQL(supplyRoomConfigManager, dateQueryAdapter, querySupplyRoom, startDateTime , endDateTime,tousseTypes, - null, null, null, packageSizes); - - washAmountSql += ") t1 group by t1.userName "; + materialAmountSql += String.format("select '清洗数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + + ") tl group by tl.userName "); - materialAmountSql += washAmountSql; - materialAmountSql += "union all "; //装配记录（配包数量） -// String packingAmountByMaterial = String.format("select '配包数量' type,pr.packer userName,sum(pr.amount * ms.count) amount from PackingRecord pr,tousseDefinition td,materialInstance ms " -// + "where pr.tousseDefinitionId = td.id and td.id = ms.tousse_id and pr.orgUnitCoding = '%s' and pr.packTime %s and pr.packer is not null and %s(pr.packer)<>0 %s " -// + "group by pr.packer ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + materialAmountSql += String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("配包数量", params) + + ") tl group by tl.userName "); - String packingAmountByMaterial = "select '配包数量' type,t1.userName userName,sum(amount) amount from ("; - packingAmountByMaterial += DataIndex.getPackingAmountByMaterialSQL(supplyRoomConfigManager, dateQueryAdapter, querySupplyRoom, startDateTime , endDateTime,tousseTypes, - null, null, null, packageSizes, sqlLengthFunctionName); - packingAmountByMaterial += ") t1 group by t1.userName "; - - materialAmountSql += packingAmountByMaterial; - // 包装检查数量 if(showInspectorField){ //中山六院的项目开启了 materialAmountSql += " union all "; //检查数量 - materialAmountSql += String.format("select '检查数量' type,pr.inspector userName,sum(pr.amount * ms.count) amount from PackingRecord pr,tousseDefinition td,materialInstance ms " - + "where pr.tousseDefinitionId = td.id and td.id = ms.tousse_id and pr.orgUnitCoding= '%s' and pr.packTime %s and pr.inspector is not null and %s(pr.inspector)<>0 %s " - + "group by pr.inspector ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + materialAmountSql += String.format("select '检查数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("检查数量", params) + + ") tl group by tl.userName "); } //装配记录（包装数量） if (showWrapperField) { materialAmountSql += " union all "; - materialAmountSql += String.format("select '包装数量' type,pr.wrapper userName,sum(pr.amount * ms.count) amount from PackingRecord pr,tousseDefinition td,materialInstance ms " - + "where pr.tousseDefinitionId = td.id and td.id = ms.tousse_id and pr.orgUnitCoding = '%s' and pr.packTime %s and pr.wrapper is not null and %s(pr.wrapper)<>0 %s " - + "group by pr.wrapper ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + materialAmountSql += String.format("select '包装数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("包装数量", params) + + ") tl group by tl.userName "); } materialAmountSql += "union all "; //审核记录 - String reviewAmountByMaterialSql = String.format("select '审核数量' type,ti.reviewer userName,sum(ms.count) amount from tousseInstance ti,tousseDefinition td,materialInstance ms " - + "where ti.tousseDefinition_id = td.id and td.id = ms.tousse_id and ti.orgUnitCoding = '%s' and ti.reviewTime %s and ti.reviewer is not null and %s(ti.reviewer) <> 0 %s " - + "group by ti.reviewer ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + String reviewAmountByMaterialSql = String.format("select '审核数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("审核数量", params) + + ") tl group by tl.userName "); materialAmountSql += reviewAmountByMaterialSql; materialAmountSql += " union all "; - materialAmountSql += "select '灭菌数量' type,tmp.userName,sum(tmp.amount) from ("; - materialAmountSql += String.format("select sr.sterilizationUser userName,sum(ms.count) amount from SterilizationRecord sr,sterilization_tousseInstance st,TousseInstance ti,tousseDefinition td,materialInstance ms " - +" where sr.id = st.sterilizationRecord_id and st.tousseInstance_id = ti.id and ti.tousseDefinition_id = td.id and td.id = ms.tousse_id and sr.orgUnitCoding = '%s' and sr.endDate %s " - + "and %s(sr.sterilizationUser) <> 0 %s group by sr.sterilizationUser ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); - materialAmountSql += " union all "; - materialAmountSql += String.format("select sr.sterilizationUser userName,sum(ms.count) amount from SterilizationRecord sr,sterilization_reviewed sre,ReviewedBasket rb,TousseInstance ti,tousseDefinition td,materialInstance ms " - +" where sr.id = sre.sterilizationRecord_id and sre.reviewedBasket_id = rb.id and rb.id = ti.reviewBasket_ID and ti.tousseDefinition_id = td.id and td.id = ms.tousse_id and sr.orgUnitCoding = '%s' " - + "and sr.endDate %s and %s(sr.sterilizationUser) <> 0 %s group by sr.sterilizationUser ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); - - materialAmountSql += ")tmp group by tmp.userName"; + materialAmountSql += String.format("select '灭菌数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("灭菌数量", params) + + ") tl group by tl.userName "); materialAmountSql += " union all "; - materialAmountSql += "select '发货数量' type,tmp.userName,sum(tmp.amount) from ("; - materialAmountSql += String.format("select i.sender userName,sum(ms.count) amount from Invoice i,tousseInstance ti,tousseDefinition td,materialInstance ms " - + "where ti.invoice_id = i.id and ti.tousseDefinition_id = td.id and td.id = ms.tousse_id and i.orgUnitCoding = '%s' and i.sendTime %s and %s(i.sender) <> 0 %s " - + "group by i.sender ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); + // 包含一次性物品的发货统计 + params.includeDisposableGoods = false; + materialAmountSql += String.format("select '发货数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("发货数量", params) + + ") tl group by tl.userName "); materialAmountSql += " union all "; - materialAmountSql += String.format("select i.sender userName,sum(ii.amount) amount from Invoice i,invoiceItem ii " - + "where ii.invoice_id = i.id and i.orgUnitCoding = '%s' and ii.tousseType = '一次性物品' and i.sendTime %s and %s(i.sender) <> 0 " - + "group by i.sender ", querySupplyRoom,betweenSql,sqlLengthFunctionName); - materialAmountSql += ")tmp group by tmp.userName"; - materialAmountSql += " union all "; - materialAmountSql += "select '核对数量' type,tmp.userName,sum(tmp.amount) from ("; - materialAmountSql += String.format("select i.assistantSender userName,sum(ms.count) amount from Invoice i,tousseInstance ti,tousseDefinition td,materialInstance ms " - + "where ti.invoice_id = i.id and ti.tousseDefinition_id = td.id and td.id = ms.tousse_id and i.orgUnitCoding = '%s' and i.sendTime %s and %s(i.assistantSender) <> 0 %s " - + "group by i.assistantSender ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); - materialAmountSql += " union all "; - materialAmountSql += String.format("select i.assistantSender userName,sum(ii.amount) amount from Invoice i,invoiceItem ii " - + "where ii.invoice_id = i.id and i.orgUnitCoding = '%s' and ii.tousseType = '一次性物品' and i.sendTime %s and %s(i.assistantSender) <> 0 " - + "group by i.assistantSender ", querySupplyRoom,betweenSql,sqlLengthFunctionName); - materialAmountSql += ")tmp group by tmp.userName"; + materialAmountSql += String.format("select '核对数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("核对数量", params) + + ") tl group by tl.userName "); //发货下送责任人 if(showInvoicePersonInCharge){ materialAmountSql += " union all "; //下送数量 - materialAmountSql += "select '下送数量' type,tmp.userName,sum(tmp.amount) from ("; - materialAmountSql += String.format("select i.personInCharge userName,sum(ms.count) amount from Invoice i,tousseInstance ti,tousseDefinition td,materialInstance ms " - + "where ti.invoice_id = i.id and ti.tousseDefinition_id = td.id and td.id = ms.tousse_id and i.orgUnitCoding = '%s' and i.sendTime %s and %s(i.personInCharge) <> 0 %s " - + "group by i.personInCharge ", querySupplyRoom,betweenSql,sqlLengthFunctionName,tousseTypeAndPackageSizeSql); - materialAmountSql += " union all "; - materialAmountSql += String.format("select i.personInCharge userName,sum(ii.amount) amount from Invoice i,invoiceItem ii " - + "where ii.invoice_id = i.id and i.orgUnitCoding = '%s' and ii.tousseType = '一次性物品' and i.sendTime %s and %s(i.personInCharge) <> 0 " - + "group by i.personInCharge ", querySupplyRoom,betweenSql,sqlLengthFunctionName); - materialAmountSql += ")tmp group by tmp.userName"; + materialAmountSql += String.format("select '下送数量' type,tl.userName,sum(tl.amount) amount from (" + +DataIndex.getWorkAmountByMaterialSQL("下送数量", params) + + ") tl group by tl.userName "); } @@ -12297,11 +12224,13 @@ String endTime, String departCoding, String tousseTypes, String operator, String isDisableIDCard, String taskGroup, String tousseGroup) { long begin = System.currentTimeMillis(); + String startDateTime = startTime; if(StringUtils.isNotBlank(startTime) && startTime.trim().length() == 16){ - startTime = startTime + ":00"; + startDateTime = startTime + ":00"; } + String endDateTime = endTime; if(StringUtils.isNotBlank(endTime) && endTime.trim().length() == 16){ - endTime = endTime + ":59"; + endDateTime = endTime + ":59"; } String dateFormatter = "yyyy-mm-dd HH24:MI:SS"; @@ -12332,133 +12261,83 @@ includeForeignTousse = true; } - boolean notSignedItemsCanRegistUseRecord = CssdUtils.getSystemSetConfigByNameBool("notSignedItemsCanRegistUseRecord"); //已发货未签收的物品，是否允许登记使用记录 - String signedTimeString = "tis.invoiceSendTime"; - if (!notSignedItemsCanRegistUseRecord) { - signedTimeString = "tis.signedDate"; - } - SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj(); + + //查询的时间段 + String betweenSql = String.format(" between %s and %s ", dateQueryAdapter.dateAdapter(startDateTime),dateQueryAdapter.dateAdapter(endDateTime)); + //是否禁用标识牌的过滤(分别用于器械包定义别名为td及ti的过滤条件) - String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = ""; - String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi = ""; - //假条件 - String isDisableIDCardFalseSqlCondition = ""; - if (StringTools.isNotBlank(isDisableIDCard)) { - if (StringTools.equals(isDisableIDCard, Constants.STR_NO)) { - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = String.format(" and td.isDisableIDCard='%s' ", Constants.STR_NO); - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi = String.format(" and ti.isDisableIDCard='%s' ", Constants.STR_NO); - isDisableIDCardFalseSqlCondition = " and 1=2 "; - } else { - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = String.format(" and (td.isDisableIDCard='%s' or td.isDisableIDCard='' or td.isDisableIDCard is null) ", Constants.STR_YES); - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi = String.format(" and (ti.isDisableIDCard='%s' or ti.isDisableIDCard='' or ti.isDisableIDCard is null) ", Constants.STR_YES); - } - } + String isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = DataIndex.getIsDisableIDCardFilterSQL(isDisableIDCard); //任务组的过滤 - String taskGroupSqlWithAliasOfTousseDefinitionIsTd = ""; - String taskGroupSqlWithAliasOfTousseDefinitionIsTi = ""; - String taskGroupFalseSqlCondition = ""; - if (StringTools.isNotBlank(taskGroup)) { - taskGroupSqlWithAliasOfTousseDefinitionIsTd = String.format(" and td.taskGroup='%s' ", taskGroup); - taskGroupSqlWithAliasOfTousseDefinitionIsTi = String.format(" and ti.taskGroup='%s' ", taskGroup); - SupplyRoomConfig supplyRoomConfig = supplyRoomConfigManager.getSystemParamsObj(); - //外来器械包默认处理科室及任务组 - JSONObject foreignTousseHandleDepartAndTaskGroupJsonobject = supplyRoomConfig.getOneForeignTousseHandleDepartAndTaskGroupByDepartCode(AcegiHelper.getCurrentOrgUnitCode()); - if (foreignTousseHandleDepartAndTaskGroupJsonobject == null || !StringTools.equals(taskGroup, foreignTousseHandleDepartAndTaskGroupJsonobject.optString("taskGroup"))) { - taskGroupFalseSqlCondition = " and 1=2 "; - } - } + String taskGroupSqlWithAliasOfTousseDefinitionIsTd = DataIndex.getTaskGroupFilterSQL(supplyRoomConfigManager,taskGroup); //器械包分组的过滤 - String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = ""; - String tousseGroupSqlWithAliasOfTousseDefinitionIsTi = ""; + String tousseGroupSqlWithAliasOfTousseDefinitionIsTd = DataIndex.getTousseGroupFilterSQL(tousseGroup); + + + //按器械包类型和器械包大小过滤 + String tousseTypeSql = DataIndex.getTousseTypesFilterSQL(tousseTypes); + + String packageSizeSql = DataIndex.getPackageSizeFilterSQL(null); + + String tousseTypeAndPackageSizeSql = String.format(" and %s and %s ", tousseTypeSql, packageSizeSql); + + ReportQueryParams params = new ReportQueryParams(); + params.betweenSql = betweenSql; + params.querySupplyRoom = departCoding; + params.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd = isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd; + params.taskGroupSqlWithAliasOfTousseDefinitionIsTd = taskGroupSqlWithAliasOfTousseDefinitionIsTd; + params.tousseGroupSqlWithAliasOfTousseDefinitionIsTd = tousseGroupSqlWithAliasOfTousseDefinitionIsTd; + params.tousseTypeAndPackageSizeSql = tousseTypeAndPackageSizeSql; + params.sqlLengthFunctionName = sqlLengthFunctionName; + + String isDisableIDCardFalseSqlCondition = ""; + String taskGroupFalseSqlCondition = ""; String tousseGroupFalseSqlCondition = ""; - if (StringTools.isNotBlank(tousseGroup)) { - tousseGroupSqlWithAliasOfTousseDefinitionIsTd = String.format(" and td.tousseGroupName='%s' ", tousseGroup); - tousseGroupSqlWithAliasOfTousseDefinitionIsTi = String.format(" and ti.tousseGroupName='%s' ", tousseGroup); - tousseGroupFalseSqlCondition = " and 1=2 "; - } //申请数量（按包统计） - String sql = String.format("select 'application' as type,ti.tousseName,sum(ti.amount),ti.tousseType from invoicePlan ip,TousseItem ti,TousseDefinition td " - +" where ti.recyclingApplication_ID=ip.id and td.id=ti.tousseDefinitionId %s %s %s " - + "and ip.handleDepartCoding = '%s' and ip.applicationTime between %s and %s and ti.tousseType<>'一次性物品' %s " - + "%s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - departCoding,startDateSql,endDateSql,tousseTypeSqlWithAliasOfTousseDefinitionIsTd,DataIndex.getAndSql("ip.applicant", operator)); + String sql = String.format("select 'application' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + +DataIndex.getWorkAmountByPackageSQL("申请数量", params) + + ") tl group by tl.tousseType,tl.tousseName "); //回收数量（按包统计） sql += " union all "; - sql += String.format("select 'recyc' as type,ti.tousseName,sum(ti.amount),ti.tousseType from recyclingRecord rr,RecyclingItem ti,TousseDefinition td " - +" where rr.id = ti.recyclingRecord_id and td.id=ti.tousseDefinitionId %s %s %s and rr.orgUnitCoding = '%s' and rr.recyclingTime between %s and %s " - + "%s %s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - departCoding,startDateSql,endDateSql,DataIndex.getAndSql("rr.recyclingUser", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTd); + sql += String.format("select 'recyc' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + +DataIndex.getWorkAmountByPackageSQL("回收数量", params) + + ") tl group by tl.tousseType,tl.tousseName "); //清点数量（按包统计） sql += " union all "; - sql += String.format("select 'inventory' as type,ti.tousseName,sum(ti.amount),ti.tousseType from recyclingRecord rr,RecyclingItem ti,TousseDefinition td " - +" where rr.id = ti.recyclingRecord_id and td.id=ti.tousseDefinitionId %s %s %s and rr.orgUnitCoding = '%s' and rr.recyclingTime between %s and %s " - + "%s %s group by ti.tousseType,ti.tousseName ", - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - departCoding,startDateSql,endDateSql,DataIndex.getAndSql("rr.operator", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTd); + sql += String.format("select 'inventory' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + +DataIndex.getWorkAmountByPackageSQL("清点数量", params) + + ") tl group by tl.tousseType,tl.tousseName "); - //清洗数量（按包统计） -// sql += " union all "; -// String wash = String.format("select 'wash' as type,ti.tousseName,sum(ti.tousseAmount),ti.tousseType from (" -// + "select ci.name as tousseName,ci.amount as tousseAmount,ci.itemType as tousseType," -// + "case when (cb.personInCharge is null or cb.personInCharge = '') then wdr.operator else cb.personInCharge end userName " -// + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td " -// + "where wdr.id = cw.WashAndDisinfectRecord_ID and cw.ClassifyBasket_ID = cb.id and cb.id = ci.classifybasket_id " -// + "and ci.tousseDefinitionID=td.id %s %s %s " -// + "and ci.itemType != '材料' " -// + "and wdr.orgUnitCoding = '%s' and wdr.endDate between %s and %s and wdr.washMaterialAmount<>0 " -// + " union all " -// + "select min(ci.tousseNameForMaterial) as tousseName,min(ci.tousseAmountForMaterial) as tousseAmount,min(td.tousseType) as tousseType," -// + "case when (min(cb.personInCharge) is null or min(cb.personInCharge) = '') then min(wdr.operator) else min(cb.personInCharge) end userName " -// + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td " -// + "where wdr.id = cw.WashAndDisinfectRecord_ID and cw.ClassifyBasket_ID = cb.id and cb.id = ci.classifybasket_id and ci.tousseDefinitionID=td.id and ci.itemType = '材料' " -// + "%s %s %s and wdr.orgUnitCoding = '%s' and wdr.endDate between %s and %s and wdr.washMaterialAmount<>0 group by ci.tousseDefinitionID,ci.recyclingRecordId" -// + ") ti where 1=1 %s %s group by ti.tousseType,ti.tousseName ", -// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd,departCoding,startDateSql,endDateSql, -// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd,departCoding,startDateSql,endDateSql, -// getAndSql("ti.userName", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); -// sql += wash; sql += " union all "; //清洗数量（按包统计） - String wash = "select 'wash' type,tl.tousseName,sum(tl.tousseAmount) amount,tl.tousseType from(" - + DataIndex.getWashAmountByPackageSQL(supplyRoomConfigManager, dateQueryAdapter, departCoding, - startTime, endTime, tousseTypes, isDisableIDCard ,taskGroup,tousseGroup,null) - + ") tl group by tl.userName,tl.tousseType,tl.tousseName "; - sql += wash; + sql += String.format("select 'wash' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + +DataIndex.getWorkAmountByPackageSQL("清洗数量", params) + + ") tl group by tl.tousseType,tl.tousseName "); + //清洗数量（按材料统计） sql += " union all "; - String washMaterial = "select 'wash-material' as type,ti.tousseName,sum(ti.materialCount),ti.tousseType from ("; - - washMaterial += DataIndex.getWashAmountByMaterialSQL(supplyRoomConfigManager, dateQueryAdapter, departCoding, startTime , endTime,tousseTypes, - isDisableIDCard,taskGroup, tousseGroup, null); - - washMaterial += String.format(") ti where 1=1 %s %s group by ti.tousseType,ti.tousseName ",DataIndex.getAndSql("ti.userName", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); - sql += washMaterial; + sql += String.format("select 'wash-material' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + +DataIndex.getWorkAmountByMaterialSQL("清洗数量", params) + + ") tl group by tl.tousseType,tl.tousseName "); + //装配数量（按包统计） sql += " union all "; - String packingAmountSql = String.format("select 'packing' as type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" - +DataIndex.getPackingAmountByPackageSQL(supplyRoomConfigManager,dateQueryAdapter,departCoding, - startTime, endTime, tousseTypes, isDisableIDCard, - taskGroup, tousseGroup, null, sqlLengthFunctionName) + sql += String.format("select 'packing' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + +DataIndex.getWorkAmountByPackageSQL("配包数量", params) + ") tl group by tl.tousseType,tl.tousseName "); - sql += packingAmountSql; - - //审核数量（按包统计） sql += " union all "; // TODO:以下的代码加了包定义中是否统计审核的工作量的判断，需要考虑该属性是否需要 @@ -12469,101 +12348,30 @@ // isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, // departCoding,startDateSql,endDateSql,Constants.STR_YES,Constants.STR_YES,getAndSql("t.reviewer", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); - String reviewSql = String.format("select 'review' type,tl.tousseName,tl.amount,tl.tousseType from (") - + DataIndex.getReviewAmountByPackageSQL(departCoding, String.format(" between %s and %s ",startDateSql,endDateSql), - sqlLengthFunctionName, String.format(" %s %s %s %s",isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd, - tousseGroupSqlWithAliasOfTousseDefinitionIsTd,tousseTypeSqlWithAliasOfTousseDefinitionIsTd)); - reviewSql += ") tl "; - sql += reviewSql; + sql += String.format("select 'review' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + +DataIndex.getWorkAmountByPackageSQL("审核数量", params) + + ") tl group by tl.tousseType,tl.tousseName "); //灭菌数量（按包统计） sql += " union all "; - String sterileSql = String.format("select 'sterilization' as type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" - +DataIndex.getWorkAmountByPackageSQL("灭菌数量", supplyRoomConfigManager,dateQueryAdapter,departCoding, - startTime, endTime, tousseTypes, isDisableIDCard, - taskGroup, tousseGroup, null, sqlLengthFunctionName) + sql += String.format("select 'sterilization' type,tl.tousseName,sum(tl.amount) amount ,tl.tousseType from (" + +DataIndex.getWorkAmountByPackageSQL("灭菌数量", params) + ") tl group by tl.tousseType,tl.tousseName "); -// String sterileSql = String.format("select 'sterilization' as type,tis.tousseName,count(*),ti.tousseType from SterilizationRecord sr," -// + "sterilization_tousseInstance st,TousseInstance tis,TousseDefinition ti " -// +" where sr.id = st.sterilizationRecord_id and st.tousseInstance_id = tis.id and tis.tousseDefinition_id=ti.id " -// +" %s %s %s and sr.orgUnitCoding = '%s' and sr.endDate between %s and %s and tis.foreignTousseApp_id is null " -// + "%s %s group by ti.tousseType,tis.tousseName ", -// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, -// departCoding,startDateSql,endDateSql,DataIndex.getAndSql("sr.sterilizationUser", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); -// if (includeForeignTousse) { -// sterileSql += " union all "; -// sterileSql += String.format("select 'sterilization' as type,fta.tousseName,count(*),'外来器械包' from SterilizationRecord sr," -// + "sterilization_tousseInstance st,TousseInstance ti,ForeignTousseApplication fta " -// +" where sr.id = st.sterilizationRecord_id and st.tousseInstance_id = ti.id and ti.foreignTousseApp_id=fta.id " -// +" %s %s %s and sr.orgUnitCoding = '%s' and sr.endDate between %s and %s " -// + "%s group by fta.tousseName ", -// isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, -// departCoding,startDateSql,endDateSql,DataIndex.getAndSql("sr.sterilizationUser", operator)); -// } -// -// sterileSql += " union all "; -// sterileSql += String.format("select 'sterilization' as type,tis.tousseName,count(*),ti.tousseType from SterilizationRecord sr," -// + "sterilization_reviewed sre,ReviewedBasket rb,TousseInstance tis,TousseDefinition ti " -// +" where sr.id = sre.sterilizationRecord_id and sre.reviewedBasket_id = rb.id and rb.id = tis.reviewBasket_ID and tis.tousseDefinition_id=ti.id " -// +" %s %s %s and sr.orgUnitCoding = '%s' and sr.endDate between %s and %s and tis.foreignTousseApp_id is null " -// + "%s %s group by ti.tousseType,tis.tousseName", -// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, -// departCoding,startDateSql,endDateSql,DataIndex.getAndSql("sr.sterilizationUser", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); -// if (includeForeignTousse) { -// sterileSql += " union all "; -// sterileSql += String.format("select 'sterilization' as type,fta.tousseName,count(*),'外来器械包' from SterilizationRecord sr,sterilization_reviewed sre,ReviewedBasket rb,TousseInstance ti,ForeignTousseApplication fta " -// +" where sr.id = sre.sterilizationRecord_id and sre.reviewedBasket_id = rb.id and rb.id = ti.reviewBasket_ID and ti.foreignTousseApp_id=fta.id " -// +" %s %s %s and sr.orgUnitCoding = '%s' and sr.endDate between %s and %s " -// + "%s group by fta.tousseName ", -// isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, -// departCoding,startDateSql,endDateSql,DataIndex.getAndSql("sr.sterilizationUser", operator)); -// } - - sql += sterileSql; - //发货数量（按包统计） sql += " union all "; -// sql += String.format("select 'invoice' as type,ti.tousseName,sum(ti.amount) amount,ti.tousseType from Invoice i join InvoiceItem ti on ti.invoice_id=i.id " -// + "join TousseDefinition td on td.id=ti.tousseDefinitionId " -// + "where 1=1 %s %s %s and i.orgUnitCoding = '%s' and ti.diposable='否' and i.sendTime between %s and %s " -// + "and not exists(select 1 from ForeignTousseApplication fta where fta.id=i.invoicePlan_ID) " -// + "%s %s group by ti.tousseType,ti.tousseName ", -// isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd,taskGroupSqlWithAliasOfTousseDefinitionIsTd,tousseGroupSqlWithAliasOfTousseDefinitionIsTd, -// departCoding,startDateSql,endDateSql,DataIndex.getAndSql("i.sender", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTd); -// if (includeForeignTousse) { -// sql += " union all "; -// sql += String.format("select 'invoice' type,fta.tousseName,sum(ii.amount) amount,'外来器械包' tousseType from invoice i join InvoiceItem ii on ii.invoice_id=i.id " -// + "join ForeignTousseApplication fta on fta.id=i.invoicePlan_ID " -// +" where 1=1 %s %s %s and i.orgUnitCoding = '%s' and i.sendTime between %s and %s " -// + "%s group by fta.tousseName", -// isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, -// departCoding,startDateSql,endDateSql,DataIndex.getAndSql("i.sender", operator)); -// } sql += String.format("select 'invoice' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType from (" - +DataIndex.getWorkAmountByPackageSQL("发货数量", supplyRoomConfigManager,dateQueryAdapter,departCoding, - startTime, endTime, tousseTypes, isDisableIDCard, - taskGroup, tousseGroup, null, sqlLengthFunctionName) + +DataIndex.getWorkAmountByPackageSQL("发货数量", params) + ") tl group by tl.tousseName,tl.tousseType "); //签收数量（按包统计） sql += " union all "; - sql += String.format("select 'signed' as type,tis.tousseName,count(*),ti.tousseType from TousseInstance tis,TousseDefinition ti " - +" where tis.tousseDefinition_id=ti.id %s %s %s and tis.orgUnitCoding = '%s' and %s between %s and %s and tis.foreignTousseApp_id is null " - + "%s %s group by ti.tousseType,tis.tousseName ", - isDisableIDCardSqlWithAliasOfTousseDefinitionIsTi,taskGroupSqlWithAliasOfTousseDefinitionIsTi,tousseGroupSqlWithAliasOfTousseDefinitionIsTi, - departCoding,signedTimeString,startDateSql,endDateSql,DataIndex.getAndSql("tis.signedUser", operator),tousseTypeSqlWithAliasOfTousseDefinitionIsTi); - if (includeForeignTousse) { - sql += " union all "; - sql += String.format("select 'signed' as type,fta.tousseName,count(*),'外来器械包' from TousseInstance tis,ForeignTousseApplication fta " - +" where tis.foreignTousseApp_id=fta.id %s %s %s " - +" and tis.orgUnitCoding = '%s' and %s between %s and %s %s group by fta.tousseName ", - isDisableIDCardFalseSqlCondition,taskGroupFalseSqlCondition,tousseGroupFalseSqlCondition, - departCoding,signedTimeString,startDateSql,endDateSql,DataIndex.getAndSql("tis.signedUser", operator)); - } + sql += String.format("select 'signed' type,tl.tousseName,sum(tl.amount) amount,tl.tousseType from (" + +DataIndex.getWorkAmountByPackageSQL("签收数量", params) + + ") tl group by tl.tousseName,tl.tousseType "); Map> typeOfChild = new HashMap>(); GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_TOUSSEWORKLOAD, departCoding); @@ -12603,6 +12411,7 @@ } // 如果设置了查询范围，但当前的包不在所选择的范围，则继续下一个包 + // 特别注意：如果设置了包类型，则所有的材料都查不到了 if (selectedTousseNameMap.size() > 0 && selectedTousseNameMap.get(tousseName) == null && selectedTousseNameMap.get(formatedName) == null) {