private bool CreateReport(StatisticType statisticType, int statisticValue, int year) { using (var db = new OMSContext()) { var lst = db.OrderPandianProductInfoSet.Where(s => s.MonthNum == statisticValue && s.Year == year).ToList(); var q = lst.GroupBy(p => new { p.ProductPlatName, p.sku, p.weightCode, p.weightCodeDesc, p.MonthNum }); List <StatisticProduct> smp = new List <StatisticProduct>(); foreach (var item in q) { StatisticProduct statistic = new StatisticProduct() { Source = ServerName, SourceDesc = Util.Helpers.Reflection.GetDescription <OrderSource>(ServerName), Year = year, ProductPlatName = item.Key.ProductPlatName, ProductTotalAmount = item.Sum(o => o.TotalAmount), weightCode = item.Key.weightCode, weightCodeDesc = item.Key.weightCodeDesc, ProductTotalWeight = item.Sum(o => o.ProductWeight) / 1000, ProductCount = item.Sum(o => o.ProductCount), CreateDate = DateTime.Now, StatisticType = (int)StatisticType.Month, StatisticValue = statisticValue }; smp.Add(statistic); } if (Environment.UserInteractive) { foreach (var item in smp) { Console.WriteLine($"{item.SourceDesc}\t{item.ProductPlatName}\t{item.weightCodeDesc}\t{item.ProductTotalWeight}\t{item.ProductTotalAmount}\t{item.ProductCount}"); } } var removeobj = db.StatisticProductSet.Where(s => s.Year == year && s.Source == ServerName && s.StatisticType == (int)StatisticType.Month && s.StatisticValue == statisticValue); db.Set <StatisticProduct>().RemoveRange(removeobj); db.SaveChanges(); db.Set <StatisticProduct>().AddRange(smp); db.SaveChanges(); } return(true); }
private bool CreateReport(StatisticType statisticType, int statisticValue, int year) { try { using (var db = new OMSContext()) { List <OrderEntity> orderEntities = new List <OrderEntity>(); switch (statisticType) { case StatisticType.Day: DateTime start = new DateTime(year, 1, 1).AddDays(statisticValue - 1); DateTime end = start.AddDays(1); orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => s.OrderDateInfo.CreateTime >= start && s.OrderDateInfo.CreateTime < end && s.CreatedDate.Year == year).ToList(); break; case StatisticType.Week: orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => s.OrderDateInfo.WeekNum == statisticValue && s.CreatedDate.Year == year).ToList(); break; case StatisticType.Month: orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => s.OrderDateInfo.MonthNum == statisticValue && s.CreatedDate.Year == year).ToList(); break; case StatisticType.Quarter: orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => s.OrderDateInfo.SeasonNum == statisticValue && s.CreatedDate.Year == year).ToList(); break; case StatisticType.Year: orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => s.OrderDateInfo.Year == statisticValue && s.Source == ServerName).ToList(); break; default: break; } if (ServerName != OrderSource.ALL) { orderEntities = orderEntities.Where(o => o.Source == ServerName).ToList(); } if (orderEntities.Any()) { var plst = orderEntities.SelectMany(o => o.Products); var q = plst.GroupBy(p => new { p.ProductPlatName, p.weightCode, p.weightCodeDesc, p.sku }); List <StatisticProduct> smp = new List <StatisticProduct>(); foreach (var item in q) { StatisticProduct statistic = new StatisticProduct() { Source = ServerName, SourceDesc = Util.Helpers.Reflection.GetDescription <OrderSource>(ServerName), Year = year, ProductPlatName = item.Key.ProductPlatName, ProductTotalAmount = item.Sum(o => o.TotalAmount), weightCode = item.Key.weightCode, weightCodeDesc = item.Key.weightCodeDesc, ProductTotalWeight = item.Sum(o => o.ProductWeight) / 1000, ProductCount = item.Sum(o => o.ProductCount), CreateDate = DateTime.Now, StatisticType = (int)statisticType, StatisticValue = statisticValue, TotalCustomers = orderEntities.GroupBy(o => o.Consignee).ToList().Count(),//总计客户数量 TotalOrders = orderEntities.Count() }; switch (statisticType) { case StatisticType.Day: statistic.TotalOrderRepurchase = orderEntities.Where(o => o.OrderRepurchase.DailyRepurchase == true).Count(); statistic.TotalCustomerRepurchase = orderEntities.Where(o => o.OrderRepurchase.DailyRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); var daylst = orderEntities.Where(o => o.OrderRepurchase.DailyRepurchase == true).SelectMany(o => o.Products).GroupBy(o => o.ProductPlatName); var d = daylst.FirstOrDefault(g => g.Key == item.Key.ProductPlatName); if (d != null) { statistic.TotalProductRepurchase = d.Sum(o => o.ProductCount); } else { statistic.TotalProductRepurchase = 0; } break; case StatisticType.Week: statistic.TotalOrderRepurchase = orderEntities.Where(o => o.OrderRepurchase.WeekRepurchase == true).Count(); statistic.TotalCustomerRepurchase = orderEntities.Where(o => o.OrderRepurchase.WeekRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); var weeklst = orderEntities.Where(o => o.OrderRepurchase.WeekRepurchase == true).SelectMany(o => o.Products).GroupBy(o => o.ProductPlatName); var w = weeklst.FirstOrDefault(g => g.Key == item.Key.ProductPlatName); if (w != null) { statistic.TotalProductRepurchase = w.Sum(o => o.ProductCount); } else { statistic.TotalProductRepurchase = 0; } break; case StatisticType.Month: statistic.TotalOrderRepurchase = orderEntities.Where(o => o.OrderRepurchase.MonthRepurchase == true).Count(); statistic.TotalCustomerRepurchase = orderEntities.Where(o => o.OrderRepurchase.MonthRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); var monthlst = orderEntities.Where(o => o.OrderRepurchase.WeekRepurchase == true).SelectMany(o => o.Products).GroupBy(o => o.ProductPlatName); var m = monthlst.FirstOrDefault(g => g.Key == item.Key.ProductPlatName); if (m != null) { statistic.TotalProductRepurchase = m.Sum(o => o.ProductCount); } else { statistic.TotalProductRepurchase = 0; } break; case StatisticType.Quarter: statistic.TotalOrderRepurchase = orderEntities.Where(o => o.OrderRepurchase.SeasonRepurchase == true).Count(); statistic.TotalCustomerRepurchase = orderEntities.Where(o => o.OrderRepurchase.SeasonRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); var seasonlst = orderEntities.Where(o => o.OrderRepurchase.WeekRepurchase == true).SelectMany(o => o.Products).GroupBy(o => o.ProductPlatName); var s = seasonlst.FirstOrDefault(g => g.Key == item.Key.ProductPlatName); if (s != null) { statistic.TotalProductRepurchase = s.Sum(o => o.ProductCount); } else { statistic.TotalProductRepurchase = 0; } break; case StatisticType.Year: statistic.TotalOrderRepurchase = orderEntities.Where(o => o.OrderRepurchase.YearRepurchase == true).Count(); statistic.TotalCustomerRepurchase = orderEntities.Where(o => o.OrderRepurchase.YearRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); var yearlst = orderEntities.Where(o => o.OrderRepurchase.WeekRepurchase == true).SelectMany(o => o.Products).GroupBy(o => o.ProductPlatName); var y = yearlst.FirstOrDefault(g => g.Key == item.Key.ProductPlatName); if (y != null) { statistic.TotalProductRepurchase = y.Sum(o => o.ProductCount); } else { statistic.TotalProductRepurchase = 0; } break; default: break; } smp.Add(statistic); } foreach (var item in smp) { OnUIMessageEventHandle($"{item.SourceDesc}\t{item.ProductPlatName}\t{item.weightCodeDesc}\t{item.ProductTotalWeight}\t{item.ProductTotalAmount}\t{item.ProductCount}"); } var removeobj = db.StatisticProductSet.Where(s => s.Year == year && s.Source == ServerName && s.StatisticType == (int)statisticType && s.StatisticValue == statisticValue); db.Set <StatisticProduct>().RemoveRange(removeobj); db.SaveChanges(); db.Set <StatisticProduct>().AddRange(smp); db.SaveChanges(); } else { return(false); } return(true); } } catch (Exception ex) { Util.Logs.Log.GetLog(nameof(ProductStatisticServerBase)).Error($"{ServerName}:error:{ex.Message},statck:{ex.StackTrace}"); return(false); } }
private bool CreateReport(StatisticType statisticType, int statisticValue, int year) { OnUIMessageEventHandle($"{ServerName}-{statisticType.ToString()}-{statisticValue}-商品报表开始统计"); try { using (var db = new OMSContext()) { db.Database.CommandTimeout = 600000; List <OrderEntity> orderEntities = new List <OrderEntity>(); switch (statisticType) { case StatisticType.Day: DateTime start = new DateTime(year, 1, 1).AddDays(statisticValue - 1); DateTime end = start.AddDays(1); orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => (s.OrderType & 1) == 0 && (s.OrderType & 4) == 0 && s.OrderDateInfo.CreateTime >= start && s.OrderDateInfo.CreateTime < end && s.CreatedDate.Year == year).ToList(); break; case StatisticType.Week: orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => (s.OrderType & 1) == 0 && (s.OrderType & 4) == 0 && s.OrderDateInfo.WeekNum == statisticValue && s.CreatedDate.Year == year).ToList(); break; case StatisticType.Month: orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => (s.OrderType & 1) == 0 && (s.OrderType & 4) == 0 && s.OrderDateInfo.MonthNum == statisticValue && s.CreatedDate.Year == year).ToList(); break; case StatisticType.Quarter: orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => (s.OrderType & 1) == 0 && (s.OrderType & 4) == 0 && s.OrderDateInfo.SeasonNum == statisticValue && s.CreatedDate.Year == year).ToList(); break; case StatisticType.Year: orderEntities = db.OrderSet.Include(o => o.Consignee).Include(o => o.OrderDateInfo).Include(o => o.OrderRepurchase).Include(o => o.Products).Where(s => (s.OrderType & 1) == 0 && (s.OrderType & 4) == 0 && s.OrderDateInfo.Year == statisticValue).ToList(); break; default: break; } if (ServerName != OrderSource.ALL) { orderEntities = orderEntities.Where(o => o.Source == ServerName).ToList(); } if (orderEntities.Any()) { var plst = orderEntities.SelectMany(o => o.Products).Where(o => o.sku != null); var q = plst.GroupBy(p => new { p.weightCode, p.sku }); List <StatisticProduct> smp = new List <StatisticProduct>(); foreach (var item in q) { var lst = orderEntities.Where(o => o.Products.Any(p => p.sku == item.Key.sku)); StatisticProduct statistic = new StatisticProduct() { Source = ServerName, SourceDesc = Util.Helpers.Reflection.GetDescription <OrderSource>(ServerName), Year = year, ProductPlatName = db.ProductsSet.Find(item.Key.sku)?.ShortName ?? db.ProductsSet.Find(item.Key.sku)?.Name, SKU = item.Key.sku, ProductTotalAmount = item.Sum(o => o.TotalAmount), ProductTotalCostAmount = item.Sum(o => o.TotalCostPrice), ProductTotalFlatAmount = item.Sum(o => o.TotalFlatAmount), weightCode = item.Key.weightCode, weightCodeDesc = db.WeightCodeSet.Find(item.Key.weightCode)?.Value.ToString(), ProductTotalWeight = item.Sum(o => o.ProductWeight) / 1000, ProductCount = item.Sum(o => o.ProductCount), CreateDate = DateTime.Now, StatisticType = (int)statisticType, StatisticValue = statisticValue, TotalCustomers = lst.GroupBy(o => o.Consignee).Count(),//总计客户数量 TotalOrders = lst.Count() }; //复购统计 switch (statisticType) { case StatisticType.Day: DateTime start = new DateTime(year, 1, 1).AddDays(statisticValue - 1); statistic.CreateDate = start; statistic.TotalOrderRepurchase = lst.Where(o => o.OrderRepurchase.DailyRepurchase == true).Count(); statistic.TotalCustomerRepurchase = lst.Where(o => o.OrderRepurchase.DailyRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); statistic.TotalProductRepurchase = lst.Where(o => o.OrderRepurchase.DailyRepurchase == true).SelectMany(o => o.Products.Where(P => P.sku == item.Key.sku)).Sum(p => p.ProductCount); break; case StatisticType.Week: statistic.TotalOrderRepurchase = lst.Where(o => o.OrderRepurchase.WeekRepurchase == true).Count(); statistic.TotalCustomerRepurchase = lst.Where(o => o.OrderRepurchase.WeekRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); statistic.TotalProductRepurchase = lst.Where(o => o.OrderRepurchase.WeekRepurchase == true).SelectMany(o => o.Products.Where(P => P.sku == item.Key.sku)).Sum(p => p.ProductCount); break; case StatisticType.Month: statistic.TotalOrderRepurchase = lst.Where(o => o.OrderRepurchase.MonthRepurchase == true).Count(); statistic.TotalCustomerRepurchase = lst.Where(o => o.OrderRepurchase.MonthRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); statistic.TotalProductRepurchase = lst.Where(o => o.OrderRepurchase.MonthRepurchase == true).SelectMany(o => o.Products.Where(P => P.sku == item.Key.sku)).Sum(p => p.ProductCount); break; case StatisticType.Quarter: statistic.TotalOrderRepurchase = lst.Where(o => o.OrderRepurchase.SeasonRepurchase == true).Count(); statistic.TotalCustomerRepurchase = lst.Where(o => o.OrderRepurchase.SeasonRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); statistic.TotalProductRepurchase = lst.Where(o => o.OrderRepurchase.SeasonRepurchase == true).ToList().SelectMany(o => o.Products.Where(P => P.sku == item.Key.sku)).Sum(p => p.ProductCount); break; case StatisticType.Year: statistic.TotalOrderRepurchase = lst.Where(o => o.OrderRepurchase.YearRepurchase == true).Count(); statistic.TotalCustomerRepurchase = lst.Where(o => o.OrderRepurchase.YearRepurchase == true).GroupBy(o => o.Consignee).ToList().Count(); statistic.TotalProductRepurchase = lst.Where(o => o.OrderRepurchase.YearRepurchase == true).SelectMany(o => o.Products.Where(P => P.sku == item.Key.sku)).Sum(p => p.ProductCount); break; default: break; } smp.Add(statistic); } //foreach (var item in smp) //{ // OnUIMessageEventHandle($"{item.SourceDesc}\t{item.ProductPlatName}\t{item.weightCodeDesc}\t{item.ProductTotalWeight}\t{item.ProductTotalAmount}\t{item.ProductCount}"); //} var removeobj = db.StatisticProductSet.Where(s => s.Year == year && s.Source == ServerName && s.StatisticType == (int)statisticType && s.StatisticValue == statisticValue); if (removeobj.Any()) { //bool saveFailed; //do //{ // saveFailed = false; // try // { // db.SaveChanges(); // } // catch (Db ex) // { // saveFailed = true; // // Update original values from the database // var entry = ex.Entries.Single(); // entry.OriginalValues.SetValues(entry.GetDatabaseValues()); // } //} while (saveFailed); db.Set <StatisticProduct>().RemoveRange(removeobj); db.SaveChanges(); } db.Set <StatisticProduct>().AddRange(smp); db.SaveChanges(); OnUIMessageEventHandle($"{ServerName}-{statisticType.ToString()}-{statisticValue}-商品统计完毕"); } else { OnUIMessageEventHandle($"{ServerName}-{statisticType.ToString()}-{statisticValue}-产品方面无统计结果"); return(false); } return(true); } } catch (Exception ex) { Util.Logs.Log.GetLog(nameof(ProductStatisticServerBase)).Error($"{ServerName}:error:{ex.Message},statck:{ex.StackTrace}"); return(false); } }