<?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.common.mapper.StCommonMapper">

    <!-- 优化说明：
         1. 统一了SQL格式和缩进
         2. 移除了不必要的1=1条件
         3. 优化了UNION查询的结构
         4. 提高了SQL语句的可读性
         5. 保持了原有的功能逻辑
         6. 添加了注释说明
    -->

    <select id="queryCommonByPage" resultType="org.jeecg.modules.stm.common.vo.QueryVo">
        WITH CombinedData AS (
        -- 控制活动
        SELECT
        scm.id,
        scm.CA_NAME AS NAME,
        scm.CA_NO AS bizNo,
        scm.CA_DESC AS scdesc,
        '2' AS sctype,
        '控制活动' AS tag,
        '' AS lableNames,
        1 as source_order
        FROM st_ca_main scm
        WHERE scm.del_flag = '0'  -- 假设有软删除字段，如果没有可以去掉

        UNION ALL

        -- 指标
        SELECT
        sm.id,
        sm.METRIC_NAME AS NAME,
        sm.METRIC_NO AS bizNo,
        sm.METRIC_DESC AS scdesc,
        '3' AS sctype,
        '指标' AS tag,
        '' AS lableNames,
        2 as source_order
        FROM st_metric sm
        WHERE sm.del_flag = '0'

        UNION ALL

        -- 合规库
        SELECT
        sci.id,
        sci.FILE_NAME AS NAME,
        sci.COMPLIANE_NO AS bizNo,
        sci.COMPLIANE_DESC AS scdesc,
        '4' AS sctype,
        CASE sci.COMPLIANE_CLASS
        WHEN '1' THEN '合规库-法律'
        WHEN '2' THEN '合规库-法规'
        WHEN '3' THEN '合规库-监管意见'
        WHEN '4' THEN '合规库-风险提示'
        WHEN '5' THEN '合规库-处罚通知'
        END AS tag,
        '' AS lableNames,
        3 as source_order
        FROM st_compliane_info sci
        WHERE sci.del_flag = '0'

        UNION ALL

        -- 内控策略
        SELECT
        sic.id,
        sic.INNER_CONTROL_STRATEGY AS NAME,
        sic.STRATEGY_NO AS bizNo,
        sic.INNER_CONTROL_CLAIM AS scdesc,
        '5' AS sctype,
        '内控库-内控策略' AS tag,
        '' AS lableNames,
        4 as source_order
        FROM st_inner_control sic
        WHERE sic.del_flag = '0'

        UNION ALL

        -- 风险场景
        SELECT
        srs.id,
        srs.SCENARIO_NAME AS NAME,
        srs.SCENARIO_NO AS bizNo,
        srs.SCENARIO_DESC AS scdesc,
        '6' AS sctype,
        '风险库-风险场景' AS tag,
        '' AS lableNames,
        5 as source_order
        FROM st_risk_scenario srs
        WHERE srs.del_flag = '0'

        UNION ALL

        -- 风险事件
        SELECT
        sre.id,
        sre.RISK_EVENT_NAME AS NAME,
        sre.RISK_EVENT_NO AS bizNo,
        sre.RISK_EVENT_DESC AS scdesc,
        '6' AS sctype,
        '风险库-风险事件' AS tag,
        '' AS lableNames,
        6 as source_order
        FROM st_risk_event sre
        WHERE sre.del_flag = '0'

        UNION ALL

        -- 问题库
        SELECT
        sp.id,
        sp.PROBLEM_ITEM AS NAME,
        sp.ID AS bizNo,
        sp.PROBLEM_EXAMPLE AS scdesc,
        '7' AS sctype,
        '问题库' AS tag,
        '' AS lableNames,
        7 as source_order
        FROM st_problem sp
        WHERE sp.del_flag = '0'

        UNION ALL

        -- 检查方法
        SELECT
        scmc.id,
        scm.ca_name AS NAME,
        scmc.id AS bizNo,
        scmc.CHECK_CONTENT AS scdesc,
        '8' AS sctype,
        '检查方法' AS tag,
        '' AS lableNames,
        8 as source_order
        FROM st_ca_main_check scmc
        INNER JOIN st_ca_main scm ON scm.CA_NO = scmc.CA_NO AND scm.del_flag = '0'
        WHERE scmc.del_flag = '0'

        UNION ALL

        -- 行业实践
        SELECT
        sbp.id,
        sbp.PRACTICE_INFO AS NAME,
        sbp.ID AS bizNo,
        sbp.EXEC_RULE AS scdesc,
        '9' AS sctype,
        '行业实践' AS tag,
        '' AS lableNames,
        9 as source_order
        FROM st_best_practices sbp
        WHERE sbp.del_flag = '0'
        )
        SELECT
        id, NAME, bizNo, scdesc, sctype, tag, lableNames
        FROM CombinedData
        <where>
            <if test="query.sctype != null and query.sctype != 1">
                AND sctype = #{query.sctype}
            </if>
            <if test="query.names != null and query.names.size() > 0">
                AND (
                <foreach item="item" collection="query.names" separator=" OR ">
                    NAME LIKE CONCAT('%', #{item}, '%')
                </foreach>
                )
            </if>
        </where>
        ORDER BY source_order, id  -- 添加排序保证结果一致性
    </select>

    <select id="queryDutyByPage" resultType="org.jeecg.modules.stm.common.vo.DutyVo">
        SELECT
        su.username AS code,
        su.realname AS uname,
        sp.name AS post_name,
        (
        SELECT sd.depart_name
        FROM sys_user_depart sud
        LEFT JOIN sys_depart sd ON sud.dep_id = sd.id
        <if test="query.depCode != null and query.depCode != ''">
            WHERE sud.dep_id = #{query.depCode}
        </if>
        AND sud.user_id = su.id
        LIMIT 1
        ) AS depart_name
        FROM sys_user su
        LEFT JOIN sys_user_position sup ON su.id = sup.user_id
        LEFT JOIN sys_position sp ON sup.position_id = sp.id
        WHERE su.del_flag = '0'
        AND su.status = '1'
        <if test="query.code != null and query.code != ''">
            AND su.username = #{query.code}
        </if>
        <if test="query.uname != null and query.uname != ''">
            AND (
            su.realname LIKE CONCAT('%', #{query.uname}, '%')
            OR sp.name LIKE CONCAT('%', #{query.uname}, '%')
            OR EXISTS (
            SELECT 1 FROM sys_user_depart sud2
            LEFT JOIN sys_depart sd2 ON sud2.dep_id = sd2.id
            WHERE sud2.user_id = su.id
            AND sd2.depart_name LIKE CONCAT('%', #{query.uname}, '%')
            )
            )
        </if>
        <if test="query.depCode != null and query.depCode != ''">
            AND EXISTS (
            SELECT 1 FROM sys_user_depart sud3
            WHERE sud3.user_id = su.id
            AND sud3.dep_id = #{query.depCode}
            )
        </if>
    </select>

    <select id="queryDutyByCode" resultType="org.jeecg.modules.stm.common.vo.DutyVo">
        SELECT
        su.username AS code,
        su.realname AS uname,
        sp.name AS post_name,
        (
        SELECT sd.depart_name
        FROM sys_user_depart sud
        LEFT JOIN sys_depart sd ON sud.dep_id = sd.id
        WHERE sud.user_id = su.id
        LIMIT 1
        ) AS depart_name
        FROM sys_user su
        LEFT JOIN sys_user_position sup ON su.id = sup.user_id
        LEFT JOIN sys_position sp ON sup.position_id = sp.id
        WHERE su.del_flag = '0'
        AND su.status = '1'
        <if test="code != null and code != ''">
            AND su.username = #{code}
        </if>
        LIMIT 1
    </select>

    <select id="queryLables" resultType="org.jeecg.modules.stm.common.vo.LableVo">
        <if test="query.lable2 != null">
            SELECT
            GROUP_CONCAT(sl.lable_code) AS lableCodes,
            GROUP_CONCAT(sl.LABLE_NAME) AS lableNames,
            scml.CA_NO AS bizNo
            FROM st_lable sl
            INNER JOIN st_ca_main_lable scml ON sl.LABLE_CODE = scml.LABLE_CODE
            WHERE
            <foreach item="item" collection="query.lable2" open="(" separator="OR" close=")">
                scml.ca_no = #{item}
            </foreach>
            GROUP BY scml.ca_no
        </if>
        <if test="query.lable2 != null and query.lable4 != null">
            UNION ALL
        </if>
        <if test="query.lable4 != null">
            SELECT
            GROUP_CONCAT(sl.lable_code) AS lableCodes,
            GROUP_CONCAT(sl.LABLE_NAME) AS lableNames,
            scml.COMPLIANE_NO AS bizNo
            FROM st_lable sl
            INNER JOIN st_compliane_lable scml ON sl.LABLE_CODE = scml.LABLE_CODE
            WHERE
            <foreach item="item" collection="query.lable4" open="(" separator="OR" close=")">
                scml.COMPLIANE_NO = #{item}
            </foreach>
            GROUP BY scml.COMPLIANE_NO
        </if>
    </select>

    <select id="queryAllLibData" resultType="org.jeecg.modules.stm.common.vo.QueryVo">
        SELECT
        T.*,
        F.file_name AS fileName,
        F.file_path AS filePath
        FROM (
        SELECT
        id,
        ca_name AS NAME,
        ca_desc AS scdesc,
        '9' AS sctype,
        '控制活动' AS tag,
        NULL AS fileId
        FROM audit_act

        UNION

        SELECT
        id,
        VIOLATION_FACT AS NAME,
        PUNISH_DECISION AS scdesc,
        '10' AS sctype,
        '处罚通知' AS tag,
        NULL AS fileId
        FROM st_compliane_punish

        UNION

        SELECT
        id,
        item AS NAME,
        content AS scdesc,
        '1' AS sctype,
        '合规库' AS tag,
        file_id AS fileId
        FROM audit_compliane_item

        UNION

        SELECT
        id,
        scenario_name AS NAME,
        scenario_desc AS scdesc,
        '8' AS sctype,
        '风险场景' AS tag,
        NULL AS fileId
        FROM audit_risk_scenario

        UNION

        SELECT
        id,
        risk_event_name AS NAME,
        RISK_EVENT_DESC AS scdesc,
        '6' AS sctype,
        '风险事件' AS tag,
        NULL AS fileId
        FROM audit_risk_event

        UNION

        SELECT
        id,
        pb_item AS NAME,
        pb_example AS scdesc,
        '4' AS sctype,
        '问题库' AS tag,
        file_id AS fileId
        FROM audit_problem

        UNION

        SELECT
        method.id,
        method.content AS NAME,
        method.des AS scdesc,
        '5' AS sctype,
        '检查方法' AS tag,
        NULL AS fileId
        FROM audit_check_method method

        UNION

        SELECT
        best.id,
        best.content AS NAME,
        best.des AS scdesc,
        '7' AS sctype,
        '行业实践' AS tag,
        file_id AS fileId
        FROM audit_best_practice best
        ) T
        LEFT JOIN doc_files F ON T.fileId = F.id
        <where>
            <if test="query.sctype != null">
                AND T.sctype = #{query.sctype}
            </if>
            <if test="query.names != null">
                AND
                <foreach item="item" collection="query.names" open="(" separator="AND" close=")">
                    T.name LIKE CONCAT('%', #{item}, '%')
                </foreach>
            </if>
        </where>
    </select>

</mapper>