using Microsoft.Data.SqlClient;
|
using NPOI.HSSF.Record;
|
using SkiaSharp;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
using System.Text;
|
using System.Threading.Tasks;
|
using WIDESEAWCS_Core;
|
using WIDESEAWCS_Core.BaseServices;
|
using WIDESEAWCS_ISystemRepository;
|
using WIDESEAWCS_ISystemServices;
|
using WIDESEAWCS_Model.Models;
|
|
namespace WIDESEAWCS_SystemServices
|
{
|
public class Sys_LogService : ServiceBase<Sys_Log, ISys_LogRepository>, ISys_LogService
|
{
|
private static DateTime? _lastExecutionDate = null;
|
private static DateTime? _lastFileSizeCheckDate = null;
|
private readonly object _lock = new object();
|
private readonly object _fileSizeLock = new object();
|
|
private const string DbName = "WIDESEA_HF";
|
private const string ConnectionString = "Data Source=11.2.30.112;Initial Catalog=WIDESEA_HF;User ID=kuka;Password=kuka;Integrated Security=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
|
private const long MaxMdfSizeBytes = 7L * 1024 * 1024 * 1024; // 7GB
|
private const int TargetShrinkSizeMB = 4500; // 4.5GB
|
private const double FragmentationThreshold = 30.0; // 30%碎片率阈值
|
|
public Sys_LogService(ISys_LogRepository BaseDal) : base(BaseDal)
|
{
|
}
|
|
public void DeleteOldLogs()
|
{
|
// 使用锁确保线程安全
|
lock (_lock)
|
{
|
var today = DateTime.Today;
|
|
// 检查今天是否已经执行过
|
if (_lastExecutionDate.HasValue && _lastExecutionDate.Value.Date == today)
|
{
|
return;
|
}
|
try
|
{
|
// 删除5天前的日志
|
var result = BaseDal.Db.Deleteable<Sys_Log>()
|
.Where(log => log.EndDate < DateTime.Now.AddDays(-4))
|
.ExecuteCommand();
|
// 更新最后执行日期为今天
|
_lastExecutionDate = today;
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"执行日志清理时发生错误：{ex.Message}");
|
throw new Exception($"日志清理时发生错误: {ex.Message}", ex);
|
// 发生错误时不更新最后执行日期，以便重试
|
}
|
}
|
}
|
|
// 每周星期天2点执行一次
|
public void CheckAndManageFileSize()
|
{
|
try
|
{
|
using (var connection = new SqlConnection(ConnectionString))
|
{
|
connection.Open();
|
|
// 1. 检查数据库文件大小
|
CheckDatabaseFileSize(connection);
|
|
// 2. 检查并处理索引碎片
|
CheckAndHandleIndexFragmentation(connection);
|
|
// 3. 更新统计信息
|
UpdateStatistics(connection);
|
|
// 更新最后检查日期
|
lock (_fileSizeLock)
|
{
|
_lastFileSizeCheckDate = DateTime.Now;
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"文件监控失败: {ex.Message}");
|
throw new Exception($"文件监控失败: {ex.Message}", ex);
|
}
|
}
|
|
private bool ShouldExecuteMaintenance()
|
{
|
lock (_fileSizeLock)
|
{
|
var now = DateTime.Now;
|
|
// 检查今天是否是星期天
|
if (now.DayOfWeek != DayOfWeek.Sunday)
|
{
|
return false;
|
}
|
|
// 检查今天是否已经执行过
|
if (_lastFileSizeCheckDate.HasValue)
|
{
|
// 检查是否在今天的同一小时内执行过（避免重复执行）
|
if (_lastFileSizeCheckDate.Value.Date == now.Date)
|
{
|
// 如果已经执行过，跳过
|
return false;
|
}
|
}
|
|
return true;
|
}
|
}
|
|
private void CheckDatabaseFileSize(SqlConnection connection)
|
{
|
try
|
{
|
var sizeCheckSql = @"
|
SELECT
|
name AS [文件逻辑名],
|
physical_name AS [物理路径],
|
size/128.0 AS [当前大小MB],
|
FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [已用空间MB],
|
size/128.0 - FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [可用空间MB],
|
max_size/128.0 AS [最大大小MB],
|
growth/128.0 AS [增长量MB],
|
CASE is_percent_growth
|
WHEN 1 THEN '百分比增长'
|
ELSE '固定大小增长'
|
END AS [增长方式]
|
FROM sys.database_files
|
WHERE type = 0"; // 0表示数据文件，1表示日志文件
|
|
using (var command = new SqlCommand(sizeCheckSql, connection))
|
{
|
using (var reader = command.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
string logicalName = reader["文件逻辑名"].ToString();
|
double currentSizeMB = Convert.ToDouble(reader["当前大小MB"]);
|
double usedSpaceMB = Convert.ToDouble(reader["已用空间MB"]);
|
double freeSpaceMB = Convert.ToDouble(reader["可用空间MB"]);
|
|
// 检查是否需要收缩
|
if (currentSizeMB * 1024 * 1024 > MaxMdfSizeBytes)
|
{
|
Console.WriteLine("数据库文件超过7GB，开始收缩...");
|
ShrinkDatabaseFile(connection, logicalName);
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"检查数据库文件大小时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
private void ShrinkDatabaseFile(SqlConnection connection, string logicalName)
|
{
|
try
|
{
|
// 收缩数据库文件
|
string shrinkSql = $"DBCC SHRINKFILE (N'{logicalName}', {TargetShrinkSizeMB})";
|
using (var command = new SqlCommand(shrinkSql, connection))
|
{
|
int rowsAffected = command.ExecuteNonQuery();
|
Console.WriteLine($"数据库文件收缩完成，目标大小: {TargetShrinkSizeMB} MB");
|
}
|
|
// 重新组织索引以避免收缩导致的碎片
|
ReorganizeAllIndexes(connection);
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"收缩数据库文件时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
private void CheckAndHandleIndexFragmentation(SqlConnection connection)
|
{
|
try
|
{
|
// 查询所有表的索引碎片率
|
string fragmentationSql = @"
|
SELECT
|
OBJECT_NAME(ips.object_id) AS TableName,
|
i.name AS IndexName,
|
ips.index_type_desc AS IndexType,
|
ips.avg_fragmentation_in_percent AS FragmentationPercent,
|
ips.page_count AS PageCount
|
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
|
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
|
WHERE ips.avg_fragmentation_in_percent > 0
|
AND ips.index_id > 0 -- 排除堆
|
AND ips.page_count > 100 -- 只处理超过100页的索引
|
ORDER BY ips.avg_fragmentation_in_percent DESC";
|
|
using (var command = new SqlCommand(fragmentationSql, connection))
|
using (var reader = command.ExecuteReader())
|
{
|
var indexesToReorganize = new List<(string TableName, string IndexName, double Fragmentation)>();
|
|
while (reader.Read())
|
{
|
string tableName = reader["TableName"].ToString();
|
string indexName = reader["IndexName"].ToString();
|
double fragmentation = Convert.ToDouble(reader["FragmentationPercent"]);
|
long pageCount = Convert.ToInt64(reader["PageCount"]);
|
|
Console.WriteLine($"表: {tableName}, 索引: {indexName}, 碎片率: {fragmentation:F2}%, 页数: {pageCount}");
|
|
if (fragmentation > FragmentationThreshold)
|
{
|
indexesToReorganize.Add((tableName, indexName, fragmentation));
|
}
|
}
|
|
reader.Close(); // 关闭reader以执行后续命令
|
|
// 处理需要重新组织的索引
|
if (indexesToReorganize.Any())
|
{
|
foreach (var index in indexesToReorganize)
|
{
|
ReorganizeIndex(connection, index.TableName, index.IndexName);
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"检查索引碎片时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
private void ReorganizeIndex(SqlConnection connection, string tableName, string indexName)
|
{
|
try
|
{
|
string reorganizeSql = $"ALTER INDEX [{indexName}] ON [{tableName}] REORGANIZE";
|
using (var command = new SqlCommand(reorganizeSql, connection))
|
{
|
command.CommandTimeout = 600; // 设置10分钟超时
|
command.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"重新组织索引 {tableName}.{indexName} 时出错: {ex.Message}");
|
// 继续处理其他索引
|
}
|
}
|
|
private void ReorganizeAllIndexes(SqlConnection connection)
|
{
|
try
|
{
|
string reorganizeAllSql = @"
|
DECLARE @TableName NVARCHAR(128)
|
DECLARE @IndexName NVARCHAR(128)
|
DECLARE @Sql NVARCHAR(MAX)
|
|
DECLARE IndexCursor CURSOR FOR
|
SELECT
|
OBJECT_NAME(i.object_id) AS TableName,
|
i.name AS IndexName
|
FROM sys.indexes i
|
WHERE i.index_id > 0 -- 排除堆
|
AND OBJECT_NAME(i.object_id) IS NOT NULL
|
|
OPEN IndexCursor
|
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName
|
|
WHILE @@FETCH_STATUS = 0
|
BEGIN
|
SET @Sql = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE'
|
BEGIN TRY
|
EXEC sp_executesql @Sql
|
PRINT '已重新组织索引: ' + @TableName + '.' + @IndexName
|
END TRY
|
BEGIN CATCH
|
PRINT '重新组织索引 ' + @TableName + '.' + @IndexName + ' 时出错: ' + ERROR_MESSAGE()
|
END CATCH
|
|
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName
|
END
|
|
CLOSE IndexCursor
|
DEALLOCATE IndexCursor";
|
|
using (var command = new SqlCommand(reorganizeAllSql, connection))
|
{
|
command.CommandTimeout = 1800; // 设置30分钟超时
|
command.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"重新组织所有索引时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
private void UpdateStatistics(SqlConnection connection)
|
{
|
try
|
{
|
// 更新所有表的统计信息
|
string updateStatsSql = "EXEC sp_updatestats";
|
using (var command = new SqlCommand(updateStatsSql, connection))
|
{
|
command.CommandTimeout = 600; // 设置10分钟超时
|
command.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"更新统计信息时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
// 提供一个方法供任务调度器调用
|
public void ScheduleDatabaseMaintenance()
|
{
|
try
|
{
|
// 1. 删除旧日志
|
DeleteOldLogs();
|
// 检查是否符合执行条件（每周星期天执行）
|
if (!ShouldExecuteMaintenance())
|
{
|
return;
|
}
|
// 2. 检查和管理文件大小
|
CheckAndManageFileSize();
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine($"执行数据库维护任务时出错: {ex.Message}");
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 递归获取文件信息
|
/// </summary>
|
/// <param name="dirPath"></param>
|
/// <returns></returns>
|
private DirInfo GetDirInfo(string dirPath)
|
{
|
//当前文件夹
|
var dirInfo = new DirInfo();
|
dirInfo.dirName = Path.GetFileName(dirPath);
|
|
//子文件
|
List<FileDataInfo> files = new List<FileDataInfo>();
|
foreach (var file in Directory.GetFiles(dirPath))
|
{
|
files.Add(new FileDataInfo()
|
{
|
filePath = file,
|
fileName = Path.GetFileName(file)
|
});
|
}
|
|
//子文件夹
|
var dirs = Directory.GetDirectories(dirPath);
|
dirInfo.dirs = new List<DirInfo>();
|
foreach (var dir in dirs)
|
{
|
dirInfo.dirs.Add(GetDirInfo(dir));
|
}
|
|
//子文件夹，与子目录合并
|
foreach (var file in files)
|
{
|
dirInfo.dirs.Add(new DirInfo() { dirPath = file.filePath, dirName = file.fileName });
|
}
|
return dirInfo;
|
}
|
|
/// <summary>
|
/// 获取日志文件列表
|
/// </summary>
|
/// <returns></returns>
|
public WebResponseContent GetLogList()
|
{
|
WebResponseContent content = new WebResponseContent();
|
string path = Path.Combine(AppContext.BaseDirectory, "log");
|
if (!Directory.Exists(path))
|
{
|
return content.Error("暂无日志文件");
|
}
|
|
content.Data = GetDirInfo(path);
|
content.OK();
|
return content;
|
}
|
|
/// <summary>
|
/// 获取文件内容
|
/// </summary>
|
/// <param name="parm"></param>
|
/// <returns></returns>
|
public WebResponseContent GetLogData(GetLogParm parm)
|
{
|
WebResponseContent content = new WebResponseContent();
|
string res = "";
|
//是否读取到最后
|
bool isEnd = false;
|
long startIndex = 0;
|
//文件大小
|
long len = 0;
|
try
|
{
|
if (!File.Exists(parm.path))
|
{
|
throw new Exception($"文件{parm.path}不存在！");
|
}
|
using (FileStream fs = new FileStream(parm.path, FileMode.Open, FileAccess.Read, FileShare.Read))
|
{
|
//最大读取大小
|
int maxsize = (int)(1024 * parm.maxsize_KB);
|
len = fs.Length;
|
long startPos = (long)(len * (parm.percent / 100));//起始位置
|
long readLen = len - startPos;//读取长度
|
|
//向前加载
|
if (parm.topStartPos != 0)
|
{
|
startPos = parm.topStartPos - maxsize;
|
if (startPos < 0)
|
{
|
//已读到起始位
|
startPos = 0;
|
readLen = parm.topStartPos;
|
}
|
else
|
{
|
readLen = maxsize;
|
}
|
}
|
else
|
{
|
//读取大小是否超出最大长度
|
if (readLen > maxsize)
|
{
|
readLen = maxsize;
|
}
|
else
|
{
|
isEnd = true;
|
}
|
}
|
|
//加载100%，按最大内容读取
|
if (parm.percent == 100)
|
{
|
if (len < maxsize)
|
{
|
startPos = 0;
|
readLen = len;
|
}
|
else
|
{
|
startPos = len - maxsize;
|
readLen = maxsize;
|
}
|
}
|
|
fs.Seek(startPos, SeekOrigin.Begin);
|
var buffer = new byte[readLen];
|
fs.Read(buffer, 0, (int)readLen);
|
|
startIndex = startPos;
|
if (startPos != 0 && (parm.percent != 0 || parm.topStartPos != 0))
|
{
|
//不是从头加载，删除可能不完整的第一行
|
int skipCount = 0;
|
for (int i = 0; i < buffer.Length; i++)
|
{
|
if (buffer[i] == 10)
|
{
|
skipCount = i;
|
break;
|
}
|
}
|
if (skipCount != 0)
|
{
|
//去掉换行
|
skipCount++;
|
//下次读取前延
|
startIndex += skipCount;
|
}
|
res = Encoding.UTF8.GetString(buffer.Skip(skipCount).ToArray());
|
}
|
else
|
{
|
res = Encoding.UTF8.GetString(buffer);
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
return content.Error(ex.Message);
|
}
|
return content.OK(data: new { content = res, isEnd, startIndex, len });
|
}
|
}
|
}
|
