/// <summary>
/// Get Products and Suppliers details from a list of Products
/// </summary>
/// <param name="lstProd"></param>
/// <returns>List of Products_Suppilers</returns>
public List <Products_Supplier> GetProducts_Suppliers(List <Product> lstProd)
{
// set up DataAccess
TravelExpertsDataContext dbContext = new TravelExpertsDataContext();
Products_Supplier ps; // temp Product_Supplier object
List <Products_Supplier> lstPS = new List <Products_Supplier>(); // temp list
// get ProductSupplierID based on ProductID
foreach (Product pd in lstProd)
{
ps = new Products_Supplier(); // create a new object each iteration
// get the ProductSupplierId based on the ProductID of the current item
ps.ProductSupplierId = Convert.ToInt32((from p in dbContext.Products_Suppliers
where p.ProductId == pd.ProductId
select p.ProductSupplierId).First());
ps.ProductId = pd.ProductId; // set ProductID
lstPS.Add(ps); // add it to the list
}
return(lstPS); //return the list of Product_Supplier
}
/// <summary>
/// When Save button clicked, proceed based on whether add or edit, validate inputs, then store in DB
/// </summary>
private void btnSave_Click(object sender, EventArgs e)
{
int suppID; // to hold the supplier ID later
// global validations: are there assigned products? is there a name?
if (lbAssigned.Items.Count == 0) // if no products are assigned
{
MessageBox.Show("The supplier must have at least one assigned product", "Missing data");
return;
}
else if (txtSuppName.Text.Length == 0) // if no name
{
MessageBox.Show("The supplier must have a name", "Missing data");
txtSuppName.Focus();
return;
}
else if (!Int32.TryParse(txtSuppID.Text, out suppID)) // if the ID isn't a valid integer
{
MessageBox.Show("The supplier ID must be a number without decimals", "Incorrect data");
txtSuppID.Text = "";
txtSuppID.Focus();
return;
}
try
{
using (TravelExpertsDataContext db = new TravelExpertsDataContext())
{
if (isNew) // if we're adding a new record
{
// new record validations
if (txtSuppID.Text.Length == 0) // if no ID
{
MessageBox.Show("The supplier must have an ID", "Missing data");
txtSuppID.Focus();
return;
}
// check if the supplier ID is already used
// look in the DB for the number of records with that ID -- expecting 0 or 1
int checkID = (from s in db.Suppliers where s.SupplierId == suppID select s).Count();
if (checkID > null) // if it found something, do an error
{
MessageBox.Show("The supplier ID is already in use. Please use another", "Incorrect data");
txtSuppID.Text = "";
txtSuppID.Focus();
return;
}
// make a new supplier object and give it the properties the user entered
Supplier newSupp = new Supplier();
newSupp.SupplierId = suppID;
newSupp.SupName = txtSuppName.Text;
// put it in the DB
db.Suppliers.InsertOnSubmit(newSupp);
// add the products to the Products_Suppliers table
foreach (Product p in lbAssigned.Items)
{
// create a new product-supplier record and give it the product and supplier IDs
Products_Supplier ps = new Products_Supplier();
ps.ProductId = p.ProductId;
ps.SupplierId = suppID;
// put it in the table
db.Products_Suppliers.InsertOnSubmit(ps);
}
}
else // if we're editing an existing record
{
// delete removed entries
foreach (Product p in originalProdList)
{
if (!lbAssigned.Items.Contains(p))
{
// find the record to delete
Products_Supplier deletedRecord = (Products_Supplier)db.Products_Suppliers.Where(ps => (ps.SupplierId == suppID && ps.ProductId == p.ProductId)).Single();
// delete it from the DB
db.Products_Suppliers.DeleteOnSubmit(deletedRecord);
}
}
// add new entries
foreach (Product p in lbAssigned.Items)
{
if (!originalProdList.Contains(p))
{
// create the record to add, with product ID and supplier ID
Products_Supplier addedRecord = new Products_Supplier();
addedRecord.ProductId = p.ProductId;
addedRecord.SupplierId = suppID;
// add it to the DB
db.Products_Suppliers.InsertOnSubmit(addedRecord);
}
}
// update the name in suppliers table (ID can't be changed)
Supplier curSupp = db.Suppliers.Single(s => s.SupplierId == suppID);
curSupp.SupName = txtSuppName.Text;
}
db.SubmitChanges(); // make the changes happen if we've got to this point with no problems.
}
}
catch (SqlException) // this will be thrown if there's a foreign key constraint problem
{
MessageBox.Show("Problem Saving Changes: One of the products you're trying to remove from this supplier is assigned to a package. " +
"Please remove this supplier's product from the package first, then try again", "Product In Use");
return;
}
catch (Exception ex) // generic exception catching
{
MessageBox.Show("Problem saving to database: " + ex.Message, ex.GetType().ToString());
}
finally
{
// update the DGV
LoadSuppliers();
if (!isNew) // if completing an edit, reload the details
{
LoadSupplierDetails(suppID);
}
else // if completing an add, clear to prepare for the next new supplier
{
PrepareNew();
}
}
}
/// <summary>
/// Fires when Save is clicked
/// </summary>
private void btnSave_Click(object sender, EventArgs e)
{
// set booleans for various checks later on
bool allGoodAdd = false; // True when adding Packages_Products_Suppliers records succesfully
bool allGoodRmv = false; // True when deleting Packages_Products_Suppliers records succesfully
bool noItems = false; // set to true if we have no items (no changes in Products of package)
// temporary lists and objects
List <Products_Supplier> prodsToAdd = new List <Products_Supplier>();
Packages_Products_Supplier ppsd = new Packages_Products_Supplier();
List <Packages_Products_Supplier> ppsdList = new List <Packages_Products_Supplier>();
Products_Supplier ps = new Products_Supplier();
// setup DataAccess
TravelExpertsDataContext dbContext = new TravelExpertsDataContext();
// if we are not adding a new record
if (!isAdd)
{
try
{
// Add new products to existing record
if (addProd.Count > 0)
{
// create lists for Products_Suppliers, Packages_Products_Suppliers
prodsToAdd = GetProducts_Suppliers(addProd);
ppsdList = GetPackages_Products_Suppliers(prodsToAdd);
// call the Save method, indicating true for save
allGoodAdd = Save_Packages_Products_Suppliers(ppsdList, true);
}
else
{
noItems = true; // no items to save
}
//Remove products from existing package
if (rmvProd.Count > 0)
{
// create lists for Products_Suppliers, Packages_Products_Suppliers
prodsToAdd = GetProducts_Suppliers(rmvProd);
ppsdList = GetPackages_Products_Suppliers(prodsToAdd);
// call the Save method, indicating false for delete
allGoodRmv = Save_Packages_Products_Suppliers(ppsdList, false);
}
else
{
noItems = true; // no items to remove
}
// Save main record detail
// if there are noItems or Add/Remove methods were successful
if (noItems || allGoodAdd || allGoodRmv)
{
// setup variables for later use
decimal basePrice, agcyComm;
// create a Package object with detail from DB based on PackageID
Package pkg = dbContext.Packages.Single(p => p.PackageId == Convert.ToInt32(txtPackageID.Text));
// set the various attributes of the object from form controls
pkg.PkgName = txtPkgName.Text;
pkg.PkgDesc = txtPkgDesc.Text;
pkg.PkgStartDate = dtpPkgStart.Value.Date;
pkg.PkgEndDate = dtpPkgEnd.Value.Date;
//if (pkg.PkgStartDate < pkg.PkgEndDate)
//{
if (txtPkgBase.Text.StartsWith("$")) // remove the leading $ if it exists
{
basePrice = Convert.ToDecimal(txtPkgBase.Text.Remove(0, 1));
}
else
{
basePrice = Convert.ToDecimal(txtPkgBase.Text);
}
if (txtPakComm.Text.StartsWith("$")) // remove the leading $ if it exists
{
agcyComm = Convert.ToDecimal(txtPakComm.Text.Remove(0, 1));
}
else
{
agcyComm = Convert.ToDecimal(txtPakComm.Text);
}
//set the object attributes
pkg.PkgBasePrice = basePrice;
pkg.PkgAgencyCommission = agcyComm;
if (basePrice > agcyComm) // check the Commision is not more than the base price
{
dbContext.SubmitChanges(); // save the changes
}
else
{
MessageBox.Show("Agency Commision is too high");
}
//}
}
else
{
MessageBox.Show("An error occurred saving the data, tasks cancelled");
}
}
catch (ChangeConflictException)
{
// if we have concurency exceptions, resolve them and contine the save
dbContext.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
dbContext.SubmitChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " - " + ex.ToString());
}
}
else // this is a new Package
{
// create lists, objects and variables needed later
prodsToAdd = GetProducts_Suppliers(addProd);
ppsdList = GetPackages_Products_Suppliers(prodsToAdd);
allGoodAdd = Save_Packages_Products_Suppliers(ppsdList, true);
decimal basePrice, agcyComm;
Package pkg = new Package(); // create a new Package object
// set object attributes based on form controls
pkg.PkgName = txtPkgName.Text;
pkg.PkgDesc = txtPkgDesc.Text;
pkg.PkgStartDate = dtpPkgStart.Value.Date;
pkg.PkgEndDate = dtpPkgEnd.Value.Date;
if (txtPkgBase.Text.StartsWith("$")) // remove the leading $ if it exists
{
basePrice = Convert.ToDecimal(txtPkgBase.Text.Remove(0, 1));
}
else
{
basePrice = Convert.ToDecimal(txtPkgBase.Text);
}
if (txtPakComm.Text.StartsWith("$")) // remove the leading $ if it exists
{
agcyComm = Convert.ToDecimal(txtPakComm.Text.Remove(0, 1));
}
else
{
agcyComm = Convert.ToDecimal(txtPakComm.Text);
}
// set object attributes
pkg.PkgBasePrice = basePrice;
pkg.PkgAgencyCommission = agcyComm;
if (basePrice > agcyComm) // ensure commision is less than base price
{
dbContext.Packages.InsertOnSubmit(pkg);
dbContext.SubmitChanges(); // submit the changes to the DB
currPkg = (from pk in dbContext.Packages
where pk.PkgName == pkg.PkgName
select pk).Single();
}
else
{
MessageBox.Show("Agency Commision is too high");
}
// need to retrieve the newly created package ID
prodsToAdd = GetProducts_Suppliers(addProd);
ppsdList = GetPackages_Products_Suppliers(prodsToAdd);
allGoodAdd = Save_Packages_Products_Suppliers(ppsdList, true);
NewOrClear();
}
LoadDGV();
lbAvail.Items.Clear();
gbDetails.Enabled = false;
}
private void detach_Products_Suppliers(Products_Supplier entity)
{
this.SendPropertyChanging();
entity.Product = null;
}
private void attach_Products_Suppliers(Products_Supplier entity)
{
this.SendPropertyChanging();
entity.Supplier = this;
}
partial void DeleteProducts_Supplier(Products_Supplier instance);
partial void UpdateProducts_Supplier(Products_Supplier instance);
partial void InsertProducts_Supplier(Products_Supplier instance);
