<?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.huiju.estateagents.property.mapper.TpMessageBoardMapper">

    <sql id="compactQuery">
        SELECT
            t.msg_id,
            NULL AS repy_id,
            t.content,
            t.content_type,
            t.org_id,
            t.create_date,
            t.person_id,
            NULL AS user_id
        FROM
            tp_message_board t
        WHERE
            t.org_id = #{orgId}
        UNION
        SELECT
            s.msg_id,
            s.reply_id,
            s.content,
            s.content_type,
            s.org_id,
            s.create_date,
            s.person_id,
            s.user_id
        FROM
            tp_message_reply s
        WHERE
            s.org_id = #{orgId}
    </sql>

    <select id="getPagedBy" resultType="com.huiju.estateagents.property.entity.TpMessageBoard">
        SELECT
            t.msg_id,
            t.org_id,
            s.reply_id,
            IFNULL( s.content, t.content ) AS content,
            IFNULL( s.content_type, t.content_type ) AS content_type,
            IFNULL( s.create_date, t.create_date ) AS create_date,
            t.person_id,
            s.user_id,
            m.nickname,
            m.phone,
            m.avatarurl AS avatar
        FROM
            tp_message_board t
            INNER JOIN ta_person m ON m.person_id = t.person_id
            LEFT JOIN (
                SELECT
                    *
                FROM
                    tp_message_reply a
                WHERE
                    a.org_id = #{params.orgId}
                    AND a.user_id IS NULL
                    HAVING 1
                    ORDER BY a.create_date DESC
                ) s ON s.msg_id = t.msg_id
        WHERE
            t.org_id = #{params.orgId}
            <if test="null != params.name and '' != params.name">
                AND m.nickname like CONCAT('%', #{params.name}, '%')
            </if>
            <if test="null != params.phone and '' != params.phone">
                AND m.phone like CONCAT('%', #{params.phone}, '%')
            </if>
            <if test="null != params.startDate and '' != params.startDate">
                AND DATE_FORMAT(IFNULL( s.create_date, t.create_date ), '%Y-%m-%d') &gt;= #{params.startDate}
            </if>
            <if test="null != params.endDate and '' != params.endDate">
                AND DATE_FORMAT(IFNULL( s.create_date, t.create_date ), '%Y-%m-%d') &lt;= #{params.endDate}
            </if>
        GROUP BY
            s.msg_id
        ORDER BY
            IFNULL( s.create_date, t.create_date ) DESC
    </select>
    <select id="getByMsgId" resultType="com.huiju.estateagents.property.entity.TpMessageBoard">
        SELECT
            a.*,
            b.nickname,
            b.phone,
            b.avatarurl AS avatar
        FROM
            (<include refid="compactQuery" />) a
            INNER JOIN ta_person b
        WHERE
            b.person_id = a.person_id
            AND a.msg_id = #{id}
        ORDER BY
            a.create_date DESC
    </select>
    <select id="getByPersonId" resultType="com.huiju.estateagents.property.entity.TpMessageBoard">
        SELECT
            t.*,
            s.nickname,
            s.phone,
            s.avatarurl as avatar
        FROM
            tp_message_board t
            LEFT JOIN ta_person s ON t.org_id = s.org_id
            AND t.person_id = s.person_id
        WHERE
            t.org_id = #{orgId}
            AND t.person_id = #{personId}
    </select>
    <select id="getWxList" resultType="com.huiju.estateagents.property.entity.TpMessageBoard">
        SELECT
            a.*,
            b.nickname,
            b.phone,
            b.avatarurl AS avatar
        FROM
            (<include refid="compactQuery" />) a
            INNER JOIN ta_person b ON b.person_id = a.person_id
        WHERE
            b.person_id = #{personId}
        ORDER BY
            a.create_date DESC
    </select>
</mapper>
