im.doctor/src/doctor/repository/group.msg.repo.js

"use strict";

var imRepo = require("./database/im.db.js");

exports.save = function (from, groupId, at, contentType, content, handler) {
    imRepo.execQuery({
        "sql": "INSERT INTO msg_group (to_gid,from_uid,at_uid,type,content) VALUES (?,?,?,?,?)",
        "args": [groupId, from, at, contentType, content],
        "handler": handler
    });
};

exports.findAllMessages = function (groupId, contentType, start, end, count, handler) {
    var sql = "SELECT to_gid, msg_id, from_uid, at_uid, type, content, timestamp " +
        "FROM msg_group " +
        "WHERE to_gid = ? AND type in(" + contentType + ") AND msg_id BETWEEN ? AND ? ORDER BY timestamp DESC LIMIT ?";

    imRepo.execQuery({
        "sql": sql,
        "args": [groupId, end + 1, start - 1, count],
        "handler": handler
    });
};

/**
 * 查找用户参与的组列表，包括行政与求助。
 *
 * @param userId 指定的用户
 * @param handler
 */
exports.findAllGroupsWithDoctor = function (userId, handler) {
    //var sql = "SELECT * FROM (" +
    //    " SELECT DISTINCT g.id code, g.name name, ms.last_content_type, ms.last_content, ms.timestamp, ms.new_msg_count, '1' group_type " +
    //    " FROM wlyy.wlyy_admin_team g, wlyy.wlyy_admin_team_member m, wlyy.wlyy_doctor d, msg_statistic ms ," +
    //    " ( SELECT new_msg_count, from_gid  FROM  msg_statistic  WHERE uid =?  GROUP BY from_gid  ORDER BY  `timestamp` DESC ) msgcount " +
    //    " WHERE d.code = ? AND d.code = m.doctor_code AND msgcount.from_gid=ms.from_gid AND m.team_id = g.id AND g.id = ms.from_gid AND ms.last_content_type IN (1,2,3,5,6) GROUP BY g.id , g.name " +
    //    " UNION " +
    //    " SELECT m.group_code, m.group_name, ms.last_content_type, ms.last_content, ms.timestamp, ms.new_msg_count, '2' group_type " +
    //    " FROM wlyy.wlyy_talk_group g, wlyy.wlyy_talk_group_member m, wlyy.wlyy_doctor d, msg_statistic ms, " +
    //    " ( SELECT new_msg_count, from_gid  FROM  msg_statistic  WHERE uid =?  GROUP BY from_gid  ORDER BY  `timestamp` DESC ) msgcount " +
    //    " WHERE d.code = ? AND d.code = m.member_code AND msgcount.from_gid=ms.from_gid AND m.group_code = g.code AND g.type=2  AND g.code = ms.from_gid group by m.group_code, m.group_name" +
    //    ") X ORDER BY timestamp DESC";

    var  sql="SELECT * FROM ( select  g.id code, g. NAME name, ms.last_content_type, ms.last_content, ms.TIMESTAMP AS timestamp, ms.new_msg_count, '1' as group_type FROM wlyy.wlyy_admin_team g, wlyy.wlyy_admin_team_member m, wlyy.wlyy_doctor d, msg_statistic ms WHERE d. CODE = ? AND d. CODE = m.doctor_code AND m.team_id = g.id AND g.id = ms.from_gid AND ms.uid = ? AND ms.last_content_type IN (1, 2, 3, 5, 6) GROUP BY g.id, g. NAME UNION SELECT g.`code`, g.`name`, s.last_content_type, s.last_content, s. TIMESTAMP AS timestamp, s.new_msg_count, '2' AS group_type FROM msg_group w, wlyy.wlyy_talk_group g, wlyy.wlyy_talk_group_member t, msg_statistic s WHERE w.to_gid = g.`code` AND t.type < 4 AND t.group_code = g. CODE AND w.to_gid IN ( SELECT w1.group_code FROM wlyy.wlyy_talk_group_member w1 WHERE w1.member_code = ? AND w1. STATUS = 1 ) AND W.to_gid NOT IN ( SELECT w1.group_code FROM wlyy.wlyy_talk_group_member w1 WHERE w1.group_code = g. CODE AND w1. STATUS = 1 AND TYPE = 4 ) AND s.from_gid = g. CODE AND s.uid = ? GROUP BY g. NAME, g.`code` ) X ORDER BY TIMESTAMP DESC;";

    imRepo.execQuery({
        "sql": sql,
        "args": [userId, userId,userId, userId],
        "handler": handler
    });
};

/**
 * 查找用户参与的讨论组，且含有患者的讨论组。直接从wlyy_talk_group中查找即可。
 *
 * @param userId
 * @param handler
 */
exports.findAllGroupsWithPatient = function (userId, handler) {
    //var sql = "SELECT * FROM (" +
    //    " SELECT g.code, g.name, ms.last_content_type, ms.last_content, ms.timestamp, msgcount.new_msg_count, g.type group_type " +
    //    " FROM msg_statistic ms, (" +
    //    " SELECT g.code code, g.name name, g.type type FROM wlyy.wlyy_talk_group g, wlyy.wlyy_talk_group_member m " +
    //    " WHERE g.code = m.group_code and m.member_code = ?) g, wlyy.wlyy_patient p , " +
    //    " ( SELECT new_msg_count, from_gid  FROM  msg_statistic  WHERE uid =?  GROUP BY from_gid  ORDER BY  `timestamp` DESC ) msgcount " +
    //    " WHERE ((ms.uid = ? and ms.from_uid = p.code)) " +
    //    " and ms.from_gid = g.code and ms.msg_type = 2 AND ms.last_content_type in (1,2,3,5,6) AND msgcount.from_gid=ms.from_gid " +
    //    " UNION " +
    //    " SELECT m.group_code code, m.group_name name, ms.last_content_type, ms.last_content, ms.timestamp, ms.new_msg_count, '2' group_type " +
    //    " FROM wlyy.wlyy_talk_group g, wlyy.wlyy_talk_group_member m, wlyy.wlyy_doctor d, msg_statistic ms, " +
    //    " ( SELECT new_msg_count, from_gid  FROM  msg_statistic  WHERE uid =?  GROUP BY from_gid  ORDER BY  `timestamp` DESC ) msgcount " +
    //    " WHERE d.code = ? AND d.code = m.member_code AND msgcount.from_gid=ms.from_gid " +
    //    "   AND m.group_code = g.code AND g.type=1  AND g.code = ms.from_gid group by m.group_code, m.group_name" +
    //    ") X ORDER BY timestamp DESC";
    var sql ="SELECT g.`code`, g.`name`, s.last_content_type, s.last_content, s. TIMESTAMP as timestamp , s.new_msg_count, g.type group_type " +
              "FROM msg_group w, wlyy.wlyy_talk_group g, wlyy.wlyy_talk_group_member t, msg_statistic s " +
              "WHERE w.to_gid = g.`code` AND t.group_code = g. CODE " +
              "AND w.to_gid IN ( SELECT w1.group_code FROM wlyy.wlyy_talk_group_member w1 WHERE w1.member_code =? ) " +
              "AND s.uid = ? AND s.from_gid IS NOT NULL AND s.from_gid  = g.code GROUP BY g. NAME, g.`code` ORDER BY w.msg_id DESC;"


    imRepo.execQuery({
        "sql": sql,
        "args": [userId, userId, userId, userId, userId, userId],
        "handler": handler
    });
};

/**
 * 查找指定的消息。
 *
 * @param messageId
 * @param handler
 */
exports.findOneMessage = function (messageId, handler) {
    var sql = "SELECT to_gid, msg_id, from_uid, at_uid, type, content, timestamp " +
        "FROM msg_group " +
        "WHERE msg_id = ?";

    imRepo.execQuery({
        "sql": sql,
        "args": [messageId],
        "handler": handler
    });
};

/**
 * 查找未读消息。
 *
 * @param groupId
 * @param start
 * @param count
 * @param handler
 */
exports.findUnread = function (groupId, start, count, handler) {
    var sql = "SELECT msg_id, to_gid, from_uid, at_uid, type, content, timestamp " +
        "FROM msg_group " +
        "WHERE to_gid = ? AND msg_id < ? ORDER BY timestamp DESC LIMIT ?";

    imRepo.execQuery({
        "sql": sql,
        "args": [groupId, start, count],
        "handler": handler
    });
};