TMTT/DataService/OperationRecordService.cs

using Model;
using StandardLibrary;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SQLite;
using System.Security.Claims;

public class OperationRecordService
{
    private static readonly object _lock = new object();
    public static void Log(OperationRecord operationRecord)
    {
        using (var connection = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            connection.Open();

            using (var command = new SQLiteCommand("INSERT INTO OperationLogs (UserId, Username, ActionType, ControlName,OldValue, NewValue, Details ,Timestamp) VALUES (@userId, @username, @actionType, @controlName, @oldValue, @newValue, @details, @timestamp)", connection))
            {
                command.Parameters.AddWithValue("@userId", operationRecord.User.Id);
                command.Parameters.AddWithValue("@username", operationRecord.User.Username);
                command.Parameters.AddWithValue("@actionType",(int)operationRecord.OperationType);
                command.Parameters.AddWithValue("@controlName", operationRecord.ControlName);
                command.Parameters.AddWithValue("@oldValue", operationRecord.OldValue?.ToString() ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@newValue", operationRecord.NewValue?.ToString() ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@details", operationRecord.Detail?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@timestamp", operationRecord.Timestamp.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                command.ExecuteNonQuery();
            }
        }
    }
   
    public static List<OperationRecordDisplay> GetOperationRecordByTimeRange(DateTime start, DateTime end)
    {
        using (var connection = new SQLiteConnection(DatabaseHelper.ConnectionString))
        {
            connection.Open();

            var records = new List<OperationRecordDisplay>();
            string sql = @"
            SELECT Id, Timestamp, Username, ActionType, ControlName,OldValue, NewValue, Details 
            FROM OperationLogs 
            WHERE Timestamp BETWEEN @Start AND @End 
            ORDER BY Timestamp DESC";

            using (var cmd = new SQLiteCommand(sql, connection))
            {
                // 参数化查询，防止注入
                cmd.Parameters.AddWithValue("@Start", start.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@End", end.ToString("yyyy-MM-dd HH:mm:ss"));

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        records.Add(new OperationRecordDisplay
                        {
                            Id = reader.GetInt32(0),
                            Timestamp = DateTime.Parse(reader.GetString(1)),
                            Username = reader.GetString(2),
                            OperationType= EnumExt.TryParseEnum<OperationType>(reader.GetInt32(3), out OperationType opType)? opType.GetDescription() : "未知操作",
                            ControlName = reader.IsDBNull(4) ? null : reader.GetString(4),
                            OldValue = reader.IsDBNull(5) ? null : reader.GetString(5),
                            NewValue = reader.IsDBNull(6) ? null : reader.GetString(6),
                            Detail = reader.IsDBNull(7) ? null : reader.GetString(7),
                        });
                    }
                }
            }

            return records;
        }
    }
}