WhosOn / Email2DB - Dan's Development Blog

Custom Field Filling in WhosOn Database

2010-10-13T13:37:00.004+01:00

I had a requirement from a customer to collect the order numbers from their website, but at their current state they were unable to use the normal method of modifying the tracking code to set the order details in WhosOn. Fortunately, the details of the order were stored inside the URL for the customer, on the thank you page thankyou.aspx as the parameter OrderID.

I wanted this to show up as a form fill variable so it can easily be used for querying the order number based on chatted, invited etc.

The route I went down for this was modification of the AddPageView procedure. This procedure finds the page from the page header information (or inserts it if this page hasn't been seen before) and inserts a view record for the visitor.

The code below could be applied to the WhosOn Server Database in order to AddPageView

ALTER PROCEDURE [dbo].[AddPageView]

@VisitID int,

@VisitorID int,

@SiteKey smallint,

@PageName varchar(250),

@PageDate datetime,

@PageNumber smallint,

@ContentGroup varchar(50),

@HTTPStatus smallint,

@HTTPSubStatus smallint,

@CachedPageHeaderID int,

@PageHeaderID int OUTPUT,

@LastModifiedDate datetime OUTPUT,

@LastCheckedDate datetime OUTPUT,

@TitleTag varchar(250) OUTPUT

BEGIN

SET NOCOUNT ON;

DECLARE @cgroup varchar(50)

DECLARE @orderref varchar(50)

IF CHARINDEX('thankyou.aspx',@PageName) > 0

BEGIN

DECLARE @ia INT;

DECLARE @iaa INT;

SET @ia = CHARINDEX('OrderID', @PageName);

SET @ia = CHARINDEX('=',@PageName, @ia);

SET @iaa = CHARINDEX('&',@PageName, @ia);

if @iaa = 0

BEGIN

SET @iaa = LEN(@PageName);

END

SET @orderref = SUBSTRING(@PageName,@ia+1, @iaa-@ia);

INSERT INTO dbo.VisitFormVars (VisitID, SiteKey, VarForm, VarName, VarData, VarTime)

VALUES (@VisitID, @SiteKey, 'Order', 'OrderNumber', @orderref, GETDATE())

SET @PageName = 'thankyou.aspx';

SET @CachedPageHeaderID = 0;

END

IF @CachedPageHeaderID = 0

BEGIN

SELECT @PageHeaderID = PageHeaderID,

@LastCheckedDate = LastChecked,

@LastModifiedDate = LastModified,

@TitleTag = TitleTag,

@cgroup = ContentGroup

FROM PageHeader

WHERE SiteKey = @SiteKey AND Page = @PageName

IF @PageHeaderID IS NULL

BEGIN

INSERT INTO PageHeader (

Page,

ContentGroup, LastModified,

LastChecked, SiteKey, TitleTag)

VALUES

(@PageName, @ContentGroup, '2000-01-01 00:00:00','2000-01-01 00:00:00', @SiteKey, '');

SET @LastCheckedDate = '2000-01-01 00:00:00';

SET @LastModifiedDate = '2000-01-01 00:00:00';

SET @TitleTag = '';

SET @PageHeaderID = SCOPE_IDENTITY()

END

ELSE

BEGIN

IF NOT @cgroup = @ContentGroup

UPDATE PageHeader SET ContentGroup = @ContentGroup

WHERE PageHeaderID = @PageHeaderID;

END

ELSE

SET @PageHeaderID = @CachedPageHeaderID

INSERT INTO PageViews

(VisitID, PageID, VisitorID, SiteKey, PageDate, PageNumber,

HTTPStatus, HTTPSubStatus)

VALUES

(@VisitID, @PageHeaderID, @VisitorID, @SiteKey,

@PageDate, @PageNumber, @HTTPStatus, @HTTPSubStatus);

END

Set Prospect Client Command

2010-10-12T00:32:00.007+01:00

I've just completed a test project designed for adding more rules into how WhosOn Live Chat can decide on Prospect Detection. Prospect detection is used for setting up different rules for allocating the quality of visitors, and very commonly as something to send automatic invites based on. The new WhosOn V6 that will be released shortly allows for multiple types of prospect to be defined.

This uses the new Set Prospect command to insert the prospect into WhosOn and force the actions for that prospect to happen.

The system connects to a given site using the client protocol, and stores the visitors internally, subscribing to the WhosOn visitor event model (waiting for the @EV and @ER events, and using an internal array to hold the information).

The detection allows for second level triggering of time on site, time on page, time idle etc, and is easily plugable to allow for expansion of the types of filters that can be used.

It breaks down the rules into two types of rules - those that require timers, and those that don't.
If the rules don't require timers, then they wait for the events (or the initial visitor list population on connection) and check the rules at this stage.

For the rules that do require timers, these are executed based on the timer refresh length set in the program, and the number of seconds can be set here.

The rules are multi-threaded (along with each connection) so the program can deal with many connections for many different rule sets.

The rule set uses an XML structure for if a rule should be activated, containing the rule type and the login information.

Rules that I have created so far:

Regular Expression for Current Page, Previous Page, Any Page this Session
Form Variable Check
Number of Seconds on Page
Number of Seconds on Site

Possible Future updates:

More Rules Types
More intelligent timer - don't need to check every X seconds if there are no new visitors. This could be calculated by checking the current timer wait time, and minimum time for the rule, then setting up the internal timers to wait this long.
More Actions (I.E. sending different messages through to the visitors in the invites, or sending messages to the operators about the visitors).
Performance enhancements on the visitors arrays.
Use reflection for loading the types, rather than just doing a case selection on the type name.

If you are interested in using this sort of rules engine, then please contact our professional services team, and we will endeavour to match up prospecting systems to deal with whatever your requirements for prospecting are. The link below shows information about our Configuration Professional Services for WhosOn Live Chat.

http://www.whoson.com/professionalconfiguration.aspx

WhosOn Database - Preventing Storage of Chat Transcripts

2010-10-01T21:24:00.004+01:00

Sometimes it is necessary for security or privacy reasons to not store the chat transcript in the database. In WhosOn this can easily be achieved by adding a new database trigger to the database.

The following trigger drops the INSERT command if the LineNumber > 1, and for the first line creates an entry to the effect that the transcript is not stored.

The visitor's session information will still be stored, but this could be prevented with another trigger if required.

Just run the below code in SQL Management Studio, in a SQL Query window against the WhosOn database.

CREATE TRIGGER [dbo].[UserTranscriptStop]
ON [dbo].[UserTranscript]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for trigger here
DECLARE @LineNum int
SELECT @LineNum = LineNumber FROM INSERTED

IF @LineNum = 1
BEGIN
INSERT INTO UserTranscript (SiteKey, ChatUID, LineNumber, LineTime, OperatorLine, LineText)
SELECT SiteKey, ChatUID, 1, LineTime, OperatorLine, 'Data not stored in database due to DB Settings'
FROM INSERTED
END
END

Tracking Order Numbers Into WhosOn

2010-07-16T11:12:00.003+01:00

If you want to track order numbers, or some other generic field, which you may then want to query from the database, you can use code similar to the below in your page.

This executes the standard form code, which is set up to store and show inside WhosOn already.

sWOImageLoaded = function () {

var imOrder = document.createElement('IMG');

imOrder.src = sWOProtocol+"//"+sWOGateway+"/form.gif?d="+sWODomain+"&u="+sWOSession+"&frm=Order&frn=OrderID&frd=WOTEST";

document.body.appendChild(imOrder);

}

if(typeof sWOTrackPage=='function')sWOTrackPage();

</script>

WCF and Silverlight development – impressions and usage.

2010-02-19T21:46:00.003Z

We’ve recently developed a Silverlight based client, and I worked on the backend code for communication between the Silverlight client and the WhosOn server. WhosOn’s general encryption is Public / Private Key to obtain a Session Key, then AES 256 using this session key for normal data.

Silverlight doesn’t currently have support for these areas of the .NET framework, so we were left with 3 choices:

1. TCP + Plain Text

2. TCP + Alternative Encryption

3. Proxied Connection

Using the proxied connection had the advantage of being able to use a more ubiquitous transport, so that we could go through client side proxy servers and avoid other issues with firewalls blocking the non-standard ports.

TCP + Plain Text was ignored due to insecurity, and TCP + Alternative Encryption was discounted due to the lack of any high security protocols in Silverlight.

Next, we had to decide how to write our proxy server. We already have a .NET based class library for connecting to the server, so .NET was the first approach. We looked at creating our own HTTP handling service, or running something through the existing WhosOn Gateway Service which handles simple HTTP requests.

I discovered that WCF had the ability to run through IIS and provide secure connections. Investigating further, I found that you could have an asynchronous subscribe / publish mode as described by Tomasz Janczuk in his blog: http://tomasz.janczuk.org/2009/07/pubsub-sample-using-http-polling-duplex.html

Having looked through his code, and developed our own Subscriber style interface (although at the moment it is the equivalent of each subscriber subscribing to a single topic), we got to our current Silverlight client communication library.

The web services can technically be used by other SOAP supporting clients, and we will be looking at pushing out examples using these.

We ran into a few issues which we haven’t figured out as yet – we’ve had trouble doing an automatic deployment (including installing .net 3.5, registering WCF against IIS, and setting up the application pool), and also the difficulty in allowing the same path to run both secure and insecure versions of the customBinding at the same time.

To have a look at our documentation on it, please see http://www.whoson.com/help/Content/SilverlightClient.htm which includes the installation steps.

Using SitePal for the WhosOn Chat Operator Status

2009-07-16T12:32:00.010+01:00

I've had a few requests recently to show a SitePal rep when you are available to talk. You can do this with some custom javascript code. An extension to this is to make it so that if the SitePal is clicked, it opens the chat window.

You will have your standard embed code from SitePal - this will look like:

<script language="JavaScript" type="text/javascript" src="http://vhss-d.oddcast.com/vhost_embed_functions_v2.php?acc=100000&js=1"></script><script language="JavaScript" type="text/javascript">AC_VHost_Embed(100000,300,400,'',1,1, 100000, 0,1,0,'aaaaaaaaaaaaaaaaaaaaa',9);</script>

This is made up of two components - the loading of the javascript from the site, and the call to create the window. We want to create this window when the operator status is online.

You will also have your WhosOn code, which will look like this:


<script type='text/javascript' src='http://yourwhosongateway.com/include.js?domain=www.domain.com'></script>
<script type='text/javascript' >
if(typeof sWOTrackPage=='function')sWOTrackPage();
</script>


To prepare in your WhosOn, first upload your normal online and offline images into your website - these will no longer be used directly in WhosOn.

In your site properties -> Visitor Chat -> Display Nothing When Off Line

This will mean that the WhosOn code will return a 1x1 pixel when offline, and we can test for this in the javascript.

We leave the first line of WhosOn code in place:

<script type='text/javascript' src='http://yourwhosongateway.com/include.js?domain=www.domain.com'></script>

but we are going to override some of the other things - this is the standard custom code for execution / override of what happens when the status is returned.

<script>
function overrideLoad()
{
if (sWOImage.width == 1)
{
// show offline image
var sWOOffline = document.createElement('img');
sWOOffline.src = "/images/offline.gif";
sWOChatElement.appendChild(sWOOffline);
}
else
{
var sWOOnline = document.createElement('img');
sWOOnline.src = "/images/online.gif";
sWOChatElement.appendChild(sWOOnline);sWOChatElement.onclick=sWOStartChat;
}
}
if(typeof sWOTrackPage=='function')
{
sWOImageLoaded=overrideLoad;
sWOTrackPage();
}
</script>

Now we need to add in the call to the SitePal.

The complete code will look like:

<script language="JavaScript" type="text/javascript" src="http://vhss-d.oddcast.com/vhost_embed_functions_v2.php?acc=100000&js=1">

<script type='text/javascript' src='http://yourwhosongateway.com/include.js?domain=www.domain.com'></script>
<script>
function overrideLoad()
{
if (sWOImage.width == 1)
{
// show offline image
var sWOOffline = document.createElement('img');
sWOOffline.src = "/images/offline.gif";
sWOChatElement.appendChild(sWOOffline);
}
else
{
AC_VHost_Embed(100000,300,400,'',1,1, 100000, 0,1,0,'aaaaaaaaaaaaaaaaaaaaa',9);
var sWOOnline = document.createElement('img');
sWOOnline.src = "/images/online.gif";
sWOChatElement.appendChild(sWOOnline);sWOChatElement.onclick=sWOStartChat;
}
}
if(typeof sWOTrackPage=='function')
{
sWOImageLoaded=overrideLoad;
sWOTrackPage();
}
</script>

You can see that we have called the AC_VHost_Embed function as part of the online status call.

This will mean that your SitePal will show, and it will normally say something like "click below to chat to us" or "click the 'online' link on the right to chat to us"

You can also get the scene to directly send someone to the chat when it is clicked on.
First, we need to generate the click link in WhosOn - go to site properties -> visitor chat -> manual links, and you will get a cut & paste link like:

<a href="https://chatserver.whoson.com/chat/chatstart.htm?domain=www.domain.com" onclick="javascript:window.open('https://chatserver.whoson.com/chat/chatstart.htm?domain=www.domain.com','wochat','width=484,height=361');return false;">Click To Chat</a>

You only need the href part of this:
https://chatserver.whoson.com/chat/chatstart.htm?domain=www.domain.com

Then, when you are creating the scene in SitePal, if you go to the Scene Options, then put the href part of the link into the URL: box.

Then you need to check "clicking on the scene opens the link", and the "open in new window" option. Now when someone clicks on your new SitePal scene, it will open up the WhosOn chat window.

You can see the areas for this in the SitePal User Guide, section 2.5

Using WhosOn in your Blogspot Blog.

2009-04-17T10:27:00.006+01:00

I've decided to add tracking & chat to my blog here, partially as an excercise in how to do it and also for vanity purposes... although the small number of human visitors is always a disappointment!

In the WhosOn client, generate the tracking code. It will look like this:

<script type='text/javascript' src='http://{your-gateway}/include.js?domain={your-domain}'></script><script type='text/javascript' >if(typeof sWOTrackPage=='function')sWOTrackPage();</script>

Add an HTML/Javascript Gadget to the bottom of your blog Layout, and paste this code into it.

Then, you can add another Gadget to show the chat link - I put a new HTML/Javascript Gadget on the left hand side, and put in:

<a id='whoson_chat_link'></a>

The WhosOn code dynamically replaces this link with the correct image (online / offline).

If you only want to track, then you can generate the tracking only code through the client - you don't need to put the anchor in the left if you do this.

WhosOn V5 - Scripts

2009-04-15T17:51:00.003+01:00

WhosOn Version 5 (http://www.whoson.com/v5whatsnew.aspx) has some great new features for users who want more advanced chat distribution among their agents.

We've used a VBScript compatible engine, which allows the script to access the data about the visitor (IP, Geo-Location, #Visits etc) and any pre-chat survey information that has been filled in, and use this to route the chat to the correct operator, send some automatic text, or cancel / block the chatter.

This means dynamic rules can be used to find existing customers, and pass through to the right sales person based on a database query, or just get the lead to the correct person immediately, rather than having to do a manual transfer after the event.

I'm working with some of our customers to provide the custom logic for what they need - here is the first example I have worked on:

Sub Main()

' commands start here

CancelChatRequest = False

Dim ticket As String

Dim tr As String

Dim p As Integer

tr = ThisChatSession.PreChatSurvey

p = InStr(1, LCase(tr), "ticket")

If p > 0 Then

p = InStr(p, tr, "") + 7

ticket = Left(Mid(tr, p), InStr(p, tr, "") - p)

AddToLog("Ticket: " & ticket)

Dim conn As New ADODB.Connection

Dim adors As New ADODB.Recordset

conn.ConnectionString = "DSN=Tickets;"

conn.Open

Set adors.ActiveConnection = conn

With adors

.Open("SELECT * FROM Tickets WHERE TicketNo = '" & ticket & "'")

If Not .EOF Then

Dim op As String

op = LCase(.Fields("Operator"))

' check if operator is online

For p = 0 To OperatorsConnectedCount - 1

If op = LCase(Operator(p).Name) And Operator(p).CurrentStatus <>

' if operator is online or busy, send this chat to them

SendChatRequestToOperators = op

SendMessageToVisitor = "You are being connected to the assigned user for this issue - " & op

AddToLog("Script: Operator for issue online: " & op)

p = OperatorsConnectedCount

End If

Next p

If Not Len(SendChatRequestToOperators) > 0 Then

SendMessageToVisitor = "The operator (" & op & ") assigned to this issue is not online - transfering to Support"

AddToLog("Script: Operator for issue not online: " & op)

SendChatRequestToDepartments = "Support"

End If

Else

SendMessageToVisitor = "The ticket number was not found - transfering to Support"

AddToLog("Script: The ticket number was not found")

SendChatRequestToDepartments = "Support"

End If

.Close

End With

Set adors = Nothing

conn.Close

Set conn = Nothing

Else

SendChatRequestToDepartments = "Support"

End If

End Sub