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.utils.DatasourceUtil;
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.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 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;

    @Test
    void test() throws Exception {
        DataSource datasource = DatasourceUtil.getDataSource(GlobalConstant.DEFAULT_DATASOURCE_KEY);
        Db use = Db.use(datasource);
        String sql = "SELECT distinct table_name FROM hikvision_data WHERE 1 = 1";
        List<JianyueData> query = use.query(sql, JianyueData.class);
        Set<String> tables = new HashSet<>();
        for (JianyueData jianyueData : query) {
            tables.add(jianyueData.getTableName());
        }
        sql = "SELECT * FROM hikvision_data WHERE 0 = 0";
        List<HikvisionData> list = use.query(sql, HikvisionData.class);
        Map<String, Map<String, String>> dataMap = new HashMap<>();
        for (String table : tables) {
            Map<String, String> tableData = new HashMap<>();
            for (HikvisionData hikvisiondata : list) {
                if(!table.equals(hikvisiondata.getTableName())){
                    continue;
                }
                tableData.put(hikvisiondata.getSourceId(), hikvisiondata.getHikvisionId());
            }
            dataMap.put(table, tableData);
        }
        DataUtil dataUtil = new DataUtil();

//        deletePerson();
        String tableName = "xjr_department";
        Map<String, String> department = dataUtil.insertDepartment(use, tableName, dataMap.get(tableName));
        if(department.isEmpty() && dataMap.get(tableName) != null){
            department = dataMap.get(tableName);
        }
//
        //推送教职工
        tableName = "base_teacher";
        dataUtil.insertTeacher(use, 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 = dataUtil.insertStudentOne(use, tableName, baseStudent);

//        selectOrg(use, "base_class");

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


//        selecAllPersonById(use);
        selecAllPersonById(use);
    }

    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(Db db, String tableName, Map<String, String> tableData) throws Exception {
        String sql = "SELECT t1.id,t1.name,CONCAT(t3.id,'-',t4.name,'-',t3.class_type,'-',t2.stduy_status)," +
                " t1.user_name,t1.gender,DATE_FORMAT(t1.birth_date, '%Y-%m-%d'),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.id NOT IN (SELECT source_id FROM hikvision_data WHERE table_name = 'base_student'\n" +
                ") AND t4.name NOT IN ('2021级','2020级')";
        List<String[]> list = db.query(sql, String[].class);
        Map<Integer, String> clientMap = new HashMap<>();

        String apiPath = "/api/resource/v2/person/single/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_student");

        for(int i = 0; i < list.size(); i ++){
            String[] el = list.get(i);
            if(tableData != null && tableData.containsKey(el[0])){
                continue;
            }
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("clientId", i);
            paramJson.addProperty("personId", el[0]);
            paramJson.addProperty("personName", el[1]);
            paramJson.addProperty("orgIndexCode", el[2]);
            paramJson.addProperty("phoneNo", el[6]);
            paramJson.addProperty("jobNo", el[3]);
            paramJson.addProperty("birthday", el[5]);
            paramJson.addProperty("personType", 2);

            int gender;
            switch (el[4]) {
                case "SB10001":
                    gender = 1;
                    break;
                case "SB10002":
                    gender = 2;
                    break;
                default:
                    gender = 0;
                    break;
            }
            paramJson.addProperty("gender", gender);

            clientMap.put(i, el[0]);
            System.out.println("请求参数：" + paramJson.toString());
            String result = apiUtil.doPost(apiPath, paramJson.toString(), null, header);
            System.out.println("返回结果：" + result);
            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(el[0], array.get("personId").getAsString());
            }
        }
        insertRecord(db, tableName, idMap);
        //插入记录表
        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);
    }




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


        Map<Long, String> map = new HashMap<>();
        map.put(14954817043910L, "肖会春");map.put(14954817552454L, "苏平");map.put(14954818123205L, "刘瑞田");
        map.put(14954818337094L, "王开兰");map.put(14954818837189L, "龚仁容");map.put(14954819407814L, "张洪梅");
        map.put(14954819516102L, "袁夕芳");map.put(14954819746757L, "冯加林");map.put(14954820166213L, "张凤琴");
        map.put(14954820362949L, "周家慧");map.put(14954820552390L, "龙运刚");map.put(14954820751429L, "张怀会");
        map.put(14954825048006L, "李孝秋");map.put(14954802607430L, "王强");
        ApiUtil apiUtil = new ApiUtil();
        JsonParser parser = new JsonParser();
        Map<Long, String> idMap = new HashMap<>();
        for (Long id : map.keySet()) {
            JsonObject jsonObject = new JsonObject();
            jsonObject.addProperty("pageNo", 1);
            jsonObject.addProperty("pageSize", 500);
            jsonObject.addProperty("personName", map.get(id));
            String result = apiUtil.doPost(apiPath, jsonObject.toString(), null, null);
            JsonObject resultJson = parser.parse(result).getAsJsonObject();
            JsonObject info = resultJson.get("data").getAsJsonObject().get("list").getAsJsonArray().get(0).getAsJsonObject();
            String personId = info.get("personId").getAsString();
            idMap.put(id, personId);
        }
        insertRecord(db, "base_teacher",idMap);

    }

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

    void deletePerson(){
        String apiPath = "/api/resource/v1/person/batch/delete";
        Map<Long, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        ApiUtil apiUtil = new ApiUtil();
        JsonArray dataArray = new JsonArray();
        dataArray.add("14954805813957");dataArray.add("14954812339909");dataArray.add("14954808977606");
        dataArray.add("14954812285254");dataArray.add("14954812392774");dataArray.add("14954812448325");
        dataArray.add("14954804711366");dataArray.add("15331003650245");dataArray.add("14954802607430");
        dataArray.add("14954807016389");dataArray.add("14954803353286");dataArray.add("14954806910277");
        dataArray.add("14954807016389");

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

}