<?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">
<!-- 不使用namespace的话sql搜索定位会比较方便 -->
<mapper namespace="com.blueland.dao.TucOrgDao">
    <resultMap id="RM_TucOrgEntity" type="com.blueland.bo.Org">
        <result property="id" column="id"/>
        <result property="orgId" column="org_id"/>
        <result property="orgFullName" column="org_full_name"/>
        <result property="orgName" column="org_name"/>
        <result property="orgFullSpells" column="org_full_spells"/>
        <result property="orgSpells" column="org_spells"/>
        <result property="orgType" column="org_type"/>
        <result property="orgLevel" column="org_level"/>
        <result property="orgParentId" column="org_parent_id"/>
        <result property="orgParentName" column="org_parent_name"/>
        <result property="companyId" column="company_id"/>
        <result property="orgBranchLeader" column="org_branch_leader"/>
        <result property="orgBranchLeaderName" column="org_branch_leader_name"/>
        <result property="orgWorkAddress" column="org_work_address"/>
        <result property="orgWorkPhone" column="org_work_phone"/>
        <result property="orgStatus" column="org_status"/>
        <result property="orgSort" column="org_sort"/>
        <result property="orgCreateTime" column="org_create_time"/>
        <result property="orgCreator" column="org_creator"/>
        <result property="orgUpdateTime" column="org_update_time"/>
        <result property="orgUpdator" column="org_updator"/>
        <result property="companyName" column="company_name"/>
        <result property="orgMgrType" column="org_mgr_type"/>
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="orgSubType" column="org_sub_type"/>
        <result property="orgHrId" column="org_hr_id"/>
        <result property="staffing" column="staffing"/>
        <result property="orgWorkType" column="org_work_type"/>
        <result property="appId" column="app_id"/>
    </resultMap>

    <sql id="TucOrgEntityColumns">
        id,
        org_id,
        org_full_name,
        org_name,
        org_full_spells,
        org_spells,
        org_type,
        org_level,
        org_parent_id,
        org_parent_name,
        company_id,
        org_branch_leader,
        org_branch_leader_name,
        org_work_address,
        org_work_phone,
        org_status,
        org_sort,
        org_create_time,
        org_creator,
        org_update_time,
        org_updator,
        company_name,
        org_mgr_type,
        dept_id,
        dept_name,
        org_sub_type,
        org_hr_id,
        staffing,
        org_work_type,
        app_id
    </sql>

    <sql id="TucOrgEntityQueryColumns">
        id,
        org_id,
        org_full_name,
        org_name,
        org_full_spells,
        org_spells,
        org_type,
        org_level,
        org_parent_id,
        org_parent_name,
        company_id,
        org_branch_leader,
        org_branch_leader_name,
        org_work_address,
        org_work_phone,
        org_status,
        org_sort,
        org_create_time,
        org_creator,
        org_update_time,
        org_updator,
        company_name,
        org_mgr_type,
        dept_id,
        dept_name,
        org_sub_type,
        org_hr_id,
        staffing,
        org_work_type,
        app_id
    </sql>
    <!-- useGeneratedKeys="true" keyProperty="xxx" for sqlserver and mysql -->
<!--    <insert id="insert" useGeneratedKeys="false" keyProperty="orgId">-->
<!--        INSERT INTO t_uc_org (<include refid="TucOrgEntityColumns"/>)-->
<!--            VALUES (#{id,jdbcType=VARCHAR},-->
<!--                    #{orgId,jdbcType=VARCHAR},-->
<!--                    #{orgFullName,jdbcType=VARCHAR},-->
<!--                    #{orgName,jdbcType=VARCHAR},-->
<!--                    #{orgFullSpells,jdbcType=VARCHAR},-->
<!--                    #{orgSpells,jdbcType=VARCHAR},-->
<!--                    #{orgType,jdbcType=VARCHAR},-->
<!--                    #{orgLevel,jdbcType=INTEGER},-->
<!--                    #{orgParentId,jdbcType=VARCHAR},-->
<!--                    #{orgParentName,jdbcType=VARCHAR},-->
<!--                    #{companyId,jdbcType=VARCHAR},-->
<!--                    #{orgBranchLeader,jdbcType=VARCHAR},-->
<!--                    #{orgBranchLeaderName,jdbcType=VARCHAR},-->
<!--                    #{orgWorkAddress,jdbcType=VARCHAR},-->
<!--                    #{orgWorkPhone,jdbcType=VARCHAR},-->
<!--                    #{orgStatus,jdbcType=VARCHAR},-->
<!--                    #{orgSort,jdbcType=INTEGER},-->
<!--                    #{orgCreateTime,jdbcType=TIMESTAMP},-->
<!--                    #{orgCreator,jdbcType=VARCHAR},-->
<!--                    #{orgUpdateTime,jdbcType=TIMESTAMP},-->
<!--                    #{orgUpdator,jdbcType=VARCHAR},-->
<!--                    #{companyName,jdbcType=VARCHAR},-->
<!--                    #{orgMgrType,jdbcType=VARCHAR},-->
<!--                    #{deptId,jdbcType=VARCHAR},-->
<!--                    #{deptName,jdbcType=VARCHAR},-->
<!--                    #{orgSubType,jdbcType=VARCHAR},-->
<!--                    #{orgHrId,jdbcType=VARCHAR},-->
<!--                    #{staffing,jdbcType=VARCHAR},-->
<!--                    #{orgWorkType,jdbcType=VARCHAR},-->
<!--                    #{appId,jdbcType=VARCHAR})-->
<!--    </insert>-->

    <select id="get" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM t_uc_org
        WHERE org_id = #{orgId,jdbcType=VARCHAR}
    </select>

    <select id="findOrgIdList" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM t_uc_org
        WHERE org_parent_id = #{bo.orgId,jdbcType=VARCHAR}
          and org_status = 'QY'
        order by org_sort
    </select>

    <!--
    	分页查询已经使用Dialect进行分页,也可以不使用Dialect直接编写分页
    	因为分页查询将传 offset,pageSize,lastRows 三个参数,不同的数据库可以根于此三个参数属性应用不同的分页实现
    -->
    <select id="findList" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM t_uc_org
        <include refid="findList_where"/>

        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
    </select>

    <select id="findPage" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM t_uc_org
        <include refid="findList_where"/>

        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
    </select>

    <select id="getOne" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM t_uc_org
        <include refid="findList_where"/>
        and org_status = "QY"
        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
        Limit 1
    </select>

    <select id="getByAppId" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM t_uc_org
        <include refid="findList_where"/>
        and org_status = "QY"
        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
        Limit 1
    </select>


    <sql id="findList_where">
        <!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用rapid中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用，具体请查看Ognl类 -->
        <where>
            <if test="@Ognl@isNotEmpty(bo.id)">
                AND id = #{bo.id,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgId)">
                AND org_id = #{bo.orgId,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgFullName)">
                AND org_full_name = #{bo.orgFullName,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgName)">
                AND org_name = #{bo.orgName,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgFullSpells)">
                AND org_full_spells = #{bo.orgFullSpells,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgSpells)">
                AND org_spells = #{bo.orgSpells,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgType)">
                AND org_type = #{bo.orgType,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgLevel)">
                AND org_level = #{bo.orgLevel,jdbcType=INTEGER}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgParentId)">
                AND org_parent_id = #{bo.orgParentId,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgParentName)">
                AND org_parent_name = #{bo.orgParentName,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.companyId)">
                AND (company_id = #{bo.companyId,jdbcType=VARCHAR}
                OR dept_id like concat(#{bo.companyId},'%'))
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgBranchLeader)">
                AND org_branch_leader = #{bo.orgBranchLeader,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgBranchLeaderName)">
                AND org_branch_leader_name = #{bo.orgBranchLeaderName,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgWorkAddress)">
                AND org_work_address = #{bo.orgWorkAddress,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgWorkPhone)">
                AND org_work_phone = #{bo.orgWorkPhone,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgStatus)">
                AND org_status = #{bo.orgStatus,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgSort)">
                AND org_sort = #{bo.orgSort,jdbcType=INTEGER}
            </if>

            <if test="@Ognl@isNotEmpty(bo.orgCreator)">
                AND org_creator = #{bo.orgCreator,jdbcType=VARCHAR}
            </if>

            <if test="@Ognl@isNotEmpty(bo.orgUpdator)">
                AND org_updator = #{bo.orgUpdator,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.companyName)">
                AND company_name = #{bo.companyName,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgMgrType)">
                AND org_mgr_type = #{bo.orgMgrType,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.deptId)">
                AND dept_id = #{bo.deptId}
            </if>
            <if test="@Ognl@isNotEmpty(bo.likeDeptId)">
                AND dept_id like concat('%', #{bo.likeDeptId}, '%')
            </if>
            <if test="@Ognl@isNotEmpty(bo.deptName)">
                AND dept_name = #{bo.deptName,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgSubType)">
                AND org_sub_type = #{bo.orgSubType,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgHrId)">
                AND org_hr_id = #{bo.orgHrId,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.staffing)">
                AND staffing = #{bo.staffing,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.orgWorkType)">
                AND org_work_type = #{bo.orgWorkType,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.queryOrg)">
                AND (org_name like concat('%',#{bo.queryOrg},'%') OR org_id = #{bo.queryOrg} )
            </if>
            <if test="@Ognl@isNotEmpty(bo.appId)">
                and app_id = #{bo.appId,jdbcType=VARCHAR}
            </if>
            <if test="@Ognl@isNotEmpty(bo.deptIds)">
                and dept_id in
                <foreach collection="bo.deptIds" item="id" index="index" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="@Ognl@isNotEmpty(bo.erpCodes)">
                and org_spells in
                <foreach collection="bo.erpCodes" item="code" index="index" open="(" separator="," close=")">
                    #{code}
                </foreach>
            </if>
        </where>
    </sql>

    <!-- 没有分页的组织列表数据 -->
    <select id="getListNoPage" resultMap="RM_TucOrgEntity">
        SELECT *
        FROM t_uc_org
        <include refid="findList_where"/>
        and org_type != 'SYGS'
        and org_type != 'GYS'
        and org_status = 'QY'
        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
    </select>

    <select id="getOrgPage" resultMap="RM_TucOrgEntity">
        SELECT *
        FROM t_uc_org
        <include refid="findList_where"/>
        and org_type != 'SYGS'
        and org_type != 'GYS'
        and org_status = 'QY'
        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
    </select>

    <select id="getOrgChildLst" resultMap="RM_TucOrgEntity">
        SELECT *
        FROM t_uc_org
                where org_type = #{bo.orgType}
                  AND FIND_IN_SET(org_id, getOrgChildLst(#{bo.orgId}, #{bo.orgType}))
        <if test="@Ognl@isNotEmpty(bo.orgStatus)">
            AND org_status = #{bo.orgStatus}
        </if>
        <if test="@Ognl@isNotEmpty(bo.orgWorkType)">
            AND org_work_type = #{bo.orgWorkType}
        </if>
        <if test="@Ognl@isNotEmpty(bo.orgFullName)">
            AND org_full_name like concat('%', #{bo.orgFullName}, '%')
        </if>
        <if test="@Ognl@isNotEmpty(bo.orgName)">
            AND org_name like concat('%', #{bo.orgName}, '%')
        </if>
        <if test="@Ognl@isNotEmpty(bo.queryOrg)">
            AND (org_name like concat('%', #{bo.queryOrg}, '%') OR org_id = #{bo.queryOrg})
        </if>
        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
    </select>

    <select id="getOrgChildPage" resultMap="RM_TucOrgEntity">
        SELECT *
        FROM t_uc_org
        where org_type = #{bo.orgType}
        AND FIND_IN_SET(org_id, getOrgChildLst(#{bo.orgId}, #{bo.orgType}))
        <if test="@Ognl@isNotEmpty(bo.orgStatus)">
            AND org_status = #{bo.orgStatus}
        </if>
        <if test="@Ognl@isNotEmpty(bo.orgWorkType)">
            AND org_work_type = #{bo.orgWorkType}
        </if>
        <if test="@Ognl@isNotEmpty(bo.orgFullName)">
            AND org_full_name like concat('%', #{bo.orgFullName}, '%')
        </if>
        <if test="@Ognl@isNotEmpty(bo.orgName)">
            AND org_name like concat('%', #{bo.orgName}, '%')
        </if>
        <if test="@Ognl@isNotEmpty(bo.queryOrg)">
            AND (org_name like concat('%', #{bo.queryOrg}, '%') OR org_id = #{bo.queryOrg})
        </if>
        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
    </select>

    <select id="searchOrg" resultType="com.blueland.bo.TreeEntity">
        SELECT org_id        as id,
               ORG_NAME      as NAME,
               dept_id       as deptId,
               dept_name     as deptName,
               org_id        as orgId,
               org_name      as orgName,
               company_id    as companyId,
               company_name  as companyName,
               org_parent_id as parentId,
               'org'         as type,
               'false'       as leaf,
               ''            as staffNum,
               ''            as staffCode
        FROM t_uc_org
        <include refid="findList_where"/>

        <if test="@Ognl@isNotEmpty(bo.sortColumns)">
            ORDER BY ${bo.sortColumns}
        </if>
    </select>

    <select id="getChildLstByOrgId" resultMap="RM_TucOrgEntity">
        select *
        FROM t_uc_org
        where dept_id
                      like CONCAT((select dept_id from t_uc_org where org_id = #{orgId}), '%')
    </select>

    <select id="list" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM
          t_uc_org
        WHERE org_status = 'QY'
        and app_id is not null and app_id != ''
        <if test="appId != null and appId != ''">
            and app_id = #{appId}
        </if>
    </select>

    <select id="countOrgById" resultType="integer">
        select count(*) from t_uc_org
        WHERE org_status = 'QY'
        and dept_id = #{id} and app_id = #{appId}
    </select>

    <delete id="deleteOrgByAppId">
        delete from t_uc_org WHERE app_id = #{appId}
    </delete>

    <select id="getTopOrgId" resultType="string">
        select org_id from t_uc_org  where org_parent_id = '0'  and org_status = "QY" limit 1
    </select>

    <select id="getSellOrgId" resultType="string">
        select org_id from t_uc_org  where dept_id = #{deptCode}  and org_status = "QY" limit 1
    </select>

    <select id="getAppIdByParentId" resultType="string">
        select app_id from t_uc_org  where org_id = #{parentId}  and org_status = "QY" limit 1
    </select>

    <select id="getOrgIdByAppId" resultType="string">
        select org_id from t_uc_org
        where org_id = #{deptId}
          and org_status = "QY"
          and app_id = #{appId}
          limit 1
    </select>

    <select id="getByOgId" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM t_uc_org
        WHERE org_id = #{orgId} limit 1
    </select>

    <select id="getByOgIdAndType" resultMap="RM_TucOrgEntity">
        SELECT
        <include refid="TucOrgEntityQueryColumns"/>
        FROM t_uc_org
        WHERE org_id = #{orgId} and org_type = #{type}
        limit 1
    </select>

</mapper>

