<?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.diagbot.mapper.RetrievalMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.diagbot.entity.Retrieval">
        <id column="id" property="id" />
        <result column="is_deleted" property="isDeleted" />
        <result column="gmt_create" property="gmtCreate" />
        <result column="gmt_modified" property="gmtModified" />
        <result column="creator" property="creator" />
        <result column="modifier" property="modifier" />
        <result column="name" property="name" />
        <result column="spell" property="spell" />
        <result column="remark" property="remark" />
    </resultMap>

	<select id="getRetrievalsByTag" parameterType="com.diagbot.vo.GetRetrievalsByTagVO"
		resultType="com.diagbot.dto.GetRetrievalsByTagDTO">
		SELECT
			c.id as retrievalId,
			b.id as retrievalMappingId,
			c.`name` as retrievalName,
			c.spell as retrievalSpell,
			b.show_type as retrievalType
		FROM icss_question_info a 
		JOIN icss_retrieval_mapping b
		ON a.id=b.question_id
		JOIN icss_retrieval c 
		ON b.retrieval_id=c.id
		WHERE a.is_deleted='N' AND b.is_deleted='N' AND c.is_deleted='N'
		<if test="questionId!=null">
			AND a.id=#{questionId}
		</if>
		<if test="questionName!=null">
			AND a.`name`=#{questionName}
		</if>
	</select>
	
	<select id="getRetrievalList" parameterType="com.diagbot.vo.RetrievalListVO" 
		resultType="com.diagbot.dto.RetrievalListDTO">
		SELECT
		a.id as questionId,
		a.tag_name as questionName,
		MAX(b.gmt_modified) as gmtOperate,
		sum(case when b.is_deleted='N' then 1 else 0 end) as delSum
		FROM icss_question_info a JOIN icss_retrieval_mapping b ON a.id=b.question_id
		JOIN icss_retrieval c ON b.retrieval_id=c.id
		WHERE a.is_deleted='N' AND c.is_deleted='N'
		AND a.tag_name LIKE CONCAT('%',#{questionName},'%')
		GROUP BY a.id
		HAVING delSum>0
		ORDER BY gmtOperate DESC
	</select>

</mapper>
