public FilterAtomEditor(CollectionVM collection, FltAtomExprData filter)
{
InitializeComponent();
var field = collection.AvailableProperties.FirstOrDefault(p => p.Path == filter.Field);
Filter = filter;
IsWithEntityIdFilter = false;
setField(field);
}
public a7FilterElementEditor(a7SingleTableExplorer entity, FltAtomExprData filter)
{
InitializeComponent();
var field = entity.AvailableColumns.FirstOrDefault(c => c.Name == filter.Field);
Filter = filter;
IsWithEntityIdFilter = false;
setField(field);
}
public FilterAtomEditor(PropertyDefinitionModel field)
{
InitializeComponent();
IsWithEntityIdFilter = false;
Filter = new FltAtomExprData()
{
Operator = FilterFieldOperator.Equal,
PropertyType = field.Type,
Field = field.Path
};
setField(field);
}
public a7FilterElementEditor(a7FilterElementDefinition elementDef)
{
InitializeComponent();
IsWithEntityIdFilter = false;
var field = elementDef.FieldData;
Filter = new FltAtomExprData()
{
Operator = FilterFieldOperator.Equal,
Field = field.Name
};
setField(field);
}
public FilterElementEditor(CollectionVM collection, FltAtomExprData filter, bool isPopupMode)
{
this.IsPopupMode = isPopupMode;
InitializeComponent();
this.propSelector.SelectedPropertyChanged += PropSelector_SelectedPropertyChanged;
Filter = filter;
this.propSelector.Properties = collection.AvailableProperties;
this.propSelector.SelectedProperty = new PropertyDefinitionModel
{
Path = filter.Field,
Name = filter.Field,
Type = filter.PropertyType
};
IsWithcollectionIdFilter = false;
setField(this.propSelector.SelectedProperty);
}
public FilterElementEditor(CollectionVM collection, PropertyDefinitionModel elementDef, bool isPopupMode)
{
this.IsPopupMode = isPopupMode;
InitializeComponent();
this.propSelector.SelectedPropertyChanged += PropSelector_SelectedPropertyChanged;
IsWithcollectionIdFilter = false;
var field = elementDef;
this.propSelector.Properties = collection.AvailableProperties;
this.propSelector.SelectedProperty = elementDef;
Filter = new FltAtomExprData()
{
Operator = FilterFieldOperator.Equal,
PropertyType = field.Type,
Field = field.Path
};
setField(field);
}
private string FieldName2ParamName(string fieldName, FltAtomExprData atomic)
{
return(fieldName.Replace(".", "") + atomic.GetHashCode().ToString());
}
private void appendFilterAtom2WhereSb(FltAtomExprData filterAtom, string preffixToAppend, string collName)
{
if (filterAtom == null)
{
return;
}
var fltValue = filterAtom.Value;
var isCustomValue = false;
var pField = filterAtom.Field;// StringUtils.FirstCharacterToLower(filterAtom.Field);
if (filterAtom.Operator == FilterFieldOperator.Between)
{
var filterBigger = filterAtom.Clone() as FltAtomExprData;
filterBigger.Operator = FilterFieldOperator.GreaterEqualThan;
filterBigger.Value = filterBigger.Value.BeforeString(";");
var filterSmaller = filterAtom.Clone() as FltAtomExprData;
filterSmaller.Operator = FilterFieldOperator.LessEqualThan;
filterSmaller.Value = filterSmaller.Value.AfterString(";");
var group = Filter.Filter.And(filterBigger, filterSmaller);
filterExprAppendWhereAndParams(group, preffixToAppend, collName);
return;
}
if (filterAtom.Operator == FilterFieldOperator.In && (filterAtom.Values == null || filterAtom.Values.Count == 0) && filterAtom.Value.IsEmpty())
{
filterAtom = Filter.Filter.AlwaysFalse();
}
if (filterAtom.Field == "1")
{
_whereStringBuilder.Append(preffixToAppend);
_whereStringBuilder.Append(" (1=" + filterAtom.Value + ")");
return;
}
if (pField == null)
{
filterAtom.Field = "";
filterAtom.Operator = FilterFieldOperator.IsNull;
}
_whereStringBuilder.Append(preffixToAppend);
_whereStringBuilder.Append("(");
var fld = pField;
if (filterAtom.Operator != FilterFieldOperator.Contains &&
filterAtom.Operator != FilterFieldOperator.StartsWith &&
filterAtom.Operator != FilterFieldOperator.EndsWith)
{
if (collName.IsNotEmpty())
{
_whereStringBuilder.Append(collName);
_whereStringBuilder.Append(".");
}
_whereStringBuilder.Append(fld);
}
else
{
// fld = $"{collName}.{fld}";
if (collName.IsNotEmpty())
{
_whereStringBuilder.Append(collName);
_whereStringBuilder.Append(".");
}
_whereStringBuilder.Append(fld);
}
_whereStringBuilder.Append(" ");
switch (filterAtom.Operator)
{
case FilterFieldOperator.Equal:
_whereStringBuilder.Append("="); break;
case FilterFieldOperator.GreaterThan:
_whereStringBuilder.Append(">"); break;
case FilterFieldOperator.GreaterEqualThan:
_whereStringBuilder.Append(">="); break;
case FilterFieldOperator.In:
_whereStringBuilder.Append("IN"); break;
case FilterFieldOperator.LessThan:
_whereStringBuilder.Append("<"); break;
case FilterFieldOperator.LessEqualThan:
_whereStringBuilder.Append("<="); break;
case FilterFieldOperator.Like:
case FilterFieldOperator.Contains:
case FilterFieldOperator.StartsWith:
case FilterFieldOperator.EndsWith:
_whereStringBuilder.Append("LIKE"); break;
case FilterFieldOperator.NotEqual:
_whereStringBuilder.Append("<>"); break;
case FilterFieldOperator.IsNull:
_whereStringBuilder.Append("IS NULL"); break;
case FilterFieldOperator.IsNotNull:
_whereStringBuilder.Append("IS NOT NULL"); break;
}
_whereStringBuilder.Append(" ");
if (filterAtom.Operator == FilterFieldOperator.In ||
filterAtom.Operator == FilterFieldOperator.Like ||
filterAtom.Operator == FilterFieldOperator.Contains ||
filterAtom.Operator == FilterFieldOperator.StartsWith ||
filterAtom.Operator == FilterFieldOperator.EndsWith)
{
_whereStringBuilder.Append("(");
}
if (filterAtom.Operator == FilterFieldOperator.In)
{
var valuesList = new List <string>();
valuesList = filterAtom.Values ?? filterAtom.Value.SplitCharList(';');
for (var i = 0; i < valuesList.Count; i++)
{
if (i > 0)
{
_whereStringBuilder.Append(",");
}
_whereStringBuilder.Append("@");
_whereStringBuilder.Append(FieldName2ParamName(pField + i, filterAtom));
Parameters.Add(FieldName2ParamName(pField + i, filterAtom), valuesList[i]);
}
}
else if (filterAtom.Operator != FilterFieldOperator.IsNull && filterAtom.Operator != FilterFieldOperator.IsNotNull)
{
if (!isCustomValue)
{
_whereStringBuilder.Append("@");
_whereStringBuilder.Append(FieldName2ParamName(pField, filterAtom));
object value = fltValue;
var column = _table.AvailableColumns.FirstOrDefault(c => c.Name == fld);
var fieldType = column?.Type ?? PropertyType.String;
//if date type field, and less than operator, and minutes and hours is 00:00, add one day, so that the day which less than should be, would be included
if (fieldType == PropertyType.DateTime)
{
DateTime tmpDt = DateTime.MinValue;
bool parsed = false;
if (DateTime.TryParse(fltValue, CultureInfo.CurrentCulture, DateTimeStyles.None, out tmpDt))
{
parsed = true;
}
else if (DateTime.TryParse(fltValue, out tmpDt))
{
parsed = true;
}
else
{
string[] formats = { "M/d/yyyy h:mm:ss tt", "M/d/yyyy h:mm tt",
"MM/dd/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss",
"M/d/yyyy hh:mm tt", "M/d/yyyy hh tt",
"M/d/yyyy h:mm", "M/d/yyyy h:mm",
"MM/dd/yyyy hh:mm", "M/dd/yyyy hh:mm", "dd.MM.yyyy" };
if (DateTime.TryParseExact(fltValue, formats,
new CultureInfo("en-US"),
DateTimeStyles.None,
out tmpDt))
{
parsed = true;
}
}
if (parsed)
{
if (filterAtom.Operator == FilterFieldOperator.LessEqualThan)
{
if (tmpDt.Hour == 0 && tmpDt.Minute == 0)
{
tmpDt = tmpDt.AddDays(1);
}
}
value = tmpDt.ToString("s");
}
}
if (filterAtom.Operator == FilterFieldOperator.Contains)
{
value = $"%{value}%";
}
else if (filterAtom.Operator == FilterFieldOperator.StartsWith)
{
value = $"{value}%";
}
else if (filterAtom.Operator == FilterFieldOperator.EndsWith)
{
value = $"%{value}";
}
Parameters[FieldName2ParamName(pField, filterAtom)] = value;
}
else
{
fltValue = fltValue.Replace("'", "''");
_whereStringBuilder.Append(fltValue);
}
}
if (filterAtom.Operator == FilterFieldOperator.In ||
filterAtom.Operator == FilterFieldOperator.Like ||
filterAtom.Operator == FilterFieldOperator.Contains ||
filterAtom.Operator == FilterFieldOperator.StartsWith ||
filterAtom.Operator == FilterFieldOperator.EndsWith)
{
_whereStringBuilder.Append(")");
}
_whereStringBuilder.Append(")");
}
private void appendFilterAtom2WhereSb(FltAtomExprData filterAtom, string preffixToAppend, string collName)
{
if (filterAtom == null)
{
return;
}
var fltValue = filterAtom.Value;
var isCustomValue = false;
var pField = filterAtom.Field;// StringUtils.FirstCharacterToLower(filterAtom.Field);
if (filterAtom.Operator == FilterFieldOperator.Between)
{
var filterBigger = filterAtom.Clone() as FltAtomExprData;
filterBigger.Operator = FilterFieldOperator.GreaterEqualThan;
filterBigger.Value = filterBigger.Value.BeforeString(";");
var filterSmaller = filterAtom.Clone() as FltAtomExprData;
filterSmaller.Operator = FilterFieldOperator.LessEqualThan;
filterSmaller.Value = filterSmaller.Value.AfterString(";");
var group = Filter.Filter.And(filterBigger, filterSmaller);
filterExprAppendWhereAndParams(group, preffixToAppend, collName);
return;
}
if (filterAtom.Operator == FilterFieldOperator.In && (filterAtom.Values == null || filterAtom.Values.Count == 0) && filterAtom.Value.IsEmpty())
{
filterAtom = Filter.Filter.AlwaysFalse();
}
if (filterAtom.Field == "1")
{
_whereStringBuilder.Append(preffixToAppend);
_whereStringBuilder.Append(" (1=" + filterAtom.Value + ")");
return;
}
if (pField == null)
{
filterAtom.Field = "";
filterAtom.Operator = FilterFieldOperator.IsNull;
}
_whereStringBuilder.Append(preffixToAppend);
_whereStringBuilder.Append("(");
var fld = pField;
if (filterAtom.Operator != FilterFieldOperator.Contains &&
filterAtom.Operator != FilterFieldOperator.StartsWith &&
filterAtom.Operator != FilterFieldOperator.EndsWith)
{
if (collName.IsNotEmpty())
{
_whereStringBuilder.Append(collName);
_whereStringBuilder.Append(".");
}
_whereStringBuilder.Append(fld);
}
else
{
fld = $"{collName}.{fld}";
}
_whereStringBuilder.Append(" ");
switch (filterAtom.Operator)
{
case FilterFieldOperator.Equal:
_whereStringBuilder.Append("="); break;
case FilterFieldOperator.GreaterThan:
_whereStringBuilder.Append(">"); break;
case FilterFieldOperator.GreaterEqualThan:
_whereStringBuilder.Append(">="); break;
case FilterFieldOperator.In:
_whereStringBuilder.Append("IN"); break;
case FilterFieldOperator.LessThan:
_whereStringBuilder.Append("<"); break;
case FilterFieldOperator.LessEqualThan:
_whereStringBuilder.Append("<="); break;
//In document DB it's different
//case FilterFieldOperator.Like:
//case FilterFieldOperator.Contains:
//case FilterFieldOperator.StartsWith:
//case FilterFieldOperator.EndsWith:
// _whereStringBuilder.Append("LIKE"); break;
case FilterFieldOperator.NotEqual:
_whereStringBuilder.Append("<>"); break;
case FilterFieldOperator.IsNull:
_whereStringBuilder.Append("IS NULL"); break;
case FilterFieldOperator.IsNotNull:
_whereStringBuilder.Append("IS NOT NULL"); break;
}
_whereStringBuilder.Append(" ");
if (filterAtom.Operator == FilterFieldOperator.In)
{
_whereStringBuilder.Append("(");
}
if (filterAtom.Operator == FilterFieldOperator.In)
{
List <string> valuesList = new List <string>();
if (filterAtom.Values != null)
{
valuesList = filterAtom.Values;
}
else
{
valuesList = filterAtom.Value.SplitCharList(';');
}
for (int i = 0; i < valuesList.Count; i++)
{
if (i > 0)
{
_whereStringBuilder.Append(",");
}
_whereStringBuilder.Append("@");
_whereStringBuilder.Append(FieldName2ParamName(pField + i, filterAtom));
Parameters.Add(FieldName2ParamName(pField + i, filterAtom), valuesList[i]);
}
}
else if (filterAtom.Operator == FilterFieldOperator.StartsWith ||
filterAtom.Operator == FilterFieldOperator.EndsWith ||
filterAtom.Operator == FilterFieldOperator.Contains)
{
var func = "";
if (filterAtom.Operator == FilterFieldOperator.StartsWith)
{
func = "STARTSWITH";
}
else if (filterAtom.Operator == FilterFieldOperator.EndsWith)
{
func = "ENDSWITH";
}
else if (filterAtom.Operator == FilterFieldOperator.Contains)
{
func = "CONTAINS";
}
_whereStringBuilder.Append($" {func}({fld},@{FieldName2ParamName(pField, filterAtom)}) ");
Parameters[FieldName2ParamName(pField, filterAtom)] = fltValue;
}
else if (filterAtom.Operator != FilterFieldOperator.IsNull && filterAtom.Operator != FilterFieldOperator.IsNotNull)
{
if (!isCustomValue)
{
_whereStringBuilder.Append("@");
_whereStringBuilder.Append(FieldName2ParamName(pField, filterAtom));
object value = fltValue;
if (filterAtom.PropertyType == PropertyType.Integer)
{
var isNumberTry = fltValue.ToInt(int.MaxValue);
if (isNumberTry != int.MaxValue)
{
value = isNumberTry;
}
}
else if (filterAtom.PropertyType == PropertyType.Float)
{
var isNumberTry = fltValue.ToDouble(double.NaN);
if (isNumberTry != double.NaN)
{
value = isNumberTry;
}
}
Parameters[FieldName2ParamName(pField, filterAtom)] = value;
}
else
{
fltValue = fltValue.Replace("'", "''");
_whereStringBuilder.Append(fltValue);
}
}
if (filterAtom.Operator == FilterFieldOperator.In)
{
_whereStringBuilder.Append(")");
}
_whereStringBuilder.Append(")");
}
