From b071a06b8031af79d7a22edcf160c09e6dcd31d6 Mon Sep 17 00:00:00 2001
From: Laury GvR <laury@gaslightmedia.com>
Date: Wed, 17 Jul 2019 19:39:59 -0400
Subject: [PATCH] Get more complete events data through recurrences

---
 setup/routes/dashboard.php | 77 +++++++++++++++++++++++++++++++++-----
 1 file changed, 67 insertions(+), 10 deletions(-)

diff --git a/setup/routes/dashboard.php b/setup/routes/dashboard.php
index 2a15efb..edbd26a 100644
--- a/setup/routes/dashboard.php
+++ b/setup/routes/dashboard.php
@@ -100,7 +100,7 @@ class GLMA_Dashboard_Rest_Controller
                         
                         if ( defined("GLM_MEMBERS_PLUGIN_PATH") ) {
                             $members = $this->get_members_summary();
-                            $summary['members'] = $members;
+                            //$summary['members'] = $members;
                         }
                         if ( defined("GLM_MEMBERS_EVENTS_PLUGIN_PATH") ) {
                             $events = $this->get_events_summary();
@@ -357,15 +357,72 @@ class GLMA_Dashboard_Rest_Controller
         // !!!
         // WE'LL NEED TO GET EVENT RECURRENCES AS WELL (for start/end date)
         // !!!
-        $fields = "id, name, name_slug, descr, image";
-        $table = GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."events";
-        $where = "status IN (".$this->config['status_numb']['Active'].")";
-        $sql = "
-            SELECT $fields
-            FROM $table
-            WHERE $where;";
-            
-        $events = $this->wpdb->get_results( $sql );
+
+        
+        // $recurrence_fields = "R.id, R.name, R.event, R.start_date, R.start_time, R.end_time, R.from_date, R.to_date";
+        // $recurrence_table = GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."recurrences AS R, $event_table AS E";
+        // $recurrence_where = "R.event = E.id";
+
+        $recurrence_fields = "R.id, R.name, R.event, R.start_date, R.start_time, R.end_time, R.from_date, R.to_date";
+        $recurrence_table = GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."recurrences AS R";
+        $recurrence_where = "true";//"R.event = 1";
+        $recurrence_subquery = "SELECT $recurrence_fields FROM $recurrence_table WHERE $recurrence_where";
+
+        $event_fields = "id, name, name_slug, descr, image, ($recurrence_subquery) AS recurrences";
+        $event_table = GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX."events";
+        $event_where = "status IN (".$this->config['status_numb']['Active'].")";
+
+        
+
+
+        // $event_sql = "
+        //     SELECT $event_fields
+        //     FROM $event_table AS E
+        //     WHERE $event_where;
+        // ";
+
+        $event_sql = "
+            SELECT E.id, E.name, E.name_slug, R.start_date, R.start_time, R.end_time, R.from_date, R.to_date, R.id as recurrence_id
+            FROM $event_table AS E
+            INNER JOIN $recurrence_table AS R ON (E.id = R.event)
+            WHERE $event_where;
+        ";
+
+        $limit = 5;
+        $events = $this->wpdb->get_results(
+            $this->wpdb->prepare(
+                "SELECT E.*,T1.start_time as start,T2.end_time as end
+                   FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "events E,
+                        " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times T1,
+                        " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times T2
+                  WHERE E.status = 10
+                    AND T1.event = E.id AND T1.start_time IN (SELECT MIN(start_time) FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times WHERE event = E.id AND end_time <= %s)
+                    AND T2.event = E.id AND T2.end_time IN (SELECT MAX(end_time) FROM " . GLM_MEMBERS_EVENTS_PLUGIN_DB_PREFIX . "times WHERE event = E.id AND end_time >= %s)
+                 ORDER BY start, end
+                 LIMIT %d
+                 OFFSET 0",
+                date( 'Y-m-d' ),
+                date( 'Y-m-d' ),
+                $limit
+            ),
+            ARRAY_A
+        );
+          
+        // $event_sql = $recurrence_subquery;
+        // $events = $this->wpdb->get_results( $event_sql );
+
+
+        //$recurrence_sql = "
+        //    SELECT $recurrence_fields
+        //    FROM $recurrence_table
+        //    WHERE $recurrence_where
+        //";
+
+        
+
+        //$recurrences = $this->wpdb->get_results( $recurrence_sql );
+
+        //$events[0]->recurrences = $recurrences;
 
         return $events;
     }
-- 
2.17.1