<?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.qianhe.zqbf.mapper.ZqbfXmzTjfxMapper">
    <!--  返回实体  -->
    <resultMap id="GlqsbslResult" type="GlqsbslVo">
        <result property="dept_id" column="dept_id"/>
        <result property="dept_name" column="dept_name"/>
        <result property="tbsl" column="tbsl"/>
        <result property="ywcsl" column="ywcsl"/>
    </resultMap>
    <!--  条件查询  -->
    <select id="glqsbsl" parameterType="ZqczTjfxQuery" resultMap="GlqsbslResult">
        WITH RECURSIVE child_tree AS (
        SELECT dept_id, dept_id AS root_id
        FROM sys_dept
        <where>
            <if test="dwid != null">
                parent_id = #{dwid}
            </if>
        </where>
        UNION ALL
        SELECT d.dept_id, ct.root_id
        FROM sys_dept d
        INNER JOIN child_tree ct ON d.parent_id = ct.dept_id
        ),
        stats_data AS (
        SELECT
        ct.root_id,
        COUNT(bfxq.dwid) AS tbsl,
        SUM(CASE WHEN bfxq.dqzt >= 7 THEN 1 ELSE 0 END) AS ywcsl
        FROM child_tree ct
        LEFT JOIN zqbf_xmz_bfxq bfxq ON ct.dept_id = bfxq.dwid
        <!-- 时间区间重叠判断：两个区间有交集 -->
        <if test="kssj != null and jssj != null">
            AND NOT (bfxq.bfjssj &lt; #{kssj} OR bfxq.bfkssj &gt; #{jssj})
        </if>
        <if test="kssj != null and jssj == null">
            AND bfxq.bfkssj &gt;= #{kssj}
        </if>
        <if test="jssj != null and kssj == null">
            AND bfxq.bfjssj &lt;= #{jssj}
        </if>
        INNER JOIN zqbf_xmz_bfxm bfxm ON bfxq.xmid = bfxm.xmid
        <if test="xmzb != null and xmzb != ''">
            AND bfxm.xmzb = #{xmzb}
        </if>
        GROUP BY ct.root_id
        )
        SELECT
        dept_id,
        dept_name,
        COALESCE(tbsl, 0) AS tbsl,
        COALESCE(ywcsl, 0) AS ywcsl
        FROM (
        -- 情况1：有子节点时
        SELECT
        d.dept_id,
        d.dept_name,
        stats.tbsl,
        stats.ywcsl,
        1 AS sort_order
        FROM sys_dept d
        CROSS JOIN (
        SELECT COUNT(*) AS cnt
        FROM sys_dept
        <where>
            <if test="dwid != null">
                parent_id = #{dwid}
            </if>
        </where>
        ) has_child
        LEFT JOIN stats_data stats ON d.dept_id = stats.root_id
        WHERE has_child.cnt > 0
        <if test="dwid != null">
            AND d.parent_id = #{dwid}
        </if>

        UNION ALL

        -- 情况2：没有子节点时
        SELECT
        dept_id,
        dept_name,
        COALESCE(tbsl, 0) AS tbsl,
        COALESCE(ywcsl, 0) AS ywcsl,
        2 AS sort_order
        FROM sys_dept
        LEFT JOIN (
        SELECT
        COUNT(bfxq.dwid) AS tbsl,
        SUM(CASE WHEN bfxq.dqzt >= 7 THEN 1 ELSE 0 END) AS ywcsl
        FROM zqbf_xmz_bfxq bfxq
        INNER JOIN zqbf_xmz_bfxm bfxm ON bfxq.xmid = bfxm.xmid
        <if test="xmzb != null and xmzb != ''">
            AND bfxm.xmzb = #{xmzb}
        </if>
        <where>
            <if test="dwid != null">
                AND bfxq.dwid = #{dwid}
            </if>
            <!-- 时间区间重叠判断 -->
            <if test="kssj != null and jssj != null">
                AND NOT (bfxq.bfjssj &lt; #{kssj} OR bfxq.bfkssj &gt; #{jssj})
            </if>
            <if test="kssj != null and jssj == null">
                AND bfxq.bfkssj &gt;= #{kssj}
            </if>
            <if test="jssj != null and kssj == null">
                AND bfxq.bfjssj &lt;= #{jssj}
            </if>
        </where>
        ) current_stats ON 1=1
        <where>
            <if test="dwid != null">
                dept_id = #{dwid}
            </if>
        </where>
        AND NOT EXISTS (
        SELECT 1 FROM sys_dept
        <where>
            <if test="dwid != null">
                parent_id = #{dwid}
            </if>
        </where>
        )
        ) t
        ORDER BY sort_order, tbsl DESC, ywcsl DESC
    </select>
</mapper>