<?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.hzs.system.base.mapper.BdNoticeMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.hzs.common.domain.system.base.BdNotice">
        <id column="PK_ID" property="pkId"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
        <result column="DEL_FLAG" property="delFlag"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="TITLE" property="title"/>
        <result column="AWARDS" property="awards"/>
        <result column="CONTENT" property="content"/>
        <result column="IS_POP_SCREEN" property="isPopScreen"/>
        <result column="START_TIME" property="startTime"/>
        <result column="END_TIME" property="endTime"/>
        <result column="WEBSITE_TYPE" property="websiteType"/>
        <result column="FUNCTION_TYPE" property="functionType"/>
        <result column="PUBLISH_LOCATION" property="publishLocation"/>
        <result column="GRADE" property="grade"/>
        <result column="state" property="state"/>
        <result column="APPROVE_STATE" property="approveState"/>
        <result column="APPROVER" property="approver"/>
        <result column="APPROVE_TIME" property="approveTime"/>
        <result column="BATCH" property="batch"/>
        <result column="ROLE" property="role"/>
        <result column="SORT" property="sort"/>
        <result column="TOP" property="top"/>
        <result column="OLD_SORT" property="oldSort"/>
        <result column="MAIL_TYPE" property="mailType"/>
        <result column="SYSTEM_TYPE" property="mailType"/>
    </resultMap>

    <select id="getMemberNotice" resultMap="BaseResultMap">
        select t.title, t.pk_id,t.sort,t.top,
        t.website_type,
        t.function_type,
        t.publish_location,
        t.grade,
        t.awards,
        t.role,
        t.is_pop_screen,
        t.creation_time,
        t.start_time,
        t.end_time,
        t.pk_country,
        t.approve_state,
        case
        when t.end_time &lt; sysdate then 2 else 1
        end state
        from BD_NOTICE t
        -- and t.approve_state = 2
        <include refid="list_where"></include>
        order by t.sort desc, t.creation_time desc
    </select>

    <select id="getAdminNotice" resultMap="BaseResultMap">
        select t.title, t.pk_id,
        t.website_type,
        t.function_type,
        t.publish_location,
        t.grade,
        t.awards,
        t.role,
        t.is_pop_screen,
        t.creation_time,
        t.start_time,
        t.end_time,
        t.pk_country,
        t.approve_state,
        case
        when t.end_time &lt; sysdate then 2 else 1
        end state
        from BD_NOTICE t
        <include refid="list_where"></include>
        order by t.creation_time desc
    </select>
    <sql id="list_where">
        <where>
            t.del_flag = 0
            and t.mail_type = 1
            <if test="pkCountry != null">
                and t.pk_country = #{pkCountry}
            </if>
            <if test="title != null and title != ''">
                and t.title like '%'|| #{title} || '%'
            </if>
            <if test="websiteType != null">
                and t.website_type = #{websiteType}
            </if>
            <if test="functionType != null">
                and t.FUNCTION_TYPE = #{functionType}
            </if>
            <if test="publishLocation != null and publishLocation != ''">
                and t.publish_location like '%'|| #{publishLocation} || '%'
            </if>
            <if test="grade != null and grade != ''">
                and t.grade like '%'|| #{grade} || '%'
            </if>
            <if test="role != null and role != ''">
                and t.ROLE like '%'|| #{role} || '%'
            </if>
            <if test="awards != null and awards != ''">
                and t.awards like '%'|| #{awards} || '%'
            </if>
            <if test="isPopScreen != null">
                and t.is_pop_screen = #{isPopScreen}
            </if>
            <if test='state != null and state == "2"'>
                and t.end_time &lt; sysdate
            </if>
            <if test='state != null and state == "1"'>
                and t.end_time > sysdate
            </if>
            <if test="startDate != null and startDate != ''">
                and t.CREATION_TIME >= to_date(#{startDate}, 'yyyy-mm-dd')
            </if>
            <if test="endDate != null and endDate != ''">
                and t.CREATION_TIME &lt;= to_date(#{endDate} || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
            </if>
            <if test="startFinishtDate != null and startFinishtDate != ''">
                and t.end_time >= to_date(#{startFinishtDate}, 'yyyy-mm-dd')
            </if>
            <if test="endFinishDate != null and endFinishDate != ''">
                and t.end_time &lt;= to_date(#{endFinishDate} || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
            </if>
        </where>
    </sql>
    <select id="getAdminIndexNotice" resultMap="BaseResultMap">
        select t.title, t.pk_id,t.creation_time
        from BD_NOTICE t
        where t.del_flag = 0
        and t.approve_state = 2
        <if test="pkCountry != null">
            and t.pk_country = #{pkCountry}
        </if>
        <if test="websiteType != null">
            and t.website_type = #{websiteType}
        </if>
        <if test="functionType != null">
            and t.FUNCTION_TYPE = #{functionType}
        </if>
        and sysdate >= t.start_time
        and sysdate &lt;= t.end_time
        order by t.creation_time desc
    </select>

    <select id="showIndexNotice" resultType="com.hzs.common.domain.system.ext.BdNoticeExt">
        select n.pk_id, n.title, n.content,n.top,
        n.creation_time creationTime,
        n.grade,n.awards,
        n.publish_location publishLocation,
        n.is_pop_screen isPopScreen
        from bd_notice n
        where n.del_flag = 0
        and n.approve_state = 2
        and n.pk_country = #{pkCountry}
        and n.function_type = #{functionType}
        and n.website_type = #{websiteType}
        and sysdate >= n.start_time
        and sysdate &lt; n.end_time
        <if test="grade != null and grade != ''">
            and instr(',' || n.grade || ',', ',' || #{grade} || ',') > 0
        </if>
        <if test="awards != null and awards != ''">
            and instr(',' || n.awards || ',', ',' || #{awards} || ',') > 0
        </if>
        <if test='isShowDay != null and isShowDay == "1"'>
            and n.creation_time >= trunc(SYSDATE - 6)
        </if>
        order by n.sort desc, n.creation_time desc
    </select>


    <select id="showIndexMailNotice" resultType="com.hzs.common.domain.system.ext.BdNoticeExt">
        select *
        from (select n.pk_id              pkId,
                     n.title,
                     n.content,
                     n.creation_time      creationTime,
                     nvl(d.read_state, 1) readState
              from bd_notice n
                       left join bd_notice_detail d
                                 on n.pk_id = d.pk_notice
                                     and d.pk_member = #{pkMember}
                                     and d.del_flag = 0
              where n.del_flag = 0
                and n.mail_type = 1
                and n.approve_state = 2
                and n.pk_country = #{pkCountry}
                and n.function_type = 2
                and n.website_type = 1
                and sysdate >= n.start_time
                and sysdate &lt;= n.end_time
              union all
              select n.pk_id              pkId,
                     n.title,
                     n.content,
                     n.creation_time      creationTime,
                     nvl(d.read_state, 1) readState
              from bd_notice n
                       inner join bd_member_notice mn
                                  on n.pk_id = mn.pk_notice
                                      and mn.pk_member = #{pkMember}
                       left join bd_notice_detail d
                                 on n.pk_id = d.pk_notice
                                     and d.pk_member = #{pkMember}
                                     and d.del_flag = 0
              where n.del_flag = 0
                and n.mail_type = 2
                and n.approve_state = 2
                and n.pk_country = #{pkCountry}
                and n.function_type = 2
                and n.website_type = 1
                and sysdate >= n.start_time
                and sysdate &lt;= n.end_time
             )
        order by creationTime desc
    </select>

    <select id="showIndexPopScreen" resultMap="BaseResultMap">
        select n.pk_id, n.title, n.content, n.creation_time,n.grade,n.awards,n.publish_location,n.is_pop_screen
        from bd_notice n
        where n.del_flag = 0
        and n.approve_state=2
        and n.pk_country = #{pkCountry}
        and n.function_type = #{functionType}
        and n.website_type = #{websiteType}
        and sysdate >= n.start_time
        and sysdate &lt;= n.end_time
        <if test="(grade != null and grade != '') or (awards != null and awards != '')">
            and (
                <if test="grade != null and grade != ''">
                    instr(',' || n.grade || ',', ',' || #{grade} || ',') > 0
                </if>
                <if test="(grade != null and grade != '') and (awards != null and awards != '')">
                    or
                </if>
                <if test="awards != null and awards != ''">
                    instr(',' || n.awards || ',', ',' || #{awards} || ',') > 0
                </if>
            )
        </if>
        and n.is_pop_screen = 1
        order by n.sort desc, n.creation_time desc
    </select>

    <select id="showIndexNoticeCount" resultType="Integer">
        select count(1)
        from bd_notice n
        where n.del_flag = 0
        and n.approve_state = 2
        and n.pk_country = #{pkCountry}
        and n.function_type = #{functionType}
        and n.website_type = #{websiteType}
        and sysdate >= n.start_time
        and sysdate &lt;= n.end_time
        <if test="systemTypeList != null and systemTypeList.size > 0">
            and n.system_type in
            <foreach collection="systemTypeList" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="showIndexMailCount" resultType="Integer">
        select sum(total) total from (
        select nvl(count(1), 0) - nvl(count(d.read_state), 0) total
        from bd_notice n
        left join bd_notice_detail d
        on n.pk_id = d.pk_notice
        and d.pk_member = #{pkMember}
        and d.del_flag = 0
        where n.del_flag = 0
        and n.mail_type = 1
        and n.approve_state=2
        and n.pk_country = #{pkCountry}
        and n.function_type = #{functionType}
        and n.website_type = #{websiteType}
        and sysdate >= n.start_time
        and sysdate &lt;= n.end_time
        <if test='functionType != null and functionType == "2"'>
            union all
            select nvl(count(1), 0) - nvl(count(d.read_state), 0) total
            from bd_notice n
            inner join bd_member_notice mn
            on n.pk_id = mn.pk_notice
            and mn.pk_member = #{pkMember}
            left join bd_notice_detail d
            on n.pk_id = d.pk_notice
            and d.pk_member = #{pkMember}
            and d.del_flag = 0
            where n.del_flag = 0
            and n.mail_type = 2
            and n.approve_state = 2
            and n.pk_country = #{pkCountry}
            and n.function_type = 2
            and n.website_type = #{websiteType}
            and sysdate >= n.start_time
            and sysdate &lt;= n.end_time
        </if>
        )
    </select>


    <select id="selectAdminNoticeByRole" resultMap="BaseResultMap">
        select n.pk_id, n.title, n.content, n.creation_time, n.role
        from bd_notice n
        where n.del_flag = 0
        and n.approve_state = 2
        and n.pk_country = #{pkCountry}
        and n.function_type = #{functionType}
        and n.website_type = #{websiteType}
        and sysdate >= n.start_time
        and sysdate &lt;= n.end_time
        <foreach item="role" collection="roles" open=" and " separator="and" close="">
            instr(',' || n.role || ',', ',' || #{role} || ',') > 0
        </foreach>
        order by n.creation_time desc
    </select>

    <update id="updateNoticeByBatch" parameterType="com.hzs.common.domain.system.base.BdNotice">
        update bd_notice
        <set>
            <if test="approveState != null">approve_state = #{approveState},</if>
            <if test="approver != null">approver = #{approver},</if>
            <if test="approveTime != null">approve_time = #{approveTime},</if>
            <if test="pkModified != null">pk_modified = #{pkModified},</if>
            modified_time = sysdate
        </set>
        where BATCH = #{batch}
    </update>


    <select id="selectAdminNotice" resultType="com.hzs.common.domain.system.ext.BdNoticeExt">
        select n.title,
               n.pk_id                             pkId,
               n.content,
               n.role,
               n.is_pop_screen                     isPopScreen,
               n.creation_time                     creationTime,
               to_char(n.creation_time, 'yyyy-mm') monthTime
        from BD_NOTICE n
        where n.website_type = 2
          and n.function_type = 1
          and n.del_flag = 0
          and sysdate between n.start_time and n.end_time
          and n.approve_state = 2
          and n.pk_country = #{pkCountry}
        order by n.creation_time desc
    </select>

    <select id="selectUpRecordId" resultMap="BaseResultMap">
        select nid pk_id, nsort sort, top, old_sort
        from (select n.pk_id,
                     lag(n.top, 1) over(order by n.sort desc, n.creation_time desc) top, lag(n.old_sort, 1) over(order by n.sort desc, n.creation_time desc) old_sort, lag(n.pk_id, 1) over(order by n.sort desc, n.creation_time desc) nid, lag(n.sort, 1) over(order by n.sort desc, n.creation_time desc) nsort
              from bd_notice n
              where n.del_flag = 0
                and n.function_type = 1
                and n.website_type = 1
                and n.pk_country = #{pkCountry}
             ) t
        where t.pk_id = #{pkId}

    </select>


    <select id="selectDownRecordId" resultMap="BaseResultMap">
        select nid pk_id, nsort sort, top, old_sort
        from (select n.pk_id,
                     lead(n.top, 1) over(order by n.sort desc, n.creation_time desc) top, lead(n.old_sort, 1) over(order by n.sort desc, n.creation_time desc) old_sort, lead(n.pk_id, 1) over(order by n.sort desc, n.creation_time desc) nid, lead(n.sort, 1) over(order by n.sort desc, n.creation_time desc) nsort
              from bd_notice n
              where n.del_flag = 0
                and n.function_type = 1
                and n.website_type = 1
                and n.pk_country = #{pkCountry}
             ) t
        where t.pk_id = #{pkId}
    </select>


    <update id="updateNoticeSortById" parameterType="com.hzs.common.domain.system.base.BdNotice">
        update bd_notice
        <set>
            <if test="sort != null">sort = #{sort},</if>
            <if test="pkModified != null">pk_modified = #{pkModified},</if>
            <if test="oldSort != null">OLD_SORT = #{oldSort},</if>
            modified_time = sysdate
        </set>
        where PK_ID = #{pkId}
    </update>


    <update id="updateNoticeTopById" parameterType="com.hzs.common.domain.system.base.BdNotice">
        update bd_notice
        <set>
            <if test="top != null">top = #{top},</if>
            <if test="oldSort != null">OLD_SORT = #{oldSort},</if>
            <if test="sort != null">sort = #{sort},</if>
            modified_time = sysdate
        </set>
        where PK_ID = #{pkId}
    </update>


    <select id="selectMaxId" resultType="long">
        select max(n.sort)
        from bd_notice n
        where n.del_flag = 0
          and n.function_type = 1
          and n.website_type = 1
          and sysdate >= n.start_time
          and sysdate &lt;= n.end_time
          and n.sort > 100000000
    </select>


    <select id="selectMaxSort" resultType="long">
        select max(n.sort) + 1
        from bd_notice n
        where n.function_type = 1
          and n.website_type = 1
          and n.pk_country = #{pkCountry}
          and n.sort &lt; 100000000
    </select>

    <select id="selectMemberMail" resultType="com.hzs.common.domain.system.ext.BdNoticeExt">
        select nn.pk_id             pkId,
               nn.title,
               nn.content,
               nvl(d.read_state, 1) readState,
               nn.creation_time     creationTime
        from (select n.pk_id, n.title, n.content, n.creation_time, mn.pk_member
              from bd_notice n,
                   bd_member_notice mn
              where n.pk_id = mn.pk_notice
                and n.function_type = 2
                and n.website_type = 1
                and n.mail_type = 2
                and n.del_flag = 0
                and mn.del_flag = 0
                and sysdate >= n.start_time
                and sysdate &lt;= n.end_time
                and n.pk_country = #{pkCountry}
                and mn.pk_member = #{pkMember}) nn
                 left join bd_notice_detail d
                           on d.pk_member = nn.pk_member
                               and d.pk_notice = nn.pk_id
                               and d.del_flag = 0
        where d.read_state is null
        order by nn.creation_time desc
    </select>


</mapper>
