<?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.hssx.pcbms.mapper.IdeaMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.hssx.pcbms.entity.Idea">
        <id column="id" property="id" />
        <result column="uid" property="uid" />
        <result column="indate" property="indate" />
        <result column="content" property="content" />
        <result column="score" property="score" />
        <result column="is_draft" property="isDraft" />
        <result column="is_evaluated" property="isEvaluated" />
        <result column="score_id" property="scoreId" />
    </resultMap>
    <resultMap id="BaseResultIdeaCommentMap" type="com.hssx.pcbms.entity.IdeaComment">
        <id column="cid" property="id" />
        <result column="idea_id" property="ideaId" />
        <result column="content" property="content" />
        <result column="responder_id" property="responderId" />
        <result column="responder" property="responder" />
        <result column="indate" property="indate" />
    </resultMap>
    <resultMap id="BaseResultMapVO" type="com.hssx.pcbms.entity.vo.IdeaVO">
        <id column="id" property="id" />
        <result column="uid" property="uid" />
        <result column="idea_indate" property="indate" />
        <result column="idea_content" property="content" />
        <result column="score" property="score" />
        <result column="is_draft" property="isDraft" />
        <result column="is_evaluated" property="isEvaluated" />
        <result column="score_id" property="scoreId" />
        <association property="ideaComment" resultMap="BaseResultIdeaCommentMap" >
        </association>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, uid, indate, content, score, is_draft, is_evaluated, score_id
    </sql>

    <select id="getIdeaListByUid" resultMap="BaseResultMapVO">
        select
        i.id, i.uid, i.indate idea_indate, i.content idea_content, i.score, i.is_draft, i.is_evaluated, i.score_id,
        ic.id cid,ic.idea_id, ic.content, ic.responder_id, ic.responder, ic.indate
        from
        idea i
        left join idea_comment ic
        on i.id = ic.idea_id
        <where>
            <if test="idea.uid != null">
                i.uid = #{idea.uid,jdbcType=INTEGER}
            </if>
            <if test="time != null">
                and date_format(i.indate,'%Y-%m') = #{time}
            </if>
            <if test="idea.scoreId != null">
                and score_id = #{idea.scoreId}
            </if>
        </where>
        order by i.is_evaluated asc,i.indate,i.id desc
    </select>
    <select id="getIdeaScoreLists" resultMap="BaseResultMap">
        select
        id, uid, indate, score, is_draft, is_evaluated, score_id
        from
        idea i
        <where>
            i.uid = #{ideaVO.uid,jdbcType=INTEGER}
            and is_evaluated = 1
            <if test="ideaVO.time != null">
                and date_format(i.indate,'%Y-%m') = #{time}
            </if>
            <if test="ideaVO.scoreId != null">
                and score_id = #{ideaVO.scoreId}
            </if>
        </where>
        order by i.indate desc
    </select>

    <update id="updateListByTime">
        update idea
            set score_id = #{score.scoreId}
        where
          date_format(indate,'%Y-%m') = #{score.scoringYearMonth,jdbcType=VARCHAR}
          and uid = #{score.uid,jdbcType=INTEGER}
    </update>

</mapper>
