private void view_customer_details() { db = new Database.DatabaseHandler(); db.openConnection(); //query = "SELECT * FROM customer"; query = "SELECT c.C_Name, c.Email, m.Type from customer as c inner join membership as m on c.M_ID = m.M_ID"; try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); MySqlDataAdapter myAdapter = new MySqlDataAdapter(); myAdapter.SelectCommand = cmd; DataTable dt = new DataTable(); myAdapter.Fill(dt); customerGridView.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } db.closeConnection(); }
private void search_btn_Click(object sender, EventArgs e) { search_av = search_product_availibility.Text; db = new Database.DatabaseHandler(); db.openConnection(); query = "SELECT p.P_Name, s.P_Quantity, b.Location, b.Phone from product as p inner join stores_in as s on p.P_ID = s.P_ID inner join branch as b on b.Branch_ID = s.Branch_ID where p.P_Name LIKE '" + search_av + "%'"; try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); MySqlDataAdapter myAdapter = new MySqlDataAdapter(); myAdapter.SelectCommand = cmd; DataTable dt = new DataTable(); myAdapter.Fill(dt); productAvailGridView.DataSource = dt; productAvailGridView.AutoResizeColumns(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } db.closeConnection(); }
private void loadCategory() { db = new Database.DatabaseHandler(); db.openConnection(); query = "SELECT C_Name FROM category"; try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); MySqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { top_pro_cat.Items.Add(dataReader.GetString("C_Name")); cat_name = dataReader.GetString("C_Name"); } top_pro_cat.Show(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } db.closeConnection(); }
public Manage_Product() { db = new Database.DatabaseHandler(); dbAdmin = new Database.DatabaseAdmin(); InitializeComponent(); loadCategory(); }
private void customer_Update_Click(object sender, EventArgs e) { db = new Database.DatabaseHandler(); db.openConnection(); String type = customer_comboBox.Text; //MessageBox.Show(type); if (string.Equals(type, "silver", StringComparison.OrdinalIgnoreCase)) { num = 2; } else if (string.Equals(type, "gold", StringComparison.OrdinalIgnoreCase)) { num = 3; } else if (string.Equals(type, "platinum", StringComparison.OrdinalIgnoreCase)) { num = 4; } else { num = 1; } string i = Convert.ToString(id); //MessageBox.Show(i); query = "UPDATE customer SET C_Name='" + customer_Fname.Text + "'" + " , Email = '" + customer_Email.Text + "' , M_ID =" + num + " where Email ='" + email + "'"; try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); cmd.ExecuteNonQuery(); MessageBox.Show("Updated"); view_customer_details(); } catch (Exception ev) { MessageBox.Show(ev.ToString()); } db.closeConnection(); }
private void customer_Insert_Click(object sender, EventArgs e) { db = new Database.DatabaseHandler(); db.openConnection(); //int num; String type = customer_comboBox.Text; MessageBox.Show(type); if (string.Equals(type, "silver", StringComparison.OrdinalIgnoreCase)) { num = 2; } else if (string.Equals(type, "gold", StringComparison.OrdinalIgnoreCase)) { num = 3; } else if (string.Equals(type, "platinum", StringComparison.OrdinalIgnoreCase)) { num = 4; } else { num = 1; } query = "INSERT INTO customer(C_Name,Email,M_ID) VALUES('" + customer_Fname.Text + "','" + customer_Email.Text + "'," + num + ")"; try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); cmd.ExecuteNonQuery(); MessageBox.Show("Inserted"); view_customer_details(); } catch (Exception ev) { MessageBox.Show(ev.ToString()); } db.closeConnection(); }
private void customer_Delete_Click(object sender, EventArgs e) { db = new Database.DatabaseHandler(); db.openConnection(); query = "DELETE FROM customer WHERE Email ='" + email + "'"; //MessageBox.Show(email); try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); cmd.ExecuteNonQuery(); MessageBox.Show("Deleted"); view_customer_details(); } catch (Exception ev) { MessageBox.Show(ev.ToString()); } db.closeConnection(); }
private void top_sell_search_Click(object sender, EventArgs e) { cat_name = top_pro_cat.SelectedItem.ToString(); fromDate = dateTimePicker1.Value.ToString("yyyy-MM-dd"); toDate = dateTimePicker2.Value.ToString("yyyy-MM-dd"); db = new Database.DatabaseHandler(); db.openConnection(); query = "SELECT p.P_Name FROM product as p INNER JOIN transaction as t on t.P_ID = p.P_ID INNER JOIN category as c on c.C_ID = p.C_ID" + " WHERE t.Date between '" + fromDate + "' AND '" + toDate + "' " + "AND c.C_Name = '" + cat_name + "' GROUP BY p.P_Name ORDER BY sum(t.Quantity) DESC"; try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); MySqlDataAdapter myAdapter = new MySqlDataAdapter(); myAdapter.SelectCommand = cmd; DataTable dt = new DataTable(); myAdapter.Fill(dt); topSellGridView.DataSource = dt; topSellGridView.AutoResizeColumns(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } db.closeConnection(); }
public DatabaseAdmin() { this.db = new DatabaseHandler(); }
private void transactionAdd_Click(object sender, EventArgs e) { n++; int quantity = Convert.ToInt32(salesman_quantity.Text); db = new Database.DatabaseHandler(); db.openConnection(); int id = Convert.ToInt32(salesman_P_ID.Text); query = "select P_Name from product where P_ID=" + id; string p_Name = ""; try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); cmd.ExecuteNonQuery(); p_Name = (String)cmd.ExecuteScalar(); } catch (Exception ev) { MessageBox.Show(ev.ToString()); } db.closeConnection(); db.openConnection(); query = "select Selling_Price from product where P_ID=" + id; //string s_Price; int price = 0; //double price = 0.0 ; try { //MySql.Data.MySqlClient.MySqlCommand myCommand = //new MySql.Data.MySqlClient.MySqlCommand(insertQuery, connection); // MySql.Data.MySqlClient.MySqlCommand cmd = // new MySql.Data.MySqlClient.MySqlCommand(query, db.getmyConn()); MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); cmd.ExecuteNonQuery(); price = Convert.ToInt32(cmd.ExecuteScalar()); p = price; } catch (Exception ev) { MessageBox.Show(ev.ToString()); } db.closeConnection(); if (flag == 0) { salesman_gridview.Columns.Add("Product Barcode", "Product Barcode"); salesman_gridview.Columns.Add("Product Name ", "Product Name "); salesman_gridview.Columns.Add("Price", "Price"); salesman_gridview.Columns.Add("Quantity", "Quantity"); flag = 1; } q = Convert.ToInt32(salesman_quantity.Text); total_Cost = total_Cost + (double)(p * q); price = p * q; salesman_gridview.Rows.Add(new object[] { id, p_Name, price, quantity }); }
private void transactionSave_Click(object sender, EventArgs e) { db = new Database.DatabaseHandler(); db.openConnection(); String dates = dateTimePicker1.Value.ToString("yyyy-MM-dd"); for (int i = 0; i < salesman_gridview.RowCount - 1; i++) { string P_ID = salesman_gridview.Rows[i].Cells[0].Value.ToString(); string Quantity = salesman_gridview.Rows[i].Cells[3].Value.ToString(); string Total_Price = salesman_gridview.Rows[i].Cells[2].Value.ToString(); //for (int i = 0; i < salesman_gridview.RowCount; i++) double total_Cost = Convert.ToDouble(Total_Price); if (m_ID == 1) { total_Cost = total_Cost - (total_Cost * 0.05); } if (m_ID == 2) { total_Cost = total_Cost - (total_Cost * 0.1); } if (m_ID == 3) { total_Cost = total_Cost - (total_Cost * 0.2); } if (m_ID == 4) { total_Cost = total_Cost - (total_Cost * 0.25); } if (i == 0) { query = "INSERT INTO transaction(P_ID , Quantity , Total_Price , Date) VALUES('" + (P_ID) + "','" + (Quantity) + "','" + total_Cost + "','" + dates + "')"; } else { query = "INSERT INTO transaction " + "SELECT transaction.T_ID, " + "'" + (P_ID) + "','" + (Quantity) + "','" + total_Cost + "','" + dates + "' " + "FROM transaction ORDER BY transaction.T_ID DESC LIMIT 1"; } try { MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); cmd.ExecuteNonQuery(); } catch (Exception ev) { MessageBox.Show(ev.ToString()); } } if (id != 0) { try { query = "INSERT INTO proceed " + "SELECT transaction.T_ID, " + "'" + id + "'" + " FROM transaction ORDER BY transaction.T_ID DESC LIMIT 1"; MySqlCommand cmd = new MySqlCommand(query, db.getmyConn()); cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } MessageBox.Show("Saved"); db.closeConnection(); }