public static bool DeletePPSbypps(Pkg_Product_Suppliers pps)
{
SqlConnection conn = Connection.GetConnection();
string strdelcmd = "Delete from Packages_Products_Suppliers " +
"where PackageId=@pkgID and ProductSupplierId=@ProdSupId";
SqlCommand delcmd = new SqlCommand(strdelcmd, conn);
delcmd.Parameters.AddWithValue("@pkgID", pps.pkgId);
delcmd.Parameters.AddWithValue("@ProdSupId", pps.prodSupId);
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 Pkg_Product_Suppliers GetDatabyPSID(int prodsupId)
{
SqlConnection conn = Connection.GetConnection();
string strcmd = "select * from Packages_Products_Suppliers where ProductSupplierId=@prodsupId";
SqlCommand selcmd = new SqlCommand(strcmd, conn);
selcmd.Parameters.AddWithValue("@prodsupId", prodsupId);
try
{
conn.Open();
SqlDataReader datareader = selcmd.ExecuteReader(CommandBehavior.SingleRow);
if (datareader.Read())
{
Pkg_Product_Suppliers pkgprodsup = new Pkg_Product_Suppliers();
pkgprodsup.pkgId = (int)datareader["PackageId"];
pkgprodsup.prodSupId = (int)datareader["ProductSupplierId"];
return(pkgprodsup);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static int AddPPSData(Pkg_Product_Suppliers pps)
{
SqlConnection conn = Connection.GetConnection();
string strcmd = "insert Packages_Products_Suppliers " +
"(PackageId, ProductSupplierId) " +
"values(@pkgId,@ProdSupID)";
SqlCommand insertcmd = new SqlCommand(strcmd, conn);
insertcmd.Parameters.AddWithValue("@pkgId", pps.pkgId);
insertcmd.Parameters.AddWithValue("@ProdSupID", pps.prodSupId);
try
{
conn.Open();
insertcmd.ExecuteNonQuery();
//string strselcmd = "select IDENT_CURRENT('Packages_Products_Suppliers') FROM Packages_Products_Suppliers";
//SqlCommand selectCommand =
// new SqlCommand(strselcmd, conn);
//int ppsID = Convert.ToInt32(selectCommand.ExecuteScalar());
//return ppsID;
return(1);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static bool UpdateProdSupData(Pkg_Product_Suppliers oldpps, Pkg_Product_Suppliers newpps)
{
SqlConnection conn = Connection.GetConnection();
string strupdatecmd = "update Packages_Products_Suppliers set " +
"ProductSupplierId=@prodsupId " +
"where PackageId=@pkgId";
SqlCommand updatecmd = new SqlCommand(strupdatecmd, conn);
updatecmd.Parameters.AddWithValue("@pkgId", oldpps.pkgId);
updatecmd.Parameters.AddWithValue("@prodsupId", newpps.prodSupId);
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 BindingNavigatorAddNewItem_Click(object sender, EventArgs e)
//{
// dgvPkgProd.DataSource = null;
// dgvPkgProd.ColumnCount = 1;
// dgvPkgProd.Columns[0].Name = "ProdName";
//}
private void BtnDeductProdfromPkg_Click(object sender, EventArgs e)
{
//if pkg with prod_sup
//update pkg_prod_sup delete record
if (dgvPkgProd.Rows.Count > 0)
{
if (packageIdTextBox.Text == "" || packageIdTextBox.Text == "-1")
{
//if new package
//remove row
if (dgvPkgProd.SelectedRows.Count > 0)
{
for (int i = 0; i < dgvPkgProd.SelectedRows.Count; i++)
{
dgvPkgProd.Rows.RemoveAt(dgvPkgProd.SelectedRows[i].Index);
dgvPkgProd.Refresh();
}
}
}
else if (packageIdTextBox.Text != "" && packageIdTextBox.Text != "-1")
{
//if pkg with prod_sup
//update pkg_prod_sup delete record
int pkgId = Convert.ToInt32(packageIdTextBox.Text);
DataTable dt = Pkg_Product_SuppliersDB.GetAllPsDatabyPkgId(pkgId);
if (dgvPkgProd.SelectedRows.Count > 0)
{
for (int i = 0; i < dgvPkgProd.SelectedRows.Count; i++)
{
//delete pkg_prod_sup record according to datagridview prodname and supname
int prodId = ProductDB.GetProductsbyName(dgvPkgProd.SelectedRows[i].Cells[0].Value.ToString()).nProdId;
int supId = SupplierDB.GetSuppliersbyName(dgvPkgProd.SelectedRows[i].Cells[1].Value.ToString()).nSupId;
DataTable dtps = Prod_SuppliersDB.GetProd_SupbyPsId(prodId, supId);
for (int m = 0; m < dtps.Rows.Count; m++)
{
int prodsupId = Convert.ToInt32(dtps.Rows[m]["ProductSupplierId"]);
for (int j = 0; j < dt.Rows.Count; j++)
{
int ppspsId = Convert.ToInt32(dt.Rows[j]["ProductSupplierId"]);
if (prodsupId == ppspsId)
{
Pkg_Product_Suppliers pps = new Pkg_Product_Suppliers();
pps.pkgId = pkgId;
pps.prodSupId = prodsupId;
Pkg_Product_SuppliersDB.DeletePPSbypps(pps);
}
}
}
}
refreshitems();
}
}
}
}
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 BindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
if (packageIdTextBox.Text != "" && packageIdTextBox.Text != "-1")
{
int pkgId = Convert.ToInt32(packageIdTextBox.Text);
//pkgId = Convert.ToInt32(packageIdTextBox.Text);
if (Pkg_Product_SuppliersDB.CheckDatabyPkgID(pkgId))
{
Package oldpkg = new Package();
oldpkg.nPkgId = pkgId;
if (pkgId != 0)
{
Pkg_Product_Suppliers pps = new Pkg_Product_Suppliers();
pps.pkgId = pkgId;
Pkg_Product_SuppliersDB.DeletePPSbyPkgId(pps);
if (!BookingDB.CheckPackageIdInuse(pkgId))
{
PackageDB.DeletePackage(oldpkg);
}
else
{
CustMesg cust = new CustMesg();
cust.Showmsg("Package is in use in Bookings table!");
cust.Show();
}
}
BindingSource pkgbindsource = new BindingSource();
pkgbindsource.DataSource = PackageDB.GetAllPkg();
PackagesBindingNavigator.BindingSource = pkgbindsource;
refreshitems();
}
else if (PackageDB.CheckPackIdExist(pkgId))
{
Package oldpkg = new Package();
oldpkg.nPkgId = pkgId;
if (!BookingDB.CheckPackageIdInuse(pkgId))
{
PackageDB.DeletePackage(oldpkg);
}
else
{
CustMesg cust = new CustMesg();
cust.Showmsg("Package is in use in Bookings table!");
cust.Show();
}
BindingSource pkgbindsource = new BindingSource();
pkgbindsource.DataSource = PackageDB.GetAllPkg();
PackagesBindingNavigator.BindingSource = pkgbindsource;
refreshitems();
}
else
{
//Pkg_Product_Suppliers pps = new Pkg_Product_Suppliers();
//pps.pkgId = pkgId;
//Pkg_Product_SuppliersDB.DeletePPSbyPkgId(pps);
//Package oldpkg = new Package();
//oldpkg.nPkgId = pkgId;
//PackageDB.DeletePackage(oldpkg);
//if (pkgId != 0)
//{
// Pkg_Product_Suppliers pkgps = new Pkg_Product_Suppliers();
// pps.pkgId = pkgId;
// Pkg_Product_SuppliersDB.DeletePPSbyPkgId(pkgps);
//}
BindingSource pkgbindsource = new BindingSource();
pkgbindsource.DataSource = PackageDB.GetAllPkg();
PackagesBindingNavigator.BindingSource = pkgbindsource;
refreshitems();
CustMesg cust = new CustMesg();
cust.Showmsg("Not a valid record!");
cust.Show();
// MessageBox.Show("The Package is using in Package_Product_Supplier Table, Please delete record in Package_Product_Supplier Table First!");
}
}
else
{
BindingSource pkgbindsource = new BindingSource();
pkgbindsource.DataSource = PackageDB.GetAllPkg();
PackagesBindingNavigator.BindingSource = pkgbindsource;
refreshitems();
CustMesg cust = new CustMesg();
cust.Showmsg("Not a valid record!");
cust.Show();
}
}
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();
}
}