<?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.zcloud.mapper.dsno1.system.UsersMapper">

	<resultMap type="User" id="userAndRoleResultMap">
		<id column="USER_ID" property="USER_ID"/>
		<result column="USERNAME" property="USERNAME"/>
		<result column="PASSWORD" property="PASSWORD"/>
		<result column="NAME" property="NAME"/>
		<result column="LAST_LOGIN" property="LAST_LOGIN"/>
		<result column="IP" property="IP"/>
		<result column="STATUS" property="STATUS"/>
		<result column="SKIN" property="SKIN"/>
		<result column="ROLE_IDS" property="ROLE_IDS"/>
		<association property="role" column="ROLE_ID" javaType="Role">
			<id column="ROLE_ID" property="ROLE_ID"/>
			<result column="ROLE_NAME" property="ROLE_NAME"/>
			<result column="ROLE_RIGHTS" property="RIGHTS"/>
			<result column="ADD_QX" property="ADD_QX"/>
			<result column="DEL_QX" property="DEL_QX"/>
			<result column="EDIT_QX" property="EDIT_QX"/>
			<result column="CHA_QX" property="CHA_QX"/>
		</association>
	</resultMap>
	<resultMap type="User" id="userResultMap">
		<id column="USER_ID" property="USER_ID"/>
		<result column="USERNAME" property="USERNAME"/>
		<result column="PASSWORD" property="PASSWORD"/>
		<result column="NAME" property="NAME"/>
		<result column="LAST_LOGIN" property="LAST_LOGIN"/>
		<result column="IP" property="IP"/>
		<result column="STATUS" property="STATUS"/>
		<result column="ROLE_ID" property="ROLE_ID"/>
		<result column="SKIN" property="SKIN"/>
		<result column="ROLE_IDS" property="ROLE_IDS"/>
	</resultMap>

	<!--用户表名 -->
	<sql id="tableName">
		SYS_USER
	</sql>

	<!--角色表名 -->
	<sql id="roleTableName">
		SYS_ROLE
	</sql>

	<!-- 字段 -->
	<sql id="Field">
		USER_ID,
		USERNAME,
		PASSWORD,
		NAME,
		ROLE_ID,
		LAST_LOGIN,
		IP,
		STATUS,
		BZ,
		SKIN,
		EMAIL,
		NUMBER,
		PHONE,
		ROLE_IDS,
		DEPARTMENT_ID,
		POST_ID,
		ISMAIN,
<!-- 		CORPINFO_ID, -->
<!-- 		BASICINFO_ID, -->
		FUN_IDS,
		CREATCOUNT,
		ISAGENT,
		AGENTLEVEL,
		AGENTPROVINCE,
		AGENTCITY,
		AGENTCOUNTY,
		AGENTVILLAGE,
		AGENTSTREET,
		RIGHTS,
		ISDELETE,
		CORPINFO_ID,
		SEX,
		AGENCY_MANAGEMENT_ID
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{USER_ID},
		#{USERNAME},
		#{PASSWORD},
		#{NAME},
		#{ROLE_ID},
		#{LAST_LOGIN},
		#{IP},
		#{STATUS},
		#{BZ},
		#{SKIN},
		#{EMAIL},
		#{NUMBER},
		#{PHONE},
		#{ROLE_IDS},
		#{DEPARTMENT_ID},
		#{POST_ID},
		#{ISMAIN},
		#{FUN_IDS},
		#{CREATCOUNT},
		#{ISAGENT},
		#{AGENTLEVEL},
		#{AGENTPROVINCE},
		#{AGENTCITY},
		#{AGENTCOUNTY},
		#{AGENTVILLAGE},
		#{AGENTSTREET},
		#{RIGHTS},
		#{ISDELETE},
		#{CORPINFO_ID},
		#{SEX},
		#{AGENCY_MANAGEMENT_ID}

	</sql>

	<!-- 通过USERNAME获取数据 -->
	<select id="findByUsername" parameterType="pd" resultType="pd" >
		select
			<include refid="Field"></include>
		from
			<include refid="tableName"></include>
		where
			USERNAME = #{USERNAME} and ISDELETE = '0'
	</select>

	<select id="findByName" parameterType="pd" resultType="pd" >
		select
			<include refid="Field"></include>
		from
			<include refid="tableName"></include>
		where
			NAME = #{NAME} AND CORPINFO_ID = #{CORPINFO_ID}
	</select>
	<!-- 通过用户ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd" >
		select
			<include refid="Field"></include>
		from
			<include refid="tableName"></include>
		where
			USER_ID = #{USER_ID}
	</select>

	<select id="listbyId" parameterType="pd" resultType="pd" >
		select
		f.USER_ID,
		f.USERNAME,
		f.NAME
		from
		<include refid="tableName"></include> f
		 left join sys_role m on m.ROLE_ID = f.ROLE_ID
		where
		m.PARENT_ID = #{PARENT_ID} and f.ISDELETE = '0'
		<choose>
			<when test="AGENCY_MANAGEMENT_ID != null and AGENCY_MANAGEMENT_ID != ''">
				and f.AGENCY_MANAGEMENT_ID=#{AGENCY_MANAGEMENT_ID}
			</when>
			<otherwise>
				and f.AGENCY_MANAGEMENT_ID is null
			</otherwise>
		</choose>

	</select>

	<!-- 列出所有用户 -->
	<select id="findUserNameAll"  parameterType="pd" resultType="pd" >
		select USERNAME
		from
		<include refid="tableName"></include>
		where ISDELETE = '0'
		order by DEPARTMENT_ID,SORT
	</select>

	<!-- 通过企业ID获取数据 -->
	<select id="findByCorpId" parameterType="pd" resultType="pd" >
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include>
		where CORPINFO_ID in
		<foreach item="item" index="index"
				 collection="corpinfoIds" open="(" separator="," close=")">
			#{item}
		</foreach>
		and ISMAIN = '1'
	</select>

	<!-- 通过邮箱获取数据 -->
	<select id="findByEmail" parameterType="pd" resultType="pd" >
		select
			<include refid="Field"></include>
		from
			<include refid="tableName"></include>
		where
			EMAIL = #{EMAIL}
		<if test="USERNAME != null and USERNAME != ''">
			and USERNAME != #{USERNAME}
		</if>
	</select>

	<!-- 通过编码获取数据 -->
	<select id="findByNumbe" parameterType="pd" resultType="pd" >
		select
			<include refid="Field"></include>
		from
			<include refid="tableName"></include>
		where
			NUMBER = #{NUMBER}
		<if test="USERNAME != null and USERNAME != ''">
			and USERNAME != #{USERNAME}
		</if>
	</select>

	<!-- 列出某角色下的所有用户 -->
	<select id="listAllUserByRoldId"  parameterType="pd" resultType="pd" >
		select USER_ID
		from
		<include refid="tableName"></include>
		where
		ROLE_ID = #{ROLE_ID}
	</select>

	<!-- 新增用户 -->
	<insert id="saveUser" parameterType="pd" >
		insert into <include refid="tableName"></include> (
			<include refid="Field"></include>
		) values (
			<include refid="FieldValue"></include>
		)
	</insert>

	<!-- 修改 -->
	<update id="editUser" parameterType="pd" >
		update  <include refid="tableName"></include>
			set NAME 					= #{NAME},
				DEPARTMENT_ID	= #{DEPARTMENT_ID},
				POST_ID				=#{POST_ID},
				ROLE_ID 				= #{ROLE_ID},
				ROLE_IDS 				= #{ROLE_IDS},
				BZ						= #{BZ},
				EMAIL					= #{EMAIL},
				NUMBER				= #{NUMBER},
				SEX                    =#{SEX},
				PHONE					= #{PHONE},
				CREATCOUNT					= #{CREATCOUNT},
				AGENTLEVEL = #{AGENTLEVEL},
				AGENTPROVINCE = #{AGENTPROVINCE},
				AGENTCITY = #{AGENTCITY},
				AGENTCOUNTY = #{AGENTCOUNTY},
				AGENTVILLAGE = #{AGENTVILLAGE},
				AGENTSTREET = #{AGENTSTREET}
			<if test="PASSWORD != null and PASSWORD != ''">
				,PASSWORD				= #{PASSWORD}
			</if>
		where
				USER_ID = #{USER_ID}
	</update>
	<update id="editPassword" parameterType="pd" >
		update  <include refid="tableName"></include>
			set PASSWORD				= #{PASSWORD}
		where
				USER_ID = #{USER_ID}
	</update>

	<!-- 用户列表 -->
	<select id="userlistPage" parameterType="page" resultType="pd" >
		select  u.USER_ID,
				u.USERNAME,
				u.PASSWORD,
				u.LAST_LOGIN,
				u.NAME,
				u.IP,
				u.EMAIL,
				u.NUMBER,
				s.NAME AS SEX_NAME,
				u.PHONE,
				u.CREATCOUNT,
				r.ROLE_ID,
				r.ROLE_NAME,
				(select a.AGENCY_NAME from `integrated_whb`.bus_agency_management a where u.AGENCY_MANAGEMENT_ID = a.AGENCY_MANAGEMENT_ID and a.ISDELETE = '0')
				as AGENCY_NAME
		from <include refid="tableName"></include> u
		left join SYS_ROLE r on u.ROLE_ID = r.ROLE_ID
		left join `integrated_whb`.bus_agency_management a on a.AGENCY_MANAGEMENT_ID = u.AGENCY_MANAGEMENT_ID
		left join SYS_DICTIONARIES s on s.DICTIONARIES_ID = u.SEX
		where
		u.USERNAME != 'admin'
		and ( u.CORPINFO_ID is null or u.CORPINFO_ID = '') and u.ISDELETE = '0' and u.RIGHTS = '3245474264'   and r.ROLE_ID != '62b427ef7ebe461299878c801a6dfb2e'
		<!-- <if test="pd.PARENT_ID != null and pd.PARENT_ID != ''">
			and r.PARENT_ID=#{pd.PARENT_ID}
		</if> -->
		<if test="pd.AGENCY_MANAGEMENT_ID != null and pd.AGENCY_MANAGEMENT_ID != ''">
			and u.AGENCY_MANAGEMENT_ID=#{pd.AGENCY_MANAGEMENT_ID}
		</if>

		<if test="pd.KEYWORDS!= null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
					 u.USERNAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					 u.EMAIL LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					 u.NUMBER LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					 u.NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					 u.PHONE LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					or
					a.AGENCY_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
				)
		</if>
		<if test="pd.ROLE_ID != null and pd.ROLE_ID != ''">		<!-- 角色检索 -->
			and u.ROLE_ID=#{pd.ROLE_ID}
		</if>
		<if test="pd.STRARTTIME!=null and pd.STRARTTIME!=''">	<!-- 登录时间检索 -->
			and u.LAST_LOGIN &gt;= #{pd.STRARTTIME}
		</if>
		<if test="pd.ENDTIME!=null and pd.ENDTIME!=''">		 	<!-- 登录时间检索 -->
			and u.LAST_LOGIN &lt;= #{pd.ENDTIME}
		</if>
		<if test="pd.CORPINFO_ID!=null and pd.CORPINFO_ID!=''">		 	<!-- 登录时间检索 -->
			and u.CORPINFO_ID =#{pd.CORPINFO_ID}
		</if>
		order by u.LAST_LOGIN desc
	</select>

	<!-- 用户列表(全部) -->
	<select id="listAllUser" parameterType="pd" resultType="pd" >
		select  u.USER_ID,
				u.USERNAME,
				u.PASSWORD,
				u.LAST_LOGIN,
				u.NAME,
				u.IP,
				u.EMAIL,
				u.NUMBER,
				u.PHONE,
				u.CREATCOUNT,
				r.ROLE_ID,
				r.ROLE_NAME
		from <include refid="tableName"></include> u, <include refid="roleTableName"></include> r
		where u.ROLE_ID = r.ROLE_ID
		and u.USERNAME != 'admin'
<!-- 		and r.PARENT_ID = '1' -->
		<if test="KEYWORDS != null and KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
					 u.USERNAME LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					 or
					 u.EMAIL LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					 or
					 u.NUMBER LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					 or
					 u.NAME LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					 or
					 u.PHONE LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
				)
		</if>
		<if test="ROLE_ID != null and ROLE_ID != ''"><!-- 角色检索 -->
			and u.ROLE_ID=#{ROLE_ID}
		</if>
		<if test="STRARTTIME!=null and STRARTTIME!=''"><!-- 登录时间检索 -->
			and u.LAST_LOGIN &gt;= #{STRARTTIME}
		</if>
		<if test="ENDTIME!=null and ENDTIME!=''"><!-- 登录时间检索 -->
			and u.LAST_LOGIN &lt;= #{ENDTIME}
		</if>
		<if test="CORPINFO_ID != null and CORPINFO_ID != ''"><!-- 关键词检索 -->
			AND
				u.CORPINFO_ID = #{CORPINFO_ID}
		</if>
		<if test="DEPARTMENT_ID != null and DEPARTMENT_ID != ''"><!-- 关键词检索 -->
			AND
				u.DEPARTMENT_ID = #{DEPARTMENT_ID}
		</if>
		<if test="POST_ID != null and POST_ID != ''"><!-- 关键词检索 -->
			AND
				u.POST_ID = #{POST_ID}
		</if>
		order by u.LAST_LOGIN desc
	</select>

	<select id="listAll" parameterType="pd" resultType="pd" >
        select u.*
        from <include refid="tableName"></include> u
        where u.ISDELETE = 0
        AND u.CORPINFO_ID = #{CORPINFO_ID}
    </select>

	<!-- 通过用户ID获取用户信息和角色信息 -->
	<select id="getUserAndRoleById" parameterType="String" resultMap="userAndRoleResultMap">
		select  u.USER_ID,
				u.USERNAME,
				u.NAME,
				u.PASSWORD,
				u.SKIN,
				u.NUMBER,
				u.ROLE_IDS,
				r.ROLE_ID,
				r.ROLE_NAME,
				r.RIGHTS as ROLE_RIGHTS,
				r.ADD_QX,
				r.DEL_QX,
				r.EDIT_QX,
				r.CHA_QX
		from
		<include refid="tableName"></include> u
		left join
		<include refid="roleTableName"></include> r
		on u.ROLE_ID=r.ROLE_ID
		where u.STATUS=0
		and u.USER_ID=#{USER_ID}
	</select>

	<!-- 存入IP -->
	<update id="saveIP" parameterType="pd" >
		update
		<include refid="tableName"></include>
		set
			IP = #{IP},
			LAST_LOGIN = #{LAST_LOGIN}
		where
			USERNAME = #{USERNAME}
	</update>

	<!-- 保存用户皮肤 -->
	<update id="saveSkin" parameterType="pd" >
		update
		<include refid="tableName"></include>
		set
			SKIN = #{SKIN}
		where USERNAME = #{USERNAME}
	</update>

	<update id="deleteUser" parameterType="pd" >
		update
		<include refid="tableName"></include>
		set
		ISDELETE = '1'
		where USER_ID = #{USER_ID}
		and
		USER_ID != '1'
	</update>

	<delete id="deleteUsers" parameterType="pd">
		update
		<include refid="tableName"></include>
		set ISDELETE = 1
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</delete>
	<!-- 批量删除用户 -->
	<delete id="deleteAllUser" parameterType="String" >
		update
		<include refid="tableName"></include>
		set
		ISDELETE = '1'
		where
			USER_ID in
  			 <foreach item="item" index="index" collection="array" open="(" separator="," close=")">
                  #{item}
             </foreach>
            and
				USER_ID != '1'
	</delete>

	<!-- 用户列表(弹窗选择用) -->
	<select id="userBystafflistPage" parameterType="page" resultType="pd" >
		select  u.USER_ID,
				u.USERNAME,
				u.PASSWORD,
				u.LAST_LOGIN,
				u.NAME,
				u.IP,
				u.EMAIL,
				u.NUMBER,
				u.PHONE,
				r.ROLE_ID,
				r.ROLE_NAME
		from <include refid="tableName"></include> u, <include refid="roleTableName"></include> r
		where u.ROLE_ID = r.ROLE_ID
		and u.USERNAME != 'admin'
		and r.PARENT_ID = '1'
		<if test="pd.KEYWORDS!= null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
					 u.USERNAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					 u.EMAIL LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					 u.NUMBER LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					 u.NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					 or
					 u.PHONE LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
				)
		</if>
		<if test="pd.ROLE_ID != null and pd.ROLE_ID != ''"><!-- 角色检索 -->
			and u.ROLE_ID=#{pd.ROLE_ID}
		</if>
		<if test="pd.STRARTTIME!=null and pd.STRARTTIME!=''">	<!-- 登录时间检索 -->
			and u.LAST_LOGIN &gt;= #{pd.STRARTTIME}
		</if>
		<if test="pd.ENDTIME!=null and pd.ENDTIME!=''">		 	<!-- 登录时间检索 -->
			and u.LAST_LOGIN &lt;= #{pd.ENDTIME}
		</if>
		order by u.LAST_LOGIN desc
	</select>

	<!--设置小程序菜单 -->
	<update id="editUserFuns" parameterType="pd" >
		update  <include refid="tableName"></include>
			set FUN_IDS = #{FUN_IDS}
		where
				USER_ID = #{USER_ID}
	</update>

	<!-- 查询小程序用户 -->
	<select id="findAppUserById" parameterType="pd" resultType="pd" >
		select
			f.ID,
			f.NAME,
			f.APPID,
			f.ISAGREEPROTOCOL,
			f.USER_TYPE,
			bw.BALANCE,
			bp.POINTS
		from
			user_view f
			left join bus_wallet bw on bw.ISDELETE = '0' and bw.USER_ID = f.APPID and bw.USER_TYPE = f.USER_TYPE
			left join bus_points bp on bp.ISDELETE = '0' and bp.USER_ID = f.APPID
		where
			f.APPID = #{APPID}
		<if test="USER_TYPE!=null and USER_TYPE!=''">		 	<!-- 登录时间检索 -->
			and f.USER_TYPE = #{USER_TYPE}
		</if>
	</select>

	<!--用户同意协议 -->
	<update id="agreeProtocol" parameterType="pd" >
		update
			sys_user
		SET
			ISAGREEPROTOCOL = '1'
		where
			APPID = #{APPID}
	</update>

		<!-- 获取企业的主账号 -->
	<select id="findCorpMain" parameterType="page" resultType="pd" >
		select
			<include refid="Field"></include>
		from
			<include refid="tableName"></include>
		where
			ISMAIN = '1'
			and CORPINFO_ID =#{CORPINFO_ID}
			and STATUS = '0'
	</select>

		<!-- 获取企业的主账号(及分组id) -->
	<select id="findCorpMainUser" parameterType="pd" resultType="pd" >
		select
			f.USER_ID,
			f.USERNAME,
			f.PASSWORD,
			f.NAME,
			f.ROLE_ID,
			f.LAST_LOGIN,
			f.IP,
			f.STATUS,
			f.BZ,
			f.SKIN,
			f.EMAIL,
			f.NUMBER,
			f.PHONE,
			f.ROLE_IDS,
			f.DEPARTMENT_ID,
			f.POST_ID,
			f.ISMAIN,
	<!-- 		CORPINFO_ID, -->
	<!-- 		BASICINFO_ID, -->
			f.FUN_IDS,
			f.SORT,
			f.LEARNERCATEGORY,
			f.USERAVATARPREFIX,
			f.USERAVATARURL,
			f.SHIFTDUTYONE,
			f.SHIFTDUTYTWO,
			f.DURATION,
			f.WORKSTATUS,
			f.WORKPERIOD,
			f.CORPINFO_ID,
			r.PARENT_ID ROLE_GROUP_ID
		from
			<include refid="tableName"></include> f
			left join sys_role r on f.ROLE_ID = r.ROLE_ID
		where
			f.ISMAIN = '1'
			and f.CORPINFO_ID =#{CORPINFO_ID}
			and f.STATUS = '0'
	</select>


	<!-- 修改公司名称时同步修改企业账号 -->
	<update id="updateCornUser" parameterType="pd" >
		update
		<include refid="tableName"></include>
		set
			USERNAME = #{USERNAME},
			NAME = #{NAME},
			PASSWORD = #{PASSWORD}
		where
			USERNAME = #{OLDUSERNAME}
			and
			CORPINFO_ID = #{CORPINFO_ID}
	</update>
	<!-- 获取人员Cascader数据(人员ID，姓名，部门ID，部门级别) -->
	<select id="listCascader" parameterType="pd" resultType="pd" >
		select
		u.USER_ID,
		u.NAME USERNAME,
		u.LEARNERCATEGORY,
		d.NAME DEPARTMENTNAME,
		d.LEVEL
		from
		<include refid="tableName"></include> u
		left join OA_DEPARTMENT d on d.DEPARTMENT_ID = u.DEPARTMENT_ID
		where
		u.CORPINFO_ID = #{CORPINFO_ID}
		and u.STATUS = '0'
		<if test="BIANMA != null and BIANMA != ''"><!-- 部门级别 -->
			and d.LEVEL = #{BIANMA}
			<if test="BIANMA == 'departmentLevel0001'"><!-- 部门级别 -->
				and d.PARENT_ID = '0'
			</if>
		</if>
		<if test="DEPARTMENT_ID != null and DEPARTMENT_ID != ''"><!-- 部门ID -->
			and u.DEPARTMENT_ID = #{DEPARTMENT_ID}
		</if>
		<if test="LEARNERCATEGORY != null and LEARNERCATEGORY != ''"><!-- 关键词检索 -->
			and u.LEARNERCATEGORY = #{LEARNERCATEGORY}
		</if>
		order by u.SORT
	</select>

		<!--修改用户角色 -->
	<update id="updateUserRole" parameterType="pd" >
		update
			sys_user
		SET
			ROLE_ID = #{ROLE_ID}
		where
			CORPINFO_ID = #{CORPINFO_ID}
			and ISMAIN = #{ISMAIN}
	</update>

	<!--根据机构id查询机构下人数-->
	<select id="getUserCountByAgencyId" parameterType="pd" resultType="Integer">
		select
		    count(su.USER_ID) as userCount
		from
		    sys_user su
		where su.ISDELETE = 0 and su.ROLE_ID != '62b427ef7ebe461299878c801a6dfb2e' and su.AGENCY_MANAGEMENT_ID = #{AGENCY_MANAGEMENT_ID}
	</select>

	<!-- 项目负责人管理-开始部署-实施人员下拉列表-->
    <select id="getImplementPersonList" resultType="pd" parameterType="pd">
		<choose>
			<when test=" WHETHER_PRE_SALE_SERVICE != '' and WHETHER_PRE_SALE_SERVICE != null ">
				<if test='WHETHER_PRE_SALE_SERVICE == "0"'><!--  从代理机构中选择实施人员  -->
					select
					su.USER_ID,
					su.USERNAME,
					su.NAME
					from SYS_USER su, SYS_ROLE sr
					where su.ISDELETE = '0' and su.ROLE_ID = sr.ROLE_ID
					and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')

					and  su.AGENCY_MANAGEMENT_ID = #{AGENCY_MANAGEMENT_ID}
				</if>
				<if test='WHETHER_PRE_SALE_SERVICE == "1"'><!--  从平台选择实施人员  -->
					select
					su.USER_ID,
					su.USERNAME,
					su.NAME
					from SYS_USER su ,SYS_ROLE sr
					where (su.CORPINFO_ID is null or su.CORPINFO_ID = '')
					and (su.AGENCY_MANAGEMENT_ID is null or su.AGENCY_MANAGEMENT_ID = '')
					and su.ROLE_ID = sr.ROLE_ID
					and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')

					and su.ISDELETE = '0'
				</if>
			</when>
			<otherwise>
				select
				su.USER_ID,
				su.USERNAME,
				su.NAME
				from SYS_USER su ,SYS_ROLE sr
				where (su.CORPINFO_ID is null or su.CORPINFO_ID = '')
				and (su.AGENCY_MANAGEMENT_ID is null or su.AGENCY_MANAGEMENT_ID = '')
				and su.ROLE_ID = sr.ROLE_ID
				and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')

				and su.ISDELETE = '0'
			</otherwise>
		</choose>
	</select>

	<!--项目负责人管理-交付-获取运维人员下拉列表-->
    <select id="getOperationPersonList" resultType="pd" parameterType="pd">
		<choose>
			<when test=" WHETHER_AFTER_SALES_SERVICE != '' and WHETHER_AFTER_SALES_SERVICE != null ">
				<if test='WHETHER_AFTER_SALES_SERVICE == "0"'><!--  从代理机构中选择运维人员  -->
					select
					su.USER_ID,
					su.USERNAME,
					su.NAME
					from SYS_USER su, SYS_ROLE sr
					where su.ISDELETE = '0' and su.ROLE_ID = sr.ROLE_ID
					and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')

					and  su.AGENCY_MANAGEMENT_ID = #{AGENCY_MANAGEMENT_ID}
				</if>
				<if test='WHETHER_AFTER_SALES_SERVICE == "1"'><!--  从平台选择运维人员  -->
					select
					su.USER_ID,
					su.USERNAME,
					su.NAME
					from SYS_USER su ,SYS_ROLE sr
					where (su.CORPINFO_ID is null or su.CORPINFO_ID = '')
					and (su.AGENCY_MANAGEMENT_ID is null or su.AGENCY_MANAGEMENT_ID = '')
					and su.ROLE_ID = sr.ROLE_ID
					and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')
					and su.ISDELETE = '0'
				</if>
			</when>
			<otherwise>
				select
				su.USER_ID,
				su.USERNAME,
				su.NAME
				from SYS_USER su ,SYS_ROLE sr
				where (su.CORPINFO_ID is null or su.CORPINFO_ID = '')
				and (su.AGENCY_MANAGEMENT_ID is null or su.AGENCY_MANAGEMENT_ID = '')
				and su.ROLE_ID = sr.ROLE_ID
				and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')
				and su.ISDELETE = '0'
			</otherwise>
		</choose>
	</select>

    <!--删除机构账号-->
	<update id="deleteAgencyAccountByAgencyId" parameterType="pd">
		update
			sys_user
		set
			ISDELETE = 1
		where ROLE_ID = '62b427ef7ebe461299878c801a6dfb2e' and AGENCY_MANAGEMENT_ID = #{AGENCY_MANAGEMENT_ID}
	</update>

	<select id="listUserDeptPostlist" parameterType="pd" resultType="pd" >
		select
			u.USER_ID,
			u.NAME USERNAME,
			d.NAME DEPARTNAME,
			getFullName(d.DEPARTMENT_ID) DEPARTNAME_ALL,
			p.NAME POSTNAME
		from
			sys_user u
		left join oa_department d on d.DEPARTMENT_ID = u.DEPARTMENT_ID
		left join sys_post p on p.POST_ID = u.POST_ID and p.ISDELETE = '0' and p.STATUS = '0'
		where
			u.ISDELETE = 0 and
			u.CORPINFO_ID =#{CORPINFO_ID}
			and u.STATUS = '0'
	</select>

	<insert id="batchUpdate" parameterType="pd">
		INSERT INTO
		<include refid="tableName"></include>
		(
		USER_ID,
		PLS_ID,
		WORK_ID
		) VALUES
		<foreach collection="list" item="item" separator=",">
		    (
		    #{item.USER_ID},
		    #{item.PLS_ID},
		    #{item.WORK_ID}
		    )
		</foreach>
		ON DUPLICATE KEY UPDATE
		PLS_ID = VALUES(PLS_ID),
		WORK_ID = VALUES(WORK_ID)
	</insert>

	<update id="updateGeneralUserRole" parameterType="pd" >
		update	<include refid="tableName"></include> u
		left join sys_role sr on sr.ROLE_ID = u.ROLE_ID
		set u.ROLE_ID = (select ROLE_ID from sys_role sr2 where sr2.PARENT_ID = #{PARENT_ID} and sr2.ROLE_NAME = sr.ROLE_NAME)
		where u.CORPINFO_ID = #{CORPINFO_ID}
		and u.ISMAIN = #{ISMAIN}
	</update>
</mapper>
