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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.Loginaccount">
        <id column="LOGINID" property="loginid" />
        <result column="USERID" property="userid" />
        <result column="LOGINFAILNUM" property="loginfailnum" />
        <result column="PASSWORD" property="password" />
        <result column="LOGINUSERTYPE" property="loginusertype" />
        <result column="LOGINSTATUS" property="loginstatus" />
        <result column="LASTLOGINTIME" property="lastlogintime" />
        <result column="LASTLOGINMODE" property="lastloginmode" />
        <result column="LASTLOGINIP" property="lastloginip" />
        <result column="LOGINPORT" property="loginport" />
        <result column="LASTLOGINREMARK" property="lastloginremark" />
        <result column="MODIFYTIME" property="modifytime" />
        <result column="MODIFIERID" property="modifierid" />
        <result column="PWDWRONGCOUNT" property="pwdwrongcount" />
        <result column="UNFREEZETIME" property="unfreezetime" />
        <result column="HASLOGINED" property="haslogined" />
        <result column="HASUPDATEDPWD" property="hasupdatedpwd" />
        <result column="LOGINCODE" property="logincode" />
        <result column="CANOUTIN" property="canoutin" />
        <result column="CLIENTROLEID" property="clientroleid" />
        <result column="TAACCOUNTRIGHTTYPE" property="taaccountrighttype" />
        <result column="MOBILE" property="mobile" />
        <result column="ACCOUNTNAME" property="accountname" />
        <result column="MODIFYSRC" property="modifysrc" />
        <result column="RELATEDUSERID" property="relateduserid" />
        <result column="SUBJECTID" property="subjectid" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        LOGINID, USERID, LOGINFAILNUM, PASSWORD, LOGINUSERTYPE, LOGINSTATUS, LASTLOGINTIME, LASTLOGINMODE, LASTLOGINIP, LOGINPORT, LASTLOGINREMARK, MODIFYTIME, MODIFIERID, PWDWRONGCOUNT, UNFREEZETIME, HASLOGINED, HASUPDATEDPWD, LOGINCODE, CANOUTIN, CLIENTROLEID, TAACCOUNTRIGHTTYPE, MOBILE, ACCOUNTNAME, MODIFYSRC, RELATEDUSERID, SUBJECTID
    </sql>

    <select id="getLoginidsByUserid" parameterType="long" resultType="string">
        select wm_concat(la.loginid) loginids from loginaccount la where la.userid = #{userid,jdbcType=VARCHAR}
    </select>

    <select id="getLoginNotExistsLt" parameterType="java.lang.Long" resultType="java.lang.Long">
        select t.loginid
        from loginaccount t
        where t.userid = #{useid}
          and t.loginstatus = 1
          and not exists(select * from logintaaccount l where l.loginid = t.loginid)
    </select>


    <select id="queryLoginAccountPage" resultType="com.muchinfo.mtp3pojos.vo.investor.InvestorLoginQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.investor.InvestorLoginQueryParam">
        select t.loginid ,t.loginstatus ,t.modifytime,s.username as modifiername
        from loginaccount t
        left join systemmanager s on t.modifierid = s.autoid
        <where>
            <if test="vo.loginusertype != null">
                and t.loginusertype = #{vo.loginusertype}
            </if>
            <if test="vo.loginid !=null">
                and t.loginid = #{vo.loginid}
            </if>
            <if test="vo.loginstatus != null">
                and t.loginstatus = #{vo.loginstatus}
            </if>
            <if test="vo.userid != null">
                and t.userid = #{vo.userid}
            </if>
        </where>
        order by t.loginstatus,t.loginid
    </select>

    <select id="queryTradingAccount" resultType="com.muchinfo.mtp3pojos.vo.tradingacc.TradingAccountQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.tradingacc.TradingAccountQueryParam">
        select t.*,s.username,smr.rolename,s1.username as modifiername,
        (select listagg(uai.authid, ',') within group(order by uai.authtype,uai.authid)
        from userauthinfo uai where uai.loginid=t.loginid and uai.userid=t.userid) "auth",
        (select listagg(uai.authtype, ',') within group(order by uai.authtype,uai.authid)
        from userauthinfo uai where uai.loginid=t.loginid and uai.userid=t.userid) "authtype"
        from loginaccount t
        left join systemmanager s on t.logincode = s.logincode
        left join systemmanager s1 on t.modifierid = s1.autoid
        left join SystemManagerRole smr on t.clientroleid = smr.autoid
        <where>
            <if test="vo.loginusertype != null">
                and t.loginusertype = #{vo.loginusertype}
            </if>
            <if test="vo.loginid !=null">
                and t.loginid = #{vo.loginid}
            </if>
            <if test="vo.loginstatus != null">
                and t.loginstatus = #{vo.loginstatus}
            </if>
            <if test="vo.userid != null">
                and t.userid = #{vo.userid}
            </if>
        </where>
        order by t.loginstatus,t.loginid
    </select>

</mapper>
