Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java =================================================================== diff -u -r32258 -r32261 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 32258) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/dataindex/DataIndex.java (.../DataIndex.java) (revision 32261) @@ -978,7 +978,7 @@ case "发货数量": String miCountSql = null; if(dataSoureOfMaterialsCountOfToussesInReports == 3){ - miCountSql = "ii.materialAmount*ii.amount"; + miCountSql = "ti.materialAmount"; joinMaterialInstanceSql = ""; }else{ miCountSql = "mi.count"; @@ -987,7 +987,7 @@ if(!obj.getIsOnlyQueryComboTousse()){ amountSql = String .format("select sum(%s) amount %s " - + "from Invoice i inner join InvoiceItem ii on i.id = ii.invoice_id inner join TousseDefinition td on ii.tousseDefinitionId = td.id " + + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + " %s 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 %s", miCountSql, @@ -1006,13 +1006,6 @@ if(!obj.getIsOnlyQueryComboTousse()){ amountSql += " union all "; } - if(dataSoureOfMaterialsCountOfToussesInReports == 3){ - miCountSql = "ti.materialAmount"; - joinMaterialInstanceSql = ""; - }else{ - miCountSql = "mi.count"; - joinMaterialInstanceSql = "inner join MaterialInstance mi on mi.tousse_id = td.id"; - } amountSql += String .format("select sum(%s) amount %s " + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id " @@ -1050,15 +1043,15 @@ case "年度报表中的发货数量（按材料）": if(dataSoureOfMaterialsCountOfToussesInReports == 3){ - amountColumnSql = "ii.materialAmount*ii.amount"; + amountColumnSql = "ti.materialAmount"; joinMaterialInstanceSql = ""; }else{ amountColumnSql = "mi.count"; joinMaterialInstanceSql = " inner join MaterialInstance mi on mi.tousse_id = td.id "; } amountSql = String .format("select " + obj.monthlyStr + " monthstr, sum(%s) amount " - + "from Invoice i join InvoiceItem ii on i.id = ii.invoice_id inner join TousseDefinition td on ii.tousseDefinitionId = td.id " + + "from Invoice i inner join TousseInstance ti on ti.invoice_id = i.id inner join TousseDefinition td on ti.tousseDefinition_id = td.id " + " %s where i.sendTime %s %s " + "and i.sender is not null and %s(i.sender)<>0 %s %s %s %s " + "group by " Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r32258 -r32261 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 32258) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 32261) @@ -5703,9 +5703,9 @@ String returnGoodsTousseType = " CASE rr.type WHEN '一次性物品' THEN (rr.type) WHEN '器械包' THEN (td.toussetype) ELSE rr.type END "; String returnGoodsTousseDefinitionId = " CASE rr.type WHEN '器械包' THEN ri.tousseDefinition_id ELSE 0 END "; //器械包 - String tousseSql = " (select "+ tousseMaterialAmountSql +" i.depart,i.settleAccountsDepart "+ tousseDefinitionOfSupplierNameSql + integralOftousseDefinitionSql +",ii.tousseName as name,sum(case when ii.tousseType='消毒物品' then ii.materialAmount*ii.amount else ii.amount end) as amount," + String tousseSql = " (select "+ tousseMaterialAmountSql +" i.depart,i.settleAccountsDepart "+ tousseDefinitionOfSupplierNameSql + integralOftousseDefinitionSql +",ii.tousseName as name,sum(ii.amount) as amount," +"sum(ii.settlementPrice) as settlementprice,sum(ii.settlementDiscountPrice) as settlementDiscountPrice,ii.tousseType as type" - + ",'' as batchNumber,null as expDate,ii.tousseDefinitionId as tousseDefinitionId,'invoiceItem' amountType from invoice i" + + ",'' as batchNumber,null as expDate,ii.tousseDefinitionId as tousseDefinitionId from invoice i" + " join InvoiceItem ii on i.id = ii.invoice_id " + addTousseDefinitionAtWhere + " where ii.diposable = '否' and ii.expensiveGoodsInstanceId is null and "+getHandleDeptCodeSql("i.orgUnitCoding")+" " @@ -5719,9 +5719,9 @@ String selectExpensiveGoodsSql = ""; //如果有启用高值耗材则查询 if(enableExpensiveGoods){ - selectExpensiveGoodsSql = " union all (select "+ noMaterialAmountSql +" i.depart,i.settleAccountsDepart"+noQuerySupplierName + noQueryIntegralSql +",case when egm.model is null then eg.name else eg.name "+ concatSymbolInDb +" '[' "+ concatSymbolInDb +" egm.model "+ concatSymbolInDb +" ']' end as name,sum(case when ii.tousseType='消毒物品' then ii.materialAmount*ii.amount else ii.amount end) as amount," + selectExpensiveGoodsSql = " union all (select "+ noMaterialAmountSql +" i.depart,i.settleAccountsDepart"+noQuerySupplierName + noQueryIntegralSql +",case when egm.model is null then eg.name else eg.name "+ concatSymbolInDb +" '[' "+ concatSymbolInDb +" egm.model "+ concatSymbolInDb +" ']' end as name,sum(ii.amount) as amount," +"sum(ii.settlementPrice) as settlementprice,sum(ii.settlementDiscountPrice) as settlementDiscountPrice,ii.tousseType as type" - + ",'' as batchNumber,ei.expDate as expDate,0 as tousseDefinitionId,'invoiceItem' amountType from " + Invoice.class.getSimpleName() + " i join " + + ",'' as batchNumber,ei.expDate as expDate,0 as tousseDefinitionId from " + Invoice.class.getSimpleName() + " i join " + InvoiceItem.class.getSimpleName()+" ii on i.id = ii.invoice_id join " + ExpensiveGoodsInstance.class.getSimpleName() + " ei on ei.id = ii.expensiveGoodsInstanceId" + " join ExpensiveGoods eg on ei.expensiveGoods_id=eg.id " @@ -5735,7 +5735,7 @@ + " and i.settleaccountsdepart is not null group by i.depart,i.settleAccountsDepart,ii.tousseType,case when egm.model is null then eg.name else eg.name "+ concatSymbolInDb +" '[' "+ concatSymbolInDb +" egm.model "+ concatSymbolInDb +" ']' end,ii.expensiveGoodsInstanceId,ei.expDate)"; //新的高值耗材(一键退库-即退货),如果所选物品类型为全部或高值耗材时, String orgUnitCodingSql = departCodingSet.size() > 0?" and " + SqlUtils.getStringFieldInLargeCollectionsPredicate("i.orgUnitCoding", departCodingSet):""; - selectExpensiveGoodsSql += " union all (select "+ noMaterialAmountSql +" i.depart,i.settleAccountsDepart "+ noQuerySupplierName + noQueryIntegralSql +",case when egm.model is null then eg.name else eg.name "+ concatSymbolInDb +" '[' "+ concatSymbolInDb +" egm.model "+ concatSymbolInDb +" ']' end as name,-1 * count(egi.id) as amount,-1 * sum(egi.price) settlementprice,-1 * sum(egi.price) settlementprice,'高值耗材' as type,null as batchNumber,egi.expDate,null as tousseDefinitionId,null amountType " + selectExpensiveGoodsSql += " union all (select "+ noMaterialAmountSql +" i.depart,i.settleAccountsDepart "+ noQuerySupplierName + noQueryIntegralSql +",case when egm.model is null then eg.name else eg.name "+ concatSymbolInDb +" '[' "+ concatSymbolInDb +" egm.model "+ concatSymbolInDb +" ']' end as name,-1 * count(egi.id) as amount,-1 * sum(egi.price) settlementprice,-1 * sum(egi.price) settlementprice,'高值耗材' as type,null as batchNumber,egi.expDate,null as tousseDefinitionId " + " from ExpensiveGoodsGodownEntry egge join ExpensiveGoodsGodownEntryItem eggei on egge.id=eggei.expensiveGoodsGodownEntry_id " +" join ExpensiveGoodsInstance egi on egi.id=eggei.expensiveGoodsInstanceId " +" join Invoice i on egi.invoiceId=i.id " @@ -5771,7 +5771,7 @@ if(StringUtils.isBlank(typeSearch) || "一次性物品".equals(typeSearch) || "高值耗材".equals(typeSearch) && !enableExpensiveGoods || TYPE_AUTO_DEDUCTION.equals(typeSearch)){ diposableGoodsSql = " union all (select "+ noMaterialAmountSql +" i.depart,i.settleaccountsdepart "+ dgbsOfSupplierNameSql+noQueryIntegralSql +",d.name as name,sum(d.amount) as amount," +"sum(d.fluctuationPrice*d.amount) as settlementprice,sum(d.fluctuationPrice*d.amount) as settlementDiscountPrice,'一次性物品' as type" - + ",d.batch as batchNumber,dgbs.expDate,0 as tousseDefinitionId,null amountType from invoice i join DiposableGoodsItem d on d.invoice_id=i.id join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on d.disposableGoodsBatchStockID=dgbs.id " + + ",d.batch as batchNumber,dgbs.expDate,0 as tousseDefinitionId from invoice i join DiposableGoodsItem d on d.invoice_id=i.id join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on d.disposableGoodsBatchStockID=dgbs.id " + invoiceJoinInvoicePlanSql + " where 1=1 and "+getHandleDeptCodeSql("i.orgUnitCoding")+" " +" and "+diposableGoodsItemPredicate @@ -5789,7 +5789,7 @@ if(StringUtils.isBlank(typeSearch) || "一次性物品".equals(typeSearch)){ packingDisposableGoodsSql = " union all (select "+ noMaterialAmountSql +" pr.depart,pr.orgUnitName "+ dgbsOfSupplierNameSql + noQueryIntegralSql +",dgi.name as name,sum(dgi.amount) as amount," +"sum(dgi.fluctuationPrice*dgi.amount) as settlementprice,sum(dgi.fluctuationPrice*dgi.amount) as settlementDiscountPrice,'一次性物品' as type" - + ",dgi.batch as batchNumber,dgbs.expDate,0 as tousseDefinitionId,null amountType from PackingRecord pr inner join DiposableGoodsItem dgi on pr.id = dgi.packingRecordId " + + ",dgi.batch as batchNumber,dgbs.expDate,0 as tousseDefinitionId from PackingRecord pr inner join DiposableGoodsItem dgi on pr.id = dgi.packingRecordId " + " inner join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on dgi.disposableGoodsBatchStockID=dgbs.id" + " where "+getHandleDeptCodeSql("pr.orgUnitCoding")+" " +" and "+packingDisposableGoodsTypeSql @@ -5812,7 +5812,7 @@ if ((StringUtils.isBlank(typeSearch) || "器械材料".equals(typeSearch) || "高值耗材".equals(typeSearch))) { // 材料发货 materialInvoiceSql = " union all (select "+ noMaterialAmountSql +"i.depart,i.settleAccountsDepart "+noQuerySupplierName + noQueryIntegralSql +",case when td.name is null then ii.materialName else ii.materialName "+ concatSymbolInDb +"'('"+ concatSymbolInDb +" td.name "+ concatSymbolInDb +"')' end,sum(ii.amount),sum(ii.settlementPrice)," - +"sum(ii.settlementPrice) as settlementDiscountPrice,'材料' as type,'' as batchNumber,null as expDate,td.id as tousseDefinitionId,null amountType " + +"sum(ii.settlementPrice) as settlementDiscountPrice,'材料' as type,'' as batchNumber,null as expDate,td.id as tousseDefinitionId " + " from MaterialInvoiceItem ii" + " left join TousseDefinition td on td.id= ii.tousseDefinitionId " + " inner join MaterialInvoice i on i.id = ii.materialInvoice_id " @@ -5828,7 +5828,7 @@ if(StringUtils.isBlank(typeSearch) || "全部".equals(typeSearch) || "器械材料".equals(typeSearch)){ // 材料退货 materialReturnSql = " union all (select "+ noMaterialAmountSql +"rr.depart,rr.settleAccountsDepart "+ noQuerySupplierName + noQueryIntegralSql +",ii.materialName,-sum(ii.amount),-sum(ii.settlementPrice)," - +"-sum(ii.settlementPrice) as settlementDiscountPrice,'材料' as type,'' as batchNumber,null as expDate,0 as tousseDefinitionId,null amountType " + +"-sum(ii.settlementPrice) as settlementDiscountPrice,'材料' as type,'' as batchNumber,null as expDate,0 as tousseDefinitionId " + " from ReturnMaterialRecord rr,materialDefinition d," + "ReturnMaterialItem ii where rr.id = ii.returnMaterialRecord_ID " + " and ii.materialDefinitionId = d.id and "+getHandleDeptCodeSql("rr.handleDepartCode")+" " @@ -5840,7 +5840,7 @@ + " group by rr.depart,rr.settleAccountsDepart,ii.materialName) "; } lostMaterial = " union all (select "+ noMaterialAmountSql +"i.depart, i.settleAccountsDepart "+noQuerySupplierName + noQueryIntegralSql +",i.materialName,sum(i.additionalAmount),sum(i.additionalAmount * i.materialCost)," - +"sum(i.additionalAmount * i.materialCost) as settlementDiscountPrice,'材料' as type,'' as batchNumber,null as expDate,0 as tousseDefinitionId,null amountType " + +"sum(i.additionalAmount * i.materialCost) as settlementDiscountPrice,'材料' as type,'' as batchNumber,null as expDate,0 as tousseDefinitionId " + " from MaterialErrorDamageDetail i left join materialDefinition m on i.materialDefinitionId = m.id where 1=1 and i.errorType = '缺失' and i.type = '"+MaterialErrorDamageDetail.TYPE_ERROR+"' and i.materialName != '器械包标识牌' and i.additionalAmount > 0 and "+getHandleDeptCodeSql("i.handleDepartCode")+" " + lostMaterialFilterSql + iDepartCodeSql @@ -5854,7 +5854,7 @@ banQuery = ""; } damageMaterial = " union all (select "+ noMaterialAmountSql +"i.depart,i.settleAccountsDepart "+noQuerySupplierName + noQueryIntegralSql +",i.materialName,sum(i.additionalAmount),sum(i.materialCost*i.additionalAmount)," - +"sum(i.materialCost*i.additionalAmount) as settlementDiscountPrice,'材料' as type,'' as batchNumber,null as expDate,0 as tousseDefinitionId,null amountType " + +"sum(i.materialCost*i.additionalAmount) as settlementDiscountPrice,'材料' as type,'' as batchNumber,null as expDate,0 as tousseDefinitionId " + "from MaterialErrorDamageDetail i left join materialDefinition m on i.materialDefinitionId = m.id where 1=1 " + banQuery + " and i.type = '" + MaterialErrorDamageDetail.TYPE_DAMAGE + "' and i.additionalAmount > 0 and "+getHandleDeptCodeSql("i.handleDepartCode")+" " + damageFilterSql @@ -5865,7 +5865,7 @@ String returnGoodsSql = " union all (select "+ returnGoodsItemMaterialAmountSql +"rr.depart,rr.settleAccountsDepart as settleaccountsdepart "+dgbsOfSupplierNameSql + noQueryIntegralSql+",ri.toussename as name," + "-sum(ri.amount) as amount,-sum(ri.settlementPrice) as settlementprice,-sum(ri.settlementPrice) as settlementDiscountPrice," +returnGoodsTousseType+" as type" - + ",ri.batchNumber as batchNumber,dgbs.expDate,"+returnGoodsTousseDefinitionId+" as tousseDefinitionId,null amountType from " + ReturnGoodsRecord.class.getSimpleName() + " rr inner join " + + ",ri.batchNumber as batchNumber,dgbs.expDate,"+returnGoodsTousseDefinitionId+" as tousseDefinitionId from " + ReturnGoodsRecord.class.getSimpleName() + " rr inner join " + ReturnGoodsItem.class.getSimpleName()+ " ri on rr.id = ri.returngoodsrecord_id " + " left join " + TousseDefinition.class.getSimpleName() + " td on td.id = ri.tousseDefinition_id" + " left join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on ri.disposableGoodsBatchStockID=dgbs.id" @@ -5880,7 +5880,7 @@ supplyRoomTousseTypeSql = String.format(" and rr.type='%s' ", InvoiceItem.TYPE_AUTO_DEDUCTION); } String supplyRoomDiposableGoodsSql = " union all (select "+ sumRriAmountSql +" rr.depart,rr.depart as settleaccountsdepart "+ dgbsOfSupplierNameSql + noQueryIntegralSql +",rri.goodsName,sum(rri.amount)," - +"sum(rri.price*rri.amount) as settlementprice,sum(rri.price*rri.amount) as settlementDiscountPrice,rri.type as type,dgbs.batchNumber as batchNumber,dgbs.expDate,0 as tousseDefinitionId,null amountType " + +"sum(rri.price*rri.amount) as settlementprice,sum(rri.price*rri.amount) as settlementDiscountPrice,rri.type as type,dgbs.batchNumber as batchNumber,dgbs.expDate,0 as tousseDefinitionId " +" from ReceiveRecord rr,ReceiveRecordItem rri left join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs " +" on rri.diposableGoodBatchStock_id = dgbs.id left join DisposableGoods d on d.id = rri.disposableGoodsId where rr.id = rri.receiveRecord_id and "+getHandleDeptCodeSql("rr.departCoding")+" " +supplyRoomDiposableGoodsFilterSqlOFDgbs @@ -5891,7 +5891,7 @@ + " group by rr.depart,rri.goodsName,rri.amount,rri.type,dgbs.batchNumber,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; // 调拨出库 String appropriateOutDiposableGoodsSql = " union all (select "+ noMaterialAmountSql +" oge.targetOrgUnitName depart ,oge.targetOrgUnitName as settleaccountsdepart"+dgbsOfSupplierNameSql+noQueryIntegralSql+",ged.goodsName,sum(ged.amount)," - +"sum(ged.price*ged.amount) as settlementprice,sum(ged.price*ged.amount) as settlementDiscountPrice,'一次性物品' as type,dgbs.batchNumber as batchNumber,dgbs.expDate,0 as tousseDefinitionId ,null amountType" + +"sum(ged.price*ged.amount) as settlementprice,sum(ged.price*ged.amount) as settlementDiscountPrice,'一次性物品' as type,dgbs.batchNumber as batchNumber,dgbs.expDate,0 as tousseDefinitionId " +" from GodownEntry oge inner join GodownEntryDiposableGoodsItem ged on oge.id = ged.godownEntryID inner join DisposableGoodsBatch dgbs on ged.disposableGoodsBatchID=dgbs.id inner join DisposableGoods dg on dgbs.diposablegoods_id = dg.id " +" where oge.type ='退库单' and oge.subType='调拨出库' and "+getHandleDeptCodeSql("oge.orgUnitCode")+" " +outEntryFilterSql @@ -5900,7 +5900,7 @@ + " group by oge.targetOrgUnitName, ged.goodsName,dgbs.batchNumber,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; // 盘亏出库 String stocktakeOutDiposableGoodsSql = " union all (select "+ noMaterialAmountSql +" oge.orgUnitName depart, oge.orgUnitName"+dgbsOfSupplierNameSql+" as settleaccountsdepart"+ noQueryIntegralSql +",ged.goodsName,sum(ged.amount)," - +"sum(ged.price*ged.amount) as settlementprice,sum(ged.price*ged.amount) as settlementDiscountPrice,'一次性物品' as type,dgbs.batchNumber as batchNumber,dgbs.expDate,0 as tousseDefinitionId,null amountType " + +"sum(ged.price*ged.amount) as settlementprice,sum(ged.price*ged.amount) as settlementDiscountPrice,'一次性物品' as type,dgbs.batchNumber as batchNumber,dgbs.expDate,0 as tousseDefinitionId " +" from GodownEntry oge inner join GodownEntryDiposableGoodsItem ged on oge.id = ged.godownEntryID inner join DisposableGoodsBatch dgbs on ged.disposableGoodsBatchID=dgbs.id inner join DisposableGoods dg on dgbs.diposablegoods_id = dg.id " +" where oge.type ='退库单' and oge.subType='盘亏出库' and "+getHandleDeptCodeSql("oge.orgUnitCode")+" " +outEntryFilterSql @@ -5936,13 +5936,13 @@ } } if(TYPE_CHARGE.equals(typeSearch)){ - String chargeSql = String.format(" select "+ noMaterialAmountSql +"ci.orgUnitName depart, ci.orgUnitName settleAccountsDepart"+ noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type ,'' batchNumber,NULL as expDate ,null as tousseDefinitionId,null amountType " + String chargeSql = String.format(" select "+ noMaterialAmountSql +"ci.orgUnitName depart, ci.orgUnitName settleAccountsDepart"+ noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type ,'' batchNumber,NULL as expDate ,null as tousseDefinitionId " + " from ChargeRecordItem ci inner join ChargeRecord cr on cr.id=ci.chargeRecord_id where 1=1 %s %s %s %s group by ci.orgUnitName,chargeItem,price,chargeTime " , chargeDepartSql,chargeDateSql,chargeItemSql, ciOrgUnitCodeSql); sql = chargeSql; }else if(TYPE_ALL.equals(typeSearch) || StringUtils.isBlank(typeSearch)){ - sql += String.format(" union all select "+ noMaterialAmountSql +"ci.orgUnitName depart,ci.orgUnitName settleAccountsDepart"+ noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type ,'' batchNumber,NULL as expDate ,null as tousseDefinitionId,null amountType " + sql += String.format(" union all select "+ noMaterialAmountSql +"ci.orgUnitName depart,ci.orgUnitName settleAccountsDepart"+ noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type ,'' batchNumber,NULL as expDate ,null as tousseDefinitionId " + " from ChargeRecordItem ci inner join ChargeRecord cr on cr.id=ci.chargeRecord_id where 1=1 %s %s %s %s group by ci.orgUnitName,chargeItem,price,chargeTime " , chargeDepartSql,chargeDateSql,chargeItemSql, ciOrgUnitCodeSql); @@ -5995,8 +5995,7 @@ int totalAmount = amount; DepartmentMonthDetailItem mdi = new DepartmentMonthDetailItem(); boolean flag = true; - String amountType = rs.getString("amountType"); - if (!"invoiceItem".equals(amountType) && TousseDefinition.PACKAGE_TYPE_DISINFECTION.equals(type) + if (TousseDefinition.PACKAGE_TYPE_DISINFECTION.equals(type) || (TousseDefinition.PACKAGE_TYPE_CUSTOM.equals(type) && !showCustonTousseAmount)) { Long toussedefinitionId = rs.getLong("tousseDefinitionId"); if (DatabaseUtil.isPoIdValid(toussedefinitionId)) {