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

package com.xjrsoft.module.job;

import cn.hutool.db.Entity;
import cn.hutool.extra.spring.SpringUtil;
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.module.base.entity.BaseClass;
import com.xjrsoft.module.base.service.IBaseClassService;
import com.xjrsoft.module.schedule.entity.CourseReceiveMsg;
import com.xjrsoft.module.schedule.util.DataUtil;
import com.xjrsoft.module.schedule.util.ScheduleUtil;
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.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @author dzx
 * @date 2024/8/7
 */

@RunWith(SpringRunner.class)
@SpringBootTest(classes = XjrSoftApplication.class)
class JianyuekbScheduleTaskTest {
    @Autowired
    private IBaseClassService classService;
    @Test
    public void RefreshConnectionPool() throws Exception {
        String active = SpringUtil.getActiveProfile();
        String sql = "SELECT * FROM course_receive_msg WHERE delete_mark = 0 AND is_callback = -1";
        List<Map<String, Object>> receiveMsgs = SqlRunnerAdapter.db().selectList(sql, CourseReceiveMsg.class);
        if(receiveMsgs.isEmpty()){
            return;
        }
        JsonArray allScheduleInfo = new JsonArray();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        DataUtil dataUtil = new DataUtil();

        List<String> eduYearSerialNo1 = receiveMsgs.stream().map(x -> "'" + x.get("edu_year_serial_no").toString() + "'").collect(Collectors.toList());

        //查询出传入排课系统的年级和班级
        sql = "SELECT * FROM jianyue_data WHERE source_id IS NOT NULL AND jianyue_id != ''" +
                " and table_name = 'base_grade'" +
                " and jianyue_id in (" + eduYearSerialNo1.toString().replace("[","").replace("]","") + ")";
        List<Map<String, Object>> jianyueData = SqlRunnerAdapter.db().selectList(sql);
        Map<String, List<Long>> gradeClassMaps = new HashMap<>();//存入对应年级的所有班级id
        for (Map<String, Object> el : jianyueData) {
            String gradeId = el.get("source_id").toString().split("_")[1];
            List<BaseClass> classList = classService.list(new QueryWrapper<BaseClass>().lambda().eq(BaseClass::getGradeId, gradeId));
            List<Long> classIds = classList.stream().map(BaseClass::getId).collect(Collectors.toList());

            gradeClassMaps.put(el.get("jianyue_id").toString(), classIds);
        }


        for (Map<String, Object> receiveMsg : receiveMsgs) {
            String updSql = "update course_receive_msg set is_callback = 0 where id = " + receiveMsg.get("id").toString();
            SqlRunnerAdapter.db().update(updSql);

            String eduYearSerialNo = receiveMsg.get("edu_year_serial_no").toString();
            String startDateStr = receiveMsg.get("start_date").toString();
            String endDateStr = receiveMsg.get("end_date").toString();
            //删除课表信息
            String delSql = "delete from course_table where schedule_date between '" + startDateStr + "'" +
                    " and '" + endDateStr + "'" +
                    " and class_id in (" + gradeClassMaps.get(eduYearSerialNo).toString().replace("[","").replace("]","")+ ")";
            SqlRunnerAdapter.db().delete(delSql);

            LocalDate startDateObj = LocalDate.parse(startDateStr);
            LocalDate endDateObj = LocalDate.parse(endDateStr);
            long between = ChronoUnit.DAYS.between(startDateObj, endDateObj);
            int times = Integer.parseInt(((between / 7) + 1) + "");

            for (int index = 0; index < times; index ++) {
                LocalDate statrTime = startDateObj.plusDays(index * 7L);
                String startDate = statrTime.format(formatter);
                LocalDate endTime = statrTime.plusDays(6L);
                if(endTime.isAfter(endDateObj)){
                    endTime = endDateObj;
                }

                String endDate = endTime.format(formatter);
                //获取课表并存到数据库
                JsonArray scheduleInfo = dataUtil.getScheduleInfoByGrade(eduYearSerialNo, startDate, endDate);
                allScheduleInfo.addAll(scheduleInfo);
            }

            updSql = "update course_receive_msg set is_callback = 1 where id = " + receiveMsg.get("id").toString();
            SqlRunnerAdapter.db().update(updSql);
        }
        doExecute(allScheduleInfo);
    }

    void doExecute(JsonArray scheduleInfo){
        String sql = "SELECT distinct table_name FROM jianyue_data WHERE 1 = 1";
        List<Map<String, Object>> query = SqlRunnerAdapter.db().selectList(sql);
        Set<String> tables = new HashSet<>();
        for (Map<String, Object> jianyueData : query) {
            tables.add(jianyueData.get("table_name").toString());
        }
        sql = "SELECT * FROM jianyue_data WHERE 0 = 0";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        Map<String, Map<String, String>> dataMap = new HashMap<>();
        for (String table : tables) {
            Map<String, String> tableData = new HashMap<>();
            for (Map<String, Object> jianyueData : list) {
                if(!table.equals(jianyueData.get("table_name").toString())){
                    continue;
                }
                tableData.put(jianyueData.get("jianyue_id").toString(), jianyueData.get("source_id").toString());
            }
            dataMap.put(table, tableData);
        }
        DataUtil dataUtil = new DataUtil();
        //获取年级
        String tableName = "base_grade";
//            Map<String, Long> gradeMap = dataMap.get(tableName);
        //获取学期
        tableName = "base_semester";
        Map<String, String> semesterMap = dataMap.get(tableName);
        //获取课程
        tableName = "base_course_subject";
        Map<String, String> courseMap = dataMap.get(tableName);
        //获取教职工
        tableName = "base_teacher";
        Map<String, String> teacherMap = dataMap.get(tableName);
        //获取行政班
        tableName = "base_class";
        Map<String, String> classMap = dataMap.get(tableName);

        tableName = "base_classroom";
        Map<String, String> classroomMap = dataMap.get(tableName);

        dataUtil.insertCourseTableEntiy(scheduleInfo, classroomMap, courseMap, semesterMap, teacherMap, classMap);
        dataUtil.insertClassTime(scheduleInfo);
    }


    @Test
    void updateClass() throws Exception {
        String url  = ScheduleUtil.apiUrl + "Class/page";
        JsonObject pageJson = new JsonObject();
        pageJson.addProperty("pageSize", 200);
        pageJson.addProperty("pageIndex", 1);
        JsonParser parser = new JsonParser();

        long timestamp = System.currentTimeMillis();
        String sign = ScheduleUtil.createSign(timestamp);

        String doPost = ScheduleUtil.doPost(url, pageJson.toString(), sign, timestamp);
        System.out.println(doPost);
        JsonArray dataList = parser.parse(doPost).getAsJsonObject().get("data").getAsJsonObject().get("dataList").getAsJsonArray();
        String sql = "select * from base_class where delete_mark = 0";
        List<Map<String, Object>> classList = SqlRunnerAdapter.db().selectList(sql);
        Map<String, Long> classMap = new HashMap<>();
        for (Map<String, Object> objectMap : classList) {
            classMap.put(objectMap.get("name").toString(), (Long)objectMap.get("id"));
        }

        sql = "select * from jianyue_data where table_name = 'base_class'";
        List<Map<String, Object>> classList2 = SqlRunnerAdapter.db().selectList(sql);
        Set<String> sourceIds = new HashSet<>();
        for (Map<String, Object> objectMap : classList2) {
            sourceIds.add(objectMap.get("jianyue_id").toString());
        }

        String tableName = "jianyue_data";
        List<Entity> insertList = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (JsonElement jsonElement : dataList) {
            JsonObject object = jsonElement.getAsJsonObject();
            String serialNo = object.get("serialNo").getAsString();
            if(sourceIds.contains(serialNo)){
                continue;
            }
            if(classMap.get(object.get("name").getAsString()) == null){
                continue;
            }
            Entity entity = Entity.create(tableName);
            entity.set("create_date", sdf.format(new Date()));
            entity.set("table_name", "base_class");
            entity.set("source_id", classMap.get(object.get("name").getAsString()));
            entity.set("jianyue_id", serialNo);
            insertList.add(entity);
        }
        SqlRunnerAdapter.db().dynamicInsertBatch(tableName, insertList);

    }


    @Test
    void updateClass2() throws Exception {
        String url  = ScheduleUtil.apiUrl + "Class/page";
        JsonObject pageJson = new JsonObject();
        pageJson.addProperty("pageSize", 200);
        pageJson.addProperty("pageIndex", 1);
        JsonParser parser = new JsonParser();

        long timestamp = System.currentTimeMillis();
        String sign = ScheduleUtil.createSign(timestamp);

        String doPost = ScheduleUtil.doPost(url, pageJson.toString(), sign, timestamp);
        System.out.println(doPost);
        JsonArray dataList = parser.parse(doPost).getAsJsonObject().get("data").getAsJsonObject().get("dataList").getAsJsonArray();
        String sql = "select * from base_class where delete_mark = 0";
        List<Map<String, Object>> classList = SqlRunnerAdapter.db().selectList(sql);
        Map<String, Long> classMap = new HashMap<>();
        for (Map<String, Object> objectMap : classList) {
            classMap.put(objectMap.get("name").toString(), (Long)objectMap.get("id"));
        }

        sql = "select * from jianyue_data where table_name = 'base_class'";
        List<Map<String, Object>> classList2 = SqlRunnerAdapter.db().selectList(sql);
        Set<String> sourceIds = new HashSet<>();
        for (Map<String, Object> objectMap : classList2) {
            sourceIds.add(objectMap.get("jianyue_id").toString());
        }

        String tableName = "jianyue_data";
        List<Entity> insertList = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (JsonElement jsonElement : dataList) {
            JsonObject object = jsonElement.getAsJsonObject();
            String serialNo = object.get("serialNo").getAsString();
            if(sourceIds.contains(serialNo)){
                continue;
            }
            if(classMap.get(object.get("name").getAsString()) == null){
                continue;
            }
            Entity entity = Entity.create(tableName);
            entity.set("create_date", sdf.format(new Date()));
            entity.set("table_name", "base_class");
            entity.set("source_id", classMap.get(object.get("name").getAsString()));
            entity.set("jianyue_id", serialNo);
            insertList.add(entity);
        }
        SqlRunnerAdapter.db().dynamicInsertBatch(tableName, insertList);

    }

    @Test
    void test2(){
        String sql = "SELECT schedule_date,weeks,time_period,time_number,teacher_id,class_id,COUNT(*) FROM course_table WHERE 1 = 1" +
                " GROUP BY schedule_date,weeks,time_period,time_number,teacher_id,class_id HAVING COUNT(*) > 1";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            sql = "SELECT * FROM course_table WHERE schedule_date = '" + objectMap.get("schedule_date").toString() +"'" +
                    " AND weeks = " + objectMap.get("weeks").toString() +
                    " AND time_period = " + objectMap.get("time_period").toString() +
                    " AND time_number = " + objectMap.get("time_number").toString() +
                    " AND teacher_id = " + objectMap.get("teacher_id").toString() +
                    " AND class_id = " + objectMap.get("class_id").toString();
            List<Map<String, Object>> list2 = SqlRunnerAdapter.db().selectList(sql);

            String delSql = "delete from course_table where id = " + list2.get(0).get("id").toString();
            SqlRunnerAdapter.db().delete(delSql);
        }
    }

    @Test
    void test3(){
        String sql = "SELECT * FROM wf_course_adjust t1" +
                " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
                " WHERE adjust_type = 'course_substitute' AND t1.delete_mark = 0 AND t2.current_state = 'COMPLETED'";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            List<String> split = Arrays.asList(objectMap.get("course_id").toString().split(","));
            sql = "update course_table set teacher_id = '" + objectMap.get("exchange_teacher_id").toString() + "' WHERE id in (" + split.toString().replace("[", "").replace("]", "") + ")";

            SqlRunnerAdapter.db().update(sql);
        }
    }

    @Test
    void test4(){
        String sql = "SELECT t1.* FROM wf_course_adjust t1" +
                " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
                " LEFT JOIN course_table_bak t3 ON t1.id = t3.wf_course_adjust_id" +
                " WHERE t1.adjust_type = 'course_substitute' " +
                " AND t2.current_state = 'COMPLETED'" +
                " AND t1.delete_mark = 0 " +
                " AND t3.wf_course_adjust_id IS NULL";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        String tableName = "course_table_bak";
        for (Map<String, Object> objectMap : list) {
            List<String> split = Arrays.asList(objectMap.get("course_id").toString().split(","));
            String insertSql = "INSERT INTO course_table_bak(id,base_semester_id,teacher_id,teacher_name,course_id,course_name,class_id,class_name,WEEK,weeks," +
                    " weeks_cn,time_period,time_number,site_id,site_name,STATUS,err_msg,create_date,modify_date,jianyue_id,source_data,schedule_date,teacher_serial_no," +
                    " wf_course_adjust_id,adjust_type)" +
                    " SELECT id,base_semester_id,teacher_id,teacher_name,course_id,course_name,class_id,class_name,WEEK,weeks," +
                    " weeks_cn,time_period,time_number,site_id,site_name,STATUS,err_msg,create_date,modify_date,jianyue_id,source_data,schedule_date,teacher_serial_no," +
                    " " + objectMap.get("id").toString() + ",'course_substitute' FROM course_table " +
                    " WHERE id IN (" + split.toString().replace("[", "").replace("]", "") + ")";
            SqlRunnerAdapter.db().insert(insertSql);
        }
    }


}