//initialize form area
private void Form1_Load(object sender, EventArgs e)//initialize form
{
//initiallize recorders from Prodcuts,Suppliers and Product_supplier three TABLES
Products = ProductDB.GetAllProducts(); // LIST of Prodcut : get all prodcuts from product TABLE to Prodcut List
Suppliers = SupplierDB.GetAllsuppliers(); //List of Supplier:get all suppliers from supplier TABLE
allPS = ProductSupplierDB.GetAllProductsSuppliers(); //List of PS: get all of products and corresponding suppliers from PRODUCTS_SUPPLIERS TABLE
var allProductName = from pro in Products //get each row in List of Prodcuts
orderby pro.ProdName
select new { ProductId = pro.ProductID, ProductName = pro.ProdName }; //
//initialzie datasour for lstProducts to display each product come from products TABLE
lstProducts.SelectedIndexChanged -= lstProducts_SelectedIndexChanged; //
lstProducts.DataSource = allProductName.ToList(); //display all of product
lstProducts.ValueMember = "ProductId"; //set Product ID as value
lstProducts.DisplayMember = "ProductName"; //set product name as display
lstProducts.SelectedIndex = -1;
lstProducts.SelectedIndexChanged += lstProducts_SelectedIndexChanged;
//initialize button status,before select product row or (PS row ),none of buttons are available!
btnAdd.Enabled = false;
btnUpdate.Enabled = false;
btnDelete.Enabled = false;
btnSave.Enabled = false;
panel2.Visible = false;
lblSave.Visible = false;
}
public static List <ProductSupplier> getCurrentPS(int produtid, int supplierid, string OperationSign) //pass a specific productsupplier object into loadprosupplier function and return all rows with same productID
{
currentProdId = produtid; //get current selected product's productId in listbox of product(topleft)
currentSupId = supplierid;
return(ProductSupplierDB.GetOneSuppliersForSelectedProduct(currentProdId, currentSupId));//when go to take update or delete operation,only foucuseon on current row
}
private void refreshLstProducts()//refresh lstSupplier which lists all related suppliers for one specific product
{
// Suppliers = SupplierDB.GetAllsuppliers();
currentProdId = Convert.ToInt32(lstProducts.SelectedValue); //currentProdid is used for ADD button
List <ProductSupplier> listps = ProductSupplierDB.GetSuppliersForSelectedProduct(currentProdId); //get all have product-supplier related rows from productsuppliers Table where productId
var relatedSupplierName = from allsupplier in Suppliers //get each row in sUppliers TABle
where (from c in listps select c.SupplierID).Contains(allsupplier.SupplierId) // current row from currentPS doesnt included same supplierid to allsupplier
orderby allsupplier.SupName
select new { Supplierid = allsupplier.SupplierId, SupplierName = allsupplier.SupName };
//initialize lstSuppliers control
lstSuppliers.SelectedIndexChanged -= lstSuppliers_SelectedIndexChanged;
lstSuppliers.DataSource = relatedSupplierName.ToList();//set LIQN result to supplier listbox
lstSuppliers.ValueMember = "Supplierid";
lstSuppliers.DisplayMember = "SupplierName";
lstSuppliers.SelectedIndex = -1;
lstSuppliers.SelectedIndexChanged += lstSuppliers_SelectedIndexChanged;
//initialize ADD Update Delete Buttons
btnAdd.Enabled = true;
if (lstSuppliers.Text == "")//When not selecting specific supplier ,only do ADD
{
btnDelete.Enabled = false;
btnUpdate.Enabled = false;
}
}
private void btnDelete_Click(object sender, EventArgs e)//button for delete
{
OperationSign = "Delete";
cklstProductSupplier.Visible = false;
btnDelete.Enabled = false;
currentDeleteProductId = Convert.ToInt32(lstProducts.SelectedValue); //get delete target productID
currentDeleteSupplierId = Convert.ToInt32(lstSuppliers.SelectedValue); //get delete target supplierID
currentDeleteProductName = lstProducts.Text;
currentDeleteSupplierName = lstSuppliers.Text;
currentDeleteProductSupplierId = ProductSupplierDB.getProductSupplierId(currentDeleteProductId, currentDeleteSupplierId); //get productsupplierId in PS TABLE
if (ProductSupplierDB.referencedInPackages(currentDeleteProductSupplierId)) //chech delete target is referenced in Package table or no
{
MessageBox.Show("The product:" + currentDeleteProductName + " with supplier of " + currentDeleteSupplierName + " has been referenced in Package Table,Please go to Package!");
panel2.Visible = false;
panel1.Visible = true;
btnSave.Enabled = false;
lblSave.Enabled = false;
lblSave.Visible = false;
}
else//selected row is not referenced in PAckage Table
{
panel2.Visible = true;
panel1.Visible = false;
btnSave.Enabled = true;
lblSave.Enabled = true;
lblSave.Visible = true;
currentPS = OperationPS.getCurrentPS(currentDeleteProductId, currentDeleteSupplierId, OperationSign);
lblSave.Text = "You are preparing to delete product:" + currentDeleteProductName + " with supplier:" + currentDeleteSupplierName;
Display();
}
}
private void button1_Click(object sender, EventArgs e)
{
supProduct.Form2 form2 = new supProduct.Form2();
DialogResult result = form2.ShowDialog();
if (result == DialogResult.OK)
{
Products = ProductDB.GetAllProducts(); // LIST of Prodcut : get all prodcuts from product TABLE to Prodcut List
// Suppliers = SupplierDB.GetAllsuppliers();//List of Supplier:get all suppliers from supplier TABLE
allPS = ProductSupplierDB.GetAllProductsSuppliers(); //List of PS: get all of products and corresponding suppliers from PRODUCTS_SUPPLIERS TABLE
var allProductName = from pro in Products //get each row in List of Prodcuts
orderby pro.ProdName
select new { ProductId = pro.ProductID, ProductName = pro.ProdName }; //
//initialzie datasour for lstProducts to display each product come from products TABLE
lstProducts.SelectedIndexChanged -= lstProducts_SelectedIndexChanged; //
lstProducts.DataSource = allProductName.ToList();
lstProducts.ValueMember = "ProductId";
lstProducts.DisplayMember = "ProductName";
lstProducts.SelectedIndex = -1;
lstProducts.SelectedIndexChanged += lstProducts_SelectedIndexChanged;
}
}
private void txtSupplierSearch_KeyUp(object sender, KeyEventArgs e)
{
currentProdId = Convert.ToInt32(lstProducts.SelectedValue);
List <ProductSupplier> listps = ProductSupplierDB.SearchAllSuppliersForSelectedProduct(currentProdId, txtSupplierSearch.Text);
var relatedSupplierName = from relatedSupplier in listps
select new { Supplierid = relatedSupplier.SupplierID, SupplierName = relatedSupplier.SupplierName };
lstSuppliers.SelectedIndexChanged -= lstSuppliers_SelectedIndexChanged;
lstSuppliers.DataSource = relatedSupplierName.ToList();
lstSuppliers.ValueMember = "Supplierid";
lstSuppliers.DisplayMember = "SupplierName";
lstSuppliers.SelectedIndex = -1;
lstSuppliers.SelectedIndexChanged += lstSuppliers_SelectedIndexChanged;
}
private void txtSupplierSearch_KeyUp(object sender, KeyEventArgs e)
{
currentProdId = Convert.ToInt32(lstProducts.SelectedValue);
//Suppliers = SupplierDB.GetAllsuppliers();
List <ProductSupplier> listps = ProductSupplierDB.SearchAllSuppliersForSelectedProduct(currentProdId, txtSupplierSearch.Text);
// var relatedSupplierName = from allsupplier in Suppliers//get each row in sUppliers TABle
// where (from c in listps select c.SupplierID).Contains(allsupplier.SupplierId)// current row from currentPS doesnt included same
// select new { Supplierid = allsupplier.SupplierId, SupplierName = allsupplier.SupName };
var relatedSupplierName = from relatedSupplier in listps
select new { Supplierid = relatedSupplier.SupplierID, SupplierName = relatedSupplier.SupplierName };
lstSuppliers.SelectedIndexChanged -= lstSuppliers_SelectedIndexChanged;
lstSuppliers.DataSource = relatedSupplierName.ToList();
lstSuppliers.ValueMember = "Supplierid";
lstSuppliers.DisplayMember = "SupplierName";
lstSuppliers.SelectedIndex = -1;
lstSuppliers.SelectedIndexChanged += lstSuppliers_SelectedIndexChanged;
}
private void UpdateAvailableSupplier()//method to update new available supplier to current supplier for a specific row
{
//when update,only select one available supplier to update old one,available suppliers is same to add function
currentPS = ProductSupplierDB.GetAllSuppliersForSelectedProduct(currentUpdateProductId); //select all of suppliers for this current ProductID
List <Supplier> Suppliers = SupplierDB.GetAllsuppliers(); //access DB by select statement and return rows
var availableSupplierName = from allsupplier in Suppliers
where !(from c in currentPS select c.SupplierID).Contains(allsupplier.SupplierId) //select sullpiers which has no any relationship with current product
orderby allsupplier.SupName
select new { Supplierid = allsupplier.SupplierId, SupplierName = allsupplier.SupName };
cklstProductSupplier.DataSource = availableSupplierName.ToList();
cklstProductSupplier.DisplayMember = "SupplierName";
cklstProductSupplier.ValueMember = "Supplierid";
for (int i = 0; i < cklstProductSupplier.Items.Count; i++)
{
if (cklstProductSupplier.GetItemChecked(i))
{
cklstProductSupplier.SetItemChecked(i, false);
}
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
//int currentDeletePSid;
OperationSign = "Delete";
//
cklstProductSupplier.Visible = false;
//
btnDelete.Enabled = false;
//
currentDeleteProductId = Convert.ToInt32(lstProducts.SelectedValue);
currentDeleteSupplierId = Convert.ToInt32(lstSuppliers.SelectedValue);
currentDeleteProductName = lstProducts.Text;
currentDeleteSupplierName = lstSuppliers.Text;
currentDeleteProductSupplierId = ProductSupplierDB.getProductSupplierId(currentDeleteProductId, currentDeleteSupplierId);//get productsupplierId in PS TABLE
if (ProductSupplierDB.referencedInPackages(currentDeleteProductSupplierId))
{
MessageBox.Show("Your selection product:" + currentDeleteProductName + " with supplier of " + currentDeleteSupplierName + " has been referenced in Package Table,Please go to Package!");
panel2.Visible = false;
panel1.Visible = true;
btnSave.Enabled = false;
lblSave.Enabled = false;
lblSave.Visible = false;
}
else
{
panel2.Visible = true;
panel1.Visible = false;
btnSave.Enabled = true;
lblSave.Enabled = true;
lblSave.Visible = true;
currentPS = OperationPS.getCurrentPS(currentDeleteProductId, currentDeleteSupplierId, OperationSign);
lblSave.Text = "You are preparing to delete product:" + currentDeleteProductName + " with supplier:" + currentDeleteSupplierName;
Display();
}
}
// public static List<ProductSupplier> currentPS = new List<ProductSupplier>();
public static List <ProductSupplier> getCurrentPS(int produtid, string OperationSign) //pass a specific productsupplier object into loadprosupplier function and return all rows with same productID
{
currentProdId = produtid; //get current selected product's productId in listbox of product(topleft)
return(ProductSupplierDB.GetAllSuppliersForSelectedProduct(currentProdId)); //get all rows where their productID is equal to current selected row's productID
}
//button function area
private void btnSave_Click(object sender, EventArgs e) //save action result
{
int count = this.cklstProductSupplier.Items.Count; //how many items is loaded in checkedlistbox
List <int> availSupplierID = new List <int>(); //declare current productid which is available
int j = 0;
for (int i = 0; i < count; i++)
{
if (this.cklstProductSupplier.GetItemChecked(i)) //tell whether item is selected of not
{
this.cklstProductSupplier.SetSelected(i, true); //if selected then set its selected value is ture
//MessageBox.Show(this.cklstProductSupplier.Text.ToString()); //获取文本
// MessageBox.Show(this.cklstProductSupplier.SelectedValue.ToString()); //获取Value值
availSupplierID.Add(Convert.ToInt32(cklstProductSupplier.SelectedValue)); //store all of selected item's value to availsupplierID list
j++;
}
}
if (OperationSign == "Add") //if custoemr click Add Button
{
if (j > 0) // you have selected at leat one item from checkedlistbox
{
int addsuccess = 1;
foreach (int p in availSupplierID)//get all of supplierd whic will be added to product
{
try
{
if (!ProductSupplierDB.AddProductsSuppliers(currentAddProductId, p))//currentAddProdID to locate a specific product and p is the supplier's id number
{
addsuccess = 0;
}
else
{
}
}
catch (Exception ex)
{
throw ex;
}
}
if (addsuccess == 1)
{
MessageBox.Show("You have added new suppliers succesfullly!");
lblSave.Text = "You have added " + j.ToString() + " new suppliers to product:" + currentAddProductName;
refreshLstProducts(); //refresh products list to display latest info
}
currentPS = ProductSupplierDB.GetAllSuppliersForSelectedProduct(currentAddProductId); //after Add operation,current selected product have more suppliers so reload currenPS list
dgvCurrentProSupplier.DataSource = currentPS; //refresh bottom grid
dgvCurrentProSupplier.Columns[1].Visible = false;
dgvCurrentProSupplier.Columns[2].Visible = false;
AddAvailableSupplier();//refrehs checkedlistbox and before do it ,must reassign values in currentPS list
}
else
{
MessageBox.Show("please Select at least one Prodcut as new item!");
}
btnSave.Enabled = false;
}
if (OperationSign == "Update") //when customer select Update operation
{
if (j > 0) //if select a target supplier to replace current supplier
{
DialogResult result = MessageBox.Show("Update product:" + currentUpdateProductName + ",replace " + currentUpdatesupplierName + " with " + cklstProductSupplier.Text + " ? ",
"Confirm Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
try
{
if (!ProductSupplierDB.UpdateProductSupplier(availSupplierID[0], currentUpdatesupplierId, currentUpdateProductId))//update operation failed
{
MessageBox.Show("Another user has updated or deleted " +
"that customer.", "Database Error");
}
else//update operation successed!
{
MessageBox.Show("You have updated old supplier:" + currentUpdatesupplierName + " with new supplier " + cklstProductSupplier.Text + "successfully!");
lblSave.Text = "You have updated supplier:" + currentUpdatesupplierName + " with new suppliers :" + cklstProductSupplier.Text;
currentPS = ProductSupplierDB.GetAllSuppliersForSelectedProduct(currentUpdateProductId);//in david idea,delete excuted successfully
dgvCurrentProSupplier.DataSource = currentPS;
refreshLstProducts();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
currentPS = ProductSupplierDB.GetAllSuppliersForSelectedProduct(currentUpdateProductId);
// MessageBox.Show(currentPS.Count().ToString());
dgvCurrentProSupplier.DataSource = currentPS;//
dgvCurrentProSupplier.Columns[1].Visible = false;
dgvCurrentProSupplier.Columns[2].Visible = false;
AddAvailableSupplier();
currentUpdateProductId = -1; //update saving has finished
btnSave.Enabled = false; //after update operation save is locked
}
}
else//if dont select any target supplier
{
MessageBox.Show("please Select one Prodcut !");
}
}
if (OperationSign == "Delete")//when click"delete"button
{
DialogResult result = MessageBox.Show("Delete product: " + currentDeleteProductName + " with supplier :" + currentDeleteSupplierName + " ? ",
"Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
try
{
if (!ProductSupplierDB.DeleteProductSupplier(currentDeleteProductSupplierId)) //delete failure
{
MessageBox.Show("Please contact DataBase Administration ", "Database Error");
}
else //delete succussful
//currentProdId = currentPS[0].ProductID;
{
MessageBox.Show("Product: " + currentDeleteProductName + " with supplier :" + currentDeleteSupplierName + "has been deleted successfully!");
currentPS = ProductSupplierDB.GetAllSuppliersForSelectedProduct(currentDeleteProductId); //in david idea,delete excuted successfully
dgvCurrentProSupplier.DataSource = currentPS;
refreshLstProducts();
lblSave.Text = "You have deleted product:" + currentDeleteProductName + " with suplier:" + currentDeleteSupplierName;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
currentDeleteProductId = -1;
btnSave.Enabled = false;//after update operation save is locked
}
}
//after any of above operation,we have to refresh top grid
allPS = ProductSupplierDB.GetAllProductsSuppliers();
btnAdd.Enabled = false;
btnDelete.Enabled = false;
btnUpdate.Enabled = false;
panel1.Visible = true;
panel2.Visible = false;
}
