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

package com.xjrsoft.module.schedule.util;

import cn.hutool.core.util.StrUtil;
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.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.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author dzx
 * @date 2024/1/23
 */
@Slf4j
public class DataUtil {

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

        Map<Long, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        for (Department department : dataList) {
            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());
            long timestamp = System.currentTimeMillis();
            String sign = ScheduleUtil.createSign(timestamp);
            ScheduleUtil.doPost(url, paramJson.toString(), sign, timestamp);
            if(ids != null && ids.get(department.getId()) != null){
                url  = ScheduleUtil.apiUrl + "SchoolDistrict/Update";
                paramJson.addProperty("id", ids.get(department.getId()));
                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(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, tableName, idMap);
        return idMap;
    }

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

        Map<Long, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        for (BaseClass baseClass : dataList) {
            String url  = ScheduleUtil.apiUrl + "class/create";
            JsonObject paramJson = new JsonObject();
            paramJson.addProperty("teacherSerialNo", teacherMap.get(baseClass.getTeacherId()));
            paramJson.addProperty("name", baseClass.getName());

            paramJson.addProperty("semesterSerialNo", semesterSerialNo);
            paramJson.addProperty("eduYearSerialNo", gradeMap.get(baseClass.getGradeId()));
            paramJson.addProperty("extendId", baseClass.getId());
            paramJson.addProperty("classRoomSerialNo", classroomMap.get(baseClass.getClassroomId()));
            if(ids != null && ids.get(baseClass.getId()) != null){
                url  = ScheduleUtil.apiUrl + "class/update";
                paramJson.addProperty("serialNo", ids.get(baseClass.getId()));
                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(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, tableName, idMap);
        return idMap;
    }

    public Map<Long, String> insertClassRoom(Db db, String tableName, String schoolDistrictId, Map<Long, 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<ClassroomJianyuekbVo> dataList = db.query(sql, ClassroomJianyuekbVo.class);

        JsonParser jsonParser = new JsonParser();
        Map<Long, String> idMap = new HashMap<>();
        for (ClassroomJianyuekbVo classroom : dataList) {
            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()) != null){
                url  = ScheduleUtil.apiUrl + "classroom/update";
                paramJson.addProperty("serialNo", ids.get(classroom.getExtendId()));
                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(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, tableName, idMap);
        return idMap;
    }

    public Map<Long, String> insertStudent(Db db, String tableName, Map<Long, String> gradeMap, Map<Long, 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<StudentJianyuekbVo> dataList = db.query(sql, StudentJianyuekbVo.class);

        JsonParser jsonParser = new JsonParser();
        Map<Long, String> idMap = new HashMap<>();
        for (StudentJianyuekbVo student : dataList) {
            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()));
            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);

            if(ids != null && ids.get(student.getExtendId()) != null){
                paramJson.addProperty("serialNo", ids.get(student.getExtendId()));
                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(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, tableName, idMap);
        return idMap;
    }
    /**
     * 新增教职工
     * @param db
     * @param tableName
     * @return
     */
    public Map<Long, String> insertTeacher(Db db, String tableName, Map<Long, 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<XjrUser> xjrUsers = db.query(sql, XjrUser.class);
        JsonParser jsonParser = new JsonParser();

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

            paramJson.addProperty("name", user.getName());
            paramJson.addProperty("alias", user.getNickName());
            paramJson.addProperty("extendId", user.getId().toString());
            //获取时间戳
            long timestamp = System.currentTimeMillis();
            //生成签名
            String sign = ScheduleUtil.createSign(timestamp);
            if(ids != null && ids.get(user.getId()) != null){
                url  = ScheduleUtil.apiUrl + "teacher/update";
                paramJson.addProperty("serialNo", ids.get(user.getId()));
                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.getId(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, tableName, idMap);
        return idMap;
    }
    /**
     *  新增课程
     */
    public Map<Long, String> insertCourse(Db db, String tableName, Map<Long, String> ids, Map<Long, String> tagMap) throws Exception {
        String sql = "select * from " + tableName + " where delete_mark = 0";
        List<BaseCourseSubject> list = db.query(sql, BaseCourseSubject.class);

        Map<Long, String> idMap = new HashMap<>();
        JsonParser jsonParser = new JsonParser();
        for (BaseCourseSubject courseSubject : list) {
            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(tagMap.get(courseSubject.getBaseLabelId()) != null){
                JsonArray jsonArray = new JsonArray();
                jsonArray.add(tagMap.get(courseSubject.getBaseLabelId()));
                paramJson.add("tagSerialNos", jsonArray);
            }

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

            if(ids != null && ids.get(courseSubject.getId()) != null){
                url  = ScheduleUtil.apiUrl + "courseclass/update";
                paramJson.addProperty("id", ids.get(courseSubject.getId()));
                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(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, tableName, idMap);

        return idMap;
    }

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

        Map<Long, 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 (BaseSemester semester : list) {
            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()) != null){
                url  = ScheduleUtil.apiUrl + "semester/update";
                paramJson.addProperty("serialNo", ids.get(semester.getId()));
                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(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, tableName, idMap);

        return idMap;
    }

    public Map<Long, String> insertCourseTag(Db db, String tableName, Map<Long, 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<BaseLabel> list = db.query(sql, BaseLabel.class);
        String url = ScheduleUtil.apiUrl + "courseclass/tag/create";
        JsonParser jsonParser = new JsonParser();
        Map<Long, String> idMap = new HashMap<>();
        for (BaseLabel department : list) {
            if(ids != null && ids.get(department.getId()) != 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(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, tableName, idMap);
        return idMap;
    }

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

        JsonParser jsonParser = new JsonParser();
        Map<Long, String> idMap = new HashMap<>();
        for (Long l : schoolDistrictMap.keySet()) {
            
        }
        for (BaseGrade baseGrade : list) {
            String url = ScheduleUtil.apiUrl + "eduyear/create";
            JsonObject paramJson = new JsonObject();
//            paramJson.addProperty("schoolDistrictId", schoolDistrictId);
            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(baseGrade.getId()) != null){
                paramJson.addProperty("serialNo", ids.get(baseGrade.getId()));
                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(baseGrade.getId(), resultJson.get("data").getAsString());
        }
        //插入记录表
        insertRecord(db, 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 db 数据库链接
     * @param data 接口数据
     * @param classroomMap 教室对应关系
     * @param coureseMap 课程对应关系
     * @param semesterMap 学期对应关系
     * @param teacherMap 教师对应关系
     */
    public void insertCourseTable(Db db, JsonArray data, Map<String, Long> classroomMap, Map<String, Long> coureseMap,
        Map<String, Long> semesterMap, Map<String, Long> teacherMap, Map<String, Long> classMap) throws SQLException {
        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);
            count ++;
        }
        db.executeBatch(sqls);
    }

    /**
     * 提取课时相关信息
     * @param db 数据库链接
     * @param data 数据
     *
     */
    public void insertClassTime(Db db, JsonArray data) throws SQLException {

        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);
        }
        List<String> sqls = new ArrayList<>();
        int count = 1;
        for (String number : infoMap.keySet()) {
            String substring1 = number.substring(number.length() - 1);
            JsonObject jsonObject = infoMap.get(number);
            long id = System.currentTimeMillis();
            String fullName = "第" +substring1 + "节课";
            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() + ","
                    + substring1 + ","
                    + "'" + fullName + "',"
                    + "'" + fullName + "',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 = " + substring1 + ")";
            sqls.add(sql);
            count ++;
        }
        db.executeBatch(sqls);
    }

    /**
     * 插入记录表
     * @param db 数据库链接工具
     * @param tableName 表名字
     * @param idsMap id对应关系
     */
    public void insertRecord(Db db, String tableName, Map<Long, String> idsMap) throws SQLException {
        if(idsMap.isEmpty()){
            return;
        }
        List<String> sqls = new ArrayList<>();
        for (Long 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);
        }
        db.executeBatch(sqls);
    }

}