From 9a691ad9e22036e61edb379953bca5be16f1903d Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 29 Nov 2018 16:31:00 -0500 Subject: [PATCH] More db updates for billing. Adding county to the billing account table. Adding more settings and management options for the plugin. --- classes/data/dataAccounts.php | 12 +++ classes/data/dataManagement.php | 7 ++ classes/data/dataSettings.php | 35 +++++++++ index.php | 2 +- models/admin/billing/invoicing.php | 7 +- models/admin/management/importAccounts.php | 34 ++++++++- ...0.0.28.sql => create_database_V0.0.29.sql} | 10 ++- setup/databaseScripts/dbVersions.php | 1 + .../update_database_V0.0.28.sql | 4 +- .../update_database_V0.0.29.sql | 40 ++++++++++ views/admin/billing/editAccount.html | 27 ++++++- views/admin/management/billing.html | 15 ++++ views/admin/settings/billing.html | 75 +++++++++++++++++-- 13 files changed, 247 insertions(+), 22 deletions(-) rename setup/databaseScripts/{create_database_V0.0.28.sql => create_database_V0.0.29.sql} (95%) create mode 100644 setup/databaseScripts/update_database_V0.0.29.sql diff --git a/classes/data/dataAccounts.php b/classes/data/dataAccounts.php index e12270c..632baae 100644 --- a/classes/data/dataAccounts.php +++ b/classes/data/dataAccounts.php @@ -340,6 +340,18 @@ class GlmDataAccounts extends GlmDataAbstract 'required' => false, ), + // Billing County + 'billing_county' => array( + 'field' => 'billing_county', + 'type' => 'pointer', + 'p_table' => GLM_MEMBERS_PLUGIN_DB_PREFIX . 'counties', + 'p_field' => 'name', + 'p_orderby' => 'name', + 'p_blank' => false, + 'use' => 'a', + 'required' => false, + ), + // Customer Profile Id // 'customer_profile_id' => array( // 'field' => 'customer_profile_id', diff --git a/classes/data/dataManagement.php b/classes/data/dataManagement.php index d930f0a..5bb8f29 100644 --- a/classes/data/dataManagement.php +++ b/classes/data/dataManagement.php @@ -218,6 +218,13 @@ class GlmDataBillingManagement extends GlmDataAbstract 'use' => 'a' ), + // Use uptra invoice template + 'uptravel_invoice_template' => array( + 'field' => 'uptravel_invoice_template', + 'type' => 'checkbox', + 'use' => 'a', + ), + ); } diff --git a/classes/data/dataSettings.php b/classes/data/dataSettings.php index 024d052..7022bd1 100644 --- a/classes/data/dataSettings.php +++ b/classes/data/dataSettings.php @@ -272,6 +272,41 @@ class GlmDataBillingSettings extends GlmDataAbstract 'use' => 'a', ), + // Renewal Date static + 'renewal_day_static' => array( + 'field' => 'renewal_day_static', + 'type' => 'checkbox', + 'use' => 'a', + ), + + // Billing Counties enabled + 'billing_county_enabled' => array( + 'field' => 'billing_county_enabled', + 'type' => 'checkbox', + 'use' => 'a', + ), + + // Renewal Day + 'renewal_day' => array( + 'field' => 'renewal_day', + 'type' => 'text', + 'use' => 'a', + ), + + // Renewal Month + 'renewal_month' => array( + 'field' => 'renewal_month', + 'type' => 'text', + 'use' => 'a', + ), + + // Create PDF invoices + 'invoice_pdf_enabled' => array( + 'field' => 'invoice_pdf_enabled', + 'type' => 'checkbox', + 'use' => 'a', + ), + ); } diff --git a/index.php b/index.php index cf9bf47..1d49158 100644 --- a/index.php +++ b/index.php @@ -38,7 +38,7 @@ * version from this plugin. */ define('GLM_MEMBERS_BILLING_PLUGIN_VERSION', '1.0.16'); -define('GLM_MEMBERS_BILLING_PLUGIN_DB_VERSION', '0.0.28'); +define('GLM_MEMBERS_BILLING_PLUGIN_DB_VERSION', '0.0.29'); // 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/billing/invoicing.php b/models/admin/billing/invoicing.php index d5aafc6..653cca1 100644 --- a/models/admin/billing/invoicing.php +++ b/models/admin/billing/invoicing.php @@ -112,7 +112,7 @@ class GlmMembersAdmin_billing_invoicing //extends GlmDataAccounts $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); $Accounts = new GlmDataAccounts( $this->wpdb, $this->config ); - echo '
$_REQUEST: ' . print_r( $_REQUEST, true ) . '
'; + // echo '
$_REQUEST: ' . print_r( $_REQUEST, true ) . '
'; if ( isset( $_REQUEST['invoice_types'] ) ) { @@ -123,7 +123,8 @@ class GlmMembersAdmin_billing_invoicing //extends GlmDataAccounts if ( isset( $_REQUEST['counties'] ) ) { - // $wParts[] = "T."; + $countiesSelected = $_REQUEST['counties']; + $wParts[] = "T.billing_county IN (" . implode(',', $countiesSelected ) . ")"; } @@ -137,7 +138,7 @@ class GlmMembersAdmin_billing_invoicing //extends GlmDataAccounts switch( $option2 ) { case 'Create Invoices': $accounts = $Accounts->getSimpleAccountList( $where ); - echo '
$accounts: ' . print_r( $accounts, true ) . '
'; + // echo '
$accounts: ' . print_r( $accounts, true ) . '
'; foreach ( $accounts as $account ) { // Get the invoice type $invoiceType = $BillingSupport->getInvoiceTypeById( $account['invoice_type'] ); diff --git a/models/admin/management/importAccounts.php b/models/admin/management/importAccounts.php index c924574..9fedbb5 100644 --- a/models/admin/management/importAccounts.php +++ b/models/admin/management/importAccounts.php @@ -145,7 +145,7 @@ SELECT M.member_id,M.billing_contact,M.account_number, PT.name as payment_type,M.process_email as email, M.member_name, MA.email_invoice, MA.usmail_invoice, MA.fax_invoice, M.mailing_address,M.mailing_city_id,M.mailing_state_id,M.mailing_zip, - M.street,M.city_id,M.state_id,M.zip + M.street,M.city_id,M.state_id,M.zip,M.region FROM members.member M LEFT OUTER JOIN members.member_account MA ON ( MA.member_id = M.member_id ) LEFT OUTER JOIN members.payment_types PT ON ( MA.payment_type = PT.id ) @@ -182,6 +182,23 @@ $getCity = $dbh->prepare( FROM members.city WHERE city_id = :id" ); +$getRegion = $dbh->prepare( + "SELECT region_name + FROM members.region + WHERE region_id = :id" +); + +function getCountyId( $wpdb, $county_name ) +{ + return $wpdb->get_var( + $wpdb->prepare( + "SELECT id + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "counties + WHERE name = %s", + $county_name + ) + ); +} foreach ( $members as $member ) { @@ -204,7 +221,7 @@ foreach ( $members as $member ) { //$refName = ( $member['billing_contact'] ) ? $member['billing_contact'] : $member['member_name']; $refName = $member['member_name']; - $city = $state = ''; + $city = $state = $county = ''; if ( $member['mailing_address'] ) { $addr1 = $member['mailing_address']; @@ -224,18 +241,25 @@ foreach ( $members as $member ) { $city = $member['city']; $state = $member['state']; if ( $member['city_id'] ) { - $getCity->bindParam( ':id', $member['city_id'] ); + $getCity->bindParam( ':id', $member['city_id'], PDO::PARAM_INT ); $getCity->execute(); $city = $getCity->fetchColumn(); } if ( $member['state_id'] ) { - $getState->bindParam( ':id', $member['state_id'] ); + $getState->bindParam( ':id', $member['state_id'], PDO::PARAM_INT ); $getState->execute(); $state = $getState->fetchColumn(); } $zip = $member['zip']; } + if ( $member['region'] ) { + $getRegion->bindParam( ':id', $member['region'], PDO::PARAM_INT ); + $getRegion->execute(); + $countyName = $getRegion->fetchColumn(); + $county = getCountyId( $this->wpdb, $countyName ); + } + $accountData = array( 'ref_dest' => $newMemberId, 'ref_name' => $refName, @@ -251,6 +275,7 @@ foreach ( $members as $member ) { 'billing_city' => $city, 'billing_state' => $state, 'billing_zip' => $zip, + 'billing_county' => $county, ); $accountDataFormat = array( '%d', // ref_dest @@ -267,6 +292,7 @@ foreach ( $members as $member ) { '%s', // billing_city '%s', // billing_state '%s', // billing_zip + '%s', // billing_county ); $importResults .= '
$accountData: ' . print_r( $accountData, true ) . '
'; diff --git a/setup/databaseScripts/create_database_V0.0.28.sql b/setup/databaseScripts/create_database_V0.0.29.sql similarity index 95% rename from setup/databaseScripts/create_database_V0.0.28.sql rename to setup/databaseScripts/create_database_V0.0.29.sql index b46f1af..81c6d61 100644 --- a/setup/databaseScripts/create_database_V0.0.28.sql +++ b/setup/databaseScripts/create_database_V0.0.29.sql @@ -23,6 +23,7 @@ CREATE TABLE {prefix}accounts ( billing_addr1 TINYTEXT NULL, -- Billing Address 1 billing_addr2 TINYTEXT NULL, -- Billing Address 2 billing_city TINYTEXT NULL, -- Billing City + billing_county INT NULL, -- Billing County billing_state TINYTEXT NULL, -- Billing State billing_zip TINYTEXT NULL, -- Billing Zip billing_country TINYTEXT NULL, -- Billing Country @@ -45,7 +46,8 @@ CREATE TABLE {prefix}accounts ( INDEX(ref_name(20)), INDEX(email(20)), INDEX(invoice_type), - INDEX(renewal_date) + INDEX(renewal_date), + INDEX(billing_county(20)) ); ---- @@ -252,6 +254,11 @@ CREATE TABLE {prefix}settings ( member_billing_enabled BOOLEAN DEFAULT '1', -- Member have access to billing info tab billing_fields_required BOOLEAN DEFAULT '1', -- Require Billing Fields invoice_methods_enabled BOOLEAN DEFAULT '1', -- Enable Uptra invoice methods + renewal_day_static BOOLEAN DEFAULT '0', -- Renewal dates are same day every year + renewal_day INT NULL, -- Day of the month for renewals + renewal_month INT NULL, -- Month of year for renewals + billing_county_enabled BOOLEAN DEFAULT '0', -- Billing uses county field + invoice_pdf_enabled BOOLEAN DEFAULT '0', -- Create PDF invoices PRIMARY KEY (id) ); @@ -283,6 +290,7 @@ CREATE TABLE {prefix}management ( merchant_solutions_test TINYINT NULL, -- Merchant Solutions test mode - List - see proc_test_mode in plugin.ini merchant_solutions_conf BOOLEAN NULL, -- Flag to send payment confirmation Email merchant_solutions_merchant_email TINYTEXT NULL, -- Merchant Solutions will send copy of confirmation E-Mail + uptravel_invoice_template BOOLEAN DEFAULT '0', -- Use uptravel template for invoices. PRIMARY KEY (id) ); diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index 97ffdf1..b3f0ccd 100644 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -42,5 +42,6 @@ $glmMembersBillingDbVersions = array( '0.0.26' => array('version' => '0.0.26', 'tables' => 15), '0.0.27' => array('version' => '0.0.27', 'tables' => 15, 'date' => '11/19/2018'), '0.0.28' => array('version' => '0.0.28', 'tables' => 15, 'date' => '11/27/2018'), + '0.0.29' => array('version' => '0.0.29', 'tables' => 15, 'date' => '11/29/2018'), ); diff --git a/setup/databaseScripts/update_database_V0.0.28.sql b/setup/databaseScripts/update_database_V0.0.28.sql index 8a8575a..366a249 100644 --- a/setup/databaseScripts/update_database_V0.0.28.sql +++ b/setup/databaseScripts/update_database_V0.0.28.sql @@ -1,6 +1,6 @@ -- Gaslight Media Billing Database --- File Created: 11/19/2018 --- Database Version: 0.0.27 +-- File Created: 11/27/2018 +-- Database Version: 0.0.28 -- -- To permit each query below to be executed separately, -- all queries must be separated by a line with four dashes diff --git a/setup/databaseScripts/update_database_V0.0.29.sql b/setup/databaseScripts/update_database_V0.0.29.sql new file mode 100644 index 0000000..d8d6773 --- /dev/null +++ b/setup/databaseScripts/update_database_V0.0.29.sql @@ -0,0 +1,40 @@ +-- Gaslight Media Billing Database +-- File Created: 11/29/2018 +-- Database Version: 0.0.29 +-- +-- To permit each query below to be executed separately, +-- all queries must be separated by a line with four dashes + +-- Update the accounts table +ALTER TABLE {prefix}accounts ADD billing_county INT NULL; + +---- + +CREATE INDEX billing_county ON {prefix}accounts (billing_county(20)); + +---- + +-- Update the settings table +ALTER TABLE {prefix}settings ADD renewal_day_static BOOLEAN DEFAULT '0'; + +---- + +ALTER TABLE {prefix}settings ADD renewal_day INT NULL; + +---- + +ALTER TABLE {prefix}settings ADD renewal_month INT NULL; + +---- + +ALTER TABLE {prefix}settings ADD billing_county_enabled BOOLEAN DEFAULT '0'; + +---- + +ALTER TABLE {prefix}settings ADD invoice_pdf_enabled BOOLEAN DEFAULT '0'; + +---- + +-- Update the Management table +ALTER TABLE {prefix}management ADD uptravel_invoice_template BOOLEAN DEFAULT '0'; + diff --git a/views/admin/billing/editAccount.html b/views/admin/billing/editAccount.html index 311fd81..b64012c 100644 --- a/views/admin/billing/editAccount.html +++ b/views/admin/billing/editAccount.html @@ -202,7 +202,26 @@ + {if $settings.billing_county_enabled}
+
Billing County
+
+ + {if $account.fieldFail.billing_county}

{$account.fieldFail.billing_county}

{/if}
+
+
+ {else} + + {/if} + +
Billing State
@@ -225,7 +244,7 @@
-
+
Billing Country
@@ -233,7 +252,7 @@
-
+
Billing Phone
@@ -241,7 +260,7 @@
-
+
Billing Fax
diff --git a/views/admin/management/billing.html b/views/admin/management/billing.html index 4a29629..08954ac 100644 --- a/views/admin/management/billing.html +++ b/views/admin/management/billing.html @@ -23,6 +23,7 @@

Payment

+ Payment Methods: @@ -143,6 +144,20 @@ + + +

Templates

+ + + + + + + + Use UPTRA invoice Template + + + diff --git a/views/admin/settings/billing.html b/views/admin/settings/billing.html index f64d0f9..8607368 100644 --- a/views/admin/settings/billing.html +++ b/views/admin/settings/billing.html @@ -140,64 +140,125 @@ - Allow Membership Choice When renewing + Allow Membership Choice When renewing - Allow Employees + Allow Employees - Use member types in Invoice Types + Use member types in Invoice Types - Enable Quickbooks + Enable Quickbooks - Enable Members billing tab (for members) + Enable Members billing tab (for members) - Require Billing Fields + Require Billing Fields - Enable Invoice Methods + Enable Invoice Methods + + + + + + + + + Enable Billing Counties + + + + + + + + + All Members renewal same day each year + + + + + + + + + Create PDF Invoices + + + + + + Renewal Day of Month
+ Must be a number + + + + {if $billingSettings.fieldFail.renewal_day}

{$billingSettings.fieldFail.renewal_day}

{/if}
+ + + + + + Renewal Month of Year
+ Must be a number + + + + {if $billingSettings.fieldFail.renewal_month}

{$billingSettings.fieldFail.renewal_month}

{/if}
-- 2.17.1