From e997eee6b747c343e432783111371aca0bd89306 Mon Sep 17 00:00:00 2001
From: 李宇 <986321569@qq.com>
Date: 星期二, 29 六月 2021 13:15:24 +0800
Subject: [PATCH] 导出试剂
---
src/main/java/com/nanometer/smartlab/service/SysReagentService.java | 4
src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java | 15 +++
src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml | 43 ++++++++
src/main/webapp/reagent_mng.xhtml | 12 ++
src/main/java/com/nanometer/smartlab/controller/SupplierOrderMngController.java | 76 ++++++++------
src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java | 2
pom.xml | 18 +--
src/main/java/com/nanometer/smartlab/util/ExcelUtils.java | 28 +++--
src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java | 55 ++++++++++
9 files changed, 193 insertions(+), 60 deletions(-)
diff --git a/pom.xml b/pom.xml
index 9786c9e..5b57304 100644
--- a/pom.xml
+++ b/pom.xml
@@ -140,7 +140,7 @@
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
- <version>1.2</version>
+ <version>1.1.2</version>
</dependency>
<dependency>
@@ -376,20 +376,18 @@
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
- <version>4.0.1</version>
+ <version>4.1.1</version>
</dependency>
-
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
- <version>3.17</version>
+ <version>4.1.1</version>
</dependency>
-
- <dependency>
- <groupId>org.apache.commons</groupId>
- <artifactId>commons-compress</artifactId>
- <version>1.9</version>
- </dependency>
+ <dependency>
+ <groupId>org.apache.commons</groupId>
+ <artifactId>commons-compress</artifactId>
+ <version>1.20</version>
+ </dependency>
</dependencies>
<profiles>
diff --git a/src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java b/src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java
index c64d093..b6c37f5 100644
--- a/src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java
+++ b/src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java
@@ -7,6 +7,7 @@
import com.nanometer.smartlab.util.Constants;
import com.nanometer.smartlab.util.FacesUtils;
import com.nanometer.smartlab.util.IDUtils;
+import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
@@ -839,6 +840,20 @@
return dataModel;
}
+ public void export2Excel() {
+
+ List<Map> list = sysReagentService.selectAll(name, cas, supplierId,getType(), productSn);
+ try{
+ boolean isexport = sysReagentService.export2Excel(list,getType());
+ if (isexport){
+
+ }
+ }catch (Exception e){
+ e.printStackTrace();
+ FacesUtils.warn("导出失败");
+ }
+ }
+
public SysReagent getSysReagent() {
return sysReagent;
}
diff --git a/src/main/java/com/nanometer/smartlab/controller/SupplierOrderMngController.java b/src/main/java/com/nanometer/smartlab/controller/SupplierOrderMngController.java
index 3469ea5..1477f09 100644
--- a/src/main/java/com/nanometer/smartlab/controller/SupplierOrderMngController.java
+++ b/src/main/java/com/nanometer/smartlab/controller/SupplierOrderMngController.java
@@ -269,28 +269,21 @@
try {
if(allOpeOrder != null && allOpeOrder.size() > 0)
{
- int count = allOpeOrder.size();
- this.setRowCount(count);
- //list = queryByPage();
- list = supplierOpeOrderService.getSupplierOpeOrderList(orderCode,applyCode,status,orderTime,orderEndTime,first,pageSize);
- for(int i=0;i<list.size();i++){
- System.out.println(list.get(i));
- /*if(list.get(i).getSecondUserName()!=null&&!list.get(i).getSecondUserName().equals("")){
- String name=list.get(i).getApproveUserName();
- list.get(i).setApproveUserName(list.get(i).getSecondUserName());
- list.get(i).setSecondUserName(name);
- }*/
- if(list.get(i).getBeforeApproveUserId()!=null&&!list.get(i).getBeforeApproveUserId().equals("")){
- String name=list.get(i).getApproveUserName();
- list.get(i).setFirst(list.get(i).getFirName());
- list.get(i).setSecond(name);
- }else {
- list.get(i).setFirst(list.get(i).getApproveUserName());
- list.get(i).setSecond(list.get(i).getSecondUserName());
- }
+ int count = allOpeOrder.size();
+ this.setRowCount(count);
+
+ list = supplierOpeOrderService.getSupplierOpeOrderList(orderCode,applyCode,status,orderTime,orderEndTime,first,pageSize);
+ for (int i=0;i<list.size();i++) {
+ if (list.get(i).getBeforeApproveUserId() != null && !list.get(i).getBeforeApproveUserId().equals("")) {
+ String name = list.get(i).getApproveUserName();
+ list.get(i).setFirst(list.get(i).getFirName());
+ list.get(i).setSecond(name);
+ } else {
+ list.get(i).setFirst(list.get(i).getApproveUserName());
+ list.get(i).setSecond(list.get(i).getSecondUserName());
}
- for (SupplierOrder supplierOrder:list
- ) {
+ }
+ for (SupplierOrder supplierOrder:list) {
if(supplierOrder.getIds()!=null&&!supplierOrder.getIds().equals("")){
List ids = Arrays.asList(supplierOrder.getIds().split(","));
supplierOrder.setApplyIds(ids);
@@ -322,17 +315,36 @@
@Override
public SupplierOrder getRowData(String rowKey) {
-// Iterator<SupplierOrder> iterator = this.iterator();
-// if (iterator != null) {
-// SupplierOrder ss = null;
-// while (iterator.hasNext()) {
-// ss = iterator.next();
-// if (rowKey.equals(ss.getId())) {
-// return ss;
-// }
-// }
-// }
- return supplierOpeOrderService.selectById(rowKey);
+ SupplierOrder supplierOrder = supplierOpeOrderService.selectById(rowKey);
+ if (supplierOrder.getBeforeApproveUserId() != null && !supplierOrder.getBeforeApproveUserId().equals("")) {
+ String name = supplierOrder.getApproveUserName();
+ supplierOrder.setFirst(supplierOrder.getFirName());
+ supplierOrder.setSecond(name);
+ } else {
+ supplierOrder.setFirst(supplierOrder.getApproveUserName());
+ supplierOrder.setSecond(supplierOrder.getSecondUserName());
+ }
+
+ if(supplierOrder.getIds()!=null&&!supplierOrder.getIds().equals("")){
+ List ids = Arrays.asList(supplierOrder.getIds().split(","));
+ supplierOrder.setApplyIds(ids);
+ Double a=0.00;
+ BigDecimal total=BigDecimal.valueOf(a);
+ for (int j=0;j<ids.size();j++) {
+ OpeApply app=opeApplyService.getOpeApply(ids.get(j).toString());
+ if(app.getStockFlag()!=0){//判断申购试剂是否缺货
+ if(app.getApplyPrice()!=null){//判断试剂是否有实际售价
+ BigDecimal b=app.getApplyPrice().multiply(BigDecimal.valueOf(app.getNum()));
+ total=b.add(total);
+ }else{
+ BigDecimal c=app.getReagent().getPrice().multiply(BigDecimal.valueOf(app.getNum()));
+ total=c.add(total);
+ }
+ }
+ }
+ supplierOrder.setTotalPrice(total.doubleValue());
+ }
+ return supplierOrder;
}
};
}
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
index b64a27d..da75df3 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
+++ b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
@@ -51,4 +51,6 @@
SysReagent getRowData(@Param("id") String rowKey);
SysReagent getSysReagentListNewRowData(@Param("id")String rowKey);
+
+ List<Map> selectAll(Map params);
}
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
index 8aa3d68..0c3bb58 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
+++ b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
@@ -549,4 +549,47 @@
left join sys_supplier supplier on supplier.id = sr.supplier_id
where sr.id = #{id}
</select>
+ <select id="selectAll" resultType="java.util.Map">
+ select
+ sr.name,
+ sr.product_sn as productSn,
+ sr.cas,
+ sr.price,
+ sr.per_box as perBox,
+ sr.safetynum,
+
+ CASE WHEN type = 0 THEN '耗材' WHEN type = 1 THEN '试剂' ELSE '' END AS kind,
+ ss.meta_value as productHomeName,
+ bm.meta_value as control_products_name,
+ supplier.name as supplierName,
+ bm1.meta_value as reagentType,
+ bm2.meta_value as reagentCharacter,
+ bm3.meta_value as reagentFormat,
+ CONCAT(sr.main_metering,bm4.meta_value) as reagentUnit
+ from sys_reagent as sr
+ left join base_meta as ss on sr.product_home = ss.id
+ left join base_meta bm on bm.id = sr.control_products
+ left join base_meta bm1 on bm1.id = sr.reagent_type
+ left join base_meta bm2 on bm2.id = sr.reagent_character
+ left join base_meta bm3 on bm3.id = sr.reagent_format
+ left join base_meta bm4 on bm4.id = sr.reagent_unit
+ left join sys_supplier supplier on supplier.id = sr.supplier_id
+ where sr.valid_flag = 1
+ <if test="cas != null and cas != ''">
+ and sr.cas = #{cas}
+ </if>
+ <if test="supplierId != null and supplierId != ''">
+ and sr.supplier_id = #{supplierId}
+ </if>
+ <if test="name != null and name != ''">
+ and sr.name like concat("%", #{name} ,"%")
+ </if>
+ <if test="type != null">
+ and sr.type = #{type}
+ </if>
+ <if test="productSn != null and productSn != ''">
+ and sr.product_sn like concat("%", #{productSn} ,"%")
+ </if>
+ ORDER BY length(sr.name),sr.create_time desc
+ </select>
</mapper>
diff --git a/src/main/java/com/nanometer/smartlab/service/SysReagentService.java b/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
index 8404eca..251ba7f 100644
--- a/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
+++ b/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
@@ -52,4 +52,8 @@
SysReagent getRowData(String rowKey);
SysReagent getSysReagentListNewRowData(String rowKey);
+
+ boolean export2Excel(List<Map> list, Integer type) throws Exception;
+
+ List<Map> selectAll(String name, String cas, String supplierId, Integer type, String productSn);
}
diff --git a/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java b/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
index 6dca8bf..eb22dbe 100644
--- a/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
+++ b/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
@@ -8,6 +8,7 @@
import com.nanometer.smartlab.exception.AlarmException;
import com.nanometer.smartlab.exception.BusinessException;
import com.nanometer.smartlab.exception.ExceptionEnumCode;
+import com.nanometer.smartlab.util.ExcelUtils;
import com.nanometer.smartlab.util.IDUtils;
import com.nanometer.smartlab.util.MessageUtil;
import org.apache.commons.lang.StringUtils;
@@ -21,10 +22,7 @@
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
-import java.util.ArrayList;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
/**
* Created by johnny on 17/11/29.
@@ -192,6 +190,55 @@
return sysReagentDao.getSysReagentListNewRowData(rowKey);
}
+ @Override
+ public boolean export2Excel(List<Map> list, Integer type) throws Exception {
+ Map<String,String> map = new LinkedHashMap<>();
+ if (type == 0){
+ map.put("kind", "种类");
+ map.put("name", "名称");
+ map.put("productSn", "产品编号");
+ map.put("cas", "CAS");
+ map.put("reagentType", "试剂类型");
+ map.put("reagentCharacter", "危险性质");
+ map.put("supplierName", "供应商");
+ map.put("productHomeName", "厂家");
+ map.put("reagentFormat", "规格");
+ map.put("reagentUnit", "包装");
+ map.put("price", "含税售价");
+ map.put("perBox", "每箱数量");
+ ExcelUtils.export2Excel(list,"耗材列表",map);
+ }else {
+ map.put("kind", "种类");
+ map.put("name", "试剂名称");
+ map.put("control_products_name", "管制品");
+ map.put("productSn", "产品编号");
+ map.put("cas", "CAS");
+ map.put("reagentType", "试剂类型");
+ map.put("reagentCharacter", "危险性质");
+ map.put("supplierName", "供应商");
+ map.put("productHomeName", "厂家");
+ map.put("reagentFormat", "规格");
+ map.put("reagentUnit", "包装");
+ map.put("price", "含税售价");
+// map.put("originprice", "原始售价");
+ map.put("perBox", "每箱数量");
+ map.put("safetynum", "安全库存");
+ ExcelUtils.export2Excel(list,"试剂列表",map);
+ }
+ return true;
+ }
+
+ @Override
+ public List<Map> selectAll(String name, String cas, String supplierId, Integer type, String productSn) {
+ Map<String, Object> params = new HashMap<String, Object>();
+ params.put("name", name);
+ params.put("cas", cas);
+ params.put("supplierId", supplierId);
+ params.put("type", type);
+ params.put("productSn", productSn);
+ return sysReagentDao.selectAll(params);
+ }
+
@Transactional(propagation = Propagation.REQUIRED)
public int getSysReagentTotalCount(String name, String cas, String supplierId,Integer type) {
diff --git a/src/main/java/com/nanometer/smartlab/util/ExcelUtils.java b/src/main/java/com/nanometer/smartlab/util/ExcelUtils.java
index ba95396..4ba663e 100644
--- a/src/main/java/com/nanometer/smartlab/util/ExcelUtils.java
+++ b/src/main/java/com/nanometer/smartlab/util/ExcelUtils.java
@@ -3,24 +3,28 @@
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.xssf.streaming.SXSSFRow;
+import org.apache.poi.xssf.streaming.SXSSFSheet;
+import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.faces.context.FacesContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
+import java.io.OutputStream;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class ExcelUtils {
- public static HSSFWorkbook exportExcelNew(Map<String, String> map, List<Map> dataList, String name){
- HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
+ public static SXSSFWorkbook exportExcelNew(Map<String, String> map, List<Map> dataList, String name){
+ SXSSFWorkbook sb = new SXSSFWorkbook(1000);
Integer total = null;
//sheet名字
- HSSFSheet sheet = hssfWorkbook.createSheet(name);
- HSSFRow titlerRow = sheet.createRow(0);
+ SXSSFSheet sheet = sb.createSheet(name);
+ SXSSFRow titlerRow = sheet.createRow(0);
int k = 0;
for(Map.Entry<String, String> entry:map.entrySet()){
@@ -30,7 +34,7 @@
}
//数据
for (int i = 0; i < dataList.size(); i++) {
- HSSFRow dataRow = sheet.createRow(i + 1);
+ SXSSFRow dataRow = sheet.createRow(i + 1);
int j = 0;
for(Map.Entry<String, String> entry:map.entrySet()){
//表头
@@ -41,7 +45,7 @@
- return hssfWorkbook;
+ return sb;
}
@@ -50,7 +54,7 @@
throw new Exception("没有数据可以导出");
}
- ServletOutputStream out = null;
+ OutputStream out = null;
InputStream is = null;
try {
FacesContext ctx = FacesContext.getCurrentInstance();
@@ -62,18 +66,18 @@
StringBuffer contentDisposition = new StringBuffer();
contentDisposition.append("attachment;");
contentDisposition.append("filename=\"");
- contentDisposition.append(name).append(".xls");
+ contentDisposition.append(name).append(".xlsx");
contentDisposition.append("\"");
response.setHeader(
"Content-Disposition",
new String(contentDisposition.toString().getBytes(
System.getProperty("file.encoding")), "ISO8859-1"));
out = response.getOutputStream();
- HSSFWorkbook hssfWorkbook = ExcelUtils.exportExcelNew(map, list, name);
- hssfWorkbook.write(out);
-// out.flush();
+ SXSSFWorkbook sb = ExcelUtils.exportExcelNew(map, list, name);
+ sb.write(out);
+ out.flush();
+ sb.dispose();// 释放workbook所占用的所有windows资源
ctx.responseComplete();
-
} catch (Exception e) {
if (is != null) {
try {
diff --git a/src/main/webapp/reagent_mng.xhtml b/src/main/webapp/reagent_mng.xhtml
index e4c1195..01a8c83 100644
--- a/src/main/webapp/reagent_mng.xhtml
+++ b/src/main/webapp/reagent_mng.xhtml
@@ -39,7 +39,7 @@
</p:panelGrid>
</p:panel>
<p:panel styleClass="center-body">
- <p:panelGrid columns="8" styleClass="btn">
+ <p:panelGrid columns="9" styleClass="btn">
<p:commandButton value="新建" styleClass="new-btn"
process="@this"
actionListener="#{reagentMngController.onNewBtnClick}"
@@ -66,7 +66,9 @@
<p:commandButton value="耗材导入" styleClass="import-btn" onclick="importReagent2()"
></p:commandButton>
<a href="resources/template/耗材导入模板.xlsx" style="display: inline-block;"><img src="resources/images/xlsx.png" width="30px;" alt=""/></a>
- <script>
+ <p:commandButton value="导出" styleClass="new-btn" ajax="false" id="exportButton" widgetVar="exportButton"
+ actionListener="#{reagentMngController.export2Excel}" />
+ <script type="text/javascript">
function importReagent(){
$("#reagentMngForm\\:importReagentBtn_input").click();
}
@@ -142,6 +144,9 @@
<p:column headerText="安全库存" rendered="#{reagentMngController.type == 1}">
<h:outputText value="#{row.safetynum}" ></h:outputText>
</p:column>
+ <p:column headerText="管制品" rendered="#{reagentMngController.type == 1}">
+ <h:outputText value="#{row.controlProductsName}" ></h:outputText>
+ </p:column>
<!-- <p:column headerText="库存">-->
<!-- <h:outputText value="#{row.reserve}"></h:outputText>-->
<!-- </p:column>-->
@@ -162,6 +167,7 @@
update="@form" auto="true" sizeLimit="5000000"
mode="advanced"/>
</div>
+
</h:form>
<p:dialog modal="true" header="#{reagentMngController.action==constants.ACTION_ADD?'新建':'修改'}" appendTo="@(body)"
@@ -528,5 +534,7 @@
</p:panel>
</h:form>
</p:dialog>
+
+
</ui:composition>
</html>
--
Gitblit v1.9.2