diff options
author | Dominique Martinet <asmadeus@codewreck.org> | 2015-05-08 13:31:07 +0200 |
---|---|---|
committer | Dominique Martinet <asmadeus@codewreck.org> | 2015-11-08 14:27:59 +0100 |
commit | 346e69cea8592a9548b64e7bb52adbf6fc7594fd (patch) | |
tree | 529a3ed6bb1f80d5edc619b02ee8f1283d0bb114 | |
parent | 4ebfd5fc8437cae25402f4e86733eadeefe002b6 (diff) |
listguests page and old view creation sql
-rw-r--r-- | listguests.php | 104 | ||||
-rw-r--r-- | schema-updates/2013-05-07-views.sql | 7 |
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`; |