using BBWYB.Common.Log; using BBWYB.Common.Models; using BBWYB.Server.Model; using BBWYB.Server.Model.Db; using FreeSql; using Yitter.IdGenerator; namespace BBWYB.Server.Business { public class AggregionPurchaserBusiness : BaseBusiness, IDenpendency { private TaskSchedulerManager taskSchedulerManager; public AggregionPurchaserBusiness(IFreeSql fsql, NLogManager nLogManager, IIdGenerator idGenerator, TaskSchedulerManager taskSchedulerManager) : base(fsql, nLogManager, idGenerator) { this.taskSchedulerManager = taskSchedulerManager; } public void AutoAggregion() { var startTime = DateTime.Now.Date.AddDays(-90); //查询最近有采购的采购商列表 var purchaserIdList = fsql.Select() .InnerJoin((opi, o) => opi.OrderId == o.Id) .Where((opi, o) => opi.IsEnabled == true && o.OrderState != Enums.OrderState.已取消 && o.StartTime >= startTime && !string.IsNullOrEmpty(opi.PurchaserId)) .Distinct() .ToList((opi, o) => opi.PurchaserId); Task.Factory.StartNew(() => AggregionByGroup(purchaserIdList), CancellationToken.None, TaskCreationOptions.LongRunning, taskSchedulerManager.AggregationPurchaserTaskScheduler); } public void AggregionByPurchaserIdList(IList purchaserIdList) { Task.Factory.StartNew(() => AggregionByGroup(purchaserIdList), CancellationToken.None, TaskCreationOptions.LongRunning, taskSchedulerManager.AggregationPurchaserTaskScheduler); } public void AggregionAllPurchaser() { var purchaserIdList = fsql.Select().ToList(p => p.Id); Task.Factory.StartNew(() => AggregionByGroup(purchaserIdList), CancellationToken.None, TaskCreationOptions.LongRunning, taskSchedulerManager.AggregationPurchaserTaskScheduler); } private void AggregionByGroup(IList purchaserIdList) { var tempPurchaserIdList = new List(); for (var i = 0; i < purchaserIdList.Count(); i++) { tempPurchaserIdList.Add(purchaserIdList[i]); if (i != 0 && i % 30 == 0) { Console.WriteLine($"聚合{i + 1}/{purchaserIdList.Count()}"); Aggregion(tempPurchaserIdList); tempPurchaserIdList.Clear(); Thread.Sleep(2000); } } if (tempPurchaserIdList.Count() > 0) { Console.WriteLine($"最后聚合"); Aggregion(tempPurchaserIdList); tempPurchaserIdList.Clear(); } } private void Aggregion(IList purchaserIdList) { List> updatePurchaserList = new List>(); List deletePeiList = new List(); List insertPeiList = new List(); List dbPeiList = fsql.Select() .Where(pei => pei.ExtendedType == Enums.PurchaserBasicInfoType.主营类目 && purchaserIdList.Contains(pei.PurchaserId)) .ToList(); var pspList = fsql.Select() .InnerJoin((psp, p) => psp.ProductId == p.Id) .Where((psp, p) => p.CategoryId != null && purchaserIdList.Contains(psp.PurchaserId)) .ToList((psp, p) => new { psp.PurchaserId, psp.ProductId, p.CategoryId }); #region 查询SPU绑定数/SKU绑定数 var bindList = fsql.Select() .InnerJoin((psp, psc) => psp.SkuPurchaseSchemeId == psc.Id) .Where((psp, psc) => purchaserIdList.Contains(psp.PurchaserId)) .GroupBy((psp, psc) => new { psp.PurchaserId, psp.ProductId, psp.SkuId }) .ToList(g => new { g.Key.PurchaserId, g.Key.ProductId, g.Key.SkuId }); #endregion #region 查询SPU采购数/SKU采购数 var purchasedList = fsql.Select() .InnerJoin((ps, ori, opi, o) => ps.Id == ori.BelongSkuId) .InnerJoin((ps, ori, opi, o) => ori.PurchaseOrderId == opi.PurchaseOrderId && ori.OrderId == opi.OrderId) .InnerJoin((ps, ori, opi, o) => opi.OrderId == o.Id) .Where((ps, ori, opi, o) => o.OrderState != Enums.OrderState.已取消 && opi.IsEnabled == true && purchaserIdList.Contains(opi.PurchaserId)) .GroupBy((ps, ori, opi, o) => new { opi.PurchaserId, ps.Id, ps.ProductId }) .ToList(g => new { g.Key.PurchaserId, g.Key.ProductId, SkuId = g.Key.Id }); #endregion #region 查询订单数/最近采购时间 var poList = fsql.Select() .InnerJoin((opi, o) => opi.OrderId == o.Id) .Where((opi, o) => opi.IsEnabled == true && o.OrderState != Enums.OrderState.已取消 && purchaserIdList.Contains(opi.PurchaserId)) .GroupBy((opi, o) => opi.PurchaserId) .ToList(g => new { PurchaserId = g.Key, Count = g.Count(), MaxPurchaseTime = g.Max(g.Value.Item1.CreateTime) }); #endregion #region 查询最近90天采购数量 var recent90d = DateTime.Now.Date.AddDays(-90); var recent90dPurchasedCountList = fsql.Select() .InnerJoin((opi, o) => opi.OrderId == o.Id) .Where((opi, o) => o.OrderState != Enums.OrderState.已取消 && opi.IsEnabled == true && opi.CreateTime >= recent90d && purchaserIdList.Contains(opi.PurchaserId)) .GroupBy((opi, o) => opi.PurchaserId) .ToList(g => new { PurchaserId = g.Key, Count = g.Count(), }); #endregion #region 查询采购金额 var purchasedAmountList = fsql.Select() .InnerJoin((ocd, o, opi) => ocd.OrderId == o.Id) .InnerJoin((ocd, o, opi) => o.Id == opi.OrderId) .Where((ocd, o, opi) => o.OrderState != Enums.OrderState.已取消 && ocd.IsEnabled == true && opi.IsEnabled == true && purchaserIdList.Contains(opi.PurchaserId)) .GroupBy((ocd, o, opi) => opi.PurchaserId) .ToList(g => new { PurchaserId = g.Key, PurchasedAmount = g.Sum(g.Value.Item1.SkuAmount + g.Value.Item1.PurchaseFreight) }); #endregion #region 查询最近90天采购金额 var recent90dPurchasedAmountList = fsql.Select() .InnerJoin((ocd, o, opi) => ocd.OrderId == o.Id) .InnerJoin((ocd, o, opi) => o.Id == opi.OrderId) .Where((ocd, o, opi) => o.OrderState != Enums.OrderState.已取消 && ocd.IsEnabled == true && opi.IsEnabled == true && opi.CreateTime >= recent90d && purchaserIdList.Contains(opi.PurchaserId)) .GroupBy((ocd, o, opi) => opi.PurchaserId) .ToList(g => new { PurchaserId = g.Key, PurchasedAmount = g.Sum(g.Value.Item1.SkuAmount + g.Value.Item1.PurchaseFreight) }); #endregion foreach (var purchaserId in purchaserIdList) { #region SPU绑定数/SKU绑定数 var currentBindList = bindList.Where(x => x.PurchaserId == purchaserId).ToList(); var bindingSpuCount = currentBindList.Select(x => x.ProductId).Distinct().Count(); var bindingSkuCount = currentBindList.Select(x => x.SkuId).Count(); #endregion #region SPU采购数/SKU采购数 var currentPurchasedList = purchasedList.Where(x => x.PurchaserId == purchaserId).ToList(); var purchasedSpuCount = currentPurchasedList.Select(x => x.ProductId).Distinct().Count(); var purchasedSkuCount = currentPurchasedList.Select(x => x.SkuId).Count(); #endregion #region 订单数/最近采购时间 var purchasedCountAndTime = poList.FirstOrDefault(x => x.PurchaserId == purchaserId); var purchasedCount = purchasedCountAndTime?.Count ?? 0; var lastPurchasedTime = purchasedCountAndTime?.MaxPurchaseTime; #endregion #region 采购金额 var purchasedAmount = purchasedAmountList.FirstOrDefault(x => x.PurchaserId == purchaserId)?.PurchasedAmount ?? 0; #endregion #region 最近90天采购金额 var recent90dPurchasedAmount = recent90dPurchasedAmountList.FirstOrDefault(x => x.PurchaserId == purchaserId)?.PurchasedAmount ?? 0; #endregion #region 最近90天采购数量 var recent90dPurchasedCount = recent90dPurchasedCountList.FirstOrDefault(x => x.PurchaserId == purchaserId)?.Count ?? 0; #endregion #region 主营类目 { var currentDbPeiList = dbPeiList.Where(pei => pei.PurchaserId == purchaserId).ToList(); var currentDbPspList = pspList.Where(psp => psp.PurchaserId == purchaserId).ToList(); var currentDeletePeiList = currentDbPeiList.Where(pei => !currentDbPspList.Any(psp => psp.PurchaserId == pei.PurchaserId && psp.CategoryId == pei.ExtendedInfoId)) .Select(pei => pei.Id).ToList(); if (currentDeletePeiList.Count() > 0) deletePeiList.AddRange(currentDeletePeiList); var currentInsertPeiList = currentDbPspList.Where(psp => !currentDbPeiList.Any(pei => pei.PurchaserId == psp.PurchaserId && pei.ExtendedInfoId == psp.CategoryId)) .GroupBy(psp => psp.CategoryId) .Select(g => new Purchaser_ExtendedInfo_Relation() { Id = idGenerator.NewLong(), ExtendedInfoId = g.Key, ExtendedType = Enums.PurchaserBasicInfoType.主营类目, PurchaserId = purchaserId }).ToList(); if (currentInsertPeiList.Count() > 0) insertPeiList.AddRange(currentInsertPeiList); } #endregion var update = fsql.Update(purchaserId) .Set(p => p.BindingSpuCount, bindingSpuCount) .Set(p => p.BindingSkuCount, bindingSkuCount) .Set(p => p.PurchasedSpuCount, purchasedSpuCount) .Set(p => p.PurchasedSkuCount, purchasedSkuCount) .Set(p => p.PurchasedCount, purchasedCount) .Set(p => p.PurchasedAmount, purchasedAmount) .Set(p => p.LastPurchasedTime, lastPurchasedTime) .Set(p => p.Recent90dPurchasedAmount, recent90dPurchasedAmount) .Set(p => p.Recent90dPurchasedCount, recent90dPurchasedCount); updatePurchaserList.Add(update); } fsql.Transaction(() => { foreach (var update in updatePurchaserList) update.ExecuteAffrows(); if (deletePeiList.Count() > 0) fsql.Delete(deletePeiList).ExecuteAffrows(); if (insertPeiList.Count() > 0) fsql.Insert(insertPeiList).ExecuteAffrows(); }); } } }