public virtual IEnumerable <object> GetAll(string sortColumn = "", string sortOrder = "")
{
var db = GetDb();
var query = new Sql().Select("*").From(_typeInfo.TableName);
var a1 = new QueryEventArgs(_typeInfo.Type, _typeInfo.TableName, query, sortColumn, sortOrder, "", null);
UIOMaticObjectService.OnBuildingQuery(a1);
query = a1.Query;
if (!this._config.DeletedColumnName.IsNullOrWhiteSpace())
{
query.Append("WHERE " + this._config.DeletedColumnName + " = 0");
}
if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
{
query.OrderBy(sortColumn + " " + sortOrder);
}
var a2 = new QueryEventArgs(_typeInfo.Type, _typeInfo.TableName, query, sortColumn, sortOrder, "", null);
UIOMaticObjectService.OnBuiltQuery(a2);
query = a2.Query;
return(db.Fetch(_typeInfo.Type, query));
}
public virtual UIOMaticPagedResult GetPaged(
int pageNumber,
int itemsPerPage,
string searchTerm = "",
IDictionary <string, string> filters = null,
string sortColumn = "",
string sortOrder = "")
{
var numberDataTypes = new[] {
typeof(Byte),
typeof(Decimal),
typeof(Double),
typeof(Int16),
typeof(Int32),
typeof(Int64),
typeof(SByte),
typeof(Single),
typeof(UInt16),
typeof(UInt32),
typeof(Byte?),
typeof(Decimal?),
typeof(Double?),
typeof(Int16?),
typeof(Int32?),
typeof(Int64?),
typeof(SByte?),
typeof(Single?),
typeof(UInt16?),
typeof(UInt32?)
};
var guidDataTypes = new[] {
typeof(Guid),
typeof(Guid?)
};
var dateDataTypes = new[] {
typeof(DateTime),
typeof(DateTime?)
};
var boolDataTypes = new[] {
typeof(bool),
typeof(bool?)
};
var db = GetDb();
var query = new Sql().Select("*").From(_typeInfo.TableName);
var a1 = new QueryEventArgs(_typeInfo.Type, _typeInfo.TableName, query, sortColumn, sortOrder, searchTerm, filters);
UIOMaticObjectService.OnBuildingQuery(a1);
query = a1.Query;
if (!this._config.DeletedColumnName.IsNullOrWhiteSpace())
{
query.Append("WHERE " + this._config.DeletedColumnName + " = 0");
}
else
{
query.Append("WHERE 1=1");
}
// Filter by search term
if (!string.IsNullOrEmpty(searchTerm))
{
query.Append("AND (1=0");
var c = 0;
foreach (var property in _typeInfo.Type.GetProperties())
{
var attris = property.GetCustomAttributes(true);
if (attris.All(x => x.GetType() != typeof(IgnoreAttribute)))
{
var columnName = property.Name;
var columnAttri = attris.FirstOrDefault(x => x.GetType() == typeof(ColumnAttribute)) as ColumnAttribute;
if (columnAttri != null)
{
columnName = columnAttri.Name;
}
// guid
else if (guidDataTypes.Contains(property.PropertyType))
{
Guid searchGuid;
if (Guid.TryParse(searchTerm, out searchGuid))
{
query.Append("OR " + columnName + " = @0", searchGuid);
}
}
// number / boolean
else if (numberDataTypes.Contains(property.PropertyType) || boolDataTypes.Contains(property.PropertyType))
{
decimal searchNumber;
if (decimal.TryParse(searchTerm, out searchNumber))
{
query.Append("OR " + columnName + " = @0", searchNumber);
}
}
// date
else if (dateDataTypes.Contains(property.PropertyType))
{
DateTime searchDate;
if (DateTime.TryParse(searchTerm, out searchDate))
{
query.Append("OR " + columnName + " >= @0 AND " + columnName + " < @1", searchDate.Date, searchDate.AddDays(1).Date);
}
}
else if (property.PropertyType == typeof(string))
{
query.Append("OR " + columnName + " like @0", "%" + searchTerm + "%");
}
c++;
}
}
query.Append(")");
}
if (filters != null && filters.Any())
{
foreach (var filter in filters)
{
query.Append("AND " + filter.Key + " = @0", filter.Value);
}
}
// Sort
// BUG: There is a bug in the peta poco version used that errors if sort column is wrapped in [] so need to make sure it's not
if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
{
query.OrderBy(sortColumn + " " + sortOrder);
}
else if (!string.IsNullOrEmpty(_config.SortColumn) && !string.IsNullOrEmpty(_config.SortOrder))
{
query.OrderBy(_config.SortColumn + " " + _config.SortOrder);
}
else
{
var primaryKeyColum = _typeInfo.Type.GetPrimaryKeyName();
query.OrderBy(primaryKeyColum + " asc");
}
var a2 = new QueryEventArgs(_typeInfo.Type, _typeInfo.TableName, query, sortColumn, sortOrder, searchTerm, filters);
UIOMaticObjectService.OnBuiltQuery(a2);
query = a2.Query;
var p = db.Page(_typeInfo.Type, pageNumber, itemsPerPage, query);
return(new UIOMaticPagedResult
{
CurrentPage = p.CurrentPage,
ItemsPerPage = p.ItemsPerPage,
TotalItems = p.TotalItems,
TotalPages = p.TotalPages,
Items = p.Items
});
}
