<?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.tangguo.mapper.BbsMomentMapper">
	
	<select id="selectBbsMomentList" resultType="com.tangguo.domain.BbsMoment">
        SELECT
            m.*,
            uv.nick_name,
            ud.dept_name,
            ud.ancestors
        FROM
            bbs_moment m
        LEFT JOIN
            sys_user uv ON uv.user_name = m.user_name
        LEFT JOIN
            sys_dept ud ON ud.dept_id = uv.dept_id
        <where>
            <if test="moment.content != null and moment.content != ''">
                m.content LIKE CONCAT('%', #{moment.content}, '%')
            </if>
	        <if test="moment.userName != null and moment.userName != ''">
		        AND uv.user_name LIKE CONCAT('%', #{moment.userName}, '%')
	        </if>
	        <if test="moment.nickName != null and moment.nickName != ''">
		        AND uv.nick_name LIKE CONCAT('%', #{moment.nickName}, '%')
	        </if>
            <if test="moment.topicNames != null and moment.topicNames != ''">
                AND m.topic_names LIKE CONCAT('%', #{moment.topicNames}, '%')
            </if>
            <if test="moment.params.startTime != null and moment.params.startTime != ''">
                AND DATE_FORMAT(m.create_time, '%Y-%m-%d') >= #{moment.params.startTime}
            </if>
            <if test="moment.params.endTime != null and moment.params.endTime != ''">
                AND DATE_FORMAT(m.create_time, '%Y-%m-%d') &lt;= #{moment.params.endTime}
            </if>
            <if test="moment.deptName != null and moment.deptName != ''">
                AND d.dept_name LIKE CONCAT('%', #{moment.deptName}, '%')
            </if>
        </where>
        ORDER BY
            m.create_time DESC
    </select>
    
    
    <select id="selectBbsMomentDept" resultType="java.lang.String">
        SELECT
            dept_name
        FROM
            sys_dept
        WHERE
           dept_type = '3'
	   	AND
           dept_id IN
            <foreach collection="deptIds" item="deptId" open="(" separator="," close=")">
                #{deptId}
            </foreach>
        LIMIT 1
    </select>
    
    
	<select id="selectUserMomentCount" resultType="com.tangguo.domain.vo.BbsUserMomentCountVO">
        SELECT
            (SELECT COUNT(*) FROM bbs_moment WHERE user_name = #{userName}) AS moment_count,
            (SELECT COUNT(*) FROM bbs_moment_comment WHERE user_name = #{userName}) AS comment_count,
            (SELECT COUNT(*) FROM bbs_moment_like WHERE user_name = #{userName}) AS like_count
    </select>
    
    
    <select id="selectUserMoments" resultType="com.tangguo.domain.vo.BbsMomentListVO">
        SELECT
            m.*,
            uv.nick_name,
            uv.avatar,
            IF(l.id IS NOT NULL, 1, 0) AS is_like,
            IF(v.id IS NOT NULL, 1, 0) AS is_vote
        FROM
            bbs_moment m
        LEFT JOIN
            sys_user uv ON uv.user_name = m.user_name
        LEFT JOIN
            (
                SELECT
                    id, moment_id
                FROM
                    bbs_moment_like
                WHERE
                    user_name = #{userName}
            ) l ON l.moment_id = m.id
        LEFT JOIN
            (
                SELECT
                    id, moment_id
                FROM
                    bbs_moment_vote
                WHERE
                    user_name = #{userName}
            ) v ON v.moment_id = m.id
        <where>
            <if test="1==1">
                m.user_name = #{userName}
            </if>
            <if test="content != null and content != ''">
                AND m.content LIKE CONCAT('%', #{content}, '%')
            </if>
        </where>
        ORDER BY
            m.create_time DESC
    </select>
    
    
    <select id="selectMoments" resultType="com.tangguo.domain.vo.BbsMomentListVO">
        SELECT
            m.*,
            uv.nick_name,
            uv.avatar,
            IF(l.id IS NOT NULL, 1, 0) AS is_like,
            IF(v.id IS NOT NULL, 1, 0) AS is_vote,
            IF(m.user_name = #{userName}, 1, 0) AS is_self
        FROM
            bbs_moment m
        LEFT JOIN
            sys_user uv ON uv.user_name = m.user_name
        LEFT JOIN
            (
                SELECT
                    id, moment_id
                FROM
                    bbs_moment_like
                WHERE
                    user_name = #{userName}
            ) l ON l.moment_id = m.id
        LEFT JOIN
            (
                SELECT
                    id, moment_id
                FROM
                    bbs_moment_vote
                WHERE
                    user_name = #{userName}
            ) v ON v.moment_id = m.id
        <where>
            <if test="bo.content != null and bo.content != ''">
                (m.content LIKE CONCAT('%', #{bo.content}, '%')
        			OR uv.user_name LIKE CONCAT('%', #{bo.content}, '%')
        				OR uv.nick_name LIKE CONCAT('%', #{bo.content}, '%'))
            </if>
            <if test="bo.topicName != null and bo.topicName != ''">
                AND m.topic_names LIKE CONCAT('%', #{bo.topicName}, '%')
            </if>
            <if test="bo.topicId != null">
                AND FIND_IN_SET(#{bo.topicId}, m.topic_ids)
            </if>
        </where>
        ORDER BY
            m.create_time DESC
    </select>
    
    
    <select id="selectMomentDetail" resultType="com.tangguo.domain.vo.BbsMomentListVO">
        SELECT
            m.*,
            uv.nick_name,
            uv.avatar,
            IF(l.id IS NOT NULL, 1, 0) AS is_like,
            IF(v.id IS NOT NULL, 1, 0) AS is_vote,
            IF(m.user_name = #{userName}, 1, 0) AS is_self
        FROM
            bbs_moment m
        LEFT JOIN
            sys_user uv ON uv.user_name = m.user_name
        LEFT JOIN
            (
                SELECT
                    id, moment_id
                FROM
                    bbs_moment_like
                WHERE
                    user_name = #{userName}
            ) l ON l.moment_id = m.id
        LEFT JOIN
            (
            SELECT
                id, moment_id
            FROM
                bbs_moment_vote
            WHERE
                user_name = #{userName}
            ) v ON v.moment_id = m.id
        WHERE
            m.id = #{momentId}
        ORDER BY
            m.create_time DESC
    </select>
    
    
    <update id="incrMomentLikeCount">
        UPDATE bbs_moment SET like_count = like_count + #{count} WHERE id = #{momentId}
    </update>
    
    
    <update id="decrMomentLikeCount">
        UPDATE bbs_moment SET like_count = like_count - #{count} WHERE id = #{momentId}
    </update>
    
    
    <update id="incrMomentCommentCount">
        UPDATE bbs_moment SET comment_count = comment_count + #{count} WHERE id = #{momentId}
    </update>
    
    
    <update id="decrMomentCommentCount">
        UPDATE bbs_moment SET comment_count = comment_count - #{count} WHERE id = #{momentId}
    </update>
    
    
    <update id="incrMomentVoteCount">
        UPDATE bbs_moment SET vote_count = vote_count + #{count} WHERE id = #{momentId}
    </update>
    
    
    <update id="decrMomentVoteCount">
        UPDATE bbs_moment SET vote_count = vote_count - #{count} WHERE id = #{momentId}
    </update>
    
</mapper>
