public bool InsertDrinkNames(string drinkName, int drinkId)
{
bool success = true;
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "insert into DrinkNames (name, drink_ID) output inserted.id values(@name, @drink_ID);";
cmd.Parameters.AddWithValue("@name", drinkName);
cmd.Parameters.AddWithValue("@drink_ID", drinkId);
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
success = false;
}
}
}
return(success);
}
public int CreateOrder(Order order)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "insert into Orders (orderTime, status, bar_ID, customer_ID) output inserted.orderNumber, inserted.id values(@orderTime, @status, @bar_ID, @customer_ID);";
cmd.Parameters.AddWithValue("@orderTime", order.OrderTime);
cmd.Parameters.AddWithValue("@status", order.Status);
cmd.Parameters.AddWithValue("@bar_ID", order.Bar.ID);
cmd.Parameters.AddWithValue("@customer_ID", order.Customer.Id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
order.OrderNumber = reader.GetInt32("orderNumber");
order.Id = reader.GetInt32("id");
}
}
foreach (OrderLine orderLine in order.OrderLines)
{
using (SqlCommand cmdOrderLines = connection.CreateCommand())
{
cmdOrderLines.CommandText = "insert into OrderLines(quantity, subtotal, drink_ID, order_ID) values(@quantity, @subtotal, @drink_ID, @order_ID);";
cmdOrderLines.Parameters.AddWithValue("@quantity", orderLine.Quantity);
cmdOrderLines.Parameters.AddWithValue("@subtotal", orderLine.Subtotal);
cmdOrderLines.Parameters.AddWithValue("@drink_ID", orderLine.Drink.Id);
cmdOrderLines.Parameters.AddWithValue("@order_ID", order.Id);
cmdOrderLines.ExecuteNonQuery();
}
}
}
scope.Complete();
}
}
return(order.OrderNumber);
}
public void InsertPrice(Bar bar, Price price)
{
int result = 0;
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "insert into Prices (sellingPrice, byingPrice, ingredient_ID, bar_ID) values (@SellingPRice, @buyingPrice, @ingredientID, @barID)";
cmd.Parameters.AddWithValue("@SellingPrice", price.SellingPrice);
cmd.Parameters.AddWithValue("@buyingPrice", price.BuyingPrice);
cmd.Parameters.AddWithValue("@ingredientID", price.Ingredient.Id);
cmd.Parameters.AddWithValue("@barID", bar.ID);
result = cmd.ExecuteNonQuery();
}
}
if (result != 1)
{
throw new NotImplementedException();
}
}
public int FindZipcodeIdByZipcode(int zipcode)
{
int zipcodeId = 0;
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "select id from Zipcodes where zipcode = @zipcode;";
cmd.Parameters.AddWithValue("@zipcode", zipcode);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
zipcodeId = reader.GetInt32("id");
}
}
}
}
return(zipcodeId);
}
public bool Delete(int id)
{
bool success = false;
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = $"Delete from Bars where id = @id;";
cmd.Parameters.AddWithValue("@id", id);
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
success = true;
}
}
}
return(success);
}
public Measurement Find(int id)
{
/*
* id int primary key identity(1,1),
* type varchar(100) not null unique,
* proportion float
*/
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "select type, proportion from Measurements where id = @id";
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string type = reader.GetString("type");
try
{
float proportion = reader.GetFloat("proportion");
return(new Measurement()
{
Id = id, Type = type, Proportion = proportion
});
}
catch (Exception e)
{
return(new Measurement()
{
Id = id, Type = type
});
}
}
}
}
}
return(null);
}
public double FindDrinkPriceById(int drinkId, int barId)
{
double price = 0;
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "select p.sellingPrice, qfi.quantity from Prices p, Ingredients i, Drinks d, QuantifiedIngredients qfi, Bars b where d.id = @drinkId and b.id = @barId and d.id = qfi.drink_ID and qfi.ingredient_ID = i.id and p.ingredient_ID = i.id;";
cmd.Parameters.AddWithValue("@drinkId", drinkId);
cmd.Parameters.AddWithValue("@barId", barId);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
price += (double)reader.GetDecimal("sellingPrice") * reader.GetDouble("quantity");
}
}
}
}
return(price);
}
public Address FindAddress(int id)
{
Address a = new Address();
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "select (name, zipcode_Id) from Addresses where id = @id";
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
a.AddressName = reader.GetString("name");
a.Zipcode = LocationDB.instance.findZipById(reader.GetInt32("zipcode_ID"));
}
}
return(a);
}
}
}
public Customer Find(string username)
{
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "select id, name, phonenumber, email, username from customers where username = @username";
cmd.Parameters.AddWithValue("@username", username);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int cId = reader.GetInt32("id");
string name = reader.GetString("name");
string phonenumber = reader.GetString("phonenumber");
string email = reader.GetString("email");
return(new Customer(name, phonenumber, email, username, cId));
}
}
}
}
return(null);
}
public bool Update(Customer customer)
{
bool isSuccess = false;
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "update customers set name = @name, phonenumber = @phonenumber, email = @email where id=@id;";
cmd.Parameters.AddWithValue("@name", customer.Name);
cmd.Parameters.AddWithValue("@phonenumber", customer.PhoneNumber);
cmd.Parameters.AddWithValue("@email", customer.Email);
cmd.Parameters.AddWithValue("@id", customer.Id);
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 1)
{
isSuccess = true;
}
}
}
return(isSuccess);
}
public Country getCountryById(int countryId)
{
Country country = null;
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = $"SELECT id, name from Countries where id = @id;";
cmd.Parameters.AddWithValue("@id", countryId);
using (SqlDataReader resultSet = cmd.ExecuteReader())
{
while (resultSet.Read())
{
int id = resultSet.GetInt32(resultSet.GetOrdinal("id"));
string name = resultSet.GetString(resultSet.GetOrdinal("name"));
country = new Country(id, name);
}
}
}
}
return(country);
}
public List <Country> getCountries()
{
List <Country> countries = new List <Country>();
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = $"SELECT id, name from Countries;";
using (SqlDataReader resultSet = cmd.ExecuteReader())
{
while (resultSet.Read())
{
int id = resultSet.GetInt32(resultSet.GetOrdinal("id"));
string name = resultSet.GetString(resultSet.GetOrdinal("name"));
Country country = new Country(id, name);
countries.Add(country);
}
}
}
}
return(countries);
}
public Bar Create(Bar bar, int managerId, string password)
{
//TODO TransactionScope - Fjernes og timeout fejl kommer ikke ??
using (TransactionScope rootScope = new TransactionScope(TransactionScopeOption.Required))
{
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
if (bar.Address.Id == default(int))
{
bar.Address = LocationDB.Instance.CreateAddress(bar.Address);
}
using (SqlCommand cmd = connection.CreateCommand())
{
string hashedPassword = Security.GenerateHashedPassword(password, out string salt);
cmd.CommandText = "insert into Bars (name, phonenumber, email, username, hashedPassword, salt, address_ID, manager_ID) output INSERTED.ID values(@Name, @phonenumber, @email, @username, @hashedpassword, @salt, @addressId, @managerId);";
cmd.Parameters.AddWithValue("@name", bar.Name);
cmd.Parameters.AddWithValue("@addressId", bar.Address.Id);
cmd.Parameters.AddWithValue("@phonenumber", bar.PhoneNumber);
cmd.Parameters.AddWithValue("@email", bar.Email);
cmd.Parameters.AddWithValue("@username", bar.Username);
cmd.Parameters.AddWithValue("@managerId", managerId);
cmd.Parameters.AddWithValue("@hashedpassword", hashedPassword);
cmd.Parameters.AddWithValue("@salt", salt);
bar.ID = (int)cmd.ExecuteScalar(); //TODO
if (bar.ID == default(int))
{
return(null);
}
}
}
rootScope.Complete();
}
return(bar);
}
public Dictionary <int, DrinkViewModel> FindAllAvailableDrinks(Bar bar)
{
Dictionary <int, DrinkViewModel> drinks = new Dictionary <int, DrinkViewModel>();
using (SqlConnection connection = DBConnection.GetSqlConnection())
{
TransactionOptions transactionoption = new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionoption))
{
using (SqlCommand cmd = new SqlCommand("exec FindAllAvailableDrinks @BarId = @inputBarId", connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@inputBarId", bar.ID);
using (SqlDataReader reader = cmd.ExecuteReader())
{
//Der bliver returneret to resultset. Det første er ingredienserne der skal bruges til de drinks der kan lavet. Det andet er alle navnene på drinksne.
while (reader.Read())
{
int quantity = reader.GetInt32(0);
string type = reader.GetString(1);
string name = reader.GetString(2);
int id = reader.GetInt32(3);
string ingredient = $"{quantity} {type} {name}, "; //quantity.ToString() + " " + type + " " + name + ", ";
if (drinks.Count == 0 || !drinks.ContainsKey(id))
{
DrinkViewModel drinkView = new DrinkViewModel
{
Id = id,
Ingredients = ingredient,
Names = new List <string>()
};
drinks.Add(id, drinkView);
}
else
{
drinks[id].Ingredients += ingredient;
}
}
//Avancerer til næste resultset.
reader.NextResult();
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
drinks[id].Names.Add(name);
}
}
}
}
return(drinks);
}
}