/// <summary>
/// Gets the latest created event ID
/// </summary>
/// <returns>If record exists return a int that is not 0 or negative</returns>
private static int GetLatestEventID()
{
int Check = 0;
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "SELECT MAX(ID) AS Max FROM Event";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Check = Convert.ToInt32(reader["Max"]);
}
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(Check);
}
/// <summary>
/// asign all selected materials to an event
/// </summary>
/// <param name="MaterialList">List of materials</param>
/// <param name="EventID">EventID integer</param>
/// <returns>true if database allows changes false if not</returns>
public static bool UpdateMaterial(List <Material> MaterialList, int EventID)
{
foreach (Material M in MaterialList)
{
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "UPDATE Materiaal SET EventID = @EventID WHERE ID = @ID";
cmd.Parameters.Add(new SqlParameter("ID", M.ID));
cmd.Parameters.Add(new SqlParameter("EventID", EventID));
cmd.ExecuteNonQuery();
return(true);
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
return(false);
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(true);
}
return(true);
}
/// <summary>
/// returns amount of people in a reservation
/// </summary>
/// <param name="Reservation">reservation</param>
/// <returns>gets amount of all people in a reservation</returns>
public static int GetReservationAmountReservation(Reservation Reservation)
{
int reservationCount = 0;
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "SELECT COUNT(AccountID) As count FROM Account_Reservering WHERE ReserveringID = @ReserveringID";
cmd.Parameters.Add(new SqlParameter("ReserveringID", Reservation.ID));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
reservationCount = Convert.ToInt32(reader["count"]);
}
return(reservationCount);
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(reservationCount);
}
/// <summary>
/// Removes the account to an event
/// </summary>
/// <param name="Account">Account that has to be checked out</param>
/// <param name="EventID">ID of event that has to be updated</param>
/// <returns>True if Database allows the changes false if not</returns>
public static bool UpdateEventID_checkuit(Account Account, int EventID)
{
bool Check = false;
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "UPDATE Account SET EventID = null WHERE Account.ID = @Accountid ";
cmd.Parameters.Add(new SqlParameter("EventID", EventID));
cmd.Parameters.Add(new SqlParameter("Accountid", Account.ID));
cmd.ExecuteNonQuery();
Check = true;
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
Check = false;
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(Check);
}
/// <summary>
/// Changes a account
/// </summary>
/// <param name="account">Account has to be edited</param>
/// <returns>True if Database allows the changes false if not</returns>
public static bool EditAccount(Account Account)
{
bool Check = false;
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "UPDATE Account SET Gebruikersnaam = @UserName, Wachtwoord = @Password, Naam = @Name, Functie = @Function WHERE ID = @ID";
cmd.Parameters.Add(new SqlParameter("Name", Account.Name));
cmd.Parameters.Add(new SqlParameter("UserName", Account.Username));
cmd.Parameters.Add(new SqlParameter("Password", Account.Password));
cmd.Parameters.Add(new SqlParameter("Function", Account.Function.ToString()));
cmd.Parameters.Add(new SqlParameter("ID", Account.ID));
cmd.ExecuteNonQuery();
Check = true;
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
Check = false;
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(Check);
}
/// <summary>
/// Gets list of reservations that belongs to a specific event
/// </summary>
/// <param name="EventID">EventID integer</param>
/// <returns>A list of reservations</returns>
public static List <Reservation> GetReservation(int EventID)
{
List <Reservation> ReservationList = new List <Reservation>();
Place Place;
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "SELECT * FROM Reservering Where EventID = @EventID";
cmd.Parameters.Add(new SqlParameter("EventID", EventID));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int ID = Convert.ToInt32(reader["ID"]);
int PlaceID = (reader["PlaatsID"] != DBNull.Value) ? Convert.ToInt32(reader["PlaatsID"]) : 0;
int Amount = Convert.ToInt32(reader["BetalingBedrag"]);
bool Status = Convert.ToBoolean(reader["BetalingStatus"]);
List <Account> Accountlist = DatabaseGetAccounts.GetAccountsReservation(ID);
Event newEvent = DatabaseGetEvents.GetSingleEvent(EventID);
if (PlaceID == 0)
{
Place = null;
}
else
{
Place = DatabaseGetPlace.GetPlace(PlaceID);
}
Reservation Reservation = new Reservation(ID, newEvent, new Payment(Amount, Status), Place, Accountlist);
ReservationList.Add(Reservation);
}
return(ReservationList);
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(ReservationList);
}
/// <summary>
/// Get all materials that are linked with a event and a account
/// </summary>
/// <param name="EventID">EventID integer</param>
/// <param name="AccoutnID">AccountID integer</param>
/// <returns>A list of materials that is linked with the event and a account</returns>
public static List <Material> GetMaterialforAccountonEvent(int EventID, int AccountID)
{
List <Material> MaterialList = new List <Material>();
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "SELECT * FROM Materiaal WHERE EventID = @EventID AND AccountID = @AccountID";
cmd.Parameters.Add(new SqlParameter("EventID", EventID));
cmd.Parameters.Add(new SqlParameter("AccountID", AccountID));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int ID = Convert.ToInt32(reader["ID"]);
int Price = Convert.ToInt32(reader["Prijs"]);
string Type = (reader["Type"].ToString());
int? eventid = (reader["EventID"] != DBNull.Value) ? Convert.ToInt32(reader["EventID"]) : 0;
int? accountID = (reader["AccountID"] != DBNull.Value) ? Convert.ToInt32(reader["AccountID"]) : 0;
if (eventid == 0)
{
eventid = null;
}
if (accountID == 0)
{
accountID = null;
}
Material Material = new Material(ID, Type, Price, eventid, accountID);
MaterialList.Add(Material);
}
return(MaterialList);
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(MaterialList);
}
/// <summary>
/// When a Event is being creater so does a timeline
/// </summary>
/// <returns>True if Database allows the changes false if not</returns>
public static bool CreateTimeline()
{
bool Check = false;
int ID = DatabaseGetHighestID.GetHighestID("Tijdlijn");
int EventID = GetLatestEventID();
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "INSERT INTO Tijdlijn (ID, EventID) VALUES (@ID, @EventID)";
cmd.Parameters.Add(new SqlParameter("ID", ID));
cmd.Parameters.Add(new SqlParameter("EventID", EventID));
if (EventID > 0)
{
cmd.ExecuteNonQuery();
}
Check = true;
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
Check = false;
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(Check);
}
/// <summary>
/// Check if a user exist in the database
/// </summary>
/// <param name="Password">Currently submitted password</param>
/// <param name="Username">Currently submitted Username</param>
/// <returns>If record exists return a int that is not 0 or negative</returns>
public static int CheckUser(string Password, string Username)
{
int Check = 0;
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "SELECT * FROM Account WHERE Wachtwoord = @Password AND GebruikersNaam = @UserName";
cmd.Parameters.Add(new SqlParameter("Password", Password));
cmd.Parameters.Add(new SqlParameter("UserName", Username));
//cmd.ExecuteNonQuery();
//OracleDataReader reader = cmd.ExecuteReader();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Check = Convert.ToInt32(reader["ID"]);
return(Check);
}
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(Check);
}
/// <summary>
/// Get string that is requested
/// </summary>
/// <param name="ID">ID from account integer</param>
/// <param name="Data">Data you want to get string</param>
/// <returns>A string</returns>
public static string GetString(int ID, string Data)
{
string Check = "";
if (DatabaseConnectie.OpenConnection())
{
try
{
DatabaseConnectie.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = DatabaseConnectie.connect;
cmd.CommandText = "SELECT * FROM Account WHERE ID = @ID";
cmd.Parameters.Add(new SqlParameter("ID", ID));
//cmd.ExecuteNonQuery();
//OracleDataReader reader = cmd.ExecuteReader();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Check = (reader[Data].ToString());
return(Check);
}
}
catch (SqlException e)
{
Console.WriteLine("Query Failed: " + e.StackTrace + e.Message.ToString());
}
finally
{
DatabaseConnectie.CloseConnection();
}
}
return(Check);
}
