<?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="cn.com.countrygarden.bi.main.dao.wcx.WcxNoticeMapper">
    <resultMap id="BaseWcxNoticeMap" type="cn.com.countrygarden.bi.main.dto.wcx.WcxNoticeDto">
        <id column="notice_id" property="noticeId"/>
        <result column="notice_title" property="noticeTitle"/>
        <result column="notice_content" property="noticeContent"/>
        <result column="user_id" property="userId"/>
        <result column="status" property="status"/>
        <result column="create_date" property="createDate"/>
        <result column="release_status" property="releaseStatus"/>
        <result column="release_time" property="releaseTime"/>
        <result column="release_user" property="releaseUser"/>
        <result column="operator" property="operator"/>
        <result column="project_name" property="projectName"/>
        <result column="project_code" property="projectCode"/>
        <result column="region_name" property="regionName"/>
        <result column="region_code" property="regionCode"/>
        <result column="update_time" property="updateTime"/>
        <result column="update_by" property="updateBy"/>
        <result column="create_by" property="createBy"/>
    </resultMap>

    <select id="selectPageListWcxNotice" resultMap="BaseWcxNoticeMap">
 SELECT
  n.notice_id,
  n.notice_title,
  n.notice_content,
  n.project_code,
  n.project_name,
  n.region_code,
  n.region_name,
  n.user_id,
  n.status,
  n.create_date,
  n.release_status,
  n.release_time,
  n.release_user,
  n.operator,
  n.update_time,
        n.create_by,
        pic.file_url pic_file_url,
        pic.file_id pic_file_id,
        pic.order_no pic_order_no,
        (SELECT COUNT(re.reading_id) FROM wcx_notice_reading re
        WHERE re.notice_id=n.notice_id) AS reading
        FROM (
        SELECT notice_id,createDate FROM (
        SELECT ti.notice_id, ti.create_date createDate,ti.update_time updateTime
        FROM wcx_notice ti
        WHERE ti.status=1
        <if test="noticeTitle!=null and noticeTitle!=''">
            and ti.notice_title like concat('%',#{noticeTitle},'%')
        </if>
        <if test="releaseStatus!=null and releaseStatus!=''">
            and ti.release_status=#{releaseStatus}
        </if>
        <if test="releaseUser!=null and releaseUser!=''">
            and ti.release_user like concat('%',#{releaseUser},'%')
        </if>
        <if test="startDateStr!=null and startDateStr!='' and endDateStr!=null and endDateStr!=''">
            and ti.release_time BETWEEN #{startDateStr} and #{endDateStr}
        </if>
        ) temiall
        ORDER BY temiall.createDate DESC
        LIMIT #{pageOffset},#{pageSize}
        ) tiall
        JOIN wcx_notice n ON tiall.notice_id = n.notice_id
        LEFT JOIN wcx_notice_file pic ON pic.notice_id = n.notice_id AND pic.file_type = '1' and pic.status=1
        <where>
            <if test="noticeTitle!=null and noticeTitle!=''">
               and n.notice_title like concat('%',#{noticeTitle},'%')
            </if>
            <if test="releaseStatus!=null and releaseStatus!=''">
                and n.release_status=#{releaseStatus}
            </if>
            <if test="releaseUser!=null and releaseUser!=''">
                and n.release_user like concat('%',#{releaseUser},'%')
            </if>
            <if test="startDateStr!=null and startDateStr!='' and endDateStr!=null and endDateStr!=''">
                and n.release_time BETWEEN #{startDateStr} and #{endDateStr}
            </if>
        </where>
        ORDER BY n.create_date DESC
    </select>

    <select id="selectIndexPageWcxNoticeCount" resultType="java.lang.Long">
             SELECT COUNT(tem.notice_id)
        FROM (
        SELECT
        ilead.notice_id
        FROM wcx_notice ilead
        WHERE ilead.status=1
        <if test="noticeTitle!=null and noticeTitle!=''">
            and ilead.notice_title like concat('%',#{noticeTitle},'%')
        </if>
        <if test="releaseStatus!=null and releaseStatus!=''">
            and ilead.release_status=#{releaseStatus}
        </if>
        <if test="releaseUser!=null and releaseUser!=''">
            and ilead.release_user like concat('%',#{releaseUser},'%')
        </if>
        <if test="startDateStr!=null and startDateStr!='' and endDateStr!=null and endDateStr!=''">
            and ilead.release_time BETWEEN #{startDateStr} and #{endDateStr}
        </if>
        ) tem
    </select>

    <insert id="addNotice">
        INSERT INTO wcx_notice (notice_id,notice_title,notice_content,user_id,`status`,create_date,release_status,operator,region_code,region_name,create_by)
        VALUES(#{noticeId},#{noticeTitle},#{noticeContent},#{userId},#{status},#{createDate},#{releaseStatus},#{operator},#{regionCode},#{regionName},#{createBy});
    </insert>

    <select id="selectId" resultMap="BaseWcxNoticeMap">
        SELECT notice_id,notice_title,notice_content,project_code,project_name,region_code,region_name,user_id,`status`,
        create_date,release_status,release_time,release_user,operator,update_time FROM wcx_notice
         WHERE notice_id=#{noticeId}
    </select>

    <update id="updateReleaseStatus">
       UPDATE wcx_notice SET release_status=#{releaseStatus},release_time=#{releaseTime},release_user=#{releaseUser},
       update_time = #{updateTime}
       WHERE notice_id = #{noticeId};
    </update>

    <update id="updateDelete">
       UPDATE wcx_notice SET status=#{status},update_by=#{updateBy},
       update_time = #{updateTime}
       WHERE notice_id = #{noticeId};
    </update>

    <select id="check" resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxNoticeDto">
       SELECT n.notice_id as noticeId,n.notice_title as noticeTitle,n.notice_content as noticeContent,n.project_code,n.project_name as projectName,n.region_code,n.region_name as regionName,n.user_id,n.`status`,n.create_date as createDate,n.release_status,
n.release_time,n.release_user,n.operator,n.update_time,n.update_by,f.file_url as dialogImageUrl,n.create_by as createBy FROM wcx_notice n
LEFT JOIN wcx_notice_file f ON f.notice_id=n.notice_id
WHERE n.notice_id=#{noticeId}
    </select>

    <update id="update">
        UPDATE wcx_notice SET
  notice_title = #{noticeTitle},
  notice_content = #{noticeContent},
  update_time =#{updateTime},
  update_by = #{updateBy}
WHERE notice_id = #{noticeId};
    </update>

    <select id="selectPageListWcxNoticeExel" resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxNoticeDtoVO">
        SELECT
        n.notice_id,
        n.notice_title as noticeTitle,
        n.notice_content,
        n.project_code,
        n.project_name,
        n.region_code,
        n.region_name,
        n.user_id,
        n.status,
        n.create_date as createDate,
        n.release_status as releaseStatus,
        n.release_time as releaseTime,
        n.release_user as releaseUser,
        n.operator,
        n.update_time,
        pic.file_url pic_file_url,
        pic.file_id pic_file_id,
        pic.order_no pic_order_no,
        n.create_by as createBy
        FROM (
        SELECT notice_id,createDate FROM (
        SELECT ti.notice_id, ti.create_date createDate,ti.update_time updateTime
        FROM wcx_notice ti
        WHERE ti.status=1
        ) temiall
        ORDER BY temiall.createDate DESC, temiall.notice_id
        ) tiall
        JOIN wcx_notice n ON tiall.notice_id = n.notice_id
        LEFT JOIN wcx_notice_file pic ON pic.notice_id = n.notice_id AND pic.file_type = '1' and pic.status=1
        <where>
            <if test="noticeTitle!=null and noticeTitle!=''">
                and n.notice_title like concat('%',#{noticeTitle},'%')
            </if>
            <if test="releaseStatus!=null and releaseStatus!=''">
                and n.release_status=#{releaseStatus}
            </if>
            <if test="releaseUser!=null and releaseUser!=''">
                and n.release_user like concat('%',#{releaseUser},'%')
            </if>
            <if test="startDateStr!=null and startDateStr!='' and endDateStr!=null and endDateStr!=''">
                and n.release_time BETWEEN #{startDateStr} and #{endDateStr}
            </if>
        </where>
        ORDER BY n.create_date DESC, n.notice_id
    </select>


</mapper>