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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.WrtradeOrderdetail">
        <id column="WRTRADEORDERID" property="wrtradeorderid" />
        <result column="TRADEDATE" property="tradedate" />
        <result column="WRTRADETYPE" property="wrtradetype" />
        <result column="BUYORSELL" property="buyorsell" />
        <result column="RELATEDWRTRADEORDERID" property="relatedwrtradeorderid" />
        <result column="MARKETID" property="marketid" />
        <result column="USERID" property="userid" />
        <result column="ACCOUNTID" property="accountid" />
        <result column="WRFACTORTYPEID" property="wrfactortypeid" />
        <result column="ORDERQTY" property="orderqty" />
        <result column="TRADEQTY" property="tradeqty" />
        <result column="CANCELQTY" property="cancelqty" />
        <result column="WRPRICETYPE" property="wrpricetype" />
        <result column="FIXEDPRICE" property="fixedprice" />
        <result column="ISSPECIFIED" property="isspecified" />
        <result column="PRICEFACTOR" property="pricefactor" />
        <result column="PRICEMOVE" property="pricemove" />
        <result column="FIRSTRATIO" property="firstratio" />
        <result column="WRTRANSFERUSERID" property="wrtransferuserid" />
        <result column="TRADEPRICE" property="tradeprice" />
        <result column="FREEZEFIRSTAMOUNT" property="freezefirstamount" />
        <result column="UNFREEZEFIRSTAMOUNT" property="unfreezefirstamount" />
        <result column="PERFORMANCETEMPLATEID" property="performancetemplateid" />
        <result column="VALIDTYPE" property="validtype" />
        <result column="VALIDTIME" property="validtime" />
        <result column="ORDERTIME" property="ordertime" />
        <result column="CANCELTIME" property="canceltime" />
        <result column="WRTRADEORDERSTATUS" property="wrtradeorderstatus" />
        <result column="CLIENTTICKET" property="clientticket" />
        <result column="ORDERSRC" property="ordersrc" />
        <result column="CLIENTTYPE" property="clienttype" />
        <result column="UUID" property="uuid" />
        <result column="CLIENTORDERTIME" property="clientordertime" />
        <result column="RETCODE" property="retcode" />
        <result column="OPERATORID" property="operatorid" />
        <result column="PRICEDISPLAYMODE" property="pricedisplaymode" />
        <result column="FREEZEMARGIN" property="freezemargin" />
        <result column="UNFREEZEMARGIN" property="unfreezemargin" />
        <result column="FREEZECHARGE" property="freezecharge" />
        <result column="UNFREEZECHARGE" property="unfreezecharge" />
        <result column="MARGINALGORITHM" property="marginalgorithm" />
        <result column="MARGINVALUE" property="marginvalue" />
        <result column="CHARGEALGORITHM" property="chargealgorithm" />
        <result column="CHARGEALGORITHMVALUE" property="chargealgorithmvalue" />
        <result column="CHARGEALGORITHMVALUE2" property="chargealgorithmvalue2" />
        <result column="CANBARGAIN" property="canbargain" />
        <result column="WRBARGAINID" property="wrbargainid" />
        <result column="ATTACHMENT1" property="attachment1" />
        <result column="ATTACHMENT2" property="attachment2" />
        <result column="REMARK" property="remark" />
        <result column="APPLYID" property="applyid" />
        <result column="PERFORMANCEWAY" property="performanceway" />
        <result column="BUYRELEASERATIO" property="buyreleaseratio" />
        <result column="SELLRELEASERATIO" property="sellreleaseratio" />
        <result column="EXECUTEMODE" property="executemode" />
        <result column="EXECUTESIDE" property="executeside" />
        <result column="MINQTY" property="minqty" />
        <result column="HOLDDAYS" property="holddays" />
        <result column="AUCTIONWRTYPE" property="auctionwrtype" />
        <result column="HASWR" property="haswr" />
        <result column="LIMITEDPRICE" property="limitedprice" />
        <result column="CANPART" property="canpart" />
        <result column="WRID" property="wrid" />
        <result column="DELIVERYMONTH" property="deliverymonth" />
        <result column="DELIVERYGOODSID" property="deliverygoodsid" />
        <result column="WRSTANDARDID" property="wrstandardid" />
        <result column="DELISTMINQTY" property="delistminqty" />
        <result column="MARGINFLAG" property="marginflag" />
        <result column="ALLFRIENDSFLAG" property="allfriendsflag" />
        <result column="PRESALEAPPLYID" property="presaleapplyid" />
        <result column="PRESALETYPE" property="presaletype" />
        <result column="EXCHANGERATE" property="exchangerate" />
        <result column="ORDERAMOUNT" property="orderamount" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        WRTRADEORDERID, TRADEDATE, WRTRADETYPE, BUYORSELL, RELATEDWRTRADEORDERID, MARKETID, USERID, ACCOUNTID, WRFACTORTYPEID, ORDERQTY, TRADEQTY, CANCELQTY, WRPRICETYPE, FIXEDPRICE, ISSPECIFIED, PRICEFACTOR, PRICEMOVE, FIRSTRATIO, WRTRANSFERUSERID, TRADEPRICE, FREEZEFIRSTAMOUNT, UNFREEZEFIRSTAMOUNT, PERFORMANCETEMPLATEID, VALIDTYPE, VALIDTIME, ORDERTIME, CANCELTIME, WRTRADEORDERSTATUS, CLIENTTICKET, ORDERSRC, CLIENTTYPE, UUID, CLIENTORDERTIME, RETCODE, OPERATORID, PRICEDISPLAYMODE, FREEZEMARGIN, UNFREEZEMARGIN, FREEZECHARGE, UNFREEZECHARGE, MARGINALGORITHM, MARGINVALUE, CHARGEALGORITHM, CHARGEALGORITHMVALUE, CHARGEALGORITHMVALUE2, CANBARGAIN, WRBARGAINID, ATTACHMENT1, ATTACHMENT2, REMARK, APPLYID, PERFORMANCEWAY, BUYRELEASERATIO, SELLRELEASERATIO, EXECUTEMODE, EXECUTESIDE, MINQTY, HOLDDAYS, AUCTIONWRTYPE, HASWR, LIMITEDPRICE, CANPART, WRID, DELIVERYMONTH, DELIVERYGOODSID, WRSTANDARDID, DELISTMINQTY, MARGINFLAG, ALLFRIENDSFLAG, PRESALEAPPLYID, PRESALETYPE, EXCHANGERATE, ORDERAMOUNT
    </sql>

    <select id="getAllWrTradeOrderMap" parameterType="com.muchinfo.mtp3pojos.vo.query.warehouse.WarehouseCommissionParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.warehouse.WarehouseCommissionResult">
        select
        wr.accountid,
        wr.wrtradeorderid ,
        mk.marketname,
        wr.wrtradetype,
        wr.buyorsell ,
        ws.wrstandardcode,
        ws.wrstandardcode || '/' ||  ws.wrstandardname as wrstandard,
        ft.optioncompare as wrfactortypename,
        wr.orderqty || '(' ||  ei.enumdicname || ')' as orderqtyname,
        wr.tradeqty || '(' ||  ei.enumdicname || ')' as tradeqtyname,
        wr.wrtradeorderstatus ,
        to_char(wr.validtime,'yyyy-MM-dd') as validtime,
        to_char(wr.ordertime,'yyyy-MM-dd hh24:mi:ss') as ordertime,
        to_char(to_date(wr.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') as tradedate,
        lo.loginids || '/' ||  ua.accountname as userid,
        <choose>
            <when test="vo.ishis == 1">
              1 as ishis
            </when>
            <otherwise>
              0 as ishis
            </otherwise>
        </choose>
        <choose>
            <when test="vo.ishis == 1">
                from his_WRTrade_OrderDetail wr
            </when>
            <otherwise>
                from WRTrade_OrderDetail wr
            </otherwise>
        </choose>
        left join WRFactorType ft on wr.wrfactortypeid=ft.wrfactortypeid
        left join useraccount ua on wr.userid = ua.userid
        left join wrstandard ws on ft.wrstandardid = ws.wrstandardid
        left join enumdicitem ei on ei.enumitemname = ws.unitid and ei.enumdiccode='goodsunit'
        left join market mk on  mk.marketid = wr.marketid
        left join VIEW_USER_LOGINIDS lo on ua.userid = lo.userid
        <where>
            <if test="vo.marketid != null">
                and wr.marketid = #{vo.marketid}
            </if>
            <if test="vo.wrtradetype != null">
                and wr.wrtradetype = #{vo.wrtradetype}
            </if>
            <if test="vo.buyorsell != null">
                and wr.buyorsell = #{vo.buyorsell}
            </if>
            <if test="vo.tradeid != null and vo.tradeid != ''">
                and wr.wrtradeorderid like '%'||#{vo.tradeid}||'%'
            </if>
            <if test="vo.accountname != null and vo.accountname !=''">
                and ((ua.accountname like '%'||#{vo.accountname}||'%')
                or(wr.userid like '%'||#{vo.accountname}||'%'))
            </if>
            <if test="vo.handlestatus != null">
                and wr.wrtradeorderstatus = #{vo.handlestatus}
            </if>
            <if test="vo.ishis==1">
                and wr.isvaliddata=1
                <!-- 开始日期 -->
                <if test="vo.hisstartdate != null and vo.hisstartdate != ''">
                    and to_date(wr.histradedate, 'yyyyMMdd') &gt;= to_date(#{vo.hisstartdate},'yyyy-MM-dd')
                </if>
                <!-- 结束日期 -->
                <if test="vo.hisenddate != null and vo.hisenddate != ''">
                    and to_date(wr.histradedate, 'yyyyMMdd') &lt;= to_date(#{vo.hisenddate},'yyyy-MM-dd')
                </if>
            </if>
            <if test="vo.userid != null">
                and ua.subarealevelpath like (select ua.subarealevelpath from useraccount ua where ua.userid =
                #{vo.userid}) || '%'
            </if>
        </where>
        order by wr.ordertime desc,wr.wrtradeorderstatus asc
    </select>

    <select id="getWRTradeGoodsConfigById" parameterType="java.lang.Long"
            resultType="com.muchinfo.mtp3pojos.entity.WrtradeGoodsconfig">
        select gf.wrtradeorderid,
               gf.goodsid,
               gf.pricefactor ,
               gf.pricemove   ,
               gf.weightratio  ,
               gf.goodscode ,
               g.goodsname
        from WRTrade_GoodsConfig gf
                 left join Goods g on gf.goodsid = g.goodsid
        where gf.wrtradeorderid = #{wrtradeorderid}
    </select>

    <select id="getWRTradeMatchDetailById" parameterType="java.lang.Long"
            resultType="com.muchinfo.mtp3pojos.entity.WrtradeMatchdetail">
        select
        m.wrtradeorderid ,
        m.matchuserid ,
        u.accountname
        from WRTrade_MatchDetail m
        left join useraccount u on m.matchuserid =u.userid
        where m.wrtradeorderid=#{wrtradeorderid}
    </select>

    <select id="queryPerformanceStep" parameterType="java.lang.Long"
            resultType="com.muchinfo.mtp3pojos.entity.Performancesteptemplate">
        select p.performancestepid,
               p.planid,
               p.stepindex,
               p.stepdays,
               p.steptypeid,
               pt.StepTypeName,
               p.stepvalue,
               p.stepamount,
               p.realamount,
               p.remaindays,
               p.delaydays,
               p.isauto,
               p.islastreceivestep,
               p.stepstatus,
               p.steplanchtype,
               p.executeside,
               p.starttime,
               p.endtime,
               p.remark,
               p.stepremark
        from PerformanceStep p
                 left join PerformancePlan pl on pl.PerformancePlanID = p.planid
                 left join PerformanceStepType pt on p.StepTypeID = pt.StepTypeID
        where p.planid = #{performancetemplateid}
        order by p.starttime asc
    </select>

    <select id="queryTradeDetail" parameterType="com.muchinfo.mtp3pojos.vo.query.warehouse.WarehouseContractParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.warehouse.WarehouseContractResult">
        select
        tr.accountid,
        tr.wrtradedetailid ,
        lo.loginids || '/' ||  ua.accountname as userid,
        mk.marketname,
        tr.wrtradetype ,
        wr.buyorsell ,
        ws.wrstandardcode,
        ws.wrstandardcode || '/' ||  ws.wrstandardname as wrstandard,
        ft.optioncompare as wrfactortypename,
        wr.orderqty || '(' ||  ei.enumdicname || ')'  as orderqtyname,
        wr.realtradeqty || '(' ||  ei.enumdicname || ')'  as realtradeqtyname,
        tr.tradeprice ,
        case mk.trademode
        when 64 then
        tr.firstratio * tr.tradeamount
        else
        tr.realtradeamount
        end realtradeamount,
        tr.performanceplanid ,
        tr.handlestatus ,
        to_char(tr.tradetime,'yyyy-MM-dd hh24:mi:ss') tradetime,
        <choose>
            <when test="vo.ishis == 1">
                to_char(to_date(tr.histradedate,'yyyy-MM-dd'),'yyyy-MM-dd') tradedate,
            </when>
            <otherwise>
                to_char(to_date(tr.tradedate,'yyyy-MM-dd'),'yyyy-MM-dd') tradedate,
            </otherwise>
        </choose>
        <choose>
            <when test="vo.ishis == 1">
                1 as ishis
            </when>
            <otherwise>
                0 as ishis
            </otherwise>
        </choose>

        <choose>
            <when test="vo.ishis == 1">
                from his_WRTrade_TradeDetail tr
                left join his_WRTrade_OrderDetail wr on tr.wrtradeorderid=wr.wrtradeorderid and wr.IsValidData=1
            </when>
            <otherwise>
                from WRTrade_TradeDetail tr
                left join WRTrade_OrderDetail wr on tr.wrtradeorderid=wr.wrtradeorderid
            </otherwise>
        </choose>
        left join useraccount ua on wr.userid = ua.userid
        left join WRFactorType ft on wr.wrfactortypeid=ft.wrfactortypeid
        left join wrstandard ws on ft.wrstandardid = ws.wrstandardid
        left join enumdicitem ei on ei.enumitemname = ws.unitid and ei.enumdiccode='goodsunit'
        left join market mk on tr.marketid = mk.marketid
        left join VIEW_USER_LOGINIDS lo on ua.userid = lo.userid
        <where>
            <if test="vo.marketid != null">
                and tr.marketid = #{vo.marketid}
            </if>
            <if test="vo.wrtradetype != null">
                and tr.wrtradetype = #{vo.wrtradetype}
            </if>
            <if test="vo.buyorsell != null">
                and wr.buyorsell = #{vo.buyorsell}
            </if>
            <if test="vo.tradeid != null and vo.tradeid != ''">
                and tr.wrtradedetailid like '%'||#{vo.tradeid}||'%'
            </if>
            <if test="vo.accountname != null and vo.accountname !=''">
                and ((ua.accountname like '%'||#{vo.accountname}||'%')
                or(wr.userid like '%'||#{vo.accountname}||'%'))
            </if>
            <if test="vo.ishis==1">
                and tr.IsValidData=1
                <!-- 开始日期 -->
                <if test="vo.hisstartdate != null and vo.hisstartdate != ''">
                    and to_date(tr.HisTradeDate, 'yyyyMMdd') &gt;= to_date(#{vo.hisstartdate},'yyyy-MM-dd')
                </if>
                <!-- 结束日期 -->
                <if test="vo.hisenddate != null and vo.hisenddate != ''">
                    and to_date(tr.HisTradeDate, 'yyyyMMdd') &lt;= to_date(#{vo.hisenddate},'yyyy-MM-dd')
                </if>
            </if>
            <if test="vo.userid != null">
                and ua.subarealevelpath like '%,' || ${vo.userid} || ',%'
            </if>
        </where>
        order by tr.tradetime desc
    </select>


    <select id="querySum" parameterType="com.muchinfo.mtp3pojos.vo.query.warehouse.WarehouseSumParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.warehouse.WarehouseSumResult">
        select
        ws.wrstandardid,
        wh.autoid as warehouseid,
        ws.wrstandardcode || '/' ||  ws.wrstandardname as wrstandardname,
        wh.warehousename ,
        sum(sp.totalqty) as totalqty,
        sum(sp.totalqty) - sum(sp.freezeqty) as avaliableqty,
        sum(sp.freezeqty) as freezeqty,
        sum(sp.increaseqty) as increaseqty,
        sum(sp.decreaseqty) as decreaseqty,
        edi.enumdicname ,
        <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 WRPosition sp
            </when>
            <when test="vo.ishis == 1">
                from HIS_WRPosition sp
            </when>
        </choose>
        left join wrfactortype wf on sp.wrfactortypeid = wf.wrfactortypeid
        left join wrstandard ws on wf.wrstandardid = ws.wrstandardid
        left join enumdicitem edi on ws.unitid = edi.enumitemname and edi.enumdiccode = 'goodsunit'
        left join warehouseinfo wh on wf.warehouseid = wh.autoid
        <where>
            sp.totalqty > 0
            <if test="vo.wrstandardids!=null and vo.wrstandardids.size>0">
                <foreach collection="vo.wrstandardids" item="item" open="and ws.wrstandardid in (" close=")" separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.warehouseids!=null and vo.warehouseids.size>0">
                <foreach collection="vo.warehouseids" item="item" open="and wh.autoid in (" close=")" separator=",">
                    ${item}
                </foreach>
            </if>
            <!-- 开始日期 -->
            <if test="vo.ishis == 1 and vo.hisstartdate != null and vo.hisstartdate != ''">
                and to_date(sp.histradedate, 'yyyyMMdd') &gt;= to_date(#{vo.hisstartdate},'yyyy-MM-dd')
            </if>
            <!-- 结束日期 -->
            <if test="vo.ishis == 1 and vo.hisenddate != null and vo.hisenddate != ''">
                and to_date(sp.histradedate, 'yyyyMMdd') &lt;= to_date(#{vo.hisenddate},'yyyy-MM-dd')
            </if>
            <if test="vo.memberuserid != null">
                and sp.wruserid = #{vo.memberuserid}
            </if>
        </where>
        group by ws.wrstandardid, ws.wrstandardcode, ws.wrstandardname, wh.autoid, wh.warehousecode, wh.warehousename,
        edi.enumdicname
    </select>


    <select id="summaryQueryDetail" parameterType="com.muchinfo.mtp3pojos.vo.query.warehouse.WarehouseSumDetailParam"
            resultType="com.muchinfo.mtp3pojos.vo.query.warehouse.WarehouseSumDetailResult">
        select
        ua.userid || '/' || lo.loginids || '/' || ua.accountname as accountname,
        whl.accountid ,
        decode(whl.subnum,0,to_char(whl.ladingbillid),to_char(whl.ladingbillid) ||'-'||to_char(whl.subnum) ) as ladingbillid,
        whl.qty ,
        whl.freezerqty ,
        wf.wrfactortypename
        <choose>
            <when test="vo.ishis == null or vo.ishis == 0">
                from WRHoldLB whl
            </when>
            <when test="vo.ishis == 1">
                from HIS_WRHoldLB whl
            </when>
        </choose>
        left join useraccount ua on whl.userid = ua.userid
        left join wrfactortype wf on whl.wrfactortypeid = wf.wrfactortypeid
        left join wrstandard ws on wf.wrstandardid = ws.wrstandardid
        left join VIEW_USER_LOGINIDS lo on ua.userid = lo.userid
        <where>
            ws.wrstandardid = #{vo.wrstandardid}
            and whl.qty > 0
            <if test="vo.memberuserid != null">
                and whl.userid = #{vo.memberuserid}
            </if>
            <if test="vo.userids!=null and vo.userids.size>0">
                <foreach collection="vo.userids" item="item" open="and whl.userid in (" close=")" separator=",">
                    ${item}
                </foreach>
            </if>
        </where>
    </select>
</mapper>
