public IHttpActionResult GenerateNewStorageReport([FromUri] int templateId, [FromUri] string customerCode, [FromUri] DateTime lastBillingDate, [FromUri] DateTime currentBillingDate, [FromUri] float p1Discount, [FromUri] float p2Discount, [FromUri] bool isEstimatingCharge)
{
var closeDate = new DateTime(currentBillingDate.Year, currentBillingDate.Month, currentBillingDate.Day).AddDays(1);
var startDate = new DateTime(lastBillingDate.Year, currentBillingDate.Month, currentBillingDate.Day);
var warehouseLocationsInDb = _context2.WarehouseLocations.Select(x => x.WarehouseCode).ToArray();
var customerId = _context2.UpperVendors.SingleOrDefault(x => x.CustomerCode == customerCode).Id;
var generator = new FBAExcelGenerator(@"D:\Template\StorageFee-Template.xlsx");
var fullPath = generator.GenerateStorageReport(customerId, lastBillingDate, closeDate, p1Discount, p2Discount, warehouseLocationsInDb, false);
var chargeMethodsList = _context.ChargeMethods
.Include(x => x.ChargeTemplate)
.Where(x => x.ChargeTemplate.Id == templateId)
.OrderBy(x => x.From)
.ToList();
var calculator = new InventoryFeeCalculator(fullPath);
calculator.RecalculateInventoryFeeInExcel(chargeMethodsList, chargeMethodsList.First().TimeUnit, lastBillingDate.ToString("yyyy-MM-dd"), currentBillingDate.ToString("yyyy-MM-dd"));
//强行关闭Excel进程
var killer = new ExcelKiller();
killer.Dispose();
return(Ok(fullPath));
}
public void UploadAndExtractFreeCountryExcel([FromUri] int id)
{
//从httpRequest中获取文件并写入磁盘系统
var filesGetter = new FilesGetter();
var fileSavePath = filesGetter.GetAndSaveSingleFileFromHttpRequest(@"D:\TempFiles\");
if (fileSavePath == "")
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
//var excel = new ExcelExtracter(fileSavePath);
//excel.ExtractFCPurchaseOrderSummary(id);
//excel.ExtractFCPurchaseOrderDetail(id);
var parser = new ExcelParser(fileSavePath);
parser.ParseFreeCountryPackingListV2(id);
//强行关闭进程
var killer = new ExcelKiller();
killer.Dispose();
}
public void UploadAndDownloadFile([FromUri] int templateId, [FromUri] string lastBillingDate, [FromUri] string currentBillingDate)
{
var chargeMethodsList = _context.ChargeMethods
.Include(x => x.ChargeTemplate)
.Where(x => x.ChargeTemplate.Id == templateId)
.OrderBy(x => x.From)
.ToList();
var fileGetter = new FilesGetter();
var path = fileGetter.GetAndSaveSingleFileFromHttpRequest(@"D:\TempFiles\");
if (path == "")
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
var calculator = new InventoryFeeCalculator(path);
calculator.RecalculateInventoryFeeInExcel(chargeMethodsList, chargeMethodsList.First().TimeUnit, lastBillingDate, currentBillingDate);
//强行关闭Excel进程
var killer = new ExcelKiller();
killer.Dispose();
//在静态变量中记录下载信息
DownloadRecord.FileName = fileGetter.FileName;
DownloadRecord.FilePath = path;
}
public IHttpActionResult GenerateNewStorageReportThroughPayload([FromBody] StorageChargePayload data)
{
var closeDate = new DateTime(data.CurrentBillingDate.Year, data.CurrentBillingDate.Month, data.CurrentBillingDate.Day).AddDays(1);
var startDate = new DateTime(data.LastBillingDate.Year, data.CurrentBillingDate.Month, data.CurrentBillingDate.Day);
var warehouseLocationsInDb = _context2.WarehouseLocations.Select(x => x.WarehouseCode).ToArray();
var warehouseLocations = data.WarehouseLocation.Length > 0 ? data.WarehouseLocation : warehouseLocationsInDb;
var customerId = _context2.UpperVendors.SingleOrDefault(x => x.CustomerCode == data.CustomerCode).Id;
var generator = new FBAExcelGenerator(@"D:\Template\StorageFee-Template.xlsx");
var fullPath = generator.GenerateStorageReport(customerId, data.LastBillingDate, closeDate, data.P1Discount, data.P2Discount, warehouseLocations, data.IncludePrereleasedOrder);
var chargeMethodsList = _context.ChargeMethods
.Include(x => x.ChargeTemplate)
.Where(x => x.ChargeTemplate.Id == data.TemplateId)
.OrderBy(x => x.From)
.ToList();
var calculator = new InventoryFeeCalculator(fullPath);
calculator.RecalculateInventoryFeeInExcel(chargeMethodsList, chargeMethodsList.First().TimeUnit, data.LastBillingDate.ToString("yyyy-MM-dd"), data.CurrentBillingDate.ToString("yyyy-MM-dd"));
//强行关闭Excel进程
var killer = new ExcelKiller();
killer.Dispose();
return(Ok(fullPath));
}
public IHttpActionResult UploadFBATemplateByMasterOrderId([FromUri] int masterOrderId, [FromUri] string operation)
{
//从httpRequest中获取文件并写入磁盘系统
var filesGetter = new FilesGetter();
var fileSavePath = filesGetter.GetAndSaveSingleFileFromHttpRequest(@"D:\PackingLists\");
if (fileSavePath == "")
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
var excel = new FBAExcelExtracter(fileSavePath);
var killer = new ExcelKiller();
var result = new List <FBAOrderDetail>();
if (operation == "Upload")
{
try
{
result = excel.ExtractFBAPackingListTemplate("", masterOrderId);
}
catch (Exception e)
{
killer.Dispose();
throw new Exception(e.Message);
}
}
// 将成功上传的packing list保存到efile中
var masterOrderInDb = _context.FBAMasterOrders.Find(masterOrderId);
_context.EFiles.Add(new EFile {
UploadDate = DateTime.Now,
CustomizedFileName = "Packing List",
RootPath = @"D:\PackingLists\",
FileName = fileSavePath.Split('\\').Last(),
UploadBy = _userName,
FBAMasterOrder = masterOrderInDb
});
_context.SaveChanges();
killer.Dispose();
return(Ok(Mapper.Map <IList <FBAOrderDetail>, IList <FBAOrderDetailDto> >(result)));
}
//读取库存报告模板并另存报告,返回完整储存路径
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();
}
public void UploadFBATemplate([FromUri] string grandNumber)
{
//从httpRequest中获取文件并写入磁盘系统
var filesGetter = new FilesGetter();
var fileSavePath = filesGetter.GetAndSaveFileFromHttpRequest(@"D:\TempFiles\");
if (fileSavePath == "")
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
var excel = new FBAExcelExtracter(fileSavePath);
var killer = new ExcelKiller();
excel.ExtractFBAPackingListTemplate(grandNumber);
killer.Dispose();
}
public void UploadRegularPackingList([FromUri] int preId, [FromUri] string vendor)
{
var fileSavePath = "";
var filesGetter = new FilesGetter();
fileSavePath = filesGetter.GetAndSaveSingleFileFromHttpRequest(@"D:\TempFiles\");
if (fileSavePath == "")
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
var excel = new ExcelExtracter(fileSavePath);
excel.ExtractPOSummaryAndCartonDetail(preId, vendor);
var killer = new ExcelKiller();
killer.Dispose();
}
public void SaveUploadedFileAndExtractExcel([FromUri] int id, [FromUri] string vendor)
{
var fileSavePath = "";
//写入磁盘系统
var filesGetter = new FilesGetter();
fileSavePath = filesGetter.GetAndSaveSingleFileFromHttpRequest(@"D:\TempFiles\");
if (fileSavePath == "")
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
var excel = new ExcelExtracter(fileSavePath);
excel.ExtractPOSummaryAndCartonDetail(id, vendor);
//强行关闭进程
var killer = new ExcelKiller();
killer.Dispose();
}
//清理账单，将收费单号和收费项目、价格分离
public string ClearBills(string path)
{
_excel = new Application();
_wb = _excel.Workbooks.Open(path);
_ws = _wb.Worksheets[1];
var billCount = -1;
var index = 1;
var billList = new List <LabelBill>();
var chargingItemList = new List <string>();
while (_ws.Cells[index, 1].Value2 != null)
{
billCount++;
index++;
}
_ws.Cells[1, 35] = "Length";
_ws.Cells[1, 36] = "Width";
_ws.Cells[1, 37] = "Height";
for (int i = 0; i < billCount; i++)
{
var newBill = new LabelBill();
var str = _ws.Cells[i + 2, 35].Value2.ToString();
newBill.LabelCode = _ws.Cells[i + 2, 1].Value2.ToString();
newBill.LabelBillDetails = ConvertStringToDetail(str, chargingItemList);
billList.Add(newBill);
string dms = _ws.Cells[i + 2, 34].Value2 == null ? "0x0x0" : _ws.Cells[i + 2, 34].Value2.ToString();
if (dms == "0")
{
_ws.Cells[i + 2, 35] = 0;
_ws.Cells[i + 2, 36] = 0;
_ws.Cells[i + 2, 37] = 0;
_ws.Cells[i + 2, 37].NumberFormat = "#,###,###";
}
else
{
_ws.Cells[i + 2, 35] = dms.Split('x')[0];
_ws.Cells[i + 2, 36] = dms.Split('x')[1];
_ws.Cells[i + 2, 37] = dms.Split('x')[2];
_ws.Cells[i + 2, 37].NumberFormat = "#,###,###";
}
}
index++;
index = 1;
var itemArray = chargingItemList.ToArray();
//_ws.Cells[index, 1] = "Label Code";
for (int i = 0; i < itemArray.Length; i++)
{
_ws.Cells[index, i + 38] = itemArray[i];
}
index++;
foreach (var b in billList)
{
//_ws.Cells[index, 1] = b.LabelCode;
foreach (var l in b.LabelBillDetails)
{
var itemIndex = Array.IndexOf(itemArray, l.ChargingItem);
_ws.Cells[index, itemIndex + 38] = l.Rate;
}
index++;
}
_wb.Save();
//var fullPath = @"D:\PickingList\test.xlsx";
//_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_wb.Close();
_excel.Quit();
var killer = new ExcelKiller();
killer.Dispose();
return(path);
}
//生成Receipt文件并返回完整路径
public string GenerateReceipt(int masterOrderId)
{
var masterOrderInDb = _context.FBAMasterOrders
.Include(x => x.FBAOrderDetails)
.Include(x => x.Customer)
.SingleOrDefault(x => x.Id == masterOrderId);
var cartonLocations = _context.FBACartonLocations
.Include(x => x.FBAOrderDetail.FBAMasterOrder)
.Where(x => x.Location != "Pallet" && x.FBAOrderDetail.FBAMasterOrder.Id == masterOrderId);
var unPalletizedSKU = masterOrderInDb.FBAOrderDetails
.Where(x => x.ComsumedQuantity < x.ActualQuantity);
var pallets = _context.FBAPallets
.Include(x => x.FBAMasterOrder)
.Include(x => x.FBACartonLocations)
.Where(x => x.FBAMasterOrder.Id == masterOrderId && x.ActualPallets != 0)
.ToList();
var totalPlts = pallets == null ? 0 : pallets.Sum(x => x.ActualPallets);
// 接收报告
_ws = _wb.Worksheets[1];
var startRow = 5;
foreach (var d in masterOrderInDb.FBAOrderDetails)
{
_ws.Cells[startRow, 1] = d.ShipmentId;
_ws.Cells[startRow, 2] = d.AmzRefId;
_ws.Cells[startRow, 3] = d.Quantity;
_ws.Cells[startRow, 4] = d.ActualQuantity;
_ws.Cells[startRow, 5] = d.WarehouseCode;
startRow += 1;
}
// 写入表头信息
_ws.Cells[2, 2] = masterOrderInDb.Customer.CustomerCode;
_ws.Cells[2, 4] = masterOrderInDb.InboundDate.ToString("yyyy-MM-dd");
_ws.Cells[3, 2] = masterOrderInDb.Container;
_ws.Cells[3, 4] = masterOrderInDb.OriginalPlts;
// 写入表脚信息
_ws.Cells[startRow, 1] = "Total:";
_ws.Cells[startRow, 3] = masterOrderInDb == null ? 0 : masterOrderInDb.FBAOrderDetails.Sum(x => x.Quantity);
_ws.Cells[startRow, 4] = masterOrderInDb == null ? 0 : masterOrderInDb.FBAOrderDetails.Sum(x => x.ActualQuantity);
//加上边框
var range = _ws.get_Range("A5", "E" + startRow);
range.Borders.LineStyle = 1;
// 打托报告
_ws = _wb.Worksheets[2];
var ws = (Worksheet)_wb.ActiveSheet;
startRow = 5;
// 写入打托的SKU
foreach (var p in pallets)
{
//合并单元格
_ws.Cells[startRow, 1] = "Palletized";
_ws.Cells[startRow, 6] = p.PalletSize;
_ws.Cells[startRow, 7] = p.ActualPallets;
var statusRange = _ws.get_Range("A" + startRow, "A" + (startRow + p.FBACartonLocations.Count - 1));
var pltSizeRange = _ws.get_Range("F" + startRow, "F" + (startRow + p.FBACartonLocations.Count - 1));
var palletsRange = _ws.get_Range("G" + startRow, "G" + (startRow + p.FBACartonLocations.Count - 1));
statusRange.Merge(statusRange.MergeCells);
pltSizeRange.Merge(pltSizeRange.MergeCells);
palletsRange.Merge(palletsRange.MergeCells);
foreach (var c in p.FBACartonLocations)
{
_ws.Cells[startRow, 2] = c.ShipmentId;
_ws.Cells[startRow, 3] = c.AmzRefId;
_ws.Cells[startRow, 4] = c.FBAOrderDetail.ActualQuantity;
_ws.Cells[startRow, 5] = c.ActualQuantity + " / " + c.FBAOrderDetail.ActualQuantity;
_ws.Cells[startRow, 8] = c.WarehouseCode;
startRow += 1;
}
}
// 写入未打托但直接分派了库位的SKU
foreach (var c in cartonLocations)
{
_ws.Cells[startRow, 1] = "Unpalletized";
_ws.Cells[startRow, 2] = c.ShipmentId;
_ws.Cells[startRow, 3] = c.AmzRefId;
_ws.Cells[startRow, 4] = c.FBAOrderDetail.ActualQuantity;
_ws.Cells[startRow, 5] = c.ActualQuantity + " / " + c.FBAOrderDetail.ActualQuantity;
_ws.Cells[startRow, 6] = "N/A";
_ws.Cells[startRow, 7] = "N/A";
_ws.Cells[startRow, 8] = c.WarehouseCode;
startRow += 1;
}
// 写入未打托且未直接入库的SKU
foreach (var s in unPalletizedSKU)
{
_ws.Cells[startRow, 1] = "Pending";
_ws.Cells[startRow, 2] = s.ShipmentId;
_ws.Cells[startRow, 3] = s.AmzRefId;
_ws.Cells[startRow, 4] = s.ActualQuantity;
_ws.Cells[startRow, 5] = s.ActualQuantity - s.ComsumedQuantity + " / " + s.ActualQuantity;
_ws.Cells[startRow, 6] = "N/A";
_ws.Cells[startRow, 7] = "N/A";
_ws.Cells[startRow, 8] = s.WarehouseCode;
startRow += 1;
}
// 写入表头信息
_ws.Cells[2, 2] = masterOrderInDb.Customer.CustomerCode;
_ws.Cells[2, 4] = masterOrderInDb.InboundDate.ToString("yyyy-MM-dd");
_ws.Cells[3, 2] = masterOrderInDb.Container;
_ws.Cells[3, 4] = masterOrderInDb.OriginalPlts;
//写入表脚信息
_ws.Cells[startRow, 1] = "Total:";
_ws.Cells[startRow, 5] = masterOrderInDb == null ? 0 : masterOrderInDb.FBAOrderDetails.Sum(x => x.ActualQuantity) + " / " + masterOrderInDb == null ? 0 : masterOrderInDb.FBAOrderDetails.Sum(x => x.ActualQuantity);
_ws.Cells[startRow, 7] = pallets == null ? 0 : pallets.Sum(x => x.ActualPallets);
//加上边框
range = _ws.get_Range("A5", "H" + startRow);
range.Borders.LineStyle = 1;
var fullPath = @"D:\Receipts\FBA-" + masterOrderInDb.Customer.CustomerCode + "-Receipt-" + masterOrderInDb.Container + "-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
//强行关闭Excel进程
var killer = new ExcelKiller();
killer.Dispose();
return(fullPath);
}
//输入Invoice Detail列表，客户CODE，日期范围，生成Excel
public string GenerateExcelFileAndReturnPath(FBAInvoiceInfo info)
{
//Worksheet summaryWookSheet;
//summaryWookSheet = (Worksheet)_wb.Worksheets.Add();
for (int i = 1; i <= 3; i++)
{
_ws = _wb.Worksheets[i];
_ws.Cells[4, 2] = info.CustomerCode;
_ws.Cells[4, 4] = info.FromDate == null ? "" : info.FromDate.ToString("yyyy-MM-dd").Substring(0, 10);
_ws.Cells[4, 6] = info.ToDate == null ? "" : info.ToDate.ToString("yyyy-MM-dd").Substring(0, 10);
_ws.Cells[4, 8] = DateTime.Now.ToString("yyyy-MM-dd");
}
//制作第一个Summary表
_ws = _wb.Worksheets[1];
var startRow = 9;
foreach (var i in info.InvoiceReportDetails)
{
_ws.Cells[startRow, 1] = i.InvoiceType;
_ws.Cells[startRow, 2] = i.Reference;
_ws.Cells[startRow, 3] = i.GrandNumber;
_ws.Cells[startRow, 4] = i.Activity;
_ws.Cells[startRow, 5] = i.ChargingType;
_ws.Cells[startRow, 6] = i.Unit;
_ws.Cells[startRow, 7] = i.Quantity;
_ws.Cells[startRow, 8] = i.Rate;
_ws.Cells[startRow, 9] = i.Amount;
_ws.Cells[startRow, 10] = i.DateOfCost.ToString("yyyy-MM-dd");
_ws.Cells[startRow, 11] = i.Memo;
startRow += 1;
}
_ws.Cells[startRow, 8] = "Total";
_ws.Cells[startRow, 9] = info.InvoiceReportDetails.Sum(x => x.Amount);
//制作第二个收费项目统计表
_ws = _wb.Worksheets[2];
startRow = 8;
var referenceGroup = info.InvoiceReportDetails.GroupBy(x => x.Reference);
var chargeActivityGroup = info.InvoiceReportDetails.GroupBy(x => x.Activity);
_ws.Cells[startRow, 1] = "Order Type";
_ws.Cells[startRow, 2] = "Reference #";
_ws.Cells[startRow, 3] = "Grand #";
_ws.Cells[startRow, 4] = "Destination";
_ws.Cells[startRow, 5] = "Total Ctns";
_ws.Cells[startRow, 6] = "Total Plts";
var columnIndex = 7;
var activityList = new List <string>();
foreach (var c in chargeActivityGroup)
{
_ws.Cells[startRow, columnIndex] = c.First().Activity;
activityList.Add(c.First().Activity);
columnIndex += 1;
}
_ws.Cells[startRow, columnIndex] = "Date of Close";
_ws.Cells[startRow, columnIndex + 1] = "Amount";
startRow += 1;
var countOfActivity = chargeActivityGroup.Count();
var totalCtns = 0;
var totalPlts = 0;
foreach (var r in referenceGroup)
{
_ws.Cells[startRow, 1] = r.First().InvoiceType;
_ws.Cells[startRow, 2] = r.First().Reference;
_ws.Cells[startRow, 3] = r.First().GrandNumber;
_ws.Cells[startRow, 4] = r.First().Destination;
_ws.Cells[startRow, 5] = r.First().ActualCtnsInThisOrder;
_ws.Cells[startRow, 6] = r.First().ActualPltsInThisOrder;
for (var i = 0; i < countOfActivity; i++)
{
_ws.Cells[startRow, 7 + i] = 0.0;
}
_ws.Cells[startRow, columnIndex] = r.First().DateOfClose.ToString("MM/dd/yyyy");
_ws.Cells[startRow, columnIndex + 1] = r.Sum(x => x.Amount);
foreach (var i in r)
{
var index = activityList.IndexOf(i.Activity);
_ws.Cells[startRow, index + 7] = _ws.Cells[startRow, index + 7].Value2 + i.Amount;
}
totalCtns += r.First().ActualCtnsInThisOrder;
totalPlts += r.First().ActualPltsInThisOrder;
startRow += 1;
}
foreach (var c in chargeActivityGroup)
{
var activity = c.First().Activity;
_ws.Cells[startRow, activityList.IndexOf(activity) + 7] = info.InvoiceReportDetails.Where(x => x.Activity == activity).Sum(x => x.Amount);
}
_ws.Cells[startRow, 1] = "Total";
_ws.Cells[startRow, 5] = totalCtns;
_ws.Cells[startRow, 6] = totalPlts;
_ws.Cells[startRow, columnIndex + 1] = info.InvoiceReportDetails.Sum(x => x.Amount);
//制作第三个收费细节表
_ws = _wb.Worksheets[3];
startRow = 6;
var shipOrderList = _context.FBAShipOrders
.Include(x => x.FBAPickDetails)
.Where(x => x.CustomerCode == info.CustomerCode &&
x.ShipDate >= info.FromDate &&
x.ShipDate <= info.ToDate)
.ToList();
foreach (var s in shipOrderList)
{
_ws.Cells[startRow, 1] = "Reference";
_ws.Cells[startRow, 2] = s.ShipOrderNumber;
_ws.Cells[startRow, 3] = "Outbound Date";
_ws.Cells[startRow, 4] = s.ShipDate.ToString("yyyy-MM-dd");
startRow++;
_ws.Cells[startRow, 1] = "Container";
_ws.Cells[startRow, 2] = "SKU";
_ws.Cells[startRow, 3] = "Pickable Ctns";
_ws.Cells[startRow, 4] = "Actual Ctns";
_ws.Cells[startRow, 5] = "Plts From Inventory";
_ws.Cells[startRow, 6] = "New Plts";
_ws.Cells[startRow, 7] = "Actual Plts";
_ws.Cells[startRow, 8] = "Inbound Date";
_ws.Cells[startRow, 9] = "Ship Date";
startRow++;
foreach (var p in s.FBAPickDetails)
{
_ws.Cells[startRow, 1] = p.Container;
_ws.Cells[startRow, 2] = p.ShipmentId;
_ws.Cells[startRow, 3] = p.PickableCtns;
_ws.Cells[startRow, 4] = p.ActualQuantity;
_ws.Cells[startRow, 5] = p.PltsFromInventory;
_ws.Cells[startRow, 6] = p.NewPlts;
_ws.Cells[startRow, 7] = p.ActualPlts;
_ws.Cells[startRow, 8] = p.InboundDate.ToString("yyyy-MM-dd");
_ws.Cells[startRow, 9] = p.FBAShipOrder.ShipDate.ToString("yyyy-MM-dd");
startRow++;
}
_ws.Cells[startRow, 1] = "Total";
_ws.Cells[startRow, 3] = s.FBAPickDetails.Sum(x => x.PickableCtns);
_ws.Cells[startRow, 4] = s.FBAPickDetails.Sum(x => x.ActualQuantity);
_ws.Cells[startRow, 5] = s.FBAPickDetails.Sum(x => x.PltsFromInventory);
_ws.Cells[startRow, 6] = s.FBAPickDetails.Sum(x => x.NewPlts);
_ws.Cells[startRow, 7] = s.FBAPickDetails.Sum(x => x.ActualPlts);
startRow += 2;
}
var fullPath = @"D:\ChargingReport\FBA-" + info.CustomerCode + "-ChargingReport-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
var killer = new ExcelKiller();
killer.Dispose();
return(fullPath);
}
public string GenerateExcelFileForAllCustomerAndReturnPath(FBAInvoiceInfo info)
{
_ws = _wb.Worksheets[1];
_ws.Cells[4, 2] = info.CustomerCode;
_ws.Cells[4, 4] = info.FromDate == null ? "" : info.FromDate.ToString("yyyy-MM-dd").Substring(0, 10);
_ws.Cells[4, 6] = info.ToDate == null ? "" : info.ToDate.ToString("yyyy-MM-dd").Substring(0, 10);
_ws.Cells[4, 8] = DateTime.Now.ToString("yyyy-MM-dd");
var groupByCustomer = info.InvoiceReportDetails.GroupBy(x => x.CustomerCode);
var startRow = 8;
foreach (var g in groupByCustomer)
{
_ws.Cells[startRow - 1, 1] = g.First().CustomerCode;
_ws.Cells[startRow, 1] = "Order Type";
_ws.Cells[startRow, 2] = "Reference #";
_ws.Cells[startRow, 3] = "Grand #";
_ws.Cells[startRow, 4] = "Activity";
_ws.Cells[startRow, 5] = "Charging Type";
_ws.Cells[startRow, 6] = "UOM";
_ws.Cells[startRow, 7] = "Quantity";
_ws.Cells[startRow, 8] = "Rate";
_ws.Cells[startRow, 9] = "Amout";
_ws.Cells[startRow, 10] = "Date of Cost";
_ws.Cells[startRow, 11] = "Memo";
_ws.Cells[startRow, 12] = "Cost";
startRow += 1;
foreach (var i in g)
{
_ws.Cells[startRow, 1] = i.InvoiceType;
_ws.Cells[startRow, 2] = i.Reference;
_ws.Cells[startRow, 3] = i.GrandNumber;
_ws.Cells[startRow, 4] = i.Activity;
_ws.Cells[startRow, 5] = i.ChargingType;
_ws.Cells[startRow, 6] = i.Unit;
_ws.Cells[startRow, 7] = i.Quantity;
_ws.Cells[startRow, 8] = i.Rate;
_ws.Cells[startRow, 9] = i.Amount;
_ws.Cells[startRow, 10] = i.DateOfCost.ToString("yyyy-MM-dd");
_ws.Cells[startRow, 11] = i.Memo;
_ws.Cells[startRow, 12] = i.Cost;
startRow += 1;
}
_ws.Cells[startRow, 8] = "Total";
_ws.Cells[startRow, 9] = g.Sum(x => x.Amount);
_ws.Cells[startRow, 12] = g.Sum(x => x.Cost);
startRow += 3;
}
var fullPath = @"D:\ChargingReport\FBA-" + info.CustomerCode + "-ChargingReport-" + 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();
var killer = new ExcelKiller();
killer.Dispose();
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();
}
public string GenerateSKUStatement(string customerCode, string sku, DateTime startDate, DateTime endDate)
{
var originalStartDate = startDate.ToString("yyyy-MM-dd");
var originalEndDate = endDate.ToString("yyyy-MM-dd");
startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day);
endDate = new DateTime(endDate.Year, endDate.Month, endDate.Day).AddDays(1);
var index = 5;
var total = 0;
var balanceList = new List <ItemStatisticLine>();
var inboundCollection = _context.FBAOrderDetails
.Include(x => x.FBAMasterOrder)
.Where(x => x.ShipmentId == sku &&
x.FBAMasterOrder.InboundDate >= startDate &&
x.FBAMasterOrder.InboundDate < endDate &&
x.FBAMasterOrder.CustomerCode == customerCode)
.ToList()
.OrderBy(x => x.FBAMasterOrder.InboundDate);
var outboundCollection = _context.FBAPickDetailCartons
.Include(x => x.FBAPickDetail.FBAShipOrder)
.Include(x => x.FBACartonLocation)
.Where(x => x.FBACartonLocation.ShipmentId == sku &&
x.FBAPickDetail.FBAShipOrder.ReleasedDate >= startDate &&
x.FBAPickDetail.FBAShipOrder.ReleasedDate < endDate &&
x.FBAPickDetail.FBAShipOrder.CustomerCode == customerCode)
.ToList()
.OrderBy(x => x.FBAPickDetail.FBAShipOrder.ReleasedDate);
// sku inbound statement
_ws = _wb.Worksheets[1];
total = 0;
index = 5;
_ws.Cells[3, 2] = customerCode;
_ws.Cells[3, 4] = sku;
_ws.Cells[3, 6] = originalStartDate;
_ws.Cells[3, 8] = originalEndDate;
foreach (var i in inboundCollection)
{
total += i.ActualQuantity;
_ws.Cells[index, 1] = i.ShipmentId;
_ws.Cells[index, 2] = i.Container;
_ws.Cells[index, 3] = i.AmzRefId;
_ws.Cells[index, 4] = i.WarehouseCode;
_ws.Cells[index, 5] = i.FBAMasterOrder.WarehouseLocation;
_ws.Cells[index, 6] = i.FBAMasterOrder.InboundDate.ToString("yyyy-MM-dd");
_ws.Cells[index, 7] = i.ActualQuantity;
_ws.Cells[index, 8] = total;
balanceList.Add(new ItemStatisticLine {
Reference = i.Container,
Container = i.Container,
Type = FBAOrderType.Inbound,
SKU = i.ShipmentId,
AmzRefId = i.AmzRefId,
WarehouseCode = i.WarehouseCode,
WarehouseLocation = i.FBAMasterOrder.WarehouseLocation,
Date = i.FBAMasterOrder.InboundDate,
QuantityChange = i.ActualQuantity
});
index++;
}
// sku outbound statement
_ws = _wb.Worksheets[2];
index = 5;
total = 0;
_ws.Cells[3, 2] = customerCode;
_ws.Cells[3, 4] = sku;
_ws.Cells[3, 6] = originalStartDate;
_ws.Cells[3, 8] = originalEndDate;
foreach (var i in outboundCollection)
{
total -= i.PickCtns;
_ws.Cells[index, 1] = i.FBACartonLocation.ShipmentId;
_ws.Cells[index, 2] = i.FBACartonLocation.Container;
_ws.Cells[index, 3] = i.FBACartonLocation.AmzRefId;
_ws.Cells[index, 4] = i.FBACartonLocation.WarehouseCode;
_ws.Cells[index, 5] = i.FBAPickDetail.FBAShipOrder.WarehouseLocation;
_ws.Cells[index, 6] = i.FBAPickDetail.FBAShipOrder.ReleasedDate.ToString("yyyy-MM-dd");
_ws.Cells[index, 7] = -i.PickCtns;
_ws.Cells[index, 8] = total;
balanceList.Add(new ItemStatisticLine
{
Reference = i.FBAPickDetail.FBAShipOrder.ShipOrderNumber,
Container = i.FBACartonLocation.Container,
Type = FBAOrderType.Outbound,
SKU = i.FBACartonLocation.ShipmentId,
AmzRefId = i.FBACartonLocation.AmzRefId,
WarehouseCode = i.FBACartonLocation.WarehouseCode,
WarehouseLocation = i.FBAPickDetail.FBAShipOrder.WarehouseLocation,
Date = i.FBAPickDetail.FBAShipOrder.ReleasedDate,
QuantityChange = -i.PickCtns
});
index++;
}
// sku balance statement
_ws = _wb.Worksheets[3];
total = 0;
index = 6;
var balance = 0;
_ws.Cells[3, 2] = customerCode;
_ws.Cells[3, 4] = sku;
_ws.Cells[3, 6] = originalStartDate;
_ws.Cells[3, 8] = originalEndDate;
// TO DO 计算在start date之前的balance
var totalInboundBeforeStartDate = _context.FBAOrderDetails
.Include(x => x.FBAMasterOrder)
.Where(x => x.FBAMasterOrder.InboundDate < startDate && x.ShipmentId == sku)
.ToList()
.Sum(x => x.ActualQuantity);
var totalOutboundBeforeStartDate = _context.FBAPickDetailCartons
.Include(x => x.FBAPickDetail.FBAShipOrder)
.Include(x => x.FBACartonLocation)
.Where(x => x.FBAPickDetail.FBAShipOrder.ReleasedDate < startDate && x.FBAPickDetail.FBAShipOrder.ReleasedDate.Year != 1900 && x.FBACartonLocation.ShipmentId == sku)
.ToList()
.Sum(x => x.PickCtns);
balance = totalInboundBeforeStartDate - totalOutboundBeforeStartDate;
_ws.Cells[5, 1] = sku;
_ws.Cells[5, 2] = "N/A";
_ws.Cells[5, 3] = "N/A";
_ws.Cells[5, 4] = "N/A";
_ws.Cells[5, 5] = "N/A";
_ws.Cells[5, 6] = "N/A";
_ws.Cells[5, 7] = "By end of";
_ws.Cells[5, 8] = startDate.ToString("yyyy-MM-dd");
_ws.Cells[5, 9] = balance;
_ws.Cells[5, 10] = balance;
balanceList = balanceList.OrderBy(x => x.Date).ToList();
foreach (var i in balanceList)
{
balance += i.QuantityChange;
_ws.Cells[index, 1] = i.SKU;
_ws.Cells[index, 2] = i.Reference;
_ws.Cells[index, 3] = i.Container;
_ws.Cells[index, 4] = i.AmzRefId;
_ws.Cells[index, 5] = i.WarehouseCode;
_ws.Cells[index, 6] = i.WarehouseLocation;
_ws.Cells[index, 7] = i.Type;
_ws.Cells[index, 8] = i.Date;
_ws.Cells[index, 9] = i.QuantityChange;
_ws.Cells[index, 10] = balance;
index++;
}
var fullPath = @"D:\OtherReport\" + customerCode + "-SKU-" + sku + "-Statement-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
//强行关闭Excel进程
var killer = new ExcelKiller();
killer.Dispose();
return(fullPath);
}
public string GenerateAllSKUStatement(string customerCode, DateTime startDate, DateTime endDate)
{
var originalStartDate = startDate.ToString("yyyy-MM-dd");
var originalEndDate = endDate.ToString("yyyy-MM-dd");
startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day);
endDate = new DateTime(endDate.Year, endDate.Month, endDate.Day).AddDays(1);
var skuList = new List <ItemStatisticLine>();
var inboundSKUs = _context.FBAOrderDetails
.Include(x => x.FBAMasterOrder)
.Where(x => x.FBAMasterOrder.CustomerCode == customerCode &&
x.FBAMasterOrder.InboundDate >= startDate &&
x.FBAMasterOrder.InboundDate < endDate)
.ToList()
.GroupBy(x => new { x.ShipmentId, x.FBAMasterOrder.WarehouseLocation });
var outboundSKUs = _context.FBAPickDetailCartons
.Include(x => x.FBACartonLocation)
.Include(x => x.FBAPickDetail.FBAShipOrder)
.Where(x => x.FBAPickDetail.FBAShipOrder.ReleasedDate >= startDate &&
x.FBAPickDetail.FBAShipOrder.ReleasedDate < endDate &&
x.FBAPickDetail.FBAShipOrder.CustomerCode == customerCode)
.ToList()
.GroupBy(x => new { x.FBACartonLocation.ShipmentId, x.FBAPickDetail.FBAShipOrder.WarehouseLocation });
var totalInboundBeforeStartDate = _context.FBAOrderDetails
.Include(x => x.FBAMasterOrder)
.Where(x => x.FBAMasterOrder.InboundDate < startDate)
.ToList();
var totalOutboundBeforeStartDate = _context.FBAPickDetailCartons
.Include(x => x.FBAPickDetail.FBAShipOrder)
.Include(x => x.FBACartonLocation)
.Where(x => x.FBAPickDetail.FBAShipOrder.ReleasedDate < startDate && x.FBAPickDetail.FBAShipOrder.ReleasedDate.Year != 1900)
.ToList();
// 获取所有涉及到进出库的SKU列表
foreach (var i in inboundSKUs)
{
if (!skuList.Where(x => x.SKU == i.First().ShipmentId).Any())
{
skuList.Add(new ItemStatisticLine {
SKU = i.First().ShipmentId,
WarehouseLocation = i.First().FBAMasterOrder.WarehouseLocation
});
}
}
foreach (var o in outboundSKUs)
{
if (!skuList.Where(x => x.SKU == o.First().FBACartonLocation.ShipmentId).Any())
{
skuList.Add(new ItemStatisticLine {
SKU = o.First().FBACartonLocation.ShipmentId,
WarehouseLocation = o.First().FBAPickDetail.FBAShipOrder.WarehouseLocation
});
}
}
// 画表
// sku summary statement
_ws = _wb.Worksheets[1];
var index = 5;
_ws.Cells[3, 2] = customerCode;
_ws.Cells[3, 4] = originalStartDate;
_ws.Cells[3, 6] = originalEndDate;
foreach (var i in skuList)
{
var totalInbound = 0;
var totalOutbouns = 0;
var openingBalance = totalInboundBeforeStartDate.Where(x => x.ShipmentId == i.SKU).Sum(x => x.ActualQuantity) - totalOutboundBeforeStartDate.Where(x => x.FBACartonLocation.ShipmentId == i.SKU).Sum(x => x.PickCtns);
var inboundGroup = inboundSKUs.SingleOrDefault(x => x.Key.ShipmentId == i.SKU && x.Key.WarehouseLocation == i.WarehouseLocation);
var outboundGroup = outboundSKUs.SingleOrDefault(x => x.Key.ShipmentId == i.SKU && x.Key.WarehouseLocation == i.WarehouseLocation);
if (inboundGroup != null)
{
totalInbound = inboundGroup.Sum(x => x.ActualQuantity);
}
if (outboundGroup != null)
{
totalOutbouns = outboundGroup.Sum(x => x.PickCtns);
}
_ws.Cells[index, 1] = i.SKU;
_ws.Cells[index, 2] = i.WarehouseLocation;
_ws.Cells[index, 3] = openingBalance;
_ws.Cells[index, 4] = totalInbound;
_ws.Cells[index, 5] = -totalOutbouns;
_ws.Cells[index, 6] = openingBalance + totalInbound - totalOutbouns;
index++;
}
// 生成第二张Detail表格
index = 5;
_ws = _wb.Worksheets[2];
_ws.Cells[3, 2] = customerCode;
_ws.Cells[3, 4] = "All SKU";
_ws.Cells[3, 6] = originalStartDate;
_ws.Cells[3, 8] = originalEndDate;
var skuArray = skuList.Select(x => x.SKU).ToArray();
var allInboundBeforeStartDate = _context.FBAOrderDetails
.Include(x => x.FBAMasterOrder)
.Where(x => x.FBAMasterOrder.InboundDate < startDate && skuArray.Contains(x.ShipmentId))
.ToList();
var allOutboundBeforeStartDate = _context.FBAPickDetailCartons
.Include(x => x.FBAPickDetail.FBAShipOrder)
.Include(x => x.FBACartonLocation)
.Where(x => x.FBAPickDetail.FBAShipOrder.ReleasedDate < startDate && x.FBAPickDetail.FBAShipOrder.ReleasedDate.Year != 1900 && skuArray.Contains(x.FBACartonLocation.ShipmentId))
.ToList();
var balanceList = new List <ItemStatisticLine>();
var inboundCollection = _context.FBAOrderDetails
.Include(x => x.FBAMasterOrder)
.Where(x => x.FBAMasterOrder.InboundDate >= startDate &&
x.FBAMasterOrder.InboundDate < endDate &&
x.FBAMasterOrder.CustomerCode == customerCode)
.ToList()
.OrderBy(x => x.FBAMasterOrder.InboundDate);
var outboundCollection = _context.FBAPickDetailCartons
.Include(x => x.FBAPickDetail.FBAShipOrder)
.Include(x => x.FBACartonLocation)
.Where(x => x.FBAPickDetail.FBAShipOrder.ReleasedDate >= startDate &&
x.FBAPickDetail.FBAShipOrder.ReleasedDate < endDate &&
x.FBAPickDetail.FBAShipOrder.CustomerCode == customerCode)
.ToList()
.OrderBy(x => x.FBAPickDetail.FBAShipOrder.ReleasedDate);
foreach (var i in inboundCollection)
{
balanceList.Add(new ItemStatisticLine
{
Reference = i.Container,
Container = i.Container,
Type = FBAOrderType.Inbound,
SKU = i.ShipmentId,
AmzRefId = i.AmzRefId,
WarehouseCode = i.WarehouseCode,
WarehouseLocation = i.FBAMasterOrder.WarehouseLocation,
Date = i.FBAMasterOrder.InboundDate,
QuantityChange = i.ActualQuantity
});
}
foreach (var i in outboundCollection)
{
balanceList.Add(new ItemStatisticLine
{
Reference = i.FBAPickDetail.FBAShipOrder.ShipOrderNumber,
Container = i.FBACartonLocation.Container,
Type = FBAOrderType.Outbound,
SKU = i.FBACartonLocation.ShipmentId,
AmzRefId = i.FBACartonLocation.AmzRefId,
WarehouseCode = i.FBACartonLocation.WarehouseCode,
WarehouseLocation = i.FBAPickDetail.FBAShipOrder.WarehouseLocation,
Date = i.FBAPickDetail.FBAShipOrder.ReleasedDate,
QuantityChange = -i.PickCtns
});
}
foreach (var s in skuList)
{
var allInbound = allInboundBeforeStartDate.Where(x => x.ShipmentId == s.SKU).Sum(x => x.ActualQuantity);
var allOutbound = allOutboundBeforeStartDate.Where(x => x.FBACartonLocation.ShipmentId == s.SKU).Sum(x => x.PickCtns);
var balance = allInbound - allOutbound;
_ws.Cells[index, 1] = "SKU:";
_ws.Cells[index, 2] = s.SKU;
_ws.Cells[index, 5] = "By end of " + startDate.ToString("yyyy-MM-dd");
_ws.Cells[index, 6] = "Quantity: " + balance.ToString();
index++;
_ws.Cells[index, 1] = "SKU";
_ws.Cells[index, 2] = "Reference";
_ws.Cells[index, 3] = "Container";
_ws.Cells[index, 4] = "Amz Ref Id";
_ws.Cells[index, 5] = "Dest. Warehouse";
_ws.Cells[index, 6] = "Op Warehouse";
_ws.Cells[index, 7] = "Type";
_ws.Cells[index, 8] = "Date";
_ws.Cells[index, 9] = "Quantity Change";
_ws.Cells[index, 10] = "Balance";
index++;
var list = balanceList.Where(x => x.SKU == s.SKU).OrderBy(x => x.Date).ToList();
foreach (var i in list)
{
balance += i.QuantityChange;
_ws.Cells[index, 1] = i.SKU;
_ws.Cells[index, 2] = i.Reference;
_ws.Cells[index, 3] = i.Container;
_ws.Cells[index, 4] = i.AmzRefId;
_ws.Cells[index, 5] = i.WarehouseCode;
_ws.Cells[index, 6] = i.WarehouseLocation;
_ws.Cells[index, 7] = i.Type;
_ws.Cells[index, 8] = i.Date;
_ws.Cells[index, 9] = i.QuantityChange;
_ws.Cells[index, 10] = balance;
index++;
}
_ws.Cells[index - 2 - list.Count, 9] = "By end of " + endDate.ToString("yyyy-MM-dd");
_ws.Cells[index - 2 - list.Count, 10] = "Quantity: " + balance;
index++;
}
var fullPath = @"D:\OtherReport\" + customerCode + "-SKU-Statement-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
//强行关闭Excel进程
var killer = new ExcelKiller();
killer.Dispose();
return(fullPath);
}