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

package com.example.dao.house;

import com.example.entity.house.HouseBuildingPo;
import com.example.entity.house.HouseSiteBuildingAreaIdleDiffPo;
import com.example.entity.house.HouseSitePo;
import com.example.entity.house.HouseSiteRepairInvestorStatPo;
import com.example.entity.house.HouseSiteRepairMonthPo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface HouseSiteStatDao {
    /**
     * 根据账期和局址id查询局址信息
     * @param yearMonth 账期
     * @param siteId 局址id
     */
    @Select("""
select * from house.site_month where year_month <= #{yearMonth} and site_id = #{siteId} order by year_month desc limit 1
""")
    HouseSitePo getHouseSiteBySiteIdAndYearMonth(@Param("yearMonth") Integer yearMonth, @Param("siteId") String siteId);

    /**
     * 根据账期和局址id查询所有建筑
     * @param yearMonth 账期
     * @param siteId 局址id
     */
    @Select("""
select * from house.building_month
where year_month = (select max(year_month) from house.building_month where year_month <= #{yearMonth})
and site_id = #{siteId}
""")
    List<HouseBuildingPo> allBuildingBySiteIdAndYearMonth(@Param("yearMonth") Integer yearMonth,
                                                          @Param("siteId") String siteId);

    /**
     * 根据账期和局址id查询土地总面积
     * @param yearMonth 账期
     * @param siteId 局址id
     */
    @Select("""
select
    sum(total_land_area) as land_area_sum
from
    house.land_month
where
    year_month = (select max(year_month) from house.land_month where year_month <= #{yearMonth})
    and site_id = #{siteId}
""")
    BigDecimal getLandAreaBySiteIdAndYearMonth(@Param("yearMonth") Integer yearMonth, @Param("siteId") String siteId);

    /**
     * 根据局址id查询当年累计维修费按投资主体分组
     * @param startYearMonth 开始年月
     * @param endYearMonth 结束年月
     * @param siteId 局址id
     */
    @Select("""
select
    sum(case when investor = 1 then final_cost else 0 end) as building_repair_ss,
    sum(case when investor = 3 then final_cost else 0 end) as building_repair_cx
from
    house.building_repair_month
where
    year_month >= #{startYearMonth}
    and year_month <= #{endYearMonth}
    and site_id = #{siteId}
""")
    HouseSiteRepairInvestorStatPo getSiteRepairInvestorStat(@Param("startYearMonth") Integer startYearMonth,
                                                            @Param("endYearMonth") Integer endYearMonth,
                                                            @Param("siteId") String siteId);

    /**
     * 根据局址id查询分月维修费
     * @param startYearMonth 开始年月
     * @param endYearMonth 结束年月
     * @param siteId 局址id
     */
    @Select("""
select
    month_no,
    sum(final_cost) as final_cost_sum
from
    house.building_repair_month
where
    year_month >= #{startYearMonth}
    and year_month <= #{endYearMonth}
    and site_id = #{siteId}
group by month_no
order by month_no
""")
    List<HouseSiteRepairMonthPo> getHouseSiteRepairMonth(@Param("startYearMonth") Integer startYearMonth,
                                                         @Param("endYearMonth") Integer endYearMonth,
                                                         @Param("siteId") String siteId);

    @Select("""
with
t101 as (
select
    case
        when (sum(building_area_idle) + sum(building_area_rent)) = 0 then 0
        else round(sum(building_area_idle) / (sum(building_area_idle) + sum(building_area_rent)) * 100,
        2)
    end as building_area_idle_rate_past
from
    house.building_month
where
    year_month = #{startYearMonth}
    and site_id = #{siteId}
),
t102 as (
select
    case
        when (sum(building_area_idle) + sum(building_area_rent)) = 0 then 0
        else round(sum(building_area_idle) / (sum(building_area_idle) + sum(building_area_rent)) * 100,
        2)
    end as building_area_idle_rate_now
from
    house.building_month
where
    year_month = #{endYearMonth}
    and site_id = #{siteId}
),
t103 as (
select
    *,
    building_area_idle_rate_now - building_area_idle_rate_past as building_area_idle_rate_diff
from
    t101
cross join t102
)
select
    *
from
    t103
""")
    HouseSiteBuildingAreaIdleDiffPo getSiteBuildingAreaIdleDiff(@Param("startYearMonth") Integer startYearMonth,
                                                                @Param("endYearMonth") Integer endYearMonth,
                                                                @Param("siteId") String siteId);
}