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

package com.xjrsoft.module.job;

import cn.hutool.db.Db;
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.constant.GlobalConstant;
import com.xjrsoft.common.mybatis.SqlRunnerAdapter;
import com.xjrsoft.common.utils.DatasourceUtil;
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.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.schedule.entity.JianyueData;
import com.xjrsoft.module.teacher.entity.XjrUser;
import com.xjrsoft.module.teacher.mapper.FaceImportMapper;
import com.yomahub.liteflow.util.JsonUtil;
import lombok.var;
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 javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
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();

    @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 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(use, tableName, baseClass);


        tableName = "base_student";
        Map<String, String> baseStudent = dataMap.get(tableName);
        Map<String, String> student = insertStudentOne(tableName, baseStudent);

//        selectOrg(use, "base_class");

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

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

    @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) 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 t1.name IN ('罗一钦','刘嘉靖')";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, String[].class);
        Map<Integer, String> clientMap = new HashMap<>();

        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");

        for(int i = 0; i < list.size(); i ++){
            Map<String, Object> objectMap = list.get(i);
            String apiPath = "/api/resource/v2/person/single/add";

            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("clientId", i);
            paramJson.addProperty("personId", objectMap.get("id").toString());
            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;
            switch (objectMap.get("gender").toString()) {
                case "SB10001":
                    gender = 1;
                    break;
                case "SB10002":
                    gender = 2;
                    break;
                default:
                    gender = 0;
                    break;
            }
            paramJson.addProperty("gender", gender);

            if(tableData != null && tableData.containsKey(objectMap.get("id").toString())){
                apiPath = "/api/resource/v1/person/single/update";
                apiUtil.doPost(apiPath, paramJson.toString(), null, header);
                continue;
            }

            clientMap.put(i, objectMap.get("id").toString());
            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())){
                JsonObject array = resultJson.get("data").getAsJsonObject();
                idMap.put(objectMap.get("id").toString(), array.get("personId").getAsString());
            }
        }
        //插入记录表
        return idMap;
    }



    void deleteClass(Db db) throws SQLException {

        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);

        String sql = "SELECT * FROM hikvision_data WHERE table_name = 'base_class' order by length(hikvision_id) desc";
        List<HikvisionData> dataList = db.query(sql, HikvisionData.class);
        apiPath = "/api/resource/v1/org/batch/delete";
        Map<Long, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        JsonArray dataArray = new JsonArray();
        for (HikvisionData department : dataList) {
            dataArray.add(department.getHikvisionId());
        }

        JsonObject paramJson = new JsonObject();
        paramJson.add("indexCodes", dataArray);
        Map<String, String> headerMap = new HashMap<>();
        headerMap.put("tagId", "deleteOrg");
        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 * FROM xjr_user WHERE id IN (" +
                " SELECT source_id FROM hikvision_data WHERE table_name IN( 'base_teacher','base_student')" +
                " AND hikvision_id LIKE '%-%'" +
                " )";
        List<Map<String, Object>> maps = SqlRunnerAdapter.db().selectList(sql);
        ApiUtil apiUtil = new ApiUtil();
        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("pageNo", 1);
        jsonObject.addProperty("pageSize", 500);
        jsonObject.addProperty("personName", "彭兴玲");
        String result = apiUtil.doPost(apiPath, jsonObject.toString(), null, null);
        System.out.println(result);


        for (Map<String, Object> map : maps) {
            String name = map.get("name").toString();


        }

    }

    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);
    }


    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);
    }

}