From a60f710085fa9f8291ddb398b815e93b7669b451 Mon Sep 17 00:00:00 2001
From: Steve Sutton <steve@gaslightmedia.com>
Date: Tue, 19 Mar 2019 15:19:32 -0400
Subject: [PATCH] Billing import uptra data and accounts

Updating uptra imports.
---
 models/admin/billing/reports.php              |  2 +-
 models/admin/management/billing.php           | 20 +++++++-
 models/admin/management/importAccounts.php    |  2 +-
 models/admin/management/importBillingData.php | 21 +++++----
 models/admin/management/verifyBillingData.php | 46 +++++++++++++++++++
 views/admin/management/subHeader.html         |  2 +
 views/admin/management/verifyBillingData.html | 25 ++++++++++
 7 files changed, 105 insertions(+), 13 deletions(-)
 create mode 100644 models/admin/management/verifyBillingData.php
 create mode 100644 views/admin/management/verifyBillingData.html

diff --git a/models/admin/billing/reports.php b/models/admin/billing/reports.php
index 9c9a622..60a5084 100644
--- a/models/admin/billing/reports.php
+++ b/models/admin/billing/reports.php
@@ -222,7 +222,7 @@ class GlmMembersAdmin_billing_reports extends GlmDataTransactions
             $wParts[] = "T.id IN (
                 SELECT account
                   FROM " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "invoices
-                 WHERE balance > 0.00
+                 WHERE balance > 0 AND balance IS NOT NULL
                 )";
             $view = 'reports';
             break;
diff --git a/models/admin/management/billing.php b/models/admin/management/billing.php
index 18d25f4..1bd76c3 100644
--- a/models/admin/management/billing.php
+++ b/models/admin/management/billing.php
@@ -164,6 +164,24 @@ class GlmMembersAdmin_management_billing extends GlmDataBillingManagement
 
                 break;
 
+            case 'verifyBillingData':
+                $view = 'verifyBillingData';
+                if ( isset( $_REQUEST['option2'] ) ) {
+                    $option2 = $_REQUEST['option2'];
+                }
+                if ( !isset( $option2 ) ) {
+                    $option2 = '';
+                }
+                switch ( $option2 ) {
+                case 'verify':
+                    require_once GLM_MEMBERS_BILLING_PLUGIN_PATH.'/models/admin/management/verifyBillingData.php';
+                    // die('here');
+                    break;
+                default:
+                    break;
+                }
+                break;
+
             case 'importBillingData':
                 $view = 'importBillingData';
                 if ( isset( $_REQUEST['option2'] ) ) {
@@ -173,7 +191,7 @@ class GlmMembersAdmin_management_billing extends GlmDataBillingManagement
                     $option2 = '';
                 }
                 switch ( $option2 ) {
-                case 'import':
+                case 'verify':
                     require_once GLM_MEMBERS_BILLING_PLUGIN_PATH.'/models/admin/management/importBillingData.php';
                     // die('here');
                     break;
diff --git a/models/admin/management/importAccounts.php b/models/admin/management/importAccounts.php
index 858da6e..383df98 100644
--- a/models/admin/management/importAccounts.php
+++ b/models/admin/management/importAccounts.php
@@ -93,7 +93,7 @@ function addPaymentType( $wpdb, $data )
 
 // Connect to their live database.
 $dbh = new PDO(
-    'pgsql: host=ds4.gaslightmedia.com dbname=uptravel user=nobody',
+    'pgsql: host=ds5.gaslightmedia.com dbname=uptravel user=nobody',
     null,
     null,
     array(
diff --git a/models/admin/management/importBillingData.php b/models/admin/management/importBillingData.php
index 726f767..c433674 100644
--- a/models/admin/management/importBillingData.php
+++ b/models/admin/management/importBillingData.php
@@ -7,7 +7,7 @@ require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/billingSupport.php';
 $BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config );
 // Connect to their live database.
 $dbh = new PDO(
-    'pgsql: host=localhost dbname=uptravel user=postgres password=tweety',
+    'pgsql: host=ds5 dbname=uptravel user=postgres',
     null,
     null,
     array(
@@ -28,15 +28,16 @@ if ( isset( $_REQUEST['start'] ) && $start = filter_var( $_REQUEST['start'], FIL
 $importResults = '';
 
 // Get records.
-$sqlWhere = "
- WHERE invoice_id IN (
-       SELECT invoice_id
-         FROM members.billing
-        WHERE billing_type = 1
-          AND transaction_time >= '2017-01-01'
-        ORDER BY transaction_date,transaction_time)
-   AND billing_type IN (1,2,3)
-";
+$sqlWhere = "";
+// $sqlWhere = "
+//  WHERE invoice_id IN (
+//        SELECT invoice_id
+//          FROM members.billing
+//         WHERE billing_type = 1
+//          -- AND transaction_time >= '2017-01-01'
+//         ORDER BY transaction_date,transaction_time)
+//    AND billing_type IN (1,2,3)
+// ";
 $mainSql = "
 SELECT *
   FROM members.billing
diff --git a/models/admin/management/verifyBillingData.php b/models/admin/management/verifyBillingData.php
new file mode 100644
index 0000000..75b180d
--- /dev/null
+++ b/models/admin/management/verifyBillingData.php
@@ -0,0 +1,46 @@
+<?php
+/**
+ * Verify Billing Data (UPTRA)
+ */
+// For billing support functions
+require_once GLM_MEMBERS_BILLING_PLUGIN_CLASS_PATH . '/billingSupport.php';
+$BillingSupport = new GlmBillingSupport( $this->wpdb, $this->config );
+// Connect to their live database.
+$dbh = new PDO(
+    'pgsql: host=ds5 dbname=uptravel user=postgres',
+    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 records.
+$query = "
+SELECT id,name
+  FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
+ WHERE id in ( SELECT DISTINCT ref_dest
+                 FROM  " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts )
+LIMIT 20 OFFSET 0";
+
+$results = $this->wpdb->get_results( $query, ARRAY_A );
+
+$importResults .= '<pre>$results: ' . print_r( $results, true ) . '</pre>';
+
+
+foreach ( $results as $row ) {
+    $account_id = $BillingSupport->getAccountByRefDest( $row['id'] );
+    echo '<pre>$account_id: ' . print_r( $account_id, true ) . '</pre>';
+}
diff --git a/views/admin/management/subHeader.html b/views/admin/management/subHeader.html
index a986b39..2643ffc 100644
--- a/views/admin/management/subHeader.html
+++ b/views/admin/management/subHeader.html
@@ -6,4 +6,6 @@
     class="glm-settings-tab nav-tab{if $option == 'importAccounts'} nav-tab-active{/if}">Import Accounts</a>
     <a href="{$thisUrl}?page=glm-members-admin-menu-management&glm_action=billing&option=importBillingData"
     class="glm-settings-tab nav-tab{if $option == 'importBillingData'} nav-tab-active{/if}">Import Billing Data</a>
+    <a href="{$thisUrl}?page=glm-members-admin-menu-management&glm_action=billing&option=verifyBillingData"
+    class="glm-settings-tab nav-tab{if $option == 'verifyBillingData'} nav-tab-active{/if}">Verify Billing Data</a>
 </h2>
diff --git a/views/admin/management/verifyBillingData.html b/views/admin/management/verifyBillingData.html
new file mode 100644
index 0000000..b4cf372
--- /dev/null
+++ b/views/admin/management/verifyBillingData.html
@@ -0,0 +1,25 @@
+{include file='admin/management/header.html'}
+
+{include file='admin/management/subHeader.html'}
+
+{if $importResults}
+    {$importResults}
+{else}
+    <form action="{$thisUrl}?page={$thisPage}" method="post">
+
+        <input type="hidden" name="glm_action" value="billing" />
+        <input type="hidden" name="option" value="verifyBillingData" />
+        <input type="hidden" name="option2" value="verify" />
+
+        <table class="glm-admin-table glm-settings-table">
+
+            <tr>
+                <td><input type="submit" value="Verify Billing Data" /></td>
+            </tr>
+
+        </table>
+    </form>
+{/if}
+
+
+{include file='admin/management/footer.html'}
-- 
2.17.1