• Increase
  • Decrease
  • Normal

Current Size: 75%

No public posts in this group. You must register or login and become a member in order to post messages, and view any private posts.


Retired Teachers of Ontario
Since 1968, Retired Teachers of Ontario - RTO/ERO has provided excellent retirement programs and services for teachers, school and board administrators, educational support staff and college and university faculty. RTO/ERO is a voluntary membership organization with a strong and active voice of over 64,000 paid members. RTO/ERO is the only organization with representation from elementary, secondary, public, Catholic and francophone schools.

RTO engaged BPO Canada Global Services Corporation. BPO Canada and Gloscon Solutions Pvt Limited, India, jointly executed this project. BPO Canada and Gloscon have executed over 130 Drupal projects during the last five years.
This case study will provide you with a framework for understanding the challenges and constraints of the original website and new custom solutions using Drupal 6. Learn how we approached a number of tough challenges to reshape member experiences and provide an elegant open source content management platform. The collaboration succeeded based on fluid and transparent communications.
Quick View of the New Features
1.     Splash Page with Multilingual Option
2.     Member Center which is accessible to the paid members
3.     Online Registration with Beanstream Payment Gateway
4.     External data source for Drupal user authentication and login (remote authentication with MS SQL Server)
5.     Interactive District Map
6.     Job Postings and Volunteer Opportunities Board
7.     Events and Calendar Sections
8.     Collapsible Breadcrumbs
9.     Photo Gallery
Some of the requirements we will cover in the case study include: A) Multiple languages, B) Custom map, C) MS-SQL Server and Drupal Integration, D) Drupal and third party membership application integration - MS SQL server authentication, E) Online registration with the Benstream Payment Gateway and Ubercart.
A. Multilingual
RTO/ERO serves content in English and French languages. To accomplish the multilingual approach, we used combination of core and contributed modules like locale and i18n, languageicons, translation404, etc. One of the project’s requirements was to provide an interface whereby the user can select a language during the registration process. The language selection is also provided in the admin interface. This was accomplished by tweaking contributed reglang module.
For ease of content localization and user interface we used following contributed modules:
  • Block translation
  • CCK translation
  • Content type translation
  • Internationalization
  • Language Icons
  • Menu translation
  • String translation
B. District Map - Integration with Openlayers
Another requirement was to populate the Custom Map with a combination of Popup and rich UI to show RTO/ERO’s district based contact information. To achieve this, we looked at options such as jquery maps, html maps and Openlayers, etc. The Openlayers solution came close to meeting the specific requirements of RTO/ERO. Therefore we recommended it.
In the initial stage we used combinations of various modules available on the site, namely http://drupal.org/project/openlayers and other dependent modules. During the implementation, we came across a few challenges such as themeing of each element and performance issues. We evaluated whether we should building a new module or modify the Openlayers module. We surmised that a custom module would take minimal effort.
Solution: We wrote a custom module that defined custom content type and unique node.tpl.php file. All required JS and CSS files were included in the module and triggered only on unique node.tpl.php file. For theming, we have used Image layer method available on OpenLayers. (http://openlayers.org/dev/examples/image-layer.html). We used different API functions and plotted all districts as polygon on the map. You can view this excellent solution at http://www.ero-rto.org/district-map-1
C. MS-SQL Server and Drupal Integration
Our client, RTO/ERO, already had an existing membership application that was using MS-SQL server database. The requirement was to facilitate a Drupal integration with this application. We needed to authenticate users against SQL Server membership database. Importing Users data from SQL Server into MySQL DB (Drupal) was not an option because of security reasons. 
One possibility was mssql_connect() function in PHP, which we proposed for MS SQL Server connection establishment and querying against the database. The client recommended that we follow standards of existing code-base that uses FreeTDS which is used by other applications to interface with SQL Server based membership database. The client wanted us to maintain consistency with this approach. Hence, we did a proof of concept and then decided to use FreeTDS (freetds.org) library's tsql command for communications with MS SQL Server from Drupal.

Steps followed to configure freetds and iODBC:

  1. Download freetds and iodbc from :
  2. First extract and configure iODBC and then FreeTDS:
    cd $HOME/local
    tar -xvf libiodbc-3.52.7
    tar -xvf freetds-0.82.tar.gz
    cd libiodbc-3.52.7
    make make install
    cd freetds-0.82
    ./configure -prefix=$HOME/local/freetds—with-iodbc=$HOME/local/libiodbc—with-tdsver=8.0
    make install 
  3. Find where tsql is created:
    find $HOME -name “tsql”
    result should be:
  4. copy all file from /home/gloscon/local/freetds/bin to /home/gloscon/bin
    cp /home/gloscon/local/freetds/bin/* to /home/gloscon/bin
  5. To validate the connection, create sqlquery.sql file and try to connect MSSQL and use tsql from PHP:
    sqlquery.sql looks like
    use gloscon_db
    select * from user_master

    PHP look like:
    exec(”/home/gloscon/bin/tsql -S MSSQL_Server_IP -U ‘Domain\username’ -P ‘password’ < /home/gloscon/public_html/sqlquery.sql”, $output, $return); echo “Dir returned $return, and output:\n”; var_dump($output);

D. Drupal and third party Membership application integration - MS SQL Server authentication

Our client’s in-house local network's Windows Server (exposed via Static IP) stores membership registration details in MS SQL server database. One of the major requirements was to authenticate and validate the Drupal user (member) with the Database located on MS-SQL Server.
Registration Process - Before a new user registers on the site, there were a few authentication/validation rules to be checked. E.g., validating against duplication of SIN (Social Insurance Number), Spouse SIN, etc., to ensure non-duplication of a same member. While validation was against SIN on MS SQL Server database, there was another requirement not to store the SIN Number in Drupal DB.
Login Process - Users can login to the Drupal Application only after the Membership ID and Password match with MS SQL Server database. The process was uniform to their existing 64000+ members as well as newly registered users. This produced special challenges. We used the freeTDS library to execute queries from command prompt. We had to write every query as a command. We created one function tsql() for the all command prompt requests. Our tsql() function does the same functionality like db_query() function of drupal,

function tsql($query){

    $newsinID1 = "USE gloscon_db";   
    $newsinID6 = $query;
    $newsinID7 = "go";
    $newsinID5 = "exit";
    $newsinID = $newsinID1."\n".$newsinID6 ."\n".$newsinID7."\n".$newsinID5."\n";
    $filename = tempnam('/tmp', '');
    $fp = fopen($filename, 'w');
     fwrite($fp, $newsinID);
     $firsttoe = "/home/gloscon/bin/tsql -H -p 1433 -U visitor -P visitor < $filename";
     exec($firsttoe, $output, $return);
     return $output; 
The interface of the Registration process contains multi-step approach. At the Initial step, all information is collected using Drupal site. In the second step, the user has to complete the payment process for their membership using Beanstream. After successful payment confirmation, application passes data to MS SQL Server and only deletes user data from Drupal application. There are some chances that transaction may fail during the validate or transfer of data to the MS-SQL database. One reason for this may be the loss of connectivity between Drupal and remote MS SQL Server application.
To overcome this we created a temporary queue table to store the transaction information as a record, Transaction records are stored in this table till transaction is not successfully completed. We wrote a one cron job that executes a function on a predefined interval to pass the record with all fields for authentication and validation to ensure that there won’t be duplication of the data.
Once the record is successfully transferred, it will automatically be flushed from Queue Table. Every failed attempt will send an email notification to the Client's web admin. Also we have provided an admin interface that lists all uncompleted transactions. The Admin can also update these transactions to MS-SQL Server Database without waiting for cron job.

Membership Flow - Only when registration process is successfully completed and data transferred to MS SQL Server membership database, user receives mail with their Membership ID and Password as login credential to access Drupal site. We have altered Drupal login process for checking login credentials into MS SQL Server. We verify user's Membership ID and Password against remote MS SQL database and if the user is verified then only we load one generic Drupal user with paid member role. This user, when authenticating against MS-SQL Server database, is granted the paid member role in Drupal site and can then access all content/functionality available to paid-member role.
It is important to note that we are storing user's Membership ID in session after successful login for further communication with MS SQL Server. For a currently logged-in user, we also allow them to edit user's profile and change a password. This is also done directly against MS SQL Server database using tsql(). After this integration, 64000+ users from MS SQL Server Membership database are able to access this website with their Membership ID and password.
There were also specific encryption requirements for this project for which we have written functionality as per client’s requirements:
- To Encrypt and Decrypt the password using htpasswd
- SIN Number encryption and decryption.

E. Online Registration with Benstream Payment Gateway and Ubercart

We have used ubercart, uc_beanstream, uc_node_checkout modules for the Online Membership Process. We were not able to retrieve the Transaction id, so we have made some changes into the uc_beanstream module.

function uc_beanstream_charge($order_id, $amount, $data) {

+ foreach($response_data as $key => $val){
+ $rdata = explode('=',$val);
+ $response_array[$rdata[0]] = urldecode($rdata[1]);
+ }
+ $message_id = $response_array['trnApproved'];
+ $message_text = $response_array['messageText'];
+ $auth_code = $response_array['trnId'];
- $response_code = explode('=', $response_data[0]);
- $response_text = explode('=', $response_data[3]);
- $approval_code = explode('=', $response_data[4]);
- $message_id = $response_code[0];
- $message_text = $response_text[0];
- $auth_code = $approval_code[0];

Thank you and we look forward to your comments.
BPO Canada and Gloscon Solutions, 12/23/10.