<?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.jeeplus.pubmodules.reimApprovalType.mapper.CwReimbursementTypeMapper">
	<resultMap id="BaseResultMap" type="com.jeeplus.pubmodules.reimApprovalType.domain.CwReimbursementTypeInfo">
		<id property="id" column="id" jdbcType="VARCHAR"/>
		<result property="createById" column="create_by_id" jdbcType="VARCHAR"/>
		<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
		<result property="updateById" column="update_by_id" jdbcType="VARCHAR"/>
		<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
		<result property="delFlag" column="del_flag" jdbcType="INTEGER"/>

		<result property="level" column="level" jdbcType="VARCHAR"/>
		<result property="name" column="name" jdbcType="VARCHAR"/>
		<result property="sort" column="sort" jdbcType="VARCHAR"/>
		<result property="parentId" column="parent_id" jdbcType="VARCHAR"/>
		<result property="parentIds" column="parent_ids" jdbcType="VARCHAR"/>
		<collection property="officeDTOList" ofType="com.jeeplus.sys.service.dto.OfficeDTO" column="id" select="getOfficeClient"></collection>

	</resultMap>


	<sql id="Base_Column_List">
        a.id,
        a.create_by_id,
        a.create_time,
        a.update_by_id,
        a.update_time,
        a.del_flag,
        a.level,
		a.name,
		a.sort,
		a.parent_id,
		a.parent_ids
    </sql>

	<sql id="officeColumns">
    	so.id as "id",
        so.parent_id as "parent.id",
        so.parent_ids as "parentIds",
        so.name as "name",
        so.sort as "sort",
        so.area as "area",
        so.code as "code",
        so.type as "type",
        so.grade as "grade",
        so.address as "address",
        so.zip_code as "zipCode",
        so.master as "master",
        so.phone as "phone",
        so.fax as "fax",
        so.email as "email",
        so.useable as "useable",
        so.primary_person as "primaryPerson",
        so.deputy_person as "deputyPerson",
		so.create_by_id as "createBy.id",
		so.create_time as "createTime",
		so.update_by_id as "updateBy.id",
		so.update_time as "updateTime",
        so.remarks as "remarks",
        so.del_flag as "delFlag",
        so.is_public as "isPublic",
        so.administrator as "administrator"
    </sql>

	<select id="getOfficeClient" resultType="com.jeeplus.sys.service.dto.OfficeDTO">
		select
		<include refid="officeColumns"></include>
		from sys_office so
		left join cw_reimbursement_type_office_info rtoi on rtoi.office_id = so.id and so.del_flag = '0'
		where rtoi.reimbursement_type_id = #{id}
	</select>


	<select id="findList" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List"></include>
		,d.`name` as relateDepartment
		from cw_reimbursement_type_info a
		LEFT JOIN
		(SELECT GROUP_CONCAT(c.name) as name,c.office_id,c.reimbursement_type_id FROM
		(SELECT name,cw.office_id,cw.reimbursement_type_id
		FROM cw_reimbursement_type_office_info cw
		LEFT JOIN sys_office so on cw.office_id = so.id) c GROUP BY c.reimbursement_type_id) d
		on a.id = d.reimbursement_type_id
		${ew.customSqlSegment}
	</select>


	<select id="getById" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List"></include>
		from cw_reimbursement_type_info a
		<where>
			a.del_flag = 0 and a.id = #{id}
		</where>
	</select>

	<select id="getByName" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List"></include>
		from cw_reimbursement_type_info a
		<where>
			a.del_flag = 0 and a.name = #{name}
		</where>
	</select>

	<select id="checkNameIsExist" resultType="java.lang.Integer">
        SELECT
			COUNT( 0 )
		FROM
			cw_reimbursement_type_info
		WHERE
			del_flag = 0
			AND `name` = #{name}
			AND parent_id = #{parentId}
    </select>
    <select id="getNo" resultType="java.lang.Integer">
        SELECT
			(MAX(`sort`) + 1) AS `sort`
		FROM
			cw_reimbursement_type_info
		WHERE
			del_flag = 0
			AND parent_id = #{parentId}
    </select>
    <select id="getLevel" resultType="java.lang.Integer">
        SELECT
			(MAX(level) + 1) AS `level`
		FROM
			cw_reimbursement_type_info
		WHERE
			del_flag = 0
			AND id = #{parentId}
    </select>
	<select id="getChildId" resultType="java.lang.String">
		select id from cw_reimbursement_type_info where parent_id = #{id}
	</select>
	<select id="getAllList"
			resultType="com.jeeplus.pubmodules.reimApprovalType.domain.CwReimbursementTypeInfo">
		select * from cw_reimbursement_type_info where  del_flag = '0' order by sort asc
	</select>
	<select id="bxList2"
			resultType="com.jeeplus.pubmodules.reimApprovalType.domain.CwReimbursementTypeInfo">
		SELECT
			t.id,
			t.create_time,
			t.create_by_id,
			t.update_time,
			t.update_by_id,
			t.del_flag,
			t.LEVEL,
			t.NAME,
			t.sort,
			t.parent_id,
			t.parent_ids
			FROM
				(
				SELECT
					id,
					create_time,
					a.create_by_id,
					a.update_time,
					a.update_by_id,
					a.del_flag,
					a.LEVEL,
					a.NAME,
					a.sort,
					a.parent_id,
					a.parent_ids
				FROM
					cw_reimbursement_type_info a
				WHERE
					del_flag = 0
					AND a.id IN (
					SELECT
						id
					FROM
						cw_reimbursement_type_info a
					WHERE
						del_flag = 0
					AND a.parent_id NOT IN ( SELECT b.reimbursement_type_id FROM cw_reimbursement_type_office_info b)
					AND a.id NOT IN ( SELECT b.reimbursement_type_id FROM cw_reimbursement_type_office_info b)
					)
					union
				SELECT
					id,
					create_time,
					a.create_by_id,
					a.update_time,
					a.update_by_id,
					a.del_flag,
					a.LEVEL,
					a.NAME,
					a.sort,
					a.parent_id,
					a.parent_ids
					FROM
					cw_reimbursement_type_info a
					WHERE
					del_flag = 0
					and a.parent_id in (SELECT b.reimbursement_type_id FROM cw_reimbursement_type_office_info b WHERE b.office_id = #{officeId})
				union
				SELECT
					id,
					create_time,
					a.create_by_id,
					a.update_time,
					a.update_by_id,
					a.del_flag,
					a.LEVEL,
					a.NAME,
					a.sort,
					a.parent_id,
					a.parent_ids
					FROM
					cw_reimbursement_type_info a
					WHERE
					del_flag = 0
					and a.parent_id in
						(SELECT
							id
						FROM
							cw_reimbursement_type_info a
						WHERE
							del_flag = 0
							and a.parent_id in (SELECT b.reimbursement_type_id FROM cw_reimbursement_type_office_info b WHERE b.office_id = #{officeId})
						)
				UNION
				SELECT
					id,
					create_time,
					a.create_by_id,
					a.update_time,
					a.update_by_id,
					a.del_flag,
					a.LEVEL,
					a.NAME,
					a.sort,
					a.parent_id,
					a.parent_ids
					FROM
					cw_reimbursement_type_info a
					WHERE
					del_flag = 0
					and a.id in (SELECT b.reimbursement_type_id FROM cw_reimbursement_type_office_info b WHERE b.office_id = #{officeId})
				) t
			${ew.customSqlSegment}
	</select>
	<select id="officeInfo"
			resultType="com.jeeplus.pubmodules.reimApprovalType.domain.OfficeDomain">
		SELECT id,name,grade,parent_id FROM sys_office WHERE id = #{id} and del_flag = 0
	</select>
	<select id="officeInfo2"
			resultType="com.jeeplus.pubmodules.reimApprovalType.domain.OfficeDomain">
		SELECT id,name,grade,parent_id FROM sys_office WHERE id =
		(SELECT parent_id FROM sys_office WHERE id = #{id} and del_flag = 0)
		 and del_flag = 0
	</select>
	<select id="officeInfo3"
			resultType="com.jeeplus.pubmodules.reimApprovalType.domain.OfficeDomain">
		SELECT T2.ID, T2.parent_id,T2.name,T2.grade
			FROM
			(SELECT @r := #{id}, @l := 0) vars,
			(SELECT
				@r as _id,
				(SELECT
					@r := parent_id
					FROM
					sys_office
					WHERE
					id = _id
				) as parent_id,
				@l := @l + 1 as lv1
				FROM sys_office h
				WHERE
				@r != 0
			) T1
		join sys_office T2 on T1._id = T2.id and T2.del_flag = 0
		ORDER BY grade desc
	</select>

	<delete id="deleteReimbursementTypeOfficeInfo">
		delete from cw_reimbursement_type_office_info where reimbursement_type_id = #{id}
	</delete>

	<insert id="saveReimbursementTypeOfficeInfo">
		<foreach collection="officeIdList" item="officeId" separator=";">
			insert into cw_reimbursement_type_office_info(reimbursement_type_id,office_id) values(#{id},#{officeId})
		</foreach>
	</insert>
</mapper>
