Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java =================================================================== diff -u -r34455 -r34460 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 34455) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 34460) @@ -75,6 +75,7 @@ import com.forgon.disinfectsystem.entity.stocktakerecordmanager.StockTakeRecord; import com.forgon.disinfectsystem.entity.useRecord.UseDiposableGoodsItem; import com.forgon.disinfectsystem.entity.useRecord.UseRecord; +import com.forgon.disinfectsystem.jasperreports.javabeansource.DepartmentMonthDetail; import com.forgon.disinfectsystem.jasperreports.javabeansource.EighteenQuotaBean; import com.forgon.disinfectsystem.jasperreports.javabeansource.FirstColumnVo; import com.forgon.disinfectsystem.jasperreports.javabeansource.ForeignTousseAfterUseDeliveryVO; @@ -1259,6 +1260,7 @@ String invoicePlanDepartCoding = StrutsParamUtils.getPraramValue("invoicePlanDepartCoding", ""); String whetherToCharge = StrutsParamUtils.getPraramValue("whetherToCharge", ""); boolean showSendTime = StrutsParamUtils.getBoolPraramValue("showSendTime", false); + boolean printByDepart = StrutsParamUtils.getBoolPraramValue("printByDepart", false); if("singleDepart".equals(patternOfReport)){//单个科室的明细核算月报 if(StringUtils.isNotBlank(tousseType) && tousseType.contains(",")){ throw new RuntimeException("单个科室的明细核算月报不支持物品类型多选查询"); @@ -1268,7 +1270,7 @@ } return jasperReportManager.getDepartmentMonthDetailListOfSingleDepart(whetherToCharge,startTime, endTime, departSearch, departCoding, tousseType, goodsSearch, batch, searchType, disinfection, disposableGoodsType, goodsType, showCustonTousseAmount,invoicePlanDepartCoding); }else{ - return jasperReportManager.getDepartmentMonthDetailList(showSendTime, whetherToCharge,startTime,endTime, departSearch, departCoding,tousseType, goodsSearch, batch,searchType,disinfection,disposableGoodsType,goodsType,showCustonTousseAmount, invoicePlanDepartCoding, departGroupStr); + return jasperReportManager.getDepartmentMonthDetailList(showSendTime, whetherToCharge,startTime,endTime, departSearch, departCoding,tousseType, goodsSearch, batch,searchType,disinfection,disposableGoodsType,goodsType,showCustonTousseAmount, invoicePlanDepartCoding, departGroupStr, printByDepart); } } else if (reportName.equals("tousseReturnDisinfection")) {// 器械包返消 String startDate = StrutsParamUtils.getPraramValue("startDate", null); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r34454 -r34460 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 34454) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 34460) @@ -5511,11 +5511,12 @@ public List getDepartmentMonthDetailList( boolean showSendTime, String whetherToCharge, String startTime,String endTime, String departSearch,String departCoding,String typeSearch, String goodsNameSearch, String batch,String searchType,boolean disinfection,String disposableGoodsType,String goodsType, - Boolean showCustonTousseAmount, String invoicePlanDepartCoding, String departGroupStr) { + Boolean showCustonTousseAmount, String invoicePlanDepartCoding, String departGroupStr, boolean printByDepart) { boolean filterWhetherToCharge = false; if(StringUtils.isNotBlank(whetherToCharge) && !whetherToCharge.contains("全部")){ filterWhetherToCharge = true; } + boolean queryUnit = printByDepart; //数据库拼接符号 String concatSymbolInDb = DatabaseUtil.isOracle(dbConnection.getDatabase())?"||":"+"; SupplyRoomConfig config = supplyRoomConfigManager.getSystemParamsObj(); @@ -5631,13 +5632,39 @@ String noQuerySupplierName = "";//不需要查询器械包名称的sql，用于连接union all连接表时保持列数不变 String integralOftousseDefinitionSql = ""; String noQueryIntegralSql = ""; + String unitSqlOftousseDefinitionSql = ""; + if(enableToussePointsStatistics){ addTousseDefinitionAtWhere = " left join TousseDefinition td on td.id = ii.tousseDefinitionId "; integralOftousseDefinitionSql = ",td.integral "; noQueryIntegralSql = ",0 integral "; }else if(showSupplierNameInDetailedAccountingMonthlyReport){ addTousseDefinitionAtWhere = " join TousseDefinition td on td.id = ii.tousseDefinitionId "; + }else if(queryUnit){ + addTousseDefinitionAtWhere = " left join TousseDefinition td on td.id = ii.tousseDefinitionId "; + unitSqlOftousseDefinitionSql = ",td.unit "; } + String noQueryUnitSql = ""; + //通过DiposableGoodsItem查询一次性物品单位的相关sql + String unitDSqlOfDiposableGoodsItem = ""; + String unitDGroupBySqlOfDiposableGoodsItem = ""; + String unitDGISqlOfDiposableGoodsItem = ""; + String unitDGIGroupBySqlOfDiposableGoodsItem = ""; + //通过materialDefinition查询一次性物品单位的相关sql + String unitDSqlOfMd = ""; + String unitMSqlOfMd = ""; + //通过DisposableGoods查询一次性物品单位的相关sql + String unitDGSqlOfDisposableGoods = ""; + if(queryUnit){ + unitDSqlOfDiposableGoodsItem = ",(select unit from DisposableGoods where id=d.disposableGoodsID) unit "; + unitDGroupBySqlOfDiposableGoodsItem = ",d.disposableGoodsID"; + unitDGISqlOfDiposableGoodsItem = ",(select unit from DisposableGoods where id=dgi.disposableGoodsID) unit "; + unitDGIGroupBySqlOfDiposableGoodsItem = ",dgi.disposableGoodsID"; + unitDSqlOfMd = ",d.unit "; + unitMSqlOfMd = ",m.unit"; + unitDGSqlOfDisposableGoods = ",dg.unit"; + noQueryUnitSql = ",null unit"; + } if(showSupplierNameInDetailedAccountingMonthlyReport){ tousseDefinitionOfSupplierNameSql = " ,td.supplierName "; dgbsOfSupplierNameSql = " ,dgbs.supplierName "; @@ -5899,6 +5926,7 @@ //器械包 String tousseSql = " (select "+ tousseMaterialAmountSql +" i.depart,i.settleAccountsDepart "+ iSendTime + 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" + + unitSqlOftousseDefinitionSql + ",'' as batchNumber,null as expDate,ii.tousseDefinitionId as tousseDefinitionId from invoice i" + " join InvoiceItem ii on i.id = ii.invoice_id " + addTousseDefinitionAtWhere @@ -5908,13 +5936,14 @@ + departFilterSql + typeFilterSql + goodsNameFilterSql - + " and i.settleaccountsdepart is not null group by i.depart"+ groupByIsendTime +",i.settleAccountsDepart,ii.tousseType,ii.tousseName,ii.tousseDefinitionId " + tousseDefinitionOfSupplierNameSql + integralOftousseDefinitionSql + ")"; + + " and i.settleaccountsdepart is not null group by i.depart"+ groupByIsendTime + unitSqlOftousseDefinitionSql +",i.settleAccountsDepart,ii.tousseType,ii.tousseName,ii.tousseDefinitionId " + tousseDefinitionOfSupplierNameSql + integralOftousseDefinitionSql + ")"; //新的高值耗材(发货) String selectExpensiveGoodsSql = ""; //如果有启用高值耗材则查询 if(enableExpensiveGoods){ selectExpensiveGoodsSql = " union all (select "+ noMaterialAmountSql +" i.depart,i.settleAccountsDepart"+ iSendTime + 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" + + noQueryUnitSql + ",'' 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" @@ -5929,7 +5958,9 @@ + " and i.settleaccountsdepart is not null group by i.depart"+ groupByIsendTime +",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 " + iSendTime + 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 " + selectExpensiveGoodsSql += " union all (select "+ noMaterialAmountSql +" i.depart,i.settleAccountsDepart " + iSendTime + 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" + + noQueryUnitSql + + ",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 " @@ -5961,6 +5992,7 @@ if(StringUtils.isBlank(typeSearch) || typeSearch.contains("一次性物品")){ diposableGoodsSql += " union all (select "+ noMaterialAmountSql +" i.depart,i.settleaccountsdepart "+ iSendTime + 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" + + unitDSqlOfDiposableGoodsItem + ",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")+" " @@ -5970,11 +6002,12 @@ + departFilterSql + iDepartCodingSql + nameFilterSql - + " group by i.depart"+ groupByIsendTime +",i.settleaccountsdepart,d.name,d.batch,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; + + " group by i.depart"+ groupByIsendTime +unitDGroupBySqlOfDiposableGoodsItem +",i.settleaccountsdepart,d.name,d.batch,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; }else{ if(typeSearch.contains("高值耗材")){ diposableGoodsSql += " union all (select "+ noMaterialAmountSql +" i.depart,i.settleaccountsdepart " + iSendTime + 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" + + unitDSqlOfDiposableGoodsItem + ",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")+" " @@ -5986,11 +6019,12 @@ + nameFilterSql + " and d.expensiveDisposablegoodsId is not null " + expensiveDisposablegoodsIdSql - + " group by i.depart"+ groupByIsendTime +",i.settleaccountsdepart,d.name,d.batch,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; + + " group by i.depart"+ groupByIsendTime + unitDGroupBySqlOfDiposableGoodsItem +",i.settleaccountsdepart,d.name,d.batch,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; } if(typeSearch.contains(TYPE_AUTO_DEDUCTION)){ diposableGoodsSql += " union all (select "+ noMaterialAmountSql +" i.depart,i.settleaccountsdepart "+ iSendTime+ 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" + + unitDSqlOfDiposableGoodsItem + ",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")+" " @@ -6001,14 +6035,15 @@ + departFilterSql + iDepartCodingSql + nameFilterSql - + " group by i.depart"+ groupByIsendTime +",i.settleaccountsdepart,d.name,d.batch,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; + + " group by i.depart"+ groupByIsendTime + unitDGroupBySqlOfDiposableGoodsItem +",i.settleaccountsdepart,d.name,d.batch,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; } } String packingDisposableGoodsSql = " "; { if(StringUtils.isBlank(typeSearch) || "一次性物品".equals(typeSearch)){ packingDisposableGoodsSql = " union all (select "+ noMaterialAmountSql +" pr.depart,pr.orgUnitName "+ noSendTime + 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" + + unitDGISqlOfDiposableGoodsItem + ",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")+" " @@ -6019,7 +6054,7 @@ + excludeDiposableGoodsItemSql + packingDisposableGoodsNameSql // + expensiveDisposablegoodsSql - + " group by pr.depart,pr.orgUnitName,dgi.name,dgi.batch,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; + + " group by pr.depart"+ unitDGIGroupBySqlOfDiposableGoodsItem +",pr.orgUnitName,dgi.name,dgi.batch,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; } } @@ -6032,7 +6067,9 @@ if ((StringUtils.isBlank(typeSearch) || typeSearch.contains("器械材料") || typeSearch.contains("高值耗材"))) { // 材料发货 materialInvoiceSql = " union all (select "+ noMaterialAmountSql +"i.depart,i.settleAccountsDepart "+ iSendTime +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 " + +"sum(ii.settlementPrice) as settlementDiscountPrice,'材料' as type" + + unitDSqlOfMd + + ",'' 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 " @@ -6043,12 +6080,14 @@ + iDepartCodingSql + materialNameFilterSql + materialTypeFilterSql - + " group by i.depart"+ groupByIsendTime +",i.settleaccountsdepart,case when td.name is null then ii.materialName else ii.materialName "+ concatSymbolInDb +" '(' "+ concatSymbolInDb +" td.name "+ concatSymbolInDb +"')' end,td.id) "; + + " group by i.depart"+ groupByIsendTime + unitDSqlOfMd +",i.settleaccountsdepart,case when td.name is null then ii.materialName else ii.materialName "+ concatSymbolInDb +" '(' "+ concatSymbolInDb +" td.name "+ concatSymbolInDb +"')' end,td.id) "; } if(StringUtils.isBlank(typeSearch) || "全部".equals(typeSearch) || typeSearch.contains("器械材料")){ // 材料退货 materialReturnSql = " union all (select "+ noMaterialAmountSql +"rr.depart,rr.settleAccountsDepart " + noSendTime + 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 " + +"-sum(ii.settlementPrice) as settlementDiscountPrice,'材料' as type" + + unitDSqlOfMd + + ",'' 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")+" " @@ -6057,14 +6096,16 @@ + rrDepartCodingSql + materialNameFilterSql + materialTypeFilterSql - + " group by rr.depart,rr.settleAccountsDepart,ii.materialName) "; + + " group by rr.depart"+ unitDSqlOfMd +",rr.settleAccountsDepart,ii.materialName) "; } lostMaterial = " union all (select "+ noMaterialAmountSql +"i.depart, i.settleAccountsDepart " + noSendTime +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 " + +"sum(i.additionalAmount * i.materialCost) as settlementDiscountPrice,'材料' as type" + + unitMSqlOfMd + + ",'' 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 - + "group by i.depart,i.settleAccountsDepart,i.materialName ) "; + + "group by i.depart"+ unitMSqlOfMd +",i.settleAccountsDepart,i.materialName ) "; String banQuery = " and 1 = 0 "; String damagedTousseAtCost = config.getDamagedTousseAtCost(); /*if(StringUtils.isBlank(damagedTousseAtCost) || Constants.STR_YES.equals(damagedTousseAtCost)){ @@ -6074,26 +6115,37 @@ banQuery = ""; } damageMaterial = " union all (select "+ noMaterialAmountSql +"i.depart,i.settleAccountsDepart " + noSendTime +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 " + +"sum(i.materialCost*i.additionalAmount) as settlementDiscountPrice,'材料' as type" + + unitMSqlOfMd + + ",'' 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 + iDepartCodeSql - + " group by i.depart,i.settleAccountsDepart,i.materialName ) "; + + " group by i.depart" + + unitMSqlOfMd + + ",i.settleAccountsDepart,i.materialName ) "; } //退货(一次性物品、器械包) String returnGoodsSql = " union all (select "+ returnGoodsItemMaterialAmountSql +"rr.depart,rr.settleAccountsDepart as settleaccountsdepart " + noSendTime +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 from " + ReturnGoodsRecord.class.getSimpleName() + " rr inner join " + +returnGoodsTousseType+" as type"; + if(queryUnit){ + returnGoodsSql += ",case when ri.disposableGoodsID is not null THEN (select unit from DisposableGoods where id=ri.disposableGoodsID) else td.unit end unit"; + } + returnGoodsSql += ",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" + " where " + getHandleDeptCodeSql("rr.handleDepartCode")+" " + returnGoodsFilterSql + rrSettleAccountsDepartCodingSql +" and "+returnGoodsItemPredicate - + " group by rr.depart,rr.settleAccountsDepart,ri.toussename,"+returnGoodsTousseType+",ri.batchNumber"+ groupByMaterialAmountAtThatTime +",dgbs.expDate,"+returnGoodsTousseDefinitionId+ dgbsOfSupplierNameSql +") "; + + " group by rr.depart"; + if(queryUnit){ + returnGoodsSql += ",ri.disposableGoodsID,td.unit "; + } + returnGoodsSql += ",rr.settleAccountsDepart,ri.toussename,"+returnGoodsTousseType+",ri.batchNumber"+ groupByMaterialAmountAtThatTime +",dgbs.expDate,"+returnGoodsTousseDefinitionId+ dgbsOfSupplierNameSql +") "; String supplyRoomTousseTypeSql = ""; /*if(!(StringUtils.isBlank(typeSearch) || typeSearch.contains("一次性物品"))){ @@ -6102,33 +6154,41 @@ } }*/ String supplyRoomDiposableGoodsSql = " union all (select "+ sumRriAmountSql +" rr.depart,rr.depart as settleaccountsdepart "+ noSendTime + 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 " + +"sum(rri.price*rri.amount) as settlementprice,sum(rri.price*rri.amount) as settlementDiscountPrice,rri.type as type" + + unitDSqlOfMd + + ",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 +supplyRoomTousseTypeSql +" and "+receiveRecordItemPredicate + rrDepartCodingSql + supplyRoomDiposableGoodsFilterSql - + " group by rr.depart,rri.goodsName,rri.amount,rri.type,dgbs.batchNumber,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; + + " group by rr.depart" + + unitDSqlOfMd + + ",rri.goodsName,rri.amount,rri.type,dgbs.batchNumber,dgbs.expDate "+ unitDSqlOfMd + dgbsOfSupplierNameSql +") "; // 调拨出库 String appropriateOutDiposableGoodsSql = " union all (select "+ noMaterialAmountSql +" oge.targetOrgUnitName depart ,oge.targetOrgUnitName as settleaccountsdepart" + noSendTime +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 " + +"sum(ged.price*ged.amount) as settlementprice,sum(ged.price*ged.amount) as settlementDiscountPrice,'一次性物品' as type" + +unitDGSqlOfDisposableGoods + + ",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 + ogeTargetOrgUnitCodeSql +" and "+outEntryPredicate - + " group by oge.targetOrgUnitName, ged.goodsName,dgbs.batchNumber,dgbs.expDate "+ dgbsOfSupplierNameSql +") "; + + " group by oge.targetOrgUnitName, ged.goodsName,dgbs.batchNumber,dgbs.expDate "+ unitDGSqlOfDisposableGoods + dgbsOfSupplierNameSql +") "; // 盘亏出库 String stocktakeOutDiposableGoodsSql = " union all (select "+ noMaterialAmountSql +" oge.orgUnitName depart, oge.orgUnitName" +dgbsOfSupplierNameSql+" as settleaccountsdepart"+ noSendTime + 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 " + +"sum(ged.price*ged.amount) as settlementprice,sum(ged.price*ged.amount) as settlementDiscountPrice,'一次性物品' as type" + +unitDGSqlOfDisposableGoods + + ",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 + ogeOrgUnitCodeSql +" and "+outEntryPredicate - + " group by oge.orgUnitName, ged.goodsName,dgbs.batchNumber,dgbs.expDate"+dgbsOfSupplierNameSql+") "; + + " group by oge.orgUnitName, ged.goodsName,dgbs.batchNumber,dgbs.expDate" + unitDGSqlOfDisposableGoods + dgbsOfSupplierNameSql+") "; String sql = tousseSql + selectExpensiveGoodsSql + diposableGoodsSql + supplyRoomDiposableGoodsSql ; @@ -6158,13 +6218,13 @@ } } if(StringUtils.isNotBlank(typeSearch) && typeSearch.contains(TYPE_CHARGE)){ - String chargeSql = String.format(" union all select "+ noMaterialAmountSql +"ci.orgUnitName depart, ci.orgUnitName settleAccountsDepart" + noSendTime+ noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type ,'' batchNumber,NULL as expDate ,null as tousseDefinitionId " + String chargeSql = String.format(" union all select "+ noMaterialAmountSql +"ci.orgUnitName depart, ci.orgUnitName settleAccountsDepart" + noSendTime+ noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type "+ noQueryUnitSql +",'' 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(StringUtils.isBlank(typeSearch)){ - sql += String.format(" union all select "+ noMaterialAmountSql +"ci.orgUnitName depart,ci.orgUnitName settleAccountsDepart" + noSendTime + noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type ,'' batchNumber,NULL as expDate ,null as tousseDefinitionId " + sql += String.format(" union all select "+ noMaterialAmountSql +"ci.orgUnitName depart,ci.orgUnitName settleAccountsDepart" + noSendTime + noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type "+ noQueryUnitSql +",'' 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); @@ -6200,7 +6260,7 @@ settleaccountsdepart = ""; } String depart = rs.getString("depart"); - if(StringUtils.isBlank(depart)){ + if(StringUtils.isBlank(depart) || queryUnit){ depart = ""; } String departKey = settleaccountsdepart + ";_;" + depart; @@ -6256,11 +6316,23 @@ if(showSendTime){ mdi.setSendTime(rs.getString("sendTime")); } + if(queryUnit && DisposableGoods.TYPE_NAME.equals(type)){ + int beginIndex = goodsName.indexOf("["); + int endIndex = goodsName.lastIndexOf("]"); + if(beginIndex != -1 && endIndex != -1){ + String specification = goodsName.substring(beginIndex + 1, endIndex); + goodsName = goodsName.substring(0, beginIndex); + mdi.setSpecification(specification); + } + } mdi.setGoodsName(goodsName); mdi.setType(type); mdi.setSettlementPrice(settlementPrice); mdi.setSettlementDiscountPrice(settlementDiscountPrice); mdi.setBatchNumber(rs.getString("batchNumber")); + if(queryUnit){ + mdi.setUnit(rs.getString("unit")); + } mdi.setExpDate(ForgonDateUtils.safelyFormatDate(expDate, Constants.SIMPLEDATEFORMAT_YYYYMMDD, "")); if ("一次性物品".equals(type)) { mdi.setManufacturer(manufacturerMap.get(goodsName + "_" + mdi.getBatchNumber())); @@ -24025,7 +24097,7 @@ String tousseDefinitionIdCondition = DatabaseUtil.isPoIdValid(tousseDefinitionId) ? "and td.ancestorId=" + tousseDefinitionId : ""; //时间过滤 String dateCondition = "and " + dateQueryAdapter.dateAreaSql("trdr.operateDate", startDate, endDate, true); - String sql = "select trdr.orgUnitName,td.tousseType,trdi.tousseName,amount,case when bd.barcode is not null then bd.barcode else trdi.scanBarcode end barcode " + String sql = "select trdr.operateDate, trdr.orgUnitName,td.tousseType,trdi.tousseName,amount,case when bd.barcode is not null then bd.barcode else trdi.scanBarcode end barcode " + ",idiBd.barcode idBarcode,ti.sterilizerName,ti.sterileFrequency,ti.sterileEndTime,ti.validUntil from TousseReturnDisinfectRecord trdr join TousseReturnDisinfectItem trdi on trdr.id=trdi.tousseReturnDisinfectRecordId " + " join TousseDefinition td on td.id=trdi.tousseDefinitionId " + " left join TousseInstance ti on "+ (DatabaseUtil.isSqlServer(dbConnection.getDatabase()) ? "convert(varchar(20),ti.id)" : "to_char(ti.id)") +"=trdi.tousseInstanceIds " @@ -24072,6 +24144,7 @@ detailVo.setTousseBarcode(barcode); } detailVo.setUntilDate(ForgonDateUtils.safelyFormatDate(rs.getTimestamp("validUntil"),ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMM,"")); + detailVo.setOperateDate(ForgonDateUtils.safelyFormatDate(rs.getTimestamp("operateDate"),ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMM,"")); list.add(detailVo); departMap.put(orgUnitName, list); } @@ -24736,7 +24809,7 @@ ReportDataWriteRecord data = (ReportDataWriteRecord)objectDao.getBySql(ReportDataWriteRecord.class.getSimpleName(), queryCondition); if(data == null){ List resultList = - getDepartmentMonthDetailList(false, null, startTime, endTime, null, null, null, null, null, null, false, null, null, showCustonTousseAmount, null, null); + getDepartmentMonthDetailList(false, null, startTime, endTime, null, null, null, null, null, null, false, null, null, showCustonTousseAmount, null, null,false); if(CollectionUtils.isNotEmpty(resultList)){ JSONObject resultParamData = new JSONObject(); resultParamData.put("startTime", startTime); @@ -24757,7 +24830,7 @@ endTime = ForgonDateUtils.safelyFormatDate(cal.getTime(), ForgonDateUtils.SIMPLEDATEFORMAT_YYYYMMDDHHMM, null); List resultList = - getDepartmentMonthDetailList(false, null, startTime, endTime, null, null, null, null, null, null, false, null, null, showCustonTousseAmount,null, null); + getDepartmentMonthDetailList(false, null, startTime, endTime, null, null, null, null, null, null, false, null, null, showCustonTousseAmount,null, null, false); if(CollectionUtils.isNotEmpty(resultList)){ JSONObject resultParamData = new JSONObject(); resultParamData.put("startTime", startTime); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/DepartmentMonthDetailItem.java =================================================================== diff -u -r33683 -r34460 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/DepartmentMonthDetailItem.java (.../DepartmentMonthDetailItem.java) (revision 33683) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/DepartmentMonthDetailItem.java (.../DepartmentMonthDetailItem.java) (revision 34460) @@ -58,6 +58,10 @@ * 发货时间 */ private String sendTime = ""; + /** + * 单位 + */ + private String unit; public String getGoodsName() { return goodsName; } @@ -203,5 +207,13 @@ } this.sendTime = sendTime; } + + public String getUnit() { + return unit; + } + + public void setUnit(String unit) { + this.unit = unit; + } } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java =================================================================== diff -u -r34397 -r34460 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java (.../JasperReportManager.java) (revision 34397) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java (.../JasperReportManager.java) (revision 34460) @@ -290,11 +290,13 @@ * @param showCustonTousseAmount * @param invoicePlanDepart 申请科室 * @param departGroupStr 科室分组 + * @param printByDepart 按科室打印 * @return */ public List getDepartmentMonthDetailList( boolean showSendTime,String whetherToCharge, String startTime,String endTime, String depart,String departCoding,String type, String goodsName, - String batch,String searchType,boolean disinfection,String disposableGoodsType,String goodsType,Boolean showCustonTousseAmount, String invoicePlanDepart, String departGroupStr); + String batch,String searchType,boolean disinfection,String disposableGoodsType,String goodsType,Boolean showCustonTousseAmount, String invoicePlanDepart, String departGroupStr + ,boolean printByDepart); /** * 查询单个科室的明细核算月报数据 * @param whetherToCharge 一次性物品是否收费