<?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">
        <!--        <collection column="company_id" property="id" ofType="com.xxfc.platform.vehicle.entity.BranchCompany" select="com.xxfc.platform.vehicle.mapper.BranchCompanyMapper.selectByPrimaryKey" />-->
        <!--        <result column="company_id" property="companyId" jdbcType="VARCHAR" javaType="java.lang.Integer"/>-->
        <!--        <result column="model_id" property="modelId" jdbcType="INTEGER" javaType="java.lang.Integer"/>-->
        <!--        <result column="vehicle_id" property="modelId" jdbcType="INTEGER" javaType="java.lang.Integer"/>-->
        <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"/>
        <!--        <association property="vehicle" column="vehicle_id" select="com.xxfc.platform.vehicle.mapper.VehicleMapper.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.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  ">
            <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=" 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.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="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>
                )-->
    <!--            or v.subordinate_branch 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="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 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  ">
            <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 ">
            <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,
        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  ">
            <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=" 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,
        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="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>
            )-->
            <!--            or v.subordinate_branch 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="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  ">
            <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 ">
            <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="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>
            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="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
        <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>
            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
        <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>
            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
        <if test=" catas != null ">
            ,GROUP_CONCAT(vc.cata_id) as catas
        </if>
        <!-- yearNo4Where 标识时间参数不用于where条件，用于select部分 -->
        <if test=" yearMonthAndParam !=null and 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})
                </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 and yearNo4Where != null and yearNo4Where == true">
                ,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 = 0 and vm.app_show = 1)
        </if>
        <if test="lon != null and lat != null">
            order by
            <if test=" yearMonthAndParam !=null and yearNo4Where != null and yearNo4Where == true">
                hasVehicle desc,
            </if>
            distance asc, model_id asc
        </if>
    </select>

    <select id="searchUsableVehicle" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.entity.Vehicle">
        select v.*
        <if test="lon != null and lat != null">
            ,st_distance_sphere(point(#{lon}, #{lat}), point(bc.longitude, bc.latitude)) as distance
        </if>
<!--        <if test=" yearMonthAndParam !=null ">-->
<!--            ,ifnull(vbi.booked_date,0) as booked_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="lon != null and lat != null">
            order by
            distance asc
        </if>
    </select>

    <!-- 查询可用车辆/车型 的公用 from 和 where 部分条件 -->
    <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
        <if test="startCompanyId != null and endCompanyId != null ">
            left join (
                <include refid="aroundBookRecord"></include>
            ) abr on v.`id` = abr.vehicle_id
        </if>
        <if test=" catas != null ">
            left join vehicle_cata vc on vm.id = vc.vehicle_model_id
        </if>
        <where>
            <!-- 若需根据预定日期条件查询，针对换为位操作  -->
                <!-- yearNo4Where 标识时间参数是否用于where条件 -->
            <if test=" yearMonthAndParam !=null and yearMonthAndParam != null and yearNo4Where == null">
                vbi.bit_str = #{yearMonthAndParamBitStr}
            </if>

            <!-- 根据前后record 过滤车辆  -->
            <if test="startCompanyId != null and endCompanyId != null ">
                <if test="withoutRecordWhere == 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})
                    )
                </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>
            and v.is_del = 0
            and v.status != 3
            <if test="useType != null">
                and v.use_type = #{useType}
            </if>
            and bc.is_del = 0
            and bc.is_show = 1
        </where>
    </sql>

    <!-- 时间参数循环 -->
    <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
        , vbrs.lift_company as to_return_company
        from
        <!--
            查询开始时间 前面的最后一条 预约记录
            查询结束时间 后面的第一条 预约记录
            添加比较actual_start_date 和 actual_end_date-->
        (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 = 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 = vbrs.book_start_date and vbrs.status != 4 and vbrs.status != 6
    </sql>

</mapper>