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