<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ningdatech.carapi.safe.mapper.VehicleOverspeedInfoMapper">

    <select id="countDailyToll" resultType="java.lang.Long">
        select
            count(id)
        from nd_vehicle_overspeed_info o
        where overspeed_date between #{startTime} and #{endTime}
        <if test="regionIds != null and regionIds.size>0">
            and o.region_id in
            <foreach collection="regionIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="pageGroupCompany"
            parameterType="com.ningdatech.carapi.safe.model.dto.OverspeedPageQuery"
            resultType="com.ningdatech.carapi.safe.model.po.VehicleOverspeedCompanyPO">
        select
        `rank`,overspeed_times,max_speed,company_id
        from (
        select
        if(@times = overspeed_times,@rowno,@rowno:=@rowno+1) as rank,
        @times := overspeed_times,
        overspeed_times,max_speed,company_id
        from (SELECT
        count(o.id) overspeed_times,
        max(o.max_speed) max_speed,company_id
        FROM `nd_vehicle_overspeed_info` o
        left join nd_region r on o.region_id = r.id
        left join nd_company_info c on o.company_id = c.id,
        (select @rowno:=0,@times:=0) t
        where o.company_id is not null
        <if test="ov.regionIds != null and ov.regionIds.size>0">
            and o.region_id in
            <foreach collection="ov.regionIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="ov.companyIds != null and ov.companyIds.size>0">
            and o.company_id in
            <foreach collection="ov.companyIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="ov.startTime != null and ov.startTime != ''">
            and o.overspeed_date &gt;= #{ov.startTime}
        </if>
        <if test="ov.endTime != null and ov.endTime != ''">
            and o.overspeed_date &lt;= #{ov.endTime}
        </if>

        <if test="ov.companyName != null and ov.companyName != ''">
            and o.company_name like concat('%',#{ov.companyName},'%')
        </if>
        ${ov.dataScopeSql}
        group by o.company_id) ov
        order by ov.overspeed_times desc,ov.max_speed desc) ew
        <if test="ov.rank != null">
            where rank &lt;= #{ov.rank}
        </if>
    </select>

    <select id="pageGroupVehicle"
            parameterType="com.ningdatech.carapi.safe.model.dto.OverspeedPageQuery"
            resultType="com.ningdatech.carapi.safe.model.po.VehicleOverspeedVehiclePO">
        select
        `rank`,overspeed_times,overspeed_date,max_speed,company_id,company_name,city,distinguish,region_id,car_plate,region_name
        from (
        select
        if(@times = overspeed_times,@rowno,@rowno:=@rowno+1) as rank,
        @times := overspeed_times,
        overspeed_times,overspeed_date,max_speed,company_id,company_name,city,distinguish,region_id,car_plate,region_name
        from (SELECT
        count(o.id) overspeed_times,
        concat(year(o.overspeed_date),'-',Right(100 + month(o.overspeed_date),2),'-',Right(100 +day(o.overspeed_date),2)) overspeed_date,
        max(o.max_speed) max_speed,o.company_id,c.name as company_name,o.city,o.distinguish,o.region_id,o.car_plate,o.region_name
        FROM `nd_vehicle_overspeed_info` o
        left join nd_region r on o.region_id = r.id
        left join nd_company_info c on o.company_id = c.id
        ,(select @rowno:=0,@times:=0) t
        where 1 = 1
        <!--<if test="ov.regionIds != null and ov.regionIds.size>0">-->
        <!--and o.region_id in-->
        <!--<foreach collection="ov.regionIds" item="item" open="(" close=")" separator=",">-->
        <!--#{item}-->
        <!--</foreach>-->
        <!--</if>-->
        <if test="ov.carPlateList != null and ov.carPlateList.size>0">
            and o.car_plate in
            <foreach collection="ov.carPlateList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="ov.startTime != null and ov.startTime != ''">
            and o.overspeed_date &gt;= #{ov.startTime}
        </if>
        <if test="ov.endTime != null and ov.endTime != ''">
            and o.overspeed_date &lt;= #{ov.endTime}
        </if>
        <if test="ov.carPlate != null and ov.carPlate != ''">
            and o.car_plate like concat('%',#{ov.carPlate},'%')
        </if>
        ${ov.dataScopeSql}
        group by o.company_id,o.company_name,
        concat(year(o.overspeed_date),'-',Right(100 + month(o.overspeed_date),2),'-',Right(100 +day(o.overspeed_date),2)),
        o.city,o.distinguish,o.region_id,o.car_plate,o.region_name) ov
        order by ov.overspeed_date desc, ov.overspeed_times desc,ov.max_speed desc) ew
        <if test="ov.rank != null">
            where rank &lt;= #{ov.rank}
        </if>
    </select>

    <select id="pageGroupWaysection"
            parameterType="com.ningdatech.carapi.safe.model.dto.OverspeedPageQuery"
            resultType="com.ningdatech.carapi.safe.model.po.NdVehicleOverspeedWaysectionPO">
        select
            rank,overspeed_times,overspeed_date,max_speed,company_id,company_name,city,
            distinguish,region_id,car_plate,way_section,way_section_start,way_section_end
        from (select
            if(@times = overspeed_times,@rowno,@rowno:=@rowno+1) as rank,
            @times := overspeed_times,
            overspeed_times,overspeed_date,max_speed,company_id,company_name,city,distinguish,region_id,car_plate,way_section,
            way_section_start,way_section_end
        from (SELECT
            count(o.id) overspeed_times,
        concat(year(o.overspeed_date),'-',Right(100 + month(o.overspeed_date),2),'-',Right(100 +day(o.overspeed_date),2)) overspeed_date,
            max(o.max_speed) max_speed,o.company_id,c.name as company_name,o.city,o.distinguish,o.region_id,o.car_plate,o.way_section,
            way_section_start,way_section_end
        FROM `nd_vehicle_overspeed_info` o
        left join nd_region r on o.region_id = r.id
        left join nd_company_info c on o.company_id = c.id
        ,(select @rowno:=0,@times:=0) t
        where 1 = 1
        <if test="ov.regionIds != null and ov.regionIds.size>0">
            and o.region_id in
            <foreach collection="ov.regionIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="ov.carPlateList != null and ov.carPlateList.size>0">
            and o.car_plate in
            <foreach collection="ov.carPlateList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="ov.startTime != null and ov.startTime != ''">
            and o.overspeed_date &gt;= #{ov.startTime}
        </if>
        <if test="ov.endTime != null and ov.endTime != ''">
            and o.overspeed_date &lt;= #{ov.endTime}
        </if>
        <if test="ov.companyName != null and ov.companyName != ''">
            and o.company_name like concat('%',#{ov.companyName},'%')
        </if>
        <if test="ov.carPlate != null and ov.carPlate != ''">
            and o.car_plate like concat('%',#{ov.carPlate},'%')
        </if>
        <if test="ov.waySection != null and ov.waySection != ''">
            and o.way_section like concat('%',#{ov.waySection},'%')
        </if>
        ${ov.dataScopeSql}
        group by o.company_id,o.company_name,o.car_plate,o.way_section,way_section_start,way_section_end,
        concat(year(o.overspeed_date),'-',Right(100 + month(o.overspeed_date),2),'-',Right(100 +day(o.overspeed_date),2)),
        o.city,o.distinguish,o.region_id) ov
        order by ov.overspeed_date desc, ov.overspeed_times desc,ov.max_speed desc
        ) ew
        <if test="ov.rank != null">
            where rank &lt;= #{ov.rank}
        </if>
    </select>
</mapper>