reconcile direct bank transfer for low cost online payments for schools

I have implemented a low cost e-commerce payment system for educational institutions in India using Virtual Accounts. Educational institutions need to be able to collect payments from users for tuition and other such services. Typically, payment gateways charge a hefty commission for payment by card, of the order of 2%. A payment by a direct bank transfer (using NEFT/RTGS/IMPS) costs next to nothing but is offline and reconciliation of payment to order (and payee) is not automatic. However, using the Virtual Account features being offered by many payment gateways in India, it is possible to have automatic reconciliation, although the direct bank transfer payment is made offline. I describe a solution below using the Indian Payment gateway Vendor, Cashfree.

The e-commerce happens on an intranet site of a multi-site belonging to the School using the WordPress platform. The payment site has an SSO (Single-Sign-On) from Moodle LMS, using a customized LTI plugin, as explained in this post. The cost of collecting an online payment from a user via the direct bank transfer is only Rs.17 as compared to 2% of transaction value using Card.

In addition, on the Moodle side, the popular and versatile plugin called Configurable Reports has been further customized in order to create and close Virtual Accounts for students on Cashfree using the Cashfree Autocollect API. The synchronization of Cashfree accounts with student active status is managed by this plugin.

Since the school is located in India,, an Indian e-commerce vendor was chosen to implement the gateway. This vendor was chosen since it has:

  • Reasonable rates for Card and Net Banking options (2.065% including 18% GST) with no setup and maintenance fees
  • Ability to create unlimited number of virtual accounts (VAs) that can be uniquely assigned to payers. This facilitates payment using Direct Bank Transfer to these VAs using NEFT/RTGS/IMPS which are all inexpensive forms of electronic payment compared to card payments (Rs.17 max per transaction, independent of item value, charged to payee). Most banks charge a very nominal or no fee to the user for NEFT/RTGS/IMPS.
  • A reasonably comprehensive plugin for WooCommerce
  • A reasonably comprehensive API especially for creating and querying Virtual Accounts. The most useful feature of the API is the ability to relate the virtual account ID to say the user’s moodle ID. Thus knowing the Moodle ID, I can uniquely determine the VA ID for this user since they can be one and the same. This, along with the lower cost per transaction, makes this vendor standout compared with Razorpay and Instamojo.
  • One disadvantage of Cashfree is that their virtual account numbers can have alphanumerics instead of just numerics. Some Apps just as Google Pay (India) do not accept alphabets in account number entry.

There are at least 2 other vendors that offer Virtual Accounts: Razorpay and Instamojo. They have the following deficiencies:

Razorpay: The main issue with this vendor is that there is no facility to derive the ID number of a user’s Virtual Account (VA) from any other user information such as Moodle user id, etc., using the API. You can get that information using the search facility from the vendor website’s dashboard but that is not useful for integration with API. Knowing the virtual account ID from a webhook, I was unable to determine the user or order related to it. Also the maximum cost per transaction is Rs.59 including GST.

Instamojo: The main issue for us with this vendor is that a new VA is created for every transaction for any user. So a user, over course of time, needs to keep re-creating their payee, since the payee details keep changing with every transaction. This is quite painful for repeated payments. So this vendor was dropped.

The advantages of our implementation are:

  • It provides for fully automated tracking and reconciliation of all payments including payments of Direct Bank Transfer Type which are made offline. (Traditionally, this type of payment is the least expensive but very difficult to track and reconcile. Tracking and reconciliation was done by numerous back and forth emails between payers and school accounts department, keeping track of bank references of transactions made, etc. Receipts had to be generated manually and sent by email. Data had to be maintained in spreadsheets manually. This took up-to 50% of an admin’s time.)
  • With our system using Virtual Accounts, we are able to track payments and associate payments to WooCommerce shop orders by payee, time, and amount involved and do an automatic reconciliation.
  • Reports can be automatically generated, data can be exported and archived conveniently, etc.
  • Upon satisfactory payments made the user’s Moodle profile field “fees” is appropriately updated
  • Easy check on paid status of all students that can be easily generated in another report in Configurable Reports.
  • Tremendous flexibility since we can customize both Moodle Side as well as WordPress/WooCommerce side of things.

There is the inevitable drawback to this flexibility: Maintenance of custom code and tracking all the plugins to make sure nothing breaks during updates, etc.

The Process in detail from a user viewpoint

The student’s parent logs into Moodle in the standard way using the student’s account.

She then SSO’s into the payment site through an LTI activity which is via a Moodle Assignment Activity. This makes it easy to setup access control and messages for due dates using usual Moodle features such as Groups, dates, etc. The LTI activity sets up the user with appropriate user meta for the virtual account if there are multiple payment entities based on the LTI activity name in Moodle. The LTI activity name should be same as the desired payment sub-site. For example, if the intended payment wordpress site is the LTI activity must be named hset-payments.

She is shown products appropriate for her child based on product filters and user meta so she does not see other products. So for example, grade4 students will only see grade4 products and so on. Of course, since this site is for logged-in users only, outside visitors will not even be able to enter the site.

At checkout, our user is given instructions for payment into her uniquely assigned virtual account including details of account itself. This is supplemented by email having all required information.

Once the parent makes a payment to the the virtual account assigned to her, using her bank’s net banking App, a webhook is sent by Cashfree that helps in reconciling the payment with the appropriate order. As a backup, should the webhook not work, a link in the admin menu is available for the shop manager to reconcile payments vs orders on demand.

Standard Woocommerce emails are setup for all phases of the process including generation of receipt etc.

Implementation Details

Moodle: We create several user profile fields for this purpose:

  • studentcat (A text field that holds the category of student – For example: general, children of staff, scholarship, installment, rte,
  • virtualaccounts (A text field that holds the JSON encoded string that holds the virtual account information assigned to the student for all payment entities)
  • payments (A text area that holds the JSON encoded string containing all payments made this student to all Virtual Accounts assigned to the student)
  • fees ( A text field that holds information about whether the student has made all necessary payments for the year satisfactorily or not)

Management of Virtual Accounts (using customized plugin Configurable_Reports in Moodle)

We implemented the Virtual Account management on Moodle by customizing the highly useful plugin called configurable_reports. Custom SQL generates user data for selected cohort. This data is used to synchronize Virtual Accounts using Cashfree API. This is implemented by modifying export.php of the CSV export inside of configurable_reports. For each user in the table generated by the SQL we check to see if a Virtual Account exists at Cashfree. If so we get the account details. If not we create a new Virtual Account and get its details. The account details are obtained for all payment entities (upto a total of 2) and JSON encoded and stored in the user profile field called virtualaccounts. We can simulate the SYNC before doing the actual synchronization as is standard practice in these sort of situations.

E-commerce sites on WordPress:

We implement the e-commerce payment site as one of the sites of a multi-site on WordPress. Each payment entity is a separate site on this multi-site network.

WooCommerce plugin is used to implement the store.

PODS ADMIN is a very verstaile plugin used to create new meta for User, Product, and Order. We create the following:

  • Plain text meta field for Product Post Type called surcharge .
  • Plain text Meta fields for User: va_id, beneficiary_name, account_number, sritoni_username, va_ifsc_code, sritoni_student_category, grade_or_class.
  • Plain text Meta fields for Shop Order: va_payment_id, amount_paid_by_va_payment, bank_reference, payment_notes_by_customer, va_id.

Booster for WooCommerce is another very powerful plugin. We use it to implement customization of WooCoomerce for things such as product visibility by user role, charges specific to gateway, custom invoice generation and mailing, etc.)

SSO from Moodle LMS to Wprdpress payment site:

We customized a plugin called WordPress-LTI-1.2 to implement the following:

  • SSO from Moodle to WordPress (This feature allows us to seamlessly integrate the entire WordPress ecosystem with Moodle. Enough cannot be said about the advantages that such an arrangement brings)
  • The calling activity name is used to direct to the correct site in the multi-site network.
  • The user and virtual account information is retrieved from Moodle using Moodle REST API and the user meta in WordPress is appropriately updated.
  • A check is made to see if valid VA exists for intended site by examining data contained in virtualaccounts field of student’s profile. If not, a new VA is created for this user for this site on Cashfree using Cashfree’s API. This new VA information is used to also update the virtualaccounts field of student’s profile in Moodle.
  • The user’s payments are extracted from the Moodle user profile field fees. Fees with not paid status are extracted. This data is used to show the payments required to be made by the user.

Order and Payment reconciliation:

We wrote a custom plugin called SriToniCashfreeAutoCollect to implement the following:

  • Settings for Cashfree API, other information such as permissible student categories, groups, etc.
  • Implement a BACS payment gateway that displays virtual account information and instructions for bank transfer. This data is extracted from user meta.
  • Webhook processing that is sent by Cashfree after any payment is collected
  • View special columns in the Orders page
  • View payments and reconciliation orders for any virtual account by clicking a custom column in the orders page using Cashfree API.
  • Reconcile payments and Orders on demand if webhook is not working.
  • Upon order completion, update the payments user profile field as well as the fees user profile field in Moodle with the new payment using Moodle REST API.

WP Support Plus WooCommerce extension (For any help with a product, a support ticket can be raised. In the ticket, the user can include product and order from their order history from a drop down) to be included in their ticket for convenience)

Posted in Configurable Reports, customization, e-commerce, Intranet, moodle, plugin, WordPress.