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 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 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);
}
private ResultsModel IncompleteLinesAnalysis(IEnumerable <MCDLine> data)
{
var model = new ResultsModel {
ReportTitle = new NameModel("Incomplete Lines Analysis")
};
var notSuppliedLines = data.Where(mcdLine => !string.IsNullOrEmpty(mcdLine.Reason)).ToList();
var totalNotSupplied = notSuppliedLines.Count;
var rows = new List <AxisDetailsModel>
{
new AxisDetailsModel("Allocated", GridDisplayType.Value),
new AxisDetailsModel("No Stock", GridDisplayType.Value),
new AxisDetailsModel("Credit Limit", GridDisplayType.Value),
new AxisDetailsModel("Supply In Full", GridDisplayType.Value),
new AxisDetailsModel("Account Hold", GridDisplayType.Value),
new AxisDetailsModel("Shipment Hold", GridDisplayType.Value),
new AxisDetailsModel("Dunno", GridDisplayType.Value)
};
var columns = new List <AxisDetailsModel>
{
new AxisDetailsModel("Qty", GridDisplayType.Value),
new AxisDetailsModel("%", GridDisplayType.Value)
};
model.AddSortedRows(rows);
model.AddSortedColumns(columns);
this.reportingHelper.ZeroPad(model);
if (totalNotSupplied > 0)
{
var allocated = notSuppliedLines.Count(a => a.Reason.Contains("allocated"));
var noStock = notSuppliedLines.Count(a => a.Reason.Contains("No Stock"));
var creditLimit = notSuppliedLines.Count(a => a.Reason.Contains("CR"));
var accountHold = notSuppliedLines.Count(a => a.Reason.Contains("AH") && !a.Reason.Contains("CR"));
var shipmentHold = notSuppliedLines.Count(a => a.Reason.Contains("SH") && !a.Reason.Contains("CR") && !a.Reason.Contains("AH"));
var supplyInFull = notSuppliedLines.Count(a => a.Reason.Contains("SIF") && !a.Reason.Contains("SH") && !a.Reason.Contains("CR") && !a.Reason.Contains("AH"));
var dunno = totalNotSupplied - (noStock + creditLimit + supplyInFull + accountHold + shipmentHold);
model.SetGridValue(model.RowIndex("Allocated"), model.ColumnIndex("Qty"), allocated);
model.SetGridValue(model.RowIndex("Allocated"), model.ColumnIndex("%"), this.GetPercentage(allocated, totalNotSupplied));
model.SetGridValue(model.RowIndex("No Stock"), model.ColumnIndex("Qty"), noStock);
model.SetGridValue(model.RowIndex("No Stock"), model.ColumnIndex("%"), this.GetPercentage(noStock, totalNotSupplied));
model.SetGridValue(model.RowIndex("Credit Limit"), model.ColumnIndex("Qty"), creditLimit);
model.SetGridValue(model.RowIndex("Credit Limit"), model.ColumnIndex("%"), this.GetPercentage(creditLimit, totalNotSupplied));
model.SetGridValue(model.RowIndex("Supply In Full"), model.ColumnIndex("Qty"), supplyInFull);
model.SetGridValue(model.RowIndex("Supply In Full"), model.ColumnIndex("%"), this.GetPercentage(supplyInFull, totalNotSupplied));
model.SetGridValue(model.RowIndex("Account Hold"), model.ColumnIndex("Qty"), accountHold);
model.SetGridValue(model.RowIndex("Account Hold"), model.ColumnIndex("%"), this.GetPercentage(accountHold, totalNotSupplied));
model.SetGridValue(model.RowIndex("Shipment Hold"), model.ColumnIndex("Qty"), shipmentHold);
model.SetGridValue(model.RowIndex("Shipment Hold"), model.ColumnIndex("%"), this.GetPercentage(shipmentHold, totalNotSupplied));
model.SetGridValue(model.RowIndex("Dunno"), model.ColumnIndex("Qty"), dunno);
model.SetGridValue(model.RowIndex("Dunno"), model.ColumnIndex("%"), this.GetPercentage(dunno, totalNotSupplied));
}
return(model);
}
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);
}
public IEnumerable <ResultsModel> TqmsSummaryByCategoryReport(string jobRef, bool headingsOnly = true)
{
var stock = this.tqmsSummaryByCategoryQueryRepository.FilterBy(t => t.JobRef == jobRef);
var loan = this.tqmsOutstandingLoansByCategoryRepository.FilterBy(t => t.JobRef == jobRef);
var jobRefDetails = this.tqmsJobRefsRepository.FindById(jobRef);
var summaryResultsModel = new ResultsModel
{
ReportTitle = new NameModel($"Total Stock Summary {jobRefDetails.DateOfRun:dd-MMM-yyyy} ({jobRefDetails.JobRef})")
};
summaryResultsModel.AddSortedColumns(new List <AxisDetailsModel>
{
new AxisDetailsModel("StockType", GridDisplayType.TextValue),
new AxisDetailsModel("Value", GridDisplayType.Value)
});
summaryResultsModel.AddRow("Total Stock");
summaryResultsModel.AddRow("Loan Stock Value");
summaryResultsModel.SetGridTextValue(0, 0, "Stock Value");
summaryResultsModel.SetGridValue(0, 1, stock.Sum(a => a.TotalValue));
summaryResultsModel.SetGridTextValue(1, 0, "Loan Stock Value");
summaryResultsModel.SetGridValue(1, 1, loan.Sum(a => a.TotalStoresValue));
var resultsModel = new ResultsModel {
ReportTitle = new NameModel("TQMS Summary")
};
if (headingsOnly)
{
var columns = new List <AxisDetailsModel>
{
new AxisDetailsModel("Heading", GridDisplayType.TextValue),
new AxisDetailsModel("Active", GridDisplayType.Value),
new AxisDetailsModel("Inactive", GridDisplayType.Value),
new AxisDetailsModel("Value", GridDisplayType.Value)
};
resultsModel.AddSortedColumns(columns);
var models = new List <CalculationValueModel>();
foreach (var tqmsSummaryByCategory in stock.OrderBy(a => a.HeadingOrder))
{
models.Add(
new CalculationValueModel
{
RowId = tqmsSummaryByCategory.HeadingCode,
ColumnId = "Heading",
TextDisplay = tqmsSummaryByCategory.HeadingDescription
});
if (tqmsSummaryByCategory.ActiveCategory == "Y")
{
models.Add(
new CalculationValueModel
{
RowId = tqmsSummaryByCategory.HeadingCode,
ColumnId = "Active",
Value = tqmsSummaryByCategory.TotalValue
});
}
else
{
models.Add(
new CalculationValueModel
{
RowId = tqmsSummaryByCategory.HeadingCode,
ColumnId = "Inactive",
Value = tqmsSummaryByCategory.TotalValue
});
}
models.Add(
new CalculationValueModel
{
RowId = tqmsSummaryByCategory.HeadingCode,
ColumnId = "Value",
Value = tqmsSummaryByCategory.TotalValue
});
}
this.reportingHelper.AddResultsToModel(resultsModel, models, CalculationValueModelType.Value, true);
}
else
{
var columns = new List <AxisDetailsModel>
{
new AxisDetailsModel("Heading", GridDisplayType.TextValue),
new AxisDetailsModel("Category", GridDisplayType.TextValue),
new AxisDetailsModel("Value", GridDisplayType.Value)
};
resultsModel.AddSortedColumns(columns);
var models = new List <CalculationValueModel>();
var rowNumber = 0;
foreach (var tqmsSummaryByCategory in stock)
{
rowNumber++;
var rowId = $"{rowNumber:000}";
models.Add(
new CalculationValueModel
{
RowId = rowId,
ColumnId = "Heading",
TextDisplay = tqmsSummaryByCategory.HeadingDescription
});
models.Add(
new CalculationValueModel
{
RowId = rowId,
ColumnId = "Category",
TextDisplay = tqmsSummaryByCategory.CategoryDescription
});
models.Add(
new CalculationValueModel
{
RowId = rowId,
ColumnId = "Value",
Value = tqmsSummaryByCategory.TotalValue
});
}
this.reportingHelper.AddResultsToModel(resultsModel, models, CalculationValueModelType.Value, true);
this.reportingHelper.SubtotalRowsByTextColumnValue(resultsModel, 0, new[] { 2 }, false);
this.reportingHelper.RemovedRepeatedValues(resultsModel, 0, new[] { 0 });
}
return(new List <ResultsModel> {
summaryResultsModel, resultsModel
});
}