<?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.EventRelatedMapper">
	<resultMap id="BaseResultMap" type="com.cku.model.EventRelatedModel">
		<id column="id" jdbcType="VARCHAR" property="id" />
		<result column="starting_data" jdbcType="VARCHAR" property="starting_data" />
		<result column="ending_data" jdbcType="VARCHAR" property="ending_data" />
		<result column="show_name" jdbcType="VARCHAR" property="show_name" />
		<result column="show_location" jdbcType="VARCHAR" property="show_location" />
		<result column="main_game_name" jdbcType="VARCHAR" property="main_game_name" />
		<result column="top_dog" jdbcType="VARCHAR" property="top_dog" />
		<result column="expiry_date" jdbcType="VARCHAR" property="expiry_date" />
		<result column="shows_detail" jdbcType="VARCHAR" property="shows_detail" />
		<result column="show_num" jdbcType="VARCHAR" property="show_num" />
		<result column="expiry_time" jdbcType="VARCHAR" property="expiry_time" />
		<result column="organizer" jdbcType="VARCHAR" property="organizer" />
		<result column="cku_caipanid" property="cku_caipanid" jdbcType="VARCHAR" />
		<result column="bmFei" property="bmFei" jdbcType="DECIMAL" />
		<result column="showSystem" property="showSystem" jdbcType="VARCHAR" />
		<result column="gameType" property="gameType" jdbcType="VARCHAR" />
		<result column="isTop"  property="isTop" jdbcType="INTEGER"/>
		<result column="topMessage" property="topMessage" jdbcType="VARCHAR"/>
		<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"/>
			<result column="remarks" property="remarks" jdbcType="VARCHAR" />
		    <result column="dog_class_id" property="dogClassId" jdbcType="INTEGER" />
		    <result column="match_id" property="matchId" jdbcType="INTEGER" />
		    <result column="match_hotel_id" property="matchHotelId" jdbcType="INTEGER" />
		    <result column="match_referee_id" property="matchRefereeId" jdbcType="INTEGER" />
		    <result column="match_venue_id" property="matchVenueId" jdbcType="INTEGER" />
		    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
		    <result column="showLocation_isTop" property="showLocationIsTop" jdbcType="INTEGER"/>
		</collection>
	</resultMap>
	<resultMap id="ResultMap" type="com.cku.model.CkuMatch" >
	    <result column="id" property="id" jdbcType="INTEGER" />
	    <result column="showSystem" property="showsystem" jdbcType="CHAR" />
	    <result column="show_num" property="showNum" jdbcType="VARCHAR" />
	    <result column="starting_data" property="startingData" jdbcType="TIMESTAMP" />
	    <result column="ending_data" property="endingData" jdbcType="TIMESTAMP" />
	    <result column="show_name" property="showName" jdbcType="VARCHAR" />
	    <result column="show_location" property="showLocation" jdbcType="VARCHAR" />
	    <result column="show_enrolment" property="showEnrolment" jdbcType="VARCHAR" />
	    <result column="add_data" property="addData" jdbcType="TIMESTAMP" />
	    <result column="modifi_data" property="modifiData" jdbcType="TIMESTAMP" />
	    <result column="bmFei" property="bmfei" jdbcType="DECIMAL" />
	    <result column="del_tag" property="delTag" jdbcType="CHAR" />
	    <result column="cku_endtime" property="ckuEndtime" jdbcType="TIMESTAMP" />
	    <result column="cku_zhuantiweb" property="ckuZhuantiweb" jdbcType="VARCHAR" />
	    <result column="cku_picture1" property="ckuPicture1" jdbcType="VARCHAR" />
	    <result column="cku_picture2" property="ckuPicture2" jdbcType="VARCHAR" />
	    <result column="cku_caipanid" property="ckuCaipanid" jdbcType="VARCHAR" />
	    <result column="cku_picture3" property="ckuPicture3" jdbcType="VARCHAR" />
	    <result column="cku_tel" property="ckuTel" jdbcType="VARCHAR" />
	    <result column="cku_tableweb" property="ckuTableweb" jdbcType="VARCHAR" />
	    <result column="cku_slzweb" property="ckuSlzweb" jdbcType="VARCHAR" />
	    <result column="cku_gjdlweb" property="ckuGjdlweb" jdbcType="VARCHAR" />
	    <result column="cku_jfweb" property="ckuJfweb" jdbcType="VARCHAR" />
	    <result column="privileges_enrolment" property="privilegesEnrolment" jdbcType="VARCHAR" />
	    <result column="gameType" property="gametype" jdbcType="CHAR" />
	    <result column="organizer" property="organizer" jdbcType="CHAR" />
	    <result column="showCancel" property="showcancel" jdbcType="CHAR" />
	    <result column="venue_id" property="venueId" jdbcType="INTEGER" />
	    <result column="cost_id" property="costId" jdbcType="INTEGER" />
		<result column="isTop"  property="isTop" jdbcType="INTEGER"/>
		<result column="topMessage" property="topMessage" jdbcType="VARCHAR"/>
	  </resultMap>
	  <resultMap id="BaseResult" type="com.cku.model.CkuMatchSponsor">
		<id column="id" jdbcType="INTEGER" property="id" />
		<result column="city" jdbcType="VARCHAR" property="city" />
		<result column="sponsor" jdbcType="VARCHAR" property="sponsor" />
		<result column="direct_line" jdbcType="VARCHAR" property="direct_line" />
		<result column="direct_line2" jdbcType="VARCHAR" property="direct_line2" />
		<result column="contact" jdbcType="VARCHAR" property="contact" />
		<result column="phone_number" jdbcType="VARCHAR" property="phone_number" />
		<result column="work_time" jdbcType="VARCHAR" property="work_time" />
		<result column="supervise_phone" jdbcType="VARCHAR" property="supervise_phone" />
		<collection property="ckuSponsoList" javaType="list" ofType="com.cku.model.CkuSponsorContacts">
			<result property="name" column="name"/>
			<result property="phone" column="phone"/>
		</collection>
	</resultMap>
	<!--SQL SERVER 根据页码、展示条数查询主赛事列表 zhuoHeng-->
	<select id="getEventList" resultMap="BaseResultMap" parameterType="java.lang.Long">
	SELECT
		top ${limit} 
		image.image_min_url,
		image.image_max_url,
		tab.*
	FROM
		(
		SELECT
			赛事地点  show_location,
			main_game_name,
			shows_detail,
			max(top_dog) top_dog,
			min(id) id,
			min(赛事编号) show_num,
			min(organizer) organizer,
			min(cku_caipanid) cku_caipanid,
			MIN(开始日期) AS starting_data,
			MAX(结束日期) AS ending_data,
			MAX(cku_endtime) AS expiry_time
		FROM
			tab_games
		GROUP BY
			赛事地点,main_game_name,shows_detail
		) tab LEFT JOIN cku_image image on tab.show_location = image.remarks
	WHERE
		tab.id NOT IN (
			SELECT
				top ${page} min(id) id
			FROM
				tab_games
			GROUP BY
				赛事地点
			ORDER BY
				id DESC
		)
	AND  datediff(day,expiry_time,getdate())<![CDATA[<]]> 0
	ORDER BY
		id DESC
	</select>
	<select id="getEventCount"  resultMap="BaseResultMap" parameterType="java.lang.Long">
		SELECT
		image.image_min_url,
		image.image_max_url,
		tab.*
	FROM
		(
		SELECT
			赛事地点  show_location,
			main_game_name,
			shows_detail,
			max(top_dog) top_dog,
			min(id) id,
			min(赛事编号) show_num,
			min(organizer) organizer,
			min(cku_caipanid) cku_caipanid,
			MIN(开始日期) AS starting_data,
			MAX(结束日期) AS ending_data,
			MAX(cku_endtime) AS expiry_time
		FROM
			tab_games
		GROUP BY
			赛事地点,main_game_name,shows_detail
		) tab LEFT JOIN cku_image image on tab.show_location = image.remarks
	WHERE datediff(day,expiry_time,getdate())<![CDATA[<]]> 0
	ORDER BY
		id DESC
	</select>
	<!--SQL SERVER 根据赛主赛事场地查询主赛事信息 zhuoHeng -->
	<select id="getTrunkEventInfo" resultMap="BaseResultMap" parameterType="java.lang.Long">
	SELECT
		tab.*
	FROM
		(
			SELECT
				min(id) id,
				赛事地点 show_location,
				shows_detail,
				MIN(开始日期) AS starting_data,
				MAX(结束日期) AS ending_data,
				MAX(cku_endtime) AS expiry_time,
				main_game_name,
				shows_detail,
				max(top_dog) top_dog,
				min(赛事编号) show_num
			FROM
				tab_games
			GROUP BY
				赛事地点,
				main_game_name,
				shows_detail
		) tab
	WHERE tab.show_location =  #{showLocation}
	</select>
	<!--SQL SERVER 根据主赛事场地查询子赛事列表 zhuoHeng-->
	<select id="getSpecificEvent" resultMap="BaseResultMap" parameterType="java.lang.Long">
		SELECT
			id,
			开始日期 starting_data,
			结束日期 ending_data,
			赛事名称 show_name,
			赛事地点 show_location,
			赛事编号 show_num,
			bmFei
		FROM
			tab_games
		where 赛事地点 = #{showLocation}
		<if test="ids!='' and ids!=null">
			and id in (${ids})
		</if>
	</select>
	
	<!--SQL SERVER 根据主办方获取主办方详细信息 zhuoHeng-->
	<select id="getSponsorInfo" resultMap="BaseResult" parameterType="java.lang.Long">
		SELECT
			game.*, cku. NAME,
			cku.phone
		FROM
			cku_game_sponsor game
		LEFT JOIN cku_sponsor_contacts cku ON game.id = cku.sponsor_id
		WHERE city =  #{organizer}
	</select>
	
	<!--SQL SERVER 根据赛事编号查询赛事信息 zhuoHeng-->
	<select id="getSpecificEventByShowNum" resultMap="BaseResultMap" parameterType="java.lang.Long">
		SELECT
			id,
			开始日期 starting_data,
			结束日期 ending_data,
			赛事名称 show_name,
			赛事地点 show_location,
			赛事编号 show_num,
			showSystem showSystem,
			bmFei,
			main_game_name,
			gameType,
			organizer
		FROM
			tab_games
		where 赛事编号 = #{showNum}
	</select>
	<select id="getSpecificEventByShowNums" resultMap="BaseResultMap" parameterType="java.lang.Long">
		SELECT
			min(开始日期) starting_data
		FROM
			tab_games
		where 赛事编号  in (${showNums})
	</select>
	<!-- 修改赛事为是否推荐赛事 -->
	<update id="updateMatchToTop" parameterType="java.lang.Integer">
			update show_main
	    <set >
	      <if test="isTop == 0" >
	        isTop = 0,
	      </if>
	      <if test="isTop == 1">
	      	isTop = 1,
	      </if>
	     </set>
	      where show_location =#{showLocation}
	</update>
	<!-- 查询赛事为推荐赛事 -->
	<select id="getTopMatch" resultMap="BaseResultMap" parameterType="java.lang.Long">
		SELECT
	TOP ${limit} image.*, tab.*
FROM
	(
		SELECT
			赛事地点 show_location,
			main_game_name,
			shows_detail,
			MAX (top_dog) top_dog,
			MIN (id) id,
			MIN (赛事编号) show_num,
			MIN (organizer) organizer,
			MIN (cku_caipanid) cku_caipanid,
			MIN (开始日期) AS starting_data,
			MAX (结束日期) AS ending_data,
			MAX (cku_endtime) AS expiry_time,
			MIN (bmFei) AS bmFei,
			MIN (赛事名称) AS show_name,
			MIN (showSystem) AS showSystem,
			MIN (gameType) AS gameType,
			MIN (isTop) AS isTop,
			MIN(topMessage) AS topMessage
		FROM
			tab_games
		GROUP BY
			赛事地点,
			main_game_name,
			shows_detail
	) tab
LEFT JOIN cku_image image ON tab.show_location = image.remarks
WHERE
	tab.id NOT IN (
		SELECT
			TOP ${start} MIN (id) id
		FROM
			tab_games
		GROUP BY
			赛事地点
		ORDER BY
			id DESC
	)
AND tab.isTop = '1' 
AND  datediff(day,expiry_time,getdate())<![CDATA[<]]> 0
ORDER BY
	id DESC
	</select>
	<!-- 查询赛事地点 -->
	<select id="getMatchByAdress" resultType="java.lang.String">
	SELECT show_location 赛事地点  from show_main where id=#{id}
	</select>
	
	<select id="selectById" resultType="com.cku.model.EventRelatedModel">
	select id,赛事地点 as show_location,topMessage from tab_games   where id=#{id}
	</select>
	<select id="getMatchImage" resultType="java.lang.String">
	select image.image_max_url from tab_games tab,cku_image image where image.showLocation_isTop=tab.id and tab.id=#{id}
	</select>
</mapper>