/// <summary>
        /// Adds the condition to statement.
        /// </summary>
        /// <param name="sb">
        /// The sb.
        /// </param>
        /// <param name="context">
        /// The context.
        /// </param>
        /// <param name="tableAlias">
        /// The table alias.
        /// </param>
        public override void AddConditionToStatement(StringBuilder sb, StatementCreationContext context, string tableAlias)
        {
            if (this.SubItemCount == 1)
            {
                this.subItems[0].AddConditionToStatement(sb, context, tableAlias);
            }
            else if (this.SubItemCount > 1)
            {
                for (var i = 0; i < this.SubItemCount; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(") ");
                        sb.Append(this.relation);
                        sb.Append(" (");
                    }
                    else
                    {
                        sb.Append("(");
                    }

                    this.subItems[i].AddConditionToStatement(sb, context, tableAlias);
                }

                sb.Append(")");
            }
        }
예제 #2
0
        /// <summary>
        /// Executes the specified count only.
        /// </summary>
        /// <param name="countOnly">
        /// if set to <c>true</c> [count only].
        /// </param>
        /// <returns>
        /// The <see cref="GenericRecordSet"/>.
        /// </returns>
        public GenericRecordSet Execute(bool countOnly)
        {
            var recordSet = new DatabaseRecordSet(this.crmDatabase);
            var context   = new StatementCreationContext(this);

            var statementString = this.CreateStatement(context, countOnly);

            if (!string.IsNullOrEmpty(context.ErrorText))
            {
                // _crmDatabase.Trace(_crmDatabase, context.GetErrorText());
                return(null);
            }

            var parameterList  = context.ParameterValues;
            var parameterCount = parameterList?.Count ?? 0;

            if (parameterCount > 0)
            {
                recordSet.Execute(statementString, parameterList?.ToArray(), this.MaxResultRowCount);
            }
            else
            {
                recordSet.Execute(statementString, this.MaxResultRowCount);
            }

            if (recordSet.GetRowCount() > 0 && this.RootTreeItem.HasSubQueries())
            {
                var joinedRecordSet = this.RootTreeItem.ExecuteSubQueries(recordSet);
                return(joinedRecordSet);
            }

            return(recordSet);
        }
예제 #3
0
        /// <summary>
        /// Adds to order by.
        /// </summary>
        /// <param name="sb">
        /// The sb.
        /// </param>
        /// <param name="context">
        /// The context.
        /// </param>
        public void AddToOrderBy(StringBuilder sb, StatementCreationContext context)
        {
            if (!string.IsNullOrEmpty(this.lookupTableName))
            {
                var _usePrioColumn = (this.IsFixCat && context.Query.SortFixBySortInfoAndCode) ||
                                     (this.IsVarCat && context.Query.SortVarBySortInfo);
                var _useCode = this.IsFixCat && context.Query.SortFixBySortInfoAndCode;

                if (_usePrioColumn && !string.IsNullOrEmpty(this.prioSortColumn))
                {
                    sb.Append("CASE ");
                    sb.Append(this.alias);
                    sb.Append(".");
                    sb.Append(this.prioSortColumn);
                    sb.Append(" WHEN 0 THEN 30000 ELSE IFNULL(");
                    sb.Append(this.alias);
                    sb.Append(".");
                    sb.Append(this.prioSortColumn);
                    sb.Append(",32000) END, ");
                }

                sb.Append(this.alias);
                sb.Append(".");

                sb.Append(_useCode ? this.codeColumn : this.sortColumn);
            }
            else
            {
                var fieldName = this.queryTreeItem.GetFieldName(this.fieldId);
                sb.Append(fieldName);
            }

            var collation = context.Collation;

            if (!string.IsNullOrEmpty(collation))
            {
                var fieldInfo = this.queryTreeItem.GetFieldInfo(this.fieldId);

                if (fieldInfo != null && !fieldInfo.IsNumeric)
                {
                    sb.Append(" COLLATE ");
                    sb.Append(collation);
                }
            }

            if (this.IsDescending)
            {
                sb.Append(" DESC");
            }
        }
        /// <summary>
        /// Adds the condition to statement.
        /// </summary>
        /// <param name="sb">
        /// The sb.
        /// </param>
        /// <param name="context">
        /// The context.
        /// </param>
        /// <param name="tableAlias">
        /// The table alias.
        /// </param>
        public override void AddConditionToStatement(StringBuilder sb, StatementCreationContext context, string tableAlias)
        {
            if (!string.IsNullOrEmpty(tableAlias))
            {
                sb.Append(tableAlias);
                sb.Append(".");
            }

            sb.Append(this.fieldName);
            sb.Append(" ");
            sb.Append(this.fieldCompare);
            sb.Append(" ?");
            context.AddParameterValue(this.fieldValue);
        }
예제 #5
0
        /// <summary>
        /// Adds to from.
        /// </summary>
        /// <param name="sb">
        /// The sb.
        /// </param>
        /// <param name="context">
        /// The context.
        /// </param>
        public void AddToFrom(StringBuilder sb, StatementCreationContext context)
        {
            if (string.IsNullOrEmpty(this.lookupTableName))
            {
                return;
            }

            var fieldName = this.queryTreeItem.GetFieldName(this.fieldId);

            // const char *tableAlias = _queryTreeItem->GetAlias();
            // if (tableAlias)
            // sprintf (joinClause, " LEFT JOIN %s AS %s ON %s.%s = %s.%s", _lookupTableName, _alias, _queryTreeItem->GetAlias(), fieldName, _alias, _codeColumn);
            // else
            var joinClause =
                $" LEFT JOIN {this.lookupTableName} AS {this.alias} ON {fieldName} = {this.alias}.{this.codeColumn}";

            sb.Append(joinClause);
        }
        /// <summary>
        /// Adds the condition to statement.
        /// </summary>
        /// <param name="sb">
        /// The sb.
        /// </param>
        /// <param name="context">
        /// The context.
        /// </param>
        /// <param name="tableAlias">
        /// The table alias.
        /// </param>
        public override void AddConditionToStatement(StringBuilder sb, StatementCreationContext context, string tableAlias)
        {
            if (!string.IsNullOrEmpty(tableAlias))
            {
                sb.Append(tableAlias);
                sb.Append(".");
            }

            sb.Append(this.fieldName);
            sb.Append(" IN (SELECT ");
            sb.Append(this.foreignField);
            sb.Append(" FROM ");
            sb.Append(this.foreignTable);
            sb.Append(" AS FKEY");

            if (this.foreignCondition != null)
            {
                sb.Append(" WHERE ");
                this.foreignCondition.AddConditionToStatement(sb, context, "FKEY");
            }

            sb.Append(")");
        }
 /// <summary>
 /// Adds the condition to statement.
 /// </summary>
 /// <param name="sb">
 /// The sb.
 /// </param>
 /// <param name="context">
 /// The context.
 /// </param>
 /// <param name="tableAlias">
 /// The table alias.
 /// </param>
 public abstract void AddConditionToStatement(
     StringBuilder sb,
     StatementCreationContext context,
     string tableAlias);
        /// <summary>
        /// Adds the condition to statement.
        /// </summary>
        /// <param name="sb">
        /// The sb.
        /// </param>
        /// <param name="context">
        /// The context.
        /// </param>
        /// <param name="tableAlias">
        /// The table alias.
        /// </param>
        public override void AddConditionToStatement(
            StringBuilder sb,
            StatementCreationContext context,
            string tableAlias)
        {
            var equal         = this.fieldCompare == "=";
            var notequal      = this.fieldCompare == "<>";
            var containsEqual = this.fieldCompare.Contains("=");

            var fieldName = context.RecordTemplate.GetFieldName(this.fieldIndex);
            var fieldInfo = context.RecordTemplate.GetFieldInfo(this.fieldIndex);

            string alternateValue = null;

            if (fieldInfo != null)
            {
                if (!string.IsNullOrEmpty(this.FieldValue))
                {
                    if (fieldInfo.IsNumeric)
                    {
                        if (this.FieldValue == "0")
                        {
                            if (equal || notequal || containsEqual)
                            {
                                this.FieldValue = null;
                                alternateValue  = "0";
                            }
                        }
                    }
                    else if (fieldInfo.IsCatalog)
                    {
                        if (this.FieldValue == "0")
                        {
                            this.FieldValue = null;
                            alternateValue  = "0";
                        }
                    }

                    if (fieldInfo.FieldType == 'B' && this.FieldValue == "false")
                    {
                        this.FieldValue = null;
                        alternateValue  = "false";
                    }
                }
                else if (fieldInfo.IsNumeric)
                {
                    if (equal || notequal || containsEqual)
                    {
                        alternateValue = "0";
                    }
                }
                else if (fieldInfo.IsCatalog)
                {
                    alternateValue = "0";
                }
                else if (fieldInfo.FieldType == 'B')
                {
                    alternateValue = "false";
                }
            }

            if (string.IsNullOrEmpty(this.FieldValue))
            {
                sb.Append("(");
                if (!string.IsNullOrEmpty(tableAlias))
                {
                    sb.Append(tableAlias);
                    sb.Append(".");
                }

                sb.Append(fieldName);
                sb.Append(" ");

                if (!string.IsNullOrEmpty(alternateValue))
                {
                    sb.Append((equal || containsEqual) ? "=" : "<>");
                    sb.Append(" ?");
                    context.AddParameterValue(alternateValue);
                    sb.Append((equal || containsEqual) ? " OR " : " AND ");
                    if (!string.IsNullOrEmpty(tableAlias))
                    {
                        sb.Append(tableAlias);
                        sb.Append(".");
                    }

                    sb.Append(fieldName);
                    sb.Append(" ");
                }

                sb.Append(this.fieldCompare);
                sb.Append(" ?");
                context.AddParameterValue(string.Empty);

                sb.Append((equal || containsEqual) ? " OR " : " AND ");

                if (!equal && !containsEqual)
                {
                    sb.Append("NOT ");
                }

                if (!string.IsNullOrEmpty(tableAlias))
                {
                    sb.Append(tableAlias);
                    sb.Append(".");
                }

                sb.Append(fieldName);
                sb.Append(" IS NULL");
                sb.Append(")");
            }
            else
            {
                var         checkEqualNull        = notequal;
                CatalogInfo catalogInfo           = null;
                string      participantsTableName = null;

                if (fieldInfo != null && fieldInfo.IsParticipantsField)
                {
                    var tableInfo = context.RecordTemplate.GetTableInfo();
                    if (tableInfo != null)
                    {
                        participantsTableName = tableInfo.GetDatabaseTableNameForParticipantsField(fieldInfo);
                    }
                }

                if (!string.IsNullOrEmpty(participantsTableName))
                {
                    sb.Append("EXISTS (SELECT repId FROM ");
                    sb.Append(participantsTableName);
                    sb.Append(" WHERE repid ");
                    checkEqualNull = false;
                }
                else
                {
                    if (fieldInfo != null)
                    {
                        switch (fieldInfo.FieldType)
                        {
                        case 'K':
                            if (this.IsCatalogTextValue())
                            {
                                catalogInfo = context.RecordTemplate.Database.DataModel.GetVarCat(fieldInfo.Cat);
                            }

                            break;

                        case 'X':
                            if (this.IsCatalogTextValue())
                            {
                                catalogInfo = context.RecordTemplate.Database.DataModel.GetFixCat(fieldInfo.Cat);
                            }

                            break;
                        }

                        if (catalogInfo != null)
                        {
                            checkEqualNull = false;
                        }
                    }

                    if (checkEqualNull)
                    {
                        sb.Append("(");
                    }

                    if (!string.IsNullOrEmpty(tableAlias))
                    {
                        sb.Append(tableAlias);
                        sb.Append(".");
                    }

                    sb.Append(fieldName);
                    sb.Append(" ");

                    if (catalogInfo != null)
                    {
                        sb.Append("IN (SELECT code FROM ");
                        sb.Append(catalogInfo.GetDatabaseTableName());
                        sb.Append(" WHERE text ");
                    }

                    if ((fieldInfo?.RepMode?.Contains("Rep") ?? false) && this.FieldValue.Contains("*"))
                    {
                        sb.Append("IN (SELECT F0 FROM CRM_ID WHERE F3 ");
                    }
                }

                if (this.FieldValue != null && (equal || notequal) && (this.FieldValue.Contains("*") || this.FieldValue.Contains("?")))
                {
                    for (var i = 0; i < this.FieldValue?.Length; i++)
                    {
                        if (this.FieldValue[i] == '*')
                        {
                            var tb = new StringBuilder(this.FieldValue)
                            {
                                [i] = '%'
                            };
                            this.FieldValue = tb.ToString();
                        }

                        if (this.FieldValue[i] == '?')
                        {
                            var tb = new StringBuilder(this.FieldValue)
                            {
                                [i] = '_'
                            };
                            this.FieldValue = tb.ToString();
                        }
                    }

                    sb.Append(equal ? " LIKE " : " NOT LIKE ");
                }
                else
                {
                    sb.Append(this.fieldCompare);
                }

                sb.Append(" ?");
                context.AddParameterValue(this.FieldValue);

                if (catalogInfo != null || ((fieldInfo?.RepMode?.Contains("Rep") ?? false) && this.FieldValue.Contains("%")))
                {
                    sb.Append(")");
                }
                else if (!string.IsNullOrEmpty(participantsTableName))
                {
                    sb.Append(" AND ");
                    sb.Append(participantsTableName);
                    sb.Append(".recid = ");
                    sb.Append(tableAlias);
                    sb.Append(".recid)");
                }
                else if (checkEqualNull)
                {
                    sb.Append(" OR ");
                    if (!string.IsNullOrEmpty(tableAlias))
                    {
                        sb.Append(tableAlias);
                        sb.Append(".");
                    }

                    sb.Append(fieldName);
                    sb.Append(" IS NULL)");
                }
            }
        }
예제 #9
0
        /// <summary>
        /// Creates the statement.
        /// </summary>
        /// <param name="context">
        /// The context.
        /// </param>
        /// <param name="countOnly">
        /// if set to <c>true</c> [count only].
        /// </param>
        /// <returns>
        /// The <see cref="string"/>.
        /// </returns>
        public string CreateStatement(StatementCreationContext context, bool countOnly)
        {
            var sb         = new StringBuilder();
            var startIndex = 0;
            int i;
            var needsDistinct = false;

            if (this.NeedsDistinct)
            {
                needsDistinct = true;
                if (countOnly)
                {
                    sb.Append("SELECT COUNT(*) FROM (");
                }
            }

            sb.Append("SELECT ");

            if (needsDistinct)
            {
                sb.Append("DISTINCT ");
            }
            else if (countOnly)
            {
                sb.Append("COUNT(*)");
            }

            this.RootTreeItem.CheckSubQueries();

            if (needsDistinct || !countOnly)
            {
                this.RootTreeItem.AddOutputColumns(sb, ref startIndex, true);
            }

            sb.Append(" FROM ");
            this.RootTreeItem.AddFromPart(sb, context, null);

            if (!string.IsNullOrEmpty(context.ErrorText))
            {
                return(null);
            }

            if (this.sortFields != null)
            {
                for (i = 0; i < this.SortFieldCount; i++)
                {
                    var qti = this.sortFields[i].GetTreeItem();
                    if (qti == this.RootTreeItem || qti.SubQuery == null)
                    {
                        this.sortFields[i].AddToFrom(sb, context);
                    }
                }
            }

            if (this.RootTreeItem.Condition != null || this.RootTreeItem.HasExistsConditions() || this.RootTreeItem.IgnoreLookupRows)
            {
                sb.Append(" WHERE ");
                this.RootTreeItem.AddConditionToStatement(sb, context);
            }

            if (!countOnly && this.sortFields != null)
            {
                sb.Append(" ORDER BY ");

                var first = true;
                for (i = 0; i < this.SortFieldCount; i++)
                {
                    var qti = this.sortFields[i].GetTreeItem();
                    if (qti != this.RootTreeItem && qti.SubQuery != null)
                    {
                        continue;
                    }

                    if (!first)
                    {
                        sb.Append(", ");
                    }
                    else
                    {
                        first = false;
                    }

                    this.sortFields[i].AddToOrderBy(sb, context);
                }
            }

            if (this.MaxResultRowCount > 0)
            {
                sb.Append($" LIMIT {this.MaxResultRowCount}");

                if (this.SkipResultRowCount > 0)
                {
                    sb.Append($", {this.SkipResultRowCount}");
                }
            }

            if (needsDistinct && countOnly)
            {
                sb.Append(")");
            }

            return(sb.ToString());
        }