<?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.car.rpt.mapper.RptMonthSpecialOfflineMapper">
    <select id="reportPage" resultType="com.ningdatech.carapi.car.rpt.model.po.RptMonthSpecialOfflinePO">
        select nums offlineDays,
               <!--count(car_plate) as offlineVehicleNums,-->
               operator_id,
               car_plate,
               region_id,
               company_id
        from
        (select
        max(t3.nums) nums,
        b.operator_id,
        t3.car_plate,
        r.id region_id,
        c.id company_id
        from
        (select
        if(@car = car_plate and (day(ymd) - day(@date) = 1),@nums := @nums + 1, @nums := 1) nums,
        (@car := car_plate) car_plate,
        (@date := ymd) ymd
        from
        (select
        distinct t.car_plate,
        t.ymd
        from
        (select
        t1.car_plate,
        t1.year_month_day ymd,
        IF(t2.car_plate is NULL, 0, t2.state) state
        from
        (select *
        from
        (SELECT DATE_FORMAT(DATE_SUB(#{param.endTime}, INTERVAL d DAY), '%Y-%m-%d') as year_month_day
        FROM
        (select d
        from
        (SELECT @xi := @xi + 1 as d
        from
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
        (SELECT @xi := -1) xc0
        ) as m
        where d &lt;= (day(#{param.endTime}) - day(#{param.startTime}))
        ) dtc) t1,
        (SELECT
        DISTINCT car_plate
        FROM ${param.tableName} t2
        ORDER BY car_plate, year_month_day) t1
        LEFT JOIN
        (SELECT car_plate,
        concat(year, '-', right(month + 100, 2), '-', right(day + 100, 2)) ymd,
        MAX(`status`) state
        FROM ${param.tableName} h
        GROUP BY concat(year, '-', right(month + 100, 2), '-', right(day + 100, 2)),car_plate
        ORDER BY car_plate, ymd) t2
        on t1.car_plate = t2.car_plate and t1.year_month_day = t2.ymd) t
        GROUP BY t.car_plate, t.ymd
        HAVING MAX(state) = 0) tt,
        (select @nums := 0, @car := null, @date := null) t2
        ) t3
        left join nd_vehicle_base_info b on t3.car_plate = b.car_plate
        left join nd_region r on b.region_id = r.id
        left join nd_company_info c on b.company_id = c.id
        ${param.dataScopeSql}
        where b.deleted = 0 and b.audit_state = 2 and b.vehicle_type in (1, 2, 3, 4) and t3.nums > (#{param.offlineDays} - 1)
        group by t3.car_plate,b.operator_id,c.id, r.id
        <!--统计不在online表里的车辆，不在线天数就是日期的差值-->
        union all
        select
        (day (#{param.endTime}) - day (#{param.startTime}) + 1) nums,b.operator_id,b.car_plate,b.region_id, b.company_id
        from nd_vehicle_base_info b
        left join nd_region r on b.region_id = r.id
        left join nd_company_info c on b.company_id = c.id
        ${param.dataScopeSql}
        where car_plate not in
        (SELECT DISTINCT car_plate
        FROM ${param.tableName})
        and
        b.deleted = 0 and b.audit_state = 2
        and b.vehicle_type in (1, 2, 3, 4)
        ) t4
        order by offlineDays desc,region_id desc
    </select>
</mapper>
