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