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

    <select id="getNo" resultType="String">
       SELECT TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval SENO FROM DUAL       
    </select>

    <insert id="addCouponRec" parameterType="Map">
       INSERT INTO td_coupon_rec(
			ID,USERID,ORDERID,SPID,STARTTIME,ENDTIME,STATUS,COUPONMONTH,INSERTTIME,COUPONID)
 		VALUES(#{id},#{userid},
 		#{orderid},#{spid},#{starttime},#{endtime},#{status},#{couponmonth},#{inserttime},#{couponid})
    </insert>

	<update id="upCouponRec2" parameterType="HashMap">
		UPDATE td_coupon_rec SET status = #{status}
		WHERE id = #{id} and userid =#{userid}  and COUPONMONTH = #{couponmonth}
	</update>

	<update id="upCouponRec3" parameterType="HashMap">
		UPDATE td_coupon_rec SET status = #{status}
		WHERE ID = #{ID} and COUPONMONTH = #{couponmonth} and userid = #{USERID}
	</update>

	<update id="verifyCoupon" parameterType="HashMap">
		UPDATE td_coupon_rec SET status = #{verifyres},verifymonth = #{verifymonth} ,verifyres = #{verifyres}
		WHERE id = #{id}
	</update>

    <select id="findOrderRela" resultType="HashMap">
		select distinct(t1.id) id,t2.id couponid,userid,t1.cpid cpid,t1.spid spid,to_char(ordertime,'yyyy-MM-dd HH24:mm:ss') ordertime,t1.status status,t1.endtime endtime,t2.isunique isunique
		from td_order_relations t1,(select * from tb_acproduct_config where status = '0' and coupontype = '0') t2
		where userid = #{userid} and  t1.spid = t2.spid
		and t1.spid in (select spid from tb_acproduct_config where status = '0' and starttime &lt;= sysdate and sysdate &lt;= endtime)
		and to_char(ordertime, 'yyyymm') &lt;= to_char(sysdate, 'yyyymm')
		order by ordertime desc
	</select>

	<select id="findCouponRec" resultType="HashMap">
		select t1.id id,t2.couponname,t1.spid,t2.spname,t2.amount,
		t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,t1.endtime couponendtime
		from td_coupon_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t1.userid = #{userid}
		and t1.orderid = #{orderid} and t1.couponmonth = #{couponmonth}
	</select>

	<select id="findCouponRec2" resultType="HashMap">
		select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
		t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,t1.endtime couponendtime,t1.couponmonth
		from td_coupon_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t2.status = '0'
		and t1.coupontype = t2.coupontype and t1.userid = #{userid}
		and t1.id = #{id}  order by t1.inserttime desc
	</select>

	<select id="getStatus" resultType="String">
		select status from td_coupon_rec where userid = #{userid} and spid = #{spid}
		and couponmonth = #{couponmonth}
		and inserttime >=  trunc(add_months(sysdate, -2), 'mm') order by inserttime desc
	</select>

	<select id="getStatus_cap" resultType="HashMap">
		select id,status from td_coupon_rec where userid = #{userid} and spid = #{spid}
		and couponmonth = #{couponmonth}
		and inserttime >=  trunc(add_months(sysdate, -2), 'mm') order by inserttime desc
	</select>

	<select id="getStatusById" resultType="String">
		select status from td_coupon_rec where id=#{id}
	</select>

<!--	<select id="getCouponRec" resultType="HashMap">-->
<!--    select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,-->
<!--    t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,t1.endtime couponendtime-->
<!--    from td_coupon_rec t1,tb_acproduct_config t2 where t1.spid = t2.spid and t1.userid = #{userid}-->
<!--		and to_date(t1.starttime,'yyyy-mm-dd') >=  trunc(add_months(sysdate, -2), 'mm') order by to_date(t1.starttime,'yyyy-mm-dd') desc-->
<!--	</select>-->
	<select id="getCouponRec" resultType="HashMap">
           select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
    t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,
    t1.endtime couponendtime,t2.coupontype,t1.couponmonth
    from td_coupon_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t1.userid = #{userid} and t2.coupontype = t1.coupontype
    and to_date(t1.starttime,'yyyy-mm-dd') >= (case when to_char(sysdate,'yyyymm')='202107' then  trunc(add_months(sysdate,0), 'mm')
              when to_char(sysdate,'yyyymm')='202108' then trunc(add_months(sysdate,-1), 'mm')
                else trunc(add_months(sysdate, -2), 'mm') end) order by to_date(t1.starttime,'yyyy-mm-dd') desc
	</select>

	<select id="getCouponRec2" resultType="HashMap">
    select t1.*
    from td_coupon_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id  and t1.userid = #{userid}
		and to_date(t1.starttime,'yyyy-mm-dd') >=  trunc(add_months(sysdate, -2), 'mm') order by to_date(t1.starttime,'yyyy-mm-dd') desc
	</select>

	<select id="findOrderLog" resultType="HashMap">
		select status,count(*) count from tl_order_log where userid  = #{userid} and spid = #{spid} and errorcode = '0'
		and to_char(inserttime,'yyyymm')=#{checkmonth} group by status
	</select>

	<select id="hasRec" resultType="HashMap">
		select status,count(*) count from td_coupon_rec where couponmonth = to_char(sysdate,'yyyymm') group by status
	</select>

	<select id ="getRecentStatus" resultType="HashMap">
		select * from (select status from tl_order_log where userid = #{userid} and spid = #{spid}
		and errorcode = '0' and to_char(inserttime,'yyyymm')&lt;#{couponmonth}
		order by inserttime desc) where rownum &lt;2
	</select>

	<select id ="getRecentStatus_Cap" resultType="HashMap">
		select * from (select status from tl_order_log where userid = #{userid} and spid = #{spid}
		and errorcode = '0' and to_char(inserttime,'yyyymm')&lt;#{couponmonth}
		and inserttime >= (select starttime from tb_acproduct_config where spid = #{spid}  and coupontype = '0' and status = '0')
		and inserttime &lt;= (select endtime from tb_acproduct_config  where spid = #{spid}  and coupontype = '0' and status = '0')
		order by inserttime desc) where rownum &lt;2
	</select>

	<select id="hasRec2" resultType="HashMap">
		select * from td_coupon_rec t1,(select id,spid,starttime,endtime from tb_acproduct_config where status = '0') t2
		where t1.couponid = t2.id and userid =#{userid}
		and t1.spid = #{spid} and to_date(t1.starttime, 'yyyy-mm-dd hh24:mi:ss') >= t2.starttime
		and to_date(t1.endtime, 'yyyy-mm-dd hh24:mi:ss') &lt;= t2.endtime
	</select>

	<select id="hasRec3" resultType="HashMap">
		select * from td_coupon_rec t1,(select id,spid,starttime,endtime from tb_acproduct_config where isunique = '2') t2
		where t1.couponid = t2.id and userid =#{userid}
		and t1.spid = #{spid} and to_date(t1.starttime, 'yyyy-mm-dd hh24:mi:ss') >= t2.starttime
		and to_date(t1.endtime, 'yyyy-mm-dd hh24:mi:ss') &lt;= t2.endtime
	</select>

	<select id="secondverify" resultType="HashMap">
		select status from tl_order_log a
		 where spid = #{spid}  and userid =#{userid}
		 and inserttime >=to_date(#{starttime}, 'yyyy-mm-dd hh24:mi:ss')
		 and inserttime &lt;= to_date(#{endtime}, 'yyyy-mm-dd hh24:mi:ss')
		 and errorcode = '0'
		order by inserttime desc
	</select>

	<select id="secondverify_timely" resultType="String">
		select min(to_char(inserttime,'yyyy-mm-dd hh24:mi:ss')) inserttime from tl_order_log a
		where spid = #{spid} and userid =#{userid} and status = '0' and errorcode = '0'
		and inserttime >= (select starttime from tb_acproduct_config where spid = #{spid}  and coupontype = '0' and status = '0')
		and inserttime &lt;= (select endtime from tb_acproduct_config  where spid = #{spid}  and coupontype = '0' and status = '0')
	</select>

	<select id="activetime" resultType="HashMap">
		select to_char(starttime,'yyyy-MM-dd') starttime,to_char(endtime,'yyyy-MM-dd HH24:mm:ss') endtime
		 from tb_acproduct_config where spid = #{spid} and coupontype = '0' and isunique = #{isunique} and status = '0'
	</select>

	<select id="capProductCheck" resultType="HashMap">
		select to_char(inserttime,'yyyy-mm-dd') inserttime ,update_type from td_cap_revsync_order where inserttime
		> (select starttime from tb_acproduct_config where spid = #{spid}) and mdn =#{userid}
		and sp_product_id in (select prmid from tb_acproduct_config where spid = #{spid}) and status = '0' and result = '处理成功'
		and staff_id in (select staffid from tb_coupon_staff) order by inserttime asc
	</select>

	<select id="meituanCheck" resultType="String">
		select coupontype from tb_acproduct_config  where spid = #{spid} and coupontype = '1'
		and to_date(#{starttime}, 'yyyy-mm-dd') >= starttime and to_date(#{starttime}, 'yyyy-mm-dd') &lt;=endtime
	</select>

	<insert id="addMeiTuan" parameterType="Map">
       INSERT INTO td_coupon_rec(
			ID,USERID,ORDERID,SPID,STARTTIME,ENDTIME,STATUS,COUPONMONTH,INSERTTIME,COUPONTYPE,COUPONID)
 		VALUES(#{id},#{userid},
 		#{orderid},#{spid},#{starttime},#{endtime},#{status},#{couponmonth},#{inserttime},'1',#{couponid})
    </insert>

	<select id="findOrderLogCap" resultType="HashMap">
		select status,count(*) count  from (
		select * from tl_order_log where userid = #{userid} and spid =#{spid} and errorcode = '0' --order by inserttime desc for update
		and channel in ('cap','capftp') and status = '0'
		and staff_id in (select staffid from tb_coupon_staff)
		and to_char(inserttime,'yyyymm')=#{checkmonth}

		union all

		select * from tl_order_log where userid = #{userid} and spid =#{spid} and errorcode = '0'
		and status = '1'
		and to_char(inserttime,'yyyymm')=#{checkmonth}

		)
		where
		inserttime >= (select starttime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
		and inserttime &lt;= (select endtime from tb_acproduct_config  where spid = #{spid}  and coupontype = '0')
		group by status
	</select>

	<select id="findOrderLogCapSpid" resultType="String">
    select count(*) count from tl_order_log where userid = #{userid} and spid =#{spid} and errorcode = '0'
    and status = '1'
    and  inserttime >= (select starttime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
    and to_char(inserttime,'yyyymm') &lt;=#{lastmonth}
	</select>

	<select id="findEarlyCancelMonth" resultType="String">
    select to_char(inserttime,'yyyymm') earlymonth from
    (select * from tl_order_log where userid = #{userid} and spid =#{spid} and errorcode = '0'
    and status = '1'
    and  inserttime >= (select starttime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
    order by inserttime asc
     ) where rownum=1
	</select>

	<select id="getTheLastOrder" resultType="String">
    select to_char(inserttime,'yyyy-mm-dd') lastmonth from
    (select * from tl_order_log where userid = #{userid} and spid =#{spid} and errorcode = '0'
    	and channel in ('cap','capftp') and status = '0'
		and staff_id in (select staffid from tb_coupon_staff)
    and  inserttime >= (select starttime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
    and inserttime &lt;= (select endtime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
    and to_char(inserttime,'yyyymm') = #{lastmonth}
    order by inserttime desc
     ) where rownum=1
	</select>

	<select id="getSqlSysDate" resultType="String">
    	select to_char(sysdate,'yyyymm') sqlsysdate from dual
	</select>

	<select id="getTheEarlyOrder" resultType="String">
    select to_char(inserttime,'yyyy-mm-dd') lastmonth from
    (select * from tl_order_log where userid = #{userid} and spid =#{spid} and errorcode = '0'
    	and channel in ('cap','capftp') and status = '0'
		and staff_id in (select staffid from tb_coupon_staff)
    and  inserttime >= (select starttime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
    and inserttime &lt;= (select endtime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
    order by inserttime asc
     ) where rownum=1
	</select>

	<select id="getTheEarlyOrderByMonth" resultType="String">
    select to_char(inserttime,'yyyy-mm-dd') lastmonth from
    (select * from tl_order_log where userid = #{userid} and spid =#{spid} and errorcode = '0'
    	and channel in ('cap','capftp') and status = '0'
		and staff_id in (select staffid from tb_coupon_staff)
    and  inserttime >= (select starttime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
    and inserttime &lt;= (select endtime from tb_acproduct_config where spid = #{spid} and coupontype = '0')
    and to_char(inserttime,'yyyymm') &lt;#{earlycancelmonth}
    order by inserttime asc
     ) where rownum=1
	</select>

	<select id="getTheEarlyOrderTime" resultType="String">
		select to_char(inserttime,'yyyy-mm-dd hh24:mi:ss') inserttime from (select * from tl_order_log where
		userid = #{userid} and spid =#{spid} and errorcode = '0'
		and channel in ('cap','capftp') and status = '0'
		and staff_id in (select staffid from tb_coupon_staff)
		and inserttime >= (select starttime from tb_acproduct_config where spid =#{spid} and coupontype = '0')
		and inserttime &lt;= (select endtime from tb_acproduct_config  where spid =#{spid}  and coupontype = '0')
		order by inserttime asc) where rownum = 1
	</select>

	<select id="getTheEarlyCancelTime" resultType="String">
		select to_char(inserttime,'yyyy-mm-dd hh24:mi:ss') inserttime from (select * from tl_order_log where
		userid = #{userid} and spid =#{spid}  and errorcode = '0'
		and status = '1'
		and inserttime >= (select starttime from tb_acproduct_config where spid =#{spid}  and coupontype = '0')
		and inserttime &lt;= (select endtime from tb_acproduct_config  where spid =#{spid}  and coupontype = '0')
		order by inserttime asc
		) where rownum = 1
	</select>

	<select id="getTheEarlyOrderTimeMaps" resultType="HashMap">
		select to_char(inserttime,'yyyymmdd') inserttime from tl_order_log where
		userid = #{userid} and spid =#{spid} and errorcode = '0'
		and channel in ('cap','capftp') and status = '0'
		and staff_id in (select staffid from tb_coupon_staff)
		and inserttime >= (select starttime from tb_acproduct_config where spid =#{spid} and coupontype = '0')
		and inserttime &lt;= (select endtime from tb_acproduct_config  where spid =#{spid}  and coupontype = '0')
		order by inserttime asc
	</select>

	<select id="getTheEarlyCancelTimeMaps" resultType="HashMap">
		select to_char(inserttime,'yyyymmdd') inserttime from tl_order_log where
		userid = #{userid} and spid =#{spid}  and errorcode = '0'
		and status = '1'
		and inserttime >= (select starttime from tb_acproduct_config where spid =#{spid}  and coupontype = '0')
		and inserttime &lt;= (select endtime from tb_acproduct_config  where spid =#{spid}  and coupontype = '0')
		order by inserttime asc
	</select>

	<select id="activeTimeCap" resultType="HashMap">
		select * from tb_acproduct_config where spid = #{spid} and  coupontype = '0' and
		to_char(starttime, 'yyyymm')&lt;=#{nowmonth} and  to_char(endtime, 'yyyymm')>=#{nowmonth}
	</select>

	<select id="getHasMonthCoupon" resultType="String">
			select couponmonth from td_coupon_rec where spid = #{spid} and userid = #{userid} group by couponmonth
	</select>

	<select id="isAssignChanneldata" resultType="HashMap">
		select id,userid,spid,to_char(ordertime,'yyyy-mm-dd') ordertime from td_order_relations  where id = #{orderid}
		  and  orderchannel2 in
		  (select channel from tb_acproduct_config where spid = #{spid} and status = '0' and channel is not null)
		  and  ordertime >=(select starttime from tb_acproduct_config where spid = #{spid} and status = '0' and orderstart is not null)
		  and  ordertime &lt;=(select endtime from tb_acproduct_config where spid = #{spid} and status = '0' and orderend is not null)
	</select>

	<select id="isAssignChanneldataByLog" resultType="HashMap">
		select to_char(inserttime,'yyyy-mm-dd') ordertime
		from (select * from tl_order_log where userid = #{userid} and  spid = #{spid}
      	and  errorcode = '0' and status = '0'
      	and  inserttime >=(select starttime from tb_acproduct_config where spid = #{spid} and status = '0' and orderstart is not null)
    	and  inserttime &lt;=(select endtime from tb_acproduct_config where spid = #{spid} and status = '0' and orderend is not null)
    	) where rownum = 1
	</select>

	<select id="getTimelyCoupon" resultType="HashMap">
			   select t1.id id,t1.spid spid, t1.cpid cpid,t2.id orderid from tb_acproduct_config t1,td_order_Relations t2
               where t1.spid = t2.spid and t1.isunique = '2' and t1.endtime >= trunc(add_months(sysdate,-2), 'mm') and userid=#{userid}
	</select>

	<select id="findCouponRec_new" resultType="HashMap">
    		select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
		t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,t1.endtime couponendtime,t1.couponmonth
		from td_coupon_new_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t2.status = '0'
		and t1.coupontype = t2.coupontype and t1.userid = #{userid}
		and t1.id = #{id}  order by t1.inserttime desc
	</select>

	<select id="findCouponRec_reflow" resultType="HashMap">
    		select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
		t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,t1.endtime couponendtime,t1.couponmonth
		from td_coupon_reflow_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t2.status = '0'
		and t1.coupontype = t2.coupontype and t1.userid = #{userid}
		and t1.id = #{id}  order by t1.inserttime desc
	</select>

	<select id="findCouponRec_telecom" resultType="HashMap">
    		select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
		t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,t1.endtime couponendtime,t1.couponmonth
		from td_coupon_telecom_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t2.status = '0'
		and t1.coupontype = t2.coupontype and t1.userid = #{userid}
		and t1.id = #{id}  order by t1.inserttime desc
	</select>

	<select id="findCouponRec_cancel" resultType="java.util.HashMap">
		select id,couponname,spid,spname,amount,status,to_char(inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,starttime couponstarttime,endtime couponendtime,couponmonth
		from (select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount, t1.status,t1.inserttime,t1.starttime ,t1.endtime ,t1.couponmonth
		from td_coupon_cancel_rec t1,tb_acproduct_config t2
			       where t1.couponid = t2.id and t2.status = '0'
					and t1.coupontype = t2.coupontype and t1.userid = #{userid}
					and t1.id = #{id}  order by t1.inserttime desc) where rownum &lt;= 1
	</select>

	<update id="verifyCoupon_new" parameterType="HashMap">
		UPDATE td_coupon_new_rec SET status = #{verifyres},verifymonth = #{verifymonth} ,verifyres = #{verifyres}
		WHERE id = #{id}
	</update>

	<update id="verifyCoupon_reflow" parameterType="HashMap">
		UPDATE td_coupon_reflow_rec SET status = #{verifyres},verifymonth = #{verifymonth} ,verifyres = #{verifyres}
		WHERE id = #{id}
	</update>

	<update id="verifyCoupon_telecom" parameterType="HashMap">
		UPDATE td_coupon_telecom_rec SET status = #{verifyres},verifymonth = #{verifymonth} ,verifyres = #{verifyres}
		WHERE id = #{id}
	</update>

	<update id="verifyCoupon_cancel" parameterType="java.util.HashMap">
		UPDATE td_coupon_cancel_rec SET status = #{verifyres},verifymonth = #{verifymonth} ,verifyres = #{verifyres},UPDATETIME = SYSDATE
		WHERE id = #{id}
	</update>

	<update id="upCouponRec_new" parameterType="HashMap">
		UPDATE td_coupon_new_rec SET status = #{status}
		WHERE ID = #{ID} and COUPONMONTH = #{couponmonth} and userid = #{USERID}
	</update>

	<update id="upCouponRec_reflow" parameterType="HashMap">
		UPDATE td_coupon_reflow_rec SET status = #{status}
		WHERE ID = #{ID} and COUPONMONTH = #{couponmonth} and userid = #{USERID}
	</update>

	<update id="upCouponRec_telecom" parameterType="HashMap">
		UPDATE td_coupon_telecom_rec SET status = #{status}
		WHERE ID = #{ID} and COUPONMONTH = #{couponmonth} and userid = #{USERID}
	</update>

	<update id="upCouponRec_cancel" parameterType="java.util.HashMap">
		UPDATE td_coupon_cancel_rec SET status = #{status} ,UPDATETIME = SYSDATE
		WHERE ID = #{ID} and COUPONMONTH = #{couponmonth} and userid = #{USERID}
	</update>


	<select id="getCouponRec_new" resultType="HashMap">
	select * from (
    select t1.*
    from td_coupon_new_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id  and t1.userid =#{userid}
    and to_date(t1.starttime,'yyyy-mm-dd') >=  trunc(add_months(sysdate, -2), 'mm')

    union all

    select t1.*
    from td_coupon_reflow_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id  and t1.userid =#{userid}
    and to_date(t1.starttime,'yyyy-mm-dd') >=  trunc(add_months(sysdate, -2), 'mm')

	union all


	select t1.id,t1.orderid,t1.userid,t1.spid,t1.starttime,t1.endtime,t1.status,t1.inserttime,t1.verifymonth,t1.verifyres,
		   t1.couponmonth,t1.coupontype,t1.couponid,t1.activitycode,t1.chsyncwaitid
	from td_coupon_cancel_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id  and t1.userid =#{userid}
	and to_date(t1.starttime,'yyyy-mm-dd') >=  trunc(add_months(sysdate, -2), 'mm')
	and t1.status &lt;&gt;'5'

    union all
        select t1.id,t1.orderid,t1.userid,t1.spid,t1.starttime,t1.endtime,t1.status,t1.inserttime,t1.verifymonth,t1.verifyres,
        t1.couponmonth,t1.coupontype,t1.couponid,t1.activitycode,t1.chsyncwaitid
    from td_coupon_telecom_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id  and t1.userid =#{userid}
    and t1.status &lt;&gt;'5'
    and to_date(t1.starttime,'yyyy-mm-dd') >=  trunc(add_months(sysdate, -2), 'mm')

	union all
	select t1.*
	from td_coupon_youtu_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id  and t1.userid =#{userid}
    and to_date(t1.starttime,'yyyy-mm-dd') >=  trunc(add_months(sysdate, -2), 'mm')

    )order by to_date(starttime,'yyyy-mm-dd') desc
	</select>

	<select id="getCouponRec_show" resultType="HashMap">
        select * from (
               select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
    t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,
    t1.endtime couponendtime,t2.coupontype,t1.couponmonth,t2.activitycode,t2.issplit,t2.splitrule
    from td_coupon_new_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t1.userid =  #{userid} and t2.coupontype = t1.coupontype
    and to_date(t1.starttime,'yyyy-mm-dd') >= trunc(add_months(sysdate, -2), 'mm')

                union all

                select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
    t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,
    t1.endtime couponendtime,t2.coupontype,t1.couponmonth,t2.activitycode,t2.issplit,t2.splitrule
    from td_coupon_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t1.userid = #{userid}  and t2.coupontype = t1.coupontype
    and to_date(t1.starttime,'yyyy-mm-dd') >= trunc(add_months(sysdate, -2), 'mm')

                    union all

                select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
    t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,
    t1.endtime couponendtime,t2.coupontype,t1.couponmonth,t2.activitycode,t2.issplit,t2.splitrule
    from td_coupon_reflow_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t1.userid = #{userid}  and t2.coupontype = t1.coupontype
    and to_date(t1.starttime,'yyyy-mm-dd') >= trunc(add_months(sysdate, -2), 'mm')

			   union all

			   select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
					  t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,
					  t1.endtime couponendtime,t2.coupontype,t1.couponmonth,t2.activitycode,t2.issplit,t2.splitrule
			   from td_coupon_cancel_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t1.userid = #{userid}  and t2.coupontype = t1.coupontype
				and t1.status &lt;&gt;'5'
				and to_date(t1.starttime,'yyyy-mm-dd') >= trunc(add_months(sysdate, -2), 'mm')

        union all
		select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
    t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,
    t1.endtime couponendtime,t2.coupontype,t1.couponmonth,t2.activitycode,t2.issplit,t2.splitrule
    from td_coupon_telecom_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id  and t1.userid =#{userid}
    and t1.status &lt;&gt;'5'
    and to_date(t1.starttime,'yyyy-mm-dd') >=  trunc(add_months(sysdate, -2), 'mm')

   union all
   select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
		  t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,
		  t1.endtime couponendtime,t2.coupontype,t1.couponmonth,t2.activitycode,t2.issplit,t2.splitrule
   from td_coupon_youtu_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t1.userid = #{userid}  and t2.coupontype = t1.coupontype
   and to_date(t1.starttime,'yyyy-mm-dd') >= trunc(add_months(sysdate, -2), 'mm')

    ) order by to_date(couponstarttime,'yyyy-mm-dd') desc
	</select>


	<select id="findCouponRec_youtu" resultType="HashMap">
		select t1.id,t2.couponname,t1.spid,t2.spname,t2.amount,
			   t1.status,to_char(t1.inserttime,'yyyy-mm-dd hh24:mi:ss') coupontime,t1.starttime couponstarttime,t1.endtime couponendtime,t1.couponmonth
		from td_coupon_youtu_rec t1,tb_acproduct_config t2 where t1.couponid = t2.id and t2.status = '0'
		 and t1.coupontype = t2.coupontype and t1.userid = #{userid}
		 and t1.id = #{id}  order by t1.inserttime desc
	</select>

	<update id="upCouponRec_youtu" parameterType="HashMap">
		UPDATE td_coupon_youtu_rec SET status = #{status}
		WHERE ID = #{ID} and COUPONMONTH = #{couponmonth} and userid = #{USERID}
	</update>

	<update id="verifyCoupon_youtu" parameterType="HashMap">
		UPDATE td_coupon_youtu_rec SET status = #{verifyres},verifymonth = #{verifymonth} ,verifyres = #{verifyres}
		WHERE id = #{id}
	</update>

</mapper>