/// <summary>
/// returns an enumerator to view the history of items in this inventory
/// </summary>
/// <returns></returns>
public DateTime getLatestModification(int id)
{
HistoryRecord hr = history.FirstOrDefault(x => x.ii.item.id == id);
if (hr == null)
{
return(DateTime.MinValue);
}
return(hr.dateTime);
}
/// <summary>
/// makes a copy of this instance, uses a clone of InverntoryItem, that uses the original Item Instnace
/// </summary>
/// <returns></returns>
public object Clone()
{
HistoryRecord newHR = new HistoryRecord();
newHR.dateTime = this.dateTime;
newHR.ii = (InventoryItem)this.ii.Clone();
newHR.qtyChanged = this.qtyChanged;
newHR.note = (string)this.note.Clone();
return(newHR);
}
/// <summary>
/// updates an existing history item
/// </summary>
/// <param name="hr"></param>
public void updateHistory(HistoryRecord hr)
{
string unsaved = " Unsaved Changes";
if ((Program.mainForm != null) && (!Program.mainForm.Text.EndsWith(unsaved)))
{
if (Program.mainForm.InvokeRequired)
{
Program.mainForm.Invoke(new Action(() => Program.mainForm.Text += unsaved)); // do stuff on UI thread, not here
return;
}
}
this.sqlTasks.Enqueue(hr);
}
static void Main()
{
///TODO: clean up duplicate entries in inventory sql table caused by multiple recipies using an item
try
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
FrmLoading.ShowSplashScreen();
loadAllFromDB();
FrmLoading.setText("Creating main form...");
mainForm = new FrmMain();
FrmLoading.setText("Loading main form...");
Application.Run(mainForm);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
HistoryRecord hrCloser = new HistoryRecord()
{
ii = new InventoryItem()
{
item = new Item()
{
id = int.MaxValue
}
}
};
foreach (KeyValuePair <string, Inventory> i in inventories)
{
i.Value.addHistory(hrCloser);
}
}
/// <summary>
/// Loads all the items and recipies from the database
/// </summary>
public Inventory(string loc)
{
this.location = loc;
loot = new Dictionary <int, InventoryItem>();
history = new Stack <HistoryRecord>();
using (SqlConnection dataConnection = new SqlConnection(Program.connectionString))
{
dataConnection.Open();
//load mod lookup table
DataSet mods = new DataSet();
string selectMods = @"SELECT inventoryId, subItemId
FROM mods
ORDER BY inventoryId";
using (SqlDataAdapter da = new SqlDataAdapter(selectMods, dataConnection))
{
da.Fill(mods);
}
int modLine = 0;
//load loot
string selectLoot = @"SELECT *
FROM inventory
WHERE location = '" + loc.Replace("'", "''") + @"'
ORDER BY id" ;
using (SqlCommand comm = new SqlCommand(selectLoot, dataConnection))
{
using (SqlDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
InventoryItem ii = new InventoryItem();
ii.id = (int)reader["id"];
ii.qty = (int)reader["qty"];
ii.item = Program.items[(int)reader["itemId"]];
while (modLine < mods.Tables[0].Rows.Count)
{
if (((int)mods.Tables[0].Rows[modLine]["inventoryId"]) == ii.id)
{
ii.modsAttached.Add((int)mods.Tables[0].Rows[modLine]["subItemId"]);
}
else if (((int)mods.Tables[0].Rows[modLine]["inventoryId"]) > ii.id)
{
break;
}
modLine++;
}
loot.Add(ii.id, ii);
}
}
}
//load empty records for loot we don't have any of yet
string selectZeroLoot = @"select items.id
from items
where id not in (select itemID
from inventory
where location = '" + loc.Replace("'", "''") + @"')
and id != 0";
using (SqlCommand comm = new SqlCommand(selectZeroLoot, dataConnection))
{
using (SqlDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
InventoryItem iq = new InventoryItem();
iq.id = -1 * (int)reader["id"];
iq.qty = 0;
iq.item = Program.items[(int)reader["id"]];
loot.Add(iq.id, iq);
}
}
}
//load history
//maybe limit this to top 1000?
DataSet dsHistory = new DataSet();
string selectHistory = @"SELECT *
FROM inventoryHistory
WHERE location = '" + loc.Replace("'", "''") + @"'
ORDER BY modificationDate desc" ;
using (SqlDataAdapter da = new SqlDataAdapter(selectHistory, dataConnection))
{
da.Fill(dsHistory);
}
for (int i = dsHistory.Tables[0].Rows.Count - 1; i >= 0; i--)
{
DataRow row = dsHistory.Tables[0].Rows[i];
HistoryRecord hr = new HistoryRecord();
hr.dateTime = ((DateTime)row["modificationDate"]).ToLocalTime();
hr.ii = loot[(int)row["inventoryID"]];
hr.note = (string)row["note"];
hr.qtyChanged = (int)row["qty"];
history.Push(hr);
}
}
sqlTasks = new ConcurrentQueue <HistoryRecord>();
workerThread = new Thread(new ThreadStart(workerThreadLoop));
workerThread.Start();
}
/// <summary>
/// adds a new history line
/// </summary>
/// <param name="hr"></param>
public void addHistory(HistoryRecord hr)
{
history.Push(hr);
updateHistory(hr);
}
/// <summary>
/// Proccessing loop for the worker thread, only exits once it finds a HistoryRecord with an Item ID of int.MaxValue
/// </summary>
private void workerThreadLoop()
{
string unsaved = " Unsaved Changes";
//Wait for the main window to be created
while (Program.mainForm == null)
{
Thread.Sleep(1000);
}
while (Program.mainForm.Visible || (!this.sqlTasks.IsEmpty))
{
Thread.Sleep(1000);
int attemptsLeft = 5;
while (attemptsLeft > 0)
{
try
{
using (SqlConnection dataConnection = new SqlConnection(Program.connectionString))
{
dataConnection.Open();
//Look for updates from other clients
DateTime checkDate = DateTime.Now.AddSeconds(-60 * 5).ToUniversalTime();
string findUpdatesSQL = @"SELECT *
FROM inventoryHistory
WHERE modificationDate > '" + checkDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + @"'
AND location = '" + this.location + @"'
AND clientName != '" + Program.clientID + @"'";
using (SqlCommand comm = new SqlCommand(findUpdatesSQL, dataConnection))
{
using (SqlDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
/// TODO: check into quick changes using up/down arrows being overwritten by old values
/// TODO: deal with other clients adding an inventory id that this client doesn't have yet
/// TODO: deal with other clients adding/editing a mod/item/inventoryItem that this client doesn't have yet (qty 0, reload everything related to the item)
//look for a HistoryRecord that matches, but allow a 10 millisecond variance due to SQL truncating dates
HistoryRecord hrToUpdate = history.SingleOrDefault(x =>
(x.ii.id == (int)reader["id"]) &&
(100000 > Math.Abs((x.dateTime - ((DateTime)reader["modificationDate"]).ToLocalTime()).Ticks)));
if (hrToUpdate == null)
{
hrToUpdate = new HistoryRecord()
{
dateTime = ((DateTime)reader["modificationDate"]).ToLocalTime(),
ii = this.loot[(int)reader["ID"]],
qtyChanged = (int)reader["qty"],
note = (string)reader["note"]
};
hrToUpdate.ii.qty += hrToUpdate.qtyChanged;
this.history.Push(hrToUpdate);
if (Program.mainForm.inventory == this)
{
Program.mainForm.updateItemQty(hrToUpdate.ii);
}
}
else if ((hrToUpdate.qtyChanged != (int)reader["qty"]) ||
(hrToUpdate.note != (string)reader["note"]))
{
hrToUpdate.note = (string)reader["note"];
hrToUpdate.ii.qty -= hrToUpdate.qtyChanged;
hrToUpdate.qtyChanged = (int)reader["qty"];
hrToUpdate.ii.qty += hrToUpdate.qtyChanged;
if (Program.mainForm.inventory == this)
{
Program.mainForm.updateItemQty(hrToUpdate.ii);
}
}
}
}
}
//update database with this client's changes
HistoryRecord hr = null;
while ((!paused) && this.sqlTasks.TryDequeue(out hr))
{
//if the program is closing, finish up
if (hr.ii.item.id == int.MaxValue)
{
return;
}
//clone the history record to avoid cross-threadding issues
HistoryRecord hrClone = (HistoryRecord)hr.Clone();
string sql = @"UPDATE inventory
SET qty = '" + hrClone.ii.qty + @"'
WHERE id = '" + hrClone.ii.id + @"'
AND itemId = '" + hrClone.ii.item.id + @"'
AND location = '" + this.location + @"'";
using (SqlCommand comm = new SqlCommand(sql, dataConnection))
{
//run the update, if no rows are changed, then do an insert
if (comm.ExecuteNonQuery() == 0)
{
sql = @"INSERT INTO inventory
(itemId,
location,
qty)
VALUES ('" + hrClone.ii.item.id + @"',
'" + this.location + @"',
'" + hrClone.ii.qty + @"')";
using (SqlCommand insertComm = new SqlCommand(sql, dataConnection))
{
insertComm.ExecuteNonQuery();
}
//grab id of new item
sql = @"SELECT max(id)
FROM inventory
WHERE itemId = '" + hrClone.ii.item.id + @"'
AND location = '" + this.location + @"'
AND qty = '" + hrClone.ii.qty + @"'";
using (SqlCommand getIDComm = new SqlCommand(sql, dataConnection))
{
hrClone.ii.id = (int)getIDComm.ExecuteScalar();
}
}
}
sql = @"INSERT INTO inventoryHistory
(inventoryId,
itemId,
modificationDate,
location,
qty,
note,
clientName)
VALUES ('" + hrClone.ii.id + @"',
'" + hrClone.ii.item.id + @"',
'" + hrClone.dateTime.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.fff") + @"',
'" + this.location + @"',
'" + hrClone.qtyChanged + @"',
'" + hrClone.note + @"',
'" + Program.clientID + @"')";
using (SqlCommand comm = new SqlCommand(sql, dataConnection))
{
try
{
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (ex.Message.StartsWith("Violation of PRIMARY KEY"))
{
sql = @"UPDATE inventoryHistory
SET qty = '" + hrClone.qtyChanged + @"',
note = '" + hrClone.note + @"',
clientName = '" + Program.clientID + @"'
WHERE inventoryId = '" + hrClone.ii.id + @"'
AND itemID = '" + hrClone.ii.item.id + @"'
AND modificationDate = '" + hrClone.dateTime.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.fff") + @"'
AND location = '" + this.location + @"'";
using (SqlCommand commUpdate = new SqlCommand(sql, dataConnection))
{
commUpdate.ExecuteNonQuery();
}
}
else
{
throw;
}
}
}
//if this was a new item for the inventory, update the local data
if (hr.ii.id != hrClone.ii.id)
{
///TODO: look into why making the Bomblet of Necrosis caused this function to have a negative value for hr.ii.id that caused and error
updateID(hr.ii.id, hrClone.ii.id);
}
}
}
}
catch (Exception ex)
{
if ((Program.mainForm != null) &&
(Program.mainForm.Visible) &&
(!Program.mainForm.Text.EndsWith(" - Error contacting server")))
{
Program.mainForm.setText(Program.mainForm.Text + " - Error contacting server");
}
attemptsLeft--;
if (attemptsLeft == 0)
{
Program.mainForm.Invoke(new Action(() =>
{
System.Windows.Forms.MessageBox.Show(ex.ToString(), "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}));
}
}
attemptsLeft = 0;
}
if ((Program.mainForm != null) &&
(Program.mainForm.Visible) &&
(Program.mainForm.Text.EndsWith(unsaved)) &&
(!this.sqlTasks.IsEmpty))
{
Program.mainForm.ResetText();
}
}
}