gdc-finance-api/src/main/java/com/example/dao/house/HouseBuildingDao.java

package com.example.dao.house;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.entity.house.BuildingIdleStatPo;
import com.example.entity.house.BuildingOfficeAreaStatPo;
import com.example.entity.house.HouseAgeStatPo;
import com.example.entity.house.HouseBuildingPo;
import com.example.pojo.bo.BuildingIdleStatBo;
import com.example.pojo.bo.BuildingOfficeAreaStatBo;
import com.example.pojo.bo.HouseAgeStatBo;
import com.example.pojo.bo.ListBuildingBo;
import com.example.pojo.bo.ListBuildingIdleBo;
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 HouseBuildingDao {
    /**
     * 查询不动产自有房产空置
     */
    @Select("""
<script>
select
*
from house.building_idle_strategy
<choose>
  <when test="dto.yearMonth != null">
    where year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    where year_month = (select max(year_month) from house.building_idle_strategy)
  </otherwise>
</choose>
<if test="dto.areaNo != null and dto.areaNo != ''">
  and area_no = #{dto.areaNo}
</if>
<if test="dto.cityNo != null and dto.cityNo != ''">
  and city_no = #{dto.cityNo}
</if>
<if test="dto.siteName != null and dto.siteName != ''">
  and site_name = #{dto.siteName}
</if>
<if test="dto.siteNum != null and dto.siteNum != ''">
  and site_num = #{dto.siteNum}
</if>
</script>
""")
    List<HouseBuildingPo> listBuildingIdle(Page<HouseBuildingPo> page, @Param("dto") ListBuildingIdleBo dto);

    /**
     * 查询不动产建筑
     */
    @Select("""
<script>
select
*
from house.building_month
<choose>
  <when test="dto.yearMonth != null">
    where year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    where year_month = (select max(year_month) from house.building_month)
  </otherwise>
</choose>
<if test="dto.areaNo != null and dto.areaNo != ''">
  and area_no = #{dto.areaNo}
</if>
<if test="dto.cityNo != null and dto.cityNo != ''">
  and city_no = #{dto.cityNo}
</if>
<if test="dto.siteName != null and dto.siteName != ''">
  and site_name = #{dto.siteName}
</if>
<if test="dto.siteNum != null and dto.siteNum != ''">
  and site_num = #{dto.siteNum}
</if>
</script>
""")
    List<HouseBuildingPo> listBuilding(Page<HouseBuildingPo> page, @Param("dto") ListBuildingBo dto);

    /**
     * 不动产自有房产房龄统计
     */
    @Select("""
<script>
with
t101 as (
select
    area_no,
    city_no,
    count(house_age <![CDATA[ >= ]]> 0 and house_age <![CDATA[ <= ]]> 10 or null) as a1,
    count(house_age <![CDATA[ >= ]]> 11 and house_age <![CDATA[ <= ]]> 20 or null) as a2,
    count(house_age <![CDATA[ >= ]]> 21 and house_age <![CDATA[ <= ]]> 30 or null) as a3,
    count(house_age <![CDATA[ >= ]]> 31 and house_age <![CDATA[ <= ]]> 40 or null) as a4,
    count(house_age <![CDATA[ >= ]]> 41 and house_age <![CDATA[ <= ]]> 50 or null) as a5,
    count(house_age <![CDATA[ > ]]> 50 or null) as a6,
    count(house_age is null or null) as au,
    count(house_age) as total,
    sum(house_age) as house_age_sum,
    round(avg(house_age),
    2) as house_age_avg
from
    house.building_month
<choose>
  <when test="dto.yearMonth != null">
    where year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    where year_month = (select max(year_month) from house.building_month)
  </otherwise>
</choose>
<if test="dto.areaNo != null and dto.areaNo != ''">
  and area_no = #{dto.areaNo}
</if>
<if test="dto.cityNo != null and dto.cityNo != ''">
  and city_no = #{dto.cityNo}
</if>
group by
    area_no,
    city_no
),
t102 as (
select
    b.id as area_no,
    b."name" as area_name,
    a.id as city_no,
    a."name" as city_name,
    coalesce(c.a1, 0) as a1,
    coalesce(c.a2, 0) as a2,
    coalesce(c.a3, 0) as a3,
    coalesce(c.a4, 0) as a4,
    coalesce(c.a5, 0) as a5,
    coalesce(c.a6, 0) as a6,
    coalesce(c.au, 0) as au,
    coalesce(c.total, 0) as total,
    coalesce(c.house_age_sum, 0) as house_age_sum,
    c.house_age_avg as house_age_avg
from
    common.organization a
left join common.organization b on
    a.parent_id = b.id
left join t101 c on
    a.id = c.city_no
where
    a.grade = 2
    and a.unhide = 1
<if test="dto.areaNo != null and dto.areaNo != ''">
  and a.parent_id = #{dto.areaNo}
</if>
<if test="dto.cityNo != null and dto.cityNo != ''">
  and a.id = #{dto.cityNo}
</if>
order by
    b.order_num,
    a.order_num
)
select
    *
from
    t102
</script>
""")
    List<HouseAgeStatPo> houseAgeStat(@Param("dto") HouseAgeStatBo dto);

    /**
     * 不动产自有房产闲置统计
     */
    @Select("""
<script>
with
t101 as (
select
    area_no,
    city_no,
    count(building_area_idle <![CDATA[ > ]]> 0 or null) as idle_count,
    sum(building_area) as building_area_sum,
    sum(building_area_self_use) as building_area_self_use_sum,
    sum(building_area_rent) as building_area_rent_sum,
    sum(building_area_idle) as building_area_idle_sum,
    case
        when (sum(building_area_idle) + sum(building_area_rent)) = 0 then null
        else round(sum(building_area_idle) / (sum(building_area_idle) + sum(building_area_rent)),
        4)
    end as idle_percent
from
    house.building_month
<choose>
  <when test="dto.yearMonth != null">
    where year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    where year_month = (select max(year_month) from house.building_month)
  </otherwise>
</choose>
<if test="dto.areaNo != null and dto.areaNo != ''">
  and area_no = #{dto.areaNo}
</if>
<if test="dto.cityNo != null and dto.cityNo != ''">
  and city_no = #{dto.cityNo}
</if>
group by
    area_no,
    city_no
),
t102 as (
select
    b.id as area_no,
    b."name" as area_name,
    a.id as city_no,
    a."name" as city_name,
    coalesce(c.idle_count, 0) as idle_count,
    coalesce(c.building_area_sum, 0) as building_area_sum,
    coalesce(c.building_area_self_use_sum, 0) as building_area_self_use_sum,
    coalesce(c.building_area_rent_sum, 0) as building_area_rent_sum,
    coalesce(c.building_area_idle_sum, 0) as building_area_idle_sum,
    c.idle_percent as idle_percent
from
    common.organization a
left join common.organization b on
    a.parent_id = b.id
left join t101 c on
    a.id = c.city_no
where
    a.grade = 2
    and a.unhide = 1
<if test="dto.areaNo != null and dto.areaNo != ''">
  and a.parent_id = #{dto.areaNo}
</if>
<if test="dto.cityNo != null and dto.cityNo != ''">
  and a.id = #{dto.cityNo}
</if>
order by
    b.order_num, a.order_num
)
select * from t102
</script>
""")
    List<BuildingIdleStatPo> buildingIdleStat(@Param("dto") BuildingIdleStatBo dto);

    /**
     * 不动产自有房产人均办公面积统计
     */
    @Select("""
<script>
select * from house.building_office_area_stat
<choose>
  <when test="dto.yearMonth != null">
    where year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    where year_month = (select max(year_month) from house.building_office_area_stat)
  </otherwise>
</choose>
<if test="dto.areaNo != null and dto.areaNo != ''">
  and area_no = #{dto.areaNo}
</if>
<if test="dto.cityNo != null and dto.cityNo != ''">
  and city_no = #{dto.cityNo}
</if>
order by
    area_order,
    city_order
</script>
""")
    List<BuildingOfficeAreaStatPo> buildingOfficeAreaStat(@Param("dto") BuildingOfficeAreaStatBo dto);
}