<?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.problem.mapper.StProblemMapper">
    <select id="queryStProblems" resultType="org.jeecg.modules.stm.problem.vo.StProblemVo">
        SELECT DISTINCT A.id,A.ca_no AS caNo,A.compliane_no AS complianeNo,A.problem_item AS problemItem,
        A.problem_example AS problemExample,A.approve_flag AS approveFlag,A.valid,A.created_user AS createdUser,
        A.created_time AS createdTime,A.updated_user AS updatedUser,A.updated_time AS updatedTime,
        A.problem_level AS problemLevel,A.problem_advise AS problemAdvise,A.domain,A.problem_nature AS problemNature,
        C.ca_name AS caName,D.domain_name as domainName,A.risk_event_tp as riskEventTp
        FROM st_problem A
        LEFT JOIN st_problem_ca B ON A.id=B.problemid
        LEFT JOIN st_ca_main C ON C.ca_no=B.ca_no
        LEFT JOIN st_domain D ON C.domain_code=D.domain_code
        LEFT JOIN st_ca_main_lable E ON A.ca_no=E.ca_no
        LEFT JOIN st_lable F ON E.lable_code=F.lable_code
        <where>
            <if test="query.caNo!=null and query.caNo!=''">
                A.ca_no = #{query.caNo}
            </if>
            <if test="query.id!=null">
                and  A.id = #{query.id}
            </if>
            <if test="query.problemItem!=null and query.problemItem!=''">
                and  A.problem_item like concat('%',#{query.problemItem},'%')
            </if>
            <if test="query.problemExample!=null and query.problemExample!='' ">
                and   A.problem_example like concat('%',#{query.problemExample},'%')
            </if>
            <if test="query.caName!=null and query.caName!=''">
                and  C.ca_name like concat('%',#{query.caName},'%')
            </if>
            <if test="query.codes!=null">
                and  E.lable_code in
                <foreach item="code" collection="query.codes" open="(" separator="," close=")">
                    #{code,jdbcType=VARCHAR}
                </foreach>
            </if>
            <if test="query.domainCodeMore!=null and query.domainCodeMore!=''">
                and  D.domain_code like concat(#{query.domainCodeMore},'%')
            </if>
            <if test="query.checkClassFiy!=null">
                and  F.SCLASSIFY_CODE in
                <foreach item="item" collection="query.checkClassFiy" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="query.riskEventTp!=null">
                and A.risk_event_tp = #{query.riskEventTp}
            </if>
        </where>
    </select>
    <insert id="insertProblemCa"  parameterType="org.jeecg.modules.stm.problem.entity.StProblemCa">
        insert into st_problem_ca(ca_no,problemid) value(#{caNo},#{problemid})
    </insert>
    <delete id="deleteProblemCa"  parameterType="org.jeecg.modules.stm.problem.entity.StProblemCa">
        delete from st_problem_ca where problemid = #{problemid};
    </delete>
</mapper>