//生成StorageFee报告并返回完整路径
public string GenerateStorageReport(int customerId, DateTime startDate, DateTime closeDate, float p1Discount, float p2Discount, string[] warehouseLocations, bool includePrereleasedOrder)
{
var customerInDb = _context.UpperVendors.Find(customerId);
var palletLocationInDb = _context.FBAPalletLocations
.Include(x => x.FBAMasterOrder.Customer)
.Include(x => x.FBAMasterOrder.FBAPallets)
.Include(x => x.FBAPickDetails.Select(c => c.FBAShipOrder))
.Where(x => x.FBAMasterOrder.InboundDate < closeDate &&
x.FBAMasterOrder.Customer.Id == customerId)
.Where(x => warehouseLocations.Contains(x.FBAMasterOrder.WarehouseLocation));
IQueryable <FBAPickDetail> pickDetailInDb;
if (!includePrereleasedOrder)
{
pickDetailInDb = _context.FBAPickDetails
.Include(x => x.FBAPalletLocation.FBAMasterOrder.Customer)
.Include(x => x.FBAShipOrder)
.Where(x => x.FBAShipOrder.ShipDate < closeDate &&
x.FBAShipOrder.ShipDate >= startDate &&
x.FBAShipOrder.Status == FBAStatus.Shipped &&
x.FBAPalletLocation != null &&
x.FBAPalletLocation.FBAMasterOrder.InboundDate < closeDate &&
x.FBAPalletLocation.FBAMasterOrder.Customer.Id == customerId &&
x.PltsFromInventory != 0)
.Where(x => warehouseLocations.Contains(x.FBAShipOrder.WarehouseLocation));
}
else
{
pickDetailInDb = _context.FBAPickDetails
.Include(x => x.FBAPalletLocation.FBAMasterOrder.Customer)
.Include(x => x.FBAShipOrder)
.Where(x => ((x.FBAShipOrder.ReleasedDate < closeDate && x.FBAShipOrder.ReleasedDate >= startDate) ||
(x.FBAShipOrder.ShipDate < closeDate && x.FBAShipOrder.ShipDate >= startDate)) &&
(x.FBAShipOrder.Status == FBAStatus.Shipped || (x.FBAShipOrder.Status == FBAStatus.Released && x.FBAShipOrder.IsPrereleasing)) &&
x.FBAPalletLocation != null &&
x.FBAPalletLocation.FBAMasterOrder.InboundDate < closeDate &&
x.FBAPalletLocation.FBAMasterOrder.Customer.Id == customerId &&
x.PltsFromInventory != 0)
.Where(x => warehouseLocations.Contains(x.FBAShipOrder.WarehouseLocation));
//pickDetailInDb = _context.FBAPickDetails
// .Include(x => x.FBAPalletLocation.FBAMasterOrder.Customer)
// .Include(x => x.FBAShipOrder)
// .Where(x => x.FBAShipOrder.ReleasedDate < closeDate
// && x.FBAShipOrder.ReleasedDate >= startDate
// && x.FBAShipOrder.Status == FBAStatus.Released
// && x.FBAShipOrder.IsPrereleasing == true
// && x.FBAPalletLocation != null
// && x.FBAPalletLocation.FBAMasterOrder.InboundDate < closeDate
// && x.FBAPalletLocation.FBAMasterOrder.Customer.Id == customerId
// && x.PltsFromInventory != 0)
// .Where(x => warehouseLocations.Contains(x.FBAShipOrder.WarehouseLocation));
}
//var cartonLocationInDb = _context.FBACartonLocations
// .Include(x => x.FBAOrderDetail.FBAMasterOrder.Customer)
// .Where(x => x.FBAOrderDetail.FBAMasterOrder.InboundDate <= closeDate
// && x.FBAOrderDetail.FBAMasterOrder.Customer.Id == customerId);
foreach (var p in palletLocationInDb)
{
foreach (var pick in p.FBAPickDetails)
{
if (!includePrereleasedOrder)
{
//从原有托盘数量扣除状态为shipped状态，且发货日期在结束日期之前的运单中的托盘数量
if (pick.FBAShipOrder.Status == FBAStatus.Shipped && pick.FBAShipOrder.ShipDate < closeDate)
{
p.ActualPlts -= pick.PltsFromInventory;
}
}
else
{
//从原有托盘数量扣除状态为shipped和pre-releasing状态，且发货日期、预发货日期在结束日期之前的运单中的托盘数量
if ((pick.FBAShipOrder.Status == FBAStatus.Shipped || (pick.FBAShipOrder.Status == FBAStatus.Released && pick.FBAShipOrder.IsPrereleasing)) && pick.FBAShipOrder.ShipDate < closeDate && pick.FBAShipOrder.ReleasedDate < closeDate)
{
p.ActualPlts -= pick.PltsFromInventory;
}
}
}
}
_ws = _wb.Worksheets[1];
var startIndex = 2;
var palletsInDbGroup = palletLocationInDb.GroupBy(x => x.FBAMasterOrder.Container);
//对仓库剩余托盘进行收费
foreach (var p in palletsInDbGroup)
{
//var pallets = p.Sum(x => x.ActualPlts);
var standardPlts = p.Where(x => x.PalletSize == "P1").Sum(x => x.ActualPlts);
var plusPlts = p.Where(x => x.PalletSize == "P2").Sum(x => x.ActualPlts);
var pallets = Math.Round(standardPlts * p1Discount + 2 * plusPlts * p2Discount, 2);
//if (pallets == 0)
//{
// continue;
//}
_ws.Cells[startIndex, 1] = FBAOrderType.MasterOrder;
_ws.Cells[startIndex, 2] = p.First().Container;
_ws.Cells[startIndex, 3] = p.First().FBAMasterOrder.FBAPallets.Where(x => x.PalletSize == "P1").Sum(x => x.ActualPallets);
_ws.Cells[startIndex, 4] = p.First().FBAMasterOrder.FBAPallets.Where(x => x.PalletSize == "P2").Sum(x => x.ActualPallets);
_ws.Cells[startIndex, 5] = standardPlts;
_ws.Cells[startIndex, 6] = plusPlts;
_ws.Cells[startIndex, 7] = pallets;
_ws.Cells[startIndex, 8] = p.First().FBAMasterOrder.InboundDate.ToString("MM/dd/yyyy");
_ws.Cells[startIndex, 13] = p.First().FBAMasterOrder.WarehouseLocation;
startIndex += 1;
}
//对每一运单进行收费
var shipList = new List <ShipRecord>();
foreach (var s in pickDetailInDb)
{
var newShipRecord = new ShipRecord {
Reference = s.FBAShipOrder.ShipOrderNumber,
ShippedPlts = s.PltsFromInventory,
InboundDate = s.FBAPalletLocation.FBAMasterOrder.InboundDate.ToString("MM/dd/yyyy"),
OutboundDate = s.FBAShipOrder.ShipDate.ToString("MM/dd/yyyy"),
PalletSize = s.Size,
WarehouseLocation = s.FBAShipOrder.WarehouseLocation
};
if (includePrereleasedOrder && s.FBAShipOrder.Status == FBAStatus.Released && s.FBAShipOrder.IsPrereleasing)
{
newShipRecord.OutboundDate = s.FBAShipOrder.ReleasedDate.ToString("MM/dd/yyyy");
newShipRecord.IsPreReleased = true;
}
var sameShipRecord = shipList.SingleOrDefault(x => x.Reference == newShipRecord.Reference &&
x.InboundDate == newShipRecord.InboundDate &&
x.OutboundDate == newShipRecord.OutboundDate &&
x.PalletSize == newShipRecord.PalletSize);
if (sameShipRecord == null)
{
shipList.Add(newShipRecord);
}
else
{
sameShipRecord.ShippedPlts += newShipRecord.ShippedPlts;
}
}
foreach (var s in shipList)
{
if (s.PalletSize == "P1")
{
_ws.Cells[startIndex, 5] = s.ShippedPlts;
_ws.Cells[startIndex, 7] = Math.Round(s.ShippedPlts * p1Discount, 2);
}
else if (s.PalletSize == "P2")
{
_ws.Cells[startIndex, 6] = s.ShippedPlts;
_ws.Cells[startIndex, 7] = Math.Round(s.ShippedPlts * 2 * p2Discount, 2);
}
else
{
_ws.Cells[startIndex, 7] = "Invalid pallet size";
}
_ws.Cells[startIndex, 1] = FBAOrderType.ShipOrder;
_ws.Cells[startIndex, 2] = s.Reference;
_ws.Cells[startIndex, 8] = s.InboundDate;
_ws.Cells[startIndex, 9] = s.OutboundDate;
_ws.Cells[startIndex, 13] = s.WarehouseLocation;
if (s.IsPreReleased)
{
_ws.Cells[startIndex, 14] = "Pre-released";
}
startIndex += 1;
}
if (p1Discount != 1)
{
_ws.Cells[startIndex, 5] = (1 - p1Discount) * 100 + "%OFF";
}
if (p2Discount != 1)
{
_ws.Cells[startIndex, 6] = Math.Round((1 - p2Discount), 2) * 100 + "%OFF";
}
var range = _ws.get_Range("A1", "L" + (startIndex + 1));
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlHAlign.xlHAlignCenter;
var fullPath = @"D:\StorageFee\FBA-" + customerInDb.CustomerCode + "-StorageFee-" + 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();
return(fullPath);
}
//生成StorageFee报告并返回完整路径
public string GenerateStorageReport(int customerId, DateTime startDate, DateTime closeDate)
{
var actualCloseDate = closeDate.AddDays(1);
var customerInDb = _context.UpperVendors.Find(customerId);
var palletLocationInDb = _context.FBAPalletLocations
.Include(x => x.FBAMasterOrder.Customer)
.Include(x => x.FBAMasterOrder.FBAPallets)
.Include(x => x.FBAPickDetails.Select(c => c.FBAShipOrder))
.Where(x => x.FBAMasterOrder.InboundDate < actualCloseDate &&
x.FBAMasterOrder.Customer.Id == customerId);
var pickDetailInDb = _context.FBAPickDetails
.Include(x => x.FBAPalletLocation.FBAMasterOrder.Customer)
.Include(x => x.FBAShipOrder)
.Where(x => x.FBAShipOrder.ShipDate < actualCloseDate &&
x.FBAShipOrder.ShipDate >= startDate &&
x.FBAShipOrder.Status == FBAStatus.Shipped &&
x.FBAPalletLocation != null &&
x.FBAPalletLocation.FBAMasterOrder.InboundDate < actualCloseDate &&
x.FBAPalletLocation.FBAMasterOrder.Customer.Id == customerId &&
x.PltsFromInventory != 0);
//var cartonLocationInDb = _context.FBACartonLocations
// .Include(x => x.FBAOrderDetail.FBAMasterOrder.Customer)
// .Where(x => x.FBAOrderDetail.FBAMasterOrder.InboundDate <= closeDate
// && x.FBAOrderDetail.FBAMasterOrder.Customer.Id == customerId);
foreach (var p in palletLocationInDb)
{
foreach (var pick in p.FBAPickDetails)
{
//从原有托盘数量扣除状态为shipped状态，且发货日期在结束日期之前的运单中的托盘数量
if (pick.FBAShipOrder.Status == FBAStatus.Shipped && pick.FBAShipOrder.ShipDate < actualCloseDate)
{
p.ActualPlts -= pick.PltsFromInventory;
}
}
}
_ws = _wb.Worksheets[1];
var startIndex = 2;
var palletsInDbGroup = palletLocationInDb.GroupBy(x => x.Container);
//对仓库剩余托盘进行收费
foreach (var p in palletsInDbGroup)
{
var pallets = p.Sum(x => x.ActualPlts);
//if (pallets == 0)
//{
// continue;
//}
_ws.Cells[startIndex, 1] = FBAOrderType.MasterOrder;
_ws.Cells[startIndex, 2] = p.First().Container;
_ws.Cells[startIndex, 6] = p.First().FBAMasterOrder.FBAPallets.Sum(x => x.ActualPallets);
_ws.Cells[startIndex, 7] = pallets;
_ws.Cells[startIndex, 8] = p.First().FBAMasterOrder.InboundDate.ToString("MM/dd/yyyy");
startIndex += 1;
}
//对每一运单进行收费
var shipList = new List <ShipRecord>();
foreach (var s in pickDetailInDb)
{
var newShipRecord = new ShipRecord {
Reference = s.FBAShipOrder.ShipOrderNumber,
ShippedPlts = s.PltsFromInventory,
InboundDate = s.FBAPalletLocation.FBAMasterOrder.InboundDate.ToString("MM/dd/yyyy"),
OutboundDate = s.FBAShipOrder.ShipDate.ToString("MM/dd/yyyy")
};
var sameShipRecord = shipList.SingleOrDefault(x => x.Reference == newShipRecord.Reference &&
x.InboundDate == newShipRecord.InboundDate &&
x.OutboundDate == newShipRecord.OutboundDate);
if (sameShipRecord == null)
{
shipList.Add(newShipRecord);
}
else
{
sameShipRecord.ShippedPlts += newShipRecord.ShippedPlts;
}
}
foreach (var s in shipList)
{
_ws.Cells[startIndex, 1] = FBAOrderType.ShipOrder;
_ws.Cells[startIndex, 2] = s.Reference;
_ws.Cells[startIndex, 7] = s.ShippedPlts;
_ws.Cells[startIndex, 8] = s.InboundDate;
_ws.Cells[startIndex, 9] = s.OutboundDate;
startIndex += 1;
}
var fullPath = @"D:\StorageFee\FBA-" + customerInDb.CustomerCode + "-StorageFee-" + 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();
return(fullPath);
}
