From 6898f68402cf3e2209f52d220d98f9efd29893ea Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Wed, 26 Dec 2018 16:52:48 -0500 Subject: [PATCH] Working on import of uptra billing invoices and payments Adding new field for invoices for the old_invoice_id. --- classes/billingSupport.php | 58 ++--- index.php | 2 +- models/admin/ajax/exportQIF.php | 56 +++-- models/admin/billing/invoicing.php | 7 - models/admin/management/billing.php | 17 ++ models/admin/management/importBillingData.php | 211 ++++++++++++++++++ ...0.0.31.sql => create_database_V0.0.32.sql} | 1 + setup/databaseScripts/dbVersions.php | 1 + .../update_database_V0.0.32.sql | 9 + views/admin/management/importAccounts.html | 14 ++ 10 files changed, 323 insertions(+), 53 deletions(-) create mode 100644 models/admin/management/importBillingData.php rename setup/databaseScripts/{create_database_V0.0.31.sql => create_database_V0.0.32.sql} (99%) create mode 100644 setup/databaseScripts/update_database_V0.0.32.sql diff --git a/classes/billingSupport.php b/classes/billingSupport.php index c618275..319c1ee 100644 --- a/classes/billingSupport.php +++ b/classes/billingSupport.php @@ -145,35 +145,37 @@ class GlmBillingSupport // Get the unpaid invoices $oldInvoices = $this->getUnPaidInvoicesByAccount( $account ); - foreach ( $oldInvoices as $key => $inv ) { - if ( in_array( $inv['id'], $invoices ) ) { - unset( $oldInvoices[$key] ); + if ( $oldInvoices ) { + foreach ( $oldInvoices as $key => $inv ) { + if ( in_array( $inv['id'], $invoices ) ) { + unset( $oldInvoices[$key] ); + } } - } - // Need to exclude any invoices already done ($invoices) - if ( $oldInvoices && is_array( $oldInvoices ) && count( $oldInvoices ) > 0 ) { - foreach ( $oldInvoices as $invoice ) { - $balance = (float)$invoice['balance']; - if ( $payment == $invoice['balance'] ) { - // Mark this as paid then - $this->updateInvoiceBalance( $invoice['id'], (float)0.00 ); - // Record the payment to the invoice_payments table - $this->recordInvoicePayment( $invoice['id'], $payment_id, $balance ); - // $payment is used up so break from the foreach loop - break; - } else if ( $payment > $invoice['balance'] ) { - $this->updateInvoiceBalance( $invoice['id'], (float)0.00 ); - $payment -= $invoice['balance']; - // Record the payment to the invoice_payments table - $this->recordInvoicePayment( $invoice['id'], $payment_id, $balance ); - } else if ( $invoice['balance'] > $payment ) { - // Update the balance of the invoice - $balance = (float)$balance - (float)$payment; - $this->updateInvoiceBalance( $invoice['id'], (float)$balance ); - // Record the payment to the invoice_payments table - $this->recordInvoicePayment( $invoice['id'], $payment_id, (float)$payment ); - // $payment is used up so break from the foreach loop - break; + // Need to exclude any invoices already done ($invoices) + if ( $oldInvoices && is_array( $oldInvoices ) && count( $oldInvoices ) > 0 ) { + foreach ( $oldInvoices as $invoice ) { + $balance = (float)$invoice['balance']; + if ( $payment == $invoice['balance'] ) { + // Mark this as paid then + $this->updateInvoiceBalance( $invoice['id'], (float)0.00 ); + // Record the payment to the invoice_payments table + $this->recordInvoicePayment( $invoice['id'], $payment_id, $balance ); + // $payment is used up so break from the foreach loop + break; + } else if ( $payment > $invoice['balance'] ) { + $this->updateInvoiceBalance( $invoice['id'], (float)0.00 ); + $payment -= $invoice['balance']; + // Record the payment to the invoice_payments table + $this->recordInvoicePayment( $invoice['id'], $payment_id, $balance ); + } else if ( $invoice['balance'] > $payment ) { + // Update the balance of the invoice + $balance = (float)$balance - (float)$payment; + $this->updateInvoiceBalance( $invoice['id'], (float)$balance ); + // Record the payment to the invoice_payments table + $this->recordInvoicePayment( $invoice['id'], $payment_id, (float)$payment ); + // $payment is used up so break from the foreach loop + break; + } } } } diff --git a/index.php b/index.php index 27fb5cf..2d2ad0c 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.31'); +define('GLM_MEMBERS_BILLING_PLUGIN_DB_VERSION', '0.0.32'); // 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/exportQIF.php b/models/admin/ajax/exportQIF.php index b1d707e..bbb8c7c 100644 --- a/models/admin/ajax/exportQIF.php +++ b/models/admin/ajax/exportQIF.php @@ -81,7 +81,8 @@ class GlmMembersAdmin_ajax_exportQIF extends GlmDataTransactions public function modelAction( $actionData = false ) { - $fileData = array(); + $fileData = array(); + $paymentTypes = array(); $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); @@ -141,35 +142,56 @@ class GlmMembersAdmin_ajax_exportQIF extends GlmDataTransactions $this->member_data = false; $this->notes = false; + // Open file pointer to php://output (Direct to browser) $fp = fopen( 'php://output', 'w' ); - $paymentTypes = $BillingSupport->getAllInvoiceTypes(); + $paymentTypeData = $BillingSupport->getAllInvoiceTypes(); + + if ( $paymentTypeData ) { + foreach ( $paymentTypeData as $payType ) { + $paymentTypes[$payType['id']] = $payType; + } + } + + // echo '
$paymentTypes: ' . print_r( $paymentTypes, true ) . '
'; + // echo '
$transactions: ' . print_r( $transactions, true ) . '
'; + // exit; // Generate the QIF format data $fileData[] = "!Type:Bank\n"; foreach ( $transactions as $record ) { + // echo '
$record: ' . print_r( $record, true ) . '
'; switch ( $record['type'] ) { case $this->config['transaction_numb']['Payment']: - $paymentType = $paymentTypes[$record['type_id']]; - if ( $paymentType ) { - $fileData[] = sprintf( - self::PAYMENTFORMAT, - date( 'm/d/Y', strtotime( $record['transaction_time']['datetime'] ) ), // D - '-' . $record['current_payment_total'], // T - $record['member_name'], // P - preg_replace( - '[0-9]', - '', - $record['notes'] - ), // N - $paymentType['category'], // M - $paymentType['qcode'] // L - ); + $account = $BillingSupport->getAccountById( $record['type_id'] ); + if ( $account ) { + $paymentType = $paymentTypes[$account['invoice_type']]; + // echo '
$paymentType: ' . print_r( $paymentType, true ) . '
'; + if ( $paymentType ) { + $fileData[] = sprintf( + self::PAYMENTFORMAT, + date( 'm/d/Y', strtotime( $record['transaction_time']['datetime'] ) ), // D + '-' . $record['current_payment_total'], // T + $record['member_name'], // P + preg_replace( + '[0-9]', + '', + $record['notes'] + ), // N + $paymentType['category'], // M + $paymentType['qcode'] // L + ); + } } break; } } + + // echo '
$fileData: ' . print_r( $fileData, true ) . '
'; + // exit; + + $buf = implode( '', $fileData ); $len = strlen( $buf ); diff --git a/models/admin/billing/invoicing.php b/models/admin/billing/invoicing.php index 8feea0f..4ea6c2d 100644 --- a/models/admin/billing/invoicing.php +++ b/models/admin/billing/invoicing.php @@ -115,23 +115,17 @@ class GlmMembersAdmin_billing_invoicing //extends GlmDataAccounts $Accounts = new GlmDataAccounts( $this->wpdb, $this->config ); if ( isset( $_REQUEST['invoice_types'] ) ) { - $invoiceTypes = $_REQUEST['invoice_types']; $wParts[] = " T.invoice_type IN (" . implode(',', $invoiceTypes) . ") "; - } if ( isset( $_REQUEST['counties'] ) ) { - $countiesSelected = $_REQUEST['counties']; $wParts[] = "T.billing_county IN (" . implode(',', $countiesSelected ) . ")"; - } if ( isset( $_REQUEST['submitType'] ) ) { - $option2 = filter_var( $_REQUEST['submitType'], FILTER_SANITIZE_STRING ); - } // Do selected option @@ -163,7 +157,6 @@ class GlmMembersAdmin_billing_invoicing //extends GlmDataAccounts $view = 'invoicing'; if ( $option2 ) { - // $where used in all places. $where = implode( ' AND ', $wParts ); $accounts = $Accounts->getSimpleAccountList( $where ); diff --git a/models/admin/management/billing.php b/models/admin/management/billing.php index 03452ee..2d5407a 100644 --- a/models/admin/management/billing.php +++ b/models/admin/management/billing.php @@ -164,6 +164,23 @@ class GlmMembersAdmin_management_billing extends GlmDataBillingManagement break; + case 'importBillingData': + $view = 'importAccounts'; + if ( isset( $_REQUEST['option2'] ) ) { + $option2 = $_REQUEST['option2']; + } + if ( !isset( $option2 ) ) { + $option2 = ''; + } + switch ( $option2 ) { + case 'import': + require_once GLM_MEMBERS_BILLING_PLUGIN_PATH.'/models/admin/management/importBillingData.php'; + break; + default: + break; + } + break; + case 'importAccounts': $view = 'importAccounts'; if ( isset( $_REQUEST['option2'] ) ) { diff --git a/models/admin/management/importBillingData.php b/models/admin/management/importBillingData.php new file mode 100644 index 0000000..6b095fc --- /dev/null +++ b/models/admin/management/importBillingData.php @@ -0,0 +1,211 @@ +wpdb, $this->config ); + +// Connect to their live database. +$dbh = new PDO( + 'pgsql: host=ds4.gaslightmedia.com dbname=uptravel user=nobody', + null, + null, + array( + PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC + ) +); + +$dbh->setAttribute( + PDO::ATTR_ERRMODE, + PDO::ERRMODE_EXCEPTION +); + +if ( isset( $_REQUEST['start'] ) && $start = filter_var( $_REQUEST['start'], FILTER_VALIDATE_INT ) ) { +} else { + $start = 0; +} + +$importResults = ''; + +// Get total number of records. +$sql = " +SELECT * + FROM members.billing + WHERE invoice_id IN ( + SELECT invoice_id + FROM members.billing + WHERE transaction_date >= '2018-01-01' + AND billing_type = 1 + ORDER BY transaction_date,transaction_time + LIMIT 10 OFFSET 0) + AND billing_type IN (1, 2) + ORDER BY transaction_date,transaction_time + LIMIT 20 +OFFSET 0"; + +//LIMIT 10 +//OFFSET $start"; +$stmt = $dbh->query( $sql ); +$billingRecords = $stmt->fetchAll(); + +$importResults .= '
$billingRecords: ' . print_r( $billingRecords, true ) . '
'; + +if ( $start === 0 ) { + // Clear the invoices,line_items and transactions + $this->wpdb->query( "DELETE FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "invoices" ); + $this->wpdb->query( "DELETE FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "line_items" ); + $this->wpdb->query( "DELETE FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "transactions" ); + $this->wpdb->query( "DELETE FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "payments" ); + $this->wpdb->query( "DELETE FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "invoice_payments" ); +} + +if ( $billingRecords ) { + foreach ( $billingRecords as $billingRecord ) { + // Have to get the member id and then get it's new account id + $refDest = $this->wpdb->get_var( + $this->wpdb->prepare( + "SELECT id + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members + WHERE old_member_id = %d", + $billingRecord['member_id'] + ) + ); + if ( $refDest ) { + $accountId = $BillingSupport->getAccountByRefDest( $refDest ); + // $importResults .= '
$accountId: ' . print_r( $accountId, true ) . '
'; + if ( $accountId ) { + // Get the invoice Type + $invoiceType = $BillingSupport->getInvoiceTypeById( $accountId['invoice_type'] ); + // $importResults .= '
$invoiceType: ' . print_r( $invoiceType, true ) . '
'; + if ( $invoiceType ) { + // Check the type (1 = invoice, 2 = payment, 3 = Adjustment, 4 = Comment) + switch ( $billingRecord['billing_type']) { + case "1": // Invoices + // Store the invoice + $this->wpdb->insert( + GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . 'invoices', + array( + 'transaction_time' => $billingRecord['transaction_time'], + 'account' => $accountId['id'], + 'old_invoice_id' => $billingRecord['invoice_id'], + 'amount_total' => $billingRecord['amount'], + 'balance' => $billingRecord['balance'], + 'due_date' => $billingRecord['transaction_date'], + 'paid' => $billingRecord['paid'], + 'notes' => $billingRecord['notes'], + 'renewal' => true, + 'recurring' => true, + 'recurrence' => 20 + ), + array( + '%s', // transaction_time + '%d', // account + '%d', // old_invoice_id + '%s', // amount + '%s', // balance + '%s', // due_date + '%s', // paid + '%s', // notes + '%s', // renewal + '%s', // recurring + '%s', // recurrence + ) + ); + $newInvoiceId = $this->wpdb->insert_id; + if ( $newInvoiceId ) { + $BillingSupport->createLineItemForInvoice( + array( + 'invoice_id' => $newInvoiceId, + 'line_item_type' => $invoiceType['id'], + 'account' => $accountId['id'], + 'name' => $invoiceType['name'], + 'amount' => $billingRecord['amount'], + 'due_date' => $billingRecord['transaction_date'], + 'recurring' => $invoiceType['recurring'], + 'recurrence' => $invoiceType['recurrence'], + ) + ); + $BillingSupport->recordInvoice( $newInvoiceId, $accountId['id'], $billingRecord['amount'] ); + } + break; + + case "2": // Payments + // Convert the payment method + $paymentMethod = false; + switch ( $billingRecord['payment_method'] ) { + case "1": // Check 10 + $paymentMethod = 'Check'; + break; + case "2": // not being used? + $paymentMethod = 'Credit Card'; + break; + case "3": // Cash 30 + $paymentMethod = 'Cash'; + break; + case "4": // Other 40 + $paymentMethod = 'Other'; + break; + case "5": // Adjustment 50 + $paymentMethod = 'Adjustment'; + break; + } + // Find the new invoice id + $newInvoiceId = $this->wpdb->get_var( + $this->wpdb->prepare( + "SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "invoices + WHERE old_invoice_id = %d", + $billingRecord['invoice_id'] + ) + ); + $importResults .= '
$newInvoiceId: ' . print_r( $newInvoiceId, true ) . '
'; + if ( $newInvoiceId ) { + $this->wpdb->insert( + GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . 'payments', + array( + 'transaction_time' => $billingRecord['transaction_time'], + 'account' => $accountId['id'], + 'amount' => $billingRecord['amount'], + 'payment_method' => $paymentMethod, + 'payment_data' => $billingRecord['payment_data'], + ), + array( + '%s', // transaction_time + '%d', // account + '%s', // amount + '%s', // payment_method + '%s', // payment_data + ) + ); + $paymentId = $this->wpdb->insert_id; + if ( $paymentId ) { + $BillingSupport->recordPayment( + $paymentId, + $accountId['id'], + $billingRecord['amount'], + array( $newInvoiceId ), + $this->config['transaction_numb']['Payment'] + ); + } + } + + break; + + case "3": // Adjustments + break; + + case "4": // Comments + break; + + default: + die('billing type not correct'); + break; + } + } + } + } + } +} + +$numberProcessed = $start + 10; diff --git a/setup/databaseScripts/create_database_V0.0.31.sql b/setup/databaseScripts/create_database_V0.0.32.sql similarity index 99% rename from setup/databaseScripts/create_database_V0.0.31.sql rename to setup/databaseScripts/create_database_V0.0.32.sql index 7bbcb25..102d3b5 100644 --- a/setup/databaseScripts/create_database_V0.0.31.sql +++ b/setup/databaseScripts/create_database_V0.0.32.sql @@ -73,6 +73,7 @@ CREATE TABLE {prefix}transactions ( -- Invoices CREATE TABLE {prefix}invoices ( id INT NOT NULL AUTO_INCREMENT, + old_invoice_id INT NULL, -- old invoice id from uptra transaction_time DATETIME NOT NULL, -- datetime for the invoice account INT NOT NULL, -- ref to account id amount_total DECIMAL(8, 2) NOT NULL, -- total amount for invoice diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index 7bb16c2..6d5a9c0 100644 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -45,5 +45,6 @@ $glmMembersBillingDbVersions = array( '0.0.29' => array('version' => '0.0.29', 'tables' => 15, 'date' => '11/29/2018'), '0.0.30' => array('version' => '0.0.30', 'tables' => 15, 'date' => '12/04/2018'), '0.0.31' => array('version' => '0.0.31', 'tables' => 15, 'date' => '12/17/2018'), + '0.0.32' => array('version' => '0.0.32', 'tables' => 15, 'date' => '12/26/2018'), ); diff --git a/setup/databaseScripts/update_database_V0.0.32.sql b/setup/databaseScripts/update_database_V0.0.32.sql new file mode 100644 index 0000000..a56906a --- /dev/null +++ b/setup/databaseScripts/update_database_V0.0.32.sql @@ -0,0 +1,9 @@ +-- Gaslight Media Billing Database +-- File Created: 12/26/2018 +-- Database Version: 0.0.32 +-- +-- 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}invoicies ADD old_invoice_id INT NULL; diff --git a/views/admin/management/importAccounts.html b/views/admin/management/importAccounts.html index 2f584b1..d2c67b4 100644 --- a/views/admin/management/importAccounts.html +++ b/views/admin/management/importAccounts.html @@ -19,6 +19,20 @@ +
+ + + + + + + + + + + +
+
{/if} Number processed: {$numberProcessed} -- 2.17.1