<?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.xinxincaravan.caravan.vehicle.mapper.VehicleMapper">

    <update id="updateStatusById" parameterType="java.util.Map">
        update vehicle set status = #{status} where id in
        <foreach collection="idList" index="i" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>


    <select id="getByPage" parameterType="java.util.Map"
            resultType="com.xinxincaravan.caravan.vehicle.vo.QueryVehicleVo">
        SELECT r.* FROM (
        select  DISTINCT v.`id`,
        v.`code`,
        v.`status`,
        v.number_plate,
        v.brand,
        IFNULL(v.park_branch_company_id,v.expect_destination_branch_company_id) AS subordinate_branch,
        IFNULL(bc.name,bc1.name) AS subBranchName,
        v.use_type,
        v.remark,
        v.create_time,
        v.vin,
        v.insurance_company,
        v.insurance_no,
        v.insurance_start_date,
        v.insurance_end_date,
        v.annual_verification_date,
        v.maintenance_date,
        v.maintenance_mileage,
        v.belong_to,
        v.belong_to_name,
        v.driving_license_path,
        v.engine_num,
        v.manufacturer,
        v.receive_time,
        v.update_time,
        v.mileage_last_update as mileage
        <if test=" yearMonthAndParam !=null ">
            ,vbi.booked_date
        </if>
        from vehicle v
        <if test=" yearMonthAndParam !=null  ">
            left join
            vehicle_book_info vbi on v.`id` = vbi.vehicle
        </if>
        LEFT JOIN branch_company bc ON v.park_branch_company_id = bc.id
        LEFT JOIN branch_company bc1 ON v.expect_destination_branch_company_id = bc1.id
        where
        v.is_del=0
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="mRangeDateStart !=null">
            and v.maintenance_date &gt;= #{mRangeDateStart}
        </if>
        <if test="aVRangeDateStart !=null">
            and v.annual_verification_date &lt;= #{aVRangeDateEnd}
        </if>
        <if test="aVRangeDateEnd !=null">
            and v.annual_verification_date &gt;= #{aVRangeDateStart}
        </if>
        <if test="insuranceDateRangeStart !=null">
            and v.insurance_end_date &lt;= #{insuranceDateRangeEnd}
        </if>
        <if test="insuranceDateRangeEnd !=null">
            and v.insurance_end_date &gt;= #{insuranceDateRangeStart}
        </if>
        <if test="insuranceCompany !=null">
            and v.insurance_company = #{insuranceCompany}
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin = #{vin}
        </if>
        <if test="code !=null">
            and v.code = #{code}
        </if>
        <if test="status !=null">
            and v.status = #{status}
        </if>
        <if test="numberPlate !=null and numberPlate != ''">
            and v.number_plate  like concat('%',#{numberPlate},'%')
        </if>
        <if test="brand !=null">
            and v.brand = #{brand}
        </if>
        <if test="useType !=null">
            and v.use_type = #{useType}
        </if>
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
        <if test=" yearMonthAndParam !=null  ">
            <foreach collection="yearMonthAndParam" index="yearMonth" item="andOperation">
                and
                ( (vbi.`year_month` = #{yearMonth} or vbi.`year_month` is null) and
                ifnull(vbi.`booked_date`,0) &amp; #{andOperation.andOperationFactor} = #{andOperation.andOperationRs}
                )
            </foreach>
        </if>
        <if test="subordinateBranch !=null ">
            and ( v.park_branch_company_id = #{subordinateBranch} or v.expect_destination_branch_company_id=#{subordinateBranch} )
        </if>
         <if test=" addrProvince !=null or  addrCity !=null or zoneId !=null ">
        and (
         <trim suffixOverrides="OR">

        <if test="addrProvince !=null">
             (bc.addr_province=#{addrProvince} or bc1.addr_province=#{addrProvince}) OR
        </if>
        <if test="addrCity !=null">
              (bc.addr_city=#{addrProvince} or bc1.addr_city=#{addrProvince}) OR
        </if>
        <if test="zoneId !=null">
              (bc.zone_id=#{zoneId} or bc1.zone_id=#{zoneId})  OR
        </if>
         </trim>
        )
         </if>
        order by v.code
        ) r ORDER BY r.subBranchName
    </select>

    <select id="getByPageNotAllData" parameterType="java.util.Map"
            resultType="com.xinxincaravan.caravan.vehicle.vo.QueryVehicleVo">
        SELECT r.* FROM (
        select DISTINCT  v.`id`,
        v.`code`,
        v.`status`,
        v.number_plate,
        v.brand,
        IFNULL(v.park_branch_company_id,v.expect_destination_branch_company_id) AS subordinate_branch,
        IFNULL(bc.name,bc1.name) AS subBranchName,
        v.use_type,
        v.remark,
        v.create_time,
        v.vin,
        v.insurance_company,
        v.insurance_no,
        v.insurance_start_date,
        v.insurance_end_date,
        v.annual_verification_date,
        v.maintenance_date,
        v.maintenance_mileage,
        v.belong_to,
        v.belong_to_name,
        v.driving_license_path,
        v.engine_num,
        v.manufacturer,
        v.receive_time,
        v.update_time,
        v.mileage_last_update as mileage
        <if test=" yearMonthAndParam !=null ">
            ,vbi.booked_date
        </if>
        from vehicle v
        <if test=" yearMonthAndParam !=null  ">
            left join
            vehicle_book_info vbi on v.`id` = vbi.vehicle
        </if>
        LEFT JOIN branch_company bc ON v.park_branch_company_id = bc.id
        LEFT JOIN branch_company bc1 ON v.expect_destination_branch_company_id = bc1.id
        where
        v.is_del=0
        <if test="companyList != null">
            and ( v.park_branch_company_id in (
            <trim  suffixOverrides=",">
            <foreach collection="companyList" item="companyId">
                      #{companyId},
            </foreach>
            </trim>
            )
            or v.expect_destination_branch_company_id in (
            <trim   suffixOverrides=",">
            <foreach collection="companyList" item="companyId">
                    #{companyId},
            </foreach>
            </trim>
            )
            )
        </if>
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="mRangeDateStart !=null">
            and v.maintenance_date &gt;= #{mRangeDateStart}
        </if>
        <if test="aVRangeDateStart !=null">
            and v.annual_verification_date &lt;= #{aVRangeDateEnd}
        </if>
        <if test="aVRangeDateEnd !=null">
            and v.annual_verification_date &gt;= #{aVRangeDateStart}
        </if>
        <if test="insuranceDateRangeStart !=null">
            and v.insurance_end_date &lt;= #{insuranceDateRangeEnd}
        </if>
        <if test="insuranceDateRangeEnd !=null">
            and v.insurance_end_date &gt;= #{insuranceDateRangeStart}
        </if>
        <if test="insuranceCompany !=null">
            and v.insurance_company = #{insuranceCompany}
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin = #{vin}
        </if>
        <if test="code !=null">
            and v.code = #{code}
        </if>
        <if test="status !=null">
            and v.status = #{status}
        </if>
        <if test="numberPlate !=null and numberPlate != ''">
            and v.number_plate  like concat('%',#{numberPlate},'%')
        </if>
        <if test="brand !=null">
            and v.brand = #{brand}
        </if>
        <if test="useType !=null">
            and v.use_type = #{useType}
        </if>
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
        <if test=" yearMonthAndParam !=null  ">
            <foreach collection="yearMonthAndParam" index="yearMonth" item="andOperation">
                and
                ( (vbi.`year_month` = #{yearMonth} or vbi.`year_month` is null) and
                ifnull(vbi.`booked_date`,0) &amp; #{andOperation.andOperationFactor} = #{andOperation.andOperationRs}
                )
            </foreach>
        </if>
        <if test="subordinateBranch !=null ">
            and ( v.park_branch_company_id = #{subordinateBranch} or v.expect_destination_branch_company_id=#{subordinateBranch} )
        </if>
        <if test=" addrProvince !=null or  addrCity !=null or zoneId !=null ">
            and (
            <trim suffixOverrides="OR">
                <if test="addrProvince !=null">
                    (bc.addr_province=#{addrProvince} or bc1.addr_province=#{addrProvince}) OR
                </if>
                <if test="addrCity !=null">
                    (bc.addr_city=#{addrProvince} or bc1.addr_city=#{addrProvince}) OR
                </if>
                <if test="zoneId !=null">
                    (bc.zone_id=#{zoneId} or bc1.zone_id=#{zoneId})  OR
                </if>
            </trim>
            )
        </if>
        order by v.code
        ) r ORDER BY r.subBranchName
    </select>

    <select id="lockByCode" resultType="com.xinxincaravan.caravan.vehicle.entity.Vehicle"
            parameterType="com.xinxincaravan.caravan.vehicle.vo.AddOrUpdateVehicleVo">
        select
            v.`id`,
            v.`code`,
            v.number_plate
        from vehicle v
        where
            `code` = #{code}
        for update
    </select>
    <select id="selectByNumberPlate" resultType="com.xinxincaravan.caravan.vehicle.entity.Vehicle">
        select * from vehicle
        where number_plate = #{numberPlate}
    </select>

    <update id="updateStatusByIdAndStatus">
        update vehicle
        set status = #{status}
        where id = #{vehicleId} and status = #{lastStatus}
    </update>

    <update id="updateMileageStatusByIdAndStatus">
        update vehicle
        set status = #{status},maintenance_mileage=#{mileage}
        where id = #{vehicleId} and status = #{lastStatus}
    </update>
    <update id="upMileageByIdAndStatus">
        update vehicle
        set status = #{status},mileage_last_update=#{mileage}
        where id = #{vehicleId} and status = #{lastStatus}
    </update>

</mapper>