From 1430eb51b984be082ad1e513bf8b51b40e9d958d Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Fri, 19 Jan 2018 09:05:00 -0500 Subject: [PATCH] DB Update for registrations notifications Created new table for the notification queue. --- index.php | 2 +- ...0.0.22.sql => create_database_V0.0.23.sql} | 30 ++++++++++++++----- setup/databaseScripts/dbVersions.php | 3 +- .../update_database_V0.0.23.sql | 21 +++++++++++++ 4 files changed, 47 insertions(+), 9 deletions(-) rename setup/databaseScripts/{create_database_V0.0.22.sql => create_database_V0.0.23.sql} (97%) create mode 100644 setup/databaseScripts/update_database_V0.0.23.sql diff --git a/index.php b/index.php index 32e0124..a6e7f6f 100644 --- a/index.php +++ b/index.php @@ -44,7 +44,7 @@ if (!defined('ABSPATH')) { * version from this plugin. */ define('GLM_MEMBERS_REGISTRATIONS_PLUGIN_VERSION', '0.0.1'); -define('GLM_MEMBERS_REGISTRATIONS_PLUGIN_DB_VERSION', '0.0.22'); +define('GLM_MEMBERS_REGISTRATIONS_PLUGIN_DB_VERSION', '0.0.23'); // This is the minimum version of the GLM Members DB plugin require for this plugin. define('GLM_MEMBERS_REGISTRATIONS_PLUGIN_MIN_MEMBERS_REQUIRED_VERSION', '2.10.17'); diff --git a/setup/databaseScripts/create_database_V0.0.22.sql b/setup/databaseScripts/create_database_V0.0.23.sql similarity index 97% rename from setup/databaseScripts/create_database_V0.0.22.sql rename to setup/databaseScripts/create_database_V0.0.23.sql index 4a6b1c9..e396ee8 100644 --- a/setup/databaseScripts/create_database_V0.0.22.sql +++ b/setup/databaseScripts/create_database_V0.0.23.sql @@ -40,7 +40,7 @@ * User selects an event * If the event has available registrations * User selects a date for the registration (or if only one that's selected automatically) - * User can log back into registrations at this point if they've been there before and have a password, create an account, or continue as guest ??? + * User can log back into registrations at this point if they've been there before and have a password, create an ccount, or continue as guest ??? * User selects an available rate type and adds one or more registrants for that rate type * User may loop back to select additional rate type(s) and registrants * User may go back to date selection and request additional rate types and registrants for the addional dates @@ -76,7 +76,7 @@ CREATE TABLE {prefix}management ( reg_org_internal_email TINYTEXT NULL, reg_org_from_email TINYTEXT NULL, reg_payment_methods SMALLINT NULL, -- Payment methods available for all registrations - Bitmap - see payment_method in plugin.ini - reg_proc_methods SMALLINT NULL, -- Creadit Cart payment processing methods available - Bitmap - see proc_method in plugin.ini + reg_proc_methods SMALLINT NULL, -- Credit Cart payment processing methods available - Bitmap - see proc_method in plugin.ini reg_cc_accepts SMALLINT NULL, -- Credit Cards Accepted - Bitmap - See credit_card in plugin.ini -- Authorize.net Credentials reg_authorize_net_login TINYTEXT NULL, @@ -207,7 +207,7 @@ CREATE TABLE {prefix}reg_event ( event INT NULL, -- Pointer to event in Events add-on - False if event record in Events add-on no longer exists event_name TINYTEXT NULL, -- Name of Event so it will always be in the cart data event_code TINYTEXT NULL, -- A short code used to reference this event - can be used to directly select an event to register for - notify_email TINYTEXT NULL, -- E-Mail addresses to recieve notification of a registration other than org_internal_email in management, comma separated + notify_email TINYTEXT NULL, -- E-Mail addresses to receive notification of a registration other than org_internal_email in management, comma separated admin_active BOOLEAN NULL, -- Active flag for admin from Events - If logged in Admin user for this event and this is true then admin user may enter registrations even if active is off. active BOOLEAN NULL, -- Active flag to indicate that this event is available for registrations time_specific BOOLEAN NULL, -- Registration for this event is not date/time specific. Can attend any date/time of event. @@ -235,7 +235,7 @@ CREATE TABLE {prefix}reg_event ( ---- --- Registration Event Time - Information and summary data for a specific event instance (relates to a perticular time record in events) +-- Registration Event Time - Information and summary data for a specific event instance (relates to a particular time record in events) -- A pseudo entry is created if registration is not date/time sensitive for this event. The pseudo entry does not point to an event time. -- These are created the first time a person tries to register for an event instance (time) -- One or more for each event @@ -305,8 +305,8 @@ CREATE TABLE {prefix}reg_rate ( id INT NOT NULL AUTO_INCREMENT, reg_event INT NULL, -- Pointer to reg_event table reg_class INT NULL, -- Pointer to reg_class table - name TINYTEXT NULL, -- Namme of this rate - start_days INT NULL, -- # of days before event time rate becomes available - may be selected as a date then coverted for storage + name TINYTEXT NULL, -- Name of this rate + start_days INT NULL, -- # of days before event time rate becomes available - may be selected as a date then converted for storage end_days INT NULL, -- # of days before event time rate becomes unavailable base_rate FLOAT, -- Base rate to register per_registrant FLOAT, -- Rate per registrant @@ -332,6 +332,22 @@ CREATE TABLE {prefix}reg_notification ( ---- +-- Notification Queue +CREATE TABLE {prefix}notification_queue ( + id INT NOT NULL AUTO_INCREMENT, + reg_notification INT NOT NULL, -- Pointer to the reg_notification table + account INT NOT NULL, -- Pointer to the account table + queued_time DATETIME NOT NULL, -- Creation time + processed_time DATETIME NULL, -- The time this queue was processed. (sent out) + to_email TINYTEXT NOT NULL, -- The To email address + from_email TINYTEXT NOT NULL, -- The From email address + subject TINYTEXT NOT NULL, -- The subject + html_message TEXT NOT NULL, -- The complete html message + PRIMARY KEY (id) +); + +---- + -- An account for a person submitting a registration or a registrant for an event -- Depending on the use of these entries may not have all data included -- A single account may be referenced as a person submitting registrations and/or a person registered for an event @@ -542,7 +558,7 @@ CREATE TABLE {prefix}reg_request_pay_code ( code TINYTEXT NULL, -- Copy of Payment Code text in case payment code is deleted or changed reg_request INT NULL, -- Pointer to the registration request record credit DOUBLE PRECISION NULL, -- Total credit for this payment code - expire_date DATE NULL, -- Expiration Date - NOT USING NOW - Getting from payment_code table allways + expire_date DATE NULL, -- Expiration Date - NOT USING NOW - Getting from payment_code table always PRIMARY KEY (id), INDEX (payment_code), INDEX (reg_request) diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index b8dc1f1..2ae18f1 100644 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -35,7 +35,8 @@ $glmMembersRegistrationsDbVersions = array( '0.0.19' => array('version' => '0.0.19', 'tables' => 15, 'date' => '11/22/2017'), '0.0.20' => array('version' => '0.0.20', 'tables' => 16, 'date' => '01/02/2018'), '0.0.21' => array('version' => '0.0.21', 'tables' => 17, 'date' => '01/08/2018'), - '0.0.22' => array('version' => '0.0.22', 'tables' => 17, 'date' => '01/09/2018') + '0.0.22' => array('version' => '0.0.22', 'tables' => 17, 'date' => '01/09/2018'), + '0.0.23' => array('version' => '0.0.23', 'tables' => 18, 'date' => '01/19/2018'), ); diff --git a/setup/databaseScripts/update_database_V0.0.23.sql b/setup/databaseScripts/update_database_V0.0.23.sql new file mode 100644 index 0000000..4e5fd49 --- /dev/null +++ b/setup/databaseScripts/update_database_V0.0.23.sql @@ -0,0 +1,21 @@ +-- Gaslight Media Members Database - Registratiuons Add-On +-- File Created: 2018-01-19 +-- Database Version: 0.0.23 +-- Database Update From Previous Version Script +-- +-- To permit each query below to be executed separately, +-- all queries must be separated by a line with four dashes + +-- Notification Queue +CREATE TABLE {prefix}notification_queue ( + id INT NOT NULL AUTO_INCREMENT, + reg_notification INT NOT NULL, -- Pointer to the reg_notification table + account INT NOT NULL, -- Pointer to the account table + queued_time DATETIME NOT NULL, -- Creation time + processed_time DATETIME NULL, -- The time this queue was processed. (sent out) + to_email TINYTEXT NOT NULL, -- The To email address + from_email TINYTEXT NOT NULL, -- The From email address + subject TINYTEXT NOT NULL, -- The subject + html_message TEXT NOT NULL, -- The complete html message + PRIMARY KEY (id) +); -- 2.17.1