Imports System.IO
Imports MySql.Data.MySqlClient
Imports PrnComexcep
Imports PrnDatawork
Imports PrnDatbasehlp
Imports PrnGlbcfg
Imports PrnUtlthods
Public Class SystemService
Implements IDisposable
Private _configRepository As SystemConfigRepository
Private _authorizationService As AuthorizationService
Private _isDisposed As Boolean = False
Public Sub New()
_configRepository = New SystemConfigRepository()
_authorizationService = New AuthorizationService()
End Sub
Public Async Function GetSystemConfigAsync(configKey As String, currentUserId As Integer) As Task(Of SystemConfigResult)
Try
Dim hasAccess = Await _authorizationService.CheckOperationPermissionAsync(currentUserId, "system_config_view")
If Not hasAccess Then
Return New SystemConfigResult(False, Nothing, "没有查看系统配置的权限")
End If
Dim config = Await _configRepository.GetConfigByKeyAsync(configKey)
If config Is Nothing Then
Return New SystemConfigResult(False, Nothing, "配置项不存在")
End If
Return New SystemConfigResult(True, config, "获取系统配置成功")
Catch ex As Exception
Logger.Error($"获取系统配置失败: {configKey}", ex)
Return New SystemConfigResult(False, Nothing, "系统错误，请稍后重试")
End Try
End Function
Public Async Function UpdateSystemConfigAsync(configKey As String, configValue As String, updatedBy As Integer) As Task(Of OperationResult)
Try
Dim hasAccess = Await _authorizationService.CheckOperationPermissionAsync(updatedBy, "system_config_update")
If Not hasAccess Then
Return New OperationResult(False, "没有更新系统配置的权限")
End If
Dim existingConfig = Await _configRepository.GetConfigByKeyAsync(configKey)
If existingConfig Is Nothing Then
Return New OperationResult(False, "配置项不存在")
End If
If existingConfig.IsSystem Then
Return New OperationResult(False, "系统配置项不允许修改")
End If
existingConfig.ConfigValue = configValue
existingConfig.UpdatedTime = DateTime.Now
existingConfig.UpdatedBy = updatedBy
Dim success = Await _configRepository.UpdateConfigAsync(existingConfig)
If success Then
ConfigManager.UpdateConfig(configKey, configValue)
Return New OperationResult(True, "系统配置更新成功")
Else
Return New OperationResult(False, "系统配置更新失败")
End If
Catch ex As Exception
Logger.Error($"更新系统配置失败: {configKey}", ex)
Return New OperationResult(False, "系统错误，请稍后重试")
End Try
End Function
Public Async Function GetAllConfigsAsync(currentUserId As Integer) As Task(Of SystemConfigsResult)
Try
Dim hasAccess = Await _authorizationService.CheckOperationPermissionAsync(currentUserId, "system_config_view")
If Not hasAccess Then
Return New SystemConfigsResult(False, Nothing, "没有查看系统配置的权限")
End If
Dim configs = Await _configRepository.GetAllConfigsAsync()
Return New SystemConfigsResult(True, configs, "获取系统配置列表成功")
Catch ex As Exception
Logger.Error("获取系统配置列表失败", ex)
Return New SystemConfigsResult(False, Nothing, "系统错误，请稍后重试")
End Try
End Function
Public Async Function GetSystemStatusAsync(currentUserId As Integer) As Task(Of SystemStatusResult)
Try
Dim hasAccess = Await _authorizationService.CheckOperationPermissionAsync(currentUserId, "system_monitor")
If Not hasAccess Then
Return New SystemStatusResult(False, Nothing, "没有查看系统状态的权限")
End If
Dim status As New SystemStatus With {
.ServerStartTime = DateTime.Now.AddHours(-2),
.DatabaseStatus = Await CheckDatabaseStatusAsync(),
.ActiveSessions = Await GetActiveSessionsCountAsync(),
.MemoryUsage = GetMemoryUsage(),
.CPUUsage = GetCPUUsage(),
.DiskSpace = GetDiskSpace()
}
Return New SystemStatusResult(True, status, "获取系统状态成功")
Catch ex As Exception
Logger.Error("获取系统状态失败", ex)
Return New SystemStatusResult(False, Nothing, "系统错误，请稍后重试")
End Try
End Function
Public Async Function BackupDatabaseAsync(backupPath As String, backupBy As Integer) As Task(Of OperationResult)
Try
Dim hasAccess = Await _authorizationService.CheckOperationPermissionAsync(backupBy, "system_backup")
If Not hasAccess Then
Return New OperationResult(False, "没有备份数据库的权限")
End If
If String.IsNullOrEmpty(backupPath) Then
backupPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Backups")
End If
If Not Directory.Exists(backupPath) Then
Directory.CreateDirectory(backupPath)
End If
Dim backupFile = Path.Combine(backupPath, $"backup_{DateTime.Now:yyyyMMddHHmmss}.sql")
Dim success = Await PerformDatabaseBackupAsync(backupFile)
If success Then
Return New OperationResult(True, $"数据库备份成功: {backupFile}")
Else
Return New OperationResult(False, "数据库备份失败")
End If
Catch ex As Exception
Logger.Error("数据库备份失败", ex)
Return New OperationResult(False, "系统错误，请稍后重试")
End Try
End Function
Public Async Function CleanupOldDataAsync(daysToKeep As Integer, cleanedBy As Integer) As Task(Of OperationResult)
Try
Dim hasAccess = Await _authorizationService.CheckOperationPermissionAsync(cleanedBy, "system_cleanup")
If Not hasAccess Then
Return New OperationResult(False, "没有清理数据的权限")
End If
Dim cutoffDate = DateTime.Now.AddDays(-daysToKeep)
Using uow As New UnitOfWork()
uow.BeginTransaction()
Dim cleanupLogsSql = "DELETE FROM system_operation_log WHERE operation_time < @cutoffDate"
Dim cleanupLogsParams = {uow.GetDatabaseHelper().CreateParameter("@cutoffDate", cutoffDate)}
Await uow.GetDatabaseHelper().ExecuteNonQueryAsync(cleanupLogsSql, cleanupLogsParams)
Dim cleanupSessionsSql = "DELETE FROM user_session WHERE login_time < @cutoffDate AND is_active = 0"
Dim cleanupSessionsParams = {uow.GetDatabaseHelper().CreateParameter("@cutoffDate", cutoffDate)}
Await uow.GetDatabaseHelper().ExecuteNonQueryAsync(cleanupSessionsSql, cleanupSessionsParams)
uow.Commit()
End Using
Return New OperationResult(True, $"数据清理完成，保留{daysToKeep}天内的数据")
Catch ex As Exception
Logger.Error("数据清理失败", ex)
Return New OperationResult(False, "系统错误，请稍后重试")
End Try
End Function
Private Async Function CheckDatabaseStatusAsync() As Task(Of Boolean)
Try
Return Await DbContext.Instance.TestConnectionAsync()
Catch ex As Exception
Return False
End Try
End Function
Private Async Function GetActiveSessionsCountAsync() As Task(Of Integer)
Try
Using sessionService As New SessionManagementService()
Return Await sessionService.GetActiveSessionsCountAsync()
End Using
Catch ex As Exception
Return 0
End Try
End Function
Private Function GetMemoryUsage() As String
Try
Dim process = process.GetCurrentProcess()
Dim memoryMB = process.WorkingSet64 / 1024 / 1024
Return $"{memoryMB:F2} MB"
Catch ex As Exception
Return "N/A"
End Try
End Function
Private Function GetCPUUsage() As String
Try
Return "N/A"
Catch ex As Exception
Return "N/A"
End Try
End Function
Private Function GetDiskSpace() As String
Try
Dim drive = New DriveInfo(Path.GetPathRoot(Environment.CurrentDirectory))
Dim freeSpaceGB = drive.AvailableFreeSpace / 1024 / 1024 / 1024
Dim totalSpaceGB = drive.TotalSize / 1024 / 1024 / 1024
Return $"{freeSpaceGB:F1} GB / {totalSpaceGB:F1} GB"
Catch ex As Exception
Return "N/A"
End Try
End Function
Private Async Function PerformDatabaseBackupAsync(backupFile As String) As Task(Of Boolean)
Try
Using dbHelper As New DatabaseHelper()
Await dbHelper.OpenConnectionAsync()
Using cmd As New MySqlCommand()
cmd.Connection = dbHelper.Connection
cmd.CommandText = "SELECT * FROM sys_user"
Using reader As MySqlDataReader = Await cmd.ExecuteReaderAsync()
Using writer As New StreamWriter(backupFile)
While Await reader.ReadAsync()
Dim values As New List(Of String)()
For i As Integer = 0 To reader.FieldCount - 1
values.Add(If(reader.IsDBNull(i), "NULL", $"'{reader.GetValue(i).ToString().Replace("'", "''")}'"))
Next
writer.WriteLine($"INSERT INTO sys_user VALUES ({String.Join(", ", values)});")
End While
End Using
End Using
End Using
End Using
Return True
Catch ex As Exception
Logger.Error("执行数据库备份失败", ex)
Return False
End Try
End Function
Public Sub Dispose() Implements IDisposable.Dispose
If Not _isDisposed Then
If _configRepository IsNot Nothing Then
_configRepository.Dispose()
_configRepository = Nothing
End If
If _authorizationService IsNot Nothing Then
_authorizationService.Dispose()
_authorizationService = Nothing
End If
_isDisposed = True
End If
End Sub
End Class
Public Class SystemConfigResult
Public Property Success As Boolean
Public Property Config As SystemConfig
Public Property Message As String
Public Sub New(success As Boolean, config As SystemConfig, message As String)
Me.Success = success
Me.Config = config
Me.Message = message
End Sub
End Class
Public Class SystemConfigsResult
Public Property Success As Boolean
Public Property Configs As List(Of SystemConfig)
Public Property Message As String
Public Sub New(success As Boolean, configs As List(Of SystemConfig), message As String)
Me.Success = success
Me.Configs = configs
Me.Message = message
End Sub
End Class
Public Class SystemStatusResult
Public Property Success As Boolean
Public Property Status As SystemStatus
Public Property Message As String
Public Sub New(success As Boolean, status As SystemStatus, message As String)
Me.Success = success
Me.Status = status
Me.Message = message
End Sub
End Class
Public Class SystemConfig
Public Property ConfigId As Integer
Public Property ConfigKey As String
Public Property ConfigValue As String
Public Property ConfigType As String
Public Property Description As String
Public Property IsSystem As Boolean
Public Property CreatedTime As DateTime
Public Property UpdatedTime As DateTime
End Class
Public Class SystemStatus
Public Property ServerStartTime As DateTime
Public Property DatabaseStatus As Boolean
Public Property ActiveSessions As Integer
Public Property MemoryUsage As String
Public Property CPUUsage As String
Public Property DiskSpace As String
End Class
Public Class SystemConfigRepository
Implements IDisposable
Private _dbHelper As DatabaseHelper
Public Sub New()
_dbHelper = DbContext.Instance.GetConnection()
End Sub
Public Async Function GetConfigByKeyAsync(configKey As String) As Task(Of SystemConfig)
Try
Dim sql As String = "SELECT * FROM system_config WHERE config_key = @configKey"
Dim parameters As MySqlParameter() = {
_dbHelper.CreateParameter("@configKey", configKey)
}
Dim dataTable As DataTable = Await _dbHelper.GetDataTableAsync(sql, parameters)
If dataTable.Rows.Count > 0 Then
Return DataRowToConfig(dataTable.Rows(0))
Else
Return Nothing
End If
Catch ex As Exception
Logger.Error($"获取系统配置失败: {configKey}", ex)
Throw New DatabaseException($"获取系统配置失败", 0, $"SELECT * FROM system_config WHERE config_key = '{configKey}'", ex)
End Try
End Function
Public Async Function GetAllConfigsAsync() As Task(Of List(Of SystemConfig))
Try
Dim sql As String = "SELECT * FROM system_config ORDER BY config_key"
Dim dataTable As DataTable = Await _dbHelper.GetDataTableAsync(sql)
Return DataTableToConfigList(dataTable)
Catch ex As Exception
Logger.Error("获取所有系统配置失败", ex)
Throw New DatabaseException("获取所有系统配置失败", 0, "SELECT * FROM system_config", ex)
End Try
End Function
Public Async Function UpdateConfigAsync(config As SystemConfig) As Task(Of Boolean)
Try
Dim sql As String = "UPDATE system_config SET config_value = @configValue, updated_time = @updatedTime, updated_by = @updatedBy WHERE config_id = @configId"
Dim parameters As MySqlParameter() = {
_dbHelper.CreateParameter("@configValue", config.ConfigValue),
_dbHelper.CreateParameter("@updatedTime", config.UpdatedTime),
_dbHelper.CreateParameter("@updatedBy", config.UpdatedBy),
_dbHelper.CreateParameter("@configId", config.ConfigId)
}
Dim result As Integer = Await _dbHelper.ExecuteNonQueryAsync(sql, parameters)
Return result > 0
Catch ex As Exception
Logger.Error($"更新系统配置失败: {config.ConfigKey}", ex)
Throw New DatabaseException($"更新系统配置失败", 0, "UPDATE", ex)
End Try
End Function
Private Function DataRowToConfig(row As DataRow) As SystemConfig
Return New SystemConfig With {
.ConfigId = Convert.ToInt32(row("config_id")),
.ConfigKey = row("config_key").ToString(),
.ConfigValue = row("config_value").ToString(),
.ConfigType = row("config_type").ToString(),
.Description = If(row.IsNull("description"), String.Empty, row("description").ToString()),
.IsSystem = Convert.ToBoolean(row("is_system")),
.CreatedTime = Convert.ToDateTime(row("created_time")),
.UpdatedTime = Convert.ToDateTime(row("updated_time"))
}
End Function
Private Function DataTableToConfigList(dataTable As DataTable) As List(Of SystemConfig)
Dim configs As New List(Of SystemConfig)()
For Each row As DataRow In dataTable.Rows
configs.Add(DataRowToConfig(row))
Next
Return configs
End Function
Public Sub Dispose() Implements IDisposable.Dispose
If _dbHelper IsNot Nothing Then
DbContext.Instance.ReturnConnection(_dbHelper)
_dbHelper = Nothing
End If
End Sub
End Class 检查错误
本文深入讲解了DBHelper类的各种操作方法，包括如何获取当前会话、执行保存、更新、删除和查询操作。通过实例展示了如何使用Where条件进行精确查询，以及如何通过JoinQueryOver进行复杂关联查询。
扫一扫
1513
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
