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