<?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.audit.mapper.AuditActRelOthersMapper">

    <!-- 优化说明：
         1. 保留原有字段命名风格(混合驼峰和下划线)
         2. 移除了所有索引提示(USE INDEX)
         3. 优化了JOIN条件和WHERE子句
         4. 保留分页支持(当前注释状态)
         5. 简化了不必要的条件(如1=1)
    -->

    <select id="queryActAndComplianeItems" resultType="java.util.Map">
        SELECT
        A.id, A.ind, A.item, A.content, A.file_id AS "fileId", A.cgrp, A.cgrp_path AS "cgrpPath",
        B.file_tp AS "fileTp", B.file_path AS "filePath", B.file_name AS "fileName",
        B.issued, B.issued_date AS "issuedDate", B.issued_sta AS "issuedSta",
        B.doc_no AS "docNo", B.apply_range_codes AS "applyRangeCodes"
        FROM audit_compliane_item A
        LEFT JOIN doc_files B ON A.file_id = B.id
        <where>
            <if test="idList != null and idList.size() > 0">
                A.id IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY A.ind, A.id
    </select>

    <select id="queryActAndInnerStrategyItems" resultType="java.util.Map">
        SELECT
        A.id, A.name, A.create_time AS "createTime", A.create_user AS "createUser"
        FROM audit_inner_ctrl A
        <where>
            <if test="idList != null and idList.size() > 0">
                A.id IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY A.id DESC
    </select>

    <select id="queryActAndInnerControlItems" resultType="java.util.Map">
        SELECT
        A.id, A.ind, A.item, A.content, A.file_id AS "fileId", A.cgrp, A.cgrp_path AS "cgrpPath",
        B.file_tp AS "fileTp", B.file_path AS "filePath", B.file_name AS "fileName",
        B.issued, B.issued_date AS "issuedDate", B.issued_sta AS "issuedSta",
        B.doc_no AS "docNo", B.apply_range_codes AS "applyRangeCodes"
        FROM audit_inner_order A
        LEFT JOIN doc_files B ON A.file_id = B.id
        <where>
            <if test="idList != null and idList.size() > 0">
                A.id IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY A.id DESC
    </select>

    <select id="queryActAndProblemItems" resultType="java.util.Map">
        SELECT
        A.id, A.pb_item AS "pbItem", A.pb_example AS "pbExample", A.pb_advise AS "pbAdvise",
        A.pb_analysis AS "pbAnalysis", A.pb_nature AS "pbNature", A.file_id AS "fileId",
        B.file_tp AS "fileTp", B.file_path AS "filePath", B.file_name AS "fileName",
        B.issued, B.issued_date AS "issuedDate", B.issued_sta AS "issuedSta",
        B.doc_no AS "docNo", B.apply_range_codes AS "applyRangeCodes"
        FROM audit_problem A
        LEFT JOIN doc_files B ON A.file_id = B.id
        <where>
            <if test="idList != null and idList.size() > 0">
                A.id IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY A.create_time DESC
    </select>

    <select id="queryActAndCheckItems" resultType="java.util.Map">
        SELECT
        A.id, A.content, A.des, D.item, D.id AS "citemid", D.content AS "itemContent",
        D.cgrp, D.cgrp_path AS "cgrpPath", D.file_id AS "fileId",
        E.file_tp AS "fileTp", E.file_path AS "filePath", E.file_name AS "fileName",
        E.issued, E.issued_date AS "issuedDate", E.issued_sta AS "issuedSta",
        E.doc_no AS "docNo", E.apply_range_codes AS "applyRangeCodes"
        FROM audit_check_method A
        LEFT JOIN audit_item_rel_check C ON A.id = C.checkid AND A.is_public = TRUE
        LEFT JOIN audit_compliane_item D ON C.citemid = D.id
        LEFT JOIN doc_files E ON D.file_id = E.id
        <where>
            <if test="idList != null and idList.size() > 0">
                A.id IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY A.create_time DESC
    </select>

    <select id="queryActAndRiskItems" resultType="java.util.Map">
        SELECT
        A.id, B.scenario_name AS "scenarioName", A.risk_event_name AS "riskEventName",
        A.risk_event_desc AS "riskEventDesc", A.risk_tp AS "riskTp", A.scid
        FROM audit_risk_event A
        LEFT JOIN audit_risk_scenario B ON A.scid = B.id
        <where>
            <if test="idList != null and idList.size() > 0">
                A.id IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY B.create_time DESC
    </select>

    <select id="queryActAndPracticeItems" resultType="java.util.Map">
        SELECT
        A.id, A.content, A.exe_rule AS "exeRule", A.des, A.file_id AS "fileId",
        B.file_tp AS "fileTp", B.file_path AS "filePath", B.file_name AS "fileName",
        B.issued, B.issued_date AS "issuedDate", B.issued_sta AS "issuedSta",
        B.doc_no AS "docNo", B.apply_range_codes AS "applyRangeCodes"
        FROM audit_best_practice A
        LEFT JOIN doc_files B ON A.file_id = B.id
        <where>
            <if test="idList != null and idList.size() > 0">
                A.id IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY A.create_time DESC
    </select>

    <select id="queryActAndRiskScenarioItems" resultType="java.util.Map">
        SELECT
        A.id, A.scenario_name AS "scenarioName", A.scenario_desc AS "scenarioDesc", A.pid
        FROM audit_risk_scenario A
        <where>
            <if test="idList != null and idList.size() > 0">
                A.id IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY A.create_time DESC
    </select>

    <select id="queryAllAct" resultType="java.util.Map">
        SELECT
            id, ca_name AS "caName", domain_code AS "domainCode", ca_desc AS "caDesc",
            create_user AS "createUser", create_time AS "createTime"
        FROM audit_act
        ORDER BY create_time DESC
    </select>

    <select id="queryAllLabels" resultType="java.util.Map">
        SELECT
            A.id, A.lable_code AS "lableCode", A.lable_name AS "lableName",
            A.lable_desc AS "lableDesc", A.sclassify_code AS "sclassifyCode",
            A.pclassify_code AS "pclassifyCode", B.classify_name AS "level2",
            C.classify_name AS "level1"
        FROM st_lable A
                 LEFT JOIN st_classify B ON A.sclassify_code = B.classify_code AND B.level = 2
                 LEFT JOIN st_classify C ON A.pclassify_code = C.classify_code AND C.level = 1
        ORDER BY A.pclassify_code, A.sclassify_code
    </select>

    <select id="queryAllActByTids" resultType="java.util.Map">
        SELECT
        A.ca_name AS "caName", A.id AS "actId", B.tid
        FROM audit_act A
        INNER JOIN audit_act_rel_others B ON A.id = B.act_id
        <where>
            <if test="tp != null">
                B.tp = #{tp}
            </if>
            <if test="idList != null and idList.size() > 0">
                AND B.tid IN
                <foreach item="id" collection="idList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ORDER BY A.create_time DESC
    </select>

</mapper>