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