From f6b140a5e8efac371486d436ed12290f843f1a33 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Fri, 14 Dec 2018 15:20:18 -0500 Subject: [PATCH] Getting QIF export and noAccount list Members without accounts working. Worked on qif export. --- classes/data/dataAccounts.php | 13 +- css/admin.css | 27 +++ models/admin/ajax/exportQIF.php | 197 +++++++++++++++++++++ models/admin/billing/reports.php | 79 +++++++-- setup/adminMenus.php | 19 +- setup/validActions.php | 1 + views/admin/billing/accountSearchForm.html | 36 ++-- views/admin/billing/accounts.html | 2 +- views/admin/billing/invoices.html | 42 ++--- views/admin/billing/payments.html | 17 +- views/admin/billing/reports.html | 73 +++++--- 11 files changed, 414 insertions(+), 92 deletions(-) create mode 100644 models/admin/ajax/exportQIF.php diff --git a/classes/data/dataAccounts.php b/classes/data/dataAccounts.php index c1d7044..c162442 100644 --- a/classes/data/dataAccounts.php +++ b/classes/data/dataAccounts.php @@ -115,7 +115,14 @@ class GlmDataAccounts extends GlmDataAbstract } - $billingFieldsRequired = $this->config['settings']['billing_fields_required']; + $billingNameFieldsRequired = false; + $billingFieldsRequired = $this->config['settings']['billing_fields_required']; + $billingContactNameEnabled = $this->config['settings']['billing_contact_name_enabled']; + if ( $billingFieldsRequired && $billingContactNameEnabled ) { + $billingNameFieldsRequired = false; + } else if ( $billingFieldsRequired ) { + $billingNameFieldsRequired = true; + } /* * Table Name @@ -271,7 +278,7 @@ class GlmDataAccounts extends GlmDataAbstract 'field' => 'billing_fname', 'type' => 'text', 'use' => 'a', - 'required' => $billingFieldsRequired, + 'required' => $billingNameFieldsRequired, ), // Billing Last Name @@ -279,7 +286,7 @@ class GlmDataAccounts extends GlmDataAbstract 'field' => 'billing_lname', 'type' => 'text', 'use' => 'a', - 'required' => $billingFieldsRequired, + 'required' => $billingNameFieldsRequired, ), // Billing Address 1 diff --git a/css/admin.css b/css/admin.css index bde9f9f..ac72bd7 100644 --- a/css/admin.css +++ b/css/admin.css @@ -156,3 +156,30 @@ text-decoration: underline; font-size: 12px; } +/* billing search form styles */ +.billing-search-form-select { + width: 200px; + float: left; +} +.billing-search-form-select label { + font-weight: bold; +} +.billing-search-form-container { + width: 200px; + float: left; + margin-right: 5px; +} +.billing-search-form-container label { + font-weight: bold; +} +.billing-search-form-submit { + width: 400px; + height: 30px; + clear: left; + float: left; +} +.billing-search-form-checkbox { + width: 100%; + float:left; + font-weight: normal; +} diff --git a/models/admin/ajax/exportQIF.php b/models/admin/ajax/exportQIF.php new file mode 100644 index 0000000..b1d707e --- /dev/null +++ b/models/admin/ajax/exportQIF.php @@ -0,0 +1,197 @@ + + * @license http://www.gaslightmedia.com Gaslightmedia + * @version 0.1 + */ + +require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/billingSupport.php'; +require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/data/dataTransactions.php'; + +/** + * Steve Note + * + * You can get to this using the following URL. + * + * + {host}/wp-admin/admin-ajax.php?action=glm_members_admin_ajax&glm_action=runQueue + * + * You should be able to do this as POST or GET and should be able to add and read additional parameters. + * I added a "mystuff" parameter to the URL above and it does output from the code in the + * modelAction() function below. + * + * To add another model under models/admin/ajax all you need to do is create it and add it to the + * setup/validActions.php file. + * + */ + +/** + * This class handles the work of creating new invoices based on. + * 1) Member Type of member matching a paid invoiceType + * 2) Member renewal date past + * 3) Member has Billing Account + * 4) Member has no active Invoice + * 5) Renewal date is within the next 30 Days + * + */ +class GlmMembersAdmin_ajax_exportQIF extends GlmDataTransactions +{ + + const HEADERFORMAT = "!Type:Bank\nD%s\nT%s\nP%s\nL[%s]\n^\n"; + const INVOICEFORMAT = "!D%s\nT%s\nP%s\nL%s\n^\n"; + const PAYMENTFORMAT = "D%s\nT%s\nP%s\nN%s\nM%s\nL%s\n^\n"; + const MAINCATEGORY = 'Member Services:'; + const INVCATEGORY = 'Other Inc'; + + /** + * WordPress Database Object + * + * @var $wpdb + * @access public + */ + public $wpdb; + /** + * Plugin Configuration Data + * + * @var $config + * @access public + */ + public $config; + + public function __construct ($wpdb, $config) + { + + // Save WordPress Database object + $this->wpdb = $wpdb; + + // Save plugin configuration object + $this->config = $config; + + parent::__construct( false, false, true ); + + } + + public function modelAction( $actionData = false ) + { + $fileData = array(); + + $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config ); + + // Query Params + if ( isset( $_REQUEST['invoice_types'] ) && $invoiceTypes = filter_var( $_REQUEST['invoice_types'], FILTER_VALIDATE_INT,array( 'flags' => FILTER_FORCE_ARRAY ) ) ) { + // $invoiceTypes = $_REQUEST['invoice_types']; + $wParts[] = " T.invoice_type IN (" . implode(',', $invoiceTypes) . ") "; + $reportParts[] = " T.account IN ( + SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE invoice_type IN (" . implode(',', $invoiceTypes) . ") + ) "; + } + + if ( isset( $_REQUEST['counties'] ) && $countiesSelected = filter_var( $_REQUEST['counties'], FILTER_VALIDATE_INT,array( 'flags' => FILTER_FORCE_ARRAY ) ) ) { + // $countiesSelected = $_REQUEST['counties']; + $wParts[] = "T.billing_county IN (" . implode(',', $countiesSelected ) . ")"; + $reportParts[] = " T.account IN ( + SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE billing_county IN (" . implode(',', $countiesSelected) . ") + ) "; + } + + if ( isset( $_REQUEST['member_name'] ) && $member_name = filter_var( $_REQUEST['member_name'], FILTER_SANITIZE_STRING ) ) { + $reportParts[] = "T.account IN ( + SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE ref_name like '%" . esc_sql( $member_name ) . "%' + )"; + } + if ( isset( $_REQUEST['account_number'] ) && $account_number = filter_var( $_REQUEST['account_number'], FILTER_SANITIZE_STRING ) ) { + $reportParts[] = "T.account IN ( + SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE account_number = '" . esc_sql( $account_number ) . "' + )"; + } + + $reportParts[] = "T.type = " . $this->config['transaction_numb']['Payment']; + + $dateRegExp = '%[0-9]{2}/[0-9]{2}/[0-9]{4}%'; + if ( isset( $_REQUEST['from_date'] ) && $fromDate = filter_var( $_REQUEST['from_date'], FILTER_VALIDATE_REGEXP, array( 'options' => array( 'regexp' => $dateRegExp ) ) ) ) { + $reportParts[] = "T.transaction_time >= '" . date( 'Y-m-d', strtotime( $fromDate ) ) . "'"; + } + if ( isset( $_REQUEST['to_date'] ) && $toDate = filter_var( $_REQUEST['to_date'], FILTER_VALIDATE_REGEXP, array( 'options' => array( 'regexp' => $dateRegExp ) ) ) ) { + $reportParts[] = "T.transaction_time <= '" . date( 'Y-m-d', strtotime( $toDate . '+1 day' ) ) . "'"; + } + + + // $where used in all places. + $reportWhere = implode( ' AND ', $reportParts ); + $orderBy = 'account,transaction_time,type'; + $this->member_data = true; + $this->notes = true; + $transactions = $this->getList( $reportWhere, $orderBy ); + $this->member_data = false; + $this->notes = false; + + // Open file pointer to php://output (Direct to browser) + $fp = fopen( 'php://output', 'w' ); + + $paymentTypes = $BillingSupport->getAllInvoiceTypes(); + + // Generate the QIF format data + $fileData[] = "!Type:Bank\n"; + foreach ( $transactions as $record ) { + 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 + ); + } + break; + } + } + $buf = implode( '', $fileData ); + $len = strlen( $buf ); + + // Setup headers for forcing a file download. + if (ini_get('zlib.output_compression')) { + ini_set('zlib.output_compression', 'Off'); + } + header("Content-Type: application/force-download\n"); + /* Correction for the stupid MSIE thing */ + $fileName = 'uptraImport-'.date('m-d-Y').'.qif'; + if (strstr(getenv('HTTP_USER_AGENT'), 'MSIE')) { + header("Content-Disposition: inline; filename=\"$fileName\""); + } else { + header("Content-Disposition: attachment; filename=\"$fileName\""); + } + + echo $buf; + + // Close the file pointer + fclose( $fp ); + + wp_die(); + } + +} diff --git a/models/admin/billing/reports.php b/models/admin/billing/reports.php index bc27656..e7559a6 100644 --- a/models/admin/billing/reports.php +++ b/models/admin/billing/reports.php @@ -93,6 +93,9 @@ class GlmMembersAdmin_billing_reports extends GlmDataTransactions $option = 'openAccounts'; $view = 'reports'; $wParts = array( 'true' ); + $where = false; + $reportParts = array( 'true' ); + $reportWhere = false; $paymentTypes = false; $counties = false; $accounts = false; @@ -118,24 +121,53 @@ class GlmMembersAdmin_billing_reports extends GlmDataTransactions // 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['invoice_types'] ) && $invoiceTypes = filter_var( $_REQUEST['invoice_types'], FILTER_VALIDATE_INT,array( 'flags' => FILTER_FORCE_ARRAY ) ) ) { + // $invoiceTypes = $_REQUEST['invoice_types']; + $wParts[] = " T.invoice_type IN (" . implode(',', $invoiceTypes) . ") "; + $reportParts[] = " T.account IN ( + SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE invoice_type IN (" . implode(',', $invoiceTypes) . ") + ) "; } - if ( isset( $_REQUEST['counties'] ) ) { - - $countiesSelected = $_REQUEST['counties']; - $wParts[] = "T.billing_county IN (" . implode(',', $countiesSelected ) . ")"; + if ( isset( $_REQUEST['counties'] ) && $countiesSelected = filter_var( $_REQUEST['counties'], FILTER_VALIDATE_INT,array( 'flags' => FILTER_FORCE_ARRAY ) ) ) { + // $countiesSelected = $_REQUEST['counties']; + $wParts[] = "T.billing_county IN (" . implode(',', $countiesSelected ) . ")"; + $reportParts[] = " T.account IN ( + SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE billing_county IN (" . implode(',', $countiesSelected) . ") + ) "; + } + if ( isset( $_REQUEST['member_name'] ) && $member_name = filter_var( $_REQUEST['member_name'], FILTER_SANITIZE_STRING ) ) { + $reportParts[] = "T.account IN ( + SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE ref_name like '%" . esc_sql( $member_name ) . "%' + )"; + } + if ( isset( $_REQUEST['account_number'] ) && $account_number = filter_var( $_REQUEST['account_number'], FILTER_SANITIZE_STRING ) ) { + $reportParts[] = "T.account IN ( + SELECT id + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts + WHERE account_number = '" . esc_sql( $account_number ) . "' + )"; + } + if ( isset( $_REQUEST['transactionTypes'] ) && $transactionTypes = filter_var( $_REQUEST['transactionTypes'], FILTER_VALIDATE_INT,array( 'flags' => FILTER_FORCE_ARRAY ) ) ) { + $reportParts[] = "T.type IN (" . implode(',', $transactionTypes) . ")"; + } + $dateRegExp = '%[0-9]{2}/[0-9]{2}/[0-9]{4}%'; + if ( isset( $_REQUEST['from_date'] ) && $fromDate = filter_var( $_REQUEST['from_date'], FILTER_VALIDATE_REGEXP, array( 'options' => array( 'regexp' => $dateRegExp ) ) ) ) { + $reportParts[] = "T.transaction_time >= '" . date( 'Y-m-d', strtotime( $fromDate ) ) . "'"; + } + if ( isset( $_REQUEST['to_date'] ) && $toDate = filter_var( $_REQUEST['to_date'], FILTER_VALIDATE_REGEXP, array( 'options' => array( 'regexp' => $dateRegExp ) ) ) ) { + $reportParts[] = "T.transaction_time <= '" . date( 'Y-m-d', strtotime( $toDate . '+1 day' ) ) . "'"; } if ( isset( $_REQUEST['submitType'] ) ) { - $option2 = filter_var( $_REQUEST['submitType'], FILTER_SANITIZE_STRING ); - } // Do selected option @@ -164,6 +196,8 @@ class GlmMembersAdmin_billing_reports extends GlmDataTransactions break; case 'noAccounts': + // Get a list of members that are in full and basic member types. + break; case 'reportGenerator': @@ -194,7 +228,8 @@ class GlmMembersAdmin_billing_reports extends GlmDataTransactions } // $where used in all places. - $where = implode( ' AND ', $wParts ); + $where = implode( ' AND ', $wParts ); + $reportWhere = implode( ' AND ', $reportParts ); if (isset($_REQUEST['pageSelect'])) { @@ -226,12 +261,27 @@ class GlmMembersAdmin_billing_reports extends GlmDataTransactions $orderBy = 'transaction_time'; $this->member_data = true; $this->notes = true; - $accountsResult = $this->getList( $where, $orderBy, true, 'id', $start, $limit ); - $totalAccounts = $this->getStats( $where ); + $accountsResult = $this->getList( $reportWhere, $orderBy, true, 'id', $start, $limit ); + $totalAccounts = $this->getStats( $reportWhere ); $this->member_data = false; $this->notes = false; break; case 'noAccounts': + $sql = " + SELECT id as ref_dest, name as member_name,'' as account_number,'' as payment_type, 'N/A' as balance_due + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members + WHERE id NOT IN ( + SELECT DISTINCT ref_dest + FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts) + AND member_type IN ( + SELECT id + FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "member_type + WHERE name IN ('full', 'enhanced') + ) + ORDER BY name"; + // echo '
$sql: ' . print_r( $sql, true ) . '
'; + $accounts = $this->wpdb->get_results( $sql, ARRAY_A ); + $paging = false; break; default: break; @@ -296,6 +346,7 @@ class GlmMembersAdmin_billing_reports extends GlmDataTransactions $templateData = array( 'tActionTypes' => $this->config['transaction_numb'], + 'actionTypeSel' => $this->config['transaction_type'], 'option' => $option, 'paymentTypes' => $paymentTypes, 'counties' => $counties, diff --git a/setup/adminMenus.php b/setup/adminMenus.php index 7a6ca6e..fcc9b87 100644 --- a/setup/adminMenus.php +++ b/setup/adminMenus.php @@ -51,14 +51,17 @@ */ if (isset($this->config['loggedInUser']) && isset($this->config['loggedInUser']['contactUser']) && $this->config['loggedInUser']['contactUser']) { - add_submenu_page( - $mainMenuSlug, - 'Billing', - 'Billing', - 'glm_members_edit_my_entity', - 'glm-members-admin-menu-billing-index', - function() { $this->controller('member', 'billing'); } - ); + $memberBillingEnabled = $this->config['settings']['member_billing_enabled']; + if ( $memberBillingEnabled ) { + add_submenu_page( + $mainMenuSlug, + 'Billing', + 'Billing', + 'glm_members_edit_my_entity', + 'glm-members-admin-menu-billing-index', + function() { $this->controller('member', 'billing'); } + ); + } } else { add_submenu_page( 'glm-members-admin-menu-members', diff --git a/setup/validActions.php b/setup/validActions.php index 6c8111a..18c2061 100644 --- a/setup/validActions.php +++ b/setup/validActions.php @@ -68,6 +68,7 @@ $glmMembersBillingAddOnValidActions = array( 'createNewInvoices' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, 'createPDFInvoice' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, 'printInvoices' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, + 'exportQIF' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, 'createPDFLabels' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, 'createCSVLabels' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, 'accountsListExport' => GLM_MEMBERS_BILLING_PLUGIN_SLUG, diff --git a/views/admin/billing/accountSearchForm.html b/views/admin/billing/accountSearchForm.html index 48058c0..f760448 100644 --- a/views/admin/billing/accountSearchForm.html +++ b/views/admin/billing/accountSearchForm.html @@ -83,25 +83,33 @@ jQuery(document).ready(function($) { -
- - From Date: - To Date: - - +
+
+ + + + +
+
Member Account:  - -
- +
+
+ + + + +
+
+
+
diff --git a/views/admin/billing/accounts.html b/views/admin/billing/accounts.html index f6619d3..443f145 100644 --- a/views/admin/billing/accounts.html +++ b/views/admin/billing/accounts.html @@ -34,7 +34,7 @@ {foreach $accounts as $t} {$t.id} - + {if $settings.allow_employees} {if $t.boss.value}Yes{/if} {/if} diff --git a/views/admin/billing/invoices.html b/views/admin/billing/invoices.html index 64b86c0..aaea34a 100644 --- a/views/admin/billing/invoices.html +++ b/views/admin/billing/invoices.html @@ -14,28 +14,28 @@ -
-

- - From Date: - To Date: - - - Member Account:  - - - -
- +

+
+ + + + +
+
+ + + +
+
+
+
+ +
+

diff --git a/views/admin/billing/payments.html b/views/admin/billing/payments.html index f0ab051..cebf085 100644 --- a/views/admin/billing/payments.html +++ b/views/admin/billing/payments.html @@ -14,22 +14,23 @@ -
- +
+
From Date: To Date: - - +
+
Member Account:  - -
- +
+
- +
+
+ {if $paging} diff --git a/views/admin/billing/reports.html b/views/admin/billing/reports.html index d9ce994..1536c0c 100644 --- a/views/admin/billing/reports.html +++ b/views/admin/billing/reports.html @@ -11,9 +11,9 @@ {if $option == 'reportGenerator'}
{if $paymentTypes} -
-
- {foreach $paymentTypes as $paymentType} {/foreach} @@ -21,17 +21,39 @@
{/if} {if $counties} -
-
- {foreach $counties as $county} {/foreach}
{/if} -
- +
+ + + + + + + + +
+
+ + +
+ +
+
+
{/if} @@ -67,13 +89,18 @@ {$t.account_number} {$t.payment_type} - {$t.balance_due|string_format:"%.2f"} + + {if $t.balance_due == 'N/A'} + {$t.balance_due} + {else} + {$t.balance_due|string_format:"%.2f"} + {/if} + {/foreach}
{/if} - {debug} {if isset( $transactions ) && !empty( $transactions )}
@@ -122,23 +149,23 @@