im.doctor/src/server/repository/oracle/participant.repo.js

/**
 * 搜索功能。
 */
"use strict";

let ImDb = require('../oracle/db/im.db');
let DbUtil = require('../../util/db.util');
let log = require('../../util/log.js');

const DB_TABLES = require('../../include/commons').DB_TABLES;
const SESSION_USER_STATUS = require('../../include/commons').SESSION_USER_STATUS;
const SESSION_TYPES = require('../../include/commons').SESSION_TYPES;
const SESSION_BUSINESS_TYPE = require('../../include/commons').SESSION_BUSINESS_TYPE;

class ParticipantRepo {
    constructor() {
    }

    /**
     * 获取会话的成员列表
     *
     * @param sessionId
     * @param handler
     */
    static findAll(sessionId, handler) {
        let sql = "SELECT U.ID, U.NAME, U.SEX, U.BIRTHDATE, U.AVATAR,U.HOSPITAL_NAME, P.PARTICIPANT_ROLE ROLE, FALSE IS_PATIENT,P.LAST_FETCH_TIME,U.LEVEL,U.MOBILE FROM SESSIONS S, PARTICIPANTS P, DOCTORS U " +
            "WHERE S.ID = ? AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID UNION " +
            "SELECT U.ID, U.NAME, U.SEX, U.BIRTHDATE, U.AVATAR,U.HOSPITAL_NAME, P.PARTICIPANT_ROLE ROLE, TRUE IS_PATIENT,P.LAST_FETCH_TIME,0 AS LEVEL,U.MOBILE FROM SESSIONS S, PARTICIPANTS P, PATIENTS U " +
            "WHERE S.ID = ? AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID";

        ImDb.execQuery({
            "sql": sql,
            "args": [sessionId, sessionId],
            "handler": function(err,res){
                if(res&&res.length>0){
                    for(var j in res){
                        if(res[j].last_fetch_time){
                            res[j].last_fetch_time = res[j].last_fetch_time.getTime();
                        }
                    }
                }
                handler(err,res);
            }
        });
    }

    /**
     * 获取会话的成员ID列表
     *
     * @param sessionId
     * @param handler
     */
    static findIds(sessionId, handler) {
        let sql =
            "SELECT U.ID, U.NAME, FALSE IS_PATIENT, P.PARTICIPANT_ROLE,U.AVATAR FROM SESSIONS S, PARTICIPANTS P, DOCTORS U " +
            "WHERE S.ID = ? AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID " +
            "UNION " +
            "SELECT U.ID, U.NAME, TRUE IS_PATIENT, P.PARTICIPANT_ROLE,U.AVATAR FROM SESSIONS S, PARTICIPANTS P, PATIENTS U " +
            "WHERE S.ID = ? AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID ";

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

    /**
     * 根据会话id查找会话成员
     * @param sessionId
     * @param handler
     */
    static findParricipantBySessionId(sessionId,handler){
        let sql = "SELECT PARTICIPANT_ID,PARTICIPANT_ROLE FROM PARTICIPANTS P WHERE P.SESSION_ID = ?";
        ImDb.execQuery({
            "sql": sql,
            "args": [sessionId],
            "handler": handler
        });
    }

    /**
     * 获取会话医生的id
     * @param sessionId
     * @param handler
     */
    static findDoctorIds(sessionId,handler){
        let sql =
            "SELECT U.ID, U.NAME, FALSE IS_PATIENT, P.PARTICIPANT_ROLE,U.AVATAR FROM SESSIONS S, PARTICIPANTS P, DOCTORS U " +
            "WHERE S.ID = ? AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID ";

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

    /**
     * 获取会话的居民的家庭成员
     * @param sessionId
     * @param handler
     */
    static findFamilyIds(sessionId, handler){
        let sql =
            "SELECT U.ID, U.NAME, TRUE IS_PATIENT, P.PARTICIPANT_ROLE,U.AVATAR,PS.NAME PNAME,PS.ID PID " +
            "FROM SESSIONS S, PARTICIPANTS P, PATIENTS U ,WLYY.WLYY_PATIENT_FAMILY_MEMBER M,PATIENTS PS " +
            "WHERE S.ID = ? AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = M.PATIENT AND M.FAMILY_MEMBER = U.ID AND M.IS_AUTHORIZE = 1 AND P.PARTICIPANT_ID = PS.ID ";
        //新增发送代理人

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

    /**
     * 获取会话的成员头像列表
     *
     * @param sessionId
     * @param handler
     */
    static findAllAvatars(sessionId, handler) {
        let sql = "SELECT U.ID, U.AVATAR,'0' AS ISPATIENT,U.NAME AS NAME FROM SESSIONS S, PARTICIPANTS P, DOCTORS U " +
            "WHERE S.ID = ? AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID UNION " +
            "SELECT U.ID, U.AVATAR,'1' AS ISPATIENT,U.NAME AS NAME FROM SESSIONS S, PARTICIPANTS P, PATIENTS U " +
            "WHERE S.ID = ? AND S.ID = P.SESSION_ID AND P.PARTICIPANT_ID = U.ID";

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

    /**
     * 获取会话的成员列表
     *
     * @param sessionId
     * @param participantId
     * @param role
     * @param handler
     */
    static updateParticipant(sessionId, participantId, role, handler) {
        let sql = "UPDATE PARTICIPANTS SET PARTICIPANT_ROLE = ? WHERE SESSION_ID = ? AND PARTICIPANT_ID = ?";
        ImDb.execQuery({
            "sql": sql,
            "args": [role, sessionId, participantId],
            "handler": handler
        });
    }

    /**
     * 获取P2P成员所在会话。将成员的ID排序后取哈希值即可。
     *
     * @param userId
     * @param anotherUserId
     * @param handler
     */
    static findSessionIdByParticipantIds(userId, anotherUserId, handler) {
        let sessionId = DbUtil.stringArrayHash([userId, anotherUserId]);
        handler(null, sessionId);
    }

    static getBusinessType(users, handler) {
        let sql = "SELECT COUNT(1) AS COUNT FROM PATIENTS P WHERE P.ID IN (?)";
        ImDb.execQuery({
            "sql": sql,
            "args": [users],
            "handler": function (err, res) {
                if (err) {
                    console.log("err businessType  : " + err);
                } else {
                    if (res[0].count > 0) {
                        handler(err, SESSION_BUSINESS_TYPE.PATIENT);
                    } else {
                        handler(err, SESSION_BUSINESS_TYPE.DOCTOR);
                    }
                }
            }
        });


    }

    static findNameById(userId, handler) {
        let sql = "SELECT P.NAME,P.SEX,P.BIRTHDATE FROM PATIENTS P WHERE P.ID =? UNION SELECT D.NAME,D.SEX,D.BIRTHDATE FROM DOCTORS D WHERE D.ID =?";
        ImDb.execQuery({
            "sql": sql,
            "args": [userId, userId],
            "handler": function (err, res) {
                if (err) {
                    console.log("err businessType  : " + err);
                } else {
                    handler(null, res);
                }
            }
        });
    }

    static findMucSessionIdByUser(users, handler) {
        //先匹配出在线用户
        let userTemp = [];
        users.forEach(function (user) {
            if (user == SESSION_USER_STATUS.ONLINE) {
                userTemp.push(user);
            }
        });

        let sql = "SELECT DISTINCT S.* FROM " + DB_TABLES.Participants + " P1," + DB_TABLES.Participants + " P2," +
            DB_TABLES.Sessions + " S WHERE P1.SESSION_ID =  S.ID  AND P2.SESSION_ID = S.ID  AND S.TYPE =? " +
            "AND ((P1.PARTICIPANT_ID =? AND P2.PARTICIPANT_ID = ?) OR (P1.PARTICIPANT_ID =? AND P2.PARTICIPANT_ID = ?))";

        ImDb.execQuery({
            "sql": sql,
            "args": [SESSION_TYPES.MUC, userTemp[0], userTemp[1], userTemp[1], userTemp[0]],
            "handler": handler
        });
    }

    /**
     * 更新最后消息获取时间。
     *
     * @param lastMessageTime
     * @param sessionId
     * @param participantId
     * @param handler
     */
    static updateLastFetchTime(lastMessageTime, sessionId, participantId, handler) {
        let sql = "UPDATE " + DB_TABLES.Participants + " SET LAST_FETCH_TIME=? WHERE SESSION_ID = ? AND PARTICIPANT_ID =?";
        ImDb.execQuery({
            "sql": sql,
            "args": [lastMessageTime, sessionId, participantId],
            "handler": handler
        });
    }

    /**
     * 用户是否在指定Session中
     *
     * @param sessionId
     * @param userId
     * @param handler
     */
    static existsParticipant(sessionId, userId, handler) {
        let sql = "SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END EXIST FROM PARTICIPANTS W WHERE W.SESSION_ID =? AND W.PARTICIPANT_ID = ? ";
        log.info("用户是否在指定Session中:sql:" + sql);
        log.info("用户是否在指定Session中:args:" + [sessionId, userId]);
        ImDb.execQuery({
            "sql": sql,
            "args": [sessionId, userId],
            "handler": handler
        });
    }

    /**
     * mysql成员创建
     *
     * @param sessionId
     * @param users JSON
     * @param handler
     */
    static saveParticipantsToMysql(sessionId, users, handler) {
        let sql = "INSERT INTO " + DB_TABLES.Participants + " (SESSION_ID,PARTICIPANT_ID,PARTICIPANT_ROLE,LAST_FETCH_TIME) VALUES ";
        let args = [];
        let nowDate = new Date();
        log.info("saveParticipantsToMysql:[sql] = " + sql);
        log.info("saveParticipantsToMysql:[users.length] = " + users.length);
        for (let j in users) {
            let tokens = users[j].split(":");
            sql += "(?,?,?,?)";
            args.push(sessionId);
            args.push(tokens[0]);
            args.push(tokens.length > 1 ? tokens[1] : '0');
            args.push(nowDate);
            if (j != users.length - 1) sql += ", ";
            log.info("saveParticipantsToMysql:[args] = " + args);
        }
        sql += " ON DUPLICATE KEY UPDATE PARTICIPANT_ROLE = VALUES(PARTICIPANT_ROLE)";
        ImDb.execQuery({
            "sql": sql,
            "args": args,
            "handler": handler
        });
    }

    static deleteUserFromMysql(sessionId, userId, handler) {
        let sql = "DELETE FROM " + DB_TABLES.Participants + " WHERE PARTICIPANT_ID=? AND SESSION_ID=? ";
        ImDb.execQuery({
            "sql": sql,
            "args": [userId, sessionId],
            "handler": handler || function (err, res) {
                log.info("deleteUserFromMysql");
            }
        });
    }

    static deleteAllUser(sessionId, handler) {
        let sql = "DELETE FROM " + DB_TABLES.Participants + " WHERE SESSION_ID=? ";
        ImDb.execQuery({
            "sql": sql,
            "args": [sessionId],
            "handler": handler || function (err, res) {
                log.info("deleteUserFromMysql");
            }
        });
    }

    static findLastFetchTime(sessionId,userId,handler){
        let sql = "SELECT LAST_FETCH_TIME FROM PARTICIPANTS P WHERE P.SESSION_ID = ? AND P.PARTICIPANT_ID=?";
        ImDb.execQuery({
            "sql": sql,
            "args": [sessionId,userId],
            "handler": handler
        });
    }
}

module.exports = ParticipantRepo;