private void Login()
{
string username = textBoxUsername.Text;
string password = textBoxPassword.Text;
User user = null;
if (username.Length > 0 && password.Length > 0)
{
try
{
FormLoading.Show();
Database db = new Database();
db.CreateCommand("SELECT id FROM users WHERE username = :username AND password = :password AND isEmployee = 1");
db.AddParameter("username", username);
db.AddParameter("password", password);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
user = User.GetById(dr.GetValueByColumn<int>("id"));
}
db.CloseConnection();
FormLoading.CloseForm();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
labelResult.Text = "Verbinding met database mislukt.";
return;
}
if (user != null)
{
this.Hide();
this.formMain = new FormMain();
this.formMain.Show();
this.formMain.FormClosing += formMain_FormClosing;
//this.formMain.logoutToolStripMenuItem.Click += afmeldenToolStripMenuItem_Click;
}
else
{
labelResult.Text = "Onbekende aanmeldgegevens.";
}
}
}
private void FillMap()
{
Database db = new Database();
db.CreateCommand("SELECT * FROM LOCATIONS");
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
Kampeerplek kampeerplek = new Kampeerplek(dr.GetInt16(0), new Point(dr.GetInt16(2), dr.GetInt16(3)) , dr.GetInt16(1), dr.GetInt16(4));
kampeerplekken.Add(kampeerplek);
}
for (int i = 0; i < kampeerplekken.Count; i++)
{
Image tentIcon = new Image();
tentIcon.HorizontalAlignment = HorizontalAlignment.Left;
tentIcon.Name = "id" + kampeerplekken[i].id.ToString();
tentIcon.Stretch = Stretch.Fill;
tentIcon.VerticalAlignment = VerticalAlignment.Top;
tentIcon.Height = 37;
tentIcon.Width = 32;
tentIcon.Margin = new Thickness(kampeerplekken[i].location.X,kampeerplekken[i].location.Y,0,0);
tentIcon.Cursor = Cursors.Hand;
tentIcon.MouseEnter += new MouseEventHandler(TentLocation_MouseEnter);
tentIcon.MouseLeave += new MouseEventHandler(TentLocation_MouseLeave);
tentIcon.MouseLeftButtonDown += new MouseButtonEventHandler(TentLocation_MouseDown);
if (kampeerplekken[i].isGereserveerd)
{
var uri = new Uri("pack://application:,,,/Resources/tent_full.png");
tentIcon.Source = new BitmapImage(uri);
//tentIcon.Source = (ImageSource)new ImageSourceConverter().ConvertFrom("Rescources/tent.png");
tentIcon.Visibility = Visibility.Hidden;
}
else
{
var uri = new Uri("pack://application:,,,/Resources/tent.png");
tentIcon.Source = new BitmapImage(uri);
}
mapicons.Add(tentIcon);
tentIcons.Children.Add(tentIcon);
}
}
public static User GetById(int id)
{
User user;
try
{
Database db = new Database();
db.CreateCommand("SELECT * FROM users WHERE id = :id");
db.AddParameter("id", id);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
if (dr.HasRows)
{
dr.Read();
int userId = dr.GetValueByColumn<int>("id");
string username = dr.GetValueByColumn<string>("username");
long uploadLimit = dr.GetValueByColumn<long>("uploadlimit");
bool isEmployee = dr.GetValueByColumn<int>("isemployee") == 1 ? true : false;
user = new User(id, username, uploadLimit, isEmployee);
db.CloseConnection();
return user;
}
db.CloseConnection();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
return null;
}
/// <summary>
/// Refresh reservation grid with new items, also check database
/// </summary>
private void RefreshGridWithItems()
{
Database db = new Database();
db.CreateCommand("SELECT R.ID, B.FIRSTNAME, M.LASTNAME, B.PHONE, M.ADDRESS, M.POSTALCODE, M.PLACE, M.LAND, R.TOTALPAYED, R.TOTALCOSTS FROM BOOKERS B, MAINBOOKERS M, RESERVATIONS R WHERE B.RESERVATIONS_ID = R.ID AND M.BOOKERS_ID = B.ID");
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
mainBookers = new List<MainBooker>();
while (dr.Read())
{
MainBooker booker = new MainBooker(dr.GetInt16(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetInt16(8), dr.GetInt16(9), dr.GetString(6), dr.GetString(7));
mainBookers.Add(booker);
}
db.CloseConnection();
dataGridReservations.DataSource = null;
dataGridReservations.DataSource = mainBookers;
}
//get all products from the database
private void GetProductsFromDatabase()
{
Database db = new Database();
db.CreateCommand("SELECT * FROM PRODUCTS");
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
Item item = new Item(dr.GetInt16(0), dr.GetInt16(1), dr.GetString(2), dr.GetString(3), dr.GetInt16(4));
alleItems.Add(item);
}
uitleenbareItems = new List<Item>(alleItems);
db.CloseConnection();
}
/// <summary>
/// Get all categories
/// </summary>
private void GetCategories()
{
lbCategorie.Items.Clear();
Database db = new Database();
db.CreateCommand("SELECT * FROM PRODUCTS_CATEGORIES");
db.OpenConnection();
db.ExecuteCommand();
lbCategorie.Items.Add("Alles");
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
Product_Categorie categorie = new Product_Categorie(dr.GetInt16(0), dr.GetInt16(1), dr.GetString(2));
product_categorieen.Add(categorie);
lbCategorie.Items.Add(dr.GetString(2));
}
db.CloseConnection();
}
/// <summary>
/// Get all bookers from the database
/// </summary>
private void GetBookersFromDB()
{
Database db = new Database();
db.CreateCommand("SELECT id,firstname FROM BOOKERS");
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
while (dr.Read())
{
Booker booker = new Booker(dr.GetInt16(0), dr.GetString(1));
BookerList.Add(booker);
comboBox1.Items.Add(booker.Name);
}
db.CloseConnection();
}
/// <summary>
/// Completing the reservation
/// </summary>
private void CompleteReservation()
{
DialogResult dialogResult = MessageBox.Show("Weet je zeker dat alle gegevens kloppen?", "Reservering afronden", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
int reservationId;
int mainUserId;
int bookerId;
int mainBookerId;
userName = mainBooker.FirstName + mainBooker.LastName;
bool userNameNotCorrect = true;
Database db = new Database();
db.OpenConnection();
db.CreateCommand("INSERT INTO RESERVATIONS (MAINBOOKER_ID, TOTALCOSTS, TOTALPAYED, CREATED) VALUES (0, :totalcosts, :totalpayed, :created) RETURNING id INTO :LASTID");
db.AddParameter("totalcosts", numPayCosts.Value);
db.AddParameter("totalpayed", 0);
db.AddParameter("created", DateTime.Now);
db.GetParameter(":LASTID");
db.ExecuteCommand();
OracleParameter lastId = db.lastId;
//MessageBox.Show(lastId.Value.ToString());
reservationId = Convert.ToInt32(lastId.Value.ToString());
while (userNameNotCorrect)
{
db.CreateCommand("SELECT USERNAME FROM USERS WHERE USERNAME = :username");
db.AddParameter(":username", userName);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
if (dr.HasRows)
userName += 1.ToString();
else
userNameNotCorrect = false;
}
userNames.Add(userName);
db.CreateCommand("INSERT INTO USERS (USERNAME, PASSWORD, UPLOADLIMIT, ISEMPLOYEE) VALUES (:username, :password, :uploadlimit, 0) RETURNING id INTO :LASTID");
db.AddParameter("username", userName);
passWords.Add(GeneratePassword(8));
db.AddParameter("password", passWords[0]);
db.AddParameter("uploadlimit", 5000000);
db.GetParameter(":LASTID");
db.ExecuteCommand();
lastId = db.lastId;
//MessageBox.Show(lastId.Value.ToString());
mainUserId = Convert.ToInt32(lastId.Value.ToString());
db.CreateCommand("SELECT * FROM ( SELECT ID,CARDVALUE FROM CARDVALUES WHERE AVAILABLE = 1 ) WHERE ROWNUM = 1");
db.ExecuteCommand();
OracleDataReader odr = db.DataReader;
string cardValue;
cardValue = GeneratePassword(10);
while (odr.Read())
{
cardValue = odr.GetString(1);
db.CreateCommand("UPDATE CARDVALUES SET AVAILABLE = 0 WHERE id = :id");
db.AddParameter("id", odr.GetInt16(0));
db.ExecuteCommand();
}
db.CreateCommand("INSERT INTO BOOKERS (RESERVATIONS_ID, USERS_ID, LOCATIONS_ID, FIRSTNAME, PHONE, CARDVALUE) VALUES (:reservation_id, :user_id, :location_id, :firstname, :phone, :cardvalue) RETURNING id INTO :LASTID");
db.AddParameter("reservation_id", reservationId);
db.AddParameter("user_id", mainUserId);
db.AddParameter("location_id", Convert.ToInt16(lbCampPlace.Text));
db.AddParameter("firstname", mainBooker.FirstName);
db.AddParameter("phone", mainBooker.Phone);
db.AddParameter("cardvalue", cardValue);
db.GetParameter(":LASTID");
db.ExecuteCommand();
lastId = db.lastId;
// MessageBox.Show(lastId.Value.ToString());
bookerId = Convert.ToInt32(lastId.Value.ToString());
db.CreateCommand("INSERT INTO MAINBOOKERS (BOOKERS_ID, LASTNAME, ADDRESS, POSTALCODE, PLACE, LAND) VALUES (:booker_id, :lastname, :address, :postalcode, :place, :land) RETURNING id INTO :LASTID");
db.AddParameter("booker_id", bookerId);
db.AddParameter("lastname", mainBooker.LastName);
db.AddParameter("address", mainBooker.Address);
db.AddParameter("postalcode", mainBooker.PostalCode);
db.AddParameter("place", mainBooker.Place);
db.AddParameter("land", mainBooker.Land);
db.GetParameter(":LASTID");
db.ExecuteCommand();
lastId = db.lastId;
// MessageBox.Show(lastId.Value.ToString());
mainBookerId = Convert.ToInt32(lastId.Value.ToString());
db.CreateCommand("UPDATE RESERVATIONS SET MAINBOOKER_ID = :mainbooker_id WHERE id = :reservation_id");
db.AddParameter("mainbooker_id", mainBookerId);
db.AddParameter("reservation_id", reservationId);
db.ExecuteCommand();
for (int i = 0; i < medeboekers.Count; i++)
{
userName = medeboekers[i].Name;
userNameNotCorrect = true;
while (userNameNotCorrect)
{
db.CreateCommand("SELECT USERNAME FROM USERS WHERE USERNAME = :username");
db.AddParameter(":username", userName);
db.OpenConnection();
db.ExecuteCommand();
OracleDataReader dr = db.DataReader;
if (dr.HasRows)
userName += 1.ToString();
else
userNameNotCorrect = false;
}
userNames.Add(userName);
db.CreateCommand("INSERT INTO USERS (USERNAME, PASSWORD, UPLOADLIMIT, ISEMPLOYEE) VALUES (:username, :password, :uploadlimit, 0) RETURNING id INTO :LASTID");
db.AddParameter("username", userName);
passWords.Add(GeneratePassword(8));
db.AddParameter("password", passWords[i + 1]);
db.AddParameter("uploadlimit", 5000000);
db.GetParameter(":LASTID");
db.ExecuteCommand();
lastId = db.lastId;
//MessageBox.Show(lastId.Value.ToString());
int userId = Convert.ToInt32(lastId.Value.ToString());
db.CreateCommand("SELECT * FROM ( SELECT ID,CARDVALUE FROM CARDVALUES WHERE AVAILABLE = 1 ) WHERE ROWNUM = 1");
db.ExecuteCommand();
OracleDataReader dar = db.DataReader;
cardValue = GeneratePassword(10);
while (odr.Read())
{
cardValue = dar.GetString(1);
db.CreateCommand("UPDATE CARDVALUES SET AVAILABLE = 0 WHERE id = :id");
db.AddParameter("id", dar.GetInt16(0));
db.ExecuteCommand();
}
db.CreateCommand("INSERT INTO BOOKERS (RESERVATIONS_ID, USERS_ID, LOCATIONS_ID, FIRSTNAME, PHONE, CARDVALUE) VALUES (:reservation_id, :user_id, :location_id, :firstname, :phone, :cardvalue)");
db.AddParameter("reservation_id", reservationId);
db.AddParameter("user_id", userId);
db.AddParameter("location_id", Convert.ToInt16(lbCampPlace.Text));
db.AddParameter("firstname", medeboekers[i].Name);
db.AddParameter("phone", medeboekers[i].PhoneNumber);
db.AddParameter("cardvalue", cardValue);
db.ExecuteCommand();
}
db.CloseConnection();
CreateEndTextFile();
//EMPTY FORMS
//STEP 1
numNumPers.Value = 0;
tbFirstName.Text = "";
tbLastName.Text = "";
tbPhone.Text = "";
tbAddress.Text = "";
tbPostalCode.Text = "";
tbLivePlace.Text = "";
tbLand.Text = "";
//STEP 3
cbPayMethod.SelectedIndex = -1;
tbPayName.Text = "";
tbPayIBAN.Text = "";
cbPayLater.Checked = false;
//STEP 4
lbCampPlace.Text = "";
//STEP 5
uitleenbareItems.Clear();
geleendeItems.Clear();
cbNoItemReservation.Checked = false;
MessageBox.Show("De reservering is succesvol in de database geplaatst", "Succes!");
Reserveren.SelectedIndex = 0;
}
}
/// <summary>
/// Hired selected products
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
if (selectedIndexProducts != -1)
{
Database db = new Database();
db.OpenConnection();
for (int i = 0; i < geleendeItems.Count; i++)
{
db.CreateCommand("INSERT INTO PRODUCTS_BOOKERS (PRODUCTS_ID, BOOKERS_ID, AMOUNT_HIRED, AMOUNT_RETURNED, CREATED, MODIFIED) VALUES (:product_id, :booker_id, :amount_hired, :amount_returned, :created, NULL)");
db.AddParameter("product_id", geleendeItems[i].GetId());
db.AddParameter("booker_id", BookerList[selectedIndexProducts].GetId());
db.AddParameter("amount_hired", geleendeItems[i].Aantal);
db.AddParameter("amount_returned", 0);
db.AddParameter("created", DateTime.Now);
db.ExecuteCommand();
db.CloseConnection();
}
}
}
/// <summary>
/// Remove amount payed to amount costs
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btPayReservation_Click(object sender, EventArgs e)
{
if (dataGridReservations.CurrentRow.Index != -1)
{
int reservationId = mainBookers[dataGridReservations.CurrentRow.Index].GetId();
int amountCosts = mainBookers[dataGridReservations.CurrentRow.Index].TotaalKoste;
Database db = new Database();
db.OpenConnection();
db.CreateCommand("UPDATE RESERVATIONS SET TOTALPAYED = :amount_payed WHERE id = :reservation_id");
db.AddParameter("amount_payed", amountCosts);
db.AddParameter("reservation_id", reservationId);
db.ExecuteCommand();
db.CloseConnection();
RefreshGridWithItems();
}
}
