UCCX Wallboard Development Part 3

Well after my last post you now all should have a good idea on how to get a good connection to the UCCX database. This does have some advantages for us now as we can run custom reports using this connection as well as getting information for the wallboards.
Maybe this could be another post in the future you never know.

To test the wallboard ODBC Connection is working i use a little program called RAZORSQL which you can download from here
I don’t get any money / Sponsorship for recommending this product but i have used it quite a lot and in my opinion worth a buy. They do however give you a free trail copy which should be good enough to get you going. (Has always worked for me)

doing a simple select from the wallboard queue you should see all of the csq’s configured on your system.

UCCX Real Time Data Write

Before you will see any data in the rtcsqssummary fields you need to enable the writing of this in the UCCX Admin.
Under Tools –> Real Time Snapshot config you will see this window.

tick all the boxes and make sure you add the ip address of your webserver in the field provided. This field is very important when you have a cluster. If you do not put the ip address of the wallboard server in here and want to query this web site http://<ip Address of UCCX/uccx/isDBMaster. if you don’t do this you will not see the window below.

This will become clearer when i talk about setting up a wallboard to work across clusters.(Futures)

Wallboards – Where to start

 

Well you can display what ever you want on a wallboard really but i guess we have to have somewhere to start. I am  interested in the following information.

• Logged In Agents
• Average Talk Time
• Longest Talk Time
• Available Agents
• Average Wait Time
• Longest Wait Time
• Talking Agents
• Oldest Contact
• How Many Calls have been handled
• Calls Waiting in Queue
• How many Calls Have been abandoned

this should be enough information to get us going.
Using a simple Select * from rtcsqssummary here csqname = ‘<CSQ Name>’ query you can display more information as this query will return the following information.

• csqname
• loggedinagents
• availableagents
• unavailableagents
• totalcalls
• oldestcontact
• callshandled
• callsabandonded
• callsdequeued
• avgtalkduration
• avgwaitduration
• longesttalkduration
• longestwaitduration
• callswaiting
• enddatetime
• workingagents
• talkingagents
• reservedagents
• startdatetime
• convavgtalkduration
• convavgwaitduration
• convlongestwaitduration
• convlongestwaitduration
• convoldestcontact

so as you can see there are lots and lots of fields you can display using just one simple query. if you need more complicated stats like agent status or even display things like SLA of a particular queue / team this is also possible if you have enough SQL knowledge and time (Trust me you will need some time). I may cover this in another post later on but for now lets keep things simple.

What Server Technology To Use

 

This is quite an important choice really as there are many programming languages out there in the wilds of the internets for us to use. You can choose any language you like for this so long as it can connect to an ODBC connection and query a SQL server, as you might think there are loads from classic ASP/JSP to the .nets or even PHP, this is why i use ODBC as so many different technologies can connect to it and use it.
i am going to use the same technologies as the GILA wallboards use for now and maybe later on expand on this to some other languages like .net or PHP.

So we are going to use a mix of classic ASP and JavaScript. Also just because we can i want to try and make this a little bit modular if possible (Programming gurus don’t hate on me i am not the best programmer in the world, even though i studied it for a while) to allow me to re use a lot of the code across multiple pages and keep thing more manageable in the future. So i am going to use some JQuery too. JQuery is a cool framework built of top of JavaScript making it easier to manipulate the DOM, this is good for us because we can use its DOM Manipulation goodness to make the cells of our nice new wallboard change colour when certain values are returned from the database.

Shall we talk about wallboard layouts now ?

 

I want to keep this simple for now and if any of you have been using the GILA wallboard then a lot of this will make a lot of sense, my version will be a little simpler well for now anyway and i know there are a million ways to create a wallboard i am just trying to spark some sort of idea in your minds as i have found little or no good resources that explain how to do any of this other than a great free solution (GILA-Wallboard 2.4) that we can use as a good template. Ramble Ramble Ramble …

Layouts

I want a simple layout to start with a page containing 4 cells down and 3 cells across or 4×3

ok time for some simple html code to get us started. (Before i get shot down in flames some of this code does need re-factoring and cleaning so i guess suggestions welcome )

Parameters File

To keep all the config settings in one place (Again thank GILA-WB Guys) i am using a parameters file parameters.cfg

<%
' **********************  Wallboard Parameters **************************
Const IPCCversion = 8
Const PageTitle = "UCCX Wallboard"
Const ServiceName = "Service Name"
Const Left_Logo = "../images/Blank.png"
Const Left_Logo_Alt_Txt = "Blank"
Const Right_Logo = "../images/Blank.png"
Const Right_Logo_Alt_Txt = "Blank"
Const ServerURL = "http://1.1.1.1/wb/"
Const DBsource = "2.2.2.2"
Const DSN = "Wallboard-1"
Const ServerName = "uccx01_uccx" 
Const ServicePort = "1504" 
Const Protocol = "onsoctcp"
Const DBdatabase = "db_cra"  
Const DBuserID = "uccxhruser"  
Const DBpass = "mypassword"  
Const companyName = "VOIPMonkey"
Const RefreshTime = 5
Const wbPageTitle = "Wallboard"
Const wbqueuename = "Service"
Const UCCX_1_IP = "2.2.2.2" ' * Future Use *
Const UCCX_2_IP = "3.3.3.3" ' * Future Use *
Const UCCX_Rest_URI = "/uccx/isDBMaster" ' * Future Use *
Const SQL_Conn_String = "" ' * Future Use *
Const SQL_Command = "select * from rtcsqssummary where csqname like 'Queue1'"
%>

currently this is a very simple parameters file but this may grow later on in the project.

All the layouts are done using divs and a css file.

CSS File we are using

/************************************************/
/*  Author:  	voipmonkey						*/
/*  Pupose:		Style Sheet for Wallboard		*/
/************************************************/


/* layout css */
* {
	text-align:center;
	vertical-align:text-top;
	font-family:Arial,Georgia,Serif;
}

#container {
  margin: 0 auto;
  height: 100%;
  width: 100%;
  text-align: left;
  }

#clear_float
	{
		clear: both;
	}
.clearfix:after {
   content: ".";
   visibility: hidden;
   display: block;
   height: 0;
   clear: both;
}
	
#lia,#at,#lt,#aa,#aw,#lw,#ta,#oc,#ch
	{
		float:left;
		margin: 0;
		width: 33%;
		height: 20%;
		display: block;
		padding: 0;
		border-style:solid;
		border-width:1px;
		border-color:black;
	}
#cw,#tm,#ca
	{
		float:left;
		margin: 0;
		width: 33%;
		height: 20%;
		display: block;
		padding: 0;
		border-style:solid;
		border-width:1px;
		border-color:black;
	}
	
/*page css*/
			
body
	{ 
		font: 85.5% "Arial", sans-serif;
		background-color: Black;
	}
	
h1	
	{
		text-align:center;
		vertical-align:text-top;
		font-size:60px;
		font-family:Arial,Georgia,Serif;
		color:White;
	}
	
h2	
	{
		text-align:center;
		vertical-align:text-top;
		font-size:30px;
		font-family:Arial,Georgia,Serif;
		color:White;
	}

This is a basic css to get the right layout for our wallboard. You need to create a css folder under the wallboard folder and save this into it with the name layout.css. I know this is not the cleanest but hey work in progress. This will be enough to get the desired layout we want. Now the gila wallboard does show you the status of your agents and this is something i want to utilise but for now as i keep saying simple simple simple.

ok so we got our parameters file and the css for this wallboard so i guess we should look at the layout for the wallboard.asp file. I will break this down into small chunks so can explain what each section does.

Lets Begin

The first thing we need to do is get a connection to the database and setup a record set to collect all the data and store it for display purposes. As you can see all the connection string has been broken up to use the variables we set in the parameters.cfg file.

<!DOCTYPE html>
<html>
<!---------------------------------->
<!-- Created VOIPMonkey			  -->
<!---------------------------------->
<!--#include file="./parameters.cfg"-->
<%
	
					WBConnect = "Dsn="& DSN &";" & _
						   "Host="& DBsource &";" & _
						   "Server="& ServerName &";" & _
						   "Service="& ServicePort &";" & _
						   "Protocol="& Protocol &";" & _
						   "Database="& DBdatabase &";" & _
						   "Uid="& DBuserID &";" & _
						   "Pwd="& DBpass &""
						   
					Set cn = Server.CreateObject("ADODB.Connection")
					cn.ConnectionTimeout = Session("ConnectionTimeout")
					cn.CommandTimeout = Session("CommandTimeout")
					cn.open WBConnect
					
					Set SQLStmt = Server.CreateObject("ADODB.Command")
					Set RS = Server.CreateObject ("ADODB.Recordset")
					SQLStmt.CommandText = SQL_Command
					SQLStmt.CommandType = 1
					Set SQLStmt.ActiveConnection = cn
					RS.Open SQLStmt
					RS.MoveFirst
	
				%>

Header Section

<head>
	<meta http-equiv="Refresh" content="<%=RefreshTime%>;">
	<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
	<title><%=PageTitle%></title>
	<link type="text/css" href="../css/wallboard.css" rel="stylesheet" />
		<script type="text/javascript" src="../js/jquery-1.6.2.min.js"></script>
		<script type="text/javascript" src="../js/Wallboard.js"></script>

The head section uses the old refresh technique to refresh the page. i need to figure out how to get this to refresh using Ajax i guess to make it slicker but for now this does function ok. we use the head section also to set the page title again from the parameters file and also load all the external JQuery files. All query files can be downloaded from www.JQuery.com or a better way if your web server has internet access is to point to a web location then the query is always up to date. as i have no internet on the web server calling locally is fine. CSS files are also loaded here too.

JQuery Section (Where the magic Happens) Wallboard.js

Most of the data returned by the query can just be displayed but i thought it would be nice to checked the returned data for certain fields and change the colour of the Div Background. This was it will catch your eye and come one replicate most wallboard out there. So for Available Agents, LoggedInAgents and calls waiting we have some JQuery code to alter the Divs

<script type="text/javascript">
			// DOM Ready !!
			$(function(){
			// Data Check and Div Change
			
			// Check Available Agents and set Display Colours
				CheckAvailableAgents(<%=RS("AvailableAgents")%>);
				
			// Check Logged In Agents and set Diaplay Colours
				CheckLoggedInAgents(<%=RS("LoggedInAgents")%>);
				
			// Check Calls Waiting and set Display Colours
				CheckCallsWaiting(<%=RS("CallsWaiting")%>);
			});
		</script>
		
		
</head>

The JQuery below is what makes the changes to wallboard when the DOM is ready.

(function($){
	CheckActiveServer = function(){
	//Check the UCCX Server for the master database for displaying data
		
	};
	CheckAvailableAgents = function(AvailableAgents){
		if (AvailableAgents == 0) {
				$('#aa').css('background-color','Red');
				$('#aa').css('color','white');
				}
		if (AvailableAgents >= 1) {
				$('#aa').css('background-color','Green');
				$('#aa').css('color','white');
				}
	}
	CheckLoggedInAgents = function(LoggedInAgents){
		if (LoggedInAgents == 0){
			$('#lia').css('background-color','Red');
			$('#lia').css('color','white');
		}
		
		if (LoggedInAgents >= 1){
			$('#lia').css('background-color','Green');
			$('#lia').css('color','white');
		}
	}
	CheckCallsWaiting = function(CallsWaiting){
		if (CallsWaiting >= 6){
			$('#cw').css('background-color','Red');
			$('#cw').css('color','white');
		}
		if (CallsWaiting >= 4){
			$('#cw').css('background-color','CD4232');
			$('#cw').css('color','white');
		}
		if (CallsWaiting >= 2){
			$('#cw').css('background-color','CD5547');
			$('#cw').css('color','white');
		}
		if (CallsWaiting == 1){
			$('#cw').css('background-color','CD826F');
			$('#cw').css('color','white');
		}
		/*if (CallsWaiting == 0){
			$('#cw').css('background-color','Black');
			$('#cw').css('color','White');
		}*/
	}
})(jQuery);

This code above need to be store in a file called wallboard.js which we referenced in the head section above.

Main Body Section

Now for the rest of the code this takes all of the stuff we setup above and renders the page for the user.

<body>
<div id="container">
<div id="main_content">
	<div id="lia">
		<h2>Logged In Agents</h2>
		<h1><%=RS("LoggedInAgents")%></h1>
	</div>
	<div id="at">
		<h2>Average Talk</h2>
		<h1><%=RS("ConvAvgTalkDuration")%></h1>
	</div>
	<div id="lt">
		<h2>Longest Talk</h2>
		<h1><%=RS("ConvLongestTalkDuration")%></h1>
	</div>
<div class="clearfix"></div>
	<div id="aa">
		<h2>Available Agents</h2>
		<h1><%=RS("AvailableAgents")%></h1>
	</div>
	<div id="aw">
		<h2>Average Wait</h2>
		<h1><%=RS("ConvAvgWaitDuration")%></h1>
	</div>
	<div id="lw">
		<h2>Longest Wait</h2>
		<h1><%=RS("ConvLongestWaitDuration")%></h1>
	</div>
<div class="clearfix"></div>	
	<div id="ta">
		<h2>Talking Agents</h2>
		<h1><%=RS("TalkingAgents")%></h1>
	</div>
	<div id="oc">
		<h2>Oldest Contact</h2>
		<h1><%=RS("ConvOldestContact")%></h1>
	</div>
	<div id="ch">
		<h2>Calls Handled</h2>
		<h1><%=RS("CallsHandled")%></h1>
	</div>
<div class="clearfix"></div>
	<div id="cw">
		<h2>Calls Waiting</h2>
		<h1><%=RS("CallsWaiting")%></h1>
	</div>
	<div id="tm">
		<h2 class="csqname"><%=RS("csqname")%></h2>
		<h1>
			<% 
				sOffset = +0 'The time offset from GMT at the server 
				cTime = i + sOffset 
				tDiff = (DateAdd("h",cTime,Now)) 
				Response.write FormatDateTime(tDiff,3)
			%>
		</h1>
	</div>
	<div id="ca">
		<h2>Calls Abandoned</h2>
		<h1><%=RS("CallsAbandoned")%></h1>
	</div>	
<div class="clearfix"></div>
</div>
		<!-- End of Main Content Div -->
	</div>
	<!-- End of Container Div -->
</body>		
<%
RS.Close
cn.Close
%>
</html>

This will now give you a very simple wallboard using old technologies but works ok. I will at some point in the future be changing all of this to use c# and aspx files so will post an update when this has been completed.

Hope you found this useful.

About these ads