MvvmScaffoldFrame48/MvvmScaffoldFrame48.DLL/AuditTrail/OperationRecordManagement.cs

//值变更记录管理
using MvvmScaffoldFrame48.Model.StorageModel.AuditTrail;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Threading.Tasks;

namespace MvvmScaffoldFrame48.DLL.AuditTrail
{
    public class OperationRecordManagement
    {
        private readonly string _dbPath;

        private string _connString;

        private object locker = new object();

        /// <summary>
        /// 构造函数
        /// </summary>
        public OperationRecordManagement()
        {
            _dbPath = $"{AppDomain.CurrentDomain.BaseDirectory}DATA\\AuditTrail.db";
            // 1. 获取文件夹路径
            string directoryPath = Path.GetDirectoryName(_dbPath);

            // 2. 检查文件夹是否存在
            if (!Directory.Exists(directoryPath))
            {
                // 创建文件夹
                Directory.CreateDirectory(directoryPath);
            }
            InitializeDatabase();
        }

        /// <summary>
        /// 构造函数(指定数据库路径)
        /// </summary>
        /// <param name="dbPath"></param>
        public OperationRecordManagement(string dbPath)
        {
            if(dbPath == null|| dbPath == "")
            {
                _dbPath = $"{AppDomain.CurrentDomain.BaseDirectory}DATA\\AuditTrail.db";
            }
            else
            {
                _dbPath = dbPath;
            }

            // 1. 获取文件夹路径
            string directoryPath = Path.GetDirectoryName(_dbPath);

            // 2. 检查文件夹是否存在
            if (!Directory.Exists(directoryPath))
            {
                // 创建文件夹
                Directory.CreateDirectory(directoryPath);
            }
            InitializeDatabase();
        }

        /// <summary>
        /// 初始化数据库和表
        /// </summary>
        private void InitializeDatabase()
        {
            if (!File.Exists(_dbPath))
            {
                SQLiteConnection.CreateFile(_dbPath);
            }

            _connString = $"Data Source={_dbPath};Version=3;" +
                "Journal Mode=WAL;" +           // 使用WAL模式提高并发性
                "Synchronous=Normal;" +         // 平衡性能和安全性
                "Cache Size=10000;" +           // 增加缓存大小
                "Pooling=true;" +               // 启用连接池
                "Max Pool Size=100;";         // 设置最大连接池大小

            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string createTableSql = @"
                    CREATE TABLE IF NOT EXISTS OperationRecord (
                        ID INTEGER PRIMARY KEY AUTOINCREMENT,
                        Category TEXT NOT NULL,
                        OldMessage TEXT NOT NULL,
                        NewMessage TEXT NOT NULL,
                        DateTime TEXT NOT NULL,
                        userID INTEGER NOT NULL,
                        IsDeleted INTEGER CHECK (IsDeleted IN (0, 1))
                )";
                new SQLiteCommand(createTableSql, conn).ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="Message"></param>
        public void InsertOperationRecord(OperationRecordModel Message)
        {
            lock (locker)
            {
                using (var conn = new SQLiteConnection(_connString))
                {
                    conn.Open();
                    string sql = "INSERT INTO OperationRecord (Category,OldMessage,NewMessage,DateTime,userID,IsDeleted) VALUES (@Category, @OldMessage,@NewMessage, @DateTime, @userID,@IsDeleted)";
                    var cmd = new SQLiteCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@Category", Message.Category);
                    cmd.Parameters.AddWithValue("@OldMessage", Message.OldMessage);
                    cmd.Parameters.AddWithValue("@NewMessage", Message.NewMessage);
                    cmd.Parameters.AddWithValue("@DateTime", Message.DateTime.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    cmd.Parameters.AddWithValue("@userID", Message.userID); // 格式化为字符串
                    cmd.Parameters.AddWithValue("@IsDeleted", Message.IsDeleted == true ? 1 : 0);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<OperationRecordModel> GetAllOperationRecord()
        {
            var persons = new List<OperationRecordModel>();
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string sql = "SELECT * FROM OperationRecord  WHERE IsDeleted=0";
                var cmd = new SQLiteCommand(sql, conn);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new OperationRecordModel
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            Category = reader["Category"].ToString(),
                            OldMessage = reader["OldMessage"].ToString(),
                            NewMessage = reader["NewMessage"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            userID = Convert.ToInt32(reader["userID"]),
                            IsDeleted = Convert.ToInt32(reader["IsDeleted"]) == 1 ? true : false
                        });
                    }
                }
            }
            return persons;
        }

        /// <summary>
        /// 获取值改变信息
        /// </summary>
        /// <param name="UserID">操作员ID</param>
        /// <returns></returns>
        public List<OperationRecordModel> GetOperationRecordByID(int UserID)
        {
            var persons = new List<OperationRecordModel>();
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string sql = "SELECT * FROM OperationRecord WHERE userID=@userID AND IsDeleted=0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@userID", UserID);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new OperationRecordModel
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            Category = reader["Category"].ToString(),
                            OldMessage = reader["OldMessage"].ToString(),
                            NewMessage = reader["NewMessage"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            userID = Convert.ToInt32(reader["userID"]),
                            IsDeleted = Convert.ToInt32(reader["IsDeleted"]) == 1 ? true : false
                        });
                    }
                }
            }
            return persons;
        }

        /// <summary>
        /// 获取指定日期的值改变信息
        /// </summary>
        /// <param name="OperationDateTime"></param>
        /// <returns></returns>
        public List<OperationRecordModel> GetOperationRecordByDateTime(DateTime OperationDateTime)
        {
            var persons = new List<OperationRecordModel>();
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string sql = "SELECT * FROM OperationRecord WHERE date(DateTime) = date(@DateTime) AND IsDeleted=0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@DateTime", OperationDateTime.ToString("yyyy-MM-dd"));
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new OperationRecordModel
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            Category = reader["Category"].ToString(),
                            OldMessage = reader["OldMessage"].ToString(),
                            NewMessage = reader["NewMessage"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            userID = Convert.ToInt32(reader["userID"]),
                            IsDeleted = Convert.ToInt32(reader["IsDeleted"]) == 1 ? true : false
                        });
                    }
                }
            }
            return persons;
        }

        /// <summary>
        /// 获取指定时间段内的值改变信息
        /// </summary>
        public List<OperationRecordModel> GetOperationRecordByDateTime(DateTime OperationStartDateTime, DateTime OperationEndDateTime)
        {
            var persons = new List<OperationRecordModel>();
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open();
                string sql = "SELECT * FROM OperationRecord WHERE date(DateTime) >= date(@StartDateTime) AND date(DateTime) <= date(@EndDateTime) AND IsDeleted=0";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@StartDateTime", OperationStartDateTime.ToString("yyyy-MM-dd"));
                cmd.Parameters.AddWithValue("@EndDateTime", OperationEndDateTime.ToString("yyyy-MM-dd"));
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        persons.Add(new OperationRecordModel
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            Category = reader["Category"].ToString(),
                            OldMessage = reader["OldMessage"].ToString(),
                            NewMessage = reader["NewMessage"].ToString(),
                            // 解析时间字符串
                            DateTime = DateTime.Parse(reader["DateTime"].ToString()),
                            userID = Convert.ToInt32(reader["userID"]),
                            IsDeleted = Convert.ToInt32(reader["IsDeleted"]) == 1 ? true : false
                        });
                    }
                }
            }
            return persons;
        }
    }
}