Esempio n. 1
0
        /// <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;
            }
        }
Esempio n. 2
0
        /// <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));
        }
Esempio n. 3
0
        /// <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 + " + '%'"));
            }
        }
Esempio n. 4
0
        /// <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 })");
        }