1043 lines
42 KiB
C#
1043 lines
42 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Threading.Tasks;
|
||
using MDL.Models;
|
||
using MDL.DTOs;
|
||
using DAL.Interfaces;
|
||
using DB.Database;
|
||
using SqlSugar;
|
||
|
||
namespace DAL.Repositories
|
||
{
|
||
/// <summary>
|
||
/// 标签替换请求的数据访问实现类
|
||
/// </summary>
|
||
public class LabelReplaceRepository : ILabelReplaceRepository
|
||
{
|
||
private readonly ISqlSugarProvider _provider;
|
||
|
||
/// <summary>
|
||
/// 构造函数
|
||
/// </summary>
|
||
/// <param name="provider">SqlSugar数据库提供程序</param>
|
||
public LabelReplaceRepository(ISqlSugarProvider provider)
|
||
{
|
||
_provider = provider;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 创建标签替换请求记录
|
||
/// </summary>
|
||
/// <param name="entity">标签替换请求实体</param>
|
||
/// <returns>创建的记录ID</returns>
|
||
public async Task<int> CreateAsync(LabelReplaceEntity entity)
|
||
{
|
||
var db = _provider.GetClient();
|
||
|
||
// 确保表存在
|
||
// 使用更安全的表初始化方式
|
||
if (!db.DbMaintenance.IsAnyTable("label_replace_requests"))
|
||
{
|
||
db.CodeFirst.InitTables(typeof(LabelReplaceEntity));
|
||
}
|
||
|
||
// 设置时间戳
|
||
entity.CreatedAt = DateTime.UtcNow;
|
||
entity.UpdatedAt = DateTime.UtcNow;
|
||
|
||
// 插入数据并返回自增ID
|
||
return await db.Insertable(entity).ExecuteReturnIdentityAsync();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据ID获取标签替换请求记录
|
||
/// </summary>
|
||
/// <param name="id">记录ID</param>
|
||
/// <returns>标签替换请求实体</returns>
|
||
public async Task<LabelReplaceEntity?> GetByIdAsync(int id)
|
||
{
|
||
var db = _provider.GetClient();
|
||
return await db.Queryable<LabelReplaceEntity>()
|
||
.Where(x => x.Id == id)
|
||
.FirstAsync();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据中性面单单号获取标签替换请求记录
|
||
/// </summary>
|
||
/// <param name="waybillNumber">中性面单单号</param>
|
||
/// <returns>标签替换请求实体</returns>
|
||
public async Task<LabelReplaceEntity?> GetByWaybillNumberAsync(string waybillNumber)
|
||
{
|
||
var db = _provider.GetClient();
|
||
return await db.Queryable<LabelReplaceEntity>()
|
||
.Where(x => x.NeutralWaybillNumber == waybillNumber)
|
||
.FirstAsync();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据跟踪单号获取标签替换请求记录
|
||
/// </summary>
|
||
/// <param name="trackingNumber">跟踪单号</param>
|
||
/// <returns>标签替换请求实体列表</returns>
|
||
public async Task<List<LabelReplaceEntity>> GetByTrackingNumberAsync(string trackingNumber)
|
||
{
|
||
var db = _provider.GetClient();
|
||
return await db.Queryable<LabelReplaceEntity>()
|
||
.Where(x => x.FinalMileTrackingNumber == trackingNumber)
|
||
.OrderByDescending(x => x.CreatedAt)
|
||
.ToListAsync();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 更新标签替换请求记录
|
||
/// </summary>
|
||
/// <param name="entity">标签替换请求实体</param>
|
||
/// <returns>更新是否成功</returns>
|
||
public async Task<bool> UpdateAsync(LabelReplaceEntity entity)
|
||
{
|
||
var db = _provider.GetClient();
|
||
|
||
// 更新时间戳
|
||
entity.UpdatedAt = DateTime.UtcNow;
|
||
|
||
// 更新数据并返回影响行数
|
||
var rows = await db.Updateable(entity).ExecuteCommandAsync();
|
||
return rows > 0;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 删除标签替换请求记录
|
||
/// </summary>
|
||
/// <param name="id">记录ID</param>
|
||
/// <returns>删除是否成功</returns>
|
||
public async Task<bool> DeleteAsync(int id)
|
||
{
|
||
var db = _provider.GetClient();
|
||
var rows = await db.Deleteable<LabelReplaceEntity>()
|
||
.Where(x => x.Id == id)
|
||
.ExecuteCommandAsync();
|
||
return rows > 0;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取所有标签替换请求记录
|
||
/// </summary>
|
||
/// <returns>标签替换请求实体列表</returns>
|
||
public async Task<List<LabelReplaceEntity>> GetAllAsync()
|
||
{
|
||
var db = _provider.GetClient();
|
||
return await db.Queryable<LabelReplaceEntity>()
|
||
.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="billOfLadingNumber">提单号</param>
|
||
/// <param name="masterPackageNumber">大包号</param>
|
||
/// <param name="referenceNumber">参考号</param>
|
||
/// <param name="neutralWaybillNumber">中性面单单号</param>
|
||
/// <param name="finalMileTrackingNumber">尾程跟踪单号</param>
|
||
/// <param name="replaceStatus">换单状态</param>
|
||
/// <param name="customerId">客户ID</param>
|
||
/// <param name="startCreatedAt">创建时间开始</param>
|
||
/// <param name="endCreatedAt">创建时间结束</param>
|
||
/// <param name="startReplacedAt">换单时间开始</param>
|
||
/// <param name="endReplacedAt">换单时间结束</param>
|
||
/// <returns>包含客户信息的标签替换请求DTO列表和总记录数</returns>
|
||
public async Task<(List<MDL.DTOs.LabelReplaceWithCustomerDto>, int)> GetByPageAsync(int page, int pageSize, string sortBy, string sortOrder, string billOfLadingNumber, string masterPackageNumber, string referenceNumber, string neutralWaybillNumber, string finalMileTrackingNumber, string replaceStatus, int? customerId, string startCreatedAt, string endCreatedAt, string startReplacedAt, string endReplacedAt)
|
||
{
|
||
var db = _provider.GetClient();
|
||
|
||
// 解析时间筛选参数
|
||
DateTime? parsedStartCreatedAt = null;
|
||
DateTime? parsedEndCreatedAt = null;
|
||
DateTime? parsedStartReplacedAt = null;
|
||
DateTime? parsedEndReplacedAt = null;
|
||
|
||
if (!string.IsNullOrEmpty(startCreatedAt) && DateTime.TryParse(startCreatedAt, out DateTime startCreatedAtDate))
|
||
{
|
||
parsedStartCreatedAt = startCreatedAtDate;
|
||
}
|
||
if (!string.IsNullOrEmpty(endCreatedAt) && DateTime.TryParse(endCreatedAt, out DateTime endCreatedAtDate))
|
||
{
|
||
parsedEndCreatedAt = endCreatedAtDate;
|
||
}
|
||
if (!string.IsNullOrEmpty(startReplacedAt) && DateTime.TryParse(startReplacedAt, out DateTime startReplacedAtDate))
|
||
{
|
||
parsedStartReplacedAt = startReplacedAtDate;
|
||
}
|
||
if (!string.IsNullOrEmpty(endReplacedAt) && DateTime.TryParse(endReplacedAt, out DateTime endReplacedAtDate))
|
||
{
|
||
parsedEndReplacedAt = endReplacedAtDate;
|
||
}
|
||
|
||
// 先创建基础查询
|
||
var baseQuery = db.Queryable<LabelReplaceEntity>();
|
||
|
||
// 应用基础筛选条件
|
||
if (!string.IsNullOrEmpty(billOfLadingNumber))
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.BillOfLadingNumber.Contains(billOfLadingNumber));
|
||
}
|
||
if (!string.IsNullOrEmpty(masterPackageNumber))
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.MasterPackageNumber.Contains(masterPackageNumber));
|
||
}
|
||
if (!string.IsNullOrEmpty(referenceNumber))
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.ReferenceNumber.Contains(referenceNumber));
|
||
}
|
||
if (!string.IsNullOrEmpty(neutralWaybillNumber))
|
||
{
|
||
// 处理多个单号的情况，支持逗号分隔
|
||
var waybillNumbers = neutralWaybillNumber.Split(new[] { ',', '\n', '\r', ' ' }, StringSplitOptions.RemoveEmptyEntries);
|
||
if (waybillNumbers.Length > 1)
|
||
{
|
||
baseQuery = baseQuery.Where(l => waybillNumbers.Contains(l.NeutralWaybillNumber));
|
||
}
|
||
else if (waybillNumbers.Length == 1)
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.NeutralWaybillNumber.Contains(waybillNumbers[0]));
|
||
}
|
||
}
|
||
if (!string.IsNullOrEmpty(finalMileTrackingNumber))
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.FinalMileTrackingNumber.Contains(finalMileTrackingNumber));
|
||
}
|
||
if (!string.IsNullOrEmpty(replaceStatus))
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.ReplaceStatus == replaceStatus);
|
||
}
|
||
if (customerId.HasValue)
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.CustomerId == customerId);
|
||
}
|
||
|
||
// 只应用 CreatedAt 筛选条件，ReplacedAt 稍后在内存中筛选
|
||
if (parsedStartCreatedAt.HasValue)
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.CreatedAt >= parsedStartCreatedAt.Value);
|
||
}
|
||
if (parsedEndCreatedAt.HasValue)
|
||
{
|
||
baseQuery = baseQuery.Where(l => l.CreatedAt <= parsedEndCreatedAt.Value);
|
||
}
|
||
|
||
// 先获取所有符合基础条件的记录（不分页）
|
||
var allLabelReplaceEntities = await baseQuery.ToListAsync();
|
||
|
||
// 如果没有数据，直接返回
|
||
if (allLabelReplaceEntities.Count == 0)
|
||
{
|
||
return (new List<MDL.DTOs.LabelReplaceWithCustomerDto>(), 0);
|
||
}
|
||
|
||
// 提取所有中性面单单号，用于批量查询最新扫描记录
|
||
var scanWaybillNumbers = allLabelReplaceEntities.Select(l => l.NeutralWaybillNumber).Distinct().ToList();
|
||
|
||
// 批量查询每个中性面单单号的最新扫描成功记录
|
||
var latestScans = new List<MDL.Models.LabelScanEntity>();
|
||
if (scanWaybillNumbers.Count > 0)
|
||
{
|
||
// 先获取所有符合条件的扫描记录
|
||
var allScans = await db.Queryable<MDL.Models.LabelScanEntity>()
|
||
.Where(s => scanWaybillNumbers.Contains(s.NeutralWaybillNumber) && s.Result == 0)
|
||
.OrderByDescending(s => s.CreatedAt)
|
||
.ToListAsync();
|
||
|
||
// 按中性面单单号分组，取每组的第一条记录（最新的）
|
||
var groupedScans = allScans.GroupBy(s => s.NeutralWaybillNumber);
|
||
foreach (var group in groupedScans)
|
||
{
|
||
latestScans.Add(group.First());
|
||
}
|
||
}
|
||
|
||
// 创建中性面单单号到最新扫描时间的映射，确保类型正确
|
||
var latestScanMap = new Dictionary<string, DateTime>();
|
||
foreach (var scan in latestScans)
|
||
{
|
||
latestScanMap[scan.NeutralWaybillNumber] = scan.CreatedAt;
|
||
}
|
||
|
||
// 根据 ReplacedAt 筛选记录
|
||
var filteredEntities = new List<LabelReplaceEntity>();
|
||
foreach (var entity in allLabelReplaceEntities)
|
||
{
|
||
bool shouldInclude = true;
|
||
|
||
// 获取此订单的ReplacedAt时间
|
||
DateTime? replacedAt = null;
|
||
if (latestScanMap.TryGetValue(entity.NeutralWaybillNumber, out var latestCreatedAt))
|
||
{
|
||
replacedAt = latestCreatedAt;
|
||
}
|
||
|
||
// 应用 ReplacedAt 筛选
|
||
if (parsedStartReplacedAt.HasValue)
|
||
{
|
||
if (!replacedAt.HasValue || replacedAt.Value < parsedStartReplacedAt.Value)
|
||
{
|
||
shouldInclude = false;
|
||
}
|
||
}
|
||
if (parsedEndReplacedAt.HasValue)
|
||
{
|
||
if (!replacedAt.HasValue || replacedAt.Value > parsedEndReplacedAt.Value)
|
||
{
|
||
shouldInclude = false;
|
||
}
|
||
}
|
||
|
||
if (shouldInclude)
|
||
{
|
||
filteredEntities.Add(entity);
|
||
}
|
||
}
|
||
|
||
// 计算筛选后的总记录数
|
||
int totalCount = filteredEntities.Count;
|
||
|
||
// 对筛选后的实体进行排序
|
||
if (!string.IsNullOrEmpty(sortBy))
|
||
{
|
||
if (sortOrder.ToLower() == "asc")
|
||
{
|
||
// 根据常用排序字段排序
|
||
filteredEntities = sortBy switch
|
||
{
|
||
"CreatedAt" => filteredEntities.OrderBy(e => e.CreatedAt).ToList(),
|
||
"UpdatedAt" => filteredEntities.OrderBy(e => e.UpdatedAt).ToList(),
|
||
"Id" => filteredEntities.OrderBy(e => e.Id).ToList(),
|
||
_ => filteredEntities.OrderBy(e => e.CreatedAt).ToList()
|
||
};
|
||
}
|
||
else
|
||
{
|
||
filteredEntities = sortBy switch
|
||
{
|
||
"CreatedAt" => filteredEntities.OrderByDescending(e => e.CreatedAt).ToList(),
|
||
"UpdatedAt" => filteredEntities.OrderByDescending(e => e.UpdatedAt).ToList(),
|
||
"Id" => filteredEntities.OrderByDescending(e => e.Id).ToList(),
|
||
_ => filteredEntities.OrderByDescending(e => e.CreatedAt).ToList()
|
||
};
|
||
}
|
||
}
|
||
else
|
||
{
|
||
filteredEntities = filteredEntities.OrderByDescending(e => e.CreatedAt).ToList();
|
||
}
|
||
|
||
// 应用分页
|
||
var pagedEntities = filteredEntities
|
||
.Skip((page - 1) * pageSize)
|
||
.Take(pageSize)
|
||
.ToList();
|
||
|
||
// 如果分页后没有数据，直接返回
|
||
if (pagedEntities.Count == 0)
|
||
{
|
||
return (new List<MDL.DTOs.LabelReplaceWithCustomerDto>(), totalCount);
|
||
}
|
||
|
||
// 提取所有非null的客户ID，用于批量查询
|
||
var customerIds = pagedEntities.Where(l => l.CustomerId.HasValue).Select(l => l.CustomerId.Value).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.LabelReplaceWithCustomerDto>();
|
||
foreach (var l in pagedEntities)
|
||
{
|
||
var dto = new MDL.DTOs.LabelReplaceWithCustomerDto
|
||
{
|
||
Id = l.Id,
|
||
BillOfLadingNumber = l.BillOfLadingNumber,
|
||
MasterPackageNumber = l.MasterPackageNumber,
|
||
ReferenceNumber = l.ReferenceNumber,
|
||
NeutralWaybillNumber = l.NeutralWaybillNumber,
|
||
FinalMileTrackingNumber = l.FinalMileTrackingNumber,
|
||
Label = l.Label,
|
||
HasLabel = !string.IsNullOrEmpty(l.Label),
|
||
ReplaceStatus = l.ReplaceStatus,
|
||
LabelRetrievedAt = l.LabelRetrievedAt,
|
||
CreatedAt = l.CreatedAt,
|
||
UpdatedAt = l.UpdatedAt
|
||
};
|
||
|
||
// 处理客户代码
|
||
if (l.CustomerId.HasValue)
|
||
{
|
||
if (customers.TryGetValue(l.CustomerId.Value, out var customerCode))
|
||
{
|
||
dto.CustomerCode = customerCode;
|
||
}
|
||
}
|
||
|
||
// 处理扫描时间
|
||
if (latestScanMap.TryGetValue(l.NeutralWaybillNumber, out var latestCreatedAt))
|
||
{
|
||
dto.ReplacedAt = latestCreatedAt;
|
||
}
|
||
|
||
dtos.Add(dto);
|
||
}
|
||
|
||
return (dtos, totalCount);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 批量查询换单状态
|
||
/// </summary>
|
||
/// <param name="customerId">客户ID</param>
|
||
/// <param name="waybillNumbers">中性面单单号列表</param>
|
||
/// <param name="trackingNumbers">尾程跟踪单号列表</param>
|
||
/// <returns>标签替换请求实体列表</returns>
|
||
public async Task<(List<LabelReplaceEntity>, Dictionary<string, DateTime>)> GetLabelReplaceStatusAsync(int customerId, List<string> waybillNumbers, List<string> trackingNumbers)
|
||
{
|
||
var db = _provider.GetClient();
|
||
|
||
// 构建查询条件
|
||
var query = db.Queryable<LabelReplaceEntity>()
|
||
.Where(x => x.CustomerId == customerId);
|
||
|
||
// 添加单号查询条件
|
||
if (waybillNumbers != null && waybillNumbers.Count > 0)
|
||
{
|
||
query = query.Where(x => waybillNumbers.Contains(x.NeutralWaybillNumber));
|
||
}
|
||
|
||
if (trackingNumbers != null && trackingNumbers.Count > 0)
|
||
{
|
||
query = query.Where(x => x.FinalMileTrackingNumber != null && trackingNumbers.Contains(x.FinalMileTrackingNumber));
|
||
}
|
||
|
||
// 执行查询
|
||
var labelReplaceEntities = await query.ToListAsync();
|
||
|
||
// 提取所有中性面单单号，用于批量查询最新扫描记录
|
||
var scanWaybillNumbers = labelReplaceEntities.Select(l => l.NeutralWaybillNumber).Distinct().ToList();
|
||
|
||
// 批量查询每个中性面单单号的最新扫描成功记录
|
||
var latestScanMap = new Dictionary<string, DateTime>();
|
||
if (scanWaybillNumbers.Count > 0)
|
||
{
|
||
// 先获取所有符合条件的扫描记录
|
||
var allScans = await db.Queryable<MDL.Models.LabelScanEntity>()
|
||
.Where(s => scanWaybillNumbers.Contains(s.NeutralWaybillNumber) && s.Result == 0)
|
||
.OrderByDescending(s => s.CreatedAt)
|
||
.ToListAsync();
|
||
|
||
// 按中性面单单号分组，取每组的第一条记录（最新的）
|
||
var groupedScans = allScans.GroupBy(s => s.NeutralWaybillNumber);
|
||
foreach (var group in groupedScans)
|
||
{
|
||
var latestScan = group.First();
|
||
latestScanMap[latestScan.NeutralWaybillNumber] = latestScan.CreatedAt;
|
||
}
|
||
}
|
||
|
||
return (labelReplaceEntities, latestScanMap);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据交接单号列表批量获取标签替换请求记录
|
||
/// </summary>
|
||
/// <param name="handoverNumbers">交接单号列表</param>
|
||
/// <param name="customerId">客户ID</param>
|
||
/// <returns>标签替换请求实体列表</returns>
|
||
public async Task<List<LabelReplaceEntity>> GetByHandoverNumbersAsync(List<string> handoverNumbers, int? customerId)
|
||
{
|
||
var db = _provider.GetClient();
|
||
|
||
if (handoverNumbers == null || handoverNumbers.Count == 0)
|
||
return new List<LabelReplaceEntity>();
|
||
|
||
var query = db.Queryable<LabelReplaceEntity>()
|
||
.Where(x => handoverNumbers.Contains(x.BillOfLadingNumber) || handoverNumbers.Contains(x.MasterPackageNumber));
|
||
|
||
if (customerId.HasValue)
|
||
{
|
||
query = query.Where(x => x.CustomerId == customerId.Value);
|
||
}
|
||
|
||
return await query.ToListAsync();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 将base64编码转换为PDF文件并保存
|
||
/// </summary>
|
||
/// <param name="base64Content">base64编码的PDF内容</param>
|
||
/// <param name="fileName">文件名</param>
|
||
/// <returns>保存的文件路径</returns>
|
||
public async Task<string> ConvertBase64ToPdfAsync(string base64Content, string fileName)
|
||
{
|
||
// 定义保存目录
|
||
string saveDirectory = @"C:\TESYSTEM\OMS\api-lable\pdf\20260302";
|
||
|
||
// 确保目录存在
|
||
if (!System.IO.Directory.Exists(saveDirectory))
|
||
{
|
||
System.IO.Directory.CreateDirectory(saveDirectory);
|
||
}
|
||
|
||
// 构建完整的文件路径
|
||
string filePath = System.IO.Path.Combine(saveDirectory, fileName);
|
||
|
||
// 处理base64内容，移除可能的前缀
|
||
if (base64Content.StartsWith("data:application/pdf;base64,"))
|
||
{
|
||
base64Content = base64Content.Substring("data:application/pdf;base64,".Length);
|
||
}
|
||
|
||
// 转换base64为字节数组
|
||
byte[] pdfBytes = Convert.FromBase64String(base64Content);
|
||
|
||
// 保存到文件
|
||
await System.IO.File.WriteAllBytesAsync(filePath, pdfBytes);
|
||
|
||
return filePath;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 更新Label字段但保持LabelRetrievedAt不变
|
||
/// </summary>
|
||
/// <param name="id">记录ID</param>
|
||
/// <param name="newLabel">新的Label值</param>
|
||
/// <returns>更新是否成功</returns>
|
||
public async Task<bool> UpdateLabelWithoutChangingRetrievedAtAsync(int id, string newLabel)
|
||
{
|
||
var db = _provider.GetClient();
|
||
|
||
// 首先获取现有记录，以便获取LabelRetrievedAt的当前值
|
||
var existing = await db.Queryable<LabelReplaceEntity>()
|
||
.Where(x => x.Id == id)
|
||
.FirstAsync();
|
||
|
||
if (existing == null)
|
||
{
|
||
return false;
|
||
}
|
||
|
||
// 显式更新Label字段，同时将LabelRetrievedAt设置为原值，
|
||
// 这样触发器就不会改变它
|
||
var rows = await db.Updateable<LabelReplaceEntity>()
|
||
.SetColumns(x => x.Label == newLabel)
|
||
.SetColumns(x => x.LabelRetrievedAt == existing.LabelRetrievedAt)
|
||
.SetColumns(x => x.UpdatedAt == DateTime.UtcNow)
|
||
.Where(x => x.Id == id)
|
||
.ExecuteCommandAsync();
|
||
|
||
return rows > 0;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取每日标签统计数据
|
||
/// </summary>
|
||
/// <param name="startDate">开始日期</param>
|
||
/// <param name="endDate">结束日期</param>
|
||
/// <param name="customerId">客户ID</param>
|
||
/// <returns>每日标签统计数据列表</returns>
|
||
public async Task<List<DailyLabelStatsDto>> GetDailyLabelStatsAsync(string startDate, string endDate, int? customerId)
|
||
{
|
||
var db = _provider.GetClient();
|
||
|
||
// 构建日期范围条件
|
||
DateTime? start = null;
|
||
DateTime? end = null;
|
||
|
||
if (!string.IsNullOrEmpty(startDate))
|
||
{
|
||
start = DateTime.Parse(startDate);
|
||
}
|
||
if (!string.IsNullOrEmpty(endDate))
|
||
{
|
||
end = DateTime.Parse(endDate).AddDays(1).AddSeconds(-1);
|
||
}
|
||
|
||
// 获取所有符合条件的 label_replace_requests（label不为空）
|
||
var labelRequestsQuery = db.Queryable<LabelReplaceEntity>()
|
||
.Where(x => x.Label != null && x.Label != "");
|
||
|
||
if (customerId.HasValue)
|
||
{
|
||
labelRequestsQuery = labelRequestsQuery.Where(x => x.CustomerId == customerId);
|
||
}
|
||
|
||
var labelRequests = await labelRequestsQuery.ToListAsync();
|
||
var waybillNumbers = labelRequests.Select(x => x.NeutralWaybillNumber).Distinct().ToList();
|
||
|
||
if (waybillNumbers.Count == 0)
|
||
{
|
||
return new List<DailyLabelStatsDto>();
|
||
}
|
||
|
||
// 获取所有相关的扫描记录
|
||
var scansQuery = db.Queryable<LabelScanEntity>()
|
||
.Where(x => waybillNumbers.Contains(x.NeutralWaybillNumber));
|
||
|
||
if (customerId.HasValue)
|
||
{
|
||
scansQuery = scansQuery.Where(x => x.CustomerId == customerId);
|
||
}
|
||
|
||
var allScans = await scansQuery.OrderBy(x => x.CreatedAt).ToListAsync();
|
||
|
||
// 构建统计字典
|
||
var statsDict = new Dictionary<string, DailyLabelStatsDto>();
|
||
|
||
// 获取数据拉取时间（当前UTC时间减5小时）
|
||
var dataFetchTime = DateTime.UtcNow.AddHours(-5);
|
||
|
||
// 1. 统计换单失败未完结数（所有有扫描记录但未能成功换单的数量）
|
||
// 对于每个中性面单单号，检查是否有Result = 0的记录，如果没有就算失败未完结
|
||
var waybillScanGroups = allScans.GroupBy(x => x.NeutralWaybillNumber);
|
||
var unfinishedFailureSet = new HashSet<string>();
|
||
|
||
foreach (var group in waybillScanGroups)
|
||
{
|
||
var hasSuccess = group.Any(x => x.Result == 0);
|
||
if (!hasSuccess)
|
||
{
|
||
unfinishedFailureSet.Add(group.Key);
|
||
}
|
||
}
|
||
|
||
// 2. 按日期统计各项指标
|
||
foreach (var scan in allScans)
|
||
{
|
||
// 转换为UTC-5时区的日期
|
||
var dateKey = scan.CreatedAt.AddHours(-5).ToString("yyyy-MM-dd");
|
||
|
||
// 应用日期范围过滤
|
||
if (start.HasValue && scan.CreatedAt.AddHours(-5) < start.Value)
|
||
continue;
|
||
if (end.HasValue && scan.CreatedAt.AddHours(-5) > end.Value)
|
||
continue;
|
||
|
||
if (!statsDict.ContainsKey(dateKey))
|
||
{
|
||
statsDict[dateKey] = new DailyLabelStatsDto
|
||
{
|
||
Date = dateKey,
|
||
DataFetchTime = dataFetchTime
|
||
};
|
||
}
|
||
|
||
var stats = statsDict[dateKey];
|
||
|
||
// 当日扫描数
|
||
stats.DailyScanCount++;
|
||
|
||
// 当日换单成功数（Result = 0）
|
||
if (scan.Result == 0)
|
||
{
|
||
stats.DailySuccessCount++;
|
||
|
||
// 当日STOP数（Result = 0且描述包含"成功返回STOP标签"）
|
||
if (!string.IsNullOrEmpty(scan.Description) && scan.Description.Contains("成功返回STOP标签"))
|
||
{
|
||
stats.DailyStopCount++;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
// 当日换单失败数（Result != 0）
|
||
stats.DailyFailureCount++;
|
||
}
|
||
}
|
||
|
||
// 3. 统计当日标签推送数（按LabelRetrievedAt统计）
|
||
foreach (var request in labelRequests)
|
||
{
|
||
if (request.LabelRetrievedAt.HasValue)
|
||
{
|
||
var dateKey = request.LabelRetrievedAt.Value.AddHours(-5).ToString("yyyy-MM-dd");
|
||
|
||
// 应用日期范围过滤
|
||
if (start.HasValue && request.LabelRetrievedAt.Value.AddHours(-5) < start.Value)
|
||
continue;
|
||
if (end.HasValue && request.LabelRetrievedAt.Value.AddHours(-5) > end.Value)
|
||
continue;
|
||
|
||
if (!statsDict.ContainsKey(dateKey))
|
||
{
|
||
statsDict[dateKey] = new DailyLabelStatsDto
|
||
{
|
||
Date = dateKey,
|
||
DataFetchTime = dataFetchTime
|
||
};
|
||
}
|
||
|
||
statsDict[dateKey].DailyLabelPushCount++;
|
||
}
|
||
}
|
||
|
||
// 4. 为每个日期设置换单失败未完结数（这是一个全局统计，适用于所有日期）
|
||
foreach (var stats in statsDict.Values)
|
||
{
|
||
stats.UnfinishedFailureCount = unfinishedFailureSet.Count;
|
||
}
|
||
|
||
// 转换为列表并按日期排序
|
||
var result = statsDict.Values.OrderBy(x => x.Date).ToList();
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取每日标签统计数据（中文版本，使用自定义SQL）
|
||
/// </summary>
|
||
/// <returns>每日标签统计数据列表</returns>
|
||
public async Task<List<DailyLabelStatsChineseDto>> GetDailyLabelStatsChineseAsync()
|
||
{
|
||
// 使用固定的数据库连接
|
||
string customConnectionString = "server=172.233.222.200;port=6033;user id=oms_user;password=oms_user@pwd;database=lr01mainusa;CharSet=utf8;allow zero datetime=true;Convert Zero Datetime=true;Max Pool Size=100;Min Pool Size=10;Connection Timeout=30;Allow User Variables=True;";
|
||
|
||
// 直接使用 MySqlConnection 来执行 SQL，绕过 SqlSugar 初始化问题
|
||
var result = new List<DailyLabelStatsChineseDto>();
|
||
|
||
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(customConnectionString))
|
||
{
|
||
await connection.OpenAsync();
|
||
|
||
// 读取 SQL 文件内容
|
||
string sql = @"
|
||
WITH
|
||
-- 步骤1：获取所有到货交接单，日期已是UTC-5
|
||
ArrivalFormsWithDate AS (
|
||
SELECT
|
||
a.Id,
|
||
a.HandoverNumber,
|
||
DATE(a.ReceiptTime) AS 到货日期,
|
||
a.ReceiptTime AS 到货时间
|
||
FROM arrival_handover_forms a
|
||
),
|
||
|
||
-- 步骤2：关联到货交接单与换单请求（只取Label有值的），并记录订单级别的信息，计算考核时间
|
||
ArrivalRequests AS (
|
||
SELECT
|
||
a.到货日期,
|
||
a.到货时间,
|
||
l.Id AS RequestId,
|
||
l.NeutralWaybillNumber,
|
||
l.BillOfLadingNumber,
|
||
l.MasterPackageNumber,
|
||
l.Label,
|
||
l.LabelRetrievedAt,
|
||
l.CustomerId,
|
||
-- 考核时间：标签推送时间与到仓时间比较，哪个最新用哪个
|
||
CASE
|
||
WHEN l.LabelRetrievedAt IS NULL THEN a.到货时间
|
||
WHEN l.LabelRetrievedAt > a.到货时间 THEN l.LabelRetrievedAt
|
||
ELSE a.到货时间
|
||
END AS 考核时间
|
||
FROM ArrivalFormsWithDate a
|
||
INNER JOIN label_replace_requests l
|
||
ON l.BillOfLadingNumber = a.HandoverNumber
|
||
OR l.MasterPackageNumber = a.HandoverNumber
|
||
WHERE l.Label IS NOT NULL AND l.Label != ''
|
||
),
|
||
|
||
-- 步骤3：获取每个订单的扫描记录情况（按天）
|
||
DailyScanStatus AS (
|
||
SELECT
|
||
s.NeutralWaybillNumber,
|
||
DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
|
||
MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 当日是否成功,
|
||
MAX(CASE WHEN s.Result != 0 THEN 1 ELSE 0 END) AS 当日是否失败
|
||
FROM label_scan_history s
|
||
INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
|
||
WHERE l.Label IS NOT NULL AND l.Label != ''
|
||
GROUP BY s.NeutralWaybillNumber, DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
|
||
),
|
||
|
||
-- 步骤4：获取每个订单是否曾经成功，以及首次成功日期和时间
|
||
OverallScanStatus AS (
|
||
SELECT
|
||
s.NeutralWaybillNumber,
|
||
MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 曾成功,
|
||
MIN(CASE WHEN s.Result = 0 THEN DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) ELSE NULL END) AS 首次成功日期,
|
||
MIN(CASE WHEN s.Result = 0 THEN s.CreatedAt ELSE NULL END) AS 首次成功时间
|
||
FROM label_scan_history s
|
||
GROUP BY s.NeutralWaybillNumber
|
||
),
|
||
|
||
-- 步骤5：从数据中收集所有日期
|
||
AllDates AS (
|
||
SELECT 到货日期 AS 日期 FROM ArrivalRequests
|
||
UNION
|
||
SELECT 日期 FROM DailyScanStatus
|
||
UNION
|
||
SELECT DATE(CONVERT_TZ(l.LabelRetrievedAt, '+00:00', '-05:00')) AS 日期
|
||
FROM label_replace_requests l
|
||
WHERE l.LabelRetrievedAt IS NOT NULL
|
||
),
|
||
|
||
-- 步骤6：去重并排序日期
|
||
DistinctDates AS (
|
||
SELECT DISTINCT 日期
|
||
FROM AllDates
|
||
ORDER BY 日期
|
||
),
|
||
|
||
-- 步骤7：获取最新日期
|
||
LatestDate AS (
|
||
SELECT MAX(日期) AS 日期
|
||
FROM DistinctDates
|
||
),
|
||
|
||
-- 步骤8：每日基础统计 - 当日新增换单数
|
||
DailyBase AS (
|
||
SELECT
|
||
dd.日期,
|
||
-- 当日新增换单数：当天到货并且推送了标签数据的订单
|
||
COUNT(DISTINCT CASE
|
||
WHEN ar.到货日期 = dd.日期 AND ar.LabelRetrievedAt IS NOT NULL
|
||
THEN ar.RequestId
|
||
END) AS 当日新增换单数,
|
||
-- 当天标签推送数
|
||
COUNT(DISTINCT CASE
|
||
WHEN DATE(CONVERT_TZ(ar.LabelRetrievedAt, '+00:00', '-05:00')) = dd.日期
|
||
THEN ar.RequestId
|
||
END) AS 当日标签推送数
|
||
FROM DistinctDates dd
|
||
CROSS JOIN ArrivalRequests ar
|
||
GROUP BY dd.日期
|
||
),
|
||
|
||
-- 步骤9：每日扫描统计（扫描次数），换单成功数去重
|
||
DailyScanMetrics AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
|
||
COUNT(*) AS 当日扫描数,
|
||
-- 当日STOP数（同样去重）
|
||
COUNT(DISTINCT CASE WHEN s.Result = 0 AND s.Description LIKE '%成功返回STOP标签%' THEN s.NeutralWaybillNumber END) AS 当日STOP数
|
||
FROM label_scan_history s
|
||
INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
|
||
WHERE l.Label IS NOT NULL AND l.Label != ''
|
||
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
|
||
),
|
||
|
||
-- 步骤9b：每日换单成功数（去重）
|
||
DailySuccessCount AS (
|
||
SELECT
|
||
scan_date AS 日期,
|
||
COUNT(DISTINCT NeutralWaybillNumber) AS 当日换单成功数
|
||
FROM (
|
||
-- 找出每个包裹每天最新的成功记录
|
||
SELECT
|
||
s.NeutralWaybillNumber,
|
||
DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS scan_date,
|
||
ROW_NUMBER() OVER (PARTITION BY s.NeutralWaybillNumber, DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) ORDER BY s.CreatedAt DESC) AS rn
|
||
FROM label_scan_history s
|
||
INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
|
||
WHERE l.Label IS NOT NULL AND l.Label != '' AND s.Result = 0
|
||
) AS s
|
||
WHERE rn = 1
|
||
GROUP BY scan_date
|
||
),
|
||
|
||
-- 步骤10：历史日期的换单失败未完结统计（非最新日期）
|
||
HistoryUnfinished AS (
|
||
SELECT
|
||
dd.日期,
|
||
COUNT(DISTINCT CASE
|
||
-- 当天失败并且当天没有成功的订单
|
||
WHEN dss.日期 = dd.日期 AND dss.当日是否失败 = 1 AND dss.当日是否成功 = 0
|
||
THEN dss.NeutralWaybillNumber
|
||
END) AS 换单失败未完结订单
|
||
FROM DistinctDates dd
|
||
LEFT JOIN DailyScanStatus dss ON dd.日期 = dss.日期
|
||
CROSS JOIN LatestDate ld
|
||
WHERE dd.日期 != ld.日期
|
||
GROUP BY dd.日期
|
||
),
|
||
|
||
-- 步骤11：最新日期的换单失败未完结统计（所有历史从未成功的）
|
||
LatestUnfinished AS (
|
||
SELECT
|
||
ld.日期,
|
||
COUNT(DISTINCT CASE
|
||
-- 必须同时满足：
|
||
-- 1. 有过扫描记录（OverallScanStatus中有该订单）
|
||
-- 2. 从未成功（曾成功 = 0）
|
||
-- 3. 并且至少有一次失败记录
|
||
WHEN oss.曾成功 = 0
|
||
THEN ar.NeutralWaybillNumber
|
||
END) AS 换单失败未完结订单
|
||
FROM LatestDate ld
|
||
CROSS JOIN ArrivalRequests ar
|
||
INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
|
||
GROUP BY ld.日期
|
||
),
|
||
|
||
-- 步骤12：每日换单失败订单统计（当天失败并且当天没成功的订单数）
|
||
DailyFailedOrders AS (
|
||
SELECT
|
||
日期,
|
||
COUNT(DISTINCT CASE
|
||
WHEN 当日是否失败 = 1 AND 当日是否成功 = 0
|
||
THEN NeutralWaybillNumber
|
||
END) AS 当日换单失败
|
||
FROM DailyScanStatus
|
||
GROUP BY 日期
|
||
),
|
||
|
||
-- 步骤13：每日完成订单统计 - 订单必须在我们关联的ArrivalRequests中
|
||
DailyCompletedOrders AS (
|
||
SELECT
|
||
oss.首次成功日期 AS 日期,
|
||
COUNT(DISTINCT oss.NeutralWaybillNumber) AS 当日完成数
|
||
FROM OverallScanStatus oss
|
||
INNER JOIN ArrivalRequests ar ON oss.NeutralWaybillNumber = ar.NeutralWaybillNumber
|
||
WHERE oss.曾成功 = 1
|
||
GROUP BY oss.首次成功日期
|
||
),
|
||
|
||
-- 步骤14：24小时换单完成订单统计 - 根据考核时间判断
|
||
Daily24HCompletedOrders AS (
|
||
SELECT
|
||
oss.首次成功日期 AS 日期,
|
||
COUNT(DISTINCT ar.NeutralWaybillNumber) AS 24H内完成数
|
||
FROM ArrivalRequests ar
|
||
INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
|
||
WHERE
|
||
oss.曾成功 = 1
|
||
AND oss.首次成功时间 IS NOT NULL
|
||
AND ar.考核时间 IS NOT NULL
|
||
-- 考核时间减去换单完成时间小于等于24小时
|
||
AND TIMESTAMPDIFF(HOUR, ar.考核时间, oss.首次成功时间) <= 24
|
||
GROUP BY oss.首次成功日期
|
||
),
|
||
|
||
-- 步骤15：完整的每日统计基础 - 准备每日的新增和完成，并获取前一日数据
|
||
DailyStatsWithPrev AS (
|
||
SELECT
|
||
db.日期,
|
||
db.当日新增换单数,
|
||
db.当日标签推送数,
|
||
COALESCE(dco.当日完成数, 0) AS 当日完成数,
|
||
COALESCE(dc24h.24H内完成数, 0) AS 24H内完成数,
|
||
-- 获取前一日的新增
|
||
LAG(db.当日新增换单数, 1, 0) OVER (ORDER BY db.日期) AS 前一日新增,
|
||
-- 获取前一日的完成数
|
||
LAG(COALESCE(dco.当日完成数, 0), 1, 0) OVER (ORDER BY db.日期) AS 前一日完成数,
|
||
-- 行号
|
||
ROW_NUMBER() OVER (ORDER BY db.日期) AS rn
|
||
FROM DailyBase db
|
||
LEFT JOIN DailyCompletedOrders dco ON db.日期 = dco.日期
|
||
LEFT JOIN Daily24HCompletedOrders dc24h ON db.日期 = dc24h.日期
|
||
ORDER BY db.日期
|
||
)
|
||
|
||
-- 步骤16：计算累计数据并最终输出
|
||
SELECT
|
||
日期,
|
||
当日新增换单数,
|
||
累计要换的总单数,
|
||
换单失败未完结订单,
|
||
当日换单失败,
|
||
当日换单成功数,
|
||
当日STOP数,
|
||
-- 24小时换单率
|
||
CASE
|
||
WHEN 当日完成数 = 0 THEN '0.00%'
|
||
ELSE CONCAT(ROUND(24H内完成数 / 当日完成数 * 100, 2), '%')
|
||
END AS 24H换单率,
|
||
-- 当天换单完成率
|
||
CASE
|
||
WHEN (当日新增换单数 + 累计要换的总单数) = 0 THEN '0.00%'
|
||
ELSE CONCAT(ROUND(当日完成数 / (当日新增换单数 + 累计要换的总单数) * 100, 2), '%')
|
||
END AS 当天换单完成率,
|
||
当日标签推送数,
|
||
当日扫描数,
|
||
数据拉取时间（UTC_5）
|
||
FROM (
|
||
SELECT
|
||
t.日期,
|
||
t.当日新增换单数,
|
||
-- 使用变量保持状态，每次计算前一天的累计
|
||
-- 公式：累计 = MAX(0, 前一日累计 + 前一日新增 - 前一日完成)
|
||
-- 第1天直接用当日新增
|
||
@running_total := GREATEST(0,
|
||
CASE
|
||
WHEN t.rn = 1 THEN t.当日新增换单数
|
||
ELSE @running_total + t.前一日新增 - t.前一日完成数
|
||
END) AS 累计要换的总单数,
|
||
-- 根据是否是最新日期选择不同的未完结统计
|
||
COALESCE(
|
||
CASE
|
||
WHEN t.日期 = (SELECT 日期 FROM LatestDate) THEN lu.换单失败未完结订单
|
||
ELSE hu.换单失败未完结订单
|
||
END,
|
||
0
|
||
) AS 换单失败未完结订单,
|
||
COALESCE(dfo.当日换单失败, 0) AS 当日换单失败,
|
||
COALESCE(dsc.当日换单成功数, 0) AS 当日换单成功数,
|
||
COALESCE(dsm.当日STOP数, 0) AS 当日STOP数,
|
||
t.24H内完成数,
|
||
t.当日完成数,
|
||
t.当日标签推送数,
|
||
COALESCE(dsm.当日扫描数, 0) AS 当日扫描数,
|
||
(UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间（UTC_5）
|
||
FROM DailyStatsWithPrev t
|
||
LEFT JOIN DailyScanMetrics dsm ON t.日期 = dsm.日期
|
||
LEFT JOIN DailySuccessCount dsc ON t.日期 = dsc.日期
|
||
LEFT JOIN HistoryUnfinished hu ON t.日期 = hu.日期
|
||
LEFT JOIN LatestUnfinished lu ON t.日期 = lu.日期
|
||
LEFT JOIN DailyFailedOrders dfo ON t.日期 = dfo.日期
|
||
-- 初始化变量
|
||
CROSS JOIN (SELECT @running_total := 0) AS init
|
||
ORDER BY t.日期
|
||
) AS subquery
|
||
ORDER BY 日期 DESC
|
||
";
|
||
|
||
using (var command = new MySql.Data.MySqlClient.MySqlCommand(sql, connection))
|
||
using (var reader = await command.ExecuteReaderAsync())
|
||
{
|
||
while (await reader.ReadAsync())
|
||
{
|
||
var dto = new DailyLabelStatsChineseDto
|
||
{
|
||
Date = reader["日期"] != DBNull.Value ? Convert.ToDateTime(reader["日期"]).ToString("yyyy-MM-dd") : string.Empty,
|
||
DailyNewReplaceCount = reader["当日新增换单数"] != DBNull.Value ? Convert.ToInt32(reader["当日新增换单数"]) : 0,
|
||
CumulativeTotalReplaceCount = reader["累计要换的总单数"] != DBNull.Value ? Convert.ToInt32(reader["累计要换的总单数"]) : 0,
|
||
UnfinishedFailureCount = reader["换单失败未完结订单"] != DBNull.Value ? Convert.ToInt32(reader["换单失败未完结订单"]) : 0,
|
||
DailyFailureCount = reader["当日换单失败"] != DBNull.Value ? Convert.ToInt32(reader["当日换单失败"]) : 0,
|
||
DailySuccessCount = reader["当日换单成功数"] != DBNull.Value ? Convert.ToInt32(reader["当日换单成功数"]) : 0,
|
||
DailyStopCount = reader["当日STOP数"] != DBNull.Value ? Convert.ToInt32(reader["当日STOP数"]) : 0,
|
||
Rate24Hour = reader["24H换单率"] as string,
|
||
DailyCompletionRate = reader["当天换单完成率"] as string,
|
||
DailyLabelPushCount = reader["当日标签推送数"] != DBNull.Value ? Convert.ToInt32(reader["当日标签推送数"]) : 0,
|
||
DailyScanCount = reader["当日扫描数"] != DBNull.Value ? Convert.ToInt32(reader["当日扫描数"]) : 0,
|
||
DataFetchTime = reader["数据拉取时间（UTC_5）"] != DBNull.Value ? Convert.ToDateTime(reader["数据拉取时间（UTC_5）"]) : DateTime.Now
|
||
};
|
||
result.Add(dto);
|
||
}
|
||
}
|
||
}
|
||
|
||
return result;
|
||
}
|
||
}
|
||
}
|