Building an Insurance Company Website on WordPress

The Objective:

A local insurance company needed a new website that allowed them to better manage their page content as well as handle the complex operations of a normal insurance company.

The company won’t be named in this post, but the following list of challenges will be outlined:

All right, let’s begin!

WordPress site integration with 3rd party server hosting policyholder data

Insurance companies take data security very important and there are many regulations on how this data needs to be handled. Because of this and a few other logical reasons, I opted to keep the existing policyholder database offshore, and make the two servers communicate.

The offshore server that holds all of the policyholder database info was given a user on the WordPress install server and a script which runs a daily export of the policyholder data we would need for the website, and then transports the data as a CSV via FTP using a public/private key pair.

Now that we have the data, now it needs go into our SQL database, and since it updates daily, we’ll need to create a cron task that runs the following script daily:

<?php
/**
 * Takes a CSV from somewhere on the current server and imports it into the specified database using PDO after truncating the table.
 * by Brandyn L
 * https://the-dev.ninja/
 */
// configure your settings
$table_name = 'sql_table_name';
$path_to_file = '/path_to_file.csv';
$delimiter = ',';
// You can probably store the following as environemental variables
$db_info = 'mysql:host=127.0.0.1;dbname=database-name;';
$db_user = 'user';
$db_pass ='pass';
try{
    $db = new PDO($db_info, $db_user, $db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
} catch (exception $e) {
    die();
    // echo $e->getMessage();
}
$sql = 'TRUNCATE '. $table_name;
$db->query($sql);
ini_set('auto_detect_line_endings',TRUE);
$f = fopen($path_to_file, 'r');
$data = [];
while($d = fgetcsv($f, 1024, $delimiter)) {    
    $data[] = $d;
}
$sql = 'INSERT INTO '.$table_name.' ('.implode($data[0], ', ').' ) VALUES ';
$i = 1;
$c = count($data);
for ($i = 1;$i < $c; $i++) {
    $sql .= '("'.implode($data[$i], '","');
    if ($i != ($c - 1) ){
        $sql .= '"), ';
    } else {
        $sql .= '") ';
    }
}
$db->query($sql);
?>

https://github.com/BrandynL/Import-CSV-to-an-SQL-table/blob/master/sql-import-csv.php

It’s just that simple!

Now that we have all of our data, we need a place to display in in the WordPress admin dashboard for reviewing and other troubleshooting assistance

Display all policies on the WordPress admin dashboard

The next step involved creating a custom page that utilizes the $wpdb object along with some optional filters we can get from the URL.

Once we have our results, we can simply use a bit of RegEx to filter out some wonky placeholder data as well as display how we want!

<?php
// start looping through policies
$sql = 'SELECT * FROM table_name';
// do the filter
if (!empty($_GET['policySearch'])){
    $s = $_GET['policySearch'];
    $sql .= " WHERE PolicyRef = '$s'";
} else {
    $sql .= ' LIMIT 50 ';
    echo "<p>Note: Results limited to first 50</p>";
}
global $wpdb;
$results = $wpdb->get_results($sql);
$o = [];
$output = exec('crontab [-u root] -l', $o);
// var_dump($o);
// regex results for pretty
$premiumTrailing = '/\.?0{4,}/i';
if (!empty($results[0])) { // check if results are returned
?>
    <h5><?= count($results) . " Result(s) Found" ?></h5>
    <table id='policyHolders'>
        <tbody>
        <?php foreach($results as $r){ ?>
            <?php
            // skip the line with all dashes
            $skipped = false;
            if ($skipped == false){
                preg_match('/-{3,}/i', $r->PolicyRef, $match);
                if (!empty($match)){
                    $skipped = true;
                    continue;
                }
            }
            if ($s) { ?>
            <tr>
            <td>PolicyRef</td>
            <td>FirstName</td>
            <td>LastName</td>
            <td>Zip</td>
            <td>PolicyEffDate</td>
            <td>PolicyExpDate</td>
            <td>TotalPremium</td>
            </tr>
            <?php } 
            echo "<tr>";
            echo "<td>$r->PolicyRef</td>";
            echo "<td>$r->FirstName</td>";
            echo "<td>$r->LastName</td>";
            echo "<td>$r->Zip</td>";
            echo "<td>". preg_replace('/(0{2,}[:\.]?)/i', '', $r->PolicyEffDate) . "</td>";
            echo "<td>". preg_replace('/(0{2,}[:\.]?)/i', '', $r->PolicyExpDate) . "</td>";
            echo "<td>". preg_replace('/\.?0{4,}/i', '.00', $r->TotalPremium) . "</td>";
            echo "</tr>";
        } ?>
        </tbody>
    </table>
<?php } else {
    echo '<h2>No results found</h2>';
} ?>

User policy payment portal login with custom payment integration

This was perhaps the most intricate (yet fun!) aspect of the entire build.

After making a custom page template for this, we don’t want users to access the page when we are truncating the table and importing the new policyholder information

<?php
date_default_timezone_set('America/New_York');
$datecheck = localtime(time(), $is_associative = true);
$d = (int)($datecheck['tm_hour'] . $datecheck['tm_min']);
if ($d >= 1800 && $d < 1830 ){
 // display a maintenance message
}
?>

Otherwise, we want to grab the information to display, as well as include in a form we build to integrate a payment gateway. This was basically the same instance as the code above, utilizing $wpdb

After we have the data, we use PHP request method checks along with the value of a hidden field to see if we are submitting a payment, and which payment type we are attempting:

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['payment-submit-credit-card'])) {
    // filter all the inputs
    // do the credit card payment
} else if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['payment-submit-ach'])) {
    // filter the inputs
    // do the ACH payment
}

After we successfully process our payment, we want to log the transaction to a log file as json, which we set up our own log rotation based on file size

function create_new_transaction_log($_log_msg, $_log_dir){
    $label = time().".log";
    fopen($_log_dir.$label, 'a');
    error_log($_log_msg, 3, $_log_dir.$label);
}
function log_payment_transaction($request, $response) {
    $_log_dir = __DIR__."/includes/logs/"; // path to log dir
    $_max_log_size = 1034000; // in bytes
    $_log_files = [];
    $req = json_decode($request, true);
    // obfuscate values that we dont want to store in the transaction log....
    if ( isset($req['tele_check']) ) {
        $req['tele_check']['check_number'] = substr($req['tele_check']['check_number'], -4);
        $req['tele_check']['account_number'] = substr($req['tele_check']['account_number'], -4);
        $req['tele_check']['customer_id_number'] = substr($req['tele_check']['customer_id_number'], -4);
        $response['tele_check']['customer_id_number'] = substr($response['tele_check']['customer_id_number'], -4);
    } else if (isset($req['credit_card'])) {
        $req['credit_card']['card_number'] = substr($req['credit_card']['card_number'], -4);
    }
    $_log_msg = "Transaction Timestamp:".time()."\n--Request: ".json_encode($req)."\n--Response: ".json_encode($response)."\n";

    if (is_dir($_log_dir)) {
        if ($dh = opendir($_log_dir)) {
            while (($file = readdir($dh)) !== false) {
                if (preg_match("/(.*)\.log/", $file)) {
                    $_log_files[] = $file;
                }
            }
            closedir($dh);
        }
    }

    if (!empty($_log_files)){
        foreach($_log_files as $log_file){
            $working_file = filesize($_log_dir.$log_file) < $_max_log_size ? $log_file : null;
        }
        if ($working_file){
            error_log($_log_msg, 3, $_log_dir.$working_file);
        } else {
            create_new_transaction_log($_log_msg, $_log_dir);
        }
    } else {
        create_new_transaction_log($_log_msg, $_log_dir);
    }    
}

Whew, now that we have that out of the way, we need to log the details of this successful payment to the database for later use

Transaction Logging System

Next we need to log all successful transactions to the database so we can export them to a text file later.

Thankfully we can create a function that will handle this for us once we have all the required data:

function db_log_payment_data($payment_data){
    global $wpdb;
    $columns;
    $values;
    foreach($payment_data as $line => $key){
        $columns[] = $line;
        $values[] = $key;
    }
    $sql = "INSERT INTO table_name ("
    . implode(", ", $columns)
    .") VALUES ('"
    . implode("', '", $values)
    ."')";
    $wpdb->get_results($sql);
}

Email notifications

Another great time to create a small function to handle this!

function fk_send_email_confirmation($recipient, array $data){
    $to = $recipient;
    $subject = 'Insurance Payment Received';
    $body = "<h3>Payment Received</h3>";
    $body .= "<p>Here are the details of the transaction:</p><p>";
    foreach($data as $d=>$k){
        if (empty($k)) continue;
        $body .= "<strong>$d</strong> : $k<br>";
    }
    $headers[] = 'Content-Type: text/html; charset=UTF-8';
    $headers[] = 'From: preson <noreply@company.com>';
    $headers[] = 'Reply-To: preson <info@company.com>';
    
    // add bcc emails - maybe build this into plugin later ?
    $headers[] = 'Bcc: someone@company.com';
    $headers[] = 'Bcc: someone-else@company.com';
    wp_mail( $to, $subject, $body, $headers );
}

Transaction history export functionality for format specific importing

The company had a 3rd party CRM that handled their policyholder database and it was a bit antiquated – but the new site had to work with it, so a custom plugin was created that allowed admins to view all transactions stored in the database, as well as filter dates and download as a perfectly formatted import file.

This system required a .txt file upload that would make updates to policies in the database based on values in the text field, separated by a total number of alloted characters for each value as shown in the $colstart variable

<?php
    /**
     * optional error reporting
     */
    // error_reporting(E_ALL);
    // ini_set('display_errors', 1);

    //format date
    $start = $_GET['start'];
    $d_start = new dateTime($start);
    $start = $d_start->format('mdY');

    $filename = $d_start->format('m-d-Y');
    
    if (!empty($_GET['end']) && isset($_GET['end'])){
        $end = $_GET['end'];
        $d_end = new dateTime($end);
        $end = $d_end->format('mdY');
        $filename .= "-to-".$d_end->format('m-d-Y');
    }
    
    $filename .= "_payment-report.txt";
    // header
    header("Content-type: text/plain");
    header("Content-Disposition: attachment; filename=".$filename);
    
    // do the download
    $host = 'host_details';
    $db = new PDO($host, 'user', 'pass');
    $sql = "SELECT
        state,
        site_merchant,
        billing_type,
        account_number,
        premium_due_date,
        payment_date,
        payment_method,
        FORMAT(bill_amount, 2),
        FORMAT(bill_amount, 2) as company_amount,
        FORMAT(provider_fee, 2),
        FORMAT(total_payment, 2),
        transaction_id,
        policy_number,
        zip as field_2,
        premium_due_date as field_3,
        TRIM(CONCAT(first_name,' ', last_name)),
        account_number as field_5,
        email,
        first_name,
        last_name,
        street,
        city,
        state as payee_state,
        zip
    FROM payments WHERE payment_date ";
    if (!isset($end) || empty($end)) {
        $sql .= " = $start";
    } else {
        $sql .= " >= $start and payment_date <= $end";
    }


    $data = $db->prepare($sql);
    $data->execute();
    $results = $data->fetchAll(PDO::FETCH_ASSOC);
    // column each line has to start at, in order
    $colstart = [
        1, // state
        21, // site merchant
        71, // billing_type
        121, // unique_id - policyID/account number
        171, // bill date
        179, // payment date
        187, // payment method
        193, // bill amount
        217, // company amount
        238, // provider fee
        247, // total payment
        259, // transaction id
        271, // policy number field_1
        321,// mailing address zip field_2 -- NEW
        371,// due date field_3 -- NEW
        421,// insured name field_4 -- NEW
        471,// account number field_5 -- NEW
        521, // payee email
        578, // name (name1) -- NEW
        629, // name (name2)
        679, // payee address -- NEW
        779, // payee city -- NEW
        839, // payee state -- NEW
        838, // zip
        0 // end
    ];
    foreach ($results as $r ) {
        $counter_index = 1; //need to get column number for the next line
        foreach ($r as $line) {
            if (is_string($line) == true && trim($line == '')) {
                echo str_pad(" ", ($colstart[$counter_index] - $colstart[($counter_index - 1)]), " ", STR_PAD_RIGHT);
            } else {
                echo str_pad($line, ($colstart[$counter_index] - $colstart[($counter_index - 1)]), " ", STR_PAD_RIGHT);
            }
            $counter_index++;
        }
        if (!empty(next($results))) {
            echo "\r\n";
        }
    }
?>

Insurance agent profile directory and login integrating a cross domain authentication

Lastly, the company had about 100 insurance agents that needed the ability to login and view/download important documents that were provided to all agents. Another feature would include a directory of all agents on the site, and the ability to update this information.

Thankfully, a plugin handled just about all of this with very minimal tweaking: Ultimate Member Pro

Lastly, when agents log into the site, they should automatically by signed into an affiliate site used for generating reports, quotes, claims, and other things of that nature by using shared login credendtials unknown to them with the use of a few session variables

Administrators would also have this feature, but with a different set of login credentials.

We could make this all come together perfectly by hooking in the [code]wp_login[/code] hook:

add_action('wp_login', 'cross_authentication', 10, 2);
function cross_authentication($user_login, $user){
    $url = "https://the-url.com";
    $_SESSION['username'] = $user_login;
    $_SESSION['roles'] = $user->roles;

    if (in_array("administrator", $user->roles)){
        $_username = "ASpecifiedUsername";
        $_password = "ASpecifiedPassword";
        $vusername = $_SESSION["username"];
        $url .= "?username=$nazm_username&password=$_password&vusername=$vusername";
    } else {
        $_username = "AnotherSpecifiedUsername";
        $_password = "AnotherSpecifiedUsername";
        $vusername = $_SESSION['username'];
        $url .= "?username=$_username&password=$_password&vusername=$vusername";
    }
    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    $response = curl_exec($ch);
    curl_close($ch);
    
    // decode from json
    $parse = xml_parser_create();
    xml_parse_into_struct($parse, $response, $return, $index);
    xml_parser_free($parse);
        
    $_SESSION['request-url'] = $url;
    $_SESSION['curl-resp'] = $return;
    $_SESSION['cross-authcode'] = $return[1]['value'];
    $_SESSION['cross-authid'] = $return[3]['value'];
}