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;
            }
        }
Ejemplo n.º 2
0
        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;
            }
        }
Ejemplo n.º 4
0
        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;
            }
        }
Ejemplo n.º 5
0
 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!");
     }
 }
Ejemplo n.º 6
0
        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;
            }
        }
Ejemplo n.º 9
0
        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;
        }
Ejemplo n.º 10
0
        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;
            }
        }
Ejemplo n.º 11
0
        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;
        }