<?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.VehicleModelMapper">
    <select id="findVehicleModelPage" parameterType="com.xxfc.platform.vehicle.pojo.VehicleModelQueryCondition"
            resultType="com.xxfc.platform.vehicle.pojo.VehicleModelVo">
        SELECT * from
        (select vm.*,v.sum,v2.leasableQuantity,vpc.`name` brandName,vpc2.name numberName from vehicle_model vm
        left JOIN
        (select model_id as mid, count(1) as sum FROM vehicle where is_del= 0 GROUP BY mid) v
        on vm.id=v.mid
        left JOIN
        (select model_id as mid2, count(1) as leasableQuantity FROM vehicle where`use_type`=1 and is_del= 0 GROUP BY model_id) v2
        on v.mid=v2.mid2
        left join (SELECT id,name FROM vehicle_plat_cata where state=0) vpc on vm.brand=vpc.id
        left join (SELECT id,name FROM vehicle_plat_cata where state=0) vpc2 on vm.number=vpc2.id
        where vm.isdel = 0
        ) vmqc WHERE 1=1

        <if test="isDel !=null ">
            and isdel=#{isDel}
        </if>


        <if test="status != null">
            and status = #{status}
        </if>


        <if test="vehicleName !=null and vehicleName !=''">
            AND vmqc.name LIKE CONCAT('%',#{vehicleName},'%')
        </if>

        <if test="vehicleType!=null and vehicleType!=''">
            AND EXISTS (SELECT vc.vehicle_model_id FROM vehicle_cata vc WHERE vc.cata_id=#{vehicleType} AND
            vmqc.id=vc.vehicle_model_id )
        </if>

        <if test="takeALieTheNumber!=null and takeALieTheNumber!=''">
            AND EXISTS(SELECT vc2.vehicle_model_id FROM vehicle_cata vc2 WHERE vc2.cata_id=#{takeALieTheNumber} AND
            vmqc.id=vc2.vehicle_model_id)
        </if>

        <if test="catas != null">
            AND ( 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},vmqc.config) > 0 then 1 else 0 end)
                </foreach>
                )
            </foreach>
            ) > 0
        </if>
        <if test="notInIds != null">
            and vmqc.id not in (
            <foreach collection="notInIds.split(',')" item="noIdItem" index="noIdIndex">
                #{noIdItem}
            </foreach>
            )
        </if>
        ORDER BY vmqc.sort ASC
    </select>

    <update id="updateScoreByModelIdAndavgScore">
        update `vehicle_model` set `score`=(`score`+#{avgScore})/2 where `id`=#{modelId}
    </update>
</mapper>