private void combo_categories_SelectedValueChanged(object sender, EventArgs e)
{
DataTable existed_products = db.ExecuteSQL(string.Format(@"
SELECT product_name,
product_name_id
FROM products_names
WHERE category_id = {0}
ORDER BY product_name
", combo_categories.SelectedValue.ToString()));
combo_existed_products_names.DisplayMember = "product_name";
combo_existed_products_names.ValueMember = "product_name_id";
combo_existed_products_names.DataSource = existed_products;
combo_existed_products_names_2.DisplayMember = "product_name";
combo_existed_products_names_2.ValueMember = "product_name_id";
combo_existed_products_names_2.DataSource = existed_products;
}
private void combo_category_SelectedValueChanged(object sender, EventArgs e)
{
string sql = string.Format(@"
SELECT products.category_id AS category_id,
categories.category AS category_name,
products.product_id,
products_names.product_name,
units.unit AS unit_name,
units.id AS unit_id,
products.selling_price,
products.amount
FROM products
JOIN categories ON products.category_id = categories.id
JOIN units ON products.unit_id = units.id
JOIN products_names ON products.product_name_id = products_names.product_name_id
WHERE categories.id = {0}
ORDER BY products_names.product_name;", combo_category.SelectedValue.ToString());
dgv_all_products.DataSource = db.ExecuteSQL(sql);
}
private void btn_save_Click(object sender, EventArgs e)
{
DataAccessLayer.DBHandler db = new DataAccessLayer.DBHandler();
DataTable tbl = (DataTable)dgv_product_name_data.DataSource;
foreach (DataRow row in tbl.Rows)
{
string sql = string.Format("UPDATE products SET selling_price = {1} , purchasing_price = {0}, profit_margin = {2} WHERE product_id = {3} ",
row[3].ToString(),
row[4].ToString(),
row[5].ToString(),
row[0].ToString());
db.ExecuteSQL(sql);
}
this.Close();
}
public void search(string text)
{
string search_query = string.Format(@"
SELECT products.category_id AS category_id,
categories.category AS category_name,
products.product_id,
products_names.product_name,
units.unit AS unit_name,
units.id AS unit_id,
products.selling_price,
products.amount
FROM products
JOIN categories ON products.category_id = categories.id
JOIN units ON products.unit_id = units.id
JOIN products_names ON products.product_name_id = products_names.product_name_id
WHERE products_names.product_name LIKE '%{0}%'
ORDER BY category_id, products_names.product_name, products.selling_price
", text.Trim());
dgv_all_products.DataSource = db.ExecuteSQL(search_query);
}