From 5d55a31d8ce95e511ffb408f38ed06e81742b67e Mon Sep 17 00:00:00 2001
From: xiazhengtongxue <133085197+xiazhengtongxue@users.noreply.github.com>
Date: 星期五, 27 三月 2026 16:17:30 +0800
Subject: [PATCH] 1
---
项目代码/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs | 489 +++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 477 insertions(+), 12 deletions(-)
diff --git "a/\351\241\271\347\233\256\344\273\243\347\240\201/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs" "b/\351\241\271\347\233\256\344\273\243\347\240\201/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs"
index 5dc5329..21978b3 100644
--- "a/\351\241\271\347\233\256\344\273\243\347\240\201/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs"
+++ "b/\351\241\271\347\233\256\344\273\243\347\240\201/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs"
@@ -1,10 +1,13 @@
-锘縰sing NPOI.HSSF.Record;
+锘縰sing 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;
@@ -15,11 +18,18 @@
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()
@@ -36,17 +46,9 @@
}
try
{
- // 璁＄畻3涓湀鍓嶇殑鏃ユ湡
- var threeMonthsAgo = DateTime.Now.AddMonths(-3);
- // 璁＄畻3澶╁墠鐨勬棩鏈�
- var sevenDaysAgo = DateTime.Now.AddDays(-3);
- // 鐗瑰畾URL
- var specificUrl = "http://11.2.30.141:10870/interfaces/api/amr/robotQuery";
-
- // 鏋勫缓鍒犻櫎鏉′欢锛�3涓湀鍓嶇殑鏃ュ織 鎴� (7澶╁墠涓擴RL涓虹壒瀹氬湴鍧�鐨勬棩蹇�)
+ // 鍒犻櫎5澶╁墠鐨勬棩蹇�
var result = BaseDal.Db.Deleteable<Sys_Log>()
- .Where(log => log.EndDate < threeMonthsAgo ||
- (log.EndDate < sevenDaysAgo && log.Url == specificUrl))
+ .Where(log => log.EndDate < DateTime.Now.AddDays(-4))
.ExecuteCommand();
// 鏇存柊鏈�鍚庢墽琛屾棩鏈熶负浠婂ぉ
_lastExecutionDate = today;
@@ -54,9 +56,472 @@
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 [鏈�澶уぇ灏廙B],
+ growth/128.0 AS [澧為暱閲廙B],
+ 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 });
+ }
}
}
\ No newline at end of file
--
Gitblit v1.9.3