From 2b3636715882e16c1f7c3dd8c1b250e0d9a29964 Mon Sep 17 00:00:00 2001
From: Steve Sutton <steve@gaslightmedia.com>
Date: Wed, 22 May 2019 14:25:54 -0400
Subject: [PATCH] Updating billing account import

Import only 2018 data and accounts.
---
 models/admin/billing/index.php                |  5 ++++
 models/admin/billing/invoices.php             |  2 +-
 models/admin/billing/reports.php              |  2 +-
 models/admin/management/importAccounts.php    | 27 ++++++++++---------
 models/admin/management/importBillingData.php |  1 +
 5 files changed, 22 insertions(+), 15 deletions(-)

diff --git a/models/admin/billing/index.php b/models/admin/billing/index.php
index f20ddc4..547b317 100644
--- a/models/admin/billing/index.php
+++ b/models/admin/billing/index.php
@@ -241,6 +241,11 @@ class GlmMembersAdmin_billing_index extends GlmDataAccounts
                      WHERE due_date < '$curDate'
                        AND ( paid <> true OR paid IS NULL )
                 )";
+                $overDueWhere       .= " AND T.ref_dest IN (
+                    SELECT id
+                      FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
+                     WHERE access IN ( 30, 40, 20 )
+                )";
                 $start               = 1;
                 $this->postEmployees = true;
                 $overdue             = $this->getList( $overDueWhere, $orderBy, true, 'id', $start, $limit );
diff --git a/models/admin/billing/invoices.php b/models/admin/billing/invoices.php
index 596a01e..7ec9ff7 100644
--- a/models/admin/billing/invoices.php
+++ b/models/admin/billing/invoices.php
@@ -657,7 +657,7 @@ class GlmMembersAdmin_billing_invoices extends GlmDataInvoices
             if ( isset( $_REQUEST['filterUnpaid'] ) ) {
                 $filterUnpaid = filter_var( $_REQUEST['filterUnpaid'], FILTER_VALIDATE_BOOLEAN );
                 if ( $filterUnpaid ) {
-                    $where_params[] = "T.paid <> true";
+                    $where_params[] = "(T.paid <> true OR T.paid IS NULL)";
                 }
             }
             if ( isset( $_REQUEST['filterInvoice'] ) && $_REQUEST['filterInvoice'] ) {
diff --git a/models/admin/billing/reports.php b/models/admin/billing/reports.php
index 1bff06a..3487fb4 100644
--- a/models/admin/billing/reports.php
+++ b/models/admin/billing/reports.php
@@ -254,7 +254,7 @@ class GlmMembersAdmin_billing_reports extends GlmDataTransactions
             }
         }
         if ( !in_array( $option, array( 'reportGenerator', 'noAccounts' ) ) ) {
-            echo '<pre>$where: ' . print_r( $where, true ) . '</pre>';
+            // echo '<pre>$where: ' . print_r( $where, true ) . '</pre>';
             // Getting the account listings
             $orderBy                = 'member_name';
             $Accounts->paymentTypes = true;
diff --git a/models/admin/management/importAccounts.php b/models/admin/management/importAccounts.php
index b0f0173..4571e0f 100644
--- a/models/admin/management/importAccounts.php
+++ b/models/admin/management/importAccounts.php
@@ -92,7 +92,7 @@ function addPaymentType( $wpdb, $data )
 }
 
 // Connect to their live database.
-$dbConn = 'pgsql: host=ds5 dbname=uptravel user=postgres';
+$dbConn = 'pgsql: host=ds5.gaslightmedia.com dbname=uptravel user=postgres';
 // $dbConn = 'pgsql: dbname=uptravel user=postgres';
 $dbh = new PDO(
     $dbConn,
@@ -124,36 +124,37 @@ ORDER BY id";
 $paymentTypes = $dbh->query( $sql )->fetchAll();
 foreach ( $paymentTypes as $pType ) {
     $pid = addPaymentType( $this->wpdb, $pType );
-    $importResults .= '<pre>$pid: ' . print_r( $pid, true ) . '</pre>';
+    // $importResults .= '<pre>$pid: ' . print_r( $pid, true ) . '</pre>';
 }
-$importResults .= '<pre>$paymentTypes: ' . print_r( $paymentTypes, true ) . '</pre>';
+// $importResults .= '<pre>$paymentTypes: ' . print_r( $paymentTypes, true ) . '</pre>';
 
-// Get total number of members.
-$sql = "
-SELECT count(M.member_id) as total
+$fromWhere = "
   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 )
  WHERE PT.name != ''
    AND PT.name IS NOT NULL
-   -- AND (M.type = 'full' OR M.type = 'enhanced')
+   AND M.member_id IN ( SELECT DISTINCT member_id FROM members.billing where transaction_date >= '01/01/2018' )
+";
+
+// Get total number of members.
+$sql = "
+SELECT count(M.member_id) as total
+ $fromWhere
 ";
 $totalStmt      = $dbh->query( $sql );
 $totalMembers   = $totalStmt->fetchColumn();
 $importResults .= 'Total Members: ' . $totalMembers . "<br>";
 
+return false;
+
 $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,
        M.mailing_address,M.mailing_city_id,M.mailing_state_id,M.mailing_zip,
        M.street,M.city_id,M.state_id,M.zip,M.region
-  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 )
- WHERE PT.name != ''
-   AND PT.name IS NOT NULL
-  -- AND (M.type = 'full' OR M.type = 'enhanced')
+ $fromWhere
 ORDER BY M.member_id";
 
 //LIMIT 10
diff --git a/models/admin/management/importBillingData.php b/models/admin/management/importBillingData.php
index 5191399..e5cf72c 100644
--- a/models/admin/management/importBillingData.php
+++ b/models/admin/management/importBillingData.php
@@ -60,6 +60,7 @@ SELECT id,invoice_id,transaction_time,transaction_date,member_name,member_id,acc
        printed,paid,notes
   FROM members.billing
 WHERE member_id = :member_id
+  AND transaction_date >= '2018/01/01'
 ORDER BY transaction_date,transaction_time";
 $getBilling = $dbh->prepare( $billingStmtSql );
 
-- 
2.17.1