Updated NetCenter Screenshots

Ok, no reams of code in this post, just some recent screenshots of NetCenter, an ajax rich jquery/Grails based CRM I've been working on. Most of the icons below come from the CrystalClear icon set on wikimedia.

This first shot shows our TODO manager rollup/down side bar:


And the asset management module:


Who are those cute kids ;-) ?

And finally, the document management accordion panel for an account:


Seeing the product live is far more impressive - new tab load speed, yahoo map popups, click to call - but hopefully these screenshots give you a sense of the general UI layout of NetCenter. This is really the first time I've down a tab oriented layout but I thought it would be the best design for a web-based CRM solution where you are jumping around alot, with multiple ways to get to the same information, but don't want to lose your place.

Document Management in NetCenter

Although our mid to long term plans for NetCenter365 include Sharepoint and Alfresco integration, we currently provide a more streamlined, account oriented, document management capability within NetCenter that we think might better serve some organizations.

Documents in NetCenter are attached to customer records or accounts. Here's a screenshot:


On the backend, I created a C++/FUSE based filesystem. When you mount it you see a list of customer names as directories, under which documents attached to the accounts are found. This metadata is stored in the NetCenter database while the actual file contents are simply stored in a backing ext3 filesystem. This way it's easy to backup and restore, replicate, etc. Here's a snippet from account_node::readdir()

 int account_node::readdir(void *buf, fuse_fill_dir_t filler, off_t offset, struct fuse_file_info *fi)
 {
  filler(buf, ".", NULL, 0);
  filler(buf, "..", NULL, 0);

  pqxx::connection db(connect_string());
  pqxx::nontransaction work(db);
  pqxx::result result = work.exec("SELECT name,id,trunc(date_part('epoch',last_updated)),path FROM document where account_id=" + id());

  std::string did; long lctm; std::string rpath;
  for (pqxx::result::const_iterator r = result.begin(); r != result.end(); ++r)
  {
   filler(buf, r[0].c_str(), NULL, 0);
   did = r[1].c_str();
   r[2].to(lctm);
   rpath = r[3].c_str();

   std::string path = _path + "/" + r[0].c_str();
   _filesystem->set_attributes(path, attributes(did, lctm, rpath));
  }

  return 0;
 }

Whereas the code to read the actual file contents, looks something like this:

 int poi_node::open(struct fuse_file_info *fi)
 {
  std::string fpath = full_path();

  int res = ::open(fpath.c_str(), fi->flags);
  if (res == -1)
   return -errno;

  ::close(res);
  return 0;
 }

With the virtual filesystem mounted, we simply serve it up via Apache webdav and since we store the document metadata in the NetCenter database it's very easy to provide the frontend UI via grails.

As far as the frontend goes, one big complaint we've heard about other document management solutions is how confusing it is for some users to download a file, find it on their hard drive, edit it, go back to their browser, and upload a new version. That's a very frustrating set of steps for many users.

We built a very simple JetPack based extension for Firefox that registers a "webdav://" protocol handler that passes off such links to OpenOffice which already knows how to handle them properly such that there is no downloading, finding, editing, and re-uploading. OpenOffice will directly save the document back to our Apache webdav server that sits on top of the NetCenter virtual filesystem discussed above.

For Internet Explorer, we wrote a small C# based protocol handler that does almost the same thing but handles Microsoft Word or OpenOffice. Not quite as nice as the Firefox solution, but we can push out the MSI via AD group policy.

Grails, jQuery, and Yahoo Maps

I recently completed a new NetCenter365 feature that uses Yahoo Maps to show the location of all current customers. Here's a screenshot:


I really appreciate Yahoo's "Maps Web Services" which include a helpful geolocation service.

First, we map out HQ with:

 var map = new YMap(document.getElementById('map'));
 map.addTypeControl(); map.addZoomLong(); map.addPanControl();
 map.setMapType(YAHOO_MAP_REG);

 var hq = new YGeoPoint(HQ.latitude, HQ.longitude); 
 map.drawZoomAndCenter(hq, 11); 

Then we use grails and jquery to loop through every customer and fire off the following ajax requests:

 var url = '${createLink(controller: "location", action: "latlong")}' + "/";

 <g:each var="account" in="${accounts}">
  $.getJSON(url + ${account.id}, function(x) { 
    var pt = new YGeoPoint(x.latitude, x.longitude); 
    var m = new YMarker(pt); 
    m.addAutoExpand('${account.name.encodeAsJavaScript()}');
    map.addOverlay(m); 
  });
 </g:each>

The heart of the location/latlong method uses Yahoo's geolocation services. Here's a snippet of the groovy code:

 def geocoder = "http://local.yahooapis.com/MapsService/V1/geocode?appid=${APPID}"
 if (account.line1) geocoder += "&street=" + URLEncoder.encode(account.line1);
 if (account.city) geocoder += "&city=" + URLEncoder.encode(account.city);
 if (account.state) geocoder += "&state=" + account.state;
 if (account.zip) geocoder += "&zip=" + account.zip;

 def xml = geocoder.toURL().text
 def records = new XmlParser().parseText(xml);
 location.latitude = records.Result[0].Latitude.text()
 location.longitude = records.Result[0].Longitude.text()

Performance wise, the map pops up quite quickly and the markers appear in rapid procession. This is aided by caching Lat/Long info to minimize geolocation requests.

CRM Integration via LDAP

Our vision for NetCenter is to facilitate and drive a customer centric view of day to day activities within an organization. Whether you're in sales, engineering, administration, or elsewhere, we want to help organize your documents, emails, phone calls, projects, and other day to day activities in a customer centric way.

We also want a platform that's easy to use and integrates well into existing business systems.

As part of this effort, I recently completed exposing NetCenter contacts to mail clients like Zimbra, Outlook, and Thunderbird via a custom OpenLDAP backend.

All of these mail clients can leverage LDAP based address books, so we expose NetCenter contacts via LDAP so that you can quickly and easily send emails to prospective and current customers. Here's a screenshot from Outlook:


And Zimbra:


There's no real documentation on how to create a custom backend, but the back-null and back-shell backends are pretty good places to start.

Incoming call screen pops with sipX, rabbitMQ, and Adobe Air

I just finished the first beta of NetCenterPlus, an Adobe Air html based tray application that presents screen pops for incoming calls on sipX systems. NetCenterPlus is part of NetCenter, a CRM/Business Productivity solution from NetServe365.

Here's a screenshot of the notification window on an incoming call.

On the backend, I implemented a solution very similar to the one I did for Integrating sipx with ejabberd. There are two database triggers installed into the SIPXCDR database, the second of which is a PostgreSQL plperlu trigger which uses Net::Stomp to send a message to our rabbitMQ server indicating the callerId of an incoming call to the user registered for the destination extension. Not many of lines of code:

CREATE FUNCTION cse_ncplus_change() RETURNS trigger AS $end$
use Net::Stomp;

my ($domain, $uid, $pwd) = @{$_TD->{args}};
my $msg = TD->{"new"}{"from_id"};

my $stomp = Net::Stomp->new({hostname=>'mq.nvizn.com', port=>'61613'});
$stomp->connect({login=>$uid, passcode=>$pwd});

my $uid = $_TD->{"new"}{"username"};
$stomp->send({destination=>"/$domain/ncplus/$uid",
       body=>($msg)});

$stomp->disconnect;
return undef;
$end$
LANGUAGE plperlu;

The other plpgsql trigger looks up the destination extension and munges up a nice looking incoming call number. That exercise is left to the reader.

Now we've got a message on a per-user queue for every incoming call on our sipX system. So what next?

I wanted an easy to deploy, cross platform, tray application that would listen for incoming messages on present the screen pop. I looked at Mozilla Prism, Silverlight, and Adobe Air. Air was not my first choice to be honest, but the Prism project seems to have stagnated afaict, and Silverlight 2.0 on Linux doesn't look like it will be out anytime soon, so I went with Air. After spending some time with the product, I've definitely grown in my appreciation of its ease of use and design. It's really nice to be able to leverage existing web development skills to build these type of applications.

So what does the Air application do? First of it, I used air.Socket and javascript to implement a STOMP client.

First the connection code:

air.trace("setting up MessageQueue...");
  this.socket = new air.Socket();
  var self = this;

this.socket.addEventListener(air.Event.CONNECT, function(event) {
    self.sendCommand("CONNECT\nlogin:guest\npasscode:" + password + "\n\n");
    self.state = self.STATE.CONNECT;
  });

The main listener loop looks something like this:

this.socket.addEventListener(air.ProgressEvent.SOCKET_DATA,
                               function(event) {
                                                                              
    switch (self.state) {
    case self.STATE.CONNECT:
      self.subscribe();
      break;
    case self.STATE.READY:
      var data = event.target.readUTFBytes(event.target.bytesAvailable);
      var lines = data.split("\n");
      if (lines[0] == "MESSAGE" && lines.length>5) {
        msg_callback(lines[6]);
      }
      break;
    }
  });

So a NetCenterPlus user installs the application via a web page (yet to be prettied up!).



Then, the user enters their NetCenter username and password (again, this dialog needs some UI love. Did I mention I'm not a graphic artist?):



You can read and write to a local encrypted store in Air via functions like this:

readFromLocalStore = function(key, defstr) {                                
  var item = air.EncryptedLocalStore.getItem(key);                            
  if (item == null) return defstr;                                            
  return item.readUTFBytes(item.length);                                      
  }                                                                           
                                                                              
saveToLocalStore = function(key, value) {                                   
  var bytes = new air.ByteArray();                                            
  bytes.writeUTFBytes(value);                                                 
  air.EncryptedLocalStore.setItem(key, bytes);                                
  } 

When NetCenterPlus receives an incoming screen pop, we use the DOM to set the incoming call caller id, then we do an authenticated HTTP GET on the NetCenter REST based API to lookup the contact's name. The code looks something like this:

    var cpnum = this.document.getElementById("callpop_number");
    cpnum.innerHTML = fnum;

    var url = "http://" + server + "/api/contact/byPhone/" + callnum;
    var request = new air.URLRequest(url);

    var loader = new air.URLLoader();
    var self = this; var loader_sucess = true;
                                                                              
    loader.addEventListener(air.IOErrorEvent.IO_ERROR, function(error) {
        air.trace("Failed to load: " + url);
    });

    loader.addEventListener(air.Event.COMPLETE, function(event) {
 var data = new air.URLVariables(event.target.data);
        var cpname = self.document.getElementById("callpop_name");
 cpname.innerHTML = data.name;
    });

You setup the login credentials in Air, with a single line of code:

air.URLRequestDefaults.setLoginCredentialsForHost(this.server, username, password);

The NetCenter CRM is a Grails application that exposes a REST based api via basic authentication tied into Active Directory. To set this up, I added the following lines to grails-app/conf/Config.groovy:

jsecurity.filter.config = """                                                 
                                                                              
[filters]                                                                     
authcBasic = org.jsecurity.web.filter.authc.BasicHttpAuthenticationFilter     
authcBasic.applicationName = NetCenter API                                    
                                                                              
[urls]                                                                        
/api/** = authcBasic                                                          
"""

The contact controller "byPhone" method that the Air application uses is a very simple:

    def byPhone = {
      def contacts = Contact.withCriteria {
        eq('active', true)
        eq('licensee.id', session.lid)
        or {
          eq('workPhone', params.id)
          eq('homePhone', params.id)
          eq('cellPhone', params.id)
        }
      }                                                                       

      return [ 'contacts': contacts ]
    }

Well that about describes how all these parts come together. We plan on adding a lot more functionality to the NetCenterPlus Air application and thus far I'm pretty pleased with the Air platform.

NetCenter Click to Call

I just completed adding "Click to Call" functionality to NetCenter. Since this is a bit difficult to demonstrate with screenshots, I made a YouTube video instead.



I implemented "Click to Call" using Aloha and RabbitMQ, testing the solution on sipX.

I have a grails PlaceCallService that sets up a connection to our RabbitMQ instance like this:

  static transactional = false;
  ConnectionParameters connectionParameters;
  ConnectionFactory connectionFactory;
  ConfigObject config = ConfigurationHolder.config;

  MessageQueueService() {
    connectionParameters = new ConnectionParameters();
    connectionParameters.setUsername(config.mq.username);
    connectionParameters.setPassword(config.mq.password);
    connectionFactory = new ConnectionFactory(connectionParameters);
  }

The actual message publish function looks something like this:

  def publish(message) {
    try {
      Connection conn = connectionFactory.newConnection(config.mq.host,
                                                        AMQP.PROTOCOL.PORT);
      Channel ch = conn.createChannel();

      ch.queueDeclare(config.placeCall.routingKey);
      ch.basicPublish("", config.placeCall.routingKey, null,
                      message.getBytes());                                    

      ch.close();
      conn.close();
    }
    catch (Exception e) {
      log.error("Main thread caught exception: " + e);
      return false
    }

    return true
  }

Then in a "Third Party call initiator daemon", I unpack the message and use the Aloha stack to do a

        try {
            OutboundCallLegBean outboundCallLegBean = (OutboundCallLegBean)
                applicationContext.getBean("outboundCallLegBean");
            CallBean callBean = (CallBean)
                applicationContext.getBean("callBean");
            callBean.addCallListener(this);

            // create two call legs                                           
            String callLegId1 =
                outboundCallLegBean.createCallLeg(URI.create(callee),
                                                  URI.create(caller));
            String callLegId2 =
                outboundCallLegBean.createCallLeg(URI.create(caller),
                                                  URI.create(callee));

            // join the call legs                                             
            System.out.println(String.format("connecting %s and %s in call...$
            System.out.println(callBean.joinCallLegs(callLegId1, callLegId2))$
        }

This chunk of code is based on the helpful Third Party Call sample from Aloha's subversion repository.

Anyway, it's working well, consumes minimal resources on the web server (just post message to the "/placeCall/request" queue), and only took a few days to setup and deploy into production. Many thanks to the Aloha team, RabbitMQ folks, and sipX gurus.

NetCenter CRM

For the last month or so, I've been working on "NetCenter" a Grails 1.1 based CRM system that will integrate with sipX for call detail records, Zimbra or Exchange 2007 for email, calendaring, and time tracking purposes, and finally Alfresco or Sharepoint for document management.

I've really enjoyed using Grails - its a real productivity booster and I really appreciate the Separation of concerns you get with an MVC framework.

I completed the sipX integration first and am now working with Exchange 2007 Web Services so that users can associate meetings with accounts and mark them billable/non-billable.

First a few screenshots, then a brief overview of the sipx integration. Note: in the screenshots below the account and contact information is randomly generated test data, while the call records are real records coming out of our production sipX server.

Call Manager:


Account Calls:


Contact Calls:


I used the Grails Quartz Plugin and added a grails-app/jobs/CdrSyncJob.groovy that looks at licensees with registered sipX servers and then queries with sipX instance for call detail records that have not yet been processed.

I wanted call detail report generation to be as fast as possible, so the CdrSyncJob looks up the sipX callee and caller phone numbers against the contact table and licencedUser table then writes a new "call" record into the NetCenter database and marks the sipX call record has having been processed so it can be ignored the next time the job runs. Now whenever anyone wants to view all calls made to any contact within a certain account, its a simple database query that has a few joins and doesn't involve any phone number normalization, determining whether a call is related to any known contact, ignoring interoffice calls, or figuring out the call direction.

Here a few snippets for CdrSyncJob. First the execute() method:

def execute() {

  if (Environment.current == Environment.DEVELOPMENT) return
  def licensees = Licensee.withCriteria {
    eq("active", true)
    isNotNull("sipHost")
  }

  licensees.each { syncCdrs(it); }
}

Then syncCdrs begins with some Groovy SQL like this:

   def cdr = Sql.newInstance("jdbc:postgresql://${licensee.sipHost}/SIPXCDR", "username", "password", "org.postgresql.Driver")
                                                   cdr.eachRow("select * from view_call_records A, cdrs_sync B where A.id=B.id and NOT(B.done)")

Hmmm, I guess I should point out that view_call_records and cdrs_sync are custom tables. Here's the SQL:

CREATE VIEW view_call_records as
  select id, SUBSTRING(caller_aor FROM '.*.*') as caller,
 LTRIM(LTRIM(SUBSTRING(callee_aor FROM '.*.*'), '8'), '1') as callee,
  connect_time as start_time,
 to_char(cdrs.end_time-cdrs.connect_time, 'MI') AS minutes, 
 to_char(cdrs.end_time-cdrs.connect_time, 'SS') as seconds
  from cdrs where cdrs.termination != 'F' and cdrs.connect_time IS NOT NULL;

CREATE TABLE cdrs_sync (
    id integer PRIMARY KEY,
    done boolean DEFAULT FALSE
);

Anyway, the rest of syncCdrs is just about ignoring interoffice calls or calls to contacts with don't have on record, then adding new entries to the NetCenter call table:

new Call(callDirection: direction, callId: it.id, contact: contact, dateStarted: it.start_time, minutes: it.minutes, seconds: it.seconds, licensee: licensee, owner: owner).save();

and marking the call as processed in the cdrs_sync table.

Next time I get a chance to blog, I hope to show the Exchange integration and some jQuery snippets. jQuery has been a big productivity booster as well. Web development has come along way!

Integrating sipX with ejabberd

I recently completed integrating our sipX based voip platform with our ejabberd XMPP server, so that users can see when others are on the phone or not. There are alot of similar integrations that people have done with Asterisk using their AMI api, but I haven't found anything similar for sipX yet, so we rolled our own for now. While, it's not terribly exciting, here's a screenshot of what it looks like when someone is on the phone:


The solution I came up with involves 3 parts. First, I setup a clustered RabbitMQ server (an open source implementation of AMQP). I plan on using it to facilitate a loosely coupled, event driven architecture for integrating multiple open source
applications. I'm pretty happy with RabbitMQ thus far - about the only complaint I have is that they don't have any message tracing capabilities right now (version 1.5.0) which made it more difficult to debug my client side code. I'm also hoping that sometime soon we start seeing debian packages for python/perl amqp libraries. For now, I'm using Net::Stomp and the RabbitMQ stomp adapter which seemed like the most stable, easily deployed client side solution.

On the XMPP server side, I created an erlang module that acts as a message consumer. Each virtual host in our ejabberd server listens on a separate queue for presence messages generated by the sipX side and sends out XMPP presence updates to online sessions.

After getting the RabbitMQ erlang client library installed, here's the code I used to connect and setup my consumer:

Connection = amqp_connection:start(Uname, Pwd, "mq.nvizn.com"),
Channel = amqp_connection:open_channel(Connection),                                
Qname = list_to_binary("/" ++ Host ++ "/presence/phone"),
Q = lib_amqp:declare_queue(Channel, Qname),
lib_amqp:bind_queue(Channel, <<"">>, Q, Qname),
lib_amqp:subscribe(Channel, Q, self(), false),

Then I created a handle_info function that looks like this:

handle_info({ {'basic.deliver', DeliveryTag, _, _, _, _ },
              {content, ClassId, Properties, PropertiesBin,
              [Payload]} = Info}, State) ->

%% Message processing here, then send out the XMPP presence update...,
BroadcastPresence = fun({U, S, R}) ->
    Dest = jlib:make_jid(U, S, R),
    ejabberd_router:route(FromJID, Dest, Presence)
  end,                                                                             
Sessions = ejabberd_sm:get_vh_session_list(State#state.host),
lists:foreach(BroadcastPresence, Sessions),

Now on the sipX side, things are a bit more ugly, and when I have more time later, I'd like to rework this end. For now, I created a PL/pgSQL AFTER trigger on SIPXCDR.call_state_events table that handles new call state events ('S' and 'E' event_types to be specific). This trigger inserts new rows into a new cse_summary table I created for every call, one for when the call is setup and one for call termination and it does this for each internal user. If the call involves two internal folks, you end up with 4 rows, if on the other hand, one side is external, you end up with only 2 rows. This trigger also looks up the XMPP jid for the extension and records that in the generated cse_summary rows.

When a row is created in the cse_summary table, a separate
PL/Perl AFTER trigger uses Net::Stomp to generate a call state
event message for the RabbitMQ cluster.

Here's what the PL/Perl trigger looks like:

my $stomp = Net::Stomp->new({hostname=>'mq.nvizn.com',port=>'61613'});
$stomp->connect({login=>$uid, passcode=>$pwd});

my $msg = sprintf("%s,%s,%s", $domain,                                             
$_TD->{"new"}{"event_type"}, $_TD->{"new"}{"jid"});

$stomp->send({destination=>"/$domain/presence/phone", body=>($msg)});
$stomp->disconnect;

Now, I'm just creating some debian packages and RPMs (for the sipX side), documenting how it works, and thinking about our next integration.