<?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.chinacreator.video.mapper.TenDrawDao" >

    <select id="getUseridById" parameterType="string" resultType="string">
		SELECT USERID FROM TD_TENDRAW_USERIDMAPP WHERE ID = #{id} AND CEIL((SYSDATE - INSERTTIME) * 24*60) &lt;= 5
	</select>
	
	<select id="getUser" parameterType="string" resultType="string">
		SELECT USERID FROM TD_TENDRAW_USERIDMAPP WHERE ID = #{id}
	</select>  
	
    <insert id="addUserMapp" parameterType="string">
       INSERT INTO TD_TENDRAW_USERIDMAPP( ID,USERID ) VALUES(#{id},#{userid})
    </insert> 
    
    <insert id="addPrize" parameterType="Map">
       INSERT INTO TD_TENDRAW_PRIZELIST(
			ID,USERID,PRIZENAME,PRIZETIME, MAPPID)
 		VALUES(TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#{USERID},#{PRIZENAME},TO_DATE(#{PRIZETIME},'YYYYMMDDHH24MISS'),#{MAPPID})
    </insert>

	<select id="getNo" resultType="String" >
       SELECT TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval SENO FROM DUAL       
    </select>
    
    <select id="getPrizeList" parameterType="string" resultType="Map">
		SELECT * FROM (SELECT SUBSTR(USERID,1,3)||'****'||SUBSTR(USERID,8) "userid",PRIZENAME "prizename" ,TO_CHAR(PRIZETIME, 'YYYYMMDDHH24MISS') "prizetime"FROM TD_TENDRAW_PRIZELIST ORDER BY PRIZETIME DESC ) WHERE ROWNUM &lt;= 50  
	</select> 
	
	  <select id="getOrderRel" parameterType="string" resultType="Map">
	  	SELECT ID, CPID,SPID,USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME, 
			TO_CHAR(ENDTIME, 'yyyymmddhh24miss') ENDTIME, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME,
			(CASE WHEN ENDTIME IS NULL THEN '0' WHEN ENDTIME >= SYSDATE THEN '1' ELSE '2' END) STATUS
			FROM TD_ORDER_RELATIONS WHERE USERID = #{userid} AND SPID = '101' 
	</select>

	<select id="findByPhone" resultType="java.util.Map" parameterType="java.lang.String">
		SELECT
		CPID,SPID,TO_CHAR(ORDERTIME,'yyyyMMddhhmmss') as ORDERTIME,USERID
		FROM TD_ORDER_RELATIONS
		WHERE to_number(to_char(ORDERTIME,'yyyymm')) &lt; to_number(to_char(sysdate,'yyyymm')) and ( ENDTIME >= SYSDATE or endtime is null)
	    and  USERID=#{userid} and SPID=#{spid} AND CPID=#{cpid}
</select>

	<select id="findOrderCoode" resultType="java.util.Map" parameterType="java.lang.String">
		SELECT
		(CASE WHEN ENDTIME IS NULL	THEN '0'
		when  ENDTIME >= SYSDATE  THEN '1'
		ELSE '2'
		END)  CODE
		FROM TD_ORDER_RELATIONS
		WHERE  USERID=#{userid}
	    and SPID=#{spid}  AND CPID=#{cpid} ORDER BY ENDTIME DESC
	</select>

	<select id="findByUser" resultType="java.util.Map">
		SELECT
		USERID
		FROM TB_ACTIVITY_QUA
		WHERE USERID=#{userid}  AND STATUS='0'  AND SPID =#{spid} AND CPID=#{cpid}
	</select>

	<insert id="insertActivity" parameterType="java.util.Map">
		INSERT  INTO TB_ACTIVITY_QUA(USERID,CPID,SPID,STATUS) VALUES(#{USERID},#{CPID},#{SPID},#{STATUS})
	</insert>
</mapper>
