<?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">
        <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.getByVehicleId"/>
    </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 r.* FROM (
        select DISTINCT v.`id`,
        v.`code`,
        v.`status`,
        v.number_plate,
        v.brand,
        IFNULL(v.park_branch_company_id,v.expect_destination_branch_company_id) AS subordinate_branch,
        IFNULL(bc.name,bc1.name) AS subBranchName,
        v.use_type,
        v.remark,
        v.create_time,
        v.vin,
        v.insurance_company,
        v.insurance_no,
        v.insurance_start_date,
        v.insurance_end_date,
        v.annual_verification_date,
        v.maintenance_date,
        v.maintenance_mileage,
        v.belong_to,
        v.belong_to_name,
        v.driving_license_path,
        v.engine_num,
        v.manufacturer,
        v.receive_time,
        v.update_time,
        v.mileage_last_update as mileage
        <if test=" yearMonthAndParam != null ">
            ,vbi.booked_date
        </if>
        from vehicle v
        <if test=" yearMonthAndParam != null  ">
            left join
            vehicle_book_info vbi on v.`id` = vbi.vehicle
        </if>
        LEFT JOIN branch_company bc ON v.park_branch_company_id = bc.id
        LEFT JOIN branch_company bc1 ON v.expect_destination_branch_company_id = bc1.id
        where
        v.is_del=0
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="mRangeDateStart !=null">
            and v.maintenance_date &gt;= #{mRangeDateStart}
        </if>
        <if test="aVRangeDateStart !=null">
            and v.annual_verification_date &lt;= #{aVRangeDateEnd}
        </if>
        <if test="aVRangeDateEnd !=null">
            and v.annual_verification_date &gt;= #{aVRangeDateStart}
        </if>
        <if test="insuranceDateRangeStart !=null">
            and v.insurance_end_date &lt;= #{insuranceDateRangeEnd}
        </if>
        <if test="insuranceDateRangeEnd !=null">
            and v.insurance_end_date &gt;= #{insuranceDateRangeStart}
        </if>
        <if test="insuranceCompany !=null">
            and v.insurance_company = #{insuranceCompany}
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin = #{vin}
        </if>
        <if test="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 ">
        	and (
        	<trim suffixOverrides="OR">
                <if test="addrProvince !=null">
                    (bc.addr_province=#{addrProvince} or bc1.addr_province=#{addrProvince}) OR
                </if>
                <if test="addrCity !=null">
                    (bc.addr_city=#{addrProvince} or bc1.addr_city=#{addrProvince}) OR
                </if>
                <if test="zoneId !=null">
                    (bc.zone_id=#{zoneId} or bc1.zone_id=#{zoneId}) OR
                </if>
        	</trim>
        	)
        </if>
        order by v.code
        ) r ORDER BY r.subBranchName
    </select>

    <select id="getByPageNotAllData" parameterType="java.util.Map"
            resultType="com.xxfc.platform.vehicle.pojo.QueryVehicleVo">
        SELECT r.* FROM (
        select DISTINCT v.`id`,
        v.`code`,
        v.`status`,
        v.number_plate,
        v.brand,
        IFNULL(v.park_branch_company_id,v.expect_destination_branch_company_id) AS subordinate_branch,
        IFNULL(bc.name,bc1.name) AS subBranchName,
        v.use_type,
        v.remark,
        v.create_time,
        v.vin,
        v.insurance_company,
        v.insurance_no,
        v.insurance_start_date,
        v.insurance_end_date,
        v.annual_verification_date,
        v.maintenance_date,
        v.maintenance_mileage,
        v.belong_to,
        v.belong_to_name,
        v.driving_license_path,
        v.engine_num,
        v.manufacturer,
        v.receive_time,
        v.update_time,
        v.mileage_last_update as mileage
        <if test=" yearMonthAndParam !=null ">
            ,vbi.booked_date
        </if>
        from vehicle v
        <if test=" yearMonthAndParam !=null  ">
            left join
            vehicle_book_info vbi on v.`id` = vbi.vehicle
        </if>
        LEFT JOIN branch_company bc ON v.park_branch_company_id = bc.id
        LEFT JOIN branch_company bc1 ON v.expect_destination_branch_company_id = bc1.id
        where
        v.is_del=0
        <if test="companyList != null">
            and ( v.park_branch_company_id in (
                <trim suffixOverrides=",">
                    <foreach collection="companyList" item="companyId">
                    #{companyId},
                    </foreach>
                </trim>
            )
            or v.expect_destination_branch_company_id in (
                <trim suffixOverrides=",">
                    <foreach collection="companyList" item="companyId">
                    #{companyId},
                    </foreach>
                </trim>
            )
            )
        </if>
        <if test="mRangeDateEnd !=null">
            and v.maintenance_date &lt;= #{mRangeDateEnd}
        </if>
        <if test="mRangeDateStart !=null">
            and v.maintenance_date &gt;= #{mRangeDateStart}
        </if>
        <if test="aVRangeDateStart !=null">
            and v.annual_verification_date &lt;= #{aVRangeDateEnd}
        </if>
        <if test="aVRangeDateEnd !=null">
            and v.annual_verification_date &gt;= #{aVRangeDateStart}
        </if>
        <if test="insuranceDateRangeStart !=null">
            and v.insurance_end_date &lt;= #{insuranceDateRangeEnd}
        </if>
        <if test="insuranceDateRangeEnd !=null">
            and v.insurance_end_date &gt;= #{insuranceDateRangeStart}
        </if>
        <if test="insuranceCompany !=null">
            and v.insurance_company = #{insuranceCompany}
        </if>
        <if test="vin !=null and vin != ''">
            and v.vin = #{vin}
        </if>
        <if test="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 ">
            and (
            <trim suffixOverrides="OR">
                <if test="addrProvince !=null">
                    (bc.addr_province=#{addrProvince} or bc1.addr_province=#{addrProvince}) OR
                </if>
                <if test="addrCity !=null">
                    (bc.addr_city=#{addrProvince} or bc1.addr_city=#{addrProvince}) OR
                </if>
                <if test="zoneId !=null">
                    (bc.zone_id=#{zoneId} or bc1.zone_id=#{zoneId})  OR
                </if>
            </trim>
            )
        </if>
        order by v.code
        ) r ORDER BY r.subBranchName
    </select>

    <select id="lockByCode" resultType="com.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>

    <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="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="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>
    <select id="getAllVehicle" parameterType="java.util.Map" resultMap="searchModel">
        select *
        from vehicle v1
        <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="subordinateBranch != null">
                and v1.subordinate_branch = #{subordinateBranch}
            </if>
            <if test="parkBranchCompanyId != null">
                and v1.park_branch_company_id = #{parkBranchCompanyId}
            </if>
        </where>
    </select>
    <select id="countVehicleByParam" parameterType="com.xxfc.platform.vehicle.pojo.dto.VehiclePlanDto"
            resultType="com.xxfc.platform.vehicle.pojo.VehicleCountVo">
        SELECT count(*) total ,
        CASE
        WHEN travel_status = 1 THEN
        '1'
        WHEN travel_status = 2 THEN
        '2'
        WHEN travel_status = 3 THEN
        '3'
        WHEN travel_status = 4 THEN
        '4'
        ELSE
        '-1'
        END travel_status,
        CASE
        WHEN travel_status = 1 THEN
        '出行中'
        WHEN travel_status = 2 THEN
        '预约中'
        WHEN travel_status = 3 THEN
        '空闲中'
        WHEN travel_status = 4 THEN
        '保养中'
        ELSE
        '不可用'
        END detail
        from vehicle v1
        <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="subordinateBranch != null">
                and v1.subordinate_branch = #{subordinateBranch}
            </if>
            <if test="parkBranchCompanyId != null">
                and v1.park_branch_company_id = #{parkBranchCompanyId}
            </if>
        </where>
        GROUP BY travel_status
        union all
        SELECT count(*) total, '0' travel_status, '总数' detail from vehicle v2
        <where>
            <if test="startTime != null">
                and v2.create_time between #{startTime} and #{endTime}
            </if>
            <if test="numberPlate != null">
                and v2.number_plate = #{numberPlate}
            </if>
            <if test="status != null">
                and v2.status = #{status}
            </if>
            <if test="subordinateBranch != null">
                and v2.subordinate_branch = #{subordinateBranch}
            </if>
            <if test="parkBranchCompanyId != null">
                and v2.park_branch_company_id = #{parkBranchCompanyId}
            </if>
        </where>
        union all
        SELECT count(*) total,
        CASE
        WHEN status = 1 THEN
        '5'
        WHEN status = 2 THEN
        '6'
        ELSE
        '7'
        END travel_status,
        CASE
        WHEN status = 1 THEN
        '正常运行'
        WHEN status = 2 THEN
        '维修'
        ELSE
        '报废'
        END travel_status
        FROM vehicle v3
        <where>
            <if test="startTime != null">
                and v3.create_time between #{startTime} and #{endTime}
            </if>
            <if test="numberPlate != null">
                and v3.number_plate = #{numberPlate}
            </if>
            <if test="status != null">
                and v3.status = #{status}
            </if>
            <if test="subordinateBranch != null">
                and v3.subordinate_branch = #{subordinateBranch}
            </if>
            <if test="parkBranchCompanyId != null">
                and v3.park_branch_company_id = #{parkBranchCompanyId}
            </if>
        </where>
        GROUP BY status
    </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>
            <if test=" hotSign != null ">
                and vm.hot_sign = #{hotSign}
            </if>
        </where>
    </sql>

</mapper>