public string[] getProductByBarcode(string barcode)
{
try
{
SqlCommand 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())
{
productData[0] = dr[0].ToString();
productData[1] = dr[1].ToString();
productData[2] = dr[2].ToString();
}
}
else
{
}
MainClass.con.Close();
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.showMSG(ex.Message, "Error", "Error");
}
return(productData);
}
public void showSuppliers(DataGridView gv, DataGridViewColumn suppIDGV, DataGridViewColumn comNameGV, DataGridViewColumn personGV, DataGridViewColumn phone1GV, DataGridViewColumn phone2GV, DataGridViewColumn addressGV, DataGridViewColumn ntnGV, DataGridViewColumn statusGV)
{
try
{
SqlCommand cmd = new SqlCommand("st_getSupplierData", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
suppIDGV.DataPropertyName = dt.Columns["ID"].ToString();
comNameGV.DataPropertyName = dt.Columns["Company"].ToString();
personGV.DataPropertyName = dt.Columns["Contact Person"].ToString();
phone1GV.DataPropertyName = dt.Columns["Phone 1"].ToString();
phone2GV.DataPropertyName = dt.Columns["Phone 2"].ToString();
addressGV.DataPropertyName = dt.Columns["Address"].ToString();
ntnGV.DataPropertyName = dt.Columns["NTN #"].ToString();
statusGV.DataPropertyName = dt.Columns["Status"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.showMSG(ex.Message, "Error", "Error");
}
}
public void insertProduct(string product, string barcode, float price, int catID, DateTime?expiry = null)
{
try
{
SqlCommand cmd = new SqlCommand("st_insertProduct", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", product);
cmd.Parameters.AddWithValue("@barcode", barcode);
cmd.Parameters.AddWithValue("@price", price);
if (expiry == null)
{
cmd.Parameters.AddWithValue("@expiry", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@expiry", expiry);
}
cmd.Parameters.AddWithValue("@catID", catID);
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
MainClass.showMSG(product + " added to the system successfully", "Success", "Success");
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.showMSG(ex.Message, "Error...", "Error");
}
}
public void showUsers(DataGridView gv, DataGridViewColumn userIDGV, DataGridViewColumn nameGV, DataGridViewColumn usernameGV, DataGridViewColumn pwdGV, DataGridViewColumn phoneGV, DataGridViewColumn emailGV, DataGridViewColumn statusGV, string data = null)
{
try
{
SqlCommand cmd;
if (data == null)
{
cmd = new SqlCommand("st_getUsersData", MainClass.con);
}
else
{
cmd = new SqlCommand("st_getUsersDataLike", 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();
pwdGV.DataPropertyName = dt.Columns["Password"].ToString();
phoneGV.DataPropertyName = dt.Columns["Phone"].ToString();
emailGV.DataPropertyName = dt.Columns["Email"].ToString();
statusGV.DataPropertyName = dt.Columns["Status"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.showMSG(ex.Message, "Error", "Error");
}
}
public static bool getUserDetails(string username, string password)
{
try
{
SqlCommand cmd = new SqlCommand("st_getUsersDetails", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", 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();
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)
{
MainClass.con.Close();
MainClass.showMSG("Unable to Login...", "Error", "Error");
}
return(checkLogin);
}
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("Data Deleted successfully", "Success", "Success");
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.showMSG(ex.Message, "Error...", "Error");
}
}
public void insertCat(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");
}
}
private void MDI_Load(object sender, EventArgs e)
{
try
{
if (File.Exists(path + "\\connect"))
{
Login log = new Login();
MainClass.showWindow(log, this);
}
else
{
Settings set = new Settings();
MainClass.showWindow(set, this);
}
}
catch (Exception ex)
{
MainClass.showMSG(ex.Message, "Error", "Error");
}
}
public void showCategories(DataGridView gv, DataGridViewColumn catIDGV, DataGridViewColumn catNameGV, DataGridViewColumn statusGV, string data = null)
{
try
{
SqlCommand cmd = new SqlCommand("st_getCategoriesData", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
catIDGV.DataPropertyName = dt.Columns["ID"].ToString();
catNameGV.DataPropertyName = dt.Columns["Category"].ToString();
statusGV.DataPropertyName = dt.Columns["Status"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.showMSG(ex.Message, "Error", "Error");
}
}
public void updateSupplier(int supID, string company, string person, string phone1, string address, Int16 status, string phone2 = null, string ntn = null)
{
try
{
SqlCommand cmd = new SqlCommand("st_UpdateSupplier", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@company", company);
cmd.Parameters.AddWithValue("@contactPerson", person);
cmd.Parameters.AddWithValue("@phone1", phone1);
if (phone2 == null)
{
cmd.Parameters.AddWithValue("@phone2", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@phone2", phone2);
}
cmd.Parameters.AddWithValue("@address", address);
if (ntn == null)
{
cmd.Parameters.AddWithValue("@ntn", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@ntn", ntn);
}
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@suppID", supID);
MainClass.con.Open();
cmd.ExecuteNonQuery();
MainClass.con.Close();
MainClass.showMSG(company + " updated successfully", "Success", "Success");
}
catch (Exception ex)
{
MainClass.con.Close();
MainClass.showMSG(ex.Message, "Error...", "Error");
}
}
public void getList(string proc, ComboBox cb, string DisplayMember, string ValueMember)
{
try
{
cb.Items.Clear();
cb.DataSource = null;
SqlCommand cmd = new SqlCommand(proc, MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
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 = DisplayMember;
cb.ValueMember = ValueMember;
}
catch (Exception ex)
{
MainClass.showMSG(ex.Message, "Error", "Error");
}
}
public void insertUser(string name, string username, string pwd, string phone, string email, Int16 status)
{
try
{
SqlCommand cmd = new SqlCommand("st_insertUsers", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@pwd", pwd);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@status", 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");
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
if (usernameTxt.Text.Trim() == string.Empty)
{
nameErrorLabel.Visible = true;
}
else
{
nameErrorLabel.Visible = false;
}
if (passtxt.Text.Trim() == string.Empty)
{
PassErrorLabel.Visible = true;
}
else
{
PassErrorLabel.Visible = false;
}
if (nameErrorLabel.Visible || PassErrorLabel.Visible)
{
MainClass.showMSG("Fields with * are mandatory", "Stop", "Error"); //Error is the type of message
}
else
{
if (usernameTxt.Text != "" && passtxt.Text != "")
{
if (retrieval.getUserDetails(usernameTxt.Text, passtxt.Text))
{
HomeScreen hs = new HomeScreen();
MainClass.showWindow(hs, this, MDI.ActiveForm);
}
else
{
}
}
}
}
public void showProducts(DataGridView gv, DataGridViewColumn proIDGV, DataGridViewColumn proNameGV, DataGridViewColumn barcodeGV, DataGridViewColumn expiryGV, DataGridViewColumn priceGV, DataGridViewColumn catGV, DataGridViewColumn catIDGV)
{
try
{
SqlCommand cmd = new SqlCommand("st_getProductData", MainClass.con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
proIDGV.DataPropertyName = dt.Columns["Product ID"].ToString();
proNameGV.DataPropertyName = dt.Columns["Product Name"].ToString();
barcodeGV.DataPropertyName = dt.Columns["Barcode"].ToString();
expiryGV.DataPropertyName = dt.Columns["Expiry"].ToString();
priceGV.DataPropertyName = dt.Columns["Price"].ToString();
catGV.DataPropertyName = dt.Columns["Category"].ToString();
catIDGV.DataPropertyName = dt.Columns["Category ID"].ToString();
gv.DataSource = dt;
}
catch (Exception ex)
{
MainClass.showMSG(ex.Message, "Error", "Error");
}
}
public override void BtnSave_Click(object sender, EventArgs e)
{
//validation for Users form
if (nameTxt.Text == string.Empty)
{
nameErrorLabel.Visible = true;
}
else
{
nameErrorLabel.Visible = false;
}
if (userNameTxt.Text == string.Empty)
{
usernameErrorLabel.Visible = true;
}
else
{
usernameErrorLabel.Visible = false;
}
if (pwdTxt.Text == string.Empty)
{
pwdErrorLabel.Visible = true;
}
else
{
pwdErrorLabel.Visible = false;
}
if (phoneTxt.Text == string.Empty)
{
phoneErrorLabel.Visible = true;
}
else
{
phoneErrorLabel.Visible = false;
}
if (emailTxt.Text == string.Empty)
{
emailErrorLabel.Visible = true;
}
else
{
emailErrorLabel.Visible = false;
}
if (statusCb.SelectedIndex == -1)
{
statusErrorLabel.Visible = true;
}
else
{
statusErrorLabel.Visible = false;
}
if (nameErrorLabel.Visible || usernameErrorLabel.Visible || pwdErrorLabel.Visible || phoneErrorLabel.Visible || emailErrorLabel.Visible || statusErrorLabel.Visible)
{
MainClass.showMSG("Fields with * are mandatory", "Stop", "Error"); //Error is the type of message
}
else
{
if (statusCb.SelectedIndex == 0)
{
stat = 1;
}
else if (statusCb.SelectedIndex == 1)
{
stat = 0;
}
if (edit == 0) // Code for SAVE Operation
{
Insertion i = new Insertion();
i.insertUser(nameTxt.Text, userNameTxt.Text, pwdTxt.Text, phoneTxt.Text, emailTxt.Text, stat);
r.showUsers(dataGridView1, UserIDGV, NameGV, UsernameGV, PassGV, PhoneGV, EmailGV, StatusGV);
MainClass.disable_reset(LeftPanel);
}
else if (edit == 1) // Code for EDIT Operation
{
DialogResult dr = MessageBox.Show("Are you sure, You want to Update record?", "Question...", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
Updation u = new Updation();
if (statusCb.SelectedIndex == 0)
{
stat = 1;
}
else if (statusCb.SelectedIndex == 1)
{
stat = 0;
}
u.updateUser(userID, nameTxt.Text, userNameTxt.Text, pwdTxt.Text, phoneTxt.Text, emailTxt.Text, stat);
r.showUsers(dataGridView1, UserIDGV, NameGV, UsernameGV, PassGV, PhoneGV, EmailGV, StatusGV);
MainClass.disable_reset(LeftPanel);
}
}
}
}
public override void BtnSave_Click(object sender, EventArgs e)
{
//validation for Users form
if (proNameTxt.Text == string.Empty)
{
proNameErrorLabel.Visible = true;
}
else
{
proNameErrorLabel.Visible = false;
}
if (barCodeTxt.Text == string.Empty)
{
BarcodeErrorLabel.Visible = true;
}
else
{
BarcodeErrorLabel.Visible = false;
}
if (ExpiryPicker.Value < DateTime.Now)
{
ExpiryErrorLabel.Visible = true; ExpiryErrorLabel.Text = "Invalid Date";
}
else
{
ExpiryErrorLabel.Visible = false;
}
if (ExpiryPicker.Value.Date == DateTime.Now.Date)
{
ExpiryErrorLabel.Visible = false;
}
if (PriceTxt.Text == string.Empty)
{
PriceErrorLabel.Visible = true;
}
else
{
PriceErrorLabel.Visible = false;
}
if (CatDD.SelectedIndex == -1 || CatDD.SelectedIndex == 0)
{
catErrorLabel.Visible = true;
}
else
{
catErrorLabel.Visible = false;
}
if (proNameErrorLabel.Visible || BarcodeErrorLabel.Visible || ExpiryErrorLabel.Visible || PriceErrorLabel.Visible || catErrorLabel.Visible)
{
MainClass.showMSG("Fields with * are mandatory", "Stop", "Error"); //Error is the type of message
}
else
{
if (edit == 0) // Code for SAVE Operation
{
Insertion i = new Insertion();
if (ExpiryPicker.Value.Date == DateTime.Now.Date)
{
i.insertProduct(proNameTxt.Text, barCodeTxt.Text, Convert.ToSingle(PriceTxt.Text), Convert.ToInt32(CatDD.SelectedValue));
}
else
{
i.insertProduct(proNameTxt.Text, barCodeTxt.Text, Convert.ToSingle(PriceTxt.Text), Convert.ToInt32(CatDD.SelectedValue), ExpiryPicker.Value);
}
r.showProducts(dataGridView1, proIDGV, proNameGV, barcodeGV, expiryGV, priceGV, catGV, catIDGV);
MainClass.disable_reset(LeftPanel);
}
else if (edit == 1) // Code for EDIT Operation
{
DialogResult dr = MessageBox.Show("Are you sure, You want to Update record?", "Question...", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
Updation u = new Updation();
if (ExpiryPicker.Value.Date == DateTime.Now.Date)
{
u.updateProduct(proID, proNameTxt.Text, barCodeTxt.Text, Convert.ToSingle(PriceTxt.Text), Convert.ToInt32(CatDD.SelectedValue));
}
else
{
u.updateProduct(proID, proNameTxt.Text, barCodeTxt.Text, Convert.ToSingle(PriceTxt.Text), Convert.ToInt32(CatDD.SelectedValue), ExpiryPicker.Value);
}
r.showProducts(dataGridView1, proIDGV, proNameGV, barcodeGV, expiryGV, priceGV, catGV, catIDGV);
MainClass.disable_reset(LeftPanel);
}
}
}
MainClass.enable_reset(LeftPanel);
}
public override void BtnSave_Click(object sender, EventArgs e)
{
//validation for Users form
if (categoryNameTxt.Text == string.Empty)
{
catNameErrorLabel.Visible = true;
}
else
{
catNameErrorLabel.Visible = false;
}
if (isActiveCB.SelectedIndex == -1)
{
IsActiveErrorLabel.Visible = true;
}
else
{
IsActiveErrorLabel.Visible = false;
}
if (catNameErrorLabel.Visible || IsActiveErrorLabel.Visible)
{
MainClass.showMSG("Fields with * are mandatory", "Stop", "Error"); //Error is the type of message
}
else
{
if (isActiveCB.SelectedIndex == 0)
{
stat = 1;
}
else if (isActiveCB.SelectedIndex == 1)
{
stat = 0;
}
if (edit == 0) // Code for SAVE Operation
{
Insertion i = new Insertion();
i.insertCat(categoryNameTxt.Text, stat);
r.showCategories(CatDataGridView, catIDGV, NameGV, StatusGV);
MainClass.disable_reset(LeftPanel);
}
else if (edit == 1) // Code for EDIT Operation
{
DialogResult dr = MessageBox.Show("Are you sure, You want to Update record?", "Question...", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
Updation u = new Updation();
if (isActiveCB.SelectedIndex == 0)
{
stat = 1;
}
else if (isActiveCB.SelectedIndex == 1)
{
stat = 0;
}
u.updatetCat(catID, categoryNameTxt.Text, stat);
r.showCategories(CatDataGridView, catIDGV, NameGV, StatusGV);
MainClass.disable_reset(LeftPanel);
}
}
}
}
