private void showDataGridView()
{
MySqlConnection con = DBConnection.getConn();
try
{
con.Open();
string query = "Select FullName,FirstName,EmpID,NIC,Address,Shop,Type,Status,ContactNo from employee";
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(query, connectionString);
adapter.Fill(table);
dataGridView1.DataSource = table;
dataGridView1.ReadOnly = true;
dataGridView1.Rows[0].Selected = false;
//dataGridView1.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
}
public static String getQuestion()
{
String question = "";
string query = "Select SecurityQuestion from employee where EmpID = '" + User.LogObj.getUserName() + "'";
MySqlConnection con = DBConnection.getConn();
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
question = reader.GetString(0);
}
}
catch (Exception ex)
{
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
return(question);
}
private void saveBtn_Click(object sender, EventArgs e)
{
MySqlConnection con = DBConnection.getConn();
try
{
string query = "update employee set Address ='" + addressTxt.Text + "',ContactNo='" + contactNoTxt.Text + "',Shop='" + shopCBox.Text + "',Type='" + typeCBox.Text + "',Status='" + statusCbx.Text + "' where EmpID = '" + getEmpID() + "'";
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Update Success!");
groupBox1.Visible = false;
showDataGridView();
selectRaw(getEmpID()); //Select updated row just after saving
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
}
private static bool isPasswordCorrect(String password)
{
bool correct = false;
string query = "Select Password from employee where EmpID ='" + User.LogObj.getUserName() + "'";
MySqlConnection con = DBConnection.getConn();
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.GetString(0) == password)
{
correct = true;
break;
}
}
return(correct);
}
catch (Exception ex)
{
ChangePasswordInterface.showMessage("Catch exception");
return(correct);
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
}
public static void changePassword(String oldPassword, String newPassword, String question, String answer)
{
if (isPasswordCorrect(oldPassword))
{
string query = "update employee set password ='******', SecurityQuestion = '" + question + "', Answer = '" + answer + "' where EmpID = '" + User.LogObj.getUserName() + "'";
MySqlConnection con = DBConnection.getConn();
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();
User.ChangePassObj.clearText();
ChangePasswordInterface.showMessage("Password Changed successfully!");
}
catch (Exception ex)
{
ChangePasswordInterface.showMessage("Error in doing, Please try again later" + ex.Message);
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
}
else
{
ChangePasswordInterface.showMessage("Current Password wrong!");
}
}
public void addUserToDatabase(User user)
{
string query = "insert into employee (EmpID,FullName,FirstName,NIC,Address,ContactNo,Shop,Type,Status,Password) values ('" + user.empID + "','" + user.fullName + "','" + user.firstName + "','" + user.nic + "','" + user.address + "','" + user.contactNo + "','" + user.shop + "','" + user.type + "','" + user.status + "','" + user.password + "')";
MySqlConnection con = DBConnection.getConn();
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
//addemp.clearAllText();
//addemp.showMessage("Successfully added the new Employee!");
User.addEmpObj.clearAllText();
User.addEmpObj.showMessage("Successfully added the new Employee!");
}
catch (Exception ex)
{
User.addEmpObj.showMessage(ex.Message);
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
MySqlConnection con = DBConnection.getConn();
try
{
if (nameTxt.Text != "")
{
nicTxt.Enabled = false;
employeeIdTxt.Enabled = false;
/*con.Open();
* String query = "Select FirstName,LastName,EmpID,NIC,Address,Shop,Type,ContactNo from employee where FirstName like '%" + nameTxt.Text + "%'";
* DataTable table = new DataTable();
* MySqlDataAdapter ada;
* //MySqlCommand cmd;
* for (int i = 0; i < 2; i++)
* {
* //cmd = new MySqlCommand(query, con);
* ada = new MySqlDataAdapter(query, con);
* //ada.SelectCommand = cmd;
* ada.Fill(table);
* //ada.Update(table);
* query = "Select FirstName,LastName,EmpID,NIC,Address,Shop,Type,ContactNo from employee where LastName like '%" + nameTxt.Text + "%'";
* }
* //BindingSource bsource = new BindingSource();
* //bsource.DataSource = table;
* dataGridView1.DataSource = table;*/
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
(dataGridView1.DataSource as DataTable).DefaultView.RowFilter = "FirstName LIKE '" + nameTxt.Text + "%'";
//(dataGridView1.DataSource as DataTable).DefaultView.RowFilter = "LastName LIKE '" + nameTxt.Text + "%'";
//(dataGridView1.DataSource as DataTable).DefaultView.RowFilter = string.Format();
/*foreach (DataGridViewRow row in dataGridView1.Rows)
* {
* if (row.Cells[0].Value.ToString().StartsWith(nameTxt.Text) || row.Cells[1].Value.ToString().StartsWith(nameTxt.Text))
* {
*
* }
* }*/
}
else
{
showDataGridView();
nicTxt.Enabled = true;
employeeIdTxt.Enabled = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
}
private void nicTxt_TextChanged(object sender, EventArgs e)
{
MySqlConnection con = DBConnection.getConn();
try
{
if (nicTxt.Text != "")
{
nameTxt.Enabled = false;
employeeIdTxt.Enabled = false;
/*con.Open();
* String query = "Select FirstName,LastName,EmpID,NIC,Address,Shop,Type,ContactNo from employee where NIC like '%" + nicTxt.Text + "%'";
* //MySqlCommand cmd = new MySqlCommand(query, con);
* MySqlDataAdapter ada = new MySqlDataAdapter(query, con);
* //ada.SelectCommand = cmd;
* DataTable table = new DataTable();
* ada.Fill(table);
* //BindingSource bsource = new BindingSource();
* //bsource.DataSource = table;
* dataGridView1.DataSource = table;
* //ada.Update(table);*/
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
(dataGridView1.DataSource as DataTable).DefaultView.RowFilter = "NIC LIKE '" + nicTxt.Text + "%'";
}
else
{
showDataGridView();
nameTxt.Enabled = true;
employeeIdTxt.Enabled = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
}
public void execute(string username, int shop, DataGridView dataGridView3)
{
DateTime currentDateandTime = DateTime.Now;
string date = "";
string time = "";
date = currentDateandTime.ToShortDateString();
time = currentDateandTime.ToShortTimeString();
MySqlConnection connection = DBConnection.getConn();
connection.Open();
string insertQuery = "INSERT INTO pharmacy_management.return_table VALUES (@invoice_number,@user,@shop,@details,@date,@time)";
MySqlCommand command = new MySqlCommand(insertQuery, connection);
command.Parameters.AddWithValue("@invoice_number", invoice_number);
command.Parameters.AddWithValue("@user", username);
command.Parameters.AddWithValue("@shop", shop);
command.Parameters.AddWithValue("@details", returnDetails);
command.Parameters.AddWithValue("@date", date);
command.Parameters.AddWithValue("@time", time);
command.ExecuteNonQuery();
connection.Close();
foreach (DataGridViewRow row in dataGridView3.Rows)
{
string quantity = row.Cells[3].Value.ToString();
string currentstock = "";
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT stock_shop_" + shop + " FROM pharmacy_management.product WHERE product_code = '" + Convert.ToString(row.Cells[1].Value) + "'";
try
{
connection.Open();
MySqlDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
currentstock = Convert.ToInt32(myReader.GetString("stock_shop_" + shop)).ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
string newstock = Convert.ToString(Convert.ToInt32(currentstock) + Convert.ToInt32(quantity));
string updatequery = "UPDATE pharmacy_management.product SET stock_shop_" + shop + " = '" + newstock + "' WHERE product_code = '" + Convert.ToString(row.Cells[1].Value) + "' ";
MySqlCommand cmdsampleinventory = new MySqlCommand(updatequery, connection);
try
{
connection.Open();
int myread2er = cmdsampleinventory.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
DBConnection.returnConn(connection);
connection = null;
}
public static void loginUsers(String userName, String password)
{
MySqlConnection con = DBConnection.getConn();
try
{
con.Open();
String query = "Select Type,Password,Status,Shop from employee where EmpID = '" + userName + "'";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//string type = reader["Type"].ToString();
string type = reader.GetString(0);
string status = reader.GetString(2);
if (status == "Active")
{
if (reader.GetString(1) == password)
{
User.CurrentUserName = userName;
User.UserShop = reader.GetString(3);
User.UserType = type;
if (type == "Owner")
{
/*AdminInterface owner = new AdminInterface();
* owner.Show();
* this.Hide();*/
Owner.login();
LogObj.Hide();
}
else if (type == "Supervisor")
{
/*SupervisorInterface sp = new SupervisorInterface();
* sp.Show();
* this.Hide();*/
Supervisor.login();
LogObj.Hide();
}
else if (type == "Cashier")
{
/*CashierInterface emp = new CashierInterface();
* emp.Show();
* this.Hide();*/
Cashier.login();
LogObj.Hide();
}
else
{
//nameErLbl.Visible = true;
User.LogObj.displayErr();
}
}
else
{
//nameErLbl.Visible = true;
User.LogObj.displayErr();
User.LogObj.showForgotPassButton();
}
}
else
{
User.LogObj.inactiveErr();
}
}
if (!reader.HasRows)
{
//nameErLbl.Visible = true;
User.LogObj.displayErr();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
con.Close();
DBConnection.returnConn(con);
con = null;
}
}
public void execute(DataGridView datagridview, DataGridView datagridview2, string amount, string user_name, int shop)
{
MySqlConnection connection = DBConnection.getConn();
for (int i = 0; i < datagridview.Rows.Count; i++)
{
string currentstock = "";
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT stock_shop_" + shop + " FROM pharmacy_management.product WHERE product_code = '" + Convert.ToString(datagridview.Rows[i].Cells[1].Value) + "'";
try
{
connection.Open();
MySqlDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
currentstock = Convert.ToString(Convert.ToInt32(myReader.GetString("stock_shop_" + shop)));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
/*string stock_requested = Convert.ToString(datagridview.Rows[i].Cells[3].Value);
* MessageBox.Show(stock_requested);
* MessageBox.Show(currentstock);
* int value = Convert.ToInt32(currentstock) - Convert.ToInt32(stock_requested);
* MessageBox.Show(Convert.ToString(value));*/
string newstock = Convert.ToString(Convert.ToInt32(currentstock) - Convert.ToInt32(datagridview.Rows[i].Cells[3].Value));
string updatequery = "UPDATE pharmacy_management.product SET stock_shop_" + shop + " = '" + newstock + "' WHERE product_code = '" + Convert.ToString(datagridview.Rows[i].Cells[1].Value) + "' ";
MySqlCommand cmdsampleinventory = new MySqlCommand(updatequery, connection);
try
{
connection.Open();
int myread2er = cmdsampleinventory.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
string details = "";
for (int i = 0; i < datagridview.Rows.Count; i++)
{
string product_code = "";
string product = "";
string quantity = "";
string price = "";
string total = "";
product_code = Convert.ToString(datagridview.Rows[i].Cells[1].Value);
product = Convert.ToString(datagridview.Rows[i].Cells[2].Value);
quantity = Convert.ToString(datagridview.Rows[i].Cells[3].Value);
price = Convert.ToString(datagridview.Rows[i].Cells[4].Value);
total = Convert.ToString(datagridview.Rows[i].Cells[5].Value);
string newname = "";
try
{
string[] names = product.Split();
foreach (string item in names)
{
newname += item + "_";
}
}
catch { }
string outcome = newname.Substring(0, newname.Length - 1);
details += product_code + " " + outcome + " " + quantity + " " + price + " " + total + "/";
}
try
{
DateTime current = DateTime.Now;
string date = "";
string time = "";
date = current.ToShortDateString();
time = current.ToShortTimeString();
connection.Open();
string insertquery = "INSERT INTO pharmacy_management.invoice VALUES (null,@user,@shop,@details,@date,@time)";
MySqlCommand command = new MySqlCommand(insertquery, connection);
command.Parameters.AddWithValue("@user", user_name);
command.Parameters.AddWithValue("@shop", shop);
command.Parameters.AddWithValue("@details", details);
command.Parameters.AddWithValue("@date", date);
command.Parameters.AddWithValue("@time", time);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
Invoice frm = new Invoice(user_name);
for (int i = 0; i < datagridview.RowCount; i++)
{
frm.dataGridView3.Rows.Add();
for (int j = 2; j < 6; j++)
{
frm.dataGridView3.Rows[i].Cells[j - 2].Value = datagridview.Rows[i].Cells[j].Value;
//MessageBox.Show(Convert.ToString(this.datagridview.Rows[i].Cells[j].Value));
}
}
MySqlCommand cmd2 = connection.CreateCommand();
cmd2.CommandText = "SELECT * FROM pharmacy_management.invoice WHERE invoice_number = (SELECT MAX(invoice_number) FROM pharmacy_management.invoice)";
string invoice_num = "";
try
{
connection.Open();
MySqlDataReader myReader2 = cmd2.ExecuteReader();
while (myReader2.Read())
{
invoice_num = Convert.ToString(Convert.ToInt32(myReader2.GetString("Invoice_number")));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
frm.invcnumtxtx.Text = invoice_num;
frm.ttlbilltxt.Text = amount;
string selectQuery = "SELECT product_code, name, description, category, stock_shop_" + shop + ",unit_price, re_order_size FROM pharmacy_management.product ";
MySqlDataAdapter adapter = new MySqlDataAdapter(selectQuery, connection);
DataTable dt = new DataTable();
adapter.Fill(dt);
datagridview2.DataSource = dt;
datagridview.Rows.Clear();
frm.Show();
DBConnection.returnConn(connection);
connection = null;
}