<?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.cku.dao.CkuMatchVenueMapper" >
  <resultMap id="BaseResultMap" type="com.cku.model.CkuMatchVenue" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="venue_name" property="venueName" jdbcType="VARCHAR" />
    <result column="venue_longitude" property="venueLongitude" jdbcType="VARCHAR" />
    <result column="venue_latitude" property="venueLatitude" jdbcType="VARCHAR" />
    <result column="venue_address" property="venueAddress" jdbcType="VARCHAR" />
    <result column="venue_phone" property="venuePhone" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <collection property="ckuImageList" ofType="com.cku.model.CkuImage" column="matchVenueId" >
    	<id column="image_id" property="imageId" />
    	<result column="image_min_url" property="imageMinUrl" />
    	<result column="image_max_url" property="imageMaxUrl" />
    </collection>
  </resultMap>
  <sql id="Base_Column_List" >
    id, venue_name, venue_longitude, venue_latitude, venue_address, venue_phone, create_time
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
	    SELECT
		venue.*, image.*
	FROM
		cku_match_venue venue
	LEFT JOIN
		cku_image image
	ON venue.id = image.match_venue_id
	WHERE venue.id = #{id,jdbcType=INTEGER}
  </select>
  <!-- 查询场馆信息 不包含图片 -->
  <select id="selectVenueById" resultMap="BaseResultMap">
  select 
  	<include refid="Base_Column_List" />
   FROM cku_match_venue where id=#{id}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from cku_match_venue
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.cku.model.CkuMatchVenue" >
    insert into cku_match_venue (id, venue_name, venue_longitude, 
      venue_latitude, venue_address, venue_phone, 
      create_time)
    values (#{id,jdbcType=INTEGER}, #{venueName,jdbcType=VARCHAR}, #{venueLongitude,jdbcType=VARCHAR}, 
      #{venueLatitude,jdbcType=VARCHAR}, #{venueAddress,jdbcType=VARCHAR}, #{venuePhone,jdbcType=VARCHAR}, 
      #{createTime,jdbcType=TIMESTAMP})
  </insert>
  <insert id="insertSelective" parameterType="com.cku.model.CkuMatchVenue" >
    insert into cku_match_venue
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="venueName != null" >
        venue_name,
      </if>
      <if test="venueLongitude != null" >
        venue_longitude,
      </if>
      <if test="venueLatitude != null" >
        venue_latitude,
      </if>
      <if test="venueAddress != null" >
        venue_address,
      </if>
      <if test="venuePhone != null" >
        venue_phone,
      </if>
      <if test="createTime != null" >
        create_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="venueName != null" >
        #{venueName,jdbcType=VARCHAR},
      </if>
      <if test="venueLongitude != null" >
        #{venueLongitude,jdbcType=VARCHAR},
      </if>
      <if test="venueLatitude != null" >
        #{venueLatitude,jdbcType=VARCHAR},
      </if>
      <if test="venueAddress != null" >
        #{venueAddress,jdbcType=VARCHAR},
      </if>
      <if test="venuePhone != null" >
        #{venuePhone,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.cku.model.CkuMatchVenue" >
    update cku_match_venue
    <set >
      <if test="venueName != null" >
        venue_name = #{venueName,jdbcType=VARCHAR},
      </if>
      <if test="venueLongitude != null" >
        venue_longitude = #{venueLongitude,jdbcType=VARCHAR},
      </if>
      <if test="venueLatitude != null" >
        venue_latitude = #{venueLatitude,jdbcType=VARCHAR},
      </if>
      <if test="venueAddress != null" >
        venue_address = #{venueAddress,jdbcType=VARCHAR},
      </if>
      <if test="venuePhone != null" >
        venue_phone = #{venuePhone,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.cku.model.CkuMatchVenue" >
    update cku_match_venue
    set venue_name = #{venueName,jdbcType=VARCHAR},
      venue_longitude = #{venueLongitude,jdbcType=VARCHAR},
      venue_latitude = #{venueLatitude,jdbcType=VARCHAR},
      venue_address = #{venueAddress,jdbcType=VARCHAR},
      venue_phone = #{venuePhone,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>
  	<!--********************************************************* 自定义********************************************* -->
      <resultMap id="WithImageBaseResultMap" type="com.cku.model.CkuMatchVenue" extends="BaseResultMap" >
		<collection property="ckuImageList" javaType="list" ofType="com.cku.model.CkuImage">
			<id property="imageId" column="image_id"/>
			<result property="imageMinUrl" column="image_min_url"/>
			<result property="imageMaxUrl" column="image_max_url"/>
		</collection>
  </resultMap>
  <!-- SQL SERVER -->
    <select id="getVenue" resultMap="WithImageBaseResultMap" parameterType="java.lang.Integer" >
<!-- 		SELECT
			mv.*,
			ci.image_min_url,
			ci.image_max_url
		FROM
			cku_match_venue mv
		LEFT JOIN cku_image ci ON mv.id = ci.match_venue_id
		WHERE
			mv.show_location = #{showLocation} -->
		SELECT
			mv.*,
			ci.image_min_url,
			ci.image_max_url
		FROM
			 cku_match_venue mv left join tab_games tg on tg.venue_id = mv.id
		LEFT JOIN cku_image ci ON mv.id = ci.match_venue_id
		WHERE
			tg.赛事地点 = #{showLocation}
	  </select>
    <select id="getList" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
		SELECT * FROM cku_match_venue  
		where 1=1 
		<if test="name != '' and  name != null" >
			and venue_name like '%${name}%'
	    </if>
	    <if test="address != '' and  address != null" >
			and venue_address like '%${address}%'
	    </if>
		ORDER BY ID DESC  limit ${count},${limit}
	  </select>
  <select id="getCount" resultType="java.lang.Long" parameterType="java.lang.Integer" >
    SELECT
		count(*)
	FROM cku_match_venue 
	WHERE 1=1 
	<if test="name != '' and  name != null" >
		and venue_name like '%${name}%'
    </if>
    <if test="address != '' and  address != null" >
		and venue_address like '%${address}%'
    </if>
  </select>
</mapper>