public void AddOrderItem(OrderItem orderItem)
{
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
sql.Open();
StringBuilder sb = new StringBuilder();
string query = "INSERT INTO OrderItem (OrderMenuId, MenuItemId, TableId, Count, OrderStatus, Comment) Values (@OrderMenuId, @MenuItemId, @TableId, @Count, @OrderStatus, @Comment);";
sb.Append(query);
String sqlquery = sb.ToString();
SqlParameter OrderId = new SqlParameter("@OrderMenuId", SqlDbType.Int, 3);
SqlParameter MenuItemId = new SqlParameter("@MenuItemId", SqlDbType.Int, 5);
SqlParameter TableId = new SqlParameter("@TableId", SqlDbType.Int);
SqlParameter Count = new SqlParameter("@Count", SqlDbType.Int, 1);
SqlParameter OrderStatus = new SqlParameter("@OrderStatus", SqlDbType.Int);
SqlParameter Comment = new SqlParameter("@Comment", SqlDbType.NVarChar, 500);
SqlCommand command = new SqlCommand(sqlquery, sql);
command.Parameters.Add(OrderId).Value = orderItem.Order.OrderId;
command.Parameters.Add(MenuItemId).Value = orderItem.MenuItem.ItemId;
command.Parameters.Add(TableId).Value = orderItem.Table.TableId;
command.Parameters.Add(Count).Value = orderItem.Count;
command.Parameters.Add(OrderStatus).Value = orderItem.OrderStatus;
command.Parameters.Add(Comment).Value = orderItem.Comment;
command.ExecuteNonQuery();
sql.Close();
}
public int GetTableId(Table table)
{
int TableId = table.TableId;
int tableId = 0;
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
sql.Open();
string query = "SELECT TableId FROM RestaurantTable WHERE TableId = @TableId";
StringBuilder sb = new StringBuilder();
sb.Append(query);
String sqlquery = sb.ToString();
SqlParameter TableIdDB = new SqlParameter("@TableId", SqlDbType.Int, 3);
SqlCommand command = new SqlCommand(query, sql);
command.Prepare();
command.Parameters.Add(TableIdDB).Value = TableId;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
tableId = reader.GetInt32(0);
}
reader.Close();
sql.Close();
return(tableId);
}
public void ChangeTableStatus(int status, Table table)
{
TableService tableService = new TableService();
int tableStatus = status;
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
sql.Open();
string query = "UPDATE RestaurantTable Set TableStatus = @TableStatus WHERE TableId = @TableId";
StringBuilder sb = new StringBuilder();
sb.Append(query);
String sqlquery = sb.ToString();
SqlParameter TableId = new SqlParameter("@TableId", SqlDbType.Int, 3);
SqlParameter TableStatus = new SqlParameter("@tableStatus", SqlDbType.Int, 3);
SqlCommand command = new SqlCommand(query, sql);
command.Parameters.Add(TableId).Value = table.TableId;
command.Parameters.Add(TableStatus).Value = tableStatus;
command.ExecuteNonQuery();
sql.Close();
}
public List <Table> GetAllTables()
{
List <Table> TableList = new List <Table>();
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
sql.Open();
string query = "SELECT * FROM RestaurantTable";
StringBuilder sb = new StringBuilder();
sb.Append(query);
String sqlquery = query.ToString();
SqlCommand command = new SqlCommand(sqlquery, sql);
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int TableId = reader.GetInt32(0);
int TableStatus = reader.GetInt32(1);
Table table = new Table(TableId, TableStatus);
TableList.Add(table);
}
reader.Close();
sql.Close();
return(TableList);
}
public List <OrderItem> GetOrderItemsForOverview(Function function)
{
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
sql.Open();
IItemDAL itemDAL = new ItemDAL();
List <OrderItem> Orderitems = new List <OrderItem>();
StringBuilder sb = new StringBuilder();
string query;
if (function == Function.Barkeeper)
{
query = "SELECT OrderMenu.OrderMenuId, MenuItem.Name, OrderItem.Count, OrderMenu.TableId, OrderItem.Comment" +
" FROM OrderItem INNER JOIN OrderMenu on OrderItem.OrderMenuId = OrderMenu.OrderMenuId INNER JOIN MenuItem ON OrderItem.MenuItemId = MenuItem.ItemId" +
" WHERE OrderItem.MenuItemId > 21" +
" AND OrderItem.OrderStatus != 3 ORDER" +
" BY OrderMenu.TableId; ";
}
else
{
query = "SELECT OrderMenu.OrderMenuId, MenuItem.Name, OrderItem.Count, OrderMenu.TableId, OrderItem.Comment FROM OrderItem INNER JOIN OrderMenu on OrderItem.OrderMenuId = OrderMenu.OrderMenuId INNER JOIN MenuItem on OrderItem.MenuItemId = MenuItem.ItemId WHERE OrderItem.MenuItemId < 21 AND OrderItem.OrderStatus != 3 ORDER BY OrderMenu.TableId; ";
}
sb.Append(query);
String sqlquery = sb.ToString();
SqlCommand command = new SqlCommand(query, sql);
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int OrderMenuId = reader.GetInt32(0);
Order order = new Order(OrderMenuId);
string MenuItemName = reader.GetString(1);
MenuItem MenuItem = itemDAL.ItemGetByName(MenuItemName);
int Count = reader.GetInt32(2);
int TableId = reader.GetInt32(3);
Table table = new Table(TableId);
string Comment = reader.GetString(4);
OrderItem orderitem = new OrderItem(order, MenuItem, Count, table, Comment);
Orderitems.Add(orderitem);
}
sql.Close();
return(Orderitems);
}
public int AddOrder(Order order)
{
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
sql.Open();
StringBuilder sb = new StringBuilder();
string query = "INSERT INTO OrderMenu (TableId, OrderTime, Date, Paid, PaymentMethod) VALUES (@TableId, @OrderTime, @Date, @Paid, @PaymentMethod); ";
sb.Append(query);
String sqlquery = sb.ToString();
SqlParameter TableId = new SqlParameter("@TableId", SqlDbType.Int);
SqlParameter OrderTime = new SqlParameter("@OrderTime", SqlDbType.Time, 5);
SqlParameter Date = new SqlParameter("@Date", SqlDbType.Date, 3);
SqlParameter Paid = new SqlParameter("@Paid", SqlDbType.Bit);
SqlParameter PaymentMethod = new SqlParameter("@PaymentMethod", SqlDbType.Int);
SqlCommand command = new SqlCommand(sqlquery, sql);
DateTime now = DateTime.Now;
command.Parameters.Add(TableId).Value = order.Table.TableId;
command.Parameters.Add(OrderTime).Value = now.Hour + ":" + now.Minute + ":" + now.Second;
command.Parameters.Add(Date).Value = now.Date;
command.Parameters.Add(Paid).Value = order.Paid;
command.Parameters.Add(PaymentMethod).Value = order.PaymentMethod;
command.ExecuteNonQuery();
query = "SELECT MAX(OrderMenuId) FROM OrderMenu";
//sb.Append(query);
//sqlquery = sb.ToString();
//command = new SqlCommand(sqlquery, sql);
command = new SqlCommand(query, sql);
return((int)command.ExecuteScalar());
}
public Employee GetEmployee(int EmployeeId)
{
Employee employee = new Employee();
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
sql.Open();
string query = "SELECT * FROM Employee WHERE EmployeeId = @EmployeeId";
StringBuilder sb = new StringBuilder();
sb.Append(query);
String sqlquery = sb.ToString();
SqlParameter employeeId = new SqlParameter("@EmployeeId", SqlDbType.Int, 3);
SqlCommand command = new SqlCommand(query, sql);
command.Parameters.Add(employeeId).Value = EmployeeId;
SqlDataReader reader = command.ExecuteReader();
//command.Prepare();
while (reader.Read())
{
string Password = reader.GetString(1);
string FirstName = reader.GetString(2);
string LastName = reader.GetString(3);
DateTime BirthDay = reader.GetDateTime(4);
int PhoneNumber = reader.GetInt32(5);
string Email = reader.GetString(6);
string InternationBankAccountNumber = reader.GetString(7);
Function function = (Function)reader.GetInt32(8);
employee = new Employee(EmployeeId, Password, FirstName, LastName, BirthDay, PhoneNumber, Email, InternationBankAccountNumber, function);
}
reader.Close();
sql.Close();
return(employee);
}
public MenuItem ItemGetByName(string Name)
{
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
MenuItem menuItem = null;
sql.Open();
StringBuilder sb = new StringBuilder();
string query = "SELECT ItemId, Name, Price, Count FROM MenuItem WHERE Name = @Name";
sb.Append(query);
String sqlquery = query.ToString();
SqlParameter name = new SqlParameter("@Name", SqlDbType.NVarChar, 100);
SqlCommand command = new SqlCommand(sqlquery, sql);
command.Parameters.Add(name).Value = Name;
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
int Id = reader.GetInt32(0);
string NameDb = Name;
float Price = (float)reader.GetDouble(2);
int Count = reader.GetInt32(3);
menuItem = new MenuItem(Id, NameDb, Price, Count);
}
reader.Close();
sql.Close();
return(menuItem);
}
public List <Menu> GetItemByCategorie(int Id)
{
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
ItemDAL itemDAL = new ItemDAL();
sql.Open();
List <Menu> menus = new List <Menu>();
StringBuilder sb = new StringBuilder();
string query = "SELECT ItemId, MenuCategorie FROM Menu WHERE MenuCategorie = @Categorie";
sb.Append(query);
String sqlquery = sb.ToString();
SqlParameter id = new SqlParameter("@Categorie", SqlDbType.Int, 3);
SqlCommand command = new SqlCommand(sqlquery, sql);
command.Parameters.Add(id).Value = Id;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int ItemId = reader.GetInt32(0);
int MenuCategorie = reader.GetInt32(1);
MenuItem item = itemDAL.ItemGetById(ItemId);
Menu menu = new Menu(Id, item, MenuCategorie);
menus.Add(menu);
}
reader.Close();
sql.Close();
return(menus);
}
public MenuItem ItemGetById(int Id)
{
DBconnection conn = new DBconnection();
SqlConnection sql = conn.Dbconnection();
MenuItem menuitem = null;
sql.Open();
StringBuilder sb = new StringBuilder();
string query = "SELECT ItemId, Name, Price, Count FROM MenuItem WHERE ItemId = @ItemId";
sb.Append(query);
String sqlquery = sb.ToString();
SqlParameter ItemID = new SqlParameter("@ItemID", SqlDbType.Int, 3);
SqlCommand command = new SqlCommand(sqlquery, sql);
command.Parameters.Add(ItemID).Value = Id;
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
string name = reader.GetString(1);
float price = (float)reader.GetDouble(2);
int count = reader.GetInt32(3);
menuitem = new MenuItem(Id, name, price, count);
}
reader.Close();
sql.Close();
return(menuitem);
}
