SWRIS-HD/Repository/Record/RecordRepository.cs

using Dapper;
using SWRIS.Core;
using SWRIS.Dtos;
using SWRIS.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;

namespace SWRIS.Repository
{
    public class RecordRepository : SqLiteBaseRepository, IRecordRepository
    {
        public RecordDto GetRecord(int id)
        {
            using (var cnn = DbConnection())
            {
                cnn.Open();
                try
                {
                    RecordDto result = cnn.QueryFirstOrDefault<RecordDto>(
                         @"SELECT Id,
                               RopeNumber,
                               RopeName,
                               StartTime,
                               EndTime,
                               StartPoint,
                               EndPoint,
                               DetectionLength,
                               DetectedSpeed,
                               RiskLevel,
                               DamageCount,
                               DataFilePath,
                               SensorCount,
                               SamplingStep,
                               ExportCount,
                               InUseSensors
                          FROM Records 
                          WHERE Id = @id", new { id });

                    if (result != null)
                    {
                        var damages = cnn.Query<DamageDto>(
                            @"SELECT Id,
                                   RecordId,
                                   DamagePoint,
                                   DamageValue,
                                   DamageLevel
                              FROM Damages  
                              WHERE RecordId = @id", new { id }).ToList();

                        result.Damages.AddRange(damages);
                    }
                    return result;
                }
                catch (Exception ex)
                {
                    LogHelper.Error("执行SQL语句时出错：" + ex.Message, ex);
                }
                return null;

            }
        }
        public List<RecordDto> GetLastRecords(int lastCount = 10, bool hasDamages = false)
        {
            using (var cnn = DbConnection())
            {
                try
                {
                    cnn.Open();
                    var sql = @"SELECT Id,
                                   RopeNumber,
                                   RopeName,
                                   StartTime,
                                   EndTime,
                                   StartPoint,
                                   EndPoint,
                                   DetectionLength,
                                   DetectedSpeed,
                                   RiskLevel,
                                   DamageCount,
                                   DataFilePath,
                                   SensorCount,
                                   SamplingStep,
                                   ExportCount,
                                   InUseSensors
                                FROM Records
                                ORDER BY StartTime DESC
                                LIMIT @Limit OFFSET @Offset";

                    var result = cnn.Query<RecordDto>(sql, new
                    {
                        Limit = lastCount,
                        Offset = 0
                    });
                    if (hasDamages && result != null && result.Any())
                    {
                        foreach (var item in result)
                        {
                            var damages = cnn.Query<DamageDto>(
                                @"SELECT Id,
                                         RecordId,
                                         DamagePoint,
                                         DamageValue,
                                         DamageLevel
                                  FROM Damages 
                                  WHERE RecordId = @id", new { id = item.Id }).ToList();
                            item.Damages.AddRange(damages);
                        }
                    }
                    return result.ToList();
                }
                catch (Exception ex)
                {
                    LogHelper.Error("执行SQL语句时出错：" + ex.Message, ex);
                }
                return new List<RecordDto>();
            }
        }
        public List<RecordDto> GetRecords(int? ropeNumber,
                                          DateTime? startTime = null,
                                          DateTime? endTime = null,
                                          int? riskLevel = null,
                                          int? limit = null,
                                          int? offset = null,
                                          bool hasDamages = false)
        {
            using (var cnn = DbConnection())
            {
                try
                {
                    cnn.Open();
                    var sql = @"SELECT Id,
                                   RopeNumber,
                                   RopeName,
                                   StartTime,
                                   EndTime,
                                   StartPoint,
                                   EndPoint,
                                   DetectionLength,
                                   DetectedSpeed,
                                   RiskLevel,
                                   DamageCount,
                                   DataFilePath,
                                   SensorCount,
                                   SamplingStep,
                                   ExportCount,
                                   InUseSensors
                                FROM Records 
                                WHERE 1=1 ";
                    if (ropeNumber.HasValue)
                    {
                        sql += @"AND RopeNumber==@RopeNumber ";
                    }
                    if (riskLevel.HasValue)
                    {
                        sql += @"AND RiskLevel==@RiskLevel ";
                    }
                    if (startTime.HasValue)
                    {
                        sql += @"AND StartTime>=@StartTime ";
                    }
                    if (endTime.HasValue)
                    {
                        sql += @"AND EndTime<=@EndTime ";
                    }
                    sql += @"ORDER BY StartTime DESC ";
                    if (limit.HasValue && offset.HasValue)
                    {
                        sql += @"LIMIT @Limit OFFSET @Offset ";
                    }
                    var result = cnn.Query<RecordDto>(sql, new
                    {
                        Limit = limit,
                        Offset = offset,
                        StartTime = startTime,
                        EndTime = endTime,
                        RopeNumber = ropeNumber,
                        RiskLevel = riskLevel,
                    });
                    if (hasDamages && result != null && result.Any())
                    {
                        foreach (var item in result)
                        {
                            var damages = cnn.Query<DamageDto>(
                                @"SELECT Id,
                                         RecordId,
                                         DamagePoint,
                                         DamageValue,
                                         DamageLevel
                                  FROM Damages 
                                  WHERE RecordId = @id", new { id = item.Id }).ToList();
                            item.Damages.AddRange(damages);
                        }
                    }
                    return result.ToList();
                }
                catch (Exception ex)
                {
                    LogHelper.Error("执行SQL语句时出错：" + ex.Message, ex);
                }
                return new List<RecordDto>();
            }
        }

        public int? CreateRecord(RecordDto record)
        {
            using (var cnn = DbConnection())
            {
                try
                {
                    cnn.Open();
                    record.Id = cnn.ExecuteScalar<int>(
                        @"INSERT INTO Records (
                             RopeNumber,
                             RopeName,
                             StartTime,
                             EndTime,
                             StartPoint,
                             EndPoint,
                             DetectionLength,
                             DetectedSpeed,
                             DamageCount,
                             SensorCount,
                             SamplingStep,
                             DataFilePath,
                             RiskLevel,
                             ExportCount,
                             InUseSensors)
                          VALUES (
                            @RopeNumber,
                            @RopeName,
                            @StartTime,
                            @EndTime,
                            @StartPoint,
                            @EndPoint,
                            @DetectionLength,
                            @DetectedSpeed,
                            @DamageCount,
                            @SensorCount,
                            @SamplingStep,
                            @DataFilePath,
                            @RiskLevel,
                            @ExportCount,
                            @InUseSensors);
                         SELECT LAST_INSERT_ROWID()", record);
                    if (record?.Id > 0)
                    {
                        if (record.Damages != null && record.Damages.Any())
                        {
                            foreach (var d in record.Damages)
                            {
                                d.RecordId = record.Id.Value;
                            }
                            cnn.Execute(@"INSERT INTO Damages (
                                                RecordId,
                                                DamagePoint,
                                                DamageValue,
                                                DamageLevel)
                                          VALUES (
                                                @RecordId,
                                                @DamagePoint,
                                                @DamageValue,
                                                @DamageLevel)", record.Damages);
                        }
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.Error("执行SQL语句时出错：" + ex.Message, ex);
                    return null;
                }
                return record.Id;
            }
        }
        public bool UpdateRecord(RecordDto record)
        {
            using (var cnn = DbConnection())
            {
                try
                {
                    cnn.Open();
                    int affected = cnn.Execute(
                         @"UPDATE Records
                               SET 
                                   RopeNumber = @RopeNumber,
                                   RopeName = @RopeName,
                                   StartTime = @StartTime,
                                   EndTime = @EndTime,
                                   StartPoint = @StartPoint,
                                   EndPoint = @EndPoint,
                                   DetectionLength = @DetectionLength,
                                   DetectedSpeed = @DetectedSpeed,
                                   DamageCount = @DamageCount,
                                   DataFilePath = @DataFilePath,
                                   SensorCount = @SensorCount,
                                   SamplingStep = @SamplingStep,
                                   ExportCount = @ExportCount,
                                   RiskLevel=@RiskLevel,
                                   InUseSensors=@InUseSensors
                           WHERE Id = @Id;", record);

                    foreach (var item in record.Damages)
                    {
                        affected += cnn.Execute(
                          @"UPDATE Damages
                               SET RecordId = @RecordId,
                                   DamagePoint =@DamagePoint,
                                   DamageValue =@DamageValue,
                                   DamageLevel =@DamageLevel 
                               WHERE Id = @Id;", item);
                    }
                    return affected > 0;
                }
                catch (Exception ex)
                {
                    LogHelper.Error("执行SQL语句时出错：" + ex.Message, ex);
                    return false;
                }
            }
        }
        public bool UpdateRecordDamageCount(int id, int damageCount)
        {
            using (var cnn = DbConnection())
            {
                cnn.Open();
                return cnn.Execute(@"UPDATE Records Set DamageCount=@DamageCount WHERE Id = @Id;", new { Id = id, DamageCount = damageCount }) > 0;
            }
        }
        public bool AddRecordExportCount(int id)
        {
            using (var cnn = DbConnection())
            {
                cnn.Open();
                return cnn.Execute(@"UPDATE Records Set ExportCount=ExportCount+1 WHERE Id = @Id;", new { Id = id }) > 0;
            }
        }
        public bool DeleteRecord(int id)
        {
            using (var cnn = DbConnection())
            {
                cnn.Open();
                return cnn.Execute(@"DELETE FROM Damages WHERE RecordId = @Id;DELETE FROM Records WHERE Id = @Id;", new { Id = id }) > 0;
            }
        }
        public int DeleteRecords(int[] ids)
        {
            using (var cnn = DbConnection())
            {
                cnn.Open();
                return cnn.Execute(@"DELETE FROM Damages WHERE RecordId in @Ids;DELETE FROM Records WHERE Id in @Ids;", new { Ids = ids });
            }
        }
        public (int, List<string>) DeleteRecords(DateTime endTime)
        {
            using (var cnn = DbConnection())
            {
                cnn.Open();

                // 首先查询需要删除的记录的DataFilePath
                var filePaths = cnn.Query<string>(
                    @"SELECT DataFilePath 
                        FROM Records
                        WHERE StartTime <= @EndTime AND ExportCount = 0",
                    new { EndTime = endTime }).ToList();

                // 执行删除操作
                int effect = cnn.Execute(@"
                      BEGIN TRANSACTION;
                          DELETE FROM Damages 
                          WHERE RecordId IN (
                              SELECT Id 
                              FROM Records 
                              WHERE StartTime <= @EndTime AND ExportCount = 0
                          );
                          DELETE FROM Records 
                          WHERE StartTime <= @EndTime AND ExportCount = 0;
                      COMMIT;",
                  new { EndTime = endTime });

                return (effect / 2, filePaths);
            }
        }
        public bool UpdateDamage(DamageDto damage)
        {
            using (var cnn = DbConnection())
            {
                cnn.Open();
                return cnn.Execute(@"UPDATE Damages
                                       SET RecordId = @RecordId,
                                           DamagePoint = @DamagePoint,
                                           DamageValue = @DamageValue,
                                           DamageLevel = @DamageLevel 
                                       WHERE Id = @Id;", damage) > 0;
            }
        }
        public bool DeleteDamage(int id)
        {
            using (var cnn = DbConnection())
            {
                cnn.Open();
                return cnn.Execute(@"DELETE FROM Damages WHERE Id = @Id;", new { Id = id }) > 0;
            }
        }

    }
}