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

/**
 * 会话库。
 */
"use strict";

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

const DB_TABLES = require('../../include/commons').DB_TABLES;
const PARTICIPANT_ROLES = require('../../include/commons').PARTICIPANT_ROLES;
const SESSION_STATUS = require('../../include/commons').SESSION_STATUS;
const SESSION_BUSINESS_TYPE = require('../../include/commons').SESSION_BUSINESS_TYPE;
class SessionRepo {
    constructor() {
    }

    /**
     * 获取单个session对象
     *
     * @param sessionId
     * @param handler
     */
    static findOne(sessionId, handler) {
        let sessionSQL = "select id,name,type,create_date,business_type from " + DB_TABLES.Sessions + " s where s.id = ?";
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [sessionId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 获取用户全部会话不包含角色未1的会话
     *
     * @param userId
     * @param handler
     */
    static findAll(userId, handler) {
        let sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? and participant_role =0  group by w.session_id";
        let sys_session = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = 'system' and participant_role =0  group by w.session_id";
        let sessionSQL = "select id, name, type, create_date,business_type, last_sender_id, last_sender_name, last_content_type, last_content, last_message_time,status from "
            + DB_TABLES.Sessions + " s where s.id in(" + sql + ") and s.id not in ("+sys_session+")";
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [userId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 获取用户全部(未结束的)会话不包含角色未1的会话
     * @param userId
     * @param handler
     */
    static findUnEndAll(userId, handler) {
        let sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? and participant_role =0  group by w.session_id";
        let sys_session = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = 'system' and participant_role =0  group by w.session_id";
        let sessionSQL = "select id, name, type, create_date,business_type, last_sender_id, last_sender_name, last_content_type, last_content, last_message_time,status from "
            + DB_TABLES.Sessions + " s where s.id in(" + sql + ") and s.id not in ("+sys_session+") and s.`status` = 0";
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [userId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }


    /**
     * 获取用户全部会话忽略角色
     *
     * @param userId
     * @param handler
     */
    static findAllIgnoreRole(userId, handler) {
        let sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ?  group by w.session_id";
        let sys_session = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = 'system' and participant_role =0  group by w.session_id";
        let sessionSQL = "select id, name, type, create_date,business_type, last_sender_id, last_sender_name, last_content_type, last_content, last_message_time,status from "
            + DB_TABLES.Sessions + " s where s.id in(" + sql + ") and s.id not in ("+sys_session+")";
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [userId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 获取用户全部会话
     *
     * @param userId
     * @param type
     * @param handler
     */
    static findAllByType(userId, type, handler) {
        let sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? and type=? group by w.session_id";
        let sessionSQL = "select id, name, type, create_date, last_sender_id, last_sender_name, last_content_type, last_content, last_message_time from "
            + DB_TABLES.Sessions + " s where s.id in(" + sql + ") ";
        log.info("获取用户全部会话: sql :" + sql);
        log.info("获取用户全部会话: args :" + args);
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [userId, type],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 查找某类型的用户的会话数量
     * @param userId
     * @param type
     * @param handler
     */
    static findSessionCountByType(userId,type,status,handler){
        let sql = "select session_id count from " + DB_TABLES.Participants + " w where w.participant_id = ? group by w.session_id";
        let sessionSQL = "select count(id) count from " + DB_TABLES.Sessions + " s where s.id in(" + sql + ") and s.type=?";
        if(status != null){
            sessionSQL = sessionSQL + " and s.status="+status;
        }
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [userId, type],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 分页获取用户会话列表
     * @param userId
     * @param type
     * @param handler
     */
    static findListByType(userId, type,page,pagesize, status,handler) {
        if (page > 0) {
            if (page == 1) {
                page = 0;
            }else{
                page = (parseInt(page)-1) * parseInt(pagesize);
            }
        }

        let sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? group by w.session_id";
        let sessionSQL = "select id, name, type, create_date, last_sender_id, last_sender_name, last_content_type, last_content, last_message_time from "
            + DB_TABLES.Sessions + " s where s.id in(" + sql + ") and s.type=? ";

        if(status != null && status != ""){
            sessionSQL += " and s.status="+status
        }

        sessionSQL +=  " order by s.last_message_time desc limit "+page+","+pagesize;
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [userId, type],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    static findAllByTypeAndStatus(userId, businessType,status,page,pagesize, handler) {
        if (page > 0) {
            if (page == 1) {
                page = 0;
            }else{
                page = (parseInt(page)-1) * parseInt(pagesize);
            }
        }
        let sessionSQL ="";
        let sql ="";
        if(status == SESSION_STATUS.ENDED){
            if(businessType == SESSION_BUSINESS_TYPE.PATIENT){//区分居民，有未读消息的置顶排列
                //找出已经结束的咨询
                sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ?  group by w.session_id";
                //找出角色讨论组中为旁听且未结束的咨询
                let sql1 = ("select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? and participant_role ="+PARTICIPANT_ROLES.REGULAR+" group by w.session_id")
                sessionSQL =  "select s.* from " + DB_TABLES.Sessions + " s, " + DB_TABLES.Participants + " p " +
                    " where ((s.id in(" + sql + ") and s.business_type = ? and s.status = 1) or (s.id in(" + sql1 + ") and s.business_type = ? and s.status = 0)) " +
                    // " and s.id = p.session_id and p.participant_id = ? ORDER BY (p.last_fetch_time - s.last_message_time+1)>0,s.create_date desc limit "+page+","+pagesize;
                    " and s.id = p.session_id and p.participant_id = ? ORDER BY s.last_message_time desc limit "+page+","+pagesize;
                ImDb.execQuery({
                    "sql": sessionSQL,
                    "args": [userId, businessType,userId,businessType,userId],
                    "handler": handler || function (err, res) {
                        if(err) log.error(err);
                    }
                });
            }else{
                //找出已经结束的咨询
                sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ?  group by w.session_id";
                //找出角色讨论组中为旁听且未结束的咨询
                let sql1 = ("select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? and participant_role ="+PARTICIPANT_ROLES.REGULAR+" group by w.session_id")
                sessionSQL =  "select * from "
                    + DB_TABLES.Sessions + " s where (s.id in(" + sql + ") and s.business_type = ? and s.status = 1) or (s.id in(" + sql1 + ") and s.business_type = ? and s.status = 0) limit "+page+","+pagesize;
                log.info("findAllByTypeAndStatus： sql " + sessionSQL);
                log.info("findAllByTypeAndStatus： args " + [userId, businessType,userId,businessType]);
                ImDb.execQuery({
                    "sql": sessionSQL,
                    "args": [userId, businessType,userId,businessType],
                    "handler": handler || function (err, res) {
                        if(err) log.error(err);
                    }
                });
            }


        }else{
            sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? and participant_role ="+PARTICIPANT_ROLES.HOST+" group by w.session_id";
            sessionSQL =  "select * from "
                + DB_TABLES.Sessions + " s where s.id in(" + sql + ") and s.business_type = ? and s.status = ? limit "+page+","+pagesize;
            log.info("findAllByTypeAndStatus: sql : "+sessionSQL);
            log.info("findAllByTypeAndStatus: args : "+[userId, businessType,status]);
            ImDb.execQuery({
                "sql": sessionSQL,
                "args": [userId, businessType,status],
                "handler": handler || function (err, res) {
                    if(err) log.error(err);
                }
            });
        }

    }

    static findAllByType(userId, businessType,page,pagesize, handler) {
        if (page > 0) {
            if (page == 1) {
                page = 0;
            }else{
                page = (parseInt(page)-1) * parseInt(pagesize);
            }
        }
        let sessionSQL ="";
        let sql ="";
        //sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? and participant_role ="+PARTICIPANT_ROLES.HOST+" group by w.session_id";
        //中山医院无法查询到所有会话记录，暂时取消participant_role的判断条件 20190619
        sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? group by w.session_id";
        sessionSQL =  "select * from "
            + DB_TABLES.Sessions + " s where s.id in(" + sql + ") and s.business_type = ? and s.type!=0 limit "+page+","+pagesize;
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [userId, businessType],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });


    }

    /**
     * 按时间跨度查询会话。
     *
     * TODO: 暂时屏蔽与患者相关的会话，包括MUC与患者名医咨询
     *
     * @param userId
     * @param dateSpan
     * @param handler
     */
    static findAllByTimestampAndType(userId, dateSpan, handler) {
        let sql = "SELECT DISTINCT s.id, CASE WHEN TYPE = 2 THEN d.name ELSE s.name END 'name',s.last_content_type, s.type, s.create_date, s.business_type " +
        "FROM sessions s, participants p " +
        "LEFT JOIN doctors d ON p.participant_id = d.id " +
        "WHERE s.id = p.session_id AND s.last_sender_id <> 'system' " +
        "AND UNIX_TIMESTAMP(s.last_message_time) > UNIX_TIMESTAMP(NOW()) - ? " +
        "AND p.participant_id <> ? AND s.type <> 1 AND s.business_type = 1 " +
        "AND s.id in (select s.id from sessions s, participants p where s.id = p.session_id and p.participant_id = ?) " +
         "ORDER BY s.last_message_time DESC";

        ImDb.execQuery({
            sql: sql,
            args: [dateSpan * 3600 * 24, userId, userId],
            handler: handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 获取用户置顶会话
     *
     * @param userId
     * @param handler
     */
    static findStickySessions(userId, handler) {
        let sql = "select session_id from " + DB_TABLES.Participants + " w where w.participant_id = ? group by w.session_id";
        let sessionSQL = "select s.id,s.name,s.type,s.create_date from " + DB_TABLES.Sessions + " s," + DB_TABLES.StickySessions + " ss  where s.id = ss.session_id s.id in(" + sql + ")";
        ImDb.execQuery({
            "sql": sessionSQL,
            "args": [userId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 保存session。若会话重复创建，则更新会话名称。
     *
     * @param sessionId
     * @param name
     * @param type
     * @param createDate
     * @param businessType
     * @param handler
     */
    static saveSession(sessionId, name, type, createDate, businessType, handler) {
        let sql = "insert into " + DB_TABLES.Sessions + " (id, name, type, create_date,business_type) VALUES (?,?,?,?,?) " +
            "ON DUPLICATE KEY UPDATE name = ?,type = ?";
        ImDb.execQuery({
            "sql": sql,
            "args": [sessionId, name, type, createDate, businessType, name,type],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 更新会话的最终状态。
     *
     * @param lastSenderId
     * @param lastSenderName
     * @param lastMessageTime
     * @param lastContent
     * @param lastContentType
     * @param sessionId
     * @param handler
     */
    static updateSessionLastStatus(lastSenderId, lastSenderName, lastMessageTime, lastContent, lastContentType, sessionId, handler) {
        let sql = "update " + DB_TABLES.Sessions + " set last_sender_id=?,last_sender_name=?,last_message_time=?,last_content=?,last_content_type=? where id = ?";
        ImDb.execQuery({
            "sql": sql,
            "args": [lastSenderId, lastSenderName, lastMessageTime, lastContent, lastContentType, sessionId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 保存置顶会话。
     *
     * @param sessionId
     * @param user
     * @param score
     */
    static saveStickySession(sessionId, user, score) {
        let sql = "insert into " + DB_TABLES.StickySessions + " (user_id,session_id,score) VALUES (?,?,?) ";
        ImDb.execQuery({
            "sql": sql,
            "args": [user, sessionId, score],
            "handler": function (err, res) {
                if (err) {
                    log.error("sql:" + sql + "data:sessionId：" + sessionId + ",user：" + user + ",score：" + score);
                }
            }
        });
    }

    /**
     * 取消会话置顶。
     *
     * @param sessionId
     * @param userId
     */
    static unStickySession(sessionId, userId) {
        let sql = "delete from " + DB_TABLES.StickySessions + " where user_id=? and session_id=? ";
        ImDb.execQuery({
            "sql": sql,
            "args": [userId, sessionId],
            "handler": function (err, res) {
                if (err) {
                    log.error("sql:" + sql + "data:sessionId：" + sessionId + ",user：" + userId);
                }
            }
        });
    }

    static updateSessionStatus(sessionId,status,handler){
        let sql = "update " + DB_TABLES.Sessions + " set status=? where id = ?";
        ImDb.execQuery({
            "sql": sql,
            "args": [status, sessionId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    static updateSessionName(sessionId,name,handler){
        let sql = "update " + DB_TABLES.Sessions + " set name=? where id = ?";
        ImDb.execQuery({
            "sql": sql,
            "args": [name, sessionId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }
}

module.exports = SessionRepo;