<?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="cn.cecep.talroad.mapper.analysis.PcRaProblemTypeMapper">

    <resultMap type="cn.cecep.talroad.domain.analysis.PcRaProblemType" id="PcRaProblemTypeResult">
        <result property="id"    column="id"    />
        <result property="name"    column="name"    />
        <result property="parentId"    column="parent_id"    />
        <result property="sort"    column="sort"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>


    <sql id="selectPcRaProblemTypeVo">
        select id, name, parent_id, sort, create_by, create_time, update_by, update_time from pc_ra_problem_type
    </sql>

    <select id="selectPcRaProblemTypeList" parameterType="cn.cecep.talroad.domain.analysis.PcRaProblemType" resultMap="PcRaProblemTypeResult">
        <include refid="selectPcRaProblemTypeVo"/>
        <where>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="parentId != null  and parentId != ''"> and parent_id = #{parentId}</if>
            <if test="sort != null "> and sort = #{sort}</if>
        </where>
    </select>

    <select id="getProblemList" resultMap="PcRaProblemTypeResult">
        SELECT * FROM pc_ra_problem_type WHERE type=#{problemType,jdbcType=VARCHAR}

    </select>

    <select id="getAIProblemList" resultType="cn.cecep.talroad.vo.effective.ForecastAccuracyInfo">
        SELECT
            problemName ,
            total,
            trueSum,
            ROUND(CAST(trueSum AS numeric) / NULLIF(CAST(total AS NUMERIC), 0), 4)  AS result
        FROM
            (
                SELECT
                    ppt.NAME AS problemName,
                    COUNT ( mpf.ID ) total,
                    SUM ( CASE WHEN mpf.model_valid_db = '1' THEN 1 ELSE 0 END ) AS trueSum
                FROM
                    "pc_ra_problem_type" ppt
                        LEFT JOIN m_am_problem_fi mpf ON ppt.ID = mpf.problem_type
                WHERE
                    ppt.TYPE = 'A'
                    AND mpf.model_valid_db IN ('0','1')
                <if test="startTime != null">
                    AND mpf.analyse_time &gt;= #{startTime,jdbcType=TIMESTAMP}
                </if>
                <if test="endTime != null">
                    AND mpf.analyse_time &lt;= #{endTime,jdbcType=TIMESTAMP}
                </if>
                <if test="problemType != null and problemType.trim() != ''">
                    AND ppt.id=#{problemType,jdbcType=VARCHAR}
                </if>
        GROUP BY
        ppt.NAME
        ) A
    </select>
</mapper>
