//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);
}
