<?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.testor.module.news.dao.SysNewsDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.news.model.domain.SysNews">
        <id column="id" property="id"/>
        <id column="release_unit" property="releaseUnit"/>
        <id column="title" property="title"/>
        <id column="content" property="content"/>
        <id column="is_read" property="isRead"/>
        <id column="process_status" property="processStatus"/>
        <id column="process_id" property="processId"/>
        <id column="parent_id" property="parentId"/>
        <id column="reject_opinion" property="rejectOpinion"/>
        <id column="tenant_id" property="tenantId"/>
    </resultMap>

    <resultMap id="news" type="com.testor.module.news.model.domain.NewStatice">
        <id column="areaCom" property="areaCom"/>
        <id column="newCount" property="newCount"/>
        <id column="newAvg" property="newAvg"/>
    </resultMap>

    <!--    区域公司新闻总数-->
    <select id="getAreaCount" resultMap="news" databaseId="mysql">
        select
            tso1.org_name areaCom ,
            count(tsn1.release_org_id) newCount,
            ifnull(ANY_VALUE(tso2.new_count), 0)/ifnull(ANY_VALUE(tso2.org_count), 0) newAvg
        from (SELECT * FROM t_sys_org WHERE tree_level = 2 and status='0') tso1
                 left join t_sys_news tsn1
                           on tsn1.release_org_id = tso1.org_id and tsn1.process_status = 5 and
                               YEAR(tsn1.release_time) = year(now())
            left join (select tso.parent_id, count(tsn2.release_org_id) new_count, count(distinct tso.org_id) org_count
        from (SELECT * FROM t_sys_org WHERE tree_level = 3) tso
            left join t_sys_news tsn2
        on tso.org_id = tsn2.release_org_id and tsn2.process_status = 5 and
            YEAR(tsn2.release_time) = year(now())
        group by tso.parent_id) tso2 on tso2.parent_id = tso1.org_id
        group by tso1.org_id;
    </select>

    <select id="getAreaCount" resultMap="news" databaseId="kingbase">
        SELECT
            tso1.org_name AS areaCom,
            COUNT(tsn1.release_org_id) AS newCount,
            COALESCE(SUM(tso2.new_count), 0) / NULLIF(SUM(tso2.org_count), 0) AS newAvg
        FROM
                (SELECT * FROM t_sys_org WHERE tree_level = 2 AND status = '0') tso1
                    LEFT JOIN
            t_sys_news tsn1
            ON
                        tsn1.release_org_id = tso1.org_id AND tsn1.process_status = 5 AND
                        EXTRACT(YEAR FROM tsn1.release_time) = EXTRACT(YEAR FROM CURRENT_DATE)
                    LEFT JOIN
            (
                SELECT
                    tso.parent_id,
                    COUNT(tsn2.release_org_id) AS new_count,
                    COUNT(DISTINCT tso.org_id) AS org_count
                FROM
                        (SELECT * FROM t_sys_org WHERE tree_level = 3) tso
                            LEFT JOIN
                    t_sys_news tsn2
                    ON
                                tso.org_id = tsn2.release_org_id AND tsn2.process_status = 5 AND
                                EXTRACT(YEAR FROM tsn2.release_time) = EXTRACT(YEAR FROM CURRENT_DATE)
                GROUP BY
                    tso.parent_id
            ) tso2
            ON
                tso2.parent_id = tso1.org_id
        GROUP BY
            tso1.org_id, tso1.org_name;
    </select>



    <!--    基层粮库新闻总数-->
    <select id="getBasicGrainNews" resultType="Integer">
        select release_unit, count(release_unit) 文章数量
        from t_sys_news
        where status = '0'
          and process_status = '5'
          and release_unit in (select org_name from t_sys_org where tree_level = '3')
        group by release_unit;
    </select>
    <!--    基层粮库总数-->
    <select id="getBasicGrain" resultType="Integer">
        select count(org_name)
        from t_sys_org
        where tree_level = '3';
    </select>
    <!--    获取已读数据-->
    <select id="getRead" parameterType="com.testor.module.news.model.dto.SysNewsParam"
            resultType="com.testor.module.news.model.domain.SysNews">
        select ne.*
        from t_sys_news ne
        join t_sys_is_read r
        on ne.id = r.article_id
        where ne.status != 1
        and ne.process_status = 5
        and ne.process_status != 6
        and r.user_id = #{loginUserId}
        <if test="param.title!=null and param.title!=''">
            AND ne.title LIKE CONCAT('%',#{param.title},'%')
        </if>
        <if test="param.isRead!=null and param.isRead!=''">
            AND r.is_read = #{param.isRead}
        </if>
        <if test="startTime!=null and startTime!='' or endTime!=null and endTime !=''">
            AND ne.release_time between #{startTime} and #{endTime}
        </if>
        <if test="list!=null and list!=''">
            AND ne.level in
            <foreach open="(" close=")" separator="," collection="list" item="item" index="index">
                #{item}
            </foreach>
        </if>
        order by release_time
    </select>

    <!--    获取未读数据-->
    <select id="getUnRead" parameterType="com.testor.module.news.model.dto.SysNewsParam"
            resultType="com.testor.module.news.model.domain.SysNews">
        select *
        from t_sys_news
        where id not in (select r.article_id
        from t_sys_news ne
        join t_sys_is_read r
        on ne.id = r.article_id
        where r.is_read = '1'
        and r.user_id = #{loginUserId})
        and status != 1
        and process_status = 5
        and process_status != 6
        <if test="param.title!=null and param.title!=''">
            AND title LIKE CONCAT('%',#{param.title},'%')
        </if>
        <if test="startTime!=null and startTime!='' or endTime!=null and endTime !=''">
            AND release_time between #{startTime} and #{endTime}
        </if>
        <if test="list!=null and list!=''">
            AND level in
            <foreach open="(" close=")" separator="," collection="list" item="item" index="index">
                #{item}
            </foreach>
        </if>
        order by release_time
    </select>
</mapper>