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.
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, _updated_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, _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) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
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,
$key,
get_current_user_id(),
$product_id,
current_time('mysql', 1),
current_time('mysql', 1),
"Admin",
$payment_due_date->format($mysql_format),
($i == 1 ? current_time('mysql', 1) : '0000-00-00 00:00:00'),
$quantity * $payment_amts[$i - 1],
$i,
($i == 1 ? 1 : 0),
1,
'',
'',
($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,
i._order_id,
i._order_item_id,
i._payment_num,
i._payment_amt,
i._customer_id,
i._gateway_name,
i._status,
u.user_email,
u.display_name
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(
1,
'TTPAdmin Billing',
current_time('mysql', 1),
'paid',
$rec->_id
);
$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';
$wpdb->update($tableName,
array('_status' => $status),
array('_id' => $installment->id),
array('%s'),
array('%d'));
}
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.
#!/usr/bin/perl `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.
Notes
- 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.
- As of this writing. I am unaware of any PayPal APIs for charging a customer due to the way PayPal works.
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.- Optionally, you would want to lock down the REST url and make it only available to your internal network.