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

package com.example.dao.house;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.pojo.bo.ListRentInCompareBo;
import com.example.pojo.bo.ListRentInContractBo;
import com.example.pojo.bo.ListRentInTopBo;
import com.example.pojo.bo.RentInContractStatBo;
import com.example.pojo.bo.RentInUseStatBo;
import com.example.pojo.po.house.ListRentInComparePo;
import com.example.pojo.po.house.RentInContractStatPo;
import com.example.pojo.po.house.RentInMonthPo;
import com.example.pojo.po.house.RentInUseStatPo;
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 HouseRentInDao {
    @Select("""
<script>
with
t101 as (
select
    area_no,
    city_no,
    count(1) as total,
    sum(gross_amount_month) * 12 as gross_amount_year,
    round(avg(unit_price),
    2) as unit_price_avg,
    round(sum(case when use_type = '营业' then building_area else 0 end),
    2) as yy,
    round(sum(case when use_type = '办公' then building_area else 0 end),
    2) as bg,
    round(sum(case when use_type = '通信设施机房' then building_area else 0 end),
    2) as tx,
    round(sum(case when use_type = '其他租入用途' then building_area else 0 end),
    2) as qt,
    round(sum(case when use_type in ('营业', '办公', '通信设施机房', '其他租入用途') then building_area else 0 end),
    2) as zj
from
    house.rent_in_month
where
    contract_status = '履行中'
<choose>
  <when test="dto.yearMonth != null">
    and year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    and year_month = (select max(year_month) from house.rent_in_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.total,
    0) as total,
    coalesce(c.gross_amount_year,
    0) as gross_amount_year,
    coalesce(c.unit_price_avg,
    0) as unit_price_avg,
    coalesce(c.yy,
    0) as yy,
    coalesce(c.bg,
    0) as bg,
    coalesce(c.tx,
    0) as tx,
    coalesce(c.qt,
    0) as qt,
    coalesce(c.zj,
    0) as zj
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<RentInContractStatPo> rentInContractStat(@Param("dto") RentInContractStatBo dto);

    /**
     * 查询租入合同
     */
    @Select("""
<script>
select
*
from house.rent_in_month
where contract_status = '履行中'
<choose>
  <when test="dto.yearMonth != null">
    and year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    and year_month = (select max(year_month) from house.rent_in_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>
</script>
""")
    List<RentInMonthPo> listRentInContract(Page<RentInMonthPo> page, @Param("dto") ListRentInContractBo dto);

    @Select("""
<script>
with
t101 as (
select
    area_no,
    city_no,
    count(use_type = '营业' or null) as yy,
    count(use_type = '办公' or null) as bg,
    count(use_type = '通信设施机房' or null) as tx,
    count(use_type in ('营业', '办公', '通信设施机房', '其他租入用途') or null) as qt,
    count(1) as zj
from
    house.rent_in_month
where
    contract_status = '履行中'
<choose>
  <when test="dto.yearMonth != null">
    and year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    and year_month = (select max(year_month) from house.rent_in_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.yy,
    0) as yy,
    coalesce(c.bg,
    0) as bg,
    coalesce(c.tx,
    0) as tx,
    coalesce(c.qt,
    0) as qt,
    coalesce(c.zj,
    0) as zj
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<RentInUseStatPo> rentInUseStat(@Param("dto") RentInUseStatBo dto);

    /**
     * 查询租入合同
     */
    @Select("""
<script>
with
t101 as (
select
    *
from
    house.rent_in_month
where
    contract_status = '履行中'
<choose>
  <when test="dto.yearMonth != null">
    and year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    and year_month = (select max(year_month) from house.rent_in_month)
  </otherwise>
</choose>
order by
    unit_price2 desc nulls last
limit 10
)
select * from t101
<where>
  <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>
</where>
</script>
""")
    List<RentInMonthPo> listRentInTop(Page<RentInMonthPo> page, @Param("dto") ListRentInTopBo dto);

    /**
     * 查询租入单价与平均单价比较
     */
    @Select("""
<script>
with
t101 as (
select
    use_type,
    round(avg(unit_price2),
    2) as unit_price_avg
from
    house.rent_in_month
where
    contract_status = '履行中'
<choose>
  <when test="dto.yearMonth != null">
    and year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    and year_month = (select max(year_month) from house.rent_in_month)
  </otherwise>
</choose>
group by
    use_type
),
t102 as (
select
    a.contract_no,
    a.contract_name,
    a.area_name,
    a.city_name,
    a.field,
    a.use_type,
    a.use_description,
    a.city_region,
    a.area_sector,
    a.gross_amount,
    a.building_area,
    a.rent_months,
    a.rent_years,
    a.unit_price2 as unit_price,
    b.unit_price_avg,
    case
        when b.unit_price_avg = 0 then null
        else round(a.unit_price2 / b.unit_price_avg,
        2)
    end as unit_price_compare
from
    house.rent_in_month a
left join t101 b on
    a.use_type = b.use_type
where
    a.contract_status = '履行中'
<choose>
  <when test="dto.yearMonth != null">
    and a.year_month = #{dto.yearMonth}
  </when>
  <otherwise>
    and a.year_month = (select max(year_month) from house.rent_in_month)
  </otherwise>
</choose>
<if test="dto.areaNo != null and dto.areaNo != ''">
  and a.area_no = #{dto.areaNo}
</if>
<if test="dto.cityNo != null and dto.cityNo != ''">
  and a.city_no = #{dto.cityNo}
</if>
)
select
    *
from
    t102
</script>
""")
    List<ListRentInComparePo> listRentInCompare(Page<ListRentInComparePo> page, @Param("dto") ListRentInCompareBo dto);
}