flutter-sport/lib/db/message_db.dart

import 'dart:async';
import 'dart:convert';

import 'package:path/path.dart';
import 'package:sport/bean/login.dart';
import 'package:sport/bean/message.dart';
import 'package:sqflite/sqflite.dart';

class MessageItem {
  MessageInstance message;
  int status;
  int curId;
  int userId;
  int unReadCount; //未读数量...
  int messageId;
  int isTop;
  // 时间
  int day;
  int time;
  String messageType;
  String dateTime;
  MessageItem({
    this.message,
    this.status,
    this.curId,
    this.userId,
    this.unReadCount,
    this.messageId,
    this.messageType,
    this.day,
    this.time,
    this.dateTime,
  });

  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    data['message'] = json.encode(this.message);
    data['status'] = this.status;
    data['curId'] = this.curId;
    data['userId'] = this.userId;
    data['messageId'] = this.messageId;
//    data['unReadCount'] = this.unReadCount;
//    data["messageId"] = this.messageId;
    return data;
  }

  MessageItem.fromJson(Map<String, dynamic> map) {
    message = MessageInstance.fromJson(json.decode(map['message']));
    if (message != null && message.createdAt.isNotEmpty == true) {
      var t = DateTime.parse(message.createdAt);
      day = t.day;
      time = t.millisecondsSinceEpoch;
    }
    status = map['status'];
    curId = map['curId'];
    userId = map['userId'];
    messageId = map['messageId'];
//    unReadCount = map['unReadCount'];
//    messageId = map['messageId'];
  }
}

class UserTableInfo {
  int userId;
  int isTop;

  UserTableInfo({this.userId, this.isTop});

  UserTableInfo.fromJson(Map<String, dynamic> map) {
    userId = map['userId'];
    isTop = map['isTop'];
  }
  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    data['userId'] = this.userId;
    data['isTop'] = this.isTop;
    return data;
  }
}

class MessageDB {
  static final MessageDB _instance = new MessageDB.internal();

  factory MessageDB() => _instance;

  static Database _db;

  MessageDB.internal();

  Future<Database> get db async {
    if (_db != null) {
      return _db;
    }
    _db = await initDb();

    return _db;
  }

  final String TABLE = 'Message';
  final String USERTABLE = "user";

  initDb() async {
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'message.db');

    var db = await openDatabase(path, version: 1, onCreate: _onCreate);
    return db;
  }

  // @messageId 本地数据库的id
  // @status    该条数据是否是已读的
  // @message   信息
  // @userId    跟谁聊天的信息 跟谁 跟谁 跟谁 跟谁 ...
  FutureOr<void> _onCreate(Database db, int version) async {
    // 表1: message表
    await db.execute(
        'CREATE TABLE $TABLE(messageId INTEGER PRIMARY KEY, status INTEGER ,message TEXT, curId INTEGER,userId INTEGER)');

    // 表2: 用户表
    await db.execute(
        'CREATE TABLE $USERTABLE(userId INTEGER PRIMARY KEY, isTop INTEGER)');
  }

  Future<int> insert(MessageItem item) async {
    var dbClient = await db;
    var result = await dbClient.insert(TABLE, item.toJson());
    return result;
  }

  Future<int> insertUser(UserTableInfo item) async {
    var dbClient = await db;
    var result = await dbClient.insert(USERTABLE, item.toJson());
    return result;
  }

  Future insertAll(List<MessageItem> items) async {
    var dbClient = await db;
    var batch = dbClient.batch();
    items.forEach((item) {
      batch.insert(TABLE, item.toJson());
    });
    batch.commit();
  }

  // 最新的那些...
  Future<List<Map<String, dynamic>>> findLatest() async {
    var dbClient = await db;
    return await dbClient.rawQuery(
      'SELECT * FROM $TABLE ORDER BY curId DESC LIMIT 1',
    );
  }

  Future<List<Map<String, dynamic>>> findAll() async {
    var dbClient = await db;
    return await dbClient.rawQuery(
      'SELECT * FROM $TABLE ORDER BY messageId DESC',
    );
  }

  Future<List<Map<String, dynamic>>> findHasUserId(int userId) async {
    var dbClient = await db;
    return await dbClient
        .rawQuery("SELECT * FROM $USERTABLE where userId = $userId");
  }

  Future<List<Map<String, dynamic>>> findAllUser() async {
    var dbClient = await db;
    return await dbClient
        .rawQuery("SELECT * FROM $USERTABLE ORDER BY userId DESC");
  }

  // 更新状态...
  Future<List<Map<String, dynamic>>> updateStatus(int userId) async {
    var dbClient = await db;

    // status = 0 表示 已读状态... 1： 未读
    await dbClient.update(
      TABLE,
      {"status": 0},
      // Ensure that the Dog has a matching id.
      where: "userId = ?",
      // Pass the Dog's id as a whereArg to prevent SQL injection.
      whereArgs: [userId],
    );
  }

  // 更新状态...
  Future<List<Map<String, dynamic>>> updateIsTop(int userId, int isTop) async {
    var dbClient = await db;
    // status = 0 表示 没有置顶  ... 1:已经置顶
    await dbClient.update(
      USERTABLE,
      {"isTop": isTop},
      // Ensure that the Dog has a matching id.
      where: "userId = ?",
      // Pass the Dog's id as a whereArg to prevent SQL injection.
      whereArgs: [userId],
    );
  }

  Future<List<Map<String, dynamic>>> getMessageList() async {
    var dbClient = await db;
    return await dbClient.rawQuery(
        "select * from $TABLE c, (select userId, max(messageId) mid FROM $TABLE group by userId) cc where c.messageId = cc.mid order by messageId desc;");
  }

  Future<List<Map<String, dynamic>>> getMessageForUserId(int userId) async {
    var dbClient = await db;
    return await dbClient.rawQuery(
      'SELECT * FROM $TABLE WHERE userId = $userId;',
    );
  }

  // 删除数据表格
  Future<int> delete() async {
    var dbClient = await db;
    return await dbClient.delete("$TABLE");
  }

  // 获取还有多少未读的数量
  Future<List<Map<String, dynamic>>> getMessageUnRead() async {
    var dbClient = await db;
//    return await dbClient.rawQuery(
//      "select c.*, ccc.cout from $TABLE c, (select userId, max(messageId) mid FROM $TABLE group by userId) cc, " +
//          "(select userId, sum(status) cout  FROM $TABLE group by userId) ccc where c.messageId = cc.mid order by messageId desc;",
//    );
    return await dbClient.rawQuery(
        "select u.isTop, c.*, ccc.cout from $TABLE c, (select userId, max(messageId) mid FROM $TABLE group by userId) cc,"
        "(select userId, sum(status) cout  FROM $TABLE group by userId) ccc, "
        "$USERTABLE u where c.messageId = cc.mid and cc.userId = ccc.userId and u.userId = cc.userId order by u.isTop desc, messageId desc;");
  }

  // 删除数据库
  void deleteTable() async {
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'message.db');
    await deleteDatabase(path);
    print('删除数据库');
  }

  Future<int> deleteMessage(int messageId) async {
    var dbClient = await db;
    return await dbClient
        .delete("$TABLE", where: "messageId = ?", whereArgs: [messageId]);
  }

  Future<int> deleteUserIdMessage(int userId) async {
    var dbClient = await db;
    return await dbClient
        .delete("$TABLE", where: "userId=?", whereArgs: [userId]);
  }

  Future<int> deleteMessageIdMessage(int messageId) async {
    var dbClient = await db;
    return await dbClient
        .delete("$TABLE", where: "messageId=?", whereArgs: [messageId]);
  }

//  Future<int> delete(int time) async {
//    var dbClient = await db;
//    return await dbClient.delete(TABLE, where: "time <= ?", whereArgs: [time]);
//  }
}