WooCommerce installment payments part 2

Posted on June 21, 2016

Posted by Henry Tam

In part 2 of the installment payment series, we will explore how to capture the remaining installments of an installment purchase. The basic steps include setting up a database table to store all future payments related to the installment purchase, writing the installments data to that table, and setting up a cron job to call a script which will check and capture future payments. If you missed part 1 of this series which goes through the coding process of setting up installment payments mostly from the WooCommerce / WordPress side. Click here to view it.

Installment table in action

Installments table in action

Installment billing capture table & code

Here is the DDL script to create the installments table. This table is used to capture the remaining installments for each order.

CREATE TABLE IF NOT EXISTS prefix_installments (
  _id                    bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  _order_id              bigint(20) UNSIGNED NOT NULL,
  _order_item_id         bigint(20) UNSIGNED NOT NULL,
  _user_id               bigint(20) UNSIGNED NOT NULL,
  _product_id            bigint(20) UNSIGNED NOT NULL,
  _created_date          timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  _updated_by            varchar(100) NOT NULL,
  _payment_due_date      timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  _payment_capture_date  timestamp  NULL,
  _payment_amt           decimal(19,2) DEFAULT 0.00 NOT NULL,
  _payment_num           smallint NOT NULL,
  _payment_paid_full     tinyint(1) DEFAULT 0 NOT NULL,
  _payment_inst_agree    tinyint(1) DEFAULT 1 NOT NULL,
  _customer_id           varchar(255) COLLATE utf8_bin NOT NULL,
  _gateway_name          varchar(255) NOT NULL,
  _status                varchar(25),
  PRIMARY KEY  (_id),
  UNIQUE KEY order_idx (_order_id, _order_item_id, _payment_num)

We will add the code to record future installments in the woocommerce_order_status_completed. This action is called by WooCommerce when an order’s status changes to ‘Completed’. You may need to explore other hooks if your store process does not set orders to completed after checkout.

add_action('woocommerce_order_status_completed', 'ttp_order_completed', 10, 1);
 * After order marked as completed. Record all future installments in the
 * database.
function ttp_order_completed($order_id) {
    global $wpdb;
    $order = new WC_Order($order_id);

    $order_items = $order->get_items();
    foreach ($order_items as $key => $value) {
        $order_item_meta = $order->get_item_meta($key);

        if (!empty($order_item_meta['_installment_purchase'][0]) &&
            'yes' == $order_item_meta['_installment_purchase'][0]) {

            $product_id        = $order_item_meta['_product_id'][0];
            $quantity          = $order_item_meta['_qty'][0];
            $installments      = unserialize($order_item_meta['_installments'][0]);
            $payment_dates     = array_keys($installments);
            $payment_amts      = array_values($installments);
            $payment_due_date  = new DateTime(NULL, new DateTimeZone("UTC"));

            for ($i = 1; $i <= count($payment_dates); $i++) {
                $due_date = DateTime::createFromFormat('m/d/y', $payment_dates[$i - 1], new DateTimeZone("UTC"));
                $payment_due_date->setDate($due_date->format('Y'), $due_date->format('m'), $due_date->format('d'));
                $mysql_format = 'Y-m-d H:i:s';
                $data = array($order_id,
                              current_time('mysql', 1),
                              current_time('mysql', 1),
                              ($i == 1 ? current_time('mysql', 1) : '0000-00-00 00:00:00'),
                              $quantity * $payment_amts[$i - 1],
                              ($i == 1 ? 1 : 0),
                              ($i == 1 ? 'paid' : ''));

                $result = $wpdb->query($wpdb->prepare("
                            INSERT INTO {$wpdb->prefix}installments
                            (_order_id, _order_item_id, _user_id, _product_id, _updated_date, _created_date, _updated_by,
                             _payment_due_date, _payment_capture_date, _payment_amt, _payment_num, _payment_paid_full,
                             _payment_inst_agree, _customer_id, _gateway_name, _status)
                            VALUES (%d, %d, %d, %d, %s, %s, %s, %s, %s, %f, %d, %d, %d, %s, %s, %s)", $data));

Setting up a REST endpoint for recurring billing capture

Next, we need to setup a RESTful service with logic to capture future installment payments. Install the WP REST API v2 if you have not already done so. This plugin adds REST API to your WordPress system. To register the url /ttp/installment_billing as the REST endpoint, use this filter and code.

add_filter('rest_api_init', 'ttp_register_routes', 10, 1);
 * Register the url /ttp/installment_billing as the REST endpoint
 * to run the periodic billing processing.
 * The rest URL for the configuration below will be,
 * https:/www.example.com/wp-json/ttp/installment_billing
function ttp_register_routes($routes) {
    register_rest_route('ttp', 'installment_billing', array(
        'callback' => 'ttp_installment_billing',
        'methods' => WP_REST_Server::READABLE,
        'args' => array()

The callback function ttp_installment_billing is the entry point to processing any installment payments that needs to be captured. Here is the code with the processing logic. The periodic billing will check the installments database table to see if there is an installment payment that needs to be captured. If so, then process it. Each payment gateway has their own web service API for capturing payments. You will need to implement this by referring to your payment gateway’s API documentation.

The class TTPInstallment is a DTO used to encapsulate the data retrieved from the database.

 * Process installment payments if available.
function ttp_installment_billing(WP_REST_Request $request) {
    global $wpdb;

    $output = '';

    $rows = $wpdb->get_results($wpdb->prepare("
          SELECT i._id,
          FROM {$wpdb->prefix}installments i,
               {$wpdb->prefix}users u
          WHERE  i._user_id = u.ID
             and i._payment_paid_full = %d
             and i._status in ('rebill', '')
             and i._payment_due_date <= NOW()", 0));

    foreach ($rows as $rec) {
        $installment              = new TTPInstallment();
        $installment->id          = $rec->_id;
        $installment->orderId     = $rec->_order_id;
        $installment->orderItemId = $rec->_order_item_id;
        $installment->paymentNum  = $rec->_payment_num;
        $installment->paymentAmt  = $rec->_payment_amt;
        $installment->customerId  = $rec->_customer_id;
        $installment->gatewayName = $rec->_gateway_name;
        $installment->userEmail   = $rec->user_email;
        $installment->displayName = $rec->display_name;
        $installment->status      = $rec->_status;

        /* Call the gateway processor
         * Here, you will need to implement your Payment Gateway's 
         * payment processing to capture the amount in,
         * $installment->paymentAmt
         * The result of the operation will be stored in the 
         * $charge variable. Assuming processed successfully below.

        $charge = true; 

        if (!empty($charge)) {
            /* Update db record */

            $data = array(
                'TTPAdmin Billing',
                current_time('mysql', 1),

            $result = $wpdb->query($wpdb->prepare("
                UPDATE {$wpdb->prefix}installments
                SET   _payment_paid_full = %d,
                      _updated_by = %s,
                      _payment_capture_date = %s,
                      _status = %s
                WHERE _id = %d", $data));

            if (false === $result || 0 === $result) {
                /* Optionally log technical error */
        } else {

            /* Mark installment as failed */
            $this->setInstallmentStatusInDB($installment, 'failed');

            /* Optionally, log and inform the admin */

    $response = new WP_REST_Response(array(
                    'result' => 'Done'
    return $response;

Code for the class TTPInstallment.php. This is a wrapper class that transfers the data from the sql query into a php object for convenience.

class TTPInstallment {
    public $id;
    public $orderId;
    public $orderItemId;
    public $userId;
    public $productId;
    public $updatedDate;
    public $createdDate;
    public $updatedBy;
    public $paymentDueDate;
    public $paymentCaptureDate;
    public $paymentAmt;
    public $paymentNum;
    public $paymentPaidFull;
    public $paymentInstAgree;
    public $customerId;
    public $gatewayName;
    public $status;

    public $userEmail;
    public $displayName;

This is the helper function to set update the status of an installment in the database table.

 * Helper function to update status of an installment.
function setInstallmentStatusInDB($installment, $status) {
    global $wpdb;

    $tableName = $wpdb->prefix . 'installments';

    array('_status' => $status),
    array('_id' => $installment->id),

Cron job

The final step is to create a cron job to automatically trigger the periodic billing. Since the WordPress cron system is not reliable, we will fallback to the operating system cron. You can read more about the limitations of the WordPress cron system here

Here is the configuration to add to your crontab. It will run your script every 30 minutes. You can adjust according to your application needs.

*/30 * * * * /path/to/your/script/run_wp_cron.pl

Here is my Perl script use to call the automatic billing.


`wget -O /dev/null https://www.example.com/wp-cron.php?doing_wp_cron > /dev/null 2>&1`;

This concludes the WooCommerce installment payment series. This guide will help you code a dynamic installment payment system for the WordPress platform. If you have any questions or comments, please add those below. Thank you.


  1. How would you deal with failed recurring billing captures? What if the recurring billing fails before full payment is received? You should have checks in place and notify all interested parties. In addition, create a page which allows the user to update their payment method to continue re-billing.
  2. As of this writing. I am unaware of any PayPal APIs for charging a customer due to the way PayPal works.
  3. ttp_order_completed, Need to check that installments were recorded already before adding using this action. E.g., if an order went from processing to completed twice, you do not want multiple installments for the same order.
  4. Optionally, you would want to lock down the REST url and make it only available to your internal network.
WooCommerce installment payments part 2 was last modified: June 21st, 2016 by Henry Tam

Leave a Reply

Your email address will not be published. Required fields are marked *