EmploymentSite/src/main/java/com/hz/employmentsite/controller/statistics/StatisticsController.java

package com.hz.employmentsite.controller.statistics;

import com.hz.employmentsite.filter.exception.BaseResponse;
import com.hz.employmentsite.filter.exception.RespGenerstor;
import com.hz.employmentsite.services.service.statistics.StatisticsService;
import com.hz.employmentsite.util.DateUtils;
import com.hz.employmentsite.util.ExcelHelper;
import com.hz.employmentsite.vo.statistics.HourNewAddCount;
import com.hz.employmentsite.vo.statistics.RegionSystemDataCount;
import com.hz.employmentsite.vo.statistics.SiteServiceCount;
import com.hz.employmentsite.vo.statistics.SystemDataCount;
import com.hz.employmentsite.vo.statistics.company.ModelCompanyCount;
import com.hz.employmentsite.vo.statistics.jobUser.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.*;

@RestController
@RequestMapping(value = "/api/statistics")
public class StatisticsController {
    @Autowired
    private StatisticsService statisticsService;

    @Autowired
    private ExcelHelper excelHelper;

    @Autowired
    private DateUtils dateUtils;

    /**
     * 查询指定时间段系统使用情况
     *
     * @param startDate 开始时间
     * @param endDate   结束时间
     * @return 数据统计VO类集合
     */
    @GetMapping("/systemApplyCount")
    public BaseResponse getSystemApplyCount(@RequestParam(value = "startDate", required = false) Date startDate,
                                            @RequestParam(value = "endDate", required = false) Date endDate) {
        List<SystemDataCount> systemDataCount = statisticsService.findSystemDataCount(startDate, endDate);
        return RespGenerstor.success(systemDataCount);
    }

    /**
     * 查询某月的系统使用情况
     *
     * @param dateStr
     * @return
     */
    @GetMapping("/monthSystemApplyCount")
    public BaseResponse findMonthSystemDataCount(@RequestParam String dateStr, @RequestParam(required = false) String regionCode,
                                                 @RequestParam(required = false) String institutionID,
                                                 @RequestParam(required = false) String siteID) {
        Map<String, List<RegionSystemDataCount>> result = statisticsService.findMonthSystemDataCount(dateStr, regionCode, institutionID, siteID);
        return RespGenerstor.success(result);
    }

    /**
     * 查询某年的系统使用情况
     *
     * @param year
     * @return
     */
    @GetMapping("/yearSystemApplyCount")
    public BaseResponse findYearSystemDataCount(@RequestParam(required = false) String year, @RequestParam(required = false) String regionCode,
                                                @RequestParam(required = false) String institutionID, @RequestParam(required = false) String siteID,
                                                @RequestParam(required = false) Date startDate, @RequestParam(required = false) Date endDate) {
        Map<String, List<RegionSystemDataCount>> result = statisticsService.findYearSystemDataCount(year, regionCode, institutionID, siteID, startDate, endDate);
        return RespGenerstor.success(result);
    }

    /**
     * 查询各个年龄段的求职人员数量
     */
    @GetMapping("/jobUserCountByAgeRange")
    public BaseResponse findJobUserCountByAgeRange(@RequestParam(required = false) String year,
                                                   @RequestParam(required = false) String month,
                                                   @RequestParam(required = false) Boolean isNotCity) {
        List<AgeRangeJobUserCount> result = statisticsService.findJobUserCountByAgeRange(year, month, isNotCity);
        return RespGenerstor.success(result);
    }

    /**
     * 查询各重点人员类别在各学历的分布情况
     */
    @GetMapping("/jobUserCountByPersonType")
    public BaseResponse findJobUserCountByPersonType(@RequestParam(required = false) String year, @RequestParam(required = false) String month) {
        List<PersonTypeJobUserCount> result = statisticsService.findJobUserCountByPersonType(year, month);
        return RespGenerstor.success(result);
    }

    /**
     * 查询各区县的普通失业人员与就业困难人员情况
     */
    @GetMapping("/jobUserByRegionAndDifficulty")
    public BaseResponse findJobUserByRegionAndDifficulty(@RequestParam(required = false) String year, @RequestParam(required = false) String month) {
        List<RegionJobUserCount> result = statisticsService.findJobUserByRegionAndDifficulty(year, month);
        return RespGenerstor.success(result);
    }

    /**
     * 查询各区县的已就业人员
     */
    @GetMapping("/employedJobUserCount")
    public BaseResponse findEmployedJobUserCount() {
        List<RegionJobUserCount> result = statisticsService.findEmployedJobUserCount();
        return RespGenerstor.success(result);
    }

    /**
     * 查询各区县的失业与已就业人员情况
     */
    @GetMapping("/empAndUnEmpJobUserCount")
    public BaseResponse findEmployedAndUnemployedCount() {
        List<RegionJobUserCount> result = statisticsService.findEmployedAndUnemployedCount();
        return RespGenerstor.success(result);
    }

    /**
     * 查询各驿站的业务数据情况
     */
    @GetMapping("/systemApplyCountBySite")
    public BaseResponse getSystemApplyCountBySite(@RequestParam(value = "startDate", required = false) Date startDate,
                                                  @RequestParam(value = "endDate", required = false) Date endDate) {
        List<SystemDataCount> systemDataCount = statisticsService.findSystemDataCountBySite(startDate, endDate);
        return RespGenerstor.success(systemDataCount);
    }

    /**
     * 查询各区县的企业与岗位数量-数据大屏
     */
    @GetMapping("/companyAndPostCountByRegion")
    public BaseResponse getCompanyAndPostByRegion(@RequestParam(value = "startDate", required = false) Date startDate,
                                                  @RequestParam(value = "endDate", required = false) Date endDate) {
        List<RegionSystemDataCount> companyAndPostByRegion = statisticsService.findCompanyAndPostByRegion(startDate, endDate);
        return RespGenerstor.success(companyAndPostByRegion);
    }

    /**
     * 查询各区县的企业规模情况-数据大屏
     */
    @GetMapping("/companyModelCount")
    public BaseResponse getCompanyModelCount() {
        List<ModelCompanyCount> regionCompanyModel = statisticsService.findRegionCompanyModel();
        return RespGenerstor.success(regionCompanyModel);
    }

    /**
     * 查询12种就业困难人员的分布情况
     */
    @GetMapping("/difficultyPersonTypeCount")
    public BaseResponse getDifficultyPersonTypeCount() {
        List<PersonTypeJobUserCount> result = statisticsService.findDifficultyPersonTypeCount();
        return RespGenerstor.success(result);
    }

    /**
     * 查询各县区的指定时间内求职人员与就业意向新增数量
     */
    @GetMapping("/jobUserAndJobHuntCount")
    public BaseResponse getJobUserAndJobHuntCount(@RequestParam(required = false) Date startDate,
                                                  @RequestParam(required = false) Date endDate){
        List<RegionJobUserCount> result = statisticsService.findJobUserAndJobHuntCount(startDate, endDate);
        return RespGenerstor.success(result);
    }

    /**
     * 查询各区县的驿站与求职人员数量
     */
    @GetMapping("/siteAndSiteUserCount")
    public BaseResponse getSiteAndSiteUserCountByRegion() {
        List<RegionSystemDataCount> result = statisticsService.findSiteAndSiteUserCountByRegion();
        return RespGenerstor.success(result);
    }

    /**
     * 查询各驿站的求职人员数量
     */
    @GetMapping("/siteJobUserCount")
    public BaseResponse getSiteJobUserCount(@RequestParam(required = false) Integer jobStatus) {
        List<SiteJobUserCount> result = statisticsService.findSiteJobUserCount(jobStatus);
        return RespGenerstor.success(result);
    }

    /**
     * 查询就业困难人员的服务记录
     */
    @GetMapping("/difficultyUserServiceCount")
    public BaseResponse getDifficultyUserServiceCount() {
        DifficultyUserServiceCount result = statisticsService.findDifficultyUserServiceCount();
        return RespGenerstor.success(result);
    }

    /**
     * 查询某一天的24小时系统使用情况
     */
    @GetMapping("/hourSystemDataCount")
    public BaseResponse getOneDaySystemDataCount(@RequestParam(required = false) Date day) {
        List<HourNewAddCount> result = statisticsService.findOneDaySystemDataCount(day);
        return RespGenerstor.success(result);
    }

    /**
     * 查询各驿站的服务数量
     */
    @GetMapping("/siteServiceCount")
    public BaseResponse getSiteServiceCount(@RequestParam(required = false) Date startDate,
                                            @RequestParam(required = false) Date endDate) {
        List<SiteServiceCount> siteServiceCount = statisticsService.findSiteServiceCount(startDate, endDate);
        return RespGenerstor.success(siteServiceCount);
    }

    /**
     * 查询重点人员在各区县的分布情况
     */
    @GetMapping("/personTypeCountInRegion")
    public BaseResponse getPersonTypeCountInRegion(@RequestParam(required = false) Integer keyPersonTypeID, @RequestParam(required = false) Date startDate,
                                                 @RequestParam(required = false) Date endDate, @RequestParam(required = false) String regionCode,
                                                 @RequestParam(required = false)String siteID) {
        return RespGenerstor.success(statisticsService.findPersonTypeCountInRegion(keyPersonTypeID, startDate, endDate, regionCode, siteID));
    }

    /**
     * 查询重点人员在求职人员的性别，年龄，学历的分布情况
     */
    @GetMapping("/personTypeCountInJobUserData")
    public BaseResponse getPersonTypeCountInJobUserData(@RequestParam(required = false) Integer keyPersonTypeID, @RequestParam(required = false) Date startDate,
                                                        @RequestParam(required = false) Date endDate, @RequestParam(required = false) String regionCode,
                                                        @RequestParam(required = false)String siteID) {
        return RespGenerstor.success(statisticsService.findPersonTypeCountInJobUserData(keyPersonTypeID, startDate, endDate, regionCode, siteID));
    }

    /**
     * 查询各区县的各个规模的企业数量与走访企业次数
     */
    @GetMapping("/companyModelCompanyCountInRegion")
    public BaseResponse getRegionCompanyModelCompanyCount(@RequestParam(required = false) Date startDate, @RequestParam(required = false) Date endDate) {
        return RespGenerstor.success(statisticsService.findRegionCompanyModelCompanyCount(startDate, endDate));
    }

    /**
     * 查询各区县的各个规模的企业用工人数与岗位招聘人数
     */
    @GetMapping("/workSituationCountInRegion")
    public BaseResponse getRegionCompanyWorkSituationCount(@RequestParam(required = false) Date startDate, @RequestParam(required = false) Date endDate) {
        return RespGenerstor.success(statisticsService.findRegionCompanyWorkSituationCount(startDate, endDate));
    }

    /**
     * 查询各个行业各个规模的企业的岗位招聘人数情况
     */
    @GetMapping("/workSituationCountInIndustry")
    public BaseResponse getIndustryCompanyWorkSituationCount(@RequestParam(required = false) Date startDate, @RequestParam(required = false) Date endDate) {
        return RespGenerstor.success(statisticsService.findIndustryCompanyWorkSituationCount(startDate, endDate));
    }

    /**
     * 导出指定时间段的系统使用情况
     *
     * @param response  请求响应
     * @param startDate 开始时间
     * @param endDate   结束时间
     * @return
     */
    @GetMapping("/export/systemApplyCount")
    public BaseResponse exportSystemApplyCount(HttpServletResponse response,
                                               @RequestParam(value = "startDate", required = false) Date startDate,
                                               @RequestParam(value = "endDate", required = false) Date endDate) throws Exception {
        List<SystemDataCount> systemDataCount = statisticsService.findSystemDataCount(startDate, endDate);
        ExcelHelper.ExcelData data = excelHelper.new ExcelData();
        Map<String, CellRangeAddress> titleMap = new HashMap<>();
        String dataStr = "";
        if (startDate != null && endDate != null) {
            dataStr = dateUtils.dateToStrFormat(startDate, "yyyy年MM月dd日") + "-" + dateUtils.dateToStrFormat(endDate, "yyyy年MM月dd日");
        }
        titleMap.put(dataStr + "就业驿站系统使用情况表", new CellRangeAddress(0, 0, 0, 6));
        data.setColspanTitles(Arrays.asList(titleMap));
        data.setTitles(Arrays.asList("行政区划", "驿站名", "驿站人员数量", "录入企业数", "收集岗位条数","收集岗位个数", "登记求职人数"));
        List<List<Object>> rowsData = new ArrayList();
        for (SystemDataCount dataCount : systemDataCount) {
            List<Object> row = new ArrayList();
            row.add(dataCount.getRegionName());
            row.add(dataCount.getSiteName());
            row.add(dataCount.getSiteUserCount());
            row.add(dataCount.getCompanyCount());
            row.add(dataCount.getPostCount());
            row.add(dataCount.getPostRecruitCount());
            row.add(dataCount.getJobUserCount());
            rowsData.add(row);
        }
        data.setRows(rowsData);
        // 设置单元格合并
        List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
        // 合计行合并
        cellRangeAddressList.add(new CellRangeAddress(2, 2, 0, 1));
        // 计算数据，将相同的县区进行合并
        Map<String, Integer> regionCodeFirstIndex = new HashMap<>();
        Map<String, Integer> regionCodeLastIndex = new HashMap<>();
        for (int i = 0; i < systemDataCount.size(); i++) {
            String regionCode = systemDataCount.get(i).getRegionCode();
            if (!"合计".equals(regionCode)) { // 排除 '合计' 这个 regionCode
                if (!regionCodeFirstIndex.containsKey(regionCode)) {
                    regionCodeFirstIndex.put(regionCode, i + 2); // 起始位置加2，避开标题行和合并统计
                }
                regionCodeLastIndex.put(regionCode, i + 2); // 结束位置加2
            }
        }
        // 解析最终坐标
        for (Map.Entry<String, Integer> entry : regionCodeFirstIndex.entrySet()) {
            String regionCode = entry.getKey();
            int firstIndex = entry.getValue();
            int lastIndex = regionCodeLastIndex.get(regionCode);
            // 创建合并区域
            CellRangeAddress newMergedRegion = new CellRangeAddress(firstIndex, lastIndex, 0, 0);
            cellRangeAddressList.add(newMergedRegion);
        }
        data.setCellRangeList(cellRangeAddressList);
        excelHelper.exportExcel(response, data);
        return null;
    }

    /**
     * 导出指定月份的系统使用情况
     *
     * @param response 请求响应
     * @param dateStr  查询月份
     * @return
     */
    @GetMapping("/export/monthSystemApplyCount")
    public BaseResponse exportMonthSystemApplyCount(HttpServletResponse response,
                                                    @RequestParam String dateStr,
                                                    @RequestParam(required = false) String regionCode,
                                                    @RequestParam(required = false) String institutionID,
                                                    @RequestParam(required = false) String siteID) throws Exception {
        Map<String, List<RegionSystemDataCount>> result = statisticsService.findMonthSystemDataCount(dateStr, regionCode, institutionID, siteID);
        ExcelHelper.ExcelData data = excelHelper.new ExcelData();
        // 设置标题区域
        Map<String, CellRangeAddress> titleMap = new HashMap<>();
        titleMap.put("项目", new CellRangeAddress(0, 1, 0, 0));
        // 子标题
        List<String> childrenTitle = new ArrayList<>(Arrays.asList(""));
        // Excel数据
        List<List<Object>> rowsData = new ArrayList();
        // 初始合并位置
        int firstCol = 1;
        // 汇总有"驿站人员数量"
        titleMap.put("汇总", new CellRangeAddress(0, 0, firstCol, firstCol + 4));
        setRSDCExcelData(result.get("汇总"), rowsData, true);
        // 填充子标题
        childrenTitle.addAll(Arrays.asList("驿站人员数量","录入企业数量","收集岗位条数","收集岗位个数","登记求职人数"));
        firstCol += 5;
        // 生成周数标题，周数标题没有驿站人员数量
        for (Map.Entry<String, List<RegionSystemDataCount>> entry : result.entrySet()) {
            String key = entry.getKey();
            if (!key.equals("汇总")){
                titleMap.put(key, new CellRangeAddress(0, 0, firstCol, firstCol + 3));
                // 填充子标题
                childrenTitle.addAll(Arrays.asList("录入企业数量","收集岗位条数","收集岗位个数","登记求职人数"));
                firstCol += 4;
                setRSDCExcelData(result.get(key), rowsData,false);
            }
        }
        data.setColspanTitles(Arrays.asList(titleMap));
        data.setTitles(childrenTitle);
        data.setRows(rowsData);
        excelHelper.exportExcel(response, data);
        return null;
    }


    /**
     * 导出指定年份的系统使用情况
     *
     * @param response 请求响应
     * @param year  查询月份
     * @return
     */
    @GetMapping("/export/yearSystemApplyCount")
    public BaseResponse exportYearSystemApplyCount(HttpServletResponse response,
                                                   @RequestParam String year,
                                                   @RequestParam(required = false) String regionCode,
                                                   @RequestParam(required = false) String institutionID,
                                                   @RequestParam(required = false) String siteID) throws Exception {
        Map<String, List<RegionSystemDataCount>> result = statisticsService.findYearSystemDataCount(year, regionCode, institutionID, siteID, null, null);
        ExcelHelper.ExcelData data = excelHelper.new ExcelData();
        // 设置标题区域
        Map<String, CellRangeAddress> titleMap = new HashMap<>();
        titleMap.put("项目", new CellRangeAddress(0, 1, 0, 0));
        // 子标题
        List<String> childrenTitle = new ArrayList<>(Arrays.asList(""));
        // Excel数据
        List<List<Object>> rowsData = new ArrayList();
        // 初始合并位置
        int firstCol = 1;
        // 汇总有"驿站人员数量"
        titleMap.put("汇总", new CellRangeAddress(0, 0, firstCol, firstCol + 4));
        setRSDCExcelData(result.get("汇总"), rowsData, true);
        // 填充子标题
        childrenTitle.addAll(Arrays.asList("驿站人员数量","录入企业数量","收集岗位条数","收集岗位个数","登记求职人数"));
        firstCol += 5;
        // 生成周数标题，周数标题没有驿站人员数量
        for (Map.Entry<String, List<RegionSystemDataCount>> entry : result.entrySet()) {
            String key = entry.getKey();
            if (!key.equals("汇总")){
                titleMap.put(key, new CellRangeAddress(0, 0, firstCol, firstCol + 3));
                // 填充子标题
                childrenTitle.addAll(Arrays.asList("录入企业数量","收集岗位条数","收集岗位个数","登记求职人数"));
                firstCol += 4;
                setRSDCExcelData(result.get(key), rowsData,false);
            }
        }
        data.setColspanTitles(Arrays.asList(titleMap));
        data.setTitles(childrenTitle);
        data.setRows(rowsData);
        excelHelper.exportExcel(response, data);
        return null;
    }

    /**
     * 系统月度使用情况Excel数据生成
     */
    public void setRSDCExcelData (List<RegionSystemDataCount> valueList, List<List<Object>> rowsData, Boolean isSiteUserCount){
        for (RegionSystemDataCount item : valueList) {
            boolean found = false;
            // 判断最终结果中是否已经生成过相同的县区数据，如有则追加，不新建
            for (List<Object> existingRow : rowsData) {
                if (existingRow.get(0).equals(item.getRegionName())) {
                    if (isSiteUserCount){
                        existingRow.add(item.getSiteUserCount());
                    }
                    existingRow.add(item.getCompanyCount());
                    existingRow.add(item.getPostCount());
                    existingRow.add(item.getPostRecruitCount());
                    existingRow.add(item.getJobUserCount());
                    found = true;
                    break;
                }
            }
            // 没有则新建
            if (!found) {
                List<Object> row = new ArrayList<>();
                row.add(item.getRegionName());
                if (isSiteUserCount){
                    row.add(item.getSiteUserCount());
                }
                row.add(item.getCompanyCount());
                row.add(item.getPostCount());
                row.add(item.getPostRecruitCount());
                row.add(item.getJobUserCount());
                rowsData.add(row);
            }
        }
    }
}