<?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" >

    <resultMap id="searchBookRecord" type="com.xxfc.platform.vehicle.pojo.DepartureLogVo">
        <result column="book_record_id" property="bookRecordId" />
        <association property="bookRecordId" column="bookRecordId"  javaType="java.lang.Long"
                     select="com.xxfc.platform.vehicle.mapper.VehicleBookRecordMapper.selectOne"/>
    </resultMap>
    <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="selectByVehicle" parameterType="java.lang.String" resultType="com.xxfc.platform.vehicle.entity.VehicleDepartureLog">
        select vehicle_departure_log.*
        from vehicle_departure_log
        where vehicle_id = #{vehicleId}
        order by create_time desc
    </select>

    <select id="selectByBookRecordId" parameterType="java.lang.Long" resultType="com.xxfc.platform.vehicle.pojo.VehicleDepartureLogVo">
        select v.*, bc.name as departureName,bc1.name as arrivalName
        from vehicle_departure_log v
        LEFT JOIN branch_company bc ON v.departure_branch_company_id = bc.id
        LEFT JOIN branch_company bc1 ON v.arrival_branch_company_id = bc1.id
        where v.book_record_id = #{id}
        order by create_time desc
    </select>
    <select id="selectAllDepartureLog" resultType="java.lang.Integer" parameterType="Map">
        SELECT
            count(*)
        FROM
        vehicle_departure_log v1
        LEFT JOIN vehicle_book_record v2 on v1.book_record_id = v2.id
        <where>
            <if test="startTime != null and status == 1">
                and (v1.departure_time between #{startTime} and #{endTime})
            </if>
            <if test="startTime != null and status == 2">
                and (v1.arrival_time between #{startTime} and #{endTime})
            </if>
            <!--正常出车-->
            <if test="startTime != null and status == 1 and type == 1">
                and (v2.book_start_date between #{startTime} and #{endTime})
            </if>
            <!--提前出车-->
            <if test="endTime != null and status == 1 and type == 2">
                and v2.book_start_date &gt;= #{endTime}
            </if>
            <!--延期出车-->
            <if test="startTime != null and status == 1 and type == 3">
                and v2.book_start_date &lt;= #{startTime}
            </if>

            <!--正常还车-->
            <if test="startTime != null and status == 2 and type == 1">
                and (v2.book_start_date between #{startTime} and #{endTime})
            </if>
            <!--提前还车-->
            <if test="endTime != null and status == 2 and type == 2">
                and v2.book_start_date &gt;= #{endTime}
            </if>
            <!--延期还车-->
            <if test="startTime != null and status == 2 and type == 3">
                and v2.book_start_date &lt;= #{startTime}
            </if>

            <!--统计客户用车-->
            <if test="bookUser != null">
                and v2.book_user = #{bookUser}
            </if>
            <if test="bookUser == null">
                and v2.book_user != -2
            </if>
        </where>

    </select>
    <select id="selectVoAll" resultMap="searchBookRecord">
        select vehicle_departure_log.*,vehicle.number_plate,bc2.actual_end_date,bc2.actual_start_date,bc2.book_start_date,bc2.book_end_date,bc2.remark,
         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,bc3.name as expectArrivalName,bc4.name as subordinateName,
        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
        LEFT JOIN branch_company bc3 ON vehicle_departure_log.expect_arrival_branch_company_id = bc3.id
        LEFT JOIN branch_company bc4 ON vehicle.subordinate_branch = bc4.id
        LEFT JOIN vehicle_book_record bc2 ON vehicle_departure_log.book_record_id = bc2.id
        <trim prefix="where">
            1=1
            <if test="numberPlate != null and numberPlate != ''">
                and vehicle.number_plate = #{numberPlate}
            </if>
            <if test="startTime != null and startTime != ''">
                and
                vehicle_departure_log.departure_time &gt;= str_to_date(#{startTime}, '%Y-%m-%d %H')
            </if>
            <if test="endTime != null and endTime != ''">
                and vehicle_departure_log.arrival_time &gt;= str_to_date(#{endTime}, '%Y-%m-%d %H')
            </if>
            <if test="code!=null">
                and vehicle.code=#{code}
            </if>
            <if test="zoneId!=null">
                and bc.zone_id=#{zoneId}
            </if>
            <if test="departureId!=null">
                and vehicle_departure_log.departure_branch_company_id=#{departureId}
            </if>
            <if test="use!=null">
                and bc2.book_type=#{use}
            </if>
            <if test="arrivalBranchCompanyId!=null">
                and vehicle_departure_log.arrival_branch_company_id=#{arrivalBranchCompanyId}
            </if>
        </trim>
        order by create_time desc
    </select>
    <select id="selectVoAllNotAllData" resultMap="searchBookRecord">
        select vehicle_departure_log.*,vehicle.number_plate,bc2.actual_end_date,bc2.actual_start_date,bc2.book_start_date,bc2.book_end_date,bc2.remark,
        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,bc2.name as expectArrivalName,bc3.name as subordinateName,
        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
        LEFT JOIN branch_company bc2 ON vehicle_departure_log.expect_arrival_branch_company_id = bc2.id
        LEFT JOIN branch_company bc3 ON vehicle.subordinate_branch = bc3.id
        LEFT JOIN vehicle_book_record bc2 ON vehicle_departure_log.book_record_id = bc2.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="startTime != null and startTime != ''">
                and
                vehicle_departure_log.departure_time &lt;= str_to_date(#{startTime}, '%Y-%m-%d %H')
                or TO_DAYS(vehicle_departure_log.departure_time)=TO_DAYS(str_to_date(#{startTime}, '%Y-%m-%d %H'))
                or vehicle_departure_log.departure_time is null

            </if>
            <if test="endTime != null and endTime != ''">
                and vehicle_departure_log.arrival_time >= str_to_date(#{endTime}, '%Y-%m-%d %H')
                or TO_DAYS(vehicle_departure_log.arrival_time)=TO_DAYS(str_to_date(#{endTime}, '%Y-%m-%d %H'))
                or vehicle_departure_log.arrival_time is null
            </if>
            <if test="code!=null">
                and vehicle.code=#{code}
            </if>
            <if test="zoneId!=null">
                and bc.zone_id=#{zoneId}
            </if>
            <if test="departureId!=null">
                and vehicle_departure_log.departure_branch_company_id=#{departureId}
            </if>
            <if test="use!=null">
                and bc2.book_type=#{use}
            </if>
            <if test="arrivalBranchCompanyId!=null">
                and vehicle_departure_log.arrival_branch_company_id=#{arrivalBranchCompanyId}
            </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>
