Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r34483 -r34504 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 34483) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 34504) @@ -9633,6 +9633,8 @@ // 是否显示发货下送责任人，默认值为true boolean showInvoicePersonInCharge = CssdUtils.getSystemSetConfigByNameBool("showInvoicePersonInCharge", true); + // 启用消毒供应中心员工绩效评分表按照包定义的积分系数字段评分，默认值为false + boolean enableScoreByIntegralFieldOfTousseDefinitionInStaffStatisticalWorkloadReport = CssdUtils.getSystemSetConfigByNameBool("enableScoreByIntegralFieldOfTousseDefinitionInStaffStatisticalWorkloadReport", false); Map sequenceMap = new HashMap(); // 统计工作环节的数量 @@ -9699,85 +9701,94 @@ } //按包数量统计的SQL //回收记录 - String tousseAmountSql = String.format("select '预回收数量' type,tl.userName,sum(tl.amount) amount from (" + boolean queryIntegralOfTousse = false; + String integralSql = ""; + String sumIntegralSql = ""; + if(enableScoreByIntegralFieldOfTousseDefinitionInStaffStatisticalWorkloadReport && isStrengthen){ + queryIntegralOfTousse = true; + integralSql = ",tl.integral "; + sumIntegralSql = ",sum(tl.integral) integral "; + params.queryIntegral = true; + } + String tousseAmountSql = String.format("select '预回收数量' type,tl.userName,sum(tl.amount) amount "+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("预回收数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); tousseAmountSql += " union all "; - tousseAmountSql += String.format("select '回收数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '回收数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("回收数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); tousseAmountSql += " union all "; //清点记录 - tousseAmountSql += String.format("select '清点数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '清点数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("清点数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); tousseAmountSql += " union all "; //清洗数量 - tousseAmountSql += String.format("select '清洗数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '清洗数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("清洗数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); tousseAmountSql += " union all "; //装配数量（按包统计） - tousseAmountSql += String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '配包数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("配包数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); //检查数量 if(showInspectorField){ //中山六院的项目开启了 tousseAmountSql += " union all "; //检查数量 - tousseAmountSql += String.format("select '检查数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '检查数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("检查数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); } //包装数量 if(enablePackagingRecordFunction && showWrapperField){ tousseAmountSql += " union all "; - tousseAmountSql += String.format("select '包装数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '包装数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("打包数量", params) + " union all " +dataIndex.getWorkAmountByPackageSQL("包装数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); }else if(enablePackagingRecordFunction){ tousseAmountSql += " union all "; - tousseAmountSql += String.format("select '包装数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '包装数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("打包数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); }else if(showWrapperField){ tousseAmountSql += " union all "; - tousseAmountSql += String.format("select '包装数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '包装数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("包装数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); } tousseAmountSql += " union all "; //审核记录 - tousseAmountSql += String.format("select '审核数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '审核数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("审核数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); tousseAmountSql += " union all "; //灭菌记录 - tousseAmountSql += String.format("select '灭菌数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '灭菌数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("灭菌数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); tousseAmountSql += " union all "; //发货记录 - tousseAmountSql += String.format("select '发货数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '发货数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("发货数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); tousseAmountSql += " union all "; //查询一次性物品 @@ -9788,17 +9799,17 @@ params.includeTousses = true; } //核对记录 - tousseAmountSql += String.format("select '核对数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '核对数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("核对数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); //发货下送责任人 if(showInvoicePersonInCharge){ tousseAmountSql += " union all "; //下送数量 - tousseAmountSql += String.format("select '下送数量' type,tl.userName,sum(tl.amount) amount from (" + tousseAmountSql += String.format("select '下送数量' type,tl.userName,sum(tl.amount) amount"+ sumIntegralSql +" from (" +dataIndex.getWorkAmountByPackageSQL("下送数量", params) - + ") tl group by tl.userName "); + + ") tl group by tl.userName " + integralSql); } Set readedNames = new HashSet(); @@ -9813,6 +9824,12 @@ workLoadBean.setOperator(userName); workLoadBean.setAmount(amount); workLoadBean.setColumnName(type); + if(queryIntegralOfTousse){ + Double integral = rs.getDouble("integral"); + if(integral != null){ + workLoadBean.setScore(integral.intValue()); + } + } if(disposableGoodsMap.containsKey(userName) && !readedNames.contains(userName)){ readedNames.add(userName); workLoadBean.setDisposableGoodAmount(disposableGoodsMap.get(userName)); @@ -9841,7 +9858,7 @@ } } Map scoreConfigMap = new HashMap(); - if (isStrengthen) { + if (isStrengthen && !queryIntegralOfTousse) { List scoreConfigs = objectDao.findBySql(WorkScoreConfig.class.getSimpleName(), "where po.orgUnitCoding = '" + querySupplyRoom + "'"); scoreConfigMap = new HashMap(); if(scoreConfigs != null){ @@ -27461,7 +27478,7 @@ List resultList = new ArrayList(); String sql1 = "SELECT inn.bhName,inn.id FROM " + " (SELECT t.id, t.bhName, ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY t.id ) num " - + " FROM （ select id, bhName, count(*) count from (select te.id, " + + " FROM (select id, bhName, count(*) count from (select te.id, " + " (select bh.name from brancheOfHospital bh " + " join OrgUnitGroup og on og.id = bh.orgUnitGroupId " + " join Org_OrgGroup oog on oog.orgGroupId = og.id " Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r34199 -r34504 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 34199) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 34504) @@ -490,7 +490,8 @@ && StringUtils.isBlank(obj.packageTypeSqlWithAliasOfTousseDefinitionIsTd) && !obj.extraQuery.contains("ci.") && !obj.extraQuery.contains("td.") - && !obj.extraQuery.contains("tdc.")) { + && !obj.extraQuery.contains("tdc.") + && !obj.extraGroupBy.contains("tdc.")) { amountSql += " union all "; amountSql += String.format("select %s %s %s %s %s %s " + getWashMaterialAmountSqlFromTousseTypeIsMaterial(obj.extraJoinCondition) @@ -1440,10 +1441,11 @@ groupBySql = ""; } amountSql = String - .format("select sum(ri.amount) amount %s %s %s %s" + .format("select sum(ri.amount) amount %s %s %s %s %s" + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id inner join TousseDefinition td on ri.tousseDefinitionId=td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + "where rr.recyclingTime %s %s %s and %s %s %s " + " %s %s %s %s %s %s", + obj.queryIntegral?",sum(ri.amount*tdc.integral) integral":"", obj.selectUserName ? ",rr.recyclingUser userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName " : "", @@ -1463,11 +1465,12 @@ break; case "预回收数量": amountSql = String - .format("select SUM(ti.prepareRecycleAmount) amount %s from invoicePlan ip" + .format("select SUM(ti.prepareRecycleAmount) amount %s %s from invoicePlan ip" + " inner join TousseItem ti on ti.recyclingApplication_ID =ip.id inner join TousseDefinition td on ti.tousseDefinitionId = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + " where ip.prepareRecycleDateTime %s %s " + " and ip.prepareRecycleOperator is not null and ip.prepareRecycleOperator<>'' %s %s %s %s " + " group by ip.prepareRecycleOperator ", + obj.queryIntegral?",sum(ti.prepareRecycleAmount*tdc.integral) integral":"", obj.selectUserName ? ",ip.prepareRecycleOperator userName" : "", obj.betweenSql, SqlUtils.get_InSql_Extra("ip.handleDepartCoding", obj.querySupplyRoom), @@ -1493,11 +1496,12 @@ groupBySql = ""; } amountSql = String - .format("select sum(ri.amount) amount %s %s %s %s" + .format("select sum(ri.amount) amount %s %s %s %s %s" + "from RecyclingRecord rr inner join RecyclingItem ri on ri.recyclingRecord_id = rr.id " + "inner join TousseDefinition td on ri.tousseDefinitionId=td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where rr.recyclingTime %s %s %s and %s %s %s " + "and rr.operator is not null and rr.operator<>'' %s %s %s %s %s %s", + obj.queryIntegral?",sum(ri.amount*tdc.integral) integral":"", obj.selectUserName?",rr.operator userName":"", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -1520,12 +1524,13 @@ case "清洗数量": amountSql = String.format( - "select min(ci.tousseAmountForMaterial) amount %s %s %s %s " + "select min(ci.tousseAmountForMaterial) amount %s %s %s %s %s " + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td,TousseDefinition tdc " + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and tdc.id=td.ancestorID " + "and ci.itemType = '材料' " + "and wdr.endDate %s %s %s and %s %s %s and wdr.washMaterialAmount <> 0 %s %s %s %s %s " + "group by ci.tousseDefinitionID,ci.recyclingRecordId", + obj.queryIntegral?",sum(ci.tousseAmountForMaterial*tdc.integral) integral":"", obj.selectUserName?",case when (min(cb.personInCharge) is null or min(cb.personInCharge) = '') then min(wdr.operator) else min(cb.personInCharge) end userName":"", obj.selectTousseType ? ",min(td.tousseType) tousseType" : "", obj.selectTousseName ? ",min(td.name) tousseName" : "", @@ -1543,10 +1548,11 @@ obj.extraQuery); amountSql += " union all "; - amountSql += String.format("select (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount %s %s %s " + amountSql += String.format("select (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount %s %s %s %s " + "from WashAndDisinfectRecord wdr,ClassifyBasket_WashRecord cw,ClassifyBasket cb,ClassifiedItem ci,TousseDefinition td,TousseDefinition tdc " + "where cw.WashAndDisinfectRecord_ID = wdr.id and cb.id = cw.ClassifyBasket_ID and ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and tdc.id=td.ancestorID " + "and wdr.endDate %s %s %s and %s %s %s and ci.itemtype != '材料' and wdr.washMaterialAmount <> 0 %s %s %s %s %s ", + obj.queryIntegral?",(ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end)*tdc.integral integral":"", obj.selectUserName?",case when (cb.personInCharge is null or cb.personInCharge = '') then wdr.operator else cb.personInCharge end userName":"", obj.selectTousseType ? ",ci.itemType tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -1571,12 +1577,13 @@ String selectTousseNameSql = ",td.name tousseName "; if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String - .format("select %s count(*) amount %s %s %s %s " + .format("select %s count(*) amount %s %s %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where %s and ti.operationTime %s %s %s and %s %s %s " + "%s %s %s %s %s " + "%s ", getDatePeriodSelectSql(obj.monthlyStr), + obj.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? selectUserNameSql : "", obj.selectTousseType ? selectTousseTypeSql : "", obj.selectTousseName ? selectTousseNameSql : "", @@ -1601,11 +1608,12 @@ amountSql += " union all "; } amountSql += String - .format("select %s count(*) amount %s %s %s %s " + .format("select %s count(*) amount %s %s %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.operationTime %s %s %s and %s %s %s " + "%s %s %s %s %s ", getDatePeriodSelectSql(obj.monthlyStr), + obj.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? selectUserNameSql : "", obj.selectTousseType ? selectTousseTypeSql : "", obj.selectTousseName ? ",td.name tousseName " : "", @@ -1631,12 +1639,13 @@ selectTousseNameSql = ",td.name tousseName "; if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String - .format("select %s count(*) amount %s %s %s %s " + .format("select %s count(*) amount %s %s %s %s %s " + "from PackagingRecord po join TousseInstance ti on ti.id=po.tousseInstance_id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where %s and po.entryDate %s %s %s and %s %s %s " + "%s %s %s %s %s " + "%s ", getDatePeriodSelectSql(obj.monthlyStr), + obj.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? selectUserNameSql : "", obj.selectTousseType ? selectTousseTypeSql : "", obj.selectTousseName ? selectTousseNameSql : "", @@ -1661,12 +1670,13 @@ amountSql += " union all "; } amountSql += String - .format("select %s count(*) amount %s %s %s %s " + .format("select %s count(*) amount %s %s %s %s %s " + "from PackagingRecord po join TousseInstance ti on ti.id=po.tousseInstance_id inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null " + "and po.entryDate %s %s %s and %s %s %s " + "%s %s %s %s %s ", getDatePeriodSelectSql(obj.monthlyStr), + obj.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? selectUserNameSql : "", obj.selectTousseType ? selectTousseTypeSql : "", obj.selectTousseName ? ",td.name tousseName " : "", @@ -1704,11 +1714,12 @@ case "检查数量": amountSql = String - .format("select sum(pr.amount) amount %s " + .format("select sum(pr.amount) amount %s %s " + "from PackingRecord pr inner join TousseDefinition td on td.id=pr.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + "where pr.packTime %s %s " + "and pr.inspector is not null and pr.inspector<>'' %s %s %s %s " + "group by pr.inspector ", + obj.queryIntegral?",sum(pr.amount*tdc.integral) integral":"", obj.selectUserName ? ",pr.inspector userName" : "", obj.betweenSql, SqlUtils.get_InSql_Extra("pr.orgUnitCoding", obj.querySupplyRoom), @@ -1722,11 +1733,12 @@ case "包装数量": amountSql = String - .format("select count(*) amount %s " + .format("select count(*) amount %s %s " + "from PackingRecord pr inner join TousseInstance ti on ti.packingRecord_id=pr.id left join PackagingRecord prd on prd.tousseInstance_id=ti.id inner join TousseDefinition td on td.id=pr.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + "where prd.id is null and pr.packTime %s %s " + "and pr.wrapper is not null and pr.wrapper<>'' %s %s %s %s " + "group by pr.wrapper ", + obj.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? ",pr.wrapper userName" : "", obj.betweenSql, SqlUtils.get_InSql_Extra("pr.orgUnitCoding", obj.querySupplyRoom), @@ -1755,11 +1767,12 @@ } if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String - .format("select count(*) amount %s %s %s " + .format("select count(*) amount %s %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + "where %s and ti.reviewTime %s %s %s and %s %s %s " + "and ti.reviewer is not null and ti.reviewer<>'' %s %s %s %s %s " + " %s", + obj.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? ",ti.reviewer userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -1783,11 +1796,12 @@ amountSql += " union all "; } amountSql += String - .format("select count(*) amount %s %s %s " + .format("select count(*) amount %s %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and ti.reviewTime %s %s %s and %s %s %s " + "and ti.reviewer is not null and ti.reviewer<>'' %s %s %s %s " + " %s ", + obj.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? ",ti.reviewer userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -1827,14 +1841,15 @@ case "灭菌数量": if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String - .format("select %s count(*) amount %s %s %s %s " + .format("select %s count(*) amount %s %s %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id %s " + "where %s and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s %s and %s %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.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? ",ti.sterilizationUser userName " : "", obj.selectTousseType ? ",td.tousseType tousseType " : "", obj.selectTousseName ? ",td.name tousseName " : "", @@ -1860,14 +1875,15 @@ amountSql += " union all "; } amountSql += String - .format("select %s count(*) amount %s %s %s %s " + .format("select %s count(*) amount %s %s %s %s %s " + "from TousseInstance ti inner join TousseDefinition td on ti.tousseDefinition_id = td.id join TousseDefinition tdc on tdc.id=td.ancestorID join SterilizationRecord sr on sr.id=ti.sterilizationRecord_id %s " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is not null and " + dateQueryAdapter.stringFieldToDate("ti.sterileEndTime") + " %s %s %s and %s %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.queryIntegral?",sum(tdc.integral) integral":"", obj.selectUserName ? ",ti.sterilizationUser userName " : "", obj.selectTousseType ? ",td.tousseType tousseType " : "", obj.selectTousseName ? ",td.name tousseName " : "", @@ -1906,13 +1922,14 @@ if(!obj.getIsOnlyQueryComboTousse()){ if (obj.includeTousses) { amountSql = String - .format("select sum(ii.amount) amount %s %s %s %s " + .format("select sum(ii.amount) amount %s %s %s %s %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "where %s and i.sendTime %s %s %s and %s %s %s " //+ "and i.sender is not null and %s(i.sender)<>0" + " %s %s %s %s %s " + " %s %s ", + obj.queryIntegral?",sum(ii.amount*tdc.integral) integral":"", obj.selectUserName ? ",i.sender userName" : "", obj.selectTousseType ? ",td.tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -1942,11 +1959,12 @@ amountSql += " union all "; } amountSql += String - .format("select sum(ii.amount) amount %s %s %s %s " + .format("select sum(ii.amount) amount %s %s %s %s %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + "where ii.tousseType = '一次性物品' and i.sendTime %s %s %s and %s " //+ "and i.sender is not null and %s(i.sender)<>0 " + " %s", + obj.queryIntegral?",0 integral":"", obj.selectUserName ? ",i.sender userName" : "", obj.selectTousseType ? ",ii.tousseType" : "", obj.selectTousseName ? "ii.tousseName" : "", @@ -1965,14 +1983,15 @@ amountSql += " union all "; } amountSql += String - .format("select sum(ii.amount) amount %s %s %s %s " + .format("select sum(ii.amount) amount %s %s %s %s %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id " + "inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID %s " + "inner join TousseInstance ti on ti.tousseDefinition_id=td.id " + "where "+ obj.getIsQueryComByPackageSize() +" ti.comboTousseInstanceId is null and ti.comboTousseDefinitionId is null and ti.invoice_id = i.id and td.tousseType ='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"' and i.sendTime %s %s %s and %s %s %s " //+ "and i.sender is not null and %s(i.sender)<>0" + " %s %s %s %s %s " + " %s", + obj.queryIntegral?",sum(ii.amount*tdc.integral) integral":"", obj.selectUserName ? ",i.sender userName" : "", obj.selectTousseType ? ", '"+TousseDefinition.PACKAGE_TYPE_COMBO+"' tousseType" : "", obj.selectTousseName ? ",td.name tousseName" : "", @@ -2043,11 +2062,12 @@ case "核对数量": if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String - .format("select sum(ii.amount) amount %s " + .format("select sum(ii.amount) amount %s %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + "where %s and i.sendTime %s %s " + "and i.assistantSender is not null and i.assistantSender<>'' %s %s %s %s " + "group by i.assistantSender ", + obj.queryIntegral?",sum(ii.amount*tdc.integral) integral":"", obj.selectUserName ? ",i.assistantSender userName" : "", obj.haveCombo?"ii.comboTousseInstanceId is null":"1=1", obj.betweenSql, @@ -2063,12 +2083,13 @@ amountSql += " union all "; } amountSql += String - .format("select sum(ii.amount) amount %s " + .format("select sum(ii.amount) amount %s %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + "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 ='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"' and i.sendTime %s %s " + "and i.assistantSender is not null and i.assistantSender<>'' %s %s %s " + "group by i.assistantSender ", + obj.queryIntegral?",sum(ii.amount*tdc.integral) integral":"", obj.selectUserName ? ",i.assistantSender userName" : "", obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), @@ -2080,11 +2101,12 @@ break; case "下送数量": amountSql = String - .format("select sum(ii.amount) amount %s " + .format("select sum(ii.amount) amount %s %s " + "from Invoice i inner join InvoiceItem ii on ii.invoice_id = i.id inner join TousseDefinition td on td.id=ii.tousseDefinitionId join TousseDefinition tdc on tdc.id=td.ancestorID " + "where i.sendTime %s %s " + "and i.personInCharge is not null and i.personInCharge<>'' %s %s %s %s " + "group by i.personInCharge ", + obj.queryIntegral?",sum(ii.amount*tdc.integral) integral":"", obj.selectUserName ? ",i.personInCharge userName" : "", obj.betweenSql, SqlUtils.get_InSql_Extra("i.orgUnitCoding", obj.querySupplyRoom), Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java =================================================================== diff -u -r33802 -r34504 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 33802) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/vo/ReportQueryParams.java (.../ReportQueryParams.java) (revision 34504) @@ -141,6 +141,10 @@ */ public String packageTypeSqlWithAliasOfTousseDefinitionIsTd = ""; /** + * 查询积分 + */ + public boolean queryIntegral = false; + /** * 回收科室分组 */ public Set recyDepartGroup = null;