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

/**
 * P2P消息库。
 */
"use strict";

var ImDb = require("../mysql/db/im.db.js");

class PrivateMsgRepo {
    constructor() {
    }

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

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


    static findOnePatientMessage(messageId, handler) {
        ImDb.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
     */
    static findAllMessages(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 ?";

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

    /**
     * 查找用户聊天过的医生列表。
     *
     * @param userId 指定的用户
     * @param handler
     */
    static findAllP2PWithDoctor(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"

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

    /**
     * 查找用户聊天过的患者列表。
     *
     * @param userId
     * @param handler
     */
    static findAllP2PWithPatient(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 (1, 2, 3, 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;";
        ImDb.execQuery({
            "sql": sql,
            "args": [userId, userId, userId, userId, userId, userId],
            "handler": handler
        });
    };

    /**
     * 查找未读消息。
     *
     * @param from
     * @param to
     * @param start
     * @param count
     * @param handler
     */
    static findUnread  (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 ?";

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

    static isCurrentSessionFinished(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";

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

module.exports = PrivateMsgRepo;