Esempio n. 1
0
        /// <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());
        }
Esempio n. 2
0
        /// <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);
            }
        }
Esempio n. 3
0
        /// <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);
        }
Esempio n. 4
0
        /// <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);
        }
Esempio n. 5
0
        /// <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);
            }
        }
Esempio n. 6
0
 /// <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);
         }
     }
 }
Esempio n. 7
0
        /// <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);
        }
Esempio n. 8
0
        /// <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
        }