From 37db15f58589ea90202eba77bb0e60e62b17c8db Mon Sep 17 00:00:00 2001
From: Sam White <webmaster@ycra.org.uk>
Date: Fri, 31 Dec 2021 20:45:47 +0000
Subject: [PATCH] Add way to view responses to the winter event form.

---
 public_html/login.php                  |  4 +-
 public_html/winter-event-responses.php | 99 ++++++++++++++++++++++++++
 2 files changed, 102 insertions(+), 1 deletion(-)
 create mode 100644 public_html/winter-event-responses.php

diff --git a/public_html/login.php b/public_html/login.php
index b6400a7..67d0f22 100644
--- a/public_html/login.php
+++ b/public_html/login.php
@@ -43,7 +43,9 @@ function content() {
     if ($uid) {
       esc('Login successful!.');
       $_SESSION['user'] = $uid;?>
-      <p>Click <a <?php href('members.php');?>>here</a> to view the membership list.</p><?php
+      <p>Click <a <?php href('members.php');?>>here</a> to view the membership list.</p>
+      <p>Click <a <?php href('winter-event-responses.php');?>>here</a> to view 
+      the responses for the winter event.</p><?php
       return;
     }
     $params['email_address'] = $_POST['email_address'];
diff --git a/public_html/winter-event-responses.php b/public_html/winter-event-responses.php
new file mode 100644
index 0000000..b99114d
--- /dev/null
+++ b/public_html/winter-event-responses.php
@@ -0,0 +1,99 @@
+<?php
+require_once('includes/utils.php');
+require_once('includes/html-templating.php');
+require_once('includes/database.php');
+require_once('includes/auth.php');
+
+session_start();
+
+function page_title() {
+  return 'Members list';
+}
+
+function content() {
+  if (!check_logged_in()) {?>
+    <h1>Restricted access page</h1><?php
+    return;
+  }?>
+
+  <h1>YCRA Winter Event Responses</h1>
+
+  <p>Below is a list of the winter event ticket form responses (ordered by most 
+  recent first).</p>
+
+  <p>Click <a <?php href('winter-event-responses.php?csv=emails');?>>here</a> to download a CSV
+  file containing the email addresses of all members that have replied to this form.</p>
+  
+  <p>Click <a <?php href('winter-event-responses.php?csv=responses');?>>here</a> 
+  to download a CSV file containing the responses to this form.</p>
+
+  <p>Please note this is still a work in progress.</p><?php
+
+  $result = run_sql('SELECT members.first_name,members.surname,members.email_address,event_tickets.* FROM event_tickets JOIN members ON members.id=member_id WHERE event_id=1 ORDER BY event_tickets.date_added DESC');
+  $members_num = 0;?>
+
+  <table>
+    <tr>
+      <th>Name</th>
+      <th>Email address</th>
+      <th>Date replied</th>
+      <th>Accompanying Adults</th>
+      <th>Dietary Requirements</th>
+    </tr><?php
+    while ($member = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
+      $members_num++;?>
+      <tr>
+        <td><?php esc($member['first_name'] . ' ' . $member['surname']);?></td>
+        <td><?php esc($member['email_address']);?></td>
+        <td><?php
+          esc($member['date_added']
+                      ? date('j M Y', strtotime($member['date_added']))
+                      : '');?>
+        </td><?php
+        $details = json_decode($member['details'], true);
+        foreach ($details as $d) {?>
+          <td><?php if ($d) echo nl2br(esc_str($d));?></td><?php
+        }?>
+      </tr><?php
+    }?>
+  </table><?php
+
+  esc('Total number of responses: ' . $members_num);
+}
+
+if (!empty($_GET['csv']) && $_GET['csv'] == 'emails' && check_logged_in()) {
+  $result = run_sql('SELECT members.email_address FROM event_tickets JOIN members ON members.id=member_id WHERE event_id=1 ORDER BY event_tickets.date_added DESC');
+
+  header('Content-Type: text/csv');
+  header('Content-Disposition: inline; filename="ycra-winter-event-2022-member-emails.csv"');
+
+  while ($member = mysqli_fetch_array($result, MYSQLI_ASSOC))
+    esc($member['email_address'] . ',');
+
+  exit;
+}
+
+if (!empty($_GET['csv']) && $_GET['csv'] == 'responses' && check_logged_in()) {
+  $result = run_sql('SELECT members.first_name,members.surname,members.email_address,event_tickets.* FROM event_tickets JOIN members ON members.id=member_id WHERE event_id=1 ORDER BY event_tickets.date_added DESC');
+
+  header('Content-Type: text/csv');
+  header('Content-Disposition: inline; filename="ycra-winter-event-2022-responses.csv"');
+
+  echo '"First name","Surname","Email address","Response date","Accompanying Adults",'
+      .'"Dietary Requirements"'."\n";
+  while ($member = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
+    esc($member['first_name'] . ',');
+    esc($member['surname'] . ',');
+    esc($member['email_address'] . ',');
+    esc($member['date_added'] ? date('j M Y', strtotime($member['date_added'])) : '');
+    esc(',');
+    $details = json_decode($member['details'], true);
+    foreach ($details as $d)
+      echo '"'.str_replace('"','\'',$d).'",';
+    esc("\n");
+  }
+  exit;
+}
+
+require_once('includes/template.php');
+?>
-- 
2.25.1