private void UpdateButton_Click(object sender, EventArgs e)
{
const string errorMessage = "A supplier with this name doesn't exist.\n\nPlease enter a different name.";
_connection = DB_Connect.connect();
_connection.Open();
if (IfSupplierExists(_connection, SNTextBox.Text))
{
var sqlQuery = @"UPDATE[Suppliers] SET[SupplierName] = '" + SNTextBox.Text + "', [SupplierAddress] = '" + SATextBox.Text + "' , [SupplierNumber] = '" + SNUTextBox.Text + "', [SupplierEmail] = '" + SETextBox.Text + "' WHERE[SupplierName] = '" + SNTextBox.Text + "'";
ClearText();
SNTextBox.Focus();
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
}
else
{
MessageBox.Show(errorMessage);
SNTextBox.Clear();
SNTextBox.Focus();
}
_connection.Close();
LoadData();
}
public void LoadData()
{
FillComboBox();
FillComboBox2();
_connection = DB_Connect.connect();
_sda = new SqlDataAdapter(@"SELECT * FROM[Products] order by len(ProductCode), ProductCode", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView2.Rows.Clear();
foreach (DataRow item in _dt.Rows)
{
var n = dataGridView2.Rows.Add();
dataGridView2.Rows[n].Cells[0].Value = item["ProductCode"].ToString();
dataGridView2.Rows[n].Cells[1].Value = item["ProductName"].ToString();
dataGridView2.Rows[n].Cells[2].Value = item["ProductSupplier"].ToString();
dataGridView2.Rows[n].Cells[3].Value = item["ProductSupplierCode"].ToString();
dataGridView2.Rows[n].Cells[4].Value = item["ProductPrice"].ToString();
dataGridView2.Rows[n].Cells[5].Value = item["ProductStock"].ToString();
}
_sda = new SqlDataAdapter(@"SELECT * FROM[Cart]", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView1.Rows.Clear();
foreach (DataRow item in _dt.Rows)
{
var n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item["ProductCode"].ToString();
dataGridView1.Rows[n].Cells[1].Value = item["Quantity"].ToString();
}
}
private void Button1_Click(object sender, EventArgs e)
{
var error = "Passwords do not match. Please try again.";
var error2 = "Password does not contain at least 6 letters. Please try again.";
var success = "Password successfully changed!";
if (textBox1.Text == textBox2.Text)
{
if (textBox1.Text.Length < 6)
{
MessageBox.Show(error2);
}
else
{
_connection = DB_Connect.connect();
_connection.Open();
var sqlQuery = @"UPDATE[Users] SET[Password] = '" + textBox2.Text + "' WHERE [UserName] = '" + Login.UserName + "'";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
_connection.Close();
Close();
MessageBox.Show(success);
}
}
else
{
MessageBox.Show(error);
}
}
private void AddButton_Click(object sender, EventArgs e)
{
const string errorMessage = "A supplier with this name already exists.\n\nPlease enter a different name.";
_connection = DB_Connect.connect();
_connection.Open();
if (IfSupplierExists(_connection, SNTextBox.Text))
{
MessageBox.Show(errorMessage);
SNTextBox.Clear();
SNTextBox.Focus();
}
else
{
var sqlQuery = @"INSERT INTO[Suppliers] ([SupplierName], [SupplierAddress], [SupplierNumber], [SupplierEmail]) VALUES
('" + SNTextBox.Text + "', '" + SATextBox.Text + "', '" + SNUTextBox.Text + "', '" + SETextBox.Text + "')";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
ClearText();
SNTextBox.Focus();
}
_connection.Close();
LoadData();
}
private void AddButton_Click(object sender, EventArgs e)
{
const string errorMessage = "An item with this product code already exists.\n\nPlease enter a different product code.";
_connection = DB_Connect.connect();
_connection.Open();
if (IfProductExists(_connection, PCTextBox.Text))
{
MessageBox.Show(errorMessage);
PCTextBox.Clear();
PCTextBox.Focus();
}
else
{
var sqlQuery = @"INSERT INTO[Products] ([ProductCode], [ProductName], [ProductSupplier], [ProductSupplierCode], [ProductPrice], [ProductStock]) VALUES
('" + PCTextBox.Text + "', '" + PNTextBox.Text + "', '" + SNTextBox.Text + "', '" + SCTextBox.Text + "', '" + PriceTextBox.Text + "', '" + StockTextBox.Text + "')";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
ClearText();
PCTextBox.Focus();
}
_connection.Close();
LoadData();
}
private void UpdateButton_Click(object sender, EventArgs e)
{
const string errorMessage = "An item with this product code doesn't exist.\n\nPlease enter a different product code.";
_connection = DB_Connect.connect();
_connection.Open();
if (IfProductExists(_connection, PCTextBox.Text))
{
var sqlQuery = @"UPDATE[Products] SET[ProductName] = '" + PNTextBox.Text + "', [ProductSupplier] = '" + SNTextBox.Text + "' , [ProductSupplierCode] = '" + SCTextBox.Text + "', [ProductPrice] = '" + PriceTextBox.Text + "', [ProductStock] = '" + StockTextBox.Text + "' WHERE[ProductCode] = '" + PCTextBox.Text + "'";
ClearText();
PCTextBox.Focus();
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
}
else
{
MessageBox.Show(errorMessage);
PCTextBox.Clear();
PCTextBox.Focus();
}
_connection.Close();
LoadData();
}
public void CheckLogInAuthenticity(string userName, string passWord)
{
var errorMessage1 = "Invalid login details supplied.\n\nPlease try again.";
var error = "Error";
var localDate = DateTime.Now;
LogInTime = localDate;
_connection = DB_Connect.connect();
// Handles connection.Open() && connection.Close()
_sda = new SqlDataAdapter(@"SELECT * FROM [FYP_DB].[dbo].[Users]
Where UserName = '******' and Password = '******'", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
if (_dt.Rows.Count == 1)
{
UserName = userName;
PassWord = passWord;
SetAccessLevel();
MakeRecordOfLogin();
}
else
{
MessageBox.Show(errorMessage1, error);
}
}
private void CreateOrder_FormClosing(object sender, FormClosingEventArgs e)
{
_connection = DB_Connect.connect();
_connection.Open();
_command = new SqlCommand("SELECT * FROM[Cart] DELETE FROM[Cart]", _connection);
_command.ExecuteNonQuery();
_connection.Close();
}
private void ClearButton_Click(object sender, EventArgs e)
{
_connection = DB_Connect.connect();
_connection.Open();
dataGridView1.Rows.Clear();
var sqlQuery = @"SELECT *FROM[Cart] DELETE FROM[Cart]";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
_connection.Close();
}
private void RecordLogOut()
{
var localDate = DateTime.Now;
var userName = Login.UserName;
var logInTime = Login.LogInTime;
_connection = DB_Connect.connect();
_connection.Open();
_command = new SqlCommand(@"UPDATE[Active Users] SET[LoggedOutAt] = '" + localDate + "' WHERE[Username] = '" + userName + "' AND [LoggedInAt] = '" + logInTime + "'", _connection);
_command.ExecuteNonQuery();
_connection.Close();
}
private void DeleteButton_Click(object sender, EventArgs e)
{
_connection = DB_Connect.connect();
_connection.Open();
var sqlQuery = @"DELETE FROM[Users] WHERE[UserName] = '" + UNcb.Text + "'";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
_connection.Close();
UNcb.Focus();
LoadData();
}
private void Button1_Click(object sender, EventArgs e)
{
_connection = DB_Connect.connect();
_connection.Open();
var sqlQuery = @"DELETE FROM[Orders] WHERE[OrderID] = '" + dataGridView2.Rows[0].Cells[0].Value + "'";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
_connection.Close();
foreach (DataGridViewRow row in dataGridView2.SelectedRows)
{
dataGridView2.Rows.RemoveAt(row.Index);
}
}
private void AddButton_Click(object sender, EventArgs e)
{
const string error = "You have provided invalid details. Please try again.";
var quantity = Convert.ToInt32(QuantityUpDown.Text);
var prodCode = ProductCodeComboBox.Text;
_connection = DB_Connect.connect();
_connection.Open();
_sda = new SqlDataAdapter(@"SELECT * FROM[Products] WHERE[ProductCode] = '" + prodCode + "'", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView1.Rows.Clear();
if (_dt.Rows.Count == 1 && quantity != 0)
{
foreach (DataRow unused in _dt.Rows)
{
NumOfItems = NumOfItems + 1;
}
_command = new SqlCommand(@"INSERT INTO[Ordered Items]([OrderID], [ProductCode], [Quantity]) VALUES
('" + _randCode + "', '" + prodCode + "', '" + quantity + "')", _connection);
_command.ExecuteNonQuery();
_command = new SqlCommand(@"INSERT INTO[Cart]([ProductCode], [Quantity]) VALUES
('" + prodCode + "', '" + quantity + "')", _connection);
_command.ExecuteNonQuery();
_sda = new SqlDataAdapter(@"SELECT * FROM[Cart]", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView1.Rows.Clear();
foreach (DataRow item in _dt.Rows)
{
var n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item["ProductCode"].ToString();
dataGridView1.Rows[n].Cells[1].Value = item["Quantity"].ToString();
}
}
else
{
MessageBox.Show(error);
QuantityUpDown.Value = 0;
QuantityUpDown.Focus();
}
_sda.Dispose();
_connection.Close();
}
public void SimulateProduction()
{
var random = new Random();
for (int n = 0; n < 4; n++)
{
var randomNumber = random.Next(1, GetTableSize() + 1);
_connection = DB_Connect.connect();
_connection.Open();
_command = new SqlCommand(@"UPDATE[Products] SET[ProductStock] -= 1 WHERE[ProductCode] = + '" + randomNumber + "' AND [ProductStock] > 0", _connection);
_command.ExecuteNonQuery();
_connection.Close();
_sda = new SqlDataAdapter(@"SELECT * FROM[Products] WHERE[ProductCode] = + '" + randomNumber + "'", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
}
}
public void LoadData()
{
_connection = DB_Connect.connect();
_sda = new SqlDataAdapter(@"SELECT * FROM[Suppliers] order by SupplierName", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView1.Rows.Clear();
foreach (DataRow item in _dt.Rows)
{
var n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item["SupplierName"].ToString();
dataGridView1.Rows[n].Cells[1].Value = item["SupplierAddress"].ToString();
dataGridView1.Rows[n].Cells[2].Value = item["SupplierNumber"].ToString();
dataGridView1.Rows[n].Cells[3].Value = item["SupplierEmail"].ToString();
}
}
private void FillDataGridView()
{
_connection = DB_Connect.connect();
_connection.Open();
_sda = new SqlDataAdapter(@"SELECT * FROM[Ordered Items] order by len(OrderID), OrderID", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView2.Rows.Clear();
foreach (DataRow item in _dt.Rows)
{
var n = dataGridView2.Rows.Add();
dataGridView2.Rows[n].Cells[0].Value = item["OrderID"].ToString();
dataGridView2.Rows[n].Cells[1].Value = item["ProductCode"].ToString();
dataGridView2.Rows[n].Cells[2].Value = item["Quantity"].ToString();
}
}
private void DeleteButton_Click(object sender, EventArgs e)
{
_connection = DB_Connect.connect();
_connection.Open();
foreach (DataGridViewRow row in dataGridView1.SelectedRows)
{
if (row.Index >= 0)
{
dataGridView1.Rows.RemoveAt(row.Index);
_command = new SqlCommand(@"DELETE FROM[Cart] WHERE[ProductCode] = '" + dataGridView1.Rows[0].Cells[0].Value + "'", _connection);
_command.ExecuteNonQuery();
_command = new SqlCommand(@"DELETE FROM[OrderItems] WHERE[ProductCode] = '" + dataGridView1.Rows[0].Cells[0].Value + "'", _connection);
_command.ExecuteNonQuery();
}
}
_connection.Close();
}
private void FilterMenuFeatures()
{
_connection = DB_Connect.connect();
_connection.Open();
_sda = new SqlDataAdapter(@"SELECT * FROM[Orders] order by len(OrderDate), OrderDate", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView2.Rows.Clear();
foreach (DataRow item in _dt.Rows)
{
int n = dataGridView2.Rows.Add();
dataGridView2.Rows[n].Cells[0].Value = item["OrderID"].ToString();
dataGridView2.Rows[n].Cells[1].Value = item[Constants.req].ToString();
dataGridView2.Rows[n].Cells[2].Value = item["OrderStatus"].ToString();
dataGridView2.Rows[n].Cells[3].Value = item["OrderDate"].ToString();
}
if (Login.AccessLevel == 3)
{
MenuFeatureChange(false);
}
else
{
if (Login.AccessLevel == 2)
{
manageOrdersToolStripMenuItem.Visible = false;
accountManagementToolStripMenuItem.Visible = false;
}
_sda = new SqlDataAdapter(@"SELECT [ProductCode], [ProductStock] FROM[Products] WHERE [ProductStock] < 10 order by len(ProductCode), ProductCode", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView1.Rows.Clear();
foreach (DataRow item in _dt.Rows)
{
int n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item["ProductCode"].ToString();
dataGridView1.Rows[n].Cells[1].Value = item["ProductStock"].ToString();
}
}
_connection.Close();
}
private void LogItemButton_Click(object sender, EventArgs e)
{
_newQuantity = _currQuantity - (Convert.ToSingle(QuantityUpDown.Text));
_connection = DB_Connect.connect();
_connection.Open();
var sqlQuery = @"UPDATE [Ordered Items] SET [Quantity] = '" + _newQuantity + "' WHERE[ProductCode] = '" + PCComboBox.Text + "' AND [OrderID] = '" + OIDComboBox.Text + "'";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
sqlQuery = @"DELETE FROM [Orders] WHERE [NumberOfItems] = 0 ";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
sqlQuery = @"DELETE FROM [Ordered Items] WHERE [Quantity] = 0 ";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
_connection.Close();
FillDataGridView();
}
public void LoadData()
{
FillComboBox2();
_connection = DB_Connect.connect();
_sda = new SqlDataAdapter(@"SELECT * FROM[Users] order by UserName", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView1.Rows.Clear();
foreach (DataRow item in _dt.Rows)
{
var n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item["UserName"].ToString();
dataGridView1.Rows[n].Cells[1].Value = item[Constants.dep].ToString();
dataGridView1.Rows[n].Cells[2].Value = item["AccessLevel"].ToString();
dataGridView1.Rows[n].Cells[3].Value = item["Password"].ToString();
}
}
private void FillComboBox2()
{
_connection = DB_Connect.connect();
_command = new SqlCommand(@"SELECT [UserName] FROM[Users]", _connection);
try
{
_connection.Open();
var myReader = _command.ExecuteReader();
while (myReader.Read())
{
UNcb.Items.Add(myReader.GetString(0));
}
_connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void UpdateButton_Click(object sender, EventArgs e)
{
var error = "Password entered is too short. Please enter a password with 6 characters or more.";
_connection = DB_Connect.connect();
_connection.Open();
if (PWTextBox.Text.Length > 1 && PWTextBox.Text.Length < 6)
{
MessageBox.Show(error);
}
else
{
var sqlQuery = @"UPDATE[Users] SET[Password] = '" + PWTextBox.Text + "', [AccessLevel] = '" + ALcb.Text + "' , [Department] = '" + DEPcb.Text + "' WHERE[UserName] = '" + UNcb.Text + "'";
UNcb.Focus();
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
_connection.Close();
LoadData();
}
}
private void PCComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
_connection = DB_Connect.connect();
_command = new SqlCommand(@"SELECT [Quantity] FROM[Ordered Items] WHERE [OrderID] = '" + OIDComboBox.Text + "' AND [ProductCode] = '" + PCComboBox.Text + "'", _connection);
try
{
_connection.Open();
var myReader = _command.ExecuteReader();
while (myReader.Read())
{
QuantityUpDown.Maximum = new decimal(myReader.GetDouble(0));
_currQuantity = myReader.GetDouble(0);
}
_connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FillComboBox()
{
_connection = DB_Connect.connect();
_command = new SqlCommand(@"SELECT DISTINCT [OrderID] FROM[Ordered Items]", _connection);
try
{
_connection.Open();
var myReader = _command.ExecuteReader();
while (myReader.Read())
{
var _Oid = myReader.GetString(0);
OIDComboBox.Items.Add(_Oid);
}
_connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FillComboBox2()
{
_connection = DB_Connect.connect();
_command = new SqlCommand(@"SELECT * FROM[Department]", _connection);
try
{
_connection.Open();
var myReader = _command.ExecuteReader();
while (myReader.Read())
{
var x = myReader.GetString(0);
DepComboBox.Items.Add(x);
}
_connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void AddAccount()
{
string name = FNTextBox.Text + " " + SNTextBox.Text;
string x = GenerateUN(FNTextBox.Text.ToLower(), DEPcb.Text.ToUpper());
var success = "You have successfully created an account!\n\nYour username is : " + x + "\nYour password is : default\n\nThis can be changed when you log in for the first time.";
_connection = DB_Connect.connect();
_connection.Open();
var sqlQuery = @"INSERT INTO[Users] ([Name], [Department], [UserName], [Password], [AccessLevel]) VALUES
('" + name + "', '" + DEPcb.Text + "', '" + x + "', '" + "default" + "', '" + ALcb.Text + "')";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
FNTextBox.Clear();
SNTextBox.Clear();
DEPcb.SelectedIndex = -1;
ALcb.SelectedIndex = -1;
FNTextBox.Focus();
MessageBox.Show(success);
_connection.Close();
}
private void SetAccessLevel()
{
_connection = DB_Connect.connect();
_command = new SqlCommand(@"SELECT * FROM[Users] WHERE [Username] = '" + UserName + "'", _connection);
try
{
_connection.Open();
var myReader = _command.ExecuteReader();
while (myReader.Read())
{
AccessLevel = myReader.GetInt32(4);
Forename = myReader.GetString(0);
Forename = Forename.Substring(0, Forename.IndexOf(' ') + 1);
}
_connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Load_Data()
{
OrderIDComboBox.Items.Clear();
_connection = DB_Connect.connect();
_sda = new SqlDataAdapter(@"SELECT * FROM[Orders]", _connection);
_dt = new DataTable();
_sda.Fill(_dt);
dataGridView2.Rows.Clear();
//TimeLabel.Location = new Point(914, 498);
FillComboBox(_connection);
foreach (DataRow item in _dt.Rows)
{
int n = dataGridView2.Rows.Add();
dataGridView2.Rows[n].Cells[0].Value = item["OrderID"].ToString();
dataGridView2.Rows[n].Cells[1].Value = item["NumberOFItems"].ToString();
dataGridView2.Rows[n].Cells[2].Value = item[Constants.dep].ToString();
dataGridView2.Rows[n].Cells[3].Value = item[Constants.req].ToString();
dataGridView2.Rows[n].Cells[4].Value = item["OrderDate"].ToString();
dataGridView2.Rows[n].Cells[5].Value = item["OrderStatus"].ToString();
dataGridView2.Rows[n].Cells[6].Value = item["OrderTotal"].ToString();
}
}
private void OIDComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
PCComboBox.Items.Clear();
_connection = DB_Connect.connect();
_command = new SqlCommand(@"SELECT * FROM[Ordered Items] WHERE [OrderID] = '" + OIDComboBox.Text + "'", _connection);
try
{
_connection.Open();
var myReader = _command.ExecuteReader();
while (myReader.Read())
{
var pCode = myReader.GetString(1);
_newQuantity = myReader.GetDouble(2);
PCComboBox.Items.Add(pCode);
}
_connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void DeleteButton_Click(object sender, EventArgs e)
{
const string errorMessage = "This item does not exist!";
_connection = DB_Connect.connect();
if (IfProductExists(_connection, PCTextBox.Text))
{
_connection.Open();
var sqlQuery = @"DELETE FROM[Products] WHERE[ProductCode] = '" + PCTextBox.Text + "'";
_command = new SqlCommand(sqlQuery, _connection);
_command.ExecuteNonQuery();
_connection.Close();
}
else
{
MessageBox.Show(errorMessage);
}
ClearText();
PCTextBox.Focus();
LoadData();
}