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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.TradeOrderdetail">
        <id column="ORDERID" property="orderid" />
        <result column="TRADEDATE" property="tradedate" />
        <result column="BUILDTYPE" property="buildtype" />
        <result column="PREORDERID" property="preorderid" />
        <result column="CANCELORDERID" property="cancelorderid" />
        <result column="RELATEDID" property="relatedid" />
        <result column="MARKETID" property="marketid" />
        <result column="GOODSID" property="goodsid" />
        <result column="ACCOUNTID" property="accountid" />
        <result column="MEMBERUSERID" property="memberuserid" />
        <result column="BUYORSELL" property="buyorsell" />
        <result column="PRICEMODE" property="pricemode" />
        <result column="ORDERPRICE" property="orderprice" />
        <result column="MARKETMAXSUB" property="marketmaxsub" />
        <result column="ORDERQTY" property="orderqty" />
        <result column="TRADEQTY" property="tradeqty" />
        <result column="CANCELQTY" property="cancelqty" />
        <result column="OPENQTY" property="openqty" />
        <result column="CLOSEQTY" property="closeqty" />
        <result column="OPENTRADEQTY" property="opentradeqty" />
        <result column="CLOSETRADEQTY" property="closetradeqty" />
        <result column="FREEZEMARGIN" property="freezemargin" />
        <result column="UNFREEZEMARGIN" property="unfreezemargin" />
        <result column="FREEZECHARGE" property="freezecharge" />
        <result column="UNFREEZECHARGE" property="unfreezecharge" />
        <result column="OPENFREEZECHARGE" property="openfreezecharge" />
        <result column="CLOSEFREEZECHARGE" property="closefreezecharge" />
        <result column="OPENUNFREEZECHARGE" property="openunfreezecharge" />
        <result column="CLOSEUNFREEZECHARGE" property="closeunfreezecharge" />
        <result column="VALIDTYPE" property="validtype" />
        <result column="VALIDTIME" property="validtime" />
        <result column="VOLUMETYPE" property="volumetype" />
        <result column="OPERATETYPE" property="operatetype" />
        <result column="ORDERTIME" property="ordertime" />
        <result column="ORDERSRC" property="ordersrc" />
        <result column="ORDERSTATUS" property="orderstatus" />
        <result column="OPERATORID" property="operatorid" />
        <result column="UPDATETIME" property="updatetime" />
        <result column="CLIENTORDERTIME" property="clientordertime" />
        <result column="CLIENTTICKET" property="clientticket" />
        <result column="UUID" property="uuid" />
        <result column="CLIENTTYPE" property="clienttype" />
        <result column="RETCODE" property="retcode" />
        <result column="TRADEPROPERTY" property="tradeproperty" />
        <result column="LISTINGSELECTTYPE" property="listingselecttype" />
        <result column="DELISTINGTYPE" property="delistingtype" />
        <result column="MARGINALGORITHM" property="marginalgorithm" />
        <result column="MARGINVALUE" property="marginvalue" />
        <result column="OPENFEEALGORITHM" property="openfeealgorithm" />
        <result column="OPENMEMBERCHARGEVALUE" property="openmemberchargevalue" />
        <result column="OPENEXCHAGECHARGEVALUE" property="openexchagechargevalue" />
        <result column="CLOSEFEEALGORITHM" property="closefeealgorithm" />
        <result column="CLOSEMEMBERCHARGEVALUE" property="closememberchargevalue" />
        <result column="CLOSEEXCHAGECHARGEVALUE" property="closeexchagechargevalue" />
        <result column="OPTIONTYPE" property="optiontype" />
        <result column="PREMIUM" property="premium" />
        <result column="ISPREEXERCISE" property="ispreexercise" />
        <result column="PREEXERCISEPRICE" property="preexerciseprice" />
        <result column="ISCONFIRMEXERCISE" property="isconfirmexercise" />
        <result column="QUOTEID" property="quoteid" />
        <result column="GCACCOUNTID" property="gcaccountid" />
        <result column="SESSIONID" property="sessionid" />
        <result column="FREEZEQTY" property="freezeqty" />
        <result column="AMOUNTFLAG" property="amountflag" />
        <result column="ORDERFLAG" property="orderflag" />
        <result column="ORDERINDEXID" property="orderindexid" />
        <result column="EXCHANGERATE" property="exchangerate" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ORDERID, TRADEDATE, BUILDTYPE, PREORDERID, CANCELORDERID, RELATEDID, MARKETID, GOODSID, ACCOUNTID, MEMBERUSERID, BUYORSELL, PRICEMODE, ORDERPRICE, MARKETMAXSUB, ORDERQTY, TRADEQTY, CANCELQTY, OPENQTY, CLOSEQTY, OPENTRADEQTY, CLOSETRADEQTY, FREEZEMARGIN, UNFREEZEMARGIN, FREEZECHARGE, UNFREEZECHARGE, OPENFREEZECHARGE, CLOSEFREEZECHARGE, OPENUNFREEZECHARGE, CLOSEUNFREEZECHARGE, VALIDTYPE, VALIDTIME, VOLUMETYPE, OPERATETYPE, ORDERTIME, ORDERSRC, ORDERSTATUS, OPERATORID, UPDATETIME, CLIENTORDERTIME, CLIENTTICKET, UUID, CLIENTTYPE, RETCODE, TRADEPROPERTY, LISTINGSELECTTYPE, DELISTINGTYPE, MARGINALGORITHM, MARGINVALUE, OPENFEEALGORITHM, OPENMEMBERCHARGEVALUE, OPENEXCHAGECHARGEVALUE, CLOSEFEEALGORITHM, CLOSEMEMBERCHARGEVALUE, CLOSEEXCHAGECHARGEVALUE, OPTIONTYPE, PREMIUM, ISPREEXERCISE, PREEXERCISEPRICE, ISCONFIRMEXERCISE, QUOTEID, GCACCOUNTID, SESSIONID, FREEZEQTY, AMOUNTFLAG, ORDERFLAG, ORDERINDEXID, EXCHANGERATE
    </sql>

    <select id="queryCommissionSheet" resultType="com.muchinfo.mtp3pojos.vo.query.commission.CommissionSheetResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.commission.CommissionSheetParam">
        select
        t.orderid orderId,t.buildtype buildType,t.buyorsell buyOrSell,t.orderqty orderQty,t.orderprice orderPrice,
        t.tradeqty tradeQty,t.orderstatus orderStatus,t.ordertime orderTime,t.optiontype optionType,t.premium,
        <choose>
            <when test="vo.isHis == null or vo.isHis == 0">
                to_char(to_date(t.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradeDate,
            </when>
            <when test="vo.isHis == 1">
                to_char(to_date(t.histradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradeDate,
            </when>
        </choose>
        g.goodscode || '/' || g.goodsname goodsCodeName,
        g.goodscode,
        ta.accountid || '/' || lo.loginids || '/' || ua.accountname as accountNameId,
        t.orderqty * t.orderprice * g.agreeunit as orderaccount, t.marketmaxsub as marketmaxsub,
        <choose>
            <when test="vo.isHis == null or vo.isHis == 0">
                0 as isHis
            </when>
            <when test="vo.isHis == 1">
                1 as isHis
            </when>
        </choose>
        <choose>
            <when test="vo.isHis == null or vo.isHis == 0">
                from TRADE_ORDERDETAIL t
            </when>
            <when test="vo.isHis == 1">
                from HIS_TRADE_ORDERDETAIL t
            </when>
        </choose>
        left join market m on t.marketid = m.marketid
        left join goods g on t.goodsid = g.goodsid
        left join taaccount ta on ta.accountid = t.accountid
        left join useraccount ua on ta.relateduserid = ua.userid
        left join VIEW_USER_LOGINIDS lo on ua.userid = lo.userid
        <where>
            t.ordersrc not in (10)
            <if test="vo.isHis == 1">
                AND t.ISVALIDDATA = 1
            </if>
            <if test="vo.orderStatus != null and vo.orderStatus != ''">
                AND t.orderstatus = #{vo.orderStatus,jdbcType=INTEGER}
            </if>
            <if test="vo.operateType == 2">
                and t.operatetype = 2
            </if>
            <if test="vo.marketId != -1">
                and t.marketid = #{vo.marketId}
            </if>
            <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.tradeDate != null and vo.tradeDate != ''">
                and t.tradedate = #{vo.tradeDate}
            </if>
            <if test="vo.buyOrSell != null">
                AND t.buyorsell = #{vo.buyOrSell}
            </if>
            <if test="vo.goodsId != null">
                and t.goodsid = #{vo.goodsId}
            </if>
            <if test="vo.buildType != null and vo.buildType != ''">
                AND t.buildtype = #{vo.buildType}
            </if>
            <if test="vo.orderId != null and vo.orderId != ''">
                AND t.orderid = #{vo.orderId}
            </if>
            <if test="vo.accountId != null and vo.accountId != ''">
                AND t.accountid = #{vo.accountId}
            </if>
            <if test="vo.excludeAccountIds!=null and vo.excludeAccountIds.length>0">
                <foreach collection="vo.excludeAccountIds" item="item" open=" and ta.accountid not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.searchStatus != null and vo.searchStatus != '' and vo.searchStatus == 1 ">
                and t.orderstatus in (7,8,9)
            </if>
            <if test="vo.searchStatus != null and vo.searchStatus != '' and vo.searchStatus == 2 ">
                and t.orderstatus in (3,7,12)
            </if>
            <if test="vo.startDateHis != null and vo.startDateHis != ''">
                <![CDATA[and t.TRADEDATE >= replace(#{vo.startDateHis}, '-')]]>
            </if>
            <if test="vo.endDateHis != null and vo.endDateHis != ''">
                <![CDATA[and t.TRADEDATE <= replace(#{vo.endDateHis}, '-')]]>
            </if>
            <if test="vo.filterTrademodeList!=null and vo.filterTrademodeList.size>0">
                <foreach collection="vo.filterTrademodeList" item="item" open="and m.trademode not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </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>
        </where>
        <choose>
            <when test="vo.sortField != null and vo.sortField == 1 ">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by t.buyorsell asc
                    </when>
                    <otherwise>
                        order by t.buyorsell desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 2 ">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by t.orderprice asc
                    </when>
                    <otherwise>
                        order by t.orderprice desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 3 ">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by t.orderqty asc
                    </when>
                    <otherwise>
                        order by t.orderqty desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 4 ">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by t.tradeqty asc
                    </when>
                    <otherwise>
                        order by t.tradeqty desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 5 ">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by t.orderid asc
                    </when>
                    <otherwise>
                        order by t.orderid desc
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                order by t.orderid desc
            </otherwise>
        </choose>
    </select>

    <select id="queryCoinSheet" resultType="com.muchinfo.mtp3pojos.vo.query.commission.CommissionSheetResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.commission.CommissionSheetParam">
        select
        t.orderid orderId,
        t.listtype listType,
        t.buyorsell buyOrSell,
        t.tradeaccountid tradeAccountId,
        t.orderqty orderQty,
        t.orderprice orderPrice,
        t.tradeqty tradeQty,
        t.ordertime orderTime,
        t.orderstatus orderStatus,
        to_char(to_date(t.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradeDate,
        <choose>
            <when test="vo.isHis == null or vo.isHis == 0">
                0 as isHis
            </when>
            <when test="vo.isHis == 1">
                1 as isHis
            </when>
        </choose>
        <choose>
            <when test="vo.isHis !=null and vo.isHis==0">
                from Coin_OrderDetail t
            </when>
            <otherwise>
                from His_Coin_OrderDetail t
            </otherwise>
        </choose>
        left join taaccount ta on ta.accountid = t.tradeaccountid
        left join useraccount ua on ta.userid = ua.userid
        <where>
            <if test="vo.isHis == 1">
                AND t.ISVALIDDATA = 1
            </if>
            <if test="vo.marketId != -1">
                and t.marketid = #{vo.marketId}
            </if>
            <if test="vo.orderId != null and vo.orderId != ''">
                AND t.orderid = #{vo.orderId}
            </if>
            <if test="vo.accountId != null and vo.accountId != ''">
                AND t.accountid = #{vo.accountId}
            </if>
            <if test="vo.orderStatus != null and vo.orderStatus != ''">
                AND t.orderstatus = #{vo.orderStatus}
            </if>
            <if test="vo.buyOrSell != null">
                AND t.buyorsell = #{vo.buyOrSell}
            </if>
            <if test="vo.ishis !=null and vo.ishis==1">
                AND t.isvaliddata = 1
            </if>
            <if test="vo.startDateHis != null and vo.startDateHis != '' and vo.endDateHis != null and vo.endDateHis != '' ">
                <![CDATA[and t.histradedate >= replace(#{vo.startDateHis}, '-')
                         and t.histradedate <= replace(#{vo.endDateHis}, '-') ]]>
            </if>
        </where>
        order by t.orderid desc
    </select>
    <select id="commissionSheetQueryDetail" resultType="com.muchinfo.mtp3pojos.vo.query.commission.CommissionSheetResultDetail">
        select
        t.orderid,t.buildtype,t.accountid,t.preorderid,t.relatedid,
        t.buyorsell,t.pricemode,t.operatetype,t.orderprice,
        t.orderqty,t.validtype,t.validtime,t.ordersrc,
        t.clientticket,t.ordertime,t.orderstatus,t.freezecharge,
        t.freezemargin,t.openfreezecharge,t.closefreezecharge,
        t.unfreezemargin,t.unfreezecharge,t.listingselecttype, t.delistingtype, t.marginalgorithm,
        t.marginvalue, t.openfeealgorithm, t.openmemberchargevalue, t.openexchagechargevalue, t.closefeealgorithm,
        t.closememberchargevalue, t.closeexchagechargevalue,
        t.openunfreezecharge,t.closeunfreezecharge,t.optiontype,t.premium,
        t.retcode,t.uuid,t.cancelqty,t.cancelorderid,t.marketid,t.goodsid,g.goodscode,
        u.username,t.openqty,t.closeqty,t.tradeqty, g.refgoodsid, g.swapdays,
        ua.userid as uuserid,g.goodsname,g.goodscode,m.marketname,
        ua.accountname,m.trademode,parent.accountname as parentusername
        <choose>
            <when test="isHis == null or isHis == 0">
                from TRADE_ORDERDETAIL t
            </when>
            <when test="isHis == 1">
                from HIS_TRADE_ORDERDETAIL t
            </when>
        </choose>
        left join systemmanager u on t.operatorid=u.autoid
        left join taaccount ta on ta.accountid = t.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join useraccount parent on parent.userid = ua.parentuserid
        left join market m on t.marketid = m.marketid
        left join goods g on g.goodsid = t.goodsid
        where t.orderid = #{orderId}
        <if test=" isHis == 1">
            AND t.ISVALIDDATA = 1
            AND t.histradedate = #{tradeDate}
        </if>
    </select>

    <select id="queryHisTradeList" resultType="com.muchinfo.mtp3pojos.vo.query.commission.TradeHisResult">
        select
        td.tradeid,td.tradeamount,
        td.tradeqty,td.tradeprice,
        td.openexchagechargevalue,
        td.openmemberchargevalue,
        td.closememberchargevalue,
        td.closeexchagechargevalue,
        td.goodsid,td.marketid,td.tradetype,
        to_char(to_date(td.histradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as histradedate,
        td.buyorsell,td.buildtype,td.premium,
        td.tradetime,td.openfeealgorithm,
        td.matchaccountid,td.optiontype,
        td.opencharge,td.closecharge,
        g.goodsname,g.goodscode,
        ua.AccountName, to_char(td.opencharge + td.closecharge, '999999990.99') as chargevalue
        from HIS_TRADE_TRADEDETAIL td
        left join goods g on g.goodsid=td.goodsid
        left join taaccount ta on ta.accountid = td.accountid
        left join useraccount ua on ta.userid = ua.userid
        <where>
            <if test="orderId != null">
                td.orderid = #{orderId}
            </if>
        </where>
        order by td.tradetime desc
    </select>


    <select id="queryTradeTradeDetails" resultType="com.muchinfo.mtp3pojos.vo.query.commission.TradeResult">
        select
        td.tradeid,td.tradeamount,
        td.tradeqty,td.tradeprice,
        td.openexchagechargevalue,
        td.openmemberchargevalue,
        td.closememberchargevalue,
        td.closeexchagechargevalue,
        td.goodsid,td.marketid,
        td.buyorsell,td.buildtype,
        td.tradetime,td.openfeealgorithm,
        td.matchaccountid,td.tradetype,
        td.opencharge,td.closecharge, td.goodsid,
        g.goodsname,g.goodscode,td.premium,td.optiontype,
        ua.AccountName, to_char(td.opencharge + td.closecharge, '999999990.99') as chargevalue
        from trade_tradedetail td
        left join goods g on g.goodsid=td.goodsid
        left join taaccount ta on ta.accountid = td.accountid
        left join useraccount ua on ta.userid = ua.userid
        <where>
            <if test="orderId != null and orderId != ''">
                AND td.orderId = #{orderId}
            </if>
        </where>
    </select>

    <select id="queryContractSheet" resultType="com.muchinfo.mtp3pojos.vo.query.contract.ContractSheetResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.contract.ContractSheetParam">
        select
        td.tradeid,td.accountid,td.buyorsell,td.buildtype,td.tradetype,
        td.tradeprice,td.tradeqty, td.tradeamount,td.tradetime,
        g.goodscode,
        g.goodscode || '/' || g.goodsname goodsCodeName,
        vlog.loginids as loginid,
        ua.accountname as currentname,td.closepl,
        td.opencharge + td.closecharge as chargevalue,
        memberua.userid || '/' || memberua.accountname as membername,
        <choose>
            <when test="vo.isSwap !=null and vo.isSwap==1">
                od.marketmaxsub,
            </when>
            <otherwise>
                0 as marketmaxsub,
            </otherwise>
        </choose>
        <choose>
            <when test="vo.isHis == null or vo.isHis == 0">
                0 as ishis
            </when>
            <when test="vo.isHis == 1">
                1 as ishis
            </when>
        </choose>
        <choose>
            <when test="vo.isHis == null or vo.isHis == 0">
                from trade_tradedetail td
                left join trade_orderdetail od on od.orderid = td.orderid
            </when>
            <when test="vo.isHis == 1">
                from HIS_trade_tradedetail td
                <if test="vo.isSwap !=null and vo.isSwap==1">
                    left join his_trade_orderdetail od on od.isvaliddata = 1 and od.orderid = td.orderid
                </if>
            </when>
        </choose>
        left join goods g on g.goodsid=td.goodsid
        left join taaccount ta on ta.accountid = td.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join useraccount rua on ta.relateduserid = rua.userid
        left join market m on td.marketid = m.marketid
        left join useraccount memberua on memberua.userid = ua.memberuserid
        left join view_user_loginids vlog on vlog.userid = ta.userid
        <where>
            <if test="vo.isHis == 1">
                AND td.ISVALIDDATA = 1
            </if>
            <if test="vo.marketId != -1">
                AND td.marketid = #{vo.marketId}
            </if>
            <if test="vo.accountId != null and vo.accountId != ''">
                AND td.accountid = #{vo.accountId}
            </if>
            <if test="vo.tradeId != null">
                AND td.tradeid = #{vo.tradeId}
            </if>
            <if test="vo.tradeDate != null and vo.tradeDate != '' ">
                AND td.tradedate = #{vo.tradeDate}
            </if>
            <if test="vo.goodsId != null">
                and td.goodsid = #{vo.goodsId}
            </if>
            <if test="vo.buyOrSell != null">
                AND td.buyorsell = #{vo.buyOrSell}
            </if>
            <if test="vo.buildType != null">
                AND td.buildtype = #{vo.buildType}
            </if>
            <if test="vo.areaUserid != null and vo.areaUserid != 0 and vo.areaUserid != 1">
                and (rua.memberuserid = #{vo.areaUserid} or rua.parentuserid = #{vo.areaUserid})
            </if>
            <if test="vo.filterTrademodeList!=null and vo.filterTrademodeList.size>0">
                <foreach collection="vo.filterTrademodeList" item="item" open="and m.trademode not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.tradeTypes != null and vo.tradeTypes !=''">
                AND td.tradetype in (${vo.tradeTypes})
            </if>
            <if test="vo.areaUserName != null and vo.areaUserName !=''">
                and ua.subarealevelpath like '%,' || #{vo.areaUserName} || ',%'
            </if>
            <if test="vo.startDateHis != null and vo.startDateHis != ''">
                <![CDATA[and td.tradedate >= replace(#{vo.startDateHis}, '-')]]>
            </if>
            <if test="vo.endDateHis != null and vo.endDateHis != ''">
                <![CDATA[and td.tradedate <= replace(#{vo.endDateHis}, '-')]]>
            </if>
            <if test="vo.feeSearchType != null and vo.feeSearchType == 1">
                and (td.opencharge + td.closecharge) = 0
            </if>
            <if test="vo.feeSearchType != null and vo.feeSearchType == 2">
                and (td.opencharge + td.closecharge) &lt;&gt; 0
            </if>
        </where>
        <choose>
            <when test="vo.sortField != null and vo.sortField == 1">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by td.tradeid asc
                    </when>
                    <otherwise>
                        order by td.tradeid desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 2">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by td.buyorsell asc
                    </when>
                    <otherwise>
                        order by td.buyorsell desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 3">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by td.tradeprice asc
                    </when>
                    <otherwise>
                        order by td.tradeprice desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 4">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by td.tradeqty asc
                    </when>
                    <otherwise>
                        order by td.tradeqty desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 5">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by td.tradeamount asc
                    </when>
                    <otherwise>
                        order by td.tradeamount desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 6">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by (td.opencharge + td.closecharge) asc
                    </when>
                    <otherwise>
                        order by (td.opencharge + td.closecharge) desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortField != null and vo.sortField == 7">
                <choose>
                    <when test="vo.sortType != null and vo.sortType == 0 ">
                        order by td.closepl asc
                    </when>
                    <otherwise>
                        order by td.closepl desc
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                order by td.tradeid desc
            </otherwise>
        </choose>
    </select>

    <select id="contractSheetQueryDetail" resultType="com.muchinfo.mtp3pojos.vo.query.contract.ContractSheetResultDetail">
        select
            td.tradeid, m.trademode,to_char(to_date(td.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate,
            td.accountid,ua.accountname,  td.tradetype, td.orderid,g.goodsname,g.goodscode,td.buildtype,
            td.buyorsell,td.tradeprice,  td.optiontype, td.isconfirmexercise, td.premium,td.tradeqty,
            td.goodsid, td.tradeamount,td.closepl,td.tradetime, td.matchaccountid,g.refgoodsid,g.swapdays,
            td.opencharge,td.closecharge,td.openfeealgorithm,td.openexchagechargevalue,td.openmemberchargevalue,
            td.closefeealgorithm,td.closeexchagechargevalue,td.closememberchargevalue,
            td.openqty,td.closeqty,parent.accountname as parentusername,m.marketname
        <choose>
            <when test="ishis !=null and ishis == 0">
                from TRADE_TRADEDETAIL td
            </when>
            <when test="ishis == 1">
                from HIS_TRADE_TRADEDETAIL td
            </when>
        </choose>
        left join taaccount ta on ta.accountid = td.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join useraccount parent on parent.userid = ua.parentuserid
        left join market m on td.marketid = m.marketid
        left join goods g on g.goodsid=td.goodsid
        where td.tradeid = #{tradeid}
        and td.buyorsell = #{buyorsell}
    </select>
    <select id="queryTradeClose" resultType="com.muchinfo.mtp3pojos.vo.query.contract.ContractTradeResult">
        select
            t.opentradeid,
            t.buyorsell,
            t.closeprice,
            t.openprice,
            t.holderprice,
            t.closetime
        <choose>
            <when test="ishis !=null and ishis == 0">
                from TRADE_CLOSEDETAIL t
            </when>
            <when test="ishis == 1">
                from HIS_TRADE_CLOSEDETAIL t
            </when>
        </choose>
        <where>
            <if test="tradeid != null">
                and t.tradeid = #{tradeid,jdbcType=DECIMAL}
            </if>
            <if test="buyorsell != null">
                and t.buyorsell = #{buyorsell,jdbcType=DECIMAL}
            </if>
        </where>
        order by t.closetime desc
    </select>

    <select id="selectMemberUserName" parameterType="java.lang.Long"
            resultType="java.lang.String">
        select t.userid || '/' || t.accountname as accountname
        from useraccount t
                 left join useraccount u on u.memberuserid = t.userid
                 left join taaccount ta on ta.userid = u.userid
        where ta.accountid = #{accountid}
    </select>


    <select id="queryTradeHolderDetailList" resultType="com.muchinfo.mtp3pojos.vo.query.position.PositionSheetResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.position.PositionSheetParam">
        select
            t.tradeid,
            to_char(to_date(t.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate,
            ta.accountid || '/' || la.loginids || '/' || ua.accountname as accountNameId,
            g.goodscode || '/' || g.goodsname goodsCodeName,
            g.goodscode,
            t.buyorsell,
            t.openqty,
            t.openprice,
            t.tradeamount,
            t.holderqty,
            t.holderprice,
            t.holderamount,
            t.holderdays,
            t.tradetime,
            ua.usertype,
            parent.accountname as parentusername,
            m.marketname,
            t.expiredate,
            t.expiretype,
            t.optiontype,
            t.premium,
            0 as ishis,
            g.refgoodscode,
            g.agreeUnit,
            g.goodsid,
            t.preExercisePrice,
            t.isPreExercise,
            mem.accountname as memberusername,
            t.isConfirmExercise
        from trade_holderdetail t
        left join goods g on t.goodsid = g.goodsid
        left join taaccount ta on ta.accountid = t.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join useraccount rua on ta.relateduserid = rua.userid
        left join useraccount parent on parent.userid = ua.parentuserid
        left join useraccount mem on ua.memberuserid = mem.userid
        left join market m on t.marketid = m.marketid
        left join VIEW_USER_LOGINIDS la on ta.userid = la.userid
        <where>
            <if test="vo.marketId != -1">
                and t.marketid = #{vo.marketId}
            </if>
            <if test="vo.goodsId != null">
                and t.goodsid = #{vo.goodsId}
            </if>
            <if test="vo.buyOrSell != null">
                and t.buyorsell = #{vo.buyOrSell}
            </if>
            <if test="vo.loginId != null and vo.loginId != '' ">
                and ua.userid in (select la.userid from loginaccount la where la.loginid like
                '%'||#{vo.loginId}||'%' )
            </if>
            <if test="vo.tradeId != null and vo.tradeId != ''">
                and t.tradeid like
                '%'||#{vo.tradeId}||'%'
            </if>
            <if test="vo.parentAreaId != null and vo.parentAreaId !=''">
                and (ua.parentuserid in (select userid from useraccount uo
                start with uo.userid = #{vo.parentAreaId}
                connect by prior uo.userid =uo.parentuserid and uo.usertype!=2) or ua.userid = #{vo.parentAreaId})
            </if>
            <if test="vo.accountId != null">
                and t.accountid = #{vo.accountId}
            </if>
            <if test="vo.validHolderQty != null and vo.validHolderQty == 1">
                <![CDATA[ and t.holderqty > 0 ]]>
            </if>
            <if test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''">
                <![CDATA[and t.tradedate >= replace(#{vo.startDate}, '-')
                         and t.tradedate <= replace(#{vo.endDate}, '-') ]]>
            </if>
            <if test="vo.filterTrademodeList!=null and vo.filterTrademodeList.size>0">
                <foreach collection="vo.filterTrademodeList" item="item" open="and m.trademode not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.areaUserid != null and vo.areaUserid != 0 and vo.areaUserid != 1">
                and (rua.memberuserid = #{vo.areaUserid} or rua.parentuserid =  #{vo.areaUserid})
            </if>
        </where>
        order by t.tradetime desc,t.accountid desc
    </select>

    <select id="queryHisTradeHolderDetaileList" resultType="com.muchinfo.mtp3pojos.vo.query.position.PositionSheetResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.position.PositionSheetParam">
        select
        t.tradeid,
        t.accountid,
        t.buyorsell,
        t.openqty,
        t.holderqty,
        t.goodsid,t.marketid,
        t.holderprice,
        t.openprice,
        t.holderamount,
        t.tradetime,t.holderdays,
        t.tradeproperty,
        t.premium,t.optiontype,
        t.expiretype,t.expiredate,
        t.tradeamount,
        to_char(to_date(t.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate,
        m.marketname,
        g.goodscode,g.goodsname,
        ua.accountname,
        t.histradedate as histradedate, la.loginids loginids,
        ta.accountid || '/' || la.loginids || '/' || ua.accountname as accountNameId,
        g.goodscode || '/' || g.goodsname goodsCodeName,
        mem.accountname as memberusername, mu.accountname as parentusername,
        1 as ishis
        from HIS_TRADE_HOLDERDETAIL t
        left join goods g on t.goodsid = g.goodsid
        left join taaccount ta on ta.accountid = t.accountid
        left join useraccount ua on ta.relateduserid = ua.userid
        left join market m on t.marketid = m.marketid
        left join VIEW_USER_LOGINIDS la on ta.userid = la.userid
        left join useraccount mem on ua.memberuserid = mem.userid
        left join useraccount mu on mem.parentuserid = mu.userid and mem.parentuserid != 0
        <where>
            <if test="vo.marketId != -1">
                and t.marketid = #{vo.marketId}
            </if>
            <if test="vo.goodsId != null">
                and t.goodsid = #{vo.goodsId}
            </if>
            <if test="vo.buyOrSell != null">
                and t.buyorsell = #{vo.buyOrSell}
            </if>
            <if test="vo.loginId != null and vo.loginId != '' ">
                and ua.userid in (select la.userid from loginaccount la where la.loginid like
                '%'||#{vo.loginId}||'%' )
            </if>
            <if test="vo.tradeId != null and vo.tradeId != ''">
                and t.tradeid like
                '%'||#{vo.tradeId}||'%'
            </if>
            <if test="vo.parentAreaId != null and vo.parentAreaId !=''">
                and (ua.parentuserid in (select userid from useraccount uo
                start with uo.userid = #{vo.parentAreaId}
                connect by prior uo.userid =uo.parentuserid and uo.usertype!=2) or ua.userid = #{vo.parentAreaId})
            </if>
            <if test="vo.accountId != null">
                and t.accountid = #{vo.accountId}
            </if>
            <if test="vo.validHolderQty != null and vo.validHolderQty == 1">
                <![CDATA[ and t.holderqty > 0 ]]>
            </if>
            <if test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''">
                <![CDATA[and t.tradedate >= replace(#{vo.startDate}, '-')
                         and t.tradedate <= replace(#{vo.endDate}, '-') ]]>
            </if>
            <if test="vo.areaUserid != null and vo.areaUserid != 0 and vo.areaUserid != 1">
                <choose>
                    <when test="vo.selfSupport!=null and vo.selfSupport==3">
                        and ua.memberuserid in (select userid from useraccount u1 where u1.parentuserid=#{vo.areaUserid}
                        or u1.userid=#{vo.areaUserid}
                        )
                    </when>
                    <otherwise>
                        and (ua.userid in (
                        select userid
                        from useraccount uo
                        start with uo.parentuserid = #{vo.areaUserid}
                        connect by prior uo.userid =uo.parentuserid and uo.usertype !=2 ))
                    </otherwise>
                </choose>
            </if>
        </where>
        order by t.tradeid desc
    </select>

    <!-- 持仓单历史查询 -->
    <select id="queryHisTradeHolderDetaileByPage"  resultType="com.muchinfo.mtp3pojos.vo.query.position.PositionSheetResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.position.PositionSheetParam">
        select
        t.tradeid, t.accountid, t.buyorsell, t.holderqty, t.goodsid, t.marketid, t.holderprice, t.openprice,
        t.holderamount,
        t.tradetime,t.holderdays, t.tradeproperty, t.premium,t.optiontype, t.reckonpl, t.openqty,t.tradeamount,
        t.expiretype, t.expirecycle, t.expiredate, t.holdercredit, t.releaseholdercredit,
        t.reckondate as tradedate,
        t.reckondate as histradedate,
        g.goodscode || '/' || g.goodsname goodsCodeName,
        m.marketname, ua.usertype, g.goodscode, ua.accountname,
        ua.userid || '/' || lo.loginids || '/' ||ua.accountname as accountNameId,
        mem.accountname as memberusername, mu.accountname as parentusername,
        nvl(rpl.reckonprice, 0) reckonprice,
        nvl(t.defercharge, 0) + nvl(t.interestcharge, 0) defercharge,
        decode(nvl(rpl.reckonprice, 0), 0, 0, decode(t.buyorsell, 0, 1, -1) * (nvl(rpl.reckonprice, 0) - t.holderprice)
        * g.agreeunit * t.holderqty) actuallYpl,
        1 as ishis
        from Reckon_DayHolderDetail t
        left join goods g on t.goodsid = g.goodsid
        left join taaccount ta on ta.accountid = t.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join useraccount rua on ta.relateduserid = rua.userid
        left join market m on t.marketid = m.marketid
        left join useraccount mem on ua.memberuserid = mem.userid
        left join useraccount mu on mem.parentuserid = mu.userid and mem.parentuserid != 0
        left join Reckon_PriceLog rpl on g.goodsid = rpl.goodsid and rpl.tradedate = t.reckondate
        left join VIEW_USER_LOGINIDS lo on ua.userid = lo.userid
        <where>
            <if test="vo.validHolderQty != null and vo.validHolderQty == 1">
                <![CDATA[ and t.holderqty > 0 ]]>
            </if>
            <if test="vo.tradeId != null and vo.tradeId != ''">
                and t.tradeid like
                '%'||#{vo.tradeId}||'%'
            </if>
            <if test="vo.marketId != -1">
                and t.marketid = #{vo.marketId}
            </if>
            <if test="vo.goodsId != null">
                and t.goodsid = #{vo.goodsId}
            </if>
            <if test="vo.accountId != null">
                and t.accountid = #{vo.accountId}
            </if>
            <if test="vo.areaUserid != null and vo.areauserid != 0 and vo.areaUserid != 1">
                and rua.memberuserid = #{vo.areaUserid}
            </if>
            <if test="vo.filterTrademodeList!=null and vo.filterTrademodeList.size>0">
                <foreach collection="vo.filterTrademodeList" item="item" open="and m.trademode not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.parentAreaId != null and vo.parentAreaId !=''">
                and (ua.parentuserid in (select userid from useraccount uo
                start with uo.userid = #{vo.parentAreaId}
                connect by prior uo.userid =uo.parentuserid and uo.usertype!=2) or ua.userid = #{vo.parentAreaId})
            </if>
            <if test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''">
                and to_date(t.reckondate,'yyyyMMdd') between to_date(#{vo.startDate},'yyyy-MM-dd')
                and to_date(#{vo.endDate},'yyyy-MM-dd')
            </if>
            <if test="vo.buyOrSell != null">
                and t.buyorsell = #{vo.buyOrSell}
            </if>
            <if test="vo.loginId != null and vo.loginId != '' ">
                and ua.userid in (select la.userid from loginaccount la where la.loginid like
                '%'||#{vo.loginId}||'%' )
            </if>
        </where>
        order by t.tradetime desc,t.reckondate desc,t.accountid desc
    </select>

    <select id="queryTradeHolderDetailById" resultType="com.muchinfo.mtp3pojos.vo.query.position.PositionSheetResultDetail">
        select t.tradeid,
            <choose>
                <when test="ishis == null or ishis == 0">
                    t.tradedate as tradedate,
                </when>
                <when test="ishis == 1">
                    t.reckondate as tradedate,
                </when>
            </choose>
            ua.accountname,
            t.accountid,
            mem.accountname  as memberusername,
            mu.accountname as parentusername,
            m.marketname,
            g.goodscode,
            g.goodsname,
            t.optiontype,
            t.buyorsell,
            t.holderdays,
            t.tradetime,
            t.openprice,
            t.holderprice,
            t.premium,
            t.openqty,
            t.holderqty,
            t.expiretype,
            t.expirecycle,
            to_char(to_date(t.expiredate, 'yyyy-MM-dd'), 'yyyy-MM-dd') as expiredate,
            g.refgoodsid,
            g.swapdays,
            m.trademode
        <choose>
            <when test="ishis == null or ishis == 0">
                from trade_holderdetail t
            </when>
            <when test="ishis == 1">
                from Reckon_DayHolderDetail t
            </when>
        </choose>
             left join taaccount ta on ta.accountid = t.accountid
             left join useraccount ua on ta.userid = ua.userid
             left join useraccount mem on ua.memberuserid = mem.userid
             left join useraccount mu on mem.parentuserid = mu.userid and mem.parentuserid != 0
             left join market m on t.marketid = m.marketid
             left join goods g on g.goodsid = t.goodsid
        where
            t.tradeid = #{tradeid}
            and t.buyorsell = #{buyorsell}
            <if test="histradedate != null and histradedate !='' ">
                and t.reckondate = #{histradedate}
            </if>
    </select>


    <select id="queryTradePositionPage" resultType="com.muchinfo.mtp3pojos.vo.query.position.PositionSumSheetResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.position.PositionSumSheetParam">
        select
            g.goodscode || '/' || g.goodsname goodsCodeName,
            t.accountid || '/' ||ua.accountname as accountidname,
            t.goodsid,
            t.holdertype,t.tradeproperty,t.buypositionqty,t.buyholderamount,t.buycurpositionqty,
            t.buycurholderamount,t.sellpositionqty,t.sellholderamount,t.sellcurpositionqty,
            t.sellcurholderamount,ua.accountname,
            lo.loginids , g.agreeunit agreeunit,g.goodscode,
            g.goodunitid goodsunitid,
            case when t.buycurpositionqty=0 then null
            else ROUND(t.buycurholderamount / (t.buycurpositionqty * g.agreeunit), g.decimalplace)
            end buycuravgprice,
            case when t.sellcurpositionqty=0 then null
            else ROUND(t.sellcurholderamount / (t.sellcurpositionqty * g.agreeunit), g.decimalplace)
            end sellcuravgprice
        from tradeposition t
        left join goods g on t.goodsid = g.goodsid
        left join taaccount ta on t.accountid = ta.accountid
        left join USERACCOUNT ua on ta.userid = ua.userid
        left join useraccount rua on ta.relateduserid = rua.userid
        join market m on g.marketid = m.marketid
        left join VIEW_USER_LOGINIDS lo on rua.userid = lo.userid
        <where>
            m.trademode not in (40,41,48,51)
            <if test="vo.marketid != null and vo.marketid != '' ">
                and g.marketid = #{vo.marketid,jdbcType=DECIMAL}
            </if>
            <if test="vo.goodsid !=null and vo.goodsid !='' ">
                and t.goodsid = #{vo.goodsid}
            </if>
            <if test="vo.accountid !=null and vo.accountid !='' ">
                and t.accountid = #{vo.accountid}
            </if>
            <if test="vo.accname !=null and vo.accname !='' ">
                and (ua.accountname like '%'||#{vo.accname,jdbcType=VARCHAR}||'%'
                or t.accountid like '%'||#{vo.accname,jdbcType=INTEGER}||'%')
            </if>
            <if test="vo.areauseridQuery != null">
                and (ua.parentuserid in (select userid from useraccount uo
                start with uo.userid = #{vo.areauseridQuery}
                connect by prior uo.userid =uo.parentuserid and uo.usertype !=2) or
                ua.userid = #{vo.areauseridQuery})
            </if>
            <if test="vo.loginid != null and vo.loginid != '' ">
                <!-- and la.loginid = #{loginid} -->
                and ua.userid in (select la.userid from loginaccount la where la.loginid like
                '%'||#{vo.loginid}||'%' )
            </if>
            <if test="vo.areauserid != null and vo.areauserid != '' ">
                and (rua.memberuserid = #{vo.areauserid} or rua.parentuserid = #{vo.areauserid})
            </if>
            <if test="vo.filterTrademodeList!=null and vo.filterTrademodeList.size>0">
                <foreach collection="vo.filterTrademodeList" item="item" open="and m.trademode not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
        </where>
        <choose>
            <when test="vo.sortfield != null and vo.sortfield == 1">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.buycurpositionqty asc
                    </when>
                    <otherwise>
                        order by t.buycurpositionqty desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 2">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.buycurholderamount asc
                    </when>
                    <otherwise>
                        order by t.buycurholderamount desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 3">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.sellcurpositionqty asc
                    </when>
                    <otherwise>
                        order by t.sellcurpositionqty desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 4">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.sellcurholderamount asc
                    </when>
                    <otherwise>
                        order by t.sellcurholderamount desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 5">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by abs(t.buycurpositionqty - t.sellcurpositionqty) asc
                    </when>
                    <otherwise>
                        order by abs(t.buycurpositionqty - t.sellcurpositionqty) desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 6">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by (case when (t.buycurpositionqty - t.sellcurpositionqty) >=0 then 0 else 1 end) asc
                    </when>
                    <otherwise>
                        order by (case when (t.buycurpositionqty - t.sellcurpositionqty) >=0 then 0 else 1 end) desc
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                order by abs(t.buycurpositionqty-t.sellcurpositionqty) desc, t.accountid desc
            </otherwise>
        </choose>
    </select>

    <select id="queryByAccount" parameterType="com.muchinfo.mtp3pojos.vo.query.accounttransaction.AccountTransactionQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.accounttransaction.AccountTransactionQueryResult">
        SELECT
        '--' as tradedate,
        ua.userid || '/' || t.accountid || '/' || ua.accountname as userfullname, g.goodscode,
        g.goodscode || '/' || g.goodsname as  goodsfullname,
        case
        when  (ua.usertype = 5 and pua.userid != mua.userid) then  pua.userid || '/' || to_char(pua.accountname)
        else '--'
        end as parentname,

        case
        when (ua.usertype = 5) then  mua.userid || '/' || to_char(mua.accountname)
        else '--'
        end as membername,

        case
        when (m.trademode=52) then sum(t.tradeqty * g.agreeunit) || en.enumdicname
        else sum(t.tradeqty) || ''
        end as tradeqtysum,

        sum(t.tradeamount) as tradeamountsum,
        sum(t.closepl) as closeplsum,
        sum(t.opencharge + t.closecharge) as chargesum
        FROM RECKON_DAYACCOUNTTRADE t
        inner join goods g on g.goodsid = t.goodsid
        left join enumdicitem en on en.enumdiccode='goodsunit' and en.enumitemname = g.goodunitid
        inner join market m on m.marketid = g.marketid
        inner join taaccount ta on ta.accountid = t.accountid
        inner join useraccount ua on ta.userid = ua.userid
        inner join useraccount pua on pua.userid = ua.parentuserid
        inner join useraccount mua on mua.userid = ua.memberuserid
        <where>
            <if test="vo.startdate != null and vo.startdate != ''">
                <![CDATA[AND t.reckondate >= #{vo.startdate}]]>
            </if>
            <if test="vo.enddate != null and vo.enddate != ''">
                <![CDATA[AND t.reckondate <= #{vo.enddate}]]>
            </if>
            <if test="vo.goodsid != null">
                and g.goodsid=#{vo.goodsid}
            </if>
            <if test="vo.accountid != null and vo.accountid !=''">
                and ta.accountid = #{vo.accountid}
            </if>
            <if test="vo.excludeAccountIds!=null and vo.excludeAccountIds.length>0">
                <foreach collection="vo.excludeAccountIds" item="item" open=" and ta.accountid not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.userid != null and vo.userid != ''">
                and (pua.userid in
                (select uo.userid from useraccount uo where uo.usertype in (1,2,3) start with uo.userid = #{vo.userid} connect by prior uo.userid = uo.parentuserid)
                or ua.userid = #{vo.userid})
            </if>
        </where>
        group by t.accountid, ua.userid, ua.accountname, t.goodsid, g.goodscode, g.goodsname,
        ua.usertype, pua.userid, pua.accountname, mua.userid, mua.accountname, m.trademode, en.enumdicname
        order by to_char(ua.userid)
    </select>

    <select id="queryByAccountDate" parameterType="com.muchinfo.mtp3pojos.vo.query.accounttransaction.AccountTransactionQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.accounttransaction.AccountTransactionQueryResult">
        SELECT
        t.reckondate as tradedate,
        ua.userid || '/' || t.accountid || '/' || ua.accountname as userfullname, g.goodscode,
        g.goodscode || '/' || g.goodsname as goodsfullname,
        case
        when  (ua.usertype = 5 and pua.userid != mua.userid) then  pua.userid || '/' || to_char(pua.accountname)
        else '--'
        end as parentname,

        case
        when (ua.usertype = 5) then  mua.userid || '/' || to_char(mua.accountname)
        else '--'
        end as membername,

        case
        when (m.trademode=52) then sum(t.tradeqty * g.agreeunit) || en.enumdicname
        else  sum(t.tradeqty) || ''
        end as tradeqtysum,

        sum(t.tradeamount) as tradeamountsum,
        sum(t.closepl) as closeplsum,
        sum(t.opencharge + t.closecharge) as chargesum
        FROM RECKON_DAYACCOUNTTRADE t
        inner join goods g on g.goodsid = t.goodsid
        inner join market m on m.marketid = g.marketid
        left join enumdicitem en on en.enumdiccode='goodsunit' and en.enumitemname = g.goodunitid
        inner join taaccount ta on ta.accountid = t.accountid
        inner join useraccount ua on ta.userid = ua.userid
        inner join useraccount pua on pua.userid = ua.parentuserid
        inner join useraccount mua on mua.userid = ua.memberuserid
        <where>
            <if test="vo.startdate != null and vo.startdate != ''">
                <![CDATA[AND t.reckondate >= #{vo.startdate}]]>
            </if>
            <if test="vo.enddate != null and vo.enddate != ''">
                <![CDATA[AND t.reckondate <= #{vo.enddate}]]>
            </if>
            <if test="vo.goodsid != null">
                and g.goodsid=#{vo.goodsid}
            </if>
            <if test="vo.accountid != null and vo.accountid !=''">
                and ta.accountid = #{vo.accountid}
            </if>
            <if test="vo.excludeAccountIds!=null and vo.excludeAccountIds.length>0">
                <foreach collection="vo.excludeAccountIds" item="item" open=" and ta.accountid not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.userid != null and vo.userid != ''">
                and (pua.userid in
                (select uo.userid from useraccount uo where uo.usertype in (1,2,3) start with uo.userid = #{vo.userid} connect by prior uo.userid = uo.parentuserid)
                or ua.userid = #{vo.userid})
            </if>
        </where>
        group by t.reckondate, t.accountid, ua.userid, ua.accountname, t.goodsid, g.goodscode, g.goodsname,
        ua.usertype, pua.userid, pua.accountname, mua.userid, mua.accountname, m.trademode, en.enumdicname
        order by t.reckondate, to_char(ua.userid)
    </select>

    <select id="queryInvestorSumList" resultType="com.muchinfo.mtp3pojos.vo.query.investorsum.InvestorSumQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.investorsum.InvestorSumQueryParam">
        select tt.userid , tt.accountname ,
        tt.goodsid , tt.goodscode , tt.goodsname ,
        tt.tradeqtysum , tt.tradeamountsum ,
        tt.closeplsum , tt.chargesum
        from
        (select ua1.userid, ua1.accountname, g.goodsid, g.goodscode, g.goodsname,
        sum(t.tradeqty) tradeqtysum,
        sum(t.tradeamount) tradeamountsum,
        sum(t.closepl) closeplsum,
        sum(t.opencharge + t.closecharge) chargesum
        from RECKON_DAYACCOUNTTRADE t
        inner join taaccount ta on ta.accountid = t.accountid
        inner join useraccount ua on ua.userid = ta.userid and ua.usertype = 5
        inner join useraccount ua1 on ua.memberuserid = ua1.userid and ua1.usertype = 2
        inner join goods g on g.goodsid = t.goodsid
        where 1=1
        <if test="vo.startdate != null and vo.startdate != ''">
            <![CDATA[AND t.reckondate >= #{vo.startdate}]]>
        </if>
        <if test="vo.enddate != null and vo.enddate != ''">
            <![CDATA[AND t.reckondate <= #{vo.enddate}]]>
        </if>
        group by ua1.userid, ua1.accountname, g.goodsid, g.goodscode, g.goodsname
        union
        select ua1.userid, ua1.accountname, g.goodsid, g.goodscode, g.goodsname,
        sum(t.tradeqty) tradeqtysum,
        sum(t.tradeamount) tradeamountsum,
        sum(t.closepl) closeplsum,
        sum(t.opencharge + t.closecharge) chargesum
        from RECKON_DAYACCOUNTTRADE t
        inner join taaccount ta on ta.accountid = t.accountid
        inner join useraccount ua on ua.userid = ta.userid and ua.usertype = 5
        inner join useraccount ua1 on ua.parentuserid = ua1.userid and ua1.usertype = 3
        inner join goods g on g.goodsid = t.goodsid
        where 1=1
        <if test="vo.startdate != null and vo.startdate != ''">
            <![CDATA[AND t.reckondate >= #{vo.startdate}]]>
        </if>
        <if test="vo.enddate != null and vo.enddate != ''">
            <![CDATA[AND t.reckondate <= #{vo.enddate}]]>
        </if>
        group by ua1.userid, ua1.accountname, g.goodsid, g.goodscode, g.goodsname) tt
        where 1= 1
        <if test="vo.goodsid != null and vo.goodsid != ''">
            AND tt.goodsid=#{vo.goodsid}
        </if>
        <if test="vo.userid != null and vo.userid != ''">
            and tt.userid in
            (select uo.userid from useraccount uo where uo.usertype in (2,3) start with uo.userid = #{vo.userid} connect by prior uo.userid = uo.parentuserid)
        </if>
        <if test="vo.parentuserid != null and vo.parentuserid != ''">
            and tt.userid in
            (select uo.userid from useraccount uo where uo.usertype in (2,3) start with uo.userid = #{vo.parentuserid} connect by prior uo.userid = uo.parentuserid)
        </if>
        order by to_char(tt.userid)
    </select>

    <select id="queryByAGoods" parameterType="com.muchinfo.mtp3pojos.vo.query.goodstrade.GoodsTradeQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.goodstrade.GoodsTradeQueryResult">
        SELECT
        '--' as  tradedate,
        g.goodsid,
        g.goodscode || '/' || g.goodsname as goodsfullname,g.goodscode,

        case
        when (m.trademode=52) then sum(t.tradeqty * g.agreeunit) || en.enumdicname
        else sum(t.tradeqty) || ''
        end as "tradeqtysum",

        sum(t.tradeamount) as tradeamountsum,
        sum(t.closepl) as closeplsum,
        sum(t.opencharge + t.closecharge) as chargesum
        FROM RECKON_DAYACCOUNTTRADE t
        inner join goods g on g.goodsid = t.goodsid
        left join enumdicitem en on en.enumdiccode='goodsunit' and en.enumitemname = g.goodunitid
        inner join market m on m.marketid = g.marketid
        inner join taaccount ta on ta.accountid = t.accountid
        inner join useraccount ua on ta.userid = ua.userid
        inner join useraccount pua on pua.userid = ua.parentuserid
        inner join useraccount mua on mua.userid = ua.memberuserid
        <where>
            <if test="vo.startdate != null and vo.startdate != ''">
                <![CDATA[AND t.reckondate >= #{vo.startdate}]]>
            </if>
            <if test="vo.enddate != null and vo.enddate != ''">
                <![CDATA[AND t.reckondate <= #{vo.enddate}]]>
            </if>
            <if test="vo.goodsid != null">
                and g.goodsid=#{vo.goodsid}
            </if>
            <if test="vo.marketid != null and vo.marketid != ''">
                and m.marketid=#{vo.marketid}
            </if>
            <if test="vo.excludeAccountIds!=null and vo.excludeAccountIds.length>0">
                <foreach collection="vo.excludeAccountIds" item="item" open=" and ta.accountid not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.userid != null and vo.userid != ''">
                and pua.userid in
                (select uo.userid from useraccount uo where uo.usertype in (2,3) start with uo.userid = #{vo.userid} connect by prior uo.userid = uo.parentuserid)
            </if>
        </where>
        group by g.goodsid, g.goodscode, g.goodsname,g.agreeunit, en.enumdicname, m.trademode
        order by to_char(g.goodscode)
    </select>

    <select id="queryByAGoodsDate" parameterType="com.muchinfo.mtp3pojos.vo.query.goodstrade.GoodsTradeQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.goodstrade.GoodsTradeQueryResult">
        SELECT
        t.reckondate as tradedate,
        g.goodsid,
        g.goodscode || '/' || g.goodsname as goodsfullname,g.goodscode,

        case
        when (m.trademode=52) then sum(t.tradeqty * g.agreeunit) || en.enumdicname
        else sum(t.tradeqty) || ''
        end as tradeqtysum,

        sum(t.tradeamount) as tradeamountsum,
        sum(t.closepl) as closeplsum,
        sum(t.opencharge + t.closecharge) as chargesum
        FROM RECKON_DAYACCOUNTTRADE t
        inner join goods g on g.goodsid = t.goodsid
        inner join market m on m.marketid = g.marketid
        left join enumdicitem en on en.enumdiccode='goodsunit' and en.enumitemname = g.goodunitid
        inner join taaccount ta on ta.accountid = t.accountid
        inner join useraccount ua on ta.userid = ua.userid
        inner join useraccount pua on pua.userid = ua.parentuserid
        inner join useraccount mua on mua.userid = ua.memberuserid
        <where>
            <if test="vo.startdate != null and vo.startdate != ''">
                <![CDATA[AND t.reckondate >= #{vo.startdate}]]>
            </if>
            <if test="vo.enddate != null and vo.enddate != ''">
                <![CDATA[AND t.reckondate <= #{vo.enddate}]]>
            </if>
            <if test="vo.goodsid != null">
                and g.goodsid=#{vo.goodsid}
            </if>
            <if test="vo.marketid != null and vo.marketid != ''">
                and m.marketid=#{vo.marketid}
            </if>
            <if test="vo.excludeAccountIds!=null and vo.excludeAccountIds.length>0">
                <foreach collection="vo.excludeAccountIds" item="item" open=" and ta.accountid not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.userid != null and vo.userid != ''">
                and pua.userid in
                (select uo.userid from useraccount uo where uo.usertype in (2,3) start with uo.userid = #{vo.userid} connect by prior uo.userid = uo.parentuserid)
            </if>
        </where>
        group by t.reckondate, g.goodsid, g.goodscode, g.goodsname,g.agreeunit, en.enumdicname, m.trademode
        order by t.reckondate, to_char(g.goodscode)
    </select>


    <!-- 查询斩仓委托明细表(新 - CUTPOSITIONDETAIL表记录交易没更新成交) -->
    <select id="queryCutListFromOrderDetail" resultType="com.muchinfo.mtp3pojos.vo.marketrun.MarketRunCutPositionResult">
        select t.orderid as cutorderid, t.ordertime as cuttime, t.accountid,
               g.goodsname , t.buyorsell , t.tradeqty as cuttradeqty
        from trade_orderdetail t
        left join goods g on g.goodsid = t.goodsid
        where t.operatetype = 2 and t.listingselecttype = 1
        order by t.orderindexid desc, t.orderid desc
    </select>

    <select id="queryTradeSum" parameterType="com.muchinfo.mtp3pojos.vo.query.tradesum.TradeSumQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.tradesum.TradeSumQueryResult">
        SELECT t.tradedate,
        t.ACCOUNTID,
        t.tradetype,
        ua.userid,
        vlo.LOGINIDS as loginid,
        t.GOODSID,
        t.buyorsell,
        sum(t.tradeqty) as tradeqty,
        sum(t.tradeamount) as tradeamount,
        sum(t.opencharge + t.closecharge) as totalcharge,
        case
        when sum(t.tradeqty) = 0 then
        0
        else
        ROUND(sum(t.tradeamount) / sum(t.tradeqty * g.agreeunit), g.decimalplace)
        end "tradepriceavg",
        ua.accountname as currentname,
        pua.accountname as parentusername,
        g.goodscode,
        g.goodsname,
        en.enumdicname as buyorselldisplay
        FROM his_trade_tradedetail t
        left join goods g
        on g.goodsid = t.goodsid
        left join taaccount ta
        on ta.accountid = t.accountid
        left join useraccount ua
        on ta.userid = ua.userid
        left join useraccount pua
        on pua.userid = ua.parentuserid
        left join enumdicitem en
        on en.enumdiccode = 'buyOrSell'
        and en.enumitemname = t.buyorsell
        left join view_user_loginids vlo
        on vlo.USERID = ta.userid
        <where>
            t.isvaliddata = 1
            <if test="vo.cycletime != null and vo.cycletime !=''">
                and t.tradedate =#{vo.cycletime}
            </if>
            <if test="vo.goodsid != null">
                and t.goodsid=#{vo.goodsid}
            </if>
            <if test="vo.buyorsell != null">
                and t.buyorsell=#{vo.buyorsell}
            </if>
            <if test="vo.tradetypes != null and vo.tradetypes !=''">
                and t.tradetype in (${vo.tradetypes})
            </if>
            <if test="vo.loginid != null and vo.loginid !=''">
                and vlo.LOGINIDS = #{vo.loginid}
            </if>
        </where>
        group by t.tradedate,
        t.ACCOUNTID,
        t.GOODSID,
        t.tradetype,
        ua.userid,
        t.buyorsell,
        g.decimalplace,
        ua.accountname,
        pua.accountname,
        g.goodscode,
        g.goodsname,
        en.enumdicname,
        vlo.LOGINIDS
        order by t.tradedate desc
    </select>

    <select id="queryDeliveryOrder" parameterType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.DeliveryOrderEntrustParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.DeliveryOrderEntrustResult">
        select
        <choose>
            <when test="vo.ishis == 1">
                d.histradedate,
            </when>
        </choose>
        d.deliveryorderid, d.deliveryticket, d.accountid,d.matchaccountid,
        d.xgoodsid, d.xdeliveryqty, d.pgoodsid,
        d.pdeliveryqty, d.deliverygoodsid, d.deliveryqty,
        d.deliveryorderstatus, d.ordertime,
        g1.goodsname as xgoodsname,
        g1.goodscode as xgoodscode,
        g2.goodsname as pgoodsname,
        wr.wrstandardid, wr.wrstandardcode , wr.wrstandardname , dic.enumdicname unitname,
        lo.loginids || '/' || u.accountname as accountidname
        <choose>
            <when test="vo.ishis == null or vo.ishis == 0">
                from Deliveryorder d
            </when>
            <when test="vo.ishis == 1">
                from HIS_DELIVERYORDER d
            </when>
        </choose>
        left join goods g1
        on d.xgoodsid = g1.goodsid
        left join goods g2
        on d.pgoodsid = g2.goodsid
        left join deliverygoods dg
        on d.deliverygoodsid = dg.deliverygoodsid
        left join TAAccount t
        on d.accountid = t.accountid
        left join useraccount u
        on u.userid = t.userid
        left join wrfactortype wft on d.wrfactortypeid = wft.wrfactortypeid
        left join wrstandard wr on wft.wrstandardid = wr.wrstandardid
        left join enumdicitem dic on wr.unitid=dic.enumitemname and dic.enumdiccode = 'goodsunit'
        left join VIEW_USER_LOGINIDS lo on u.userid = lo.userid
        <where>
            d.selldeliveryapplyorderid is null
            <if test="vo.ishis != null and vo.ishis == 1">
                and d.isvaliddata = 1
            </if>
            <if test="vo.deliveryorderid != null and vo.deliveryorderid != ''">
                AND d.deliveryorderid like '%'||#{vo.deliveryorderid}||'%'
            </if>
            <if test="vo.accountname != null and vo.accountname != ''">
                AND u.accountname like '%'||#{vo.accountname}||'%'
            </if>
            <if test="vo.xgoodsid != null and vo.xgoodsid != ''">
                AND d.xgoodsid = #{vo.xgoodsid}
            </if>
            <if test="vo.wrstandardid != null">
                and wr.wrstandardid = #{vo.wrstandardid}
            </if>
            <if test="vo.deliveryorderstatus != null and vo.deliveryorderstatus != ''">
                and deliveryorderstatus = #{vo.deliveryorderstatus}
            </if>
            <if test="vo.startdate != null and vo.startdate != '' and vo.enddate != null and vo.enddate != '' ">
                and to_date(d.histradedate,'yyyyMMdd') between to_date(#{vo.startdate},'yyyy-MM-dd')
                and to_date(#{vo.enddate},'yyyy-MM-dd')
            </if>
        </where>
        <choose>
            <when test="vo.ishis == null or vo.ishis == 0">
                order by d.ordertime desc
            </when>
            <when test="vo.ishis == 1">
                order by d.histradedate desc
            </when>
        </choose>

    </select>

    <select id="queryDeliveryOrderById" resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.DeliveryOrderDetailResult">
        SELECT
        <choose>
            <when test="histradedate != null and histradedate != ''">
                d.histradedate,
            </when>
        </choose>
        d.tradedate,
        d.deliveryorderid,
        d.deliveryticket,
        d.accountid,
        d.xgoodsid, d.xdeliveryqty, d.xdeliveryprice,
        d.pgoodsid, d.pdeliveryqty, d.pdeliveryprice,d.ppricemode,
        d.p2goodsid, d.p2deliveryqty, d.p2deliveryprice,d.p2pricemode,
        d.deliverygoodsid, d.deliveryqty,
        d.deliveryorderfrom,
        d.clientticket,
        d.deliveryorderstatus,
        d.handlestatus,
        d.ordertime,
        d.matchaccountid,
        d.brandid ,
        d.qualityid ,
        d.specid ,
        d.warehouseid ,
        d.retcode ,
        d.deliverymonthid ,
        d.audittime,
        d.auditremark,
        d.wrfactortypeid,
        sm.logincode as auditname,
        g1.goodsname as xgoodsname,
        g2.goodsname as pgoodsname,
        g3.goodsname as p2goodsname,
        dg.deliverygoodsname,
        dgp1.pricemovedesc as brandname ,
        dgp2.pricemovedesc as specname ,
        dgp3.pricemovedesc as qualityname ,
        dgp4.pricemovedesc as warehousename ,
        dgp5.pricemovedesc as deliverymonthname,
        wr.wrstandardcode, wr.wrstandardname
        <choose>
            <when test="histradedate != null and histradedate != ''">
                from HIS_DELIVERYORDER d
            </when>
            <otherwise>
                from Deliveryorder d
            </otherwise>
        </choose>
        left join goods g1
        on d.xgoodsid = g1.goodsid
        left join goods g2
        on d.pgoodsid = g2.goodsid
        left join goods g3
        on d.p2goodsid = g3.goodsid
        left join deliverygoods dg
        on d.deliverygoodsid = dg.deliverygoodsid
        left join wrfactortype wft on d.wrfactortypeid = wft.wrfactortypeid
        left join wrstandard wr on wr.wrstandardid = wft.wrstandardid
        left join deliverygoodspricemove dgp1 on dgp1.autoid = d.brandid
        left join deliverygoodspricemove dgp2 on dgp2.autoid = d.specid
        left join deliverygoodspricemove dgp3 on dgp3.autoid = d.qualityid
        left join deliverygoodspricemove dgp4 on dgp4.autoid = d.warehouseid
        left join deliverygoodspricemove dgp5 on dgp5.autoid = d.deliverymonthid
        left join SystemManager sm on sm.autoid = d.auditid
        <where>
            d.deliveryorderid = #{deliveryorderid}
            <if test="histradedate != null and histradedate != ''">
                and d.isvaliddata = 1
            </if>
            <if test="histradedate != null and histradedate != ''">
                and d.histradedate = #{histradedate}
            </if>
        </where>
    </select>

    <select id="queryWRFactorTypeItemNew" resultType="com.muchinfo.mtp3pojos.entity.Wrfactortypeitem">
        select w.*,
            t.itemtypename dgfactoryitemtypename,
            f.dgfactoryitemvalue dgfactoryitemname
        from wrfactortype wft
        left join wrfactortypeitem w on wft.wrfactortypeid = w.wrfactortypeid
        left join DGFactoryItemType t on t.itemtypemode = 1 and wft.deliverygoodsid = t.deliverygoodsid and w.dgfactoryitemtypeid = t.dgfactoryitemtypeid
        left join DGFactoryItem f on w.dgfactoryitemid = f.dgfactoryitemid
        <where>
            <if test="wrfactortypeid != null and wrfactortypeid != ''">
                and w.wrfactortypeid = #{wrfactortypeid}
            </if>
        </where>
    </select>

    <select id="querySettlementDeal"
            parameterType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.SettlementDealParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.SettlementDealResult">
        select
        <choose>
            <when test="vo.ishis == 1">
                a.histradedate,
            </when>
        </choose>
        a.deliveryid,a.buyorsell,
        to_char(to_date(a.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate,
        a.deliveryorderid,a.accountid,
        a.matchaccountid,a.xgoodsid,b.goodsname as x_goodsname,a.pgoodsid,
        c.goodsname as p_goodsname,a.deliverygoodsid,
        a.deliveryqty,a.deliveryprice,a.deliveryamount,a.deliverypricemove,
        a.deliverycharge,a.buydeliveryapplyorderid,a.selldeliveryapplyorderid,
        wr.wrstandardid, wr.wrstandardcode || '/' || wr.wrstandardname as deliverygoodsname, dic.enumdicname unitname,
        lo1.loginids as loginid, lo2.loginids as matchloginid
        <choose>
            <when test="vo.ishis == 1">
                from HIS_Deliverydetail a
            </when>
            <otherwise>
                from Deliverydetail a
            </otherwise>
        </choose>
        left join goods b on a.xgoodsid =b.goodsid
        left join goods c on a.pgoodsid =c.goodsid
        left join deliverygoods d on a.deliverygoodsid = d.deliverygoodsid
        left join wrfactortype wft on a.wrfactortypeid = wft.wrfactortypeid
        left join wrstandard wr on wft.wrstandardid = wr.wrstandardid
        left join enumdicitem dic on wr.unitid=dic.enumitemname and dic.enumdiccode = 'goodsunit'
        left join taaccount ta1 on ta1.accountid = a.accountid
        left join VIEW_USER_LOGINIDS lo1 on ta1.userid = lo1.userid
        left join taaccount ta2 on ta2.accountid = a.matchaccountid
        left join VIEW_USER_LOGINIDS lo2 on ta2.userid = lo2.userid
        <where>
            <choose>
                <when test="vo.ishis == 1">
                    a.deliverystatus in (2,4) and a.mainorderflag = 1 and a.isvaliddata = 1
                    <if test="vo.userid != null">
                        and ((a.accountid in (select ab.accountid from taaccount ab where ab.userid=#{vo.userid}))
                        or(a.matchaccountid in (select ab.accountid from taaccount ab where ab.userid=#{vo.userid})))
                    </if>
                </when>
                <otherwise>
                    <if test="vo.mainorderflag != null">
                        and a.mainorderflag = #{vo.mainorderflag}
                    </if>
                    <if test="vo.userid != null">
                        and (a.accountid in (select ab.accountid from taaccount ab where ab.userid=#{vo.userid}))
                    </if>
                </otherwise>
            </choose>
            <if test="vo.deliveryid != null and vo.deliveryid != '' ">
                and a.deliveryid like '%'||#{vo.deliveryid}||'%'
            </if>
            <if test="vo.xgoodsid != null and vo.xgoodsid != '' ">
                and a.xgoodsid = #{vo.xgoodsid}
            </if>
            <if test="vo.tradedate != null and vo.tradedate != '' ">
                and a.tradedate= #{vo.tradedate}
            </if>
            <if test="vo.deliveryorderid != null and vo.deliveryorderid != '' ">
                and (a.deliveryorderid like '%'||#{vo.deliveryorderid}||'%'
                or a.buydeliveryapplyorderid like '%'||#{vo.deliveryorderid}||'%'
                or a.selldeliveryapplyorderid like '%'||#{vo.deliveryorderid}||'%')
            </if>
            <if test="vo.wrstandardid != null">
                and wr.wrstandardid = #{vo.wrstandardid}
            </if>
            <if test="vo.accountid != null and vo.accountid != '' ">
                and (a.accountid = #{vo.accountid} or a.matchaccountid = #{vo.accountid})
            </if>
            <if test="vo.startdate != null and vo.startdate != '' and vo.enddate != null and vo.enddate != '' ">
                and to_date(a.histradedate,'yyyyMMdd') between to_date(#{vo.startdate},'yyyy-MM-dd')
                and to_date(#{vo.enddate},'yyyy-MM-dd')
            </if>
            <if test="vo.buyorsell != null">
                and a.buyorsell = #{vo.buyorsell,jdbcType=DECIMAL}
            </if>
        </where>
        order by
        <choose>
            <when test="vo.ishis == 1">
                a.histradedate desc,
            </when>
            <otherwise>
                a.tradedate desc,
            </otherwise>
        </choose>
        a.deliveryid desc
    </select>


    <select id="settlementDealDetail" resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.SettlementDealDetailResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.SettlementDealDetailParam">
        select
        a.deliveryid,
        a.buyorsell,
        <choose>
            <when test="vo.histradedate != null and vo.histradedate != ''">
                to_char(to_date(a.histradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as histradedate,
            </when>
        </choose>
        to_char(to_date(a.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate,
        a.deliveryorderid,
        a.accountid,
        a.matchaccountid,
        a.xgoodsid, g1.goodsname as xgoodsname,a.xdeliverycloseqty,a.xdeliveryprice,
        a.pgoodsid, a.ppricemode, g2.goodsname as pgoodsname,a.pdeliverycloseqty,a.pdeliveryprice,
        a.p2goodsid, a.p2pricemode, g3.goodsname as p2goodsname,a.p2deliverycloseqty,a.p2deliveryprice,
        a.deliverygoodsid,d.deliverygoodsname,
        a.deliverytime,
        a.deliveryqty,
        a.deliveryrtradeqty,
        a.deliveryamount,
        a.deliverypricemove,
        a.deliverytotalamount,
        a.deliveryprice,
        a.deliverycharge,
        a.remaintotalamount,
        a.closeflag,
        a.deliverystatus,
        a.handlestatus,
        a.mainorderflag,
        a.relatedwrid,
        a.feealgorithm,
        a.memberchargevalue,
        a.exchchargevalue,
        a.buydeliveryapplyorderid,
        a.selldeliveryapplyorderid,
        a.marginalgorithm,
        a.marginvalue,
        a.freezemargin,
        a.takemode,
        a.receiveraddress,
        e.enumdicname as deliverygoodsunitname,
        <choose>
            <when test="vo.histradedate != null and vo.histradedate != ''">
                dgp1.pricemovedesc as brandname ,
                dgp2.pricemovedesc as specname ,
                dgp3.pricemovedesc as qualityname ,
                dgp4.pricemovedesc as warehousename ,
                dgp5.pricemovedesc as deliverymonthname,
                a.wrfactortypeid
                from his_Deliverydetail a
                left join goods g1 on a.xgoodsid =g1.goodsid
                left join goods g2 on a.pgoodsid =g2.goodsid
                left join goods g3 on a.p2goodsid =g3.goodsid
                left join deliverygoods d on a.deliverygoodsid = d.deliverygoodsid
                left join ENUMDICITEM e on e.enumdiccode = 'goodsunit' and e.enumitemname = d.goodsunitid
                left join deliverygoodspricemove dgp1 on dgp1.autoid = a.brandid
                left join deliverygoodspricemove dgp2 on dgp2.autoid = a.specid
                left join deliverygoodspricemove dgp3 on dgp3.autoid = a.qualityid
                left join deliverygoodspricemove dgp4 on dgp4.autoid = a.warehouseid
                left join deliverygoodspricemove dgp5 on dgp5.autoid = a.deliverymonthid
            </when>
            <otherwise>
                wft.warehouseid,
                wi.warehousename as warehousename,
                a.wrfactortypeid,
                wr.wrstandardcode,
                wr.wrstandardname
                from Deliverydetail a
                left join goods g1 on a.xgoodsid =g1.goodsid
                left join goods g2 on a.pgoodsid =g2.goodsid
                left join goods g3 on a.p2goodsid =g3.goodsid
                left join deliverygoods d on a.deliverygoodsid = d.deliverygoodsid
                left join ENUMDICITEM e on e.enumdiccode = 'goodsunit' and e.enumitemname = d.goodsunitid
                left join wrfactortype wft on a.wrfactortypeid = wft.wrfactortypeid
                left join wrstandard wr on wr.wrstandardid = wft.wrstandardid
                left join warehouseinfo wi on wft.warehouseid = wi.autoid
            </otherwise>
        </choose>
        <where>
            a.deliveryid = #{vo.deliveryid, jdbcType=DECIMAL}
            and a.buyorsell=#{vo.buyorsell, jdbcType=INTEGER}
            <if test="vo.histradedate != null">
                and a.histradedate = #{vo.histradedate}
                and a.isvaliddata = 1
            </if>
        </where>
    </select>

    <select id="applyOrderDetailHis" resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.ApplyOrderDetailResult">
        SELECT
        d.deliveryapplyorderid,
        d.histradedate,
        d.tradedate,
        d.accountid,
        d.buyorsell,
        d.xgoodsid, d.xdeliveryqty, d.xdeliveryprice,
        d.pgoodsid, d.pdeliveryqty, d.pdeliveryprice,d.ppricemode,
        d.p2goodsid, d.p2deliveryqty, d.p2deliveryprice,d.p2pricemode,
        d.deliverygoodsid, d.deliveryqty,
        d.applyorderstatus,
        d.operatetype,d.ordersrc,
        d.operatorid,d.counterorderid,
        d.brandid ,
        d.qualityid ,
        d.specid ,
        d.warehouseid ,
        d.deliverymonthid ,
        d.isstandard ,
        d.retcode,
        d.freezeamount, d.xdeliverytradeqty, d.xdeliverycancelqty,
        d.validtype, d.validtime,
        d.pricemove, d.ordertime,
        g1.goodunitid,
        g1.goodsname as xgoodsname,
        g2.goodsname as pgoodsname,
        g3.goodsname as p2goodsname,
        dg.deliverygoodsname,
        dgp1.pricemovedesc as brandname ,
        dgp2.pricemovedesc as specname ,
        dgp3.pricemovedesc as qualityname ,
        dgp4.pricemovedesc as warehousename ,
        dgp5.pricemovedesc as deliverymonthname,
        e.enumdicname as deliverygoodsunitname,
        e2.enumdicname as xgoodsUnitName
        from HIS_DELIVERYAPPLYORDER d
        left join goods g1
        on d.xgoodsid = g1.goodsid
        left join goods g2
        on d.pgoodsid = g2.goodsid
        left join goods g3
        on d.p2goodsid = g3.goodsid
        left join deliverygoods dg
        on d.deliverygoodsid = dg.deliverygoodsid
        left join ENUMDICITEM e on e.enumdiccode = 'goodsunit' and e.enumitemname = dg.goodsunitid
        left join ENUMDICITEM e2 on e.enumdiccode = 'goodsunit' and e.enumitemname = g1.goodunitid
        left join deliverygoodspricemove dgp1 on dgp1.autoid = d.brandid
        left join deliverygoodspricemove dgp2 on dgp2.autoid = d.specid
        left join deliverygoodspricemove dgp3 on dgp3.autoid = d.qualityid
        left join deliverygoodspricemove dgp4 on dgp4.autoid = d.warehouseid
        left join deliverygoodspricemove dgp5 on dgp5.autoid = d.deliverymonthid
        <where>
            d.deliveryapplyorderid = #{deliveryorderid} and d.isvaliddata = 1
            and d.histradedate = (select max(t1.histradedate) from HIS_DELIVERYAPPLYORDER t1 where t1.deliveryapplyorderid = #{deliveryorderid} and t1.isvaliddata = 1)
        </where>
    </select>

    <select id="applyOrderDetail" resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.ApplyOrderDetailResult">
        SELECT
        d.deliveryapplyorderid,
        d.tradedate,
        d.accountid,
        d.buyorsell,
        d.xgoodsid, d.xdeliveryqty, d.xdeliveryprice,
        d.pgoodsid, d.pdeliveryqty, d.pdeliveryprice,d.ppricemode,
        d.p2goodsid, d.p2deliveryqty, d.p2deliveryprice,d.p2pricemode,
        d.deliverygoodsid, d.deliveryqty,
        d.applyorderstatus,
        d.operatetype,d.ordersrc,
        d.operatorid,d.counterorderid,
        d.brandid ,
        d.qualityid ,
        d.specid ,
        d.warehouseid ,
        d.deliverymonthid ,
        d.isstandard ,
        d.retcode,
        d.freezeamount, d.xdeliverytradeqty, d.xdeliverycancelqty,
        d.validtype, d.validtime,
        d.pricemove, d.ordertime,
        g1.goodunitid,
        g1.goodsname as xgoodsname,
        g2.goodsname as pgoodsname,
        g3.goodsname as p2goodsname,
        dg.deliverygoodsname,
        dgp1.pricemovedesc as brandname ,
        dgp2.pricemovedesc as specname ,
        dgp3.pricemovedesc as qualityname ,
        dgp4.pricemovedesc as warehousename ,
        dgp5.pricemovedesc as deliverymonthname,
        e.enumdicname as deliverygoodsunitname,
        e2.enumdicname as xgoodsUnitName
        from Deliveryapplyorder d
        left join goods g1
        on d.xgoodsid = g1.goodsid
        left join goods g2
        on d.pgoodsid = g2.goodsid
        left join goods g3
        on d.p2goodsid = g3.goodsid
        left join deliverygoods dg on d.deliverygoodsid = dg.deliverygoodsid
        left join ENUMDICITEM e on e.enumdiccode = 'goodsunit' and e.enumitemname = dg.goodsunitid
        left join ENUMDICITEM e2 on e.enumdiccode = 'goodsunit' and e.enumitemname = g1.goodunitid
        left join deliverygoodspricemove dgp1 on dgp1.autoid = d.brandid
        left join deliverygoodspricemove dgp2 on dgp2.autoid = d.specid
        left join deliverygoodspricemove dgp3 on dgp3.autoid = d.qualityid
        left join deliverygoodspricemove dgp4 on dgp4.autoid = d.warehouseid
        left join deliverygoodspricemove dgp5 on dgp5.autoid = d.deliverymonthid
        left join TAACCOUNT ta on d.accountid = ta.accountid
        <where>
            d.deliveryapplyorderid = #{deliveryorderid}
        </where>
    </select>

    <select id="getHisDeliveryDetailByBuyOrSellOrderId" resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.DeliveryDetailResult">
        select
        a.deliveryid,
        a.buyorsell,
        to_char(to_date(a.histradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as histradedate,
        to_char(to_date(a.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate,
        a.deliveryorderid,
        a.accountid,
        a.matchaccountid,
        a.xgoodsid, g1.goodsname as x_goodsname,a.xdeliverycloseqty,a.xdeliveryprice,
        a.pgoodsid, a.ppricemode, g2.goodsname as p_goodsname,a.pdeliverycloseqty,a.pdeliveryprice,
        a.p2goodsid, a.p2pricemode, g3.goodsname as p2goodsname,a.p2deliverycloseqty,a.p2deliveryprice,
        a.deliverygoodsid,d.deliverygoodsname,
        a.deliverytime,
        a.deliveryqty,
        a.deliveryrtradeqty,
        a.deliveryamount,
        a.deliverypricemove,
        a.deliverytotalamount,
        a.deliveryprice,
        a.deliverycharge,
        a.remaintotalamount,
        a.closeflag,
        a.deliverystatus,
        a.handlestatus,
        a.mainorderflag,
        a.relatedwrid,
        a.feealgorithm,
        a.memberchargevalue,
        a.exchchargevalue,
        a.buydeliveryapplyorderid,
        a.selldeliveryapplyorderid,
        a.marginalgorithm,
        a.marginvalue,
        a.freezemargin,
        a.takemode,
        a.receiveraddress,
        dgp1.pricemovedesc as brandname ,
        dgp2.pricemovedesc as specname ,
        dgp3.pricemovedesc as qualityname ,
        dgp4.pricemovedesc as warehousename ,
        dgp5.pricemovedesc as deliverymonthname
        from his_Deliverydetail a
        left join goods g1 on a.xgoodsid =g1.goodsid
        left join goods g2 on a.pgoodsid =g2.goodsid
        left join goods g3 on a.p2goodsid =g3.goodsid
        left join deliverygoods d on a.deliverygoodsid = d.deliverygoodsid
        left join deliverygoodspricemove dgp1 on dgp1.autoid = a.brandid
        left join deliverygoodspricemove dgp2 on dgp2.autoid = a.specid
        left join deliverygoodspricemove dgp3 on dgp3.autoid = a.qualityid
        left join deliverygoodspricemove dgp4 on dgp4.autoid = a.warehouseid
        left join deliverygoodspricemove dgp5 on dgp5.autoid = a.deliverymonthid
        where a.isvaliddata = 1 and
        a.histradedate=(
        select max(histradedate) from his_Deliverydetail
        <where>
            <if test="buyId != null">
                and buydeliveryapplyorderid = #{buyId}
            </if>
            and isvaliddata = 1
            <if test="sellId != null">
                and selldeliveryapplyorderid = #{sellId}
            </if>
        </where>
        )
        <if test="buyorsell != null">
            and a.buyorsell= #{buyorsell}
        </if>
        <if test="buyId != null">
            and a.buydeliveryapplyorderid = #{buyId}
        </if>
        <if test="sellId != null">
            and a.selldeliveryapplyorderid = #{sellId}
        </if>

    </select>

    <select id="getDeliveryDetailByBuyOrSellOrderId"  resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.DeliveryDetailResult">
        select
        a.deliveryid,
        a.buyorsell,
        to_char(to_date(a.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate,
        a.deliveryorderid,
        a.accountid,
        a.matchaccountid,
        a.xgoodsid, g1.goodsname as x_goodsname,a.xdeliverycloseqty,a.xdeliveryprice,
        a.pgoodsid, a.ppricemode, g2.goodsname as p_goodsname,a.pdeliverycloseqty,a.pdeliveryprice,
        a.p2goodsid, a.p2pricemode, g3.goodsname as p2goodsname,a.p2deliverycloseqty,a.p2deliveryprice,
        a.deliverygoodsid,d.deliverygoodsname,
        a.deliverytime,
        a.deliveryqty,
        a.deliveryrtradeqty,
        a.deliveryamount,
        a.deliverypricemove,
        a.deliverytotalamount,
        a.deliveryprice,
        a.deliverycharge,
        a.remaintotalamount,
        a.closeflag,
        a.deliverystatus,
        a.handlestatus,
        a.mainorderflag,
        a.relatedwrid,
        a.deliverycharge,
        a.feealgorithm,
        a.memberchargevalue,
        a.exchchargevalue,
        a.buydeliveryapplyorderid,
        a.selldeliveryapplyorderid,
        a.marginalgorithm,
        a.marginvalue,
        a.freezemargin,
        a.takemode,
        a.receiveraddress,
        dgp1.pricemovedesc as brandname ,
        dgp2.pricemovedesc as specname ,
        dgp3.pricemovedesc as qualityname ,
        dgp4.pricemovedesc as warehousename ,
        dgp5.pricemovedesc as deliverymonthname
        from Deliverydetail a
        left join goods g1 on a.xgoodsid =g1.goodsid
        left join goods g2 on a.pgoodsid =g2.goodsid
        left join goods g3 on a.p2goodsid =g3.goodsid
        left join deliverygoods d on a.deliverygoodsid = d.deliverygoodsid
        left join deliverygoodspricemove dgp1 on dgp1.autoid = a.brandid
        left join deliverygoodspricemove dgp2 on dgp2.autoid = a.specid
        left join deliverygoodspricemove dgp3 on dgp3.autoid = a.qualityid
        left join deliverygoodspricemove dgp4 on dgp4.autoid = a.warehouseid
        left join deliverygoodspricemove dgp5 on dgp5.autoid = a.deliverymonthid
        <where>
            <if test="buyorsell != null">
                and a.buyorsell= #{buyorsell}
            </if>
            <if test="buyId != null">
                and a.buydeliveryapplyorderid = #{buyId}
            </if>
            <if test="sellId != null">
                and a.selldeliveryapplyorderid = #{sellId}
            </if>
        </where>
    </select>


    <select id="queryDeliveryOfflinePage" parameterType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.OfflineDeliveryParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.OfflineDeliveryResult">
        select ua.userid || '/' || lo.loginids || '/' || ua.accountname "username",
        g.goodscode || '/' || g.goodsname "goodsdisplay",
        g.goodscode,
        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",
        mar.trademode "trademode",
        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 market mar on mar.marketid = t.marketid
        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, 'yyyyMMdd') = #{vo.startdate}
        </if>
        order by t.reqtime desc
    </select>

    <select id="queryDeliveryOfflineDetail" resultType="com.muchinfo.mtp3pojos.vo.query.deliveryorder.OfflineDeliveryDetailResult">
        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",
        t.totalpricemove "totalpricemove", t.totalshippingfee "totalshippingfee",
        t.totalotherfee "totalotherfee"
        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="deliveryorderid != null and deliveryorderid !=''">
            AND t.deliveryorderid = #{deliveryorderid}
        </if>
        order by t.reqtime desc
    </select>

    <update id="closeDeliveryOffline" >
        update Trade_GoodsDeliveryOffLine t
        set t.orderstatus   =3,
            t.closetime     = sysdate,
            t.closetradedate=#{tradedate}
        where t.deliveryorderid = #{deliveryorderid}
    </update>

    <update id="failDeliveryOffline" >
        update Trade_GoodsDeliveryOffLine t
        set t.orderstatus   =5,
            t.closetime     = sysdate,
            t.closetradedate=#{tradedate},
            t.remark        = #{auditremark}
        where t.deliveryorderid = #{deliveryorderid}
    </update>


    <!-- 查询电子签记录 -->
    <select id="queryesignrecord" parameterType="com.muchinfo.mtp3pojos.vo.query.other.EsignrecordQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.other.EsignrecordQueryResult">
        select uer.recordid "recordid",
        ua.userid || '/' || ua.accountname "userfullname",
        vlogs.LOGINIDS "loginid",
        et.esigntype "esigntype",
        uer.templatetype "templatetype",
        uer.templatename "templatename",
        uer.recordstatus "recordstatus",
        to_char(uer.updatetime, 'yyyy-MM-dd hh24:mi:ss') "updatetimestr"
        from useresignrecord uer
        left join esigntemplateconfig et on et.templateconfigid = uer.templateconfigid
        left join useraccount ua on ua.userid = uer.userid
        left join view_user_loginids vlogs on vlogs.USERID = uer.userid
        where 1=1
        <if test="vo.userid != null and vo.userid != ''">
            and uer.userid = (${vo.userid})
        </if>
        <if test="vo.recordstatus != null and vo.recordstatus != ''">
            and uer.recordstatus = (${vo.recordstatus})
        </if>
        order by uer.userid, uer.orderindex
    </select>

    <select id="queryinvestor" resultType="com.muchinfo.mtp3pojos.vo.query.other.InvestorQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.query.other.InvestorQueryParam">
        select
        t.accountid,t.userid,t.currencyid,
        t.changeflag,t.tradestatus,
        t.signstatus, t.ismain, t.parentaccountid, t.fromaccountid,
        t.relateduserid,
        t.relatedaccountstatus, t.taaccounttype,
        t.isreckonaccount,
        t.ismarketaccount, t.balance,
        t.orifreezemargin, t.oriusedmargin,
        t.oriotherfreezemargin,
        t.orifreezecharge, t.orimortgagecredit,
        t.oriothercredit,
        t.orioutamountfreeze, t.currentbalance,
        t.freezemargin,
        t.usedmargin, t.otherfreezemargin, t.freezecharge,
        t.mortgagecredit, t.othercredit, t.outamountfreeze,
        t.inamount,
        t.outamount, t.paycharge,
        t.closepl, t.creditincrease,
        t.creditdecrease,
        t.othercreditincrease, t.othercreditdecrease,
        t.transferamount, t.otherpay, t.otherincome,
        t.outthreshold,
        u.accountname,
        u.userid || '/' || u.accountname as accountcodename,
        t1. invloginids,
        u.parentuserid,
        area.userid || '/' || area.accountname as parentarea,
        u.memberuserid as memberid,
        ua.userid || '/' || ua.accountname as membername
        from (
        select
        t.accountid,t.userid,t.currencyid,
        wm_concat(la.loginid) as invloginids
        from
        taaccount t
        left join useraccount u on t.userid = u.userid
        left join
        loginaccount la on t.userid=la.userid
        group by
        t.accountid,t.userid,t.currencyid) t1 ,taaccount t
        left join
        useraccount u on t.userid = u.userid
        left join useraccount ua on
        ua.userid=u.memberuserid
        left join useraccount area on
        area.userid=u.parentuserid
        <where>
            t.accountid != 1 and t1.accountid=t.accountid
            <if test="vo.accountcode !=null and vo.accountcode != '' ">
                and t.userid = #{vo.accountcode}
            </if>
            <if test="vo.roles != null and vo.roles !='' ">
                and t.userid in (select userid from arearole where roletype in (${vo.roles}))
            </if>
            <if test="vo.accountstatus != null and vo.accountstatus == 1">
                and u.accountstatus = 4
            </if>
            <if test="vo.accountstatus != null and vo.accountstatus == 2">
                and u.accountstatus in (6,7)
            </if>
            <if test="vo.accountid !=null and vo.accountid != '' ">
                and t.accountid like
                '%'||#{vo.accountid,jdbcType=DECIMAL}||'%'
            </if>
            <if test="vo.type !=null and vo.type != '' ">
                <choose>
                    <when test="vo.type == 2">
                        and u.usertype in (1,2,3)
                    </when>
                    <otherwise>
                        and u.usertype = #{vo.type}
                    </otherwise>
                </choose>
            </if>
            <if test="vo.areauserid != null and vo.areauserid != '' ">
                <choose>
                    <when test="vo.changeflag!=null and vo.changeflag==3">
                        and u.memberuserid in (select u1.userid from useraccount u1 where u1.parentuserid=#{vo.areauserid}
                        or u1.userid=#{vo.areauserid})
                    </when>
                    <otherwise>
                        and (u.userid in (
                        select userid from useraccount uo
                        start with uo.parentuserid = #{vo.areauserid}
                        connect by prior uo.userid =uo.parentuserid and uo.userid !=#{vo.areauserid} ))
                    </otherwise>
                </choose>
            </if>
            <if test="vo.loginid != null and vo.loginid != '' ">
                and t.userid in (select la.userid from loginaccount la
                where la.loginid = #{vo.loginid})
            </if>
            <if test="vo.memberid !=null and vo.memberid != ''">
                and u.memberuserid = #{vo.memberid}
            </if>
            <if test="vo.parentarea !=null and vo.parentarea != ''">
                and ((area.accountname like
                '%'||#{vo.parentarea,jdbcType=VARCHAR}||'%')
                or(u.parentuserid like
                '%'||#{vo.parentarea,jdbcType=VARCHAR}||'%'))
            </if>
        </where>
        <choose>
            <when test="vo.sortfield != null and vo.sortfield == 1">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.balance asc
                    </when>
                    <otherwise>
                        order by t.balance desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 2">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.currentbalance asc
                    </when>
                    <otherwise>
                        order by t.currentbalance desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 3">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.inamount asc
                    </when>
                    <otherwise>
                        order by t.inamount desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 4">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.outamount asc
                    </when>
                    <otherwise>
                        order by t.outamount desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 5">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.closepl asc
                    </when>
                    <otherwise>
                        order by t.closepl desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 6">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by t.PAYCHARGE asc
                    </when>
                    <otherwise>
                        order by t.PAYCHARGE desc
                    </otherwise>
                </choose>
            </when>

            <otherwise>
                order by u.accountname
            </otherwise>
        </choose>
    </select>

    <select id="holdtransferquery" resultType="com.muchinfo.mtp3pojos.vo.query.other.HoldtransferQueryResult">
        select t.*, g.goodscode, g.goodsname, g.goodscode || '/' || g.goodsname "goodsfullname",
        m.marketname, m.marketid || '/' || m.marketname "marketfullname",
        <choose>
            <when test="vo.ishis!=null and vo.ishis==1">
                1 ishis
                from his_trade_holdtransferapply t
            </when>
            <otherwise>
                0 ishis
                from trade_holdtransferapply t
            </otherwise>
        </choose>
        left join goods g on t.goodsid = g.goodsid
        left join market m on t.marketid = m.marketid
        <where>
            <if test="vo.applystatus != null">
                and t.applystatus=#{vo.applystatus}
            </if>
            <if test="vo.goodsid != null">
                and t.goodsid=#{vo.goodsid}
            </if>
            <if test="vo.outaccountid != null">
                and t.outaccountid=#{vo.outaccountid}
            </if>
            <if test="vo.inaccountid != null">
                and t.inaccountid=#{vo.inaccountid}
            </if>
            <if test="vo.applydate != null and vo.applydate!=''">
                and (t.applytime between to_date(#{vo.applydate}||' 00:00:00','yyyy-MM-dd hh24:mi:ss')
                and to_date(#{vo.applydate}||' 23:59:59','yyyy-MM-dd hh24:mi:ss'))
            </if>
            <if test="vo.transfertypestr != null and vo.transfertypestr!=''">
                and t.transfertype in (#{vo.transfertypestr})
            </if>
            <if test="vo.yyareauserid != null and vo.yyareauserid !=''">
                and t.goodsid in (select t.goodsid from areagoodsconfig t where t.userid= #{vo.yyareauserid})
            </if>
        </where>
        order by t.applytime desc
    </select>
    <select id="querySumClosePL" resultType="com.muchinfo.mtp3pojos.vo.marketrun.TradeCloseDetailParam"
            parameterType="com.muchinfo.mtp3pojos.vo.marketrun.TradeCloseDetailParam">
        select sum(t.closepl) "closepl"
        from TRADE_CLOSEDETAIL t
        <where>
            <if test="vo.accountid != null and vo.accountid != ''">
                and t.accountid = #{vo.accountid}
            </if>
            <if test="vo.goodsid != null and vo.goodsid != ''">
                and t.goodsid = #{vo.goodsid}
            </if>
            <if test="vo.excludeaccountids!=null and vo.excludeaccountids.length>0">
                <foreach collection="vo.excludeaccountids" item="item" open=" and t.accountid not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="monitorHoldDetail" resultType="com.muchinfo.mtp3pojos.vo.marketrun.MarketRunPositionResult">
        select
        t.tradeid,
        t.accountid,
        t.buyorsell,
        t.holderqty,
        t.goodsid,t.marketid,
        t.holderprice,
        t.openprice,
        t.holderamount,
        t.tradetime,t.holderdays,
        t.tradeproperty,
        t.premium,t.optiontype,
        t.expiretype,t.expirecycle,t.expiredate,
        to_char(to_date(t.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate1,
        m.marketname,
        la.loginids loginids,
        ua.usertype,
        ua.userid as uuserid,
        ua.userid as userid,
        g.goodscode,g.goodsname,
        rua.accountname,
        '0' as histradedate,
        0 as ishis,
        t.isPreExercise,
        t.preExercisePrice,
        t.isConfirmExercise,
        t.holdercredit,
        t.releaseholdercredit,
        t.openqty,
        g.agreeUnit,
        m.trademode,
        g.decimalplace,
        ta.accountname taaccountname
        from trade_holderdetail t
        left join goods g on t.goodsid = g.goodsid
        left join taaccount ta on ta.accountid = t.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join useraccount rua on ta.relateduserid = rua.userid
        left join market m on t.marketid = m.marketid
        left join VIEW_USER_LOGINIDS la on ta.userid = la.userid
        where t.holderqty > 0
        <if test="vo.buyorsell != null">
            and t.buyorsell = #{vo.buyorsell}
        </if>
        <if test="vo.goodsid != null and vo.goodsid != ''">
            and t.goodsid = #{vo.goodsid}
        </if>

        <if test="vo.accountid != null and vo.accountid != ''">
            and t.accountid = #{vo.accountid}
        </if>

        <if test="vo.excludeaccountidarray!=null and vo.excludeaccountidarray.length>0">
            <foreach collection="vo.excludeaccountidarray" item="item" open="and t.accountid not in (" close=")"
                     separator=",">
                ${item}
            </foreach>
        </if>

        <if test="vo.includeaccountidarray!=null and vo.includeaccountidarray.length>0">
            <foreach collection="vo.includeaccountidarray" item="item" open="and t.accountid in (" close=")"
                     separator=",">
                ${item}
            </foreach>
        </if>

        <choose>
            <when test="vo.orderby != null and vo.orderby == 3">
                order by t.holderqty desc, t.tradetime desc, t.accountid desc
            </when>
            <when test="vo.orderby != null and vo.orderby == 4">
                order by t.holderqty asc, t.tradetime desc, t.accountid desc
            </when>
            <when test="vo.orderby != null and vo.orderby == 5">
                order by t.holderprice desc, t.tradetime desc, t.accountid desc
            </when>
            <when test="vo.orderby != null and vo.orderby == 6">
                order by t.holderprice asc, t.tradetime desc, t.accountid desc
            </when>
            <otherwise>
                order by t.tradetime desc,t.accountid desc
            </otherwise>
        </choose>
    </select>
</mapper>
