public DeficientResult GetData(SimulationModel data, int[] totalYears)
{
// Deficient and DeficientResults are models. Deficient gets data
// from the database. DeficientResult gets the processed data
IQueryable <DeficientReportModel> deficients = null;
DeficientResult result = null;
var select =
"SELECT TargetID, Years, TargetMet, IsDeficient " +
" FROM Target_" + data.NetworkId
+ "_" + data.SimulationId;
try
{
var rawDeficientList = db.Database.SqlQuery <DeficientReportModel>(select).AsQueryable();
deficients = rawDeficientList.Where(_ => _.IsDeficient == true);
var targetAndYear = this.deficients.GetData(deficients);
result = GetDeficientInformation(data, targetAndYear, totalYears);
}
catch (SqlException ex)
{
HandleException.SqlError(ex, "Target");
}
catch (OutOfMemoryException ex)
{
HandleException.OutOfMemoryError(ex);
}
catch (Exception ex)
{
HandleException.GeneralError(ex);
}
return(result);
}
/// <summary>
/// Generate InventoryItemDetailModel
/// </summary>
/// <param name="inventoryModel"></param>
/// <returns></returns>
public InventoryItemDetailModel MakeInventoryItemDetailModel(InventoryModel inventoryModel)
{
var inventoryItems = inventoryModel.InventoryItems;
var inventoryNbiLoadRatings = inventoryModel.InventoryNbiLoadRatings;
var inventoryItemDetailModel = new InventoryItemDetailModel();
try
{
AddLocation(inventoryItemDetailModel, inventoryItems);
AddAgeService(inventoryItemDetailModel, inventoryItems);
AddManagement(inventoryItemDetailModel, inventoryItems);
AddDeckInformation(inventoryItemDetailModel, inventoryItems);
AddSpanInformation(inventoryItemDetailModel, inventoryItems);
AddNbiLoadRating(inventoryItemDetailModel, inventoryNbiLoadRatings);
AddPosting(inventoryItemDetailModel, inventoryItems);
AddRoadwayInfo(inventoryItemDetailModel, inventoryItems);
AddCurrentConditionDuration(inventoryItemDetailModel, inventoryItems);
AddRiskScores(inventoryItemDetailModel);
AddOperatingInventoryRating(inventoryItemDetailModel, inventoryItems);
}
catch (Exception ex)
{
HandleException.GeneralError(ex);
}
return(inventoryItemDetailModel);
}
public SectionLocationModel Locate(SectionModel section, BridgeCareContext db)
{
try
{
var sectionLocationModel = new SectionLocationModel();
// Including isnull statments to change these to '0' to avoid an
// exception as mapping a null to a non nullable data type
// crashes the entity framwork
var query =
$"SELECT Sectionid, isnull(Lat,0) as Latitude, isnull(Long,0) as Longitude FROM Segment_{section.NetworkId}_NS0 WHERE SectionId = @sectionId";
// create and open db connection
var connection = new SqlConnection(db.Database.Connection.ConnectionString);
connection.Open();
// create a sql command with the query and connection
var sqlCommand = new SqlCommand(query, connection);
// add parameter to sqlCommand
sqlCommand.Parameters.Add(new SqlParameter()
{
ParameterName = "@sectionId",
Value = section.SectionId
});
// create data reader from the sql command
var dataReader = sqlCommand.ExecuteReader();
// check if data reader has rows and can execute a Read
if (dataReader.HasRows && dataReader.Read())
{
// get the section location details
sectionLocationModel.SectionId = dataReader.GetFieldValue <int>(0);
sectionLocationModel.Latitude = dataReader.GetFieldValue <double>(1);
sectionLocationModel.Longitude = dataReader.GetFieldValue <double>(2);
}
// close the data reader
dataReader.Close();
// close the connection
connection.Close();
// return the sectionLocationModel
return(sectionLocationModel);
}
catch (SqlException ex)
{
log.Error(ex.Message);
HandleException.SqlError(ex, "SectionLocator_");
}
catch (OutOfMemoryException ex)
{
log.Error(ex.Message);
HandleException.OutOfMemoryError(ex);
}
catch (Exception ex)
{
log.Error(ex.Message);
HandleException.GeneralError(ex);
}
return(new SectionLocationModel());
}
private void GetCommittedProjectModels(HttpPostedFile postedFile, int simulationId, int networkId, bool applyNoTreatment,
List <CommittedProjectModel> committedProjectModels, BridgeCareContext db)
{
try
{
var package = new ExcelPackage(postedFile.InputStream); //(new FileInfo(postedFile.FileName));
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
var headers = worksheet.Cells.GroupBy(cell => cell.Start.Row).First();
var start = worksheet.Dimension.Start;
var end = worksheet.Dimension.End;
for (int row = start.Row + 1; row <= end.Row; row++)
{
var column = start.Column + 2;
var brKey = Convert.ToInt32(GetCellValue(worksheet, row, 1));
var sectionId = sectionsRepo.GetSectionId(networkId, brKey, db);
// BMSID till COST -> entry in COMMITTED_
var committedProjectModel = new CommittedProjectModel
{
SectionId = sectionId,
SimulationId = simulationId,
TreatmentName = GetCellValue(worksheet, row, column),
Years = Convert.ToInt32(GetCellValue(worksheet, row, ++column)),
YearAny = Convert.ToInt32(GetCellValue(worksheet, row, ++column)),
YearSame = Convert.ToInt32(GetCellValue(worksheet, row, ++column)),
Budget = GetCellValue(worksheet, row, ++column),
Cost = Convert.ToInt32(GetCellValue(worksheet, row, ++column))
};
var commitConsequences = new List <CommitConsequenceModel>();
// Ignore AREA column, from current column till end.Column -> attributes i.e. entry in COMMIT_CONSEQUENCES
for (var col = column + 2; col <= end.Column; col++)
{
commitConsequences.Add(new CommitConsequenceModel
{
Attribute_ = GetHeader(headers, col),
Change_ = GetCellValue(worksheet, row, col)
});
}
committedProjectModel.CommitConsequences = commitConsequences;
committedProjectModels.Add(committedProjectModel);
var simulation = db.Simulations.SingleOrDefault(s => s.SIMULATIONID == simulationId);
if (applyNoTreatment && simulation != null)
{
if (simulation.COMMITTED_START < committedProjectModel.Years)
{
var year = committedProjectModel.Years - 1;
while (year >= simulation.COMMITTED_START)
{
committedProjectModels.Add(new CommittedProjectModel()
{
SectionId = committedProjectModel.SectionId,
SimulationId = committedProjectModel.SimulationId,
TreatmentName = "No Treatment",
Years = year,
YearAny = committedProjectModel.YearAny,
YearSame = committedProjectModel.YearSame,
Budget = committedProjectModel.Budget,
Cost = 0,
CommitConsequences = committedProjectModel.CommitConsequences
});
year--;
}
}
}
}
}
catch (Exception ex)
{
HandleException.GeneralError(ex);
}
}
public void Fill(ExcelWorksheet worksheet, int[] totalYears, SimulationModel data, BridgeCareContext dbContext)
{
var totalYearsCount = totalYears.Count();
var rawQueryForData = detailedReport.GetRawQuery(data, dbContext);
var headers = new List <string>
{
"Facility",
"Section"
};
for (int i = 0; i < totalYearsCount; i++)
{
headers.Add(totalYears[i].ToString());
}
for (int x = 0; x < headers.Count; x++)
{
worksheet.Cells[1, x + 1].Value = headers[x];
}
int rowsToColumns = 0, columnNumber = 2, rowNumber = 2;
var conditionalData = new DetailReportModel();
try
{
foreach (var newData in rawQueryForData)
{
if (rowsToColumns == 0)
{
columnNumber = 2;
worksheet.Cells[rowNumber, columnNumber - 1].Value = newData.Facility;
worksheet.Cells[rowNumber, columnNumber].Value = newData.Section;
}
conditionalData.Treatment = newData.Treatment;
conditionalData.IsCommitted = newData.IsCommitted;
conditionalData.NumberTreatment = newData.NumberTreatment;
conditionalData.RowNumber = rowNumber;
conditionalData.ColumnNumber = columnNumber;
ExcelValues[conditionalData.IsCommitted].Invoke(conditionalData, worksheet);
columnNumber++;
if (rowsToColumns + 1 >= totalYearsCount)
{
rowsToColumns = 0;
rowNumber++;
}
else
{
rowsToColumns++;
}
}
}
catch (Exception ex)
{
HandleException.GeneralError(ex);
}
worksheet.Cells.AutoFitColumns();
}