im/src/doctor/repository/private.msg.repo.js

"use strict";

var imRepo = require("./database/im.db.js");

/**
 * 保存消息。
 *
 * @param to
 * @param from
 * @param type
 * @param content
 * @param handler
 */
exports.save = function (to, from, type, content, handler) {
    imRepo.execQuery({
        "sql": "INSERT INTO msg_p2p (to_uid,from_uid,type,content) VALUES (?,?,?,?)",
        "args": [to, from, type, content],
        "handler": handler
    });
};

exports.findOneMessage = function (messageId, handler) {
  imRepo.execQuery({
      "sql": "SELECT msg_id, to_uid, from_uid, type, content, timestamp from msg_p2p where msg_id = ?",
      "args": [messageId],
      "handler": handler
  });
};


exports.findOnePatientMessage = function (messageId,handler) {
    imRepo.execQuery({
        "sql": "SELECT m.*,d.name,d.photo FROM msg_p2p m, wlyy.wlyy_doctor d, wlyy.wlyy_patient p WHERE m.from_uid = d. CODE AND m.to_uid = p. CODE AND m.msg_id =?",
        "args": [messageId],
        "handler": handler
    });
};

/**
 * 查找所有消息。
 *
 * @param to
 * @param from
 * @param contentType
 * @param start
 * @param end
 * @param count
 * @param closedInterval
 * @param handler
 */
exports.findAllMessages = function (to, from, contentType, start, end, count, closedInterval, handler) {
    var sql = "SELECT msg_id, to_uid, from_uid, type, content, timestamp from msg_p2p " +
        "WHERE ((to_uid=? AND from_uid=?) OR (to_uid=? AND from_uid=?)) " +
        "   AND type in (" + contentType + ") AND msg_id between ? and ? ORDER BY msg_id DESC LIMIT ?";

    imRepo.execQuery({
        "sql": sql,
        "args": [to, from, from, to, closedInterval ? end : end + 1, closedInterval ? start : start - 1, count],
        "handler": handler
    });
};

/**
 * 查找用户聊天过的医生列表。
 *
 * @param userId 指定的用户
 * @param handler
 */
exports.findAllP2PWithDoctor = function (userId, handler) {
    //var sql = "SELECT DISTINCT d.code, d.name, d.sex, d.photo, ms3.last_content_type, ms3.last_content, ms3.timestamp, ms3.new_msg_count " +
    //    "FROM (SELECT DISTINCT CASE WHEN ms1.timestamp > ms2.timestamp THEN ms1.id ELSE ms2.id END id " +
    //    "      FROM msg_statistic ms1, msg_statistic ms2 " +
    //    "      WHERE ms1.from_gid IS NULL AND ms2.from_gid IS NULL " +
    //    "            AND ms1.uid = ms2.peer_uid AND ms1.peer_uid = ms2.uid) x, msg_statistic ms3, wlyy.wlyy_doctor d " +
    //    "WHERE x.id = ms3.id AND ms3.last_content_type in (1,2,3,5,6) AND " +
    //    "(ms3.uid = ? AND ms3.peer_uid = d.code) GROUP BY d.code, d.name ORDER BY ms3.timestamp DESC";
    var sql ="SELECT d. CODE as code, d. NAME as name,  d.sex, d.photo, s.last_content_type, s.last_content, s. TIMESTAMP as timestamp, s.new_msg_count " +
        "FROM msg_p2p p, wlyy.wlyy_doctor d, msg_statistic s " +
        "WHERE (( p.from_uid = d. CODE AND p.to_uid = ? ) OR ( p.to_uid = d. CODE AND p.from_uid = ? )) " +
        "AND s.from_gid IS NULL AND s.uid = ? " +
        "AND s.peer_uid = d. CODE " +
        "GROUP BY d. NAME, d. CODE, d.hospital_name, d.job_name, d.sex, d.photo ORDER BY p.msg_id DESC"

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

/**
 * 查找用户聊天过的患者列表。
 *
 * @param userId
 * @param handler
 */
exports.findAllP2PWithPatient = function (userId, handler) {
    //var sql = "SELECT p.code, p.name, p.birthday, p.sex, p.photo, ms.last_content_type, ms.last_content, ms.timestamp, ms.new_msg_count " +
    //    "FROM msg_statistic ms, wlyy.wlyy_patient p " +
    //    "WHERE ms.msg_type = 1 AND ms.last_content_type in (1,2,3,5,6) " +
    //    "AND ((ms.from_uid = ? AND ms.uid = p.code) OR (ms.uid = ? AND ms.from_uid = p.code)) ORDER BY ms.timestamp";
    var sql="SELECT p1. CODE as code , p1. NAME as name, p1.birthday, p1.sex, p1.photo, w.last_content, w.last_content_type, w. TIMESTAMP as timestamp, w.new_msg_count " +
             "FROM msg_p2p p, wlyy.wlyy_patient p1, msg_statistic w " +
             "WHERE (( p.to_uid = p1.`code` AND p.from_uid = ? ) OR ( p.from_uid = p1.`code` AND p.to_uid = ? )) " +
             "AND w.last_content_type IN (0,1, 2, 3, 4, 5, 6) AND w.uid = ? AND w.peer_uid = p1.`code` AND w.from_gid IS NULL " +
             "GROUP BY p1. CODE, p1. NAME, p1.birthday, p1.sex, p1.photo " +
             "union all " +
             "SELECT p1. CODE AS code, p1. NAME AS name, p1.birthday, p1.sex, p1.photo, w.last_content, t.type, w. TIMESTAMP AS timestamp, w.new_msg_count " +
             "FROM msg_p2p p, wlyy.wlyy_patient p1, msg_statistic w, wlyy.wlyy_consult_team t " +
             "WHERE (( p.to_uid = p1.`code` AND p.from_uid = ? ) OR ( p.from_uid = p1.`code` AND p.to_uid = ? )) " +
             "AND w.last_content_type = 7 AND t.type = 6 " +
             "AND (( t.patient = p.from_uid AND t.doctor = p.to_uid ) OR ( t.patient = p.to_uid AND t.doctor = p.from_uid )) " +
             "AND w.uid = ? AND w.peer_uid = p1.`code` AND w.from_gid IS NULL GROUP BY p1. CODE, p1. NAME, p1.birthday, p1.sex, p1.photo;";
    imRepo.execQuery({
        "sql": sql,
        "args": [userId, userId,userId,userId, userId,userId],
        "handler": handler
    });
};

/**
 * 查找未读消息。
 *
 * @param from
 * @param to
 * @param start
 * @param count
 * @param handler
 */
exports.findUnread = function(from, to, start, count, handler) {
    var sql = "SELECT msg_id, to_uid, from_uid, type, content, timestamp from msg_p2p " +
        "WHERE from_uid = ? AND to_uid = ? AND msg_id < ? ORDER BY timestamp DESC LIMIT ?";

    imRepo.execQuery({
        "sql": sql,
        "args": [from, to, start, count],
        "handler": handler
    });
};

exports.isCurrentSessionFinished = function (doctorId, patientId, handler) {
    var sql = "SELECT c.consult consult_id, case when c.end_msg_id is not null then 1 else 0 end finished " +
        "FROM wlyy.wlyy_consult_team c where c.doctor=? and c.patient = ? ORDER BY id DESC LIMIT 1";

    imRepo.execQuery({
        "sql": sql,
        "args": [doctorId, patientId],
        "handler": handler
    });
};