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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.CptradePositionapply">
        <id column="APPLYID" property="applyid" />
        <result column="USERID" property="userid" />
        <result column="ACCOUNTID" property="accountid" />
        <result column="WRSTANDARDID" property="wrstandardid" />
        <result column="GOODSID" property="goodsid" />
        <result column="WRQTY" property="wrqty" />
        <result column="GOODSQTY" property="goodsqty" />
        <result column="APPLYSTATUS" property="applystatus" />
        <result column="HANDLESTATUS" property="handlestatus" />
        <result column="APPLYTIME" property="applytime" />
        <result column="APPLYREMARK" property="applyremark" />
        <result column="CREATORID" property="creatorid" />
        <result column="CREATETIME" property="createtime" />
        <result column="FIRSTAUDITID" property="firstauditid" />
        <result column="FIRSTAUDITTIME" property="firstaudittime" />
        <result column="FIRSTREMARK" property="firstremark" />
        <result column="SECONDAUDITID" property="secondauditid" />
        <result column="SECONDAUDITTIME" property="secondaudittime" />
        <result column="SECONDREMARK" property="secondremark" />
        <result column="CLIENTTICKET" property="clientticket" />
        <result column="TRADEDATE" property="tradedate" />
        <result column="MARKETID" property="marketid" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        APPLYID, USERID, ACCOUNTID, WRSTANDARDID, GOODSID, WRQTY, GOODSQTY, APPLYSTATUS, HANDLESTATUS, APPLYTIME, APPLYREMARK, CREATORID, CREATETIME, FIRSTAUDITID, FIRSTAUDITTIME, FIRSTREMARK, SECONDAUDITID, SECONDAUDITTIME, SECONDREMARK, CLIENTTICKET, TRADEDATE, MARKETID
    </sql>

    <select id="queryForward" parameterType="com.muchinfo.mtp3pojos.vo.presale.ForwardOrderApplyParam" resultType="com.muchinfo.mtp3pojos.vo.presale.ForwardOrderApplyResult">
        select t.*,
        m.marketname,
        g.goodscode,
        g.goodsname,
        g.deliveryflag,
        w.wrstandardcode,
        w.wrstandardname,
        d.deliverygoodscode,
        d.deliverygoodsname,
        u.customername username,
        s.username firstauditname,
        sm.username secondauditname
        from CPTrade_PositionApply t
        left join market m on t.marketid = m.marketid
        left join goods g on t.goodsid = g.goodsid
        left join wrstandard w on t.wrstandardid = w.wrstandardid
        left join deliverygoods d on w.deliverygoodsid = d.deliverygoodsid
        left join userinfo u on t.userid = u.userid
        left join systemmanager s on t.firstauditid = s.autoid
        left join systemmanager sm on t.secondauditid = sm.autoid
        <where>
            <if test="vo.applyid != null and vo.applyid != ''">
                and (t.applyid like '%'||#{vo.applyid}||'%')
            </if>
            <if test="vo.deliverygoodsname != null and vo.deliverygoodsname != ''">
                and ((d.deliverygoodscode like '%'||#{vo.deliverygoodsname}||'%')
                or(d.deliverygoodsname like '%'||#{vo.deliverygoodsname}||'%'))
            </if>
            <if test="vo.username != null and vo.username != ''">
                and ((t.userid like '%'||#{vo.username}||'%')
                or(u.customername like '%'||#{vo.username}||'%'))
            </if>
            <if test="vo.marketname != null and vo.marketname != ''">
                and ((m.marketname like '%'||#{vo.marketname}||'%')
                or(m.marketid like '%'||#{vo.marketname}||'%'))
            </if>
            <if test="vo.goodsname != null and vo.goodsname != ''">
                and ((g.goodscode like '%'||#{vo.goodsname}||'%')
                or(g.goodsname like '%'||#{vo.goodsname}||'%'))
            </if>
            <if test="vo.applytimestr != null and vo.applytimestr != ''">
                and t.applytime &gt;= to_date(#{vo.applytimestr}||',00:00:00','yyyyMMdd,hh24:mi:ss')
                and t.applytime &lt;= to_date(#{vo.applytimestr}||',23:59:59','yyyyMMdd,hh24:mi:ss')
            </if>
            <if test="vo.applystatus != null">
                and t.applystatus = #{vo.applystatus}
            </if>
            <if test="vo.userid != null">
                and t.userid = #{vo.userid}
            </if>
        </where>
        order by t.applytime desc
    </select>

    <select id="queryOrder" parameterType="com.muchinfo.mtp3pojos.vo.presale.ForwardOrderQueryParam" resultType="com.muchinfo.mtp3pojos.vo.presale.ForwardOrderQueryResult">
        select t.*,
        m.marketname,
        g.goodscode,
        g.goodsname,
        g.deliveryflag,
        w.wrstandardcode,
        w.wrstandardname,
        d.deliverygoodscode,
        d.deliverygoodsname,
        u.customername username,
        least((nvl(t.inqty,0)- nvl(t.deliveryqty,0) - nvl(t.cancelqty,0) - nvl(t.curpresaleqty,0) - nvl(t.presaledqty,0)),
        (nvl(tp.buycurpositionqty,0) - nvl(tp.buyfrozenqty,0)))  canqty
        from CPTrade_UserGoodsData t
        left join market m on t.marketid = m.marketid
        left join goods g on t.goodsid = g.goodsid
        left join wrstandard w on t.wrstandardid = w.wrstandardid
        left join deliverygoods d on w.deliverygoodsid = d.deliverygoodsid
        left join userinfo u on t.userid = u.userid
        left join tradeposition tp on t.accountid = tp.accountid and t.goodsid = tp.goodsid
        <where>
            <if test="vo.username != null and vo.username != ''">
                and ((t.userid like '%'||#{vo.username}||'%')
                or(u.customername like '%'||#{vo.username}||'%'))
            </if>
            <if test="vo.marketname != null and vo.marketname != ''">
                and ((m.marketname like '%'||#{vo.marketname}||'%')
                or(m.marketid like '%'||#{vo.marketname}||'%'))
            </if>
            <if test="vo.goodsname != null and vo.goodsname != ''">
                and ((g.goodscode like '%'||#{vo.goodsname}||'%')
                or(g.goodsname like '%'||#{vo.goodsname}||'%'))
            </if>
            <if test="vo.accountid != null and vo.accountid != ''">
                and (t.accountid like '%'||#{vo.accountid}||'%')
            </if>
            <if test="vo.userid != null">
                and t.userid = #{vo.userid}
            </if>
        </where>
        order by t.accountid,t.goodsid
    </select>

    <select id="queryCancelApply" parameterType="com.muchinfo.mtp3pojos.vo.presale.ForwardCancelApplyParam"  resultType="com.muchinfo.mtp3pojos.vo.presale.ForwardCancelApplyResult">
        select t.*,
        m.marketname,
        g.goodscode,
        g.goodsname,
        g.deliveryflag,
        u.customername username,
        s.username firstauditname,
        sm.username secondauditname,
        ssm.username creatorname,
        dg.deliverygoodscode,
        dg.deliverygoodsname,
        least((ug.inqty - ug.cancelqty - ug.deliveryqty -ug.curpresaleqty),
        (tp.buycurpositionqty-tp.buyfrozenqty-tp.buyotherfrozenqty)) canrealqty,
        ei.enumdicname
        from CPTrade_PositionCancel t
        left join market m on t.marketid = m.marketid
        left join goods g on t.goodsid = g.goodsid
        left join userinfo u on t.userid = u.userid
        left join systemmanager s on t.firstauditid = s.autoid
        left join systemmanager sm on t.secondauditid = sm.autoid
        left join systemmanager ssm on t.creatorid = ssm.autoid
        left join CPTrade_UserGoodsData ug on t.goodsid = ug.goodsid and t.accountid = ug.accountid
        left join tradeposition tp on ug.accountid = tp.accountid and ug.goodsid = tp.goodsid
        left join wrstandard wr on ug.wrstandardid = wr.wrstandardid
        left join deliverygoods dg on wr.deliverygoodsid = dg.deliverygoodsid
        left join enumdicitem ei on ei.enumitemname = wr.unitid and ei.enumdiccode='goodsunit'
        <where>
            <if test="vo.cancelid != null and vo.cancelid != ''">
                and (t.cancelid like '%'||#{vo.cancelid}||'%')
            </if>
            <if test="vo.username != null and vo.username != ''">
                and ((t.userid like '%'||#{vo.username}||'%')
                or(u.customername like '%'||#{vo.username}||'%'))
            </if>
            <if test="vo.marketname != null and vo.marketname != ''">
                and ((m.marketname like '%'||#{vo.marketname}||'%')
                or(m.marketid like '%'||#{vo.marketname}||'%'))
            </if>
            <if test="vo.goodsname != null and vo.goodsname != ''">
                and ((g.goodscode like '%'||#{vo.goodsname}||'%')
                or(g.goodsname like '%'||#{vo.goodsname}||'%'))
            </if>
            <if test="vo.deliverygoodsname != null and vo.deliverygoodsname != ''">
                and ((dg.deliverygoodscode like '%'||#{vo.deliverygoodsname}||'%')
                or(dg.deliverygoodsname like '%'||#{vo.deliverygoodsname}||'%'))
            </if>
            <if test="vo.applytimestr != null and vo.applytimestr != ''">
                and t.applytime &gt;= to_date(#{vo.applytimestr}||',00:00:00','yyyyMMdd,hh24:mi:ss')
                and t.applytime &lt;= to_date(#{vo.applytimestr}||',23:59:59','yyyyMMdd,hh24:mi:ss')
            </if>
            <if test="vo.applystatus != null">
                and t.applystatus = #{vo.applystatus}
            </if>
            <if test="vo.userid != null">
                and t.userid = #{vo.userid}
            </if>
        </where>
        order by t.applytime desc
    </select>

    <select id="queryDRelation" resultType="com.muchinfo.mtp3pojos.vo.presale.ForwardAddOtherInit">
        select
            g.goodsid,
            g.agreeunit,
            dr.mindeliveryqty,
            dr.rratio1,
            dr.rratio2,
            dr.deliverymode
        from goods g,DeliveryRelation dr
        where g.goodsid = dr.goodsid
          and g.goodsid = #{goodsid}
    </select>

    <select id="cancelApplyAddInit" resultType="com.muchinfo.mtp3pojos.vo.presale.ForwardCancelApplyInit">
        select t.*,
        g.transferdepositratio,
        least((t.inqty - t.cancelqty - t.deliveryqty - t.curpresaleqty),
        (tp.buycurpositionqty - tp.buyfrozenqty - tp.buyotherfrozenqty)) canrealqty,
        m.marketname,
        g.goodscode,
        g.goodsname,
        g.deliveryflag,
        g.decimalplace,
        g.quoteminunit agreeunit,
        decode(w.wrstandardid, null, 1, w.minivalue / power(10, w.minivaluedp)) minivalue,
        nvl(w.unitid, g.goodunitid) unitid,
        ei.enumdicname
        from CPTrade_UserGoodsData t
        left join market m on t.marketid = m.marketid
        left join goods g on t.goodsid = g.goodsid
        left join wrstandard w on t.wrstandardid = w.wrstandardid
        left join enumdicitem ei on ei.enumitemname = w.unitid and ei.enumdiccode = 'goodsunit'
        left join tradeposition tp on t.accountid = tp.accountid and t.goodsid = tp.goodsid
        where t.accountid = #{accountid}
        and m.trademode in (10,13,16,70,49)
        and least((t.inqty - t.cancelqty - t.deliveryqty - t.curpresaleqty),
        (tp.buycurpositionqty - tp.buyfrozenqty - tp.buyotherfrozenqty)) > 0
    </select>

    <select id="querypresaleresult" resultType="com.muchinfo.mtp3pojos.vo.presale.ForwardPresaleResult">
        select t.tradeid "tradeid",
        ua1.userid || '/' || ua1.accountname "buyname",
        ua2.userid || '/' || ua2.accountname "sellname",
        g1.goodscode || '/' || g1.goodsname "firstgoods", g2.goodscode || '/' || g2.goodsname "secondgoods",
        to_char(pg.endtime,'yyyy-MM-dd') "endtime",
        t.tradeqty "tradeqty", t.openprice "openprice", t.tradeamount "tradeamount",
        to_char(t.createtime, 'yyyy-MM-dd hh24:mi:ss') "tradetime",
        t.transferdepositratio * 100 "transferdepositratio", t.transferdeposit "transferdeposit",
        t.depositremain "depositremain", t.paystatus "paystatus"
        from CPTrade_PresaleResult t
        inner join CPTrade_PresaleGoodsEx pg on t.goodsid = pg.goodsid
        inner join goods g1 on pg.goodsid = g1.goodsid
        inner join goods g2 on pg.relatedgoodsid = g2.goodsid
        inner join useraccount ua1 on t.buyuserid = ua1.userid
        inner join useraccount ua2 on t.selluserid = ua2.userid
        inner join market mt on g1.marketid = mt.marketid
        where t.buyuserid != t.selluserid
        <if test="vo.trademodes != null and vo.trademodes != ''">
            and mt.trademode in (${vo.trademodes})
        </if>
        <if test="vo.username != null and vo.username != ''">
            and (ua1.accountname like '%'||#{vo.username}||'%' or ua1.userid like '%'||#{vo.username}||'%')
        </if>
        <if test="vo.goodsname1 != null and vo.goodsname1 != ''">
            and (g1.goodscode like '%'||#{vo.goodsname1}||'%' or g1.goodsname like '%'||#{vo.goodsname1}||'%')
        </if>
        <if test="vo.goodsname2 != null and vo.goodsname2 != ''">
            and (g2.goodscode like '%'||#{vo.goodsname2}||'%' or g2.goodsname like '%'||#{vo.goodsname2}||'%')
        </if>
        <if test="vo.paystatus != null and vo.paystatus != ''">
            and t.paystatus = #{vo.paystatus}
        </if>
        <if test="vo.areauserid != null">
            and t.selluserid = #{vo.areauserid}
        </if>
        order by t.createtime desc, g1.goodscode
    </select>
    <select id="querypresaleposition"
            resultType="com.muchinfo.mtp3pojos.vo.presale.ForwardPresalePositionResult">
        select ua.userid || '/' || ua.accountname "accountname",
        g.goodscode || '/' || g.goodsname "secondgoods",
        tp.buycurpositionqty "buycurpositionqty", tp.buyfrozenqty "buyfrozenqty",
        tpex.transferdepositratio * 100 "transferdepositratio", tpex.transferdeposit "transferdeposit",
        tpex.depositremain "depositremain", tpex.paystatus "paystatus",
        ua.userid "userid", tp.accountid "accountid", tp.goodsid "goodsid",
        g.marketid "marketid", mt.trademode "trademode"
        from TradePosition tp
        inner join TradePositionEx tpex on tp.accountid = tpex.accountid and tp.goodsid = tpex.goodsid
        left join taaccount ta on tp.accountid = ta.accountid
        left join useraccount ua on ta.userid = ua.userid
        left join goods g on tp.goodsid = g.goodsid
        inner join market mt on g.marketid = mt.marketid
        <where>
            <if test="vo.trademodes != null and vo.trademodes != ''">
                and mt.trademode in (${vo.trademodes})
            </if>
            <if test="vo.username != null and vo.username != ''">
                and (ua.accountname like '%'||#{vo.username}||'%' or ua.userid like '%'||#{vo.username}||'%')
            </if>
            <if test="vo.goodsname != null and vo.goodsname != ''">
                and (g.goodscode like '%'||#{vo.goodsname}||'%' or g.goodsname like '%'||#{vo.goodsname}||'%')
            </if>
            <if test="vo.paystatus != null and vo.paystatus != ''">
                and tpex.paystatus = #{vo.paystatus}
            </if>
        </where>
        order by ua.accountname, g.goodscode
    </select>
</mapper>
