Running the API from a cronjob

[yamyoume]

Hello, sorry again, I'm opening too many issues, now that I've got everything to work, I'm trying to run my script from a Cron Job but I think something is not right with tokenization (it works from a browser !) but not in CLI

[13-Apr-2020 19:35:01 America/Chicago] PHP Fatal error:  Uncaught exception 'Infusionsoft\TokenExpiredException' in /home1/s9tuvijs/private/vendor/infusionsoft/php-sdk/src/Infusionsoft/Infusionsoft.php:484
Stack trace:
#0 /home1/s9tuvijs/private/vendor/infusionsoft/php-sdk/src/Infusionsoft/Api/Rest/RestModel.php(243): Infusionsoft\Infusionsoft->restfulRequest('get', 'https://api.inf...', Array)
#1 /home1/s9tuvijs/public_html/api/cron/infusionsoft_sync_contacts.php(19): Infusionsoft\Api\Rest\RestModel->get()
#2 {main}
  thrown in /home1/s9tuvijs/private/vendor/infusionsoft/php-sdk/src/Infusionsoft/Infusionsoft.php on line 484

[yamyoume]

SO yes basically to get it to work from CLI , I had to store the $token in the database and now it works as expected, haven't tested out the refreshAccessToken(); yet, but in 6 hours I'll know, and also I haven't tested when the refresh_token expire because that actually takes about 90 days if i'm not mistaken, will keep an eye on it though here's the code:

// If the serialized token is available in the session storage, we tell the SDK
// to use that token for subsequent requests.
if (isset($_SESSION['token'])) {
    $this->setToken(unserialize($_SESSION['token']));
} else {
    $stmt = DatabaseObject::$pdo->query("SELECT infusionsoft_access_token FROM constants");
    $token = unserialize($stmt->fetch()['infusionsoft_access_token']);
    if (!empty($token)) {
        $_SESSION['token'] = $this->setToken($token);
    }
}

// If we are returning from Infusionsoft we need to exchange the code for an
// access token.
if (isset($_GET['code']) and !$this->getToken()) {
    $token = serialize($this->requestAccessToken($_GET['code']));
    $_SESSION['token'] = $token;
    $stmt = DatabaseObject::$pdo->prepare("UPDATE constants SET infusionsoft_access_token = :code");
    $stmt->execute(["code" => $token]);
}

 if (isset($_SESSION['token']) && $this->isTokenExpired()) {
    $this->refreshAccessToken();
}

if ($this->getToken()) {
    // Save the serialized token to the current session for subsequent requests
    $_SESSION['token'] = serialize($this->getToken());

    // MAKE INFUSIONSOFT REQUEST
} else {
    echo '<a href="' . $this->getAuthorizationUrl() . '">Click here to authorize</a>';
}

[xemacscode]

how did you manage to make it work in CRON? I believe the script you posted is for the initial oauth process not the refreshing the access token after 24 hours has passed.

[yamyoume]

Yes, that's what I'm struggling with, I'll post here if I figure it out

[xemacscode]

I'm trying to figure this one out too. So I'll post what I did so far:

$stmtuser = $db->prepare("SELECT * FROM users");
$stmtuser->execute();
$users = $stmtuser->fetchAll(PDO::FETCH_ASSOC);

$infusionsoft = new \Infusionsoft\Infusionsoft(array(
    'clientId'     => 'clientidvalue',
    'clientSecret' => 'clientsecrentvalue',
    'redirectUri'  => 'uri',
));
// I have multiple users 
foreach($users as $user){
    if(!empty($user['itoken'])){ //db column itoken
                $itoken = unserialize($user['itoken']);
                $infusionsoft->setToken($itoken);
                $tokentime = $infusionsoft->isTokenExpired();
                if($tokentime){

                    // var_dump($infusionsoft->getToken());

                    $infusionsoft->refreshAccessToken();        //request to refresh access token
                    $ntoken = $infusionsoft->getToken();        //get the token
                    $srtoken = serialize($ntoken);                  //serialize for db

                    $tokens = json_decode(json_encode($ntoken), true);
                    $endoflife = $tokens['endOfLife'];

                    $tokens = $db->prepare("UPDATE users SET itoken = ?, iendoflife = ?  WHERE id = ?");
                  $tokens->execute([$srtoken, $endoflife, $user['id']]);
                }
    }
}

[yamyoume]

Hi, so i just spent an hour working on this, and I figured out what was wrong with my code, thing is my code up there works great for getting the access token, storing in the database and then using it, when it comes to the refresh token, it actually WORKS for the first call, then everything breaks.. the reason is, (after reading the infusionsoft api docs) turns out when you refresh the token, you get both new accesstoken and a new refresh token. it was failing in the second call because I'm using the old refresh token... only thing I had to change is after refreshing the token (btw it automatically sets the token in the class when you do $this->refreshAccessToken();), so all i had to do, is update the database and store that new token object in there, and it never fails afterwards, I tested it by manually refreshing... Here is my code:

// If the serialized token is available in the session storage, we tell the SDK
// to use that token for subsequent requests.
if (isset($_SESSION['token'])) {
    $this->setToken(unserialize($_SESSION['token']));
} else {
    $stmt = DatabaseObject::$pdo->query("SELECT infusionsoft_access_token FROM constants");
    $token = unserialize($stmt->fetch()['infusionsoft_access_token']);
    if (!empty($token)) {
        $_SESSION['token'] = $token;
        $this->setToken($token);
    }
}

// If we are returning from Infusionsoft we need to exchange the code for an
// access token.
if (isset($_GET['code']) && !$this->getToken()) {
    $token = serialize($this->requestAccessToken($_GET['code']));
    $_SESSION['token'] = $token;
    $stmt = DatabaseObject::$pdo->prepare("UPDATE constants SET infusionsoft_access_token = :token");
    $stmt->execute(["token" => $token]);
}

 if (isset($_SESSION['token']) && $this->isTokenExpired()) {
    $this->refreshAccessToken();
    $token = serialize($this->getToken());
    $_SESSION['token'] = $token;
    $stmt = DatabaseObject::$pdo->prepare("UPDATE constants SET infusionsoft_access_token = :token");
    $stmt->execute(["token" => $token]);
}

if ($this->getToken()) {
    // Save the serialized token to the current session for subsequent requests
    $_SESSION['token'] = serialize($this->getToken());

    // MAKE INFUSIONSOFT REQUEST
} else {
    echo '<a href="' . $this->getAuthorizationUrl() . '">Click here to authorize</a>';
}

[ribeiroeder]

Hello @yamyoume

Your code seems to me the closest to the ideal of connecting and updating tokens.

Based on it, I'm trying to make it work using a PDO connection, but I'm getting something wrong, because even though I don't get any errors, nothing is written to the SQL. If you could share your "DatabaseObject" function it would be very useful to me.

Below my code:

// Host
function PDO_Connect() {
    $user = 'site_token';
    $pass = 'mypass';
    $pdo = new PDO('mysql:host=localhost;dbname=site_token', $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
    return $pdo;
}

$pdo = PDO_Connect();

// If the serialized token is available in the session storage, we tell the SDK
// to use that token for subsequent requests.
if (isset($_SESSION['token'])) {
    $infusionsoft->setToken(unserialize($_SESSION['token']));
} else {
    $stmt = $pdo->query("SELECT infusionsoft_access_token FROM constants");
    $token = unserialize($stmt->fetch()['infusionsoft_access_token']);
    if (!empty($token)) {
        $_SESSION['token'] = $token;
        $infusionsoft->setToken($token);
    }
}

// If we are returning from Infusionsoft we need to exchange the code for an access token.
if (isset($_GET['code']) && !$infusionsoft->getToken()) {
    $token = serialize($infusionsoft->requestAccessToken($_GET['code']));
    $_SESSION['token'] = $token;
    $stmt = $pdo->prepare("UPDATE constants SET infusionsoft_access_token = :token");
    $stmt->execute(["token" => $token]);
}

 if (isset($_SESSION['token']) && $infusionsoft->isTokenExpired()) {
    $infusionsoft->refreshAccessToken();
    $token = serialize($infusionsoft->getToken());
    $_SESSION['token'] = $token;
    $stmt = $pdo->prepare("UPDATE constants SET infusionsoft_access_token = :token");
    $stmt->execute(["token" => $token]);
}

if ($infusionsoft->getToken()) {
    // Save the serialized token to the current session for subsequent requests
    $_SESSION['token'] = serialize($infusionsoft->getToken());

    // MAKE INFUSIONSOFT REQUEST
} else {
    echo '<a href="' . $infusionsoft->getAuthorizationUrl() . '">Click here to authorize</a>';
}

[yamyoume]

hi there, I don't think there is anything special in my DatabaseObject class, in fact I don't even use most of the functions in it, I mainly use it to access my $pdo var

here is databaseobject.class.php and also there's another file that does the connection:

<?php 

class DatabaseObject {
    static public $pdo;
    static protected $table_name = '';
    static protected $db_columns = [];
    static protected $where = '';
    static protected $exe = [];
    // public $errors = [];

    static public function set_database($pdo) {
        self::$pdo = $pdo;
    }

    static protected function build_sql() {
        $sql = "SELECT ";
        $i = 1;
        foreach (static::$db_columns as $key => $value) {
            $table_alias = explode('.', $value)[0];
            $column_name = explode('.', $value)[1];
            $column_alias = isset(explode('.', $value)[2]) ? explode('.', $value)[2] : '';
            $sql .= $table_alias.'.'.$column_name;
            if (!empty($column_alias)) {
                $sql .= ' "'.$column_alias.'" ';
            }
            if ($i < count(static::$db_columns)) {
                $sql .= ', ';
            }
            $i++;
        }
        $sql .= " FROM ".static::$table_name;
        if (!empty(static::$where)) {
            $sql .= ' WHERE ';
            $i = 1;
            foreach (static::$where as $key => $value) {
                static::$exe[explode('.', $key)[1]] = $value;
                $sql .= $key." = :".explode('.', $key)[1];
                if ($i < count(static::$where)) {
                    $sql .= ' AND ';
                }
                $i++;
            }
        }
        return $sql;
    }

    static public function find_by_sql($sql, $exe = []) {
        confirm_stmt($stmt = self::$pdo->prepare($sql));
        $stmt->execute($exe);
        $set = unser($stmt->fetchAll());

        $object_array = [];
        foreach ($set as $key => $record) {
            $object_array[] = static::instantiate($record);
        }
        return $object_array;

        // // results into objects
        // $object_array = [];
        // while ($record = $result->fetch_assoc()) {
        //  $object_array[] = static::instantiate($record);
        // }

        // $result->free();

        // return $object_array;
    }

    static public function find_by_id($id) {
        $sql = "SELECT * FROM ".static::$table_name." WHERE id = :id";
        $object_array = static::find_by_sql($sql, ["id" => $id]);
        if (!empty($object_array)) {
            return array_shift($object_array);
        } else {
            return false;
        }
    }

    static public function find_all() {
        $sql = static::build_sql();
        return static::find_by_sql($sql, static::$exe);
    }

    static public function count_all() {;
        $sql = "SELECT COUNT(*) count FROM ".static::$table_name;
        $stmt =  self::$pdo->query($sql);
        return $stmt->fetch()['count'];

        // $row = $result_set->fetch_array();
        // return array_shift($row);
    }

    static protected function instantiate($record) {
        $object = new static;
        // Could manually assign values to properties
        // but automatically assignment is easier and re-useable
        foreach ($record as $property => $value) {
            if (property_exists($object, $property)) {
                $object->$property = $value;
            }
        }
        return $object;
    } 

    protected function validate() {
        $this->errors = [];

        // Add custom validations 

        return $this->errors;
    }

    protected function sanitized_attributes() {
        $sanitized = [];
        foreach ($this->attributes() as $key => $value) {
            $sanitized[$key] = self::$database->escape_string($value);
        }
        return $sanitized;
    }

}

this is database_functions.php

<?php 

function db_connect() {
    $dsn = 'mysql:host='. DB_SERVER .';dbname='. DB_NAME.';charset=utf8';
    $pdo = new PDO($dsn, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->query("SET sql_mode='PIPES_AS_CONCAT'");
    return $pdo;
}

function confirm_db_connect($connection) {
    if ($connection->connect_errno) {
        $msg = "Database connection failed: ";
        $msg .= $connection->connect_error;
        $msg .= " (".$connection->connect_errno. ")";
        exit($msg);
    }
}

function db_disconnect($connection) {
    if (isset($connection)) {
        $connection->close();
    }
}

also in my initialize.php here is what I do to start the connection:

$database = db_connect();

DatabaseObject::set_database($database);

[ribeiroeder]

I'll try it this way, thanks so much for sharing!

In addition to this connection, I assume you have set up a cron job to access the renewal script frequently every hour?

Thought of doing this using cPanel's Cron Job