//save new product-supplier into database
private void btnProdSupplierSave_Click(object sender, EventArgs e)
{
if (cboProductSupplierAdd.SelectedValue != null)
{
ProductSupplier ps = new ProductSupplier
{
ProductId = currentProduct.ProductId,
SupplierId = Convert.ToInt32(cboProductSupplierAdd.SelectedValue)
};
int addedProdSupplierId = ProductSupplierDB.AddProductSupplier(ps);
if (addedProdSupplierId != 0)
{
//refresh suppliers part of form
SetUpFrmControls();
LoadProductSuppliers(currentProduct.ProductId);
}
else
{
MessageBox.Show("A problem occured adding this item to the database. Please try again.");
}
}
else
{
MessageBox.Show("Please select a supplier from the drop-down list to add.", "Data entry error");
}
}
/// <summary>
/// Add a product to the package
/// </summary>
/// <param name="productId"></param>
/// <param name="supplierId"></param>
private void AddProductToPackage(int productId, int supplierId)
{
try
{
//get the productsupplier from db
ProductSupplier productSupplier = ProductSupplierDB.GetProductSupplier(productId, supplierId);
//create the PPS to be added to db
PackageProductSupplier newPackagePS = new PackageProductSupplier()
{
ProductSupplierId = productSupplier.ProductSupplierID,
PackageId = PackageSelected.PackageId
};
if (!PackageValidator.IsPackageProductSupplierExisting(newPackagePS))
{
//add packageproductsupplier to db
PackageProductSupplierDB.Add(newPackagePS);
GetBindedPackageProducts(PackageSelected.PackageId);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Unable to add product", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//get suppliers for the selected product from the databse and display in list box
private void LoadProductSuppliers(int prodId)
{
//clear suppliers list
lvProdSuppliers.Items.Clear();
//get suppliers from database
productSupplierList = ProductSupplierDB.GetProductSuppliersByProductID(prodId);
//populate the controls
grpProdSupplier.Text = "Suppliers providing product: " + currentProduct.ProdName;
//filter suppliers list to only Product_Suppliers for the selected product
var prodSuppliersNames = from productSupplierItem in productSupplierList
join supplierItem in supplierList
on productSupplierItem.SupplierId equals supplierItem.SupID
orderby supplierItem.SupName
select new { productSupplierItem.ProductSupplierId, supplierItem.SupName };
//populate the list box
if (prodSuppliersNames != null)
{
int i = 0;
foreach (var item in prodSuppliersNames)
{
lvProdSuppliers.Items.Add(item.ProductSupplierId.ToString());
lvProdSuppliers.Items[i].SubItems.Add(item.SupName);
i++;
}
}
}
//clicks the button to remove one product from the suppliedProd listView
private void btnRemoveSuppliedProd_Click(object sender, EventArgs e)
{
tabSupplierAccessMode = AccessMode.Edit;
ProductSupplier removeSupProd = new ProductSupplier();
//get the selected Items
ListView.SelectedListViewItemCollection selectedProds = lvSuppliedProds.SelectedItems;
if (lvSuppliedProds.SelectedItems.Count > 0)//if there is selected item
{
//remove each product from the database
foreach (ListViewItem item in selectedProds)
{
removeSupProd.ProductId = Convert.ToInt32(item.SubItems[0].Text);
removeSupProd.SupplierId = Convert.ToInt32(txtSupplierId.Text);
// delete the data from the Products_Suppliers table
try
{
if (!(ProductSupplierDB.DeleteSupProd(removeSupProd)))
{
MessageBox.Show("Another user has updated or deleted that product.", "Database Error");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
refreshTabSuppliersListViews(); //refresh the ListViews
}
else
{
lblSupMessage.Text = "Note:Please select a product.";
}
}
//clicks the button to add one supplied product into the database and refresh the ListViews
private void btnAddSuppliedProd_Click(object sender, EventArgs e)
{
tabSupplierAccessMode = AccessMode.Edit;
ProductSupplier newSupProd = new ProductSupplier();
//get the selected Items
ListView.SelectedListViewItemCollection selectedProds = lvUnsuppliedProducts.SelectedItems;
if (lvUnsuppliedProducts.SelectedItems.Count > 0)//if there is selected item
{
//add the data of each item into the database
foreach (ListViewItem item in selectedProds)
{
newSupProd.ProductId = Convert.ToInt32(item.SubItems[0].Text);
newSupProd.SupplierId = Convert.ToInt32(txtSupplierId.Text);
// add the data to the Products_Suppliers table and get the ProductsSupplierId
try
{
newSupProd.ProductSupplierId = ProductSupplierDB.AddSupProd(newSupProd);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
refreshTabSuppliersListViews(); //refresh the ListViews
}
else
{
lblSupMessage.Text = "Note:Please select a product";
}
}
public void LoadProductSupplierList()
{
suppliers = SupplierDB.GetAllSuppliers();
products_suppliers = ProductSupplierDB.GetProductSuppliers();
var sup = from prod_supp in products_suppliers
join supp in suppliers
on prod_supp.SupplierId equals supp.SupplierId
where prod_supp.ProductId == (int)productIdComboBox.SelectedItem
select new
{
prod_supp.SupplierId,
supp.SupName,
prod_supp.ProductId,
prod_supp.ProductSupplierId
};
lvProductSupplier.Items.Clear();
int i = 0;
foreach (var s in sup)
{
lvProductSupplier.Items.Add(s.SupplierId.ToString());
lvProductSupplier.Items[i].SubItems.Add(s.SupName);
lvProductSupplier.Items[i].SubItems.Add(s.ProductId.ToString());
lvProductSupplier.Items[i].SubItems.Add(s.ProductSupplierId.ToString());
i++;
}
}
/// <summary>
/// Add selected product to package in detail view
/// </summary>
/// @author Harry
private void btnAddProdToPkg_Click(object sender, EventArgs e)
{
if (cmboBoxSupsOfProd.SelectedIndex > -1) // selection required
{
// get product and supplier ids (to get ProductSupplierId)
int prodId = products[cmboBoxProducts.SelectedIndex].ProductID;
int supplierId = suppliersOfProd[cmboBoxSupsOfProd.SelectedIndex].SupplierId;
try
{
// get product supplier id
int prodSuppId = ProductSupplierDB.GetId(prodId, supplierId);
// create new package product supplier object to enter into package products suppliers table
PackageProductSupplier newPkgProdSup = new PackageProductSupplier();
newPkgProdSup.PackageId = Convert.ToInt32(packageIdTextBox.Text);
newPkgProdSup.ProductSupplierId = prodSuppId;
// update packages product supplier table
bool success = PackageProductSupplierDB.AddNewPkgProdSupp(newPkgProdSup);
if (success)
{
// clear combo boxes
cmboBoxProducts.Items.Clear();
cmboBoxSupsOfProd.Items.Clear();
// show updated version of products associated with package
List <string> pkgProducts;
List <string> pkgProdSupps;
// access products and suppliers associated with current package
PackageDB.GetPackageProducts(packages[pkgPos].PackageId, out pkgProducts, out pkgProdSupps);
ShowPkgProducts(pkgProducts, pkgProdSupps);
ShowAllProducts();
cmboBoxSupsOfProd.Text = "";
cmboBoxProducts.Text = "";
cmboBoxSupsOfProd.Items.Clear();
MessageBox.Show("Added product successfully.", "Success");
}
else
{
// clear combo boxes
cmboBoxProducts.Items.Clear();
cmboBoxSupsOfProd.Items.Clear();
MessageBox.Show("Error occured while adding product.", "Failure");
}
}
catch (Exception ex)
{
MessageBox.Show("Error while adding product to package\n" + ex.Message,
ex.GetType().ToString());
}
}
else
{
MessageBox.Show("Please select a product and its supplier.", "Select Error");
}
}
private void refreshDataSource()
{
ProdSups = ProductSupplierDB.GetProductSuppliers();
var data = from ps in ProdSups
orderby ps.Supplier.Name
select new { ID = ps.ProductSupplierId, Supplier = ps.Supplier.Name, Product = ps.Product.Name, ps.FullName };
source.DataSource = data;
}
ProductSupplier GetProductSupplier()
{
int productID = 0;
int supplierID = 0;
productID = Convert.ToInt32(cboProduct.SelectedValue);
supplierID = Convert.ToInt32(cboSuppliers.SelectedValue);
return(ProductSupplierDB.GetProductSupplier(productID, supplierID));
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
productSuppliers = ProductSupplierDB.GetAllProductSuppliers();
ProductSupplierDataGrid.DataSource = productSuppliers;
}
catch (Exception ex)
{
MessageBox.Show("Error while loading Product Supplier Data: " + ex.Message,
ex.GetType().ToString());
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (checkDuplicate(cmbSupplier.Text, cmbProd.Text))
{
MessageBox.Show("This Product Supplier Already Exists");
cmbSupplier.Focus();
}
else
{
ProductSupplierDB.AddProductSupplier((Product)cmbProd.SelectedItem, (Supplier)cmbSupplier.SelectedItem);
refreshDataSource();
dgvProdSup.Focus();
}
}
/// <summary>
/// Jorge: Lists product supplier information when prodsup id is selected from combobox
/// </summary>
private void prodSupplierIdComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
int selectedId = Convert.ToInt32(prodSupplierIdComboBox.SelectedValue);
try
{
currentProdSup = ProductSupplierDB.GetProductSupplier(selectedId);
DisplayCurrentProdSupData();
}
catch (Exception ex)
{
MessageBox.Show("Error while retrieving product supplier with selected ID: " + ex.Message,
ex.GetType().ToString());
}
}
//delete product supplier item from database
private void btnProdSupplierDelete_Click(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection psSelectedItems =
this.lvProdSuppliers.SelectedItems;
//confirm that only one item is selected
if (psSelectedItems.Count != 0)
{
//get selected product-supplier id
int selectedProductSupplier = Convert.ToInt32(psSelectedItems[0].SubItems[0].Text);
ProductSupplier ps = ProductSupplierDB.getProductSupplierById(selectedProductSupplier);
if (ps != null) //Product-Supplier exists
{
try
{
if (ProductSupplierDB.DeleteProductSupplier(ps))
{
MessageBox.Show("Supplier removed from this product.");
}
else
{
MessageBox.Show("A problem occurred with removing this supplier.");
}
}
catch (Exception ex)
{
MessageBox.Show("Supplier-product item is part of a Package-Product-Supplier item and cannot be deleted.");
}
}
else
{
//ProductSupplier item does not exist in database - concurrency issue
MessageBox.Show("Supplier has already been removed from this product");
}
LoadProductSuppliers(currentProduct.ProductId);
SetUpFrmControls();
}
else
{
MessageBox.Show("Please select a supplier to delete.");
}
}
/// <summary>
/// Operations for click event on Add Supplier button.
/// </summary>
/// @author Chi
private void btnAddSupplier_Click(object sender, EventArgs e)
{
Supplier curSupplier; // Currently select supplier
int currentSupID; // Currently select supplier's ID
int currentProductID = Convert.ToInt32(txtPSProdID.Text); // Current product's ProductID
int currentSupplierIndex = lstboxSuppliers.SelectedIndex; // Current index of supplier selected in list box
if (currentSupplierIndex >= 0) // If user have selected an item from list box
{
// Set current supplier's ID to supplier selected in list box.
curSupplier = filteredSuppliers[currentSupplierIndex];
currentSupID = curSupplier.SupplierId;
// Verify if supplier is already added to product's suppliers list.
int result = ProductSupplierDB.GetId(currentProductID, currentSupID);
if (result < 0) // If supplier is not in product's supplier list.
{
try
{
// Add supplier to current product, reset/reload view for current product
// and notify user that supplier was added successfully.
ProductSupplierDB.AddProductSupplier(currentProductID, currentSupID);
loadProductSupplierData(currentProductID - 1);
MessageBox.Show("Supplier Added", "Success");
}
catch (Exception ex)
{
// Notify user of optimistic concurrency error or DB error.
MessageBox.Show("Unable to Add Supplier.\nThe Product Supplier was either removed or updated.\n\n" +
ex.Message, "Unexpected Database Error");
}
}
else
{
MessageBox.Show("That supplier is already added to the product", "Failed");
}
}
else
{
MessageBox.Show("Please choose a supplier.", "No supplier selected");
}
}
/// <summary>
/// Jorge: Save new prod sup data based on add or modify
/// </summary>
private void ProdSupplierSaveButton_Click(object sender, EventArgs e)
{
if (addProductSupplier)
{
this.PutProdSupplierData();
try
{
productSupplier.ProductSupplierId = ProductSupplierDB.AddProductSupplier(productSupplier);
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else // this is Modify
{
productSupplier.ProductId = currentProdSup.ProductId;
productSupplier.SupplierId = currentProdSup.SupplierId;
productSupplier.ProductSupplierId = currentProdSup.ProductSupplierId;
this.PutProdSupplierData();
try // try to update
{
if (!ProductSupplierDB.UpdateProductSupplier(currentProdSup, productSupplier)) // failed
{
MessageBox.Show("Another user has updated or deleted current product supplier", "Concurrency Error");
this.DialogResult = DialogResult.Retry;
}
else
{
currentProdSup = ProductSupplierDB.GetProductSupplier(productSupplier.ProductSupplierId);
Console.WriteLine(currentProdSup.ProdName);
this.DialogResult = DialogResult.OK;
}
}
catch (Exception ex)
{
MessageBox.Show("Error while updating: " + ex.Message, ex.GetType().ToString());
}
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
//ProductSupplierDB.CheckDependency((int)dgvProdSup.CurrentRow.Cells[1].ToString());
if (dgvProdSup.SelectedCells.Count > 0)
{
int selectedrowindex = dgvProdSup.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dgvProdSup.Rows[selectedrowindex];
int id = Convert.ToInt32(selectedRow.Cells["ID"].Value);
DialogResult result = MessageBox.Show("Are you sure you want to delete this record?" + "\n\nProductSupplier: " + Convert.ToString(selectedRow.Cells["FullName"].Value), "Confirmation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string message = ProductSupplierDB.DeleteProductSupplier(id);
MessageBox.Show(message);
refreshDataSource();
dgvProdSup.Focus();
}
}
}
/// <summary>
/// Add a product to the package
/// </summary>
/// <param name="productId"></param>
/// <param name="supplierId"></param>
private void AddProductToPackage(int productId, int supplierId)
{
//get the productsupplier from db
ProductSupplier productSupplier = ProductSupplierDB.GetProductSupplier(productId, supplierId);
//create the PPS to be added to db
PackageProductSupplier newPackagePS = new PackageProductSupplier()
{
ProductSupplierId = productSupplier.ProductSupplierID,
PackageId = PackageSelected.PackageId
};
if (!PackageValidator.IsPackageProductSupplierExisting(newPackagePS))
{
//add packageproductsupplier to db
PackageProductSupplierDB.Add(newPackagePS);
GetBindedPackageProducts(PackageSelected.PackageId);
}
}
private void cboProduct_SelectedIndexChanged(object sender, EventArgs e)
{
suppliers = new List <Supplier>();
int productID = (int)cboProduct.SelectedValue;
//Populate supplier list for all suppliers offering the selected product
//Get supplierIDs
var possibleSuppliers = ProductSupplierDB.GetProductSuppliers();
var availableSupplierIDs = from supplier in possibleSuppliers
where supplier.ProductId == productID
select supplier.SupplierId;
//Add suppliers to our list
foreach (int id in availableSupplierIDs)
{
suppliers.Add(SupplierDB.GetSupplier(id));
}
//Add list to the dropdown
cboSuppliers.DataSource = suppliers;
}
/* ----------------------------------------------------
* PRODUCT SUPPLIER TAB - JORGE PEREZ
* ---------------------------------------------------- */
/// <summary>
/// Jorge: Refreshes prod sup combobox
/// </summary>
/// <param name="lastIndex"></param>
private void RefreshProdSupComboBox(bool lastIndex = false)
{
int selectIndex = 0;
productSupplierIds = ProductSupplierDB.GetProductSupplierIds();
if (productSupplierIds.Count > 0) // if there are prod sups
{
prodSupplierIdComboBox.DataSource = productSupplierIds;
if (lastIndex == true)
{
selectIndex = prodSupplierIdComboBox.Items.Count - 1;
}
prodSupplierIdComboBox.SelectedIndex = selectIndex; // triggers selectedindexchanged
}
else // no packages
{
MessageBox.Show("There are no product suppliers. Add a product supplier in the database and restart the application ", "Empty Load");
Application.Exit();
}
}
/// <summary>
/// Operations for click event on Remove Supplier button.
/// </summary>
/// @author Chi
private void btnRemoveSupplier_Click(object sender, EventArgs e)
{
int currentSupID; // Currently select supplier's ID.
int currentProductID = Convert.ToInt32(txtPSProdID.Text); // Currently viewing product's ID.
int row = dataGridViewProdSuppliers.CurrentRow.Index; // Index of currently selected row in DGV.
Supplier selectedSupplier = curProductSuppliers[row]; // Supplier that was selected/highlighted on DGV.
currentSupID = selectedSupplier.SupplierId; // Set current supplier's ID to selected supplier.
try {
// Remove supplier for current product, reset/reload view for current product
// and notify user that supplier was added successfully.
ProductSupplierDB.RemoveProductSupplier(currentProductID, currentSupID);
loadProductSupplierData(currentProductID - 1);
MessageBox.Show("Supplier Removed.", "Success");
}
catch (SqlException ex)
{
if (ex.Errors[0].Number == 547 || // Foreign Key violation
ex.Errors[0].Number == 2601 // Primary key violation
)
{
// Notify user of DB violation
MessageBox.Show("Unable to remove Supplier.\n" +
"The Product Supplier is currently part of a booking or package.\n\n",
"Supplier In Use");
}
}
catch (Exception ex)
{
// Notify user of optimistic concurrency error.
MessageBox.Show("Unable to remove Supplier.\nThe Product Supplier was either removed or updated.\n\n" +
ex.Message, "Unexpected Database Error");
}
}
/// <summary>
/// Load data for Product Suppliers tab
/// </summary>
/// <param name="productIndex">Current index of Product Supplier list. Optional parameter.</param>
/// <returns>Rows affected by query, 1 for success and 0 for failure</returns>
// @author Chi
private void loadProductSupplierData(int productIndex = 0)
{
// Clear list box and data grid view
lstboxSuppliers.Items.Clear();
dataGridViewProdSuppliers.Columns.Clear();
// Set current product's view to index of 'products' list (global variable).
Products curProduct = products[productIndex];
curProductSuppliers = ProductSupplierDB.GetProductSuppliersByProductID(curProduct.ProductID);
// Display current product's ID and Name.
txtPSProdID.Text = curProduct.ProductID.ToString();
txtPSProdName.Text = curProduct.ProductName;
// Set customized properties for Data Grid View.
dataGridViewProdSuppliers.AutoGenerateColumns = false;
dataGridViewProdSuppliers.DataSource = curProductSuppliers;
// Created and bind custom column to data source's data property SupplierId.
DataGridViewTextBoxColumn idColumn = new DataGridViewTextBoxColumn();
idColumn.Name = "Supplier's ID";
idColumn.DataPropertyName = "SupplierId";
idColumn.Width = 100;
idColumn.ReadOnly = true;
// Created and bind custom column to data source's data property SupName.
DataGridViewTextBoxColumn nameColumn = new DataGridViewTextBoxColumn();
nameColumn.Name = "Supplier's Name";
nameColumn.DataPropertyName = "SupName";
nameColumn.Width = 239;
nameColumn.ReadOnly = true;
// Add data bound columns to Data Grid View.
dataGridViewProdSuppliers.Columns.Add(idColumn);
dataGridViewProdSuppliers.Columns.Add(nameColumn);
// List of all suppliers
List <Supplier> allSuppliers = SuppliersDB.GetAllSuppliers();
// Get a list of suppliers that are currently not added to current product's suppliers list
filteredSuppliers = new List <Supplier>(allSuppliers.Where(
Sup => curProductSuppliers.All(
sup2 => sup2.SupplierId != Sup.SupplierId
)));
// Add/Update filtered list to list box to prevent customer from adding
// same suppliers to product's supplier list.
foreach (Supplier sup in filteredSuppliers)
{
// check if supplier name is null
if (sup.SupName == null)
{
lstboxSuppliers.Items.Add("");
}
else
{
lstboxSuppliers.Items.Add(sup.SupName);
}
}
}
