From 5d2ff4b490006df6d4e452dd6e436ba366bc8d06 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Wed, 28 Nov 2018 16:54:35 -0500 Subject: [PATCH] Updates for billing invoicing Working on the invoicing section. Creating invoices setting up the payment type and counties filter. more work on the update import for member accounts for uptravel. --- classes/billingSupport.php | 9 +- classes/data/dataAccounts.php | 38 +++ models/admin/billing/invoices.php | 19 +- models/admin/billing/invoicing.php | 265 ++++++++++++++++++ models/admin/management/importAccounts.php | 78 +++++- setup/validActions.php | 15 +- views/admin/billing/bulkAddInvoices.html | 41 --- views/admin/billing/editAccount.html | 2 +- views/admin/billing/header.html | 1 + views/admin/billing/invoicing.html | 85 ++++++ .../admin/billing/memberBillingSubHeader.html | 6 +- views/admin/billing/subHeader.html | 2 - 12 files changed, 490 insertions(+), 71 deletions(-) create mode 100644 models/admin/billing/invoicing.php delete mode 100644 views/admin/billing/bulkAddInvoices.html create mode 100644 views/admin/billing/invoicing.html diff --git a/classes/billingSupport.php b/classes/billingSupport.php index 2a44049..920d055 100644 --- a/classes/billingSupport.php +++ b/classes/billingSupport.php @@ -682,11 +682,10 @@ class GlmBillingSupport { // Find all invoices for this account that aren't marked as paid. $balance_due = (float)0.00; - $results = $this->wpdb->get_results( - "SELECT * - FROM ", - ARRAY_A - ); + $invoices = $this->getUnPaidInvoicesByAccount( $account ); + foreach ( $invoices as $invoice ) { + $balance_due += (float)$invoice['balance']; + } return $balance_due; } diff --git a/classes/data/dataAccounts.php b/classes/data/dataAccounts.php index 9603c47..e12270c 100644 --- a/classes/data/dataAccounts.php +++ b/classes/data/dataAccounts.php @@ -82,6 +82,15 @@ class GlmDataAccounts extends GlmDataAbstract */ public $postEmployees = false; + /** + * Pull in the name of the payment type. + */ + public $paymentTypes = false; + /** + * Pull in the balance due for this account. + */ + public $balanceDue = false; + /** * Constructor * @@ -144,6 +153,18 @@ class GlmDataAccounts extends GlmDataAbstract 'use' => 'a' ), + 'member_name' => array( + 'field' => 'ref_dest', + 'type' => 'pointer', + 'p_table' => GLM_MEMBERS_PLUGIN_DB_PREFIX . 'members', + 'p_field' => 'name', + 'p_orderby' => 'name', + 'p_blank' => false, + 'required' => false, + 'use' => 'l', + 'as' => 'member_name', + ), + // Name of red_dest 'ref_name' => array( 'field' => 'ref_name', @@ -390,6 +411,23 @@ class GlmDataAccounts extends GlmDataAbstract $r['employees'] = $employees; } + if ( $this->paymentTypes ) { + $r['payment_type'] = $this->wpdb->get_var( + $this->wpdb->prepare( + "SELECT name + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "invoice_types + WHERE id = %d", + $r['invoice_type'] + ) + ); + } + + if ( $this->balanceDue ) { + include_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH.'/billingSupport.php'; + $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); + $r['balance_due'] = $BillingSupport->getBalanceDueByAccount( $r['id'] ); + } + return $r; } diff --git a/models/admin/billing/invoices.php b/models/admin/billing/invoices.php index f78f572..2f04706 100644 --- a/models/admin/billing/invoices.php +++ b/models/admin/billing/invoices.php @@ -131,6 +131,7 @@ class GlmMembersAdmin_billing_invoices extends GlmDataInvoices $filterOverdue = false; $counties = false; $paymentTypes = false; + $invoiceTypes = false; // Get any provided option if (isset($_REQUEST['option'])) { @@ -140,7 +141,8 @@ class GlmMembersAdmin_billing_invoices extends GlmDataInvoices // Do selected option switch ($option) { case 'bulkadd': - $view = 'bulkAddInvoices'; + $view = 'bulkAddInvoices'; + $wParts = array(); // Check if Counties is enabled and fetch counties if ( isset( $this->config['settings']['enable_counties'] ) && $this->config['settings']['enable_counties'] ) { @@ -157,7 +159,20 @@ class GlmMembersAdmin_billing_invoices extends GlmDataInvoices // Need to get the accounts $Accounts = new GlmDataAccounts( $this->wpdb, $this->config ); - $accountList = $Accounts->getSimpleAccountList( '', '', true, 'id', 1, 20 ); + + if ( isset( $_REQUEST['invoice_types'] ) ) { + + $invoiceTypes = $_REQUEST['invoice_types']; + + $wParts[] = "T.id IN ( + SELECT invoice + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "line_items + WHERE line_item_type IN (" . implode( ',', $invoiceTypes ) . ") )"; + } + + $where = implode( ' AND ', $wParts ); + + $accountList = $Accounts->getSimpleAccountList( $where, '', true, 'id', 1, 20 ); $accounts = $accountList['list']; break; diff --git a/models/admin/billing/invoicing.php b/models/admin/billing/invoicing.php new file mode 100644 index 0000000..d5aafc6 --- /dev/null +++ b/models/admin/billing/invoicing.php @@ -0,0 +1,265 @@ + + * @license http://www.gaslightmedia.com Gaslightmedia + * @release index.php,v 1.0 2014/10/31 19:31:47 cscott Exp $ + * @link http://dev.gaslightmedia.com/ + */ + +// Load Contacts data class +require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/billingSupport.php'; + +class GlmMembersAdmin_billing_invoicing //extends GlmDataAccounts +{ + + /** + * WordPress Database Object + * + * @var $wpdb + * @access public + */ + public $wpdb; + /** + * Plugin Configuration Data + * + * @var $config + * @access public + */ + public $config; + /** + * Transactions ID + * + * @var $account_id + * @access public + */ + public $account_id = false; + + /** + * Constructor + * + * This constructor performs the work for this model. This model returns + * an array containing the following. + * + * 'status' + * + * True if successful and false if there was a fatal failure. + * + * 'view' + * + * 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. + * + * 'data' + * + * Data that the model is returning for use in merging with the view to + * produce output. + * + * @wpdb object WordPress database object + * + * @return array Array containing status, suggested view, and any data + */ + public function __construct ( $wpdb, $config ) + { + + // Save WordPress Database object + $this->wpdb = $wpdb; + + // Save plugin configuration object + $this->config = $config; + + /* + * Run constructor for the Contacts data class + * + * Note, the third parameter is a flag that indicates to the Contacts + * data class that it should flag a group of fields as 'view_only'. + */ + // parent::__construct( false, false, true ); + + } + + public function modelAction($actionData = false) + { + + $haveAccounts = false; + $option = 'list'; + $view = 'invoicing'; + $wParts = array( 'true' ); + $paymentTypes = false; + $counties = false; + $accounts = false; + $paging = true; + $prevStart = false; + $nextStart = false; + $start = 1; + $limit = 20; // Set to the number of listings per page + $numbDisplayed = false; + $lastDisplayed = false; + $totalAccounts = false; + + // Get any provided option + if ( isset( $_REQUEST['option'] ) ) { + $option = $_REQUEST['option']; + } + + $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); + $Accounts = new GlmDataAccounts( $this->wpdb, $this->config ); + + echo '
$_REQUEST: ' . print_r( $_REQUEST, true ) . '
'; + + if ( isset( $_REQUEST['invoice_types'] ) ) { + + $invoiceTypes = $_REQUEST['invoice_types']; + $wParts[] = " T.invoice_type IN (" . implode(',', $invoiceTypes) . ") "; + + } + + if ( isset( $_REQUEST['counties'] ) ) { + + // $wParts[] = "T."; + + } + + // $where used in all places. + $where = implode( ' AND ', $wParts ); + + if ( isset( $_REQUEST['submitType'] ) ) { + + $option2 = filter_var( $_REQUEST['submitType'], FILTER_SANITIZE_STRING ); + + switch( $option2 ) { + case 'Create Invoices': + $accounts = $Accounts->getSimpleAccountList( $where ); + echo '
$accounts: ' . print_r( $accounts, true ) . '
'; + foreach ( $accounts as $account ) { + // Get the invoice type + $invoiceType = $BillingSupport->getInvoiceTypeById( $account['invoice_type'] ); + + // Create the invoice for this member account + $BillingSupport->createMemberInvoiceWithEmployees( + array( + 'account_id' => $account['id'], + 'renew_type_id' => $account['invoice_type'], + 'amount' => $invoiceType['amount'], + 'due_date' => date( 'Y-m-d' ), + 'member_invoice' => $account['invoice_type'], + 'employee_data' => array(), + 'employees' => array(), + ) + ); + } + + break; + case 'Print Invoices': + break; + case 'Send Invoices': + break; + default: + break; + } + + } + + // Do selected option + switch ($option) { + + case 'list': + default: + $view = 'invoicing'; + + // Check if Counties is enabled and fetch counties + if ( isset( $this->config['settings']['enable_counties'] ) && $this->config['settings']['enable_counties'] ) { + // Grab counties + $counties = $this->wpdb->get_results( + "SELECT * + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "counties + ORDER BY name", + ARRAY_A + ); + } + + $paymentTypes = $BillingSupport->getAllInvoiceTypes(); + + + if (isset($_REQUEST['pageSelect'])) { + // If request is for Next + if ($_REQUEST['pageSelect'][0] == 'N') { + $newStart = $_REQUEST['nextStart'] - 0; + + // Otherwise it must be Previous + } else { + $newStart = $_REQUEST['prevStart'] - 0; + } + + if ($newStart > 0) { + $start = $newStart; + } + } + $orderBy = 'member_name'; + $Accounts->paymentTypes = true; + $Accounts->balanceDue = true; + $accountsResult = $Accounts->getList( $where, $orderBy, true, 'id', $start, $limit ); + $totalAccounts = $Accounts->getStats( $where ); + $Accounts->paymentTypes = false; + $Accounts->balanceDue = false; + // Get paging results + $numbDisplayed = $accountsResult['returned']; + $lastDisplayed = $accountsResult['last']; + if ( $start == 1 ) { + $prevStart = false; + } else { + $prevStart = $start - $limit; + if ( $start < 1 ) { + $start = 1; + } + } + if ( $accountsResult['returned'] == $limit ) { + $nextStart = $start + $limit; + } + + // since we're doing paging, we have to break out just the accounts data + $accounts = $accountsResult['list']; + // echo '
$accounts: ' . print_r( $accounts, true ) . '
'; + if ( count( $accounts ) > 0 ) { + $haveAccounts = true; + } + + break; + + } + + + $templateData = array( + 'option' => $option, + 'paymentTypes' => $paymentTypes, + 'counties' => $counties, + 'accounts' => $accounts, + 'paging' => $paging, + 'prevStart' => $prevStart, + 'nextStart' => $nextStart, + 'start' => $start = 1, + 'limit' => $limit, + 'haveAccounts' => $haveAccounts, + 'numbDisplayed' => $numbDisplayed, + 'lastDisplayed' => $lastDisplayed, + 'totalAccounts' => $totalAccounts, + ); + + // Return status, any suggested view, and any data to controller + return array( + 'status' => true, + 'modelRedirect' => false, + 'view' => "admin/billing/$view.html", + 'data' => $templateData + ); + + } + +} diff --git a/models/admin/management/importAccounts.php b/models/admin/management/importAccounts.php index d260f02..c924574 100644 --- a/models/admin/management/importAccounts.php +++ b/models/admin/management/importAccounts.php @@ -143,7 +143,9 @@ $importResults .= 'Total Members: ' . $totalMembers . "
"; $sql = " 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 + 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 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 ) @@ -169,6 +171,18 @@ if ( $start === 0 ) { // $this->wpdb->query( "DELETE FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "transactions" ); } +$getState = $dbh->prepare( + "SELECT state_abb + FROM members.state + WHERE state_id = :id" +); + +$getCity = $dbh->prepare( + "SELECT city_name + FROM members.city + WHERE city_id = :id" +); + foreach ( $members as $member ) { $importResults .= '
$member: ' . print_r( $member, true ) . '
'; @@ -188,18 +202,55 @@ foreach ( $members as $member ) { $paymentTypeId = getPaymentTypeId( $this->wpdb, $member['payment_type'] ); - $refName = ( $member['billing_contact'] ) ? $member['billing_contact'] : $member['member_name']; + //$refName = ( $member['billing_contact'] ) ? $member['billing_contact'] : $member['member_name']; + $refName = $member['member_name']; + $city = $state = ''; + + if ( $member['mailing_address'] ) { + $addr1 = $member['mailing_address']; + if ( $member['mailing_city_id'] ) { + $getCity->bindParam( ':id', $member['mailing_city_id'] ); + $getCity->execute(); + $city = $getCity->fetchColumn(); + } + if ( $member['mailing_state_id'] ) { + $getState->bindParam( ':id', $member['mailing_state_id'] ); + $getState->execute(); + $state = $getState->fetchColumn(); + } + $zip = $member['mailing_zip']; + } else { + $addr1 = $member['street']; + $city = $member['city']; + $state = $member['state']; + if ( $member['city_id'] ) { + $getCity->bindParam( ':id', $member['city_id'] ); + $getCity->execute(); + $city = $getCity->fetchColumn(); + } + if ( $member['state_id'] ) { + $getState->bindParam( ':id', $member['state_id'] ); + $getState->execute(); + $state = $getState->fetchColumn(); + } + $zip = $member['zip']; + } $accountData = array( - 'ref_dest' => $newMemberId, - 'ref_name' => $refName, - 'invoice_type' => $paymentTypeId, - 'email' => $member['email'], - 'account_number' => $member['account_number'], - 'renewal_date' => '2018-07-01', - 'email_invoice' => $member['email_invoice'], - 'usmail_invoice' => $member['usmail_invoice'], - 'fax_invoice' => $member['fax_invoice'], + 'ref_dest' => $newMemberId, + 'ref_name' => $refName, + 'invoice_type' => $paymentTypeId, + 'email' => $member['email'], + 'account_number' => $member['account_number'], + 'renewal_date' => '2018-07-01', + 'email_invoice' => $member['email_invoice'], + 'usmail_invoice' => $member['usmail_invoice'], + 'fax_invoice' => $member['fax_invoice'], + 'billing_company' => $member['member_name'], + 'billing_addr1' => $addr1, + 'billing_city' => $city, + 'billing_state' => $state, + 'billing_zip' => $zip, ); $accountDataFormat = array( '%d', // ref_dest @@ -211,6 +262,11 @@ foreach ( $members as $member ) { '%d', // email_invoice '%d', // usmail_invoice '%d', // fax_invoice + '%s', // billing_company + '%s', // billing_addr1 + '%s', // billing_city + '%s', // billing_state + '%s', // billing_zip ); $importResults .= '
$accountData: ' . print_r( $accountData, true ) . '
'; diff --git a/setup/validActions.php b/setup/validActions.php index 2c0e1ae..e721194 100644 --- a/setup/validActions.php +++ b/setup/validActions.php @@ -80,13 +80,14 @@ $glmMembersBillingAddOnValidActions = array( 'notificationTypes' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, ), 'billing' => array( - 'index' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'list' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'invoices' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'payments' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'accounts' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'logs' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, - 'contact' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'index' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'list' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'invoices' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'payments' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'accounts' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'invoicing' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'logs' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'contact' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, ), 'member' => array( 'billing' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, diff --git a/views/admin/billing/bulkAddInvoices.html b/views/admin/billing/bulkAddInvoices.html deleted file mode 100644 index ba159b4..0000000 --- a/views/admin/billing/bulkAddInvoices.html +++ /dev/null @@ -1,41 +0,0 @@ -{include file='admin/billing/header.html'} - -{include file='admin/billing/subHeader.html'} - -

Bulk Create Invoices

- -{if $paymentTypes} - - - - - -{/if} - -{if $counties} - - - - - -{/if} - - - -
- - {foreach $accounts as $t} -
{$t.ref_name}
- {/foreach} - -
- -{include file='admin/footer.html'} diff --git a/views/admin/billing/editAccount.html b/views/admin/billing/editAccount.html index 4b106fb..311fd81 100644 --- a/views/admin/billing/editAccount.html +++ b/views/admin/billing/editAccount.html @@ -69,7 +69,7 @@ {/if} - {if !$settings.member_types_enabled} + {if $settings.invoice_methods_enabled}
Invoice Delivery Methods
diff --git a/views/admin/billing/header.html b/views/admin/billing/header.html index ff6dc65..f4ff6d7 100644 --- a/views/admin/billing/header.html +++ b/views/admin/billing/header.html @@ -5,6 +5,7 @@ Invoices Payments Accounts + Invoicing Logs
diff --git a/views/admin/billing/invoicing.html b/views/admin/billing/invoicing.html new file mode 100644 index 0000000..4ad2e46 --- /dev/null +++ b/views/admin/billing/invoicing.html @@ -0,0 +1,85 @@ +{include file='admin/billing/header.html'} + +{* include file='admin/billing/invoicingSubHeader.html' *} + +

Invoicing

+ +
+ + + + + + + +
+ {if $paymentTypes} + + + + + + {/if} + + {if $counties} + + + + + + {/if} + + + + + +
+ +

Total found: {$totalAccounts}

+ + {* Paging *} + {if $paging} + + + {/if} + +
+ +
+ + + + + + + + + + {foreach $accounts as $t} + + + + + + + {/foreach} +
Member Name Account Number Payment Type Balance Due
{$t.member_name} {$t.account_number} {$t.payment_type} {$t.balance_due|string_format:"%.2f"}
+
+ + {* Paging *} + {if $paging} + + + {/if} + +
+ +{include file='admin/footer.html'} diff --git a/views/admin/billing/memberBillingSubHeader.html b/views/admin/billing/memberBillingSubHeader.html index 01fb837..33439eb 100644 --- a/views/admin/billing/memberBillingSubHeader.html +++ b/views/admin/billing/memberBillingSubHeader.html @@ -1,13 +1,15 @@
Member Name: {$memberData.name}
-
{$memberData.member_type_short}
+ {if $settings.member_types_enabled} +
{$memberData.member_type_short}
+ {/if} {if $account_data} {if $settings.member_types_enabled}
Renewal date: {$account_data.renewal_date|strtotime|date_format}
Membership Status: {$account_status}
{else} - Uptra stuff here + {* Uptra stuff here *} {/if} {/if}
diff --git a/views/admin/billing/subHeader.html b/views/admin/billing/subHeader.html index 5f5266a..f7fcaf2 100644 --- a/views/admin/billing/subHeader.html +++ b/views/admin/billing/subHeader.html @@ -3,6 +3,4 @@ class="nav-tab{if $option == 'list'} nav-tab-active{/if}">Search Invoices Create Invoice - Create Bulk Invoices -- 2.17.1