<?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.cnpc.oms2.qual.apply.mapper.QualApplyExternalUserMapper">
  <select id="showExternalUser" parameterType="hashmap" resultType="hashmap">
    SELECT exUser.PK_USEREXTEND_CHANGE_ID,exUser.EMPLOYEE_ID,exUser.ENT_NAME,exUser.NAME,exUser.SERVER_OILFIELD,exUser.REQBOOK_NUM,exUser.IS_VALID,exUser.BSFLAG,exUser.CREATE_USER,exUser.UPDATE_USER,exUser.CREATE_DATE,exUser.UPDATE_DATE,exUser.ORG_CODE,sysUser.USER_NAME as USER_NAME,sysUser.START_DATE+numtodsinterval(sysUser.VALID_DATES,‘day‘) as END_DATE
    FROM QUAL_BASE_USEREXTEND_CHANGE exUser
    LEFT JOIN OMS_SYS_USER sysUser
    ON exUser.EMPLOYEE_ID = sysUser.EMPLOYEE_ID  
    <where>
        <if test="entName !=null and  entName != ‘‘ ">  
            exUser.ENT_NAME like CONCAT(CONCAT(‘%‘,#{entName}),‘%‘)
        </if>
        <if test="userName !=null and  userName != ‘‘ ">  
            AND sysUser.USER_NAME like CONCAT(CONCAT(‘%‘,#{userName}),‘%‘)
        </if>
        <if test="loginName !=null and  loginName != ‘‘ ">  
            AND exUser.NAME like CONCAT(CONCAT(‘%‘,#{loginName}),‘%‘)
        </if>
        
        <if test="reportUnit !=null and  reportUnit != ‘‘ ">  
            AND exUser.SERVER_OILFIELD like CONCAT(CONCAT(‘%‘,#{reportUnit}),‘%‘)
        </if>
        <if test="reportNum !=null and  reportNum != ‘‘ ">  
            AND exUser.REQBOOK_NUM like CONCAT(CONCAT(‘%‘,#{reportNum}),‘%‘)
        </if>
        
        <if test="startDate !=null and startDate !=‘‘">  
            AND TO_CHAR(sysUser.START_DATE,‘YYYY-mm-dd‘) &gt;= #{startDate}
        </if>
        <if test="endDate !=null and endDate !=‘‘">  
            AND TO_CHAR(sysUser.START_DATE+numtodsinterval(sysUser.VALID_DATES,‘day‘),‘YYYY-mm-dd‘) &lt;= #{endDate}
        </if>
        AND    
        exUser.ORG_CODE like CONCAT(CONCAT(‘%‘,#{orgCode}),‘%‘)
        <if test="entId !=null and entId != ‘‘ ">  
            OR a.ENT_ID like CONCAT(CONCAT(‘%‘,#{entId}),‘%‘)
        </if>
        AND exUser.BSFLAG = ‘0‘ 
    </where>
    ORDER BY 
        <if test="isSort == ‘yes‘">
              <trim suffixOverrides=",">
                  
              </trim>
          </if> 
          <if  test="isSort == ‘no‘">
              END_DATE DESC,exUser.SERVER_OILFIELD
          </if>  
  </select>
  
</mapper>