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); } } }