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.github.yulichang.toolkit.MPJWrappers;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
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.databoard.dto.StatisticsDetailDto;
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.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.StudnetStatisticsDetailVo;
import com.xjrsoft.module.databoard.vo.SubscriptionStatisticsDetailVo;
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.ITeacherbaseManagerService;
import com.xjrsoft.module.teacher.vo.XjrUserPageVo;
import com.xjrsoft.module.workflow.constant.WorkflowConstant;
import com.xjrsoft.module.workflow.entity.WorkflowExtra;
import com.xjrsoft.module.workflow.entity.WorkflowFormRelation;
import com.xjrsoft.module.workflow.entity.WorkflowSchema;
import com.xjrsoft.module.workflow.service.IWorkflowExtraService;
import com.xjrsoft.module.workflow.utils.WorkFlowUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import org.camunda.bpm.engine.HistoryService;
import org.camunda.bpm.engine.history.HistoricProcessInstance;
import org.camunda.bpm.engine.history.HistoricProcessInstanceQuery;
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.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashSet;
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 HistoryService historyService;
    private final IWorkflowExtraService extraService;
    private final ITeacherbaseManagerService teacherService;
    private final IBaseStudentService studentService;
    private final IWfSubscriptionService subscriptionService;

    @GetMapping(value = "/process-statistics")
    @ApiOperation(value="流程统计详情")
    @SaCheckPermission("datadetail:detail")
    public RT<ProcessStatisticsDetailVo> processStatistics(@Valid StatisticsDetailDto dto){
        HistoricProcessInstanceQuery instanceQuery = historyService.createHistoricProcessInstanceQuery();
        if(dto.getUserId() != null){
            instanceQuery.variableValueEquals(WorkflowConstant.PROCESS_START_USER_ID_KEY, dto.getUserId());
        }
        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);
        }
        if (!ObjectUtil.isNull(dto.getStartDate())) {
            instanceQuery.startedAfter(WorkFlowUtil.getStartOfDay(dto.getStartDate()));
        }
        if (!ObjectUtil.isNull(dto.getEndDate())) {
            instanceQuery.startedBefore(WorkFlowUtil.getEndOfDay(dto.getEndDate()));
        }

        List<WorkflowExtra> workflowExtras = extraService.list(
                new MPJLambdaWrapper<WorkflowExtra>()
                        .between((startTime != null && endTime != null), WorkflowExtra::getLaunchTime, startTime, endTime)
                        .select(WorkflowExtra::getId)
                        .select(WorkflowExtra.class, x -> VoToColumnUtil.fieldsToColumns(WorkflowExtra.class).contains(x.getProperty()))
                        .leftJoin(WorkflowSchema.class, WorkflowSchema::getId, WorkflowExtra::getSchemaId)
                        .leftJoin(DictionaryDetail.class, DictionaryDetail::getId, WorkflowSchema::getCategory)
        );


        ProcessStatisticsDetailVo result = new ProcessStatisticsDetailVo();
        List<HistoricProcessInstance> list = instanceQuery.list();
        List<WorkflowExtra> allCountList = new ArrayList<>();
        for (HistoricProcessInstance historicProcessInstance : list) {
            workflowExtras.stream()
                    .filter(e -> e.getProcessId().equals(historicProcessInstance.getId()))
                    .max(Comparator.comparing(WorkflowExtra::getStartTime))
                    .ifPresent(e -> {
                        allCountList.add(e);
                    });
        }
        result.setAllCount(Long.parseLong(allCountList.size() + ""));

        List<HistoricProcessInstance> finished = historyService.createHistoricProcessInstanceQuery().finished().list();
        List<WorkflowExtra> completeCountList = new ArrayList<>();
        for (HistoricProcessInstance historicProcessInstance : finished) {
            workflowExtras.stream()
                    .filter(e -> e.getProcessId().equals(historicProcessInstance.getId()) && e.getEndTime() != null)
                    .max(Comparator.comparing(WorkflowExtra::getStartTime))
                    .ifPresent(e -> {
                        completeCountList.add(e);
                    });
        }
        result.setCompleteCount(Long.parseLong(completeCountList.size() + ""));

        List<HistoricProcessInstance> unfinished = historyService.createHistoricProcessInstanceQuery().unfinished().list();
        List<WorkflowExtra> uncompleteCountList = new ArrayList<>();
        for (HistoricProcessInstance historicProcessInstance : unfinished) {
            workflowExtras.stream()
                    .filter(e -> e.getProcessId().equals(historicProcessInstance.getId()))
                    .max(Comparator.comparing(WorkflowExtra::getStartTime))
                    .ifPresent(e -> {
                        uncompleteCountList.add(e);
                    });
        }
        result.setUncompleteCount(Long.parseLong(uncompleteCountList.size() + ""));

        Map<String, Long> finishedMap = completeCountList.stream()
                .collect(Collectors.groupingBy(WorkflowExtra::getSchemaName, Collectors.counting()));
        Map<String, Long> unfinishedMap = uncompleteCountList.stream()
                .collect(Collectors.groupingBy(WorkflowExtra::getSchemaName, Collectors.counting()));

        Set<String> itemSet = new HashSet<>();
        itemSet.addAll(finishedMap.keySet());
        itemSet.addAll(unfinishedMap.keySet());
        List<DistributionVo> distributionList = new ArrayList();
        for (String item : itemSet) {
            Long uncompleteCount = unfinishedMap.get(item) == null?0L:unfinishedMap.get(item);
            Long completeCount = finishedMap.get(item) == null?0L:finishedMap.get(item);
            distributionList.add(
                    new DistributionVo(){{
                        setItem(item);
                        setCompleteCount(completeCount);
                        setUncompleteCount(uncompleteCount);
                    }}
            );
        }
        result.setDistributionList(distributionList);
        List<DurationVo> durationList = new ArrayList<>();

        List<Long> secondList = new ArrayList<>();
        completeCountList.stream().filter(el -> el.getEndTime() != null && el.getStartTime() != null).forEach(el -> {
            Duration diff = Duration.between(el.getStartTime(), el.getEndTime());
            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")
    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")
    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 xjr_dictionary_detail t2 ON t1.education  = t2.code" +
                " WHERE t1.delete_mark = 0 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 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_way  = t3.code AND t3.item_id = 2023000000000000016" +
            " WHERE t1.delete_mark = 0 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 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 = "/student-statistics")
    @ApiOperation(value="学生详情数据统计")
    @SaCheckPermission("datadetail:detail")
    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")
    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")
    public RT<CourseStatisticsDetailVo> courseStatistics(@Valid StatisticsDetailDto dto) {
        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 teacher_id like concat('%', t1.id,'%') ) AS course_names," +
                " (SELECT COUNT(*) FROM course_table WHERE teacher_id like concat('%', t1.id,'%')) 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" +
                " 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 t1.teacher_id like concat('%', t2.user_id,'%')" +
            " 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";
        long allCourseCount = SqlRunnerAdapter.db().selectCount(sql);
        sql = "SELECT name,(" +
            " SELECT COUNT(*) FROM course_table t1" +
            " INNER JOIN base_teacher t2 ON t1.teacher_id like concat('%', t2.user_id,'%')" +
            " INNER JOIN xjr_user_dept_relation t3 ON t2.user_id = t3.user_id" +
            " WHERE t3.dept_id = xjr_department.id" +
            " ) 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);
    }

}