<?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.shanghaichengdi.ghjgitem.mapper.SysUserMapper">

    <resultMap id="BaseResultMap" type="com.shanghaichengdi.ghjgitem.domain.SysUser">
            <id property="id" column="ID" jdbcType="DECIMAL"/>
            <result property="userid" column="USERID" jdbcType="VARCHAR"/>
            <result property="username" column="USERNAME" jdbcType="VARCHAR"/>
            <result property="description" column="DESCRIPTION" jdbcType="VARCHAR"/>
            <result property="password" column="PASSWORD" jdbcType="VARCHAR"/>
            <result property="salt" column="SALT" jdbcType="VARCHAR"/>
            <result property="email" column="EMAIL" jdbcType="VARCHAR"/>
            <result property="mobile" column="MOBILE" jdbcType="VARCHAR"/>
            <result property="status" column="STATUS" jdbcType="VARCHAR"/>
            <result property="maxOnline" column="MAX_ONLINE" jdbcType="DECIMAL"/>
            <result property="timeout" column="TIMEOUT" jdbcType="DECIMAL"/>
            <result property="validTimeBegin" column="VALID_TIME_BEGIN" jdbcType="TIMESTAMP"/>
            <result property="validTimeEnd" column="VALID_TIME_END" jdbcType="TIMESTAMP"/>
            <result property="createTime" column="CREATE_TIME" jdbcType="TIMESTAMP"/>
            <result property="updateTime" column="UPDATE_TIME" jdbcType="TIMESTAMP"/>
            <result property="deleted" column="DELETED" jdbcType="DECIMAL"/>
            <result property="available" column="AVAILABLE" jdbcType="DECIMAL"/>
            <result property="accessIpRestrict" column="ACCESS_IP_RESTRICT" jdbcType="DECIMAL"/>
            <result property="admin" column="ADMIN" jdbcType="DECIMAL"/>
            <result property="passValidTime" column="PASS_VALID_TIME" jdbcType="TIMESTAMP"/>
            <result property="passErrorCount" column="PASS_ERROR_COUNT" jdbcType="DECIMAL"/>
            <result property="property3" column="PROPERTY3" jdbcType="VARCHAR"/>
            <result property="property4" column="PROPERTY4" jdbcType="VARCHAR"/>
            <result property="property5" column="PROPERTY5" jdbcType="VARCHAR"/>
            <result property="property6" column="PROPERTY6" jdbcType="VARCHAR"/>
            <result property="property7" column="PROPERTY7" jdbcType="VARCHAR"/>
            <result property="property8" column="PROPERTY8" jdbcType="VARCHAR"/>
            <result property="property9" column="PROPERTY9" jdbcType="VARCHAR"/>
            <result property="property10" column="PROPERTY10" jdbcType="VARCHAR"/>
            <result property="property1" column="PROPERTY1" jdbcType="VARCHAR"/>
            <result property="property2" column="PROPERTY2" jdbcType="VARCHAR"/>
            <result property="caAccessKey" column="CA_ACCESS_KEY" jdbcType="VARCHAR"/>
            <result property="groupIds" column="GROUP_IDS" jdbcType="VARCHAR"/>
            <result property="organizationIds" column="ORGANIZATION_IDS" jdbcType="VARCHAR"/>
            <result property="organizationName" column="ORGANIZATION_NAME" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        ID,USERID,USERNAME,
        DESCRIPTION,PASSWORD,SALT,
        EMAIL,MOBILE,STATUS,
        MAX_ONLINE,TIMEOUT,VALID_TIME_BEGIN,
        VALID_TIME_END,CREATE_TIME,UPDATE_TIME,
        DELETED,AVAILABLE,ACCESS_IP_RESTRICT,
        ADMIN,PASS_VALID_TIME,PASS_ERROR_COUNT,
        PROPERTY3,PROPERTY4,PROPERTY5,
        PROPERTY6,PROPERTY7,PROPERTY8,
        PROPERTY9,PROPERTY10,PROPERTY1,
        PROPERTY2,CA_ACCESS_KEY,GROUP_IDS,
        ORGANIZATION_IDS,ORGANIZATION_NAME
    </sql>
  <select id="selectUserInfo" resultType="com.shanghaichengdi.ghjgitem.vo.SysUserVo">
    select T.id , T.userid , T.username,T.description, T.password, T.salt, T.email, T.mobile, T.status,T.create_time,
    T.update_time, deleted, T.available,
    T.group_ids, T.organization_ids, T.organization_name from GHJG_BASICS.SYS_USER T
    left join GHJG_BASICS.SYS_GROUP T1 on T.ORGANIZATION_IDS = T1.ID
    <where>
      <if test="id != '' and id != null">
        and T.ID = #{id}
      </if>
      <if test="userId != '' and userId != null">
        and T.USERID = #{userId}
      </if>
      <if test="userName != '' and userName != null">
        and T.USERNAME like '%' || #{userName} || '%'
      </if>
      <if test="orgId != '' and orgId != null">
        and T.Organization_Ids in (select id from SYS_GROUP tree start with tree.ID = #{orgId}
        connect by prior tree.id = tree.PARENT_ID)
      </if>
    </where>
  </select>
    <select id="selectInspectedManagerUser"
            resultType="com.shanghaichengdi.ghjgitem.vo.SyInspectedManageUserVo">
        select s.license_number certNumber,m.CNAME userName, m.POST,m.CERTIFICATE_ID certificateId,m.TO_SCOPE toScope
        from GHJG_PORT_MANAGE.SY_INSPECT_MANAGER M
                 left join GHJG_PORT_MANAGE.SY_INSPECT S ON M.SY_CODE = S.CODE
                 LEFT JOIN GHJG_BASICS.Vw_Cret_Info_Self_All infoall ON S.LICENSE_NUMBER = INFOALL.cret_numer
        WHERE S.CODE IS NOT NULL
          AND info_stats != '注销'
          and LICENSE_NUMBER in (select cret_numer
                                 from GHJG_BASICS.Vw_Cret_Info_Self_All infoall
                                 where (infoall.JUDGMENT like '%上海市浦东新区建设和交通委员会%')
                                   and infoall.cname not in ('上海新洋山集装箱运输有限公司'))
         <if test="companyName != null and companyName != ''">
             and s.cname like '%'||#{companyName}||'%'
         </if>
         <if test="certNumber != null and certNumber != ''">
            and s.LICENSE_NUMBER like '%'||#{certNumber}||'%'
         </if>
    </select>
</mapper>
