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

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

let ImDb = require('../mysql/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 as \"id\", u.name as \"name\", u.sex as \"sex\", u.birthdate as \"birthdate\", u.avatar as \"avatar\",u.hospital_name as \"hospital_name\", p.participant_role as \"role\", false as \"is_patient\",p.last_fetch_time as \"last_fetch_time\",u.level as \"level\",u.mobile as\" 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 " +
            "SELECT u.id as \"id\", u.name as \"name\", u.sex as \"sex\", u.birthdate as \"birthdate\", u.avatar as \"avatar\",u.hospital_name as \"hospital_name\", p.participant_role as \"role\", true as \"is_patient\",p.last_fetch_time as \"last_fetch_time\",0 as \"level\",u.mobile as\" 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 as \"id\", u.name as \"name\", false \"is_patient\", p.participant_role as \"participant_role\",u.avatar as \"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 as \"id\", u.name as \"name\", true \"is_patient\", p.participant_role as \"participant_role\",u.avatar as \"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 as \"participant_id\",participant_role as \"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 as \"id\", u.name as \"name\", false \"is_patient\", p.participant_role as \"participant_role\",u.avatar as \"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 as \"id\", u.name as \"name\", true \"is_patient\", p.participant_role as \"participant_role\",u.avatar as \"avatar\",ps.name as \"pname\",ps.id as \"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 as \"id\", u.avatar as \"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 as \"id\", u.avatar as \"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) {
        var userNew = users.join(",");
        let sql = "SELECT count(1) as count FROM patients p WHERE p.id in (?)";
        ImDb.execQuery({
            "sql": sql,
            "args": [userNew],
            "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 as \"name\",p.sex as \"sex\",p.birthdate as \"birthdate\",2 as \"userType\" FROM patients p WHERE p.id =? union" +
            " SELECT d.name as \"name\",d.sex as \"sex\",d.birthdate as \"birthdate\",1 as \"userType\" 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 as \"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 = "merge into " + DB_TABLES.Participants + " t1  using( ";
        let args = [];
        let nowDate = new Date();
        log.info("saveParticipantsToMysql:[users.length] = " + users.length);
        for (let j = 0; j < users.length; j++) {
            let tokens = users[j].split(":");
            sql += " select '"+sessionId+"' session_id,'"+tokens[0]+"' participant_id,"+(tokens.length >= 1 ? tokens[1] : '0')+"" +
                " participant_role,now() last_fetch_time from dual ";
            if(j<(users.length-1)){
                sql += " union all ";
            }
        }
        sql += " ) t2 on (t1.session_id=t2.session_id and t1.participant_id=t2.participant_id) "+
            " WHEN MATCHED THEN update set t1.participant_role = t2.participant_role " +
            " WHEN NOT MATCHED THEN INSERT (session_id,participant_id,participant_role,last_fetch_time) VALUES " +
            " (t2.session_id,t2.participant_id,t2.participant_role,t2.last_fetch_time)";
        log.info("saveParticipantsToMysql:[sql] = " + sql);
        ImDb.execQuery({
            "sql": sql,
            "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 as \"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;