//populate the race comboBox public void LoadComboBox() { //empty out the comboBox comboBox1.Items.Clear(); //load the names of the races from file var races = new DataTable(); CommonSQL.BuildIfNotExsistDB(); using (var conn = new SQLiteConnection(CommonSQL.SQLiteConnectionString)) { conn.Open(); using (var cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandText = "select Name from Race;"; SQLiteDataReader r = cmd.ExecuteReader(); var daRaces = new SQLiteDataAdapter(cmd); while (r.Read()) { this.comboBox1.Items.Add(r[0]); } } conn.Close(); } //if there is data, load the first value if (comboBox1.Items.Count > 0) { comboBox1.SelectedIndex = 0; } }
//remove row from runners table private void DataGridViewRunnerRowDel(object sender, DataGridViewRowCancelEventArgs e) { string first = dataGridRunners["FirstName", e.Row.Index].Value.ToString(); string last = dataGridRunners["LastName", e.Row.Index].Value.ToString(); var parts = dataGridRunners["DOB", e.Row.Index].Value.ToString().Split('-'); DateTime dob = new DateTime(Int32.Parse(parts[0]), Int32.Parse(parts[1]), Int32.Parse(parts[2])); CommonSQL.DelRunner(first, last, dob, raceData.RaceID); }
static void Main() { CommonSQL.BuildIfNotExsistDB(); Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new StartScreen()); //Class1.test(); }
//temp... this is to ensure that the parent window (StartScreen) is also closed private void MainWindow_FormClosing(object sender, FormClosingEventArgs e) { if (raceData.StartWindow != null) { this.Dispose(); raceData.StartWindow.Close(); } //One last backup of the database is called when user closes form CommonSQL.BackupDB(); }
//record bib and time to db public void RecordTime(string bib, string time) { if (_raceID < 0) { throw new Exception("No Race set!"); } CommonSQL.AddTimeAndBib(_raceID, bib, time); foreach (ITimerListener obs in _listeners) { obs.OnTime(); } }
//pre-pop with runner data and get runner ID (this will preform an update) public NewUserWindow(RaceData _raceData, MainWindow _parent, string firstName, string lastName, DateTime dob, string bibID, char sex, string team, string org) : this(_raceData, _parent) { textBoxFirstName.Text = firstName; textBoxLastName.Text = lastName; dateTimePicker1.Value = dob; textBoxBibId.Text = bibID; textBoxTeam.Text = team; textBoxOrginization.Text = org; tbGender.Text = sex + ""; _RunnerID = CommonSQL.GetRunnerID(firstName, lastName, dob); }
//updates an exsisting runners info based on runnerID private void updateRunner() { if (!CommonSQL.BibExistOutsideRunner(textBoxBibId.Text, raceData.RaceID, _RunnerID)) { CommonSQL.UpdateRunner(_RunnerID, textBoxFirstName.Text, textBoxLastName.Text, Convert.ToDateTime(dateTimePicker1.Value.ToShortDateString()), textBoxBibId.Text, tbGender.Text, textBoxTeam.Text, textBoxOrginization.Text, raceData.raceName, raceData.connectionString); if (parent != null) { parent.Reload(); } CommonSQL.BackupDB(); this.Close(); } else { MessageBox.Show("Can not add: Duplicate bib"); } }
//highlight duplicated bibs private void HighlightTimingErrors() { //find bad data in the table var test = CommonSQL.FindBadBibs(raceData.RaceID); //look through visable cells if (test.Count > 0) { for (int rowIndex = 0; rowIndex <= dataGridTiming.RowCount - 1; rowIndex++) { var cells = dataGridTiming.Rows[rowIndex].Cells; foreach (DataGridViewCell cell in cells) { if (test.Contains(cell.Value.ToString())) { cell.Style.BackColor = Color.Red; } } } } }
//validateds and updates changes in the timing table private void TimingTableCellChanging(object sender, DataGridViewCellValidatingEventArgs e) { if (!timingTableUpdating) { var oldValue = dataGridTiming[e.ColumnIndex, e.RowIndex].Value.ToString(); var newValue = e.FormattedValue.ToString(); timingCellBeingEdited = false; if (oldValue != newValue) { if (e.ColumnIndex == 1)//if we are changing the bib { CommonSQL.UpdateTimingBib(raceData.RaceID, oldValue, dataGridTiming[2, e.RowIndex].Value.ToString(), newValue); } else if (e.ColumnIndex == 2)//if we are changing the time { CommonSQL.UpdateTimingTime(raceData.RaceID, dataGridTiming[1, e.RowIndex].Value.ToString(), oldValue, newValue); } else { MessageBox.Show("That value can not be edited"); } } } }
//remove row from timing table private void DataGridViewTimingRowDel(object sender, DataGridViewRowCancelEventArgs e) { CommonSQL.DelTimingRow(raceData.RaceID, dataGridTiming[2, e.Row.Index].Value.ToString()); }
//take excel file and import runner data from that file private async void AddUsersToRace(String filename) { int firstNameRow = -1; int lastNameRow = -1; int bibIDRow = -1; int dobRow = -1; int genderRow = -1; int teamRow = -1; int orgRow = -1; int shirtRow = -1; int ageRow = -1; int curRow = 1; try { Cursor.Current = Cursors.WaitCursor; lblProgress.Text = "Scanning Excel document..."; importProgressPanel.Visible = true; var excelApp = new Excel.Application(); var workbook = excelApp.Workbooks.Open(filename); var worksheet = workbook.Worksheets[1] as Excel.Worksheet;//first page Excel.Range range = worksheet.UsedRange as Excel.Range; //parse the first line to get the positions of each column int i = 1; while (range.Cells[1, i].Value2 != null && range.Cells[1, i].Value2 + "" != "") { string title = range.Cells[1, i].Value2 + ""; switch (title.ToLower()) { case "bibid": case "bib #": bibIDRow = i; break; case "firstname": case "first name": firstNameRow = i; break; case "lastname": case "last name": lastNameRow = i; break; case "dob": dobRow = i; break; case "gender": genderRow = i; break; case "organization": orgRow = i; break; case "team": teamRow = i; break; case "shirt": shirtRow = i; break; case "age": ageRow = i; break; default: break; } i++; } if (workbook.Worksheets.Count > 0) { int rowCount = range.Rows.Count; int colCount = range.Columns.Count; if (comboBox1.SelectedItem == null) { MessageBox.Show("No Race Selected"); return; } string race = this.comboBox1.SelectedItem.ToString(); if (rowCount > 1 && colCount > 4)//minimum valid input { //curRo-1 becouse the first row is headers var FirstNames = new string[rowCount - 1]; var LastNames = new string[rowCount - 1]; var DOBs = new DateTime[rowCount - 1]; var Genders = new char[rowCount - 1]; var BibIDs = new string[rowCount - 1]; var Orginizations = new string[rowCount - 1]; var Teams = new string[rowCount - 1]; Dictionary <string, int> dictionary = new Dictionary <string, int>(); string errorMessage = "Full Error log at " + LogFile.PATH + "\r\n"; int errrorThreashold = errorMessage.Length; if (firstNameRow < 1 || lastNameRow < 1 || bibIDRow < 1 || (dobRow < 1 && ageRow < 1)) { MessageBox.Show("Some columns not found. Need to see: FirstName, LastName, BibId, and either DOB or age"); return; } for (curRow = 2; curRow <= rowCount; curRow++) { FirstNames[curRow - 2] = range.Cells[curRow, firstNameRow].Value2 as string; LastNames[curRow - 2] = range.Cells[curRow, lastNameRow].Value2 as string; if (dobRow > 0 && range.Cells[curRow, dobRow].Value2 != null) { DOBs[curRow - 2] = DateTime.FromOADate(range.Cells[curRow, dobRow].Value2); } else if (ageRow > 0) { int age = 0; bool succesful = false; var celVal = range.Cells[curRow, ageRow].Value2; if (celVal != null) { succesful = Int32.TryParse(celVal.ToString(), out age); } if (succesful) { DOBs[curRow - 2] = DateTime.Now.AddYears(-age); } else { DOBs[curRow - 2] = DateTime.Now; string date = string.Format("{0:G}", DateTime.Now); string dobError = "Runner " + FirstNames[curRow - 2] + " " + LastNames[curRow - 2] + " could not read age!"; LogFile.WriteToErrorLog(dobError + " " + date + "\r\n"); errorMessage += dobError + "\r\n"; } } //check to make sure that the dob is in the past, allow them to be entered but warn user if (DOBs[curRow - 2] > DateTime.Now) { string date = string.Format("{0:G}", DateTime.Now); string birthError = "Runner " + FirstNames[curRow - 2] + " " + LastNames[curRow - 2] + " has not been born yet!"; LogFile.WriteToErrorLog(birthError + " " + date + "\r\n"); errorMessage += birthError + "\r\n"; } //might be male, might be female, just take first letter string temp = "N"; if (genderRow > 0) { temp = range.Cells[curRow, genderRow].Value2 as string; } Genders[curRow - 2] = (temp != null && temp.Length > 0)? temp.ToUpper().ToCharArray()[0]: 'N'; string BibID = range.Cells[curRow, bibIDRow].Value2.ToString() ?? ""; if (!dictionary.ContainsKey(BibID)) { dictionary.Add(BibID, 1); BibIDs[curRow - 2] = range.Cells[curRow, bibIDRow].Value2.ToString() ?? ""; } else { string date = string.Format("{0:G}", DateTime.Now); string dateError = "Duplicate bib #" + BibID + " found. "; LogFile.WriteToErrorLog(dateError + date + "\r\n"); errorMessage += dateError + "\r\n"; } if (teamRow > 0) { Teams[curRow - 2] = range.Cells[curRow, teamRow].Value2 as string ?? ""; } if (orgRow > 0) { Orginizations[curRow - 2] = range.Cells[curRow, orgRow] as string ?? ""; } } if (errorMessage.Length > errrorThreashold) { MessageBox.Show(errorMessage); } workbook.Close(); //CommonSQL.AddRunners(FirstNames, LastNames, DOBs, BibIDs, Teams, Orginizations, race, CommonSQL.SQLiteConnection); var progress = new Progress <ProgressReport>(); lblProgress.Text = "Importing..."; //updates the progress bar progress.ProgressChanged += (o, report) => { progressBar1.Value = report.PercentComplete; progressBar1.Update(); }; //keep user from messing things up LockGUI(true); //waits for runners to be inserted asynchrously await CommonSQL.ProcessRunners(FirstNames, LastNames, DOBs, Genders, BibIDs, Teams, Orginizations, race, CommonSQL.SQLiteConnectionString, progress); //re-enable user interaction LockGUI(false); } lblProgress.Text = "Import complete for " + race; progressBar1.Value = 0; } else { MessageBox.Show("No Valid Data to Import!"); } } catch (Exception e) { MessageBox.Show("Error occured at row: " + curRow + e.Message); } finally { this.UseWaitCursor = false; importProgressPanel.Visible = false; } }