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

    <resultMap id="searchUsableModelMap" type="com.xxfc.platform.vehicle.pojo.UsableVehicleModelVO">
        <association property="company" column="company_id"
                     select="com.xxfc.platform.vehicle.mapper.BranchCompanyMapper.selectByPrimaryKey"/>
        <association property="vehicleModel" column="model_id"
                     select="com.xxfc.platform.vehicle.mapper.VehicleModelMapper.selectByPrimaryKey"/>
    </resultMap>

    <resultMap id="searchModel" type="com.xxfc.platform.vehicle.pojo.VehicleAndModelInfoVo">
        <result column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <association property="vehicleModel" column="model_id"
                     select="com.xxfc.platform.vehicle.mapper.VehicleModelMapper.selectByPrimaryKey"/>
        <association property="vehicleBookRecord" column="id"
                     select="com.xxfc.platform.vehicle.mapper.VehicleBookRecordMapper.selectByVehicleId"/>
    </resultMap>

    <resultMap id="vehicleModel" type="com.xxfc.platform.vehicle.pojo.VehicleAndModelInfoVo">
        <result column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <association property="vehicleModel" column="model_id"
                     select="com.xxfc.platform.vehicle.mapper.VehicleModelMapper.selectByPrimaryKey"/>
    </resultMap>

    <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.xxfc.platform.vehicle.pojo.ResultVehicleVo">
        SELECT r.* FROM (
        select DISTINCT v.`id`,
        v.`code`,
        (CASE WHEN #{status} is not null THEN #{status}
        ELSE v.status END) status ,
        v.number_plate,
        v.brand,
        v.park_branch_company_id,
        bc.name as parkBranchCompanyName,
        v.expect_destination_branch_company_id,
        bc1.name as expectDestinationBranchCompanyName,
        v.subordinate_branch,
        bc2.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.model_id,
        v.member_use,
        v.vehicle_status,
        v.vehicle_body_status,
        v.vehicle_inner_status,
        v.vehicle_register_date,
        v.mileage_last_update as mileage,
        c.val as useTypeName,
        ci.name as companyName,
        vm.name as vehicleType
        <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
        -- 停车分公司
        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
        -- 所属分公司
        LEFT JOIN branch_company bc2 ON v.subordinate_branch = bc2.id
        LEFT JOIN vehicle_book_record v2 on v2.id = (select id from vehicle_book_record where vehicle_id = v.id order by update_time DESC LIMIT 1)
        LEFT JOIN vehicle_departure_log v3 on v2.id = v3.book_record_id
        LEFT JOIN vehicle_model vm ON  v.model_id = vm.id
        LEFT JOIN company_info ci on ci.id = bc2.company_id
        LEFT JOIN (select * from constant where  type = 2) c ON  v.use_type = c.code
        where
        v.is_del=0
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="modelId != null and modelId != ''">
            and v.model_id = #{modelId}
        </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="belongToName !=null and belongToName != ''">
            and v.belong_to_name  like concat('%',#{belongToName},'%')
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin like CONCAT('%',#{vin},'%')
        </if>
        <!--        <if test="subordinateBranch !=null">-->
        <!--            and v.subordinate_branch = #{subordinateBranch}-->
        <!--        </if>-->
        <if test="code !=null">
            and v.code = #{code}
        </if>
        <if test="status !=null and status != 6 and status != 7">
            and v.status = #{status}
        </if>
        <if test="status !=null and status == 6">
            and  v3.id is not NULL and v2.book_type = 3 and v3.state = 0
        </if>
        <if test="status !=null and status == 7">
            and  v3.id is not NULL and v2.book_type = 6 and v3.state = 0
        </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="zoneId !=null">
            and bc.zone_id=#{zoneId}
        </if>
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
        <if test=" yearMonthAndParam !=null  ">
            <!-- 包含     vbi：车辆预约信息  -->
            <!-- vbi年月相等 或者 年月为空-->
            <!-- booked_date 与换算 判断是否有重叠 日期-->
            <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 ( bc2.id = #{subordinateBranch})
        </if>
        <if test="parkBranchCompanyId !=null ">
            and ( bc.id = #{parkBranchCompanyId})
        </if>
        <if test="companyId !=null ">
            and ( ci.id = #{companyId})
        </if>
        <if test="dataCorporationIds != null and dataCorporationIds.size > 0">
            and ci.id in (
            <foreach collection="dataCorporationIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            )
        </if>
        <if test="dataCompanyIds != null and dataCompanyIds.size > 0">
            and (bc.id in (
            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            ) or
            bc2.id in (
            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            ))
        </if>
        <if test=" addrProvince !=null or addrCity !=null or zoneId !=null ">
            <if test="addrProvince !=null">
                and bc.addr_province=#{addrProvince}
            </if>
            <if test="addrCity !=null">
                and  bc.addr_city=#{addrCity}
            </if>
            <if test="zoneId !=null">
                and  bc.zone_id=#{zoneId}
            </if>
        </if>
        order by v.code
        ) r ORDER BY r.parkBranchCompanyName
    </select>


    <select id="getByPageNotAllData" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.pojo.ResultVehicleVo">
        SELECT r.* FROM (
        select DISTINCT v.`id`,
        v.`code`,
        (CASE WHEN #{status} is not null THEN #{status}
        ELSE v.status END) status ,
        v.number_plate,
        v.brand,
        v.park_branch_company_id,
        bc.name as parkBranchCompanyName,
        v.expect_destination_branch_company_id,
        bc1.name as expectDestinationBranchCompanyName,
        v.subordinate_branch,
        bc2.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.strong_insurance_company,
        v.strong_insurance_no,
        v.strong_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.model_id,
        v.member_use,
        v.vehicle_status,
        v.vehicle_body_status,
        v.vehicle_inner_status,
        v.vehicle_register_date,
        v.mileage_last_update as mileage,
        c.val as useTypeName,
        ci.name as companyName,
        vm.name as vehicleType
        <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
        -- 所属分公司
        LEFT JOIN branch_company bc2 ON v.subordinate_branch = bc2.id
        LEFT JOIN vehicle_book_record v2 on v2.id = (select id from vehicle_book_record where vehicle_id = v.id order by update_time DESC LIMIT 1)
        LEFT JOIN vehicle_departure_log v3 on v2.id = v3.book_record_id
        LEFT JOIN vehicle_model vm ON  v.model_id = vm.id
        LEFT JOIN company_info ci on ci.id = bc2.company_id
        LEFT JOIN (select * from constant where  type = 2) c ON  v.use_type = c.code
        where
        v.is_del=0
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="modelId != null and modelId != ''">
            and v.model_id = #{modelId}
        </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="belongToName !=null">
            and v.belong_to_name = #{belongToName}
        </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="zoneId !=null">
            and bc.zone_id=#{zoneId}
        </if>
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
        <if test=" yearMonthAndParam !=null  ">
            <!-- 包含     vbi：车辆预约信息  -->
            <!-- vbi年月相等 或者 年月为空-->
            <!-- booked_date 与换算 判断是否有重叠 日期-->
            <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 ( bc2.id = #{subordinateBranch})
        </if>
        <if test="parkBranchCompanyId !=null ">
            and ( bc.id = #{parkBranchCompanyId})
        </if>
        <if test="companyId !=null ">
            and ( ci.id = #{companyId})
        </if>
        <if test="dataCorporationIds != null and dataCorporationIds.size > 0">
            and ci.id in (
            <foreach collection="dataCorporationIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            )
        </if>
        <if test="dataCompanyIds != null and dataCompanyIds.size > 0">
            and (bc.id in (
            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            ) or
            bc2.id in (
            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            ))
        </if>

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

    <select id="getVehicleExcelVo" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.pojo.VehicleExcelVo">
        SELECT r.* FROM (
        select
        v.`code`,
        v.number_plate,
        v.vehicle_register_date,
        bc.name as parkBranchCompanyName,
        c.val as useType,
        ci.name as companyName,
        vm.name as vehicleType
        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
        -- 停车分公司
        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
        -- 所属分公司
        LEFT JOIN branch_company bc2 ON v.subordinate_branch = bc2.id
        LEFT JOIN vehicle_book_record v2 on v2.vehicle_id = v.id
        LEFT JOIN vehicle_departure_log v3 on v2.id = v3.book_record_id
        LEFT JOIN vehicle_model vm ON  v.model_id = vm.id
        LEFT JOIN company_info ci on ci.id = bc2.company_id
        LEFT JOIN (select * from constant where  type = 2) c ON  v.use_type = c.code
        where
        v.is_del=0
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="modelId != null and modelId != ''">
            and v.model_id = #{modelId}
        </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="belongToName !=null">
            and v.belong_to_name = #{belongToName}
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin = #{vin}
        </if>
        <!--        <if test="subordinateBranch !=null">-->
        <!--            and v.subordinate_branch = #{subordinateBranch}-->
        <!--        </if>-->
        <if test="code !=null">
            and v.code = #{code}
        </if>
        <if test="status !=null and status != 6 and status != 7">
            and v.status = #{status}
        </if>
        <if test="status !=null and status == 6">
            and  v3.id is not NULL and v2.book_type = 3 and v3.state = 0
        </if>
        <if test="status !=null and status == 7">
            and  v3.id is not NULL and v2.book_type = 6 and v3.state = 0
        </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="zoneId !=null">
            and bc.zone_id=#{zoneId}
        </if>
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
        <if test=" yearMonthAndParam !=null  ">
            <!-- 包含     vbi：车辆预约信息  -->
            <!-- vbi年月相等 或者 年月为空-->
            <!-- booked_date 与换算 判断是否有重叠 日期-->
            <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})
        </if>
        <if test="parkBranchCompanyId !=null ">
            and ( bc.id = #{parkBranchCompanyId})
        </if>
        <if test="companyId !=null ">
            and ( ci.id = #{companyId})
        </if>
        <if test="dataCorporationIds != null and dataCorporationIds.size > 0">
            and ci.id in (
            <foreach collection="dataCorporationIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            )
        </if>
        <if test="dataCompanyIds != null and dataCompanyIds.size > 0">
            and (bc.id in (
            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            ) or
            bc2.id in (
            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            ))
        </if>
        <if test=" addrProvince !=null or addrCity !=null or zoneId !=null ">
            <if test="addrProvince !=null">
                and bc.addr_province=#{addrProvince}
            </if>
            <if test="addrCity !=null">
                and  bc.addr_city=#{addrCity}
            </if>
            <if test="zoneId !=null">
                and  bc.zone_id=#{zoneId}
            </if>
        </if>
        order by v.code
        ) r ORDER BY r.parkBranchCompanyName
    </select>
    <select id="getVehicleExcelVoNotAllData" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.pojo.VehicleExcelVo">
        SELECT r.* FROM (
        select
        v.`code`,
        v.number_plate,
        v.vehicle_register_date,
        bc.name as parkBranchCompanyName,
        c.val as useType,
        vm.name as vehicleType
        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
        -- 所属分公司
        LEFT JOIN branch_company bc2 ON v.subordinate_branch = bc2.id
        LEFT JOIN vehicle_model vm ON  v.model_id = vm.id
        LEFT JOIN (select * from constant where  type = 2) c ON  v.use_type = c.code
        where
        v.is_del=0
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="modelId != null and modelId != ''">
            and v.model_id = #{modelId}
        </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="belongToName !=null and belongToName != ''">
            and v.belong_to_name like concat('%',#{belongToName},'%')
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin like CONCAT('%',#{vin},'%')
        </if>
        <!--        <if test="subordinateBranch !=null">-->
        <!--            and v.subordinate_branch = #{subordinateBranch}-->
        <!--        </if>-->
        <if test="code !=null">
            and v.code = #{code}
        </if>
        <if test="status !=null and status != 6 and status != 7">
            and v.status = #{status}
        </if>
        <if test="status !=null and status == 6">
            and  v3.id is not NULL and v2.book_type = 3 and v3.state = 0
        </if>
        <if test="status !=null and status == 7">
            and  v3.id is not NULL and v2.book_type = 6 and v3.state = 0
        </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="zoneId !=null">
            and bc.zone_id=#{zoneId}
        </if>
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
        <if test=" yearMonthAndParam !=null  ">
            <!-- 包含     vbi：车辆预约信息  -->
            <!-- vbi年月相等 或者 年月为空-->
            <!-- booked_date 与换算 判断是否有重叠 日期-->
            <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="dataCorporationIds != null and dataCorporationIds.size > 0">
            and ci.id in (
            <foreach collection="dataCorporationIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            )
        </if>
        <if test="dataCompanyIds != null and dataCompanyIds.size > 0">
            and (bc.id in (
            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            ) or
            bc2.id in (
            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            ))
        </if>
        <if test=" addrProvince !=null or  addrCity !=null or zoneId !=null ">
            <if test="addrProvince !=null">
                and bc.addr_province=#{addrProvince}
            </if>
            <if test="addrCity !=null">
                and  bc.addr_city=#{addrCity}
            </if>
            <if test="zoneId !=null">
                and  bc.zone_id=#{zoneId}
            </if>
        </if>
        order by v.code
        ) r ORDER BY r.parkBranchCompanyName
    </select>

    <!--导出分公司停靠所有车辆-->
    <select id="getAllVehicleInfo" resultType="com.xxfc.platform.vehicle.pojo.BranchCompanyVehicleCountVo">
        SELECT
        b.id AS companyId,
        DATE_FORMAT(
        NOW(),
        '%Y'
        ) AS countYear,
        DATE_FORMAT(NOW(), '%m') AS countMonth,
        DATE_FORMAT(NOW(), '%u') AS countWeek,
        DATE_FORMAT(NOW(), '%Y-%m-%d') AS countDate,
        count(*) AS vehicleNum
        FROM
        vehicle v
        LEFT JOIN branch_company b ON b.id = v.park_branch_company_id
        where v.is_del = 0
        GROUP BY
        b.id
    </select>

    <select id="lockByCode" resultType="com.xxfc.platform.vehicle.entity.Vehicle"
            parameterType="com.xxfc.platform.vehicle.pojo.AddOrUpdateVehicleVo">
        select
            v.`id`,
            v.`code`,
            v.number_plate
        from vehicle v
        where
            number_plate=#{numberPlate}
            and is_del=0
        for update


    </select>
    <select id="selectByNumberPlate" resultType="com.xxfc.platform.vehicle.entity.Vehicle">
        select * from vehicle
        where number_plate = #{numberPlate} and is_del=0
    </select>

    <select id="selectById" resultMap="searchModel">
        select * from vehicle
        where id = #{id}
        order By create_time DESC
    </select>

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

    <update id="updateStatus">
        update vehicle
        set status = #{status}
        where id = #{vehicleId} and status != 3
    </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>


    <select id="getAllVehicle" parameterType="java.util.Map" resultMap="searchModel">
        select v1.*, bc2.name subordinateBranchName, bc3.name parkCompanyName, bc4.name destinationBranchCompanyName
        from vehicle v1
        -- 所属分公司
        LEFT JOIN branch_company bc2 ON v1.subordinate_branch = bc2.id
        LEFT JOIN branch_company bc3 ON v1.park_branch_company_id = bc3.id
        LEFT JOIN branch_company bc4 ON v1.expect_destination_branch_company_id = bc4.id
        <where>
            <if test="startTime != null">
                and v1.create_time between #{startTime} and #{endTime}
            </if>
            <if test="numberPlate != null">
                and v1.number_plate = #{numberPlate}
            </if>
            <if test="status != null">
                and v1.status = #{status}
            </if>
            <if test="companyIds != null and companyIds.size > 0">
                and v1.park_branch_company_id in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="parkBranchCompanyId != null and parkBranchCompanyId > 0">
              and (bc2.id = #{parkBranchCompanyId} or bc3.id = #{parkBranchCompanyId}  or  bc4.id = #{parkBranchCompanyId})
             </if>
             <if test="branchId != null and branchId > 0">
              and (bc2.company_id = #{branchId} or bc3.company_id = #{branchId} or bc4.company_id = #{branchId})
             </if>
            <if test="zoneId !=null">
                and bc2.zone_id = #{zoneId}
            </if>
            <if test="useType != null">
                and v1.use_type = #{useType}
            </if>
            <if test="modelId != null and modelId != ''">
                and v1.model_id = #{modelId}
            </if>
            <if test="code !=null">
                and v1.code=#{code}
            </if>
            <if test="dataCompanyIds != null and dataCompanyIds.size() > 0">
                and ( bc2.id in
                <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                    or bc3.id in
               <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                    or bc4.id in
               <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
              )
            </if>

            <if test="dataCorporationIds != null and dataCorporationIds.size() > 0">
                and ( bc2.company_id  in
                <foreach collection="dataCorporationIds"  item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                    or bc3.company_id  in
                <foreach collection="dataCorporationIds"  item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                    or bc4.company_id  in
                <foreach collection="dataCorporationIds"  item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                )
            </if>
            and v1.is_del != 1
        </where>
    </select>

    <select id="getVehicle" resultMap="vehicleModel" parameterType="java.util.Map">
        select v1.*,bc3.name parkCompanyName  from vehicle v1
        LEFT JOIN branch_company bc3 ON v1.park_branch_company_id = bc3.id
       <where>
           <if test="numberPlate != null and numberPlate != ''">
               and v1.number_plate = #{numberPlate}
           </if>
           <if test="code != null and code != ''">
               and v1.code = #{code}
           </if>
           <if test="keywords != null and keywords != ''">
               and (v1.number_plate like concat('%', #{keywords}, '%') or v1.code like concat('%', #{keywords}, '%'))
           </if>
           <if test="companyIds != null and companyIds.size > 0">
               and v1.park_branch_company_id in
               <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                   #{id}
               </foreach>
           </if>
           <if test="zoneIds != null and zoneIds.size > 0">
               and bc3.zone_id in
               <foreach collection="zoneIds" item="id" open="(" separator="," close=")">
                   #{id}
               </foreach>
           </if>
           <if test="modelId != null and modelId != ''">
               and v1.model_id = #{modelId}
           </if>
           and v1.is_del != 1
       </where>
        ORDER BY parkCompanyName
    </select>

    <select id="getAllVehicleByParam" resultType="com.xxfc.platform.vehicle.entity.Vehicle">
        select * from vehicle v
        where v.is_del != 1
        <if test="modelId != null and modelId != ''">
            and v.model_id = #{modelId}
        </if>
        <if test="companyIds != null and companyIds.size > 0">
            and v.subordinate_branch in
            <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </select>

    <select id="countVehicleByParam" parameterType="com.xxfc.platform.vehicle.pojo.dto.VehiclePlanDto"
            resultType="com.xxfc.platform.vehicle.pojo.VehicleCountVo">
        SELECT count(*) total ,
        CASE
        WHEN v1.status = 1 THEN
        '1'
        WHEN v1.status = 2 THEN
        '2'
        WHEN v1.status = 3 THEN
        '3'
        WHEN v1.status = 4 THEN
        '4'
        WHEN v1.status = 5 THEN
        '5'
        ELSE
        '6'
        END travel_status,
        CASE
        WHEN v1.status = 1 THEN
        '正常运行'
        WHEN v1.status = 2 THEN
        '维修'
        WHEN v1.status = 3 THEN
        '报废'
        WHEN v1.status = 4 THEN
        '出车'
        WHEN v1.status = 5 THEN
        '保养'
        ELSE
        '不可用'
        END detail
        from vehicle v1
        -- 所属分公司
        LEFT JOIN branch_company bc1 ON v1.subordinate_branch = bc1.id
        LEFT JOIN branch_company bc3 ON v1.park_branch_company_id = bc3.id
        LEFT JOIN branch_company bc4 ON v1.expect_destination_branch_company_id = bc4.id
        <where>
            <if test="startTime != null">
                and v1.create_time between #{startTime} and #{endTime}
            </if>
            <if test="numberPlate != null and numberPlate != ''">
                and v1.number_plate = #{numberPlate}
            </if>
            <if test="status != null">
                and v1.status = #{status}
            </if>
            <if test="code !=null">
                and v1.code=#{code}
            </if>
            <if test="companyIds != null and companyIds.size > 0">
                and v1.park_branch_company_id in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
             <if test="parkBranchCompanyId != null and parkBranchCompanyId > 0">
                and (bc1.id = #{parkBranchCompanyId} or bc3.id = #{parkBranchCompanyId}  or bc4.id = #{parkBranchCompanyId})
            </if>
            <if test="branchId != null and branchId > 0">
                and (bc1.company_id = #{branchId} or bc3.company_id = #{branchId} or bc4.company_id = #{branchId})
            </if>
            <if test="dataCompanyIds != null and dataCompanyIds.size() > 0">
                and ( bc1.id in
                <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                    or bc3.id in
                <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                     or bc4.id in
                <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                )
            </if>

            <if test="dataCorporationIds != null and dataCorporationIds.size() > 0">
                and ( bc1.company_id in
                <foreach collection="dataCorporationIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                    or bc3.company_id in
                <foreach collection="dataCorporationIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                     or bc4.company_id in
                <foreach collection="dataCorporationIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                )
            </if>
            and v1.is_del != 1
        </where>
        GROUP BY v1.status
        union all
        SELECT count(*) total, '0' travel_status, '总数' detail from vehicle v2
        -- 所属分公司
        LEFT JOIN branch_company bc2 ON v2.subordinate_branch = bc2.id
         LEFT JOIN branch_company bc3 ON v2.park_branch_company_id = bc3.id
        LEFT JOIN branch_company bc4 ON v2.expect_destination_branch_company_id = bc4.id
        <where>
            <if test="startTime != null">
                and v2.create_time between #{startTime} and #{endTime}
            </if>
            <if test="numberPlate != null and numberPlate != ''">
                and v2.number_plate = #{numberPlate}
            </if>
            <if test="status != null">
                and v2.status = #{status}
            </if>
            <if test="code !=null">
                and v2.code=#{code}
            </if>
            <if test="companyIds != null and companyIds.size > 0">
                and v2.park_branch_company_id in
                <foreach collection="companyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="modelId != null and modelId != ''">
                and v2.model_id = #{modelId}
            </if>
             <if test="parkBranchCompanyId != null and parkBranchCompanyId > 0">
                and (bc2.id = #{parkBranchCompanyId} or bc3.id = #{parkBranchCompanyId}  or bc4.id = #{parkBranchCompanyId})
            </if>
            <if test="branchId != null and branchId > 0">
                and (bc2.company_id = #{branchId} or bc3.company_id = #{branchId} or bc4.company_id = #{branchId})
            </if>
            <if test="dataCompanyIds != null and dataCompanyIds.size() > 0">
                and ( bc2.id in
                <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                    or bc3.id in
                <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                     or bc4.id in
                <foreach collection="dataCompanyIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                )
            </if>

            <if test="dataCorporationIds != null and dataCorporationIds.size() > 0">
                and ( bc2.company_id in
                <foreach collection="dataCorporationIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                    or bc3.company_id in
                <foreach collection="dataCorporationIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                     or bc4.company_id in
                <foreach collection="dataCorporationIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
                )
            </if>
            and v2.is_del != 1
        </where>
    </select>

    <select id="searchUsableModel" parameterType="java.util.Map"
            resultMap="searchUsableModelMap">
        (select distinct vm.id as model_id, bc.id as company_id
        <!-- , group_concat(v.id) as vehicle_ids -->
        <if test=" catas != null ">
            ,GROUP_CONCAT(vc.cata_id) as catas
        </if>
        <!-- yearNo4Where 标识时间参数不用于where条件，用于select部分 -->
        <if test=" yearMonthAndParam !=null  ">
            ,
            <if test="yearNo4Where != null and yearNo4Where == true">
                (max(
                ifnull(vbi.bit_str = #{yearMonthAndParamBitStr}, false)
                <!-- 租车列表 不过滤前后预约记录不符的车辆 但是合并标示车型是否有车 即 hasVehicle-->
                <if test="withoutRecordWhere != null and withoutRecordWhere = 1 and startCompanyId != null and endCompanyId != null ">
                    <!--                    and (abr.to_lift_company is null or abr.to_lift_company = #{startCompanyId})-->
                    <!--                    and (abr.to_return_company is null or abr.to_return_company = #{endCompanyId})-->
                    and (
                    (IFNULL(abr.to_lift_company, v.park_branch_company_id) = #{startCompanyId})
                    and
                    (abr.to_return_company is null or abr.to_return_company = #{endCompanyId})
                    )
                </if>
                )
                )
            </if>
            <if test="yearNo4Where == null or yearNo4Where != true">
                (if(count(*) > 1, 1 , 0))
            </if>

            as hasVehicle
        </if>
        <if test="lon != null and lat != null">
            ,st_distance_sphere(point(#{lon}, #{lat}), point(bc.longitude, bc.latitude)) as distance
        </if>

        <include refid="searchUsableSql"/>
        and vm.id is not null
        and vm.status = 1
        and vm.isdel = 0
        and vm.app_show = 1
        and bc.id is not null
        GROUP BY model_id, company_id
        <if test="lon != null and lat != null">, distance</if>
        <!--<if test=" yearMonthAndParam !=null and yearNo4Where != null and yearNo4Where == true">, hasVehicle</if>-->

        <!-- 循环 相同父级 数据做并集， 不同父级做或集 -->
        <if test=" catas != null ">
            having ( 1
            <foreach collection="catas" index="key" item="cataList">
                &amp; (
                <foreach collection="cataList" index="cIndex" item="cata">
                    <if test=" cIndex != 0">
                        |
                    </if>
                    (case when FIND_IN_SET(#{cata.id},catas) > 0 then 1 else 0 end)
                </foreach>
                )
            </foreach>
            ) > 0
        </if>
        )
        <!-- union 所有下架车型 -->

        <if test="startCompanyId != null or parkBranchCompanyId != null ">
            union
            (select vm.id as model_id, bc.id as company_id
            <if test=" catas != null ">
                ,null as catas
            </if>
            <if test=" yearMonthAndParam !=null ">
                ,0 as hasVehicle
            </if>
            <if test="lon != null and lat != null">
                <!-- 处理距离 -->
                ,st_distance_sphere(point(#{lon}, #{lat}), point(bc.longitude, bc.latitude)) as distance
            </if>
            from vehicle_model vm
                left join branch_company bc on
                <choose>
                    <when test="startCompanyId != null">
                        bc.id = #{startCompanyId}
                    </when>
                    <otherwise>
                        bc.id = #{parkBranchCompanyId}
                    </otherwise>
                </choose>
            where vm.isdel = 0 and vm.status = 1 and vm.app_show = 1)
        </if>
        <if test="lon != null and lat != null">
            order by
            <if test=" yearMonthAndParam !=null ">
                hasVehicle desc,
            </if>
            distance asc, model_id asc
        </if>
    </select>

    <select id="searchUsableVehicle" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.pojo.vo.UsableVeicleVO">
        select v.*
        , bcm.province_name as manageProvinceName
        , bcm.city_name as manageCityName
        <if test="lon != null and lat != null">
            ,st_distance_sphere(point(#{lon}, #{lat}), point(bc.longitude, bc.latitude)) as distance
        </if>
        <if test=" catas2 != null ">
            ,vc.catas as catas
        </if>
<!--        <if test=" yearMonthAndParam !=null ">-->
<!--            ,ifnull(vbi.booked_date,0) as booked_date-->
<!--        </if>-->
        <if test="startCompanyId != null and endCompanyId != null and recordIntersection != null and recordIntersection == true">
            <!-- 显示 配对 开始时间结束时间 -->
            , abr.to_lift_company as to_lift_company_id
            , abr.to_lift_must_after_date
            , abr.to_return_company as to_return_company_id
            , abr.to_return_must_before_date
        </if>
        <include refid="searchUsableSql"/>
        <if test="memberUse != null and memberUse>0">
            and (v.member_use = 1 or v.member_use = #{memberUse})
        </if>
        <if test="brandId != null ">
            and brand_id = #{brandId}
        </if>
        <if test="categoryId != null ">
            and category_id = #{categoryId}
        </if>

        <!-- 循环 相同父级 数据做并集， 不同父级做或集 -->
        <if test=" catas2 != null ">
            and ( 1
            <foreach collection="catas2" index="key" item="cataList">
                &amp; (
                <foreach collection="cataList" index="cIndex" item="cata">
                    <if test=" cIndex != 0">
                        |
                    </if>
                    (case when FIND_IN_SET(#{cata.id},catas) > 0 then 1 else 0 end)
                </foreach>
                )
            </foreach>
            ) > 0
        </if>

        <if test="lon != null and lat != null">
            order by
            distance asc
        </if>
    </select>

    <!-- 查询可用车辆/车型 的公用 from 和 where 部分条件 -->
    <!-- 包含     vbi： -->
    <sql id="searchUsableSql">
        from vehicle v
        <if test=" yearMonthAndParam !=null ">
            left join (
                select
                    vbi.vehicle,
                    BIT_OR(
                       CONCAT(
                           <foreach collection="yearMonthAndParam" index="yearMonth" item="andOperation" separator=",">
                                <include refid="yearMonthAndParamSql"></include>
                           </foreach>
                       )
                    ) as bit_str
                from vehicle_book_info vbi group by vbi.vehicle
            ) vbi on v.`id` = vbi.vehicle
        </if>
        left join vehicle_model vm on v.model_id = vm.id
        left join branch_company bc on v.park_branch_company_id = bc.id
        left join branch_company bcm on v.manage_company_id = bcm.id
        <if test="timeUse != null and timeUse == true">
            left join vehicle_use_time vut on v.id = vut.vehicle_id and vut.status = 1
        </if>
<!--        <if test="startCompanyId != null and endCompanyId != null ">-->
            <!--
                 * 原（大于预约结束时间，小于预约开始时间的预约记录）
                 * 相交叉（大于预约开始时间，小于预约结束时间的预约记录）
            -->
            <if test="recordIntersection == null or recordIntersection != true">
                left join (
                <include refid="aroundBookRecord"></include>
                ) abr on v.`id` = abr.vehicle_id
            </if>
            <if test="recordIntersection != null and recordIntersection == true">
                left join (
                <include refid="aroundBookRecordIntersection"></include>
                ) abr on v.`id` = abr.vehicle_id
            </if>
<!--        </if>-->
        <if test=" catas != null ">
            left join vehicle_cata vc on vm.id = vc.vehicle_model_id
        </if>
        <if test=" catas2 != null ">
            left join (select vehicle_id,GROUP_CONCAT(cata_id) as catas from vehicle_extension where is_del = 0 group by vehicle_id) vc on v.id = vc.vehicle_id
        </if>
        <where>
            <if test="timeUse != null and timeUse == true">
                and (vut.vehicle_id is null  or (

                (#{bookStartDateTimestamp} &gt; vut.start_time and #{bookEndDateTimestamp} &lt; vut.end_time)

                <if test="startCompanyId != null and endCompanyId != null">
                    and vut.company_id = #{startCompanyId} and vut.company_id = #{endCompanyId}
                </if>

                ) )
            </if>
            <!-- 若需根据预定日期条件查询，针对换为位操作  -->
                <!-- yearNo4Where 标识时间参数是否用于where条件 -->
            <if test=" yearMonthAndParam !=null and yearMonthAndParam != null and (yearNo4Where == null or yearNo4Where != true)">
                and vbi.bit_str = #{yearMonthAndParamBitStr}
            </if>

            <if test=" yearNo4Where != null and recordIntersection != null and recordIntersection == true">
                <!-- 1、最后还车时间(天) 大于 最早提车时间(天)  并且多于一天-->
                and if((abr.to_lift_must_after_date is null or abr.to_return_must_before_date is null), true
                , (str_to_date(DATE_FORMAT(abr.to_return_must_before_date,'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s')
                >  DATE_ADD(DATE_ADD(str_to_date(DATE_FORMAT(abr.to_lift_must_after_date,'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 2 DAY),INTERVAL -1 SECOND))
                )
            </if>

            <!-- 根据前后record 过滤车辆  -->
            <if test="startCompanyId != null and endCompanyId != null ">
                <if test="withoutRecordWhere == null">
                    and (
                    (IFNULL(abr.to_lift_company, v.park_branch_company_id) = #{startCompanyId})
                    and
                    (abr.to_return_company is null or abr.to_return_company = #{endCompanyId})
                    )
                </if>
            </if>
            <if test=" modelId != null ">
                and v.model_id = #{modelId}
            </if>
            <if test=" addrCity != null ">
                and bc.addr_city = #{addrCity}
            </if>
            <if test=" hotSign != null ">
                and vm.hot_sign = #{hotSign}
            </if>
            <if test=" parkBranchCompanyId != null ">
                and v.park_branch_company_id = #{parkBranchCompanyId}
            </if>
            <if test=" goodsType != null ">
                and v.goods_type = #{goodsType}
            </if>
            and v.is_del = 0
            and v.status != 3
            and v.state = 1
            <if test="useType != null">
                and v.use_type = #{useType}
            </if>
            and bc.is_del = 0
            and bc.is_show = 1
        </where>
    </sql>

    <!-- 时间参数循环 -->
    <!-- 包含     vbi：车辆预约信息  -->
    <!-- vbi年月相等 或者 年月为空-->
    <!-- booked_date 与换算 判断是否有重叠 日期-->
    <sql id = "yearMonthAndParamSql">
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
                ( (vbi.`year_month` = #{yearMonth} or vbi.`year_month` is null) and
                ifnull(vbi.`booked_date`,0) &amp; #{andOperation.andOperationFactor} =
                #{andOperation.andOperationRs}
                )
    </sql>

    <!-- 前后被预定的情况 -->
    <!-- 大于预约结束时间，小于预约开始时间的预约记录） -->
    <sql id = "aroundBookRecord">
        select sevbr.*
        , vbre.ret_company as to_lift_company
        , sevbr.max_book_end_date as to_lift_must_after_date
        , vbrs.lift_company as to_return_company
        , sevbr.min_book_start_date as to_return_must_before_date
        from
        <!--
            查询开始时间 前面的最后一条 预约记录
            查询结束时间 后面的第一条 预约记录
            添加比较actual_start_date 和 actual_end_date-->
        <!-- sevbr表：车辆联表预约记录，统计获得预约开始时间前最大预约记录时间、、预约结束时间后最小预约记录时间，过滤掉status != 4 and status != 6，和 -->
        (select vehicle_id
            , max(if(IFNULL(actual_end_date, book_end_date) &lt; #{startDateExtend}, IFNULL(actual_end_date,book_end_date), null)) as max_book_end_date
            , min(if(IFNULL(actual_start_date, book_start_date) &gt; #{endDateExtend}, IFNULL(actual_start_date, book_start_date), null)) as min_book_start_date
        from vehicle_book_record where status != 4 and status != 6 group by vehicle_id) sevbr
        left join vehicle_book_record vbre
        on sevbr.vehicle_id = vbre.vehicle_id and sevbr.max_book_end_date = IFNULL(vbre.actual_end_date, vbre.book_end_date) and vbre.status != 4 and vbre.status != 6
        left join vehicle_book_record vbrs
        on sevbr.vehicle_id = vbrs.vehicle_id and sevbr.min_book_start_date = IFNULL(vbrs.actual_start_date, vbrs.book_start_date) and vbrs.status != 4 and vbrs.status != 6
    </sql>

    <!-- 前后被预定的情况(相交叉) -->
    <!-- （大于预约开始时间，小于预约结束时间的预约记录） -->
    <sql id = "aroundBookRecordIntersection">
        select sevbr.*
        , vbre.ret_company as to_lift_company
        , sevbr.max_book_end_date as to_lift_must_after_date
        , vbrs.lift_company as to_return_company
        , sevbr.min_book_start_date as to_return_must_before_date
        from
        <!--
            查询【结束】时间 前面的最后一条 预约记录
            查询【开始】时间 后面的第一条 预约记录
            添加比较actual_start_date 和 actual_end_date-->
        <!-- sevbr表：车辆联表预约记录，统计获得预约开始时间前最大预约记录时间、、预约结束时间后最小预约记录时间，过滤掉status != 4 and status != 6，和 -->
        (select vehicle_id
        , max(if(IFNULL(actual_end_date, book_end_date) &lt; #{endDateExtend}, IFNULL(actual_end_date,book_end_date), null)) as max_book_end_date
        , min(if(IFNULL(actual_start_date, book_start_date) &gt; #{startDateExtend}, IFNULL(actual_start_date, book_start_date), null)) as min_book_start_date
        from vehicle_book_record where status != 4 and status != 6 group by vehicle_id) sevbr
        left join vehicle_book_record vbre
        on sevbr.vehicle_id = vbre.vehicle_id and sevbr.max_book_end_date = IFNULL(vbre.actual_end_date, vbre.book_end_date) and vbre.status != 4 and vbre.status != 6
        left join vehicle_book_record vbrs
        on sevbr.vehicle_id = vbrs.vehicle_id and sevbr.min_book_start_date = IFNULL(vbrs.actual_start_date, vbrs.book_start_date) and vbrs.status != 4 and vbrs.status != 6
    </sql>

    <select id="calendarPriceList" resultType="com.xxfc.platform.vehicle.pojo.VehicleBookRecordVO2">
        select *,
        if(IFNULL(actual_end_date, book_end_date) &lt; #{startDateExtend}, IFNULL(actual_end_date,book_end_date), null) as book_end_date,
        if(IFNULL(actual_start_date, book_start_date) &gt; #{endDateExtend}, IFNULL(actual_start_date, book_start_date), null) as book_start_date
        from vehicle_book_record
        where status != 4 and status != 6
        order by book_start_date asc;
    </select>


    <select id="selectList"  resultType="com.xxfc.platform.vehicle.pojo.vo.VehicleVO" parameterType="com.xxfc.platform.vehicle.pojo.dto.VehicleFindDTO">
        SELECT
        v.*,
        b.cn_name as brandName,
        c.`name` categoryName,
        bc.`leader` as companyLeader,
        bc.`leader_contact_info` as companyLeaderContact,
        bc.`name` as companyName,
        bc1.`name` as manageCompanyName,
        bc1.`leader` as manageCompanyLeader,
        bc1.`leader_contact_info` as manageCompanyLeaderContact,
        bc2.`name` as parkCompanyName,
        bc2.`leader` as parkCompanyLeader,
        bc2.`leader_contact_info` as parkCompanyLeaderContact
        FROM  vehicle v
        LEFT JOIN vehicle_brand b ON v.brand_id=b.id
        LEFT JOIN vehicle_category c ON v.category_id=c.id
        LEFT JOIN branch_company bc ON v.subordinate_branch=bc.id
        LEFT JOIN branch_company bc1 ON v.manage_company_id=bc1.id
        LEFT JOIN branch_company bc2 ON v.park_branch_company_id=bc2.id
        <where>
            v.is_del = 0
            <if test="name != null and  name != ''">
                AND ( v.`name` like concat('%',#{name},'%') or v.`number_plate` like concat('%',#{name},'%') )
            </if>
            <if test="brandId != null and brandId > 0">
                AND  v.`brand_id`= #{brandId}
            </if>
            <if test="vehicleId != null and vehicleId != '' ">
                AND  v.`id`= #{vehicleId}
            </if>
            <if test="categoryId != null and categoryId > 0">
                AND  v.`category_id`= #{categoryId}
            </if>
            <if test="branchId != null and branchId > 0">
                AND  (v.`subordinate_branch`= #{branchId} or v.`park_branch_company_id`= #{branchId} or v.`manage_company_id`= #{branchId} )
            </if>
            <if test="state != null ">
                AND  v.`state`= #{state}
            </if>
            <if test="goodsType != null ">
                AND  v.`goods_type`= #{goodsType}
            </if>
            <if test="goodStatus != null and goodStatus > 0 and type != null and type > 0 ">
                <choose>
                    <when test="goodStatus == 1 and type == 1">
                        and v.`state`= 1 and  v.`subordinate_branch` = v.`park_branch_company_id` and  v.`subordinate_branch` = v.`manage_company_id`
                    </when>
                    <when test="goodStatus == 2 and type == 1">
                        and v.`state`= 2 and  v.`subordinate_branch` = v.`park_branch_company_id` and  v.`subordinate_branch` = v.`manage_company_id`
                    </when>
                    <when test="goodStatus == 1 and type == 2">
                        and v.`state`= 1
                    </when>
                    <when test="goodStatus == 2 and type == 2 ">
                        and v.`state`= 2
                    </when>
                    <when test="goodStatus == 3">
                        and  ( v.`subordinate_branch` != v.`park_branch_company_id` or v.`subordinate_branch` != v.`manage_company_id` )
                    </when>
                </choose>
            </if>
            <if test="dataCompanyIds != null and dataCompanyIds.size > 0">
                <if test="type != null and type > 0">
                    <choose>
                        <when test="type !=null and type == 1">
                            and bc.id in
                            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                                #{id}
                            </foreach>
                        </when>
                        <when test="type !=null and type == 2">
                            and bc1.id in
                            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                                #{id}
                            </foreach>
                        </when>
                        <when test="type !=null and type == 3">
                            and (
                            bc2.id in
                            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                                #{id}
                            </foreach>
                            and  v.subordinate_branch not in
                            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                                #{id}
                            </foreach>
                            )
                        </when>
                        <otherwise>
                            and (bc.id in
                            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                                #{id}
                            </foreach>
                            or bc1.id in
                            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                                #{id}
                            </foreach>
                            or bc2.id in
                            <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                                #{id}
                            </foreach>
                            )
                        </otherwise>
                    </choose>
                </if>
            </if>
            <if test="dataCorporationIds != null and dataCorporationIds.size > 0">
                and bc.company_id  in
                <foreach collection="dataCorporationIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        <choose>
            <when test="isMinPrice !=null and isMinPrice==1">
                ORDER BY v.price ASC,v.create_time DESC
            </when>
            <otherwise>
                ORDER BY v.create_time DESC
            </otherwise>
        </choose>

    </select>


    <select id="selectListV2"  resultType="com.xxfc.platform.vehicle.pojo.vo.VehicleVO" parameterType="java.util.Map">
        SELECT
        v.*,
        b.cn_name as brandName,
        c.`name` categoryName
        FROM  ( <include refid="searchUsableSqlV2"/> )  v
        LEFT JOIN (select  * from  vehicle_use_time where  status =1 ) u ON v.id = u.vehicle_id
        LEFT JOIN vehicle_brand b ON v.brand_id=b.id
        LEFT JOIN vehicle_category c ON v.category_id=c.id
        <where>
            v.is_del = 0 and u.id is null 
            <if test="goodsType != null ">
                AND  v.`goods_type`= #{goodsType}
            </if>
            <if test="branchId != null and branchId > 0">
                AND  (v.`subordinate_branch`= #{branchId} AND  v.`manage_company_id`= #{branchId} )
            </if>
        </where>
        ORDER BY v.create_time DESC
    </select>

    <sql id="searchUsableSqlV2">
        select  *  from vehicle v
        <if test=" yearMonthAndParam !=null ">
            left join (
            select
            vbi.vehicle,
            BIT_OR(
            CONCAT(
            <foreach collection="yearMonthAndParam" index="yearMonth" item="andOperation" separator=",">
                <include refid="yearMonthAndParamSql"></include>
            </foreach>
            )
            ) as bit_str
            from vehicle_book_info vbi group by vbi.vehicle
            ) vbi on v.`id` = vbi.vehicle
        </if>
        <!--        <if test="startCompanyId != null and endCompanyId != null ">-->
        <!--
             * 原（大于预约结束时间，小于预约开始时间的预约记录）
             * 相交叉（大于预约开始时间，小于预约结束时间的预约记录）
        -->
        <where>
             v.is_del = 0
            <!-- 若需根据预定日期条件查询，针对换为位操作  -->
            <!-- yearNo4Where 标识时间参数是否用于where条件 -->
            <if test=" yearMonthAndParam !=null and yearMonthAndParam != null and (yearNo4Where == null or yearNo4Where != true)">
                and vbi.bit_str = #{yearMonthAndParamBitStr}
            </if>

            <if test=" yearNo4Where != null and recordIntersection != null and recordIntersection == true">
                <!-- 1、最后还车时间(天) 大于 最早提车时间(天)  并且多于一天-->
                and if((abr.to_lift_must_after_date is null or abr.to_return_must_before_date is null), true
                , (str_to_date(DATE_FORMAT(abr.to_return_must_before_date,'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s')
                >  DATE_ADD(DATE_ADD(str_to_date(DATE_FORMAT(abr.to_lift_must_after_date,'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 2 DAY),INTERVAL -1 SECOND))
                )
            </if>
        </where>
    </sql>

    <select id="appSelectList"  resultType="com.xxfc.platform.vehicle.pojo.vo.VehicleVO" parameterType="com.xxfc.platform.vehicle.pojo.dto.VehicleFindAppDTO">
        SELECT
        v.*,
        b.cn_name as brandName,
        c.`name` categoryName,
        bc.`name` as companyName,
        bc1.`leader` as manageCompanyLeader,
        bc1.`leader_contact_info` as manageCompanyLeaderContact,
        bc1.province_name as manageProvinceName,
        bc1.city_name as manageCityName
        <if test="lon != null and lat != null">
            ,st_distance_sphere(point(#{lon}, #{lat}), point(bc.longitude, bc.latitude)) as distance
        </if>
        FROM  vehicle v
        LEFT JOIN vehicle_brand b ON v.brand_id=b.id
        LEFT JOIN vehicle_category c ON v.category_id=c.id
        LEFT JOIN branch_company bc ON v.subordinate_branch=bc.id
        LEFT JOIN branch_company bc1 ON v.manage_company_id=bc1.id
        <where>
            v.is_del = 0
            <if test="addrCity !=null">
                AND bc.addr_city=#{addrCity}
            </if>
            <if test="name != null and  name != ''">
                AND ( v.`name` like concat('%',#{name},'%') or v.`number_plate` like concat('%',#{name},'%') )
            </if>
            <if test="brandId != null and brandId > 0">
                AND  v.`brand_id`= #{brandId}
            </if>
            <if test="vehicleId != null and vehicleId != '' ">
                AND  v.`id`= #{vehicleId}
            </if>
            <if test="categoryId != null and categoryId > 0">
                AND  v.`category_id`= #{categoryId}
            </if>
            <if test="branchId != null and branchId > 0">
                AND  v.`subordinate_branch`= #{branchId}
            </if>
            <if test="state != null ">
                AND  v.`state`= #{state}
            </if>
            <if test="goodsType != null ">
                AND  v.`goods_type`= #{goodsType}
            </if>
            <if test="dataCompanyIds != null and dataCompanyIds.size > 0">
                and bc.id in
                <foreach collection="dataCompanyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="dataCorporationIds != null and dataCorporationIds.size > 0">
                and bc.company_id  in
                <foreach collection="dataCorporationIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        order by
        <if test="lon != null and lat != null">
            distance asc,
        </if>
        <choose>
            <when test="isMinPrice !=null and isMinPrice==1">
                v.price ASC,v.create_time DESC
            </when>
            <otherwise>
                v.create_time DESC
            </otherwise>
        </choose>

    </select>

</mapper>