导出试剂流向，修改个人领用查询

李宇

2021-07-02 5758b702aceacd76f2a1e1a63d848cd4948e99e1

导出试剂流向，修改个人领用查询

 src/main/java/com/nanometer/smartlab/controller/OpeUseFlowInfoController.java

@@ -52,6 +52,8 @@

    private String containerCode;

    private String operatestate;

    public String getHouseName() {
        return houseName;
    }
@@ -105,10 +107,10 @@
                        Map<String, Object> filters) {
                    List<OpeUseFlow> list = null;
                    try {
                        int count = opeUseFlowService.getOpeUseFlowTotalCountByName(houseName,reagentId, reagentCode, containerCode,null, getUserId(),startDate,endDate);
                        int count = opeUseFlowService.getOpeUseFlowTotalCountByName(houseName,reagentId, reagentCode, containerCode,null, getUserId(),startDate,endDate,operatestate);
                        this.setRowCount(count);
                        if (count > 0) {
                            list = opeUseFlowService.getOpeUseFlowListByName(houseName,reagentId, reagentCode, containerCode,null, getUserId(),startDate,endDate, first, pageSize);
                            list = opeUseFlowService.getOpeUseFlowListByName(houseName,reagentId, reagentCode, containerCode,null, getUserId(),startDate,endDate, first, pageSize,operatestate);
                        }
                        selectedOne = null;
                    } catch (Exception e) {
@@ -138,6 +140,17 @@
        }

        return dataModel;
    }

    public void export2Excel() {

        List<Map> list = opeUseFlowService.selectAll(houseName,reagentId, reagentCode, containerCode,getUserId(),startDate,endDate);
        try{
            boolean isexport = opeUseFlowService.export2Excel(list);
        }catch (Exception e){
            e.printStackTrace();
            FacesUtils.warn("导出失败");
        }
    }

    public String getReagentId() {
@@ -275,4 +288,12 @@
    public void setSelectedOne(OpeUseFlow selectedOne) {
        this.selectedOne = selectedOne;
    }

    public void setOperatestate(String operatestate) {
        this.operatestate = operatestate;
    }

    public String getOperatestate(){
        return operatestate;
    }
}

 src/main/java/com/nanometer/smartlab/controller/PersonalUseInfoController.java

@@ -323,16 +323,27 @@
    }

    public Date getStartTime() {
        return startTime;
        if (null == startTime){
            Date now = new Date();
            Calendar cal = Calendar.getInstance();
            cal.setTime(now);
            cal.add(Calendar.DATE, -7);
            return cal.getTime();
        }else {
            return startTime;
        }
    }

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }


    public Date getEndTime() {
        return endTime;
        if (null == endTime){
            return new Date();
        }else {
            return endTime;
        }
    }

    public void setEndTime(Date endTime) {

 src/main/java/com/nanometer/smartlab/dao/OpeUseFlowDao.java

@@ -50,4 +50,6 @@
    OpeUseFlow selectById(@Param("id") String id);

    OpeUseFlow selectByReceiptNumber(Map params);

    List<Map> selectAll(Map params);
}

 src/main/java/com/nanometer/smartlab/dao/OpeUseFlowDao.xml

@@ -85,6 +85,9 @@
    <if test="endDate != null and endDate !=''">
        and oa.create_time &lt;= #{endDate}
    </if>
    <if test="operatestate != null and operatestate !=''">
        and bm1.id = #{operatestate}
    </if>
    <if test="containerCode != null and containerCode !=''">
        and wc.container_code = #{containerCode}
        or lc.container_code =#{containerCode}
@@ -361,6 +364,30 @@
        group by ouf.receipt_number
        ORDER BY ouf.create_time desc
    </select>
    <select id="selectAll" resultType="java.util.Map">
        select
        oa.reagent_code as reagentCode,
        sr.`name` as reagentName,
        CASE WHEN wc.container_code is NULL THEN lc.container_code ELSE wc.container_code END as laboratoryContainerCode,
        bm1.meta_value as operatestateName,
        CASE WHEN bm1.meta_value = '仓库入库' or bm1.meta_value='仓库领用' THEN NULL ELSE oa.remainder END as remainder,
        CASE WHEN w.name is NULL THEN l.name ELSE w.name END as warehouseContainerName,
        su.name as userName,
        oa.create_time as createTime
        from ope_use_flow as oa
        left join ope_reagent_status ors on ors.reagent_code = oa.reagent_code
        left join sys_reagent sr on ors.reagent_id = sr.id
        left join sys_supplier as ss on sr.supplier_id = ss.id
        left join sys_user as su on oa.user_id = su.id
        left join sys_warehouse_container wc on wc.id = oa.container_id
        left join sys_warehouse w on w.id = wc.warehouse_id
        left join sys_laboratory_container lc on lc.id = oa.container_id
        left join sys_laboratory l on l.id = lc.laboratory_id
        left join base_meta bm1 on bm1.id = oa.operatestate
        where oa.valid_flag = 1
        <include refid="queryWhereSql"/>
        order by oa.create_time desc
    </select>
    <insert id="insertOpeUseFlow" parameterType="com.nanometer.smartlab.entity.OpeUseFlow">
    insert into ope_use_flow(id, reagent_code, status, house_id, container_id, user_id, remainder, place, store_type, valid_flag, create_time, realstatus, operatestate
    <if test="receiptNumber != null">

 src/main/java/com/nanometer/smartlab/service/OpeUseFlowService.java

@@ -24,9 +24,9 @@
    int getOpeUseFlowTotalCount(String reagentId, String reagentCode, Integer status, String userId);

    List<OpeUseFlow> getOpeUseFlowListByName(String houseName,String reagentId, String reagentCode,String containerCode, Integer status, String userId, Date startDate,Date endDate,
            Integer first, Integer pageSize);
            Integer first, Integer pageSize,String operatestate);

    int getOpeUseFlowTotalCountByName(String houseName, String reagentId, String reagentCode,String containerCode, Integer status, String userId, Date startDate,Date endDate);
    int getOpeUseFlowTotalCountByName(String houseName, String reagentId, String reagentCode,String containerCode, Integer status, String userId, Date startDate,Date endDate,String operatestate);

    boolean update(OpeUseFlow opeUseFlow);

@@ -63,4 +63,8 @@
    OpeUseFlow selectById(String rowKey);

    OpeUseFlow selectByReceiptNumber(String receiptNumber,String userId);

    List<Map> selectAll(String houseName, String reagentId, String reagentCode, String containerCode, String userId, Date startDate, Date endDate);

    boolean export2Excel(List<Map> list) throws Exception;
}

 src/main/java/com/nanometer/smartlab/service/OpeUseFlowServiceImpl.java

@@ -13,6 +13,7 @@
import com.nanometer.smartlab.entity.dto.PersonUseDetail;
import com.nanometer.smartlab.entity.enumtype.OperateStatus;
import com.nanometer.smartlab.util.Constants;
import com.nanometer.smartlab.util.ExcelUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.log4j.Logger;
@@ -134,7 +135,7 @@

    @Transactional(propagation = Propagation.REQUIRED)
    public List<OpeUseFlow> getOpeUseFlowListByName(String houseName,String reagentId, String reagentCode,String containerCode, Integer status, String userId,
                                                    Date startDate,Date endDate,Integer first, Integer pageSize) {
                                                    Date startDate,Date endDate,Integer first, Integer pageSize,String operatestate) {
        try {
            Map<String, Object> params = new HashMap<String, Object>();

@@ -173,6 +174,7 @@

            params.put("status", status);
            params.put("houseName", houseName);
            params.put("operatestate", operatestate);
            params.put("containerCode", containerCode);
            addParamByUserId(userId, params);

@@ -187,7 +189,7 @@
    }

    @Transactional(propagation = Propagation.REQUIRED)
    public int getOpeUseFlowTotalCountByName(String houseName,String reagentId, String reagentCode,String containerCode, Integer status, String userId,Date startDate,Date endDate) {
    public int getOpeUseFlowTotalCountByName(String houseName,String reagentId, String reagentCode,String containerCode, Integer status, String userId,Date startDate,Date endDate,String operatestate) {
        try {
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("reagentName", reagentId);
@@ -224,6 +226,7 @@
                params.put("endDate",sdfend.format(now));
            }

            params.put("operatestate", operatestate);
            params.put("houseName", houseName);
            params.put("containerCode", containerCode);
            addParamByUserId(userId, params);
@@ -452,12 +455,39 @@
    }

    @Override
    public int getPersonalUseInfoCount(String reagentCode,String userId, Date startTime, Date endTime, String receiptNumber,
    public int getPersonalUseInfoCount(String reagentCode,String userId, Date startDate, Date endDate, String receiptNumber,
                                       String department, String project,String applyPerson,String reagentName) {
        Map<String,Object> params = new HashMap<>();

        SimpleDateFormat sdfstart = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
        SimpleDateFormat sdfend = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
        Date now = new Date();
        Calendar cal = Calendar.getInstance();
        cal.setTime(now);
        cal.add(Calendar.DATE, -7);
        Date startTime = cal.getTime();

        if (null != startDate){
            try {
                params.put("startDate",sdfstart.format(startDate));
            }catch (Exception e){
                e.printStackTrace();
            }
        }else {
            params.put("startDate",sdfstart.format(startTime));
        }

        if (null != endDate){
            try {
                params.put("endDate",sdfend.format(endDate));
            }catch (Exception e){
                e.printStackTrace();
            }
        }else {
            params.put("endDate",sdfend.format(now));
        }

        params.put("userId", userId);
        params.put("startTime", startTime);
        params.put("endTime", endTime);
        params.put("receiptNumber", receiptNumber);
        params.put("department", department);
        params.put("project", project);
@@ -480,12 +510,35 @@
    }

    @Override
    public List<OpeUseFlow> getPersonalUseInfoList(String reagentCode,String userId, Date startTime, Date endTime, String receiptNumber,
    public List<OpeUseFlow> getPersonalUseInfoList(String reagentCode,String userId, Date startDate, Date endDate, String receiptNumber,
                                                   String department, String project,String applyPerson,String reagentName, int first, int pageSize) {
        Map<String, Object> params = new HashMap<>();
        SimpleDateFormat sdfstart = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
        SimpleDateFormat sdfend = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
        Date now = new Date();
        Calendar cal = Calendar.getInstance();
        cal.setTime(now);
        cal.add(Calendar.DATE, -7);
        Date startTime = cal.getTime();
        if (null != startDate){
            try {
                params.put("startDate",sdfstart.format(startDate));
            }catch (Exception e){
                e.printStackTrace();
            }
        }else {
            params.put("startDate",sdfstart.format(startTime));
        }
        if (null != endDate){
            try {
                params.put("endDate",sdfend.format(endDate));
            }catch (Exception e){
                e.printStackTrace();
            }
        }else {
            params.put("endDate",sdfend.format(now));
        }
        params.put("userId", userId);
        params.put("startTime", startTime);
        params.put("endTime", endTime);
        params.put("receiptNumber", receiptNumber);
        params.put("department", department);
        params.put("project", project);
@@ -599,4 +652,65 @@
        return opeUseFlowDao.selectByReceiptNumber(params);
    }

    @Override
    public List<Map> selectAll(String houseName, String reagentId, String reagentCode, String containerCode, String userId, Date startDate, Date endDate) {
        try {
            Map<String, Object> params = new HashMap<String, Object>();

            params.put("reagentName", reagentId);
            if (StringUtils.isNotBlank(reagentCode)) {
                params.put("reagentCode", "%" + reagentCode + "%");
            }
            SimpleDateFormat sdfstart = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
            SimpleDateFormat sdfend = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
            Date now = new Date();
            Calendar cal = Calendar.getInstance();
            cal.setTime(now);
            cal.add(Calendar.DATE, -7);
            Date startTime = cal.getTime();

            if (null != startDate){
                try {
                    params.put("startDate",sdfstart.format(startDate));
                }catch (Exception e){
                    e.printStackTrace();
                }
            }else {
                params.put("startDate",sdfstart.format(startTime));
            }
            if (null != endDate){
                try {
                    params.put("endDate",sdfend.format(endDate));
                }catch (Exception e){
                    e.printStackTrace();
                }
            }else {
                params.put("endDate",sdfend.format(now));
            }
            params.put("houseName", houseName);
            params.put("containerCode", containerCode);
            addParamByUserId(userId, params);
            return opeUseFlowDao.selectAll(params);
        } catch (DataAccessException e) {
            logger.error(e.getMessage(), e);
            throw new BusinessException(ExceptionEnumCode.DB_ERR,
                    MessageUtil.getMessageByCode(ExceptionEnumCode.DB_ERR.getCode()), e);
        }
    }

    @Override
    public boolean export2Excel(List<Map> list) throws Exception {
        Map<String,String> map = new LinkedHashMap<>();
        map.put("reagentCode", "试剂条形码");
        map.put("reagentName", "试剂名称");
        map.put("laboratoryContainerCode", "柜号");
        map.put("operatestateName", "操作状态");
        map.put("remainder", "残存量");
        map.put("userName", "持有者");
        map.put("createTime", "更新时间");
        map.put("warehouseContainerName", "场所名");
        ExcelUtils.export2Excel(list,"试剂流向追踪列表",map);
        return true;
    }

}

 src/main/java/com/nanometer/smartlab/util/Constants.java

@@ -72,6 +72,7 @@
    public static final String BASE_META_GROUP_CONTRILLER_TYPE = "controller_type";
    public static final String SEQ_APPLY_CODE = "apply_code_seq";
    public static final String SEQ_ORDER_CODE = "order_code_seq";
    public static final String OPERATE_STATUS = "operate_status";

    public int getACTION_ADD() {
        return ACTION_ADD;
@@ -105,4 +106,5 @@
    public String getBASE_META_GROUP_PROJECT_NUM() {
        return BASE_META_GROUP_PROJECT_NUM;
    }
    public String getOPERATE_STATUS() { return OPERATE_STATUS; }
}

 src/main/webapp/apply_mng_new.xhtml

@@ -297,12 +297,6 @@
                        <p:commandLink styleClass="search reagentSearch" process="@form" update="@form"></p:commandLink>
                    </p:panelGrid>

                    <p:commandButton value="新增试剂" process="@this"
                                     actionListener="#{applyMngController.onReagentNewBtnClick}"
                                     oncomplete="PF('dialog').show();"
                                     update="@parent:@parent:@parent:dialog"
                                     styleClass="new-btn" style="position: absolute;display: none;right: 42px;top:62px"></p:commandButton>

                    <p:dataTable id="applyMngNewReagentDataTable" styleClass="data-table" style="margin-left: 2px"
                                 resizableColumns="true" paginator="true" paginatorAlwaysVisible="false" paginatorPosition="bottom"
                                 lazy="true" value="#{applyMngController.reagentDataModel}" var="row" rowKey="#{row.id}"

 src/main/webapp/personal_use_info.xhtml

@@ -66,9 +66,9 @@
                    <p:inputText value="#{personalUseInfoController.department}"/>

                    <p:outputLabel for="startTime" value="开始时间:" />
                    <p:calendar id="startTime" value="#{personalUseInfoController.startTime}" pattern="yyyy-MM-dd HH:mm" />
                    <p:calendar id="startTime" value="#{personalUseInfoController.startTime}" />
                    <p:outputLabel for="endTime" value="截止时间:" />
                    <p:calendar id="endTime" value="#{personalUseInfoController.endTime}" pattern="yyyy-MM-dd HH:mm" />
                    <p:calendar id="endTime" value="#{personalUseInfoController.endTime}" />

                    <p:commandLink styleClass="search" process="@form" update="@form"/>


 src/main/webapp/reagent_user_flow_info.xhtml

@@ -15,7 +15,15 @@
            <p:outputLabel styleClass="title" value="试剂流向追踪"></p:outputLabel>

            <p:panel styleClass="center-header">
                <p:panelGrid styleClass="filter" columns="13">
                <p:panelGrid styleClass="filter" columns="16">
                    <style type="text/css">
                        .ui-selectonemenu.ui-widget.ui-state-default.ui-corner-all{
                            min-width: 60px !important;
                        }
                        .ui-selectonemenu-items.ui-selectonemenu-list.ui-widget-content.ui-widget.ui-corner-all.ui-helper-reset{
                            width: 77px !important;
                        }
                    </style>
                    <p:outputLabel value="开始时间："></p:outputLabel>
                    <p:calendar value="#{opeUseFlowInfoController.startDate}"></p:calendar>

@@ -33,6 +41,15 @@

                    <p:outputLabel value="场所名："></p:outputLabel>
                    <p:inputText value="#{opeUseFlowInfoController.houseName}"></p:inputText>

                    <p:outputLabel value="操作状态："></p:outputLabel>
                    <p:selectOneMenu value="#{opeUseFlowInfoController.operatestate}" style="width: 60px;">
                        <f:selectItem itemLabel="全部" itemValue="#{null}" noSelectionOption="true"></f:selectItem>
                        <f:selectItems value="#{baseMetaService.getBaseMetaList(constants.OPERATE_STATUS)}"
                                       var="item" itemLabel="#{item.metaValue}" itemValue="#{item.id}"></f:selectItems>
                    </p:selectOneMenu>


                    <p:commandLink styleClass="search" process="@form" update="@form"></p:commandLink>
                </p:panelGrid>
            </p:panel>
@@ -40,8 +57,10 @@


        <p:panel styleClass="center-body">
            <p:panelGrid columns="1" styleClass="btn">
            <p:panelGrid columns="2" styleClass="btn">
                <p:commandButton value="报废" styleClass="del-btn"  ajax="false" actionListener="#{opeUseFlowInfoController.scrapReagent}" />
                <p:commandButton value="导出" styleClass="new-btn"  ajax="false" id="exportButton" widgetVar="exportButton"
                                 actionListener="#{opeUseFlowInfoController.export2Excel}" />
            </p:panelGrid>
            <p:dataTable id="reagentUseFlowInfoDataTable" styleClass="data-table"
                paginator="true" paginatorAlwaysVisible="false" paginatorPosition="bottom"