Index: ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/service/DisposableGoodsStorageRecordManager.java =================================================================== diff -u -r13020 -r30434 --- ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/service/DisposableGoodsStorageRecordManager.java (.../DisposableGoodsStorageRecordManager.java) (revision 13020) +++ ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/service/DisposableGoodsStorageRecordManager.java (.../DisposableGoodsStorageRecordManager.java) (revision 30434) @@ -1,5 +1,7 @@ package com.forgon.disinfectsystem.diposablegoods.service; +import javax.servlet.ServletOutputStream; + import net.sf.json.JSONObject; /** @@ -16,4 +18,40 @@ * @return */ public JSONObject getStorageRecord(String disposableGoodsId,String startDate,String endDate); + + /** + * 获取一次性物品出入库记录 + * @param disposableGoodsId 按物品名称（默认为空，代表全部物品） + * @param startDate 开时日期,可以为空或null,表示开始日期无限制 + * @param endDate 结束日期,可以为空或null,表示结束日期无限制 + * @param diposableGoodsType 物品类型（默认为空，代表全部类型） + * @param departCoding 按申领科室 + * @param settleAccountsDepartCoding 结算科室（默认为空，代表所有科室） + * @param invoiceDepartmentId 按发放科室分组（默认为空，代表所有） + * @param searchModel 查询方式（可选择“明细查询”、“汇总查询”）选择汇总查询，只显示某物品的出入库汇总，不显示批次号 + * @return + */ + public JSONObject getStorageRecordBySearchModel(String disposableGoodsId, String disposableGoodsName, + String startDate, String endDate, String diposableGoodsType, + String departCoding, String settleAccountsDepartCoding, + String invoiceDepartmentId, String searchModel); + + /** + * 导出一次性物品出入库记录 + * @param disposableGoodsId + * @param diposableGoodsName + * @param startDate + * @param endDate + * @param diposableGoodsType + * @param departCoding + * @param settleAccountsDepartCoding + * @param invoiceDepartmentId + * @param searchModel + * @param outputStream + */ + public void exportDisposableGoodsStorageRecord(String disposableGoodsId, + String diposableGoodsName, String startDate, String endDate, + String diposableGoodsType, String departCoding, + String settleAccountsDepartCoding, String invoiceDepartmentId, + String searchModel, ServletOutputStream outputStream); } Index: ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/action/DiposableGoodsAction.java =================================================================== diff -u -r29375 -r30434 --- ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/action/DiposableGoodsAction.java (.../DiposableGoodsAction.java) (revision 29375) +++ ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/action/DiposableGoodsAction.java (.../DiposableGoodsAction.java) (revision 30434) @@ -15,12 +15,12 @@ import java.util.regex.Matcher; import java.util.regex.Pattern; +import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import net.sf.json.JsonConfig; -import net.sf.json.util.JSONBuilder; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang.StringUtils; @@ -66,7 +66,6 @@ import com.forgon.tools.util.IntegerUtils; import com.forgon.tools.util.PageUtil; import com.forgon.tools.util.SqlUtils; -import com.google.gson.JsonObject; import com.opensymphony.xwork2.ModelDriven; import com.opensymphony.xwork2.Preparable; @@ -1449,14 +1448,78 @@ public void loadDisposableGoodsStorageRecord(){ String disposableGoodsId = StrutsParamUtils.getPraramValue("disposableGoodsId", null); String startDate = StrutsParamUtils.getPraramValue("startDate", null); - String endDate = StrutsParamUtils.getPraramValue("endData", null); - JSONObject jsonObject = disposableGoodsStorageRecordManager.getStorageRecord( - disposableGoodsId,startDate,endDate); + String endDate = StrutsParamUtils.getPraramValue("endDate", null); + //一次性物品类型 + String diposableGoodsName = StrutsParamUtils.getPraramValue("diposableGoodsName", null); + //一次性物品类型 + String diposableGoodsType = StrutsParamUtils.getPraramValue("diposableGoodsType", null); + //申请科室编码 + String departCoding = StrutsParamUtils.getPraramValue("departCoding", null); + //结算科室编码 + String settleAccountsDepartCoding = StrutsParamUtils.getPraramValue("settleAccountsDepartCoding", null); + //发货科室分组id + String invoiceDepartmentId = StrutsParamUtils.getPraramValue("invoiceDepartmentId", null); + //查询方式 + String searchModel = StrutsParamUtils.getPraramValue("searchModel", null); + JSONObject jsonObject = new JSONObject(); + if(StringUtils.isNotBlank(searchModel)){ + jsonObject = disposableGoodsStorageRecordManager.getStorageRecordBySearchModel( + disposableGoodsId, diposableGoodsName,startDate,endDate,diposableGoodsType,departCoding,settleAccountsDepartCoding,invoiceDepartmentId,searchModel); + }else{ + jsonObject = disposableGoodsStorageRecordManager.getStorageRecord( + disposableGoodsId,startDate,endDate); + } + StrutsResponseUtils.output(jsonObject); } /** + * 导出一次性物品出入库记录 + */ + public void exportDisposableGoodsStorageRecord(){ + HttpServletResponse response = StrutsParamUtils.getResponse(); + try { + String fileName = "一次性物品出入库记录.xls"; + ServletOutputStream servletOutputStream = response + .getOutputStream(); + response.setContentType("application/octet-stream"); + response.addHeader("Content-Disposition", "attachment;filename=" + + new String(fileName.getBytes("GBK"), "ISO8859_1")); + + String disposableGoodsId = StrutsParamUtils.getPraramValue("disposableGoodsId", null); + String startDate = StrutsParamUtils.getPraramValue("startDate", null); + String endDate = StrutsParamUtils.getPraramValue("endDate", null); + //一次性物品类型 + String diposableGoodsName = StrutsParamUtils.getPraramValue("diposableGoodsName", null); + //一次性物品类型 + String diposableGoodsType = StrutsParamUtils.getPraramValue("diposableGoodsType", null); + //申请科室编码 + String departCoding = StrutsParamUtils.getPraramValue("departCoding", null); + //结算科室编码 + String settleAccountsDepartCoding = StrutsParamUtils.getPraramValue("settleAccountsDepartCoding", null); + //发货科室分组id + String invoiceDepartmentId = StrutsParamUtils.getPraramValue("invoiceDepartmentId", null); + //查询方式 + String searchModel = StrutsParamUtils.getPraramValue("searchModel", null); + + disposableGoodsStorageRecordManager.exportDisposableGoodsStorageRecord(disposableGoodsId, diposableGoodsName, + startDate, endDate, diposableGoodsType, + departCoding, settleAccountsDepartCoding, invoiceDepartmentId, searchModel, + response.getOutputStream()); + servletOutputStream.flush(); + } catch (Exception e) { + e.printStackTrace(); + } finally { + try { + response.getOutputStream().close(); + } catch (IOException e) { + e.printStackTrace(); + } + } + } + + /** * 根据关键字搜索一次性物品. * @param spell 搜索关键字 * return 返回给前台的字符串格式如下： Index: ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/service/DisposableGoodsStorageRecordManagerImpl.java =================================================================== diff -u -r21968 -r30434 --- ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/service/DisposableGoodsStorageRecordManagerImpl.java (.../DisposableGoodsStorageRecordManagerImpl.java) (revision 21968) +++ ssts-diposablegoods/src/main/java/com/forgon/disinfectsystem/diposablegoods/service/DisposableGoodsStorageRecordManagerImpl.java (.../DisposableGoodsStorageRecordManagerImpl.java) (revision 30434) @@ -3,9 +3,23 @@ import java.math.BigDecimal; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.ArrayList; import java.util.Date; +import java.util.List; import java.util.Set; +import javax.servlet.ServletOutputStream; + +import jxl.Workbook; +import jxl.write.Label; +import jxl.write.WritableCellFormat; +import jxl.write.WritableFont; +import jxl.write.WritableSheet; +import jxl.write.WritableWorkbook; + +import org.apache.commons.collections4.CollectionUtils; +import org.apache.commons.lang.StringUtils; + import net.sf.json.JSONArray; import net.sf.json.JSONObject; @@ -17,6 +31,7 @@ import com.forgon.disinfectsystem.entity.assestmanagement.GodownEntry; import com.forgon.disinfectsystem.entity.assestmanagement.GodownEntryItem; import com.forgon.disinfectsystem.entity.invoicemanager.DiposableGoodsItem; +import com.forgon.disinfectsystem.entity.invoicemanager.InvoiceDepartment; import com.forgon.disinfectsystem.entity.invoicemanager.InvoiceItem; import com.forgon.disinfectsystem.entity.packing.PackingRecord; import com.forgon.disinfectsystem.entity.receiverecord.ReceiveRecord; @@ -28,6 +43,7 @@ import com.forgon.tools.hibernate.ObjectDao; import com.forgon.tools.string.StringTools; import com.forgon.tools.util.BaseUtils; +import com.forgon.tools.util.SqlUtils; public class DisposableGoodsStorageRecordManagerImpl implements DisposableGoodsStorageRecordManager { @@ -234,5 +250,512 @@ } return false; } + + @Override + public JSONObject getStorageRecordBySearchModel(String disposableGoodsId, String disposableGoodsName, + String startDate, String endDate, String diposableGoodsType, + String departCoding, String settleAccountsDepartCoding, + String invoiceDepartmentId, String searchModel) { + JSONObject jsonObject = new JSONObject(); + JSONArray jsonArray = new JSONArray(); + boolean success = false; + //汇总信息 + long sumTotalAmount = 0; + long sumOutTotalAmount = 0; + double sumTotalPrice = 0.0; + double sumOutTotalPrice = 0.0; + + List disposableGoodsIdList = new ArrayList(); + if(StringUtils.isNotBlank(disposableGoodsName) || DatabaseUtil.isPoIdValid(disposableGoodsId) || StringUtils.isNotBlank(diposableGoodsType)){ + String hql = String.format("select po from %s po where 1=1 ", DisposableGoods.class.getSimpleName()); + if(DatabaseUtil.isPoIdValid(disposableGoodsId)){ + hql += " and po.id = " + disposableGoodsId; + }else{ + if(StringUtils.isNotBlank(disposableGoodsName)){ + hql += " and po.name like '%" + disposableGoodsName + "%'"; + } + if(StringUtils.isNotBlank(diposableGoodsType)){ + hql += " and po.type = '" + diposableGoodsType + "'"; + } + } + + @SuppressWarnings("unchecked") + List disposableGoodsList = objectDao.findByHql(hql); + if(CollectionUtils.isNotEmpty(disposableGoodsList)){ + for (DisposableGoods disposableGoods : disposableGoodsList) { + disposableGoodsIdList.add(disposableGoods.getId()); + } + } + } + + List departCodeList = new ArrayList(); + if(StringUtils.isNotBlank(departCoding)){ + departCodeList.add(departCoding); + } + if(DatabaseUtil.isPoIdValid(invoiceDepartmentId)){ + InvoiceDepartment invoiceDepartment = (InvoiceDepartment) objectDao.getById(InvoiceDepartment.class.getSimpleName(), invoiceDepartmentId); + String departCodes = invoiceDepartment.getDepartCodes(); + departCodeList.addAll(StringTools.toCollectionIgnoreNullAndBlank(departCodes, ";")); + } + + StringBuilder sb = new StringBuilder(); + + if(StringUtils.equals(searchModel, "明细查询")){ + sb = getStorageRecordSearchSqlByDetailModel(disposableGoodsIdList, startDate, endDate, departCodeList, settleAccountsDepartCoding); + }else{ + sb = getStorageRecordSearchSqlByTotalModel(disposableGoodsIdList, startDate, endDate, departCodeList, settleAccountsDepartCoding); + } + + ResultSet resultSet = null; + try { + resultSet = objectDao.executeSql(sb.toString()); + while (resultSet.next()) { + JSONObject item = new JSONObject(); + String timeStr = ""; + if(SqlUtils.isExistColumn(resultSet, "time")){ + Date time = resultSet.getTimestamp("time"); + if(time != null){ + timeStr = Constants.SIMPLEDATEFORMAT_YYYYMMDDHHMM.format(time); + } + } + String name = resultSet.getString("name"); + String specification = resultSet.getString("specification"); + String displayName = name; + if(StringTools.isNotBlank(specification)){ + displayName = name + "[" + specification + "]"; + } + + long amout = resultSet.getLong("amount"); + BigDecimal price = resultSet.getBigDecimal("price"); + String priceStr = ""; + if(price != null){ + priceStr = String.format("%.2f",price.doubleValue()); + } + double dTotalPrice = resultSet.getDouble("totalPrice"); + String totalPrice = String.format("%.2f",dTotalPrice); + String subType = resultSet.getString("subType"); + + if(SqlUtils.isExistColumn(resultSet, "operator")){ + item.put("operator", resultSet.getString("operator")); + } + if(SqlUtils.isExistColumn(resultSet, "operator")){ + item.put("serialNumber", resultSet.getString("serialNumber")); + } + if(SqlUtils.isExistColumn(resultSet, "operator")){ + item.put("batchNumber", resultSet.getString("batchNumber")); + } + if(SqlUtils.isExistColumn(resultSet, "depart")){ + item.put("depart", resultSet.getString("depart")); + } + if(SqlUtils.isExistColumn(resultSet, "settleAccountsDepart")){ + item.put("settleAccountsDepart", resultSet.getString("settleAccountsDepart")); + } + item.put("time", timeStr); + item.put("type", resultSet.getString("type")); + item.put("subType", subType); + item.put("warehouseName", resultSet.getString("warehouseName")); + if(isInOperator(subType)){ + item.put("amount", amout); + item.put("price", priceStr); + item.put("totalPrice", totalPrice); + item.put("outAmout", ""); + item.put("outTotalPrice", ""); + sumTotalAmount += amout; + sumTotalPrice += dTotalPrice; + }else{ + item.put("amount", ""); + item.put("price", ""); + item.put("totalPrice", ""); + item.put("outAmout", amout); + item.put("outTotalPrice", totalPrice); + sumOutTotalAmount += amout; + sumOutTotalPrice += dTotalPrice; + } + item.put("name", name); + item.put("specification", specification); + item.put("displayName", displayName); + + jsonArray.add(item); + } + success = true; + } catch (SQLException e) { + e.printStackTrace(); + success = false; + }finally { + DatabaseUtil.closeResultSetAndStatement(resultSet); + } + + JSONObject item = new JSONObject(); + item.put("name", "汇总"); + item.put("displayName", "汇总"); + item.put("amount", sumTotalAmount); + item.put("totalPrice", String.format("%.2f",sumTotalPrice)); + item.put("outAmout", sumOutTotalAmount); + item.put("outTotalPrice", String.format("%.2f",sumOutTotalPrice)); + jsonArray.add(item); + + jsonObject.put("data", jsonArray); + jsonObject.put("success", success); + return jsonObject; + } + + /** + * 获取一次性物品出入库记录汇总查询的sql + * @param disposableGoodsIdList + * @param startDate + * @param endDate + * @param departCodeList + * @param settleAccountsDepartCoding + * @return + */ + private StringBuilder getStorageRecordSearchSqlByTotalModel( + List disposableGoodsIdList, String startDate, String endDate, + List departCodeList, String settleAccountsDepartCoding) { + StringBuilder sb = new StringBuilder(); + Set warehosueIds = wareHouseManager.getCurrentUserOrgUnitWarehouseIds(); + sb.append("select * from ( "); + //入库单 + sb.append("select ge.type,ge.subType,ge.warehouseName,dg.name,dg.specification, sum(gei.amount) as amount, gei.cost as price, sum(gei.amount*gei.cost) as totalPrice "); + sb.append("from GodownEntry ge "); + sb.append("inner join GodownEntryItem gei on gei.godownEntry_id = ge.id "); + sb.append("inner join DisposableGoods dg on dg.id = gei.disposableGoodsID "); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append("and ").append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + }else{ + sb.append("and dg.id IN (select id from DisposableGoods) "); + } + sb.append(" and ge.type <> '" + GodownEntry.TYPE_APPROPRIATE + "'"); + sb.append(" and "); + sb.append(SqlBuilder.build_number_IN_Statement("ge.warehouseID", SqlBuilder.IN, warehosueIds)); + sb.append(" and "); + sb.append(dateQueryAdapter.dateAreaSql("ge.time", startDate, endDate, true, true)); + sb.append("group by ge.type,ge.subType,ge.warehouseName, dg.name,dg.specification,gei.cost "); + + sb.append(" union all "); + + //发货记录 + sb.append("select '发货单' as type,'发货出库' as subType,i.sourceWarehouseName as warehouseName,dg.name,dg.specification,sum(dgi.amount) as amount,dgi.price as price, sum(dgi.amount*dgi.price) as totalPrice "); + sb.append("from Invoice i "); + sb.append("inner join InvoiceItem ii on ii.invoice_id = i.id "); + sb.append("inner join DiposableGoodsItem dgi on ii.id = dgi.invoiceItemID "); + sb.append("inner join DisposableGoods dg on dg.id = dgi.disposableGoodsID "); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append("and ").append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + }else{ + sb.append("and dg.id IN (select id from DisposableGoods) "); + } + sb.append(" and "); + sb.append(dateQueryAdapter.dateAreaSql("i.sendTime", startDate, endDate, true, true)); + sb.append(" and "); + sb.append(SqlBuilder.build_number_IN_Statement("i.sourceWarehouseId", SqlBuilder.IN, warehosueIds)); + sb.append("group by i.sourceWarehouseName, dg.name,dg.specification,dgi.price "); + + + sb.append(" union all "); + + //退货记录 + sb.append("select '退货单' as type,'退货入库' as subType,rgr.warehouseName as warehouseName,dg.name,dg.specification,sum(rgi.amount) as amount, rgi.price as price, sum(rgi.amount*rgi.price) as totalPrice "); + sb.append("from ReturnGoodsRecord rgr "); + sb.append("inner join ReturnGoodsItem rgi on rgi.returnGoodsRecord_ID = rgr.id "); + sb.append("inner join DisposableGoods dg on rgi.disposableGoodsID = dg.id "); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append("and ").append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + }else{ + sb.append("and dg.id IN (select id from DisposableGoods) "); + } + sb.append(" and "); + sb.append(dateQueryAdapter.dateAreaSql("rgr.returnTime", startDate, endDate, true, true)); + sb.append(" and "); + sb.append(SqlBuilder.build_number_IN_Statement("rgr.warehouseID", SqlBuilder.IN, warehosueIds)); + sb.append("group by rgr.warehouseName,dg.name,dg.specification,rgi.price "); + + sb.append(" union all "); + + //装配扣减 + sb.append("select '装配扣减' as type,'装配出库' as subType,dgbs.warehouseName as warehouseName,dg.name,dg.specification, dgi.amount, dgi.price as price,(dgi.amount*dgi.price) as totalPrice "); + sb.append("from PackingRecord pr "); + sb.append("join DiposableGoodsItem dgi on pr.id=dgi.packingRecordId "); + sb.append("inner join DisposableGoods dg on dg.id=dgi.disposableGoodsID "); + sb.append("inner join DisposableGoodsBatchStock dgbs on dgi.disposableGoodsBatchStockID=dgbs.id "); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append("and ").append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + }else{ + sb.append("and dg.id IN (select id from DisposableGoods) "); + } + sb.append("and "); + sb.append(dateQueryAdapter.dateAreaSql("pr.packTime", startDate, endDate, true, true)); + sb.append("and "); + sb.append(SqlBuilder.build_number_IN_Statement("dgbs.warehouseID", SqlBuilder.IN, warehosueIds)); + + sb.append(" union all "); + + //供应室领用 + sb.append("select '供应室领用' as type,'' as subType,rri.warehouseName as warehouseName,dg.name,dg.specification, rri.amount,rri.price as price,(rri.amount*rri.price) as totalPrice "); + sb.append("from ReceiveRecord rr "); + sb.append("join ReceiveRecordItem rri on rri.receiveRecord_id = rr.id "); + sb.append("join DisposableGoods dg on dg.id = rri.disposableGoodsId "); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append("and ").append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + }else{ + sb.append("and dg.id IN (select id from DisposableGoods) "); + } + sb.append("and "); + sb.append(dateQueryAdapter.dateAreaSql("rr.time", startDate, endDate, true, true)); + sb.append("and "); + sb.append(SqlBuilder.build_number_IN_Statement("rri.warehouseId", SqlBuilder.IN, warehosueIds)); + + sb.append(" ) md "); + System.out.println(sb.toString()); + return sb; + } + + /** + * 获取一次性物品出入库记录明细查询的sql + * @param disposableGoodsIdList + * @param startDate + * @param endDate + * @param departCodeList + * @param settleAccountsDepartCoding + * @return + */ + private StringBuilder getStorageRecordSearchSqlByDetailModel(List disposableGoodsIdList, String startDate, String endDate, List departCodeList, String settleAccountsDepartCoding) { + StringBuilder sb = new StringBuilder(); + Set warehosueIds = wareHouseManager.getCurrentUserOrgUnitWarehouseIds(); + sb.append("select * from ( "); + //单号与单类型及单id的修正关系(对于调拨单产生的入库单和退库单,由于用户不知道自动产生的单，所以需要把单号和单类型改为调拨单的单号和单类型) + String godownEntryIdSerialNumber = " (select ge.id,mge.type,mge.serialNumber from GodownEntry ge inner join (select id,type,serialNumber from GodownEntry) mge on ge.sourceId = mge.id union all select id,type,serialNumber from GodownEntry where sourceId is null or sourceId = 0) "; + + String godownEntryItemSummarySql = "select dgbs.batchNumber, ge.id as entryId,gei.disposableGoodsID, gei.cost as price, sum(gei.amount) as amount,sum(gei.amount*gei.cost) as totalPrice " + +"from "+GodownEntry.class.getSimpleName()+" ge inner join " + + GodownEntryItem.class.getSimpleName()+" gei on gei.godownEntry_id = ge.id " + + " inner join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on dgbs.id=gei.disposableGoodsBatchStockID " + + " group by gei.disposableGoodsID,gei.cost,ge.id,dgbs.batchNumber"; + + //入库单 + sb.append("select atp.batchNumber as batchNumber, ge.operator,isn.serialNumber,ge.time,isn.type,ge.subType,ge.warehouseName, "); + sb.append("atp.amount,atp.price,atp.totalPrice as totalPrice,dg.name,dg.specification, '' as depart, '' as settleAccountsDepart "); + sb.append(" from GodownEntry ge inner join " + godownEntryIdSerialNumber + " isn on ge.id = isn.id"); + sb.append(" inner join ("+godownEntryItemSummarySql+") atp"); + sb.append(" on atp.entryId = ge.id "); + sb.append(" inner join DisposableGoods dg on dg.id = atp.disposableGoodsID "); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append(" and "); + sb.append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + } + sb.append(" and ge.type <> '" + GodownEntry.TYPE_APPROPRIATE + "'"); + sb.append(" and "); + sb.append(SqlBuilder.build_number_IN_Statement("ge.warehouseID", SqlBuilder.IN, warehosueIds)); + sb.append(" where "); + sb.append(dateQueryAdapter.dateAreaSql("ge.time", startDate, endDate, true, true)); + + sb.append(" union all "); + + //发货记录 + sb.append(" select idtp.batchNumber as batchNumber, i.sender as operator,i.serialNumber,i.sendTime as time,'发货单' as type,'发货出库' as subType,i.sourceWarehouseName as warehouseName," ); + sb.append("idtp.amount, null as price,idtp.totalPrice,dg.name,dg.specification, i.depart as depart, i.settleAccountsDepart as settleAccountsDepart "); + sb.append(" from Invoice i "); + sb.append(" inner join ( "); + sb.append(" select dgbs.disposableGoodsId,dgbs.batchNumber, ii.invoice_id,sum(dgi.amount) as amount,sum(dgi.amount*dgi.price) as totalPrice from " + + InvoiceItem.class.getSimpleName() + " ii inner join " + + DiposableGoodsItem.class.getSimpleName()+ " dgi on ii.id = dgi.invoiceItemID " + + " inner join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on dgbs.id = dgi.disposableGoodsBatchStockID"); + sb.append(" group by dgbs.disposableGoodsId, dgbs.batchNumber, ii.invoice_id) idtp"); + sb.append(" on i.id = idtp.invoice_id "); + sb.append(" inner join DisposableGoods dg on dg.id = idtp.disposableGoodsId where "); + sb.append(dateQueryAdapter.dateAreaSql("i.sendTime", startDate, endDate, true, true)); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append(" and "); + sb.append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + } + sb.append(" and "); + sb.append(SqlBuilder.build_number_IN_Statement("i.sourceWarehouseId", SqlBuilder.IN, warehosueIds)); + + if(CollectionUtils.isNotEmpty(departCodeList)){ + sb.append(" and "); + sb.append(SqlUtils.getStringFieldInLargeCollectionsPredicate("i.departCoding", departCodeList)); + } + if(StringUtils.isNotBlank(settleAccountsDepartCoding)){ + sb.append(" and i.settleAccountsDepartCoding = '" + settleAccountsDepartCoding + "'"); + } + + + sb.append(" union all "); + + //退货记录 + sb.append(" select mt.batchNumber as batchNumber, rgr.operator,'' as serialNumber,rgr.returnTime as time,'退货单' as type,'退货入库' as subType,rgr.warehouseName as warehouseName,"); + sb.append(" mt.amount,null as price,mt.totalPrice as totalPrice,dg.name,dg.specification, rgr.depart as depart, rgr.settleAccountsDepart as settleAccountsDepart "); + sb.append(" from ReturnGoodsRecord rgr inner join"); + sb.append(" (select dgbs.batchNumber, rgr.id as recordId,rgi.disposableGoodsID as disposableGoodsId, sum(rgi.amount) as amount,sum(rgi.amount*rgi.price) as totalPrice from " + + ReturnGoodsRecord.class.getSimpleName()+ " rgr inner join " + + ReturnGoodsItem.class.getSimpleName() + " rgi on rgi.returnGoodsRecord_ID = rgr.id" + + " inner join " + DisposableGoodsBatchStock.class.getSimpleName() + + " dgbs on dgbs.id=rgi.disposableGoodsBatchStockID" +" group by dgbs.batchNumber,rgi.disposableGoodsID,rgr.id) mt "); + sb.append(" on mt.recordId = rgr.id "); + sb.append(" inner join DisposableGoods dg on mt.disposableGoodsID = dg.id"); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append(" and"); + sb.append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + } + sb.append(" where "); + sb.append(SqlBuilder.build_number_IN_Statement("rgr.warehouseID", SqlBuilder.IN, warehosueIds)); + + sb.append(" union all "); + + //装配扣减 + sb.append(" select dgbs.batchNumber as batchNumber, pr.packer as operator,'' as serialNumber,pr.packTime as time,'装配扣减' as type,'装配出库' as subType,dgbs.warehouseName as warehouseName," ); + sb.append(" dgi.amount, dgi.price as price,(dgi.amount*dgi.price) as totalPrice,dg.name,dg.specification, '' as depart, '' as settleAccountsDepart "); + sb.append(" from " + PackingRecord.class.getSimpleName() + " pr "); + sb.append(" inner join " + DiposableGoodsItem.class.getSimpleName() + " dgi on pr.id=dgi.packingRecordId"); + sb.append(" inner join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on dgi.disposableGoodsBatchStockID=dgbs.id"); + sb.append(" inner join " + DisposableGoods.class.getSimpleName() + " dg on dg.id=dgi.disposableGoodsID where (1=1) "); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append(" and"); + sb.append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + } + sb.append(" and "); + sb.append(SqlBuilder.build_number_IN_Statement("dgbs.warehouseID", SqlBuilder.IN, warehosueIds)); + sb.append(" and "); + sb.append(dateQueryAdapter.dateAreaSql("pr.packTime", startDate, endDate, true, true)); + + sb.append(" union all "); + + //供应室领用 + sb.append(" select dgbs.batchNumber as batchNumber,rr.operator as operator,'' as serialNumber,rr.time as time,'供应室领用' as type,'' as subType,dgbs.warehouseName as warehouseName,"); + sb.append(" rri.amount,rri.price as price,(rri.amount*rri.price) as totalPrice,dg.name,dg.specification, '' as depart, '' as settleAccountsDepart"); + sb.append(" from " + ReceiveRecordItem.class.getSimpleName() + " rri "); + sb.append(" inner join " + ReceiveRecord.class.getSimpleName() + " rr on rr.id = rri.receiveRecord_id"); + sb.append(" inner join " + DisposableGoods.class.getSimpleName() + " dg on dg.id = rri.disposableGoodsId"); + sb.append(" inner join " + DisposableGoodsBatchStock.class.getSimpleName() + " dgbs on rri.disposableGoodsBatchStockId=dgbs.id where (1=1)"); + if(CollectionUtils.isNotEmpty(disposableGoodsIdList)){ + sb.append(" and "); + sb.append(SqlUtils.getNonStringFieldInLargeCollectionsPredicate("dg.id", disposableGoodsIdList)); + } + sb.append(" and "); + sb.append(SqlBuilder.build_number_IN_Statement("dgbs.warehouseID", SqlBuilder.IN, warehosueIds)); + + sb.append(" and "); + sb.append(dateQueryAdapter.dateAreaSql("rr.time", startDate, endDate, true, true)); + + sb.append(" ) md "); + sb.append(" where "); + sb.append(dateQueryAdapter.dateAreaSql("time", startDate, endDate, true, true)); + sb.append(" order by time "); + System.out.println(sb.toString()); + return sb; + } + + @Override + public void exportDisposableGoodsStorageRecord(String disposableGoodsId, + String disposableGoodsName, String startDate, String endDate, + String diposableGoodsType, String departCoding, + String settleAccountsDepartCoding, String invoiceDepartmentId, + String searchModel, ServletOutputStream outputStream) { + JSONObject result = new JSONObject(); + if(StringUtils.isNotBlank(searchModel)){ + result = this.getStorageRecordBySearchModel(disposableGoodsId, disposableGoodsName, startDate, endDate, diposableGoodsType, departCoding, settleAccountsDepartCoding, invoiceDepartmentId, searchModel); + } else { + result = this.getStorageRecord(disposableGoodsId, startDate, endDate); + } + createExportFileByJxl(result, outputStream); + } + + private void createExportFileByJxl(JSONObject result, + ServletOutputStream ops) { + JSONArray data = result.optJSONArray("data"); + if(data == null || data.size() == 0){ + return; + } + WritableWorkbook book = null; + // 打开文件 + try { + book = Workbook.createWorkbook(ops); + int length=data.size();//查询结果记录数 + int sheetSize=60000; + int sheetNum=1; + if(length%sheetSize>0){ + sheetNum=length/sheetSize+1; + }else{ + sheetNum=length/sheetSize; + } + //需导出的列头定义(与器械包信息页面列表保持一致) + String ss[] = {"物品名称","批次","单号","单类型","操作员","操作内容","操作时间","仓库", + "入库金额","入库单价","入库数量","出库金额","出库数量","申请科室","结算科室" + }; + Label label; + //循环sheet数量 + for(int kk=0;kk