public ResultsModel GetEanCodeReport(bool includePhasedOut = false, bool cartonisedOnly = false)
{
var articles = this.salesArticleService.GetByDiscountFamily("HIFI", includePhasedOut);
if (cartonisedOnly)
{
articles = articles.Where(c => !string.IsNullOrEmpty(c.CartonType));
}
var results = new ResultsModel(new[] { "Description", "EAN Code" })
{
RowHeader = "Article Number",
ReportTitle = new NameModel("Sales Article EAN Codes")
};
results.SetColumnType(0, GridDisplayType.TextValue);
results.SetColumnType(1, GridDisplayType.TextValue);
foreach (var salesArticle in articles.OrderBy(a => a.ArticleNumber))
{
var row = results.AddRow(salesArticle.ArticleNumber);
results.SetGridTextValue(row.RowIndex, 0, salesArticle.InvoiceDescription);
results.SetGridTextValue(row.RowIndex, 1, salesArticle.EanCode);
}
return(results);
}
public ResultsModel GetPartDataAtLocation(int locationId)
{
var table = this.databaseService.GetPartDataAtLocation(locationId);
var results = new ResultsModel(new[] { "Trigger Level", "Max Capacity", "Qty At Location", "Total Qty At EK-2 Locations" })
{
ReportTitle = new NameModel($"Stock Trigger Levels")
};
results.SetColumnType(0, GridDisplayType.TextValue);
results.SetColumnType(1, GridDisplayType.Value);
results.SetColumnType(2, GridDisplayType.Value);
results.SetColumnType(3, GridDisplayType.Value);
foreach (DataRow tableRow in table.Rows)
{
var eK2Total = this.databaseService.GetQtyAvailableAtEk2Location((string)tableRow[1]);
var row = results.AddRow(tableRow[1].ToString().Replace("/", "%2F"), tableRow[1].ToString());
results.SetGridValue(row.RowIndex, 0, NullOrNumber(tableRow[2]));
results.SetGridValue(row.RowIndex, 1, NullOrNumber(tableRow[3]));
results.SetGridValue(row.RowIndex, 2, NullOrNumber(tableRow[6]));
results.SetGridValue(row.RowIndex, 3, NullOrNumber(eK2Total));
}
results.RowDrillDownTemplates.Add(new DrillDownModel("name", $"/products/reports/stock-trigger-levels/{locationId}/" + "{rowId}"));
return(results);
}
public ResultsModel GetStockTriggerLevelReportForPartAtLocation(int locationId, string partNumber)
{
var table = this.databaseService.GetStockTriggerLevelsForPartAtLocation(locationId, partNumber);
var results =
new ResultsModel(
new[] { "Pallet Number", "Location Code", "Qty Available", "Qty Allocated", "Stock Rotation Date" })
{
ReportTitle = new NameModel($"Part {partNumber} Availability")
};
results.SetColumnType(0, GridDisplayType.Value);
results.SetColumnType(1, GridDisplayType.TextValue);
results.SetColumnType(2, GridDisplayType.Value);
results.SetColumnType(3, GridDisplayType.Value);
results.SetColumnType(4, GridDisplayType.TextValue);
var rowId = 0;
foreach (DataRow tableRow in table.Rows)
{
var row = results.AddRow((rowId++).ToString());
results.SetGridValue(row.RowIndex, 0, NullOrNumber(tableRow[1]));
results.SetGridTextValue(row.RowIndex, 1, tableRow[3] == DBNull.Value ? null : tableRow[3].ToString());
results.SetGridValue(row.RowIndex, 2, NullOrNumber(tableRow[4]));
results.SetGridValue(row.RowIndex, 3, NullOrNumber(tableRow[5]));
results.SetGridTextValue(row.RowIndex, 4, tableRow[6] == DBNull.Value ? null : tableRow[6].ToString());
}
return(results);
}
public ResultsModel GetOutstandingWorksOrders(string reportType, string searchParameter)
{
var table = this.databaseService.GetReport(reportType, searchParameter);
var results =
new ResultsModel(
new[]
{
"Part Number",
"Description",
"Date Raised",
"Qty Outstanding"
})
{
RowHeader = "Order Number",
ReportTitle = new NameModel("Outstanding Works Orders")
};
for (var i = 0; i < 3; i++)
{
results.SetColumnType(i, GridDisplayType.TextValue);
}
foreach (DataRow tableRow in table.Rows)
{
var row = results.AddRow(tableRow[0]?.ToString());
results.SetGridTextValue(row.RowIndex, 0, tableRow[1]?.ToString());
results.SetGridTextValue(row.RowIndex, 1, tableRow[5]?.ToString());
results.SetGridTextValue(row.RowIndex, 2, tableRow[3]?.ToString());
results.SetGridValue(row.RowIndex, 3, tableRow[2]?.ToString().ParseDecimal());
}
return(results);
}
public ResultsModel GetHoldStoriesForRootProduct(string rootProduct)
{
var stories = this.saHoldStoryRepository.FindAll().Where(s => s.RootProduct.Name == rootProduct);
var results = new ResultsModel(new[] { "Date Started", "Date Finished" })
{
RowHeader = "Name", ReportTitle = new NameModel("Hold Stories")
};
results.SetColumnType(0, GridDisplayType.TextValue);
foreach (var story in stories.OrderByDescending(a => a.DateStarted))
{
var row = results.AddRow(story.HoldStoryId.ToString().Replace("/", "%2F"));
results.SetGridTextValue(row.RowIndex, 0, story.DateStarted.ToShortDateString());
results.SetGridTextValue(
row.RowIndex,
1,
story.DateFinished != null
? ((DateTime)story.DateFinished).ToShortDateString()
: story.DateFinished.ToString());
}
results.RowDrillDownTemplates.Add(new DrillDownModel("story", "/products/reports/sa-hold-stories/{rowId}"));
return(results);
}
public ResultsModel GetCartonsReport()
{
var cartons = this.cartonRepository.GetCartons();
var results = new ResultsModel(new[] { "Description", "Height", "Width", "Depth" })
{
RowHeader = "Name",
ReportTitle = new NameModel("Carton Details")
};
results.SetColumnType(0, GridDisplayType.TextValue);
foreach (var carton in cartons.OrderBy(a => a.Name))
{
var row = results.AddRow(carton.Name);
results.SetGridTextValue(row.RowIndex, 0, carton.Description);
results.SetGridValue(row.RowIndex, 1, (decimal)carton.Height);
results.SetGridValue(row.RowIndex, 2, (decimal)carton.Width);
results.SetGridValue(row.RowIndex, 3, (decimal)carton.Depth);
}
results.RowDrillDownTemplates.Add(new DrillDownModel("carton", "/products/maint/carton-types/{rowId}"));
return(results);
}
public ResultsModel GetSalesProductByRangeReport(int productRangeId, bool includePhasedOut = false)
{
var salesProducts = this.salesProductRepository.GetSalesProducts();
salesProducts = salesProducts.Where(s => s.ProductRange?.Id == productRangeId);
if (!includePhasedOut)
{
salesProducts = salesProducts.Where(p => p.PhasedOutOn == null);
}
var results = new ResultsModel(new[] { "Name", "Description" })
{
RowHeader = "Id",
ReportTitle = new NameModel("Sales Products")
};
results.SetColumnType(0, GridDisplayType.TextValue);
results.SetColumnType(1, GridDisplayType.TextValue);
if (includePhasedOut)
{
results.AddColumn("Phased Out On");
results.SetColumnType(2, GridDisplayType.TextValue);
}
foreach (var salesProduct in salesProducts.OrderBy(a => a.Name))
{
var row = results.AddRow(salesProduct.Id.ToString());
results.SetGridTextValue(row.RowIndex, 0, salesProduct.Name);
results.SetGridTextValue(row.RowIndex, 1, salesProduct.Description);
if (includePhasedOut)
{
results.SetGridTextValue(row.RowIndex, 2, salesProduct.PhasedOutOn?.ToShortDateString());
}
}
results.ValueDrillDownTemplates.Add(
new DrillDownModel(
"details",
$"/products/sales-products/{{rowId}}",
null,
0));
return(results);
}
public ResultsModel GetProductRangeReport(bool includePhasedOut = false)
{
var productRanges = this.productRangeRepository.GetProductRanges();
if (!includePhasedOut)
{
productRanges = productRanges.Where(p => p.PhasedOutOn == null);
}
var results = new ResultsModel(new[] { "Name", "Description" })
{
RowHeader = "Id",
ReportTitle = new NameModel("Product Ranges")
};
results.SetColumnType(0, GridDisplayType.TextValue);
results.SetColumnType(1, GridDisplayType.TextValue);
if (includePhasedOut)
{
results.AddColumn("Phased Out On");
results.SetColumnType(2, GridDisplayType.TextValue);
}
foreach (var productRange in productRanges.OrderBy(a => a.Name))
{
var row = results.AddRow(productRange.Id.ToString());
results.SetGridTextValue(row.RowIndex, 0, productRange.Name);
results.SetGridTextValue(row.RowIndex, 1, productRange.Description);
if (includePhasedOut)
{
results.SetGridTextValue(row.RowIndex, 2, productRange.PhasedOutOn?.ToShortDateString());
}
}
results.ValueDrillDownTemplates.Add(
new DrillDownModel(
"sales-products",
$"/products/reports/sales-products-by-product-range?productRangeId={{rowId}}&productRangeName={{textValue}}&includePhasedOut={includePhasedOut.ToString().ToLowerInvariant()}",
null,
0));
return(results);
}
public ResultsModel SalesArticleCoreTypeReport()
{
var articles = this.salesArticleRepository.FilterBy(a => a.PhaseOutDate == null && a.SaCoreType != null);
var results = new ResultsModel(new[] { "Description", "CoreType" })
{
RowHeader = "Article Number",
ReportTitle = new NameModel("Sales Article Core Types")
};
results.SetColumnType(0, GridDisplayType.TextValue);
results.SetColumnType(1, GridDisplayType.TextValue);
foreach (var salesArticle in articles.OrderBy(a => a.ArticleNumber))
{
var row = results.AddRow(salesArticle.ArticleNumber);
results.SetGridTextValue(row.RowIndex, 0, salesArticle.InvoiceDescription);
results.SetGridTextValue(row.RowIndex, 1, salesArticle.SaCoreType?.Description);
}
return(results);
}
public ResultsModel GetProductsOnHold()
{
var salesArticlesOnHold = this.salesArticleRepository.FindAll().Where(s => s.LastHoldStoryId != null);
var results = new ResultsModel(new[] { "Article Number", "Invoice Description", "Put On Hold By", "Date Started", "Anticipated End Date", "Reason Started" })
{
RowHeader = "Name",
ReportTitle = new NameModel("Products On Hold")
};
results.SetColumnType(0, GridDisplayType.TextValue);
results.SetColumnType(1, GridDisplayType.TextValue);
results.SetColumnType(2, GridDisplayType.TextValue);
results.SetColumnType(3, GridDisplayType.TextValue);
results.SetColumnType(4, GridDisplayType.TextValue);
results.SetColumnType(5, GridDisplayType.TextValue);
foreach (var salesArticleOnHold in salesArticlesOnHold)
{
var holdStory = this.saHoldStoryRepository.FindById((int)salesArticleOnHold.LastHoldStoryId);
var row = results.AddRow(salesArticleOnHold.ArticleNumber.Replace("/", "%2F"), salesArticleOnHold.ArticleNumber);
results.SetGridTextValue(row.RowIndex, 0, salesArticleOnHold.ArticleNumber);
results.SetGridTextValue(row.RowIndex, 1, salesArticleOnHold.InvoiceDescription);
results.SetGridTextValue(row.RowIndex, 2, holdStory.PutOnHoldByEmployee.FullName);
results.SetGridTextValue(row.RowIndex, 3, holdStory.DateStarted.ToShortDateString());
results.SetGridTextValue(row.RowIndex, 4, holdStory.AnticipatedEndDate?.ToShortDateString());
results.SetGridTextValue(row.RowIndex, 5, holdStory.ReasonStarted);
}
results.RowDrillDownTemplates.Add(new DrillDownModel("stories", "/products/maint/sales-articles/{rowId}"));
return(results);
}
public ResultsModel GetAssemblyFailsWaitingListReport()
{
var assemblyFails = this.assemblyFailsRepository
.FindAll().Where(f => f.CompletedBy == null && f.DateInvalid == null)
.OrderBy(f => f.Id);
var results =
new ResultsModel(
new[]
{
"Week",
"When Found",
"Part Number",
"Serial",
"Reported Fault",
"In Slot"
})
{
RowHeader = "Id",
ReportTitle = new NameModel("Assembly Fail Waiting List")
};
foreach (var resultsColumn in results.Columns)
{
results.SetColumnType(resultsColumn.ColumnIndex, GridDisplayType.TextValue);
}
foreach (var fail in assemblyFails.ToList())
{
this.weekPack.Wwsyy(fail.DateTimeFound);
var row = results.AddRow(fail.Id.ToString());
this.weekPack.Wwsyy(fail.DateTimeFound);
results.SetGridTextValue(row.RowIndex, 0, this.weekPack.Wwsyy(fail.DateTimeFound));
results.SetGridTextValue(row.RowIndex, 1, fail.DateTimeFound.ToString("d", new CultureInfo("en-GB")));
results.SetGridTextValue(row.RowIndex, 2, fail.WorksOrder.PartNumber);
results.SetGridTextValue(row.RowIndex, 3, fail.SerialNumber.ToString());
results.SetGridTextValue(row.RowIndex, 4, fail.ReportedFault);
results.SetGridTextValue(row.RowIndex, 5, fail.InSlot);
}
results.RowDrillDownTemplates.Add(new DrillDownModel("Id", "/production/quality/assembly-fails/{rowId}"));
return(results);
}
public IResult <ResultsModel> GetSalesArticleByTariff(int tariffId)
{
var results = this.salesArticleRepository.FilterBy(x => x.TariffId == tariffId);
var tariffCode = this.tariffRepository.FindById(tariffId).TariffCode;
var resultsModel = new ResultsModel(new[] { "Description" })
{
RowHeader = "Article Number",
ReportTitle = new NameModel($"Sales Articles by Tariff Code {tariffCode}")
};
resultsModel.SetColumnType(0, GridDisplayType.TextValue);
foreach (var salesArticle in results.OrderBy(a => a.ArticleNumber))
{
var row = resultsModel.AddRow(salesArticle.ArticleNumber);
resultsModel.SetGridTextValue(row.RowIndex, 0, salesArticle.InvoiceDescription);
}
resultsModel.RowDrillDownTemplates.Add(new DrillDownModel("sales article", "/products/maint/sales-articles/{rowId}"));
return(new SuccessResult <ResultsModel>(resultsModel));
}
private ResultsModel GetFailureRateReport(
DateTime fromDate,
DateTime toDate,
string smtOrPcb,
string placeFound,
IReadOnlyCollection <LinnWeek> weeks)
{
var details = this.GetAteTests(fromDate, toDate, placeFound);
var resultsModel = new ResultsModel
{
ReportTitle = new NameModel("ATE Failure Rate By Board")
};
var columns = new List <AxisDetailsModel>();
foreach (var linnWeek in weeks)
{
columns.Add(new AxisDetailsModel($"{linnWeek.LinnWeekNumber}-tests", $"Tests w/e {linnWeek.WeekEndingDDMON}")
{
SortOrder = (linnWeek.LinnWeekNumber * 10) + 1
});
columns.Add(new AxisDetailsModel($"{linnWeek.LinnWeekNumber}-fails", "Fails")
{
SortOrder = (linnWeek.LinnWeekNumber * 10) + 2
});
columns.Add(new AxisDetailsModel($"{linnWeek.LinnWeekNumber}-percentage", "Pass%")
{
SortOrder = (linnWeek.LinnWeekNumber * 10) + 3
});
}
columns.Add(new AxisDetailsModel($"total-tests", $"Total Tests")
{
SortOrder = int.MaxValue - 3
});
columns.Add(new AxisDetailsModel($"total-fails", "Total Fails")
{
SortOrder = int.MaxValue - 2
});
columns.Add(new AxisDetailsModel($"total-percentage", "Pass%")
{
SortOrder = int.MaxValue - 1
});
resultsModel.AddSortedColumns(columns);
this.reportingHelper.AddResultsToModel(
resultsModel,
this.CalculateFailureRateValues(details, weeks, smtOrPcb),
CalculationValueModelType.Quantity,
true);
this.reportingHelper.SortRowsByRowTitle(resultsModel);
var testColumns = new List <int>();
var failColumns = new List <int>();
foreach (var linnWeek in weeks)
{
var testColumn = resultsModel.ColumnIndex($"{linnWeek.LinnWeekNumber}-tests");
var failColumn = resultsModel.ColumnIndex($"{linnWeek.LinnWeekNumber}-fails");
var percentageColumn = resultsModel.ColumnIndex($"{linnWeek.LinnWeekNumber}-percentage");
this.reportingHelper.SetValuesForPercentageColumn(
resultsModel,
failColumn,
testColumn,
percentageColumn,
1,
true);
testColumns.Add(testColumn);
failColumns.Add(failColumn);
resultsModel.SetColumnType(percentageColumn, GridDisplayType.Total);
}
this.reportingHelper.SetTotalColumn(resultsModel, testColumns, resultsModel.ColumnIndex("total-tests"));
this.reportingHelper.SetTotalColumn(resultsModel, failColumns, resultsModel.ColumnIndex("total-fails"));
this.reportingHelper.SetValuesForPercentageColumn(
resultsModel,
resultsModel.ColumnIndex("total-fails"),
resultsModel.ColumnIndex("total-tests"),
resultsModel.ColumnIndex("total-percentage"),
1,
true);
resultsModel.SetColumnType(resultsModel.ColumnIndex("total-percentage"), GridDisplayType.Total);
return(resultsModel);
}
public ResultsModel GetBuildsDetailReport(
DateTime from,
DateTime to,
string department,
string quantityOrValue,
bool monthly = false)
{
var table = this.databaseService.GetBuildsDetail(from, to, quantityOrValue, department, monthly);
var partGroups = table.Select().GroupBy(r => r[2]).ToList();
var weeks = partGroups
.Select(
g => ((DateTime)g.First().ItemArray[4])).Distinct().OrderBy(w => w).ToList();
var colHeaders = new List <string> {
"Part Number"
};
colHeaders.AddRange(weeks.Select(w => w.ToShortDateString()));
colHeaders.Add("Total");
var results = new ResultsModel(colHeaders)
{
ReportTitle = new NameModel(
$"Builds {quantityOrValue} for {this.departmentRepository.FindById(department).Description}")
};
var rowIndex = 0;
foreach (var partGroup in partGroups)
{
var partTotal = 0m;
results.AddRow(partGroup.Key.ToString());
results.SetGridTextValue(rowIndex, 0, partGroup.Key.ToString());
for (var i = 0; i < weeks.Count; i++)
{
var valueExistsThisWeek = partGroup.FirstOrDefault(g =>
((DateTime)g.ItemArray[4]).ToShortDateString()
== weeks.ElementAt(i).ToShortDateString()) != null;
var val = valueExistsThisWeek
? ConvertFromDbVal <decimal>(
partGroup.FirstOrDefault(
g => ((DateTime)g.ItemArray[4]).ToShortDateString() == weeks.ElementAt(i).ToShortDateString())
?.ItemArray[quantityOrValue == "Mins" ? 6 : 5])
: new decimal(0);
results.SetColumnType(i + 1, GridDisplayType.Value);
results.SetGridValue(rowIndex, i + 1, val, decimalPlaces: 2);
if (!valueExistsThisWeek)
{
continue;
}
var itemArray = partGroup.First(
g => ((DateTime)g.ItemArray[4]).ToShortDateString()
== weeks.ElementAt(i).ToShortDateString())
?.ItemArray;
{
if (itemArray != null)
{
partTotal += ConvertFromDbVal <decimal>(itemArray?[quantityOrValue == "Mins" ? 6: 5]);
}
}
}
results.SetColumnType(weeks.Count, GridDisplayType.Value);
results.SetGridValue(rowIndex, weeks.Count + 1, partTotal, decimalPlaces: 2);
rowIndex++;
}
return(results);
}