sys/src/main/java/com/ssj/statistics/dao/impl/StatisticsQueryImpl.java

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());
    }
}