public string[] getProductDetailBarcode(string barcode)
{
try
{
SqlCommand cmd = new SqlCommand("st_getProductDetailByBarcode", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@barcode", barcode);
MainClass.con.Open();
SqlDataReader dr1 = cmd.ExecuteReader();
if (dr1.HasRows)
{
while (dr1.Read())
{
//product id
product[0] = dr1[0].ToString();
//product name
product[1] = dr1[1].ToString();
//product barcode
product[2] = dr1[2].ToString();
}
}
else
{
MainClass.ShowMSG("Invalid Barcode", "Error", "Error");
Array.Clear(product, 0, product.Length);
}
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
return(product);
}
public void showStock(DataGridView gv, DataGridViewColumn ProductIDGV, DataGridViewColumn NameGV, DataGridViewColumn BarcodeGV, DataGridViewColumn ExpiryGV, DataGridViewColumn BuyPriceGV, DataGridViewColumn SellPriceGV, DataGridViewColumn CategoryGV, DataGridViewColumn StockGV, DataGridViewColumn StatusGV, DataGridViewColumn TotGV)
{
try
{
SqlCommand cmd;
cmd = new SqlCommand("st_getAllStock", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
ProductIDGV.DataPropertyName = dt.Columns["ProductID"].ToString();
NameGV.DataPropertyName = dt.Columns["Product"].ToString();
BarcodeGV.DataPropertyName = dt.Columns["Barcode"].ToString();
ExpiryGV.DataPropertyName = dt.Columns["Expiry"].ToString();
BuyPriceGV.DataPropertyName = dt.Columns["BuyPrice"].ToString();
SellPriceGV.DataPropertyName = dt.Columns["SellPrice"].ToString();
CategoryGV.DataPropertyName = dt.Columns["Category"].ToString();
StockGV.DataPropertyName = dt.Columns["Stock"].ToString();
StatusGV.DataPropertyName = dt.Columns["Status"].ToString();
TotGV.DataPropertyName = dt.Columns["TotAmount"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public void showSupplier(DataGridView gv, DataGridViewColumn SupplierIDGV, DataGridViewColumn CompanyGV, DataGridViewColumn ContactPersonGV, DataGridViewColumn Phone1GV, DataGridViewColumn Phone2GV, DataGridViewColumn AddressGV, DataGridViewColumn TinGV, DataGridViewColumn StatusGV, string data = null)
{
try
{
SqlCommand cmd;
if (data == null)
{
cmd = new SqlCommand("st_getSupplierData", MainClass.con);
}
else
{
cmd = new SqlCommand("st_getSupplierDataLIKE", MainClass.con);
cmd.Parameters.AddWithValue("@data", data);
}
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
SupplierIDGV.DataPropertyName = dt.Columns["ID"].ToString();
CompanyGV.DataPropertyName = dt.Columns["Company"].ToString();
ContactPersonGV.DataPropertyName = dt.Columns["ContactPerson"].ToString();
Phone1GV.DataPropertyName = dt.Columns["phone1"].ToString();
Phone2GV.DataPropertyName = dt.Columns["phone2"].ToString();
AddressGV.DataPropertyName = dt.Columns["Address"].ToString();
TinGV.DataPropertyName = dt.Columns["tin"].ToString();
StatusGV.DataPropertyName = dt.Columns["Status"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error...", "Error");
}
}
private void LoginBtn_Click(object sender, EventArgs e)
{
if (usernameTxt.Text == "")
{
usernameErrorLbl.Visible = true;
}
else
{
usernameErrorLbl.Visible = false;
}
if (pwdTxt.Text == "")
{
PasswordErrorLbl.Visible = true;
}
else
{
PasswordErrorLbl.Visible = false;
}
if (usernameErrorLbl.Visible || PasswordErrorLbl.Visible)
{
MainClass.ShowMSG("Fileds with * are mandatory", "Stop", "Error");
}
if (usernameTxt.Text != "" && pwdTxt.Text != "")
{
if (retrieval.getUserDetails(usernameTxt.Text, pwdTxt.Text))
{
HomeScreen hm = new HomeScreen();
MainClass.showWindow(hm, this, MDI.ActiveForm);
}
}
}
public int insertRefunds(Int64 saleID, DateTime date, int donBy, Int64 proID, Int16 quantity, float Amount)
{
try
{
SqlCommand cmd = new SqlCommand("st_insertRefundsReturns", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@saleID", saleID);
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@doneBy", donBy);
cmd.Parameters.AddWithValue("@proID", proID);
cmd.Parameters.AddWithValue("@quantity", quantity);
cmd.Parameters.AddWithValue("@amount", Amount);
MainClass.con.Open();
refCount = cmd.ExecuteNonQuery();
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
return(refCount);
}
public void showReport(ReportDocument rd, CrystalReportViewer crv, string proc, string reportFile, string param = null, object val = null)
{
try
{
//DialogResult dr = MessageBox.Show(param, Convert.ToInt32(val).ToString(), MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
SqlCommand cmd = new SqlCommand(proc, MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
if (param != null && val != null)
{
cmd.Parameters.AddWithValue(param, val);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
rd.Load(Application.StartupPath + "\\Reports\\" + reportFile);
rd.SetDataSource(dt);
crv.ReportSource = rd;
crv.RefreshReport();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error ", "Error");
}
}
public void UpdateProductPrice(Int64 proID, float buy, float sell = 0, float discount = 0, float profit = 0)
{
try
{
SqlCommand cmd;
if (sell == 0 && discount == 0 && profit == 0)
{
cmd = new SqlCommand("st_updateProductBuyPrice", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@proID", proID);
cmd.Parameters.AddWithValue("@buyPrice", buy);
}
else
{
cmd = new SqlCommand("st_updateProductPrice", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@proID", proID);
cmd.Parameters.AddWithValue("@bp", buy);
cmd.Parameters.AddWithValue("@sp", sell);
cmd.Parameters.AddWithValue("@dis", discount);
cmd.Parameters.AddWithValue("@profit", profit);
}
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public void showDailySales(DateTime date, DataGridView gv, DataGridViewColumn SaleIDGV, DataGridViewColumn UserGV, DataGridViewColumn TotAmountGV, DataGridViewColumn TotDisGV, DataGridViewColumn GivenGV, DataGridViewColumn ReturnGV, DataGridViewColumn UserIDGV)
{
try
{
SqlCommand cmd = new SqlCommand("st_getDailySales", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@date", date);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
SaleIDGV.DataPropertyName = dt.Columns["SalesID"].ToString();
UserGV.DataPropertyName = dt.Columns["UserName"].ToString();
TotAmountGV.DataPropertyName = dt.Columns["TotalAmount"].ToString();
TotDisGV.DataPropertyName = dt.Columns["TotalDiscount"].ToString();
GivenGV.DataPropertyName = dt.Columns["GivenAmount"].ToString();
ReturnGV.DataPropertyName = dt.Columns["ReturnAmount"].ToString();
UserIDGV.DataPropertyName = dt.Columns["UserID"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public void updateProduct(Int64 proId, string name, string barcode, int categoryId, DateTime?expiry = null)
{
try
{
SqlCommand cmd = new SqlCommand("st_updateProduct", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@barcode", barcode);
cmd.Parameters.AddWithValue("@catID", categoryId);
cmd.Parameters.AddWithValue("@id", proId);
if (expiry == null)
{
cmd.Parameters.AddWithValue("@expiry", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@expiry", expiry);
}
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
MainClass.ShowMSG(name + " updated to the system successfully", "Success...", "Success");
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public object getProductQuantity(Int64 proID, string salID = null)
{
try
{
SqlCommand cmd;
if (salID != null)
{
cmd = new SqlCommand("st_getQuantityFromSD", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
Int64 salId = Convert.ToInt64(salID);
cmd.Parameters.AddWithValue("@saleID", salId);
}
else
{
cmd = new SqlCommand("st_getProductQuantity", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.Parameters.AddWithValue("@proID", proID);
MainClass.con.Open();
productStockCount = cmd.ExecuteScalar();
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
return(productStockCount);
}
public void showProduct(DataGridView gv, DataGridViewColumn ProductIDGV, DataGridViewColumn NameGV, DataGridViewColumn BarcodeGV, DataGridViewColumn ExpiryGV, DataGridViewColumn CategoryGV, DataGridViewColumn CategoryIDGV, string data = null)
{
try
{
SqlCommand cmd;
if (data == null)
{
cmd = new SqlCommand("st_getProductData", MainClass.con);
}
else
{
cmd = new SqlCommand("st_getProductDataLIKE", MainClass.con);
cmd.Parameters.AddWithValue("@data", data);
}
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
ProductIDGV.DataPropertyName = dt.Columns["ID"].ToString();
NameGV.DataPropertyName = dt.Columns["Name"].ToString();
BarcodeGV.DataPropertyName = dt.Columns["Barcode"].ToString();
ExpiryGV.DataPropertyName = dt.Columns["Expdate"].ToString();
CategoryGV.DataPropertyName = dt.Columns["Category"].ToString();
CategoryIDGV.DataPropertyName = dt.Columns["CategoryId"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public void getListWithTwoParameters(string proc, ComboBox cb, string displayMemeber, string valueMemeber, string param1, object val1, string param2, object val2)
{
try
{
cb.DataSource = null;
//cb.Items.Clear();
cb.Items.Insert(0, "Select...");
SqlCommand cmd = new SqlCommand(proc, MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(param1, val1);
cmd.Parameters.AddWithValue(param2, val2);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow dr = dt.NewRow();
dr.ItemArray = new object[] { 0, "Select..." };
dt.Rows.InsertAt(dr, 0);
cb.DataSource = dt;
cb.DisplayMember = displayMemeber;
cb.ValueMember = valueMemeber;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public void showCategory(DataGridView gv, DataGridViewColumn categoryIDGV, DataGridViewColumn categoryNameGV, DataGridViewColumn statusGV, string data = null)
{
try
{
SqlCommand cmd;
if (data == null)
{
cmd = new SqlCommand("st_getCategories", MainClass.con);
}
else
{
cmd = new SqlCommand("st_getCategoryDataLIKE", MainClass.con);
cmd.Parameters.AddWithValue("@data", data);
}
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
categoryIDGV.DataPropertyName = dt.Columns["ID"].ToString();
categoryNameGV.DataPropertyName = dt.Columns["Category"].ToString();
statusGV.DataPropertyName = dt.Columns["Status"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error...", "Error");
}
}
public void showUsers(DataGridView gv, DataGridViewColumn userIDGV, DataGridViewColumn nameGV, DataGridViewColumn usernameGV, DataGridViewColumn passGV, DataGridViewColumn emailGV, DataGridViewColumn phoneGV, DataGridViewColumn statusGV, string data = null)
{
try
{
SqlCommand cmd;
if (data == null)
{
cmd = new SqlCommand("st_getUserData", MainClass.con);
}
else
{
cmd = new SqlCommand("st_getUserDataLIKE", MainClass.con);
cmd.Parameters.AddWithValue("@data", data);
}
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
userIDGV.DataPropertyName = dt.Columns["ID"].ToString();
nameGV.DataPropertyName = dt.Columns["Name"].ToString();
usernameGV.DataPropertyName = dt.Columns["Username"].ToString();
passGV.DataPropertyName = dt.Columns["Password"].ToString();
emailGV.DataPropertyName = dt.Columns["Email"].ToString();
phoneGV.DataPropertyName = dt.Columns["Phone"].ToString();
statusGV.DataPropertyName = dt.Columns["Status"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error...", "Error");
}
}
public void updateUser(int id, string name, string username, string pass, string email, string phone, Int16 status)
{
try
{
SqlCommand cmd = new SqlCommand("st_updateUsers", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@pwd", pass);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@status", status);
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
MainClass.ShowMSG(name + " updated to the system successfully", "Success...", "Success");
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public bool checkProductPriceExistance(Int64 pID)
{
try
{
SqlCommand cmd = new SqlCommand("st_checkProductPriceExist", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@proID", pID);
MainClass.con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
existance = true;
}
else
{
existance = false;
}
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
return(existance);
}
public void showPurchaseInvoiceDetails(Int64 pid, DataGridView gv, DataGridViewColumn PIDGV, DataGridViewColumn ProductIDGV, DataGridViewColumn NameGV, DataGridViewColumn pupGV, DataGridViewColumn quanGV, DataGridViewColumn totGV)
{
try
{
SqlCommand cmd = new SqlCommand("st_getPurchaseInvoiceDetails", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pid", pid);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
ProductIDGV.DataPropertyName = dt.Columns["ProductID"].ToString();
PIDGV.DataPropertyName = dt.Columns["PID"].ToString();
NameGV.DataPropertyName = dt.Columns["Product"].ToString();
pupGV.DataPropertyName = dt.Columns["UnitPrice"].ToString();
totGV.DataPropertyName = dt.Columns["TotalPrice"].ToString();
quanGV.DataPropertyName = dt.Columns["Quantity"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error...", "Error");
}
}
public static bool checkAdminExist()
{
try
{
SqlCommand cmd = new SqlCommand("st_checkAdminExistance", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
MainClass.con.Open();
int userExist = (int)cmd.ExecuteScalar();
if (userExist > 0)
{
checkAdmin = true;
}
else
{
checkAdmin = false;
}
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error ", "Error");
}
return(checkAdmin);
}
public void showProductsWRTCategory(int catID, DataGridView gv, DataGridViewColumn ProductIDGV, DataGridViewColumn NameGV, DataGridViewColumn BuyPriceGV, DataGridViewColumn FinalPriceGV, DataGridViewColumn DiscountGV, DataGridViewColumn ProfitMarginGV)
{
try
{
SqlCommand cmd = new SqlCommand("st_getProductsWRTCategory", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@catID", catID);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
ProductIDGV.DataPropertyName = dt.Columns["ProductID"].ToString();
NameGV.DataPropertyName = dt.Columns["ProductName"].ToString();
BuyPriceGV.DataPropertyName = dt.Columns["BuyPrice"].ToString();
FinalPriceGV.DataPropertyName = dt.Columns["SellPrice"].ToString();
DiscountGV.DataPropertyName = dt.Columns["Discount"].ToString();
ProfitMarginGV.DataPropertyName = dt.Columns["ProfitPercentage"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public override void SaveBtn_Click(object sender, EventArgs e)
{
if (CatnameTxt.Text == "")
{
CategoryErrorLbl.Visible = true;
}
else
{
CategoryErrorLbl.Visible = false;
}
if (activeCB.SelectedIndex == -1)
{
activeErrorLbl.Visible = true;
}
else
{
activeErrorLbl.Visible = false;
}
if (CategoryErrorLbl.Visible || activeErrorLbl.Visible)
{
MainClass.ShowMSG("fields with * are mandatory", "stop", "Error");//Error is the type of message
}
else
{
if (activeCB.SelectedIndex == 0)
{
stat = 1;
}
else if (activeCB.SelectedIndex == 1)
{
stat = 0;
}
if (edit == 0)//code for save operation
{
Insertion i = new Insertion();
i.insertCategory(CatnameTxt.Text, stat);
r.showCategory(CatdataGridView, categoryIDGV, CatNameGV, CatStatusGV);
MainClass.disable_reset(leftPanel);
}
else if (edit == 1)//code for update operation
{
DialogResult dr = MessageBox.Show("Are you sure, you want to update record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
Updation u = new Updation();
u.updateCategory(catID, CatnameTxt.Text, stat);
r.showCategory(CatdataGridView, categoryIDGV, CatNameGV, CatStatusGV);
MainClass.disable_reset(leftPanel);
}
}
}
}
public static bool getUserDetails(string username, string password)
{
try
{
SqlCommand cmd = new SqlCommand("st_getUserDetails", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@user", username);
cmd.Parameters.AddWithValue("@pass", password);
MainClass.con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
checkLogin = true;
while (dr.Read())
{
USER_ID = Convert.ToInt32(dr["ID"].ToString());
EMP_NAME = dr["Name"].ToString();
Role = Convert.ToInt32(dr["Role"].ToString());
user_name = dr["Username"].ToString();
pass_word = dr["Password"].ToString();
}
}
else
{
checkLogin = false;
if (username != null && password != null)
{
if (user_name != username && pass_word == password)
{
MainClass.ShowMSG("Invalid Username", "Error", "Error");
}
else if (user_name == username && pass_word != password)
{
MainClass.ShowMSG("Invalid Password", "Error", "Error");
}
else if (user_name != username && pass_word != password)
{
MainClass.ShowMSG("Invalid Username and Password", "Error", "Error");
}
}
}
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error ", "Error");
}
return(checkLogin);
}
public void UpdateStockNoConnection(Int64 proID, int quan)
{
try
{
SqlCommand cmd = new SqlCommand("st_updateStock", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@proID", proID);
cmd.Parameters.AddWithValue("@quan", quan);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public object getProductQuantityNoConnection(Int64 proID)
{
try
{
SqlCommand cmd = new SqlCommand("st_getProductQuantity", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@proID", proID);
productStockCount = cmd.ExecuteScalar();
}
catch (Exception ex)
{
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
return(productStockCount);
}
public override void SaveBtn_Click(object sender, EventArgs e)
{
if (PurchasedataGridView.Rows.Count > 0)
{
Int64 purchaseInvoiceID;
Insertion i = new Insertion();
using (TransactionScope sc = new TransactionScope())
{
purchaseInvoiceID = i.insertPurchaseInvoice(DateTime.Today, retrieval.USER_ID, Convert.ToInt32(SupplierDD.SelectedValue));
foreach (DataGridViewRow row in PurchasedataGridView.Rows)
{
co += i.insertPurchaseInvoiceDetails(purchaseInvoiceID, Convert.ToInt32(row.Cells["ProductIDGV"].Value.ToString()), Convert.ToInt32(row.Cells["QuantityGV"].Value.ToString()), Convert.ToSingle(row.Cells["TotGV"].Value.ToString()));
if (r.checkProductPriceExistance(Convert.ToInt32(row.Cells["ProductIDGV"].Value.ToString())))
{
u.UpdateProductPrice(Convert.ToInt32(row.Cells["ProductIDGV"].Value.ToString()), Convert.ToSingle(row.Cells["PerUnitGV"].Value.ToString()));
}
else
{
i.insertProductPrice(Convert.ToInt32(row.Cells["ProductIDGV"].Value.ToString()), Convert.ToSingle(row.Cells["PerUnitGV"].Value.ToString()));
}
object ob = r.getProductQuantity(Convert.ToInt32(row.Cells["ProductIDGV"].Value.ToString()));
int q = Convert.ToInt32(ob);
if (ob != null)
{
q += Convert.ToInt32(row.Cells["QuantityGV"].Value.ToString());
u.UpdateStock(Convert.ToInt32(row.Cells["ProductIDGV"].Value.ToString()), q);
}
else
{
i.insertStock(Convert.ToInt32(row.Cells["ProductIDGV"].Value.ToString()), Convert.ToInt32(row.Cells["QuantityGV"].Value.ToString()));
}
}
if (co > 0)
{
MainClass.ShowMSG("Purchase invoice created Successfully.", "Success", "Success");
}
else
{
MainClass.ShowMSG("Unable to create Purchase invoice.", "Error", "Error");
}
sc.Complete();
}
}
}
public object getProductSellPrice(int proID)
{
try
{
SqlCommand cmd = new SqlCommand("st_getPerProductSellPrice", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@proID", proID);
MainClass.con.Open();
productSellPrice = cmd.ExecuteScalar();
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
return(productSellPrice);
}
public string[] getProductByBarcodeList(string barcode)
{
SqlCommand cmd;
try
{
cmd = new SqlCommand("st_getProductByBarcode", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@barcode", barcode);
MainClass.con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
//product id
productData[0] = dr[0].ToString();
//product name
productData[1] = dr[1].ToString();
//product barcode
productData[2] = dr[2].ToString();
//selling price
productData[3] = dr[3].ToString();
//discount
productData[4] = dr[4].ToString();
////final price
//productData[5] = dr[5].ToString();
}
}
else
{
MainClass.ShowMSG("Invalid Barcode", "Error", "Error");
Array.Clear(productData, 0, productData.Length);
}
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
return(productData);
}
public void insertCategory(string name, Int16 status)
{
try
{
SqlCommand cmd = new SqlCommand("st_insertCategory", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@isActive", status);
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
MainClass.ShowMSG(name + " added to the system successfully", "Success", "Success");
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public void updateSalesQuantity(Int64 id, Int64 proId, int Quantity)
{
try
{
SqlCommand cmd = new SqlCommand("st_updateQuantityInSalesDetails", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@saleID", id);
cmd.Parameters.AddWithValue("@proID", proId);
cmd.Parameters.AddWithValue("@quan", Quantity);
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
//MainClass.ShowMSG(name + " updated to the system successfully", "Success...", "Success");
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
public void delete(object id, string proc, string param)
{
try
{
SqlCommand cmd = new SqlCommand(proc, MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(param, id);
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
MainClass.ShowMSG("Record deleted successfully", "Success...", "Success");
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error...", "Error");
}
}
public void insertProductPrice(Int64 proID, float buyingAmount)
{
try
{
SqlCommand cmd = new SqlCommand("st_insertProductPrice", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@proID", proID);
cmd.Parameters.AddWithValue("@bp", buyingAmount);
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.ShowMSG(ex.Message, "Error", "Error");
}
}
