public static int AddRecord(Prod_Suppliers prodsup)
{
SqlConnection conn = Connection.GetConnection();
string strcmd = "insert Products_Suppliers " +
"(ProductId,SupplierId) " +
"values(@ProdID,@SupID)";
SqlCommand insertcmd = new SqlCommand(strcmd, conn);
//insertcmd.Parameters.AddWithValue("@prodsupID", prodsup.nProdSupId);
insertcmd.Parameters.AddWithValue("@ProdID", prodsup.nProdId);
insertcmd.Parameters.AddWithValue("@SupID", prodsup.nSupId);
try
{
conn.Open();
insertcmd.ExecuteNonQuery();
string strselcmd = "select IDENT_CURRENT('Products_Suppliers') FROM Products_Suppliers";
SqlCommand selectCommand =
new SqlCommand(strselcmd, conn);
int prodsupID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(prodsupID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static bool DeleteRec(Prod_Suppliers prodsup)
{
SqlConnection conn = Connection.GetConnection();
string strdelcmd = "Delete from Products_Suppliers " +
"where ProductSupplierId=@prod_supID";
SqlCommand delcmd = new SqlCommand(strdelcmd, conn);
delcmd.Parameters.AddWithValue("prod_supID", prodsup.nProdSupId);
try
{
conn.Open();
int count = delcmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static bool UpdateRecord(Prod_Suppliers oldrec, Prod_Suppliers newrec)
{
SqlConnection conn = Connection.GetConnection();
string strupdatecmd = "update Products_Suppliers set " +
"ProductId=@ProdID, " +
"SupplierId=@SupID " +
"where ProductSupplierId=@Prod_SupID";
SqlCommand updatecmd = new SqlCommand(strupdatecmd, conn);
updatecmd.Parameters.AddWithValue("@ProdID", newrec.nProdId);
updatecmd.Parameters.AddWithValue("@SupID", newrec.nSupId);
updatecmd.Parameters.AddWithValue("@Prod_SupID", oldrec.nProdSupId);
try
{
conn.Open();
int count = updatecmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
private void UpdateProdSupTopps(int pkgId)
{
Prod_Suppliers prodsup = new Prod_Suppliers();
Products prod = ProductDB.GetProductsbyName(cbProducts.Text);
prodsup.nProdId = prod.nProdId;//cbProducts.SelectedValue);
Suppliers sup = SupplierDB.GetSuppliersbyName(cbSups.Text);
prodsup.nSupId = sup.nSupId;// Convert.ToInt32(cbSups.SelectedValue);
DataTable dt = Prod_SuppliersDB.GetProd_SupbyPsId(prodsup.nProdId, prodsup.nSupId);
int prodsupid = 0;
if (dt.Rows.Count > 0)
{
prodsupid = Convert.ToInt32(dt.Rows[0]["ProductSupplierId"]);
}
if (prodsupid > 0)
{
Pkg_Product_Suppliers pps = new Pkg_Product_Suppliers();
pps.prodSupId = prodsupid;
pps.pkgId = pkgId;
Pkg_Product_SuppliersDB.AddPPSData(pps);
refreshitems();
}
else
{
CustMesg custMesg = new CustMesg();
custMesg.Showmsg("There is no supplier information in database! \n Please select other products!");
custMesg.Show();
}
}
private void BindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
if (Validation())
{
if (txtProdSupID.Text != "-1")
{
int nprodId = ProductDB.GetProductsbyName(cbProducts.Text).nProdId;
int nsupId = SupplierDB.GetSuppliersbyName(cbSuppliers.Text).nSupId;
Prod_Suppliers ps = new Prod_Suppliers();
ps.nProdId = nprodId;
ps.nSupId = nsupId;
int prodsupID = Convert.ToInt32(txtProdSupID.Text);
ps.nProdSupId = prodsupID;
if (!Pkg_Product_SuppliersDB.CheckDataInusebyPSID(prodsupID) && !BookingDetailsDB.CheckProdSupInuse(prodsupID))
{
Prod_SuppliersDB.DeleteRec(ps);
refreshitems();
}
else
{
CustMesg custMesg = new CustMesg();
custMesg.Showmsg("The Product_Supplier record is in use in Pkg_Product_Supplier table,\n Please delete record in Pkg_Product_Supplier table first!");
custMesg.Show();
refreshitems();
BindingSource psbindsource = new BindingSource();
psbindsource.DataSource = Prod_SuppliersDB.GetAllDatabyName();
bindingNavigatorPS.BindingSource = psbindsource;
}
}
else
{
CustMesg msgfrm = new CustMesg();
msgfrm.Showmsg("Not a valid record!");
msgfrm.Show();
refreshitems();
BindingSource psbindsource = new BindingSource();
psbindsource.DataSource = Prod_SuppliersDB.GetAllDatabyName();
bindingNavigatorPS.BindingSource = psbindsource;
//MessageBox.Show("Not a valid record!");
}
}
else
{
refreshitems();
BindingSource psbindsource = new BindingSource();
psbindsource.DataSource = Prod_SuppliersDB.GetAllDatabyName();
bindingNavigatorPS.BindingSource = psbindsource;
}
}
private void InsertProdSupTopps(int pkgId)
{
Prod_Suppliers prodsup = new Prod_Suppliers();
Products prod = ProductDB.GetProductsbyName(cbProducts.Text);
prodsup.nProdId = prod.nProdId;//cbProducts.SelectedValue);
Suppliers sup = SupplierDB.GetSuppliersbyName(cbSups.Text);
prodsup.nSupId = sup.nSupId;// Convert.ToInt32(cbSups.SelectedValue);
DataTable dt = Prod_SuppliersDB.GetProd_SupbyPsId(prodsup.nProdId, prodsup.nSupId);
int prodsupid = Convert.ToInt32(dt.Rows[0]["ProductSupplierId"]);
Pkg_Product_Suppliers pps = new Pkg_Product_Suppliers();
pps.prodSupId = prodsupid;
pps.pkgId = pkgId;
Pkg_Product_SuppliersDB.AddPPSData(pps);
refreshitems();
}
private void ToolStripButton1_Click(object sender, EventArgs e)
{
//Save
if (Validation())
{
int nprodId = ProductDB.GetProductsbyName(cbProducts.Text).nProdId;
int nsupId = SupplierDB.GetSuppliersbyName(cbSuppliers.Text).nSupId;
Prod_Suppliers ps = new Prod_Suppliers();
ps.nProdId = nprodId;
ps.nSupId = nsupId;
if (txtProdSupID.Text == "-1")
{
if (Prod_SuppliersDB.GetProd_SupbyPsId(nprodId, nsupId).Rows.Count <= 0)
{
Prod_SuppliersDB.AddRecord(ps);
}
else
{
CustMesg msgfrm = new CustMesg();
msgfrm.Showmsg("Record exist already!");
msgfrm.Show();
//MessageBox.Show("Record exist already!");
}
}
else
{
int prodsupID = Convert.ToInt32(txtProdSupID.Text);
ps.nProdSupId = prodsupID;
Prod_SuppliersDB.UpdateRecord(ps, ps);
}
refreshitems();
BindingSource psbindsource = new BindingSource();
psbindsource.DataSource = Prod_SuppliersDB.GetAllDatabyName();
bindingNavigatorPS.BindingSource = psbindsource;
}
}
public static Prod_Suppliers GetProd_SupbyProdId(int ProdSupID)
{
SqlConnection conn = Connection.GetConnection();
string strcmd = "select * from Products_Suppliers " +
" where ProductSupplierId=@ProdSupID " +
"order by ProductSupplierId asc";
SqlCommand selcmd = new SqlCommand(strcmd, conn);
selcmd.Parameters.AddWithValue("@ProdSupID", ProdSupID);
try
{
conn.Open();
SqlDataReader prodsupreader = selcmd.ExecuteReader(CommandBehavior.SingleRow);
if (prodsupreader.Read())
{
Prod_Suppliers prod_Suppliers = new Prod_Suppliers();
prod_Suppliers.nProdSupId = (int)prodsupreader["ProductSupplierId"];
prod_Suppliers.nProdId = (int)prodsupreader["ProductId"];
prod_Suppliers.nSupId = (int)prodsupreader["SupplierId"];
return(prod_Suppliers);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
private void ToolStripBtnSave_Click(object sender, EventArgs e)
{
if (Validation())
{
int pkgId = 0;
Package pkg = new Package();
pkg.PkgName = pkgNameTextBox.Text;
pkg.pkgStDate = pkgStartDateDateTimePicker.Value;
pkg.pkgEndDate = pkgEndDateDateTimePicker.Value;
pkg.pkgDesc = pkgDescTextBox.Text;
pkg.pkgBasePrice = Convert.ToDouble(pkgBasePriceTextBox.Text);
pkg.pkgAgentCom = Convert.ToDouble(pkgAgencyCommissionTextBox.Text);
if (packageIdTextBox.Text == "-1")
{
pkgId = PackageDB.AddPackage(pkg);
if (pkgId != 0)
{
if (dgvPkgProd.Rows.Count > 0)
{
for (int i = 0; i < dgvPkgProd.Rows.Count; i++)
{
Prod_Suppliers prodsup = new Prod_Suppliers();
Products prod = ProductDB.GetProductsbyName(dgvPkgProd.Rows[i].Cells[0].Value.ToString());
prodsup.nProdId = prod.nProdId; //cbProducts.SelectedValue);
Suppliers sup = SupplierDB.GetSuppliersbyName(dgvPkgProd.Rows[i].Cells[1].Value.ToString());
prodsup.nSupId = sup.nSupId; // Convert.ToInt32(cbSups.SelectedValue);
DataTable dt = Prod_SuppliersDB.GetProd_SupbyPsId(prodsup.nProdId, prodsup.nSupId);
int prodsupid = Convert.ToInt32(dt.Rows[0]["ProductSupplierId"]);
//int prodsupid = Prod_SuppliersDB.AddRecord(prodsup);
Pkg_Product_Suppliers pps = new Pkg_Product_Suppliers();
pps.prodSupId = prodsupid;
pps.pkgId = pkgId;
Pkg_Product_SuppliersDB.AddPPSData(pps);
}
}
}
}
else
{
//update package
pkgId = Convert.ToInt32(packageIdTextBox.Text);
if (pkgId != 0)
{
Package oldpkg = new Package();
oldpkg.nPkgId = pkgId;
PackageDB.UpdatePkg(oldpkg, pkg);
DataTable dtprodsup = Pkg_Product_SuppliersDB.GetAllPkgProdSupData(pkgId);
if (dgvPkgProd.Rows.Count > 0)
{
for (int i = 0; i < dgvPkgProd.Rows.Count; i++)
{
string strprodname = dgvPkgProd.Rows[i].Cells[0].Value.ToString();
string strsupName = dgvPkgProd.Rows[i].Cells[1].Value.ToString();
for (int j = 0; j < dtprodsup.Rows.Count; j++)
{
if (strprodname != dtprodsup.Rows[j]["ProdName"].ToString() && strsupName != dtprodsup.Rows[j]["SupName"].ToString())
{
Prod_Suppliers prodsup = new Prod_Suppliers();
Products prod = ProductDB.GetProductsbyName(dgvPkgProd.Rows[i].Cells[0].Value.ToString());
prodsup.nProdId = prod.nProdId; //cbProducts.SelectedValue);
Suppliers sup = SupplierDB.GetSuppliersbyName(dgvPkgProd.Rows[i].Cells[1].Value.ToString());
prodsup.nSupId = sup.nSupId; // Convert.ToInt32(cbSups.SelectedValue);
DataTable dt = Prod_SuppliersDB.GetProd_SupbyPsId(prodsup.nProdId, prodsup.nSupId);
Prod_Suppliers newprodsup = new Prod_Suppliers();
newprodsup.nProdSupId = Convert.ToInt32(dt.Rows[0]["ProductSupplierId"]);
int prodsupid = newprodsup.nProdSupId;
Pkg_Product_Suppliers oldpps = new Pkg_Product_Suppliers();
Pkg_Product_Suppliers pps = new Pkg_Product_Suppliers();
pps.prodSupId = prodsupid;
oldpps.pkgId = pkgId;
Pkg_Product_SuppliersDB.UpdatePkgData(oldpps, pps);
}
}
}
}
}
}
BindingSource pkgbindsource = new BindingSource();
pkgbindsource.DataSource = PackageDB.GetAllPkg();
PackagesBindingNavigator.BindingSource = pkgbindsource;
refreshitems();
}
}
private void BtnAddProdSup_Click(object sender, EventArgs e)
{
if (validateprodSup())
{
//if current pkg have prod sup already change current package prod supplier
//update pkg_prod_supplier
//if current no prod sup
//add new recordd into pkg_prod_supplier
//if add to new package
//reemove binding souce of datagridview(done by package add button already) and just add row
int pkgId = Convert.ToInt32(packageIdTextBox.Text);
if (pkgId == -1)//new package
{
//create new package
//if no prod_supplier info then insert only package info
//if have prod_supplier info then insert package and package_product_supplier info
if (dgvPkgProd.Rows.Count > 0)
{
//have prod_supplier info then insert package and package_product_supplier info
List <Prod_Suppliers> prodlist = new List <Prod_Suppliers>();
for (int j = 0; j < dgvPkgProd.Rows.Count; j++)
{
Prod_Suppliers prodsup = new Prod_Suppliers();
prodsup.strProdName = dgvPkgProd.Rows[j].Cells[0].Value.ToString();
prodsup.strSupName = dgvPkgProd.Rows[j].Cells[1].Value.ToString();
prodlist.Add(prodsup);
}
for (int j = 0; j < prodlist.Count; j++)
{
if ((cbProducts.Text == prodlist[j].strProdName) && (cbSups.Text == prodlist[j].strSupName))
{
prodlist.RemoveAt(j);
break;
}
else
{
continue;
}
}
if (prodlist.Count > 0)
{
for (int i = 0; i < prodlist.Count; i++)
{
DataGridViewRow newrow = (DataGridViewRow)dgvPkgProd.Rows[0].Clone();;
newrow.Cells[0].Value = cbProducts.Text;
newrow.Cells[1].Value = cbSups.Text;
dgvPkgProd.Rows.Add(newrow);
}
}
}
else
{
//No prod_supplier info, only insert package information into package table
DataGridViewRow newrow = new DataGridViewRow();
//(DataGridViewRow)dgvPkgProd.Rows;
newrow.Cells[0].Value = cbProducts.Text;
newrow.Cells[1].Value = cbSups.Text;
dgvPkgProd.Rows.Add(newrow);
}
}
else if (dgvPkgProd.Rows.Count > 0)
{
//if combobox content already in datagridview
//do nothing
//if not in datagridview
//update pkg_prod_sup insert record
List <Prod_Suppliers> prodlist = new List <Prod_Suppliers>();
bool bexist = false;
//take current data in table into list
for (int j = 0; j < dgvPkgProd.Rows.Count; j++)
{
Prod_Suppliers prodsup = new Prod_Suppliers();
prodsup.strProdName = dgvPkgProd.Rows[j].Cells[0].Value.ToString();
prodsup.strSupName = dgvPkgProd.Rows[j].Cells[1].Value.ToString();
prodlist.Add(prodsup);
}
for (int j = 0; j < prodlist.Count; j++)
{
if ((cbProducts.Text == prodlist[j].strProdName) && (cbSups.Text == prodlist[j].strSupName))
{
//if the product and supplier already in table then remove from the list
prodlist.RemoveAt(j);
CustMesg custMesg = new CustMesg();
custMesg.Showmsg("Product_Supplier Info already in table!");
custMesg.Show();
//lblAddDelMsg.Text = "Product_Supplier Info already in table!";
bexist = true;
break;
}
else
{
continue;
}
}
//check data in prod_supplier table? if exist then just show info in gridview, else insert into prod_supplier table
//update data in package and pkg_prod_supplier
if (!bexist)
{
UpdateProdSupTopps(pkgId);
}
}
else
{
//package no prod_supplier yet
InsertProdSupTopps(pkgId);
}
}
}