<?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.NdVehicleAccidentAnalysisMapper">
    <select id="pageAnalysis" resultType="com.ningdatech.carapi.safe.entity.NdVehicleAccidentAnalysis">
        select
--      实际应该以事故驾驶员 -> 事故时间 -> 事故地点为顺序分组去重
--      现在不能确定事故的驾驶员是谁,先按照事故地点去重
        ew.accident_address,
        ew.accident_time,
        ew.accident_driver,
        ew.region_id,
        ew.region_name,
        ew.company_id,
        ew.longitude_latitude
--             ,ew.car_plate
        from(
                select
                    r.id region_id,
                    r.name region_name,
                    c.id company_id,
                    a.accident_time,
                    a.accident_driver,
                    if(a.longitude is not null and a.latitude is not null,concat(a.longitude,',',a.latitude ),null) longitude_latitude,
                    a.accident_address,
                    a.car_plate
                from nd_vehicle_accident a
                         left join nd_company_info c on a.company_id = c.id
                         left join nd_region r on a.region_id = r.id
                where 1 = 1
                <if test="param.startTime != null and param.startTime != ''">
                    and a.accident_time &gt;= #{param.startTime}
                </if>
                <if test="param.endTime != null and param.endTime != ''">
                    and a.accident_time &lt;= #{param.endTime}
                </if>
                <if test="param.carPlate != null and param.carPlate != ''">
                    and a.car_plate = #{param.carPlate}
                </if>
                <if test="param.regionIds != null and param.regionIds.size > 0">
                    and r.id in
                    <foreach collection="param.regionIds" item="item" open="(" close=")" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="param.carPlateList != null and param.carPlateList.size > 0">
                    and a.car_plate in
                    <foreach collection="param.carPlateList" item="item" open="(" close=")" separator=",">
                        #{item}
                    </foreach>
                </if>
                ${param.dataScopeSql}
                union all
                select
                    r.id region_id,
                    r.name region_name,
                    c.id company_id,
                    d.accident_time,
                    d.accident_driver,
                    if(d.longitude is not null and d.latitude is not null,concat(d.longitude,',',d.latitude ),null) longitude_latitude,
                    d.accident_address,
                    d.car_plate
                from nd_vehicle_accident_dead d
                         left join nd_company_info c on d.company_id = c.id
                         left join nd_region r on d.region_id = r.id
                where 1 = 1
                <if test="param.startTime != null and param.startTime != ''">
                    and d.accident_time &gt;= #{param.startTime}
                </if>
                <if test="param.endTime != null and param.endTime != ''">
                    and d.accident_time &lt;= #{param.endTime}
                </if>
                <if test="param.carPlate != null and param.carPlate != ''">
                    and d.car_plate = #{param.carPlate}
                </if>
                <if test="param.regionIds != null and param.regionIds.size > 0">
                    and r.id in
                    <foreach collection="param.regionIds" item="item" open="(" close=")" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="param.carPlateList != null and param.carPlateList.size > 0">
                    and d.car_plate in
                    <foreach collection="param.carPlateList" item="item" open="(" close=")" separator=",">
                        #{item}
                    </foreach>
                </if>
                ${param.dataScopeSql}
        ) ew
        group by
        ew.accident_address,
        ew.accident_time,
        ew.accident_driver,
        ew.region_id,
        ew.region_name,
        ew.company_id,
        ew.longitude_latitude
--         ,ew.car_plate
        order by ew.accident_time desc
    </select>
</mapper>