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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.Quoter">
        <id column="QUOTERID" property="quoterid" />
        <result column="QUOTERNAME" property="quotername" />
        <result column="AREAUSERID" property="areauserid" />
        <result column="TRADEPROPERTY" property="tradeproperty" />
        <result column="DEFAULTMAKERID" property="defaultmakerid" />
        <result column="QUOTERSTATUS" property="quoterstatus" />
        <result column="MODIFIERID" property="modifierid" />
        <result column="MODIFYTIME" property="modifytime" />
        <result column="MATCHMODE" property="matchmode" />
    </resultMap>

    <resultMap id="pageResultMap" type="com.muchinfo.mtp3pojos.vo.tradingmarket.TradingQuerySecRelationResult">
        <association property="firstname" column="MEMBERUSERID" jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
        <association property="children" column="MEMBERUSERID"
                     select="query2List"></association>
    </resultMap>

    <resultMap id="childrenResultMap" type="com.muchinfo.mtp3pojos.entity.Mmrelation2">
        <association property="matchname" column="MATCHUSERID" jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        QUOTERID, QUOTERNAME, AREAUSERID, TRADEPROPERTY, DEFAULTMAKERID, QUOTERSTATUS, MODIFIERID, MODIFYTIME, MATCHMODE
    </sql>

    <select id="query2List" parameterType="java.lang.Long" resultMap="childrenResultMap">
        select t.*
        from mmrelation2 t
        where t.memberuserid = #{memberuserid}
        order by t.matchseq
    </select>

    <select id="getUserListExistsFirstByQuoter" resultType="com.muchinfo.mtp3pojos.entity.Useraccount">
        select u.userid,u.accountname from useraccount u ,mmrelation1 m
        <where>
            u.userid = m.memberuserid
            <if test="quoterid != null">
                and m.quoterid = #{quoterid}
            </if>
            <if test="matchuserid != null">
                and m.matchuserid = #{matchuserid}
            </if>
        </where>
        order by u.userid
    </select>

    <select id="getUserListNotExistsFirstByQuoter" resultType="com.muchinfo.mtp3pojos.entity.Useraccount">
        select u.userid, u.accountname
        from useraccount u,
        (select userid from arearole a where a.roletype = 6 and a.userid != #{matchuserid}
        union
        select userid from arearole a where a.roletype = 7) a
        where u.userid = a.userid
        <if test="quoterid !=null">
            and u.userid not in (select m.memberuserid from mmrelation1 m where
            m.quoterid = #{quoterid})
        </if>
        group by u.userid, u.accountname
        order by u.userid
    </select>

    <select id="queryGoodsQuoterPage"
            resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.TradingManualQuoterResult">
        <choose>
            <!-- 平台管理端员能看到所有有效报价 -->
            <when test="areauserid == 0 or areauserid == 1 or areauserid == null">
                SELECT
                G.GOODSID, G.GOODSCODE, M.MARKETNAME, G.GOODSNAME ,mr.RUNSTATUS ,
                tq.price "bid", tq.qty "buyqty", tq.curqty "buycurqty", tq.isvalid "buyisvalid",
                tq1.price "ask", tq1.qty "sellqty", tq1.curqty "sellcurqty", tq1.isvalid "sellisvalid",
                q.AREAUSERID,q.QUOTERNAME, M.TRADEMODE
                FROM
                QUOTER q, GOODSQUOTER gq,MARKET M , GOODS G
                LEFT JOIN MARKETRUN mr ON G .MARKETID = mr .MARKETID
                LEFT JOIN Trade_QuoteDetailNew tq ON G.goodsid = tq.goodsid and tq.buyorsell=0
                AND tq.QUOTERID IN (SELECT QUOTERID FROM QUOTER)
                AND SYSDATE &lt;= (tq.QUOTETIME+NUMTODSINTERVAL(tq.QUOTEEFFECTIVESECOND, 'second'))
                AND tq.TradeDate in (SELECT MR.TradeDate2 FROM MARKETRUN MR,GOODS GD
                WHERE GD.goodsid = G.goodsid AND GD.MARKETID = MR.MARKETID)
                LEFT JOIN Trade_QuoteDetailNew tq1 ON G.goodsid = tq1.goodsid and tq1.buyorsell=1
                AND tq1.QUOTERID IN (SELECT QUOTERID FROM QUOTER)
                AND SYSDATE &lt;= (tq1.QUOTETIME+NUMTODSINTERVAL(tq1.QUOTEEFFECTIVESECOND, 'second'))
                AND tq1.TradeDate in (SELECT MR.TradeDate2 FROM MARKETRUN MR,GOODS GD
                WHERE GD.goodsid = G.goodsid AND GD.MARKETID = MR.MARKETID)
                WHERE
                q.QUOTERID = GQ.QUOTERID
                AND G .MARKETID = M .MARKETID
                AND GQ.GOODSID = G.GOODSID
                AND G.GOODSSTATUS = 3
                and gq.quoteMode = 1
                and m.tradeproperty in (1,2,5,7)
                and m.trademode not in (18, 32)
                <if test="vo.trademodes!=null and vo.trademodes.length>0">
                    <foreach collection="vo.trademodes" item="item" open="and m.trademode in (" close=")" separator=",">
                        ${item}
                    </foreach>
                </if>
                <if test="vo.goodsname != null and vo.goodsname != '' ">
                    and (G.GOODSCODE like '%,'|| #{vo.goodsname}||',%'
                    or G.goodsname like '%' || #{vo.goodsname} || '%')
                </if>
                ORDER BY G.GOODSCODE ASC
            </when>
            <otherwise>
                SELECT
                G.GOODSID, G.GOODSCODE, M.MARKETNAME, G.GOODSNAME ,mr.RUNSTATUS ,
                tq.price "bid", tq.qty "buyqty", tq.curqty "buycurqty", tq.isvalid "buyisvalid",
                tq1.price "ask", tq1.qty "sellqty", tq1.curqty "sellcurqty", tq1.isvalid "sellisvalid",
                q.AREAUSERID,q.QUOTERNAME, M.TRADEMODE
                FROM
                QUOTER q, GOODSQUOTER gq,MARKET M , GOODS G
                LEFT JOIN MARKETRUN mr ON G .MARKETID = mr .MARKETID
                LEFT JOIN Trade_QuoteDetailNew tq ON G.goodsid = tq.goodsid and tq.buyorsell=0
                AND tq.QUOTERID IN (SELECT QUOTERID FROM QUOTER WHERE AREAUSERID = #{vo.areauserid,jdbcType=DECIMAL})
                AND SYSDATE &lt;= (tq.QUOTETIME+NUMTODSINTERVAL(tq.QUOTEEFFECTIVESECOND, 'second'))
                AND tq.TradeDate in (SELECT MR.TradeDate2 FROM MARKETRUN MR,GOODS GD
                WHERE GD.goodsid = G.goodsid AND GD.MARKETID = MR.MARKETID)
                LEFT JOIN Trade_QuoteDetailNew tq1 ON G.goodsid = tq1.goodsid and tq1.buyorsell=1
                AND tq1.QUOTERID IN (SELECT QUOTERID FROM QUOTER WHERE AREAUSERID = #{vo.areauserid,jdbcType=DECIMAL})
                AND SYSDATE &lt;= (tq1.QUOTETIME+NUMTODSINTERVAL(tq1.QUOTEEFFECTIVESECOND, 'second'))
                AND tq1.TradeDate in (SELECT MR.TradeDate2 FROM MARKETRUN MR,GOODS GD
                WHERE GD.goodsid = G.goodsid AND GD.MARKETID = MR.MARKETID)
                WHERE
                q.QUOTERID = GQ.QUOTERID
                AND G .MARKETID = M .MARKETID
                AND GQ.GOODSID = G.GOODSID
                AND G.GOODSSTATUS = 3
                and gq.quoteMode = 1
                and m.tradeproperty in (1,2,5,7)
                and m.trademode not in (18, 32)
                AND q.AREAUSERID = #{vo.areauserid,jdbcType=DECIMAL}
                <if test="vo.trademodes!=null and vo.trademodes.length>0">
                    <foreach collection="vo.trademodes" item="item" open="and m.trademode in (" close=")" separator=",">
                        ${item}
                    </foreach>
                </if>
                <if test="vo.goodsname != null and vo.goodsname != '' ">
                    and (G.GOODSCODE like '%,'|| #{vo.goodsname}||',%'
                    or G.goodsname like '%' || #{vo.goodsname} || '%')
                </if>
                ORDER BY G.GOODSCODE ASC
            </otherwise>
        </choose>
    </select>
    <select id="getParamByMap" resultType="com.muchinfo.mtp3pojos.vo.GoodsQuoterDto">
        SELECT
        G.GOODSID,q.QUOTERID,M.MARKETNAME, G.GOODSNAME,m.marketid,m.trademode,m.tradeproperty,m.canmanualquotestrike,
        G.currencyid, G.goodunitid, G.agreeunit, G.decimalplace,
        gq.quoteeffectivesecond,gq.quoteminvalue,gq.quotemaxvalue,gq.quotetype,
        mar.TRADEDATE,gop.optiontype,gop.cuppricegear,gop.cdownpricegear,gop.puppricegear,gop.pdownpricegear
        FROM
        QUOTER q,
        GOODSQUOTER gq,
        GOODS G
        LEFT JOIN MARKET M ON G .MARKETID = M .MARKETID
        LEFT JOIN GOODSOPTIONPROPERTY gop  ON gop.GOODSID = G.GOODSID
        LEFT JOIN MARKETRUN mar ON mar.MARKETID = G.MARKETID
        WHERE
        q.QUOTERID = GQ.QUOTERID
        AND GQ.GOODSID = G .GOODSID
        AND G.GOODSSTATUS = 3
        and G.GOODSID = #{goodsid,jdbcType=DECIMAL}
        <if test="areauserid != null and areauserid != 1 ">
            AND q.AREAUSERID = #{areauserid,jdbcType=DECIMAL}
        </if>
    </select>
    <select id="getParamNewByMap" resultType="com.muchinfo.mtp3pojos.vo.GoodsQuoterDto">
        SELECT
        G.GOODSID,q.QUOTERID,M.MARKETNAME, G.GOODSNAME,m.marketid,m.trademode,m.tradeproperty,m.canmanualquotestrike,
        G.currencyid, G.goodunitid, G.agreeunit, G.decimalplace,
        gq.quoteeffectivesecond,gq.quoteminvalue,gq.quotemaxvalue,gq.quotetype,
        tq.buyorsell,tq.price,tq.qty,tq.curqty,tq.quoteeffectivesecond as expire,tq.limitprice,tq.move,
        tq.pricetype,tq.qtytype,
        mar.TRADEDATE,gop.optiontype,gop.cuppricegear,gop.cdownpricegear,gop.puppricegear,gop.pdownpricegear
        FROM
        QUOTER q,
        GOODSQUOTER gq,
        GOODS G
        LEFT JOIN MARKET M ON G .MARKETID = M .MARKETID
        LEFT JOIN GOODSOPTIONPROPERTY gop  ON gop.GOODSID = G.GOODSID
        LEFT JOIN MARKETRUN mar ON mar.MARKETID = G.MARKETID
        LEFT JOIN Trade_QuoteDetailNew tq ON G.goodsid = tq.goodsid
        and tq.QUOTERID in (select QUOTERID from QUOTER where AREAUSERID = #{areauserid,jdbcType=DECIMAL})
        AND tq.TradeDate in (SELECT MR.TradeDate2 FROM MARKETRUN MR,GOODS GD
        WHERE GD.goodsid = #{goodsid,jdbcType=DECIMAL} AND GD.MARKETID = MR.MARKETID)
        WHERE
        q.QUOTERID = GQ.QUOTERID
        AND GQ.GOODSID = G .GOODSID
        AND G.GOODSSTATUS = 3
        and G.GOODSID = #{goodsid,jdbcType=DECIMAL}
        <if test="buyorsell != null">
            and tq.buyorsell = #{buyorsell,jdbcType=DECIMAL}
        </if>
        <if test="areauserid != null and areauserid != 1 ">
            AND q.AREAUSERID = #{areauserid,jdbcType=DECIMAL}
        </if>
    </select>
    <select id="getNewSellCurQty" resultType="com.muchinfo.mtp3pojos.vo.GoodsQuoterDto">
        SELECT
            tq.curqty "sellcurqty",tq.isvalid "sellisvalid"
        FROM
            TRADE_QUOTEDETAILNEW tq
        WHERE tq.quoterid = #{quoterid,jdbcType=DECIMAL}
          AND tq.buyorsell = 1
          AND tq.goodsid = #{goodsid,jdbcType=DECIMAL}
          AND tq.TradeDate in (SELECT MR.TradeDate2 FROM MARKETRUN MR,GOODS GD
                               WHERE GD.goodsid = #{goodsid,jdbcType=DECIMAL} AND GD.MARKETID = MR.MARKETID)
          AND SYSDATE &lt;= (tq.QUOTETIME+NUMTODSINTERVAL(tq.QUOTEEFFECTIVESECOND, 'second'))
    </select>
    <select id="getUserListNotExistsSec" resultType="com.muchinfo.mtp3pojos.vo.common.OrganSelectResult"
            parameterType="java.lang.String">
        select u.userid, u.accountname
        from useraccount u, arearole a
        where u.userid = a.userid
        and a.roletype = 8
        and u.userid not in (select m.memberuserid from mmrelation2 m)
        <if test="accountname != null and accountname !=''">
            and (u.accountname like
            '%'||#{accountname,jdbcType=VARCHAR}||'%' or u.userid like
            '%'||#{accountname,jdbcType=VARCHAR}||'%')
        </if>
        and rownum &lt;= 10
    </select>
    <select id="getExistsSecUserById" resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.MmRelationResult"
            parameterType="java.lang.Long">
        select m.*,u.accountname matchname
        from mmrelation2 m, useraccount u
        <where>
            and m.matchuserid = u.userid
            <if test="memberuserid != null">
                and m.memberuserid = #{memberuserid}
            </if>
        </where>
        order by m.matchseq
    </select>
    <select id="getNotExistsSecUserById" resultType="com.muchinfo.mtp3pojos.vo.common.OrganSelectResult"
            parameterType="java.util.List">
        select u.userid,u.accountname from useraccount u,arearole a
        <where>
            and u.userid = a.userid and a.roletype=8
            <if test="ids != null">
                and u.userid not in
                <foreach collection="ids" close=")" open="(" separator="," item="i">
                    #{i}
                </foreach>
            </if>
        </where>
        order by u.userid
    </select>
    <select id="querySecRelation" resultMap="pageResultMap">
        select t.memberuserid from mmrelation2 t
        <where>
            <if test="memberuserid != null">
                and  t.memberuserid = #{memberuserid}
            </if>
        </where>
        group by t.memberuserid
        order by t.memberuserid
    </select>


</mapper>
