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

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

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 S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"CREATE_DATE\" AS \"create_date\",S.\"BUSINESS_TYPE\" as \"business_type\" FROM " + DB_TABLES.Sessions + " S WHERE S.ID = :ID";

        oracledbUtil.query({
            "sql": sessionSQL,
            "args": [sessionId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 获取用户指定会话类型会话不包含角色未1的会话
     *
     * @param userId
     * @param handler
     * @param type
     */
    static findAllBySessionType(userId,type , handler) {
        let sql = "SELECT SESSION_ID FROM " + DB_TABLES.Participants + " W WHERE W.PARTICIPANT_ID = :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 S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
            + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.TYPE IN(" + type + ")AND S.ID NOT IN ("+sys_session+")";

        oracledbUtil.query({
            "sql": sessionSQL,
            "args": [userId],
            "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 = :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 S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
            + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.ID NOT IN ("+sys_session+")";

        oracledbUtil.query({
            "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 = :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 S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
            + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.ID NOT IN ("+sys_session+") AND S.STATUS = 0";

        oracledbUtil.query({
            "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 = :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 S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
            + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.ID NOT IN ("+sys_session+")";

        oracledbUtil.query({
            "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 = :PARTICIPANT_ID AND TYPE=:TYPE GROUP BY W.SESSION_ID";
        let sessionSQL = "SELECT S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
            + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") ";

        oracledbUtil.query({
            "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\" as \"session_id\" FROM " + DB_TABLES.Participants + " W WHERE W.PARTICIPANT_ID = :PARTICIPANT_ID GROUP BY W.SESSION_ID";
        let sessionSQL = "SELECT COUNT(ID) as \"count\"  FROM " + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.TYPE=:TYPE ";
        if(status != null){
            sessionSQL = sessionSQL + " AND S.STATUS="+status;

        }

        oracledbUtil.query({
            "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);
            }
        });
    }

    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 = :PARTICIPANT_ID  GROUP BY W.SESSION_ID";
                //找出角色讨论组中为旁听且未结束的咨询
                let sql1 = ("SELECT SESSION_ID FROM " + DB_TABLES.Participants + " W WHERE W.PARTICIPANT_ID = :PARTICIPANT_ID AND PARTICIPANT_ROLE ="+PARTICIPANT_ROLES.REGULAR+" GROUP BY W.SESSION_ID")
                sessionSQL =  "SELECT rownum r, S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM " + DB_TABLES.Sessions + " S, " + DB_TABLES.Participants + " P " +
                    " WHERE ((S.ID IN(" + sql + ") AND S.BUSINESS_TYPE = :BUSINESS_TYPE AND S.STATUS = 1) OR (S.ID IN(" + sql1 + ") AND S.BUSINESS_TYPE = :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 = :PARTICIPANT_ID ";
                sessionSQL = "SELECT*FROM ( " + sessionSQL +" AND rownum<="+pagesize+" ORDER BY S.LAST_MESSAGE_TIME DESC ) WHERE r>="+page;

                oracledbUtil.query({
                    "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 = :PARTICIPANT_ID  GROUP BY W.SESSION_ID";
                //找出角色讨论组中为旁听且未结束的咨询
                let sql1 = ("SELECT SESSION_ID FROM " + DB_TABLES.Participants + " W WHERE W.PARTICIPANT_ID = :PARTICIPANT_ID AND PARTICIPANT_ROLE ="+PARTICIPANT_ROLES.REGULAR+" GROUP BY W.SESSION_ID")
                sessionSQL =  "SELECT rownum r, S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
                    + DB_TABLES.Sessions + " S WHERE (S.ID IN(" + sql + ") AND S.BUSINESS_TYPE = :BUSINESS_TYPE AND S.STATUS = 1) OR (S.ID IN(" + sql1 + ") AND S.BUSINESS_TYPE = :BUSINESS_TYPE 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]);

                oracledbUtil.query({
                    "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 = :PARTICIPANT_ID AND PARTICIPANT_ROLE ="+PARTICIPANT_ROLES.HOST+" GROUP BY W.SESSION_ID";
            sessionSQL =  "SELECT rownum r,  S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
                + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.BUSINESS_TYPE = :BUSINESS_TYPE 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]);

            oracledbUtil.query({
                "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 = :PARTICIPANT_ID GROUP BY W.SESSION_ID";
            sessionSQL =  "SELECT S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
                + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.BUSINESS_TYPE = :BUSINESS_TYPE AND S.TYPE!=0 LIMIT "+page+","+pagesize;

            oracledbUtil.query({
                "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 = :userId GROUP BY W.SESSION_ID";
            sessionSQL =  "SELECT S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"BUSINESS_TYPE\" AS \"business_type\",S.\"STATUS\" AS \"status\",S.\"CREATE_DATE\" AS \"create_date\",S.\"LAST_SENDER_ID\" AS \"last_sender_id\",S.\"LAST_SENDER_NAME\" AS \"last_sender_name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"LAST_CONTENT\" AS \"last_content\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",S.\"BAK_ID\" AS \"bak_id\" FROM "
                + DB_TABLES.Sessions + " S WHERE S.ID IN(" + sql + ") AND S.TYPE!=0 LIMIT "+page+","+pagesize;
            oracledbUtil.query({
                "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\" AS \"id\",CASE WHEN TYPE=2 THEN D.\"NAME\" AS \"name\" ELSE S.\"NAME\" AS \"name\" END 'NAME' AS \"name\",S.\"LAST_CONTENT_TYPE\" AS \"last_content_type\",S.\"TYPE\" AS \"type\",S.\"CREATE_DATE\" AS \"create_date\",S.\"BUSINESS_TYP\" E AS \"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()) - "+dateSpan * 3600 * 24+" " +
        "AND P.PARTICIPANT_ID <> :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 = :PARTICIPANT_ID) " +
         "ORDER BY S.LAST_MESSAGE_TIME DESC";

        oracledbUtil.query({
            sql: sql,
            args: [ 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 = :PARTICIPANT_ID GROUP BY W.SESSION_ID";
        let sessionSQL = "SELECT S.\"ID\" AS \"id\",S.\"NAME\" AS \"name\",S.\"TYPE\" AS \"type\",S.\"CREATE_DATE\" AS \"create_date\" FROM " + DB_TABLES.Sessions + " S," + DB_TABLES.StickySessions + " SS  WHERE S.ID = SS.SESSION_ID S.ID IN(" + sql + ")";
        oracledbUtil.query({
            "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 (:ID,:NAME,:TYPE,:CREATE_DATE,:BUSINESS_TYPE) ";
        oracledbUtil.query({
            "sql": sql,
            "args": [sessionId, name, type,createDate,businessType],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }

    /**
     * 保存session。若会话重复创建，则更新会话名称。
     *
     * @param sessionId
     * @param name
     * @param type
     * @param createDate
     * @param businessType
     * @param handler
     */
    static updateSessionToMySQL(sessionId, name, type, createDate, businessType, handler) {
        let sql = "UPDATE " + DB_TABLES.Sessions + " SET NAME = :NAME , TYPE = :NAME, CREATE_DATE=:CREATE_DATE,businessType=:BUSINESS_TYPE, STATUS=0 WHERE ID=:ID ";
        oracledbUtil.query({
            "sql": sql,
            "args": [name, type,createDate,businessType,sessionId],
            "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_ID,LAST_SENDER_NAME=:LAST_SENDER_NAME,LAST_MESSAGE_TIME=:LAST_MESSAGE_TIME,LAST_CONTENT=:LAST_CONTENT,LAST_CONTENT_TYPE=:LAST_CONTENT_TYPE WHERE ID = :ID";
        oracledbUtil.query({
            "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 (:USER_ID,:SESSION_ID,:SCORE) ";
        oracledbUtil.query({
            "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=:USER_ID AND SESSION_ID=:SESSION_ID ";
        oracledbUtil.query({
            "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=:STATUS WHERE ID = :ID";
        oracledbUtil.query({
            "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=:NAME WHERE ID = :ID";
        oracledbUtil.query({
            "sql": sql,
            "args": [name, sessionId],
            "handler": handler || function (err, res) {
                if(err) log.error(err);
            }
        });
    }
}

module.exports = SessionRepo;