<?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.hdkj.lt.core.sys.dao.SysStruMapper">

    <select id="listByCondition" resultType="com.hdkj.lt.core.sys.model.dto.OrgInfoDto">
        SELECT
            o.ORGAN_NAME,
            o.SHORT_NAME,
            o.ORGAN_CODE,
            o.ORGAN_FNAME,
            o.ORGAN_TYPE,
            s.STRU_ORDER,
            s.STRU_ID,
            s.PARENT_ID
        FROM SYS_STRU s
        INNER JOIN SYS_ORGAN o ON s.ORGAN_ID = o.ORGAN_ID
        WHERE s.STRU_TYPE != '9' AND (s.DEL_FLAG = '0' or s.DEL_FLAG is null)
        <if test="params.unitIds != null and params.unitIds.size() > 0">
            AND s.STRU_ID IN
            <foreach collection="params.unitIds" open="(" item="unitId" separator="," close=")">
                #{unitId, jdbcType=BIGINT}
            </foreach>
        </if>
        <if test="params.parentId != null">
            AND s.PARENT_ID = #{params.parentId, jdbcType=BIGINT}
        </if>
        <if test="params.unitName != null and params.unitName != ''">
            AND o.ORGAN_NAME LIKE CONCAT('%',#{params.unitName},'%')
        </if>
        <if test="params.unitTypes != null and params.unitTypes.size() > 0">
            AND s.STRU_TYPE IN
            <foreach collection="params.unitTypes" open="(" item="unitType" separator="," close=")">
                #{unitType, jdbcType=VARCHAR}
            </foreach>
        </if>
    </select>

    <select id="getUserNodeByOrgType" useCache="true" resultType="java.lang.Integer">
        SELECT if(t.userNode &lt; #{userNode}, #{userNode}, t.userNode) AS userNode
        FROM (
            SELECT CASE
                WHEN t.STRU_TYPE = '4' THEN '2'
                WHEN t.STRU_TYPE = '5' THEN '3'
                WHEN t.STRU_TYPE = '1' THEN '0'
                WHEN t.STRU_TYPE = '6' THEN '4'
                ELSE #{userNode} END AS userNode
            FROM SYS_STRU t
            WHERE t.STRU_ID = #{unitCode}
        ) t
    </select>

    <select id="queryProvinceStruList" resultType="com.hdkj.lt.core.sys.model.po.SysStru">
        SELECT STRU_ID, PARENT_ID, STRU_TYPE, ORGAN_ALIAS, STRU_ORDER, STRU_SEQ,STRU_FID
        FROM SYS_STRU
        WHERE DEL_FLAG ='0' AND STRU_TYPE IN ( '1', '4', '5' ) AND STRU_FID LIKE '/13213/14015/%'
        UNION ALL
        SELECT STRU_ID AS id, PARENT_ID, STRU_TYPE, ORGAN_ALIAS, STRU_ORDER, STRU_SEQ,STRU_FID
        FROM SYS_STRU
        WHERE DEL_FLAG ='0' AND STRU_TYPE = '6'
        AND PARENT_ID IN (SELECT STRU_ID FROM SYS_STRU WHERE STRU_TYPE = '5' AND STRU_FID LIKE '/13213/14015/%')
        ORDER BY STRU_TYPE, STRU_SEQ, STRU_ORDER
    </select>

    <select id="selectOrganByIds" resultType="com.hdkj.lt.core.sys.model.po.OrganizationBo">
        SELECT
        s.STRU_ID AS id,
        s.PARENT_ID AS parentId,
        o.ORGAN_CODE AS organCode,
        o.ORGAN_NAME AS organName,
        o.ORGAN_TYPE AS organType,
        po.ORGAN_CODE AS parentOrganCode,
        po.ORGAN_NAME AS parentOrganName,
        po.ORGAN_TYPE AS parentOrganType
        FROM SYS_STRU s
        LEFT JOIN SYS_ORGAN o ON o.ORGAN_ID = s.ORGAN_ID
        LEFT JOIN SYS_STRU ps ON s.PARENT_ID = ps.STRU_ID
        LEFT JOIN SYS_ORGAN po ON ps.ORGAN_ID = po.ORGAN_ID
        WHERE s.DEL_FLAG = '0' and s.IS_EMPLOYEE is null and s.PARENT_ID = #{parentId}
        ORDER BY s.STRU_ORDER
    </select>

    <select id="getTeamChildrenStru" resultType="java.lang.Long">
        WITH RECURSIVE VIEW_CHILD AS (
            SELECT st.STRU_ID, st.ORGAN_ALIAS,st.PARENT_ID,st.STRU_TYPE,st.STRU_LEVEL,st.STRU_ORDER FROM SYS_STRU st WHERE st.STRU_ID=#{struId}
            UNION ALL
            SELECT s.STRU_ID, s.ORGAN_ALIAS,s.PARENT_ID,s.STRU_TYPE,s.STRU_LEVEL,s.STRU_ORDER FROM SYS_STRU s INNER JOIN VIEW_CHILD p ON s.PARENT_ID = p.STRU_ID
            WHERE s.STRU_TYPE='6'
        )
        SELECT STRU_ID FROM VIEW_CHILD
    </select>


    <select id="getStruById" resultType="com.hdkj.lt.core.sys.model.po.SysStru">
        SELECT su.stru_Id    struId,
               su.stru_type  struType,
               sn.short_name name
        from SYS_STRU su
                 left join SYS_ORGAN sn on su.ORGAN_ID = sn.ORGAN_ID
        where su.stru_id = #{struId}
          and su.stru_type in ('1', '4', '5') and su.DEL_FLAG = '0'
    </select>


    <select id="getStruByParentIds" resultType="com.hdkj.lt.core.sys.model.po.SysStru">
        SELECT su.stru_Id struId,
        su.stru_type struType,
        sn.short_name name,
        su.parent_id parentId
        from SYS_STRU su
        left join SYS_ORGAN sn on su.ORGAN_ID = sn.ORGAN_ID
        where su.parent_id in
        <foreach collection="struIdList" open="(" item="struId" separator="," close=")">
            #{struId}
        </foreach>
        and su.stru_type in ('4', '5') and su.DEL_FLAG = '0'
    </select>


    <select id="getStruByChildId" resultType="com.hdkj.lt.core.sys.model.po.SysStru">
        SELECT su.dept_id    struId,
               su.stru_type  struType,
               sn.short_name name
        from sys_dept su
                 left join SYS_ORGAN sn on su.ORGAN_ID = sn.ORGAN_ID
        where su.stru_id = (select p.parent_id from SYS_STRU p where p.stru_id = #{struId} and p.DEL_FLAG = '0') and su.DEL_FLAG = '0'
    </select>
    <select id="getCityByPsrId" resultType="com.hdkj.lt.core.sys.model.dto.OrgInfoDto">

        select t.STRU_ID struId,
               o.ORGAN_NAME organName
        from dwd_shb_ds_feeder_base b
                 left join SYS_STRU t
                           on t.ISC_ORG_ID = b.city
                 left join SYS_ORGAN o
                           on t.ORGAN_ID = o.ORGAN_ID
        where b.psr_id = #{psrId}
    </select>

    <select id="getCountyByPsrId" resultType="com.hdkj.lt.core.sys.model.dto.OrgInfoDto">
        select t.dept_id struId,
               t.dept_name organName
        from dwd_shb_ds_feeder_base b
                 left join sys_dept t
                           on t.isc_org_id = b.maint_org
        where b.psr_id = #{psrId}
    </select>

    <select id="getStruList" resultType="com.hdkj.lt.core.sys.model.po.SysStru">
        WITH RECURSIVE recur AS (
            SELECT st.STRU_ID,st.ORGAN_ID, st.ORGAN_ALIAS,st.PARENT_ID,st.STRU_TYPE,st.STRU_LEVEL,st.STRU_ORDER,st.ISC_ORG_ID FROM SYS_STRU st WHERE st.STRU_TYPE= '1' and DEL_FLAG ='0'
            UNION
            SELECT s.STRU_ID, s.ORGAN_ID, s.ORGAN_ALIAS,s.PARENT_ID,s.STRU_TYPE,s.STRU_LEVEL,s.STRU_ORDER,s.ISC_ORG_ID FROM SYS_STRU s INNER JOIN recur p ON s.PARENT_ID = p.STRU_ID
            WHERE s.STRU_TYPE in ('4','5') and DEL_FLAG = '0'
        )
        SELECT r.*,sn.short_name name
        FROM recur r  left join SYS_ORGAN sn on r.ORGAN_ID = sn.ORGAN_ID order by STRU_TYPE ,STRU_LEVEL, STRU_ORDER
    </select>


</mapper>
