private void fillDataGrid()
{
this.Enabled = false;
LoadingWindow loadingWindow = new LoadingWindow();
loadingWindow.Show();
Application.DoEvents();
DatabaseControl.ConnectDB();
string sql = "SELECT * FROM recipes ORDER BY title ASC";
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
while (reader.Read())
{
dataGridView1.Rows.Add();
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["id"].Value = reader["id"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Title"].Value = reader["title"];
Bitmap img;
try
{
System.Net.WebRequest request = System.Net.WebRequest.Create(reader["img"].ToString());
request.Timeout = 500;
System.Net.WebResponse response = request.GetResponse();
System.IO.Stream responseStream = response.GetResponseStream();
img = new Bitmap(responseStream);
}
catch (Exception ex)
{
img = null;
}
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Image"].Value = img;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Time"].Value = reader["time"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Difficulty_level"].Value = reader["difficulty_level"];
}
((DataGridViewImageColumn)dataGridView1.Columns[2]).ImageLayout = DataGridViewImageCellLayout.Stretch;
DatabaseControl.DisonnectDB();
this.Enabled = true;
loadingWindow.Close();
}
public MyIngredientsWindow()
{
InitializeComponent();
ToolTip toolTip1 = new ToolTip();
ToolTip toolTip2 = new ToolTip();
toolTip1.SetToolTip(this.addIngredientButton, "Dodaj wiersz do listy");
toolTip1.SetToolTip(this.delIngredientButton, "Usuń wiersz z listy");
foreach (DataGridViewColumn col in dataGridView1.Columns)
{
col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
IngredientsListName = new Dictionary <string, Ingredient>();
IngredientsListId = new Dictionary <string, Ingredient>();
usedIngredientsList = new Dictionary <string, Ingredient>();
DatabaseControl.ConnectDB();
string sql = "SELECT * FROM ingredients ORDER BY name ASC";
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
IngredientsListName.Add(reader["name"].ToString(), new Ingredient(reader["id"].ToString(), reader["name"].ToString(), reader["unit"].ToString()));
IngredientsListId.Add(reader["id"].ToString(), new Ingredient(reader["id"].ToString(), reader["name"].ToString(), reader["unit"].ToString()));
}
sql = "SELECT * FROM my_ingredients";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
reader = command.ExecuteReader();
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
while (reader.Read())
{
Debug.WriteLine("Liczba wierszy: " + dataGridView1.RowCount);
dataGridView1.Rows.Add(reader["id"], new DataGridViewComboBoxCell(), reader["count"], IngredientsListId[reader["id"].ToString()].unit);
DataGridViewComboBoxCell TempComboCell = (DataGridViewComboBoxCell)dataGridView1.Rows[dataGridView1.Rows.Count - 2].Cells["IngridientName"];
TempComboCell.Items.Add(IngredientsListId[reader["id"].ToString()].name);
TempComboCell.Value = IngredientsListId[reader["id"].ToString()].name;
usedIngredientsList.Add(IngredientsListId[reader["id"].ToString()].name, IngredientsListId[reader["id"].ToString()]);
}
DatabaseControl.DisonnectDB();
}
private void acceptButton_Click(object sender, EventArgs e)
{
if (id != -1)
{
if (ingredient_name != ingredientNameTextBox.Text && IsinDB(ingredientNameTextBox.Text))
{
MessageBox.Show("Podany składnik już istnieje!");
return;
}
GC.Collect();
GC.WaitForPendingFinalizers();
DatabaseControl.ConnectDB();
string sql = "UPDATE ingredients SET name = '" + ingredientNameTextBox.Text + "', unit = '" + ingredientUnitTextBox.Text + "' WHERE id = " + id;
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
DatabaseControl.DisonnectDB();
}
else
{
if (IsinDB(ingredientNameTextBox.Text))
{
MessageBox.Show("Podany składnik już istnieje!");
return;
}
GC.Collect();
GC.WaitForPendingFinalizers();
DatabaseControl.ConnectDB();
string sql = "INSERT INTO ingredients (name, unit) VALUES ('" + ingredientNameTextBox.Text + "', '" + ingredientUnitTextBox.Text + "')";
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
string ingredient_id = DatabaseControl.m_dbConnection.LastInsertRowId.ToString();
sql = "ALTER TABLE recipes ADD COLUMN ingredient_" + ingredient_id + " REAL DEFAULT 0.0";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
DatabaseControl.DisonnectDB();
}
edited(true);
this.Close();
}
private void fillDataGrid()
{
DatabaseControl.ConnectDB();
string sql = "SELECT * FROM ingredients ORDER BY name ASC";
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
while (reader.Read())
{
dataGridView1.Rows.Add(reader["id"], reader["name"], reader["unit"]);
}
DatabaseControl.DisonnectDB();
}
private void AcceptButton_Click(object sender, EventArgs e)
{
bool isAllIngredientsFilled = true;
DataGridViewRow Row;
for (int i = 0; i < dataGridView1.RowCount - 1; ++i)
{
Row = dataGridView1.Rows[i];
if (Row.Cells[1].Value == null || Row.Cells[2].Value == null)
{
isAllIngredientsFilled = false;
break;
}
}
if (!isAllIngredientsFilled)
{
MessageBox.Show("UZUPEŁNIJ WSZYSTKIE WARTOŚCI DLA SKŁADNIKÓW!!!");
return;
}
DatabaseControl.ConnectDB();
SQLiteCommand command = new SQLiteCommand("DROP TABLE my_ingredients", DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
command = new SQLiteCommand("CREATE TABLE my_ingredients (id INTEGER NOT NULL," +
"count REAL DEFAULT 0.0)", DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
if (dataGridView1.RowCount > 1)
{
for (int i = 0; i < dataGridView1.RowCount - 1; ++i)
{
Row = dataGridView1.Rows[i];
command = new SQLiteCommand("INSERT INTO my_ingredients (id, count) VALUES (@id, @count)", DatabaseControl.m_dbConnection);
command.Parameters.AddWithValue("@id", Row.Cells[0].Value.ToString());
command.Parameters.AddWithValue("@count", Row.Cells[2].Value.ToString().Replace(',', '.'));
command.ExecuteNonQuery();
}
}
DatabaseControl.DisonnectDB();
this.Close();
}
private void createIngredientsList(Dictionary <string, Ingredient> keyValuePairs)
{
DatabaseControl.ConnectDB();
keyValuePairs.Clear();
string sql = "SELECT * FROM ingredients ORDER BY name ASC";
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
keyValuePairs.Add(reader["name"].ToString(), new Ingredient(reader["id"].ToString(), reader["name"].ToString(), reader["unit"].ToString()));
}
DatabaseControl.DisonnectDB();
}
private bool IsinDB(string name)
{
GC.Collect();
GC.WaitForPendingFinalizers();
DatabaseControl.ConnectDB();
SQLiteCommand command = new SQLiteCommand("SELECT * FROM ingredients WHERE name = @name", DatabaseControl.m_dbConnection);
command.Parameters.AddWithValue("@name", name);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
DatabaseControl.DisonnectDB();
return(true);
}
else
{
DatabaseControl.DisonnectDB();
return(false);
}
}
public IngredientForm(int id = -1)
{
InitializeComponent();
this.id = id;
if (id != -1)
{
DatabaseControl.ConnectDB();
string sql = "SELECT * FROM ingredients WHERE id = " + id;
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
reader.Read();
ingredient_name = reader["name"].ToString();
string ingredient_unit = reader["unit"].ToString();
ingredientNameTextBox.Text = ingredient_name;
ingredientUnitTextBox.Text = ingredient_unit;
DatabaseControl.DisonnectDB();
}
}
private void delButton_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Czy na pewno usunąć przepis?", "Usuń przepis", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
DataGridViewSelectedRowCollection row = dataGridView1.SelectedRows;
string id = row[0].Cells["id"].Value.ToString();
GC.Collect();
GC.WaitForPendingFinalizers();
DatabaseControl.ConnectDB();
string sql = "DELETE FROM recipes WHERE id = " + id.ToString();
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
DatabaseControl.DisonnectDB();
dataGridView1.Rows.RemoveAt(dataGridView1.CurrentCell.RowIndex);
editedRecipesList(true);
}
}
private void delButton_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Czy na pewno usunąć składnik?\nZOSTANĄ USUNIĘTE RÓWNIEŻ PRZEPISY ZAWIERAJĄCE TEN SKŁADNIK ORAZ ELEMENTY Z TWOJEJ LISTY SKŁADNIKÓW!!!", "Usuń składnik", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
DataGridViewSelectedRowCollection row = dataGridView1.SelectedRows;
string id = row[0].Cells["id"].Value.ToString();
GC.Collect();
GC.WaitForPendingFinalizers();
DatabaseControl.ConnectDB();
string sql = "DELETE FROM ingredients WHERE id = " + id.ToString();
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
sql = "DELETE FROM recipes WHERE ingredient_" + id + " <> 0.0";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
sql = "DELETE FROM my_ingredients WHERE id = " + id;
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
string ColumnNames = "pragma table_info(recipes)";
command = new SQLiteCommand(ColumnNames, DatabaseControl.m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
sql = "CREATE TABLE recipes_backup (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"title TEXT NOT NULL, " +
"description TEXT NOT NULL," +
"img TEXT, " +
"time INTEGER NOT NULL, ";
for (int i = 0; i < 6; ++i)
{
reader.Read();
}
sql += reader["name"].ToString() + " INTEGER NOT NULL";
reader.Read();
do
{
if (String.Compare(reader["name"].ToString(), "ingredient_" + id) != 0)
{
sql += ", " + reader["name"].ToString() + " REAL DEFAULT 0.0";
}
} while (reader.Read());
sql += ")";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
command = new SQLiteCommand(ColumnNames, DatabaseControl.m_dbConnection);
reader = command.ExecuteReader();
sql = "INSERT INTO recipes_backup SELECT ";
reader.Read();
sql += reader["name"].ToString();
reader.Read();
do
{
if (String.Compare(reader["name"].ToString(), "ingredient_" + id) != 0)
{
sql += ", " + reader["name"].ToString();
}
} while (reader.Read());
sql += " FROM recipes";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
DatabaseControl.DisonnectDB();
DatabaseControl.ConnectDB();
sql = "DROP TABLE recipes";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
sql = "ALTER TABLE recipes_backup RENAME TO recipes";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
DatabaseControl.DisonnectDB();
//fillDataGrid();
dataGridView1.Rows.RemoveAt(dataGridView1.CurrentCell.RowIndex);
editedRecipesWindow(true);
}
}
public ViewRecipeWindow(int id = -1)
{
InitializeComponent();
foreach (DataGridViewColumn col in dataGridView1.Columns)
{
col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
this.id = id;
DatabaseControl.ConnectDB();
IngredientsList = new Dictionary <string, Ingredient>();
Dictionary <string, double> MyIngredientsList = new Dictionary <string, double>();
string sql = "SELECT * FROM my_ingredients";
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
MyIngredientsList.Add(reader["id"].ToString(), double.Parse(reader["Count"].ToString()));
}
sql = "SELECT * FROM ingredients ORDER BY name ASC";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
reader = command.ExecuteReader();
while (reader.Read())
{
IngredientsList.Add(reader["id"].ToString(), new Ingredient(reader["id"].ToString(), reader["name"].ToString(), reader["unit"].ToString()));
}
sql = "SELECT * FROM recipes WHERE id = " + id;
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
reader = command.ExecuteReader();
reader.Read();
TitleBox.Text = reader["title"].ToString();
DescriptionBox.Text = reader["description"].ToString();
TimeLabel.Text = "Czas przygotowania: " + reader["time"] + " min";
DifficultyLevelLabel.Text = "Poziom trudności: " + reader["difficulty_level"];
for (int i = 6; i < reader.FieldCount; ++i)
{
if (!string.Equals(reader[i].ToString(), "0.0") && !string.Equals(reader[i].ToString(), "0"))
{
string tempId = ColumnNameToId(reader.GetName(i));
Ingredient tempIngredient = IngredientsList[tempId];
dataGridView1.Rows.Add(tempIngredient.name, reader[i].ToString(), tempIngredient.unit);
if (MyIngredientsList.ContainsKey(tempId))
{
if (double.Parse(reader[i].ToString()) > MyIngredientsList[tempId])
{
dataGridView1.Rows[dataGridView1.RowCount - 1].DefaultCellStyle.BackColor = Color.Red;
dataGridView1.Rows[dataGridView1.RowCount - 1].Cells["Count"].Value = reader[i].ToString() + " (-" + (double.Parse(reader[i].ToString()) - MyIngredientsList[tempId]).ToString() + ")";
}
}
else
{
dataGridView1.Rows[dataGridView1.RowCount - 1].DefaultCellStyle.BackColor = Color.Red;
dataGridView1.Rows[dataGridView1.RowCount - 1].Cells["Count"].Value = reader[i].ToString() + " (-" + reader[i].ToString() + ")";
}
}
}
Bitmap img;
try
{
System.Net.WebRequest request = System.Net.WebRequest.Create(reader["img"].ToString());
request.Timeout = 2000;
System.Net.WebResponse response = request.GetResponse();
System.IO.Stream responseStream = response.GetResponseStream();
img = new Bitmap(responseStream);
ImgBox.Image = img;
}
catch (Exception ex)
{
//img = null;
}
DatabaseControl.DisonnectDB();
}
private void acceptButton_Click(object sender, EventArgs e)
{
bool isAllIngredientsFilled = true;
DataGridViewRow Row;
for (int i = 0; i < dataGridView1.RowCount - 1; ++i)
{
Row = dataGridView1.Rows[i];
if (Row.Cells[1].Value == null || Row.Cells[2].Value == null)
{
isAllIngredientsFilled = false;
break;
}
}
if (!isAllIngredientsFilled)
{
MessageBox.Show("UZUPEŁNIJ WSZYSTKIE WARTOŚCI DLA SKŁADNIKÓW!!!");
return;
}
DatabaseControl.ConnectDB();
if (id != -1)
{
}
else
{
SQLiteCommand command = new SQLiteCommand("INSERT INTO recipes (title, description, img, time, difficulty_level) VALUES (@title, @description, @img, @time, @difficulty_level)", DatabaseControl.m_dbConnection);
command.Parameters.AddWithValue("@title", TitleBox.Text);
command.Parameters.AddWithValue("@description", DescriptionBox.Text);
command.Parameters.AddWithValue("@img", ImgBox.Text == "www.jakasstrona.pl/obrazek.jpg" ? "" : ImgBox.Text);
command.Parameters.AddWithValue("@time", TimeBox.Text);
command.Parameters.AddWithValue("@difficulty_level", DifficultyLevelBox.Text);
command.ExecuteNonQuery();
Debug.WriteLine("ID: " + DatabaseControl.m_dbConnection.LastInsertRowId);
if (dataGridView1.RowCount > 1)
{
string sql = "UPDATE recipes SET ";
Row = dataGridView1.Rows[0];
sql += "ingredient_" + Row.Cells[0].Value.ToString() + " = " + Row.Cells[2].Value.ToString().Replace(',', '.');
for (int i = 1; i < dataGridView1.RowCount - 1; ++i)
{
Row = dataGridView1.Rows[i];
sql += ", ingredient_" + Row.Cells[0].Value.ToString() + " = " + Row.Cells[2].Value.ToString().Replace(',', '.');
}
sql += " WHERE id = " + DatabaseControl.m_dbConnection.LastInsertRowId;
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
command.ExecuteNonQuery();
}
edited(true);
}
DatabaseControl.DisonnectDB();
this.Close();
}
private void runAlgorithmButton_Click(object sender, EventArgs e)
{
Dictionary <string, string> MyIngredientsList = new Dictionary <string, string>();
this.Enabled = false;
LoadingWindow loadingWindow = new LoadingWindow();
loadingWindow.Show();
Application.DoEvents();
DatabaseControl.ConnectDB();
string sql = "SELECT * FROM my_ingredients";
SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
MyIngredientsList.Add(reader["id"].ToString(), reader["count"].ToString());
}
DatabaseControl.DisonnectDB();
DatabaseControl.ConnectDB();
string ColumnNames = "pragma table_info(recipes)";
command = new SQLiteCommand(ColumnNames, DatabaseControl.m_dbConnection);
reader = command.ExecuteReader();
sql = "SELECT * FROM recipes WHERE ";
for (int i = 0; i < 7; ++i)
{
reader.Read();
}
if (reader.HasRows)
{
string IngredientColumn = reader["name"].ToString();
if (MyIngredientsList.ContainsKey(ColumnNameToId(IngredientColumn)))
{
sql += IngredientColumn + " <= " + MyIngredientsList[ColumnNameToId(IngredientColumn)].Replace(',', '.');
}
else
{
sql += IngredientColumn + " = 0.0";
}
while (reader.Read())
{
IngredientColumn = reader["name"].ToString();
if (MyIngredientsList.ContainsKey(ColumnNameToId(IngredientColumn)))
{
sql += " AND " + IngredientColumn + " <= " + MyIngredientsList[ColumnNameToId(IngredientColumn)].Replace(',', '.');
}
else
{
sql += " AND " + IngredientColumn + " = 0.0";
}
}
sql += " ORDER BY title ASC";
command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection);
reader = command.ExecuteReader();
if (reader.HasRows)
{
dataGridView1.Rows.Clear();
while (reader.Read())
{
Bitmap img;
try
{
System.Net.WebRequest request = System.Net.WebRequest.Create(reader["img"].ToString());
request.Timeout = 500;
System.Net.WebResponse response = request.GetResponse();
System.IO.Stream responseStream = response.GetResponseStream();
img = new Bitmap(responseStream);
}
catch (Exception ex)
{
img = null;
}
dataGridView1.Rows.Add();
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["id"].Value = reader["id"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Title"].Value = reader["title"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Image"].Value = img;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Time"].Value = reader["time"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Difficulty_level"].Value = reader["difficulty_level"];
}
((DataGridViewImageColumn)dataGridView1.Columns[2]).ImageLayout = DataGridViewImageCellLayout.Stretch;
dataGridView1.Refresh();
this.Enabled = true;
loadingWindow.Close();
}
else
{
this.Enabled = true;
loadingWindow.Close();
dataGridView1.Rows.Clear();
MessageBox.Show("Nie znaleziono żadnych przepisów!", "Brak przepisów", MessageBoxButtons.OK);
}
DatabaseControl.DisonnectDB();
}
else
{
this.Enabled = true;
loadingWindow.Close();
dataGridView1.Rows.Clear();
this.Show();
MessageBox.Show("Nie znaleziono żadnych przepisów!", "Brak przepisów", MessageBoxButtons.OK);
}
}
else
{
DatabaseControl.DisonnectDB();
DatabaseControl.ConnectDB();
this.Enabled = true;
loadingWindow.Close();
dataGridView1.Rows.Clear();
MessageBox.Show("Nie posiadasz żadnych składników!", "Brak składników", MessageBoxButtons.OK);
}
}
