1

xiazhengtongxue

2026-03-27 5d55a31d8ce95e511ffb408f38ed06e81742b67e

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

@@ -1,128 +1,4 @@
//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.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 readonly object _lock = new object();
//        private const string DbName = "WIDESEA_HF";
//        private const long MaxMdfSizeBytes = 8L * 1024 * 1024 * 1024; // 8GB
//        private const int TargetShrinkSizeMB = 4500; // 4.5GB

//        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
//                {
//                    // è®¡ç®—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个月前的日志 æˆ– (3天前且URL为特定地址的日志)
//                    var result = BaseDal.Db.Deleteable<Sys_Log>()
//                        .Where(log => log.EndDate < threeMonthsAgo ||
//                                     (log.EndDate < sevenDaysAgo && log.Url == specificUrl))
//                        .ExecuteCommand();
//                    // æ›´æ–°æœ€åŽæ‰§è¡Œæ—¥æœŸä¸ºä»Šå¤©
//                    _lastExecutionDate = today;
//                }
//                catch (Exception ex)
//                {
//                    Console.WriteLine($"执行日志清理时发生错误：{ex.Message}");
//                    // å‘生错误时不更新最后执行日期，以便重试
//                }
//            }
//        }
//        // 15天执行一次星期天2点
//        public void CheckAndManageFileSize()
//        {
//            try
//            {

//                //                --检查数据库文件空间使用情况
//                //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],
//                //    growth / 128 AS[增长量MB],
//                //    CASE is_percent_growth
//                //        WHEN 1 THEN CAST(growth AS VARCHAR) +'%'
//                //        ELSE CAST(growth/ 128 AS VARCHAR) +'MB'
//                //    END AS[增长方式]
//                //FROM sys.database_files;
//                using (var connection = new SqlConnection("Data Source=.;Initial Catalog=WIDESEA_HF;User ID=sa;Password=123456;Integrated Security=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"))
//                {
//                    connection.Open();

//                    // æ£€æŸ¥æ•°æ®åº“文件大小
//                    var sizeCheckSql = @"SELECT name AS [文件逻辑名], size/128.0 AS [当前大小MB], FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [已用空间MB] FROM sys.database_files WHERE name = @dbName";

//                    using (var command = new SqlCommand(sizeCheckSql, connection))
//                    {
//                        command.Parameters.AddWithValue("@dbName", DbName);
//                        using (var reader = command.ExecuteReader())
//                        {
//                            if (reader.Read())
//                            {
//                                double currentSizeMB = Convert.ToDouble(reader["当前大小MB"]);
//                                double usedSpaceMB = Convert.ToDouble(reader["已用空间MB"]);

//                                if (currentSizeMB > MaxMdfSizeBytes / (1024 * 1024))
//                                {
//                                    // æ‰§è¡Œæ”¶ç¼©æ•°æ®åº“

//                                }else
//                                {
//                                    // æ•´ç†ç¢Žç‰‡>30%

//                                }
//                            }
//                        }
//                    }
//                }
//            }
//            catch (Exception ex)
//            {
//                throw new Exception($"文件监控失败: {ex.Message}");
//            }
//        }


//    }
//}
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient;
using NPOI.HSSF.Record;
using SkiaSharp;
using System;
@@ -143,44 +19,17 @@
    {
        private static DateTime? _lastExecutionDate = null;
        private static DateTime? _lastFileSizeCheckDate = null;
        private static DateTime? _lastBackupDate = null;
        private readonly object _lock = new object();
        private readonly object _fileSizeLock = new object();
        private readonly object _backupLock = 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%碎片率阈值
        private const int MaintenanceIntervalDays = 15; // 15天执行一次
        private const int BackupRetentionDays = 30; // å¤‡ä»½ä¿ç•™30天
        private static readonly string BackupBasePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DatabaseBackups");  // å¤‡ä»½æ–‡ä»¶å­˜å‚¨ç›®å½•
        private const bool UseBackupCompression = true; // æ˜¯å¦ä½¿ç”¨å¤‡ä»½åŽ‹ç¼©
        private const bool VerifyBackup = true; // æ˜¯å¦éªŒè¯å¤‡ä»½
        private const int MaxBackupRetryCount = 3; // æœ€å¤§é‡è¯•æ¬¡æ•°
        private const int BackupTimeoutSeconds = 7200; // å¤‡ä»½è¶…时时间（秒）

        public Sys_LogService(ISys_LogRepository BaseDal) : base(BaseDal)
        {
            // ç¡®ä¿å¤‡ä»½ç›®å½•å­˜åœ¨
            //EnsureBackupDirectory();
        }

        private void EnsureBackupDirectory()
        {
            try
            {
                if (!Directory.Exists(BackupBasePath))
                {
                    Directory.CreateDirectory(BackupBasePath);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建备份目录失败: {ex.Message}");
                throw;
            }
        }

        public void DeleteOldLogs()
@@ -197,20 +46,10 @@
                }
                try
                {
                    var result = BaseDal.Db.Deleteable<Sys_Log>().ExecuteCommand();
                    //// è®¡ç®—3个月前的日期
                    //var threeMonthsAgo = DateTime.Now.AddMonths(-3);
                    //// è®¡ç®—3天前的日期
                    //var threeDaysAgo = DateTime.Now.AddDays(-3);
                    //// ç‰¹å®šURL
                    //var specificUrl = "http://11.2.30.141:10870/interfaces/api/amr/robotQuery";
                    //var anonUrl = "http://11.2.30.111/anon/api/kuka/drivestatus";

                    //// æž„建删除条件：3个月前的日志 æˆ– (3天前且URL为特定地址的日志)
                    //var result = BaseDal.Db.Deleteable<Sys_Log>()
                    //    .Where(log => log.EndDate < threeMonthsAgo ||
                    //                 (log.EndDate < threeDaysAgo && (log.Url == specificUrl || log.Url == anonUrl)))
                    //    .ExecuteCommand();
                    // åˆ é™¤5天前的日志
                    var result = BaseDal.Db.Deleteable<Sys_Log>()
                        .Where(log => log.EndDate < DateTime.Now.AddDays(-4))
                        .ExecuteCommand();
                    // æ›´æ–°æœ€åŽæ‰§è¡Œæ—¥æœŸä¸ºä»Šå¤©
                    _lastExecutionDate = today;
                }
@@ -223,35 +62,23 @@
            }
        }

        // 15天执行一次星期天2点
        // æ¯å‘¨æ˜ŸæœŸå¤©2点执行一次
        public void CheckAndManageFileSize()
        {
            // æ£€æŸ¥æ˜¯å¦ç¬¦åˆæ‰§è¡Œæ¡ä»¶ï¼šæ˜ŸæœŸå¤©ã€2点左右、距离上次执行至少15天
            //if (!ShouldExecuteMaintenance())
            //{
            //    return;
            //}

            try
            {
                using (var connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();

                    // 1. å¤‡ä»½æ•°æ®åº“
                    //BackupDatabase();

                    // 2. æ£€æŸ¥æ•°æ®åº“文件大小
                    // 1. æ£€æŸ¥æ•°æ®åº“文件大小
                    CheckDatabaseFileSize(connection);

                    // 3. æ£€æŸ¥å¹¶å¤„理索引碎片
                    // 2. æ£€æŸ¥å¹¶å¤„理索引碎片
                    CheckAndHandleIndexFragmentation(connection);

                    // 4. æ›´æ–°ç»Ÿè®¡ä¿¡æ¯
                    // 3. æ›´æ–°ç»Ÿè®¡ä¿¡æ¯
                    UpdateStatistics(connection);

                    // 5. æ¸…理旧备份
                    //CleanupOldBackups();

                    // æ›´æ–°æœ€åŽæ£€æŸ¥æ—¥æœŸ
                    lock (_fileSizeLock)
@@ -267,205 +94,6 @@
            }
        }

        public void BackupDatabase()
        {
            lock (_backupLock)
            {
                try
                {
                    var today = DateTime.Today;
                    // æ£€æŸ¥ä»Šå¤©æ˜¯å¦å·²ç»å¤‡ä»½è¿‡
                    if (_lastBackupDate.HasValue && _lastBackupDate.Value.Date == today)
                    {
                        return;
                    }
                    var timestamp = DateTime.Now.ToString("yyyyMMdd_HHmmss");
                    var backupFileName = $"{DbName}_Full_{timestamp}.bak";
                    var backupFilePath = Path.Combine(BackupBasePath, backupFileName);

                    using (var connection = new SqlConnection(ConnectionString))
                    {
                        connection.Open();
                        // èŽ·å–数据库文件信息，计算备份文件大小
                        var dbSizeQuery = @"
                            SELECT 
                                SUM(size/128.0) AS TotalSizeMB,
                                SUM(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS UsedSizeMB,
                                COUNT(*) AS FileCount
                            FROM sys.database_files
                            WHERE type IN (0, 1)"; // æ•°æ®æ–‡ä»¶å’Œæ—¥å¿—文件

                        double totalSizeMB = 0;
                        double usedSizeMB = 0;
                        int fileCount = 0;

                        using (var cmd = new SqlCommand(dbSizeQuery, connection))
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                totalSizeMB = Convert.ToDouble(reader["TotalSizeMB"]);
                                usedSizeMB = Convert.ToDouble(reader["UsedSizeMB"]);
                                fileCount = Convert.ToInt32(reader["FileCount"]);
                            }
                        }
                        // æž„建备份SQL语句
                        var backupSql = BuildBackupSql(backupFilePath);

                        // æ‰§è¡Œå¤‡ä»½ï¼ˆå¸¦é‡è¯•æœºåˆ¶ï¼‰
                        ExecuteBackupWithRetry(connection, backupSql, backupFilePath);

                        // éªŒè¯å¤‡ä»½æ–‡ä»¶
                        if (VerifyBackup)
                        {
                            VerifyBackupFile(connection, backupFilePath);
                        }

                        // æ›´æ–°æœ€åŽå¤‡ä»½æ—¥æœŸ
                        _lastBackupDate = DateTime.Now;

                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"数据库备份失败: {ex.Message}");
                    throw new Exception($"数据库备份失败: {ex.Message}", ex);
                }
            }
        }

        private string BuildBackupSql(string backupFilePath)
        {
            var compressionOption = UseBackupCompression ? "COMPRESSION" : "NO_COMPRESSION";
            var verifyOption = VerifyBackup ? ", CHECKSUM" : "";

            return $@"
                BACKUP DATABASE [{DbName}]
                TO DISK = '{backupFilePath.Replace("'", "''")}'
                WITH {compressionOption}
                     {verifyOption}
                     , STATS = 5
                     , DESCRIPTION = 'Full database backup created by automated maintenance on {DateTime.Now:yyyy-MM-dd HH:mm:ss}'
                     , MAXTRANSFERSIZE = 4194304
                     , BLOCKSIZE = 65536
                     , BUFFERCOUNT = 10";
        }

        private void ExecuteBackupWithRetry(SqlConnection connection, string backupSql, string backupFilePath)
        {
            int retryCount = 0;
            while (retryCount < MaxBackupRetryCount)
            {
                try
                {

                    using (var cmd = new SqlCommand(backupSql, connection))
                    {
                        cmd.CommandTimeout = BackupTimeoutSeconds;

                        var rowsAffected = cmd.ExecuteNonQuery();
                        return; // æˆåŠŸåˆ™é€€å‡º
                    }
                }
                catch (Exception ex)
                {
                    retryCount++;

                    if (retryCount >= MaxBackupRetryCount)
                    {
                        // åˆ é™¤å¯èƒ½æŸåçš„备份文件
                        try
                        {
                            if (File.Exists(backupFilePath))
                            {
                                File.Delete(backupFilePath);
                            }
                        }
                        catch { }

                        throw new Exception($"备份失败，已达到最大重试次数({MaxBackupRetryCount}次): {ex.Message}", ex);
                    }

                    Console.WriteLine($"备份失败，{MaxBackupRetryCount - retryCount}次重试机会: {ex.Message}");
                    Thread.Sleep(5000); // ç­‰å¾…5秒后重试
                }
            }
        }

        private void VerifyBackupFile(SqlConnection connection, string backupFilePath)
        {
            try
            {

                var verifySql = $@"
                    RESTORE VERIFYONLY 
                    FROM DISK = '{backupFilePath.Replace("'", "''")}'
                    WITH CHECKSUM";

                using (var cmd = new SqlCommand(verifySql, connection))
                {
                    cmd.CommandTimeout = 300; // 5分钟超时
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                // éªŒè¯å¤±è´¥æ—¶åˆ é™¤å¤‡ä»½æ–‡ä»¶
                try
                {
                    if (File.Exists(backupFilePath))
                    {
                        File.Delete(backupFilePath);
                    }
                }
                catch { }

                throw new Exception($"备份文件验证失败: {ex.Message}", ex);
            }
        }

        private void CleanupOldBackups()
        {
            try
            {

                if (!Directory.Exists(BackupBasePath))
                {
                    Console.WriteLine("备份目录不存在，跳过清理");
                    return;
                }

                var cutoffDate = DateTime.Now.AddDays(-BackupRetentionDays);
                var backupFiles = Directory.GetFiles(BackupBasePath, $"{DbName}_Full_*.bak");
                int deletedCount = 0;

                foreach (var file in backupFiles)
                {
                    try
                    {
                        var fileInfo = new FileInfo(file);
                        if (fileInfo.CreationTime < cutoffDate)
                        {
                            fileInfo.Delete();
                            deletedCount++;
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"删除备份文件 {file} æ—¶å‡ºé”™: {ex.Message}");
                        // ç»§ç»­å¤„理其他文件
                    }
                }


            }
            catch (Exception ex)
            {
                Console.WriteLine($"清理旧备份时出错: {ex.Message}");
                // ä¸æŠ›å‡ºå¼‚常，避免影响主流程
            }
        }

        private bool ShouldExecuteMaintenance()
        {
            lock (_fileSizeLock)
@@ -477,19 +105,14 @@
                {
                    return false;
                }
                // æ£€æŸ¥æ—¶é—´æ˜¯å¦æ˜¯1点左右（允许1:00-1:59）
                if (now.Hour != 1 && now.Hour != 2)
                {
                    return false;
                }

                // æ£€æŸ¥æ˜¯å¦å·²ç»æ‰§è¡Œè¿‡
                // æ£€æŸ¥ä»Šå¤©æ˜¯å¦å·²ç»æ‰§è¡Œè¿‡
                if (_lastFileSizeCheckDate.HasValue)
                {
                    // æ£€æŸ¥è·ç¦»ä¸Šæ¬¡æ‰§è¡Œæ˜¯å¦æ»¡è¶³15天间隔
                    var daysSinceLastCheck = (now - _lastFileSizeCheckDate.Value).TotalDays;
                    if (daysSinceLastCheck < MaintenanceIntervalDays)
                    // æ£€æŸ¥æ˜¯å¦åœ¨ä»Šå¤©çš„同一小时内执行过（避免重复执行）
                    if (_lastFileSizeCheckDate.Value.Date == now.Date)
                    {
                        // å¦‚果已经执行过，跳过
                        return false;
                    }
                }
@@ -529,11 +152,10 @@
                            double usedSpaceMB = Convert.ToDouble(reader["已用空间MB"]);
                            double freeSpaceMB = Convert.ToDouble(reader["可用空间MB"]);


                            // æ£€æŸ¥æ˜¯å¦éœ€è¦æ”¶ç¼©
                            if (currentSizeMB * 1024 * 1024 > MaxMdfSizeBytes)
                            {
                                Console.WriteLine("数据库文件超过8GB，开始收缩...");
                                Console.WriteLine("数据库文件超过7GB，开始收缩...");
                                ShrinkDatabaseFile(connection, logicalName);
                            }
                        }
@@ -551,7 +173,6 @@
        {
            try
            {

                // æ”¶ç¼©æ•°æ®åº“文件
                string shrinkSql = $"DBCC SHRINKFILE (N'{logicalName}', {TargetShrinkSizeMB})";
                using (var command = new SqlCommand(shrinkSql, connection))
@@ -574,7 +195,6 @@
        {
            try
            {

                // æŸ¥è¯¢æ‰€æœ‰è¡¨çš„索引碎片率
                string fragmentationSql = @"
                    SELECT 
@@ -615,7 +235,6 @@
                    // å¤„理需要重新组织的索引
                    if (indexesToReorganize.Any())
                    {

                        foreach (var index in indexesToReorganize)
                        {
                            ReorganizeIndex(connection, index.TableName, index.IndexName);
@@ -697,6 +316,7 @@
                throw;
            }
        }

        private void UpdateStatistics(SqlConnection connection)
        {
            try
@@ -715,6 +335,7 @@
                throw;
            }
        }

        // æä¾›ä¸€ä¸ªæ–¹æ³•ä¾›ä»»åŠ¡è°ƒåº¦å™¨è°ƒç”¨
        public void ScheduleDatabaseMaintenance()
        {
@@ -722,7 +343,7 @@
            {
                // 1. åˆ é™¤æ—§æ—¥å¿—
                DeleteOldLogs();
                // æ£€æŸ¥æ˜¯å¦ç¬¦åˆæ‰§è¡Œæ¡ä»¶
                // æ£€æŸ¥æ˜¯å¦ç¬¦åˆæ‰§è¡Œæ¡ä»¶ï¼ˆæ¯å‘¨æ˜ŸæœŸå¤©æ‰§è¡Œï¼‰
                if (!ShouldExecuteMaintenance())
                {
                    return;
@@ -736,9 +357,6 @@
                throw;
            }
        }




            /// <summary>
            /// é€’归获取文件信息
@@ -906,5 +524,4 @@
                return content.OK(data: new { content = res, isEnd, startIndex, len });
            }
        }

}