LabelChange-server/Bak/DAL/repositories/LabelScanRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using MDL.Models;
using DAL.Interfaces;
using DB.Database;
using System;
using SqlSugar;

namespace DAL.Repositories
{
    /// <summary>
    /// 标签历史扫描记录仓储实现类
    /// </summary>
    public class LabelScanRepository : ILabelScanRepository
    {
        private readonly ISqlSugarProvider _provider;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="provider">SqlSugar提供程序</param>
        public LabelScanRepository(ISqlSugarProvider provider)
        {
            _provider = provider;
        }

        /// <summary>
        /// 创建标签扫描记录
        /// </summary>
        /// <param name="entity">标签扫描记录实体</param>
        /// <returns>创建的记录ID</returns>
        public async Task<int> CreateAsync(LabelScanEntity entity)
        {
            var db = _provider.GetClient();

            // 确保表存在
            if (!db.DbMaintenance.IsAnyTable("label_scan_history"))
            {
                db.CodeFirst.InitTables(typeof(LabelScanEntity));
            }

            // 设置时间戳
            entity.CreatedAt = DateTime.UtcNow;
            entity.UpdatedAt = DateTime.UtcNow;

            // 插入数据并返回自增ID
            return await db.Insertable(entity).ExecuteReturnIdentityAsync();
        }

        /// <summary>
        /// 根据中性面单单号获取标签扫描记录列表
        /// </summary>
        /// <param name="neutralWaybillNumber">中性面单单号</param>
        /// <returns>标签扫描记录列表</returns>
        public async Task<List<LabelScanEntity>> GetByNeutralWaybillNumberAsync(string neutralWaybillNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .Where(x => x.NeutralWaybillNumber == neutralWaybillNumber)
                .OrderBy(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 根据多个中性面单单号获取标签扫描记录列表
        /// </summary>
        /// <param name="neutralWaybillNumbers">中性面单单号列表</param>
        /// <returns>标签扫描记录列表</returns>
        public async Task<List<LabelScanEntity>> GetByNeutralWaybillNumbersAsync(List<string> neutralWaybillNumbers)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .Where(x => neutralWaybillNumbers.Contains(x.NeutralWaybillNumber))
                .OrderBy(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 根据参考号获取标签扫描记录列表
        /// </summary>
        /// <param name="referenceNumber">参考号</param>
        /// <returns>标签扫描记录列表</returns>
        public async Task<List<LabelScanEntity>> GetByReferenceNumberAsync(string referenceNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .Where(x => x.ReferenceNumber == referenceNumber)
                .OrderBy(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 根据尾程跟踪单号获取标签扫描记录列表
        /// </summary>
        /// <param name="finalMileTrackingNumber">尾程跟踪单号</param>
        /// <returns>标签扫描记录列表</returns>
        public async Task<List<LabelScanEntity>> GetByFinalMileTrackingNumberAsync(string finalMileTrackingNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .Where(x => x.FinalMileTrackingNumber == finalMileTrackingNumber)
                .OrderBy(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 根据客户ID获取标签扫描记录列表
        /// </summary>
        /// <param name="customerId">客户ID</param>
        /// <returns>标签扫描记录列表</returns>
        public async Task<List<LabelScanEntity>> GetByCustomerIdAsync(int customerId)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .Where(x => x.CustomerId == customerId)
                .OrderBy(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 根据客户ID和中性面单单号获取标签扫描记录列表
        /// </summary>
        /// <param name="customerId">客户ID</param>
        /// <param name="neutralWaybillNumber">中性面单单号</param>
        /// <returns>标签扫描记录列表</returns>
        public async Task<List<LabelScanEntity>> GetByCustomerIdAndWaybillNumberAsync(int customerId, string neutralWaybillNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .Where(x => x.CustomerId == customerId && x.NeutralWaybillNumber == neutralWaybillNumber)
                .OrderBy(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 统计客户的订单扫描次数
        /// </summary>
        /// <param name="customerId">客户ID</param>
        /// <param name="neutralWaybillNumber">中性面单单号（可选，不提供则统计所有订单）</param>
        /// <returns>扫描次数</returns>
        public async Task<int> CountScansByCustomerAndWaybillAsync(int customerId, string neutralWaybillNumber = null)
        {
            var db = _provider.GetClient();
            var query = db.Queryable<LabelScanEntity>().Where(x => x.CustomerId == customerId);
            
            if (!string.IsNullOrEmpty(neutralWaybillNumber))
            {
                query = query.Where(x => x.NeutralWaybillNumber == neutralWaybillNumber);
            }
            
            return await query.CountAsync();
        }

        /// <summary>
        /// 获取客户的扫描记录分组统计
        /// </summary>
        /// <param name="customerId">客户ID</param>
        /// <returns>按订单分组的扫描统计信息</returns>
        public async Task<Dictionary<string, int>> GetScanStatsByCustomerAsync(int customerId)
        {
            var db = _provider.GetClient();
            
            var result = await db.Queryable<LabelScanEntity>()
                .Where(x => x.CustomerId == customerId)
                .GroupBy(x => x.NeutralWaybillNumber)
                .Select(x => new { 
                    WaybillNumber = x.NeutralWaybillNumber,
                    Count = SqlFunc.AggregateCount(x.Id) 
                })
                .ToListAsync();
            
            return result.ToDictionary(item => item.WaybillNumber, item => item.Count);
        }

        /// <summary>
        /// 更新标签扫描记录
        /// </summary>
        /// <param name="entity">标签扫描记录实体</param>
        /// <returns>是否更新成功</returns>
        public async Task<bool> UpdateAsync(LabelScanEntity entity)
        {
            var db = _provider.GetClient();
            entity.UpdatedAt = DateTime.UtcNow;
            return await db.Updateable(entity).ExecuteCommandAsync() > 0;
        }

        /// <summary>
        /// 获取所有标签扫描记录
        /// </summary>
        /// <returns>标签扫描记录实体列表</returns>
        public async Task<List<LabelScanEntity>> GetAllAsync()
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .OrderByDescending(x => x.CreatedAt)
                .ToListAsync();
        }

        /// <summary>
        /// 分页获取标签扫描记录
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="sortBy">排序字段</param>
        /// <param name="sortOrder">排序顺序</param>
        /// <param name="customerId">客户ID</param>
        /// <param name="referenceNumber">参考号</param>
        /// <param name="neutralWaybillNumber">中性面单单号</param>
        /// <param name="finalMileTrackingNumber">尾程跟踪单号</param>
        /// <param name="result">扫描结果</param>
        /// <returns>标签扫描记录DTO列表和总记录数</returns>
        public async Task<(List<MDL.DTOs.LabelScanWithCustomerDto>, int)> GetByPageAsync(int page, int pageSize, string sortBy, string sortOrder, int? customerId, string referenceNumber, string neutralWaybillNumber, string finalMileTrackingNumber, int? result, string startCreatedAt, string endCreatedAt)
        {
            var db = _provider.GetClient();
            var query = db.Queryable<LabelScanEntity>();

            // 应用筛选条件
            if (customerId.HasValue)
            {
                query = query.Where(x => x.CustomerId == customerId);
            }
            if (!string.IsNullOrEmpty(referenceNumber))
            {
                query = query.Where(x => x.ReferenceNumber.Contains(referenceNumber));
            }
            if (!string.IsNullOrEmpty(neutralWaybillNumber))
            {
                // 处理多个单号的情况，支持逗号分隔
                var waybillNumbers = neutralWaybillNumber.Split(new[] { ',', '\n', '\r', ' ' }, StringSplitOptions.RemoveEmptyEntries);
                if (waybillNumbers.Length > 1)
                {
                    query = query.Where(x => waybillNumbers.Contains(x.NeutralWaybillNumber));
                }
                else if (waybillNumbers.Length == 1)
                {
                    query = query.Where(x => x.NeutralWaybillNumber.Contains(waybillNumbers[0]));
                }
            }
            if (!string.IsNullOrEmpty(finalMileTrackingNumber))
            {
                query = query.Where(x => x.FinalMileTrackingNumber.Contains(finalMileTrackingNumber));
            }
            if (result.HasValue)
            {
                query = query.Where(x => (int)x.Result == result);
            }

            // 应用时间筛选条件
            if (!string.IsNullOrEmpty(startCreatedAt) && DateTime.TryParse(startCreatedAt, out DateTime startCreatedAtDate))
            {
                query = query.Where(x => x.CreatedAt >= startCreatedAtDate);
            }
            if (!string.IsNullOrEmpty(endCreatedAt) && DateTime.TryParse(endCreatedAt, out DateTime endCreatedAtDate))
            {
                query = query.Where(x => x.CreatedAt <= endCreatedAtDate);
            }

            // 获取总记录数
            int totalCount = await query.CountAsync();

            // 应用排序
            if (!string.IsNullOrEmpty(sortBy))
            {
                if (sortOrder.ToLower() == "asc")
                {
                    query = query.OrderBy(sortBy);
                }
                else
                {
                    query = query.OrderBy($"{sortBy} DESC");
                }
            }
            else
            {
                query = query.OrderByDescending(x => x.CreatedAt);
            }

            // 应用分页
            var entities = await query.Skip((page - 1) * pageSize).Take(pageSize).ToListAsync();

            // 如果没有数据，直接返回
            if (entities.Count == 0)
            {
                return (new List<MDL.DTOs.LabelScanWithCustomerDto>(), totalCount);
            }

            // 提取所有非null的客户ID，用于批量查询
            var customerIds = entities.Select(l => l.CustomerId).Distinct().ToList();
            
            // 批量查询客户信息
            var customerList = await db.Queryable<MDL.Models.CustomerEntity>()
                .Where(c => customerIds.Contains(c.Id))
                .ToListAsync();
            
            // 手动构建字典，确保类型正确
            Dictionary<int, string> customers = new Dictionary<int, string>();
            foreach (var customer in customerList)
            {
                customers[customer.Id] = customer.CustomerCode;
            }

            // 构建DTO列表
            var dtos = new List<MDL.DTOs.LabelScanWithCustomerDto>();
            foreach (var l in entities)
            {
                var dto = new MDL.DTOs.LabelScanWithCustomerDto
                {
                    Id = l.Id,
                    CustomerId = l.CustomerId,
                    ReferenceNumber = l.ReferenceNumber,
                    NeutralWaybillNumber = l.NeutralWaybillNumber,
                    FinalMileTrackingNumber = l.FinalMileTrackingNumber,
                    Result = (int)l.Result,
                    Description = l.Description,
                    CreatedBy = l.CreatedBy,
                    CreatedAt = l.CreatedAt,
                    UpdatedAt = l.UpdatedAt
                };

                // 处理客户代码
                if (customers.TryGetValue(l.CustomerId, out var customerCode))
                {
                    dto.CustomerCode = customerCode;
                }

                dtos.Add(dto);
            }

            return (dtos, totalCount);
        }

        /// <summary>
        /// 根据中性面单单号获取最新的扫描记录
        /// </summary>
        /// <param name="neutralWaybillNumber">中性面单单号</param>
        /// <returns>最新的标签扫描记录</returns>
        public async Task<LabelScanEntity> GetLatestScanByWaybillAsync(string neutralWaybillNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .Where(x => x.NeutralWaybillNumber == neutralWaybillNumber)
                .OrderByDescending(x => x.CreatedAt)
                .FirstAsync();
        }

        /// <summary>
        /// 根据日期范围获取扫描记录
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <returns>标签扫描记录列表</returns>
        public async Task<List<LabelScanEntity>> GetByDateRangeAsync(System.DateTime startDate, System.DateTime endDate)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelScanEntity>()
                .Where(x => x.CreatedAt >= startDate && x.CreatedAt <= endDate)
                .OrderBy(x => x.CreatedAt)
                .ToListAsync();
        }
    }
}