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

package com.example.controller;

import com.example.config.annotation.IgnoreAuth;
import com.example.entity.OtnAreaEntity;
import com.example.entity.WzHouseArrearsManageEntity;
import com.example.entity.WzHouseContracEntity;
import com.example.entity.WzHouseContractOutEntity;
import com.example.entity.WzHouseCustomerEntity;
import com.example.entity.WzHouseMarketEntity;
import com.example.service.WzHouseArrearsManageService;
import com.example.service.WzHouseContracService;
import com.example.service.WzHouseContractOutService;
import com.example.service.WzHouseCustomerService;
import com.example.service.WzHouseMarketService;
import com.example.service.WzOtnAreaService;
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.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/WzHouseContrac")
public class WzHouseContracController {

    @Autowired
    private WzHouseContracService wzHouseContracService;
    @Autowired
    private WzHouseCustomerService wzHouseCustomerService;
    @Autowired
    private WzHouseArrearsManageService wzHouseArrearsManageService;
    @Autowired
    private WzHouseMarketService wzHouseMarketService;
    @Autowired
    private WzHouseContractOutService wzHouseContractOutService;
    @Autowired
    private WzOtnAreaService otnAreaService;

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

    @RequestMapping("/echartList")
    @IgnoreAuth
    public R echartList(@RequestBody PageMap pageMap) {
        //查询列表数据
        Query query = new Query(pageMap);
        List<WzHouseContracEntity> wzHouseContracEntityList = wzHouseContracService.queryList(pageMap.getMap());

        Map<String,List<WzHouseContracEntity>> cityMap=new HashMap<>();
        for (WzHouseContracEntity leaveUseVo : wzHouseContracEntityList) {
            List<WzHouseContracEntity> list;
            if(cityMap.get(leaveUseVo.getCity())!=null){
                list=cityMap.get(leaveUseVo.getCity());
                list.add(leaveUseVo);
            }else{
                list=new ArrayList<>();
                list.add(leaveUseVo);
            }
            cityMap.put(leaveUseVo.getCity(),list);
        }

        List<WzHouseContracEntity> resultList=new ArrayList<>();
        List<WzHouseContracEntity> cityList=new ArrayList<>();

        double allContractQuantity=0l;
        double allNoTaxContractAmount=0l;
        double allRentArea=0l;
        double allUnitPricePerSquareMeter=0l;
        int cityNum=0;
        for(Map.Entry<String,List<WzHouseContracEntity>> entry:cityMap.entrySet()){
            List<WzHouseContracEntity> list=entry.getValue();

            if (list.get(0).getCounty().equals("全省")) {
                continue;
            }
            //县的Map集合
            Map<String, List<WzHouseContracEntity>> countyMap = new HashMap<>();
            for (WzHouseContracEntity leaveUseVo : list) {
                if (leaveUseVo.getCounty() == null || leaveUseVo.getCounty().equals("")) {
                    continue;
                }
                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);
            }

            double cityContractQuantity=0l;
            double cityNoTaxContractAmount=0l;
            double cityRentArea=0l;
            double cityUnitPricePerSquareMeter=0l;
            List<WzHouseContracEntity> countyList=new ArrayList<>();
            for (Map.Entry<String, List<WzHouseContracEntity>> countyEntry : countyMap.entrySet()) {
                double contractQuantity=0l;
                double noTaxContractAmount=0l;
                double rentArea=0l;
                double unitPricePerSquareMeter=0l;
                for (WzHouseContracEntity leaveUseVo : countyEntry.getValue()) {
                    contractQuantity+=leaveUseVo.getContractQuantity();
                    noTaxContractAmount+=leaveUseVo.getNoTaxContractAmount();
                    rentArea+=leaveUseVo.getRentArea();
                    unitPricePerSquareMeter+=leaveUseVo.getUnitPricePerSquareMeter();

                    cityContractQuantity+=leaveUseVo.getContractQuantity();
                    cityNoTaxContractAmount+=leaveUseVo.getNoTaxContractAmount();
                    cityRentArea+=leaveUseVo.getRentArea();
                    cityUnitPricePerSquareMeter+=leaveUseVo.getUnitPricePerSquareMeter();

                    allContractQuantity+=leaveUseVo.getContractQuantity();
                    allNoTaxContractAmount+=leaveUseVo.getNoTaxContractAmount();
                    allRentArea+=leaveUseVo.getRentArea();
                    allUnitPricePerSquareMeter+=leaveUseVo.getUnitPricePerSquareMeter();
                }
                WzHouseContracEntity wzHouseInCustomerEntity = new WzHouseContracEntity();
                wzHouseInCustomerEntity.setContractQuantity(formatDouble(contractQuantity));
                wzHouseInCustomerEntity.setCity(countyEntry.getKey());
                wzHouseInCustomerEntity.setNoTaxContractAmount(formatDouble(noTaxContractAmount));
                wzHouseInCustomerEntity.setRentArea(formatDouble(rentArea));
                wzHouseInCustomerEntity.setUnitPricePerSquareMeter(formatDouble(unitPricePerSquareMeter));
                countyList.add(wzHouseInCustomerEntity);
            }
            List<WzHouseContracEntity> 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 (WzHouseContracEntity wzCity : countyList) {
                        if(wzCity.getCity().contains(otnAreaEntity.getName())){
                            i++;
                            wzCity.setFlag(i%2==0);
                            orderCountyList.add(wzCity);
                        }
                    }
                }
            }catch (Exception e){
                orderCountyList=countyList;
                e.printStackTrace();
            }

            WzHouseContracEntity wzHouseInCustomerEntity = new WzHouseContracEntity();
            wzHouseInCustomerEntity.setCity(entry.getKey());
            wzHouseInCustomerEntity.setContractQuantity(formatDouble(cityContractQuantity));
            wzHouseInCustomerEntity.setNoTaxContractAmount(formatDouble(cityNoTaxContractAmount));
            wzHouseInCustomerEntity.setRentArea(formatDouble(cityRentArea));
            double num=cityUnitPricePerSquareMeter/countyMap.size();
            cityNum+=countyMap.size();
            wzHouseInCustomerEntity.setUnitPricePerSquareMeter(formatDouble(num));
            wzHouseInCustomerEntity.setChildren(orderCountyList);
            cityList.add(wzHouseInCustomerEntity);
        }


        //对cityList 进行排序
        List<OtnAreaEntity> areaList=otnAreaService.getCityOption(0l);
        List<WzHouseContracEntity> orderList=new ArrayList<>();
        int i=0;
        for (OtnAreaEntity otnAreaEntity : areaList) {
            for (WzHouseContracEntity wzCity : cityList) {
                if(wzCity.getCity().contains(otnAreaEntity.getName())){
                    wzCity.setFlag(i%2==0);
                    orderList.add(wzCity);
                    i++;
                }
            }
        }

        WzHouseContracEntity province=new WzHouseContracEntity();
        province.setCity("全省");
        province.setContractQuantity(formatDouble(allContractQuantity));
        province.setNoTaxContractAmount(formatDouble(allNoTaxContractAmount));
        province.setRentArea(formatDouble(allRentArea));
        if(cityList.size()!=0){
            province.setUnitPricePerSquareMeter(formatDouble(allUnitPricePerSquareMeter/cityNum ));
        }
        province.setChildren(orderList);
        if(orderList.size()>0){
            resultList.add(province);
        }
        int total = wzHouseContracService.queryTotal(query);
        PageUtils pageUtil = new PageUtils(resultList, total, query.getLimit(), query.getPage());
        return R.ok().put("page", pageUtil);
    }

    /**
     * 清单excel
     */
    @GetMapping("/excel")
    @IgnoreAuth
    public void excel(String name,String buildingNameAlias,String city,String county, HttpServletResponse response) {
        Map<String,Object> map1=new HashMap<>();
        if(buildingNameAlias!=null){
            map1.put("buildingNameAlias",buildingNameAlias);
        }
        if(city!=null){
            map1.put("city",city);
        }
        if(county!=null){
            map1.put("county",county);
        }

        DateFormat df = new SimpleDateFormat("yyyyMMdd");
        ExcelExport ee1;

        //判定是闲置管理还是详情
        if(name.contains("合同管理")){
            ee1 = new ExcelExport("房屋对外出租_合同管理" + df.format(new Date()));
            responseTab1(df,ee1,map1);
            ee1.export(response);
        }else if(name.contains("客户管理")){
            ee1 = new ExcelExport("房屋对外出租_客户管理" + df.format(new Date()));
            responseTab2(df,ee1,map1);
            ee1.export(response);
        }else if(name.contains("出租欠费管理")){
            ee1 = new ExcelExport("房屋对外出租_出租欠费管理" + df.format(new Date()));
            responseTab3(df,ee1,map1);
            ee1.export(response);
        }else if(name.contains("市场调查")){
            ee1 = new ExcelExport("房屋对外出租_市场调查" + df.format(new Date()));
            responseTab4(df,ee1,map1);
            ee1.export(response);
        }else if(name.contains("出租合同详单")){
            ee1 = new ExcelExport("房屋对外出租_出租合同详情" + df.format(new Date()));
            responseTab5(df,ee1,map1);
            ee1.export(response);
        }


    }

    public void responseTab1(DateFormat df, ExcelExport ee1, Map<String, Object> map1){
        List<WzHouseContracEntity> wzHouseContracEntityList = wzHouseContracService.queryList(map1);

        Map<String,List<WzHouseContracEntity>> cityMap=new HashMap<>();
        for (WzHouseContracEntity leaveUseVo : wzHouseContracEntityList) {
            List<WzHouseContracEntity> list;
            if(cityMap.get(leaveUseVo.getCity())!=null){
                list=cityMap.get(leaveUseVo.getCity());
                list.add(leaveUseVo);
            }else{
                list=new ArrayList<>();
                list.add(leaveUseVo);
            }
            cityMap.put(leaveUseVo.getCity(),list);
        }

        List<WzHouseContracEntity> resultList=new ArrayList<>();
        List<WzHouseContracEntity> cityList=new ArrayList<>();

        double allContractQuantity=0l;
        double allNoTaxContractAmount=0l;
        double allRentArea=0l;
        double allUnitPricePerSquareMeter=0l;
        int cityNum=0;
        for(Map.Entry<String,List<WzHouseContracEntity>> entry:cityMap.entrySet()){
            List<WzHouseContracEntity> list=entry.getValue();

            if (list.get(0).getCounty().equals("全省")) {
                continue;
            }
            //县的Map集合
            Map<String, List<WzHouseContracEntity>> countyMap = new HashMap<>();
            for (WzHouseContracEntity leaveUseVo : list) {
                if (leaveUseVo.getCounty() == null || leaveUseVo.getCounty().equals("")) {
                    continue;
                }
                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);
            }

            double cityContractQuantity=0l;
            double cityNoTaxContractAmount=0l;
            double cityRentArea=0l;
            double cityUnitPricePerSquareMeter=0l;
            List<WzHouseContracEntity> countyList=new ArrayList<>();
            for (Map.Entry<String, List<WzHouseContracEntity>> countyEntry : countyMap.entrySet()) {
                double contractQuantity=0l;
                double noTaxContractAmount=0l;
                double rentArea=0l;
                double unitPricePerSquareMeter=0l;
                for (WzHouseContracEntity leaveUseVo : countyEntry.getValue()) {
                    contractQuantity+=leaveUseVo.getContractQuantity();
                    noTaxContractAmount+=leaveUseVo.getNoTaxContractAmount();
                    rentArea+=leaveUseVo.getRentArea();
                    unitPricePerSquareMeter+=leaveUseVo.getUnitPricePerSquareMeter();

                    cityContractQuantity+=leaveUseVo.getContractQuantity();
                    cityNoTaxContractAmount+=leaveUseVo.getNoTaxContractAmount();
                    cityRentArea+=leaveUseVo.getRentArea();
                    cityUnitPricePerSquareMeter+=leaveUseVo.getUnitPricePerSquareMeter();

                    allContractQuantity+=leaveUseVo.getContractQuantity();
                    allNoTaxContractAmount+=leaveUseVo.getNoTaxContractAmount();
                    allRentArea+=leaveUseVo.getRentArea();
                    allUnitPricePerSquareMeter+=leaveUseVo.getUnitPricePerSquareMeter();
                }
                WzHouseContracEntity wzHouseInCustomerEntity = new WzHouseContracEntity();
                wzHouseInCustomerEntity.setContractQuantity(formatDouble(contractQuantity));
                wzHouseInCustomerEntity.setCity(countyEntry.getKey());
                wzHouseInCustomerEntity.setNoTaxContractAmount(formatDouble(noTaxContractAmount));
                wzHouseInCustomerEntity.setRentArea(formatDouble(rentArea));
                wzHouseInCustomerEntity.setUnitPricePerSquareMeter(formatDouble(unitPricePerSquareMeter));
                countyList.add(wzHouseInCustomerEntity);
            }
            List<WzHouseContracEntity> 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 (WzHouseContracEntity wzCity : countyList) {
                        if(wzCity.getCity().contains(otnAreaEntity.getName())){
                            i++;
                            wzCity.setFlag(i%2==0);
                            orderCountyList.add(wzCity);
                        }
                    }
                }
            }catch (Exception e){
                orderCountyList=countyList;
                e.printStackTrace();
            }

            WzHouseContracEntity wzHouseInCustomerEntity = new WzHouseContracEntity();
            wzHouseInCustomerEntity.setCity(entry.getKey());
            wzHouseInCustomerEntity.setContractQuantity(formatDouble(cityContractQuantity));
            wzHouseInCustomerEntity.setNoTaxContractAmount(formatDouble(cityNoTaxContractAmount));
            wzHouseInCustomerEntity.setRentArea(formatDouble(cityRentArea));
            double num=cityUnitPricePerSquareMeter/countyMap.size();
            cityNum+=countyMap.size();
            wzHouseInCustomerEntity.setUnitPricePerSquareMeter(formatDouble(num));
            wzHouseInCustomerEntity.setChildren(orderCountyList);
            cityList.add(wzHouseInCustomerEntity);
        }


        //对cityList 进行排序
        List<OtnAreaEntity> areaList=otnAreaService.getCityOption(0l);
        List<WzHouseContracEntity> orderList=new ArrayList<>();
        for (OtnAreaEntity otnAreaEntity : areaList) {
            for (WzHouseContracEntity wzCity : cityList) {
                if(wzCity.getCity().contains(otnAreaEntity.getName())){
                    orderList.add(wzCity);
                }
            }
        }
        WzHouseContracEntity province=new WzHouseContracEntity();
        province.setCity("全省");
        province.setContractQuantity(formatDouble(allContractQuantity));
        province.setNoTaxContractAmount(formatDouble(allNoTaxContractAmount));
        province.setRentArea(formatDouble(allRentArea));
        if(cityList.size()!=0){
            province.setUnitPricePerSquareMeter(formatDouble(allUnitPricePerSquareMeter/cityNum ));
        }
        province.setChildren(orderList);
        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).getContractQuantity(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getNoTaxContractAmount(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getRentArea(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getUnitPricePerSquareMeter(),true));
                allList.add(colList.toArray());

                //处理城市的
                List<WzHouseContracEntity> cityList1=resultList.get(i).getChildren();
                for (WzHouseContracEntity floorNoUserVo : cityList1) {
                    colList = new ArrayList();
                    colList.add("");
                    colList.add(floorNoUserVo.getCity());
                    colList.add("");
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getContractQuantity(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getNoTaxContractAmount(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getRentArea(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getUnitPricePerSquareMeter(),true));
                    allList.add(colList.toArray());


                    //处理县城的
                    List<WzHouseContracEntity> countyList1 = floorNoUserVo.getChildren();
                    for (WzHouseContracEntity noUserVo : countyList1) {
                        colList = new ArrayList();
                        colList.add("");
                        colList.add("");
                        colList.add(noUserVo.getCity());
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getContractQuantity(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getNoTaxContractAmount(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getRentArea(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getUnitPricePerSquareMeter(),true));
                        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<WzHouseCustomerEntity> wzHouseContracEntityList = wzHouseCustomerService.queryList(query);

        WzHouseCustomerEntity province=new WzHouseCustomerEntity();
        province.setCity("全省");

        Map<String,List<WzHouseCustomerEntity>> map=new HashMap<>();
        for (WzHouseCustomerEntity leaveUseVo : wzHouseContracEntityList) {
            List<WzHouseCustomerEntity> list;
            if(map.get(leaveUseVo.getCity())!=null){
                list=map.get(leaveUseVo.getCity());
                list.add(leaveUseVo);
            }else{
                list=new ArrayList<>();
                list.add(leaveUseVo);
            }
            map.put(leaveUseVo.getCity(),list);
        }
        List<WzHouseCustomerEntity> resultList=new ArrayList<>();
        List<WzHouseCustomerEntity> cityList=new ArrayList<>();

        Integer allPerson=0;
        Integer allPersonPrivate=0;
        Integer allStateOwnedEnterprises=0;
        Integer allGovernment=0;
        Integer allAllNum=0;

        for(Map.Entry<String,List<WzHouseCustomerEntity>> entry:map.entrySet()){
            List<WzHouseCustomerEntity> list=entry.getValue();
            if(list.get(0).getCity().equals("全省")){
                continue;
            }
            Integer person=0;
            Integer personPrivate=0;
            Integer stateOwnedEnterprises=0;
            Integer government=0;
            Integer allNum=0;
            for (WzHouseCustomerEntity leaveUseVo : list) {

                if(leaveUseVo.getPerson()!=null){
                    person+=leaveUseVo.getPerson();
                    allPerson+=leaveUseVo.getPerson();
                }
                if(leaveUseVo.getPersonPrivate()!=null){
                    personPrivate+=leaveUseVo.getPersonPrivate();
                    allPersonPrivate+=leaveUseVo.getPersonPrivate();
                }
                if(leaveUseVo.getStateOwnedEnterprises()!=null){
                    stateOwnedEnterprises+=leaveUseVo.getStateOwnedEnterprises();
                    allStateOwnedEnterprises+=leaveUseVo.getStateOwnedEnterprises();
                }
                if(leaveUseVo.getGovernment()!=null){
                    government+=leaveUseVo.getGovernment();
                    allGovernment+=leaveUseVo.getGovernment();
                }
                allNum=person+personPrivate+stateOwnedEnterprises+government;
                allAllNum+=allNum;
                leaveUseVo.setAllNum(allNum);
            }
            cityList.add(list.get(0));
        }
        //对cityList 进行排序
        List<OtnAreaEntity> areaList=otnAreaService.getCityOption(0l);
        List<WzHouseCustomerEntity> orderList=new ArrayList<>();
        for (OtnAreaEntity otnAreaEntity : areaList) {
            for (WzHouseCustomerEntity wzCity : cityList) {
                if(wzCity.getCity().contains(otnAreaEntity.getName())){
                    orderList.add(wzCity);
                }
            }
        }

        province.setPerson(allPerson);
        province.setPersonPrivate(allPersonPrivate);
        province.setStateOwnedEnterprises(allStateOwnedEnterprises);
        province.setGovernment(allGovernment);
        province.setChildren(orderList);
        province.setAllNum(allAllNum);
        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(resultList.get(i).getPerson());
                colList.add(resultList.get(i).getPersonPrivate());
                colList.add(resultList.get(i).getStateOwnedEnterprises());
                colList.add(resultList.get(i).getGovernment());
                colList.add(resultList.get(i).getAllNum());
                allList.add(colList.toArray());

                //处理城市的
                List<WzHouseCustomerEntity> cityList1=resultList.get(i).getChildren();
                for (WzHouseCustomerEntity floorNoUserVo : cityList1) {
                    colList = new ArrayList();
                    colList.add("");
                    colList.add(floorNoUserVo.getCity());
                    colList.add(floorNoUserVo.getPerson());
                    colList.add(floorNoUserVo.getPersonPrivate());
                    colList.add(floorNoUserVo.getStateOwnedEnterprises());
                    colList.add(floorNoUserVo.getGovernment());
                    colList.add(floorNoUserVo.getAllNum());
                    allList.add(colList.toArray());
                }
            }
        }
        String[] header = new String[]{
                "一级单位",
                "二级单位",
                "个人",
                "私营",
                "国企",
                "政府",
                "总计"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }

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

        //查询列表数据
        Query query = new Query(map1);
        List<WzHouseArrearsManageEntity> wzHouseContracEntityList = wzHouseArrearsManageService.queryList(query);

        WzHouseArrearsManageEntity province=new WzHouseArrearsManageEntity();
        province.setCity("全省");


        Map<String,List<WzHouseArrearsManageEntity>> map=new HashMap<>();
        for (WzHouseArrearsManageEntity leaveUseVo : wzHouseContracEntityList) {
            List<WzHouseArrearsManageEntity> list;
            if(map.get(leaveUseVo.getCity())!=null){
                list=map.get(leaveUseVo.getCity());
                list.add(leaveUseVo);
            }else{
                list=new ArrayList<>();
                list.add(leaveUseVo);
            }
            map.put(leaveUseVo.getCity(),list);
        }
        List<WzHouseArrearsManageEntity> resultList=new ArrayList<>();
        List<WzHouseArrearsManageEntity> cityList=new ArrayList<>();

        double allAccumulateArrears=0l;
        double allmoreThanThreeMonths=0l;
        double allmoreThanOneYears=0l;
        double allyearCumulativeNewAdditions=0l;
        double allyearCumulativeWithdrawal=0l;

        for(Map.Entry<String,List<WzHouseArrearsManageEntity>> entry:map.entrySet()){

            List<WzHouseArrearsManageEntity> list=entry.getValue();

            //县的Map集合
            Map<String,List<WzHouseArrearsManageEntity>> countyMap=new HashMap<>();
            for (WzHouseArrearsManageEntity leaveUseVo : list) {
                if (leaveUseVo.getCounty() == null || leaveUseVo.getCounty().equals("")) {
                    continue;
                }
                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);
            }

            double cityaccumulateArrears=0l;
            double citymoreThanThreeMonths=0l;
            double citymoreThanOneYears=0l;
            double cityyearCumulativeNewAdditions=0l;
            double cityyearCumulativeWithdrawal=0l;
            List<WzHouseArrearsManageEntity> countyList=new ArrayList<>();
            for (Map.Entry<String, List<WzHouseArrearsManageEntity>> countyEntry : countyMap.entrySet()) {
                double accumulateArrears=0l;
                double moreThanThreeMonths=0l;
                double moreThanOneYears=0l;
                double yearCumulativeNewAdditions=0l;
                double yearCumulativeWithdrawal=0l;
                for (WzHouseArrearsManageEntity leaveUseVo : countyEntry.getValue()) {
                    if(leaveUseVo.getAccumulateArrears()!=null){
                        accumulateArrears+=leaveUseVo.getAccumulateArrears();
                        cityaccumulateArrears+=leaveUseVo.getAccumulateArrears();
                        allAccumulateArrears+=leaveUseVo.getAccumulateArrears();
                    }
                    if(leaveUseVo.getMoreThanThreeMonths()!=null){
                        moreThanThreeMonths+=leaveUseVo.getMoreThanThreeMonths();
                        citymoreThanThreeMonths+=leaveUseVo.getMoreThanThreeMonths();
                        allmoreThanThreeMonths+=leaveUseVo.getMoreThanThreeMonths();
                    }
                    if(leaveUseVo.getMoreThanOneYears()!=null){
                        moreThanOneYears+=leaveUseVo.getMoreThanOneYears();
                        citymoreThanOneYears+=leaveUseVo.getMoreThanOneYears();
                        allmoreThanOneYears+=leaveUseVo.getMoreThanOneYears();
                    }
                    if(leaveUseVo.getYearCumulativeNewAdditions()!=null){
                        yearCumulativeNewAdditions+=leaveUseVo.getYearCumulativeNewAdditions();
                        cityyearCumulativeNewAdditions+=leaveUseVo.getYearCumulativeNewAdditions();
                        allyearCumulativeNewAdditions+=leaveUseVo.getYearCumulativeNewAdditions();
                    }
                    if(leaveUseVo.getYearCumulativeWithdrawal()!=null){
                        yearCumulativeWithdrawal+=leaveUseVo.getYearCumulativeWithdrawal();
                        cityyearCumulativeWithdrawal+=leaveUseVo.getYearCumulativeWithdrawal();
                        allyearCumulativeWithdrawal+=leaveUseVo.getYearCumulativeWithdrawal();
                    }
                }

                WzHouseArrearsManageEntity countyEntity=new WzHouseArrearsManageEntity();
                countyEntity.setAccumulateArrears(formatDouble(accumulateArrears));
                countyEntity.setYearCumulativeNewAdditions(formatDouble(yearCumulativeNewAdditions));
                countyEntity.setYearCumulativeWithdrawal(formatDouble(yearCumulativeWithdrawal));
                countyEntity.setMoreThanOneYears(formatDouble(moreThanOneYears));
                countyEntity.setMoreThanThreeMonths(formatDouble(moreThanThreeMonths));
                countyEntity.setCity(countyEntry.getValue().get(0).getCounty());
                countyList.add(countyEntity);
            }
            List<WzHouseArrearsManageEntity> 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());
                for (OtnAreaEntity otnAreaEntity : countyAreaList) {
                    for (WzHouseArrearsManageEntity wzCity : countyList) {
                        if(wzCity.getCity().contains(otnAreaEntity.getName())){
                            orderCountyList.add(wzCity);
                        }
                    }
                }
            }catch (Exception e){
                orderCountyList=countyList;
                e.printStackTrace();
            }


            WzHouseArrearsManageEntity cityEntity=new WzHouseArrearsManageEntity();
            cityEntity.setAccumulateArrears(formatDouble(cityaccumulateArrears));
            cityEntity.setYearCumulativeNewAdditions(formatDouble(cityyearCumulativeNewAdditions));
            cityEntity.setYearCumulativeWithdrawal(formatDouble(cityyearCumulativeWithdrawal));
            cityEntity.setMoreThanOneYears(formatDouble(citymoreThanOneYears));
            cityEntity.setMoreThanThreeMonths(formatDouble(citymoreThanThreeMonths));
            cityEntity.setCity(list.get(0).getCity());
            cityEntity.setChildren(orderCountyList);
            cityList.add(cityEntity);
        }

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

        province.setAccumulateArrears(formatDouble(allAccumulateArrears));
        province.setMoreThanOneYears(formatDouble(allmoreThanOneYears));
        province.setMoreThanThreeMonths(formatDouble(allmoreThanThreeMonths));
        province.setYearCumulativeNewAdditions(formatDouble(allyearCumulativeNewAdditions));
        province.setYearCumulativeWithdrawal(formatDouble(allyearCumulativeWithdrawal));
        province.setChildren(orderList);
        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).getAccumulateArrears(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getMoreThanThreeMonths(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getMoreThanOneYears(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getYearCumulativeNewAdditions(),true));
                colList.add(WzHouseMaintenaCostController.toThousands(resultList.get(i).getYearCumulativeWithdrawal(),true));
                allList.add(colList.toArray());

                //处理城市的
                List<WzHouseArrearsManageEntity> cityList1=resultList.get(i).getChildren();
                for (WzHouseArrearsManageEntity floorNoUserVo : cityList1) {
                    colList = new ArrayList();
                    colList.add("");
                    colList.add(floorNoUserVo.getCity());
                    colList.add("");
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getAccumulateArrears(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getMoreThanThreeMonths(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getMoreThanOneYears(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getYearCumulativeNewAdditions(),true));
                    colList.add(WzHouseMaintenaCostController.toThousands(floorNoUserVo.getYearCumulativeWithdrawal(),true));
                    allList.add(colList.toArray());
                    //处理县城的
                    List<WzHouseArrearsManageEntity> countyList1 = floorNoUserVo.getChildren();
                    for (WzHouseArrearsManageEntity noUserVo : countyList1) {
                        colList = new ArrayList();
                        colList.add("");
                        colList.add("");
                        colList.add(noUserVo.getCity());
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getAccumulateArrears(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getMoreThanThreeMonths(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getMoreThanOneYears(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getYearCumulativeNewAdditions(),true));
                        colList.add(WzHouseMaintenaCostController.toThousands(noUserVo.getYearCumulativeWithdrawal(),true));
                        allList.add(colList.toArray());
                    }
                }
            }
        }
        String[] header = new String[]{
                "资产所属单位(一级)",
                "资产所属单位(二级)",
                "资产所属单位(三级)",
                "累计欠费（万元）",
                "其中：3个月以上欠费",
                "其中：1年以上欠费",
                "本年累计新增欠费",
                "本年累计收回欠费"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }

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

        Query query = new Query(map1);
        List<WzHouseMarketEntity> resultList = wzHouseMarketService.queryList(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).getBuildingId());
            colList.add(resultList.get(i).getBuildingAlias());
            colList.add(resultList.get(i).getBuildingAddress());
            colList.add(resultList.get(i).getContractNumber());
            colList.add(resultList.get(i).getLeaseCategory());
            colList.add(resultList.get(i).getContractAmountExcludeTax());
            colList.add(resultList.get(i).getContractLeaseTerm());
            colList.add(resultList.get(i).getContractRentalArea());
            colList.add(resultList.get(i).getTotalMonthlyRent());
            colList.add(resultList.get(i).getUnitPriceMonth());
            colList.add(resultList.get(i).getSurveyTime());
            colList.add(resultList.get(i).getBuildingLongitude());
            colList.add(resultList.get(i).getBuildingDimension());
            colList.add(resultList.get(i).getHasNearbyProperty());
            colList.add(resultList.get(i).getNoNearbyPropertyReason());
            colList.add(resultList.get(i).getMarketBuildingName());
            colList.add(resultList.get(i).getDistanceBetweenBuildings());
            colList.add(resultList.get(i).getMarketContractLeaseTerm());
            colList.add(resultList.get(i).getMarketContractRentalArea());
            colList.add(resultList.get(i).getMonthlyRent());
            colList.add(resultList.get(i).getMarketUnitPriceMonth());
            colList.add(resultList.get(i).getMarketUnitPriceDay());
            colList.add(resultList.get(i).getMarketStandardAddress());
            colList.add(resultList.get(i).getHousingRentCollectionMethod());
            colList.add(resultList.get(i).getPropertyType());
            colList.add(resultList.get(i).getTotalNumberOfFloors());
            colList.add(resultList.get(i).getRenovationSituation());
            allList.add(colList.toArray());
        }
        String[] header = new String[]{
                "建筑ID",
                "建筑别名",
                "地址",
                "合同号",
                "租赁类别",
                "不含增值税合同额",
                "合同租期（月数）",
                "合同出租面积",
                "月租金（元）",
                "每平米单价（元/㎡/月）",
                "调查时间",
                "房屋经度",
                "房屋纬度",
                "1.5公里内是否有类似物业",
                "‘否’原因",
                "市场建筑名",
                "建筑之间距离m",
                "市场合同租期（月数）",
                "市场合同出租面积",
                "月租金（元）",
                "每平米单价 元/㎡/月",
                "每平米单价 元/㎡/天",
                "市场标准地址",
                "房屋租金收取方式",
                "物业类型",
                "楼层总数",
                "装修"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }

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

        //查询列表数据
        Query query = new Query(map1);
        List<WzHouseContractOutEntity> resultList = wzHouseContractOutService.queryList(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).getContractNumber());
            colList.add(resultList.get(i).getContractSerialNumber());
            colList.add(resultList.get(i).getCreateDate());
            colList.add(resultList.get(i).getContractStatus());
            colList.add(resultList.get(i).getContractName());
            colList.add(resultList.get(i).getUndertakingDepartment());
            colList.add(resultList.get(i).getUndertaker());
            colList.add(resultList.get(i).getContractNumber());
            colList.add(resultList.get(i).getOurSubject());
            colList.add(resultList.get(i).getFullNameOfThTenant());
            colList.add(resultList.get(i).getContractType());
            colList.add(resultList.get(i).getContractDescription());
            colList.add(resultList.get(i).getExcludingValueAddedTaxContractAmount());
            colList.add(resultList.get(i).getContractAmountIncludingVat());
            colList.add(resultList.get(i).getRate());
            colList.add(resultList.get(i).getPriceIncreaseMechanism());
            colList.add(resultList.get(i).getRenPaymentMethod());
            colList.add(resultList.get(i).getLeaseCategory());
            colList.add(resultList.get(i).getRentFreeStartDate());
            colList.add(resultList.get(i).getRentFreeEndDate());
            colList.add(resultList.get(i).getRentalStartDate());
            colList.add(resultList.get(i).getRentalEndDate());
            colList.add(resultList.get(i).getContractLeaseTerm());
            colList.add(resultList.get(i).getContractRentalArea());
            colList.add(resultList.get(i).getDebt());
            colList.add(resultList.get(i).getEndTime());
            colList.add(resultList.get(i).getEndReason());
            colList.add(resultList.get(i).getSubordinateUnits());
            colList.add(resultList.get(i).getResourceType());
            colList.add(resultList.get(i).getBuildingAlias());
            colList.add(resultList.get(i).getStandardAddress());
            colList.add(resultList.get(i).getFloor());
            colList.add(resultList.get(i).getRoomName());
            colList.add(resultList.get(i).getThisRentalBuildingArea());
            colList.add(resultList.get(i).getResourceType());
            colList.add(resultList.get(i).getBuildingAlias());
            colList.add(resultList.get(i).getStandardAddress());
            colList.add(resultList.get(i).getFloor());
            colList.add(resultList.get(i).getRoomName());
            colList.add(resultList.get(i).getThisRentalBuildingArea());
            colList.add(resultList.get(i).getTotalBuildingArea());
            colList.add(resultList.get(i).getSelfUseBuildingArea());
            colList.add(resultList.get(i).getIdleBuildingArea());
            colList.add(resultList.get(i).getRentalBuildingArea());
            colList.add(resultList.get(i).getUnusableBuildingArea());
            colList.add(resultList.get(i).getThisRentalUseArea());
            colList.add(resultList.get(i).getTotalUseArea());
            colList.add(resultList.get(i).getSelfUseArea());
            colList.add(resultList.get(i).getIdleArea());
            colList.add(resultList.get(i).getRentalUseArea());
            colList.add(resultList.get(i).getUnusableUseArea());
            colList.add(resultList.get(i).getStatisticalMonth());
            allList.add(colList.toArray());
        }
        String[] header = new String[]{
                "合同编号",
                "合同流水号",
                "创建日期",
                "合同状态",
                "合同名称",
                "承办部门",
                "承办人",
                "联系电话",
                "我方主体",
                "合同类型",
                "投资主体",
                "合同说明",
                "含增值税合同金额",
                "不含增值税合同额",
                "税率",
                "涨价机制",
                "交租方式",
                "租赁类别",
                "免租期起始日期",
                "免租期终止日期",
                "出租起始日期",
                "出租终止日期",
                "合同租期（月数）",
                "合同出租面积",
                "是否有欠款",
                "终止时间",
                "终止原因",
                "所属单位",
                "资源类型",
                "建筑/土地别名",
                "标准地址",
                "楼层",
                "房间名",
                "本次出租面积（建筑）",
                "总面积（建筑）",
                "自用面积（建筑）",
                "闲置面积（建筑）",
                "不可使用面积（建筑）",
                "本次出租面积（使用）",
                "总面积（使用）",
                "自用面积（使用）",
                "闲置面积（使用)",
                "出租面积（使用）",
                "不可使用面积（使用）",
                "账期"
        };
        ee1.addSheetByArray(df.format(new Date()) + "清单", allList, header);
    }

    public double formatDouble(double num){
        double avera=0l;
        try{
            DecimalFormat format2 = new DecimalFormat("#.00");
            String str2 = format2.format(num);
            avera = Double.parseDouble(str2);
        }catch (Exception e){
            e.printStackTrace();
        }
        return avera;
    }

}