public void addMissingItems()
{
try
{
string sql = "SELECT DISTINCT slot, item, short_event_name FROM loot AS l WHERE NOT EXISTS(SELECT 1 FROM items WHERE item = l.item AND short_event_name = l.short_event_name AND slot = l.slot) AND NOT EXISTS(SELECT 1 FROM items WHERE is_global = 'Yes' AND item = l.item AND slot = l.slot)";
DbDataReader rs = DBManager.getManager().executeQuery(sql);
while (rs.Read())
{
string slot = rs[0].ToString().Trim();
string item = rs[1].ToString().Trim();
string eventName = rs[2].ToString().Trim();
logger.Info(String.Format("Inserting loot entry. [{0}, {1}, {2}]", slot, item, eventName));
// Send the new row to the API for insertion.
IList <Object> values = new List <Object> {
slot, item, eventName
};
GDriveManager.appendSpreadsheet(Constants.ITEMS_ID, "ROFItems", values);
DBManager.getManager().insertItemEntry(slot, item, eventName, "", "", "");
}
}
catch (Exception e)
{
logger.Error("Failed to insert loot entry", e);
throw e;
}
}
public void loadItems()
{
// Clear the current items
itemArr.Clear();
ValueRange response = GDriveManager.readSpreadsheet(Constants.ITEMS_ID, "ROFItems");
IDictionary <string, int> headerMap = GDriveManager.getHeaderMap(response.Values);
List <BulkLoader> rows = new List <BulkLoader>();
// Iterate through each row
foreach (IList <Object> row in response.Values.Skip(1))
{
ItemEntry item = new ItemEntry();
item.itemName = GDriveManager.readCell(row, headerMap[ITEM_NAME_COL]);
item.eventName = GDriveManager.readCell(row, headerMap[EVENT_COL]);
item.slot = GDriveManager.readCell(row, headerMap[SLOT_COL]);
item.is_special = GDriveManager.readCell(row, headerMap[SPECIAL_COL]);
rows.Add(item);
itemArr.Add(item);
}
DBManager.getManager().bulkInsert(rows, "items");
logger.Info("Items loaded successfully. " + itemArr.Count + " entries.");
}
public void loadLootInfo()
{
if (!isLoading)
{
isLoading = true;
loadLogFeed();
IDictionary <string, int> headerMap = GDriveManager.getHeaderMap(logFeed.Values);
List <BulkLoader> rows = new List <BulkLoader>();
// Iterate through each row
foreach (IList <Object> row in logFeed.Values.Skip(1))
{
LootEntry loot = new LootEntry();
loot.date = GDriveManager.readCell(row, headerMap["date"]);
loot.name = GDriveManager.readCell(row, headerMap["name"]);
loot.eventName = GDriveManager.readCell(row, headerMap["event"]);
loot.item = GDriveManager.readCell(row, headerMap["item"]);
loot.slot = GDriveManager.readCell(row, headerMap["slot"]);
loot.rot = GDriveManager.readCell(row, headerMap["rot"]);
loot.altLoot = GDriveManager.readCell(row, headerMap["alt loot"]);
rows.Add(loot);
}
DBManager.getManager().emptyTable("loot");
DBManager.getManager().bulkInsert(rows, "loot");
isLoading = false;
}
}
public void loadEvents()
{
events.Clear();
ValueRange response = GDriveManager.readSpreadsheet(logURI, "RainOfFearRaids");
IDictionary <string, int> headerMap = GDriveManager.getHeaderMap(response.Values);
// order by 2nd column (Short Name)
IEnumerable <IList <Object> > sorted = response.Values.Skip(1).OrderBy(f => f.ElementAt(headerMap[EVENT_SHORT_COL]).ToString());
List <BulkLoader> rows = new List <BulkLoader>();
// Iterate through each row
foreach (IList <Object> row in sorted)
{
string display = row[headerMap[EVENT_DISPLAY_COL]].ToString().ToLower();
if (display.Equals("yes"))
{
EventEntry evt = new EventEntry();
evt.eventName = row[headerMap[EVENT_EVENT_COL]].ToString();
evt.shortName = row[headerMap[EVENT_SHORT_COL]].ToString();
evt.tier = row[headerMap[EVENT_TIER_COL]].ToString();
events.Add(evt);
rows.Add(evt);
}
}
DBManager.getManager().bulkInsert(rows, "events");
logger.Info("Events loaded successfully. " + events.Count + " entries.");
}
public Roster()
{
// Clear the current roster
rosterArr.Clear();
ValueRange response = GDriveManager.readSpreadsheet(Constants.ROSTER_ID, "Sheet1");
IDictionary <string, int> headerMap = GDriveManager.getHeaderMap(response.Values);
IEnumerable <IList <Object> > sorted = response.Values.Skip(1).OrderBy(f => f.ElementAt(headerMap[NAME_COL]).ToString());
List <BulkLoader> rows = new List <BulkLoader>();
// Iterate through each row
foreach (IList <Object> row in sorted)
{
RosterEntry r = new RosterEntry();
r.name = GDriveManager.readCell(row, headerMap[NAME_COL]);
r.classType = GDriveManager.readCell(row, headerMap[CLASS_COL]);
r.rank = GDriveManager.readCell(row, headerMap[RANK_COL]);
r.active = GDriveManager.readCell(row, headerMap[ACTIVE_COL]);
rows.Add(r);
rosterArr.Add(r);
}
DBManager.getManager().bulkInsert(rows, "roster");
logger.Info("Roster loaded successfully. " + rosterArr.Count + " entries.");
}
private void launchLootLogWindow(string name)
{
lootLogForm.Close();
lootLogForm = new frmLootLog(Location.X, Location.Y);
lootLogForm.Text = "Loot Lot for: " + name;
lootLogForm.getView().SortCompare += new DataGridViewSortCompareEventHandler(lootSummarySorter);
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT strftime('%m/%d/%Y', loot_date),");
sql.AppendLine(" short_event_name,");
sql.AppendLine(" item,");
sql.AppendLine(" slot,");
sql.AppendLine(" rot,");
sql.AppendLine(" alt_loot,");
sql.AppendLine(" (SELECT DISTINCT is_special FROM items AS i WHERE UPPER(l.item) = UPPER(i.item))");
sql.AppendLine("FROM loot AS l");
sql.AppendLine("WHERE name = '" + DBManager.safeParam(name) + "'");
sql.AppendLine("ORDER BY loot_date");
DbDataReader rs = DBManager.getManager().executeQuery(sql.ToString());
while (rs.Read())
{
lootLogForm.getView().Rows.Add(new string[] { rs[0].ToString(), rs[1].ToString(), rs[2].ToString(), rs[3].ToString(), rs[4].ToString(), rs[5].ToString(), rs[6].ToString() });
}
rs.Close();
lootLogForm.ShowDialog();
}
private void selectLoot(object sender, ListViewItemSelectionChangedEventArgs args)
{
if (lootLogView.SelectedItems.Count > 0)
{
//reset
removeLoot.Enabled = true;
cmbName.SelectedIndex = -1;
cmbName.Text = "";
cmbSlot.SelectedIndex = -1;
cmbSlot.Text = "";
cmbItems.SelectedIndex = -1;
cmbItems.Text = "";
int nameIndex = cmbName.FindString(lootLogView.SelectedItems[0].SubItems[0].Text);
if (nameIndex >= 0)
{
cmbName.SelectedIndex = nameIndex;
}
else
{
// must be a new member
cmbName.Text = lootLogView.SelectedItems[0].SubItems[0].Text;
}
// set slot before item
string slot = DBManager.getManager().getSlotForItem(lootLogView.SelectedItems[0].SubItems[1].Text);
int slotIndex = -1;
if (slot != null && (slotIndex = cmbSlot.FindString(slot)) >= 0)
{
cmbSlot.SelectedIndex = slotIndex;
}
int itemIndex = cmbItems.FindString(lootLogView.SelectedItems[0].SubItems[1].Text);
if (itemIndex >= 0)
{
cmbItems.SelectedIndex = itemIndex;
}
else
{
// must be a new item
cmbItems.Text = lootLogView.SelectedItems[0].SubItems[1].Text;
}
}
else
{
removeLoot.Enabled = false;
}
}
private void loadRosterNames()
{
summaryRosterChanging = true;
DbDataReader rs = DBManager.getManager().executeQuery("SELECT name FROM roster WHERE active = 'Yes' ORDER BY name");
lvRosterNames.Items.Clear();
while (rs.Read())
{
string name = rs[0].ToString();
ListViewItem item = new ListViewItem(name);
item.Text = name;
item.Name = name;
lvRosterNames.Items.Add(item);
}
rs.Close();
summaryRosterChanging = false;
}
private void updateSlotEventList()
{
try
{
string item = cmbItems.Text;
if (item != null || "".Equals(item))
{
DbDataReader reader = DBManager.getManager().executeQuery(String.Format("SELECT DISTINCT short_event_name, slot FROM items WHERE item = '{0}';", DBManager.safeParam(item)));
int numRows = 0;
string eventName = "";
string slot = "";
if (reader.Read())
{
eventName = reader[0].ToString();
slot = reader[1].ToString();
numRows++;
}
if (numRows == 1)
{
if (eventName != null || !"".Equals(eventName))
{
//cmbEvent.Text = eventName;
}
if (slot != null || !"".Equals(slot))
{
//cmbSlot.SelectedIndex = cmbSlot.Items.IndexOf(slot);
//cmbSlot.Text = slot;
}
}
}
}
catch (Exception e)
{
// Ignore errors here
logger.Error("Could not update combos based on item selection.", e);
}
}
public void loadAttendance()
{
using (var webClient = new System.Net.WebClient())
{
List <BulkLoader> attend = new List <BulkLoader>();
string json = webClient.DownloadString(Constants.RAID_ATTENDANCE_URL);
JsonTextReader reader = new JsonTextReader(new StringReader(json));
while (reader.Read())
{
if (JsonToken.StartObject.Equals(reader.TokenType))
{
RosterAttendence r = new RosterAttendence();
reader.Read();
reader.Read();
r.thirty = reader.Value.ToString();
reader.Read();
reader.Read();
r.sixty = reader.Value.ToString();
reader.Read();
reader.Read();
r.ninety = reader.Value.ToString();
reader.Read();
reader.Read();
reader.Read();
reader.Read();
r.name = reader.Value.ToString();
attend.Add(r);
}
}
DBManager.getManager().emptyTable("attendance");
DBManager.getManager().bulkInsert(attend, "attendance");
}
}
private void getLootSummary()
{
dgvLootSummary.Rows.Clear();
dgvVisibleSummary.Rows.Clear();
dgvNonVisibleSummary.Rows.Clear();
dgvWeaponSummary.Rows.Clear();
if (lvRosterNames.CheckedItems.Count > 0)
{
StringBuilder selectedNames = new StringBuilder();
foreach (ListViewItem o in lvRosterNames.CheckedItems)
{
selectedNames.Append(String.Format("'{0}', ", DBManager.safeParam(o.Text)));
}
string namesFilter = selectedNames.ToString().Trim().Trim(',');
string queryFilters = getIncludeRotsFilter() + getTierFilter();
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT r.name,");
sql.AppendLine(" (SELECT thirty || ' / ' || sixty || ' / ' || ninety FROM attendance WHERE name = r.name) AS attendance,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name" + queryFilters + ") AS loot_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Arms', 'Chest', 'Feet', 'Hands', 'Head', 'Legs', 'Wrist')" + queryFilters + ") AS visible_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Back', 'Charm', 'Ear', 'Face', 'Neck', 'Range', 'Ring', 'Shield', 'Shoulders', 'Waist')" + queryFilters + ") AS non_visible_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('1HB', '1HP', '1HS', '2HB', '2HP', '2HS', 'HTH')" + queryFilters + ") AS weapon_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND rot = 'Yes'" + getTierFilter() + " AND alt_loot <> 'Yes') AS rot_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM (SELECT DISTINCT i.item FROM loot AS l, items AS i WHERE name = r.name " + queryFilters + " AND UPPER(l.item) = UPPER(i.item) AND i.is_special = 'Yes')) AS special_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND alt_loot = 'Yes'" + getTierFilter() + ") AS alt_total,");
sql.AppendLine(" strftime('%m/%d/%Y', (SELECT MAX(loot_date) FROM loot AS l WHERE name = r.name AND alt_loot = 'Yes'" + getTierFilter() + ")) AS alt_last_loot_date,");
sql.AppendLine(" strftime('%m/%d/%Y', (SELECT MAX(loot_date) FROM loot AS l WHERE name = r.name" + queryFilters + ")) AS last_loot_date");
sql.AppendLine("FROM roster AS r");
sql.AppendLine("WHERE r.active = 'Yes'");
sql.AppendLine("AND r.name IN (" + namesFilter + ")");
sql.AppendLine("ORDER BY r.name");
DbDataReader rs = DBManager.getManager().executeQuery(sql.ToString());
while (rs.Read())
{
dgvLootSummary.Rows.Add(new string[] { rs[0].ToString(), rs[1].ToString(), rs[2].ToString(), rs[3].ToString(), rs[4].ToString(), rs[5].ToString(), rs[6].ToString(), rs[7].ToString(), rs[8].ToString(), rs[9].ToString(), rs[10].ToString() });
}
rs.Close();
sql = new StringBuilder();
sql.AppendLine("SELECT r.name,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Arms')" + queryFilters + ") AS arms_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Chest')" + queryFilters + ") AS chest_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Feet')" + queryFilters + ") AS feet_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Hands')" + queryFilters + ") AS hands_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Head')" + queryFilters + ") AS head_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Legs')" + queryFilters + ") AS legs_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Wrist')" + queryFilters + ") AS wrist_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Arms', 'Chest', 'Feet', 'Hands', 'Head', 'Legs', 'Wrist')" + queryFilters + ") AS visible_total,");
sql.AppendLine(" strftime('%m/%d/%Y', (SELECT MAX(loot_date) FROM loot AS l WHERE name = r.name AND slot IN ('Arms', 'Chest', 'Feet', 'Hands', 'Head', 'Legs', 'Wrist')" + queryFilters + ")) AS last_visible_loot_date");
sql.AppendLine("FROM roster AS r");
sql.AppendLine("WHERE r.active = 'Yes'");
sql.AppendLine("AND r.name IN (" + namesFilter + ")");
sql.AppendLine("ORDER BY r.name");
rs = DBManager.getManager().executeQuery(sql.ToString());
while (rs.Read())
{
dgvVisibleSummary.Rows.Add(new string[] { rs[0].ToString(), rs[1].ToString(), rs[2].ToString(), rs[3].ToString(), rs[4].ToString(), rs[5].ToString(), rs[6].ToString(), rs[7].ToString(), rs[8].ToString(), rs[9].ToString() });
}
rs.Close();
sql = new StringBuilder();
sql.AppendLine("SELECT r.name,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Back')" + queryFilters + ") AS back_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Charm')" + queryFilters + ") AS charm_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Ear')" + queryFilters + ") AS ear_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Face')" + queryFilters + ") AS face_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Neck')" + queryFilters + ") AS neck_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Range')" + queryFilters + ") AS range_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Ring')" + queryFilters + ") AS ring_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Shield')" + queryFilters + ") AS shield_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Shoulders')" + queryFilters + ") AS shoulders_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Waist')" + queryFilters + ") AS waist_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('Back', 'Charm', 'Ear', 'Face', 'Neck', 'Range', 'Ring', 'Shield', 'Shoulders', 'Waist')" + queryFilters + ") AS non_visible_total,");
sql.AppendLine(" strftime('%m/%d/%Y', (SELECT MAX(loot_date) FROM loot AS l WHERE name = r.name AND slot IN ('Back', 'Charm', 'Ear', 'Face', 'Neck', 'Range', 'Ring', 'Shield', 'Shoulders', 'Waist')" + queryFilters + ")) AS last_non_vis_loot_date");
sql.AppendLine("FROM roster AS r");
sql.AppendLine("WHERE r.active = 'Yes'");
sql.AppendLine("AND r.name IN (" + namesFilter + ")");
sql.AppendLine("ORDER BY r.name");
rs = DBManager.getManager().executeQuery(sql.ToString());
while (rs.Read())
{
dgvNonVisibleSummary.Rows.Add(new string[] { rs[0].ToString(), rs[1].ToString(), rs[2].ToString(), rs[3].ToString(), rs[4].ToString(), rs[5].ToString(), rs[6].ToString(), rs[7].ToString(), rs[8].ToString(), rs[9].ToString(), rs[10].ToString(), rs[11].ToString(), rs[12].ToString() });
}
rs.Close();
sql = new StringBuilder();
sql.AppendLine("SELECT r.name,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('1HB')" + queryFilters + ") AS blunt_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('1HP')" + queryFilters + ") AS piercing_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('1HS')" + queryFilters + ") AS slash_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('2HB')" + queryFilters + ") AS two_hand_blunt_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('2HP')" + queryFilters + ") AS two_hand_pierce_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('2HS')" + queryFilters + ") AS two_hand_slash_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('HTH')" + queryFilters + ") AS hth_total,");
sql.AppendLine(" (SELECT COUNT(1) FROM loot AS l WHERE name = r.name AND slot IN ('1HB', '1HP', '1HS', '2HB', '2HP', '2HS', 'HTH')" + queryFilters + ") AS weapon_total,");
sql.AppendLine(" strftime('%m/%d/%Y', (SELECT MAX(loot_date) FROM loot AS l WHERE name = r.name AND slot IN ('1HB', '1HP', '1HS', '2HB', '2HP', '2HS', 'HTH')" + queryFilters + ")) AS last_weapon_loot_date");
sql.AppendLine("FROM roster AS r");
sql.AppendLine("WHERE r.active = 'Yes'");
sql.AppendLine("AND r.name IN (" + namesFilter + ")");
sql.AppendLine("ORDER BY r.name");
rs = DBManager.getManager().executeQuery(sql.ToString());
while (rs.Read())
{
dgvWeaponSummary.Rows.Add(new string[] { rs[0].ToString(), rs[1].ToString(), rs[2].ToString(), rs[3].ToString(), rs[4].ToString(), rs[5].ToString(), rs[6].ToString(), rs[7].ToString(), rs[8].ToString(), rs[9].ToString() });
}
rs.Close();
// Resort the data grid. Ignore any errors for now
SortOrder order;
try
{
order = dgvLootSummary.SortOrder;
if (dgvLootSummary.SortedColumn != null && !order.Equals(SortOrder.None))
{
dgvLootSummary.Sort(dgvLootSummary.SortedColumn, (order.Equals(SortOrder.Ascending) ? ListSortDirection.Ascending : ListSortDirection.Descending));
}
order = dgvVisibleSummary.SortOrder;
if (dgvVisibleSummary.SortedColumn != null && !order.Equals(SortOrder.None))
{
dgvVisibleSummary.Sort(dgvVisibleSummary.SortedColumn, (order.Equals(SortOrder.Ascending) ? ListSortDirection.Ascending : ListSortDirection.Descending));
}
order = dgvNonVisibleSummary.SortOrder;
if (dgvNonVisibleSummary.SortedColumn != null && !order.Equals(SortOrder.None))
{
dgvNonVisibleSummary.Sort(dgvNonVisibleSummary.SortedColumn, (order.Equals(SortOrder.Ascending) ? ListSortDirection.Ascending : ListSortDirection.Descending));
}
order = dgvWeaponSummary.SortOrder;
if (dgvWeaponSummary.SortedColumn != null && !order.Equals(SortOrder.None))
{
dgvWeaponSummary.Sort(dgvWeaponSummary.SortedColumn, (order.Equals(SortOrder.Ascending) ? ListSortDirection.Ascending : ListSortDirection.Descending));
}
}
catch (Exception e)
{
logger.Error("Error resorting the data grids ignored.", e);
}
}
}
