/// <summary>
/// Fulls the name search.
/// </summary>
/// <param name="firstNameField">The first name field.</param>
/// <param name="lastNameField">The last name field.</param>
/// <param name="uniqueIdField">The unique identifier field.</param>
/// <param name="searchString">The search string.</param>
protected void FullNameSearch(string firstNameField, string lastNameField, string uniqueIdField, string searchString)
{
var parts = new List <string>();
if (!String.IsNullOrWhiteSpace(searchString))
{
parts = searchString.Trim().Split(SearchFullNamePattern, StringSplitOptions.RemoveEmptyEntries).Take(3).ToList();
}
switch (parts.Count)
{
case 0:
DynamicHelper.SetProperty(Param, Parameters.Query, "-1");
Conditions.Add(SearchNameLike(uniqueIdField, Parameters.Query));
break;
case 1:
DynamicHelper.SetProperty(Param, Parameters.Query, parts[0]);
Conditions.Add(String.Format(
ConditionThreeOrTemplate,
SearchNameLike(firstNameField, Parameters.Query),
SearchNameLike(lastNameField, Parameters.Query),
SearchNameLike(uniqueIdField, Parameters.Query)));
break;
case 2:
DynamicHelper.SetProperty(Param, Parameters.Query, String.Join(" ", parts));
DynamicHelper.SetProperty(Param, Parameters.SearchQuery1, parts[0]);
DynamicHelper.SetProperty(Param, Parameters.SearchQuery2, parts[1]);
Conditions.Add(String.Format(
ConditionFourOrTemplate,
String.Format(ConditionAndTemplate, SearchNameLike(firstNameField, Parameters.SearchQuery1), SearchNameLike(lastNameField, Parameters.SearchQuery2)),
String.Format(ConditionAndTemplate, SearchNameLike(firstNameField, Parameters.SearchQuery2), SearchNameLike(lastNameField, Parameters.SearchQuery1)),
SearchNameLike(firstNameField, Parameters.Query),
SearchNameLike(lastNameField, Parameters.Query)));
break;
case 3:
DynamicHelper.SetProperty(Param, Parameters.SearchQuery1, $"{parts[0]} {parts[1]}");
DynamicHelper.SetProperty(Param, Parameters.SearchQuery2, parts[2]);
DynamicHelper.SetProperty(Param, Parameters.SearchQuery3, parts[0]);
DynamicHelper.SetProperty(Param, Parameters.SearchQuery4, $"{parts[1]} {parts[2]}");
Conditions.Add(String.Format(
ConditionFourOrTemplate,
String.Format(ConditionAndTemplate, SearchNameLike(firstNameField, Parameters.SearchQuery1), SearchNameLike(lastNameField, Parameters.SearchQuery2)),
String.Format(ConditionAndTemplate, SearchNameLike(firstNameField, Parameters.SearchQuery2), SearchNameLike(lastNameField, Parameters.SearchQuery1)),
String.Format(ConditionAndTemplate, SearchNameLike(firstNameField, Parameters.SearchQuery3), SearchNameLike(lastNameField, Parameters.SearchQuery4)),
String.Format(ConditionAndTemplate, SearchNameLike(firstNameField, Parameters.SearchQuery4), SearchNameLike(lastNameField, Parameters.SearchQuery3))));
break;
}
}
/// <summary>
/// Gets the list ids query condition.
/// </summary>
/// <typeparam name="K"></typeparam>
/// <param name="entityIds">The entity ids.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="param">The parameter.</param>
/// <param name="alias">The alias.</param>
/// <param name="field">The field.</param>
/// <returns></returns>
public string GetListIdsQueryCondition <K>(IList <K> entityIds, string parameters, object param, string alias, string field) where K : struct
{
var ids = entityIds.ToList();
var condition = new StringBuilder();
condition.Append(ConditionStartBracketTemplate);
if (ids.Count < DbConstants.MaxParametersPerCommand)
{
while (ids.Any())
{
DynamicHelper.SetProperty(
param,
String.Concat(parameters, ids.Count),
ids.Take(DbConstants.MaxParametersPerCommand).ToList());
condition.Append(String.Format(ConditionInTemplate, alias, field, "@" + String.Concat(parameters, ids.Count)));
ids.RemoveRange(
0,
ids.Count > DbConstants.MaxParametersPerCommand ? DbConstants.MaxParametersPerCommand : ids.Count);
if (ids.Any())
{
condition.Append(ConditionPartialOrTemplate);
}
}
condition.Append(ConditionEndBracketTemplate);
return(condition.ToString());
}
for (var i = 0; i < ids.Count; i++)
{
condition.Append(i == 0
? String.Format(ConditionSelectInTableTemplate, ids[i])
: String.Format(ConditionStartSelectInTableTemplate, ids[i]));
}
condition.Append(String.Format(ConditionEndSelectInTableTemplate, parameters));
condition.Append(ConditionEndBracketTemplate);
return(String.Format(ConditionInQueryTemplate, alias, field, condition));
}
/// <summary>
/// Builds the query filters.
/// </summary>
private void BuildQueryFilters()
{
if (Filter == null)
{
return;
}
if (!String.IsNullOrWhiteSpace(Filter.UserName))
{
DynamicHelper.SetProperty(Param, Parameters.UserName, Filter.UserName.Replace("_", "[_]"));
var field = ReflectionHelper.GetString((AppUser x) => x.UserName);
Conditions.Add(String.Format(ConditionLikeTemplate, Alias, field, "@" + Parameters.UserName));
}
if (!String.IsNullOrEmpty(Filter.FirstName))
{
DynamicHelper.SetProperty(Param, Parameters.FirstName, Filter.FirstName);
var field = ReflectionHelper.GetString((AppUser x) => x.FirstName);
Conditions.Add(String.Format(ConditionLikeTemplate, Alias, field, "@" + Parameters.FirstName + " + '%'"));
}
}
/// <summary>
/// Gets the field values with separator condition.
/// </summary>
/// <param name="entityIds">The entity ids.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="param">The parameter.</param>
/// <param name="alias">The alias.</param>
/// <param name="field">The field.</param>
/// <param name="separator">The separator.</param>
/// <returns></returns>
public string GetFieldValuesWithSeparatorCondition(IList <string> entityIds, string parameters, object param, string alias, string field, char separator = ',')
{
var ids = entityIds.ToList();
var commonCondition = new StringBuilder();
var queryCondition = new StringBuilder();
DynamicHelper.SetProperty(param, Parameters.StringValues, ids);
commonCondition.Append(String.Format(ConditionIsNotNullAndNotEmptyTemplate, alias, field));
var index = 1;
foreach (var id in ids)
{
queryCondition.Append(String.Format(ConditionFieldValuesWithSeparatorTemplate, separator, alias, field, "@" + String.Concat(parameters, index)));
if (index < ids.Count)
{
queryCondition.Append(" OR ");
}
index++;
}
return($"{ commonCondition } AND ({ queryCondition })");
}