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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.TradeHolderdetailex">
        <id column="TRADEID" property="tradeid" />
        <result column="BUYORSELL" property="buyorsell" />
        <result column="TRADEDATE" property="tradedate" />
        <result column="MARKETID" property="marketid" />
        <result column="GOODSID" property="goodsid" />
        <result column="USERID" property="userid" />
        <result column="ACCOUNTID" property="accountid" />
        <result column="MATCHUSERID" property="matchuserid" />
        <result column="MATCHACCOUNTID" property="matchaccountid" />
        <result column="OPENQTY" property="openqty" />
        <result column="TRADEAMOUNT" property="tradeamount" />
        <result column="OPENPRICE" property="openprice" />
        <result column="HOLDERQTY" property="holderqty" />
        <result column="HOLDERAMOUNT" property="holderamount" />
        <result column="HOLDERPRICE" property="holderprice" />
        <result column="FREEZEQTY" property="freezeqty" />
        <result column="DELIVERYQTY" property="deliveryqty" />
        <result column="HOLDDAYS" property="holddays" />
        <result column="PAYEDDEPOSIT" property="payeddeposit" />
        <result column="RESTOCKDEPOSIT" property="restockdeposit" />
        <result column="LATEFEEDAYS" property="latefeedays" />
        <result column="LATEFEEALGORITHM" property="latefeealgorithm" />
        <result column="LATEFEEVALUE" property="latefeevalue" />
        <result column="CALLATEFEE" property="callatefee" />
        <result column="PROMPTDEPOSITRATE" property="promptdepositrate" />
        <result column="CUTDEPOSITRATE" property="cutdepositrate" />
        <result column="PROMPTPRICE" property="promptprice" />
        <result column="CUTPRICE" property="cutprice" />
        <result column="CLOSEPL" property="closepl" />
        <result column="FLOATPL" property="floatpl" />
        <result column="DEPOSITRATE" property="depositrate" />
        <result column="REFUNDABLEDEPOSIT" property="refundabledeposit" />
        <result column="PAYEDLATEFEE" property="payedlatefee" />
        <result column="TRADETIME" property="tradetime" />
        <result column="HANDLESTATUS" property="handlestatus" />
        <result column="ORIPAYEDDEPOSIT" property="oripayeddeposit" />
        <result column="ORIRESTOCKDEPOSIT" property="orirestockdeposit" />
        <result column="CANAUTOADDDEPOSIT" property="canautoadddeposit" />
        <result column="ADDDEPOSITRATE" property="adddepositrate" />
        <result column="CANAUTOREFUNDDEPOSIT" property="canautorefunddeposit" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        TRADEID, BUYORSELL, TRADEDATE, MARKETID, GOODSID, USERID, ACCOUNTID, MATCHUSERID, MATCHACCOUNTID, OPENQTY, TRADEAMOUNT, OPENPRICE, HOLDERQTY, HOLDERAMOUNT, HOLDERPRICE, FREEZEQTY, DELIVERYQTY, HOLDDAYS, PAYEDDEPOSIT, RESTOCKDEPOSIT, LATEFEEDAYS, LATEFEEALGORITHM, LATEFEEVALUE, CALLATEFEE, PROMPTDEPOSITRATE, CUTDEPOSITRATE, PROMPTPRICE, CUTPRICE, CLOSEPL, FLOATPL, DEPOSITRATE, REFUNDABLEDEPOSIT, PAYEDLATEFEE, TRADETIME, HANDLESTATUS, ORIPAYEDDEPOSIT, ORIRESTOCKDEPOSIT, CANAUTOADDDEPOSIT, ADDDEPOSITRATE, CANAUTOREFUNDDEPOSIT
    </sql>

    <select id="query" resultType="com.muchinfo.mtp3pojos.vo.query.order.OrderQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.order.OrderQueryParam">
        select
        m.trademode "trademode",
        ua.userid || '/' || ua.accountname "accountname",
        ua2.userid || '/' || ua2.accountname "membername",
        ua3.userid || '/' || ua3.accountname "parentname",
        g.goodscode || '/' || g.goodsname "goodsname",
        g.goodunitid "goodsunitid",
        g.agreeunit "agreeunit",
        g.decimalplace "decimalplace",
        g.goodscode "goodscode",
        t.tradeid "tradeid",
        t.buyorsell "buyorsell",
        t.tradedate "tradedate",
        t.marketid "marketid",
        t.goodsid "goodsid",
        t.userid "userid",
        t.accountid "accountid",
        t.matchuserid "matchuserid",
        t.matchaccountid "matchaccountid",
        t.openqty "openqty",
        t.openqty * g.agreeunit || ' ' || en.enumdicname "opennum",
        t.tradeamount "tradeamount",
        t.openprice "openprice",
        t.holderqty "holderqty",
        t.holderqty * g.agreeunit || ' ' || en.enumdicname "holdernum",
        t.holderamount "holderamount",
        t.holderprice "holderprice",
        t.freezeqty "freezeqty",
        t.freezeqty * g.agreeunit || ' ' || en.enumdicname "freezenum",
        (t.holderqty - t.freezeqty) * g.agreeunit "avaliablenumval",
        (t.holderqty - t.freezeqty) * g.agreeunit || ' ' || en.enumdicname "avaliablenum",
        t.deliveryqty "deliveryqty",
        t.deliveryqty * g.agreeunit || ' ' || en.enumdicname "deliverynum",
        nvl(t.holddays, 0) + 1 "holddays",
        t.payeddeposit "payeddeposit",
        t.restockdeposit "restockdeposit",
        t.latefeedays "latefeedays",
        t.latefeealgorithm "latefeealgorithm",
        t.latefeevalue "latefeevalue",
        t.callatefee "callatefee",
        t.promptdepositrate "promptdepositrate",
        t.cutdepositrate "cutdepositrate",
        t.promptprice "promptprice",
        t.cutprice "cutprice",
        t.closepl "closepl",
        t.floatpl "floatpl",
        t.depositrate "depositrate",
        t.refundabledeposit "refundabledeposit",
        t.payedlatefee "payedlatefee",
        to_char(t.tradetime, 'yyyy-MM-dd hh24:mi:ss') "tradetimestr",
        t.oripayeddeposit "oripayeddeposit",
        t.orirestockdeposit "orirestockdeposit",
        t.canautoadddeposit "canautoadddeposit",
        t.adddepositrate "adddepositrate",
        t.canautorefunddeposit "canautorefunddeposit"
        from trade_holderdetailex t
        left join useraccount ua on t.userid = ua.userid
        left join useraccount ua2 on ua.memberuserid = ua2.userid
        left join useraccount ua3 on ua.parentuserid = ua3.userid
        left join goods g on t.goodsid = g.goodsid
        left join market m on m.marketid = g.marketid
        left join enumdicitem en on en.enumitemname = g.goodunitid and en.enumdiccode='goodsunit'
        where 1=1
        <if test="vo.goodsid != null and vo.goodsid != ''">
            and t.goodsid = #{vo.goodsid}
        </if>
        <if test="vo.buyorsell != null">
            and t.buyorsell = #{vo.buyorsell}
        </if>
        <if test="vo.tradeid != null and vo.tradeid != ''">
            and t.tradeid like '%'||#{vo.tradeid}||'%'
        </if>
        <if test="vo.accountname != null and vo.accountname != ''">
            and (ua.accountname like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%'
            or t.accountid like'%'||#{vo.accountname,jdbcType=VARCHAR}||'%'
            or t.userid like'%'||#{vo.accountname,jdbcType=VARCHAR}||'%')
        </if>
        <if test="vo.parantareaid != null and vo.parantareaid != ''">
            and ua.parentuserid = #{vo.parantareaid}
        </if>
        <if test="vo.areauserid != null and vo.areauserid != 0 and vo.areauserid != 1">
            and (ua.memberuserid = #{vo.areauserid} or ua.parentuserid =  #{vo.areauserid})
        </if>
        <if test="vo.validholderqty != null and vo.validholderqty == 1">
            <![CDATA[ and t.holderqty > 0 ]]>
        </if>
        order by t.tradeid desc
    </select>

    <select id="queryYJCloselList" resultType="com.muchinfo.mtp3pojos.vo.query.order.TerOrderQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.order.TerOrderQueryParam">
        select cd.opentradeid, cd.closeid,g.goodscode,
        vlog.LOGINIDS loginid, cd.accountid,
        ua.userid || '/' || ua.accountname currentname,
        g.goodscode || '/' || g.goodsname goodsfullname, hd.openprice,
        hd.openqty, hd.openqty * g.agreeunit || ' ' || en.enumdicname "opennum",
        cd.closeqty, cd.closeqty * g.agreeunit || ' ' || en.enumdicname "closenum",
        cd.closeprice, cd.closepl,
        to_char(hd.tradetime, 'yyyy-MM-dd hh24:mi:ss') as tradetime,
        to_char(cd.closetime,'yyyy-MM-dd hh24:mi:ss') as closetime,
        hd.buyorsell,
        enbuyorsell.enumdicname "buyorselldisplay",
        entradetype.enumdicname "tradetypedisplay",
        cde.repayeddeposit, cde.rerestockdeposit, cde.recallatefee,
        <choose>
            <when test="vo.ishis == null or vo.ishis == 0">
                0 "ishis"
            </when>
            <when test="vo.ishis == 1">
                1 "ishis"
            </when>
        </choose>
        <choose>
            <when test="vo.ishis == null or vo.ishis == 0">
                from trade_closedetail cd
                left join trade_tradedetail td on cd.tradeid = td.tradeid and cd.buyorsell = td.buyorsell
            </when>
            <when test="vo.ishis == 1">
                from his_trade_closedetail cd
                left join his_trade_tradedetail td on cd.tradeid = td.tradeid and cd.buyorsell = td.buyorsell and cd.histradedate = td.histradedate
            </when>
        </choose>
        inner join trade_holderdetailex hd on hd.tradeid = cd.opentradeid and hd.accountid = cd.accountid
        left join trade_closedetailex cde on cd.closeid = cde.closeid
        left join goods g on cd.goodsid = g.goodsid
        left join taaccount ta on ta.accountid = cd.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join view_user_loginids vlog on vlog.userid = ta.userid
        left join enumdicitem enbuyorsell on hd.buyorsell = enbuyorsell.enumitemname and enbuyorsell.enumdiccode = 'buyOrSell'
        left join enumdicitem en on en.enumitemname = g.goodunitid and en.enumdiccode='goodsunit'
        left join enumdicitem entradetype on entradetype.enumdiccode = 'tradetype' and entradetype.enumitemname = td.tradetype
        <where>
            <if test="vo.ishis == 1">
                AND cd.ISVALIDDATA = 1
            </if>
            <if test="vo.account != null and vo.account != ''">
                AND (ua.accountname like '%'||#{vo.account,jdbcType=VARCHAR}||'%'
                or ua.userid like '%'||#{vo.account,jdbcType=INTEGER}||'%')
            </if>

            <if test="vo.tradeid != null">
                AND cd.opentradeid like '%'||#{vo.tradeid,jdbcType=BIGINT}||'%'
            </if>
            <if test="vo.goodsid != null">
                and cd.goodsid = #{vo.goodsid,jdbcType=DECIMAL}
            </if>
            <if test="vo.startdate != null and vo.startdate != ''">
                <![CDATA[and cd.tradedate >= replace(#{vo.startdate}, '-')]]>
            </if>
            <if test="vo.enddate != null and vo.enddate != ''">
                <![CDATA[and cd.tradedate <= replace(#{vo.enddate}, '-')]]>
            </if>
            <if test="vo.areauserid != null and vo.areauserid != 0 and vo.areauserid != 1">
                and (ua.memberuserid = #{vo.areauserid} or ua.parentuserid = #{vo.areauserid})
            </if>
            <if test="vo.buyorsell != null">
                and hd.buyorsell = #{vo.buyorsell}
            </if>
            <if test="vo.tradetype != null">
                and td.tradetype = #{vo.tradetype}
            </if>
        </where>
        order by cd.closeid desc
    </select>

    <select id="queryDeliveryOfflinePage" parameterType="com.muchinfo.mtp3pojos.vo.query.order.DeliveryOrderParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.order.DeliveryOrderResult">
        select ua.userid || '/' || lo.loginids || '/' || ua.accountname "username", g.goodscode,
        g.goodscode || '/' || g.goodsname "goodsdisplay",
        e1.enumdicname "buyorselldisplay",
        t.deliveryqty "deliveryqty",
        t.deliveryprice "deliveryprice",
        t.deliveryamount "deliveryamount",
        ua2.userid || '/' || ua2.accountname "matchname",
        t.deliveryinfo "deliveryinfo",
        to_char(t.reqtime, 'yyyy-MM-dd hh24:mi:ss') "reqtime",
        e2.enumdicname "orderstatusdisplay",
        e3.enumdicname "deliverystatusdisplay",
        t.orderstatus "orderstatus",
        to_char(t.closetime, 'yyyy-MM-dd hh24:mi:ss') "closetime",
        t.deliveryorderid "deliveryorderid",
        t.deliverycharge "deliverycharge",
        t.deliverypremium "deliverypremium",
        t.deliverytotalamount "deliverytotalamount",
        t.deliverystatus "deliverystatus",
        t.payedamount "payedamount",
        t.freezeamount "freezeamount",
        t.deliveryqty * t.deliverypremium "deliverytotalpremium",
        t.buyorsell "buyorsell",
        g.goodunitid "goodunitid",
        t.marketid "marketid",
        t.userid "userid",
        t.accountid "accountid",
        t.goodsid "goodsid",
        t.remark "remark",
        t.sucdeliveryqty "sucdeliveryqty",
        t.cancledeliveryqty "cancledeliveryqty",
        t.canceldeliverylot "canceldeliverylot",
        nvl(t.deliverytotalamount, 0) - nvl(t.payedamount, 0) "remainamount",
        t.realdeliveryqty "realdeliveryqty",
        t.realdeliveryqty * t.deliverypremium "realdeliverytotalpremium"
        from Trade_GoodsDeliveryOffLine t
        left join useraccount ua on t.userid = ua.userid
        left join useraccount ua2 on t.matchuserid = ua2.userid
        left join goods g on t.goodsid = g.goodsid
        left join enumdicitem e1 on e1.enumdiccode = 'buyOrSell' and t.buyorsell = e1.enumitemname
        left join enumdicitem e2 on e2.enumdiccode = 'deliveryOrderStatus' and t.orderstatus = e2.enumitemname
        left join enumdicitem e3 on e3.enumdiccode = 'deliveryStatus' and t.deliverystatus = e3.enumitemname
        left join VIEW_USER_LOGINIDS lo on ua.userid = lo.userid
        where 1=1
        <if test="vo.accountname !=null and vo.accountname != '' ">
            AND (ua.accountname = #{vo.accountname} or t.accountid like #{vo.accountname}
            or ua.userid like #{vo.accountname}
            )
        </if>
        <if test="vo.deliverygoodsname  != null and vo.deliverygoodsname !=''">
            and (g.goodscode like '%'||#{vo.deliverygoodsname}||'%' or g.goodsname like '%'||#{vo.deliverygoodsname}||'%')
        </if>
        <if test="vo.deliveryorderstatus != null and vo.deliveryorderstatus !=''">
            AND (t.orderstatus = #{vo.deliveryorderstatus} or t.deliverystatus = #{vo.deliveryorderstatus})
        </if>
        <if test="vo.matchuserid  != null and vo.matchuserid !=''">
            and t.matchuserid = #{vo.matchuserid}
        </if>
        <if test="vo.buyorsell != null">
            and t.buyorsell = #{vo.buyorsell}
        </if>
        <if test="vo.startdate != null and vo.startdate !=''">
            and to_char(t.reqtime, 'yyyy-MM-dd') = #{vo.startdate}
        </if>
        <if test="vo.deliveryorderid != null and vo.deliveryorderid !=''">
            AND t.deliveryorderid = #{vo.deliveryorderid}
        </if>
        order by t.reqtime desc
    </select>

    <!--	持仓单扩展日照表 亿爵-->
    <select id="queryOrderDate" parameterType="com.muchinfo.mtp3pojos.vo.query.order.OrderDateQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.order.OrderDateQueryResult">
        WITH MaxDates AS (
        SELECT t.tradeid, t.accountid, max(t.reckondate) reckondate
        FROM Reckon_Trade_HolderDetailEx t
        GROUP BY t.tradeid, t.accountid
        )
        select t.tradedate "reckondate",
        t.tradeid "tradeid",
        t.buyorsell "buyorsell",
        t.tradedate "tradedate",
        t.marketid "marketid",
        t.goodsid "goodsid",
        t.userid "userid",
        t.accountid "accountid",
        t.matchuserid "matchuserid",
        t.matchaccountid "matchaccountid",
        t.openqty "openqty",
        t.tradeamount "tradeamount",
        t.openprice "openprice",
        t.holderqty "holderqty",
        t.holderamount "holderamount",
        t.holderprice "holderprice",
        t.freezeqty "freezeqty",
        t.deliveryqty "deliveryqty",
        t.holddays "holddays",
        t.payeddeposit "payeddeposit",
        t.restockdeposit "restockdeposit",
        t.latefeedays "latefeedays",
        t.latefeealgorithm "latefeealgorithm",
        t.latefeevalue "latefeevalue",
        t.callatefee "callatefee",
        t.promptdepositrate "promptdepositrate",
        t.cutdepositrate "cutdepositrate",
        t.promptprice "promptprice",
        t.cutprice "cutprice",
        t.closepl "closepl",
        t.floatpl "floatpl",
        t.depositrate "depositrate",
        --         t.reckondeposit "reckondeposit",
        t.refundabledeposit "refundabledeposit",
        t.payedlatefee "payedlatefee",
        g.goodscode,
        g.goodscode || '/' || g.goodsname "goodsfullname",
        ua.userid || '/' || ua.accountname "userfullname",
        ua2.userid || '/' || ua2.accountname "areafullname",
        t.openqty * g.agreeunit || ' ' || e2.enumdicname "opennum",
        t.holderqty * g.agreeunit || ' ' || e2.enumdicname "holdernum",
        t.freezeqty * g.agreeunit || ' ' || e2.enumdicname "freezenum",
        t.deliveryqty * g.agreeunit || ' ' || e2.enumdicname "deliverynum",
        e.enumdicname "buyorselldisplay",
        TO_CHAR(ROUND(t.depositrate * 100,2), '9999999999.99') || '%' "depositratedisplay",
        sm.logincode "operatorcode",
        t.oripayeddeposit "oripayeddeposit",
        t.orirestockdeposit "orirestockdeposit"
        from reckon_trade_holderdetailex t
        inner join MaxDates m on t.tradeid = m.tradeid and t.reckondate = m.reckondate and t.accountid = m.accountid
        left join hedge_outimportorder ho on t.tradeid = ho.relatedtradeid and t.userid = ho.userid
        left join systemmanager sm on ho.creatorid = sm.autoid
        left join goods g on t.goodsid = g.goodsid
        left join useraccount ua on t.userid = ua.userid
        left join useraccount ua2 on ua2.userid = ua.memberuserid
        left join enumdicitem e on e.enumdiccode = 'buyOrSell' and t.buyorsell = e.enumitemname
        left join enumdicitem e2 on e2.enumdiccode = 'goodsunit' and g.goodunitid = e2.enumitemname
        where 1 = 1
        <if test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''">
            and (to_date(t.tradedate,'yyyyMMdd') between to_date(#{vo.startDate},'yyyy-MM-dd') and
            to_date(#{vo.endDate},'yyyy-MM-dd'))
        </if>
        <if test="vo.areauserid !=null and vo.areauserid != ''">
            and ua.memberuserid = #{vo.areauserid}
        </if>
        <if test="vo.currentUserId !=null and vo.currentUserId != ''">
            and t.userid = #{vo.currentUserId}
        </if>
        <if test="vo.goodsid !=null and vo.goodsid != ''">
            and t.goodsid = #{vo.goodsid}
        </if>
        <if test="vo.tradeid !=null and vo.tradeid != ''">
            and t.tradeid = #{vo.tradeid}
        </if>
        order by t.tradedate desc, t.tradeid desc
    </select>

    <select id="queryForcedOrder" parameterType="com.muchinfo.mtp3pojos.vo.query.order.ForcedOrderParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.order.ForcedOrderResult">
        select t.closeid "closeid",
        t.tradeid "tradeid",
        t.opentradeid "opentradeid",
        ua.userid "userid",
        t.accountid "accountid",
        t.marketid "marketid",
        g.goodscode,
        ua.userid || '/' || ua.accountname "userfullname",
        ua2.userid || '/' || ua2.accountname "memberfullname",
        g.goodscode || '/' || g.goodsname "goodsfullname",
        t.buyorsell "buyorsell",
        hd.buyorsell "holderbuyorsell",
        t.openprice "openprice",
        hd.openqty "openqty",
        hd.openqty * g.agreeunit || ' ' || en.enumdicname "opennum",
        to_char(hd.tradetime, 'yyyy-MM-dd hh24:mi:ss') "opentime",
        t.closeprice "closeprice",
        t.closeqty "closeqty",
        t.isconfirmexercise "isconfirmexercise",
        t.closeqty * g.agreeunit || ' ' || en.enumdicname "closenum",
        to_char(t.closetime, 'yyyy-MM-dd hh24:mi:ss') "closetime",
        t.closepl "closepl",
        TO_CHAR(hd.depositrate * 100, 'FM9999990.00') || '%' "depositrate"
        <choose>
            <when test="vo.ishis == 1">
                from his_trade_tradedetail td
                inner join his_trade_closedetail t on t.tradeid = td.tradeid and t.accountid = td.accountid and td.histradedate = t.histradedate
            </when>
            <otherwise>
                from trade_tradedetail td
                inner join trade_closedetail t on t.tradeid = td.tradeid and t.accountid = td.accountid
            </otherwise>
        </choose>
        inner join trade_holderdetailex hd on t.opentradeid = hd.tradeid and t.accountid = hd.accountid
        left join taaccount ta on ta.accountid = t.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join useraccount ua2 on ua.memberuserid = ua2.userid
        left join goods g on g.goodsid = t.goodsid
        left join enumdicitem en on en.enumdiccode = 'goodsunit' and g.goodunitid = en.enumitemname
        where td.tradetype = 6
        <if test="vo.closeid != null and vo.closeid != ''">
            and t.closeid = #{vo.closeid}
        </if>
        <if test="vo.opentradeid != null and vo.opentradeid != ''">
            and t.opentradeid like '%' || #{vo.opentradeid} || '%'
        </if>
        <if test="vo.holderbuyorsell != null">
            and hd.buyorsell = #{vo.holderbuyorsell}
        </if>
        <if test="vo.goodsid != null and vo.goodsid != ''">
            and t.goodsid = #{vo.goodsid}
        </if>
        <if test="vo.accountname != null and vo.accountname != ''">
            and (ua.userid like '%' || #{vo.accountname} ||'%' or ua.accountname like '%' || #{vo.accountname} ||'%')
        </if>
        <if test="vo.ishis == 1">
            AND td.ISVALIDDATA = 1
        </if>
        <if test="vo.startdate != null and vo.startdate != ''">
            <![CDATA[and td.tradedate >= replace(#{vo.startdate}, '-')]]>
        </if>
        <if test="vo.enddate != null and vo.enddate != ''">
            <![CDATA[and td.tradedate <= replace(#{vo.enddate}, '-')]]>
        </if>
        order by t.closetime desc
    </select>
</mapper>
