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

package com.xjrsoft.module.databoard.controller;

import cn.dev33.satoken.annotation.SaCheckPermission;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.github.yulichang.toolkit.MPJWrappers;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.xjrsoft.common.annotation.XjrLog;
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.SqlRunnerAdapterUtil;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.module.base.entity.BaseSemester;
import com.xjrsoft.module.base.mapper.BaseSemesterMapper;
import com.xjrsoft.module.databoard.dto.StatisticsDetailDto;
import com.xjrsoft.module.databoard.dto.TeacherChangeStatisticsDetailDto;
import com.xjrsoft.module.databoard.vo.CourseCountListVo;
import com.xjrsoft.module.databoard.vo.CourseStatisticsDetailVo;
import com.xjrsoft.module.databoard.vo.DistributionVo;
import com.xjrsoft.module.databoard.vo.DurationVo;
import com.xjrsoft.module.databoard.vo.HealthItemCountVo;
import com.xjrsoft.module.databoard.vo.HealthStatisticsDetailVo;
import com.xjrsoft.module.databoard.vo.ItemCount2Vo;
import com.xjrsoft.module.databoard.vo.ItemCountAmountVo;
import com.xjrsoft.module.databoard.vo.ItemCountRatioVo;
import com.xjrsoft.module.databoard.vo.ItemCountVo;
import com.xjrsoft.module.databoard.vo.ItemDoubleVo;
import com.xjrsoft.module.databoard.vo.ProcessStatisticsDetailVo;
import com.xjrsoft.module.databoard.vo.RoomStatisticsDetailVo;
import com.xjrsoft.module.databoard.vo.StudnetStatisticsDetailVo;
import com.xjrsoft.module.databoard.vo.SubscriptionStatisticsDetailVo;
import com.xjrsoft.module.databoard.vo.TeacherChangeStatisticsDetailVo;
import com.xjrsoft.module.databoard.vo.TeacherStatisticsDetailVo;
import com.xjrsoft.module.oa.entity.WfSubscription;
import com.xjrsoft.module.oa.service.IWfSubscriptionService;
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.student.vo.StudentReportRecordItemVo;
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.IBaseTeacherService;
import com.xjrsoft.module.teacher.service.ITeacherbaseManagerService;
import com.xjrsoft.module.teacher.vo.XjrUserPageVo;
import com.xjrsoft.module.workflow.entity.WorkflowFormRelation;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
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.math.BigDecimal;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Duration;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Period;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.LinkedHashMap;
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("/datadetail" + "/datadetail")
@Api(value = "/datadetail" + "/datadetail", tags = "数据看板详情代码")
@AllArgsConstructor
public class DatadetailController {

    private final ITeacherbaseManagerService teacherService;
    private final IBaseTeacherService baseTeacherService;
    private final IBaseStudentService studentService;
    private final IWfSubscriptionService subscriptionService;
    private final BaseSemesterMapper baseSemesterMapper;

    @GetMapping(value = "/process-statistics")
    @ApiOperation(value = "流程统计详情")
    @SaCheckPermission("datadetail:detail")
    @XjrLog(value = "流程统计详情", saveResponseData = true)
    public RT<ProcessStatisticsDetailVo> processStatistics(@Valid StatisticsDetailDto dto) {
        ProcessStatisticsDetailVo result = new ProcessStatisticsDetailVo();
        String sql = "SELECT DISTINCT t1.id,t2.schema_name,t1.start_time,t1.end_time,t1.current_state FROM xjr_workflow_form_relation t1" +
                " INNER JOIN xjr_workflow_extra t2 ON t1.process_id = t2.process_id" +
                " WHERE 1 = 1";
        if (dto.getStartDate() != null && dto.getEndDate() != null) {
            sql += " and DATE_FORMAT(t1.start_time, '%Y-%m-%d') BETWEEN '" + dto.getStartDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + "'"
                + " and '" + dto.getEndDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")) + "'";
        }
        List<Map<String, Object>> dataList = SqlRunnerAdapter.db().selectList(sql);
        result.setAllCount(dataList.stream().count());
        Long uncompleteCount = 0L;
        Long completeCount = 0L;

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

        Set<String> itemSet = dataList.stream().map(x -> x.get("schema_name").toString()).collect(Collectors.toSet());

        Map<String, Long> finishedMap = dataList.stream()
                .filter(x -> !HistoricProcessInstance.STATE_ACTIVE.equals(x.get("current_state").toString()))
                .collect(Collectors.groupingBy(x -> x.get("schema_name").toString(), Collectors.counting()));
        Map<String, Long> unfinishedMap = dataList.stream()
                .filter(x -> HistoricProcessInstance.STATE_ACTIVE.equals(x.get("current_state").toString()))
                .collect(Collectors.groupingBy(x -> x.get("schema_name").toString(), Collectors.counting()));

        List<DistributionVo> distributionList = new ArrayList<>();
        for (String item : itemSet) {
            Long unfinishedCount = unfinishedMap.get(item) == null ? 0L : unfinishedMap.get(item);
            Long finishedCount = finishedMap.get(item) == null ? 0L : finishedMap.get(item);
            distributionList.add(
                    new DistributionVo() {{
                        setItem(item);
                        setCompleteCount(finishedCount);
                        setUncompleteCount(unfinishedCount);
                        setAllCount(unfinishedCount + finishedCount);
                    }}
            );
        }
        distributionList.sort(Comparator.comparingLong(DistributionVo::getAllCount).reversed());
        result.setDistributionList(distributionList);
        List<DurationVo> durationList = new ArrayList<>();

        List<Long> secondList = new ArrayList<>();
        List<Map<String, Object>> completeCountList = dataList.stream()
                .filter(x -> HistoricProcessInstance.STATE_COMPLETED.equals(x.get("current_state").toString()))
                .collect(Collectors.toList());
        completeCountList.stream().forEach(el -> {
            Duration diff = Duration.between((LocalDateTime)el.get("start_time"), (LocalDateTime)el.get("end_time"));
            secondList.add(diff.getSeconds());
        });

        long zeroHour = secondList.stream().filter(x -> x < 3600L).count();
        long oneHour = secondList.stream().filter(x -> x < 14400L && x >= 3600L).count();
        long fourHour = secondList.stream().filter(x -> x < 28800L && x >= 14400L).count();
        long eightHour = secondList.stream().filter(x -> x >= 28800L).count();

        DurationVo zeroVo = new DurationVo();
        zeroVo.setDuration("0-1h");
        zeroVo.setCount(zeroHour);
        durationList.add(zeroVo);

        zeroVo = new DurationVo();
        zeroVo.setDuration("1-4h");
        zeroVo.setCount(oneHour);
        durationList.add(zeroVo);

        zeroVo = new DurationVo();
        zeroVo.setDuration("4-8h");
        zeroVo.setCount(fourHour);
        durationList.add(zeroVo);

        zeroVo = new DurationVo();
        zeroVo.setDuration("8h以上");
        zeroVo.setCount(eightHour);
        durationList.add(zeroVo);

        result.setDurationList(durationList);

        return RT.ok(result);
    }

    @GetMapping(value = "/health-statistics")
    @ApiOperation(value = "学生健康统计")
    @SaCheckPermission("datadetail:detail")
    @XjrLog(value = "学生健康统计", saveResponseData = true)
    public RT<HealthStatisticsDetailVo> healthStatistics(@Valid StatisticsDetailDto dto) {
        String sql = "SELECT t1.gender,COUNT(t1.*) 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);
        HealthStatisticsDetailVo result = new HealthStatisticsDetailVo();
        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.*) 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()));
            }
        }

        List<HealthItemCountVo> gradeList = new ArrayList<>();
        sql = "SELECT t1.name as item,COUNT(t3.student_id) AS count FROM base_grade t1" +
                " INNER JOIN base_student_school_roll t2 ON t1.id=  t2.grade_id" +
                " INNER JOIN student_infection t3 ON t3.student_id = t2.user_id" +
                " INNER JOIN xjr_workflow_form_relation t4 ON t3.id = t4.form_key_value" +
                " WHERE t4.current_state = 'COMPLETED' AND t1.delete_mark = 0" +
                " GROUP BY t1.name";
        list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            gradeList.add(
                    new HealthItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setInfectionCount(Long.parseLong(objectMap.get("count").toString()));
                    }}
            );
        }

        sql = "SELECT t1.name as item,COUNT(t3.student_id) AS count FROM base_grade t1" +
                " INNER JOIN base_student_school_roll t2 ON t1.id=  t2.grade_id" +
                " INNER JOIN student_psychological t3 ON t3.student_id = t2.user_id" +
                " INNER JOIN xjr_workflow_form_relation t4 ON t3.id = t4.form_key_value" +
                " WHERE t4.current_state = 'COMPLETED' AND t1.delete_mark = 0" +
                " GROUP BY t1.name";
        list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            for (HealthItemCountVo itemCountVo : gradeList) {
                if (objectMap.get("item").toString().equals(itemCountVo.getItem())) {
                    itemCountVo.setPsychologicalCount(Long.parseLong(objectMap.get("count").toString()));
                }
            }
        }
        result.setGradeList(gradeList);

        List<HealthItemCountVo> classList = new ArrayList<>();
        sql = "SELECT t1.name as item,COUNT(t3.student_id) AS count FROM base_class t1" +
                " INNER JOIN base_student_school_roll t2 ON t1.id=  t2.grade_id" +
                " INNER JOIN student_infection t3 ON t3.student_id = t2.user_id" +
                " INNER JOIN xjr_workflow_form_relation t4 ON t3.id = t4.form_key_value" +
                " WHERE t4.current_state = 'COMPLETED' AND t1.delete_mark = 0" +
                " GROUP BY t1.name";
        list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            classList.add(
                    new HealthItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setInfectionCount(Long.parseLong(objectMap.get("count").toString()));
                    }}
            );
        }

        sql = "SELECT t1.name as item,COUNT(t3.student_id) AS count FROM base_class t1" +
                " INNER JOIN base_student_school_roll t2 ON t1.id=  t2.grade_id" +
                " INNER JOIN student_psychological t3 ON t3.student_id = t2.user_id" +
                " INNER JOIN xjr_workflow_form_relation t4 ON t3.id = t4.form_key_value" +
                " WHERE t4.current_state = 'COMPLETED' AND t1.delete_mark = 0" +
                " GROUP BY t1.name";
        list = SqlRunnerAdapter.db().selectList(sql);
        for (Map<String, Object> objectMap : list) {
            for (HealthItemCountVo itemCountVo : classList) {
                if (objectMap.get("item").toString().equals(itemCountVo.getItem())) {
                    itemCountVo.setPsychologicalCount(Long.parseLong(objectMap.get("count").toString()));
                }
            }
        }
        result.setClassList(classList);
        return RT.ok(result);
    }

    @GetMapping(value = "/person-statistics")
    @ApiOperation(value = "教职工详情数据统计")
    @SaCheckPermission("datadetail:detail")
    @XjrLog(value = "教职工详情数据统计", saveResponseData = true)
    public RT<TeacherStatisticsDetailVo> teahcerStatistics(@Valid StatisticsDetailDto dto) throws ParseException {
        String sql = "SELECT IFNULL(t2.name,'未填写') AS item ,ifnull(COUNT(*), 0) AS a_count FROM base_teacher_education t1" +
                " left join base_teacher t3 on t1.user_id = t3.user_id" +
                " LEFT JOIN xjr_dictionary_detail t2 ON t1.education  = t2.code" +
                " WHERE t1.delete_mark = 0 and t3.job_state in ('ZZZT10004','ZZZT10009','JOB_WCPX','JOB_DDSX')" +
                " GROUP BY t2.name";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        TeacherStatisticsDetailVo result = new TeacherStatisticsDetailVo();
        List<ItemCountVo> educationList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            educationList.add(
                    new ItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                    }}
            );
        }
        result.setEducationList(educationList);

        sql = "SELECT IFNULL(t3.name,'未填写') AS item ,ifnull(COUNT(*), 0) AS a_count FROM xjr_user t1" +
                " INNER JOIN base_teacher t2 ON t1.id = t2.user_id" +
                " LEFT JOIN xjr_dictionary_detail t3 ON t1.gender  = t3.code AND t3.item_id = 2023000000000000004" +
                " WHERE t1.delete_mark = 0 and t2.job_state in ('ZZZT10004','ZZZT10009','JOB_WCPX','JOB_DDSX') GROUP BY t3.name";
        list = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountVo> genderList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            genderList.add(
                    new ItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                    }}
            );
        }
        result.setGenderList(genderList);

        sql = "SELECT IFNULL(t3.name,'未填写') AS item ,ifnull(COUNT(*), 0) AS a_count FROM xjr_user t1" +
                " INNER JOIN base_teacher t2 ON t1.id = t2.user_id" +
                " LEFT JOIN xjr_dictionary_detail t3 ON t2.employ_type  = t3.code AND t3.item_id = 2023000000000000016" +
                " WHERE t1.delete_mark = 0 and t2.job_state in ('ZZZT10004','ZZZT10009','JOB_WCPX','JOB_DDSX') GROUP BY t3.name";
        list = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountVo> employList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            employList.add(
                    new ItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                    }}
            );
        }
        result.setEmployList(employList);

        sql = "SELECT IFNULL(t4.name,'未填写') AS item ,ifnull(COUNT(*), 0) AS a_count FROM xjr_user t1" +
                " INNER JOIN base_teacher t2 ON t1.id = t2.user_id" +
                " LEFT JOIN xjr_user_dept_relation t3 ON t1.id  = t3.user_id" +
                " LEFT JOIN xjr_department t4 ON t3.dept_id = t4.id" +
                " WHERE t1.delete_mark = 0 AND t4.is_major = 1 and t2.job_state in ('ZZZT10004','ZZZT10009','JOB_WCPX','JOB_DDSX') GROUP BY t4.name";

        list = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountVo> deptList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            deptList.add(
                    new ItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                    }}
            );
        }
        result.setDeptList(deptList);

        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);
        List<String> idCardList = teacherList.stream().map(XjrUserPageVo::getCredentialNumber).collect(Collectors.toList());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        LocalDate currentDate = LocalDate.now();
        int age20 = 0;
        int age30 = 0;
        int age40 = 0;
        int age50 = 0;
        int age60 = 0;
        for (String idCard : idCardList) {
            if (StrUtil.isEmpty(idCard) || idCard.length() < 18) {
                continue;
            }
            String birthdayStr = idCard.substring(6, 14);
            java.util.Date date = sdf.parse(birthdayStr);

            // 将Date对象转换为LocalDate对象
            LocalDate birthDate = date.toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDate();
            Period ageObj = Period.between(birthDate, currentDate);
            int age = ageObj.getYears();
            if (age >= 20 && age <= 29) {
                age20++;
            } else if (age >= 30 && age <= 39) {
                age30++;
            } else if (age >= 40 && age <= 49) {
                age40++;
            } else if (age >= 50 && age <= 59) {
                age50++;
            } else if (age >= 60) {
                age60++;
            }
        }

        List<ItemCountVo> ageList = new ArrayList<>();
        ItemCountVo itemCountVo = new ItemCountVo();
        itemCountVo.setItem("20-29岁");
        itemCountVo.setCount(age20);
        ageList.add(itemCountVo);

        itemCountVo = new ItemCountVo();
        itemCountVo.setItem("30-39岁");
        itemCountVo.setCount(age30);
        ageList.add(itemCountVo);

        itemCountVo = new ItemCountVo();
        itemCountVo.setItem("40-49岁");
        itemCountVo.setCount(age40);
        ageList.add(itemCountVo);

        itemCountVo = new ItemCountVo();
        itemCountVo.setItem("50-59岁");
        itemCountVo.setCount(age50);
        ageList.add(itemCountVo);

        itemCountVo = new ItemCountVo();
        itemCountVo.setItem("60岁以上");
        itemCountVo.setCount(age60);
        ageList.add(itemCountVo);

        result.setAgeList(ageList);

        return RT.ok(result);
    }

    @GetMapping(value = "/person-statistics-tea-change")
    @ApiOperation(value = "教职工详情数据统计之教职工变动情况")
    @SaCheckPermission("datadetail:detail")
    @XjrLog(value = "教职工详情数据统计之教职工变动情况", saveResponseData = true)
    public RT<TeacherChangeStatisticsDetailVo> teacherChangeStatisticsDetail(@Valid TeacherChangeStatisticsDetailDto dto){
        TeacherChangeStatisticsDetailVo teacherChangeStatisticsDetailVo = new TeacherChangeStatisticsDetailVo();

        // 处理时间范围
        if(ObjectUtils.isNotEmpty(dto.getStatus()) && dto.getStatus() == 1 && StringUtils.isNotEmpty(dto.getYearMonth())){
            String yearMonthStr = dto.getYearMonth() + "-01";
            LocalDate yearMonthLocalDate = LocalDate.parse(yearMonthStr);

            // 上个月的最后一天
            LocalDate lastDayOfPreviousMonth = yearMonthLocalDate.minusMonths(1).with(TemporalAdjusters.lastDayOfMonth());
            LocalDateTime startTime = lastDayOfPreviousMonth.atTime(23, 59, 59);

            // 下个月的第一天
            LocalDate firstDayOfNextMonth = yearMonthLocalDate.plusMonths(1).with(TemporalAdjusters.firstDayOfMonth());
            LocalDateTime endTime = firstDayOfNextMonth.atStartOfDay();

            dto.setStartTime(startTime);
            dto.setEndTime(endTime);
        }

        if(ObjectUtils.isNotEmpty(dto.getStatus()) && dto.getStatus() == 2 && ObjectUtils.isNotEmpty(dto.getBaseSemesterId())){
            // 获取学期
            BaseSemester baseSemester = baseSemesterMapper.selectById(dto.getBaseSemesterId());
            LocalDateTime startTime = baseSemester.getStartDate().toInstant()
                    .atZone(ZoneId.systemDefault()) // 使用系统默认时区
                    .toLocalDateTime();
            LocalDateTime endTime = baseSemester.getEndDate().toInstant()
                    .atZone(ZoneId.systemDefault()) // 使用系统默认时区
                    .toLocalDateTime();

            dto.setStartTime(startTime);
            dto.setEndTime(endTime);
        }

        if(ObjectUtils.isNotEmpty(dto.getStatus()) && dto.getStatus() == 3 && ObjectUtils.isNotEmpty(dto.getYear())){
            LocalDate yearLocalDate = LocalDate.of(dto.getYear(), 1, 1);
            LocalDate lastDayOfLastYear = yearLocalDate.minusYears(1).with(TemporalAdjusters.lastDayOfYear());
            LocalDateTime startTime = lastDayOfLastYear.atTime(23, 59, 59);

            LocalDate firstDayOfNextYear = yearLocalDate.plusYears(1).with(TemporalAdjusters.firstDayOfYear());
            LocalDateTime endTime = firstDayOfNextYear.atStartOfDay();

            dto.setStartTime(startTime);
            dto.setEndTime(endTime);
        }

        if(ObjectUtils.isEmpty(dto.getStatus())){
            LocalDate yearLocalDate = LocalDate.now();
            LocalDate lastDayOfLastYear = yearLocalDate.minusYears(1).with(TemporalAdjusters.lastDayOfYear());
            LocalDateTime startTime = lastDayOfLastYear.atTime(23, 59, 59);

            LocalDate firstDayOfNextYear = yearLocalDate.plusYears(1).with(TemporalAdjusters.firstDayOfYear());
            LocalDateTime endTime = firstDayOfNextYear.atStartOfDay();

            dto.setStartTime(startTime);
            dto.setEndTime(endTime);
        }

        // 教职工异动统计数据
        // 获取每个教职工异动最后一条数据
        String sql = "select t.user_id,\n" +
                "       t.new_job_state,\n" +
                "       t1.employ_type\n" +
                "from base_teacher_change_record t\n" +
                "         inner join (SELECT user_id,\n" +
                "                            MAX(id) AS max_id\n" +
                "                     FROM base_teacher_change_record a\n" +
                "                     where a.delete_mark = 0\n" +
                "                       and a.create_date between '" + dto.getStartTime() + "' and '" + dto.getEndTime()+ "'\n" +
                "                     GROUP BY user_id) t2 on t2.max_id = t.id\n" +
                "         left join base_teacher t1 on t1.user_id = t.user_id\n" +
                "where t.delete_mark = 0\n" +
                "  and t1.delete_mark = 0"
                ;
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        Map<String, Map<String, Object>> statics = new LinkedHashMap<>();
        for (Map<String, Object> oneRecord : list){
            String jobState = oneRecord.get("new_job_state").toString();
            if(jobState.equals("在职")){
                continue;
            }

            String employType = oneRecord.get("employ_type").toString();
            if(!employType.equals("FB1601") && !employType.equals("FB1605")){
                continue;
            }

            if(statics.containsKey(jobState)){
                Map<String, Object> oneMap = statics.get(jobState);
                if(employType.equals("FB1601")){
                    oneMap.put("count", (int)oneMap.get("count") + 1);
                }
                if(employType.equals("FB1605")){
                    oneMap.put("count1", (int)oneMap.get("count1") + 1);
                }
            }else {
                Map<String, Object> oneMap = new LinkedHashMap<>();
                oneMap.put("item", jobState);
                oneMap.put("count", 0);
                oneMap.put("count1", 0);
                if(employType.equals("FB1601")){
                    oneMap.put("count", (int)oneMap.get("count") + 1);
                }
                if(employType.equals("FB1605")){
                    oneMap.put("count1", (int)oneMap.get("count1") + 1);
                }
                statics.put(jobState, oneMap);
            }
        }
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        // 处理入职人数
        LambdaQueryWrapper<BaseTeacher> baseTeacherLambdaQueryWrapper = new LambdaQueryWrapper<>();
        baseTeacherLambdaQueryWrapper
                .ge(BaseTeacher::getJoinTime, formatter.format(dto.getStartTime()))
                .le(BaseTeacher::getJoinTime, formatter.format(dto.getEndTime()))
                .eq(BaseTeacher::getDeleteMark, DeleteMark.NODELETE.getCode())
                ;
        List<BaseTeacher> baseTeacherList = baseTeacherService.list(baseTeacherLambdaQueryWrapper);
        if(CollectionUtils.isNotEmpty(baseTeacherList)){
            for (BaseTeacher baseTeacher : baseTeacherList){
                Map<String, Object> oneMap = new LinkedHashMap<>();
                oneMap.put("item", "入职人数");
                oneMap.put("count", 0);
                oneMap.put("count1", 0);
                if("FB1601".equals(baseTeacher.getEmployType())){
                    oneMap.put("count", (int)oneMap.get("count") + 1);
                }
                if("FB1605".equals(baseTeacher.getEmployType())){
                    oneMap.put("count1", (int)oneMap.get("count1") + 1);
                }
                statics.put("入职人数", oneMap);
            }
        }

        teacherChangeStatisticsDetailVo.setTeacherChangeItemVoList(new ArrayList<>(statics.values()));
        return RT.ok(teacherChangeStatisticsDetailVo);
    }

    @GetMapping(value = "/student-statistics")
    @ApiOperation(value = "学生详情数据统计")
    @SaCheckPermission("datadetail:detail")
    @XjrLog(value = "学生详情数据统计", saveResponseData = true)
    public RT<StudnetStatisticsDetailVo> studentStatistics(@Valid StatisticsDetailDto dto) {
        StudnetStatisticsDetailVo result = new StudnetStatisticsDetailVo();

        String sql = "SELECT IFNULL(t3.name,'未填写') AS item ,COUNT(*) AS a_count FROM xjr_user t1" +
                " INNER JOIN base_student t2 ON t1.id = t2.user_id" +
                " INNER JOIN base_student_school_roll t4 ON t1.id = t4.user_id" +
                " LEFT JOIN xjr_dictionary_detail t3 ON t1.gender  = t3.code" +
                " AND t3.item_id = 2023000000000000004 and t3.delete_mark = 0" +
                " WHERE t1.delete_mark = 0" +
                " and t4.archives_status = 'FB2901'" +
                " GROUP BY t3.name";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountVo> genderList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            genderList.add(
                    new ItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                    }}
            );
        }
        result.setGenderList(genderList);

        sql = "SELECT IFNULL(t3.name,'未填写') AS item ,COUNT(*) AS a_count FROM xjr_user t1" +
                " INNER JOIN base_student_school_roll t2 ON t1.id = t2.user_id" +
                " LEFT JOIN xjr_dictionary_detail t3 ON t2.student_type = t3.code" +
                " AND t3.item_id = 2023000000000000028 and t3.delete_mark = 0" +
                " WHERE t1.delete_mark = 0" +
                " and t2.archives_status = 'FB2901'" +
                " GROUP BY t3.name";
        list = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountVo> studentTypeList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            studentTypeList.add(
                    new ItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                    }}
            );
        }
        result.setStudentTypeList(studentTypeList);

        List<BaseStudentUserPageVo> studentList = studentService.getStudentList(new BaseStudentUserPageDto());
        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());

        sql = "SELECT name AS item,(" +
                " SELECT COUNT(*) FROM xjr_user t1" +
                " INNER JOIN base_student_school_roll t2 ON t1.id = t2.user_id" +
                " WHERE t1.delete_mark = 0 AND t2.grade_id = base_grade.id" +
                " and t2.archives_status = 'FB2901') AS a_count FROM base_grade" +
                " WHERE delete_mark = 0 AND status = 1";
        if (dto.getGradeId() != null) {
            sql += " id = " + dto.getGradeId();
        }
        sql += " ORDER BY name DESC LIMIT 4";
        list = SqlRunnerAdapter.db().selectList(sql);
        Collections.reverse(list);
        List<ItemCountVo> gradeList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            int aCount = Integer.parseInt(objectMap.get("a_count").toString());
            if (aCount == 0) {
                continue;
            }
            gradeList.add(
                    new ItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(aCount);
                    }}
            );
        }
        result.setGradeList(gradeList);

        sql = "SELECT NAME AS item,(\n" +
                "SELECT COUNT(*) FROM xjr_user t1\n" +
                "INNER JOIN base_student_school_roll t2 ON t1.id = t2.user_id\n" +
                "INNER JOIN base_class t3 ON t2.class_id = t3.id\n" +
                "WHERE t1.delete_mark = 0 AND t3.org_id = xjr_department.id\n" +
                "AND t2.archives_status = 'FB2901' AND t1.gender = 'SB10001') AS a_count,\n" +
                "(\n" +
                "SELECT COUNT(*) FROM xjr_user t1\n" +
                "INNER JOIN base_student_school_roll t2 ON t1.id = t2.user_id\n" +
                "INNER JOIN base_class t3 ON t2.class_id = t3.id\n" +
                "WHERE t1.delete_mark = 0 AND t3.org_id = xjr_department.id\n" +
                "AND t2.archives_status = 'FB2901' AND t1.gender = 'SB10002') AS b_count FROM xjr_department\n" +
                "WHERE delete_mark = 0 AND is_major = 1 ORDER BY NAME DESC";
        list = SqlRunnerAdapter.db().selectList(sql);
        Collections.reverse(list);
        List<StudentReportRecordItemVo> deptList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            deptList.add(
                    new StudentReportRecordItemVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                        setCount2(Integer.parseInt(objectMap.get("b_count").toString()));
                    }}
            );
        }
        result.setDeptList(deptList);

        return RT.ok(result);
    }

    @GetMapping(value = "/subscription-statistics")
    @ApiOperation(value = "物品申购详情数据统计")
    @SaCheckPermission("datadetail:detail")
    @XjrLog(value = "物品申购详情数据统计", saveResponseData = true)
    public RT<SubscriptionStatisticsDetailVo> subscriptionStatistics(@Valid StatisticsDetailDto dto) {
        SubscriptionStatisticsDetailVo result = new SubscriptionStatisticsDetailVo();
        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);
        }
        List<WfSubscription> list = subscriptionService.list(
                new MPJLambdaWrapper<WfSubscription>()
                        .select(WfSubscription::getId)
                        .select(WfSubscription.class, x -> VoToColumnUtil.fieldsToColumns(WfSubscription.class).contains(x.getProperty()))
                        .innerJoin(WorkflowFormRelation.class, WorkflowFormRelation::getFormKeyValue, WfSubscription::getId)
                        .eq(WorkflowFormRelation::getCurrentState, HistoricProcessInstance.STATE_COMPLETED)
                        .between((startTime != null && endTime != null), WfSubscription::getShenQingRiQi4752, startTime, endTime)
        );

        result.setAllCount(list.size());

        double totalAmount = list.stream().filter(x -> x.getTotalAmount() != null && x.getStatus() == 1).mapToDouble(WfSubscription::getTotalAmount).sum();
        result.setTotalAmount(totalAmount);

        int adoptCount = list.stream().filter(x -> x.getStatus() == 1).collect(Collectors.toList()).size();
        result.setAdoptCount(adoptCount);

        int pendingCount = list.stream().filter(x -> x.getStatus() == null || x.getStatus() == 0).collect(Collectors.toList()).size();
        result.setPendingCount(pendingCount);

        String sql = "SELECT IFNULL(t3.name, '未填写') AS item,COUNT(t1.id) AS a_count,SUM(t1.estimated_unit_price * t1.amount) as a_sum FROM wf_subscription_list t1" +
                " INNER JOIN wf_subscription t2 ON t1.parent_id = t2.id" +
                " INNER JOIN xjr_workflow_form_relation t4 ON t1.id = t4.form_key_value" +
                " LEFT JOIN xjr_dictionary_detail t3 ON t1.item_type = t3.code" +
                " AND t3.item_id = 1752140413593518081" +
                " WHERE t4.current_state = 'COMPLETED'";
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        if (startTime != null && endTime != null) {
            sql += " and t2.shen_qing_ri_qi4752 between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        sql += " GROUP BY t3.name";
        List<Map<String, Object>> datalist = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountVo> categoryCountList = new ArrayList<>();
        List<ItemDoubleVo> categoryAmountList = new ArrayList<>();
        for (Map<String, Object> objectMap : datalist) {
            categoryCountList.add(
                    new ItemCountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                    }}
            );

            categoryAmountList.add(
                    new ItemDoubleVo() {{
                        setItem(objectMap.get("item").toString());
                        setAmount(Double.parseDouble(objectMap.get("a_sum").toString()));
                    }}
            );
        }
        result.setCategoryCountList(categoryCountList);
        result.setCategoryAmountList(categoryAmountList);

        sql = "SELECT IFNULL(t3.name, '未填写') AS item,COUNT(t1.id) AS a_count,SUM(t1.estimated_unit_price * t1.amount) as a_sum FROM wf_subscription_list t1" +
                " INNER JOIN wf_subscription t2 ON t1.parent_id = t2.id" +
                " INNER JOIN xjr_workflow_form_relation t4 ON t1.id = t4.form_key_value" +
                " LEFT JOIN xjr_department t3 ON t2.application_department = t3.id" +
                " WHERE t4.current_state = 'COMPLETED'";
        if (startTime != null && endTime != null) {
            sql += " and t2.shen_qing_ri_qi4752 between '" + startTime.format(formatter) + "' and '" + endTime.format(formatter) + "'";
        }
        sql += " GROUP BY t3.name";
        datalist = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountAmountVo> deptList = new ArrayList<>();
        for (Map<String, Object> objectMap : datalist) {
            deptList.add(
                    new ItemCountAmountVo() {{
                        setItem(objectMap.get("item").toString());
                        setCount(Integer.parseInt(objectMap.get("a_count").toString()));
                        setAmount(Double.parseDouble(objectMap.get("a_sum").toString()));
                    }}
            );
        }
        result.setDeptList(deptList);
        return RT.ok(result);
    }

    @GetMapping(value = "/course-statistics")
    @ApiOperation(value = "课表详情数据统计")
    @SaCheckPermission("datadetail:detail")
    @XjrLog(value = "课表详情数据统计", saveResponseData = true)
    public RT<CourseStatisticsDetailVo> courseStatistics(@Valid StatisticsDetailDto dto) {
        String startDate = null;
        String endDate = null;
        if(dto.getStartDate() != null &&  dto.getEndDate() != null){
            startDate = dto.getStartDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            endDate = dto.getEndDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        }
        CourseStatisticsDetailVo result = new CourseStatisticsDetailVo();
        String sql = "SELECT t1.id, t1.name,t4.name AS dept_name,( " +
                " SELECT GROUP_CONCAT(DISTINCT(course_name)) FROM course_table WHERE FIND_IN_SET(t1.id, teacher_id) > 0 ";
        if(startDate != null){
            sql += " and schedule_date between '" + startDate + "' and '" + endDate + "'";
        }
        sql += " ) AS course_names,(" +
                "SELECT COUNT(*) FROM course_table WHERE FIND_IN_SET(t1.id, teacher_id) > 0 ";
        if(startDate != null){
            sql += " and schedule_date between '" + startDate + "' and '" + endDate + "'";
        }
        sql += " ) AS course_count FROM xjr_user t1" +
            " INNER JOIN base_teacher t2 ON t1.id = t2.user_id" +
            " INNER JOIN xjr_user_dept_relation t3 ON t1.id = t3.user_id" +
            " INNER JOIN xjr_department t4 ON t3.dept_id = t4.id" +
            " WHERE t1.delete_mark = 0 AND t4.is_major = 1";
        sql += " ORDER BY course_count DESC LIMIT 5";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        List<CourseCountListVo> courseCountList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            CourseCountListVo listVo = SqlRunnerAdapterUtil.convertMapToEntity(objectMap, CourseCountListVo.class);
            courseCountList.add(listVo);
        }
        result.setCourseCountList(courseCountList);

        sql = "SELECT count(*) FROM course_table t1" +
            " INNER JOIN base_teacher t2 ON FIND_IN_SET(t2.user_id, t1.teacher_id) > 0" +
            " INNER JOIN xjr_user_dept_relation t3 ON t2.user_id = t3.user_id" +
            " INNER JOIN xjr_department t4 ON t4.id = t3.dept_id" +
            " WHERE t4.delete_mark = 0 AND t2.delete_mark = 0" +
            " AND t4.is_major = 1";
        if(startDate != null){
            sql += " and t1.schedule_date between '" + startDate + "' and '" + endDate + "'";
        }
        long allCourseCount = SqlRunnerAdapter.db().selectCount(sql);


        sql = "SELECT name,(" +
            " SELECT COUNT(*) FROM course_table t1" +
            " INNER JOIN base_teacher t2 ON FIND_IN_SET(t2.user_id, t1.teacher_id) > 0" +
            " INNER JOIN xjr_user_dept_relation t3 ON t2.user_id = t3.user_id" +
            " WHERE t3.dept_id = xjr_department.id";
        if(startDate != null){
            sql += " and t1.schedule_date between '" + startDate + "' and '" + endDate + "'";
        }
        sql += " ) AS course_count FROM xjr_department WHERE is_major = 1";
        list = SqlRunnerAdapter.db().selectList(sql);
        List<ItemCountRatioVo> deptCourseList = new ArrayList<>();
        for (Map<String, Object> objectMap : list) {
            int courseCount = Integer.parseInt(objectMap.get("course_count").toString());
            //计算出勤率
            BigDecimal divide = BigDecimal.valueOf(courseCount).divide(BigDecimal.valueOf(allCourseCount), 4, RoundingMode.HALF_UP);
            deptCourseList.add(
                    new ItemCountRatioVo() {{
                        setItem(objectMap.get("name").toString());
                        setCount(courseCount);
                        setRatio(divide.doubleValue());
                    }}
            );
        }
        result.setDeptCourseList(deptCourseList);
        return RT.ok(result);
    }

    @GetMapping(value = "/room-statistics")
    @ApiOperation(value = "寝室统计")
    @SaCheckPermission("databoard:detail")
    @XjrLog(value = "寝室统计", saveResponseData = true)
    public RT<RoomStatisticsDetailVo> roomStatistics(@Valid StatisticsDetailDto dto) {
        RoomStatisticsDetailVo result = new RoomStatisticsDetailVo();
        String sql = "SELECT t2.id, t3.user_id,t4.name AS grade_name, t6.name AS dept_name,t7.gender FROM room t1" +
                " INNER JOIN room_bed t2 ON t1.id = t2.room_id" +
                " LEFT JOIN base_student_school_roll t3 ON t2.student_user_id = t3.user_id" +
                " LEFT JOIN base_grade t4 ON t3.grade_id = t4.id AND t4.status = 1" +
                " LEFT JOIN base_class t5 ON t3.class_id = t5.id" +
                " LEFT JOIN xjr_department t6 ON t5.org_id = t6.id" +
                " LEFT JOIN xjr_user t7 ON t3.user_id = t7.id" +
                " WHERE t1.delete_mark = 0 AND t2.delete_mark = 0";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);

        Integer allCount = list.size();
        long emptyCount = list.stream().filter(x -> ObjectUtil.isNull(x.get("user_id"))).count();
        BigDecimal emptyRatio = BigDecimal.valueOf(emptyCount).divide(BigDecimal.valueOf(allCount), 4, RoundingMode.HALF_UP);
        result.setEmptyRatio(emptyRatio.doubleValue() + "");

        long notEmptyCount = list.stream().filter(x -> ObjectUtil.isNotNull(x.get("user_id"))).count();
        BigDecimal notEmptyRatio = BigDecimal.valueOf(notEmptyCount).divide(BigDecimal.valueOf(allCount), 4, RoundingMode.HALF_UP);
        result.setNotEmptyRatio(notEmptyRatio.doubleValue() + "");

        Map<String, List<Map<String, Object>>> gradeMap = list.stream().filter(x -> ObjectUtil.isNotNull(x.get("grade_name"))).collect(Collectors.groupingBy(x -> x.get("grade_name").toString()));
        List<ItemCount2Vo> gradeList = new ArrayList<>();
        for (String gradeName : gradeMap.keySet()) {
            List<Map<String, Object>> gradeDataList = gradeMap.get(gradeName);
            Map<String, Long> genderMap = gradeDataList.stream().filter(x -> ObjectUtil.isNotNull(x.get("gender"))).collect(Collectors.groupingBy(x -> x.get("gender").toString(), Collectors.counting()));
            ItemCount2Vo gradeVo = new ItemCount2Vo();
            gradeVo.setItem(gradeName);
            if(genderMap.get(GenderDictionaryEnum.MALE.getCode()) != null){
                gradeVo.setCount(genderMap.get(GenderDictionaryEnum.MALE.getCode()).intValue());
            }
            if(genderMap.get(GenderDictionaryEnum.FEMALE.getCode()) != null){
                gradeVo.setCount2(genderMap.get(GenderDictionaryEnum.FEMALE.getCode()).intValue());
            }

            gradeList.add(gradeVo);
        }
        Collections.reverse(gradeList);
        result.setGradeList(gradeList);

        Map<String, List<Map<String, Object>>> deptMap = list.stream().filter(x -> ObjectUtil.isNotNull(x.get("dept_name"))).collect(Collectors.groupingBy(x -> x.get("dept_name").toString()));
        List<ItemCount2Vo> deptList = new ArrayList<>();
        for (String deptName : deptMap.keySet()) {
            List<Map<String, Object>> deptDataList = deptMap.get(deptName);
            Map<String, Long> genderMap = deptDataList.stream().filter(x -> ObjectUtil.isNotNull(x.get("gender"))).collect(Collectors.groupingBy(x -> x.get("gender").toString(), Collectors.counting()));
            ItemCount2Vo deptVo = new ItemCount2Vo();
            deptVo.setItem(deptName);
            if(genderMap.get(GenderDictionaryEnum.MALE.getCode()) != null){
                deptVo.setCount(genderMap.get(GenderDictionaryEnum.MALE.getCode()).intValue());
            }
            if(genderMap.get(GenderDictionaryEnum.FEMALE.getCode()) != null){
                deptVo.setCount2(genderMap.get(GenderDictionaryEnum.FEMALE.getCode()).intValue());
            }

            deptList.add(deptVo);
        }
        result.setDeptList(deptList);
        return RT.ok(result);
    }

}