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!