private void PublishingFill() //заполнение combo box данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTPublishing.Clear();
publishingDependency.OnChanged += PublishingDependency_Changed;
publishingDependency.Start();
dbTables.DTPublishingFill();
cbPublishing.DataSource = dbTables.DTPublishing;
cbPublishing.ValueMember = "ID_Publishing_Book";
cbPublishing.DisplayMember = "Publishing";
cbPublishing.SelectedValue = -1;
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void GenreFill() //заполнение combo box данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTGenre.Clear();
dbTables.DTGenreFill();
dbTables.dependency.OnChange += ChangeDataGenre;
cbGenre.DataSource = dbTables.DTGenre;
cbGenre.ValueMember = "ID_Genre_Book";
cbGenre.DisplayMember = "Genre";
cbGenre.SelectedValue = -1;
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void BookFill() //заполнение combo box данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTBook.Clear();
dbTables.DTBookForComboBoxFill();
bookDependency.OnChanged += BookDependency_Changed;
bookDependency.Start();
cbBook.DataSource = dbTables.DTBook;
cbBook.ValueMember = "ID_Book";
cbBook.DisplayMember = "Book_Title";
cbBook.SelectedValue = -1;
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void WriterBookFill() //заполнение data grid view данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTWriterBook.Clear();
dbTables.DTWriterBookFill();
filterWriterBook = dbTables.QRWriterBook;
dbTables.dependency.OnChange += ChangeDataWriter;
dgvWriterBook.DataSource = dbTables.DTWriterBook;
dgvWriterBook.Columns[0].Visible = false;
dgvWriterBook.Columns[1].HeaderText = MessageUser.Surname;
dgvWriterBook.Columns[2].HeaderText = MessageUser.Name;
dgvWriterBook.Columns[3].HeaderText = MessageUser.Patronymic;
dgvWriterBook.ClearSelection();
}
catch
{
MessageBox.Show("Ошибка загрузки данных из базы данных!", "Ошибки в результате работы информационной системы", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void UserFill() //заполнение data grid view данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
dbTables.DTUsers.Clear();
dbTables.DTUsersFill();
filterUser = dbTables.QRUsers;
dbTables.dependency.OnChange += ChangeDataUsers;
dgvUsers.DataSource = dbTables.DTUsers;
dgvUsers.Columns[0].Visible = false;
dgvUsers.Columns[1].HeaderText = MessageUser.Surname;
dgvUsers.Columns[2].HeaderText = MessageUser.Name;
dgvUsers.Columns[3].HeaderText = MessageUser.Patronymic;
dgvUsers.Columns[4].HeaderText = MessageUser.Login;
dgvUsers.Columns[5].HeaderText = MessageUser.Password;
dgvUsers.Columns[6].Visible = false;
dgvUsers.Columns[7].HeaderText = MessageUser.Post;
dgvUsers.ClearSelection();
};
Invoke(action);
}
private void ListBookFill() //заполнение data grid view данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTListBookForAct.Clear();
dbTables.DTListBookForActFill();
dbTables.dependency.OnChange += ChangeDataListBook;
dgvListBook.DataSource = dbTables.DTListBookForAct;
dgvListBook.Columns[0].Visible = false;
dgvListBook.Columns[1].HeaderText = MessageUser.TitleBookAct;
dgvListBook.Columns[2].HeaderText = MessageUser.CounPriceAct;
dgvListBook.Columns[3].HeaderText = MessageUser.CountInstanceAct;
dgvListBook.Columns[4].HeaderText = MessageUser.SummAct;
dgvListBook.ClearSelection();
dgvListBook.CurrentCell = null;
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void WriterBookFill() //заполнение combo box данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTWriterBook.Clear();
dbTables.DTWriterForComboBoxFill();
dbTables.dependency.OnChange += ChangeDataWriterBook;
cbWriter.DataSource = dbTables.DTWriterBook;
cbWriter.ValueMember = "ID_Writer";
cbWriter.DisplayMember = "FIO_Writer";
cbWriter.SelectedValue = -1;
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void FormularFill() //заполнение data grid view данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTFormular.Clear();
dbTables.DTFormularFill();
dbTables.dependency.OnChange += ChangeDataFormular;
filterBook = dbTables.QRFormular;
dgvFormular.DataSource = dbTables.DTFormular;
dgvFormular.Columns[0].Visible = false;
dgvFormular.Columns[1].Visible = false;
dgvFormular.Columns[2].HeaderText = MessageUser.ReaderFormular;
dgvFormular.Columns[3].HeaderText = MessageUser.PassportFormular;
dgvFormular.Columns[4].Visible = false;
dgvFormular.Columns[5].HeaderText = MessageUser.BookFormular;
dgvFormular.Columns[6].HeaderText = MessageUser.DateIssueBook;
dgvFormular.Columns[7].HeaderText = MessageUser.NumberDaysIssue;
dgvFormular.Columns[8].HeaderText = MessageUser.DateReturnedBook;
dgvFormular.Columns[9].HeaderText = MessageUser.BookReturned;
dgvFormular.ClearSelection();
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void DataStorageFormular() //запоминание данных для формирования формуляра
{
registrationNumber = dgvRegistrationCard.CurrentRow.Cells[0].Value.ToString();
surname = dgvRegistrationCard.CurrentRow.Cells[1].Value.ToString();
name = dgvRegistrationCard.CurrentRow.Cells[2].Value.ToString();
patronymic = dgvRegistrationCard.CurrentRow.Cells[3].Value.ToString();
passportSeries = dgvRegistrationCard.CurrentRow.Cells[5].Value.ToString();
passportNumber = dgvRegistrationCard.CurrentRow.Cells[6].Value.ToString();
SqlCommand commandFormular = new SqlCommand("", RegistryData.DBConnectionString);
commandFormular.CommandText = "select CONVERT([varchar] (10), [Дата выдачи], 104), CONVERT([varchar] (10), [Дата возврата], 104), [Срок выдачи], [dbo].[Book].[ID_Book], [Название книги], [Писатель] from [dbo].[Formular_Registration_List] inner join [dbo].[Book] on [dbo].[Formular_Registration_List].[Название книги] = [dbo].[Book].[Book_Title] where [Паспорт] = '" + passportSeries + " " + passportNumber + "'";
try
{
DBTables dbTables = new DBTables();
RegistryData.DBConnectionString.Open();
dbTables.CommandOpenKey.ExecuteNonQuery();
dtFormular.Load(commandFormular.ExecuteReader());
dbTables.CommandCloseKey.ExecuteNonQuery();
}
catch (Exception ex)
{
RegistryData.ErrorMessage += "\n" + DateTime.Now.ToLongDateString() + ex.Message;
}
finally
{
RegistryData.DBConnectionString.Close();
}
}
private void ReaderFill() //заполнение combo box данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTRegistrationCard.Clear();
dbTables.DTReaderForComboBoxFill();
regCardDependency.OnChanged += RegCardDependency_Changed;
regCardDependency.Start();
cbReader.DataSource = dbTables.DTRegistrationCard;
cbReader.ValueMember = "ID_Registration_Card_Reader";
cbReader.DisplayMember = "Reader";
cbReader.SelectedValue = -1;
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void chbFiltration_CheckedChanged(object sender, EventArgs e) //изменение check box
{
switch (chbFiltration.CheckState)
{
case (CheckState.Checked): //фильтрация
DataTable data = new DataTable("Book");
commandSearchBook.Notification = null;
commandSearchBook.CommandText = filterBook + " and [Date_Issue_Book] like '%" + tbSearch.Text
+ "%' or [Number_Days_Issue_Book] like '%" + tbSearch.Text + "%' or [Date_Return_Book] like '%" + tbSearch.Text
+ "%' or [Book_Returned] like '%" + tbSearch.Text + "%' or [dbo].[Book].[Book_Title] like '%" + tbSearch.Text
+ "%' or [dbo].[Registration_Card_Reader].[Surname_Reader] like '%" + tbSearch.Text
+ "%' or [dbo].[Registration_Card_Reader].[Name_Reader] like '%" + tbSearch.Text
+ "%' or [dbo].[Registration_Card_Reader].[Patronymic_Reader] like '%" + tbSearch.Text
+ "%' or CONVERT([nvarchar] (4), DECRYPTBYKEY([dbo].[Registration_Card_Reader].[Passport_Series_Reader])) like '%"
+ tbSearch.Text + "%' or CONVERT([nvarchar] (6), DECRYPTBYKEY([dbo].[Registration_Card_Reader].[Passport_Number_Reader])) like '%"
+ tbSearch.Text + "%'";
RegistryData.DBConnectionString.Open();
DBTables dbTables = new DBTables();
dbTables.CommandOpenKey.ExecuteNonQuery();
data.Load(commandSearchBook.ExecuteReader());
dbTables.CommandCloseKey.ExecuteNonQuery();
RegistryData.DBConnectionString.Close();
dgvFormular.DataSource = data;
dgvFormular.Columns[0].Visible = false;
dgvFormular.Columns[1].Visible = false;
dgvFormular.Columns[2].HeaderText = MessageUser.ReaderFormular;
dgvFormular.Columns[3].HeaderText = MessageUser.PassportFormular;
dgvFormular.Columns[4].Visible = false;
dgvFormular.Columns[5].HeaderText = MessageUser.BookFormular;
dgvFormular.Columns[6].HeaderText = MessageUser.DateIssueBook;
dgvFormular.Columns[7].HeaderText = MessageUser.NumberDaysIssue;
dgvFormular.Columns[8].HeaderText = MessageUser.DateReturnedBook;
dgvFormular.Columns[9].HeaderText = MessageUser.BookReturned;
dgvFormular.ClearSelection();
break;
case (CheckState.Unchecked): //поиск
FormularFill();
for (int i = 0; i < dgvFormular.RowCount; i++)
{
for (int j = 0; j < dgvFormular.ColumnCount; j++)
{
if (dgvFormular.Rows[i].Cells[j].Value != null)
{
if (dgvFormular.Rows[i].Cells[j].Value.ToString().Contains(tbSearch.Text))
{
dgvFormular.Rows[i].Selected = true;
break;
}
}
}
}
break;
}
}
private void btnIdentificationReader_Click(object sender, EventArgs e) //клик по кнопке идентификации клиента
{
SqlCommand command = new SqlCommand("", RegistryData.DBConnectionString);
command.CommandText = "select count(*) from [dbo].[Registration_Card_Reader_View] where [Surname_Reader] = '" + tbSurname.Text + "' and [Name_Reader] = '" + tbName.Text + "' and [Patronymic_Reader] = '" + tbPatronymic.Text + "' and [Passport_Series_Reader] = '" + tbPassportSeries.Text + "' and [Passport_Number_Reader] = '" + tbPassportNumber.Text + "'";
try
{
RegistryData.DBConnectionString.Open();
DBTables dbTables = new DBTables();
dbTables.CommandOpenKey.ExecuteNonQuery();
countReader = Convert.ToInt32(command.ExecuteScalar().ToString());
dbTables.CommandCloseKey.ExecuteNonQuery();
}
catch (Exception ex)
{
RegistryData.ErrorMessage += "\n" + DateTime.Now.ToLongDateString() + ex.Message;
}
finally
{
RegistryData.DBConnectionString.Close();
}
if (countReader == 0)
{
MessageBox.Show(MessageUser.NoReader, MessageUser.TitleLibrary, MessageBoxButtons.OK, MessageBoxIcon.Error);
tbSurname.Clear();
tbName.Clear();
tbPatronymic.Clear();
tbPassportSeries.Clear();
tbPassportNumber.Clear();
}
else
{
Thread threadBook = new Thread(BookFill);
Thread threadWriter = new Thread(WriterBookFill);
Thread threadGenre = new Thread(GenreFill);
Thread threadPublishing = new Thread(PublishingFill);
threadBook.Start();
threadWriter.Start();
threadGenre.Start();
threadPublishing.Start();
dgvBook.Enabled = true;
tbSurname.Enabled = false;
tbName.Enabled = false;
tbPatronymic.Enabled = false;
tbPassportSeries.Enabled = false;
tbPassportNumber.Enabled = false;
btnIdentificationReader.Enabled = false;
btnRegistrationReader.Enabled = false;
tbTitleBook.Enabled = true;
cbWriter.Enabled = true;
cbGenre.Enabled = true;
cbPublishing.Enabled = true;
}
}
private void chbFiltration_CheckedChanged(object sender, EventArgs e) //изменение check box
{
switch (chbFiltration.CheckState)
{
case (CheckState.Checked): //фильтрация
DataTable data = new DataTable("User");
commandSearchUser.CommandText = filterUser + " and [Surname_User] like '%" + tbSearch.Text + "%' or [Name_User] like '%" +
tbSearch.Text + "%' or [Patronymic_User] like '%" + tbSearch.Text + "%' or [Login_User] like '%" + tbSearch.Text +
"%' or [Password_User] like '%" + tbSearch.Text + "%' or [Role_Name] like '%" + tbSearch.Text + "%'";
RegistryData.DBConnectionString.Open();
DBTables dbTables = new DBTables();
dbTables.CommandOpenKey.ExecuteNonQuery();
data.Load(commandSearchUser.ExecuteReader());
dbTables.CommandCloseKey.ExecuteNonQuery();
RegistryData.DBConnectionString.Close();
dgvUsers.DataSource = data;
dgvUsers.Columns[0].Visible = false;
dgvUsers.Columns[1].HeaderText = MessageUser.Surname;
dgvUsers.Columns[2].HeaderText = MessageUser.Name;
dgvUsers.Columns[3].HeaderText = MessageUser.Patronymic;
dgvUsers.Columns[4].HeaderText = MessageUser.Login;
dgvUsers.Columns[5].HeaderText = MessageUser.Password;
dgvUsers.Columns[6].Visible = false;
dgvUsers.Columns[7].HeaderText = MessageUser.Post;
dgvUsers.ClearSelection();
break;
case (CheckState.Unchecked): //поиск
UserFill();
for (int i = 0; i < dgvUsers.RowCount; i++)
{
for (int j = 0; j < dgvUsers.ColumnCount; j++)
{
if (dgvUsers.Rows[i].Cells[j].Value != null)
{
if (dgvUsers.Rows[i].Cells[j].Value.ToString().Contains(tbSearch.Text))
{
dgvUsers.Rows[i].Selected = true;
break;
}
}
}
}
break;
}
}
private void GenreBookFill() //заполнение combo box данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
dbTables.DTGenre.Clear();
dbTables.DTGenreFill();
dbTables.dependency.OnChange += ChangeDataGenre;
ltbGenre.DataSource = dbTables.DTGenre;
ltbGenre.ValueMember = "ID_Genre_Book";
ltbGenre.DisplayMember = "Genre";
};
Invoke(action);
}
private void PublishingBookFill() //заполнение combo box данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
dbTables.DTPublishing.Clear();
dbTables.DTPublishingFill();
dbTables.dependency.OnChange += ChangeDataPublishing;
ltbPublishing.DataSource = dbTables.DTPublishing;
ltbPublishing.ValueMember = "ID_Publishing_Book";
ltbPublishing.DisplayMember = "Publishing";
};
Invoke(action);
}
private void RoleUserFill() //заполнение combo box данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
dbTables.DTRoleUser.Clear();
dbTables.DTRoleUserForComboBox();
dbTables.dependency.OnChange += ChangeDataRoleUsers;
cbRole.DataSource = dbTables.DTRoleUser;
cbRole.ValueMember = "ID_Role_User";
cbRole.DisplayMember = "Role_Name";
};
Invoke(action);
}
private void RegistrationCardFill() //заполнение data grid view данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTRegistrationCard.Clear();
dbTables.DTRegistrationCardFill();
regCardDependency.OnChanged += RegCardDependency_Changed;
regCardDependency.Start();
filterWriterBook = dbTables.QRRegistrationCard;
dgvRegistrationCard.DataSource = dbTables.DTRegistrationCard;
dgvRegistrationCard.Columns[0].Visible = false;
dgvRegistrationCard.Columns[1].HeaderText = MessageUser.Surname;
dgvRegistrationCard.Columns[2].HeaderText = MessageUser.Name;
dgvRegistrationCard.Columns[3].HeaderText = MessageUser.Patronymic;
dgvRegistrationCard.Columns[4].HeaderText = MessageUser.DataBirthday;
dgvRegistrationCard.Columns[5].HeaderText = MessageUser.PassportSeries;
dgvRegistrationCard.Columns[6].HeaderText = MessageUser.PassportNumbers;
dgvRegistrationCard.Columns[7].HeaderText = MessageUser.WhoGivePassport;
dgvRegistrationCard.Columns[8].HeaderText = MessageUser.WhenGivePassport;
dgvRegistrationCard.Columns[9].HeaderText = MessageUser.City;
dgvRegistrationCard.Columns[10].HeaderText = MessageUser.Street;
dgvRegistrationCard.Columns[11].HeaderText = MessageUser.Building;
dgvRegistrationCard.Columns[12].HeaderText = MessageUser.Apartment;
dgvRegistrationCard.Columns[14].HeaderText = MessageUser.MobilePhone;
dgvRegistrationCard.Columns[13].HeaderText = MessageUser.HomePhone;
dgvRegistrationCard.Columns[15].HeaderText = MessageUser.Email;
dgvRegistrationCard.Columns[16].HeaderText = MessageUser.HaveBook;
dgvRegistrationCard.Columns[17].Visible = false;
dgvRegistrationCard.ClearSelection();
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
public void BookFill() //заполнение data grid view данными из базы данных
{
DBTables dbTables = new DBTables();
Action action = () =>
{
try
{
dbTables.DTBook.Clear();
bookDependency.OnChanged += BookDependency_Changed;
bookDependency.Start();
dbTables.DTBookFill();
filterBook = dbTables.QRBook;
dgvBook.DataSource = dbTables.DTBook;
dgvBook.Columns[0].Visible = false;
dgvBook.Columns[1].HeaderText = MessageUser.BookTitle;
dgvBook.Columns[2].Visible = false;
dgvBook.Columns[3].HeaderText = MessageUser.WriterBook;
dgvBook.Columns[4].Visible = false;
dgvBook.Columns[5].HeaderText = MessageUser.GenreBook;
dgvBook.Columns[6].Visible = false;
dgvBook.Columns[7].HeaderText = MessageUser.PublishingBook;
dgvBook.Columns[8].HeaderText = MessageUser.PublicationDate;
dgvBook.Columns[9].HeaderText = MessageUser.NumberOfPages;
dgvBook.Columns[10].HeaderText = MessageUser.NumberISBN;
dgvBook.Columns[11].HeaderText = MessageUser.TheCostOfInstance;
dgvBook.Columns[12].HeaderText = MessageUser.TotalNumberOfInstances;
dgvBook.Columns[13].HeaderText = MessageUser.NumberOfInstancesAvailable;
dgvBook.Columns[14].HeaderText = MessageUser.DateRistrationBook;
dgvBook.ClearSelection();
}
catch
{
MessageBox.Show(MessageUser.ErrorLoadingData, MessageUser.TitleError, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
Invoke(action);
}
private void PersonalDataFill() //загрузка данных
{
Action action = () =>
{
DBTables dbTables = new DBTables();
dbTables.DTPersonalCabinet.Clear();
userDependency.OnChanged += UserDependency_Changed;
userDependency.Start();
dbTables.DTPersonalCabinetFill();
tbSurname.Text = dbTables.DTPersonalCabinet.Rows[0]["Surname_User"].ToString();
tbName.Text = dbTables.DTPersonalCabinet.Rows[0]["Name_User"].ToString();
tbPatronymic.Text = dbTables.DTPersonalCabinet.Rows[0]["Patronymic_User"].ToString();
tbLogin.Text = dbTables.DTPersonalCabinet.Rows[0]["Column1"].ToString();
tbPassword.Text = dbTables.DTPersonalCabinet.Rows[0]["Column2"].ToString();
tbRepeatPassword.Text = tbPassword.Text;
};
Invoke(action);
}
private void btnReturnBook_Click(object sender, EventArgs e) //клик по кнопке возврата книги
{
SqlCommand commandReader = new SqlCommand("", RegistryData.DBConnectionString);
commandReader.CommandText = "select count(*) from [dbo].[Registration_Card_Reader_View] where [Surname_Reader] = '" + tbSurname.Text + "' and [Name_Reader] = '" + tbName.Text + "' and [Patronymic_Reader] = '" + tbPatronymic.Text + "' and [Passport_Series_Reader] = '" + tbPassportSeries.Text + "' and [Passport_Number_Reader] = '" + tbPassportNumber.Text + "'";
try //проверка наличия читателя
{
RegistryData.DBConnectionString.Open();
DBTables dbTables = new DBTables();
dbTables.CommandOpenKey.ExecuteNonQuery();
countReader = Convert.ToInt32(commandReader.ExecuteScalar().ToString());
dbTables.CommandCloseKey.ExecuteNonQuery();
}
catch (Exception ex)
{
RegistryData.ErrorMessage += "\n" + DateTime.Now.ToLongDateString() + ex.Message;
}
finally
{
RegistryData.DBConnectionString.Close();
}
if (countReader == 0) //если такого чиитателя нет
{
MessageBox.Show(MessageUser.NoReaderLibrary, MessageUser.TitleLibrary, MessageBoxButtons.OK, MessageBoxIcon.Error);
tbSurname.Clear();
tbName.Clear();
tbPatronymic.Clear();
tbPassportSeries.Clear();
tbPassportNumber.Clear();
}
else
{
try
{
SqlCommand commandIDReader = new SqlCommand("", RegistryData.DBConnectionString);
commandIDReader.CommandText = "select [ID_Registration_Card_Reader] from [dbo].[Registration_Card_Reader_View] where [Surname_Reader] = '" + tbSurname.Text + "' and [Name_Reader] = '" + tbName.Text + "' and [Patronymic_Reader] = '" + tbPatronymic.Text + "' and [Passport_Series_Reader] = '" + tbPassportSeries.Text + "' and [Passport_Number_Reader] = '" + tbPassportNumber.Text + "'";
RegistryData.DBConnectionString.Open();
DBTables dbTables = new DBTables();
dbTables.CommandOpenKey.ExecuteNonQuery();
idReader = Convert.ToInt32(commandIDReader.ExecuteScalar().ToString());
dbTables.CommandCloseKey.ExecuteNonQuery();
SqlCommand commandIDFormular = new SqlCommand("", RegistryData.DBConnectionString);
commandIDFormular.CommandText = "select [ID_Formular_Reader] from [dbo].[Formular_Reader] where [Book_Returned] = 0 and [Registration_Card_Reader_ID] = " + idReader.ToString();
idFormular = Convert.ToInt32(commandIDFormular.ExecuteScalar().ToString());
}
catch (Exception ex)
{
RegistryData.ErrorMessage += "\n" + DateTime.Now.ToLongDateString() + ex.Message;
}
finally
{
RegistryData.DBConnectionString.Close();
}
if (idFormular > 0)
{
storedProcedure.SPFormularReturnBookUpdate(idFormular);
MessageBox.Show(MessageUser.BookReturnedSucc, MessageUser.TitleLibrary, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btnIssueBook_Click(object sender, EventArgs e) //клик по кнопке выдать книгу
{
SqlCommand commandBookOnHandReader = new SqlCommand("", RegistryData.DBConnectionString);
SqlCommand commandIDRegistrationCard = new SqlCommand("", RegistryData.DBConnectionString);
commandBookOnHandReader.CommandText = "select [Book_On_Hand_Reader] from [dbo].[Registration_Card_Reader_View] where [Surname_Reader] = '" + tbSurname.Text + "' and [Name_Reader] = '" + tbName.Text + "' and [Patronymic_Reader] = '" + tbPatronymic.Text + "' and [Passport_Series_Reader] = '" + tbPassportSeries.Text + "' and [Passport_Number_Reader] = '" + tbPassportNumber.Text + "'";
commandIDRegistrationCard.CommandText = "select [ID_Registration_Card_Reader] from [dbo].[Registration_Card_Reader_View] where [Surname_Reader] = '" + tbSurname.Text + "' and [Name_Reader] = '" + tbName.Text + "' and [Patronymic_Reader] = '" + tbPatronymic.Text + "' and [Passport_Series_Reader] = '" + tbPassportSeries.Text + "' and [Passport_Number_Reader] = '" + tbPassportNumber.Text + "'";
try
{
RegistryData.DBConnectionString.Open();
DBTables dbTables = new DBTables();
dbTables.CommandOpenKey.ExecuteNonQuery();
bookOnReader = Convert.ToInt32(commandBookOnHandReader.ExecuteScalar().ToString());
idRegistrationCardReader = Convert.ToInt32(commandIDRegistrationCard.ExecuteScalar().ToString());
dbTables.CommandCloseKey.ExecuteNonQuery();
}
catch (Exception ex)
{
RegistryData.ErrorMessage += "\n" + DateTime.Now.ToLongDateString() + ex.Message;
}
finally
{
RegistryData.DBConnectionString.Close();
}
if (bookOnReader == 1)
{
MessageBox.Show(MessageUser.ReaderNoReturnedBook, MessageUser.TitleLibrary, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
issueBook = dtpDateIssueBook.Value.ToString("yyyy-MM-dd");
storedProcedure.SPFormularReaderInsert(issueBook, Convert.ToInt32(nudNumberDays.Value), idRegistrationCardReader, Convert.ToInt32(dgvBook.CurrentRow.Cells[0].Value.ToString()));
MessageBox.Show(MessageUser.BookIssue, MessageUser.TitleLibrary, MessageBoxButtons.OK, MessageBoxIcon.Error);
btnIssueBook.Enabled = false;
nudNumberDays.Enabled = false;
dgvBook.DataSource = null;
dgvBook.Enabled = false;
btnCheckBook.Enabled = false;
cbGenre.SelectedIndex = -1;
cbGenre.Enabled = false;
cbPublishing.Enabled = false;
cbPublishing.Enabled = false;
cbWriter.Enabled = false;
cbWriter.Enabled = false;
tbTitleBook.Enabled = false;
tbTitleBook.Clear();
tbPassportNumber.Enabled = true;
tbPassportNumber.Clear();
tbPassportSeries.Enabled = true;
tbPassportSeries.Clear();
tbPatronymic.Enabled = true;
tbPatronymic.Clear();
tbName.Enabled = true;
tbName.Clear();
tbSurname.Enabled = true;
tbSurname.Clear();
btnIdentificationReader.Enabled = true;
btnRegistrationReader.Enabled = true;
}
}
private void chbFiltration_CheckedChanged(object sender, EventArgs e) //изменение check box
{
switch (chbFiltration.CheckState)
{
case (CheckState.Checked): //фильтрация
DataTable data = new DataTable("Registration_Card_Reader");
commandSearchCard.CommandText = filterWriterBook + "and [Surname_Reader] like '%" +
tbSearch.Text + "%' or [Name_Reader] like '%" + tbSearch.Text + "%' or [Patronymic_Reader] like '%" +
tbSearch.Text + "%' or CONVERT([varchar] (10), [dbo].[Registration_Card_Reader].[Birthday_Reader], 104) like '%" + tbSearch.Text + "%' or CONVERT([nvarchar] (4), DECRYPTBYKEY([dbo].[Registration_Card_Reader].[Passport_Series_Reader])) like '%" +
tbSearch.Text + "%' or CONVERT([nvarchar] (6), DECRYPTBYKEY([dbo].[Registration_Card_Reader].[Passport_Number_Reader])) like '%" + tbSearch.Text + "%' or [Who_Give_Passport_Reader] like '%"
+ tbSearch.Text + "%' or CONVERT([varchar] (10), [dbo].[Registration_Card_Reader].[When_Give_Passport_Reader], 104) like '%" + tbSearch.Text + "%' or [Town_Reader] like '%" +
tbSearch.Text + "%' or CONVERT([nvarchar] (50), DECRYPTBYKEY([dbo].[Registration_Card_Reader].[Street_Reader])) like '%" + tbSearch.Text + "%' or [Building_Reader] like '%" + tbSearch.Text
+ "%' or [Apartment_Reader] like '%" + tbSearch.Text + "%' or CONVERT([nvarchar] (15), DECRYPTBYKEY([dbo].[Registration_Card_Reader].[Home_Phone_Reader])) like '%" + tbSearch.Text +
"%' or CONVERT([nvarchar] (15), DECRYPTBYKEY([dbo].[Registration_Card_Reader].[Mobile_Phone_Reader])) like '%" + tbSearch.Text + "%' or CONVERT([nvarchar] (129), DECRYPTBYKEY([dbo].[Registration_Card_Reader].[Email_Reader])) like '%" + tbSearch.Text +
"%' or [Book_On_Hand_Reader] like '%" + tbSearch.Text + "%'";
RegistryData.DBConnectionString.Open();
DBTables dbTables = new DBTables();
dbTables.CommandOpenKey.ExecuteNonQuery();
data.Load(commandSearchCard.ExecuteReader());
dbTables.CommandCloseKey.ExecuteNonQuery();
RegistryData.DBConnectionString.Close();
dgvRegistrationCard.DataSource = data;
dgvRegistrationCard.Columns[0].Visible = false;
dgvRegistrationCard.Columns[1].HeaderText = MessageUser.Surname;
dgvRegistrationCard.Columns[2].HeaderText = MessageUser.Name;
dgvRegistrationCard.Columns[3].HeaderText = MessageUser.Patronymic;
dgvRegistrationCard.Columns[4].HeaderText = MessageUser.DataBirthday;
dgvRegistrationCard.Columns[5].HeaderText = MessageUser.PassportSeries;
dgvRegistrationCard.Columns[6].HeaderText = MessageUser.PassportNumbers;
dgvRegistrationCard.Columns[7].HeaderText = MessageUser.WhoGivePassport;
dgvRegistrationCard.Columns[8].HeaderText = MessageUser.WhenGivePassport;
dgvRegistrationCard.Columns[9].HeaderText = MessageUser.City;
dgvRegistrationCard.Columns[10].HeaderText = MessageUser.Street;
dgvRegistrationCard.Columns[11].HeaderText = MessageUser.Building;
dgvRegistrationCard.Columns[12].HeaderText = MessageUser.Apartment;
dgvRegistrationCard.Columns[14].HeaderText = MessageUser.MobilePhone;
dgvRegistrationCard.Columns[13].HeaderText = MessageUser.HomePhone;
dgvRegistrationCard.Columns[15].HeaderText = MessageUser.Email;
dgvRegistrationCard.Columns[16].HeaderText = MessageUser.HaveBook;
dgvRegistrationCard.ClearSelection();
break;
case (CheckState.Unchecked): //поиск
RegistrationCardFill();
for (int i = 0; i < dgvRegistrationCard.RowCount; i++)
{
for (int j = 0; j < dgvRegistrationCard.ColumnCount; j++)
{
if (dgvRegistrationCard.Rows[i].Cells[j].Value != null)
{
if (dgvRegistrationCard.Rows[i].Cells[j].Value.ToString().Contains(tbSearch.Text))
{
dgvRegistrationCard.Rows[i].Selected = true;
break;
}
}
}
}
break;
}
}