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

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

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

//oracle工具类
let oracledbUtil = require('../../util/oracledb.util');

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 = '"+userId+"' 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+")";

        oracledbUtil.query({
            "sql": sql,
            "args": [userId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
        //
        // 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) {
        log.info("type="+type);
        if (page > 0) {
            if (page == 1) {
                page = 0;
            }else{
                page = (parseInt(page)-1) * parseInt(pagesize);
            }
        }

        let sqlTemp = "SELECT SESSION_ID  FROM " + DB_TABLES.Participants + " W WHERE W.PARTICIPANT_ID IN(:USER_ID) GROUP BY W.SESSION_ID";
        let sessionSQL = "SELECT rownum r, \"ID\" as \"id\", \"NAME\" as \"name\", \"TYPE\" as \"type\", \"CREATE_DATE\" as \"create_date\", \"LAST_SENDER_ID\" as \"last_sender_id\", \"LAST_SENDER_NAME\" as \"last_sender_name\",\"LAST_CONTENT_TYPE\" as \"last_contnet_type\", \"LAST_CONTENT\" as \"last_content\", \"LAST_MESSAGE_TIME\" as \"last_message_time\" FROM "
            + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sqlTemp + ") AND S.TYPE IN("+type+") ";

        if(status != null && status != ""){
            sessionSQL += " AND S.STATUS="+status
        }

        let sql = "SELECT * FROM ( "+sessionSQL+" AND rownum<="+pagesize+" ORDER BY S.LAST_MESSAGE_TIME DESC ) WHERE r>="+page
        log.info(sessionSQL)
        oracledbUtil.query({
            "sql": sql,
            "args": [userId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
        // ImDb.execQuery({
        //     "sql": sql,
        //     "args": [userId],
        //     "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 rownum r, 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 = ? ";
                sessionSQL = "SELECT*FROM ( " + sessionSQL +" AND rownum<="+pagesize+" ORDER BY S.LAST_MESSAGE_TIME DESC ) WHERE r>="+page;

                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 = '"+userId+"'  GROUP BY W.SESSION_ID";
                //找出角色讨论组中为旁听且未结束的咨询
                let sql1 = ("SELECT SESSION_ID FROM " + DB_TABLES.Participants + " W WHERE W.PARTICIPANT_ID = '"+userId+"' AND PARTICIPANT_ROLE ="+PARTICIPANT_ROLES.REGULAR+" GROUP BY W.SESSION_ID")
                sessionSQL =  "SELECT rownum r, * FROM "
                    + DB_TABLES.Sessions + " S WHERE (S.ID IN(" + sql + ") AND S.BUSINESS_TYPE = "+businessType+" AND S.STATUS = 1) OR (S.ID IN(" + sql1 + ") AND S.BUSINESS_TYPE = "+businessType+" AND S.STATUS = 0) ";
                sessionSQL = "SELECT*FROM ( " + sessionSQL +" AND rownum<="+pagesize+") WHERE r>="+page;

                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 = '"+userId+"' AND PARTICIPANT_ROLE ="+PARTICIPANT_ROLES.HOST+" GROUP BY W.SESSION_ID";
            sessionSQL =  "SELECT rownum r,  * FROM "
                + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.BUSINESS_TYPE = "+businessType+" AND S.STATUS = "+status+" ";
            sessionSQL = "SELECT*FROM ( " + sessionSQL +" AND rownum<="+pagesize+") WHERE r>="+page;

            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);
            }
        }

        // MDT 不执行 businessType 的过滤查询 20191028
        if(businessType){
            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);
                }
            });
        }else{
            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.TYPE!=0 LIMIT "+page+","+pagesize;
            ImDb.execQuery({
                "sql": sessionSQL,
                "args": [userId],
                "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;