小青牛项目人数和时间统计

小青牛幼儿系统（教师端）

需求1

统计幼儿园中，所有的区域，本班每一个学生，在传入的第 n 个周内，在有效时间内（9：00 ~ 11：00），出现的次数

传参

周次 week（Integer） 、班级 Id（classId）、token

代码

Controller

/**
 * 教师端--添加图文速记功能：老师了解班内各个学生各自喜欢的区域（学生包含区域）
 *
 * @param token,classId,week
 * @return
 */
@ResponseBody
@GetMapping("/teacher/graphicShorthand/teacherFindStudentTeacher")
public BaseReturnDto teacherFindStudentTeacher(@CookieValue("token") String token, @RequestParam("classId") String classId, @RequestParam("week") Integer week) {
   try {
      DecodedJWT tokenInfo = JWTUtil.getTokenInfo(token);
      Integer schoolId = Integer.valueOf(tokenInfo.getClaim("schoolId").asString());
      String startTime = observationRecordService.teacherFindTermSchoolTime(schoolId);

      DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
      Date firSunDate = TimeUtils.nextSunday(format.parse(startTime));
      Date startDate = TimeUtils.weekStartTime(firSunDate, week);
      Date endDate = TimeUtils.nextSaturday(startDate);

      startDate = format.parse(format.format(startDate));
      endDate = format.parse(format.format(endDate));
      List<OREveryStudentActive> orEveryStudentActiveList = observationRecordService.teacherFindStudentTeacher(classId, startDate, endDate);
      return new DataReturnDto<>(ReturnCodeEnum.SUCCESS, orEveryStudentActiveList);
   } catch (IllegalArgumentException e) {
      e.printStackTrace();
      log.error(e.getMessage(), e);
      return new SimpleReturnDto(ReturnCodeEnum.PARAMS_ERROR, e.getMessage());
   } catch (Exception e) {
      e.printStackTrace();
      log.error(e.getMessage(), e);
      return new SimpleReturnDto(ReturnCodeEnum.SERVER_ERROR, e.getMessage());
   }
}

Service

@Override
public List<OREveryStudentActive> teacherFindStudentTeacher(String classId, Date startTime, Date endTime) {
    // 总共有多少个区域
    final int allArea = 19;

    endTime = TimeUtils.nextDay(endTime);
    // 查找所有学生
    List<String> studentList = observationRecordDao.teacherFindEveryStudent(classId);
    List<Integer> dayList = new ArrayList<>();
    // 记录学生信息
    OREveryStudentActive orEveryStudentActive = new OREveryStudentActive();
    // 记录区域信息
    OREveryStudentActiveTeacherArea orEveryStudentActiveTeacherArea = new OREveryStudentActiveTeacherArea();

    // 记录所有的学生信息list
    List<OREveryStudentActive> orEveryStudentActiveList = new ArrayList<>();
    // 记录所有的区域信息list
    List<OREveryStudentActiveTeacherArea> orEveryStudentActiveTeacherAreaList = new ArrayList<>();

    // 查看相差几天
    Integer daysBetween = TimeUtils.daysBetween(startTime, endTime);

    // 包含该学生的id，出现区域，出现时间
    List<StudentActiveHoursDto> studentActiveHoursDtoList = observationRecordDao.teacherFindStudentTeacher(startTime, endTime);

    // 每个学生
    for (String studentId : studentList) {
        String studentName = observationRecordDao.teacherFindStudentName(studentId);

        orEveryStudentActive.setStudentId(studentId);
        orEveryStudentActive.setStudentName(studentName);
        // 包含该学生的出现区域，出现时间
        List<StudentActiveHoursDto> studentActiveHoursDtoList_AT = new ArrayList<>();
        for (StudentActiveHoursDto dto : studentActiveHoursDtoList) {
            if (dto.getStudentId() != null && dto.getStudentId().equals(studentId)) {
                studentActiveHoursDtoList_AT.add(dto);
            }
        }

        // 每个区域
        for (int areaId = 1; areaId <= allArea; areaId++) {
            int num = 0;
            orEveryStudentActiveTeacherArea.setAreaId(areaId);
            // 查找出所有天数内该学生所有的出现时间
            List<Date> timeList = new ArrayList<>();
            for (StudentActiveHoursDto dto : studentActiveHoursDtoList_AT) {
                if (dto.getAreaId() != null && dto.getAreaId() == areaId) {
                    timeList.add(dto.getCreateTime());
                }
            }
            Date endTime_temp = endTime;
            Date start_temp = startTime;
            // 每一天
            for (int i = 0; i < daysBetween; i++) {
                endTime_temp = TimeUtils.nextDay(start_temp);
                // 查找符合要求的时间
                for (Date date : timeList) {
                    if (TimeUtils.betweenDays(start_temp,endTime_temp,date)) {
                        if (TimeUtils.timeBetween9And11(date)) {
                            num++;
                        }
                    }
                }
                start_temp = endTime_temp;
                dayList.add(num);
                num = 0;
            }
            orEveryStudentActiveTeacherArea.setDayList(dayList);
            dayList = new ArrayList<>();
            orEveryStudentActiveTeacherAreaList.add(orEveryStudentActiveTeacherArea);
            orEveryStudentActiveTeacherArea = new OREveryStudentActiveTeacherArea();
        }
        orEveryStudentActive.setOrEveryStudentActiveTeacherAreaList(orEveryStudentActiveTeacherAreaList);
        orEveryStudentActiveTeacherAreaList = new ArrayList<>();
        orEveryStudentActiveList.add(orEveryStudentActive);
        orEveryStudentActive = new OREveryStudentActive();
    }
    return orEveryStudentActiveList;
}

Mapper

<!--教师查看本班的每个学生在所有区域的活动时间-->
<select id="teacherFindEveryStudentActiveHousrs" resultType="com.bnuz.yjpj.observationRecord.dto.StudentActiveHoursDto">
    SELECT th.create_time, th.area_id
    FROM t_graphic_shorthand_history AS th
             LEFT JOIN t_graphic_shorthand_image AS ti
                       ON th.graphic_shorthand_history_id = ti.graphic_shorthand_history_id
    WHERE ti.student_id = #{studentId}
      AND th.create_time BETWEEN #{startTime} AND #{endTime}
</select>

<!--查看某段时间内某个区域里特定学生的出现次数-->
<select id="teacherFindStudentTeacher" resultType="com.bnuz.yjpj.observationRecord.dto.StudentActiveHoursDto">
    SELECT ti.student_id, th.create_time, th.area_id
    FROM t_graphic_shorthand_history AS th
             LEFT JOIN t_graphic_shorthand_image AS ti
                       ON th.graphic_shorthand_history_id = ti.graphic_shorthand_history_id
    WHERE th.create_time BETWEEN #{startTime} AND #{endTime}
</select>

<!--获取student的名字-->
<select id="teacherFindStudentName" resultType="String">
    SELECT name
    FROM  t_student
    WHERE studentId = #{studentId}
</select>

TimeUtils

public class TimeUtils {

    // 计算两个date的时间差，返回 minutes
    public static Integer timeSubtracts(Date firstTime, Date secondTime) {
        Instant instant1 = firstTime.toInstant();
        Instant instant2 = secondTime.toInstant();

        Duration duration = Duration.between(instant1, instant2);

        long minutes = duration.toMinutes();

        return Math.toIntExact(minutes);
    }

    // 判断是否在同一天
    public static boolean timeDay(Date firstTime, Date secondTime) {
        LocalDate localDate1 = firstTime.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toLocalDate();
        LocalDate localDate2 = secondTime.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toLocalDate();

        // 判断两个日期是否相等，true：是，false：否
        return localDate1.isEqual(localDate2);
    }

    // 计算传入date到11点的时间
    public static Integer timeSubtracts11(Date date) {
        LocalDateTime localDateTime = date.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toLocalDateTime();
        LocalDateTime elevenOClockToday = localDateTime.toLocalDate().atTime(11, 0);

        Duration duration = Duration.between(elevenOClockToday, localDateTime);

        long minutes = duration.toMinutes();
        return Math.toIntExact(Math.abs(minutes));
    }

    // 判断date是否在9点到11点之间
    public static boolean timeBetween9And11(Date date) {
        LocalDateTime localDateTime = date.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toLocalDateTime();
        int hour = localDateTime.getHour();

        if (hour >= 9 && hour < 11) {
            return true;
        }
        return false;
    }

    // 计算两个时间相差几天
    public static Integer daysBetween(Date date1, Date date2) {
        Instant instant1 = date1.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toInstant();
        Instant instant2 = date2.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toInstant();
        Duration duration = Duration.between(instant1, instant2);
        return Math.toIntExact(Math.abs(duration.toDays())); // 返回时间差的天数
    }

    // 计算某个date的下一天
    public static Date nextDay(Date inputDate) {
        LocalDate date = inputDate.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toLocalDate();
        LocalDate nextDate = date.plusDays(1);
        Date outputDate = Date.from(nextDate.atStartOfDay(ZoneId.of("Asia/Shanghai")).toInstant());
        return outputDate;
    }

    public static boolean betweenDays(Date start, Date end, Date date) {
        // 判断日期是否在起始日期和结束日期之间
        boolean isBetween = date.getTime() >= start.getTime() && date.getTime() <= end.getTime();
        return isBetween;
    }

    // 计算下一个星期日
    public static Date nextSunday(Date startTime) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startTime);

        while (calendar.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
            calendar.add(Calendar.DAY_OF_WEEK, 1);
        }

        return calendar.getTime();
    }

    // 计算下一个星期六
    public static Date nextSaturday(Date startTime) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startTime);

        while (calendar.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY) {
            calendar.add(Calendar.DAY_OF_WEEK, 1);
        }

        return calendar.getTime();
    }

    // 计算第n周的开始时间
    public static Date weekStartTime(Date inputDate, Integer week) {
        if (week == 1) {
            week = 0;
        } else {
            week -= 2;
        }

        LocalDate date = inputDate.toInstant().atZone(ZoneId.of("Asia/Shanghai")).toLocalDate();
        LocalDate nextDate = date.plusDays(week * 7);
        Date outputDate = Date.from(nextDate.atStartOfDay(ZoneId.of("Asia/Shanghai")).toInstant());

        return outputDate;
    }
}

OREveryStudentActive

/**
 * 记录区域的id以及区域的学生
 * 具体到 学生名字，id
 */

@Data
@AllArgsConstructor
@NoArgsConstructor
public class OREveryStudentActive {
    private String studentId;
    private String studentName;
    private List<OREveryStudentActiveTeacherArea> orEveryStudentActiveTeacherAreaList;
}

OREveryStudentActiveTeacherArea

/**
 * 记录区域的id以及区域的活动人数
 */

@Data
@AllArgsConstructor
@NoArgsConstructor
public class OREveryStudentActiveTeacherArea {
    private Integer areaId;
    private List<Integer> dayList;
}

StudentActiveHoursDto

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentActiveHoursDto {
    private String studentId;
    private Integer areaId;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

}

返回

需求2

统计幼儿园中，所有的区域，本班所有的学生，在传入的第 n 个周内，在有效时间内（9：00 ~ 11：00），出现的总时间

传参

周次 week（Integer） 、班级 Id（classId）、token

代码

Controller

/**
 * 教师端--添加图文速记功能：教师查看本班的每个学生在所有区域的活动时间
 *
 * @param classId,week,token
 * @return
 */
@ResponseBody
@GetMapping("/teacher/graphicShorthand/teacherFindEveryStudentActiveHousrs")
public BaseReturnDto teacherFindEveryStudentActiveHousrs(@CookieValue("token") String token, @RequestParam("classId") String classId, @RequestParam("week") Integer week) {
   try {
      DecodedJWT tokenInfo = JWTUtil.getTokenInfo(token);
      Integer schoolId = Integer.valueOf(tokenInfo.getClaim("schoolId").asString());
      String startTime = observationRecordService.teacherFindTermSchoolTime(schoolId);
      DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
      Date firSunDate = TimeUtils.nextSunday(format.parse(startTime));
      Date startDate = TimeUtils.weekStartTime(firSunDate, week);
      Date endDate = TimeUtils.nextSaturday(startDate);
      startDate = format.parse(format.format(startDate));
      endDate = format.parse(format.format(endDate));
      List<List<Integer>> resultList = observationRecordService.teacherFindEveryStudentActiveHousrs(classId, startDate, endDate);
      return new DataReturnDto<>(ReturnCodeEnum.SUCCESS, resultList);
   } catch (IllegalArgumentException e) {
      e.printStackTrace();
      log.error(e.getMessage(), e);
      return new SimpleReturnDto(ReturnCodeEnum.PARAMS_ERROR, e.getMessage());
   } catch (Exception e) {
      e.printStackTrace();
      log.error(e.getMessage(), e);
      return new SimpleReturnDto(ReturnCodeEnum.SERVER_ERROR, e.getMessage());
   }
}

Service

@Override
public List<List<Integer>> teacherFindEveryStudentActiveHousrs(String classId, Date startTime, Date endTime) {
    List<Integer> resultList = new ArrayList<>();
    List<List<Integer>> resultList_All = new ArrayList<>();
    endTime = TimeUtils.nextDay(endTime);
    Integer time = 0;
    // 总的时间有多少
    Integer days = TimeUtils.daysBetween(startTime, endTime);
    for (int i = 0;i < 19;i++){
        List<Integer> tempList = new ArrayList<>();
        for (int j = 0;j < days;j++){
            tempList.add(0);
        }
        resultList_All.add(tempList);
    }
    // 查找所有学生，这里把多出的重复的学生排除掉了
    List<String> studentList = observationRecordDao.teacherFindEveryStudent(classId);
    int daysApart = 0;
    for (int j = 0; j < studentList.size(); j++) {
        // 根据学生id查询创建时间、区域id
        List<StudentActiveHoursDto> dtoList = observationRecordDao.teacherFindEveryStudentActiveHousrs(startTime, endTime, studentList.get(j));
        resultList = new ArrayList<>();
        // 相差时间
        daysApart = 0;
        time = 0;
        for (int i = 0; i < dtoList.size() - 1; i++) {
            // 相差时间
            daysApart = TimeUtils.daysBetween(startTime, dtoList.get(i).getCreateTime());
            // // 如果没有数据的日子下，自动添加0
            // while (daysApartTemp > 0 && resultList.size() < daysApart) {
            //     resultList.add(0);
            //     daysApartTemp -= 1;
            // }
            // 如果是在同一天
            if (TimeUtils.timeDay(dtoList.get(i).getCreateTime(), dtoList.get(i + 1).getCreateTime())) {
                // 如果在9到11点期间
                if (TimeUtils.timeBetween9And11(dtoList.get(i).getCreateTime())) {
                    // 计算时间差
                    if (TimeUtils.timeBetween9And11(dtoList.get(i + 1).getCreateTime())) {
                        time = TimeUtils.timeSubtracts(dtoList.get(i).getCreateTime(), dtoList.get(i + 1).getCreateTime());
                    } else {
                        time = TimeUtils.timeSubtracts11(dtoList.get(i).getCreateTime());
                    }
                    resultList_All.get(dtoList.get(i).getAreaId() - 1)
                            .set(daysApart,
                                    resultList_All.get(dtoList.get(i).getAreaId() - 1).get(daysApart) + time);
                }
            } else { // 如果不同天
                // 如果在9到11点期间
                if (TimeUtils.timeBetween9And11(dtoList.get(i).getCreateTime())) {
                    time = TimeUtils.timeSubtracts11(dtoList.get(i).getCreateTime());
                    resultList_All.get(dtoList.get(i).getAreaId() - 1)
                            .set(daysApart,
                                resultList_All.get(dtoList.get(i).getAreaId() - 1).get(daysApart) + time);
                }
            }
        }
        // 添加最后一个时间段的时间差
        if (!dtoList.isEmpty() && TimeUtils.timeBetween9And11(dtoList.get(dtoList.size() - 1).getCreateTime())) {
            daysApart = TimeUtils.daysBetween(startTime, dtoList.get(dtoList.size() - 1).getCreateTime());
            // 计算最后一个数据和11点的差距
            time = TimeUtils.timeSubtracts11(dtoList.get(dtoList.size() - 1).getCreateTime());
            // 保存数据到 resultList_All
            resultList_All.get(dtoList.get(dtoList.size() - 1).getAreaId() - 1)
                    .set(daysApart,
                        resultList_All.get(dtoList.get(dtoList.size() - 1).getAreaId() - 1).get(daysApart) + time);
        }
    }
    return resultList_All;
}

Mapper

<!--教师查看本班的每个学生在所有区域的活动时间-->
<select id="teacherFindEveryStudentActiveHousrs" resultType="com.bnuz.yjpj.observationRecord.dto.StudentActiveHoursDto">
    SELECT th.create_time, th.area_id
    FROM t_graphic_shorthand_history AS th
             LEFT JOIN t_graphic_shorthand_image AS ti
                       ON th.graphic_shorthand_history_id = ti.graphic_shorthand_history_id
    WHERE ti.student_id = #{studentId}
      AND th.create_time BETWEEN #{startTime} AND #{endTime}
</select>

<!--获取在同一个班的student-->
<select id="teacherFindEveryStudent" resultType="String">
    SELECT studentId
    FROM  t_students_attend_class
    WHERE classId = #{classId}
</select>

TimeUtils

和上面的一样

返回

EFT JOIN t_graphic_shorthand_image AS ti
ON th.graphic_shorthand_history_id = ti.graphic_shorthand_history_id
WHERE ti.student_id = #{studentId}
AND th.create_time BETWEEN #{startTime} AND #{endTime}

SELECT studentId FROM t_students_attend_class WHERE classId = #{classId} ```

TimeUtils

和上面的一样

返回

总结

这个次数的统计，因为要一层包一层，最开始是因为把sql放到第三个for，导致运行时间噌噌噌向上涨，跑一下要10多秒，最后从第三个for到第二个，再到第一个，这样才能勉强及格到1秒多，希望以后能注意这个sql的放置

最开始使用时间统计时，使用的并不是这种sql直接查询所有的时间，然后从第一天开始往后分析数据

而是查询每一天进行查看，但不知道为啥会使得我的时间复杂度超标，如果后头有机会我再尝试搞一下这中写法