小青牛幼儿系统(教师端)
需求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}
TimeUtils
和上面的一样
返回
总结
这个次数的统计,因为要一层包一层,最开始是因为把sql放到第三个for,导致运行时间噌噌噌向上涨,跑一下要10多秒,最后从第三个for到第二个,再到第一个,这样才能勉强及格到1秒多,希望以后能注意这个sql的放置
最开始使用时间统计时,使用的并不是这种sql直接查询所有的时间,然后从第一天开始往后分析数据
而是查询每一天进行查看,但不知道为啥会使得我的时间复杂度超标,如果后头有机会我再尝试搞一下这中写法