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 String getTierFilter() { if (lvTierSelection.CheckedItems.ContainsKey("All") || lvTierSelection.CheckedItems.Count == 0) { return(" "); } else { String search = ""; foreach (ListViewItem i in lvTierSelection.CheckedItems) { search += "'" + DBManager.safeParam(i.Text) + "', "; } search = search.Substring(0, search.Length - 2); return(" AND EXISTS(SELECT 1 FROM events WHERE short_event_name = l.short_event_name AND tier IN (" + search + ")) "); } /* * if (selectedTier == null || "".Equals(selectedTier) || "All".Equals(selectedTier)) { * return " "; * } else { * return " AND EXISTS(SELECT 1 FROM events WHERE short_event_name = l.short_event_name AND tier = '" + DBManager.safeParam(selectedTier) + "') "; * } */ }
public string getSlotForItem(string item) { string slot = null; string sql = String.Format(SLOT_QUERY, DBManager.safeParam(item)); DbDataReader reader = executeQuery(sql); if (reader.Read()) { slot = reader[0].ToString(); } return(slot); }
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); } }
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); } } }