package com.gk.hotwork.Service.ServiceImpl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.gk.hotwork.Domain.*;
import com.gk.hotwork.Domain.Exception.BusinessException;
import com.gk.hotwork.Domain.Utils.*;
import com.gk.hotwork.Domain.Vo.DepartmentVo;
import com.gk.hotwork.Service.*;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service("ExcelExportService")
public class ExcelExportServiceImpl implements ExcelExportService {
    private Logger logger = LogManager.getLogger(getClass());

    String[] districttitle = {"code", "name", "parentcode", "type"};

    String[] userTitle = {"手机号", "密码", "姓名", "身份证", "单位", "部门"};


    String[] supplierUserTitle = {"手机号", "密码", "姓名", "身份证", "邮箱", "部门", "职务",
            "是否单位负责人", "是否部门负责人", "用户类型", "状态"};

    String[] selfCheckTitle = {"作业类型","类型","检查类型", "检查内容", "参考判断", "排序", "备注"};


    String[] riskEventTitle = {"安全风险分析单元名称", "安全风险事件名称"};

    String[] riskAnaUnitTitle = {"安全风险分析对象编码", "安全风险分析单元名称", "责任部门", "责任人"};

    String[] riskControlMeasureTitle = {"安全风险事件名称", "管控方式", "管控方式描述", "管控措施分类1"
                    , "管控措施分类2", "管控措施分类3", "隐患排查内容", "周期", "单位"};

    String[] riskSourceTitle = {"生产装置", "风险等级", "可能导致事故的主要原因", "区域位置", "所属部门"};
    //正整数
    String pattern = "^[0-9]*[1-9][0-9]*$";

    Pattern pattern2 = Pattern.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$");

    @Autowired
    private DistrictService districtService;
    @Autowired
    private UserService userService;
    @Autowired
    private UserRolesService userRolesService;
    @Autowired
    private RoleService roleService;
    @Autowired
    SelfCheckService selfCheckService;
    @Autowired
    DepartmentService departmentService;
    @Autowired
    CompanyService companyService;
    @Autowired
    RiskEventService riskEventService;
    @Autowired
    RiskAnaUnitService riskAnaUnitService;
    @Autowired
    RiskControlMeasureService riskControlMeasureService;
    @Autowired
    RiskControlClassifyService riskControlClassifyService;
    @Autowired
    private RiskSourceService riskSourceService;



    @Override
    public BooleanReason importDistrictExcel(InputStream in, String operator, Boolean isExcel2007) {
        BooleanReason blret = new BooleanReason();
        List<DistrictInfo> districtInfoList = new ArrayList<>();
        String retmsg = "";
        blret.setValue(false);
        try {
            Workbook workbook = null;
            if (isExcel2007) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                blret.addReason("excel 第1页不能为空");
                return blret;
            }
            Row row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            String[] title = new String[colNum];

            if (colNum != districttitle.length) {
                blret.addReason("上传文件的列的个数错误");
                return blret;
            }

            for (int i = 0; i < colNum; i++) {
                title[i] = row.getCell(i).getStringCellValue();
                if (StringUtils.isNotBlank(title[i])) {
                    title[i] = title[i].trim();
                }
                if (!title[i].equals((districttitle[i]))) {
                    blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + districttitle[i]);
                    return blret;
                }
            }

            for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
                int realrow = irow + 1;
                DistrictInfo districtInfo = new DistrictInfo();
                Object value = null;
                row = sheet.getRow(irow);

                value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,编码不能为空.<br/>";
                    continue;
                } else {
                    districtInfo.setCode(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,名称不能为空.<br/>";
                    continue;
                } else {
                    districtInfo.setName(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,父编码不能为空.<br/>";
                    continue;
                } else {
                    districtInfo.setParentcode(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,类型不能为空.<br/>";
                    continue;
                } else {
                    districtInfo.setType(Byte.valueOf(value.toString()));
                }

                districtInfoList.add(districtInfo);
            }

            for (DistrictInfo districtInfo : districtInfoList) {
                districtService.save(districtInfo);
            }

            if (StringUtils.isNotBlank(retmsg)) {
                retmsg = retmsg + "导入成功" + districtInfoList.size() + "个，" + "导入失败" + (sheet.getLastRowNum() - districtInfoList.size()) + "个。";
                blret.addReason(retmsg);
                return blret;
            }
        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
            blret.addReason("导入错误");
            return blret;
        }

        blret.setValue(true);
        return blret;
    }

    @Override
    @Transactional
    public void importUserExcel(InputStream in, String username, Boolean isExcel2007) {
        List<UserInfo> userInfoList = new ArrayList<>();
        //准备单位和部门
        Set<String> companySet = new HashSet<>();
        List<CompanyInfo> companyInfos = companyService.selectByAll();
        for (CompanyInfo companyInfo : companyInfos) {
            companySet.add(companyInfo.getCompany());
        }
        Set<String> departmentSet = new HashSet<>();
        List<DepartmentVo> departmentVos = departmentService.selectByAll();
        for (DepartmentVo departmentVo : departmentVos) {
            departmentSet.add(departmentVo.getDepartment());
        }
        try {
            Workbook workbook = null;
            if (isExcel2007) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                throw new BusinessException("excel 第1页不能为空");
            }
            Row row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            String[] title = new String[colNum];

            if (colNum != userTitle.length) {
                throw new BusinessException("上传文件的列的个数错误");
            }

            for (int i = 0; i < colNum; i++) {
                title[i] = row.getCell(i).getStringCellValue();
                if (StringUtils.isNotBlank(title[i])) {
                    title[i] = title[i].trim();
                }
                if (!title[i].equals((userTitle[i]))) {
                    throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + userTitle[i]);
                }
            }

            for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
                int realrow = irow + 1;
                UserInfo userInfo = new UserInfo();
                Object value = null;
                row = sheet.getRow(irow);

                value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,手机号不能为空.<br/>");
                } else {
                    userInfo.setUsername(value.toString().trim());
                    if (userInfo.getUsername().length() != 11) {
                        throw new BusinessException("手机号不为11");
                    }
                    if (userService.phoneIsOccupied(userInfo.getUsername())) {
                        throw new BusinessException("导入失败,第" + realrow + "行,手机号重复.<br/>");
                    }
                }

                value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,密码不能为空.<br/>");
                } else {
                    userInfo.setPassword(MD5Utils.encode(value.toString().trim()));
                }

                value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,姓名不能为空.<br/>");
                } else {
                    userInfo.setRealname(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    userInfo.setIdcard(value.toString().trim());
                    if (!IdCardUtil.strongVerifyIdNumber(userInfo.getIdcard())) {
                        throw new BusinessException( "导入失败,第" + realrow + "行,身份证非法.<br/>");
                    }
                    if (userService.idCardIsOccupied(userInfo.getIdcard())) {
                        throw new BusinessException( "导入失败,第" + realrow + "行,身份证重复.<br/>");
                    }

                }



                value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    userInfo.setCompany(value.toString().trim());
                    if (!companySet.contains(userInfo.getCompany()))
                        throw new BusinessException("导入失败,第" + realrow + "行,单位不存在.<br/>");
                }

                value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    DepartmentInfo infoByName = departmentService.getInfoByName(value.toString().trim());
                    if (infoByName != null) {
                        userInfo.setDepartment(infoByName.getId());
                    }

                    if (!departmentSet.contains(userInfo.getDepartment())) {
                        throw new BusinessException("导入失败,第" + realrow + "行,部门不存在.<br/>");
                    }
                }





                userInfo.setIsupload((byte) 0);
                userInfo.setIsdel((byte) 0);
                userInfo.setCreatedby(username);
                userInfo.setCreateddate(new Date());
                userInfo.setLastmodifiedby(username);
                userInfo.setLastmodifieddate(new Date());
                userInfo.setType(3);
                userInfo.setStatus((byte)1);
                userInfo.setIsdepartment((byte) 0);
                userInfo.setIscompany((byte) 0);
                userInfoList.add(userInfo);
            }
            //sheet 用户手机号和身份证重复判断
            List<String> phoneList = userInfoList.stream().map(UserInfo::getUsername).distinct().collect(Collectors.toList());
            if(phoneList.size() != userInfoList.size()) throw new BusinessException("本sheet中包含重复手机号");
            List<String> idCardList = userInfoList.stream().map(UserInfo::getIdcard).distinct().collect(Collectors.toList());
            if(idCardList.size() != userInfoList.size()) throw new BusinessException("本sheet中包含重复身份证号");
            //保存用户信息 同时赋予橘色

            RoleInfo role = roleService.getOne(new LambdaQueryWrapper<RoleInfo>().eq(RoleInfo::getName, "员工"));
            if (role == null) throw new BusinessException("请查询角色是否存在员工");
            for (UserInfo userInfo : userInfoList) {
                //只赋予员工角色
                userService.save(userInfo);
                UserRolesInfo ur = new UserRolesInfo();
                ur.setUserid(userInfo.getId());
                ur.setRoleid(role.getId());
                userRolesService.save(ur);

            }

        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
            throw new BusinessException("导入错误");
        }

    }

    @Override
    public BooleanReason importSupplierUserExcel(InputStream in, String username, String company, Boolean isExcel2007) {
        BooleanReason blret = new BooleanReason();
        List<UserInfo> userInfoList = new ArrayList<>();
        String retmsg = "";
        blret.setValue(false);
        try {
            Workbook workbook = null;
            if (isExcel2007) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                blret.addReason("excel 第1页不能为空");
                return blret;
            }
            Row row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            String[] title = new String[colNum];

            if (colNum != supplierUserTitle.length) {
                blret.addReason("上传文件的列的个数错误");
                return blret;
            }

            for (int i = 0; i < colNum; i++) {
                title[i] = row.getCell(i).getStringCellValue();
                if (StringUtils.isNotBlank(title[i])) {
                    title[i] = title[i].trim();
                }
                if (!title[i].equals((supplierUserTitle[i]))) {
                    blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + supplierUserTitle[i]);
                    return blret;
                }
            }

            for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
                int realrow = irow + 1;
                UserInfo userInfo = new UserInfo();
                Object value = null;
                row = sheet.getRow(irow);

                value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,手机号不能为空.<br/>";
                    continue;
                } else {
                    userInfo.setUsername(value.toString().trim());
                    List<UserInfo> userInfoExist = userService.selectUserInfo(null, userInfo.getUsername());
                    if (userInfoExist.size() > 0) {
                        retmsg = retmsg + "导入失败,第" + realrow + "行,手机号重复.<br/>";
                        continue;
                    }
                }

                value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,密码不能为空.<br/>";
                    continue;
                } else {
                    String PW_PATTERN = "(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9])(?=.*[~!@#$%^&*_.]).{8,}";
                    if (!value.toString().trim().matches(PW_PATTERN)) {
                        retmsg = retmsg + "导入失败,第" + realrow + "行,密码必须8位以上,并且包含大小写字母、数字、特殊符号三种以上.<br/>";
                        continue;
                    } else {
                        userInfo.setPassword(Base64Encrypt.encode(value.toString().trim().getBytes()));
                    }
                }

                value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,姓名不能为空.<br/>";
                    continue;
                } else {
                    userInfo.setRealname(value.toString().trim());
                    UserInfo userInfoExist = userService.selectByRealName(null, userInfo.getRealname());
                    if (null != userInfoExist) {
                        retmsg = retmsg + "导入失败,第" + realrow + "行,姓名重复.<br/>";
                        continue;
                    }
                }

                value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    userInfo.setIdcard(value.toString().trim());
                    UserInfo idCardExist = userService.selectByIdCard(null, userInfo.getIdcard());
                    if (null != idCardExist) {
                        retmsg = retmsg + "导入失败,第" + realrow + "行,身份证重复.<br/>";
                        continue;
                    }
                }

                value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    userInfo.setEmail(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(6), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    DepartmentInfo infoByName = departmentService.getInfoByName(value.toString().trim());
                    if (infoByName != null) {
                        userInfo.setDepartment(infoByName.getId());
                    }

                }

                value = ExcelProperty.getCellValue(row.getCell(7), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    userInfo.setJob(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(8), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString()) && value.toString().trim().equals("是")) {
                    userInfo.setIscompany((byte) 1);
                } else {
                    userInfo.setIscompany((byte) 0);
                }

                value = ExcelProperty.getCellValue(row.getCell(9), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString()) && value.toString().trim().equals("是")) {
                    userInfo.setIsdepartment((byte) 1);
                } else {
                    userInfo.setIsdepartment((byte) 0);
                }

                value = ExcelProperty.getCellValue(row.getCell(10), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString()) && value.toString().trim().equals("管理员")) {
                    userInfo.setType(2);
                } else {
                    userInfo.setType(3);
                }

                value = ExcelProperty.getCellValue(row.getCell(11), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString()) && value.toString().trim().equals("停用")) {
                    userInfo.setStatus((byte) 0);
                } else {
                    userInfo.setStatus((byte) 1);
                }
                userInfo.setCompany(company);
                userInfo.setIsupload((byte) 0);
                userInfo.setIsdel((byte) 0);
                userInfo.setCreatedby(username);
                userInfo.setCreateddate(new Date());
                userInfo.setLastmodifiedby(username);
                userInfo.setLastmodifieddate(new Date());
                userService.save(userInfo);
                userInfoList.add(userInfo);
            }

            if (StringUtils.isNotBlank(retmsg)) {
                retmsg = retmsg + "导入成功" + userInfoList.size() + "个，" + "导入失败" + (sheet.getLastRowNum() - userInfoList.size()) + "个。";
                blret.addReason(retmsg);
                return blret;
            }
        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
            blret.addReason("导入错误");
            return blret;
        }

        blret.setValue(true);
        return blret;
    }



    @Override
    public BooleanReason imporSelfCheckExcel(InputStream in, String username, Boolean isExcel2007) {
        BooleanReason blret = new BooleanReason();
        List<SelfCheckInfo> selfCheckInfoList = new ArrayList<>();
        String retmsg = "";
        blret.setValue(false);
        try {
            Workbook workbook = null;
            if (isExcel2007) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                blret.addReason("excel 第1页不能为空");
                return blret;
            }
            Row row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            String[] title = new String[colNum];

            if (colNum != selfCheckTitle.length) {
                blret.addReason("上传文件的列的个数错误");
                return blret;
            }

            for (int i = 0; i < colNum; i++) {
                title[i] = row.getCell(i).getStringCellValue();
                if (StringUtils.isNotBlank(title[i])) {
                    title[i] = title[i].trim();
                }
                if (!title[i].equals((selfCheckTitle[i]))) {
                    blret.addReason("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + selfCheckTitle[i]);
                    return blret;
                }
            }

            for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
                int realrow = irow + 1;
                SelfCheckInfo selfCheckInfo = new SelfCheckInfo();
                Object value = null;
                row = sheet.getRow(irow);

                value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,作业类型不能为空.<br/>";
                    continue;
                } else {
                    selfCheckInfo.setTasktype(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,类型不能为空.<br/>";
                    continue;
                } else {
                    selfCheckInfo.setType(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,检查类型不能为空.<br/>";
                    continue;
                } else {
                    selfCheckInfo.setChecktype(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,检查内容不能为空.<br/>";
                    continue;
                } else {
                    selfCheckInfo.setContent(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    retmsg = retmsg + "导入失败,第" + realrow + "行,参考判断不能为空.<br/>";
                    continue;
                } else {
                    selfCheckInfo.setStandard(value.toString().trim());
                }

                value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    try {
                        selfCheckInfo.setSort(Integer.parseInt(value.toString().trim()));
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
                selfCheckInfo.setCreatedby(username);
                selfCheckInfo.setModifiedby(username);
                selfCheckInfo.setCreateddate(new Date());
                selfCheckInfo.setModifieddate(new Date());
                selfCheckInfo.setIsdel((byte)0);
                selfCheckInfoList.add(selfCheckInfo);
            }
            selfCheckService.saveBatch(selfCheckInfoList);

            if (StringUtils.isNotBlank(retmsg)) {
                retmsg = retmsg + "导入成功" + selfCheckInfoList.size() + "个，" + "导入失败" + (sheet.getLastRowNum() - selfCheckInfoList.size()) + "个。";
                blret.addReason(retmsg);
                return blret;
            }
        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
            blret.addReason("导入错误");
            return blret;
        }

        blret.setValue(true);
        return blret;
    }

    @Override
    public void importRiskEventExcel(InputStream in, String operator, Boolean isExcel2007) {
        List<RiskEvent> riskEventList = new ArrayList<>();
        //校验文件
        try {
            Workbook workbook = null;
            if (isExcel2007) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                throw new BusinessException("excel 第1页不能为空");
            }
            Row row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            String[] title = new String[colNum];

            if (colNum != riskEventTitle.length) {
                throw new BusinessException("上传文件的列的个数错误");
            }

            for (int i = 0; i < colNum; i++) {
                title[i] = row.getCell(i).getStringCellValue();
                if (StringUtils.isNotBlank(title[i])) {
                    title[i] = title[i].trim();
                }
                if (!title[i].equals((riskEventTitle[i]))) {
                    throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + riskEventTitle[i]);
                }
            }

            for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
                int realrow = irow + 1;
                RiskEvent riskEvent = new RiskEvent();
                Object value = null;
                Object value1 = null;
                row = sheet.getRow(irow);
                //安全风险分析单元
                //TODO
                value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
                value1 = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,安全风险分析单元名称不能为空.<br/>");
                } else {
                    String trim = value.toString().trim();
                    RiskAnaUnit infoByRiskUnitName = riskAnaUnitService.getInfoByRiskUnitName(value.toString().trim());
                    if (ObjectUtils.isEmpty(infoByRiskUnitName)){
                        throw new BusinessException("请检查风险分析单元名称是否正确或者是否有事件对应的分析单元");
                    }
                    riskEvent.setRiskUnitId(infoByRiskUnitName.getId());
                }
                //安全风险事件
                value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,安全风险事件名称不能为空.<br/>");
                } else {
                    riskEvent.setRiskEventName(value.toString().trim());
                }


                //设置属性
                riskEvent.setValidFlag(Boolean.TRUE);
                riskEvent.setUpdateBy(operator);
                riskEvent.setCreateBy(operator);
                riskEvent.setCreateTime(new Date());
                riskEvent.setUpdateTime(new Date());

                riskEventList.add(riskEvent);
            }

            for (RiskEvent riskEvent : riskEventList) {
                riskEventService.save(riskEvent);
            }

        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
            throw new BusinessException("导入错误");
        }

    }

    @Override
    public void importRiskAnaUnitExcel(InputStream in, String operator, Boolean isExcel2007) {
        List<RiskAnaUnit> riskAnaUnitList = new ArrayList<>();
        //校验文件
        try {
            Workbook workbook = null;
            if (isExcel2007) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                throw new BusinessException("excel 第1页不能为空");
            }
            Row row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            String[] title = new String[colNum];

            if (colNum != riskAnaUnitTitle.length) {
                throw new BusinessException("上传文件的列的个数错误");
            }

            for (int i = 0; i < colNum; i++) {

                title[i] = row.getCell(i).getStringCellValue();
                if (StringUtils.isNotBlank(title[i])) {
                    title[i] = title[i].trim();
                }
                if (!title[i].equals((riskAnaUnitTitle[i]))) {
                    throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + riskAnaUnitTitle[i]);
                }
            }

            for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
                int realrow = irow + 1;
                RiskAnaUnit riskAnaUnit = new RiskAnaUnit();
                Object value = null;
                Object value1 = null;
                row = sheet.getRow(irow);

                //字段-安全风险分析对象编码
                value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,安全风险分析对象编码不能为空.<br/>");
                } else {
                    String trim = value.toString().trim();
                    riskAnaUnit.setHazardCode(value.toString().trim());
                }

                //字段2-安全风险分析单元名称
                value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,安全风险分析单元名称不能为空.<br/>");
                } else {
                    riskAnaUnit.setRiskUnitName(value.toString().trim());
                }

                //参数属性3-责任部门, 责任部门id
                value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,责任部门不能为空.<br/>");
                } else {
                    DepartmentInfo depInfoByName = departmentService.getInfoByName(value.toString().trim());
                    if (ObjectUtils.isEmpty(depInfoByName)){
                        throw new BusinessException("请检查部门名称是否正确");
                    }
                    riskAnaUnit.setHazardDep(value.toString().trim());
                    riskAnaUnit.setHazardDepId(depInfoByName.getId());
                }

                //参数属性4-责任人 ,责任人id
                value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
                value1 = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,责任人不能为空.<br/>");
                } else {
                    UserInfo userByRealName = userService.getByRealName(value.toString().trim(), value1.toString().trim());
                    if (ObjectUtils.isEmpty(userByRealName)){
                        throw new BusinessException("请检查责任人名称是否正确");
                    }
                    riskAnaUnit.setHazardLiablePerson(value.toString().trim());

                    riskAnaUnit.setHazardLiablePersonId(userByRealName.getId());
                }

                //设置属性
                riskAnaUnit.setCreateTime(new Date());
                riskAnaUnit.setUpdateTime(new Date());
                riskAnaUnit.setCreateBy(operator);
                riskAnaUnit.setUpdateBy(operator);
                riskAnaUnit.setValidFlag(Boolean.TRUE);
                riskAnaUnit.setValidFlag(Boolean.TRUE);

                riskAnaUnitList.add(riskAnaUnit);
            }

            for (RiskAnaUnit riskAnaUnit : riskAnaUnitList) {
                riskAnaUnitService.save(riskAnaUnit);
            }

        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
            throw new BusinessException("导入错误");
        }

    }

    @Override
    public void importRiskControlMeasureExcel(InputStream in, String operator, Boolean isExcel2007) {
        List<RiskControlMeasure> riskControlMeasureList = new ArrayList<>();
        //校验文件
        try {
            Workbook workbook = null;
            if (isExcel2007) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                throw new BusinessException("excel 第1页不能为空");
            }
            Row row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            String[] title = new String[colNum];

            if (colNum != riskControlMeasureTitle.length) {
                throw new BusinessException("上传文件的列的个数错误");
            }

            for (int i = 0; i < colNum; i++) {

                title[i] = row.getCell(i).getStringCellValue();
                if (StringUtils.isNotBlank(title[i])) {
                    title[i] = title[i].trim();
                }
                if (!title[i].equals((riskControlMeasureTitle[i]))) {
                    throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + riskControlMeasureTitle[i]);
                }
            }

            for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
                int realrow = irow + 1;
                RiskControlMeasure riskControlMeasure = new RiskControlMeasure();
                Object value = null;
                row = sheet.getRow(irow);

                //字段-安全风险事件id
                value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,安全风险事件名称不能为空.<br/>");
                } else {
                    RiskEvent byRiskEventName = riskEventService.getByRiskEventName(value.toString().trim());
                    if (ObjectUtils.isEmpty(byRiskEventName)){
                        throw new BusinessException("请检查安全风险事件名称是否正确");
                    }
                    riskControlMeasure.setRiskEventId(byRiskEventName.getId());
                }

                //字段2-管控方式
                String auto= "自动化监控";
                String riskCheck= "隐患排查";
                value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,管控方式不能为空.<br/>");
                }else {
                    if (value != auto || value !=riskCheck){
                        riskControlMeasure.setDataSrc(value.toString().trim());
                    }else {
                        throw new BusinessException("请检查管控方式是否正确");
                    }
                }




                //参数属性3-管控方式描述
                value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,管控方式描述不能为空.<br/>");
                } else {
                    riskControlMeasure.setRiskMeasureDesc(value.toString().trim());
                }

                //参数属性4，5-管控措施分类
                Object value1 = null;
                value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
                value1 = ExcelProperty.getCellValue(row.getCell(4), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,管控措施分类1不能为空.<br/>");
                }
                if ( value1 == null || StringUtils.isBlank(value1.toString()) ){
                    throw new BusinessException("导入失败,管控措施分类2不能为空.<br/>");
                }else {
                    RiskControlClassify byClassify1AndClassify2 =
                            riskControlClassifyService.getByClassify1AndClassify12(value.toString().trim(), value1.toString().trim());
                    if (ObjectUtils.isEmpty(byClassify1AndClassify2)){
                        throw new BusinessException("请检查管控措施分类1,2是否正确");
                    }
                    riskControlMeasure.setClassify1(value.toString().trim());
                    riskControlMeasure.setClassify2(value1.toString().trim());
                }

                //参数属性6-管控分类措施3
                value = ExcelProperty.getCellValue(row.getCell(5), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    riskControlMeasure.setClassify3("");
                } else {
                    riskControlMeasure.setClassify3(value.toString().trim());
                }



                //参数属性7-隐患排查内容
                value = ExcelProperty.getCellValue(row.getCell(6), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,隐患排查内容不能为空.<br/>");
                } else {
                    riskControlMeasure.setTroubleshootContent(value.toString().trim());
                }

                //参数属性8-隐患排查措施-时间周期
                value = ExcelProperty.getCellValue(row.getCell(7), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,隐患排查周期不能为空.<br/>");
                } else {
                    riskControlMeasure.setTimeEffect(value.toString().trim());
                }

                //参数属性9-隐患排查措施-时间单位
                value = ExcelProperty.getCellValue(row.getCell(8), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,隐患排查单位不能为空.<br/>");
                } else {
                    riskControlMeasure.setTimeUnit(value.toString().trim());
                }

                //设置属性
                riskControlMeasure.setCreateBy(operator);
                riskControlMeasure.setUpdateBy(operator);
                riskControlMeasure.setCreateTime(new Date());
                riskControlMeasure.setUpdateTime(new Date());
                riskControlMeasure.setValidFlag(Boolean.TRUE);

                riskControlMeasureList.add(riskControlMeasure);
            }


            for (RiskControlMeasure riskControlMeasure : riskControlMeasureList) {
                riskControlMeasureService.save(riskControlMeasure);
            }

        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
            throw new BusinessException("导入错误");
        }
    }

    /** 生产装置导入*/
    @Override
    public void importRiskSourceExcel(InputStream in, String realname, Boolean isExcel2007) {

        List<RiskSource> riskSourceList = new ArrayList<>();
        //校验文件
        try {
            Workbook workbook = null;
            if (isExcel2007) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                throw new BusinessException("excel 第1页不能为空");
            }
            Row row = sheet.getRow(0);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            String[] title = new String[colNum];

            if (colNum != riskSourceTitle.length) {
                throw new BusinessException("上传文件的列的个数错误");
            }

            for (int i = 0; i < colNum; i++) {

                title[i] = row.getCell(i).getStringCellValue();
                if (StringUtils.isNotBlank(title[i])) {
                    title[i] = title[i].trim();
                }
                if (!title[i].equals((riskSourceTitle[i]))) {
                    throw new BusinessException("上传文件的第" + (i + 1) + "列的标题为" + title[i] + "不为" + riskSourceTitle[i]);
                }
            }

            for (int irow = 1; irow <= sheet.getLastRowNum(); irow++) {
                int realrow = irow + 1;
                RiskSource riskSource = new RiskSource();
                Object value = null;
                row = sheet.getRow(irow);

                //字段-生产装置
                value = ExcelProperty.getCellValue(row.getCell(0), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,生产装置不能为空.<br/>");
                } else {
                    riskSource.setName(value.toString().trim());
                }

                //字段2-风险等级
                String level1="低风险";
                value = ExcelProperty.getCellValue(row.getCell(1), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,风险等级不能为空.<br/>");
                }
                    /*1-低风险，2-一般风险，3-较大风险，4-重大风险*/
                    if(value.toString().trim().equals(level1)){
                        riskSource.setLevel((byte) 1);
                    }
                    else if(value.toString().trim().equals("一般风险")){
                        riskSource.setLevel((byte) 2);
                    }
                    else if(value.toString().trim().equals("较大风险")){
                        riskSource.setLevel((byte) 3);
                    }
                    else if(value.toString().trim().equals("重大风险")){
                        riskSource.setLevel((byte) 4);
                    } else {
                        throw new BusinessException("导入失败,第" + realrow + "行,风险等级不符合规范.<br/>");
                    }

                //参数属性3-可能导致事故的主要原因
                value = ExcelProperty.getCellValue(row.getCell(2), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,可能导致事故的主要原因不能为空.<br/>");
                } else {
                    riskSource.setAccidentDesc(value.toString().trim());
                }

                //参数属性-区域位置
                value = ExcelProperty.getCellValue(row.getCell(3), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,区域位置不能为空.<br/>");
                } else {
                    riskSource.setLocation(value.toString().trim());
                }

                //参数属性-所属部门  并查询设置id
                value = ExcelProperty.getCellValue(row.getCell(4), evaluator);
                if (null == value || StringUtils.isBlank(value.toString())) {
                    throw new BusinessException("导入失败,第" + realrow + "行,所属部门不能为空.<br/>");
                } else {

                    DepartmentInfo infoByName = departmentService.getInfoByName(value.toString().trim());
                    riskSource.setDepName(value.toString().trim());
                    riskSource.setDepId(infoByName.getId());
                }
                //设置属性
                UserInfo userInfo = userService.getByRealName(realname, value.toString().trim());

                riskSource.setType((byte) 1);
                riskSource.setStatus((byte) 1);
                riskSource.setCreateTime(new Date());
                riskSource.setCreateUid(userInfo.getId());
                riskSource.setCreateUname(realname);
                riskSource.setEditTime(new Date());
                riskSource.setEditUid(userInfo.getId());
                riskSource.setEditUname(realname);
                riskSourceList.add(riskSource);
            }

            for (RiskSource riskSource : riskSourceList) {
                riskSourceService.save(riskSource);
            }

        } catch (IOException e) {
            logger.error(e.getLocalizedMessage());
            throw new BusinessException("导入错误");
        }
    }

}