<?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.CkuMyMatchMapper" >
  <resultMap id="BaseResultMap" type="com.cku.model.CkuMyMatchModel" >
    <result column="starting_data" property="starting_data" jdbcType="TIMESTAMP" />
    <result column="ending_data" property="ending_data" jdbcType="TIMESTAMP" />
    <result column="show_location" property="show_location" jdbcType="VARCHAR" />
    <result column="show_num" property="show_num" jdbcType="VARCHAR" />
    <result column="main_game_name" property="main_game_name" jdbcType="VARCHAR" />
    <result column="image_min_url" property="image_min_url" jdbcType="VARCHAR" />
    <result column="image_max_url" property="image_max_url" jdbcType="VARCHAR" />
    <result column="time_tab_url" property="time_tab_url" jdbcType="VARCHAR" />
    <result column="gradeMonth" property="gradeMonth" jdbcType="VARCHAR" />
  </resultMap>
  <resultMap id="BaseResultMapForDog" type="com.cku.model.CkuMatchDogModel" >
  </resultMap>
  <select id="getMyMatchByLoginUser" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    SELECT
		a.*,
		image.image_min_url,
		image.image_max_url,
		images.image_max_url time_tab_url
	FROM
		(
			SELECT
				min(开始日期) starting_data,
				max(结束日期) ending_data,
				left(convert(varchar(100),max(结束日期),23),7) charTime,
				max(LEFT(赛事编号, 6)) show_num,
				赛事地点 show_location,
				max(main_game_name) main_game_name,
				left(CONVERT(varchar(100), max(结束日期), 23),7) gradeMonth
			FROM
				tab_games
			GROUP BY
				赛事地点
		) a
	RIGHT JOIN (
		SELECT
			show_num
		FROM
			(
				SELECT
					LEFT (赛事编号, 6) show_num
				FROM
					cku_shows_baoming
				WHERE
					会员号 = #{idNum}
			) tab
		GROUP BY
			show_num
	) b ON a.show_num = b.show_num LEFT JOIN cku_image image on a.show_location = image.remarks 
	LEFT JOIN cku_image images on a.show_location = images.showLocation
  	where DATEDIFF ([second], ending_data, getDate())
	<if test="flag=='There'">
	<![CDATA[<]]> 0
	</if>
	<if test="flag=='History'">
	<![CDATA[>]]> 0
		<if test="hrstime!='' and hrstime!=null">
		and  charTime = #{hrstime}
		</if>
	</if>
  </select>
  <!-- 查询历史赛事中最近存在数据的日期 zhuoHeng -->
  <select id="getMyMatchMonth" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    SELECT
		TOP 1 LEFT (
			CONVERT (VARCHAR (100),MAX (结束日期),23),7) gradeMonth
	FROM
		tab_games
	WHERE
		LEFT (赛事编号, 6) = (
			SELECT
				TOP 1 LEFT (a.赛事编号, 6) show_num
			FROM
				tab_games a
			RIGHT JOIN cku_shows_baoming b ON a.赛事编号 = b.赛事编号
			WHERE
				b.会员号 = #{idNum}
			AND DATEDIFF([second],a.结束日期,getDate()) <![CDATA[>]]> 0
			ORDER BY
				b.id DESC
		)
  </select>
  <select id="getMatchDogByShowNum" resultMap="BaseResultMapForDog" parameterType="java.lang.Long" >
    SELECT 
		LEFT (赛事编号, 6) show_num,
		CKU血统证书号 pedigree_num,
		payout
	FROM
		cku_shows_baoming
	WHERE
		会员号 = #{idNum} and 赛事编号 like '%${showNum}%'
  </select>
</mapper>