finance-api/src/main/java/com/nokia/financeapi/dao/car/CarResourceMapMapper.java

package com.nokia.financeapi.dao.car;

import com.nokia.financeapi.pojo.dto.GetCarMapStatDto;
import com.nokia.financeapi.pojo.dto.GetCarNoticeDto;
import com.nokia.financeapi.pojo.dto.GetCarTypeStatDto;
import com.nokia.financeapi.pojo.dto.GetDriveStatDto;
import com.nokia.financeapi.pojo.vo.GetCarMapStatVo;
import com.nokia.financeapi.pojo.vo.GetCarNoticeVo;
import com.nokia.financeapi.pojo.vo.GetCarTypeStatVo;
import com.nokia.financeapi.pojo.vo.GetDriveStatVo;
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 CarResourceMapMapper {
    /**
     * 获取公告
     */
    @Select("""
select * from car.notices
where city = #{dto.city}
order by create_time desc
""")
    List<GetCarNoticeVo> getNotice(@Param("dto") GetCarNoticeDto dto);

    /**
     * 获取全省或某个地市的车辆类型统计
     */
    @Select("""
<script>
select
    count(che_liang_lei_xing = '皮卡车' or null) as pkc,
    count(che_liang_lei_xing = '微型面包车' or null) as wxmbc,
    count(che_liang_lei_xing = '越野车' or null) as yyc,
    count(che_liang_lei_xing = '轿车' or null) as jc,
    count(che_liang_lei_xing not in ('皮卡车', '微型面包车', '越野车', '轿车') or null) as qtc
from
    car.car_base_data_month
where
    nian_yue = (
    select
        max(nian_yue)
    from
        car.car_base_data_month)
    <if test="dto.city != null and dto.city != ''">
      and city = #{dto.city}
    </if>
</script>
""")
    GetCarTypeStatVo getCarTypeStat(@Param("dto") GetCarTypeStatDto dto);

    /**
     * 获取各个地市的车辆数量和低效数量统计
     */
    @Select("""
with
t101 as (
select
    city,
    count(1) as total
from
    car.car_base_data_month
where
    nian_yue = (
    select
        max(nian_yue)
    from
        car.car_base_data_month)
    and city is not null
    and city != ''
group by
    city
),
t102 as (
select
    city,
    count(1) as inefficiency_count
from
    car.car_di_xiao_month
where
    nian_yue = (
    select
        max(nian_yue)
    from
        car.car_di_xiao_month)
    and city is not null
    and city != ''
group by
    city
),
t103 as (
select
t101.*,
t102.inefficiency_count
from t101 left join t102 on t101.city = t102.city
),
t104 as (
select
city as area_name,
total,
coalesce(inefficiency_count, 0) as inefficiency_count
from t103
)
select * from t104 order by area_name
""")
    List<GetCarMapStatVo> getCityCarMapStat();

    /**
     * 获取某个地市各区县的车辆数量和低效数量统计
     */
    @Select("""
with
t101 as (
select
    district,
    count(1) as total
from
    car.car_base_data_month
where
    nian_yue = (
    select
        max(nian_yue)
    from
        car.car_base_data_month)
    and district is not null
    and district != ''
    and city = #{dto.city}
group by
    district
),
t102 as (
select
    district,
    count(1) as inefficiency_count
from
    car.car_di_xiao_month
where
    nian_yue = (
    select
        max(nian_yue)
    from
        car.car_di_xiao_month)
    and district is not null
    and district != ''
    and city = #{dto.city}
group by
    district
),
t103 as (
select
t101.*,
t102.inefficiency_count
from t101 left join t102 on t101.district = t102.district
),
t104 as (
select
district as area_name,
total,
coalesce(inefficiency_count, 0) as inefficiency_count
from t103
)
select * from t104 order by area_name
""")
    List<GetCarMapStatVo> getDistrictCarMapStat(@Param("dto") GetCarMapStatDto dto);

    /**
     * 获取各个地市的行驶统计
     */
    @Select("""
with
t101 as (
select
    city,
    avg(zong_li_cheng) as mileage,
    avg(chu_qin_lv) as attendanceRate
from
    car.car_li_cheng_month
where
    year_no = (
    select
        max(year_no)
    from
        car.car_li_cheng_month)
    and city is not null
    and city != ''
group by
    city
),
t102 as (
select
    t101.*,
    car.car_second_unit_sort.sort
from
    t101
left join car.car_second_unit_sort on
    t101.city = car.car_second_unit_sort.second_unit
),
t103 as (
select
    city as area_name,
    round(mileage,
    2) as mileage,
    round(attendanceRate * 100,
    2) as attendanceRate,
    sort
from
    t102
order by
    sort desc
)
select
    *
from
    t103
""")
    List<GetDriveStatVo> getCityDriveStat();

    /**
     * 获取某个地市各个区县的行驶统计
     */
    @Select("""
with
t101 as (
select
    district,
    avg(zong_li_cheng) as mileage,
    avg(chu_qin_lv) as attendanceRate
from
    car.car_li_cheng_month
where
    year_no = (
    select
        max(year_no)
    from
        car.car_li_cheng_month)
    and district is not null
    and district != ''
    and city = #{dto.city}
group by
    district
),
t102 as (
select
    district as area_name,
    round(mileage,
    2) as mileage,
    round(attendanceRate * 100,
    2) as attendanceRate
from
    t101
order by
    district
)
select
    *
from
    t102
""")
    List<GetDriveStatVo> getDistrictDriveStat(GetDriveStatDto dto);
}