public Product_Supplier selectedProductSupplier { get; set; } //the product suppliers selected from the list of active package product suppliers
//form constructor, sets active package, fills the product suppliers
public frmEditProductSuppliers(Package package)
{
InitializeComponent();
activePackage = package;
Text = activePackage.PkgName;
activePackage.fillSuppliers();
}
//The form constructor for edit mode
public frmAddEdit(string message, Package package)
{
InitializeComponent();
this.Text = message;
this.message = message;
this.activePackage = package;
PkgImg = package.PkgImg;
displayPackage();
btnConfirm.Text = "Confirm";
}
public static List<Package> GetAllPackages()
{
// SELECT * FROM Packages (Table)
// Get the List of all Packages from the Packages Table
// returns List<Package>
// throws SqlException and Exception
// checked jan 13 DS
List<Package> ListPkg = new List<Package>();
string selectAll = "SELECT * FROM Packages";
SqlConnection connection = MMATravelExperts.GetConnection();
SqlCommand selectAllCmd = new SqlCommand(selectAll, connection);
try
{
connection.Open();
SqlDataReader reader = selectAllCmd.ExecuteReader();
while(reader.Read())
{
Package pkg = new Package();
pkg.PackageId = (int)reader["PackageId"];
pkg.PkgName = reader["PkgName"].ToString();
pkg.PkgStartDate = (DateTime)reader["PkgStartDate"];
pkg.PkgEndDate = (DateTime)reader["PkgEndDate"];
pkg.PkgDesc = reader["PkgDesc"].ToString();
pkg.PkgBasePrice = (decimal)reader["PkgBasePrice"];
pkg.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
if (!reader.IsDBNull(7))
pkg.PkgImg = (byte[])reader["PkgImg"];
ListPkg.Add(pkg);
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
return ListPkg;
}
/// <summary>
/// Gets the package object from the package table
/// </summary>
/// <param name="PackageId"></param>
/// <returns>package</returns>
public static Package GetPackage(int PackageID)
// Get the Package for a given PackageID from table Packages
// PackageId is an int and the PK for Table Package
// returns type Package containing 1 of package object, !!!! null if no package (only 4 packages) !!!!
// throws SqlException and Exception
// checked checked jan 13 DS
{
SqlConnection connection = MMATravelExperts.GetConnection();
string selectStatement = "SELECT * FROM Packages WHERE PackageId=@PackageId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PackageId", PackageID);
try
{
connection.Open();
SqlDataReader pkgReader= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if(pkgReader.Read())
{
Package pkg = new Package();
pkg.PackageId = (int)pkgReader["PackageId"];
pkg.PkgName = pkgReader["PkgName"].ToString();
pkg.PkgStartDate= (DateTime)pkgReader["PkgStartDate"];
pkg.PkgEndDate=(DateTime)pkgReader["PkgEndDate"];
pkg.PkgDesc=pkgReader["PkgDesc"].ToString();
pkg.PkgBasePrice=(Decimal)pkgReader["PkgBasePrice"];
pkg.PkgAgencyCommission=(Decimal)pkgReader["PkgAgencyCommission"];
return pkg;
}
else
{
return null; // no package returns null
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
//<summary>
//Updates a package in the Packages Table
//</summary>
//<param name="oldPkg">old package</param>
//<param name="newPkg">new package</param>
//<returns></returns>
public static bool UpdatePackage(Package oldPkg, Package newPkg)
{
// Updates the Packages Table
// parameter oldPkg ... the old row as an instance of Package class
// parameter newPkg ... the new row as an instance of Package class
// returns true row updated, false row not updated
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
string updateStatement="UPDATE Packages SET PkgName=@newPkgName, "+
"PkgStartDate=@newPkgStartDate, PkgEndDate=@newPkgEndDate, "+
"PkgDesc=@newPkgDesc, PkgBasePrice=@newPkgBasePrice, PkgAgencyCommission=@newPkgAgencyCommission, PkgImg=@newPkgImg " +
"WHERE PackageId=@oldPackageId and PkgName=@oldPkgName and PkgStartDate=@oldPkgStartDate and "+
"PkgEndDate=@oldPkgEndDate and PkgDesc=@oldPkgDesc and PkgBasePrice=@oldPkgBasePrice and "+
"PkgAgencyCommission=@oldPkgAgencyCommission";
SqlCommand updateCommand = new SqlCommand(updateStatement,connection);
// new package listing
updateCommand.Parameters.AddWithValue("@newPackageId",newPkg.PackageId);
updateCommand.Parameters.AddWithValue("@newPkgName",newPkg.PkgName);
updateCommand.Parameters.AddWithValue("@newPkgStartDate",newPkg.PkgStartDate);
updateCommand.Parameters.AddWithValue("@newPkgEndDate",newPkg.PkgEndDate);
updateCommand.Parameters.AddWithValue("@newPkgDesc",newPkg.PkgDesc);
updateCommand.Parameters.AddWithValue("@newPkgBasePrice",newPkg.PkgBasePrice);
updateCommand.Parameters.AddWithValue("@newPkgAgencyCommission",newPkg.PkgAgencyCommission);
if (newPkg.PkgImg == null)
updateCommand.Parameters.AddWithValue("@newPkgImg", SqlDbType.VarBinary);
else
updateCommand.Parameters.AddWithValue("@newPkgImg", newPkg.PkgImg);
// old package listing
updateCommand.Parameters.AddWithValue("@oldPackageId",oldPkg.PackageId);
updateCommand.Parameters.AddWithValue("@oldPkgName",oldPkg.PkgName);
updateCommand.Parameters.AddWithValue("@oldPkgStartDate",oldPkg.PkgStartDate);
updateCommand.Parameters.AddWithValue("@oldPkgEndDate",oldPkg.PkgEndDate);
updateCommand.Parameters.AddWithValue("@oldPkgDesc",oldPkg.PkgDesc);
updateCommand.Parameters.AddWithValue("@oldPkgBasePrice",oldPkg.PkgBasePrice);
updateCommand.Parameters.AddWithValue("@oldPkgAgencyCommission",oldPkg.PkgAgencyCommission);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count>0)
{
return true; // rows updated
}
else
{
return false; //rows not updated
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
connection.Close();
}
}
//<summary>
//Add a package object to the Package Table
//</summary>
//<param name="pkg">package</param>
//<returns>PackageId (PK)</returns>
public static int AddPackage(Package pkg)
{
// Add a Package to the Packages Table
// package is the instance of Package class
// returns the PackageId of the row inserted or -1 if not added to table
// throws SqlException and Exception
SqlConnection connection = MMATravelExperts.GetConnection();
String insertStatement = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, PkgImg) " +
"VALUES (@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission, @PkgImg)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@PkgName", pkg.PkgName);
insertCommand.Parameters.AddWithValue("@PkgStartDate", pkg.PkgStartDate);
insertCommand.Parameters.AddWithValue("@PkgEndDate", pkg.PkgEndDate);
insertCommand.Parameters.AddWithValue("@PkgDesc", pkg.PkgDesc);
insertCommand.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice);
insertCommand.Parameters.AddWithValue("@PkgAgencyCommission", pkg.PkgAgencyCommission);
if (pkg.PkgImg == null)
insertCommand.Parameters.AddWithValue("@PkgImg", SqlDbType.VarBinary);
else
insertCommand.Parameters.AddWithValue("@PkgImg", pkg.PkgImg);
try
{
connection.Open();
int numRows = insertCommand.ExecuteNonQuery();
if (numRows > 0)
{
int prodIDcheck=0;
string selectStatement = "SELECT PackageId FROM Packages where PkgName=@PkgName and PkgStartDate=@PkgStartDate and PkgEndDate=@PkgEndDate "+
"and PkgDesc=@PkgDesc and PkgBasePrice=@PkgBasePrice and PkgAgencyCommission=@PkgAgencyCommission";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PkgName", pkg.PkgName);
selectCommand.Parameters.AddWithValue("@PkgStartDate", pkg.PkgStartDate);
selectCommand.Parameters.AddWithValue("@PkgEndDate", pkg.PkgEndDate);
selectCommand.Parameters.AddWithValue("@PkgDesc", pkg.PkgDesc);
selectCommand.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice);
selectCommand.Parameters.AddWithValue("@PkgAgencyCommission", pkg.PkgAgencyCommission);
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
prodIDcheck = Convert.ToInt32(reader["PackageId"]);
}
return prodIDcheck;
}
else
{
return -1;
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Adds the input product supplier to the input package with a new row inserted into the packages_products_suppliers table
/// </summary>
/// <param name="ps">the product-supplier to add</param>
/// <param name="pack">the package to add to</param>
/// <returns>true if successful, false if fail</returns>
public static bool addProductSupplierToPackage(Product_Supplier ps, Package pack)
{
SqlConnection connection = MMATravelExperts.GetConnection();
string insertStatement = "Insert into Packages_Products_Suppliers values (@packageId,@productSupplierId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@PackageId", pack.PackageId);
insertCommand.Parameters.AddWithValue("@productSupplierId", ps.ProductSupplierId);
try
{
connection.Open();
int result = insertCommand.ExecuteNonQuery();
if (result == 1)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//set the active package based on the combo box selection
private void cbPackages_SelectedIndexChanged(object sender, EventArgs e)
{
ClearControls();
activePackage = (Package)cbPackages.SelectedItem;
activePackage.fillSuppliers();
DisplayPackage();
}
private void createPackage()
{
//handle the ToString currency format
string newBasePrice = txtPrice.Text.Replace(",", "").Replace("$", "");
string newCommission = txtCommission.Text.Replace(",", "").Replace("$", "");
//create a new package from the form controls
newPackage = new Package();
newPackage.PkgName = txtName.Text;
newPackage.PkgStartDate = dtpStart.Value;
newPackage.PkgEndDate = dtpEnd.Value;
newPackage.PkgDesc = txtDescription.Text;
newPackage.PkgBasePrice = Convert.ToDecimal(newBasePrice);
if (txtCommission.Text == "")
newPackage.PkgAgencyCommission = 0m;
else
newPackage.PkgAgencyCommission = Convert.ToDecimal(newCommission);
if (PkgImg != null)
newPackage.PkgImg = PkgImg;
}
