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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.WskhUserinfo">
        <id column="USERID" property="userid" />
        <result column="USERNAME" property="username" />
        <result column="SEX" property="sex" />
        <result column="CARDTYPE" property="cardtype" />
        <result column="CARDNUM" property="cardnum" />
        <result column="CARDADDRESS" property="cardaddress" />
        <result column="MOBILEPHONE" property="mobilephone" />
        <result column="WECHAT" property="wechat" />
        <result column="EMAIL" property="email" />
        <result column="IPADDRESS" property="ipaddress" />
        <result column="HALFBODYPHOTOURL" property="halfbodyphotourl" />
        <result column="CARDFRONTPHOTOURL" property="cardfrontphotourl" />
        <result column="CARDBACKPHOTOURL" property="cardbackphotourl" />
        <result column="BANKID" property="bankid" />
        <result column="BANKNAME" property="bankname" />
        <result column="BANKACCOUNT" property="bankaccount" />
        <result column="BANKCARDFRONTPHOTOURL" property="bankcardfrontphotourl" />
        <result column="BANKCARDBACKPHOTOURL" property="bankcardbackphotourl" />
        <result column="AREACODE" property="areacode" />
        <result column="AREAID" property="areaid" />
        <result column="MEMBERAREAID" property="memberareaid" />
        <result column="BROKERID" property="brokerid" />
        <result column="ACCOUNTID" property="accountid" />
        <result column="LOGINCODE" property="logincode" />
        <result column="OPENMODE" property="openmode" />
        <result column="USERSTATE" property="userstate" />
        <result column="REMARK" property="remark" />
        <result column="ISVALIDATE" property="isvalidate" />
        <result column="CREATETIME" property="createtime" />
        <result column="MODIFIEDTIME" property="modifiedtime" />
        <result column="MODIFIEDBY" property="modifiedby" />
        <result column="REMOVEBEFORESTATUS" property="removebeforestatus" />
        <result column="CUSBANKID" property="cusbankid" />
        <result column="CUSBANKNAME" property="cusbankname" />
        <result column="SIGNPDFURL" property="signpdfurl" />
        <result column="REFERRAL" property="referral" />
        <result column="SUBBRANCH" property="subbranch" />
        <result column="BANKPICTUREURL" property="bankpictureurl" />
        <result column="AUDITIME" property="auditime" />
        <result column="AUDITEDBY" property="auditedby" />
        <result column="VIDEOURL" property="videourl" />
        <result column="SIGNEDSTATUS" property="signedstatus" />
        <result column="USERINFOTYPE" property="userinfotype" />
        <result column="CUSTOMERNAME" property="customername" />
        <result column="BIZNATURE" property="biznature" />
        <result column="BIZSCOPE" property="bizscope" />
        <result column="LEGALPERSONNAME" property="legalpersonname" />
        <result column="CONTACTNAME" property="contactname" />
        <result column="COUNTRYID" property="countryid" />
        <result column="PROVINCEID" property="provinceid" />
        <result column="CITYID" property="cityid" />
        <result column="DISTRICTID" property="districtid" />
        <result column="POSTALCODE" property="postalcode" />
        <result column="COMPANY" property="company" />
        <result column="LEGALCARDFRONTPHOTOURL" property="legalcardfrontphotourl" />
        <result column="LEGALCARDBACKPHOTOURL" property="legalcardbackphotourl" />
        <result column="OTHERURL" property="otherurl" />
        <result column="USERTYPE" property="usertype" />
        <result column="QQ" property="qq" />
        <result column="TELPHONE" property="telphone" />
        <result column="ATTACHMENT1" property="attachment1" />
        <result column="ATTACHMENT2" property="attachment2" />
        <result column="BANKACCOUNTNAME" property="bankaccountname" />
        <result column="NICKNAME" property="nickname" />
        <result column="HEADURL" property="headurl" />
        <result column="MOBILE2" property="mobile2" />
        <result column="BIZTYPE" property="biztype" />
        <result column="PROXYSTATEMENTURL" property="proxystatementurl" />
        <result column="CONTACTCARDFRONTPHOTOURL" property="contactcardfrontphotourl" />
        <result column="CONTACTCARDBACKPHOTOURL" property="contactcardbackphotourl" />
        <result column="ATTACHMENT3" property="attachment3" />
        <result column="ATTACHMENT4" property="attachment4" />
        <result column="ATTACHMENT5" property="attachment5" />
        <result column="AUDITSRC" property="auditsrc" />
        <result column="TAXPAYERNUM" property="taxpayernum" />
        <result column="LOGINPWD" property="loginpwd" />
        <result column="AGREEMENTIDS" property="agreementids" />
        <result column="BIRTHDAY" property="birthday" />
        <result column="CREDITQUOTA" property="creditquota" />
    </resultMap>

    <resultMap id="InvestorResultMap" type="com.muchinfo.mtp3pojos.vo.investor.InvestorQueryResult">
        <id column="USERID" property="userId" />
        <result column="USERNAME" property="userName" />
        <result column="OPENMODE" property="openMode" />
        <result column="USERSTATE" property="userState" />
        <result column="CREATETIME" property="createTime" />
        <association property="memberUserName" column="MEMBERAREAID"
                     jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
        <association property="areaName" column="AREAID" jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
    </resultMap>


    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        USERID, USERNAME, SEX, CARDTYPE, CARDNUM, CARDADDRESS, MOBILEPHONE, WECHAT, EMAIL, IPADDRESS, HALFBODYPHOTOURL, CARDFRONTPHOTOURL, CARDBACKPHOTOURL, BANKID, BANKNAME, BANKACCOUNT, BANKCARDFRONTPHOTOURL, BANKCARDBACKPHOTOURL, AREACODE, AREAID, MEMBERAREAID, BROKERID, ACCOUNTID, LOGINCODE, OPENMODE, USERSTATE, REMARK, ISVALIDATE, CREATETIME, MODIFIEDTIME, MODIFIEDBY, REMOVEBEFORESTATUS, CUSBANKID, CUSBANKNAME, SIGNPDFURL, REFERRAL, SUBBRANCH, BANKPICTUREURL, AUDITIME, AUDITEDBY, VIDEOURL, SIGNEDSTATUS, USERINFOTYPE, CUSTOMERNAME, BIZNATURE, BIZSCOPE, LEGALPERSONNAME, CONTACTNAME, COUNTRYID, PROVINCEID, CITYID, DISTRICTID, POSTALCODE, COMPANY, LEGALCARDFRONTPHOTOURL, LEGALCARDBACKPHOTOURL, OTHERURL, USERTYPE, QQ, TELPHONE, ATTACHMENT1, ATTACHMENT2, BANKACCOUNTNAME, NICKNAME, HEADURL, MOBILE2, BIZTYPE, PROXYSTATEMENTURL, CONTACTCARDFRONTPHOTOURL, CONTACTCARDBACKPHOTOURL, ATTACHMENT3, ATTACHMENT4, ATTACHMENT5, AUDITSRC, TAXPAYERNUM, LOGINPWD, AGREEMENTIDS, BIRTHDAY, CREDITQUOTA
    </sql>


    <select id="selectByPage" resultType="com.muchinfo.mtp3pojos.entity.WskhUserinfo"
            parameterType="com.muchinfo.mtp3pojos.entity.WskhUserinfo">
        select o.* from wskh_userinfo o
        <where>
            and o.userstate in (1,2,3,4)
            <if test="vo.username != null and vo.username != '' ">
                and (o.username like
                '%'||#{vo.username,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.usertype != null">
                and o.usertype=${vo.usertype}
            </if>
        </where>
        order by o.createtime desc
    </select>

    <select id="queryInvestorPage" resultMap="InvestorResultMap"
            parameterType="com.muchinfo.mtp3pojos.vo.investor.InvestorQueryParam">
        select o.userid userId,o.username userName, o.openmode openMode,o.userstate userState ,o.createtime createTime,
        o.MEMBERAREAID,o.AREAID
        from wskh_userinfo o
        <where>
            <if test="vo.userName != null and vo.userName != '' ">
                and (o.username like
                '%'||#{vo.userName,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.userTypes != null and vo.userTypes != '' ">
                and o.usertype in (${vo.userTypes})
            </if>
            <if test="vo.areaCode != null and vo.areaCode != '' ">
                and (o.memberareaid=${vo.areaCode} or o.areaid=${vo.areaCode})
            </if>
            <choose>
                <when test="vo.userstate != null and vo.userstate==0">
                    <!-- and o.userstate = 1 -->
                </when>
                <when test="vo.userState != null and vo.userState==2">
                    and o.userstate = 2
                </when>
                <when test="vo.userState != null and vo.userState==4">
                    and o.userstate = 4
                </when>
                <otherwise>
                    and o.userstate in (2,4)
                </otherwise>
            </choose>
            <if test="vo.areaId != null and vo.areaId!=0">
                and o.areaid in (
                select u.userid from useraccount u
                start with u.userid =
                #{vo.areaId}
                connect by u.parentuserid = prior
                u.userid and u.usertype=3
                )
            </if>
        </where>
        order by o.userstate,o.createtime desc
    </select>
    <select id="querymembergoodslimit" resultType="com.muchinfo.mtp3pojos.vo.organ.MemberGoodsLimitResult">
        select t.autoid "autoid", t.userid "userid", t.roletype "roletype", t.goodsid "goodsid",
        t.isnodisplay "isnodisplay", t.iscannotbuy "iscannotbuy", t.iscannotsell "iscannotsell",
        g.marketid "marketid",
        ua.userid || '/' || ua.accountname "membername",
        g.goodscode || '/' || g.goodsname "goodsdisplay",
        ei.enumdicname "roledisplay"
        from MemberGoodsLimitConfig t
        left join useraccount ua on t.userid = ua.userid
        left join goods g on t.goodsid = g.goodsid
        left join enumdicitem ei on ei.enumdiccode='roleType' and t.roletype = ei.enumitemname
        where 1 = 1
        <if test="vo.roletype !=null and vo.roletype!=''">
            and t.roletype = #{vo.roletype}
        </if>
        <if test="vo.userid !=null">
            and t.userid = #{vo.userid}
        </if>
        <if test="vo.goodsid !=null">
            and t.goodsid = #{vo.goodsid}
        </if>
        <if test="vo.marketid != null and vo.marketid != -1">
            and g.marketid = #{vo.marketid,jdbcType=DECIMAL}
        </if>
        order by t.userid, g.goodscode
    </select>
    <select id="accountriskconfig" resultType="com.muchinfo.mtp3pojos.vo.organ.AccountRiskConfigResult">

        select a.*,u.accountname as username,u.memberuserid,t.ismarketaccount from AccountRiskConfig a
        left join useraccount u on a.userid = u.userid
        left join taaccount t on a.accountid=t.accountid
        <where>
            and u.accountstatus = 4
            <choose>
                <when test="vo.member!=null and vo.member==1">
                    and u.usertype=2
                </when>
                <otherwise>
                    and u.usertype=5
                </otherwise>
            </choose>

            <if test="vo.accountid != null">
                and a.accountid = #{vo.accountid}
            </if>
            <if test="vo.username != null and vo.username != ''">
                and ((u.accountname like
                '%'||#{vo.username,jdbcType=VARCHAR}||'%')
                or(u.userid like '%'||#{vo.username,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.memberuserid != null">
                and a.userid in (
                select t.userid from useraccount t where u.usertype=5 start with t.userid =#{vo.memberuserid}
                connect by prior t.userid = t.parentuserid
                and t.usertype !=2
                )
            </if>
            order by a.userid ,a.accountid
        </where>
    </select>
    <select id="queryWareHouse" resultType="com.muchinfo.mtp3pojos.vo.tradingmarket.goodssetup.WarehouseInfoParam"
            parameterType="java.lang.Long">
        select t.dgfactoryitemid,w.*,d.pathname
        from DGFactoryItem t
                 left join warehouseinfo w on t.warehouseid = w.autoid
                 left join division d on w.districtid = d.autoid
        where t.deliverygoodsid = #{deliverygoodsid}
          and t.warehouseid is not null
    </select>

</mapper>
