public ActionResult FirstYield(GenerateReportModel model)
{
// get list of stations
var stations = this.StationService.List().Where(s => s.StationType == "Inspection").ToList();
var yieldModel = new FirstYieldModel();
yieldModel.Charts = new Collection <Highcharts>();
foreach (var station in stations.OrderBy(s => s.Identifier))
{
// inspection and related rework and scrap states
var inspection = station.Identifier;
var rework = inspection + "_REWORK";
var reject = inspection + "_SCRAP";
// linq to get the stages
var list = this.FilterStages(this.ProductionStageService.List(), model)
.Where(s => s.Station.Identifier == rework ||
s.Station.Identifier == reject ||
s.Station.Identifier == inspection)
.GroupBy(s => s.Station)
.OrderBy(g => g.Count())
.Select(g => new { Name = (g.Key.StationType == "Inspection" ? "Passed" : g.Key.StationType), Y = g.Count() })
.ToArray();
// get the total of "not first times"
var bad = list.Where(a => a.Name != "Passed").Sum(a => a.Y);
// we need to subract the number of
// not first times from the first times
var results = new List <object>();
foreach (var a in list)
{
if (a.Name == "Passed")
{
results.Add(new { Name = a.Name, Y = a.Y - bad });
}
else
{
results.Add(a);
}
}
// build chart
var chart = ChartHelper.PieChart(
string.Format("{0} First Time Yield", station.Description),
"First Time Yield",
results.ToArray(),
station.Identifier);
yieldModel.Charts.Add(chart);
}
return(PartialView("FirstYield", yieldModel));
}
public ActionResult FinalYield(GenerateReportModel model)
{
// linq to get the final yeild of good, bad, and ugly
var list = this.FilterStages(this.ProductionStageService.List(), model)
.Where(s => s.Station.StationType == "Scrap" || s.Station.StationType == "Complete")
.GroupBy(e => e.Station)
.OrderBy(g => g.Count())
.Select(g => new { Name = g.Key.Description, Y = g.Count() })
.ToArray();
return(PartialView("FinalYield", ChartHelper.PieChart("Final Yields", "Station Final Yields", list, "final")));
}
public void GenerateReport(GenerateReportModel generateReport, Guid userId, string nameFile)
{
try
{
nameFile = Helpers.Helpers.GetNameFileWithCurrentDate(nameFile);
Report report = new Report
{
ReportTypeId = generateReport.ReportTypeId,
SignerUserId = userId,
Name = nameFile
};
_docFlowContext.Reports.Add(report);
ReportHistory reportHistory = new ReportHistory
{
ReportId = report.Id,
CreateUserId = userId,
CreateDate = DateTime.Now
};
_docFlowContext.ReportHistory.Add(reportHistory);
foreach (ReportLabelModel label in generateReport.Values)
{
ReportValue reportValue = new ReportValue
{
ReportId = report.Id,
ReportLabelId = label.Id,
Value = label.Value
};
_docFlowContext.ReportValues.Add(reportValue);
ReportValuesHistory reportValuesHistory = new ReportValuesHistory
{
ReportValueId = reportValue.Id,
ReportHistoryId = reportHistory.Id,
NewValue = reportValue.Value
};
_docFlowContext.ReportValuesHistory.Add(reportValuesHistory);
}
_docFlowContext.SaveChanges();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
public ActionResult Index(GenerateReportViewModel grvm)
{
GenerateReportModel grm = new GenerateReportModel();
var getRows = db.Employees_Take_Leaves.Where(s => s.financial_year_start == grvm.financial_year_start).Where(s => s.financial_year_end == grvm.financial_year_end).Where(s => s.leave_id == grvm.leave_id);
var leaveName = db.Leaves.FirstOrDefault(s => s.id == grvm.leave_id).name;
grm.leave_name = leaveName;
grm.leave_id = grvm.leave_id;
grm.financial_year_end = grvm.financial_year_end;
grm.financial_year_start = grvm.financial_year_start;
return(RedirectToAction("GetReports", "GenerateReport", grm));
}
public async Task GenerateReportFile([FromQuery] DateTime dateFrom, [FromQuery] DateTime dateTo, [FromQuery] string tagIds, [FromQuery] string type)
{
var requestModel = new GenerateReportModel();
var tags = tagIds?.Split(",").ToList();
if (tags != null)
{
foreach (string tag in tags)
{
requestModel.TagIds.Add(int.Parse(tag));
}
}
requestModel.DateFrom = dateFrom;
requestModel.DateTo = dateTo;
requestModel.Type = type;
var result = await _reportService.GenerateReportFile(requestModel);
const int bufferSize = 4086;
byte[] buffer = new byte[bufferSize];
Response.OnStarting(async() =>
{
if (!result.Success)
{
Response.ContentLength = result.Message.Length;
Response.ContentType = "text/plain";
Response.StatusCode = 400;
byte[] bytes = Encoding.UTF8.GetBytes(result.Message);
await Response.Body.WriteAsync(bytes, 0, result.Message.Length);
await Response.Body.FlushAsync();
}
else
{
await using var wordStream = result.Model;
int bytesRead;
Response.ContentLength = wordStream.Length;
Response.ContentType = requestModel.Type == "docx"
? "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
while ((bytesRead = wordStream.Read(buffer, 0, buffer.Length)) > 0 &&
!HttpContext.RequestAborted.IsCancellationRequested)
{
await Response.Body.WriteAsync(buffer, 0, bytesRead);
await Response.Body.FlushAsync();
}
}
});
}
public async Task <ActionResult> GenerateReport([FromBody] GenerateReportModel generateReport)
{
try
{
await _reportService.GenerateReportAsync(generateReport, UserId, generateReport.Name);
await PushFilesToDrive(generateReport.Name, generateReport.ReportTypeId, generateReport.Values, (int)DriveTypeEnum.Private);
return(Ok());
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
public async Task GenerateReportFile(GenerateReportModel requestModel)
{
OperationDataResult <FileStreamModel> result = await _reportService.GenerateReportFile(requestModel);
const int bufferSize = 4086;
byte[] buffer = new byte[bufferSize];
Response.OnStarting(async() =>
{
try
{
if (!result.Success)
{
Response.ContentLength = result.Message.Length;
Response.ContentType = "text/plain";
Response.StatusCode = 400;
byte[] bytes = Encoding.UTF8.GetBytes(result.Message);
await Response.Body.WriteAsync(bytes, 0, result.Message.Length);
await Response.Body.FlushAsync();
}
else
{
using (FileStream excelStream = result.Model.FileStream)
{
int bytesRead;
Response.ContentLength = excelStream.Length;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
while ((bytesRead = excelStream.Read(buffer, 0, buffer.Length)) > 0 &&
!HttpContext.RequestAborted.IsCancellationRequested)
{
await Response.Body.WriteAsync(buffer, 0, bytesRead);
await Response.Body.FlushAsync();
}
}
}
}
finally
{
if (!string.IsNullOrEmpty(result?.Model?.FilePath) &&
System.IO.File.Exists(result.Model.FilePath))
{
System.IO.File.Delete(result.Model.FilePath);
}
}
});
}
public async Task <OperationDataResult <Stream> > GenerateReportFile(GenerateReportModel model)
{
try
{
var reportDataResult = await GenerateReport(model, true);
if (!reportDataResult.Success)
{
return(new OperationDataResult <Stream>(false, reportDataResult.Message));
}
if (model.Type == "docx")
{
var wordDataResult = await _wordService
.GenerateWordDashboard(reportDataResult.Model);
if (!wordDataResult.Success)
{
return(new OperationDataResult <Stream>(false, wordDataResult.Message));
}
return(new OperationDataResult <Stream>(true, wordDataResult.Model));
}
else
{
var wordDataResult = await _excelService
.GenerateExcelDashboard(reportDataResult.Model);
if (!wordDataResult.Success)
{
return(new OperationDataResult <Stream>(false, wordDataResult.Message));
}
return(new OperationDataResult <Stream>(true, wordDataResult.Model));
}
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <Stream>(
false,
_itemsPlanningLocalizationService.GetString("ErrorWhileGeneratingReportFile")));
}
}
public async Task <OperationDataResult <ReportModel> > GenerateReport(GenerateReportModel model)
{
try
{
var core = await _coreHelper.GetCore();
await using var dbContext = core.DbContextHelper.GetDbContext();
var itemList = await _dbContext.ItemLists.FirstAsync(x => x.Id == model.ItemList);
var item = await _dbContext.Items.FirstAsync(x => x.Id == model.Item);
var locale = await _userService.GetCurrentUserLocale();
Language language = dbContext.Languages.Single(x => x.LanguageCode.ToLower() == locale.ToLower());
var template = await core.ReadeForm(itemList.RelatedEFormId, language);
var casesQuery = _dbContext.ItemCases.Where(x => x.ItemId == item.Id);
if (model.DateFrom != null)
{
casesQuery = casesQuery.Where(x =>
x.CreatedAt >= new DateTime(model.DateFrom.Value.Year, model.DateFrom.Value.Month, model.DateFrom.Value.Day, 0, 0, 0));
}
if (model.DateTo != null)
{
casesQuery = casesQuery.Where(x =>
x.CreatedAt <= new DateTime(model.DateTo.Value.Year, model.DateTo.Value.Month, model.DateTo.Value.Day, 23, 59, 59));
}
var itemCases = await casesQuery.ToListAsync();
var reportModel = await GetReportData(model, item, itemCases, template);
return(new OperationDataResult <ReportModel>(true, reportModel));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <ReportModel>(false,
_itemsPlanningLocalizationService.GetString("ErrorWhileGeneratingReport")));
}
}
private List <CropDetailsList> SearchCropDetails(GenerateReportModel generateReportModel)
{
if (generateReportModel.CropNumber == 0 &&
string.IsNullOrEmpty(generateReportModel.SearchFromDate) &&
string.IsNullOrEmpty(generateReportModel.SearchToDate))
{
return(generateReportModel.CropDetailsList = new List <CropDetailsList>());
}
DateTime searchFromDate = DateTime.Parse(generateReportModel.SearchFromDate);
DateTime searchToDate = DateTime.Parse(generateReportModel.SearchToDate);
List <Crop_Details> cropDetailsData = CropDetailsManager.SearchCropData(
generateReportModel.CropNumber,
searchFromDate,
searchToDate);
return(generateReportModel.CropDetailsList = cropDetailsData.Select(i => new CropDetailsList()
{
CropId = i.CropId,
EstateLeafWeight = i.EstateLeafWeight,
BoughtLeafWeight = i.BoughtLeafWeight,
SupplierName = i.Supplier_Details.SupplierName,
ProductionWeight = i.ProducedWeight,
ProductionStatus = i.InProduction,
requiredTroughMachineCount = i.Machine_Shedule.Count(z => z.Machine_Details.MaxWight == 2500),
requiredRollerMachineCount = i.Machine_Shedule.Count(z => z.Machine_Details.MaxWight == 140),
requiredRollBreakerMachineCount = i.Machine_Shedule.Count(z => z.Machine_Details.MaxWight == 175),
requiredDryerMachineCount = i.Machine_Shedule.Count(z => z.Machine_Details.MaxWight == 300),
requiredSifterMachineCount = i.Machine_Shedule.Count(z => z.Machine_Details.MaxWight == 355),
requiredColorSorterMachineCount = i.Machine_Shedule.Count(z => z.Machine_Details.MaxWight == 200),
Withering = i.Employee_Shedule.Where(x => x.ProcessId == 1).Select(x => x.EmployeeCount).FirstOrDefault(),
Rolling = i.Employee_Shedule.Where(x => x.ProcessId == 2).Select(x => x.EmployeeCount).FirstOrDefault(),
Drying = i.Employee_Shedule.Where(x => x.ProcessId == 5).Select(x => x.EmployeeCount).FirstOrDefault(),
Sifter = i.Employee_Shedule.Where(x => x.ProcessId == 6).Select(x => x.EmployeeCount).FirstOrDefault(),
ColorSorter = i.Employee_Shedule.Where(x => x.ProcessId == 7).Select(x => x.EmployeeCount).FirstOrDefault(),
RollBrakes = i.Employee_Shedule.Where(x => x.ProcessId == 3).Select(x => x.EmployeeCount).FirstOrDefault(),
Packing = i.Employee_Shedule.Where(x => x.ProcessId == 8).Select(x => x.EmployeeCount).FirstOrDefault(),
Fermentation = i.Employee_Shedule.Where(x => x.ProcessId == 4).Select(x => x.EmployeeCount).FirstOrDefault(),
Transportation = i.Employee_Shedule.Where(x => x.ProcessId == 9).Select(x => x.EmployeeCount).FirstOrDefault(),
Spreading = i.Employee_Shedule.Where(x => x.ProcessId == 10).Select(x => x.EmployeeCount).FirstOrDefault()
}).
OrderBy(i => i.EnteredDate).ToList());
}
/// <summary>
/// Generates the report.
/// </summary>
/// <param name="reportType">Type of the report.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="product">The product.</param>
/// <param name="workArea">The work area.</param>
/// <param name="line">The line.</param>
/// <returns>The report view.</returns>
public ActionResult GenerateReport(
string reportType,
DateTime startDate,
DateTime endDate,
Guid?productId,
string workArea,
int?line)
{
var model = new GenerateReportModel();
model.StartDate = startDate;
model.EndDate = endDate;
model.Line = line;
model.ProductId = productId;
model.WorkArea = workArea;
var method = typeof(ReportController).GetMethod(reportType);
var ret = method.Invoke(this, new object[] { model });
return(ret as ActionResult);
}
/// <summary>
/// Filters production stages by the report model.
/// </summary>
/// <param name="list">The list.</param>
/// <param name="model">The model.</param>
/// <returns>Filtered queryable.</returns>
private IQueryable <ProductionStage> FilterStages(IQueryable <ProductionStage> list, GenerateReportModel model)
{
list = list.Where(s => s.TimeStamp > model.StartDate && s.TimeStamp < model.EndDate);
if (model.ProductId.HasValue)
{
list = list.Where(s => s.ProductionEntry.Product.Id == model.ProductId);
}
if (!string.IsNullOrEmpty(model.WorkArea))
{
list = list.Where(s => s.WorkArea == model.WorkArea);
if (model.Line.HasValue)
{
var i = model.Line.Value;
list = list.Where(s => s.LineNumber == i);
}
}
return(list);
}
public static ReportModel GetReportData(
GenerateReportModel model, List <ResourceTimeRegistration> jobsList,
List <SiteDto> sitesList, int timeType)
{
List <ReportEntityModel> reportEntitiesList = new List <ReportEntityModel>();
List <DateTime> reportDates = new List <DateTime>();
List <ReportEntityHeaderModel> reportHeaders = new List <ReportEntityHeaderModel>();
ReportModel finalModel = new ReportModel();
switch (model.Type)
{
case ReportType.Day:
DateTime dateFrom = new DateTime(
model.DateFrom.Year,
model.DateFrom.Month,
model.DateFrom.Day,
0, 0, 0);
DateTime dateTo = new DateTime(
model.DateTo.Year,
model.DateTo.Month,
model.DateTo.Day,
23, 59, 59);
for (DateTime date = dateFrom; date <= dateTo; date = date.AddDays(1))
{
reportDates.Add(date);
}
foreach (DateTime reportDate in reportDates)
{
reportHeaders.Add(new ReportEntityHeaderModel
{
HeaderValue = reportDate.ToString("dd/MM/yyyy")
});
}
switch (model.Relationship)
{
case ReportRelationshipType.EmployeeTotal:
case ReportRelationshipType.Employee:
reportEntitiesList = jobsList.GroupBy(x => x.SDKSiteId)
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key)?.SiteName,
EntityId = x.Key,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt.Day == z.Day &&
j.DoneAt.Month == z.Month &&
j.DoneAt.Year == z.Year)
.Sum(s => (decimal)CorrectedTime(s, timeType)
)
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.OuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.OuterResource?.Name,
EntityId = x.Key.OuterResourceId,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt.Day == z.Day &&
j.DoneAt.Month == z.Month &&
j.DoneAt.Year == z.Year)
.Sum(s => (decimal)CorrectedTime(s, timeType)
)
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.InnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.InnerResource?.Name,
EntityId = x.Key.InnerResourceId,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt.Day == z.Day &&
j.DoneAt.Month == z.Month &&
j.DoneAt.Year == z.Year)
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.EmployeeInnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.SDKSiteId, x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key.SDKSiteId)?.SiteName,
EntityId = x.Key.SDKSiteId,
RelatedEntityId = x.Key.InnerResourceId,
RelatedEntityName = x.Key.InnerResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt.Day == z.Day &&
j.DoneAt.Month == z.Month &&
j.DoneAt.Year == z.Year)
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
case ReportRelationshipType.EmployeeOuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.SDKSiteId, x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key.SDKSiteId)?.SiteName,
EntityId = x.Key.SDKSiteId,
RelatedEntityId = x.Key.OuterResourceId,
RelatedEntityName = x.Key.OuterResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt.Day == z.Day &&
j.DoneAt.Month == z.Month &&
j.DoneAt.Year == z.Year)
.Sum(s => (decimal)TimeSpan.FromSeconds(s.TimeInSeconds).TotalMinutes)
)
.ToList(),
TotalTime = x.Sum(z => (decimal)TimeSpan.FromSeconds(z.TimeInSeconds).TotalMinutes)
}).ToList();
break;
case ReportRelationshipType.OuterInnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.OuterResourceId, x.OuterResource, x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.OuterResource.Name,
EntityId = x.Key.OuterResourceId,
RelatedEntityId = x.Key.InnerResourceId,
RelatedEntityName = x.Key.InnerResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt.Day == z.Day &&
j.DoneAt.Month == z.Month &&
j.DoneAt.Year == z.Year)
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
case ReportRelationshipType.InnerOuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.InnerResourceId, x.InnerResource, x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.InnerResource.Name,
EntityId = x.Key.InnerResourceId,
RelatedEntityId = x.Key.OuterResourceId,
RelatedEntityName = x.Key.OuterResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt.Day == z.Day &&
j.DoneAt.Month == z.Month &&
j.DoneAt.Year == z.Year)
.Sum(s => (decimal)TimeSpan.FromSeconds(s.TimeInSeconds).TotalMinutes)
)
.ToList(),
TotalTime = x.Sum(z => (decimal)TimeSpan.FromSeconds(z.TimeInSeconds).TotalMinutes)
}).ToList();
break;
default:
throw new ArgumentOutOfRangeException();
}
// Employee - Machine
break;
case ReportType.Week:
DateTime firstDayOfWeek = model.DateFrom.FirstDayOfWeek(DayOfWeek.Monday);
DateTime lastDayOfWeek = model.DateTo.LastDayOfWeek(DayOfWeek.Monday);
DateTime dateFromWeek = new DateTime(
firstDayOfWeek.Year,
firstDayOfWeek.Month,
firstDayOfWeek.Day,
0, 0, 0);
DateTime dateToWeek = new DateTime(
lastDayOfWeek.Year,
lastDayOfWeek.Month,
lastDayOfWeek.Day,
23, 59, 59);
for (DateTime date = dateFromWeek; date <= dateToWeek; date = date.AddDays(7))
{
reportDates.Add(date);
}
foreach (DateTime reportDate in reportDates)
{
reportHeaders.Add(new ReportEntityHeaderModel
{
HeaderValue = $"W{DatesHelper.GetIso8601WeekOfYear(reportDate)}-{reportDate:yy}"
});
}
switch (model.Relationship)
{
case ReportRelationshipType.Employee:
case ReportRelationshipType.EmployeeTotal:
reportEntitiesList = jobsList.GroupBy(x => x.SDKSiteId)
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key)?.SiteName,
EntityId = x.Key,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddDays(7))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.OuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.OuterResource?.Name,
EntityId = x.Key.OuterResourceId,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddDays(7))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.InnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.InnerResource?.Name,
EntityId = x.Key.InnerResourceId,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddDays(7))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.EmployeeOuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.SDKSiteId, x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key.SDKSiteId)?.SiteName,
EntityId = x.Key.SDKSiteId,
RelatedEntityId = x.Key.OuterResourceId,
RelatedEntityName = x.Key.OuterResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddDays(7))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
case ReportRelationshipType.EmployeeInnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.SDKSiteId, x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key.SDKSiteId)?.SiteName,
EntityId = x.Key.SDKSiteId,
RelatedEntityId = x.Key.InnerResourceId,
RelatedEntityName = x.Key.InnerResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddDays(7))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
case ReportRelationshipType.OuterInnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.OuterResourceId, x.OuterResource, x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.OuterResource.Name,
EntityId = x.Key.OuterResourceId,
RelatedEntityId = x.Key.InnerResourceId,
RelatedEntityName = x.Key.InnerResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddDays(7))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
case ReportRelationshipType.InnerOuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.InnerResourceId, x.InnerResource, x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.InnerResource.Name,
EntityId = x.Key.InnerResourceId,
RelatedEntityId = x.Key.OuterResourceId,
RelatedEntityName = x.Key.OuterResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddDays(7))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
default:
throw new ArgumentOutOfRangeException();
}
break;
case ReportType.Month:
DateTime firstDayOfMonth = model.DateFrom.FirstDayOfMonth();
DateTime lastDayOfMonth = model.DateTo.LastDayOfMonth();
DateTime dateFromMonth = new DateTime(
firstDayOfMonth.Year,
firstDayOfMonth.Month,
firstDayOfMonth.Day,
0, 0, 0);
DateTime dateToMonth = new DateTime(
lastDayOfMonth.Year,
lastDayOfMonth.Month,
lastDayOfMonth.Day,
23, 59, 59);
// Fill dates array to arrange time by time unit
for (DateTime date = dateFromMonth; date <= dateToMonth; date = date.AddMonths(1))
{
reportDates.Add(date);
}
// Add headers with required format
foreach (DateTime reportDate in reportDates)
{
reportHeaders.Add(new ReportEntityHeaderModel
{
HeaderValue = reportDate.ToString("MM/yyyy")
});
}
switch (model.Relationship)
{
case ReportRelationshipType.Employee:
case ReportRelationshipType.EmployeeTotal:
reportEntitiesList = jobsList.GroupBy(x => x.SDKSiteId)
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key)?.SiteName,
EntityId = x.Key,
// Fill dates array foreach date and calc sum for this date
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddMonths(1))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.OuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.OuterResource?.Name,
EntityId = x.Key.OuterResourceId,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddMonths(1))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.InnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.InnerResource?.Name,
EntityId = x.Key.InnerResourceId,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddMonths(1))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
})
.ToList();
break;
case ReportRelationshipType.EmployeeOuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.SDKSiteId, x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key.SDKSiteId)?.SiteName,
EntityId = x.Key.SDKSiteId,
RelatedEntityId = x.Key.OuterResourceId,
RelatedEntityName = x.Key.OuterResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddMonths(1))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
case ReportRelationshipType.EmployeeInnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.SDKSiteId, x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = sitesList.FirstOrDefault(y => y.SiteId == x.Key.SDKSiteId)?.SiteName,
EntityId = x.Key.SDKSiteId,
RelatedEntityId = x.Key.InnerResourceId,
RelatedEntityName = x.Key.InnerResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddMonths(1))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
case ReportRelationshipType.OuterInnerResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.OuterResourceId, x.OuterResource, x.InnerResourceId, x.InnerResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.OuterResource.Name,
EntityId = x.Key.OuterResourceId,
RelatedEntityId = x.Key.InnerResourceId,
RelatedEntityName = x.Key.InnerResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddMonths(1))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
case ReportRelationshipType.InnerOuterResource:
reportEntitiesList = jobsList.GroupBy(x => new { x.InnerResourceId, x.InnerResource, x.OuterResourceId, x.OuterResource })
.Select(x => new ReportEntityModel()
{
EntityName = x.Key.InnerResource.Name,
EntityId = x.Key.InnerResourceId,
RelatedEntityId = x.Key.OuterResourceId,
RelatedEntityName = x.Key.OuterResource.Name,
TimePerTimeUnit = reportDates.Select(z =>
x
.Where(j => j.DoneAt >= z &&
j.DoneAt < z.AddMonths(1))
.Sum(s => (decimal)CorrectedTime(s, timeType))
)
.ToList(),
TotalTime = x.Sum(z => (decimal)CorrectedTime(z, timeType))
}).ToList();
break;
default:
throw new ArgumentOutOfRangeException();
}
break;
}
if (model.Relationship == ReportRelationshipType.EmployeeOuterResource ||
model.Relationship == ReportRelationshipType.EmployeeInnerResource)
{
// Group reports by employee
List <IGrouping <int, ReportEntityModel> > groupedReports = reportEntitiesList.GroupBy(x => x.EntityId).ToList();
foreach (IGrouping <int, ReportEntityModel> groupedReport in groupedReports)
{
// Calculate sum for sub report
List <decimal> sumByTimeUnit = new List <decimal>();
foreach (ReportEntityModel reportEntity in groupedReport)
{
int i = 0;
foreach (decimal timePerTimeUnit in reportEntity.TimePerTimeUnit)
{
if (sumByTimeUnit.Count <= i)
{
sumByTimeUnit.Add(timePerTimeUnit);
}
else
{
sumByTimeUnit[i] += timePerTimeUnit;
}
i++;
}
}
// Push sub report to reports array
finalModel.SubReports.Add(new SubReportModel()
{
Entities = groupedReport.ToList(),
TotalTime = groupedReport.Sum(x => x.TotalTime),
TotalTimePerTimeUnit = sumByTimeUnit
});
}
}
else
{
// Calculate only one sub report for employee/area/machine
List <decimal> sumByTimeUnit = new List <decimal>();
foreach (ReportEntityModel reportEntity in reportEntitiesList)
{
int i = 0;
foreach (decimal timePerTimeUnit in reportEntity.TimePerTimeUnit)
{
if (sumByTimeUnit.Count <= i)
{
sumByTimeUnit.Add(timePerTimeUnit);
}
else
{
sumByTimeUnit[i] += timePerTimeUnit;
}
i++;
}
}
finalModel.SubReports = new List <SubReportModel>()
{
new SubReportModel()
{
Entities = reportEntitiesList,
TotalTime = reportEntitiesList.Sum(x => x.TotalTime),
TotalTimePerTimeUnit = sumByTimeUnit
}
};
}
finalModel.ReportHeaders = reportHeaders;
finalModel.Relationship = model.Relationship;
return(finalModel);
}
public async Task <OperationDataResult <FileStreamModel> > GenerateReportFile(GenerateReportModel model)
{
string excelFile = null;
try
{
OperationDataResult <ReportModel> reportDataResult = await GenerateReport(model);
if (!reportDataResult.Success)
{
return(new OperationDataResult <FileStreamModel>(false, reportDataResult.Message));
}
string outerResourceName = "";
string innerResourceName = "";
try
{
outerResourceName = _dbContext.PluginConfigurationValues.SingleOrDefault(x => x.Name == "OuterInnerResourceSettings:OuterResourceName")?.Value;
innerResourceName = _dbContext.PluginConfigurationValues.SingleOrDefault(x => x.Name == "OuterInnerResourceSettings:InnerResourceName")?.Value;
}
catch
{
}
switch (reportDataResult.Model.Relationship)
{
case ReportRelationshipType.Employee:
reportDataResult.Model.HumanReadableName =
_outerInnerResourceLocalizationService.GetString("Employee");
break;
case ReportRelationshipType.InnerResource:
reportDataResult.Model.HumanReadableName = innerResourceName;
break;
case ReportRelationshipType.OuterResource:
reportDataResult.Model.HumanReadableName = outerResourceName;
break;
case ReportRelationshipType.EmployeeInnerResource:
reportDataResult.Model.HumanReadableName =
_outerInnerResourceLocalizationService.GetString("Employee") + "-" + innerResourceName;
break;
case ReportRelationshipType.EmployeeOuterResource:
reportDataResult.Model.HumanReadableName =
_outerInnerResourceLocalizationService.GetString("Employee") + "-" + outerResourceName;
break;
case ReportRelationshipType.EmployeeTotal:
reportDataResult.Model.HumanReadableName =
_outerInnerResourceLocalizationService.GetString("Employee" + "-Total");
break;
case ReportRelationshipType.OuterInnerResource:
reportDataResult.Model.HumanReadableName =
$"{outerResourceName} {innerResourceName}";
break;
case ReportRelationshipType.InnerOuterResource:
reportDataResult.Model.HumanReadableName =
$"{innerResourceName} {outerResourceName}";
break;
}
excelFile = _excelService.CopyTemplateForNewAccount("report_template");
bool writeResult = _excelService.WriteRecordsExportModelsToExcelFile(
reportDataResult.Model,
model,
excelFile);
if (!writeResult)
{
throw new Exception($"Error while writing excel file {excelFile}");
}
FileStreamModel result = new FileStreamModel()
{
FilePath = excelFile,
FileStream = new FileStream(excelFile, FileMode.Open),
};
return(new OperationDataResult <FileStreamModel>(true, result));
}
catch (Exception e)
{
if (!string.IsNullOrEmpty(excelFile) && File.Exists(excelFile))
{
File.Delete(excelFile);
}
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <FileStreamModel>(
false,
_outerInnerResourceLocalizationService.GetString("ErrorWhileGeneratingReportFile")));
}
}
public async Task <OperationDataResult <ReportModel> > GenerateReport(GenerateReportModel model)
{
try
{
var reportTimeType = _options.Value.ReportTimeType;
Core core = await _coreHelper.GetCore();
List <SiteDto> sitesList = await core.SiteReadAll(false);
DateTime modelDateFrom = new DateTime(
model.DateFrom.Year,
model.DateFrom.Month,
model.DateFrom.Day,
0, 0, 0);
DateTime modelDateTo = new DateTime(
model.DateTo.Year,
model.DateTo.Month,
model.DateTo.Day,
23, 59, 59);
// results to exclude
string outerResourceName = "";
OuterResource areaToExclude;
InnerResource machineToExclude;
string innerResourceName = "";
List <ResourceTimeRegistration> jobsList;
outerResourceName = _dbContext.PluginConfigurationValues.SingleOrDefault(x => x.Name == "OuterInnerResourceSettings:OuterTotalTimeName")?.Value;
areaToExclude = _dbContext.OuterResources.SingleOrDefaultAsync(x => x.Name == outerResourceName).Result;
innerResourceName = _dbContext.PluginConfigurationValues.SingleOrDefault(x => x.Name == "OuterInnerResourceSettings:InnerTotalTimeName")?.Value;
machineToExclude = await _dbContext.InnerResources.SingleOrDefaultAsync(x => x.Name == innerResourceName);
IQueryable <ResourceTimeRegistration> query = _dbContext.ResourceTimeRegistrations
.Include(x => x.InnerResource)
.Include(x => x.OuterResource);
if (areaToExclude != null && machineToExclude != null)
{
if (model.Relationship == ReportRelationshipType.EmployeeTotal)
{
query = query.Where(x =>
x.OuterResourceId == areaToExclude.Id && x.InnerResourceId == machineToExclude.Id);
}
else
{
query = query.Where(x =>
x.OuterResourceId != areaToExclude.Id && x.InnerResourceId != machineToExclude.Id);
}
}
else
{
query = query
.Where(x => x.DoneAt >= modelDateFrom && x.DoneAt <= modelDateTo);
}
jobsList = await query.ToListAsync();
ReportModel reportModel = ReportsHelper.GetReportData(model, jobsList, sitesList, reportTimeType);
return(new OperationDataResult <ReportModel>(true, reportModel));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <ReportModel>(false,
_outerInnerResourceLocalizationService.GetString("ErrorWhileGeneratingReport")));
}
}
public ActionResult GetReports(GenerateReportModel grm)
{
return(View(grm));
}
private async Task <ReportModel> GetReportData(
GenerateReportModel model,
Item item,
IEnumerable <ItemCase> itemCases,
CoreElement template)
{
var core = await _coreHelper.GetCore();
await using MicrotingDbContext microtingDbContext = core.DbContextHelper.GetDbContext();
var finalModel = new ReportModel
{
Name = item.Name,
Description = item.Description,
DateFrom = model.DateFrom,
DateTo = model.DateTo
};
// Go through template elements and get fields and options labels
foreach (var element in template.ElementList)
{
if (!(element is DataElement dataElement))
{
continue;
}
var dataItems = dataElement.DataItemList;
foreach (var dataItem in dataItems)
{
var reportFieldModel = new ReportFormFieldModel()
{
DataItemId = dataItem.Id,
Label = dataItem.Label
};
switch (dataItem)
{
case MultiSelect multiSelect:
// Add label for each option
reportFieldModel.Options = multiSelect.KeyValuePairList.Select(x => new ReportFormFieldOptionModel()
{
Key = x.Key,
Label = x.Value
}).ToList();
break;
case SingleSelect singleSelect:
case CheckBox checkBox:
case Number number:
case Text text:
// No option label needed for these types
reportFieldModel.Options.Add(new ReportFormFieldOptionModel()
{
Key = string.Empty,
Label = string.Empty
});
break;
default:
continue;
}
finalModel.FormFields.Add(reportFieldModel);
}
}
// Get all answered cases
TimeZoneInfo timeZoneInfo;
try
{
timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Europe/Copenhagen");
}
catch
{
timeZoneInfo = TimeZoneInfo.Local;
}
var casesList = core.CaseReadAll(template.Id, null, null, timeZoneInfo).Result
.Where(c => itemCases.Select(ic => ic.MicrotingSdkCaseId).Contains(c.Id))
.ToList();
// Go through all itemCases
foreach (var ic in itemCases)
{
finalModel.Ids.Add($"{ic.Id} / {ic.MicrotingSdkCaseId}");
finalModel.Dates.Add(ic.CreatedAt);
var @case = casesList.FirstOrDefault(c => c.Id == ic.MicrotingSdkCaseId);
// Fill with empty values, if this itemCase was not replied
if (@case == null)
{
foreach (var fieldModel in finalModel.FormFields)
{
foreach (var optionModel in fieldModel.Options)
{
optionModel.Values.Add("");
}
}
finalModel.DatesDoneAt.Add(null);
finalModel.DoneBy.Add(null);
continue;
}
else
{
finalModel.DoneBy.Add(@case.WorkerName);
finalModel.DatesDoneAt.Add(@case.DoneAt);
}
// Get the reply and work with its ElementList
var locale = await _userService.GetCurrentUserLocale();
Language language = core.DbContextHelper.GetDbContext().Languages.Single(x => x.LanguageCode.ToLower() == locale.ToLower());
foreach (var element in core.CaseRead((int)@case.MicrotingUId, (int)@case.CheckUIid, language).Result.ElementList)
{
if (!(element is CheckListValue checkListValue))
{
continue;
}
// Get the values for each field from the reply
foreach (var fieldModel in finalModel.FormFields)
{
if (!(checkListValue.DataItemList.First(x => x.Id == fieldModel.DataItemId) is Field field))
{
continue;
}
// Fill values for field options
foreach (var optionModel in fieldModel.Options)
{
if (optionModel.Key.IsNullOrEmpty())
{
optionModel.Values.Add(field.FieldValues[0].ValueReadable);
}
else
{
var selectedKeys = field.FieldValues[0].Value.Split('|');
optionModel.Values.Add(
selectedKeys.Contains(optionModel.Key)
? _itemsPlanningLocalizationService.GetString("Yes")
: ""
);
}
}
}
}
}
return(finalModel);
}
public async Task <OperationDataResult <FileStreamModel> > GenerateReportFile(GenerateReportModel model)
{
string excelFile = null;
try
{
OperationDataResult <ReportModel> reportDataResult = await GenerateReport(model);
if (!reportDataResult.Success)
{
return(new OperationDataResult <FileStreamModel>(false, reportDataResult.Message));
}
excelFile = _excelService.CopyTemplateForNewAccount("report_template");
bool writeResult = _excelService.WriteRecordsExportModelsToExcelFile(
reportDataResult.Model,
model,
excelFile);
if (!writeResult)
{
throw new Exception($"Error while writing excel file {excelFile}");
}
FileStreamModel result = new FileStreamModel()
{
FilePath = excelFile,
FileStream = new FileStream(excelFile, FileMode.Open),
};
return(new OperationDataResult <FileStreamModel>(true, result));
}
catch (Exception e)
{
if (!string.IsNullOrEmpty(excelFile) && File.Exists(excelFile))
{
File.Delete(excelFile);
}
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <FileStreamModel>(
false,
_itemsPlanningLocalizationService.GetString("ErrorWhileGeneratingReportFile")));
}
}
public ActionResult DefectCategories(GenerateReportModel model)
{
// linq to get defects by occurrence type
var defectCategories = this.FilterStages(this.ProductionStageService.List(), model)
.Where(s => s.ProductFlaw != null)
.GroupBy(s => s.ProductFlaw)
.OrderByDescending(g => g.Count())
.Select(g => new { Name = g.Key.Reason, Count = g.Count() })
.ToArray();
// get the bar values
var barValues = defectCategories.Select(d => d.Count);
// get the line values
var linesValues = new List <int>();
foreach (var num in barValues)
{
var next = linesValues.LastOrDefault() + num;
linesValues.Add(next);
}
// build the chart
Highcharts chart = new Highcharts("chart")
.InitChart(new Chart {
ZoomType = ZoomTypes.Xy
})
.SetTitle(new Title {
Text = "Defect Categories"
})
.SetSubtitle(new Subtitle {
Text = ""
})
.SetXAxis(new XAxis {
Categories = defectCategories.Select(d => d.Name).ToArray()
})
.SetYAxis(new[]
{
new YAxis
{
Labels = new YAxisLabels
{
Formatter = "function() { return this.value; }",
Style = "color: '#89A54E'"
},
Title = new XAxisTitle
{
Text = "Count",
Style = "color: '#89A54E'"
}
},
new YAxis
{
Labels = new YAxisLabels
{
Formatter = "function() { return this.value; }",
Style = "color: '#4572A7'"
},
Title = new XAxisTitle
{
Text = "Defect Categories",
Style = "color: '#4572A7'"
},
Opposite = true
}
})
.SetTooltip(new Tooltip
{
Formatter = "function() { return ''+ this.x +': '+ this.y; }"
})
.SetLegend(new Legend
{
Layout = Layouts.Vertical,
Align = HorizontalAligns.Left,
X = 120,
VerticalAlign = VerticalAligns.Top,
Y = 100,
Floating = true,
BackgroundColor = ColorTranslator.FromHtml("#FFFFFF")
})
.SetSeries(new[]
{
new Series
{
Name = "Count",
Color = ColorTranslator.FromHtml("#4572A7"),
Type = ChartTypes.Column,
YAxis = 0,
Data = new Data(barValues.Cast <object>().ToArray())
},
new Series
{
Name = "Defect Categories",
Color = ColorTranslator.FromHtml("#89A54E"),
Type = ChartTypes.Spline,
YAxis = 0,
Data = new Data(linesValues.Cast <object>().ToArray())
}
});
return(PartialView("DefectCategories", chart));
}
public async Task <OperationDataResult <List <ReportEformModel> > > GenerateReport([FromBody] GenerateReportModel requestModel)
{
return(await _reportService.GenerateReport(requestModel, false));
}
public async Task <OperationDataResult <List <ReportEformModel> > > GenerateReport(GenerateReportModel model, bool isDocx)
{
try
{
var timeZoneInfo = await _userService.GetCurrentUserTimeZoneInfo();
var core = await _coreHelper.GetCore();
await using var sdkDbContext = core.DbContextHelper.GetDbContext();
//var casesQuery = microtingDbContext.cases
// .Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
// .Include(x => x.Site)
// .AsQueryable();
var fromDate = new DateTime(model.DateFrom.Value.Year, model.DateFrom.Value.Month,
model.DateFrom.Value.Day, 0, 0, 0);
var toDate = new DateTime(model.DateTo.Value.Year, model.DateTo.Value.Month,
model.DateTo.Value.Day, 23, 59, 59);
var planningCasesQuery = _dbContext.PlanningCases
.Include(x => x.Planning)
.ThenInclude(x => x.PlanningsTags)
.Where(x => x.Status == 100)
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.AsNoTracking()
.AsQueryable();
if (model.DateFrom != null)
{
planningCasesQuery = planningCasesQuery.Where(x =>
x.MicrotingSdkCaseDoneAt >= fromDate);
}
if (model.DateTo != null)
{
planningCasesQuery = planningCasesQuery.Where(x =>
x.MicrotingSdkCaseDoneAt <= toDate);
}
if (model.TagIds.Count > 0)
{
foreach (var tagId in model.TagIds)
{
planningCasesQuery = planningCasesQuery.Where(x =>
x.Planning.PlanningsTags.Any(y => y.PlanningTagId == tagId && y.WorkflowState != Constants.WorkflowStates.Removed))
.AsNoTracking();
}
}
var groupedCaseCheckListIds = planningCasesQuery.GroupBy(x => x.MicrotingSdkeFormId)
.Select(x => x.Key)
.ToList();
List <CheckList> checkLists = new List <CheckList>();
if (groupedCaseCheckListIds.Count > 0)
{
checkLists = await sdkDbContext.CheckLists
.FromSqlRaw("SELECT * FROM CheckLists WHERE" +
$" Id IN ({string.Join(",", groupedCaseCheckListIds)})" +
" ORDER BY ReportH1, ReportH2, ReportH3, ReportH4").AsNoTracking().ToListAsync();
}
var itemCases = await planningCasesQuery
.OrderBy(x => x.Planning.RelatedEFormName)
.ToListAsync();
var groupedCases = itemCases
.GroupBy(x => x.MicrotingSdkeFormId)
.Select(x => new
{
templateId = x.Key,
cases = x.ToList(),
})
.ToList();
var result = new List <ReportEformModel>();
// Exclude field types: None, Picture, Audio, Movie, Signature, Show PDF, FieldGroup, SaveButton
var excludedFieldTypes = new List <string>()
{
Constants.FieldTypes.None,
Constants.FieldTypes.Picture,
Constants.FieldTypes.Audio,
Constants.FieldTypes.Movie,
Constants.FieldTypes.Signature,
Constants.FieldTypes.ShowPdf,
Constants.FieldTypes.FieldGroup,
Constants.FieldTypes.SaveButton
};
var localeString = await _userService.GetCurrentUserLocale();
var language = sdkDbContext.Languages.Single(x => x.LanguageCode == localeString);
//foreach (var groupedCase in groupedCases)
foreach (var checkList in checkLists)
{
bool hasChildCheckLists = sdkDbContext.CheckLists.Any(x => x.ParentId == checkList.Id);
var checkListTranslation = sdkDbContext.CheckListTranslations
.Where(x => x.CheckListId == checkList.Id)
.First(x => x.LanguageId == language.Id).Text;
//var template = await sdkDbContext.CheckLists.SingleAsync(x => x.Id == groupedCase.templateId);
var groupedCase = groupedCases.SingleOrDefault(x => x.templateId == checkList.Id);
if (groupedCase != null)
{
var reportModel = new ReportEformModel
{
TemplateName = checkListTranslation,
FromDate = $"{fromDate:yyyy-MM-dd}",
ToDate = $"{toDate:yyyy-MM-dd}",
TextHeaders = new ReportEformTextHeaderModel(),
TableName = checkListTranslation,
};
// first pass
if (result.Count <= 0)
{
if (checkList.ReportH1 != null)
{
reportModel.TextHeaders.Header1 = checkList.ReportH1;
}
else
{
reportModel.TextHeaders.Header1 = checkListTranslation;
// reportModel.TableName = null;
// reportModel.TemplateName = null;
}
reportModel.TextHeaders.Header2 = checkList.ReportH2;
reportModel.TextHeaders.Header3 = checkList.ReportH3;
reportModel.TextHeaders.Header4 = checkList.ReportH4;
reportModel.TextHeaders.Header5 = checkList.ReportH5;
}
else // other pass
{
var header1 = result.LastOrDefault(x => x.TextHeaders.Header1 != null)?.TextHeaders.Header1;
var header2 = result.LastOrDefault(x => x.TextHeaders.Header2 != null)?.TextHeaders.Header2;
var header3 = result.LastOrDefault(x => x.TextHeaders.Header3 != null)?.TextHeaders.Header3;
var header4 = result.LastOrDefault(x => x.TextHeaders.Header4 != null)?.TextHeaders.Header4;
var header5 = result.LastOrDefault(x => x.TextHeaders.Header5 != null)?.TextHeaders.Header5;
// if not find or finded and templateHeader not equal
if (header1 == null || checkList.ReportH1 != header1)
{
reportModel.TextHeaders.Header1 = checkList.ReportH1 ?? "";
}
if (header2 == null || checkList.ReportH2 != header2)
{
reportModel.TextHeaders.Header2 = checkList.ReportH2 ?? "";
}
if (header3 == null || checkList.ReportH3 != header3)
{
reportModel.TextHeaders.Header3 = checkList.ReportH3 ?? "";
}
if (header4 == null || checkList.ReportH4 != header4)
{
reportModel.TextHeaders.Header4 = checkList.ReportH4 ?? "";
}
if (header5 == null || checkList.ReportH5 != header5)
{
reportModel.TextHeaders.Header5 = checkList.ReportH5 ?? "";
}
}
var fields = await core.Advanced_TemplateFieldReadAll(
checkList.Id, language);
foreach (var fieldDto in fields)
{
if (fieldDto.FieldType == Constants.FieldTypes.None)
{
var fieldTranslation =
await sdkDbContext.FieldTranslations.FirstAsync(x =>
x.FieldId == fieldDto.Id && x.LanguageId == language.Id);
reportModel.DescriptionBlocks.Add(fieldTranslation.Description);
}
if (!excludedFieldTypes.Contains(fieldDto.FieldType))
{
var fieldTranslation =
await sdkDbContext.FieldTranslations.FirstAsync(x =>
x.FieldId == fieldDto.Id && x.LanguageId == language.Id);
string text = fieldTranslation.Text;
if (hasChildCheckLists)
{
var clTranslation =
await sdkDbContext.CheckListTranslations.FirstOrDefaultAsync(x =>
x.CheckListId == fieldDto.CheckListId && x.LanguageId == language.Id);
if (checkListTranslation != clTranslation.Text)
{
text = $"{clTranslation.Text} - {text}";
}
}
var kvp = new KeyValuePair <int, string>(fieldDto.Id, text);
reportModel.ItemHeaders.Add(kvp);
}
}
// images
var templateCaseIds = groupedCase.cases.Select(x => (int?)x.MicrotingSdkCaseId).ToArray();
var imagesForEform = await sdkDbContext.FieldValues
.Include(x => x.UploadedData)
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => x.UploadedData.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => x.Field.FieldTypeId == 5)
.Where(x => templateCaseIds.Contains(x.CaseId))
.Where(x => x.UploadedDataId != null)
.OrderBy(x => x.CaseId)
.ToListAsync();
foreach (var imageField in imagesForEform)
{
var planningCase = groupedCase.cases.Single(x => x.MicrotingSdkCaseId == imageField.CaseId && x.PlanningId != 0);
var planningNameTranslation =
await _dbContext.PlanningNameTranslation.SingleOrDefaultAsync(x =>
x.PlanningId == planningCase.PlanningId && x.LanguageId == language.Id);
if (planningNameTranslation != null)
{
var label = $"{imageField.CaseId}; {planningNameTranslation.Name}";
var geoTag = "";
if (!string.IsNullOrEmpty(imageField.Latitude))
{
geoTag =
$"https://www.google.com/maps/place/{imageField.Latitude},{imageField.Longitude}";
}
var keyList = new List <string> {
imageField.CaseId.ToString(), label
};
var list = new List <string>();
if (!string.IsNullOrEmpty(imageField.UploadedData.FileName))
{
list.Add(isDocx
? $"{imageField.UploadedData.Id}_700_{imageField.UploadedData.Checksum}{imageField.UploadedData.Extension}"
: imageField.UploadedData.FileName);
list.Add(geoTag);
reportModel.ImageNames.Add(
new KeyValuePair <List <string>, List <string> >(keyList, list));
}
}
}
// posts
var casePostRequest = new CasePostsRequestCommonModel
{
Offset = 0,
PageSize = int.MaxValue,
TemplateId = checkList.Id,
};
var casePostListResult = await _casePostBaseService.GetCommonPosts(casePostRequest);
if (!casePostListResult.Success)
{
return(new OperationDataResult <List <ReportEformModel> >(
false,
casePostListResult.Message));
}
foreach (var casePostCommonModel in casePostListResult.Model.Entities)
{
reportModel.Posts.Add(new ReportEformPostModel
{
CaseId = casePostCommonModel.CaseId,
PostId = casePostCommonModel.PostId,
Comment = casePostCommonModel.Text,
SentTo = casePostCommonModel.ToRecipients,
SentToTags = casePostCommonModel.ToRecipientsTags,
PostDate = casePostCommonModel.PostDate
});
}
// add cases
foreach (var planningCase in groupedCase.cases.OrderBy(x => x.MicrotingSdkCaseDoneAt).ToList())
{
var planningNameTranslation =
await _dbContext.PlanningNameTranslation.SingleOrDefaultAsync(x =>
x.PlanningId == planningCase.PlanningId && x.LanguageId == language.Id);
if (planningNameTranslation != null)
{
var item = new ReportEformItemModel
{
Id = planningCase.Id,
MicrotingSdkCaseId = planningCase.MicrotingSdkCaseId,
MicrotingSdkCaseDoneAt = TimeZoneInfo.ConvertTimeFromUtc((DateTime)planningCase.MicrotingSdkCaseDoneAt, timeZoneInfo),
eFormId = planningCase.MicrotingSdkeFormId,
DoneBy = planningCase.DoneByUserName,
ItemName = planningNameTranslation.Name,
ItemDescription = planningCase.Planning.Description,
};
var caseFields = await core.Advanced_FieldValueReadList(
new List <int>()
{
planningCase.MicrotingSdkCaseId
}, language);
foreach (var caseField in reportModel.ItemHeaders.Select(itemHeader => caseFields
.FirstOrDefault(x => x.FieldId == itemHeader.Key)).Where(caseField => caseField != null))
{
switch (caseField.FieldType)
{
case Constants.FieldTypes.MultiSelect:
item.CaseFields.Add(caseField.ValueReadable.Replace("|", "<br>"));
break;
case Constants.FieldTypes.EntitySearch or
Constants.FieldTypes.EntitySelect or
Constants.FieldTypes.SingleSelect:
item.CaseFields.Add(caseField.ValueReadable);
break;
default:
item.CaseFields.Add(caseField.Value);
break;
}
}
item.ImagesCount = await sdkDbContext.FieldValues
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => x.Field.FieldTypeId == 5)
.Where(x => x.CaseId == planningCase.MicrotingSdkCaseId)
.Where(x => x.UploadedDataId != null)
.Select(x => x.Id)
.CountAsync();
item.PostsCount = casePostListResult.Model.Entities
.Where(x => x.CaseId == planningCase.MicrotingSdkCaseId)
.Select(x => x.PostId)
.Count();
reportModel.Items.Add(item);
}
}
result.Add(reportModel);
}
}
var reportEformModel = new ReportEformModel();
reportEformModel.NameTagsInEndPage.AddRange(_dbContext.PlanningTags
.Where(x => x.WorkflowState != Constants.WorkflowStates.Removed)
.Where(x => model.TagIds.Any(y => y == x.Id))
.Select(x => x.Name));
result.Add(reportEformModel);
if (result.Any())
{
return(new OperationDataResult <List <ReportEformModel> >(true, result));
}
return(new OperationDataResult <List <ReportEformModel> >(false, _itemsPlanningLocalizationService.GetString("NoDataInSelectedPeriod")));
}
catch (Exception e)
{
Trace.TraceError(e.Message);
_logger.LogError(e.Message);
return(new OperationDataResult <List <ReportEformModel> >(false,
_itemsPlanningLocalizationService.GetString("ErrorWhileGeneratingReport")));
}
}
public bool WriteRecordsExportModelsToExcelFile(ReportModel reportModel, GenerateReportModel generateReportModel, string destFile)
{
var workbook = new XLWorkbook(destFile);
var worksheet = workbook.Worksheets.First();
// Fill base info
var nameTitle = _itemsPlanningLocalizationService.GetString("Name");
worksheet.Cell(2, 2).Value = nameTitle;
worksheet.Cell(2, 3).Value = reportModel.Name;
var descriptionTitle = _itemsPlanningLocalizationService.GetString("Description");
worksheet.Cell(3, 2).Value = descriptionTitle;
worksheet.Cell(3, 3).Value = reportModel.Description;
var periodFromTitle = _itemsPlanningLocalizationService.GetString("DateFrom");
worksheet.Cell(5, 2).Value = periodFromTitle;
worksheet.Cell(5, 3).Value = reportModel.DateFrom?.ToString("MM/dd/yyyy HH:mm");
var periodToTitle = _itemsPlanningLocalizationService.GetString("DateTo");
worksheet.Cell(6, 2).Value = periodToTitle;
worksheet.Cell(6, 3).Value = reportModel.DateTo?.ToString("MM/dd/yyyy HH:mm");
var col = 4;
var row = 8;
// Fill dates headers
var idName = _itemsPlanningLocalizationService.GetString("Id");
worksheet.Cell(8, 3).Value = idName;
foreach (var id in reportModel.Ids)
{
worksheet.Cell(row, col).Value = id;
worksheet.Cell(row, col).Style.Font.Bold = true;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// TODO fix worksheet.Cell(row, col).AutoFitColumns();
col++;
}
row += 1;
col = 4;
var deployedAt = _itemsPlanningLocalizationService.GetString("Deployed at");
worksheet.Cell(9, 3).Value = deployedAt;
foreach (var date in reportModel.Dates)
{
worksheet.Cell(row, col).Value = date?.ToString("MM/dd/yyyy");
worksheet.Cell(row, col).Style.Font.Bold = true;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// TODO fix worksheet.Cell(row, col).AutoFitColumns();
col++;
}
row += 1;
col = 4;
var doneAt = _itemsPlanningLocalizationService.GetString("Date of doing");
worksheet.Cell(10, 3).Value = doneAt;
foreach (var date in reportModel.DatesDoneAt)
{
worksheet.Cell(row, col).Value = date?.ToString("MM/dd/yyyy");
worksheet.Cell(row, col).Style.Font.Bold = true;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// TODO fix worksheet.Cell(row, col).AutoFitColumns();
col++;
}
row += 1;
col = 4;
var doneBy = _itemsPlanningLocalizationService.GetString("Done by");
worksheet.Cell(11, 3).Value = doneBy;
foreach (var name in reportModel.DoneBy)
{
worksheet.Cell(row, col).Value = name;
worksheet.Cell(row, col).Style.Font.Bold = true;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// TODO fix worksheet.Cell(row, col).AutoFitColumns();
col++;
}
col = 4;
worksheet.Range(row, 2, row, col - 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
row++;
// Fill form fields and options with data
foreach (var field in reportModel.FormFields)
{
worksheet.Cell(row, 2).Value = field.Label;
//worksheet.Cells(row, 2, row + field.Options.Count - 1, 2);
var fRow = row;
foreach (var option in field.Options)
{
col = 4;
foreach (var value in option.Values)
{
if (value?.GetType() == typeof(decimal))
{
worksheet.Cell(row, col).Style.NumberFormat.Format = "0.00";
}
worksheet.Cell(row, col).Value = value;
worksheet.Cell(row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
col++;
}
worksheet.Cell(row, 3).Value = option.Label;
worksheet.Cell(row, 3).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
row++;
}
worksheet.Range(fRow, 2, row - 1, col - 1).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
workbook.Save();
return(true);
}
public JsonResult GenerateReort(GenerateReportModel model)
{
var response = new ResponseModel()
{
IsSuccess = false
};
try
{
log.Debug(model);
string errorMessage = "Model is not valid: ";
if (!ModelState.IsValid)
{
foreach (ModelState modelState in ViewData.ModelState.Values)
{
foreach (ModelError error in modelState.Errors)
{
errorMessage = errorMessage + "; " + error.ErrorMessage;
}
}
if (Request.Files.Count == 0)
{
errorMessage = errorMessage + "; " + "File not selected";
}
response.Message = errorMessage;
return(Json(response));
}
if (Request.Files.Count > 0)
{
var file = Request.Files[0];
if (file != null && file.ContentLength > 0)
{
var fileName = Path.GetFileName(file.FileName);
var inputFilePath = Path.Combine(Server.MapPath("~/Uploads/"), fileName);
file.SaveAs(inputFilePath);
//R Process
//REngine.SetEnvironmentVariables();
//RHelper.InitREngine();
//REngine engine = RHelper.engine;
//REngine engine = REngine.GetInstance();
//using (REngine engine = REngine.GetInstance())
//{
var rScriptFile = Path.Combine(Server.MapPath("~/Commands/"), string.Format("model{0}.R", model.ModelId));
if (!System.IO.File.Exists(rScriptFile))
{
log.Error(string.Format("Script doesnt exists: {0}", rScriptFile));
response.Message = string.Format("Script doesnt exists: {0}", rScriptFile);
return(Json(response));
}
rScriptFile = rScriptFile.Replace("\\", "/");
log.Debug(rScriptFile);
var outputFile = getOutputFile(); //Path.Combine(Server.MapPath("~/Output/"), "Generated.csv");
string[] input = new string[6];
input[0] = model.Server;
input[1] = model.Database;
input[2] = model.Username;
input[3] = model.Password;
input[4] = inputFilePath;
input[5] = outputFile;
log.Debug(input);
engine.SetCommandLineArguments(input);
string expression = string.Format("source('{0}')", rScriptFile);
engine.Evaluate(expression);
response.IsSuccess = true;
// engine.Evaluate("source('C:/01_Dev/POC/RScript/RScript/Reference/model1.R')");
//Rscript D:\Dev\POC\RScript\RScript\Reference\cmm.R LPT-002384\SQLEXPRESS AdventureWorks2016CTP3 sa Soders@123 D:\Dev\POC\RScript\RScript\Reference\cc.csv
//"C:\Program Files\R\R-3.4.0\bin\i386\Rscript" C:\01_Dev\POC\RScript\RScript\Commands\model1.R SOBS-DELL-3470\MSSQL2016 AdventureWorks2016CTP3 rscript Rscript@123 C:\01_Dev\POC\RScript\RScript\Uploads\cc.csv
//"C:\Program Files\R\R-3.4.0\bin\i386\Rscript" C:\01_Dev\POC\RScript\RScript\Commands\model1.R SOBS-DELL-3470\MSSQL2016 AdventureWorks2016CTP3 rscript Rscript@123 C:\01_Dev\POC\RScript\RScript\Uploads\cc.csv C:\01_Dev\POC\RScript\RScript\Output\Generated.csv
//engine.Dispose();
//}
}
}
else
{
errorMessage = errorMessage + "; " + "File not selected";
response.Message = errorMessage;
}
}
catch (Exception ex)
{
log.Error(ex);
response.Message = ex.Message;
}
return(Json(response));
}
public async Task <OperationDataResult <ReportModel> > GenerateReport(GenerateReportModel requestModel)
{
return(await _reportService.GenerateReport(requestModel));
}
public bool WriteRecordsExportModelsToExcelFile(ReportModel reportModel, GenerateReportModel generateReportModel, string destFile)
{
FileInfo file = new FileInfo(destFile);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[ExcelConsts.MachineAreaReportSheetNumber];
// Fill base info
string periodFromTitle = _outerInnerResourceLocalizationService.GetString("DateFrom");
worksheet.Cells[ExcelConsts.EmployeeReport.PeriodFromTitleRow, ExcelConsts.EmployeeReport.PeriodFromTitleCol].Value = periodFromTitle;
worksheet.Cells[ExcelConsts.EmployeeReport.PeriodFromRow, ExcelConsts.EmployeeReport.PeriodFromCol].Value = generateReportModel.DateFrom;
string periodToTitle = _outerInnerResourceLocalizationService.GetString("DateTo");
worksheet.Cells[ExcelConsts.EmployeeReport.PeriodToTitleRow, ExcelConsts.EmployeeReport.PeriodToTitleCol].Value = periodToTitle;
worksheet.Cells[ExcelConsts.EmployeeReport.PeriodToRow, ExcelConsts.EmployeeReport.PeriodToCol].Value = generateReportModel.DateTo;
string showDataByTitle = _outerInnerResourceLocalizationService.GetString("ShowDataBy");
worksheet.Cells[ExcelConsts.EmployeeReport.PeriodTypeTitleRow, ExcelConsts.EmployeeReport.PeriodTypeTitleCol].Value = showDataByTitle;
string showDataByValue = _outerInnerResourceLocalizationService.GetString(generateReportModel.Type.ToString());
worksheet.Cells[ExcelConsts.EmployeeReport.PeriodTypeRow, ExcelConsts.EmployeeReport.PeriodTypeCol].Value = showDataByValue;
string reportTitle = _outerInnerResourceLocalizationService.GetString("Report");
worksheet.Cells[ExcelConsts.EmployeeReport.ReportTitleRow, ExcelConsts.EmployeeReport.ReportTitleCol].Value = reportTitle;
string reportName = _outerInnerResourceLocalizationService.GetString(reportModel.HumanReadableName);
worksheet.Cells[ExcelConsts.EmployeeReport.ReportNameRow, ExcelConsts.EmployeeReport.ReportNameCol].Value = reportName;
// Debugger.Break();
int entityPosition = 0;
foreach (SubReportModel subReport in reportModel.SubReports)
{
// entity names
for (int i = 0; i < subReport.Entities.Count; i++)
{
int rowIndex = ExcelConsts.EmployeeReport.EntityNameStartRow + i + entityPosition;
ReportEntityModel reportEntity = subReport.Entities[i];
worksheet.UpdateValue(rowIndex, ExcelConsts.EmployeeReport.EntityNameStartCol, reportEntity?.EntityName, true);
}
// related entity names
for (int i = 0; i < subReport.Entities.Count; i++)
{
int rowIndex = ExcelConsts.EmployeeReport.RelatedEntityNameStartRow + i + entityPosition;
ReportEntityModel reportEntity = subReport.Entities[i];
worksheet.UpdateValue(rowIndex, ExcelConsts.EmployeeReport.RelatedEntityNameStartCol,
reportEntity?.RelatedEntityName, true);
}
// headers
for (int i = 0; i < reportModel.ReportHeaders.Count; i++)
{
ReportEntityHeaderModel reportHeader = reportModel.ReportHeaders[i];
int colIndex = ExcelConsts.EmployeeReport.HeaderStartCol + i;
int rowIndex = ExcelConsts.EmployeeReport.HeaderStartRow + entityPosition;
worksheet.UpdateValue(rowIndex, colIndex, reportHeader?.HeaderValue, true, true, Color.Wheat);
}
// vertical sum
for (int i = 0; i < subReport.Entities.Count; i++)
{
int rowIndex = ExcelConsts.EmployeeReport.VerticalSumStartRow + i + entityPosition;
ReportEntityModel reportEntity = subReport.Entities[i];
worksheet.UpdateValue(rowIndex, ExcelConsts.EmployeeReport.VerticalSumStartCol, reportEntity?.TotalTime, true, "0");
}
// vertical sum title
worksheet.UpdateValue(ExcelConsts.EmployeeReport.VerticalSumTitleRow + entityPosition, ExcelConsts.EmployeeReport.VerticalSumTitleCol, "Sum", true, true);
// data
for (int i = 0; i < subReport.Entities.Count; i++)
{
ReportEntityModel reportEntity = subReport.Entities[i];
int rowIndex = ExcelConsts.EmployeeReport.DataStartRow + i + entityPosition;
for (int y = 0; y < reportEntity.TimePerTimeUnit.Count; y++)
{
decimal time = reportEntity.TimePerTimeUnit[y];
int colIndex = ExcelConsts.EmployeeReport.DataStartCol + y;
worksheet.UpdateValue(rowIndex, colIndex, time, true, "0");
}
}
// horizontal sum
int horizontalSumRowIndex = ExcelConsts.EmployeeReport.DataStartRow + subReport.Entities.Count + entityPosition;
for (int i = 0; i < subReport.TotalTimePerTimeUnit.Count; i++)
{
decimal time = subReport.TotalTimePerTimeUnit[i];
int colIndex = ExcelConsts.EmployeeReport.HorizontalSumStartCol + i;
worksheet.UpdateValue(horizontalSumRowIndex, colIndex, time, true, "0");
}
// Report sum
int totalSumRowIndex = ExcelConsts.EmployeeReport.DataStartRow + subReport.Entities.Count + entityPosition;
decimal totalSum = subReport.TotalTime;
worksheet.UpdateValue(totalSumRowIndex, ExcelConsts.EmployeeReport.TotalSumCol, totalSum, true);
worksheet.UpdateValue(totalSumRowIndex, ExcelConsts.EmployeeReport.TotalSumTitleCol, "Sum", true);
entityPosition += subReport.Entities.Count + 3;
}
package.Save(); //Save the workbook.
}
return(true);
}
public async Task EmployeesReportByMonth_Generate_DoesGenerate()
{
OuterResource newArea = new OuterResource()
{
Name = "My OuterResource 1", Version = 1
};
OuterResource newArea1 = new OuterResource()
{
Name = "My OuterResource 2", Version = 1
};
OuterResource newArea2 = new OuterResource()
{
Name = "My OuterResource 3", Version = 1
};
InnerResource newMachine = new InnerResource()
{
Name = "My InnerResource 1", Version = 1
};
InnerResource newMachine1 = new InnerResource()
{
Name = "My InnerResource 2", Version = 1
};
InnerResource newMachine2 = new InnerResource()
{
Name = "My InnerResource 3", Version = 1
};
await newArea.Create(DbContext);
await newArea1.Create(DbContext);
await newArea2.Create(DbContext);
await newMachine.Create(DbContext);
await newMachine1.Create(DbContext);
await newMachine2.Create(DbContext);
// Different Month
ResourceTimeRegistration newTimeRegistrationMonth = new ResourceTimeRegistration()
{
OuterResourceId = newArea.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 06, 13)
};
ResourceTimeRegistration newTimeRegistrationMonth1 = new ResourceTimeRegistration()
{
OuterResourceId = newArea1.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 07, 14)
};
ResourceTimeRegistration newTimeRegistrationMonth2 = new ResourceTimeRegistration()
{
OuterResourceId = newArea2.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine2.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 07, 15)
};
ResourceTimeRegistration newTimeRegistrationMonth3 = new ResourceTimeRegistration()
{
OuterResourceId = newArea.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 08, 13)
};
ResourceTimeRegistration newTimeRegistrationMonth4 = new ResourceTimeRegistration()
{
OuterResourceId = newArea1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 08, 14)
};
ResourceTimeRegistration newTimeRegistrationMonth5 = new ResourceTimeRegistration()
{
OuterResourceId = newArea1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine2.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 09, 15)
};
ResourceTimeRegistration newTimeRegistrationMonth6 = new ResourceTimeRegistration()
{
OuterResourceId = newArea1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 09, 15)
};
await DbContext.ResourceTimeRegistrations.AddRangeAsync(newTimeRegistrationMonth, newTimeRegistrationMonth1,
newTimeRegistrationMonth2, newTimeRegistrationMonth3, newTimeRegistrationMonth4, newTimeRegistrationMonth5,
newTimeRegistrationMonth6);
await DbContext.SaveChangesAsync();
GenerateReportModel model = new GenerateReportModel()
{
DateFrom = new DateTime(2019, 5, 13),
DateTo = new DateTime(2019, 9, 13),
Relationship = ReportRelationshipType.Employee,
Type = ReportType.Month
};
List <SiteDto> sitesList = new List <SiteDto>()
{
// new SiteDto(1, "Test Site 1", "", "", 1, 1, 1, 1),
new SiteDto()
{
CustomerNo = 1,
Email = "bla",
FirstName = "Test",
LastName = "Site 1",
OtpCode = 1,
SiteId = 1,
SiteName = "Test Site 1",
UnitId = 1,
WorkerUid = 1
},
// new SiteDto(2, "Test Site 2", "", "", 1, 1, 1, 1)
new SiteDto()
{
CustomerNo = 1,
Email = "bla",
FirstName = "Test",
LastName = "Site 2",
OtpCode = 1,
SiteId = 2,
SiteName = "Test Site 2",
UnitId = 1,
WorkerUid = 1
}
};
DateTime modelDateFrom = new DateTime(
model.DateFrom.Year,
model.DateFrom.Month,
model.DateFrom.Day,
0, 0, 0);
DateTime modelDateTo = new DateTime(
model.DateTo.Year,
model.DateTo.Month,
model.DateTo.Day,
23, 59, 59);
List <ResourceTimeRegistration> jobsList = await DbContext.ResourceTimeRegistrations
.Include(x => x.InnerResource)
.Include(x => x.OuterResource)
.Where(x => x.DoneAt >= modelDateFrom && x.DoneAt <= modelDateTo)
.ToListAsync();
ReportModel reportModel = ReportsHelper.GetReportData(model, jobsList, sitesList, (int)ReportTimeType.Minutes);
Assert.AreEqual(reportModel.SubReports.Count, 1);
Assert.AreEqual(reportModel.SubReports[0].TotalTime, 3000);
Assert.AreEqual(reportModel.SubReports[0].Entities.Count, sitesList.Count);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].EntityName, sitesList[0].SiteName);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].EntityName, sitesList[1].SiteName);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TotalTime, 1800);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].TotalTime, 1200);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[0], 0);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[1], 600);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[2], 1200);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[3], 0);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[4], 0);
}
public async Task OuterResourcesReportByWeek_Generate_DoesGenerate()
{
OuterResource newOuterResource = new OuterResource()
{
Name = "My OuterResource 1", Version = 1
};
OuterResource newOuterResource1 = new OuterResource()
{
Name = "My OuterResource 2", Version = 1
};
OuterResource newOuterResource2 = new OuterResource()
{
Name = "My OuterResource 3", Version = 1
};
InnerResource newMachine = new InnerResource()
{
Name = "My InnerResource 1", Version = 1
};
InnerResource newMachine1 = new InnerResource()
{
Name = "My InnerResource 2", Version = 1
};
InnerResource newMachine2 = new InnerResource()
{
Name = "My InnerResource 3", Version = 1
};
await newOuterResource.Create(DbContext);
await newOuterResource1.Create(DbContext);
await newOuterResource2.Create(DbContext);
await newMachine.Create(DbContext);
await newMachine1.Create(DbContext);
await newMachine2.Create(DbContext);
// Different Weeks
ResourceTimeRegistration newTimeRegistrationWeek = new ResourceTimeRegistration()
{
OuterResourceId = newOuterResource.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 20)
};
ResourceTimeRegistration newTimeRegistrationWeek1 = new ResourceTimeRegistration()
{
OuterResourceId = newOuterResource1.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 20)
};
ResourceTimeRegistration newTimeRegistrationWeek2 = new ResourceTimeRegistration()
{
OuterResourceId = newOuterResource2.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine2.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 27)
};
ResourceTimeRegistration newTimeRegistrationWeek3 = new ResourceTimeRegistration()
{
OuterResourceId = newOuterResource.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 06, 27)
};
ResourceTimeRegistration newTimeRegistrationWeek4 = new ResourceTimeRegistration()
{
OuterResourceId = newOuterResource1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 06, 05)
};
ResourceTimeRegistration newTimeRegistrationWeek5 = new ResourceTimeRegistration()
{
OuterResourceId = newOuterResource1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine2.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 06, 05)
};
ResourceTimeRegistration newTimeRegistrationWeek6 = new ResourceTimeRegistration()
{
OuterResourceId = newOuterResource1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 06, 06)
};
await DbContext.ResourceTimeRegistrations.AddRangeAsync(newTimeRegistrationWeek, newTimeRegistrationWeek1,
newTimeRegistrationWeek2, newTimeRegistrationWeek3, newTimeRegistrationWeek4, newTimeRegistrationWeek5,
newTimeRegistrationWeek6);
await DbContext.SaveChangesAsync();
GenerateReportModel model = new GenerateReportModel()
{
DateFrom = new DateTime(2019, 5, 13),
DateTo = new DateTime(2019, 6, 07),
Relationship = ReportRelationshipType.OuterResource,
Type = ReportType.Week
};
List <SiteDto> sitesList = new List <SiteDto>()
{
new SiteDto(1, "Test Site 1", "", "", 1, 1, 1, 1),
new SiteDto(2, "Test Site 2", "", "", 1, 1, 1, 1)
};
DateTime modelDateFrom = new DateTime(
model.DateFrom.Year,
model.DateFrom.Month,
model.DateFrom.Day,
0, 0, 0);
DateTime modelDateTo = new DateTime(
model.DateTo.Year,
model.DateTo.Month,
model.DateTo.Day,
23, 59, 59);
List <ResourceTimeRegistration> jobsList = await DbContext.ResourceTimeRegistrations
.Include(x => x.InnerResource)
.Include(x => x.OuterResource)
.Where(x => x.DoneAt >= modelDateFrom && x.DoneAt <= modelDateTo)
.ToListAsync();
ReportModel reportModel = ReportsHelper.GetReportData(model, jobsList, sitesList, (int)ReportTimeType.Minutes);
Assert.AreEqual(reportModel.SubReports.Count, 1);
Assert.AreEqual(reportModel.SubReports[0].TotalTime, 3600);
Assert.AreEqual(reportModel.SubReports[0].Entities.Count, 3);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].EntityName, newOuterResource.Name);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].EntityName, newOuterResource1.Name);
Assert.AreEqual(reportModel.SubReports[0].Entities[2].EntityName, newOuterResource2.Name);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TotalTime, 600);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].TotalTime, 2400);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[0], 0);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[1], 600);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[2], 0);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[3], 0);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].TimePerTimeUnit[0], 0);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].TimePerTimeUnit[1], 600);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].TimePerTimeUnit[2], 0);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].TimePerTimeUnit[3], 1800);
}
public async Task EmployeesReportByDay_Generate_DoesGenerate()
{
OuterResource newArea = new OuterResource()
{
Name = "My OuterResource 1", Version = 1
};
OuterResource newArea1 = new OuterResource()
{
Name = "My OuterResource 2", Version = 1
};
OuterResource newArea2 = new OuterResource()
{
Name = "My OuterResource 3", Version = 1
};
InnerResource newMachine = new InnerResource()
{
Name = "My InnerResource 1", Version = 1
};
InnerResource newMachine1 = new InnerResource()
{
Name = "My InnerResource 2", Version = 1
};
InnerResource newMachine2 = new InnerResource()
{
Name = "My InnerResource 3", Version = 1
};
await newArea.Create(DbContext);
await newArea1.Create(DbContext);
await newArea2.Create(DbContext);
await newMachine.Create(DbContext);
await newMachine1.Create(DbContext);
await newMachine2.Create(DbContext);
// Different days
ResourceTimeRegistration newTimeRegistrationDay = new ResourceTimeRegistration()
{
OuterResourceId = newArea.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 13)
};
ResourceTimeRegistration newTimeRegistrationDay1 = new ResourceTimeRegistration()
{
OuterResourceId = newArea1.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 14)
};
ResourceTimeRegistration newTimeRegistrationDay2 = new ResourceTimeRegistration()
{
OuterResourceId = newArea2.Id, Version = 1, SDKSiteId = 1, InnerResourceId = newMachine2.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 15)
};
ResourceTimeRegistration newTimeRegistrationDay3 = new ResourceTimeRegistration()
{
OuterResourceId = newArea.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 13)
};
ResourceTimeRegistration newTimeRegistrationDay4 = new ResourceTimeRegistration()
{
OuterResourceId = newArea1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 14)
};
ResourceTimeRegistration newTimeRegistrationDay5 = new ResourceTimeRegistration()
{
OuterResourceId = newArea1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine2.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 15)
};
ResourceTimeRegistration newTimeRegistrationDay6 = new ResourceTimeRegistration()
{
OuterResourceId = newArea1.Id, Version = 1, SDKSiteId = 2, InnerResourceId = newMachine1.Id, TimeInSeconds = 36000, DoneAt = new DateTime(2019, 05, 15)
};
await DbContext.ResourceTimeRegistrations.AddRangeAsync(newTimeRegistrationDay, newTimeRegistrationDay1,
newTimeRegistrationDay2, newTimeRegistrationDay3, newTimeRegistrationDay4, newTimeRegistrationDay5,
newTimeRegistrationDay6);
await DbContext.SaveChangesAsync();
GenerateReportModel model = new GenerateReportModel()
{
DateFrom = new DateTime(2019, 5, 13),
DateTo = new DateTime(2019, 5, 16),
Relationship = ReportRelationshipType.Employee,
Type = ReportType.Day
};
List <SiteDto> sitesList = new List <SiteDto>()
{
new SiteDto(1, "Test Site 1", "", "", 1, 1, 1, 1),
new SiteDto(2, "Test Site 2", "", "", 1, 1, 1, 1)
};
DateTime modelDateFrom = new DateTime(
model.DateFrom.Year,
model.DateFrom.Month,
model.DateFrom.Day,
0, 0, 0);
DateTime modelDateTo = new DateTime(
model.DateTo.Year,
model.DateTo.Month,
model.DateTo.Day,
23, 59, 59);
List <ResourceTimeRegistration> jobsList = await DbContext.ResourceTimeRegistrations
.Include(x => x.InnerResource)
.Include(x => x.OuterResource)
.Where(x => x.DoneAt >= modelDateFrom && x.DoneAt <= modelDateTo)
.ToListAsync();
ReportModel reportModel = ReportsHelper.GetReportData(model, jobsList, sitesList, (int)ReportTimeType.Minutes);
Assert.AreEqual(reportModel.SubReports.Count, 1);
Assert.AreEqual(reportModel.SubReports[0].TotalTime, 4200);
Assert.AreEqual(reportModel.SubReports[0].Entities.Count, sitesList.Count);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].EntityName, sitesList[0].SiteName);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].EntityName, sitesList[1].SiteName);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TotalTime, 1800);
Assert.AreEqual(reportModel.SubReports[0].Entities[1].TotalTime, 2400);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[0], 600);
Assert.AreEqual(reportModel.SubReports[0].Entities[0].TimePerTimeUnit[1], 600);
}
public ActionResult GenerateReport(GenerateReportModel generateReportModel)
{
SearchCropDetails(generateReportModel);
return(View(generateReportModel));
}
