public void generalReport(object sender, EventArgs e)
{
DataTable general = addColumns();
string thisSql = moduleRoads.GetSelectAllSQL();
try
{
DataTable resultsTable = Database.GetDataByQuery(Project.conn, thisSql);
foreach (DataRow row in resultsTable.Rows)
{
DataRow nr = general.NewRow();
addRows(nr, row);
general.Rows.Add(nr);
}
general.DefaultView.Sort = "Name asc, Treatment asc, From Address asc";
general = general.DefaultView.ToTable();
FormOutput report = new FormOutput(Project, moduleRoads);
report.dataGridViewReport.DataSource = general;
report.Text = "Treatment Report";
report.Show();
}
catch (Exception err)
{
Log.Error("Could not get database values for " + ModuleName + " module.\n" + err.ToString());
MessageBox.Show("An error has occured while trying to consolidate data.");
}
}
private void makeTypeGraph(string[] roadTypes, string column, string title, Color[] c = null)
{
string thisSql = moduleRoads.GetSelectAllSQL();
try
{
DataTable roadTable = Database.GetDataByQuery(Project.conn, thisSql);
if (roadTable.Rows.Count == 0)
{
MessageBox.Show("No graph could be generated because no roads have a road type set.", "No Roads", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
Dictionary <string, double> roadArea = new Dictionary <string, double>();
for (int i = 0; i < roadTypes.Length; i++)
{
roadArea.Add(roadTypes[i], 0.0);
}
double totalArea = 0.0;
foreach (DataRow row in roadTable.Rows)
{
for (int i = 0; i < roadTypes.Length; i++)
{
if (row[column].ToString().Contains(roadTypes[i]))
{
totalArea += Util.ToDouble(row["length"].ToString()) * Util.ToDouble(row["width"].ToString());
roadArea[roadTypes[i]] += Util.ToDouble(row["length"].ToString()) * Util.ToDouble(row["width"].ToString());
}
}
}
DataTable results = new DataTable();
results.Columns.Add("Distribution");
for (int i = 0; i < roadTypes.Length; i++)
{
results.Columns.Add(Util.UppercaseFirst(roadTypes[i]));
}
DataRow totalsRow = results.NewRow();
DataRow percentageRow = results.NewRow();
totalsRow["Distribution"] = "Area (sqr. ft.)";
percentageRow["Distribution"] = "Percentage";
string[] domain = new string[roadTypes.Length];
double[] range = new double[roadTypes.Length];
for (int i = 0; i < roadTypes.Length; i++)
{
totalsRow[Util.UppercaseFirst(roadTypes[i])] = roadArea[roadTypes[i]];
percentageRow[Util.UppercaseFirst(roadTypes[i])] = Math.Round(roadArea[roadTypes[i]] / totalArea, 3) * 100;
domain[i] = roadTypes[i];
range[i] = Math.Round(roadArea[roadTypes[i]] / totalArea, 3) * 100;
}
results.Rows.Add(totalsRow);
results.Rows.Add(percentageRow);
FormGraphDisplay graph = new FormGraphDisplay(results, domain, range, title, c);
graph.Show();
}
catch (Exception err)
{
Log.Error("Problem getting data from database " + err.ToString());
}
}
private void roadReport(DataTable report)
{
string thisSql = moduleRoads.GetSelectAllSQL();
DataTable fullDataSet = Database.GetDataByQuery(Project.conn, thisSql);
string sql = "";
foreach (DataColumn col in report.Columns)
{
foreach (DataRow row in report.Rows)
{
string column = "";
string thisCol = col.ToString();
if (thisCol == "ID")
{
column = "TAMSID";
}
else if (thisCol == "Name")
{
column = "name";
}
else if (thisCol == "Speed Limit")
{
column = "speed_limit";
}
else if (thisCol == "Lanes")
{
column = "lanes";
}
else if (thisCol == "Width (ft)")
{
column = "width";
}
else if (thisCol == "Length (ft)")
{
column = "length";
}
else if (thisCol == "From Address")
{
column = "from_address";
}
else if (thisCol == "To Address")
{
column = "to_address";
}
else if (thisCol == "Surface")
{
column = "surface";
}
else if (thisCol == "Treatment")
{
column = "suggested_treatment";
}
else if (thisCol == "RSL")
{
column = "rsl";
}
else if (thisCol == "Functional Classification")
{
column = "type";
}
else if (thisCol == "Survey Date")
{
column = "survey_date";
}
else if (thisCol == "Photo")
{
column = "photo";
}
else if (thisCol == "Fat/Spa/Pot")
{
column = "distress1";
}
else if (thisCol == "Edg/Joi/Rut")
{
column = "distress2";
}
else if (thisCol == "Lon/Cor/X-S")
{
column = "distress3";
}
else if (thisCol == "Pat/Bro/Dra")
{
column = "distress4";
}
else if (thisCol == "Pot/Fau/Dus")
{
column = "distress5";
}
else if (thisCol == "Dra/Lon/Agg")
{
column = "distress6";
}
else if (thisCol == "Tra/Tra/Cor")
{
column = "distress7";
}
else if (thisCol == "Block/Crack")
{
column = "distress8";
}
else if (thisCol == "Rutti/Patch")
{
column = "distress9";
}
else
{
continue;
}
string currentID = row["ID"].ToString();
if (String.IsNullOrEmpty(currentID))
{
currentID = null;
}
if (currentID == null)
{
continue;
}
string newValue = row[col].ToString();
if (String.IsNullOrEmpty(newValue))
{
newValue = null;
}
bool valuePresent = false;
string searchDataSet = "TAMSID = null";
if (!(currentID == null))
{
searchDataSet = "TAMSID = " + row["ID"].ToString();
}
DataRow[] existingRow = fullDataSet.Select(searchDataSet);
foreach (DataRow dr in existingRow)
{
string oldValue = dr[column].ToString();
if (String.IsNullOrEmpty(oldValue))
{
oldValue = null;
}
if (oldValue == newValue)
{
valuePresent = true;
continue;
}
}
if (valuePresent)
{
continue;
}
sql += "UPDATE road SET " + column + " = \"" + newValue + "\" WHERE TAMSID = " + currentID + ";";
}
}
try
{
Database.ExecuteNonQuery(Project.conn, sql);
}
catch
{
Cursor.Current = Cursors.Arrow;
MessageBox.Show("Make sure the column names match each of the column names found in the 'general report.'", "Error: Save Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}