TimeonsiteTracker (TOS) - Saving TOS to database

Saving TOS to database - Examples || Server-side data handling

Important! This page is only for demonstrating schema and sample data - save operation. Entire backend code is already available for immediate access and use in PHP/NodeJs Visual, reporting and analytics dashboard

Time On Site Tracking

Node.js example

//file 1: tos.route.js
var controller = require('tos.controller.js')

module.exports = function(app) {

    app.route('/tos')
        .post(controller.SaveTos);
};



//file 2: tos.model.js
var mongoose = require('mongoose'),
Schema = new mongoose.Schema({
    //dynamic schema
}, {
    versionKey: false,
    strict: false
});

var TosSchema = mongoose.model('tos', Schema);
module.exports = TosSchema;



//file 3: tos.controller.js
var TosSchema = require('tos.model.js');
module.exports.SaveTos = function(req, res, next) {
    var data = req.body;
    console.log(data);

    var freshData = {};

    if (data && data.trackingType && data.trackingType == 'tos') {

        // converting to ISO date format
        data.entryTime = (new Date(data.entryTime)).toISOString();
        data.exitTime = (new Date(data.exitTime)).toISOString();

        freshData = {
            tos_id : data.TOSId,
            tos_session_key : data.TOSSessionKey,
            tos_user_id : data.TOSUserId,
            url : data.URL,
            title : data.title,
            entry_time : data.entryTime,
            timeonpage : data.timeOnPage,
            timeonpage_tracked_by : data.timeOnPageTrackedBy,
            timeonsite : data.timeOnSite,
            timeonpage_by_duration : data.timeOnPageByDuration,
            timeonsite_by_duration : data.timeOnSiteByDuration,
            tracking_type : data.trackingType,
            exit_time : data.exitTime
        };


        var Tos = new TosSchema(freshData);
        // Data saved to mongoDB's tos collection
        Tos.save(function(err, record) {
            console.log(err);
            res.send('success');
        });

    } else {
        console.log('TOS data not received. Please check your endpoint URL and POST data from client-side!');
    }
    
};

     The example uses node's express framework to handle server-side data. We have three files here for saving data with node.js. A route file, a model file and a controller file.

* In route file, we create a route named "/tos" which is of POST method to accept data from incoming requests.

* In model file, we create new schema named "tos" which will save our TOS data. Since mongoDB is schema-free, we can save any number of fields in its collection. This is preferrable for collecting large number of custom data in TOS over RDBMS like MSSQL, MySql/Maria DB or Oracle.

* In controller file, we just check if there is data in "SaveTos" method. Then we load schema file and save our TOS data. By now, you might check your mongoDB collection to see if there is any data saved successfully.

PHP example

// Create database named "tosdata". Then, create table in any RDBMS (this example was tested in mysql/maria DB) as follows:

CREATE TABLE `tos` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `tos_id` decimal(30,0) DEFAULT NULL,
 `tos_session_key` varchar(50) DEFAULT NULL,
 `tos_user_id` varchar(1000) DEFAULT NULL,
 `url` varchar(10000) DEFAULT NULL,
 `title` varchar(5000) DEFAULT NULL,
 `entry_time` datetime(3) DEFAULT NULL,
 `exit_time` datetime(3) DEFAULT NULL,
 `timeonpage` int(11) DEFAULT NULL,
 `timeonpage_by_duration` varchar(20) DEFAULT NULL,
 `timeonpage_tracked_by` varchar(15) DEFAULT NULL,
 `timeonsite` int(11) DEFAULT NULL,
 `timeonsite_by_duration` varchar(20) DEFAULT NULL,
 `tracking_type` varchar(15) DEFAULT NULL,
 PRIMARY KEY (`id`)
) CHARACTER SET utf8 COLLATE utf8_general_ci

//In PHP, create file: tos.php
<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    $data = json_decode(file_get_contents("php://input"));
    //print_r($data);

    // Mysql/Maria DB credentials
    $host = "localhost";
    $user = "root";
    $password = "";
    $database = "tosdata";

    $con = mysqli_connect($host, $user, $password, $database);
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    // Perform queries
    $qry = "INSERT INTO tos (tos_id, tos_session_key, tos_user_id, url, title, entry_time, exit_time, timeonpage, timeonpage_by_duration, timeonpage_tracked_by, timeonsite, timeonsite_by_duration, tracking_type) VALUES (".$data->TOSId.", '".$data->TOSSessionKey."', '".$data->TOSUserId."', '".$data->URL."', '".$data->title."', '".$data->entryTime."', '".$data->exitTime."', ".$data->timeOnPage.", '".$data->timeOnPageByDuration."', '".$data->timeOnPageTrackedBy."', ".$data->timeOnSite.", '".$data->timeOnSiteByDuration."', '".$data->trackingType."')";
    //echo $qry;
    $res = mysqli_query($con, $qry);

    mysqli_close($con);
    if ($res) {
        echo 'success';
    } else {
        echo 'error';
    }
} else {
    echo 'TOS data not received. Please check your endpoint URL and POST data from client-side!';
}

* We form the required SQL query for saving our data in MySql or any other RDBMS of your preference. Create a database named "tosdata". Then execute the given query to create new table named "tos".

* Create a new file named "tos.php" which checks if there is any incoming request with POST data. In case of valid data, it saves the data to "tos" table in database we just created. Do not forget to start your server (Apache, nginx etc.) that runs PHP while working out this example. Since saving data in RDBMS is schema-based and type checking is strict, you need to be careful in defining the fields while creating the table given above. In case of adding custom data or additional data in future, you need to modify schema and verify thoroughly to avoid query failure or data loss.

Activity Tracking - NodeJs

//file 1: activity.route.js
var controller = require('activity.controller.js')

module.exports = function(app) {

    app.route('/activity')
        .post(controller.SaveActivity);
};



//file 2: activity.model.js
var mongoose = require('mongoose'),
Schema = new mongoose.Schema({ 
// we don't specify any fields here since mongo is schema-free. If you want to 
// specify fields with data type, you could do that. It applies to tos.model.js 
// file too which we created in this page.
}, {
    versionKey: false,
    strict: false
});

var ActivitySchema = mongoose.model('activity', Schema);
module.exports = ActivitySchema;



//file 3: activity.controller.js
var ActivitySchema = require('activity.model.js');
module.exports.SaveActivity = function(req, res, next) {
    var data = req.body;
    console.log(data);

    var freshData = {};

    if (data && data.trackingType && data.trackingType == 'activity') {

        data.activityStart = (new Date(data.activityStart)).toISOString();
        data.activityEnd = (new Date(data.activityEnd)).toISOString();

        freshData = {
            tos_id : data.TOSId,
            tos_session_key : data.TOSSessionKey,
            tos_user_id : data.TOSUserId,
            url : data.URL,
            title : data.title,
            activity_start: data.activityStart,
            activity_end: data.activityEnd,
            time_taken: data.timeTaken,
            time_taken_by_duration: data.timeTakenByDuration,
            time_taken_tracked_by: data.timeTakenTrackedBy,
            tracking_type: data.trackingType
        };

        var Activity = new ActivitySchema(freshData);
        // Data saved to mongoDB's tos collection
        Activity.save(function(err, record){
            console.log(err);
            res.send('success');
        });

    } else {
        console.log('TOS activity data not received. Please check your endpoint URL and POST data from client-side!');
    } 
    
};

This code is self-explanatory. Create route, model and controller files for tracking activity. In controller, check if received data is of type "activity" if (data.trackingType == 'activity'). Save it to activities collection. You are done!

Activity Tracking - PHP

// Create database named "tosdata". Then, create table in any RDBMS (this example was tested in mysql/maria DB) as follows:

CREATE TABLE `activity` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `tos_id` decimal(30,0) DEFAULT NULL,
 `tos_session_key` varchar(50) DEFAULT NULL,
 `tos_user_id` varchar(1000) DEFAULT NULL,
 `url` varchar(10000) DEFAULT NULL,
 `title` varchar(5000) DEFAULT NULL,
 `activity_start` datetime(3) DEFAULT NULL,
 `activity_end` datetime(3) DEFAULT NULL,
 `time_taken` int(11) DEFAULT NULL,
 `time_taken_by_duration` varchar(20) DEFAULT NULL,
 `time_taken_tracked_by` varchar(15) DEFAULT NULL,
 `tracking_type` varchar(15) DEFAULT NULL,
 PRIMARY KEY (`id`)
) CHARACTER SET utf8 COLLATE utf8_general_ci

//In PHP, create file: activity.php
<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    $data = json_decode(file_get_contents("php://input"));
    //print_r($data);

    // Mysql/Maria DB credentials
    $host = "localhost";
    $user = "root";
    $password = "";
    $database = "tosdata";

    $con = mysqli_connect($host, $user, $password, $database);
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    // Perform queries
    $qry = "INSERT INTO activity (tos_id, tos_session_key, tos_user_id, url, title, activity_start, activity_end, time_taken, time_taken_by_duration, time_taken_tracked_by, tracking_type) VALUES (".$data->TOSId.", '".$data->TOSSessionKey."', '".$data->TOSUserId."', '".$data->URL."', '".$data->title."', '".$data->activityStart."', '".$data->activityEnd."', ".$data->timeTaken.",  '".$data->timeTakenByDuration."', '".$data->timeTakenTrackedBy."', '".$data->trackingType."')";
    //echo $qry;
    $res = mysqli_query($con, $qry);

    mysqli_close($con);
    if ($res) {
        echo 'success';
    } else {
        echo 'error';
    }
} else {
    echo 'No data received!';
}

Test API call with Postman client

URL: http://localhost/project-z/examples/tos.php  //give URL where your server-side tos.php file resides/NodeJs route to handle tos data.

Method: POST

Type: application/json

// In request body,
Body:
{
    "TOSId" : 263367021778,
    "TOSSessionKey" : "14835465391401391732",
    "TOSUserId" : "anonymous",
    "URL" : "http://localdata-tos.chennai/#user-sessions",
    "title" : "TimeonsiteTracker by saleemkce",
    "entryTime" : "2016-09-04 16:01:22.081",
    "timeOnPage" : 10,
    "timeOnPageByDuration" : "0d 00h 05m 39s",
    "timeOnPageTrackedBy" : "second",
    "timeOnSite" : 10,
    "timeOnSiteByDuration" : "0d 00h 00m 00s",
    "trackingType" : "tos",
    "exitTime" : "2016-09-04 16:01:32.945"
}

In HTTP client like Postman, you could test if your route works before applying the code in your application.

Important! This page is only for demonstrating schema and sample data - save operation. Entire backend code is already available for immediate access and in use PHP/NodeJs Visual, reporting and analytics dashboard

Developed with love for web & analytics at Chennai, remember Marina? All praise be to Almighty God.
TimeonsiteTracker.js, the commercial software.