Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/GoodsAmountSummaryByGradeHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/GoodsAmountSummaryByGradeHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/GoodsAmountSummaryByGradeHelper.java (revision 35813) @@ -0,0 +1,308 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.commons.collections4.CollectionUtils; +import org.apache.commons.lang.StringUtils; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; +import com.forgon.directory.acegi.tools.AcegiHelper; +import com.forgon.directory.model.OrgUnit; +import com.forgon.disinfectsystem.entity.assestmanagement.GodownEntry; +import com.forgon.disinfectsystem.jasperreports.javabeansource.GoodsAmountSummaryByGrade; +import com.forgon.tools.db.InitDbConnection; +import com.forgon.tools.hibernate.ObjectDao; +/** + * 一次性物品三类查询报表 + * + */ +@Component +public class GoodsAmountSummaryByGradeHelper { + @Autowired + protected ObjectDao objectDao; + @Autowired + protected InitDbConnection dbConnection; + /** + * 根据物品、时间范围、类别等条件按物品分组统计采购计划数量、入库数量、出库数量 + * @param timeStart + * @param timeEnd + * @param goodsName + * @param grade + * @param orgUnitCoding + * @return + */ + public List getGoodsAmountSummaryByGrade(String timeStart, String timeEnd, String goodsName,String grade,String orgUnitName){ + OrgUnit orgUnit = (OrgUnit)objectDao.getByProperty(OrgUnit.class.getSimpleName(), "name", orgUnitName); + String orgUnitCoding = orgUnit == null ? AcegiHelper.getCurrentOrgUnitCode() : orgUnit.getOrgUnitCoding(); + List returnList = new ArrayList(); + String timeCondition = "";//采购计划、入/退库时间条件 + String sendTimeCondition = "";//发货时间条件 + String returnTimeCondition = "";//退货时间条件 + String goodsNameCondition = "";//物品条件 + String gradeCondition = "";//类别条件 + + if(StringUtils.isNotBlank(grade)){ + gradeCondition = " and po.grade = '" + grade + "'"; + } + + String goodsPlanSummarySql = "";//采购计划 + String inStorageSummarySql = "";//购入入库 + String moveInSummarySql = "";//调拨入库 + String stockTaskInSummarySql = "";//盘盈入库 + String moveOutSummarySql = "";//调拨出库 + String stockTaskOutSummarySql = "";//盘亏出库 + String outStorageSummarySql = "";//退库 + String invoiceSummarySql = "";//发货 + String receiveSummarySql = "";//供应室领用 + String returnSummarySql = "";//退货 + //按数据库类型分别处理 + if(dbConnection.isSqlServer()){ + //sqlserver的处理逻辑 + if(StringUtils.isNotBlank(timeStart) && StringUtils.isNotBlank(timeEnd)){ + timeCondition = " and f.time between '"+timeStart+"' and '"+timeEnd+"'"; + sendTimeCondition = " and f.sendTime between '"+timeStart+"' and '"+timeEnd+"'"; + returnTimeCondition = " and f.returnTime between '"+timeStart+"' and '"+timeEnd+"'"; + } + if(StringUtils.isNotBlank(goodsName)){ + if(goodsName.contains("[") && goodsName.contains("]") && goodsName.lastIndexOf("]") > goodsName.indexOf("[")){ + goodsNameCondition = " and po.name + '[' + po.specification + ']' = '" + goodsName + "'"; + }else{ + goodsNameCondition = " and po.name = '" + goodsName + "'"; + } + } + //一次性物品信息及采购数量 + goodsPlanSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(gpi.scheduleAmout) amount from DisposableGoods po" + +" left join GoodPurchaseItem gpi on po.id=gpi.disposableGoodsId" + +" and gpi.goodPurchasePlan_id in (select id from GoodPurchasePlan f where 1=1 " + timeCondition + +" and f.orgUnit_id in (select id from OrgUnit where orgUnitCoding='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-购入入库(手工入库,同步入库,导入入库) + inStorageSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(gei.amount) amount from DisposableGoods po " + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in " + +" (select id from GodownEntry f where f.type='"+GodownEntry.TYPE_IN+"' and f.subType in ('"+GodownEntry.SUBTYPE_MANUAL_IN+"','"+GodownEntry.SUBTYPE_SYNC_IN+"','"+GodownEntry.SUBTYPE_IMPORT_IN+"') " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-调拨入库 + moveInSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(gei.amount) amount from DisposableGoods po" + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_MOVE_IN + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-盘盈入库 + stockTaskInSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(gei.amount) amount from DisposableGoods po " + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_STOCKTAKE_IN + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-调拨出库 + moveOutSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, -1 * sum(gei.amount) amount from DisposableGoods po " + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_MOVE_OUT + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-盘亏出库 + stockTaskOutSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, -1 * sum(gei.amount) amount from DisposableGoods po" + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType = '" + GodownEntry.SUBTYPE_STOCKTAKE_OUT + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-退库单 + outStorageSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, -1 * sum(gei.amount) amount from DisposableGoods po " + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subtype='" + GodownEntry.SUBTYPE_MANUAL_OUT + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息出库数量查询-发货 + invoiceSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(ii.amount) amount from DisposableGoods po" + +" left join InvoiceItem ii on po.id=ii.disposableGoodsId" + +" and ii.invoice_id in (select id from Invoice f where 1=1 " + sendTimeCondition + " and f.orgUnitCoding='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //供应室领用 + receiveSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(rri.amount) amount from DisposableGoods po" + +" left join ReceiveRecordItem rri on po.id=rri.disposableGoodsId" + +" and rri.receiveRecord_id in (select id from ReceiveRecord f where 1=1 " + timeCondition + " and f.departCoding='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息出库数量查询-退货 + returnSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, -1*sum(rgi.amount) amount from DisposableGoods po " + +" left join ReturnGoodsItem rgi on po.id=rgi.disposableGoodsID" + +" and rgi.returnGoodsRecord_ID in (select id from ReturnGoodsRecord f where 1=1 " + returnTimeCondition + " and f.handleDepartCode='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + }else if(dbConnection.isOracle()){ + //oracle的处理逻辑 + if(StringUtils.isNotBlank(timeStart) && StringUtils.isNotBlank(timeEnd)){ + timeCondition = " and f.time between to_date('"+timeStart+"','yyyy-mm-dd hh24:mi:ss') and to_date('"+timeEnd+"','yyyy-mm-dd hh24:mi:ss')"; + sendTimeCondition = " and f.sendTime between to_date('"+timeStart+"','yyyy-mm-dd hh24:mi:ss') and to_date('"+timeEnd+"','yyyy-mm-dd hh24:mi:ss')"; + returnTimeCondition = " and f.returnTime between to_date('"+timeStart+"','yyyy-mm-dd hh24:mi:ss') and to_date('"+timeEnd+"','yyyy-mm-dd hh24:mi:ss')"; + } + if(StringUtils.isNotBlank(goodsName)){ + if(goodsName.contains("[") && goodsName.contains("]") && goodsName.lastIndexOf("]") > goodsName.indexOf("[")){ + goodsNameCondition = " and po.name || '[' || po.specification || ']' = '" + goodsName + "'"; + }else{ + goodsNameCondition = " and po.name = '" + goodsName + "'"; + } + } + //一次性物品信息及采购数量 + goodsPlanSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(gpi.scheduleAmout) amount from DisposableGoods po" + +" left join GoodPurchaseItem gpi on po.id=gpi.disposableGoodsId" + +" and gpi.goodPurchasePlan_id in (select id from GoodPurchasePlan f where 1=1 " + timeCondition + +" and f.orgUnit_id in (select id from OrgUnit where orgUnitCoding='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-购入入库(手工入库,同步入库,导入入库) + inStorageSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(gei.amount) amount from DisposableGoods po " + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in " + +" (select id from GodownEntry f where f.type='"+GodownEntry.TYPE_IN+"' and f.subType in ('"+GodownEntry.SUBTYPE_MANUAL_IN+"','"+GodownEntry.SUBTYPE_SYNC_IN+"','"+GodownEntry.SUBTYPE_IMPORT_IN+"') " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-调拨入库 + moveInSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(gei.amount) amount from DisposableGoods po" + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_MOVE_IN + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-盘盈入库 + stockTaskInSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(gei.amount) amount from DisposableGoods po " + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_STOCKTAKE_IN + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-调拨出库 + moveOutSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, -1 * sum(gei.amount) amount from DisposableGoods po " + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_MOVE_OUT + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-盘亏出库 + stockTaskOutSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, -1 * sum(gei.amount) amount from DisposableGoods po" + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType = '" + GodownEntry.SUBTYPE_STOCKTAKE_OUT + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息入库数量-退库单 + outStorageSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, -1 * sum(gei.amount) amount from DisposableGoods po " + +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" + +" and gei.godownEntry_id in (select id from GodownEntry f where f.subtype='" + GodownEntry.SUBTYPE_MANUAL_OUT + "' " + timeCondition + +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息出库数量查询-发货 + invoiceSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(ii.amount) amount from DisposableGoods po" + +" left join InvoiceItem ii on po.id=ii.disposableGoodsId" + +" and ii.invoice_id in (select id from Invoice f where 1=1 " + sendTimeCondition + " and f.orgUnitCoding='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //供应室领用 + receiveSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(rri.amount) amount from DisposableGoods po" + +" left join ReceiveRecordItem rri on po.id=rri.disposableGoodsId" + +" and rri.receiveRecord_id in (select id from ReceiveRecord f where 1=1 " + timeCondition + " and f.departCoding='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + + //一次性物品信息出库数量查询-退货 + returnSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, -1*sum(rgi.amount) amount from DisposableGoods po " + +" left join ReturnGoodsItem rgi on po.id=rgi.disposableGoodsId" + +" and rgi.returnGoodsRecord_ID in (select id from ReturnGoodsRecord f where 1=1 " + returnTimeCondition + " and f.handleDepartCode='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition + +" group by po.externalCode, po.name,po.specification order by po.externalCode"; + }else{ + //sqlserver与oracle以外的数据库处理 + } + + List> goodsPlanSummaryMapList = getGoodsAmountSummaryByGradeMap(goodsPlanSummarySql); + List> inStorageSummaryMapList = getGoodsAmountSummaryByGradeMap(inStorageSummarySql); + List> moveInSummaryMapList = getGoodsAmountSummaryByGradeMap(moveInSummarySql); + List> stockTaskInSummaryMapList = getGoodsAmountSummaryByGradeMap(stockTaskInSummarySql); + List> moveOutSummaryMapList = getGoodsAmountSummaryByGradeMap(moveOutSummarySql); + List> stockTaskOutSummaryMapList = getGoodsAmountSummaryByGradeMap(stockTaskOutSummarySql); + + List> outStorageSummaryMapList = getGoodsAmountSummaryByGradeMap(outStorageSummarySql); + List> invoiceSummaryMapList = getGoodsAmountSummaryByGradeMap(invoiceSummarySql); + List> receiveSummaryMapList = getGoodsAmountSummaryByGradeMap(receiveSummarySql); + List> returnSummaryMapList = getGoodsAmountSummaryByGradeMap(returnSummarySql); + + if(CollectionUtils.isNotEmpty(goodsPlanSummaryMapList)){ + for (int i = 0; i < goodsPlanSummaryMapList.size(); i++) { + Map goodsPlanSummaryMap = goodsPlanSummaryMapList.get(i); + Map inStorageSummaryMap = inStorageSummaryMapList.get(i); + Map moveInSummaryMap = moveInSummaryMapList.get(i); + Map moveOutSummaryMap = moveOutSummaryMapList.get(i); + Map stockTaskInSummaryMap = stockTaskInSummaryMapList.get(i); + Map stockTaskOutSummaryMap = stockTaskOutSummaryMapList.get(i); + Map outStorageSummaryMap = outStorageSummaryMapList.get(i); + Map invoiceSummaryMap = invoiceSummaryMapList.get(i); + Map receiveSummaryMap = receiveSummaryMapList.get(i); + Map returnSummaryMap = returnSummaryMapList.get(i); + + long goodsPlanAmount = goodsPlanSummaryMap.get("amount") == null ? 0 : ((Number)goodsPlanSummaryMap.get("amount")).longValue(); + + long inStorageAmount = inStorageSummaryMap.get("amount") == null ? 0 : ((Number)inStorageSummaryMap.get("amount")).longValue(); + long moveInAmount = moveInSummaryMap.get("amount") == null ? 0 : ((Number)moveInSummaryMap.get("amount")).longValue(); + long moveOutAmount = moveOutSummaryMap.get("amount") == null ? 0 : ((Number)moveOutSummaryMap.get("amount")).longValue(); + long stockTaskInAmount = stockTaskInSummaryMap.get("amount") == null ? 0 : ((Number)stockTaskInSummaryMap.get("amount")).longValue(); + long stockTaskOutAmount = stockTaskOutSummaryMap.get("amount") == null ? 0 : ((Number)stockTaskOutSummaryMap.get("amount")).longValue(); + long outStorageAmount = outStorageSummaryMap.get("amount") == null ? 0 : ((Number)outStorageSummaryMap.get("amount")).longValue(); + long inTotalAmount = inStorageAmount + moveInAmount + moveOutAmount + stockTaskInAmount + stockTaskOutAmount + outStorageAmount; + + long invoiceAmount = invoiceSummaryMap.get("amount") == null ? 0 : ((Number)invoiceSummaryMap.get("amount")).longValue(); + long receiveAmount = receiveSummaryMap.get("amount") == null ? 0 : ((Number)receiveSummaryMap.get("amount")).longValue(); + long returnAmount = returnSummaryMap.get("amount") == null ? 0 : ((Number)returnSummaryMap.get("amount")).longValue(); + long outTotalAmount = invoiceAmount + receiveAmount + returnAmount; + + /*logger.debug("##########externalCode=" + goodsPlanSummaryMap.get("externalCode") + ",goodsPlanAmount=" +goodsPlanAmount+ + ",inTotalAmount=" +inTotalAmount+ ",outTotalAmount=" +outTotalAmount);*/ + if(goodsPlanAmount != 0 || inTotalAmount != 0 || outTotalAmount != 0){ + GoodsAmountSummaryByGrade vo = new GoodsAmountSummaryByGrade(); + vo.setExternalCode((String)goodsPlanSummaryMap.get("externalCode")); + vo.setName((String)goodsPlanSummaryMap.get("name")); + vo.setGoodsPurchaseAmount(goodsPlanAmount); + vo.setInStorageAmount(inTotalAmount); + vo.setOutStorageAmount(outTotalAmount); + returnList.add(vo); + } + } + } + + return returnList; + } + /** + * 根据汇总语句生成到集合中 + * @param summarySql + * @return + */ + private List> getGoodsAmountSummaryByGradeMap(String summarySql){ +// logger.debug("getGoodsAmountSummaryByGradeMap:summarySql=" + summarySql); + List> list = new ArrayList>(); + ResultSet rs = null; + try{ + rs = objectDao.executeSql(summarySql); + while(rs.next()){ + Map map = new HashMap(); + map.put("externalCode", rs.getString("externalCode")); + map.put("name", rs.getString("name")); + map.put("amount", rs.getObject("amount")); + list.add(map); + } + }catch(Exception e){ + e.printStackTrace(); + } + return list; + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ForeignTousseSupplierChargeReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ForeignTousseSupplierChargeReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ForeignTousseSupplierChargeReportHelper.java (revision 35813) @@ -0,0 +1,337 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.commons.collections4.CollectionUtils; +import org.apache.commons.lang.StringUtils; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +import com.forgon.databaseadapter.service.DateQueryAdapter; +import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; +import com.forgon.disinfectsystem.entity.foreigntoussecharge.ChargeSetting; +import com.forgon.disinfectsystem.entity.foreigntoussecharge.ChargeSettingModeItem; +import com.forgon.disinfectsystem.entity.foreigntoussecharge.ChargeSettingSpecItem; +import com.forgon.disinfectsystem.entity.foreigntoussecharge.ForeignTousseChargeMode; +import com.forgon.disinfectsystem.entity.foreigntoussecharge.ForeignTousseSpecification; +import com.forgon.disinfectsystem.entity.invoicemanager.InvoicePlan; +import com.forgon.disinfectsystem.jasperreports.javabeansource.FirstColumnVo; +import com.forgon.disinfectsystem.jasperreports.javabeansource.ForeignTousseAppDetailedVO; +import com.forgon.disinfectsystem.jasperreports.javabeansource.ForeignTousseAppSumVO; +import com.forgon.disinfectsystem.jasperreports.javabeansource.SecondColumnVo; +import com.forgon.disinfectsystem.jasperreports.javabeansource.ThirdColumnVo; +import com.forgon.tools.MathTools; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.hibernate.ObjectDao; + +/** + * + * 外来器械包供应商收费报表 + * + */ +@Component +public class ForeignTousseSupplierChargeReportHelper { + @Autowired + protected DateQueryAdapter dateQueryAdapter; + @Autowired + protected ObjectDao objectDao; + /** + * 外来器械包供应商收费报表之明细表 + * @param startDay + * @param endDay + * @param companyName供应商 + * @param foreignTousseName 外来器械包名称 + * @param chargingMode 收费模式 + * @return + */ + public List queryForeignTousseApplicationDetailedReport( + String startDay, String endDay, String companyName, String foreignTousseName, Long chargingModeId, Long specificationId) { + ResultSet rs = null; + List firstColumnVos = new ArrayList(); + Map firstColumnVoMap = new HashMap(); + try { + rs = objectDao.executeSql(getForeignTousseApplicationDetailedReportSql(startDay, endDay, companyName, foreignTousseName, chargingModeId, specificationId)); + while (rs.next()) { + int amount = rs.getInt("amount"); + if(amount == 0){ + continue; + } + ForeignTousseAppDetailedVO foreignTousseAppDetailedVO = new ForeignTousseAppDetailedVO(rs.getString("specification"), rs.getDouble("price"), amount, rs.getDouble("totalPrice"), rs.getString("serialNumber"), rs.getTimestamp("applicationTime")); + String firstColumnName = rs.getString("supplierName"); + String secondColumnName = rs.getString("name"); + String thirdColumnName =rs.getString("foreignTousseChargeModeName"); + //ForeignTousseAppSumVO vo = new ForeignTousseAppSumVO(); + if(StringUtils.isBlank(firstColumnName)){ + firstColumnName = null; + } + if(StringUtils.isBlank(secondColumnName)){ + secondColumnName = null; + } + FirstColumnVo firstColumnVo = null; + SecondColumnVo secondColumnVo = null; + ThirdColumnVo thirdColumnVo = null; + List secondColumnVos = null; + List thirdColumnVos = null; + List foreignTousseAppDetailedVOs = null; + + if(firstColumnVoMap.containsKey(firstColumnName)){ + firstColumnVo = firstColumnVoMap.get(firstColumnName); + secondColumnVos = firstColumnVo.getFirstColumnDetaileds(); + }else{ + firstColumnVoMap.clear();//因为是按第一列的值排序的， 所以找不到就说明是新的列值，旧的数据可以清掉了 + firstColumnVo = new FirstColumnVo(); + firstColumnVo.setFirstColumnName(firstColumnName); + secondColumnVos = new ArrayList(); + firstColumnVo.setFirstColumnDetaileds(secondColumnVos); + firstColumnVoMap.put(firstColumnName, firstColumnVo); + firstColumnVos.add(firstColumnVo); + } + if(!CollectionUtils.isEmpty(secondColumnVos)){ + for (SecondColumnVo findSecondColumnVo : secondColumnVos) { + if(StringUtils.equals(findSecondColumnVo.getSecondColumnName(), secondColumnName)){ + secondColumnVo = findSecondColumnVo; + break; + } + } + } + if(secondColumnVo == null){ + secondColumnVo = new SecondColumnVo(); + secondColumnVo.setSecondColumnName(secondColumnName); + secondColumnVos.add(secondColumnVo); + thirdColumnVos = new ArrayList(); + secondColumnVo.setSecondColumnDetaileds(thirdColumnVos); + }else{ + thirdColumnVos = secondColumnVo.getSecondColumnDetaileds(); + } + if(!CollectionUtils.isEmpty(thirdColumnVos)){ + for (ThirdColumnVo findThirdColumnVo : thirdColumnVos) { + if(StringUtils.equals(findThirdColumnVo.getThirdColumnName(), thirdColumnName)){ + thirdColumnVo = findThirdColumnVo; + break; + } + } + } + if(thirdColumnVo == null){ + thirdColumnVo = new ThirdColumnVo(); + thirdColumnVo.setThirdColumnName(thirdColumnName); + thirdColumnVos.add(thirdColumnVo); + foreignTousseAppDetailedVOs = new ArrayList(); + thirdColumnVo.setThirdColumnDetaileds(foreignTousseAppDetailedVOs); + } + thirdColumnVo.getThirdColumnDetaileds().add(foreignTousseAppDetailedVO); + firstColumnVo.setFirstColumnTotalAmount(MathTools.add(foreignTousseAppDetailedVO.getAmount(), firstColumnVo.getFirstColumnTotalAmount()).intValue()); + firstColumnVo.setFirstColumnTotalPrice(MathTools.add(foreignTousseAppDetailedVO.getTotalPrice(), firstColumnVo.getFirstColumnTotalPrice()).doubleValue()); + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + return firstColumnVos; + } + /** + * 查询外来器械包供应商收费明细报表的sql + * @param startDay + * @param endDay + * @param companyName 供应商 + * @param foreignTousseName 外来器械包名称 + * @param chargingModeId 收费模式id + * @param specificationId 收费规格id + * @return + */ + private String getForeignTousseApplicationDetailedReportSql(String startDay, String endDay, String companyName, String foreignTousseName, Long chargingModeId, Long specificationId){ + if (StringUtils.isNotBlank(endDay)) { + endDay += ":59"; + } + if (StringUtils.isNotBlank(startDay)) { + startDay += ":00"; + } + String sql = "select * from (select cs.supplierName,td.name,csmi.foreignTousseChargeModeName,case when csmi.chargeMode='" + + ForeignTousseChargeMode.MODE_RATE + + "' then cssi.foreignTousseSpecName else null end specification,case when csmi.chargeMode='" + + ForeignTousseChargeMode.MODE_RATE + + "' then cssi.price else csmi.fixedPrice end price,case when csmi.chargeMode='" + + ForeignTousseChargeMode.MODE_RATE + + "' then cssi.amount else csmi.fixedAmount end amount,case when csmi.chargeMode='" + + ForeignTousseChargeMode.MODE_RATE + + "' then cssi.totalPrice else csmi.fixedTotalPrice end totalPrice,ip.serialNumber,ip.applicationTime from " + + ChargeSetting.class.getSimpleName() + + " cs join " + + InvoicePlan.class.getSimpleName() + + " ip on ip.id=cs.invoicePlanID join " + + TousseDefinition.class.getSimpleName() + + " td on td.id=cs.tousseDefinitionID left join " + + ChargeSettingModeItem.class.getSimpleName() + + " csmi on csmi.setting_id=cs.id left join " + + ChargeSettingSpecItem.class.getSimpleName() + + " cssi on cssi.setting_mode_id=csmi.id where (ip.recyclingStatus is null or ip.recyclingStatus<>'"+ InvoicePlan.STATUS_END +"') and ip.applicationTime between " + + dateQueryAdapter.dateAdapter(startDay) + + " and " + + dateQueryAdapter.dateAdapter(endDay); + if(StringUtils.isNotBlank(companyName)){ + sql += " and cs.supplierName ='" + companyName + "' "; + } + if(StringUtils.isNotBlank(foreignTousseName)){ + sql += " and td.name ='" + foreignTousseName + "' "; + } + if(DatabaseUtil.isPoIdValid(chargingModeId)){ + sql += " and csmi.foreignTousseChargeModeID ='" + chargingModeId + "' "; + } + if(DatabaseUtil.isPoIdValid(specificationId)){ + sql += " and cssi.foreignTousseSpecID ='" + specificationId + "' "; + } + sql += ") tempt left join "+ ForeignTousseSpecification.class.getSimpleName() +" fts on fts.name=tempt.specification where tempt.amount>0 order by tempt.supplierName,tempt.name,tempt.foreignTousseChargeModeName,fts.id"; + return sql; + } + /** + * 外来器械包供应商收费报表之汇总表 + * @param startDay + * @param endDay + * @param companyName 供应商 + * @param foreignTousseName 外来器械包名称 + * @param chargingMode 收费模式 + * @return + */ + public List queryForeignTousseApplicationSummaryReport( + String startDay, String endDay, String companyName, String foreignTousseName, Long chargingModeId, Long specificationId) { + ResultSet rs = null; + List firstColumnVos = new ArrayList(); + Map firstColumnVoMap = new HashMap(); + try { + rs = objectDao.executeSql(getForeignTousseApplicationSummaryReportSql(startDay, endDay, companyName, foreignTousseName, chargingModeId, specificationId)); + while (rs.next()) { + int amount = rs.getInt("amount"); + if(amount == 0){ + continue; + } + ForeignTousseAppSumVO foreignTousseAppSumVO = new ForeignTousseAppSumVO(rs.getDouble("price"), amount, rs.getDouble("totalPrice")); + String firstColumnName = rs.getString("supplierName"); + String secondColumnName = rs.getString("foreignTousseChargeModeName"); + String thirdColumnName =rs.getString("specification"); + if(StringUtils.isBlank(firstColumnName)){ + firstColumnName = null; + } + if(StringUtils.isBlank(secondColumnName)){ + secondColumnName = null; + } + FirstColumnVo firstColumnVo = null; + SecondColumnVo secondColumnVo = null; + ThirdColumnVo thirdColumnVo = null; + List secondColumnVos = null; + List thirdColumnVos = null; + List foreignTousseAppDetailedVOs = null; + if(firstColumnVoMap.containsKey(firstColumnName)){ + firstColumnVo = firstColumnVoMap.get(firstColumnName); + secondColumnVos = firstColumnVo.getFirstColumnDetaileds(); + }else{ + firstColumnVoMap.clear();//因为是按第一列的值排序的， 所以找不到就说明是新的列值，旧的数据可以清掉了 + firstColumnVo = new FirstColumnVo(); + firstColumnVo.setFirstColumnName(firstColumnName); + secondColumnVos = new ArrayList(); + firstColumnVo.setFirstColumnDetaileds(secondColumnVos); + firstColumnVoMap.put(firstColumnName, firstColumnVo); + firstColumnVos.add(firstColumnVo); + } + if(!CollectionUtils.isEmpty(secondColumnVos)){ + for (SecondColumnVo findSecondColumnVo : secondColumnVos) { + if(StringUtils.equals(findSecondColumnVo.getSecondColumnName(), secondColumnName)){ + secondColumnVo = findSecondColumnVo; + break; + } + } + } + if(secondColumnVo == null){ + secondColumnVo = new SecondColumnVo(); + secondColumnVo.setSecondColumnName(secondColumnName); + secondColumnVos.add(secondColumnVo); + thirdColumnVos = new ArrayList(); + secondColumnVo.setSecondColumnDetaileds(thirdColumnVos); + }else{ + thirdColumnVos = secondColumnVo.getSecondColumnDetaileds(); + } + if(!CollectionUtils.isEmpty(thirdColumnVos)){ + for (ThirdColumnVo findThirdColumnVo : thirdColumnVos) { + if(StringUtils.equals(findThirdColumnVo.getThirdColumnName(), thirdColumnName)){ + thirdColumnVo = findThirdColumnVo; + break; + } + } + } + if(thirdColumnVo == null){ + thirdColumnVo = new ThirdColumnVo(); + thirdColumnVo.setThirdColumnName(thirdColumnName); + thirdColumnVos.add(thirdColumnVo); + foreignTousseAppDetailedVOs = new ArrayList(); + thirdColumnVo.setThirdColumnDetaileds(foreignTousseAppDetailedVOs); + } + thirdColumnVo.getThirdColumnDetaileds().add(foreignTousseAppSumVO); + firstColumnVo.setFirstColumnTotalAmount(MathTools.add(foreignTousseAppSumVO.getAmount(), firstColumnVo.getFirstColumnTotalAmount()).intValue()); + firstColumnVo.setFirstColumnTotalPrice(MathTools.add(foreignTousseAppSumVO.getTotalPrice(), firstColumnVo.getFirstColumnTotalPrice()).doubleValue()); + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + return firstColumnVos; + } + /** + * 查询外来器械包供应商收费汇总报表的sql + * @param startDay + * @param endDay + * @param companyName 供应商 + * @param chargingModeId 收费模式id + * @param specificationId 收费规格id + * @return + */ + private String getForeignTousseApplicationSummaryReportSql(String startDay, String endDay, String companyName, String foreignTousseName, Long chargingModeId, Long specificationId){ + if (StringUtils.isNotBlank(endDay)) { + endDay += ":59"; + } + if (StringUtils.isNotBlank(startDay)) { + startDay += ":00"; + } + String sql = "select tempt.supplierName,tempt.foreignTousseChargeModeName,tempt.specification,tempt.price,sum(tempt.amount) amount,sum(tempt.totalPrice) totalPrice from (select cs.supplierName,csmi.foreignTousseChargeModeName,case when csmi.chargeMode='" + + ForeignTousseChargeMode.MODE_RATE + + "' then cssi.foreignTousseSpecName else null end specification,case when csmi.chargeMode='" + + ForeignTousseChargeMode.MODE_RATE + + "' then cssi.price else csmi.fixedPrice end price,case when csmi.chargeMode='" + + ForeignTousseChargeMode.MODE_RATE + + "' then cssi.amount else csmi.fixedAmount end amount,case when csmi.chargeMode='" + + ForeignTousseChargeMode.MODE_RATE + + "' then cssi.totalPrice else csmi.fixedTotalPrice end totalPrice from " + + ChargeSetting.class.getSimpleName() + + " cs join " + + InvoicePlan.class.getSimpleName() + + " ip on ip.id=cs.invoicePlanID join " + + TousseDefinition.class.getSimpleName() + + " td on td.id=cs.tousseDefinitionID left join " + + ChargeSettingModeItem.class.getSimpleName() + + " csmi on csmi.setting_id=cs.id " + + " left join " + + ChargeSettingSpecItem.class.getSimpleName() + + " cssi on cssi.setting_mode_id=csmi.id where (ip.recyclingStatus is null or ip.recyclingStatus<>'"+ InvoicePlan.STATUS_END +"') and ip.applicationTime between " + + dateQueryAdapter.dateAdapter(startDay) + + " and " + + dateQueryAdapter.dateAdapter(endDay); + if(StringUtils.isNotBlank(companyName)){ + sql += " and cs.supplierName ='" + companyName + "' "; + } + if(StringUtils.isNotBlank(foreignTousseName)){ + sql += " and td.name ='" + foreignTousseName + "' "; + } + if(DatabaseUtil.isPoIdValid(chargingModeId)){ + sql += " and csmi.foreignTousseChargeModeID ='" + chargingModeId + "' "; + } + if(DatabaseUtil.isPoIdValid(specificationId)){ + sql += " and cssi.foreignTousseSpecID ='" + specificationId + "' "; + } + sql += " ) tempt left join "+ ForeignTousseSpecification.class.getSimpleName() +" fts on fts.name=tempt.specification where tempt.amount>0 group by tempt.supplierName,tempt.foreignTousseChargeModeName,tempt.specification,fts.id,tempt.price order by tempt.supplierName, tempt.foreignTousseChargeModeName,fts.id,tempt.price "; + return sql; + } +} Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35812 -r35813 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35812) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35813) @@ -182,7 +182,9 @@ import com.forgon.disinfectsystem.jasperreports.util.DeviceMaintenanceHelper; import com.forgon.disinfectsystem.jasperreports.util.EighteenQuotaBeanHelper; import com.forgon.disinfectsystem.jasperreports.util.ForeignTousseApplicationReportHelper; +import com.forgon.disinfectsystem.jasperreports.util.ForeignTousseSupplierChargeReportHelper; import com.forgon.disinfectsystem.jasperreports.util.FormDefinitionHelper; +import com.forgon.disinfectsystem.jasperreports.util.GoodsAmountSummaryByGradeHelper; import com.forgon.disinfectsystem.jasperreports.util.InformationOfDepartmentOperationMonitoringHelper; import com.forgon.disinfectsystem.jasperreports.util.InstrumentRepairReportHelper; import com.forgon.disinfectsystem.jasperreports.util.MonthReportGroupByDisposableGoodsTypeHelper; @@ -372,6 +374,10 @@ private DeviceMaintenanceHelper deviceMaintenanceHelper; @Autowired private InformationOfDepartmentOperationMonitoringHelper informationOfDepartmentOperationMonitoringHelper; + @Autowired + private ForeignTousseSupplierChargeReportHelper foreignTousseSupplierChargeReportHelper; + @Autowired + private GoodsAmountSummaryByGradeHelper goodsAmountSummaryByGradeHelper; public void setPackingManager(PackingManager packingManager) { this.packingManager = packingManager; } @@ -4824,288 +4830,11 @@ } - /** - * 根据物品、时间范围、类别等条件按物品分组统计采购计划数量、入库数量、出库数量 - * @param timeStart - * @param timeEnd - * @param goodsName - * @param grade - * @param orgUnitCoding - * @return - */ @Override public List getGoodsAmountSummaryByGrade(String timeStart, String timeEnd, String goodsName,String grade,String orgUnitName){ - OrgUnit orgUnit = (OrgUnit)objectDao.getByProperty(OrgUnit.class.getSimpleName(), "name", orgUnitName); - String orgUnitCoding = orgUnit == null ? AcegiHelper.getCurrentOrgUnitCode() : orgUnit.getOrgUnitCoding(); - List returnList = new ArrayList(); - String timeCondition = "";//采购计划、入/退库时间条件 - String sendTimeCondition = "";//发货时间条件 - String returnTimeCondition = "";//退货时间条件 - String goodsNameCondition = "";//物品条件 - String gradeCondition = "";//类别条件 - - if(StringUtils.isNotBlank(grade)){ - gradeCondition = " and po.grade = '" + grade + "'"; - } - - String goodsPlanSummarySql = "";//采购计划 - String inStorageSummarySql = "";//购入入库 - String moveInSummarySql = "";//调拨入库 - String stockTaskInSummarySql = "";//盘盈入库 - String moveOutSummarySql = "";//调拨出库 - String stockTaskOutSummarySql = "";//盘亏出库 - String outStorageSummarySql = "";//退库 - String invoiceSummarySql = "";//发货 - String receiveSummarySql = "";//供应室领用 - String returnSummarySql = "";//退货 - //按数据库类型分别处理 - if(dbConnection.isSqlServer()){ - //sqlserver的处理逻辑 - if(StringUtils.isNotBlank(timeStart) && StringUtils.isNotBlank(timeEnd)){ - timeCondition = " and f.time between '"+timeStart+"' and '"+timeEnd+"'"; - sendTimeCondition = " and f.sendTime between '"+timeStart+"' and '"+timeEnd+"'"; - returnTimeCondition = " and f.returnTime between '"+timeStart+"' and '"+timeEnd+"'"; - } - if(StringUtils.isNotBlank(goodsName)){ - if(goodsName.contains("[") && goodsName.contains("]") && goodsName.lastIndexOf("]") > goodsName.indexOf("[")){ - goodsNameCondition = " and po.name + '[' + po.specification + ']' = '" + goodsName + "'"; - }else{ - goodsNameCondition = " and po.name = '" + goodsName + "'"; - } - } - //一次性物品信息及采购数量 - goodsPlanSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(gpi.scheduleAmout) amount from DisposableGoods po" - +" left join GoodPurchaseItem gpi on po.id=gpi.disposableGoodsId" - +" and gpi.goodPurchasePlan_id in (select id from GoodPurchasePlan f where 1=1 " + timeCondition - +" and f.orgUnit_id in (select id from OrgUnit where orgUnitCoding='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-购入入库(手工入库,同步入库,导入入库) - inStorageSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(gei.amount) amount from DisposableGoods po " - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in " - +" (select id from GodownEntry f where f.type='"+GodownEntry.TYPE_IN+"' and f.subType in ('"+GodownEntry.SUBTYPE_MANUAL_IN+"','"+GodownEntry.SUBTYPE_SYNC_IN+"','"+GodownEntry.SUBTYPE_IMPORT_IN+"') " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-调拨入库 - moveInSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(gei.amount) amount from DisposableGoods po" - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_MOVE_IN + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-盘盈入库 - stockTaskInSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(gei.amount) amount from DisposableGoods po " - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_STOCKTAKE_IN + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-调拨出库 - moveOutSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, -1 * sum(gei.amount) amount from DisposableGoods po " - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_MOVE_OUT + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-盘亏出库 - stockTaskOutSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, -1 * sum(gei.amount) amount from DisposableGoods po" - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType = '" + GodownEntry.SUBTYPE_STOCKTAKE_OUT + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-退库单 - outStorageSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, -1 * sum(gei.amount) amount from DisposableGoods po " - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subtype='" + GodownEntry.SUBTYPE_MANUAL_OUT + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息出库数量查询-发货 - invoiceSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(ii.amount) amount from DisposableGoods po" - +" left join InvoiceItem ii on po.id=ii.disposableGoodsId" - +" and ii.invoice_id in (select id from Invoice f where 1=1 " + sendTimeCondition + " and f.orgUnitCoding='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //供应室领用 - receiveSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, sum(rri.amount) amount from DisposableGoods po" - +" left join ReceiveRecordItem rri on po.id=rri.disposableGoodsId" - +" and rri.receiveRecord_id in (select id from ReceiveRecord f where 1=1 " + timeCondition + " and f.departCoding='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息出库数量查询-退货 - returnSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name + '['+ po.specification +']' end name, -1*sum(rgi.amount) amount from DisposableGoods po " - +" left join ReturnGoodsItem rgi on po.id=rgi.disposableGoodsID" - +" and rgi.returnGoodsRecord_ID in (select id from ReturnGoodsRecord f where 1=1 " + returnTimeCondition + " and f.handleDepartCode='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - }else if(dbConnection.isOracle()){ - //oracle的处理逻辑 - if(StringUtils.isNotBlank(timeStart) && StringUtils.isNotBlank(timeEnd)){ - timeCondition = " and f.time between to_date('"+timeStart+"','yyyy-mm-dd hh24:mi:ss') and to_date('"+timeEnd+"','yyyy-mm-dd hh24:mi:ss')"; - sendTimeCondition = " and f.sendTime between to_date('"+timeStart+"','yyyy-mm-dd hh24:mi:ss') and to_date('"+timeEnd+"','yyyy-mm-dd hh24:mi:ss')"; - returnTimeCondition = " and f.returnTime between to_date('"+timeStart+"','yyyy-mm-dd hh24:mi:ss') and to_date('"+timeEnd+"','yyyy-mm-dd hh24:mi:ss')"; - } - if(StringUtils.isNotBlank(goodsName)){ - if(goodsName.contains("[") && goodsName.contains("]") && goodsName.lastIndexOf("]") > goodsName.indexOf("[")){ - goodsNameCondition = " and po.name || '[' || po.specification || ']' = '" + goodsName + "'"; - }else{ - goodsNameCondition = " and po.name = '" + goodsName + "'"; - } - } - //一次性物品信息及采购数量 - goodsPlanSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(gpi.scheduleAmout) amount from DisposableGoods po" - +" left join GoodPurchaseItem gpi on po.id=gpi.disposableGoodsId" - +" and gpi.goodPurchasePlan_id in (select id from GoodPurchasePlan f where 1=1 " + timeCondition - +" and f.orgUnit_id in (select id from OrgUnit where orgUnitCoding='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-购入入库(手工入库,同步入库,导入入库) - inStorageSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(gei.amount) amount from DisposableGoods po " - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in " - +" (select id from GodownEntry f where f.type='"+GodownEntry.TYPE_IN+"' and f.subType in ('"+GodownEntry.SUBTYPE_MANUAL_IN+"','"+GodownEntry.SUBTYPE_SYNC_IN+"','"+GodownEntry.SUBTYPE_IMPORT_IN+"') " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-调拨入库 - moveInSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(gei.amount) amount from DisposableGoods po" - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_MOVE_IN + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-盘盈入库 - stockTaskInSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(gei.amount) amount from DisposableGoods po " - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_STOCKTAKE_IN + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-调拨出库 - moveOutSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, -1 * sum(gei.amount) amount from DisposableGoods po " - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType='" + GodownEntry.SUBTYPE_MOVE_OUT + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-盘亏出库 - stockTaskOutSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, -1 * sum(gei.amount) amount from DisposableGoods po" - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subType = '" + GodownEntry.SUBTYPE_STOCKTAKE_OUT + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息入库数量-退库单 - outStorageSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, -1 * sum(gei.amount) amount from DisposableGoods po " - +" left join GodownEntryItem gei on po.id=gei.disposableGoodsId" - +" and gei.godownEntry_id in (select id from GodownEntry f where f.subtype='" + GodownEntry.SUBTYPE_MANUAL_OUT + "' " + timeCondition - +" and warehouseID in (select id from WareHouse where orgUnitCode='" + orgUnitCoding + "')) where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息出库数量查询-发货 - invoiceSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(ii.amount) amount from DisposableGoods po" - +" left join InvoiceItem ii on po.id=ii.disposableGoodsId" - +" and ii.invoice_id in (select id from Invoice f where 1=1 " + sendTimeCondition + " and f.orgUnitCoding='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //供应室领用 - receiveSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, sum(rri.amount) amount from DisposableGoods po" - +" left join ReceiveRecordItem rri on po.id=rri.disposableGoodsId" - +" and rri.receiveRecord_id in (select id from ReceiveRecord f where 1=1 " + timeCondition + " and f.departCoding='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - - //一次性物品信息出库数量查询-退货 - returnSummarySql = "select po.externalCode, case when po.specification is null then po.name else po.name || '['|| po.specification ||']' end name, -1*sum(rgi.amount) amount from DisposableGoods po " - +" left join ReturnGoodsItem rgi on po.id=rgi.disposableGoodsId" - +" and rgi.returnGoodsRecord_ID in (select id from ReturnGoodsRecord f where 1=1 " + returnTimeCondition + " and f.handleDepartCode='" + orgUnitCoding + "') where 1=1 " + goodsNameCondition + gradeCondition - +" group by po.externalCode, po.name,po.specification order by po.externalCode"; - }else{ - //sqlserver与oracle以外的数据库处理 - } - - List> goodsPlanSummaryMapList = getGoodsAmountSummaryByGradeMap(goodsPlanSummarySql); - List> inStorageSummaryMapList = getGoodsAmountSummaryByGradeMap(inStorageSummarySql); - List> moveInSummaryMapList = getGoodsAmountSummaryByGradeMap(moveInSummarySql); - List> stockTaskInSummaryMapList = getGoodsAmountSummaryByGradeMap(stockTaskInSummarySql); - List> moveOutSummaryMapList = getGoodsAmountSummaryByGradeMap(moveOutSummarySql); - List> stockTaskOutSummaryMapList = getGoodsAmountSummaryByGradeMap(stockTaskOutSummarySql); - - List> outStorageSummaryMapList = getGoodsAmountSummaryByGradeMap(outStorageSummarySql); - List> invoiceSummaryMapList = getGoodsAmountSummaryByGradeMap(invoiceSummarySql); - List> receiveSummaryMapList = getGoodsAmountSummaryByGradeMap(receiveSummarySql); - List> returnSummaryMapList = getGoodsAmountSummaryByGradeMap(returnSummarySql); - - if(CollectionUtils.isNotEmpty(goodsPlanSummaryMapList)){ - for (int i = 0; i < goodsPlanSummaryMapList.size(); i++) { - Map goodsPlanSummaryMap = goodsPlanSummaryMapList.get(i); - Map inStorageSummaryMap = inStorageSummaryMapList.get(i); - Map moveInSummaryMap = moveInSummaryMapList.get(i); - Map moveOutSummaryMap = moveOutSummaryMapList.get(i); - Map stockTaskInSummaryMap = stockTaskInSummaryMapList.get(i); - Map stockTaskOutSummaryMap = stockTaskOutSummaryMapList.get(i); - Map outStorageSummaryMap = outStorageSummaryMapList.get(i); - Map invoiceSummaryMap = invoiceSummaryMapList.get(i); - Map receiveSummaryMap = receiveSummaryMapList.get(i); - Map returnSummaryMap = returnSummaryMapList.get(i); - - long goodsPlanAmount = goodsPlanSummaryMap.get("amount") == null ? 0 : ((Number)goodsPlanSummaryMap.get("amount")).longValue(); - - long inStorageAmount = inStorageSummaryMap.get("amount") == null ? 0 : ((Number)inStorageSummaryMap.get("amount")).longValue(); - long moveInAmount = moveInSummaryMap.get("amount") == null ? 0 : ((Number)moveInSummaryMap.get("amount")).longValue(); - long moveOutAmount = moveOutSummaryMap.get("amount") == null ? 0 : ((Number)moveOutSummaryMap.get("amount")).longValue(); - long stockTaskInAmount = stockTaskInSummaryMap.get("amount") == null ? 0 : ((Number)stockTaskInSummaryMap.get("amount")).longValue(); - long stockTaskOutAmount = stockTaskOutSummaryMap.get("amount") == null ? 0 : ((Number)stockTaskOutSummaryMap.get("amount")).longValue(); - long outStorageAmount = outStorageSummaryMap.get("amount") == null ? 0 : ((Number)outStorageSummaryMap.get("amount")).longValue(); - long inTotalAmount = inStorageAmount + moveInAmount + moveOutAmount + stockTaskInAmount + stockTaskOutAmount + outStorageAmount; - - long invoiceAmount = invoiceSummaryMap.get("amount") == null ? 0 : ((Number)invoiceSummaryMap.get("amount")).longValue(); - long receiveAmount = receiveSummaryMap.get("amount") == null ? 0 : ((Number)receiveSummaryMap.get("amount")).longValue(); - long returnAmount = returnSummaryMap.get("amount") == null ? 0 : ((Number)returnSummaryMap.get("amount")).longValue(); - long outTotalAmount = invoiceAmount + receiveAmount + returnAmount; - - /*logger.debug("##########externalCode=" + goodsPlanSummaryMap.get("externalCode") + ",goodsPlanAmount=" +goodsPlanAmount+ - ",inTotalAmount=" +inTotalAmount+ ",outTotalAmount=" +outTotalAmount);*/ - if(goodsPlanAmount != 0 || inTotalAmount != 0 || outTotalAmount != 0){ - GoodsAmountSummaryByGrade vo = new GoodsAmountSummaryByGrade(); - vo.setExternalCode((String)goodsPlanSummaryMap.get("externalCode")); - vo.setName((String)goodsPlanSummaryMap.get("name")); - vo.setGoodsPurchaseAmount(goodsPlanAmount); - vo.setInStorageAmount(inTotalAmount); - vo.setOutStorageAmount(outTotalAmount); - returnList.add(vo); - } - } - } - - return returnList; + return goodsAmountSummaryByGradeHelper.getGoodsAmountSummaryByGrade(timeStart, timeEnd, goodsName, grade, orgUnitName); } - /** - * 根据汇总语句生成到集合中 - * @param summarySql - * @return - */ - private List> getGoodsAmountSummaryByGradeMap(String summarySql){ -// logger.debug("getGoodsAmountSummaryByGradeMap:summarySql=" + summarySql); - List> list = new ArrayList>(); - ResultSet rs = null; - try{ - rs = objectDao.executeSql(summarySql); - while(rs.next()){ - Map map = new HashMap(); - map.put("externalCode", rs.getString("externalCode")); - map.put("name", rs.getString("name")); - map.put("amount", rs.getObject("amount")); - list.add(map); - } - }catch(Exception e){ - e.printStackTrace(); - } - return list; - } - private void addEntryItem(List vos,String supplierName,Long amout,Double cost){ if(vos == null){ return; @@ -13220,285 +12949,14 @@ @Override public List queryForeignTousseApplicationSummaryReport( String startDay, String endDay, String companyName, String foreignTousseName, Long chargingModeId, Long specificationId) { - List list = new LinkedList(); - ResultSet rs = null; - List firstColumnVos = new ArrayList(); - Map firstColumnVoMap = new HashMap(); - try { - rs = objectDao.executeSql(getForeignTousseApplicationSummaryReportSql(startDay, endDay, companyName, foreignTousseName, chargingModeId, specificationId)); - while (rs.next()) { - int amount = rs.getInt("amount"); - if(amount == 0){ - continue; - } - ForeignTousseAppSumVO foreignTousseAppSumVO = new ForeignTousseAppSumVO(rs.getDouble("price"), amount, rs.getDouble("totalPrice")); - String firstColumnName = rs.getString("supplierName"); - String secondColumnName = rs.getString("foreignTousseChargeModeName"); - String thirdColumnName =rs.getString("specification"); - ForeignTousseAppSumVO vo = new ForeignTousseAppSumVO(); - if(StringUtils.isBlank(firstColumnName)){ - firstColumnName = null; - } - if(StringUtils.isBlank(secondColumnName)){ - secondColumnName = null; - } - FirstColumnVo firstColumnVo = null; - SecondColumnVo secondColumnVo = null; - ThirdColumnVo thirdColumnVo = null; - List secondColumnVos = null; - List thirdColumnVos = null; - List foreignTousseAppDetailedVOs = null; - if(firstColumnVoMap.containsKey(firstColumnName)){ - firstColumnVo = firstColumnVoMap.get(firstColumnName); - secondColumnVos = firstColumnVo.getFirstColumnDetaileds(); - }else{ - firstColumnVoMap.clear();//因为是按第一列的值排序的， 所以找不到就说明是新的列值，旧的数据可以清掉了 - firstColumnVo = new FirstColumnVo(); - firstColumnVo.setFirstColumnName(firstColumnName); - secondColumnVos = new ArrayList(); - firstColumnVo.setFirstColumnDetaileds(secondColumnVos); - firstColumnVoMap.put(firstColumnName, firstColumnVo); - firstColumnVos.add(firstColumnVo); - } - if(!CollectionUtils.isEmpty(secondColumnVos)){ - for (SecondColumnVo findSecondColumnVo : secondColumnVos) { - if(StringUtils.equals(findSecondColumnVo.getSecondColumnName(), secondColumnName)){ - secondColumnVo = findSecondColumnVo; - break; - } - } - } - if(secondColumnVo == null){ - secondColumnVo = new SecondColumnVo(); - secondColumnVo.setSecondColumnName(secondColumnName); - secondColumnVos.add(secondColumnVo); - thirdColumnVos = new ArrayList(); - secondColumnVo.setSecondColumnDetaileds(thirdColumnVos); - }else{ - thirdColumnVos = secondColumnVo.getSecondColumnDetaileds(); - } - if(!CollectionUtils.isEmpty(thirdColumnVos)){ - for (ThirdColumnVo findThirdColumnVo : thirdColumnVos) { - if(StringUtils.equals(findThirdColumnVo.getThirdColumnName(), thirdColumnName)){ - thirdColumnVo = findThirdColumnVo; - break; - } - } - } - if(thirdColumnVo == null){ - thirdColumnVo = new ThirdColumnVo(); - thirdColumnVo.setThirdColumnName(thirdColumnName); - thirdColumnVos.add(thirdColumnVo); - foreignTousseAppDetailedVOs = new ArrayList(); - thirdColumnVo.setThirdColumnDetaileds(foreignTousseAppDetailedVOs); - } - thirdColumnVo.getThirdColumnDetaileds().add(foreignTousseAppSumVO); - firstColumnVo.setFirstColumnTotalAmount(MathTools.add(foreignTousseAppSumVO.getAmount(), firstColumnVo.getFirstColumnTotalAmount()).intValue()); - firstColumnVo.setFirstColumnTotalPrice(MathTools.add(foreignTousseAppSumVO.getTotalPrice(), firstColumnVo.getFirstColumnTotalPrice()).doubleValue()); - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - return firstColumnVos; + return foreignTousseSupplierChargeReportHelper.queryForeignTousseApplicationSummaryReport(startDay, endDay, companyName, foreignTousseName, chargingModeId, specificationId); } - /** - * 查询外来器械包供应商收费汇总报表的sql - * @param startDay - * @param endDay - * @param companyName 供应商 - * @param chargingModeId 收费模式id - * @param specificationId 收费规格id - * @return - */ - private String getForeignTousseApplicationSummaryReportSql(String startDay, String endDay, String companyName, String foreignTousseName, Long chargingModeId, Long specificationId){ - if (StringUtils.isNotBlank(endDay)) { - endDay += ":59"; - } - if (StringUtils.isNotBlank(startDay)) { - startDay += ":00"; - } - String sql = "select tempt.supplierName,tempt.foreignTousseChargeModeName,tempt.specification,tempt.price,sum(tempt.amount) amount,sum(tempt.totalPrice) totalPrice from (select cs.supplierName,csmi.foreignTousseChargeModeName,case when csmi.chargeMode='" - + ForeignTousseChargeMode.MODE_RATE - + "' then cssi.foreignTousseSpecName else null end specification,case when csmi.chargeMode='" - + ForeignTousseChargeMode.MODE_RATE - + "' then cssi.price else csmi.fixedPrice end price,case when csmi.chargeMode='" - + ForeignTousseChargeMode.MODE_RATE - + "' then cssi.amount else csmi.fixedAmount end amount,case when csmi.chargeMode='" - + ForeignTousseChargeMode.MODE_RATE - + "' then cssi.totalPrice else csmi.fixedTotalPrice end totalPrice from " - + ChargeSetting.class.getSimpleName() - + " cs join " - + InvoicePlan.class.getSimpleName() - + " ip on ip.id=cs.invoicePlanID join " - + TousseDefinition.class.getSimpleName() - + " td on td.id=cs.tousseDefinitionID left join " - + ChargeSettingModeItem.class.getSimpleName() - + " csmi on csmi.setting_id=cs.id " - + " left join " - + ChargeSettingSpecItem.class.getSimpleName() - + " cssi on cssi.setting_mode_id=csmi.id where (ip.recyclingStatus is null or ip.recyclingStatus<>'"+ InvoicePlan.STATUS_END +"') and ip.applicationTime between " - + dateQueryAdapter.dateAdapter(startDay) - + " and " - + dateQueryAdapter.dateAdapter(endDay); - if(StringUtils.isNotBlank(companyName)){ - sql += " and cs.supplierName ='" + companyName + "' "; - } - if(StringUtils.isNotBlank(foreignTousseName)){ - sql += " and td.name ='" + foreignTousseName + "' "; - } - if(DatabaseUtil.isPoIdValid(chargingModeId)){ - sql += " and csmi.foreignTousseChargeModeID ='" + chargingModeId + "' "; - } - if(DatabaseUtil.isPoIdValid(specificationId)){ - sql += " and cssi.foreignTousseSpecID ='" + specificationId + "' "; - } - sql += " ) tempt left join "+ ForeignTousseSpecification.class.getSimpleName() +" fts on fts.name=tempt.specification where tempt.amount>0 group by tempt.supplierName,tempt.foreignTousseChargeModeName,tempt.specification,fts.id,tempt.price order by tempt.supplierName, tempt.foreignTousseChargeModeName,fts.id,tempt.price "; - return sql; - } @Override public List queryForeignTousseApplicationDetailedReport( String startDay, String endDay, String companyName, String foreignTousseName, Long chargingModeId, Long specificationId) { - List list = new LinkedList(); - - ResultSet rs = null; - List firstColumnVos = new ArrayList(); - Map firstColumnVoMap = new HashMap(); - try { - rs = objectDao.executeSql(getForeignTousseApplicationDetailedReportSql(startDay, endDay, companyName, foreignTousseName, chargingModeId, specificationId)); - while (rs.next()) { - int amount = rs.getInt("amount"); - if(amount == 0){ - continue; - } - ForeignTousseAppDetailedVO foreignTousseAppDetailedVO = new ForeignTousseAppDetailedVO(rs.getString("specification"), rs.getDouble("price"), amount, rs.getDouble("totalPrice"), rs.getString("serialNumber"), rs.getTimestamp("applicationTime")); - String firstColumnName = rs.getString("supplierName"); - String secondColumnName = rs.getString("name"); - String thirdColumnName =rs.getString("foreignTousseChargeModeName"); - //ForeignTousseAppSumVO vo = new ForeignTousseAppSumVO(); - if(StringUtils.isBlank(firstColumnName)){ - firstColumnName = null; - } - if(StringUtils.isBlank(secondColumnName)){ - secondColumnName = null; - } - FirstColumnVo firstColumnVo = null; - SecondColumnVo secondColumnVo = null; - ThirdColumnVo thirdColumnVo = null; - List secondColumnVos = null; - List thirdColumnVos = null; - List foreignTousseAppDetailedVOs = null; - - if(firstColumnVoMap.containsKey(firstColumnName)){ - firstColumnVo = firstColumnVoMap.get(firstColumnName); - secondColumnVos = firstColumnVo.getFirstColumnDetaileds(); - }else{ - firstColumnVoMap.clear();//因为是按第一列的值排序的， 所以找不到就说明是新的列值，旧的数据可以清掉了 - firstColumnVo = new FirstColumnVo(); - firstColumnVo.setFirstColumnName(firstColumnName); - secondColumnVos = new ArrayList(); - firstColumnVo.setFirstColumnDetaileds(secondColumnVos); - firstColumnVoMap.put(firstColumnName, firstColumnVo); - firstColumnVos.add(firstColumnVo); - } - if(!CollectionUtils.isEmpty(secondColumnVos)){ - for (SecondColumnVo findSecondColumnVo : secondColumnVos) { - if(StringUtils.equals(findSecondColumnVo.getSecondColumnName(), secondColumnName)){ - secondColumnVo = findSecondColumnVo; - break; - } - } - } - if(secondColumnVo == null){ - secondColumnVo = new SecondColumnVo(); - secondColumnVo.setSecondColumnName(secondColumnName); - secondColumnVos.add(secondColumnVo); - thirdColumnVos = new ArrayList(); - secondColumnVo.setSecondColumnDetaileds(thirdColumnVos); - }else{ - thirdColumnVos = secondColumnVo.getSecondColumnDetaileds(); - } - if(!CollectionUtils.isEmpty(thirdColumnVos)){ - for (ThirdColumnVo findThirdColumnVo : thirdColumnVos) { - if(StringUtils.equals(findThirdColumnVo.getThirdColumnName(), thirdColumnName)){ - thirdColumnVo = findThirdColumnVo; - break; - } - } - } - if(thirdColumnVo == null){ - thirdColumnVo = new ThirdColumnVo(); - thirdColumnVo.setThirdColumnName(thirdColumnName); - thirdColumnVos.add(thirdColumnVo); - foreignTousseAppDetailedVOs = new ArrayList(); - thirdColumnVo.setThirdColumnDetaileds(foreignTousseAppDetailedVOs); - } - thirdColumnVo.getThirdColumnDetaileds().add(foreignTousseAppDetailedVO); - firstColumnVo.setFirstColumnTotalAmount(MathTools.add(foreignTousseAppDetailedVO.getAmount(), firstColumnVo.getFirstColumnTotalAmount()).intValue()); - firstColumnVo.setFirstColumnTotalPrice(MathTools.add(foreignTousseAppDetailedVO.getTotalPrice(), firstColumnVo.getFirstColumnTotalPrice()).doubleValue()); - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - return firstColumnVos; + return foreignTousseSupplierChargeReportHelper.queryForeignTousseApplicationDetailedReport(startDay, endDay, companyName, foreignTousseName, chargingModeId, specificationId); } - /** - * 查询外来器械包供应商收费明细报表的sql - * @param startDay - * @param endDay - * @param companyName 供应商 - * @param foreignTousseName 外来器械包名称 - * @param chargingModeId 收费模式id - * @param specificationId 收费规格id - * @return - */ - private String getForeignTousseApplicationDetailedReportSql(String startDay, String endDay, String companyName, String foreignTousseName, Long chargingModeId, Long specificationId){ - if (StringUtils.isNotBlank(endDay)) { - endDay += ":59"; - } - if (StringUtils.isNotBlank(startDay)) { - startDay += ":00"; - } - String sql = "select * from (select cs.supplierName,td.name,csmi.foreignTousseChargeModeName,case when csmi.chargeMode='" - + ForeignTousseChargeMode.MODE_RATE - + "' then cssi.foreignTousseSpecName else null end specification,case when csmi.chargeMode='" - + ForeignTousseChargeMode.MODE_RATE - + "' then cssi.price else csmi.fixedPrice end price,case when csmi.chargeMode='" - + ForeignTousseChargeMode.MODE_RATE - + "' then cssi.amount else csmi.fixedAmount end amount,case when csmi.chargeMode='" - + ForeignTousseChargeMode.MODE_RATE - + "' then cssi.totalPrice else csmi.fixedTotalPrice end totalPrice,ip.serialNumber,ip.applicationTime from " - + ChargeSetting.class.getSimpleName() - + " cs join " - + InvoicePlan.class.getSimpleName() - + " ip on ip.id=cs.invoicePlanID join " - + TousseDefinition.class.getSimpleName() - + " td on td.id=cs.tousseDefinitionID left join " - + ChargeSettingModeItem.class.getSimpleName() - + " csmi on csmi.setting_id=cs.id left join " - + ChargeSettingSpecItem.class.getSimpleName() - + " cssi on cssi.setting_mode_id=csmi.id where (ip.recyclingStatus is null or ip.recyclingStatus<>'"+ InvoicePlan.STATUS_END +"') and ip.applicationTime between " - + dateQueryAdapter.dateAdapter(startDay) - + " and " - + dateQueryAdapter.dateAdapter(endDay); - if(StringUtils.isNotBlank(companyName)){ - sql += " and cs.supplierName ='" + companyName + "' "; - } - if(StringUtils.isNotBlank(foreignTousseName)){ - sql += " and td.name ='" + foreignTousseName + "' "; - } - if(DatabaseUtil.isPoIdValid(chargingModeId)){ - sql += " and csmi.foreignTousseChargeModeID ='" + chargingModeId + "' "; - } - if(DatabaseUtil.isPoIdValid(specificationId)){ - sql += " and cssi.foreignTousseSpecID ='" + specificationId + "' "; - } - sql += ") tempt left join "+ ForeignTousseSpecification.class.getSimpleName() +" fts on fts.name=tempt.specification where tempt.amount>0 order by tempt.supplierName,tempt.name,tempt.foreignTousseChargeModeName,fts.id"; - return sql; - } + @Override public JSONObject getReportDataByReportName(String reportName) { if("materialTypeWorkloadReport".equals(reportName)){