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

package com.example.dao.house;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.entity.house.HouseLandPo;
import com.example.entity.house.LandIdleStatPo;
import com.example.pojo.bo.LandIdleStatBo;
import com.example.pojo.bo.ListLandBo;
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 HouseLandDao {
    /**
     * 不动产土地闲置统计
     */
    @Select("""
<script>
with
t101 as (
select
    area_no,
    city_no,
    count(land_area_idle <![CDATA[ > ]]> 0 or null) as idle_count,
    sum(total_land_area) as total_land_area_sum,
    sum(land_area_self_use) as land_area_self_use_sum,
    sum(land_area_unusable) as land_area_unusable_sum,
    sum(land_area_rent) as land_area_rent_sum,
    sum(land_area_idle) as land_area_idle_sum,
    case
        when (sum(land_area_idle) + sum(land_area_rent)) = 0 then null
        else round(sum(land_area_idle) / (sum(land_area_idle) + sum(land_area_rent)),
        4)
    end as idle_percent
from
    house.land_month
<choose>
  <when test="dto.yearMonth != null">
    where year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    where year_month = (select max(year_month) from house.land_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.total_land_area_sum, 0) as total_land_area_sum,
    coalesce(c.land_area_self_use_sum, 0) as land_area_self_use_sum,
    coalesce(c.land_area_unusable_sum, 0) as land_area_unusable_sum,
    coalesce(c.land_area_rent_sum, 0) as land_area_rent_sum,
    coalesce(c.land_area_idle_sum, 0) as land_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<LandIdleStatPo> landIdleStat(@Param("dto") LandIdleStatBo dto);

    /**
     * 查询不动产土地
     */
    @Select("""
<script>
select
*
from house.land_month
<choose>
  <when test="dto.yearMonth != null">
    where year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    where year_month = (select max(year_month) from house.land_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<HouseLandPo> listLand(Page<HouseLandPo> page, @Param("dto") ListLandBo dto);
}