<?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="com.yeejoin.amos.supervision.business.dao.mapper.RiskFactorMapper">

    <select id="getById" resultType="com.yeejoin.amos.supervision.business.entity.mybatis.RiskFactorBo">
        select
            a.*,
            b.name as identificationMethodName,
            c.region_id as regionId,c.department_id as equipmentDepartmentId,
            c.workshop_section as workshopSection,c.name as equipmentName,
            c.code as equipmentCode,c.org_code as equipmentCode,
            d.name as riskResourceName
        from
            spc_risk_factors as a
        left join
            spc_identification_method as b on a.identification_method_id = b.id
        left join
            spc_equipment as c on a.control_object_id = c.id
        left join
            spc_risk_source as d on a.risk_source_id = d.id
        where
            a.id = #{id}
        limit 1
    </select>

    <select id="getByEvaluateId" resultType="com.yeejoin.amos.supervision.business.entity.mybatis.RiskFactorBo">
        select
            a.*,
            c.region_id as regionId,c.department_id as equipmentDepartmentId,
            c.workshop_section as workshopSection,c.name as equipmentName,
            c.code as equipmentCode,c.org_code as equipmentCode,
            d.name as riskResourceName
        from
            spc_risk_factors as a
        left join
            spc_equipment as c on a.control_object_id = c.id
        left join
            spc_risk_source as d on a.risk_source_id = d.id
        where
            a.evaluate_id = #{id}
        limit 1
    </select>

    <select id="listEquipmentByRiskSourceId" resultType="com.yeejoin.amos.supervision.business.entity.mybatis.RiskFactorBo">
        select
            b.id,b.name,b.level_id,b.control_object_id,c.level as rfLevel
        from
            p_point_classify as a
        left join
            spc_risk_factors as b on a.original_id = b.id
        left join
            spc_risk_level as c on b.level_id = c.id
        where
            a.point_id = #{riskSourceId}
        and
            b.type = "1";
    </select>

    <select id="listEquipmentByEquipmentId" resultType="com.yeejoin.amos.supervision.business.entity.mybatis.RiskFactorBo">
        select
            a.id,a.name,a.level_id,a.control_object_id,b.level as rfLevel
        from
            spc_risk_factors as a
        left join
            spc_risk_level as b on a.level_id = b.id
        where
            a.control_object_id = #{equipmentId}
        and
            a.type = "1"
        and
            a.status = 3
    </select>

    <select id="listTaskworkByTaskworkContentId" resultType="com.yeejoin.amos.supervision.business.entity.mybatis.RiskFactorBo">
        select
            a.id,a.name,a.level_id,b.level as rfLevel,a.evaluate_id
        from
            spc_risk_factors as a
        left join
            spc_risk_level as b on a.level_id = b.id
        where
            a.risk_source_id = #{taskworkContentId}
        and
            a.type = "2"
        and
            a.status = 3
    </select>

    <select id="listTaskworkByTaskworkContentIds" resultType="com.yeejoin.amos.supervision.business.entity.mybatis.RiskFactorBo">
        select
        a.id,a.name,a.level_id,b.level as rfLevel,a.evaluate_id
        from
        spc_risk_factors as a
        left join
        spc_risk_level as b on a.level_id = b.id
        where
        a.risk_source_id in
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
        and
        a.type = "2"
        and
        a.status = 3
    </select>

    <update id="updateControlStatus">
        update
            spc_risk_factors
        set
            control_status = #{controlStatus}
        where
            id = #{id}
    </update>

    <select id="countByLevelCodeAndDeptId" resultType="java.lang.Long">
        select
        count(1)
        from
        spc_risk_factors as a
        left join
        spc_risk_level as b on a.level_id = b.id
        left join
        spc_risk_source as c on a.risk_source_id = c.id
        where
        a.level_id is not null
        <if test="departmentId != null and departmentId != -1">
            and c.belong_department_id = #{departmentId}
        </if>
        and
        b.code = #{code}
    </select>

    <select id="getIdsByLevelCodeAndDeptId" resultType="java.lang.String">
        SELECT
        GROUP_CONCAT( a.id ) extraIds
        FROM
        spc_risk_factors AS a
        LEFT JOIN spc_risk_level AS b ON a.level_id = b.id
        LEFT JOIN spc_risk_source AS c ON a.risk_source_id = c.id
        WHERE
        a.level_id IS NOT NULL
        <if test="departmentId != null and departmentId != -1">
            AND c.belong_department_id = #{departmentId}
        </if>
        AND b.CODE = #{code}
    </select>
</mapper>