private static CalculationAwbInfo[] GetInfo(
IEnumerable<AirWaybillData> awbs,
IEnumerable<ApplicationEditData> items,
IReadOnlyDictionary<long, decimal> tariffs)
{
return awbs.Select(awb =>
{
var rows = items.Where(a => a.AirWaybillId == awb.Id).ToArray();
var info = new CalculationAwbInfo
{
AirWaybillId = awb.Id,
TotalCostOfSenderForWeight = awb.TotalCostOfSenderForWeight ?? 0,
FlightCost = awb.FlightCost ?? 0,
CustomCost = awb.CustomCost ?? 0,
BrokerCost = awb.BrokerCost ?? 0,
AdditionalCost = awb.AdditionalCost,
TotalSenderRate = rows.Sum(x => CalculationHelper.GetTotalSenderRate(x.SenderRate, x.Weight)),
TotalScotchCost = rows.Sum(x => CalculationHelper.GetSenderTapeTariff(tariffs, x.SenderId) * x.Count) ?? 0,
TotalFactureCost = rows.Sum(x => x.FactureCost ?? 0),
TotalFactureCostEx = rows.Sum(x => x.FactureCostEx ?? 0),
TotalPickupCost = rows.Sum(x => x.PickupCost ?? 0),
TotalTransitCost = rows.Sum(x => x.TransitCost ?? 0),
TotalInsuranceCost = rows.Sum(x => CalculationHelper.GetInsuranceCost(x.Value, x.InsuranceRate)),
BrokerCostPerKg = null,
CostPerKgOfSender = null,
CustomCostPerKg = null,
FlightCostPerKg = null,
ProfitPerKg = null,
Profit = 0
};
info.Profit = rows.Sum(x => CalculationHelper.GetProfit(x, tariffs)) - info.TotalExpenses;
var totalWeight = (decimal)rows.Sum(x => x.Weight ?? 0);
if(totalWeight != 0)
{
info.ProfitPerKg = info.Profit / totalWeight;
info.CostPerKgOfSender = info.TotalSenderRate / totalWeight;
info.FlightCostPerKg = info.FlightCost / totalWeight;
info.CustomCostPerKg = info.CustomCost / totalWeight;
info.BrokerCostPerKg = info.BrokerCost / totalWeight;
}
return info;
}).ToArray();
}
private static int DrawInfo(ExcelWorksheet ws, CalculationAwbInfo info, int iRow, int count)
{
var range = ws.Cells[iRow, 1, iRow, count];
range.Merge = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "sender";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 8].Value = info.CostPerKgOfSender;
ws.Cells[iRow, 9].Style.Font.Bold = true;
ws.Cells[iRow, 9].Value = info.TotalSenderRate;
ws.Cells[iRow, 14].Value = info.TotalScotchCost;
ws.Cells[iRow, 16].Value = info.TotalFactureCost;
ws.Cells[iRow, 17].Value = info.TotalFactureCostEx;
ws.Cells[iRow, 18].Value = info.TotalPickupCost;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.TotalOfSender;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "flight";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 9].Style.Font.Bold = true;
ws.Cells[iRow, 9].Value = info.FlightCostPerKg;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.FlightCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "custom";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 9].Style.Font.Bold = true;
ws.Cells[iRow, 9].Value = info.CustomCostPerKg;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.CustomCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "broker";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 9].Style.Font.Bold = true;
ws.Cells[iRow, 9].Value = info.BrokerCostPerKg;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.BrokerCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "insurance";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.TotalInsuranceCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "forwarder";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.TotalTransitCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "additional";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.AdditionalCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "cost total";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.TotalExpenses;
var rangeCost = ws.Cells[iRow, 1, iRow, count];
rangeCost.Style.Fill.PatternType = ExcelFillStyle.Solid;
rangeCost.Style.Fill.BackgroundColor.SetColor(Color.HotPink);
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 19].Value = info.ProfitPerKg;
if (info.ProfitPerKg <= 0)
{
ws.Cells[iRow, 19].Style.Font.Color.SetColor(Color.Red);
}
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.Profit;
if (info.Profit <= 0)
{
ws.Cells[iRow, 20].Style.Font.Color.SetColor(Color.Red);
}
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
return iRow;
}