im.doctor/src/server/repository/mysql/search.repo.js

'use strict';

let ImDb = require('../mysql/db/im.db');
let async = require("async");
var ObjectUtil = require("../../util/object.util.js");

const DB_TABLES = require('../../include/commons').DB_TABLES;

class SearchRepo {
    constructor() {
    }

    /**
     * 查询正常会话及议题已结束的会话(P2P, MUC)
     *
     * @param userId
     * @param handler
     */
    static findTopicEndedSessionIdList(userId, handler) {
        let sql = "SELECT s.id " +
            "FROM sessions s, topics t, participants p " +
            "WHERE p.participant_id = ? AND p.session_id = s.id AND s.id = t.session_id AND t.end_message_id IS NOT NULL AND s.`type` IN (1) " +
            " UNION " +
            "SELECT s.id " +
            "FROM sessions s, participants p " +
            "WHERE p.participant_id = ? AND p.session_id = s.id AND s.`type` IN (2, 3)";

        ImDb.execQuery({
            sql: sql,
            args: [userId, userId],
            handler: handler
        });

    }

    /**
     * 全部搜索
     *
     * @param sessionIdList
     * @param keyword
     * @param userTable
     * @param handler
     */
    static searchAll(sessionIdList, keyword, userTable, handler) {
        async.waterfall([
            function (callback) {
                SearchRepo.searchUser(sessionIdList, keyword, userTable, 0, 3, function (err, res) {
                    if (err) return handler(err, null);

                    let data = {};
                    if (userTable == DB_TABLES.Doctors) {
                        data.doctors = res;
                    } else {
                        data.patients = res;
                    }

                    callback(null, data);
                });
            },
            function (data, callback) {
                SearchRepo.searchSessions(sessionIdList, keyword, 0, 3, function (err, res) {
                    if (err) return handler(err, null);

                    data.sessions = res;

                    callback(null, data);
                })
            },
            function (data, callback) {
                SearchRepo.searchMessages(sessionIdList, keyword, 0, 3, function (err, res) {
                    if (err) return handler(err, null);

                    res.forEach(function (message) {
                        message.timestamp = ObjectUtil.timestampToLong(message.timestamp);
                    });

                    data.messages = res;

                    handler(null, data);
                })
            }
        ]);
    }

    /**
     * 用户搜索
     *
     * @param sessionIdList
     * @param keyword
     * @param userTable
     * @param page
     * @param size
     * @param handler
     */
    static searchUser(sessionIdList, keyword, userTable, page, size, handler) {
        let sql = "SELECT u.id, u.name, u.sex, u.avatar FROM sessions s, participants p, " + userTable +
            " u WHERE s.id in (?) AND s.id = p.session_id AND p.participant_id = u.id AND u.name like ? limit ?, ?";

        keyword = '%' + keyword + '%';
        ImDb.execQuery({
            sql: sql,
            args: [sessionIdList, keyword, page * size, size],
            handler: handler
        });
    }

    /**
     * 会话搜索
     */
    static searchSessions(sessionIdList, keyword, page, size, handler) {
        if(sessionIdList.length == 0){
            return handler(null, []);
        }

        let sql = "SELECT s.id, s.name, s.type, s.create_date, s.business_type FROM sessions s WHERE s.id in (?) AND s.name LIKE ? LIMIT ?, ? ";

        keyword = '%' + keyword + '%';
        ImDb.execQuery({
            sql: sql,
            args: [sessionIdList, keyword, page * size, size],
            handler: handler
        });
    }

    /**
     * 消息搜索
     */
    static searchMessages(sessionIdList, keyword, page, size, handler) {
        let sql = "SELECT * FROM(" +
            "SELECT s.id session_id, s.name session_name, s.type session_type, s.business_type session_business_type, m.id message_id, m.sender_id, m.sender_name, m.timestamp, m.content " +
            "FROM sessions s, muc_messages m " +
            "WHERE s.id IN (?) AND s.id = m.session_id AND s.`type` = 1 AND m.content_type = 1 AND m.content LIKE ? GROUP BY s.id" +
            " UNION " +
            "SELECT s.id session_id, s.name session_name, s.type session_type, s.business_type session_business_type, m.id message_id, m.sender_id, m.sender_name, m.timestamp, m.content " +
            "FROM sessions s, p2p_messages m " +
            "WHERE s.id IN (?) AND s.id = m.session_id AND s.`type` = 2 AND m.content_type = 1 AND m.content LIKE ? GROUP BY s.id" +
            " UNION " +
            "SELECT s.id session_id, s.name session_name, s.type session_type, s.business_type session_business_type, m.id message_id, m.sender_id, m.sender_name, m.timestamp, m.content " +
            "FROM sessions s, group_messages m " +
            "WHERE s.id IN (?) AND s.id = m.session_id AND s.`type` = 3 AND m.content_type = 1 AND m.content LIKE ? GROUP BY s.id) X " +
            "ORDER BY X.timestamp";

        keyword = '%' + keyword + '%';
        ImDb.execQuery({
            sql: sql,
            args: [sessionIdList, keyword, sessionIdList, keyword, sessionIdList, keyword, page * size, size],
            handler: handler
        });
    }
}

module.exports = SearchRepo;