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();
            }
        }