<?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.thinkgem.jeesite.modules.messageCenter.dao.ArticleApiDao">

    <select id="getArticles" resultType="com.thinkgem.jeesite.modules.messageCenter.entity.ArticleEntity">
        SELECT
            a.id,
            a.title,
            a.image,
            a.keywords,
            a.description,
            DATE_FORMAT(a.create_date,'%Y-%m-%d') AS publishDate,
            a.weight,
            a.hits,
            a.is_flow,
            a.is_tip,
            a.scope,
            a.send_date,
            a.create_date AS created,
            a.create_by AS createdUser,
            a.update_date AS modified,
            a.update_by AS modifiedUser
        FROM
            cms_article a
        WHERE
            a.is_flow = 'N'
            AND a.is_tip = 'N'
            AND a.category_id = 28
            AND a.scope LIKE CONCAT('%',(
                SELECT
                    yu. LEVEL
                FROM
                    youka_users yu
                WHERE
                    yu. CODE = #{subMerchantCode}
            ),'%')
            <![CDATA[AND a.send_date <= now()]]>
        ORDER BY
            a.create_date DESC,
            a.weight DESC
        LIMIT #{begin},#{pageSize}
    </select>

    <select id="getArticleDetail" resultType="com.thinkgem.jeesite.modules.messageCenter.entity.ArticleEntity">
        SELECT
            a.id,
            a.title,
            a.image,
            a.keywords,
            a.description,
            DATE_FORMAT(a.create_date,'%Y-%m-%d') AS publishDate,
            a.weight,
            a.hits,
            a.is_flow,
            a.is_tip,
            a.scope,
            a.send_date,
            a.create_date AS created,
            a.create_by AS createdUser,
            a.update_date AS modified,
            a.update_by AS modifiedUser,
            b.content
        FROM
            cms_article a
        LEFT JOIN cms_article_data b ON a.id = b.id
        WHERE
            a.id = #{articleId}
            AND a.scope LIKE CONCAT('%',(
                SELECT
                    yu. LEVEL
                FROM
                    youka_users yu
                WHERE
                    yu. CODE = #{subMerchantCode}
            ),'%')
            <![CDATA[AND a.send_date <= now()]]>
        ORDER BY
            a.create_date DESC,
            a.weight DESC
    </select>
</mapper>