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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.Systemmanager">
        <id column="AUTOID" property="autoid" />
        <result column="RELETYPE" property="reletype" />
        <result column="LOGINCODE" property="logincode" />
        <result column="USERNAME" property="username" />
        <result column="PASSWORD" property="password" />
        <result column="AREAUSERID" property="areauserid" />
        <result column="PWDEXPDATE" property="pwdexpdate" />
        <result column="MANAGERSTATUS" property="managerstatus" />
        <result column="LOGINSTATUS" property="loginstatus" />
        <result column="ISDEFAULT" property="isdefault" />
        <result column="LOGINTIME" property="logintime" />
        <result column="LOGINIP" property="loginip" />
        <result column="LOGINPORT" property="loginport" />
        <result column="LOGINFAILNUM" property="loginfailnum" />
        <result column="UPLOGINID" property="uploginid" />
        <result column="MODIFYTIME" property="modifytime" />
        <result column="REMARK" property="remark" />
        <result column="ONYMAC" property="onymac" />
        <result column="MANAGERTYPE" property="managertype" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        AUTOID, RELETYPE, LOGINCODE, USERNAME, PASSWORD, AREAUSERID, PWDEXPDATE, MANAGERSTATUS, LOGINSTATUS, ISDEFAULT, LOGINTIME, LOGINIP, LOGINPORT, LOGINFAILNUM, UPLOGINID, MODIFYTIME, REMARK, ONYMAC, MANAGERTYPE
    </sql>

    <select id="selectUsersName" parameterType="java.lang.Long"
            resultType="java.lang.String">
        select username from SystemManager where autoid = #{userid,jdbcType=BIGINT}
    </select>

    <select id="selectByPageVo" resultType="com.muchinfo.mtp3pojos.vo.systemuser.SystemUserResResult"
            parameterType="com.muchinfo.mtp3pojos.vo.systemuser.SystemUserReqParam">
        select distinct t.autoid,
            t.logincode,
            t.username,
            t.areauserid,
            a.accountname as areaName,
            t.managerstatus,
            t.loginstatus,
            t.uploginid,
            t.modifytime,
            t.isdefault,
            listagg( to_char(r.rolename),',') within group(order by r.rolename) over(partition by t.autoid) as roleName
        from SystemManager t
        left join RoleUser u on u.managerid = t.autoid
        left join SystemManagerRole r on r.autoid = u.roleid
        left join UserAccount a on a.userid=t.areauserid
        <where>
            1=1
            and t.managertype = 1
            and t.systemtype = 2
            <if test="systemParam.managerStatus != null ">
                and t.managerstatus=#{systemParam.managerStatus,jdbcType=INTEGER}
            </if>
            <if test="systemParam.loginCode != null and systemParam.loginCode != '' ">
                and ((t.logincode like '%'||#{systemParam.loginCode,jdbcType=VARCHAR}||'%')
                or (t.username like '%'||#{systemParam.loginCode,jdbcType=VARCHAR}||'%'))
            </if>

            <if test="systemParam.areaids != null and systemParam.areaids!='' ">
                and t.areauserid in (${systemParam.areaids})
            </if>
        </where>
        order by
        case t.managerstatus
        when 1 then 1
        when 3 then 2
        when 2 then 3
        end asc , t.logincode
    </select>

    <select id="getSystemManagerByRole" resultType="com.muchinfo.mtp3pojos.entity.Systemmanager" parameterType="java.lang.Integer">
        select s.logincode, s.username
        from SYSTEMMANAGER s
        left join ROLEUSER r on s.autoid = r.managerid
        <where>
            <if test="autoId != null">
                and r.roleid = #{autoId}
            </if>
        </where>
        order by s.logincode
    </select>

    <select id="selectLogByParam" resultType="com.muchinfo.mtp3pojos.vo.systemrun.SystemUserLogResult" parameterType="com.muchinfo.mtp3pojos.vo.systemrun.SystemUserLogParam" >
        select
        l.autoid,
        l.userid,
        a.accountname as areaname,
        l.logincode,
        l.username,
        l.resourcecode,
        l.resourcename,
        l.content,
        l.operatetime,
        l.ip
        from SystemManagerLog  l
        left join UserAccount a on a.userid = l.parentuserid
        <where>
            <if test="vo.userName != null and vo.userName != '' " >
                and l.username like  concat('%','#{vo.userName}', '%')
            </if>
            <if test="vo.ishiddenlog == null || vo.ishiddenlog == '' " >
                and l.resourcecode != 'M00000000000'
            </if>
            <if test="vo.resourcecodes != null and vo.resourcecodes != '' " >
                and l.resourcecode in (${vo.resourcecodes})
            </if>
            <if test="vo.logincode != null and vo.logincode != '' " >
                and l.logincode = #{vo.logincode, jdbcType=VARCHAR}
            </if>
            <if test="vo.starttime != null and vo.starttime != ''">
                <![CDATA[and l.operatetime >= to_date(#{vo.starttime}, 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>
            <if test="vo.endtime != null and vo.endtime != ''">
                <![CDATA[and l.operatetime <= to_date(#{vo.endtime}, 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>
            <choose>
                <when test="vo.areaids != null and vo.areaids != '' ">
                    and l.parentuserid in (${vo.areaids})
                </when>
                <when test="vo.areauserid != null">
                    and l.parentuserid=#{vo.areauserid,jdbcType=DECIMAL}
                </when>
            </choose>
            <if test="vo.issenv != null and vo.issenv != '' " >
                and l.content like '%敏感%'
            </if>
            <if test="vo.content != null and vo.content != '' " >
                and l.content like concat('%','#{vo.content}', '%')
            </if>
        </where>
        order by l.operatetime desc
    </select>


    <select id="queryUserLoginFlow" parameterType="com.muchinfo.mtp3pojos.vo.systemrun.SystemLoginErrorParam"
            resultType="com.muchinfo.mtp3pojos.vo.systemrun.SystemLoginErrorResult">
        select re.* from(
        select
        decode(f.clienttype,1,(case to_char(m.logincode||'-') when '-' then to_char(f.loginid) else m.logincode end ||(case to_char('('||m.username||')')when '()' then  '' ELSE to_char('('||m.username||')')end)),
        f.loginid||(case to_char('('||u.accountname||')')when '()' then  '' ELSE to_char('('||u.accountname||')') end)) as loginAccount,
        to_char(f.operatetime,'yyyy-MM-dd HH24:mi:ss') as operatetime,
        f.clientType,
        f.loginip,
        decode(f.clienttype,1,f.remark,e.description) as description
        from userloginflow f
        left join loginaccount l on f.loginid =l.loginid
        left join useraccount u on l.userid = u.userid
        left join systemmanager m on m.autoid=f.loginid
        left join errorcode e on f.loginretcode=e.errorid
        where
        f.loginretcode is not null
        and f.loginretcode != 0
        <if test="vo.starttime !=  null">
            and f.operatetime &gt;=to_date(#{vo.starttime},'yyyy-MM-dd HH24:mi')
        </if>
        <if test="vo.endtime !=  null">
            and f.operatetime &lt;=to_date(#{vo.endtime},'yyyy-MM-dd  HH24:mi')
        </if>
        <if test="vo.parentuserid !=  null">
            and (m.areauserid=#{vo.parentuserid} or u.parentuserid=#{vo.parentuserid})
        </if>
        order by f.operatetime desc) re
        <if test="vo.userName != null">
            where re.loginAccount like '%'||#{vo.userName}||'%'
        </if>
    </select>

    <!-- 查询在线用户 -->
    <select id="queryUserOnlineByPage" resultType="com.muchinfo.mtp3pojos.vo.marketrun.MarketRunLoginUserResult">
        select t.loginid "loginid", t.parentuserid "parentuserid",
        t.memberuserid "memberuserid", t.clienttype "clienttype",
        t.groupnum "groupnum", t.softversion "softversion",
        t.loginip "loginip", t.loginport "loginport",
        t.logintime "logintime", t.deviceid "deviceid",
        t.environmentinfo "environmentinfo", mem.userid || '/' || mem.accountname "membername",
        area.userid || '/' || area.accountname "areaname", ua.userid || '/' || ua.accountname "username"
        from useronline t left join useraccount mem on t.memberuserid=mem.userid
        left join useraccount area on t.parentuserid=area.userid
        left join loginaccount la on t.loginid=la.loginid
        left join useraccount ua on la.userid=ua.userid
        <where>
            <if test="loginid != null">
                <![CDATA[and t.loginid = #{loginid}]]>
            </if>
            <if test="parentuserid != null">
                <![CDATA[and ua.parentuserid = #{parentuserid}]]>
            </if>
            <if test="memberuserid != null">
                and ua.userid in (
                select uo.userid
                from useraccount uo
                start with uo.parentuserid = #{memberuserid}
                connect by prior uo.userid =uo.parentuserid and uo.userid !=#{memberuserid}
                )
            </if>
        </where>
        order by t.logintime desc
    </select>

    <select id="getAllUserByOrgan2" resultType="com.muchinfo.mtp3pojos.vo.organ.UserAccountOrgan2UserResult">
        select o.* from SystemManager o,UserAccount t
        where o.areauserid=t.userid and t.usertype !=5 and (t.userid= #{userid} or t.memberuserid =#{userid})
        order by t.userid,o.logincode
    </select>

    <update id="updateorgan2User">
        update SystemManager set managerstatus = #{status}
        where areauserid in (select userid from UserAccount t where t.usertype !=5
          and (t.memberuserid =#{userid} or t.userid= #{userid}))
    </update>

    <select id="queryselfsupport" resultType="com.muchinfo.mtp3pojos.vo.organ.UserAccountSelfsupportResult">
        select t.*,s.username,smr.rolename,
        (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 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.userid != null">
                and t.userid = #{vo.userid}
            </if>
        </where>
        order by t.loginstatus,t.loginid
    </select>
    <select id="querymrsl" resultType="com.muchinfo.mtp3pojos.vo.systemrun.YmrslQueryResult">
        select t.*,m.marketname "marketname" from MarketRunStatusLog t
        left join market m on t.marketid=m.marketid
        <where>
            <if test="vo.marketid != null">
                and t.marketid = #{vo.marketid}
            </if>
            <if test="vo.tradedate != null and vo.tradedate !=''">
                and t.tradedate = #{vo.tradedate}
            </if>
        </where>
        order by t.updatetime desc
    </select>
    <select id="queryloginlog" resultType="com.muchinfo.mtp3pojos.vo.systemrun.LoginLogQueryResult">
        select t.loginflowno "loginflowno",t.loginid "loginid",t.funcode "funcode",
        t.clienttype "clienttype",t.softversion "softversion",t.operatetime "operatetime",
        t.loginip "loginip",t.loginport "loginport",t.deviceid "deviceid",
        t.environmentinfo "environmentinfo",t.operatetype "operatetype",t.remark "remark",
        t.logintype "logintype",t.authid "authid",t.authtype "authtype",t.loginretcode "loginretcode",
        la.loginid "logincode",ua.accountname "accountname",area.accountname "areaname",
        mem.accountname "membername",
        <choose>
            <when test="vo.ishis == 1">
                1 as ishis
            </when>
            <otherwise>
                0 as ishis
            </otherwise>
        </choose>
        <choose>
            <when test="vo.ishis == 1">
                from his_userloginflow t
            </when>
            <otherwise>
                from userloginflow t
            </otherwise>
        </choose>
        left join loginaccount la on t.loginid=la.loginid
        left join useraccount ua on la.userid=ua.userid
        left join useraccount area on ua.parentuserid = area.userid
        left join useraccount mem on ua.memberuserid = mem.userid
        <where>
            and t.clienttype != 1
            <if test="vo.logincode != null and vo.logincode != ''">
                <![CDATA[and la.loginid = #{vo.logincode}]]>
            </if>
            <if test="vo.accountname !=null and vo.accountname != '' ">
                <![CDATA[and ua.accountname like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%']]>
            </if>
            <if test="vo.operatetime != null and vo.operatetime != ''">
                <![CDATA[and t.operatetime >= to_date(#{vo.operatetime}||' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                and t.operatetime <= to_date(#{vo.operatetime}||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>
            <if test="vo.loginid != null and vo.loginid != ''">
                <![CDATA[and t.loginid = #{vo.loginid}]]>
            </if>
            <if test="vo.loginflowno != null and vo.loginflowno != ''">
                <![CDATA[and t.loginflowno = #{vo.loginflowno}]]>
            </if>
            <if test="vo.memberuserid != null and vo.memberuserid != ''">
                <![CDATA[and ua.userid in (
            		select uo.userid
					from useraccount uo
					start with uo.parentuserid = #{vo.memberuserid}
					connect by prior uo.userid =uo.parentuserid and uo.userid != #{vo.memberuserid})]]>
            </if>
        </where>
        order by t.operatetime desc
    </select>
    <select id="queryMarketSet" resultType="com.muchinfo.mtp3pojos.vo.systemsetup.SystemMarketSectionResult">
        select
        m.marketsectionid,m.marketsectionname,
        (select count(1) from  MarketSectionConfig mc where mc.marketsectionid = m.marketsectionid)  totalchild,
        m.orderindex ,to_char(m.modifytime,'yyyy-MM-dd hh24:mi:ss') modifytimestr,
        decode(m.modifiersrc,1,s.username,ui.customername) modifiername
        from MarketSection m
        left join systemmanager s on m.modifierid = s.autoid
        left join loginaccount la on m.modifierid = la.loginid
        left join userinfo ui on la.userid = ui.userid
        <where>
            m.isvalid ='1'
            <if test="vo.marketsectionid!=null and vo.marketsectionid!=''">
                and  m.marketsectionid = #{vo.marketsectionid }
            </if>
            <if test="vo.marketsectionname!=null and vo.marketsectionname!=''">
                and m.marketsectionname like '%'||#{vo.marketsectionname }||'%'
            </if>
        </where>
        order by m.orderindex
    </select>
    <select id="queryMarketSectionBean" resultType="com.muchinfo.mtp3pojos.vo.systemsetup.SystemMarketSetDetail"
            parameterType="java.lang.Integer">
        select
            m.marketsectionid,m.marketsectionname,m.pictureurl,
            m.marketsectionnameen,m.marketsectionnamezh_tw as marketsectionnamezhtw,m.marketsectionnameth,m.marketsectionnamevi,
            m.orderindex,to_char(m.modifytime,'yyyy-MM-dd hh24:mi:ss') modifytimestr,
            decode(m.modifiersrc,1,s.username,ui.customername) modifiername
        from MarketSection m
        left join systemmanager s on m.modifierid = s.autoid
        left join loginaccount la on m.modifierid = la.loginid
        left join userinfo ui on la.userid = ui.userid
        <where>   m.isvalid ='1'
            <if test="marketsectionid!=null and marketsectionid!=''">
                and  m.marketsectionid = #{marketsectionid }
            </if>
        </where>
        order by m.orderindex
    </select>
    <select id="queryMarketSectionConfigNewList"
            resultType="com.muchinfo.mtp3pojos.vo.systemsetup.ListMarketSectionConfig"
            parameterType="java.lang.Integer">
        select ms.marketsectionid, ms.displayname, ms.marketids,
               ms.orderindex, ms.trademode,
               (select LISTAGG(m.marketname, ',')   within group (order by marketid) as marketnames
        from market m where ',' || ms.marketids || ',' like '%,' ||  m.marketid || ',%') as marketnames,
            ms.displaynameen, ms.displaynamezh_tw as displaynamezhtw, ms.displaynameth, ms.displaynamevi
        from MarketSectionConfigNew ms
        where ms.marketsectionid = #{marketsectionid}
        order by ms.orderindex
    </select>
    <select id="queryI18nConfigs" resultType="com.muchinfo.mtp3pojos.vo.I18nconfig">
        select langcode "langcode", langname "langname"
        from i18nconfig
        where isvalid = 1
    </select>

    <delete id="deletemarketSet" parameterType="java.lang.Integer">
        begin
            delete from MarketSection ms where ms.marketsectionid = #{marketsectionid};
            delete from MarketSectionConfigNew mscn where mscn.marketsectionid = #{marketsectionid};
            commit;
        end;
    </delete>
</mapper>
