Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r13085 -r13088 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 13085) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 13088) @@ -3981,7 +3981,11 @@ endTime += " 23:59:59"; String handleDeptCode = AcegiHelper.getLoginUser().getOrgUnitCodingFromSupplyRoomConfig(); String monthFilterSql = ""; - String returnGoodsMonthFilterSql = ""; + String returnGoodsFilterSql = ""; + String returnGoodsTimeFilterSql = ""; + String returnGoodsDepartFilterSql = ""; + String materialNameFilterSql = ""; + String materialTypeFilterSql = ""; String lostMaterialFilterSql = ""; String damageFilterSql = ""; String supplyRoomDiposableGoodsFilterSql = ""; @@ -4009,12 +4013,13 @@ + " and " + dateQueryAdapter.dateConverAdapter2( endTime , "yyyy-mm-dd HH24:MI:SS"); - returnGoodsMonthFilterSql = " and rr.returntime between " + returnGoodsTimeFilterSql = " and rr.returntime between " + dateQueryAdapter.dateConverAdapter2( startTime , "yyyy-mm-dd HH24:MI:SS") + " and " + dateQueryAdapter.dateConverAdapter2( endTime , "yyyy-mm-dd HH24:MI:SS"); + returnGoodsFilterSql = returnGoodsTimeFilterSql; lostMaterialFilterSql = " and r.missTime between " + dateQueryAdapter.dateConverAdapter2( startTime , "yyyy-mm-dd HH24:MI:SS") @@ -4036,16 +4041,18 @@ , "yyyy-mm-dd HH24:MI:SS"); } else { monthFilterSql = " and 1=2"; - returnGoodsMonthFilterSql = " and 1=2"; + returnGoodsFilterSql = " and 1=2"; lostMaterialFilterSql = " and 1=2"; damageFilterSql = " and 1=2"; } String departFilterSql = ""; if (StringUtils.isNotBlank(departSearch)) { departFilterSql = " and i.settleaccountsdepart = '" + departSearch + "'"; - returnGoodsMonthFilterSql += " and rr.depart = '" + departSearch + returnGoodsFilterSql += " and rr.depart = '" + departSearch + "'"; + returnGoodsDepartFilterSql = " and rr.depart = '" + departSearch + + "'"; lostMaterialFilterSql += " and r.depart = '" + departSearch + "'"; damageFilterSql += " and r.depart = '" @@ -4054,23 +4061,23 @@ supplyRoomDiposableGoodsFilterSql += " and rr.departCoding = '"+departCoding+"'"; } String typeFilterSql = ""; + materialTypeFilterSql = " "; if (StringUtils.isNotBlank(typeSearch)) { if ("全部器械包".equals(typeSearch)) { typeFilterSql = " and ii.tousseType != '一次性物品'"; - returnGoodsMonthFilterSql += " and rr.type = '器械包'"; + returnGoodsFilterSql += " and rr.type = '器械包'"; supplyRoomDiposableGoodsFilterSql += " and 1=2"; - }else if("器械包".equals(typeSearch)) { typeFilterSql = " and ii.tousseType = '" + TousseDefinition.PACKAGE_TYPE_INSIDE + "'"; - returnGoodsMonthFilterSql += " and rr.type = '器械包'"; + returnGoodsFilterSql += " and rr.type = '器械包'"; supplyRoomDiposableGoodsFilterSql += " and 1=2"; } else if ("消毒物品".equals(typeSearch)) { typeFilterSql = " and ii.tousseType = '" + TousseDefinition.PACKAGE_TYPE_DISINFECTION + "'"; - returnGoodsMonthFilterSql += " and rr.type = '消毒物品'"; + returnGoodsFilterSql += " and rr.type = '消毒物品'"; supplyRoomDiposableGoodsFilterSql += " and 1=2"; } else if ("外来器械包".equals(typeSearch)) { @@ -4079,24 +4086,24 @@ + "' or ii.tousseType = '" + TousseDefinition.PACKAGE_TYPE_SPLIT + "') "; - returnGoodsMonthFilterSql += " and rr.type = '外来器械包'"; + returnGoodsFilterSql += " and rr.type = '外来器械包'"; supplyRoomDiposableGoodsFilterSql += " and 1=2"; } else if ("自定义器械包".equals(typeSearch)) { typeFilterSql = " and ii.tousseType = '" + TousseDefinition.PACKAGE_TYPE_CUSTOM + "'"; - returnGoodsMonthFilterSql += " and rr.type = '自定义器械包'"; + returnGoodsFilterSql += " and rr.type = '自定义器械包'"; supplyRoomDiposableGoodsFilterSql += " and 1=2"; } else if ("外部代理灭菌包".equals(typeSearch)) { typeFilterSql = " and ii.tousseType = '" + TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY + "'"; - returnGoodsMonthFilterSql += " and rr.type = '外部器械包'"; + returnGoodsFilterSql += " and rr.type = '外部器械包'"; lostMaterialFilterSql += " and 1=2 "; damageFilterSql += " and 1=2 "; supplyRoomDiposableGoodsFilterSql += " and 1=2"; } else if ("一次性物品".equals(typeSearch)) { typeFilterSql = " and ii.tousseType = '一次性物品'"; - returnGoodsMonthFilterSql += " and rr.type = '一次性物品'"; + returnGoodsFilterSql += " and rr.type = '一次性物品'"; lostMaterialFilterSql += " and 1=2 "; damageFilterSql += " and 1=2 "; supplyRoomDiposableGoodsFilterSql += " and rri.type = '一次性物品'"; @@ -4108,7 +4115,7 @@ if(disinfection){ goodsNameFilterSql = " and ii.toussename like '%" + goodsNameSearch + "%'"; - returnGoodsMonthFilterSql += " and ri.toussename like '%" + returnGoodsFilterSql += " and ri.toussename like '%" + goodsNameSearch + "%'"; lostMaterialFilterSql += " and r.materialName like '%" + goodsNameSearch + "%'"; @@ -4120,8 +4127,10 @@ if("精确查询".equals(searchType)){ goodsNameFilterSql = " and ii.toussename = '" + goodsNameSearch + "'"; - returnGoodsMonthFilterSql += " and ri.toussename = '" + returnGoodsFilterSql += " and ri.toussename = '" + goodsNameSearch + "'"; + materialNameFilterSql = " ii.materialName = '" + + goodsNameSearch + "'"; lostMaterialFilterSql += " and r.materialName = '" + goodsNameSearch + "'"; damageFilterSql += " and i.materialName = '" @@ -4130,8 +4139,10 @@ }else{ goodsNameFilterSql = " and ii.toussename like '%" + goodsNameSearch + "%'"; - returnGoodsMonthFilterSql += " and ri.toussename like '%" + returnGoodsFilterSql += " and ri.toussename like '%" + goodsNameSearch + "%'"; + materialNameFilterSql = " and ii.materialName like '%" + + goodsNameSearch + "%'"; lostMaterialFilterSql += " and r.materialName like '%" + goodsNameSearch + "%'"; damageFilterSql += " and r.materialName like '%" @@ -4147,7 +4158,7 @@ if (StringUtils.isNotBlank(batch)) { nameFilterSql += " and d.batch = '" + batch + "'"; goodsNameFilterSql += " and 1=2 "; - returnGoodsMonthFilterSql += " and ri.batchNumber = '"+batch+"' "; + returnGoodsFilterSql += " and ri.batchNumber = '"+batch+"' "; lostMaterialFilterSql += " and 1=2 "; damageFilterSql += " and 1=2 "; supplyRoomDiposableGoodsFilterSql += " and dbs.batchNumber = '"+batch+"'"; @@ -4185,20 +4196,24 @@ + " from MaterialInvoice i," + "MaterialInvoiceItem ii where i.id = ii.materialInvoiceId " + monthFilterSql - + departFilterSql + + departFilterSql + + materialNameFilterSql + + materialTypeFilterSql + " group by i.settleaccountsdepart,ii.materialName) "; // 材料退货 - String materialReturnSql = " union all (select i.depart as settleaccountsdepart,ii.materialName,-sum(ii.amount),-sum(ii.settlementPrice),'材料' as type,'' " - + " from ReturnMaterialRecord i," - + "ReturnMaterialItem ii where i.id = ii.returnMaterialRecord_ID " - + monthFilterSql - + departFilterSql - + " group by i.settleaccountsdepart,ii.materialName) "; + String materialReturnSql = " union all (select rr.depart as settleaccountsdepart,ii.materialName,-sum(ii.amount),-sum(ii.settlementPrice),'材料' as type,'' " + + " from ReturnMaterialRecord rr," + + "ReturnMaterialItem ii where rr.id = ii.returnMaterialRecord_ID " + + returnGoodsTimeFilterSql + + returnGoodsDepartFilterSql + + materialNameFilterSql + + materialTypeFilterSql + + " group by rr.depart,ii.materialName) "; 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 and rr.handleDepartCode = '"+handleDeptCode+"' " - + returnGoodsMonthFilterSql + + returnGoodsFilterSql +" and "+returnGoodsItemPredicate + " group by rr.depart,ri.toussename,rr.type,ri.batchNumber)";