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

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

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

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

class TopicRepo {
    constructor() {
    }

    /**
     * 查找议题.
     *
     * @param topicId
     * @param handler
     */
    static findOne(topicId, handler) {
        let sql = "SELECT ID, SESSION_ID, NAME, CREATE_TIME, END_BY, END_TIME," +
            " START_MESSAGE_ID, END_MESSAGE_ID, DESCRIPTION, STATUS FROM " + DB_TABLES.Topics + " WHERE ID = ?";

        ImDb.execQuery({
            sql: sql,
            args: [topicId],
            handler: handler || function (err, res) {
                if (err) log.error(err);
            }
        });
    }

    static findLastTopicStatus(sessionId, handler) {
        let sql = "SELECT ID FROM " + DB_TABLES.Topics + " WHERE  SESSION_ID = ? ORDER BY CREATE_TIME DESC LIMIT 0, 1";
        ImDb.execQuery({
            sql: sql,
            args: [sessionId],
            handler: function (err, res) {
                if (res && res.length == 0) {
                    handler(null, null);
                } else {
                    TopicRepo.findTopicStatus(res[0].id, handler);
                }
            }
        });
    }

    static findLastBySessionId(sessionId, handler) {
        let sql = "SELECT * FROM " + DB_TABLES.Topics + " WHERE  SESSION_ID = ? ORDER BY CREATE_TIME DESC LIMIT 0, 1";
        ImDb.execQuery({
            sql: sql,
            args: [sessionId],
            handler: function (err, res) {
                handler(err, res);
            }
        });
    }


    static findAllByUserAndReplyAndStatus(userId,reply,status,page,size,handler){
        let sql = "";
        var args =[];
        if(status==10){
            args.push(userId,status,page,size);
            sql =  "SELECT T.*, S.AVATAR,S.SEX,S.BIRTHDATE, S.`NAME` AS PATIENT_NAME,C.DOCTOR FROM "+
                  DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsult+" C,"+DB_TABLES.Patients+" S "+
                  "WHERE D.ID = P.PARTICIPANT_ID AND C.ID = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                  "AND D.ID IN (?) AND T. STATUS = ? ORDER BY CREATE_TIME DESC LIMIT ?,?";
        }else{
            args.push(userId,status,reply,page,size);
            sql =  "SELECT T.*, S.AVATAR,S.SEX,S.BIRTHDATE, S.`NAME` AS PATIENT_NAME,C.DOCTOR FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsult+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.ID = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND D.ID IN (?) AND T. STATUS = ? AND T.REPLY=? ORDER BY CREATE_TIME DESC LIMIT ?,?";
        }
        ImDb.execQuery({
            sql: sql,
            args: args,
            handler: function (err, res) {
                handler(err, res);
            }
        });
    }

    /**
     * 过滤名医咨询和续方咨询
     * @param userId
     * @param reply
     * @param status
     * @param page
     * @param size
     * @param handler
     */
    static findAllByUserAndReplyAndStatusHealthTopic(userId,reply,status,page,size,handler){
        let sql = "";
        var args =[];
        if(status==10){
            args.push(userId,status,page,size);
            sql =  "SELECT T.*, S.AVATAR,S.SEX,S.BIRTHDATE, S.`NAME` AS PATIENT_NAME,C.DOCTOR FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsult+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.ID = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND D.ID IN (?) AND T. STATUS = ? AND C.TYPE NOT IN ('6','8') ORDER BY CREATE_TIME DESC LIMIT ?,?";
        }else{
            args.push(userId,status,reply,page,size);
            sql =  "SELECT T.*, S.AVATAR,S.SEX,S.BIRTHDATE, S.`NAME` AS PATIENT_NAME,C.DOCTOR FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsult+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.ID = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND D.ID IN (?) AND T. STATUS = ? AND T.REPLY=? AND C.TYPE NOT IN ('6','8') ORDER BY CREATE_TIME DESC LIMIT ?,?";
        }
        ImDb.execQuery({
            sql: sql,
            args: args,
            handler: function (err, res) {
                handler(err, res);
            }
        });
    }

    /**
     * 按类型查找医生的未回复，进行中，已完成的咨询
     * @param userId
     * @param reply
     * @param status
     * @param type
     * @param page
     * @param size
     * @param handler
     */
    static findAllTopicByType(userId,reply,status,type,patientName,startTime,endTime,page,size,handler){
        let sql = "";
        var args =[];
        var tempParms = "";
        if(patientName){
            tempParms += " AND S.NAME LIKE '%"+patientName+"%' ";
        }
        if(startTime){
            tempParms += " AND T.CREATE_TIME >= '"+startTime+"' ";
        }
        if(endTime){
            tempParms += " AND T.CREATE_TIME <= '"+endTime+"' ";
        }
        if(status==10){
            args.push(userId,status,type,page,size);
            sql =  "SELECT T.*, S.AVATAR,S.SEX,S.BIRTHDATE, S.`NAME` AS PATIENT_NAME,C.DOCTOR FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsult+" D,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.ID = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND D.ID IN (?) AND T. STATUS = ? AND C.TYPE =? "+tempParms+" ORDER BY CREATE_TIME DESC LIMIT ?,?";
        }else{
            args.push(userId,status,reply,type,page,size);
            sql =  "SELECT T.*, S.AVATAR,S.SEX,S.BIRTHDATE, S.`NAME` AS PATIENT_NAME,C.DOCTOR FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsult+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.ID = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND D.ID IN (?) AND T. STATUS = ? AND T.REPLY=? AND C.TYPE = ? "+tempParms+" ORDER BY CREATE_TIME DESC LIMIT ?,?";
        }
        ImDb.execQuery({
            sql: sql,
            args: args,
            handler: function (err, res) {
                handler(err, res);
            }
        });
    }

    /**
     * 按类型查找医生的未回复，进行中，已完成的咨询 总数
     * @param userId
     * @param reply
     * @param status
     * @param type
     * @param handler
     */
    static topicListCountByType(userId,reply,status,type,patientName,startTime,endTime,handler){
        let sql = "";
        var args =[];
        var tempParms = "";
        if(patientName){
            tempParms += " AND S.NAME LIKE '%"+patientName+"%' ";
        }
        if(startTime){
            tempParms += " AND T.CREATE_TIME >= '"+startTime+"' ";
        }
        if(endTime){
            tempParms += " AND T.CREATE_TIME <= '"+endTime+"' ";
        }
        if(status==10){
            args.push(userId,status,type);
            sql =  "SELECT COUNT(T.ID) COUNT FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsult+" D,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.ID = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND D.ID IN (?) AND T. STATUS = ? AND C.TYPE =? "+tempParms;
        }else{
            args.push(userId,status,reply,type);
            sql =  "SELECT COUNT(T.ID) COUNT FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsult+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.ID = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND D.ID IN (?) AND T. STATUS = ? AND T.REPLY=? AND C.TYPE = ? "+tempParms;
        }
        ImDb.execQuery({
            sql: sql,
            args: args,
            handler: function (err, res) {
                handler(err, res);
            }
        });
    }

    /**
     * 过滤名医咨询和续方咨询(区分团队)
     * @param userId
     * @param reply
     * @param status
     * @param page
     * @param size
     * @param handler
     */
    static findAllByUserAndReplyAndStatusHealthTeamTopic(userId,reply,status,adminTeamCode,page,size,handler){
        let sql = "";
        var args =[];
        if(status==10){
            args.push(adminTeamCode,userId,status,page,size);
            sql =  "SELECT T.*, S.AVATAR,S.SEX,S.BIRTHDATE, S.`NAME` AS PATIENT_NAME,C.DOCTOR FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsultTeam+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.CONSULT = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND C.ADMIN_TEAM_CODE =? AND D.ID IN (?) AND T. STATUS = ? AND C.TYPE NOT IN ('6','8') ORDER BY CREATE_TIME DESC LIMIT ?,?";
        }else{
            args.push(adminTeamCode,userId,status,reply,page,size);
            sql =  "SELECT T.*, S.AVATAR,S.SEX,S.BIRTHDATE, S.`NAME` AS PATIENT_NAME,C.DOCTOR FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsultTeam+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.CONSULT = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND C.ADMIN_TEAM_CODE =? AND D.ID IN (?) AND T. STATUS = ? AND T.REPLY=? AND C.TYPE NOT IN ('6','8') ORDER BY CREATE_TIME DESC LIMIT ?,?";
        }
        ImDb.execQuery({
            sql: sql,
            args: args,
            handler: function (err, res) {
                handler(err, res);
            }
        });
    }

    static findReplyCount(userId,reply,status,adminTeamCode,handler){
        let sql = "";
        var args =[];
        if(status==10){
            args.push(adminTeamCode,userId,status);
            sql =  "SELECT COUNT(1)  AS COUNT FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsultTeam+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.CONSULT = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND C.ADMIN_TEAM_CODE=? AND C.TYPE!=8 AND D.ID IN (?) AND T. STATUS = ? ";
        }else if(status){
            args.push(adminTeamCode,userId,status,reply);
            sql =  "SELECT count(1) as count FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsultTeam+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.CONSULT = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND C.ADMIN_TEAM_CODE=? AND C.TYPE!=8 AND D.ID IN (?) AND T. STATUS = ? AND T.REPLY=? ";
        }else{
            args.push(adminTeamCode,userId);
            sql =  "SELECT COUNT(1) AS COUNT FROM "+
                DB_TABLES.Topics+" T,"+DB_TABLES.Participants+" P,"+DB_TABLES.Doctors+" D,"+DB_TABLES.WlyyConsultTeam+" C,"+DB_TABLES.Patients+" S "+
                "WHERE D.ID = P.PARTICIPANT_ID AND C.CONSULT = T.ID AND C.PATIENT = S.ID AND P.SESSION_ID = T.SESSION_ID "+
                "AND C.ADMIN_TEAM_CODE=? AND C.TYPE!=8 AND D.ID IN (?)";
        }
        ImDb.execQuery({
            sql: sql,
            args: args,
            handler: function (err, res) {
                if(err){
                    log.error("get topic count error");
                    handler(err,0);
                    return;
                }
                handler(err, res[0].count);
            }
        });
    }

    static findLastTopicStatusAndType(sessionId, handler) {

        let sqlTemp = "SELECT rownum r, ID FROM " + DB_TABLES.Topics + " WHERE  SESSION_ID = ? ORDER BY CREATE_TIME DESC ";
        let sql = "SELECT*FROM ( "+sqlTemp+" AND rownum<=1) WHERE r>=0"
        ImDb.execQuery({
            sql: sql,
            args: [sessionId],
            handler: function (err, res) {
                if (res && res.length == 0) {
                    handler(null, null);
                } else {
                    TopicRepo.findTopicStatusAndType(res[0].id, handler);
                }
            }
        });
    }


    static findTopicStatus(topicId, handler) {
        let sql = "SELECT ID, NAME, DESCRIPTION, STATUS,AGENT FROM " + DB_TABLES.Topics + " WHERE ID = ?";

        ImDb.execQuery({
            sql: sql,
            args: [topicId],
            handler: handler || function (err, res) {
                if (err) log.error(err);
            }
        });
    }

    static findTopicStatusAndType(topicId, handler) {
        let sql = "SELECT T.ID, T.NAME, T.DESCRIPTION, T.STATUS,T.AGENT,C.TYPE FROM TOPICS T,BASE.WLYY_CONSULT C WHERE T.ID = ? AND T.ID = C.ID";

        ImDb.execQuery({
            sql: sql,
            args: [topicId],
            handler: handler || function (err, res) {
                if (err) log.error(err);
            }
        });
    }

    /**
     * 获取会话中的议题。
     *
     * @param sessionId
     * @param handler
     */
    static findAllBySessionId(sessionId, handler) {
        let sql = "SELECT ID, SESSION_ID, NAME, CREATE_TIME, END_BY, END_TIME," +
            " START_MESSAGE_ID, END_MESSAGE_ID, DESCRIPTION, STATUS FROM " + DB_TABLES.Topics + " WHERE SESSION_ID = ? ORDER BY ID";

        ImDb.execQuery({
            sql: sql,
            args: [sessionId],
            handler: handler || function (err, res) {
                if (err) log.error(err);
            }
        });
    }

    /**
     * 获取会话中的议题。
     *
     * @param id
     * @param handler
     */
    static findAllByTopicId(id, handler) {
        let sql = "SELECT ID, SESSION_ID, NAME, CREATE_TIME, END_BY, END_TIME," +
            " START_MESSAGE_ID, END_MESSAGE_ID, DESCRIPTION, STATUS FROM " + DB_TABLES.Topics + " WHERE ID = ? ORDER BY ID";

        ImDb.execQuery({
            sql: sql,
            args: [id],
            handler: handler || function (err, res) {
                if (err) log.error(err);
            }
        });
    }

    static findAllBySessionIdsAndStatus(sessionIds, status, page, pagesize, handler) {
        let sql = "SELECT ID, SESSION_ID, NAME, CREATE_TIME, END_BY, END_TIME," +
            " START_MESSAGE_ID, END_MESSAGE_ID, DESCRIPTION, STATUS FROM " + DB_TABLES.Topics + " WHERE SESSION_ID IN ('" + sessionIds + "') AND STATUS IN (" + status + ") ORDER BY STATUS DESC LIMIT ?,? ";
        ImDb.execQuery({
            sql: sql,
            args: [page, pagesize],
            handler: handler || function (err, res) {
                if (err) log.error(err);
            }
        });
    }


    /**
     * 保存议题
     *
     * @param topicName
     * @param topicId
     * @param sessionId
     * @param messageId
     * @param date
     * @param description
     * @param status
     * @param agent
     * @param handler
     */
    static saveTopic(topicName, topicId, sessionId, messageId, date, description, status, agent, handler) {
        let sql = "INSERT INTO " + DB_TABLES.Topics + " (ID,SESSION_ID,NAME,CREATE_TIME,START_MESSAGE_ID,DESCRIPTION,STATUS,AGENT) VALUES (?,?,?,?,?,?,?,?)";
        ImDb.execQuery({
            "sql": sql,
            "args": [topicId, sessionId, topicName, date, messageId, description, status, agent],
            "handler": function (err, res) {
                handler(err, res);
            }
        });
    }

    /**
     * 结束议题
     *
     * @param topicId
     * @param endUser
     * @param date
     * @param messageId
     * @param status
     */
    static endTopic(topicId, endUser, date, messageId, status) {
        let sql = "UPDATE " + DB_TABLES.Topics + " SET END_BY = ?,END_TIME=?,END_MESSAGE_ID=?,STATUS = ? WHERE  ID = ?";
        ImDb.execQuery({
            "sql": sql,
            "args": [endUser, date, messageId, status, topicId],
            "handler": function (err, res) {
                if (err) {
                    log.error("endTopic is fail error: " + err);
                } else {
                    log.info("endTopic is success");
                }
            }
        });
    }

    /**
     * 医生第一次回复咨询
     * @param reply_user
     * @param reply_message_id
     * @param topicId
     */
    static replyTopic(reply_user,reply_message_id,topicId,handler){
        let sql = "UPDATE " + DB_TABLES.Topics + " SET REPLY = 1,REPLY_TIME = NOW(),REPLY_USER = ?,REPLY_MESSAGE_ID = ? WHERE ID = ?";
        ImDb.execQuery({
            "sql": sql,
            "args": [reply_user, reply_message_id, topicId],
            "handler": handler
        });
    }

    /**
     * 更新议题状态。
     *
     * @param topicId
     * @param jsonValue
     * @param handler
     */
    static updateTopics(topicId, jsonValue, handler) {
        let values = [];
        let sql = "UPDATE TOPICS SET ";
        let key = [];
        for (let j in jsonValue) {
            key.push(j + " = ?");
            values.push(jsonValue[j]);
        }

        sql = sql + key.join(",");
        sql = sql + " WHERE ID = ?";
        values.push(topicId);

        ImDb.execQuery({
            "sql": sql,
            "args": values,
            "handler": handler
        });
    }

    /**
     * 搜索最后回复时间超过指定时限的议题，此议题最后一条消息的回复者必须是医生，即医生发送消息后，患者未理睬的，关闭。
     *
     * @param timespan 时限，以小时计
     * @param handler
     */
    static findAllBySessionLastActiveTime(timespan, handler) {
        let sql = "SELECT S.ID SESSION_ID, S.NAME SESSION_NAME, S.CREATE_DATE SESSION_CREATE_TIME, S.LAST_MESSAGE_TIME, " +
            "T.ID TOPIC_ID, T.NAME TOPIC_NAME, T.CREATE_TIME TOPIC_CREATE_TIME, T.START_MESSAGE_ID " +
            "FROM SESSIONS S, WLYY_CONSULTS T " +
            "WHERE S.ID = T.SESSION_ID  AND T.END_MESSAGE_ID IS NULL AND S.LAST_CONTENT_TYPE IN(1,2,3,4) AND S.LAST_SENDER_ID IN (SELECT ID FROM DOCTORS D WHERE D.ID<>T.PATIENT) " +
            "AND UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(S.LAST_MESSAGE_TIME) > ? " +
            "ORDER BY T.CREATE_TIME";

        ImDb.execQuery({
            sql: sql,
            args: [timespan * 3600],
            handler: handler
        });
    }
}

module.exports = TopicRepo;