Web-based CRM Module - Telephony Integration

[red1]

We now have a highly exciting project that uses the Telephone at its core!

When a caller calls into the office PABX System, it shall trigger the Flash Operator Panel (from www.asternic.org) to call up Compiere onto the PC so that we know who is calling and all the customers' relevant data is at our fingertips.

Naturally we interface with the web-based CRM module of Compiere. But not just yet. We will use the more familiar Mecca framework (from www.sf.net/projects/university) to be the opening callup screen and it drills into the DB tables particularly C_BPartner, Historical tables and R_Request tables that CRM mainly sits on.

Among the first tasks is to study Compiere's CRM Module. This workshop is to walk thru that and expose its inner workings especially when we encounter stuff we can't figure out.

This is a project brainchild of a private firm that controls the copyright and commercial ownership. Tutorials here are just workshop parts on related Compiere issues that are already Open Source.

BEGINNING OF PART I - MAKING A REQUEST

When Compiere is run from the web, it opens the following screen. http://compiere.red1.org/FirstScreen.htm . At the Self Service row, there is a Send Request link. Where u can see i have put in 2 requests. Now if u go into your favorite DB Editor or TOAD u look into the R_Request table u will find the requests u made.

But when i login into the Compiere webstart i do not find uch requests in the Requests panel at the bottom of the dialog box. Thus this becomes our first journey into debuging the issue and hopefully we learn alot from it.

PART II - DEBUGGING REQUEST PROMPT MYSTERY

Using Eclipse upon the source codes, click on the Search icon and search for R_Request among the *.java. U will hit 464 instances. And the very first java that has such a reference is AMenu.java which is exactly what we need to look into cos it must be the opening box that we are interested in. Upon peering inside sure enuf we see a telling clue in this snipper

Code: Select all

   /**
    *  Ger Number of open Requests
    *  @return number of requests
    */
   private int getRequests()
   {
      int retValue = 0;
      if (m_requestSQL == null)
         m_requestSQL = MRole.getDefault().addAccessSQL ("SELECT COUNT(*) FROM R_Request "
            + "WHERE (SalesRep_ID=? OR AD_Role_ID=?) AND Processed='N'"
            + " AND (DateNextAction IS NULL OR TRUNC(DateNextAction) <= TRUNC(SysDate))"
            + " AND (R_Status_ID IS NULL OR R_Status_ID IN (SELECT R_Status_ID FROM R_Status WHERE IsClosed='N'))",
               "R_Request", false, true);   //   not qualified - RW
      try
      {
         PreparedStatement pstmt = DB.prepareStatement(m_requestSQL, null);
         pstmt.setInt(1, m_AD_User_ID);
         pstmt.setInt(2, m_AD_Role_ID);

Just put a break somewhere after the SQL forming and run in debug mode. Before the dialog panel can open fully, it shall stop to give us its SQL string. Put that string in TOAD, and it asks for

Code: Select all

SELECT * FROM R_Request WHERE (SalesRep_ID=? OR AD_Role_ID=?)
AND Processed='N' AND (DateNextAction IS NULL OR TRUNC(DateNextAction) <= TRUNC
(SysDate)) AND (R_Status_ID IS NULL OR R_Status_ID IN (SELECT R_Status_ID FROM R_Status
WHERE IsClosed='N')) AND AD_Client_ID=11 AND AD_Org_ID IN(0,11,12)

Notice there are two questions marks in the stmt. Replace them with the values that u obtain by mouseover the setInt part of the last portion of the snip above. Here it is again

Code: Select all

         pstmt.setInt(1, m_AD_User_ID);
         pstmt.setInt(2, m_AD_Role_ID);

Eclipse shows the values of AD_User_ID and AD_Role_ID that in my case is 100 and 102, and that is the cause of the problem. When run, it returns no rows. When that Where part is taken off, the requests are returned. Thus we have to understand what is the business context such a where stmt is made.

First we trace who is SalesRep_ID = 100.

(to be cont'd)

[red1]

Mystery Solved!

It seems that AD_User_ID = 100 means that i have login into the WebStart as SuperUser. Clue from AD_User table.

And examining the R_Request table shows that the SalesRep ID field has 101 as the value, which is the GardenUser login account. This means that
1) U can view this only when u login as GardenUser.
2) A Request will assign someone to handle each request.
3) That someone must be a Sales Rep
4) That someone is assigned in Compiere setup of Request Processer > Routing where in the HELP Screen it explains more

Define the sequence of request types and keywords and to whom the web and mail requests should be routed to.

That is a wow! That means that this CRM can filter and smartly route requests to the best person according to what is in the request text.

Thus its best that our mecca screen call-in have to somehow call up the Compiere request screen for the receptionist to keyin such requests on behalf of the caller.

[red1]

Now we are able to login via URL bypassing the usual Login Screen.

The URL we send is like

Code: Select all

http://localhost/wstore/loginServlet?mode=Login&EMail=red1@red1.org&Password=red1

We are able to do so cos we inserted the Login logic part from doPost into doGet of serverApps/org.compiere/wstore/LoginServlet.java:

Code: Select all

      if ("Login".equals(mode))
      {
         //   Get Base Info
         Properties ctx = JSPEnv.getCtx(request);
         String email = WebUtil.getParameter (request, "EMail");
         if (email == null)
            email = "";
         email = email.trim();
         String password = WebUtil.getParameter (request, "Password");
         if (password == null)
            password = "";   //   null loads w/o check
         password = password.trim();

         log.info("** login " + email + "/" + password);
         //   add Cookie
         JSPEnv.addCookieWebUser(request, response, email);

         //   Always re-query
         WebUser wu = WebUser.get (ctx, email, password, false);
         wu.login(password);
         //   Password valid
         if (wu.isLoggedIn())
         {
            if (url.equals(LOGIN_JSP))
               url = "/index.jsp";
            //   Create Session with User ID
            MSession cSession = MSession.get (ctx, request.getRemoteAddr(),
               request.getRemoteHost(), session.getId());
            if (cSession != null)
               cSession.setWebStoreSession(true);
         }
         else
         {
            url = LOGIN_JSP;
            log.fine("- PasswordMessage=" + wu.getPasswordMessage());
         }
         session.setAttribute (Info.NAME, new Info (ctx, wu));
      }   //   Login

We send the URL from the Dashboard page called by the FOP.
The Dashboard page gives first light info such as
1) CLI of calling number
2) Existence of C_BPartner record containing that phone number.
3) Gives the last request history by existing AD_User
4) Pull its email_id login and password for the above URL

It also does:
5) Allow tagging to another User if caller is using another number instead of registered number.
6) Creating of tagging file to store (5)
7) Forming the URL string for bypassing into LoginServlet
7) Button to direct the URL string to HTTP

[red1]

Basic Dashboard is done here http://compiere.red1.org/CLIdash.htm
However the previous coding on calling loginServlet.doPost is wrong.
After some research and YFaizal's assisting, i found the simple param passing without changing a code in Compiere.

Code: Select all

<input name="button" type="button" onClick='loginServlet()' value="LOGIN CRM">
<input type="hidden" name="command">
<input type="hidden" name="EMail" value="$email">
<input type="hidden" name="Password" value="$password">
<input type="hidden" name="Mode" value="Login">
</form>
<script>
function loginServlet() {
   document.f.action = "http://localhost/wstore/loginServlet";
   document.f.submit();   
}

[red1]

Detected that the keyword filtering of Request Processor abruptly stopped working. Have tried with different keywords assigned to different Employee/SalesReps to handle some Requests but they became u/a. Suspect that we miss some config settings and not necessary bugs.
This leads me to trace the source in RequestServlet.java...
We look for how Compiere gets the SalesRep to handle the Request and after musing around till MRequest.java we found a spot to start looking:

Code: Select all

   public MUser getSalesRep()
   {
      if (getSalesRep_ID() == 0)
         return null;
      return MUser.get(getCtx(), getSalesRep_ID());
   }   //   getSalesRep

Which obviously doesnt do much except return the SalesRep assigned to that customer in the first place.
Thus we need to explore if this is entirely so, and if so, have to amend this part to incorporate the keyword matching.
We do this checking via Eclipse's Open Call Hierarchy. This eventually leads us to the elusive but fitting source in RequestProcessor.java (which its namesake is self-explanatory as the Compiere window has the same where keywords are handled)

Code: Select all

   /**
    *  Find SalesRep/User based on Request Type and Question.
    *  @param request request
    *  @return SalesRep_ID user
    */
   private int findSalesRep (MRequest request)
   {
      String QText = request.getSummary();
      if (QText == null)
         QText = "";
      else
         QText = QText.toUpperCase();
      //
      MRequestProcessorRoute[] routes = m_model.getRoutes(false);
      for (int i = 0; i < routes.length; i++)
      {
         MRequestProcessorRoute route = routes[i];
         
         //   Match first on Request Type
         if (request.getR_RequestType_ID() == route.getR_RequestType_ID()
            && route.getR_RequestType_ID() != 0)
            return route.getAD_User_ID();
         
         //   Match on element of keyword
         String keyword = route.getKeyword();
         if (keyword != null)
         {
            StringTokenizer st = new StringTokenizer(keyword.toUpperCase(), " ,;\t\n\r\f");
            while (st.hasMoreElements())
            {
               if (QText.indexOf(st.nextToken()) != -1)
                  return route.getAD_User_ID();
            }
         }
      }   //   for all routes

      return m_model.getSupervisor_ID();
   }   //  findSalesRep

As seen from the comments, its clear that we stumbled upon the right snips here. We can then place a break here and do a RemoteDebug to see if this indeed get called and returns the right SalesRep assignment.

From closer examination of the code above, we can say that the issue been resolved. It seems that in order for keyword matchings to work, we must leave the RequestType selection blank. This is fine cos the user has the choice of defining RequestTypes to filter instead.

(note: Another example of Compiere User Manual poor editing that shuld have made clear such selection constraints and saved us the jitters of having the Bible but still remaining unforgiven, unless u go thru your own hell. )

We again worship IBM's Eclipse profusely for allowing us to trace the elusive Java source, as Compiere's RequestServlet did not contain such a reference in the first place, and Eclipse' Call Hierarchy gave away the shadows of it. This is because Compiere abstracted using the Thread framework:

Code: Select all

public abstract class CompiereServer extends Thread
{
   /**
    *    Create New Server Thead
    *   @param model model
    *   @return server tread or null
    */
   public static CompiereServer create (CompiereProcessor model)
   {
      if (model instanceof MRequestProcessor)
         return new RequestProcessor ((MRequestProcessor)model);

So here is the mantra:

Compiere + Eclipse + RED1 Workshop = FREEDOM!

[red1]

One important associated module for the CRM is the Assets component of ERP.

Usually when customers call they refer to their Assets that the Client sold them.

a) Whether its been issued and its serial number and related info.

b) After Sales maintenance, where an Asset is maintained and keeping record of it

c) During Fault Reporting, the Asset is again refered to and follow up actions logged for it.

[red1]

The Assets do work and appears in the CRM window. The system will send a validation code to the User before able to view its Assets.

In the CRM, during replying to requests, the service personnel may enter what spare parts are used, the Qty spent and the Qty to be invoiced to the customer. However the Request(All) window allows only one product to be selected.

Then a Invoice Requests Process will generate Invoices based on that Qty provided the whole request ticket is closed and the Request IsInvoice box is checked.

==============================================

Another important aspect for Call Centre Help Desk is that the CRM must handle
a) Predictive Call Out
b) Script for Reading to the Caller, based on the type of call or caller.
c) Knowledge based system can pop up also a script for the reader to say based on the same key words or RequestType of the request as it is been entered.

[red1]

The system can now do predictive callout from the local extension the user is at. Here is a CRM Analysis screen for the management of the CRM http://compiere.red1.org/crm_analysis.mht
It gives all the Requesters in the pull down and all the Service personnel that has request in the Assigned pull down. This way at a single view, the users know all there is to know happening in the CRM.

[wadie]

In client account, we have to setup email address of the client so that email function can run properly. This is the example setting.

Mail Host : smtp.yourcompany.com
Request Email : you@yourcompany.com
Request User : you@yourcompany.com
SMTP Authentication : Checked
Model Validation Classes : compiere.model.MyValidator

Test your email setting.....

[bluefox]

Thanks red1.

Am sorry ....am new here. So excuse me if the questions are silly

1. Is Asterisk interface now available to download and use? Is it free or commercial?
2. Am I right in thinking that it is working fine now?
3. What would be very useful is when we receive a call, especially to the Helpdesk, the customer enters their account number or their telephone number and then a new Request gets opened with the BP's name, Location populated. Is that possible?

Many thanks

Regards
BlueFox

[red1]

Info u requested is stated at beginning of this thread. U have to setup the IP PBX box from www.asternic.org.

What our module here does is to take the CLI from that box and automatically call up the Dashboard info and allow auto login to CRM of the Client as shown in the screenshots above.

We have committed this CLI to Crm Erp portion open source, thats why we call it Cli-CE, which is my project focus.

I cannot provide a full end to end as yet. However asterisk from asternic.org has all the setup info and i need not cater for it thus.

The Cli-CE can also work independently from IP-PBX, where u just key in the phone number and it does the same.

When integrated to IP-PBX (getting CLI and CallBack functions of Asterisk) this module can magically call up CRM and call back the contact numbers displayed in dashboard, thru the local IP Phone or SoftPhone without keying numbers.

This project is been moved to www.adempiereforge.org which shall come up within these few days as its own ADempiereForge.

Pls direct further queries to the Project Forum of http://sf.net/projects/adempiere or at the project thread https://sourceforge.net/forum/forum.php ... _id=610548

There are also other ADempiereans involved in IP-PBX and i have invited them to collaborate and contribute to Open Source.

thanking you very much for your query,
best regards
red1
"Community is Power"