<?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 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 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  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>
      ORDER BY	vmqc.id ASC
  </select>

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