<?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.xxfc.platform.vehicle.mapper.VehicleDepartureLogMapper" >


    <select id="selectLastByVehicleId" resultType="com.xxfc.platform.vehicle.entity.VehicleDepartureLog">
        select * from vehicle_departure_log
        where vehicle_id = #{vehicleId}
        order by create_time desc
        limit 1
    </select>
    <select id="selectByVehicleId">
        select vehicle_departure_log.*,vehicle.number_plate
        from vehicle_departure_log
                 left join vehicle on vehicle_departure_log.vehicle_id = vehicle.id
        where vehicle_id = #{vehicleId}
        order by create_time desc
    </select>
    <select id="selectVoAll" resultType="com.xxfc.platform.vehicle.pojo.VehicleDepartureLogVo">
        select vehicle_departure_log.*,vehicle.number_plate,
        /* IFNULL(DATEDIFF(vehicle_departure_log.arrival_time,vehicle_departure_log.departure_time),0)*/
        CONCAT(IFNULL(floor((unix_timestamp(vehicle_departure_log.arrival_time) - unix_timestamp(vehicle_departure_log.departure_time)) / 86400),0),'天',
        IFNULL(floor(IF((unix_timestamp(vehicle_departure_log.arrival_time) - unix_timestamp(vehicle_departure_log.departure_time))>86400,(unix_timestamp(vehicle_departure_log.arrival_time)
        - unix_timestamp(vehicle_departure_log.departure_time)) MOD 86400/3600,(unix_timestamp(vehicle_departure_log.arrival_time) - unix_timestamp(vehicle_departure_log.departure_time))/3600)),0),'小时')
        as departureDay,bc.name as departureName,bc1.name as arrivalName,
        IF((IFNULL(vehicle_departure_log.mileage_end,0)-IFNULL(vehicle_departure_log.mileage_start,0))>0,IFNULL(vehicle_departure_log.mileage_end,0)-IFNULL(vehicle_departure_log.mileage_start,0),0) as mileage
        from vehicle_departure_log
        left join vehicle on vehicle_departure_log.vehicle_id = vehicle.id
        LEFT JOIN branch_company bc ON vehicle_departure_log.departure_branch_company_id = bc.id
        LEFT JOIN branch_company bc1 ON vehicle_departure_log.arrival_branch_company_id = bc1.id
        <trim prefix="where">
            1=1
            <if test="numberPlate != null and numberPlate != ''">
                and vehicle.number_plate = #{numberPlate}
            </if>
            <if test="time != null and time != ''">
                and  (vehicle_departure_log.arrival_time >=  str_to_date(#{time}, '%Y-%m-%d %H')
                or TO_DAYS(vehicle_departure_log.arrival_time)=TO_DAYS(str_to_date(#{time}, '%Y-%m-%d %H'))
                or vehicle_departure_log.arrival_time is null
                )
                and
                (
                vehicle_departure_log.departure_time &lt;= str_to_date(#{time}, '%Y-%m-%d %H')
                or TO_DAYS(vehicle_departure_log.departure_time)=TO_DAYS(str_to_date(#{time}, '%Y-%m-%d %H'))
                or vehicle_departure_log.departure_time is null
                )

            </if>
        </trim>
        order by create_time desc
    </select>
    <select id="selectVoAllNotAllData" resultType="com.xxfc.platform.vehicle.pojo.VehicleDepartureLogVo">
        select vehicle_departure_log.*,vehicle.number_plate,
        /*IFNULL(DATEDIFF(vehicle_departure_log.arrival_time,vehicle_departure_log.departure_time),0)*/
        CONCAT(IFNULL(floor((unix_timestamp(vehicle_departure_log.arrival_time) - unix_timestamp(vehicle_departure_log.departure_time)) / 86400),0),'天',
        IFNULL(floor(IF((unix_timestamp(vehicle_departure_log.arrival_time) - unix_timestamp(vehicle_departure_log.departure_time))>86400,(unix_timestamp(vehicle_departure_log.arrival_time)
        - unix_timestamp(vehicle_departure_log.departure_time)) MOD 86400/3600,(unix_timestamp(vehicle_departure_log.arrival_time) - unix_timestamp(vehicle_departure_log.departure_time))/3600)),0),'小时')
        as departureDay,bc.name as departureName,bc1.name as arrivalName,
        IF((IFNULL(vehicle_departure_log.mileage_end,0)-IFNULL(vehicle_departure_log.mileage_start,0))>0,IFNULL(vehicle_departure_log.mileage_end,0)-IFNULL(vehicle_departure_log.mileage_start,0),0) as mileage
        from vehicle_departure_log
        left join vehicle on vehicle_departure_log.vehicle_id = vehicle.id
        LEFT JOIN branch_company bc ON vehicle_departure_log.departure_branch_company_id = bc.id
        LEFT JOIN branch_company bc1 ON vehicle_departure_log.arrival_branch_company_id = bc1.id
        <trim prefix="where" suffixOverrides="and">
            <if test="numberPlate != null and numberPlate != ''">
                vehicle.number_plate = #{numberPlate} and
            </if>
            <if test="companyList != null">
                ( vehicle_departure_log.departure_branch_company_id in (
                <foreach collection="companyList" item="companyId">
                    <trim suffixOverrides=",">
                        #{companyId},
                    </trim>
                </foreach>
                )
                or vehicle_departure_log.arrival_branch_company_id in (
                <foreach collection="companyList" item="companyId">
                    <trim suffixOverrides=",">
                        #{companyId},
                    </trim>
                </foreach>
                )
                )
            </if>
            <if test="time != null and time != ''">
                and  (vehicle_departure_log.arrival_time >=  str_to_date(#{time}, '%Y-%m-%d %H')
                or TO_DAYS(vehicle_departure_log.arrival_time)=TO_DAYS(str_to_date(#{time}, '%Y-%m-%d %H'))
                or vehicle_departure_log.arrival_time is null
                )
                and
                (
                vehicle_departure_log.departure_time  &lt;= str_to_date(#{time}, '%Y-%m-%d %H')
                or TO_DAYS(vehicle_departure_log.departure_time)=TO_DAYS(str_to_date(#{time}, '%Y-%m-%d %H'))
                or vehicle_departure_log.departure_time is null
                )
            </if>
        </trim>
        order by create_time desc
    </select>
    <select id="selectMileageByVehicleId" resultType="int">
        select sum(mileage_end - mileage_start)
        from vehicle_departure_log
        where vehicle_id = #{vehicleId};
    </select>
    <select id="selectDayByVehicleId" resultType="String">
        SELECT
            CONCAT(IFNULL(floor(t.total_expire_minute / 86400),0),'天',IFNULL(floor(IF(t.total_expire_minute>86400,t.total_expire_minute MOD 86400/3600,
                                                                                      t.total_expire_minute/3600)),0),'小时')
        FROM
            (
                SELECT
                    SUM(
                            unix_timestamp(arrival_time) - unix_timestamp(departure_time)
                        ) AS total_expire_minute
                FROM
                    vehicle_departure_log
                WHERE
                    vehicle_id =#{vehicleId}
            ) t
    </select>

</mapper>
