im/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,msg_id 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 ydf_doctor_team g, ydf_doctor_team_member m, 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_talk_group g, wlyy_talk_group_member m, 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 ydf_doctor_team g, ydf_doctor_team_member m, ydf_doctor d, msg_statistic ms WHERE d. id = ? AND d. id = 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,7) GROUP BY g.id, g. NAME ) X ORDER BY TIMESTAMP DESC;";

    imRepo.execQuery({
        "sql": sql,
        "args": [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
    });
};