public ReportViewModel(List<SegmentSurvey> SegmentSurveys, DashBoardViewModel data, string placeName, string placeType)
{
//DateTime = DateTime.Now;
dateTime = DateTime.Now.ToString();
this.SegmentSurveys = SegmentSurveys;
this.ReportData = data;
Jurisdiction = placeName;
PlaceType = placeType;
}
// Used for dashboard score distribution
// 90-100, 80-90, 70-80,60-70,50-60,<50
//public static double[] get_X_Year_ScoreDistribution(string placeID, int year=100, int score1=90,
// int score2=80,int score3=70, int score4=60, int score5=50,
// int score6=0)
#endregion
// for Dashboard, calculate unfinished/ finished/ uninitialized roads
public static DashBoardViewModel Get_X_Year_Road_Information(string placeID)
{
var sqlConnection = new SqlConnection(connectionString);
if (sqlConnection.State == ConnectionState.Closed) sqlConnection.Open();
string databaseName = sqlConnection.Database.Split('_')[0];
// Step 1. Calculate total road length
double? totalLength = 0;
double? uninitiatedLength = 0;
SqlCommand command = new SqlCommand("select sum(Length) from " + databaseName + ".segments where PlaceId=" + placeID, sqlConnection);
object sql_result;
try
{
sql_result = command.ExecuteScalar();
totalLength = (sql_result == null || DBNull.Value.Equals(sql_result)) ? 0 : (double)sql_result;
}
catch (Exception e)
{
e.ToString();
throw new InvalidOperationException("The sql_result is not valid");
}
command = new SqlCommand("select sum(Length) as inactiveLength from " + databaseName + ".segments join " + databaseName + ".roads on " + databaseName + ".segments.roadid=" + databaseName + ".roads.id where " + databaseName + ".roads.placeid=" + placeID + " and " + databaseName + ".roads.lastsurveydate is null", sqlConnection);
try
{
sql_result = command.ExecuteScalar();
if (sql_result != null && !DBNull.Value.Equals(sql_result))
uninitiatedLength = (double)sql_result;
uninitiatedLength = Convert.ToInt32(uninitiatedLength);
}
catch (Exception e)
{
e.ToString();
throw new InvalidOperationException("The sql_result is not valid");
}
command = new SqlCommand("select sum(seg.length) from " + databaseName + ".segmentsurveys as sur join " + databaseName + ".segments as seg on sur.roadid= seg.roadid and sur.sequenceid=seg.sequenceid where seg.placeid=" + placeID, sqlConnection);
double FinishedLength;
try
{
sql_result = command.ExecuteScalar();
FinishedLength = (sql_result == null || DBNull.Value.Equals(sql_result)) ? 0 : (double)sql_result;
}
catch (Exception e)
{
e.ToString();
throw;
}
var UnfinishedLength = Convert.ToInt32(totalLength) - FinishedLength - uninitiatedLength;
int pctcompletion = 0;
if (totalLength == null || totalLength < 1)
{
pctcompletion = 0;
}
else
{
pctcompletion = (int)FinishedLength * 100 / Convert.ToInt32(totalLength);
}
// To calculate the nearest 3 years.
double[] unfinished_X_yearLength = new double[3];
double[] finished_X_yearLength = new double[3];
//for (int i = 0; i < 3; i++)
//{
// finished_X_yearLength[i] = 0;
// unfinished_X_yearLength[i] = 0;
// var Date_X_years = DateTime.Today.AddYears(-i - 1);
// var surveys_within_X_Years = surveys.Where(x => Convert.ToDateTime(x.SegSurveyDate).CompareTo(Date_X_years) >= 0);
// segmentList.Clear();
// foreach (var segSurvey in surveys_within_X_Years)
// {
// var seg = ApiHelper.GetSegmentByRoadIdAndSequenceId(segSurvey.RoadId, segSurvey.SequenceId).Result;
// if (!segmentList.Contains(seg.InventId))
// {
// finished_X_yearLength[i] += Convert.ToInt32(seg.Length);
// segmentList.Add(seg.InventId);
// }
// unfinished_X_yearLength[i] = Convert.ToInt32(totalLength) - finished_X_yearLength[i] - uninitiatedLength;
// }
//}
for (int i = 0; i < 3; i++)
{
var Date_X_years = DateTime.Today.AddYears(-i - 1).ToString();
//command = new SqlCommand("select sum(seg.length) from " + databaseName + ".segmentsurveys as sur join " + databaseName + ".segments as seg on sur.roadid= seg.roadid and sur.sequenceid=seg.sequenceid where seg.placeid=" + placeID + " and sur.SegsurveyDate >= '" + DateTime.Now.AddYears(-i - 1).ToString() + "' ", sqlConnection);
command = new SqlCommand("select sum(avg_length) from (select sur.roadid, sur.sequenceid, avg(seg.length) as avg_length from " +
databaseName + ".segmentsurveys as sur join " + databaseName +
".segments as seg on sur.roadid=seg.roadid and sur.sequenceid=seg.sequenceid where seg.placeid=" +
placeID + " and sur.SegsurveyDate >= '" + DateTime.Now.AddYears(-i - 1).ToString() + "' group by sur.roadid, sur.sequenceid) as aaa"
, sqlConnection);
try
{
sql_result = command.ExecuteScalar();
finished_X_yearLength[i] = (sql_result == null || DBNull.Value.Equals(sql_result)) ? 0 : (double)sql_result;
}
catch (Exception e)
{
e.ToString();
throw;
}
unfinished_X_yearLength[i] = (Convert.ToInt32(totalLength) - finished_X_yearLength[i] - (int)uninitiatedLength);
}
command = new SqlCommand("select count(inventid) from " + databaseName + ".segments as seg where seg.placeid=" + placeID +"AND seg.isDeleted = 'false'", sqlConnection);
try
{
sql_result = command.ExecuteScalar();
}
catch (Exception e)
{
e.ToString();
throw;
}
int segment_Count = (sql_result == null || DBNull.Value.Equals(sql_result)) ? 0 : (int)sql_result;
command = new SqlCommand("select count(id) from " + databaseName + ".segmentsurveys as sur where sur.placeid=" + placeID, sqlConnection);
try
{
sql_result = command.ExecuteScalar();
}
catch (Exception e)
{
e.ToString();
throw;
}
int survey_Count = (sql_result == null || DBNull.Value.Equals(sql_result)) ? 0 : (int)sql_result;
// End of Step 3
DashBoardViewModel results = new DashBoardViewModel();
results.TotalLength = Convert.ToInt32(totalLength);
int[] unfinished_X_yearLength_int = new int[unfinished_X_yearLength.Length];
for (int i = 0; i < unfinished_X_yearLength.Length; i++)
{
unfinished_X_yearLength_int[i] = (int)unfinished_X_yearLength[i];
}
results.Unfinished_X_yearLength = unfinished_X_yearLength_int;
results.pct = pctcompletion;
results.PlaceID = placeID;
results.Segments_Count = segment_Count;
results.UninitiatedLength = (int)uninitiatedLength;
int[] finished_X_yearLength_int = new int[finished_X_yearLength.Length];
for (int i = 0; i < finished_X_yearLength.Length; i++)
{
finished_X_yearLength_int[i] = (int)finished_X_yearLength[i];
}
results.Finished_X_yearLength = finished_X_yearLength_int;
results.surveysCount = survey_Count;
sqlConnection.Close();
return results;
}