summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDominique Martinet <asmadeus@codewreck.org>2015-05-08 13:31:07 +0200
committerDominique Martinet <asmadeus@codewreck.org>2015-11-08 14:27:59 +0100
commit346e69cea8592a9548b64e7bb52adbf6fc7594fd (patch)
tree529a3ed6bb1f80d5edc619b02ee8f1283d0bb114
parent4ebfd5fc8437cae25402f4e86733eadeefe002b6 (diff)
listguests page and old view creation sql
-rw-r--r--listguests.php104
-rw-r--r--schema-updates/2013-05-07-views.sql7
2 files changed, 111 insertions, 0 deletions
diff --git a/listguests.php b/listguests.php
new file mode 100644
index 0000000..70a46db
--- /dev/null
+++ b/listguests.php
@@ -0,0 +1,104 @@
+<?php
+/******************************
+ * EQdkp
+ * Copyright NONE!!!!
+ * Licensed under the WTFPLS - just do whatever the fuck you want with this.
+ * ------------------
+ * August 2014
+ *
+ ******************************/
+
+define('EQDKP_INC', true);
+$eqdkp_root_path = './';
+include_once($eqdkp_root_path . 'common.php');
+
+$user->check_auth('u_member_list');
+
+
+$thirty_days = mktime(0, 0, 0, date('m'), date('d')-30, date('Y'));
+$sixty_days = mktime(0, 0, 0, date('m'), date('d')-60, date('Y'));
+$ninety_days = mktime(0, 0, 0, date('m'), date('d')-90, date('Y'));
+
+$raid_count_30 = $db->query_first('SELECT count(*) FROM ' . RAIDS_TABLE . ' WHERE raid_date BETWEEN '.$thirty_days.' AND '.time());
+$raid_count_60 = $db->query_first('SELECT count(*) FROM ' . RAIDS_TABLE . ' WHERE raid_date BETWEEN '.$sixty_days.' AND '.time());
+$raid_count_90 = $db->query_first('SELECT count(*) FROM ' . RAIDS_TABLE . ' WHERE raid_date BETWEEN '.$ninety_days.' AND '.time());
+
+
+
+// Build SQL query based on GET options
+$sql = 'SELECT
+ m.member_name,
+ member_earned,
+ member_spent,
+ member_adjustment,
+ (member_earned-member_spent+member_adjustment) AS member_current,
+ member_firstraid,
+ member_lastraid,
+ member_raidcount,
+ raid_count_30,
+ raid_count_60,
+ raid_count_90,
+ c.class_name AS member_class
+ FROM ' . MEMBERS_TABLE . ' m
+ LEFT JOIN ' . MEMBER_RANKS_TABLE . ' r
+ ON (m.member_rank_id = r.rank_id)
+ LEFT JOIN ' . CLASS_TABLE . ' c
+ ON (m.member_class_id = c.class_id)
+ LEFT JOIN R30 r30
+ ON (m.member_name = r30.member_name)
+ JOIN R60 r60
+ ON (m.member_name = r60.member_name)
+ LEFT JOIN R90 r90
+ ON (m.member_name = r90.member_name)
+ WHERE r.rank_name = "Guest"
+ ORDER BY member_lastraid desc, member_name';
+
+if ( !($members_result = $db->query($sql)) )
+{
+ message_die('Could not obtain member information', '', __FILE__, __LINE__, $sql);
+}
+
+
+// Print stuff
+printf("[table]<br/>");
+printf("[tr]");
+printf("[td]Name[/td]");
+printf("[td]Class[/td]");
+printf("[td]First[/td]");
+printf("[td]Last[/td]");
+printf("[td]30 days[/td]");
+printf("[td]60 days[/td]");
+printf("[td]90 days[/td]");
+printf("[td]Earned[/td]");
+printf("[td]Spent[/td]");
+printf("[td]Adjustment[/td]");
+printf("[td]Current[/td]");
+printf("[/tr]<br/>");
+
+while ( $row = $db->fetch_record($members_result) )
+{
+ $attended_percent_30 = ( $raid_count_30 > 0 ) ? round(($row['raid_count_30'] / $raid_count_30) * 100) : 0;
+ $attended_percent_60 = ( $raid_count_60 > 0 ) ? round(($row['raid_count_60'] / $raid_count_60) * 100) : 0;
+ $attended_percent_90 = ( $raid_count_90 > 0 ) ? round(($row['raid_count_90'] / $raid_count_90) * 100) : 0;
+
+
+ // TODO: add colors
+ printf("[tr]");
+ printf("[td]%s[/td]", $row['member_name']);
+ printf("[td]%s[/td]", $row['member_class']);
+ printf("[td]%s[/td]", date($user->style['date_notime_short'], $row['member_firstraid']));
+ printf("[td]%s[/td]", date($user->style['date_notime_short'], $row['member_lastraid']));
+ printf("[td]%s%%[/td]", $attended_percent_30);
+ printf("[td]%s%%[/td]", $attended_percent_60);
+ printf("[td]%s%%[/td]", $attended_percent_90);
+ printf("[td][color=green]%s[/color][/td]", $row['member_earned']);
+ printf("[td][color=red]%s[/color][/td]", $row['member_spent']);
+ printf("[td]%s[/td]", $row['member_adjustment']);
+ printf("[td][color=%s]%s[/color][/td]", $row['member_current'] > 0 ? 'green' : 'red', $row['member_current']);
+ printf("[/tr]<br/>");
+}
+
+printf("[/table]<br/>");
+$db->free_result($members_result);
+
+?>
diff --git a/schema-updates/2013-05-07-views.sql b/schema-updates/2013-05-07-views.sql
new file mode 100644
index 0000000..fe75408
--- /dev/null
+++ b/schema-updates/2013-05-07-views.sql
@@ -0,0 +1,7 @@
+DROP VIEW R30, R60, R90;
+
+CREATE VIEW `R60` AS select `ra`.`member_name` AS `member_name`,count(`ra`.`raid_id`) AS `raid_count_60` from (`neweqdkp_raid_attendees` `ra` join `neweqdkp_raids` `r` on((`r`.`raid_id` = `ra`.`raid_id`))) where (`r`.`raid_date` >= (unix_timestamp() - ((60 * 24) * 3600))) AND (`r`.`raid_name` = 'On time bonus' OR `r`.`raid_name` = 'Attendance Log') group by `ra`.`member_name`;
+
+CREATE VIEW `R30` AS select `ra`.`member_name` AS `member_name`,count(`ra`.`raid_id`) AS `raid_count_30` from (`neweqdkp_raid_attendees` `ra` join `neweqdkp_raids` `r` on((`r`.`raid_id` = `ra`.`raid_id`))) where (`r`.`raid_date` >= (unix_timestamp() - ((30 * 24) * 3600))) AND (`r`.`raid_name` = 'On time bonus' OR `r`.`raid_name` = 'Attendance Log') group by `ra`.`member_name`;
+
+CREATE VIEW `R90` AS select `ra`.`member_name` AS `member_name`,count(`ra`.`raid_id`) AS `raid_count_90` from (`neweqdkp_raid_attendees` `ra` join `neweqdkp_raids` `r` on((`r`.`raid_id` = `ra`.`raid_id`))) where (`r`.`raid_date` >= (unix_timestamp() - ((90 * 24) * 3600))) AND (`r`.`raid_name` = 'On time bonus' OR `r`.`raid_name` = 'Attendance Log') group by `ra`.`member_name`;