backend_dormitory/src/main/java/com/template/controller/CensusController.java

package com.template.controller;

import com.alibaba.fastjson.JSON;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.template.annotation.PassToken;
import com.template.api.CensusControllerAPI;
import com.template.api.LoginControllerAPI;
import com.template.common.utils.*;
import com.template.config.WxOpenidConfig;
import com.template.model.enumModel.eSchool;
import com.template.model.pojo.*;
import com.template.model.request.changePasswordRequest;
import com.template.model.request.loginRequest;
import com.template.model.request.mobileLoginRequest;
import com.template.model.request.payResultRequest;
import com.template.model.result.CommonResult;
import com.template.model.result.Wx_user;
import com.template.model.vo.*;
import com.template.services.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author: binguo
 * @Date: 2023/7/5 星期三 9:28
 * @Description: com.template.controller
 * @Version: 1.0
 */
@RestController
public class CensusController implements CensusControllerAPI {

    @Autowired
    private WelcomeBedService welcomeBedService;

    @Autowired
    private WelcomeOrgService welcomeOrgService;

    @Autowired
    private WelcomeStudentService welcomeStudentService;

    @Autowired
    private WelcomeAccountService welcomeAccountService;

    // #分配
    // #总人数 totalData
    // #已分配床位数 根据性别、学院求和的 totalBed
    // #已分配寝室数
    // select (select sum(1) as total from welcome_student where deleted = 0 and college = wb.college and iden_type = 1) as totalData,
    // SUM(1) as totalBed,college,sex,build,dormitory
    // from welcome_bed wb
    // where wb.deleted = 0 and wb.college is not null and wb.college != ''
    // group by wb.college,wb.sex,wb.build,wb.dormitory
    @Override
    public CommonResult queryBedTotal(String userId, Integer collegeId, String sex) {
        List<BedTotalVo> result = new ArrayList<>();

        WelcomeAccount wa = welcomeAccountService.getManageById(userId);
        if (wa == null) {
            return CommonResult.fail("当前账户已失效，无权限查看");
        }
        List<String> collegeIds = Arrays.asList(wa.getCollegeId().split(","));
        if(collegeId != null && collegeId.intValue() > 0){
            if (!collegeIds.contains(String.valueOf(collegeId))) {
                return CommonResult.ok(result);
            }else {
                collegeIds = new ArrayList<>();
                collegeIds.add(String.valueOf(collegeId));
            }
        }

        List<WelcomeOrg> orgs = welcomeOrgService.queryColleges(collegeIds);
        List<BedSqlTotalVo> sqlDatas = welcomeBedService.queryBedSqlList(collegeId, sex);
        List<StudentTotalVo> students = welcomeStudentService.queryStudentTotal();

        List<String> sexs = new ArrayList<>();
        if (StringUtils.hasText(sex)) {
            sexs.add(sex);
        } else {
            sexs.add("男");
            sexs.add("女");
        }
        for (WelcomeOrg org : orgs) {
            for (String sexStr : sexs) {
                BedTotalVo btv = new BedTotalVo();
                btv.setCollege(org.getName());
                btv.setSex(sexStr);
                List<BedSqlTotalVo> sqlDataCs = sqlDatas.stream().filter(e -> e.getCollege().equals(org.getName()) && e.getSex().equals(sexStr)).collect(Collectors.toList());
                if (sqlDataCs != null && sqlDataCs.size() > 0) {
                    //int totalData = sqlDataCs.get(0).getTotalData() == null ? 0 : sqlDataCs.get(0).getTotalData();
                    //btv.setTotal(totalData);//总人数
                    int bedTotals = 0;
                    for (BedSqlTotalVo d : sqlDataCs) {
                        bedTotals = bedTotals + d.getTotalBed();
                    }
                    btv.setAssignedBed(bedTotals);//已分配床
                    btv.setAssignedDormitory(sqlDataCs.size());//已分配寝室
                    int buildCount = (int) sqlDataCs.stream().map(BedSqlTotalVo::getBuild).distinct().count();
                    btv.setAssignedBuild(buildCount);//已分配楼栋
                } else {
                    //btv.setTotal(0);
                    btv.setAssignedBed(0);//已分配床
                    btv.setAssignedDormitory(0);//已分配寝室
                    btv.setAssignedBuild(0);//已分配楼栋
                }

                Optional<StudentTotalVo> ostudent = students.stream().filter(e -> e.getCollege().equals(org.getName()) && e.getSex().equals(sexStr)).findFirst();
                if (ostudent != null && ostudent.isPresent()) {
                    btv.setTotal(ostudent.get().getTotal());
                } else {
                    btv.setTotal(0);
                }
                result.add(btv);
            }
        }

        return CommonResult.ok(result);
    }

    @Override
    public void bedTotalExport(HttpServletResponse response, String userId, Integer collegeId, String sex) {
        List<BedTotalVo> result = new ArrayList<>();
        WelcomeAccount wa = welcomeAccountService.getManageById(userId);
        if (wa == null) {
            System.out.println("当前账户已失效，无权限查看");
            ExcelUtils.excelDownload(null, "床位分配统计.xlsx", response);
        }
        List<String> collegeIds = Arrays.asList(wa.getCollegeId().split(","));
        if(collegeId != null && collegeId.intValue() > 0){
            if (!collegeIds.contains(String.valueOf(collegeId))) {

                ExcelUtils.excelDownload(null, "床位分配统计.xlsx", response);
            }else {
                collegeIds = new ArrayList<>();
                collegeIds.add(String.valueOf(collegeId));
            }
        }

        List<WelcomeOrg> orgs = welcomeOrgService.queryColleges(collegeIds);
        List<BedSqlTotalVo> sqlDatas = welcomeBedService.queryBedSqlList(collegeId, sex);
        List<StudentTotalVo> students = welcomeStudentService.queryStudentTotal();

        List<String> sexs = new ArrayList<>();
        if (StringUtils.hasText(sex)) {
            sexs.add(sex);
        } else {
            sexs.add("男");
            sexs.add("女");
        }
        for (WelcomeOrg org : orgs) {
            for (String sexStr : sexs) {
                BedTotalVo btv = new BedTotalVo();
                btv.setCollege(org.getName());
                btv.setSex(sexStr);
                List<BedSqlTotalVo> sqlDataCs = sqlDatas.stream().filter(e -> e.getCollege().equals(org.getName()) && e.getSex().equals(sexStr)).collect(Collectors.toList());
                if (sqlDataCs != null && sqlDataCs.size() > 0) {
                    //int totalData = sqlDataCs.get(0).getTotalData() == null ? 0 : sqlDataCs.get(0).getTotalData();
                    //btv.setTotal(totalData);//总人数
                    int bedTotals = 0;
                    for (BedSqlTotalVo d : sqlDataCs) {
                        bedTotals = bedTotals + d.getTotalBed();
                    }
                    btv.setAssignedBed(bedTotals);//已分配床
                    btv.setAssignedDormitory(sqlDataCs.size());//已分配寝室
                    int buildCount = (int) sqlDataCs.stream().map(BedSqlTotalVo::getBuild).distinct().count();
                    btv.setAssignedBuild(buildCount);//已分配楼栋
                } else {
                    //btv.setTotal(0);
                    btv.setAssignedBed(0);//已分配床
                    btv.setAssignedDormitory(0);//已分配寝室
                    btv.setAssignedBuild(0);//已分配楼栋
                }
                Optional<StudentTotalVo> ostudent = students.stream().filter(e -> e.getCollege().equals(org.getName()) && e.getSex().equals(sexStr)).findFirst();
                if (ostudent != null && ostudent.isPresent()) {
                    btv.setTotal(ostudent.get().getTotal());
                } else {
                    btv.setTotal(0);
                }
                result.add(btv);
            }
        }
        //导出
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("床位分配统计");

        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("序号");
        headerRow.createCell(1).setCellValue("学院名称");
        headerRow.createCell(2).setCellValue("性别");
        headerRow.createCell(3).setCellValue("总人数");
        headerRow.createCell(4).setCellValue("已分配床位数");
        headerRow.createCell(5).setCellValue("已分配寝室数");
        headerRow.createCell(6).setCellValue("已分配楼栋数");
        for (int i = 0; i < result.size(); i++) {
            BedTotalVo bedData = result.get(i);
            Row dataRow = sheet.createRow(i + 1);
            dataRow.createCell(0).setCellValue(i + 1);
            dataRow.createCell(1).setCellValue(bedData.getCollege());
            dataRow.createCell(2).setCellValue(bedData.getSex());
            dataRow.createCell(3).setCellValue(bedData.getTotal());
            dataRow.createCell(4).setCellValue(bedData.getAssignedBed());
            dataRow.createCell(5).setCellValue(bedData.getAssignedDormitory());
            dataRow.createCell(6).setCellValue(bedData.getAssignedBuild());
        }
        // 将工作簿写入文件
        ExcelUtils.excelDownload(workbook, "床位分配统计.xlsx", response);
    }

    //    #总人数 totalData
    //    #已入住人数 sleeper
    //    #未入住人数 = 总人数 - 已入住人数
    //    #总床位数 totalBed
    //    #已入住床位数 = 已入住人数
    //    #未入住床位数 = 总床位数 - 已入住床位数
    //    select SUM(1) as totalBed,
    //    (select SUM(1) as total from welcome_bed where deleted = 0 and `name` is not null and name != '' and college = wb.college and sex = wb.sex) as sleeper,
    //    (select sum(1) as total from welcome_student where deleted = 0 and college = wb.college and iden_type = 1 and sex = wb.sex) as totalData,wb.college,wb.sex from welcome_bed wb
    //    where wb.deleted = 0 and wb.college is not null
    //    group by wb.college,wb.sex
    @Override
    public CommonResult queryCheckTotal(String userId, Integer collegeId, String sex) {
        List<SleepTotalVo> result = new ArrayList<>();
        WelcomeAccount wa = welcomeAccountService.getManageById(userId);
        if (wa == null) {
            return CommonResult.fail("当前账户已失效，无权限查看");
        }
        List<String> collegeIds = Arrays.asList(wa.getCollegeId().split(","));
        if(collegeId != null && collegeId.intValue() > 0){
            if (!collegeIds.contains(String.valueOf(collegeId))) {
                return CommonResult.ok(result);
            }else {
                collegeIds = new ArrayList<>();
                collegeIds.add(String.valueOf(collegeId));
            }
        }

        List<WelcomeOrg> orgs = welcomeOrgService.queryColleges(collegeIds);
        List<SleepSqlTotalVo> sqlDatas = welcomeBedService.queryTotalSqlList(collegeId, sex);
        List<StudentTotalVo> students = welcomeStudentService.queryStudentTotal();

        List<String> sexs = new ArrayList<>();
        if (StringUtils.hasText(sex)) {
            sexs.add(sex);
        } else {
            sexs.add("男");
            sexs.add("女");
        }
        for (WelcomeOrg org : orgs) {
            for (String sexStr : sexs) {
                SleepTotalVo btv = new SleepTotalVo();
                btv.setCollege(org.getName());
                btv.setSex(sexStr);
                Optional<StudentTotalVo> ostudent = students.stream().filter(e -> e.getCollege().equals(org.getName()) && e.getSex().equals(sexStr)).findFirst();
                if (ostudent != null && ostudent.isPresent()) {
                    btv.setTotalData(ostudent.get().getTotal());
                } else {
                    btv.setTotalData(0);
                }
                List<SleepSqlTotalVo> sqlDataCs = sqlDatas.stream().filter(e -> e.getCollege().equals(org.getName()) && e.getSex().equals(sexStr)).collect(Collectors.toList());
                if (sqlDataCs != null && sqlDataCs.size() > 0) {
                    int toalData = btv.getTotalData();//sqlDataCs.get(0).getTotalData() == null ? 0 : sqlDataCs.get(0).getTotalData();
                    int sleeper = sqlDataCs.get(0).getSleeper() == null ? 0 : sqlDataCs.get(0).getSleeper();
                    int totalBed = sqlDataCs.get(0).getTotalBed() == null ? 0 : sqlDataCs.get(0).getTotalBed();
                    //btv.setTotalData(toalData);//总人数
                    btv.setSleeper(sleeper);//已入住人数
                    btv.setUnSleeper((toalData - sleeper) < 0 ? 0 : (toalData - sleeper));//未入住人数
                    btv.setTotalBed(totalBed);//总床数
                    btv.setSleepBed(sleeper);//已入住床位数
                    btv.setUnSleepBed((totalBed - sleeper) < 0 ? 0 : (totalBed - sleeper));//未入住床位数
                } else {
                    //btv.setTotalData(0);//总人数
                    btv.setSleeper(0);//已入住人数
                    btv.setUnSleeper(0);//未入住人数
                    btv.setTotalBed(0);//总床数
                    btv.setSleepBed(0);//已入住床位数
                    btv.setUnSleepBed(0);//未入住床位数
                }

                result.add(btv);
            }
        }
        return CommonResult.ok(result);
    }

    @Override
    public void checkTotalExport(HttpServletResponse response, String userId, Integer collegeId, String sex) {
        List<SleepTotalVo> result = new ArrayList<>();
        WelcomeAccount wa = welcomeAccountService.getManageById(userId);
        if (wa == null) {
            System.out.println("当前账户已失效，无权限查看");
            ExcelUtils.excelDownload(null, "床位入住统计.xlsx", response);
        }
        List<String> collegeIds = Arrays.asList(wa.getCollegeId().split(","));
        if(collegeId != null && collegeId.intValue() > 0){
            if (!collegeIds.contains(String.valueOf(collegeId))) {

                ExcelUtils.excelDownload(null, "床位入住统计.xlsx", response);
            }else {
                collegeIds = new ArrayList<>();
                collegeIds.add(String.valueOf(collegeId));
            }
        }

        List<WelcomeOrg> orgs = welcomeOrgService.queryColleges(collegeIds);
        List<SleepSqlTotalVo> sqlDatas = welcomeBedService.queryTotalSqlList(collegeId, sex);
        List<StudentTotalVo> students = welcomeStudentService.queryStudentTotal();

        List<String> sexs = new ArrayList<>();
        if (StringUtils.hasText(sex)) {
            sexs.add(sex);
        } else {
            sexs.add("男");
            sexs.add("女");
        }

        for (WelcomeOrg org : orgs) {
            for (String sexStr : sexs) {
                SleepTotalVo btv = new SleepTotalVo();
                btv.setCollege(org.getName());
                btv.setSex(sexStr);
                Optional<StudentTotalVo> ostudent = students.stream().filter(e -> e.getCollege().equals(org.getName()) && e.getSex().equals(sexStr)).findFirst();
                if (ostudent != null && ostudent.isPresent()) {
                    btv.setTotalData(ostudent.get().getTotal());
                } else {
                    btv.setTotalData(0);
                }
                List<SleepSqlTotalVo> sqlDataCs = sqlDatas.stream().filter(e -> e.getCollege().equals(org.getName()) && e.getSex().equals(sexStr)).collect(Collectors.toList());
                if (sqlDataCs != null && sqlDataCs.size() > 0) {
                    int toalData = btv.getTotalData();//sqlDataCs.get(0).getTotalData() == null ? 0 : sqlDataCs.get(0).getTotalData();
                    int sleeper = sqlDataCs.get(0).getSleeper() == null ? 0 : sqlDataCs.get(0).getSleeper();
                    int totalBed = sqlDataCs.get(0).getTotalBed() == null ? 0 : sqlDataCs.get(0).getTotalBed();
                    //btv.setTotalData(toalData);//总人数
                    btv.setSleeper(sleeper);//已入住人数
                    btv.setUnSleeper((toalData - sleeper) < 0 ? 0 : (toalData - sleeper));//未入住人数
                    btv.setTotalBed(totalBed);//总床数
                    btv.setSleepBed(sleeper);//已入住床位数
                    btv.setUnSleepBed((totalBed - sleeper) < 0 ? 0 : (totalBed - sleeper));//未入住床位数
                } else {
                    //btv.setTotalData(0);//总人数
                    btv.setSleeper(0);//已入住人数
                    btv.setUnSleeper(0);//未入住人数
                    btv.setTotalBed(0);//总床数
                    btv.setSleepBed(0);//已入住床位数
                    btv.setUnSleepBed(0);//未入住床位数
                }
                result.add(btv);
            }
        }
        //导出
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("床位入住统计");

        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("序号");
        headerRow.createCell(1).setCellValue("学院名称");
        headerRow.createCell(2).setCellValue("性别");
        headerRow.createCell(3).setCellValue("总人数");
        headerRow.createCell(4).setCellValue("已入住人数");
        headerRow.createCell(5).setCellValue("未入住人数");
        headerRow.createCell(6).setCellValue("总床位数");
        headerRow.createCell(7).setCellValue("已入住床位数");
        headerRow.createCell(8).setCellValue("未入住床位数");
        for (int i = 0; i < result.size(); i++) {
            SleepTotalVo bedData = result.get(i);
            Row dataRow = sheet.createRow(i + 1);
            dataRow.createCell(0).setCellValue(i + 1);
            dataRow.createCell(1).setCellValue(bedData.getCollege());
            dataRow.createCell(2).setCellValue(bedData.getSex());
            dataRow.createCell(3).setCellValue(bedData.getTotalData());
            dataRow.createCell(4).setCellValue(bedData.getSleeper());
            dataRow.createCell(5).setCellValue(bedData.getUnSleeper());
            dataRow.createCell(6).setCellValue(bedData.getTotalBed());
            dataRow.createCell(7).setCellValue(bedData.getSleepBed());
            dataRow.createCell(8).setCellValue(bedData.getUnSleepBed());
        }
        // 将工作簿写入文件
        ExcelUtils.excelDownload(workbook, "床位入住统计.xlsx", response);
    }
}