基于javaweb+mysql的ssm+maven报销erp系统(java+ssm+jsp+layui+jquery+mysql)

基于javaweb+mysql的ssm+maven报销erp系统(java+ssm+jsp+layui+jquery+mysql)

运行环境

Java≥8、MySQL≥5.7、Tomcat≥8

开发工具

eclipse/idea/myeclipse/sts等均可配置运行

适用

课程设计，大作业，毕业设计，项目练习，学习演示等

功能说明

基于javaweb+mysql的SSM+Maven报销erp系统(java+ssm+jsp+layui+jquery+mysql)

项目介绍

ssm ERP报销系统。主要分4个角色，总经理、部门经理、财务、普通员工，普通员工填写报销单后需要提交给部门经理审核，再由财务支付，如果金额大于5000，还需要总经理审核。 总经理拥有 部门管理 和 员工管理 功能 部门经理拥有 员工管理 功能 其他职务没有

环境需要

1.运行环境：最好是java jdk 1.8，我们在这个平台上运行的。其他版本理论上也可以。 2.IDE环境：IDEA，Eclipse,Myeclipse都可以。推荐IDEA; 3.tomcat环境：Tomcat 7.x,8.x,9.x版本均可 4.硬件环境：windows 7/8/10 1G内存以上；或者 Mac OS； 5.是否Maven项目: 是；查看源码目录中是否包含pom.xml；若包含，则为maven项目，否则为非maven项目 6.数据库：MySql 5.7版本；

技术栈

1. 后端：Spring SpringMVC MyBatis 2. 前端：JSP+LayUI+jQuery

主要技术

• Spring Ioc - Mybatis+Spring整合 - 声明式事务 - Spring标签库 - Spring拦截器

软件架构

三层架构

• 持久层–Mybatis - 表现层–Spring MVC - 控制器–Spring Controller

基于MVC模式

• 视图–Jsp - 模型–JavaBean - 业务层–JavaBean

使用说明

1. 使用Navicat或者其它工具，在mysql中创建对应名称的数据库，并导入项目的sql文件； 2. 使用IDEA/Eclipse/MyEclipse导入项目，Eclipse/MyEclipse导入时，若为maven项目请选择maven; 若为maven项目，导入成功后请执行maven clean;maven install命令，配置tomcat，然后运行； 3. 将项目中spring-dao.xml配置文件中的数据库配置改为自己的配置 4. 运行项目，在浏览器中输入http://localhost:8080/ 登录

账户说明

系统默认用户里有四个，分别对应10001（总经理）,10002（部门经理）,10003（财务）,10004（普通员工） 密码为000000 员工填写报销单后需要提交给部门经理审核，再由财务支付， 如果金额大于5000，还需要总经理审核。 总经理拥有 部门管理 和 员工管理 功能 部门经理拥有 员工管理 功能 其他职务没有

    }

    @RequestMapping("/to_add")
    public String toAdd(Map<String,Object> map){
        map.put("department",new Department());
        return "department_add";
    }

    @RequestMapping("/add")
    public String add(Department department){
        departmentBiz.add(department);
        return "redirect:list";
    }

    @RequestMapping(value = "/to_update",params = "sn")
    public String toUpdate(String sn, Map<String,Object> map){
        map.put("department",departmentBiz.get(sn));
        return "department_update";
    }

    @RequestMapping("/update")
    public String update(Department department){
        departmentBiz.edit(department);
        return "redirect:list";
    }

    @RequestMapping(value = "/remove",params = "sn")
    public String remove(String sn){
        //根据部门号找到对应的员工
        List<Employee> employeeList = employeeDao.selectByDepartmentAndPost(sn,null);
        List<ClaimVoucher> claimVoucherList = null;
        List<Integer> ids = null;
        if (!employeeList.isEmpty() && employeeList != null){
            //删除员工及其报销单
            for (Employee employee : employeeList){
                //根据报销单id删除报销单
                claimVoucherList = claimVoucherBiz.getForSelf(employee.getSn());
                if (!claimVoucherList.isEmpty() && claimVoucherList != null){
                    ids = new ArrayList<>();
                    for (ClaimVoucher claimVoucher : claimVoucherList){
                        ids.add(claimVoucher.getId());
                    }
                    for (int id : ids){
                        claimVoucherBiz.deleteById(id);
                    }
                }
                employeeBiz.remove(employee.getSn());
            }
        }
        departmentBiz.remove(sn);
        return "redirect:list";
    }

    public String remove(String sn){
        //根据员工工号找到对应报销单 再根据对应的报销单id删除报销单 最后删除员工信息
        List<ClaimVoucher> claimVoucherList = claimVoucherBiz.getForSelf(sn);
        if (!claimVoucherList.isEmpty() && claimVoucherList != null){
            List<Integer> ids = new ArrayList<>();
            for (ClaimVoucher claimVoucher : claimVoucherList){
                ids.add(claimVoucher.getId());
            }
            for (int id : ids){
                claimVoucherBiz.deleteById(id);
            }
        }
        employeeBiz.remove(sn);
        return "redirect:list";
    }

}
package com.cmj.oa.controller;

@Controller("globalController")
public class GlobalController {

    @Autowired
    private GlobalBiz globalBiz;

    @RequestMapping("/to_login")
    public String toLogin(){
        return "login";
    }

    @RequestMapping("/login")
    @ResponseBody
    public String login(HttpSession session, HttpServletRequest request) throws Exception{
        String sn = request.getParameter("username");
        String password = request.getParameter("password");
        Employee employee = globalBiz.login(sn,password);
        if (employee == null) {
            return "false";
        }
        session.setAttribute("employee",employee);

    }

    @RequestMapping("/login")
    @ResponseBody
    public String login(HttpSession session, HttpServletRequest request) throws Exception{
        String sn = request.getParameter("username");
        String password = request.getParameter("password");
        Employee employee = globalBiz.login(sn,password);
        if (employee == null) {
            return "false";
        }
        session.setAttribute("employee",employee);
        return "true";
    }

    @RequestMapping("/self")
    public String self(){
        return "self";
    }

    @RequestMapping("/quit")
    public String quit(HttpSession session){
        session.setAttribute("employee",null);
        return "redirect:to_login";
    }

    @RequestMapping("/to_change_password")
    public String toChangePassword(){
        return "change_password";
    }

    @RequestMapping("/change_password")
    public String changePassword(HttpSession session, @RequestParam String old, @RequestParam String new1,@RequestParam String new2){
        Employee employee = (Employee) session.getAttribute("employee");
        if (employee.getPassword().equals(old)) {
            if(new1.equals(new2)){
                employee.setPassword(new1);
                globalBiz.changePassword(employee);
                return "redirect:self";
            }
        }
        return "redirect:to_change_password";

        map.put("plist",Contant.getPost());
        return "employee_update";
    }

    @RequestMapping("/update")
    public String update(Employee employee){
        employeeBiz.edit(employee);
        return "redirect:list";
    }

    @RequestMapping(value = "/remove",params = "sn")
    public String remove(String sn){
        //根据员工工号找到对应报销单 再根据对应的报销单id删除报销单 最后删除员工信息
        List<ClaimVoucher> claimVoucherList = claimVoucherBiz.getForSelf(sn);
        if (!claimVoucherList.isEmpty() && claimVoucherList != null){
            List<Integer> ids = new ArrayList<>();
            for (ClaimVoucher claimVoucher : claimVoucherList){
                ids.add(claimVoucher.getId());
            }
            for (int id : ids){
                claimVoucherBiz.deleteById(id);
            }
        }
        employeeBiz.remove(sn);
        return "redirect:list";
    }

}
package com.cmj.oa.controller;

@Controller("globalController")
public class GlobalController {

            String fileName=DateUtil.formatDate(new Date(), "yyyyMMdd-HHmmssSSS")+"_"+file.getOriginalFilename();

            FileUtil.makeDirs(webPath+filePath);
            //保存服务器
            file.transferTo(new File(webPath+filePath+fileName));

            //解析
            List<ClaimVoucherInfo> list =  excel_to_claimVoucherInfo(new File(webPath+filePath+fileName));

            //开始 上传 数据库
            for(ClaimVoucherInfo info:list) {
                claimVoucherBiz.save(info.getClaimVoucher(),info.getItems());
            }

            //删除用过的文件
            FileUtil.deleteFile(webPath+filePath+fileName);
        }
        result.put("success", true);
        result.put("msg", "导入成功");
        return result;
    }

    private List<ClaimVoucherInfo> excel_to_claimVoucherInfo(File userUploadFile) throws ParseException {
        List<ClaimVoucherInfo> list = new ArrayList<>();
        ClaimVoucherInfo claimVoucherInfo = null;
        ClaimVoucherItem claimVoucherItem = null;
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(userUploadFile));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            //获取第一个sheet页
            HSSFSheet sheet = wb.getSheetAt(0);
            if(sheet!=null){
                //从第二行开始导入数据
                for(int rowNum =1;rowNum<=sheet.getLastRowNum();rowNum++){
                    HSSFRow row = sheet.getRow(rowNum);
                    if(row==null){
                        continue;
                    }
                    if (row.getCell(0)!=null && !row.getCell(0).toString().isEmpty()){
                        claimVoucherInfo  = new ClaimVoucherInfo();
                        claimVoucherInfo.setClaimVoucher(new ClaimVoucher());
                        claimVoucherInfo.setItems(new ArrayList<ClaimVoucherItem>());
                        list.add(claimVoucherInfo);

    private EmployeeBiz employeeBiz;

    @RequestMapping("/to_add")
    public String toAdd(Map<String,Object> map){
        map.put("items", Contant.getItems());
        map.put("info",new ClaimVoucherInfo());
        return "claim_voucher_add";
    }

    @RequestMapping("/add")
    public String add(HttpSession session, ClaimVoucherInfo info){
        Employee employee = (Employee)session.getAttribute("employee");
        info.getClaimVoucher().setCreateSn(employee.getSn());
        claimVoucherBiz.save(info.getClaimVoucher(),info.getItems());
        return "redirect:detail?id="+info.getClaimVoucher().getId();
    }

    @RequestMapping("/detail")
    public String detail(int id,Map<String,Object> map){
        map.put("claimVoucher",claimVoucherBiz.get(id));
        map.put("items",claimVoucherBiz.getItems(id));
        map.put("records",claimVoucherBiz.getRecords(id));
        return "claim_voucher_detail";
    }

    @RequestMapping("/self")
    public String self(HttpSession session,Map<String,Object> map){
        Employee employee = (Employee)session.getAttribute("employee");
        map.put("list",claimVoucherBiz.getForSelf(employee.getSn()));
        return "claim_voucher_self";
    }

    @RequestMapping("/deal")
    public String deal(HttpSession session,Map<String,Object> map){
        Employee employee = (Employee)session.getAttribute("employee");
        map.put("list",claimVoucherBiz.getForDeal(employee.getSn()));
        return "claim_voucher_deal";
    }

    @RequestMapping("/to_update")
    public String toUpdate(int id,Map<String,Object> map){
        map.put("items",Contant.getItems());
        ClaimVoucherInfo info = new ClaimVoucherInfo();
        info.setClaimVoucher(claimVoucherBiz.get(id));
        info.setItems(claimVoucherBiz.getItems(id));
        map.put("info",info);

    }

    @RequestMapping("/to_update")
    public String toUpdate(int id,Map<String,Object> map){
        map.put("items",Contant.getItems());
        ClaimVoucherInfo info = new ClaimVoucherInfo();
        info.setClaimVoucher(claimVoucherBiz.get(id));
        info.setItems(claimVoucherBiz.getItems(id));
        map.put("info",info);
        return "claim_voucher_update";
    }

    @RequestMapping("/update")
    public String update(HttpSession session,ClaimVoucherInfo info){
        Employee employee = (Employee)session.getAttribute("employee");
        info.getClaimVoucher().setCreateSn(employee.getSn());
        claimVoucherBiz.update(info.getClaimVoucher(),info.getItems());
        return "redirect:deal";
    }

    @RequestMapping("/submit")
    public String submit(int id){
        claimVoucherBiz.submit(id);
        return "redirect:deal";
    }

    @RequestMapping("/to_check")
    public String toCheck(int id,Map<String,Object> map){
        map.put("claimVoucher",claimVoucherBiz.get(id));
        map.put("items",claimVoucherBiz.getItems(id));
        map.put("records",claimVoucherBiz.getRecords(id));
        DealRecord dealRecord =new DealRecord();
        dealRecord.setClaimVoucherId(id);
        map.put("record",dealRecord);
        return "claim_voucher_check";
    }

    @RequestMapping("/check")
    public String check(HttpSession session, DealRecord dealRecord){
        Employee employee = (Employee)session.getAttribute("employee");
        dealRecord.setDealSn(employee.getSn());

        for (int i = 0 ; i < str.length ; i++){
            id.add(Integer.parseInt(str[i]));
        }
        List<ClaimVoucher> claimVoucherList = new ArrayList<>();
        for (Integer i : id) {
            claimVoucherList.add(claimVoucherBiz.get(i));
        }
        String webPath=request.getServletContext().getRealPath("/");
        Workbook wb = fillExcelDataWithTemplate(claimVoucherList, webPath+"/static/excel/claim.xls");

        ResponseUtil.export(response,wb,"报销单.xls");
        return null;
    }

    public Workbook fillExcelDataWithTemplate(List<ClaimVoucher> list , String templateFileUrl) {
        Workbook wb = null ;
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileUrl));
            wb = new HSSFWorkbook(fs);
            // 取得 模板的 第一个sheet 页
            Sheet sheet = wb.getSheetAt(0);
            // 拿到sheet页有多少列
            int cellNums = sheet.getRow(0).getLastCellNum();
            // 从第3行 开始写入 前两行为标题
            int rowIndex = 2;
            Row row = sheet.createRow(rowIndex);
            DateFormat df = DateFormat.getDateTimeInstance();
            for(ClaimVoucher claimVoucher : list){
                row.createCell(0).setCellValue(claimVoucher.getId());
                row.createCell(1).setCellValue(claimVoucher.getStatus());
                row.createCell(2).setCellValue(claimVoucher.getCause());
                row.createCell(3).setCellValue(employeeBiz.getEmployeeName(claimVoucher.getCreateSn()));
                row.createCell(4).setCellValue(claimVoucher.getTotalAmount());
                row.createCell(5).setCellValue(df.format(claimVoucher.getCreateTime()));
                if (claimVoucherBiz.getRecords(claimVoucher.getId()).size()==0){
                    rowIndex++;
                    row = sheet.createRow(rowIndex);
                    continue;
                }else {
                    for (DealRecord dealRecord : claimVoucherBiz.getRecords(claimVoucher.getId())){
                        row.createCell(6).setCellValue(employeeBiz.getEmployeeName(dealRecord.getDealSn()));
                        row.createCell(7).setCellValue(df.format(dealRecord.getDealTime()));
                        row.createCell(8).setCellValue(dealRecord.getDealWay());

            FileUtil.makeDirs(webPath+filePath);
            //保存服务器
            file.transferTo(new File(webPath+filePath+fileName));

            //解析
            List<ClaimVoucherInfo> list =  excel_to_claimVoucherInfo(new File(webPath+filePath+fileName));

            //开始 上传 数据库
            for(ClaimVoucherInfo info:list) {
                claimVoucherBiz.save(info.getClaimVoucher(),info.getItems());
            }

            //删除用过的文件
            FileUtil.deleteFile(webPath+filePath+fileName);
        }
        result.put("success", true);
        result.put("msg", "导入成功");
        return result;
    }

    private List<ClaimVoucherInfo> excel_to_claimVoucherInfo(File userUploadFile) throws ParseException {
        List<ClaimVoucherInfo> list = new ArrayList<>();
        ClaimVoucherInfo claimVoucherInfo = null;
        ClaimVoucherItem claimVoucherItem = null;
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(userUploadFile));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            //获取第一个sheet页
            HSSFSheet sheet = wb.getSheetAt(0);
            if(sheet!=null){
                //从第二行开始导入数据
                for(int rowNum =1;rowNum<=sheet.getLastRowNum();rowNum++){
                    HSSFRow row = sheet.getRow(rowNum);
                    if(row==null){
                        continue;
                    }
                    if (row.getCell(0)!=null && !row.getCell(0).toString().isEmpty()){
                        claimVoucherInfo  = new ClaimVoucherInfo();
                        claimVoucherInfo.setClaimVoucher(new ClaimVoucher());
                        claimVoucherInfo.setItems(new ArrayList<ClaimVoucherItem>());
                        list.add(claimVoucherInfo);
                        //去掉创建者工号中的 .0  因为如果全是数字后面有.0
                        claimVoucherInfo.getClaimVoucher().setCreateSn(Integer.toString((int)(row.getCell(0).getNumericCellValue())));
                        claimVoucherInfo.getClaimVoucher().setCause(row.getCell(1).toString());
                        claimVoucherInfo.getClaimVoucher().setTotalAmount(row.getCell(2).getNumericCellValue());
                    }
                    claimVoucherItem = new ClaimVoucherItem();
                    claimVoucherItem.setItem(row.getCell(3).toString());
                    claimVoucherItem.setAmount(row.getCell(4).getNumericCellValue());
                    if (!row.getCell(5).toString().isEmpty() && row.getCell(5).toString() != null) {
                        claimVoucherItem.setComment(row.getCell(5).toString());

    public String remove(String sn){
        //根据员工工号找到对应报销单 再根据对应的报销单id删除报销单 最后删除员工信息
        List<ClaimVoucher> claimVoucherList = claimVoucherBiz.getForSelf(sn);
        if (!claimVoucherList.isEmpty() && claimVoucherList != null){
            List<Integer> ids = new ArrayList<>();
            for (ClaimVoucher claimVoucher : claimVoucherList){
                ids.add(claimVoucher.getId());
            }
            for (int id : ids){
                claimVoucherBiz.deleteById(id);
            }
        }
        employeeBiz.remove(sn);
        return "redirect:list";
    }

}
package com.cmj.oa.controller;

@Controller("globalController")
public class GlobalController {

    @Autowired
    private GlobalBiz globalBiz;

    @RequestMapping("/to_login")
    public String toLogin(){
        return "login";
    }

    @RequestMapping("/login")
    @ResponseBody
    public String login(HttpSession session, HttpServletRequest request) throws Exception{

        return "claim_voucher_add";
    }

    @RequestMapping("/add")
    public String add(HttpSession session, ClaimVoucherInfo info){
        Employee employee = (Employee)session.getAttribute("employee");
        info.getClaimVoucher().setCreateSn(employee.getSn());
        claimVoucherBiz.save(info.getClaimVoucher(),info.getItems());
        return "redirect:detail?id="+info.getClaimVoucher().getId();
    }

    @RequestMapping("/detail")
    public String detail(int id,Map<String,Object> map){
        map.put("claimVoucher",claimVoucherBiz.get(id));
        map.put("items",claimVoucherBiz.getItems(id));
        map.put("records",claimVoucherBiz.getRecords(id));
        return "claim_voucher_detail";
    }

    @RequestMapping("/self")
    public String self(HttpSession session,Map<String,Object> map){
        Employee employee = (Employee)session.getAttribute("employee");
        map.put("list",claimVoucherBiz.getForSelf(employee.getSn()));
        return "claim_voucher_self";
    }

    @RequestMapping("/deal")
    public String deal(HttpSession session,Map<String,Object> map){
        Employee employee = (Employee)session.getAttribute("employee");
        map.put("list",claimVoucherBiz.getForDeal(employee.getSn()));
        return "claim_voucher_deal";
    }

    @RequestMapping("/to_update")
    public String toUpdate(int id,Map<String,Object> map){
        map.put("items",Contant.getItems());
        ClaimVoucherInfo info = new ClaimVoucherInfo();
        info.setClaimVoucher(claimVoucherBiz.get(id));
        info.setItems(claimVoucherBiz.getItems(id));
        map.put("info",info);
        return "claim_voucher_update";
    }

    @Autowired
    private ClaimVoucherBiz claimVoucherBiz;
    @GetMapping("/list")
    public String list(Map<String,Object> map){
        map.put("list",employeeBiz.getAll());
        return "employee_list";
    }

    @RequestMapping("/to_add")
    public String toAdd(Employee employee,Map<String,Object> map){
        map.put("employee",employee);
        map.put("dlist",departmentBiz.getdlist());
        map.put("plist",Contant.getPost());
        return "employee_add";
    }

    @PostMapping("/add")
    public String add(Employee employee){
        employeeBiz.add(employee);
        return "redirect:list";
    }

    @RequestMapping(value = "/to_update",params = "sn")
    public String toUpdate(String sn, Map<String,Object> map){
        map.put("employee",employeeBiz.get(sn));
        map.put("dlist",departmentBiz.getdlist());
        map.put("plist",Contant.getPost());
        return "employee_update";
    }

    @RequestMapping("/update")
    public String update(Employee employee){
        employeeBiz.edit(employee);
        return "redirect:list";
    }

    @RequestMapping(value = "/remove",params = "sn")
    public String remove(String sn){
        //根据员工工号找到对应报销单 再根据对应的报销单id删除报销单 最后删除员工信息
        List<ClaimVoucher> claimVoucherList = claimVoucherBiz.getForSelf(sn);
        if (!claimVoucherList.isEmpty() && claimVoucherList != null){
            List<Integer> ids = new ArrayList<>();
            for (ClaimVoucher claimVoucher : claimVoucherList){
                ids.add(claimVoucher.getId());
            }
            for (int id : ids){
                claimVoucherBiz.deleteById(id);

        session.setAttribute("employee",null);
        return "redirect:to_login";
    }

    @RequestMapping("/to_change_password")
    public String toChangePassword(){
        return "change_password";
    }

    @RequestMapping("/change_password")
    public String changePassword(HttpSession session, @RequestParam String old, @RequestParam String new1,@RequestParam String new2){
        Employee employee = (Employee) session.getAttribute("employee");
        if (employee.getPassword().equals(old)) {
            if(new1.equals(new2)){
                employee.setPassword(new1);
                globalBiz.changePassword(employee);
                return "redirect:self";
            }
        }
        return "redirect:to_change_password";
    }

}
package com.cmj.oa.global;

public class LoginInterceptor implements HandlerInterceptor {

    @Override
    public boolean preHandle(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse, Object o) throws Exception {

        String url = httpServletRequest.getRequestURI();
        if(url.toLowerCase().indexOf("login")>=0){
            return true;
        }

        HttpSession session = httpServletRequest.getSession();
        if(session.getAttribute("employee")!=null){
            return true;
        }
        httpServletResponse.sendRedirect("/to_login");
        return false;
    }

    @Override
    public void postHandle(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse, Object o, ModelAndView modelAndView) throws Exception {

@Controller("claimVoucherController")
@RequestMapping("/claim_voucher")
public class ClaimVoucherController {

    @Autowired
    private ClaimVoucherBiz claimVoucherBiz;

    @Autowired
    private EmployeeBiz employeeBiz;

    @RequestMapping("/to_add")
    public String toAdd(Map<String,Object> map){
        map.put("items", Contant.getItems());
        map.put("info",new ClaimVoucherInfo());
        return "claim_voucher_add";

                }else {
                    for (DealRecord dealRecord : claimVoucherBiz.getRecords(claimVoucher.getId())){
                        row.createCell(6).setCellValue(employeeBiz.getEmployeeName(dealRecord.getDealSn()));
                        row.createCell(7).setCellValue(df.format(dealRecord.getDealTime()));
                        row.createCell(8).setCellValue(dealRecord.getDealWay());
                        row.createCell(9).setCellValue(dealRecord.getComment());
                        rowIndex ++;
                        row = sheet.createRow(rowIndex);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    @ResponseBody
    @RequestMapping(value = "/upload_excel")
    public JSONObject upload_excel(@RequestParam("file") MultipartFile file, HttpServletResponse response, HttpServletRequest request)throws Exception {
        JSONObject result = new JSONObject();

        System.out.println(file.getOriginalFilename());

        if (file.isEmpty()){
            result.put("success", false);
            result.put("msg", "文件为空");
            return result;
        }else {
            String webPath=request.getServletContext().getRealPath("");
            String filePath= "/static/upload_file/excel/";
            //把文件名换成（时间戳.png）
            String fileName=DateUtil.formatDate(new Date(), "yyyyMMdd-HHmmssSSS")+"_"+file.getOriginalFilename();

            FileUtil.makeDirs(webPath+filePath);
            //保存服务器
            file.transferTo(new File(webPath+filePath+fileName));

            //解析
            List<ClaimVoucherInfo> list =  excel_to_claimVoucherInfo(new File(webPath+filePath+fileName));

            //开始 上传 数据库

        return "claim_voucher_deal";
    }

    @RequestMapping("/to_update")
    public String toUpdate(int id,Map<String,Object> map){
        map.put("items",Contant.getItems());
        ClaimVoucherInfo info = new ClaimVoucherInfo();
        info.setClaimVoucher(claimVoucherBiz.get(id));
        info.setItems(claimVoucherBiz.getItems(id));
        map.put("info",info);
        return "claim_voucher_update";
    }

    @RequestMapping("/update")
    public String update(HttpSession session,ClaimVoucherInfo info){
        Employee employee = (Employee)session.getAttribute("employee");
        info.getClaimVoucher().setCreateSn(employee.getSn());
        claimVoucherBiz.update(info.getClaimVoucher(),info.getItems());
        return "redirect:deal";
    }

    @RequestMapping("/submit")
    public String submit(int id){
        claimVoucherBiz.submit(id);
        return "redirect:deal";
    }

    @RequestMapping("/to_check")
    public String toCheck(int id,Map<String,Object> map){
        map.put("claimVoucher",claimVoucherBiz.get(id));
        map.put("items",claimVoucherBiz.getItems(id));
        map.put("records",claimVoucherBiz.getRecords(id));
        DealRecord dealRecord =new DealRecord();
        dealRecord.setClaimVoucherId(id);
        map.put("record",dealRecord);
        return "claim_voucher_check";
    }

    @RequestMapping("/check")
    public String check(HttpSession session, DealRecord dealRecord){
        Employee employee = (Employee)session.getAttribute("employee");
        dealRecord.setDealSn(employee.getSn());
        claimVoucherBiz.deal(dealRecord);
        return "redirect:deal";
    }

    @RequestMapping("/fuzzyQuery")
    public String fuzzyQuery(HttpSession session,Map<String,Object> map,@RequestParam String status,@RequestParam String total_amount,@RequestParam String create_time) throws ParseException, IOException {
        Employee employee = (Employee)session.getAttribute("employee");
        String createSn = employee.getSn();

    @RequestMapping(value = "/export")
    public String exportExcel(HttpServletResponse response, HttpServletRequest  request,String ids) throws Exception {
        if (ids.length()==0 || ids.isEmpty()){
            return null;
        }
        String[] str = ids.split(",");
        List<Integer> id = new ArrayList<>();
        for (int i = 0 ; i < str.length ; i++){
            id.add(Integer.parseInt(str[i]));
        }
        List<ClaimVoucher> claimVoucherList = new ArrayList<>();
        for (Integer i : id) {
            claimVoucherList.add(claimVoucherBiz.get(i));
        }
        String webPath=request.getServletContext().getRealPath("/");
        Workbook wb = fillExcelDataWithTemplate(claimVoucherList, webPath+"/static/excel/claim.xls");

        ResponseUtil.export(response,wb,"报销单.xls");
        return null;
    }

    public Workbook fillExcelDataWithTemplate(List<ClaimVoucher> list , String templateFileUrl) {
        Workbook wb = null ;
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templateFileUrl));
            wb = new HSSFWorkbook(fs);
            // 取得 模板的 第一个sheet 页
            Sheet sheet = wb.getSheetAt(0);
            // 拿到sheet页有多少列
            int cellNums = sheet.getRow(0).getLastCellNum();
            // 从第3行 开始写入 前两行为标题
            int rowIndex = 2;
            Row row = sheet.createRow(rowIndex);
            DateFormat df = DateFormat.getDateTimeInstance();
            for(ClaimVoucher claimVoucher : list){
                row.createCell(0).setCellValue(claimVoucher.getId());
                row.createCell(1).setCellValue(claimVoucher.getStatus());
                row.createCell(2).setCellValue(claimVoucher.getCause());
                row.createCell(3).setCellValue(employeeBiz.getEmployeeName(claimVoucher.getCreateSn()));
                row.createCell(4).setCellValue(claimVoucher.getTotalAmount());

    }

    @RequestMapping("/check")
    public String check(HttpSession session, DealRecord dealRecord){
        Employee employee = (Employee)session.getAttribute("employee");
        dealRecord.setDealSn(employee.getSn());
        claimVoucherBiz.deal(dealRecord);
        return "redirect:deal";
    }

    @RequestMapping("/fuzzyQuery")
    public String fuzzyQuery(HttpSession session,Map<String,Object> map,@RequestParam String status,@RequestParam String total_amount,@RequestParam String create_time) throws ParseException, IOException {
        Employee employee = (Employee)session.getAttribute("employee");
        String createSn = employee.getSn();
        Double totalAmount = null;
        if (!total_amount.isEmpty() && total_amount != null){
            totalAmount = Double.valueOf(total_amount);
        }
        map.put("list",claimVoucherBiz.fuzzyQuery(createSn,status,totalAmount,create_time));
        return "claim_voucher_self";
    }

    @RequestMapping("/fuzzyQueryForDeal")
    public String fuzzyQueryForDeal(HttpSession session,Map<String,Object> map,@RequestParam String createSn,@RequestParam String status,@RequestParam String total_amount,@RequestParam String create_time){
        Employee employee = (Employee)session.getAttribute("employee");
        String nextDealSn = employee.getSn();
        Double totalAmount = null;
        if (!total_amount.isEmpty() && total_amount != null){
            totalAmount = Double.valueOf(total_amount);
        }
        map.put("list",claimVoucherBiz.fuzzyQueryForDeal(createSn,nextDealSn,status,totalAmount,create_time));
        return "claim_voucher_deal";
    }

    @RequestMapping("/delete")
    @ResponseBody
    public String deleteClaimVoucher(@RequestParam("ids") String ids){
        if (ids.length()==0 || ids.isEmpty()){
            return null;

@Controller("employeeController")
@RequestMapping("/employee")
public class EmployeeController {
    @Autowired
    private DepartmentBiz departmentBiz;
    @Autowired
    private EmployeeBiz employeeBiz;
    @Autowired
    private ClaimVoucherBiz claimVoucherBiz;
    @GetMapping("/list")
    public String list(Map<String,Object> map){
        map.put("list",employeeBiz.getAll());
        return "employee_list";
    }

    @RequestMapping("/to_add")
    public String toAdd(Employee employee,Map<String,Object> map){
        map.put("employee",employee);
        map.put("dlist",departmentBiz.getdlist());
        map.put("plist",Contant.getPost());
        return "employee_add";
    }

    @PostMapping("/add")
    public String add(Employee employee){
        employeeBiz.add(employee);
        return "redirect:list";
    }

    @RequestMapping(value = "/to_update",params = "sn")
    public String toUpdate(String sn, Map<String,Object> map){
        map.put("employee",employeeBiz.get(sn));
        map.put("dlist",departmentBiz.getdlist());
        map.put("plist",Contant.getPost());
        return "employee_update";
    }

    @RequestMapping("/update")
    public String update(Employee employee){
        employeeBiz.edit(employee);
        return "redirect:list";
    }

    @RequestMapping(value = "/remove",params = "sn")
    public String remove(String sn){
        //根据员工工号找到对应报销单 再根据对应的报销单id删除报销单 最后删除员工信息
        List<ClaimVoucher> claimVoucherList = claimVoucherBiz.getForSelf(sn);
        if (!claimVoucherList.isEmpty() && claimVoucherList != null){

            // 拿到sheet页有多少列
            int cellNums = sheet.getRow(0).getLastCellNum();
            // 从第3行 开始写入 前两行为标题
            int rowIndex = 2;
            Row row = sheet.createRow(rowIndex);
            DateFormat df = DateFormat.getDateTimeInstance();
            for(ClaimVoucher claimVoucher : list){
                row.createCell(0).setCellValue(claimVoucher.getId());
                row.createCell(1).setCellValue(claimVoucher.getStatus());
                row.createCell(2).setCellValue(claimVoucher.getCause());
                row.createCell(3).setCellValue(employeeBiz.getEmployeeName(claimVoucher.getCreateSn()));
                row.createCell(4).setCellValue(claimVoucher.getTotalAmount());
                row.createCell(5).setCellValue(df.format(claimVoucher.getCreateTime()));
                if (claimVoucherBiz.getRecords(claimVoucher.getId()).size()==0){
                    rowIndex++;
                    row = sheet.createRow(rowIndex);
                    continue;
                }else {
                    for (DealRecord dealRecord : claimVoucherBiz.getRecords(claimVoucher.getId())){
                        row.createCell(6).setCellValue(employeeBiz.getEmployeeName(dealRecord.getDealSn()));
                        row.createCell(7).setCellValue(df.format(dealRecord.getDealTime()));
                        row.createCell(8).setCellValue(dealRecord.getDealWay());
                        row.createCell(9).setCellValue(dealRecord.getComment());
                        rowIndex ++;
                        row = sheet.createRow(rowIndex);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    @ResponseBody
    @RequestMapping(value = "/upload_excel")
    public JSONObject upload_excel(@RequestParam("file") MultipartFile file, HttpServletResponse response, HttpServletRequest request)throws Exception {
        JSONObject result = new JSONObject();

        System.out.println(file.getOriginalFilename());

        if (file.isEmpty()){
            result.put("success", false);
            result.put("msg", "文件为空");
            return result;
        }else {
            String webPath=request.getServletContext().getRealPath("");
            String filePath= "/static/upload_file/excel/";
            //把文件名换成（时间戳.png）
            String fileName=DateUtil.formatDate(new Date(), "yyyyMMdd-HHmmssSSS")+"_"+file.getOriginalFilename();

            FileUtil.makeDirs(webPath+filePath);

                    }
                    if (row.getCell(0)!=null && !row.getCell(0).toString().isEmpty()){
                        claimVoucherInfo  = new ClaimVoucherInfo();
                        claimVoucherInfo.setClaimVoucher(new ClaimVoucher());
                        claimVoucherInfo.setItems(new ArrayList<ClaimVoucherItem>());
                        list.add(claimVoucherInfo);
                        //去掉创建者工号中的 .0  因为如果全是数字后面有.0
                        claimVoucherInfo.getClaimVoucher().setCreateSn(Integer.toString((int)(row.getCell(0).getNumericCellValue())));
                        claimVoucherInfo.getClaimVoucher().setCause(row.getCell(1).toString());
                        claimVoucherInfo.getClaimVoucher().setTotalAmount(row.getCell(2).getNumericCellValue());
                    }
                    claimVoucherItem = new ClaimVoucherItem();
                    claimVoucherItem.setItem(row.getCell(3).toString());
                    claimVoucherItem.setAmount(row.getCell(4).getNumericCellValue());
                    if (!row.getCell(5).toString().isEmpty() && row.getCell(5).toString() != null) {
                        claimVoucherItem.setComment(row.getCell(5).toString());
                    }else {
                        claimVoucherItem.setComment("无");
                    }
                    claimVoucherInfo.getItems().add(claimVoucherItem);
                }
            }
        }  catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    }
package com.cmj.oa.controller;

        if (file.isEmpty()){
            result.put("success", false);
            result.put("msg", "文件为空");
            return result;
        }else {
            String webPath=request.getServletContext().getRealPath("");
            String filePath= "/static/upload_file/excel/";
            //把文件名换成（时间戳.png）
            String fileName=DateUtil.formatDate(new Date(), "yyyyMMdd-HHmmssSSS")+"_"+file.getOriginalFilename();

            FileUtil.makeDirs(webPath+filePath);
            //保存服务器
            file.transferTo(new File(webPath+filePath+fileName));

            //解析
            List<ClaimVoucherInfo> list =  excel_to_claimVoucherInfo(new File(webPath+filePath+fileName));

            //开始 上传 数据库
            for(ClaimVoucherInfo info:list) {
                claimVoucherBiz.save(info.getClaimVoucher(),info.getItems());
            }

            //删除用过的文件
            FileUtil.deleteFile(webPath+filePath+fileName);
        }
        result.put("success", true);
        result.put("msg", "导入成功");
        return result;
    }

    private List<ClaimVoucherInfo> excel_to_claimVoucherInfo(File userUploadFile) throws ParseException {
        List<ClaimVoucherInfo> list = new ArrayList<>();
        ClaimVoucherInfo claimVoucherInfo = null;
        ClaimVoucherItem claimVoucherItem = null;
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(userUploadFile));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            //获取第一个sheet页
            HSSFSheet sheet = wb.getSheetAt(0);
            if(sheet!=null){
                //从第二行开始导入数据
                for(int rowNum =1;rowNum<=sheet.getLastRowNum();rowNum++){
                    HSSFRow row = sheet.getRow(rowNum);
                    if(row==null){
                        continue;

    @RequestMapping("/add")
    public String add(HttpSession session, ClaimVoucherInfo info){
        Employee employee = (Employee)session.getAttribute("employee");
        info.getClaimVoucher().setCreateSn(employee.getSn());
        claimVoucherBiz.save(info.getClaimVoucher(),info.getItems());
        return "redirect:detail?id="+info.getClaimVoucher().getId();
    }

    @RequestMapping("/detail")
    public String detail(int id,Map<String,Object> map){
        map.put("claimVoucher",claimVoucherBiz.get(id));
        map.put("items",claimVoucherBiz.getItems(id));
        map.put("records",claimVoucherBiz.getRecords(id));
        return "claim_voucher_detail";
    }

    @RequestMapping("/self")
    public String self(HttpSession session,Map<String,Object> map){
        Employee employee = (Employee)session.getAttribute("employee");
        map.put("list",claimVoucherBiz.getForSelf(employee.getSn()));
        return "claim_voucher_self";
    }

    @RequestMapping("/deal")
    public String deal(HttpSession session,Map<String,Object> map){
        Employee employee = (Employee)session.getAttribute("employee");
        map.put("list",claimVoucherBiz.getForDeal(employee.getSn()));
        return "claim_voucher_deal";
    }

    @RequestMapping("/to_update")
    public String toUpdate(int id,Map<String,Object> map){
        map.put("items",Contant.getItems());
        ClaimVoucherInfo info = new ClaimVoucherInfo();
        info.setClaimVoucher(claimVoucherBiz.get(id));
        info.setItems(claimVoucherBiz.getItems(id));
        map.put("info",info);
        return "claim_voucher_update";
    }

    @RequestMapping("/update")
    public String update(HttpSession session,ClaimVoucherInfo info){
        Employee employee = (Employee)session.getAttribute("employee");
        info.getClaimVoucher().setCreateSn(employee.getSn());
        claimVoucherBiz.update(info.getClaimVoucher(),info.getItems());
        return "redirect:deal";
    }

    public String update(HttpSession session,ClaimVoucherInfo info){
        Employee employee = (Employee)session.getAttribute("employee");
        info.getClaimVoucher().setCreateSn(employee.getSn());
        claimVoucherBiz.update(info.getClaimVoucher(),info.getItems());
        return "redirect:deal";
    }

    @RequestMapping("/submit")
    public String submit(int id){
        claimVoucherBiz.submit(id);
        return "redirect:deal";
    }

    @RequestMapping("/to_check")
    public String toCheck(int id,Map<String,Object> map){
        map.put("claimVoucher",claimVoucherBiz.get(id));
        map.put("items",claimVoucherBiz.getItems(id));
        map.put("records",claimVoucherBiz.getRecords(id));
        DealRecord dealRecord =new DealRecord();
        dealRecord.setClaimVoucherId(id);
        map.put("record",dealRecord);
        return "claim_voucher_check";
    }

    @RequestMapping("/check")
    public String check(HttpSession session, DealRecord dealRecord){
        Employee employee = (Employee)session.getAttribute("employee");
        dealRecord.setDealSn(employee.getSn());
        claimVoucherBiz.deal(dealRecord);
        return "redirect:deal";
    }

    @RequestMapping("/fuzzyQuery")
    public String fuzzyQuery(HttpSession session,Map<String,Object> map,@RequestParam String status,@RequestParam String total_amount,@RequestParam String create_time) throws ParseException, IOException {
        Employee employee = (Employee)session.getAttribute("employee");
        String createSn = employee.getSn();
        Double totalAmount = null;
        if (!total_amount.isEmpty() && total_amount != null){
            totalAmount = Double.valueOf(total_amount);
        }
        map.put("list",claimVoucherBiz.fuzzyQuery(createSn,status,totalAmount,create_time));
        return "claim_voucher_self";
    }

    @RequestMapping("/fuzzyQueryForDeal")
    public String fuzzyQueryForDeal(HttpSession session,Map<String,Object> map,@RequestParam String createSn,@RequestParam String status,@RequestParam String total_amount,@RequestParam String create_time){

                    claimVoucherItem = new ClaimVoucherItem();
                    claimVoucherItem.setItem(row.getCell(3).toString());
                    claimVoucherItem.setAmount(row.getCell(4).getNumericCellValue());
                    if (!row.getCell(5).toString().isEmpty() && row.getCell(5).toString() != null) {
                        claimVoucherItem.setComment(row.getCell(5).toString());
                    }else {
                        claimVoucherItem.setComment("无");
                    }
                    claimVoucherInfo.getItems().add(claimVoucherItem);
                }
            }
        }  catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    }
package com.cmj.oa.controller;

@Controller("departmentController")
@RequestMapping("/department")
public class DepartmentController {
    @Autowired
    private DepartmentBiz departmentBiz;
    @Autowired
    private ClaimVoucherBiz claimVoucherBiz;
    @Autowired
    private EmployeeBiz employeeBiz;
    @Qualifier("employeeDao")
    @Autowired
    private EmployeeDao employeeDao;
    @RequestMapping("/list")
    public String list(Map<String,Object> map){
        map.put("list",departmentBiz.getAll());
        return "department_list";
    }

    private DepartmentBiz departmentBiz;
    @Autowired
    private EmployeeBiz employeeBiz;
    @Autowired
    private ClaimVoucherBiz claimVoucherBiz;
    @GetMapping("/list")
    public String list(Map<String,Object> map){
        map.put("list",employeeBiz.getAll());
        return "employee_list";
    }

    @RequestMapping("/to_add")
    public String toAdd(Employee employee,Map<String,Object> map){
        map.put("employee",employee);
        map.put("dlist",departmentBiz.getdlist());
        map.put("plist",Contant.getPost());
        return "employee_add";
    }

    @PostMapping("/add")
    public String add(Employee employee){
        employeeBiz.add(employee);
        return "redirect:list";
    }

    @RequestMapping(value = "/to_update",params = "sn")
    public String toUpdate(String sn, Map<String,Object> map){
        map.put("employee",employeeBiz.get(sn));
        map.put("dlist",departmentBiz.getdlist());
        map.put("plist",Contant.getPost());
        return "employee_update";
    }

    @RequestMapping("/update")
    public String update(Employee employee){
        employeeBiz.edit(employee);
        return "redirect:list";
    }

    @RequestMapping(value = "/remove",params = "sn")
    public String remove(String sn){
        //根据员工工号找到对应报销单 再根据对应的报销单id删除报销单 最后删除员工信息
        List<ClaimVoucher> claimVoucherList = claimVoucherBiz.getForSelf(sn);
        if (!claimVoucherList.isEmpty() && claimVoucherList != null){
            List<Integer> ids = new ArrayList<>();
            for (ClaimVoucher claimVoucher : claimVoucherList){
                ids.add(claimVoucher.getId());