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

	<!--
   <select id="getTranIpLimitConf" parameterType="string"  resultType="Map" >
       SELECT CHANNEL, CPID, SPID, IP, BUSITYPE FROM TB_UNIFIEDLIMIT_CONFIG
               WHERE STATUS = '0' AND BUSITYPE ='0' AND CHANNEL = #{channel} ORDER BY CHANNEL,CPID,SPID
    </select>

    <select id="getQryIpLimitConf" parameterType="string"  resultType="Map" >
       SELECT CHANNEL, CPID, SPID, IP, BUSITYPE FROM TB_UNIFIEDLIMIT_CONFIG
               WHERE STATUS = '0' AND BUSITYPE ='1' AND CHANNEL = #{channel} ORDER BY CHANNEL,CPID,SPID
    </select>
    -->
	<select id="getIpLimitConf" parameterType="string"  resultType="Map" >
    	SELECT CHANNEL, CPID, SPID, IP, BUSITYPE, INVOKEFACE  FROM TB_UNIFIEDLIMIT_CONFIG
				WHERE STATUS = '0' AND BUSITYPE = #{busitype} AND CHANNEL = #{channel} 
				ORDER BY CPID DESC,SPID DESC,INVOKEFACE DESC
 	</select>
 	
 	<select id="getIpLimitConfByInvokeface" parameterType="string"  resultType="Map" >
 	    SELECT CHANNEL, CPID, SPID, IP, BUSITYPE, INVOKEFACE  FROM TB_UNIFIEDLIMIT_CONFIG
                WHERE STATUS = '0' AND BUSITYPE =  #{busitype} AND CHANNEL = #{channel} 
                AND (INVOKEFACE = #{invokeface} OR INVOKEFACE IS NULL)
                ORDER BY CPID DESC,SPID DESC,INVOKEFACE 
	</select>

	<select id="getCpSpInfo" resultType="Map" >
       SELECT
        b.cpid, b.spid,netpwd,vacproductid,spcode,b.type,mutex,mobtype,mutextype
	   FROM  TB_CP_ACCOUNT_CONFIG a, tb_sp_info b where a.cpid=b.cpid
    </select>

	<select id="getlimitQryConf" resultType="Map" >
    SELECT CHANNEL,HOURLIMIT,DAYLIMIT,MONTHLIMIT,YEARLIMIT,TOTALLIMIT
 		FROM TB_UNIFIEDQRY_CONFIG WHERE STATUS = '0'
  	</select>
  	
  	<select id="getlimitQryConfByChannel" resultType="Map" parameterType="string">
    SELECT CHANNEL,HOURLIMIT,DAYLIMIT,MONTHLIMIT,YEARLIMIT,TOTALLIMIT,INTERFACENAME
 		FROM TB_UNIFIEDQRY_CONFIG WHERE STATUS = '0' AND CHANNEL = #{channel} ORDER BY INTERFACENAME 
  	</select>

	<select id="getAllOrder" parameterType="string"  resultType="Map">
    SELECT NVL(A.SPNAME,'NULL') SPNAME, NVL(A.PRICE,'0') PRICE,B.* FROM TB_SP_INFO A,
	(SELECT
	        USERID, CPID, SPID, TO_CHAR(ORDERTIME,'YYYYMMDDHH24MISS') ORDERTIME,
	        (CASE WHEN CANCELTIME IS NULL THEN 'NULL'  ELSE TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') END) ENDTIME,
	        (CASE WHEN CANCELTIME IS NULL THEN 'NULL'  ELSE TO_CHAR(CANCELTIME,'YYYYMMDDHH24MISS') END) CANCELTIME,
	         (CASE WHEN CANCELTIME IS NULL THEN '0' WHEN ENDTIME >= SYSDATE THEN '1' ELSE '2' END) STATUS, ORDERCHANNEL,
	         NVL(CANCELCHANNEL,'NULL') CANCELCHANNEL
	         FROM TD_ORDER_RELATIONS WHERE USERID =  #{userid} ORDER BY ORDERTIME DESC
	) B WHERE A.SPID(+) = B.SPID
    </select>

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

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

	<select id="findOrderRela" parameterType="string"  resultType="HashMap" >
			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 CPID = #{cpid} and SPID = #{spid}
	</select>

	<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="generateId" resultType="String" >
       SELECT
         TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval ID
 		FROM DUAL
    </select>

	<insert id="addOrderRal" parameterType="com.chinacreator.video.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 ordertime=to_date(#{ordertime}, 'yyyymmddhh24miss'),
			effecttime=to_date(#{effecttime}, 'yyyymmddhh24miss'),
			canceltime='',
			endtime=to_date(#{endtime,jdbcType = VARCHAR}, 'yyyymmddhh24miss'),
			status=#{status},synccount=0,syncsucceed='1',lastsynctime='',issms='1',
			smstime='',orderchannel=#{orderchannel,jdbcType=VARCHAR},channel=#{channel,jdbcType=VARCHAR},
			type=#{type},orderstatus=#{orderstatus},
			ordertype=#{ordertype},apptype=#{apptype,jdbcType=VARCHAR},isexperience=#{isexperience},
			videoid=#{videoid,jdbcType=VARCHAR},videoname=#{videoname,jdbcType=VARCHAR},
			cachesucceed=#{cachesucceed},cdnsucceed='1',showstatus=#{showstatus}
			when not matched then insert (id,cpid,spid,userid,ordertime,effecttime,endtime,status,province,area,channel,orderchannel,synccount,syncsucceed,type,orderstatus,ordertype,apptype,isexperience,videoid,videoname,cachesucceed,cdnsucceed, showstatus)values(
			CASE WHEN #{id} IS NULL THEN to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval ELSE #{id} END ,
			#{cpid},#{spid},#{userid},to_date(#{ordertime}, 'yyyymmddhh24miss'),to_date(#{effecttime}, 'yyyymmddhh24miss'),
			to_date(#{endtime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'),
			#{status},#{province,jdbcType=VARCHAR},#{area,jdbcType=VARCHAR},#{channel,jdbcType=VARCHAR},#{orderchannel,jdbcType=VARCHAR},0,1,#{type},#{orderstatus},#{ordertype},#{apptype,jdbcType=VARCHAR},#{isexperience},#{videoid,jdbcType=VARCHAR},#{videoname,jdbcType=VARCHAR},1,'1', #{showstatus})

    </insert>

	<insert id="addCancelRal" parameterType="com.chinacreator.video.bean.OrderInfo">
    		merge into TD_ORDER_RELATIONS a using (select #{cpid} cpid, #{spid} spid, #{userid} userid from dual) b
			on (a.userid = b.userid and a.cpid = b.cpid and a.spid = b.spid and a.status in ('0', '1'))
			when matched then update set
			canceltime=to_date(#{canceltime}, 'yyyymmddhh24miss'),
			endtime=to_date(#{endtime}, 'yyyymmddhh24miss'),
			status='1', synccount=0,syncsucceed='1',lastsynctime='',issms='1',smstime='', cancelchannel=#{cancelchannel}, channel=#{channel,jdbcType=VARCHAR}, orderstatus=#{orderstatus}, type=#{type},cachesucceed=1,cdnsucceed='1'
			when not matched then insert (id, cpid,spid,userid,ordertime,effecttime,canceltime,endtime,status,province,area,channel,cancelchannel,synccount,syncsucceed,type,orderstatus,ordertype,cachesucceed,cdnsucceed) values (
			CASE WHEN #{id} IS NULL THEN to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval ELSE #{id} END ,
			#{cpid}, #{spid}, #{userid},
			to_date(#{ordertime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'),to_date(#{effecttime}, 'yyyymmddhh24miss'),
			to_date(#{canceltime}, 'yyyymmddhh24miss'),to_date(#{endtime}, 'yyyymmddhh24miss'),
			'1', #{province,jdbcType=VARCHAR}, #{area,jdbcType=VARCHAR}, #{channel,jdbcType=VARCHAR}, #{cancelchannel,jdbcType=VARCHAR}, 0, 1, #{type}, #{orderstatus}, #{ordertype},1,'1')
    </insert>

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

	<insert id="addRelidLog" parameterType="Map">
			INSERT INTO TL_ORDERID_RELID_LOG (ID, RELID, ORDERNO, CHANNEL, STATUS, INSERTTIME) VALUES (
			TO_CHAR(SYSDATE, 'yyyymmddhh24miss')||SEQ_COMMON6.nextval,#{RELID}, #{ORDERNO}, #{CHANNEL}, #{STATUS}, SYSDATE)
    </insert>

	<insert id="addOrderRelidRec" parameterType="Map">
    		MERGE INTO TD_ORDERID_RELID_REC a USING (SELECT #{RELID} RELID FROM DUAL) b
			ON(a.RELID = b.RELID)
			WHEN MATCHED THEN UPDATE
			SET ORDERCHANNEL= #{ORDERCHANNEL,jdbcType=VARCHAR}, ORDERID = #{ORDERID,jdbcType=VARCHAR}
			WHEN NOT MATCHED THEN INSERT(RELID, ORDERID, ORDERCHANNEL) VALUES (
			#{RELID}, #{ORDERID,jdbcType=VARCHAR}, #{ORDERCHANNEL,jdbcType=VARCHAR})
    </insert>

	<insert id="addCancelRelidRec" parameterType="Map">
    		MERGE INTO TD_ORDERID_RELID_REC a USING (SELECT #{RELID} RELID FROM DUAL) b
			ON(a.RELID = b.RELID)
			WHEN MATCHED THEN UPDATE
			SET CANCELCHANNEL = #{CANCELCHANNEL,jdbcType=VARCHAR}, CANCELID = #{CANCELID,jdbcType=VARCHAR}
			WHEN NOT MATCHED THEN INSERT(RELID, CANCELID,CANCELCHANNEL) VALUES (
			#{RELID}, #{CANCELID,jdbcType=VARCHAR}, #{CANCELCHANNEL,jdbcType=VARCHAR})
    </insert>

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

	<select id="getEndDayOfCurrentMonth" resultType="String">
    select to_char(trunc(last_day(sysdate))+1-1/86400, 'yyyymmddhh24miss') ENDTIME from dual
    </select>
    
    <select id="getEndDayOfOpertime" resultType="String" parameterType="String">
    SELECT TO_CHAR(TRUNC(LAST_DAY(TO_DATE(#{opertime},'YYYYMMDDHH24MISS')))+1-1/86400, 'yyyymmddhh24miss') ENDTIME FROM DUAL
    </select>
    
    <select id="findNmaOrderRela" parameterType="string"  resultType="Map" >
    SELECT NVL(A.SPNAME,'NULL') SPNAME, NVL(A.PRICE,'0') PRICE,B.* FROM TB_SP_INFO A,
    (SELECT
            USERID, CPID, SPID, TO_CHAR(ORDERTIME,'YYYYMMDDHH24MISS') ORDERTIME,
            TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME,
            TO_CHAR(CANCELTIME,'YYYYMMDDHH24MISS') CANCELTIME,
             (CASE WHEN CANCELTIME IS NULL THEN '0' WHEN ENDTIME >= SYSDATE THEN '1' ELSE '2' END) STATUS,
             ORDERCHANNEL, SYNCSTATUS, CANCELCHANNEL
             FROM TD_NMA_ORDER_RELATIONS WHERE USERID =  #{userid} ORDER BY ORDERTIME DESC
    ) B WHERE A.SPID(+) = B.SPID
	</select>
	
	<select id="qryChannelSpidLimitConf" parameterType="string" resultType="HashMap">
    	SELECT ID, CHANNEL, CPID, SPID, PERIODTIME, TO_CHAR(UPPERCOUNT) UPPERCOUNT, PROVINCEAREA, INVOKEFACE 
    	 FROM TB_CHANNEL_SPIDLIMIT_CONF WHERE STATUS = '0' AND CHANNEL = #{channel} AND CPID = #{cpid} AND SPID = #{spid} 
    	 AND ( INVOKEFACE = #{invokeface} OR INVOKEFACE = '*' )
    </select>
    
	<select id="qryUnifychannelSpidLimitConf" parameterType="string" resultType="HashMap">
    	SELECT ID, CHANNEL, CPID, SPID, PERIODTIME, TO_CHAR(UPPERCOUNT) UPPERCOUNT, PROVINCEAREA, INVOKEFACE 
    	 FROM TB_CHANNEL_SPIDLIMIT_CONF WHERE STATUS = '0' 
    	 AND ( CHANNEL = #{channel} OR CHANNEL = #{unifychannel} )
    	 AND CPID = #{cpid} AND SPID = #{spid} 
    	 AND ( INVOKEFACE = #{invokeface} OR INVOKEFACE = '*' )
    </select>
    
    <select id="qryEffectiveRela" parameterType="string"  resultType="HashMap" >
			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 ( ENDTIME IS NULL OR ENDTIME >= SYSDATE )
	</select>
	
	<select id="qryProvInvokeLimit" parameterType="string" resultType="HashMap">
		SELECT INVOKEFACE, PROVINCE, CHANNEL, TYPE FROM TB_PROVINCE_INVOKELIMIT_CONF WHERE PROJNAME = 'univideo' AND STATUS = '0' AND INVOKEFACE = #{invokeface}
			AND ( CHANNEL = #{channel} OR CHANNEL = '*'  OR CHANNEL IS NULL ) AND ( TYPE =  #{type} OR TYPE = '*' )
			AND ( PROVINCE =  #{province} OR PROVINCE = '*' ) 
	</select>    
</mapper>