<?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.ReturnActiDao" >

	<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_DATE(#{startTime},'yyyyMMdd hh24:mi:ss') &lt;= ENDTIME
		 or ENDTIME IS NULL) and ORDERTIME &lt;=TO_DATE(#{endTime},'yyyyMMdd hh24:mi:ss')
        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>
    <select id="findByPhoneKs" resultType="java.util.Map">
        SELECT
        CPID,SPID,TO_CHAR(ORDERTIME,'yyyyMMddhhmmss') as ORDERTIME,USERID
        FROM TD_ORDER_RELATIONS
        where ordertime &gt;=to_date('20210401','yyyyMMdd') and ordertime &lt;=to_date('20210430235959','yyyyMMddHH24miss')
        and  (endtime is null or  endtime>=to_date('20210501','yyyyMMdd'))
        and  USERID=#{userid} and SPID=#{spid} AND CPID=#{cpid}
    </select>
    <select id="findByPhoneBilibl" resultType="java.util.Map">
        SELECT
        CPID,SPID,TO_CHAR(ORDERTIME,'yyyyMMddhhmmss') as ORDERTIME,USERID
        FROM TD_ORDER_RELATIONS
        where ordertime &gt;=to_date('20210501','yyyyMMdd') and ordertime &lt;=to_date('20210630235959','yyyyMMddHH24miss')
        and  (endtime is null or  endtime>=trunc(add_months(ordertime,1),'mm'))
        and  USERID=#{userid} 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>