im.doctor/src/server/repository/mysql/topics.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;

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.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 findBedBySessionId(sessionId, handler) {
        let sql = "select * from " + DB_TABLES.Sessions + " where session_id = ? and type=7 ";
        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 patient
     * @param patientName
     * @param handler
     */
    static findAllByUserAndReplyAndStatusHealthTopic(userId,reply,status,page,size,patient,patientName,handler){
        let sql = "";
        var args =[];
        var tempParms = "";

        if (patient){
            tempParms += " and s.id='"+patient+"' ";
        }
        if (patientName){
            tempParms += " and s.name like '%"+patientName+"%' ";
        }

        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 ('3','6','8')"+tempParms+" 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 ('3','6','8')"+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 patient
     * @param patientName
     * @param startTime
     * @param endTime
     * @param page
     * @param size
     * @param handler
     */
    static findAllTopicByType(userId,reply,status,type,patient,patientName,startTime,endTime,page,size,handler){
        let sql = "";
        var args =[];
        var tempParms = "";
        if(patient){
            tempParms += " and s.id = '"+patient+"' ";
        }
        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(type){
            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,c.type 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 =? "+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,c.type 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 ?,?";
            }
        }else{
            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,c.type 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 = ?  "+tempParms+" 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,c.type 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=?  "+tempParms+" ORDER BY create_time DESC limit ?,?";
            }
        }

        log.info("sql:"+sql);

        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+" 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 =? "+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 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.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,wlyy.wlyy_consult c where t.id = ? and t.id = c.code";

        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 reply_user
     * @param reply_message_id
     * @param topicId
     */
    static specialistReplyTopic(reply_user,topicId,handler){
        let sql = "UPDATE wlyy.wlyy_consult_help SET status = '1',reply_time = now()" +
            ",response_time=TIMESTAMPDIFF(SECOND,create_time,now()) WHERE consult = ? and status='0' and specialist=?";
        ImDb.execQuery({
            "sql": sql,
            "args": [topicId,reply_user],
            "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;