Praneet Tiwari: Praneet's blogs

Accessing ADempiere through Web Services

Adempiere is an enterprise application. It’s quite natural that ADempiere would be deployed in an environment where a lot of legacy applications of heterogeneous nature exist. In such an environment it becomes imperative that Adempiere expose web services so that other programs are able to access information from ADempiere. In this blog, I am going to show a extensible way of coding Web Services for Adempiere.

What are we going to achieve?

 To set the expectations straight, let me explain what are we going to accomplish. If you follow the steps mentioned below, you will be able to programmatically access all the business objects and functionalities that lie under the Menu tab shown in the image below -

I'd imagine that the most important and interesting part of Adempiere is this Menu tab; I am afraid the methodology detailed in this blog may not work for Workflow activities.

Note that the word used here is 'access' business objects. Modifying the business objects such as users is slightly more complicated and will be covered in the later blogs.

Prerequisites

Make sure that you have -

Leveraging Axis2

To develop web services, we are going to use Axis2. If you have no previous experience of creating web services in Netbeans, I'd strongly suggest reading this tutorial. Make sure that you follow all the steps mentioned in the tutorial. Though not a strict requirement, you would benefit by having ADempiere source code running in Glassfish. You may also want to go through this blog.

Patching Axis2

 You need to patch Axis2 to be able to integrate ADempiere and Axis2. There are multiple ways of achieving this, I am outlining one of the possible ways. Download this source distribution and unzip into a folder of your choice. Build ADempiere and copy all jars in ADempiere's lib folder in the lib folder of Axis2 source.

Now build the Axis project and generate axis2.war file. Place this war file ${GLASSFISH-HOME}/${DOMAIN-DIR}/autodeploy. That's all to it. You are now all set to start developing ADempiere web services.

Creating web services project in Netbeans

This is quite simple. Just follow these steps:-

1. Create a new Java project from the File->New Project->Java Application
2. Once the project is created, right click on the project in Projects view and goto the Properties -> Libraries and add all Adempiere jars located in trunk/lib and Axis2 jars located in lib directory of Axis2 source that you had built in the last step.
   
   
3. If you don't have the Axis2 plugin installed, you can familiarize yourself with the installation process by going through this tutorial.
   
4. Now you can start writing your web services code. Right click on the source folder of your Netbeans project and select 'New -> Axis2 service from Java'
   
5. Now follow the steps shown in the images below -
   
   
   
   
   
   
   
   
   
6. You should now see the main Java file open. You can begin writing your web services code in this file. In this example we are going to retrieve list of sales representatives in xml format. Here is the sample code for retrieving sales representative information -
   
   public String getSalesRepInfo(){
           StringBuilder sb = new StringBuilder();
           String TableName = null;
           org.compiere.Adempiere.startup(true);
           Properties props = Env.getCtx();
           props.put("GardenAdmin", "GardenAdmin");
           //user definition
           props.put("#AD_Role_ID", "102");
           props.put("#AD_User_ID", "101");
           Login login = new Login(props);
           KeyNamePair[] roles = null;
   
           roles = login.getRoles("GardenAdmin", "GardenAdmin");
           int AD_Window_ID =293;
   
           int PO_Window_ID =0;//Initialized to 0 for compilation
           int AD_Table_ID =114 ;
           int AD_Menu_ID =0;//Initialized to 0 for compilation
   
           int AD_Record_ID = 0;
           Exception ex = new Exception("what??");
           String sql = "SELECT TableName, AD_Window_ID, PO_Window_ID FROM AD_Table WHERE AD_Table_ID=?";
           try {
               PreparedStatement pstmt = DB.prepareStatement(sql, null);
               pstmt.setInt(1, AD_Table_ID);
               ResultSet rs = pstmt.executeQuery();
               if (rs.next()) {
                   TableName = rs.getString(1);
                   AD_Window_ID = rs.getInt(2);
                   PO_Window_ID = rs.getInt(3);
               }
               rs.close();
               pstmt.close();
           } catch (SQLException e) {
               ex = e;
           }
           // At this point AD_Window_ID must be 293;
   
           try {
               GridWindowVO mWindowVO = GridWindowVO.create(props, 2, AD_Window_ID, AD_Menu_ID);
               if (mWindowVO == null) {
           // throw some exception; eaten it for the time being
                   throw new Exception("mWindowVo is null, check your AD_Window_ID value, it needs to be 293");
               }
               GridWindow mWindow = new GridWindow(mWindowVO);
               GridTab curTab = mWindow.getTab(0);
               curTab.setSingleRow(true);
               //  Query
               if (AD_Record_ID != 0 || AD_Table_ID != 0) { //If Zoom
   
                   mWindow.initTab(curTab.getTabNo());
                   curTab.query(false);
   
                   dateFormat = DisplayType.getDateFormat(DisplayType.Date);
                   dateTimeFormat = DisplayType.getDateFormat(DisplayType.DateTime);
   
                   amountFormat = DisplayType.getNumberFormat(DisplayType.Amount);
                   integerFormat = DisplayType.getNumberFormat(DisplayType.Integer);
                   numberFormat = DisplayType.getNumberFormat(DisplayType.Number);
                   quantityFormat = DisplayType.getNumberFormat(DisplayType.Quantity);
                   int initRowNo = curTab.getCurrentRow();
                   int noFields = curTab.getFieldCount();
   
                   int MAX_LINES = 1000;
                   int lastRow = initRowNo + MAX_LINES;
                   String info = null;
                   String name = "";
                   lastRow = Math.min(lastRow, curTab.getRowCount());
                   sb.append("<salesRepInfo>");
   
                   for (int lineNo = initRowNo; lineNo < lastRow; lineNo++) {
                       sb.append("<salesRep>");
                       //  Row
                       curTab.navigate(lineNo);
                       //  for all columns
                       for (int colNo = 0; colNo < noFields; colNo++) {
   
                           GridField field = curTab.getField(colNo);
                           if (!field.isDisplayed(false)) {
                               continue;
                           }
                           name = field.getHeader();
   
                           //  Get Data - turn to string
                           Object data = curTab.getValue(field.getColumnName());
                           if (data == null) {
                               info = "null";
                           } else {
                               int dt = field.getDisplayType();
                               switch (dt) {
                                   case DisplayType.Date:
                                       info = dateFormat.format(data);
                                       break;
                                   case DisplayType.DateTime:
                                       info = dateTimeFormat.format(data);
                                       break;
                                   case DisplayType.Amount:
                                       info = amountFormat.format(data);
                                       break;
                                   case DisplayType.Number:
                                   case DisplayType.CostPrice:
                                       info = numberFormat.format(data);
                                       break;
                                   case DisplayType.Quantity:
                                       info = quantityFormat.format(data);
                                       break;
                                   case DisplayType.Integer:
                                       info = integerFormat.format(data);
                                       break;
                                   case DisplayType.YesNo:
                                       info = data.toString();
                                       break;
   
                                   default:
                                       if (DisplayType.isLookup(dt)) {
                                           info = field.getLookup().getDisplay(data);
                                       } else {
                                           info = data.toString();
                                       }
                               }
   
                               if (info == null || info.trim().length() == 0) {
                                   info = "---";
   
                               }
                               sb.append("<" + name + ">" + info + "</" + name + ">");
   
   
                           }   //  for all table lines
   
   
                       }
                       sb.append("</salesRep>");
   
                   }
                     sb.append("</salesRepInfo>");
               }
   
           } catch (Exception ee) {
               System.out.println("Exception is " + ee);
               ee.printStackTrace();
           }
           return sb.toString();
       }   
   /** Localized Date format       */
       public static SimpleDateFormat dateFormat = null;
       /** Localized Timestamp format  */
       public static SimpleDateFormat dateTimeFormat = null;
       /** Localized Amount format    */
       public static DecimalFormat amountFormat = null;
       /** Localized Integer format    */
       public static DecimalFormat integerFormat = null;
       /** Localized Number format     */
       public static DecimalFormat numberFormat = null;
       /** Localized Quantity format   */
       public static DecimalFormat quantityFormat = null;
   
   
7. It's easy to see that once you have this basic template ready, you can access other information such as business partner list by merely playing around with AD_Table_ID values. For the ones who are curious what's happening at the backend, this is the query that gets executed - 'SELECT AD_Client_ID,AD_Org_ID,Name,Value,Description,Comments,IsActive,C_BPartner_ID,
   C_BPartner_Location_ID,EMail,Password,UserPIN,Title,Birthday,Phone,Phone2,Fax,
   NotificationType,C_Job_ID,IsFullBPAccess,EMailUser,EMailUserPW,Supervisor_ID,
   LDAPUser,AD_OrgTrx_ID,ConnectionProfile,C_Greeting_ID,EMailVerifyDate,
   LastContact,EMailVerify,LastResult,AD_User_ID,Processing,Created,CreatedBy,
   Updated,UpdatedBy FROM AD_User WHERE AD_User.AD_Client_ID IN(0,11) AND AD_User.AD_Org_ID IN(0,11,12) AND AD_User.AD_User_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 114 AND AD_User_ID <> 101 AND IsActive = 'Y' ) ORDER BY Name'
   
   
   
8. After compiling your code, expand the 'Axis2 Web Services' node, locate your file and say 'Deploy to server'.
   
   
9. Once you have deployed the .aar file and started your server, you can see the output by right clicking and saying 'Test Operation in Browser'
   
   
   
   
   
10. The server here refers to the one whose root directory you gave in the 'Target Location of .aar file'. On deploying the .aar file, you can see the xml formatted output of sales representatives as shown in the image below -
    
    
    

All the XML information is in the ns:return tag. To make a valid xml you need to map the column names containing spaces between words to single word xml tags. The code for this mapping is not shown here but is included in the source code. I'll continue working upon making this project available as a part of Adempiere repository. And with the support of Trifont and Red1, I am sure that check-in will happen pretty soon.

Coming Next: Creating and maintaining sessions for Adempiere Web Services, Modifying Adempiere Business Objects through Web Services