Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35408 -r35409 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35408) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35409) @@ -3572,6 +3572,12 @@ } return months; } + /** + * 此方法多次调用时会多次查询数据库 不建议使用 建议使用getHandleDeptCodeSql(String, boolean , String) + * @param field + * @return + */ + @Deprecated private String getHandleDeptCodeSql(String field) { LoginUserData user = AcegiHelper.getLoginUser(); String handleDeptCodeSql = ""; @@ -3743,6 +3749,15 @@ //SupplyRoomConfig firstSupplyRoomConfig = supplyRoomConfigManager.getFirstSupplyRoomConfig(); //当前用户是否为供应室(包含一二级供应室)用户 boolean isSupplyRoomUser = supplyRoomConfigManager.isSupplyRoomUser(); + LoginUserData user = AcegiHelper.getLoginUser(); + String orgUnitCoding = null; + if (user != null){ + orgUnitCoding = user.getOrgUnitCodingFromSupplyRoomConfig(); + } + boolean isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser = false; + if (isSupplyRoomUser || supplyRoomConfigManager.isExpensiveGoodsSupplyRoomUser()) { + isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser = true; + } List list = new ArrayList(); String invoiceWheresql = "" ; //高值耗材(新)过滤条件 @@ -3837,15 +3852,18 @@ + ",sum(ii.settlementPrice) as price,ii.diposable,sum(ii.settlementDiscountPrice) as settlementDiscountPrice,ii.tousseType from " + Invoice.class.getSimpleName() + " i join " + InvoiceItem.class.getSimpleName() + " ii on i.id = ii.invoice_id " + invoiceJoinInvoicePlanSql - + "where 1=1 and "+invoiceItemDisposableGoodsTypePredicate+" and " + getHandleDeptCodeSql("i.orgUnitCoding") + " and (i.status ='收货签收' or " + + "where 1=1 and "+invoiceItemDisposableGoodsTypePredicate+" and " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and (i.status ='收货签收' or " + "i.status = '已发货') " + iDepartCodingWhereSql + invoiceWheresql + tousseTypeInvoiceSql + excludeSciProjectSql + " group by "+ iDepartSql +" i.settleAccountsDepartCoding,i.settleAccountsDepart,ii.diposable,ii.tousseType"+queryProjNameSql; //如果为高值耗材处理科室用户,并且启用高值耗材功能,且所选物品类型为全部或高值耗材时,则将高值耗材退货数据也抵冲掉 if(enableExpensiveGoods && (StringUtils.isBlank(tousseType) || StringUtils.equals(ExpensiveGoods.TYPE_NAME, tousseType))){ invoicePriceSql += " union all (select "+ iDepartSql +"i.settleAccountsDepartCoding coding,i.settleAccountsDepart settleAccountsDepart,-1 * sum(egi.price) price,'否' as diposable,-1 * sum(egi.price) settlementDiscountPrice,'高值耗材' as tousseType " + " 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 " - + " where " + getHandleDeptCodeSql("i.orgUnitCoding") + " and egge.type='退库单' " + expensiveGoodsWheresql + + " where "+ getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and egge.type='退库单' " + expensiveGoodsWheresql + iDepartCodingWhereSql + " group by "+ iDepartSql + "i.settleAccountsDepartCoding,i.settleAccountsDepart)"; } @@ -3922,7 +3940,9 @@ + " from ReturnGoodsRecord r join ReturnGoodsItem i on r.id = i.returnGoodsRecord_ID " + leftJoinSqlForReturnSql + joinSql - + " where "+returnGoodsItemPredicate+ tousseTypeReturnSql + rDepartCodingWhereSql + " and "+getHandleDeptCodeSql("r.handleDepartCode")+" and r.returnTime " + betweenSql; + + " where "+returnGoodsItemPredicate+ tousseTypeReturnSql + rDepartCodingWhereSql + " and " + + getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + +" and r.returnTime " + betweenSql; if(StringUtils.isNotBlank(department)){ returnSql += " and r.settleAccountsDepart = '" + department + "'"; }else if(!(departments == null || departments.isEmpty())){ @@ -4001,7 +4021,9 @@ // 材料发货价格 String materialInvoicePriceSql = "select "+ iDepartSql+"i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + ",sum(ii.settlementPrice) as price from MaterialInvoice i," - + "MaterialInvoiceItem ii where i.id = ii.materialInvoice_id and " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + invoiceWheresql + iDepartCodingWhereSql + " group by " + + "MaterialInvoiceItem ii where i.id = ii.materialInvoice_id and " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + invoiceWheresql + iDepartCodingWhereSql + " group by " + iDepartSql +"i.settleAccountsDepartCoding,i.settleAccountsDepart"; ResultSet materialInvoicePriceResultSet = objectDao.executeSql(materialInvoicePriceSql); @@ -4036,7 +4058,7 @@ // 材料发货数量 String materialInvoiceAmountSql = "select "+ iDepartSql +"i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + ",mi.amount from MaterialInvoice i inner join MaterialInvoiceItem mi on i.id=mi.materialInvoice_id " - + "where " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + + "where " + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + " and (mi.amount is not null and mi.amount>0) " + " " + invoiceWheresql + iDepartCodingWhereSql; @@ -4076,7 +4098,9 @@ } //材料报损统计 String damageSql = "select "+ iDepartSql +"i.settleAccountsDepartCoding departCode,i.settleAccountsDepart,i.materialName,sum(i.materialCost*i.additionalAmount) money,sum(i.additionalAmount) amount " - + " from MaterialErrorDamageDetail i where " + banQuery + getHandleDeptCodeSql("i.handleDepartCode") + " and i.type = '" + + " from MaterialErrorDamageDetail i where " + banQuery + + getHandleDeptCodeSql("i.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and i.type = '" + MaterialErrorDamageDetail.TYPE_DAMAGE + "' and i.additionalTime " + betweenSql + iDepartCodeWhereSql; @@ -4123,7 +4147,8 @@ //材料丢失统计 String recyclingErrorSql = "select "+ rDepartSql +"r.settleAccountsDepartCoding departCode,r.settleAccountsDepart,r.materialName,sum(r.materialCost*r.additionalAmount) money,sum(r.additionalAmount) amount " + " from MaterialErrorDamageDetail r where r.errorType = '缺失' and r.type = '"+MaterialErrorDamageDetail.TYPE_ERROR+"' and r.materialName != '器械包标识牌' " - + " and " + getHandleDeptCodeSql("r.handleDepartCode") + " and r.additionalTime " + betweenSql + rDepartCodeWhereSql; + + " and "+ getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and r.additionalTime " + betweenSql + rDepartCodeWhereSql; if(StringUtils.isNotBlank(department)){ recyclingErrorSql += " and r.settleAccountsDepart = '"+department+"' "; @@ -4168,7 +4193,8 @@ // 材料退货 String materialReturnSql = "select "+ rDepartSql +"r.settleAccountsDepartCoding departCoding,r.settleAccountsDepart , (-i.settlementPrice) settlementPrice,-i.amount amount " + "from ReturnMaterialRecord r,ReturnMaterialItem i where r.id = i.returnMaterialRecord_ID" - + " and "+getHandleDeptCodeSql("r.handleDepartCode")+" and r.returnTime " + betweenSql + rDepartCodingWhereSql; + + " and "+ getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + +" and r.returnTime " + betweenSql + rDepartCodingWhereSql; if(StringUtils.isNotBlank(department)){ materialReturnSql += " and r.settleAccountsDepart = '" + department + "'"; }else if(!(departments == null || departments.isEmpty())){ @@ -4216,7 +4242,8 @@ + queryProjNameSql + ",ii.amount from Invoice i inner join InvoiceItem ii on i.id = ii.invoice_id " + invoiceJoinInvoicePlanSql - + "where " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + + "where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) +tousseTypeSql +" and "+disposableGoodsInvoiceAmountPredicate + excludeSciProjectSql @@ -4324,7 +4351,9 @@ //供应室领用(器械材料与一次性物品) String receiveSql = "select '一次性物品',(ged.amount*ged.price),ged.amount,oge.targetOrgUnitCode,oge.targetOrgUnitName " + "from GodownEntry oge inner join GodownEntryDiposableGoodsItem ged on oge.id = ged.godownEntryID where oge.type ='退库单' and oge.subType='调拨出库' " - + "and "+outEntryPredicate+" and " + getHandleDeptCodeSql("oge.orgUnitCode") + " and oge.time " + betweenSql + ogeTargetOrgUnitCodeWhereSql; + + "and "+outEntryPredicate+" and " + + getHandleDeptCodeSql("oge.orgUnitCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and oge.time " + betweenSql + ogeTargetOrgUnitCodeWhereSql; if(StringUtils.isNotBlank(department)){ receiveSql += " and oge.targetOrgUnitName = '" + department + "'"; }else if(!(departments == null || departments.isEmpty())){ @@ -4369,7 +4398,9 @@ //供应室领用(器械材料与一次性物品) String receiveSql = "select '一次性物品',(ged.amount*ged.price),ged.amount,oge.orgUnitCode,oge.orgUnitName " + "from GodownEntry oge inner join GodownEntryDiposableGoodsItem ged on oge.id = ged.godownEntryID where oge.type ='退库单' and oge.subType='盘亏出库' " - + "and "+outEntryPredicate+" and " + getHandleDeptCodeSql("oge.orgUnitCode") + " and oge.time " + betweenSql + ogeTargetOrgUnitCodeWhereSql; + + "and "+outEntryPredicate+" and " + + getHandleDeptCodeSql("oge.orgUnitCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and oge.time " + betweenSql + ogeTargetOrgUnitCodeWhereSql; if(StringUtils.isNotBlank(department)){ receiveSql += " and oge.orgUnitName = '" + department + "'"; }else if(!(departments == null || departments.isEmpty())){ @@ -4415,7 +4446,9 @@ if(isSupplyRoomUser && (StringUtils.isBlank(tousseType) || TousseItem.TYPE_DIPOSABLE_GOODS.equals(tousseType))){ String sql = "select "+ prDepartSql +"'一次性物品' type,(dgi.amount*dgi.fluctuationPrice) price,dgi.amount,pr.orgUnitCoding,pr.orgUnitName " + "from PackingRecord pr inner join DiposableGoodsItem dgi on pr.id = dgi.packingRecordId where " - +packingDisposableGoodsTypePredicate+" and " + getHandleDeptCodeSql("pr.orgUnitCoding") + " and pr.packTime " + betweenSql + prDepartCodingWhereSql + containDiposableGoodsItemSql; + +packingDisposableGoodsTypePredicate+" and " + + getHandleDeptCodeSql("pr.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and pr.packTime " + betweenSql + prDepartCodingWhereSql + containDiposableGoodsItemSql; ResultSet rs = objectDao.executeSql(sql); try { while(rs.next()){ @@ -4444,7 +4477,9 @@ if(isSupplyRoomUser && (StringUtils.isBlank(tousseType) || TousseItem.TYPE_DIPOSABLE_GOODS.equals(tousseType))){ String sql = "select "+ prDepartSql +"'一次性物品' type,(dgi.amount*dgi.fluctuationPrice) price,dgi.amount,pr.orgUnitCoding,pr.orgUnitName " + "from PackingRecord pr inner join DiposableGoodsItem dgi on pr.id = dgi.packingRecordId where " - +packingDisposableGoodsTypePredicate+" and " + getHandleDeptCodeSql("pr.orgUnitCoding") + " and pr.packTime " + betweenSql + prDepartCodingWhereSql + excludeDiposableGoodsItemSql; + +packingDisposableGoodsTypePredicate+" and " + + getHandleDeptCodeSql("pr.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and pr.packTime " + betweenSql + prDepartCodingWhereSql + excludeDiposableGoodsItemSql; ResultSet rs = objectDao.executeSql(sql); try { @@ -4508,7 +4543,8 @@ + columnSqlForInvoiceAmountSql + " from Invoice i join InvoiceItem ii on i.id = ii.invoice_id " + leftJoinSqlForInvoiceAmountSql - + " where " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + + " where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + " and (ii.amount is not null and ii.amount>0) "// 限制为只查包含器械包(含消毒物品、敷料包等)数量的 + " and " + SqlUtils.getStringFieldInLargeCollectionsPredicate("ii.tousseType", allTousseType) + " and (i.status ='收货签收' or i.status = '已发货') " + invoiceWheresql + iDepartCodingWhereSql; @@ -4520,7 +4556,9 @@ + queryIntegralSql + " 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 " - + " where " + getHandleDeptCodeSql("i.orgUnitCoding") + " and egge.type='退库单' " + expensiveGoodsWheresql + iDepartCodingWhereSql + + " where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and egge.type='退库单' " + expensiveGoodsWheresql + iDepartCodingWhereSql + " group by "+ iDepartSql +"i.settleAccountsDepartCoding,i.settleAccountsDepart)"; } @@ -4628,6 +4666,15 @@ if(!isSupplyRoomUser){ department = AcegiHelper.getCurrentOrgUnitName(); } + LoginUserData user = AcegiHelper.getLoginUser(); + String orgUnitCoding = null; + if (user != null){ + orgUnitCoding = user.getOrgUnitCodingFromSupplyRoomConfig(); + } + boolean isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser = false; + if (isSupplyRoomUser || supplyRoomConfigManager.isExpensiveGoodsSupplyRoomUser()) { + isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser = true; + } List list = new ArrayList(); String invoiceWheresql = "" ; startDate += " 00:00:00"; @@ -4689,7 +4736,9 @@ String invoicePriceSql = "select i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + "as depart,sum(ii.settlementPrice) as price,ii.diposable,sum(ii.settlementDiscountPrice) as settlementDiscountPrice,ii.tousseType,dg.type from " + Invoice.class.getSimpleName() + " i," - + InvoiceItem.class.getSimpleName() + " ii left join "+ DisposableGoods.class.getSimpleName() +" dg on dg.id=ii.disposableGoodsId where i.id = ii.invoice_id and "+invoiceItemDisposableGoodsTypePredicate+" and " + getHandleDeptCodeSql("i.orgUnitCoding") + " and (i.status ='收货签收' or " + + InvoiceItem.class.getSimpleName() + " ii left join "+ DisposableGoods.class.getSimpleName() +" dg on dg.id=ii.disposableGoodsId where i.id = ii.invoice_id and "+invoiceItemDisposableGoodsTypePredicate+" and " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and (i.status ='收货签收' or " + "i.status = '已发货') " + invoiceWheresql + tousseTypeInvoiceSql + " group by i.settleAccountsDepartCoding,i.settleAccountsDepart,ii.diposable,ii.tousseType,dg.type"; ResultSet rs = objectDao.executeSql(invoicePriceSql); @@ -4771,7 +4820,9 @@ + leftJoinDisposableGoods + " dg on dg.id=i.disposableGoodsID " + leftJoinSqlForReturnSql - + " where "+returnGoodsItemPredicate+ tousseTypeReturnSql + " and "+getHandleDeptCodeSql("r.handleDepartCode")+" and r.returnTime " + betweenSql; + + " where "+returnGoodsItemPredicate+ tousseTypeReturnSql + " and " + + getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + +" and r.returnTime " + betweenSql; if(StringUtils.isNotBlank(department)){ returnSql += " and r.settleAccountsDepart = '" + department + "'"; } @@ -4824,7 +4875,9 @@ // 材料发货价格 String materialInvoicePriceSql = "select i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + "as depart,sum(ii.settlementPrice) as price from MaterialInvoice i," - + "MaterialInvoiceItem ii where i.id = ii.materialInvoice_id and " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + invoiceWheresql + " group by i.settleAccountsDepartCoding,i.settleAccountsDepart"; + + "MaterialInvoiceItem ii where i.id = ii.materialInvoice_id and " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " " + invoiceWheresql + " group by i.settleAccountsDepartCoding,i.settleAccountsDepart"; ResultSet materialInvoicePriceResultSet = objectDao.executeSql(materialInvoicePriceSql); try { while(materialInvoicePriceResultSet.next()){ @@ -4844,7 +4897,8 @@ // 材料发货数量 String materialInvoiceAmountSql = "select i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + "as depart,mi.amount from MaterialInvoice i inner join MaterialInvoiceItem mi on i.id=mi.materialInvoice_id " - + "where " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + + "where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + " and (mi.amount is not null and mi.amount>0) " + " " + invoiceWheresql; @@ -4873,7 +4927,9 @@ } //材料报损统计 String damageSql = "select i.settleAccountsDepartCoding departCode,i.settleAccountsDepart depart,i.materialName,sum(i.materialCost*i.additionalAmount),sum(i.additionalAmount) " - + " from MaterialErrorDamageDetail i where " + banQuery + getHandleDeptCodeSql("i.handleDepartCode") + " and i.type = '" + + " from MaterialErrorDamageDetail i where " + banQuery + + getHandleDeptCodeSql("i.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and i.type = '" + MaterialErrorDamageDetail.TYPE_DAMAGE + "' and i.additionalTime " + betweenSql; @@ -4904,7 +4960,9 @@ //材料丢失统计 String recyclingErrorSql = "select r.settleAccountsDepartCoding departCode,r.settleAccountsDepart depart,r.materialName,sum(r.materialCost*r.additionalAmount),sum(r.additionalAmount) " + " from MaterialErrorDamageDetail r where r.errorType = '缺失' and r.type = '"+MaterialErrorDamageDetail.TYPE_ERROR+"' and r.materialName != '器械包标识牌' " - + " and " + getHandleDeptCodeSql("r.handleDepartCode") + " and r.additionalTime " + betweenSql; + + " and " + + getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and r.additionalTime " + betweenSql; if(StringUtils.isNotBlank(department)){ recyclingErrorSql += " and r.settleAccountsDepart = '"+department+"' "; @@ -4933,7 +4991,9 @@ // 材料退货 String materialReturnSql = "select r.settleAccountsDepartCoding departCoding,r.settleAccountsDepart depart , (-i.settlementPrice),-i.amount " + "from ReturnMaterialRecord r,ReturnMaterialItem i where r.id = i.returnMaterialRecord_ID" - + " and "+getHandleDeptCodeSql("r.handleDepartCode")+" and r.returnTime " + betweenSql; + + " and " + + getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + +" and r.returnTime " + betweenSql; if(StringUtils.isNotBlank(department)){ materialReturnSql += " and r.settleAccountsDepart = '" + department + "'"; } @@ -4964,7 +5024,8 @@ String disposableGoodsInvoiceAmountSql = "select i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + "as depart,ii.amount,dg.type from Invoice i inner join InvoiceItem ii on i.id = ii.invoice_id " + leftJoinDisposableGoods - + " dg on dg.id=ii.disposableGoodsId where " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + + " dg on dg.id=ii.disposableGoodsId where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) +tousseTypeSql +" and "+disposableGoodsInvoiceAmountPredicate + " and (i.status ='收货签收' or i.status = '已发货') " + invoiceWheresql; @@ -5075,7 +5136,9 @@ + "from GodownEntry oge inner join GodownEntryDiposableGoodsItem ged on oge.id = ged.godownEntryID " + leftJoinDisposableGoods + " dg on dg.id=ged.disposableGoodsID where oge.type ='退库单' and oge.subType='调拨出库' " - + "and "+outEntryPredicate+" and " + getHandleDeptCodeSql("oge.orgUnitCode") + SqlUtils.getFiledWhereSql("oge.targetOrgUnitName", department, 40) +" and oge.time " + betweenSql; + + "and "+outEntryPredicate+" and " + + getHandleDeptCodeSql("oge.orgUnitCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + SqlUtils.getFiledWhereSql("oge.targetOrgUnitName", department, 40) +" and oge.time " + betweenSql; ResultSet rs4 = objectDao.executeSql(receiveSql); try { @@ -5097,7 +5160,9 @@ + " from GodownEntry oge inner join GodownEntryDiposableGoodsItem ged on oge.id = ged.godownEntryID " + leftJoinDisposableGoods + " dg on dg.id=ged.disposableGoodsID where oge.type ='退库单' and oge.subType='盘亏出库' " - + "and "+outEntryPredicate+" and " + getHandleDeptCodeSql("oge.orgUnitCode") + SqlUtils.getFiledWhereSql("oge.orgUnitName", department, 30)+" and oge.time " + betweenSql; + + "and "+outEntryPredicate+" and " + + getHandleDeptCodeSql("oge.orgUnitCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + SqlUtils.getFiledWhereSql("oge.orgUnitName", department, 30)+" and oge.time " + betweenSql; ResultSet rs4 = objectDao.executeSql(receiveSql); try { @@ -5119,7 +5184,9 @@ + "from PackingRecord pr inner join DiposableGoodsItem dgi on pr.id = dgi.packingRecordId " + leftJoinDisposableGoods + " dg on dg.id=dgi.disposableGoodsID where " - +packingDisposableGoodsTypePredicate+" and " + getHandleDeptCodeSql("pr.orgUnitCoding") + SqlUtils.getFiledWhereSql("pr.orgUnitName", department, 35) + " and pr.packTime " + betweenSql; + +packingDisposableGoodsTypePredicate+" and " + + getHandleDeptCodeSql("pr.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + SqlUtils.getFiledWhereSql("pr.orgUnitName", department, 35) + " and pr.packTime " + betweenSql; ResultSet rs = objectDao.executeSql(sql); try { @@ -5152,7 +5219,8 @@ + columnSqlForInvoiceAmountSql + " from Invoice i join InvoiceItem ii on i.id = ii.invoice_id " + leftJoinSqlForInvoiceAmountSql - + " where " + getHandleDeptCodeSql("i.orgUnitCoding") + + " where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + SqlUtils.getFiledWhereSql("i.settleAccountsDepart", department, 45) + " and (ii.amount is not null and ii.amount>0) "// 限制为只查包含器械包(含消毒物品、敷料包等)数量的 + " and " + SqlUtils.getStringFieldInLargeCollectionsPredicate("ii.tousseType", allTousseType) @@ -5314,6 +5382,15 @@ if(!isSupplyRoomUser){ department = AcegiHelper.getCurrentOrgUnitName(); } + LoginUserData user = AcegiHelper.getLoginUser(); + String orgUnitCoding = null; + if (user != null){ + orgUnitCoding = user.getOrgUnitCodingFromSupplyRoomConfig(); + } + boolean isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser = false; + if (isSupplyRoomUser || supplyRoomConfigManager.isExpensiveGoodsSupplyRoomUser()) { + isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser = true; + } List list = new ArrayList(); String invoiceWheresql = "" ; startDate += " 00:00:00"; @@ -5380,7 +5457,9 @@ String invoicePriceSql = "select i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + "as depart,sum(ii.settlementPrice) as price,ii.diposable,sum(ii.settlementDiscountPrice) as settlementDiscountPrice,ii.tousseType,td.sterilingMethod from " + Invoice.class.getSimpleName() + " i," - + InvoiceItem.class.getSimpleName() + " ii "+ leftJonsTousseDefinition +" td.id=ii.tousseDefinitionId where i.id = ii.invoice_id and "+invoiceItemDisposableGoodsTypePredicate+" and " + getHandleDeptCodeSql("i.orgUnitCoding") + " and (i.status ='收货签收' or " + + InvoiceItem.class.getSimpleName() + " ii "+ leftJonsTousseDefinition +" td.id=ii.tousseDefinitionId where i.id = ii.invoice_id and "+invoiceItemDisposableGoodsTypePredicate+" and " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and (i.status ='收货签收' or " + "i.status = '已发货') " + invoiceWheresql + tousseTypeInvoiceSql + " group by i.settleAccountsDepartCoding,i.settleAccountsDepart,ii.diposable,ii.tousseType,td.sterilingMethod"; ResultSet rs = objectDao.executeSql(invoicePriceSql); @@ -5424,7 +5503,9 @@ + leftJonsTousseDefinition + " td.id=i.tousseDefinition_id　" + leftJoinSqlForReturnSql - + " where "+returnGoodsItemPredicate+ tousseTypeReturnSql + " and "+getHandleDeptCodeSql("r.handleDepartCode")+" and r.returnTime " + betweenSql; + + " where "+returnGoodsItemPredicate+ tousseTypeReturnSql + " and " + + getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + +" and r.returnTime " + betweenSql; if(StringUtils.isNotBlank(department)){ returnSql += " and r.settleAccountsDepart = '" + department + "'"; } @@ -5477,7 +5558,9 @@ // 材料发货价格 String materialInvoicePriceSql = "select i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + "as depart,sum(ii.settlementPrice) as price from MaterialInvoice i," - + "MaterialInvoiceItem ii where i.id = ii.materialInvoice_id and " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + invoiceWheresql + " group by i.settleAccountsDepartCoding,i.settleAccountsDepart"; + + "MaterialInvoiceItem ii where i.id = ii.materialInvoice_id and " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " " + invoiceWheresql + " group by i.settleAccountsDepartCoding,i.settleAccountsDepart"; ResultSet materialInvoicePriceResultSet = objectDao.executeSql(materialInvoicePriceSql); try { while(materialInvoicePriceResultSet.next()){ @@ -5497,7 +5580,8 @@ // 材料发货数量 String materialInvoiceAmountSql = "select i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + "as depart,mi.amount from MaterialInvoice i inner join MaterialInvoiceItem mi on i.id=mi.materialInvoice_id " - + "where " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + + "where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + " and (mi.amount is not null and mi.amount>0) " + " " + invoiceWheresql; @@ -5523,7 +5607,9 @@ } //材料报损统计 String damageSql = "select i.settleAccountsDepartCoding departCode,i.settleAccountsDepart depart,i.materialName,sum(i.materialCost*i.additionalAmount),sum(i.additionalAmount) " - + " from MaterialErrorDamageDetail i where " + banQuery + getHandleDeptCodeSql("i.handleDepartCode") + " and i.type = '" + + " from MaterialErrorDamageDetail i where " + banQuery + + getHandleDeptCodeSql("i.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and i.type = '" + MaterialErrorDamageDetail.TYPE_DAMAGE + "' and i.additionalTime " + betweenSql; @@ -5554,7 +5640,9 @@ //材料丢失统计 String recyclingErrorSql = "select r.settleAccountsDepartCoding departCode,r.settleAccountsDepart depart,r.materialName,sum(r.materialCost*r.additionalAmount),sum(r.additionalAmount) " + " from MaterialErrorDamageDetail r where r.errorType = '缺失' and r.type = '"+MaterialErrorDamageDetail.TYPE_ERROR+"' and r.materialName != '器械包标识牌' " - + " and " + getHandleDeptCodeSql("r.handleDepartCode") + " and r.additionalTime " + betweenSql; + + " and " + + getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + " and r.additionalTime " + betweenSql; if(StringUtils.isNotBlank(department)){ recyclingErrorSql += " and r.settleAccountsDepart = '"+department+"' "; @@ -5583,7 +5671,9 @@ // 材料退货 String materialReturnSql = "select r.settleAccountsDepartCoding departCoding,r.settleAccountsDepart depart , (-i.settlementPrice),-i.amount " + "from ReturnMaterialRecord r,ReturnMaterialItem i where r.id = i.returnMaterialRecord_ID" - + " and "+getHandleDeptCodeSql("r.handleDepartCode")+" and r.returnTime " + betweenSql; + + " and " + + getHandleDeptCodeSql("r.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + +" and r.returnTime " + betweenSql; if(StringUtils.isNotBlank(department)){ materialReturnSql += " and r.settleAccountsDepart = '" + department + "'"; } @@ -5613,7 +5703,8 @@ // 一次性发货数量统计 String disposableGoodsInvoiceAmountSql = "select i.settleAccountsDepartCoding as coding,i.settleAccountsDepart " + "as depart,ii.amount from Invoice i inner join InvoiceItem ii on i.id = ii.invoice_id " - + "where " + getHandleDeptCodeSql("i.orgUnitCoding") + " " + + "where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) +tousseTypeSql +" and "+disposableGoodsInvoiceAmountPredicate + " and (i.status ='收货签收' or i.status = '已发货') " + invoiceWheresql; @@ -5683,7 +5774,9 @@ //供应室领用(器械材料与一次性物品) String receiveSql = "select '一次性物品',(ged.amount*ged.price),ged.amount,oge.targetOrgUnitCode,oge.targetOrgUnitName " + "from GodownEntry oge inner join GodownEntryDiposableGoodsItem ged on oge.id = ged.godownEntryID where oge.type ='退库单' and oge.subType='调拨出库' " - + "and "+outEntryPredicate+" and " + getHandleDeptCodeSql("oge.orgUnitCode") + SqlUtils.getFiledWhereSql("oge.targetOrgUnitName", department, 40) + " and oge.time " + betweenSql; + + "and "+outEntryPredicate+" and " + + getHandleDeptCodeSql("oge.orgUnitCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + SqlUtils.getFiledWhereSql("oge.targetOrgUnitName", department, 40) + " and oge.time " + betweenSql; ResultSet rs4 = objectDao.executeSql(receiveSql); try { @@ -5709,7 +5802,9 @@ //供应室领用(器械材料与一次性物品) String receiveSql = "select '一次性物品',(ged.amount*ged.price),ged.amount,oge.orgUnitCode,oge.orgUnitName " + "from GodownEntry oge inner join GodownEntryDiposableGoodsItem ged on oge.id = ged.godownEntryID where oge.type ='退库单' and oge.subType='盘亏出库' " - + "and "+outEntryPredicate+" and " + getHandleDeptCodeSql("oge.orgUnitCode") + SqlUtils.getFiledWhereSql("oge.orgUnitName", department, 30) + " and oge.time " + betweenSql; + + "and "+outEntryPredicate+" and " + + getHandleDeptCodeSql("oge.orgUnitCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + SqlUtils.getFiledWhereSql("oge.orgUnitName", department, 30) + " and oge.time " + betweenSql; ResultSet rs4 = objectDao.executeSql(receiveSql); try { @@ -5735,7 +5830,9 @@ if(isSupplyRoomUser && (StringUtils.isBlank(tousseType) || TousseItem.TYPE_DIPOSABLE_GOODS.equals(tousseType))){ String sql = "select '一次性物品',(dgi.amount*dgi.fluctuationPrice),dgi.amount,pr.orgUnitCoding,pr.orgUnitName " + "from PackingRecord pr inner join DiposableGoodsItem dgi on pr.id = dgi.packingRecordId where " - +packingDisposableGoodsTypePredicate+" and " + getHandleDeptCodeSql("pr.orgUnitCoding") + SqlUtils.getFiledWhereSql("pr.orgUnitName", department, 35) + " and pr.packTime " + betweenSql; + +packingDisposableGoodsTypePredicate+" and " + + getHandleDeptCodeSql("pr.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + + SqlUtils.getFiledWhereSql("pr.orgUnitName", department, 35) + " and pr.packTime " + betweenSql; ResultSet rs = objectDao.executeSql(sql); try { @@ -5776,7 +5873,8 @@ + leftJonsTousseDefinition + " td.id=ii.tousseDefinitionId " + leftJoinSqlForInvoiceAmountSql - + " where " + getHandleDeptCodeSql("i.orgUnitCoding") + + " where " + + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + SqlUtils.getFiledWhereSql("i.settleAccountsDepart", department, 45) + " and (ii.amount is not null and ii.amount>0) "// 限制为只查包含器械包(含消毒物品、敷料包等)数量的 + " and " + SqlUtils.getStringFieldInLargeCollectionsPredicate("ii.tousseType", allTousseType)