/// <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);
}
}
/// <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");
}
}
public static List<PackageProductSupplier> GetPackageProductSupplier(string filter)
{
List<PackageProductSupplier> packageproductsuppliers = new List<PackageProductSupplier>();
SqlConnection connection = TravelExpertsDB.GetConnection();
// Define the select query to retrieve data from multiple tables
string query = "SELECT Packages.PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc," +
" PkgBasePrice, PkgAgencyCommission, Packages_Products_Suppliers.ProductSupplierId, " +
" Suppliers.SupName, Products.ProductId, ProdName " +
"FROM Packages INNER JOIN Packages_Products_Suppliers on Packages.PackageId = Packages_Products_Suppliers.PackageId " +
"INNER JOIN Products_Suppliers on Packages_Products_Suppliers.ProductSupplierId = Products_Suppliers.ProductSupplierId " +
"INNER JOIN Suppliers on Products_Suppliers.SupplierId = Suppliers.SupplierId " +
"INNER JOIN Products on Products_Suppliers.ProductId = Products.ProductId " +
" WHERE Packages.PackageId > -1 " + filter;
SqlCommand command = new SqlCommand(query, connection);
try
{
// Open db connection and run query
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Read dataset fill Packages list
while (reader.Read())
{
// Create object to store data retrieved from select query
PackageProductSupplier packageproductsupplier = new PackageProductSupplier();
packageproductsupplier.PackageId = (int)reader["PackageId"];
packageproductsupplier.PkgName = reader["PkgName"].ToString();
packageproductsupplier.PkgStartDate = (DateTime)reader["PkgStartDate"];
packageproductsupplier.PkgEndDate = (DateTime)reader["PkgEndDate"];
packageproductsupplier.PkgDesc = reader["PkgDesc"].ToString();
packageproductsupplier.PkgBasePrice = Convert.ToDouble(reader["PkgBasePrice"]);
packageproductsupplier.PkgAgencyCommission = Convert.ToDouble(reader["PkgAgencyCommission"]);
packageproductsupplier.ProductSupplierId = (int)reader["ProductSupplierId"];
packageproductsupplier.SupName = reader["SupName"].ToString();
packageproductsupplier.ProductId = (int)reader["ProductId"];
packageproductsupplier.ProdName = reader["ProdName"].ToString();
// Add object to list
packageproductsuppliers.Add(packageproductsupplier);
}
return packageproductsuppliers; // Return the list of objects for PackagesSuppliers class
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Validates a package commission value
/// </summary>
/// <param name="tb"></param>
/// <returns></returns>
public static bool IsPackageProductSupplierExisting(PackageProductSupplier packageProductSupplier)
{
bool isExisting;
if (PackageProductSupplierDB.Get(packageProductSupplier) != null)
{
MessageBox.Show("This product is already part of the package", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
isExisting = true;
}
else
{
isExisting = false;
}
return(isExisting);
}
/// <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);
}
}
