public async Task <ActionResult> EnergyConsumption(string venueCodeSelected, string orderStatus)
{
//GET DATA REQUIRED FOR CSV GENERATION
OverviewDAL db = new OverviewDAL();
RentalsDAL rentals_db = new RentalsDAL();
ViewModels.Overview.OverviewViewModel model = new ViewModels.Overview.OverviewViewModel();
model.TrackUnitStartDate = Convert.ToDateTime(Session["StartDate"]);
model.TrackUnitEndDate = Convert.ToDateTime(Session["EndDate"]);
var motorUsages = await GetMotroUsage(model.TrackUnitStartDate, model.TrackUnitEndDate, orderStatus);
model.FuelConsumptionPerMachine = db.GetDWHCustConsPerMachine(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.TrackUnitStartDate.Date, model.TrackUnitEndDate.Date);
var groupedConsumption = model.FuelConsumptionPerMachine.GroupBy(x => new { x.inventtransid, x.serialNumber });
List <_ConsumptionAndSerialNo> csList = new List <_ConsumptionAndSerialNo>();
foreach (var gc in groupedConsumption)
{
//There will be only one value against the group of inventransid and serialno so we will take the first value
var consumption = gc.Select(x => x.machineconsumption).FirstOrDefault(); //this is the total consumption for the machine
var noOfHours = gc.Select(x => x.machinerunningHourM1).FirstOrDefault(); //this is the total number of hours the above is cost is calculated
var unit = gc.Select(x => x.unit).FirstOrDefault();
csList.Add(new _ConsumptionAndSerialNo {
ConsumptionCost = noOfHours > 0 ? consumption / noOfHours : 0, SerialNo = gc.Key.serialNumber, Unit = unit
});
}
//we will further check if there is more than one serial numbers with different cost value so to take the average of the same.
var finalConsumptionList = csList.GroupBy(x => x.SerialNo).Select(y => new _ConsumptionAndSerialNo {
SerialNo = y.Key, ConsumptionCost = y.Average(z => z.ConsumptionCost), Unit = y.FirstOrDefault().Unit
});
var reportInfo = (from mu in motorUsages
join sc in finalConsumptionList on mu.SerialNumber equals sc.SerialNo
select new _ReportInfo
{
MachineName = mu.MachineType,
MachineType = mu.MachineType,
SerialNumber = mu.SerialNumber,
TransactionDate = mu.TransDate,
Value = mu.RunningHour * sc.ConsumptionCost,
ValueUnit = sc.Unit
}).ToList();
return(CreateReport(Resources.EnergyConsumption, model.TrackUnitStartDate, model.TrackUnitEndDate, reportInfo, "Energy_Consumption"));
}
public async Task <IEnumerable <RentalOrder> > Process(IEnumerable <RentalOrder> orders, IEnumerable <TrackUnitData> units, Customer activeCustomer, string userId, string mapTrans = "")
{
if (this.ViewType == "Map" && orders.Count() > 0)
{
this.OrderStatusesCheck.Add("20");
this.ResultsPerPage = "All";
orders = orders.Where(x => this.OrderStatusesCheck.Contains(x.OrderStatusId));
if (!String.IsNullOrEmpty(mapTrans))
{
var machine = orders.Where(x => x.InventTransId == mapTrans).FirstOrDefault();
this.OrderNumber = machine == null ? "" : machine.SalesId;
this.SerialNumberSearch = machine == null ? "" : machine.ObjectId;
}
}
this.CustomerAccounts = orders.Select <RentalOrder, CustomerAccount>(x => new CustomerAccount {
CustAccount = x.CustAccount, AccountName = x.AccountName
}).Distinct()
.OrderByDescending(x => this.CustomerAccountsCheck.Contains(x.CustAccount)).ThenBy(y => y.AccountName);
if (this.ShowFuelConsumptionMachines == "true")
{
OverviewDAL overviewDAL = new OverviewDAL();
IEnumerable <FuelConsumption> fuelConsumptionPerMachine = overviewDAL.GetDWHCustConsPerMachine(activeCustomer.DatabaseName, activeCustomer.CompanyId, activeCustomer.CustomerId, this.StartDate, this.EndDate);
orders = orders.Where(i => fuelConsumptionPerMachine.Select(n => n.inventtransid).Contains(i.InventTransId));
}
if (this.ShowGPSTrackedMachines == "true")
{
OverviewDAL overviewDAL = new OverviewDAL();
//Get required data
IEnumerable <OnSiteMachine> OnSiteMachines = overviewDAL.GetDWHMachineOnSite(activeCustomer);
//Remove OnSiteMachines with no LastActive date
OnSiteMachines = OnSiteMachines.Where(i => i.lastActiveDate.HasValue && i.lastActiveDate != Convert.ToDateTime("1900-01-01")).ToList();
//Filter out machines past the users active hours selection
units = units.Where(i => i.GpsFixTime > DateTime.Now.AddHours(-this.TrackUnitHours));
//Remove machines which dont have matching OnsiteMachines
List <string> onSiteSerialNumbers = OnSiteMachines.Select(i => i.serialNumber).ToList();
orders = orders.Where(i => onSiteSerialNumbers.Contains(i.ObjectId)).ToList();
}
if (this.ShowNotInUse == "true")
{
OverviewDAL overviewDAL = new OverviewDAL();
if (activeCustomer.overviewVersion == null || activeCustomer.overviewVersion == "1")
{
var customers = orders.Select(x => x.CustAccount).Distinct();
int daysNotInUse = (await new ROLWeb.Models.UserStore().FindByIdAsync(userId)).MachinesNotInUseDays;
var OverviewDAL = new ROLWeb.Models.Overview.OverviewDAL();
var tempOrders = orders;
orders = Enumerable.Empty <RentalOrder>();
foreach (string customer in customers)
{
var machines = OverviewDAL.GetMachinesNotInUse(activeCustomer.DatabaseName, activeCustomer, daysNotInUse.ToString());
var selectedOrders = tempOrders.Join(machines, x => new { x.CustAccount, x.SalesId, x.ObjectId }, y => new { CustAccount = customer, y.SalesId, y.ObjectId }, (ord, mac) => ord);
orders = orders.Concat(selectedOrders);
}
}
else if (activeCustomer.overviewVersion == "2")
{
//Get required data
IEnumerable <OnSiteMachine> OnSiteMachines = overviewDAL.GetDWHMachineOnSite(activeCustomer);
//Remove OnSiteMachines with no LastActive date
OnSiteMachines = OnSiteMachines.Where(i => i.lastActiveDate.HasValue && i.lastActiveDate != Convert.ToDateTime("1900-01-01")).ToList();
int daysNotInUse = (await new ROLWeb.Models.UserStore().FindByIdAsync(userId)).MachinesNotInUseDays;
int MachinesNotInUseHours = daysNotInUse * 24;
//filterout machines in use
OnSiteMachines = OnSiteMachines.Where(i => Convert.ToDateTime(i.lastActiveDate) < DateTime.Now.AddHours(-MachinesNotInUseHours) || Convert.ToDateTime(i.lastActiveDate).Date == DateTime.Now.AddDays(1).Date);
//Filter out machines past the users active hours selection
units = units.Where(i => i.GpsFixTime > DateTime.Now.AddHours(-this.TrackUnitHours));
//Remove machines which dont have matching OnsiteMachines
List <string> onSiteSerialNumbers = OnSiteMachines.Select(i => i.serialNumber).ToList();
orders = orders.Where(i => onSiteSerialNumbers.Contains(i.ObjectId)).ToList();
}
}
// Filtering
if (this.CustomerAccountsCheck.Count() > 0)
{
orders = orders.Where(x => this.CustomerAccountsCheck.Contains(x.CustAccount));
}
if (!String.IsNullOrWhiteSpace(this.VenueSearch))
{
orders = orders.Where(x => x.DeliveryName.ToLower().Contains(this.VenueSearch.ToLower()) || x.DeliveryCity.ToLower().Contains(this.VenueSearch.ToLower()));
}
if (!String.IsNullOrWhiteSpace(this.OrderNumber))
{
orders = orders.Where(x => x.SalesId.Contains(this.OrderNumber));
}
if (!String.IsNullOrWhiteSpace(this.PurchOrderSearch))
{
orders = orders.Where(x => x.PurchOrderFormNum.ToLower().Contains(this.PurchOrderSearch.ToLower()));
}
if (!String.IsNullOrWhiteSpace(this.ProductSearch))
{
orders = orders.Where(x => x.ProductId.ToLower().Contains(this.ProductSearch.ToLower()) || x.ItemName.ToLower().Contains(this.ProductSearch.ToLower()));
}
if (!String.IsNullOrWhiteSpace(this.FleetNumberSearch))
{
orders = orders.Where(x => x.AssetId.ToLower().Contains(this.FleetNumberSearch.ToLower()));
}
if (!String.IsNullOrWhiteSpace(this.SerialNumberSearch))
{
orders = orders.Where(x => x.ObjectId.ToLower().Contains(this.SerialNumberSearch.ToLower()));
}
this.OrderStatuses = orders.Select <RentalOrder, OrderStatus>(x => new OrderStatus(activeCustomer.DatabaseName)
{
OrderStatusId = x.OrderStatusId
}).Distinct().OrderBy(y => y.OrderStatusName);
if (this.OrderStatusesCheck.Count() > 0)
{
orders = orders.Where(x => this.OrderStatusesCheck.Contains(x.OrderStatusId));
}
if (this.ContactPersonsCheck.Count() > 0)
{
orders = orders.Where(x => this.ContactPersonsCheck.Contains(x.ContactPersonId));
}
if (!string.IsNullOrEmpty(this.ProjectLeader))
{
orders = orders.Where(x => x.ContactPersonId == ProjectLeader);
}
this.Venues = orders.Select <RentalOrder, Venue>(x => new Venue {
DeliveryName = x.DeliveryName, DeliveryCity = x.DeliveryCity, RorVenueCode = x.RorVenueCode
}).Distinct()
.OrderByDescending(x => this.VenueCheck.Contains(x.RorVenueCode)).ThenBy(y => y.DeliveryName);
this.ItemGroups = orders.Select <RentalOrder, ItemGroup>(x => new ItemGroup {
ItemGroupId = x.ItemGroupId, ItemGroupName = x.ItemGroupName
}).Distinct()
.OrderByDescending(x => this.ItemGroupCheck.Contains(x.ItemGroupId)).ThenBy(y => y.ItemGroupName);
this.PriceRateCodes = orders.Select <RentalOrder, String>(x => x.PriceRateCode).Distinct();
if (this.VenueCheck.Count() > 0)
{
orders = orders.Where(x => this.VenueCheck.Contains(x.RorVenueCode));
}
if (this.PriceRateCodeCheck.Count() > 0)
{
orders = orders.Where(x => this.PriceRateCodeCheck.Contains(x.PriceRateCode));
}
if (this.ShowMachinesOnRent == "true")
{
List <string> itemGroupCheck = this.ItemGroups.Where(i => i.ItemGroupId != "31900").Select(i => i.ItemGroupId).ToList();
this.ItemGroupCheck = itemGroupCheck;
}
if (this.ShowExtrasOnRent == "true")
{
List <string> itemGroupCheck = this.ItemGroups.Where(i => i.ItemGroupId == "31900").Select(i => i.ItemGroupId).ToList();
this.ItemGroupCheck = itemGroupCheck;
}
if (this.ItemGroupCheck.Count() > 0)
{
orders = orders.Where(x => this.ItemGroupCheck.Contains(x.ItemGroupId));
}
if (this.RentalTermCheck.Count() > 0)
{
orders = orders.Where(x => this.RentalTermCheck.Contains(x.RentalTerm));
}
if (units != null)
{
foreach (var order in orders)
{
var unit = units.Where(x => x.ReferenceNumber == order.ObjectId).FirstOrDefault();
if (unit != null)
{
order.IsMachineLocalized = true;
order.Latitude = unit.Latitude;
order.Longitude = unit.Longitude;
TimeSpan diffTime = DateTime.Now.Subtract(unit.GpsFixTime);
if (diffTime.TotalMinutes > 240)
{
order.markerColor = "yellow";
}
else if (unit.Speed > 15)
{
order.markerColor = "green";
}
else if (unit.Input1.Value == true)
{
order.markerColor = "blue";
}
else
{
order.markerColor = "red";
}
}
else
{
order.IsMachineLocalized = false;
order.Latitude = 0.0;
order.Longitude = 0.0;
order.markerColor = "";
}
}
}
if (this.ShowGPSTrackedMachines == "true" || this.ShowNotInUse == "true")
{
orders = orders.Where(i => i.IsMachineLocalized == true);
}
this.ContactPersons = orders.Select <RentalOrder, ContactPerson>(x => new ContactPerson {
ContactPersonId = x.ContactPersonId, ContactPersonName = x.ContactPerson
}).Distinct()
.OrderByDescending(x => this.ContactPersonsCheck.Contains(x.ContactPersonId)).ThenBy(y => y.ContactPersonName);
// Ordering
if (this.OrderBy == "OnHireAsc")
{
orders = orders.OrderBy(x => x.DateOnHire).ThenBy(x => x.InventTransId);
}
if (this.OrderBy == "OnHireDesc")
{
orders = orders.OrderByDescending(x => x.DateOnHire).ThenByDescending(x => x.InventTransId);
}
// Calculations
if (this.ResultsPerPage == "5")
{
this.Orders = orders.Skip((this.CurrentPage - 1) * 5).Take(5);
this.TotalPages = (int)Math.Ceiling((decimal)orders.Count() / 5);
}
else if (this.ResultsPerPage == "15")
{
this.Orders = orders.Skip((this.CurrentPage - 1) * 15).Take(15);
this.TotalPages = (int)Math.Ceiling((decimal)orders.Count() / 15);
}
else if (this.ResultsPerPage == "25")
{
this.Orders = orders.Skip((this.CurrentPage - 1) * 25).Take(25);
this.TotalPages = (int)Math.Ceiling((decimal)orders.Count() / 25);
}
else if (this.ResultsPerPage == "All")
{
this.Orders = orders;
this.TotalPages = 1;
}
this.TotalOrders = orders.Count();
this.CurrentPage = Math.Max(this.CurrentPage, 1);
this.CurrentPage = Math.Min(this.TotalPages, this.CurrentPage);
if (this.ViewType == "Map")
{
var machine = this.Orders.Where(x => x.IsMachineLocalized).FirstOrDefault();
if (this.Orders.Count() == 1)
{
this.MapDetails = Json.Encode(new
{
Latitude = machine == null ? 0.0 : machine.Latitude,
Longitude = machine == null ? 0.0 : machine.Longitude,
Zoom = 14,
mapBounds = false
});
}
else
{
this.MapDetails = Json.Encode(new
{
Latitude = machine == null ? 0.0 : machine.Latitude,
Longitude = machine == null ? 0.0 : machine.Longitude,
Zoom = 9,
mapBounds = true
});
}
}
return(orders);
}
public async Task <ActionResult> GetOverview(DateTime?startDate, DateTime?endDate, DateTime?trackUnitStartDate, DateTime?trackUnitEndDate, string venueCode, string contactPersonId, string selectMachine, int?trackUnitHours, bool?trackUnitCurrentRentals, bool?trackUnitClosedRentals, bool?trackUnitPendingDeliveries, bool?trackUnitPendingCollections)
{
if (!ActiveCustomer.showOverview)
{
return(HttpNotFound());
}
// *** Populate session variables with new valuespassed in
if (startDate.HasValue)
{
Session["StartDate"] = startDate.Value;
}
if (endDate.HasValue)
{
Session["EndDate"] = endDate.Value;
}
if (trackUnitStartDate.HasValue)
{
Session["TrackUnitStartDate"] = trackUnitStartDate;
}
if (trackUnitEndDate.HasValue)
{
Session["TrackUnitEndDate"] = trackUnitEndDate;
}
if (trackUnitHours.HasValue)
{
Session["TrackUnitHours"] = trackUnitHours;
}
if (venueCode != null)
{
Session["venueCode"] = venueCode;
}
if (contactPersonId != null)
{
Session["contactPersonId"] = contactPersonId;
}
if (trackUnitCurrentRentals.HasValue && trackUnitCurrentRentals.Value == true)
{
Session["TrackUnitCurrentRentals"] = true;
}
else
{
Session["TrackUnitCurrentRentals"] = false;
}
if (trackUnitClosedRentals.HasValue && trackUnitClosedRentals.Value == true)
{
Session["TrackUnitClosedRentals"] = true;
}
else
{
Session["TrackUnitClosedRentals"] = false;
}
if (trackUnitPendingDeliveries.HasValue && trackUnitPendingDeliveries.Value == true)
{
Session["TrackUnitPendingDeliveries"] = true;
}
else
{
Session["TrackUnitPendingDeliveries"] = false;
}
if (trackUnitPendingCollections.HasValue && trackUnitPendingCollections.Value == true)
{
Session["TrackUnitPendingCollections"] = true;
}
else
{
Session["TrackUnitPendingCollections"] = false;
}
if (selectMachine != null)
{
if (selectMachine == "-1")
{
Session["SelectedMachine"] = null;
}
else
{
Session["SelectedMachine"] = selectMachine;
}
}
// *** Populate the model with basic info about customer, and filters passed in
OverviewViewModel model = new OverviewViewModel();
var userCustomers = (await db.GetUserCustomers(User.Identity.GetUserId())).Select(x => new { CustomerKey = x.CustomerKey, CustomerName = String.Format("{0} ({1})", x.Name, x.City), CustomerCurrency = x.currency });
model.userCustomers = new SelectList(userCustomers, "CustomerKey", "CustomerName", "CustomerCurrency");
model.accountManager = await db.GetAccountManagerDetails(this.ActiveCustomer);
model.customerSelected = this.ActiveCustomer.CustomerKey;
model.StartDate = DateTime.Today.AddMonths(-1);
model.EndDate = DateTime.Today.AddMonths(1);
model.TrackUnitStartDate = DateTime.Today.AddMonths(-1);
model.TrackUnitEndDate = DateTime.Today.AddMonths(1);
model.TrackUnitHours = 120;
model.TrackUnitCurrentRentals = (bool)Session["TrackUnitCurrentRentals"];
model.TrackUnitClosedRentals = (bool)Session["TrackUnitClosedRentals"];
model.TrackUnitPendingDeliveries = (bool)Session["TrackUnitPendingDeliveries"];
model.TrackUnitPendingCollections = (bool)Session["TrackUnitPendingCollections"];
if (Session["StartDate"] != null && Session["EndDate"] != null)
{
model.StartDate = (DateTime)Session["StartDate"];
model.EndDate = (DateTime)Session["EndDate"];
}
else
{
Session["StartDate"] = model.StartDate;
Session["EndDate"] = model.EndDate;
}
if (Session["TrackUnitStartDate"] != null && Session["TrackUnitEndDate"] != null)
{
model.TrackUnitStartDate = (DateTime)Session["TrackUnitStartDate"];
model.TrackUnitEndDate = (DateTime)Session["TrackUnitEndDate"];
}
else
{
Session["TrackUnitStartDate"] = model.TrackUnitStartDate;
Session["TrackUnitEndDate"] = model.TrackUnitEndDate;
}
if (Session["TrackUnitHours"] != null)
{
model.TrackUnitHours = (int)Session["TrackUnitHours"];
}
else
{
Session["TrackUnitHours"] = model.TrackUnitHours;
}
if (!string.IsNullOrWhiteSpace((string)Session["venueCode"]))
{
model.venueCodeSelected = (string)Session["venueCode"];
}
else
{
Session["venueCode"] = model.venueCodeSelected = "";
}
if (!string.IsNullOrWhiteSpace((string)Session["contactPersonId"]))
{
model.contactPersonIdSelected = (string)Session["contactPersonId"];
}
else
{
Session["contactPersonId"] = model.contactPersonIdSelected = "";
}
if (!string.IsNullOrWhiteSpace((string)Session["SelectedMachine"]))
{
model.selectedMachineSerial = (string)Session["SelectedMachine"];
}
else
{
Session["SelectedMachine"] = model.selectedMachineSerial = "";
}
// *** Orders, Venues and Order Statuses ***
DateTime minDate;
if (model.TrackUnitStartDate < model.StartDate)
{
minDate = model.TrackUnitStartDate;
}
else
{
minDate = model.StartDate;
}
if (DateTime.Today < minDate)
{
minDate = DateTime.Today;
}
DateTime maxDate;
if (model.TrackUnitEndDate > model.EndDate)
{
maxDate = model.TrackUnitEndDate;
}
else
{
maxDate = model.EndDate;
}
if (DateTime.Today > maxDate)
{
maxDate = DateTime.Today;
}
IEnumerable <RentalOrder> fullOrders = await ActiveCustomer.GetRentalOrders(new RentalsDAL(), minDate, maxDate);
IEnumerable <RentalOrder> orders = fullOrders.Where(i => i.DateOnHire.Date <= model.EndDate.Date && ((!i.DateOffHire.HasValue || i.DateOffHire.Value.Date >= model.StartDate)));
IEnumerable <RentalOrder> fuelConsumptionPerMachineOrders = fullOrders.Where(i => i.DateOnHire.Date <= model.TrackUnitEndDate.Date && ((!i.DateOffHire.HasValue || i.DateOffHire.Value.Date >= model.TrackUnitStartDate.Date)));
IEnumerable <RentalOrder> onSiteMachineOrders = fullOrders.Where(i => i.DateOnHire.Date <= DateTime.Today && ((!i.DateOffHire.HasValue || i.DateOffHire.Value.Date >= DateTime.Today)));
IEnumerable <RentalOrder> filteredOrders = orders;
List <string> projectVenueCodes = new List <string>();
IdentityUser userPrefs = (await new ROLWeb.Models.UserStore().FindByIdAsync(User.Identity.GetUserId()));
if (userPrefs.ShowProjectMachines)
{
foreach (RentalOrder order in filteredOrders)
{
if (!projectVenueCodes.Contains(order.RorVenueCode) && order.ContactPersonId == userPrefs.ContactPersonId)
{
projectVenueCodes.Add(order.RorVenueCode);
}
}
if (projectVenueCodes.Count() > 0)
{
filteredOrders = filteredOrders.Where(i => projectVenueCodes.Contains(i.RorVenueCode));
}
}
// Populate list of venues based on ALL orders
var venueCheck = new List <String>();
model.Venues = filteredOrders
.Select <RentalOrder, Venue>(x =>
new Venue
{
DeliveryName = x.DeliveryName,
DeliveryCity = x.DeliveryCity,
RorVenueCode = x.RorVenueCode
})
.Distinct()
.OrderByDescending(x => venueCheck.Contains(x.RorVenueCode))
.ThenBy(y => y.DeliveryName);
if (projectVenueCodes.Count() > 0)
{
model.Venues = model.Venues.Where(i => projectVenueCodes.Contains(i.RorVenueCode));
}
// Then filter the orders to only those in the current venue
if (!string.IsNullOrWhiteSpace(model.venueCodeSelected))
{
filteredOrders = filteredOrders.Where(i => i.RorVenueCode == model.venueCodeSelected);
}
model.orderContactPeople = new SelectList(filteredOrders.GroupBy(i => i.ContactPersonId).Select(i => i.First()), "ContactPersonId", "ContactPerson");
if (!string.IsNullOrWhiteSpace(model.contactPersonIdSelected))
{
filteredOrders = filteredOrders.Where(i => i.ContactPersonId == model.contactPersonIdSelected);
}
string[] CurrentRentalCodes = Models.Rentals.OrderStatus.GetOrderStatusID("CurrentRentals", ActiveCustomer.DatabaseName);
// Get the count of extras on rent
model.extrasOnRent = filteredOrders.Where(i => i.ItemGroupId == "31900" && CurrentRentalCodes.Contains(i.OrderStatusId)).Count();
// Get a list of statuses that exist withn the filterd list of orders remove extras that are on rent
model.orderStatus = filteredOrders.Where(i => !(i.ItemGroupId == "31900" && CurrentRentalCodes.Contains(i.OrderStatusId))).GroupBy(x => x.OrderStatusId).Select(y => new OrderStatusCount {
OrderStatusId = y.Key, numOrders = y.Count()
});
// *** Cost Overview, exchange rates ***
IEnumerable <ExchangeRate> exchangeRates = db.GetExchangeRates();
model.ExchangeRate = exchangeRates.Where(i => i.CountryCode == ActiveCustomer.currency.ToUpper()).FirstOrDefault();
if (model.ExchangeRate == null)
{
model.ExchangeRate = new ExchangeRate()
{
CountryCode = "EUR",
EuroRate = 1m
};
}
model.CostMultiplierElectric = 1m;
model.CostMultiplierDiesel = 1m;
model.CostMultiplierHybrid = 1m;
if (ActiveCustomer.currency.ToLower() == "eur")
{
model.CurrencySymbol = "€";
model.CostMultiplierElectric = 1m;
if (ActiveCustomer.CompanyId == "140")
{
model.CostMultiplierDiesel = 1.8m;
}
else
{
model.CostMultiplierDiesel = 1m;
}
model.CostMultiplierHybrid = 1m;
}
else if (ActiveCustomer.currency.ToLower() == "dkk")
{
model.CurrencySymbol = "KR";
model.CostMultiplierDiesel = 13.98m;
model.CostMultiplierElectric = 2.3m;
model.CostMultiplierHybrid = 1m;
}
else if (ActiveCustomer.currency.ToLower() == "sek")
{
model.CurrencySymbol = "SEK";
model.CostMultiplierDiesel = 18m;
model.CostMultiplierElectric = 2.5m;
model.CostMultiplierHybrid = 1m;
}
else if (ActiveCustomer.currency.ToLower() == "nok")
{
model.CurrencySymbol = "NOK";
model.CostMultiplierDiesel = 15m;
model.CostMultiplierElectric = 2.5m;
model.CostMultiplierHybrid = 1m;
}
else if (ActiveCustomer.currency.ToLower() == "pln")
{
model.CurrencySymbol = "PLN";
model.CostMultiplierDiesel = 1.21m;
model.CostMultiplierElectric = 1m;
model.CostMultiplierHybrid = 1m;
}
else if (ActiveCustomer.currency.ToLower() == "gbp")
{
model.CurrencySymbol = "£";
model.CostMultiplierDiesel = model.ExchangeRate.EuroRate;
model.CostMultiplierElectric = model.ExchangeRate.EuroRate;
model.CostMultiplierHybrid = model.ExchangeRate.EuroRate;
}
else
{
model.CurrencySymbol = "€";
}
if (ActiveCustomer.showOverviewCosts)
{
if (string.IsNullOrWhiteSpace(model.venueCodeSelected))
{
if (ActiveCustomer.showChildBRInvoices)
{
if (ActiveCustomer.useInvoiceAccount)
{
model.rentalCosts = await db.GetRentalCostsInvoiceAccountChildBR(this.ActiveCustomer, model.StartDate, model.EndDate);
model.rentalCostsLastYear = await db.GetRentalCostsInvoiceAccountChildBR(this.ActiveCustomer, model.StartDate.AddYears(-1), model.EndDate.AddYears(-1));
}
else
{
model.rentalCosts = await db.GetRentalCostsOrderAccountChildBR(this.ActiveCustomer, model.StartDate, model.EndDate);
model.rentalCostsLastYear = await db.GetRentalCostsOrderAccountChildBR(this.ActiveCustomer, model.StartDate.AddYears(-1), model.EndDate.AddYears(-1));
}
}
else
{
if (ActiveCustomer.useInvoiceAccount)
{
model.rentalCosts = await db.GetRentalCostsInvoiceAccount(this.ActiveCustomer, model.StartDate, model.EndDate);
model.rentalCostsLastYear = await db.GetRentalCostsInvoiceAccount(this.ActiveCustomer, model.StartDate.AddYears(-1), model.EndDate.AddYears(-1));
}
else
{
model.rentalCosts = await db.GetRentalCostsOrderAccount(this.ActiveCustomer, model.StartDate, model.EndDate);
model.rentalCostsLastYear = await db.GetRentalCostsOrderAccount(this.ActiveCustomer, model.StartDate.AddYears(-1), model.EndDate.AddYears(-1));
}
}
}
else
{
model.rentalCosts = await db.GetRentalCostsInvoiceVenueAccount(this.ActiveCustomer, model.StartDate, model.EndDate);
model.rentalCosts = model.rentalCosts.Where(i => i.RorVenueCode == model.venueCodeSelected);
model.rentalCostsLastYear = await db.GetRentalCostsInvoiceVenueAccount(this.ActiveCustomer, model.StartDate.AddYears(-1), model.EndDate.AddYears(-1));
model.rentalCostsLastYear = model.rentalCosts.Where(i => i.RorVenueCode == model.venueCodeSelected);
}
}
// *** Response Time ***
Task GetCustKPIs = new Task(() =>
{
if (ActiveCustomer.showOverviewResponseTime || ActiveCustomer.showOverviewMachinesNotInUse)
{
if (string.IsNullOrEmpty(model.venueCodeSelected))
{
if (projectVenueCodes.Count() > 0)
{
IEnumerable <CustTRWKPIs> allKPIs = db.GetDWHCustVenuTRWKPIs(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.venueCodeSelected, model.StartDate, model.EndDate);
List <CustTRWKPIs> projectVenueKPIs = allKPIs.Where(i => projectVenueCodes.Contains(i.venuecode)).ToList();
List <decimal> resolutionTime = projectVenueKPIs.Where(i => i.avgResolutionTime.HasValue).Select(i => i.avgResolutionTime.Value).ToList();
List <decimal> responseTime = projectVenueKPIs.Where(i => i.avgResponseTime.HasValue).Select(i => i.avgResponseTime.Value).ToList();
if (projectVenueKPIs.Count > 0)
{
CustTRWKPIs custTRWKPIs = new CustTRWKPIs()
{
companyid = projectVenueKPIs.First().companyid,
customerid = projectVenueKPIs.First().customerid,
venuecode = projectVenueKPIs.First().venuecode,
machineCount = projectVenueKPIs.Sum(rec => rec.machineCount),
avgResolutionTime = resolutionTime.Sum(rec => rec),
avgResponseTime = responseTime.Sum(rec => rec),
firstDayBreakdownCount = projectVenueKPIs.Sum(rec => rec.firstDayBreakdownCount),
onTimeDeliveryCount = projectVenueKPIs.Sum(rec => rec.onTimeDeliveryCount),
totalDeliveryCount = projectVenueKPIs.Sum(rec => rec.totalDeliveryCount),
upTimePerc = projectVenueKPIs.Sum(rec => rec.upTimePerc) / projectVenueKPIs.Count(),
solvedByPhoneCount = projectVenueKPIs.Sum(rec => rec.solvedByPhoneCount)
};
if (resolutionTime.Count() > 0)
{
custTRWKPIs.avgResolutionTime /= resolutionTime.Count();
}
if (responseTime.Count() > 0)
{
custTRWKPIs.avgResponseTime /= responseTime.Count();
}
model.ResponseTimeKPI = custTRWKPIs;
}
var allPickupTimes = db.GetDWHCustTRWKPI_pickUpTime(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.StartDate, model.EndDate);
model.CustTRWKPI_pickUpTime = allPickupTimes.FirstOrDefault();
}
else
{
var allKPIs = db.GetDWHCustTRWKPIs(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.StartDate, model.EndDate);
model.ResponseTimeKPI = allKPIs.FirstOrDefault();
var allPickupTimes = db.GetDWHCustTRWKPI_pickUpTime(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.StartDate, model.EndDate);
model.CustTRWKPI_pickUpTime = allPickupTimes.FirstOrDefault();
}
}
else
{
var allKPIs = db.GetDWHCustVenuTRWKPIs(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.venueCodeSelected, model.StartDate, model.EndDate);
model.ResponseTimeKPI = allKPIs.FirstOrDefault(i => i.venuecode == model.venueCodeSelected);
var allPickupTimes = db.GetDWHCustVenuTRWKPI_pickUpTime(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, model.venueCodeSelected, ActiveCustomer.CustomerId, model.StartDate, model.EndDate);
model.CustTRWKPI_pickUpTime = allPickupTimes.FirstOrDefault();
}
if (model.ResponseTimeKPI == null)
{
model.ResponseTimeKPI = new CustTRWKPIs();
model.ResponseTimeKPI.avgResponseTime = 0.0m;
model.ResponseTimeKPI.avgResolutionTime = 0.0m;
}
if (model.ResponseTimeKPI.avgResponseTime == null)
{
model.ResponseTimeKPI.avgResponseTime = 0.0m;
}
if (model.ResponseTimeKPI.avgResolutionTime == null)
{
model.ResponseTimeKPI.avgResolutionTime = 0.0m;
}
if (model.CustTRWKPI_pickUpTime == null)
{
model.CustTRWKPI_pickUpTime = new CustTRWKPI_pickUpTime();
model.CustTRWKPI_pickUpTime.avgPickupTime = 0.0m;
}
if (model.CustTRWKPI_pickUpTime.avgPickupTime == null)
{
model.CustTRWKPI_pickUpTime.avgPickupTime = 0.0m;
}
//If the active customer is French recalculate the On time delivery KPI
if (ActiveCustomer.CompanyId == "140")
{
model.ResponseTimeKPI.totalDeliveryCount = filteredOrders.Where(i => i.DateOnHire < DateTime.Now).Count();
}
}
});
GetCustKPIs.Start();
// *** Per machine data ***
Task PerMachineData = new Task(() =>
{
model.FuelConsumptionPerMachine = db.GetDWHCustConsPerMachine(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.TrackUnitStartDate, model.TrackUnitEndDate);
if (model.TrackUnitCurrentRentals || model.TrackUnitClosedRentals || model.TrackUnitPendingDeliveries || model.TrackUnitPendingCollections)
{
model.TrackUnitRentalCodes = new List <string>();
if (model.TrackUnitCurrentRentals == true)
{
model.TrackUnitRentalCodes.Add(Models.Rentals.OrderStatus.GetOrderStatusID_String("CurrentRentals", ActiveCustomer.DatabaseName));
}
if (model.TrackUnitClosedRentals == true)
{
model.TrackUnitRentalCodes.Add(Models.Rentals.OrderStatus.GetOrderStatusID_String("ClosedRentals", ActiveCustomer.DatabaseName));
}
if (model.TrackUnitPendingDeliveries == true)
{
model.TrackUnitRentalCodes.Add(Models.Rentals.OrderStatus.GetOrderStatusID_String("PendingDeliveries", ActiveCustomer.DatabaseName));
}
if (model.TrackUnitPendingCollections == true)
{
model.TrackUnitRentalCodes.Add(Models.Rentals.OrderStatus.GetOrderStatusID_String("PendingCollections", ActiveCustomer.DatabaseName));
}
string[] fuelConsumptionPerMachineInventTransIds = fuelConsumptionPerMachineOrders.Where(i => model.TrackUnitRentalCodes.Contains(i.OrderStatusId) && model.FuelConsumptionPerMachine.Select(n => n.inventtransid).Contains(i.InventTransId)).Select(i => i.InventTransId).ToArray();
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => fuelConsumptionPerMachineInventTransIds.Contains(i.inventtransid));
if (!string.IsNullOrWhiteSpace(model.contactPersonIdSelected))
{
//Bind contactpersonid to matching orders and fuelconsumptions
foreach (FuelConsumption machine in model.FuelConsumptionPerMachine)
{
foreach (RentalOrder order in fuelConsumptionPerMachineOrders)
{
if (machine.inventtransid == order.InventTransId)
{
machine.ContactPersonId = order.ContactPersonId;
}
}
}
//Filter to machines with matching contact person id
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => i.ContactPersonId == model.contactPersonIdSelected);
}
}
if (projectVenueCodes.Count() > 0)
{
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => projectVenueCodes.Contains(i.rorvenuecode));
}
if (!string.IsNullOrEmpty(model.venueCodeSelected))
{
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => i.rorvenuecode == model.venueCodeSelected).ToList();
}
var duplicateMachines = model.FuelConsumptionPerMachine.Where(i => model.FuelConsumptionPerMachine.Where(x => x.inventtransid == i.inventtransid).Count() > 1).GroupBy(n => n.inventtransid);
List <FuelConsumption> nonDuplicateMachines = model.FuelConsumptionPerMachine.Where(i => model.FuelConsumptionPerMachine.Where(x => x.inventtransid == i.inventtransid).Count() < 2).ToList();
List <FuelConsumption> summedDuplicates = new List <FuelConsumption>();
foreach (var inventTransitIdGroup in duplicateMachines)
{
FuelConsumption summedDupplicate = inventTransitIdGroup.First();
summedDupplicate.machineconsumption = inventTransitIdGroup.Sum(i => i.machineconsumption);
summedDupplicate.machineEmission = inventTransitIdGroup.Sum(i => i.machineEmission);
summedDupplicate.machineFuelCost = inventTransitIdGroup.Sum(i => i.machineFuelCost);
summedDupplicate.machinerunningHourM1 = inventTransitIdGroup.Sum(i => i.machinerunningHourM1);
summedDupplicate.lastActiveDate = inventTransitIdGroup.Max(i => i.lastActiveDate);
summedDuplicates.Add(summedDupplicate);
}
model.FuelConsumptionPerMachine = nonDuplicateMachines.Concat(summedDuplicates);
//Create list of machine types to populate the overview dropdown
model.SelectMachines =
model.FuelConsumptionPerMachine.Select(i => new SelectMachine()
{
Code = i.serialNumber, Name = i.machineType, FleetNumber = i.fleetNumber
})
.OrderBy(i => i.Name);
// Filter results based on selected machine type
if (model.selectedMachineSerial != "")
{
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => i.serialNumber == model.selectedMachineSerial).ToList();
}
});
PerMachineData.Start();
PerMachineData.Wait();
// *** Machines not in Use ***
if (ActiveCustomer.showOverviewMachinesNotInUse)
{
try
{
var user = (await new ROLWeb.Models.UserStore().FindByIdAsync(User.Identity.GetUserId()));
int daysNotInUse = user.MachinesNotInUseDays;
model.MachinesNotInUseHours = daysNotInUse * 24;
//Dashboard V1.0 Definition
if (ActiveCustomer.overviewVersion == null || ActiveCustomer.overviewVersion == "1")
{
var customers = orders.Select(x => x.CustAccount).Distinct();
int machinesNotInUse = 0;
foreach (string customer in customers)
{
var machines = db.GetMachinesNotInUse(this.ActiveCustomer.DatabaseName, this.ActiveCustomer, daysNotInUse.ToString());
var ordersMachines = orders.Join(machines, x => new { x.CustAccount, x.SalesId, x.ObjectId }, y => new { CustAccount = customer, y.SalesId, y.ObjectId }, (ord, mac) => ord);
machinesNotInUse += ordersMachines.Count();
}
model.MachinesNotInUse = machinesNotInUse;
}
//Dashboard V2.0 Definition
if (ActiveCustomer.overviewVersion == "2")
{
//Get required data
model.MachinesNotInUse = 0;
model.OnSiteMachines = db.GetDWHMachineOnSite(ActiveCustomer);
if (!string.IsNullOrWhiteSpace(model.contactPersonIdSelected))
{
onSiteMachineOrders = onSiteMachineOrders.Where(i => i.ContactPersonId == model.contactPersonIdSelected);
}
//Remove OnSiteMachines with no LastActive date
model.OnSiteMachines = model.OnSiteMachines.Where(i => i.lastActiveDate.HasValue && i.lastActiveDate != Convert.ToDateTime("1900-01-01")).ToList();
IEnumerable <TrackUnitData> units = await rentals_db.GetTrackUnitData();
//Filter out machines past the users active hours selection
//units = units.Where(i => i.GpsFixTime > DateTime.Now.AddHours(-model.TrackUnitHours));
//Remove machines which dont have matching order details
List <string> onSiteMachineOrderSerialNumbers = onSiteMachineOrders.Select(i => i.ObjectId).ToList();
model.OnSiteMachines = model.OnSiteMachines.Where(i => onSiteMachineOrderSerialNumbers.Contains(i.serialNumber)).ToList();
//Remove machines which dont have track unit data
List <string> trackUnitSerialNumbers = units.Select(i => i.ReferenceNumber).ToList();
model.OnSiteMachines = model.OnSiteMachines.Where(x => trackUnitSerialNumbers.Contains(x.serialNumber)).ToList();
//Filter out non venue machines
if (!string.IsNullOrWhiteSpace(model.venueCodeSelected))
{
model.OnSiteMachines = model.OnSiteMachines.Where(i => i.rorvenuecode == model.venueCodeSelected).ToList();
}
//Filter out non project machines
if (projectVenueCodes.Count() > 0)
{
model.OnSiteMachines = model.OnSiteMachines.Where(i => projectVenueCodes.Contains(i.rorvenuecode)).ToList();
}
//Count Machines in use
foreach (var machine in model.OnSiteMachines)
{
if (Convert.ToDateTime(machine.lastActiveDate) < DateTime.Now.AddHours(-model.TrackUnitHours) || Convert.ToDateTime(machine.lastActiveDate).Date == DateTime.Now.AddDays(1).Date)
{
model.MachinesNotInUse++;
}
}
//Apply Track Unit Data
model.OnSiteMachines.ForEach(i => i.Process(onSiteMachineOrders.First(x => x.ObjectId == i.serialNumber)));
model.OnSiteMachines = model.ProcessOnSiteMachines(model.OnSiteMachines, units);
model.MachinesInUse = model.OnSiteMachines.Count() - model.MachinesNotInUse;
if (model.OnSiteMachines == null)
{
model.OnSiteMachines = new List <OnSiteMachine>();
}
}
}
catch (Exception e)
{
model.OnSiteMachines = new List <OnSiteMachine>();
model.MachinesInUse = 0;
}
}
else
{
model.OnSiteMachines = new List <OnSiteMachine>();
}
// *** Energy Consumption and CO2 emissions ***
var consumptionTotals = new List <FuelConsumptionAndEmission>();
if (ActiveCustomer.showOverviewEnergyConsumption || ActiveCustomer.showOverviewCO2Emissions)
{
var machines_GroupedByFuelType = model.FuelConsumptionPerMachine.GroupBy(i => i.fueltype);
foreach (var machineGroup in machines_GroupedByFuelType)
{
if (!string.IsNullOrWhiteSpace(machineGroup.First().fueltype))
{
consumptionTotals.Add(new FuelConsumptionAndEmission()
{
fueltype = machineGroup.First().fueltype,
machineconsumption = machineGroup.Sum(i => i.machineconsumption),
custaccount = ActiveCustomer.CustomerId,
machineEmission = machineGroup.Sum(i => i.machineEmission),
machineFuelCost = machineGroup.Sum(i => i.machineFuelCost),
machinerunningHourM1 = machineGroup.Sum(i => i.machinerunningHourM1),
unit = machineGroup.First().unit
});
}
}
}
model.ConsumptionTotals = consumptionTotals;
GetCustKPIs.Wait();
if (ActiveCustomer.overviewVersion == null)
{
return(PartialView("_Overview_1", model));
}
else
{
return(PartialView("_Overview_" + ActiveCustomer.overviewVersion, model));
}
}
public async Task <ActionResult> CO2Emissions(string venueCodeSelected, string orderStatus)
{
//GET DATA REQUIRED FOR CSV GENERATION
OverviewDAL db = new OverviewDAL();
RentalsDAL rentals_db = new RentalsDAL();
ViewModels.Overview.OverviewViewModel model = new ViewModels.Overview.OverviewViewModel();
model.TrackUnitStartDate = Convert.ToDateTime(Session["TrackUnitStartDate"]);
model.TrackUnitEndDate = Convert.ToDateTime(Session["TrackUnitEndDate"]);
IEnumerable <RentalOrder> orders = await ActiveCustomer.GetRentalOrders(rentals_db, model.TrackUnitStartDate.Date, model.TrackUnitEndDate.Date);
model.FuelConsumptionPerMachine = db.GetDWHCustConsPerMachine(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.TrackUnitStartDate, model.TrackUnitEndDate);
if (!string.IsNullOrWhiteSpace(orderStatus))
{
string[] orderStatusCodes = orderStatus.Split(',');
IEnumerable <RentalOrder> FuelConsumptionPerMachineOrders = await ActiveCustomer.GetRentalOrders(new RentalsDAL(), model.TrackUnitStartDate, model.TrackUnitEndDate);
string[] orderInventTransIds = FuelConsumptionPerMachineOrders.Where(i => orderStatusCodes.Contains(i.OrderStatusId)).Select(i => i.InventTransId).ToArray();
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => orderInventTransIds.Contains(i.inventtransid));
}
if (!string.IsNullOrEmpty(venueCodeSelected))
{
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => i.rorvenuecode == venueCodeSelected).ToList();
}
model.SelectMachines =
model.FuelConsumptionPerMachine
.GroupBy(
i => i.machineType,
(i1, i2) => new SelectMachine()
{
Code = i1, Name = i2.First().machineTypeDesc, Count = i2.Count()
})
.OrderBy(i => i.Name);
//Find the template breeam report
var fi = new FileInfo(HttpContext.Server.MapPath("/App_Data/ReportTemp/breeam-report.xlsx"));
//Load the template into memory and begin adding customer information
using (var breeamReport = new ExcelPackage(fi))
{
var ws = breeamReport.Workbook.Worksheets["H. Materieel"];
ws.Cells["A1"].Value = Resources.CO2ProducedByMachines;
ws.Cells["D1"].Value = Resources.BuildingSiteMachines;
ws.Cells["A7"].Value = Resources.Date;
ws.Cells["B7"].Value = Resources.CompanyName;
ws.Cells["C7"].Value = Resources.MachineType;
ws.Cells["D7"].Value = Resources.Brand;
ws.Cells["E7"].Value = Resources.SerialNumber;
ws.Cells["F7"].Value = Resources.AdverageFuel;
ws.Cells["G7"].Value = Resources.RunningHours;
ws.Cells["H7"].Value = Resources.DailyFuelConsumption;
ws.Cells["I7"].Value = Resources.KGCo2Total;
ws.Cells["J7"].Value = Resources.RentalPeriod;
int rowCount = 8;
foreach (var machine in model.FuelConsumptionPerMachine)
{
string date = model.TrackUnitStartDate.ToShortDateString() + "-" + model.TrackUnitEndDate.ToShortDateString();
RentalOrder order = orders.Where(i => i.InventTransId == machine.inventtransid && i.ObjectId == machine.serialNumber).FirstOrDefault();
string companyName = ActiveCustomer.Name;
string machineType = "N/A";
foreach (var machineModel in model.SelectMachines)
{
if (machineModel.Code == machine.machineType)
{
machineType = machineModel.Name;
break;
}
}
string brandAndModel = machine.machineType;
string serialNumber = machine.serialNumber;
string adverageFuel = string.Format("{0:n1}", (machine.machineconsumption / (Decimal)(model.TrackUnitEndDate - model.TrackUnitStartDate).TotalDays));
string runningHours = string.Format("{0:n1}", machine.machinerunningHourM1);
string dailyConsumption = string.Format("{0:n1}", machine.machineconsumption);
string kgCO2Total = string.Format("{0:n1}", machine.machineEmission);
string rentalPeriod = "N/A";
if (order != null)
{
rentalPeriod = order.DateOnHire.ToShortDateString() + "-" + order.ExpectedDateOffHire.ToShortDateString();
}
ws.Cells["A" + rowCount.ToString()].Value = date;
ws.Cells["B" + rowCount.ToString()].Value = companyName;
ws.Cells["C" + rowCount.ToString()].Value = machineType;
ws.Cells["D" + rowCount.ToString()].Value = brandAndModel;
ws.Cells["E" + rowCount.ToString()].Value = serialNumber;
ws.Cells["F" + rowCount.ToString()].Value = adverageFuel;
ws.Cells["G" + rowCount.ToString()].Value = runningHours;
ws.Cells["H" + rowCount.ToString()].Value = dailyConsumption;
ws.Cells["I" + rowCount.ToString()].Value = kgCO2Total;
ws.Cells["J" + rowCount.ToString()].Value = rentalPeriod;
rowCount++;
}
return(File(breeamReport.GetAsByteArray(), ".xlsx", "BREEAM_" + model.TrackUnitStartDate.ToShortDateString() + "-" + model.TrackUnitEndDate.ToShortDateString() + "_" + ActiveCustomer.Name + ".xlsx"));
};
}