public void ExportSellDetail(string sourcePath) { var ds = ExcelHelper.GetExcelDataSet(sourcePath); var dtOrderList = ds.Tables["ExportOrderList"]; var dtOrderDetailList = ds.Tables["ExportOrderDetailList"]; var query = from l in dtOrderList.AsEnumerable() join d in dtOrderDetailList.AsEnumerable() on l["订单编号"] equals d["订单编号"] where d["订单状态"].ToString() != "交易关闭" && d["订单状态"].ToString() != "等待买家付款" select new { OrderNo = l["订单编号"], CreateTime = l["订单创建时间"], GoodsSn = d["外部系统编号"], Props = d["商品属性"], SalePrice = l["买家应付货款"], Postage = l["买家应付邮费"], TotalPrice = l["总金额"], OrderState = l["订单状态"], UserName = l["买家会员名"], Remark = l["订单备注"], Price = d["价格"], Count = d["购买数量"], Title = d["标题"] }; string sheetName = "销售记录"; var excel = CreateExcelForSell(sheetName); DataTable dt = excel.ReadTable(sheetName); #region 排除重复数据,找出拍了2件及以上的客户 var repeat = query.GroupBy(i => i.OrderNo).Where(g => g.Count() > 1); var notRepeat = query.ToList(); foreach (var rep in repeat) { var lstRep = rep.ToList(); var repCount = lstRep.Sum(order => order.Count.ToType<int>()); //购买的产品实际总数,不只是订单数 var singleAvgPrice = decimal.Round((decimal)(lstRep[0].TotalPrice.ToType<double>() / repCount),2); //获取他的备注信息,检查是否有';'号分割不同的来源 var sources = GetOtherSource(lstRep); for (int index = 0; index < lstRep.Count; index++) { var c = lstRep[index]; notRepeat.Remove(c); //对一个组合订单下的某个产品包含购买了几件情况 for (int j = 0; j < c.Count.ToType<int>(); j++) { var exportModel = new ExportModel(c); exportModel.Count = 1; exportModel.TotalPrice = (double)singleAvgPrice; this.AddDataRow( exportModel, dt, excel, sources == null ? repCount : 1, //如果压货来源不是同一个来源那么付款金额就是每个备注中的价格 sources == null ? null : sources[index+j]); } } } #endregion //只购买了一个订单的产品,有可能一个订单下买了几件相同的产品 foreach (var q in notRepeat) { if (string.IsNullOrEmpty(q.Remark.ToString())) { continue; } var lstRep = new[]{ q }; var sources = GetOtherSource(lstRep); var orderCount = q.Count.ToType<int>(); for (int i = 0; i < orderCount; i++) { var exportModel = new ExportModel(q); exportModel.Count = 1; exportModel.TotalPrice = q.TotalPrice.ToType<double>() / q.Count.ToType<int>(); AddDataRow(exportModel, dt, excel, orderCount, sources == null ? null : sources[i]); } } excel.Dispose(); Process.Start("Sell"); }
public void ExportSellDetail(string sourcePath) { var ds = ExcelHelper.GetExcelDataSet(sourcePath); var dtOrderList = ds.Tables["ExportOrderList"]; var dtOrderDetailList = ds.Tables["ExportOrderDetailList"]; var query = from l in dtOrderList.AsEnumerable() join d in dtOrderDetailList.AsEnumerable() on l["订单编号"] equals d["订单编号"] where d["订单状态"].ToString() != "交易关闭" && d["订单状态"].ToString() != "等待买家付款" select new { OrderNo = l["订单编号"], CreateTime = l["订单创建时间"], GoodsSn = d["外部系统编号"], Props = d["商品属性"], SalePrice = l["买家应付货款"], Postage = l["买家应付邮费"], TotalPrice = l["总金额"], OrderState = l["订单状态"], UserName = l["买家会员名"], Remark = l["订单备注"], Price = d["价格"], Count = d["购买数量"], Title = d["标题"] }; string sheetName = "销售记录"; var excel = CreateExcelForSell(sheetName); DataTable dt = excel.ReadTable(sheetName); #region 排除重复数据,找出拍了2件及以上的客户 var repeat = query.GroupBy(i => i.OrderNo).Where(g => g.Count() > 1); var notRepeat = query.ToList(); foreach (var rep in repeat) { var lstRep = rep.ToList(); var repCount = lstRep.Sum(order => order.Count.ToType <int>()); //购买的产品实际总数,不只是订单数 var singleAvgPrice = decimal.Round((decimal)(lstRep[0].TotalPrice.ToType <double>() / repCount), 2); //获取他的备注信息,检查是否有';'号分割不同的来源 var sources = GetOtherSource(lstRep); for (int index = 0; index < lstRep.Count; index++) { var c = lstRep[index]; notRepeat.Remove(c); //对一个组合订单下的某个产品包含购买了几件情况 for (int j = 0; j < c.Count.ToType <int>(); j++) { var exportModel = new ExportModel(c); exportModel.Count = 1; exportModel.TotalPrice = (double)singleAvgPrice; this.AddDataRow( exportModel, dt, excel, sources == null ? repCount : 1, //如果压货来源不是同一个来源那么付款金额就是每个备注中的价格 sources == null ? null : sources[index + j]); } } } #endregion //只购买了一个订单的产品,有可能一个订单下买了几件相同的产品 foreach (var q in notRepeat) { if (string.IsNullOrEmpty(q.Remark.ToString())) { continue; } var lstRep = new[] { q }; var sources = GetOtherSource(lstRep); var orderCount = q.Count.ToType <int>(); for (int i = 0; i < orderCount; i++) { var exportModel = new ExportModel(q); exportModel.Count = 1; exportModel.TotalPrice = q.TotalPrice.ToType <double>() / q.Count.ToType <int>(); AddDataRow(exportModel, dt, excel, orderCount, sources == null ? null : sources[i]); } } excel.Dispose(); Process.Start("Sell"); }