package com.example.service.house;

import cn.hutool.core.net.URLEncodeUtil;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.common.PageVo;
import com.example.common.Rsp;
import com.example.dao.WzOtnAreaDao;
import com.example.dao.house.HouseBuildingDao;
import com.example.entity.house.BuildingIdleStatPo;
import com.example.entity.house.HouseAgeStatPo;
import com.example.entity.house.HouseBuildingPo;
import com.example.entity.house.HouseSitePo;
import com.example.enums.ListBuildingIdleOrderEnum;
import com.example.enums.ListBuildingOrderEnum;
import com.example.enums.OrderEnum;
import com.example.pojo.bo.BuildingIdleStatBo;
import com.example.pojo.bo.HouseAgeStatBo;
import com.example.pojo.bo.ListBuildingBo;
import com.example.pojo.bo.ListBuildingIdleBo;
import com.example.pojo.bo.ListHouseSiteBo;
import com.example.pojo.dto.BuildingIdleStatDto;
import com.example.pojo.dto.HouseAgeStatDto;
import com.example.pojo.dto.ListBuildingDto;
import com.example.pojo.dto.ListBuildingIdleDto;
import com.example.pojo.dto.ListSiteNameDto;
import com.example.pojo.dto.ListSiteNumDto;
import com.example.pojo.vo.BuildingIdleStatVo;
import com.example.pojo.vo.HouseAgeStatVo;
import com.example.pojo.vo.ListBuildingIdleVo;
import com.example.pojo.vo.ListBuildingVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletResponse;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Stream;

@Slf4j
@Service
public class HouseWzBuildManageDetailsService {
    private final HouseBuildingDao houseBuildingDao;
    private final WzOtnAreaDao wzOtnAreaDao;

    public HouseWzBuildManageDetailsService(HouseBuildingDao houseBuildingDao, WzOtnAreaDao wzOtnAreaDao) {
        this.houseBuildingDao = houseBuildingDao;
        this.wzOtnAreaDao = wzOtnAreaDao;
    }

    public Rsp<PageVo<ListBuildingIdleVo>> ideList(ListBuildingIdleDto dto) {
        Page<HouseBuildingPo> page = new Page<>(dto.getPage().getPageNum(), dto.getPage().getPageSize());
        List<HouseBuildingPo> list = getBuildingIdle(dto, page);
        List<ListBuildingIdleVo> vos = new ArrayList<>();
        for (HouseBuildingPo po : list) {
            vos.add(new ListBuildingIdleVo(po));
        }
        PageVo<ListBuildingIdleVo> pageVo = new PageVo<>(vos, page);
        return Rsp.ok(pageVo);
    }

    public void ideListExport(ListBuildingIdleDto.ListBuildingIdleMapDTO dto) {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder
                .getRequestAttributes();
        if (servletRequestAttributes == null) {
            return;
        }
        HttpServletResponse response = servletRequestAttributes.getResponse();
        if (response == null) {
            return;
        }
        String filename = "房屋空置_空置1000平方米以上"
                + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")) + ".xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncodeUtil.encode(filename));
        response.setContentType("application/octet-stream");
        try (SXSSFWorkbook wb = new SXSSFWorkbook()) {
            writeIdleList(new ListBuildingIdleDto(dto), wb);
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    /**
     * 查询房屋空置
     */
    private List<HouseBuildingPo> getBuildingIdle(ListBuildingIdleDto dto, Page<HouseBuildingPo> page) {
        ListBuildingIdleBo bo = new ListBuildingIdleBo();
        if (dto.getMap() != null && dto.getMap().getStatisticalMonth() != null) {
            bo.setYearMonth(dto.getMap().getStatisticalMonth());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getCity()) && !"全省".equals(dto.getMap().getCity())) {
            bo.setAreaNo(dto.getMap().getCity());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getCounty())) {
            bo.setCityNo(dto.getMap().getCounty());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getBuildingAddressNumber())) {
            bo.setSiteNum(dto.getMap().getBuildingAddressNumber());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getBuildingNameAlias())) {
            bo.setSiteName(dto.getMap().getBuildingNameAlias());
        }
        if (dto.getMap() != null && dto.getMap().getOrder() != null && dto.getMap().getSidx() != null) {
            if (OrderEnum.asc.equals(dto.getMap().getOrder())) {
                page.addOrder(OrderItem.asc(dto.getMap().getSidx().getColumnName()));
            } else {
                page.addOrder(OrderItem.desc(dto.getMap().getSidx().getColumnName()));
            }
        } else {
            page.addOrder(OrderItem.desc(ListBuildingIdleOrderEnum.buildingAreaIdelArea.getColumnName()));
        }
        return houseBuildingDao.listBuildingIdle(page, bo);
    }

    /**
     * 写空置1000平米以上建筑文件
     */
    private void writeIdleList(ListBuildingIdleDto dto, SXSSFWorkbook wb) {
        Page<HouseBuildingPo> page = new Page<>(1, Long.MAX_VALUE);
        List<HouseBuildingPo> list = getBuildingIdle(dto, page);
        DataFormat dataFormat = wb.createDataFormat();
        // 数字样式
        CellStyle numberCellStyle = wb.createCellStyle();
        numberCellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
        numberCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 默认样式
        CellStyle baseCellStyle = wb.createCellStyle();
        baseCellStyle.setAlignment(HorizontalAlignment.CENTER);
        baseCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        CellStyle percentCellStyle = wb.createCellStyle();
        percentCellStyle.setDataFormat(dataFormat.getFormat("0.00%"));
        percentCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        SXSSFSheet sheet = wb.createSheet();
        AtomicInteger rowIndex = new AtomicInteger(0);
        // 表头
        Row headerRow = sheet.createRow(rowIndex.getAndIncrement());
        List<String> headers = Stream.of("资产所属单位(一级)", "资产所属单位(二级)", "资产所属单位(三级)", "局址编号",
                "局址别名", "建筑别名", "地段", "建筑面积(㎡)","建筑面积-出租(㎡)", "建筑面积-闲置(㎡)", "建筑面积-自用(㎡)",
                "建筑面积-不可使用(㎡)", "上级土地名称", "得房率", "房屋来源", "取得日期", "房龄开始年份", "投资主体", "管理层级",
                "房屋结构", "楼层总数", "是否临街", "是否有院落", "整栋是否独有", "是否有房产证", "无房产证原因", "未关联资产",
                "资产编号", "资产标签号", "使用状态", "建筑用途", "权属状态", "建筑占地面积(㎡)", "使用面积(㎡)",
                "使用面积-自用(㎡)", "使用面积-出租(㎡)", "使用面积-闲置(㎡)", "使用面积-不可使用(㎡)", "楼长姓名",
                "楼长所在单位", "经度", "纬度", "实际产权人").toList();
        int headerLength = headers.size();
        for (int i = 0; i < headerLength; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
            cell.setCellStyle(baseCellStyle);
            // 根据内容长度设置列宽
            int columnWidth = headers.get(i).length() * 256 * 2 + 256;
            sheet.setColumnWidth(i, columnWidth);
        }
        // 数据
        for (HouseBuildingPo po : list) {
            AtomicInteger columnIndex = new AtomicInteger(0);
            Row row = sheet.createRow(rowIndex.getAndIncrement());
            // 资产所属单位(一级)
            Cell firstUnitCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getFirstUnit() != null) {
                firstUnitCell.setCellValue(po.getFirstUnit());
            }
            // 二级单位
            Cell areaNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAreaName() != null) {
                areaNameCell.setCellValue(po.getAreaName());
            }
            // 三级单位
            Cell cityNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getCityName() != null) {
                cityNameCell.setCellValue(po.getCityName());
            }
            // 局址编号
            Cell siteNumCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getSiteName() != null) {
                siteNumCell.setCellValue(po.getSiteNum());
            }
            // 局址别名
            Cell siteNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getSiteName() != null) {
                siteNameCell.setCellValue(po.getSiteName());
            }
            // 建筑别名
            Cell buildingNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingName() != null) {
                buildingNameCell.setCellValue(po.getBuildingName());
            }
            // 地段
            Cell areaSectorCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAreaSector() != null) {
                areaSectorCell.setCellValue(po.getAreaSector());
            }
            // 建筑面积(㎡)
            Cell buildingAreaCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingArea() != null) {
                buildingAreaCell.setCellValue(po.getBuildingArea().doubleValue());
            }
            buildingAreaCell.setCellStyle(numberCellStyle);
            // 建筑面积-出租(㎡)
            Cell buildingAreaRentCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingAreaRent() != null) {
                buildingAreaRentCell.setCellValue(po.getBuildingAreaRent().doubleValue());
            }
            buildingAreaRentCell.setCellStyle(numberCellStyle);
            // 建筑面积-闲置(㎡)
            Cell buildingAreaIdleCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingAreaIdle() != null) {
                buildingAreaIdleCell.setCellValue(po.getBuildingAreaIdle().doubleValue());
            }
            buildingAreaIdleCell.setCellStyle(numberCellStyle);
            // 建筑面积-自用(㎡)
            Cell buildingAreaSelfUseCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingAreaSelfUse() != null) {
                buildingAreaSelfUseCell.setCellValue(po.getBuildingAreaSelfUse().doubleValue());
            }
            buildingAreaSelfUseCell.setCellStyle(numberCellStyle);
            // 建筑面积-不可使用(㎡)
            Cell buildingAreaUnusableCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingAreaUnusable() != null) {
                buildingAreaUnusableCell.setCellValue(po.getBuildingAreaUnusable().doubleValue());
            }
            buildingAreaUnusableCell.setCellStyle(numberCellStyle);
            // 上级土地名称
            Cell landNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getLandName() != null) {
                landNameCell.setCellValue(po.getLandName());
            }
            // 得房率
            Cell housingAcquisitionRateCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getHousingAcquisitionRate() != null) {
                housingAcquisitionRateCell.setCellValue(po.getHousingAcquisitionRate().doubleValue());
            }
            housingAcquisitionRateCell.setCellStyle(percentCellStyle);
            // 房屋来源
            Cell housingSourceCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getHousingSource() != null) {
                housingSourceCell.setCellValue(po.getHousingSource());
            }
            // 取得日期
            Cell acquisitionDateCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAcquisitionDate() != null) {
                acquisitionDateCell.setCellValue(po.getAcquisitionDate());
            }
            // 房龄开始年份
            Cell houseYearBeganCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getHouseYearBegan() != null) {
                houseYearBeganCell.setCellValue(po.getHouseYearBegan());
            }
            // 投资主体
            Cell investorCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getInvestor() != null) {
                investorCell.setCellValue(po.getInvestor());
            }
            // 管理层级
            Cell managementLevelCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getManagementLevel() != null) {
                managementLevelCell.setCellValue(po.getManagementLevel());
            }
            // 房屋结构
            Cell buildingStructureCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingStructure() != null) {
                buildingStructureCell.setCellValue(po.getBuildingStructure());
            }
            // 楼层总数
            Cell totalFloorsCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getTotalFloors() != null) {
                totalFloorsCell.setCellValue(po.getTotalFloors());
            }
            // 是否临街
            Cell frontageCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getFrontage() != null) {
                frontageCell.setCellValue(po.getFrontage());
            }
            // 是否有院落
            Cell courtyardCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getCourtyard() != null) {
                courtyardCell.setCellValue(po.getCourtyard());
            }
            // 整栋是否独有
            Cell wholeBuildingCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getWholeBuilding() != null) {
                wholeBuildingCell.setCellValue(po.getWholeBuilding());
            }
            // 是否有房产证
            Cell propertyOwnershipCertificateCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getPropertyOwnershipCertificate() != null) {
                propertyOwnershipCertificateCell.setCellValue(po.getPropertyOwnershipCertificate());
            }
            // 无房产证原因
            Cell noPropertyOwnershipCertificateReasonCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getNoPropertyOwnershipCertificateReason() != null) {
                noPropertyOwnershipCertificateReasonCell.setCellValue(
                        po.getNoPropertyOwnershipCertificateReason());
            }
            // 未关联资产
            Cell unrelatedAssetsCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUnrelatedAssets() != null) {
                unrelatedAssetsCell.setCellValue(po.getUnrelatedAssets());
            }
            // 资产编号
            Cell assetsNumCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAssetsNum() != null) {
                assetsNumCell.setCellValue(po.getAssetsNum());
            }
            // 资产标签号
            Cell assetsTagNumCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAssetsTagNum() != null) {
                assetsTagNumCell.setCellValue(po.getAssetsTagNum());
            }
            // 使用状态
            Cell usageStatusCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsageStatus() != null) {
                usageStatusCell.setCellValue(po.getUsageStatus());
            }
            // 建筑用途
            Cell buildingUseCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingUse() != null) {
                buildingUseCell.setCellValue(po.getBuildingUse());
            }
            // 权属状态
            Cell ownershipStatusCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getOwnershipStatus() != null) {
                ownershipStatusCell.setCellValue(po.getOwnershipStatus());
            }
            // 建筑占地面积(㎡)
            Cell floorAreaCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getFloorArea() != null) {
                floorAreaCell.setCellValue(po.getFloorArea().doubleValue());
            }
            floorAreaCell.setCellStyle(numberCellStyle);
            // 使用面积(㎡)
            Cell usableAreaCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableArea() != null) {
                usableAreaCell.setCellValue(po.getUsableArea().doubleValue());
            }
            usableAreaCell.setCellStyle(numberCellStyle);
            // 使用面积-自用(㎡)
            Cell usableAreaSelfUseCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableAreaSelfUse() != null) {
                usableAreaSelfUseCell.setCellValue(po.getUsableAreaSelfUse().doubleValue());
            }
            usableAreaSelfUseCell.setCellStyle(numberCellStyle);
            // 使用面积-出租(㎡)
            Cell usableAreaRentCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableAreaRent() != null) {
                usableAreaRentCell.setCellValue(po.getUsableAreaRent().doubleValue());
            }
            usableAreaRentCell.setCellStyle(numberCellStyle);
            // 使用面积-闲置(㎡)
            Cell usableAreaIdleCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableAreaIdle() != null) {
                usableAreaIdleCell.setCellValue(po.getUsableAreaIdle().doubleValue());
            }
            usableAreaIdleCell.setCellStyle(numberCellStyle);
            // 使用面积-不可使用(㎡)
            Cell usableAreaUnusableCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableAreaUnusable() != null) {
                usableAreaUnusableCell.setCellValue(po.getUsableAreaUnusable().doubleValue());
            }
            usableAreaUnusableCell.setCellStyle(numberCellStyle);
            // 楼长姓名
            Cell communityAssistantNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getCommunityAssistantName() != null) {
                communityAssistantNameCell.setCellValue(po.getCommunityAssistantName());
            }
            // 楼长所在单位
            Cell communityAssistantUnitCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getCommunityAssistantUnit() != null) {
                communityAssistantUnitCell.setCellValue(po.getCommunityAssistantUnit());
            }
            // 经度
            Cell lngJtCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getLngJt() != null) {
                lngJtCell.setCellValue(po.getLngJt().toString());
            }
            // 纬度
            Cell latJtCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getLatJt() != null) {
                latJtCell.setCellValue(po.getLatJt().toString());
            }
            // 实际产权人
            Cell propertyOwnerCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getPropertyOwner() != null) {
                propertyOwnerCell.setCellValue(po.getPropertyOwner());
            }
        }
    }

    public Rsp<PageVo<String>> listSiteName(ListSiteNameDto dto) {
        if ("全省".equals(dto.getMap().getCity())) {
            dto.getMap().setCity(null);
            dto.getMap().setCounty(null);
        }
        ListHouseSiteBo bo = new ListHouseSiteBo();
        bo.setAreaNo(dto.getMap().getCity());
        bo.setCityNo(dto.getMap().getCounty());
        bo.setSiteName(dto.getMap().getAlias());
        Page<HouseSitePo> page = new Page<>(dto.getPage().getPageNum(), dto.getPage().getPageSize());
        List<HouseSitePo> list = wzOtnAreaDao.listHouseSite(page, bo);
        List<String> vos = list.stream().map(HouseSitePo::getSiteName).toList();
        PageVo<String> pageVo = new PageVo<>(vos, page);
        return Rsp.ok(pageVo);
    }

    public Rsp<PageVo<String>> listSiteNum(ListSiteNumDto dto) {
        if ("全省".equals(dto.getMap().getCity())) {
            dto.getMap().setCity(null);
            dto.getMap().setCounty(null);
        }
        ListHouseSiteBo bo = new ListHouseSiteBo();
        bo.setAreaNo(dto.getMap().getCity());
        bo.setCityNo(dto.getMap().getCounty());
        bo.setSiteNum(dto.getMap().getCode());
        Page<HouseSitePo> page = new Page<>(dto.getPage().getPageNum(), dto.getPage().getPageSize());
        List<HouseSitePo> list = wzOtnAreaDao.listHouseSite(page, bo);
        List<String> vos = list.stream().map(HouseSitePo::getSiteNum).toList();
        PageVo<String> pageVo = new PageVo<>(vos, page);
        return Rsp.ok(pageVo);
    }

    public Rsp<PageVo<ListBuildingVo>> listBuilding(ListBuildingDto dto) {
        Page<HouseBuildingPo> page = new Page<>(dto.getPage().getPageNum(), dto.getPage().getPageSize());
        List<HouseBuildingPo> list = getBuildingList(dto, page);
        List<ListBuildingVo> vos = new ArrayList<>();
        for (HouseBuildingPo po : list) {
            vos.add(new ListBuildingVo(po));
        }
        PageVo<ListBuildingVo> pageVo = new PageVo<>(vos, page);
        return Rsp.ok(pageVo);
    }

    /**
     * 查询不动产建筑
     */
    private List<HouseBuildingPo> getBuildingList(ListBuildingDto dto, Page<HouseBuildingPo> page) {
        ListBuildingBo bo = new ListBuildingBo();
        if (dto.getMap() != null && dto.getMap().getStatisticalMonth() != null) {
            bo.setYearMonth(dto.getMap().getStatisticalMonth());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getCity()) && !"全省".equals(dto.getMap().getCity())) {
            bo.setAreaNo(dto.getMap().getCity());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getCounty())) {
            bo.setCityNo(dto.getMap().getCounty());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getBuildingAddressNumber())) {
            bo.setSiteNum(dto.getMap().getBuildingAddressNumber());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getBuildingNameAlias())) {
            bo.setSiteName(dto.getMap().getBuildingNameAlias());
        }
        if (dto.getMap() != null && dto.getMap().getOrder() != null && dto.getMap().getSidx() != null) {
            if (OrderEnum.asc.equals(dto.getMap().getOrder())) {
                page.addOrder(OrderItem.asc(dto.getMap().getSidx().getColumnName()));
            } else {
                page.addOrder(OrderItem.desc(dto.getMap().getSidx().getColumnName()));
            }
        } else {
            page.addOrder(OrderItem.desc(ListBuildingOrderEnum.buildingArea.getColumnName()));
        }
        return houseBuildingDao.listBuilding(page, bo);
    }

    public void listBuildingExport(ListBuildingDto.ListBuildingMapDTO dto) {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder
                .getRequestAttributes();
        if (servletRequestAttributes == null) {
            return;
        }
        HttpServletResponse response = servletRequestAttributes.getResponse();
        if (response == null) {
            return;
        }
        String filename = "房屋管理_房产记录"
                + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")) + ".xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncodeUtil.encode(filename));
        response.setContentType("application/octet-stream");
        try (SXSSFWorkbook wb = new SXSSFWorkbook()) {
            writeListBuilding(new ListBuildingDto(dto), wb);
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    /**
     * 写不动产建筑文件
     */
    private void writeListBuilding(ListBuildingDto dto, SXSSFWorkbook wb) {
        Page<HouseBuildingPo> page = new Page<>(1, Long.MAX_VALUE);
        List<HouseBuildingPo> list = getBuildingList(dto, page);
        DataFormat dataFormat = wb.createDataFormat();
        // 数字样式
        CellStyle numberCellStyle = wb.createCellStyle();
        numberCellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
        numberCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 默认样式
        CellStyle baseCellStyle = wb.createCellStyle();
        baseCellStyle.setAlignment(HorizontalAlignment.CENTER);
        baseCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 百分比样式
        CellStyle percentCellStyle = wb.createCellStyle();
        percentCellStyle.setDataFormat(dataFormat.getFormat("0.00%"));
        percentCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        SXSSFSheet sheet = wb.createSheet();
        AtomicInteger rowIndex = new AtomicInteger(0);
        // 表头
        Row headerRow = sheet.createRow(rowIndex.getAndIncrement());
        List<String> headers = Stream.of("资产所属单位(一级)", "资产所属单位(二级)", "资产所属单位(三级)", "局址编号",
                "局址别名", "建筑别名", "地段", "建筑面积(㎡)","建筑面积-出租(㎡)", "建筑面积-闲置(㎡)", "建筑面积-自用(㎡)",
                "建筑面积-不可使用(㎡)", "上级土地名称", "得房率", "房屋来源", "取得日期", "房龄开始年份", "投资主体", "管理层级",
                "房屋结构", "楼层总数", "是否临街", "是否有院落", "整栋是否独有", "是否有房产证", "无房产证原因", "未关联资产",
                "资产编号", "资产标签号", "使用状态", "建筑用途", "权属状态", "建筑占地面积(㎡)", "使用面积(㎡)",
                "使用面积-自用(㎡)", "使用面积-出租(㎡)", "使用面积-闲置(㎡)", "使用面积-不可使用(㎡)", "楼长姓名",
                "楼长所在单位", "经度", "纬度", "实际产权人").toList();
        int headerLength = headers.size();
        for (int i = 0; i < headerLength; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
            cell.setCellStyle(baseCellStyle);
            // 根据内容长度设置列宽
            int columnWidth = headers.get(i).length() * 256 * 2 + 256;
            sheet.setColumnWidth(i, columnWidth);
        }
        // 数据
        for (HouseBuildingPo po : list) {
            AtomicInteger columnIndex = new AtomicInteger(0);
            Row row = sheet.createRow(rowIndex.getAndIncrement());
            // 资产所属单位(一级)
            Cell firstUnitCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getFirstUnit() != null) {
                firstUnitCell.setCellValue(po.getFirstUnit());
            }
            // 二级单位
            Cell areaNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAreaName() != null) {
                areaNameCell.setCellValue(po.getAreaName());
            }
            // 三级单位
            Cell cityNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getCityName() != null) {
                cityNameCell.setCellValue(po.getCityName());
            }
            // 局址编号
            Cell siteNumCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getSiteName() != null) {
                siteNumCell.setCellValue(po.getSiteNum());
            }
            // 局址别名
            Cell siteNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getSiteName() != null) {
                siteNameCell.setCellValue(po.getSiteName());
            }
            // 建筑别名
            Cell buildingNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingName() != null) {
                buildingNameCell.setCellValue(po.getBuildingName());
            }
            // 地段
            Cell areaSectorCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAreaSector() != null) {
                areaSectorCell.setCellValue(po.getAreaSector());
            }
            // 建筑面积(㎡)
            Cell buildingAreaCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingArea() != null) {
                buildingAreaCell.setCellValue(po.getBuildingArea().doubleValue());
            }
            buildingAreaCell.setCellStyle(numberCellStyle);
            // 建筑面积-出租(㎡)
            Cell buildingAreaRentCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingAreaRent() != null) {
                buildingAreaRentCell.setCellValue(po.getBuildingAreaRent().doubleValue());
            }
            buildingAreaRentCell.setCellStyle(numberCellStyle);
            // 建筑面积-闲置(㎡)
            Cell buildingAreaIdleCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingAreaIdle() != null) {
                buildingAreaIdleCell.setCellValue(po.getBuildingAreaIdle().doubleValue());
            }
            buildingAreaIdleCell.setCellStyle(numberCellStyle);
            // 建筑面积-自用(㎡)
            Cell buildingAreaSelfUseCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingAreaSelfUse() != null) {
                buildingAreaSelfUseCell.setCellValue(po.getBuildingAreaSelfUse().doubleValue());
            }
            buildingAreaSelfUseCell.setCellStyle(numberCellStyle);
            // 建筑面积-不可使用(㎡)
            Cell buildingAreaUnusableCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingAreaUnusable() != null) {
                buildingAreaUnusableCell.setCellValue(po.getBuildingAreaUnusable().doubleValue());
            }
            buildingAreaUnusableCell.setCellStyle(numberCellStyle);
            // 上级土地名称
            Cell landNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getLandName() != null) {
                landNameCell.setCellValue(po.getLandName());
            }
            // 得房率
            Cell housingAcquisitionRateCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getHousingAcquisitionRate() != null) {
                housingAcquisitionRateCell.setCellValue(po.getHousingAcquisitionRate().doubleValue());
            }
            housingAcquisitionRateCell.setCellStyle(percentCellStyle);
            // 房屋来源
            Cell housingSourceCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getHousingSource() != null) {
                housingSourceCell.setCellValue(po.getHousingSource());
            }
            // 取得日期
            Cell acquisitionDateCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAcquisitionDate() != null) {
                acquisitionDateCell.setCellValue(po.getAcquisitionDate());
            }
            // 房龄开始年份
            Cell houseYearBeganCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getHouseYearBegan() != null) {
                houseYearBeganCell.setCellValue(po.getHouseYearBegan());
            }
            // 投资主体
            Cell investorCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getInvestor() != null) {
                investorCell.setCellValue(po.getInvestor());
            }
            // 管理层级
            Cell managementLevelCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getManagementLevel() != null) {
                managementLevelCell.setCellValue(po.getManagementLevel());
            }
            // 房屋结构
            Cell buildingStructureCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingStructure() != null) {
                buildingStructureCell.setCellValue(po.getBuildingStructure());
            }
            // 楼层总数
            Cell totalFloorsCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getTotalFloors() != null) {
                totalFloorsCell.setCellValue(po.getTotalFloors());
            }
            // 是否临街
            Cell frontageCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getFrontage() != null) {
                frontageCell.setCellValue(po.getFrontage());
            }
            // 是否有院落
            Cell courtyardCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getCourtyard() != null) {
                courtyardCell.setCellValue(po.getCourtyard());
            }
            // 整栋是否独有
            Cell wholeBuildingCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getWholeBuilding() != null) {
                wholeBuildingCell.setCellValue(po.getWholeBuilding());
            }
            // 是否有房产证
            Cell propertyOwnershipCertificateCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getPropertyOwnershipCertificate() != null) {
                propertyOwnershipCertificateCell.setCellValue(po.getPropertyOwnershipCertificate());
            }
            // 无房产证原因
            Cell noPropertyOwnershipCertificateReasonCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getNoPropertyOwnershipCertificateReason() != null) {
                noPropertyOwnershipCertificateReasonCell.setCellValue(
                        po.getNoPropertyOwnershipCertificateReason());
            }
            // 未关联资产
            Cell unrelatedAssetsCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUnrelatedAssets() != null) {
                unrelatedAssetsCell.setCellValue(po.getUnrelatedAssets());
            }
            // 资产编号
            Cell assetsNumCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAssetsNum() != null) {
                assetsNumCell.setCellValue(po.getAssetsNum());
            }
            // 资产标签号
            Cell assetsTagNumCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getAssetsTagNum() != null) {
                assetsTagNumCell.setCellValue(po.getAssetsTagNum());
            }
            // 使用状态
            Cell usageStatusCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsageStatus() != null) {
                usageStatusCell.setCellValue(po.getUsageStatus());
            }
            // 建筑用途
            Cell buildingUseCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getBuildingUse() != null) {
                buildingUseCell.setCellValue(po.getBuildingUse());
            }
            // 权属状态
            Cell ownershipStatusCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getOwnershipStatus() != null) {
                ownershipStatusCell.setCellValue(po.getOwnershipStatus());
            }
            // 建筑占地面积(㎡)
            Cell floorAreaCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getFloorArea() != null) {
                floorAreaCell.setCellValue(po.getFloorArea().doubleValue());
            }
            floorAreaCell.setCellStyle(numberCellStyle);
            // 使用面积(㎡)
            Cell usableAreaCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableArea() != null) {
                usableAreaCell.setCellValue(po.getUsableArea().doubleValue());
            }
            usableAreaCell.setCellStyle(numberCellStyle);
            // 使用面积-自用(㎡)
            Cell usableAreaSelfUseCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableAreaSelfUse() != null) {
                usableAreaSelfUseCell.setCellValue(po.getUsableAreaSelfUse().doubleValue());
            }
            usableAreaSelfUseCell.setCellStyle(numberCellStyle);
            // 使用面积-出租(㎡)
            Cell usableAreaRentCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableAreaRent() != null) {
                usableAreaRentCell.setCellValue(po.getUsableAreaRent().doubleValue());
            }
            usableAreaRentCell.setCellStyle(numberCellStyle);
            // 使用面积-闲置(㎡)
            Cell usableAreaIdleCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableAreaIdle() != null) {
                usableAreaIdleCell.setCellValue(po.getUsableAreaIdle().doubleValue());
            }
            usableAreaIdleCell.setCellStyle(numberCellStyle);
            // 使用面积-不可使用(㎡)
            Cell usableAreaUnusableCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getUsableAreaUnusable() != null) {
                usableAreaUnusableCell.setCellValue(po.getUsableAreaUnusable().doubleValue());
            }
            usableAreaUnusableCell.setCellStyle(numberCellStyle);
            // 楼长姓名
            Cell communityAssistantNameCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getCommunityAssistantName() != null) {
                communityAssistantNameCell.setCellValue(po.getCommunityAssistantName());
            }
            // 楼长所在单位
            Cell communityAssistantUnitCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getCommunityAssistantUnit() != null) {
                communityAssistantUnitCell.setCellValue(po.getCommunityAssistantUnit());
            }
            // 经度
            Cell lngJtCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getLngJt() != null) {
                lngJtCell.setCellValue(po.getLngJt().toString());
            }
            // 纬度
            Cell latJtCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getLatJt() != null) {
                latJtCell.setCellValue(po.getLatJt().toString());
            }
            // 实际产权人
            Cell propertyOwnerCell = row.createCell(columnIndex.getAndIncrement());
            if (po.getPropertyOwner() != null) {
                propertyOwnerCell.setCellValue(po.getPropertyOwner());
            }
        }
    }

    public Rsp<PageVo<HouseAgeStatVo>> houseAgeStat(HouseAgeStatDto dto) {
        HouseAgeStatPo houseAgeStatPos = getHouseAgeStatPos(dto);
        if (houseAgeStatPos == null) {
            return Rsp.ok();
        }
        HouseAgeStatVo vo = new HouseAgeStatVo(houseAgeStatPos, false);
        PageVo<HouseAgeStatVo> pageVo = new PageVo<>(Collections.singletonList(vo));
        return Rsp.ok(pageVo);
    }

    /**
     * 不动产自有房产房龄统计
     */
    public HouseAgeStatPo getHouseAgeStatPos(HouseAgeStatDto dto) {
        HouseAgeStatBo bo = new HouseAgeStatBo();
        if (dto.getMap() != null && dto.getMap().getStatisticalMonth() != null) {
            bo.setYearMonth(dto.getMap().getStatisticalMonth());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getCity()) && !"全省".equals(dto.getMap().getCity())) {
            bo.setAreaNo(dto.getMap().getCity());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getCounty())) {
            bo.setCityNo(dto.getMap().getCounty());
        }
        // 查询三级单位统计
        List<HouseAgeStatPo> houseAgeStatPos = houseBuildingDao.houseAgeStat(bo);
        if (CollectionUtils.isEmpty(houseAgeStatPos)) {
            return null;
        }
        // 一级单位统计
        HouseAgeStatPo firstUnitPo = new HouseAgeStatPo();
        firstUnitPo.setAreaName("全省");
        firstUnitPo.setCityName("全省");
        LinkedHashMap<String, HouseAgeStatPo> secondUnitPoMap = new LinkedHashMap<>();
        // 二级单位统计
        for (HouseAgeStatPo houseAgeStatPo : houseAgeStatPos) {
            secondUnitPoMap.putIfAbsent(houseAgeStatPo.getAreaNo(),
                    new HouseAgeStatPo(houseAgeStatPo.getAreaNo(), houseAgeStatPo.getAreaName()));
            HouseAgeStatPo secondUnitPo = secondUnitPoMap.get(houseAgeStatPo.getAreaNo());
            secondUnitPo.update(houseAgeStatPo);
        }
        for (HouseAgeStatPo value : secondUnitPoMap.values()) {
            firstUnitPo.update(value);
        }
        return firstUnitPo;
    }

    public void queryHouseYearExport(HouseAgeStatDto.HouseAgeStatMapDTO dto) {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder
                .getRequestAttributes();
        if (servletRequestAttributes == null) {
            return;
        }
        HttpServletResponse response = servletRequestAttributes.getResponse();
        if (response == null) {
            return;
        }
        String filename = "房屋管理_房龄统计"
                + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")) + ".xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncodeUtil.encode(filename));
        response.setContentType("application/octet-stream");
        try (SXSSFWorkbook wb = new SXSSFWorkbook()) {
            writeQueryHouseYear(new HouseAgeStatDto(dto), wb);
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    /**
     * 写房龄统计文件
     */
    private void writeQueryHouseYear(HouseAgeStatDto dto, SXSSFWorkbook wb) {
        HouseAgeStatPo firstPo = getHouseAgeStatPos(dto);
        DataFormat dataFormat = wb.createDataFormat();
        // 整数样式
        CellStyle integerCellStyle = wb.createCellStyle();
        integerCellStyle.setDataFormat(dataFormat.getFormat("#,##0"));
        integerCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 数字样式
        CellStyle numberCellStyle = wb.createCellStyle();
        numberCellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
        numberCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 默认样式
        CellStyle baseCellStyle = wb.createCellStyle();
        baseCellStyle.setAlignment(HorizontalAlignment.CENTER);
        baseCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        SXSSFSheet sheet = wb.createSheet();
        AtomicInteger rowIndex = new AtomicInteger(0);
        // 表头
        Row headerRow = sheet.createRow(rowIndex.getAndIncrement());
        List<String> headers = Stream.of("资产所属单位(一级)", "资产所属单位(二级)", "资产所属单位(三级)",
                "1-10年", "11-20年", "21-30年", "31-40年", "41-50年", "50年以上", "平均房龄").toList();
        int headerLength = headers.size();
        for (int i = 0; i < headerLength; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
            cell.setCellStyle(baseCellStyle);
            // 根据内容长度设置列宽
            int columnWidth = headers.get(i).length() * 256 * 2 + 256;
            sheet.setColumnWidth(i, columnWidth);
        }
        if (firstPo == null) {
            return;
        }
        // 数据
        AtomicInteger columnIndex1 = new AtomicInteger(0);
        // 一级单位统计
        Row row1 = sheet.createRow(rowIndex.getAndIncrement());
        // 资产所属单位(一级)
        Cell firstUnitCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getAreaName() != null) {
            firstUnitCell1.setCellValue(firstPo.getAreaName());
        }
        // 二级单位
        Cell areaNameCell1 = row1.createCell(columnIndex1.getAndIncrement());
//        if (firstPo.getAreaName() != null) {
//            areaNameCell1.setCellValue(firstPo.getAreaName());
//        }
        // 三级单位
        Cell cityNameCell1 = row1.createCell(columnIndex1.getAndIncrement());
//        if (firstPo.getCityName() != null) {
//            cityNameCell1.setCellValue(firstPo.getCityName());
//        }
        // 1-10年
        Cell a1Cell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getA1() != null) {
            a1Cell1.setCellValue(firstPo.getA1());
        }
        a1Cell1.setCellStyle(integerCellStyle);
        // 11-20年
        Cell a2Cell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getA2() != null) {
            a2Cell1.setCellValue(firstPo.getA2());
        }
        a2Cell1.setCellStyle(integerCellStyle);
        // 21-30年
        Cell a3Cell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getA3() != null) {
            a3Cell1.setCellValue(firstPo.getA3());
        }
        a3Cell1.setCellStyle(integerCellStyle);
        // 31-40年
        Cell a4Cell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getA4() != null) {
            a4Cell1.setCellValue(firstPo.getA4());
        }
        a4Cell1.setCellStyle(integerCellStyle);
        // 41-50年
        Cell a5Cell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getA5() != null) {
            a5Cell1.setCellValue(firstPo.getA5());
        }
        a5Cell1.setCellStyle(integerCellStyle);
        // 50年以上
        Cell a6Cell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getA6() != null) {
            a6Cell1.setCellValue(firstPo.getA6());
        }
        a6Cell1.setCellStyle(integerCellStyle);
        // 平均房龄
        Cell houseAgeAvgCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getHouseAgeAvg() != null) {
            houseAgeAvgCell1.setCellValue(firstPo.getHouseAgeAvg().doubleValue());
        }
        houseAgeAvgCell1.setCellStyle(numberCellStyle);
        // 二级单位统计
        for (HouseAgeStatPo secondPo : firstPo.getChildren()) {
            AtomicInteger columnIndex2 = new AtomicInteger(0);
            Row row2 = sheet.createRow(rowIndex.getAndIncrement());
            // 资产所属单位(一级)
            Cell firstUnitCell2 = row2.createCell(columnIndex2.getAndIncrement());
//            if (secondPo.getAreaName() != null) {
//                firstUnitCell2.setCellValue(secondPo.getAreaName());
//            }
            // 二级单位
            Cell areaNameCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getAreaName() != null) {
                areaNameCell2.setCellValue(secondPo.getAreaName());
            }
            // 三级单位
            Cell cityNameCell2 = row2.createCell(columnIndex2.getAndIncrement());
//            if (secondPo.getCityName() != null) {
//                cityNameCell2.setCellValue(secondPo.getCityName());
//            }
            // 1-10年
            Cell a1Cell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getA1() != null) {
                a1Cell2.setCellValue(secondPo.getA1());
            }
            a1Cell2.setCellStyle(integerCellStyle);
            // 11-20年
            Cell a2Cell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getA2() != null) {
                a2Cell2.setCellValue(secondPo.getA2());
            }
            a2Cell2.setCellStyle(integerCellStyle);
            // 21-30年
            Cell a3Cell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getA3() != null) {
                a3Cell2.setCellValue(secondPo.getA3());
            }
            a3Cell2.setCellStyle(integerCellStyle);
            // 31-40年
            Cell a4Cell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getA4() != null) {
                a4Cell2.setCellValue(secondPo.getA4());
            }
            a4Cell2.setCellStyle(integerCellStyle);
            // 41-50年
            Cell a5Cell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getA5() != null) {
                a5Cell2.setCellValue(secondPo.getA5());
            }
            a5Cell2.setCellStyle(integerCellStyle);
            // 50年以上
            Cell a6Cell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getA6() != null) {
                a6Cell2.setCellValue(secondPo.getA6());
            }
            a6Cell2.setCellStyle(integerCellStyle);
            // 平均房龄
            Cell houseAgeAvgCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getHouseAgeAvg() != null) {
                houseAgeAvgCell2.setCellValue(secondPo.getHouseAgeAvg().doubleValue());
            }
            houseAgeAvgCell2.setCellStyle(numberCellStyle);
            // 三级单位统计
            for (HouseAgeStatPo thirdPo : secondPo.getChildren()) {
                AtomicInteger columnIndex3 = new AtomicInteger(0);
                Row row3 = sheet.createRow(rowIndex.getAndIncrement());
                // 资产所属单位(一级)
                Cell firstUnitCell3 = row3.createCell(columnIndex3.getAndIncrement());
//                if (thirdPo.getAreaName() != null) {
//                    firstUnitCell3.setCellValue(thirdPo.getAreaName());
//                }
                // 二级单位
                Cell areaNameCell3 = row3.createCell(columnIndex3.getAndIncrement());
//                if (thirdPo.getAreaName() != null) {
//                    areaNameCell3.setCellValue(thirdPo.getAreaName());
//                }
                // 三级单位
                Cell cityNameCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getCityName() != null) {
                    cityNameCell3.setCellValue(thirdPo.getCityName());
                }
                // 1-10年
                Cell a1Cell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getA1() != null) {
                    a1Cell3.setCellValue(thirdPo.getA1());
                }
                a1Cell3.setCellStyle(integerCellStyle);
                // 11-20年
                Cell a2Cell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getA2() != null) {
                    a2Cell3.setCellValue(thirdPo.getA2());
                }
                a2Cell3.setCellStyle(integerCellStyle);
                // 21-30年
                Cell a3Cell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getA3() != null) {
                    a3Cell3.setCellValue(thirdPo.getA3());
                }
                a3Cell3.setCellStyle(integerCellStyle);
                // 31-40年
                Cell a4Cell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getA4() != null) {
                    a4Cell3.setCellValue(thirdPo.getA4());
                }
                a4Cell3.setCellStyle(integerCellStyle);
                // 41-50年
                Cell a5Cell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getA5() != null) {
                    a5Cell3.setCellValue(thirdPo.getA5());
                }
                a5Cell3.setCellStyle(integerCellStyle);
                // 50年以上
                Cell a6Cell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getA6() != null) {
                    a6Cell3.setCellValue(thirdPo.getA6());
                }
                a6Cell3.setCellStyle(integerCellStyle);
                // 平均房龄
                Cell houseAgeAvgCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getHouseAgeAvg() != null) {
                    houseAgeAvgCell3.setCellValue(thirdPo.getHouseAgeAvg().doubleValue());
                }
                houseAgeAvgCell3.setCellStyle(numberCellStyle);
            }
        }
    }

    public Rsp<PageVo<BuildingIdleStatVo>> buildingIdleStat(BuildingIdleStatDto dto) {
        BuildingIdleStatPo buildingIdleStatPos = getBuildingIdleStatPos(dto);
        if (buildingIdleStatPos == null) {
            return Rsp.ok();
        }
        BuildingIdleStatVo vo = new BuildingIdleStatVo(buildingIdleStatPos, false);
        PageVo<BuildingIdleStatVo> pageVo = new PageVo<>(Collections.singletonList(vo));
        return Rsp.ok(pageVo);
    }

    private BuildingIdleStatPo getBuildingIdleStatPos(BuildingIdleStatDto dto) {
        BuildingIdleStatBo bo = new BuildingIdleStatBo();
        if (dto.getMap() != null && dto.getMap().getStatisticalMonth() != null) {
            bo.setYearMonth(dto.getMap().getStatisticalMonth());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getCity()) && !"全省".equals(dto.getMap().getCity())) {
            bo.setAreaNo(dto.getMap().getCity());
        }
        if (dto.getMap() != null && StringUtils.hasText(dto.getMap().getCounty())) {
            bo.setCityNo(dto.getMap().getCounty());
        }
        // 查询三级单位统计
        List<BuildingIdleStatPo> buildingIdleStatPos = houseBuildingDao.buildingIdleStat(bo);
        if (CollectionUtils.isEmpty(buildingIdleStatPos)) {
            return null;
        }
        // 一级单位统计
        BuildingIdleStatPo firstUnitPo = new BuildingIdleStatPo();
        firstUnitPo.setAreaName("全省");
        firstUnitPo.setCityName("全省");
        LinkedHashMap<String, BuildingIdleStatPo> secondUnitPoMap = new LinkedHashMap<>();
        // 二级单位统计
        for (BuildingIdleStatPo houseAgeStatPo : buildingIdleStatPos) {
            secondUnitPoMap.putIfAbsent(houseAgeStatPo.getAreaNo(),
                    new BuildingIdleStatPo(houseAgeStatPo.getAreaNo(), houseAgeStatPo.getAreaName()));
            BuildingIdleStatPo secondUnitPo = secondUnitPoMap.get(houseAgeStatPo.getAreaNo());
            secondUnitPo.update(houseAgeStatPo);
        }
        for (BuildingIdleStatPo value : secondUnitPoMap.values()) {
            firstUnitPo.update(value);
        }
        return firstUnitPo;
    }

    public void buildingIdleStatExport(BuildingIdleStatDto.BuildingIdleStatMapDTO dto) {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder
                .getRequestAttributes();
        if (servletRequestAttributes == null) {
            return;
        }
        HttpServletResponse response = servletRequestAttributes.getResponse();
        if (response == null) {
            return;
        }
        String filename = "房屋管理_闲置管理"
                + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")) + ".xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncodeUtil.encode(filename));
        response.setContentType("application/octet-stream");
        try (SXSSFWorkbook wb = new SXSSFWorkbook()) {
            writeBuildingIdleStat(new BuildingIdleStatDto(dto), wb);
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    /**
     * 写闲置管理文件
     */
    private void writeBuildingIdleStat(BuildingIdleStatDto dto, SXSSFWorkbook wb) {
        BuildingIdleStatPo firstPo = getBuildingIdleStatPos(dto);
        DataFormat dataFormat = wb.createDataFormat();
        // 整数样式
        CellStyle integerCellStyle = wb.createCellStyle();
        integerCellStyle.setDataFormat(dataFormat.getFormat("#,##0"));
        integerCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 数字样式
        CellStyle numberCellStyle = wb.createCellStyle();
        numberCellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
        numberCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 默认样式
        CellStyle baseCellStyle = wb.createCellStyle();
        baseCellStyle.setAlignment(HorizontalAlignment.CENTER);
        baseCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 百分比样式
        CellStyle percentCellStyle = wb.createCellStyle();
        percentCellStyle.setDataFormat(dataFormat.getFormat("0.00%"));
        percentCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        SXSSFSheet sheet = wb.createSheet();
        AtomicInteger rowIndex = new AtomicInteger(0);
        // 表头
        Row headerRow = sheet.createRow(rowIndex.getAndIncrement());
        List<String> headers = Stream.of("资产所属单位(一级)", "资产所属单位(二级)", "资产所属单位(三级)",
                "建筑面积(㎡)", "闲置建筑数量", "建筑面积-自用(㎡)","建筑面积-出租(㎡)", "建筑面积-闲置(㎡)", "闲置率(%)").toList();
        int headerLength = headers.size();
        for (int i = 0; i < headerLength; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
            cell.setCellStyle(baseCellStyle);
            // 根据内容长度设置列宽
            int columnWidth = headers.get(i).length() * 256 * 2 + 256;
            sheet.setColumnWidth(i, columnWidth);
        }
        if (firstPo == null) {
            return;
        }
        // 数据
        AtomicInteger columnIndex1 = new AtomicInteger(0);
        // 一级单位统计
        Row row1 = sheet.createRow(rowIndex.getAndIncrement());
        // 资产所属单位(一级)
        Cell firstUnitCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getAreaName() != null) {
            firstUnitCell1.setCellValue(firstPo.getAreaName());
        }
        // 二级单位
        Cell areaNameCell1 = row1.createCell(columnIndex1.getAndIncrement());
//        if (firstPo.getAreaName() != null) {
//            areaNameCell1.setCellValue(firstPo.getAreaName());
//        }
        // 三级单位
        Cell cityNameCell1 = row1.createCell(columnIndex1.getAndIncrement());
//        if (firstPo.getCityName() != null) {
//            cityNameCell1.setCellValue(firstPo.getCityName());
//        }
        // 建筑面积(㎡)
        Cell buildingAreaSumCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getBuildingAreaSum() != null) {
            buildingAreaSumCell1.setCellValue(firstPo.getBuildingAreaSum().doubleValue());
        }
        buildingAreaSumCell1.setCellStyle(numberCellStyle);
        // 闲置建筑数量
        Cell idleCountCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getIdleCount() != null) {
            idleCountCell1.setCellValue(firstPo.getIdleCount());
        }
        idleCountCell1.setCellStyle(integerCellStyle);
        // 建筑面积-自用(㎡)
        Cell buildingAreaSelfUseSumCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getBuildingAreaSelfUseSum() != null) {
            buildingAreaSelfUseSumCell1.setCellValue(firstPo.getBuildingAreaSelfUseSum().doubleValue());
        }
        buildingAreaSelfUseSumCell1.setCellStyle(numberCellStyle);
        // 建筑面积-出租(㎡)
        Cell buildingAreaRentSumCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getBuildingAreaRentSum() != null) {
            buildingAreaRentSumCell1.setCellValue(firstPo.getBuildingAreaRentSum().doubleValue());
        }
        buildingAreaRentSumCell1.setCellStyle(numberCellStyle);
        // 建筑面积-闲置(㎡)
        Cell buildingAreaIdleSumCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getBuildingAreaIdleSum() != null) {
            buildingAreaIdleSumCell1.setCellValue(firstPo.getBuildingAreaIdleSum().doubleValue());
        }
        buildingAreaIdleSumCell1.setCellStyle(numberCellStyle);
        // 闲置率
        Cell idlePercentCell1 = row1.createCell(columnIndex1.getAndIncrement());
        if (firstPo.getIdlePercent() != null) {
            idlePercentCell1.setCellValue(firstPo.getIdlePercent().doubleValue());
        }
        idlePercentCell1.setCellStyle(percentCellStyle);
        // 二级单位统计
        for (BuildingIdleStatPo secondPo : firstPo.getChildren()) {
            AtomicInteger columnIndex2 = new AtomicInteger(0);
            // 一级单位统计
            Row row2 = sheet.createRow(rowIndex.getAndIncrement());
            // 资产所属单位(一级)
            Cell firstUnitCell2 = row2.createCell(columnIndex2.getAndIncrement());
//            if (secondPo.getAreaName() != null) {
//                firstUnitCell2.setCellValue(secondPo.getAreaName());
//            }
            // 二级单位
            Cell areaNameCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getAreaName() != null) {
                areaNameCell2.setCellValue(secondPo.getAreaName());
            }
            // 三级单位
            Cell cityNameCell2 = row2.createCell(columnIndex2.getAndIncrement());
//            if (secondPo.getCityName() != null) {
//                cityNameCell2.setCellValue(secondPo.getCityName());
//            }
            // 建筑面积(㎡)
            Cell buildingAreaSumCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getBuildingAreaSum() != null) {
                buildingAreaSumCell2.setCellValue(secondPo.getBuildingAreaSum().doubleValue());
            }
            buildingAreaSumCell2.setCellStyle(numberCellStyle);
            // 闲置建筑数量
            Cell idleCountCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getIdleCount() != null) {
                idleCountCell2.setCellValue(secondPo.getIdleCount());
            }
            idleCountCell2.setCellStyle(integerCellStyle);
            // 建筑面积-自用(㎡)
            Cell buildingAreaSelfUseSumCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getBuildingAreaSelfUseSum() != null) {
                buildingAreaSelfUseSumCell2.setCellValue(secondPo.getBuildingAreaSelfUseSum().doubleValue());
            }
            buildingAreaSelfUseSumCell2.setCellStyle(numberCellStyle);
            // 建筑面积-出租(㎡)
            Cell buildingAreaRentSumCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getBuildingAreaRentSum() != null) {
                buildingAreaRentSumCell2.setCellValue(secondPo.getBuildingAreaRentSum().doubleValue());
            }
            buildingAreaRentSumCell2.setCellStyle(numberCellStyle);
            // 建筑面积-闲置(㎡)
            Cell buildingAreaIdleSumCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getBuildingAreaIdleSum() != null) {
                buildingAreaIdleSumCell2.setCellValue(secondPo.getBuildingAreaIdleSum().doubleValue());
            }
            buildingAreaIdleSumCell2.setCellStyle(numberCellStyle);
            // 闲置率
            Cell idlePercentCell2 = row2.createCell(columnIndex2.getAndIncrement());
            if (secondPo.getIdlePercent() != null) {
                idlePercentCell2.setCellValue(secondPo.getIdlePercent().doubleValue());
            }
            idlePercentCell2.setCellStyle(percentCellStyle);
            // 三级单位统计
            for (BuildingIdleStatPo thirdPo : secondPo.getChildren()) {
                AtomicInteger columnIndex3 = new AtomicInteger(0);
                Row row3 = sheet.createRow(rowIndex.getAndIncrement());
                // 资产所属单位(一级)
                Cell firstUnitCell3 = row3.createCell(columnIndex3.getAndIncrement());
//                if (thirdPo.getAreaName() != null) {
//                    firstUnitCell3.setCellValue(thirdPo.getAreaName());
//                }
                // 二级单位
                Cell areaNameCell3 = row3.createCell(columnIndex3.getAndIncrement());
//                if (thirdPo.getAreaName() != null) {
//                    areaNameCell3.setCellValue(thirdPo.getAreaName());
//                }
                // 三级单位
                Cell cityNameCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getCityName() != null) {
                    cityNameCell3.setCellValue(thirdPo.getCityName());
                }
                // 建筑面积(㎡)
                Cell buildingAreaSumCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getBuildingAreaSum() != null) {
                    buildingAreaSumCell3.setCellValue(thirdPo.getBuildingAreaSum().doubleValue());
                }
                buildingAreaSumCell3.setCellStyle(numberCellStyle);
                // 闲置建筑数量
                Cell idleCountCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getIdleCount() != null) {
                    idleCountCell3.setCellValue(thirdPo.getIdleCount());
                }
                idleCountCell3.setCellStyle(integerCellStyle);
                // 建筑面积-自用(㎡)
                Cell buildingAreaSelfUseSumCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getBuildingAreaSelfUseSum() != null) {
                    buildingAreaSelfUseSumCell3.setCellValue(thirdPo.getBuildingAreaSelfUseSum().doubleValue());
                }
                buildingAreaSelfUseSumCell3.setCellStyle(numberCellStyle);
                // 建筑面积-出租(㎡)
                Cell buildingAreaRentSumCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getBuildingAreaRentSum() != null) {
                    buildingAreaRentSumCell3.setCellValue(thirdPo.getBuildingAreaRentSum().doubleValue());
                }
                buildingAreaRentSumCell3.setCellStyle(numberCellStyle);
                // 建筑面积-闲置(㎡)
                Cell buildingAreaIdleSumCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getBuildingAreaIdleSum() != null) {
                    buildingAreaIdleSumCell3.setCellValue(thirdPo.getBuildingAreaIdleSum().doubleValue());
                }
                buildingAreaIdleSumCell3.setCellStyle(numberCellStyle);
                // 闲置率
                Cell idlePercentCell3 = row3.createCell(columnIndex3.getAndIncrement());
                if (thirdPo.getIdlePercent() != null) {
                    idlePercentCell3.setCellValue(thirdPo.getIdlePercent().doubleValue());
                }
                idlePercentCell3.setCellStyle(percentCellStyle);
            }
        }
    }
}