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);
}
}