public bool InsertCustomer(helperDB cust)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "INSERT INTO tbCustomer VALUES (@FName,@LName,@Cust_Contact)";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@FName", cust.fname);
                cmd.Parameters.AddWithValue("@LName", cust.lname);
                cmd.Parameters.AddWithValue("@Cust_Contact", cust.contact);
                con.Open();

                if (cmd.ExecuteNonQuery() > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }

            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }

            return(isSuccess);
        }
        public bool UpdateProductOrder(helperDB pro)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "spInsertUpdateDeleteProduct";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@P_ID", pro.id);
                cmd.Parameters.AddWithValue("@P_Dec", pro.fname); //fname used as description
                cmd.Parameters.AddWithValue("@P_price", pro.price);
                cmd.Parameters.AddWithValue("@P_quantity", pro.quantity);
                cmd.Parameters.AddWithValue("@StatementType", "Update");
                con.Open();
                if (cmd.ExecuteNonQuery() > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message);
            }
            finally
            {
                con.Close();
            }
            return(isSuccess);
        }
        // helper methods to entertain multitable queries
        public bool InsertUsername(helperDB emp)
        {
            SqlConnection con    = new SqlConnection(myconnstring);
            int           userid = 0;

            try
            {
                con.Open();
                // gets the ID of the employee that is inserted latest
                SqlCommand maxID = new SqlCommand("Select MAX(Emp_ID) from tbEmployee", con);
                userid = (int)maxID.ExecuteScalar();
                // insert in the login table
                String     sql1 = "spInsertUpdateDeleteLogins";
                SqlCommand cmd1 = new SqlCommand(sql1, con);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@emp_id", userid);
                cmd1.Parameters.AddWithValue("@username", emp.username);
                cmd1.Parameters.AddWithValue("@password", emp.fname + emp.contact);
                cmd1.Parameters.AddWithValue("@StatementType", "Insert");

                ///using userid as row checker
                userid = cmd1.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
            return((userid > 0) ? true : false);
        }
        public int selectSupplierID(helperDB delivery)
        {
            SqlConnection con = new SqlConnection(myconnstring);

            try
            {
                SqlCommand cmd = new SqlCommand("Select Supp_ID from tbDelivery WHERE Dev_ID = @devID", con);
                cmd.Parameters.AddWithValue("@devID", delivery.devID);
                con.Open();
                if (cmd.ExecuteScalar() != null)
                {
                    return((int)cmd.ExecuteScalar());
                }
                else
                {
                    return(0);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
            return(0);

            ;
        }
        // devID as custID
        // id as P_ID
        // suppID as empID
        public bool InsertOrderDec(helperDB order)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "INSERT INTO tbOrderDec VALUES (@O_ID,@P_ID,@Quantity)";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@O_ID", order.devID);
                cmd.Parameters.AddWithValue("@P_ID", order.id);
                cmd.Parameters.AddWithValue("@Quantity", order.quantity);
                con.Open();
                if (cmd.ExecuteNonQuery() > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }

            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }

            return(isSuccess);
        }
        public int authenticate(helperDB person)
        {
            SqlConnection con   = new SqlConnection(myconnstring);
            int           found = 0;

            try
            {
                String     sql = "SELECT dbo.fn_Login(@user, @password)";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@user", person.username);
                cmd.Parameters.AddWithValue("@password", person.password);
                con.Open();
                cmd.CommandType = CommandType.Text;
                if (!(cmd.ExecuteScalar() == null))
                {
                    found = (int)cmd.ExecuteScalar();
                }
                else
                {
                    found = 0;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
            return(found);
        }
        public bool deleteProduct(helperDB pro)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "SELECT COUNT([P_ID]) FROM tbOrderDec WHERE([P_ID]) = @P_ID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@P_ID", pro.id);
                con.Open();

                int proExist = (int)cmd.ExecuteScalar();

                if (proExist == 0)
                {
                    try
                    {
                        //delete from emloyee table
                        sql             = "spInsertUpdateDeleteProduct";
                        cmd             = new SqlCommand(sql, con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@P_id", pro.id);
                        cmd.Parameters.AddWithValue("@StatementType", "Delete");
                        int row = cmd.ExecuteNonQuery();

                        // if query executed then value of row is greater than 0
                        if (row > 0)
                        {
                            isSuccess = true;
                        }
                        else
                        {
                            isSuccess = false;
                        }
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
            return(isSuccess);
        }
        public bool deleteSupplier(helperDB supplier)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "SELECT COUNT([Supp_ID]) FROM tbDelivery WHERE([Supp_ID]) = @Supp_ID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@Supp_ID", supplier.id);
                con.Open();
                int SupplerExist = (int)cmd.ExecuteScalar();
                if (SupplerExist == 0)
                {
                    try
                    {
                        String     sql1 = "spInsertUpdateDeleteSupplier";
                        SqlCommand cmd1 = new SqlCommand(sql1, con);
                        cmd1.CommandType = CommandType.StoredProcedure;
                        cmd1.Parameters.AddWithValue("@supp_id", supplier.id);
                        cmd1.Parameters.AddWithValue("@StatementType", "Delete");
                        int row = cmd1.ExecuteNonQuery();
                        // if query executed then value of row is greater than 0
                        if (row > 0)
                        {
                            isSuccess = true;
                        }
                        else
                        {
                            isSuccess = false;
                        }
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
            return(isSuccess);
        }
        private void logIn_button_Click(object sender, EventArgs e)
        {
            helperDB log = new helperDB();

            log.username = UserName_loginForm.Text.Trim(); // removes the spaces at start and end
            log.password = Password_loginForm.Text.Trim();

            loginID = log.authenticate(log); // calling the sql function
            if (loginID != 0)
            {
                if (adminRadiobtn.Checked)
                {
                    this.Hide(); // hides the login form
                    frmMain f1 = new frmMain();
                    f1.Show();
                    f1.controlAccess(false);
                    foreach (Control c in panel1.Controls) // clear the username $ password textbox
                    {
                        if (c is TextBox)
                        {
                            c.Text = "";
                        }
                    }
                }
                else if (userRadioBtn.Checked)
                {
                    this.Hide();
                    frmMain f1 = new frmMain();
                    f1.Show();
                    f1.controlAccess(true);
                    foreach (Control c in panel1.Controls) // clear the username $ password textbox
                    {
                        if (c is TextBox)
                        {
                            c.Text = "";
                        }
                    }
                }
                else
                {
                    MessageBox.Show(panel1, "Please! check whether you are USER or ADMIN !", "Incomplete Input", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            else
            {
                MessageBox.Show(panel1, "Invalid Username or Password !", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        public bool UpdateEmployee(helperDB emp)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "spInsertUpdateDeleteEmployee";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@emp_ID", emp.id);
                cmd.Parameters.AddWithValue("@fname", emp.fname);
                cmd.Parameters.AddWithValue("@lname", emp.lname);
                cmd.Parameters.AddWithValue("@emp_contact", emp.contact);
                cmd.Parameters.AddWithValue("@emp_designation", emp.designation);
                cmd.Parameters.AddWithValue("@StatementType", "Update");
                con.Open();
                int row = cmd.ExecuteNonQuery();
                //update username
                sql             = "spInsertUpdateDeleteLogins";
                cmd             = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@emp_id", emp.id);
                cmd.Parameters.AddWithValue("@username", emp.username);
                int row1 = cmd.ExecuteNonQuery();

                // if query executed then value of row is greater than 0
                if (row > 0 && row1 > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }
            catch (Exception e)
            {
            }
            finally
            {
                con.Close();
            }
            return(isSuccess);
        }
        public bool UpdateProduct(helperDB pro)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "spInsertUpdateDeleteProduct";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@P_ID", pro.id);
                cmd.Parameters.AddWithValue("@P_Dec", pro.fname); //fname used as description
                cmd.Parameters.AddWithValue("@P_price", pro.price);
                cmd.Parameters.AddWithValue("@P_quantity", pro.quantity);
                cmd.Parameters.AddWithValue("@StatementType", "Update");
                con.Open();
                int row1 = cmd.ExecuteNonQuery();

                //update username
                sql = "UPDATE tbDeliveryDec SET pro_quantity = @p_quantity WHERE P_ID = @P_ID";
                cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@P_ID", pro.id);
                cmd.Parameters.AddWithValue("@p_quantity", pro.quantity);
                int row = cmd.ExecuteNonQuery();
                // if query executed then value of row is greater than 0
                if (row > 0 && row1 > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
            return(isSuccess);
        }
        public bool InsertEmployee(helperDB emp)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "spInsertUpdateDeleteEmployee";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@fname", emp.fname);
                cmd.Parameters.AddWithValue("@lname", emp.lname);
                cmd.Parameters.AddWithValue("@emp_contact", emp.contact);
                cmd.Parameters.AddWithValue("@emp_designation", emp.designation);
                cmd.Parameters.AddWithValue("@StatementType", "Insert");
                con.Open();
                int row = cmd.ExecuteNonQuery();
                // if query executed then value of row is greater than 0
                if (row > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }

            return((InsertUsername(emp) && isSuccess) ? true : false);
        }
        // devID as custID
        // id as O_ID
        // suppID as empID
        public bool InsertOrder(helperDB order)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                frmMain    timepick = new frmMain();
                String     sql      = "INSERT INTO tbOrder VALUES (@Cust_ID,@Emp_ID,@Amount,@Date,@Time)";
                SqlCommand cmd      = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@Cust_ID", order.devID);
                cmd.Parameters.AddWithValue("@Emp_ID", order.suppID);
                cmd.Parameters.AddWithValue("@Amount", order.price);
                cmd.Parameters.AddWithValue("@Date", SqlDbType.Date).Value = timepick.dateTimePicker3.Value.Date;
                cmd.Parameters.AddWithValue("@TIME", SqlDbType.Time).Value = DateTime.Now;
                con.Open();
                if (cmd.ExecuteNonQuery() > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }

            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }

            return(isSuccess);
        }
        public bool UpdateSupplier(helperDB supplier)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "spInsertUpdateDeleteSupplier";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Supp_ID", supplier.id);
                cmd.Parameters.AddWithValue("@fname", supplier.fname);
                cmd.Parameters.AddWithValue("@lname", supplier.lname);
                cmd.Parameters.AddWithValue("@supp_contact", supplier.contact);
                cmd.Parameters.AddWithValue("@StatementType", "Update");
                con.Open();
                int row = cmd.ExecuteNonQuery();
                // if query executed then value of row is greater than 0
                if (row > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
            return(isSuccess);
        }
        public bool InsertProduct(helperDB product)
        {
            bool          isSuccess = false;
            SqlConnection con       = new SqlConnection(myconnstring);

            try
            {
                String     sql = "SELECT Dev_ID from tbDelivery WHERE Dev_ID = @devID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@devID", product.devID); // fname used as Product description
                con.Open();


                int row = 0;
                if (cmd.ExecuteScalar() != null)
                {
                    row = (int)cmd.ExecuteScalar();
                }

                if (row == 0)
                {
                    frmMain dateTimeObject = new frmMain();
                    sql = "Insert into tbdelivery VALUES(@dev_ID,@Supp_ID,@Dev_Date,@Dev_TIME)";
                    cmd = new SqlCommand(sql, con);
                    cmd.Parameters.AddWithValue("@dev_ID", product.devID);
                    cmd.Parameters.AddWithValue("@Supp_ID", product.suppID);
                    cmd.Parameters.AddWithValue("@Dev_Date", SqlDbType.Date).Value = dateTimeObject.date_addDetails_inventory_popup_txt.Value.Date;
                    cmd.Parameters.AddWithValue("@Dev_TIME", SqlDbType.Time).Value = DateTime.Now;
                    cmd.ExecuteNonQuery();
                }

                sql             = "spInsertUpdateDeleteProduct";
                cmd             = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@P_dec", product.fname);     // fname used as Product description
                cmd.Parameters.AddWithValue("@P_price", product.price);
                cmd.Parameters.AddWithValue("@P_quantity", product.quantity);
                cmd.Parameters.AddWithValue("@StatementType", "Insert");
                int row2 = cmd.ExecuteNonQuery();

                sql = "Select MAX(P_ID) from tbProduct";
                cmd = new SqlCommand(sql, con);
                int pid = (int)cmd.ExecuteScalar();

                sql             = "spInsertUpdateDeleteDeliveryDec";
                cmd             = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@dev_id", product.devID);
                cmd.Parameters.AddWithValue("@p_id", pid);
                cmd.Parameters.AddWithValue("@quantity", product.quantity);
                cmd.Parameters.AddWithValue("@StatementType", "Insert");
                int row1 = cmd.ExecuteNonQuery();
                if (row1 > 0 && row2 > 0)
                {
                    isSuccess = true;
                }
                else
                {
                    isSuccess = false;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }

            return(isSuccess);
        }