//读取库存报告模板并另存报告,返回完整储存路径
public void GenerateFBAInventoryReport(FBAInventoryInfo info)
{
_ws = _wb.Worksheets[1];
_ws.Cells[4, 2] = info.Customer;
_ws.Cells[4, 4] = info.CloseDate.ToString("yyyy-MM-dd");
_ws.Cells[6, 2] = info.CurrentPlts;
_ws.Cells[6, 4] = info.TotalResidualQuantity;
_ws.Cells[6, 6] = info.CurrentLooseCtns;
_ws.Cells[6, 8] = info.TotalResidualQuantity - info.CurrentLooseCtns;
var startRow = 9;
foreach (var i in info.FBAResidualInventories)
{
_ws.Cells[startRow, 1] = i.Container;
_ws.Cells[startRow, 2] = i.Type;
_ws.Cells[startRow, 3] = i.ShipmentId;
_ws.Cells[startRow, 4] = i.AmzRefId;
_ws.Cells[startRow, 5] = i.WarehouseCode;
_ws.Cells[startRow, 6] = Math.Round(i.GrossWeightPerCtn, 2);
_ws.Cells[startRow, 7] = Math.Round(i.CBMPerCtn, 2);
_ws.Cells[startRow, 8] = i.OriginalQuantity;
_ws.Cells[startRow, 9] = Math.Round((double)i.ResidualQuantity, 2);
_ws.Cells[startRow, 10] = i.Location;
startRow += 1;
}
var fullPath = @"D:\InventoryReport\FBA-" + info.Customer + "-InventoryReport-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xls";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
//使用主线程调用委托，等于用主线程调用Quick()方法，以达到阻塞线程的目的
var handler = new QuitHandler(_excel.Quit);
handler.Invoke();
var response = HttpContext.Current.Response;
var downloadFile = new FileInfo(fullPath);
response.ClearHeaders();
response.Buffer = false;
response.ContentType = "application/octet-stream";
response.AppendHeader("Content-Disposition", "attachment; filename=" + info.Customer + " Inventory Report - " + HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls", System.Text.Encoding.UTF8));
response.Clear();
response.AppendHeader("Content-Length", downloadFile.Length.ToString());
response.WriteFile(downloadFile.FullName);
response.Flush();
response.Close();
response.End();
var killer = new ExcelKiller();
killer.Dispose();
}
//输入截止日期和客户代码，返回到截止日期时FBA的库存列表
public FBAInventoryInfo GetFBAInventoryResidualInfo(string customerCode, DateTime startDate, DateTime closeDate)
{
var residualInventoryList = new List <FBACtnInventory>();
//获取在指定日期之前入库的总箱数库存列表
var cartonLocationsInDb = _context.FBACartonLocations
.Include(x => x.FBAPallet.FBAPalletLocations)
.Include(x => x.FBAOrderDetail.FBAMasterOrder.Customer)
.Include(x => x.FBAPickDetailCartons
.Select(c => c.FBAPickDetail.FBAShipOrder))
.Include(x => x.FBAPickDetails
.Select(c => c.FBAShipOrder))
.Where(x => x.FBAOrderDetail.FBAMasterOrder.InboundDate < closeDate &&
x.FBAOrderDetail.FBAMasterOrder.InboundDate >= startDate &&
x.FBAOrderDetail.FBAMasterOrder.Customer.CustomerCode == customerCode);
//获取在指定日期前入库的托盘库存列表
var palletLocationsInDb = _context.FBAPalletLocations
.Include(x => x.FBAMasterOrder.Customer)
.Include(x => x.FBAPickDetails.Select(c => c.FBAShipOrder))
.Where(x => x.FBAMasterOrder.InboundDate < closeDate &&
x.FBAMasterOrder.InboundDate >= startDate &&
x.FBAMasterOrder.Customer.CustomerCode == customerCode);
var palletLocationsList = palletLocationsInDb.ToList();
var cartonLocationList = cartonLocationsInDb.ToList();
var originalPlts = palletLocationsList.Sum(x => x.ActualPlts);
var originalLooseCtns = cartonLocationList.Where(x => x.Location != "Pallet").Sum(x => x.ActualQuantity);
var currentLooseCtns = originalLooseCtns;
var pltViewList = new List <FBAPalletGroupInventory>();
var totalPickingPlts = 0;
//计算原有托盘数减去所有发出的托盘数
foreach (var plt in palletLocationsList)
{
var currentPickingPlt = 0;
var currentOriginalPlts = plt.ActualPlts;
foreach (var pick in plt.FBAPickDetails)
{
//一旦客户下单，库存报告中就会扣掉库存，跟计算仓储费的时间点不一样（仓储费按照实际出库时间计算）
if (pick.FBAShipOrder.PlaceTime < closeDate && pick.FBAShipOrder.PlaceTime.Year != 1900)
{
plt.ActualPlts -= pick.PltsFromInventory;
// 所以还没有被标记为Shipped或ShippedDate大于查询截止日期的货物，被视为正在处理processing的货物
//if (pick.FBAShipOrder.Status != FBAStatus.Shipped || pick.FBAShipOrder.ReleasedDate >= closeDate)
if (pick.FBAShipOrder.ShipDate.Year == 1900 || pick.FBAShipOrder.ShipDate >= closeDate)
{
totalPickingPlts += pick.PltsFromInventory;
currentPickingPlt += pick.PltsFromInventory;
}
}
}
if (currentPickingPlt != 0 || plt.ActualPlts != 0)
{
pltViewList.Add(new FBAPalletGroupInventory
{
PltId = plt.Id,
SubCustomer = plt.FBAMasterOrder.SubCustomer,
Container = plt.Container,
AmzRefId = plt.AmzRefId,
ShipmentId = plt.ShipmentId,
ActualPlts = currentOriginalPlts,
PickingPlts = currentPickingPlt,
WarehouseLocation = plt.FBAMasterOrder.WarehouseLocation,
AvailablePlts = plt.ActualPlts,
Location = plt.Location,
PalletSize = plt.PalletSize,
InboundDate = plt.FBAMasterOrder.InboundDate,
Memo = plt.Memo
});
}
}
var totalPickingCtns = 0;
//计算原有箱数减去每次发出的箱数并放到列表中
foreach (var cartonLocation in cartonLocationsInDb)
{
var originalQuantity = cartonLocation.ActualQuantity;
var currentPickingCtns = 0;
if (cartonLocation.Location == FBAInventoryType.Pallet)
{
foreach (var pickCarton in cartonLocation.FBAPickDetailCartons)
{
//获取在指定日期前的相关拣货列表
if (pickCarton.FBAPickDetail.FBAShipOrder.PlaceTime < closeDate && pickCarton.FBAPickDetail.FBAShipOrder.PlaceTime.Year != 1900)
{
cartonLocation.ActualQuantity -= pickCarton.PickCtns;
if (pickCarton.FBAPickDetail.FBAShipOrder.ShipDate.Year == 1900 || pickCarton.FBAPickDetail.FBAShipOrder.ShipDate >= closeDate)
{
totalPickingCtns += pickCarton.PickCtns;
currentPickingCtns += pickCarton.PickCtns;
}
}
}
}
else //直接与拣货单
{
foreach (var pickCarton in cartonLocation.FBAPickDetails)
{
if (pickCarton.FBAShipOrder.PlaceTime < closeDate && pickCarton.FBAShipOrder.PlaceTime.Year != 1900)
{
cartonLocation.ActualQuantity -= pickCarton.ActualQuantity;
currentLooseCtns -= pickCarton.ActualQuantity;
if (pickCarton.FBAShipOrder.ShipDate.Year == 1900 || pickCarton.FBAShipOrder.ShipDate >= closeDate)
{
totalPickingCtns += pickCarton.ActualQuantity;
currentPickingCtns += pickCarton.ActualQuantity;
}
}
}
}
if (cartonLocation.ActualQuantity != 0 || currentPickingCtns != 0)
{
var ctnInventory = new FBACtnInventory
{
SubCustomer = cartonLocation.FBAOrderDetail.FBAMasterOrder.SubCustomer,
Id = cartonLocation.Id,
Container = cartonLocation.Container,
Type = cartonLocation.Location == "Pallet" ? FBAStatus.InPallet : FBAStatus.LooseCtn,
ShipmentId = cartonLocation.ShipmentId,
AmzRefId = cartonLocation.AmzRefId,
HoldQuantity = cartonLocation.HoldCtns,
WarehouseCode = cartonLocation.WarehouseCode,
GrossWeightPerCtn = cartonLocation.GrossWeightPerCtn,
CBMPerCtn = cartonLocation.CBMPerCtn,
PickingCtns = currentPickingCtns,
WarehouseLocation = cartonLocation.FBAOrderDetail.FBAMasterOrder.WarehouseLocation,
ResidualCBM = cartonLocation.CBMPerCtn * cartonLocation.ActualQuantity,
ResidualQuantity = cartonLocation.ActualQuantity - cartonLocation.HoldCtns,
OriginalQuantity = originalQuantity,
InboundDate = cartonLocation.FBAOrderDetail.FBAMasterOrder.InboundDate,
Memo = cartonLocation.Memo,
Location = cartonLocation.Location == "Pallet" ? CombineLocation(cartonLocation.FBAPallet.FBAPalletLocations.Select(x => x.Location).ToList()) : cartonLocation.Location,
};
residualInventoryList.Add(ctnInventory);
if (cartonLocation.Location == FBAInventoryType.Pallet)
{
if (!cartonLocation.FBAPallet.FBAPalletLocations.Any())
{
throw new Exception("Unallocated pallets detected. Please check container: " + cartonLocation.Container + " and try again after all pallets are fully allocated");
}
var pltId = cartonLocation.FBAPallet.FBAPalletLocations.First().Id;
if (pltViewList.SingleOrDefault(x => x.PltId == pltId) != null)
{
pltViewList.SingleOrDefault(x => x.PltId == pltId).InPalletCtnInventories.Add(ctnInventory);
}
}
}
}
var info = new FBAInventoryInfo();
info.FBACtnInventories = residualInventoryList;
info.FBAPalletGroupInventories = pltViewList;
info.Customer = customerCode;
info.OriginalTotalPlts = originalPlts;
info.OriginalLooseCtns = originalLooseCtns;
info.CurrentLooseCtns = currentLooseCtns;
info.CurrentTotalPlts = palletLocationsList.Sum(x => x.ActualPlts);
info.TotalPickingPlts = totalPickingPlts;
info.CurrentTotalCtns = residualInventoryList.Sum(x => x.ResidualQuantity);
info.TotalPickingCtns = totalPickingCtns;
info.TotalInPalletCtns = (int)info.CurrentTotalCtns - info.CurrentLooseCtns;
info.TotalResidualCBM = residualInventoryList.Sum(x => x.ResidualCBM);
info.CloseDate = closeDate.AddDays(-1); // 减去一天，是真正的截止日期
info.StartDate = startDate;
return(info);
}
public string GenerateAndReturnFBAInventoryReportPath(FBAInventoryInfo info)
{
_ws = _wb.Worksheets[1];
_ws.Cells[4, 2] = info.Customer;
_ws.Cells[4, 4] = DateTime.Now.ToString("yyyy-MM-dd");
_ws.Cells[4, 6] = info.CurrentTotalCtns - info.CurrentLooseCtns;
_ws.Cells[4, 8] = info.CurrentLooseCtns;
_ws.Cells[4, 10] = info.StartDate.ToString("yyyy-MM-dd");
_ws.Cells[4, 12] = info.CloseDate.ToString("yyyy-MM-dd");
_ws.Cells[6, 2] = info.CurrentTotalPlts;
_ws.Cells[6, 4] = info.TotalPickingPlts;
_ws.Cells[6, 6] = info.CurrentTotalCtns;
_ws.Cells[6, 8] = info.TotalPickingCtns;
var startRow = 9;
foreach (var i in info.FBACtnInventories)
{
_ws.Cells[startRow, 1] = i.Container;
_ws.Cells[startRow, 2] = i.SubCustomer;
_ws.Cells[startRow, 3] = i.Type;
_ws.Cells[startRow, 4] = i.ShipmentId;
_ws.Cells[startRow, 5] = i.AmzRefId;
_ws.Cells[startRow, 6] = i.WarehouseCode;
_ws.Cells[startRow, 7] = Math.Round(i.GrossWeightPerCtn, 2);
_ws.Cells[startRow, 8] = Math.Round(i.CBMPerCtn, 2);
_ws.Cells[startRow, 9] = i.OriginalQuantity;
_ws.Cells[startRow, 10] = i.PickingCtns;
_ws.Cells[startRow, 11] = Math.Round((double)i.ResidualQuantity, 2);
_ws.Cells[startRow, 12] = Math.Round((double)i.HoldQuantity, 2);
_ws.Cells[startRow, 13] = i.Location;
_ws.Cells[startRow, 14] = i.WarehouseLocation;
_ws.Cells[startRow, 15] = i.InboundDate.ToString("yyyy-MM-dd");
startRow += 1;
}
_ws.get_Range("A1:O" + startRow, Type.Missing).HorizontalAlignment = XlVAlign.xlVAlignCenter;
_ws.get_Range("A1:O" + startRow, Type.Missing).VerticalAlignment = XlVAlign.xlVAlignCenter;
_ws.get_Range("A1:O" + startRow, Type.Missing).Borders.LineStyle = 1;
_ws = _wb.Worksheets[2];
_ws.Cells[4, 2] = info.Customer;
_ws.Cells[4, 4] = DateTime.Now.ToString("yyyy-MM-dd");
_ws.Cells[4, 6] = info.CurrentTotalCtns - info.CurrentLooseCtns;
_ws.Cells[4, 8] = info.CurrentLooseCtns;
_ws.Cells[4, 10] = info.StartDate.ToString("yyyy-MM-dd");
_ws.Cells[4, 12] = info.CloseDate.ToString("yyyy-MM-dd");
_ws.Cells[6, 2] = info.CurrentTotalPlts;
_ws.Cells[6, 4] = info.TotalPickingPlts;
_ws.Cells[6, 6] = info.CurrentTotalCtns;
_ws.Cells[6, 8] = info.TotalPickingCtns;
startRow = 9;
foreach (var g in info.FBAPalletGroupInventories)
{
var ctnIndex = startRow;
_ws.Cells[startRow, 1] = g.WarehouseLocation;
_ws.Cells[startRow, 2] = g.Container;
_ws.Cells[startRow, 3] = g.SubCustomer;
_ws.Cells[startRow, 4] = g.ActualPlts;
_ws.Cells[startRow, 5] = g.PickingPlts;
_ws.Cells[startRow, 6] = g.AvailablePlts;
_ws.Cells[startRow, 7] = g.PalletSize;
_ws.Cells[startRow, 8] = g.Location;
foreach (var c in g.InPalletCtnInventories)
{
_ws.Cells[ctnIndex, 9] = c.Id;
_ws.Cells[ctnIndex, 10] = c.ShipmentId;
_ws.Cells[ctnIndex, 11] = c.AmzRefId;
_ws.Cells[ctnIndex, 12] = c.WarehouseCode;
_ws.Cells[ctnIndex, 13] = Math.Round(c.GrossWeightPerCtn, 2);
_ws.Cells[ctnIndex, 14] = Math.Round(c.CBMPerCtn, 2);
_ws.Cells[ctnIndex, 15] = c.OriginalQuantity;
_ws.Cells[ctnIndex, 16] = c.PickingCtns;
_ws.Cells[ctnIndex, 17] = c.ResidualQuantity;
_ws.Cells[ctnIndex, 18] = c.HoldQuantity;
_ws.Cells[startRow, 19] = c.InboundDate.ToString("yyyy-MM-dd");
ctnIndex += 1;
}
//如果一托盘里面有很多SKU，则合并托盘单元格
if (g.InPalletCtnInventories.Count > 1)
{
var rangeId = _ws.get_Range("A" + startRow, "A" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeId.Merge(rangeId.MergeCells);
var rangeContainer = _ws.get_Range("B" + startRow, "B" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeContainer.Merge(rangeContainer.MergeCells);
var rangeSunCustomer = _ws.get_Range("C" + startRow, "C" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeSunCustomer.Merge(rangeSunCustomer.MergeCells);
var rangeOrgPlt = _ws.get_Range("D" + startRow, "D" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeOrgPlt.Merge(rangeOrgPlt.MergeCells);
var rangePlt = _ws.get_Range("E" + startRow, "E" + (startRow + g.InPalletCtnInventories.Count - 1));
rangePlt.Merge(rangePlt.MergeCells);
var rangeStockPlt = _ws.get_Range("F" + startRow, "F" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeStockPlt.Merge(rangeStockPlt.MergeCells);
var rangePltSize = _ws.get_Range("G" + startRow, "G" + (startRow + g.InPalletCtnInventories.Count - 1));
rangePltSize.Merge(rangePltSize.MergeCells);
var rangeLocation = _ws.get_Range("H" + startRow, "H" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeLocation.Merge(rangeLocation.MergeCells);
}
startRow += g.InPalletCtnInventories.Count;
}
_ws.get_Range("A1:S" + startRow, Type.Missing).HorizontalAlignment = XlVAlign.xlVAlignCenter;
_ws.get_Range("A1:S" + startRow, Type.Missing).VerticalAlignment = XlVAlign.xlVAlignCenter;
_ws.get_Range("A1:S" + startRow, Type.Missing).Borders.LineStyle = 1;
var fullPath = @"D:\InventoryReport\FBA-" + info.Customer + "-InventoryReport-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xls";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
return(fullPath);
}
//读取库存报告模板并另存报告,且直接下载
public void GenerateFBAInventoryReport(FBAInventoryInfo info)
{
_ws = _wb.Worksheets[1];
_ws.Cells[4, 2] = info.Customer;
_ws.Cells[4, 4] = info.CloseDate.ToString("yyyy-MM-dd");
_ws.Cells[4, 6] = info.CurrentTotalCtns - info.CurrentLooseCtns;
_ws.Cells[4, 8] = info.CurrentLooseCtns;
_ws.Cells[6, 2] = info.CurrentTotalPlts;
_ws.Cells[6, 4] = info.TotalPickingPlts;
_ws.Cells[6, 6] = info.CurrentTotalCtns;
_ws.Cells[6, 8] = info.TotalPickingCtns;
var startRow = 9;
foreach (var i in info.FBACtnInventories)
{
_ws.Cells[startRow, 1] = i.Container;
_ws.Cells[startRow, 2] = i.SubCustomer;
_ws.Cells[startRow, 3] = i.Type;
_ws.Cells[startRow, 4] = i.ShipmentId;
_ws.Cells[startRow, 5] = i.AmzRefId;
_ws.Cells[startRow, 6] = i.WarehouseCode;
_ws.Cells[startRow, 7] = Math.Round(i.GrossWeightPerCtn, 2);
_ws.Cells[startRow, 8] = Math.Round(i.CBMPerCtn, 2);
_ws.Cells[startRow, 9] = i.OriginalQuantity;
_ws.Cells[startRow, 10] = i.PickingCtns;
_ws.Cells[startRow, 11] = Math.Round((double)i.ResidualQuantity, 2);
_ws.Cells[startRow, 12] = Math.Round((double)i.HoldQuantity, 2);
_ws.Cells[startRow, 13] = i.Location;
startRow += 1;
}
_ws.get_Range("A1:M" + startRow, Type.Missing).HorizontalAlignment = XlVAlign.xlVAlignCenter;
_ws.get_Range("A1:M" + startRow, Type.Missing).VerticalAlignment = XlVAlign.xlVAlignCenter;
_ws.get_Range("A1:M" + startRow, Type.Missing).Borders.LineStyle = 1;
_ws = _wb.Worksheets[2];
_ws.Cells[4, 2] = info.Customer;
_ws.Cells[4, 4] = info.CloseDate.ToString("yyyy-MM-dd");
_ws.Cells[4, 6] = info.CurrentTotalCtns - info.CurrentLooseCtns;
_ws.Cells[4, 8] = info.CurrentLooseCtns;
_ws.Cells[6, 2] = info.CurrentTotalPlts;
_ws.Cells[6, 4] = info.TotalPickingPlts;
_ws.Cells[6, 6] = info.CurrentTotalCtns;
_ws.Cells[6, 8] = info.TotalPickingCtns;
startRow = 9;
foreach (var g in info.FBAPalletGroupInventories)
{
var ctnIndex = startRow;
_ws.Cells[startRow, 1] = g.PltId;
_ws.Cells[startRow, 2] = g.Container;
_ws.Cells[startRow, 3] = g.SubCustomer;
_ws.Cells[startRow, 4] = g.ActualPlts;
_ws.Cells[startRow, 5] = g.PickingPlts;
_ws.Cells[startRow, 6] = g.AvailablePlts;
_ws.Cells[startRow, 7] = g.Location;
foreach (var c in g.InPalletCtnInventories)
{
_ws.Cells[ctnIndex, 8] = c.Id;
_ws.Cells[ctnIndex, 9] = c.ShipmentId;
_ws.Cells[ctnIndex, 10] = c.AmzRefId;
_ws.Cells[ctnIndex, 11] = c.WarehouseCode;
_ws.Cells[ctnIndex, 12] = c.GrossWeightPerCtn;
_ws.Cells[ctnIndex, 13] = c.CBMPerCtn;
_ws.Cells[ctnIndex, 14] = c.OriginalQuantity;
_ws.Cells[ctnIndex, 15] = c.PickingCtns;
_ws.Cells[ctnIndex, 16] = c.ResidualQuantity;
_ws.Cells[ctnIndex, 17] = c.HoldQuantity;
ctnIndex += 1;
}
//如果一托盘里面有很多SKU，则合并托盘单元格
if (g.InPalletCtnInventories.Count > 1)
{
var rangeId = _ws.get_Range("A" + startRow, "A" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeId.Merge(rangeId.MergeCells);
var rangeContainer = _ws.get_Range("B" + startRow, "B" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeContainer.Merge(rangeContainer.MergeCells);
var rangeSunCustomer = _ws.get_Range("C" + startRow, "C" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeSunCustomer.Merge(rangeSunCustomer.MergeCells);
var rangeOrgPlt = _ws.get_Range("D" + startRow, "D" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeOrgPlt.Merge(rangeOrgPlt.MergeCells);
var rangePlt = _ws.get_Range("E" + startRow, "E" + (startRow + g.InPalletCtnInventories.Count - 1));
rangePlt.Merge(rangePlt.MergeCells);
var rangeStockPlt = _ws.get_Range("F" + startRow, "F" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeStockPlt.Merge(rangeStockPlt.MergeCells);
var rangeLocation = _ws.get_Range("G" + startRow, "G" + (startRow + g.InPalletCtnInventories.Count - 1));
rangeLocation.Merge(rangeLocation.MergeCells);
}
startRow += g.InPalletCtnInventories.Count;
}
_ws.get_Range("A1:O" + startRow, Type.Missing).HorizontalAlignment = XlVAlign.xlVAlignCenter;
_ws.get_Range("A1:O" + startRow, Type.Missing).VerticalAlignment = XlVAlign.xlVAlignCenter;
_ws.get_Range("A1:O" + startRow, Type.Missing).Borders.LineStyle = 1;
var fullPath = @"D:\InventoryReport\FBA-" + info.Customer + "-InventoryReport-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xls";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
//使用主线程调用委托，等于用主线程调用Quick()方法，以达到阻塞线程的目的
var handler = new QuitHandler(_excel.Quit);
handler.Invoke();
var response = HttpContext.Current.Response;
var downloadFile = new FileInfo(fullPath);
response.ClearHeaders();
response.Buffer = false;
response.ContentType = "application/octet-stream";
response.AppendHeader("Access-Control-Allow-Origin", "*");
response.AppendHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
response.AppendHeader("Access-Control-Allow-Headers", "Content-Type");
response.AppendHeader("Content-Disposition", "attachment; filename=" + info.Customer + "-Inventory Report-" + HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls", System.Text.Encoding.UTF8));
response.Clear();
response.AppendHeader("Content-Length", downloadFile.Length.ToString());
response.WriteFile(downloadFile.FullName);
response.Flush();
response.Close();
response.End();
var killer = new ExcelKiller();
killer.Dispose();
}
//输入截止日期和客户代码，返回到截止日期时FBA的库存列表
public FBAInventoryInfo GetFBAInventoryResidualInfo(string customerCode, DateTime CloseDate)
{
var residualInventoryList = new List <FBAResidualInventory>();
var minDate = new DateTime(1992, 11, 17);
//var rCloseDate = new DateTime(CloseDate.Year, CloseDate.Month, CloseDate.Day, 23, 59, 59);
var rCloseDate = CloseDate.AddDays(1);
//获取在指定日期前的相关拣货列表
//var pickDetailList = _context.FBAPickDetails
// .Include(x => x.FBAPickDetailCartons)
// .Include(x => x.FBAShipOrder)
// .Where(x => x.FBAShipOrder.PlaceTime <= rCloseDate && x.FBAShipOrder.PlaceTime >= minDate);
////.Where(x => x.FBAShipOrder.ShipDate <= closeDate && x.FBAShipOrder.ShipDate >= minDate);
//var t = pickDetailList.ToList();
//获取在指定日期之前入库的总箱数库存列表
var cartonLocationsInDb = _context.FBACartonLocations
.Include(x => x.FBAPallet.FBAPalletLocations)
.Include(x => x.FBAOrderDetail.FBAMasterOrder.Customer)
.Include(x => x.FBAPickDetailCartons
.Select(c => c.FBAPickDetail.FBAShipOrder))
.Include(x => x.FBAPickDetails)
.Where(x => x.FBAOrderDetail.FBAMasterOrder.InboundDate <= rCloseDate &&
x.FBAOrderDetail.FBAMasterOrder.InboundDate >= minDate &&
x.FBAOrderDetail.FBAMasterOrder.Customer.CustomerCode == customerCode);
//获取在指定日期前入库的托盘库存列表
var palletLocationsInDb = _context.FBAPalletLocations
.Include(x => x.FBAMasterOrder.Customer)
.Include(x => x.FBAPickDetails)
.Where(x => x.FBAMasterOrder.InboundDate <= rCloseDate &&
x.FBAMasterOrder.InboundDate >= minDate &&
x.FBAMasterOrder.Customer.CustomerCode == customerCode);
var palletLocationsList = palletLocationsInDb.ToList();
var cartonLocationList = cartonLocationsInDb.ToList();
var originalPlts = palletLocationsList.Sum(x => x.ActualPlts);
var originalLooseCtns = cartonLocationList.Where(x => x.Location != "Pallet").Sum(x => x.ActualQuantity);
var currentLooseCtns = originalLooseCtns;
//计算原有箱数减去每次发出的箱数并放到列表中
foreach (var cartonLocation in cartonLocationsInDb)
{
var originalQuantity = cartonLocation.ActualQuantity;
if (cartonLocation.Location == FBAInventoryType.Pallet)
{
foreach (var pickCarton in cartonLocation.FBAPickDetailCartons)
{
//获取在指定日期前的相关拣货列表
if (pickCarton.FBAPickDetail.FBAShipOrder.PlaceTime <= rCloseDate)
{
cartonLocation.ActualQuantity -= pickCarton.PickCtns;
}
}
}
else //直接与拣货单
{
foreach (var pickCarton in cartonLocation.FBAPickDetails)
{
cartonLocation.ActualQuantity -= pickCarton.ActualQuantity;
currentLooseCtns -= pickCarton.ActualQuantity;
}
}
if (cartonLocation.ActualQuantity != 0)
{
residualInventoryList.Add(new FBAResidualInventory {
Id = cartonLocation.Id,
Container = cartonLocation.Container,
Type = cartonLocation.Location == "Pallet" ? FBAStatus.InPallet : FBAStatus.LossCtn,
ShipmentId = cartonLocation.ShipmentId,
AmzRefId = cartonLocation.AmzRefId,
WarehouseCode = cartonLocation.WarehouseCode,
GrossWeightPerCtn = cartonLocation.GrossWeightPerCtn,
CBMPerCtn = cartonLocation.CBMPerCtn,
ResidualCBM = cartonLocation.CBMPerCtn * cartonLocation.ActualQuantity,
ResidualQuantity = cartonLocation.ActualQuantity,
OriginalQuantity = originalQuantity,
Location = cartonLocation.Location == "Pallet" ? CombineLocation(cartonLocation.FBAPallet.FBAPalletLocations.Select(x => x.Location).ToList()) : cartonLocation.Location,
});
}
}
//计算原有托盘数减去所有发出的托盘数
foreach (var plt in palletLocationsInDb)
{
foreach (var pick in plt.FBAPickDetails)
{
plt.ActualPlts -= pick.PltsFromInventory;
}
}
var info = new FBAInventoryInfo();
info.FBAResidualInventories = residualInventoryList;
info.Customer = customerCode;
info.OriginalPlts = originalPlts;
info.CurrentPlts = palletLocationsList.Sum(x => x.ActualPlts);
info.OriginalLooseCtns = originalLooseCtns;
info.CurrentLooseCtns = currentLooseCtns;
info.TotalResidualCBM = residualInventoryList.Sum(x => x.ResidualCBM);
info.TotalResidualQuantity = residualInventoryList.Sum(x => x.ResidualQuantity);
info.CloseDate = rCloseDate;
return(info);
}
