finance-api/src/main/java/com/nokia/financeapi/dao/house/HouseResourceMapMapper.java

package com.nokia.financeapi.dao.house;

import com.nokia.financeapi.pojo.dto.GetBuildingAreaStatDto;
import com.nokia.financeapi.pojo.dto.GetBuildingMapDto;
import com.nokia.financeapi.pojo.dto.GetBuildingOptionsDto;
import com.nokia.financeapi.pojo.dto.GetBuildingRepairStatDto;
import com.nokia.financeapi.pojo.dto.GetHouseNoticeDto;
import com.nokia.financeapi.pojo.dto.GetLandBuildingStatDto;
import com.nokia.financeapi.pojo.vo.GetBuildingAreaStatVo;
import com.nokia.financeapi.pojo.vo.GetBuildingMapVo;
import com.nokia.financeapi.pojo.vo.GetBuildingOptionsVo;
import com.nokia.financeapi.pojo.vo.GetBuildingRepairStatVo;
import com.nokia.financeapi.pojo.vo.GetHouseNoticeVo;
import com.nokia.financeapi.pojo.vo.GetLandBuildingStatVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface HouseResourceMapMapper {
    /**
     * 获取最新月份全省或地市的建筑面积统计
     */
    @Select("""
<script>
with
t1 as (
select
    sum(case when building_use = '综合用房' then building_area else 0 end) as area_synthesis,
    sum(case when building_use = '设备用房' then building_area else 0 end) as area_equipment,
    sum(case when building_use = '营销用房' then building_area else 0 end) as area_marketing,
    sum(case when building_use = '附属用房' then building_area else 0 end) as area_affiliate,
    sum(case when building_use = '行政用房' then building_area else 0 end) as area_administration,
    sum(case when building_use not in ('综合用房', '设备用房', '营销用房', '附属用房', '行政用房') then building_area else 0 end) as area_other,
    sum(building_area_self_use) as area_self_use,
    sum(building_area_rent) as area_rent,
    sum(building_area) - sum(building_area_self_use) - sum(building_area_rent) as area_unused,
    sum(building_area) as area_total
from
    house.building_month
where
    year_month = (
    select
        max(year_month)
    from
        house.building_month)
    <if test="dto.city != null and dto.city != ''">
      and city = #{dto.city}
    </if>
)
select
    round(area_self_use / 10000, 2) as area_self_use,
    round(area_rent / 10000, 2) as area_rent,
    round(area_unused / 10000, 2) as area_unused,
    round(area_synthesis / area_total * 100, 2) as percent_synthesis,
    round(area_equipment / area_total * 100, 2) as percent_equipment,
    round(area_marketing / area_total * 100, 2) as percent_marketing,
    round(area_affiliate / area_total * 100, 2) as percent_affiliate,
    round(area_administration / area_total * 100, 2) as percent_administration,
    round(area_other / area_total * 100, 2) as percent_other,
    round(area_self_use / area_total * 100, 2) as percent_self_use,
    round(area_rent / area_total * 100, 2) as percent_rent,
    round(area_unused / area_total * 100, 2) as percent_unused
from t1
</script>
""")
    GetBuildingAreaStatVo getBuildingAreaStat(@Param("dto") GetBuildingAreaStatDto dto);

    /**
     * 统计最新月份各个地市的建筑和土地数量
     */
    @Select("""
with
t1 as (
select
    city as area_name,
    count(1) as building_count
from
    house.building_month
where
    year_month = (
    select
        max(year_month)
    from
        house.building_month)
group by
    city
),
t2 as (
select
    city as area_name,
    count(1) as land_count
from
    house.land_month
where
    year_month = (
    select
        max(year_month)
    from
        house.land_month)
group by
    city
)
select
    t1.area_name,
    t1.building_count,
    t2.land_count
from
    t1
join t2 on
    t1.area_name = t2.area_name
order by
    t1.area_name
""")
    List<GetLandBuildingStatVo> getCityLandBuildingStat();

    /**
     * 统计最新月份某地市各个区县的建筑和土地数量
     */
    @Select("""
<script>
with
t1 as (
select
    district as area_name,
    count(1) as building_count
from
    house.building_month
where
    city = #{dto.city}
    and district is not null
    and district != ''
    and year_month = (
    select
        max(year_month)
    from
        house.building_month)
group by
    district
),
t2 as (
select
    district as area_name,
    count(1) as land_count
from
    house.land_month
where
    city = #{dto.city}
    and district is not null
    and district != ''
    and year_month = (
    select
        max(year_month)
    from
        house.land_month)
group by
    district
)
select
    t1.area_name,
    t1.building_count,
    t2.land_count
from
    t1
join t2 on
    t1.area_name = t2.area_name
order by t1.area_name
</script>
""")
    List<GetLandBuildingStatVo> getDistrictLandBuildingStat(@Param("dto") GetLandBuildingStatDto dto);

    /**
     * 获取建筑数据最新的日期
     */
    @Select("""
select max(year_month) from house.building_month
""")
    Integer getBuildingMonthMaxDate();

    /**
     * 获取区县最大面积建筑
     */
    @Select("""
with
t101 as (
select
    *
from
    house.building_month
where
    lng_bd09 is not null
    and lat_bd09 is not null
    and year_month = #{dto.endDate}
    and city = #{dto.city}
    and district = #{dto.district}
order by
    building_area desc
limit 10
),
t102 as (
select
    building_id,
    sum(amount) as maintenance_cost
from
    house.building_repair_main_month
where
    repair_type != '财务预提冲销'
    and exists (select 1 from t101 where t101.building_id = house.building_repair_main_month.building_id)
    and year_month >= #{dto.startDate}
    and year_month <= #{dto.endDate}
group by
    building_id
),
t103 as (
select
t101.*,
t102.maintenance_cost
from t101 left join t102 on t101.building_id = t102.building_id
)
select
    building_id,
    address,
    area_sector,
    building_name,
    acquisition_date,
    building_use,
    building_area,
    building_area_idle,
    building_area_rent,
    round(maintenance_cost, 2) as maintenance_cost,
    building_img,
    lng_bd09 as lng,
    lat_bd09 as lat
from
    t103
order by
    building_area desc
""")
    List<GetBuildingMapVo> getBuildingByDistrict(@Param("dto") GetBuildingMapDto dto);

    /**
     * 获取坐标范围内的建筑，相同坐标保留建筑面积最大的
     */
    @Select("""
with
t101 as (
select
    *
from
    house.building_month
where
    year_month = #{dto.endDate}
    and lng_bd09 >= #{dto.leftLng}
    and lng_bd09 <= #{dto.rightLng}
    and lat_bd09 >= #{dto.leftLat}
    and lat_bd09 <= #{dto.rightLat}
),
t102 as (
select
    distinct on
    (lng_bd09, lat_bd09) *
from
    t101
order by
    lng_bd09,
    lat_bd09,
    building_area desc
),
t103 as (
select
    building_id,
    sum(amount) as maintenance_cost
from
    house.building_repair_main_month
where
    repair_type != '财务预提冲销'
    and exists (select 1 from t102 where t102.building_id = house.building_repair_main_month.building_id)
    and year_month >= #{dto.startDate}
    and year_month <= #{dto.endDate}
group by
    building_id
),
t104 as (
select
t102.*,
t103.maintenance_cost
from t102 left join t103 on t102.building_id = t103.building_id
)
select
    building_id,
    address,
    area_sector,
    building_name,
    acquisition_date,
    building_use,
    building_area,
    building_area_idle,
    building_area_rent,
    round(maintenance_cost, 2) as maintenance_cost,
    building_img,
    lng_bd09 as lng,
    lat_bd09 as lat
from
    t104
order by
    building_area desc
limit 150
""")
    List<GetBuildingMapVo> getBuildingByCoordinate(@Param("dto") GetBuildingMapDto dto);

    /**
     * 统计最新年份各个地市的维修费
     */
    @Select("""
with
t1 as (
select
    city,
    sum(case when repair_type = '零星维修' then amount else 0 end) as odd_sum,
    sum(amount) as total_sum
from
    house.building_repair_main_month
where
    repair_type != '财务预提冲销'
    and year_num = (
    select
        max(year_num)
    from
        house.building_repair_main_month)
group by
    city
),
t2 as (
select
    *,
    case
        when total_sum = 0 then 0
        else odd_sum / total_sum * 100
    end as odd_percent
from
    t1
),
t3 as (
select
    *
from t2 left join house.second_unit_sort on t2.city = house.second_unit_sort.second_unit
order by house.second_unit_sort.sort desc
)
select
    city as area_name,
    round(odd_sum / 10000,
    2) as odd_sum,
    round(total_sum / 10000,
    2) as total_sum,
    round(odd_percent,
    2) as odd_percent
from
    t3
""")
    List<GetBuildingRepairStatVo> getCityRepairStat();

    /**
     * 统计最新年份某个地市的各个区县的维修费
     */
    @Select("""
with
t1 as (
select
    city,
    district,
    sum(case when repair_type = '零星维修' then amount else 0 end) as odd_sum,
    sum(amount) as total_sum
from
    house.building_repair_main_month
where
    repair_type != '财务预提冲销'
    and year_num = (
    select
        max(year_num)
    from
        house.building_repair_main_month)
    and district is not null
    and district != ''
    and city = #{dto.city}
group by
    city,
    district
),
t2 as (
select
    *,
    case
        when total_sum = 0 then 0
        else odd_sum / total_sum * 100
    end as odd_percent
from
    t1
)
select
    district as area_name,
    round(odd_sum / 10000,
    2) as odd_sum,
    round(total_sum / 10000,
    2) as total_sum,
    round(odd_percent,
    2) as odd_percent
from
    t2
order by
    total_sum
""")
    List<GetBuildingRepairStatVo> getDistrictRepairStat(@Param("dto") GetBuildingRepairStatDto dto);

    /**
     * 获取不动产公告
     */
    @Select("""
select * from house.notices
where city = #{dto.city}
order by create_time desc
""")
    List<GetHouseNoticeVo> getNotice(@Param("dto") GetHouseNoticeDto dto);

    /**
     * 获取区县内的建筑，相同坐标保留建筑面积最大的
     */
    @Select("""
with
t101 as (
select
    distinct on
    (lng_bd09, lat_bd09) *
from
    house.building_month
where
    lng_bd09 is not null
    and lat_bd09 is not null
    and year_month = #{dto.endDate}
    and city = #{dto.city}
    and district = #{dto.district}
order by
    lng_bd09,
    lat_bd09,
    building_area desc
),
t102 as (
select
    building_id,
    sum(amount) as maintenance_cost
from
    house.building_repair_main_month
where
    repair_type != '财务预提冲销'
    and exists (select 1 from t101 where t101.building_id = house.building_repair_main_month.building_id)
    and year_month >= #{dto.startDate}
    and year_month <= #{dto.endDate}
group by
    building_id
),
t103 as (
select
t101.*,
t102.maintenance_cost
from t101 left join t102 on t101.building_id = t102.building_id
)
select
    building_id,
    address,
    area_sector,
    building_name,
    acquisition_date,
    building_use,
    building_area,
    building_area_idle,
    building_area_rent,
    round(maintenance_cost, 2) as maintenance_cost,
    building_img,
    lng_bd09 as lng,
    lat_bd09 as lat
from
    t103
order by
    building_area desc
""")
    List<GetBuildingOptionsVo> getBuildingOptions(@Param("dto") GetBuildingOptionsDto dto);

    /**
     * 获取省本部建筑选项，相同坐标保留建筑面积最大的
     */
    @Select("""
with
t101 as (
select
    distinct on
    (lng_bd09, lat_bd09) *
from
    house.building_month
where
    lng_bd09 is not null
    and lat_bd09 is not null
    and year_month = #{dto.endDate}
    and area_name = #{dto.city}
order by
    lng_bd09,
    lat_bd09,
    building_area desc
),
t102 as (
select
    building_id,
    sum(amount) as maintenance_cost
from
    house.building_repair_main_month
where
    repair_type != '财务预提冲销'
    and exists (select 1 from t101 where t101.building_id = house.building_repair_main_month.building_id)
    and year_month >= #{dto.startDate}
    and year_month <= #{dto.endDate}
group by
    building_id
),
t103 as (
select
t101.*,
t102.maintenance_cost
from t101 left join t102 on t101.building_id = t102.building_id
)
select
    building_id,
    address,
    area_sector,
    building_name,
    acquisition_date,
    building_use,
    building_area,
    building_area_idle,
    building_area_rent,
    round(maintenance_cost, 2) as maintenance_cost,
    building_img,
    lng_bd09 as lng,
    lat_bd09 as lat
from
    t103
order by
    building_area desc
""")
    List<GetBuildingOptionsVo> getSbbBuildingOptions(@Param("dto") GetBuildingOptionsDto dto);

    /**
     * 获取省本部建筑，相同坐标保留建筑面积最大的
     */
    @Select("""
with
t101 as (
select
    distinct on
    (lng_bd09, lat_bd09) *
from
    house.building_month
where
    lng_bd09 is not null
    and lat_bd09 is not null
    and year_month = #{dto.endDate}
    and area_name = #{dto.city}
order by
    lng_bd09,
    lat_bd09,
    building_area desc
),
t102 as (
select
    building_id,
    sum(amount) as maintenance_cost
from
    house.building_repair_main_month
where
    repair_type != '财务预提冲销'
    and exists (select 1 from t101 where t101.building_id = house.building_repair_main_month.building_id)
    and year_month >= #{dto.startDate}
    and year_month <= #{dto.endDate}
group by
    building_id
),
t103 as (
select
t101.*,
t102.maintenance_cost
from t101 left join t102 on t101.building_id = t102.building_id
)
select
    building_id,
    address,
    area_sector,
    building_name,
    acquisition_date,
    building_use,
    building_area,
    building_area_idle,
    building_area_rent,
    round(maintenance_cost, 2) as maintenance_cost,
    building_img,
    lng_bd09 as lng,
    lat_bd09 as lat
from
    t103
order by
    building_area desc
""")
    List<GetBuildingMapVo> getSbbBuildings(@Param("dto") GetBuildingMapDto dto);
}