Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/DepartmentMonthDetailHelper.java =================================================================== diff -u -r38737 -r40698 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/DepartmentMonthDetailHelper.java (.../DepartmentMonthDetailHelper.java) (revision 38737) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/DepartmentMonthDetailHelper.java (.../DepartmentMonthDetailHelper.java) (revision 40698) @@ -47,6 +47,7 @@ import com.forgon.disinfectsystem.entity.returngoodsrecord.ReturnGoodsRecord; import com.forgon.disinfectsystem.jasperreports.javabeansource.DepartmentMonthDetail; import com.forgon.disinfectsystem.jasperreports.javabeansource.DepartmentMonthDetailItem; +import com.forgon.systemsetting.model.HttpOption; import com.forgon.tools.MathTools; import com.forgon.tools.StrutsParamUtils; import com.forgon.tools.db.DatabaseUtil; @@ -301,21 +302,45 @@ } addTousseDefinitionAtWhere = " left join TousseDefinition td on td.id = ii.tousseDefinitionId "; boolean queryTousseGroupName = false;//是否查询器械包分组 + boolean queryTechnicalDifficulty = false;//是否查询技术难度 + boolean queryWeightScore = false;//是否权重分 String nullTousseGroupNameSQL = ""; String tdcTousseGroupNameSQL = ""; String tdLeftJoinTdcSQL = ""; + String tdcJoinTechnicalDifficulty = ""; + String httpOptionTechnicalDifficultySQL = ""; + String nullTechnicalDifficultySQL = ""; + String tdcWeightScoreSQL = ""; + String nullWeightScoreSQL = ""; if(StringUtils.isNotBlank(extendedColumnConfigOfDetailedAccountingMonthlyReport)){ JSONArray arr = JSONArray.fromObject(extendedColumnConfigOfDetailedAccountingMonthlyReport); for (int i = 0; i < arr.size(); i++) { String extendedColumnName = arr.get(i).toString().trim(); if(StringUtils.isBlank(extendedColumnName)){ continue; } - if("tousseGroupName".equals(extendedColumnName)){ + if("tousseGroupName".equalsIgnoreCase(extendedColumnName)){ queryTousseGroupName = true; tdcTousseGroupNameSQL = ",tdc.tousseGroupName "; nullTousseGroupNameSQL = ",null tousseGroupName "; - tdLeftJoinTdcSQL = " left join TousseDefinition tdc on tdc.id=td.ancestorID "; + if(StringUtils.isBlank(tdLeftJoinTdcSQL)){ + tdLeftJoinTdcSQL = " left join TousseDefinition tdc on tdc.id=td.ancestorID "; + } + }else if("technicalDifficulty".equalsIgnoreCase(extendedColumnName)){ + queryTechnicalDifficulty = true; + httpOptionTechnicalDifficultySQL = ",httpo.optionText "; + nullTechnicalDifficultySQL = ",null optionText "; + if(StringUtils.isBlank(tdLeftJoinTdcSQL)){ + tdLeftJoinTdcSQL = " left join TousseDefinition tdc on tdc.id=td.ancestorID "; + } + tdcJoinTechnicalDifficulty = " left join " + HttpOption.class.getSimpleName() + " httpo on httpo.id=tdc.technicalDifficultyId and httpo.optionListId='"+ HttpOption.SYSTEMSETTING_TECHNICALDIFFICULTY +"' "; + }else if("weightScore".equalsIgnoreCase(extendedColumnName)){ + queryWeightScore = true; + tdcWeightScoreSQL = ",tdc.weightScore "; + nullWeightScoreSQL = ",null weightScore "; + if(StringUtils.isBlank(tdLeftJoinTdcSQL)){ + tdLeftJoinTdcSQL = " left join TousseDefinition tdc on tdc.id=td.ancestorID "; + } } } } @@ -672,8 +697,19 @@ if(queryTousseGroupName){ tousseGroupNameOfT = ",tousseGroupName "; } + String queryTechnicalDifficultyOfT = ""; + if(queryTechnicalDifficulty){ + queryTechnicalDifficultyOfT = ",optionText "; + } + String queryWeightScoreOfT = ""; + if(queryWeightScore){ + queryWeightScoreOfT = ",weightScore "; + } + String groupBySql = "group by i.depart" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + groupByIsendTimeOfShowTime + groupByIsendTime + unitSqlOftousseDefinitionSql +",i.settleAccountsDepart,td.tousseType,td.name,td.id " + tousseDefinitionOfSupplierNameSql + integralOftousseDefinitionSql @@ -682,6 +718,8 @@ + showTimeOfT + sendTimeOfT + tousseGroupNameOfT + + queryTechnicalDifficultyOfT + + queryWeightScoreOfT + getRemovePrefixSql(unitSqlOftousseDefinitionSql) +",settleAccountsDepart,type,name,tousseDefinitionId " + getRemovePrefixSql(tousseDefinitionOfSupplierNameSql) @@ -712,10 +750,13 @@ + unitSqlOftousseDefinitionSql + ",'' as batchNumber,null as expDate,td.id as tousseDefinitionId" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + fluctuationPriceOfTi + ",ti.id comboTousseInstanceId from Invoice i join InvoiceItem ii on ii.invoice_id=i.id " + " join TousseInstance ti on ti.id=ii.comboTousseInstanceId " + tousseInstanceJoinTousseDefinitionSql + + tdcJoinTechnicalDifficulty + " where td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"' and " + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + monthFilterSql @@ -738,6 +779,8 @@ + getRemovePrefixSql(unitSqlOftousseDefinitionSql) + ",'' as batchNumber,null as expDate,tousseDefinitionId" + tousseGroupNameOfT + + queryTechnicalDifficultyOfT + + queryWeightScoreOfT + " from ( select " + tousseInstanceMaterialAmountSql +" i.depart,i.settleAccountsDepart " @@ -750,10 +793,13 @@ + unitSqlOftousseDefinitionSql + ",'' as batchNumber,null as expDate,td.id as tousseDefinitionId" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + fluctuationPriceOfTi + ",null comboTousseInstanceId from invoice i " + "join TousseInstance ti on ti.invoice_id=i.id " + tousseInstanceJoinTousseDefinitionSql + + tdcJoinTechnicalDifficulty + " where 1=1 and " + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + monthFilterSql @@ -782,10 +828,13 @@ + unitSqlOftousseDefinitionSql + ",'' as batchNumber,null as expDate,td.id as tousseDefinitionId" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + fluctuationPriceOfTi + ",null comboTousseInstanceId from invoice i " + "join TousseInstance ti on ti.invoice2_id=i.id " + tousseInstanceJoinTousseDefinitionSql + + tdcJoinTechnicalDifficulty + " where 1=1 and " + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + monthFilterSql @@ -815,9 +864,12 @@ + unitSqlOftousseDefinitionSql + ",'' as batchNumber,null as expDate,ii.tousseDefinitionId as tousseDefinitionId " + tdcTousseGroupNameSQL - + "from invoice i" + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + + " from invoice i" + " join InvoiceItem ii on i.id = ii.invoice_id " + addTousseDefinitionAtWhere + + tdcJoinTechnicalDifficulty + " where ii.diposable = '否' and ii.expensiveGoodsInstanceId is null and "+ getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + monthFilterSql + iDepartCodingSql @@ -826,7 +878,7 @@ + goodsNameFilterSql + invoiceItemdisposableGoodsSQl + instrumentSetTypesSql - + " and i.settleaccountsdepart is not null group by ii.comboTousseInstanceId,td.tousseType,i.depart"+ tdcTousseGroupNameSQL + groupByIsendTimeOfShowTime + groupByIsendTime + unitSqlOftousseDefinitionSql +",i.settleAccountsDepart,ii.tousseType,ii.tousseName,ii.tousseDefinitionId " + tousseDefinitionOfSupplierNameSql + integralOftousseDefinitionSql + ")"; + + " and i.settleaccountsdepart is not null group by ii.comboTousseInstanceId,td.tousseType,i.depart"+ tdcTousseGroupNameSQL + httpOptionTechnicalDifficultySQL+ tdcWeightScoreSQL + groupByIsendTimeOfShowTime + groupByIsendTime + unitSqlOftousseDefinitionSql +",i.settleAccountsDepart,ii.tousseType,ii.tousseName,ii.tousseDefinitionId " + tousseDefinitionOfSupplierNameSql + integralOftousseDefinitionSql + ")"; } //新的高值耗材(发货) String selectExpensiveGoodsSql = ""; @@ -836,7 +888,7 @@ +"sum(ii.settlementPrice) as settlementprice,sum(ii.settlementDiscountPrice) as settlementDiscountPrice,ii.tousseType as type" + noQueryUnitSql + ",'' as batchNumber,ei.expDate as expDate,0 as tousseDefinitionId" - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL + nullTechnicalDifficultySQL + nullWeightScoreSQL + " 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" @@ -856,7 +908,7 @@ selectExpensiveGoodsSql += " union all (select "+ noMaterialAmountSql +" i.depart,i.settleAccountsDepart " + iSendTimeSqlOfShowTime + iSendTime + noQuerySupplierName + noQueryIntegralSql +",case when egm.model is null then eg.name else " + dbConnection.concatSql("eg.name", "'['", "egm.model", "']'") + " 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 " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL + nullTechnicalDifficultySQL+ nullWeightScoreSQL + " 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 " @@ -891,7 +943,7 @@ +"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 " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL + nullTechnicalDifficultySQL+ nullWeightScoreSQL + " 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 " @@ -910,7 +962,7 @@ +"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" - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL + nullTechnicalDifficultySQL+ nullWeightScoreSQL + " from invoice i join DiposableGoodsItem d on d.invoice_id=i.id join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on d.disposableGoodsBatchStockID=dgbs.id " + invoiceJoinInvoicePlanSql + " where " @@ -931,7 +983,7 @@ +"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" - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL + nullTechnicalDifficultySQL+ nullWeightScoreSQL + " 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 " @@ -954,7 +1006,7 @@ +"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" - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL + nullTechnicalDifficultySQL+ nullWeightScoreSQL + " from PackingRecord pr inner join DiposableGoodsItem dgi on pr.id = dgi.packingRecordId " + " inner join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on dgi.disposableGoodsBatchStockID=dgbs.id" + " where " @@ -984,9 +1036,12 @@ + unitDSqlOfMd + ",'' as batchNumber,null as expDate,td.id as tousseDefinitionId " + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + " from MaterialInvoiceItem ii" + " left join TousseDefinition td on td.id= ii.tousseDefinitionId " + tdLeftJoinTdcSQL + + tdcJoinTechnicalDifficulty + " inner join MaterialInvoice i on i.id = ii.materialInvoice_id " + " inner join materialDefinition d on ii.materialDefinitionId = d.id where " + getHandleDeptCodeSql("i.orgUnitCoding", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) @@ -996,15 +1051,15 @@ + materialNameFilterSql + materialTypeFilterSql + onlyQueryTousse - + " group by i.depart" + tdcTousseGroupNameSQL + groupByIsendTimeOfShowTime + groupByIsendTime + unitDSqlOfMd +",i.settleaccountsdepart,case when td.name is null then ii.materialName else " + dbConnection.concatSql("ii.materialName", "'('", "td.name", "')'") + " end,td.id) "; + + " group by i.depart" + tdcTousseGroupNameSQL + httpOptionTechnicalDifficultySQL + tdcWeightScoreSQL + groupByIsendTimeOfShowTime + groupByIsendTime + unitDSqlOfMd +",i.settleaccountsdepart,case when td.name is null then ii.materialName else " + dbConnection.concatSql("ii.materialName", "'('", "td.name", "')'") + " end,td.id) "; } if(StringUtils.isBlank(typeSearch) || "全部".equals(typeSearch) || typeSearch.contains("器械材料")){ // 材料退货 materialReturnSql = " union all (select "+ noMaterialAmountSql +"rr.depart,rr.settleAccountsDepart " + rrReturnTimeSqlOfShowTime + noSendTime + noQuerySupplierName + noQueryIntegralSql +",ii.materialName,-sum(ii.amount),-sum(ii.settlementPrice)," +"-sum(ii.settlementPrice) as settlementDiscountPrice,'材料' as type" + unitDSqlOfMd + ",'' as batchNumber,null as expDate,0 as tousseDefinitionId " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL + " from ReturnMaterialRecord rr,materialDefinition d," + "ReturnMaterialItem ii where rr.id = ii.returnMaterialRecord_ID " + " and ii.materialDefinitionId = d.id and " @@ -1021,7 +1076,7 @@ +"sum(i.additionalAmount * i.materialCost) as settlementDiscountPrice,'材料' as type" + unitMSqlOfMd + ",'' as batchNumber,null as expDate,0 as tousseDefinitionId " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL + " 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", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + lostMaterialFilterSql @@ -1040,7 +1095,7 @@ +"sum(i.materialCost*i.additionalAmount) as settlementDiscountPrice,'材料' as type" + unitMSqlOfMd + ",'' as batchNumber,null as expDate,0 as tousseDefinitionId " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL + "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 " @@ -1071,6 +1126,8 @@ } returnGoodsSql += ",null as batchNumber,null expDate,td.id as tousseDefinitionId" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + " from " + ReturnGoodsRecord.class.getSimpleName() + " rr inner join " + ReturnGoodsItem.class.getSimpleName()+ " ri on rr.id = ri.returngoodsrecord_id " @@ -1080,6 +1137,7 @@ + " join " + TousseDefinition.class.getSimpleName() + " td on td.id=ti.tousseDefinition_id" + tdLeftJoinTdcSQL + + tdcJoinTechnicalDifficulty + " where " + getHandleDeptCodeSql("rr.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + returnGoodsFilterSql @@ -1091,6 +1149,8 @@ + (haveCombo?" and not exists (select 1 from "+ TousseInstance.class.getSimpleName() +" ti where ri.id = ti.returnGoodsItem_ID and ti.comboTousseInstanceId is not null) ":"") + " group by rr.depart" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + fluctuationPriceOfTi; if(queryUnit){ returnGoodsSql += ",ri.disposableGoodsID,td.unit "; @@ -1137,6 +1197,8 @@ returnComboSql += ",null as batchNumber,null expDate,td.id as tousseDefinitionId" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + " from " + ReturnGoodsRecord.class.getSimpleName() + " rr inner join " + ReturnGoodsItem.class.getSimpleName()+ " ri on rr.id = ri.returngoodsrecord_id " @@ -1147,6 +1209,7 @@ + " left join " + TousseDefinition.class.getSimpleName() + " td on td.id=ti.tousseDefinition_id" + tdLeftJoinTdcSQL + + tdcJoinTechnicalDifficulty + " where " + getHandleDeptCodeSql("rr.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + returnGoodsFilterSql @@ -1159,6 +1222,8 @@ //+"' and exists (select 1 from "+ InvoiceItem.class.getSimpleName() +" ii join "+ TousseInstance.class.getSimpleName() +" ti on ti.id=ii.comboTousseInstanceId and ti.returnGoodsItem_ID=ri.id ) " + "' group by rr.depart" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + combPriceForGroup; if(queryUnit){ returnComboSql += ",ri.disposableGoodsID,td.unit "; @@ -1175,7 +1240,7 @@ returnGoodsSql += ",(select unit from DisposableGoods where id=ri.disposableGoodsID) unit"; } returnGoodsSql += ",ri.batchNumber as batchNumber,dgbs.expDate,null as tousseDefinitionId" - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL + " 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" @@ -1203,10 +1268,13 @@ } returnGoodsSql += ",ri.batchNumber as batchNumber,dgbs.expDate,"+returnGoodsTousseDefinitionId+" as tousseDefinitionId" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + " 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" + tdLeftJoinTdcSQL + + tdcJoinTechnicalDifficulty + " left join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on ri.disposableGoodsBatchStockID=dgbs.id" + " where " + getHandleDeptCodeSql("rr.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) @@ -1217,7 +1285,9 @@ //按整包发货的聚合包单独查询 + (haveCombo?" and NOT EXISTS (select 1 from "+ TousseInstance.class.getSimpleName() +" ti where ri.id = ti.returnGoodsItem_ID and ti.comboTousseInstanceId is not null) ":"") + " group by rr.depart" - + tdcTousseGroupNameSQL; + + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL; if(queryUnit){ returnGoodsSql += ",ri.disposableGoodsID,td.unit "; } @@ -1241,12 +1311,16 @@ } returnGoodsSql += ",null as batchNumber,null expDate,td.id as tousseDefinitionId" + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL + " from " + ReturnGoodsRecord.class.getSimpleName() + " rr inner join " + ReturnGoodsItem.class.getSimpleName()+ " ri on rr.id = ri.returngoodsrecord_id " + " join TousseInstance ti on ti.returnGoodsItem_ID =ri.id " + " join TousseDefinition td on td.id=ti.tousseDefinition_id " +" join (select sum(materialAmount) materialAmount,tit.comboTousseInstanceId from TousseInstance tit group by tit.comboTousseInstanceId ) b on b.comboTousseInstanceId=ti.id " - + " left join TousseDefinition tdc on tdc.id=td.ancestorID where " + + " left join TousseDefinition tdc on tdc.id=td.ancestorID " + + tdcJoinTechnicalDifficulty + + " where " + getHandleDeptCodeSql("rr.handleDepartCode", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) + returnGoodsFilterSql + rrSettleAccountsDepartCodingSql @@ -1255,7 +1329,9 @@ //聚合包单独查询 + " and td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_COMBO +"' " + " group by rr.depart " - + tdcTousseGroupNameSQL; + + tdcTousseGroupNameSQL + + httpOptionTechnicalDifficultySQL + + tdcWeightScoreSQL; if(queryUnit){ returnGoodsSql += ",td.unit "; } @@ -1272,7 +1348,7 @@ +"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 " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL +" 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", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) @@ -1291,7 +1367,7 @@ +"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 " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL +" 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", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) @@ -1305,7 +1381,7 @@ +"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 " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL +" 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", isSupplyRoomUserOrIsExpensiveGoodsSupplyRoomUser, orgUnitCoding) @@ -1344,15 +1420,15 @@ } if(StringUtils.isBlank(instrumentSetTypes) && !queryUrgent && StringUtils.isNotBlank(typeSearch) && typeSearch.contains(TYPE_CHARGE)){ String chargeSql = String.format(" union all select "+ noMaterialAmountSql +"ci.orgUnitName depart, ci.orgUnitName settleAccountsDepart" + crChargeTimeSqlOfShowTime + noSendTime + noQuerySupplierName + noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type "+ noQueryUnitSql +",'' batchNumber,NULL as expDate ,null as tousseDefinitionId " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL + " 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 " + groupByCrChargeTimeOfShowTime , chargeDepartSql,chargeDateSql,chargeItemSql, ciOrgUnitCodeSql); sql += chargeSql; }else if(StringUtils.isBlank(instrumentSetTypes) && !queryUrgent && StringUtils.isBlank(typeSearch)){ sql += String.format(" union all select "+ noMaterialAmountSql +"ci.orgUnitName depart,ci.orgUnitName settleAccountsDepart" + crChargeTimeSqlOfShowTime + noSendTime + noQuerySupplierName + noQueryIntegralSql +",chargeItem name,1 amount,price settlementprice,0 settlementDiscountPrice,'收费项目' type "+ noQueryUnitSql +",'' batchNumber,NULL as expDate ,null as tousseDefinitionId " - + nullTousseGroupNameSQL + + nullTousseGroupNameSQL+ nullTechnicalDifficultySQL+ nullWeightScoreSQL + " 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 " + groupByCrChargeTimeOfShowTime , chargeDepartSql,chargeDateSql,chargeItemSql, @@ -1421,6 +1497,15 @@ if(queryTousseGroupName){ mdi.setTousseGroupName(rs.getString("tousseGroupName")); } + if(queryTechnicalDifficulty){ + mdi.setTechnicalDifficulty(rs.getString("optionText")); + } + if(queryWeightScore){ + Double weightScore = rs.getDouble("weightScore"); + String weightScoreStr = String.format("%.2f", weightScore != null ? weightScore : 0.00); + mdi.setWeightScore(weightScoreStr); + } + boolean flag = true; if (TousseDefinition.PACKAGE_TYPE_DISINFECTION.equals(type) || (TousseDefinition.PACKAGE_TYPE_CUSTOM.equals(type) && !showCustonTousseAmount)) { @@ -1566,6 +1651,12 @@ if(queryTousseGroupName){ item.setTousseGroupName(dmi.getTousseGroupName()); } + if(queryTechnicalDifficulty){ + item.setTechnicalDifficulty(dmi.getTechnicalDifficulty()); + } + if(queryWeightScore){ + item.setWeightScore(dmi.getWeightScore()); + } tmpMap.put(key, item); } if(dmi.getMaterialAmount() != null){ Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/DepartmentMonthDetailItem.java =================================================================== diff -u -r35596 -r40698 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/DepartmentMonthDetailItem.java (.../DepartmentMonthDetailItem.java) (revision 35596) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/DepartmentMonthDetailItem.java (.../DepartmentMonthDetailItem.java) (revision 40698) @@ -70,6 +70,10 @@ * 器械包分组 */ private String tousseGroupName; + + private String technicalDifficulty; + + private String weightScore; public String getGoodsName() { return goodsName; } @@ -239,5 +243,23 @@ public void setTousseGroupName(String tousseGroupName) { this.tousseGroupName = tousseGroupName; } + + public String getTechnicalDifficulty() { + return technicalDifficulty; + } + + public void setTechnicalDifficulty(String technicalDifficulty) { + this.technicalDifficulty = technicalDifficulty; + } + + public String getWeightScore() { + return weightScore; + } + + public void setWeightScore(String weightScore) { + this.weightScore = weightScore; + } + + }