private void btnAdd_Click(object sender, EventArgs e)
{
add = !add;
if (add)
{
WriteMode();
maskBreak.Text = maskInit.Text = maskEnd.Text = "0000";
btnEdit.Enabled = false;
}
else
{
bool valid = false;
ReadMode();
DB database = new DB();
database.SetPreferences(filepath);
MySqlCommand command = new MySqlCommand("select * from schedules where Name = @nam", database.getConnection());
command.Parameters.Add("@nam", MySqlDbType.VarChar).Value = maskInit.Text + " - " + maskEnd.Text + " | Обед: " + maskBreak.Text;
try
{
DataTable checktable = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
database.openConnection();
adapter.Fill(checktable);
if (checktable.Rows.Count > 0)
{
valid = false; MessageBox.Show("Такая запись уже существует", "Ошибка"); return;
}
else
{
valid = true;
}
}
catch
{
MessageBox.Show("Ошибка работы базы данных", "Ошибка");
}
finally
{
database.closeConnection();
}
if (valid)
{
command = new MySqlCommand("insert into schedules(Name,TimeInit,TimeEnd,Lunch) values (@nam,@tii,@tie,@lun);", database.getConnection());
command.Parameters.Add("@nam", MySqlDbType.VarChar).Value = maskInit.Text + " - " + maskEnd.Text + " | Обед: " + maskBreak.Text;
command.Parameters.Add("@tii", MySqlDbType.Time).Value = TimeSpan.Parse(maskInit.Text + ":00");
command.Parameters.Add("@tie", MySqlDbType.Time).Value = TimeSpan.Parse(maskBreak.Text + ":00");
command.Parameters.Add("@lun", MySqlDbType.Time).Value = TimeSpan.Parse(maskBreak.Text + ":00");
try
{
database.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Запись создана");
}
else
{
MessageBox.Show("Ошибка создания записи");
}
}
catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); }
finally
{
database.closeConnection();
}
UpdateData();
}
btnEdit.Enabled = true;
}
}
private void UpdateData()
{
table.Clear();
DB database = new DB();
database.SetPreferences(filepath);
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand("select * from schedules order by TimeInit", database.getConnection());
adapter.SelectCommand = command;
try
{
database.openConnection();
adapter.Fill(table);
}
catch { MessageBox.Show("Ошибка базы данных", "Ошибка"); }
finally { database.closeConnection(); }
listschedules.Items.Clear();
for (int i = 0; i < table.Rows.Count; i++)
{
listschedules.Items.Add(table.Rows[i].Field <String>("Name"));
}
btnEdit.Enabled = btnDelete.Enabled = false;
maskInit.Text = maskEnd.Text = maskBreak.Text = "0000";
}
private void btnEdit_Click(object sender, EventArgs e)
{
edit = !edit;
if (edit)
{
WriteMode();
btnAdd.Enabled = false;
}
else
{
btnAdd.Enabled = true;
ReadMode();
DB database = new DB();
database.SetPreferences(filepath);
string name = maskInit.Text + " - " + maskEnd.Text + " | Обед: " + maskBreak.Text;
MySqlCommand command = new MySqlCommand("update schedules set Name = @nam , TimeInit = @tii , TimeEnd = @tie , Lunch = @lun where Id = @id;", database.getConnection());
command.Parameters.Add("@id", MySqlDbType.UInt32).Value = table.Rows[listschedules.SelectedIndex].Field <UInt32>("Id");
command.Parameters.Add("@nam", MySqlDbType.VarChar).Value = name;
command.Parameters.Add("@tii", MySqlDbType.Time).Value = TimeSpan.Parse(maskInit.Text + ":00");
command.Parameters.Add("@tie", MySqlDbType.Time).Value = TimeSpan.Parse(maskEnd.Text + ":00");
command.Parameters.Add("@lun", MySqlDbType.Time).Value = TimeSpan.Parse(maskBreak.Text + ":00");
try
{
database.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Запись изменена");
}
else
{
MessageBox.Show("Ошибка изменения записи");
}
}
catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); }
finally
{
database.closeConnection();
}
UpdateData();
btnAdd.Enabled = true;
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (edit)
{
edit = false;
ReadMode();
btnEdit.Enabled = false;
btnAdd.Enabled = true;
}
else if (add)
{
add = false; ReadMode(); maskBreak.Text = maskEnd.Text = maskInit.Text = "00:00";
}
else if (MessageBox.Show("Подтвердите удаление записи", "Подтвердите действие", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
DB database = new DB();
database.SetPreferences(filepath);
MySqlCommand command = new MySqlCommand("delete from schedules where Id = @id;", database.getConnection());
command.Parameters.Add("@id", MySqlDbType.UInt32).Value = table.Rows[listschedules.SelectedIndex].Field <UInt32>("Id");
try { database.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Запись удалена");
}
else
{
MessageBox.Show("Запись не удалена");
} }
catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); }
finally { database.closeConnection(); }
}
UpdateData();
}
private void panelbtncheck_Click(object sender, EventArgs e)
{
bool connection = false;
if (paneltextadr.TextLength > 0 && paneltextport.TextLength > 0 && paneltextname.TextLength > 0 && paneltextpass.TextLength > 0 && paneltextdbname.TextLength > 0)
{
DisableContents();
string cmd = "server=" + paneltextadr.Text + ";port=" + paneltextport.Text + ";username="******";password="******";database=" + paneltextdbname.Text;
File.WriteAllText(filepath, cmd);
DB database = new DB();
database.SetPreferences(filepath);
if (database.checkConnection())
{
DisableContents();
connection = true;
}
else
{
MessageBox.Show("Проверка не выполена");
EnableContents();
connection = false;
}
if (connection == true && MessageBox.Show("Соединение с базой данных установлено.\nСоздать структуру базы данных?\n(Неоходимо при первом запуске системы)", "Уведомление", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
MySqlCommand command = new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `schedules` " +
"(`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT," +
" `Name` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," +
" `TimeInit` TIME NOT NULL," +
" `TimeEnd` TIME NOT NULL," +
" `Lunch` TIME NULL DEFAULT NULL," +
" PRIMARY KEY(`Id`))" +
" ENGINE = InnoDB" +
" AUTO_INCREMENT = 14" +
" DEFAULT CHARACTER SET = utf8;" +
" CREATE UNIQUE INDEX `Id_UNIQUE` ON `schedules` (`Id` ASC) VISIBLE;" +
" CREATE UNIQUE INDEX `Name_UNIQUE` ON `schedules` (`Name` ASC) VISIBLE;" +
" CREATE TABLE IF NOT EXISTS `userstatus` (" +
" `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT," +
" `Status` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," +
" PRIMARY KEY(`Id`))" +
" ENGINE = InnoDB" +
" AUTO_INCREMENT = 3" +
" DEFAULT CHARACTER SET = utf8;" +
" CREATE UNIQUE INDEX `Id_UNIQUE` ON `userstatus` (`Id` ASC) VISIBLE;" +
" CREATE TABLE IF NOT EXISTS `user` (" +
" `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT," +
" `Name` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," +
" `Surname` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," +
" `Lastname` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL," +
" `Login` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," +
" `Password` VARCHAR(45) CHARACTER SET 'utf8' NOT NULL," +
" `Access` INT UNSIGNED NOT NULL," +
" `StatusId` INT UNSIGNED NOT NULL," +
" `ScheduleId` INT UNSIGNED NOT NULL DEFAULT '0'," +
" PRIMARY KEY(`Id`, `ScheduleId`, `StatusId`)," +
" CONSTRAINT `ScheduleId`" +
" FOREIGN KEY(`ScheduleId`)" +
" REFERENCES `schedules` (`Id`)" +
" ON DELETE RESTRICT" +
" ON UPDATE CASCADE," +
" CONSTRAINT `StatusId`" +
" FOREIGN KEY(`StatusId`)" +
" REFERENCES `userstatus` (`Id`)" +
" ON DELETE NO ACTION" +
" ON UPDATE NO ACTION)" +
" ENGINE = InnoDB" +
" AUTO_INCREMENT = 19" +
" DEFAULT CHARACTER SET = utf8;" +
" CREATE UNIQUE INDEX `Id_UNIQUE` ON `user` (`Id` ASC) VISIBLE;" +
" CREATE UNIQUE INDEX `Login_UNIQUE` ON `user` (`Login` ASC) VISIBLE;" +
" CREATE INDEX `ScheduleId_idx` ON `user` (`StatusId` ASC) VISIBLE;" +
" CREATE INDEX `ScheduleId_idx1` ON `user` (`ScheduleId` ASC) VISIBLE;" +
" CREATE TABLE IF NOT EXISTS `activities` (" +
" `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT," +
" `UserId` INT UNSIGNED NOT NULL," +
" `ActivityDate` DATE NOT NULL," +
" `ActivityTimeInit` TIME NOT NULL," +
" `ActivityTimeEnd` TIME NOT NULL DEFAULT '00:00:00'," +
" `ActivityHours` TIME NOT NULL DEFAULT '00:00:00'," +
" `Lunch` INT UNSIGNED NOT NULL DEFAULT '1'," +
" PRIMARY KEY(`Id`, `UserId`)," +
" CONSTRAINT `UserId`" +
" FOREIGN KEY(`UserId`)" +
" REFERENCES `user` (`Id`)" +
" ON DELETE RESTRICT" +
" ON UPDATE CASCADE)" +
" ENGINE = InnoDB" +
" AUTO_INCREMENT = 66" +
" DEFAULT CHARACTER SET = utf8;" +
" CREATE UNIQUE INDEX `Id_UNIQUE` ON `activities` (`Id` ASC) VISIBLE;" +
" CREATE INDEX `UserId_idx` ON `activities` (`UserId` ASC) VISIBLE;" +
" INSERT INTO schedules(Name, TimeInit, TimeEnd, Lunch) VALUES" +
" (\"DEFAULT\", \"00:00:00\", \"00:00:00\", \"00:00:00\");" +
" INSERT INTO `userstatus` (`Id`, `Status`) VALUES(1, 'Неактивен');" +
" INSERT INTO `userstatus` (`Id`, `Status`) VALUES(2, 'Активен');" +
" INSERT INTO `user` (`Name`, `Surname`, `Lastname`, `Login`, `Password`, `Access`, `StatusId`, `ScheduleId`) VALUES('SERVER', 'ACCOUNT', NULL, 'admin', 'admin', 2, 2, (select Id from schedules where Name = \"DEFAULT\"));" +
" COMMIT;", database.getConnection());
try
{
database.openConnection();
if (command.ExecuteNonQuery() == 1)
{
}
;
}
catch
{
MessageBox.Show("Ошибка создания объектов БД", "Ошибка");
}
finally
{
database.closeConnection();
MessageBox.Show("Элементы БД созданы", "Уведомление");
}
database.End();
database = null;
}
}
else
{
MessageBox.Show("Введите данные БД");
}
}
