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."); } }