search.repo.js 6.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200
  1. 'use strict';
  2. let ImDb = require('../mysql/db/im.db');
  3. let async = require("async");
  4. let ObjectUtil = require("../../util/object.util.js");
  5. let vsprintf = require("sprintf-js").vsprintf;
  6. const DB_TABLES = require('../../include/commons').DB_TABLES;
  7. class SearchRepo {
  8. constructor() {
  9. }
  10. /**
  11. * 查询正常会话及议题已结束的会话(P2P, MUC)
  12. *
  13. * @param userId
  14. * @param handler
  15. */
  16. static findTopicEndedSessionIdList(userId, handler) {
  17. let sql = "SELECT s.id " +
  18. "FROM sessions s, topics t, participants p " +
  19. "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) " +
  20. " UNION " +
  21. "SELECT s.id " +
  22. "FROM sessions s, participants p " +
  23. "WHERE p.participant_id = ? AND p.session_id = s.id AND s.`type` IN (2, 3)";
  24. ImDb.execQuery({
  25. sql: sql,
  26. args: [userId, userId],
  27. handler: handler
  28. });
  29. }
  30. /**
  31. * 全部搜索
  32. *
  33. * @param sessionIdList
  34. * @param keyword
  35. * @param userTable
  36. * @param handler
  37. */
  38. static searchAll(sessionIdList, keyword, userTable, handler) {
  39. let data = {};
  40. async.waterfall([
  41. function (callback) {
  42. SearchRepo.searchUser(sessionIdList, keyword, userTable, 0, 3, function (err, res) {
  43. if (err) return handler(err, null);
  44. let buffer = SearchRepo.userForge(res);
  45. userTable == DB_TABLES.Doctors ? data.doctors = buffer : data.patients = buffer;
  46. callback(null);
  47. });
  48. },
  49. function (callback) {
  50. SearchRepo.searchSessions(sessionIdList, keyword, userTable, 0, 3, function (err, res) {
  51. if (err) return handler(err, null);
  52. data.sessions = SearchRepo.sessionForge(res);
  53. callback(null);
  54. })
  55. },
  56. function (callback) {
  57. SearchRepo.searchMessages(sessionIdList, keyword, 0, 3, function (err, res) {
  58. if (err) return handler(err, null);
  59. data.messages = SearchRepo.messageForge(res);
  60. handler(null, data);
  61. })
  62. }
  63. ]);
  64. }
  65. /**
  66. * 用户搜索
  67. *
  68. * @param sessionIdList
  69. * @param keyword
  70. * @param userTable
  71. * @param page
  72. * @param size
  73. * @param handler
  74. */
  75. static searchUser(sessionIdList, keyword, userTable, page, size, handler) {
  76. let sql = "SELECT DISTINCT u.id, u.name, u.sex, u.birthdate, u.avatar %s FROM sessions s, participants p, " + userTable +
  77. " u WHERE s.id in (?) AND s.id = p.session_id AND p.participant_id = u.id AND u.name like ? limit ?, ?";
  78. sql = vsprintf(sql, [userTable == DB_TABLES.Doctors ? ', hospital_name' : '']);
  79. keyword = '%' + keyword + '%';
  80. ImDb.execQuery({
  81. sql: sql,
  82. args: [sessionIdList, keyword, page * size, size],
  83. handler: handler
  84. });
  85. }
  86. /**
  87. * 会话搜索。搜索会话名称与会话成员的名称。若有一个符合,就选中。
  88. */
  89. static searchSessions(sessionIdList, keyword, userTable, page, size, handler) {
  90. if (sessionIdList.length == 0) {
  91. return handler(null, []);
  92. }
  93. let sql = "SELECT s.id, s.name, s.type, s.create_date, s.business_type, u.name participant_name " +
  94. "FROM sessions s, participants p, " + userTable + " u " +
  95. "WHERE s.id IN (?) AND s.id = p.session_id AND p.participant_id = u.id AND (s.name LIKE ? or u.name like ?) " +
  96. "GROUP by s.id LIMIT ?, ?";
  97. keyword = '%' + keyword + '%';
  98. ImDb.execQuery({
  99. sql: sql,
  100. args: [sessionIdList, keyword, keyword, page * size, size],
  101. handler: handler
  102. });
  103. }
  104. /**
  105. * 消息搜索
  106. */
  107. static searchMessages(sessionIdList, keyword, page, size, handler) {
  108. let sql = "SELECT * FROM(" +
  109. "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 " +
  110. "FROM sessions s, muc_messages m " +
  111. "WHERE s.id IN (?) AND s.id = m.session_id AND s.`type` = 1 AND m.content_type = 1 AND m.content LIKE ? " +
  112. " UNION " +
  113. "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 " +
  114. "FROM sessions s, p2p_messages m " +
  115. "WHERE s.id IN (?) AND s.id = m.session_id AND s.`type` = 2 AND m.content_type = 1 AND m.content LIKE ? " +
  116. " UNION " +
  117. "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 " +
  118. "FROM sessions s, group_messages m " +
  119. "WHERE s.id IN (?) AND s.id = m.session_id AND s.`type` = 3 AND m.content_type = 1 AND m.content LIKE ? ) X " +
  120. "ORDER BY X.session_id, X.message_id LIMIT ?, ?";
  121. keyword = '%' + keyword + '%';
  122. ImDb.execQuery({
  123. sql: sql,
  124. args: [sessionIdList, keyword, sessionIdList, keyword, sessionIdList, keyword, page * size, size],
  125. handler: handler
  126. });
  127. }
  128. static userForge(res) {
  129. res.forEach(function (user) {
  130. if (!user.avatar) user.avatar = "";
  131. user.birthdate = user.birthdate ? ObjectUtil.timestampToLong(user.birthdate) : "";
  132. });
  133. return res;
  134. }
  135. static sessionForge(res) {
  136. res.forEach(function (session) {
  137. session.create_date = ObjectUtil.timestampToLong(session.timestamp);
  138. });
  139. return res;
  140. }
  141. static messageForge(res) {
  142. let result = [];
  143. let lastSessionId = null;
  144. let session = null;
  145. res.forEach(function (message) {
  146. if (message.session_id !== lastSessionId) {
  147. lastSessionId = message.session_id;
  148. session = {
  149. session_id: message.session_id,
  150. session_name: message.session_name,
  151. session_type: message.session_type,
  152. session_business_type: message.session_business_type,
  153. messages: []
  154. };
  155. result.push(session);
  156. }
  157. session.messages.push({
  158. id: message.message_id,
  159. sender_id: message.sender_id,
  160. sender_name: message.sender_name,
  161. timestamp: ObjectUtil.timestampToLong(message.timestamp),
  162. content: message.content
  163. });
  164. });
  165. return result;
  166. }
  167. }
  168. module.exports = SearchRepo;