api/src/main/java/com/xjrsoft/module/schedule/util/DataUtil.java

package com.xjrsoft.module.schedule.util;

import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.xjrsoft.common.mybatis.SqlRunnerAdapter;
import com.xjrsoft.common.utils.SqlRunnerAdapterUtil;
import com.xjrsoft.module.base.entity.BaseClass;
import com.xjrsoft.module.base.entity.BaseCourseSubject;
import com.xjrsoft.module.base.entity.BaseGrade;
import com.xjrsoft.module.base.entity.BaseLabel;
import com.xjrsoft.module.base.entity.BaseSemester;
import com.xjrsoft.module.organization.entity.Department;
import com.xjrsoft.module.schedule.vo.ClassroomJianyuekbVo;
import com.xjrsoft.module.schedule.vo.StudentJianyuekbVo;
import com.xjrsoft.module.teacher.entity.XjrUser;
import lombok.extern.slf4j.Slf4j;

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/1/23
 */
@Slf4j
public class DataUtil {

    /**
     * 推送校区信息
     * 此处将班级所属的部门当做校区进行推送
     */
    public Map<String, String> insertSchoolDistrict(String tableName, Map<String, String> ids) throws Exception {
        String sql = "SELECT * FROM " + tableName + " WHERE id IN (" +
                "SELECT DISTINCT org_id FROM base_class WHERE delete_mark = 0 " +
                ")";
        List<Map<String, Object>> dataList = SqlRunnerAdapter.db().selectList(sql, Department.class);

        Map<String, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        for (Map<String, Object> departmentMap : dataList) {
            Department department = SqlRunnerAdapterUtil.convertMapToEntity(departmentMap, Department.class);
            String url  = ScheduleUtil.apiUrl + "SchoolDistrict/Create";
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("name", department.getName());

            paramJson.addProperty("not", department.getRemark());
            paramJson.addProperty("sort", department.getSortCode());
            paramJson.addProperty("shortName", department.getName());
            paramJson.addProperty("extendId", department.getId());
            long timestamp = System.currentTimeMillis();
            String sign = ScheduleUtil.createSign(timestamp);
            ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
            if(ids != null && ids.get(department.getId().toString()) != null){
                url  = ScheduleUtil.apiUrl + "SchoolDistrict/Update";
                paramJson.addProperty("id", ids.get(department.getId().toString()));
                ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
                continue;
            }
            String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
            if(result == null){
                continue;
            }
            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            idMap.put(department.getId().toString(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(tableName, idMap);
        return idMap;
    }

    public Map<String, String> insertClass(String tableName, Map<String, String> gradeMap,
    Map<String, String> teacherMap, String semesterSerialNo, Map<String, String> ids, Map<String, String> classroomMap) throws Exception {
        String sql = "SELECT * FROM " + tableName + " WHERE delete_mark = 0 and is_graduate = 1";
        List<Map<String, Object>> dataList = SqlRunnerAdapter.db().selectList(sql, BaseClass.class);

        Map<String, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        for (Map<String, Object> baseClassMap : dataList) {
            BaseClass baseClass = SqlRunnerAdapterUtil.convertMapToEntity(baseClassMap, BaseClass.class);
            String url  = ScheduleUtil.apiUrl + "class/create";
            JsonObject paramJson = new JsonObject();
            if(baseClass.getTeacherId() != null && teacherMap.get(baseClass.getTeacherId().toString()) != null){
                paramJson.addProperty("teacherSerialNo", teacherMap.get(baseClass.getTeacherId().toString()));
            }
            paramJson.addProperty("name", baseClass.getName());

            paramJson.addProperty("semesterSerialNo", semesterSerialNo);
            paramJson.addProperty("eduYearSerialNo", gradeMap.get(baseClass.getOrgId() + "_" + baseClass.getGradeId().toString()));
            paramJson.addProperty("extendId", baseClass.getId());
            if(baseClass.getClassroomId() != null && classroomMap.get(baseClass.getClassroomId().toString()) != null){
                paramJson.addProperty("classRoomSerialNo", classroomMap.get(baseClass.getClassroomId().toString()));
            }
            if(ids != null && ids.get(baseClass.getId().toString()) != null){
                url  = ScheduleUtil.apiUrl + "class/update";
                paramJson.addProperty("serialNo", ids.get(baseClass.getId().toString()));
                long timestamp = System.currentTimeMillis();
                //生成签名
                String sign = ScheduleUtil.createSign(timestamp);
                ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
                continue;
            }

            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);
            String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
            if(result == null){
                continue;
            }
            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            idMap.put(baseClass.getId().toString(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(tableName, idMap);
        return idMap;
    }

    public Map<String, String> insertClassRoom(String tableName, String schoolDistrictId, Map<String, String> ids) throws Exception {
        String sql = "SELECT t1.name, t1.capacity AS people_count,t2.name AS location, t1.floor_num AS floor, t1.id AS extendId FROM " + tableName + " t1" +
                " INNER JOIN base_office_build t2 ON t1.office_build_id = t2.id" +
                " WHERE t1.delete_mark = 0 AND t2.delete_mark = 0";
        List<Map<String, Object>> dataList = SqlRunnerAdapter.db().selectList(sql, ClassroomJianyuekbVo.class);

        JsonParser jsonParser = new JsonParser();
        Map<String, String> idMap = new HashMap<>();
        for (Map<String, Object> classroomMap : dataList) {
            ClassroomJianyuekbVo classroom = SqlRunnerAdapterUtil.convertMapToEntity(classroomMap, ClassroomJianyuekbVo.class);
            String url  = ScheduleUtil.apiUrl + "classroom/create";
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("schoolDistrictId", schoolDistrictId);
            paramJson.addProperty("name", classroom.getName());

            paramJson.addProperty("peopleCount", 50);
            paramJson.addProperty("location", classroom.getLocation());
            paramJson.addProperty("extendId", classroom.getExtendId());
            paramJson.addProperty("floor", classroom.getFloor());

            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);

            if(ids != null && ids.get(classroom.getExtendId().toString()) != null){
                url  = ScheduleUtil.apiUrl + "classroom/update";
                paramJson.addProperty("serialNo", ids.get(classroom.getExtendId().toString()));
                ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
                continue;
            }

            String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
            if(result == null){
                continue;
            }
            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            idMap.put(classroom.getExtendId().toString(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(tableName, idMap);
        return idMap;
    }

    public Map<String, String> insertStudent(String tableName, Map<String, String> gradeMap, Map<String, String> ids) throws Exception {
        String sql = "SELECT t1.id AS registerNo, t2.name AS realName,t3.grade_id AS gradeSerialNo," +
                " t2.name AS alias,t2.gender,t2.id AS extendId FROM " + tableName + " t1" +
                " INNER JOIN xjr_user t2 ON t1.user_id = t2.id" +
                " INNER JOIN base_student_school_roll t3 ON t1.user_id = t3.user_id" +
                " WHERE t1.delete_mark = 0 AND t2.delete_mark = 0" +
                " AND t1.student_id IS NOT NULL AND t1.student_id != ''" +
                " AND t3.archives_status = 'FB2901'";
        List<Map<String, Object>> dataList = SqlRunnerAdapter.db().selectList(sql, StudentJianyuekbVo.class);

        JsonParser jsonParser = new JsonParser();
        Map<String, String> idMap = new HashMap<>();
        for (Map<String, Object> studentMap : dataList) {
            StudentJianyuekbVo student = SqlRunnerAdapterUtil.convertMapToEntity(studentMap, StudentJianyuekbVo.class);
            String url  = ScheduleUtil.apiUrl + "student/create";
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("registerNo", student.getRegisterNo());
            if("SB10001".equals(student.getGender())){
                paramJson.addProperty("gender", 1);
            }else if("SB10002".equals(student.getGender())){
                paramJson.addProperty("gender", 2);
            }

            paramJson.addProperty("realName", student.getRealName());
            paramJson.addProperty("alias", student.getAlias());
            paramJson.addProperty("extendId", student.getExtendId());
            paramJson.addProperty("gradeSerialNo", gradeMap.get(student.getGradeSerialNo().toString()));
            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);

            if(ids != null && ids.get(student.getExtendId().toString()) != null){
                paramJson.addProperty("serialNo", ids.get(student.getExtendId().toString()));
                url  = ScheduleUtil.apiUrl + "student/update";
                ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
                continue;
            }

            String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
            if(result == null){
                continue;
            }
            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            idMap.put(student.getExtendId().toString(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(tableName, idMap);
        return idMap;
    }
    /**
     * 新增教职工
     */
    public Map<String, String> insertTeacher(String tableName, Map<String, String> ids) throws Exception {
        String sql = "select t1.* from xjr_user t1" +
                " inner join xjr_user_role_relation t2 on t1.id = t2.user_id" +
                " inner join " + tableName + " t3 on t1.id = t3.user_id" +
                " where t1.delete_mark = 0 and t2.role_id = 2";
        List<Map<String, Object>> xjrUsers = SqlRunnerAdapter.db().selectList(sql, XjrUser.class);
        JsonParser jsonParser = new JsonParser();

        Map<String, String> idMap = new HashMap<>();
        for (Map<String, Object> user : xjrUsers) {
            String url  = ScheduleUtil.apiUrl + "teacher/create";
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("jobNumber", user.get("user_name").toString());
            if("SB10001".equals(user.get("gender").toString())){
                paramJson.addProperty("gender", 1);
            }else if("SB10002".equals(user.get("gender").toString())){
                paramJson.addProperty("gender", 2);
            }

            paramJson.addProperty("name", user.get("name").toString());
            paramJson.addProperty("alias", user.get("nick_name").toString());
            paramJson.addProperty("extendId", user.get("id").toString());
            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);
            if(ids != null && ids.get(user.get("id").toString()) != null){
                url  = ScheduleUtil.apiUrl + "teacher/update";
                paramJson.addProperty("serialNo", ids.get(user.get("id").toString()));
                ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
                continue;
            }

            String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
            if(result == null){
                continue;
            }
            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            idMap.put(user.get("id").toString(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(tableName, idMap);
        return idMap;
    }
    /**
     *  新增课程
     */
    public Map<String, String> insertCourse(String tableName, Map<String, String> ids, Map<String, String> tagMap) throws Exception {
        String sql = "select * from " + tableName + " where delete_mark = 0";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, BaseCourseSubject.class);

        Map<String, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        for (Map<String, Object> courseSubjectMap : list) {
            BaseCourseSubject courseSubject = SqlRunnerAdapterUtil.convertMapToEntity(courseSubjectMap, BaseCourseSubject.class);
            String url  = ScheduleUtil.apiUrl + "courseclass/Create";

            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("name", courseSubject.getName());
            paramJson.addProperty("code", courseSubject.getCode());
            paramJson.addProperty("extendId", courseSubject.getId().toString());
            if(courseSubject.getBaseLabelId() != null && tagMap.get(courseSubject.getBaseLabelId().toString()) != null){
                JsonArray jsonArray = new JsonArray();
                jsonArray.add(tagMap.get(courseSubject.getBaseLabelId().toString()));
                paramJson.add("tagSerialNos", jsonArray);
            }

            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);

            if(ids != null && ids.get(courseSubject.getId().toString()) != null){
                url  = ScheduleUtil.apiUrl + "courseclass/update";
                paramJson.addProperty("id", ids.get(courseSubject.getId().toString()));
                ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
                continue;
            }

            String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
            if(result == null){
                continue;
            }
            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            idMap.put(courseSubject.getId().toString(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(tableName, idMap);

        return idMap;
    }

    /**
     * 新增学期
     */
    public Map<String, String> insertSemester(String tableName, Map<String, String> ids) throws Exception {
        String sql = "select * from " + tableName + " where delete_mark = 0";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, BaseSemester.class);

        Map<String, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        SimpleDateFormat sdfYear = new SimpleDateFormat("yyyy");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String spring = "春";
        String autumn = "秋";

        for (Map<String, Object> semesterMap : list) {
            BaseSemester semester = SqlRunnerAdapterUtil.convertMapToEntity(semesterMap, BaseSemester.class);
            String url  = ScheduleUtil.apiUrl + "semester/Create";
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("schoolYear", Integer.parseInt(sdfYear.format(semester.getStartDate())));
            Integer period = null;
            if(semester.getName().contains(spring)){
                period = 1;
            }else if(semester.getName().contains(autumn)){
                period = 2;
            }
            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);
            paramJson.addProperty("period", period);
            paramJson.addProperty("startDate", sdf.format(semester.getStartDate()));
            paramJson.addProperty("endDate", sdf.format(semester.getEndDate()));
            paramJson.addProperty("extendId", semester.getId().toString());

            if(semester.getStartDate() != null && semester.getEndDate() != null && semester.getEndDate().getTime() > timestamp && semester.getStartDate().getTime() < timestamp){
                paramJson.addProperty("isCurrent", Boolean.TRUE);
            }else if(semester.getStartDate() != null && semester.getEndDate() != null && semester.getEndDate().getTime() > timestamp && semester.getStartDate().getTime() > timestamp){
                paramJson.addProperty("isCurrent", Boolean.TRUE);
            }else{
                paramJson.addProperty("isCurrent", Boolean.FALSE);
            }
            if(ids != null && ids.get(semester.getId().toString()) != null){
                url  = ScheduleUtil.apiUrl + "semester/update";
                paramJson.addProperty("serialNo", ids.get(semester.getId().toString()));
                ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
                continue;
            }

            String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);

            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();
            idMap.put(semester.getId().toString(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(tableName, idMap);

        return idMap;
    }

    public Map<String, String> insertCourseTag(String tableName, Map<String, String> ids) throws Exception {
        String sql = "select * from " + tableName + " where delete_mark = 0" +
                " and id in (select distinct base_label_id from base_course_subject where delete_mark = 0)";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, BaseLabel.class);
        String url = ScheduleUtil.apiUrl + "courseclass/tag/create";
        JsonParser jsonParser = new JsonParser();
        Map<String, String> idMap = new HashMap<>();
        for (Map<String, Object> departmentMap : list) {
            BaseLabel department = SqlRunnerAdapterUtil.convertMapToEntity(departmentMap, BaseLabel.class);
            if(ids != null && ids.get(department.getId().toString()) != null){
                continue;
            }
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("name", department.getName());

            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);

            String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);

            JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();

            idMap.put(department.getId().toString(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(tableName, idMap);
        return idMap;
    }

    /**
     * 插入年级
     * @param tableName 表名字
     * @param schoolDistrictMap 校区id
     * @param ids 数据库id和jianyue的对应关系
     * @return 返回数据库和jianyue的对应关系
     */
    public Map<String, String> insertGrade(String tableName, Map<String, String> schoolDistrictMap, Map<String, String> ids) throws Exception {
        String sql = "select * from " + tableName + " where delete_mark = 0 and status = 1";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql, BaseGrade.class);

        JsonParser jsonParser = new JsonParser();
        Map<String, String> idMap = new HashMap<>();
        for (String gradeId : schoolDistrictMap.keySet()) {
            for (Map<String, Object> baseGradeMap : list) {
                BaseGrade baseGrade = SqlRunnerAdapterUtil.convertMapToEntity(baseGradeMap, BaseGrade.class);
                String url = ScheduleUtil.apiUrl + "eduyear/create";
                JsonObject paramJson = new JsonObject();
                paramJson.addProperty("schoolDistrictId", schoolDistrictMap.get(gradeId));
                paramJson.addProperty("period", 4);
                paramJson.addProperty("startYear", baseGrade.getTitle().replaceAll("年", ""));
                paramJson.addProperty("extendId", baseGrade.getId().toString());
                paramJson.addProperty("year", 3);

                //获取时间戳
                long timestamp = System.currentTimeMillis();
                //生成签名
                String sign = ScheduleUtil.createSign(timestamp);

                if(ids != null && ids.get(gradeId + "_" + baseGrade.getId().toString()) != null){
                    paramJson.addProperty("serialNo", ids.get(gradeId + "_" + baseGrade.getId().toString()));
                    url = ScheduleUtil.apiUrl + "eduyear/update";
                    ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
                    continue;
                }

                String result = ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);

                JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();

                idMap.put(gradeId + "_" + baseGrade.getId().toString(), resultJson.get("data").getAsString());
            }
        }

        //插入记录表
        insertRecord(tableName, idMap);
        return idMap;
    }

    /**
     * 查询校区id
     */
    public JsonArray getSchoolDistrictData() throws Exception {
        JsonParser jsonParser = new JsonParser();

        String url  = ScheduleUtil.apiUrl + "SchoolDistrict/page";
        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("pageSize", "15");
        jsonObject.addProperty("pageIndex", "1");

        //获取时间戳
        long timestamp = System.currentTimeMillis();
        //生成签名
        String sign = ScheduleUtil.createSign(timestamp);
        String result = ScheduleUtil.doPost(url, jsonObject.toString(), sign, timestamp);
        JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();

        JsonArray jsonArray = resultJson.get("data").getAsJsonObject().get("dataList").getAsJsonArray();
        JsonArray resultArray = new JsonArray();
        for (JsonElement jsonElement : jsonArray) {
            JsonObject asJsonObject = jsonElement.getAsJsonObject();
            resultArray.add(asJsonObject.get("id").getAsString());
        }
        return resultArray;
    }

    /**
     * 查询当前学期的id
     * @return 返回当前学期的id
     */
    public String getCurrenSemeter() throws Exception {
        JsonParser jsonParser = new JsonParser();

        String url  = ScheduleUtil.apiUrl + "Semester/Page";
        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("pageSize", "15");
        jsonObject.addProperty("pageIndex", "1");

        //获取时间戳
        long timestamp = System.currentTimeMillis();
        //生成签名
        String sign = ScheduleUtil.createSign(timestamp);
        String result = ScheduleUtil.doPost(url, jsonObject.toString(), sign, timestamp);
        JsonObject resultJson = jsonParser.parse(result).getAsJsonObject();

        JsonArray jsonArray = resultJson.get("data").getAsJsonObject().get("dataList").getAsJsonArray();
        String currentSemeterId = null;
        for (JsonElement jsonElement : jsonArray) {
            JsonObject asJsonObject = jsonElement.getAsJsonObject();
            if(asJsonObject.get("isCurrent").getAsBoolean()){
                currentSemeterId = asJsonObject.get("serialNo").getAsString();
            }
        }
        return currentSemeterId;
    }

    public JsonArray getScheduleInfoByClass(Map<String, Long> classMap, Date startDate, Date endDate) throws Exception {
        JsonParser jsonParser = new JsonParser();
        String url  = ScheduleUtil.apiUrl + "TimeTable/List";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        JsonArray dataMap = new JsonArray();
        for (String classId : classMap.keySet()) {
            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);
            JsonObject jsonObject = new JsonObject();
            jsonObject.addProperty("classSerialNo", classId);
            jsonObject.addProperty("startDate", sdf.format(startDate));
            jsonObject.addProperty("endDate", sdf.format(endDate));
            String result = ScheduleUtil.doPost(url, jsonObject.toString(), sign, timestamp);

            JsonObject asJsonObject = jsonParser.parse(result).getAsJsonObject();
            dataMap.addAll(asJsonObject.get("data").getAsJsonArray());
        }
        return dataMap;
    }

    public JsonArray getScheduleInfoByGrade(String eduYearSerialNo, String startDate, String endDate) throws Exception {
        JsonParser jsonParser = new JsonParser();
        String url  = ScheduleUtil.apiUrl + "TimeTable/List";
        //获取时间戳
        long timestamp = System.currentTimeMillis();
        //生成签名
        String sign = ScheduleUtil.createSign(timestamp);
        JsonObject jsonObject = new JsonObject();
        jsonObject.addProperty("eduYearSerialNo", eduYearSerialNo);
        jsonObject.addProperty("startDate", startDate);
        jsonObject.addProperty("endDate", endDate);
        String result = ScheduleUtil.doPost(url, jsonObject.toString(), sign, timestamp);
        JsonObject asJsonObject = jsonParser.parse(result).getAsJsonObject();
        return asJsonObject.get("data").getAsJsonArray();
    }

    /**
     *
     * @param data 接口数据
     * @param classroomMap 教室对应关系
     * @param coureseMap 课程对应关系
     * @param semesterMap 学期对应关系
     * @param teacherMap 教师对应关系
     */
    public void insertCourseTable(JsonArray data, Map<String, String> classroomMap, Map<String, String> coureseMap,
        Map<String, String> semesterMap, Map<String, String> teacherMap, Map<String, String> classMap) {
//        List<String> sqls = new ArrayList<>();
//        Map<Integer, String> week = ScheduleUtil.getWeek();
        Map<Integer, Integer> tmePeriod = ScheduleUtil.getTmePeriod();
        int count = 1;
        for (JsonElement jsonElement : data) {
            JsonObject asJsonObject = jsonElement.getAsJsonObject();
            long id = System.currentTimeMillis();
            JsonArray teachers = asJsonObject.get("teachers").getAsJsonArray();
            if(asJsonObject.get("courseClassId").isJsonNull() || coureseMap.get(asJsonObject.get("courseClassId").getAsString()) == null){
                continue;
            }
            String numberOfDayName = asJsonObject.get("numberOfDayName").getAsString();
            Integer timeNumber = 0;//节次
            if(!StrUtil.isEmpty(numberOfDayName)){
                timeNumber = Integer.parseInt(numberOfDayName.substring(numberOfDayName.length() - 1));
            }
//            String updateSql = "update course_table set status = 3 where class_id = " + classMap.get(asJsonObject.get("classSerialNo").getAsString())
//                    + " and time_period = " + tmePeriod.get(asJsonObject.get("timeOption").getAsInt())
//                    + " and time_number = " + timeNumber
//                    + " and weeks = " + asJsonObject.get("dayOfWeek").getAsInt()
//                    + " and course_id = " + coureseMap.get(asJsonObject.get("courseClassId").getAsString())
//                    + " and schedule_date = '" + asJsonObject.get("scheduleDate").getAsString() + "'";

            String sql = "INSERT INTO course_table(id,base_semester_id,teacher_id,teacher_name,teacher_serial_no,course_id,course_name," +
                    "class_id, class_name,weeks,weeks_cn,time_period,time_number,site_id,site_name,status," +
                    "create_date,schedule_date,jianyue_id) select " + id + String.format("%04d", count) + ","
                    + semesterMap.get(asJsonObject.get("semesterSerialNo").getAsString()) + ",";
            if(teachers.size() > 0){
                JsonObject teacherJson = teachers.get(0).getAsJsonObject();
//                updateSql += " and teacher_id = " + teacherMap.get(teacherJson.get("teacherSerialNo").getAsString());
                sql += (teacherMap.get(teacherJson.get("teacherSerialNo").getAsString())==null?0:teacherMap.get(teacherJson.get("teacherSerialNo").getAsString())) + ","
                    + "'" + teacherJson.get("name").getAsString() + "',"
                    + "'" + teacherJson.get("teacherSerialNo").getAsString() + "',";
            }else{
                sql += "0,'',null,";
            }
            sql += coureseMap.get(asJsonObject.get("courseClassId").getAsString()) + ","
                + "'" + asJsonObject.get("courseClassName").getAsString() + "',"
                + classMap.get(asJsonObject.get("classSerialNo").getAsString()) + ","
                + "'" + asJsonObject.get("className").getAsString() + "',"
                + asJsonObject.get("dayOfWeek").getAsInt() + ","
                + "'" + asJsonObject.get("dayOfWeekName").getAsString() + "',"
                + tmePeriod.get(asJsonObject.get("timeOption").getAsInt()) + ","
                + timeNumber + ","
                + (classroomMap.get(asJsonObject.get("classRoomSerialNo").getAsString()) == null ? 0 : classroomMap.get(asJsonObject.get("classRoomSerialNo").getAsString())) + ","
                + "'" + asJsonObject.get("classRoomName").getAsString() + "',1,now(),"
                + "'" + asJsonObject.get("scheduleDate").getAsString() + "',"
                + "'" + asJsonObject.get("id").getAsString() + "' FROM DUAL"
                + " WHERE NOT EXISTS(SELECT * FROM course_table WHERE jianyue_id = '" + asJsonObject.get("id").getAsString() +"' and status = 1)";
//            sqls.add(updateSql);
//            sqls.add(sql);
            SqlRunnerAdapter.db().insert(sql);
            count ++;
        }
    }

    public void insertCourseTableEntiy(JsonArray data, Map<String, String> classroomMap, Map<String, String> coureseMap,
                                  Map<String, String> semesterMap, Map<String, String> teacherMap, Map<String, String> classMap) {
        Map<Integer, Integer> tmePeriod = ScheduleUtil.getTmePeriod();
        String tableName = "course_table";
        List<Entity> entityList = new ArrayList();
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList("select * from course_table");
        Set<String> serialNos = new HashSet<>();
        for (Map<String, Object> objectMap : list) {
            if(objectMap.get("jianyue_id") == null){
                continue;
            }
            serialNos.add(objectMap.get("jianyue_id").toString());
        }
        for (JsonElement jsonElement : data) {
            JsonObject asJsonObject = jsonElement.getAsJsonObject();
            JsonArray teachers = asJsonObject.get("teachers").getAsJsonArray();
            if(asJsonObject.get("courseClassId").isJsonNull() || coureseMap.get(asJsonObject.get("courseClassId").getAsString()) == null){
                continue;
            }
            if(serialNos.contains(asJsonObject.get("id").getAsString())){
                continue;
            }
            int timeNumber = asJsonObject.get("numberOfDay").getAsInt();//节次

            Entity entity = Entity.create(tableName);
            entity.set("id", IdUtil.getSnowflakeNextId());
            entity.set("base_semester_id", semesterMap.get(asJsonObject.get("semesterSerialNo").getAsString()));

            if(teachers.size() > 0){
                JsonObject teacherJson = teachers.get(0).getAsJsonObject();
                entity.set("teacher_id", (teacherMap.get(teacherJson.get("teacherSerialNo").getAsString())==null?0:teacherMap.get(teacherJson.get("teacherSerialNo").getAsString())));
                entity.set("teacher_name", teacherJson.get("name").getAsString());
                entity.set("teacher_serial_no", teacherJson.get("teacherSerialNo").getAsString());
            }else{
                entity.set("teacher_id", 0);
//                entity.set("teacher_name", "");
//                entity.set("teacher_serial_no", "");
            }
            entity.set("course_id", coureseMap.get(asJsonObject.get("courseClassId").getAsString()));
            entity.set("course_name", asJsonObject.get("courseClassName").getAsString());
            entity.set("class_id", classMap.get(asJsonObject.get("classSerialNo").getAsString()));
            entity.set("class_name", asJsonObject.get("className").getAsString());
            entity.set("weeks", asJsonObject.get("dayOfWeek").getAsInt());
            entity.set("weeks_cn", asJsonObject.get("dayOfWeekName").getAsString());
            entity.set("time_period", tmePeriod.get(asJsonObject.get("timeOption").getAsInt()));
            entity.set("time_number", timeNumber);
            entity.set("site_id", (classroomMap.get(asJsonObject.get("classRoomSerialNo").getAsString()) == null ? 0 : classroomMap.get(asJsonObject.get("classRoomSerialNo").getAsString())));
            entity.set("site_name", asJsonObject.get("classRoomName").getAsString());
            entity.set("status", 1);
            entity.set("create_date", sdf.format(new Date()));
            entity.set("schedule_date", asJsonObject.get("scheduleDate").getAsString());
            entity.set("jianyue_id", asJsonObject.get("id").getAsString());
            entityList.add(entity);
        }
        if(!entityList.isEmpty()){
            SqlRunnerAdapter.db().dynamicInsertBatch(tableName, entityList);
        }
    }

    /**
     * 提取课时相关信息
     * @param data 数据
     *
     */
    public void insertClassTime(JsonArray data) {

        Map<String, JsonObject> infoMap = new HashMap<>();
        Map<Integer, Integer> tmePeriod = ScheduleUtil.getTmePeriod();
        for (JsonElement jsonElement : data) {
            JsonObject asJsonObject = jsonElement.getAsJsonObject();
            String numberOfDayName = asJsonObject.get("numberOfDayName").getAsString();
            JsonObject json = new JsonObject();
            json.add("startTime", asJsonObject.get("startTime"));
            json.add("endTime", asJsonObject.get("endTime"));
            json.addProperty("tmePeriod", tmePeriod.get(asJsonObject.get("timeOption").getAsInt()));
            json.add("numberOfDay", asJsonObject.get("numberOfDay"));
            infoMap.put(numberOfDayName, json);
        }
        int count = 1;
        for (String number : infoMap.keySet()) {
            JsonObject jsonObject = infoMap.get(number);
            int timeNumber = jsonObject.get("numberOfDay").getAsInt();//节次
            long id = System.currentTimeMillis();

            String sql = "INSERT INTO class_time(id,time_period,number,full_name,short_name,create_date,summer_start_time"
                    + ",summer_end_time,winter_start_time,winter_end_time) select " + id + String.format("%04d", count) + ","
                    + jsonObject.get("tmePeriod").getAsInt() + ","
                    + timeNumber + ","
                    + "'" + number + "',"
                    + "'" + number + "',now(),"
                    + "'" + jsonObject.get("startTime").getAsString() + "',"
                    + "'" + jsonObject.get("endTime").getAsString() + "',"
                    + "'" + jsonObject.get("startTime").getAsString() + "',"
                    + "'" + jsonObject.get("endTime").getAsString() + "' FROM DUAL"
                    + " WHERE NOT EXISTS(SELECT * FROM class_time WHERE time_period = "
                    + jsonObject.get("tmePeriod").getAsInt()
                    + " and number = " + timeNumber + ")";
//            sqls.add(sql);
            SqlRunnerAdapter.db().insert(sql);
            count ++;
        }
    }

    /**
     * 插入记录表
     * @param tableName 表名字
     * @param idsMap id对应关系
     */
    public void insertRecord(String tableName, Map<String, String> idsMap){
        if(idsMap.isEmpty()){
            return;
        }
//        List<String> sqls = new ArrayList<>();
        for (String sourceId : idsMap.keySet()) {
            String sql = "INSERT INTO jianyue_data(create_date,table_name,source_id,jianyue_id) value(now(),'"
                    + tableName + "','" + sourceId + "','" + idsMap.get(sourceId) + "')";
//            sqls.add(sql);
            SqlRunnerAdapter.db().insert(sql);
        }
    }

}