//delete the product selected from the previous form
private void btnDelete_Click(object sender, EventArgs e)
{
//if valid
if (Validator.IsPresent(txtProductName))
{
//send a confirmation message to delete
DialogResult result = MessageBox.Show("Are you sure you want to delete this Product?",
"Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
bool attempt = false;
try
{
//if yes delete
attempt = ProductsDB.deleteProduct(productId);
}
catch
{
MessageBox.Show("Cannot delete " + prodName + " when it is currently linked a Supplier." +
"Please remove all links for " + prodName + " in order to delete the product");
}
if (attempt)
{
MessageBox.Show("Successfully deleted " + prodName);
this.Close();
}
}
}
}
// Deletes the Product_Supplier ID
private void btnDelete_Click_1(object sender, EventArgs e)
{
prodName = cbProdName.Text;
supName = cbSupName.Text;
// Grabs the ID through product and supplier names
int prodID = ProductsDB.getProductId(prodName);
int supID = SuppliersDB.getSupplierId(supName);
// Confirmation message for delete
DialogResult result = MessageBox.Show("Are you sure you want to delete link between "
+ prodName + " and " + supName + "?",
"Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
try
{
if (!Products_SuppliersDB.DeleteProdSup(prodID, supID)) // Checks the DB
{
MessageBox.Show("Another user has updated or deleted " +
"that customer.", "Database Error");
}
}
catch
{
MessageBox.Show("Cannot delete current Product Supplier when it is added as a product in\n" +
"a package. Please remove the product from the package first");
}
}
this.Close();
}
// This method populates the combo box with available Products
private void LoadProductCB()
{
int supId = SuppliersDB.getSupplierId(suppName);
List <Products> temp = new List <Products>(); // Temp variable to store in order to convert string to int
List <Products> productList = new List <Products>();
// Grabs all product ID that doesn't have the particular supplier ID in Product_Supplier
temp = Products_SuppliersDB.GetNotInProductsID(supId);
try
{
// Converts the Product IDs to Product Names
foreach (Products products in temp)
{
products.ProdName = ProductsDB.getProdName(products.ProductId);
productList.Add(products);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
// Populate the combo box
try
{
cbProductName.DataSource = productList;
cbProductName.DisplayMember = "ProdName";
cbProductName.ValueMember = "ProductId";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
// This method populates the combo box with available Suppliers
private void LoadSupplierCB()
{
int prodId = ProductsDB.getProductId(prodName);
List <Suppliers> temp = new List <Suppliers>(); // Temp variable to store in order to convert string to int
List <Suppliers> supplierList = new List <Suppliers>();
// Grabs all supplier ID that doesn't have the particular supplier ID in Product_Supplier
temp = Products_SuppliersDB.GetNotInSuppliersID(prodId);
try
{
foreach (Suppliers supplier in temp)
{
// Converts the Supplier IDs to Supplier Names
supplier.SupName = SuppliersDB.getSupName(supplier.SupplierId);
supplierList.Add(supplier);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
// Populate the combo box
try
{
cbSupplierName.DataSource = supplierList;
cbSupplierName.DisplayMember = "SupName";
cbSupplierName.ValueMember = "SupplierId";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
//This method controls what happens when someone selects a product in the products listbox. When a product is selected
//the supplier list box is populated with the suppliers associated with the product.
private void lbProducts_SelectedIndexChanged(object sender, EventArgs e)
{
lbSuppliers.Items.Clear();//clear all the items in the list box for the suppliers
try
{
string prodName = lbProducts.GetItemText(lbProducts.SelectedItem); //Get the product name for the selected product
int productId = ProductsDB.getProductId(prodName); //find the productId of the selected item
//Get a list of supppiers for the selected product
List <Suppliers> suppliersSel = Products_SuppliersDB.GetSupForProd(productId);
//Display the suppliers for the selected product
foreach (Suppliers supplier in suppliersSel)
{
lbSuppliers.Items.Add(supplier.SupName);
}
}
catch (SqlException ex)
{
throw ex;
}
// Button controls
btnModifyProd.Visible = true;
btnDeleteProd.Visible = true;
btnDeleteData.Visible = true;
btnAddSupToProd.Visible = true;
btnAddProdToSup.Visible = false;
btnModifySup.Visible = false;
btnDeleteSup.Visible = false;
}
private void cBProduct_SelectedIndexChanged(object sender, EventArgs e)
{
btnAddProd.Visible = false;
btnDeleteProduct.Visible = false;
cBSupplier.Items.Clear();
cBSupplier.Visible = true;
lblSupplier.Visible = true;
cBSupplier.SelectedText = "";
cBSupplier.Text = "";
try
{
string prodName = cBProduct.GetItemText(cBProduct.SelectedItem); //Get the product name for the selected product
int productId = ProductsDB.getProductId(prodName); //find the productId of the selected item
//Get a list of supppiers for the selected product
List <Suppliers> suppliersSel = Products_SuppliersDB.GetSupForProd(productId);
//Display the suppliers for the selected product
foreach (Suppliers supplier in suppliersSel)
{
cBSupplier.Items.Add(supplier.SupName);
}
}
catch (SqlException ex)
{
throw ex;
}
}
private void cBProduct_SelectedIndexChanged(object sender, EventArgs e) // when a product index selected has changed
{
btnAddProd.Visible = false; // add product is not visible
btnDeleteProduct.Visible = false; // delete product is not visible
cBSupplier.Items.Clear(); // clear items in list
cBSupplier.Visible = true; // make supplier combo box visible
lblSupplier.Visible = true; // supplier label is visible
cBSupplier.SelectedText = ""; // clear selected text
cBSupplier.Text = ""; // clear supplier text
try
{
string prodName = cBProduct.GetItemText(cBProduct.SelectedItem); //Get the product name for the selected product
int productId = ProductsDB.getProductId(prodName); //find the productId of the selected item
//Get a list of supppiers for the selected product
List <Suppliers> suppliersSel = Products_SuppliersDB.GetSupForProd(productId);
//Display the suppliers for the selected product
foreach (Suppliers supplier in suppliersSel)
{
cBSupplier.Items.Add(supplier.SupName);
}
}
catch (SqlException ex)
{
throw ex;
}
}
//* Overall button controls *//
//This method controls the show all button. When this button is clicked all the products and suppliers will be shown in the
//listboxes
private void btnShowAll_Click(object sender, EventArgs e)
{
products = ProductsDB.GetProducts(); //All the products are retreived using ProductsDB.GetProducts()
suppliers = SuppliersDB.GetSuppliers(); //All the suppliers are retreived using SuppliersDB.GetSuppliers()
ReloadListboxes();
}
private void frmProduct_Supplier_Activated(object sender, EventArgs e)
{
products = ProductsDB.GetProducts(); //All the products are retreived using ProductsDB.GetProducts()
suppliers = SuppliersDB.GetSuppliers(); //All the suppliers are retreived using SuppliersDB.GetSuppliers()
ReloadListboxes();
}
private void btnDeleteProduct_Click(object sender, EventArgs e)
{
//supName is equal to the selected items at index 0 to text
string supName = (lvPkgDetails.Items[lvPkgDetails.SelectedItems[0].Index].Text);
//suppID is equal to the result of the getProductId method taking supName as an argument
int suppID = ProductsDB.getProductId(supName);
//prodName is equal to the selected items at index 0, sub-item index 1 to text
string prodName = lvPkgDetails.SelectedItems[0].SubItems[1].Text;
//prodID is equal to the result of the getSupplierId method taking prodName as an argument
int prodID = SuppliersDB.getSupplierId(prodName);
//productSupplierID is equal to the result of the get_PpsID method taking suppID and prodID as arguments
int psID = Products_SuppliersDB.Get_PpsID(suppID, prodID);
// pkgID is equal to the result of the GetPackageIDBy_PsID method taking PsID as an argument
int pkgID = PackagesDB.GetPackageIDBy_PsID(psID);
bool exists = false;
bool attempt = false;
if (!exists) // if it does not exist, do this
{
try
{
// attempt = add product to package method, taking product supplier id and package id as arguments
attempt = ProductsDB.deleteProductFromPkg(pkgID, psID);
//MessageBox.Show("Succesfully deleted");
}
catch (SqlException ex)
{
MessageBox.Show("Error modifying package\n" + ex);
}
if (attempt)
{
MessageBox.Show("Succesfully deleted");
lvPkgDetails.Items.Clear();
int i = 0;
//int pkgID = lvPackages.Items.IndexOf(lvPackages.SelectedItems[i]); -- this is an old logic used
List <Package_Product_Suppliers> pps_o = PackagesDB.GetProductSuppliersByPackage(pkgID);
foreach (Package_Product_Suppliers pps in pps_o)
{
lvPkgDetails.Items.Add(pps.ProdName);
lvPkgDetails.Items[i].SubItems.Add(pps.SupName);
i += 1;
}
}
}
else
{
MessageBox.Show("Unable to delete");
}
btnAddPackage.Visible = true;
btnDeleteProduct.Visible = false;
lvPkgDetails.Items.Clear();
}
// Grabbing the data needed to insert into Products_Suppliers Table
private Products_Suppliers PutInSuppToProd(Products_Suppliers prodSupp)
{
prodSupp.ProductSupplierId = Convert.ToInt32(txtProdSuppId.Text);
prodSupp.ProductId = ProductsDB.getProductId(txtProdName.Text); // Grabbing Product Id through Product name
prodSupp.SupplierId = SuppliersDB.getSupplierId(cbSupplierName.Text); // Grabbing Supplier Id through Supplier name
return(prodSupp);
}
private void btnConfirm_Click(object sender, EventArgs e)
{
if (cBSupplier.SelectedText != "")
{
string prodNameSelected = cBProduct.GetItemText(cBProduct.SelectedItem); //Get the product name for the selected product
int prodIdSelected = ProductsDB.getProductId(prodNameSelected); //find the productId of the selected item
// get thee supplier name from the combo box selection
string supNameSelected = cBSupplier.GetItemText(cBSupplier.SelectedItem);
// get the supplierID from the suppliername using the method in SuppliersDB
int supplierIdSelected = SuppliersDB.getSupplierId(supNameSelected);
// product supplier ID is equal to a method that retrieves it in a SQL Query
int ppsID = Products_SuppliersDB.Get_PpsID(prodIdSelected, supplierIdSelected);
// pkgID is equal to the selected ITEM at the index clicked to maintain consistency
int pkgID = Convert.ToInt32(lvPackages.Items[lvPackages.SelectedItems[0].Index].Text);
bool exists = false;
bool attempt = false;
if (!exists) // if it does not exist, do this
{
try
{
// attempt = add product to package method, taking product supplier id and package id as arguments
attempt = PackagesDB.AddProdToPackage(ppsID, pkgID);
}
catch (SqlException ex)
{
MessageBox.Show("Error modifying package\n" + ex);
}
if (attempt)
{
MessageBox.Show("Succesfully modified");
int i = 0;
lvPkgDetails.Items.Clear();
List <Package_Product_Suppliers> pps_o = PackagesDB.GetProductSuppliersByPackage(pkgID);
foreach (Package_Product_Suppliers pps in pps_o)
{
lvPkgDetails.Items.Add(pps.ProdName);
lvPkgDetails.Items[i].SubItems.Add(pps.SupName);
i += 1;
}
}
}
else
{
MessageBox.Show("Package is already in the database");
}
}
else
{
MessageBox.Show("Please choose a valid supplier");
}
}
// Opens Delete product form
private void btnDeleteProd_Click(object sender, EventArgs e)
{
prodNameSelected = lbProducts.GetItemText(lbProducts.SelectedItem);
prodIdSelected = ProductsDB.getProductId(prodNameSelected);
using (DeleteProducts deleteproduct = new DeleteProducts())
{
if (deleteproduct.ShowDialog() == DialogResult.OK)
{
}
}
}
// Opens Modify product form
private void btnModifyProd_Click(object sender, EventArgs e)
{
prodNameSelected = lbProducts.GetItemText(lbProducts.SelectedItem);
prodIdSelected = ProductsDB.getProductId(prodNameSelected);
using (frmModifyProd modifyproduct = new frmModifyProd())
{
if (modifyproduct.ShowDialog() == DialogResult.OK)
{
}
}
}
private void GetSup()
{
try
{
int productId = ProductsDB.getProductId(prodName);//find the productId of the selected item
//Get a list of supppiers for the selected product
List <Suppliers> suppliersSel = Products_SuppliersDB.GetSupForProd(productId);
//Display the suppliers for the selected product
foreach (Suppliers supplier in suppliersSel)
{
cbSupName.Items.Add(supplier.SupName);
}
}
catch (SqlException ex)
{
throw ex;
}
}
//update the information
private void btnUpdate_Click(object sender, EventArgs e)
{
if (Validator.IsPresent(txtProductName))
{
//if the information is correct, check if the information is in the list
string prodName = txtProductName.Text;
bool exists = false;
bool attempt = false;
List <Products> products = ProductsDB.GetProducts();
foreach (Products product in products)
{
if (product.ProdName == prodName)
{
exists = true;
}
}
if (!exists)
{
try
{
//if the name is unique , replace the other one
attempt = ProductsDB.updateProduct(prodName, oldprodName);
}
catch (SqlException ex)
{
MessageBox.Show("Error modifying product\n" + ex);
}
if (attempt)
{
MessageBox.Show("Successfully modified");
this.Close();
}
}
else
{
MessageBox.Show("Product name is already in the database");
}
}
}
//This method is used to get the Products for a specific supplier. It returns a list of products.
public static List <Products> GetProdForSup(int supplierId)
{
List <Products> productsList = new List <Products>(); //create a new productsList
Products products = null; //create a null product
SqlConnection con = TravelExpertsDB.GetConnection(); //create a connection
//Create a query which selects a productId from Products_Suppliers table where the supplierId is given
string selectQuery = "SELECT ProductId " +
"FROM Products_Suppliers " +
"WHERE SupplierId = @SupplierId";
SqlCommand selectCommand = new SqlCommand(selectQuery, con); //create a select command using SqlCommand
selectCommand.Parameters.AddWithValue("@SupplierId", supplierId); //Bind the supplierId to the @supplierId
try
{
con.Open(); //Open the connection
SqlDataReader reader = selectCommand.ExecuteReader(); //Execute the query and store the result in reader
while (reader.Read()) //read the products if it exists
{
products = new Products(); //create new product
products.ProductId = (int)reader["ProductId"]; //add productId to product
productsList.Add(products); //add product to product list
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();//close connection
}
//Right now all the products only have productIds and no product name. Run through each one and add product Name
foreach (Products prod in productsList)
{
//Use the ProductsDB.getProdName to get the prod name using productId
prod.ProdName = ProductsDB.getProdName(prod.ProductId);
}
return(productsList);//Return the product list
}
private void btnAdd_Click_1(object sender, EventArgs e)
{
//if the add button is pressed and the text is valid
if (Validator.IsPresent(txtAddProd))
{
// create a list
List <Products> products = ProductsDB.GetProducts(); //add the products information to the list
bool exists = false;
String newProdName = txtAddProd.Text;
foreach (Products product in products) //for product in list, check if theres the same name in the list
{
if (product.ProdName == newProdName)
{
exists = true;
}
}
if (!exists)
{
MessageBox.Show("Successfully added " + newProdName + " to the database");
ProductsDB.addProduct(newProdName);
}
this.Close();
}
}