private void reloadData()
{
packageList = PackageDB.GetAllPackages();
productsList = ProductDB.GetProducts();
suppliersList = SupplierDB.GetSuppliers();
prodSuppList = ProductsSupplierDB.GetAllProductsSuppliers();
pkgProdSuppList = PackagesProductsSuppliersDB.GetPackagesProductsSuppliers();
}
private void deletePkgProdSupp(PackagesProductsSuppliers packagesProductsSuppliers)
{
try {
if (!PackagesProductsSuppliersDB.DeletePackagesProductsSuppliers(packagesProductsSuppliers))
{
MessageBox.Show("Another user has updated or " +
"deleted that Package.", "Database Error");
// Refresh data
reloadData();
refreshComboBox();
}
else
{
pkgProdSuppList.Remove(packagesProductsSuppliers);
// Reload Combo box
refreshComboBox();
}
} catch (Exception ex) {
MessageBox.Show("Error: " + ex.Message, ex.GetType().ToString());
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string pkgName;
decimal basePrice;
int productId;
int supplierId;
if (Validator.IsPresent(pkgNameTextBox))
{
pkgName = pkgNameTextBox.Text;
// If Package Name already exists, get package object from database
bool ValidPrice = true;
bool DateValid = true;
if (Validator.IsPresent(pkgBasePriceTextBox))
{
basePrice = Convert.ToDecimal(pkgBasePriceTextBox.Text);
SelectedPackage.PkgBasePrice = basePrice;
SelectedPackage.PkgName = pkgName;
if (string.IsNullOrWhiteSpace(pkgAgencyCommissionTextBox.Text))
{
SelectedPackage.PkgAgencyCommission = null;
}
else
{
SelectedPackage.PkgAgencyCommission = Convert.ToDecimal(pkgAgencyCommissionTextBox.Text);
}
if (string.IsNullOrWhiteSpace(pkgDescTextBox.Text))
{
SelectedPackage.PkgDesc = null;
}
else
{
SelectedPackage.PkgDesc = pkgDescTextBox.Text;
}
if (pkgStartDateDateTimePicker.Checked == false &&
pkgEndDateDateTimePicker.Checked == false)
{
SelectedPackage.PkgStartDate = null;
SelectedPackage.PkgEndDate = null;
}
else if (pkgStartDateDateTimePicker.Checked == true &&
pkgEndDateDateTimePicker.Checked == false)
{
// Package End Date has not been provided yet
SelectedPackage.PkgStartDate = pkgStartDateDateTimePicker.Value.Date;
SelectedPackage.PkgEndDate = null;
}
else if (pkgStartDateDateTimePicker.Checked == false &&
pkgEndDateDateTimePicker.Checked == true)
{
MessageBox.Show("Must Provide a Valid Start Date");
DateValid = false;
DialogResult = DialogResult.None;
}
else
{
if (DateTime.Compare(pkgStartDateDateTimePicker.Value, pkgEndDateDateTimePicker.Value) >= 0)
{
MessageBox.Show("Start Date must be earlier than End Date");
DateValid = false;
DialogResult = DialogResult.None;
}
else
{
SelectedPackage.PkgStartDate = pkgStartDateDateTimePicker.Value.Date;
SelectedPackage.PkgEndDate = pkgEndDateDateTimePicker.Value.Date;
}
}
if (DateValid)
{
// Update package in database
try {
if (!PackageDB.UpdatePackage(OldPackage, SelectedPackage))
{
MessageBox.Show("Another user has updated or " +
"deleted that Package.", "Database Error");
DialogResult = DialogResult.Retry;
PkgUpdated = false;
}
else
{
PkgUpdated = true;
}
} catch (Exception ex) {
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
else
{
ValidPrice = false;
DialogResult = DialogResult.None;
}
if (ValidPrice && DateValid && PkgUpdated)
{
productId = (int)prodNameComboBox.SelectedValue;
supplierId = (int)supNameComboBox.SelectedValue;
// Check if the combination of productId and supplierId already exists in database
if (ProductsSupplierDB.GetProductsSupplierByProductIdAndSupplierId(productId, supplierId) == null)
{
// if doesn't exist in database, insert a new record
ProductsSupplier prodSupps = new ProductsSupplier {
ProductId = productId,
SupplierId = supplierId
};
// Insert into database
int prodSuppId = ProductsSupplierDB.AddProductsSupplier(prodSupps);
prodSupps.ProductSupplierId = prodSuppId;
SelectedProductsSupplier = prodSupps;
ProdSuppInserted = true;
}
else
{
SelectedProductsSupplier =
ProductsSupplierDB.GetProductsSupplierByProductIdAndSupplierId(productId, supplierId);
ProdSuppInserted = false;
}
// Update Packages_Products_Supplier table
try {
// If there already exists a same packageId and ProductSupplierId combination
var pkgProdSuppTable = PkgProdSupps.SingleOrDefault(p => p.PackageId == SelectedPackage.PackageId &&
p.ProductSupplierId == SelectedProductsSupplier.ProductSupplierId);
if (pkgProdSuppTable != null)
{
MessageBox.Show("Package: " + SelectedPackage.PkgName + " with \n" +
"Product Name: " + prodNameComboBox.Text + "\nSupplier Name: " +
supNameComboBox.Text + " \nalready exists", "Record Exists");
DialogResult = DialogResult.None;
}
else
{
// Verify against database
PackagesProductsSuppliers pkgPS = PackagesProductsSuppliersDB.GetPackagesProductsSuppliersByPkgIdAndProductSupplierId(SelectedPackage.PackageId, SelectedProductsSupplier.ProductSupplierId);
if (pkgPS == null)
{
SelectedPkgProdSupp.ProductSupplierId = SelectedProductsSupplier.ProductSupplierId;
if (!PackagesProductsSuppliersDB.UpdatePackagesProductsSuppliers(OldPkgProdSupp, SelectedPkgProdSupp))
{
MessageBox.Show("Another user has updated or " +
"deleted that Package.", "Database Error");
DialogResult = DialogResult.Retry;
}
else
{
DialogResult = DialogResult.OK;
}
}
else
{
MessageBox.Show("Package: " + SelectedPackage.PkgName + " with \n" +
"Product Name: " + prodNameComboBox.Text + "\nSupplier Name: " +
supNameComboBox.Text + " \nalready exists", "Record Exists");
SelectedPkgProdSupp = pkgPS;
DialogResult = DialogResult.OK;
}
}
} catch (Exception ex) {
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
else
{
DialogResult = DialogResult.None;
}
}
else
{
DialogResult = DialogResult.None;
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
string pkgName;
decimal basePrice;
int productId;
int supplierId;
if (Validator.IsPresent(pkgNameTextBox))
{
pkgName = pkgNameTextBox.Text;
// If Package Name already exists, get package object from database
bool ValidPrice = true;
bool DateValid = true;
var pkg = packages.SingleOrDefault(p => p.PkgName.ToLower() == pkgName.ToLower());
if (pkg != null)
{
Package = pkg;
PkgInserted = false;
}
else
{
// Verify against database
Package pkgExist = PackageDB.GetPackageByName(pkgName);
if (pkgExist == null)
{
Package newPkg = new Package();
if (Validator.IsPresent(pkgBasePriceTextBox))
{
basePrice = Convert.ToDecimal(pkgBasePriceTextBox.Text);
newPkg.PkgBasePrice = basePrice;
newPkg.PkgName = pkgName;
if (string.IsNullOrWhiteSpace(pkgAgencyCommissionTextBox.Text))
{
newPkg.PkgAgencyCommission = null;
}
else
{
newPkg.PkgAgencyCommission = Convert.ToDecimal(pkgAgencyCommissionTextBox.Text);
}
if (string.IsNullOrWhiteSpace(pkgDescTextBox.Text))
{
newPkg.PkgDesc = null;
}
else
{
newPkg.PkgDesc = pkgDescTextBox.Text;
}
if (pkgStartDateDateTimePicker.Checked == false &&
pkgEndDateDateTimePicker.Checked == false)
{
newPkg.PkgStartDate = null;
newPkg.PkgEndDate = null;
}
else if (pkgStartDateDateTimePicker.Checked == true &&
pkgEndDateDateTimePicker.Checked == false)
{
// Package End Date has not been provided yet
newPkg.PkgStartDate = pkgStartDateDateTimePicker.Value.Date;
newPkg.PkgEndDate = null;
}
else if (pkgStartDateDateTimePicker.Checked == false &&
pkgEndDateDateTimePicker.Checked == true)
{
MessageBox.Show("Must Provide a Valid Start Date");
DateValid = false;
DialogResult = DialogResult.None;
}
else
{
if (DateTime.Compare(pkgStartDateDateTimePicker.Value, pkgEndDateDateTimePicker.Value) >= 0)
{
MessageBox.Show("Start Date must be earlier than End Date");
DateValid = false;
DialogResult = DialogResult.None;
}
else
{
newPkg.PkgStartDate = pkgStartDateDateTimePicker.Value.Date;
newPkg.PkgEndDate = pkgEndDateDateTimePicker.Value.Date;
}
}
if (DateValid)
{
// Add package to database
int pkgId = PackageDB.AddPackage(newPkg);
newPkg.PackageId = pkgId;
Package = newPkg;
PkgInserted = true;
}
}
else
{
ValidPrice = false;
DialogResult = DialogResult.None;
}
}
else // Package with pkgName exists in database
{
Package = pkgExist;
PkgInserted = true;
}
}
if (ValidPrice && DateValid)
{
productId = (int)prodNameComboBox.SelectedValue;
supplierId = (int)supNameComboBox.SelectedValue;
// Check if the combination of productId and supplierId already exists in database
if (ProductsSupplierDB.GetProductsSupplierByProductIdAndSupplierId(productId, supplierId) == null)
{
// if doesn't exist in database, insert a new record
ProductsSupplier prodSupps = new ProductsSupplier {
ProductId = productId,
SupplierId = supplierId
};
// Insert into database
int prodSuppId = ProductsSupplierDB.AddProductsSupplier(prodSupps);
prodSupps.ProductSupplierId = prodSuppId;
productsSupplier = prodSupps;
ProdSuppInserted = true;
}
else
{
productsSupplier =
ProductsSupplierDB.GetProductsSupplierByProductIdAndSupplierId(productId, supplierId);
ProdSuppInserted = false;
}
// If there already exists a same packageId and ProductSupplierId combination
var pkgProdSuppTable = pkgProdSupps.SingleOrDefault(p => p.PackageId == Package.PackageId &&
p.ProductSupplierId == productsSupplier.ProductSupplierId);
if (pkgProdSuppTable != null)
{
MessageBox.Show("Package: " + Package.PkgName + " with \n" +
"Product Name: " + prodNameComboBox.Text + "\nSupplier Name: " +
supNameComboBox.Text + " \nalready exists", "Record Exists");
DialogResult = DialogResult.None;
}
else
{
// Verify against database
PackagesProductsSuppliers pkgPS = PackagesProductsSuppliersDB.GetPackagesProductsSuppliersByPkgIdAndProductSupplierId(Package.PackageId, productsSupplier.ProductSupplierId);
if (pkgPS == null)
{
PackagesProductsSuppliers pps = new PackagesProductsSuppliers();
pps.PackageId = Package.PackageId;
pps.ProductSupplierId = productsSupplier.ProductSupplierId;
// Insert into database
PackagesProductsSuppliersDB.AddPackagesProductsSuppliers(pps);
pkgProdSupp = pps;
DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("Package: " + Package.PkgName + " with \n" +
"Product Name: " + prodNameComboBox.Text + "\nSupplier Name: " +
supNameComboBox.Text + " \nalready exists", "Record Exists");
pkgProdSupp = pkgPS;
DialogResult = DialogResult.OK;
}
}
}
else
{
DialogResult = DialogResult.None;
}
}
else
{
DialogResult = DialogResult.None;
}
}
