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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.CptradePresalegoodsex">
        <id column="GOODSID" property="goodsid" />
        <result column="APPLYID" property="applyid" />
        <result column="USERID" property="userid" />
        <result column="ACCOUNTID" property="accountid" />
        <result column="RELATEDGOODSID" property="relatedgoodsid" />
        <result column="PRESALEQTY" property="presaleqty" />
        <result column="STARTTIME" property="starttime" />
        <result column="ENDTIME" property="endtime" />
        <result column="ATTACHMENTURL" property="attachmenturl" />
        <result column="PRESALEMODE" property="presalemode" />
        <result column="MARKETID" property="marketid" />
        <result column="REFPRICE" property="refprice" />
        <result column="STARTPRICE" property="startprice" />
        <result column="FLOORPRICE" property="floorprice" />
        <result column="CREATETIME" property="createtime" />
        <result column="TRADEDATE" property="tradedate" />
        <result column="RELATEDMARKETID" property="relatedmarketid" />
        <result column="PRESALEDQTY" property="presaledqty" />
        <result column="PRESALEDAMOUNT" property="presaledamount" />
        <result column="SELLSTATUS" property="sellstatus" />
        <result column="GOODSDETAIL" property="goodsdetail" />
        <result column="TRADEPRICE" property="tradeprice" />
        <result column="BUYMAXQTY" property="buymaxqty" />
        <result column="PRESALEDEPOSITALGORITHM" property="presaledepositalgorithm" />
        <result column="PRESALEDEPOSITVALUE" property="presaledepositvalue" />
        <result column="PRESALESTATUS" property="presalestatus" />
        <result column="PICTUREURLS" property="pictureurls" />
        <result column="BANNERPICURL" property="bannerpicurl" />
        <result column="BASEQTY" property="baseqty" />
        <result column="MAXBUYQTY" property="maxbuyqty" />
        <result column="LOTTERYFLAG" property="lotteryflag" />
        <result column="LOTTERYQTY" property="lotteryqty" />
        <result column="LUCKYQTY" property="luckyqty" />
        <result column="PLACEQTY" property="placeqty" />
        <result column="HANDLESTATUS" property="handlestatus" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        GOODSID, APPLYID, USERID, ACCOUNTID, RELATEDGOODSID, PRESALEQTY, STARTTIME, ENDTIME, ATTACHMENTURL, PRESALEMODE, MARKETID, REFPRICE, STARTPRICE, FLOORPRICE, CREATETIME, TRADEDATE, RELATEDMARKETID, PRESALEDQTY, PRESALEDAMOUNT, SELLSTATUS, GOODSDETAIL, TRADEPRICE, BUYMAXQTY, PRESALEDEPOSITALGORITHM, PRESALEDEPOSITVALUE, PRESALESTATUS, PICTUREURLS, BANNERPICURL, BASEQTY, MAXBUYQTY, LOTTERYFLAG, LOTTERYQTY, LUCKYQTY, PLACEQTY, HANDLESTATUS
    </sql>

    <select id="query" parameterType="com.muchinfo.mtp3pojos.vo.presale.PresaleQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.presale.PresaleQueryResult">
        select t.*,
        m.marketname,
        gg.goodscode,
        gg.goodsname,
        gg.deliveryflag,
        gg.goodsstatus,
        g.goodscode relatedgoodscode,
        g.goodsname relatedgoodsname,
        gg.listingdate,
        gg.lasttradedate,
        u.customername username
        from CPTrade_PresaleGoodsEx t
        left join market m on t.marketid = m.marketid
        left join goods g on t.relatedgoodsid = g.goodsid
        left join goods gg on t.goodsid = gg.goodsid
        left join userinfo u on t.userid = u.userid
        left join taaccount ta on t.accountid = ta.accountid
        <where>
            <if test="vo.goodsid != null">
                and t.goodsid = #{vo.goodsid}
            </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.presalemode != null">
                and t.presalemode = #{vo.presalemode}
            </if>
            <if test="vo.relatedgoodsname != null and vo.relatedgoodsname != ''">
                and ((g.goodscode like '%'||#{vo.relatedgoodsname}||'%')
                or(g.goodsname like '%'||#{vo.relatedgoodsname}||'%'))
            </if>
            <if test="vo.goodsstatus != null">
                and gg.goodsstatus = #{vo.goodsstatus}
            </if>
            <if test="vo.applyid != null and vo.applyid != ''">
                and (t.applyid like '%'||#{vo.applyid}||'%')
            </if>
            <if test="vo.starttime != null and vo.starttime != ''">
                and t.starttime &gt;= to_date(#{vo.starttime}||',00:00:00','yyyyMMdd,hh24:mi:ss')
                and t.starttime &lt;= to_date(#{vo.starttime}||',23:59:59','yyyyMMdd,hh24:mi:ss')
            </if>
            <if test="vo.endtime != null and vo.endtime != ''">
                and t.endtime &gt;= to_date(#{vo.endtime}||',00:00:00','yyyyMMdd,hh24:mi:ss')
                and t.endtime &lt;= to_date(#{vo.endtime}||',23:59:59','yyyyMMdd,hh24:mi:ss')
            </if>
            <if test="vo.areauserid != null">
                and ta.relateduserid = #{vo.areauserid}
            </if>
        </where>
        order by t.createtime desc
    </select>

    <select id="view" parameterType="java.lang.String" resultType="com.muchinfo.mtp3pojos.vo.presale.PresaleQueryDetailResult">
        select t.*,
        m.marketname,
        gg.goodscode,
        gg.goodsname,
        gg.deliveryflag,
        gg.goodsstatus,
        g.goodscode relatedgoodscode,
        g.goodsname relatedgoodsname,
        gg.listingdate,
        gg.lasttradedate,
        u.customername username
        from CPTrade_PresaleGoodsEx t
        left join market m on t.marketid = m.marketid
        left join goods g on t.relatedgoodsid = g.goodsid
        left join goods gg on t.goodsid = gg.goodsid
        left join userinfo u on t.userid = u.userid
        left join taaccount ta on t.accountid = ta.accountid
        <where>
            <if test="applyid != null and applyid != ''">
                and t.applyid = #{applyid}
            </if>
        </where>
        order by t.createtime desc
    </select>


    <select id="viewPresaleLottery" parameterType="java.lang.Long"
            resultType="com.muchinfo.mtp3pojos.vo.presale.PresalePresaleLotteryResult">
        select wp.presaleapplyid,
               wp.wrtradeorderid,
               wp.drawlotnum,
               wp.userid,
               wp.accountid,
               u.accountname
        from WR_PresaleLottery wp
                 left join useraccount u on wp.userid = u.userid
        where wp.presaleapplyid = #{goodsid}
          and wp.islucky = '1'
        order by wp.drawlotnum
    </select>

    <select id="viewfinsh"  parameterType="java.lang.Long"
            resultType="com.muchinfo.mtp3pojos.vo.presale.PresaleFinishResult">
        select wp.presaleapplyid,
               wp.buyuserid,
               wp.buyaccountid,
               u.accountname,
               wp.placeqty,
               wp.placestatus
        from WR_PresalePlaceResult wp
                 left join useraccount u on wp.buyuserid = u.userid
        where wp.presaleapplyid = #{goodsid}
        order by wp.createtime desc
    </select>

    <select id="queryUserList" parameterType="java.lang.Long"
            resultType="com.muchinfo.mtp3pojos.vo.presale.PresaleFinishInitAddResult">
        select u.userid,
               o.accountname,
               t.accountid
        from userinfo u
                 left join useraccount o on o.userid = u.userid
                 left join taaccount t on o.userid = t.userid
        where o.accountstatus = '4'
          and u.userstatus = '1'
          and u.usertype in (2)
          and u.userid!=#{userid}
          and o.userid in (select a.userid from AREAROLE a where a.roletype in ('6'))
    </select>

    <select id="queryApply" parameterType="com.muchinfo.mtp3pojos.vo.presale.PresaleApplyParam"
            resultType="com.muchinfo.mtp3pojos.vo.presale.PresaleApplyResult">
        select t.*,
        m.marketname,
        g.goodscode relatedgoodscode,
        g.goodsname relatedgoodsname,
        to_char(g1.listingdate, 'yyyy-MM-dd') "listingdate1",
        to_char(g1.lasttradedate, 'yyyy-MM-dd') "lasttradedate1",
        g.deliveryflag,
        u.customername username,
        s.username firstauditname,
        sm.username secondauditname
        from CPTrade_PresaleApply t
        left join goods g1 on t.goodscode = g1.goodscode
        left join market m on t.marketid = m.marketid
        left join goods g on t.relatedgoodsid = 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 taaccount ta on t.accountid = ta.accountid
        <where>
            <if test="vo.applyid != null and vo.applyid != ''">
                and (t.applyid like '%'||#{vo.applyid}||'%')
            </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.relatedgoodsname != null and vo.relatedgoodsname != ''">
                and ((g.goodscode like '%'||#{vo.relatedgoodsname}||'%')
                or(g.goodsname like '%'||#{vo.relatedgoodsname}||'%'))
            </if>
            <if test="vo.applytime != null and vo.applytime != ''">
                and t.applytime &gt;= to_date(#{vo.applytime}||',00:00:00','yyyyMMdd,hh24:mi:ss')
                and t.applytime &lt;= to_date(#{vo.applytime}||',23:59:59','yyyyMMdd,hh24:mi:ss')
            </if>
            <if test="vo.applystatus != null">
                and t.applystatus = #{vo.applystatus}
            </if>
            <if test="vo.presalemode != null">
                and t.presalemode = #{vo.presalemode}
            </if>
            <if test="vo.areauserid != null">
                and ta.relateduserid = #{vo.areauserid}
            </if>
        </where>
        order by t.applytime desc
    </select>
</mapper>
