private void role_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
if (role.SelectedIndex != 0)
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT roles_id, roles.title FROM user JOIN roles ON user.roles_id=roles.id WHERE roles.title = '" + role.Text + "'";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
roles_id = Int32.Parse(dt.Rows[0][0].ToString());
}
}
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
}
private void archiveButton_Click(object sender, EventArgs e)
{
try
{
String query1 = ("UPDATE menuitem SET status = !status WHERE name = '" + menuGridView.SelectedRows[0].Cells[1].Value.ToString() + "'");
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
using (MySqlCommand cmd = new MySqlCommand(query1, con))
{
cmd.Parameters.AddWithValue("@status", 0);
con.Open();
int result = cmd.ExecuteNonQuery();
//checking for errors
if (result < 0)
{
Console.WriteLine("Error editing data.");
}
else
{
MessageBox.Show("Item status has been updated.");
}
con.Close();
}
refreshMenu();
clear();
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
}
private void loginButton_Click(object sender, EventArgs e)
{
db = new DBConnect();
con = db.connect();
MySqlDataAdapter sda = new MySqlDataAdapter("SELECT firstname, inventoryAccess, reservationAccess, posAccess, menuAccess, userAccess " +
"FROM user " +
"INNER JOIN roles " +
"ON user.roles_id = roles.id " +
"WHERE username = '******' AND password = '******'", con);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count >= 1)
{
String firstname = dt.Rows[0][0].ToString();
bool inventory = dt.Rows[0][1].Equals(true);
bool reservation = dt.Rows[0][2].Equals(true);
bool pos = dt.Rows[0][3].Equals(true);
bool posmenu = dt.Rows[0][4].Equals(true);
bool accounts = dt.Rows[0][5].Equals(true);
main_form mainform = new main_form(inventory, reservation, pos, posmenu, accounts);
mainform.reference = this;
mainform.Show();
this.Hide();
}
else
{
MessageBox.Show("Username and Password do not match.");
}
}
private void createButton_Click(object sender, EventArgs e)
{
try
{
if (firstNameTxt.Text != "" && lastNameTxt.Text != "" && usernameTxt.Text != "" && passwordText.Text != "" && role.SelectedIndex != 0)
{
String query1 = "INSERT INTO user (username, password, firstname, lastname, roles_id) VALUES ('" + usernameTxt.Text + "','" + passwordText.Text + "','" + firstNameTxt.Text + "','" + lastNameTxt.Text + "'," + roles_id + ")";
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
con.Open();
MySqlCommand com = new MySqlCommand(query1, con);
com.ExecuteNonQuery();
con.Close();
refreshAccounts();
MessageBox.Show("User has been added!");
}
else
{
MessageBox.Show("Please fill in the required fields.");
}
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
MessageBox.Show("Username already exists!");
}
}
private void createButton_Click(object sender, EventArgs e)
{
try
{
if (itemNameTxt.Text != "" && priceTxt.Text != "")
{
String query1 = "INSERT INTO menuitem (name, sell_price, cost_price) VALUES ('" + itemNameTxt.Text + "'," + float.Parse(priceTxt.Text) + "," + float.Parse(costPriceTxt.Text) + ")";
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
con.Open();
MySqlCommand com = new MySqlCommand(query1, con);
com.ExecuteNonQuery();
con.Close();
refreshMenu();
MessageBox.Show("Item has been added!");
}
else
{
MessageBox.Show("Please fill in the textboxes.");
}
}catch (Exception ee)
{
MessageBox.Show(ee.ToString());
MessageBox.Show("Item already exists!");
}
clear();
}
private void menuSearch_TextChanged(object sender, EventArgs e)
{
DBConnect db = new DBConnect();
using (MySqlConnection conn = db.connect())
{
if (String.IsNullOrWhiteSpace(menuSearch.Text))
{
conn.Open();
string query = ("SELECT * FROM menuitem WHERE status = '" + status.ToString() + "'");
MySqlDataAdapter testing1 = new MySqlDataAdapter(query, conn);
DataTable testing2 = new DataTable();
testing1.Fill(testing2);
menuGridView.DataSource = testing2;
}
else
{
conn.Open();
string query = ("SELECT * FROM menuitem WHERE status = '" + status.ToString() + "' AND name LIKE '" + menuSearch.Text + "%'");
MySqlDataAdapter testing1 = new MySqlDataAdapter(query, conn);
DataTable testing2 = new DataTable();
testing1.Fill(testing2);
menuGridView.DataSource = testing2;
}
menuGridView.ClearSelection();
}
}
public void loadAllOrders()
{
try
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT menuitem.name, order_menuitem.quantity FROM dailysalesreport JOIN order_receipt ON order_receipt.dailysalesreport_id = dailysalesreport.id JOIN order_menuitem ON order_menuitem.order_id = order_receipt.id JOIN menuitem ON order_menuitem.menuitem_id = menuitem.id WHERE dailysalesreport.id = " + dsr_id + " ORDER BY menuitem.name";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
string[] array1 = new string[dt.Rows.Count]; //idk
int[] array2 = new int[] { }; //quantity
int sumthin, counter = 0;
if (dt.Rows.Count > 0)
{
//GETS UNIQUE NAMES
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!array1.Contains(dt.Rows[i][0].ToString()))
{
array1[counter] = dt.Rows[i][0].ToString();
//MessageBox.Show(array1[counter].ToString());
counter++;
}
}
array2 = new int[array1.Length];
//FILL 0
for (int a = 0; a < array1.Length; a++)
{
array2[a] = 0;
}
//CALCULATES QUANTITY
for (int j = 0; j < array1.Length; j++)
{
sumthin = Array.IndexOf(array1, dt.Rows[j][0].ToString());
array2[sumthin] = array2[sumthin] + Int32.Parse(dt.Rows[j][1].ToString());
}
DataTable dt2 = new DataTable();
DataRow dr;
dt2.Columns.Add("Name");
dt2.Columns.Add("Quantity");
dr = dt2.NewRow();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (array2[i] != 0)
{
dr["Name"] = array1[i];
dr["Quantity"] = array2[i];
dt2.Rows.Add(dr);
dr = dt2.NewRow();
}
}
ordersGridView.DataSource = dt2;
}
}catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
}
private void createButton_Click(object sender, EventArgs e)
{
try
{
if (roleTitle.Text != "")
{
String query1 = "INSERT INTO roles (title, inventoryAccess, reservationAccess, posAccess, menuAccess, userAccess) " +
"VALUES ('" + roleTitle.Text + "'," + inv.Checked + "," + res.Checked + "," +
pos.Checked + "," + menu.Checked + "," + user.Checked + ")";
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
con.Open();
MySqlCommand com = new MySqlCommand(query1, con);
com.ExecuteNonQuery();
con.Close();
refreshRoles();
MessageBox.Show("Role has been added!");
}
else
{
MessageBox.Show("Please assign Role Title.");
}
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
MessageBox.Show("Title already exists!");
}
}
private void refreshRoles()
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT * FROM roles";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
rolesGridView.DataSource = dt;
}
public void refreshMenu()
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT * FROM menuitem WHERE status = '1' && type = '1'";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
menuGridView.DataSource = dt;
}
private void refreshAccounts()
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT user.id, username, password, firstname, lastname, roles.title, roles_id FROM user JOIN roles ON user.roles_id=roles.id ORDER BY user.id";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
accountsGridView.DataSource = dt;
}
public void loadOrderReceipts()
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT id, orderTime as Time, totalPrice as Payment FROM order_receipt WHERE dailysalesreport_id = " + dsr_id + " ORDER BY id DESC";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
orderReceiptsGridView.DataSource = dt;
}
public void loadDSR()
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT id, reportDate as Date, revenue as Revenue FROM dailysalesreport ORDER BY reportDate DESC";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
DSRGridView.DataSource = dt;
}
public void loadOrders()
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT menuitem.name, order_menuitem.quantity FROM order_menuitem JOIN menuitem ON order_menuitem.menuitem_id = menuitem.id WHERE order_menuitem.order_id = " + order_id + " ORDER BY menuitem.name";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
ordersGridView.DataSource = dt;
}
private void updateButton_Click(object sender, EventArgs e)
{
try
{
if (roleTitle.Text != "")
{
String query1 = ("UPDATE roles SET title = @title, " +
"inventoryAccess = @inventoryAccess, " +
"reservationAccess = @reservationAccess, " +
"posAccess = @posAccess, " +
"menuAccess = @menuAccess, " +
"userAccess = @userAccess " +
"WHERE id = " + Int32.Parse(idText.Text));
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
using (MySqlCommand cmd = new MySqlCommand(query1, con))
{
cmd.Parameters.AddWithValue("@title", roleTitle.Text);
cmd.Parameters.AddWithValue("@inventoryAccess", inv.Checked);
cmd.Parameters.AddWithValue("@reservationAccess", res.Checked);
cmd.Parameters.AddWithValue("@posAccess", pos.Checked);
cmd.Parameters.AddWithValue("@menuAccess", menu.Checked);
cmd.Parameters.AddWithValue("@userAccess", user.Checked);
cmd.Parameters.AddWithValue("@roles_id", res.Checked);
con.Open();
int result = cmd.ExecuteNonQuery();
//checking for errors
if (result < 0)
{
Console.WriteLine("Error editing data.");
}
else
{
MessageBox.Show("Succesfully Updated!");
}
con.Close();
}
refreshRoles();
clear();
}
else
{
MessageBox.Show("Please assign the Role Title.");
}
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
}
private void updateButton_Click(object sender, EventArgs e)
{
try
{
if (firstNameTxt.Text != "" && lastNameTxt.Text != "" && usernameTxt.Text != "" && passwordText.Text != "" && role.SelectedIndex != 0)
{
String query1 = ("UPDATE user SET username = @username, password = @password, firstname = @firstname, lastname = @lastname, roles_id = @roles_id WHERE id = " + Int32.Parse(idText.Text));
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
using (MySqlCommand cmd = new MySqlCommand(query1, con))
{
cmd.Parameters.AddWithValue("@username", usernameTxt.Text);
cmd.Parameters.AddWithValue("@password", passwordText.Text);
cmd.Parameters.AddWithValue("@firstname", firstNameTxt.Text);
cmd.Parameters.AddWithValue("@lastname", lastNameTxt.Text);
cmd.Parameters.AddWithValue("@roles_id", roles_id);
con.Open();
int result = cmd.ExecuteNonQuery();
//checking for errors
if (result < 0)
{
Console.WriteLine("Error editing data.");
}
else
{
MessageBox.Show("Succesfully Updated!");
}
con.Close();
}
refreshAccounts();
clear();
}
else
{
MessageBox.Show("Please fill in the required fields.");
}
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
}
private void filterButton_Click(object sender, EventArgs e)
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
if (status == 1)
{
status = 0;
}
else
{
status = 1;
}
String Menu = "SELECT * FROM menuitem WHERE status = '" + status.ToString() + "' && type = '1'";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
menuGridView.DataSource = dt;
menuGridView.ClearSelection();
}
private void UMS_Accounts_Load(object sender, EventArgs e)
{
refreshAccounts();
accountsGridView.Columns["roles_id"].Visible = false;
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
String Menu = "SELECT title FROM roles ORDER BY id";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(Menu, con);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
role.Items.Add("");
for (int i = 0; i < dt.Rows.Count; i++)
{
role.Items.Add(dt.Rows[i][0].ToString());
}
}
}
private void updateButton_Click(object sender, EventArgs e)
{
try
{
String query1 = ("UPDATE menuitem SET name = @name, sell_price = @sell_price, cost_price = @cost_price WHERE id = " + id);
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
using (MySqlCommand cmd = new MySqlCommand(query1, con))
{
cmd.Parameters.AddWithValue("@name", itemNameTxt.Text);
cmd.Parameters.AddWithValue("@sell_price", priceTxt.Text);
cmd.Parameters.AddWithValue("@cost_price", costPriceTxt.Text);
con.Open();
int result = cmd.ExecuteNonQuery();
//checking for errors
if (result < 0)
{
Console.WriteLine("Error editing data.");
}
else
{
MessageBox.Show("Succesfully Updated!");
}
con.Close();
}
refreshMenu();
clear();
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
}
private void createButton_Click(object sender, EventArgs e)
{
if (orderGridView.Rows.Count <= 0)
{
MessageBox.Show("There are no orders to be made.");
}
else
{
try
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
MySqlCommand comm = new MySqlCommand("SELECT id, reportDate FROM dailysalesreport WHERE reportDate = CURDATE()", con);
MySqlDataAdapter adp = new MySqlDataAdapter(comm);
DataTable dt = new DataTable();
adp.Fill(dt);
//CREATES SALES REPORT FOR THE DAY IF DOES NOT EXISTS YET
if (dt.Rows.Count == 0)
{
con.Open();
MessageBox.Show("Sales Report for the day has been created!");
MySqlCommand comm2 = new MySqlCommand("INSERT INTO dailysalesreport (reportDate) VALUES (NOW())", con);
comm2.ExecuteNonQuery();
adp.Fill(dt);
con.Close();
DSR_ID = Int32.Parse(dt.Rows[0]["id"].ToString());
}
else
{
//GRABS THE ID OF CURRENT DSR
DSR_ID = Int32.Parse(dt.Rows[0]["id"].ToString());
}
//INSERTS ORDER RECEIPTS INTO CURRENT DSR
con.Open();
MySqlCommand comm3 = new MySqlCommand("INSERT INTO order_receipt (dailysalesreport_id, orderTime, totalPrice) " +
"VALUES (" + DSR_ID + ", CURRENT_TIME()," + totalPrice + ")", con);
comm3.ExecuteNonQuery();
//SELECTS BOTH THE ID'S OF ORDER_RECEIPTS AND MENUITEM
comm = new MySqlCommand("SELECT id FROM order_receipt ORDER BY id DESC", con);
adp = new MySqlDataAdapter(comm);
DataTable dt2 = new DataTable();
adp.Fill(dt2);
//GRABS THE ID'S OF CURRENT ORDER RECEIPT AND MENUITEMS
int orderID = Int32.Parse(dt2.Rows[0]["id"].ToString());
MySqlCommand command1;
MySqlCommand comm4;
int menuitemID;
//ADDS QUANTITY TO MENUITEM
foreach (DataGridViewRow row in orderGridView.Rows)
{
command1 = new MySqlCommand("SELECT id FROM menuitem WHERE name = '" + row.Cells[0].Value.ToString() + "'", con);
adp = new MySqlDataAdapter(command1);
DataTable dt3 = new DataTable();
adp.Fill(dt3);
menuitemID = Int32.Parse(dt3.Rows[0]["id"].ToString());
comm4 = new MySqlCommand("INSERT INTO order_menuitem (order_id, menuitem_id, quantity) " +
"VALUES (" + orderID + "," + menuitemID + "," + Int32.Parse(row.Cells[2].Value.ToString()) + ")", con);
comm4.ExecuteNonQuery();
}
//UPDATES DSR REVENUE
String updateRevenue = ("UPDATE dailysalesreport SET revenue = revenue + " + (totalPrice - totalCostPrice) + " WHERE reportDate = CURDATE()");
MySqlCommand updateRev = new MySqlCommand(updateRevenue, con);
updateRev.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
MessageBox.Show("Transaction has been recorded!");
orderGridView.Rows.Clear();
totalPrice = 0.00f;
totalCostPrice = 0.00f;
amountLabel.Text = "P" + totalPrice.ToString("0.00");
}
}
