1

xiazhengtongxue

2026-03-27 5d55a31d8ce95e511ffb408f38ed06e81742b67e

 ÏîÄ¿´úÂë/WCSServices/WIDESEAWCS_SystemServices/Sys_LogService.cs

@@ -1,10 +1,13 @@
using NPOI.HSSF.Record;
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;
@@ -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天前且URL为特定地址的日志)
                    // åˆ é™¤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 [最大大小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 });
        }
    }
}