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