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

'use strict';

let ImDb = require('../oracle/db/im.db');
let async = require("async");
let ObjectUtil = require("../../util/object.util.js");
let vsprintf = require("sprintf-js").vsprintf;

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

let oracledbUtil = require('../../util/oracledb.util');

class SearchRepo {
    constructor() {
    }

    /**
     * 查询正常会话及议题已结束的会话(P2P, MUC)
     *
     * @param userId
     * @param handler
     */
    static findTopicEndedSessionIdList(userId, handler) {
        let sql = "SELECT S.\"ID\" as \"id\" " +
            "FROM sessions s, topics t, participants p " +
            "WHERE p.participant_id = :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,2,8) " +
            " UNION " +
            "SELECT S.\"ID\" as \"id\" " +
            "FROM SESSIONS S, PARTICIPANTS P " +
            "WHERE P.PARTICIPANT_ID = :PARTICIPANT_ID AND P.SESSION_ID = S.ID AND S.`TYPE` IN (2, 3, 4)";

        oracledbUtil.query({
            sql: sql,
            args: [userId, userId],
            handler: handler
        });
    }

    /**
     * 查询正常会话及议题已结束的会话(P2P, MUC)
     *
     * @param userId
     * @param handler
     */
    static findTopicActiveSessionIdList(userId, handler) {
        let sql = "SELECT S.\"ID\" as \"id\" " +
            "FROM SESSIONS S, TOPICS T, PARTICIPANTS P " +
            "WHERE P.PARTICIPANT_ID = :PARTICIPANT_ID AND P.SESSION_ID = S.ID AND S.ID = T.SESSION_ID AND T.END_MESSAGE_ID IS NULL  AND S.`TYPE` IN (1,2,8) " +
            " UNION " +
            "SELECT S.\"ID\" as \"id\" " +
            "FROM SESSIONS S, PARTICIPANTS P " +
            "WHERE P.PARTICIPANT_ID = :PARTICIPANT_ID AND P.SESSION_ID = S.ID  AND S.`TYPE` IN (2,3,4)  AND S.ID NOT IN(" +
            " SELECT DISTINCT P1.SESSION_ID FROM  PARTICIPANTS P1 ,TOPICS T WHERE P1.PARTICIPANT_ID = :PARTICIPANT_ID AND  T.SESSION_ID = P1.SESSION_ID  " +
            ") ";
        oracledbUtil.query({
            sql: sql,
            args: [userId, userId,userId],
            handler: handler
        });
    }

    /**
     * 全部搜索
     *
     * @param sessionIdList
     * @param keyword
     * @param userTable
     * @param handler
     */
    static searchAll(sessionIdList,userId, keyword, userTable, handler) {
        let data = {};

        async.waterfall([
            function (callback) {
                SearchRepo.searchUser(sessionIdList,userId, keyword, userTable, 0, 4, function (err, res) {
                    if (err) return handler(err, null);

                    let buffer = SearchRepo.userForge(res);
                    data.users = buffer;

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

                    data.sessions = SearchRepo.sessionForge(res, keyword);

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

                    data.messages = SearchRepo.messageForge(res);

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

    /**
     * 用户搜索
     *
     * @param sessionIdList
     * @param keyword
     * @param userTable
     * @param page
     * @param size
     * @param handler
     */
    static searchUser(sessionIdList,userId, keyword, userTable, page, size, handler) {
        let sql = "SELECT*FROM ( SELECT DISTINCT rownum r,U.\"NAME\" AS \"user_name\",S.\"ID\" AS \"session_id\",S.\"NAME\" AS \"session_name\",S.\"TYPE\" AS \"session_type\",S.\"BUSINESS_TYPE\" AS \"business_type\",U.\"ID\" AS \"user_id\",U.\"SEX\" AS \"sex\",U.\"BIRTHDATE\" AS \"birthdate\",U.\"AVATAR\" AS \"avatar\",U.\"IDCARD\" AS \"idcard\" %s " +
            " FROM  PARTICIPANTS P, " + userTable +
            " U,SESSIONS S WHERE S.ID IN ('"+sessionIdList+"') AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID AND P.PARTICIPANT_ID<>:PARTICIPANT_ID AND (U.NAME LIKE :NAME OR U.IDCARD LIKE :IDCARD) ";

        if (userTable === DB_TABLES.Doctors) {
            sql += " AND S.TYPE = 2 AND S.BUSINESS_TYPE = 1 ";
        }else{
            sql += " AND S.TYPE IN (1,2,8)  AND S.BUSINESS_TYPE = 2 ";
        }

        sql += " AND rownum<="+size+") WHERE r>="+page * size+" ";
        sql = vsprintf(sql, [userTable == DB_TABLES.Doctors ? ', HOSPITAL_NAME' : '']);

        keyword = '%' + keyword + '%';
        oracledbUtil.query({
            sql: sql,
            args: [userId, keyword,keyword],
            handler: handler
        });
    }

    /**
     * 会话搜索。搜索会话名称与会话成员的名称。若有一个符合，就选中。
     */
    static searchSessions(sessionIdList,userId, keyword, page, size, handler) {
          if (sessionIdList.length == 0) {
            return handler(null, []);
        }

        let sqlTemp = "SELECT rownum r,* FROM(" +
            "SELECT S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"CREATE_DATE\" AS \"create_date\",S.\"BUSINESS_TYPE\" AS \"business_type\",GROUP_CONCAT(U.NAME) AS \"participant_name\" " +
            "FROM SESSIONS S,  DOCTORS U ,PARTICIPANTS P " +
            "WHERE S.ID IN ('"+sessionIdList+"') AND S.TYPE IN (3,4) AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID AND P.PARTICIPANT_ID<>:PARTICIPANT_ID AND (U.NAME LIKE :NAME OR S.NAME LIKE :NAME) GROUP BY S.ID " +
            ") X ";

        let sql = "SELECT*FROM ( "+sqlTemp+" AND rownum<="+size+") WHERE r>="+page * size+" "

        keyword = '%' + keyword + '%';
        oracledbUtil.query({
            sql: sql,
            args: [userId, keyword,keyword],
            handler: handler
        });
    }

    /**
     * 消息搜索
     */
    static searchMessages(sessionIdList,userId, keyword, page, size, handler) {
        let sqlTemp = "SELECT rownum r, * FROM(" +
            "SELECT S.\"ID\" AS \"session_id\",S.\"NAME\" AS \"session_name\",S.\"TYPE\" AS \"SESSION_TYPE\",S.\"BUSINESS_TYPE\" AS \"session_type\",M.\"ID\" AS \"message_id\",M.\"SENDER_ID\" AS \"sender_id\",M.\"SENDER_NAME\" AS \"sender_name\",M.\"TIMESTAMP\" AS \"timestamp\",M.\"CONTENT\" AS \"content\" " +
            "FROM SESSIONS S, MUC_MESSAGES M " +
            "WHERE S.ID IN ('"+sessionIdList+"') AND S.ID = M.SESSION_ID AND S.`TYPE` = 1 AND M.CONTENT_TYPE = 1 AND M.CONTENT LIKE :CONTENT " +
            " UNION " +
            "SELECT S.\"ID\" AS \"session_id\",S.\"NAME\" AS \"session_name\",S.\"TYPE\" AS \"SESSION_TYPE\",S.\"BUSINESS_TYPE\" AS \"session_type\",M.\"ID\" AS \"message_id\",M.\"SENDER_ID\" AS \"sender_id\",M.\"SENDER_NAME\" AS \"sender_name\",M.\"TIMESTAMP\" AS \"timestamp\",M.\"CONTENT\" AS \"content\" " +
            "FROM SESSIONS S, P2P_MESSAGES M " +
            "WHERE S.ID IN ('"+sessionIdList+"') AND S.ID = M.SESSION_ID AND S.`TYPE` = 2 AND M.CONTENT_TYPE = 1 AND M.CONTENT LIKE :CONTENT " +
            " UNION " +
            "SELECT S.\"ID\" AS \"session_id\",S.\"NAME\" AS \"session_name\",S.\"TYPE\" AS \"SESSION_TYPE\",S.\"BUSINESS_TYPE\" AS \"session_type\",M.\"ID\" AS \"message_id\",M.\"SENDER_ID\" AS \"sender_id\",M.\"SENDER_NAME\" AS \"sender_name\",M.\"TIMESTAMP\" AS \"timestamp\",M.\"CONTENT\" AS \"content\" " +
            "FROM SESSIONS S, GROUP_MESSAGES M " +
            "WHERE S.ID IN ('"+sessionIdList+"') AND S.ID = M.SESSION_ID AND S.`TYPE` IN (3,5) AND M.CONTENT_TYPE = 1 AND M.CONTENT LIKE :CONTENT ) X " +
            "ORDER BY X.SESSION_ID, X.MESSAGE_ID ";
        let sql = "SELECT*FROM ( "+sqlTemp+" AND rownum<="+ size+") WHERE r>="+page * size+" "

        keyword = '%' + keyword + '%';
        oracledbUtil.query({
            sql: sql,
            args: [keyword, keyword, keyword],
            handler: handler
        });
    }

    static userForge(res) {
        res.forEach(function (user) {
            if (!user.avatar) user.avatar = "";
                user.birthdate = user.birthdate ? user.birthdate.getTime():"";
        });

        return res;
    }

    static sessionForge(res, keyword) {
        let result = [];

        let lastSessionId = null;
        let tempSession = null;
        res.forEach(function (session) {
            if (session.id !== lastSessionId) {
                lastSessionId = session.id;
                tempSession = {
                    id: session.id,
                    name: session.name,
                    type: session.type,
                    business_type: session.business_type,
                    /*create_date: ObjectUtil.timestampToLong(session.create_date),*/
                    members: []
                };

                result.push(tempSession);
            }

            if (session.participant_name.indexOf(keyword) >= 0) tempSession.members.push({name: session.participant_name});
        });

        return result;
    }

    static messageForge(res) {
        let result = [];

        let lastSessionId = null;
        let session = null;
        res.forEach(function (message) {
            if (message.session_id !== lastSessionId) {
                lastSessionId = message.session_id;
                session = {
                    session_id: message.session_id,
                    session_name: message.session_name,
                    session_type: message.session_type,
                    /*session_business_type: message.session_business_type,*/
                    messages: []
                };

                result.push(session);
            }

            session.messages.push({
                id: message.message_id,
                sender_id: message.sender_id,
                sender_name: message.sender_name,
                timestamp: ObjectUtil.timestampToLong(message.timestamp),
                content: message.content
            });
        });

        return result;
    }
}

module.exports = SearchRepo;