导入以及导入模板下载

lhf@@yxg

已于 2022-05-30 15:59:52 修改

阅读量2k

点赞数

文章标签： java

于 2022-03-30 18:15:58 首次发布

本文链接：https://blog.csdn.net/weixin_49143405/article/details/123853046

版权

一，前端html

@layout("/common/_container.html",{js:["/dict/dictjs/CANDIDATE_STATUS","/assets/modular/system/expert/expert.js"]}){

<div class="layui-body-header">
    <span class="layui-body-header-title">专家管理</span>
</div>
<div class="layui-fluid">
    <div class="layui-row layui-col-space15">
        <div class="layui-col-lg12">
            <div class="layui-card">
                <div class="layui-card-body">
                    <div class="layui-form toolbar">
                        <div class="layui-form-item">
                            <div class="layui-inline">
                                <input id="condition" class="layui-input" type="text" placeholder="关键字"/>
                                <input id="databaseId" type="hidden" value="${shiro.getCompanyId()}">
                            </div>
                            <div class="layui-inline">
                                <button id="btnSearch" class="layui-btn icon-btn"><i class="layui-icon">&#xe615;</i>搜索
                                </button>
                                <button id="btnAdd" class="layui-btn icon-btn"><i class="layui-icon">&#xe654;</i>添加
                                </button>
                                <button id="btnExp" class="layui-btn icon-btn"><i class="layui-icon">&#xe67d;</i>导出
                                </button>
                                <button id="inExp" class="layui-btn icon-btn"><i class="layui-icon">&#xe67d;</i>导入
                                </button>
<!--                                <input type="button" class="layui-btn" value="导入" onclick="impFirst();">-->
                                <input type="button" style="margin-left: 10px;" class="layui-btn" value="下载模版" onclick="expert();">
                            </div>
                        </div>
                    </div>
                    <table class="layui-table" id="expertTable" lay-filter="expertTable"></table>
                </div>
            </div>
        </div>
    </div>
</div>

<script type="text/html" id="tableBar">
    <a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="view">查看</a>
    <a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="edit">修改</a>
    <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="delete">删除</a>
</script>


@}

二，js

下载模板制作：

layui.use(['layer', 'form', 'table', 'admin', 'ax', 'func','upload'], function () {
    var $ = layui.$;
    var layer = layui.layer;
    var form = layui.form;
    var table = layui.table;
    var $ax = layui.ax;
    var admin = layui.admin;
    var func = layui.func;
    var upload = layui.upload;

    /**
     * 导入模板下载 在当前程序的static里面创建一个xlsx文档作为模板
     */
    expert = function () {
        window.location.href = Feng.ctxPath + "/projectInfo/downModel?modelName=expert.xlsx";
    }

    /**
     * 系统管理--消息管理
     */
    var Expert = {
        tableId: "expertTable"    //表格id
    };

    /**
     * 初始化表格的列
     */
    Expert.initColumn = function () {
        return [[
            {type: 'checkbox'},
            {field: 'name', align: "center", title: '专家姓名', width: 100},
            {field: 'phone', align: "center", title: '联系号码', width: 150},
            {field: 'idCode', align: "center", title: '身份证号', width: 200},
            {field: 'majorName', align: "center", title: '专业'},
            {
                align: "center", title: '地区', width: 250, templet: function (d) {
                    var arr = [];
                    if (!d.province) {
                        arr.push('不限');
                    } else if (!d.city) {
                        arr.push(d.province, '不限');
                    } else if (!d.district) {
                        arr.push(d.province, d.city, '不限');
                    } else {
                        arr.push(d.province, d.city, d.district);
                    }
                    return arr.join('/');
                }
            },
            {align: 'center', toolbar: '#tableBar', title: '操作', width: 200}
        ]];
    };

    /**
     * 点击查询按钮
     */
    Expert.search = function () {
        var queryData = {};
        queryData['condition'] = $("#condition").val();
        table.reload(Expert.tableId, {
            where: queryData, page: {curr: 1}
        });
    };

    /**
     * 弹出添加通知
     */
    Expert.openAddExpert = function () {
        //window.location.href = Feng.ctxPath + '/expert/add';
        func.open({
            height: 650,
            title: '添加专家',
            content: Feng.ctxPath + '/expert/add',
            tableId: Expert.tableId
        });
    };

    Expert.onViewExpert = function (data){
        func.open({
            height: 500,
            title: '专家详情',
            content: Feng.ctxPath + '/expert/view/' + data.id,
            tableId: Expert.tableId
        });
    }

    /**
     * 点击编辑通知
     *
     * @param data 点击按钮时候的行数据
     */
    Expert.onEditExpert = function (data) {
        //window.location.href = Feng.ctxPath + "/expert/edit?id=" + data.expertId;
        func.open({
            height: 650,
            title: '添加专家',
            content: Feng.ctxPath + '/expert/edit?id=' + data.id,
            tableId: Expert.tableId
        });
    };

    /**
     * 点击删除通知
     *
     * @param data 点击按钮时候的行数据
     */
    Expert.onDeleteExpert = function (data) {
        var operation = function () {
            var ajax = new $ax(Feng.ctxPath + "/expert/delete", function (data) {
                Feng.success("删除成功!");
                table.reload(Expert.tableId);
            }, function (data) {
                Feng.error("删除失败!" + data.responseJSON.message + "!");
            });
            ajax.set("id", data.id);
            ajax.start();
        };
        Feng.confirm("是否删除专家 " + data.name + "?", operation);
    };

    // 渲染表格
    var tableResult = table.render({
        elem: '#' + Expert.tableId,
        url: Feng.ctxPath + '/expert/list',
        page: true,
        height: "full-98",
        cellMinWidth: 100,
        cols: Expert.initColumn(),
        where: {databaseId: $('#databaseId').val()}
    });

    /**
     * 导出excel按钮
     */
    Expert.exportExcel = function () {
        var checkRows = table.checkStatus(Expert.tableId);
        console.log("2445256367")
        if (checkRows.data.length === 0) {
            Feng.error("请选择要导出的数据");
        } else {
            table.exportFile(tableResult.config.id, checkRows.data, 'xls');
        }
    };

   //专家管理导入
    upload.render({
        elem: '#inExp'
        , url: Feng.ctxPath + '/expert/ImportFirst'
        , accept: 'file'
        , exts: 'xls|xlsx'
        , done: function (res) {
          //导入成功后自动刷新页面
            Expert.search();
            Feng.success("导入成功",res.message);

        }
        , error: function (e) {
            layer.msg("导入失败！",e.message)
        }
    })

    /**
         * 下载excel模板
         */
        Expert.template = function () {
            var checkRows = table.checkStatus(Expert.tableId);
            if (checkRows.data.length === 0) {
                Feng.error("请选择要导出的数据");
            } else {
                table.exportFile(tableResult.config.id, checkRows.data, 'xls');
            }
        };


        // 导出excel
        $('#btnExp').click(function () {
            Expert.exportExcel();
        });

        /**
         * 导入excel 页面
         */
        $('#inExp').click (function () {
            var url='/expert/ImportFirst';
            this.layerIndex = Feng.importExcel(url);
        });

    // 搜索按钮点击事件
    $('#btnSearch').click(function () {
        Expert.search();
    });

    // 添加按钮点击事件
    $('#btnAdd').click(function () {
        Expert.openAddExpert();
    });

    // 工具条点击事件
    table.on('tool(' + Expert.tableId + ')', function (obj) {
        var data = obj.data;
        var layEvent = obj.event;
        if (layEvent === 'view'){
            Expert.onViewExpert(data);
        }else if (layEvent === 'edit') {
            Expert.onEditExpert(data);
        } else if (layEvent === 'delete') {
            Expert.onDeleteExpert(data);
        }
    });
});

三，后端实体类

package cn.stylefeng.guns.sys.modular.system.model.params;

import cn.stylefeng.roses.kernel.model.validator.BaseValidatingParam;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

@Data
public class ExpertParam implements Serializable, BaseValidatingParam {
    private Long id;

    /**
     * 姓名
     */
    private String name;

    /**
     * 身份证号
     */
    private String idCode;

    /**
     * 电话
     */
    private String phone;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 头像
     */
    private String avatar;

    /**
     * 密码
     */
    private String password;

    /**
     * 标签
     */
    private String tags;

    /**
     * 专业
     */
    private String major;

    /**
     * 技术职称
     */
    private String technicalTitle;

    /**
     * 专家级别
     */
    private String expertLevel;

    /**
     * 所属区域-省
     */
    private String province;

    /**
     * 所属区域-地市
     */
    private String city;

    /**
     * 所属区域-区县
     */
    private String district;

    /**
     * 是否在职
     */
    private Boolean onJob;

    /**
     * 在职公司
     */
    private String company;

    /**
     * 所属专家库id
     */
    private Long databaseId;

    /**
     * 状态(字典)
     */
    private String status;

    /**
     * 创建时间
     */
    private Date createTime;

    /**
     * 创建人
     */
    private Long createUser;

    /**
     * 更新时间
     */
    private Date updateTime;

    /**
     * 更新人
     */
    private Long updateUser;

    /**
     * 技能证书Id
     */
    private String certificates;


    /**
     * 查询关键字
     */
    private String condition;

    /**
     * 需要排除的id
     */
    private List<Long> excludeIds;

    /**
     * 需要排除的公司名称
     */
    private List<String> excludeCompanys;
}

四 controller

package cn.stylefeng.guns.sys.modular.system.controller;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import cn.stylefeng.guns.base.auth.context.LoginContextHolder;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.sys.core.util.SaltUtil;
import cn.stylefeng.guns.sys.modular.system.context.DictContext;
import cn.stylefeng.guns.sys.modular.system.entity.CompanyInfo;
import cn.stylefeng.guns.sys.modular.system.entity.Dict;
import cn.stylefeng.guns.sys.modular.system.entity.Expert;
import cn.stylefeng.guns.sys.modular.system.entity.ExpertTag;
import cn.stylefeng.guns.sys.modular.system.model.params.ExpertParam;
import cn.stylefeng.guns.sys.modular.system.service.CompanyInfoService;
import cn.stylefeng.guns.sys.modular.system.service.DictService;
import cn.stylefeng.guns.sys.modular.system.service.ExpertService;
import cn.stylefeng.guns.sys.modular.system.service.ExpertTagService;
import cn.stylefeng.roses.core.base.controller.BaseController;
import cn.stylefeng.roses.kernel.model.response.ResponseData;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * 区域信息表控制器
 *
 */
@Controller
@RequestMapping("/expert")
public class ExpertController extends BaseController {

    private String PREFIX = "/modular/system/expert";

    private final static String excel2003L = "xls"; // 2003- 版本的excel
    private final static String excel2007U = "xlsx"; // 2007+ 版本的excel

    @Autowired
    private ExpertService expertService;

    @Autowired
    private ExpertTagService expertTagService;

    @Autowired
    private DictService dictService;

    @Autowired
    private  CompanyInfoService companyInfoService;


    /**
     * 跳转到主页面
     *
     */
    @RequestMapping("")
    public String index() {
        return PREFIX + "/expert.html";
    }

    /**
     * 新增页面
     *
     */
    @RequestMapping("/add")
    public String add() {
        return PREFIX + "/expert_edit.html";
    }

    /**
     * 编辑页面
     *
     */
    @RequestMapping("/edit")
    public String edit() {
        return PREFIX + "/expert_edit.html";
    }

    /**
     * 新增接口
     *
     */
    @RequestMapping("/addItem")
    @ResponseBody
    public ResponseData addItem(ExpertParam expertParam) {
        this.expertService.add(expertParam);
        return ResponseData.success();
    }

    /**
     * 编辑接口
     *
     */
    @RequestMapping("/editItem")
    @ResponseBody
    public ResponseData editItem(ExpertParam expertParam) {
        this.expertService.update(expertParam);
        return ResponseData.success();
    }

    /**
     * 删除接口
     *
     */
    @RequestMapping("/delete")
    @ResponseBody
    public ResponseData delete(ExpertParam expertParam) {
        this.expertService.delete(expertParam);
        return ResponseData.success();
    }

    /**
     * 查看详情接口
     *
     */
    @RequestMapping("/detail")
    @ResponseBody
    public ResponseData detail(ExpertParam expertParam) {
        Map<String, Object> info = this.expertService.loadInfo(expertParam.getId());
        return ResponseData.success(info);
    }

    /**
     * 查询列表
     *
     */
    @ResponseBody
    @RequestMapping("/list")
    public LayuiPageInfo list(ExpertParam expertParam) {
        return this.expertService.findPageBySpec(expertParam);
    }


    @RequestMapping("/view/{id}")
    public String view(@PathVariable Long id, Model model){
        Map<String, Object> data = this.expertService.loadInfo(id);
        Expert expert = (Expert) data.get("expert");
        //专家的技术职称编码
        String[] technicalCodes = expert.getTechnicalTitle().replaceAll("\\[|\\]", "").split(",");

        //专家的技术职称名称（通过编码获取名称）
        List<String> technicalNames = new ArrayList<>(technicalCodes.length);
        for (String technicalCode : technicalCodes) {
            technicalNames.add(DictContext.getDictName("TECHNICAL_TITLE", technicalCode));
        }
        data.put("technicalNames", technicalNames);
        //专家级别名称
        String levelName = DictContext.getDictName("EXPERT_LEVEL", expert.getExpertLevel());
        data.put("levelName", levelName);

        //专家标签
        if(StrUtil.isNotBlank(expert.getTags())){
            ArrayList<String> ids = CollectionUtil.newArrayList(expert.getTags().split(","));
            List<ExpertTag> tags = expertTagService.listByIds(ids);
            data.put("tags", tags);
        }
        model.addAllAttributes(data);
        return PREFIX + "/expert_view.html";
    }

    /**
     * 专家导入
     * @param file
     * @return
     */
    @RequestMapping("/ImportFirst")
    @ResponseBody
    public ResponseData ImportFirst(@RequestParam("file") MultipartFile file,
                                    String identityType,String supplierName,ExpertParam expertParam,String idCode){
        if (!file.isEmpty()) {
            try {
                //获取原始的文件名
                String originalFilename = file.getOriginalFilename();
                String fileType = originalFilename.substring(originalFilename.
                        lastIndexOf(".") + 1);
                //获取输入流
                InputStream is = file.getInputStream();
                Sheet sheet;
                if (excel2003L.equals(fileType)) {
                    HSSFWorkbook work = new HSSFWorkbook(is); // 2003-
                    sheet = work.getSheetAt(0);//暂时只取第一页
                } else if (excel2007U.equals(fileType)) {
                    XSSFWorkbook work = new XSSFWorkbook(is); // 2007+
                    sheet = work.getSheetAt(0);//暂时只取第一页
                } else {
                    return ResponseData.error("格式不符");
                }
                List<Map> result = new ArrayList();
                if (sheet.getLastRowNum() > 0) {
                    //跳过标题栏
//                    Map map = new HashMap();
                    for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                        Expert expert = new Expert();
                        Row row = sheet.getRow(j);
                        expert.setName(getCellValue(row.getCell(0)));
                        expert.setPhone(getCellValue(row.getCell(1)));
                        expert.setIdCode(getCellValue(row.getCell(2)));
                        expert.setMajor(getCellValue(row.getCell(3)));
                        expert.setProvince(getCellValue(row.getCell(4)));
                        expert.setCity(getCellValue(row.getCell(5)));
                        expert.setDistrict(getCellValue(row.getCell(6)));
                        expert.setTechnicalTitle(getCellValue(row.getCell(7)));
                        expert.setExpertLevel(getCellValue(row.getCell(8)));
                        expert.setProvince(getCellValue(row.getCell(9)));
                        expert.setCompany(getCellValue(row.getCell(11)));
                        expert.setStatus("ENABLE");
                        //id
                        //2.身份证相同的更新，身份证查询，一样的更新数据
                        //3.校验，专家库id改成专家库，填写数据，查询cez_company_info，如果没有，就提示XX行没有该专家库，存在就吧id存入databaseId
                        QueryWrapper<CompanyInfo> condition = new QueryWrapper();
                        condition.eq("company_name",getCellValue(row.getCell(10)))
                                .select("id", "company_name","province","city");
                        CompanyInfo company= companyInfoService.getOne(condition);

                        //校验专家库是否为空
                        if(Objects.isNull(company)){
                            return ResponseData.error("第"+j+"行专家库不存在");
                        }else{
                            //不为空执行下面对数据的操作
                            expert.setDatabaseId(company.getId());
                            //chaxunshenfenz
                            Expert expertQuery = expertService.findByIdCode(getCellValue(row.getCell(2)));
                            //判断身份证号是否为空
                            //为空执行新增操作
                            if(Objects.isNull(expertQuery)){
                                // 生成id
                                Long id = IdUtil.getSnowflake(1, 1).nextId();
                                expert.setId(id);
                                expert.setCreateTime(DateUtil.date());
                                expert.setCreateUser(LoginContextHolder.getContext().getUser().getId());
                                expertService.insertList(expert);
                            }else{
                                //不为空执行修改操作
                                //update
                                expert.setId(expertQuery.getId());
                                expert.setUpdateTime(DateUtil.date());
                                expert.setUpdateUser(LoginContextHolder.getContext().getUser().getId());
                                expertService.updateList(expert);
                            }
                        }
                    }
                    return ResponseData.success(0, "上传成功", result);
                }
            } catch (IOException e) {
                e.printStackTrace();
                return ResponseData.error(e.getMessage());
            }
        }
        return ResponseData.error("文件不存在");
    }
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    // 验证short值
                    if (cell.getCellStyle().getDataFormat() == 14) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else if (cell.getCellStyle().getDataFormat() == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (cell.getCellStyle().getDataFormat() == 22) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    } else {
                        throw new RuntimeException("日期格式错误!!!");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = null;
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}

五 service

package cn.stylefeng.guns.sys.modular.system.service;

import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.sys.modular.system.entity.CompanyInfo;
import cn.stylefeng.guns.sys.modular.system.entity.Expert;
import cn.stylefeng.guns.sys.modular.system.model.params.ExpertParam;
import cn.stylefeng.guns.sys.modular.system.model.result.ExpertResult;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.IService;
import org.apache.ibatis.annotations.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 专家 服务类
 * </p>
 *
 */
public interface ExpertService extends IService<Expert> {

    /**
     * 新增
     *
     */
    @Transactional(rollbackFor = Exception.class)
    void add(ExpertParam param);

    /**
     * 删除
     *
     */
    @Transactional(rollbackFor = Exception.class)
    void delete(ExpertParam param);

    /**
     * 更新
     *
     */
    @Transactional(rollbackFor = Exception.class)
    void update(ExpertParam param);

    /**
     * 查询单条数据，Specification模式
     */
    ExpertResult findBySpec(ExpertParam param);

    /**
     * 查询列表，Specification模式
     *
     */
    List<ExpertResult> findListBySpec(ExpertParam param);


    /**
     * 查询分页数据，Specification模式
     */
    LayuiPageInfo findPageBySpec(ExpertParam param);


    /**
     * 加载信息（包含证件信息和专业信息）
     *
     * @param id
     * @return
     */
    Map<String, Object> loadInfo(Long id);

    /**
     * 根据身份证查询
     *
     * @param idCode
     * @return
     */
    Expert findByIdCode(@Param("idCode") String idCode);

    /**
     * 随机抽取专家
     * @param param
     * @param size
     * @return
     */
    List<ExpertResult> randomSelect(ExpertParam param, Integer size);

    /**
     * 专家导入并且入库
     * @param
     * @return
     */
    Boolean insertList(Expert expert);

    /**
     * 如果身份证相同则更新数据
     * @param
     */
    Boolean updateList(Expert expert);

}

六 servicemip

package cn.stylefeng.guns.sys.modular.system.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.stylefeng.guns.base.auth.context.LoginContextHolder;
import cn.stylefeng.guns.base.auth.model.LoginUser;
import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.sys.core.constant.state.ManagerStatus;
import cn.stylefeng.guns.sys.core.exception.enums.BizExceptionEnum;
import cn.stylefeng.guns.sys.core.util.SaltUtil;
import cn.stylefeng.guns.sys.modular.system.entity.CompanyInfo;
import cn.stylefeng.guns.sys.modular.system.entity.Dict;
import cn.stylefeng.guns.sys.modular.system.entity.Expert;
import cn.stylefeng.guns.sys.modular.system.entity.FileInfo;
import cn.stylefeng.guns.sys.modular.system.mapper.ExpertMapper;
import cn.stylefeng.guns.sys.modular.system.model.params.ExpertParam;
import cn.stylefeng.guns.sys.modular.system.model.result.ExpertMajorResult;
import cn.stylefeng.guns.sys.modular.system.model.result.ExpertResult;
import cn.stylefeng.guns.sys.modular.system.service.ExpertMajorService;
import cn.stylefeng.guns.sys.modular.system.service.ExpertService;
import cn.stylefeng.guns.sys.modular.system.service.FileInfoService;
import cn.stylefeng.guns.sys.modular.system.warpper.ExpertWrapper;
import cn.stylefeng.roses.core.util.ToolUtil;
import cn.stylefeng.roses.kernel.model.exception.ServiceException;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 专家 服务实现类
 * </p>
 *
 */
@Service
public class ExpertServiceImpl extends ServiceImpl<ExpertMapper, Expert> implements ExpertService {

    private static final String CERT_FILE_REF_CLASS = "expert_cert";

    @Autowired
    private FileInfoService fileInfoService;

    @Autowired
    private ExpertMajorService expertMajorService;

    @Override
    public void add(ExpertParam param) {
        Expert theExpert = this.findByIdCode(param.getIdCode());
        if (theExpert != null) {
            throw new ServiceException(BizExceptionEnum.USER_ALREADY_REG);
        }

        Expert entity = getEntity(param);
        // 完善账号信息
        LoginUser user = LoginContextHolder.getContext().getUser();
        entity.setDatabaseId(user.getCompanyId());
        String salt = SaltUtil.getRandomSalt();
        //初始密码为手机号
        String password = SaltUtil.md5Encrypt(param.getPhone(), salt);
        entity.setSalt(salt);
        entity.setPassword(password);
        entity.setStatus(ManagerStatus.OK.getCode());

        this.save(entity);

        if (StrUtil.isNotBlank(param.getCertificates())) {
            //更新技术证书附件的关联关系
            String[] fileIds = param.getCertificates().split(",");
            UpdateWrapper<FileInfo> wrapper = new UpdateWrapper<>();
            wrapper.set("ref_id", entity.getId()).set("ref_class", CERT_FILE_REF_CLASS);
            wrapper.in("file_id", fileIds);
            fileInfoService.update(wrapper);
        }

        param.setId(entity.getId());
    }

    @Override
    public void delete(ExpertParam param) {
        //this.removeById(getKey(param));
        UpdateWrapper<Expert> updateWrapper = new UpdateWrapper<>();
        updateWrapper.set("status", ManagerStatus.DELETED.getCode()).eq("id", param.getId());
        this.update(updateWrapper);
        //删除关联的技术证书附件
        QueryWrapper<FileInfo> wrapper = new QueryWrapper<>();
        wrapper.eq("ref_id", param.getId()).eq("ref_class", CERT_FILE_REF_CLASS);
        fileInfoService.remove(wrapper);
    }

    @Override
    public void update(ExpertParam param) {
        Expert oldEntity = getOldEntity(param);
        Expert newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);

        //查询原有的技术证书附件
        QueryWrapper<FileInfo> wrapper = new QueryWrapper<>();
        wrapper.eq("ref_id", param.getId()).eq("ref_class", CERT_FILE_REF_CLASS);
        List<FileInfo> oldFiles = fileInfoService.list(wrapper);
        List<String> oldFileIds = new ArrayList<>();
        oldFiles.forEach(e -> oldFileIds.add(e.getFileId()));
        List<String> newFileIds = new ArrayList<>();
        if (StrUtil.isNotBlank(param.getCertificates())) {
            String[] fileIds = param.getCertificates().split(",");
            for (String fileId : fileIds) {
                if (oldFileIds.contains(fileId)) {
                    //从原有附件id中排除需要继续保留的id
                    oldFileIds.remove(fileId);
                } else {
                    //新上传的附件id
                    newFileIds.add(fileId);
                }
            }
        }

        if (CollectionUtil.isNotEmpty(oldFileIds)) {
            //删除不需要继续保留的原有技术证书
            fileInfoService.removeByIds(oldFileIds);
        }

        if (CollectionUtil.isNotEmpty(newFileIds)) {
            //更新新上传的技术证书附件的关联关系
            UpdateWrapper<FileInfo> updateWrapper = new UpdateWrapper<>();
            updateWrapper.set("ref_id", param.getId()).set("ref_class", CERT_FILE_REF_CLASS);
            updateWrapper.in("file_id", newFileIds);
        }
    }

    @Override
    public ExpertResult findBySpec(ExpertParam param) {
        List<ExpertResult> list = findListBySpec(param);
        return CollectionUtil.isNotEmpty(list) ? list.get(0) : null;
    }

    @Override
    public List<ExpertResult> findListBySpec(ExpertParam param) {
        return this.baseMapper.customList(param);
    }


    @Override
    public LayuiPageInfo findPageBySpec(ExpertParam param) {
        Page pageContext = getPageContext();
        Page<Map<String, Object>> page = this.baseMapper.customPageMapList(pageContext, param);
        return LayuiPageFactory.createPageInfo(new ExpertWrapper(page).wrap());
    }

    @Override
    public Map<String, Object> loadInfo(Long id) {
        Map<String, Object> result = new HashMap<>();
        Expert entity = getById(id);
        //去掉敏感字段
        entity.setPassword(null);
        entity.setSalt(null);
        result.put("expert", entity);

        //查询专业
        List<ExpertMajorResult> expertMajors = expertMajorService.findByCode(entity.getMajor());
        List<Map<String, String>> majors = new ArrayList<>();
        expertMajors.forEach(e -> majors.add(toXmSelectItem(e)));
        result.put("majors", majors);

        //查询关联的技能证书
        QueryWrapper<FileInfo> wrapper = new QueryWrapper<>();
        wrapper.eq("ref_id", id).eq("ref_class", CERT_FILE_REF_CLASS);
        List<FileInfo> certs = fileInfoService.list(wrapper);
        result.put("certs", certs);
        return result;
    }

    private Map<String, String> toXmSelectItem(ExpertMajorResult result) {
        Map<String, String> item = new HashMap<>();
        item.put("value", String.format("[%s]", result.getCode()));
        String[] tmpArr = result.getName().split("--");
        item.put("name", tmpArr[tmpArr.length - 1]);
        return item;
    }

    @Override
    public Expert findByIdCode(String idCode) {
        return this.baseMapper.findByIdCode(idCode);
    }


    /**
     * 根据条件随机抽取专家
     * @param param
     * @param size
     * @return
     */
    @Override
    public List<ExpertResult> randomSelect(ExpertParam param, Integer size) {
        return baseMapper.randomSelect(param,size);
    }

    /**
     * 专家导入
     * @param
     * @return
     */
    @Override
    public Boolean insertList(Expert entity) {
        // 完善账号信息
        String salt = SaltUtil.getRandomSalt();
        //初始密码为手机号
        String password = SaltUtil.md5Encrypt((entity.getPhone()),salt);
        entity.setSalt(salt);
        entity.setPassword(password);
        entity.setStatus(ManagerStatus.OK.getCode());
        return this.save(entity);
    }

    @Override
    public Boolean updateList(Expert expert) {
        return this.updateById(expert);
    }

    private Serializable getKey(ExpertParam param) {
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private Expert getOldEntity(ExpertParam param) {
        return this.getById(getKey(param));
    }

    private Expert getEntity(ExpertParam param) {
        Expert entity = new Expert();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }

}

七 mapper

package cn.stylefeng.guns.sys.modular.system.mapper;

import cn.stylefeng.guns.sys.modular.system.entity.Expert;
import cn.stylefeng.guns.sys.modular.system.model.params.ExpertParam;
import cn.stylefeng.guns.sys.modular.system.model.result.ExpertResult;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 区域信息表 Mapper 接口
 * </p>
 *
 */
public interface ExpertMapper extends BaseMapper<Expert> {



    /**
     * 获取列表
     *
     */
    List<ExpertResult> customList(@Param("paramCondition") ExpertParam paramCondition);

    /**
     * 获取map列表
     *
     */
    List<Map<String, Object>> customMapList(@Param("paramCondition") ExpertParam paramCondition);

    /**
     * 获取分页实体列表
     *
     */
    Page<ExpertResult> customPageList(@Param("page") Page page, @Param("paramCondition") ExpertParam paramCondition);

    /**
     * 获取分页map列表
     *
     */
    Page<Map<String, Object>> customPageMapList(@Param("page") Page page, @Param("paramCondition") ExpertParam paramCondition);

    /**
     * 根据身份证查询
     * @param idCode
     * @return
     */
    Expert findByIdCode(@Param("idCode") String idCode);


    /**
     * 随机抽取专家
     * @param param
     * @param size
     * @return
     */
    List<ExpertResult> randomSelect(@Param("paramCondition") ExpertParam param, Integer size);

    /**
     * 导入专家信息
     * @param result
     * @return
     */
    int insertList(List<Map> result);

    /**
     * 当身份证号相同时进行数据更新操作
     * @param result
     * @return
     */
    int updateList(List<Map> result);

     Expert getIdCode(String idCode);
}

八 xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.stylefeng.guns.sys.modular.system.mapper.ExpertMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.stylefeng.guns.sys.modular.system.entity.Expert">
        <id column="id" property="id" />
        <result column="avatar" property="avatar" />
        <result column="id_code" property="idCode" />
        <result column="password" property="password" />
        <result column="salt" property="salt" />
        <result column="name" property="name" />
        <result column="email" property="email" />
        <result column="phone" property="phone" />
        <result column="tags" property="tags" />
        <result column="major" property="major" />
        <result column="technical_title" property="technicalTitle" />
        <result column="expert_level" property="expertLevel" />
        <result column="province" property="province" />
        <result column="city" property="city" />
        <result column="district" property="district" />
        <result column="on_job" property="onJob" />
        <result column="company" property="company" />
        <result column="database_id" property="databaseId" />
        <result column="status" property="status" />
        <result column="create_time" property="createTime" />
        <result column="create_user" property="createUser" />
        <result column="update_time" property="updateTime" />
        <result column="update_user" property="updateUser" />
        <result column="version" property="version" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id AS "id", avatar AS "avatar", id_code AS "idCode", name AS "name", email AS "email", phone AS "phone", tags AS "tags", major AS "major", technical_title AS "technicalTitle", expert_level AS "expertLevel", province AS "province", city AS "city", district AS "district", on_job AS "onJob", company AS "company", database_id AS "databaseId", status AS "status", create_time AS "createTime", create_user AS "createUser", update_time AS "updateTime", update_user AS "updateUser"
    </sql>

    <sql id="All_Column_List">
        id AS "id", avatar AS "avatar", id_code AS "idCode", password AS "password", salt AS "salt", name AS "name", email AS "email", phone AS "phone", tags AS "tags", major AS "major", technical_title AS "technicalTitle", expert_level AS "expertLevel", province AS "province", city AS "city", district AS "district", on_job AS "onJob", company AS "company", database_id AS "databaseId", status AS "status", create_time AS "createTime", create_user AS "createUser", update_time AS "updateTime", update_user AS "updateUser", version AS "version"
    </sql>

    <sql id="Common_Query_Condition">
        <if test="paramCondition.databaseId != null">
            and database_id = #{paramCondition.databaseId}
        </if>
        <if test="paramCondition.tags != null and paramCondition.tags != ''">
            and tags like concat('%', #{paramCondition.tags}, '%')
        </if>
        <if test="paramCondition.major != null and paramCondition.major != ''">
            and (
            <foreach collection="paramCondition.major.split(',')" item="i" open="(" separator="or" close=")">
                major like concat('%[', #{i}, ']%')
            </foreach>
            )
        </if>
        <if test="paramCondition.technicalTitle != null and paramCondition.technicalTitle != ''">
            and technical_title like concat('%[',#{paramCondition.technicalTitle},']%')
        </if>
        <if test="paramCondition.expertLevel != null and paramCondition.expertLevel != ''">
            and expert_level = #{paramCondition.expertLevel}
        </if>
        <if test="paramCondition.province != null and paramCondition.province != '' and paramCondition.province !='不限'">
            and province = #{paramCondition.province}
        </if>
        <if test="paramCondition.city != null and paramCondition.city != '' and paramCondition.city !='不限'">
            and city = #{paramCondition.city}
        </if>
        <if test="paramCondition.district != null and paramCondition.district != '' and paramCondition.city !='不限'">
            and district = #{paramCondition.district}
        </if>
        <if test="paramCondition.name != null and paramCondition.name != ''">
            and name like concat('%', #{paramCondition.name}, '%')
        </if>
        <if test="paramCondition.company != null and paramCondition.company != ''">
            and company like concat('%', #{paramCondition.company}, '%')
        </if>
        <if test="paramCondition.condition != null and paramCondition.condition != ''">
            and (name like concat('%', #{paramCondition.condition}, '%')
            or province like concat('%', #{paramCondition.condition}, '%')
            or city like concat('%', #{paramCondition.condition}, '%')
            or district like concat('%', #{paramCondition.condition}, '%')
            )
        </if>
        <if test="paramCondition.excludeIds != null and paramCondition.excludeIds.size() > 0">
            and id not in
            <foreach collection="paramCondition.excludeIds" index="index" item="i" open="(" separator="," close=")">
                #{i}
            </foreach>
        </if>
        <if test="paramCondition.excludeCompanys != null and paramCondition.excludeCompanys.size() > 0">
            and company not in
            <foreach collection="paramCondition.excludeCompanys" index="index" item="i" open="(" separator="," close=")">
                #{i}
            </foreach>
        </if>
    </sql>

    <select id="customList" resultType="cn.stylefeng.guns.sys.modular.system.model.result.ExpertResult" parameterType="cn.stylefeng.guns.sys.modular.system.model.params.ExpertParam">
        select
        <include refid="Base_Column_List"/>
        from cez_expert where status != 'DELETED'
        <include refid="Common_Query_Condition"/>
    </select>

    <select id="customMapList" resultType="map" parameterType="cn.stylefeng.guns.sys.modular.system.model.params.ExpertParam">
        select
        <include refid="Base_Column_List"/>
        from cez_expert where status != 'DELETED'
        <include refid="Common_Query_Condition"/>
    </select>

    <select id="customPageList"
            resultType="cn.stylefeng.guns.sys.modular.system.model.result.ExpertResult"
            parameterType="cn.stylefeng.guns.sys.modular.system.model.params.ExpertParam">
        select
        <include refid="Base_Column_List"/>
        from cez_expert where status != 'DELETED'
        <include refid="Common_Query_Condition"/>
    </select>

    <select id="customPageMapList" resultType="map" parameterType="cn.stylefeng.guns.sys.modular.system.model.params.ExpertParam">
        select
        <include refid="Base_Column_List"/>
        from cez_expert where status != 'DELETED'
        <include refid="Common_Query_Condition"/>
    </select>

    <select id="randomSelect" resultType="cn.stylefeng.guns.sys.modular.system.model.result.ExpertResult">
        select
        <include refid="Base_Column_List"/>
        from cez_expert where status != 'DELETED'
        <include refid="Common_Query_Condition"/>
        order by rand() limit #{size}
    </select>

    <select id="findByIdCode" resultType="cn.stylefeng.guns.sys.modular.system.entity.Expert">
        select <include refid="All_Column_List"/> from cez_expert where id_code = #{idCode}
    </select>

    <insert id="insertList" parameterType="java.util.List">
        insert IGNORE into cez_expert(id,name,phone,id_code,major,
                               province,technical_title,
                               expert_level,company,city,
                              district,status,on_job,create_time
             ) values
        <foreach collection="list" item="item" index="index" separator=",">
            (
                   #{item.id},
                   #{item.name,jdbcType=VARCHAR},
                   #{item.phone,jdbcType=VARCHAR},
                   #{item.idCode,jdbcType=VARCHAR},
                   #{item.major,jdbcType=VARCHAR},
                   #{item.province,jdbcType=VARCHAR},
                   #{item.technicalTitle,jdbcType=VARCHAR},
                   #{item.expertLevel,jdbcType=VARCHAR},
                   #{item.company,jdbcType=VARCHAR},
                   #{item.city,jdbcType=VARCHAR},
                   #{item.district,jdbcType=VARCHAR},
                   #{item.status,jdbcType=VARCHAR},
                   #{item.onJob,jdbcType=VARCHAR},
                   #{item.createTime,jdbcType=VARCHAR}
            )
        </foreach>
    </insert>
    <update id="updateList" parameterType="java.util.List">
        update cez_expert set name=#{name},phone=#{phone},major=#{major},province=#{province},
                              technical_title=#{technicalTitle},expert_level=#{expertLevel},
                              company=#{company},city=#{city},district=#{district},database_id=#{databaseId},
                              status=#{status},on_job=#{onJob},update_time=#{updateTime},update_user=#{updateUser} where id=#{id}
    </update>
</mapper>

lhf@@yxg

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
导入以及导入模板下载

一，前端html@layout("/common/_container.html",{js:["/dict/dictjs/CANDIDATE_STATUS","/assets/modular/system/expert/expert.js"]}){<div class="layui-body-header"> <span class="layui-body-header-title">专家管理</span></div><div cl
复制链接

扫一扫