<?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.AlumniUserMessageMapper" >
  <resultMap id="BaseResultMap" type="com.dgtis.dorm.manager.db.domain.AlumniUserMessage" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="fk_user_id" property="fkUserId" jdbcType="VARCHAR" />
    <result column="message_type" property="messageType" jdbcType="VARCHAR" />
    <result column="content" property="content" jdbcType="VARCHAR" />
    <result column="from_user_id" property="fromUserId" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="is_read" property="isRead" jdbcType="BIT" />
    <result column="is_delete" property="isDelete" jdbcType="BIT" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, fk_user_id, message_type, content, from_user_id, create_time, is_read, is_delete
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from zhll_user_message
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from zhll_user_message
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.dgtis.dorm.manager.db.domain.AlumniUserMessage" >
    insert into zhll_user_message (id, fk_user_id, message_type,
      content, from_user_id, create_time, 
      is_read, is_delete)
    values (#{id,jdbcType=INTEGER}, #{fkUserId,jdbcType=VARCHAR}, #{messageType,jdbcType=VARCHAR}, 
      #{content,jdbcType=VARCHAR}, #{fromUserId,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, 
      #{isRead,jdbcType=BIT}, #{isDelete,jdbcType=BIT})
  </insert>
  <insert id="insertSelective" parameterType="com.dgtis.dorm.manager.db.domain.AlumniUserMessage" >
    insert into zhll_user_message
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="fkUserId != null" >
        fk_user_id,
      </if>
      <if test="messageType != null" >
        message_type,
      </if>
      <if test="content != null" >
        content,
      </if>
      <if test="fromUserId != null" >
        from_user_id,
      </if>
      <if test="createTime != null" >
        create_time,
      </if>
      <if test="isRead != null" >
        is_read,
      </if>
      <if test="isDelete != null" >
        is_delete,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="fkUserId != null" >
        #{fkUserId,jdbcType=VARCHAR},
      </if>
      <if test="messageType != null" >
        #{messageType,jdbcType=VARCHAR},
      </if>
      <if test="content != null" >
        #{content,jdbcType=VARCHAR},
      </if>
      <if test="fromUserId != null" >
        #{fromUserId,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="isRead != null" >
        #{isRead,jdbcType=BIT},
      </if>
      <if test="isDelete != null" >
        #{isDelete,jdbcType=BIT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.dgtis.dorm.manager.db.domain.AlumniUserMessage" >
    update zhll_user_message
    <set >
      <if test="fkUserId != null" >
        fk_user_id = #{fkUserId,jdbcType=VARCHAR},
      </if>
      <if test="messageType != null" >
        message_type = #{messageType,jdbcType=VARCHAR},
      </if>
      <if test="content != null" >
        content = #{content,jdbcType=VARCHAR},
      </if>
      <if test="fromUserId != null" >
        from_user_id = #{fromUserId,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="isRead != null" >
        is_read = #{isRead,jdbcType=BIT},
      </if>
      <if test="isDelete != null" >
        is_delete = #{isDelete,jdbcType=BIT},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.dgtis.dorm.manager.db.domain.AlumniUserMessage" >
    update zhll_user_message
    set fk_user_id = #{fkUserId,jdbcType=VARCHAR},
      message_type = #{messageType,jdbcType=VARCHAR},
      content = #{content,jdbcType=VARCHAR},
      from_user_id = #{fromUserId,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      is_read = #{isRead,jdbcType=BIT},
      is_delete = #{isDelete,jdbcType=BIT}
    where id = #{id,jdbcType=INTEGER}
  </update>
  
  <select id="selectUserMessageAll"
		parameterType="com.dgtis.dorm.manager.db.domain.AlumniUserMessage"
		resultType="java.util.Map">
		select msg.id ,
		msg.message_type ,
		msg.content,
		DATE_FORMAT(msg.create_time,'%Y-%m-%d %h:%i %p') as create_time,
		(select su.user_name from zhll_user su where su.id = msg.fk_user_id)
		as fkUserName,
		(select su.photo from zhll_user su where su.id =
		msg.fk_user_id) as fkPhoto,
		(select su.user_name from zhll_user su
		where su.id = msg.from_user_id) as fromUserName,
		(select su.photo from
		zhll_user su where su.id = msg.from_user_id) as fromPhoto
		from zhll_user_message msg where msg.is_delete=0
		<if test="id != null">
			AND msg.id = #{id}
		</if>
		<if test="fkUserId != null">
			AND msg.fk_user_id = #{fkUserId}
		</if>
		<if test="fromUserId != null">
			AND msg.from_user_id = #{fromUserId}
		</if>
		<if test="messageType != null">
			AND msg.message_type = #{messageType}
		</if>

	</select>

	<select id="selectUserMessage"
		parameterType="com.dgtis.dorm.manager.db.domain.AlumniUserMessage"
		resultType="java.util.Map">
		SELECT  a.*,mm.content as content, mm.create_time as create_time from (
		SELECT au.id as userId, au.user_name, au.photo,
		(
			SELECT message.id from zhll_user_message message where
			1=1 and message.message_type = 'private' and message.is_delete = 0 and
			( 
				( message.from_user_id = au.id and message.fk_user_id = #{fkUserId} )
			or
				( message.fk_user_id = au.id and message.from_user_id =#{fkUserId} )
			) ORDER BY message.create_time DESC LIMIT 0,1
		) as last_message_id,
		( 
			SELECT count(aum.id) from zhll_user_message aum where 1=1
			and aum.from_user_id = au.id and aum.fk_user_id = #{fkUserId}
			and aum.message_type = 'private' and aum.is_delete = 0 and aum.is_read = 0
		) as unReadNum, 
		(
			SELECT COUNT(fu.id) FROM zhll_fri_user fu where 1=1
			and fu.fk_user_id = #{fkUserId} and fri_user_id = au.id
		) as isAttention
		from zhll_user au where 1=1
		and
		(
			( 
				au.id in 
				( 
					SELECT aum1.from_user_id from
					zhll_user_message aum1
					where aum1.message_type = 'private' and
					aum1.is_delete = 0 and
					aum1.fk_user_id = #{fkUserId}
				)
			)
			or
			(
				au.id in
				(
					SELECT aum2.fk_user_id from zhll_user_message aum2
					where aum2.message_type =
					'private' and aum2.is_delete = 0 and
					aum2.from_user_id = #{fkUserId}
				)
			)
		)
			and au.id != #{fkUserId}
		) a , zhll_user_message mm where a.last_message_id = mm.id
	</select>

	<select id="selectSystemUserMessageByUserId"
		parameterType="java.lang.String" resultType="java.util.Map">
		select msg.id ,
		msg.message_type ,
		msg.content,
		msg.is_read,
		DATE_FORMAT(msg.create_time,'%Y-%m-%d %h:%i:%s') as create_time 
		from zhll_user_message msg
		where msg.is_delete=0
		AND (msg.message_type = 'system' or msg.message_type = 'notification')
		AND msg.fk_user_id = #{userId}
		ORDER BY msg.create_time DESC
	</select>

	<select id="selectUserMessageDetails"
		parameterType="java.lang.String" resultType="java.util.Map">
		select msg.id ,
		msg.fk_user_id,
		msg.from_user_id,
		msg.message_type ,
		msg.content,
		DATE_FORMAT(msg.create_time,'%Y-%m-%d %h:%i:%s') as create_time,
		(select su.user_name from zhll_user su where su.id = msg.fk_user_id) as fkUserName,
		(select su.photo from zhll_user su where su.id = msg.fk_user_id) as fkPhoto,
		(select su.user_name from zhll_user su where su.id = msg.from_user_id) as fromUserName,
		(select su.photo from zhll_user su where su.id = msg.from_user_id) as fromPhoto
		from zhll_user_message msg where msg.is_delete=0
		and msg.message_type = 'private'
		and (
			msg.fk_user_id = #{userId} and msg.from_user_id = #{fromUserId}
			or 
			msg.fk_user_id = #{fromUserId} and msg.from_user_id = #{userId}
			)
		ORDER BY msg.id asc
	</select>

	<update id="updateUserMessageRead"
		parameterType="java.lang.String">
		UPDATE zhll_user_message msg set msg.is_read = 1
		where msg.message_type = 'private'
		and msg.fk_user_id = #{userId} and msg.from_user_id = #{fromUserId} 
		and msg.is_read = 0
	</update>
	
	<select id="selectPrivateNoReadCount" parameterType="java.lang.String" resultType="java.lang.Integer"> 
		select count(*) from zhll_user_message
		where message_type = 'private' 
		and is_read = 0 
		and is_delete = 0 
		and fk_user_id = #{userId}
	</select>

    <select id="findMyMsgCount" parameterType="java.lang.String" resultType="java.lang.Integer">
		select count(*) from zhll_user_message
		where is_read = 0
		and is_delete = 0
		and message_type !='private'
		and fk_user_id = #{userId}
	</select>
    <update id="updateMsg" parameterType="java.lang.String">
        update zhll_user_message set is_read = 1
        where id = #{id}
    </update>
	
	<select id="selectNoNormalCountByAOU" parameterType="java.lang.String" resultType="java.lang.Integer" >
		SELECT
           COUNT(ou.id)
        FROM zhll_organize_user a
            LEFT JOIN zhll_organize_user ou ON a.`fk_organize_id` = ou.`fk_organize_id`
            LEFT JOIN zhll_user u ON u.id = ou.fk_user_id
            LEFT JOIN zhll_organize o on o.id = ou.fk_organize_id
        WHERE 1 = 1
            AND ou.is_normal = 0
            AND a.pri_type IN ("1", "2")
            AND a.fk_user_id = #{userId}
	</select>
</mapper>