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