<?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.bonus.canteen.core.health.mapper.HealthPopularArticleMapper">
    <resultMap type="com.bonus.canteen.core.health.domain.HealthPopularArticle" id="HealthPopularArticleResult">
        <result property="articleId"    column="article_id"    />
        <result property="articleTitle"    column="article_title"    />
        <result property="coverPhoto"    column="cover_photo"    />
        <result property="articleType"    column="article_type"    />
        <result property="articleContent"    column="article_content"    />
        <result property="articleUrl"    column="article_url"    />
        <result property="ifVisible"    column="if_visible"    />
        <result property="hits"    column="hits"    />
        <result property="ifPushed"    column="if_pushed"    />
        <result property="pushTime"    column="push_time"    />
        <result property="summary"    column="summary"    />
        <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="selectHealthPopularArticleVo">
        select article_id, article_title, cover_photo, article_type, article_content, article_url, if_visible, hits, if_pushed, push_time, summary, create_by, create_time, update_by, update_time from health_popular_article
    </sql>

    <select id="selectHealthPopularArticleList" parameterType="com.bonus.canteen.core.health.domain.HealthPopularArticle" resultMap="HealthPopularArticleResult">
       select * from (
        select
        aa.article_id,
        aa.article_title,
        aa.cover_photo,
        aa.article_type,
        aa.article_content,
        aa.article_url,
        aa.if_visible,
        aa.hits,
        aa.if_pushed,
        aa.push_time,
        aa.summary,
        aa.create_by,
        aa.create_time,
        aa.update_by,
        aa.update_time ,
        GROUP_CONCAT(bb.chronic_name ORDER BY bb.chronic_id desc) AS chronic_names,
        GROUP_CONCAT(bb.chronic_id ORDER BY bb.chronic_id desc)   AS chronic_ids
        from health_popular_article aa
        left join health_popular_article_chronic bb on aa.article_id=bb.article_id
        <where>
            <if test="articleTitle != null  and articleTitle != ''"> and aa.article_title  like concat('%', #{articleTitle}, '%')</if>
            <if test="coverPhoto != null  and coverPhoto != ''"> and aa.cover_photo = #{coverPhoto}</if>
            <if test="articleType != null "> and aa.article_type = #{articleType}</if>
            <if test="articleContent != null  and articleContent != ''"> and aa.article_content = #{articleContent}</if>
            <if test="articleUrl != null  and articleUrl != ''"> and aa.article_url = #{articleUrl}</if>
            <if test="ifVisible != null "> and aa.if_visible = #{ifVisible}</if>
            <if test="hits != null "> and aa.hits = #{hits}</if>
            <if test="ifPushed != null "> and aa.if_pushed = #{ifPushed}</if>
            <if test="pushTime != null "> and aa.push_time = #{pushTime}</if>
            <if test="startDate != null "> and substring(aa.create_time,1,10) >= #{startDate} and #{endDate}>= substring(aa.create_time,1,10)</if>
            <if test="summary != null  and summary != ''"> and aa.summary = #{summary}</if>
        </where>
        group by aa.article_id,
        aa.article_title,
        aa.cover_photo,
        aa.article_type,
        aa.article_content,
        aa.article_url,
        aa.if_visible,
        aa.hits,
        aa.if_pushed,
        aa.push_time,
        aa.summary,
        aa.create_by,
        aa.create_time,
        aa.update_by,
        aa.update_time
        ) a
        <where>
            <if test="chronicIds != null  and chronicIds != ''"> and a.chronic_ids  like concat('%', #{chronicIds}, '%')</if>
            <if test="chronicId != null "> and a.chronic_ids like concat('%', #{chronicId}, '%')</if>
        </where>
        order by a.create_time desc
    </select>

    <select id="selectHealthPopularArticleByArticleId" parameterType="Long" resultMap="HealthPopularArticleResult">
        <include refid="selectHealthPopularArticleVo"/>
        where article_id = #{articleId}
    </select>

    <insert id="insertHealthPopularArticle" parameterType="com.bonus.canteen.core.health.domain.HealthPopularArticle" useGeneratedKeys="true" keyProperty="articleId">
        insert into health_popular_article
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="articleTitle != null and articleTitle != ''">article_title,</if>
            <if test="coverPhoto != null">cover_photo,</if>
            <if test="articleType != null">article_type,</if>
            <if test="articleContent != null">article_content,</if>
            <if test="articleUrl != null">article_url,</if>
            <if test="ifVisible != null">if_visible,</if>
            <if test="hits != null">hits,</if>
            <if test="ifPushed != null">if_pushed,</if>
            <if test="pushTime != null">push_time,</if>
            <if test="summary != null">summary,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="articleTitle != null and articleTitle != ''">#{articleTitle},</if>
            <if test="coverPhoto != null">#{coverPhoto},</if>
            <if test="articleType != null">#{articleType},</if>
            <if test="articleContent != null">#{articleContent},</if>
            <if test="articleUrl != null">#{articleUrl},</if>
            <if test="ifVisible != null">#{ifVisible},</if>
            <if test="hits != null">#{hits},</if>
            <if test="ifPushed != null">#{ifPushed},</if>
            <if test="pushTime != null">#{pushTime},</if>
            <if test="summary != null">#{summary},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateHealthPopularArticle" parameterType="com.bonus.canteen.core.health.domain.HealthPopularArticle">
        update health_popular_article
        <trim prefix="SET" suffixOverrides=",">
            <if test="articleTitle != null and articleTitle != ''">article_title = #{articleTitle},</if>
            <if test="coverPhoto != null">cover_photo = #{coverPhoto},</if>
            <if test="articleType != null">article_type = #{articleType},</if>
            <if test="articleContent != null">article_content = #{articleContent},</if>
            <if test="articleUrl != null">article_url = #{articleUrl},</if>
            <if test="ifVisible != null">if_visible = #{ifVisible},</if>
            <if test="hits != null">hits = #{hits},</if>
            <if test="ifPushed != null">if_pushed = #{ifPushed},</if>
            <if test="pushTime != null">push_time = #{pushTime},</if>
            <if test="summary != null">summary = #{summary},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where article_id = #{articleId}
    </update>

    <delete id="deleteHealthPopularArticleByArticleId" parameterType="Long">
        delete from health_popular_article where article_id = #{articleId}
    </delete>

    <delete id="deleteHealthPopularArticleByArticleIds" parameterType="String">
        delete from health_popular_article where article_id in
        <foreach item="articleId" collection="array" open="(" separator="," close=")">
            #{articleId}
        </foreach>
    </delete>
    <delete id="deleteHealthArticleChronic" parameterType="Long">
        delete from health_popular_article_chronic where article_id = #{articleId}
    </delete>
    <insert id="insertHealthArticleChronic" parameterType="com.bonus.canteen.core.health.domain.HealthPopularArticle">
        insert into health_popular_article_chronic(article_id, chronic_id, chronic_name)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.articleId}, #{entity.chronicId}, #{entity.chronicName})
        </foreach>
    </insert>

    <update id="updateHealthPopularArticleState" parameterType="com.bonus.canteen.core.health.domain.HealthPopularArticle">
        update health_popular_article
        <trim prefix="SET" suffixOverrides=",">
            <if test="ifPushed != null">if_pushed = #{ifPushed},</if>
            <if test="pushTime != null">push_time = #{pushTime},</if>
        </trim>
        where article_id = #{articleId}
    </update>
</mapper>
