//log in function
public void logIn()
{
informationBox info = new informationBox();
SqlCeConnection databaseConnection = new SqlCeConnection(@"Data Source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\TheLotusTempleManager\TheLotusTempleDB.sdf");
string query = "SELECT UserName, Password FROM receptionistAccounts WHERE UserName = '******' AND Password = '******'";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
SqlCeDataReader dr = cmd.ExecuteReader();
int count = 0;
//tests if the information is correct
while (dr.Read())
{
count++;
}
if (count > 0)
{
//send the username to logged as var in the main window and shows the main window
loggedAs = this.userNameRecep;
MainWindow mn = new MainWindow();
mn.Show();
logged = true;
}
else
{
info.showMsg("Incorrect Information!! Please Try again");
info.ShowDialog();
}
databaseConnection.Close();
}
//create a guest function
public void createGuest()
{
SqlCeConnection databaseConnection = new SqlCeConnection(@"Data Source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\TheLotusTempleManager\TheLotusTempleDB.sdf");
string query = "INSERT INTO guestTab([First Name], [Family Name], [Age],[Phone Number], [Address], [Gender], [Occupied Room], [Identity Number]) " +
"VALUES ('" + this.firstNameGuest + "','" + this.lastNameGuest + "','" + this.ageGuest + "', '" + this.phoneNumberGuest + "', '" + this.addressGuest + "','" + this.genderGuest + "', '" + this.occupiedRoomGuest + "', '" + this.identityGuest + "')";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
cmd.ExecuteNonQuery();
databaseConnection.Close();
informationBox info = new informationBox();
info.showMsg("Guest has been added");
info.ShowDialog();
query = "SELECT guestID FROM guestTab WHERE [Occupied Room]= '" + this.occupiedRoomGuest + "'";
SqlCeCommand cmd2 = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
SqlCeDataAdapter data = new SqlCeDataAdapter(cmd2);
databaseConnection.Close();
DataTable dt = new DataTable();
data.Fill(dt);
int guestID = int.Parse(dt.Rows[0]["guestID"].ToString());
Booking obj = new Booking(DateTime.Today, this.checkOutDate, this.occupiedRoomGuest, guestID);
obj.insertBooking();
Room.updateRoomStatus(this.occupiedRoomGuest);
}
//function that update the account's info
public void updateAccount()
{
informationBox info = new informationBox();
SqlCeConnection databaseConnection = new SqlCeConnection(@"Data Source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\TheLotusTempleManager\TheLotusTempleDB.sdf");
if (userNameRecep.Length > 3 && passwordRecep.Length > 7)
{
if (this.passwordRecep == this.rePasswordRecep)
{
string query = "SELECT UserName FROM receptionistAccounts WHERE UserName = '******'";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
SqlCeDataReader dr = cmd.ExecuteReader();
int count = 0;
//tests if the information is correct
while (dr.Read())
{
count++;
}
databaseConnection.Close();
if (count > 0 && this.userNameRecep != this.oldUserNameRecep)
{
info.showMsg("This Username is already taken");
info.ShowDialog();
}
else
{
string query2 = "UPDATE receptionistAccounts SET UserName ='******', Password = '******' WHERE UserName = '******'";
SqlCeCommand cmd2 = new SqlCeCommand(query2, databaseConnection);
databaseConnection.Open();
try
{
cmd2.ExecuteNonQuery();
databaseConnection.Close();
info.showMsg("The account has been updated");
info.ShowDialog();
}
catch (Exception)
{
databaseConnection.Close();
info.showMsg("Account doesn't exist! ");
info.ShowDialog();
}
}
}
else
{
info.showMsg("Password doesn't match!, Please try again");
info.ShowDialog();
}
}
else
{
info.showMsg("UserName must be at least 4 caracteres \n and Password 8 caracteres! please try again");
info.ShowDialog();
}
}
//function that creates an account
public void createAccountFunc()
{
informationBox info = new informationBox();
if (this.userNameRecep.Length > 3 && this.passwordRecep.Length > 7)
{
SqlCeConnection databaseConnection = new SqlCeConnection(@"Data Source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\TheLotusTempleManager\TheLotusTempleDB.sdf");
string query = "SELECT UserName FROM receptionistAccounts WHERE UserName = '******'";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
SqlCeDataReader dr = cmd.ExecuteReader();
int count = 0;
//tests if the information is correct
while (dr.Read())
{
count++;
}
databaseConnection.Close();
if (count > 0)
{
info.showMsg("UserName already Exists!, Please pick another one");
info.ShowDialog();
}
else
{
if (this.passwordRecep == this.rePasswordRecep)
{
string query2 = "INSERT INTO receptionistAccounts([UserName], [Password]) VALUES (@UserName, @Password)";
SqlCeCommand com = new SqlCeCommand(query2, databaseConnection);
com.Parameters.AddWithValue("@UserName", this.userNameRecep);
com.Parameters.AddWithValue("@Password", this.passwordRecep);
databaseConnection.Open();
com.ExecuteNonQuery();
databaseConnection.Close();
info.showMsg("The account has been created");
info.ShowDialog();
}
else
{
info.showMsg("Password doesn't match!, Please Verify");
info.ShowDialog();
}
}
}
else
{
info.showMsg("UserName must be at least 4 caracteres \n and Password 8 caracteres");
info.ShowDialog();
}
}
//function that deletes the current account
public void deleteAccount()
{
informationBox info = new informationBox();
if (this.countTableRows() > 1)
{
SqlCeConnection databaseConnection = new SqlCeConnection(@"Data Source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\TheLotusTempleManager\TheLotusTempleDB.sdf");
string query = "SELECT UserName, Password FROM receptionistAccounts WHERE UserName = '******' AND Password = '******'";
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
databaseConnection.Open();
SqlCeDataReader dr = cmd.ExecuteReader();
int count = 0;
//tests if the information is correct
while (dr.Read())
{
count++;
}
databaseConnection.Close();
if (count > 0)
{
string query2 = "DELETE FROM receptionistAccounts WHERE UserName = '******' AND Password = '******'";
SqlCeCommand cmd2 = new SqlCeCommand(query2, databaseConnection);
databaseConnection.Open();
cmd2.ExecuteNonQuery();
databaseConnection.Close();
info.showMsg("The Account has been deleted!,\n Please Relog in with another account");
info.ShowDialog();
Application.Current.Shutdown();
}
else
{
info.showMsg("Incorrect password! please try again");
info.ShowDialog();
}
}
else
{
info.showMsg("There's only one account! you can't delete it");
info.ShowDialog();
}
}
//predefined function that does the work of the thread
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
//creating a background worker & sending its info while it progresses
var worker = sender as BackgroundWorker;
worker.ReportProgress(0, string.Format("Initialisation"));
Thread.Sleep(500);
if (!pathExists)
{
Directory.CreateDirectory(appDirectoryPath);
worker.ReportProgress(2, string.Format("Creating paths"));
Thread.Sleep(200);
File.Create(dataBasePath).Close();
worker.ReportProgress(4, string.Format("Creating Database"));
Thread.Sleep(200);
try
{
//creating the database along with the first table
SqlCeConnection dataBaseConnection = new SqlCeConnection(@"Data Source=" + dataBasePath + ";Max Database Size = 4091;");
string query = " CREATE TABLE receptionistAccounts(ID int PRIMARY KEY IDENTITY(1,1) , UserName nvarchar(50), Password nvarchar(50))";
SqlCeCommand cmd = new SqlCeCommand(query, dataBaseConnection);
dataBaseConnection.Open();
cmd.ExecuteNonQuery();
dataBaseConnection.Close();
worker.ReportProgress(6, "Updating Database 1");
Thread.Sleep(200);
//inserting the default username and password
query = "INSERT INTO receptionistAccounts([UserName], [Password]) VALUES (@UserName, @Password)";
SqlCeCommand cmd2 = new SqlCeCommand(query, dataBaseConnection);
cmd2.Parameters.AddWithValue("@UserName", "TheLotusTemple");
cmd2.Parameters.AddWithValue("@Password", "Temple2018");
dataBaseConnection.Open();
cmd2.ExecuteNonQuery();
dataBaseConnection.Close();
worker.ReportProgress(8, "Updating Database 2");
Thread.Sleep(200);
//creating the room table
query = "CREATE TABLE roomsTab(roomID int IDENTITY(1,1), [Room Name] nvarchar(50) PRIMARY KEY , [Room Type] nvarchar(50), [Room Status] nvarchar(50))";
SqlCeCommand cmd3 = new SqlCeCommand(query, dataBaseConnection);
dataBaseConnection.Open();
cmd3.ExecuteNonQuery();
dataBaseConnection.Close();
worker.ReportProgress(10, "Updating Datebase 3 ");
Thread.Sleep(200);
// inserting elements in rooms' table
Room.setRooms();
worker.ReportProgress(90, "Inserting informations");
Thread.Sleep(1000);
//creating the Guests' Table
query = "CREATE TABLE guestTab(guestID int IDENTITY(1,1) PRIMARY KEY , [First Name] nvarchar(50) , [Family Name] nvarchar(50), [Age] int ,[Phone Number] numeric , [Address] nvarchar(200), [Gender] nvarchar(20), [Occupied Room] nvarchar(10), [Identity Number] numeric)";
SqlCeCommand cmd4 = new SqlCeCommand(query, dataBaseConnection);
dataBaseConnection.Open();
cmd4.ExecuteNonQuery();
dataBaseConnection.Close();
worker.ReportProgress(92, "Inserting More Informations");
Thread.Sleep(200);
//creating the booking Table
query = "CREATE TABLE booking(bookingID int IDENTITY(1,1) PRIMARY KEY, [CheckIn date] datetime, [CheckOut date] datetime, [guestID] int , [Room Name] nvarchar(50) )";
SqlCeCommand cmd5 = new SqlCeCommand(query, dataBaseConnection);
dataBaseConnection.Open();
cmd5.ExecuteNonQuery();
dataBaseConnection.Close();
worker.ReportProgress(94, "Updating Database 4");
Thread.Sleep(200);
//adding the guest foreign key to the booking table
query = "ALTER TABLE booking ADD CONSTRAINT [guestID] FOREIGN KEY ([guestID]) REFERENCES guestTab([guestID])";
SqlCeCommand cmd6 = new SqlCeCommand(query, dataBaseConnection);
dataBaseConnection.Open();
cmd6.ExecuteNonQuery();
dataBaseConnection.Close();
worker.ReportProgress(96, "Adding constraints");
Thread.Sleep(200);
//adding the room foreig key to the booking table
query = "ALTER TABLE booking ADD CONSTRAINT [Room Name] FOREIGN KEY ([Room Name]) REFERENCES roomsTab([Room Name])";
SqlCeCommand cmd7 = new SqlCeCommand(query, dataBaseConnection);
dataBaseConnection.Open();
cmd7.ExecuteNonQuery();
dataBaseConnection.Close();
worker.ReportProgress(98, "Adding constraints");
Thread.Sleep(200);
//creating the checkout table
query = "CREATE TABLE archiveTable(RguestID int IDENTITY(1,1) PRIMARY KEY , [First Name] nvarchar(50) , [Family Name] nvarchar(50), [Age] int ,[Phone Number] numeric , [Address] nvarchar(200), [Gender] nvarchar(20), [Occupied Room] nvarchar(10), [Identity Number] numeric,[CheckIn date] datetime, [CheckOut date] datetime)";
SqlCeCommand cmd8 = new SqlCeCommand(query, dataBaseConnection);
dataBaseConnection.Open();
cmd8.ExecuteNonQuery();
dataBaseConnection.Close();
}
catch (Exception)
{
informationBox info = new informationBox();
info.showMsg("An erreur has been accured,\n please restart the application");
info.ShowDialog();
}
}
else
{
worker.ReportProgress(50, "Initialisation");
Thread.Sleep(1000);
}
worker.ReportProgress(100, "Done processing");
Thread.Sleep(200);
}
//function on btn click that adds a guest
private void btnAddGuest_Click(object sender, RoutedEventArgs e)
{
informationBox obj = new informationBox();
if (firstNameTextBox.Text.Length > 1 && lastNameTextBox.Text.Length > 2 && ageTextBox.Text.Length > 0 && identityTextBox.Text.Length > 0 && phoneTextBox.Text.Length > 0 && addressTextBox.Text.Length > 0)
{
if (!int.TryParse(ageTextBox.Text, out int n))
{
obj.showMsg("age must be a number");
obj.ShowDialog();
}
else if (!(int.Parse(ageTextBox.Text) > 17))
{
obj.showMsg("Age must be 18 or above");
obj.ShowDialog();
}
else if ((int.Parse(ageTextBox.Text) > 100))
{
obj.showMsg("Age must be under 100!");
obj.ShowDialog();
}
else if (!int.TryParse(identityTextBox.Text, out int m))
{
obj.showMsg("The ID must be a number");
obj.ShowDialog();
}
else if (identityTextBox.Text.Length < 4)
{
obj.showMsg("The ID number is too small , check again");
obj.ShowDialog();
}
else if (!int.TryParse(phoneTextBox.Text, out int l))
{
obj.showMsg("Check again the phone number");
obj.ShowDialog();
}
else if (phoneTextBox.Text.Length < 5)
{
obj.showMsg("The Phone number number is too small , check again");
obj.ShowDialog();
}
else if (firstNameTextBox.Text.Any(char.IsDigit))
{
obj.showMsg("The first name has a number in it , please check again");
obj.ShowDialog();
}
else if (lastNameTextBox.Text.Any(char.IsDigit))
{
obj.showMsg("The last name has a number in it , please check again");
obj.ShowDialog();
}
else if (checkOutDatePicker.Text.Length == 0)
{
obj.showMsg("Please Enter a Check out date");
obj.ShowDialog();
}
else if (roomsComboBox.Text.Length == 0)
{
obj.showMsg("Please pick the room");
obj.ShowDialog();
}
else if (DateTime.Compare(DateTime.Parse(checkOutDatePicker.Text), DateTime.Today) < 0)
{
obj.showMsg("the date you picked is incorrect");
obj.ShowDialog();
}
else
{
Guest obj2 = new Guest(firstNameTextBox.Text, lastNameTextBox.Text, genderComboBox.Text, addressTextBox.Text, int.Parse(ageTextBox.Text), int.Parse(identityTextBox.Text), int.Parse(phoneTextBox.Text), roomsComboBox.Text, DateTime.Parse(checkOutDatePicker.Text));
obj2.createGuest();
roomsComboBox.Items.Clear();
fillComboBox();
}
}
else
{
obj.showMsg("Please Fill all the fields properly!!");
obj.ShowDialog();
}
}
//search button click event
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
informationBox info = new informationBox();
if (nameRadio.IsChecked == true)
{
if (searchBar.Text.Length < 1)
{
info.showMsg("The field is empty please enter something");
info.ShowDialog();
}
else if (searchBar.Text.Any(char.IsDigit))
{
info.showMsg("The name contains a number, check again");
info.ShowDialog();
}
else
{
string query = "SELECT [Identity Number], [First Name], [Family Name], [Age],[Gender], [CheckIn date],[CheckOut date],[Address],[Occupied Room] FROM archiveTable WHERE [First Name] = @name";
databaseConnection.Open();
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
cmd.Parameters.AddWithValue("@name", searchBar.Text);
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
searchDataGrid.ItemsSource = dt.DefaultView;
databaseConnection.Close();
}
}
else if (lnameRadio.IsChecked == true)
{
if (searchBar.Text.Length < 1)
{
info.showMsg("The field is empty please enter something");
info.ShowDialog();
}
else if (searchBar.Text.Any(char.IsDigit))
{
info.showMsg("The name contains a number, check again");
info.ShowDialog();
}
else
{
string query = "SELECT [Identity Number],[First Name], [Family Name], [Age],[Gender], [CheckIn date],[CheckOut date],[Address],[Occupied Room] FROM archiveTable WHERE [Family Name] = @name";
databaseConnection.Open();
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
cmd.Parameters.AddWithValue("@name", searchBar.Text);
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
searchDataGrid.ItemsSource = dt.DefaultView;
databaseConnection.Close();
}
}
else if (phoneRadio.IsChecked == true)
{
if (searchBar.Text.Length < 5)
{
info.showMsg("Enter a proper phone number");
info.ShowDialog();
}
else if (!int.TryParse(searchBar.Text, out int l))
{
info.showMsg("The number contains a character, check again");
info.ShowDialog();
}
else
{
string query = "SELECT [Identity Number],[First Name], [Family Name], [Age],[Gender], [CheckIn date],[CheckOut date],[Address],[Occupied Room] FROM archiveTable WHERE [Phone Number] = @name";
databaseConnection.Open();
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
cmd.Parameters.AddWithValue("@name", searchBar.Text);
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
searchDataGrid.ItemsSource = dt.DefaultView;
databaseConnection.Close();
}
}
else if (idRadio.IsChecked == true)
{
if (searchBar.Text.Length < 4)
{
info.showMsg("Enter a proper ID");
info.ShowDialog();
}
else if (!int.TryParse(searchBar.Text, out int n))
{
info.showMsg("The ID contains a character, check again");
info.ShowDialog();
}
else
{
string query = "SELECT [Identity Number],[First Name], [Family Name], [Age],[Gender], [CheckIn date],[CheckOut date],[Address],[Occupied Room] FROM archiveTable WHERE [Identity Number] = @name";
databaseConnection.Open();
SqlCeCommand cmd = new SqlCeCommand(query, databaseConnection);
cmd.Parameters.AddWithValue("@name", searchBar.Text);
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
searchDataGrid.ItemsSource = dt.DefaultView;
databaseConnection.Close();
}
}
else
{
info.showMsg("Please pick a search type");
info.ShowDialog();
}
}
