Index: ssts-web/src/main/webapp/disinfectsystem/reportforms/statisticalWorkloadView.js =================================================================== diff -u -r25723 -r26059 --- ssts-web/src/main/webapp/disinfectsystem/reportforms/statisticalWorkloadView.js (.../statisticalWorkloadView.js) (revision 25723) +++ ssts-web/src/main/webapp/disinfectsystem/reportforms/statisticalWorkloadView.js (.../statisticalWorkloadView.js) (revision 26059) @@ -158,7 +158,7 @@ */ var tousseTypeStore = new Ext.data.SimpleStore({ fields : ['value'], - data : [[ALL],[INSIDE],[DRESSING],[DISINFECTION],[FOREIGNPROXY],[FOREIGN],[SPLIT],[CUSTOM],[MATERIAL]] + data : [[ALL],[INSIDE],[DRESSING],[COMBO],[DISINFECTION],[FOREIGNPROXY],[FOREIGN],[SPLIT],[CUSTOM],[MATERIAL]] }); /** Index: ssts-web/src/main/webapp/disinfectsystem/reportforms/tousseWorkLoadView.js =================================================================== diff -u -r25237 -r26059 --- ssts-web/src/main/webapp/disinfectsystem/reportforms/tousseWorkLoadView.js (.../tousseWorkLoadView.js) (revision 25237) +++ ssts-web/src/main/webapp/disinfectsystem/reportforms/tousseWorkLoadView.js (.../tousseWorkLoadView.js) (revision 26059) @@ -114,7 +114,7 @@ */ var tousseTypeStore = new Ext.data.SimpleStore( { fields : ['value'], - data : [[ALL],[INSIDE],[DRESSING],[DISINFECTION],[FOREIGNPROXY],[FOREIGN],[FOREIGNSPLIT],[CUSTOM],[MATERIAL]] + data : [[ALL],[INSIDE],[DRESSING],[COMBO],[DISINFECTION],[FOREIGNPROXY],[FOREIGN],[FOREIGNSPLIT],[CUSTOM],[MATERIAL]] }); //人员 Index: ssts-web/src/main/webapp/disinfectsystem/reportforms/tousseWorkLoadView.jsp =================================================================== diff -u -r25237 -r26059 --- ssts-web/src/main/webapp/disinfectsystem/reportforms/tousseWorkLoadView.jsp (.../tousseWorkLoadView.jsp) (revision 25237) +++ ssts-web/src/main/webapp/disinfectsystem/reportforms/tousseWorkLoadView.jsp (.../tousseWorkLoadView.jsp) (revision 26059) @@ -58,6 +58,7 @@ var INSIDE = '<%=TousseDefinition.PACKAGE_TYPE_INSIDE%>'; var DRESSING = '<%=TousseDefinition.PACKAGE_TYPE_DRESSING%>'; var DISINFECTION = '<%=TousseDefinition.PACKAGE_TYPE_DISINFECTION%>'; +var COMBO = '<%=TousseDefinition.PACKAGE_TYPE_COMBO%>'; var FOREIGNPROXY = '<%=TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY%>'; var FOREIGN = '<%=TousseDefinition.PACKAGE_TYPE_FOREIGN%>'; var FOREIGNSPLIT = '<%=TousseDefinition.PACKAGE_TYPE_SPLIT%>'; Index: ssts-web/src/main/webapp/disinfectsystem/reportforms/statisticalWorkloadView.jsp =================================================================== diff -u -r25723 -r26059 --- ssts-web/src/main/webapp/disinfectsystem/reportforms/statisticalWorkloadView.jsp (.../statisticalWorkloadView.jsp) (revision 25723) +++ ssts-web/src/main/webapp/disinfectsystem/reportforms/statisticalWorkloadView.jsp (.../statisticalWorkloadView.jsp) (revision 26059) @@ -26,6 +26,7 @@ var DISINFECTION = '<%=TousseDefinition.PACKAGE_TYPE_DISINFECTION%>'; var FOREIGNPROXY = '<%=TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY%>'; var FOREIGN = '<%=TousseDefinition.PACKAGE_TYPE_FOREIGN%>'; + var COMBO = '<%=TousseDefinition.PACKAGE_TYPE_COMBO%>'; var SPLIT = '<%=TousseDefinition.PACKAGE_TYPE_SPLIT%>'; var CUSTOM = '<%=TousseDefinition.PACKAGE_TYPE_CUSTOM%>'; var MATERIAL = '<%=TousseDefinition.PACKAGE_TYPE_MATERIAL%>'; Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java =================================================================== diff -u -r25397 -r26059 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 25397) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 26059) @@ -1,6 +1,10 @@ package com.forgon.disinfectsystem.reportforms.vo; +import org.apache.commons.lang.StringUtils; +import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; + + /** * @author Terry 2018-12-10 下午15:54 * 报表查询参数，代表查询报表的条件 @@ -99,4 +103,45 @@ */ public boolean selectTousseName = false; + /** + * 是否查询聚合包 + */ + public boolean isQueryCom = false; + + /** + * 获取是否查询聚合包 + * @return + */ + public boolean getIsQueryCom(){ + if(StringUtils.isBlank(tousseTypes) || tousseTypes.indexOf(TousseDefinition.PACKAGE_TYPE_COMBO) != -1 || "全部".equals(tousseTypes)){ + isQueryCom = true; + } + return isQueryCom; + } + + public String IsQueryComByPackageSize = ""; + /** + * 过滤条件有包大小就不查询聚合包 + * @return + */ + public String getIsQueryComByPackageSize(){ + return tousseTypeAndPackageSizeSql.indexOf("packageSize") != -1?"1!=1 and":""; + } + + /** + * 只查询聚合包 + */ + public boolean isOnlyQueryCom = false; + + /** + * 获取是否只查询聚合包 + * @return + */ + public boolean getIsOnlyQueryCom(){ + if(TousseDefinition.PACKAGE_TYPE_COMBO.equals(tousseTypes)){ + isOnlyQueryCom = true; + } + return isOnlyQueryCom; + } + } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r26011 -r26059 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 26011) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 26059) @@ -45,7 +45,6 @@ import com.forgon.tools.string.StringTools; import com.forgon.tools.util.ForgonDateUtils; import com.forgon.tools.util.SqlUtils; -import com.forgon.util.StringUtil; /** * @author Terry 2018-12-08 上午09:22 @@ -161,8 +160,21 @@ } return tousseTypeSql; } - - + /** + * 过滤聚合包分组 + * @param tousseTypes + * @return + */ + public static String filterComTousseType(String tousseTypes){ + if(StringTools.isNotBlank(tousseTypes) && !tousseTypes.contains("全部")){ + if(tousseTypes.indexOf(","+TousseDefinition.PACKAGE_TYPE_COMBO) != -1){ + tousseTypes = tousseTypes.replaceAll(","+TousseDefinition.PACKAGE_TYPE_COMBO, ""); + }else if(tousseTypes.indexOf(TousseDefinition.PACKAGE_TYPE_COMBO) != -1){ + tousseTypes = tousseTypes.replaceAll(TousseDefinition.PACKAGE_TYPE_COMBO, ""); + } + } + return tousseTypes; + } public static String getTousseTypesAndPackageSizesFilterSQL(String tousseTypes, String packageSizes){ //按器械包类型和器械包大小过滤 String tousseTypeSql = DataIndex.getTousseTypesFilterSQL(tousseTypes); @@ -450,21 +462,43 @@ } // 按包定义的材料数量统计 else{ - amountSql = String - .format("select sum(mi.count) amount ,case when (ti.operator is null) then '系统用户' else ti.operator end userName, td.tousseType,td.name tousseName " - + "%s 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.operationTime %s %s " - + "%s %s %s %s " - + "group by ti.operator,td.tousseType,td.name %s", - obj.extraSelectColumns, - obj.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql, - obj.extraGroupBy); + if(!obj.getIsOnlyQueryCom()){ + amountSql = String + .format("select sum(mi.count) amount ,case when (ti.operator is null) then '系统用户' else ti.operator end userName, td.tousseType,td.name tousseName " + + "%s 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.comboTousseInstanceId is null and ti.operationTime %s %s " + + "%s %s %s %s " + + "group by ti.operator,td.tousseType,td.name %s", + obj.extraSelectColumns, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql, + obj.extraGroupBy); + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select sum(mi.count) amount ,case when (ti.operator is null) then '系统用户' else ti.operator end userName,'%s' tousseType,ti.comboTousseDefinitionName tousseName " + + "%s from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + + "inner join MaterialInstance mi on td.id = mi.tousse_id " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseDefinitionId is not null and ti.operationTime %s %s " + + "%s %s %s " + + "group by ti.operator,tousseType,ti.comboTousseDefinitionName %s", + TousseDefinition.PACKAGE_TYPE_COMBO, + obj.extraSelectColumns, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.extraGroupBy); + } } break; @@ -548,51 +582,112 @@ 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.reviewTime %s %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.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); + if(!obj.getIsOnlyQueryCom()){ + 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.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.reviewTime %s %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.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select sum(mi.count) amount ,ti.reviewer userName, '%s' tousseType,ti.comboTousseDefinitionName 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 "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is not null and ti.reviewTime %s %s " + + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s " + + "group by ti.reviewer,td.tousseType,ti.comboTousseDefinitionName ", + TousseDefinition.PACKAGE_TYPE_COMBO, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); + } 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 " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s " - + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 and ti.sterilizationRecord_id is not null %s %s %s %s " - + "group by ti.sterilizationUser,td.tousseType,td.name ", - obj.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); + if(!obj.getIsOnlyQueryCom()){ + 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.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s " + + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 and ti.sterilizationRecord_id is not null %s %s %s %s " + + "group by ti.sterilizationUser,td.tousseType,td.name ", + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select sum(mi.count) amount ,ti.sterilizationUser userName,'%s' tousseType,ti.comboTousseDefinitionName 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 "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseDefinitionId is not null and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s " + + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 and ti.sterilizationRecord_id is not null %s %s %s " + + "group by ti.sterilizationUser,td.tousseType,ti.comboTousseDefinitionName ", + TousseDefinition.PACKAGE_TYPE_COMBO, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replace("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); + } 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.sendTime %s %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.betweenSql, - SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); - + if(!obj.getIsOnlyQueryCom()){ + 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 ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and i.sendTime %s %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.betweenSql, + SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select sum(mi.count) amount ,i.sender userName,'%s' tousseType,ti.comboTousseDefinitionName 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 "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is not null and i.sendTime %s %s " + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s " + + "group by i.sender,td.tousseType,ti.comboTousseDefinitionName ", + TousseDefinition.PACKAGE_TYPE_COMBO, + obj.betweenSql, + SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); + } + // 一次性物品的发货统计 // TODO:原来的代码做了一次性物品的数量统计，导致发货的器械件数很大，应该单独做一列，列出一次性物品的数量 // if (obj.includeDisposableGoods){ @@ -647,66 +742,104 @@ 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.sendTime %s %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.betweenSql, - SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); - - // 一次性物品的发货核对统计 - if (obj.includeDisposableGoods){ - amountSql += " union all "; + if(!obj.getIsOnlyQueryCom()){ 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 ii.tousseType = '一次性物品' and i.sendTime %s %s " - + "and i.assistantSender is not null and %s(i.assistantSender)<>0 " - + "group by i.assistantSender,ii.tousseType,ii.tousseName ", + .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 ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and i.sendTime %s %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.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName); - + 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 ii.tousseType = '一次性物品' and i.sendTime %s %s " + + "and i.assistantSender is not null and %s(i.assistantSender)<>0 " + + "group by i.assistantSender,ii.tousseType,ii.tousseName ", + obj.betweenSql, + SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName); + } } - + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select sum(mi.count) amount ,i.assistantSender userName,'%s' tousseType,ti.comboTousseDefinitionName 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 "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is not null and i.sendTime %s %s " + + "and i.assistantSender is not null and %s(i.assistantSender)<>0 %s %s %s " + + "group by i.assistantSender,td.tousseType,ti.comboTousseDefinitionName ", + TousseDefinition.PACKAGE_TYPE_COMBO, + obj.betweenSql, + SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); + } 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.sendTime %s %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.betweenSql, - SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); - - // 一次性物品的发货下送数量统计 - if (obj.includeDisposableGoods){ - amountSql += " union all "; + if(!obj.getIsOnlyQueryCom()){ 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 ii.tousseType = '一次性物品' and i.sendTime %s %s " - + "and i.personInCharge is not null and %s(i.personInCharge)<>0 " - + "group by i.personInCharge,ii.tousseType,ii.tousseName ", + .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 ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and i.sendTime %s %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.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName); - + //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 ii.tousseType = '一次性物品' and i.sendTime %s %s " + //+ "and i.personInCharge is not null and %s(i.personInCharge)<>0 " + + "group by i.personInCharge,ii.tousseType,ii.tousseName ", + obj.betweenSql, + SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom) + //,obj.sqlLengthFunctionName + ); + } } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select sum(mi.count) amount ,i.personInCharge userName,'%s' tousseType,ti.comboTousseDefinitionName 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 "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is not null and i.sendTime %s %s " + //+ "and i.personInCharge is not null and %s(i.personInCharge)<>0" + + " %s %s %s " + + "group by i.personInCharge,td.tousseType,ti.comboTousseDefinitionName ", + TousseDefinition.PACKAGE_TYPE_COMBO, + obj.betweenSql, + SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), + //obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); + } break; @@ -748,11 +881,10 @@ .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.recyclingTime %s %s " - + "and rr.recyclingUser is not null and %s(rr.recyclingUser)<>0 %s %s %s %s " + + " %s %s %s %s " + "group by rr.recyclingUser,td.tousseType,td.name ", obj.betweenSql, SqlUtils.get_InSql_Extra("rr.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, @@ -794,7 +926,7 @@ 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 " + + "min(td.name) 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 = '材料' " @@ -809,7 +941,7 @@ 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 " + + "td.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.endDate %s %s and ci.itemtype != '材料' and wdr.washMaterialAmount <> 0 %s %s %s %s ", @@ -827,29 +959,54 @@ String selectUserNameSql = ",case when (ti.operator is null) then '系统用户' else ti.operator end userName "; String selectTousseTypeSql = ",td.tousseType tousseType "; String selectTousseNameSql = ",td.name tousseName "; - - amountSql = String - .format("select %s count(*) amount %s %s %s %s " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id %s " - + "where ti.operationTime %s %s " - + "%s %s %s %s %s " - + "%s ", - getDatePeriodSelectSql(obj.monthlyStr), - obj.selectUserName ? selectUserNameSql : "", - obj.selectTousseType ? selectTousseTypeSql : "", - obj.selectTousseName ? selectTousseNameSql : "", - obj.extraSelectColumns, - obj.extraJoinCondition, - obj.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql, - obj.extraQuery, - getMultipleFieldsGroupBySql(obj,",ti.operator")); + if(!obj.getIsOnlyQueryCom()){ + amountSql = String + .format("select %s count(*) amount %s %s %s %s " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id %s " + + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.operationTime %s %s " + + "%s %s %s %s %s " + + "%s ", + getDatePeriodSelectSql(obj.monthlyStr), + obj.selectUserName ? selectUserNameSql : "", + obj.selectTousseType ? selectTousseTypeSql : "", + obj.selectTousseName ? selectTousseNameSql : "", + obj.extraSelectColumns, + obj.extraJoinCondition, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, + getMultipleFieldsGroupBySql(obj,",ti.operator")); + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select %s count(*) amount %s %s %s %s " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id %s " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.operationTime %s %s " + + "%s %s %s %s " + + "%s ", + getDatePeriodSelectSql(obj.monthlyStr), + obj.selectUserName ? selectUserNameSql : "", + obj.selectTousseType ? selectTousseTypeSql : "", + obj.selectTousseName ? ",td.name tousseName " : "", + obj.extraSelectColumns, + obj.extraJoinCondition, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.extraQuery, + getMultipleFieldsGroupBySql(obj,",ti.operator")); + } break; - + // TODO:需要将此case与配包数量合并 case "年度报表中的配包数量（按包）": amountSql = String @@ -902,10 +1059,11 @@ case "审核数量": + if(!obj.getIsOnlyQueryCom()){ 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.reviewTime %s %s " + + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.reviewTime %s %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.betweenSql, @@ -915,7 +1073,24 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql); - + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + 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 "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.reviewTime %s %s " + + "and ti.reviewer is not null and %s(ti.reviewer)<>0 %s %s %s " + + "group by ti.reviewer,td.tousseType,td.name ", + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); + } break; @@ -937,37 +1112,65 @@ // 支持按日期分组查询灭菌数量 case "灭菌数量": - amountSql = String - .format("select %s count(*) amount %s %s %s %s " - + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id %s " - + "where " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s " - + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 " - + "and ti.sterilizationRecord_id is not null %s %s %s %s %s " - + "%s ", - getDatePeriodSelectSql(obj.monthlyStr), - obj.selectUserName ? ",ti.sterilizationUser userName " : "", - obj.selectTousseType ? ",td.tousseType tousseType " : "", - obj.selectTousseName ? ",td.name tousseName " : "", - obj.extraSelectColumns, - obj.extraJoinCondition, - obj.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql, - obj.extraQuery, - getMultipleFieldsGroupBySql(obj,",ti.sterilizationUser")); + if(!obj.getIsOnlyQueryCom()){ + amountSql = String + .format("select %s count(*) amount %s %s %s %s " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id %s " + + "where ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s " + + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 " + + "and ti.sterilizationRecord_id is not null %s %s %s %s %s " + + "%s ", + getDatePeriodSelectSql(obj.monthlyStr), + obj.selectUserName ? ",ti.sterilizationUser userName " : "", + obj.selectTousseType ? ",td.tousseType tousseType " : "", + obj.selectTousseName ? ",td.name tousseName " : "", + obj.extraSelectColumns, + obj.extraJoinCondition, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql, + obj.extraQuery, + getMultipleFieldsGroupBySql(obj,",ti.sterilizationUser")); + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select %s count(*) amount %s %s %s %s " + + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id %s " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s " + + "and ti.sterilizationUser is not null and %s(ti.sterilizationUser)<>0 " + + "and ti.sterilizationRecord_id is not null %s %s %s %s " + + "%s ", + getDatePeriodSelectSql(obj.monthlyStr), + obj.selectUserName ? ",ti.sterilizationUser userName " : "", + obj.selectTousseType ? ",td.tousseType tousseType " : "", + obj.selectTousseName ? ",td.name tousseName " : "", + obj.extraSelectColumns, + obj.extraJoinCondition, + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.extraQuery, + getMultipleFieldsGroupBySql(obj,",ti.sterilizationUser")); + } break; - case "发货数量": - if (obj.includeTousses) { + if(!obj.isOnlyQueryCom){ + if (obj.includeTousses) { 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.sendTime %s %s " + + "where ii.comboTousseInstanceId is null and i.sendTime %s %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.betweenSql, @@ -979,22 +1182,44 @@ obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql); + } + // 一次性物品的发货统计 + if (obj.includeDisposableGoods) { + if (obj.includeTousses) { + 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 ii.tousseType = '一次性物品' and i.sendTime %s %s " + + "and i.sender is not null and %s(i.sender)<>0 " + + "group by i.sender,ii.tousseType,ii.tousseName ", + obj.betweenSql, + SqlUtils.get_InSql_Extra("i.orgUnitCoding", + obj.querySupplyRoom), + obj.sqlLengthFunctionName); + } } - // 一次性物品的发货统计 - if (obj.includeDisposableGoods) { - if (obj.includeTousses) { + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ amountSql += " union all "; } amountSql += String - .format("select sum(ii.amount) amount ,i.sender userName, ii.tousseType tousseType,ii.tousseName tousseName " + .format("select sum(ii.amount) amount ,i.sender userName,'%s' tousseType, td.name tousseName " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " - + "where ii.tousseType = '一次性物品' and i.sendTime %s %s " - + "and i.sender is not null and %s(i.sender)<>0 " - + "group by i.sender,ii.tousseType,ii.tousseName ", + + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId " + + "inner join TousseInstance ti on ti.tousseDefinition_id=td.id " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and td.tousseType ='聚合包' and i.sendTime %s %s " + + "and i.sender is not null and %s(i.sender)<>0 %s %s %s " + + "group by i.sender,td.tousseType,td.name ", + TousseDefinition.PACKAGE_TYPE_COMBO, obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName); + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); } break; @@ -1042,10 +1267,11 @@ break; case "核对数量": + if(!obj.isOnlyQueryCom){ 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.sendTime %s %s " + + "where ii.comboTousseInstanceId is null and i.sendTime %s %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.betweenSql, @@ -1055,7 +1281,26 @@ obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseTypeAndPackageSizeSql); - + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + amountSql += String + .format("select sum(ii.amount) amount ,i.assistantSender userName,'%s' 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 " + + "inner join TousseInstance ti on ti.tousseDefinition_id=td.id " + + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and td.tousseType ='聚合包' and i.sendTime %s %s " + + "and i.assistantSender is not null and %s(i.assistantSender)<>0 %s %s %s " + + "group by i.assistantSender,td.tousseType,td.name ", + TousseDefinition.PACKAGE_TYPE_COMBO, + obj.betweenSql, + SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); + } break; @@ -1064,11 +1309,12 @@ .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.sendTime %s %s " - + "and i.personInCharge is not null and %s(i.personInCharge)<>0 %s %s %s %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.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, + //obj.sqlLengthFunctionName, obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, @@ -1077,19 +1323,38 @@ 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.signedDate %s %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.betweenSql, - SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), - obj.sqlLengthFunctionName, - obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, - obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, - obj.tousseTypeAndPackageSizeSql); + if(!obj.getIsOnlyQueryCom()){ + 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.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.signedDate %s %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.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd, + obj.tousseTypeAndPackageSizeSql); + } + if(obj.getIsQueryCom()){ + if(!obj.getIsOnlyQueryCom()){ + amountSql += " union all "; + } + 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 "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.signedDate %s %s " + + "and ti.signedUser is not null and %s(ti.signedUser)<>0 %s %s %s " + + "group by ti.signedUser,td.tousseType,td.name ", + obj.betweenSql, + SqlUtils.get_InSql_Extra("ti.orgUnitCoding", obj.querySupplyRoom), + obj.sqlLengthFunctionName, + obj.isDisableIDCardSqlWithAliasOfTousseDefinitionIsTd, + obj.taskGroupSqlWithAliasOfTousseDefinitionIsTd.replaceAll("td", "ti"), + obj.tousseGroupSqlWithAliasOfTousseDefinitionIsTd); + } break; @@ -2099,5 +2364,4 @@ return sql.toString(); } - }