<?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.sunxung.factoring.mapper.sys.OrgMapper">

	<resultMap type="Org" id="orgResult">
		<result property="id" column="id"/>
		<result property="departmentId" column="department_id"/>
		<result property="parent.id" column="parent_id"/>
		<result property="name" column="name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="internalCode" column="internal_code"/>
		<result property="subCountMax" column="sub_count_max"/>
		<result property="subCount" column="sub_count"/>
		<result property="gradation" column="gradation"/>
		<result property="simplePinYin" column="simple_pinyin"/>
		<result property="fullPinYin" column="full_pinyin"/>
		<result property="userCreate" column="user_create"/>
		<result property="userModified" column="user_modified"/>
		<result property="gmtCreate" column="gmt_create"/>
		<result property="gmtModified" column="gmt_modified"/>
	</resultMap>

	<sql id="fields">
		id,
		department_id,
		parent_id,
		name,
		dept_no,
		internal_code,
		sub_count_max,
		sub_count,
		gradation,
		simple_pinyin,
		full_pinyin,
		user_create,
		user_modified,
		gmt_create,
		gmt_modified
	</sql>

	<insert id="add" useGeneratedKeys="true" keyProperty="id" keyColumn="id" parameterType="Org">
		insert into sys_org
			(parent_id, name, dept_no, internal_code, simple_pinyin, full_pinyin,department_id,gradation, user_create, user_modified,seq )
		values
			( #{parent.id}, #{name}, #{deptNo}, #{internalCode}, #{simplePinYin}, #{fullPinYin},#{departmentId},#{gradation} ,#{userCreate}, #{userModified},#{seq} )
	</insert>

	<update id="update" parameterType="Org">
		update
			sys_org
		<set>
			id = #{id}
			<if test="name!=null and name!=''">
				, name=#{name}
			</if>
			<if test="simplePinYin!=null and simplePinYin!=''">
				, simple_pinyin=#{simplePinYin}
			</if>
			<if test="fullPinYin!=null and fullPinYin!=''">
				, full_pinyin=#{fullPinYin}
			</if>
			,  user_modified=#{userModified}
		</set>
		where
			id = #{id}
	</update>

	<update id="modifySubCountMaxAndSubCountById">
		update
			sys_org
		set
			sub_count=#{subCount}, sub_count_max=#{subCountMax}
		where
			id=#{id}
	</update>

	<update id="modifySubCountById">
		update
			sys_org
		set
			sub_count=#{subCount}
		where
			id=#{id}
	</update>

	<delete id="delete" parameterType="java.lang.Long">
		delete from sys_org where id=#{value}
	</delete>

	<delete id="deleteByParentId" parameterType="java.lang.Long">
		delete from sys_org where parent_id=#{parentid}
	</delete>

	<select id="findOperationCenter" resultMap="orgResult">
		select <include refid="fields"/> from sys_org
		<if test="parentId!=null">
			where	 parent_id=#{parentId} and id in (2,3,4,14)
		</if>
		<if test="parentId==null">
			where	 ISNULL(parent_id)
		</if>
		order by gradation
	</select>

	<select id="findByParentId" resultMap="orgResult">
		select <include refid="fields"/> from sys_org
		<if test="parentId!=null">
			where	 parent_id=#{parentId}
		</if>
		<if test="parentId==null">
			where	 ISNULL(parent_id)
		</if>
		order by gradation
	</select>

	<select id="findAll" resultMap="orgResult">
		select <include refid="fields"/> from sys_org
		order by gradation
	</select>

	<select id="get" parameterType="java.lang.Long" resultMap="orgResult">
		select <include refid="fields"/> from sys_org where id=#{value}
	</select>

	<select id="getByCode" parameterType="java.lang.String" resultMap="orgResult">
		select <include refid="fields"/> from sys_org where dept_no=#{value}
	</select>

	<select id="getByParentIdAndName"  resultMap="orgResult">
		select <include refid="fields"/> from sys_org where parent_id=#{parentId} and name=#{name}
	</select>

	<sql id="sql_where">
		<if test="parentId != null and parentId != ''">
			and parent_id = #{parentId}
		</if>
		<if test="name != null and name != ''">
			and (name  like   CONCAT('%','${name}','%' ) or simple_pinyin like CONCAT('%','${name}','%') or full_pinyin like CONCAT('%','${name}','%'))
		</if>
	</sql>

	<select id="findBySearch" parameterType="SearchOrg" resultMap="orgResult">
		select <include refid="fields"/> from sys_org
		<where>
			<include refid="sql_where"/>
		</where>
		order by gradation
	</select>

	<select id="countBySearch" parameterType="SearchOrg" resultType="java.lang.Integer">
		select count(*) from sys_org
		<where>
			<include refid="sql_where"/>
		</where>
	</select>

	<update id="adjustOrder">
		update sys_org set gradation=#{gradation} where id=#{orgId}
	</update>

	<select id="findOrgByNameAndParentId" resultMap="orgResult">
		select <include refid="fields"/> from sys_org
		<where>
			<if test="parentId != null">
				and parent_id=#{parentId}
			</if>
			<if test="orgName != null and orgName !='' ">
				and name=#{orgName}
			</if>
		</where>
	</select>

	<select id="findUserIdByOrgCode" parameterType="String" resultType="Long">
		select distinct user_id from sys_user_org_rel where org_code like CONCAT(#{code},'%')
	</select>

	<select id="findRootByUseId" parameterType="java.lang.Long" resultType="com.sunxung.factoring.entity.sys.Org">
		SELECT
			c.*
		FROM
			sys_user a,
			sys_user_org_rel b,
			sys_org c
		WHERE
			a.id = b.user_id
		  AND b.org_id = c.id
		  AND c.id = 1
		  AND a.id = #{userId}
	</select>
	<select id="findIdentityByUseId" parameterType="java.lang.Long" resultType="com.sunxung.factoring.entity.sys.Org">
		SELECT
			c.*
		FROM
			sys_user a,
			sys_user_org_rel b,
			sys_org c
		WHERE
			a.id = b.user_id
		  AND b.org_id = c.id
		  AND b.user_identity = 1
		  AND a.id = #{userId};
	</select>
	<select id="findByName" resultType="com.sunxung.factoring.entity.sys.Org">
		SELECT
			<include refid="fields" />
		FROM sys_org WHERE name = #{name}
	</select>
</mapper>
