<?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.WoInnovateDao">
    <select id="findBySpInfoTable"  resultType="java.lang.String">
        select vacproductid from TB_SP_INFO where vacproductid  is not null and CANORDER = 0 and spid != '1009'
    </select>

    <select id="checkStatus"  resultType="java.lang.Integer" parameterType="java.lang.String">
        select count(1) from TD_ORDER_RELATIONS where spid = (select spid from tb_sp_info where vacproductid = #{skuId} and spid != '1009') and userid = #{userid}
    </select>

    <select id="findStatus"  resultType="java.lang.String" parameterType="java.lang.String">
        select status from TD_ORDER_RELATIONS where spid = (select spid from tb_sp_info where vacproductid = #{skuId} and spid != '1009') and userid = #{userid}
    </select>

    <select id="findSpidBySkuId"  resultType="java.util.Map" parameterType="java.lang.String">
        select spid,cpid from tb_sp_info where vacproductid = #{vacproductid} and spid != '1009'
    </select>
    
    <insert id="add" parameterType="com.chinacreator.video.bean.OrderRelationsBean">
        INSERT INTO TD_ORDER_RELATIONS(ID,CPID,SPID,USERID,ORDERTIME,STATUS,PROVINCE,AREA,ORDERCHANNEL,ORDERSTATUS,CHANNEL
    )
    VALUES(TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#{cpid},#{spid},#{userid},TO_DATE(#{ordertime},'yyyy-MM-dd hh24:mi:ss'),#{status},#{province},#{area},#{orderchannel},#{orderstatus},#{channel})
    </insert>

    <insert id="saveLog" parameterType="com.chinacreator.video.bean.LogBean">
        INSERT INTO TL_WOINNOVATE_LOG(ID,SERIESNUMBER,ORDERID,BUSINESSNOTICETYPE,TIMESTAMP,NOTIFYURL,SKUID,GOODSNAME,STARTDATE,ORDERTYPE,ISFIRSTORDER,MOBILE
    )
    VALUES(TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#{seriesNumber,jdbcType=VARCHAR},#{orderId,jdbcType=VARCHAR},#{businessNoticeType,jdbcType=VARCHAR},#{timeStamp,jdbcType=VARCHAR},#{notifyUrl,jdbcType=VARCHAR},#{skuId,jdbcType=VARCHAR},#{goodsName,jdbcType=VARCHAR},TO_DATE(#{startDate},'yyyy-MM-dd hh24:mi:ss'),#{orderType,jdbcType=VARCHAR},#{isFirstOrder,jdbcType=VARCHAR},#{mobile,jdbcType=VARCHAR})
    </insert>

    <select id="findRelationCount"  resultType="java.lang.Integer" parameterType="java.lang.String">
        select count(1) from TD_ORDER_RELATIONS where userid = #{userid} and spid = #{spid}
    </select>
    <update id="updateRelation" parameterType="com.chinacreator.video.bean.OrderRelationsBean">
        update TD_ORDER_RELATIONS set ORDERTIME = TO_DATE(#{ordertime},'yyyy-MM-dd hh24:mi:ss'),canceltime = '' ,status = '0',endtime = '',synccount = 0,syncsucceed = '1',lastsynctime = '',issms=1,
        smstime='',ORDERCHANNEL=#{orderchannel},TYPE = 0,cdnsucceed = '1' where userid = #{userid} and spid = #{spid}
    </update>

    <insert id="addBusshandle" parameterType="com.chinacreator.video.bean.OrderRelationsBean">
        INSERT INTO TD_BUSSHANDLE_WAIT(ID,USERID,PROVINCE,AREA,CPID,SPID,STATUS,ORDERTIME,ORDERCHANNEL,SRCFLAG)
        VALUES (TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#{userid},#{province},#{area},#{cpid},#{spid},#{status},TO_CHAR(TO_DATE(#{ordertime},'yyyy-MM-dd hh24:mi:ss'),'yyyyMMddhh24miss'),#{orderchannel},'0')
    </insert>
</mapper>