<?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.NewsCommentMapper">
  <resultMap id="BaseResultMap" type="com.dgtis.dorm.manager.db.domain.NewsComment">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="fk_user_id" jdbcType="INTEGER" property="userId" />
    <result column="content" jdbcType="VARCHAR" property="content" />
    <result column="fk_news_id" jdbcType="INTEGER" property="newsId" />
    <result column="create_date" jdbcType="TIMESTAMP" property="createDate" />

    <result column="userName" jdbcType="VARCHAR" property="userName" />
    <result column="photo" jdbcType="VARCHAR" property="photo" />
    <result column="newsTitle" jdbcType="VARCHAR" property="newsTitle" />

    
     <result column="comment_first_id" jdbcType="INTEGER" property="commentFirstId" />
    <result column="comment_second_id" jdbcType="INTEGER" property="commentSecondId" />
    <result column="commentSecondUserName" jdbcType="VARCHAR" property="commentSecondUserName" />
  </resultMap>

  <sql id="Base_Column_List">
    zhll_news_comment.id, fk_user_id, content, fk_news_id, zhll_news_comment.create_date
    ,comment_first_id,comment_second_id
  </sql>

  <select id="selectByExample" parameterType="com.dgtis.dorm.manager.db.domain.NewsComment" resultMap="BaseResultMap">
    select distinct
    <include refid="Base_Column_List" />
    ,au.user_name as userName, au.photo
    from zhll_news_comment
    left join zhll_user au
    on zhll_news_comment.fk_user_id = au.id
    <where>
    	 comment_first_id is null 
      <if test="userId != 0">
          and fk_user_id = #{userId }
      </if>
      <if test="newsId != 0">
          and fk_news_id = #{newsId }
      </if>

    </where>
      order by create_date DESC
  </select>

  <select id="selectCommonList" parameterType="com.dgtis.dorm.manager.db.domain.NewsComment" resultMap="BaseResultMap">
    select distinct
    <include refid="Base_Column_List" />
    ,au.user_name as userName, au.photo,
    anews.news_title as newsTitle
    from zhll_news_comment
    left join zhll_user au
    on zhll_news_comment.fk_user_id = au.id
    left join zhll_news anews
    on zhll_news_comment.fk_news_id = anews.id
    <where>
      comment_first_id is null
      <if test="userName != null and userName != '' ">
        and au.user_name like concat ('%',#{userName },'%')
      </if>
      <if test="content != null and content != '' ">
        and content like concat ('%',#{content },'%')
      </if>
      <if test="createDate != null and createDate != '' ">
        and datediff (zhll_news_comment.create_date , #{createDate } ) = 0
      </if>
    </where>
    order by create_date DESC
  </select>

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

  <select id="countByExample" parameterType="com.dgtis.dorm.manager.db.domain.NewsComment" resultType="java.lang.Long">
    select count(*) from zhll_news_comment
    <where>
     comment_first_id is null 
        <if test="userId != 0">
            and fk_user_id = #{userId }
        </if>
        <if test="newsId != 0">
            and fk_news_id = #{newsId }
        </if>
      <if test="createDate != null and createDate != ''">
        and datediff (create_date , #{createDate } ) = 0
      </if>
    </where>
  </select>

  <select id="countByComList" parameterType="com.dgtis.dorm.manager.db.domain.NewsComment" resultType="java.lang.Long">
    select count(*) from (select an.comment_first_id, an.id,au.photo,an.content, an.create_date ,au.user_name from
    zhll_news_comment an
    left join zhll_user au on
    an.fk_user_id = au.id
    <where>
      an.comment_first_id is null
      <if test="userName != null and userName != '' ">
        and au.user_name like concat('%',#{userName },'%')
      </if>
      <if test="content != null and content != '' ">
        and an.content like concat('%',#{content },'%')
      </if>
      <if test="createDate != null and createDate != '' ">
        and datediff ( an.create_date , #{createDate } ) = 0
      </if>
    </where>
    ) counter
  </select>

  <insert id="insertSelective" parameterType="com.dgtis.dorm.manager.db.domain.NewsComment">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into zhll_news_comment
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="userId != null">
          fk_user_id,
      </if>
      <if test="content != null">
          content,
      </if>
      <if test="newsId != null">
          fk_news_id,
      </if>
      <if test="createDate != null">
          create_date,
      </if>
      <if test="commentFirstId != null">
          comment_first_id,
      </if>
      <if test="commentSecondId != null">
          comment_second_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="userId != null">
        #{userId,jdbcType=VARCHAR},
      </if>
      <if test="content != null">
        #{content,jdbcType=VARCHAR},
      </if>
      <if test="newsId != null">
        #{newsId,jdbcType=INTEGER},
      </if>
      <if test="createDate != null">
        #{createDate,jdbcType=TIMESTAMP },
      </if>
      <if test="commentFirstId != null">
          #{commentFirstId,jdbcType=INTEGER},
      </if>
      <if test="commentSecondId != null">
           #{commentSecondId,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from zhll_news_comment
    where id = #{id,jdbcType=INTEGER }
  </delete>
  
  <select id="selectByZiExample"  resultMap="BaseResultMap">
    select distinct
    zhll_news_comment.id, fk_user_id, content, fk_news_id, zhll_news_comment.create_date
    ,comment_first_id,comment_second_id ,au.user_name as userName, au.photo
    ,(select t.user_name from zhll_user t where
   		 t.id=(select tc.fk_user_id from zhll_news_comment tc where tc.id=zhll_news_comment.comment_second_id )
    ) as commentSecondUserName
    from zhll_news_comment
    left join zhll_user au  on zhll_news_comment.fk_user_id = au.id
    
   where
           comment_first_id = #{commentfirstid}
       
     
      order by create_date DESC
  </select>

  <delete id="deleteByNewId" parameterType="java.lang.Integer">
    delete from zhll_news_comment
    where fk_news_id = #{newsId,jdbcType=INTEGER }
  </delete>
  
</mapper>