loan-backend/src/main/java/com/loan/system/controller/wechat/StatisticsController.java

package com.loan.system.controller.wechat;

import com.loan.system.config.FileUploadConfig;
import com.loan.system.domain.entity.*;
import com.loan.system.domain.enums.DecisionEnum;
import com.loan.system.domain.enums.StepEnum;
import com.loan.system.domain.enums.StepPropertyEnum;
import com.loan.system.domain.pojo.Result;
import com.loan.system.domain.vo.*;
import com.loan.system.service.*;
import com.loan.system.utils.ResultUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@RestController
@RequestMapping("/wechat/statistics")
@Api(tags = "数据统计接口")
public class StatisticsController {
    @Autowired
    private LoanService loanService;
    @Autowired
    private DisbursementService disbursementService;
    @Autowired
    private DisbursementRecordService disbursementRecordService;
    @Autowired
    private RepaymentService repaymentService;
    @Autowired
    private RepaymentRecordService repaymentRecordService;
    @Autowired
    private FileUploadConfig fileUploadConfig;
    @Autowired
    private ContractService contractService;
    @Autowired
    private CustomerService customerService;
    @Autowired
    private StepService stepService;
    @Autowired
    private UserService userService;
    @Autowired
    private CollateralPlanService collateralPlanService;
    @Autowired
    private LocationDatumService locationDatumService;

    @GetMapping("/dailyReport")
    @ApiOperation("获取日报表")
    public Result dailyReport(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                              @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {//TODO：利息也要

        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String beginTime = begin.atStartOfDay().format(fmt);
        String endTime = begin.atTime(23, 59, 59).format(fmt);

        List<DailyReport> dailyReports =new ArrayList<>();

        //TODO：repayTime要改成数据库标准时间
        List<RepaymentRecord> records = repaymentRecordService.findBetweenRange(beginTime, endTime);
        for (RepaymentRecord record : records){
            DailyReport dailyReport = new DailyReport();
            Repayment repayment = repaymentService.findByIdAndIsDelete(record.getRepaymentId());
            Long caseId = repayment.getCaseId();
            LoanCaseSimpleVO loanCase = loanService.findLoanCaseSimpleByIdAndIsDelete(caseId, false);

            dailyReport.setCustomerName(customerService.findByIdAndIsDelete(loanCase.getCustomerId()).getName());//1.
            dailyReport.setLoanCaseAttribute(loanCase.getBusinessAttrs());//2.
            dailyReport.setDisbursementDate(disbursementService.getLoanTime(caseId));//3.
            dailyReport.setLoanAmount(loanCase.getTotalLoanAmount());//4.

            List<RepaymentRecord> repaymentRecords = repaymentRecordService.findByRepaymentIdAndIsDelete(record.getRepaymentId(), false);
            double totalRepayAmount = 0.0;
            for (RepaymentRecord repaymentRecord : repaymentRecords){
                if(!repaymentRecord.getIsInterest())
                    totalRepayAmount += repaymentRecord.getAmount();
            }
            dailyReport.setRepayTotalAmount(totalRepayAmount);//5.
            dailyReport.setRepayDate(record.getRepayTime());//7.
            dailyReport.setRemainAmount(loanCase.getTotalLoanAmount()-totalRepayAmount);//6.
            if (dailyReport.getRemainAmount()<Double.MIN_VALUE){
                //还清才能结算利息
                List<ContractVO> contracts = contractService.findContractByCaseId(caseId);
                double totalInterest = 0.0;
                for (ContractVO contract : contracts)
                    if (contract.getClearedStatus().equals(DecisionEnum.CLEAR_AMOUNT_YES.getMsg()))
                        totalInterest += contract.getInterestAmount();

                dailyReport.setLoanInterest(totalInterest);//8.
            }

            dailyReports.add(dailyReport);
        }

        return ResultUtil.success("success",dailyReports);
    }

    @GetMapping("/dailyReport/export")
    @ApiOperation("导出日报表")
    public void exportDailyReport(@RequestBody List<DailyReport> dailyReports ,@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                                  @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end,
                                  HttpServletResponse response) {
        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yy-MM-dd HH:mm:ss");
        String beginTime = begin.atStartOfDay().format(fmt);
        String endTime = begin.atTime(23, 59, 59).format(fmt);

        List<DailyReport> dailyReportList = dailyReports.stream()
                .filter(Objects::nonNull)
                .filter(c -> c.getRepayDate() != null && !c.getRepayDate().equals(" "))
                .filter(c -> {
                    try {
                        LocalDate d = LocalDate.parse(c.getRepayDate(), fmt);
                        return !d.isBefore(LocalDate.parse(beginTime, fmt)) && !d.isAfter(LocalDate.parse(endTime, fmt));
                    } catch (Exception e) {
                        return false;   // 格式不对直接丢弃
                    }
                })
                .collect(Collectors.toList());
        //查询概览运营数据，提供给Excel模板文件
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(fileUploadConfig.getUploadDir() + "日报汇总.xlsx");
        try {
            //基于提供好的模板文件创建一个新的Excel表格对象
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);

            //获得Excel文件中的一个Sheet页
            XSSFSheet sheet = excel.getSheet("Sheet1");
            //TODO：row（从0开始）行，cell（从0开始）获取单元格
            //获得第1行
            XSSFRow row = sheet.getRow(0);
            row.getCell(0).setCellValue(begin.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日")));

            //获取单元格
            for (int i = 0; i <dailyReportList.size(); i++) {
                DailyReport dailyReport = dailyReportList.get(i);
                row = sheet.getRow(3+i);
                row.getCell(0).setCellValue(dailyReport.getCustomerName());
                row.getCell(1).setCellValue(dailyReport.getLoanCaseAttribute());
                row.getCell(2).setCellValue(dailyReport.getDisbursementDate());
                row.getCell(3).setCellValue(dailyReport.getLoanAmount());
                row.getCell(4).setCellValue(dailyReport.getRepayTotalAmount());
                row.getCell(5).setCellValue(dailyReport.getRemainAmount());
                row.getCell(6).setCellValue(dailyReport.getRepayDate());
                row.getCell(7).setCellValue(dailyReport.getLoanInterest());

            }

            //通过输出流将文件下载到客户端浏览器中
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);

            //关闭资源
            out.flush();
            out.close();
            excel.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @GetMapping("/arrangeReport")
    @ApiOperation("获取排期统计表")
    public Result arrangeReport(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin) {

        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String endTime = begin.atTime(23, 59, 59).format(fmt);

        List<LoanCaseStatistic> loanCaseStatistics = new ArrayList<>();

        //指派人员只需要显示进行中的业务
        List<LoanCaseSimpleVO> loanCases = loanService.findLoanCaseSimpleByIsComplete(DecisionEnum.PROCESS.getMsg());
        for (LoanCaseSimpleVO loanCase : loanCases){
            LoanCaseStatistic loanCaseStatistic = new LoanCaseStatistic();
            List<StepVO> steps = stepService.getChildStepByCaseIdBeforeTime(loanCase.getId(), endTime);
            //仅显示派单环节的信息
            for (StepVO step : steps){
                if (step.getStatus().equals(StepEnum.UNSTART.getMsg()) || step.getUserId1() == null)
                    continue;

                if (!step.getCode().equals(StepPropertyEnum.EVIDENCE_CONFIRMATION.getCode()) && !step.getCode().equals(StepPropertyEnum.DELIVERY_CONFIRMATION.getCode())
                &&!step.getCode().equals(StepPropertyEnum.DISBURSE_START.getCode()) && !step.getCode().equals(StepPropertyEnum.BALANCE_REPAY.getCode()))
                    continue;

                loanCaseStatistic.setWeekSeq(calculateWeekSequence(LocalDate.parse(step.getBeginTime(), fmt)));
                loanCaseStatistic.setDisburseDate(getDateAndPlace( step ,loanCase.getId()).get(0));//2.
                loanCaseStatistic.setCustomerName(customerService.findByCustomerIdAndIsDelete(loanCase.getCustomerId(), false).getName());//3.
                loanCaseStatistic.setBusinessType(loanCase.getBusinessType());//4.
                loanCaseStatistic.setAmount(loanCase.getTotalLoanAmount());//5.

                loanCaseStatistic.setStepName(step.getStepName());//6.

                if (step.getCode().equals(StepPropertyEnum.BALANCE_REPAY.getCode())||step.getCode().equals(StepPropertyEnum.DISBURSE_START.getCode())){
                    List<String> data = getDateAndPlace(step, loanCase.getId());
                    if (data.size()>=3)
                        loanCaseStatistic.setUserName1(data.get(2));
                    if (data.size()==4)
                        loanCaseStatistic.setUserName2(data.get(3));
                }else {
                    String[] ids = new String[]{};
                    if (step.getUserIds() != null)
                        ids = step.getUserIds().split(",");
                    String userName1 = "";
                    if (ids.length > 0)
                        userName1 = userService.findByIdAndIsDelete(Long.parseLong(ids[0])).getRealName();
                    loanCaseStatistic.setUserName1(userName1);//7.

                    String userName2 = "";
                    Stream<String> distinct = Arrays.stream(ids).distinct();
                    List<Long> userIds = distinct.map(Long::parseLong).collect(Collectors.toList());
                    if(userIds.size() > 1){
                        List<User> users = userService.findByIdsAndIsDelete(userIds.subList(1, userIds.size()));//保证了第一个不会出现在后面
                        userName2 = users.stream().map(User::getRealName).collect(Collectors.toList()).toString();
                    }
                    loanCaseStatistic.setUserName2(userName2);//8.
                }

                loanCaseStatistic.setStartDate(step.getBeginTime());//9.
                loanCaseStatistic.setPlace(getDateAndPlace( step ,loanCase.getId()).get(1));//10.
                loanCaseStatistic.setEndDate(step.getStatus().equals(StepEnum.COMPLETED.getMsg()) ? step.getUpdateTime() : step.getStatus());//11.

                loanCaseStatistics.add(loanCaseStatistic);
            }

        }
        // 对 loanCaseStatistics 进行排序
        loanCaseStatistics.sort(Comparator
                .comparing(LoanCaseStatistic::getStartDate)
                .thenComparing(LoanCaseStatistic::getUserName1));


        return ResultUtil.success("success",loanCaseStatistics);
    }

    @GetMapping("/loanCaseReport")
    @ApiOperation("获取业务统计表")
    public Result loanCaseReport(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                                 @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {
        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String beginTime = begin.atStartOfDay().format(fmt);
        String endTime = end.atTime(23, 59, 59).format(fmt);

        List<LoanCaseStatistic> loanCaseStatistics = new ArrayList<>();

        List<LoanCaseSimpleVO> loanCases = loanService.findLoanCaseBetweenRange(beginTime, endTime);

        for (LoanCaseSimpleVO loanCase : loanCases){
            LoanCaseStatistic loanCaseStatistic = new LoanCaseStatistic();
            List<StepVO> steps = stepService.getChildStepByCaseIdBetweenRange(loanCase.getId(), beginTime, endTime);
            for (StepVO step : steps){
                if (step.getStatus().equals(StepEnum.UNSTART.getMsg()) || step.getUserId1() == null)
                    continue;

                loanCaseStatistic.setWeekSeq(calculateWeekSequence(LocalDate.parse(step.getBeginTime(), fmt)));
                List<String> dateAndPlace = getDateAndPlace(step, loanCase.getId());
                if(!dateAndPlace.isEmpty())
                    loanCaseStatistic.setDisburseDate(dateAndPlace.get(0));//2.
                loanCaseStatistic.setCustomerName(customerService.findByCustomerIdAndIsDelete(loanCase.getCustomerId(), false).getName());//3.
                loanCaseStatistic.setBusinessType(loanCase.getBusinessType());//4.
                loanCaseStatistic.setAmount(loanCase.getTotalLoanAmount());//5.

//                String stepName = "";
//                if(step.getCode()==StepPropertyEnum.CHANNEL_PUSH.getCode())
//                    stepName += "（取证）";
//                else if (step.getCode()==StepPropertyEnum.CHANNEL_PUSH_2.getCode())
//                    stepName += "（送证）";
//                else
//                    stepName = step.getStepName();
                loanCaseStatistic.setStepName(step.getStepName());//6.

                if (step.getCode().equals(StepPropertyEnum.BALANCE_REPAY.getCode())||step.getCode().equals(StepPropertyEnum.DISBURSE_START.getCode())){
                    List<String> data = getDateAndPlace(step, loanCase.getId());
                    if (data.size()>=3)
                        loanCaseStatistic.setUserName1(data.get(2));
                    if (data.size()==4)
                        loanCaseStatistic.setUserName2(data.get(3));
                }else {
                    String[] ids = new String[]{};
                    if (step.getUserIds() != null)
                        ids = step.getUserIds().split(",");
                    String userName1 = "";
                    if (ids.length > 0)
                        userName1 = userService.findByIdAndIsDelete(Long.parseLong(ids[0])).getRealName();
                    loanCaseStatistic.setUserName1(userName1);//7.

                    String userName2 = "";
                    Stream<String> distinct = Arrays.stream(ids).distinct();
                    List<Long> userIds = distinct.map(Long::parseLong).collect(Collectors.toList());
                    if(userIds.size() > 1){
                        List<User> users = userService.findByIdsAndIsDelete(userIds.subList(1, userIds.size()));//保证了第一个不会出现在后面
                        userName2 = users.stream().map(User::getRealName).collect(Collectors.toList()).toString();
                    }
                    loanCaseStatistic.setUserName2(userName2);//8.
                }

                loanCaseStatistic.setStartDate(step.getBeginTime());//9.
                if(dateAndPlace.size()>1)
                    loanCaseStatistic.setPlace(dateAndPlace.get(1));//10.
                loanCaseStatistic.setEndDate(step.getStatus().equals(StepEnum.COMPLETED.getMsg()) ? step.getUpdateTime() : step.getStatus());//11.

                loanCaseStatistics.add(loanCaseStatistic);
            }

            // 对 loanCaseStatistics 进行排序
            loanCaseStatistics.sort(Comparator
                    .comparing(LoanCaseStatistic::getStartDate)
                    .thenComparing(LoanCaseStatistic::getUserName1));

        }

        return ResultUtil.success("success",loanCaseStatistics);
    }

    private String calculateWeekSequence(LocalDate date) {
        // 获取月份的第一天
        LocalDate firstDayOfMonth = date.withDayOfMonth(1);

        // 获取第一天是星期几(1=Monday, 7=Sunday)
        int firstDayOfWeek = firstDayOfMonth.getDayOfWeek().getValue();

        // 计算指定日期是当月第几天
        int dayOfMonth = date.getDayOfMonth();

        // 计算是第几周 (W1, W2, W3...)TODO:取余计算需要从0开始
        // 例如：如果1号是周三，那么1-3号属于第一周，4-10号属于第二周...
        int weekSequence = ((dayOfMonth + firstDayOfWeek - 2) / 7) + 1;

        return "W" + weekSequence;
    }

    private List<String > getDateAndPlace(StepVO  step , Long caseId){
        List<String> list = new ArrayList<>();//有两个，第一个是date，第二个是place
        String date = "";
        String place = "";
        String mainUSerName="";
        String assistUserName="";
        if (step.getCode().equals(StepPropertyEnum.EVIDENCE_CONFIRMATION.getCode())){
            List<CollateralPlan> collateralPlans = collateralPlanService.findByCaseIdAndIsDelete(caseId, false);

            for (CollateralPlan plan : collateralPlans){
                if (plan.getTime()== null || plan.getPlace()== null)
                    continue;
                if (plan.getFlag().equals(DecisionEnum.ENTER_WAREHOUSE.getMsg())){
                    date = date.isEmpty() ? plan.getTime() :date.concat("、").concat(plan.getTime());
                    place = place.isEmpty() ? plan.getPlace() :place.concat("、").concat(plan.getPlace());
                }
            }
            list.add(date);
            list.add(place);
        }else if (step.getCode().equals(StepPropertyEnum.DELIVERY_CONFIRMATION.getCode())){
            List<CollateralPlan> collateralPlans = collateralPlanService.findByCaseIdAndIsDelete(caseId, false);

            for (CollateralPlan plan : collateralPlans){
                if (plan.getTime()== null || plan.getPlace()== null)
                    continue;
                if (plan.getFlag().equals(DecisionEnum.OUT_WAREHOUSE.getMsg())){
                    date = date.isEmpty() ? plan.getTime() :date.concat("、").concat(plan.getTime());
                    place = place.isEmpty() ? plan.getPlace() :place.concat("、").concat(plan.getPlace());
                }
            }
            list.add(date);
            list.add(place);
        }else if (step.getCode().equals(StepPropertyEnum.DISBURSE_START.getCode())) {
            List<Disbursement> disbursements = disbursementService.getDisbursementByCaseId(caseId);
            for (Disbursement disbursement : disbursements){
                if(disbursement.getPlannedTime()!=null)
                    date = date.isEmpty() ? disbursement.getPlannedTime() :date.concat("、").concat(disbursement.getPlannedTime());
                if(disbursement.getLocationId()!=null){
                    LocationDatum locationDatum = locationDatumService.findById(disbursement.getLocationId());
                    place = place.isEmpty() ? locationDatum.getSimpleAddress() :place.concat("、").concat(locationDatum.getSimpleAddress());
                }
                User user = userService.findByIdAndIsDelete(disbursement.getMainUserId());
                if (user !=null)
                    mainUSerName = mainUSerName.isEmpty() ? user.getRealName() :mainUSerName.concat("、").concat(user.getRealName());
                User user1 = userService.findByIdAndIsDelete(disbursement.getAssistUserId());
                if (user1 !=null)
                    assistUserName = assistUserName.isEmpty() ? user1.getRealName() :assistUserName.concat( "、").concat(user1.getRealName());
            }

            list.add( date);
            list.add(place);
            list.add(mainUSerName);
            list.add(assistUserName);
        }else if(step.getCode().equals(StepPropertyEnum.BALANCE_REPAY.getCode())){
            Repayment repayment = repaymentService.findByCaseIdAndIsDelete(caseId, false);
            if (repayment!=null){
                List<RepaymentRecord> records = repaymentRecordService.findByRepaymentIdAndIsInterestAndIsDelete(repayment.getId(), false);
                for (RepaymentRecord record : records){
                    if (record.getRepayTime()!= null || record.getRepayLocation()== null)
                        date = date.isEmpty() ? record.getRepayTime() :date.concat("、").concat(record.getRepayTime());
                    if(record.getRepayLocation()!=null)
                        place = place.isEmpty() ? record.getRepayLocation() :place.concat("、").concat(record.getRepayLocation());

                    User user = userService.findByIdAndIsDelete(record.getMainUser());
                    if (user !=null)
                        mainUSerName = mainUSerName.isEmpty() ? user.getRealName() :mainUSerName.concat("、").concat(user.getRealName());
                    User user1 = userService.findByIdAndIsDelete(record.getAssistUser());
                    if (user1 !=null)
                        assistUserName = assistUserName.isEmpty() ? user1.getRealName() :assistUserName.concat( "、").concat(user1.getRealName());

                }
            }
            list.add( date);
            list.add(place);
            list.add(mainUSerName);
            list.add(assistUserName);
        }
        return list;
    }

    @GetMapping("/loanCaseReport/export")
    @ApiOperation("导出业务统计表")
    public void exportLaonCaseReport(@RequestBody List<LoanCaseStatistic> loanCaseStatistics, @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
                                     @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end, HttpServletResponse response) {
        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yy-MM-dd HH:mm:ss");
        String beginTime = begin.atStartOfDay().format(fmt);
        String endTime = end.atTime(23, 59, 59).format(fmt);

        List<LoanCaseStatistic> loanCaseStatisticList = loanCaseStatistics.stream()
                .filter(Objects::nonNull)
                .filter(c -> c.getEndDate() != null && !c.getEndDate().equals(" "))
                .filter(c -> {
                    try {
                        LocalDate d = LocalDate.parse(c.getEndDate(), fmt);
                        return !d.isBefore(LocalDate.parse(beginTime, fmt)) && !d.isAfter(LocalDate.parse(endTime, fmt));
                    } catch (Exception e) {
                        return false;   // 格式不对直接丢弃
                    }
                })
                .collect(Collectors.toList());
        //查询概览运营数据，提供给Excel模板文件
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(fileUploadConfig.getUploadDir() + "工作计划排期&业务统计表.xlsx");
        try {
            //基于提供好的模板文件创建一个新的Excel表格对象
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);

            //获得Excel文件中的一个Sheet页
            XSSFSheet sheet = excel.getSheet("Sheet1");
            //TODO：row（从0开始）行，cell（从0开始）获取单元格
            //获得第1行
            XSSFRow row = sheet.getRow(0);
            row.getCell(1).setCellValue(begin.format(DateTimeFormatter.ofPattern("M月")));

            //获取单元格
            for (int i = 0; i < loanCaseStatisticList.size(); i++) {
                LoanCaseStatistic loanCaseStatistic = loanCaseStatisticList.get(i);
                row = sheet.getRow(3+i);
                row.getCell(0).setCellValue(loanCaseStatistic.getWeekSeq());
                row.getCell(1).setCellValue(loanCaseStatistic.getStartDate());
                row.getCell(2).setCellValue(loanCaseStatistic.getCustomerName());
                row.getCell(3).setCellValue(loanCaseStatistic.getBusinessType());
                row.getCell(4).setCellValue(loanCaseStatistic.getAmount());
                row.getCell(5).setCellValue(loanCaseStatistic.getStepName());
                row.getCell(6).setCellValue(loanCaseStatistic.getUserName1());
                row.getCell(7).setCellValue(loanCaseStatistic.getUserName2());
                row.getCell(8).setCellValue(loanCaseStatistic.getEndDate());
                row.getCell(9).setCellValue(loanCaseStatistic.getPlace());

            }

            //通过输出流将文件下载到客户端浏览器中
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);

            //关闭资源
            out.flush();
            out.close();
            excel.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @GetMapping("/financeEfficiency")
    @ApiOperation("获取资金效率表")
    public Result financeEfficiency(@DateTimeFormat(pattern = "yyyy-MM") LocalDate begin,
                                    @DateTimeFormat(pattern = "yyyy-MM") LocalDate end) {
        return ResultUtil.success();
    }

    @GetMapping("/financeBalance")
    @ApiOperation("获取资金余额")
    public Result financeBalance(@DateTimeFormat(pattern = "yyyy-MM") LocalDate begin,
                                 @DateTimeFormat(pattern = "yyyy-MM") LocalDate end) {
        return ResultUtil.success();
    }

    @GetMapping("/schedule")
    @ApiOperation("获取业务安排表")
    public Result schedule(@RequestParam Long caseId) {
        return ResultUtil.success();
    }

    //到处数据
    public void exportData(HttpServletResponse response, String templatePath) {
        LocalDate begin = LocalDate.now().minusDays(30);
        LocalDate end = LocalDate.now().minusDays(1);

        //查询概览运营数据，提供给Excel模板文件
        //BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(begin, LocalTime.MIN), LocalDateTime.of(end, LocalTime.MAX));

        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
        try {
            //基于提供好的模板文件创建一个新的Excel表格对象
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);

            //获得Excel文件中的一个Sheet页
            XSSFSheet sheet = excel.getSheet("Sheet1");
            sheet.getRow(1).getCell(1).setCellValue(begin + "至" + end);

            //获得第4行
            XSSFRow row = sheet.getRow(3);
            //获取单元格
//            row.getCell(2).setCellValue(businessData.getTurnover());
//            row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
//            row.getCell(6).setCellValue(businessData.getNewUsers());
//
//            row = sheet.getRow(4);
//            row.getCell(2).setCellValue(businessData.getValidOrderCount());
//            row.getCell(4).setCellValue(businessData.getUnitPrice());
//
//            for (int i = 0; i < 30; i++) {
//                LocalDate date = begin.plusDays(i);
//                //准备明细数据
//                businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
//                row = sheet.getRow(7 + i);
//                row.getCell(1).setCellValue(date.toString());
//                row.getCell(2).setCellValue(businessData.getTurnover());
//                row.getCell(3).setCellValue(businessData.getValidOrderCount());
//                row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
//                row.getCell(5).setCellValue(businessData.getUnitPrice());
//                row.getCell(6).setCellValue(businessData.getNewUsers());
//            }
            //通过输出流将文件下载到客户端浏览器中
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);

            //关闭资源
            out.flush();
            out.close();
            excel.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    // 示例：合并连续相同userName1的情况
    private void mergeSameCells(XSSFSheet sheet, List<LoanCaseStatistic> dataList, int columnIndex) {
        int startRow = 3; // 数据起始行
        String currentValue = null;
        int mergeStart = startRow;

        for (int i = 0; i < dataList.size(); i++) {
//            String value = getCellValueByColumn(dataList.get(i), columnIndex);
            String value = " ";
            if (currentValue == null || !currentValue.equals(value)) {
                // 发现不同的值，检查是否需要合并前面的单元格
                if (i > mergeStart) {
                    // 合并单元格
                    sheet.addMergedRegion(new CellRangeAddress(mergeStart, i + startRow - 1, columnIndex, columnIndex));
                }
                currentValue = value;
                mergeStart = i + startRow;
            }
        }

        // 处理最后一组
        if (mergeStart < dataList.size() + startRow - 1) {
            sheet.addMergedRegion(new CellRangeAddress(mergeStart, dataList.size() + startRow - 1, columnIndex, columnIndex));
        }
    }


}