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