From 7a4dc819ebd5d43832c80aea3aec8f10c3c27b6d Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 15 Dec 2016 11:05:03 -0500 Subject: [PATCH] Finishing up the import scripting for coupons. This is based on coupon from mackinawcity database. It is the most current db structure for our old coupon database. --- models/admin/management/coupons.php | 400 ++++++++++++++++-- .../create_database_V0.0.3.sql | 4 +- views/admin/management/coupons.html | 72 ++++ 3 files changed, 449 insertions(+), 27 deletions(-) diff --git a/models/admin/management/coupons.php b/models/admin/management/coupons.php index d6e5141..7e12704 100644 --- a/models/admin/management/coupons.php +++ b/models/admin/management/coupons.php @@ -53,20 +53,34 @@ class GlmMembersAdmin_management_coupons extends GlmDataCouponsManagement * @access public */ public $settings = array(); + /** + * categories + * + * @var array + * @access public + */ + public $categories = array(); + /** + * oldCatMap + * + * @var array + * @access public + */ + public $oldCatMap = array(); /** * Constructor * - * This contructor performs the work for this model. This model returns + * This constructor performs the work for this model. This model returns * an array containing the following. * * 'status' * - * True if successfull and false if there was a fatal failure. + * True if successful and false if there was a fatal failure. * * 'view' * - * A suggested view name that the contoller should use instead of the + * A suggested view name that the controller should use instead of the * default view for this model or false to indicate that the default view * should be used. * @@ -97,6 +111,7 @@ class GlmMembersAdmin_management_coupons extends GlmDataCouponsManagement * modelAction * * @param bool $actionData + * * @access public * @return void */ @@ -108,7 +123,7 @@ class GlmMembersAdmin_management_coupons extends GlmDataCouponsManagement $import_result = false; $settings_updated = false; $settings_update_error = false; - $coupon_settings = false; + $coupon_settings = false; $icalFeedResult = false; $import_feeds = false; $option2 = false; @@ -118,6 +133,51 @@ class GlmMembersAdmin_management_coupons extends GlmDataCouponsManagement } switch ($option) { + case 'couponimport': + $import = false; + if (isset($_REQUEST['import'])) { + $import = filter_var( $_REQUEST['import'], FILTER_VALIDATE_BOOLEAN ); + } + if ( $import ) { + $import_result = '
$_REQUEST: ' . print_r( $_REQUEST, true ) . '
'; + $db_host = filter_var( $_REQUEST['db_host'], FILTER_SANITIZE_STRING ); + $db_name = filter_var( $_REQUEST['db_name'], FILTER_SANITIZE_STRING ); + $db_user = filter_var( $_REQUEST['db_user'], FILTER_SANITIZE_STRING ); + $db_password = filter_var( $_REQUEST['db_password'], FILTER_SANITIZE_STRING ); + $this->connectPostgresDb( $db_host, $db_name, $db_user, $db_password ); + + $this->settings = filter_var_array( + $_REQUEST, + array( + 'schema' => FILTER_SANITIZE_STRING, + 'cattablename' => FILTER_SANITIZE_STRING, + 'tablename' => FILTER_SANITIZE_STRING, + 'sdate' => array( + 'filter' => FILTER_VALIDATE_REGEXP, + 'options' => array( + 'regexp' => '%[0-9]{2}/[0-9]{2}/[0-9]{4}%' + ) + ) + + ) + ); + // Add the categories + $this->addCategories(); + $import_result .= '
$this->settings: ' . print_r($this->settings, true) . '
'; + $import_result .= $this->addCoupons(); + } + + break; + + case 'couponImagesImport': + $this->image_owner = filter_var( $_REQUEST['img_owner_id'], FILTER_SANITIZE_STRING ); + if ( $this->image_owner ) { + $this->importCouponImages(); + $import_result = '

All images imported

'; + } else { + $import_result = '

Failure: Not given owner id.

'; + } + break; case 'settings': @@ -144,38 +204,39 @@ class GlmMembersAdmin_management_coupons extends GlmDataCouponsManagement if (isset($_REQUEST['option2'])) { $option2 = $_REQUEST['option2']; } - switch($option2) { - // Update the settings and redisplay the form - case 'submit': - // Update the coupon management settings - $coupon_settings = $this->updateEntry(1); - if ($coupon_settings['status']) { - $settings_updated = true; - } else { - $settings_update_error = true; - } - break; + // Update the settings and redisplay the form + case 'submit': - // Default is to get the current settings and display the form - default: + // Update the coupon management settings + $coupon_settings = $this->updateEntry(1); + if ($coupon_settings['status']) { + $settings_updated = true; + } else { + $settings_update_error = true; + } - // Try to get the first (should be only) entry for general settings. - $coupon_settings = $this->editEntry(1); - //echo '
$coupon_settings: ' . print_r( $coupon_settings, true ) . '
'; + break; - if ($coupon_settings === false) { + // Default is to get the current settings and display the form + default: - if (GLM_MEMBERS_PLUGIN_ADMIN_DEBUG) { - glmMembersAdmin::addNotice("  /models/admin/management/coupons.php: Unable to load coupons management settings.", 'Alert'); - } + // Try to get the first (should be only) entry for general settings. + $coupon_settings = $this->editEntry(1); + //echo '
$coupon_settings: ' . print_r( $coupon_settings, true ) . '
'; + if ($coupon_settings === false) { + + if (GLM_MEMBERS_PLUGIN_ADMIN_DEBUG) { + glmMembersAdmin::addNotice("  /models/admin/management/coupons.php: Unable to load coupons management settings.", 'Alert'); } - break; + } + + break; } @@ -210,6 +271,295 @@ class GlmMembersAdmin_management_coupons extends GlmDataCouponsManagement } + /** + * connectPostgresDb + * + * Make a connection to the given database for the site. (postgres) + * Sets the $this->dbh with the postgres database connection + * + * @param mixed $db_host + * @param mixed $db_name + * @param mixed $db_user + * @param mixed $db_password + * + * @access public + * @return void + */ + public function connectPostgresDb($db_host, $db_name, $db_user, $db_password) + { + $conn_str = "pgsql:"; + if ( $db_host ) { + $conn_part[] = "host={$db_host}"; + } + if ( $db_name ) { + $conn_part[] = "dbname={$db_name}"; + } + if ( $db_user ) { + $conn_part[] = "user={$db_user}"; + } + if ( $db_password ) { + $conn_part[] = "password={$db_password}"; + } + if ( !empty($conn_part) ) { + $conn_str .= implode( " ", $conn_part ); + } + $driver_options = array( + PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_BOTH, + ); + try { + $this->dbh = new PDO($conn_str, null, null, $driver_options); + $this->dbh->setAttribute( + PDO::ATTR_ERRMODE, + PDO::ERRMODE_EXCEPTION + ); + } catch(PDOException $e) { + echo '
$e: ' . print_r($e, true) . '
'; + wp_die(); + } + } + + /** + * Add coupons from the old site + * + * Based on Mackinawcity. Which is a current version of the coupon database + * structure. The may be some older ones that we'll have to make + * adjustments for the categories or some fields in the coupon table. + * + * @access public + * @return void + */ + public function addCoupons() + { + // clear the coupons data tables + $this->clearData(); + $sql = " + SELECT * + FROM {$this->settings['schema']}.{$this->settings['tablename']}"; + try { + $coupons = $this->dbh->query( $sql )->fetchAll(PDO::FETCH_ASSOC); + foreach ( $coupons as $coupon ) { + $refType = $member_id = $found_id = null; + if ( isset( $coupon['member'] ) && $coupon['member'] ) { + // get the new member id + $sql = " + SELECT id + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members + WHERE old_member_id = " . esc_sql( trim( $coupon['member'] ) ); + $found_id = $this->wpdb->get_row( $sql, ARRAY_A ); + if ( $found_id ) { + $member_id = $found_id['id']; + $refType = $this->config['ref_type_numb']['Member']; + } + } + $coupon_data = array( + 'status' => ( ( $coupon['active'] == '1' ) + ? $this->config['status_numb']['Active'] + : $this->config['status_numb']['Inactive'] ), + 'created' => $coupon['sdate'], + 'updated' => $coupon['sdate'], + 'start_date' => $coupon['sdate'], + 'end_date' => $coupon['edate'], + 'expire' => $coupon['expiration'], + 'name' => $coupon['title'], + 'ref_type' => $refType, + 'ref_dest' => $member_id, + 'descr' => strip_tags( trim( $coupon['description'] ) ), + 'image' => $coupon['image'], + 'url' => $coupon['url'], + ); + $coupon_data_format = array( + '%d', + '%s', + '%s', + '%s', + '%s', + '%s', + '%s', + '%d', + '%d', + '%s', + '%s', + '%s', + ); + $this->wpdb->insert( + GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . 'coupons', + $coupon_data, + $coupon_data_format + ); + $coupon_id = $this->wpdb->insert_id; + var_dump( $coupon_id ); + echo '
$couponId: from insert ' . print_r( $coupon_id, true ) . '
'; + if ( !$coupon_id ) { + die( 'something is wrong no couponId' ); + } + $coupon_abstract = new GlmDataCoupons( $this->wpdb, $this->config ); + $coupon_abstract->updateSlug( $coupon_id ); + if ( $coupon['category'] ) { + // find out if the category given in coupon is int or text + $cat_id = filter_var( $coupon['category'], FILTER_VALIDATE_INT ); + if ( $cat_id ) { + $this->wpdb->insert( + GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . 'coupon_categories', + array( + 'coupon' => $coupon_id, + 'category' => $this->oldCatMap[$cat_id] + ), + array( + '%d', + '%d' + ) + ); + } else { + // do stuff here for text value + $cat_id = $this->getCategoryId( $coupon['category'] ); + $this->wpdb->insert( + GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . 'coupon_categories', + array( + 'coupon' => $coupon_id, + 'category' => $cat_id + ), + array( + '%d', + '%d' + ) + ); + } + } + } + } catch( PDOException $e ) { + echo '
$e: ' . print_r( $e, true ) . '
'; + } + return 'Testing'; + } + /** + * addCategories + * + * Checks to see if there's an Coupon Category already in the database. + * If not it will create the entry. + * Sets up the class variables + * $this->categories (contains all the categories that get added keyed with + * category id) + * $this->oldCatMap (map of the old category id to new category ids) + * + * @access public + * @return void + */ + public function addCategories() + { + $category_label = 'name'; + $return = ''; + $sql = " + SELECT * + FROM {$this->settings['schema']}.{$this->settings['cattablename']} + ORDER BY id"; + $return .= '
$sql: ' . print_r($sql, true) . '
'; + try { + $categories = $this->dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC); + $return .= '
$categories: ' . print_r($categories, true) . '
'; + foreach ( $categories as $category ) { + // first check to see if the category is already there + $sql = " + SELECT id + FROM " . GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . "categories + WHERE name = '" . esc_sql( trim($category[$category_label]) ) . "'"; + $return .= '
$sql: ' . print_r($sql, true) . '
'; + $found = $this->wpdb->get_row($sql, ARRAY_A); + $return .= '
$found: ' . print_r($found, true) . '
'; + if ( !isset($found) ) { + $this->wpdb->insert( + GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . 'categories', + array( 'name' => trim($category[$category_label]) ), + array( '%s' ) + ); + $catid = $this->wpdb->insert_id; + $this->categories[$catid] = trim($category[$category_label]); + $this->oldCatMap[$category['id']] = $catid; + } else { + $this->categories[$found['id']] = trim($category[$category_label]); + $this->oldCatMap[$category['id']] = $found['id']; + } + } + } catch(PDOException $e) { + echo '
$e: ' . print_r($e, true) . '
'; + die('end here'); + } + return $return; + } + /** + * clearData + * + * Clears the coupon and coupon_category data from the wordpress database. + * + * @access public + * @return void + */ + public function clearData() + { + $this->wpdb->query( "DELETE FROM " . GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . "coupons" ); + $this->wpdb->query( "DELETE FROM " . GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . "coupon_categories" ); + } + /** + * getCategoryId + * + * Get the coupon category id given a coupon category name. + * If the coupon category is not found by the name then add it. + * + * @param mixed $category + * + * @access public + * @return void + */ + public function getCategoryId( $category ) + { + return $this->wpdb->get_var( + $this->wpdb->prepare( + "SELECT id + FROM " . GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . "categories + WHERE name = %s", + $category + ) + ); + } + + /** + * importCouponImages + * + * Grabs the coupon images from is0 server and updates each coupon record + * with the new image name. + * + * @access public + * @return void + */ + public function importCouponImages() + { + require_once GLM_MEMBERS_PLUGIN_PATH.'/models/admin/ajax/imageUpload.php'; + $ImageUpload = new GlmMembersAdmin_ajax_imageUpload($this->wpdb, $this->config); + // get all coupons with images + $sql = " + SELECT id,image + FROM " . GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX. "coupons + WHERE image != '' AND image IS NOT NULL"; + $results = $this->wpdb->get_results($sql, ARRAY_A); + $out = '
$results: ' . print_r($results, true) . '
'; + $imgUrl = 'http://is0.gaslightmedia.com/' . $this->image_owner . '/original/'; + foreach ( $results as $coupon ) { + $imageFullUrl = $imgUrl . $coupon['image']; + $res = $ImageUpload->storeImage($imageFullUrl); + if ( isset( $res['newFileName']) && $res['newFileName'] ) { + $this->wpdb->update( + GLM_MEMBERS_COUPONS_PLUGIN_DB_PREFIX . 'coupons', + array( + 'image' => $res['newFileName'] + ), + array( 'id' => $coupon['id'] ), + array( '%s' ), + array( '%d' ) + ); + } + } + return $out; + } + } ?> diff --git a/setup/databaseScripts/create_database_V0.0.3.sql b/setup/databaseScripts/create_database_V0.0.3.sql index 89b9ee9..f87b6a2 100644 --- a/setup/databaseScripts/create_database_V0.0.3.sql +++ b/setup/databaseScripts/create_database_V0.0.3.sql @@ -19,8 +19,8 @@ CREATE TABLE {prefix}coupons ( created DATETIME NULL, -- Create date updated DATETIME NULL, -- Update date approved DATETIME NULL, -- Approved date - start_date DATE NULL, -- Starting date of the coupon - end_date DATE NULL, -- Ending date of the coupon + start_date DATE NULL, -- Starting date of the coupon + end_date DATE NULL, -- Ending date of the coupon expire DATE NULL, -- Expire date of the coupon name TINYTEXT NULL, -- Coupon name name_slug TINYTEXT NULL, -- Coupon name slug diff --git a/views/admin/management/coupons.html b/views/admin/management/coupons.html index fbef15c..7ab60f9 100644 --- a/views/admin/management/coupons.html +++ b/views/admin/management/coupons.html @@ -2,6 +2,7 @@

Settings + Import

@@ -63,7 +64,78 @@ + + + {if $importResult} + + + + + {else} + + + + + + + + + + {/if} + +
Return to Import Coupons
+ {$importResult} +

Import from Gaslight Coupons

+
+ + + + +
+
+ Database to import from: + + + + + + + + +
+
+
+
+ Settings: + + + + + + + + +
+ + +
+
+
+
+ + + +
+
+ Image Style Owner: + + +
+ +
+
+