private void btnSave_Click(object sender, EventArgs e)
{
try
{
using (TravelExpertDataContext dataContext = new TravelExpertDataContext())
{
Products_Supplier prodSupplier = null;
if (isModify)// MODIFY ITEM
{
prodSupplier = (from m in dataContext.Products_Suppliers
where m.ProductSupplierId == Convert.ToInt32(txtproductSupplierId.Text)
select m).Single();
prodSupplier.ProductId = Convert.ToInt32(productIdComboBox.SelectedItem);
prodSupplier.SupplierId = Convert.ToInt32(supplierIdcomboBox.SelectedItem);
}
else // ADD NEW ITEM
{
prodSupplier = new Products_Supplier
{
ProductId = Convert.ToInt32(productIdComboBox.SelectedItem),
SupplierId = Convert.ToInt32(supplierIdcomboBox.SelectedItem)
};// object initializer syntax
dataContext.Products_Suppliers.InsertOnSubmit(prodSupplier);
}
dataContext.SubmitChanges();
MessageBox.Show("Changes have been saved", "Data update");
DialogResult = DialogResult.OK;
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
/// <summary>
/// On clicking add product to package button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAddProdtoPack_Click(object sender, EventArgs e)
{
int rowIndexSec = dgProd.CurrentRow.Index; //needed to preserve state of tables
Products selectedProduct = products[rowIndexSec]; //selected product from product array
int rowIndex = dgPackageProductSuppliers.CurrentRow.Index; //needed to preserve state
Package selectedPackage = packages[rowIndex]; // selected package array to add to
Products_Supplier selectedSupplier = selectedProduct.Suppliers[dgSup.CurrentRow.Index]; //selected product supplier to add to package
DialogResult result = MessageBox.Show( //confirm add?
"You are about to add the product " + selectedProduct.ProdName + " by \n\n" +
selectedSupplier.SupName + " to "
+ selectedPackage.Name, "Add to Package?",
MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
try
{
Packages_Products_SuppliersDB.AddPackages_Products_Supplier(selectedPackage, selectedSupplier);
}
catch (SqlException ex)
{
switch (ex.Errors[0].Number)
{
//if primary key error is encountered
case 2627:
MessageBox.Show("You already added this Product", "Duplicate Error");
break;
}
}
catch (Exception ex) // catch any other issus
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
finally
{
//display all, and keep state
this.displayAll(rowIndex, rowIndexSec, true);
}
}
}
private void deleteSupplierButton_Click(object sender, EventArgs e)
{
// make sure that someon is selected before displaying the information
int index = productSupplierListbox.SelectedIndex;
// make sure that a course is selected
if (index == -1)
{
MessageBox.Show("Must select a course first");
return;
}
// issue a a confirmation dialog
DialogResult result1 = MessageBox.Show($"Do you wish to delete " +
$"{ProductSupplierList[index]} course?", "Confirmation Message", MessageBoxButtons.YesNo);
if (result1 == DialogResult.Yes)
{
// remove the item from the database and
//remove selected item from both the display the display list box
// and the backiging store list also
using (PackageDataContext dbContext = new PackageDataContext())
{
try //use try and linq to delete the only select one.
{
Products_Supplier currentPS = (from p in dbContext.Products_Suppliers
where p.ProductSupplierId == ProductSupplierList[index].ProductSupplierId
select p).Single();
dbContext.Products_Suppliers.DeleteOnSubmit(currentPS);
dbContext.SubmitChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
ProductSupplierList.RemoveAt(productSupplierListbox.SelectedIndex);
productSupplierListbox.Items.RemoveAt(productSupplierListbox.SelectedIndex);
}
}
// 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}).");
}
}
}
}
private void DisplayCurrentPackage()
{
if (ReadOnly)
{
// display current Product data
packageIdTextBox.Text = currentPackage.PackageId.ToString();
pkgNameTextBox.Text = currentPackage.PkgName;
pkgDescTextBox.Text = currentPackage.PkgDesc.ToString();
pkgBasePriceTextBox.Text = currentPackage.PkgBasePrice.ToString();
if (currentPackage.PkgStartDate is null)
{
pkgStartDateDateTimePicker.Value = DateTime.Now;
}
else
{
pkgStartDateDateTimePicker.Value = (DateTime)currentPackage.PkgStartDate;
}
if (currentPackage.PkgEndDate is null)
{
pkgEndDateDateTimePicker.Value = DateTime.Now;
}
else
{
pkgEndDateDateTimePicker.Value = (DateTime)currentPackage.PkgEndDate;
}
pkgAgencyCommissionTextBox.Text = currentPackage.PkgAgencyCommission.ToString();
try
{
using (PackageDataContext dbContext = new PackageDataContext())
{
var product_Suppliers = from pps in dbContext.Packages_Products_Suppliers
join pk in dbContext.Packages
on pps.PackageId equals pk.PackageId
join ps in dbContext.Products_Suppliers
on pps.ProductSupplierId equals ps.ProductSupplierId
join s in dbContext.Suppliers
on ps.SupplierId equals s.SupplierId
join p in dbContext.Products
on ps.ProductId equals p.ProductId
where pk.PackageId == currentPackage.PackageId
select new
{
s.SupName,
p.ProdName
};
foreach (var ps in product_Suppliers)
{
productSupplierListbox.Items.Add($"Product Name: {ps.ProdName}, Product Supplier: {ps.SupName}");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
// Read Only text box
packageIdTextBox.Enabled = false;
pkgNameTextBox.Enabled = false;
pkgStartDateDateTimePicker.Enabled = false;
pkgEndDateDateTimePicker.Enabled = false;
pkgDescTextBox.Enabled = false;
pkgBasePriceTextBox.Enabled = false;
pkgAgencyCommissionTextBox.Enabled = false;
productSupplierListbox.Enabled = false;
CancelBtn.Visible = false;
SavePackageBtn.Text = "OK";
productNameLabel.Visible = false;
supplierNameLabel.Visible = false;
productNameLabel.Visible = false;
addsupplierButton.Visible = false;
deleteSupplierButton.Visible = false;
productComboBox.Visible = false;
supplierComboBox.Visible = false;
}
else if (fullPackageModified)
{
// display current Product data
packageIdTextBox.Text = currentPackage.PackageId.ToString();
pkgNameTextBox.Text = currentPackage.PkgName;
pkgDescTextBox.Text = currentPackage.PkgDesc.ToString();
pkgBasePriceTextBox.Text = currentPackage.PkgBasePrice.ToString();
if (currentPackage.PkgStartDate is null)
{
pkgStartDateDateTimePicker.Value = DateTime.Now;
}
else
{
pkgStartDateDateTimePicker.Value = (DateTime)currentPackage.PkgStartDate;
}
if (currentPackage.PkgEndDate is null)
{
pkgEndDateDateTimePicker.Value = DateTime.Now;
}
else
{
pkgEndDateDateTimePicker.Value = (DateTime)currentPackage.PkgEndDate;
}
pkgAgencyCommissionTextBox.Text = currentPackage.PkgAgencyCommission.ToString();
try
{
using (PackageDataContext dbContext = new PackageDataContext())
{
var Product_Suppliers = from pps in dbContext.Packages_Products_Suppliers
join pk in dbContext.Packages
on pps.PackageId equals pk.PackageId
join ps in dbContext.Products_Suppliers
on pps.ProductSupplierId equals ps.ProductSupplierId
join s in dbContext.Suppliers
on ps.SupplierId equals s.SupplierId
join p in dbContext.Products
on ps.ProductId equals p.ProductId
where pk.PackageId == currentPackage.PackageId
select new
{
pps.ProductSupplierId,
s.SupplierId,
s.SupName,
p.ProductId,
p.ProdName
};
foreach (var ps in Product_Suppliers)
{
productSupplierListbox.Items.Add($"Product Name: {ps.ProdName}, Product Supplier: {ps.SupName}");
Products_Supplier newProduct_Supplier = new Products_Supplier();
newProduct_Supplier.ProductSupplierId = ps.ProductSupplierId;
newProduct_Supplier.SupplierId = ps.SupplierId;
newProduct_Supplier.ProductId = ps.ProductId;
ProductSupplierList.Add(newProduct_Supplier);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
} // Read Only text box
}
else
{
// display current Product data
packageIdTextBox.Text = currentPackage.PackageId.ToString();
pkgNameTextBox.Text = currentPackage.PkgName;
pkgDescTextBox.Text = currentPackage.PkgDesc.ToString();
pkgBasePriceTextBox.Text = currentPackage.PkgBasePrice.ToString();
if (currentPackage.PkgStartDate is null)
{
pkgStartDateDateTimePicker.Value = DateTime.Now;
}
else
{
pkgStartDateDateTimePicker.Value = (DateTime)currentPackage.PkgStartDate;
}
if (currentPackage.PkgEndDate is null)
{
pkgEndDateDateTimePicker.Value = DateTime.Now;
}
else
{
pkgEndDateDateTimePicker.Value = (DateTime)currentPackage.PkgEndDate;
}
pkgAgencyCommissionTextBox.Text = currentPackage.PkgAgencyCommission.ToString();
productSupplierLabel.Visible = false;
productNameLabel.Visible = false;
supplierNameLabel.Visible = false;
productNameLabel.Visible = false;
productSupplierListbox.Visible = false;
addsupplierButton.Visible = false;
deleteSupplierButton.Visible = false;
productComboBox.Visible = false;
supplierComboBox.Visible = false;
this.Size = new System.Drawing.Size(450, 400);
}
}
