<?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.dgtis.dorm.manager.db.dao.NewsMapper">
  <resultMap id="BaseResultMap" type="com.dgtis.dorm.manager.db.domain.News">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="news_type" jdbcType="VARCHAR" property="type" />
    <result column="news_title" jdbcType="VARCHAR" property="title" />
    <result column="news_content" jdbcType="VARCHAR" property="content" />
    <result column="weight" jdbcType="INTEGER" property="weight" />
    <result column="news_image" jdbcType="VARCHAR" property="image" />
    <result column="news_file" jdbcType="VARCHAR" property="file" />
    <result column="file_name" jdbcType="VARCHAR" property="fileName" />
    <result column="create_date" jdbcType="TIMESTAMP" property="createDate" />
    <result column="activity_start_date" jdbcType="TIMESTAMP" property="activityStartDate" />
    <result column="activity_end_date" jdbcType="TIMESTAMP" property="activityEndDate" />
    <result column="activity" jdbcType="VARCHAR" property="activity" />
    <result column="creator" jdbcType="VARCHAR" property="creator" />
    <result column="creatorId" jdbcType="VARCHAR" property="creatorId" />
    <result column="order_index" jdbcType="INTEGER" property="orderIndex" />
    <result column="activity_audit_state" jdbcType="INTEGER" property="activityAuditState" />
    <result column="activity_for_news" jdbcType="INTEGER" property="activityForNews" />
    <result column="activity_address" jdbcType="VARCHAR" property="activityAddress" />

    <result column="activityForNewsDate" jdbcType="TIMESTAMP" property="activityForNewsDate" />
    <result column="activityForNewsAddress" jdbcType="VARCHAR" property="activityForNewsAddress" />

    <result column="commentCount" jdbcType="INTEGER" property="commentCount" />
    <result column="zanCount" jdbcType="INTEGER" property="zanCount" />
    <result column="userCount" jdbcType="INTEGER" property="userCount" />
    <result column="hadCount" jdbcType="INTEGER" property="hadCount" />
    <result column="activityForNewsFlag" jdbcType="VARCHAR" property="activityForNewsFlag" />

    <result column="universitarios" jdbcType="VARCHAR" property="universitarios" />

    <result column="photo" jdbcType="VARCHAR" property="photo" />
    <result column="activity_cost" jdbcType="DECIMAL" property="activityCost" />
    <result column="activity_limit" jdbcType="INTEGER" property="activityLimit" />
    <result column="is_admin" jdbcType="VARCHAR" property="isAdmin" />
    <result column="activity_type" jdbcType="VARCHAR" property="activityType" />
  </resultMap>

  <sql id="Base_Column_List">
    id, news_type, news_title, news_content, weight, news_image, news_file, create_date, activity_start_date,
    activity_end_date, activity, creator, creatorId,
     order_index,activity_audit_state,activity_for_news,
    activity_address, universitarios, activity_cost, file_name,activity_limit,is_admin, activity_type
  </sql>

  <select id="selectByExample" parameterType="com.dgtis.dorm.manager.db.domain.News" resultMap="BaseResultMap">
    select distinct
    <include refid="Base_Column_List" />
    <if test="type == 'activity'">
      ,(select count(id) from alumni_activity_user au where au.fk_news_id = zhll_news.id) as userCount
    </if>
    from zhll_news
    <where>
      <if test="newsCriterion">
        ${newsCriterion.sql }
      </if>
      order by weight DESC, create_date DESC, order_index DESC
    </where>
  </select>

  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from zhll_news
    where id = #{id,jdbcType=INTEGER}
  </select>

  <select id="countByExample" parameterType="com.dgtis.dorm.manager.db.domain.News" resultType="java.lang.Long">
    select count(*) from zhll_news
    <where>
      <if test="newsCriterion">
        ${newsCriterion.sql }
      </if>
    </where>
  </select>

  <select id="selectByExampleWithComment" parameterType="com.dgtis.dorm.manager.db.domain.News" resultMap="BaseResultMap">
    select distinct
    <include refid="Base_Column_List" />
    ,(select count(id) from zhll_news_comment nc where nc.fk_news_id = zhll_news.id and nc.comment_first_id is null ) as commentCount,
     (select count(id) from zhll_news_zan nz where nz.fk_news_id = zhll_news.id) as zanCount
    <if test="newsCriterion != null and newsCriterion.userIdForZan != 0">
    ,(select count(id) from zhll_news_zan nz where nz.fk_news_id = zhll_news.id and nz.fk_user_id = #{newsCriterion.userIdForZan}) as hadCount
    </if>
    from zhll_news
    <where>
      <if test="newsCriterion">
        ${newsCriterion.sql }
      </if>
      order by weight DESC, create_date DESC, order_index DESC
    </where>
  </select>

    <select id="selectByExampleForActivityNews" parameterType="com.dgtis.dorm.manager.db.domain.News" resultMap="BaseResultMap">
        select distinct
        <include refid="Base_Column_List" />
        ,(select count(id) from zhll_news_comment nc where nc.fk_news_id = zhll_news.id) as commentCount,
         (select count(id) from zhll_news_zan nz where nz.fk_news_id = zhll_news.id) as zanCount,

         (select activity_start_date from zhll_news an where an.id = zhll_news.id) as activityForNewsDate,
         (select activity_address from zhll_news an where an.id = zhll_news.id) as activityForNewsAddress,
         (select activity from zhll_news an where an.id = zhll_news.id) as activityForNewsFlag
      from zhll_news
        <where>
            <if test="newsCriterion">
                ${newsCriterion.sql }
            </if>
        </where>
      order by weight DESC, create_date DESC, order_index DESC
    </select>

  <select id="selectByOrgIdForActivityNews" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select distinct
    zhll_news.*,au.photo,
    (select count(id) from zhll_news_comment nc where nc.fk_news_id = zhll_news.id) as commentCount,
    (select count(id) from zhll_news_zan nz where nz.fk_news_id = zhll_news.id) as zanCount,
    (select count(id) from alumni_activity_user au where au.fk_news_id = zhll_news.id) as userCount,
    (select activity_start_date from zhll_news an where an.id = zhll_news.activity_for_news) as activityForNewsDate,
    (select activity_address from zhll_news an where an.id = zhll_news.activity_for_news) as activityForNewsAddress,
    (select activity from zhll_news an where an.id = zhll_news.activity_for_news) as activityForNewsFlag
    <if test="userIdForZan != null and userIdForZan != 0">
    ,(select count(id) from zhll_news_zan nz where nz.fk_news_id = zhll_news.id and nz.fk_user_id = #{userIdForZan}) as hadCount
    </if>
    from zhll_news
    left join zhll_news_organize ano
    on zhll_news.id = ano.fk_news_id
    left join zhll_user au
    on zhll_news.creatorId = au.id
    <where>
      ano.fk_organize_id = #{orgId}
    </where>
    order by zhll_news.weight DESC, zhll_news.create_date DESC, zhll_news.order_index DESC
  </select>

  <select id="countForFriendNews" parameterType="java.lang.Integer" resultType="java.lang.Integer">
    select count(zhll_news.id)
    from zhll_news
    left join zhll_fri_user afu
    on zhll_news.creatorId = afu.fri_user_id
    <where>
      afu.fk_user_id = #{userId}
      and (zhll_news.news_type = 'activity' or zhll_news.news_type = 'tidings')
    </where>
  </select>

    <select id="selectForFriendNews" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select distinct
        zhll_news.*,au.photo,
        (select count(id) from zhll_news_comment nc where nc.fk_news_id = zhll_news.id) as commentCount,
        (select count(id) from zhll_news_zan nz where nz.fk_news_id = zhll_news.id) as zanCount,
        (select count(id) from alumni_activity_user au where au.fk_news_id = zhll_news.activity_for_news) as userCount,
        (select activity_start_date from zhll_news an where an.id = zhll_news.activity_for_news) as activityForNewsDate,
        (select activity_address from zhll_news an where an.id = zhll_news.activity_for_news) as activityForNewsAddress,
        (select activity from zhll_news an where an.id = zhll_news.activity_for_news) as activityForNewsFlag
        from zhll_news
        left join zhll_fri_user afu
        on zhll_news.creatorId = afu.fri_user_id
        left join zhll_user au
        on afu.fri_user_id = au.id
        <where>
            afu.fk_user_id = #{userId}
            and (zhll_news.news_type = 'activity' or zhll_news.news_type = 'tidings')
        </where>
        order by zhll_news.weight DESC, zhll_news.create_date DESC, zhll_news.order_index DESC
    </select>

    <select id="countForActivityNews" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(zhll_news.id)
        from zhll_news
        left join zhll_news_organize ano
        on zhll_news.id = ano.fk_news_id
        <where>
            ano.fk_organize_id = #{orgId}
        </where>
    </select>

  <select id="selectByPrimaryKeyWithComment" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    ,(select count(id) from zhll_news_comment nc where nc.fk_news_id = zhll_news.id and nc.comment_first_id is null) as commentCount
    ,(select count(id) from zhll_news_zan nz where nz.fk_news_id = zhll_news.id) as zanCount
    <if test="userIdForZan != null and userIdForZan != 0">
    ,(select count(id) from zhll_news_zan nz where nz.fk_news_id = zhll_news.id and nz.fk_user_id = #{userIdForZan}) as hadCount
    </if>
    from zhll_news
    where id = #{id,jdbcType=INTEGER}
  </select>

  <select id="selectByExampleForMyJoin" parameterType="com.dgtis.dorm.manager.db.domain.News" resultMap="BaseResultMap">
    select distinct zhll_news.*
    ,(select count(id) from alumni_activity_user au where au.fk_news_id = zhll_news.id) as userCount
     from zhll_news
    left join alumni_activity_user aau
    on zhll_news.id = aau.fk_news_id
    <where>
        <if test="newsCriterion">
            ${newsCriterion.sql }
            and aau.fk_user_id = #{newsCriterion.userId}
        </if>
    </where>

  </select>

  <select id="countForMyJoin" parameterType="com.dgtis.dorm.manager.db.domain.News" resultType="java.lang.Integer">
    select count(zhll_news.id)
    from zhll_news
    left join alumni_activity_user aau
    on zhll_news.id = aau.fk_news_id
    <where>
            <if test="newsCriterion">
                ${newsCriterion.sql }
            </if>
            and aau.fk_user_id = #{newsCriterion.userId}
    </where>
  </select>

  <insert id="insertSelective" parameterType="com.dgtis.dorm.manager.db.domain.News">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into zhll_news
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="type != null">
        news_type,
      </if>
      <if test="title != null">
        news_title,
      </if>
      <if test="content != null">
        news_content,
      </if>
      <if test="weight != null">
        weight,
      </if>
      <if test="image != null">
        news_image,
      </if>
      <if test="file != null">
        news_file,
      </if>
      <if test="fileName != null">
        file_name,
      </if>
      <if test="createDate != null">
        create_date,
      </if>
      <if test="activityStartDate != null">
        activity_start_date,
      </if>
      <if test="activityEndDate != null">
        activity_end_date,
      </if>
      <if test="activity != null">
        activity,
      </if>
      <if test="creator != null">
        creator,
      </if>
      <if test="creatorId != null">
        creatorId,
      </if>
      <if test="activityAddress != null">
        activity_address,
      </if>
      <if test="orderIndex != null">
        order_index,
      </if>
      <if test="activityAuditState != null">
        activity_audit_state,
      </if>
      <if test="activityForNews != 0">
        activity_for_news,
      </if>
      <if test="universitarios != null">
        universitarios,
      </if>
      <if test="isAdmin != null">
        is_admin,
      </if>
      <if test="activityLimit != null">
        activity_Limit,
      </if>
      <if test="activityCost != null">
        activity_cost,
      </if>
      <if test="activityType != null">
        activity_type
      </if>
    </trim>

    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="type != null">
        #{type,jdbcType=VARCHAR},
      </if>
      <if test="title != null">
        #{title,jdbcType=VARCHAR},
      </if>
      <if test="content != null">
        #{content,jdbcType=VARCHAR},
      </if>
      <if test="weight != null">
        #{weight,jdbcType=INTEGER},
      </if>
      <if test="image != null">
        #{image,jdbcType=VARCHAR},
      </if>
      <if test="file != null">
        #{file,jdbcType=VARCHAR},
      </if>
      <if test="fileName != null">
        #{fileName, jdbcType=VARCHAR},
      </if>
      <if test="createDate != null">
        #{createDate,jdbcType=TIMESTAMP},
      </if>
      <if test="activityStartDate != null">
        #{activityStartDate,jdbcType=TIMESTAMP},
      </if>
      <if test="activityEndDate != null">
        #{activityEndDate,jdbcType=TIMESTAMP},
      </if>
      <if test="activity != null">
        #{activity,jdbcType=VARCHAR},
      </if>
      <if test="creator != null">
        #{creator,jdbcType=VARCHAR},
      </if>
      <if test="creatorId != null">
        #{creatorId,jdbcType=VARCHAR},
      </if>
      <if test="registStartDate != null">
        #{registStartDate, jdbcType=TIMESTAMP},
      </if>
      <if test="registEndDate != null">
        #{registEndDate, jdbcType=TIMESTAMP},
      </if>
      <if test="activityAddress != null">
        #{activityAddress,jdbcType=VARCHAR},
      </if>
      <if test="orderIndex != null">
        #{orderIndex, jdbcType=INTEGER},
      </if>
      <if test="activityAuditState != null">
        #{activityAuditState, jdbcType=INTEGER},
      </if>
      <if test="activityForNews != 0">
        #{activityForNews, jdbcType=INTEGER},
      </if>
      <if test="universitarios != null">
        #{universitarios, jdbcType=VARCHAR},
      </if>
      <if test="isAdmin != null">
      	#{isAdmin, jdbcType=VARCHAR},
      </if>
      <if test="activityLimit != null">
        #{activityLimit, jdbcType=INTEGER},
      </if>
      <if test="activityCost != null">
        #{activityCost, jdbcType=DECIMAL},
      </if>
      <if test="activityType != null">
        #{activityType, jdbcType=VARCHAR}
      </if>
    </trim>
  </insert>

  <update id="updateByPrimaryKeySelective" parameterType="com.dgtis.dorm.manager.db.domain.News">
    update zhll_news
    <set>
      <if test="type != null">
        news_type = #{type,jdbcType=VARCHAR},
      </if>
      <if test="title != null">
        news_title = #{title,jdbcType=VARCHAR},
      </if>
      <if test="content != null">
        news_content = #{content,jdbcType=VARCHAR},
      </if>
      <if test="weight != null">
        weight = #{weight,jdbcType=INTEGER},
      </if>
      <if test="image != null">
        news_image = #{image,jdbcType=VARCHAR},
      </if>
      <if test="file != null">
        news_file = #{file,jdbcType=VARCHAR},
      </if>
      <if test="fileName != null">
        file_name = #{fileName,jdbcType=VARCHAR},
      </if>
      <if test="createDate != null">
        create_date = #{createDate,jdbcType=TIMESTAMP},
      </if>
      <if test="activityStartDate != null">
        activity_start_date = #{activityStartDate,jdbcType=TIMESTAMP},
      </if>
      <if test="activityEndDate != null">
        activity_end_date = #{activityEndDate,jdbcType=TIMESTAMP},
      </if>
      <if test="activityAddress != null">
        activity_address = #{activityAddress,jdbcType=VARCHAR},
      </if>
      <if test="activity != null">
        activity = #{activity,jdbcType=VARCHAR},
      </if>
      <if test="creator != null">
        creator = #{creator,jdbcType=VARCHAR},
      </if>
      <if test="creatorId != null">
        creatorId = #{creatorId,jdbcType=VARCHAR},
      </if>
      <if test="activityAuditState != null">
        activity_audit_state = #{activityAuditState, jdbcType=INTEGER},
      </if>
      <if test="orderIndex != 0">
        order_index = #{orderIndex, jdbcType=INTEGER},
      </if>
      <if test="activityForNews != 0">
        activity_for_news = #{activityForNews, jdbcType=INTEGER},
      </if>
      <if test="universitarios != null">
        universitarios = #{universitarios, jdbcType=VARCHAR},
      </if>
      <if test="activityLimit != null">
        activity_limit = #{activityLimit, jdbcType=DECIMAL},
      </if>
      <if test="activityCost != null">
        activity_cost = #{activityCost, jdbcType=DECIMAL},
      </if>
      <if test="activityType != null">
        activity_type = #{activityType, jdbcType=VARCHAR}
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from zhll_news
    where id = #{id,jdbcType=INTEGER}
  </delete>
</mapper>