gdc-finance-api/src/main/java/com/example/controller/WzHouseMaintenaCostController.java

package com.example.controller;

import com.example.config.annotation.IgnoreAuth;
import com.example.entity.OtnAreaEntity;
import com.example.entity.WzHouseMaintEntity;
import com.example.entity.WzHouseMaintenaCostEntity;
import com.example.entity.vo.DailyModifyVo;
import com.example.entity.vo.HighFrequencyVo;
import com.example.entity.vo.housecost.HouseCostVo;
import com.example.service.WzHouseMaintService;
import com.example.service.WzHouseMaintenaCostService;
import com.example.service.WzOtnAreaService;
import com.example.service.WzStrategyService;
import com.example.utils.PageMap;
import com.example.utils.PageUtils;
import com.example.utils.Query;
import com.example.utils.R;
import com.example.utils.excel.ExcelExport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/house-car/house/dist/api/wzHouseMaintenaCost")
public class WzHouseMaintenaCostController {

    @Autowired
    private WzOtnAreaService otnAreaService;
    @Autowired
    private WzHouseMaintenaCostService wzHouseMaintenaCostService;
    @Autowired
    private WzHouseMaintService wzHouseMaintService;
    @Autowired
    private WzStrategyService wzStrategyService;

//    /**
//     * 查看列表
//     */
//    @RequestMapping("/list")
//    @IgnoreAuth
//    public R list(@RequestBody PageMap pageMap) {
//        //查询列表数据
//        Query query = new Query(pageMap);
//        List<WzHouseMaintenaCostEntity> resultList = wzHouseMaintenaCostService.queryList(query);
//        Query query1 = new Query(pageMap);
//        int total = wzHouseMaintenaCostService.queryTotal(query1);
//        PageUtils pageUtil = new PageUtils(resultList, total, query.getLimit(), query.getPage());
//        return R.ok().put("page", pageUtil);
//    }

//    /**
//     * 查看列表
//     */
//    @RequestMapping("/list1")
//    @IgnoreAuth
//    public R list1(@RequestBody PageMap pageMap) {
//        //查询列表数据
//        Query query = new Query(pageMap);
//        List<WzHouseMaintenaCostEntity> resultList = wzHouseMaintenaCostService.queryList1(query);
//        //每平方米维修单价
//        for (WzHouseMaintenaCostEntity wzHouseMaintenaCostEntity : resultList) {
//            wzHouseMaintenaCostEntity.setAveragePrice(formatDouble(wzHouseMaintenaCostEntity.getAveragePrice()));
//        }
//        Query query1 = new Query(pageMap);
//        int total = wzHouseMaintenaCostService.queryTotal1(query1);
//        PageUtils pageUtil = new PageUtils(resultList, total, query.getLimit(), query.getPage());
//        return R.ok().put("page", pageUtil);
//    }


    @RequestMapping("/queryVoList")
    @IgnoreAuth
    public R queryVoList(@RequestBody PageMap pageMap) {
        //查询列表数据
        Query query = new Query(pageMap);
        List<WzHouseMaintenaCostEntity> resultList = wzHouseMaintenaCostService.queryList(query);
        int total = wzHouseMaintenaCostService.queryTotal(query);
        PageUtils pageUtil = new PageUtils(resultList, total, query.getLimit(), query.getPage());
        return R.ok().put("page", pageUtil);
    }

//    @RequestMapping("/queryEchart")
//    @IgnoreAuth
//    public R queryEchart(@RequestBody PageMap pageMap) {
//        //查询列表数据
//        List<WzHouseMaintenaCostEntity> alllist = wzHouseMaintenaCostService.queryList1(pageMap.getMap());
//        //全省节点
//        HouseCostVo province = new HouseCostVo();
//        province.setCounty("全省");
//        province.setCity("全省");
//
//        double aProjectCost = 0l;
//        double aSmallCost = 0l;
//        double aWarnCost = 0l;
//        double aProCost = 0l;
//        double aOtherCost = 0l;
//        double aAllCost = 0l;
//        //按地市进行划分
//        Map<String, List<WzHouseMaintenaCostEntity>> cityMap = new HashMap<>();
//        for (WzHouseMaintenaCostEntity entity : alllist) {
//            if (entity.getCity() == null) continue;
//            List<WzHouseMaintenaCostEntity> list;
//            if (cityMap.get(entity.getCity()) != null) {
//                list = cityMap.get(entity.getCity());
//                list.add(entity);
//            } else {
//                list = new ArrayList<>();
//                list.add(entity);
//            }
//            cityMap.put(entity.getCity(), list);
//        }
//        //按区进行划分
//        List<HouseCostVo> resultList = new ArrayList<>();
//        List<HouseCostVo> cityList = new ArrayList<>();
//        for (Map.Entry<String, List<WzHouseMaintenaCostEntity>> entry : cityMap.entrySet()) {
//            List<WzHouseMaintenaCostEntity> list = entry.getValue();
//            //县的Map集合
//            Map<String, List<WzHouseMaintenaCostEntity>> countyMap = new HashMap<>();
//            for (WzHouseMaintenaCostEntity leaveUseVo : list) {
//                if (countyMap.get(leaveUseVo.getCounty()) != null) {
//                    list = countyMap.get(leaveUseVo.getCounty());
//                    list.add(leaveUseVo);
//                } else {
//                    list = new ArrayList<>();
//                    list.add(leaveUseVo);
//                }
//                countyMap.put(leaveUseVo.getCounty(), list);
//            }
//
//            List<HouseCostVo> countyList = new ArrayList<>();
//            double cityProjectCost = 0l;
//            double citySmallCost = 0l;
//            double cityWarnCost = 0l;
//            double cityProCost = 0l;
//            double cityOtherCost = 0l;
//            double cityAllCost = 0l;
//            for (Map.Entry<String, List<WzHouseMaintenaCostEntity>> countyEntry : countyMap.entrySet()) {
//                double projectCost = 0l;
//                double smallCost = 0l;
//                double warnCost = 0l;
//                double proCost = 0l;
//                double otherCost = 0l;
//                double allCost = 0l;
//
//                HouseCostVo county = new HouseCostVo();
//                for (WzHouseMaintenaCostEntity leaveUseVo : countyEntry.getValue()) {
//                    county.setCity(leaveUseVo.getCounty());
//                    if (leaveUseVo.getRepairType().equals("项目制维修")) {
//                        projectCost += leaveUseVo.getErpCreditedAmount();
//                        cityProjectCost += leaveUseVo.getErpCreditedAmount();
//                        aProjectCost += leaveUseVo.getErpCreditedAmount();
//                        allCost +=leaveUseVo.getErpCreditedAmount();
//                        cityAllCost +=leaveUseVo.getErpCreditedAmount();
//                        aAllCost +=leaveUseVo.getErpCreditedAmount();
//                    } else if (leaveUseVo.getRepairType().equals("零星维修")) {
//                        smallCost += leaveUseVo.getErpCreditedAmount();
//                        citySmallCost += leaveUseVo.getErpCreditedAmount();
//                        aSmallCost += leaveUseVo.getErpCreditedAmount();
//                        allCost +=leaveUseVo.getErpCreditedAmount();
//                        cityAllCost +=leaveUseVo.getErpCreditedAmount();
//                        aAllCost +=leaveUseVo.getErpCreditedAmount();
//                    } else if (leaveUseVo.getRepairType().equals("应急维修")) {
//                        warnCost += leaveUseVo.getErpCreditedAmount();
//                        cityWarnCost += leaveUseVo.getErpCreditedAmount();
//                        aWarnCost += leaveUseVo.getErpCreditedAmount();
//                        allCost +=leaveUseVo.getErpCreditedAmount();
//                        cityAllCost +=leaveUseVo.getErpCreditedAmount();
//                        aAllCost +=leaveUseVo.getErpCreditedAmount();
//                    } else if (leaveUseVo.getRepairType().equals("安防费用")) {
//                        proCost += leaveUseVo.getErpCreditedAmount();
//                        cityProCost += leaveUseVo.getErpCreditedAmount();
//                        aProCost += leaveUseVo.getErpCreditedAmount();
//                        allCost +=leaveUseVo.getErpCreditedAmount();
//                        cityAllCost +=leaveUseVo.getErpCreditedAmount();
//                        aAllCost +=leaveUseVo.getErpCreditedAmount();
//                    } else {
//                        if(leaveUseVo.getRepairType().contains("预提冲销")){
//                            continue;
//                        }
//                        otherCost += leaveUseVo.getErpCreditedAmount();
//                        cityOtherCost += leaveUseVo.getErpCreditedAmount();
//                        aOtherCost += leaveUseVo.getErpCreditedAmount();
//                    }
////                    allCost += projectCost + smallCost + warnCost + proCost + otherCost;
////                    cityAllCost += cityProjectCost + citySmallCost + cityWarnCost + cityProCost + cityOtherCost;
////                    aAllCost += aProjectCost + aSmallCost + aWarnCost + aProCost + aOtherCost;
//
//                }
//                county.setProjectCost(formatDouble(projectCost));
//                county.setProCost(formatDouble(proCost));
//                county.setOtherCost(formatDouble(otherCost));
//                county.setSmallCost(formatDouble(smallCost));
//                county.setWarnCost(formatDouble(warnCost));
//                county.setAllCost(formatDouble(allCost));
//                countyList.add(county);
//            }
//
//            List<HouseCostVo> orderCountyList = new ArrayList<>();
//            try {
//                //对countList 进行排序
//                String city = list.get(0).getCity();
//                if (city.contains("市")) {
//                    city = city.split("市")[0];
//                }
//                OtnAreaEntity area = otnAreaService.queryObject(city);
//                List<OtnAreaEntity> countyAreaList = otnAreaService.getCityOption(area.getId());
//                int i=0;
//                for (OtnAreaEntity otnAreaEntity : countyAreaList) {
//                    for (HouseCostVo wzCity : countyList) {
//                        if (otnAreaEntity.getName().equals(wzCity.getCity())) {
//                            i++;
//                            wzCity.setFlag(i%2==0);
//                            orderCountyList.add(wzCity);
//                        }
//                    }
//                }
//            } catch (Exception e) {
//                orderCountyList = countyList;
//                e.printStackTrace();
//            }
//
//            HouseCostVo parent = new HouseCostVo();
//            parent.setProCost(formatDouble(cityProCost));
//            parent.setWarnCost(formatDouble(cityWarnCost));
//            parent.setProjectCost(formatDouble(cityProjectCost));
//            parent.setOtherCost(formatDouble(cityOtherCost));
//            parent.setSmallCost(formatDouble(citySmallCost));
//            parent.setAllCost(formatDouble(cityAllCost));
//            parent.setCity(list.get(0).getCity());
//            parent.setChildren(orderCountyList);
//            cityList.add(parent);
//        }
//
//        //对cityList 进行排序
//        List<OtnAreaEntity> areaList = otnAreaService.getCityOption("018");
//        List<HouseCostVo> orderList = new ArrayList<>();
//        int i=0;
//        for (OtnAreaEntity otnAreaEntity : areaList) {
//            for (HouseCostVo wzCity : cityList) {
//                if (wzCity.getCity().contains(otnAreaEntity.getName())) {
//                    wzCity.setFlag(i%2==0);
//                    orderList.add(wzCity);
//                    i++;
//                }
//            }
//        }
//
//        province.setChildren(orderList);
//        province.setAllCost(formatDouble(aAllCost));
//        province.setSmallCost(formatDouble(aSmallCost));
//        province.setOtherCost(formatDouble(aOtherCost));
//        province.setProjectCost(formatDouble(aProjectCost));
//        province.setProCost(formatDouble(aProCost));
//        province.setWarnCost(formatDouble(aWarnCost));
//        resultList.add(province);
//
//        PageUtils pageUtil = new PageUtils(resultList, 0, 10, 10);
//        return R.ok().put("page", pageUtil);
//    }

    /**
     * 同维修内容频次统计
     */
//    @RequestMapping("/queryHighFrequency")
//    @IgnoreAuth
//    public R queryHighFrequency(@RequestBody PageMap pageMap) {
//        Query query = new Query(pageMap);
//        List<HighFrequencyVo> highPriceList = wzHouseMaintenaCostService.queryHighFrequency(query);
//
//        Query query1 = new Query(pageMap);
//        query1.put("sidx","num");
//        query1.put("order","desc");
//        query1.put("limit",20);
//        query1.put("page",1);
//        query1.put("offset",0);
//        List<HighFrequencyVo> allhighPriceList = wzHouseMaintenaCostService.queryHighFrequency(query1);
//
//        int i=1;
//        for (HighFrequencyVo highFrequencyVo : allhighPriceList) {
//            highFrequencyVo.setOrder(i);
//            i++;
//        }
//        for (HighFrequencyVo highFrequencyVo : highPriceList) {
//            for (HighFrequencyVo item : allhighPriceList) {
//                if(item.getBuildingAlias()==null&&highFrequencyVo.getBuildingAlias()!=null){
//                    continue;
//                }
//                if(item.getBuildingAlias()!=null&&highFrequencyVo.getBuildingAlias()==null){
//                    continue;
//                }
//                if(item.getCity()==null&&highFrequencyVo.getCity()!=null){
//                    continue;
//                }
//                if(item.getCity()!=null&&highFrequencyVo.getCity()==null){
//                    continue;
//                }
//                if(item.getRepairType()==null&&highFrequencyVo.getRepairType()!=null){
//                    continue;
//                }
//                if(item.getRepairType()!=null&&highFrequencyVo.getRepairType()==null){
//                    continue;
//                }
//               if(((item.getCity()==null&&highFrequencyVo.getCity()==null)||item.getCity().equals(highFrequencyVo.getCity()))
//                       &&((item.getBuildingAlias()==null&&highFrequencyVo.getBuildingAlias()==null)||item.getBuildingAlias().equals(highFrequencyVo.getBuildingAlias()))
//                       &&((item.getRepairType()==null&&highFrequencyVo.getRepairType()==null)||item.getRepairType().equals(highFrequencyVo.getRepairType()))){
//                   highFrequencyVo.setOrder(item.getOrder());
//                   break;
//               }
//            }
//        }
//        PageUtils pageUtil = new PageUtils(highPriceList, 20, 10, 10);
//        return R.ok().put("page", pageUtil);
//    }


    /**
     * 清单excel
     */
    @GetMapping("/excel")
    @IgnoreAuth
    public void excel(String name,
                      String statisticalMonth,
                      String buildingNameAlias,
                      String investmentEntity,
                      String city,
                      String county,
                      String code,
                      HttpServletResponse response) {

        Map<String, Object> map = new HashMap<>();
        if(statisticalMonth==null||statisticalMonth.equals("")||statisticalMonth.equals("undefined")){
        }else{
            map.put("statisticalMonth",statisticalMonth);
        }
        if(investmentEntity==null||investmentEntity.equals("")||investmentEntity.equals("undefined")){
        }else{
            map.put("investmentEntity",investmentEntity);
        }
        if(code==null||code.equals("")||code.equals("undefined")){
        }else{
            map.put("code",code);
        }
        if (buildingNameAlias != null) {
            map.put("buildingNameAlias", buildingNameAlias);
        }
        if (city != null) {
            map.put("city", city);
        }
        if (county != null) {
            map.put("county", county);
        }

        DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        ExcelExport ee1 = new ExcelExport("报表" + df.format(new Date()));
        //判定是闲置管理还是记录
        if (name.contains("相同维修频次统计")) {
            ee1 = new ExcelExport("维修异常监控_相同维修频次统计" + df.format(new Date()));
            responseTab1(df, ee1, map);
            ee1.export(response);
        } else if (name.contains("高总价统计")) {
            ee1 = new ExcelExport("维修异常监控_高总价统计" + df.format(new Date()));
            responseTab2(df,ee1,map);
            ee1.export(response);
        } else if (name.contains("高额日常零星维修")) {
            ee1 = new ExcelExport("维修异常监控_高额日常零星维修" + df.format(new Date()));
            responseTab3(df,ee1,map);
            ee1.export(response);
        } else if (name.contains("不可用发生维修费")) {
            ee1 = new ExcelExport("维修异常监控_闲置+不可用发生维修费" + df.format(new Date()));
            responseTab4(df,ee1,map);
            ee1.export(response);
        } else if (name.contains("房产3年无维修")) {
            ee1 = new ExcelExport("维修异常监控_房产3年无维修" + df.format(new Date()));
            responseTab5(df,ee1,map);
            ee1.export(response);
        } else if (name.contains("维修费统计")){
            ee1 = new ExcelExport("房屋维修_维修费统计" + df.format(new Date()));
            responseTab6(df,ee1,map);
            ee1.export(response);
        }else if (name.contains("维修费详表")){
            ee1 = new ExcelExport("房屋维修_维修费记录" + df.format(new Date()));
            responseTab7(df,ee1,map);
            ee1.export(response);
        }
    }

    public void responseTab1(DateFormat df, ExcelExport ee1, Map<String, Object> map1) {
        Query query = new Query(map1);
        List<HighFrequencyVo> resultList = wzHouseMaintenaCostService.queryHighFrequency(query);

        Query query1 = new Query(map1);
        query1.put("sidx","num");
        query1.put("order","desc");
        query1.put("limit",20);
        query1.put("page",1);
        query1.put("offset",0);
        List<HighFrequencyVo> allhighPriceList = wzHouseMaintenaCostService.queryHighFrequency(query1);

        int j=1;
        for (HighFrequencyVo highFrequencyVo : allhighPriceList) {
            highFrequencyVo.setOrder(j);
            j++;
        }
        for (HighFrequencyVo highFrequencyVo : resultList) {
            for (HighFrequencyVo item : allhighPriceList) {
                if(item.getBuildingAlias()==null&&highFrequencyVo.getBuildingAlias()!=null){
                    continue;
                }
                if(item.getBuildingAlias()!=null&&highFrequencyVo.getBuildingAlias()==null){
                    continue;
                }
                if(item.getCity()==null&&highFrequencyVo.getCity()!=null){
                    continue;
                }
                if(item.getCity()!=null&&highFrequencyVo.getCity()==null){
                    continue;
                }
                if(item.getRepairType()==null&&highFrequencyVo.getRepairType()!=null){
                    continue;
                }
                if(item.getRepairType()!=null&&highFrequencyVo.getRepairType()==null){
                    continue;
                }
                if(((item.getCity()==null&&highFrequencyVo.getCity()==null)||item.getCity().equals(highFrequencyVo.getCity()))
                        &&((item.getBuildingAlias()==null&&highFrequencyVo.getBuildingAlias()==null)||item.getBuildingAlias().equals(highFrequencyVo.getBuildingAlias()))
                        &&((item.getRepairType()==null&&highFrequencyVo.getRepairType()==null)||item.getRepairType().equals(highFrequencyVo.getRepairType()))){
                    highFrequencyVo.setOrder(item.getOrder());
                    break;
                }
            }
        }
        List<Object> colList = new ArrayList();;
        List<Object[]> allList = new ArrayList();
        for (int i = 0; i < resultList.size(); i++) {
            colList = new ArrayList();
            colList.add(resultList.get(i).getCity());
            colList.add(resultList.get(i).getBuildingAlias());
            colList.add(resultList.get(i).getRepairType());
            colList.add(resultList.get(i).getTime());
            colList.add(resultList.get(i).getNum());
            colList.add(resultList.get(i).getOrder());
            allList.add(colList.toArray());

        }
        String[] header = new String[]{
                "单位",
                "维修建筑",
                "维修类型",
                "时间",
                "发生频率",
                "排名"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }
    /**
     *  高总价维修统计
     */
    public void responseTab2(DateFormat df, ExcelExport ee1, Map<String, Object> map1) {
        Query query = new Query(map1);
        List<WzHouseMaintenaCostEntity> resultList = wzHouseMaintenaCostService.queryHighPrice(query);
        List<Object> colList;
        List<Object[]> allList = new ArrayList();
        for (int i = 0; i < resultList.size(); i++) {
            colList = new ArrayList();
            colList.add(resultList.get(i).getStatisticalMonth());
            colList.add(resultList.get(i).getCity());
            colList.add(resultList.get(i).getCounty());
            colList.add(resultList.get(i).getBuildingAlias());
            colList.add(resultList.get(i).getRepairType());
            colList.add(resultList.get(i).getRepairContent());
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getErpCreditedAmount(),true));
            colList.add(resultList.get(i).getStandardAddress());
            colList.add(resultList.get(i).getBuildingUse());
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaUseArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaRentArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaUnavailable(),true));
            allList.add(colList.toArray());
        }
        String[] header = new String[]{
                "入账日期",
                "二级单位",
                "三级单位",
                "建筑别名",
                "维修类型",
                "维修内容",
                "erp入账金额元",
                "标准地址",
                "建筑用途",
                "建筑面积㎡",
                "建筑面积自用㎡",
                "建筑面积出租㎡",
                "建筑面积闲置㎡"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }

    public void responseTab3(DateFormat df, ExcelExport ee1, Map<String, Object> map1) {
        map1.put("repairType", "零星维修");
        map1.put("erpCreditedAmount", 10000);
        Query query=new Query(map1);
        List<DailyModifyVo> resultList = wzHouseMaintenaCostService.queryDailyModify(query);

        List<Object> colList = new ArrayList();;
        List<Object[]> allList = new ArrayList();
        for (int i = 0; i < resultList.size(); i++) {
            colList = new ArrayList();
            colList.add(resultList.get(i).getCity());
            colList.add(resultList.get(i).getBuildingAlias());
            colList.add(resultList.get(i).getDate());
            colList.add(resultList.get(i).getRepairType());
            colList.add(resultList.get(i).getRepairContent());
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getErpMoney(),true));
            colList.add(resultList.get(i).getNum());
            allList.add(colList.toArray());

        }
        String[] header = new String[]{
                "单位",
                "维修建筑",
                "维修时间",
                "维修类型",
                "维修内容",
                "维修金额",
                "本年超万元次数"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }

    public void responseTab4(DateFormat df, ExcelExport ee1, Map<String, Object> map1) {
        List<WzHouseMaintenaCostEntity> resultList = wzHouseMaintenaCostService.queryList(map1);
        List<Object> colList = new ArrayList();;
        List<Object[]> allList = new ArrayList();
        for (int i = 0; i < resultList.size(); i++) {
            colList = new ArrayList();
            colList.add(resultList.get(i).getStatisticalMonth());
            colList.add(resultList.get(i).getCity());
            colList.add(resultList.get(i).getCounty());
            colList.add(resultList.get(i).getBuildingAlias());
            colList.add(resultList.get(i).getRepairType());
            colList.add(resultList.get(i).getRepairContent());
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getErpCreditedAmount(),true));
            colList.add(resultList.get(i).getStandardAddress());
            colList.add(resultList.get(i).getUseState());
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaUseArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaRentArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaIdelArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaUnavailable(),true));
            allList.add(colList.toArray());

        }
        String[] header = new String[]{
                "入账日期",
                "二级单位",
                "三级单位",
                "建筑别名",
                "维修类型",
                "维修内容",
                "erp入账金额元",
                "标准地址",
                "使用状态",
                "建筑面积㎡",
                "建筑面积自用㎡",
                "建筑面积出租㎡",
                "建筑面积闲置㎡",
                "建筑面积不可使用㎡"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }

    public void responseTab5(DateFormat df, ExcelExport ee1, Map<String, Object> map1) {
        List<WzHouseMaintEntity> list = wzHouseMaintService.queryList(map1);
        //对cityList 进行排序
        List<OtnAreaEntity> areaList = otnAreaService.getCityOption("018");
        List<WzHouseMaintEntity> resultList = new ArrayList<>();
        for (OtnAreaEntity otnAreaEntity : areaList) {
            for (WzHouseMaintEntity wzCity : list) {
                if (wzCity.getCity().contains(otnAreaEntity.getName())) {
                    wzCity.setAll();
                    resultList.add(wzCity);
                }
            }
        }
        List<Object> colList;
        List<Object[]> allList = new ArrayList();
        for (int i = 0; i < resultList.size(); i++) {
            colList = new ArrayList();
            colList.add(resultList.get(i).getCity());
            colList.add("");
            colList.add(resultList.get(i).getHouseAgeLessThanTwenty());
            colList.add(resultList.get(i).getHouseAgeBetweentTewntyAndForty());
            colList.add(resultList.get(i).getHouseAgeMoreThanFortyOne());
            colList.add(resultList.get(i).getHouseAll());
            colList.add("");
            colList.add(resultList.get(i).getBuildingAgeLessThanTwenty());
            colList.add(resultList.get(i).getBuildingAgeBetweentTewntyAndForty());
            colList.add(resultList.get(i).getBuildingAgeMoreThanFortyOne());
            colList.add(resultList.get(i).getBuildingAll());
            allList.add(colList.toArray());
        }
        String[] header = new String[]{
                "单位",
                "主业用房",
                "房龄<20年",
                "房龄21~40年",
                "房龄>41年",
                "总计",
                "全部建筑",
                "房龄<20年",
                "房龄21~40年",
                "房龄>41年",
                "总计"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }

    /**
     * 高总价统计
     */
//    @RequestMapping("/queryHighPrice")
//    @IgnoreAuth
//    public R queryHighPrice(@RequestBody PageMap pageMap) {
//        Query query = new Query(pageMap);
//        WzStrategyEntity objectByType = wzStrategyService.getObjectByType();
//        int value=0;
//        if(objectByType!=null){
//            String[] values=objectByType.getValue().split(",");
//            value=Integer.valueOf(values[2]);
//            query.put("erpCreditedAmount", value);
//        }
//        List<WzHouseMaintenaCostEntity> highPriceList = wzHouseMaintenaCostService.queryHighPrice(query);
//        PageUtils pageUtil = new PageUtils(highPriceList, highPriceList.size(), 10, 10);
//        return R.ok().put("page", pageUtil);
//    }

    /**
     * 日常零星维修
     */
//    @RequestMapping("/queryDailyModify")
//    @IgnoreAuth
//    public R queryDailyModify(@RequestBody PageMap pageMap) {
//        Query query = new Query(pageMap);
//        Query query1 = new Query(pageMap);
//        //获取配置参数
//        int value=10000;
//        WzStrategyEntity objectByType = wzStrategyService.getObjectByType();
//        if(objectByType!=null){
//            String[] values=objectByType.getValue().split(",");
//            value=Integer.valueOf(values[3]);
//            query.put("erpCreditedAmount", value);
//            query1.put("erpCreditedAmount", value);
//        }
//        query.put("repairType", "零星维修");
//        List<DailyModifyVo> highPriceList = wzHouseMaintenaCostService.queryDailyModify(query);
//        query1.put("repairType", "零星维修");
//
//        int total = wzHouseMaintenaCostService.queryDailyModifyTotal(query1);
//        PageUtils pageUtil = new PageUtils(highPriceList, total, 10, 10);
//        return R.ok().put("page", pageUtil);
//    }

    /**
     * 闲置+不可用发生维修费
     */
    @RequestMapping("/queryHappenCost")
    @IgnoreAuth
    public R queryHappenCost(@RequestBody PageMap pageMap) {
        Query query = new Query(pageMap);
        query.put("useAreaSelfUseArea", 0);
        query.put("useAreaRentArea", 0);
        List<WzHouseMaintenaCostEntity> highPriceList = wzHouseMaintenaCostService.queryList(query);
        Query query1 = new Query(pageMap);
        query1.put("useAreaSelfUseArea", 0);
        query1.put("useAreaRentArea", 0);
        int total = wzHouseMaintenaCostService.queryTotal(query1);
        PageUtils pageUtil = new PageUtils(highPriceList, total, 10, 10);
        return R.ok().put("page", pageUtil);
    }

//    /**
//     * 获取局址别名
//     */
//    @RequestMapping("/getBuildingNameCode")
//    @IgnoreAuth
//    public R getBuildingNameCode(@RequestBody PageMap pageMap) {
//        //查询列表数据
//        Query query = new Query(pageMap);
//        List<String> list = wzHouseMaintenaCostService.getBuildingNameCode(query);
//        Query query1 = new Query(pageMap);
//        int total = wzHouseMaintenaCostService.queryBuildDetailByCodeTotal(query1);
//        PageUtils pageUtil = new PageUtils(list, total, query.getLimit(), query.getPage());
//        return R.ok().put("page", pageUtil);
//    }
//
//
//    /**
//     * 获取局址别名
//     */
//    @RequestMapping("/getBuildingNameAlias")
//    @IgnoreAuth
//    public R getBuildingNameAlias(@RequestBody PageMap pageMap) {
//        //查询列表数据
//        Query query = new Query(pageMap);
//        List<String> list = wzHouseMaintenaCostService.getBuildingNameAlias(query);
//        Query query1 = new Query(pageMap);
//        int total = wzHouseMaintenaCostService.getBuildingNameAliasTotal(query1);
//        PageUtils pageUtil = new PageUtils(list, total, query.getLimit(), query.getPage());
//        return R.ok().put("page", pageUtil);
//    }

    public void responseTab6(DateFormat df, ExcelExport ee1, Map<String, Object> map1){
        List<WzHouseMaintenaCostEntity> alllist = wzHouseMaintenaCostService.queryList1(map1);
        //全省节点
        HouseCostVo province = new HouseCostVo();
        province.setCounty("全省");
        province.setCity("全省");

        double aProjectCost = 0l;
        double aSmallCost = 0l;
        double aWarnCost = 0l;
        double aProCost = 0l;
        double aOtherCost = 0l;
        double aAllCost = 0l;
        //按地市进行划分
        Map<String, List<WzHouseMaintenaCostEntity>> cityMap = new HashMap<>();
        for (WzHouseMaintenaCostEntity entity : alllist) {
            if (entity.getCity() == null) continue;
            List<WzHouseMaintenaCostEntity> list;
            if (cityMap.get(entity.getCity()) != null) {
                list = cityMap.get(entity.getCity());
                list.add(entity);
            } else {
                list = new ArrayList<>();
                list.add(entity);
            }
            cityMap.put(entity.getCity(), list);
        }
        //按区进行划分
        List<HouseCostVo> resultList = new ArrayList<>();
        List<HouseCostVo> cityList = new ArrayList<>();
        for (Map.Entry<String, List<WzHouseMaintenaCostEntity>> entry : cityMap.entrySet()) {
            List<WzHouseMaintenaCostEntity> list = entry.getValue();
            //县的Map集合
            Map<String, List<WzHouseMaintenaCostEntity>> countyMap = new HashMap<>();
            for (WzHouseMaintenaCostEntity leaveUseVo : list) {
                if (countyMap.get(leaveUseVo.getCounty()) != null) {
                    list = countyMap.get(leaveUseVo.getCounty());
                    list.add(leaveUseVo);
                } else {
                    list = new ArrayList<>();
                    list.add(leaveUseVo);
                }
                countyMap.put(leaveUseVo.getCounty(), list);
            }

            List<HouseCostVo> countyList = new ArrayList<>();
            double cityProjectCost = 0l;
            double citySmallCost = 0l;
            double cityWarnCost = 0l;
            double cityProCost = 0l;
            double cityOtherCost = 0l;
            double cityAllCost = 0l;
            for (Map.Entry<String, List<WzHouseMaintenaCostEntity>> countyEntry : countyMap.entrySet()) {
                double projectCost = 0l;
                double smallCost = 0l;
                double warnCost = 0l;
                double proCost = 0l;
                double otherCost = 0l;
                double allCost = 0l;

                HouseCostVo county = new HouseCostVo();
                for (WzHouseMaintenaCostEntity leaveUseVo : countyEntry.getValue()) {
                    county.setCity(leaveUseVo.getCounty());
                    if (leaveUseVo.getRepairType().equals("项目制维修")) {
                        projectCost += leaveUseVo.getErpCreditedAmount();
                        cityProjectCost += leaveUseVo.getErpCreditedAmount();
                        aProjectCost += leaveUseVo.getErpCreditedAmount();
                        allCost +=leaveUseVo.getErpCreditedAmount();
                        cityAllCost +=leaveUseVo.getErpCreditedAmount();
                        aAllCost +=leaveUseVo.getErpCreditedAmount();
                    } else if (leaveUseVo.getRepairType().equals("零星维修")) {
                        smallCost += leaveUseVo.getErpCreditedAmount();
                        citySmallCost += leaveUseVo.getErpCreditedAmount();
                        aSmallCost += leaveUseVo.getErpCreditedAmount();
                        allCost +=leaveUseVo.getErpCreditedAmount();
                        cityAllCost +=leaveUseVo.getErpCreditedAmount();
                        aAllCost +=leaveUseVo.getErpCreditedAmount();
                    } else if (leaveUseVo.getRepairType().equals("应急维修")) {
                        warnCost += leaveUseVo.getErpCreditedAmount();
                        cityWarnCost += leaveUseVo.getErpCreditedAmount();
                        aWarnCost += leaveUseVo.getErpCreditedAmount();
                        allCost +=leaveUseVo.getErpCreditedAmount();
                        cityAllCost +=leaveUseVo.getErpCreditedAmount();
                        aAllCost +=leaveUseVo.getErpCreditedAmount();
                    } else if (leaveUseVo.getRepairType().equals("安防费用")) {
                        proCost += leaveUseVo.getErpCreditedAmount();
                        cityProCost += leaveUseVo.getErpCreditedAmount();
                        aProCost += leaveUseVo.getErpCreditedAmount();
                        allCost +=leaveUseVo.getErpCreditedAmount();
                        cityAllCost +=leaveUseVo.getErpCreditedAmount();
                        aAllCost +=leaveUseVo.getErpCreditedAmount();
                    } else {
                        if(leaveUseVo.getRepairType().contains("预提冲销")){
                            continue;
                        }
                        otherCost += leaveUseVo.getErpCreditedAmount();
                        cityOtherCost += leaveUseVo.getErpCreditedAmount();
                        aOtherCost += leaveUseVo.getErpCreditedAmount();
                    }
//                    allCost += projectCost + smallCost + warnCost + proCost + otherCost;
//                    cityAllCost += cityProjectCost + citySmallCost + cityWarnCost + cityProCost + cityOtherCost;
//                    aAllCost += aProjectCost + aSmallCost + aWarnCost + aProCost + aOtherCost;

                }
                county.setProjectCost(formatDouble(projectCost));
                county.setProCost(formatDouble(proCost));
                county.setOtherCost(formatDouble(otherCost));
                county.setSmallCost(formatDouble(smallCost));
                county.setWarnCost(formatDouble(warnCost));
                county.setAllCost(formatDouble(allCost));
                countyList.add(county);
            }

            List<HouseCostVo> orderCountyList = new ArrayList<>();
            try {
                //对countList 进行排序
                String city = list.get(0).getCity();
                if (city.contains("市")) {
                    city = city.split("市")[0];
                }
                OtnAreaEntity area = otnAreaService.queryObject(city);
                List<OtnAreaEntity> countyAreaList = otnAreaService.getCityOption(area.getId());
                int i=0;
                for (OtnAreaEntity otnAreaEntity : countyAreaList) {
                    for (HouseCostVo wzCity : countyList) {
                        if (otnAreaEntity.getName().equals(wzCity.getCity())) {
                            i++;
                            wzCity.setFlag(i%2==0);
                            orderCountyList.add(wzCity);
                        }
                    }
                }
            } catch (Exception e) {
                orderCountyList = countyList;
                e.printStackTrace();
            }

            HouseCostVo parent = new HouseCostVo();
            parent.setProCost(formatDouble(cityProCost));
            parent.setWarnCost(formatDouble(cityWarnCost));
            parent.setProjectCost(formatDouble(cityProjectCost));
            parent.setOtherCost(formatDouble(cityOtherCost));
            parent.setSmallCost(formatDouble(citySmallCost));
            parent.setAllCost(formatDouble(cityAllCost));
            parent.setCity(list.get(0).getCity());
            parent.setChildren(orderCountyList);
            cityList.add(parent);
        }

        //对cityList 进行排序
        List<OtnAreaEntity> areaList = otnAreaService.getCityOption("018");
        List<HouseCostVo> orderList = new ArrayList<>();
        for (OtnAreaEntity otnAreaEntity : areaList) {
            for (HouseCostVo wzCity : cityList) {
                if (wzCity.getCity().contains(otnAreaEntity.getName())) {
                    orderList.add(wzCity);
                }
            }
        }

        province.setChildren(orderList);
        province.setAllCost(formatDouble(aAllCost));
        province.setSmallCost(formatDouble(aSmallCost));
        province.setOtherCost(formatDouble(aOtherCost));
        province.setProjectCost(formatDouble(aProjectCost));
        province.setProCost(formatDouble(aProCost));
        province.setWarnCost(formatDouble(aWarnCost));
        resultList.add(province);

        List<Object> colList=new ArrayList();;
        List<Object[]> allList = new ArrayList();
        for (int i = 0; i < resultList.size(); i++) {

            if(resultList.get(i).getCity().contains("省")){
                colList = new ArrayList();
                colList.add(resultList.get(i).getCity());
                colList.add("");
                colList.add("");
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getProjectCost(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getSmallCost(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getWarnCost(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getAllCost(),true));
                allList.add(colList.toArray());

                //处理城市的
                List<HouseCostVo> cityList1=resultList.get(i).getChildren();
                for (HouseCostVo floorNoUserVo : cityList1) {
                    colList = new ArrayList();
                    colList.add("");
                    colList.add(floorNoUserVo.getCity());
                    colList.add("");
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getProjectCost(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getSmallCost(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getWarnCost(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getAllCost(),true));
                    allList.add(colList.toArray());
                    //处理县城的
                    List<HouseCostVo> countyList1 = floorNoUserVo.getChildren();
                    for (HouseCostVo noUserVo : countyList1) {
                        colList = new ArrayList();
                        colList.add("");
                        colList.add("");
                        colList.add(noUserVo.getCity());
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getProjectCost(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getSmallCost(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getWarnCost(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getAllCost(),true));
                        allList.add(colList.toArray());
                    }
                }
            }
        }
        String[] header = new String[]{
                "资产所属单位(一级)",
                "资产所属单位(二级)",
                "资产所属单位(三级)",
                "项目制维修（元）",
                "零星维修（元）",
                "应急维修（元）",
                "总计（元）"
        };
        ee1.addSheetByArray("房屋维修_维修费统计"+df.format(new Date()) + "清单", allList, header);

    }

    public void responseTab7(DateFormat df, ExcelExport ee1, Map<String, Object> map1){

        List<WzHouseMaintenaCostEntity> resultList = wzHouseMaintenaCostService.queryList1(map1);
        //每平方米维修单价
        for (WzHouseMaintenaCostEntity wzHouseMaintenaCostEntity : resultList) {
            wzHouseMaintenaCostEntity.setAveragePrice(formatDouble(wzHouseMaintenaCostEntity.dealPrice(wzHouseMaintenaCostEntity)));
        }

        List<Object> colList;
        List<Object[]> allList = new ArrayList();
        for (int i = 0; i < resultList.size(); i++) {
            colList = new ArrayList();
            colList.add(resultList.get(i).getBuildingId());
            colList.add(resultList.get(i).getStatisticalMonth());
            colList.add(resultList.get(i).getRepairType());
            colList.add(resultList.get(i).getRepairContent());
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getErpCreditedAmount(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getAveragePrice(),true));
            colList.add(resultList.get(i).getCity());
            colList.add(resultList.get(i).getCounty());
            colList.add(resultList.get(i).getBuildingAddressNumber());
            colList.add(resultList.get(i).getBuildingNnameAlias());
            colList.add(resultList.get(i).getStandardAddress());
            colList.add(resultList.get(i).getCityGrade());
            colList.add(resultList.get(i).getCityArea());
            colList.add(resultList.get(i).getInvestmentEntity());
            colList.add(resultList.get(i).getManagementHierarchy());
            colList.add(resultList.get(i).getBuildingStructure());
            colList.add(resultList.get(i).getUseState());
            colList.add(resultList.get(i).getBuildingUse());
            colList.add(resultList.get(i).getOwnershipStatus());
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingFloorArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaUseArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaRentArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaIdelArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getBuildingAreaUnavailable(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getUseArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getUseAreaSelfUseArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getUseAreaRentArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getUseAreaIdelArea(),true));
            colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getUseAreaUnavailableArea(),true));
            allList.add(colList.toArray());
        }
        String[] header = new String[]{
                "建筑id基本信息",
                "入账日期",
                "维修类型",
                "维修内容",
                "erp入账金额元",
                "每平方米维修单价",
                "资产所属单位二级",
                "资产所属单位三级",
                "局址编号",
                "局址别名",
                "标准地址",
                "城市等级",
                "城市区域",
                "投资主体",
                "管理层级",
                "房屋结构",
                "使用状况",
                "建筑用途",
                "权属状态",
                "建筑占地面积㎡",
                "建筑面积㎡",
                "建筑面积自用㎡",
                "建筑面积出租㎡",
                "建筑面积闲置㎡",
                "建筑面积不可使用㎡",
                "使用面积㎡",
                "使用面积自用㎡",
                "使用面积出租㎡",
                "使用面积闲置㎡",
                "使用面积不可使用㎡"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }


    public double formatDouble(double num) {
        DecimalFormat format2 = new DecimalFormat("#.00");
        String str2 = format2.format(num);
        double avera = Double.parseDouble(str2);
        return avera;
    }

    public static String toThousands(Object number,boolean flag) {
        if(number==null){
            return "";
        }
        String num=number.toString();
        if(num.contains("E")){
            BigDecimal decimal=new BigDecimal(num);
            BigDecimal decimal1 = decimal.setScale(4, 2);
            num=decimal1.toString();
        }
        String[] snum=num.split("\\.");
        num=snum[0];
        String result = "";
        int counter = 0;
        for (int i = num.length() - 1; i >= 0; i--) {
            counter++;
            result = num.charAt(i) + result;
            if (counter % 3==0 && i != 0) {
                result = ',' + result;
            }
        }
        if(snum.length<2){
            if(flag){
                return result+".00";
            }else{
                return result;
            }
        }
        if(snum[1].length()==1){
            snum[1]+="0";
        }
        if(snum[1].length()>2){
           snum[1]=snum[1].substring(0,2);
        }
        return result+"."+snum[1];
    }
}