Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r12943 -r12953 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 12943) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 12953) @@ -3648,11 +3648,10 @@ if(StringUtils.isNotBlank(department)){ invoiceWheresql += " and i.settleAccountsDepart = '"+department+"'"; } - String invoicePriceSql = "select p.settleAccountsDepartCoding as coding,min(i.settleAccountsDepart) " + String invoicePriceSql = "select '' as coding,min(i.settleAccountsDepart) " + "as depart,sum(ii.settlementPrice) as price,ii.diposable from Invoice i," - + "invoiceplan p,InvoiceItem ii where i.invoiceplan_id = p.id and i.id = " - + "ii.invoice_id and i.orgUnitCoding = '"+handleDeptCode+"' and (i.status ='收货签收' or " - + "i.status = '已发货') " + invoiceWheresql + " group by p.settleAccountsDepartCoding,ii.diposable"; + + "InvoiceItem ii where i.id = ii.invoice_id and i.orgUnitCoding = '" + handleDeptCode + "' and (i.status ='收货签收' or " + + "i.status = '已发货') " + invoiceWheresql + " group by i.settleAccountsDepart,ii.diposable"; ResultSet rs = objectDao.executeSql(invoicePriceSql); try { while(rs.next()){ @@ -3674,9 +3673,9 @@ DatabaseUtil.closeResultSetAndStatement(rs); } - String invoiceAmountSql = "select p.settleAccountsDepartCoding as coding,i.settleAccountsDepart " - + "as depart,i.tousseAmount,i.diposableGoodsAmount from Invoice i,invoiceplan p " - + "where i.invoiceplan_id = p.id and i.orgUnitCoding = '"+handleDeptCode+"' " + String invoiceAmountSql = "select '' as coding,i.settleAccountsDepart " + + "as depart,i.tousseAmount,i.diposableGoodsAmount from Invoice i " + + "where i.orgUnitCoding = '" + handleDeptCode + "' " + "and (i.status ='收货签收' or i.status = '已发货') " + invoiceWheresql; ResultSet rs2 = objectDao.executeSql(invoiceAmountSql); @@ -3701,7 +3700,7 @@ String recyclingErrorSql = "select r.departCode,r.depart,r.materialName,sum(r.materialCost*r.amount),sum(r.amount) " + " from RecyclingError r where r.packingTask_ID is null and r.errorType = '缺失' " - + " and r.handleDepartCode = '"+handleDeptCode+"' and r.missTime " + betweenSql; + + " and r.handleDepartCode = '" + handleDeptCode + "' and r.missTime " + betweenSql; if(StringUtils.isNotBlank(department)){ recyclingErrorSql += " and r.depart = '"+department+"' "; @@ -3729,7 +3728,7 @@ String damageSql = "select r.departCoding,r.depart,i.materialName,sum(i.materialCost*i.additionalAmount),sum(i.additionalAmount) " + " from RecyclingDamageRecord r,RecyclingDamageItem i where r.id = i.RecyclingDamageRecord_ID " - + " and r.additionalDeptCode = '"+handleDeptCode+"' and r.status != '未补充' and i.additionalTime is not null and i.additionalTime " + + " and r.additionalDeptCode = '" + handleDeptCode + "' and r.status != '未补充' and i.additionalTime is not null and i.additionalTime " + betweenSql; if(StringUtils.isNotBlank(department)){ @@ -3758,7 +3757,7 @@ String receiveSql = "select i.type,(i.amount*i.price),i.amount,r.departcoding " + "from ReceiveRecord r,ReceiveRecordItem i where r.id = i.receiverecord_id " - + "and r.departCoding = '"+handleDeptCode+"' and r.time " + betweenSql; + + "and r.departCoding = '" + handleDeptCode + "' and r.time " + betweenSql; SupplyRoomConfig config = supplyRoomConfigManager.getFirstSupplyRoomConfig(); ResultSet rs4 = objectDao.executeSql(receiveSql); @@ -3782,7 +3781,7 @@ DatabaseUtil.closeResultSetAndStatement(rs4); } - String returnSql = "select r.type,(-i.settlementPrice),-i.amount,r.departcoding,r.depart " + String returnSql = "select r.type,(-i.settlementPrice),-i.amount,'',r.depart " + "from ReturnGoodsRecord r,ReturnGoodsItem i where r.id = i.returnGoodsRecord_ID" + " and r.handleDepartCode = '"+handleDeptCode+"' and r.returnTime " + betweenSql; if(StringUtils.isNotBlank(department)){ @@ -3861,7 +3860,7 @@ startTime += " 00:00:00"; endTime += " 23:59:59"; - + String handleDeptCode = AcegiHelper.getLoginUser().getOrgUnitCodingFromSupplyRoomConfig(); String monthFilterSql = ""; String returnGoodsMonthFilterSql = ""; String lostMaterialFilterSql = ""; @@ -4018,21 +4017,21 @@ supplyRoomDiposableGoodsFilterSql += " and dbs.batchNumber = '"+batch+"'"; } - String tousseSql = "select p.settleaccountsdepart,ii.toussename as name,sum(ii.amount) as amount,sum(ii.settlementPrice) as settlementprice,ii.tousseType as type" - + ",'' from invoice i,InvoiceItem ii,invoicePlan p " - + " where i.id = ii.invoice_id and i.invoicePlan_ID = p.id " - + " and ii.diposable = '否'" + String tousseSql = "select i.settleAccountsDepart,ii.toussename as name,sum(ii.amount) as amount,sum(ii.settlementPrice) as settlementprice,ii.tousseType as type" + + ",'' from invoice i,InvoiceItem ii " + + " where i.id = ii.invoice_id " + + " and ii.diposable = '否' and i.orgUnitCoding = '"+handleDeptCode+"' " + monthFilterSql + departFilterSql + typeFilterSql + goodsNameFilterSql - + " and i.settleaccountsdepart is not null and p.type != '二级供应室申请单' group by p.settleaccountsdepart,ii.tousseType,ii.toussename"; + + " and i.settleaccountsdepart is not null group by i.settleAccountsDepart,ii.tousseType,ii.toussename"; String diposableGoodsSql = ""; if(StringUtils.isBlank(typeSearch) || "一次性物品".equals(typeSearch)){ diposableGoodsSql = " union all (select i.settleaccountsdepart,d.name as name,sum(d.amount) as amount,sum(d.fluctuationPrice*d.amount) as settlementprice,'一次性物品' as type" + ",d.batch from invoice i,DiposableGoodsItem d " - + " where i.id = d.invoice_id " + + " where i.id = d.invoice_id and i.orgUnitCoding = '"+handleDeptCode+"' " + monthFilterSql + departFilterSql + nameFilterSql @@ -4041,22 +4040,22 @@ String returnGoodsSql = " union all (select rr.depart as settleaccountsdepart,ri.toussename as name," + "-sum(ri.amount) as amount,-sum(ri.settlementPrice) as settlementprice,rr.type as type" - + ",ri.batchNumber from returngoodsrecord rr,returngoodsitem ri where rr.id = ri.returngoodsrecord_id " + + ",ri.batchNumber from returngoodsrecord rr,returngoodsitem ri where rr.id = ri.returngoodsrecord_id and rr.handleDepartCode = '"+handleDeptCode+"' " + returnGoodsMonthFilterSql + " group by rr.depart,ri.toussename,rr.type,ri.batchNumber)"; String lostMaterial = " union all (select r.depart,r.materialName,sum(r.amount),sum(r.amount*r.materialCost),'新增器械' as type,'' " - + " from RecyclingError r where r.packingTask_ID is null and r.errorType = '缺失' " + + " from RecyclingError r where r.packingTask_ID is null and r.errorType = '缺失' and r.handleDepartCode = '"+handleDeptCode+"' " + lostMaterialFilterSql + "group by r.depart,r.materialName )"; String damageMaterial = " union all (select r.depart,i.materialName,sum(i.additionalAmount),sum(i.materialCost*i.additionalAmount),'新增器械','' " + "from RecyclingDamageRecord r,RecyclingDamageItem i where r.id = i.RecyclingDamageRecord_ID " - + "and r.status != '未补充' and i.additionalTime is not null " + damageFilterSql + + "and r.status != '未补充' and r.additionalDeptCode = '"+handleDeptCode+"' and i.additionalTime is not null " + damageFilterSql + " group by r.departCoding,r.depart,i.materialName )"; String supplyRoomDiposableGoodsSql = " union all (select '消毒供应中心' as settleaccountsdepart,rri.goodsName,sum(rri.amount),sum(rri.price*rri.amount) as settlementprice,rri.type as type,dbs.batchNumber " +" from ReceiveRecord rr,ReceiveRecordItem rri left join " + DisposableGoodsBatchStock.class.getSimpleName() + " dbs " - +" on rri.diposableGoodBatchStock_id = dbs.id where rr.id = rri.receiveRecord_id " + +" on rri.diposableGoodBatchStock_id = dbs.id where rr.id = rri.receiveRecord_id and rr.departCoding = '"+handleDeptCode+"' " +supplyRoomDiposableGoodsFilterSql + " group by rri.goodsName,rri.amount,rri.type,dbs.batchNumber) ";