/// <summary>
/// constructor for loading an existing recipie
/// </summary>
/// <param name="current"></param>
public FrmRecipie(Recipie current)
{
//setup basic form
InitializeComponent();
updateLbItemSearch(Program.items.Values);
setProfessionAndLevel();
//load data for current recipie
curRecipie = current;
this.Text += current.id;
cbProfession.Text = current.profession;
cbCrafterLevel.Text = current.crafterLevel;
result = current.result;
txtResultName.Text = result.name;
nudNumProduced.Value = current.resultQty;
nudHours.Value = current.hours;
lvIngredients.SuspendLayout();
foreach (InventoryItem iq in current.ingredients)
{
addIngredient(iq.item, iq.qty);
}
lvIngredients.ResumeLayout();
}
/// <summary>
/// Loads all the items and recipies from the database
/// </summary>
public static void loadAllFromDB()
{
items = new Dictionary <int, Item>();
recipies = new List <Recipie>();
FrmLoading.setText("Connecting to DB...");
using (SqlConnection dataConnection = new SqlConnection(Program.connectionString))
{
dataConnection.Open();
FrmLoading.setText("Loading Items from DB...");
//load loot
DataSet dsItems = new DataSet();
string selectLoot = @"SELECT *
FROM items
ORDER BY id" ;
using (SqlDataAdapter da = new SqlDataAdapter(selectLoot, dataConnection))
{
da.Fill(dsItems);
}
foreach (DataRow row in dsItems.Tables[0].Rows)
{
Item item = new Item();
item.id = (int)row["id"];
item.name = (string)row["name"];
item.cost = (int)row["cost"];
item.description = (string)row["description"];
item.type = (string)row["type"];
item.subType = (string)row["subType"];
items.Add(item.id, item);
}
FrmLoading.setText("Loading Recipies from DB...");
//load recipies
DataSet dsRecipies = new DataSet();
string selectRecipies = @"SELECT *
FROM recipies
ORDER BY id" ;
using (SqlDataAdapter da = new SqlDataAdapter(selectRecipies, dataConnection))
{
da.Fill(dsRecipies);
}
DataSet dsRecipieIngredients = new DataSet();
string selectIngredients = @"SELECT *
FROM recipieIngredients
ORDER BY recipieID";
using (SqlDataAdapter da = new SqlDataAdapter(selectIngredients, dataConnection))
{
da.Fill(dsRecipieIngredients);
}
int iLine = 0;
foreach (DataRow row in dsRecipies.Tables[0].Rows)
{
Recipie r = new Recipie();
r.id = (int)row["id"];
r.result = Program.items[(int)row["result"]];
r.resultQty = (int)row["resultQty"];
r.profession = (string)row["profession"];
r.crafterLevel = (string)row["crafterLevel"];
r.hours = (byte)row["hours"];
while (iLine < dsRecipieIngredients.Tables[0].Rows.Count)
{
if (((int)dsRecipieIngredients.Tables[0].Rows[iLine]["recipieID"]) == r.id)
{
InventoryItem iq = new InventoryItem();
iq.item = Program.items[(int)dsRecipieIngredients.Tables[0].Rows[iLine]["ingredient"]];
iq.qty = (int)dsRecipieIngredients.Tables[0].Rows[iLine]["qty"];
r.ingredients.Add(iq);
}
else if (((int)dsRecipieIngredients.Tables[0].Rows[iLine]["recipieID"]) > r.id)
{
break;
}
iLine++;
}
recipies.Add(r);
}
FrmLoading.setText("Generating Local Inventory Lists...");
inventories = new Dictionary <string, Inventory>();
inventories.Add("On Hand", new Inventory("On Hand"));
string selectLootLocations = @"select distinct location from inventory where location != 'On Hand'";
using (SqlCommand commIngredients = new SqlCommand(selectLootLocations, dataConnection))
{
using (SqlDataReader reader = commIngredients.ExecuteReader())
{
while (reader.Read())
{
inventories.Add(reader.GetString(0), new Inventory(reader.GetString(0)));
}
}
}
}
}
/// <summary>
/// event handler for the save and close button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSave_Click(object sender, EventArgs e)
{
if (cbProfession.Text.Replace("'", "''").Length == 0)
{
System.Windows.Forms.MessageBox.Show("Profession is a required field", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
if (cbCrafterLevel.Text.Replace("'", "''").Length == 0)
{
System.Windows.Forms.MessageBox.Show("Crafter level is a required field", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
if (result == null)
{
System.Windows.Forms.MessageBox.Show("Result is a required field", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
if (lvIngredients.Items.Count == 0)
{
System.Windows.Forms.MessageBox.Show("Ingedients must be specified", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
//setup the update to the main sql table
string sql;
if (this.curRecipie == null)
{
sql = @"INSERT INTO recipies
(result,
resultQty,
profession,
crafterLevel,
hours)
VALUES ('-" + result.id + @"',
'" + nudNumProduced.Value + @"',
'" + cbProfession.Text.Replace("'", "''") + @"',
'" + cbCrafterLevel.Text.Replace("'", "''") + @"',
'" + nudHours.Value + @"')";
curRecipie = new Recipie();
}
else
{
sql = @"UPDATE recipies
SET result = '" + result.id + @"',
resultQty = '" + nudNumProduced.Value + @"',
profession = '" + cbProfession.Text.Replace("'", "''") + @"',
crafterLevel = '" + cbCrafterLevel.Text.Replace("'", "''") + @"',
hours = '" + nudHours.Value + @"'
WHERE id = '" + curRecipie.id + @"'";
}
//update the memory copy of the recipie
curRecipie.result = this.result;
curRecipie.resultQty = (int)nudNumProduced.Value;
curRecipie.profession = cbProfession.Text;
curRecipie.crafterLevel = cbCrafterLevel.Text;
curRecipie.hours = (byte)nudHours.Value;
using (SqlConnection dataConnection = new SqlConnection(Program.connectionString))
{
dataConnection.Open();
using (SqlCommand comm = new SqlCommand(sql, dataConnection))
{
comm.ExecuteNonQuery();
}
if (curRecipie.id == -1)
{
//find the new recipie
sql = @"SELECT id
FROM recipies
WHERE result < 0";
using (SqlCommand comm = new SqlCommand(sql, dataConnection))
{
curRecipie.id = (int)comm.ExecuteScalar();
}
//mark the recipie as not new
sql = @"UPDATE recipies
SET result = -1*result
WHERE id = '" + curRecipie.id + "'";
using (SqlCommand comm = new SqlCommand(sql, dataConnection))
{
comm.ExecuteNonQuery();
}
//add the link to this recipie to the rest of the program,
//(since it's done by reference we can do this before the shared add ingredients code)
Program.recipies.Add(curRecipie);
}
else
{
//if it's an existing recipie we already have the ids set,
//but need to clear out the old ingredients before adding the new ones
curRecipie.ingredients.Clear();
sql = @"DELETE FROM recipieIngredients
WHERE recipieID = '" + curRecipie.id + "'";
using (SqlCommand comm = new SqlCommand(sql, dataConnection))
{
comm.ExecuteNonQuery();
}
}
//add the ingredients to the recipie
foreach (ListViewItem lvi in lvIngredients.Items)
{
InventoryItem iq = (InventoryItem)lvi.Tag;
curRecipie.ingredients.Add(iq);
sql = @"INSERT INTO recipieIngredients
(recipieID,
ingredient,
qty)
VALUES ('" + curRecipie.id + @"',
'" + iq.item.id + @"',
'" + iq.qty + @"')";
using (SqlCommand comm = new SqlCommand(sql, dataConnection))
{
comm.ExecuteNonQuery();
}
}
//update the main window with the recipie
Program.mainForm.lvItems_SelectedIndexChanged(null, null);
}
this.Close();
}
