Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/AmountContrastReportHelper.java =================================================================== diff -u -r35852 -r35932 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/AmountContrastReportHelper.java (.../AmountContrastReportHelper.java) (revision 35852) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/AmountContrastReportHelper.java (.../AmountContrastReportHelper.java) (revision 35932) @@ -1,25 +1,51 @@ 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 java.util.Map.Entry; +import net.sf.json.JSONObject; + 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.basedatamanager.reportoption.GoodsOptionManager; +import com.forgon.disinfectsystem.diposablegoods.service.GodownEntryItemManager; +import com.forgon.disinfectsystem.diposablegoods.util.DisposableGoodsUtils; +import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoods; +import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoodsBatchStock; +import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoodsStock; +import com.forgon.disinfectsystem.entity.assestmanagement.GodownEntry; +import com.forgon.disinfectsystem.entity.assestmanagement.GodownEntryItem; import com.forgon.disinfectsystem.entity.basedatamanager.reportoption.GoodsOption; import com.forgon.disinfectsystem.entity.invoicemanager.DiposableGoodsItem; +import com.forgon.disinfectsystem.entity.packing.PackingRecord; +import com.forgon.disinfectsystem.entity.packing.PackingRecordOutItem; +import com.forgon.disinfectsystem.entity.receiverecord.ReceiveRecord; +import com.forgon.disinfectsystem.entity.receiverecord.ReceiveRecordItem; +import com.forgon.disinfectsystem.entity.returngoodsrecord.ReturnGoodsItem; +import com.forgon.disinfectsystem.entity.returngoodsrecord.ReturnGoodsRecord; +import com.forgon.disinfectsystem.entity.stocktakerecordmanager.MonthGoodsDetailVo; +import com.forgon.disinfectsystem.entity.stocktakerecordmanager.StockDiposableGoodBatch; +import com.forgon.disinfectsystem.entity.stocktakerecordmanager.StockDiposableGoods; +import com.forgon.disinfectsystem.entity.stocktakerecordmanager.StockIdentificationOfGoods; +import com.forgon.disinfectsystem.entity.stocktakerecordmanager.StockTakeRecord; import com.forgon.disinfectsystem.jasperreports.javabeansource.InvoiceAmountContrastBean; +import com.forgon.disinfectsystem.jasperreports.service.MonthGoodsDetailSummaryService; +import com.forgon.tools.MathTools; +import com.forgon.tools.db.DatabaseUtil; import com.forgon.tools.hibernate.ObjectDao; import com.forgon.tools.util.SqlUtils; /** * 一次性物品发货数量对比报表 + * 一次性物品结算 */ @Component public class AmountContrastReportHelper { @@ -29,6 +55,8 @@ private DateQueryAdapter dateQueryAdapter; @Autowired private ObjectDao objectDao; + @Autowired + private GodownEntryItemManager godownEntryItemManager; /** * 获取一次性物品发货数量对比报表数据 * @param startMonth @@ -190,5 +218,244 @@ return bean; } - + /** + * 一次性物品结算数据查询 + * @param manualInIncludeSyncIn 手工入库是否包含同步入库(针对中医药附三要分开的模板) + * @param startRecord + * @param endRecord + * @param type + * @param summaryType + * @return + */ + public List getStockTakeList( + StockTakeRecord startRecord, StockTakeRecord endRecord, + String type, String summaryType, boolean manualInIncludeSyncIn) { + if ("汇总".equals(summaryType)) { + return createGoodsSummary(endRecord, startRecord, type, manualInIncludeSyncIn); + } else { + return createGoodsDetail(endRecord, startRecord, type, manualInIncludeSyncIn); + } + } + public List createGoodsSummary(StockTakeRecord currentRecord,StockTakeRecord lastMonthRecord,String type, boolean manualInIncludeSyncIn){ + List list = createGoodsDetail(currentRecord,lastMonthRecord,type, manualInIncludeSyncIn); + MonthGoodsDetailVo vo = new MonthGoodsDetailVo(); + for(MonthGoodsDetailVo item : list){ + vo.setStartStockPrice(MathTools.add(vo.getStartStockPrice(), item.getStartStockPrice()).doubleValue()); + vo.setEndStockPrice(MathTools.add(vo.getEndStockPrice(), item.getEndStockPrice()).doubleValue()); + vo.setInStoragePrice(MathTools.add(vo.getInStoragePrice(), item.getInStoragePrice()).doubleValue()); + vo.setOutStoragePrice(MathTools.add(vo.getOutStoragePrice(), item.getOutStoragePrice()).doubleValue()); + + vo.setBuyInStoragePrice(MathTools.add(vo.getBuyInStoragePrice(),item.getBuyInStoragePrice()).doubleValue()); + vo.setMoveInStoragePrice(MathTools.add(vo.getMoveInStoragePrice(),item.getMoveInStoragePrice()).doubleValue()); + vo.setStockTakeInStoragePrice(MathTools.add(vo.getStockTakeInStoragePrice(),item.getStockTakeInStoragePrice()).doubleValue()); + vo.setReturnBackToSupplierStoragePrice(MathTools.add(vo.getReturnBackToSupplierStoragePrice(),item.getReturnBackToSupplierStoragePrice()).doubleValue()); + vo.setSendOutStoragePrice(MathTools.add(vo.getSendOutStoragePrice(),item.getSendOutStoragePrice()).doubleValue()); + vo.setReceiveRecordStoragePrice(MathTools.add(vo.getReceiveRecordStoragePrice(),item.getReceiveRecordStoragePrice()).doubleValue()); + vo.setMoveOutStoragePrice(MathTools.add(vo.getMoveOutStoragePrice(),item.getMoveOutStoragePrice()).doubleValue()); + vo.setStockTakeOutStoragePrice(MathTools.add(vo.getStockTakeOutStoragePrice(),item.getStockTakeOutStoragePrice()).doubleValue()); + vo.setDepartmentReturnStoragePrice(MathTools.add(vo.getDepartmentReturnStoragePrice(),item.getDepartmentReturnStoragePrice()).doubleValue()); + } + List retList = new ArrayList(); + retList.add(vo); + return retList; + } + public List createGoodsDetail(StockTakeRecord currentRecord,StockTakeRecord lastMonthRecord,String type, boolean manualInIncludeSyncIn){ + //List voList = new ArrayList(); + MonthGoodsDetailSummaryService service = new MonthGoodsDetailSummaryService(); + if(currentRecord != null){ + //入库单及出库单 + String startDate = ""; + if(lastMonthRecord != null){ + startDate = dateQueryAdapter.dateAdapter(lastMonthRecord.getStockTakeDate()); + } + String endDate = dateQueryAdapter.dateAdapter(currentRecord.getStockTakeDate()); + //查出该时间内各一次性物品最后入库的供应商 + Map disposableGoodsLastEntrySupplierMap = + godownEntryItemManager.getDisposableGoodsLastEntrySupplierMap(startDate,endDate); + + //计算期末 + calcStockTakeRecord(currentRecord, type, service,false , disposableGoodsLastEntrySupplierMap); + //计算期初 + calcStockTakeRecord(lastMonthRecord, type, service,true , disposableGoodsLastEntrySupplierMap); + + String sql = "select d.name,d.specification,i.amount,i.cost,r.type,r.subType from " + + GodownEntry.class.getSimpleName() + " r inner join " + GodownEntryItem.class.getSimpleName() + + " i on r.id = i.godownEntry_id" + + " inner join " + DisposableGoodsStock.class.getSimpleName() + + " d on i.diposableGoods_id = d.id" + + " where r.warehouseID=" + currentRecord.getWarehouseId(); + + if(StringUtils.isBlank(startDate)){ + sql += " and r.time < " + endDate + ""; + }else{ + sql += " and r.time >= " + startDate + " and r.time < " + endDate + " "; + } + if(StringUtils.isNotBlank(type)){ + sql += " and d.type = '" + type + "' "; + } + + //一次性物品发货单 + String diposableSql = "select dg.name,dg.specification,bo.amount,bo.price as cost," + + "'" + GodownEntry.TYPE_OUT + "' as type,'发货出库' as subType" + + " from Invoice po,DiposableGoodsItem bo,DisposableGoods dg " + + "where po.id = bo.invoice_id and bo.disposableGoodsID = dg.id " + + " and po.sourceWarehouseId=" + currentRecord.getWarehouseId(); + + if(StringUtils.isBlank(startDate)){ + diposableSql += " and po.sendTime < " + endDate; + }else{ + diposableSql += " and po.sendTime >= " + startDate + " and po.sendTime < " + endDate; + } + if(StringUtils.isNotBlank(type)){ + diposableSql += " and dg.type = '" + type + "' "; + } + + //供应室领用 + String supplyRoomDiposableGoodsSql = " select dg.name,dg.specification,rri.amount,rri.price as cost," + + "'" + GodownEntry.TYPE_OUT + "' as type,'供应室领用' as subType" + +" from " + ReceiveRecord.class.getSimpleName() + " rr left join " + + ReceiveRecordItem.class.getSimpleName() + " rri on rr.id = rri.receiveRecord_id" + //+ " left join " + DisposableGoodsBatchStock.class.getSimpleName() + " dbs on rri.diposableGoodBatchStock_id = dbs.id" + +" left join " + DisposableGoods.class.getSimpleName() + " dg on dg.id=rri.disposableGoodsId" + + " where rri.type='一次性物品' and rr.warehouseId="+currentRecord.getWarehouseId(); + if(StringUtils.isBlank(startDate)){ + supplyRoomDiposableGoodsSql += " and rr.time < " + endDate; + }else{ + supplyRoomDiposableGoodsSql += " and rr.time >= " + startDate + " and rr.time < " + endDate; + } + if(StringUtils.isNotBlank(type)){ + supplyRoomDiposableGoodsSql += " and dg.type = '" + type + "' "; + } + //装配扣减,暂时算到供应室领用 + String packingRecordUsedSql = " select dg.name,dg.specification,dgi.amount,dgi.price as cost," + + "'" + GodownEntry.TYPE_OUT + "' as type,'供应室领用' as subType" + +" from " + PackingRecord.class.getSimpleName() + " pr left join " + + PackingRecordOutItem.class.getSimpleName() + " pri on pr.id = pri.packingRecordId" + +" left join " + DisposableGoods.class.getSimpleName() + " dg on dg.id=pri.disposableGoodsId" + +" left join " + DiposableGoodsItem.class.getSimpleName() + " dgi on dgi.packingRecordOutItemId=pri.id" + +" left join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on dgbs.id=dgi.disposableGoodsBatchStockID" + + " where 1=1 and dgbs.warehouseID="+currentRecord.getWarehouseId(); + if(StringUtils.isBlank(startDate)){ + packingRecordUsedSql += " and pr.packTime < " + endDate; + }else{ + packingRecordUsedSql += " and pr.packTime >= " + startDate + " and pr.packTime < " + endDate; + } + if(StringUtils.isNotBlank(type)){ + packingRecordUsedSql += " and dg.type = '" + type + "' "; + } + //科室退货 + String returnGoodsSql = "select dg.name,dg.specification,i.amount,i.price as cost," + + "'" + GodownEntry.TYPE_IN + "' as type,'科室退货' as subType from " + + ReturnGoodsItem.class.getSimpleName() + " i inner join " + + ReturnGoodsRecord.class.getSimpleName() + " r on r.id = i.returnGoodsRecord_ID " + + " inner join " + DisposableGoods.class.getSimpleName() + " dg on dg.id=i.disposableGoodsID" + + " where r.type='一次性物品' and r.warehouseID=" + currentRecord.getWarehouseId(); + if(StringUtils.isBlank(startDate)){ + returnGoodsSql += " and r.returnTime < " + endDate; + }else{ + returnGoodsSql += " and r.returnTime >= " + startDate + " and r.returnTime < " + endDate; + } + if(StringUtils.isNotBlank(type)){ + returnGoodsSql += " and dg.type = '" + type + "' "; + } + sql += " union all " + diposableSql; + sql += " union all " + supplyRoomDiposableGoodsSql; + sql += " union all " + packingRecordUsedSql; + sql += " union all " + returnGoodsSql; + ResultSet rs = null; + try { + rs = objectDao.executeSql(sql); + while(rs.next()){ + JSONObject item = new JSONObject(); + String name = rs.getString("name"); + String specification = rs.getString("specification"); + item.put("name", name); + item.put("specification", specification); + item.put("amount",rs.getLong("amount")); + item.put("cost",rs.getDouble("cost")); + item.put("type", rs.getString("type")); + item.put("subType", rs.getString("subType")); + String fullName = DisposableGoodsUtils.getDisposableGoodsFullName(name, specification); + if(disposableGoodsLastEntrySupplierMap != null){ + String lastEntrySupplier = disposableGoodsLastEntrySupplierMap.get(fullName); + if(StringUtils.isNotBlank(lastEntrySupplier)){ + item.put("lastEntrySupplier", lastEntrySupplier); + } + } + service.addItem(item , manualInIncludeSyncIn); + } + } catch (SQLException e) { + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + return service.getList(); + //return voList; + } + /** + * 计算期初或者期末余数 + * @param currentRecord + * @param type + * @param service + * @param isStart 计算期初或者是期末 + */ + private void calcStockTakeRecord(StockTakeRecord stockTakeRecord, + String type, MonthGoodsDetailSummaryService service,boolean isStart,Map disposableGoodsLastEntrySupplierMap) { + if(stockTakeRecord == null){ + return; + } + String typesql = ""; + if(StringUtils.isNotBlank(type)){ + typesql = " and po.type = '" + type + "' "; + } + + //一次性物品盘点的id与总价,数量的中间结果 + String idStartPrice = "select g.id,sum(i.amount*i.price) as price,sum(i.amount) as amount from " + StockDiposableGoods.class.getSimpleName() + " g" + + " left join " + StockDiposableGoodBatch.class.getSimpleName() + " b" + + " on g.id=b.diposablegoods_id" + + " left join " + StockIdentificationOfGoods.class.getSimpleName() + " i" + + " on b.id=i.batch_id group by g.id"; + //计算期末/期末余数 + String stockDiposableGoodsSql = "select po.externalCode,po.name,po.unit,po.type,po.specification, " + + " m.price as price ,m.amount as amout " + + " from " + StockDiposableGoods.class.getSimpleName() + " po" + + " inner join " + StockTakeRecord.class.getSimpleName() + " r on r.id=po.takeRecord_id" + + " inner join ( " + idStartPrice + " ) m" + + " on m.id=po.id" + + " where r.id=" + stockTakeRecord.getId() + typesql + + " order by po.typeSequence,po.externalCode asc"; + + ResultSet rs = objectDao.executeSql(stockDiposableGoodsSql); + try { + while(rs.next()){ + JSONObject item = new JSONObject(); + item.put("externalCode", rs.getString("externalCode")); + String name = rs.getString("name"); + String specification = rs.getString("specification"); + item.put("name", name); + item.put("specification", specification); + String fullName = DisposableGoodsUtils.getDisposableGoodsFullName(name, specification); + if(disposableGoodsLastEntrySupplierMap != null){ + String lastEntrySupplier = disposableGoodsLastEntrySupplierMap.get(fullName); + if(StringUtils.isNotBlank(lastEntrySupplier)){ + item.put("lastEntrySupplier", lastEntrySupplier); + } + } + item.put("unit",rs.getString("unit")); + item.put("type",rs.getString("type")); + item.put("amout", rs.getLong("amout")); + item.put("price", rs.getDouble("price")); + if(isStart){ + service.addStartItem(item); + }else{ + service.addEndItem(item); + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally{ + DatabaseUtil.closeResultSetAndStatement(rs); + } + } } Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35931 -r35932 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35931) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35932) @@ -1859,267 +1859,8 @@ public List getStockTakeList( StockTakeRecord startRecord, StockTakeRecord endRecord, String type, String summaryType, boolean manualInIncludeSyncIn) { - if ("汇总".equals(summaryType)) { - return createGoodsSummary(endRecord, startRecord, type, manualInIncludeSyncIn); - } else { - return createGoodsDetail(endRecord, startRecord, type, manualInIncludeSyncIn); - } + return amountContrastReportHelper.getStockTakeList(startRecord, endRecord, type, summaryType, manualInIncludeSyncIn); } - - public List createGoodsSummary(StockTakeRecord currentRecord,StockTakeRecord lastMonthRecord,String type, boolean manualInIncludeSyncIn){ - List list = createGoodsDetail(currentRecord,lastMonthRecord,type, manualInIncludeSyncIn); - MonthGoodsDetailVo vo = new MonthGoodsDetailVo(); - for(MonthGoodsDetailVo item : list){ - vo.setStartStockPrice(MathTools.add(vo.getStartStockPrice(), item.getStartStockPrice()).doubleValue()); - vo.setEndStockPrice(MathTools.add(vo.getEndStockPrice(), item.getEndStockPrice()).doubleValue()); - vo.setInStoragePrice(MathTools.add(vo.getInStoragePrice(), item.getInStoragePrice()).doubleValue()); - vo.setOutStoragePrice(MathTools.add(vo.getOutStoragePrice(), item.getOutStoragePrice()).doubleValue()); - - vo.setBuyInStoragePrice(MathTools.add(vo.getBuyInStoragePrice(),item.getBuyInStoragePrice()).doubleValue()); - vo.setMoveInStoragePrice(MathTools.add(vo.getMoveInStoragePrice(),item.getMoveInStoragePrice()).doubleValue()); - vo.setStockTakeInStoragePrice(MathTools.add(vo.getStockTakeInStoragePrice(),item.getStockTakeInStoragePrice()).doubleValue()); - vo.setReturnBackToSupplierStoragePrice(MathTools.add(vo.getReturnBackToSupplierStoragePrice(),item.getReturnBackToSupplierStoragePrice()).doubleValue()); - vo.setSendOutStoragePrice(MathTools.add(vo.getSendOutStoragePrice(),item.getSendOutStoragePrice()).doubleValue()); - vo.setReceiveRecordStoragePrice(MathTools.add(vo.getReceiveRecordStoragePrice(),item.getReceiveRecordStoragePrice()).doubleValue()); - vo.setMoveOutStoragePrice(MathTools.add(vo.getMoveOutStoragePrice(),item.getMoveOutStoragePrice()).doubleValue()); - vo.setStockTakeOutStoragePrice(MathTools.add(vo.getStockTakeOutStoragePrice(),item.getStockTakeOutStoragePrice()).doubleValue()); - vo.setDepartmentReturnStoragePrice(MathTools.add(vo.getDepartmentReturnStoragePrice(),item.getDepartmentReturnStoragePrice()).doubleValue()); - } - List retList = new ArrayList(); - retList.add(vo); - return retList; - } - public List createGoodsDetail(StockTakeRecord currentRecord,StockTakeRecord lastMonthRecord,String type, boolean manualInIncludeSyncIn){ - //List voList = new ArrayList(); - MonthGoodsDetailSummaryService service = new MonthGoodsDetailSummaryService(); - if(currentRecord != null){ - //入库单及出库单 - String startDate = ""; - if(lastMonthRecord != null){ - startDate = dateQueryAdapter.dateAdapter(lastMonthRecord.getStockTakeDate()); - } - String endDate = dateQueryAdapter.dateAdapter(currentRecord.getStockTakeDate()); - //查出该时间内各一次性物品最后入库的供应商 - Map disposableGoodsLastEntrySupplierMap = - godownEntryItemManager.getDisposableGoodsLastEntrySupplierMap(startDate,endDate); - - //计算期末 - calcStockTakeRecord(currentRecord, type, service,false , disposableGoodsLastEntrySupplierMap); - //计算期初 - calcStockTakeRecord(lastMonthRecord, type, service,true , disposableGoodsLastEntrySupplierMap); - - String sql = "select d.name,d.specification,i.amount,i.cost,r.type,r.subType from " - + GodownEntry.class.getSimpleName() + " r inner join " + GodownEntryItem.class.getSimpleName() - + " i on r.id = i.godownEntry_id" - + " inner join " + DisposableGoodsStock.class.getSimpleName() - + " d on i.diposableGoods_id = d.id" - + " where r.warehouseID=" + currentRecord.getWarehouseId(); - - if(StringUtils.isBlank(startDate)){ - sql += " and r.time < " + endDate + ""; - }else{ - sql += " and r.time >= " + startDate + " and r.time < " + endDate + " "; - } - if(StringUtils.isNotBlank(type)){ - sql += " and d.type = '" + type + "' "; - } - - //一次性物品发货单 - String diposableSql = "select dg.name,dg.specification,bo.amount,bo.price as cost," - + "'" + GodownEntry.TYPE_OUT + "' as type,'发货出库' as subType" - + " from Invoice po,DiposableGoodsItem bo,DisposableGoods dg " - + "where po.id = bo.invoice_id and bo.disposableGoodsID = dg.id " - + " and po.sourceWarehouseId=" + currentRecord.getWarehouseId(); - - if(StringUtils.isBlank(startDate)){ - diposableSql += " and po.sendTime < " + endDate; - }else{ - diposableSql += " and po.sendTime >= " + startDate + " and po.sendTime < " + endDate; - } - if(StringUtils.isNotBlank(type)){ - diposableSql += " and dg.type = '" + type + "' "; - } - - //供应室领用 - String supplyRoomDiposableGoodsSql = " select dg.name,dg.specification,rri.amount,rri.price as cost," - + "'" + GodownEntry.TYPE_OUT + "' as type,'供应室领用' as subType" - +" from " + ReceiveRecord.class.getSimpleName() + " rr left join " - + ReceiveRecordItem.class.getSimpleName() + " rri on rr.id = rri.receiveRecord_id" - //+ " left join " + DisposableGoodsBatchStock.class.getSimpleName() + " dbs on rri.diposableGoodBatchStock_id = dbs.id" - +" left join " + DisposableGoods.class.getSimpleName() + " dg on dg.id=rri.disposableGoodsId" - + " where rri.type='一次性物品' and rr.warehouseId="+currentRecord.getWarehouseId(); - if(StringUtils.isBlank(startDate)){ - supplyRoomDiposableGoodsSql += " and rr.time < " + endDate; - }else{ - supplyRoomDiposableGoodsSql += " and rr.time >= " + startDate + " and rr.time < " + endDate; - } - if(StringUtils.isNotBlank(type)){ - supplyRoomDiposableGoodsSql += " and dg.type = '" + type + "' "; - } - //装配扣减,暂时算到供应室领用 - String packingRecordUsedSql = " select dg.name,dg.specification,dgi.amount,dgi.price as cost," - + "'" + GodownEntry.TYPE_OUT + "' as type,'供应室领用' as subType" - +" from " + PackingRecord.class.getSimpleName() + " pr left join " - + PackingRecordOutItem.class.getSimpleName() + " pri on pr.id = pri.packingRecordId" - +" left join " + DisposableGoods.class.getSimpleName() + " dg on dg.id=pri.disposableGoodsId" - +" left join " + DiposableGoodsItem.class.getSimpleName() + " dgi on dgi.packingRecordOutItemId=pri.id" - +" left join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on dgbs.id=dgi.disposableGoodsBatchStockID" - + " where 1=1 and dgbs.warehouseID="+currentRecord.getWarehouseId(); - if(StringUtils.isBlank(startDate)){ - packingRecordUsedSql += " and pr.packTime < " + endDate; - }else{ - packingRecordUsedSql += " and pr.packTime >= " + startDate + " and pr.packTime < " + endDate; - } - if(StringUtils.isNotBlank(type)){ - packingRecordUsedSql += " and dg.type = '" + type + "' "; - } - //科室退货 - String returnGoodsSql = "select dg.name,dg.specification,i.amount,i.price as cost," - + "'" + GodownEntry.TYPE_IN + "' as type,'科室退货' as subType from " - + ReturnGoodsItem.class.getSimpleName() + " i inner join " - + ReturnGoodsRecord.class.getSimpleName() + " r on r.id = i.returnGoodsRecord_ID " - + " inner join " + DisposableGoods.class.getSimpleName() + " dg on dg.id=i.disposableGoodsID" - + " where r.type='一次性物品' and r.warehouseID=" + currentRecord.getWarehouseId(); - if(StringUtils.isBlank(startDate)){ - returnGoodsSql += " and r.returnTime < " + endDate; - }else{ - returnGoodsSql += " and r.returnTime >= " + startDate + " and r.returnTime < " + endDate; - } - if(StringUtils.isNotBlank(type)){ - returnGoodsSql += " and dg.type = '" + type + "' "; - } - sql += " union all " + diposableSql; - sql += " union all " + supplyRoomDiposableGoodsSql; - sql += " union all " + packingRecordUsedSql; - sql += " union all " + returnGoodsSql; - ResultSet rs = null; - try { - rs = objectDao.executeSql(sql); - while(rs.next()){ - JSONObject item = new JSONObject(); - String name = rs.getString("name"); - String specification = rs.getString("specification"); - item.put("name", name); - item.put("specification", specification); - item.put("amount",rs.getLong("amount")); - item.put("cost",rs.getDouble("cost")); - item.put("type", rs.getString("type")); - item.put("subType", rs.getString("subType")); - String fullName = DisposableGoodsUtils.getDisposableGoodsFullName(name, specification); - if(disposableGoodsLastEntrySupplierMap != null){ - String lastEntrySupplier = disposableGoodsLastEntrySupplierMap.get(fullName); - if(StringUtils.isNotBlank(lastEntrySupplier)){ - item.put("lastEntrySupplier", lastEntrySupplier); - } - } - service.addItem(item , manualInIncludeSyncIn); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally{ - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - return service.getList(); - //return voList; - } - /** - * 计算期初或者期末余数 - * @param currentRecord - * @param type - * @param service - * @param isStart 计算期初或者是期末 - */ - private void calcStockTakeRecord(StockTakeRecord stockTakeRecord, - String type, MonthGoodsDetailSummaryService service,boolean isStart,Map disposableGoodsLastEntrySupplierMap) { - if(stockTakeRecord == null){ - return; - } - String typesql = ""; - if(StringUtils.isNotBlank(type)){ - typesql = " and po.type = '" + type + "' "; - } - - //一次性物品盘点的id与总价,数量的中间结果 - String idStartPrice = "select g.id,sum(i.amount*i.price) as price,sum(i.amount) as amount from " + StockDiposableGoods.class.getSimpleName() + " g" - + " left join " + StockDiposableGoodBatch.class.getSimpleName() + " b" - + " on g.id=b.diposablegoods_id" - + " left join " + StockIdentificationOfGoods.class.getSimpleName() + " i" - + " on b.id=i.batch_id group by g.id"; - //计算期末/期末余数 - String stockDiposableGoodsSql = "select po.externalCode,po.name,po.unit,po.type,po.specification, " - + " m.price as price ,m.amount as amout " - + " from " + StockDiposableGoods.class.getSimpleName() + " po" - + " inner join " + StockTakeRecord.class.getSimpleName() + " r on r.id=po.takeRecord_id" - + " inner join ( " + idStartPrice + " ) m" - + " on m.id=po.id" - + " where r.id=" + stockTakeRecord.getId() + typesql - + " order by po.typeSequence,po.externalCode asc"; - - ResultSet rs = objectDao.executeSql(stockDiposableGoodsSql); - try { - while(rs.next()){ - JSONObject item = new JSONObject(); - item.put("externalCode", rs.getString("externalCode")); - String name = rs.getString("name"); - String specification = rs.getString("specification"); - item.put("name", name); - item.put("specification", specification); - String fullName = DisposableGoodsUtils.getDisposableGoodsFullName(name, specification); - if(disposableGoodsLastEntrySupplierMap != null){ - String lastEntrySupplier = disposableGoodsLastEntrySupplierMap.get(fullName); - if(StringUtils.isNotBlank(lastEntrySupplier)){ - item.put("lastEntrySupplier", lastEntrySupplier); - } - } - item.put("unit",rs.getString("unit")); - item.put("type",rs.getString("type")); - item.put("amout", rs.getLong("amout")); - item.put("price", rs.getDouble("price")); - if(isStart){ - service.addStartItem(item); - }else{ - service.addEndItem(item); - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally{ - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - - @SuppressWarnings("unchecked") - public Map> getGodownEntryItemBySql(String sql){ - ResultSet rs = objectDao.executeSql(sql); - Map> map = new HashMap>(); - try { - while(rs.next()){ - GodownEntryItem item = new GodownEntryItem(); - item.setAmount(rs.getLong(3)); - item.setCost(rs.getDouble(4)); - String key = rs.getString(1); - if(StringUtils.isNotBlank(rs.getString(2))){ - key += "[" + rs.getString(2) + "]"; - } - List items = map.get(key); - if(items == null){ - items = new ArrayList(); - map.put(key, items); - } - items.add(item); - } - } catch (SQLException e) { - e.printStackTrace(); - }finally{ - DatabaseUtil.closeResultSetAndStatement(rs); - } - return map; - } - - public List getGoodsBySql(String sql){ - return objectDao.findBySql(StockDiposableGoods.class.getSimpleName(), sql); - } // 清洗区材料类型工作量统计报表 @Override public List findMaterialTypeWorkloadReportList(Set instrumentSetTypesSet, String month,String querySupplyRoom){