api/src/test/java/com/xjrsoft/module/job/HikvisionBaseDataTaskTest.java

package com.xjrsoft.module.job;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.xjrsoft.XjrSoftApplication;
import com.xjrsoft.common.mybatis.SqlRunnerAdapter;
import com.xjrsoft.common.utils.SqlRunnerAdapterUtil;
import com.xjrsoft.module.base.entity.BaseClass;
import com.xjrsoft.module.evaluate.controller.EvaluateItemController;
import com.xjrsoft.module.hikvision.entity.HikvisionData;
import com.xjrsoft.module.hikvision.mapper.HikvisionDataMapper;
import com.xjrsoft.module.hikvision.util.ApiUtil;
import com.xjrsoft.module.hikvision.util.DataUtil;
import com.xjrsoft.module.hikvision.util.OutInRecordUtil;
import com.xjrsoft.module.organization.entity.Department;
import com.xjrsoft.module.teacher.entity.XjrUser;
import com.xjrsoft.module.teacher.mapper.FaceImportMapper;
import com.yomahub.liteflow.util.JsonUtil;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author dzx
 * @date 2024/5/14
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = XjrSoftApplication.class)
class HikvisionBaseDataTaskTest {
    @Autowired
    private FaceImportMapper faceImportMapper;

    @Autowired
    EvaluateItemController evaluateItemController;

    OutInRecordUtil outInRecordUtil = new OutInRecordUtil();

    @Autowired
    private HikvisionDataMapper hikvisionDataMapper;

    @Test
    void test2(){
        String sql = "select * from base_class";
        List<Map<String, Object>> maps = SqlRunnerAdapter.db().selectList(sql, BaseClass.class);

        for (Map<String, Object> map : maps) {
            BaseClass baseClass = SqlRunnerAdapterUtil.convertMapToEntity(map, BaseClass.class);

            System.out.println(JsonUtil.toJsonString(baseClass));
        }


        sql = "SELECT DISTINCT LENGTH(hierarchy) FROM xjr_department WHERE delete_mark = 0";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);

        Set<Integer> levelSet = new HashSet<>();
        for (Map<String, Object> strings : list) {
            Integer[] strings1 = SqlRunnerAdapterUtil.convertMapToIntegerArray(strings);
            System.out.println(strings1.toString());
            levelSet.add(strings1[0]);
        }
    }

    @Test
    void syncStudentData() throws InterruptedException {
        DataUtil dataUtil = new DataUtil();
        String tableName = "base_student";
        List<HikvisionData> studentList = hikvisionDataMapper.selectList(
                new QueryWrapper<HikvisionData>().lambda().eq(HikvisionData::getTableName, tableName)
        );
        Map<String, String> stringStringMap = dataUtil.insertStudentOne(tableName, null, studentList, 1827234730185633794L);
        System.out.println(JsonUtil.toJsonString(stringStringMap));
    }

    @Test
    void selectOrg(){
        String apiPath = "/api/resource/v1/org/orgList";
        ApiUtil apiUtil = new ApiUtil();

        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("pageNo", 1);
        jsonObject.addProperty("pageSize", 500);

        String doPost = apiUtil.doPost(apiPath, jsonObject.toString(), null);
        System.out.println(doPost);

        String sql = "SELECT * FROM hikvision_data WHERE 1 = 1 and table_name = 'base_class'";
        List<Map<String, Object>> maps = SqlRunnerAdapter.db().selectList(sql);
        List<String> sourceIds = new ArrayList<>();
        for (Map<String, Object> map : maps) {
            sourceIds.add(map.get("source_id").toString());
        }
        JsonParser jsonParser = new JsonParser();
        JsonElement parse = jsonParser.parse(doPost);
        JsonObject resultJson = parse.getAsJsonObject();

        String tableName = "hikvision_data";
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
        if("0".equals(resultJson.get("code").getAsString()) && "success".equals(resultJson.get("msg").getAsString())){
            JsonArray array = resultJson.get("data").getAsJsonObject().get("list").getAsJsonArray();
            for (JsonElement jsonElement : array) {
                JsonObject json = jsonElement.getAsJsonObject();
                String orgIndexCode = json.get("orgIndexCode").getAsString();
                if(sourceIds.contains(orgIndexCode)){
                    continue;
                }

                Entity entity = Entity.create(tableName);
                entity.set("source_id", orgIndexCode);
                entity.set("table_name", "base_class");
                entity.set("hikvision_id", orgIndexCode);
                entity.set("create_date", sdf.format(new Date()));
                SqlRunnerAdapter.db().dynamicInsert(tableName, entity);
            }
        }


    }


    @Test
    void test() throws Exception {
        String sql = "SELECT distinct table_name FROM hikvision_data WHERE 1 = 1";
        List<Map<String, Object>> maps = SqlRunnerAdapter.db().selectList(sql, HikvisionData.class);
        Set<String> tables = new HashSet<>();
        for (Map<String, Object> map : maps) {
            String[] strings = SqlRunnerAdapterUtil.convertMapToStringArray(map);
            tables.add(strings[0]);
        }
        sql = "SELECT * FROM hikvision_data WHERE 0 = 0";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, HikvisionData.class);
        Map<String, Map<String, String>> dataMap = new HashMap<>();
        for (String table : tables) {
            Map<String, String> tableData = new HashMap<>();
            for (Map<String, Object> hikvision : list) {

                if(!table.equals(hikvision.get("table_name").toString())){
                    continue;
                }
                tableData.put(hikvision.get("source_id").toString(), hikvision.get("hikvision_id").toString());
            }
            dataMap.put(table, tableData);
        }
        DataUtil dataUtil = new DataUtil();
        String tableName = "xjr_department";
        Map<String, String> department = dataMap.get(tableName);//dataUtil.insertDepartment(use, tableName, dataMap.get(tableName));
        if(department.isEmpty() && dataMap.get(tableName) != null){
            department = dataMap.get(tableName);
        }
//
//        //推送教职工
        tableName = "base_teacher";
//        insertTeacher(tableName, dataMap.get(tableName), department);
//
        //推送车辆

//        String carTableName = "car_message_apply";
//        Map<String, String> baseClass = dataMap.get(carTableName);
//        dataUtil.insertCar(use, carTableName,baseClass, faceImportMapper);


        tableName = "base_class";
//        Map<String, String> baseClass = dataMap.get(tableName);
//        Map<String, String> classes = dataUtil.insertClass(tableName, baseClass);


        tableName = "base_student";
        List<HikvisionData> studentList = hikvisionDataMapper.selectList(
                new QueryWrapper<HikvisionData>().lambda().eq(HikvisionData::getTableName, tableName)
        );
        Map<String, String> baseStudent = dataMap.get(tableName);
        insertStudentOne(tableName, baseStudent, studentList);
//        sql = "SELECT * FROM wf_room_applicant WHERE delete_mark = 0 AND STATUS = 1";
//        List<Map<String, Object>> wfRoomApplicantList = SqlRunnerAdapter.db().selectList(sql);
//
//        Map<String, String> baseStudent = dataMap.get(tableName);
//        for (Map<String, Object> objectMap : wfRoomApplicantList) {
//            Long applicantUserId = Long.parseLong(objectMap.get("applicant_user_id").toString());
//            Map<String, String> student = dataUtil.insertStudentOne(tableName, baseStudent, studentList, applicantUserId);
//        }


//        selectOrg(use, "base_class");

        //查询车辆
//        String carTableName = "car_message_apply";
//        selectCar(use, carTableName);

//        outInRecordUtil.GetVehicleRecord(faceImportMapper);
//        outInRecordUtil.GetTeacherAndStudentRecords(faceImportMapper);
////        selecAllPersonById(use);
//        selectResource(use);
    }

    @Test
    void selectPerson(){
        String sql = "SELECT * FROM xjr_user WHERE id IN (\n" +
                "SELECT source_id FROM hikvision_data WHERE hikvision_id LIKE '%-%' AND table_name = 'base_teacher'\n" +
                ")";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, String[].class);
    }

    @Test
    void testCarInsert() throws Exception {
        //outInRecordUtil.GetVehicleRecord(faceImportMapper);
    }

    public static void selectCar(Db db, String tableName) throws SQLException {
        String sql = "SELECT * FROM " + tableName + " WHERE delete_mark = 0";
        List<Department> dataList = db.query(sql, Department.class);
        String apiPath = "/api/resource/v2/vehicle/advance/vehicleList";
        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("pageNo", 1);
        jsonObject.addProperty("pageSize", 500);
        ApiUtil apiUtil = new ApiUtil();
        String result = apiUtil.doPost(apiPath, jsonObject.toString(), null);
        System.out.println("-----------------------------------------------------");
        System.out.println(result);
    }

    public static void insertRecord(Db db, String tableName, Map<?, String> idsMap) throws SQLException {
        if (idsMap.isEmpty()) {
            return;
        }
        var sqls = new ArrayList<String>();
        for (var entry : idsMap.entrySet()) {
            var sourceId = entry.getKey();
            var sql = "INSERT INTO hikvision_data(create_date, table_name, source_id, hikvision_id) " +
                    "VALUES(now(), '" + tableName + "', '" + sourceId + "', '" + entry.getValue() + "')";
            sqls.add(sql);
        }
        db.executeBatch(sqls);
    }

    public Map<String, String> insertStudentOne(String tableName, Map<String, String> tableData, List<HikvisionData> studentList) throws Exception {
        String sql = "SELECT t1.id,t1.name,CONCAT(t3.id,'-',replace(t4.name,'级',''),'-',t3.class_type,'-',t2.stduy_status) as orgIndexCode," +
                " t1.user_name,t1.gender,DATE_FORMAT(t1.birth_date, '%Y-%m-%d') as birthday,t1.mobile,t1.email,t1.credential_type,t1.credential_number FROM xjr_user t1" +
                " INNER JOIN base_student_school_roll t2 ON t1.id = t2.user_id" +
                " INNER JOIN base_class t3 ON t2.class_id = t3.id" +
                " INNER JOIN base_grade t4 ON t3.grade_id = t4.id" +
                " WHERE t1.delete_mark = 0 AND t2.delete_mark = 0" +
                " AND t3.class_type IS NOT NULL AND t3.is_graduate = 1" +
                " and t1.credential_number IN (" +
                " SELECT t3.credential_number\n" +
                "        FROM student_report_record t1\n" +
                "        INNER JOIN base_new_student t3 ON t3.id = t1.user_id\n" +
                "        LEFT JOIN banding_task_class_student t2 ON t1.user_id = t2.new_student_id AND t2.delete_mark = 0\n" +
                "        LEFT JOIN banding_task_class t5 ON t2.banding_task_class_id = t5.id\n" +
                "        LEFT JOIN xjr_user t6 ON t5.teacher_id = t6.id\n" +
                "        LEFT JOIN xjr_dictionary_detail t7 ON t3.gender = t7.code AND t7.delete_mark = 0\n" +
                "        LEFT JOIN xjr_dictionary_detail t8 ON t3.source = t8.code AND t8.delete_mark = 0\n" +
                "        LEFT JOIN xjr_dictionary_detail t9 ON t3.stduy_status = t9.code AND t9.delete_mark = 0\n" +
                "        LEFT JOIN banding_task t10 ON t5.banding_task_id = t10.id\n" +
                "        LEFT JOIN enrollment_plan t11 ON t11.id = t3.enrollment_plan_id\n" +
                "        LEFT JOIN base_grade t4 ON t11.grade_id = t4.id\n" +
                "        LEFT JOIN student_report_plan t12 ON t12.id = t1.student_report_plan_id\n" +
                "        LEFT JOIN base_major_set t13 ON t13.id = t5.major_set_id\n" +
                "        LEFT JOIN xjr_dictionary_detail t14 ON t5.class_type = t14.code AND t14.delete_mark = 0\n" +
                "        LEFT JOIN xjr_department t15 ON t15.id = t13.department_id\n" +
                "        WHERE t1.delete_mark = 0 AND t1.enabled_mark = 1\n" +
                "        AND t12.status IN(1,2)\n" +
                "        AND t3.enrollment_plan_id = 1897950348853186562\n" +
                "        AND t12.category = 2\n" +
                "        AND t1.report_time IS NOT NULL" +
                " )";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, String[].class);
        Map<Integer, String> clientMap = new HashMap<>();
        List<Entity> insertList = new ArrayList<>();


        Map<String, String> historyMap = new HashMap<>();
        for (HikvisionData hikvisionData : studentList) {
            historyMap.put(hikvisionData.getSourceId(), hikvisionData.getSendData());
        }

        Map<String, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        ApiUtil apiUtil = new ApiUtil();
        Map<String, String> header = new HashMap<>();
        header.put("tagId", "insert_student");

        List<JsonObject> addList = new ArrayList<>();
        List<JsonObject> updateList = new ArrayList<>();

        for(int i = 0; i < list.size(); i ++){
            Map<String, Object> objectMap = list.get(i);
            String id = objectMap.get("id").toString();

            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("clientId", i);
            paramJson.addProperty("personId", id);
            paramJson.addProperty("personName", objectMap.get("name").toString());
            paramJson.addProperty("orgIndexCode", objectMap.get("orgIndexCode").toString());
            paramJson.addProperty("phoneNo", objectMap.get("mobile")==null?"":objectMap.get("mobile").toString());
            paramJson.addProperty("jobNo", objectMap.get("user_name").toString());
            paramJson.addProperty("birthday", objectMap.get("birthday")==null?"":objectMap.get("birthday").toString());
            paramJson.addProperty("personType", 2);

            int gender = 0;
            String genderData = objectMap.get("gender") == null ? "" : objectMap.get("gender").toString();
            switch (genderData) {
                case "SB10001":
                    gender = 1;
                    break;
                case "SB10002":
                    gender = 2;
                    break;
                default:
                    gender = 0;
                    break;
            }
            paramJson.addProperty("gender", gender);

            if(historyMap.containsKey(id)){
                if(historyMap.get(id) ==null || !paramJson.toString().equals(historyMap.get(id))){
                    updateList.add(paramJson);
                }
                continue;
            }

            addList.add(paramJson);
        }

        String apiPath = "/api/resource/v1/person/single/add";
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
        for (JsonObject paramJson : addList) {
            String result = apiUtil.doPost(apiPath, paramJson.toString(), null, header);
            JsonElement parse = jsonParser.parse(result);
            JsonObject resultJson = parse.getAsJsonObject();
            if("0".equals(resultJson.get("code").getAsString()) && "success".equals(resultJson.get("msg").getAsString())){
                Entity entity = Entity.create(tableName);
                entity.set("table_name", tableName);
                entity.set("create_date", sdf.format(new Date()));
                entity.set("source_id", clientMap.get(paramJson.get("clientId").getAsInt()));
                entity.set("hikvision_id", paramJson.get("personId").getAsString());
                entity.set("send_data", paramJson.toString());
                insertList.add(entity);
            }else if("0x00052102".equals(resultJson.get("code").getAsString()) && "PersonId Already In Db".equals(resultJson.get("msg").getAsString())){
                Entity entity = Entity.create(tableName);
                entity.set("table_name", tableName);
                entity.set("create_date", sdf.format(new Date()));
                entity.set("source_id", paramJson.get("personId").getAsString());
                entity.set("hikvision_id", paramJson.get("personId").getAsString());
                entity.set("send_data", paramJson.toString());
                insertList.add(entity);
            }
        }
        tableName = "hikvision_data";
        if(!insertList.isEmpty()){
            SqlRunnerAdapter.db().dynamicInsertBatch(tableName, insertList);
        }

        apiPath = "/api/resource/v1/person/single/update";
        for (JsonObject paramJson : updateList) {
            Thread.sleep(500);
            String result = apiUtil.doPost(apiPath, paramJson.toString(), null, header);
            JsonElement parse = jsonParser.parse(result);
            JsonObject resultJson = parse.getAsJsonObject();
            if("0".equals(resultJson.get("code").getAsString()) && "success".equals(resultJson.get("msg").getAsString())){
                Entity entity = Entity.create(tableName);
                entity.set("send_data", paramJson.toString());
                Entity where = Entity.create(tableName);
                where.set("source_id", paramJson.get("personId").getAsString());
                SqlRunnerAdapter.db().dynamicUpdate(tableName, entity, where);
            }
        }
        //插入记录表
        return idMap;
    }



    @Test
    void deleteClass() {

        String apiPath = "/api/resource/v1/org/batch/delete";
        JsonObject jsonObject = new JsonObject();
        ApiUtil apiUtil = new ApiUtil();

        JsonArray dataArray = new JsonArray();
        dataArray.add("2025-job_class-FB3002");
        JsonObject paramJson = new JsonObject();
        paramJson.add("indexCodes", dataArray);
        Map<String, String> headerMap = new HashMap<>();
        headerMap.put("tagId", "deleteOrg");
        String result = apiUtil.doPost(apiPath, paramJson.toString(), null, headerMap);
        System.out.println(result);
    }

    /**
     * 查询资源信息
     * @param db
     * @throws SQLException
     */
    void selectResource(Db db) throws SQLException {
        String apiPath = "/api/irds/v2/resource/resourcesByParams";
        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("pageNo", 1);
        jsonObject.addProperty("pageSize", 500);
        jsonObject.addProperty("resourceType", "door");


        ApiUtil apiUtil = new ApiUtil();
        String result = apiUtil.doPost(apiPath, jsonObject.toString(), null, null);
        System.out.println("------------------------------------------------------------------------");
        System.out.println(result);
    }




    @Test
    void selecAllPersonById() throws SQLException {
        String apiPath = "/api/resource/v2/person/advance/personList";

        String sql = "SELECT t1.id, t1.name,t1.user_name,t2.dept_id FROM xjr_user t1\n" +
                "INNER JOIN xjr_user_dept_relation t2 ON t1.id = t2.user_id\n" +
                " WHERE t1.id IN (\n" +
                "SELECT source_id FROM hikvision_data WHERE hikvision_id LIKE '%-%' AND table_name = 'base_teacher'\n" +
                ")";
        List<Map<String, Object>> maps = SqlRunnerAdapter.db().selectList(sql);
        ApiUtil apiUtil = new ApiUtil();

        Map<String, String> userInfoMap = new HashMap<>();

        JsonParser parser = new JsonParser();
        for (Map<String, Object> map : maps) {
            String name = map.get("name").toString();
            JsonObject jsonObject = new JsonObject();
            jsonObject.addProperty("pageNo", 1);
            jsonObject.addProperty("pageSize", 500);
            jsonObject.addProperty("personName", name);
            jsonObject.addProperty("orgIndexCodes", map.get("dept_id").toString());
            String result = apiUtil.doPost(apiPath, jsonObject.toString(), null, null);
            JsonObject asJsonObject = parser.parse(result).getAsJsonObject();
            if("0".equals(asJsonObject.get("code").getAsString())){
                JsonArray jsonArray = asJsonObject.get("data").getAsJsonObject().get("list").getAsJsonArray();
                if(jsonArray.size() == 0){
                    continue;
                }
                JsonObject object = jsonArray.get(0).getAsJsonObject();
                if(name.equals(object.get("personName").getAsString()) && map.get("user_name").toString().equals(object.get("jobNo").getAsString())){
                    userInfoMap.put(map.get("id").toString(), object.get("personId").getAsString());
                }
            }
        }
        System.out.println(userInfoMap);
    }

    void selecAllPerson(Db db) throws SQLException {
        String apiPath = "/api/resource/v2/person/personList";
        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("pageNo", 1);
        jsonObject.addProperty("pageSize", 500);

        ApiUtil apiUtil = new ApiUtil();
        String result = apiUtil.doPost(apiPath, jsonObject.toString(), null, null);

        System.out.println(result);

        String sql = "SELECT t1.* FROM xjr_user t1" +
                " INNER JOIN base_teacher t2 ON t1.id = t2.user_id" +
                " WHERE t1.delete_mark = 0 AND t2.delete_mark = 0";
        List<XjrUser> dataList = db.query(sql, XjrUser.class);
        Map<String, Long> phoneMap = new HashMap<>();
        for (XjrUser xjrUser : dataList) {
            phoneMap.put(xjrUser.getMobile(), xjrUser.getId());
        }

        JsonParser jsonParser = new JsonParser();
        JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
        JsonArray array = resultJson.get("data").getAsJsonObject().get("list").getAsJsonArray();
        Map<Long, String> idMap = new HashMap<>();
        for (JsonElement jsonElement : array) {
            JsonObject element = jsonElement.getAsJsonObject();
            if(!element.has("phoneNo") || element.get("phoneNo").isJsonNull()){
                System.out.println(element.toString());
                continue;
            }
            String phoneNo = element.get("phoneNo").getAsString();

            idMap.put(phoneMap.get(phoneNo), element.get("personId").getAsString());
        }

        insertRecord(db, "base_teacher", idMap);

    }

    void selectOrg(Db db, String tableName) throws SQLException {
        String sql = "SELECT * FROM " + tableName + " WHERE delete_mark = 0";
        List<Department> dataList = db.query(sql, Department.class);
        String apiPath = "/api/resource/v1/org/orgList";
        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("pageNo", 1);
        jsonObject.addProperty("pageSize", 500);
        ApiUtil apiUtil = new ApiUtil();
        String result = apiUtil.doPost(apiPath, jsonObject.toString(), null);
        System.out.println("-----------------------------------------------------");
        System.out.println(result);
    }


    @Test
    void deleteOrg(Db db, String tableName) throws SQLException {
        String sql = "SELECT * FROM " + tableName + " WHERE delete_mark = 0 ORDER BY LENGTH(hierarchy)";
        List<Department> dataList = db.query(sql, Department.class);
        String apiPath = "/api/resource/v1/org/batch/delete";
        Map<Long, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        ApiUtil apiUtil = new ApiUtil();
        JsonArray dataArray = new JsonArray();
        for (Department department : dataList) {
            dataArray.add(department.getId().toString());
        }

        JsonObject paramJson = new JsonObject();
        paramJson.add("indexCodes", dataArray);
        Map<String, String> headerMap = new HashMap<>();
        headerMap.put("tagId", "deleteOrg");
        String result = apiUtil.doPost(apiPath, paramJson.toString(), null, headerMap);
        System.out.println(result);
    }


    public Map<String, String> insertTeacher(String tableName, Map<String, String> tableData, Map<String, String> deptMap){
        String sql = "SELECT distinct t1.id,t1.name,ifnull(t1.gender,'SB10001') as gender,t3.dept_id,t1.birth_date,t1.mobile,t1.email,t1.credential_type,t1.credential_number,t1.user_name FROM xjr_user t1" +
                " INNER JOIN " + tableName + " t2 ON t1.id = t2.user_id" +
                " INNER JOIN xjr_user_dept_relation t3 ON t1.id = t3.user_id" +
                " INNER JOIN xjr_department t4 ON t3.dept_id = t4.id " +
                " WHERE t1.delete_mark = 0 AND t2.delete_mark = 0 AND t4.delete_mark = 0" +
                " AND t1.name = '徐海洋'";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, String[].class);
        JsonArray paramArray = new JsonArray();
        Map<Integer, String> clientMap = new HashMap<>();
        for(int i = 0; i < list.size(); i ++){
            Map<String, Object> objectMap = list.get(i);
            if(tableData != null && tableData.containsKey(objectMap.get("id").toString())){
                //continue;
            }
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("clientId", i);
            paramJson.addProperty("personId", objectMap.get("id").toString() + i);
            paramJson.addProperty("personName", objectMap.get("name").toString());
            paramJson.addProperty("orgIndexCode", objectMap.get("dept_id").toString());
            paramJson.addProperty("phoneNo", objectMap.get("mobile")==null?"":objectMap.get("mobile").toString());
            paramJson.addProperty("jobNo", objectMap.get("user_name").toString());
            paramJson.addProperty("birthday", objectMap.get("birth_date")==null?"":objectMap.get("birth_date").toString());
            paramJson.addProperty("personType", 1);

            int gender = 1;
            switch (objectMap.get("gender").toString()) {
                case "SB10001":
                    gender = 1;
                    break;
                case "SB10002":
                    gender = 2;
                    break;
                default:
                    gender = 0;
                    break;
            }
            paramJson.addProperty("gender", gender);
            paramArray.add(paramJson);

            clientMap.put(i, objectMap.get("id").toString());
        }

        String apiPath = "/api/resource/v1/person/batch/add";
        Map<String, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        ApiUtil apiUtil = new ApiUtil();
        Map<String, String> header = new HashMap<>();
        header.put("tagId", "insert_teacher");

        String result = apiUtil.doPost(apiPath, paramArray.toString(), null, header);
        JsonElement parse = jsonParser.parse(result);
        JsonObject resultJson = parse.getAsJsonObject();
        if("0".equals(resultJson.get("code").getAsString()) && "success".equals(resultJson.get("msg").getAsString())){
            JsonArray array = resultJson.get("data").getAsJsonObject().get("successes").getAsJsonArray();
            for (JsonElement jsonElement : array) {
                JsonObject jsonObject = jsonElement.getAsJsonObject();
                idMap.put(clientMap.get(jsonObject.get("clientId").getAsInt()), jsonObject.get("personId").getAsString());
            }
        }else{

        }
        //插入记录表

        insertRecord(tableName, idMap);
        return idMap;
    }

    void insertRecord(String tableName, Map<?, String> idsMap){
        if (idsMap.isEmpty()) {
            return;
        }
        for (var entry : idsMap.entrySet()) {
            var sourceId = entry.getKey();
            String sql = "INSERT INTO hikvision_data(create_date, table_name, source_id, hikvision_id) " +
                    "VALUES(now(), '" + tableName + "', '" + sourceId + "', '" + entry.getValue() + "')";
            SqlRunnerAdapter.db().insert(sql);
        }
    }


    @Test
    void deletePerson() throws SQLException {
        String sql = "SELECT * FROM xjr_user WHERE id IN (\n" +
                "SELECT source_id FROM hikvision_data WHERE table_name IN( 'base_teacher','base_student')\n" +
                "AND hikvision_id LIKE '%-%'\n" +
                "AND hikvision_id NOT IN('14954805813957-14','14954812448325-19','14954812339909-17','14954812392774-18')\n" +
                ") AND id NOT IN ('14954807016389', '14954806910277')\n";
        List<Map<String, Object>> maps = SqlRunnerAdapter.db().selectList(sql);


        String apiPath = "/api/resource/v1/person/batch/delete";
        JsonObject paramJson = new JsonObject();
        JsonArray personIds = new JsonArray();
        for (Map<String, Object> map : maps) {

            personIds.add(map.get("id").toString());
        }
        paramJson.add("personIds", personIds);

        ApiUtil apiUtil = new ApiUtil();

        Map<String, String> header = new HashMap<>();
        header.put("tagId", "delete_teacher");
        String result = apiUtil.doPost(apiPath, paramJson.toString(), null, header);

        System.out.println(result);
    }

    /**
     * 同步海康学生信息到hikvision_data表中
     */
    @Test
    void syncStudentId(){
        String sql = "SELECT t1.* FROM hikvision_data t1" +
                " INNER JOIN base_class t2 ON t1.source_id LIKE CONCAT(t2.id, '%')" +
                " WHERE t1.table_name = 'base_class'" +
                " AND t2.grade_id = 1868532405673439232";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        List<String> orgIndexCodes = new ArrayList<>();
        for (int i = 0; i < list.size(); i ++){
            Map<String, Object> dataMap = list.get(i);
            orgIndexCodes.add(dataMap.get("hikvision_id").toString());
        }

        JsonParser jsonParser = new JsonParser();
        ApiUtil apiUtil = new ApiUtil();
        String apiPath = "/api/resource/v2/person/advance/personList";

        Set<String> personIds = new HashSet<>();
        for (String orgIndexCode : orgIndexCodes) {
            JsonObject jsonObject = new JsonObject();
            jsonObject.addProperty("pageNo", 1);
            jsonObject.addProperty("pageSize", 500);
            jsonObject.addProperty("orgIndexCodes", orgIndexCode);

            String result = apiUtil.doPost(apiPath, jsonObject.toString(), null, null);

            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            JsonArray array = resultJson.get("data").getAsJsonObject().get("list").getAsJsonArray();
            for (JsonElement jsonElement : array) {
                JsonObject element = jsonElement.getAsJsonObject();
                String personId = element.get("personId").getAsString();
                personIds.add(personId);
            }
        }

        for (String personId : personIds) {
            String insertsql = "INSERT INTO hikvision_data(create_date,TABLE_NAME,source_id,hikvision_id) " +
                    " SELECT NOW(),'base_student','" + personId + "','" + personId + "'" +
                    " where not EXISTS (" +
                    " SELECT 1 FROM hikvision_data " +
                    " WHERE source_id = '" + personId + "'" +
                    " );";
            SqlRunnerAdapter.db().insert(insertsql);
        }
    }


    @Test
    void selecAllStudent() throws SQLException {
        ApiUtil apiUtil = new ApiUtil();
        String apiPath = "/api/resource/v2/person/orgIndexCode/personList";
        String sql = "SELECT CONCAT(t1.id, '-', REPLACE(t2.title,'年',''), '-', t1.class_type,'-FB3001') as id FROM base_class t1" +
                " INNER JOIN base_grade t2 ON t1.grade_id = t2.id" +
                " WHERE t1.delete_mark = 0 AND t1.is_graduate = 1 and t2.id = 1868532405673439232";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            JsonObject jsonObject = new JsonObject();
            jsonObject.addProperty("pageNo", 1);
            jsonObject.addProperty("pageSize", 500);
            jsonObject.addProperty("orgIndexCode", objectMap.get("id").toString());

            String result = apiUtil.doPost(apiPath, jsonObject.toString(), null, null);

            JsonParser jsonParser = new JsonParser();
            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            JsonArray array = resultJson.get("data").getAsJsonObject().get("list").getAsJsonArray();
            Map<Long, String> idMap = new HashMap<>();
            for (JsonElement jsonElement : array) {
                JsonObject element = jsonElement.getAsJsonObject();
                if(!element.has("phoneNo") || element.get("phoneNo").isJsonNull()){
                    System.out.println(element.toString());
                    continue;
                }
                String personId = element.get("personId").getAsString();
                String insSql = "INSERT INTO hikvision_data(create_date,TABLE_NAME,source_id,hikvision_id) " +
                        "SELECT NOW(),'base_student', '" + personId + "','" + personId + "' WHERE NOT EXISTS (SELECT 1 FROM hikvision_data WHERE hikvision_id = '" + personId + "')";
                SqlRunnerAdapter.db().insert(insSql);
            }
        }

        sql = "SELECT CONCAT(t1.id, '-', REPLACE(t2.title,'年',''), '-', t1.class_type,'-FB3003') as id FROM base_class t1" +
                " INNER JOIN base_grade t2 ON t1.grade_id = t2.id" +
                " WHERE t1.delete_mark = 0 AND t1.is_graduate = 1 and t2.id = 1868532405673439232";
        list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            JsonObject jsonObject = new JsonObject();
            jsonObject.addProperty("pageNo", 1);
            jsonObject.addProperty("pageSize", 500);
            jsonObject.addProperty("orgIndexCode", objectMap.get("id").toString());

            String result = apiUtil.doPost(apiPath, jsonObject.toString(), null, null);

            JsonParser jsonParser = new JsonParser();
            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            JsonArray array = resultJson.get("data").getAsJsonObject().get("list").getAsJsonArray();
            Map<Long, String> idMap = new HashMap<>();
            for (JsonElement jsonElement : array) {
                JsonObject element = jsonElement.getAsJsonObject();
                if(!element.has("phoneNo") || element.get("phoneNo").isJsonNull()){
                    System.out.println(element.toString());
                    continue;
                }
                String personId = element.get("personId").getAsString();
                String insSql = "INSERT INTO hikvision_data(create_date,TABLE_NAME,source_id,hikvision_id) " +
                        "SELECT NOW(),'base_student', '" + personId + "','" + personId + "' WHERE NOT EXISTS (SELECT 1 FROM teacher_out_in_record WHERE hikvision_id = '" + personId + "')";
                SqlRunnerAdapter.db().insert(insSql);
            }
        }

    }

}