In a previous post I examined how to get access the entity data (basically the ORM layer, as well as the dynamic methods piece) using the Saleslogix assemblies from outside of the web client. Obviously this is vital for unit testing, but also has some interesting application for external application. In this next installment I would like to look at the Groups API. In addition to being useful in unit testing and external application I feel the Groups API is poorly documented so a bit of exploring would help.

First off remember that most of the group access is done via a COM component called GroupTranslator. The goal of that component (which I presume was written in Delphi) is to translate the Group blob stored within the database (in the Plugin table) to an XML description and vice versa. It is not terribly reliable and Sage is notoriously slow about releasing fixes for it, but it is what it is. For the general cases it is probably still better than rolling out your own translator.

Next take a look (with Reflector) at the API offered in the 7.2 client. GroupInfo is the main one – it is full of useful static methods for manipulating the groups. Unfortunately they are not documented and some of them look very buggy so we have to thread carefully when dealing with it. It also has a few instance methods but watch out – it makes heavy use of globals so I would avoid messing too much with several groups at the same time. Another one we have to deal with is GroupContext – this has some information about the group that the web user is currently using (sadly it has a few pitfalls as we will see below). Very often when you use a method to retrieve the group’s data it seems to set the current group in GroupContext (as a global). So watch out for that. Sometimes you have to break down and examine the group’s XML yourself (as returned by the group translator) but I prefer to avoid that – my hope is that eventually the GroupInfo API will be fixed to be more reliable. Here are a few of my favorites:

• GroupInfo.GetGroupIdFromNameFamilyAndType – don’t you hate having to figuring that one out in SQL on the LAN client?
• GroupInfo.GetGroupInfo – static method to build a group info object, knowing the plugin id.

• GroupInfo.GetGroupDataReader – I looked at the code and I am pretty sure this won’t release the connection correctly, so I would stay away from that one for now (too bad, it sounds yummy, and does not have the global reliance of the next one)
• GroupInfo.GetGroupDataTable – almost as good as GetGroupDataReader, and does not have the connection problem. Only works when paging is enabled which can only be done using the last 2 overloads. Be careful if you use those because they will mess with the current (global) group context (not sure what the exact effect would be).
• GroupInfo.GetGroupKeyFieldIDs – not bad to get a group’s data. One of the rare data retrieval methods that doesn’t affect the current global group. Unfortunately this is currently broken so do not use it.
• GroupInfo.GetGroupIDs – I am not sure what the difference in purpose is with the previous one. But anyway, this uses the GetGroupDataReader method, so it will leak connection – do not use.
• GroupInfo.GetGroupList – gets you a list of groups for a specific entity. Watch out retrieving some of the properties like IsAdHoc – some are very very slow. So it will be easier to access the DB directly in most cases I think.
• GroupInfo.AddAdHocGroupMember (and AddAdHocGroupMembers) – to add to an adhoc group (works fine)
• GroupInfo.CreateAdhocGroup – create a new adhoc group (this works well)
• GroupInfo.AddLookupCondition – to add a condition to a dynamic group (didn’t try it but it looks OK)
• GroupInfo.SaveAsNewGroup – save group to database (should work fine)
• GroupInfo.getGroupSQL – this is a private method but I just had to mention it anyway. For example to retrieve the “where” part of the SQL:
  

  MethodInfo method = typeof(GroupInfo).GetMethod("getGroupSQL",
      BindingFlags.Instance | BindingFlags.NonPublic);
  String sql = (String)method.Invoke(currentGroup,
     new object[] { "WHERE", currentGroup.GroupXML, false, 1, 1, null });

  Unlike the GroupInfo.GroupSQL property, this one actually works. The first parameter (where I put “WHERE”) is the part of the SQL that you want to retrieve. You can use WHERE, FROM, SELECT, ORDERBY. WHERE seems to expand all parameters, even things like :UserID. The second parameter is whether you want to do paging or not. Usually false. The next 2 parameters are related to paging, but make sure you do NOT set them to 0. Last parameter is the column to sort by, but this is ignored unless you are using paging. If you use “ALL” as the SQL part, you will get the whole SQL for the group, but none of the parameters won’t be expanded.

• GroupInfo.GetGroupLayoutsNodes – you can use this to get the columns from the group.
• GroupInfo.WhereSQL – SQL condition for the group. Equivalent to getGroupSQL(“WHERE”). Works well.
• GroupInfo.FromSQL – the part after the FROM keyword. Equivalent to getGroupSQL(“FROM”). Works well.
• GroupInfo.GroupSQL – access the actual group SQL (same as in the LAN client). Doesn’t return the condition correctly (always returns it as “1=2″).

As a practical example here is an ugly little wrapper class with a working “GetGroupEntityIds” method:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Sage.SalesLogix.Client.GroupBuilder;
using System.Xml;
using Sage.Platform.Orm;
using System.Data;

namespace SSSWorld.Slx72.Utility
{
    /// <summary>
    /// Helper methods for groups.
    /// </summary>
    public class GroupHelper
    {
        /// <summary>
        /// Return all entity ids on that group.
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public static String[] GetGroupEntityIds(String groupId)
        {
            String sql = GetGroupKeysSQL(groupId);
            using (SessionScopeWrapper session = new SessionScopeWrapper())
            {
                using (IDbCommand command = session.Connection.CreateCommand())
                {
                    command.CommandText = sql;
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        List<String> ids = new List<String>();
                        while (reader.Read())
                        {
                            ids.Add(reader.GetString(0));
                        }
                        return ids.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// Retrieve the full group SQL.
        /// </summary>
        /// <returns></returns>
        public static String GetGroupSQL(String groupId)
        {
            GroupInfo groupInfo = GroupInfo.GetGroupInfo(groupId);
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT ")
                .Append(GetGroupSQLPart(groupInfo, GroupSqlPart.SELECT));
            BuildGroupFromClause(sqlBuilder, groupInfo);
            return sqlBuilder.ToString();
        }

        /// <summary>
        /// Retrieve the SQL appropriate for reading the group entity ids.
        /// </summary>
        /// <returns></returns>
        public static String GetGroupKeysSQL(String groupId)
        {
            GroupInfo groupInfo = GroupInfo.GetGroupInfo(groupId);
            XmlNodeList layoutNodes = groupInfo.GetGroupLayoutNodes();
            XmlElement layoutNode = (XmlElement)layoutNodes[0].ParentNode;
            String mainTable = layoutNode.GetAttribute("maintable");
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT A1.")
                .Append(mainTable)
                .Append("ID ");
            BuildGroupFromClause(sqlBuilder, groupInfo);
            return sqlBuilder.ToString();
        }

        public static String GetGroupSQLPart(GroupInfo groupInfo, GroupSqlPart part)
        {
            MethodInfo getGroupSQL = typeof(GroupInfo).GetMethod("getGroupSQL", BindingFlags.Instance | BindingFlags.NonPublic);
            return (String)getGroupSQL.Invoke(groupInfo, new object[] { part.ToString(), groupInfo.GroupXML, false, 1, 1, null });
        }

        /// <summary>
        /// Which part of the SQL do you want to select
        /// </summary>
        public enum GroupSqlPart
        {
            /// <summary>
            /// After the WHERE (WHERE keyword not included)
            /// </summary>
            WHERE,
            /// <summary>
            /// After the ORDER BY (ORDER BY keyword not included)
            /// </summary>
            ORDERBY,
            /// <summary>
            /// After the SELECT (SELECT keyword not included)
            /// </summary>
            SELECT,
            /// <summary>
            /// After the FROM (FROM keyword not included)
            /// </summary>
            FROM
        }

        #region Private Methods

        /// <summary>
        /// Append the FROM and subsequent clauses to the SQL builder.
        /// </summary>
        /// <param name="sqlBuilder"></param>
        /// <param name="groupInfo"></param>
        private static void BuildGroupFromClause(StringBuilder sqlBuilder, GroupInfo groupInfo)
        {
            sqlBuilder.Append(" FROM ")
                .Append(groupInfo.FromSQL);
            String where = groupInfo.WhereSQL;
            if (!String.IsNullOrEmpty(where))
                sqlBuilder.Append(" WHERE ").Append(where);
            String orderBy = GetGroupSQLPart(groupInfo, GroupSqlPart.ORDERBY);
            if (!String.IsNullOrEmpty(orderBy))
                sqlBuilder.Append(" ORDER BY ").Append(orderBy);

        }

        #endregion
    }
}

Another problem with GroupInfo is that almost all of its methods will want to call GroupContext.GetGroupContext for one reason or another, and GetGroupContext is hard-wired to HttpContext, so not very testing-friendly. This can be fixed in IL though I have not bothered yet.

That’s it for now – this turned out to be a lot harder than I thought it would be. It was actually much harder than on the LAN client because of the poor (or rather, non-existent) documentation and the fact that most of the methods shipped do not actually work. I certainly do not want to turn this post into a rant, but I still have to mention how truly appalling that is. The good side of this coin is that we know the Saleslogix devs are hard at work on the next version and from what I have seen it will probably include a major overhaul of the group interface (and the API, presumably) which might explain why they are not focused on fixing this one. Next installment will be how to get to this stuff from outside of the web client but I thought this short overview of the API warranted a post by itself.