From 123188cd4858079c37c960160002ce0c8ab98006 Mon Sep 17 00:00:00 2001
From: gdg <764716047@qq.com>
Date: Thu, 26 Nov 2020 16:42:18 +0800
Subject: [PATCH] 试剂管理查询优化
---
src/main/java/com/nanometer/smartlab/service/SysReagentService.java | 3 +
src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java | 6 +-
src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml | 49 ++++++++++++++++++++++++
src/main/webapp/reagent_mng.xhtml | 1
src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java | 2 +
src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java | 34 +++++++++++++++++
6 files changed, 91 insertions(+), 4 deletions(-)
diff --git a/src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java b/src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java
index a836046..fd71861 100644
--- a/src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java
+++ b/src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java
@@ -86,7 +86,7 @@
private Integer type;
public Integer getType() {
- return type;
+ return type == null?0:type;
}
public void setType(Integer type) {
@@ -798,10 +798,10 @@
public List<SysReagent> load(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
List<SysReagent> list = null;
try {
- int count = sysReagentService.getSysReagentTotalCount(name, cas, supplierId,type);
+ int count = sysReagentService.getSysReagentTotalCountNew(name, cas, supplierId,getType());
this.setRowCount(count);
if (count > 0) {
- list = sysReagentService.getSysReagentList(name, cas, supplierId,type, first, pageSize);
+ list = sysReagentService.getSysReagentListNew(name, cas, supplierId,getType(), first, pageSize);
}
} catch (Exception e) {
logger.error(e);
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
index 54619ec..056dee3 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
+++ b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java
@@ -15,7 +15,9 @@
public SysReagent getSysReagentByCas(SysReagent sysReagent) throws DataAccessException;
public SysReagent getSysReagentByCas2(SysReagent sysReagent) throws DataAccessException;
public List<SysReagent> getSysReagentList(Map params) throws DataAccessException;
+ public List<SysReagent> getSysReagentListNew(Map params) throws DataAccessException;
public int getSysReagentTotalCount(Map params) throws DataAccessException;
+ public int getSysReagentTotalCountNew(Map params) throws DataAccessException;
public void insertSysReagent(SysReagent sysReagent) throws DataAccessException;
public int updateSysReagent(SysReagent sysReagent) throws DataAccessException;
diff --git a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
index 77133ee..02acbed 100644
--- a/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
+++ b/src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml
@@ -95,6 +95,35 @@
</if>
</select>
+
+ <select id="getSysReagentListNew" parameterType="java.util.Map" resultMap="SysReagent">
+ select sr.*, ss.meta_value as product_home_name, ifnull(w.reserve,0) as reserve, bm.meta_value as control_products_name,supplier.name as supplierName
+ 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 (select reagent_id, sum(reserve) reserve from ope_warehouse_reserve where valid_flag = 1 group by reagent_id) w on w.reagent_id = sr.id
+
+ 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 type != ''">
+ and sr.type = #{type}
+ </if>
+ ORDER BY sr.create_time desc
+ <if test="first != null and pageSize != null">
+ limit #{first}, #{pageSize}
+ </if>
+ </select>
+
<select id="getSysReagentTotalCount" parameterType="java.util.Map" resultType="int">
select count(1)
from sys_reagent as sr
@@ -111,6 +140,26 @@
</if>
</select>
+ <select id="getSysReagentTotalCountNew" parameterType="java.util.Map" resultType="int">
+ select count(1)
+ from sys_reagent as sr
+ 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 type != ''">
+ and sr.type = #{type}
+ </if>
+
+ </select>
+
+
<select id="getSysReagentByCas2" parameterType="com.nanometer.smartlab.entity.SysReagent" resultMap="SysReagent">
select * From sys_reagent where type=0 and valid_flag = 1
<if test="cas!=null and cas!=''">
diff --git a/src/main/java/com/nanometer/smartlab/service/SysReagentService.java b/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
index e38ca00..a0e8e5f 100644
--- a/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
+++ b/src/main/java/com/nanometer/smartlab/service/SysReagentService.java
@@ -22,7 +22,10 @@
public List<SysReagent> getSysReagentList2(SysUser user,Integer favorFlag, String name, String cas, String productSn, String supplierId, Integer first, Integer pageSize);
public int getSysReagentTotalCount2(SysUser user,Integer favorFlag,String name, String cas,String productSn, String supplierId);
+ int getSysReagentTotalCountNew(String name, String cas, String supplierId, Integer type);
public List<SysReagent> query();
Map getReagentDetail(String id);
+
+ public List<SysReagent> getSysReagentListNew(String name, String cas, String supplierId, Integer type, Integer first, Integer pageSize);
}
diff --git a/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java b/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
index f3c8dcd..5c9195a 100644
--- a/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
+++ b/src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java
@@ -60,6 +60,24 @@
throw new BusinessException(ExceptionEnumCode.DB_ERR, MessageUtil.getMessageByCode(ExceptionEnumCode.DB_ERR.getCode()), e);
}
}
+
+ public List<SysReagent> getSysReagentListNew(String name, String cas, String supplierId,Integer type, Integer first, Integer pageSize) {
+ try {
+ 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("first", first);
+ params.put("pageSize", pageSize);
+ return this.sysReagentDao.getSysReagentListNew(params);
+ } catch (DataAccessException e) {
+ logger.error(e.getMessage(), e);
+ throw new BusinessException(ExceptionEnumCode.DB_ERR, MessageUtil.getMessageByCode(ExceptionEnumCode.DB_ERR.getCode()), e);
+ }
+ }
+
+
@Transactional(propagation = Propagation.REQUIRED)
public int getSysReagentTotalCount(String name, String cas, String supplierId,Integer type) {
try {
@@ -81,6 +99,22 @@
}
}
+
+ public int getSysReagentTotalCountNew(String name, String cas, String supplierId,Integer type) {
+ try {
+ Map<String, Object> params = new HashMap<String, Object>();
+ params.put("name", name);
+ params.put("cas", cas);
+ params.put("type", type);
+ params.put("supplierId", supplierId);
+ return this.sysReagentDao.getSysReagentTotalCountNew(params);
+ } catch (DataAccessException e) {
+ logger.error(e.getMessage(), e);
+ throw new BusinessException(ExceptionEnumCode.DB_ERR, MessageUtil.getMessageByCode(ExceptionEnumCode.DB_ERR.getCode()), e);
+ }
+ }
+
+
@Transactional(propagation = Propagation.REQUIRED)
public List<SysReagent> getSysReagentList2(SysUser user,Integer favorFlag, String name, String cas, String productSn, String supplierId, Integer first, Integer pageSize) {
try {
diff --git a/src/main/webapp/reagent_mng.xhtml b/src/main/webapp/reagent_mng.xhtml
index 876d61e..d537c15 100644
--- a/src/main/webapp/reagent_mng.xhtml
+++ b/src/main/webapp/reagent_mng.xhtml
@@ -28,7 +28,6 @@
</p:selectOneMenu>
<p:outputLabel value="种类："></p:outputLabel>
<p:selectOneMenu value="#{reagentMngController.type}">
- <f:selectItem itemLabel="全部" itemValue="#{null}" noSelectionOption="true"></f:selectItem>
<f:selectItems value="#{reagentMngController.typeList}" var="item"
itemLabel="#{item.text}" itemValue="#{item.key}"></f:selectItems>
</p:selectOneMenu>
--
Gitblit v1.9.2