From f47d8c5346ba3277c007711d95aecb2dc83f0dbe Mon Sep 17 00:00:00 2001
From: Steve Sutton <steve@gaslightmedia.com>
Date: Thu, 3 May 2018 15:31:48 -0400
Subject: [PATCH] Update renewal dates

reset dates based on the 2017 or 2018 member categories.
---
 models/admin/migcsa/index.php | 119 ++++++++++++++++++++++++++++++----
 1 file changed, 105 insertions(+), 14 deletions(-)

diff --git a/models/admin/migcsa/index.php b/models/admin/migcsa/index.php
index 5cba2ce..36d86df 100644
--- a/models/admin/migcsa/index.php
+++ b/models/admin/migcsa/index.php
@@ -449,26 +449,117 @@ class GlmMembersAdmin_migcsa_index
 
     public function updateRenawalDates()
     {
+        $memberCategories = array();
+        $sql = "
+        SELECT *
+          FROM member_category
+         WHERE category_id = 40";
+        $stmt = $this->dbh->query( $sql );
+        $memberCategories['2017'] = $stmt->fetchAll();
+        $sql = "
+        SELECT *
+          FROM member_category
+         WHERE category_id = 42";
+        $stmt = $this->dbh->query( $sql );
+        $memberCategories['2018'] = $stmt->fetchAll();
+
         $sql = "
         SELECT member_id,renew_date
           FROM member
-         WHERE renew_date IS NOT NULL
-           AND renew_date >= '01/01/2016'";
-        $stmt    = $this->dbh->query( $sql );
-        $members = $stmt->fetchAll();
+         WHERE member_id = :member_id";
+        $getMember = $this->dbh->prepare( $sql );
+
         $this->wpdb->query('START TRANSACTION');
-        foreach ( $members as $m ) {
-            $this->wpdb->query(
-                "UPDATE " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts
-                    SET renewal_date = '" . $m['renew_date']  . "'
-                  WHERE ref_dest = (
-                        SELECT id
-                          FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
-                         WHERE old_member_id = " . $m['member_id'] . " )"
-            );
+        foreach ( $memberCategories as $year => $member_cat ) {
+            echo '<b>year</b>: ' . $year;
+            foreach ( $member_cat as $cat ) {
+                $getMember->bindParam( ':member_id', $cat['member_id'], PDO::PARAM_INT );
+                $getMember->execute();
+                $member = $getMember->fetch();
+                $renew_date = $member['renew_date'];
+                echo '<pre>$renew_date: ' . print_r( $renew_date, true ) . '</pre>';
+                $parts = explode( '-', $renew_date );
+                echo '<pre>$parts: ' . print_r( $parts, true ) . '</pre>';
+                $y = $parts[0];
+                $m = $parts[1];
+                $d = $parts[2];
+
+                echo '<pre>$y: ' . print_r( $y, true ) . '</pre>';
+                echo '<pre>$m: ' . print_r( $m, true ) . '</pre>';
+                echo '<pre>$d: ' . print_r( $d, true ) . '</pre>';
+                // break 2;
+
+                echo '<pre>$member: ' . print_r( $member, true ) . '</pre>';
+                if ( $year == '2017' ) {
+                    // check the year
+                    if ( $y != $year ) {
+                        $new_date = date( 'Y-m-d', mktime( 0, 0, 0, $m, $d, 2017 ) );
+                        $this->wpdb->query(
+                            "UPDATE " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts
+                                SET renewal_date = '" . $new_date  . "'
+                              WHERE ref_dest = (
+                                    SELECT id
+                                      FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
+                                     WHERE old_member_id = " . $cat['member_id'] . " )"
+                        );
+                    }
+                } else {
+                    if ( $y != $year ) {
+                        // make sure the date is in the past.
+                        $current_date_time = mktime( 0, 0, 0, date('n'), date('j'), 2018 );
+                        $renewal_date_time = mktime( 0, 0, 0, (int)$m, (int)$d, 2018 );
+                        if ( $renewal_date_time > $current_date_time ) {
+                            $renewal_date_time = mktime( 0, 0, 0, (int)$m, (int)$d, 2017 );
+                        }
+                        $new_date = date( 'Y-m-d', $renewal_date_time );
+                        $this->wpdb->query(
+                            "UPDATE " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts
+                                SET renewal_date = '" . $new_date  . "'
+                              WHERE ref_dest = (
+                                    SELECT id
+                                      FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
+                                     WHERE old_member_id = " . $cat['member_id'] . " )"
+                        );
+                    }
+
+                }
+            }
         }
         $this->wpdb->query('COMMIT');
-        return '<pre>$members: ' . print_r( $members, true ) . '</pre>';
+        // $this->wpdb->query('ROLLBACK');
+
+        // $sql = "
+        // SELECT member_id,renew_date
+        //   FROM member
+        //  WHERE member_id IN (SELECT member_id
+        //                        FROM member_category
+        //                       WHERE category_id IN (40,42))";
+        // $stmt    = $this->dbh->query( $sql );
+        // $members = $stmt->fetchAll();
+        //
+        // $catsSQL2017 = "SELECT member_category_id FROM member_category WHERE member_id = :member_id AND category_id = 40";
+        // $catsSQL2018 = "SELECT member_category_id FROM member_category WHERE member_id = :member_id AND category_id = 42";
+        // $cat2017     = $this->dbh->prepare( $catsSQL2017 );
+        // $cat2018     = $this->dbh->prepare( $catsSQL2018 );
+        //
+        // $this->wpdb->query('START TRANSACTION');
+        // foreach ( $members as $m ) {
+        //     echo '<pre>$m: ' . print_r( $m, true ) . '</pre>';
+        //     $cat2017->bindParam( ':member_id', $m['member_id'], PDO::PARAM_INT );
+        //     $cat2017->execute();
+        //     $cats = $cat2017->fetchAll();
+        //     echo '<pre>$memberCats: ' . print_r( $cats, true ) . '</pre>';
+            // $this->wpdb->query(
+            //     "UPDATE " . GLM_MEMBERS_BILLING_PLUGIN_DB_PREFIX . "accounts
+            //         SET renewal_date = '" . $m['renew_date']  . "'
+            //       WHERE ref_dest = (
+            //             SELECT id
+            //               FROM " . GLM_MEMBERS_PLUGIN_DB_PREFIX . "members
+            //              WHERE old_member_id = " . $m['member_id'] . " )"
+            // );
+        // }
+        // $this->wpdb->query('COMMIT');
+        // return '<pre>$members: ' . print_r( $members, true ) . '</pre>';
 
     }
 
-- 
2.17.1