From dd429fd2bb3ab9a6bd2f65d79180f18c6d2f844f Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Wed, 25 Apr 2018 15:20:29 -0400 Subject: [PATCH] Working on cron task and db update db update for new fields for the notification queue. The rest is setting up the cron task for generating email notices based on renewal dates. --- classes/billingSupport.php | 5 +- classes/notifications.php | 1 + index.php | 2 +- ...xpired.php => billingFlagExpiredUsers.php} | 27 +++- .../{runQueue.php => billingRunQueue.php} | 3 +- models/admin/ajax/billingSetupQueue.php | 101 ++++++++++++ models/admin/ajax/setupQueue.php | 153 ------------------ setup/adminHooks.php | 48 ++++++ ...0.0.23.sql => create_database_V0.0.24.sql} | 10 +- setup/databaseScripts/dbVersions.php | 1 + .../update_database_V0.0.24.sql | 34 ++++ setup/validActions.php | 17 +- 12 files changed, 229 insertions(+), 173 deletions(-) rename models/admin/ajax/{flagExpired.php => billingFlagExpiredUsers.php} (63%) rename models/admin/ajax/{runQueue.php => billingRunQueue.php} (95%) create mode 100644 models/admin/ajax/billingSetupQueue.php delete mode 100644 models/admin/ajax/setupQueue.php rename setup/databaseScripts/{create_database_V0.0.23.sql => create_database_V0.0.24.sql} (97%) create mode 100644 setup/databaseScripts/update_database_V0.0.24.sql diff --git a/classes/billingSupport.php b/classes/billingSupport.php index f671429..821e46a 100644 --- a/classes/billingSupport.php +++ b/classes/billingSupport.php @@ -580,8 +580,9 @@ class GlmBillingSupport return $this->wpdb->get_results( "SELECT * FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts - WHERE renewal_date + INTERVAL 1 YEAR " . ($before ? '-' : '+' ) . " $days DAY = '$curDate' - AND archived <> true" + WHERE renewal_date + INTERVAL 1 YEAR " . ($before ? '-' : '+' ) . " INTERVAL $days DAY = '$curDate' + AND archived <> true", + ARRAY_A ); } diff --git a/classes/notifications.php b/classes/notifications.php index 58fbf62..dcfa912 100644 --- a/classes/notifications.php +++ b/classes/notifications.php @@ -397,6 +397,7 @@ class GlmNotifications $query = "SELECT * FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "notification_queue"; + //$query = " WHERE processed_time IS NULL"; if ( $numb = filter_var( $limit, FILTER_VALIDATE_INT ) ) { $query .= " LIMIT $limit OFFSET 0"; } diff --git a/index.php b/index.php index e100a30..b5ba692 100644 --- a/index.php +++ b/index.php @@ -38,7 +38,7 @@ * version from this plugin. */ define('GLM_MEMBERS_BILLING_PLUGIN_VERSION', '0.0.1'); -define('GLM_MEMBERS_BILLING_PLUGIN_DB_VERSION', '0.0.23'); +define('GLM_MEMBERS_BILLING_PLUGIN_DB_VERSION', '0.0.24'); // This is the minimum version of the GLM Members DB plugin require for this plugin. define('GLM_MEMBERS_BILLING_PLUGIN_MIN_MEMBERS_REQUIRED_VERSION', '2.8.0'); diff --git a/models/admin/ajax/flagExpired.php b/models/admin/ajax/billingFlagExpiredUsers.php similarity index 63% rename from models/admin/ajax/flagExpired.php rename to models/admin/ajax/billingFlagExpiredUsers.php index 3977775..d9f79cb 100644 --- a/models/admin/ajax/flagExpired.php +++ b/models/admin/ajax/billingFlagExpiredUsers.php @@ -38,7 +38,7 @@ require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/billingSupport.php'; * an AJAX call that goes through the WorPress AJAX Handler. * */ -class GlmMembersAdmin_ajax_flagExpired +class GlmMembersAdmin_ajax_billingFlagExpiredUsers { /** @@ -67,15 +67,30 @@ class GlmMembersAdmin_ajax_flagExpired } - public function modelAction( $actionData = false ) { + public function modelAction( $actionData = false ) + { + + echo '
$actionData: ' . print_r( $actionData, true ) . '
'; + return false; - $currentDate = new DateTime(); - echo '
$currentDate: ' . print_r( $currentDate, true ) . '
'; + $days_after_expired = $this->config['settings']['days_after_expired']; + $current_date = date( 'Y-m-d' ); // Update All expired accounts so they can only be in members only not admin. - // TODO: Find all member contacts (by ref_dest) with accounts that are expired. + // Find all member contacts (by ref_dest) with accounts that are expired. // With those set their contact_role to 30 (login only) - + if ( $days_after_expired = filter_var( $days_after_expired, FILTER_VALIDATE_INT ) ) { + $this->wpdb->query( + "UPDATE " . GLM_MEMBERS_CONTACTS_PLUGIN_DB_PREFIX. "contacts + SET contact_role = 30 + WHERE ref_dest IN ( + SELECT ref_dest + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE renewal_date + INTERVAL 1 YEAR + INTERVAL $days_after_expired DAY <= '$current_date' + ) + AND contact_role <> 30" + ); + } wp_die(); } diff --git a/models/admin/ajax/runQueue.php b/models/admin/ajax/billingRunQueue.php similarity index 95% rename from models/admin/ajax/runQueue.php rename to models/admin/ajax/billingRunQueue.php index 28e674e..c77a936 100644 --- a/models/admin/ajax/runQueue.php +++ b/models/admin/ajax/billingRunQueue.php @@ -40,7 +40,7 @@ require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/billingSupport.php'; * an AJAX call that goes through the WorPress AJAX Handler. * */ -class GlmMembersAdmin_ajax_runQueue +class GlmMembersAdmin_ajax_billingRunQueue { /** @@ -78,6 +78,7 @@ class GlmMembersAdmin_ajax_runQueue foreach ( $queued as $queue ) { $Notifications->sendEmailNotification( $queue['notification_type'], $queue['account'] ); $Notifications->markQueueProcessed( $queue['id'] ); + // $Notifications->deleteQueue( $queue['id'] ); } } wp_die(); diff --git a/models/admin/ajax/billingSetupQueue.php b/models/admin/ajax/billingSetupQueue.php new file mode 100644 index 0000000..13f1dda --- /dev/null +++ b/models/admin/ajax/billingSetupQueue.php @@ -0,0 +1,101 @@ + + * @license http://www.gaslightmedia.com Gaslightmedia + * @version 0.1 + */ + +require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/notifications.php'; +require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/billingSupport.php'; +/** + * Steve Note... + * + * You can get to this using the following URL. + * + * {host}/wp-admin/admin-ajax.php?action=glm_members_admin_ajax&glm_action=setupQueue + * + * You should be able to do this as POST or GET and should be able to add and read additional parameters. + * I added a "mystuff" parameter to the URL above and it does output from the code in the + * modelAction() function below. + * + * To add another model under models/admin/ajax all you need to do is create it and add it to the + * setup/validActions.php file. + * + */ + +// Load Members data abstract +// require_once GLM_MEMBERS_PLUGIN_CLASS_PATH.'/data/dataImages.php'; + +/** + * This class performs the work of handling images passed to it via + * an AJAX call that goes through the WorPress AJAX Handler. + * + */ +class GlmMembersAdmin_ajax_billingSetupQueue +{ + + /** + * WordPress Database Object + * + * @var $wpdb + * @access public + */ + public $wpdb; + /** + * Plugin Configuration Data + * + * @var $config + * @access public + */ + public $config; + + public function __construct ($wpdb, $config) + { + + // Save WordPress Database object + $this->wpdb = $wpdb; + + // Save plugin configuration object + $this->config = $config; + + } + + public function modelAction( $actionData = false ) { + // Get all notifications that are done by date. + $Notifications = new GlmNotifications( $this->wpdb, $this->config ); + $notifications = $Notifications->getNotificationsWithSendByDate(); + // echo '
$notifications: ' . print_r( $notifications, true ) . '
'; + + // Get all invoices that are unpaid. + $billingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); + + foreach ( $notifications as $notice ) { + echo '
$notice: ' . print_r( $notice, true ) . '
'; + switch( $notice['send_date_period'] ) { + case $this->config['send_date_when_numb']['After']: + $before = false; + break; + case $this->config['send_date_when_numb']['Before']: + $before = true; + break; + } + $accounts = $billingSupport->getAccountsRenewing( $notice['send_date_number'], $before ); + echo '
$accounts: ' . print_r( $accounts, true ) . '
'; + if ( isset( $accounts ) && is_array( $accounts ) && !empty( $accounts ) ) { + foreach ( $accounts as $account ) { + $Notifications->queueNotice( $notice['id'], $account['id'] ); + } + } + } + + wp_die(); + } +} diff --git a/models/admin/ajax/setupQueue.php b/models/admin/ajax/setupQueue.php deleted file mode 100644 index 9c6e0f4..0000000 --- a/models/admin/ajax/setupQueue.php +++ /dev/null @@ -1,153 +0,0 @@ - - * @license http://www.gaslightmedia.com Gaslightmedia - * @version 0.1 - */ - -require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/notifications.php'; -require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/billingSupport.php'; -/** - * Steve Note... - * - * You can get to this using the following URL. - * - * {host}/wp-admin/admin-ajax.php?action=glm_members_admin_ajax&glm_action=setupQueue - * - * You should be able to do this as POST or GET and should be able to add and read additional parameters. - * I added a "mystuff" parameter to the URL above and it does output from the code in the - * modelAction() function below. - * - * To add another model under models/admin/ajax all you need to do is create it and add it to the - * setup/validActions.php file. - * - */ - -// Load Members data abstract -// require_once GLM_MEMBERS_PLUGIN_CLASS_PATH.'/data/dataImages.php'; - -/** - * This class performs the work of handling images passed to it via - * an AJAX call that goes through the WorPress AJAX Handler. - * - */ -class GlmMembersAdmin_ajax_setupQueue -{ - - /** - * WordPress Database Object - * - * @var $wpdb - * @access public - */ - public $wpdb; - /** - * Plugin Configuration Data - * - * @var $config - * @access public - */ - public $config; - - public function __construct ($wpdb, $config) - { - - // Save WordPress Database object - $this->wpdb = $wpdb; - - // Save plugin configuration object - $this->config = $config; - - } - - public function modelAction( $actionData = false ) { - // Get all notifications that are done by date. - $Notifications = new GlmNotifications( $this->wpdb, $this->config ); - $notifications = $Notifications->getNotificationsWithSendByDate(); - echo '
$notifications: ' . print_r( $notifications, true ) . '
'; - - // Get all invoices that are unpaid. - $billingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); - //$unpaid_invoices = $billingSupport->getUnPaidInvoices(); - //echo '
$unpaid_invoices: ' . print_r( $unpaid_invoices, true ) . '
'; - - foreach ( $notifications as $notice ) { - echo '
$notice: ' . print_r( $notice, true ) . '
'; - switch( $notice['send_date_period'] ) { - case $this->config['send_date_when_numb']['After']: - $before = false; - break; - case $this->config['send_date_when_numb']['Before']: - $before = true; - break; - } - $accounts = $billingSupport->getAccountsRenewing( $notice['send_date_number'], $before ); - echo '
$accounts: ' . print_r( $accounts, true ) . '
'; - } - - // Loop through the invoices and for each one see if any notices are to be sent out. - $currentDate = new DateTime(); - echo '
$currentDate: ' . print_r( $currentDate, true ) . '
'; - // foreach ( $unpaid_invoices as $invoice ) { - // $due_date = $invoice['due_date']; - // - // // Loop through the notifications and see if this unpaid one is due. - // foreach ( $notifications as $notice ) { - // // Need due date set in this loop each time. - // $dueDate = new DateTime( $due_date ); - // // Is this before or after - // switch ( $notice['send_date_when'] ) { - // case $this->config['send_date_when_numb']['Before']: - // echo '

Before

'; - // $dueDate->modify( - // '-' . $notice['send_date_number'] . ' '. - // $this->config['send_date_period'][$notice['send_date_period']] . - // (($notice['send_date_number'] > 1) ? 's': '') - // ); - // echo '
$dueDate: ' . print_r( $dueDate, true ) . '
'; - // $diff = $dueDate->diff( $currentDate ); - // // When the diff is 0 days then the notice needs to go out. - // - // $days_from = $diff->format( '%a' ); - // echo '
$days_from: ' . print_r( $days_from, true ) . '
'; - // // Check to see if this date occurs today. - // if ( $days_from === '0' ) { - // // Add the notice id and account id to the queue - // $Notifications->queueNotice( $notice['id'], $invoice['account'] ); - // } - // - // break; - // case $this->config['send_date_when_numb']['After']: - // echo '

After

'; - // $dueDate->modify( - // '+' . $notice['send_date_number'] . ' '. - // $this->config['send_date_period'][$notice['send_date_period']] . - // (($notice['send_date_number'] > 1) ? 's': '') - // ); - // echo '
$dueDate: ' . print_r( $dueDate, true ) . '
'; - // $diff = $dueDate->diff( $currentDate ); - // $days_from = $diff->format( '%a' ); - // echo '
$days_from: ' . print_r( $days_from, true ) . '
'; - // // Check to see if this date occurs today. - // if ( $days_from === '0' ) { - // // Create the notice email - // $Notifications->queueNotice( $notice['id'], $invoice['account'] ); - // } - // break; - // default: - // break; - // } - // } - // } - - wp_die(); - } -} diff --git a/setup/adminHooks.php b/setup/adminHooks.php index cacc7c5..07c3e08 100644 --- a/setup/adminHooks.php +++ b/setup/adminHooks.php @@ -33,3 +33,51 @@ add_filter( 9, 1 ); + +// add_filter( +// 'glm_associate_cron_request', +// function( $cron_task ){ +// $new_cron = array( +// array( +// 'menu' => 'ajax', +// 'action' => 'billingFlagExpiredUsers', +// 'daysOfWeek' => range( 1, 7 ), +// 'times' => range( 1, 24 ), +// 'params' => array() +// ) +// ); +// return array_merge( $cron_task, $new_cron ); +// } +// ); + +// add_filter( +// 'glm_associate_cron_request', +// function( $cron_task ) { +// $new_cron = array( +// array( +// 'menu' => 'ajax', +// 'action' => 'billingSetupQueue', +// 'daysOfWeek' => range( 1, 7 ), +// 'times' => range( 1, 24 ), +// 'params' => array() +// ) +// ); +// return array_merge( $cron_task, $new_cron ); +// } +// ); + +// add_filter( +// 'glm_associate_cron_request', +// function( $cron_task ) { +// $new_cron = array( +// array( +// 'menu' => 'ajax', +// 'action' => 'billingRunQueue', +// 'daysOfWeek' => range( 1, 7 ), +// 'times' => range( 1, 24 ), +// 'params' => array() +// ) +// ); +// return array_merge( $cron_task, $new_cron ); +// } +// ); diff --git a/setup/databaseScripts/create_database_V0.0.23.sql b/setup/databaseScripts/create_database_V0.0.24.sql similarity index 97% rename from setup/databaseScripts/create_database_V0.0.23.sql rename to setup/databaseScripts/create_database_V0.0.24.sql index 170c7af..e8fec7f 100644 --- a/setup/databaseScripts/create_database_V0.0.23.sql +++ b/setup/databaseScripts/create_database_V0.0.24.sql @@ -1,6 +1,6 @@ -- Gaslight Media Billing Module -- File Created: 11/08/2017 --- Database Version: 0.0.23 +-- Database Version: 0.0.24 -- Database Creation Script -- -- To permit each query below to be executed separately, @@ -181,7 +181,13 @@ CREATE TABLE {prefix}notification_queue ( id INT NOT NULL AUTO_INCREMENT, notification_type INT NOT NULL, -- ref to notification type account INT NOT NULL, -- ref to account - PRIMARY KEY (id) + queued_time DATETIME NOT NULL, -- Creation time + processed_time DATETIME NULL, -- The time this queue was processed. (sent out) + PRIMARY KEY (id), + INDEX (notification_type), + INDEX (account), + INDEX (queued_time), + INDEX (processed_time) ); ---- diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index 0975228..964fb44 100644 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -37,5 +37,6 @@ $glmMembersBillingDbVersions = array( '0.0.21' => array('version' => '0.0.21', 'tables' => 15), '0.0.22' => array('version' => '0.0.22', 'tables' => 15), '0.0.23' => array('version' => '0.0.23', 'tables' => 15), + '0.0.24' => array('version' => '0.0.24', 'tables' => 15), ); diff --git a/setup/databaseScripts/update_database_V0.0.24.sql b/setup/databaseScripts/update_database_V0.0.24.sql new file mode 100644 index 0000000..c50335f --- /dev/null +++ b/setup/databaseScripts/update_database_V0.0.24.sql @@ -0,0 +1,34 @@ +-- Gaslight Media Billing Database +-- File Created: 04/25/2018 +-- Database Version: 0.0.24 +-- +-- To permit each query below to be executed separately, +-- all queries must be separated by a line with four dashes + +-- Add queued_time field to notification_queue table +ALTER TABLE {prefix}notification_queue ADD queued_time DATETIME NOT NULL; -- Creation time + +---- + +-- Add processed_time field to notification_queue table +ALTER TABLE {prefix}notification_queue ADD processed_time DATETIME NOT NULL; -- Processed time + +---- + +-- Add index for notification_type +CREATE INDEX queue_noticification_type ON {prefix}notification_queue (notification_type); + +---- + +-- Add index for account +CREATE INDEX queue_account ON {prefix}notification_queue (account); + +---- + +-- Add index for queue time +CREATE INDEX queue_queued_time ON {prefix}notification_queue (queued_time); + +---- + +-- Add index for processed time +CREATE INDEX queue_processed_time ON {prefix}notification_queue (processed_time); diff --git a/setup/validActions.php b/setup/validActions.php index 2419d94..e0b1806 100644 --- a/setup/validActions.php +++ b/setup/validActions.php @@ -60,14 +60,15 @@ $glmMembersBillingAddOnValidActions = array( 'adminActions' => array( 'ajax' => array( - 'setupQueue' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'runQueue' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'billingAccount' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'invoices' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'invoiceTypes' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'createNewInvoices' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'accountsListExport' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'paymentsListExport' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'billingSetupQueue' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'billingRunQueue' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'billingAccount' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'invoices' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'invoiceTypes' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'createNewInvoices' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'accountsListExport' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'paymentsListExport' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'billingFlagExpiredUsers' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, ), 'management' => array( 'billing' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, -- 2.17.1