public static List <ListViewItem> getTakeAwayOrders() { List <ListViewItem> toReturn = new List <ListViewItem>(); dbConn con = new dbConn(); OleDbDataReader reader = default(OleDbDataReader); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT takeAwayOrders.orderID, takeAwayID from takeAwayOrders INNER JOIN customerOrder on takeAwayOrders.orderID = customerOrder.orderID WHERE customerOrder.orderPaid = false;"; cmd.Connection = con.oleconnection; try { con.open(); reader = cmd.ExecuteReader(); while (reader.Read()) { ListViewItem item = new ListViewItem(new String[] { reader["orderID"].ToString(), reader["takeAwayID"].ToString() }); toReturn.Add(item); } } catch (Exception ex) { } finally { con.close(); } return(toReturn); }
public static string createTakeAwayOrder() { dbConn con = new dbConn(); con.open(); OleDbCommand order = new OleDbCommand(); order.CommandType = CommandType.Text; order.CommandText = "INSERT INTO customerOrder(orderDate) values(@date)"; order.Parameters.AddWithValue("@date", DateTime.Today); order.Connection = con.oleconnection; order.ExecuteNonQuery(); order.CommandText = "SELECT @@IDENTITY"; String orderID = order.ExecuteScalar().ToString(); OleDbCommand takeAway = new OleDbCommand(); takeAway.CommandType = CommandType.Text; takeAway.CommandText = "INSERT INTO takeAwayOrders(orderID) values(@orderID)"; takeAway.Parameters.AddWithValue("@orderID", orderID); takeAway.Connection = con.oleconnection; takeAway.ExecuteNonQuery(); con.close(); return(orderID); }
public static string createDineInOrder(string tableID) { dbConn con = new dbConn(); con.open(); OleDbCommand order = new OleDbCommand(); order.CommandType = CommandType.Text; order.CommandText = "INSERT INTO customerOrder(orderDate) values(@date)"; order.Parameters.AddWithValue("@date", DateTime.Today); order.Connection = con.oleconnection; order.ExecuteNonQuery(); order.CommandText = "SELECT @@IDENTITY"; String orderID = order.ExecuteScalar().ToString(); OleDbCommand table = new OleDbCommand(); table.CommandType = CommandType.Text; table.CommandText = "UPDATE restaurantTables SET orderID = @orderID WHERE tableID = @tableID"; table.Parameters.AddWithValue("@orderID", orderID); table.Parameters.AddWithValue("@tableID", tableID); table.Connection = con.oleconnection; table.ExecuteNonQuery(); con.close(); return(orderID); }
public static string addItem(string itemName, string itemPrice, string itemCategory) { dbConn con = new dbConn(); try { con.open(); string sqlquery = "INSERT INTO menu(itemName,itemPrice,itemCategory)values(@itemName,@itemPrice,@itemCategory)"; OleDbCommand cmd = new OleDbCommand(sqlquery, con.oleconnection); cmd.Parameters.AddWithValue("@itemName", itemName); cmd.Parameters.AddWithValue("@itemPrice", itemPrice); cmd.Parameters.AddWithValue("@itemCategory", itemCategory); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT @@IDENTITY"; string itemID = cmd.ExecuteScalar().ToString(); return(itemID); } catch (Exception ex) { throw new System.ArgumentException("Unable to Add!"); } finally { con.close(); } }
public static void removeItemByItemID(string itemID) { dbConn con = new dbConn(); con.open(); string sqlquery = "UPDATE menu set itemValidity = FALSE WHERE itemID=@itemID;"; OleDbCommand cmd = new OleDbCommand(sqlquery, con.oleconnection); cmd.Parameters.AddWithValue("@itemID", itemID); cmd.ExecuteNonQuery(); con.close(); }
public static void deleteItem(string orderID, Item item) { dbConn con = new dbConn(); con.open(); string sqlQuery = "SELECT * FROM orderDetails WHERE orderID = @orderID AND itemID = @itemID;"; OleDbCommand cmd = new OleDbCommand(sqlQuery, con.oleconnection); cmd.Parameters.AddWithValue("@orderID", orderID); cmd.Parameters.AddWithValue("@itemID", item.itemID); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); if (reader.HasRows) //If item exist in the order { int quantity = int.Parse(reader["itemQuantity"].ToString()); if (quantity > 1) { //Since I couldn't get nested SQL queries to work. //Get old values for update. int oldItemQuantity = int.Parse(reader["itemQuantity"].ToString()); double oldSubPrice = double.Parse(reader["subTotalPrice"].ToString()); //Update the values //Derive item price from quantity and total price int newItemQuantity = oldItemQuantity - 1; double newSubPrice = (oldSubPrice / oldItemQuantity) * newItemQuantity; //Here comes the actual update query OleDbCommand update = new OleDbCommand("UPDATE orderDetails SET itemQuantity = @qty, subTotalPrice = @price WHERE orderID = @orderID AND itemID = @itemID", con.oleconnection); update.Parameters.AddWithValue("@qty", newItemQuantity); update.Parameters.AddWithValue("@price", newSubPrice); update.Parameters.AddWithValue("@orderID", orderID); update.Parameters.AddWithValue("@itemID", item.itemID); update.ExecuteNonQuery(); } else { OleDbCommand orderDetails = new OleDbCommand(); orderDetails.CommandType = CommandType.Text; orderDetails.CommandText = "DELETE FROM orderDetails WHERE orderID = @orderID AND itemID = @itemID;"; orderDetails.Connection = con.oleconnection; orderDetails.Parameters.AddWithValue("@orderID", orderID); orderDetails.Parameters.AddWithValue("@itemID", item.itemID); orderDetails.ExecuteNonQuery(); } } con.close(); }
public void login() { bool authenticated = false; dbConn con = new dbConn(); String sqlSelect = "SELECT loginUserID, loginUsername, loginPassword FROM Login where loginUsername = @username;"; OleDbCommand cmd = new OleDbCommand(sqlSelect, con.oleconnection); cmd.Parameters.AddWithValue("@username", usernameText.Text); OleDbDataReader reader = default(OleDbDataReader); try { con.open(); reader = cmd.ExecuteReader(); reader.Read(); if (reader.HasRows == true) { if (passwordText.Text == (string)reader["loginPassword"]) { usernameText.Text = (string)reader["loginUsername"]; authenticated = true; } } } catch (Exception ex) { } finally { con.close(); } if (authenticated) { posPanel pos = new posPanel(usernameText.Text); this.Hide(); pos.ShowDialog(); this.Show(); errorLabel.Visible = false; errorIcon.Visible = false; usernameText.Text = ""; passwordText.Text = ""; } else { errorLabel.ForeColor = Color.Red; errorLabel.Visible = true; errorIcon.Visible = true; } }
public static void orderPayment(string orderID) { dbConn con = new dbConn(); con.open(); OleDbCommand update = new OleDbCommand("UPDATE customerOrder SET orderPaid = TRUE WHERE orderID = @orderID", con.oleconnection); update.Parameters.AddWithValue("@orderID", orderID); update.ExecuteNonQuery(); update = new OleDbCommand("UPDATE restaurantTables SET orderID = NULL WHERE orderID = @orderID", con.oleconnection); update.Parameters.AddWithValue("@orderID", orderID); update.ExecuteNonQuery(); con.close(); }
public static List <ListViewItem> getOrderDetails(string orderID) { dbConn con = new dbConn(); con.open(); OleDbDataReader reader = default(OleDbDataReader); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT menu.itemID, itemName, itemQuantity, subTotalPrice from orderDetails, menu where orderID = @orderID AND orderDetails.itemID = menu.itemID"; cmd.Parameters.AddWithValue("@orderID", orderID); cmd.Connection = con.oleconnection; reader = cmd.ExecuteReader(); List <ListViewItem> toReturn = new List <ListViewItem>(); while (reader.Read()) { ListViewItem item = new ListViewItem(new String[] { reader["itemID"].ToString(), reader["itemName"].ToString(), reader["itemQuantity"].ToString(), currency.toCurrency(reader["subTotalPrice"].ToString()) }); toReturn.Add(item); } con.close(); return(toReturn); }
private void Settings_Load(object sender, EventArgs e) { dbConn con = new dbConn(); OleDbDataReader reader = default(OleDbDataReader); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT loginUsername, privilege FROM Login where loginUsername = @username"; cmd.Parameters.AddWithValue("@username", loggedInUser); cmd.Connection = con.oleconnection; try { con.open(); reader = cmd.ExecuteReader(); reader.Read(); if (reader.HasRows) { privilege = (string)reader["privilege"]; userLabel.Text = (string)reader["loginUsername"] + " (" + privilege + ")"; } if (!(privilege == "ADMIN")) { adminButton.Enabled = false; } } catch (Exception ex) { } finally { con.close(); } }