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