api/src/main/java/com/xjrsoft/module/ledger/controller/LedgerStatisticsController.java

package com.xjrsoft.module.ledger.controller;

import cn.dev33.satoken.annotation.SaCheckPermission;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.xjrsoft.common.model.result.RT;
import com.xjrsoft.common.mybatis.SqlRunnerAdapter;
import com.xjrsoft.common.page.ConventPage;
import com.xjrsoft.common.page.PageOutput;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.module.ledger.dto.LedgerStatisticsPageDto;
import com.xjrsoft.module.ledger.vo.LedgerStatisticsLeaveExcelVo;
import com.xjrsoft.module.ledger.vo.LedgerStatisticsLeaveVo;
import com.xjrsoft.module.ledger.vo.LedgerStatisticsListenExcelVo;
import com.xjrsoft.module.ledger.vo.LedgerStatisticsListenVo;
import com.xjrsoft.module.ledger.vo.LedgerStatisticsOvertimeExcelVo;
import com.xjrsoft.module.ledger.vo.LedgerStatisticsOvertimeVo;
import com.xjrsoft.module.organization.entity.Department;
import com.xjrsoft.module.organization.entity.User;
import com.xjrsoft.module.organization.entity.UserDeptRelation;
import com.xjrsoft.module.organization.service.IUserDeptRelationService;
import com.xjrsoft.module.organization.service.IUserService;
import com.xjrsoft.module.organization.vo.UserPageVo;
import com.xjrsoft.module.teacher.entity.BaseTeacher;
import com.xjrsoft.module.teacher.entity.WfTeacherleave;
import com.xjrsoft.module.teacher.service.IWfTeacherleaveService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.validation.Valid;
import java.io.ByteArrayOutputStream;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
* @title: 台账统计
* @Author dzx
* @Date: 2024年11月29日
* @Version 1.0
*/
@RestController
@RequestMapping("/ledger" + "/ledgerStatistics")
@Api(value = "/ledger"  + "/ledgerStatistics",tags = "台账统计代码")
@AllArgsConstructor
public class LedgerStatisticsController {


    private final IUserService userService;
    private final IWfTeacherleaveService teacherleaveService;
    private final IUserDeptRelationService deptRelationService;

    @GetMapping(value = "/teacher-leave-page")
    @ApiOperation(value="教师请假统计(分页)")
    @SaCheckPermission("ledgerstatistics:detail")
    public RT<PageOutput<LedgerStatisticsLeaveVo>> teacherLeavePage(@Valid LedgerStatisticsPageDto dto){
        List<Long> userIdList = new ArrayList<>();
        if(StrUtil.isNotEmpty(dto.getDeptName())){
            List<UserDeptRelation> userList = deptRelationService.list(
                    new MPJLambdaWrapper<UserDeptRelation>()
                            .select(UserDeptRelation::getId)
                            .select(UserDeptRelation::getDeptId)
                            .select(UserDeptRelation::getUserId)
                            .innerJoin(Department.class, Department::getId, UserDeptRelation::getDeptId)
                            .like(StrUtil.isNotEmpty(dto.getDeptName()), Department::getName, dto.getDeptName())
            );
            userIdList.addAll(userList.stream().map(UserDeptRelation::getUserId).collect(Collectors.toList()));
        }

        LocalDateTime startTime = null;
        LocalDateTime endTime = null;
        if(dto.getStartDate() != null && dto.getEndDate() != null){
            startTime = dto.getStartDate().atTime(0,0,0);
            endTime = dto.getEndDate().atTime(23,59,59);
        }
        Map<String, List<WfTeacherleave>> userLeaveMap = teacherleaveService.getUserLeaveList(startTime, endTime, userIdList);

        List<String> userIds = new ArrayList<>(userLeaveMap.keySet());
        if(userIds.isEmpty()){
            return RT.ok(new PageOutput<>());
        }

        IPage<LedgerStatisticsLeaveVo> userPage = userService.selectJoinListPage(ConventPage.getPage(dto), LedgerStatisticsLeaveVo.class,
                new MPJLambdaWrapper<User>()
                        .disableSubLogicDel()
                        .select(User::getId)
                        .select(User.class, x -> VoToColumnUtil.fieldsToColumns(UserPageVo.class).contains(x.getProperty()))
                        .select(" (SELECT GROUP_CONCAT(t1.name) FROM xjr_department t1" +
                                " INNER JOIN xjr_user_dept_relation t2 ON t1.id = t2.dept_id" +
                                " WHERE t1.delete_mark = 0 AND t2.user_id = t.id) as dept_name")
                        .innerJoin(BaseTeacher.class, BaseTeacher::getUserId, User::getId)
                        .like(StrUtil.isNotEmpty(dto.getName()), User::getName, dto.getName())
                        .like(StrUtil.isNotEmpty(dto.getUserName()), User::getUserName, dto.getUserName())
                        .in(User::getId, userIds)
                        .orderByAsc(User::getId)
        );

        for (LedgerStatisticsLeaveVo record : userPage.getRecords()) {
            List<WfTeacherleave> wfTeacherleaves = userLeaveMap.get(record.getId().toString());
            if(wfTeacherleaves == null || wfTeacherleaves.isEmpty()){
                continue;
            }
            double value = wfTeacherleaves.stream().mapToDouble(WfTeacherleave::getLeaveDays).sum();
            boolean isInteger = (value % 1) == 0;
            DecimalFormat df = new DecimalFormat(isInteger ? "0" : "#.##");
            String formattedValue = df.format(value);
            record.setLeaveDays(formattedValue);
        }

        PageOutput<LedgerStatisticsLeaveVo> pageOutput = ConventPage.getPageOutput(userPage, LedgerStatisticsLeaveVo.class);
        return RT.ok(pageOutput);
    }

    @PostMapping(value = "/teacher-leave-export-query")
    @ApiOperation(value="教师请假统计-导出")
    @SaCheckPermission("ledgerstatistics:detail")
    public ResponseEntity<byte[]> teacherLeaveExportQuery(@Valid @RequestBody LedgerStatisticsPageDto dto){
        List<Long> userIdList = new ArrayList<>();
        if(StrUtil.isNotEmpty(dto.getDeptName())){
            List<UserDeptRelation> userList = deptRelationService.list(
                    new MPJLambdaWrapper<UserDeptRelation>()
                            .select(UserDeptRelation::getId)
                            .select(UserDeptRelation::getDeptId)
                            .select(UserDeptRelation::getUserId)
                            .innerJoin(Department.class, Department::getId, UserDeptRelation::getDeptId)
                            .like(StrUtil.isNotEmpty(dto.getDeptName()), Department::getName, dto.getDeptName())
            );
            userIdList.addAll(userList.stream().map(UserDeptRelation::getUserId).collect(Collectors.toList()));
        }

        LocalDateTime startTime = null;
        LocalDateTime endTime = null;
        if(dto.getStartDate() != null && dto.getEndDate() != null){
            startTime = dto.getStartDate().atTime(0,0,0);
            endTime = dto.getEndDate().atTime(23,59,59);
        }
        Map<String, List<WfTeacherleave>> userLeaveMap = teacherleaveService.getUserLeaveList(startTime, endTime, userIdList);

        List<String> userIds = new ArrayList<>(userLeaveMap.keySet());
        if(userIds.isEmpty()){
            List<LedgerStatisticsLeaveExcelVo> list = new ArrayList<>();
            ByteArrayOutputStream bot = new ByteArrayOutputStream();
            EasyExcel.write(bot, LedgerStatisticsLeaveExcelVo.class).automaticMergeHead(false).excelType(ExcelTypeEnum.XLSX).sheet().doWrite(list);

            return RT.fileStream(bot.toByteArray(), "teacher-leave" + ExcelTypeEnum.XLSX.getValue());
        }

        List<LedgerStatisticsLeaveVo> list = userService.selectJoinList(LedgerStatisticsLeaveVo.class,
                new MPJLambdaWrapper<User>()
                        .disableSubLogicDel()
                        .select(User::getId)
                        .select(User.class, x -> VoToColumnUtil.fieldsToColumns(UserPageVo.class).contains(x.getProperty()))
                        .select(" (SELECT GROUP_CONCAT(t1.name) FROM xjr_department t1" +
                                " INNER JOIN xjr_user_dept_relation t2 ON t1.id = t2.dept_id" +
                                " WHERE t1.delete_mark = 0 AND t2.user_id = t.id) as dept_name")
                        .innerJoin(BaseTeacher.class, BaseTeacher::getUserId, User::getId)
                        .like(StrUtil.isNotEmpty(dto.getName()), User::getName, dto.getName())
                        .like(StrUtil.isNotEmpty(dto.getUserName()), User::getUserName, dto.getUserName())
                        .in(User::getId, userIds)
                        .orderByAsc(User::getId)
        );
        List<LedgerStatisticsLeaveExcelVo> dataList = new ArrayList<>();

        int sortCode = 1;
        for (LedgerStatisticsLeaveVo record : list) {
            List<WfTeacherleave> wfTeacherleaves = userLeaveMap.get(record.getId().toString());
            if(wfTeacherleaves == null || wfTeacherleaves.isEmpty()){
                continue;
            }
            double value = wfTeacherleaves.stream().mapToDouble(WfTeacherleave::getLeaveDays).sum();
            boolean isInteger = (value % 1) == 0;
            DecimalFormat df = new DecimalFormat(isInteger ? "0" : "#.##");
            String formattedValue = df.format(value);
            record.setLeaveDays(formattedValue);
            record.setSortCode(sortCode);
            LedgerStatisticsLeaveExcelVo bean = BeanUtil.toBean(record, LedgerStatisticsLeaveExcelVo.class);
            dataList.add(bean);
            sortCode ++;
        }

        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        EasyExcel.write(bot, LedgerStatisticsLeaveExcelVo.class).automaticMergeHead(false).excelType(ExcelTypeEnum.XLSX).sheet().doWrite(dataList);

        return RT.fileStream(bot.toByteArray(), "teacher-leave" + ExcelTypeEnum.XLSX.getValue());
    }

    @GetMapping(value = "/teacher-overtime-page")
    @ApiOperation(value="教师加班统计(分页)")
    @SaCheckPermission("ledgerstatistics:detail")
    public RT<PageOutput<LedgerStatisticsOvertimeVo>> teacherOvertimePage(@Valid LedgerStatisticsPageDto dto){

        List<Long> userIdList = new ArrayList<>();
        if(StrUtil.isNotEmpty(dto.getDeptName())){
            List<UserDeptRelation> list = deptRelationService.list(
                    new MPJLambdaWrapper<UserDeptRelation>()
                            .select(UserDeptRelation::getId)
                            .select(UserDeptRelation::getDeptId)
                            .select(UserDeptRelation::getUserId)
                            .innerJoin(Department.class, Department::getId, UserDeptRelation::getDeptId)
                            .like(StrUtil.isNotEmpty(dto.getDeptName()), Department::getName, dto.getDeptName())
            );
            userIdList.addAll(list.stream().map(UserDeptRelation::getUserId).collect(Collectors.toList()));
        }

        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        String sql = "SELECT t4.teacher_user_id,SUM(t.total_days) as total_days FROM wf_overtime t" +
                " LEFT JOIN xjr_user t1 ON t1.id = t.user_id" +
                " LEFT JOIN xjr_user t3 ON t3.id = t.leader_in_charge" +
                " LEFT JOIN xjr_workflow_form_relation t2 ON t.id = t2.form_key_value" +
                " LEFT JOIN wf_overtime_teacher t4 ON t.id = t4.wf_overtime_id" +
                " WHERE t2.current_state = 'COMPLETED'";
        if(dto.getStartDate() != null && dto.getEndDate() != null){
            String startTime = dto.getStartDate().atTime(0,0,0).format(dtf);
            String endTime = dto.getEndDate().atTime(23,59,59).format(dtf);
            sql += " AND (" +
                "   (t.overtime_start_time BETWEEN '" + startTime + "' and '" + endTime + "')" +
                "   OR (t.overtime_end_time BETWEEN '" + startTime + "' and '" + endTime + "')" +
                "   OR (t.overtime_start_time > '" + startTime + "' and '" + endTime + "' > t.overtime_end_time)" +
                "   OR ('" + startTime + "' > t.overtime_start_time and t.overtime_end_time > '" + endTime + "')" +
                ")";
        }
        if(!userIdList.isEmpty()){
            sql = " AND t.teacher_user_id in (" + userIdList.toString().replace("[", "").replace("]", "") + ")";
        }
        sql += " GROUP BY t4.teacher_user_id";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        Map<Long, String> userLeaveMap = list.stream().filter(x -> x.get("total_days") != null && x.get("teacher_user_id") != null).collect(
                Collectors.toMap(x -> Long.parseLong(x.get("teacher_user_id").toString()), x -> x.get("total_days").toString())
        );

        List<Long> userIds = new ArrayList<>(userLeaveMap.keySet());
        if(userIds.isEmpty()){
            return RT.ok(new PageOutput<>());
        }


        IPage<LedgerStatisticsOvertimeVo> userPage = userService.selectJoinListPage(ConventPage.getPage(dto), LedgerStatisticsOvertimeVo.class,
                new MPJLambdaWrapper<User>()
                        .disableSubLogicDel()
                        .select(User::getId)
                        .select(User.class, x -> VoToColumnUtil.fieldsToColumns(UserPageVo.class).contains(x.getProperty()))
                        .select(" (SELECT GROUP_CONCAT(t1.name) FROM xjr_department t1" +
                                " INNER JOIN xjr_user_dept_relation t2 ON t1.id = t2.dept_id" +
                                " WHERE t1.delete_mark = 0 AND t2.user_id = t.id) as dept_name")
                        .innerJoin(BaseTeacher.class, BaseTeacher::getUserId, User::getId)
                        .like(StrUtil.isNotEmpty(dto.getName()), User::getName, dto.getName())
                        .like(StrUtil.isNotEmpty(dto.getUserName()), User::getUserName, dto.getUserName())
                        .in(User::getId, userIds)
                        .orderByAsc(User::getId)
        );

        for (LedgerStatisticsOvertimeVo record : userPage.getRecords()) {
            if(!userLeaveMap.containsKey(record.getId())){
                continue;
            }
            double value = Double.parseDouble(userLeaveMap.get(record.getId()));
            boolean isInteger = (value % 1) == 0;
            DecimalFormat df = new DecimalFormat(isInteger ? "0" : "#.##");
            String formattedValue = df.format(value);
            record.setTotalDays(formattedValue);
        }

        PageOutput<LedgerStatisticsOvertimeVo> pageOutput = ConventPage.getPageOutput(userPage, LedgerStatisticsOvertimeVo.class);
        return RT.ok(pageOutput);
    }

    @PostMapping(value = "/teacher-overtime-export-query")
    @ApiOperation(value="教师加班统计-导出")
    @SaCheckPermission("ledgerstatistics:detail")
    public ResponseEntity<byte[]> teacherOvertimeExportQuery(@Valid @RequestBody LedgerStatisticsPageDto dto){
        List<Long> userIdList = new ArrayList<>();
        if(StrUtil.isNotEmpty(dto.getDeptName())){
            List<UserDeptRelation> list = deptRelationService.list(
                    new MPJLambdaWrapper<UserDeptRelation>()
                            .select(UserDeptRelation::getId)
                            .select(UserDeptRelation::getDeptId)
                            .select(UserDeptRelation::getUserId)
                            .innerJoin(Department.class, Department::getId, UserDeptRelation::getDeptId)
                            .like(StrUtil.isNotEmpty(dto.getDeptName()), Department::getName, dto.getDeptName())
            );
            userIdList.addAll(list.stream().map(UserDeptRelation::getUserId).collect(Collectors.toList()));
        }
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        String sql = "SELECT t4.teacher_user_id,SUM(t.total_days) as total_days FROM wf_overtime t" +
                " LEFT JOIN xjr_user t1 ON t1.id = t.user_id" +
                " LEFT JOIN xjr_user t3 ON t3.id = t.leader_in_charge" +
                " LEFT JOIN xjr_workflow_form_relation t2 ON t.id = t2.form_key_value" +
                " LEFT JOIN wf_overtime_teacher t4 ON t.id = t4.wf_overtime_id" +
                " WHERE t2.current_state = 'COMPLETED'";
        if(dto.getStartDate() != null && dto.getEndDate() != null){
            String startTime = dto.getStartDate().atTime(0,0,0).format(dtf);
            String endTime = dto.getEndDate().atTime(23,59,59).format(dtf);
            sql += " AND (" +
                    "   (t.overtime_start_time BETWEEN '" + startTime + "' and '" + endTime + "')" +
                    "   OR (t.overtime_end_time BETWEEN '" + startTime + "' and '" + endTime + "')" +
                    "   OR (t.overtime_start_time > '" + startTime + "' and '" + endTime + "' > t.overtime_end_time)" +
                    "   OR ('" + startTime + "' > t.overtime_start_time and t.overtime_end_time > '" + endTime + "')" +
                    ")";
        }
        if(!userIdList.isEmpty()){
            sql = " AND t.teacher_user_id in (" + userIdList.toString().replace("[", "").replace("]", "") + ")";
        }
        sql += " GROUP BY t4.teacher_user_id";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        Map<Long, String> userLeaveMap = list.stream().filter(x -> x.get("total_days") != null && x.get("teacher_user_id") != null).collect(
                Collectors.toMap(x -> Long.parseLong(x.get("teacher_user_id").toString()), x -> x.get("total_days").toString())
        );

        List<Long> userIds = new ArrayList<>(userLeaveMap.keySet());
        if(userIds.isEmpty()){
            List<LedgerStatisticsOvertimeExcelVo> dataList = new ArrayList<>();
            ByteArrayOutputStream bot = new ByteArrayOutputStream();
            EasyExcel.write(bot, LedgerStatisticsOvertimeExcelVo.class).automaticMergeHead(false).excelType(ExcelTypeEnum.XLSX).sheet().doWrite(dataList);

            return RT.fileStream(bot.toByteArray(), "teacher-leave" + ExcelTypeEnum.XLSX.getValue());
        }

        List<LedgerStatisticsOvertimeVo> userPage = userService.selectJoinList(LedgerStatisticsOvertimeVo.class,
                new MPJLambdaWrapper<User>()
                        .disableSubLogicDel()
                        .select(User::getId)
                        .select(User.class, x -> VoToColumnUtil.fieldsToColumns(UserPageVo.class).contains(x.getProperty()))
                        .select(" (SELECT GROUP_CONCAT(t1.name) FROM xjr_department t1" +
                                " INNER JOIN xjr_user_dept_relation t2 ON t1.id = t2.dept_id" +
                                " WHERE t1.delete_mark = 0 AND t2.user_id = t.id) as dept_name")
                        .innerJoin(BaseTeacher.class, BaseTeacher::getUserId, User::getId)
                        .like(StrUtil.isNotEmpty(dto.getName()), User::getName, dto.getName())
                        .like(StrUtil.isNotEmpty(dto.getUserName()), User::getUserName, dto.getUserName())
                        .in(User::getId, userIds)
                        .orderByAsc(User::getId)
        );

        List<LedgerStatisticsOvertimeExcelVo> dataList = new ArrayList<>();

        int sortCode = 1;
        for (LedgerStatisticsOvertimeVo record : userPage) {
            if(!userLeaveMap.containsKey(record.getId())){
                continue;
            }
            double value = Double.parseDouble(userLeaveMap.get(record.getId()));
            boolean isInteger = (value % 1) == 0;
            DecimalFormat df = new DecimalFormat(isInteger ? "0" : "#.##");
            String formattedValue = df.format(value);
            record.setTotalDays(formattedValue);
            LedgerStatisticsOvertimeExcelVo bean = BeanUtil.toBean(record, LedgerStatisticsOvertimeExcelVo.class);
            bean.setSortCode(sortCode);
            dataList.add(bean);
            sortCode ++;
        }

        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        EasyExcel.write(bot, LedgerStatisticsOvertimeExcelVo.class).automaticMergeHead(false).excelType(ExcelTypeEnum.XLSX).sheet().doWrite(dataList);

        return RT.fileStream(bot.toByteArray(), "teacher-overtime" + ExcelTypeEnum.XLSX.getValue());
    }

    @GetMapping(value = "/teacher-listen-page")
    @ApiOperation(value="教师听课统计(分页)")
    @SaCheckPermission("ledgerstatistics:detail")
    public RT<PageOutput<LedgerStatisticsListenVo>> teacherListenPage(@Valid LedgerStatisticsPageDto dto){
        List<Long> userIdList = new ArrayList<>();
        if(StrUtil.isNotEmpty(dto.getDeptName())){
            List<UserDeptRelation> userList = deptRelationService.list(
                    new MPJLambdaWrapper<UserDeptRelation>()
                            .select(UserDeptRelation::getId)
                            .select(UserDeptRelation::getDeptId)
                            .select(UserDeptRelation::getUserId)
                            .innerJoin(Department.class, Department::getId, UserDeptRelation::getDeptId)
                            .like(StrUtil.isNotEmpty(dto.getDeptName()), Department::getName, dto.getDeptName())
            );
            userIdList.addAll(userList.stream().map(UserDeptRelation::getUserId).collect(Collectors.toList()));
        }
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        String sql = "SELECT t1.user_id,count(t1.course_name) as course_count FROM wf_teacher_listen t1" +
                " LEFT JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
                " WHERE t2.current_state = 'COMPLETED'";
        if(dto.getStartDate() != null && dto.getEndDate() != null){
            String startTime = dto.getStartDate().format(dtf);
            String endTime = dto.getEndDate().format(dtf);
            sql += " AND t1.schedule_date BETWEEN '" + startTime + "' and '" + endTime + "'";
        }
        if(!userIdList.isEmpty()){
            sql = " AND t1.user_id in (" + userIdList.toString().replace("[", "").replace("]", "") + ")";
        }
        sql += " GROUP BY t1.user_id";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        Map<Long, String> userLeaveMap = list.stream().filter(x -> x.get("user_id") != null && x.get("course_count") != null).collect(
                Collectors.toMap(x -> Long.parseLong(x.get("user_id").toString()), x -> x.get("course_count").toString())
        );

        List<Long> userIds = new ArrayList<>(userLeaveMap.keySet());
        if(userIds.isEmpty()){
            return RT.ok(new PageOutput<>());
        }

        IPage<LedgerStatisticsListenVo> userPage = userService.selectJoinListPage(ConventPage.getPage(dto), LedgerStatisticsListenVo.class,
                new MPJLambdaWrapper<User>()
                        .disableSubLogicDel()
                        .select(User::getId)
                        .select(User.class, x -> VoToColumnUtil.fieldsToColumns(UserPageVo.class).contains(x.getProperty()))
                        .select(" (SELECT GROUP_CONCAT(t1.name) FROM xjr_department t1" +
                                " INNER JOIN xjr_user_dept_relation t2 ON t1.id = t2.dept_id" +
                                " WHERE t1.delete_mark = 0 AND t2.user_id = t.id) as dept_name")
                        .innerJoin(BaseTeacher.class, BaseTeacher::getUserId, User::getId)
                        .like(StrUtil.isNotEmpty(dto.getName()), User::getName, dto.getName())
                        .like(StrUtil.isNotEmpty(dto.getUserName()), User::getUserName, dto.getUserName())
                        .in(User::getId, userIds)
                        .orderByAsc(User::getId)
        );

        for (LedgerStatisticsListenVo record : userPage.getRecords()) {
            if(!userLeaveMap.containsKey(record.getId())){
                continue;
            }
            double value = Double.parseDouble(userLeaveMap.get(record.getId()));
            boolean isInteger = (value % 1) == 0;
            DecimalFormat df = new DecimalFormat(isInteger ? "0" : "#.##");
            String formattedValue = df.format(value);
            record.setCourseCount(formattedValue);
        }

        PageOutput<LedgerStatisticsListenVo> pageOutput = ConventPage.getPageOutput(userPage, LedgerStatisticsListenVo.class);
        return RT.ok(pageOutput);
    }

    @PostMapping(value = "/teacher-listen-export-query")
    @ApiOperation(value="教师听课统计-导出")
    @SaCheckPermission("ledgerstatistics:detail")
    public ResponseEntity<byte[]> teacherListenExportQuery(@Valid @RequestBody LedgerStatisticsPageDto dto){
        List<Long> userIdList = new ArrayList<>();
        if(StrUtil.isNotEmpty(dto.getDeptName())){
            List<UserDeptRelation> userList = deptRelationService.list(
                    new MPJLambdaWrapper<UserDeptRelation>()
                            .select(UserDeptRelation::getId)
                            .select(UserDeptRelation::getDeptId)
                            .select(UserDeptRelation::getUserId)
                            .innerJoin(Department.class, Department::getId, UserDeptRelation::getDeptId)
                            .like(StrUtil.isNotEmpty(dto.getDeptName()), Department::getName, dto.getDeptName())
            );
            userIdList.addAll(userList.stream().map(UserDeptRelation::getUserId).collect(Collectors.toList()));
        }

        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        String sql = "SELECT t1.user_id,count(t1.course_name) as course_count FROM wf_teacher_listen t1" +
                " LEFT JOIN xjr_workflow_form_relation t2 ON t1.id = t2.form_key_value" +
                " WHERE t2.current_state = 'COMPLETED'";
        if(dto.getStartDate() != null && dto.getEndDate() != null){
            String startTime = dto.getStartDate().format(dtf);
            String endTime = dto.getEndDate().format(dtf);
            sql += " AND t1.schedule_date BETWEEN '" + startTime + "' and '" + endTime + "'";
        }
        if(!userIdList.isEmpty()){
            sql = " AND t1.user_id in (" + userIdList.toString().replace("[", "").replace("]", "") + ")";
        }
        sql += " GROUP BY t1.user_id";
        List<Map<String, Object>> list = SqlRunnerAdapter.db().selectList(sql);
        Map<Long, String> userLeaveMap = list.stream().filter(x -> x.get("user_id") != null && x.get("course_count") != null).collect(
                Collectors.toMap(x -> Long.parseLong(x.get("user_id").toString()), x -> x.get("course_count").toString())
        );

        List<Long> userIds = new ArrayList<>(userLeaveMap.keySet());
        if(userIds.isEmpty()){
            List<LedgerStatisticsListenExcelVo> dataList = new ArrayList<>();
            ByteArrayOutputStream bot = new ByteArrayOutputStream();
            EasyExcel.write(bot, LedgerStatisticsListenExcelVo.class).automaticMergeHead(false).excelType(ExcelTypeEnum.XLSX).sheet().doWrite(dataList);

            return RT.fileStream(bot.toByteArray(), "teacher-leave" + ExcelTypeEnum.XLSX.getValue());
        }

        List<LedgerStatisticsListenVo> userPage = userService.selectJoinList(LedgerStatisticsListenVo.class,
                new MPJLambdaWrapper<User>()
                        .disableSubLogicDel()
                        .select(User::getId)
                        .select(User.class, x -> VoToColumnUtil.fieldsToColumns(UserPageVo.class).contains(x.getProperty()))
                        .select(" (SELECT GROUP_CONCAT(t1.name) FROM xjr_department t1" +
                                " INNER JOIN xjr_user_dept_relation t2 ON t1.id = t2.dept_id" +
                                " WHERE t1.delete_mark = 0 AND t2.user_id = t.id) as dept_name")
                        .innerJoin(BaseTeacher.class, BaseTeacher::getUserId, User::getId)
                        .like(StrUtil.isNotEmpty(dto.getName()), User::getName, dto.getName())
                        .like(StrUtil.isNotEmpty(dto.getUserName()), User::getUserName, dto.getUserName())
                        .in(User::getId, userIds)
                        .orderByAsc(User::getId)
        );

        List<LedgerStatisticsListenExcelVo> dataList = new ArrayList<>();

        int sortCode = 1;

        for (LedgerStatisticsListenVo record : userPage) {
            if(!userLeaveMap.containsKey(record.getId())){
                continue;
            }
            double value = Double.parseDouble(userLeaveMap.get(record.getId()));
            boolean isInteger = (value % 1) == 0;
            DecimalFormat df = new DecimalFormat(isInteger ? "0" : "#.##");
            String formattedValue = df.format(value);
            record.setCourseCount(formattedValue);

            LedgerStatisticsListenExcelVo bean = BeanUtil.toBean(record, LedgerStatisticsListenExcelVo.class);
            bean.setSortCode(sortCode);
            dataList.add(bean);
            sortCode ++;
        }

        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        EasyExcel.write(bot, LedgerStatisticsListenExcelVo.class).automaticMergeHead(false).excelType(ExcelTypeEnum.XLSX).sheet().doWrite(dataList);

        return RT.fileStream(bot.toByteArray(), "teacher-listen" + ExcelTypeEnum.XLSX.getValue());
    }

}