'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;