package com.yeejoin.amos.adpter.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.yeejoin.amos.adpter.model.AreaExcelModel;
import com.yeejoin.amos.adpter.model.DeviceExcelModel;
import com.yeejoin.amos.adpter.model.PersonExcelModel;
import com.yeejoin.amos.adpter.model.SystemExcelModel;
import com.yeejoin.amos.adpter.util.HutoolIdGenerator;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.RuntimeSqlException;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class AdpterService {

    private final static Logger log = LoggerFactory.getLogger(AdpterService.class);

    //开始时间
    private static Long startTime = 0L;
    //结束时间
    private static Long endTime = 0L;


    @Value("${source.jdbc.ip}")
    String sourceJdbc;

    @Value("${goal.jdbc.ip}")
    String goalJdbc;

    @Value("${source.jdbc.ip.idx}")
    String sourceJdbcIdx;

    @Value("${goal.jdbc.ip.idx}")
    String goalJdbcIdx;

    @Value("${source.jdbc.ip.buss}")
    String sourceJdbcBuss;

    @Value("${goal.jdbc.ip.buss}")
    String goalJdbcBuss;

    @Value("${source.jdbc.ip.dataview}")
    String dataView;

    @Value("${goal.jdbc.ip.dlbuss}")
    String dlBuss;

    @Value("${goal.jdbc.ip.relation}")
    String relation;

    @Value("${goal.jdbc.ip.dlcom}")
    String dlCom;
    String driver = "com.mysql.cj.jdbc.Driver";

    @Value("${yeejoin.user}")
    String userName;

    @Value("${yeejoin.pwd}")
    String pwd;

    @Value("${source.jdbc.ip.project}")
    String sourceJdbcProject;

    @Value("${goal.jdbc.ip.project}")
    String goalJdbcProject;

    @Value("${goal.jdbc.ip.project.org.seq}")
    String projectOrgSeq;

    @Value("${goal.jdbc.ip.project.role.seq}")
    String projectRoleSeq;

    @Value("${goal.jdbc.ip.project.user.rec-time}")
    String systemUserRecTime;



    public Map<String, String> handle() {
        Map<String, String> rMap = new HashMap<>();

        // 处理common库
        copy(sourceJdbc, goalJdbc, "cb_firefighters", "cb_firefighters", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_org_usr", "cb_org_usr", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource", "cb_water_resource", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_crane", "cb_water_resource_crane", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_hydrant", "cb_water_resource_hydrant", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_index", "cb_water_resource_index", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_iot", "cb_water_resource_iot", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_natural", "cb_water_resource_natural", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_pool", "cb_water_resource_pool", "sequence_nbr", rMap);

        // 处理 idx库
        copy(sourceJdbcIdx, goalJdbcIdx, "cb_org_usr", "cb_org_usr", "sequence_nbr", rMap);
        copy(sourceJdbcIdx, goalJdbcIdx, "idx_biz_unit_person", "idx_biz_unit_person", "SEQUENCE_NBR", rMap);

        // 处理business
        copy(sourceJdbcBuss, goalJdbcBuss, "f_equipment", "f_equipment", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "cb_org_usr", "cb_org_usr", "sequence_nbr", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "cb_water_resource", "cb_water_resource", "sequence_nbr", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "f_equipment_fire_equipment", "f_equipment_fire_equipment", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "f_fire_fighting_system", "f_fire_fighting_system", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_input_item", "p_input_item", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_plan", "p_plan", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_plan_exec_statistics", "p_plan_exec_statistics", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_plan_task", "p_plan_task", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_plan_task_detail", "p_plan_task_detail", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point", "p_point", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point_classify", "p_point_classify", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point_inputitem", "p_point_inputitem", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point_photo", "p_point_photo", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_route", "p_route", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_route_point", "p_route_point", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point_config", "p_point_config", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_route_point_item", "p_route_point_item", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_car", "wl_car", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_car_property", "wl_car_property", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_dynamic_form_instance", "wl_dynamic_form_instance", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_equipment_detail", "wl_equipment_detail", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_equipment_specific", "wl_equipment_specific", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_equipment_specific_index", "wl_equipment_specific_index", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_form_instance", "wl_form_instance", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_form_instance_equip", "wl_form_instance_equip", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_warehouse", "wl_warehouse", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_warehouse_structure", "wl_warehouse_structure", "id", rMap);

        log.info("执行结束================");
        return rMap;
    }

    public Map<String, String> cpSysUserOrg() {
        String time = systemUserRecTime;
        Map<String, String> rMap = new HashMap<>();
        String srcTableName = "auth_login_info";
        String destTableName = "auth_login_info";
        startTime = System.currentTimeMillis();
        Connection conn = getConnection(goalJdbcProject);
        PreparedStatement pst = null;

        //查询数据 只查询电力用户（AGENCY_CODE = 'STATE_GRID'）
        String sql = String.format("select * from  %s where AGENCY_CODE = 'STATE_GRID'", srcTableName);
        //查询数据
        String sqlGoal = "select SEQUENCE_NBR from  " + destTableName;
        List<Map<String, Object>> queryOld = query(sourceJdbcProject, sql);
        if(ObjectUtils.isEmpty(queryOld)){
            log.info("未查询到数据，执行结束================");
            return null;
        }
        List<Map<String, Object>> query = new ArrayList<>();
        //插入数据
        String insertSql = "insert into %s(%s) values(%s)";
        StringBuilder key = new StringBuilder();
        StringBuilder value = new StringBuilder();
        List<String> columns = new ArrayList<>();
        List<List<Object>> params = new ArrayList<>();
        List<String> sqlList = new ArrayList<>();
        List<String> oldUserId = new ArrayList<>();
        try {
            pst = conn.prepareStatement(sqlGoal);
            ResultSet resultSet = pst.executeQuery();
            log.debug("重复数据查询：{}", sqlGoal );
            while (resultSet.next()) {
                oldUserId.add(resultSet.getString("SEQUENCE_NBR"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (pst != null && !pst.isClosed()) {
                    pst.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
//         剔除已存在的数据
        for (Map<String, Object> map : queryOld) {
//            map.put("SALT", "10ICG834E4");//设置密码为a1234560
//            map.put("PASSWORD", "ACCB2DE8AD752638D55F6F0FE7CA928");//设置密码为a1234560
            map.put("AGENCY_CODE", "ONEPLOT");
            map.put("REC_DATE", time);
            //去重
            if(!oldUserId.contains(map.get("SEQUENCE_NBR").toString())&&!"5048575".equals(map.get("USER_ID").toString())){
                query.add(map);
            }
        }
        if (!query.isEmpty()) {
            for (String column : query.get(0).keySet()) {
                key.append(column).append(",");
                value.append("?,");
                columns.add(column);
            }
            insertSql = String.format(insertSql,
                    destTableName,
                    key.substring(0, key.length() - 1),
                    value.substring(0, value.length() - 1));

            List<String> userId = new ArrayList<>();
            for (Map<String, Object> map : query) {
                List<Object> param = new ArrayList<>();
                for (String column : columns) {
                    param.add(map.get(column));
                }
                params.add(param);
                //用户公司表数据、用户角色表数据、用户与应用关联表数据插入
                if (map.containsKey("USER_ID") && !ObjectUtils.isEmpty(map.get("USER_ID"))&&
                        !userId.contains(map.get("USER_ID").toString())&& !"5048575".equals(map.get("USER_ID").toString())) {
                    userId.add(map.get("USER_ID").toString());
                    sqlList.add("INSERT INTO `privilege_group_user` (`SEQUENCE_NBR`, `GROUP_SEQ`, `USER_ID`, `AGENCY_CODE`, `REC_DATE`, `REC_USER_ID`, `SUB_ORG_SEQ`) VALUES (" + HutoolIdGenerator.generate() + ", " + projectRoleSeq + ", '" + map.get("USER_ID") + "', 'ONEPLOT', '"+ time +"', '4950517', NULL)");
                    sqlList.add("INSERT INTO `privilege_user_org_role` (`SEQUENCE_NBR`, `REC_DATE`, `REC_USER_ID`, `USER_ID`, `COMPANY_SEQ`, `DEPARTMENT_SEQ`, `AGENCY_CODE`, `ROLE_SEQ`) VALUES (" + HutoolIdGenerator.generate() + ", '"+ time +"', '4950517', '" + map.get("USER_ID") + "', " + projectOrgSeq + ", NULL, 'ONEPLOT', " + projectRoleSeq + ")");
                    sqlList.add("INSERT INTO `privilege_user_application` (`SEQUENCE_NBR`, `REC_DATE`, `REC_USER_ID`, `AGENCY_CODE`, `APP_CODE`, `USER_ID`, `SUB_ORG_SEQ`) VALUES (" + HutoolIdGenerator.generate() + ", '"+ time +"', '4950517', 'ONEPLOT', 'AMOS_STUDIO', '" + map.get("USER_ID") + "', NULL)");
                    sqlList.add("INSERT INTO `privilege_user_application` (`SEQUENCE_NBR`, `REC_DATE`, `REC_USER_ID`, `AGENCY_CODE`, `APP_CODE`, `USER_ID`, `SUB_ORG_SEQ`) VALUES (" + HutoolIdGenerator.generate() + ", '"+ time +"', '4950517', 'ONEPLOT', 'AMOS_ONEPLOT_DP', '" + map.get("USER_ID") + "', NULL)");
                    sqlList.add("INSERT INTO `privilege_user_application` (`SEQUENCE_NBR`, `REC_DATE`, `REC_USER_ID`, `AGENCY_CODE`, `APP_CODE`, `USER_ID`, `SUB_ORG_SEQ`) VALUES (" + HutoolIdGenerator.generate() + ", '"+ time +"', '4950517', 'ONEPLOT', 'AMOS_ADMIN', '" + map.get("USER_ID") + "', NULL)");
                    sqlList.add("INSERT INTO `privilege_user_application` (`SEQUENCE_NBR`, `REC_DATE`, `REC_USER_ID`, `AGENCY_CODE`, `APP_CODE`, `USER_ID`, `SUB_ORG_SEQ`) VALUES (" + HutoolIdGenerator.generate() + ", '"+ time +"', '4950517', 'ONEPLOT', 'AMOS_ONEPLOT', '" + map.get("USER_ID") + "', NULL)");
                    sqlList.add("INSERT INTO `privilege_user_application` (`SEQUENCE_NBR`, `REC_DATE`, `REC_USER_ID`, `AGENCY_CODE`, `APP_CODE`, `USER_ID`, `SUB_ORG_SEQ`) VALUES (" + HutoolIdGenerator.generate() + ", '"+ time +"', '4950517', 'ONEPLOT', 'AMOS_ONEPLOT_SP', '" + map.get("USER_ID") + "', NULL)");
                }
            }
            int[] count = executeBatch(goalJdbcProject, insertSql, params, destTableName);
            log.debug("复制表{}成功，用时：{}ms，复制数据行数：{}", destTableName, (System.currentTimeMillis() - startTime), count.length);
        }
        if(!ObjectUtils.isEmpty(sqlList)){
            int batchSize = 50;
            for (int i = 0; i < sqlList.size(); i += batchSize) {
                List<String> subList = sqlList.subList(i, Math.min(i + batchSize, sqlList.size()));
                executeBySql(subList, goalJdbcProject, userName, pwd);
            }
            log.debug("处理用户角色单位数据成功，用时：{}ms", (System.currentTimeMillis() - startTime));
        }
        copyUser(sourceJdbcProject, goalJdbcProject, "privilege_agency_user", "privilege_agency_user", "SEQUENCE_NBR", rMap);
        log.info("执行结束================");
        return rMap;
    }

    public void executeSqlFile(HttpServletRequest request, HttpServletResponse response, String dbHost, String port, String dbName, String userName, String dbPwd, MultipartFile file) throws UnsupportedEncodingException {
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".sql")) {
            throw new RuntimeException("文件格式错误");
        }
        String filePath = "output.txt";
        PrintWriter printWriter = null;
        File logFile = new File(filePath);
        if (logFile.exists()) {
            deleteFile(filePath);
        }
        try {
            printWriter = new PrintWriter(logFile, "UTF-8");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        initAndExecute(dbHost, port, dbName, userName, dbPwd, file, printWriter);
        String outPutFileName = "脚本执行结果.txt";
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/plain");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(outPutFileName, "UTF-8") + "\"");
        //输入流
        InputStream in = null;
        try {
            in = new FileInputStream(filePath);
            ServletOutputStream os = response.getOutputStream();
            //缓冲区
            int len = 1;
            byte[] b = new byte[1024];
            while ((len = in.read(b)) != -1) {
                os.write(b, 0, len);
                in.close();
                os.close();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (Exception Ignored) {
        } finally {
            deleteFile(filePath);
        }
    }

    /**
     * 获取数据库连接
     *
     * @param
     * @return
     */
    public Connection getConnection(String jdbc) {
        //加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String jdbcUrl = jdbc;
        String user = userName;
        String password = pwd;
        //连接数据库
        try {
            return DriverManager.getConnection(jdbcUrl, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("连接数据库失败");
        }
    }

    /**
     * 将查询的数据复制到另一个数据库的表中，要求两张表的字段名，字段类型完全相同。
     *
     * @param srcTableName  要查询的表
     * @param destTableName 目标表名称
     * @return
     */
    public int[] copy(String source, String goal, String srcTableName, String destTableName, String id, Map rMap) {
        startTime = System.currentTimeMillis();

        Connection conn = null;
        PreparedStatement pst = null;
        conn = getConnection(goal);

        int count[] = new int[0];
        List<String> list = new ArrayList<>();

        //查询数据
        String sql = String.format("select * from  %s ", srcTableName);
        //查询数据
        String sqlGoal = "select count(*) as num from  " + destTableName + " where " + id + " = ";
        List<Map<String, Object>> queryOld = query(source, sql);
        List<Map<String, Object>> query = new ArrayList<>();
        //插入数据
        String insertSql = "insert into %s(%s) values(%s)";
        StringBuilder key = new StringBuilder();
        StringBuilder value = new StringBuilder();
        List<String> columns = new ArrayList<>();
        List<List<Object>> params = new ArrayList<>();

        // 剔除已存在的数据
        for (Map map : queryOld
        ) {
            try {
                pst = conn.prepareStatement(sqlGoal + map.get(id));
                ResultSet resultSet = pst.executeQuery();
                while (resultSet.next()) {
                    int num = resultSet.getInt("num");
                    if (num < 1) {
                        query.add(map);
                    } else {
                        list.add(map.get(id).toString());
                    }
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                try {
                    if (pst != null && !pst.isClosed()) {
                        pst.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        if (query.size() > 0) {
            for (String column : query.get(0).keySet()) {
                key.append(column).append(",");
                value.append("?,");
                columns.add(column);
            }
            insertSql = String.format(insertSql,
                    destTableName,
                    key.substring(0, key.length() - 1).toString(),
                    value.substring(0, value.length() - 1).toString());

            for (Map<String, Object> map : query) {
                List<Object> param = new ArrayList<>();
                for (String column : columns) {
                    param.add(map.get(column));
                }
                params.add(param);
            }
            count = executeBatch(goal, insertSql, params, destTableName);
            endTime = System.currentTimeMillis();
            log.debug("复制表" + destTableName + "成功" + "用时" + (endTime - startTime) + "ms");
        }
        if (list.size() > 0) {
            rMap.put(destTableName, String.join(",", list));
        } else {
            rMap.put(destTableName, "复制表" + destTableName + "成功" + "用时" + (endTime - startTime) + "ms");
        }
        return count;
    }

    /**
     * 将查询的数据复制到另一个数据库的表中，要求两张表的字段名，字段类型完全相同。
     *
     * @param srcTableName  要查询的表
     * @param destTableName 目标表名称
     * @return
     */
    public int[] copyUser(String source, String goal, String srcTableName, String destTableName, String id, Map rMap) {
        startTime = System.currentTimeMillis();

        Connection conn = null;
        PreparedStatement pst = null;
        conn = getConnection(goal);

        int count[] = new int[0];
        List<String> list = new ArrayList<>();

        //查询数据
        String sql = String.format("select * from  %s where AGENCY_CODE = 'STATE_GRID'", srcTableName);
        //查询数据
        String sqlGoal = "select count(*) as num from  " + destTableName + " where " + id + " = ";
        List<Map<String, Object>> queryOld = query(source, sql);
        List<Map<String, Object>> query = new ArrayList<>();
        //插入数据
        String insertSql = "insert into %s(%s) values(%s)";
        StringBuilder key = new StringBuilder();
        StringBuilder value = new StringBuilder();
        List<String> columns = new ArrayList<>();
        List<List<Object>> params = new ArrayList<>();

        // 剔除已存在的数据
        for (Map map : queryOld) {
            map.put("AGENCY_CODE", "ONEPLOT");
            map.put("PASSWORD_RESET", 0);
            map.put("SUPER_ORG_CODE", "10*11");
            map.put("REC_DATE", new Date());
            try {
                pst = conn.prepareStatement(sqlGoal + map.get(id));
                ResultSet resultSet = pst.executeQuery();
                while (resultSet.next()) {
                    int num = resultSet.getInt("num");
                    if (num < 1 && !"5144631".equals(map.get("USER_ID").toString())) {
                        query.add(map);
                    } else {
                        list.add(map.get(id).toString());
                    }
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                try {
                    if (pst != null && !pst.isClosed()) {
                        pst.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        if (query.size() > 0) {
            for (String column : query.get(0).keySet()) {
                key.append(column).append(",");
                value.append("?,");
                columns.add(column);
            }
            insertSql = String.format(insertSql,
                    destTableName,
                    key.substring(0, key.length() - 1).toString(),
                    value.substring(0, value.length() - 1).toString());

            for (Map<String, Object> map : query) {
                List<Object> param = new ArrayList<>();
                for (String column : columns) {
                    param.add(map.get(column));
                }
                params.add(param);
            }
            count = executeBatch(goal, insertSql, params, destTableName);
            endTime = System.currentTimeMillis();
            log.debug("复制表" + destTableName + "成功" + "用时" + (endTime - startTime) + "ms");
        }
        if (list.size() > 0) {
            rMap.put(destTableName, String.join(",", list));
        } else {
            rMap.put(destTableName, "复制表" + destTableName + "成功" + "用时" + (endTime - startTime) + "ms");
        }
        return count;
    }

    /**
     * 批量执行一个 SQL 语句，可以传不同的参数
     *
     * @param sql    SQL 语句
     * @param params 参数列表
     * @return
     */
    public int[] executeBatch(String goal, String sql, List<List<Object>> params, String destTableName) {
        int count[] = new int[0];
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getConnection(goal);
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);

            for (List<Object> list : params) {
                for (int i = 0; i < list.size(); i++) {
                    pst.setObject(i + 1, list.get(i));
                }
                pst.addBatch();
            }
            count = pst.executeBatch();
            conn.commit();
            conn.setAutoCommit(autoCommit);
        } catch (Exception e) {
            log.error("插入数据失败" + e.getMessage() + "========" + destTableName);
            throw new RuntimeException("插入数据失败" + e.getMessage());
        } finally {
            closeStatement(pst);
            closeConnection(conn);
        }
        return count;
    }


    /**
     * 查询数据并封装到 List 集合中
     *
     * @param sql  SQL 语句
     * @param args 参数列表
     * @return List<Map < 字段名, 值>>
     */
    public List<Map<String, Object>> query(String source, String sql, Object... args) {
        List<Map<String, Object>> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = getConnection(source);
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
            rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            //获取字段数量
            int columnCount = rsmd.getColumnCount();

            List<String> columns = new ArrayList<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                columns.add(rsmd.getColumnName(i)); // 字段名
            }
            //封装数据
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                for (String column : columns) {
                    map.put(column, rs.getObject(column));
                }
                result.add(map);
            }
        } catch (Exception e) {
            throw new RuntimeException("查询异常" + e.getMessage());
        } finally {
            closeStatement(pst);
            closeConnection(conn);
            closeResultSet(rs);
        }
        return result;
    }

    /**
     * 关闭 Statement
     *
     * @param stmt
     * @return
     */
    public static boolean closeStatement(Statement stmt) {
        boolean flag = true;
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }

    /**
     * 关闭 ResultSet
     *
     * @param rs
     * @return
     */
    public static boolean closeResultSet(ResultSet rs) {
        boolean flag = true;
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }

    /**
     * 关闭 Connection
     *
     * @param conn
     * @return
     */
    public static boolean closeConnection(Connection conn) {
        boolean flag = true;
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }


    private PrintWriter initAndExecute(String dbHost, String port, String dbName, String userName, String dbPwd, MultipartFile file, PrintWriter printWriter) {
        Connection conn = getManagerConnection(dbHost, port, dbName, userName, dbPwd, printWriter);
        if (ObjectUtils.isEmpty(conn)) {
            return printWriter;
        }
        FileReader reader = null;
        try {
            reader = new FileReader(convertMultipartFileToFile(file));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        ScriptRunner scriptRunner = new ScriptRunner(conn);
        // 设置编码，防止中文乱码
        Resources.setCharset(Charset.forName("UTF-8"));
        // 获取整个脚本再执行
//        scriptRunner.setSendFullScript(true);
        // 执行报错时，true 则回滚, false 则会继续执行
        scriptRunner.setStopOnError(false);
        scriptRunner.setErrorLogWriter(printWriter);
        try {
            // 执行
            scriptRunner.runScript(reader);

        } catch (RuntimeSqlException e) {
            throw new RuntimeSqlException(e);
        }
        scriptRunner.closeConnection();
        try {
            reader.close();
            printWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn);
        }
        return printWriter;
    }


    public File convertMultipartFileToFile(MultipartFile file) {
        File convertedFile = null;
        try {
            convertedFile = Files.createTempFile("temp-", "-" + file.getOriginalFilename()).toFile();
            file.transferTo(convertedFile);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return convertedFile;
    }

    private Connection getManagerConnection(String dbHost, String port, String dbName, String userName, String dbPwd, PrintWriter printWriter) {
        String jdbcUrl = "jdbc:mysql://" + dbHost + ":" + port + "/" + dbName + "?useUnicode=true&allowMultiQueries=true&characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai";
        //加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //连接数据库
        try {
            return DriverManager.getConnection(jdbcUrl, userName, dbPwd);
        } catch (SQLException e) {
            String errorMsg = "连接数据库失败，请检查数据库连接信息是否有误！";
            if (printWriter.checkError()) {
                try {
                    File logFile = new File("output.txt");
                    printWriter = new PrintWriter(logFile, "UTF-8");
                    printWriter.write(errorMsg);
                    if (printWriter.checkError()) {
                        log.error("文件写入异常:" + errorMsg);
                    }
                } catch (Exception exception) {
                    log.error("日志写入异常！" + errorMsg, exception);
                }
            } else {
                printWriter.write(errorMsg);
            }
            printWriter.flush();
            printWriter.close();
            e.printStackTrace();
            return null;
        }
    }

    public static boolean deleteFile(String path) {
        boolean flag = false;
        try {
            File file = new File(path);
            // 路径为文件且不为空则进行删除
            if (file.isFile()) {
                file.delete();
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    public ArrayList<AreaExcelModel> areaExcel() throws IOException {
        Resource resource = new ClassPathResource("sql/area.sql");
        String sql = new String(Files.readAllBytes(resource.getFile().toPath()));
        List<Map<String, Object>> list = query(dlBuss, sql);
        ArrayList<AreaExcelModel> areaExcelModels = new ArrayList<>();
        list.forEach(a -> {
            AreaExcelModel areaExcelModel = new AreaExcelModel();
            areaExcelModel.setCategory(ObjectUtils.isEmpty(a.get("category")) ? "" : String.valueOf(a.get("category")));
            areaExcelModel.setCode(ObjectUtils.isEmpty(a.get("code")) ? "" : String.valueOf(a.get("code")));
            areaExcelModel.setParentCode(ObjectUtils.isEmpty(a.get("parentCode")) ? "" : String.valueOf(a.get("parentCode")));
            areaExcelModel.setType(ObjectUtils.isEmpty(a.get("type")) ? "" : String.valueOf(a.get("type")));
            areaExcelModel.setName(ObjectUtils.isEmpty(a.get("name")) ? "" : String.valueOf(a.get("name")));
            areaExcelModel.setLevel(ObjectUtils.isEmpty(a.get("level")) ? "" : String.valueOf(a.get("level")));
            areaExcelModel.setPartition(ObjectUtils.isEmpty(a.get("partition")) ? "" : String.valueOf(a.get("partition")));
            areaExcelModels.add(areaExcelModel);
        });
        return areaExcelModels;
    }

    public ArrayList<DeviceExcelModel> deviceExcel(String gatewayId) throws IOException {
        Resource resource = new ClassPathResource("sql/device.sql");
        String replace = new String(Files.readAllBytes(resource.getFile().toPath()));
        String sql = replace.replace("#{gatewayId}", gatewayId);
        List<Map<String, Object>> list = query(dlBuss, sql);
        getRelation(list);
        ArrayList<DeviceExcelModel> deviceList = new ArrayList<>();
        setData(list, deviceList);
        return deviceList;
    }

    public void getRelation(List<Map<String, Object>> list) {
        String relationSql = "SELECT *  from iot_index_relation";
        List<Map<String, Object>> relationList = query(relation, relationSql);
        list.forEach(dev -> {
            List<Map<String, Object>> collect = relationList.stream().filter(r -> !ObjectUtils.isEmpty(dev.get("equipmentIndexKey")) && !ObjectUtils.isEmpty(dev.get("equipTypeCode")) && r.get("oldIndexCode").toString().equals(dev.get("equipmentIndexKey").toString())
                    && r.get("oldequipTypeCode").toString().equals(dev.get("equipTypeCode").toString())).collect(Collectors.toList());
            if (!ObjectUtils.isEmpty(collect) && collect.size() == 1) {
                Map<String, Object> objectMap = collect.get(0);
                dev.put("iotProperName", objectMap.get("newIndexName"));
                dev.put("deviceType", objectMap.get("newEquipType"));
            } else {
                dev.put("iotProperName", dev.get("iotProperName")+"-"+(!ObjectUtils.isEmpty(dev.get("equipmentIndexKey"))?dev.get("equipmentIndexKey"):"").toString()+"缺失");
                dev.put("deviceType", dev.get("deviceType")+"-"+(!ObjectUtils.isEmpty(dev.get("equipTypeCode"))?dev.get("equipTypeCode"):"").toString()+"缺失");
            }
        });
    }

    public void syncMainDeviceRelation(String orgCode) throws IOException {
        // 获取主设备关联物联设备记录
        Resource mainResource = new ClassPathResource("sql/mainDeviceRelation.sql");
        String mainSql = new String(Files.readAllBytes(mainResource.getFile().toPath()));
        List<Map<String, Object>> mainList = query(dlBuss, mainSql);
        // 构建入库sql
        execute(mainList, dataView, userName, pwd, orgCode);
    }

    public void syncPlanRelation() {
        // 查询原主设备信息
        String mainSql = "SELECT id , name ,code from f_equipment ";
        List<Map<String, Object>> mainList = query(dlBuss, mainSql);
        // 查询主设备绑定物联设备关系
        String mainRelationSql = "SELECT a.id,\n" +
                "a.equipment_id, \n" +
                "a.fire_equipment_id," +
                "(SELECT code from f_equipment b where b.id = a.equipment_id ) as mainCode,\n" +
                "(SELECT code from wl_equipment_specific b where b.id = a.fire_equipment_id ) as equipCode\n" +
                "from    f_equipment_fire_equipment a";
        List<Map<String, Object>> mainRelationList = query(dlBuss, mainRelationSql);
        // 构建更新sql
        executePlanRelation(mainList, mainRelationList, dataView, userName, pwd);
    }


    public void executePlanRelation(List<Map<String, Object>> mainList, List<Map<String, Object>> mainRelationList, String url, String username, String password) {
        // 视频信息入库
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Connection connection = null;
        Statement statement = null;
        String getIdSql = "(SELECT SEQUENCE_NBR from iot_device_info where DEVICE_CODE = %s)";
        try {
            // 获取数据库连接
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            Statement finalStatement = statement;
            // 主设备信息
            mainList.forEach(a -> {
                StringBuilder sql = new StringBuilder();
                sql.append("UPDATE `c_plan_equipment` SET `fire_equipment_code` =  ");
                sql.append("'");
                sql.append(a.get("code"));
                sql.append("', fire_equipment_name = ").append("'");
                sql.append(a.get("name")).append("'");
                sql.append(" WHERE `fire_equipment_id` = '").append(a.get("id")).append("'");
                try {
                    finalStatement.addBatch(sql.toString());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            });
            // 更新c_plan_equipment表主设备id
            String updateMainSql = "UPDATE c_plan_equipment a SET a.fire_equipment_id =  " +
                    "(SELECT SEQUENCE_NBR from iot_device_info where DEVICE_CODE =  a.fire_equipment_code and a.fire_equipment_code is not null )";
            finalStatement.addBatch(updateMainSql);

            mainRelationList.forEach(m -> {
                StringBuilder sql = new StringBuilder();
                sql.append("UPDATE `c_equipment_fire_equipment` SET `equipment_id` =  ");
                sql.append(String.format(getIdSql, "'" + m.get("mainCode") + "'"));
                sql.append(",");
                sql.append("equipment_specific_id = ");
                sql.append(String.format(getIdSql, "'" + m.get("equipCode") + "'"));
                sql.append(" where id = '");
                sql.append(m.get("id")).append("'");
                try {
                    finalStatement.addBatch(sql.toString());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            });
            String updateIdSql = "UPDATE c_equipment a SET a.id =  " +
                    "(SELECT SEQUENCE_NBR from iot_device_info where DEVICE_CODE =  a.code and a.code is not null )";
            finalStatement.addBatch(updateIdSql);
            // 执行批处理
            int[] result = statement.executeBatch();
            // 提交事务
            connection.commit();
            System.out.println("批量插入完成，影响行数: " + Arrays.toString(result));
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void syncMainVideoRelation(String orgCode, String agencyCode) {
        // 获取自动化中视频信息
        String videoSql = "SELECT \n" +
                "wv.id,\n" +
                "wv.name,\n" +
                "wv.address, \n" +
                "wv.video_type ,\n" +
                "wv.code\n" +
                "FROM `wl_video` wv";
        List<Map<String, Object>> videoList = query(dlBuss, videoSql);
        // 获取自动化中主设备和视频绑定关系
        String videoRelationSql = "SELECT wvie.id ,\n" +
                "wvie.video_id,\n" +
                "wvie.important_equipment_id,\n" +
                "we.code\n" +
                "from  wl_video_important_equipment wvie inner JOIN  f_equipment we on wvie.important_equipment_id = we.id";
        List<Map<String, Object>> videoRelationList = query(dlBuss, videoRelationSql);
        // 构建一张图数据入库
        executeVideo(videoList, videoRelationList, dataView, userName, pwd, orgCode, agencyCode);

    }

    public void executeVideo(List<Map<String, Object>> videoList, List<Map<String, Object>> videoRelationList, String url, String username, String password, String orgCode, String agencyCode) {
        // 视频信息入库
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Connection connection = null;
        Statement statement = null;
        try {
            // 获取数据库连接
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            Statement finalStatement = statement;
            videoList.forEach(v -> {
                try {
                    StringBuilder sql = new StringBuilder();
                    sql.append("INSERT INTO `iot_device_video`(" +
                            "`SEQUENCE_NBR`" +
                            ", `REC_USER_ID`" +
                            ", `REC_USER_NAME`" +
                            ", `CREATE_DATE`" +
                            ", `REC_DATE`" +
                            ", `DEVICE_LOCATION`" +
                            ", `STREAM_URL`" +
                            ", `DEVICE_NAME`" +
                            ", `DEVICE_IP`" +
                            ", `DEVICE_PORT`" +
                            ", `DEVICE_STATUS`" +
                            ", `DEVICE_CODE`" +
                            ", `DEVICE_MODEL`" +
                            ", `OTHER_PARAM`" +
                            ", `AGENCY_CODE`" +
                            ", `ORG_CODE`" +
                            ", `VIDEO_TYPE`) VALUES (");
                    sql.append(v.get("id"));
                    sql.append(",");
                    sql.append("'admin'");
                    sql.append(",");
                    sql.append("'admin'");
                    sql.append(",");
                    sql.append("'");
                    sql.append(sdf.format(new Date()));
                    sql.append("'");
                    sql.append(",");
                    sql.append("'");
                    sql.append(sdf.format(new Date()));
                    sql.append("'");
                    sql.append(",");
                    sql.append("'");
                    sql.append(v.get("address"));
                    sql.append("'");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("'");
                    sql.append(v.get("name"));
                    sql.append("'");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("'");
                    sql.append(v.get("code"));
                    sql.append("'");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("'").append(agencyCode).append("'");
                    sql.append(",");
                    sql.append("'").append(orgCode).append("'");
                    sql.append(",");
                    sql.append("'");
                    sql.append(v.get("video_type"));
                    sql.append("'");
                    sql.append(")");
                    finalStatement.addBatch(sql.toString());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            });
            // 绑定关系表
            videoRelationList.forEach(v -> {
                try {
                    StringBuilder sql = new StringBuilder();
                    sql.append("INSERT INTO `iot_device_bind_video`(" +
                            "`SEQUENCE_NBR`" +
                            ", `REC_USER_ID`" +
                            ", `REC_DATE`" +
                            ", `DEVICE_SEQ`" +
                            ", `VIDEO_SEQ`) VALUES (");
                    sql.append(v.get("id"));
                    sql.append(",");
                    sql.append("'admin'");
                    sql.append(",");
                    sql.append("'");
                    sql.append(sdf.format(new Date()));
                    sql.append("'");
                    sql.append(",");
                    sql.append("(SELECT SEQUENCE_NBR FROM iot_device_info WHERE DEVICE_CODE = '").append(v.get("code")).append("')");
                    sql.append(",");
                    sql.append("'");
                    sql.append(v.get("video_id"));
                    sql.append("'");
                    sql.append(")");
                    finalStatement.addBatch(sql.toString());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            });
            // 执行批处理
            int[] result = statement.executeBatch();
            // 提交事务
            connection.commit();
            System.out.println("批量插入完成，影响行数: " + Arrays.toString(result));
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    private void executeBySql(List<String> sqlList, String url, String username, String password) {
        Connection connection = null;
        Statement statement = null;
        try {
            // 获取数据库连接
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            Statement finalStatement = statement;
            for (String sql : sqlList) {
                log.debug("执sql：{}", sql);
                finalStatement.addBatch(sql);
            }
            // 执行批处理
            int[] result = statement.executeBatch();
            // 提交事务
            connection.commit();
            System.out.println("批量插入完成，影响行数: " + result.length);
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void execute(List<Map<String, Object>> params, String url, String username, String password, String orgCode) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Connection connection = null;
        Statement statement = null;
        try {
            // 获取数据库连接
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            Statement finalStatement = statement;
            params.forEach(p -> {
                try {
                    StringBuilder sql = new StringBuilder();
                    sql.append("INSERT INTO iot_device_bind_relation (SEQUENCE_NBR" +
                            ", DEVICE_SEQ" +
                            ", SUB_DEVICE_SEQ" +
                            ",SUB_DEVICE_ID" +
                            ",DEVICE_ID" +
                            ",REC_USER_ID" +
                            ", REC_USER_NAME" +
                            ", CREATE_DATE" +
                            ", REC_DATE) VALUES(");
                    sql.append(p.get("id"));
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("'");
                    sql.append(p.get("deviceCode"));
                    sql.append("'");
                    sql.append(",");
                    sql.append("'");
                    sql.append(p.get("mainDeviceCode"));
                    sql.append("'");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("null");
                    sql.append(",");
                    sql.append("'");
                    sql.append(sdf.format(new Date()));
                    sql.append("'");
                    sql.append(",");
                    sql.append("'");
                    sql.append(sdf.format(new Date()));
                    sql.append("'");
                    sql.append(")");
                    finalStatement.addBatch(sql.toString());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            });
            String updateSql = new StringBuilder().append("UPDATE iot_device_bind_relation a ")
                    .append("JOIN iot_device_info b1 ON b1.DEVICE_CODE = a.DEVICE_ID ")
                    .append("JOIN iot_device_info b2 ON b2.DEVICE_CODE = a.SUB_DEVICE_ID ")
                    .append("SET a.DEVICE_SEQ = b1.SEQUENCE_NBR,")
                    .append("    a.DEVICE_ID = b1.DEVICE_ID,")
                    .append("    a.SUB_DEVICE_SEQ = b2.SEQUENCE_NBR,")
                    .append("    a.SUB_DEVICE_ID = b2.DEVICE_ID")
                    .append(" WHERE a.DEVICE_ID IS NOT NULL AND a.SUB_DEVICE_ID IS NOT NULL and b1.ORG_CODE = ")
                    .append("'")
                    .append(orgCode)
                    .append("'").append(" and b2.ORG_CODE = ")
                    .append("'").append(orgCode).append("'").toString();
            // 更新数据
            finalStatement.addBatch(updateSql);
            // 执行批处理
            int[] result = statement.executeBatch();
            // 提交事务
            connection.commit();
            System.out.println("批量插入完成，影响行数: " + Arrays.toString(result));
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void buildingExcel(HttpServletResponse response, String gatewayId) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("数据导出", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        // 获取输出流
        try (OutputStream outputStream = response.getOutputStream()) {
            // 创建ExcelWriter
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            // 建筑信息
            WriteSheet buildingSheet = EasyExcel.writerSheet(0, "建筑信息")
                    .head(AreaExcelModel.class)
                    .build();
            excelWriter.write(areaExcel(), buildingSheet);
            // 系统信息
            WriteSheet systemSheet = EasyExcel.writerSheet(1, "系统信息")
                    .head(SystemExcelModel.class)
                    .build();
            excelWriter.write(systemInfo(), systemSheet);
            // 设备信息
            WriteSheet deviceSheet = EasyExcel.writerSheet(2, "设备信息")
                    .head(DeviceExcelModel.class)
                    .build();
            excelWriter.write(deviceExcel(gatewayId), deviceSheet);
            // 主设备信息
            WriteSheet mainDeviceSheet = EasyExcel.writerSheet(3, "主设备信息")
                    .head(DeviceExcelModel.class)
                    .build();
            excelWriter.write(mainDevice(), mainDeviceSheet);
            // 应急物资
            WriteSheet emergencySheet = EasyExcel.writerSheet(4, "应急物资")
                    .head(DeviceExcelModel.class)
                    .build();
            excelWriter.write(emergency(), emergencySheet);
            // 关闭ExcelWriter
            excelWriter.finish();
        }

    }

    public List<DeviceExcelModel> emergency() throws IOException {
        Resource mainResource = new ClassPathResource("sql/emergency.sql");
        String emergencySql = new String(Files.readAllBytes(mainResource.getFile().toPath()));
        List<Map<String, Object>> emergencyList = query(dlBuss, emergencySql);
        ArrayList<DeviceExcelModel> deviceExcelModels = new ArrayList<>();
        setData(emergencyList, deviceExcelModels);
        return deviceExcelModels;
    }

    public ArrayList<SystemExcelModel> systemInfo() throws IOException {
        Resource resource = new ClassPathResource("sql/systemInfo.sql");
        String sql = new String(Files.readAllBytes(resource.getFile().toPath()));
        List<Map<String, Object>> list = query(dlBuss, sql);
        ArrayList<SystemExcelModel> systemExcelModels = new ArrayList<>();
        list.forEach(a -> {
            SystemExcelModel systemExcelModel = new SystemExcelModel();
            systemExcelModel.setCategory(ObjectUtils.isEmpty(a.get("category")) ? "" : String.valueOf(a.get("category")));
            systemExcelModel.setCode(ObjectUtils.isEmpty(a.get("code")) ? "" : String.valueOf(a.get("code")));
            systemExcelModel.setParentCode(ObjectUtils.isEmpty(a.get("parentCode")) ? "" : String.valueOf(a.get("parentCode")));
            systemExcelModel.setType(ObjectUtils.isEmpty(a.get("type")) ? "" : String.valueOf(a.get("type")));
            systemExcelModel.setName(ObjectUtils.isEmpty(a.get("name")) ? "" : String.valueOf(a.get("name")));
            systemExcelModel.setGatewayName(ObjectUtils.isEmpty(a.get("gatewayName")) ? "" : String.valueOf(a.get("gatewayName")));
            systemExcelModels.add(systemExcelModel);
        });
        return systemExcelModels;
    }

    public List<DeviceExcelModel> mainDevice() throws IOException {
        // 主设备信息
        Resource mainResource = new ClassPathResource("sql/mainDevice.sql");
        String mainSql = new String(Files.readAllBytes(mainResource.getFile().toPath()));
        List<Map<String, Object>> mainList = query(dlBuss, mainSql);
        ArrayList<DeviceExcelModel> deviceExcelModels = new ArrayList<>();
        setData(mainList, deviceExcelModels);
        return deviceExcelModels;
    }

    public void setData(List<Map<String, Object>> list, List<DeviceExcelModel> models) {
        list.forEach(a -> {
            DeviceExcelModel model = new DeviceExcelModel();
            model.setGatewayDevice(ObjectUtils.isEmpty(a.get("gatewayDevice")) ? "" : String.valueOf(a.get("gatewayDevice")));
            model.setPointType(ObjectUtils.isEmpty(a.get("pointType")) ? "" : String.valueOf(a.get("pointType")));
            model.setPointDataType(ObjectUtils.isEmpty(a.get("pointDataType")) ? "" : String.valueOf(a.get("pointDataType")));
            model.setAlarmConfig(ObjectUtils.isEmpty(a.get("alarmConfig")) ? "" : String.valueOf(a.get("alarmConfig")));
            model.setAreaCode(ObjectUtils.isEmpty(a.get("areaCode")) ? "" : String.valueOf(a.get("areaCode")));
            model.setAddress(ObjectUtils.isEmpty(a.get("address")) ? "" : String.valueOf(a.get("address")));
            model.setPointName(ObjectUtils.isEmpty(a.get("pointName")) ? "" : String.valueOf(a.get("pointName")));
            model.setIotProperName(ObjectUtils.isEmpty(a.get("iotProperName")) ? "" : String.valueOf(a.get("iotProperName")));
            model.setDeviceCode(ObjectUtils.isEmpty(a.get("deviceCode")) ? "" : String.valueOf(a.get("deviceCode")));
            model.setDeviceType(ObjectUtils.isEmpty(a.get("deviceType")) ? "" : String.valueOf(a.get("deviceType")));
            model.setDeviceName(ObjectUtils.isEmpty(a.get("deviceName")) ? "" : String.valueOf(a.get("deviceName")));
            model.setSpecs(ObjectUtils.isEmpty(a.get("specs")) ? "" : String.valueOf(a.get("specs")));
            model.setUseDate(ObjectUtils.isEmpty(a.get("useDate")) ? "" : String.valueOf(a.get("useDate")));
            model.setManufacturer(ObjectUtils.isEmpty(a.get("manufacturer")) ? "" : String.valueOf(a.get("manufacturer")));
            model.setMainDate(ObjectUtils.isEmpty(a.get("mainDate")) ? "" : String.valueOf(a.get("mainDate")));
            model.setScraplife(ObjectUtils.isEmpty(a.get("scraplife")) ? "" : String.valueOf(a.get("scraplife")));
            model.setPeriod(ObjectUtils.isEmpty(a.get("period")) ? "" : String.valueOf(a.get("period")));
            model.setSystem(ObjectUtils.isEmpty(a.get("system")) ? "" : String.valueOf(a.get("system")));
            model.setLongitude(ObjectUtils.isEmpty(a.get("longitude")) ? "" : String.valueOf(a.get("longitude")));
            model.setLatitude(ObjectUtils.isEmpty(a.get("latitude")) ? "" : String.valueOf(a.get("latitude")));
            model.setLocation(ObjectUtils.isEmpty(a.get("location")) ? "" : String.valueOf(a.get("location")));
            model.setDeviceNum(ObjectUtils.isEmpty(a.get("deviceNum")) ? "" : String.valueOf(a.get("deviceNum")));
            model.setDeviceState(ObjectUtils.isEmpty(a.get("deviceState")) ? "" : String.valueOf(a.get("deviceState")));
            models.add(model);
        });
    }

    public void personExcel(HttpServletResponse response) throws IOException {
        String sql1 = "select * from cb_org_usr where biz_org_type ='PERSON'";
        List<Map<String, Object>> list1 = query(dlBuss, sql1);
        String sql2 = "SELECT * FROM cb_dynamic_form_instance WHERE instance_id in (";
        if (ObjectUtils.isEmpty(list1)) {
            return;
        }
        List<String> arrayList = new ArrayList<>();
        for (Map<String, Object> map : list1) {
            arrayList.add(map.get("sequence_nbr").toString());
        }
        if (ObjectUtils.isEmpty(arrayList)) {
            return;
        }
        sql2 = sql2 + String.join(",", arrayList) + ") and field_value is not NULL and field_value !=''";
        List<Map<String, Object>> list2 = query(dlCom, sql2);
        ArrayList<PersonExcelModel> models = new ArrayList<>();
        String sql3 = "select * from cb_firefighters_post where org_usr_id in (" + String.join(",", arrayList) + ")";
        List<Map<String, Object>> list3 = query(dlCom, sql3);
        String sql4 = "select CONCAT(b.name,'-',a.name) as name  from cb_data_dictionary a " +
                "left join cb_data_dictionary b  on a.parent = b.sequence_nbr " +
                "where a.`type` ='GWZZ' and a.parent is not null";
        List<Map<String, Object>> list4 = query(dlCom, sql4);
        for (Map<String, Object> map : list1) {
            PersonExcelModel model = new PersonExcelModel();
            model.setName(ObjectUtils.isEmpty(map.get("biz_org_name")) ? "" : String.valueOf(map.get("biz_org_name")));
//            list2 = list2.stream().filter(v->v.containsKey("field_value")&&!ObjectUtils.isEmpty(v.get("field_value"))).collect(Collectors.toList());
            for (Map<String, Object> objectMap : list2) {
                if (map.get("sequence_nbr").toString().equals(objectMap.get("instance_id").toString())) {
                    if (objectMap.get("field_code").equals("personNumber") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
//                        model.setCode(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
                        model.setNum(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
                    }
                    if (objectMap.get("field_code").equals("peopleType") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
                        model.setType(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
                    }
//                    if (objectMap.get("field_code").equals("positionType") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
//                        model.setFirePost(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
//                    }
//                    if (objectMap.get("field_code").equals("fireManagementPostCode") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
//                        model.setPost(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
//                    }
                    if (objectMap.get("field_code").equals("telephone") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
                        model.setPhone(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
                    }
                    if (objectMap.get("field_code").equals("gender") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
                        model.setGender(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
                    }
                    if (objectMap.get("field_code").equals("certificatesTypeCode") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
                        String s = ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString();
                        model.setIdType("120".equals(s) ? "身份证" : s);
                    }
                    if (objectMap.get("field_code").equals("certificatesNumber") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
                        model.setIdNum(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
                    }
                    if (objectMap.get("field_code").equals("birthdayTime") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
                        model.setBirth(objectMap.get("field_value").toString().substring(0, 10));
                    }
//                    if (objectMap.get("field_code").equals("birthdayTime") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
//                        model.setQualification(ObjectUtils.isEmpty(objectMap.get("field_value_label"))?objectMap.get("field_value").toString():objectMap.get("field_value_label").toString());
//                    }
                    if (objectMap.get("field_code").equals("safetyTraining") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
                        model.setSafetyTraining(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
                    }
                    if (objectMap.get("field_code").equals("safetyTraining") && !ObjectUtils.isEmpty(objectMap.get("field_value"))) {
                        model.setAdmitted(ObjectUtils.isEmpty(objectMap.get("field_value_label")) ? objectMap.get("field_value").toString() : objectMap.get("field_value_label").toString());
                    }
                }
            }
            for (Map<String, Object> objectMap : list3) {
                if (map.get("sequence_nbr").toString().equals(objectMap.get("org_usr_id").toString())) {
                    if (!ObjectUtils.isEmpty(objectMap.get("post_qualification"))) {
                        String qualification = objectMap.get("post_qualification").toString();
                        List<Map<String, Object>> maps = list4.stream().filter(v -> v.get("name").toString().contains(qualification)).collect(Collectors.toList());
                        if (!ObjectUtils.isEmpty(maps)) {
                            model.setQualification(maps.get(0).get("name").toString());
                        }
                    }
                    if (!ObjectUtils.isEmpty(objectMap.get("qualification_certificate"))) {
                        model.setFirePost(objectMap.get("qualification_certificate").toString());
                    }
                    if (!ObjectUtils.isEmpty(objectMap.get("job_title"))) {
                        model.setPost(objectMap.get("job_title").toString());
                    }
                }
            }
            model.setObjType("人员");
            models.add(model);
        }
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("人员信息", "UTF-8") + ".xlsx");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            EasyExcel.write(outputStream).head(PersonExcelModel.class).sheet("Sheet1").doWrite(models);
        } catch (IOException e) {
            log.error("dynamicExportSPTemplateExcel error:", e);
        }
    }
}
