<?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.ejweb.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.name,
        a.sort,
        a.type,
        a.grade,
        a.USEABLE AS "useable",
        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",
        p.name AS "parent.name",
        p.code AS "parent.code",
        ar.name AS "area.name",
        ar.parent_ids AS "area.parentIds",
        pp.name AS "primaryPerson.name",
        dp.name AS "deputyPerson.name"
    </sql>
    
    <sql id="officeJoins">
        LEFT JOIN ct_bbtc_sys_office p ON p.id = a.parent_id
        LEFT JOIN ct_bbtc_sys_area ar ON ar.id = a.area_id
        LEFT JOIN ct_bbtc_sys_user pp ON pp.id = a.primary_person
        LEFT JOIN ct_bbtc_sys_user dp ON dp.id = a.deputy_person
    </sql>
    
    <select id="get" resultType="Office">
        SELECT
            <include refid="officeColumns"/>
        FROM ct_bbtc_sys_office a
        <include refid="officeJoins"/>
        WHERE a.id = #{id}
    </select>
    
    <select id="findList" resultType="Office">
        SELECT
            <include refid="officeColumns"/>
        FROM ct_bbtc_sys_office a
        <include refid="officeJoins"/>
        WHERE a.del_flag = #{DEL_FLAG_NORMAL}
        <!-- 数据范围过滤 -->
        ${sqlMap.dsf}
        OR a.id = #{currentUser.office.id}
        ORDER BY a.code
    </select>
            
    <select id="findAllList" resultType="Office">
        SELECT
            <include refid="officeColumns"/>
        FROM ct_bbtc_sys_office a
        <include refid="officeJoins"/>
        WHERE a.del_flag = #{DEL_FLAG_NORMAL}
        ORDER BY a.code
    </select>

    <select id="findByParentIdsLike" resultType="Office">
        SELECT
            <include refid="officeColumns"/>
        FROM ct_bbtc_sys_office a
        <include refid="officeJoins"/>
        WHERE a.del_flag = #{DEL_FLAG_NORMAL} AND a.parent_ids LIKE #{parentIds}
        ORDER BY a.code
    </select>

    <!-- zhujf start-->
    <select id="findByParentId" resultType="Office">
        SELECT
        <include refid="officeColumns"/>
        FROM ct_bbtc_sys_office a
        <include refid="officeJoins"/>
        WHERE a.del_flag = #{DEL_FLAG_NORMAL} AND a.parent_id = #{parent.id}
        ORDER BY a.code
    </select>
    <!-- zhujf end-->
    <select id="findByOfficeName" resultType="Office">
        SELECT
        <include refid="officeColumns"/>
        FROM ct_bbtc_sys_office a
        <include refid="officeJoins"/>
        WHERE a.del_flag = '0' AND a.name = #{officeName}

    </select>

    <select id="getCompany" parameterType="Office" resultType="Office">
        SELECT
        <include refid="officeColumns"/>
        FROM ct_bbtc_sys_office a
        <include refid="officeJoins"/>
        WHERE a.del_flag = #{DEL_FLAG_NORMAL} AND a.parent_id='1'
        <if test="dbName == 'oracle'"> AND rownum &lt;= 1</if>
        <if test="dbName == 'mysql'"> limit 1</if>
    </select>
    <insert id="insert">
        INSERT INTO ct_bbtc_sys_office(
            id, 
            parent_id, 
            parent_ids, 
            area_id, 
            code, 
            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
        ) VALUES (
            #{id}, 
            #{parent.id}, 
            #{parentIds}, 
            #{area.id}, 
            #{code}, 
            #{name}, 
            #{sort}, 
            #{type}, 
            #{grade}, 
            #{address}, 
            #{zipCode}, 
            #{master}, 
            #{phone}, 
            #{fax}, 
            #{email}, 
            #{createBy.id}, 
            #{createDate}, 
            #{updateBy.id}, 
            #{updateDate}, 
            #{remarks}, 
            #{delFlag},
            #{useable},
            #{primaryPerson.id},
            #{deputyPerson.id}
        )
    </insert>
    
    <update id="update">
        UPDATE ct_bbtc_sys_office SET
            parent_id = #{parent.id}, 
            parent_ids = #{parentIds}, 
            area_id = #{area.id}, 
            code = #{code}, 
            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}
        WHERE id = #{id}
    </update>
    
    <update id="updateParentIds">
        UPDATE ct_bbtc_sys_office SET
            parent_id = #{parent.id}, 
            parent_ids = #{parentIds}
        WHERE id = #{id}
    </update>
    
    <update id="delete">
        UPDATE ct_bbtc_sys_office SET
            del_flag = #{DEL_FLAG_DELETE}
        WHERE id = #{id} OR parent_ids LIKE 
                    <if test="dbName == 'oracle'">'%,'||#{id}||',%'</if>
                    <if test="dbName == 'mssql'">'%,'+#{id}+',%'</if>
                    <if test="dbName == 'mysql'">CONCAT('%,', #{id}, ',%')</if>
    </update>
    
</mapper>