<?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.modules.sys.dao.OfficeDao">

	<sql id="officeColumns">
		a.id,
		a.parent_id AS "parent.id",
		a.parent_ids,
		a.area_id AS "area.id",
		a.code,
		a.company_qrcode,
		a.name,
		a.sort,
		a.type,
		a.grade,
		a.address, 
		a.zip_code, 
		a.master, 
		a.phone, 
		a.fax, 
		a.email, 
		a.remarks,
		a.create_by AS "createBy.id",
		a.create_date,
		a.update_by AS "updateBy.id",
		a.update_date,
		a.del_flag,
		a.useable AS useable,
        a.branch_office AS "branchOffice",
		<!-- a.primary_person AS "primaryPerson.id",-->
		<!-- a.deputy_person AS "deputyPerson.id",-->
		p.name AS "parent.name",
		p.type AS "parent.type",
		a.logo,
		a.state,
		a.iscurrent,
		a.group_id,
		a.group_name,
		a.simple_name AS "simpleName",
		a.branch_simple_name AS "branchSimpleName",
		p.simple_name AS "parent.simpleName",
		a.top_company AS "topCompany",
        a.branch_no AS "branchNo",
		a.is_son AS "isSon"
	</sql>
	
	<sql id="officeJoins">
		LEFT JOIN sys_office p ON p.id = a.parent_id
    </sql>

	<select id="getAllDepts" resultType="Office">
		SELECT
			a.id,
			a.parent_id AS "parent.id",
			a.parent_ids,
			a.area_id AS "area.id",
			a.code,
			a.company_qrcode,
			a.name,
			a.sort,
			a.type,
			a.grade,
			a.address,
			a.zip_code,
			a.master,
			a.phone,
			a.fax,
			a.email,
			a.remarks,
			a.create_by AS "createBy.id",
			a.create_date,
			a.update_by AS "updateBy.id",
			a.update_date,
			a.del_flag,
			a.useable AS useable,
			<!-- a.primary_person AS "primaryPerson.id",-->
			<!-- a.deputy_person AS "deputyPerson.id",-->
			a.logo,
			a.state,
			a.iscurrent,
			a.group_id,
			a.group_name,
            a.simple_name,
            a.branch_simple_name,
			a.top_company,
            a.branch_no,
			a.is_son
		FROM sys_office  a
		WHERE
		a.del_flag = '0'
		AND a.useable = '1'
		AND a.parent_id = #{id}

	</select>

	<select id="get" resultType="Office">
		SELECT
			<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.id = #{id}
	</select>

	<select id="findByUserId" resultType="Office" >
		SELECT
		a.id ,
		a.name
		FROM sys_office a
		JOIN sys_user_office uo ON uo.company_id = a.id AND uo.del_flag = '0'
		WHERE uo.user_id = #{userId}
	</select>

	<select id="getInfo" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		where a.del_flag = 0
		AND a.useable = '1'
			<if test="parent.id != null and parent.id != ''">
				AND a.parent_id = #{parent.id}
			</if>
			<if test="type != null and type != ''">
				AND a.parent_ids like CONCAT(#{id},'%')
			</if>
	</select>
	<select id="getByOfficeInfo" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		where a.del_flag = 0
		AND a.state = '1'
		AND a.useable = '1'
		AND a.parent_ids like
		<if test="dbName == 'oracle'">'%'||#{id}||'%'</if>
		<if test="dbName == 'mysql'">CONCAT('%', #{id}, '%')</if>
	</select>

	<select id="getByCode" resultType="Office">
		SELECT
			<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.code = #{code}
	</select>
	<select id="findList" resultType="Office">
		SELECT
			<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
		<if test="useable != null and useable != ''">
			AND a.useable = #{useable}
		</if>
		<if test="name != null and name != ''">
		      AND a.name like
			<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
		    <if test="dbName == 'mysql'">CONCAT('%', #{name}, '%')</if>
		</if>
		<if test="parentIds != null and parentIds != ''">
			AND (find_in_set(#{parentIds}, a.parent_ids) OR a.id = #{parentIds})
		</if>
		<if test="topCompany != null and topCompany != ''">
		      AND a.top_company like
			<if test="dbName == 'oracle'">'%'||#{topCompany}||'%'</if>
			<if test="dbName == 'mysql'">CONCAT('%', #{topCompany}, '%')</if>
		</if>
		<!-- 数据范围过滤 -->
		AND (a.id = #{currentUser.office.id}
		${sqlMap.dsf})

		ORDER BY a.type,a.code ASC
	</select>
	<select id="findMangeList" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
		<if test="useable != null and useable != ''">
			AND a.useable = #{useable}
		</if>
		<if test="name != null and name != ''">
			AND a.name like
			<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
			<if test="dbName == 'mysql'">CONCAT('%', #{name}, '%')</if>
		</if>
		<if test="parentIds != null and parentIds != ''">
			AND (find_in_set(#{parentIds}, a.parent_ids) OR a.id = #{parentIds})
		</if>
		<if test="topCompany != null and topCompany != ''">
			AND a.top_company like
			<if test="dbName == 'oracle'">'%'||#{topCompany}||'%'</if>
			<if test="dbName == 'mysql'">CONCAT('%', #{topCompany}, '%')</if>
		</if>
		<!-- 数据范围过滤 -->
		${sqlMap.dsf}
		ORDER BY a.type,a.code ASC
	</select>
	<select id="findByCompany" resultType="Office">
		SELECT
		a.id,
		a.parent_id AS "parent.id",
		a.parent_ids,
		a.area_id AS "area.id",
		a.code,
		a.company_qrcode,
		a.name,
		a.sort,
		a.type,
		a.grade,
		a.address,
		a.zip_code,
		a.master,
		a.phone,
		a.fax,
		a.email,
		a.remarks,
		a.create_by AS "createBy.id",
		a.create_date,
		a.update_by AS "updateBy.id",
		a.update_date,
		a.del_flag,
		a.useable AS useable,
		a.logo,
		a.state,
		a.iscurrent,
		a.group_id,
		a.group_name,
		a.simple_name AS "simpleName",
		a.branch_simple_name AS "branchSimpleName",
		a.top_company AS "topCompany",
		a.branch_no AS "branchNo",
		a.is_son
		FROM sys_office a
		left join sys_office t2 on (t2.parent_ids like concat('%',a.id,'%') and t2.type='2')
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
			and a.type in('1','3')
			AND a.useable = '1'
			and ( a.parent_ids like concat('%',#{id},'%') or a.id =  #{id})
		group by a.id
		ORDER BY a.type,a.code ASC
	</select>

	
			<select id="findListByCompanyName" resultType="Office">
		SELECT
			<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
				AND a.useable = '1'
		<if test="name != null and name != ''">
		      AND a.name like
		<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
	   <if test="dbName == 'mysql'">CONCAT('%', #{name}, '%')</if>
			</if>
		<if test="type != null and type != ''">
				AND a.type = #{type}
			</if>
		</select>

	<select id="findListByCompanyNamee" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE  a.useable = '1'
		<if test="name != null and name != ''">
			AND a.name like
			<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
			<if test="dbName == 'mysql'">CONCAT('%', #{name}, '%')</if>
		</if>
		<if test="type != null and type != ''">
			AND a.type = #{type}
		</if>
	</select>

	<select id="findAllList" resultType="Office">
		SELECT
			<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL} and a.id != '1'
		/*ORDER BY a.code*/
	</select>

	<select id="findAllList1" resultType="Office" parameterType="String">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL} and a.id != '1'
		/*ORDER BY a.code*/
	</select>

	<select id="findAllListByParentIds" resultType="Office">
		SELECT
			<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
		AND a.useable = '1'
		AND (find_in_set(#{id}, a.parent_ids) OR a.id = #{id})
		ORDER BY a.code
	</select>
	<select id="findListByParentId" resultType="Office">
		SELECT
			<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
		AND a.useable = '1'
		AND a.parent_id = #{parent.id}
		ORDER BY a.code
	</select>
	<select id="findListByParentIdCount" resultType="java.lang.Integer">
		SELECT
			count(*)
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
		AND a.useable = '1'
		AND a.parent_id = #{parent.id}
		ORDER BY a.code
	</select>

	<select id="findByParentIdsLike" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL} AND a.useable = '1' AND (a.parent_ids LIKE
		<if test="dbName == 'mysql'">concat('%,',#{parentIds},',%')</if>
		 OR a.id = #{id})
		ORDER BY a.code
	</select>

	<select id="findByParent" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL} AND a.useable = '1'
		<if test="parent != null and parent.id != null and parent.id != ''">
			AND a.parent_id = #{parent.id}
		</if>
		<choose>
			<when test="type != null and type != ''">
				and a.type = #{type}
			</when>
			<otherwise>
				and a.type = '2'
			</otherwise>
		</choose>
		OR a.id = #{id}
		ORDER BY a.type DESC
	</select>

	<select id="findByParentIdsCompany" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL} AND a.useable = '1'
		<choose>
			<when test="branchOffice!= null and branchOffice != ''">
				AND a.branch_office = #{branchOffice}
			</when>
			<otherwise>
				AND a.branch_office IS NULL
			</otherwise>
		</choose>
		AND a.type = 2
		AND (a.parent_ids LIKE
		<if test="dbName == 'mysql'">concat('%,',#{parentIds},',%')</if>
		 OR a.id = #{id})
		ORDER BY a.parent_ids,a.code ASC
	</select>

	<select id="findByParentIdsLikee" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
		AND a.parent_ids LIKE
		<if test="dbName == 'mysql'">concat('%',#{parentIds},'%')</if>
		<if test="name != null and name != ''">
			AND a.name like
			<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
			<if test="dbName == 'mysql'">CONCAT('%', #{name}, '%')</if>
		</if>
		<if test="topCompany != null and topCompany != ''">
			AND a.top_company like
			<if test="dbName == 'oracle'">'%'||#{topCompany}||'%'</if>
			<if test="dbName == 'mysql'">CONCAT('%', #{topCompany}, '%')</if>
		</if>
		ORDER BY a.type DESC
	</select>

    <select id="findByGroupId" resultType="Office">
        SELECT
        <include refid="officeColumns"/>
        FROM sys_office a
        <include refid="officeJoins"/>
        WHERE a.group_id = #{groupId} AND a.useable = '1'
    </select>

	<select id="findByType" resultType="Office">
		SELECT
		<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.useable = '1' AND  find_in_set(#{id}, a.parent_ids) or a.id = #{id}
	</select>
	
	<insert id="insert">
		INSERT INTO sys_office(
			id, 
			parent_id, 
			parent_ids, 
			area_id, 
			code,
			company_qrcode,
			name,
			sort, 
			type, 
			grade, 
			address, 
			zip_code, 
			master, 
			phone, 
			fax, 
			email, 
			create_by, 
			create_date, 
			update_by, 
			update_date, 
			remarks, 
			del_flag,
			useable,
			<!-- primary_person,-->
			<!-- deputy_person, -->
			logo,
			state,
			iscurrent,
			group_id,
			group_name,
            simple_name,
			branch_office,
            branch_simple_name,
			top_company,
            branch_no
		) VALUES (
			#{id},
			#{parent.id},
			#{parentIds},
			#{area.id},
			#{code},
			#{companyQrcode},
			#{name},
			#{sort},
			#{type},
			#{grade},
			#{address},
			#{zipCode},
			#{master},
			#{phone},
			#{fax},
			#{email},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag},
			#{useable},
			<!-- #{primaryPerson.id},-->
			<!-- #{deputyPerson.id},-->
			#{logo},
			#{state},
			#{iscurrent},
			#{groupId},
			#{groupName},
            #{simpleName},
            #{branchOffice},
            #{branchSimpleName},
            #{topCompany},
            #{branchNo}
		)
	</insert>

	<update id="update">
		UPDATE sys_office SET
			parent_id = #{parent.id},
			parent_ids = #{parentIds},
			area_id = #{area.id},
			code = #{code},
			company_qrcode = #{companyQrcode},
			name = #{name},
			type = #{type},
			grade = #{grade},
			address = #{address},
			zip_code = #{zipCode},
			master = #{master},
			phone = #{phone},
			fax = #{fax},
			email = #{email},
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks},
			useable=#{useable},
			<!-- primary_person=#{primaryPerson.id}, -->
			<!-- deputy_person=#{deputyPerson.id}, -->
			logo=#{logo},
			state=#{state},
			iscurrent=#{iscurrent},
			group_id=#{groupId},
			group_name=#{groupName},
            simple_name = #{simpleName},
            branch_simple_name = #{branchSimpleName},
			top_company  = #{topCompany},
            branch_no = #{branchNo}
		WHERE id = #{id}
	</update>

	<update id="updateParentIds">
		UPDATE sys_office SET
			parent_id = #{parent.id},
			parent_ids = #{parentIds}
		WHERE id = #{id}
	</update>
	<update id="saveBranchOffice">
		UPDATE sys_office SET
			branch_office =  #{branchOffice}
		WHERE id = #{id}
	</update>

	<update id="delete">
		DELETE FROM sys_office
		WHERE id = #{id} OR parent_ids LIKE
					<if test="dbName == 'oracle'">'%,'||#{id}||',%'</if>
					<if test="dbName == 'mysql'">CONCAT('%,', #{id}, ',%')</if>
	</update>
	<update id="updateUseable">
		UPDATE sys_office
		set useable = #{useable}
		WHERE id = #{id} OR parent_ids LIKE
					<if test="dbName == 'oracle'">'%,'||#{id}||',%'</if>
					<if test="dbName == 'mysql'">CONCAT('%,', #{id}, ',%')</if>
	</update>

	<update id="deleteByLogic">
		UPDATE sys_office SET
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id} OR parent_ids LIKE
					<if test="dbName == 'oracle'">'%,'||#{id}||',%'</if>
					<if test="dbName == 'mysql'">CONCAT('%,', #{id}, ',%')</if>
	</update>

    <select id="findByName" resultType="Office">
        SELECT * FROM sys_office a WHERE a.name=#{name} AND  find_in_set(#{parentIds},a.parent_ids)
        <choose>
            <when test="branchOffice !=null and branchOffice !=''">
                AND a.branch_office = #{branchOffice}
            </when>
            <otherwise>
                AND (a.branch_office is NULL OR a.branch_office = '')
            </otherwise>
        </choose>
    </select>
    <select id="findByTopCompany" resultType="Office">
        SELECT * FROM sys_office a WHERE a.top_company=#{topCompany} AND  find_in_set(#{parentIds},a.parent_ids)
        <choose>
            <when test="branchOffice !=null and branchOffice !=''">
                AND a.branch_office = #{branchOffice}
            </when>
            <otherwise>
                AND (a.branch_office is NULL OR a.branch_office = '')
            </otherwise>
        </choose>
    </select>

	<select id="findByCompanyTemplate" resultType="Office">
		SELECT
		a.id,
		a.parent_id AS "parent.id",
		a.parent_ids,
		a.area_id AS "area.id",
		a.code,
		a.company_qrcode,
		a.name,
		a.sort,
		a.type,
		a.grade,
		a.address,
		a.zip_code,
		a.master,
		a.phone,
		a.fax,
		a.email,
		a.remarks,
		a.create_by AS "createBy.id",
		a.create_date,
		a.update_by AS "updateBy.id",
		a.update_date,
		a.del_flag,
		a.useable AS useable,
		a.logo,
		a.state,
		a.iscurrent,
		a.group_id,
		a.group_name,
		a.simple_name AS "simpleName",
		a.branch_simple_name AS "branchSimpleName",
		a.top_company AS "topCompany",
		a.branch_no AS "branchNo",
		a.is_son AS "isSon"
		FROM sys_office a
		left join sys_office t2 on (t2.parent_ids like concat('%',a.id,'%') and t2.type='2')
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
			and a.type in('1','3')
			and ( a.parent_ids like concat('%',#{id},'%') or a.id =  #{id})
			and a.id not in (select te.branch_office from work_leave_template te where te.del_flag='0' )
		group by a.id
		ORDER BY a.code
	</select>

    <select id="treeDataAllOffice" resultType="Office">
		SELECT
		a.id,
		a.parent_id AS "parent.id",
		a.parent_ids,
		a.area_id AS "area.id",
		a.code,
		a.company_qrcode,
		a.name,
		a.sort,
		a.type,
		a.grade,
		a.address,
		a.zip_code,
		a.master,
		a.phone,
		a.fax,
		a.email,
		a.remarks,
		a.create_by AS "createBy.id",
		a.create_date,
		a.update_by AS "updateBy.id",
		a.update_date,
		a.del_flag,
		a.useable AS useable,
		a.logo,
		a.state,
		a.iscurrent,
		a.group_id,
		a.group_name,
		a.simple_name AS "simpleName",
		a.branch_simple_name AS "branchSimpleName",
		a.top_company AS "topCompany",
		a.branch_no AS "branchNo",
		a.is_son AS "isSon"
		FROM sys_office a
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
        <choose>
            <when test="branchOffice !=null and branchOffice != ''">
                and  a.branch_office=#{branchOffice}
            </when>
            <otherwise>
                and ( a.parent_id=#{parent.id} and a.type='2') or a.id=#{parent.id}
            </otherwise>
        </choose>
		ORDER BY a.code
	</select>

	<select id="getAllBranch" parameterType="java.lang.String" resultType="Office">
		 SELECT * FROM sys_office a WHERE a.parent_id=#{id} and a.type='3'
	</select>
	<select id="getAllBranchOffice" parameterType="java.lang.String" resultType="Office">
		 SELECT * FROM sys_office a WHERE a.parent_id=#{id} and a.type='2'
	</select>

	<select id="findCompanyList"  resultType="Office">
		 SELECT * FROM sys_office a WHERE a.type='1'
	</select>

    <update id="logicDelete">
        UPDATE sys_office SET del_flag = '1' WHERE id = #{id} or find_in_set(#{id},parent_ids)
    </update>

    <select id="findBranchOfficeId" resultType="String">
        SELECT id from sys_office a
        <where>
            <choose>
                <when test="branchOffice !=null and branchOffice !=''">
                   a.branch_office = #{branchOffice}
                </when>
                <otherwise>
                    a.parent_id = #{companyId} and a.branch_office IS NULL
                </otherwise>
            </choose>
            AND a.type = '2'
            AND a.del_flag = '0'
        </where>
    </select>
    <select id="findChildOfficeId" resultType="String">
        SELECT id from sys_office a
        where a.parent_id = #{parentId}
            AND a.type = '2'
            AND a.del_flag = '0'
    </select>
</mapper>