<?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>
    
    <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.QueryVehicleVo">
        select v.`id`,
        v.`code`,
        v.`status`,
        v.number_plate,
        v.brand,
        v.subordinate_branch,
        bc.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
        <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.`subordinate_branch` = bc.id
        where
        1=1
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="mRangeDateStart !=null">
            and v.maintenance_date &gt;= #{mRangeDateStart}
        </if>
        <if test="aVRangeDateStart !=null">
            and v.annual_verification_date &lt;= #{aVRangeDateEnd}
        </if>
        <if test="aVRangeDateEnd !=null">
            and v.annual_verification_date &gt;= #{aVRangeDateStart}
        </if>
        <if test="insuranceDateRangeStart !=null">
            and v.insurance_end_date &lt;= #{insuranceDateRangeEnd}
        </if>
        <if test="insuranceDateRangeEnd !=null">
            and v.insurance_end_date &gt;= #{insuranceDateRangeStart}
        </if>
        <if test="insuranceCompany !=null">
            and v.insurance_company = #{insuranceCompany}
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin = #{vin}a
        </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 = #{numberPlate}
        </if>
        <if test="brand !=null">
            and v.brand = #{brand}
        </if>
        <if test="useType !=null">
            and v.use_type = #{useType}
        </if>
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
        <if test=" yearMonthAndParam !=null  ">
            <foreach collection="yearMonthAndParam" index="yearMonth" item="andOperation">
                and
                ( (vbi.`year_month` = #{yearMonth} or vbi.`year_month` is null) and
                ifnull(vbi.`booked_date`,0) &amp; #{andOperation.andOperationFactor} = #{andOperation.andOperationRs}
                )
            </foreach>
        </if>
        order by v.code
    </select>

    <select id="getByPageNotAllData" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.pojo.QueryVehicleVo">
        select v.`id`,
        v.`code`,
        v.`status`,
        v.number_plate,
        v.brand,
        v.subordinate_branch,
        bc.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
        <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.`subordinate_branch` = bc.id
        where
        1=1
        <if test="companyList != null">
            and ( v.park_branch_company_id in (
            <foreach collection="companyList" item="companyId">
                <trim suffixOverrides=",">
                    #{companyId},
                </trim>
            </foreach>
            )
            or v.expect_destination_branch_company_id in (
            <foreach collection="companyList" item="companyId">
                <trim suffixOverrides=",">
                    #{companyId},
                </trim>
            </foreach>
            )
            )
        </if>
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="mRangeDateStart !=null">
            and v.maintenance_date &gt;= #{mRangeDateStart}
        </if>
        <if test="aVRangeDateStart !=null">
            and v.annual_verification_date &lt;= #{aVRangeDateEnd}
        </if>
        <if test="aVRangeDateEnd !=null">
            and v.annual_verification_date &gt;= #{aVRangeDateStart}
        </if>
        <if test="insuranceDateRangeStart !=null">
            and v.insurance_end_date &lt;= #{insuranceDateRangeEnd}
        </if>
        <if test="insuranceDateRangeEnd !=null">
            and v.insurance_end_date &gt;= #{insuranceDateRangeStart}
        </if>
        <if test="insuranceCompany !=null">
            and v.insurance_company = #{insuranceCompany}
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin = #{vin}
        </if>
        <if test="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 = #{numberPlate}
        </if>
        <if test="brand !=null">
            and v.brand = #{brand}
        </if>
        <if test="useType !=null">
            and v.use_type = #{useType}
        </if>
        <!-- 若需根据预定日期条件查询，针对换为位操作  -->
        <if test=" yearMonthAndParam !=null  ">
            <foreach collection="yearMonthAndParam" index="yearMonth" item="andOperation">
                and
                ( (vbi.`year_month` = #{yearMonth} or vbi.`year_month` is null) and
                ifnull(vbi.`booked_date`,0) &amp; #{andOperation.andOperationFactor} = #{andOperation.andOperationRs}
                )
            </foreach>
        </if>
        order by v.code
    </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
            `code` = #{code}
        for update
    </select>
    <select id="selectByNumberPlate" resultType="com.xxfc.platform.vehicle.entity.Vehicle">
        select * from vehicle
        where number_plate = #{numberPlate}
    </select>

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

    <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>
        <if test="lon != null and lat != null">
            ,st_distance_sphere(point(#{lon}, #{lat}), point(bc.longitude, bc.latitude)) as distance
        </if>

        <include refid="searchUsableSql"/>

        GROUP BY model_id, company_id<if test="lon != null and lat != null">, distance</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>

        <if test="lon != null and lat != null">
            order by
            distance 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="lon != null and lat != null">
            order by
            distance asc
        </if>
    </select>

    <sql id="searchUsableSql">
        from vehicle v
        <if test=" yearMonthAndParam !=null  ">
            left join
            vehicle_book_info 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=" catas != null ">
            left join vehicle_cata vc on vm.id = vc.vehicle_model_id
        </if>
        <where>
            <!-- 若需根据预定日期条件查询，针对换为位操作  -->
            <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=" modelId != null ">
                and v.model_id = #{modelId}
            </if>
            <if test=" addrCity != null ">
                and bc.addr_city = #{addrCity}
            </if>
        </where>
    </sql>

</mapper>