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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.Goods">
        <id column="GOODSID" property="goodsid" />
        <result column="GOODSCODE" property="goodscode" />
        <result column="GOODSNAME" property="goodsname" />
        <result column="MARKETID" property="marketid" />
        <result column="GOODSGROUPID" property="goodsgroupid" />
        <result column="GOODSSTATUS" property="goodsstatus" />
        <result column="CURRENCYID" property="currencyid" />
        <result column="GOODUNITID" property="goodunitid" />
        <result column="AGREEUNIT" property="agreeunit" />
        <result column="DECIMALPLACE" property="decimalplace" />
        <result column="LISTINGDATE" property="listingdate" />
        <result column="LASTTRADEDATE" property="lasttradedate" />
        <result column="DELISTINGDATE" property="delistingdate" />
        <result column="DELIVERYFLAG" property="deliveryflag" />
        <result column="HASQUOTER" property="hasquoter" />
        <result column="HQPROTECTTIME" property="hqprotecttime" />
        <result column="QUOTESHOWTYPE" property="quoteshowtype" />
        <result column="QUOTESHOWTYPEINTERVAL" property="quoteshowtypeinterval" />
        <result column="MODIFYSTATUS" property="modifystatus" />
        <result column="GOODSTRADETYPE" property="goodstradetype" />
        <result column="CREATORID" property="creatorid" />
        <result column="CREATETIME" property="createtime" />
        <result column="AUDITACCOUNTID" property="auditaccountid" />
        <result column="AUDITTIME" property="audittime" />
        <result column="MODIFYTIME" property="modifytime" />
        <result column="MODIFIERID" property="modifierid" />
        <result column="CANCELACCOUNTID" property="cancelaccountid" />
        <result column="REMARK" property="remark" />
        <result column="CANCELTIME" property="canceltime" />
        <result column="DELISTINGMODE" property="delistingmode" />
        <result column="FORCECLOSEMODE" property="forceclosemode" />
        <result column="FORCECLOSEVALUE" property="forceclosevalue" />
        <result column="CUTMODE" property="cutmode" />
        <result column="QUOTEMINUNIT" property="quoteminunit" />
        <result column="ISBUYLIMITED" property="isbuylimited" />
        <result column="INNERDEALMODE" property="innerdealmode" />
        <result column="OUTERDEALMODE" property="outerdealmode" />
        <result column="CLOSEPRICEMODE" property="closepricemode" />
        <result column="CLOSEPRICEPARAM" property="closepriceparam" />
        <result column="QTYDECIMALPLACE" property="qtydecimalplace" />
        <result column="GOODSCURRENCYID" property="goodscurrencyid" />
        <result column="GOODSQUOTETYPE" property="goodsquotetype" />
        <result column="QUOTEGEAR" property="quotegear" />
        <result column="OUTGOODSCODE" property="outgoodscode" />
        <result column="ISVISIBLE" property="isvisible" />
        <result column="AREAUSERID" property="areauserid" />
        <result column="PICTUREURL" property="pictureurl" />
        <result column="REFGOODSID" property="refgoodsid" />
        <result column="SWAPDAYS" property="swapdays" />
        <result column="REFGOODSCODE" property="refgoodscode" />
        <result column="PROVIDERUSERID" property="provideruserid" />
        <result column="PROVIDERACCOUNTID" property="provideraccountid" />
        <result column="TRANSFERDEPOSITRATIO" property="transferdepositratio" />
        <result column="PRESALEPRICE" property="presaleprice" />
        <result column="PRESALEFLAG" property="presaleflag" />
        <result column="NEWTRANSFERDEPOSITRATIO" property="newtransferdepositratio" />
        <result column="MARGINALGORITHM" property="marginalgorithm" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        GOODSID, GOODSCODE, GOODSNAME, MARKETID, GOODSGROUPID, GOODSSTATUS, CURRENCYID, GOODUNITID, AGREEUNIT, DECIMALPLACE, LISTINGDATE, LASTTRADEDATE, DELISTINGDATE, DELIVERYFLAG, HASQUOTER, HQPROTECTTIME, QUOTESHOWTYPE, QUOTESHOWTYPEINTERVAL, MODIFYSTATUS, GOODSTRADETYPE, CREATORID, CREATETIME, AUDITACCOUNTID, AUDITTIME, MODIFYTIME, MODIFIERID, CANCELACCOUNTID, REMARK, CANCELTIME, DELISTINGMODE, FORCECLOSEMODE, FORCECLOSEVALUE, CUTMODE, QUOTEMINUNIT, ISBUYLIMITED, INNERDEALMODE, OUTERDEALMODE, CLOSEPRICEMODE, CLOSEPRICEPARAM, QTYDECIMALPLACE, GOODSCURRENCYID, GOODSQUOTETYPE, QUOTEGEAR, OUTGOODSCODE, ISVISIBLE, AREAUSERID, PICTUREURL, REFGOODSID, SWAPDAYS, REFGOODSCODE, PROVIDERUSERID, PROVIDERACCOUNTID, TRANSFERDEPOSITRATIO, PRESALEPRICE, PRESALEFLAG, NEWTRANSFERDEPOSITRATIO, MARGINALGORITHM
    </sql>
    <select id="queryGoodsSelect" resultType="com.muchinfo.mtp3pojos.vo.common.GoodsSelectResult">
        select * from (
        select g.goodsid as goodsId, g.goodscode as goodsCode, g.goodsname as goodsName , g.goodunitid
        ,gex.goodsnameth ,gex.goodsnameen ,gex.goodsnametw ,gex.goodsnamevi
        from goods g
        left join market m on g.marketid = m.marketid
        left join goodsex gex on g.goodsid = gex.goodsid
        <where>
            1 = 1
            <if test="vo.split!=null and vo.split.length>0">
                <foreach collection="vo.split" item="item" open="and g.goodsstatus in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.areauserid != null and vo.areauserid != 0 and vo.areauserid != 1">
                and g.marketid in (select marketid from AREAROLEMARKET where areauserid = #{vo.areauserid,jdbcType=DECIMAL})
            </if>
            <if test="vo.searchCode != null and vo.searchCode != '' ">
                and (g.goodsname like
                '%'||#{vo.searchCode,jdbcType=VARCHAR}||'%'
                or g.goodscode like
                '%'||#{vo.searchCode,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.marketId != null and vo.marketId != '' ">
                and g.marketid = #{vo.marketId}
            </if>
            <if test="vo.deliveryflag != null">
                and g.deliveryflag = #{vo.deliveryflag}
            </if>
            <if test="vo.yyareauserid != null and vo.yyareauserid !=''">
                and g.goodsid in(select t.goodsid from AreaGoodsConfig t where t.userid= #{vo.yyareauserid})
            </if>
            <if test="vo.tradeModeSplit!=null and vo.tradeModeSplit.length>0">
                <foreach collection="vo.tradeModeSplit" item="item" open=" and m.trademode in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
            <if test="vo.excludetrademodelarray!=null and vo.excludetrademodelarray.length>0">
                <foreach collection="vo.excludetrademodelarray" item="item" open=" and m.trademode not in (" close=")"
                         separator=",">
                    ${item}
                </foreach>
            </if>
        </where>
        order by g.goodscode) t where rownum &lt;= 10
    </select>

    <select id="getNoTradeConfigGoods" parameterType="map" resultMap="BaseResultMap">
        select t.* ,gex.goodsnameth ,gex.goodsnameen ,gex.goodsnametw ,gex.goodsnamevi from goods t
        left join goodsex gex on t.goodsid = gex.goodsid
        where t.marketid = #{marketid} and t.goodsstatus in (2,3)
        and not exists (
        select 1 from usergrouptradeconfig u where u.goodsid = t.goodsid
        and
        <if test="areauserid != null">u.memberuserid=#{areauserid} and</if>
        u.marketid = #{marketid} and u.usergroupid=#{usergroupid}
        )
    </select>

    <select id="queryByDate"
            parameterType="com.muchinfo.mtp3pojos.vo.reportform.GoodsReportParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.GoodsReportResult">
        select t.reckondate,
        t.goodsid,
        g.goodsname,
        t.marketid,
        m.marketname,
        nvl(t.buyinterest, 0) + nvl(t.sellinterest, 0) as totalinterest,
        (nvl(t.buyorderqty,0) + nvl(t.sellorderqty,0)) as totalorderqty,
        (nvl(t.curbuyholdqty,0) + nvl(t.cursellholdqty,0)) as totalholdqty,
        t.tradeqty,
        t.curbuyholdqty,t.oribuyholdqty,t.cursellholdqty,t.orisellholdqty,
        t.tradelot,
        t.tradeamount,
        (nvl(t.Buycharge,0) + nvl(t.sellcharge,0)) as totalcharge,
        t.buyinterest,
        t.sellinterest,
        t.holdaccountcount,
        t.orderaccountcount,
        t.tradeaccountcount,
        g.goodscode
        from
        reckon_daygoodscount t
        left join goods g on g.goodsid = t.goodsid
        left join Market m on m.marketid = t.marketid
        <where>
            m.trademode not in (40,41,48,51)
            <if test="vo.reckondate != null and vo.reckondate != '' ">
                and t.reckondate= #{vo.reckondate,jdbcType=CHAR}
            </if>
            <if test="vo.areauserid != null and vo.areauserid != '' ">
                and t.marketid in
                (select m.marketid from market m
                where m.exchareaid = #{vo.areauserid})
            </if>
        </where>
        order by t.tradeqty desc
    </select>

    <select id="queryByOther"
            parameterType="com.muchinfo.mtp3pojos.vo.reportform.GoodsReportParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.GoodsReportResult">
        select t.cycletype,
        t.cycletime as reckondate,
        t.goodsid,
        g.goodsname,
        t.marketid,
        m.marketname,
        nvl(t.buyinterest, 0) + nvl(t.sellinterest, 0) as totalinterest,
        (nvl(t.buyorderqty,0) + nvl(t.sellorderqty,0)) as totalorderqty,
        (nvl(t.curbuyholdqty,0) + nvl(t.cursellholdqty,0)) as totalholdqty,
        t.tradeqty,
        t.curbuyholdqty,t.oribuyholdqty,t.cursellholdqty,t.orisellholdqty,
        t.tradelot,
        t.tradeamount,
        (nvl(t.Buycharge,0) + nvl(t.sellcharge,0)) as totalcharge,
        t.buyinterest,
        t.sellinterest,
        t.holdaccountcount,
        t.orderaccountcount,
        t.tradeaccountcount,
        g.goodscode
        from
        report_goodscount t
        left join goods g on g.goodsid = t.goodsid
        left join Market m on m.marketid = t.marketid
        <where>
            m.trademode not in (40,41,48,51)
            <if test="vo.cycletype !=null and vo.cycletype != ''">
                and t.cycletype=#{vo.cycletype}
            </if>
            <if test="vo.reckondate !=null and vo.reckondate != '' and vo.cycletype != 4">
                and t.cycletime = #{vo.reckondate}
            </if>
            <if test="vo.reckondate !=null and vo.reckondate != '' and vo.cycletype == 4">
                and t.cycletime= (select to_char(trunc(to_date(#{vo.reckondate}, 'YYYYMMDD'),'iw')+3,'yyyy') ||
                to_char(to_date(#{vo.reckondate}, 'YYYYMMDD'), 'iw') from dual)
            </if>
            <if test="vo.areauserid != null and vo.areauserid != '' ">
                and t.marketid in
                (select m.marketid from market m
                where m.exchareaid = #{vo.areauserid})
            </if>
        </where>
        order by t.tradeqty desc
    </select>

    <select id="queryAreaGoodsConfig" resultType="com.muchinfo.mtp3pojos.vo.organ.GoodsForRoleVo">
        select g.goodsid , g.marketid ,
        g.goodscode , g.goodsname
        from  goods g
        inner join AreaGoodsConfig t on g.goodsid = t.goodsid
        where t.userid = #{userid}
        order by g.goodscode
    </select>
    <select id="getNoTradeConfigGoodsForAccount" resultType="com.muchinfo.mtp3pojos.entity.Goods"
            parameterType="com.muchinfo.mtp3pojos.vo.investor.PersonalizationDelParam">
        select * from goods t where t.marketid = #{vo.marketid}
        and t.goodsstatus in (2,3)
        and not exists (
        select 1 from accounttradeconfig u where u.goodsid = t.goodsid
        <if test="vo.memberuserid != null"> and u.memberuserid=#{vo.memberuserid} </if>
        and u.accountid = #{vo.accountid} and u.userid=#{vo.userid}
        <choose>
            <when test="vo.tradetype!=null and vo.tradetype==2">
                and u.tradetype=2
            </when>
            <otherwise>
                and (u.tradetype=1 or u.tradetype is null)
            </otherwise>
        </choose>
        )
    </select>
    <select id="groupSetQuery" resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.goodssetup.GoodsGroupSetQueryResult">
        select g.goodsgroupid,
        g.outergroupcode,
        g.goodsgroupname,
        g.goodsgroupstatus,
        g.marketid,
        g.exexchangeid,
        g.quotesourcegroupid,
        g.currencyid,
        g.goodunitid,
        g.createtime,
        g.creatorid,
        g.modifytime,
        g.modifierid,
        gc.categoryname
        from goodsgroup g
        join market m on g.marketid = m.marketid
        left join groupcategory gc on g.groupcategroyid = gc.categoryid
        <where>
            <choose>
                <when test="vo.tradeMode!=null and vo.tradeMode==15">
                    and m.trademode = 15
                </when>
                <otherwise>
                    and m.trademode != 15
                </otherwise>
            </choose>
            <if test="vo.marketid != null and vo.marketid != '' ">
                and g.marketid = #{vo.marketid,jdbcType=DECIMAL}
            </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.goodsgroupname != null and vo.goodsgroupname != '' ">
                and g.goodsgroupname like '%'||#{vo.goodsgroupname,jdbcType=VARCHAR}||'%'
            </if>
            <if test="vo.goodsgroupstatus != null and vo.goodsgroupstatus != -1 ">
                and g.goodsgroupstatus = #{vo.goodsgroupstatus,jdbcType=DECIMAL}
            </if>
            <if test="vo.exchareaid != null and vo.exchareaid != ''">
                AND m.exchareaid = #{vo.exchareaid,jdbcType=DECIMAL}
            </if>
        </where>
        order by g.goodsgroupname
    </select>
    <select id="getTradeRuleByGroupid"
            resultType="com.muchinfo.mtp3pojos.entity.Goodsgrouptraderule"
            parameterType="java.lang.Long">
        select g.ruleid,g.paramvalue,t.ismandatory,t.ispersonalized
        from goodsgrouptraderule g,goodsgroup gg,traderuleconfig t,market m
        where g.goodsgroupid = #{goodsgroupid}
          and t.ruleid = g.ruleid
          and g.goodsgroupid = gg.goodsgroupid
          and gg.marketid = m.marketid
          and t.trademodeid = m.trademode
        order by t.ismandatory desc,g.ruleid
    </select>
    <select id="getTradeFeeByGroupid" resultType="com.muchinfo.mtp3pojos.entity.Goodsgrouptradefee"
            parameterType="java.lang.Long">
        select
            g.*,
            t.tradefeedescriptionid,
            t.ismandatory,
            t.ispersonalized
        from goodsgrouptradefee g,tradefeeconfig t,goodsgroup gg,market m,tradefeedescription td
        where td.tradefeeid = g.tradefeeid
          and td.tradefeedescriptionid = t.tradefeedescriptionid
          and g.goodsgroupid = gg.goodsgroupid
          and gg.marketid = m.marketid
          and m.tradeproperty = t.tradepropertyid
          and g.goodsgroupid = #{goodsgroupid}
        order by t.ismandatory desc, g.tradefeeid
    </select>
    <select id="goodsQuery" resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.goodssetup.GoodsQueryResult">
        select
        g.goodsid,
        g.goodscode,
        g.goodsname,
        g.marketid,
        g.goodsgroupid,
        g.goodsstatus,
        g.modifystatus,
        g.LISTINGDATE,
        g.DELISTINGDATE,
        g.remark,
        g.hasquoter,
        m.trademode,
        g.provideruserid,
        g.provideraccountid,
        g.transferdepositratio,
        g.presaleflag,
        gx.drauditflag,
        gx.tmptransferdepositratio,
        gx.goodsnameen,
        gx.goodsnametw,
        gx.goodsnameth,
        gx.goodsnamevi,
        m.riskcontrolmode
        from goods g
        inner join market m on m.marketid = g.marketid
        left join goodsex gx on gx.goodsid = g.goodsid
        where m.trademode != 15
        <if test="vo.type != null and vo.type != '' ">
            <choose>
                <when test="type==9">
                    and g.goodsstatus = 1
                </when>
                <when test="type==8">
                    and g.modifystatus = 3
                </when>
            </choose>
        </if>

        <if test="vo.marketid != null and vo.marketid != '' ">
            and g.marketid = #{vo.marketid,jdbcType=DECIMAL}
        </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.goodsgroupid != null and vo.goodsgroupid != '' ">
            and g.goodsgroupid = #{vo.goodsgroupid,jdbcType=DECIMAL}
        </if>
        <if test="vo.goodscode != null and vo.goodscode != '' ">
            and g.goodscode like
            '%'||#{vo.goodscode,jdbcType=VARCHAR}||'%'
        </if>
        <if test="vo.goodsname != null and vo.goodsname != '' ">
            and g.goodsname like
            '%'||#{vo.goodsname,jdbcType=VARCHAR}||'%'
        </if>
        <if test="vo.exchareaid != null and vo.exchareaid != ''">
            AND m.exchareaid = #{vo.exchareaid,jdbcType=DECIMAL}
        </if>
        <choose>
            <when test="vo.goodsstatus != null and vo.goodsstatus != -1">
                and g.goodsstatus = #{vo.goodsstatus}
            </when>
        </choose>
        order by g.goodsstatus,g.goodscode
    </select>
    <select id="getTradeRuleByGoodsId" resultType="com.muchinfo.mtp3pojos.entity.Goodstraderule"
            parameterType="java.lang.Long">
        with GRULE as (
            select g.qtydecimalplace qtydecimalplace,
                   t.RULEID,td.RULENAME,t.PARAMVALUE,decode(gtt.RULEID,null,tc.ismandatory,1) as ISMANDATORY,TD.REGEXPRESS,TD.REMARK,'2' as flag
            from
                GOODSTRADERULE t left join (select ggt.RULEID from GOODSGROUPTRADERULE ggt,goods g1 where g1.goodsgroupid=ggt.goodsgroupid and g1.goodsid=#{goodsid,jdbcType=DECIMAL})gtt on t.RULEID=gtt.RULEID
               ,TRADERULEDESCRIPTION td,TRADERULECONFIG tc,GOODS g,MARKET m
            where t.RULEID = td.RULEID AND TD.RULEID = TC.RULEID AND t.GOODSID = g.GOODSID
              AND g.MARKETID = m.MARKETID AND TC.TRADEMODEID = m.TRADEMODE
              AND t.goodsid = #{goodsid,jdbcType=DECIMAL}
        ),
             GGRULE as (
                 select gg.qtydecimalplace qtydecimalplace,
                        ggt.RULEID,td.RULENAME,ggt.PARAMVALUE,1 as ISMANDATORY,TD.REGEXPRESS,TD.REMARK, '1' as flag
                 from GOODSGROUPTRADERULE ggt,TRADERULEDESCRIPTION td,TRADERULECONFIG tc,GOODSGROUP gg,MARKET m
                 where ggt.RULEID = td.RULEID AND TD.RULEID = TC.RULEID AND ggt.GoodsGroupID = GG.GOODSGROUPID
                   AND GG.MARKETID = m.MARKETID AND TC.TRADEMODEID = m.TRADEMODE
                   AND ggt.GoodsGroupID in (SELECT GOODSGROUPID FROM GOODS WHERE goodsid = #{goodsid,jdbcType=DECIMAL} )
                   AND NOT EXISTS (SELECT 1 FROM GRULE A WHERE ggt .RULEID = A .RULEID)
             )
        select * from GRULE union select * from GGRULE
    </select>
    <select id="getTradeFeeByGoodsId" resultType="com.muchinfo.mtp3pojos.entity.Goodstradefee"
            parameterType="java.lang.Long">
        with GFEE as (
            select
                t.TRADEFEEID,td.TRADEFEENAME,T.FeeAlgorithm,t.EXCHANGEVALUE,T.MemberMinValue,T.MemberDefaultValue,
                T.MemberMaxValue,decode(gtt.tradefeeid,null,tc.ismandatory,1) as ISMANDATORY, '2' as flag
            from
                GOODSTRADEFEE t left join (select ggt.tradefeeid from GOODSGROUPTRADEFEE ggt,goods g1 where g1.goodsgroupid=ggt.goodsgroupid and g1.goodsid=#{goodsid,jdbcType=DECIMAL})gtt on t.TRADEFEEID=gtt.TRADEFEEID
               ,TRADEFEEDESCRIPTION td,TRADEFEECONFIG tc,GOODS g,MARKET m
            where t.TRADEFEEID = td.TRADEFEEID AND TD.TRADEFEEDESCRIPTIONID = TC.TRADEFEEDESCRIPTIONID
              AND t.GOODSID = g.GOODSID
              AND g.MARKETID = m.MARKETID AND TC.TRADEPROPERTYID = m.TRADEPROPERTY
              AND t.goodsid = #{goodsid,jdbcType=DECIMAL}
        ),
             GGFEE as (
                 select
                     ggt.TRADEFEEID,td.TRADEFEENAME,ggt.FeeAlgorithm,ggt.EXCHANGEVALUE,ggt.MemberMinValue,ggt.MemberDefaultValue,
                     ggt.MemberMaxValue,1 as ISMANDATORY,'1' as flag
                 from GOODSGROUPTRADEFEE ggt,TRADEFEEDESCRIPTION td,TRADEFEECONFIG tc,GOODSGROUP gg,MARKET m
                 where ggt.TRADEFEEID = td.TRADEFEEID AND TD.TRADEFEEDESCRIPTIONID = TC.TRADEFEEDESCRIPTIONID
                   AND ggt.GoodsGroupID = GG.GOODSGROUPID
                   AND GG.MARKETID = m.MARKETID AND TC.TRADEPROPERTYID = m.TRADEPROPERTY
                   AND ggt.GoodsGroupID in (SELECT GOODSGROUPID FROM GOODS WHERE goodsid = #{goodsid,jdbcType=DECIMAL})
                   AND NOT EXISTS (SELECT 1 FROM GFEE A WHERE ggt.TRADEFEEID = A.TRADEFEEID)
             )
        select * from GFEE union select * from GGFEE
    </select>
    <select id="goodsuspended" resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.goodssetup.GoodsUspendedQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.tradingmarket.goodssetup.GoodsUspendedQueryParam">
        SELECT DISTINCT
        (TO_CHAR (
        TO_DATE (CLOSEDATE, 'yyyy-MM-dd'),
        'yyyy-MM-dd'
        )) AS closedate
        FROM
        GOODSCLOSEPLAN Gd,MARKET m,GOODS g
        LEFT JOIN GOODSGROUP gp ON gp.GoodsGroupid = g.GoodsGroupid
        WHERE SUBSTR(CLOSEDATE, 0, 4) = #{year,jdbcType=VARCHAR}
        AND g.GOODSID = Gd.GOODSID
        and g.marketid = m.MarketID
        <if test="vo.marketId != null">
            AND G.MARKETID = #{vo.marketId,jdbcType=DECIMAL}
        </if>
        <if test="vo.goodsGroupId != null">
            AND G.goodsGroupId = #{vo.goodsGroupId,jdbcType=DECIMAL}
        </if>
        <if test="vo.goodsid != null">
            AND G.goodsid = #{vo.goodsid,jdbcType=DECIMAL}
        </if>
        <if test="vo.exchareaid != null and vo.exchareaid != ''">
            AND m.exchareaid = #{vo.exchareaid,jdbcType=DECIMAL}
        </if>
    </select>
    <select id="sreach" resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.goodssetup.GoodsSearchQueryResult">
        SELECT
        gdr.AUTOID,
        gdr.GOODSID,
        G .GOODSNAME,
        m.marketname as marketName,
        gdr.CLOSEDATE as rundate,
        gdr.STARTTIME,
        gdr.ENDTIME,
        GDR.MODIFYTIME,
        SM.USERNAME as modifiername
        FROM GOODS G,MARKET m,GOODSCLOSEPLAN gdr
        LEFT JOIN SYSTEMMANAGER sm ON gdr.MODIFIERID = SM.AUTOID
        where gdr.GOODSID = G .GOODSID
        and g.marketid = m.MarketID
        and GDR.CLOSEDATE = #{vo.rundate}
        <if test="vo.goodsName != null and vo.goodsName != ''">
            AND G.GOODSNAME like '%'||#{vo.goodsName,jdbcType=VARCHAR}||'%'
        </if>
        <if test="vo.exchareaid != null and vo.exchareaid != ''">
            AND m.exchareaid = #{vo.exchareaid,jdbcType=DECIMAL}
        </if>
        ORDER BY gdr.GOODSID DESC
    </select>
    <select id="queryDelistGoodsInfo"
            resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.goodssetup.DelistGoodsInfoResult">
        select g.goodscode || '(' || g.goodsname || ')' "goodsName",
        m.marketName "marketName",
        m.trademode "trademode",
        decode(m.trademode,18,3,g.delistingmode) "delistingmode",
        g.goodsId "goodsId"
        from goods g, market m
        where g.goodsstatus = 7
        and g.marketid = m.marketid
        and m.trademode not in (40,41,48,51)
        <if test="null != vo.goodsName">
            and (g.goodscode like '%'||#{vo.goodsName}||'%' or g.goodsname like '%'||#{vo.goodsName}||'%')
        </if>
        <if test="null != vo.marketid">
            and g.marketid = #{vo.marketid}
        </if>
    </select>
</mapper>
