api/src/main/java/com/xjrsoft/module/databoard/controller/DataboardController.java

package com.xjrsoft.module.databoard.controller;

import cn.dev33.satoken.annotation.SaCheckPermission;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.github.yulichang.toolkit.MPJWrappers;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.xjrsoft.common.enums.DeleteMark;
import com.xjrsoft.common.enums.GenderDictionaryEnum;
import com.xjrsoft.common.enums.StudyStatusEnum;
import com.xjrsoft.common.model.result.RT;
import com.xjrsoft.common.mybatis.SqlRunnerAdapter;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.module.courseTable.entity.CourseTable;
import com.xjrsoft.module.courseTable.service.ICourseTableService;
import com.xjrsoft.module.databoard.dto.StatisticsDetailDto;
import com.xjrsoft.module.databoard.dto.StatisticsDto;
import com.xjrsoft.module.databoard.vo.AttendanceStatisticsVo;
import com.xjrsoft.module.databoard.vo.CourseStatisticsVo;
import com.xjrsoft.module.databoard.vo.HealthStatisticsVo;
import com.xjrsoft.module.databoard.vo.ItemCountAmountVo;
import com.xjrsoft.module.databoard.vo.MeetingStatisticsVo;
import com.xjrsoft.module.databoard.vo.PersonStatisticsVo;
import com.xjrsoft.module.databoard.vo.ProcessStatisticsVo;
import com.xjrsoft.module.databoard.vo.ReimbursementStatisticsVo;
import com.xjrsoft.module.databoard.vo.RepairStatisticsVo;
import com.xjrsoft.module.databoard.vo.VisitorStatisticsVo;
import com.xjrsoft.module.outint.entity.VisitorOutInRecord;
import com.xjrsoft.module.outint.service.IVisitorOutInRecordService;
import com.xjrsoft.module.student.dto.BaseStudentUserPageDto;
import com.xjrsoft.module.student.service.IBaseStudentService;
import com.xjrsoft.module.student.vo.BaseStudentUserPageVo;
import com.xjrsoft.module.system.entity.DictionaryDetail;
import com.xjrsoft.module.teacher.entity.BaseTeacher;
import com.xjrsoft.module.teacher.entity.XjrUser;
import com.xjrsoft.module.teacher.service.ITeacherbaseManagerService;
import com.xjrsoft.module.teacher.vo.XjrUserPageVo;
import com.xjrsoft.module.workflow.entity.WorkflowFormRelation;
import com.xjrsoft.module.workflow.service.IWorkflowFormRelationService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import org.camunda.bpm.engine.history.HistoricProcessInstance;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.validation.Valid;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
* @title: 数据看板代码
* @Date: 2024年8月2日
* @Version 1.0
*/
@RestController
@RequestMapping("/databoard" + "/databoard")
@Api(value = "/databoard"  + "/databoard",tags = "数据看板代码")
@AllArgsConstructor
public class DataboardController {

    private final ICourseTableService courseTableService;
    private final IVisitorOutInRecordService visitorService;
    private final IBaseStudentService studentService;
    private final ITeacherbaseManagerService teacherService;
    private final IWorkflowFormRelationService formRelationService;

    @GetMapping(value = "/process-statistics")
    @ApiOperation(value="流程统计")
    @SaCheckPermission("databoard:detail")
    public RT<ProcessStatisticsVo> processStatistics(@Valid StatisticsDto dto){
        ProcessStatisticsVo result = new ProcessStatisticsVo();

        List<WorkflowFormRelation> relations = formRelationService.list();
        result.setAllCount(Long.parseLong(relations.size() + ""));
        long completeCount = 0L;
        long uncompleteCount = 0L;
        for (WorkflowFormRelation relation : relations) {
            if(HistoricProcessInstance.STATE_COMPLETED.equals(relation.getCurrentState())){
                completeCount ++;
            }else if(HistoricProcessInstance.STATE_INTERNALLY_TERMINATED.equals(relation.getCurrentState())){
                completeCount ++;
            }else if(HistoricProcessInstance.STATE_EXTERNALLY_TERMINATED.equals(relation.getCurrentState())){
                completeCount ++;
            }else{
                uncompleteCount ++;
            }
        }
        result.setCompleteCount(completeCount);
        result.setUncompleteCount(uncompleteCount);

        return RT.ok(result);
    }

    @GetMapping(value = "/meeting-statistics")
    @ApiOperation(value="会议统计")
    @SaCheckPermission("databoard:detail")
    public RT<MeetingStatisticsVo> meetingStatistics(@Valid StatisticsDetailDto dto){
        LocalDateTime startTime = null;
        LocalDateTime endTime = null;
        if(dto.getStartDate() != null){
            startTime = dto.getStartDate().atStartOfDay();
        }
        if(dto.getEndDate() != null){
            endTime = dto.getEndDate().atStartOfDay().plusDays(1).plusNanos(-1);
        }
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        String sql = "SELECT t1.id, (SELECT COUNT(*) FROM xjr_user WHERE FIND_IN_SET(id, t1.meeting_apply_participants)) AS person_count FROM wf_meeting_apply t1" +
                " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
                " WHERE t2.current_state = 'COMPLETED'";
        if(startTime != null && endTime != null){
            sql += " and t1.meeting_apply_date between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        if(dto.getUserId() != null){
            sql += " and FIND_IN_SET(" + dto.getUserId() +", t1.meeting_apply_participants)";
        }
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        MeetingStatisticsVo result = new MeetingStatisticsVo();
        result.setAllCount(list.size());
        int personCount = 0;
        for (Map<String, Object> objectMap : list) {
            personCount += (Long)objectMap.get("person_count");
        }
        result.setPersonCount(personCount);
        return RT.ok(result);
    }

    @GetMapping(value = "/course-statistics")
    @ApiOperation(value="课表统计")
    @SaCheckPermission("databoard:detail")
    public RT<CourseStatisticsVo> courseStatistics(@Valid StatisticsDetailDto dto){

        List<CourseTable> list = courseTableService.list(
                new QueryWrapper<CourseTable>().lambda()
                        .eq((dto.getStartDate() == null && dto.getEndDate() == null), CourseTable::getScheduleDate, LocalDate.now())
                        .between((dto.getStartDate() != null && dto.getEndDate() != null), CourseTable::getScheduleDate, dto.getStartDate(), dto.getEndDate())
        );
        CourseStatisticsVo result = new CourseStatisticsVo();
        result.setAllCount(list.size());
        int teacherCount = 0, noTeacherCount = 0;
        for (CourseTable courseTable : list) {
            if(courseTable.getTeacherId() != null && !"0".equals(courseTable.getTeacherId())){
                teacherCount ++;
            }else{
                noTeacherCount ++;
            }
        }
        result.setTeacherCount(teacherCount);
        result.setNoTeacherCount(noTeacherCount);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String sql = "SELECT t1.* FROM wf_course_adjust t1" +
                " INNER JOIN xjr_workflow_form_relation t2 ON t2.form_key_value = t1.id" +
                " WHERE t1.adjust_type = 'course_substitute'" +
                " AND t2.current_state = 'COMPLETED'";
        if(dto.getStartDate() != null && dto.getEndDate() != null){
            sql += " AND t1.adjust_date between '" + dto.getStartDate().format(formatter) + "' and '" + dto.getEndDate().format(formatter) + "'";
        }else{
            sql += " AND t1.adjust_date = DATE_FORMAT(NOW(),'%Y-%m-%d')";
        }
        result.setAdjustCount(SqlRunnerAdapter.db().selectList(sql).size());

        sql = "SELECT t1.* FROM wf_course_adjust t1" +
            " INNER JOIN xjr_workflow_form_relation t2 ON t2.form_key_value = t1.id" +
            " WHERE t1.adjust_type = 'course_exchange'" +
            " AND t2.current_state = 'COMPLETED'";
        if(dto.getStartDate() != null && dto.getEndDate() != null){
            sql += " AND t1.adjust_date between '" + dto.getStartDate().format(formatter) + "' and '" + dto.getEndDate().format(formatter) + "'";
        }else{
            sql += " AND t1.adjust_date = DATE_FORMAT(NOW(),'%Y-%m-%d')";
        }
        result.setReplaceCount(SqlRunnerAdapter.db().selectList(sql).size());

        return RT.ok(result);
    }

    @GetMapping(value = "/visitor-statistics")
    @ApiOperation(value="访客统计")
    @SaCheckPermission("databoard:detail")
    public RT<VisitorStatisticsVo> visitorStatistics(@Valid StatisticsDto dto){
        List<VisitorOutInRecord> list = visitorService.list(
                new QueryWrapper<VisitorOutInRecord>().lambda()
                        .eq(VisitorOutInRecord::getDeleteMark, DeleteMark.NODELETE.getCode())
        );
        VisitorStatisticsVo result = new VisitorStatisticsVo();
        result.setAllCount(list.size());
        LocalDate today = LocalDate.now();
        int todayCount = 0;
        for (VisitorOutInRecord record : list) {
            if(today.equals(record.getRecordTime().toLocalDate())){
                todayCount ++;
            }
        }
        result.setTodayCount(todayCount);
        return RT.ok(result);
    }

    @GetMapping(value = "/person-statistics")
    @ApiOperation(value="全校师生数据概览")
    @SaCheckPermission("databoard:detail")
    public RT<PersonStatisticsVo> personStatistics(@Valid StatisticsDto dto){

        List<BaseStudentUserPageVo> studentList = studentService.getStudentList(new BaseStudentUserPageDto());

        PersonStatisticsVo result = new PersonStatisticsVo();
        result.setStudentCount(studentList.size());

        Set<String> studentMaleSet = studentList.stream()
                .filter(x -> (x.getGenderCn() != null && x.getGenderCn().equals(GenderDictionaryEnum.MALE.getCode())))
                .map(BaseStudentUserPageVo::getId).collect(Collectors.toSet());
        result.setStudentMaleCount(studentMaleSet.size());

        Set<String> studentFemaleSet = studentList.stream()
                .filter(x -> (x.getGenderCn() != null && x.getGenderCn().equals(GenderDictionaryEnum.FEMALE.getCode())))
                .map(BaseStudentUserPageVo::getId).collect(Collectors.toSet());
        result.setStudentFemaleCount(studentFemaleSet.size());

        Set<String> studentStayMaleSet = studentList.stream()
                .filter(x -> (
                        x.getGenderCn() != null
                        && x.getGenderCn().equals(GenderDictionaryEnum.MALE.getCode())
                        && x.getStduyStatusCn() != null
                        && x.getStduyStatusCn().equals(StudyStatusEnum.InResidence.getCode())
                ))
                .map(BaseStudentUserPageVo::getId).collect(Collectors.toSet());
        result.setStudentStayMaleCount(studentStayMaleSet.size());

        Set<String> studentNotStayMaleSet = studentList.stream()
                .filter(x -> (
                        x.getGenderCn() != null
                                && x.getGenderCn().equals(GenderDictionaryEnum.MALE.getCode())
                                && x.getStduyStatusCn() != null
                                && x.getStduyStatusCn().equals(StudyStatusEnum.AttendDaySchool.getCode())
                ))
                .map(BaseStudentUserPageVo::getId).collect(Collectors.toSet());
        result.setStudentNotStayMaleCount(studentNotStayMaleSet.size());

        Set<String> studentStayFemaleSet = studentList.stream()
                .filter(x -> (
                        x.getGenderCn() != null
                                && x.getGenderCn().equals(GenderDictionaryEnum.FEMALE.getCode())
                                && x.getStduyStatusCn() != null
                                && x.getStduyStatusCn().equals(StudyStatusEnum.InResidence.getCode())
                ))
                .map(BaseStudentUserPageVo::getId).collect(Collectors.toSet());
        result.setStudentStayFemaleCount(studentStayFemaleSet.size());

        Set<String> studentNotStayFemaleSet = studentList.stream()
                .filter(x -> (
                        x.getGenderCn() != null
                                && x.getGenderCn().equals(GenderDictionaryEnum.FEMALE.getCode())
                                && x.getStduyStatusCn() != null
                                && x.getStduyStatusCn().equals(StudyStatusEnum.AttendDaySchool.getCode())
                ))
                .map(BaseStudentUserPageVo::getId).collect(Collectors.toSet());
        result.setStudentNotStayFemaleCount(studentNotStayFemaleSet.size());

        MPJLambdaWrapper<XjrUser> queryWrapper = MPJWrappers.<XjrUser>lambdaJoin()
                .disableSubLogicDel()
                .orderByDesc(XjrUser::getId)
                .select(XjrUser::getId)
                .select(XjrUser.class,x -> VoToColumnUtil.fieldsToColumns(XjrUserPageVo.class).contains(x.getProperty()))
                .innerJoin(BaseTeacher.class,BaseTeacher::getUserId,XjrUser::getId)
                .leftJoin(DictionaryDetail.class,DictionaryDetail::getCode,BaseTeacher::getJobState, ext->ext.selectAs(DictionaryDetail::getName, XjrUserPageVo::getJobState))
                .leftJoin(DictionaryDetail.class,DictionaryDetail::getCode,XjrUser::getCredentialType,ext->ext.selectAs(DictionaryDetail::getName, XjrUserPageVo::getCredentialType))
                .leftJoin(DictionaryDetail.class,DictionaryDetail::getCode,BaseTeacher::getEmployWay,ext->ext.selectAs(DictionaryDetail::getName, XjrUserPageVo::getEmployWay))

                .selectAsClass(BaseTeacher.class, XjrUserPageVo.class);

        List<XjrUserPageVo> teacherList = teacherService.selectJoinList(XjrUserPageVo.class, queryWrapper);
        result.setTeacherCount(teacherList.size());

        Set<String> teacherMaleSet = teacherList.stream()
                .filter(x -> (x.getGender() != null && x.getGender().equals(GenderDictionaryEnum.MALE.getCode())))
                .map(XjrUserPageVo::getId).collect(Collectors.toSet());
        result.setTeacherMaleCount(teacherMaleSet.size());

        Set<String> teacherFemaleSet = teacherList.stream()
                .filter(x -> (x.getGender() != null && x.getGender().equals(GenderDictionaryEnum.FEMALE.getCode())))
                .map(XjrUserPageVo::getId).collect(Collectors.toSet());
        result.setTeacherFemaleCount(teacherFemaleSet.size());

        Set<String> teacherSet1 = teacherList.stream()
                .filter(x -> (x.getEmployWay() != null && x.getEmployWay().equals("FB1601")))
                .map(XjrUserPageVo::getId).collect(Collectors.toSet());
        result.setTeacherCount1(teacherSet1.size());
        Set<String> teacherSet2 = teacherList.stream()
                .filter(x -> (x.getEmployWay() != null && x.getEmployWay().equals("FB1602")))
                .map(XjrUserPageVo::getId).collect(Collectors.toSet());
        result.setTeacherCount2(teacherSet2.size());
        Set<String> teacherSet3 = teacherList.stream()
                .filter(x -> (x.getEmployWay() != null && x.getEmployWay().equals("FB1606")))
                .map(XjrUserPageVo::getId).collect(Collectors.toSet());
        result.setTeacherCount3(teacherSet3.size());
        Set<String> teacherSet4 = teacherList.stream()
                .filter(x -> (x.getEmployWay() != null && x.getEmployWay().equals("FB1609")))
                .map(XjrUserPageVo::getId).collect(Collectors.toSet());
        result.setTeacherCount4(teacherSet4.size());
        Set<String> teacherSet5 = teacherList.stream()
                .filter(x -> (x.getEmployWay() != null && x.getEmployWay().equals("FB1608")))
                .map(XjrUserPageVo::getId).collect(Collectors.toSet());
        result.setTeacherCount5(teacherSet5.size());
        return RT.ok(result);
    }

    @GetMapping(value = "/health-statistics")
    @ApiOperation(value="学生健康统计")
    @SaCheckPermission("databoard:detail")
    public RT<HealthStatisticsVo> healthStatistics(@Valid StatisticsDto dto){
        String sql = "SELECT t1.gender,COUNT(t1.id) AS a_count FROM student_infection t1" +
                " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
                " WHERE t2.current_state = 'COMPLETED' GROUP BY t1.gender";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        HealthStatisticsVo result = new HealthStatisticsVo();
        for (Map<String, Object> objectMap : list) {
            if(objectMap.get("gender") != null && GenderDictionaryEnum.FEMALE.getCode().equals(objectMap.get("gender").toString())){
                result.setInfectionFemaleCount(Integer.parseInt(objectMap.get("a_count").toString()));
            }if(objectMap.get("gender") != null && GenderDictionaryEnum.MALE.getCode().equals(objectMap.get("gender").toString())){
                result.setInfectionMaleCount(Integer.parseInt(objectMap.get("a_count").toString()));
            }
        }

        sql = "SELECT t1.gender,COUNT(t1.id) AS a_count FROM student_psychological t1" +
            " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
            " WHERE t2.current_state = 'COMPLETED' GROUP BY t1.gender";
        list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            if(objectMap.get("gender") != null && GenderDictionaryEnum.FEMALE.getCode().equals(objectMap.get("gender").toString())){
                result.setInfectionFemaleCount(Integer.parseInt(objectMap.get("a_count").toString()));
            }if(objectMap.get("gender") != null && GenderDictionaryEnum.MALE.getCode().equals(objectMap.get("gender").toString())){
                result.setPsychologicalMaleCount(Integer.parseInt(objectMap.get("a_count").toString()));
            }
        }
        return RT.ok(result);
    }

    @GetMapping(value = "/reimbursement-statistics")
    @ApiOperation(value="差旅费报销申请")
    @SaCheckPermission("databoard:detail")
    public RT<ReimbursementStatisticsVo> reimbursementStatistics(@Valid StatisticsDetailDto dto){
        LocalDateTime startTime = null;
        LocalDateTime endTime = null;
        if(dto.getStartDate() != null){
            startTime = dto.getStartDate().atStartOfDay();
        }
        if(dto.getEndDate() != null){
            endTime = dto.getEndDate().atStartOfDay().plusDays(1).plusNanos(-1);
        }
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        String sql = "SELECT t2.* FROM billing_reimbursement t1" +
                " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
                " where 1 = 1";
        if(startTime != null && endTime != null){
            sql += " and t1.aply_time between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        if(dto.getUserId() != null){
            sql += " and t1.applicant_id = " + dto.getUserId();
        }
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        ReimbursementStatisticsVo result = new ReimbursementStatisticsVo();
        result.setAllCount(list.size());
        int completeCount = 0;
        int uncompleteCount = 0;

        for (Map<String, Object> objectMap : list) {
            String statusObj = objectMap.get("current_state").toString();
            if(HistoricProcessInstance.STATE_COMPLETED.equals(statusObj)){
                completeCount ++;
            }else if(HistoricProcessInstance.STATE_INTERNALLY_TERMINATED.equals(statusObj)){
                completeCount ++;
            }else if(HistoricProcessInstance.STATE_EXTERNALLY_TERMINATED.equals(statusObj)){
                completeCount ++;
            }else if(HistoricProcessInstance.STATE_ACTIVE.equals(statusObj)){
                uncompleteCount ++;
            }
        }
        result.setCompleteCount(completeCount);
        result.setUncompleteCount(uncompleteCount);

        sql = "SELECT t1.city_in,SUM(t1.total_amount) AS total_amount FROM billing_reimbursement t1" +
            " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
            " WHERE t2.current_state = 'COMPLETED'";
        if(startTime != null && endTime != null){
            sql += " and t1.aply_time between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        sql += " group by t1.city_in ";
        list = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountAmountVo> amountList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            Object cityInObj = objectMap.get("city_in");
            String item = "";
            if(cityInObj != null && Integer.parseInt(cityInObj.toString()) == 1){
                item = "市外";
            }else if(cityInObj != null && Integer.parseInt(cityInObj.toString()) == 0){
                item = "室内";
            }
            ItemCountAmountVo amountVo = new ItemCountAmountVo();
            amountVo.setItem(item);
            amountVo.setAmount(Double.parseDouble(objectMap.get("total_amount").toString()));
            amountList.add(amountVo);
        }
        result.setAmountList(amountList);
        return RT.ok(result);
    }

    @GetMapping(value = "/repair-statistics")
    @ApiOperation(value="维修申报")
    @SaCheckPermission("databoard:detail")
    public RT<RepairStatisticsVo> repairStatistics(@Valid StatisticsDetailDto dto){
        LocalDateTime startTime = null;
        LocalDateTime endTime = null;
        if(dto.getStartDate() != null){
            startTime = dto.getStartDate().atStartOfDay();
        }
        if(dto.getEndDate() != null){
            endTime = dto.getEndDate().atStartOfDay().plusDays(1).plusNanos(-1);
        }
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        RepairStatisticsVo result = new RepairStatisticsVo();
        String sql = "SELECT t1.* FROM wf_repair_declaration t1" +
                " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
                " WHERE t2.current_state = 'COMPLETED'";
        if(startTime != null && endTime != null){
            sql += " and t1.application_time between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        result.setAllCount(list.size());
        int completeCount = 0;
        int uncompleteCount = 0;
        int personCount = 0;

        for (Map<String, Object> objectMap : list) {
            Object statusObj = objectMap.get("maintenance_feedback");
            if(statusObj != null && !statusObj.toString().isEmpty()){
                completeCount ++;
            }else{
                uncompleteCount ++;
            }
            if(objectMap.get("status") != null && "是".equals(objectMap.get("status").toString())){
                personCount ++;
            }

        }
        result.setCompleteCount(completeCount);
        result.setUncompleteCount(uncompleteCount);
        result.setPersonCount(personCount);

        sql = "SELECT ifnull(SUM(t1.indemnity), 0) as indemnity FROM wf_repair_declaration t1" +
            " INNER JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
            " WHERE t2.current_state = 'COMPLETED'";
        list = SqlRunnerAdapter.db().selectList(sql);
        result.setTotalAmount(Double.parseDouble(list.get(0).get("indemnity").toString()));

        return RT.ok(result);
    }

    @GetMapping(value = "/attendance-statistics")
    @ApiOperation(value="个人考勤")
    @SaCheckPermission("databoard:detail")
    public RT<AttendanceStatisticsVo> attendanceStatistics(@Valid StatisticsDetailDto dto){
        LocalDateTime startTime = null;
        LocalDateTime endTime = null;
        if(dto.getStartDate() != null){
            startTime = dto.getStartDate().atStartOfDay();
        }
        if(dto.getEndDate() != null){
            endTime = dto.getEndDate().atStartOfDay().plusDays(1).plusNanos(-1);
        }
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        AttendanceStatisticsVo result = new AttendanceStatisticsVo();

        String sql = " SELECT ifnull(sum(t1.leave_days), 0) as leave_days FROM wf_teacherleave t1" +
                " INNER JOIN xjr_workflow_form_relation t2 ON t2.form_key_value = t1.id" +
                " WHERE t2.current_state = 'COMPLETED'";

        if(startTime != null && endTime != null){
            String startTimeStr = startTime.format(formatter);
            String endTimeStr = endTime.format(formatter);
            sql +=" AND (" +
                " (t1.leave_start_time BETWEEN '" + startTimeStr + "' and '" + endTimeStr + "')" +
                " OR (t1.leave_end_time BETWEEN '" + startTimeStr + "' and '" + endTimeStr + "')" +
                " OR (t1.leave_start_time > '" + startTimeStr + "' and '" + endTimeStr + "' > t1.leave_end_time)" +
                " OR ('" + startTimeStr + "' > t1.leave_start_time and t1.leave_end_time > '" + endTimeStr + "')" +
                ")";
        }
        if(dto.getUserId() != null){
            sql += " and user_id = " + dto.getUserId();
        }
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        result.setLeaveDays(Double.parseDouble(list.get(0).get("leave_days").toString()));

        sql = "SELECT * FROM teacher_attendance_record WHERE delete_mark = 0 and attendance_status = '迟到'";
        formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        if(startTime != null && endTime != null){
            sql +=" AND attendance_date between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        if(dto.getUserId() != null){
            sql += " and user_id = " + dto.getUserId();
        }
        list = SqlRunnerAdapter.db().selectList(sql);
        result.setLateCount(list.size());

        sql = "SELECT COUNT(t1.id) AS a_count FROM wf_course_adjust t1" +
            " INNER JOIN xjr_workflow_form_relation t2 ON t2.form_key_value = t1.id" +
            " WHERE t1.adjust_type = 'course_exchange'" +
            " AND t2.current_state = 'COMPLETED'";
        if(startTime != null && endTime != null){
            sql +=" AND t1.adjust_date between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        if(dto.getUserId() != null){
            sql += " and t1.user_id = " + dto.getUserId();
        }
        list = SqlRunnerAdapter.db().selectList(sql);
        result.setAdjustCount(Integer.parseInt(list.get(0).get("a_count").toString()));

        sql = "SELECT COUNT(t1.id) AS a_count FROM wf_course_adjust t1" +
            " INNER JOIN xjr_workflow_form_relation t2 ON t2.form_key_value = t1.id" +
            " WHERE t1.adjust_type = 'course_substitute'" +
            " AND t2.current_state = 'COMPLETED'";
        if(startTime != null && endTime != null){
            sql +=" AND t1.adjust_date between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        if(dto.getUserId() != null){
            sql += " t1.and user_id = " + dto.getUserId();
        }
        list = SqlRunnerAdapter.db().selectList(sql);
        result.setExchangeCount(Integer.parseInt(list.get(0).get("a_count").toString()));
        return RT.ok(result);
    }

}