//Author: Abdulwahab Alansari /// <summary> /// Helper method to get all products for specific package /// </summary> /// <param name="packageId"></param> /// <returns>List<Product></returns> private static List <Product> GetProductsForPackage(int packageId) { List <Product> products = new List <Product>(); string productSql = "select p.ProductId, p.ProdName, s.SupplierId, s.SupName "; productSql += "from Packages_Products_Suppliers pps "; productSql += "JOIN Products_Suppliers ps on pps.ProductSupplierId = ps.ProductSupplierId "; productSql += "JOIN Products p on p.ProductId = ps.ProductId "; productSql += "JOIN Suppliers s on s.SupplierId = ps.SupplierId "; productSql += "where PackageId = " + packageId; DataRowCollection productRows = DB.getRows(productSql); foreach (DataRow productRow in productRows) { Supplier supplier = new Supplier { SupplierId = Convert.ToInt32(productRow["SupplierId"]), SupName = productRow["SupName"].ToString() }; Product product = new Product { ProductId = Convert.ToInt32(productRow["ProductId"]), ProdName = productRow["ProdName"].ToString(), Supplier = supplier }; products.Add(product); } return(products); }
//Author: Abdulwahab Alansari /// <summary> /// Helper method to get productSupplierId /// </summary> /// <param name="productId"></param> /// <param name="supplierId"></param> /// <returns></returns> private static int GetProductSupplierId(int productId, int supplierId) { //Get ProductSupplierId string sql = "SELECT ProductSupplierId FROM Products_Suppliers WHERE "; sql += "ProductId = " + productId; sql += " AND SupplierId = " + supplierId; DataRowCollection rows = DB.getRows(sql); int result = Convert.ToInt32(rows[0]["ProductSupplierId"]); return(result); }
//Author: Amin Aden public static List <Product> ShowProducts() { List <Product> ProductList = new List <Product>(); string sql = " select * from Products;"; DataRowCollection Rows = DB.getRows(sql); foreach (DataRow Row in Rows) { Product product = new Product() { ProdName = Row["ProdName"].ToString(), ProductId = Convert.ToInt32(Row["ProductID"].ToString()), }; ProductList.Add(product); } return(ProductList); }
//Author: Amin Aden public static List <Product> GetProducts() { List <Product> products = new List <Product>(); string packageSql = " SELECT ProductId, ProdName FROM Products"; DataRowCollection packageRows = DB.getRows(packageSql); foreach (DataRow packageRow in packageRows) { Product product = new Product { ProductId = Convert.ToInt32(packageRow["ProductId"]), ProdName = packageRow["ProdName"].ToString(), }; products.Add(product); } return(products); }
//Author: Mustafa Warsama public static List <Supplier> GetSuppliers() { // allows to get all suppliers table fields // this was done by Mustafa Warsama List <Supplier> suppliers = new List <Supplier>(); string sql = "SELECT SupplierID, SupName FROM Suppliers"; DataRowCollection rows = DB.getRows(sql); foreach (DataRow row in rows) { Supplier supplier = new Supplier { SupplierId = Convert.ToInt32(row["SupplierId"]), SupName = row["SupName"].ToString() }; suppliers.Add(supplier); } return(suppliers); }
//Author: Abdulwahab Alansari /// <summary> /// Helper method to get suppliers associated with specific product /// </summary> /// <param name="productId"></param> /// <returns></returns> private static List <Supplier> GetSuppliersByProduct(int productId) { List <Supplier> suppliers = new List <Supplier>(); string sql = "SELECT s.SupplierId, s.SupName FROM Products_Suppliers ps "; sql += "JOIN Suppliers s ON ps.SupplierId = s.SupplierId "; sql += "WHERE ps.ProductId = " + productId; DataRowCollection rows = DB.getRows(sql); foreach (DataRow row in rows) { Supplier supplier = new Supplier { SupplierId = Convert.ToInt32(row["SupplierId"]), SupName = row["SupName"].ToString() }; suppliers.Add(supplier); } return(suppliers); }
//Author: Abdulwahab Alansari /// <summary> /// Helper method to get all products that are not part of a specific package /// </summary> /// <param name="packageId"></param> /// <returns></returns> private static List <Product> GetProductsNotInPackage(int packageId) { List <Product> products = new List <Product>(); string packageSql = "SElECT ProductId, ProdName FROM Products "; packageSql += "WHERE ProductId not in (select ps.ProductId "; packageSql += "from Packages_Products_Suppliers pps "; packageSql += "JOIN Products_Suppliers ps on pps.ProductSupplierId = ps.ProductSupplierId "; packageSql += "where PackageId = " + packageId + ")"; DataRowCollection packageRows = DB.getRows(packageSql); foreach (DataRow packageRow in packageRows) { Product product = new Product { ProductId = Convert.ToInt32(packageRow["ProductId"]), ProdName = packageRow["ProdName"].ToString(), }; products.Add(product); } return(products); }
{ //Author: Abdulwahab Alansari /// <summary> /// Get all packages and the products associated with the packages /// </summary> /// <returns>List<Package> list of packages</returns> public static List <Package> GetPackages() { List <Package> packages = new List <Package>(); string packageSql = "SELECT PackageId, PkgName ,PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission FROM Packages"; DataRowCollection packageRows = DB.getRows(packageSql); foreach (DataRow packageRow in packageRows) { Package package = new Package { PackageId = Convert.ToInt32(packageRow["PackageId"]), PkgName = packageRow["PkgName"].ToString(), PkgStartDate = Convert.ToDateTime(packageRow["PkgStartDate"]), PkgEndDate = Convert.ToDateTime(packageRow["PkgEndDate"]), PkgDesc = packageRow["PkgDesc"].ToString(), PkgBasePrice = Convert.ToDecimal(packageRow["PkgBasePrice"]), PkgAgencyCommission = Convert.ToDecimal(packageRow["PkgAgencyCommission"]) }; package.Products = GetProductsForPackage(package.PackageId); packages.Add(package); } return(packages); }