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

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

var ImDb = require("../oracle/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;