<?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.restful.dao.HsQueryVipDao">

	<select id="findActivityConfig" parameterType="string" resultType="HashMap">
         select  activetype,t1.spid,t2.spname from tb_activity_config t1,tb_sp_info t2
         where t1.spid = t2.spid and status = '0' and sysdate between begintime and endtime
	</select>

	<select id="findBakBusiConfig" parameterType="string" resultType="HashMap">
		select vipid,t1.spid,t2.spname  from TB_BACKBUSI_CONF t1,tb_sp_info t2 where t1.spid  = t2.spid and status = '0'
	</select>

	<select id="getSendvip_conf" parameterType="string" resultType="HashMap">
    	select * from tb_order_sendvip_conf where spid = #{spid} and status = '0'
    	and ((channel = '*' or (subchannel is null or subchannel = #{subchannel} ))  or (channel = #{channel} and (subchannel is null or subchannel= #{subchannel})))
	</select>

	<select id="getJoinspid_conf" resultType="HashMap">
    	select * from tb_vip_joinspid_conf where status = '0' and spid = #{spid}
    	and ((hassubchannel = '0' and channel = #{subchannel}) or (hassubchannel = '1' and (channel = #{channel}) ))
	</select>

		<!--
		select a.userid,b.spname,a.spid,a.inserttime vipTime,'' activityType,'产品订购' usertypeName,
			   '续送' status,
			   b.spname || '续送' typeName,a.vipreultcode resultCode,a.viperrorinfo errorInfo,a.userid userval,fhspid from TL_CONTINUEORDER_LOG a, tb_sp_info  b
		where a.spid = b.spid and a.userid=#{userid}
		  and a.inserttime >= trunc(add_months(sysdate, -2), 'mm')
		  and a.fhspid is null
		-->
	<select id="getContinueVip" parameterType="string" resultType="com.chinacreator.video.restful.bean.HsQueryVipBean">

		select a.userid,(select spname from tb_sp_info c where c.spid=nvl(a.fhspid,a.spid)) spname ,a.spid,a.inserttime vipTime,'' activityType,'产品订购' usertypeName, '续送' status,
			   case when a.spid='954' then '乐视月会员' when a.spid='99133542' then '风行月会员' else (select  g.viptypename  from tb_vip_product_config g  ,tb_activity_vip_config e,tb_activity_config f where g.vipchannelid=e.vippool_continue    and e.spid=a.spid  and e.activetype=f.activetype
				and f.spid=nvl(a.fhspid,a.spid) and e.status='0' and f.status='0' )  end typeName,a.vipreultcode resultCode,a.viperrorinfo errorInfo,a.userid userval
		from TL_CONTINUEORDER_LOG a, tb_sp_info  b
		where nvl(a.fhspid,a.spid) = b.spid and a.userid= #{userid}
		  and a.inserttime >= trunc(add_months(sysdate, -2), 'mm')

	</select>

	<select id="findUserChannelData" resultType="HashMap">
    	select orderchannel,orderchannel2,to_char(ordertime,'yyyymmddhh24miss') ORDERTIME from td_order_relations where userid =#{userid} and spid = #{spid}
	</select>

	<select id="getJoinspid_conf_new" resultType="java.util.HashMap">
		select * from tb_vip_joinspid_conf where status = '0' and spid = #{spid} and cpid != 'taysxy'
		and ((hassubchannel = '0' and channel = #{subchannel}) or (hassubchannel = '1' and (channel = #{channel}) or channel = '*'))
	</select>

	<select id="getSendvip_conf_new" resultType="java.util.HashMap">
		select cpid,spid,channel,status,remark,starttime,endtime,sendtype,subchannel
		from tb_order_sendvip_conf
		where spid = #{spid}
		  and status = '0'
		  and (channel = '*' or
			   (channel = #{channel} and
				(subchannel is null or subchannel = #{subchannel})))

	</select>
</mapper>
