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; }