public void DeleteWorker(Worker worker)
{
MessageBoxResult result = MessageBox.Show("Czy na pewno chcesz usunąć pracownika?", "Potwierdzenie", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (result == MessageBoxResult.Yes)
{
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Workers.Attach(worker);
dataContext.Workers.DeleteOnSubmit(worker);
dataContext.SubmitChanges();
}
}
catch (System.Data.SqlClient.SqlException)
{
System.Windows.MessageBox.Show("Nie udało się usunąć pracownika. Błąd połączenia z bazą danych lub pracownik jest przypisany do zajęć");
}
}
else
{
Application.Current.Shutdown();
}
}
public List <WorkPlanElement> GetWorkPlanElements()
{
try
{
_workPlanElements = new List <WorkPlanElement>();
using (var sqlConn = new SqlConnection(Confiuration.GetSqlConnectionString()))
{
var cmd = new SqlCommand(@"SELECT WorkPlan.Id AS WorkPlanId, DayIndex, StartTime, EndTime, Employees.Id AS EmployeeId, Employees.Name AS EmployeeName, Salary,
Rooms.Id AS RoomId, Rooms.Name AS RoomName, Capacity, Classes.Id AS ClassId, Classes.Name AS ClassName, AddInfo FROM WorkPlan
LEFT JOIN Employees ON WorkPlan.WorkerId=Employees.Id
LEFT JOIN Rooms ON Rooms.Id=WorkPlan.RoomId
LEFT JOIN Classes ON Classes.Id=WorkPlan.ClassId")
{
CommandType = CommandType.Text,
Connection = sqlConn
};
sqlConn.Open();
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var workPlanElement = new WorkPlanElement();
var worker = new Worker();
var _class = new Class();
var room = new Room();
worker.ID = reader.GetInt32(reader.GetOrdinal("EmployeeId"));
worker.Name = reader.GetString(reader.GetOrdinal("EmployeeName"));
workPlanElement.Worker = worker;
room.ID = reader.GetInt32(reader.GetOrdinal("RoomId"));
room.Name = reader.GetString(reader.GetOrdinal("RoomName"));
room.Capacity = reader.GetInt32(reader.GetOrdinal("Capacity"));
workPlanElement.Room = room;
workPlanElement.Worker = worker;
_class.ID = reader.GetInt32(reader.GetOrdinal("ClassId"));
_class.Name = reader.GetString(reader.GetOrdinal("ClassName"));
_class.Popularity = reader.GetString(reader.GetOrdinal("AddInfo"));
workPlanElement.Class = _class;
workPlanElement.ID = reader.GetInt32(reader.GetOrdinal("WorkPlanId"));
workPlanElement.DayOfWeek = (DayOfWeek)reader.GetInt32(reader.GetOrdinal("DayIndex"));
workPlanElement.StartTime = reader.GetTimeSpan(reader.GetOrdinal("StartTime"));
workPlanElement.EndTime = reader.GetTimeSpan(reader.GetOrdinal("EndTime"));
_workPlanElements.Add(workPlanElement);
}
}
}
}
}
catch
{
System.Windows.MessageBox.Show("Brak połączenia z bazą danych!");
}
return(_workPlanElements);
}
public void AddWorker(Worker worker)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Workers.InsertOnSubmit(worker);;
dataContext.SubmitChanges();
}
}
public void AddClient(Client client)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Clients.InsertOnSubmit(client);
dataContext.SubmitChanges();
}
}
public void AddClass(Class _class)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Classes.InsertOnSubmit(_class);
dataContext.SubmitChanges();
}
}
public void AddRoom(Room room)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Rooms.InsertOnSubmit(room);
dataContext.SubmitChanges();
}
}
public void DeleteClass(Class _class)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Classes.Attach(_class);
dataContext.Classes.DeleteOnSubmit(_class);
dataContext.SubmitChanges();
}
}
public void DeleteClient(Client client)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Clients.Attach(client);
dataContext.Clients.DeleteOnSubmit(client);
dataContext.SubmitChanges();
}
}
public void DeleteWorkPlanElement(WorkPlanElement workPlanElement)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.WorkPlanElements.Attach(workPlanElement);
dataContext.WorkPlanElements.DeleteOnSubmit(workPlanElement);
dataContext.SubmitChanges();
}
}
public List <Worker> GetWorkers()
{
var _workers = new List <Worker>();
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
_workers = dataContext.Workers.ToList();
}
return(_workers);
}
public void EditClass(Class _class)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
var classToEdit = dataContext.Classes.FirstOrDefault(c => c.ID == _class.ID);
classToEdit.Name = _class.Name;
classToEdit.Popularity = _class.Popularity;
dataContext.SubmitChanges();
}
}
public List <Class> GetClasses()
{
var _classes = new List <Class>();
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
_classes = dataContext.Classes.ToList();
}
return(_classes);
}
public void EditRoom(Room room)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
var roomToEdit = dataContext.Rooms.FirstOrDefault(r => r.ID == room.ID);
roomToEdit.Name = room.Name;
roomToEdit.Capacity = room.Capacity;
dataContext.SubmitChanges();
}
}
public void EditWorker(Worker worker)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
var workerToEdit = dataContext.Workers.FirstOrDefault(w => w.ID == worker.ID);
workerToEdit.Name = worker.Name;
workerToEdit.Surname = worker.Surname;
dataContext.SubmitChanges();
}
}
public List <Room> GetRooms()
{
var _rooms = new List <Room>();
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
_rooms = dataContext.Rooms.ToList();
}
return(_rooms);
}
public void AddClass(Class _class)
{
try {
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Classes.InsertOnSubmit(_class);
dataContext.SubmitChanges();
}
}
catch (Exception e)
{
MessageBox.Show("Operacja dodaj nie powiodła się");
}
}
public void EditWorker(Worker worker)
{
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
var workerToEdit = dataContext.Workers.FirstOrDefault(w => w.ID == worker.ID);
workerToEdit.Name = worker.Name;
workerToEdit.Surname = worker.Surname;
dataContext.SubmitChanges();
}
} catch (System.Data.SqlClient.SqlException) {
System.Windows.MessageBox.Show("Nie udało się dokonać edycji pracownika.", "Błąd połączenia z bazą danych");
}
}
public void DeleteClass(Class _class)
{
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Classes.Attach(_class);
dataContext.Classes.DeleteOnSubmit(_class);
dataContext.SubmitChanges();
}
}
catch
{
System.Windows.MessageBox.Show("Brak połączenia z bazą danych!");
}
}
public void DeleteWorkPlanElement(WorkPlanElement workPlanElement)
{
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.WorkPlanElements.Attach(workPlanElement);
dataContext.WorkPlanElements.DeleteOnSubmit(workPlanElement);
dataContext.SubmitChanges();
}
}
catch
{
System.Windows.MessageBox.Show("Brak połączenia z bazą danych!");
}
}
public List <Worker> GetWorkers()
{
var _workers = new List <Worker>();
try {
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
_workers = dataContext.Workers.ToList();
}
} catch (System.Data.SqlClient.SqlException)
{
MessageBox.Show("Nie można pobrać listy pracowników.", "Błąd połączenia z bazą danych.");
}
return(_workers);
}
public void EditClass(Class _class)
{
try {
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
var classToEdit = dataContext.Classes.FirstOrDefault(c => c.ID == _class.ID);
classToEdit.Name = _class.Name;
classToEdit.Popularity = _class.Popularity;
dataContext.SubmitChanges();
}
}
catch (Exception e)
{
MessageBox.Show("Operacja edycji nie powiodła się");
}
}
public void EditRoom(Room room)
{
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
var roomToEdit = dataContext.Rooms.FirstOrDefault(r => r.ID == room.ID);
roomToEdit.Name = room.Name;
roomToEdit.Capacity = room.Capacity;
dataContext.SubmitChanges();
}
}
catch (Exception e)
{
MessageBox.Show("Operacja Edytuj nie powiodła się");
}
}
public List <Class> GetClasses()
{
var _classes = new List <Class>();
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
_classes = dataContext.Classes.ToList();
}
}
catch
{
System.Windows.MessageBox.Show("Brak połączenia z bazą danych!");
}
return(_classes);
}
public List <Room> GetRooms()
{
var _rooms = new List <Room>();
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
_rooms = dataContext.Rooms.ToList();
}
}
catch (Exception e)
{
MessageBox.Show("Nie udało się pobrać zawartości bazy danych");
}
return(_rooms);
}
public void AddWorkPlanElement(WorkPlanElement workPlanElement)
{
using (var sqlConn = new SqlConnection(Confiuration.GetSqlConnectionString()))
{
var cmd = new SqlCommand("INSERT INTO WorkPlan (WorkerId, RoomId, ClassId, DayIndex, StartTime, EndTime) VALUES (@WorkerId, @RoomId, @ClassId, @DayIndex, @StartTime, @EndTime)")
{
CommandType = CommandType.Text,
Connection = sqlConn
};
cmd.Parameters.Add("@WorkerId", SqlDbType.Int, 250).Value = workPlanElement.Worker.ID;
cmd.Parameters.Add("@RoomId", SqlDbType.Int, 250).Value = workPlanElement.Room.ID;
cmd.Parameters.Add("@ClassId", SqlDbType.Int, 250).Value = workPlanElement.Class.ID;
cmd.Parameters.Add("@DayIndex", SqlDbType.Int, 250).Value = (int)workPlanElement.DayOfWeek;
cmd.Parameters.Add("@StartTime", SqlDbType.Time, 7).Value = workPlanElement.StartTime;
cmd.Parameters.Add("@EndTime", SqlDbType.Time, 7).Value = workPlanElement.EndTime;
sqlConn.Open();
cmd.ExecuteNonQuery();
}
}
public void EditClient(Client client)
{
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
var clientToEdit = dataContext.Clients.FirstOrDefault(w => w.ID == client.ID);
clientToEdit.Name = client.Name;
clientToEdit.Surname = client.Surname;
clientToEdit.Address = client.Address;
clientToEdit.VIP = client.VIP;
dataContext.SubmitChanges();
}
}
catch (System.Data.SqlClient.SqlException)
{
System.Windows.MessageBox.Show("Nie udało się dokonać edycji klienta.", "Błąd połączenia z bazą danych");
}
}
internal void DeleteRoom(Room EditedRoom)
{
try
{
var buttons = MessageBoxButton.YesNo;
var question = MessageBoxImage.Question;
if (MessageBox.Show("Czy na pewno chcesz usunac salę?", "Potwierdzenie usuniecia", buttons, question) == MessageBoxResult.Yes)
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Rooms.Attach(EditedRoom);
dataContext.Rooms.DeleteOnSubmit(EditedRoom);
dataContext.SubmitChanges();
}
}
}
catch
{ MessageBox.Show("Nie można usunąć sali!", "Błąd"); }
}
public void AddRoom(Room room)
{
if (string.IsNullOrWhiteSpace(room.Name) == true)
{
MessageBox.Show("Nazwa nie może być pusta.");
return;
}
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
dataContext.Rooms.InsertOnSubmit(room);
dataContext.SubmitChanges();
}
}
catch (Exception e)
{
MessageBox.Show("Operacja Dodaj nie powiodła się");
}
}
public void EditWorkPlanElement(WorkPlanElement workPlanElement)
{
using (var sqlConn = new SqlConnection(Confiuration.GetSqlConnectionString()))
{
var cmd = new SqlCommand("UPDATE WorkPlan SET WorkerId=@WorkerId, RoomId=@RoomId, ClassId=@ClassId, DayIndex=@DayIndex, StartTime=@StartTime, EndTime=@EndTime WHERE Id=@Id")
{
CommandType = CommandType.Text,
Connection = sqlConn
};
cmd.Parameters.Add("@WorkerId", SqlDbType.Int, 250).Value = workPlanElement.Worker.ID;
cmd.Parameters.Add("@RoomId", SqlDbType.Int, 250).Value = workPlanElement.Room.ID;
cmd.Parameters.Add("@ClassId", SqlDbType.Int, 250).Value = workPlanElement.Class.ID;
cmd.Parameters.Add("@DayIndex", SqlDbType.Int, 250).Value = (int)workPlanElement.DayOfWeek;
cmd.Parameters.Add("@StartTime", SqlDbType.Time, 250).Value = workPlanElement.StartTime;
cmd.Parameters.Add("@EndTime", SqlDbType.Int, 250).Value = workPlanElement.EndTime;
cmd.Parameters.Add("@Id", SqlDbType.Int, 250).Value = workPlanElement.ID;
sqlConn.Open();
cmd.ExecuteNonQuery();
}
}
public void AddWorker(Worker worker)
{
if (worker.Salary <= 0)
{
MessageBox.Show("Pensja musi być większa od 0");
return;
}
try
{
using (var dataContext = new MotoFitAcademyDataContext(Confiuration.GetSqlConnectionString()))
{
string pattern = @"^[0-9]{11}$";
if (Regex.IsMatch(worker.Pesel, pattern) == false)
{
MessageBox.Show("Niepoprawny pesel");
return;
}
var workers = GetWorkers();
foreach (var w in workers)
{
if (worker.Pesel == w.Pesel)
{
MessageBox.Show("Kolizja peseli");
return;
}
}
dataContext.Workers.InsertOnSubmit(worker);
dataContext.SubmitChanges();
}
}
catch (System.Data.SqlClient.SqlException)
{
System.Windows.MessageBox.Show("Nie udało się dodać pracownika.", "Błąd połączenia z bazą danych");
}
}