From 0649495cb6bfae0691d7642e991007cd9458b914 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Fri, 22 Dec 2017 14:30:43 -0500 Subject: [PATCH] Adding new table to save invoice payment data. Working on adding another table for the billing plugin. Adding table to record the payment amounts that get applied to an invoice. This way When I show the invoice I can show the payments made to that invoice. --- classes/billingSupport.php | 79 +++- index.php | 2 +- models/admin/billing/invoices.php | 2 +- models/admin/member/billing.php | 11 + ..._V0.0.5.sql => create_database_V0.0.6.sql} | 11 + setup/databaseScripts/dbVersions.php | 1 + .../update_database_V0.0.6.sql | 16 + views/admin/billing/invoiceStore.html | 387 +++++++++--------- views/admin/billing/statements.html | 3 + views/admin/billing/viewInvoice.html | 16 +- 10 files changed, 322 insertions(+), 206 deletions(-) rename setup/databaseScripts/{create_database_V0.0.5.sql => create_database_V0.0.6.sql} (96%) create mode 100644 setup/databaseScripts/update_database_V0.0.6.sql diff --git a/classes/billingSupport.php b/classes/billingSupport.php index 2eb965f..768ffeb 100644 --- a/classes/billingSupport.php +++ b/classes/billingSupport.php @@ -85,9 +85,12 @@ class GlmBillingSupport * Record the Payment in the transaction table. Need to also go through the invoices * and marked them as paid if amount is equal or greater than the invoice amount. * + * Also need to record the payment to the invoice_payments + * table so we know which payment was applied to the invoice. + * * @param mixed $invoice_id Id of the Invoice record * @param mixed $payment Total amount of the Payment - + * * @access public * @return void */ @@ -105,15 +108,21 @@ class GlmBillingSupport 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, $invoice['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, $invoice['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; } @@ -142,9 +151,42 @@ class GlmBillingSupport ); } + /** + * recordInvoicePayment + * + * Record the payment to the invoice_payment table. + * + * @param mixed $invoice Invoice Id + * @param mixed $payment Payment Id + * @param mixed $amount Amount of payment + * + * @access public + * @return void + */ + public function recordInvoicePayment( $invoice, $payment, $amount ) + { + $this->wpdb->insert( + GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . 'invoice_payments', + array( + 'invoice' => $invoice, + 'payment' => $payment, + 'amount' => $amount, + 'transaction_time' => date('Y-m-d H:i;s'), + ), + array( + '%d', + '%d', + '%s', + '%s', + ) + ); + } + /** * updateInvoiceBalance * + * Update the balance amount for the invoice. + * * @param mixed $invoice_id Id for the invoice * @param mixed $balance New balance amount * @@ -154,7 +196,9 @@ class GlmBillingSupport public function updateInvoiceBalance( $invoice_id, $balance ) { // if the balance is 0.00 then set paid to true - $this->markInvoiceAsPaid( $invoice_id ); + if ( $balance === 0.00 ) { + $this->markInvoiceAsPaid( $invoice_id ); + } $this->wpdb->update( GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . 'invoices', array( 'balance' => $balance ), @@ -414,6 +458,28 @@ class GlmBillingSupport ); } + /** + * getInvoicePaymentsByInvoiceId + * + * Get all records from the invoice_payment table for an invoice. + * + * @param mixed $invoice Invoice Id. + * + * @access public + * @return void + */ + public function getInvoicePaymentsByInvoiceId( $invoice ) + { + return $this->wpdb->get_results( + $this->wpdb->prepare( + "SELECT * + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "invoice_payments + WHERE invoice = %d", + $invoice + ), + ARRAY_A + ); + } /** * getBalanceDueByAccount @@ -437,7 +503,6 @@ class GlmBillingSupport return $balance_due; } - /** * viewInvoice * @@ -460,12 +525,15 @@ class GlmBillingSupport // echo '
$this->config: ' . print_r( $this->config, true ) . '
'; $account = $this->getAccountById( $invoice['account'] ); // echo '
$account: ' . print_r( $account, true ) . '
'; + $payments = $this->getInvoicePaymentsByInvoiceId( $invoice_id ); + // echo '
$payments: ' . print_r( $payments, true ) . '
'; $templateData = array( 'settings' => $this->config['settings'], 'invoice' => $invoice, 'line_items' => $line_items, 'account' => $account, + 'payments' => $payments, ); $invoiceHtml = $this->generateInvoiceHtml( $templateData, 'admin/billing/invoiceStore.html' ); @@ -473,11 +541,6 @@ class GlmBillingSupport return $invoiceHtml; } - public function getMemberInfo() - { - - } - /** * getLineItemsForInvoice * diff --git a/index.php b/index.php index 3e0f1b9..86681b3 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.5'); +define('GLM_MEMBERS_BILLING_PLUGIN_DB_VERSION', '0.0.6'); // 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/invoices.php b/models/admin/billing/invoices.php index 0e6903c..ae10eee 100644 --- a/models/admin/billing/invoices.php +++ b/models/admin/billing/invoices.php @@ -248,7 +248,7 @@ class GlmMembersAdmin_billing_invoices extends GlmDataInvoices break; case 'view': - // Now we have a total for the invoice we can record the transaction + // Call in the support class $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); $invoiceHtml = $BillingSupport->viewInvoice( $_REQUEST['id'] ); diff --git a/models/admin/member/billing.php b/models/admin/member/billing.php index 07c6cec..62019df 100644 --- a/models/admin/member/billing.php +++ b/models/admin/member/billing.php @@ -216,6 +216,16 @@ class GlmMembersAdmin_member_billing // extends GlmDataBilling // echo '
$account: ' . print_r( $account, true ) . '
'; $view = 'editAccount'; + break; + case 'view': + // Call in the support class + $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); + + $invoiceHtml = $BillingSupport->viewInvoice( $_REQUEST['id'] ); + + // Set the file name for the view file. + $view = 'viewInvoice'; + break; case 'list': $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config); @@ -257,6 +267,7 @@ class GlmMembersAdmin_member_billing // extends GlmDataBilling 'account_data' => $account_data, 'balance_due' => $balance_due, 'transaction_types' => $this->config['transaction_type'], + 'invoiceHtml' => $invoiceHtml, ); // Return status, any suggested view, and any data to controller. diff --git a/setup/databaseScripts/create_database_V0.0.5.sql b/setup/databaseScripts/create_database_V0.0.6.sql similarity index 96% rename from setup/databaseScripts/create_database_V0.0.5.sql rename to setup/databaseScripts/create_database_V0.0.6.sql index 791c908..604d7df 100644 --- a/setup/databaseScripts/create_database_V0.0.5.sql +++ b/setup/databaseScripts/create_database_V0.0.6.sql @@ -157,6 +157,17 @@ CREATE TABLE {prefix}gateway_settings ( ---- +-- Invoice Payments +CREATE TABLE {prefix}invoice_payments ( + id INT NOT NULL AUTO_INCREMENT, + invoice INT NOT NULL, -- reference to invoice id + payment INT NOT NULL, -- reference to payment id + amount DECIMAL(8, 2) NOT NULL, -- payment amount + PRIMARY KEY (id) +); + +---- + -- Settings CREATE TABLE {prefix}settings ( id INT NOT NULL AUTO_INCREMENT, diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index ba4fb64..0be92ba 100644 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -19,5 +19,6 @@ $glmMembersBillingDbVersions = array( '0.0.3' => array('version' => '0.0.3', 'tables' => 12), '0.0.4' => array('version' => '0.0.4', 'tables' => 12), '0.0.5' => array('version' => '0.0.5', 'tables' => 12), + '0.0.6' => array('version' => '0.0.6', 'tables' => 13), ); diff --git a/setup/databaseScripts/update_database_V0.0.6.sql b/setup/databaseScripts/update_database_V0.0.6.sql new file mode 100644 index 0000000..53a7cec --- /dev/null +++ b/setup/databaseScripts/update_database_V0.0.6.sql @@ -0,0 +1,16 @@ +-- Gaslight Media Billing Database +-- File Created: 12/22/2017 +-- Database Version: 0.0.6 +-- +-- To permit each query below to be executed separately, +-- all queries must be separated by a line with four dashes + +-- Invoice Payments +CREATE TABLE {prefix}invoice_payments ( + id INT NOT NULL AUTO_INCREMENT, + transaction_time DATETIME NOT NULL, -- datetime for the invoice + invoice INT NOT NULL, -- reference to invoice id + payment INT NOT NULL, -- reference to payment id + amount DECIMAL(8, 2) NOT NULL, -- payment amount + PRIMARY KEY (id) +); diff --git a/views/admin/billing/invoiceStore.html b/views/admin/billing/invoiceStore.html index 42f19c0..0f6c5a3 100644 --- a/views/admin/billing/invoiceStore.html +++ b/views/admin/billing/invoiceStore.html @@ -1,200 +1,201 @@ - - - - - - - - - - - - - - - - - + +
{if $settings.company_logo}{/if} -

INVOICE

-
- - {$settings.company_name}
- {if $settings.company_name2}{$settings.company_name2}
{/if} - {if $settings.company_addr1}{$settings.company_addr1}
{/if} - {if $settings.company_addr2}{$settings.company_addr2}
{/if} - {if $settings.company_city}{$settings.company_city} {$settings.company_state}, {$settings.company_zip}
{/if} - {if $settings.company_phone}{$settings.company_phone}
{/if} - {if $settings.company_email}{$settings.company_email}
{/if} - 		- - - - - - - - - - - - - -
Date: {$invoice.transaction_time|date_format:"%D"}
Account #: {$account.id}
Invoice #: {$invoice.id}
-
-

Bill To:

- {if $account.billing_name2}{$account.billing_name2}
{/if} - {if $account.billing_addr1}{$account.billing_addr1}
{/if} - {if $account.billing_addr2}{$account.billing_addr2}
{/if} - {if $account.billing_city}{$account.billing_city} {$account.billing_state}, {$account.billing_zip}
{/if} - {if $account.billing_phone}{$account.billing_phone}
{/if} + + + + + + + + + + + + + - - - - + +
{if $settings.company_logo}{/if} +

INVOICE

+
+ + {$settings.company_name}
+ {if $settings.company_name2}{$settings.company_name2}
{/if} + {if $settings.company_addr1}{$settings.company_addr1}
{/if} + {if $settings.company_addr2}{$settings.company_addr2}
{/if} + {if $settings.company_city}{$settings.company_city} {$settings.company_state}, {$settings.company_zip}
{/if} + {if $settings.company_phone}{$settings.company_phone}
{/if} + {if $settings.company_email}{$settings.company_email}
{/if} + 		+ + + + + + + + + + + + + +
Date: {$invoice.transaction_time|date_format:"%D"}
Account #: {$account.id}
Invoice #: {$invoice.id}
+
+

Bill To:

+ {if $account.ref_name}{$account.ref_name}
{/if} + {if $account.billing_addr1}{$account.billing_addr1}
{/if} + {if $account.billing_addr2}{$account.billing_addr2}
{/if} + {if $account.billing_city}{$account.billing_city} {$account.billing_state}, {$account.billing_zip}
{/if} + {if $account.billing_phone}{$account.billing_phone}
{/if} - -
 
- - + + + + + + + + + + + + + + + + + + + + + + + + + +
 
+ + + + + + + + + + + + + + + + {foreach $line_items as $item} + + + + + + + + + {/foreach} + {foreach $payments as $payment} + + + + + + + + + {/foreach} + + + + + + +
DateQty DescriptionRateAmount
{$invoice.transaction_time|date_format:"%D"}{$item.quantity} {$item.name}${$item.amount|string_format:"%.2f"}${$item.total|string_format:"%.2f"}
{$payment.transaction_time|date_format:"%D"}  Payment - ${$payment.amount|string_format:"%.2f"}
Total Amount Due${$invoice.balance|string_format:"%.2f"}
+
 
+ Payment Terms: +
+ Membership dues need to be paid by October 1st. +
 
Please return Coupon Below with Payment
+ + + + - - - - - - - - - - - - - - - - - -
+ Make checks payable to:
+ {$settings.company_name}
+ {if $settings.company_name2}{$settings.company_name2}
{/if} + {if $settings.company_addr1}{$settings.company_addr1},{/if} + {if $settings.company_addr2}{$settings.company_addr2},{/if} + {if $settings.company_city}{$settings.company_city} {$settings.company_state}, {$settings.company_zip}
{/if} + {if $settings.company_phone}{$settings.company_phone} -{/if} + {if $settings.company_email}{$settings.company_email}
{/if} +

+ {if $account.ref_name}{$account.ref_name}
{/if} + {if $account.billing_addr1}{$account.billing_addr1}
{/if} + {if $account.billing_addr2}{$account.billing_addr2}
{/if} + {if $account.billing_city}{$account.billing_city} {$account.billing_state}, {$account.billing_zip}
{/if} + {if $account.billing_phone}{$account.billing_phone}
{/if} + 		+ - - - - - - + + - + + - - - {foreach $line_items as $item} - - - - - - - - - {/foreach} - - - + + - -
DateQty DescriptionRateAmountPlease Pay:${$invoice.balance|string_format:"%.2f"}
Payment Amount:
{$invoice.transaction_time|date_format:"%D"}{$item.quantity} {$item.name}${$item.amount|string_format:"%.2f"}${$item.total|string_format:"%.2f"}
Total Amount${$invoice.balance|string_format:"%.2f"} Account #: {$account.id}
-
 
- Payment Terms: -
- Membership dues need to be paid by October 1st. -
 
Please return Coupon Below with Payment
- - - - - -
- Make checks payable to:
- {$settings.company_name}
- {if $settings.company_name2}{$settings.company_name2}
{/if} - {if $settings.company_addr1}{$settings.company_addr1},{/if} - {if $settings.company_addr2}{$settings.company_addr2},{/if} - {if $settings.company_city}{$settings.company_city} {$settings.company_state}, {$settings.company_zip}
{/if} - {if $settings.company_phone}{$settings.company_phone} -{/if} - {if $settings.company_email}{$settings.company_email}
{/if} -

- {if $account.billing_name2}{$account.billing_name2}
{/if} - {if $account.billing_addr1}{$account.billing_addr1}
{/if} - {if $account.billing_addr2}{$account.billing_addr2}
{/if} - {if $account.billing_city}{$account.billing_city} {$account.billing_state}, {$account.billing_zip}
{/if} - {if $account.billing_phone}{$account.billing_phone}
{/if} - 		- - - - - - - - - - - - - - - - - -
Please Pay:${$invoice.balance|string_format:"%.2f"}
Payment Amount:
Account #: {$account.id}
Invoice #: {$invoice.id}
-
-
- - +
Invoice #: {$invoice.id}
+
+
diff --git a/views/admin/billing/statements.html b/views/admin/billing/statements.html index ec9e808..3bec960 100644 --- a/views/admin/billing/statements.html +++ b/views/admin/billing/statements.html @@ -12,6 +12,7 @@ Due Date Type Amount +   @@ -23,11 +24,13 @@ {$transaction.transaction_data.due_date|date_format:"%D"} {$transaction_types[$transaction.type]} ${$transaction.transaction_data.amount_total} + View / Make Payment {elseif $transaction.type == '20'} {$transaction.transaction_data.transaction_time|date_format:"%D"} {$transaction_types[$transaction.type]} ${$transaction.transaction_data.amount} +   {/if} {$alt = $alt + 1} diff --git a/views/admin/billing/viewInvoice.html b/views/admin/billing/viewInvoice.html index d320c7e..9f23d7a 100644 --- a/views/admin/billing/viewInvoice.html +++ b/views/admin/billing/viewInvoice.html @@ -1,7 +1,17 @@ -{include file='admin/billing/header.html'} - -{include file='admin/billing/subHeader.html'} +{if $fromMemberMenu} + {include file='admin/member/header.html'} + {include file='admin/billing/memberBillingSubHeader.html'} +
+{else} + {include file='admin/billing/header.html'} + {include file='admin/billing/subHeader.html'} +{/if} {$invoiceHtml} +{if $fromMemberMenu} + Make a Payment +
+{else} +{/if} {include file='admin/footer.html'} -- 2.17.1