private void btn_empcreate_Click(object sender, EventArgs e)
{
createmode = !createmode;
if (createmode)
{
WriteMode();
ClearFields();
btn_empedit.Enabled = false;
btn_empdelete.Enabled = true;
btn_empdelete.Text = "Отмена";
comboacc.SelectedIndex = combostat.SelectedIndex = 0;
}
else
{
ReadMode();
if (ValidateReg())
{
DB database = new DB();
database.SetPreferences(filepath);
MySqlCommand command = new MySqlCommand("INSERT INTO user (Name,Surname,Lastname,Login,Password,Access,StatusId,ScheduleId) values (@nam,@sur,@las,@log,@pas,@acc,@sta,@sch);", database.getConnection());
command.Parameters.Add("@nam", MySqlDbType.VarChar).Value = textnam.Text;
command.Parameters.Add("@sur", MySqlDbType.VarChar).Value = textsur.Text;
command.Parameters.Add("@las", MySqlDbType.VarChar).Value = textlas.Text;
command.Parameters.Add("@log", MySqlDbType.VarChar).Value = textlog.Text;
command.Parameters.Add("@pas", MySqlDbType.VarChar).Value = textpas.Text;
command.Parameters.Add("@acc", MySqlDbType.VarChar).Value = comboacc.SelectedIndex + 1;
command.Parameters.Add("@sta", MySqlDbType.VarChar).Value = combostat.SelectedIndex + 1;
command.Parameters.Add("@sch", MySqlDbType.UInt32).Value = schedule.Rows[combosched.SelectedIndex].Field <UInt32>("Id");
try
{
database.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Аккаунт был создан");
}
else
{
MessageBox.Show("Аккаунт не был создан");
}
}
catch { MessageBox.Show("Ошибка базы данных", "Ошибка"); }
finally { database.closeConnection(); }
}
btn_empdelete.Text = "Удалить";
employeestolist();
}
}
//BUTTONS---------------------------------------------------------
private void btn_empedit_Click(object sender, EventArgs e)
{
editmode = !editmode;
if (editmode)
{
WriteMode();
btn_empcreate.Enabled = listemployee.Enabled = false;
btn_empdelete.Text = "Отмена";
}
else
{
ReadMode();
if (ValidateEdit())
{
DB database = new DB();
database.SetPreferences(filepath);
MySqlCommand command = new MySqlCommand("UPDATE user SET Name = @nam,Surname=@sur,Lastname=@las,Login=@log,Password=@pas,Access=@acc,StatusId=@sta,ScheduleId=@sch WHERE Id = @id", database.getConnection());
command.Parameters.Add("@id", MySqlDbType.UInt32).Value = employeetable.Rows[listemployee.SelectedIndex].Field <UInt32>("Id");
command.Parameters.Add("@nam", MySqlDbType.VarChar).Value = textnam.Text;
command.Parameters.Add("@sur", MySqlDbType.VarChar).Value = textsur.Text;
command.Parameters.Add("@las", MySqlDbType.VarChar).Value = textlas.Text;
command.Parameters.Add("@log", MySqlDbType.VarChar).Value = textlog.Text;
command.Parameters.Add("@pas", MySqlDbType.VarChar).Value = textpas.Text;
command.Parameters.Add("@acc", MySqlDbType.VarChar).Value = comboacc.SelectedIndex + 1;
command.Parameters.Add("@sta", MySqlDbType.VarChar).Value = combostat.SelectedIndex + 1;
command.Parameters.Add("@sch", MySqlDbType.UInt32).Value = schedule.Rows[combosched.SelectedIndex].Field <UInt32>("Id");
try
{
database.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Аккаунт был изменён");
}
else
{
MessageBox.Show("Аккаунт не был изменён");
}
}
catch { MessageBox.Show("Ошибка базы данных", "Ошибка"); }
finally { database.closeConnection(); }
}
employeestolist();
}
}
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 bool Authorise()
{
String login = textbox_login.Text;
String password = textbox_password.Text;
DB database = new DB();
database.SetPreferences(filepath);
MySqlDataAdapter adapter = new MySqlDataAdapter();
bool islogged = false;
MySqlCommand command = new MySqlCommand("SELECT Id, Name, Surname, Lastname, Access, StatusId, ScheduleId FROM user WHERE Login = @log AND Password = @pas;", database.getConnection());
command.Parameters.Add("@log", MySqlDbType.VarChar).Value = login;
command.Parameters.Add("@pas", MySqlDbType.VarChar).Value = password;
adapter.SelectCommand = command;
try
{
database.openConnection();
adapter.Fill(table);
}
catch
{
MessageBox.Show("Ошибка работы с базой данных", "Ошибка");
}
finally
{
database.closeConnection();
}
if (table.Rows.Count > 0)
{
islogged = true;
}
else
{
islogged = false;
}
return(islogged);
}
private void btn_empdelete_Click(object sender, EventArgs e)
{
if (!createmode && !editmode)
{
if (MessageBox.Show("Удаление учётной записи приведёт к\nкаскадному удалению всех записей о ней.\nВы уверены?", "Подтвердите действие", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
DB database = new DB();
database.SetPreferences(filepath);
MySqlCommand command = new MySqlCommand("Delete from user where Id=@id;", database.getConnection());
command.Parameters.Add("@id", MySqlDbType.UInt32).Value = employeetable.Rows[listemployee.SelectedIndex].Field <UInt32>("Id");
try
{
database.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Аккаунт был удалён");
}
else
{
MessageBox.Show("Аккаунт не был удалён");
}
}
catch { MessageBox.Show("Ошибка базы данных", "Ошибка"); }
finally { database.closeConnection(); }
employeestolist();
}
}
if (createmode)
{
createmode = false; ClearFields(); employeestolist(); ReadMode(); btn_empdelete.Text = "Удалить";
}
else if (editmode)
{
editmode = false; ClearFields(); employeestolist(); ReadMode(); btn_empdelete.Text = "Удалить";
}
}
private bool ValidateReg()
{
bool validated = true;
DB database = new DB();
database.SetPreferences(filepath);
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand("SELECT * FROM user WHERE Login = @log;", database.getConnection());
command.Parameters.Add("@log", MySqlDbType.VarChar).Value = this.textlog.Text;
try { database.openConnection(); adapter.SelectCommand = command; adapter.Fill(table); }
catch { MessageBox.Show("Ошибка базы данных при проверке", "Ошибка"); validated = false; }
finally { database.closeConnection(); }
if (table.Rows.Count > 0)
{
MessageBox.Show("Запись с логином: " + this.textlog.Text + "\nуже существует!\nСмените логин.", "Ошибка");
validated = false;
}
else
{
if (textnam.Text.Length >= 45 ||
textnam.Text == "" ||
textnam.Text == "Введите имя" ||
textsur.Text.Length >= 45 ||
textsur.Text == "" ||
textsur.Text == "Введите фамилию" ||
textlas.Text.Length >= 45 ||
textlas.Text == "" ||
textlas.Text == "Введите отчество" ||
textlog.Text.Length >= 45 ||
textlog.Text == "" ||
textlog.Text == "Введите логин" ||
textpas.Text.Length >= 45 ||
textpas.Text == "" ||
textpas.Text == "Введите пароль"
)
{
validated = false;
}
else
{
for (int i = 0; i < textlog.Text.Length; i++)
{
string log = textlog.Text;
if (textlog.Text[i] < '1' || (textlog.Text[i] > '9' && textlog.Text[i] < 'A') || (textlog.Text[i] > 'Z' && textlog.Text[i] < 'a') || textlog.Text[i] > 'z')
{
validated = false; MessageBox.Show("Логин должен содержать \nтолько латинские буквы\n и цифры", "Ошибка"); break;
}
}
for (int i = 0; i < textpas.Text.Length; i++)
{
if (textpas.Text[i] < '1' || (textpas.Text[i] > '9' && textpas.Text[i] < 'A') || (textpas.Text[i] > 'Z' && textpas.Text[i] < 'a') || textpas.Text[i] > 'z')
{
validated = false; MessageBox.Show("Пароль должен содержать \nтолько латинские буквы\nи цифры", "Ошибка"); break;
}
}
for (int i = 0; i < textnam.Text.Length; i++)
{
if (textnam.Text[i] < 'А' || (textnam.Text[i] > 'п' && textnam.Text[i] < 'р') || textnam.Text[i] > 'я')
{
validated = false; MessageBox.Show("Имя должно содержать только кириллицу", "Ошибка"); break;
}
}
for (int i = 0; i < textsur.Text.Length; i++)
{
if (textsur.Text[i] < 'А' || (textsur.Text[i] > 'п' && textsur.Text[i] < 'р') || textsur.Text[i] > 'я')
{
validated = false; MessageBox.Show("Фамилия должна содержать только кириллицу", "Ошибка"); break;
}
}
for (int i = 0; i < textlas.Text.Length; i++)
{
if (textlas.Text[i] < 'А' || (textlas.Text[i] > 'п' && textlas.Text[i] < 'р') || textlas.Text[i] > 'я')
{
validated = false; MessageBox.Show("Отчество должно содержать только кириллицу", "Ошибка"); break;
}
}
}
}
return(validated);
}
private void Updatedata(Int32 month, Int32 year)
{
activities.Clear();
Years.Clear();
Months.Clear();
DB _database = new DB();
_database.SetPreferences(filepath);
MySqlCommand _command = new MySqlCommand("select distinct extract(Year from ActivityDate) as Years from activities where UserId = @uid order by Years desc", _database.getConnection());
_command.Parameters.Add("@uid", MySqlDbType.UInt32).Value = parental.id;
MySqlDataAdapter _adapter = new MySqlDataAdapter(_command);
try
{
_database.openConnection();
_adapter.Fill(Years);
}
catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); }
finally { _database.closeConnection(); }
_command = new MySqlCommand("select distinct extract(Month from ActivityDate) as Months from activities where UserId = @uid order by Months desc", _database.getConnection());
_command.Parameters.Add("@uid", MySqlDbType.UInt32).Value = parental.id;
_adapter = new MySqlDataAdapter(_command);
try
{
_database.openConnection();
_adapter.Fill(Months);
}
catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); }
finally { _database.closeConnection(); }
int nowmonth = month;
int nowyear = year;
combomonth.Items.Clear();
for (int i = 0; i < Months.Rows.Count; i++)
{
string monthname = "";
Int32 index = Months.Rows[i].Field <Int32>("Months");
monthname = MonthName(index);
combomonth.Items.Add(monthname);
}
comboyear.Items.Clear();
for (int i = 0; i < Years.Rows.Count; i++)
{
comboyear.Items.Add(Years.Rows[i].Field <Int32>("Years").ToString());
}
//
_command = new MySqlCommand("select * from activities where UserId = @uid and extract(month from ActivityDate) = @cum and extract(year from ActivityDate) = @cuy;", _database.getConnection());
_command.Parameters.Add("@uid", MySqlDbType.UInt32).Value = parental.id;
_command.Parameters.Add("@cum", MySqlDbType.UInt32).Value = nowmonth;
_command.Parameters.Add("@cuy", MySqlDbType.UInt32).Value = nowyear;
_adapter.SelectCommand = _command;
try
{
_database.openConnection();
_adapter.Fill(activities);
}
catch { MessageBox.Show("Ошибка работы базы данных", "Ошибка"); }
finally
{
_database.closeConnection();
}
listactivities.Items.Clear();
//
for (int i = 0; i < activities.Rows.Count; i++)
{
string actname = activities.Rows[i].Field <DateTime>("ActivityDate").Date.ToShortDateString() + " : " + activities.Rows[i].Field <TimeSpan>("ActivityHours").ToString();
listactivities.Items.Add(actname);
}
//
TimeSpan sumtime = new TimeSpan();
for (int i = 0; i < activities.Rows.Count; i++)
{
sumtime += activities.Rows[i].Field <TimeSpan>("ActivityHours");
}
labelmonthhour.Text = sumtime.ToString().Substring(0, 5);
}
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 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("Введите данные БД");
}
}