/// <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(")");
}
}
/// <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);
}
/// <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);
}
/// <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)");
}
}
}
/// <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());
}