fj-dc/.svn/pristine/4b/4b8e1225b81dcebde22bd723eb6b2e79fcd8f2e5.svn-base

<?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="cn.com.goldenwater.dcproj.dao.UserDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.User" id="userResultMap">
        <result property="id" column="ID"/>
        <result property="ofcId" column="OFC_ID"/>
        <result property="depId" column="DEP_ID"/>
        <result property="loginName" column="LOGIN_NAME"/>
        <result property="password" column="PASSWORD"/>
        <result property="userState" column="USER_STATE"/>
        <result property="userNbr" column="USER_NBR"/>
        <result property="userName" column="USER_NAME"/>
        <result property="userGender" column="USER_GENDER"/>
        <result property="userEmail" column="USER_EMAIL"/>
        <result property="userTel" column="USER_TEL"/>
        <result property="userMobile" column="USER_MOBILE"/>
        <result property="userType" column="USER_TYPE"/>
        <result property="userAvatarUrl" column="USER_AVATAR_URL"/>
        <result property="idCard" column="ID_CARD"/>
        <result property="idCardFrontUrl" column="ID_CARD_FRONT_URL"/>
        <result property="idCardBackUrl" column="ID_CARD_BACK_URL"/>
        <result property="loginIp" column="LOGIN_IP"/>
        <result property="loginTime" column="LOGIN_TIME"/>
        <result property="token" column="TOKEN"/>
        <result property="flagLogin" column="FLAG_LOGIN"/>
        <result property="flagValid" column="FLAG_VALID"/>
    </resultMap>
    <resultMap type="cn.com.goldenwater.dcproj.dto.UserDto" id="userDtoResultMap">
        <id property="id" column="ID"/>
        <result property="ofcId" column="OFC_ID"/>
        <result property="depId" column="DEP_ID"/>
        <result property="loginName" column="LOGIN_NAME"/>
        <result property="password" column="PASSWORD"/>
        <result property="userState" column="USER_STATE"/>
        <result property="userNbr" column="USER_NBR"/>
        <result property="userName" column="USER_NAME"/>
        <result property="userGender" column="USER_GENDER"/>
        <result property="userEmail" column="USER_EMAIL"/>
        <result property="userTel" column="USER_TEL"/>
        <result property="userMobile" column="USER_MOBILE"/>
        <result property="userType" column="USER_TYPE"/>
        <result property="userAvatarUrl" column="USER_AVATAR_URL"/>
        <result property="idCard" column="ID_CARD"/>
        <result property="idCardFrontUrl" column="ID_CARD_FRONT_URL"/>
        <result property="idCardBackUrl" column="ID_CARD_BACK_URL"/>
        <result property="loginIp" column="LOGIN_IP"/>
        <result property="loginTime" column="LOGIN_TIME"/>
        <result property="token" column="TOKEN"/>
        <result property="flagLogin" column="FLAG_LOGIN"/>
        <result property="flagValid" column="FLAG_VALID"/>
        <collection property="role" ofType="cn.com.goldenwater.dcproj.model.Role">
            <result property="id" column="ROLEID"/>
            <result property="ofcId" column="ROLE_OFC_ID"/>
            <result property="roleName" column="ROLE_NAME"/>
            <result property="flagValid" column="ROLE_FLAG_VALID"/>
            <result property="dataScope" column="DATA_SCOPE"/>
            <collection property="priv" ofType="cn.com.goldenwater.dcproj.model.Priv">
                <result property="id" column="PRIVID"/>
                <result property="privName" column="PRIV_NAME"/>
                <result property="privType" column="PRIV_TYPE"/>
                <result property="privUri" column="PRIV_URI"/>
                <result property="parentId" column="PRIV_PARENT_ID"/>
                <result property="flagLeaf" column="FLAG_LEAF"/>
            </collection>
        </collection>
    </resultMap>
    <sql id="table_columns">
		ID,
		OFC_ID,
		DEP_ID,
		LOGIN_NAME,
		PASSWORD,
		USER_STATE,
		USER_NBR,
		USER_NAME,
		USER_GENDER,
		USER_EMAIL,
		USER_TEL,
		USER_MOBILE,
		USER_TYPE,
		USER_AVATAR_URL,
		ID_CARD,
		ID_CARD_FRONT_URL,
		ID_CARD_BACK_URL,
		LOGIN_IP,
		LOGIN_TIME,
		TOKEN,
		FLAG_LOGIN,
		FLAG_VALID
    </sql>
    <sql id="entity_properties">
		#{id},
		#{ofcId},
		#{depId},
		#{loginName},
		#{password},
		#{userState},
		#{userNbr},
		#{userName},
		#{userGender},
		#{userEmail},
		#{userTel},
		#{userMobile},
		#{userType},
		#{userAvatarUrl},
		#{idCard},
		#{idCardFrontUrl},
		#{idCardBackUrl},
		#{loginIp},
		#{loginTime},
		#{token},
		#{flagLogin},
		#{flagValid}
	</sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="ofcId != null and ofcId != ''">and OFC_ID = #{ofcId}</if>
            <if test="depId != null and depId != ''">and DEP_ID = #{depId}</if>
            <if test="loginName != null and loginName != ''">and LOGIN_NAME LIKE concat(concat('%',#{loginName}),'%')</if>
            <if test="password != null and password != ''">and PASSWORD = #{password}</if>
            <if test="userState != null and userState != ''">and USER_STATE = #{userState}</if>
            <if test="userNbr != null and userNbr != ''">and USER_NBR = #{userNbr}</if>
            <if test="userName != null and userName != ''">and USER_NAME = #{userName}</if>
            <if test="userGender != null and userGender != ''">and USER_GENDER = #{userGender}</if>
            <if test="userEmail != null and userEmail != ''">and USER_EMAIL = #{userEmail}</if>
            <if test="userTel != null and userTel != ''">and USER_TEL = #{userTel}</if>
            <if test="userMobile != null and userMobile != ''">and USER_MOBILE = #{userMobile}</if>
            <if test="userType != null and userType != ''">and USER_TYPE = #{userType}</if>
            <if test="userAvatarUrl != null and userAvatarUrl != ''">and USER_AVATAR_URL = #{userAvatarUrl}</if>
            <if test="idCard != null and idCard != ''">and ID_CARD = #{idCard}</if>
            <if test="idCardFrontUrl != null and idCardFrontUrl != ''">and ID_CARD_FRONT_URL = #{idCardFrontUrl}</if>
            <if test="idCardBackUrl != null and idCardBackUrl != ''">and ID_CARD_BACK_URL = #{idCardBackUrl}</if>
            <if test="loginIp != null and loginIp != ''">and LOGIN_IP = #{loginIp}</if>
            <if test="loginTime != null">and LOGIN_TIME = #{loginTime}</if>
            <if test="token != null and token != ''">and TOKEN = #{token}</if>
            <if test="flagLogin != null">and FLAG_LOGIN = #{flagLogin}</if>
            <if test="flagValid != null">and FLAG_VALID = #{flagValid}</if>
        </trim>
    </sql>

    <select id="get" resultMap="userResultMap" parameterType="String">
        select
        <include refid="table_columns"/>
        from GW_SYS_USER where ID = #{id}
    </select>

    <select id="getBy" resultMap="userResultMap">
        select
        <include refid="table_columns"/>
        from GW_SYS_USER
        <include refid="page_where"/>
    </select>

    <select id="findAll" resultMap="userResultMap">
        select
        <include refid="table_columns"/>
        from GW_SYS_USER
    </select>

    <select id="findList" resultMap="userResultMap">
        select
        <include refid="table_columns"/>
        from GW_SYS_USER
        <include refid="page_where"/>
    </select>

    <select id="selectCount" resultType="int">
        select count(ID) from GW_SYS_USER
        <include refid="page_where"/>
    </select>


    <insert id="insert" parameterType="cn.com.goldenwater.dcproj.model.User">
        insert into GW_SYS_USER(
        <include refid="table_columns"/>
        )
        values (
        <include refid="entity_properties"/>
        )
    </insert>

    <delete id="delete" parameterType="java.lang.String">
        delete from GW_SYS_USER where ID = #{id}
    </delete>

    <delete id="deleteBy" parameterType="cn.com.goldenwater.dcproj.model.User">
        delete from GW_SYS_USER
        <include refid="page_where"/>
    </delete>

    <update id="deleteInFlag" parameterType="java.lang.String">
        update GW_SYS_USER set flag_valid = 0 where>ID = #{id}
    </update>

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.User">
        update GW_SYS_USER
        <trim prefix="set" suffixOverrides=",">
            <if test="ofcId != null and ofcId != ''">OFC_ID = #{ofcId},</if>
            <if test="depId != null and depId != ''">DEP_ID = #{depId},</if>
            <if test="loginName != null and loginName != ''">LOGIN_NAME = #{loginName},</if>
            <if test="password != null and password != ''">PASSWORD = #{password},</if>
            <if test="userState != null and userState != ''">USER_STATE = #{userState},</if>
            <if test="userNbr != null and userNbr != ''">USER_NBR = #{userNbr},</if>
            <if test="userName != null and userName != ''">USER_NAME = #{userName},</if>
            <if test="userGender != null and userGender != ''">USER_GENDER = #{userGender},</if>
            <if test="userEmail != null and userEmail != ''">USER_EMAIL = #{userEmail},</if>
            <if test="userTel != null and userTel != ''">USER_TEL = #{userTel},</if>
            <if test="userMobile != null and userMobile != ''">USER_MOBILE = #{userMobile},</if>
            <if test="userType != null and userType != ''">USER_TYPE = #{userType},</if>
            <if test="userAvatarUrl != null and userAvatarUrl != ''">USER_AVATAR_URL = #{userAvatarUrl},</if>
            <if test="idCard != null and idCard != ''">ID_CARD = #{idCard},</if>
            <if test="idCardFrontUrl != null and idCardFrontUrl != ''">ID_CARD_FRONT_URL = #{idCardFrontUrl},</if>
            <if test="idCardBackUrl != null and idCardBackUrl != ''">ID_CARD_BACK_URL = #{idCardBackUrl},</if>
            <if test="loginIp != null and loginIp != ''">LOGIN_IP = #{loginIp},</if>
            <if test="loginTime != null">LOGIN_TIME = #{loginTime},</if>
            <if test="token != null and token != ''">TOKEN = #{token},</if>
            <if test="flagLogin != null">FLAG_LOGIN = #{flagLogin},</if>
            <if test="flagValid != null">FLAG_VALID = #{flagValid},</if>
        </trim>
        <where>ID = #{id}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.User">
        update GW_SYS_USER
        <trim prefix="set" suffixOverrides=",">
            <if test="ofcId != null and ofcId != ''">OFC_ID = #{ofcId},</if>
            <if test="depId != null and depId != ''">DEP_ID = #{depId},</if>
            <if test="loginName != null and loginName != ''">LOGIN_NAME = #{loginName},</if>
            <if test="password != null and password != ''">PASSWORD = #{password},</if>
            <if test="userState != null and userState != ''">USER_STATE = #{userState},</if>
            <if test="userNbr != null and userNbr != ''">USER_NBR = #{userNbr},</if>
            <if test="userName != null and userName != ''">USER_NAME = #{userName},</if>
            <if test="userGender != null and userGender != ''">USER_GENDER = #{userGender},</if>
            <if test="userEmail != null and userEmail != ''">USER_EMAIL = #{userEmail},</if>
            <if test="userTel != null and userTel != ''">USER_TEL = #{userTel},</if>
            <if test="userMobile != null and userMobile != ''">USER_MOBILE = #{userMobile},</if>
            <if test="userType != null and userType != ''">USER_TYPE = #{userType},</if>
            <if test="userAvatarUrl != null and userAvatarUrl != ''">USER_AVATAR_URL = #{userAvatarUrl},</if>
            <if test="idCard != null and idCard != ''">ID_CARD = #{idCard},</if>
            <if test="idCardFrontUrl != null and idCardFrontUrl != ''">ID_CARD_FRONT_URL = #{idCardFrontUrl},</if>
            <if test="idCardBackUrl != null and idCardBackUrl != ''">ID_CARD_BACK_URL = #{idCardBackUrl},</if>
            <if test="loginIp != null and loginIp != ''">LOGIN_IP = #{loginIp},</if>
            <if test="loginTime != null">LOGIN_TIME = #{loginTime},</if>
            <if test="token != null and token != ''">TOKEN = #{token},</if>
            <if test="flagLogin != null">FLAG_LOGIN = #{flagLogin},</if>
            <if test="flagValid != null">FLAG_VALID = #{flagValid},</if>
        </trim>
        <include refid="page_where"/>
    </update>


    <!-- 其他自定义SQL -->
    <!-- 根据登录名查询用户 -->
    <select id="getByLoginName" resultMap="userResultMap" parameterType="String">
        select
        <include refid="table_columns"/>
        from gw_sys_user a where a.login_name = #{loginName}
    </select>

    <!-- 根据手机查询用户 -->
    <select id="getByMobile" resultMap="userResultMap" parameterType="String">
        select
        <include refid="table_columns"/>
        from gw_sys_user a where a.user_mobile = #{userMobile}
    </select>

    <!-- 根据用户ID查询用户（级联：机构、角色、岗位） -->
    <select id="getCascade" resultMap="userDtoResultMap" parameterType="String">
        select A.ID,
        A.OFC_ID,
        DEP_ID,
        LOGIN_NAME,
        PASSWORD,
        USER_STATE,
        USER_NBR,
        USER_NAME,
        USER_GENDER,
        USER_EMAIL,
        USER_TEL,
        USER_MOBILE,
        USER_TYPE,
        USER_AVATAR_URL,
        ID_CARD,
        ID_CARD_FRONT_URL,
        ID_CARD_BACK_URL,
        LOGIN_IP,
        LOGIN_TIME,
        TOKEN,
        FLAG_LOGIN,
        A.FLAG_VALID,
        B.id officeId,
        B.parent_id,
        province_id,
        city_id,
        country_id,
        ofc_addr,
        ofc_code,
        ofc_name,
        sort_level,
        ofc_type,
        ofc_level,
        ofc_zip,
        ofc_tel,
        ofc_fax,
        ofc_email,
        contact_name,
        contact_tel,
        license_nbr,
        license_url,
        D.POST_NAME,
        D.ID POSTID,
        D.FLAG_VALID POST_FLAG_VALID,
        F.ID ROLEID,
        F.ROLE_NAME,
        H.ID PRIVID,
        H.PRIV_NAME,
        H.PRIV_TYPE,
        H.PRIV_URI,
        H.PARENT_ID PRIV_PARENT_ID,
        H.FLAG_LEAF,
        D.OFC_ID POST_OFC_ID,
        F.OFC_ID ROLE_OFC_ID,
        F.FLAG_VALID ROLE_FLAG_VALID
        from GW_SYS_USER A LEFT JOIN GW_SYS_OFFICE B
        ON A.OFC_ID = B.ID
        left join GW_SYS_REL_USER_POST C ON A.ID=C.USER_ID
        LEFT JOIN GW_SYS_POST D ON C.POST_ID=D.ID
        LEFT JOIN GW_SYS_REL_USER_ROLE E ON A.id=E.USER_ID
        LEFT JOIN GW_SYS_ROLE F ON E.ROLE_ID=F.ID
        LEFT JOIN GW_SYS_REL_ROLE_PRIV G ON F.ID=G.ROLE_ID
        LEFT JOIN GW_SYS_PRIV H ON G.PRIV_ID=H.ID
        where A.ID = #{id}
    </select>

    <!-- 分页查询用户（级联：机构、角色、岗位） -->
    <select id="findPageDto" resultMap="userDtoResultMap" parameterType="cn.com.goldenwater.dcproj.param.UserParam">
        select A.ID,
        A.OFC_ID,
        A.DEP_ID,
        A.LOGIN_NAME,
        A.PASSWORD,
        A.USER_STATE,
        A.USER_NBR,
        A.USER_NAME,
        A.USER_GENDER,
        A.USER_EMAIL,
        A.USER_TEL,
        A.USER_MOBILE,
        A.USER_TYPE,
        A.USER_AVATAR_URL,
        A.ID_CARD,
        A.ID_CARD_FRONT_URL,
        A.ID_CARD_BACK_URL,
        A.LOGIN_IP,
        A.LOGIN_TIME,
        A.TOKEN,
        A.FLAG_LOGIN,
        A.FLAG_VALID,
        B.id officeId,
        B.parent_id,
        B.province_id,
        B.city_id,
        B.country_id,
        B.ofc_addr,
        B.ofc_code,
        B.ofc_name,
        B.sort_level,
        B.ofc_type,
        B.ofc_level,
        B.ofc_zip,
        B.ofc_tel,
        B.ofc_fax,
        B.ofc_email,
        B.contact_name,
        B.contact_tel,
        B.license_nbr,
        B.license_url,
        D.POST_NAME,
        D.ID POSTID,
        D.FLAG_VALID POST_FLAG_VALID,
        F.ID ROLEID,
        F.ROLE_NAME,
        H.ID PRIVID,
        H.PRIV_NAME,
        H.PRIV_TYPE,
        H.PRIV_URI,
        H.PARENT_ID PRIV_PARENT_ID,
        H.FLAG_LEAF,
        D.OFC_ID POST_OFC_ID,
        F.OFC_ID ROLE_OFC_ID,
        F.FLAG_VALID ROLE_FLAG_VALID
        from GW_SYS_USER A LEFT JOIN GW_SYS_OFFICE B
        ON A.OFC_ID = B.ID
        left join GW_SYS_REL_USER_POST C ON A.ID=C.USER_ID
        LEFT JOIN GW_SYS_POST D ON C.POST_ID=D.ID
        LEFT JOIN GW_SYS_REL_USER_ROLE E ON A.id=E.USER_ID
        LEFT JOIN GW_SYS_ROLE F ON E.ROLE_ID=F.ID
        LEFT JOIN GW_SYS_REL_ROLE_PRIV G ON F.ID=G.ROLE_ID
        LEFT JOIN GW_SYS_PRIV H ON G.PRIV_ID=H.ID
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="ofcId != null and ofcId != ''">and A.OFC_ID = #{ofcId}</if>
            <if test="depId != null and depId != ''">and A.DEP_ID = #{depId}</if>
            <if test="loginName != null and loginName != ''">and A.LOGIN_NAME LIKE '%'||#{loginName}||'%'</if>
            <if test="password != null and password != ''">and A.PASSWORD = #{password}</if>
            <if test="userState != null and userState != ''">and A.USER_STATE = #{userState}</if>
            <if test="userNbr != null and userNbr != ''">and A.USER_NBR = #{userNbr}</if>
            <if test="userName != null and userName != ''">and A.USER_NAME LIKE '%'||#{userName}||'%'</if>
            <if test="userGender != null and userGender != ''">and A.USER_GENDER = #{userGender}</if>
            <if test="userEmail != null and userEmail != ''">and A.USER_EMAIL = #{userEmail}</if>
            <if test="userTel != null and userTel != ''">and A.USER_TEL = #{userTel}</if>
            <if test="userMobile != null and userMobile != ''">and A.USER_MOBILE = #{userMobile}</if>
            <if test="userType != null and userType != ''">and A.USER_TYPE = #{userType}</if>
            <if test="userAvatarUrl != null and userAvatarUrl != ''">and A.USER_AVATAR_URL = #{userAvatarUrl}</if>
            <if test="idCard != null and idCard != ''">and A.ID_CARD = #{idCard}</if>
            <if test="idCardFrontUrl != null and idCardFrontUrl != ''">and A.ID_CARD_FRONT_URL = #{idCardFrontUrl}</if>
            <if test="idCardBackUrl != null and idCardBackUrl != ''">and A.ID_CARD_BACK_URL = #{idCardBackUrl}</if>
            <if test="loginIp != null and loginIp != ''">and A.LOGIN_IP = #{loginIp}</if>
            <if test="loginTime != null">and A.LOGIN_TIME = #{loginTime}</if>
            <if test="token != null and token != ''">and A.TOKEN = #{token}</if>
            <if test="flagLogin != null">and A.FLAG_LOGIN = #{flagLogin}</if>
            <if test="flagValid != null">and A.FLAG_VALID = #{flagValid}</if>
        </trim>
    </select>

    <!-- 分页查询用户（级联：机构、角色、岗位） -->
    <select id="findPageInfoListCount" resultType="Long" parameterType="cn.com.goldenwater.dcproj.param.UserParam">
        select count(A.ID)
        from GW_SYS_USER A
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="ofcId != null and ofcId != ''">and A.OFC_ID = #{ofcId}</if>
            <if test="depId != null and depId != ''">and A.DEP_ID = #{depId}</if>
            <if test="loginName != null and loginName != ''">and A.LOGIN_NAME LIKE '%'||#{loginName}||'%'</if>
            <if test="password != null and password != ''">and A.PASSWORD = #{password}</if>
            <if test="userState != null and userState != ''">and A.USER_STATE = #{userState}</if>
            <if test="userNbr != null and userNbr != ''">and A.USER_NBR = #{userNbr}</if>
            <if test="userName != null and userName != ''">and A.USER_NAME LIKE '%'||#{userName}||'%'</if>
            <if test="userGender != null and userGender != ''">and A.USER_GENDER = #{userGender}</if>
            <if test="userEmail != null and userEmail != ''">and A.USER_EMAIL = #{userEmail}</if>
            <if test="userTel != null and userTel != ''">and A.USER_TEL = #{userTel}</if>
            <if test="userMobile != null and userMobile != ''">and A.USER_MOBILE = #{userMobile}</if>
            <if test="userType != null and userType != ''">and A.USER_TYPE = #{userType}</if>
            <if test="userAvatarUrl != null and userAvatarUrl != ''">and A.USER_AVATAR_URL = #{userAvatarUrl}</if>
            <if test="idCard != null and idCard != ''">and A.ID_CARD = #{idCard}</if>
            <if test="idCardFrontUrl != null and idCardFrontUrl != ''">and A.ID_CARD_FRONT_URL = #{idCardFrontUrl}</if>
            <if test="idCardBackUrl != null and idCardBackUrl != ''">and A.ID_CARD_BACK_URL = #{idCardBackUrl}</if>
            <if test="loginIp != null and loginIp != ''">and A.LOGIN_IP = #{loginIp}</if>
            <if test="loginTime != null">and A.LOGIN_TIME = #{loginTime}</if>
            <if test="token != null and token != ''">and A.TOKEN = #{token}</if>
            <if test="flagLogin != null">and A.FLAG_LOGIN = #{flagLogin}</if>
            <if test="flagValid != null">and A.FLAG_VALID = #{flagValid}</if>
        </trim>
    </select>

    <!-- 分页查询用户（级联：机构、角色、岗位） -->
    <select id="findPageInfoList" resultMap="userDtoResultMap" parameterType="cn.com.goldenwater.dcproj.param.UserParam">
        select A.ID,
        A.OFC_ID,
        A.DEP_ID,
        A.LOGIN_NAME,
        A.PASSWORD,
        A.USER_STATE,
        A.USER_NBR,
        A.USER_NAME,
        A.USER_GENDER,
        A.USER_EMAIL,
        A.USER_TEL,
        A.USER_MOBILE,
        A.USER_TYPE,
        A.USER_AVATAR_URL,
        A.ID_CARD,
        A.ID_CARD_FRONT_URL,
        A.ID_CARD_BACK_URL,
        A.LOGIN_IP,
        A.LOGIN_TIME,
        A.TOKEN,
        A.FLAG_LOGIN,
        A.FLAG_VALID,
        B.id officeId,
        B.parent_id,
        B.province_id,
        B.city_id,
        B.country_id,
        B.ofc_addr,
        B.ofc_code,
        B.ofc_name,
        B.sort_level,
        B.ofc_type,
        B.ofc_level,
        B.ofc_zip,
        B.ofc_tel,
        B.ofc_fax,
        B.ofc_email,
        B.contact_name,
        B.contact_tel,
        B.license_nbr,
        B.license_url,
        D.POST_NAME,
        D.ID POSTID,
        D.FLAG_VALID POST_FLAG_VALID,
        F.ID ROLEID,
        F.ROLE_NAME,
        H.ID PRIVID,
        H.PRIV_NAME,
        H.PRIV_TYPE,
        H.PRIV_URI,
        H.PARENT_ID PRIV_PARENT_ID,
        H.FLAG_LEAF,
        D.OFC_ID POST_OFC_ID,
        F.OFC_ID ROLE_OFC_ID,
        F.FLAG_VALID ROLE_FLAG_VALID
        from (
          SELECT t1.*, ROWNUM RN from (
            SELECT * from GW_SYS_USER
                <trim prefix="where" prefixOverrides="and | or ">
                    <if test="ofcId != null and ofcId != ''">and A.OFC_ID = #{ofcId}</if>
                    <if test="depId != null and depId != ''">and A.DEP_ID = #{depId}</if>
                    <if test="loginName != null and loginName != ''">and A.LOGIN_NAME LIKE '%'||#{loginName}||'%'</if>
                    <if test="password != null and password != ''">and A.PASSWORD = #{password}</if>
                    <if test="userState != null and userState != ''">and A.USER_STATE = #{userState}</if>
                    <if test="userNbr != null and userNbr != ''">and A.USER_NBR = #{userNbr}</if>
                    <if test="userName != null and userName != ''">and A.USER_NAME LIKE '%'||#{userName}||'%'</if>
                    <if test="userGender != null and userGender != ''">and A.USER_GENDER = #{userGender}</if>
                    <if test="userEmail != null and userEmail != ''">and A.USER_EMAIL = #{userEmail}</if>
                    <if test="userTel != null and userTel != ''">and A.USER_TEL = #{userTel}</if>
                    <if test="userMobile != null and userMobile != ''">and A.USER_MOBILE = #{userMobile}</if>
                    <if test="userType != null and userType != ''">and A.USER_TYPE = #{userType}</if>
                    <if test="userAvatarUrl != null and userAvatarUrl != ''">and A.USER_AVATAR_URL = #{userAvatarUrl}</if>
                    <if test="idCard != null and idCard != ''">and A.ID_CARD = #{idCard}</if>
                    <if test="idCardFrontUrl != null and idCardFrontUrl != ''">and A.ID_CARD_FRONT_URL = #{idCardFrontUrl}</if>
                    <if test="idCardBackUrl != null and idCardBackUrl != ''">and A.ID_CARD_BACK_URL = #{idCardBackUrl}</if>
                    <if test="loginIp != null and loginIp != ''">and A.LOGIN_IP = #{loginIp}</if>
                    <if test="loginTime != null">and A.LOGIN_TIME = #{loginTime}</if>
                    <if test="token != null and token != ''">and A.TOKEN = #{token}</if>
                    <if test="flagLogin != null">and A.FLAG_LOGIN = #{flagLogin}</if>
                    <if test="flagValid != null">and A.FLAG_VALID = #{flagValid}</if>
                </trim>
          ) t1 where ROWNUM &lt;= #{to}
        ) A
        LEFT JOIN GW_SYS_OFFICE B
        ON A.OFC_ID = B.ID
        left join GW_SYS_REL_USER_POST C ON A.ID=C.USER_ID
        LEFT JOIN GW_SYS_POST D ON C.POST_ID=D.ID
        LEFT JOIN GW_SYS_REL_USER_ROLE E ON A.id=E.USER_ID
        LEFT JOIN GW_SYS_ROLE F ON E.ROLE_ID=F.ID
        LEFT JOIN GW_SYS_REL_ROLE_PRIV G ON F.ID=G.ROLE_ID
        LEFT JOIN GW_SYS_PRIV H ON G.PRIV_ID=H.ID
        where RN &gt; #{from}
    </select>
</mapper>