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