/// <summary>
        ///   Convert Outer Join.
        /// Converting joins can ve very complex when multiple tables/keys are involved.
        ///  The main scenarios supported are two tables with multiple key columns
        ///  and multiple tables with single key columns.
        /// <pre>
        ///      SELECT a.Col1, b.Col2 FROM tableA a, tableB b WHERE a.ID=b.ID(+)
        ///      => SELECT a.Col1, b.Col2 FROM tableA a LEFT OUTER JOIN tableB b ON (a.ID=b.ID)
        ///     SELECT a.Col1, b.Col2 FROM tableA a, tableB b WHERE a.ID(+)=b.ID
        ///      => SELECT a.Col1, b.Col2 FROM tableA a RIGHT OUTER JOIN tableB b ON (a.ID=b.ID)
        ///  Assumptions:
        ///  - No outer joins in sub queries (ignores sub-queries)
        ///  - OR condition ignored (not sure what to do, should not happen)
        ///  Limitations:
        ///  - Parameters for outer joins must be first - as sequence of parameters changes
        ///  </pre>*/
        /// </summary>
        /// <param name="sqlStatement">sqlStatement</param>
        /// <returns>converted statement</returns>
        protected String ConvertOuterJoin(String sqlStatement)
        {
            bool trace = false;
            //
            int fromIndex  = Util.FindIndexOf(sqlStatement.ToUpper(), " FROM ");
            int whereIndex = Util.FindIndexOf(sqlStatement.ToUpper(), " WHERE ");
            //begin vpj-cd e-evolution 03/14/2005 PostgreSQL
            //int endWhereIndex = Util.findIndexOf(sqlStatement.toUpperCase(), " GRPUP BY ");
            int endWhereIndex = Util.FindIndexOf(sqlStatement.ToUpper(), " GROUP BY ");

            //end vpj-cd e-evolution 03/14/2005	PostgreSQL
            if (endWhereIndex == -1)
            {
                endWhereIndex = Util.FindIndexOf(sqlStatement.ToUpper(), " ORDER BY ");
            }
            if (endWhereIndex == -1)
            {
                endWhereIndex = sqlStatement.Length;
            }
            //
            if (trace)
            {
                log.Info("OuterJoin<== " + sqlStatement);
                //	log.info("From=" + fromIndex + ", Where=" + whereIndex + ", End=" + endWhereIndex + ", Length=" + sqlStatement.length());
            }
            //
            String selectPart = sqlStatement.Substring(0, fromIndex);
            String fromPart   = sqlStatement.Substring(fromIndex, whereIndex);
            String wherePart  = sqlStatement.Substring(whereIndex, endWhereIndex);
            String rest       = sqlStatement.Substring(endWhereIndex);

            //  find/remove all (+) from WHERE clase ------------------------------
            String        newWherePart = wherePart;
            List <String> joins        = new List <String>();
            int           pos          = newWherePart.IndexOf("(+)");

            while (pos != -1)
            {
                //  find starting point
                int start       = newWherePart.LastIndexOf(" AND ", pos);
                int startOffset = 5;
                if (start == -1)
                {
                    start       = newWherePart.LastIndexOf(" OR ", pos);
                    startOffset = 4;
                }
                if (start == -1)
                {
                    start       = newWherePart.LastIndexOf("WHERE ", pos);
                    startOffset = 6;
                }
                if (start == -1)
                {
                    String error = "Start point not found in clause " + wherePart;
                    log.Severe(error);
                    m_conversionError = error;
                    return(sqlStatement);
                }
                //  find end point
                int end = newWherePart.IndexOf(" AND ", pos);
                if (end == -1)
                {
                    end = newWherePart.IndexOf(" OR ", pos);
                }
                if (end == -1)
                {
                    end = newWherePart.Length;
                }
                //	log.info("<= " + newWherePart + " - Start=" + start + "+" + startOffset + ", End=" + end);

                //  extract condition
                String condition = newWherePart.Substring(start + startOffset, end);
                joins.Add(condition);
                if (trace)
                {
                    log.Info("->" + condition);
                }
                //  new WHERE clause
                newWherePart = newWherePart.Substring(0, start) + newWherePart.Substring(end);
                //	log.info("=> " + newWherePart);
                //
                pos = newWherePart.IndexOf("(+)");
            }
            //  correct beginning
            newWherePart = newWherePart.Trim();
            if (newWherePart.StartsWith("AND "))
            {
                newWherePart = "WHERE" + newWherePart.Substring(3);
            }
            else if (newWherePart.StartsWith("OR "))
            {
                newWherePart = "WHERE" + newWherePart.Substring(2);
            }
            if (trace)
            {
                log.Info("=> " + newWherePart);
            }

            //  Correct FROM clause -----------------------------------------------
            //  Disassemble FROM
            String[] fromParts = fromPart.Trim().Substring(4).Split(',');
            Dictionary <String, String> fromAlias  = new Dictionary <String, String>(); //  tables to be processed
            Dictionary <String, String> fromLookup = new Dictionary <String, String>(); //  used tabled

            for (int i = 0; i < fromParts.Length; i++)
            {
                String entry = fromParts[i].Trim();
                String alias = entry; //  no alias
                String table = entry;
                int    aPos  = entry.LastIndexOf(' ');
                if (aPos != -1)
                {
                    alias = entry.Substring(aPos + 1);
                    table = entry.Substring(0, entry.IndexOf(' ')); // may have AS
                }
                fromAlias.Add(alias, table);
                fromLookup.Add(alias, table);
                if (trace)
                {
                    log.Info("Alias=" + alias + ", Table=" + table);
                }
            }

            /** Single column
             *  SELECT t.TableName, w.Name FROM AD_Table t, AD_Window w
             *  WHERE t.AD_Window_ID=w.AD_Window_ID(+)
             *  --	275 rows
             *  SELECT t.TableName, w.Name FROM AD_Table t
             *  LEFT OUTER JOIN AD_Window w ON (t.AD_Window_ID=w.AD_Window_ID)
             *
             *  SELECT t.TableName, w.Name FROM AD_Table t, AD_Window w
             *  WHERE t.AD_Window_ID(+)=w.AD_Window_ID
             *  --	239 rows
             *  SELECT t.TableName, w.Name FROM AD_Table t
             *  RIGHT OUTER JOIN AD_Window w ON (t.AD_Window_ID=w.AD_Window_ID)
             *
             **  Multiple columns
             ** SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
             ** FROM AD_TreeNode tn, AD_TreeBar tb
             ** WHERE tn.AD_Tree_ID=tb.AD_Tree_ID(+) AND tn.Node_ID=tb.Node_ID(+)
             **   AND tn.AD_Tree_ID=10
             ** --  235 rows
             ** SELECT	tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
             ** FROM AD_TreeNode tn LEFT OUTER JOIN AD_TreeBar tb
             **   ON (tn.Node_ID=tb.Node_ID AND tn.AD_Tree_ID=tb.AD_Tree_ID AND tb.AD_User_ID=0)
             ** WHERE tn.AD_Tree_ID=10
             **
             ** SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
             ** FROM AD_TreeNode tn, AD_TreeBar tb
             ** WHERE tn.AD_Tree_ID=tb.AD_Tree_ID(+) AND tn.Node_ID=tb.Node_ID(+)
             **  AND tn.AD_Tree_ID=10 AND tb.AD_User_ID(+)=0
             ** --  214 rows
             ** SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
             ** FROM AD_TreeNode tn LEFT OUTER JOIN AD_TreeBar tb
             **   ON (tn.Node_ID=tb.Node_ID AND tn.AD_Tree_ID=tb.AD_Tree_ID AND tb.AD_User_ID=0)
             ** WHERE tn.AD_Tree_ID=10
             **
             */
            StringBuilder newFrom = new StringBuilder();

            for (int i = 0; i < joins.Count; i++)
            {
                Join first = new Join(joins[i]);
                first.SetMainTable(fromLookup[first.GetMainAlias()]);
                fromAlias.Remove(first.GetMainAlias()); //  remove from list
                first.SetJoinTable(fromLookup[first.GetJoinAlias()]);
                fromAlias.Remove(first.GetJoinAlias()); //  remove from list
                if (trace)
                {
                    log.Info("-First: " + first);
                }
                //
                if (newFrom.Length == 0)
                {
                    newFrom.Append(" FROM ");
                }
                else
                {
                    newFrom.Append(", ");
                }
                newFrom.Append(first.GetMainTable()).Append(" ").Append(first.GetMainAlias())
                .Append(first.IsLeft() ? " LEFT" : " RIGHT").Append(" OUTER JOIN ")
                .Append(first.GetJoinTable()).Append(" ").Append(first.GetJoinAlias())
                .Append(" ON (").Append(first.GetCondition());
                //  keep it open - check for other key comparisons
                for (int j = i + 1; j < joins.Count; j++)
                {
                    Join second = new Join(joins[j]);
                    second.SetMainTable(fromLookup[second.GetMainAlias()]);
                    second.SetJoinTable(fromLookup[second.GetJoinAlias()]);
                    if ((first.GetMainTable().Equals(second.GetMainTable()) &&
                         first.GetJoinTable().Equals(second.GetJoinTable())) ||
                        second.IsConditionOf(first))
                    {
                        if (trace)
                        {
                            log.Info("-Second/key: " + second);
                        }
                        newFrom.Append(" AND ").Append(second.GetCondition());
                        joins.RemoveAt(j);                      //  remove from join list
                        fromAlias.Remove(first.GetJoinAlias()); //  remove from table list
                        //----
                        for (int k = i + 1; k < joins.Count; k++)
                        {
                            Join third = new Join(joins[k]);
                            third.SetMainTable(fromLookup[third.GetMainAlias()]);
                            third.SetJoinTable(fromLookup[third.GetJoinAlias()]);
                            if (third.IsConditionOf(second))
                            {
                                if (trace)
                                {
                                    log.Info("-Third/key: " + third);
                                }
                                newFrom.Append(" AND ").Append(third.GetCondition());
                                joins.RemoveAt(k);                      //  remove from join list
                                fromAlias.Remove(third.GetJoinAlias()); //  remove from table list
                            }
                            else if (trace)
                            {
                                log.Info("-Third/key-skip: " + third);
                            }
                        }
                    }
                    else if (trace)
                    {
                        log.Info("-Second/key-skip: " + second);
                    }
                }
                newFrom.Append(")"); //  close ON
                //  check dependency on first table
                for (int j = i + 1; j < joins.Count; j++)
                {
                    Join second = new Join(joins[j]);
                    second.SetMainTable(fromLookup[second.GetMainAlias()]);
                    second.SetJoinTable(fromLookup[second.GetJoinAlias()]);
                    if (first.GetMainTable().Equals(second.GetMainTable()))
                    {
                        if (trace)
                        {
                            log.Info("-Second/dep: " + second);
                        }
                        //   FROM (AD_Field f LEFT OUTER JOIN AD_Column c ON (f.AD_Column_ID = c.AD_Column_ID))
                        //  LEFT OUTER JOIN AD_FieldGroup fg ON (f.AD_FieldGroup_ID = fg.AD_FieldGroup_ID),
                        newFrom.Insert(6, '('); //  _FROM ...
                        newFrom.Append(')');    //  add parantesis on previous relation
                        //
                        newFrom.Append(second.IsLeft() ? " LEFT" : " RIGHT").Append(" OUTER JOIN ")
                        .Append(second.GetJoinTable()).Append(" ").Append(second.GetJoinAlias())
                        .Append(" ON (").Append(second.GetCondition());
                        joins.RemoveAt(j);                       //  remove from join list
                        fromAlias.Remove(second.GetJoinAlias()); //  remove from table list
                        //  additional join colums would come here
                        newFrom.Append(")");                     //  close ON
                        //----
                        for (int k = i + 1; k < joins.Count; k++)
                        {
                            Join third = new Join(joins[k]);
                            third.SetMainTable(fromLookup[third.GetMainAlias()]);
                            third.SetJoinTable(fromLookup[third.GetJoinAlias()]);
                            if (second.GetJoinTable().Equals(third.GetMainTable()))
                            {
                                if (trace)
                                {
                                    log.Info("-Third-dep: " + third);
                                }
                                //   FROM ((C_BPartner p LEFT OUTER JOIN AD_User c ON (p.C_BPartner_ID=c.C_BPartner_ID))
                                //  LEFT OUTER JOIN C_BPartner_Location l ON (p.C_BPartner_ID=l.C_BPartner_ID))
                                //  LEFT OUTER JOIN C_Location a ON (l.C_Location_ID=a.C_Location_ID)
                                newFrom.Insert(6, '('); //  _FROM ...
                                newFrom.Append(')');    //  add parantesis on previous relation
                                //
                                newFrom.Append(third.IsLeft() ? " LEFT" : " RIGHT").Append(" OUTER JOIN ")
                                .Append(third.GetJoinTable()).Append(" ").Append(third.GetJoinAlias())
                                .Append(" ON (").Append(third.GetCondition());
                                joins.RemoveAt(k);                      //  remove from join list
                                fromAlias.Remove(third.GetJoinAlias()); //  remove from table list
                                //  additional join colums would come here
                                newFrom.Append(")");                    //  close ON
                            }
                            else if (trace)
                            {
                                log.Info("-Third-skip: " + third);
                            }
                        }
                    }
                    else if (trace)
                    {
                        log.Info("-Second/dep-skip: " + second);
                    }
                } //  dependency on first table
            }
            //  remaining Tables
            var it = fromAlias.Keys.GetEnumerator();

            while (it.MoveNext())
            {
                String alias = it.Current;
                Object table = fromAlias[alias];
                newFrom.Append(", ").Append(table);
                if (!table.Equals(alias))
                {
                    newFrom.Append(" ").Append(alias);
                }
            }
            if (trace)
            {
                log.Info(newFrom.ToString());
            }
            //
            StringBuilder retValue = new StringBuilder(sqlStatement.Length + 20);

            retValue.Append(selectPart)
            .Append(newFrom).Append(" ")
            .Append(newWherePart).Append(rest);
            //
            if (trace)
            {
                log.Info("OuterJoin==> " + retValue.ToString());
            }
            return(retValue.ToString());
        }