// Takes current selected Product_Supplier and adds it to the package [Ronnie]
private void btnAdd_Click(object sender, EventArgs e)
{
// Get ProductSupplierID from combobox
int prodSupID = Convert.ToInt32(productSupplierIdTextBox.Text);
int packageID = currentPackage.PackageId;
// if the current product and supplier combination does not exist for the package, add it to the database
if (!TravelExpertsQueryManager.ExistPackagesProductsSupplier(packageID, prodSupID))
{
// Create a PackagesProductSupplier with that ID, and the Package ID from the current package
Packages_Products_Supplier newPackProdSup = new Packages_Products_Supplier
{
ProductSupplierId = prodSupID,
PackageId = packageID
};
// Add that PackagesProductsSupplier to the db
using (travelexpertsDataContext dbContext = new travelexpertsDataContext())
{
// insert through data context object from the main form
dbContext.Packages_Products_Suppliers.InsertOnSubmit(newPackProdSup);
dbContext.SubmitChanges(); // submit to the database
}
// Re-load the datagrid view
refreshDataGrid();
}
else
{
MessageBox.Show("The selected product and supplier portfolio has already been added to the package. Please try again.", "Input Error");
}
}
public static List <Packages_Products_Supplier> GetPackages_Products_Suppliers()
{
SqlConnection connection = TravelExpertsDB.GetConnection();
List <Packages_Products_Supplier> results = new List <Packages_Products_Supplier>();
try
{
string sql = "SELECT * FROM Packages_Products_Suppliers";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Packages_Products_Supplier s = new Packages_Products_Supplier();
s.PackageId = Convert.ToInt32(reader["PackageId"]);
s.ProductSupplierId = Convert.ToInt32(reader["ProductId"]);
results.Add(s);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(results);
}
//Delete from GridView so it won't add to database [Ronnie]
private void btnDelete_Click(object sender, EventArgs e)
{
// Grab data from selected cell on gridview
int rowNum = grdProdSup.CurrentCell.RowIndex; // index of the current row
int prodSuppID = Convert.ToInt32(grdProdSup[0, rowNum].Value); // Column for ProductSupplierID
using (travelexpertsDataContext dbContext = new travelexpertsDataContext())
{
//
Packages_Products_Supplier packProdSupForRemoval = (from match in dbContext.Packages_Products_Suppliers
where (match.ProductSupplierId == prodSuppID &&
match.PackageId == currentPackage.PackageId)
select match).Single();
dbContext.Packages_Products_Suppliers.DeleteOnSubmit(packProdSupForRemoval);
dbContext.SubmitChanges(); // submit to the database
}
refreshDataGrid();
}
/// <summary>
/// On clicking drop products from packages
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDropPro_Click(object sender, EventArgs e)
{
//only activate button if that datagrid view has some elements
if (dgViewSubPack.Rows.Count > 0)
{
int rowIndex = dgPackageProductSuppliers.CurrentRow.Index; //needed to preserve state
int rowIndexSec = dgProd.CurrentRow.Index; //Just to preserve state
Package selectedPackage = packages[rowIndex]; //Package to drop from
Packages_Products_Supplier package_product_supplier = packages[rowIndex].packProd[dgViewSubPack.CurrentRow.Index]; //product supplier to drop
DialogResult resultPackageProductSupplier = MessageBox.Show("You are about to remove the product "
+ package_product_supplier.ProdName +
" by \n\n" + package_product_supplier.SupName + " from " + selectedPackage.Name + " ? ",
"Confirm Remove", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (resultPackageProductSupplier == DialogResult.Yes)
{
try
{ //if not concurrent
if (!Packages_Products_SuppliersDB.DeletePackages_Products_Supplier(package_product_supplier))
{
MessageBox.Show("Another user has updated or deleted " +
"that product.", "Database Error");
}
//else refresh page
else
{
this.displayAll(rowIndex, rowIndexSec, true);
}
}
//Catch any other error
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
}
// Go back to last page without saving changes. This may involve some cleaning up, depending on the mode and if products were added [Eric]
private void btnCancel_Click(object sender, EventArgs e)
{
using (travelexpertsDataContext dbContext = new travelexpertsDataContext())
{
// First, we have to check to see if any products were added to the package before cancelling
if (didAddProducts) // this will be true if so - no need to spend time querying the db
{
// Get the current PPS entries in the database corresponsind to this package
List <Packages_Products_Supplier> ppsCurrent =
TravelExpertsQueryManager.GetPackagesProductsSuppliersByPackageID(currentPackage.PackageId);
// Next, we have to get the PPS entries to re-add (if they were deleted) and/or remove (if new ones were added)
// This is not a super efficient process, but packages shouldn't have enough products for it to make much difference
// First, get the ones to re-add
List <Packages_Products_Supplier> ppsToAdd = ppsSnapshot // Creating a list of Package_Product_Suppliers where...
.Where(ppsSnap => !ppsCurrent // ...for each snapshot entry, it is NOT the case that...
.Any(ppsCurr => ppsCurr.ProductSupplierId == ppsSnap.ProductSupplierId)) // ...any current entry has that snapshot entry's ProductSupplierID
.ToList();
// Next, the ones to remove
List <Packages_Products_Supplier> ppsToDelete = ppsCurrent // Creating a list of Package_Product_Suppliers where...
.Where(ppsCurr => !ppsSnapshot // ...for each current entry, it is NOT the case that...
.Any(ppsSnap => ppsCurr.ProductSupplierId == ppsSnap.ProductSupplierId)) // ...any snapshot entry has that current entry's ProductSupplierID
.ToList();
// Add the needed entries back
foreach (Packages_Products_Supplier ppsA in ppsToAdd)
{
// LINQ to SQL doesn't let you re-add old entity objects, so we need to create copies to add back in place
Packages_Products_Supplier clone = new Packages_Products_Supplier
{
PackageId = ppsA.PackageId,
ProductSupplierId = ppsA.ProductSupplierId
};
dbContext.Packages_Products_Suppliers.InsertOnSubmit(clone);
}
// Delete the entries to undo
foreach (Packages_Products_Supplier ppsD in ppsToDelete)
{
// Deleting only works on entities from the current context, so need to grab them
// I'm sure this could be done in the ones-to-remove LINQ query above, but I couldn't manage it
Packages_Products_Supplier deleteTarget = dbContext.Packages_Products_Suppliers // Search in the table...
.Single(pps => // ...for the one entry, with...
pps.ProductSupplierId == ppsD.ProductSupplierId && //...the matching ProductSupplierID...
pps.PackageId == ppsD.PackageId); //... and the matching PackageID
dbContext.Packages_Products_Suppliers.DeleteOnSubmit(deleteTarget);
}
// Save changes. Phew!
dbContext.SubmitChanges();
}
// One more step if in Add mode.
// A package was inserted into the database initially (to get the add products half to work).
// So, if cancelling, we want to delete it this created package.
if (isAdd)
{
// Delete package
Package packToDelete = dbContext.Packages
.Single(p => p.PackageId == currentPackage.PackageId);
dbContext.Packages.DeleteOnSubmit(packToDelete);
dbContext.SubmitChanges(); // submit to the database
}
}
// Exit the form
DialogResult = DialogResult.Cancel;
this.Close();
}
// Modifies the ProductSupplier associated with the selected row of the datagrid using the user inputs [Eric]
private void btnModifyProdSupp_Click(object sender, EventArgs e)
{
// Get user inputs
int newProdId = Convert.ToInt32(productIdComboBox.SelectedValue);
int newSupID = Convert.ToInt32(supplierIdComboBox.SelectedValue);
// The user may not have selected a value for the supplierIDComboBox yet, if so, they want the visible (top) one
if (newSupID == 0)
{
supplierIdComboBox.SelectedIndex = 0;
newSupID = Convert.ToInt32(supplierIdComboBox.SelectedValue);
}
// grab data from dropdown selectedvalues & hidden id field (productSupplierIdTextBox)
using (travelexpertsDataContext db = new travelexpertsDataContext())
{
if (addMode == false) // if modifying
{
// Get current product supplier ID
int prodSupID = Convert.ToInt32(productSupplierIdTextBox.Text);
// Grab the current entry from the database
Products_Supplier prodSup = db.Products_Suppliers
.Single(ps => ps.ProductSupplierId == prodSupID);
// Validate by ensuring this unique combination isn't in the database
Products_Supplier matchingProps = Validator.prodSupComboAlreadyExists(db, newProdId, newSupID, prodSupID);
if (matchingProps == null)
{
// Update with inputted values
prodSup.ProductId = newProdId;
prodSup.SupplierId = newSupID;
db.SubmitChanges();
}
else // there is a match for the product/supplier combo
{
//Give the user the option to change to this combination anyway (this will move all associated packages to the matching Product_Supplier)
DialogResult result = MessageBox.Show($"That product/supplier combination already exists (ID #{matchingProps.ProductSupplierId} - {matchingProps.Product.ProdName} - {matchingProps.Supplier.SupName}). Would you like to change any associated packages to have the selected product?",
"Existing Product/Supplier", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
// Get all Package_Product_Suppliers entries that refer to the current ProdSup ID (these will need to be modified)
List <Packages_Products_Supplier> ppsWithCurrentProdSupID = db.Packages_Products_Suppliers.Where(pps => pps.ProductSupplierId == prodSupID).ToList();
// Go through each, updating the ProdSupID to the existing combination
foreach (Packages_Products_Supplier pps in ppsWithCurrentProdSupID)
{
// We can't directly update ProdSupID in an existing entry as it is part of the entry's Primary key.
//Instead, we have to delete it and create a new one.
int currentPackageId = pps.PackageId; // to keep track of the package ID
db.Packages_Products_Suppliers.DeleteOnSubmit(pps);
db.SubmitChanges(); // have to submit changes here otherwise we can't create a new one
try
{
using (travelexpertsDataContext db2 = new travelexpertsDataContext())
{
// Now, create a new PPS using the PPS and the new ProdSupID
Packages_Products_Supplier newPps = new Packages_Products_Supplier
{
PackageId = currentPackageId, // the same package id
ProductSupplierId = matchingProps.ProductSupplierId // the prod_sup id that matches what the user wants to change it to
};
db2.Packages_Products_Suppliers.InsertOnSubmit(newPps);
db2.SubmitChanges();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
MessageBox.Show($"Any associated packages successfully transffered to ID# {matchingProps.ProductSupplierId} - {matchingProps.Product.ProdName} - {matchingProps.Supplier.SupName})");
}
}
// Reload data
checkboxFilterProducts_CheckedChanged(sender, e);//this updates the main datagrid based on whether the filter is on
RefreshPackagesByProdSuppGrid();
RefreshProdSupId();
} // end modify
else // if in add mode
{
// Validate to ensure the combo is new
Products_Supplier match = Validator.prodSupComboAlreadyExists(db, newProdId, newSupID);
if (match == null) //if new
{
// create a new Product_Supplier with the data
Products_Supplier newProdSup = new Products_Supplier
{
ProductId = newProdId,
SupplierId = newSupID
};
// insert into db and save
db.Products_Suppliers.InsertOnSubmit(newProdSup);
db.SubmitChanges();
// Re-enable Add new button
btnAddProdSupp.Enabled = true;
// Reload main data
checkboxFilterProducts_CheckedChanged(sender, e); //updates main datagrid based on status of filter checkbox
// Go to the new entry in the gridview
checkboxFilterProducts.Checked = false; // uncheck filter so new product can be seen
int lastIndex = grdProductSuppliers.Rows.Count - 1; // get last row of the grid
grdProductSuppliers.Rows[lastIndex].Selected = true; // select it
grdProductSuppliers.FirstDisplayedScrollingRowIndex = lastIndex; // go down to it
grdProductSuppliers_CellClick(sender, new DataGridViewCellEventArgs(1, lastIndex));
// Reload related data
RefreshPackagesByProdSuppGrid();
productIdComboBox_SelectedIndexChanged(sender, e);
RefreshProdSupId();
}
else // if the combo already exists
{
// Alert the user
MessageBox.Show($"That product/supplier combination already exists (ID #{match.ProductSupplierId}).");
}
}
}
}
