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.GetLandBuildingStatDto;
import com.nokia.financeapi.pojo.vo.GetBuildingAreaStatVo;
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, 2) as area_self_use,
    round(area_rent, 2) as area_rent,
    round(area_unused, 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);
}