public async Task <IEnumerable <RentalOrder> > GetRentalOrders(RentalsDAL db, DateTime startDate, DateTime endDate)
{
IEnumerable <RentalOrder> orders;
// If the customer has business relations, then get the rentals for those too
if (this.showChildBROrders)
{
// If we're using InspHire then the relationships are handled by the DAL
if (this.DatabaseName == Helpers.DataBases.NetsuiteInsphire)
{
orders = (await db.GetRentalOrdersChildBR(this, startDate, endDate));
}
else
{
var validCustomers = (await new OverviewDAL().GetCustomersRegistered()).Where(x => x.DatabaseName == this.DatabaseName && x.CompanyId == this.CompanyId);
orders = (await db.GetRentalOrdersChildBR(this, startDate, endDate));
orders = orders.Join(validCustomers, x => x.CustAccount, y => y.CustomerId, (ord, cust) => ord);
}
}
// Otherwise just get the active customer's rentals
else
{
orders = (await db.GetRentalOrders(this, startDate, endDate));
}
return(orders);
}
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 ActionResult AccountManagerDetails(string databaseName, string customerId, string companyId)
{
APIAuth auth = new APIAuth(Request, Response);
AuthInfo authInfo = auth.CheckLogin();
if (authInfo.IsAuthenticated)
{
Customer localCust = new Customer();
localCust.DatabaseName = databaseName;
localCust.CustomerId = customerId;
localCust.CompanyId = companyId;
//Check if this user has access to this customer
var userCustomers = db.GetUserCustomersSync(authInfo.User.Id);
if (userCustomers.Count(x => x.CustomerKey == localCust.CustomerKey) > 0)
{
//AccountManager accountManager = db.GetAccountManagerDetails(localCust).GetAwaiter().GetResult();
var accountManagers = db.GetAccountManagerDetailsSync(localCust);
AccountManager accountManager = accountManagers.FirstOrDefault();
RentalsDAL dbRental = new RentalsDAL();
string depotEmail = dbRental.GetOnRentEmailAddressSync(localCust).FirstOrDefault();;
accountManager.OnRentEmailAddress = depotEmail;
return(Json(accountManager, JsonRequestBehavior.AllowGet));
}
else
{
return(auth.Forbidden());
}
}
else
{
return(auth.Forbidden());
}
}
//This Original Code: Can be cleared once we are sure the new function is doing fine.
//public async Task<ActionResult> MotorUsage(string venueCodeSelected, string orderStatus)
//{
// //GET DATA REQUIRED FOR CSV GENERATION
// OverviewDAL db = new OverviewDAL();
// RentalsDAL rentals_db = new RentalsDAL();
// DateTime TrackUnitStartDate = Convert.ToDateTime(Session["TrackUnitStartDate"]);
// DateTime TrackUnitEndDate = Convert.ToDateTime(Session["TrackUnitEndDate"]);
// IEnumerable<RentalOrder> fuelConsumptionPerMachineOrders = await db.GetRentalOrders(ActiveCustomer, TrackUnitStartDate, TrackUnitEndDate);
// IEnumerable<MotorUsage> motorUsages = db.GetDWHCustRunningHourPerMachinePerDay(ActiveCustomer, TrackUnitStartDate, TrackUnitEndDate);
// //Get rental status codes from query string
// if (!string.IsNullOrWhiteSpace(orderStatus))
// {
// string[] orderStatusCodes = orderStatus.Split(',');
// //Filter out machines with no matching order status
// fuelConsumptionPerMachineOrders = fuelConsumptionPerMachineOrders.Where(i => orderStatusCodes.Contains(i.OrderStatusId));
// }
// //Filter out machines with no matchinging order
// motorUsages = motorUsages.Where(i => fuelConsumptionPerMachineOrders.Select(x => x.ObjectId).Contains(i.SerialNumber));
// //Filterout machine with no run time data
// //below line is commented in response to the Ticket#367 so to avoid skipping the non-used dates
// //motorUsages = motorUsages.Where(i => i.RunningHour > 0.0m);
// //Get a list of non duplicate dates from the motor usage data
// List<DateTime> motorUsageDates = motorUsages.GroupBy(i => i.TransDate).Select(i => i.Key).OrderByDescending(i => i).ToList();
// //Get a list of non diplicate machine from the data (Serial number is the unique ID here)
// List<MotorUsage> motorUsageMachines = motorUsages.GroupBy(i => i.SerialNumber).Select(i => new MotorUsage() { SerialNumber = i.Key, MachineType = i.First().MachineType }).ToList();
// //Find the template breeam report
// var fi = new FileInfo(HttpContext.Server.MapPath("/App_Data/ReportTemp/motor-usage.xlsx"));
// //Load the template into memory and begin adding customer information
// using (var energyconsumption = new ExcelPackage(fi))
// {
// var ws = energyconsumption.Workbook.Worksheets["Consumption"];
// Dictionary<string, string> machineColumns = new Dictionary<string, string>();
// //Add Date column heading
// ws.Cells["A1:C1"].Merge = true;
// ws.Cells["A1"].Value = Resources.RunningHours;
// int headerRow = 3;
// string headingCell = "A" + headerRow.ToString();
// ws.Cells[headingCell].IsRichText = true;
// var dateRichText = ws.Cells[headingCell].RichText;
// //ws.Cells["A7"].Value =
// ws.Cells[headingCell].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
// var dateRichTextValue = dateRichText.Add(Resources.Date);
// dateRichTextValue.Bold = true;
// //Loop though and create column headings for each of the machines
// for (int i = 0; i < motorUsageMachines.Count(); i++)
// {
// string columnName = utlStore.GetExcelColumn(i + 2);
// machineColumns.Add(motorUsageMachines.ElementAt(i).SerialNumber, columnName);
// var Cell = ws.Cells[columnName + headerRow.ToString()];
// Cell.IsRichText = true;
// var RichText = Cell.RichText;
// var RichValue = RichText.Add(motorUsageMachines.ElementAt(i).MachineType + "(" + motorUsageMachines.ElementAt(i).SerialNumber + ")" + Environment.NewLine);
// RichValue.Bold = true;
// RichValue = RichText.Add(Resources.StartRentalDate + ": " + TrackUnitStartDate.ToString("dd/MM/yyyy"));
// RichValue.Color = System.Drawing.Color.Green;
// RichValue.Bold = false;
// Cell.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
// Cell.AutoFitColumns(30);
// }
// int rowCount = headerRow + 1;
// foreach (DateTime date in motorUsageDates)
// {
// ws.Cells["A" + rowCount.ToString()].Value = date.ToShortDateString();
// IEnumerable<MotorUsage> dateMachines = motorUsages.Where(i => i.TransDate.Date == date.Date);
// foreach (MotorUsage machine in dateMachines)
// {
// ws.Cells[machineColumns[machine.SerialNumber] + rowCount.ToString()].Value = machine.RunningHour.ToString("N2");
// }
// rowCount++;
// }
// return File(energyconsumption.GetAsByteArray(), ".xlsx", "Motor_Usage_" + TrackUnitStartDate.ToShortDateString() + "-" + TrackUnitEndDate.ToShortDateString() + "_" + ActiveCustomer.Name + ".xlsx");
// };
//}
private async Task <IEnumerable <MotorUsage> > GetMotroUsage(DateTime TrackUnitStartDate, DateTime TrackUnitEndDate, string orderStatus)
{
//GET DATA REQUIRED FOR CSV GENERATION
OverviewDAL db = new OverviewDAL();
RentalsDAL rentals_db = new RentalsDAL();
IEnumerable <RentalOrder> fuelConsumptionPerMachineOrders = await db.GetRentalOrders(ActiveCustomer, TrackUnitStartDate, TrackUnitEndDate);
IEnumerable <MotorUsage> motorUsages = db.GetDWHCustRunningHourPerMachinePerDay(ActiveCustomer, TrackUnitStartDate, TrackUnitEndDate);
//Get rental status codes from query string
if (!string.IsNullOrWhiteSpace(orderStatus))
{
string[] orderStatusCodes = orderStatus.Split(',');
//Filter out machines with no matching order status
fuelConsumptionPerMachineOrders = fuelConsumptionPerMachineOrders.Where(i => orderStatusCodes.Contains(i.OrderStatusId));
}
//Filter out machines with no matchinging order
motorUsages = motorUsages.Where(i => fuelConsumptionPerMachineOrders.Select(x => x.ObjectId).Contains(i.SerialNumber));
return(motorUsages);
}
public ActionResult Orders(DateTime startDate, DateTime endDate, string databaseName, string customerId, string companyId)
{
APIAuth auth = new APIAuth(Request, Response);
AuthInfo authInfo = auth.CheckLogin();
if (authInfo.IsAuthenticated)
{
Customer localCust = new Customer();
localCust.DatabaseName = databaseName;
localCust.CustomerId = customerId;
localCust.CompanyId = companyId;
//Check if this user has access to this customer
var userCustomers = db.GetUserCustomersSync(authInfo.User.Id);
//Customer selectedCompany = userCustomers.Where(x => x.CustomerKey == localCust.CustomerKey).First();
//return Json(new { foo = "ok", baz = "alee" });
//if (selectedCompany != null)
if (userCustomers.Count(x => x.CustomerKey == localCust.CustomerKey) > 0)
{
IEnumerable <RentalOrder> orders;
Customer activeCust = db.GetCustomer(localCust.DatabaseName, localCust.CompanyId, localCust.CustomerId);
if (activeCust.showChildBROrders)
{
var validCustomers = new OverviewDAL().GetCustomersRegisteredSync().Where(x => x.DatabaseName == activeCust.DatabaseName && x.CompanyId == activeCust.CompanyId);
orders = db.GetRentalOrdersChildBRSync(activeCust, startDate, endDate);
orders = orders.Join(validCustomers, x => x.CustAccount, y => y.CustomerId, (ord, cust) => ord);
}
else
{
orders = db.GetRentalOrdersSync(localCust, startDate, endDate);
}
//need to manually add trackunit data
RentalsDAL dbRental = new RentalsDAL();
IEnumerable <TrackUnitData> units = dbRental.GetTrackUnitDataSync();
using (var orderEnum = orders.GetEnumerator())
{
while (orderEnum.MoveNext())
{
RentalOrder order = orderEnum.Current;
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 = "";
}
}
}
//END trackunit
//new CustomJsonResult(
//return Content(new JavaScriptSerializer().Serialize(orders.ToList()), "application/json");
return(new CustomJsonResult {
Data = orders.ToList()
});
}
else
{
return(Json(new { error = "No access" }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(auth.Forbidden());
}
}
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"));
};
}
