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

    <select id="findSpInfo" parameterType="string" resultType="HashMap">
		select cpid, spid, spname, price, vacproductid, spcode, type, feetype, cancancelorder, canorder,
		errorhandle,duration,canaccumulation,mutex,relationSp,paytype,needUnifiedAuthSync,spcodename,haslocal 
		from tb_sp_info where spid=#{spid}
	</select>

    <select id="findOrderRelaAll" parameterType="string" resultType="HashMap">
			SELECT 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}
	</select>

    <select id="getVipstatus" parameterType="string" resultType="String">
		SELECT VIPSTATUS FROM TD_POINTS_ORDER_REC WHERE ID = #{orderId} 
	</select>

    <select id="getAreaInfo" parameterType="string" resultType="Map">
		select province, area, status from TB_CALLER_AREACODE where callerhead = #{callerhead}
    </select>

    <!--
    <select id="getPointShopConfig" parameterType="string"  resultType="Map" >
       SELECT
         ID, VIPPOOL, VIPTYPE, CPID, SPID, GOODSCODE, BUSITYPE
 		FROM TB_POINTS_GOODS_CONF where GOODSCODE = #{goodsCode} AND STATUS = '0'       
    </select>
    -->

    <select id="getPointShopConfig" parameterType="string" resultType="Map">
       SELECT CHANNEL,CPID,SPID,BUSITYPE,PWD,BUSINAME,VIPDAYS,NETDAYS,GOODSCODE
 		FROM TB_BACKBUSI_CONF WHERE GOODSCODE = #{goodscode} AND STATUS = '0'       
    </select>

    <select id="getPointShopConfigBySpid" parameterType="string" resultType="Map">
       SELECT *
 		FROM TB_BACKBUSI_CONF WHERE SPID = #{spid} AND STATUS = '0'       
    </select>

    <select id="getOrderRecByOrderNo" parameterType="string" resultType="Map">
       SELECT
			ID,	USERID,PROVINCE,AREA,CPID,SPID,ORDERNO,GOODSCODE,RESULTCODE,RESULTINFO,BUSITYPE,SERVICECODE,
  			TO_CHAR(INSERTTIME,'YYYYMMDDHH24MISS') INSERTTIME,TO_CHAR(FINISHTIME,'YYYYMMDDHH24MISS') FINISHTIME,
  			TO_CHAR(ORDERTIME,'YYYYMMDDHH24MISS') ORDERTIME, ORDERNO,REQUESTID,VIPSTATUS,APPID
 		FROM TD_POINTS_ORDER_REC WHERE ORDERNO = #{orderNo}        
    </select>

    <select id="getOrderRecById" parameterType="string"
            resultType="com.chinacreator.video.restful.bean.PointShopOrderBean">
       SELECT
			ID,	USERID,PROVINCE,AREA,CPID,SPID,ORDERNO,GOODSCODE,RESULTCODE,RESULTINFO,BUSITYPE,SERVICECODE,
  			TO_CHAR(INSERTTIME,'YYYYMMDDHH24MISS') INSERTTIME,TO_CHAR(FINISHTIME,'YYYYMMDDHH24MISS') FINISHTIME,
  			TO_CHAR(ORDERTIME,'YYYYMMDDHH24MISS') ORDERTIME, ORDERNO,REQUESTID,VIPSTATUS,APPID
 		FROM TD_POINTS_ORDER_REC WHERE ID = #{id}        
    </select>

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

    <select id="findByUserAndSpid" resultType="Map">
    			SELECT CPID, SPID, USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME, 
				TO_CHAR(ENDTIME, 'yyyymmddhh24miss') ENDTIME, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME
			FROM TD_ORDER_RELATIONS WHERE USERID=#{userid} and CPID=#{cpid} AND SPID=#{spid} 
    </select>

    <select id="currTime" resultType="String">
    	SELECT TO_CHAR(SYSDATE,'yyyymmddhh24miss') FROM DUAL
    </select>

    <select id="curr31Day" resultType="String">
		SELECT TO_CHAR(30+TRUNC(SYSDATE)+1-1/86400,'yyyymmddhh24miss') FROM DUAL
	</select>

    <select id="endtime31Day" parameterType="string" resultType="String">
		SELECT TO_CHAR(31+TO_DATE(#{endtime},'yyyymmddhh24miss'),'yyyymmddhh24miss')  FROM DUAL
	</select>

    <select id="currParamDay" parameterType="string" resultType="String">
		<![CDATA[



                                SELECT TO_CHAR(#{days}+TRUNC(SYSDATE)-1/86400,'yyyymmddhh24miss') FROM DUAL



        ]]>
	</select>

    <select id="endtimeParamDay" parameterType="string" resultType="String">
        <![CDATA[



                                SELECT TO_CHAR(#{days}+TO_DATE(#{endtime},'yyyymmddhh24miss'),'yyyymmddhh24miss')  FROM DUAL



        ]]>
	</select>

    <select id="getStartTime" parameterType="string" resultType="String">
        <![CDATA[



                                SELECT TO_CHAR(TO_DATE(#{endtime},'YYYYMMDDHH24MISS')-#{netdays}+2/(24*60*60),'YYYYMMDDHH24MISS') FROM DUAL



        ]]>
	</select>

    <select id="endtimeTheMonthLastDay" resultType="java.lang.String">

    <![CDATA[



                            SELECT TO_CHAR(TRUNC(LAST_DAY(sysdate),'dd')+1-1/24/60/60,'yyyymmddhh24miss')  FROM DUAL



        ]]>
    </select>
    <select id="endtimeNextMonthLastDay" resultType="java.lang.String">
	<![CDATA[



                                select to_char(last_day(add_months(trunc(TO_DATE(#{currEndtime}, 'yyyymmddhh24miss')),1)+1-1/24/60/60),'yyyymmddhh24miss') from dual



        ]]>
    </select>
    <select id="getKsStartTime" resultType="java.lang.String">
        <![CDATA[

                      SELECT TO_CHAR(TRUNC(SYSDATE), 'YYYYMMDDHH24MISS') FROM DUAL


        ]]>
    </select>
    <select id="getBackVipstatus" resultType="java.lang.String">
     SELECT VIPSTATUS FROM TD_BACKBUSI_ORDER_REC WHERE ID = #{id}
    </select>


    <select id="getBackBusiConf" resultType="java.util.HashMap">
     SELECT * FROM TB_BACKBUSI_CONF WHERE STATUS = '0' AND CPID = #{cpid} AND SPID = #{spid}
    </select>
    <select id="getOrderRec" resultType="com.chinacreator.video.restful.bean.PointShopOrderBean">
        
          SELECT
            ID, USERID,PROVINCE,AREA,CPID,SPID,ORDERNO,GOODSCODE,RESULTCODE,RESULTINFO,BUSITYPE,SERVICECODE,
  			 TO_CHAR(INSERTTIME,'YYYYMMDDHH24MISS') INSERTTIME,TO_CHAR(FINISHTIME,'YYYYMMDDHH24MISS') FINISHTIME, 
  			 TO_CHAR(ORDERTIME,'YYYYMMDDHH24MISS') ORDERTIME,REQUESTID,VIPSTATUS,APPID 
 		     FROM TD_POINTS_ORDER_REC WHERE ID = #{id}
    </select>


    <update id="updOrderRec" parameterType="com.chinacreator.video.restful.bean.PointShopOrderBean">
		UPDATE TD_POINTS_ORDER_REC SET FINISHTIME = SYSDATE, RESULTCODE = #{resultCode}, RESULTINFO = #{resultInfo}
		WHERE ID = #{id}
	</update>

    <update id="updCallBack">
		UPDATE TD_POINTS_ORDER_REC SET BCSTATUS = #{0}, BCINFO = #{1} WHERE ID = #{2}
	</update>
    <update id="updBusiStatus" parameterType="com.chinacreator.video.restful.bean.PointShopOrderBean">

     UPDATE TD_BACKBUSI_ORDER_REC SET VIPSTATUS = #{vipstatus} , RESULTCODE = #{resultCode}, RESULTINFO = #{resultInfo} WHERE ID = #{id}

    </update>

    <update id="updPointStatus"  parameterType="com.chinacreator.video.restful.bean.PointShopOrderBean">

     UPDATE TD_POINTS_ORDER_REC SET VIPSTATUS = #{vipstatus} , RESULTCODE = #{resultCode}, RESULTINFO = #{resultInfo} WHERE ID = #{id}

    </update>
    <update id="updBackOrderRec">
     UPDATE TD_BACKBUSI_ORDER_REC SET VIPSTATUS = #{vipstatus} , RESULTCODE = #{resultCode}, RESULTINFO = #{resultInfo} WHERE ID = #{id}
    </update>
    <update id="updParamsByCb" parameterType="string">
UPDATE TL_POINTS_INOUT_PARAMS SET CBREQPARAMS = #{cbReqParams}, CBRESPARAMS = #{cbRspParams}, CBTIME = SYSDATE WHERE REQUESTID = #{requestId}
    </update>

    <insert id="addOrderRec" parameterType="com.chinacreator.video.restful.bean.PointShopOrderBean">
       INSERT INTO TD_POINTS_ORDER_REC(
			ID,	USERID,PROVINCE,AREA,CPID,SPID,ORDERNO,RESULTCODE,RESULTINFO,BUSITYPE,SERVICECODE,
  			INSERTTIME,ORDERTIME,REQUESTID,VIPSTATUS,APPID,GOODSCODE)
 		VALUES(#{id},#{userid},#{province},#{area},#{cpid},#{spid},#{orderNo},
 		#{resultCode},#{resultInfo},#{busiType},#{serviceCode},
 		SYSDATE,TO_DATE(#{orderTime},'YYYYMMDDHH24MISS'),#{requestId},#{vipstatus},#{appId},#{goodsCode})
    </insert>

    <insert id="addBackOrderRec" parameterType="com.chinacreator.video.restful.bean.PointShopOrderBean">
			MERGE INTO TD_BACKBUSI_ORDER_REC a USING (SELECT #{id} ID FROM DUAL) b
			ON(a.ID = b.ID)
			WHEN MATCHED THEN UPDATE
			SET VIPSTATUS = #{vipstatus},RESULTCODE = #{resultCode},RESULTINFO = #{resultInfo}
			WHEN NOT MATCHED THEN
			INSERT (ID, USERID, ORDERID, CPID, SPID, PROVINCE, AREA, VIPSTATUS, BUSITYPE, RESULTCODE, RESULTINFO, CHANNEL,TYPE,ENDTIME,STARTTIME)
			VALUES (
			   #{id}, #{userid}, #{orderNo}, #{cpid}, #{spid}, #{province}, #{area}, #{vipstatus}, #{busiType}, #{resultCode}, #{resultInfo}, #{appId},'0', TO_DATE(#{endtime},'yyyymmddhh24miss'), TO_DATE(#{starttime},'yyyymmddhh24miss')
			)
    </insert>

    <insert id="addOrderRal" parameterType="com.chinacreator.video.restful.bean.OrderInfo">
			MERGE INTO TD_ORDER_RELATIONS a USING (SELECT #[cpid] cpid, #[spid] spid, #[userid] userid FROM DUAL) b
			ON(a.cpid = b.cpid and a.spid = b.spid and a.userid = b.userid)
			WHEN MATCHED THEN UPDATE 
			SET status = '0',ENDTIME=TO_DATE(#[endtime],'yyyymmddhh24miss'),ORDERTIME=TO_DATE(#[ordertime],'yyyymmddhh24miss'), effecttime=TO_DATE(#[effecttime], 'yyyymmddhh24miss'),
			CDNSUCCEED = '1', SYNCSUCCEED = '1',CDNCOUNT = '0', SYNCCOUNT = '0', CACHESUCCEED = '1'
			WHEN NOT MATCHED THEN INSERT(ID,CPID,SPID,USERID,ORDERTIME,EFFECTTIME,ENDTIME,STATUS,PROVINCE,AREA,ORDERCHANNEL,SYNCCOUNT,SYNCSUCCEED,TYPE,ORDERSTATUS,ORDERTYPE,
			APPTYPE,ISEXPERIENCE,VIDEOID,VIDEONAME,CHACHESUCCEED,CDNSUCCEED,SHOWSTATUS) VALUES (
			TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,
			#[cpid],#[spid],#[userid],TO_DATE(#[ordertime], 'yyyymmddhh24miss'),TO_DATE(#[effecttime], 'yyyymmddhh24miss'),
			TO_DATE(#[endtime], 'yyyymmddhh24miss'),#[status],#[province],#[area],#[orderchannel],0,1,#[type],#[orderstatus],#[ordertype],#[apptype],#[isexperience],#[videoid],
			#[videoname],1,'1', #[showstatus])
    </insert>

    <insert id="addOrderLog" parameterType="com.chinacreator.video.restful.bean.OrderLog">
			INSERT INTO TL_ORDER_LOG (ID, USERID, PROVINCE, AREA,
			CHANNEL, CPID, SPID, ERRORCODE, ERRORINFO, STATUS,ORDERSTATUS, ORDERTYPE, INSERTTIME) VALUES (
			TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval,
			#{userid}, #{province}, #{area}, #{channel}, #{cpid},
			#{spid}, #{errorcode}, #{errorinfo}, #{status}, #{orderstatus}, #{ordertype},
			sysdate)
    </insert>

    <insert id="addOrderParamsLog" parameterType="Map">
       INSERT INTO TL_POINTS_INOUT_PARAMS(
			ID,	REQUESTID,INPARAMS,OUTPARAMS,INSERTTIME,ENINPARAMS,DNOUTPARAMS )
 		VALUES(TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#{REQUESTID},#{INPARAMS},#{OUTPARAMS},SYSDATE,#{ENINPARAMS},#{DNOUTPARAMS})
    </insert>

    <insert id="order"  parameterType="com.chinacreator.video.restful.bean.PointShopOrderBean">
     MERGE INTO TD_ORDER_RELATIONS a USING (SELECT #{cpid} cpid, #{spid} spid, #{userid} userid FROM DUAL) b
				 	ON(a.cpid = b.cpid and a.spid = b.spid and a.userid = b.userid) 
				 	WHEN MATCHED THEN UPDATE  
				 	SET status = '0',ENDTIME=TO_DATE(#{endtime},'yyyymmddhh24miss'),ORDERTIME=SYSDATE, effecttime=SYSDATE, 
				 	CDNSUCCEED = '1', SYNCSUCCEED = '1',CDNCOUNT = '0', SYNCCOUNT = '0', CACHESUCCEED = '1', ORDERCHANNEL = #{appId}
				 	WHEN NOT MATCHED THEN INSERT(ID,CPID,SPID,USERID,ORDERTIME,EFFECTTIME,ENDTIME,STATUS,PROVINCE,AREA,ORDERCHANNEL,SYNCCOUNT,SYNCSUCCEED,TYPE,ORDERSTATUS,ORDERTYPE, 
				 	ISEXPERIENCE,CACHESUCCEED,CDNSUCCEED,CDNCOUNT) VALUES ( 
				 	TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, 
				 	#{cpid},#{spid},#{userid},SYSDATE,SYSDATE, 
				 	TO_DATE(#{endtime}, 'yyyymmddhh24miss'),'0',#{province},#{area},#{appId},0,1,1,'3','1',0,1,'1','0')
    </insert>

</mapper>