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 showReport(DataTable data, string name)
{
data.DefaultView.Sort = "Support ID asc";
FormOutput report = new FormOutput(Project, null, name);
report.dataGridViewReport.DataSource = data.DefaultView.ToTable();
report.Text = name;
report.Show();
}
public void potholeReport(object sender, EventArgs e)
{
string[] pd = { "less than 1\"", "less than 2\"", "more than 2\"" };
string[] pq = { "less than 2", "less than 5", "more than 5" };
int Integer = 0;
Type typeInt = Integer.GetType();
DataTable potholes = new DataTable("Potholes");
potholes.Columns.Add("ID", typeInt);
potholes.Columns.Add("Name");
potholes.Columns.Add("From Address");
potholes.Columns.Add("To Address");
potholes.Columns.Add("Depth");
potholes.Columns.Add("Quantity");
potholes.Columns.Add("Treatment");
potholes.Columns.Add("Photo");
string thisSql = moduleRoads.GetSelectAllSQL();
try
{
DataTable resultsTable = Database.GetDataByQuery(Project.conn, thisSql);
if (resultsTable.Rows.Count == 0)
{
MessageBox.Show("No list could be generated because no roads with potholes where found.");
return;
}
foreach (DataRow row in resultsTable.Rows)
{
if (Util.ToInt(row["distress5"].ToString()) <= 0)
{
continue;
}
DataRow nr = potholes.NewRow();
nr["ID"] = row["TAMSID"];
nr["Name"] = row["name"];
nr["From Address"] = row["from_address"];
nr["To Address"] = row["to_address"];
nr["Depth"] = (Util.ToInt(row["distress5"].ToString()) > 0 ? pd[(Util.ToInt(row["distress5"].ToString()) - 1) / 3] : "None");
nr["Quantity"] = (Util.ToInt(row["distress5"].ToString()) > 0 ? pq[(Util.ToInt(row["distress5"].ToString()) - 1) % 3] : "None");
nr["Treatment"] = row["suggested_treatment"];
nr["Photo"] = row["photo"];
potholes.Rows.InsertAt(nr, potholes.Rows.Count);
}
potholes.DefaultView.Sort = "Name asc, From Address asc";
FormOutput report = new FormOutput(Project, moduleRoads);
report.dataGridViewReport.DataSource = potholes.DefaultView.ToTable();
report.Text = "Potholes 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 customRoadReport(FormQueryBuilder tableFilters)
{
bool selectResults = false;
string surfaceType = tableFilters.getSurface();
string query = tableFilters.getQuery();
if (tableFilters.checkBoxSelectResults.Checked && query != "SELECT * FROM road")
{
selectResults = true;
}
query += " GROUP BY TAMSID ORDER BY TAMSID ASC, survey_date DESC;";
DataTable results = Database.GetDataByQuery(Project.conn, query);
if (results.Rows.Count == 0)
{
MessageBox.Show("No roads matching the given description were found.");
return;
}
DataTable outputTable = roadReports.addColumns(surfaceType);
FormOutput report = new FormOutput(Project, moduleRoads);
FeatureLayer selectionLayer = (FeatureLayer)moduleRoads.Layer;
selectionLayer.ClearSelection();
foreach (DataRow row in results.Rows)
{
if (selectResults)
{
String tamsidcolumn = Project.settings.GetValue("road_f_TAMSID");
selectionLayer.SelectByAttribute(tamsidcolumn + " = " + row["TAMSID"], ModifySelectionMode.Append);
}
DataRow nr = outputTable.NewRow();
string note = row["notes"].ToString().Split(new[] { '\r', '\n' }).FirstOrDefault(); //retrive most recent note
int oldNoteLength = note.Length;
int maxLength = 17;
if (!string.IsNullOrEmpty(note))
{
note = note.Substring(0, Math.Min(oldNoteLength, maxLength));
if (note.Length == maxLength)
{
note += "...";
}
}
roadReports.addRows(nr, row, surfaceType);
outputTable.Rows.Add(nr);
}
report.dataGridViewReport.DataSource = outputTable;
report.Text = "Treatment Report";
report.Show();
if (selectResults)
{
moduleRoads.selectionChanged();
}
}
private void createReport(string query, Dictionary <string, string> mapping, string sortKey = "ID", string name = "")
{
DataTable outputTable = new DataTable();
foreach (string key in mapping.Keys)
{
if (key == "ID")
{
int Integer = 0;
Type typeInt = Integer.GetType();
outputTable.Columns.Add(key, typeInt);
}
else
{
outputTable.Columns.Add(key);
}
}
try
{
DataTable results = Database.GetDataByQuery(Project.conn, query);
if (results.Rows.Count == 0)
{
MessageBox.Show("No list could be generated because no " + name + " were found.");
return;
}
foreach (DataRow row in results.Rows)
{
DataRow nr = outputTable.NewRow();
foreach (string key in mapping.Keys)
{
if (key == "Notes")
{
nr[key] = truncateNote(row[mapping[key]]);
}
else
{
nr[key] = row[mapping[key]];
}
}
outputTable.Rows.Add(nr);
}
outputTable.DefaultView.Sort = sortKey + " asc";
FormOutput report = new FormOutput(Project, null, name);
report.dataGridViewReport.DataSource = outputTable.DefaultView.ToTable();
report.Text = name + " Report";
report.Show();
}
catch (Exception e)
{
Log.Error("Could not get data from database " + Environment.NewLine + e.ToString());
}
}
public FormOutput CreateNewOutputWindow(string strTitle, NLog.Logger logger, bool bShowTaskStaListview)
{
try
{
FormOutput form = null;
form = new FormOutput(strTitle, logger, bShowTaskStaListview);
form.Show(dockPanel1);
return(form);
}
catch (Exception)
{
}
return(null);
}
protected void createReport(string query, Dictionary <string, string> mapping, string sortKey = "ID", string things = "signs")
{
DataTable outputTable = new DataTable();
foreach (string key in mapping.Keys)
{
outputTable.Columns.Add(key);
}
try
{
DataTable results = Database.GetDataByQuery(Project.conn, query);
if (results.Rows.Count == 0)
{
MessageBox.Show("No list could be generated because no " + things + " were found.");
return;
}
foreach (DataRow row in results.Rows)
{
DataRow nr = outputTable.NewRow();
foreach (string key in mapping.Keys)
{
nr[key] = row[mapping[key]];
}
outputTable.Rows.Add(nr);
}
outputTable.DefaultView.Sort = sortKey + " asc";
FormOutput report = new FormOutput();
report.dataGridViewReport.DataSource = outputTable.DefaultView.ToTable();
report.Text = "Report";
report.Show();
}
catch (Exception e)
{
Log.Error("Could not get data from database " + Environment.NewLine + e.ToString());
}
}
private void importCSVToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog openCSV = new OpenFileDialog();
openCSV.Filter = "CSV Files|*.csv";
openCSV.Title = "Select a Comma Separtated Value File";
if (openCSV.ShowDialog() == DialogResult.OK)
{
System.IO.StreamReader sr = null;
try
{
sr = new System.IO.StreamReader(openCSV.FileName);
}
catch
{
MessageBox.Show("Failed to import file. Make sure the file is of type 'CSV' and is not open in any other application", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string importedCSV = sr.ReadToEnd();
sr.Close();
DataTable importedTable = new DataTable();
string[] rows = importedCSV.Split('\n');
string[] cols = rows[0].Split(',');
foreach (string column in cols)
{
if (column == "TAMSID")
{
importedTable.Columns.Add("ID", typeof(string));
continue;
}
importedTable.Columns.Add(column, typeof(string));
}
int i = -1;
foreach (string row in rows)
{
i++;
if (i == 0)
{
continue;
}
string[] thisRow = row.Split(',');
importedTable.Rows.Add(thisRow);
}
FormImportReport importReport = new FormImportReport();
importReport.ShowDialog();
string reportType = importReport.comboBoxReportType.Text;
if (importReport.cancel)
{
return;
}
string updateList = "";
if (reportType == "Road")
{
updateList = "\n\t\t ID" +
"\n\t\t Name" +
"\n\t\t Speed Limit" +
"\n\t\t Lanes" +
"\n\t\t Width (ft)" +
"\n\t\t Length (ft)" +
"\n\t\t From Addres" +
"\n\t\t To Address" +
"\n\t\t Surface" +
"\n\t\t Treatment" +
"\n\t\t RSL" +
"\n\t\t Functional Classification" +
"\n\t\t Survey Date" +
"\n\t\t Fat/Spa/Pot" +
"\n\t\t Edg/Joi/Rut" +
"\n\t\t Lon/Cor/X-S" +
"\n\t\t Pat/Bro/Dra" +
"\n\t\t Pot/Fau/Dus" +
"\n\t\t Dra/Lon/Agg" +
"\n\t\t Tra/Tra/Cor" +
"\n\t\t Block/Crack" +
"\n\t\t Rutti/Patch" +
"\n\nColumns such as 'Cost' and 'Area' are computed when a table is generated. ";
}
else if (reportType == "Sign Inventory")
{
updateList = "\n\t\t ID" +
"\n\t\t Support ID" +
"\n\t\t Description" +
"\n\t\t Sign Text" +
"\n\t\t Condition" +
"\n\t\t Recommendation" +
"\n\t\t Reflectivity" +
"\n\t\t Sheeting" +
"\n\t\t Backing" +
"\n\t\t Height (in)" +
"\n\t\t Width (in)" +
"\n\t\t Mount Height (ft)" +
"\n\t\t Direction" +
"\n\t\t Category" +
"\n\t\t Favorite" +
"\n\t\t MUTCD Code" +
"\n\t\t Install Date" +
"\n\t\t Survey Date \n\n";
}
else if (reportType == "Sign Recommendations")
{
updateList = "\n\t\t ID" +
"\n\t\t Support ID" +
"\n\t\t Address" +
"\n\t\t Recommendation" +
"\n\t\t Survey Date \n\n";
}
else if (reportType == "Support Inventory")
{
updateList = "\n\t\t Support ID" +
"\n\t\t Address" +
"\n\t\t Material" +
"\n\t\t Condition" +
"\n\t\t Obstructions" +
"\n\t\t Recommendation" +
"\n\t\t Road Offset (ft)" +
"\n\t\t Height (ft)" +
"\n\t\t Category" +
"\n\t\t Survey Date \n\n";
}
else if (reportType == "Support Recommendations")
{
updateList = "\n\t\t Support ID" +
"\n\t\t Address" +
"\n\t\t Recommendation" +
"\n\t\t Survey Date \n\n";
}
else if (reportType == "Roads with Sidewalks")
{
updateList = "\n\t\t ID" +
"\n\t\t Sidewalks" +
"\n\t\t Comments \n\n";
}
else
{
updateList = "\n\t\t ID" +
"\n\t\t Address" +
"\n\t\t Description";
switch (reportType)
{
case "Sidewalks":
updateList += "\n\t\t Faults" +
"\n\t\t Breaks" +
"\n\t\t Recommendation";
break;
case "Severe Road Distresses":
updateList += "\n\t\t Distress" +
"\n\t\t Recommendation";
break;
case "ADA Ramps":
updateList += "\n\t\t Condition" +
"\n\t\t Compliant" +
"\n\t\t Has Tiles";
break;
case "Drainage Problems":
updateList += "\n\t\t Type" +
"\n\t\t Recommendation";
break;
case "Accident":
updateList += "\n\t\t Date" +
"\n\t\t Type" +
"\n\t\t Severity";
break;
case "Objects":
updateList += "\n\t\t Property 1" +
"\n\t\t Property 2";
break;
}
updateList += "\n\n";
}
FormOutput report = new FormOutput(Project, road, reportType);
report.dataGridViewReport.DataSource = importedTable;
report.Text = "Imported Report";
report.Show();
MessageBox.Show("Check to make sure the table was imported correctly. " +
"Only columns with following headings will be updated:\n" + updateList +
"Notes will not be updated because they would be overwritten by the abbreviated note. Save changes if you want to keep them.",
"Importing " + reportType + " CSV", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void showHistory(object sender, EventArgs e)
{
FeatureLayer selectionLayer = (FeatureLayer)moduleRoads.Layer;
ISelection shpSelection = selectionLayer.Selection;
DataTable selectionTable = shpSelection.ToFeatureSet().DataTable;
string histring = @"SELECT * FROM road WHERE TAMSID IN (" + moduleRoads.extractTAMSIDs(selectionTable) + ") ORDER BY TAMSID ASC, id DESC;";
try
{
DataTable history = Database.GetDataByQuery(Project.conn, histring);
history.Columns["id"].ColumnName = "ID";
history.Columns["survey_date"].ColumnName = "Survey Date";
history.Columns["name"].ColumnName = "Name";
history.Columns["speed_limit"].ColumnName = "Speed Limit";
history.Columns["lanes"].ColumnName = "Lanes";
history.Columns["width"].ColumnName = "Width";
history.Columns["length"].ColumnName = "Length";
history.Columns["surface"].ColumnName = "Surface";
history.Columns["type"].ColumnName = "Functional Classification";
history.Columns["from_address"].ColumnName = "From Address";
history.Columns["to_address"].ColumnName = "To Address";
history.Columns["photo"].ColumnName = "Photo";
history.Columns["rsl"].ColumnName = "RSL";
history.Columns["suggested_treatment"].ColumnName = "Suggested Treatment";
history.Columns["notes"].ColumnName = "Notes";
history.Columns["photo"].ColumnName = "Photo";
int surface_id = 0;
string surface_type = history.Rows[0]["surface"].ToString();
if (surface_type == "asphalt")
{
surface_id = 1;
}
if (surface_type == "gravel")
{
surface_id = 2;
history.Columns.Remove("distress8");
history.Columns.Remove("distress9");
}
if (surface_type == "concrete")
{
surface_id = 3;
}
DataTable distresses = Database.GetDataByQuery(Project.conn, "SELECT name, dbkey FROM road_distresses WHERE surface_id = " + surface_id.ToString());
for (int i = 0; i < distresses.Rows.Count; i++)
{
string distressNumber = distresses.Rows[i]["dbkey"].ToString();
history.Columns[distressNumber].ColumnName = distresses.Rows[i]["name"].ToString();
}
reportTable = history.DefaultView.ToTable();
FormOutput histForm = new FormOutput(Project, moduleRoads);
histForm.Text = "Road History";
histForm.dataGridViewReport.DataSource = history;
histForm.Show();
}
catch (Exception err)
{
Log.Error("Malformed request " + err.ToString());
MessageBox.Show("An error occured when attempting to show history. Roads Database may be corrupted.");
}
}
public void reportSelected(object sender, EventArgs e)
{
DataTable general = addColumns();
FeatureLayer selectionLayer = (FeatureLayer)moduleRoads.Layer;
ISelection shpSelection = selectionLayer.Selection;
DataTable selectionTable = shpSelection.ToFeatureSet().DataTable;
string thisSql = moduleRoads.SelectionSql.Replace("[[IDLIST]]", moduleRoads.extractTAMSIDs(selectionTable));
try
{
DataTable selectedResultsTable = Database.GetDataByQuery(Project.conn, thisSql);
double totalCost = 0;
double totalArea = 0;
foreach (DataRow row in selectedResultsTable.Rows)
{
DataRow nr = general.NewRow();
addRows(nr, row);
general.Rows.Add(nr);
string costStr = nr["Cost"].ToString();
if (costStr[costStr.Length - 1] == 'k')
{
totalCost += Util.ToDouble(costStr.Remove(costStr.Length - 1)) * 1000;
}
else if (costStr[costStr.Length - 1] == 'M')
{
totalCost += Util.ToDouble(costStr.Remove(costStr.Length - 1)) * 1000000;
}
else
{
totalCost += Util.ToDouble(costStr);
}
totalArea += Util.ToDouble(nr["Area (yds\u00b2)"].ToString());
}
general.DefaultView.Sort = "Name asc, Treatment asc, From Address asc";
general = general.DefaultView.ToTable();
DataRow totals = general.NewRow();
totals["Treatment"] = "Total";
if (totalCost > 1000000)
{
totals["Cost"] = Math.Round(totalCost / 1000000, 2).ToString() + "M";
}
else if (totalCost > 1000)
{
totals["Cost"] = Math.Round(totalCost / 1000).ToString() + "k";
}
else
{
totals["Cost"] = Math.Round(totalCost).ToString();
}
totals["Area (yds\u00b2)"] = totalArea / 9;
general.Rows.Add(totals);
reportTable = general.DefaultView.ToTable();
FormOutput report = new FormOutput(Project, moduleRoads);
report.dataGridViewReport.DataSource = reportTable;
report.Text = "Treatment Report";
report.Show();
report.FormClosing += updateChanges;
}
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.");
}
}
