/// <summary> /// Gets related product data pertaining to a specific Package from the database. [Team, special thanks to Ronnie] /// Created as a team, with special mention to Ronnie and Eric for a little extra solving. /// </summary> /// <param name="db">A context from the database</param> /// <param name="id">The int ID of the package this data pertains to.</param> /// <returns>The data query containing all needed info.</returns> public static object FindProdInfoByPackage(travelexpertsDataContext db, int id) // object is probably not a great return type, but couldn't figure out better option [Eric] { var ProdInfo = from Packages in db.Packages join Packages_Products_Suppliers in db.Packages_Products_Suppliers on Packages.PackageId equals Packages_Products_Suppliers.PackageId join Products_Suppliers in db.Products_Suppliers on Packages_Products_Suppliers.ProductSupplierId equals Products_Suppliers.ProductSupplierId join Products in db.Products on Products_Suppliers.ProductId equals Products.ProductId join Suppliers in db.Suppliers on Products_Suppliers.SupplierId equals Suppliers.SupplierId where Packages.PackageId == id orderby Packages.PackageId select new { Products_Suppliers.ProductSupplierId, Packages.PackageId, Packages.PkgName, Products.ProductId, Products.ProdName, Suppliers.SupplierId, Supplier = Suppliers.SupName }; return(ProdInfo.ToList()); }
/// <summary> /// Gets a list of all PackageProductSuppliers entries with a given package ID. [Eric] /// </summary> /// <param name="packageID">Int ID of the desired package.</param> /// <returns>List of PackageProductSuppliers</returns> public static List <Packages_Products_Supplier> GetPackagesProductsSuppliersByPackageID(int packageID) { using (travelexpertsDataContext db = new travelexpertsDataContext()) { List <Packages_Products_Supplier> ppsList = db.Packages_Products_Suppliers .Where(pps => pps.PackageId == packageID) .ToList(); return(ppsList); } }
/// <summary> /// Get a subset of suppliers with associations to one given product in the ProductSuppliers table. [Eric] /// </summary> /// <param name="db">DB context</param> /// <param name="productID">Int with ID for given product.</param> /// <returns>List of Supplier objects.</returns> public static List <Supplier> GetSuppliersByProductID(travelexpertsDataContext db, int productID) { List <Supplier> filteredSuppliers = (from prodsupp in db.Products_Suppliers join Products in db.Products on prodsupp.ProductId equals Products.ProductId join Suppliers in db.Suppliers on prodsupp.SupplierId equals Suppliers.SupplierId where Products.ProductId == productID select Suppliers).ToList(); return(filteredSuppliers); }
/// <summary> /// Get the ID for a particular Product_Supplier entry using the corresponding product id and supplier id. /// </summary> /// <param name="db"></param> /// <param name="ProductID">Int ID for the given product</param> /// <param name="SupplierID">Int ID for a paired supplier to that product.</param> /// <returns>ProductSupplier ID.</returns> public static int FindProdSuppID(travelexpertsDataContext db, int ProductID, int SupplierID) { int ProdSuppID = (from prodsupp in db.Products_Suppliers join Products in db.Products on prodsupp.ProductId equals Products.ProductId join suppliers in db.Suppliers on prodsupp.SupplierId equals suppliers.SupplierId where (Products.ProductId == ProductID && suppliers.SupplierId == SupplierID) select prodsupp.ProductSupplierId).SingleOrDefault(); return(ProdSuppID); }
/// <summary> /// Gets data for all packages which contain a particular Product/Supplier pair. [Eric] /// </summary> /// <param name="prodSuppID">The ID of the Product_Supplier pair being queried.</param> /// <returns>A list of Packages using this product/supplier.</returns> public static List <Package> GetPackagesByProdSuppID(int prodSuppID) { using (travelexpertsDataContext dbContext = new travelexpertsDataContext()) { // Want to return a list of all the IDs of packages using this prod_supp List <int> packageIDs = dbContext.Packages_Products_Suppliers // Query bridge table .Where(pps => pps.ProductSupplierId == prodSuppID) // Look for entries that have the provided prodsuppID .Select(pps => pps.PackageId).ToList(); // grab the package IDs from those matches and return them as a list // Use that list of package IDs to get package data for them all List <Package> packages = dbContext.Packages.Where(pkg => packageIDs.Contains(pkg.PackageId)).ToList(); // Return this lsit return(packages); } }
/// <summary> /// Determine whether the combination of package ID and ProductSupplier ID exists [Ronnie] /// </summary> /// <param name="packageID">Package ID</param> /// <param name="prodSuppID">Product Supplier ID</param> /// <returns>returns false if not found</returns> public static bool ExistPackagesProductsSupplier(int packageID, int prodSuppID) { using (travelexpertsDataContext db = new travelexpertsDataContext()) { var package = db.Packages_Products_Suppliers.SingleOrDefault(p => p.PackageId == packageID && p.ProductSupplierId == prodSuppID); if (package == null) { return(false); } else { return(true); } } }
/// <summary> /// Gets ProdSup data with associated Product Names and Supplier Names [Team, special thanks to Ronnie and Eric] /// </summary> /// <param name="db">Database context to pull from</param> /// <returns>Queryable list of ProductSupplier data</returns> public static IQueryable GetProductsSuppliersExtended(travelexpertsDataContext db) { var ProdInfo = from Products_Suppliers in db.Products_Suppliers join Products in db.Products on Products_Suppliers.ProductId equals Products.ProductId join Suppliers in db.Suppliers on Products_Suppliers.SupplierId equals Suppliers.SupplierId orderby Products_Suppliers.ProductSupplierId select new { Products_Suppliers.ProductSupplierId, Products.ProductId, Products.ProdName, Suppliers.SupplierId, Supplier = Suppliers.SupName }; return(ProdInfo); }
/// <summary> /// Overload that gets ProdSup data where only those that are associated with a list of package IDs (in Packages_Products_Suppliers)are used. [Eric] /// </summary> /// <param name="db">Database context to pull from</param> /// <param name="packageFilter">List of PackageIDs which we want to results to be associated to</param> /// <returns>Sortable list of ProdSup data</returns> public static IQueryable GetProductsSuppliersExtended(travelexpertsDataContext db, List <int> packageFilter) { var ProdInfo = from Products_Suppliers in db.Products_Suppliers join Products in db.Products on Products_Suppliers.ProductId equals Products.ProductId join Suppliers in db.Suppliers on Products_Suppliers.SupplierId equals Suppliers.SupplierId join Packages_Products_Suppliers in db.Packages_Products_Suppliers on Products_Suppliers.ProductSupplierId equals Packages_Products_Suppliers.ProductSupplierId where packageFilter.Contains(Packages_Products_Suppliers.ProductSupplierId) orderby Products_Suppliers.ProductSupplierId select new { Products_Suppliers.ProductSupplierId, Products.ProductId, Products.ProdName, Suppliers.SupplierId, Supplier = Suppliers.SupName }; return(ProdInfo.Distinct()); //use distinct to cull out possibility of duplicates }