package com.ssj.statistics.dao.impl; import com.ssj.api.domain.vo.homework.CheckDetailVO; import com.ssj.api.domain.vo.statistics.ListVO; import com.ssj.api.domain.vo.statistics.TeacherSalarysVO; import com.ssj.framework.core.persistence.PagingHibernateJdbcDao; import com.ssj.statistics.dao.IStatisticsQueryDao; import org.apache.commons.collections4.MapUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import org.springframework.stereotype.Repository; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Objects; /** * @author sh * @className StatisticsQueryImpl * @description queryImpl * @date 2021/2/27 */ @Repository public class StatisticsQueryImpl implements IStatisticsQueryDao { @Autowired private PagingHibernateJdbcDao dao; @Override public Page<Map<String, Object>> statisticsList(Map<String, Object> params, Pageable initPage) { StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); /** *teacherId string list 老师id * teacherName string list 老师姓名 * subject string list 科目 * allSubject string list 老师拥有的科目,多科目英文逗号隔开, * correctNums int list 批注页数 * overtimeNums int list 超时批注页数 * notCorrectNums int list 未批注页数 * notCommentNums int list 不批注页数 * repeatNums int list 重复页数 * accuracy string list 正确率 * errorNums int list 报错页数 */ sql.append("select a.correctNums,a.overtimeNums, "); sql.append("a.notCorrectNums,a.notCommentNums, "); sql.append("a.repeatNums,concat(floor(100*(a.totalNums- a.errorNums-a.notCorrectNums)/a.totalNums),'%') as accuracy,a.errorNums,a.teacherId,a.teacherName,a.subject,a.allSubject "); sql.append(" from ("); listInfo(params,queryParams,sql); sql.append(" ) a"); return dao.findPage(sql.toString(),queryParams.toArray(),initPage); } private void listInfo(Map<String, Object> params,List<Object> queryParams,StringBuffer sql){ sql.append("select t.teacher_id as teacherId,t11.name as teacherName,t11.course_name as subject,t11.course_name as allSubject, \n"); sql.append(" sum(t.correct_nums) as correctNums,sum(t.overtime_nums) as overtimeNums,sum(t.not_correct_nums) as notCorrectNums, \n"); sql.append(" sum(t.not_comment_nums) as notCommentNums,sum(t.repeat_nums) as repeatNums,round(avg(t.accuracy),2) as accuracy,sum(t.error_nums) as errorNums,sum(t.total_nums) as totalNums \n"); sql.append(" from plat_teacher_statistics t \n"); sql.append(" left join tb_lib_manager t11 on t.teacher_id = t11.user_id \n"); sql.append(" where t11.type = 30 \n"); if(Objects.nonNull(params.get("beginDate")) && StringUtils.isNotBlank(MapUtils.getString(params,"beginDate"))){ sql.append(" and t.calculate_date >= ? "); queryParams.add(params.get("beginDate")); } if(Objects.nonNull(params.get("endDate")) && StringUtils.isNotBlank(MapUtils.getString(params,"endDate"))){ sql.append(" and t.calculate_date <= ? "); queryParams.add(params.get("endDate")); } if(Objects.nonNull(params.get("teacherName")) && StringUtils.isNotBlank(MapUtils.getString(params,"teacherName"))){ sql.append(" and t11.name like concat('%',?,'%') "); queryParams.add(params.get("teacherName")); } sql.append(" group by t11.user_id "); } @Override public Page<Map<String, Object>> errorList(Map<String, Object> params, Pageable initPage) { StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); sql.append("SELECT DATE_FORMAT(t.end_time,'%Y-%m-%d') as year_1,DATE_FORMAT(t.end_time,'%m月%d日') as workDate from scon_homework_picture_correct t \n"); sql.append("join scon_homework_picture t11 on t.homework_picture_id = t11.id\n"); sql.append("left join tb_lib_vip t12 on t11.vip_id = t12.id\n"); sql.append("where t.type = 8 and t.is_check_error = 1\n"); sql.append("and t11.`subject`=? and t11.teacher_id=?\n"); queryParams.add(params.get("subject")); queryParams.add(params.get("teacherId")); if(Objects.nonNull(params.get("beginDate")) && StringUtils.isNotBlank(MapUtils.getString(params,"beginDate"))){ sql.append(" and to_days(t.end_time) >=to_days(?) "); queryParams.add(params.get("beginDate")); } if(Objects.nonNull(params.get("endDate")) && StringUtils.isNotBlank(MapUtils.getString(params,"endDate"))){ sql.append(" and to_days(t.end_time) <=to_days(?) "); queryParams.add(params.get("endDate")); } if(Objects.nonNull(params.get("checkDate")) && StringUtils.isNotBlank(MapUtils.getString(params,"checkDate"))){ sql.append(" and t.check_date < ? "); queryParams.add(params.get("checkDate")); } sql.append("GROUP BY DATE_FORMAT(t.end_time,'%y-%m-%d')"); return dao.findPage(sql.toString(),queryParams.toArray(),initPage); } @Override public List<Map<String, Object>> findStudentErrorList(String date,String teacherId,String subject) { StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); sql.append("SELECT t11.homework_id as homeworkId,t12.child_name as studentName,count(1) as errorNums \n"); sql.append("from scon_homework_picture_correct t \n"); sql.append("join scon_homework_picture t11 on t.homework_picture_id = t11.id\n"); sql.append("left join tb_lib_vip t12 on t11.vip_id = t12.id\n"); sql.append("where t.type = 8 and t.is_check_error = 1\n"); sql.append("and t11.`subject`=? and t11.teacher_id=?\n"); queryParams.add(subject); queryParams.add(teacherId); sql.append(" and to_days(t.end_time) =to_days(?) "); queryParams.add(date); sql.append("group by t11.homework_id"); return dao.findMap(sql.toString(),queryParams.toArray()); } @Override public Map<String, Object> findTotalData(Map<String, Object> params) { /* correctTotalNums int data 批注总页数 overtimeTotalNums int data 超时批注总页数 notCorrectTotalNums int data 未批注总页数 notCommentTotalNums int data 不批注总页数 repeatTotalNums int data 重复总页数 accuracyTotal string data 总正确率 errorTotalNums correctNums int list 批注页数 overtimeNums int list 超时批注页数 notCorrectNums int list 未批注页数 notCommentNums int list 不批注页数 repeatNums int list 重复页数 accuracy string list 正确率 errorNums int list 报错页数 */ StringBuffer sql = new StringBuffer(); sql.append("select ifnull(sum(a.correctNums),0) as correctTotalNums,ifnull(sum(overtimeNums),0) as overtimeTotalNums, "); sql.append("ifnull(sum(a.notCorrectNums),0) as notCorrectTotalNums,ifnull(sum(notCommentNums),0) as notCommentTotalNums, "); sql.append("ifnull(sum(a.repeatNums),0) as repeatTotalNums,concat(floor(100*(sum(a.totalNums)- sum(a.errorNums)-sum(a.notCorrectNums))/sum(a.totalNums)),'%') as accuracyTotal,ifnull(sum(errorNums),0) as errorTotalNums "); sql.append(" from ("); List<Object> queryParams = new ArrayList<>(); listInfo(params,queryParams,sql); sql.append(" ) a"); return dao.findMap(sql.toString(),queryParams.toArray()).get(0); } @Override public List<ListVO> statisticsDataList(String dateTime) { StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); sql.append("SELECT a.teacher_id,a.teacher_name,a.`subject`,a.all_subject,a.correct_nums,a.overtime_nums,a.not_correct_nums,a.not_comment_nums,a.repeat_nums,a.error_nums,\n"); sql.append("floor(100*(a.total- a.error_nums-a.not_correct_nums)/a.total) as accuracy,a.calculate_date,a.total as total_nums\n"); sql.append("from (\n"); sql.append("SELECT t.name as teacher_name,t.teacher_id,t.`subject`,t.course_name as all_subject,count(if((t.is_mark in (0,2) and t.is_feedback=1) or t.is_mark in (1,3),true,NULL)) as correct_nums, \n"); sql.append("count(if((t.is_feedback=1 or t.is_mark in (1,3)) and (DATE_FORMAT(t.create_time,'%H:%i')>='20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(DATE_ADD(t.create_time,interval 1 day),'%Y-%m-%d'),' 12:00:00'))\n" + " or (DATE_FORMAT(t.create_time,'%H:%i')<'20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(t.create_time,'%Y-%m-%d'),' 23:59:59')),true,null)) as overtime_nums,\n"); sql.append("count(1) as total,\n"); sql.append("DATE_FORMAT(t.calculate_date,'%Y-%m-%d') as calculate_date,\n"); sql.append("count(if(t.is_feedback=0 and t.is_mark in (0,2),true,null)) as not_correct_nums,\n"); sql.append("count(if(t.is_mark=10,true,null)) as not_comment_nums,\n"); sql.append("count(if(t.is_mark=15,true,null)) as repeat_nums,\n"); sql.append("count(if(t.is_check_error=1,true,null)) as error_nums\n"); sql.append("from (\n"); sql.append("select t.teacher_id,t.is_mark,t11.is_check_error,ifnull(t11.end_time,t.create_time) as calculate_date,t.is_feedback,t.create_time,t11.end_time, \n"); sql.append("t12.name,t.subject,t12.course_name \n"); sql.append("from scon_homework_picture t \n"); sql.append("left join scon_homework_picture_correct t11 on t.id = t11.homework_picture_id \n"); sql.append("left join tb_lib_manager t12 on t.teacher_id = t12.user_id\n"); sql.append("where t12.type = 30 and t11.type = 8 and (to_days(t.create_time)<= to_days(?) or to_days(t11.end_time)<= to_days(?))\n"); queryParams.add(dateTime); queryParams.add(dateTime); sql.append(") t \n"); sql.append("GROUP BY t.teacher_id,to_days(t.calculate_date)\n"); sql.append(")a"); return dao.findList(sql.toString(),queryParams.toArray(),ListVO.class); } @Override public List<TeacherSalarysVO> addTeacherSalarysData(String month) { StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); sql.append("SELECT a.correct_nums,a.teacher_id,a.overtime_nums,a.perk_nums,a.error_nums,a.work_date,\n"); sql.append("FLOOR(a.sum_perk/a.perk_nums) as perk_efficiency,round(100*(a.correct_nums-a.error_nums)/a.correct_nums,2) as accuracy,\n"); sql.append("case when a.perk_nums-a.perk_overtime_nums<134 and a.perk_nums-a.perk_overtime_nums>0 and floor(a.sum_perk/a.perk_nums)<120 then 20 \n"); sql.append(" when a.perk_nums = a.perk_overtime_nums then (a.perk_overtime_nums-a.error_overtime_nums)*0.075 \n"); sql.append(" else (a.perk_nums-a.perk_overtime_nums-a.perk_error_nums+a.error_overtime_nums)*0.15+(a.perk_overtime_nums-a.error_overtime_nums)*0.075 end as perk_salary,\n"); sql.append("case when a.correct_nums-a.perk_nums-a.overtime_nums+a.perk_overtime_nums=0 then (a.correct_nums-a.perk_nums-a.error_nums+a.perk_error_nums)*0.075 \n"); sql.append(" else (a.correct_nums-a.perk_nums-a.overtime_nums+a.perk_overtime_nums-a.error_nums+a.perk_error_nums+a.error_overtime_nums2-a.error_overtime_nums)*0.15+(a.overtime_nums-a.perk_overtime_nums-a.error_overtime_nums2+a.error_overtime_nums)*0.075 end as other_salary\n"); sql.append("from (\n"); sql.append("SELECT\n"); sql.append("count(1) as correct_nums,\n"); sql.append("count(if((DATE_FORMAT(t11.create_time,'%H:%i')>='20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(DATE_ADD(t11.create_time,interval 1 day),'%Y-%m-%d'),' 12:00:00'))\n"); sql.append(" or (DATE_FORMAT(t11.create_time,'%H:%i')<'20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(t11.create_time,'%Y-%m-%d'),' 23:59:59')),true,null)) as overtime_nums,\n"); sql.append("count(IF(DATE_FORMAT(t.end_time,'%H:%i')>='18:00' and DATE_FORMAT(t.end_time,'%H:%i')<='20:00',TRUE,null)) as perk_nums,\n"); sql.append("count(IF(DATE_FORMAT(t.end_time,'%H:%i')>='18:00' and DATE_FORMAT(t.end_time,'%H:%i')<='20:00' and ((DATE_FORMAT(t11.create_time,'%H:%i')>='20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(DATE_ADD(t11.create_time,interval 1 day),'%Y-%m-%d'),' 12:00:00'))\n"); sql.append(" or (DATE_FORMAT(t11.create_time,'%H:%i')<'20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(t11.create_time,'%Y-%m-%d'),' 23:59:59'))),TRUE,null)) as perk_overtime_nums,\n"); sql.append("count(IF(t.is_check_error=1,TRUE,null)) as error_nums,\n"); sql.append("count(IF(t.is_check_error=1 and DATE_FORMAT(t.end_time,'%H:%i')>='18:00' and DATE_FORMAT(t.end_time,'%H:%i')<='20:00',TRUE,null)) as perk_error_nums,\n"); sql.append("count(IF(t.is_check_error=1 and DATE_FORMAT(t.end_time,'%H:%i')>='18:00' and DATE_FORMAT(t.end_time,'%H:%i')<='20:00' and ((DATE_FORMAT(t11.create_time,'%H:%i')>='20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(DATE_ADD(t11.create_time,interval 1 day),'%Y-%m-%d'),' 12:00:00'))\n"); sql.append(" or (DATE_FORMAT(t11.create_time,'%H:%i')<'20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(t11.create_time,'%Y-%m-%d'),' 23:59:59'))),TRUE,null)) as error_overtime_nums,\n"); sql.append("count(IF(t.is_check_error=1 and ((DATE_FORMAT(t11.create_time,'%H:%i')>='20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(DATE_ADD(t11.create_time,interval 1 day),'%Y-%m-%d'),' 12:00:00'))\n"); sql.append(" or (DATE_FORMAT(t11.create_time,'%H:%i')<'20:30' and DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s')>concat(DATE_FORMAT(t11.create_time,'%Y-%m-%d'),' 23:59:59'))),TRUE,null)) as error_overtime_nums2,\n"); sql.append("DATE_FORMAT(t.end_time,'%Y-%m-%d') as work_date,\n"); sql.append("sum(IF(DATE_FORMAT(t.end_time,'%H:%i')>='18:00' and DATE_FORMAT(t.end_time,'%H:%i')<='20:00',t.correction_time,0)) as sum_perk,\n"); sql.append("t11.teacher_id\n"); sql.append("from scon_homework_picture_correct t \n"); sql.append("join scon_homework_picture t11 on t.homework_picture_id = t11.id\n"); sql.append("where t.type=8 and t11.is_mark in(0,2) and t11.is_feedback=1 \n"); sql.append("and t.end_time like concat(?,'%') \n"); queryParams.add(month); sql.append("GROUP BY t11.teacher_id,DATE_FORMAT(t.end_time,'%Y-%m-%d')\n"); sql.append(") a"); return dao.findList(sql.toString(),queryParams.toArray(),TeacherSalarysVO.class); } @Override public List<TeacherSalarysVO> findOnlineList(String month) { StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); sql.append("select a.work_date,a.user_id as teacher_id from (\n"); sql.append("SELECT DATE_FORMAT(t.login_datetime,'%Y-%m-%d') as work_date,t.user_id \n"); sql.append(" from tb_user_login t\n"); sql.append(" where DATE_FORMAT(t.login_datetime,'%H:%i')<='18:00' \n"); sql.append(" and (concat(DATE_FORMAT(t.login_datetime,'%Y-%m-%d'),' 20:00:00') <DATE_FORMAT(t.logout_datetime,'%Y-%m-%d %H:%i:%s') or logout_time=0)\n"); sql.append(" and t.login_datetime like concat(?,'%')"); queryParams.add(month); sql.append("GROUP BY t.user_id,to_days(t.login_datetime)\n"); sql.append(") a \n"); sql.append(" join tb_lib_manager t on a.user_id = t.user_id and t.type = 30\n"); sql.append("left join scon_homework_picture b on a.user_id = b.teacher_id and a.work_date = DATE_FORMAT(b.allot_time,'%Y-%m-%d') and DATE_FORMAT(b.allot_time,'%H:%i')>='18:00' and DATE_FORMAT(b.allot_time,'%H:%i')<='20:00'\n"); sql.append("where b.id is null"); return dao.findList(sql.toString(),queryParams.toArray(),TeacherSalarysVO.class); } @Override public List<Map<String, Object>> findRegisterList(Map<String, Object> params) { StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); sql.append("SELECT t.name,t.phone_number,\n"); sql.append("case when t.type = 1 then '常规兼职' when t.type=2 then '高校合作' else '远程实习' end as type, \n"); sql.append(" DATE_FORMAT(t.create_time,'%Y.%m.%d %H.%m') as create_time \n"); sql.append(" from tb_teacher_enrollment t \n"); if(params.get("searchKey")!=null && !"".equals(params.get("searchKey"))){ sql.append(" where t.name like concat('%',?,'%') \n"); sql.append(" or t.phone_number like concat('%',?,'%') \n"); queryParams.add(params.get("searchKey")); queryParams.add(params.get("searchKey")); } sql.append(" order by t.create_time desc,t.id "); return dao.findMap(sql.toString(),queryParams.toArray()); } @Override public List<CheckDetailVO> checkDetailList(String homeworkId) { StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); sql.append("SELECT t.id as picture_id,t.corrected_picture_url as picture_url,2 as check_state \n"); sql.append("from scon_homework_picture t \n"); sql.append("left join scon_homework_picture_correct t11 on t.id = t11.homework_picture_id\n"); sql.append("where t11.type = 8 and t11.is_check_error=1\n"); sql.append(" and t.homework_id = ?"); queryParams.add(homeworkId); return dao.findList(sql.toString(),queryParams.toArray(),CheckDetailVO.class); } @Override public List<Map<String, Object>> salaryList(String userId, int month, int year) { /** * tb_teacher_salary *workDate string list 日期 * correctNums int list 批注总张数 * overtimeNums int list 超时批注总张数 * perkNums int list 高峰时段批注张数 * perkEfficiency String list 高峰时段平均批注时间 * errorNums int list 报错张数 * accuracy string list 正确率 * perkSalary String list 高峰时段薪资 * otherSalary String list 其他时段薪资 * salary String list 总薪资 */ StringBuffer sql = new StringBuffer(); List<Object> queryParams = new ArrayList<>(); sql.append("select t.work_date as workDate,t.correct_nums as correctNums, \n"); sql.append("t.overtime_nums as overtimeNums,t.perk_nums as perkNums, \n"); sql.append(" ifnull(replace(concat(SEC_TO_TIME(t.perk_efficiency),''),'.000000',''),'00:00:00') as perkEfficiency,t.error_nums as errorNums, \n"); sql.append(" concat(ifnull(t.accuracy,'0.00'),'%') as accuracy,t.perk_salary as perkSalary, \n"); sql.append(" t.other_salary as otherSalary,t.salary as salary \n"); sql.append("from tb_teacher_salary t \n"); sql.append("where t.teacher_id = ? and t.month=? and t.year=? order by t.work_date asc"); queryParams.add(userId); queryParams.add(month); queryParams.add(year); return dao.findMap(sql.toString(),queryParams.toArray()); } }