<?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.njcn.mapper.user.DeptsMapper">

    <!-- 部门 -->
    <resultMap type="com.njcn.pojo.user.Depts" id="DeptsMapperMap" autoMapping="true">

    </resultMap>
    <!-- 修改 -->
    <update id="updateDepts">
update pqs_depts
set DEPTSNAME=#{deptsName},
PARENTNODE=#{parentNode},UPDATETIME=#{updateTime},
DEPTS_DESCRIPTION=#{deptsDescription},AREA=#{area} where DEPTS_INDEX=#{deptsIndex}
</update>
    <!-- 根据父节点查询名称 -->
    <select id="selectName" resultMap="DeptsMapperMap">
select * from pqs_depts
where node in
(
select parentnode from pqs_depts
where parentnode=#{parentNode}
)
and systype=#{sysType}
and state=1
</select>


    <!-- 查询 -->
    <select id="selectDeptAll" resultMap="DeptsMapperMap">
select*from pqs_depts where SYSTYPE=#{sysType} and state=1 order by node
</select>

    <select id="queryDeptsByLikeAreaName" resultType="Depts">
	select * from PQS_DEPTS where AREA like #{area} AND  SYSTYPE=#{sysType} and state=1
</select>
    <!-- 除去本身的名称 -->
    <select id="selparentnode" resultMap="DeptsMapperMap">
select * from PQS_DEPTS  where parentnode  in (select parentnode from PQS_DEPTS where DEPTS_INDEX=#{deptsIndex} and state=1) and systype=#{sysType}
</select>
    <!-- 查询除此之外的名称 -->
    <select id="selName" resultMap="DeptsMapperMap">
select * from PQS_DEPTS  where node not in (select parentnode from  PQS_DEPTS where DEPTS_INDEX=#{deptsIndex} and state=1) and systype=#{sysType}
</select>
    <select id="getId" resultMap="DeptsMapperMap">
select * from pqs_depts where node=#{node} and systype=#{sysType} and state=1
</select>

    <select id="getAreaName" resultType="String">
	select
		t2.DIC_NAME
	from PQS_DEPTS t1,PQS_DICDATA t2
	where
		t1.AREA=t2.DIC_INDEX
	and
	    t1.SYSTYPE=#{sysType}
	and
		t1.state=1
	order by  T1.NODE asc
</select>





    <select id="selectDeptsByDeptsIndex" resultType="com.njcn.pojo.user.Depts">
		SELECT
			t2.DIC_NAME area,
			t1.*
		FROM
			PQS_DEPTS t1,
			PQS_DICDATA t2
			WHERE
			t1.AREA=t2.DIC_INDEX
			AND
			t1.DEPTS_INDEX=#{deptsIndex}
			AND
			t1.STATE=1
	</select>

    <select id="getDirectDeptsByNode" resultType="com.njcn.pojo.user.Depts">
		SELECT
			t1.DEPTS_INDEX,
			t1.DEPTSNAME,
			t1.DEPTS_DESC,
			t2.DIC_NAME area,
			t1.CUSTOM_DEPT
		FROM
			PQS_DEPTS t1
			LEFT JOIN PQS_DICDATA t2 ON t1.area = t2.dic_index
		WHERE
			t1.PARENTNODEID = #{node}
		    AND
			  t1.CUSTOM_DEPT <![CDATA[ <>  ]]>  2
			AND t1.STATE =1
		ORDER BY t1.DEPTS_DESC ASC
	</select>
    <select id="getDeptsByNode" resultType="Depts" databaseId="Oracle">
		SELECT
			t1.DEPTS_INDEX,
			t1.PARENTNODEID,
			t1.DEPTSNAME,
			t1.DEPTS_DESC,
			t2.DIC_NAME area,
			t1.CUSTOM_DEPT
		FROM
			PQS_DEPTS t1,
			PQS_DICDATA t2
		WHERE
			t1.area = t2.dic_index
		  AND t1.STATE = 1
		  AND
			t1.CUSTOM_DEPT <![CDATA[ <>  ]]>  2
			START WITH t1.PARENTNODEID = #{node} CONNECT BY PRIOR t1.DEPTS_INDEX = t1.PARENTNODEID
		ORDER BY
			t1.PARENTNODEID,
			t1.DEPTS_DESC ASC
	</select>
	
	<select id="getDeptsByNode" resultType="Depts" databaseId="MariaDB">
		WITH recursive t3 AS (
			SELECT
				a.DEPTS_INDEX,
				a.PARENTNODEID,
				a.DEPTSNAME,
				a.DEPTS_DESC,
				a.DIC_NAME,
				a.CUSTOM_DEPT
			FROM
				(
					SELECT
						t1.DEPTS_INDEX,
						t1.PARENTNODEID,
						t1.DEPTSNAME,
						t1.DEPTS_DESC,
						t2.DIC_NAME,
						t1.CUSTOM_DEPT
					FROM
						PQS_DEPTS t1,
						PQS_DICDATA t2
					WHERE
						t1.STATE = 1
					  AND
						t1.CUSTOM_DEPT <![CDATA[ <>  ]]>  2
					  AND t1.area = t2.dic_index
					  AND t1.PARENTNODEID = #{node}
				) a UNION ALL
			SELECT
				b.DEPTS_INDEX,
				b.PARENTNODEID,
				b.DEPTSNAME,
				b.DEPTS_DESC,
				b.DIC_NAME,
				b.CUSTOM_DEPT
			FROM
				(
					SELECT
						t1.DEPTS_INDEX,
						t1.PARENTNODEID,
						t1.DEPTSNAME,
						t1.DEPTS_DESC,
						t2.DIC_NAME,
						t1.CUSTOM_DEPT
					FROM
						PQS_DEPTS t1,
						PQS_DICDATA t2
					WHERE
						t1.STATE = 1
					  AND
						t1.CUSTOM_DEPT <![CDATA[ <>  ]]>  2
					  AND t1.area = t2.dic_index
				) b,
				t3
			WHERE
				t3.DEPTS_INDEX = b.PARENTNODEID
		) SELECT
			*
		FROM
			t3 order by t3.depts_index
	</select>
	<select id="getDeptsByNodeUp" resultType="Depts" databaseId="Oracle">
		SELECT DISTINCT
			t1.DEPTS_INDEX,
			t1.PARENTNODEID,
			t1.DEPTSNAME,
			t1.DEPTS_DESC,
			t2.DIC_NAME area,
			t1.CUSTOM_DEPT
		FROM
			PQS_DEPTS t1,
			PQS_DICDATA t2
		WHERE
			t1.STATE = 1
		  AND
			t1.CUSTOM_DEPT <![CDATA[ <>  ]]> 2
		  AND t1.area = t2.dic_index START WITH t1.DEPTS_INDEX = #{deptsIndex} CONNECT BY PRIOR t1.PARENTNODEID = t1.DEPTS_INDEX
		ORDER BY
			t1.PARENTNODEID,
			t1.DEPTS_DESC ASC
	</select>
	
	<select id="getDeptsByNodeUp" resultType="Depts" databaseId="MariaDB">
		with recursive t3 as
						   (
							   select
								   a.DEPTS_INDEX,
								   a.PARENTNODEID,
								   a.DEPTSNAME,
								   a.DEPTS_DESC,
								   a.DIC_NAME,
								   a.CUSTOM_DEPT  from
								   (SELECT
										t1.DEPTS_INDEX,
										t1.PARENTNODEID,
										t1.DEPTSNAME,
										t1.DEPTS_DESC,
										t2.DIC_NAME,
										t1.CUSTOM_DEPT
									FROM PQS_DEPTS t1,PQS_DICDATA t2
									where t1.STATE = 1  AND t1.CUSTOM_DEPT <![CDATA[ <>  ]]> 2 and t1.area = t2.dic_index and t1.DEPTS_INDEX = #{deptsIndex}) a
							   union all
							   select
								   b.DEPTS_INDEX,
								   b.PARENTNODEID,
								   b.DEPTSNAME,
								   b.DEPTS_DESC,
								   b.DIC_NAME,
								   b.CUSTOM_DEPT   from
													   (SELECT
															t1.DEPTS_INDEX,
															t1.PARENTNODEID,
															t1.DEPTSNAME,
															t1.DEPTS_DESC,
															t2.DIC_NAME,
															t1.CUSTOM_DEPT
														FROM PQS_DEPTS t1,PQS_DICDATA t2
														where t1.STATE = 1 AND t1.CUSTOM_DEPT <![CDATA[ <>  ]]> 2  and t1.area = t2.dic_index ) b,t3 where b.DEPTS_INDEX = t3.PARENTNODEID
						   )select * from t3
	</select>
</mapper>
