<?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.AuditInnerCheckmethodItemMapper">
    <select id="getAllInnerOrderCheckMethodByItem" 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_inner_order_checkmethod_item` A
        INNER JOIN `audit_inner_order_checkmethod` B ON A.checkid = B.id
        INNER JOIN `audit_inner_order_rel_check` C ON B.id = C.checkid
        INNER JOIN `audit_inner_order` D ON C.citemid = D.id
        LEFT 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="getAllInnerOrderCheckMethodItem" 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_inner_order_checkmethod_item A
        INNER JOIN audit_inner_order_checkmethod B ON A.checkid = B.id
        INNER JOIN audit_inner_order_rel_check C ON B.id = C.checkid
        INNER JOIN audit_inner_order 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>
</mapper>