<?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.AuditCheckMethodItemMapper">

    <select id="getAllCheckMethodByItem" resultType="java.util.Map">
        SELECT A.id,A.name,A.des,A.tp,B.content,C.checkid,C.citemid,D.item,D.file_id as fileId,
               E.file_tp AS fileTp,E.file_path AS filePath,E.file_name AS fileName,D.content as complineContent
        FROM  audit_check_method_item A
        INNER JOIN audit_check_method B ON A.checkid = B.id
        INNER JOIN audit_item_rel_check C ON B.id = C.checkid
        INNER JOIN audit_compliane_item D ON C.citemid = D.id
        INNER JOIN doc_files E ON D.file_id  = E.id
        <where>
            <if test="query.name!=null">
                A.name = #{query.name}
            </if>
            <if test="query.tp!=null">
                and A.tp = #{query.tp}
            </if>
            <if test="query.id!=null">
                and A.id = #{query.id}
            </if>
        </where>
         order by D.id,B.id,A.id
    </select>

    <select id="getAllCheckMethod" resultType="java.util.Map">
        SELECT distinct A.name,A.tp,A.des,A.id,
        CASE
        WHEN A.tp = 1 THEN '工具要求'
        WHEN A.tp = 2 THEN '记录要求'
        WHEN A.tp = 3 THEN '制度要求'
        WHEN A.tp = 4 THEN '查阅系统'
        WHEN A.tp = 5 THEN '访谈人员'
        WHEN A.tp = 6 THEN '流程要求'
        WHEN A.tp = 9 THEN '其他要求'
        ELSE A.tp
        END AS tpname
        FROM  audit_check_method_item A
        INNER JOIN audit_check_method B ON A.checkid = B.id
        INNER JOIN audit_item_rel_check C ON B.id = C.checkid
        INNER JOIN audit_compliane_item D ON C.citemid = D.id
        <where> (A.tp=1 or A.tp=2 or A.tp=3 or A.tp=6)
            <if test="query.name!=null">
                and A.name like CONCAT('%',#{query.name},'%')
            </if>
            <if test="query.fileId!=null">
                and D.file_id = #{query.fileId}
            </if>
        </where>
        order by D.id,B.id,A.id
    </select>
    <select id="getAllCheckMethodFiles" resultType="java.util.Map">
        SELECT distinct E.file_name as label,E.id as value
        FROM  audit_check_method_item A
        INNER JOIN audit_check_method B ON A.checkid = B.id
        INNER JOIN audit_item_rel_check C ON B.id = C.checkid
        INNER JOIN audit_compliane_item D ON C.citemid = D.id
        INNER JOIN doc_files E ON D.file_id  = E.id
        <where>
            <if test="query.name!=null">
                A.name like CONCAT('%',#{query.name},'%')
            </if>
            <if test="query.fileId!=null">
                and D.file_id = #{query.fileId}
            </if>
        </where>
        order by E.id
    </select>

    <select id="getAllCheckItemCount" resultType="java.util.Map">
        SELECT COUNT(*) AS cnt,checkid FROM audit_check_method_item GROUP BY checkid
    </select>
</mapper>