/// <summary>
/// Jorge: List of product-supplier ids for combobox
/// </summary>
/// <returns></returns>
public static List <int> GetProductSupplierIds()
{
List <int> productsupplierIds = new List <int>(); // Empty Prod Supplier
int prodSupId;
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT ProductSupplierId " +
"FROM Products_Suppliers " +
"ORDER BY ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
// run command and process data
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read()) // while there is data
{
prodSupId = Convert.ToInt32(dr["ProductSupplierId"]);
productsupplierIds.Add(prodSupId);
}
}
}
}
return(productsupplierIds);
}
/// <summary>
/// Katrina Spencer: Retrieves list of products-suppliers
/// </summary>
/// <returns>products-suppliers</returns>
public static List <ProductSupplier> GetAllProductsSuppliers()
{
List <ProductSupplier> prodsSups = new List <ProductSupplier>(); // empty
ProductSupplier prodSup;
// connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"ORDER BY ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
// run command and process data
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read()) // while there is data
{
prodSup = new ProductSupplier();
prodSup.ProductSupplierId = (int)dr["ProductSupplierId"];
prodSup.ProductId = (int)dr["ProductId"];
prodSup.SupplierId = (int)dr["ProdName"];
prodsSups.Add(prodSup);
}
}
}
}
return(prodsSups);
}
/// <summary>
/// Katrina Spencer: Updates supplier info
/// </summary>
/// <param name="oldSup">old supplier info</param>
/// <param name="newSup">new supplier info</param>
/// <returns>success indicator</returns>
public static bool UpdateSupplier(Supplier oldSup, Supplier newSup)
{
bool result = false; // no success yet
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string updateStatement = "UPDATE Suppliers " +
"SET SupName = @NewSupName " +
"WHERE SupplierId = @OldSupplierId " + // identifies supplier
"AND (SupName = @OldSupName " +
"OR SupName IS NULL " +
"AND @OldSupName IS NULL)";
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
if (newSup.SupName == null) // if new SupName is null
{
cmd.Parameters.AddWithValue("@NewSupName", DBNull.Value); // set DBNull
}
else
{
cmd.Parameters.AddWithValue("NewSupName", (string)newSup.SupName);
}
cmd.Parameters.AddWithValue("@OldSupplierId", oldSup.SupplierId);
if (oldSup.SupName == null) // if old SupName is null
{
cmd.Parameters.AddWithValue("@OldSupName", DBNull.Value); // set DBNull
}
else
{
cmd.Parameters.AddWithValue("@OldSupName", (string)oldSup.SupName);
}
// open connection
connection.Open();
// execute UPDATE command
int count = cmd.ExecuteNonQuery();
if (count > 0) // if row(s) affected
{
result = true;
}
}
}
return(result);
}
/// <summary>
/// Katrina: Returns a list of new product suppliers that are not yet within the selected package
/// </summary>
/// <param name="pkgId"></param>
/// <returns>list of new prod sups</returns>
public static List <PackageProductSupplier> GetNewProductsSuppliers(int pkgId)
{
List <PackageProductSupplier> prodsSups = new List <PackageProductSupplier>(); // empty
PackageProductSupplier prodSup;
// connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT DISTINCT ps.ProductSupplierId, ps.ProductId, ps.SupplierId, ProdName, SupName " +
"FROM Packages_Products_Suppliers AS pps " +
"RIGHT JOIN Products_Suppliers AS ps " +
"ON pps.ProductSupplierId = ps.ProductSupplierId " +
"JOIN Products ON ps.ProductId = Products.ProductId " +
"JOIN Suppliers ON ps.SupplierId = Suppliers.SupplierId " +
"WHERE ps.ProductSupplierId NOT IN " +
"(SELECT ps.ProductSupplierId " +
"FROM Products_Suppliers AS ps " +
"JOIN Packages_Products_Suppliers AS pps " +
"ON ps.ProductSupplierId = pps.ProductSupplierId " +
"AND PackageId = @PackageId) " +
"ORDER BY ps.ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@PackageId", pkgId);
// run command and process data
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read()) // while there is data
{
prodSup = new PackageProductSupplier();
prodSup.ProductSupplierId = (int)dr["ProductSupplierId"];
prodSup.ProductId = (int)dr["ProductId"];
prodSup.ProdName = (string)dr["ProdName"];
prodSup.SupplierId = (int)dr["SupplierId"];
prodSup.SupName = (string)dr["SupName"];
prodsSups.Add(prodSup);
}
}
}
}
return(prodsSups);
}
/// <summary>
/// Katrina Spencer: Adds a new product
/// </summary>
/// <param name="prod">new product info</param>
/// <returns>new product id</returns>
public static int AddProduct(Product prod)
{
int prodId = 0;
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string insertStatement = "INSERT INTO Products (ProdName) " +
"OUTPUT INSERTED.ProductId " + // returns single value
"VALUES(@ProdName)";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);
// open connection
connection.Open();
prodId = (int)cmd.ExecuteScalar(); // returns one value
}
}
return(prodId);
}
/// <summary>
/// Katrina Spencer: Retrieves supplier info with given id
/// </summary>
/// <param name="supId">id of supplier to get</param>
/// <returns>supplier object</returns>
public static Supplier GetSupplier(int supId)
{
Supplier sup = null;
// connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT SupplierId, SupName " +
"FROM Suppliers " +
"WHERE SupplierId = @SupplierId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@SupplierId", supId);
// run command and process data
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
if (dr.Read()) // if data
{
sup = new Supplier();
sup.SupplierId = (int)dr["SupplierId"];
// determine if it is DBNull and set
int col = dr.GetOrdinal("SupName"); // column number of SupName
if (dr.IsDBNull(col)) // if reader contains DBNull in this column
{
sup.SupName = null; // make null in the object
}
else // if not null
{
sup.SupName = (string)(dr["SupName"]);
}
}
}
}
}
return(sup);
}
/// <summary>
/// Katrina Spencer: Retrieves list of products-suppliers with given packageid
/// </summary>
/// <param name="pkgId">id of package to get</param>
/// <returns>products-suppliers of a package</returns>
public static List <PackageProductSupplier> GetProductsSuppliersByPackageId(int pkgId)
{
List <PackageProductSupplier> ppsList = new List <PackageProductSupplier>(); // empty
PackageProductSupplier pps;
// connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT pps.ProductSupplierID, " +
"ProdName + ', ' + SupName AS ProductSupplierName " +
"FROM Packages_Products_Suppliers AS pps " +
"JOIN Products_Suppliers AS ps " +
"ON pps.ProductSupplierId = ps.ProductSupplierId " +
"JOIN Products AS p " +
"ON ps.ProductId = p.ProductId " +
"JOIN Suppliers AS s " +
"ON ps.SupplierId = s.SupplierId " +
"WHERE PackageId = @PackageId " +
"ORDER BY ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@PackageId", pkgId);
// run command and process data
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read()) // while there is data
{
pps = new PackageProductSupplier();
pps.ProductSupplierId = (int)dr["ProductSupplierId"];
pps.ProductSupplierName = (string)dr["ProductSupplierName"];
ppsList.Add(pps);
}
}
}
}
return(ppsList);
}
/// <summary>
/// Returns a list of new product supplier ids that are not yet a part of the selected package
/// </summary>
/// <param name="pkgId"></param>
/// <returns>list of prod sup ids</returns>
public static List <int> GetNewProductSupplierIds(int pkgId)
{
List <int> prodSupIds = new List <int>(); // empty prod sup ids
int prodSupId;
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT DISTINCT ps.ProductSupplierId " +
"FROM Packages_Products_Suppliers AS pps " +
"RIGHT JOIN Products_Suppliers AS ps " +
"ON pps.ProductSupplierId = ps.ProductSupplierId " +
"WHERE ps.ProductSupplierId NOT IN " +
"(SELECT ps.ProductSupplierId " +
"FROM Products_Suppliers AS ps " +
"JOIN Packages_Products_Suppliers AS pps " +
"ON ps.ProductSupplierId = pps.ProductSupplierId " +
"AND PackageId = @PackageId) " +
"ORDER BY ps.ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@PackageId", (int)pkgId);
// run command and process data
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read()) // while there is data
{
prodSupId = Convert.ToInt32(dr["ProductSupplierId"]);
prodSupIds.Add(prodSupId);
}
}
}
}
return(prodSupIds);
}
/// <summary>
/// Katrina Spencer: Adds new product-supplier
/// </summary>
/// <param name="prodSup">new product-supplier info</param>
/// <returns>new product-supplier id</returns>
public static int AddProductSupplier(ProductSupplier prodSup)
{
int prodSupId = 0;
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " +
"OUTPUT INSERTED.ProductSupplierId " + // returns single value
"VALUES(@ProductId, @SupplierId)";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
if (prodSup.ProductId == null)
{
cmd.Parameters.AddWithValue("@ProductId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@ProductId", (int)prodSup.ProductId);
}
if (prodSup.SupplierId == null)
{
cmd.Parameters.AddWithValue("@SupplierId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@SupplierId", (int)prodSup.SupplierId);
}
// open connection
connection.Open();
prodSupId = (int)cmd.ExecuteScalar(); // returns one value
}
}
return(prodSupId);
}
/// <summary>
/// Katrina Spencer: Retrieves package info with given id
/// </summary>
/// <param name="pkgId">id of package to get</param>
/// <returns>package object</returns>
public static Package GetPackage(int pkgId)
{
Package pkg = null;
// connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId = @PackageId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@PackageId", pkgId);
// run command and process data
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
if (dr.Read()) // if data
{
pkg = new Package();
pkg.PackageId = (int)dr["PackageId"];
pkg.PkgName = (string)dr["PkgName"];
pkg.PkgBasePrice = Convert.ToDecimal(dr["PkgBasePrice"]);
// need to determine if it is DBNull and set
int colSD = dr.GetOrdinal("PkgStartDate"); // column number of PkgStartDate
if (dr.IsDBNull(colSD)) // if reader contains DBNull in this column
{
pkg.PkgStartDate = null; // make null in the object
}
else // if not null
{
pkg.PkgStartDate = (DateTime)dr["PkgStartDate"];
}
int colED = dr.GetOrdinal("PkgEndDate"); // column number of PkgEndDate
if (dr.IsDBNull(colED)) // if reader contains DBNull in this column
{
pkg.PkgEndDate = null; // make null in the object
}
else // if not null
{
pkg.PkgEndDate = (DateTime)dr["PkgEndDate"];
}
int colDesc = dr.GetOrdinal("PkgDesc"); // column number of PkgDesc
if (dr.IsDBNull(colDesc)) // if reader contains DBNull in this column
{
pkg.PkgDesc = null; // make null in the object
}
else // if not null
{
pkg.PkgDesc = (string)dr["PkgDesc"];
}
int colAC = dr.GetOrdinal("PkgAgencyCommission"); // column number of PkgAgencyCommission
if (dr.IsDBNull(colAC)) // if reader contains DBNull in this column
{
pkg.PkgAgencyCommission = null; // make null in the object
}
else // if not null
{
pkg.PkgAgencyCommission = Convert.ToDecimal(dr["PkgAgencyCommission"]);
}
}
}
}
}
return(pkg);
}
/// <summary>
/// Angelito: Updates package info
/// </summary>
/// <param name="oldPkg">old package info</param>
/// <param name="newPkg">new package info</param>
/// <returns>success indicator</returns>
public static bool UpdatePackage(Package oldPkg, Package newPkg)
{
// added consideration for Null values -Katrina
bool result = false; // no success yet
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string updateStatement = "UPDATE Packages SET " +
"PkgName = @NewPkgName, " +
"PkgStartDate = @NewPkgStartDate, " +
"PkgEndDate = @NewPkgEndDate, " +
"PkgDesc = @NewPkgDesc, " +
"PkgBasePrice = @NewPkgBasePrice, " +
"PkgAgencyCommission = @NewPkgAgencyCommission " +
"WHERE PackageId = @OldPackageId " + // identifies package
"AND PkgName = @OldPkgName " +
// PkgStartDate Null option -Katrina
"AND (PkgStartDate = @OldPkgStartDate " +
"OR PkgStartDate IS NULL " +
"AND @OldPkgStartDate IS NULL) " +
// PkgEndDate Null option -Katrina
"AND (PkgEndDate = @OldPkgEndDate " +
"OR PkgEndDate IS NULL " +
"AND @OldPkgEndDate IS NULL) " +
// PkgDesc Null option -Katrina
"AND (PkgDesc = @OldPkgDesc " +
"OR PkgDesc IS NULL " +
"AND @OldPkgDesc IS NULL) " +
"AND PkgBasePrice = @OldPkgBasePrice " +
// PkgAgencyCommission Null option -Katrina
"AND (PkgAgencyCommission = @OldPkgAgencyCommission " +
"OR PkgAgencyCommission IS NULL " +
"AND @OldPkgAgencyCommission IS NULL)";
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
// added consideration for Null values -Katrina
cmd.Parameters.AddWithValue("@NewPkgName", newPkg.PkgName);
if (newPkg.PkgStartDate == null) // if new PkgStartDate is null
{
cmd.Parameters.AddWithValue("@NewPkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgStartDate", (DateTime)newPkg.PkgStartDate);
}
if (newPkg.PkgEndDate == null) // if new PkgEndDate is null
{
cmd.Parameters.AddWithValue("@NewPkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgEndDate", (DateTime)newPkg.PkgEndDate);
}
// PkgDesc and PkgBasePrice can't be null when modifying
cmd.Parameters.AddWithValue("@NewPkgDesc", newPkg.PkgDesc);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPkg.PkgBasePrice);
if (newPkg.PkgAgencyCommission == null) // if new PkgAgencyCommission is null
{
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", (decimal)newPkg.PkgAgencyCommission);
}
cmd.Parameters.AddWithValue("@OldPackageId", oldPkg.PackageId);
cmd.Parameters.AddWithValue("@OldPkgName", oldPkg.PkgName);
if (oldPkg.PkgStartDate == null) // if old PkgStartDate is null
{
cmd.Parameters.AddWithValue("@OldPkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgStartDate", (DateTime)oldPkg.PkgStartDate);
}
if (oldPkg.PkgEndDate == null) // if old PkgEndDate is null
{
cmd.Parameters.AddWithValue("@OldPkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgEndDate", (DateTime)oldPkg.PkgEndDate);
}
if (oldPkg.PkgDesc == null) // if old PkgDesc is null
{
cmd.Parameters.AddWithValue("@OldPkgDesc", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgDesc", (string)oldPkg.PkgDesc);
}
cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPkg.PkgBasePrice);
if (oldPkg.PkgAgencyCommission == null) // if old PkgAgencyCommission is null
{
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", (decimal)oldPkg.PkgAgencyCommission);
}
// open connection
connection.Open();
// execute UPDATE command
int count = cmd.ExecuteNonQuery();
if (count > 0) // if row(s) affected
{
result = true;
}
}
}
return(result);
}
/// <summary>
/// Angelito: Adds a new package
/// </summary>
/// <param name="pkg">new package info</param>
/// <returns>new package id</returns>
public static int AddPackage(Package pkg)
{
int pkgId = 0;
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string insertStatement =
"INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"OUTPUT INSERTED.PackageId " +
"VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
// Added consideration for Null values -Katrina
cmd.Parameters.AddWithValue("@PkgName", pkg.PkgName);
Console.WriteLine("Package name in databse to be inserted " + pkg.PkgName);
if (pkg.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", (DateTime)pkg.PkgStartDate);
}
if (pkg.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", (DateTime)pkg.PkgEndDate);
}
if (pkg.PkgDesc == null)
{
cmd.Parameters.AddWithValue("@PkgDesc", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgDesc", (string)pkg.PkgDesc);
}
cmd.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice);
if (pkg.PkgAgencyCommission == null)
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", (decimal)pkg.PkgAgencyCommission);
}
// open connection
connection.Open();
pkgId = (int)cmd.ExecuteScalar(); // returns one value
}
}
return(pkgId);
}
/// <summary>
/// Katrina Spencer: Retrieves product-supplier info with given id
/// </summary>
/// <param name="prodSupId">id of product-supplier to get</param>
/// <returns>product-supplier object</returns>
public static ProductSupplier GetProductSupplier(int prodSupId)
{
ProductSupplier prodSup = null;
// connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT ProductSupplierId, ps.ProductId, ProdName, ps.SupplierId, SupName " +
"FROM Products_Suppliers AS ps " +
"JOIN Products ON ps.ProductId = Products.ProductId " +
"JOIN Suppliers ON ps.SupplierId = Suppliers.SupplierId " +
"WHERE ProductSupplierId = @ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@ProductSupplierId", prodSupId);
// run command and process data
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
if (dr.Read()) // if data
{
prodSup = new ProductSupplier();
prodSup.ProductSupplierId = (int)dr["ProductSupplierId"];
// determine if it is DBNull and set
int colProd = dr.GetOrdinal("ProductId"); // column number of ProductId
if (dr.IsDBNull(colProd)) // if reader contains DBNull in this column
{
prodSup.ProductId = null; // make null in the object
}
else // if not null
{
prodSup.ProductId = (int)(dr["ProductId"]);
}
int colSup = dr.GetOrdinal("SupplierId"); // column number of SupplierId
if (dr.IsDBNull(colSup)) // if reader contains DBNull in this column
{
prodSup.SupplierId = null; // make null in the object
}
else // if not null
{
prodSup.SupplierId = (int)(dr["SupplierId"]);
}
int colProdName = dr.GetOrdinal("ProdName"); // column number of ProdName
if (dr.IsDBNull(colProdName)) // if reader contains DBNull in this column
{
prodSup.ProdName = null; // make null in the object
}
else // if not null
{
prodSup.ProdName = (string)(dr["ProdName"]);
}
int colSupName = dr.GetOrdinal("SupName"); // column number of SupName
if (dr.IsDBNull(colSupName)) // if reader contains DBNull in this column
{
prodSup.SupName = null; // make null in the object
}
else // if not null
{
prodSup.SupName = (string)(dr["SupName"]);
}
}
}
}
}
return(prodSup);
}