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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.ReckonDaydividecountdetail">
        <id column="TRADEDATE" property="tradedate" />
        <result column="GOODSID" property="goodsid" />
        <result column="AREAID" property="areaid" />
        <result column="AREATYPE" property="areatype" />
        <result column="TRADEFEETYPE" property="tradefeetype" />
        <result column="ACCOUNTCURRENCYID" property="accountcurrencyid" />
        <result column="AREAACCOUNTID" property="areaaccountid" />
        <result column="TOTALCOUNT" property="totalcount" />
        <result column="TOTALFEE" property="totalfee" />
        <result column="DIVIDEAMOUNT" property="divideamount" />
        <result column="MARKETID" property="marketid" />
        <result column="PARENTUSERID" property="parentuserid" />
        <result column="PARENTACCOUNTID" property="parentaccountid" />
        <result column="SERIALNUMBER" property="serialnumber" />
        <result column="SENDSTATUS" property="sendstatus" />
        <result column="DIVIDESTATUS" property="dividestatus" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        TRADEDATE, GOODSID, AREAID, AREATYPE, TRADEFEETYPE, ACCOUNTCURRENCYID, AREAACCOUNTID, TOTALCOUNT, TOTALFEE, DIVIDEAMOUNT, MARKETID, PARENTUSERID, PARENTACCOUNTID, SERIALNUMBER, SENDSTATUS, DIVIDESTATUS
    </sql>


    <select id="queryDayDivide" parameterType="com.muchinfo.mtp3pojos.vo.reportform.ShareAmountQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.ShareAmountQueryResult">
        <choose>
            <when test="vo.searchtype!=null and vo.searchtype==3">
                select
                t.tradedate, t.areaid, t.goodsid, t.areatype, t.tradefeetype,
                t.areaaccountid, t.totalcount, t.totalfee,
                t.divideamount,m.tradeproperty,g.goodscode,g.goodsname,u.accountname areaname
                from reckon_daydividecountdetail t
                left join (
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from goods t
                union
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from presale_listinginfo t
                union
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from auction_orderinfo t
                union
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from his_presale_listinginfo t where t.isvaliddata =
                1
                union
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from his_auction_orderinfo t where t.isvaliddata = 1
                union
                select t.goodsid, t.goodscode,t.goodscode goodsname,t.marketid from his_tender_tenderinfo t where
                t.isvaliddata = 1
                union
                select t.goodsid, t.goodscode, t.goodscode goodsname, t.marketid from tender_tenderinfo t
                union
                select m.marketid goodsid, to_char(m.marketid) goodscode,
                m.marketname goodsname, m.marketid from market m where m.trademode = 17
                ) g on t.goodsid = g.goodsid
                left join market m on t.marketid = m.marketid
                left join useraccount u on t.areaid=u.userid
                <where>
                    <if test="vo.tradedate != null and vo.tradedate != '' ">
                        and t.tradedate = #{vo.tradedate,jdbcType=CHAR}
                    </if>
                    <if test="vo.areaid != null">
                        <choose>
                            <when test="vo.ismarketing!=null">
                                and u.parentuserid = #{vo.areaid} and u.usertype=2
                            </when>
                            <otherwise>
                                and t.areaid = #{vo.areaid}
                            </otherwise>
                        </choose>
                    </if>

                    <if test="vo.parentuserid != null">
                        and t.areaid in (select userid from useraccount uo where
                        uo.userid=${vo.parentuserid} or uo.parentuserid=${vo.parentuserid})
                    </if>
                </where>
            </when>
            <otherwise>
                select fee.*,m.tradeproperty,
                m.marketname marketname,
                ed.enumdicname || '/' || fee.tradefeetypedesc dividefeename,
                case when g.goodscode is null
                then to_char(m.marketid) || '(' || m.marketname || ')'
                else g.goodscode || '(' || g.goodsname || ')'
                end goodsname
                from (select t.goodsid,
                t.marketid,t.areaid,
                t.tradefeetype,
                case
                when substr(to_char(t.tradefeetype), 0,1) = '2' then
                '会员部分'
                else
                '平台部分'
                end tradefeetypedesc,
                2 searchtype,
                t.areatype,
                t.areaaccountid,
                t.tradedate,
                g.goodscode,
                sum(t.totalcount) totalcount,
                sum(t.totalfee) totalfee,
                sum(t.divideamount) divideamount
                from reckon_daydividecountdetail t
                <where>
                    and t.tradefeetype in (105,106,205, 206, 305, 306)
                    <if test="vo.tradedate != null and vo.tradedate != '' ">
                        and t.tradedate = #{vo.tradedate,jdbcType=CHAR}
                    </if>
                    <if test="vo.areaid != null">
                        <choose>
                            <when test="vo.ismarketing!=null">
                                and t.areaid in (
                                select userid from useraccount ua where ua.parentuserid=#{vo.areaid}
                                )
                            </when>
                            <otherwise>
                                and t.areaid = #{vo.areaid}
                            </otherwise>
                        </choose>
                    </if>
                    <if test="vo.parentuserid != null">
                        and t.areaid in (select userid from useraccount uo where
                        uo.userid=${vo.parentuserid} or uo.parentuserid=${vo.parentuserid})
                    </if>
                </where>
                group by t.goodsid,t.marketid, t.areaid, t.areatype,t.tradedate, t.areaaccountid, t.tradefeetype
                union
                select t.goodsid,
                t.marketid,t.areaid,
                t.tradefeetype,
                case
                when substr(to_char(t.tradefeetype), 0,1) = '2' then
                '会员部分'
                else
                '平台部分'
                end tradefeetypedesc,
                1 searchtype,
                t.areatype,
                t.areaaccountid,
                t.tradedate,
                sum(t.totalcount) totalcount,
                sum(t.totalfee) totalfee,
                sum(t.divideamount) divideamount
                from reckon_daydividecountdetail t
                <where>
                    and t.tradefeetype in (101,102,201,202,301,302,110,108,109,103,104,107,211,212,311,312)
                    <if test="vo.tradedate != null and vo.tradedate != '' ">
                        and t.tradedate = #{vo.tradedate,jdbcType=CHAR}
                    </if>
                    <if test="vo.areaid != null">
                        <choose>
                            <when test="vo.ismarketing!=null">
                                and t.areaid in (
                                select userid from useraccount ua where ua.parentuserid=#{areaid}
                                )
                            </when>
                            <otherwise>
                                and t.areaid = #{vo.areaid}
                            </otherwise>
                        </choose>
                    </if>
                    <if test="vo.parentuserid != null">
                        and t.areaid in (select userid from useraccount uo where
                        uo.userid=${vo.parentuserid} or uo.parentuserid=${vo.parentuserid})
                    </if>
                </where>
                group by t.goodsid, t.marketid,t.areaid, t.areatype,t.tradedate, t.areaaccountid, t.tradefeetype
                ) fee
                left join market m on fee.marketid = m.marketid
                left join goods g on fee.goodsid = g.goodsid
                left join warehouseinfo w on fee.goodsid = w.autoid
                left join (select distinct t.tradefeeid, t.divideusedfeeid from dividefeeconfig t) dfc on
                fee.tradefeetype = dfc.divideusedfeeid
                left join enumdicitem ed on ed.enumdiccode='feeKind' and ed.enumitemname = dfc.tradefeeid
            </otherwise>
        </choose>
    </select>

    <!-- 周、月、季、年报表 -->
    <select id="queryReportDivide" parameterType="com.muchinfo.mtp3pojos.vo.reportform.ShareAmountQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.ShareAmountQueryResult">
        <choose>
            <when test="vo.searchtype!=null and vo.searchtype==3">
                select t.cycletype,t.cycletime, t.areaid, t.goodsid, t.areatype,
                t.tradefeetype, t.areaaccountid,t.totalcount, t.totalfee,t.divideamount,
                m.tradeproperty,g.goodscode,g.goodsname
                from report_dividecountdetail t
                left join (
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from goods t
                union
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from presale_listinginfo
                t
                union
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from auction_orderinfo t
                union
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from
                his_presale_listinginfo t where t.isvaliddata = 1
                union
                select t.goodsid,t.goodscode,t.goodsname,t.marketid from
                his_auction_orderinfo t where t.isvaliddata = 1
                union
                select t.goodsid, t.goodscode,t.goodscode goodsname,t.marketid from his_tender_tenderinfo t where
                t.isvaliddata = 1
                union
                select t.goodsid, t.goodscode, t.goodscode goodsname, t.marketid from tender_tenderinfo t
                union
                select m.marketid goodsid, to_char(m.marketid) goodscode,
                m.marketname goodsname, m.marketid from market m where m.trademode in (17,47)
                ) g on t.goodsid = g.goodsid
                left join market m on g.marketid = m.marketid
                <where>
                    <if test="vo.cycletype !=null and vo.cycletype != ''">
                        and t.cycletype=#{vo.cycletype}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != '' and vo.cycletype != 4">
                        and t.cycletime = #{vo.cycletime}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != '' and vo.cycletype == 4">
                        and t.cycletime= (select to_char(trunc(to_date(#{vo.cycletime},
                        'YYYYMMDD'),'iw')+3,'yyyy') || to_char(to_date(#{vo.cycletime},
                        'YYYYMMDD'), 'iw') from dual)
                    </if>
                    <if test="vo.trade  date !=null and vo.tradedate != ''">
                        and t.cycletime = #{vo.tradedate}
                    </if>
                    <if test="vo.areaid != null">
                        <choose>
                            <when test="vo.ismarketing!=null">
                                and t.areaid in (
                                select userid from useraccount ua where ua.parentuserid=#{vo.areaid}
                                )
                            </when>
                            <otherwise>
                                and t.areaid = #{vo.areaid}
                            </otherwise>
                        </choose>
                    </if>
                    <if test="vo.parentuserid != null">
                        and t.areaid like concat('#{vo.parentuserid}', '%')
                    </if>
                </where>
                order by t.updatetime
            </when>
            <otherwise>
                with fee as (
                select t.cycletype,t.cycletime, t.areaid,
                t.goodsid,t.areatype, t.tradefeetype,
                t.areaaccountid,2 searchtype, t.totalcount,t.totalfee,
                nvl(g.goodscode || '(' || g.goodsname || ')', '--') goodsname ,
                g.marketid as marketname,
                null tradeproperty,t.divideamount
                from report_dividecountdetail t
                <!-- left join warehouseinfo w on t.goodsid = w.autoid -->
                left join goods g on t.goodsid = g.goodsid
                <where>
                    and t.tradefeetype in (105,106,205, 206, 305, 306)
                    <if test="vo.tradedate != null and vo.tradedate != '' ">
                        and t.cycletime = #{vo.tradedate}
                    </if>
                    <if test="vo.areaid != null">
                        <choose>
                            <when test="vo.ismarketing!=null">
                                and t.areaid in (
                                select userid from useraccount ua where ua.parentuserid=#{vo.areaid}
                                )
                            </when>
                            <otherwise>
                                and t.areaid = #{vo.areaid}
                            </otherwise>
                        </choose>
                    </if>
                    <if test="vo.parentuserid != null">
                        and t.areaid in (select userid from useraccount uo where
                        uo.userid=${vo.parentuserid} or uo.parentuserid=${vo.parentuserid})
                    </if>
                    <if test="vo.cycletype !=null and vo.cycletype != ''">
                        and t.cycletype=#{vo.cycletype}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != '' and vo.cycletype != 4">
                        and t.cycletime = #{vo.cycletime}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != '' and vo.cycletype == 4">
                        and t.cycletime= (select to_char(trunc(to_date(#{vo.cycletime},
                        'YYYYMMDD'),'iw')+3,'yyyy') || to_char(to_date(#{vo.cycletime},
                        'YYYYMMDD'), 'iw') from dual)
                    </if>
                </where>
                union all
                select t.cycletype,t.cycletime, t.areaid,
                t.goodsid as goodsid,t.areatype, t.tradefeetype,
                t.areaaccountid,1 searchtype, t.totalcount,t.totalfee,
                nvl(g.goodscode || '(' || g.goodsname || ')', '--') goodsname ,
                g.marketid as marketname,
                m.tradeproperty, t.divideamount
                from report_dividecountdetail t
                left join (select t.goodsid, t.goodscode, t.goodsname, t.marketid from goods t
                union
                select t.goodsid, t.goodscode, t.goodsname, t.marketid from presale_listinginfo t
                union
                select t.goodsid, t.goodscode, t.goodsname, t.marketid from auction_orderinfo t
                union
                select t.goodsid, t.goodscode, t.goodsname, t.marketid from his_presale_listinginfo t where
                t.isvaliddata = 1
                union
                select t.goodsid, t.goodscode, t.goodsname, t.marketid from his_auction_orderinfo t where t.isvaliddata
                = 1
                union
                select t.goodsid, t.goodscode, t.goodscode goodsname, t.marketid from his_tender_tenderinfo t where
                t.isvaliddata = 1
                union
                select t.goodsid, t.goodscode, t.goodscode goodsname, t.marketid from tender_tenderinfo t
                union
                select m.marketid goodsid, to_char(m.marketid) goodscode, m.marketname goodsname,
                m.marketid from market m where m.trademode in (17,47)) g on t.goodsid = g.goodsid
                left join market m on g.marketid = m.marketid
                <where>
                    and t.tradefeetype in (101,102,201,202,301,302,110,108,109,103,104,107,211,212,311,312)
                    <if test="vo.tradedate != null and vo.tradedate != '' ">
                        and t.cycletime = #{vo.tradedate}
                    </if>
                    <if test="vo.areaid != null">
                        <choose>
                            <when test="vo.ismarketing!=null">
                                and t.areaid in (
                                select userid from useraccount ua where ua.parentuserid=#{vo.areaid}
                                )
                            </when>
                            <otherwise>
                                and t.areaid = #{vo.areaid}
                            </otherwise>
                        </choose>
                    </if>
                    <if test="vo.parentuserid != null">
                        and t.areaid in (select userid from useraccount uo where
                        uo.userid=${vo.parentuserid} or uo.parentuserid=${vo.parentuserid})
                    </if>
                    <if test="vo.cycletype !=null and vo.cycletype != ''">
                        and t.cycletype=#{vo.cycletype}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != '' and vo.cycletype != 4">
                        and t.cycletime = #{vo.cycletime}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != '' and vo.cycletype == 4">
                        and t.cycletime= (select to_char(trunc(to_date(#{vo.cycletime},
                        'YYYYMMDD'),'iw')+3,'yyyy') || to_char(to_date(#{vo.cycletime},
                        'YYYYMMDD'), 'iw') from dual)
                    </if>
                </where>
                )
                select f.*,u.accountname areaname, g.goodscode,
                case
                when substr(to_char(f.tradefeetype), 0, 1) = '2'
                then ed.enumdicname || '/' || '会员部分'
                else ed.enumdicname || '/' || '平台部分'
                end dividefeename
                from (select t.cycletype,t.cycletime, t.areaid,t.goodsid,
                t.areatype,t.tradefeetype,t.areaaccountid,t.searchtype,
                t.goodsname,t.tradeproperty,
                sum(t.totalcount) totalcount,
                sum(t.totalfee) totalfee,
                sum(t.divideamount) divideamount from fee t
                group by t.cycletype,t.cycletime,t.areaid,t.goodsid,t.areatype,
                t.tradefeetype, t.areaaccountid,t.goodsname,t.tradeproperty,t.searchtype) f
                left join useraccount u on u.userid = f.areaid
                left join goods g on f.goodsid = g.goodsid
                left join market m on g.marketid = m.marketid
                left join warehouseinfo w on f.goodsid = w.autoid
                left join (select distinct t.tradefeeid, t.divideusedfeeid from dividefeeconfig t) dfc on f.tradefeetype
                = dfc.divideusedfeeid
                left join enumdicitem ed on ed.enumdiccode = 'feeKind' and ed.enumitemname = dfc.tradefeeid
                order by f.areatype,f.goodsid,f.tradefeetype
            </otherwise>
        </choose>
    </select>

    <select id="queryTrade" resultType="com.muchinfo.mtp3pojos.vo.reportform.TradeQueryResult">
        <choose>
            <when test="vo.cycletype==0">
                SELECT
                T.RECKONDATE,
                g.goodsname,
                g.goodscode,
                m.marketname,
                SUM(T.TRADEQTY) TRADEQTY,
                SUM(T.OPENQTY) OPENQTY,
                SUM(T.CLOSEQTY) CLOSEQTY,
                SUM(T.TRADEAMOUNT) TRADEAMOUNT,
                SUM(T.CLOSEPL) CLOSEPL,
                SUM(T.OPENCHARGE) OPENCHARGE,
                SUM(T.CLOSECHARGE) CLOSECHARGE
                FROM RECKON_DAYACCOUNTTRADE T
                inner join taaccount ta on t.accountid = ta.accountid
                inner join useraccount ua on ta.userid in ua.userid
                inner join goods g on g.goodsid = T.goodsid
                inner join market m on m.marketid = T.marketid
                <where>
                    <if test="vo.userid != null and vo.userid != ''">
                        and ua.memberuserid = #{vo.userid}
                    </if>
                    <if test="vo.cycletime != null and vo.cycletime !=''">
                        and T.reckondate=#{vo.cycletime}
                    </if>
                </where>
                group by T.RECKONDATE,g.goodsname, m.marketname
            </when>
            <otherwise>
                SELECT
                T.CYCLETIME as reckondate,
                T.CYCLETYPE,
                g.goodsname,
                m.marketname,
                SUM(T.TRADEQTY) TRADEQTY,
                SUM(T.OPENQTY) OPENQTY,
                SUM(T.CLOSEQTY) CLOSEQTY,
                SUM(T.TRADEAMOUNT) TRADEAMOUNT,
                SUM(T.CLOSEPL) CLOSEPL,
                SUM(T.OPENCHARGE) OPENCHARGE,
                SUM(T.CLOSECHARGE) CLOSECHARGE
                FROM REPORT_DAYACCOUNTTRADE T
                inner join taaccount ta on t.accountid = ta.accountid
                inner join useraccount ua on ta.userid in ua.userid
                inner join goods g on g.goodsid = T.goodsid
                inner join market m on m.marketid = T.marketid
                <where>
                    <if test="vo.userid != null and vo.userid != ''">
                        and ua.memberuserid = #{vo.userid}
                    </if>
                    <if test="vo.cycletype !=null and vo.cycletype != ''">
                        and T.CYCLETYPE=#{vo.cycletype}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != ''">
                        and T.CYCLETIME = #{vo.cycletime}
                    </if>
                </where>
                group by T.CYCLETIME, t.CYCLETYPE,g.goodsname, m.marketname
            </otherwise>
        </choose>
    </select>

    <select id="queryOrgReckonSum" parameterType="com.muchinfo.mtp3pojos.vo.reportform.OrgReportQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.OrgReportQueryResult">
        select
        t.userid as "userId",
        t.reckondate as "cycleTime",
        ua.userid || '/' || ua.accountname as "accountName",
        <if test="vo.type == 1">
            <!-- 包含子机构 -->
            t.investortotalnum2 as "investorTotalNum",
            t.investortrialnum2 as "investorTrialNum",
            t.investorretrialnum2 as "investorRetrialNum",
            t.investorsignqty2 as "investorSignQty",
            t.investorinamount2 as "investorInAmount",
            t.investoroutamount2 as "investorOutAmount",
            t.investoramount2 as "investorAmount",
            t.investorclosepl2 as "investorClosePl",
            t.investorreckonpl2 as "investorReckonPl",
            t.investorbuytradeqty2 as "investorBuyTradeQty",
            t.investorselltradeqty2 as "investorSellTradeQty",
            t.investorbuyholdqty2 as "investorBuyHoldQty",
            t.investorsellholdqty2 as "investorSellHoldQty",
            t.investortradefee2 as "investorTradeFee",
            t.investordeliveryfee2 as "investorDeliveryFee",
            t.investorholdfee2 as "investorHoldFee",
            t.investorbuydeliveryamount2 as "investorBuyDeliveryAmount",
            t.investorselldeliveryamount2 as "investorSellDeliveryAmount",
            t.investorusedmargin2 as "investorusedmargin"
        </if>
        <if test="vo.type != 1">
            t.investortotalnum as "investorTotalNum",
            t.investortrialnum as "investorTrialNum",
            t.investorretrialnum as "investorRetrialNum",
            t.investorsignqty as "investorSignQty",
            t.investorinamount as "investorInAmount",
            t.investoroutamount as "investorOutAmount",
            t.investoramount as "investorAmount",
            t.investorclosepl as "investorClosePl",
            t.investorreckonpl as "investorReckonPl",
            t.investorbuytradeqty as "investorBuyTradeQty",
            t.investorselltradeqty as "investorSellTradeQty",
            t.investorbuyholdqty as "investorBuyHoldQty",
            t.investorsellholdqty as "investorSellHoldQty",
            t.investortradefee as "investorTradeFee",
            t.investordeliveryfee as "investorDeliveryFee",
            t.investorholdfee as "investorHoldFee",
            t.investorbuydeliveryamount as "investorBuyDeliveryAmount",
            t.investorselldeliveryamount as "investorSellDeliveryAmount",
            t.investorusedmargin as "investorusedmargin"
        </if>
        from reckon_daymember t
        left join useraccount ua on t.userid=ua.userid
        <where>
            <if test="vo.reckondate != null and vo.reckondate != '' and vo.reckondateend != null and vo.reckondateend != ''">
                and (t.reckondate >= #{vo.reckondate,jdbcType=CHAR} and t.reckondate &lt;= #{vo.reckondateend,jdbcType=CHAR})
            </if>
            <if test="vo.accountid !=null and vo.accountid != '' ">
                and t.userid = #{vo.accountid}
            </if>
            <if test="vo.userid != null">
                <choose>
                    <when test="vo.changeflag!=null and vo.changeflag==3">
                        and t.userid in (
                        select userid from useraccount u1 where u1.parentuserid=#{vo.userid} or u1.userid=#{vo.userid}
                        )
                    </when>
                    <otherwise>
                        and t.userid = #{vo.userid}
                    </otherwise>
                </choose>
            </if>
        </where>
    </select>

    <select id="queryOrgReportSum" parameterType="com.muchinfo.mtp3pojos.vo.reportform.OrgReportQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.OrgReportQueryResult">
        select
        t.userid as "userId",
        t.cycletime as "cycleTime",
        ua.userid || '/' || ua.accountname as "accountName",
        <if test="vo.type == 1">
            <!-- 包含子机构 -->
            sum(nvl(t.investorinamount2, 0)) as "investorInAmount",
            sum(nvl(t.investoroutamount2, 0)) as "investorOutAmount",
            sum(nvl(t.investorclosepl2, 0)) as "investorClosePl",
            sum(nvl(t.investorreckonpl2, 0)) as "investorReckonPl",
            sum(nvl(t.investorbuytradeqty2, 0)) as "investorBuyTradeQty",
            sum(nvl(t.investorselltradeqty2, 0)) as "investorSellTradeQty",
            sum(nvl(t.investortradefee2, 0)) as "investorTradeFee",
            sum(nvl(t.investordeliveryfee2, 0)) as "investorDeliveryFee",
            sum(nvl(t.investorholdfee2, 0)) as "investorHoldFee",
            sum(nvl(t.investorbuydeliveryamount2, 0)) as "investorBuyDeliveryAmount",
            sum(nvl(t.investorusedmargin2, 0)) as "investorusedmargin",
            sum(nvl(t.investorbuyholdqty2, 0)) as "investorBuyHoldQty",
            sum(nvl(t.investorsellholdqty2, 0)) as "investorSellHoldQty",
            sum(nvl(t.investorsignqty2, 0)) as "investorSignQty",
            sum(nvl(t.investortotalnum2, 0)) as "investorTotalNum",
            sum(nvl(t.investortrialnum2, 0)) as "investorTrialNum",
            sum(nvl(t.investorretrialnum2, 0)) as "investorRetrialNum",
            sum(nvl(t.investoramount2, 0)) as "investorAmount"
        </if>
        <if test="vo.type != 1">
            sum(nvl(t.investorinamount, 0)) as "investorInAmount",
            sum(nvl(t.investoroutamount, 0)) as "investorOutAmount",
            sum(nvl(t.investorclosepl, 0)) as "investorClosePl",
            sum(nvl(t.investorreckonpl, 0)) as "investorReckonPl",
            sum(nvl(t.investorbuytradeqty, 0)) as "investorBuyTradeQty",
            sum(nvl(t.investorselltradeqty, 0)) as "investorSellTradeQty",
            sum(nvl(t.investortradefee, 0)) as "investorTradeFee",
            sum(nvl(t.investordeliveryfee, 0)) as "investorDeliveryFee",
            sum(nvl(t.investorholdfee, 0)) as "investorHoldFee",
            sum(nvl(t.investorbuydeliveryamount, 0)) as "investorBuyDeliveryAmount",
            sum(nvl(t.investorusedmargin, 0)) as "investorusedmargin",
            sum(nvl(t.investorbuyholdqty, 0)) as "investorBuyHoldQty",
            sum(nvl(t.investorsellholdqty, 0)) as "investorSellHoldQty",
            sum(nvl(t.investorsignqty, 0)) as "investorSignQty",
            sum(nvl(t.investortotalnum, 0)) as "investorTotalNum",
            sum(nvl(t.investortrialnum, 0)) as "investorTrialNum",
            sum(nvl(t.investorretrialnum, 0)) as "investorRetrialNum",
            sum(nvl(t.investoramount, 0)) as "investorAmount"
        </if>
        from report_daymember t
        left join useraccount ua on t.userid=ua.userid
        <where>
            <if test="vo.cycletype !=null and vo.cycletype != ''">
                and t.cycletype=#{vo.cycletype}
            </if>
            <if test="vo.reckondate !=null and vo.reckondate != '' and vo.reckondateend != null and vo.reckondateend != '' and vo.cycletype != 4">
                and (t.cycletime >= #{vo.reckondate} and t.cycletime &lt;= #{vo.reckondateend})
            </if>
            <if test="vo.reckondate !=null and vo.reckondate != '' and vo.reckondateend != null and vo.reckondateend != '' 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)
                and t.cycletime &lt;= (select to_char(trunc(to_date(#{vo.reckondateend}, 'YYYYMMDD'),'iw')+3,'yyyy') ||
                to_char(to_date(#{vo.reckondateend}, 'YYYYMMDD'), 'iw') from dual))
            </if>
            <if test="vo.accountid !=null and vo.accountid != '' ">
                and t.userid = #{vo.accountid}
            </if>
            <if test="vo.userid != null">
                <choose>
                    <when test="vo.changeflag!=null and vo.changeflag==3">
                        and t.userid in (
                        select userid from useraccount u1 where u1.parentuserid=#{vo.userid} or u1.userid=#{vo.userid}
                        )
                    </when>
                    <otherwise>
                        and t.userid = #{vo.userid}
                    </otherwise>
                </choose>
            </if>
        </where>
        GROUP BY t.userid, t.cycletime, ua.accountname, ua.userid
    </select>

    <select id="queryReckonDayTa" parameterType="com.muchinfo.mtp3pojos.vo.reportform.InvestorReportQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.InvestorReportQueryResult">
        select
        ra.reckondate,
        0 as cycletype,
        ra.userid,
        ra.accountid,
        ra.userid || '/' || ua.accountname as accountname,
        listagg(la.loginid, ',') within group(order by ra.userid) loginids,
        ra.balance,
        ra.currentbalance,
        ra.inamount,
        ra.outamount,
        ra.outamountfreeze,
        ra.usedmargin,
        ra.freezemargin,
        ra.otherfreezemargin,
        ra.paycharge,
        ra.tradecharge,
        ra.closepl,
        ra.reckonpl,
        ra.tradeqty,
        ra.tradeamount,
        nvl(ra.defercharge, 0) + nvl(ra.interestcharge, 0) interestcharge,
        ra.avaiablemoney,
        ra.avaiableoutmoney,
        ra.floatpl,
        ra.floatnetvalue,
        ra.riskrate,
        ra.otherincome,
        u.memberuserid || '/' || t.accountname memberaccountname
        from Reckon_DayTAAccount ra
        left join useraccount ua on ra.userid=ua.userid
        left join useraccount us on ua.parentuserid=us.userid
        left join loginaccount la on ra.userid = la.userid
        left join taaccount ta on ta.accountid = ra.accountid
        left join useraccount u on ta.userid = u.userid
        left join useraccount t on u.memberuserid = t.userid
        <where>
            <if test="vo.reckondate != null and vo.reckondate != '' and vo.reckondateend != null and vo.reckondateend != ''">
                and (ra.reckondate >= #{vo.reckondate,jdbcType=CHAR} and ra.reckondate &lt;=
                #{vo.reckondateend,jdbcType=CHAR})
            </if>
            <if test="vo.flag != null and vo.flag == 0">
                and ua.usertype=5
            </if>
            <if test="vo.memberid !=null and vo.memberid != ''">
                and ua.subarealevelpath like '%'||#{vo.memberid}||'%'
            </if>
            <if test="vo.accountid  !=null and vo.accountid  != '' ">
                and ra.userid = #{vo.accountid}
            </if>
            <if test="vo.subareaid !=null and vo.subareaid  != ''">
                and us.subarealevelpath like
                '%'||#{vo.subareaid}||'%'
            </if>
            <if test="vo.brokerid !=null and vo.brokerid  != ''">
                and ua.broker = #{vo.brokerid }
            </if>
            <if test="vo.inamount != null and vo.inamount !='' ">
                and ra.inamount >= #{vo.inamount}
            </if>
            <if test="vo.outamount != null and vo.outamount !='' ">
                and ra.outamount >= #{vo.outamount}
            </if>
            <if test="vo.accountstatus != null and vo.accountstatus == 1">
                and ua.accountstatus = 4
            </if>
            <if test="vo.accountstatus != null and vo.accountstatus == 2">
                and ua.accountstatus in (6,7)
            </if>
            <if test="vo.roles != null and vo.roles !='' ">
                and ua.userid in (select userid from arearole where roletype in (${vo.roles}))
            </if>
            <if test="vo.parentuserid != null">
                <choose>
                    <when test="vo.changeflag!=null and vo.changeflag==3">
                        and ua.memberuserid in (
                        select userid from useraccount u1 where u1.parentuserid=#{vo.parentuserid} or
                        u1.userid=#{vo.parentuserid}
                        )
                    </when>
                    <otherwise>
                        and ua.userid in (select userid from useraccount uo
                        start with uo.userid = #{vo.parentuserid}
                        connect by prior uo.userid =uo.parentuserid and uo.usertype!=2)
                    </otherwise>
                </choose>
            </if>
            <if test="vo.usertype != null">
                and ua.usertype=#{vo.usertype}
            </if>
        </where>
        group by ra.reckondate,
        ra.userid,
        u.memberuserid,
        ra.accountid,
        ua.accountname,
        ra.balance,
        ra.currentbalance,
        ra.inamount,
        ra.outamount,
        ra.outamountfreeze,
        ra.usedmargin,
        ra.freezemargin,
        ra.otherfreezemargin,
        ra.paycharge,
        ra.tradecharge,
        ra.closepl,
        ra.reckonpl,
        ra.tradeqty,
        ra.tradeamount,
        ra.defercharge,
        ra.interestcharge,
        ra.avaiablemoney,
        ra.avaiableoutmoney,
        ra.floatpl,
        ra.floatnetvalue,
        ra.riskrate,
        ra.otherincome,
        t.accountname
        <choose>
            <when test="vo.sortfield != null and vo.sortfield == 1">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.balance asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.currentbalance asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.inamount asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.outamount asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.closepl asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.floatpl asc
                    </when>
                    <otherwise>
                        order by ra.floatpl 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 ra.reckonpl asc
                    </when>
                    <otherwise>
                        order by ra.reckonpl desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield ==8">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.paycharge asc
                    </when>
                    <otherwise>
                        order by ra.paycharge desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 9">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by (ra.defercharge + ra.interestcharge) asc
                    </when>
                    <otherwise>
                        order by (ra.defercharge + ra.interestcharge) desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 10">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.usedmargin asc
                    </when>
                    <otherwise>
                        order by ra.usedmargin desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 11">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.freezemargin asc
                    </when>
                    <otherwise>
                        order by ra.freezemargin desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 12">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.otherfreezemargin asc
                    </when>
                    <otherwise>
                        order by ra.otherfreezemargin desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 13">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.outamountfreeze asc
                    </when>
                    <otherwise>
                        order by ra.outamountfreeze desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 14">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.avaiablemoney asc
                    </when>
                    <otherwise>
                        order by ra.avaiablemoney desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 15">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.avaiableoutmoney asc
                    </when>
                    <otherwise>
                        order by ra.avaiableoutmoney desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 16">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.riskrate asc
                    </when>
                    <otherwise>
                        order by ra.riskrate desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 17">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.reckondate asc
                    </when>
                    <otherwise>
                        order by ra.reckondate desc
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                order by ra.reckondate
            </otherwise>
        </choose>
    </select>

    <select id="queryReportTa" parameterType="com.muchinfo.mtp3pojos.vo.reportform.InvestorReportQueryParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.InvestorReportQueryResult">
        select
        ra.cycletype,
        ra.cycletime as reckondate,
        ra.userid,
        ra.accountid,
        ra.userid || '/' || ua.accountname as accountname,
        listagg(la.loginid, ',') within group(order by ra.userid) loginids,
        ra.balance,
        ra.currentbalance,
        ra.inamount,
        ra.outamount,
        ra.outamountfreeze,
        ra.usedmargin,
        ra.freezemargin,
        ra.otherfreezemargin,
        ra.paycharge,
        ra.tradecharge,
        ra.closepl,
        ra.reckonpl,
        ra.tradeqty,
        ra.tradeamount,
        nvl(ra.defercharge, 0) + nvl(ra.interestcharge, 0) interestcharge,
        ra.avaiablemoney,
        ra.avaiableoutmoney,
        ra.floatpl,
        ra.floatnetvalue,
        ra.riskrate,
        ra.otherincome,
        u.memberuserid || '/' || t.accountname memberaccountname
        from REPORT_TAACCOUNT ra
        left join useraccount ua on ra.userid=ua.userid
        left join useraccount us on ua.parentuserid=us.userid
        left join loginaccount la on ra.userid = la.userid
        left join taaccount ta on ta.accountid = ra.accountid
        left join useraccount u on ta.userid = u.userid
        left join useraccount t on u.memberuserid = t.userid
        <where>
            <if test="vo.flag != null and vo.flag == 0">
                and ua.usertype=5
            </if>
            <if test="vo.usertype != null">
                and ua.usertype=#{vo.usertype}
            </if>
            <if test="vo.cycletype !=null and vo.cycletype != ''">
                and ra.cycletype=#{vo.cycletype}
            </if>
            <if test="vo.reckondate !=null and vo.reckondate != '' and vo.reckondateend != null and vo.reckondateend != '' and vo.cycletype != 4">
                and (ra.cycletime >= #{vo.reckondate} and ra.cycletime &lt;= #{vo.reckondateend})
            </if>
            <if test="vo.reckondate !=null and vo.reckondate != '' and vo.reckondateend != null and vo.reckondateend != '' and vo.cycletype == 4">
                and (ra.cycletime >= (select to_char(trunc(to_date(#{vo.reckondate}, 'YYYYMMDD'),'iw')+3,'yyyy') ||
                to_char(to_date(#{vo.reckondate}, 'YYYYMMDD'), 'iw') from dual)
                and ra.cycletime &lt;= (select to_char(trunc(to_date(#{vo.reckondateend}, 'YYYYMMDD'),'iw')+3,'yyyy') ||
                to_char(to_date(#{vo.reckondateend}, 'YYYYMMDD'), 'iw') from dual))
            </if>
            <if test="vo.accountid !=null and vo.accountid != '' ">
                and ra.userid = #{vo.accountid}
            </if>
            <if test="vo.memberid !=null and vo.memberid != ''">
                and ua.subarealevelpath like '%'||#{vo.memberid}||'%'
            </if>
            <if test="vo.brokerid !=null and vo.brokerid != ''">
                and ua.broker = #{vo.brokerid}
            </if>
            <if test="vo.accountstatus != null and vo.accountstatus == 1">
                and ua.accountstatus = 4
            </if>
            <if test="vo.accountstatus != null and vo.accountstatus == 2">
                and ua.accountstatus in (6,7)
            </if>
            <if test="vo.roles != null and vo.roles !='' ">
                and ua.userid in (select userid from arearole where roletype in (${vo.roles}))
            </if>
            <if test="vo.subareaid !=null and vo.subareaid  != ''">
                and us.subarealevelpath like
                '%'||#{vo.subareaid}||'%'
            </if>
            <if test="vo.parentuserid != null">
                <choose>
                    <when test="vo.changeflag!=null and vo.changeflag==3">
                        and ua.memberuserid in (
                        select userid from useraccount u1 where u1.parentuserid=#{vo.parentuserid} or
                        u1.userid=#{vo.parentuserid}
                        )
                    </when>
                    <otherwise>
                        and ua.userid in (select userid from useraccount uo
                        start with uo.userid = #{vo.parentuserid}
                        connect by prior uo.userid =uo.parentuserid and uo.usertype!=2)
                    </otherwise>
                </choose>
            </if>
            <if test="vo.inamount != null and vo.inamount !='' ">
                and ra.inamount >= #{vo.inamount, jdbcType=DECIMAL}
            </if>
            <if test="vo.outamount != null and vo.outamount !='' ">
                and ra.outamount >= #{vo.outamount, jdbcType=DECIMAL}
            </if>
        </where>
        group by ra.cycletype,
        ra.cycletime,
        ra.userid,
        ra.accountid,
        ua.accountname,
        ra.balance,
        ra.currentbalance,
        ra.inamount,
        ra.outamount,
        ra.outamountfreeze,
        ra.usedmargin,
        ra.freezemargin,
        ra.otherfreezemargin,
        ra.paycharge,
        ra.tradecharge,
        ra.closepl,
        ra.reckonpl,
        ra.tradeqty,
        ra.tradeamount,
        ra.defercharge,
        ra.interestcharge,
        ra.avaiablemoney,
        ra.avaiableoutmoney,
        ra.floatpl,
        ra.floatnetvalue,
        ra.riskrate,
        ra.otherincome,
        u.memberuserid,
        t.accountname
        <choose>
            <when test="vo.sortfield != null and vo.sortfield == 1">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.balance asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.currentbalance asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.inamount asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.outamount asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.closepl asc
                    </when>
                    <otherwise>
                        order by ra.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 ra.floatpl asc
                    </when>
                    <otherwise>
                        order by ra.floatpl 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 ra.reckonpl asc
                    </when>
                    <otherwise>
                        order by ra.reckonpl desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield ==8">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.paycharge asc
                    </when>
                    <otherwise>
                        order by ra.paycharge desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 9">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by (ra.defercharge + ra.interestcharge) asc
                    </when>
                    <otherwise>
                        order by (ra.defercharge + ra.interestcharge) desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 10">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.usedmargin asc
                    </when>
                    <otherwise>
                        order by ra.usedmargin desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 11">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.freezemargin asc
                    </when>
                    <otherwise>
                        order by ra.freezemargin desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 12">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.otherfreezemargin asc
                    </when>
                    <otherwise>
                        order by ra.otherfreezemargin desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 13">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.outamountfreeze asc
                    </when>
                    <otherwise>
                        order by ra.outamountfreeze desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 14">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.avaiablemoney asc
                    </when>
                    <otherwise>
                        order by ra.avaiablemoney desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 15">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.avaiableoutmoney asc
                    </when>
                    <otherwise>
                        order by ra.avaiableoutmoney desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 16">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.riskrate asc
                    </when>
                    <otherwise>
                        order by ra.riskrate desc
                    </otherwise>
                </choose>
            </when>
            <when test="vo.sortfield != null and vo.sortfield == 17">
                <choose>
                    <when test="vo.sorttype != null and vo.sorttype == 0 ">
                        order by ra.cycletime asc
                    </when>
                    <otherwise>
                        order by ra.cycletime desc
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                order by ra.cycletime
            </otherwise>
        </choose>
    </select>

    <!-- 仓单汇总报表 -->
    <select id="wrposition"  parameterType="com.muchinfo.mtp3pojos.vo.reportform.TaAccountReportParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.WrPositionResult">
        <choose>
            <when test="vo.cycletype==0">
                select
                dwrs.totalqty "totalqty", dwrs.increaseqty "increaseqty", dwrs.decreaseqty "decreaseqty",
                dwrs.freezeqty "freezeqty", dwrs.deliverygoodsid "deliverygoodsid", dwrs.accountid "accountid",
                (dwrs.totalqty - dwrs.freezeqty) "usedqty",
                (dwrs.totalqty - dwrs.increaseqty + dwrs.decreaseqty) "startqty",
                gd.deliverygoodsname "deliverygoodsname", ws.wrstandardname "wrstandardname"
                from Reckon_DayWRStandard dwrs
                left join wrstandard ws on dwrs.wrstandardid = ws.wrstandardid
                left join DeliveryGoods gd on gd.deliverygoodsid = dwrs.deliverygoodsid
                <where>
                    <if test="vo.cycletime != null and vo.cycletime !=''">
                        and dwrs.ReckonDate=#{vo.cycletime}
                    </if>
                    <if test="vo.accountid != null">
                        and dwrs.accountid=#{vo.accountid}
                    </if>
                </where>
            </when>
            <otherwise>
                select
                dwrs.totalqty "totalqty", dwrs.increaseqty "increaseqty", dwrs.decreaseqty "decreaseqty",
                dwrs.freezeqty "freezeqty", dwrs.deliverygoodsid "deliverygoodsid", dwrs.accountid "accountid",
                (dwrs.totalqty - dwrs.freezeqty) "usedqty",
                (dwrs.totalqty - dwrs.increaseqty + dwrs.decreaseqty) "startqty",
                gd.deliverygoodsname "deliverygoodsname", ws.wrstandardname "wrstandardname"
                from report_daywrstandard dwrs
                left join wrstandard ws on dwrs.wrstandardid = ws.wrstandardid
                left join DeliveryGoods gd on gd.deliverygoodsid = dwrs.deliverygoodsid
                <where>
                    <if test="vo.cycletime != null and vo.cycletime !=''">
                        and dwrs.cycletime=#{vo.cycletime}
                    </if>
                    <if test="vo.cycletype !=null and vo.cycletype != ''">
                        and dwrs.cycletype=#{vo.cycletype}
                    </if>
                    <if test="vo.accountid != null">
                        and dwrs.accountid=#{vo.accountid}
                    </if>
                </where>
            </otherwise>
        </choose>
    </select>

    <select id="tradeposition"  parameterType="com.muchinfo.mtp3pojos.vo.reportform.TaAccountReportParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.HolderTotalResult">
        <choose>
            <when test="vo.cycletype==0">
                select r.accountid, r.goodsid, r.buypositionqty, r.buyholderamount,
                r.buycurpositionqty, r.buycurholderamount, r.buyfrozenqty, r.buyotherfrozenqty,
                r.buyopenreqqty, r.buyreckonpl, r.buyinterest, r.buyusedmargin,
                r.buyopentotalqty, r.buyclosetotalqty, r.buyclosetotalpl, r.sellpositionqty,
                r.sellholderamount, r.sellcurpositionqty, r.sellcurholderamount, r.sellfrozenqty,
                r.sellotherfrozenqty, r.sellopenreqqty, r.sellreckonpl, r.sellinterest,
                r.sellusedmargin, r.sellopentotalqty, r.sellclosetotalqty, r.sellclosetotalpl,
                r.tradeproperty, g.goodscode, g.marketid,g.goodsname
                from RECKON_DAYPOSITION r
                left join goods g on g.goodsid = r.goodsid
                left join market m on g.marketid = m.marketid
                <where>
                    <if test="vo.accountid != null">
                        and r.accountid=#{vo.accountid}
                    </if>
                    <if test="vo.cycletime != null and vo.cycletime !=''">
                        and r.reckondate=#{vo.cycletime}
                    </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>
            </when>
            <otherwise>
                select r.accountid, r.goodsid, r.cycletype,
                r.cycletime as reckondate, r.updatetime, r.buypositionqty, r.buyholderamount,
                r.buycurpositionqty, r.buycurholderamount, r.buyfrozenqty, r.buyotherfrozenqty, r.buyopenreqqty,
                r.buyreckonpl, r.buyinterest, r.buyusedmargin, r.buyopentotalqty, r.buyclosetotalqty, r.buyclosetotalpl,
                r.sellpositionqty, r.sellholderamount, r.sellcurpositionqty, r.sellcurholderamount, r.sellfrozenqty,
                r.sellotherfrozenqty, r.sellopenreqqty, r.sellreckonpl, r.sellinterest, r.sellusedmargin,
                r.sellopentotalqty,
                r.sellclosetotalqty, r.sellclosetotalpl, r.tradeproperty,
                g.goodscode,g.marketid,g.goodsname
                from REPORT_TRADEPOSITION r
                left join goods g on g.goodsid = r.goodsid
                left join market m on g.marketid = m.marketid
                <where>
                    <if test="vo.accountid !=null and vo.accountid != ''">
                        and r.accountid=#{vo.accountid}
                    </if>
                    <if test="vo.cycletype !=null and vo.cycletype != ''">
                        and r.cycletype=#{vo.cycletype}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != ''">
                        and r.cycletime=#{vo.cycletime}
                    </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>
            </otherwise>
        </choose>
    </select>

    <select id="tradequery" parameterType="com.muchinfo.mtp3pojos.vo.reportform.TaAccountReportParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.TradeReportQueryResult">
        <choose>
            <when test="vo.cycletype==0">
                SELECT
                T.RECKONDATE,
                T.ACCOUNTID,
                T.GOODSID,
                T.MARKETID,
                T.TRADEQTY,
                T.OPENQTY,
                T.CLOSEQTY,
                T.TRADEAMOUNT,
                T.CLOSEPL,
                T.OPENCHARGE,
                T.CLOSECHARGE,
                g.goodscode,g.goodsname,m.marketname
                FROM RECKON_DAYACCOUNTTRADE T
                left join goods g on g.goodsid = T.goodsid
                left join market m on T.marketid = m.marketid
                <where>
                    <if test="vo.accountid != null">
                        and T.accountid=#{vo.accountid}
                    </if>
                    <if test="vo.cycletime != null and vo.cycletime !=''">
                        and T.reckondate=#{vo.cycletime}
                    </if>
                </where>
            </when>
            <otherwise>
                SELECT
                T.CYCLETIME as reckondate,
                T.CYCLETYPE,
                T.ACCOUNTID,
                T.GOODSID,
                T.MARKETID,
                T.TRADEQTY,
                T.OPENQTY,
                T.CLOSEQTY,
                T.TRADEAMOUNT,
                T.CLOSEPL,
                T.OPENCHARGE,
                T.CLOSECHARGE,
                g.goodscode,g.goodsname,m.marketname
                FROM REPORT_DAYACCOUNTTRADE T
                left join goods g on g.goodsid = T.goodsid
                left join market m on T.marketid = m.marketid
                <where>
                    <if test="vo.accountid !=null and vo.accountid != ''">
                        and T.ACCOUNTID=#{vo.accountid}
                    </if>
                    <if test="vo.cycletype !=null and vo.cycletype != ''">
                        and T.CYCLETYPE=#{vo.cycletype}
                    </if>
                    <if test="vo.cycletime !=null and vo.cycletime != ''">
                        and T.CYCLETIME = #{vo.cycletime}
                    </if>
                </where>
            </otherwise>
        </choose>
    </select>

    <select id="queryCusBank" parameterType="com.muchinfo.mtp3pojos.vo.reportform.CusBankReportParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.CusBankReportResult">
        select
        t.cusbankid,
        t.tradedate,
        t.inamoutcout, t.totalinamout, t.inamoutnum,
        t.outamountcout, t.totaloutamount, t.outamountnum,
        t.signincount, t.signoutcount, t.opencount, t.totalsignincount,
        t.totalpl, t.totalcharge, t.totalincharge, t.totaloutcharge,
        t.balance,  t.currentbalance,
        t.checkstatus,
        b.currency,
        b.cusbankname
        from Bank_CusBankCheck t
        inner join BANK_CUSBANKINFO b on t.cusbankid=b.cusbankid
        <where>
            <if test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''">
                and to_date(t.tradedate,'yyyy-MM-dd') between to_date(#{vo.startDate},'yyyy-MM-dd')
                and to_date(#{vo.endDate},'yyyy-MM-dd')
            </if>
            <if test="vo.cusbankname != null and vo.cusbankname !=''">
                and (t.cusbankid like '%'||#{vo.cusbankname}||'%'
                or b.cusbankname like '%'||#{vo.cusbankname}||'%'
                )
            </if>
        </where>
        order by t.tradedate desc
    </select>

    <select id="managediv" parameterType="com.muchinfo.mtp3pojos.vo.reportform.CusBankReportParam"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.ManageDivReportResult">
        select
        r.tradedate, r.areaid,r.areatype,
        r.areaaccountid,r.totalcount,r.totalfee,
        r.divideamount,r.accountcurrencyid,
        u.accountname as areaname ,
        bi.bankname
        from reckon_daydividecount r
        left join useraccount u on r.areaid = u.userid
        left join Bank_AccountSign bsa on r.areaaccountid=bsa.accountcode
        left join Bank_BankInfo bi on bsa.bankid=bi.bankid
        <where>
            r.areatype = 1
            <if
                    test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''">
                and to_date(r.tradedate,'yyyy-MM-dd') between
                to_date(#{vo.startDate},'yyyy-MM-dd')
                and to_date(#{vo.endDate},'yyyy-MM-dd')
            </if>
            <if test="vo.cusbankname != null and vo.cusbankname !=''">
                and (bi.bankname like
                '%'||#{vo.cusbankname}||'%')
            </if>
        </where>
        order by r.tradedate desc
    </select>

    <select id="execReportMonthType" parameterType="java.util.HashMap" statementType="CALLABLE">
        <![CDATA[{call PKG_HIS_REPORT_TABLE.PROC_HIS_REPORT_TABLE_MONTH(#{tradedate,mode=IN,jdbcType=DATE})}]]>
    </select>

    <select id="queryByDate" resultType="com.muchinfo.mtp3pojos.vo.reportform.ShareAmountQueryResult">
        select
        r.tradedate as cycletime,
        r.areaid,
        r.areatype,
        r.areaaccountid,
        r.totalcount,
        r.totalfee,
        r.divideamount,
        r.accountcurrencyid,
        u.userid || '/' || u.accountname as areaname
        from reckon_daydividecount r
        left join useraccount u on r.areaid = u.userid
        <where>
            <if test="vo.tradedate != null and vo.tradedate != '' ">
                and r.tradedate= #{vo.tradedate,jdbcType=CHAR}
            </if>
            <if test="vo.areaid != null">
                and r.areaid = #{vo.areaid}
            </if>
            <if test="vo.areaname != null and vo.areaname != ''">
                and r.areaid = #{vo.areaname}
            </if>
            <if test="vo.areatype != null and vo.areatype !=''">
                and r.areatype in (${vo.areatype})
            </if>
            <if test="vo.usertype != null">
                and u.usertype in (${vo.usertype})
            </if>
            <if test="vo.parentuserid != null or vo.parentuserid==1">
                and (r.areaid = #{vo.parentuserid} or u.usertype=2 )
            </if>
            <if test="vo.parentareaid != null">
                and r.areaid in (select userid from useraccount uo start with
                uo.userid=${vo.parentareaid} connect by prior uo.userid
                =uo.parentuserid and uo.usertype=3)
            </if>
            <if test="vo.yxareauserid !=null and vo.yxareauserid != ''">
                and (u.userid = #{vo.yxareauserid} or u.userid in (select userid from useraccount t
                where t.usertype in (2,3) and t.memberuserid in (select userid from useraccount t where t.usertype = 2 and t.parentuserid = #{vo.yxareauserid})))
            </if>
        </where>
        order by r.areaid,r.areatype,r.areaaccountid
    </select>
    <select id="queryByOther" resultType="com.muchinfo.mtp3pojos.vo.reportform.ShareAmountQueryResult">
        select
        r.cycletype,r.cycletime,r.areaid,r.areatype,
        r.areaaccountid,r.totalcount,r.totalfee,
        r.divideamount,
        u.userid || '/' || u.accountname as areaname
        from report_dividecount r
        left join useraccount u on r.areaid = u.userid
        <where>
            <if test="vo.cycletype !=null and vo.cycletype != ''">
                and r.cycletype=#{vo.cycletype}
            </if>
            <if test="vo.tradedate !=null and vo.tradedate != '' and vo.cycletype != 4">
                and r.cycletime = #{vo.tradedate}
            </if>
            <if test="vo.tradedate !=null and vo.tradedate != '' and vo.cycletype == 4">
                and r.cycletime= (select to_char(trunc(to_date(#{vo.tradedate},
                'YYYYMMDD'),'iw')+3,'yyyy') || to_char(to_date(#{vo.tradedate},
                'YYYYMMDD'), 'iw') from dual)
            </if>
            <if test="vo.areaname != null and vo.areaname !=''">
                and r.areaid = #{vo.areaname}
            </if>
            <if test="vo.usertype != null and vo.usertype !=''">
                and u.usertype in (${vo.usertype})
            </if>
            <if test="vo.areatype != null and vo.areatype !=''">
                and r.areatype in (${vo.areatype})
            </if>
            <if test="vo.parentuserid != null or vo.parentuserid==1">
                <choose>
                    <when test="vo.parentuserid==1">
                        and (r.areaid = 1 or u.usertype=2 )
                    </when>
                    <otherwise>
                        and (r.areaid = #{vo.parentuserid} or u.parentuserid=#{vo.parentuserid} )
                    </otherwise>
                </choose>
            </if>
            <if test="vo.yxareauserid !=null and vo.yxareauserid != ''">
                and (u.userid = #{vo.yxareauserid} or u.userid in (select userid from useraccount t
                where t.usertype in (2,3) and t.memberuserid in (select userid from useraccount t where t.usertype = 2 and t.parentuserid = #{vo.yxareauserid})))
            </if>
        </where>
        order by r.areaid,r.areatype,r.areaaccountid
    </select>
    <select id="queryDODPage" resultType="com.muchinfo.mtp3pojos.vo.reportform.ReckonDivideOrderDetailResult">
        select t.tradeid, t.tradefeetype, t.areaid, t.areatype, t.buyorsell,
        t.tradedate, t.accountid,t.areaaccountid,
        t.goodsid, t.feeamount, t.dividetype, t.dividevalue, t.divideamount,
        t.creatdate,m.trademode
        from RECKON_DIVIDEORDERDETAIL t
        left join (
        select t.goodsid,t.goodscode,t.goodsname,t.marketid from goods t
        union
        select t.goodsid,t.goodscode,t.goodsname,t.marketid from presale_listinginfo t
        union
        select t.goodsid,t.goodscode,t.goodsname,t.marketid from auction_orderinfo t
        union
        select t.goodsid,t.goodscode,t.goodsname,t.marketid from his_presale_listinginfo t where t.isvaliddata = 1
        union
        select t.goodsid,t.goodscode,t.goodsname,t.marketid from his_auction_orderinfo t where t.isvaliddata = 1
        union
        select t.goodsid, t.goodscode,t.goodscode goodsname,t.marketid from his_tender_tenderinfo t where t.isvaliddata
        = 1
        union
        select t.goodsid, t.goodscode, t.goodscode goodsname,t.marketid from tender_tenderinfo t
        ) g on t.goodsid = g.goodsid
        left join market m on g.marketid = m.marketid
        <where>
            <if test="vo.areaid != null">
                and t.areaid = #{vo.areaid,jdbcType=DECIMAL}
            </if>
            <if test="vo.areatype != null">
                and t.areatype = #{vo.areatype,jdbcType=DECIMAL}
            </if>
            <if test="vo.areaaccountid != null">
                and t.areaaccountid = #{vo.areaaccountid,jdbcType=DECIMAL}
            </if>
            <if test="vo.tradefeetype != null">
                and t.tradefeetype = #{vo.tradefeetype,jdbcType=DECIMAL}
            </if>
            <if test="vo.goodsid != null and vo.goodsid != ''">
                <choose>
                    <when test="vo.searchtype==1">
                        and t.goodsid = #{vo.goodsid,jdbcType=DECIMAL}
                    </when>
                    <otherwise>
                        and t.goodsid = #{vo.goodsid,jdbcType=DECIMAL}
                    </otherwise>
                </choose>
            </if>
            <if test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''  ">
                and (t.tradedate >= #{vo.startDate} and t.tradedate &lt;= #{vo.endDate})
            </if>
        </where>
        order by t.tradedate
    </select>
    <select id="queryReckonAGDivideDetail"
            resultType="com.muchinfo.mtp3pojos.vo.reportform.ReckonDivideOrderDetailResult">
        <choose>
            <when test="vo.areatype==12">
                select t.tradedate,t.accountid,t.goodsid,t.parentuserid,t.dividetype,
                t.dividevalue,t.divideamount,t.dividemode,t.marketid,t.totalcount,
                ua.userid "relateduserid",
                ua.accountname "accountname",
                <choose>
                    <when test="vo.searchtype==1">
                        t.totalfeeamount feeamount
                    </when>
                    <otherwise>
                        t.feeamount
                    </otherwise>
                </choose>
                from Reckon_SubAGDivideDetail t
                left join taaccount ta on t.accountid = ta.accountid
                left join useraccount ua on ta.userid = ua.userid
                <where>
                    <if test="vo.areaid != null">
                        <![CDATA[and t.parentuserid = #{vo.areaid}]]>
                    </if>
                    <if test="vo.goodsid != null">
                        <choose>
                            <when test="vo.searchtype==1">
                                <!-- and t.marketid = #{goodsid,jdbcType=DECIMAL} -->
                                and t.goodsid = #{goodsid,jdbcType=DECIMAL}
                            </when>
                            <otherwise>
                                and t.goodsid = #{goodsid,jdbcType=DECIMAL}
                            </otherwise>
                        </choose>
                    </if>
                    <if test="vo.tradefeetype != null">
                        <![CDATA[and t.tradefeetype = #{vo.tradefeetype}]]>
                    </if>
                    <if test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''  ">
                        and (t.tradedate >= #{vo.startDate} and t.tradedate &lt;= #{vo.endDate})
                    </if>
                    <if test="vo.areatype != null and vo.areatype != ''  ">
                        and t.areatype = #{vo.areatype}
                    </if>
                </where>
            </when>
            <otherwise>
                select t.*,
                ua.accountname "accountname" from Reckon_AGDivideDetail t
                left join useraccount ua on t.relateduserid = ua.userid
                <where>
                    <if test="vo.memberuserid != null">
                        <![CDATA[and t.memberuserid = #{vo.memberuserid}]]>
                    </if>
                    <if test="vo.parentuserid != null">
                        <![CDATA[and t.parentuserid = #{vo.parentuserid}]]>
                    </if>
                    <if test="vo.areaaccountid != null">
                        <![CDATA[and t.accountid = #{vo.areaaccountid}]]>
                    </if>
                    <if test="vo.areaid != null">
                        <![CDATA[and t.areaid = #{vo.areaid}]]>
                    </if>
                    <if test="vo.goodsid != null">
                        <choose>
                            <when test="vo.searchtype==1">
                                <!-- and t.marketid = #{goodsid,jdbcType=DECIMAL} -->
                                and t.goodsid = #{vo.goodsid,jdbcType=DECIMAL}
                            </when>
                            <otherwise>
                                and t.goodsid = #{vo.goodsid,jdbcType=DECIMAL}
                            </otherwise>
                        </choose>
                    </if>
                    <if test="vo.tradefeetype != null">
                        <![CDATA[and t.tradefeetype = #{vo.tradefeetype}]]>
                    </if>
                    <if test="vo.startDate != null and vo.startDate != '' and vo.endDate != null and vo.endDate != ''  ">
                        and (t.tradedate >= #{vo.startDate} and t.tradedate &lt;= #{vo.endDate})
                    </if>
                    <if test="vo.areatype != null and vo.areatype != ''  ">
                        and t.areatype = #{vo.areatype}
                    </if>
                </where>
            </otherwise>
        </choose>
    </select>
</mapper>
