public string GetQuery(APISearch Search) { string query = ""; string where = " where (1=1) "; string sort = $" order by {Search.sortby} {Search.sortdirection} "; string paging = ""; if (Search.pagesize != 0) { paging = $" OFFSET { Search.page * Search.pagesize } ROWS FETCH NEXT { Search.pagesize } ROWS ONLY "; } bool detetedfieldincluded = false; if (!string.IsNullOrEmpty(Search.basequery)) { query = $"select * from ({Search.basequery}) as data \r\n"; foreach (var f in Search.filters) { if (f.values.Count > 0) { string w = this.GetFilterAsString(f, int.Parse(Search.gmt), Search.datetimeasdate); if (w != "") { //Determina si aplica el filtro automático de eliminados if (f.field.ToLower() == Search.markdeletedfield.ToLower()) { detetedfieldincluded = true; } if (where != "") { where += " AND "; } where += w; } } } if (!detetedfieldincluded) { if (!Search.ShowDeleted) { if (where != "") { where += " AND "; } where += $" ({Search.markdeletedfield} =0 ) "; } } } string finalQuery = query + "\r\n" + where + "\r\n" + sort + "\r\n" + paging; return(finalQuery); }
public int GetCategoriesTotalCount(APISearch Query) { if (!AllowExecute()) { throw new Exception("Forbidden"); } if (Query.sortby == "") { Query.sortby = CATEGORIES_DEFAULT_SORT_COLUMN; } return(this.CategoriesRepository.GetTotalCount(KEY_CATEGORIES, this.UserId, this.BucketId, Query)); }
public IQueryable <Category> GetCategories([ModelBinder(typeof(APISEarchModelBinderProvider))] APISearch Search) { SetSecurityInfo(); if (Search == null) { Search = new APISearch(); Search.lang = this.GetLanguage(); Search.sortby = this.StoreService.GetDefaultCategoriesSortColumn(); Search.sortdirection = "asc"; } return(this.StoreService.GetCategories(Search)); }
public IQueryable <Category> GetCategories(APISearch Query) { if (!AllowExecute()) { throw new Exception("Forbidden"); } if (string.IsNullOrEmpty(Query.sortby)) { Query.sortby = this.CacheService.GetOrSet <string>("CATEGORIES_DEFAULT_SORT_COLUMN", () => GetDefaultCategoriesSortColumn(), 1440); } if (string.IsNullOrEmpty(Query.markdeletedfield)) { Query.markdeletedfield = this.CacheService.GetOrSet <string>("CATEGORIES_DELETED_MARK_COLUMN", () => GetDefaultCategoriesDeletedMarkColum(), 1440); } Query.basequery = this.CacheService.GetOrSet <string>(KEY_CATEGORIES_BASEQUERY, () => GetBaseQuery(KEY_CATEGORIES), 1440); return(this.CategoriesRepository.Get(KEY_CATEGORIES, this.UserId, this.BucketId, Query)); }
public virtual IQueryable <T> Get(string EntityId, string UserId, string BucketId, APISearch Query) { string sqls = SearchProvider.GetQuery(Query); IQueryable <T> query; if (sqls != "") { DataTable dt = new DataTable(); var conn = context.Database.Connection; var connectionState = conn.State; if (connectionState == ConnectionState.Closed) { conn.Open(); } using (var cmd = conn.CreateCommand()) { cmd.CommandText = sqls; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlParameter("", "")); cmd.Connection = conn; using (var reader = cmd.ExecuteReader()) { dt.Load(reader); } } if (connectionState == ConnectionState.Closed) { conn.Close(); } Type tipo = typeof(T); IList lista = (IList)Activator.CreateInstance((typeof(List <>).MakeGenericType(tipo))); foreach (DataRow r in dt.Rows) { object o = Activator.CreateInstance(tipo); var properties = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (var property in properties) { foreach (DataColumn c in dt.Columns) { if (c.ColumnName.ToLower() == property.Name.ToLower()) { try { if (r[c.ColumnName] != System.DBNull.Value) { if (c.DataType == System.Type.GetType("System.DateTime")) { DateTime d = Convert.ToDateTime(r[c.ColumnName]).AddHours(int.Parse(Query.gmt)); property.SetValue(o, d); } else { property.SetValue(o, r[c.ColumnName]); } } } catch (Exception ex) { } } } } lista.Add(o); } query = (IQueryable <T>)lista.AsQueryable(); } else { query = null; } return(query); }
public int GetFilteredCount(string EntityId, string UserId, string BucketId, APISearch Query) { throw new NotImplementedException(); }
public bool BindModel(HttpActionContext actionContext, ModelBindingContext bindingContext) { List <string> ConditionFields = new List <string>() { "n", "o", "v", "t" }; APISearch search = new APISearch(); bindingContext.Model = search; string ct = actionContext.Request.Content.ReadAsStringAsync().Result; List <string> parameters = actionContext.Request.RequestUri.Query.TrimStart('?').Split('&').ToList(); foreach (string p in parameters) { List <string> par = p.Split('=').ToList(); switch (par[0].ToLower()) { case "p": search.page = int.Parse(par[1]); break; case "g": search.gmt = par[1]; break; case "l": search.lang = par[1]; break; case "ps": search.pagesize = int.Parse(par[1]); break; case "s": search.sortby = par[1]; break; case "d": search.sortdirection = par[1]; break; case "dd": search.datetimeasdate = (par[1].ToLower() == "true") ? true : false; break; case "gd": search.ShowDeleted = (par[1].ToLower() == "true") ? true : false; break; default: if (par[0].StartsWith("f")) { string idx = par[0].Replace("f", ""); bool complete = true; APISearchTerm term = new APISearchTerm(); term.field = par[1]; foreach (string c in ConditionFields) { string param = parameters.Where(x => x.StartsWith(c + idx)).Take(1).SingleOrDefault(); if (param != null) { List <string> condpar = param.Split('=').ToList(); switch (c) { case "n": term.not = condpar[1].ToLower() == "true" ? true: false; break; case "o": term.op = (APISearch.SearchComparer) int.Parse(condpar[1]); break; case "v": term.values = condpar[1].Split(',').ToList(); break; case "t": term.type = (Domain.Shared.DataType) int.Parse(condpar[1]); break; } } else { complete = false; break; } } if (complete) { search.filters.Add(term); } } break; } } return(true); }