<?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.hotent.uc.dao.OrgDao">
	<resultMap id="BaseResultMap" type="com.hotent.uc.model.Org">
		<id property="id" column="ID_" jdbcType="VARCHAR"/>
		<result property="name" column="NAME_" jdbcType="VARCHAR"/>
		<result property="parentId" column="PARENT_ID_" jdbcType="VARCHAR"/>
		<result property="orderNo" column="ORDER_NO_" jdbcType="NUMERIC"/>
		<result property="code" column="CODE_" jdbcType="VARCHAR"/>
		<result property="grade" column="GRADE_" jdbcType="VARCHAR"/>
		<result property="demId" column="DEM_ID_" jdbcType="VARCHAR"/>
		<result property="path" column="PATH_" jdbcType="VARCHAR"/>
		<result property="pathName" column="PATH_NAME_" jdbcType="VARCHAR"/>
		<result property="isMaster" column="IS_MASTER_" jdbcType="NUMERIC"/>
		<result property="updateTime" column="UPDATE_TIME_" jdbcType="TIMESTAMP"/>
		<result property="isDelete" column="IS_DELE_" jdbcType="VARCHAR"/>
		<result property="version" column="VERSION_" jdbcType="NUMERIC"/>
        <result property="limitNum" column="LIMIT_NUM_" jdbcType="NUMERIC"/>
        <result property="nowNum" column="NOW_NUM_" jdbcType="NUMERIC"/>
        <result property="exceedLimitNum" column="EXCEED_LIMIT_NUM_" jdbcType="NUMERIC"/>
        <result property="createBy" column="create_by_" jdbcType="VARCHAR"/>
		<result property="createTime" column="create_time_" jdbcType="TIMESTAMP"/>
		<result property="createOrgId" column="create_org_id_" jdbcType="VARCHAR"/>
		<result property="updateBy" column="update_by_" jdbcType="VARCHAR"/>
		<result property="orgType" column="ORG_TYPE_" jdbcType="NUMERIC"/>
		<result property="superiorOrgId" column="SUPERIOR_ORG_ID_" jdbcType="VARCHAR"/>
		<result property="superiorDepId" column="SUPERIOR_DEP_ID_" jdbcType="VARCHAR"/>
	</resultMap>

	<parameterMap id="Org" type="com.hotent.uc.model.Org" />

	<sql id="Base_Column_List">
		ID_,NAME_,PARENT_ID_,ORDER_NO_,CODE_,GRADE_,DEM_ID_,PATH_,PATH_NAME_,UPDATE_TIME_,IS_DELE_,VERSION_,LIMIT_NUM_,NOW_NUM_,EXCEED_LIMIT_NUM_,create_by_,create_time_,create_org_id_,update_by_,ORG_TYPE_,SUPERIOR_ORG_ID_,SUPERIOR_DEP_ID_,tenant_id_
	</sql>


	<select id="get"   parameterType="java.lang.String" resultMap="BaseResultMap">
		select  o.* ,
( case (select count(*)  from UC_ORG p where p.PARENT_ID_=o.ID_ and p.ID_!=p.PARENT_ID_  AND P.IS_DELE_!='1') when 0 then 0 else 1 end )isIsParent
		from  UC_ORG o
		where 1=1 and o.ID_ = #{id}  AND o.IS_DELE_!='1'
	</select>

	<select id="getByCode"   parameterType="java.lang.String" resultMap="BaseResultMap">
		SELECT
		<include refid="Base_Column_List" />
		FROM UC_ORG
		WHERE
		CODE_=#{code} AND IS_DELE_!='1'
	</select>

	<select id="getOrgListByUserId"   parameterType="java.lang.String" resultMap="BaseResultMap">
		SELECT distinct org.*,orguser.IS_MASTER_ FROM  UC_ORG_USER orguser
		inner join UC_ORG org on org.ID_=orguser.ORG_ID_
		WHERE orguser.USER_ID_=#{userId} AND org.IS_DELE_!='1' AND orguser.IS_DELE_!='1'
	</select>

	<select id="getMainOrgListByUser"   parameterType="java.util.Map" resultMap="BaseResultMap">
		SELECT distinct org.*,orguser.IS_MASTER_ FROM  UC_ORG_USER orguser
		inner join UC_ORG org on org.ID_=orguser.ORG_ID_
		WHERE orguser.IS_MASTER_=1 and orguser.USER_ID_=#{userId,jdbcType=VARCHAR} AND orguser.IS_DELE_!='1' AND org.IS_DELE_!='1'
	   <if test="demId!=null">
			and org.DEM_ID_=#{demId,jdbcType=VARCHAR}
	   </if>
	</select>

	<select id="getByParentId" parameterType="string"  resultMap="BaseResultMap" >
		select  ID_  ,PARENT_ID_,NAME_, ( case (select count(*)  from UC_ORG p where p.PARENT_ID_=o.ID_ and p.ID_!=p.PARENT_ID_ AND p.IS_DELE_!='1') when 0 then 0 else 1 end )isIsParent
		from  UC_ORG o
		where PARENT_ID_=#{pid} AND IS_DELE_!='1'  order by ORDER_NO_ asc
	</select>

	<select id="getOrgIdMapByUserId" parameterType="java.lang.String" resultType="java.util.Map">
		SELECT DISTINCT
			org.ID_ AS orgId,
			orguser.IS_MASTER_ AS isMaster
		FROM
			UC_ORG_USER orguser
		INNER JOIN UC_ORG org ON org.ID_ = orguser.ORG_ID_
		WHERE
			orguser.USER_ID_ = #{userId}
		AND org.IS_DELE_ != '1'
		AND orguser.IS_DELE_ != '1'
	</select>

	<select id="getSubOrgByIds" parameterType="java.lang.String" resultType="java.lang.String">
		SELECT
			ID_
		FROM
			uc_org
		WHERE
			PARENT_ID_ IN
			<foreach collection="parentIds" index="index" item="parentId" open="(" separator="," close=")">
	            #{parentId}
	        </foreach>
		AND IS_DELE_ != '1'
	</select>

	<delete id="removePhysical" >
		DELETE FROM UC_ORG  WHERE IS_DELE_='1'
	</delete>

	<select id="getByOrgName" parameterType="java.lang.String" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from UC_ORG where NAME_ = #{orgName} AND IS_DELE_!='1'
	</select>
	<select id="getByPathName" parameterType="java.lang.String" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from UC_ORG where PATH_NAME_ = #{pathName} AND IS_DELE_!='1'
	</select>

	<select id="getByParentAndDem" parameterType="java.util.Map" resultMap="BaseResultMap">
		select  o.*,
		( case (select count(*)  from UC_ORG p where p.PARENT_ID_=o.ID_ and p.ID_!=p.PARENT_ID_ AND p.IS_DELE_!='1' ) when 0 then 0 else 1 end ) isIsParent
		from  UC_ORG o where o.IS_DELE_!='1'
		<if test="ew.paramNameValuePairs.parentId!=null">
			and PARENT_ID_ in ${ew.paramNameValuePairs.parentId}
		</if>
		<if test="ew.paramNameValuePairs.authSql!=null">
			${ew.paramNameValuePairs.authSql}
		</if>
		<if test="ew.paramNameValuePairs.demId!=null">
			and o.DEM_ID_ =#{ew.paramNameValuePairs.demId}
		</if>
		<if test="ew.paramNameValuePairs.path != null">
			and o.PATH_ LIKE #{ew.paramNameValuePairs.path}
		</if>
		<if test="ew.paramNameValuePairs.name != null">
			and o.name_ LIKE '%${ew.paramNameValuePairs.name}%'
		</if>
		<if test="ew.paramNameValuePairs.id!=null">
			and o.ID_ = ${ew.paramNameValuePairs.id}
		</if>
		<if test="ew.paramNameValuePairs.orgType!=null">
			and o.ORG_TYPE_ in ${ew.paramNameValuePairs.orgType}
		</if>
			${ew.customSqlSegment}
		ORDER BY o.ORDER_NO_
	</select>

	<select id="getByParentAndDemTemp" parameterType="java.util.Map" resultMap="BaseResultMap">
		select  o.*,
		( case (select count(*)  from UC_ORG p where p.PARENT_ID_=o.ID_ and p.ID_!=p.PARENT_ID_ AND p.IS_DELE_!='1' ) when 0 then 0 else 1 end ) isIsParent
		from  UC_ORG o
		${ew.customSqlSegment}
	</select>

	<select id="getByDemIdAndSonId" resultMap="BaseResultMap">
		select  o.* from  UC_ORG o
		where o.IS_DELE_!='1' AND o.ID_ =(select og.PARENT_ID_ from UC_ORG og where og.ID_= #{sonId,jdbcType=VARCHAR} AND og.DEM_ID_= #{demId,jdbcType=VARCHAR} AND og.IS_DELE_!='1')
	</select>

	<select id="getOrgListByDemId" parameterType="java.lang.String" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from UC_ORG where DEM_ID_ = #{demId} AND IS_DELE_!='1'
	</select>

	<select id="getOrgsByAccount" parameterType="java.lang.String" resultMap="BaseResultMap">
		SELECT distinct org.*,orguser.IS_MASTER_ FROM  UC_ORG_USER orguser
		inner join UC_ORG org on org.ID_=orguser.ORG_ID_
		inner join UC_USER u on u.ID_=orguser.USER_ID_
		WHERE org.IS_DELE_!='1' AND orguser.IS_DELE_!='1' and u.ACCOUNT_=#{account}
	</select>

	<select id="justGetOrgsByAccount" parameterType="java.lang.String" resultMap="BaseResultMap">
		SELECT distinct org.*,orguser.IS_MASTER_ FROM  UC_ORG_USER orguser
		inner join UC_ORG org on org.ID_=orguser.ORG_ID_
		inner join UC_USER u on u.ID_=orguser.USER_ID_
		WHERE org.IS_DELE_!='1' AND orguser.IS_DELE_!='1' and u.ACCOUNT_=#{account} and orguser.POS_ID_ is null
	</select>

	<select id="getUserOrg"   parameterType="java.util.Map" resultMap="BaseResultMap">
		SELECT distinct org.*,orguser.IS_MASTER_ FROM  UC_ORG_USER orguser
		inner join UC_ORG org on org.ID_=orguser.ORG_ID_
		WHERE org.IS_DELE_!='1' AND orguser.IS_DELE_!='1'
		 <if test="isMain!=null">
			and orguser.IS_MASTER_=#{isMain}
	   </if>
		<if test="userId!=null">
			and orguser.USER_ID_=#{userId}
	   </if>
	   <if test="orgId!=null">
			and org.ID_=#{orgId}
	   </if>
	   <if test="demId!=null">
			and org.DEM_ID_=#{demId}
	   </if>
	</select>

	<!-- 关联uc_demension查找组织 -->
	<select id="getOrgInnerDem" resultMap="BaseResultMap">
		select  o.*
		from  UC_ORG o   INNER JOIN uc_demension d ON o.DEM_ID_ = d.ID_
		where o.IS_DELE_!='1' AND d.IS_DELE_!='1'
		 <if test="demId!=null">
			and o.DEM_ID_=#{demId}
	   	</if>
	   	<if test="code!=null">
			and o.CODE_=#{code}
	   	</if>
	   	<if test="demCode!=null">
			and d.CODE_=#{demCode}
	   	</if>
	</select>

	<!-- 按用户id组查找组织全路径 -->
	<select id="getPathNames" parameterType="java.lang.String"  resultType="java.util.Map">
		 SELECT
			ucuser.ID_ as "userId",
			ucuser.FULLNAME_ as "fullName",
			<!-- ucuser.dutyname as dutyName, -->
			ucorg.PATH_NAME_ as "pathName"
		FROM
			uc_user  ucuser
		LEFT JOIN uc_org_user  ucorguser ON ucuser.ID_ = ucorguser.USER_ID_
		AND ucorguser.IS_MASTER_ = '1'
		AND ucorguser.IS_REL_ACTIVE_ = '1'
		AND ucorguser.IS_DELE_ = '0'
		LEFT JOIN uc_org  ucorg ON ucorg.ID_ = ucorguser.ORG_ID_ AND ucorg.IS_DELE_ = '0'
		WHERE
			ucuser.ID_ IN
			<foreach collection="userIds" index="index" item="userId" open="(" separator="," close=")">
	            #{userId}
	        </foreach>
	</select>

	<!--根据用户权限Map获取有权限流程列表  -->
	<select id="getChildrenIds"  resultType="java.lang.String"  parameterType="java.util.Map" >
		select o.ID_ from UC_ORG o  where o.ID_ is null
		   <foreach collection="pathMap" index="key" item="value" open="" separator=" " close="">
				union
				select ID_ from UC_ORG o
	 				 where  o.PATH_ like '${value}%'
             </foreach>
	</select>

	<!--根据用户id 查询主组织  -->
	<select id="getOrgMaster"  resultMap="BaseResultMap"  parameterType="java.lang.String" >
		SELECT
			org.*
		FROM
			uc_user  uc
		LEFT JOIN uc_org_user  orguc ON uc.ID_ = orguc.USER_ID_
		LEFT JOIN uc_org  org ON orguc.ORG_ID_ = org.ID_
		WHERE  uc.ACCOUNT_ = #{account} AND orguc.IS_MASTER_ = '1' AND orguc.IS_DELE_ != '1'
	</select>

	<select id="getByIds" resultMap="BaseResultMap">
		SELECT o.* from UC_ORG o WHERE
		<if test="ids.isEmpty()">1=2</if>
		<if test="!ids.isEmpty()">
			o.ID_ IN
			<foreach collection="ids" separator="," open="(" close=")"  item="id">
				#{id}
			</foreach>
		</if>
		AND o.IS_DELE_ != '1'
	</select>

	<select id="getOrgByIdsAndCurOrgId"  parameterType="string" resultType="map">
		SELECT distinct  o.ID_, o.NAME_, o.CODE_, '' as ORG_ID_, o.NAME_ as ORG_NAME_, o.PATH_NAME_ as ORG_PATH_
		from UC_ORG o
		where o.IS_DELE_ = '0'
		and o.ID_ in (
			SELECT t.ID_ from UC_ORG t WHERE t.IS_DELE_='0' and t.PATH_ like '${orgPath}%'
			<if test="!ids.isEmpty()">
				and t.ID_ IN
				<foreach collection="ids" separator="," open="(" close=")"  item="id">
					#{id}
				</foreach>
			</if>
		)
		order by o.PATH_, o.ID_
	</select>

	<select id="getCountByCode" parameterType="java.lang.String" resultType="java.lang.Integer">
		SELECT count(1) FROM UC_ORG
		WHERE CODE_ = #{code} and IS_DELE_ != 1
	</select>

	<select id="getOrgListByIds" resultMap="BaseResultMap">
		SELECT
		<include refid="Base_Column_List" />
		FROM
		uc_org
		WHERE
		ID_ IN
		<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
		AND IS_DELE_ != 1
		<if test="demId!=null">
			and DEM_ID_=#{demId}
		</if>
	</select>

	<select id="queryExistsNameBySameLevel" resultType="java.lang.Boolean">
		SELECT COUNT(*) FROM uc_org
		WHERE
		PARENT_ID_ = #{parentId}
		AND IS_DELE_ != 1
		AND NAME_ = #{newName}
		AND ID_ != #{orgId}
		<if test="orgType!=null">
			and ORG_TYPE_ in (#{orgType})
		</if>
	</select>

	<select id="getOrgListByCodes" resultMap="BaseResultMap">
	SELECT
	<include refid="Base_Column_List" />
	FROM
	uc_org
	WHERE
	CODE_ IN
	<foreach collection="list" index="index" item="code" open="(" separator="," close=")">
		#{code}
	</foreach>
	AND IS_DELE_ != 1
</select>

	<select id="getPathNameList" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List" />
		from UC_ORG where PATH_NAME_
		in
		<foreach collection="list" index="index" item="pathName" open="(" separator="," close=")">
            #{pathName}
        </foreach>
		AND IS_DELE_!='1'
	</select>

	<insert id="insertBatch" parameterType="java.util.List">
		INSERT INTO UC_ORG
		(
		ID_,NAME_,PARENT_ID_,ORDER_NO_,CODE_,GRADE_,DEM_ID_,PATH_,PATH_NAME_,UPDATE_TIME_,IS_DELE_,VERSION_,LIMIT_NUM_,NOW_NUM_,EXCEED_LIMIT_NUM_,create_by_,create_time_,create_org_id_
		)
		VALUES
		<foreach collection="list" item="item" index="index" separator=",">
			(#{item.id,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, #{item.parentId,jdbcType=VARCHAR}, #{item.orderNo,jdbcType=NUMERIC}, #{item.code,jdbcType=VARCHAR}, #{item.grade,jdbcType=VARCHAR},#{item.demId,jdbcType=VARCHAR},#{item.path,jdbcType=VARCHAR},#{item.pathName,jdbcType=VARCHAR},#{item.updateTime,jdbcType=TIMESTAMP},#{item.isDelete,jdbcType=VARCHAR},#{item.version,jdbcType=NUMERIC},
			#{item.limitNum,jdbcType=NUMERIC},#{item.nowNum,jdbcType=NUMERIC},#{item.exceedLimitNum,jdbcType=NUMERIC},#{item.createBy,jdbcType=VARCHAR},#{item.createTime,jdbcType=TIMESTAMP},#{item.createOrgId,jdbcType=VARCHAR})
		</foreach>
	</insert>

	<insert id="insertBatch" databaseId="oracle" parameterType="java.util.List">
		INSERT INTO UC_ORG
		(
		ID_,NAME_,PARENT_ID_,ORDER_NO_,CODE_,GRADE_,DEM_ID_,PATH_,PATH_NAME_,UPDATE_TIME_,IS_DELE_,VERSION_,LIMIT_NUM_,NOW_NUM_,EXCEED_LIMIT_NUM_,create_by_,create_time_,create_org_id_
		)
		(
		<foreach collection="list" item="item" index="index" separator="union all">
			select #{item.id,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, #{item.parentId,jdbcType=VARCHAR}, #{item.orderNo,jdbcType=NUMERIC}, #{item.code,jdbcType=VARCHAR}, #{item.grade,jdbcType=VARCHAR},#{item.demId,jdbcType=VARCHAR},#{item.path,jdbcType=VARCHAR},#{item.pathName,jdbcType=VARCHAR},#{item.updateTime,jdbcType=TIMESTAMP},#{item.isDelete,jdbcType=VARCHAR},#{item.version,jdbcType=NUMERIC},
			#{item.limitNum,jdbcType=NUMERIC},#{item.nowNum,jdbcType=NUMERIC},#{item.exceedLimitNum,jdbcType=NUMERIC},#{item.createBy,jdbcType=VARCHAR},#{item.createTime,jdbcType=TIMESTAMP},#{item.createOrgId,jdbcType=VARCHAR} FROM DUAL
		</foreach>
		)
	</insert>

	<select id="getUsersByOrgIds" resultType="com.hotent.uc.model.User">
		select u.*
		from uc_user u left join uc_org_user ou on ou.USER_ID_ = u.ID_
		where ou.ORG_ID_ in
		<foreach collection="orgIds" item="orgId" open="(" close=")" separator=",">
			#{orgId}
		</foreach>
		<if test="isMain">
			and ou.is_master_ = 1
		</if>

	</select>
	<select id="selectOrgByWrapper" resultType="com.hotent.uc.model.Org">
		SELECT * FROM uc_org ${ew.customSqlSegment}
	</select>

	<resultMap id="ResultOrgTreeVoMap" type="com.hotent.uc.vo.OrgTreeVo">
	</resultMap>

	<select id="getTreeAllOrgByExtOrgId"   parameterType="string" resultMap="ResultOrgTreeVoMap">
		SELECT org.*
		FROM UC_ORG org
		WHERE org.IS_DELE_='0' and org.PATH_ like '${orgPath}%'
	</select>

	<select id="getTreeCheckOrgByExtOrgId" parameterType="string" resultType="map">
		SELECT org.ID_ AS "id", org.PARENT_ID_ "parentId", org.NAME_ "name", org.CODE_ "code", org.ORDER_NO_ "orderNo", org.tenant_id_,
		(SELECT COUNT(tmp.ID_) FROM UC_ORG tmp WHERE tmp.IS_DELE_='0' and tmp.PARENT_ID_ = org.ID_ ) "isParent",
		(SELECT count(tmpB.id_) FROM uc_org_auth_ext tmpB
		WHERE tmpB.ORG_ID_ = org.ID_
		<if test="appType==2">
			and tmpB.AUTH_TYPE_ = #{appType}  and tmpB.PC_APP_ID_ = #{appId}
		</if>
		<if test="appType==3">
			and tmpB.AUTH_TYPE_ = #{appType}  and tmpB.MOBILE_APP_ID_ = #{appId}
		</if>
		<if test="tenantId!=null">
			AND tmpB.TENANT_ID_ = #{tenantId}
		</if>
		) "checked"
		FROM
		UC_ORG org
		WHERE org.IS_DELE_='0' and org.PATH_ like '${orgPath}%'
		ORDER BY org.ORDER_NO_ ASC
	</select>
</mapper>
