Get your current PayPal balance via API and show on your Metabase dashboard (Plugin Developer Stats Part 5)

In part 1 to part 4 we’ve set up Metabase fetched all relevant data from WordPress.org and added this data as charts and tables to our dashboard. If you missed one of these steps here are the links:

In this part we leave wordpress.org and create our first dashboard entry for premium plugin developers. We use EasyDigitalDownloads for our plugin store and we offer two payment methods, Credit card via Stripe and PayPal.

Stripe automatically sends the money to our bank account every day but PayPal payments remain on our PayPal account until we manually transfer it, so we’d like to show our current balance. If it reaches a certain amount we log in and transfer the money.

Install PayPal PHP Merchant SDK

To simply access the PayPal API we use the official PHP libraries. If you already have composer installed just navigate to the scripts folder and run:

composer install paypal/merchant-sdk-php

otherwise you have to install composer first which depends on your server environment. On debian based systems run:

sudo apt install composer

afterwards you should see a folder vendor and your folder structure should look like this.

Create PayPal API Keys

Log in to you PayPal account and navigate to Tools -> All Tools then choose Integrate PayPal on the left and click on the card API Access.

Scroll down to NVP/SOAP API integration (Classic) and manage your credentials there.

We are going to need the values

API username
API password
signature

PHP script to get PayPal balance and save to database

Using out SQLite database and the according db.php file we already used in previous step you can just copy and paste the following script and replace your API credentials. Most of this code is not my own but I currently can’t find the source where I copied it two years ago. If someone finds out please let me know to credit the author.

<?php
require( 'vendor/autoload.php');
require( 'db.php' );
$db = new CQDashboardDB();
if($db) {
    maybe_create_database_table( $db );
    
    save_balance_to_db( 
            'my-account-name',  // this is just for internal usage in case you are using multiple accounts
            "payment_apiX.xxxxxx.com",  // api user name
            "XXXXXXXXXXXX", // api password
            "XXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX", // signature
            $db
        );    
    $db->close();
}


function save_balance_to_db( $account, $user, $pwd, $signature, $db ){
    $API_Endpoint = "https://api-3t.paypal.com/nvp";
    $version = "124";
    $resArray = CallGetBalance ( $API_Endpoint, $version, $user, $pwd, $signature );
    $ack = strtoupper ( $resArray ["ACK"] );

    if ($ack == "SUCCESS") {
        for( $i = 0; $i<10; $i++ ){
            if( array_key_exists( 'L_AMT' . $i, $resArray ) && array_key_exists( 'L_CURRENCYCODE' . $i, $resArray ) ){
                $balance = urldecode ( $resArray[ 'L_AMT' . $i ] );
                $currency = urldecode ( $resArray[ 'L_CURRENCYCODE' . $i ] );

                $sql = "
                    INSERT INTO paypal( ACCOUNT, CURRENCY, BALANCE )
                    VALUES( '$account', '$currency', $balance );
                ";
                $db->exec($sql);
            }
        }
    }
}


function maybe_create_database_table( $db ){
    $sql = "
            CREATE TABLE IF NOT EXISTS paypal
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            ACCOUNT     CHAR(10)    NOT NULL,
            CURRENCY    CHAR(3),
            BALANCE     REAL,
            TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP);
        ";
    $db->exec($sql);
}

function CallGetBalance($API_Endpoint, $version, $user, $pwd, $signature) {
    // setting the curl parameters.
    $ch = curl_init ();
    curl_setopt ( $ch, CURLOPT_URL, $API_Endpoint );
    curl_setopt ( $ch, CURLOPT_VERBOSE, 1 );
    curl_setopt ( $ch, CURLOPT_SSL_VERIFYPEER, FALSE );
    curl_setopt ( $ch, CURLOPT_SSL_VERIFYHOST, FALSE );
    curl_setopt ( $ch, CURLOPT_RETURNTRANSFER, 1 );
    curl_setopt ( $ch, CURLOPT_POST, 1 );

    // NVPRequest for submitting to server
    $nvpreq = "METHOD=GetBalance" . "&RETURNALLCURRENCIES=1" . "&VERSION=" . $version . "&PWD=" . $pwd . "&USER=" . $user . "&SIGNATURE=" . $signature;
    curl_setopt ( $ch, CURLOPT_POSTFIELDS, $nvpreq );
    $response = curl_exec ( $ch );

    $nvpResArray = deformatNVP ( $response );

    curl_close ( $ch );

    return $nvpResArray;
}

/*
 * This function will take NVPString and convert it to an Associative Array and it will decode the response. It is usefull to search for a particular key and displaying arrays. @nvpstr is NVPString. @nvpArray is Associative Array.
 */
function deformatNVP($nvpstr) {
    $intial = 0;
    $nvpArray = array ();

    while ( strlen ( $nvpstr ) ) {
        // postion of Key
        $keypos = strpos ( $nvpstr, '=' );
        // position of value
        $valuepos = strpos ( $nvpstr, '&' ) ? strpos ( $nvpstr, '&' ) : strlen ( $nvpstr );

        /* getting the Key and Value values and storing in a Associative Array */
        $keyval = substr ( $nvpstr, $intial, $keypos );
        $valval = substr ( $nvpstr, $keypos + 1, $valuepos - $keypos - 1 );
        // decoding the respose
        $nvpArray [urldecode ( $keyval )] = urldecode ( $valval );
        $nvpstr = substr ( $nvpstr, $valuepos + 1, strlen ( $nvpstr ) );
    }
    return $nvpArray;
}

Use multiple accounts

If you want to monitor multiple PayPal account balances just duplicate the call to save_balance_to_db. Each account is identified by the first parameter which is just an internal name for you. Multiple currencies are already included.

save_balance_to_db( 
            'my-first-account-name',  
            "payment_apiX.xxxxxx.com",  
            "XXXXXXXXXXXX", 
            "XXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX", 
            $db
        );   

save_balance_to_db( 
            'my-second-account-name',  
            "payment_apiX.xxxxxx.com",  
            "XXXXXXXXXXXX", 
            "XXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX", 
            $db
        );

Show PayPal Balance in Metabase

We’ve extended our existing SQLite data source so we go to the Metabase Admin -> Databases -> WordPress.org Plugin Data and click the button Sync database schema now.

Afterwards exit admin, click Ask a question -> Simple Question and choose the new data table.

You should see a data table now. Click Visualization at the bottom of the page and choose Number.

Select the column balance as Field to show and your currency symbol as prefix.

We only want to show the latest value and maybe we also need to filter by account name or currency so click the editor icon at the top right corner and change your config like the screenshot below:

For our use case we save the question for EUR now and duplicate it for USD.

And this is how our dashboard looks for now:

Install PayPal PHP Merchant SDK

Create PayPal API Keys

PHP script to get PayPal balance and save to database

Use multiple accounts

Show PayPal Balance in Metabase

Leave a Reply