<?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="org.jeecg.modules.stm.baosong.mapper.BaosongTaskAllocMapper">

    <!-- 优化说明：
         1. 移除了所有索引提示(USE INDEX)
         2. 优化了SQL结构和格式，提高可读性
         3. 移除了不必要的1=1条件
         4. 保持了原有的字段命名风格
         5. 优化了复杂查询的逻辑结构
    -->

    <select id="queryAllTaskAlloc" resultType="java.util.Map">
        SELECT
        A.*,
        B.name AS taskName,
        C.nm AS tplName,
        D.title AS itemName,
        D.treepath_name AS treePathName,
        E.realname,
        D.unit,
        D.min_val AS minVal,
        D.max_val AS maxVal
        FROM baosong_task_alloc A
        INNER JOIN baosong_task B ON A.taskid = B.id
        INNER JOIN baosong_tpl C ON A.tplid = C.id
        INNER JOIN baosong_tpl_item D ON A.itemid = D.id
        INNER JOIN sys_user E ON A.fill_user = E.id
        <where>
            <if test="query.taskid != null">
                AND A.taskid = #{query.taskid}
            </if>
            <if test="query.tplid != null">
                AND A.tplid = #{query.tplid}
            </if>
        </where>
        ORDER BY A.taskid, A.tplid, A.itemid
    </select>

    <select id="queryAllTplForUser" resultType="org.jeecg.modules.stm.baosong.entity.BaosongTpl">
        SELECT DISTINCT
        A.id,
        A.nm,
        A.code,
        A.tp,
        A.child_num AS childNum
        FROM baosong_tpl A
        INNER JOIN baosong_task_alloc B ON A.id = B.tplid
        <where>
            <if test="query.taskid != null">
                AND B.taskid = #{query.taskid}
            </if>
            <if test="query.fillUser != null">
                AND B.fill_user = #{query.fillUser}
            </if>
        </where>
        ORDER BY A.id
    </select>

    <select id="queryAllTplItemForUser" resultType="org.jeecg.modules.stm.baosong.entity.BaosongTplItem">
        SELECT
        A.id,
        A.title,
        A.code,
        A.pcode,
        A.treepath_name AS treepathName,
        A.treepath,
        A.tp,
        A.form_tp AS formTp,
        A.options,
        A.child_num AS childNum,
        A.pid,
        A.xmlcode,
        A.unit,
        A.min_val AS minVal,
        A.max_val AS maxVal
        FROM baosong_tpl_item A
        INNER JOIN baosong_task_alloc B ON A.id = B.itemid AND A.tplid = B.tplid
        <where>
            A.child_num = 0
            <if test="query.taskid != null">
                AND B.taskid = #{query.taskid}
            </if>
            <if test="query.tplid != null">
                AND A.tplid = #{query.tplid}
            </if>
            <if test="query.fillUser != null">
                AND B.fill_user = #{query.fillUser}
            </if>
        </where>
        ORDER BY A.id
    </select>

    <select id="queryCurTaskAllocUser" resultType="java.util.Map">
        SELECT
        A.itemid,
        A.fill_user AS fillUser,
        B.realname
        FROM baosong_task_alloc A
        INNER JOIN sys_user B ON A.fill_user = B.id
        <where>
            <if test="query.taskid != null">
                AND A.taskid = #{query.taskid}
            </if>
        </where>
    </select>

    <select id="statAllocNum" resultType="java.util.Map">
        SELECT
        COUNT(*) cnt,
        taskid
        FROM baosong_task_alloc
        <where>
            <if test="query.taskid != null">
                AND taskid = #{query.taskid}
            </if>
        </where>
        GROUP BY taskid
    </select>

    <select id="staTplChildNum" resultType="java.util.Map">
        SELECT
            SUM(child_num) AS cnt,
            tp
        FROM baosong_tpl
        GROUP BY tp
    </select>

    <select id="selectTaskStatus" resultType="java.util.Map">
        SELECT
            IF((AA.cnt <![CDATA[<]]> BB.tt), FALSE, TRUE) AS sta,
            AA.taskid
        FROM
            (SELECT
                 COUNT(*) AS cnt,
                 A.taskid,
                 B.tp
             FROM baosong_task_alloc A
                      INNER JOIN baosong_task B ON A.taskid = B.id
             GROUP BY A.taskid) AA
                INNER JOIN
            (SELECT
                 SUM(child_num) AS tt,
                 tp
             FROM baosong_tpl
             GROUP BY tp) BB
            ON AA.tp = BB.tp
    </select>

    <select id="selectTaskFillSta" resultType="java.util.Map">
        SELECT
            COUNT(*) AS cnt,
            taskid
        FROM baosong_task_alloc
        WHERE fill_sta = TRUE
        GROUP BY taskid
    </select>

    <select id="getFillUsers" resultType="java.util.Map">
        SELECT
        GROUP_CONCAT(DISTINCT B.realname SEPARATOR ',') AS usernames
        FROM baosong_task_alloc A
        INNER JOIN sys_user B ON A.fill_user = B.id
        <where>
            <if test="query.taskid != null">
                AND A.taskid = #{query.taskid}
            </if>
            <if test="query.tplid != null">
                AND A.tplid = #{query.tplid}
            </if>
        </where>
        GROUP BY A.tplid, A.taskid, B.realname
    </select>

</mapper>