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;

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

class TopicRepo {
    constructor() {
    }

    /**
     * 查找议题.
     *
     * @param topicId
     * @param handler
     */
    static findOne(topicId, handler) {
        let sql = "SELECT \"ID\" AS \"id\",\"SESSION_ID\" AS \"session_id\",\"NAME\" AS \"name\",\"CREATE_TIME\" AS \"create_time\",\"END_BY\" AS \"end_by\",\"END_TIME\" AS \"end_time\"," +
            "\"START_MESSAGE_ID\" AS \"start_message_id\",\"END_MESSAGE_ID\" AS \"end_message_id\",\"DESCRIPTION\" AS \"description\",\"STATUS\" AS \"status\" FROM " + DB_TABLES.Topics + " WHERE ID = :ID";

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

    static findLastTopicStatus(sessionId, handler) {
        let sql = "SELECT \"ID\" AS \"id\" FROM " + DB_TABLES.Topics + " WHERE  SESSION_ID = :SESSION_ID AND rownum = 1 ORDER BY CREATE_TIME DESC  ";
        oracledbUtil.query({
            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 \"ID\" AS \"id\",\"SESSION_ID\" AS \"session_id\",\"NAME\" AS \"name\",\"CREATE_TIME\" AS \"create_time\",\"END_BY\" AS \"end_by\",\"END_TIME\" AS \"end_time\",\"START_MESSAGE_ID\" AS \"start_message_id\",\"END_MESSAGE_ID\" AS \"end_message_id\",\"STATUS\" AS \"status\",\"DESCRIPTION\" AS \"description\",\"REPLY\" AS \"reply\",\"REPLY_TIME\" AS \"reply_time\",\"REPLY_USER\" AS \"reply_user\",\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",\"EVALUATE\" AS \"evaluate\",\"EVALUATE_TIME\" AS \"evaluate_time\",\"AGENT\" AS \"agent\" FROM " + DB_TABLES.Topics + " WHERE  SESSION_ID = :SESSION_ID AND rownum = 1 ORDER BY CREATE_TIME DESC ";
        oracledbUtil.query({
            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);
            sql =  " SELECT * FROM (  SELECT rownum r, T.\"ID\" AS \"id\",T.\"SESSION_ID\" AS \"session_id\",T.\"NAME\" AS \"name\",T.\"CREATE_TIME\" AS \"create_time\",T.\"END_BY\" AS \"end_by\",T.\"END_TIME\" AS \"end_time\",T.\"START_MESSAGE_ID\" AS \"start_message_id\",T.\"END_MESSAGE_ID\" AS \"end_message_id\",T.\"STATUS\" AS \"status\",T.\"DESCRIPTION\" AS \"description\",T.\"REPLY\" AS \"reply\",T.\"REPLY_TIME\" AS \"reply_time\",T.\"REPLY_USER\" AS \"reply_user\",T.\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",T.\"EVALUATE\" AS \"evaluate\",T.\"EVALUATE_TIME\" AS \"evaluate_time\",T.\"AGENT\" AS \"agent\", " +
                "S.\"AVATAR\" AS \"avatar\",S.\"SEX\" AS \"sex\",S.\"BIRTHDATE\" AS \"birthdate\",S.\"NAME\" AS \"patient_name\",C.\"DOCTOR\" AS \"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 (:ID) AND T. STATUS = :STATUS ORDER BY CREATE_TIME DESC";
            sql += " AND rownum<="+size+") WHERE r>="+page+" ";
        }else{
            args.push(userId,status,reply);
            sql =  " SELECT * FROM (  SELECT rownum r, T.\"ID\" AS \"id\",T.\"SESSION_ID\" AS \"session_id\",T.\"NAME\" AS \"name\",T.\"CREATE_TIME\" AS \"create_time\",T.\"END_BY\" AS \"end_by\",T.\"END_TIME\" AS \"end_time\",T.\"START_MESSAGE_ID\" AS \"start_message_id\",T.\"END_MESSAGE_ID\" AS \"end_message_id\",T.\"STATUS\" AS \"status\",T.\"DESCRIPTION\" AS \"description\",T.\"REPLY\" AS \"reply\",T.\"REPLY_TIME\" AS \"reply_time\",T.\"REPLY_USER\" AS \"reply_user\",T.\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",T.\"EVALUATE\" AS \"evaluate\",T.\"EVALUATE_TIME\" AS \"evaluate_time\",T.\"AGENT\" AS \"agent\", " +
                "S.\"AVATAR\" AS \"avatar\",S.\"SEX\" AS \"sex\",S.\"BIRTHDATE\" AS \"birthdate\",S.\"NAME\" AS \"patient_name\",C.\"DOCTOR\" AS \"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 (:ID) AND T. STATUS = :STATUS AND T.REPLY=:REPLY ORDER BY CREATE_TIME DESC ";
            sql += " AND rownum<="+size+") WHERE r>="+page+" ";
        }
        oracledbUtil.query({
            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);
            sql =  " SELECT * FROM (  SELECT rownum r, T.\"ID\" AS \"id\",T.\"SESSION_ID\" AS \"session_id\",T.\"NAME\" AS \"name\",T.\"CREATE_TIME\" AS \"create_time\",T.\"END_BY\" AS \"end_by\",T.\"END_TIME\" AS \"end_time\",T.\"START_MESSAGE_ID\" AS \"start_message_id\",T.\"END_MESSAGE_ID\" AS \"end_message_id\",T.\"STATUS\" AS \"status\",T.\"DESCRIPTION\" AS \"description\",T.\"REPLY\" AS \"reply\",T.\"REPLY_TIME\" AS \"reply_time\",T.\"REPLY_USER\" AS \"reply_user\",T.\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",T.\"EVALUATE\" AS \"evaluate\",T.\"EVALUATE_TIME\" AS \"evaluate_time\",T.\"AGENT\" AS \"agent\", " +
                "S.\"AVATAR\" AS \"avatar\",S.\"SEX\" AS \"sex\",S.\"BIRTHDATE\" AS \"birthdate\",S.\"NAME\" AS \"patient_name\",C.\"DOCTOR\" AS \"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 (:ID) AND T. STATUS = :STATUS AND C.TYPE NOT IN ('6','8') ORDER BY CREATE_TIME DESC ";
            sql += " AND rownum<="+size+") WHERE r>="+page+" ";
        }else{
            args.push(userId,status,reply);
            sql =  " SELECT * FROM (  SELECT rownum r, T.\"ID\" AS \"id\",T.\"SESSION_ID\" AS \"session_id\",T.\"NAME\" AS \"name\",T.\"CREATE_TIME\" AS \"create_time\",T.\"END_BY\" AS \"end_by\",T.\"END_TIME\" AS \"end_time\",T.\"START_MESSAGE_ID\" AS \"start_message_id\",T.\"END_MESSAGE_ID\" AS \"end_message_id\",T.\"STATUS\" AS \"status\",T.\"DESCRIPTION\" AS \"description\",T.\"REPLY\" AS \"reply\",T.\"REPLY_TIME\" AS \"reply_time\",T.\"REPLY_USER\" AS \"reply_user\",T.\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",T.\"EVALUATE\" AS \"evaluate\",T.\"EVALUATE_TIME\" AS \"evaluate_time\",T.\"AGENT\" AS \"agent\", " +
                "S.\"AVATAR\" AS \"avatar\",S.\"SEX\" AS \"sex\",S.\"BIRTHDATE\" AS \"birthdate\",S.\"NAME\" AS \"patient_name\",C.\"DOCTOR\" AS \"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 (:ID) AND T. STATUS = :STATUS AND T.REPLY=? AND C.TYPE NOT IN ('6','8') ORDER BY CREATE_TIME DESC ";
            sql += " AND rownum<="+size+") WHERE r>="+page+" ";
        }
        oracledbUtil.query({
            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);
            sql =  " SELECT * FROM (  SELECT rownum r, T.\"ID\" AS \"id\",T.\"SESSION_ID\" AS \"session_id\",T.\"NAME\" AS \"name\",T.\"CREATE_TIME\" AS \"create_time\",T.\"END_BY\" AS \"end_by\",T.\"END_TIME\" AS \"end_time\",T.\"START_MESSAGE_ID\" AS \"start_message_id\",T.\"END_MESSAGE_ID\" AS \"end_message_id\",T.\"STATUS\" AS \"status\",T.\"DESCRIPTION\" AS \"description\",T.\"REPLY\" AS \"reply\",T.\"REPLY_TIME\" AS \"reply_time\",T.\"REPLY_USER\" AS \"reply_user\",T.\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",T.\"EVALUATE\" AS \"evaluate\",T.\"EVALUATE_TIME\" AS \"evaluate_time\",T.\"AGENT\" AS \"agent\", " +
                "S.\"AVATAR\" AS \"avatar\",S.\"SEX\" AS \"sex\",S.\"BIRTHDATE\" AS \"birthdate\",S.\"NAME\" AS \"patient_name\",C.\"DOCTOR\" AS \"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 (:ID) AND T. STATUS = :STATUS AND C.TYPE =:TYPE "+tempParms+" ORDER BY CREATE_TIME DESC ";
            sql += " AND rownum<="+size+") WHERE r>="+page+" ";
        }else{
            args.push(userId,status,reply,type);
            sql =  " SELECT * FROM (  SELECT rownum r, T.\"ID\" AS \"id\",T.\"SESSION_ID\" AS \"session_id\",T.\"NAME\" AS \"name\",T.\"CREATE_TIME\" AS \"create_time\",T.\"END_BY\" AS \"end_by\",T.\"END_TIME\" AS \"end_time\",T.\"START_MESSAGE_ID\" AS \"start_message_id\",T.\"END_MESSAGE_ID\" AS \"end_message_id\",T.\"STATUS\" AS \"status\",T.\"DESCRIPTION\" AS \"description\",T.\"REPLY\" AS \"reply\",T.\"REPLY_TIME\" AS \"reply_time\",T.\"REPLY_USER\" AS \"reply_user\",T.\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",T.\"EVALUATE\" AS \"evaluate\",T.\"EVALUATE_TIME\" AS \"evaluate_time\",T.\"AGENT\" AS \"agent\", " +
                "S.\"AVATAR\" AS \"avatar\",S.\"SEX\" AS \"sex\",S.\"BIRTHDATE\" AS \"birthdate\",S.\"NAME\" AS \"patient_name\",C.\"DOCTOR\" AS \"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 (:ID) AND T. STATUS = :STATUS AND T.REPLY=:REPLY AND C.TYPE =:TYPE "+tempParms+" ORDER BY CREATE_TIME DESC ";
            sql += " AND rownum<="+size+") WHERE r>="+page+" ";
        }
        oracledbUtil.query({
            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\" as \"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 (:ID) AND T. STATUS = :STATUS AND C.TYPE =:TYPE "+tempParms;
        }else{
            args.push(userId,status,reply,type);
            sql =  "SELECT COUNT(T.ID) \"COUNT\" as \"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 (:ID) AND T. STATUS = :STATUS AND T.REPLY=:REPLY AND C.TYPE = :TYPE "+tempParms;
        }
        oracledbUtil.query({
            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);
            sql =  " SELECT * FROM (  SELECT rownum r, T.\"ID\" AS \"id\",T.\"SESSION_ID\" AS \"session_id\",T.\"NAME\" AS \"name\",T.\"CREATE_TIME\" AS \"create_time\",T.\"END_BY\" AS \"end_by\",T.\"END_TIME\" AS \"end_time\",T.\"START_MESSAGE_ID\" AS \"start_message_id\",T.\"END_MESSAGE_ID\" AS \"end_message_id\",T.\"STATUS\" AS \"status\",T.\"DESCRIPTION\" AS \"description\",T.\"REPLY\" AS \"reply\",T.\"REPLY_TIME\" AS \"reply_time\",T.\"REPLY_USER\" AS \"reply_user\",T.\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",T.\"EVALUATE\" AS \"evaluate\",T.\"EVALUATE_TIME\" AS \"evaluate_time\",T.\"AGENT\" AS \"agent\", " +
                "S.\"AVATAR\" AS \"avatar\",S.\"SEX\" AS \"sex\",S.\"BIRTHDATE\" AS \"birthdate\",S.\"NAME\" AS \"patient_name\",C.\"DOCTOR\" AS \"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 (:ID) AND T. STATUS = :STATUS AND C.TYPE NOT IN ('6','8') ORDER BY CREATE_TIME DESC ";
            sql += " AND rownum<="+size+") WHERE r>="+page+" ";
        }else{
            args.push(adminTeamCode,userId,status,reply);
            sql =  " SELECT * FROM (  SELECT rownum r, T.\"ID\" AS \"id\",T.\"SESSION_ID\" AS \"session_id\",T.\"NAME\" AS \"name\",T.\"CREATE_TIME\" AS \"create_time\",T.\"END_BY\" AS \"end_by\",T.\"END_TIME\" AS \"end_time\",T.\"START_MESSAGE_ID\" AS \"start_message_id\",T.\"END_MESSAGE_ID\" AS \"end_message_id\",T.\"STATUS\" AS \"status\",T.\"DESCRIPTION\" AS \"description\",T.\"REPLY\" AS \"reply\",T.\"REPLY_TIME\" AS \"reply_time\",T.\"REPLY_USER\" AS \"reply_user\",T.\"REPLY_MESSAGE_ID\" AS \"reply_message_id\",T.\"EVALUATE\" AS \"evaluate\",T.\"EVALUATE_TIME\" AS \"evaluate_time\",T.\"AGENT\" AS \"agent\", " +
                "S.\"AVATAR\" AS \"avatar\",S.\"SEX\" AS \"sex\",S.\"BIRTHDATE\" AS \"birthdate\",S.\"NAME\" AS \"patient_name\",C.\"DOCTOR\" AS \"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";
            sql += " AND rownum<="+size+") WHERE r>="+page+" ";
        }
        oracledbUtil.query({
            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=:ADMIN_TEAM_CODE AND C.TYPE!=8 AND D.ID IN (:ID) AND T. STATUS = :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=:ADMIN_TEAM_CODE AND C.TYPE!=8 AND D.ID IN (:ID) AND T. STATUS = :STATUS AND T.REPLY=: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=:ADMIN_TEAM_CODE AND C.TYPE!=8 AND D.ID IN (:ID)";
        }
        oracledbUtil.query({
            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\" as \"id\" FROM " + DB_TABLES.Topics + " WHERE  SESSION_ID = ? ORDER BY CREATE_TIME DESC ";
        let sql = "SELECT*FROM ( "+sqlTemp+" AND rownum<=1) WHERE r>=0"
        oracledbUtil.query({
            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\" as \"id\", \"NAME\" as \"name\", \"DESCRIPTION\" as \"description\", \"STATUS\" as \"status\",\"AGENT\" as \"agent\" FROM " + DB_TABLES.Topics + " WHERE ID = ?";

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

    static findTopicStatusAndType(topicId, handler) {
        let sql = "SELECT T.\"ID\" as \"id\", T.\"NAME\" as \"name\", T.\"DESCRIPTION\" as \"description\", T.\"STATUS\" as \"status\",T.\"AGENT\" as \"agent\",C.\"TYPE\" as \"type\" FROM TOPICS T,BASE.WLYY_CONSULT C WHERE T.ID = :ID AND T.ID = C.ID";

        oracledbUtil.query({
            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\" AS \"id\",\"SESSION_ID\" AS \"session_id\",\"NAME\" AS \"name\",\"CREATE_TIME\" AS \"create_time\",\"END_BY\" AS \"end_by\",\"END_TIME\" AS \"end_time\",\"START_MESSAGE_ID\" AS \"start_message_id\",\"END_MESSAGE_ID\" AS \"end_message_id\",\"DESCRIPTION\" AS \"description\",\"STATUS\" AS \"status\" FROM " + DB_TABLES.Topics + " WHERE SESSION_ID = :SESSION_ID ORDER BY ID";

        oracledbUtil.query({
            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\" AS \"id\",\"SESSION_ID\" AS \"session_id\",\"NAME\" AS \"name\",\"CREATE_TIME\" AS \"create_time\",\"END_BY\" AS \"end_by\",\"END_TIME\" AS \"end_time\",\"START_MESSAGE_ID\" AS \"start_message_id\",\"END_MESSAGE_ID\" AS \"end_message_id\",\"DESCRIPTION\" AS \"description\",\"STATUS\" AS \"status\" FROM " + DB_TABLES.Topics + " WHERE ID = :ID ORDER BY ID";

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

    static findAllBySessionIdsAndStatus(sessionIds, status, page, pagesize, handler) {
        let sql = "SELECT*FROM ( SELECT rownum r, \"ID\" AS \"id\",\"SESSION_ID\" AS \"session_id\",\"NAME\" AS \"name\",\"CREATE_TIME\" AS \"create_time\",\"END_BY\" AS \"end_by\",\"END_TIME\" AS \"end_time\",\"START_MESSAGE_ID\" AS \"start_message_id\",\"END_MESSAGE_ID\" AS \"end_message_id\",\"DESCRIPTION\" AS \"description\",\"STATUS\" AS \"status\" FROM " + DB_TABLES.Topics + " WHERE SESSION_ID IN ('" + sessionIds + "') AND STATUS IN (" + status + ") ORDER BY STATUS DESC";
        sql += " AND rownum<="+pagesize+") WHERE r>="+page+" ";
        oracledbUtil.query({
            sql: sql,
            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 (:ID,:SESSION_ID,:NAME,:CREATE_TIME,:START_MESSAGE_ID,:DESCRIPTION,:STATUS,:AGENT)";
        oracledbUtil.query({
            "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_BY,END_TIME=:END_TIME,END_MESSAGE_ID=:END_MESSAGE_ID,STATUS =:STATUS WHERE  ID = :ID";
        oracledbUtil.query({
            "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_USER,REPLY_MESSAGE_ID = :REPLY_MESSAGE_ID WHERE ID = :ID";
        oracledbUtil.query({
            "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 + " = :"+j+" ");
            values.push(jsonValue[j]);
        }

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

        oracledbUtil.query({
            "sql": sql,
            "args": values,
            "handler": handler
        });
    }

    /**
     * 搜索最后回复时间超过指定时限的议题，此议题最后一条消息的回复者必须是医生，即医生发送消息后，患者未理睬的，关闭。
     *
     * @param timespan 时限，以小时计
     * @param handler
     */
    static findAllBySessionLastActiveTime(timespan, handler) {
        let sql = "S.\"ID\" AS \"session_id\",S.\"NAME\" AS \"session_name\",S.\"CREATE_DATE\" AS \"session_create_time\",S.\"LAST_MESSAGE_TIME\" AS \"last_message_time\",T.\"ID\" AS \"topic_id\",T.\"NAME\" AS \"topic_name\",T.\"CREATE_TIME\" AS \"topic_create_time\",T.\"START_MESSAGE_ID\" AS \"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) > :LAST_MESSAGE_TIME " +
            "ORDER BY T.CREATE_TIME";

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

module.exports = TopicRepo;