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