private void comboBox1_SelectedValueChanged(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
try
{
SqlCommand command = new SqlCommand("SELECT DISTINCT EventType.EventTypeName, Users.FirstName, Users.LastName, RegistrationEvent.RaceTime, RegistrationEvent.RegistrationEventId, Registration.SponsorshipTarget, EventType.EventTypeName, Registration.RegistrationId FROM Users, Runner, Registration, RegistrationEvent, Event, EventType, Marathon WHERE Users.Email = Runner.Email AND Runner.RunnerId = Registration.RunnerId AND RegistrationEvent.RegistrationId = Registration.RegistrationId AND RegistrationEvent.EventId = Event.EventId AND Event.MarathonId = 4 AND EventType.EventTypeId = Event.EventTypeId AND Users.Email = '" + runnerEmail + "' AND EventType.EventTypeId = '" + (comboBox1.SelectedItem as ComboBoxItem).value + "' AND RegistrationEvent.RaceTime is not null", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.HasRows)
{
TimeSpan ts = TimeSpan.FromSeconds(Convert.ToDouble(reader["RaceTime"]));
label8.Text = "Поздравляем " + reader["LastName"].ToString() + " " + reader["FirstName"].ToString() + " с участием " + reader["EventTypeName"].ToString() + "! Ваши\n результаты " + ts.Hours + "hh " + ts.Minutes + "mm " + ts.Seconds + "ss и занятое место " + reader["RegistrationEventId"].ToString() + "!";
label7.Text = "Вы также заработали $" + reader["SponsorshipTarget"].ToString() + "\n для своей благотворительной организации!";
}
else
{
panel1.Visible = false;
MessageBox.Show("У бегуна отсутствуют результаты за Marathon Skills 2014, Osaka, Japan!", "Оповещение системы");
}
}
}
}
catch (Exception ex)
{
}
}
private void FormAddEditCharity_Load(object sender, EventArgs e)
{
timer1.Start();
openFileDialog1.Filter = "Image files | *.jpg;*.jpeg;*.png;*.gif;*.bmp;";
if (charityId != null)
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Charity WHERE CharityId = '" + charityId + "'", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["CharityLogo"].ToString() != "")
{
pictureBox2.Image = Image.FromFile("logo/" + reader["CharityLogo"].ToString());
textBox2.Text = reader["CharityLogo"].ToString();
}
textBox1.Text = reader["CharityName"].ToString();
richTextBox1.Text = reader["CharityDescription"].ToString();
}
}
}
}
private void FormEditUser_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand commandRole = new SqlCommand("SELECT * FROM Role", conn);
using (SqlDataReader reader = commandRole.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["RoleName"].ToString();
item.value = reader["RoleId"].ToString();
comboBox1.Items.Add(item);
}
}
SqlCommand command = new SqlCommand("SELECT Users.FirstName, Users.LastName, Users.Email, Role.RoleName FROM Users, Role WHERE Users.RoleId = Role.RoleId AND Users.Email = '" + userEmail + "'", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
label6.Text = reader["Email"].ToString();;
textBox4.Text = reader["LastName"].ToString();
textBox5.Text = reader["FirstName"].ToString();
comboBox1.Text = reader["RoleName"].ToString();
}
}
}
private void FormEditRunnerProfile_Load(object sender, EventArgs e)
{
timer1.Start();
openFileDialog1.Filter = "Image Files |*.jpg;*.png;*gif;";
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand commandGender = new SqlCommand("SELECT * FROM Gender", conn);
using (SqlDataReader reader = commandGender.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["Gender"].ToString();
item.value = reader["Gender"].ToString();
comboBox1.Items.Add(item);
}
}
SqlCommand commandCountry = new SqlCommand("SELECT * FROM Country ORDER BY CountryName", conn);
using (SqlDataReader reader = commandCountry.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["CountryName"].ToString();
item.value = reader["CountryCode"].ToString();
comboBox2.Items.Add(item);
}
}
SqlCommand command = new SqlCommand("SELECT Users.FirstName, Users.LastName, Country.CountryName, Runner.Gender, Runner.DateOfBirth, Users.Picture FROM Users, Country, Runner WHERE Users.Email = Runner.Email AND Runner.CountryCode = Country.CountryCode AND Users.Email = '" + email + "'", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
label5.Text = email;
textBox4.Text = reader["LastName"].ToString();
textBox5.Text = reader["FirstName"].ToString();
comboBox1.Text = reader["Gender"].ToString();
comboBox2.Text = reader["CountryName"].ToString();
dateTimePicker1.Value = Convert.ToDateTime(reader["DateOfBirth"]);
textBox6.Text = reader["Picture"].ToString();
try
{
pictureBox2.Image = Image.FromFile("picture/" + reader["Picture"]);
}
catch (Exception ex)
{
}
}
}
}
private void FormRunnerProfileCoord_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT Users.Picture, Users.FirstName, Users.LastName, Users.Email, Runner.Gender, Runner.DateOfBirth, Country.CountryName, Charity.CharityName, Registration.SponsorshipTarget, RaceKitOption.RaceKitOption, EventType.EventTypeName, RegistrationStatus.RegistrationStatusId FROM RaceKitOption, Charity, EventType, Event, RegistrationEvent, Registration, RegistrationStatus, Runner, Country, Users WHERE Users.Email = Runner.Email AND Runner.RunnerId = Registration.RunnerId AND Registration.RegistrationId = RegistrationEvent.RegistrationId AND Registration.RaceKitOptionId = RaceKitOption.RaceKitOptionId AND Registration.RegistrationStatusId = RegistrationStatus.RegistrationStatusId AND RegistrationEvent.EventId = Event.EventId AND Event.EventTypeId = EventType.EventTypeID AND Country.CountryCode = Runner.CountryCode AND Users.Email = '" + runnerEmail + "' AND Registration.RegistrationId = '" + registrationId + "' AND Charity.CharityId = Registration.CharityId", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
label19.Text = reader["Email"].ToString();
label20.Text = reader["LastName"].ToString();
label21.Text = reader["FirstName"].ToString();
label22.Text = reader["Gender"].ToString();
label23.Text = reader["DateOfBirth"].ToString();
label24.Text = reader["CountryName"].ToString();
label25.Text = reader["CharityName"].ToString();
label26.Text = "$" + reader["SponsorshipTarget"].ToString();
label27.Text = reader["RaceKitOption"].ToString();
label28.Text = reader["EventTypeName"].ToString();
try
{
pictureBox2.Image = Image.FromFile("picture/" + reader["Picture"].ToString());
}
catch (Exception ex)
{
}
if (reader["RegistrationStatusId"].ToString() == "1")
{
pictureBox3.Image = Properties.Resources.tick_icon;
}
if (reader["RegistrationStatusId"].ToString() == "2")
{
pictureBox3.Image = Properties.Resources.tick_icon;
pictureBox4.Image = Properties.Resources.tick_icon;
}
if (reader["RegistrationStatusId"].ToString() == "3")
{
pictureBox3.Image = Properties.Resources.tick_icon;
pictureBox4.Image = Properties.Resources.tick_icon;
pictureBox5.Image = Properties.Resources.tick_icon;
}
if (reader["RegistrationStatusId"].ToString() == "4")
{
pictureBox3.Image = Properties.Resources.tick_icon;
pictureBox4.Image = Properties.Resources.tick_icon;
pictureBox5.Image = Properties.Resources.tick_icon;
pictureBox6.Image = Properties.Resources.tick_icon;
}
}
}
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox5.Text == "" || comboBox1.Text == "")
{
MessageBox.Show("Пожалуйста, выберете спонсора из числа благотворительных организаций и внесите сумму спонсорского взноса", "Оповещение системы");
}
else
{
if (!checkBox1.Checked && !checkBox2.Checked && !checkBox3.Checked)
{
MessageBox.Show("Пожалуйста, выберете, как минимум, один из представленных марафонов");
}
else
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Runner WHERE Email = '" + email + "'", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
runnerId = reader["RunnerId"].ToString();
}
}
string ko = "";
if (checkBox1.Checked)
{
ko = "A";
}
if (checkBox2.Checked)
{
ko = "B";
}
if (checkBox3.Checked)
{
ko = "C";
}
SqlCommand command2 = new SqlCommand("INSERT INTO Registration Values (" + runnerId + ",'','" + ko + "','1','" + textBox5.Text + "','" + (comboBox1.SelectedItem as ComboBoxItem).value + "'," + value + ")", conn);
command2.ExecuteNonQuery();
FormRegistrationMarathonConfirmation fm = new FormRegistrationMarathonConfirmation(email);
fm.Show();
this.Hide();
}
}
}
private void FormCharityList_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Charity", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
int z = 0;
while (reader.Read())
{
PictureBox pb = new PictureBox();
pb.Size = pictureBox2.Size;
pb.Location = new Point(pictureBox2.Location.X, pictureBox2.Location.Y + z);
pb.BorderStyle = BorderStyle.Fixed3D;
pb.SizeMode = PictureBoxSizeMode.StretchImage;
if (reader["CharityLogo"].ToString() != "")
{
pb.Image = Image.FromFile("logo/" + reader["CharityLogo"].ToString());
}
Label lb = new Label();
lb.Size = label2.Size;
lb.Location = new Point(label2.Location.X, label2.Location.Y + z);
lb.Font = label2.Font;
lb.ForeColor = label2.ForeColor;
lb.Text = reader["CharityName"].ToString();
RichTextBox rb = new RichTextBox();
rb.Size = richTextBox1.Size;
rb.Location = new Point(richTextBox1.Location.X, richTextBox1.Location.Y + z);
rb.Font = richTextBox1.Font;
rb.ForeColor = richTextBox1.ForeColor;
rb.Text = reader["CharityDescription"].ToString();
rb.ReadOnly = true;
panel1.Controls.Add(pb);
panel1.Controls.Add(lb);
panel1.Controls.Add(rb);
z += 156;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || textBox6.Text == "" || comboBox1.Text == "")
{
MessageBox.Show("Одно или несколько обязательных полей для ввода (отмеченных знаком *) не были заполненны!", "Оповещение системы");
}
else
{
if (textBox3.TextLength < 16)
{
MessageBox.Show("Некорретный номер карты! Длина номера банковской карточки должна составлять 16 символов!", "Оповещение системы");
}
else
{
DateTime cardTime = new DateTime(Convert.ToInt32(textBox6.Text), Convert.ToInt32(textBox5.Text), DateTime.Now.Day);
if (cardTime <= DateTime.Now)
{
MessageBox.Show("Указанный срок действия карты считается истекшим!", "Оповещение системы");
}
else
{
if (textBox4.TextLength < 3)
{
MessageBox.Show("Некорректный CVC код! Длина кода должна составлять 3 символа!", "Оповещение системы");
}
else
{
if (textBox8.Text == "" || textBox8.Text == "0")
{
MessageBox.Show("Пожалуйста, укажите сумму спонсорского взноса более $0.", "Опомещение системы");
}
else
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("UPDATE Registration Set SponsorshipTarget = SponsorshipTarget + " + textBox8.Text + " WHERE RunnerId = '" + (comboBox1.SelectedItem as ComboBoxItem).value + "'", conn);
command.ExecuteNonQuery();
FormSponsorshipConfirmation fm = new FormSponsorshipConfirmation((comboBox1.SelectedItem as ComboBoxItem).value, textBox7.Text, textBox8.Text);
fm.Show();
this.Hide();
}
}
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || textBox2.Text == "")
{
MessageBox.Show("Одно или несколько обязательных полей для ввода (отмеченных знаком *) не были заполнены", "Оповещение системы");
}
else
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE Email = '" + textBox1.Text + "' AND Password = '******'", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
MessageBox.Show("Пользователь с таким email и паролем не найден!. Удостоверьтесь в корректности введенных данных.", "Оповещение системы");
}
else
{
while (reader.Read())
{
if (reader["RoleId"].ToString() == "R")
{
//MessageBox.Show("Runner");
FormRunner fm = new FormRunner(reader["Email"].ToString());
fm.Show();
this.Hide();
}
if (reader["RoleId"].ToString() == "A")
{
//MessageBox.Show("Runner");
FormAdmin fm = new FormAdmin(reader["Email"].ToString());
fm.Show();
this.Hide();
}
if (reader["RoleId"].ToString() == "C")
{
//MessageBox.Show("Runner");
FormCoordinator fm = new FormCoordinator(reader["Email"].ToString());
fm.Show();
this.Hide();
}
}
}
}
}
}
private void comboBox1_TextChanged(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT TOP(1) Charity.CharityName, Charity.CharityId FROM Charity, Registration WHERE Charity.CharityId = Registration.CharityId AND Registration.RunnerId = " + (comboBox1.SelectedItem as ComboBoxItem).value + "", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
textBox7.Text = reader["CharityName"].ToString();
}
}
}
private void FormAddUser_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand commandRole = new SqlCommand("SELECT * FROM Role", conn);
using (SqlDataReader reader = commandRole.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["RoleName"].ToString();
item.value = reader["RoleId"].ToString();
comboBox1.Items.Add(item);
}
}
}
private void FormRegistrationMarathon_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Charity", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["CharityName"].ToString();
item.value = reader["CharityId"].ToString();
comboBox1.Items.Add(item);
}
}
}
private void FormSponsorshipCharityInfo_Load(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(Connection.GetString()))
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Charity WHERE CharityName = '" + charityName + "'", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
pictureBox1.Image = Image.FromFile("logo/" + reader["CharityLogo"].ToString());
}
catch (Exception ex) { }
richTextBox1.Text = reader["CharityDescription"].ToString();
textBox7.Text = reader["CharityName"].ToString();
}
}
}
}
private void FormSponsorship_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT RegistrationEvent.BibNumber, Runner.RunnerId, Users.FirstName, Users.LastName, Country.CountryName FROM Users,Runner, Country, RegistrationEvent, Registration WHERE Country.CountryCode = Runner.CountryCode AND Users.RoleId = 'R' AND Users.Email = Runner.Email AND Registration.RunnerId = Runner.RunnerId AND Registration.RegistrationId = RegistrationEvent.RegistrationId ORDER BY Users.FirstName", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["FirstName"].ToString() + " " + reader["LastName"].ToString() + " - " + reader["BibNumber"].ToString() + " (" + reader["CountryName"].ToString() + ")";
item.value = reader["RunnerId"].ToString();
comboBox1.Items.Add(item);
}
}
conn.Close();
}
private void FormRegistration_Load(object sender, EventArgs e)
{
timer1.Start();
openFileDialog1.Filter = "Image Files |*.jpg;*.png;*gif;";
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand commandGender = new SqlCommand("SELECT * FROM Gender", conn);
using (SqlDataReader reader = commandGender.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["Gender"].ToString();
item.value = reader["Gender"].ToString();
comboBox1.Items.Add(item);
}
}
SqlCommand commandCountry = new SqlCommand("SELECT * FROM Country ORDER BY CountryName", conn);
using (SqlDataReader reader = commandCountry.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["CountryName"].ToString();
item.value = reader["CountryCode"].ToString();
comboBox2.Items.Add(item);
}
}
}
private void FormSponsorshipConfirmation_Load(object sender, EventArgs e)
{
timer1.Start();
textBox2.ForeColor = Color.Gray;
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT Users.FirstName, Users.LastName, Country.CountryName, RegistrationEvent.BibNumber FROM Users, Runner, Country, Registration, RegistrationEvent WHERE Country.CountryCode = Runner.CountryCode AND Runner.Email = Users.Email AND Runner.RunnerId = '" + runnerId + "' AND Registration.RunnerId = Runner.RunnerId AND Registration.RegistrationId = RegistrationEvent.RegistrationId", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
textBox1.Text = reader["FirstName"].ToString() + " " + reader["LastName"].ToString() + " (" + reader["BibNumber"].ToString() + ") " + "из " + reader["CountryName"].ToString();
textBox2.Text = charityName;
label20.Text = "$" + sponsorshipTarget;
}
}
}
private void button3_Click(object sender, EventArgs e)
{
bool changePass = false;
if (textBox2.Text != "" || textBox3.Text != "")
{
changePass = true;
}
if (textBox4.Text == "" || textBox5.Text == "" || comboBox1.Text == "")
{
MessageBox.Show("Одно или несколько обязательных полей для ввода (отмеченных знаком *) не были заполнены!", "Оповещение системы");
}
else
{
bool digit = false;
bool spec = false;
bool lowChar = false;
bool pass = false;
bool cpass = false;
if (changePass)
{
for (int i = 0; i < textBox2.TextLength; i++)
{
if (Char.IsDigit(textBox2.Text[i]))
{
digit = true;
break;
}
}
for (int i = 0; i < textBox2.TextLength; i++)
{
if (Char.IsLower(textBox2.Text[i]))
{
lowChar = true;
break;
}
}
for (int i = 0; i < textBox2.TextLength; i++)
{
if (textBox2.Text[i] == '#' || textBox2.Text[i] == '!' || textBox2.Text[i] == '@' || textBox2.Text[i] == '$' || textBox2.Text[i] == '%' || textBox2.Text[i] == '^')
{
spec = true;
break;
}
}
if (textBox2.TextLength < 6 || !spec || !digit || !lowChar)
{
MessageBox.Show("Некорректный формат пароля! Длина пароля должна быть не менее шести символов, из которых должна быть, как минимум, одна буква нижнего регистра, одна цифра и один из следующих символов: !,#,%,^,@", "Оповещение системы");
}
else
{
pass = true;
}
if (pass)
{
if (textBox2.Text != textBox3.Text)
{
MessageBox.Show("Пароли не совпадают!", "Оповещение системы");
}
else
{
changePass = false;
cpass = true;
}
}
}
if (changePass == false)
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("UPDATE Users Set FirstName = '" + textBox5.Text + "', LastName = '" + textBox4.Text + "', RoleId = '" + (comboBox1.SelectedItem as ComboBoxItem).value + "' WHERE Email = '" + userEmail + "'", conn);
command.ExecuteNonQuery();
if (cpass)
{
SqlCommand command4 = new SqlCommand("UPDATE Users Set Password = '******' WHERE Email = '" + email + "'", conn);
command4.ExecuteNonQuery();
}
MessageBox.Show("Профиль пользователя успешно изменен!", "Оповещение системы");
FormUsersList fm = new FormUsersList(email);
fm.Show();
this.Hide();
}
}
}
private void FormRunnersListCoord_Load(object sender, EventArgs e)
{
timer1.Start();
comboBox3.Text = "Any";
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand commandCount = new SqlCommand("SELECT COUNT(Email) as c FROM Users WHERE RoleId = 'R'", conn);
using (SqlDataReader reader = commandCount.ExecuteReader())
{
while (reader.Read())
{
label10.Text = reader["c"].ToString();
}
}
SqlCommand commandStatus = new SqlCommand("SELECT * FROM RegistrationStatus", conn);
using (SqlDataReader reader = commandStatus.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["RegistrationStatus"].ToString();
item.value = reader["RegistrationStatusId"].ToString();
comboBox1.Items.Add(item);
comboBox1.Text = "Any";
}
}
SqlCommand commandDistance = new SqlCommand("SELECT * FROM EventType", conn);
using (SqlDataReader reader = commandDistance.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["EventTypeName"].ToString();
item.value = reader["EventTypeId"].ToString();
comboBox2.Items.Add(item);
comboBox2.Text = "Any";
}
}
SqlCommand command = new SqlCommand("SELECT TOP(400) Registration.RegistrationId, Users.FirstName, Users.LastName, Users.Email,RegistrationStatus.RegistrationStatus FROM RegistrationStatus, Registration, Runner, Users WHERE Users.Email = Runner.Email AND Users.RoleId = 'R' AND Runner.RunnerId = Registration.RunnerId AND Registration.RegistrationStatusId = RegistrationStatus.RegistrationStatusId", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
int z = 0;
while (reader.Read())
{
TextBox tx1 = new TextBox();
tx1.Size = textBox1.Size;
tx1.Font = textBox1.Font;
tx1.Location = new Point(textBox1.Location.X, textBox1.Location.Y + z);
tx1.Text = reader["LastName"].ToString();
tx1.ReadOnly = true;
panel1.Controls.Add(tx1);
TextBox tx2 = new TextBox();
tx2.Size = textBox2.Size;
tx2.Font = textBox2.Font;
tx2.Location = new Point(textBox2.Location.X, textBox2.Location.Y + z);
tx2.Text = reader["FirstName"].ToString();
tx2.ReadOnly = true;
panel1.Controls.Add(tx2);
TextBox tx3 = new TextBox();
tx3.Size = textBox3.Size;
tx3.Font = textBox3.Font;
tx3.Location = new Point(textBox3.Location.X, textBox3.Location.Y + z);
tx3.Text = reader["Email"].ToString();
tx3.ReadOnly = true;
panel1.Controls.Add(tx3);
TextBox tx4 = new TextBox();
tx4.Size = textBox4.Size;
tx4.Font = textBox4.Font;
tx4.Location = new Point(textBox4.Location.X, textBox4.Location.Y + z);
tx4.Text = reader["RegistrationStatus"].ToString();
tx4.ReadOnly = true;
panel1.Controls.Add(tx4);
Button bn = new Button();
bn.Size = button6.Size;
bn.Location = button6.Location;
bn.Text = "Edit";
bn.Font = button6.Font;
bn.Tag = reader["Email"].ToString();
bn.TabIndex = Convert.ToInt32(reader["RegistrationId"]);
bn.Location = new Point(button6.Location.X, button6.Location.Y + z);
bn.Click += (ee, aa) => {
//MessageBox.Show(bn.Tag.ToString() + " " + bn.TabIndex);
FormRunnerProfileCoord fm = new FormRunnerProfileCoord(email, bn.Tag.ToString(), bn.TabIndex.ToString());
fm.Show();
this.Hide();
};
panel1.Controls.Add(bn);
z += 26;
}
}
}
private void button2_Click(object sender, EventArgs e)
{
string status = "";
if (comboBox1.Text != "" && comboBox1.Text != "Any")
{
status = " AND Registration.RegistrationStatusId = '" + (comboBox1.SelectedItem as ComboBoxItem).value + "' ";
}
string distance = "";
string from = "";
if (comboBox2.Text != "" && comboBox2.Text != "Any")
{
from = " RegistrationEvent, Event, EventType, ";
distance = " AND Registration.RegistrationId = RegistrationEvent.RegistrationId AND RegistrationEvent.EventId = Event.EventId AND Event.EventTypeId = EventType.EventTypeId AND EventType.EventTypeId = '" + (comboBox2.SelectedItem as ComboBoxItem).value + "' ";
}
panel1.Controls.Clear();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT TOP(400) Users.FirstName, Users.LastName, Users.Email,RegistrationStatus.RegistrationStatus FROM " + from + " RegistrationStatus, Registration, Runner, Users WHERE Users.Email = Runner.Email AND Users.RoleId = 'R' AND Runner.RunnerId = Registration.RunnerId AND Registration.RegistrationStatusId = RegistrationStatus.RegistrationStatusId" + status + distance, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
int z = 0;
while (reader.Read())
{
TextBox tx1 = new TextBox();
tx1.Size = textBox1.Size;
tx1.Font = textBox1.Font;
tx1.Location = new Point(textBox1.Location.X, textBox1.Location.Y + z);
tx1.Text = reader["LastName"].ToString();
tx1.ReadOnly = true;
panel1.Controls.Add(tx1);
TextBox tx2 = new TextBox();
tx2.Size = textBox2.Size;
tx2.Font = textBox2.Font;
tx2.Location = new Point(textBox2.Location.X, textBox2.Location.Y + z);
tx2.Text = reader["FirstName"].ToString();
tx2.ReadOnly = true;
panel1.Controls.Add(tx2);
TextBox tx3 = new TextBox();
tx3.Size = textBox3.Size;
tx3.Font = textBox3.Font;
tx3.Location = new Point(textBox3.Location.X, textBox3.Location.Y + z);
tx3.Text = reader["Email"].ToString();
tx3.ReadOnly = true;
panel1.Controls.Add(tx3);
TextBox tx4 = new TextBox();
tx4.Size = textBox4.Size;
tx4.Font = textBox4.Font;
tx4.Location = new Point(textBox4.Location.X, textBox4.Location.Y + z);
tx4.Text = reader["RegistrationStatus"].ToString();
tx4.ReadOnly = true;
panel1.Controls.Add(tx4);
Button bn = new Button();
bn.Size = button6.Size;
bn.Location = button6.Location;
bn.Text = "Edit";
bn.Font = button6.Font;
bn.Tag = reader["Email"].ToString();
bn.Location = new Point(button6.Location.X, button6.Location.Y + z);
bn.Click += (ee, aa) => {
MessageBox.Show(bn.Tag.ToString());
//FormAddEditCharity fm = new FormAddEditCharity(email, bn.Tag.ToString());
//fm.Show();
//this.Hide();
};
panel1.Controls.Add(bn);
z += 26;
}
}
}
private void FormVolunteers_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
int k = 0;
SqlCommand command = new SqlCommand("SELECT Volunteer.FirstName, Volunteer.LastName, Volunteer.Gender, Country.CountryName FROM Volunteer, Country WHERE Volunteer.CountryCode = Country.CountryCode", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
int z = 0;
if (reader.HasRows)
{
while (reader.Read())
{
k += 1;
TextBox tx1 = new TextBox();
tx1.Size = textBox1.Size;
tx1.Location = new Point(textBox1.Location.X, textBox1.Location.Y + z);
tx1.Font = textBox1.Font;
tx1.ReadOnly = true;
tx1.Text = reader["LastName"].ToString();
panel1.Controls.Add(tx1);
TextBox tx3 = new TextBox();
tx3.Size = textBox3.Size;
tx3.Location = new Point(textBox3.Location.X, textBox3.Location.Y + z);
tx3.Font = textBox3.Font;
tx3.ReadOnly = true;
tx3.Text = reader["FirstName"].ToString();
panel1.Controls.Add(tx3);
TextBox tx2 = new TextBox();
tx2.Size = textBox2.Size;
tx2.Location = new Point(textBox2.Location.X, textBox2.Location.Y + z);
tx2.Font = textBox2.Font;
tx2.ReadOnly = true;
tx2.Text = reader["CountryName"].ToString();
panel1.Controls.Add(tx2);
TextBox tx4 = new TextBox();
tx4.Size = textBox4.Size;
tx4.Location = new Point(textBox4.Location.X, textBox4.Location.Y + z);
tx4.Font = textBox4.Font;
tx4.ReadOnly = true;
tx4.Text = reader["Gender"].ToString();
panel1.Controls.Add(tx4);
z += 26;
}
}
else
{
label12.Visible = true;
}
}
label7.Text = k.ToString();
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
{
MessageBox.Show("Поле 'Наименование' благотворительной организации обязательно для заполнения!", "Оповещение системы");
}
else
{
if (charityId != null)
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("UPDATE Charity Set CharityName = @name, CharityDescription = @desc, CharityLogo = @logo WHERE CharityId = '" + charityId + "'", conn);
command.Parameters.Add("@name", textBox1.Text);
command.Parameters.Add("@desc", richTextBox1.Text);
command.Parameters.Add("@logo", textBox2.Text);
command.ExecuteNonQuery();
try
{
if (textBox2.Text != "")
{
Bitmap bmp = new Bitmap(openFileDialog1.FileName);
bmp.Save("logo/" + openFileDialog1.SafeFileName);
}
}
catch (Exception ex)
{
}
MessageBox.Show("Благотворительная организация " + textBox1.Text + " успешно обновлена!", "Оповещение системы");
FormCharityListAdmin fm = new FormCharityListAdmin(email);
fm.Show();
this.Hide();
}
else
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("INSERT INTO Charity Values(@name, @desc, @logo)", conn);
command.Parameters.Add("@name", textBox1.Text);
command.Parameters.Add("@desc", richTextBox1.Text);
command.Parameters.Add("@logo", textBox2.Text);
command.ExecuteNonQuery();
if (textBox2.Text != "")
{
Bitmap bmp = new Bitmap(openFileDialog1.FileName);
bmp.Save("logo/" + openFileDialog1.SafeFileName);
}
MessageBox.Show("Благотворительная организация " + textBox1.Text + " успешно добавлена!", "Оповещение системы");
FormCharityListAdmin fm = new FormCharityListAdmin(email);
fm.Show();
this.Hide();
}
}
}
private void button5_Click(object sender, EventArgs e)
{
bool changePass = false;
if (textBox2.Text != "" || textBox3.Text != "")
{
changePass = true;
}
if (textBox4.Text == "" || textBox5.Text == "" || comboBox1.Text == "" || comboBox2.Text == "")
{
MessageBox.Show("Одно или несколько обязательных полей для ввода (отмеченных знаком *) не были заполнены!", "Оповещение системы");
}
else
{
bool digit = false;
bool spec = false;
bool lowChar = false;
bool pass = false;
bool cpass = false;
if (changePass)
{
for (int i = 0; i < textBox2.TextLength; i++)
{
if (Char.IsDigit(textBox2.Text[i]))
{
digit = true;
break;
}
}
for (int i = 0; i < textBox2.TextLength; i++)
{
if (Char.IsLower(textBox2.Text[i]))
{
lowChar = true;
break;
}
}
for (int i = 0; i < textBox2.TextLength; i++)
{
if (textBox2.Text[i] == '#' || textBox2.Text[i] == '!' || textBox2.Text[i] == '@' || textBox2.Text[i] == '$' || textBox2.Text[i] == '%' || textBox2.Text[i] == '^')
{
spec = true;
break;
}
}
if (textBox2.TextLength < 6 || !spec || !digit || !lowChar)
{
MessageBox.Show("Некорректный формат пароля! Длина пароля должна быть не менее шести символов, из которых должна быть, как минимум, одна буква нижнего регистра, одна цифра и один из следующих символов: !,#,%,^,@", "Оповещение системы");
}
else
{
pass = true;
}
if (pass)
{
if (textBox2.Text != textBox3.Text)
{
MessageBox.Show("Пароли не совпадают!", "Оповещение системы");
}
else
{
changePass = false;
cpass = true;
}
}
}
if (changePass == false)
{
DateTime dateOfBirth = Convert.ToDateTime(dateTimePicker1.Value);
if (DateTime.Now.Year - dateOfBirth.Year < 10)
{
MessageBox.Show("Возраст бегуна на момент регистрации должен быть не менее 10ти лет!", "Оповещение системы");
}
else
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("UPDATE Users Set FirstName = '" + textBox5.Text + "', LastName = '" + textBox4.Text + "', Picture = '" + textBox6.Text + "' WHERE Email = '" + runnerEmail + "'", conn);
command.ExecuteNonQuery();
SqlCommand command2 = new SqlCommand("UPDATE Runner Set Gender = '" + (comboBox1.SelectedItem as ComboBoxItem).value + "', CountryCode = '" + (comboBox2.SelectedItem as ComboBoxItem).value + "' WHERE Email = '" + runnerEmail + "'", conn);
command2.ExecuteNonQuery();
SqlCommand command3 = new SqlCommand("UPDATE Registration Set RegistrationStatusId = '" + (comboBox3.SelectedItem as ComboBoxItem).value + "' WHERE RegistrationId = '" + registrationId + "'", conn);
command3.ExecuteNonQuery();
if (cpass)
{
SqlCommand command4 = new SqlCommand("UPDATE Users Set Password = '******' WHERE Email = '" + email + "'", conn);
command4.ExecuteNonQuery();
}
try
{
Bitmap bmp = new Bitmap(openFileDialog1.FileName);
bmp.Save("picture/" + openFileDialog1.SafeFileName);
}
catch (Exception ex)
{
}
MessageBox.Show("Профиль бегуна успешно изменен!", "Оповещение системы");
FormRunnerProfileCoord fm = new FormRunnerProfileCoord(email, runnerEmail, registrationId);
fm.Show();
this.Hide();
}
}
}
}
private void comboBox1_TextChanged(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
}
private void FormCharityListCoord_Load(object sender, EventArgs e)
{
timer1.Start();
comboBox1.Text = "Any";
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand cmd1 = new SqlCommand("SELECT COUNT(CharityId) as c from Charity", conn);
using (SqlDataReader reader = cmd1.ExecuteReader())
{
while (reader.Read())
{
label2.Text += reader["c"].ToString();
}
}
SqlCommand cmd2 = new SqlCommand("SELECT SUM(SponsorshipTarget) as c from Registration", conn);
using (SqlDataReader reader = cmd2.ExecuteReader())
{
while (reader.Read())
{
label4.Text += "$" + reader["c"].ToString();
}
}
SqlCommand command = new SqlCommand("SELECT SUM(Registration.SponsorshipTarget) as c, Charity.CharityName, Charity.CharityLogo FROM Charity, Registration WHERE Charity.CharityId = Registration.CharityId GROUP BY Charity.CharityName, Charity.CharityLogo", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
int z = 0;
while (reader.Read())
{
PictureBox pb = new PictureBox();
pb.Size = pictureBox2.Size;
pb.Location = new Point(pictureBox2.Location.X, pictureBox2.Location.Y + z);
pb.BorderStyle = BorderStyle.Fixed3D;
pb.SizeMode = PictureBoxSizeMode.StretchImage;
if (reader["CharityLogo"].ToString() != "")
{
pb.Image = Image.FromFile("logo/" + reader["CharityLogo"].ToString());
}
RichTextBox rb = new RichTextBox();
rb.Size = richTextBox1.Size;
rb.Location = new Point(richTextBox1.Location.X, richTextBox1.Location.Y + z);
rb.Font = richTextBox1.Font;
rb.ForeColor = richTextBox1.ForeColor;
rb.Text = reader["CharityName"].ToString();
rb.ReadOnly = true;
RichTextBox rb2 = new RichTextBox();
rb2.Size = richTextBox2.Size;
rb2.Location = new Point(richTextBox2.Location.X, richTextBox2.Location.Y + z);
rb2.Font = richTextBox2.Font;
rb2.ForeColor = richTextBox2.ForeColor;
rb2.Text = "$" + reader["c"].ToString();
rb2.ReadOnly = true;
panel1.Controls.Add(pb);
panel1.Controls.Add(rb);
panel1.Controls.Add(rb2);
z += 105;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || comboBox1.Text == "" || comboBox2.Text == "")
{
MessageBox.Show("Одно или несколько обязательных полей для ввода (отмеченных знаком *) не были заполнены!", "Оповещение системы");
}
else
{
Regex emailRegex = new Regex(@"\w{2,10}@\w{2,10}.\w{2,10}");
Match emailMatch = emailRegex.Match(textBox1.Text);
if (emailMatch.Value == "")
{
MessageBox.Show("Некорректный формат email!", "Оповещение системы");
}
else
{
bool digit = false;
bool spec = false;
bool lowChar = false;
bool pass = false;
for (int i = 0; i < textBox2.TextLength; i++)
{
if (Char.IsDigit(textBox2.Text[i]))
{
digit = true;
break;
}
}
for (int i = 0; i < textBox2.TextLength; i++)
{
if (Char.IsLower(textBox2.Text[i]))
{
lowChar = true;
break;
}
}
for (int i = 0; i < textBox2.TextLength; i++)
{
if (textBox2.Text[i] == '#' || textBox2.Text[i] == '!' || textBox2.Text[i] == '@' || textBox2.Text[i] == '$' || textBox2.Text[i] == '%' || textBox2.Text[i] == '^')
{
spec = true;
break;
}
}
if (textBox2.TextLength < 6 || !spec || !digit || !lowChar)
{
MessageBox.Show("Некорректный формат пароля! Длина пароля должна быть не менее шести символов, из которых должна быть, как минимум, одна буква нижнего регистра, одна цифра и один из следующих символов: !,#,%,^,@", "Оповещение системы");
}
else
{
pass = true;
}
if (pass)
{
if (textBox2.Text != textBox3.Text)
{
MessageBox.Show("Пароли не совпадают!", "Оповещение системы");
}
else
{
DateTime dateOfBirth = Convert.ToDateTime(dateTimePicker1.Value);
if (DateTime.Now.Year - dateOfBirth.Year < 10)
{
MessageBox.Show("Возраст бегуна на момент регистрации должен быть не менее 10ти лет!", "Оповещение системы");
}
else
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("INSERT INTO Users VALUES(@e,@p,@l,@f,@r,@pp)", conn);
command.Parameters.Add("@e", textBox1.Text);
command.Parameters.Add("@p", textBox2.Text);
command.Parameters.Add("@l", textBox4.Text);
command.Parameters.Add("@f", textBox5.Text);
command.Parameters.Add("@r", "R");
command.Parameters.Add("@pp", textBox6.Text);
command.ExecuteNonQuery();
SqlCommand command2 = new SqlCommand("INSERT INTO Runner(Email, Gender, DateOfBirth, CountryCode) VALUES(@e, @g, @d, @c)", conn);
command2.Parameters.Add("@e", textBox1.Text);
command2.Parameters.Add("@g", (comboBox1.SelectedItem as ComboBoxItem).value);
command2.Parameters.Add("@d", dateTimePicker1.Value);
command2.Parameters.Add("@c", (comboBox2.SelectedItem as ComboBoxItem).value);
command2.ExecuteNonQuery();
try
{
Bitmap bmp = new Bitmap(openFileDialog1.FileName);
bmp.Save("picture/" + openFileDialog1.SafeFileName);
}
catch (Exception ex)
{
}
FormRegistrationMarathon fm = new FormRegistrationMarathon(textBox1.Text);
fm.Show();
this.Hide();
}
}
}
}
}
}
private void FormCharityListAdmin_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Charity ORDER BY CharityId DESC", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
int z = 0;
while (reader.Read())
{
PictureBox pb = new PictureBox();
pb.Size = pictureBox2.Size;
pb.Location = new Point(pictureBox2.Location.X, pictureBox2.Location.Y + z);
pb.SizeMode = PictureBoxSizeMode.StretchImage;
pb.BorderStyle = BorderStyle.Fixed3D;
if (reader["CharityLogo"].ToString() != "")
{
pb.Image = Image.FromFile("logo/" + reader["CharityLogo"].ToString());
}
RichTextBox rch1 = new RichTextBox();
rch1.Text = reader["CharityName"].ToString();
rch1.Size = richTextBox1.Size;
rch1.Location = new Point(richTextBox1.Location.X, richTextBox1.Location.Y + z);
rch1.Font = richTextBox1.Font;
rch1.ReadOnly = true;
RichTextBox rch2 = new RichTextBox();
rch2.Text = reader["CharityDescription"].ToString();
rch2.Size = richTextBox2.Size;
rch2.Location = new Point(richTextBox2.Location.X, richTextBox2.Location.Y + z);
rch2.Font = richTextBox2.Font;
rch2.ReadOnly = true;
Panel pn = new Panel();
pn.Size = panel6.Size;
pn.Location = new Point(panel6.Location.X, panel6.Location.Y + z);
pn.BorderStyle = BorderStyle.Fixed3D;
Button bn = new Button();
bn.Size = button2.Size;
bn.Location = button2.Location;
bn.Text = "Edit";
bn.Font = button2.Font;
bn.Tag = reader["CharityId"].ToString();
bn.Click += (ee, aa) => {
//MessageBox.Show(bn.Tag.ToString());
FormAddEditCharity fm = new FormAddEditCharity(email, bn.Tag.ToString());
fm.Show();
this.Hide();
};
pn.Controls.Add(bn);
panel1.Controls.Add(pn);
panel1.Controls.Add(pb);
panel1.Controls.Add(rch1);
panel1.Controls.Add(rch2);
z += 122;
}
}
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || comboBox1.Text == "")
{
MessageBox.Show("Одно или несколько обязательных полей для ввода (отмеченных знаком *) не были заполнены!", "Оповещение системы");
}
else
{
Regex emailRegex = new Regex(@"\w{2,10}@\w{2,10}.\w{2,10}");
Match emailMatch = emailRegex.Match(textBox1.Text);
if (emailMatch.Value == "")
{
MessageBox.Show("Некорректный формат email!", "Оповещение системы");
}
else
{
bool digit = false;
bool spec = false;
bool lowChar = false;
bool pass = false;
for (int i = 0; i < textBox2.TextLength; i++)
{
if (Char.IsDigit(textBox2.Text[i]))
{
digit = true;
break;
}
}
for (int i = 0; i < textBox2.TextLength; i++)
{
if (Char.IsLower(textBox2.Text[i]))
{
lowChar = true;
break;
}
}
for (int i = 0; i < textBox2.TextLength; i++)
{
if (textBox2.Text[i] == '#' || textBox2.Text[i] == '!' || textBox2.Text[i] == '@' || textBox2.Text[i] == '$' || textBox2.Text[i] == '%' || textBox2.Text[i] == '^')
{
spec = true;
break;
}
}
if (textBox2.TextLength < 6 || !spec || !digit || !lowChar)
{
MessageBox.Show("Некорректный формат пароля! Длина пароля должна быть не менее шести символов, из которых должна быть, как минимум, одна буква нижнего регистра, одна цифра и один из следующих символов: !,#,%,^,@", "Оповещение системы");
}
else
{
pass = true;
}
if (pass)
{
if (textBox2.Text != textBox3.Text)
{
MessageBox.Show("Пароли не совпадают!", "Оповещение системы");
}
else
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand command = new SqlCommand("INSERT INTO Users (Email, Password, FirstName, LastName, RoleId) Values (@e, @p, @f, @l, @r)", conn);
command.Parameters.Add("@e", textBox1.Text);
command.Parameters.Add("@p", textBox2.Text);
command.Parameters.Add("@f", textBox5.Text);
command.Parameters.Add("@l", textBox4.Text);
command.Parameters.Add("@r", (comboBox1.SelectedItem as ComboBoxItem).value);
command.ExecuteNonQuery();
MessageBox.Show("Пользователь успешно добавлен в Систему!", "Оповещение системы");
FormUsersList fm = new FormUsersList(email);
fm.Show();
this.Hide();
}
}
}
}
}
private void FormAllRaces_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand commandMarathon = new SqlCommand("SELECT Marathon.MarathonId, Marathon.YearHeld, Country.CountryName FROM Marathon, Country WHERE Marathon.CountryCode = Country.CountryCode", conn);
using (SqlDataReader reader = commandMarathon.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["YearHeld"].ToString() + " - " + reader["CountryName"].ToString();
item.value = reader["MarathonId"].ToString();
comboBox1.Items.Add(item);
comboBox1.Text = "Any";
}
}
SqlCommand commandDistance = new SqlCommand("SELECT * FROM EventType", conn);
using (SqlDataReader reader = commandDistance.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["EventTypeName"].ToString();
item.value = reader["EventTypeId"].ToString();
comboBox2.Items.Add(item);
comboBox2.Text = "Any";
}
}
SqlCommand commandGender = new SqlCommand("SELECT * FROM Gender", conn);
using (SqlDataReader reader = commandGender.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["Gender"].ToString();
item.value = reader["Gender"].ToString();
comboBox4.Items.Add(item);
comboBox4.Text = "Any";
}
}
comboBox3.Text = "Any";
SqlCommand commandCountRunners = new SqlCommand("SELECT Count(Email) as c FROM Users WHERE RoleId = 'R'", conn);
using (SqlDataReader reader = commandCountRunners.ExecuteReader())
{
while (reader.Read())
{
label11.Text = reader["c"].ToString();
}
}
SqlCommand commandCountRunnersFinish = new SqlCommand("SELECT Count(Users.Email) as c FROM Users, Runner, Registration, RegistrationEvent WHERE RoleId = 'R' AND Users.Email = Runner.Email AND Registration.RunnerId = Runner.RunnerId AND Registration.RegistrationId = RegistrationEvent.RegistrationId AND RegistrationEvent.RaceTime is not null", conn);
using (SqlDataReader reader = commandCountRunnersFinish.ExecuteReader())
{
while (reader.Read())
{
label12.Text = reader["c"].ToString();
}
}
SqlCommand commandAverageTime = new SqlCommand("SELECT AVG(RaceTime) as c FROM RegistrationEvent WHERE RaceTime is not null", conn);
using (SqlDataReader reader = commandAverageTime.ExecuteReader())
{
while (reader.Read())
{
TimeSpan sp = TimeSpan.FromSeconds(Convert.ToDouble(reader["c"].ToString()));
label10.Text = sp.Hours + "hh " + sp.Minutes + "mm " + sp.Seconds + "ss ";
}
}
SqlCommand command = new SqlCommand("SELECT TOP(500) Users.FirstName, Users.LastName, Country.CountryName, RegistrationEvent.RaceTime FROM Users, RegistrationEvent, Registration, Runner, Country WHERE Users.Email = Runner.Email AND Runner.CountryCode = Country.CountryCode AND Registration.RunnerId = Runner.RunnerId AND RegistrationEvent.RegistrationId = Registration.RegistrationId AND RegistrationEvent.RaceTime is not null AND RegistrationEvent.RaceTime !=0 ORDER BY RegistrationEvent.RaceTime", conn);
//, Users.FirstName, Users.LastName, Country.CountryName FROM Registration, RegistrationEvent, Users, Runner, Country WHERE AND ORDER BY RegistrationEvent.RaceTime
using (SqlDataReader reader = command.ExecuteReader())
{
int z = 0;
int i = 1;
while (reader.Read())
{
TextBox tx1 = new TextBox();
tx1.Size = textBox1.Size;
tx1.Font = textBox1.Font;
tx1.Location = new Point(textBox1.Location.X, textBox1.Location.Y + z);
tx1.Text = i.ToString();
tx1.ReadOnly = true;
TextBox tx2 = new TextBox();
tx2.Size = textBox2.Size;
tx2.Font = textBox2.Font;
tx2.Location = new Point(textBox2.Location.X, textBox2.Location.Y + z);
TimeSpan sp = TimeSpan.FromSeconds(Convert.ToDouble(reader["RaceTime"].ToString()));
tx2.Text = sp.Hours + "hh " + sp.Minutes + "mm " + sp.Seconds + "ss ";
tx2.ReadOnly = true;
TextBox tx3 = new TextBox();
tx3.Size = textBox3.Size;
tx3.Font = textBox3.Font;
tx3.Location = new Point(textBox3.Location.X, textBox3.Location.Y + z);
tx3.Text = reader["LastName"].ToString() + " " + reader["FirstName"].ToString();
tx3.ReadOnly = true;
TextBox tx4 = new TextBox();
tx4.Size = textBox4.Size;
tx4.Font = textBox4.Font;
tx4.Location = new Point(textBox4.Location.X, textBox4.Location.Y + z);
tx4.Text = reader["CountryName"].ToString();
tx4.ReadOnly = true;
panel1.Controls.Add(tx1);
panel1.Controls.Add(tx2);
panel1.Controls.Add(tx3);
panel1.Controls.Add(tx4);
i++;
z += 30;
}
}
}
private void button3_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
string[] data = File.ReadAllLines(openFileDialog1.FileName);
for (int i = 1; i < data.Length; i++)
{
String[] word = data[i].Split(new char[] { ',' });
for (int j = 0; j < word.Length; j++)
{
//MessageBox.Show(word[j]);
SqlCommand commanSearchId = new SqlCommand("SELECT * FROM Volunteer WHERE VolunteerId = '" + word[0] + "'", conn);
bool t = true;
using (SqlDataReader reader = commanSearchId.ExecuteReader())
{
if (reader.HasRows)
{
t = false;
}
}
if (!t)
{
try
{
if (word[4] == "F")
{
word[4] = "Female";
}
if (word[4] == "M")
{
word[4] = "Male";
}
SqlCommand command1 = new SqlCommand("UPDATE Volunteer Set FirstName = '" + word[2] + "', LastName = '" + word[1] + "', CountryCode = '" + word[3] + "', Gender = '" + word[4] + "' WHERE VolunteerId = '" + word[0] + "'", conn);
command1.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
if (t)
{
try
{
SqlCommand insert = new SqlCommand("SET IDENTITY_INSERT Volunteer ON", conn);
insert.ExecuteNonQuery();
SqlCommand command2 = new SqlCommand("SET IDENTITY_INSERT Volunteer ON; INSERT INTO Volunteer(VolunteerId, FirstName, LastName, CountryCode, Gender) Values(@id, @f, @l, @c, @g)", conn);
if (word[4] == "F")
{
word[4] = "Female";
}
if (word[4] == "M")
{
word[4] = "Male";
}
command2.Parameters.Add("@id", word[0]);
command2.Parameters.Add("@f", word[2]);
command2.Parameters.Add("@l", word[1]);
command2.Parameters.Add("@c", word[3]);
command2.Parameters.Add("@g", word[4]);
command2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
MessageBox.Show("Данные о волонтерах успешно добавлены!", "Оповещение системы");
FormVolunteers fm = new FormVolunteers(email);
fm.Show();
this.Hide();
}
private void FormUsersList_Load(object sender, EventArgs e)
{
timer1.Start();
SqlConnection conn = new SqlConnection(Connection.GetString());
conn.Open();
SqlCommand commandCount = new SqlCommand("SELECT COUNT(Email) as c FROM Users", conn);
using (SqlDataReader reader = commandCount.ExecuteReader())
{
while (reader.Read())
{
label10.Text = reader["c"].ToString();
}
}
SqlCommand commandRole = new SqlCommand("SELECT * FROM Role", conn);
using (SqlDataReader reader = commandRole.ExecuteReader())
{
while (reader.Read())
{
ComboBoxItem item = new ComboBoxItem();
item.text = reader["RoleName"].ToString();
item.value = reader["RoleId"].ToString();
comboBox1.Items.Add(item);
comboBox1.Text = "Any";
comboBox2.Text = "Any";
}
}
SqlCommand command = new SqlCommand("SELECT TOP(500) Users.FirstName, Users.LastName, Users.Email, Role.RoleName FROM Users, Role WHERE Users.RoleId = Role.RoleId ORDER BY Role.RoleId", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
int z = 0;
while (reader.Read())
{
TextBox tx1 = new TextBox();
tx1.Size = textBox1.Size;
tx1.Font = textBox1.Font;
tx1.Location = new Point(textBox1.Location.X, textBox1.Location.Y + z);
tx1.Text = reader["LastName"].ToString();
tx1.ReadOnly = true;
panel1.Controls.Add(tx1);
TextBox tx2 = new TextBox();
tx2.Size = textBox2.Size;
tx2.Font = textBox2.Font;
tx2.Location = new Point(textBox2.Location.X, textBox2.Location.Y + z);
tx2.Text = reader["FirstName"].ToString();
tx2.ReadOnly = true;
panel1.Controls.Add(tx2);
TextBox tx3 = new TextBox();
tx3.Size = textBox3.Size;
tx3.Font = textBox3.Font;
tx3.Location = new Point(textBox3.Location.X, textBox3.Location.Y + z);
tx3.Text = reader["Email"].ToString();
tx3.ReadOnly = true;
panel1.Controls.Add(tx3);
TextBox tx4 = new TextBox();
tx4.Size = textBox4.Size;
tx4.Font = textBox4.Font;
tx4.Location = new Point(textBox4.Location.X, textBox4.Location.Y + z);
tx4.Text = reader["RoleName"].ToString();
tx4.ReadOnly = true;
panel1.Controls.Add(tx4);
Button bn = new Button();
bn.Size = button6.Size;
bn.Location = button6.Location;
bn.Text = "Edit";
bn.Font = button6.Font;
bn.Tag = reader["Email"].ToString();
bn.Location = new Point(button6.Location.X, button6.Location.Y + z);
bn.Click += (ee, aa) => {
//MessageBox.Show(bn.Tag.ToString() + " " + bn.TabIndex);
FormEditUser fm = new FormEditUser(email, bn.Tag.ToString());
fm.Show();
this.Hide();
};
panel1.Controls.Add(bn);
z += 26;
}
}
}
