private void SearchFood()
{
txtComment.Text = "";
txtQuantity.Text = "";
lblQuantity.Visible = false;
txtQuantity.Visible = false;
txtComment.Visible = false;
lblComment.Visible = false;
lblExpirationDate.Visible = false;
datePickerExpirationDate.Visible = false;
lblMeasure.Visible = false;
lblFoundFood.Visible = false;
comBFoundFood.Visible = false;
butChechAdd.Visible = false;
butJustAdd.Visible = false;
listVSameFood.Visible = false;
comBFoundFood.Items.Clear();
dataTableFoundFood.Clear();
if (txtFoodToFind.Text.Length > 0)
{
string query;
if (StringCorrect.CheckCorrect(txtFoodToFind.Text, 47, 58)) /// Barcode
{
typeFind = true;
query = "Select Barcode.IdFood, Food.Name, Barcode.ProductInfo, Barcode.Size, Food.Measure, Barcode.Size, Measure.Measure AS 'Mea' from Barcode, Food, Measure where Barcode.IdFood = Food.IdFood AND Measure.IdMeasure = Food.Measure AND Barcode.Barcode = " + txtFoodToFind.Text.ToString().Replace('\'', ' ');
}
else /// Name
{
typeFind = false;
query = "select IdFood, Name, Measure.Measure AS 'Mea' from Food, Measure where Measure.IdMeasure = Food.Measure AND Name like '%" + txtFoodToFind.Text.ToString() + "%'";
}
using (SqlConnection sqlConnection = new SqlConnection(X.ConnectionString("DB")))
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
sqlDataAdapter.Fill(dataTableFoundFood);
}
foreach (DataRow row in dataTableFoundFood.Rows)
{
comBFoundFood.Items.Add(row.Field <string>("Name"));
}
if (comBFoundFood.Items.Count == 0)
{
lblFoundFood.Visible = true;
comBFoundFood.Visible = true;
comBFoundFood.Items.Add("Food not found");
comBFoundFood.SelectedIndex = 0;
return;
}
else if (comBFoundFood.Items.Count > 0) // at least one food was found
{
ChangeFoodComboBox(0);
lblQuantity.Visible = true;
txtQuantity.Visible = true;
txtComment.Visible = true;
lblComment.Visible = true;
lblExpirationDate.Visible = true;
datePickerExpirationDate.Visible = true;
lblMeasure.Visible = true;
lblFoundFood.Visible = true;
comBFoundFood.Visible = true;
butChechAdd.Visible = true;
butJustAdd.Visible = true;
}
}
}
private void TakeOutFood()
{
dataTableFoundFood.Clear();
listVSameFood.Items.Clear();
List <int> ids = new List <int>();
if (txtFoodToFind.Text.Length > 0)
{
string query;
if (StringCorrect.CheckCorrect(txtFoodToFind.Text, 47, 58)) /// Barcode
{
typeFind = true;
query = "SELECT IdFood from Barcode where Barcode = " + txtFoodToFind.Text.ToString();
using (SqlConnection sqlConnection = new SqlConnection(X.ConnectionString("DB")))
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
sqlDataAdapter.Fill(dataTableFoundFood);
}
foreach (DataRow row in dataTableFoundFood.Rows)
{
ids.Add(row.Field <int>("IdFood"));
}
for (int i = 0; i < listVFridge.Items.Count; i++)
{
for (int j = 0; j < ids.Count; j++)
{
if (ids[j] == int.Parse(listVFridge.Items[i].SubItems[8].Text))
{
ListViewItem item = new ListViewItem(listVFridge.Items[i].SubItems[0].Text); //Name
item.SubItems.Add(listVFridge.Items[i].SubItems[1].Text); //Quantity with measure
item.SubItems.Add(listVFridge.Items[i].SubItems[2].Text); //Comment
item.SubItems.Add(listVFridge.Items[i].SubItems[3].Text); //Expiration date
item.SubItems.Add(listVFridge.Items[i].SubItems[4].Text); //IdItemInFridge
item.SubItems.Add(listVFridge.Items[i].SubItems[6].Text); //Quantity no
listVSameFood.Items.Add(item);
break;
}
}
}
}
else /// Name
{
for (int i = 0; i < listVFridge.Items.Count; i++)
{
if (listVFridge.Items[i].SubItems[0].Text.ToLower().Contains(txtFoodToFind.Text.ToLower()))
{
ListViewItem item = new ListViewItem(listVFridge.Items[i].SubItems[0].Text); //Name
item.SubItems.Add(listVFridge.Items[i].SubItems[1].Text); //Quantity
item.SubItems.Add(listVFridge.Items[i].SubItems[2].Text); //Comment
item.SubItems.Add(listVFridge.Items[i].SubItems[3].Text); //Expiration date
item.SubItems.Add(listVFridge.Items[i].SubItems[4].Text); //IdItemInFridge
item.SubItems.Add(listVFridge.Items[i].SubItems[6].Text); //Quantity no
listVSameFood.Items.Add(item);
break;
}
}
}
if (listVSameFood.Items.Count > 0) // at least one food was found
{
this.listVSameFood.Location = new System.Drawing.Point(402, 97);
listVSameFood.Visible = true;
}
takeOutOrAdd = true;
}
}
private void LogIn()
{
if (status) /// login
{
int id;
try
{
using (SqlConnection sqlConnection = new SqlConnection(X.ConnectionString("DB")))
{
SqlCommand sqlCommand = new SqlCommand("select dbo.FindIdLogin(@Login, @Password)", sqlConnection)
{
CommandType = CommandType.Text
};
sqlCommand.Parameters.Add(new SqlParameter("@Login", txtNick.Text.Trim()));
sqlCommand.Parameters.Add(new SqlParameter("@Password", txtPass.Text.Trim()));
sqlConnection.Open();
id = (int)sqlCommand.ExecuteScalar();
sqlConnection.Close();
}
}
catch (Exception) /// Server connection problem
{
MessageBox.Show("Couldn't connect to the server", "Server problem", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (id != 0) ///Correct login
{
X.IdUser = id;
Hide();
Menu menu = new Menu();
menu.ShowDialog();
Close();
}
else /// Wrong login
{
lblLogInfo.Visible = true;
}
string query = "SELECT * FROM Users WHERE Nickname = '" + txtNick.Text.Trim() + "' AND Password = '******'";
DataTable dataTable = new DataTable();
try
{
using (SqlConnection sqlConnection = new SqlConnection(X.ConnectionString("DB")))
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
sqlDataAdapter.Fill(dataTable);
}
}
catch (Exception) /// Server connection problem
{
MessageBox.Show("Couldn't connect to the server", "Server problem", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (dataTable.Rows.Count == 1) ///Correct login
{
X.IdUser = dataTable.Rows[0].Field <int>("IdUsers");
Hide();
Main main = new Main();
main.ShowDialog();
Close();
}
else /// Wrong login
{
lblLogInfo.Visible = true;
}
}
else /// registration
{
lblEmailInfo.Visible = false;
lblUserInfo.Visible = false;
lblPassInfo.Visible = false;
try
{
using (SqlConnection sqlConnection = new SqlConnection(X.ConnectionString("DB")))
{
string query;
SqlDataAdapter sqlDataAdapter;
DataTable dataTable = new DataTable();
///Checking Email
string email = txtEmail.Text.Trim().ToLower();
if (email.Length < 5 || email.IndexOf("@") == -1 || email.IndexOf(".") == -1) /// Wrong email format
{
lblEmailInfo.Text = "Wrong email format";
lblEmailInfo.Visible = true;
}
else
{
query = "SELECT * FROM Users WHERE Email = '" + email + "'";
sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
sqlDataAdapter.Fill(dataTable);
if (dataTable.Rows.Count == 1) /// Email is already used
{
lblEmailInfo.Text = "Email is already used";
lblEmailInfo.Visible = true;
}
}
///Checking Username
string username = txtNick.Text.Trim().ToLower();
if (username.Length < 4) /// Too short username
{
lblUserInfo.Text = "Username has to be minimum 4 letters long";
lblUserInfo.Visible = true;
}
else if (!StringCorrect.CheckCorrect(username, 47, 58, 64, 91, 96, 123))
{
lblUserInfo.Text = "Username can contains only: A-Z, a-z, 0-9";
lblUserInfo.Visible = true;
}
else
{
query = "SELECT * FROM Users WHERE Nickname = '" + txtNick.Text.Trim() + "'";
sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
dataTable.Clear();
sqlDataAdapter.Fill(dataTable);
if (dataTable.Rows.Count == 1) /// Username is already used
{
lblUserInfo.Text = "Username is already used";
lblUserInfo.Visible = true;
}
}
///Checking Password
string password = txtPass.Text.Trim(), passwordConfirm = txtRepPass.Text.Trim();
if (password != passwordConfirm) /// passwords aren't the same
{
lblPassInfo.Text = "Passwords aren't the same";
lblPassInfo.Visible = true;
}
else if (password.Length < 6) /// passwod is too short
{
lblPassInfo.Text = "Password has to be 6 characters long";
lblPassInfo.Visible = true;
}
else if (!StringCorrect.CheckCorrect(password, 32, 128))
{
lblPassInfo.Text = "Password contains unacceptable characters";
lblPassInfo.Visible = true;
}
if (lblEmailInfo.Visible == false && lblUserInfo.Visible == false && lblPassInfo.Visible == false) /// All inputs are correct, adding user to data
{
sqlConnection.Open();
/// Add user to data
SqlCommand sqlAddUserCMD = new SqlCommand("AddUser", sqlConnection)
{
CommandType = CommandType.StoredProcedure
};
sqlAddUserCMD.Parameters.AddWithValue("@Email", email);
sqlAddUserCMD.Parameters.AddWithValue("@Password", password);
sqlAddUserCMD.Parameters.AddWithValue("@Nickname", username);
sqlAddUserCMD.ExecuteNonQuery();
/// Get IdUser
///
SqlCommand sqlCommand = new SqlCommand("select dbo.FindIdLogin(@Login, @Password)", sqlConnection)
{
CommandType = CommandType.Text
};
sqlCommand.Parameters.Add(new SqlParameter("@Login", username));
sqlCommand.Parameters.Add(new SqlParameter("@Password", password));
int id = (int)sqlCommand.ExecuteScalar();
/// Add user fridge data table
SqlCommand sqlCreateFridgeCMD = new SqlCommand("CreateUserFridge", sqlConnection)
{
CommandType = CommandType.StoredProcedure
};
sqlCreateFridgeCMD.Parameters.AddWithValue("@TableName", id);
sqlCreateFridgeCMD.ExecuteNonQuery();
/// Add user diet list data table
SqlCommand sqlCreateDietListCMD = new SqlCommand("CreateUserDietList", sqlConnection)
{
CommandType = CommandType.StoredProcedure
};
sqlCreateDietListCMD.Parameters.AddWithValue("@TableName", id);
sqlCreateDietListCMD.ExecuteNonQuery();
sqlConnection.Close();
if (MessageBox.Show("Would You like to log in now?", "Registration completed", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) /// Log in automaticly after registration
{
X.IdUser = id;
Hide();
Menu menu = new Menu();
menu.ShowDialog();
Close();
}
else /// Don't log in after registration
{
txtEmail.Text = txtNick.Text = txtPass.Text = txtRepPass.Text = "";
}
;
}
}
}
catch (Exception) /// Server connection problem
{
MessageBox.Show("Couldn't connect to the server", "Server problem", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
}
