<?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.muchinfo.mtp3interface.mapper.IMarketMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.Market">
        <id column="MARKETID" property="marketid" />
        <result column="MARKETNAME" property="marketname" />
        <result column="EXCHAREAID" property="exchareaid" />
        <result column="MARKETTYPE" property="markettype" />
        <result column="TRADEMODE" property="trademode" />
        <result column="TRADEPROPERTY" property="tradeproperty" />
        <result column="OPENMETHOD" property="openmethod" />
        <result column="RECKONPRICEALGORITHM" property="reckonpricealgorithm" />
        <result column="RECKONPRICEPARAM" property="reckonpriceparam" />
        <result column="MARKETSTATUS" property="marketstatus" />
        <result column="MARKETSERVICEID" property="marketserviceid" />
        <result column="MARGINFORMULA" property="marginformula" />
        <result column="PERFORMANCETEMPID" property="performancetempid" />
        <result column="CLEARINTERVAL" property="clearinterval" />
        <result column="MATCHERMODE" property="matchermode" />
        <result column="RECKONORDER" property="reckonorder" />
        <result column="CANPREEXERCISE" property="canpreexercise" />
        <result column="CANGOODSEXERCISE" property="cangoodsexercise" />
        <result column="GOODSTYPE" property="goodstype" />
        <result column="ROLEPRIORITYTYPE" property="roleprioritytype" />
        <result column="ISRECORDSOURCE" property="isrecordsource" />
        <result column="MARGINFORMULA2" property="marginformula2" />
        <result column="HASWR" property="haswr" />
        <result column="PREMIUMQUOTEMODE" property="premiumquotemode" />
        <result column="CANGOODSEXERCISETYPE" property="cangoodsexercisetype" />
        <result column="CANACCEPTQUOTE" property="canacceptquote" />
        <result column="CANMUTISTAGE" property="canmutistage" />
        <result column="ISDEDUCTMARGIN" property="isdeductmargin" />
        <result column="CANMANUALQUOTESTRIKE" property="canmanualquotestrike" />
        <result column="TRADETYPE" property="tradetype" />
        <result column="PENDINGFLAG" property="pendingflag" />
        <result column="PENDINGTIME" property="pendingtime" />
        <result column="RECKONTIME" property="reckontime" />
        <result column="HASTRADECREDIT" property="hastradecredit" />
        <result column="CONTRACTTMP" property="contracttmp" />
        <result column="HASREBATE" property="hasrebate" />
        <result column="REBATERATIO" property="rebateratio" />
        <result column="OTCUSERID" property="otcuserid" />
        <result column="OUTERSYNCTIME" property="outersynctime" />
        <result column="AUCTIONWRTYPE" property="auctionwrtype" />
        <result column="ISRELEASEMARGIN" property="isreleasemargin" />
        <result column="SELLLISTINGAUDITFLAG" property="selllistingauditflag" />
        <result column="TRADEMARKETTYPE" property="trademarkettype" />
        <result column="PAYLATERFLAG" property="paylaterflag" />
        <result column="ISUPDATERECKONPRICE" property="isupdatereckonprice" />
        <result column="UPDATERECKONPRICEINTERVAL" property="updatereckonpriceinterval" />
        <result column="FINANCEMARKETID" property="financemarketid" />
        <result column="FORCECLOSEMODE" property="forceclosemode" />
        <result column="FORCECLOSEVALUE" property="forceclosevalue" />
        <result column="DELIVERYMODE" property="deliverymode" />
        <result column="ISDELIVERYSELLERCANAPPLY" property="isdeliverysellercanapply" />
        <result column="ISDELIVERYBUYERPAYONLINE" property="isdeliverybuyerpayonline" />
        <result column="RISKCONTROLMODE" property="riskcontrolmode" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        MARKETID, MARKETNAME, EXCHAREAID, MARKETTYPE, TRADEMODE, TRADEPROPERTY, OPENMETHOD, RECKONPRICEALGORITHM, RECKONPRICEPARAM, MARKETSTATUS, MARKETSERVICEID, MARGINFORMULA, PERFORMANCETEMPID, CLEARINTERVAL, MATCHERMODE, RECKONORDER, CANPREEXERCISE, CANGOODSEXERCISE, GOODSTYPE, ROLEPRIORITYTYPE, ISRECORDSOURCE, MARGINFORMULA2, HASWR, PREMIUMQUOTEMODE, CANGOODSEXERCISETYPE, CANACCEPTQUOTE, CANMUTISTAGE, ISDEDUCTMARGIN, CANMANUALQUOTESTRIKE, TRADETYPE, PENDINGFLAG, PENDINGTIME, RECKONTIME, HASTRADECREDIT, CONTRACTTMP, HASREBATE, REBATERATIO, OTCUSERID, OUTERSYNCTIME, AUCTIONWRTYPE, ISRELEASEMARGIN, SELLLISTINGAUDITFLAG, TRADEMARKETTYPE, PAYLATERFLAG, ISUPDATERECKONPRICE, UPDATERECKONPRICEINTERVAL, FINANCEMARKETID, FORCECLOSEMODE, FORCECLOSEVALUE, DELIVERYMODE, ISDELIVERYSELLERCANAPPLY, ISDELIVERYBUYERPAYONLINE, RISKCONTROLMODE
    </sql>

    <update id="updateExchareaid">
        update market set exchareaid = #{exchareaid}
        where marketid in
        <foreach collection="marketids" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </update>
    <update id="updateExchareaidNull" parameterType="java.lang.Long">
        update market set exchareaid = null where exchareaid = #{userid}
    </update>
    <delete id="deleteMarketCanlendarByMap"
            parameterType="com.muchinfo.mtp3pojos.vo.tradingmarket.MarketCalendarDelParam">
        DELETE FROM MARKETCALENDAR
        WHERE HOLIDAYDATE > TO_CHAR(SYSDATE,'yyyyMMdd')
        <if test="vo.year != null">
            AND SUBSTR(HOLIDAYDATE, 0, 4) = #{vo.year,jdbcType=VARCHAR}
        </if>
        <if test="vo.marketid != null">
            AND MARKETID = #{vo.marketid}
        </if>
    </delete>

    <select id="getMarketsNameByUseridAndrole" resultType="com.muchinfo.mtp3pojos.entity.Market">
        select m.marketid,m.marketname,m.trademode,o.roletype,o.spotrighttype ,
        o.buyspotrighttype,m.tradeproperty
        from market m ,arearolemarket o
        where m.marketid = o.marketid and o.areauserid = #{uid}
        and m.marketstatus = 2
        <if test="isqq!=null and isqq != 0 ">
            and m.tradeMode !=18
        </if>
        and roletype in
        <foreach collection="roletype" close=")" open="(" separator="," item="item">
            #{item}
        </foreach>
        group by m.marketid,m.marketname,m.trademode,o.roletype,
        o.spotrighttype,o.buyspotrighttype,m.tradeproperty
    </select>


    <select id="getNoTradeConfigMarkets" resultMap="BaseResultMap">
        select t.* from market t
        where t.tradeproperty in (1,2,3) and t.marketstatus!=3
        and t.trademode not in(40,41)
        and t.marketid in ( select marketid from arearolemarket a
        where <if test="areauserid != null">a.areauserid = #{areauserid} and</if>  a.roletype = 7)
        <if test="usergroupid != null">
            and not exists ( select 1 from usergrouptradeconfig u
            where <if test="areauserid != null">u.memberuserid=#{areauserid} and</if> u.marketid = t.marketid
            and u.usergroupid = #{usergroupid})
        </if>
    </select>

    <select id="getNoTradeConfigGroups" resultType="com.muchinfo.mtp3pojos.entity.Usergroup">
        select t.* from usergroup t
        where
        <if test="areauserid != null">t.areauserid = #{areauserid} and</if>
        not exists (select 1 from usergrouptradeconfig u
        where
        <if test="areauserid != null">u.memberuserid=#{areauserid} and</if>
        t.autoid = u.usergroupid)
    </select>

    <select id="queryAreaGoodsMarkets" resultMap="BaseResultMap">
        select m.marketid ,m.marketname , m.trademode
        from market m
        where m.markettype = 2 and m.marketstatus != 1
        <if test="userid != null and userid != '' and userid != 1">
            and m.marketid in
            (select g.marketid from AreaGoodsConfig t
            left join goods g on t.goodsid = g.goodsid
            where t.userid = #{userid})
        </if>
        order by m.marketid
    </select>
    <select id="getNoTradeConfigMarketsForAccount" resultType="com.muchinfo.mtp3pojos.entity.Market"
            parameterType="com.muchinfo.mtp3pojos.vo.investor.PersonalizationDelParam">
        select t.* from market t
        <choose>
            <when test="vo.accountid != null">
                ,taaccountmarket ta
                where t.tradeproperty in (1,2,3) and t.trademode not in(40,41) and t.marketid = ta.marketid and ta.accountid=#{vo.accountid}
            </when>
            <otherwise>
                where t.tradeproperty in (1,2,3) and t.trademode not in(40,41) and t.marketid in ( select marketid from arearolemarket a
                where <if test="vo.memberuserid != null"> a.areauserid = #{vo.memberuserid} and </if> a.roletype = 7)
            </otherwise>
        </choose>
    </select>
    <select id="queryoperatemarkets" resultType="com.muchinfo.mtp3pojos.entity.Market"
            parameterType="java.lang.Long">
        select m.marketid "marketid" ,m.marketname "marketname", m.trademode "trademode"
        from market m
        where m.markettype = 2 and m.marketstatus != 1
        <if test="areauserid != null and areauserid != '' and areauserid != 1">
            and m.marketid in
            (select g.marketid from AreaGoodsConfig t
            left join goods g on t.goodsid = g.goodsid
            where t.userid = #{areauserid})
        </if>
        order by m.marketid
    </select>
    <select id="queryTradeModeListNew" resultType="com.muchinfo.mtp3pojos.vo.systemsetup.SystemMarketSetResult">
        select distinct m.trademode, en.enumdicname trademodename
        from  market m
        left join enumdicitem en on en.enumdiccode='trademode' and en.enumitemname = m.trademode
        where m.markettype = 2
        and m.marketstatus =2
        and m.trademode not in(15, 45)
        and m.marketid not in(
        SELECT distinct REGEXP_SUBSTR(mscn.marketids, '[^,]+', 1, LEVEL) AS marketid
        FROM MarketSectionConfignew mscn
        <where>
            <if test="editmarketsectionid!=null and editmarketsectionid!=''">
                and  mscn.marketsectionid != #{editmarketsectionid}
            </if>
        </where>
        CONNECT BY REGEXP_SUBSTR(mscn.marketids, '[^,]+', 1, LEVEL) IS NOT NULL)
        <if test="selectedmarketids!=null and selectedmarketids.length>0">
            <foreach collection="selectedmarketids" item="item" open="and m.marketid not in (" close=")" separator=",">
                ${item}
            </foreach>
        </if>
        order by m.trademode
    </select>
    <select id="getMarketsByTradeMode" resultType="com.muchinfo.mtp3pojos.entity.Market">
        select * from MARKET where markettype = 2
        <if test="status!=null">
            and marketStatus in
            <foreach item="item" index="index" collection="status"
                     open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="exchareaid != null and exchareaid != ''">
            AND exchareaid = #{exchareaid,jdbcType=DECIMAL}
        </if>
    </select>
    <select id="querymarketrunsectiion"
            resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.MarketRunSectiionResult">
        SELECT
        M .marketid,
        M .marketname,
        wm_concat(DISTINCT T .TRADEWEEKDAY)weekPlan
        FROM MARKET M
        LEFT JOIN MARKETRUNSTEPDETAIL T ON T .marketid = M .marketid
        WHERE m.MarketStatus != 3 and m.markettype = 2 and m.trademode not in (97,99)
        and M.marketid > 1000
        <if test="exchareaid != null and exchareaid != ''">
            AND m.exchareaid = #{exchareaid,jdbcType=DECIMAL}
        </if>
        <if test="trademarkettype != null and trademarkettype != ''">
            AND m.trademarkettype = #{trademarkettype,jdbcType=DECIMAL}
        </if>
        GROUP BY m.marketid, m.marketname
        ORDER BY m.marketId, m.marketname
    </select>
    <select id="queryMarketCalendar" resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.MarketCalendarResult">
        select
        m.marketid,M.marketname, m.trademarkettype, wm_concat(a.years) year
        from
        MARKET M
        LEFT JOIN
        (
        select DISTINCT(t.marketid), substr(t.holidaydate, 0, 4) years
        from MarketCalendar t
        union
        select t.marketid, t.year years
        from MarketCalendarYear t
        ) a ON a.marketid = m.marketid
        WHERE m.MarketStatus != 3 and m.markettype = 2
        <if test="marketName != null and marketName != ''">
            AND MARKETNAME like '%'||#{marketName,jdbcType=VARCHAR}||'%'
        </if>
        <if test="exchareaid != null and exchareaid != ''">
            AND m.exchareaid = #{exchareaid,jdbcType=DECIMAL}
        </if>
        <if test="trademarkettype != null and trademarkettype != ''">
            AND m.trademarkettype = #{trademarkettype,jdbcType=DECIMAL}
        </if>

        <if test="nottrademode != null">
            AND m.trademode != #{nottrademode,jdbcType=DECIMAL}
        </if>
        GROUP BY M .marketid, M .marketname, m.trademarkettype
        ORDER BY M .marketname asc
    </select>
    <select id="queryMarketCalendarById" resultType="com.muchinfo.mtp3pojos.entity.Marketcalendar">
        SELECT
        r.HOLIDAYDATE,
        r.MARKETID,
        s.MARKETNAME
        FROM MARKETCALENDAR r
        LEFT JOIN MARKET s on r.MARKETID=s.MARKETID
        WHERE SUBSTR(HOLIDAYDATE, 0, 4) = #{year,jdbcType=VARCHAR}
        <if test="markerid != null">
            AND r.MARKETID = #{markerid}
        </if>
    </select>
    <select id="querymarketmanage" resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.MarketManageResult">
        select s.*
        from MARKET s
        where markettype = 2 and marketid>1000
        <if test="vo.marketName != null and vo.marketName != ''">
            AND (s.MARKETNAME like '%'||#{vo.marketName,jdbcType=VARCHAR}||'%'
            OR s.MARKETID  like '%'||#{vo.marketName,jdbcType=VARCHAR}||'%')
        </if>
        <if test="vo.marketStatus != null">
            AND s.marketStatus = #{vo.marketStatus,jdbcType=DECIMAL}
        </if>
        <if test="vo.exchareaid != null and vo.exchareaid != ''">
            AND s.exchareaid = #{vo.exchareaid,jdbcType=DECIMAL}
        </if>
        <if test="vo.trademarkettype != null and vo.trademarkettype != ''">
            AND s.trademarkettype = #{vo.trademarkettype,jdbcType=DECIMAL}
        </if>
        order by s.marketstatus,s.marketId,s.marketName
    </select>

    <select id="queryMarketExtension" parameterType="java.lang.Long"
            resultType="com.muchinfo.mtp3pojos.vo.MarketDto">
        select
            t.*,
            s.username defaulfcusername
        from WHZG_MarketExtension t
                 left join SystemManager s on t.DEFAULFCUSERID = s.areauserid
        where t.MARKETID = #{marketid}
    </select>
    <select id="querymarkets" resultType="com.muchinfo.mtp3pojos.vo.systemsetup.SystemMarketNameResult">
        select m.marketid,m.marketname
        from market m
        where m.markettype = 2
        and m.marketstatus =2
        and m.marketid not in(
        SELECT distinct REGEXP_SUBSTR(mscn.marketids, '[^,]+', 1, LEVEL) AS marketid
        FROM MarketSectionConfignew mscn
        CONNECT BY REGEXP_SUBSTR(mscn.marketids, '[^,]+', 1, LEVEL) IS NOT NULL)
        and m.trademode = #{ trademode }
        <if test="selectedmarketids!=null and selectedmarketids.length>0">
            <foreach collection="selectedmarketids" item="item" open="and m.marketid not in (" close=")" separator=",">
                ${item}
            </foreach>
        </if>
        order by m.marketid
    </select>
</mapper>
