private void AddButton_Click(object sender, RoutedEventArgs e)
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
if (carModel.Text == string.Empty)
{
System.Windows.MessageBox.Show("Вы не выбрали марку автомобиля.");
return;
}
if (carColor.Text == string.Empty)
{
System.Windows.MessageBox.Show("Вы не выбрали цвет автомобиля.");
return;
}
if (carOwner.Text == string.Empty)
{
System.Windows.MessageBox.Show("Вы не выбрали код владельца автомобиля.");
return;
}
if (carNumber.Text != CheckCar.CheckCarStateNumber(carNumber.Text))
{
System.Windows.MessageBox.Show(CheckCar.CheckCarStateNumber(carNumber.Text));
return;
}
string findReplays = "SELECT CarCode FROM ViolatorCar WHERE (CarModelCode = (SELECT CarModelCode FROM CarModel WHERE CarModelName = '" + carModel.Text + "') AND ColorCode = (SELECT ColorCode FROM Color WHERE ColorName = '" + carColor.Text + "') AND CarStatetNumber = '" + carNumber.Text + "' AND ViolatorCode = " + Convert.ToInt32(carOwner.Text) + ") OR CarStatetNumber = '" + carNumber.Text + "'";
SqlCommand sqlCommand3 = new SqlCommand(findReplays, connection);
SqlDataReader sqlDataReader = sqlCommand3.ExecuteReader();
if (sqlDataReader.HasRows)
{
System.Windows.MessageBox.Show("Этот автомобиль уже есть в базе, либо вы ввели уже занятый номер автомобиля.");
return;
}
else
{
using (SqlConnection journalEntry = new SqlConnection(connectionString))
using (SqlCommand lastCommnd = journalEntry.CreateCommand())
{
lastCommnd.CommandText = "INSERT INTO ViolatorCar(CarModelCode, ColorCode, CarStatetNumber, ViolatorCode) VALUES ((SELECT CarModelCode FROM CarModel WHERE CarModelName = @carName), (SELECT ColorCode FROM Color WHERE ColorName = @carColor), @carNumber, @carOwner)";
lastCommnd.Parameters.AddWithValue("@carName", carModel.Text);
lastCommnd.Parameters.AddWithValue("@carColor", carColor.Text);
lastCommnd.Parameters.AddWithValue("@carNumber", carNumber.Text);
lastCommnd.Parameters.AddWithValue("@carOwner", Convert.ToInt32(carOwner.Text));
journalEntry.Open();
lastCommnd.ExecuteNonQuery();
journalEntry.Close();
}
System.Windows.MessageBox.Show("Автомобиль добавлен.");
}
sqlDataReader.Close();
connection.Close();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
if (criterion.Text == "Модель автомобиля")
{
if (searchCriterion.Text != CheckCar.CheckCarModel(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckCar.CheckCarModel(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Цвет автомобиля")
{
if (searchCriterion.Text != CheckCar.CheckCarColor(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckCar.CheckCarColor(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Номер автомобиля")
{
if (searchCriterion.Text != CheckCar.CheckCarStateNumber(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckCar.CheckCarStateNumber(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Фамилия владельца")
{
if (searchCriterion.Text != CheckViolator.CheckViolatorSurname(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckViolator.CheckViolatorSurname(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Имя владельца")
{
if (searchCriterion.Text != CheckViolator.CheckViolatorName(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckViolator.CheckViolatorName(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Отчество владельца")
{
if (searchCriterion.Text != CheckViolator.CheckViolatorPatronymic(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckViolator.CheckViolatorPatronymic(searchCriterion.Text));
return;
}
}
else
{
System.Windows.MessageBox.Show("Вы не выбрали критерий поиска.");
return;
}
StreamWriter writeCriterion = new StreamWriter("Criterion.txt");
writeCriterion.Write(criterion.Text);
writeCriterion.Close();
StreamWriter writeSearchCriterion = new StreamWriter("SearchCriterion.txt");
writeSearchCriterion.Write(searchCriterion.Text);
writeSearchCriterion.Close();
ViolatorsCarsSearchResult violatorsCarsSearchResult = new ViolatorsCarsSearchResult();
violatorsCarsSearchResult.Show();
this.Close();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
if (criterion.Text == "Дата нарушения")
{
if (searchCriterion.Text != CheckJournalEntry.CheckEnrtyDate(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckJournalEntry.CheckEnrtyDate(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Время нарушения")
{
if (searchCriterion.Text != CheckJournalEntry.CheckEntryTime(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckJournalEntry.CheckEntryTime(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Тип нарушения")
{
if (searchCriterion.Text != CheckViolation.CheckViolationName(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckViolation.CheckViolationName(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Номер автомобиля")
{
if (searchCriterion.Text != CheckCar.CheckCarStateNumber(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckCar.CheckCarStateNumber(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Марка автомобиля")
{
if (searchCriterion.Text != CheckCar.CheckCarModel(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckCar.CheckCarModel(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Фамилия нарушителя")
{
if (searchCriterion.Text != CheckViolator.CheckViolatorSurname(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckViolator.CheckViolatorSurname(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Имя нарушителя")
{
if (searchCriterion.Text != CheckViolator.CheckViolatorName(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckViolator.CheckViolatorName(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Отчество нарушителя")
{
if (searchCriterion.Text != CheckViolator.CheckViolatorPatronymic(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckViolator.CheckViolatorPatronymic(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Сумма штрафа (руб.)")
{
if (searchCriterion.Text != CheckViolation.CheckViolationCost(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckViolation.CheckViolationCost(searchCriterion.Text));
return;
}
}
else if (criterion.Text == "Статус оплаты")
{
if (searchCriterion.Text != CheckJournalEntry.CheckPaymentStatus(searchCriterion.Text))
{
System.Windows.MessageBox.Show(CheckJournalEntry.CheckPaymentStatus(searchCriterion.Text));
return;
}
}
else
{
System.Windows.MessageBox.Show("Вы не выбрали критерий поиска.");
return;
}
StreamWriter writeCriterion = new StreamWriter("Criterion.txt");
writeCriterion.Write(criterion.Text);
writeCriterion.Close();
StreamWriter writeSearchCriterion = new StreamWriter("SearchCriterion.txt");
writeSearchCriterion.Write(searchCriterion.Text);
writeSearchCriterion.Close();
JournalEntriesResult journalEntriesResult = new JournalEntriesResult();
journalEntriesResult.Show();
this.Close();
}
private void SaveChanges(object sender, RoutedEventArgs e)
{
if (criterion.Text != string.Empty && searchCriterion.Text != string.Empty)
{
if (criterion.Text == "Марка автомобиля")
{
if (changingCriterion.Text != CheckCar.CheckCarModel(changingCriterion.Text))
{
MessageBox.Show(CheckCar.CheckCarModel(changingCriterion.Text));
return;
}
string query = @"SELECT CarModelCode FROM CarModel WHERE CarModelName = '" + changingCriterion.Text + "'";
int carModelCode = 0;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.HasRows)
{
while (sqlDataReader.Read())
{
carModelCode = Convert.ToInt32(sqlDataReader[0].ToString());
}
}
else
{
MessageBox.Show("Такой марки автомобиля нет в базе.");
return;
}
sqlDataReader.Close();
sqlConnection.Close();
using (SqlConnection updateCarModel = new SqlConnection(connectionString))
using (SqlCommand lastCommnd = updateCarModel.CreateCommand())
{
lastCommnd.CommandText = "UPDATE ViolatorCar SET CarModelCode = @carModel WHERE CarCode = @code";
lastCommnd.Parameters.AddWithValue("@carModel", carModelCode);
lastCommnd.Parameters.AddWithValue("@code", Convert.ToInt32(searchCriterion.Text));
updateCarModel.Open();
lastCommnd.ExecuteNonQuery();
updateCarModel.Close();
}
}
else if (criterion.Text == "Цвет автомобиля")
{
if (changingCriterion.Text != CheckCar.CheckCarColor(changingCriterion.Text))
{
MessageBox.Show(CheckCar.CheckCarColor(changingCriterion.Text));
return;
}
string query = @"SELECT ColorCode FROM Color WHERE ColorName = '" + changingCriterion.Text + "'";
int colorCode = 0;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.HasRows)
{
while (sqlDataReader.Read())
{
colorCode = Convert.ToInt32(sqlDataReader[0].ToString());
}
}
else
{
MessageBox.Show("Такого цвета нет в базе.");
return;
}
sqlDataReader.Close();
sqlConnection.Close();
using (SqlConnection updateColor = new SqlConnection(connectionString))
using (SqlCommand lastCommnd = updateColor.CreateCommand())
{
lastCommnd.CommandText = "UPDATE ViolatorCar SET ColorCode = @color WHERE CarCode = @code";
lastCommnd.Parameters.AddWithValue("@color", colorCode);
lastCommnd.Parameters.AddWithValue("@code", Convert.ToInt32(searchCriterion.Text));
updateColor.Open();
lastCommnd.ExecuteNonQuery();
updateColor.Close();
}
}
else if (criterion.Text == "Номер автомобиля")
{
if (changingCriterion.Text != CheckCar.CheckCarStateNumber(changingCriterion.Text))
{
MessageBox.Show(CheckCar.CheckCarStateNumber(changingCriterion.Text));
return;
}
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
string findReplays = "SELECT CarCode FROM ViolatorCar WHERE CarStatetNumber = '" + changingCriterion.Text + "'";
SqlCommand sqlCommand3 = new SqlCommand(findReplays, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand3.ExecuteReader();
if (sqlDataReader.HasRows)
{
MessageBox.Show("Вы ввели уже занятый номер автомобиля.");
return;
}
else
{
using (SqlConnection updateStateNumber = new SqlConnection(connectionString))
using (SqlCommand lastCommnd = updateStateNumber.CreateCommand())
{
lastCommnd.CommandText = "UPDATE ViolatorCar SET CarStatetNumber = @number WHERE CarCode = @code";
lastCommnd.Parameters.AddWithValue("@number", changingCriterion.Text);
lastCommnd.Parameters.AddWithValue("@code", Convert.ToInt32(searchCriterion.Text));
updateStateNumber.Open();
lastCommnd.ExecuteNonQuery();
updateStateNumber.Close();
}
}
sqlConnection.Close();
}
else if (criterion.Text == "Код владельца")
{
if (changingCriterion.Text == string.Empty)
{
MessageBox.Show("Вы не указали код нарушителя.");
}
else
{
char[] violatorCodeArray = changingCriterion.Text.ToCharArray();
for (int i = 0; i < violatorCodeArray.Length; i++)
{
if (!char.IsDigit(violatorCodeArray[i]))
{
MessageBox.Show("Вы указали в коде недопустимые символы.");
return;
}
}
}
string query = @"SELECT ViolatorCode FROM Violator WHERE ViolatorCode = '" + changingCriterion.Text + "'";
int violatorCode = 0;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.HasRows)
{
while (sqlDataReader.Read())
{
violatorCode = Convert.ToInt32(sqlDataReader[0].ToString());
}
}
else
{
MessageBoxResult meboxResult = MessageBox.Show("Такого нарушителя нет в списке. Можете добавить нового нарушителя.", "Предупреждение", MessageBoxButton.YesNo);
if (meboxResult == MessageBoxResult.Yes)
{
AddViolator addViolator = new AddViolator();
addViolator.Show();
this.Close();
}
return;
}
sqlDataReader.Close();
sqlConnection.Close();
using (SqlConnection updateViolatorCode = new SqlConnection(connectionString))
using (SqlCommand lastCommnd = updateViolatorCode.CreateCommand())
{
lastCommnd.CommandText = "UPDATE ViolatorCar SET ViolatorCode = @violatorCode WHERE CarCode = @code";
lastCommnd.Parameters.AddWithValue("@code", Convert.ToInt32(searchCriterion.Text));
lastCommnd.Parameters.AddWithValue("@violatorCode", violatorCode);
updateViolatorCode.Open();
lastCommnd.ExecuteNonQuery();
updateViolatorCode.Close();
}
}
}
else
{
MessageBox.Show("Вы не выбрали данные для изменения.");
return;
}
MessageBoxResult mboxResult = MessageBox.Show("Данные обновлены. Желаете изменить что-нибудь еще?", "Предупреждение", MessageBoxButton.YesNo);
if (mboxResult == MessageBoxResult.No)
{
ChangeViolatorsCars changeViolarorsCars = new ChangeViolatorsCars();
changeViolarorsCars.Show();
this.Close();
}
}
private void AddButton_Click(object sender, RoutedEventArgs e)
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
if (violationCode.Text == string.Empty)
{
System.Windows.MessageBox.Show("Вы не выбрали код нарушения");
return;
}
if (entryDate.Text != CheckJournalEntry.CheckEnrtyDate(entryDate.Text))
{
System.Windows.MessageBox.Show(CheckJournalEntry.CheckEnrtyDate(entryDate.Text));
return;
}
if (entryTime.Text != CheckJournalEntry.CheckEntryTime(entryTime.Text))
{
System.Windows.MessageBox.Show(CheckJournalEntry.CheckEntryTime(entryTime.Text));
return;
}
if (carStateNumber.Text != CheckCar.CheckCarStateNumber(carStateNumber.Text))
{
System.Windows.MessageBox.Show(CheckCar.CheckCarStateNumber(carStateNumber.Text));
return;
}
int carCode = 0;
string query = @"SELECT CarCode FROM ViolatorCar WHERE CarStatetNumber = '" + carStateNumber.Text + "'";
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
carCode = Convert.ToInt32(dataReader[0].ToString());
}
}
else
{
MessageBoxResult meboxResult = System.Windows.MessageBox.Show("Машины с таким номером нет в базе. Можете добавить добавить её.", "Предупреждение", MessageBoxButton.YesNo);
if (meboxResult == MessageBoxResult.Yes)
{
AddViolatorCar addViolatorCar = new AddViolatorCar();
addViolatorCar.Show();
this.Close();
}
return;
}
dataReader.Close();
sqlConnection.Close();
int inspectorCode = 0;
string login = string.Empty;
StreamReader streamReader = new StreamReader("UserLogin.txt");
login = streamReader.ReadLine();
streamReader.Close();
string query1 = @"SELECT UserCode FROM Users WHERE UserLogin = '******'";
SqlConnection sqlConnection1 = new SqlConnection(connectionString);
sqlConnection1.Open();
SqlCommand sqlCommand1 = new SqlCommand(query1, sqlConnection1);
SqlDataReader dataReader1 = sqlCommand1.ExecuteReader();
if (dataReader1.HasRows)
{
while (dataReader1.Read())
{
inspectorCode = Convert.ToInt32(dataReader1[0].ToString());
}
}
dataReader1.Close();
sqlConnection1.Close();
int statusCode = 0;
string query2 = @"SELECT ViolationStatusCode FROM ViolationStatus WHERE ViolationStatusName = 'Не оплачено'";
SqlConnection sqlConnection2 = new SqlConnection(connectionString);
sqlConnection2.Open();
SqlCommand sqlCommand2 = new SqlCommand(query2, sqlConnection2);
SqlDataReader dataReader2 = sqlCommand2.ExecuteReader();
if (dataReader2.HasRows)
{
while (dataReader2.Read())
{
statusCode = Convert.ToInt32(dataReader2[0].ToString());
}
}
dataReader2.Close();
sqlConnection2.Close();
string findReplays = "SELECT EntryNumber FROM ViolationsJournal WHERE EntryNumberDate = '" + Convert.ToDateTime(entryDate.Text) + "' AND EntryNumberTime = '" + TimeSpan.Parse(entryTime.Text) + "' AND UserCode = " + inspectorCode + " AND ViolationCode = " + Convert.ToInt32(violationCode.Text) + " AND CarCode = " + carCode + " AND ViolationStatusCode = " + statusCode + "";
SqlCommand sqlCommand3 = new SqlCommand(findReplays, connection);
SqlDataReader sqlDataReader = sqlCommand3.ExecuteReader();
if (sqlDataReader.HasRows)
{
System.Windows.MessageBox.Show("Такая запись уже есть в журнале.");
return;
}
else
{
using (SqlConnection journalEntry = new SqlConnection(connectionString))
using (SqlCommand lastCommnd = journalEntry.CreateCommand())
{
lastCommnd.CommandText = "INSERT INTO ViolationsJournal (EntryNumberDate, EntryNumberTime, UserCode, ViolationCode, CarCode, ViolationStatusCode) VALUES (@date, @time, @uc, @vc, @cc, @status)";
lastCommnd.Parameters.AddWithValue("@date", Convert.ToDateTime(entryDate.Text));
lastCommnd.Parameters.AddWithValue("@time", TimeSpan.Parse(entryTime.Text));
lastCommnd.Parameters.AddWithValue("@uc", inspectorCode);
lastCommnd.Parameters.AddWithValue("@vc", Convert.ToInt32(violationCode.Text));
lastCommnd.Parameters.AddWithValue("@cc", carCode);
lastCommnd.Parameters.AddWithValue("@status", statusCode);
journalEntry.Open();
lastCommnd.ExecuteNonQuery();
journalEntry.Close();
}
System.Windows.MessageBox.Show("Запись добавлена в журнал.");
}
sqlDataReader.Close();
connection.Close();
}