snd-escloud/src/main/java/platform/modules/government/web/LandStatisticController.java

package platform.modules.government.web;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;
import platform.common.base.controller.BaseController;
import platform.common.util.excel.DynamicOneSheetEasyExcel;
import platform.common.util.excel.OneSheetEasyExcel;
import platform.modules.government.dto.ApproveStatisticMonthly;
import platform.modules.government.entity.Street;
import platform.modules.government.service.LandStatisticsService;
import platform.modules.government.service.StreetService;
import platform.modules.sys.web.ResponseMessage;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;


/**
 * 用地统计Controller
 */
@Controller
@RequestMapping("/land_statistic")
public class LandStatisticController extends BaseController {

    @Autowired
    private LandStatisticsService landStatisticsService;

    @Autowired
    private StreetService streetService;

    @GetMapping("/region/index")
    public String region() {

        return BASE_GOVERNMENT_PATH + "land_statistic/region";
    }

    @GetMapping("/plate/index")
    public String plate(ModelMap modelMap) {
        modelMap.put("streetList", streetService.findList());
        return BASE_GOVERNMENT_PATH + "land_statistic/plate";
    }

    /**
     * @Author: huZhiHao
     * @Description: 获取待审核 审核中 已审核 统计
     * @Date: 2020/1/13
     * @Params:
     * @Return:
     **/
    @GetMapping("/approve/status")
    @ResponseBody
    public ResponseMessage approveStatus(@RequestParam(value = "street_id", defaultValue = "", required = false) String streetId) {

        Object o = landStatisticsService.getApproveStatusStatistic(streetId);
        return ResponseMessage.success("获取数据成功", o);
    }

    /**
     * @Author: huZhiHao
     * @Description: 获取月度分类统计
     * @Date: 2020/1/13
     * @Params:
     * @Return:
     **/
    @GetMapping("/approve/type")
    @ResponseBody
    public ResponseMessage getTypeStatistic(
            @RequestParam(value = "street_id", defaultValue = "", required = false) String streetId,
            @RequestParam(value = "start_time", defaultValue = "", required = false) String startTime,
            @RequestParam(value = "end_time", defaultValue = "", required = false) String endTime
    ) {

        Object o = landStatisticsService.getTypeStatistic(streetId, startTime, endTime);
        return ResponseMessage.success("获取数据成功", o);
    }

    /**
     * @Author: huZhiHao
     * @Description: 获取月度审核结果对比
     * @Date: 2020/1/13
     * @Params:
     * @Return:
     **/
    @GetMapping("/approve/result/monthly")
    @ResponseBody
    public ResponseMessage getMonthlyApproveResult() {

        Object o = landStatisticsService.getMonthlyApproveResult();
        return ResponseMessage.success("获取数据成功", o);
    }

    /**
     * @Author: huZhiHao
     * @Description: 获取月度审核统计表
     * @Date: 2020/1/13
     * @Params:
     * @Return:
     **/
//    @GetMapping("/approve/statistic/monthly")
//    @ResponseBody
//    public ResponseMessage getMonthlyApproveStatistic(
//            @RequestParam(value = "street_id", defaultValue = "", required = false) String streetId,
//            @RequestParam(value = "start_time", defaultValue = "", required = false) String startTime,
//            @RequestParam(value = "end_time", defaultValue = "", required = false) String endTime
//    ) {
//
//        Object o = landStatisticsService.getMonthlyApproveStatistic(streetId, startTime, endTime);
//        return ResponseMessage.success("获取数据成功", o);
//    }

    /**
     * @Author: huZhiHao
     * @Description: 获取月度审核统计表
     * @Date: 2020/1/13
     * @Params:
     * @Return:
     **/
    @GetMapping("/approve/statistic/monthly")
    public String getMonthlyApproveStatistic(
            @RequestParam(value = "street_id", defaultValue = "", required = false) String streetId,
            @RequestParam(value = "start_time", defaultValue = "", required = false) String startTime,
            @RequestParam(value = "end_time", defaultValue = "", required = false) String endTime,
            ModelMap modelMap) {

        Object o = landStatisticsService.getMonthlyApproveStatistic(streetId, startTime, endTime);
        modelMap.put("list", o);
        return BASE_GOVERNMENT_PATH + "land_statistic/region::approve_statistic_monthly";
    }

    /**
     * @Author: huZhiHao
     * @Description: 获取月度审核统计表
     * @Date: 2020/1/13
     * @Params:
     * @Return:
     **/
//    @GetMapping("/approve/statistic/plate")
//    @ResponseBody
//    public ResponseMessage getPlateStatistic(
//            @RequestParam(value = "start_time", defaultValue = "", required = false) String startTime,
//            @RequestParam(value = "end_time", defaultValue = "", required = false) String endTime
//    ) {
//
//        Object o = landStatisticsService.getPlateStatistic(startTime, endTime);
//        return ResponseMessage.success("获取数据成功", o);
//    }

    /**
     * @Author: huZhiHao
     * @Description: 获取板块统计表
     * @Date: 2020/1/13
     * @Params:
     * @Return:
     **/
    @GetMapping("/approve/statistic/plate")
    public String getPlateStatistic(
            @RequestParam(value = "start_time", defaultValue = "", required = false) String startTime,
            @RequestParam(value = "end_time", defaultValue = "", required = false) String endTime,
            ModelMap modelMap) {

        Map o = (Map) landStatisticsService.getPlateStatistic(startTime, endTime);
        modelMap.put("head", o.get("head"));
        modelMap.put("data", o.get("data"));
        return BASE_GOVERNMENT_PATH + "land_statistic/region::approve_statistic_plate";
    }

    /**
     * @Author: huZhiHao
     * @Description: 获取每日统计表
     * @Date: 2020/1/13
     * @Params:
     * @Return:
     **/
    @GetMapping("/approve/statistic/daily")
    @ResponseBody
    public ResponseMessage getDailyStatistic(
            @RequestParam(value = "street_id", defaultValue = "", required = false) String streetId,
            @RequestParam(value = "start_time", defaultValue = "", required = false) String startTime,
            @RequestParam(value = "end_time", defaultValue = "", required = false) String endTime
    ) throws ParseException {

        Map o = (Map) landStatisticsService.getDailyStatistic(streetId, startTime, endTime);
        return ResponseMessage.success("获取数据成功", o);
    }

    /*
     * 导出月度审核统计
     * */
    @GetMapping("/approve/statistic/monthly/export")
    public ResponseMessage approveStatisticMonthlyExport(
            HttpServletResponse response,
            @RequestParam(value = "street_id", defaultValue = "", required = false) String streetId,
            @RequestParam(value = "start_time", defaultValue = "", required = false) String startTime,
            @RequestParam(value = "end_time", defaultValue = "", required = false) String endTime) throws Exception {
        // 文件输出位置
        /*String outPath = "C:\\Users\\oukele\\Desktop\\test.xlsx";
        try {
            // 所有行的集合
            List<List<Object>> list = new ArrayList<List<Object>>();

            for (int i = 1; i <= 10; i++) {
                // 第 n 行的数据
                List<Object> row = new ArrayList<Object>();
                row.add("第" + i + "单元格");
                row.add("第" + i + "单元格");
                list.add(row);
            }

            ExcelWriter excelWriter = EasyExcelFactory.getWriter(new FileOutputStream(outPath));
            // 表单
            Sheet sheet = new Sheet(1,0);
            sheet.setSheetName("第一个Sheet");
            // 创建一个表格
            Table table = new Table(1);
            // 动态添加 表头 headList --> 所有表头行集合
            List<List<String>> headList = new ArrayList<List<String>>();
            // 第 n 行 的表头
            List<String> headTitle0 = new ArrayList<String>();
            List<String> headTitle1 = new ArrayList<String>();
            List<String> headTitle2 = new ArrayList<String>();
            headTitle0.add("最顶部-1");
            headTitle0.add("标题1");
            headTitle1.add("最顶部-1");
            headTitle1.add("标题2");
            headTitle2.add("最顶部-1");
            headTitle2.add("标题3");

            headList.add(headTitle0);
            headList.add(headTitle1);
            headList.add(headTitle2);
            table.setHead(headList);

            excelWriter.write1(list,sheet,table);
            // 记得 释放资源
            excelWriter.finish();
            System.out.println("ok");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }*/

        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("全区统计-月度审核统计表", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        List<Map> list = (List<Map>) landStatisticsService.getMonthlyApproveStatistic(streetId, startTime, endTime);
        List<ApproveStatisticMonthly> data = new ArrayList<>();
        for (Map map : list) {
            ApproveStatisticMonthly approveStatisticMonthly = new ApproveStatisticMonthly();

            approveStatisticMonthly.setRowName(map.get("rowName") + "");
            approveStatisticMonthly.setRentApplyNum(map.get("rentApplyNum") + "");
            approveStatisticMonthly.setRentPassNum(map.get("rentPassNum") + "");
            approveStatisticMonthly.setRentPassRate(map.get("rentPassRate") + "%");
            approveStatisticMonthly.setUseChangeApplyNum(map.get("useChangeApplyNum") + "");
            approveStatisticMonthly.setUseChangePassNum(map.get("useChangePassNum") + "");
            approveStatisticMonthly.setUseChangePassRate(map.get("useChangePassRate") + "%");
            approveStatisticMonthly.setImmovablesChangeApplyNum(map.get("immovablesChangeApplyNum") + "");
            approveStatisticMonthly.setImmovablesChangePassNum(map.get("immovablesChangePassNum") + "");
            approveStatisticMonthly.setImmovablesChangePassRate(map.get("immovablesChangePassRate") + "%");
            approveStatisticMonthly.setEquityStructureChangeApplyNum(map.get("equityStructureChangeApplyNum") + "");
            approveStatisticMonthly.setEquityStructureChangePassNum(map.get("equityStructureChangePassNum") + "");
            approveStatisticMonthly.setEquityStructureChangePassRate(map.get("equityStructureChangePassRate") + "");
            approveStatisticMonthly.setTotalApplyNum(map.get("totalApplyNum") + "");
            approveStatisticMonthly.setTotalPassNum(map.get("totalPassNum") + "");
            approveStatisticMonthly.setTotalPassRate(map.get("totalPassRate") + "%");

            data.add(approveStatisticMonthly);
        }
        EasyExcel.write(response.getOutputStream(), ApproveStatisticMonthly.class).sheet("sheet1").doWrite(data);
        return ResponseMessage.success("获取数据成功");
    }

    /*
     * 导出月度审核统计
     * */
    @GetMapping("/approve/statistic/plate/export")
    public ResponseMessage approveStatisticMonthlyExport(
            HttpServletResponse response,
            @RequestParam(value = "start_time", defaultValue = "", required = false) String startTime,
            @RequestParam(value = "end_time", defaultValue = "", required = false) String endTime) throws Exception {

        Map o = (Map) landStatisticsService.getPlateStatistic(startTime, endTime);

        List<Street> headers = (List<Street>) o.get("head");
        List<Map> datas = (List<Map>) o.get("data");

        //填充数据实体
        List<List<String>> data = new ArrayList<>();

        for (Map map : datas) {
            String name = (String) map.get("name");
            int[] num = (int[]) map.get("num");

            List<String> dataList = new ArrayList<>();
            dataList.add(name);
            for (int i : num) {
                dataList.add(i + "");
            }
            data.add(dataList);

            List<Map> ResultList = (List<Map>) map.get("type");
            for (Map map1 : ResultList) {
                String name1 = (String) map1.get("name");
                List<String> dataList1 = new ArrayList<>();
                dataList1.add(name1);
                if (Objects.equals("存量工业用地项目公司股权（结构）变更登记", name1)
                        ||Objects.equals("存量工业用地不动产权权属转移登记", name1)) {
                    String[] num1 = (String[]) map1.get("streetNum");
                    for (String i : num1) {
                        dataList1.add(i);
                    }
                    data.add(dataList1);
                } else {
                    int[] num1 = (int[]) map1.get("streetNum");
                    for (int i : num1) {
                        dataList1.add(i + "");
                    }
                    data.add(dataList1);
                }
            }
            List<String> empty = new ArrayList<>();
            empty.add("");
            data.add(empty);
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("板块统计表", "UTF-8");

        OneSheetEasyExcel excel = new DynamicOneSheetEasyExcel();

        // 设置excel文件名称
        excel.setName(fileName);
        // 设置sheet名称，只导出一个sheet，所以只设置一次
        excel.setSheetName("sheet1");
        // 按顺序设置列头
//        excel.setHeader("姓名")
//                .setHeader("年龄")
//                .setHeader("性别");
        for (Street street : headers) {
            excel.setHeader(street.getName());
        }

        // 设置返回文件名称
        response.setHeader("Content-disposition", "attachment;filename=" + excel.getName());

        // 准备数据 ， 下面这部分代码可以直接由service里完成
//        List<List<String>> data = new ArrayList<>();

//        List<Person> list = new ArrayList<>();
//        list.add(new Person("陈1", 34, false));
//        list.add(new Person("王2", 33, true));
//        list.add(new Person("三3", 7, true));
//        list.forEach(person -> {
//            data.add(
//                    Arrays.asList(
//                            person.getName(),
//                            person.getAge().toString(),
//                            person.getSex().toString()
//                    ));
//        });
        // 将准备好的data 塞入excel对象里，实现导出
        excel.exportData(data, response);
        return ResponseMessage.success("获取数据成功");
    }
}