private void checkUserServer2ConnectionButton_Click(object sender, EventArgs e)
{
using (SqlConnection testConnection = new SqlConnection())
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder()
{
DataSource = userServer2NameTextBox.Text,
InitialCatalog = userServer2DatabaseTextBox.Text,
UserID = LoginForm.UserName,
Password = LoginForm.Password
};
testConnection.ConnectionString = sConnB.ConnectionString;
testConnection.Open();
if (testConnection.State == ConnectionState.Open)
{
MessageBox.Show("Соединение успешно произошло");
}
}
catch (Exception ex)
{
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при проверке подключения к второму пользовательскому серверу в checkUserServer2ConnectionButton_Click.");
MessageBox.Show("Ошибка при проверке подключения к второму пользовательскому серверу.\nВызвано исключение: " + ex.Message);
}
finally
{
testConnection.Close();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
}
// Удаляет запись о прокате вещи
private void deleteRentButton_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите удалить выбранную запись?\n",
"Предупреждение", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
Int32.TryParse(rentIdLabel.Text, out int rentId);
if (rentId == 0)
{
MessageBox.Show("Выберите запись для удаления.");
}
else
{
ResidentRoomsRentThing residentRoomsRentThing = db.GetTable <ResidentRoomsRentThing>().SingleOrDefault(r => r.ResidentRoomsRentThingsId == rentId);
db.GetTable <ResidentRoomsRentThing>().DeleteOnSubmit(residentRoomsRentThing);
db.SubmitChanges();
LoadRentDataGridView();
}
}
catch (Exception ex)
{
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при удалении информации о прокате вещей в deleteRentButton_Click.");
MessageBox.Show("Ошибка при удалении информации о прокате вещей.\nВызвано исключение:" + ex.Message);
}
}
}
/// <summary>
/// Добавление проката вещи
/// </summary>
private void RentThing()
{
try
{
ResidentRooms residentRooms = db.GetTable <ResidentRooms>()
.FirstOrDefault(r => (r.ResidentId == residentId && r.DateOfEviction == null));
string selectedRentName = rentThingsComboBox.SelectedItem.ToString();
RentThing rentThing = db.GetTable <RentThing>().SingleOrDefault(r => r.Name == selectedRentName);
ResidentRoomsRentThing residentRoomsRentThing = new ResidentRoomsRentThing
{
ResidentRoomsId = residentRooms.ResidentRoomsId,
RentThingId = rentThing.RentThingId,
StartRentDate = startRentDateTimePicker.Value.Date,
EndRentDate = endRentDateTimePicker.Value.Date
};
db.GetTable <ResidentRoomsRentThing>().InsertOnSubmit(residentRoomsRentThing);
db.SubmitChanges();
LoadRentDataGridView();
SystemSounds.Beep.Play();
MessageBox.Show("Прокат успешно добавлен!");
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при добавлении проката вещей.");
MessageBox.Show("Ошибка проката. Проверьте выбранные данные.\nВызвано исключение: " + ex.Message);
}
}
// Создание резервной копии базы данных
public static void BackupDatabase(string databaseName, string folderPath)
{
using (SqlConnection sqlConnection = new SqlConnection("Data Source =.\\SQLEXPRESS; Integrated Security = True"))
{
try
{
sqlConnection.Open();
sql = "BACKUP DATABASE " + databaseName + " TO DISK = '" + folderPath + "\\"
+ databaseName + "-" + DateTime.Now.ToString("dd-MM-yyyy-hh/mm/ss") + ".bak'";
command = new SqlCommand(sql, sqlConnection);
command.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection.Dispose();
SystemSounds.Exclamation.Play();
}
catch (Exception ex)
{
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при создании резервной копии базы данных в BackupHelper.backupDatabase.");
}
finally
{
sqlConnection.Close();
}
}
}
/// <summary>
/// Отвечает за выселения жителя из комнаты
/// </summary>
/// <param name="roomId">Идентификатор комнаты</param>
/// <param name="residentId">Идентификатор жителя</param>
private void EvictResident(int roomId, int residentId)
{
DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите выселить данного жителя?\n",
"Предупреждение", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
Resident resident = db.GetTable <Resident>().FirstOrDefault(r => r.ResidentId == residentId);
RoomResidents roomResidents = db.GetTable <RoomResidents>().FirstOrDefault(r => (r.ResidentId == residentId && r.RoomId == roomId));
db.GetTable <RoomResidents>().DeleteOnSubmit(roomResidents);
ResidentRooms residentRooms = db.GetTable <ResidentRooms>()
.FirstOrDefault(r => (r.ResidentId == residentId && r.RoomId == roomId && r.DateOfEviction == null));
residentRooms.DateOfEviction = DateTime.Now;
db.SubmitChanges();
SystemSounds.Beep.Play();
MessageBox.Show("Житель успешно выселен!");
HistoryRecordsController.WriteAboutSettlement(residentRooms, false);
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при выселении жителя.");
MessageBox.Show("Ошибка выселения жителя. \nВызвано исключение: " + ex.Message);
}
}
}
// Восстановление бд с резервной копии
public static void RestoreDatabase(string databaseName, string folderPath)
{
using (SqlConnection myConn = new SqlConnection("Data Source =.\\SQLEXPRESS; Integrated Security = True"))
{
try
{
myConn.Open();
sql = "use master " +
" ALTER DATABASE " + databaseName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
sql += "RESTORE DATABASE " + databaseName + " FROM DISK = '" + folderPath + "' WITH REPLACE;";
command = new SqlCommand(sql, myConn);
command.ExecuteNonQuery();
myConn.Close();
myConn.Dispose();
SystemSounds.Exclamation.Play();
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при восстановление базы данных с резервной копии в BackupHelper.restoreDatabase.");
}
finally
{
myConn.Close();
}
}
}
// Попытка войти в систему
private void enterButton_Click(object sender, EventArgs e)
{
using (SqlConnection sqlConnection = new SqlConnection())
{
string userName = userNameTextBox.Text;
string password = passwordTextBox.Text;
if (string.IsNullOrWhiteSpace(userName) || string.IsNullOrWhiteSpace(password))
{
SystemSounds.Exclamation.Play();
MessageBox.Show("Введите логин и пароль!");
return;
}
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServerName,
InitialCatalog = Properties.Settings.Default.userServerDatabase,
UserID = userName,
Password = password,
};
sqlConnection.ConnectionString = sConnB.ConnectionString;
sqlConnection.Open();
HistoryRecordsController.ChangeUser(userName);
HistoryRecordsController.WriteAboutSystemEnter(true);
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
MainForm mainForm = new MainForm(this, sConnB);
mainForm.Show();
this.Hide();
LoginForm.UserName = userName;
LoginForm.Password = password;
userNameTextBox.Clear();
passwordTextBox.Clear();
}
catch (SqlException sqlEx)
{
HistoryRecordsController.ChangeUser(userName);
HistoryRecordsController.WriteAboutSystemEnter(false);
HistoryRecordsController.WriteExceptionToLogFile(sqlEx, "Не удалось выполнить вход. Введены неверный логин и(или) пароль.");
SystemSounds.Exclamation.Play();
MessageBox.Show("Не удалось выполнить вход.\nУбедитесь, что введены правильные логин и(или) пароль.", "Ошибка");
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при входе.");
MessageBox.Show(ex.Message);
}
finally
{
sqlConnection.Close();
}
}
}
// Поиск организации по названию
private void searchButton_Click(object sender, EventArgs e)
{
try
{
dataTable.DefaultView.RowFilter = string.Format("[{0}] LIKE '%{1}%'", "Название", nameFilterTextBox.Text);
organizationsDataGridView.ClearSelection();
}
catch (Exception ex)
{
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при поиске организации в searchButton_Click.");
MessageBox.Show("Ошибка при поиске организации в searchButton_Click.\nВызвано исключение:" + ex.Message);
}
}
// Сохраняет изменения о прокате вещи
private void saveRentButton_Click(object sender, EventArgs e)
{
if (startRentDateTimePicker.Value.Date > endRentDateTimePicker.Value.Date)
{
SystemSounds.Exclamation.Play();
MessageBox.Show("Нельзя добавить данный прокат. Дата начала проката должна быть раньше даты окончания.");
return;
}
DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите сохранить изменения?\n",
"Предупреждение", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
Int32.TryParse(rentIdLabel.Text, out int rentId);
if (rentId == 0)
{
MessageBox.Show("Выберите запись для изменения.");
}
else
{
ResidentRoomsRentThing residentRoomsRentThing = db.GetTable <ResidentRoomsRentThing>().SingleOrDefault(r => r.ResidentRoomsRentThingsId == rentId);
string selectedRentName = rentThingsComboBox.SelectedItem.ToString();
RentThing rentThing = db.GetTable <RentThing>().SingleOrDefault(r => r.Name == selectedRentName);
residentRoomsRentThing.RentThingId = rentThing.RentThingId;
residentRoomsRentThing.StartRentDate = startRentDateTimePicker.Value.Date;
residentRoomsRentThing.EndRentDate = endRentDateTimePicker.Value.Date;
db.SubmitChanges();
SystemSounds.Beep.Play();
MessageBox.Show("Успешно сохранено!");
LoadRentDataGridView();
}
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при сохранении изменений о прокате вещей в saveRentButton_Click.");
MessageBox.Show("Ошибка при сохранении изменений о прокате вещей.\nВызвано исключение:" + ex.Message);
}
}
}
// Запуск выбранного sql-скрипта
private void runSqlScriptButton_Click(object sender, EventArgs e)
{
SqlConnection myConn = new SqlConnection("Data Source =.\\SQLEXPRESS; Integrated Security = True");
string script = File.ReadAllText(sqlScriptFileTextBox.Text);
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
IEnumerable <string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
try
{
myConn.Open();
foreach (string commandString in commandStrings)
{
if (commandString.Trim() != "")
{
using (var command = new SqlCommand(commandString, myConn))
{
command.ExecuteNonQuery();
}
}
}
SystemSounds.Beep.Play();
MessageBox.Show("Успешно выполено");
SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServerName,
InitialCatalog = Properties.Settings.Default.userServerDatabase,
IntegratedSecurity = true
};
HistoryRecordsController.ChangeSqlConnection(sConnB.ConnectionString);
}
catch (Exception ex)
{
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при запуске sql-скрипта в runSqlScriptButton_Click.");
MessageBox.Show("Ошибка при запуске sql-скрипта.\nВызвано исключение: " + ex.Message);
}
finally
{
myConn.Close();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
// Восстановление бд с резервной копии
private void restoreButton_Click(object sender, EventArgs e)
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
BackupHelper.RestoreDatabase(userServerDatabaseTextBox.Text, restoreFilePathTextBox.Text);
SystemSounds.Exclamation.Play();
MessageBox.Show("Успешно восстановлено!");
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при восстановление базы данных с резервной копии в restoreButton_Click.");
MessageBox.Show("Ошибка при восстановление базы данных с резервной копии.\nВызвано исключение: " + ex.Message);
}
finally
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
/// <summary>
/// Загружает информацию для вкладок элемента TabControl главного окна
/// </summary>
private void LoadTabs()
{
sectionsTabControl.TabPages.Clear();
try
{
// SqlConnection sqlConnection2 = new SqlConnection(sqlConnectionString);
db = new DataContext(sqlConnectionString.ConnectionString);
Table <Section> sections = db.GetTable <Section>();
foreach (var section in sections)
{
LoadSection(section);
}
}
catch (Exception ex)
{
MessageBox.Show("Ошибка при загрузке секций. \nВызвано исключение: " + ex.Message);
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка загрузки секций");
}
}
private void updateButton_Click(object sender, EventArgs e)
{
try
{
SqlConnectionStringBuilder sConnBForOtherServer = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServer2Name,
InitialCatalog = Properties.Settings.Default.userServer2Database,
UserID = sqlConnectionString.UserID,
Password = sqlConnectionString.Password,
};
updateOrganizations(sConnBForOtherServer.ConnectionString);
updateOrganizations(sqlConnectionString.ConnectionString);
LoadDataGrid();
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка обновления данных организаций в updateButton_Click.");
MessageBox.Show("Ошибка объединения данных организаций в updateButton_Click.\nВызвано исключение:" + ex.Message);
}
}
// Сохранение настроек программы
private void saveSettingsButton_Click(object sender, EventArgs e)
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Properties.Settings.Default.userServerName = userServerNameTextBox.Text;
Properties.Settings.Default.userServerDatabase = userServerDatabaseTextBox.Text;
Properties.Settings.Default.userServer2Name = userServer2NameTextBox.Text;
Properties.Settings.Default.userServer2Database = userServer2DatabaseTextBox.Text;
Properties.Settings.Default.adminServerName = adminServerNameTextBox.Text;
Properties.Settings.Default.adminServerDatabase = adminServerDatabaseTextBox.Text;
Properties.Settings.Default.backupsFolderPath = backupsFolderPathTextBox.Text;
Properties.Settings.Default.restoreFilePath = restoreFilePathTextBox.Text;
Properties.Settings.Default.exceptionsLogFolderPath = exceptionsLogFolderPathTextBox.Text;
Properties.Settings.Default.Save();
SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServerName,
InitialCatalog = Properties.Settings.Default.userServerDatabase,
IntegratedSecurity = true
};
HistoryRecordsController.ChangeSqlConnection(sConnB.ConnectionString);
SystemSounds.Exclamation.Play();
MessageBox.Show("Настройки успешно сохранены!");
}
catch (Exception ex)
{
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при сохранении настроек программы в saveSettingsButton_Click.");
MessageBox.Show("Ошибка при сохранении настроек программы.\nВызвано исключение: " + ex.Message);
}
finally
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
// Проверка существования базы данных. Если бд не существует, открывается окно
// выбора sql-скрипта для создания новой базы данных.
private void LoginForm_Load(object sender, EventArgs e)
{
try
{
SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServerName,
InitialCatalog = Properties.Settings.Default.userServerDatabase,
IntegratedSecurity = true
};
sqlConnection = new SqlConnection(sConnB.ConnectionString);
sqlConnection.Open();
HistoryRecordsController.ChangeSqlConnection(sConnB.ConnectionString);
}
catch (SqlException sqlEx)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(sqlEx, "Ошибка подключения к базе данных при запуске программы.");
MessageBox.Show("Не удалось подключиться к базе данных. Ошибка:\n" + sqlEx.Message, "Ошибка");
OpenFileDialog dlg = new OpenFileDialog();
string filePath = "";
dlg.Filter = "Sql scripts(*.sql)|*.sql|All Files(*.*)|*.*";
dlg.FilterIndex = 0;
if (dlg.ShowDialog() == DialogResult.OK)
{
filePath = dlg.FileName;
}
SqlConnectionStringBuilder sConnB2 = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServerName,
IntegratedSecurity = true
};
SqlConnection myConn = new SqlConnection(sConnB2.ConnectionString);
string script = File.ReadAllText(filePath);
IEnumerable <string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
try
{
myConn.Open();
foreach (string commandString in commandStrings)
{
if (commandString.Trim() != "")
{
using (var command = new SqlCommand(commandString, myConn))
{
command.ExecuteNonQuery();
}
}
}
SystemSounds.Beep.Play();
MessageBox.Show("База данных создана");
}
catch (SqlException ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка создания базы данных через sql-скрипт.");
MessageBox.Show("Ошибка создания базы данных:\n" + sqlEx.Message);
}
finally
{
myConn.Close();
}
}
finally
{
sqlConnection.Close();
}
}
// Сохранение информации о жителе
private void saveButton_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите сохранить изменения?\n",
"Предупреждение", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
if (isAllFieldsValid() == false)
{
return;
}
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
db = new DataContext(sqlConnectionString.ConnectionString);
Resident resident = db.GetTable <Resident>().SingleOrDefault(r => r.ResidentId == residentId);
Resident oldResident = resident.ShallowCopy();
int organizationId = Int32.Parse(organizationIdLabel.Text);
resident.Surname = surnameTextBox.Text;
resident.Name = nameTextBox.Text;
resident.Patronymic = patronymicTextBox.Text;
resident.PhoneNumber = phoneNumberTextBox.Text;
resident.Birthday = birthdayDateTimePicker.NullableValue();
resident.Note = noteTextBox.Text;
resident.Organization = db.GetTable <Organization>().SingleOrDefault(r => r.OrganizationId == organizationId);
Passport passport = db.GetTable <Passport>().SingleOrDefault(p => p.PassportId == resident.PassportId);
oldResident.Passport = passport.ShallowCopy();
passport.Number = passportNumberTextBox.Text;
passport.Series = passportSeriesTextBox.Text;
passport.Registration = passportRegistrationTextBox.Text;
passport.DateOfIssue = passportDateOfIssueDateTimePicker.Value.Date;
passport.Authority = passportAuthorityTextBox.Text;
SqlConnectionStringBuilder sConnBForOtherServer = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServer2Name,
InitialCatalog = Properties.Settings.Default.userServer2Database,
UserID = sqlConnectionString.UserID,
Password = sqlConnectionString.Password,
};
DataContext dbFromOtherServer = new DataContext(sConnBForOtherServer.ConnectionString);
Resident resident2 = dbFromOtherServer.GetTable <Resident>().SingleOrDefault(r => r.ResidentId == residentId);
resident2.Surname = surnameTextBox.Text;
resident2.Name = nameTextBox.Text;
resident2.Patronymic = patronymicTextBox.Text;
resident2.PhoneNumber = phoneNumberTextBox.Text;
resident2.Birthday = birthdayDateTimePicker.NullableValue();
resident2.Note = noteTextBox.Text;
resident2.Organization = dbFromOtherServer.GetTable <Organization>().SingleOrDefault(r => r.OrganizationId == organizationId);
Passport passport2 = dbFromOtherServer.GetTable <Passport>().SingleOrDefault(p => p.PassportId == resident2.PassportId);
passport2.Number = passportNumberTextBox.Text;
passport2.Series = passportSeriesTextBox.Text;
passport2.Registration = passportRegistrationTextBox.Text;
passport2.DateOfIssue = passportDateOfIssueDateTimePicker.Value.Date;
passport2.Authority = passportAuthorityTextBox.Text;
dbFromOtherServer.SubmitChanges();
db.SubmitChanges();
SystemSounds.Beep.Play();
MessageBox.Show("Успешно сохранено!");
HistoryRecordsController.WriteAboutEditResident(oldResident, resident);
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при сохранении изменений жителя.");
MessageBox.Show("Ошибка при сохранении изменений. \nВызвано исключение: " + ex.Message);
}
finally
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
}
// Удаляет выбранного жителя
private void deleteButton_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите удалить выбранного жителя?\n",
"Предупреждение", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
Int32.TryParse(residentIdLabel.Text, out int residentId);
if (residentId == 0)
{
MessageBox.Show("Выберите жителя");
}
else
{
SqlConnectionStringBuilder sConnBForOtherServer = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServer2Name,
InitialCatalog = Properties.Settings.Default.userServer2Database,
UserID = sqlConnectionString.UserID,
Password = sqlConnectionString.Password,
};
DataContext dbFromOtherServer = new DataContext(sConnBForOtherServer.ConnectionString);
Resident resident2 = dbFromOtherServer.GetTable <Resident>().FirstOrDefault(r => r.ResidentId == residentId);
if (resident2 != null)
{
dbFromOtherServer.GetTable <Resident>().DeleteOnSubmit(resident2);
}
Passport passport2 = dbFromOtherServer.GetTable <Passport>().FirstOrDefault(r => (r.PassportId == resident2.PassportId));
if (passport2 != null)
{
resident2.Passport = passport2;
dbFromOtherServer.GetTable <Passport>().DeleteOnSubmit(passport2);
}
RoomResidents roomResidents2 = dbFromOtherServer.GetTable <RoomResidents>().FirstOrDefault(r => (r.ResidentId == residentId));
if (roomResidents2 != null)
{
dbFromOtherServer.GetTable <RoomResidents>().DeleteOnSubmit(roomResidents2);
}
ResidentRooms residentRooms2 = dbFromOtherServer.GetTable <ResidentRooms>()
.FirstOrDefault(r => (r.ResidentId == residentId && r.DateOfEviction == null));
if (residentRooms2 != null)
{
residentRooms2.DateOfEviction = DateTime.Now;
}
db = new DataContext(sqlConnectionString.ConnectionString);
Resident resident = db.GetTable <Resident>().FirstOrDefault(r => r.ResidentId == residentId);
if (resident != null)
{
db.GetTable <Resident>().DeleteOnSubmit(resident);
}
Passport passport = db.GetTable <Passport>().FirstOrDefault(r => (r.PassportId == resident.PassportId));
if (passport != null)
{
resident.Passport = passport;
db.GetTable <Passport>().DeleteOnSubmit(passport);
}
RoomResidents roomResidents = db.GetTable <RoomResidents>().FirstOrDefault(r => (r.ResidentId == residentId));
if (roomResidents != null)
{
db.GetTable <RoomResidents>().DeleteOnSubmit(roomResidents);
}
ResidentRooms residentRooms = db.GetTable <ResidentRooms>()
.FirstOrDefault(r => (r.ResidentId == residentId && r.DateOfEviction == null));
if (residentRooms != null)
{
residentRooms.DateOfEviction = DateTime.Now;
}
dbFromOtherServer.SubmitChanges();
db.SubmitChanges();
LoadDataGrid();
HistoryRecordsController.WriteAboutAddDeleteResident(resident, false);
}
}
catch (Exception ex)
{
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при удалении жителя в deleteButton_Click.");
MessageBox.Show("Ошибка при удалении жителя.\nВызвано исключение: " + ex.Message);
}
finally
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
}
// Обновить список паспортов, загрузив с другого сервера
private void updatePassports(string userServerConnectionString)
{
using (SqlConnection userServer2Connection = new SqlConnection())
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
SqlConnectionStringBuilder userServer2ConnB = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServer2Name,
InitialCatalog = Properties.Settings.Default.userServer2Database,
UserID = LoginForm.UserName,
Password = LoginForm.Password
};
userServer2Connection.ConnectionString = userServer2ConnB.ConnectionString;
userServer2Connection.Open();
if (userServer2Connection.State == ConnectionState.Open)
{
DataTable updateDataTable = new DataTable("UpdatePassports");
SqlDataAdapter updateDataAdapter = new SqlDataAdapter("SELECT PassportId, " +
"Number, Series, Registration, DateOfIssue, Authority " +
"FROM Passports", userServer2Connection);
updateDataAdapter.Fill(updateDataTable);
using (SqlConnection sqlConnection = new SqlConnection(userServerConnectionString))
{
try
{
sqlConnection.Open();
string tmpTable = "create table #Passports " +
"(PassportId int, Number nvarchar(20), Series nvarchar(20), " +
"Registration nvarchar(100), DateOfIssue datetime, Authority nvarchar(100))";
SqlCommand cmd = new SqlCommand(tmpTable, sqlConnection);
cmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
{
bulkCopy.ColumnMappings.Add("PassportId", "PassportId");
bulkCopy.ColumnMappings.Add("Number", "Number");
bulkCopy.ColumnMappings.Add("Series", "Series");
bulkCopy.ColumnMappings.Add("Registration", "Registration");
bulkCopy.ColumnMappings.Add("DateOfIssue", "DateOfIssue");
bulkCopy.ColumnMappings.Add("Authority", "Authority");
bulkCopy.DestinationTableName = "#Passports";
try
{
bulkCopy.WriteToServer(updateDataTable);
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка копирования данных во временную таблицу #Residents.");
MessageBox.Show("Ошибка копирования данных во временную таблицу #Residents.\nВызвано исключение:" + ex.Message);
}
}
cmd.CommandText = "SET IDENTITY_INSERT Passports ON";
cmd.ExecuteNonQuery();
string mergeSql = "merge into Passports as Target " +
"using #Passports as Source " +
"on " +
"Target.PassportId=Source.PassportId " +
"when matched then " +
"update set Target.Number=Source.Number, Target.Series=Source.Series, " +
"Target.Registration=Source.Registration, Target.DateOfIssue=Source.DateOfIssue, " +
"Target.Authority=Source.Authority " +
"when not matched then " +
"insert (PassportId, Number, Series, Registration, DateOfIssue, Authority) " +
"values (Source.PassportId, Source.Number, Source.Series, Source.Registration, Source.DateOfIssue, Source.Authority) " +
"when not matched by Source then delete; ";
cmd.CommandText = mergeSql;
cmd.ExecuteNonQuery();
cmd.CommandText = "SET IDENTITY_INSERT Passports OFF;";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table #Passports";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка объединения данных паспортов из двух таблиц.");
MessageBox.Show("Ошибка объединения данных паспортов из двух таблиц.\nВызвано исключение: " + ex.Message);
}
finally
{
sqlConnection.Close();
}
}
}
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка обновления данных паспортов в updatePassports.");
MessageBox.Show("Ошибка обновления данных паспортов в updatePassports.\nВызвано исключение:" + ex.Message);
}
finally
{
userServer2Connection.Close();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
}
// Обновить список жителей, загрузив с другого сервера
private void updateResidents(string userServerConnectionString)
{
using (SqlConnection userServer2Connection = new SqlConnection())
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
SqlConnectionStringBuilder userServer2ConnB = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServer2Name,
InitialCatalog = Properties.Settings.Default.userServer2Database,
UserID = LoginForm.UserName,
Password = LoginForm.Password
};
userServer2Connection.ConnectionString = userServer2ConnB.ConnectionString;
userServer2Connection.Open();
if (userServer2Connection.State == ConnectionState.Open)
{
DataTable updateDataTable = new DataTable("UpdateResidents");
SqlDataAdapter updateDataAdapter = new SqlDataAdapter("SELECT ResidentId, " +
"Surname, Name, Patronymic, PhoneNumber, Birthday, Note, PassportId, OrganizationId " +
"FROM Residents", userServer2Connection);
updateDataAdapter.Fill(updateDataTable);
using (SqlConnection sqlConnection = new SqlConnection(userServerConnectionString))
{
try
{
sqlConnection.Open();
string tmpTable = "create table #Residents " +
"(ResidentId int, Surname nvarchar(50), Name nvarchar(50), " +
"Patronymic nvarchar(50), PhoneNumber nvarchar(50), Birthday datetime, " +
"Note nvarchar(100), PassportId int, OrganizationId int)";
SqlCommand cmd = new SqlCommand(tmpTable, sqlConnection);
cmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
{
bulkCopy.ColumnMappings.Add("ResidentId", "ResidentId");
bulkCopy.ColumnMappings.Add("Surname", "Surname");
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("Patronymic", "Patronymic");
bulkCopy.ColumnMappings.Add("PhoneNumber", "PhoneNumber");
bulkCopy.ColumnMappings.Add("Birthday", "Birthday");
bulkCopy.ColumnMappings.Add("Note", "Note");
bulkCopy.ColumnMappings.Add("PassportId", "PassportId");
bulkCopy.ColumnMappings.Add("OrganizationId", "OrganizationId");
bulkCopy.DestinationTableName = "#Residents";
try
{
bulkCopy.WriteToServer(updateDataTable);
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка копирования данных во временную таблицу #Residents.");
MessageBox.Show("Ошибка копирования данных во временную таблицу #Residents.\nВызвано исключение:" + ex.Message);
}
}
cmd.CommandText = "SET IDENTITY_INSERT Residents ON";
cmd.ExecuteNonQuery();
string mergeSql = "merge into Residents as Target " +
"using #Residents as Source " +
"on " +
"Target.ResidentId=Source.ResidentId " +
"when matched then " +
"update set Target.Surname=Source.Surname, Target.Name=Source.Name, " +
"Target.Patronymic=Source.Patronymic, Target.PhoneNumber=Source.PhoneNumber, " +
"Target.Birthday=Source.Birthday, Target.Note=Source.Note, " +
"Target.PassportId=Source.PassportId, Target.OrganizationId=Source.OrganizationId " +
"when not matched then " +
"insert (ResidentId, Surname, Name, Patronymic, PhoneNumber, Birthday, Note, PassportId, OrganizationId) " +
"values (Source.ResidentId, Source.Surname, Source.Name, Source.Patronymic, Source.PhoneNumber, Source.Birthday, Source.Note, Source.PassportId, Source.OrganizationId) " +
"when not matched by Source then delete; ";
cmd.CommandText = mergeSql;
cmd.ExecuteNonQuery();
cmd.CommandText = "SET IDENTITY_INSERT Residents OFF";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table #Residents";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка объединения данных организаций из двух таблиц.");
MessageBox.Show("Ошибка объединения данных жителей из двух таблиц.\nВызвано исключение: " + ex.Message);
}
finally
{
sqlConnection.Close();
}
}
}
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка обновления данных жителей в updateResidents.");
MessageBox.Show("Ошибка обновления данных жителей в updateResidents.\nВызвано исключение:" + ex.Message);
}
finally
{
userServer2Connection.Close();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
}
// Обновить список организаций, загрузив с сервера администратора
private void updateOrganizations(string userServerConnectionString)
{
using (SqlConnection adminConnection = new SqlConnection())
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
SqlConnectionStringBuilder adminConnB = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.adminServerName,
InitialCatalog = Properties.Settings.Default.adminServerDatabase,
UserID = LoginForm.UserName,
Password = LoginForm.Password
};
adminConnection.ConnectionString = adminConnB.ConnectionString;
adminConnection.Open();
if (adminConnection.State == ConnectionState.Open)
{
DataTable updateDataTable = new DataTable("UpdateOrganizations");
SqlDataAdapter updateDataAdapter = new SqlDataAdapter("SELECT OrganizationId as [ИД], " +
"Name as [Название], PhysicAddress as [Адрес] " +
"FROM Organizations", adminConnection);
updateDataAdapter.Fill(updateDataTable);
using (SqlConnection sqlConnection = new SqlConnection(userServerConnectionString))
{
try
{
sqlConnection.Open();
string tmpTable = "create table #Organizations " +
"(OrganizationId int, Name nvarchar(100), Address nvarchar(100))";
SqlCommand cmd = new SqlCommand(tmpTable, sqlConnection);
cmd.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
{
bulkCopy.ColumnMappings.Add("[ИД]", "OrganizationId");
bulkCopy.ColumnMappings.Add("[Название]", "Name");
bulkCopy.ColumnMappings.Add("[Адрес]", "Address");
bulkCopy.DestinationTableName = "#Organizations";
try
{
bulkCopy.WriteToServer(updateDataTable);
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка копирования данных во временную таблицу #Organizations.");
MessageBox.Show("Ошибка копирования данных во временную таблицу #Organizations.\nВызвано исключение:" + ex.Message);
}
}
string mergeSql = "merge into Organizations as Target " +
"using #Organizations as Source " +
"on " +
"Target.OrganizationId=Source.OrganizationId " +
"when matched then " +
"update set Target.Name=Source.Name, Target.Address=Source.Address " +
"when not matched then " +
"insert (OrganizationId,Name,Address) values (Source.OrganizationId,Source.Name,Source.Address) " +
"when not matched by Source then delete; ";
cmd.CommandText = mergeSql;
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table #Organizations";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка объединения данных организаций из двух таблиц.");
MessageBox.Show("Ошибка объединения данных организаций из двух таблиц.\nВызвано исключение: " + ex.Message);
}
finally
{
sqlConnection.Close();
}
}
}
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка обновления данных организаций в updateOrganizations.");
MessageBox.Show("Ошибка обновления данных организаций в updateOrganizations.\nВызвано исключение:" + ex.Message);
}
finally
{
adminConnection.Close();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
}
// Добавление нового жителя
private void addButton_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите добавить данного жителя?\n",
"Предупреждение", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
if (isAllFieldsValid() == false)
{
return;
}
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
// Для сброса контекста, если была попытка ввода нового значения
db = new DataContext(sqlConnectionString.ConnectionString);
string surname = surnameTextBox.Text;
string name = nameTextBox.Text;
string patronymic = patronymicTextBox.Text;
string phoneNumber = phoneNumberTextBox.Text;
DateTime?birthday = birthdayDateTimePicker.NullableValue();
string passportSeries = passportSeriesTextBox.Text;
string passportNumber = passportNumberTextBox.Text;
string passportRegistration = passportRegistrationTextBox.Text;
string note = noteTextBox.Text;
DateTime passportDateOfIssue = passportDateOfIssueDateTimePicker.Value.Date;
string passportAuthority = passportAuthorityTextBox.Text;
int organizationId = Int32.Parse(organizationIdLabel.Text);
Organization organization = db.GetTable <Organization>().SingleOrDefault(r => r.OrganizationId == organizationId);
Passport passport = new Passport
{
Series = passportSeries,
Number = passportNumber,
Registration = passportRegistration,
DateOfIssue = passportDateOfIssue,
Authority = passportAuthority
};
db.GetTable <Passport>().InsertOnSubmit(passport);
Resident resident = new Resident
{
Surname = surname,
Name = name,
Patronymic = patronymic,
PhoneNumber = phoneNumber,
Birthday = birthday,
Note = note,
Passport = passport,
Organization = organization,
};
db.GetTable <Resident>().InsertOnSubmit(resident);
SqlConnectionStringBuilder sConnBForOtherServer = new SqlConnectionStringBuilder()
{
DataSource = Properties.Settings.Default.userServer2Name,
InitialCatalog = Properties.Settings.Default.userServer2Database,
UserID = sqlConnectionString.UserID,
Password = sqlConnectionString.Password,
};
DataContext dbFromOtherServer = new DataContext(sConnBForOtherServer.ConnectionString);
Organization organization2 = dbFromOtherServer.GetTable <Organization>().SingleOrDefault(r => r.OrganizationId == organizationId);
Passport passport2 = new Passport
{
Series = passportSeries,
Number = passportNumber,
Registration = passportRegistration,
DateOfIssue = passportDateOfIssue,
Authority = passportAuthority
};
dbFromOtherServer.GetTable <Passport>().InsertOnSubmit(passport2);
Resident resident2 = new Resident
{
Surname = surname,
Name = name,
Patronymic = patronymic,
PhoneNumber = phoneNumber,
Birthday = birthday,
Note = note,
Passport = passport2,
Organization = organization2,
};
dbFromOtherServer.GetTable <Resident>().InsertOnSubmit(resident2);
dbFromOtherServer.SubmitChanges();
db.SubmitChanges();
SystemSounds.Beep.Play();
MessageBox.Show("Успешно добавлен!");
HistoryRecordsController.WriteAboutAddDeleteResident(resident, true);
Close();
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при добавлении жителя.");
MessageBox.Show("Ошибка при добавлении жителя.\nВызвано исключение: " + ex.Message);
}
finally
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}
}
// Сохраняет изменения о проживании
private void saveLivingButton_Click(object sender, EventArgs e)
{
if (settlementDateDateTimePicker.Value.Date > dateOfEvictionDateTimePicker.NullableValue())
{
SystemSounds.Exclamation.Play();
MessageBox.Show("Дата заселения должна быть раньше даты выселения.");
return;
}
DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите сохранить изменения?\n",
"Предупреждение", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
Int32.TryParse(residentRoomsIdLabel.Text, out int residentRoomsId);
if (residentRoomsId == 0)
{
MessageBox.Show("Выберите запись для изменения.");
}
else
{
int sectonNumber = Int32.Parse(sectionNumberTextBox.Text);
int roomNumber = Int32.Parse(roomNumberTextBox.Text);
Room room = db.GetTable <Room>().SingleOrDefault(r => r.SectionNumber == sectonNumber && r.Number == roomNumber);
if (room != null)
{
ResidentRooms residentRooms = db.GetTable <ResidentRooms>().SingleOrDefault(r => r.ResidentRoomsId == residentRoomsId);
if (residentRooms.RoomId != room.RoomId)
{
RoomResidents roomResidents = db.GetTable <RoomResidents>().FirstOrDefault(r => (r.ResidentId == residentId && r.RoomId == residentRooms.RoomId));
db.GetTable <RoomResidents>().DeleteOnSubmit(roomResidents);
RoomResidents newRoomResidents = new RoomResidents
{
RoomId = room.RoomId,
ResidentId = residentRooms.ResidentId
};
db.GetTable <RoomResidents>().InsertOnSubmit(newRoomResidents);
}
residentRooms.RoomId = room.RoomId;
residentRooms.CashPayment = (bool)((ComboBoxItem)cashPaymentComboBox.SelectedItem).HiddenValue;
residentRooms.BedClothes = (bool)((ComboBoxItem)bedClothesComboBox.SelectedItem).HiddenValue;
residentRooms.SettlementDate = settlementDateDateTimePicker.Value;
residentRooms.DateOfEviction = dateOfEvictionDateTimePicker.NullableValue();
db.SubmitChanges();
SystemSounds.Beep.Play();
MessageBox.Show("Успешно сохранено!");
LoadLivingDataGridView();
}
else
{
MessageBox.Show("Введённой комнаты не существует.");
}
}
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при сохранении изменений о проживании в saveLivingButton_Click.");
MessageBox.Show("Ошибка при сохранении изменений.\nВызвано исключение:" + ex.Message);
}
}
}
// Заселяет жителя
private void settleButton_Click(object sender, EventArgs e)
{
if (startRentDateTimePicker.Value.Date > endRentDateTimePicker.Value.Date)
{
SystemSounds.Exclamation.Play();
MessageBox.Show("Нельзя добавить данный прокат. Дата начала проката должна быть раньше даты окончания.");
return;
}
DialogResult dialogResult = MessageBox.Show("Вы уверены, что хотите заселить данного жителя?\n",
"Предупреждение", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
Int32.TryParse(residentIdLabel.Text, out int residentId);
Resident resident = db.GetTable <Resident>().SingleOrDefault(r => r.ResidentId == residentId);
var exist = db.GetTable <RoomResidents>().Any(r => r.ResidentId == resident.ResidentId);
if (exist)
{
SystemSounds.Exclamation.Play();
MessageBox.Show("Данный житель уже живет в другой комнате");
}
else
{
RoomResidents roomResidents = new RoomResidents
{
RoomId = roomId,
ResidentId = resident.ResidentId
};
db.GetTable <RoomResidents>().InsertOnSubmit(roomResidents);
bool isCash = cashRadioButton.Checked ? true : false;
ResidentRooms residentRooms = new ResidentRooms
{
ResidentId = resident.ResidentId,
RoomId = roomId,
CashPayment = isCash,
BedClothes = bedClothesCheckBox.Checked,
SettlementDate = settlementDateTimePicker.Value
};
db.GetTable <ResidentRooms>().InsertOnSubmit(residentRooms);
db.SubmitChanges();
if (isRentCheckBox.Checked)
{
RentThing rentThing = db.GetTable <RentThing>().SingleOrDefault(r => r.Name == "Телевизор");
ResidentRoomsRentThing residentRoomsRentThing = new ResidentRoomsRentThing
{
ResidentRoomsId = residentRooms.ResidentRoomsId,
RentThingId = rentThing.RentThingId,
StartRentDate = startRentDateTimePicker.Value.Date,
EndRentDate = endRentDateTimePicker.Value.Date
};
db.GetTable <ResidentRoomsRentThing>().InsertOnSubmit(residentRoomsRentThing);
}
db.SubmitChanges();
HistoryRecordsController.WriteAboutSettlement(residentRooms, true);
DialogResult = DialogResult.OK;
Close();
}
}
catch (Exception ex)
{
SystemSounds.Exclamation.Play();
HistoryRecordsController.WriteExceptionToLogFile(ex, "Ошибка при заселении жителя в settleButton_Click.");
MessageBox.Show("Ошибка при заселении жителя.\nВызвано исключение: " + ex.Message);
}
}
}
