public UIOMaticPagedResult GetPaged(string typeName, int itemsPerPage, int pageNumber, string sortColumn,
string sortOrder, string searchTerm)
{
var currentType = Type.GetType(typeName);
var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));
var db = (Database)DatabaseContext.Database;
if (!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
{
db = new Database(uioMaticAttri.ConnectionStringName);
}
var query = new Sql().Select("*").From(tableName.Value);
EventHandler <QueryEventArgs> tmp = BuildingQuery;
if (tmp != null)
{
tmp(this, new QueryEventArgs(currentType, tableName.Value, query));
}
if (!string.IsNullOrEmpty(searchTerm))
{
int c = 0;
foreach (var property in currentType.GetProperties())
{
//if (property.PropertyType == typeof (string))
//{
string before = "WHERE";
if (c > 0)
{
before = "OR";
}
var columnAttri =
property.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var columnName = property.Name;
if (columnAttri.Any())
{
columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
query.Append(before + " [" + columnName + "] like @0", "%" + searchTerm + "%");
c++;
//}
}
}
if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
{
query.OrderBy(sortColumn + " " + sortOrder);
}
else if (!string.IsNullOrEmpty(uioMaticAttri.SortColumn) && !string.IsNullOrEmpty(uioMaticAttri.SortOrder))
{
query.OrderBy(uioMaticAttri.SortColumn + " " + uioMaticAttri.SortOrder);
}
else
{
var primaryKeyColum = "id";
var primKeyAttri = currentType.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyAttribute));
if (primKeyAttri.Any())
{
primaryKeyColum = ((PrimaryKeyAttribute)primKeyAttri.First()).Value;
}
foreach (var property in currentType.GetProperties())
{
var keyAttri = property.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyColumnAttribute));
if (keyAttri.Any())
{
primaryKeyColum = property.Name;
}
}
query.OrderBy(primaryKeyColum + " asc");
}
EventHandler <QueryEventArgs> temp = BuildedQuery;
var qea = new QueryEventArgs(currentType, tableName.Value, query);
if (temp != null)
{
temp(this, qea);
}
var p = db.Page <dynamic>(pageNumber, itemsPerPage, qea.Query);
var result = new UIOMaticPagedResult
{
CurrentPage = p.CurrentPage,
ItemsPerPage = p.ItemsPerPage,
TotalItems = p.TotalItems,
TotalPages = p.TotalPages
};
var items = new List <object>();
foreach (dynamic item in p.Items)
{
// get settable public properties of the type
var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => x.GetSetMethod() != null);
// create an instance of the type
var obj = Activator.CreateInstance(currentType);
// set property values using reflection
var values = (IDictionary <string, object>)item;
foreach (var prop in props)
{
var columnAttri =
prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var propName = prop.Name;
if (columnAttri.Any())
{
propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
if (values.ContainsKey(propName))
{
prop.SetValue(obj, values[propName]);
}
}
items.Add(obj);
}
result.Items = items;
return(result);
}
public UIOMaticPagedResult GetQuery(UIOMaticQueryInfo queryinfo)
{
var currentType = Type.GetType(queryinfo.TypeName);
var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));
var db = (Database)DatabaseContext.Database;
if (!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
{
db = new Database(uioMaticAttri.ConnectionStringName);
}
string strTableName = tableName.Value;
if (strTableName.IndexOf("[") < 0)
{
strTableName = "[" + strTableName + "]";
}
var query = new Sql().Select("*").From(strTableName);
EventHandler <QueryEventArgs> tmp = BuildingQuery;
if (tmp != null)
{
tmp(this, new QueryEventArgs(tableName.Value, query));
}
if (queryinfo.FilterProperty != null)
{
foreach (var item in queryinfo.FilterProperty)
{
if (!string.IsNullOrWhiteSpace(item.Value) || !string.IsNullOrWhiteSpace(item.ToValue))
{
string columnName = item.Key;
var prop = currentType.GetProperties().FirstOrDefault(x => x.Name == item.Key);
if (prop != null)
{
var columnAttri = prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
if (columnAttri.Any())
{
columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
if (string.IsNullOrWhiteSpace(columnName))
{
columnName = item.Key;
}
}
if (item.Type.ToLower().Contains("datetime"))
{
if (string.IsNullOrWhiteSpace(item.Value))
{
item.Value = DateTime.MinValue.ToString("yyyy-MM-dd");
}
if (string.IsNullOrWhiteSpace(item.ToValue))
{
item.ToValue = DateTime.MaxValue.ToString("yyyy-MM-dd");
}
query.Where("[" + columnName + "] Between @0 AND @1", item.Value, item.ToValue);
}
else if (item.Type.ToLower().Contains("bool"))
{
query.Where("[" + columnName + "] = @0", item.Value);
}
else if (item.Type.ToLower().Contains("string"))
{
query.Where("[" + columnName + "] like '%" + item.Value + "%'");
}
else
{
query.Where("[" + columnName + "] " + Helper.GetOperators(item.OperatorID) + " @0", item.Value);
}
}
}
}
if (!string.IsNullOrEmpty(queryinfo.SearchTerm))
{
int c = 0;
string search = "";
foreach (var property in currentType.GetProperties())
{
//if (property.PropertyType == typeof (string))
//{
var ig =
property.GetCustomAttributes().Where(x => x.GetType() == typeof(IgnoreAttribute));
if (ig.Any())
{
continue;
}
string before = "";
if (c > 0)
{
before = " OR ";
}
var columnAttri =
property.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var columnName = property.Name;
if (columnAttri.Any())
{
columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
if (string.IsNullOrWhiteSpace(columnName))
{
columnName = property.Name;
}
search += before + " [" + columnName + "] like '%" + queryinfo.SearchTerm + "%' ";
c++;
//}
}
query.Where(search);
}
if (!string.IsNullOrEmpty(queryinfo.SortColumn) && !string.IsNullOrEmpty(queryinfo.SortOrder))
{
var sortcolumn = queryinfo.SortColumn;
string sortcolumn1 = "";
Dictionary <int, string> order = new Dictionary <int, string>();
foreach (var property in currentType.GetProperties())
{
var orderAttri = (UIOMaticSortOrderAttribute)property.GetCustomAttributes().FirstOrDefault(x => x.GetType() == typeof(UIOMaticSortOrderAttribute));
if (orderAttri != null)
{
var columnAttri =
property.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var propName = property.Name;
if (columnAttri.Any())
{
propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
if (string.IsNullOrWhiteSpace(propName))
{
propName = property.Name;
}
if (propName.IndexOf("[") < 0)
{
propName = "([" + propName + "])";
}
if (orderAttri.IsDescending)
{
propName += " desc";
}
order.Add(orderAttri.Sequence, propName);
}
if (sortcolumn == property.Name)
{
var columnAttri =
property.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
//var propName = property.Name;
if (columnAttri.Any())
{
sortcolumn1 = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
if (string.IsNullOrWhiteSpace(sortcolumn))
{
sortcolumn1 = property.Name;
}
}
}
if (sortcolumn1.IndexOf("[") < 0)
{
sortcolumn1 = "([" + sortcolumn1 + "])";
}
query.OrderBy(sortcolumn1 + " " + queryinfo.SortOrder);
var list = order.Keys.ToList();
list.Sort();
foreach (var item in list)
{
query.OrderBy(order[item]);
}
}
else
{
var primaryKeyColum = "id";
Dictionary <int, string> order = new Dictionary <int, string>();
var primKeyAttri = currentType.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyAttribute));
if (primKeyAttri.Any())
{
primaryKeyColum = ((PrimaryKeyAttribute)primKeyAttri.First()).Value;
}
foreach (var property in currentType.GetProperties())
{
var keyAttri = property.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyColumnAttribute));
if (keyAttri.Any())
{
primaryKeyColum = property.Name;
}
var orderAttri = (UIOMaticSortOrderAttribute)property.GetCustomAttributes().FirstOrDefault(x => x.GetType() == typeof(UIOMaticSortOrderAttribute));
if (orderAttri != null)
{
var columnAttri =
property.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var propName = property.Name;
if (columnAttri.Any())
{
propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
if (string.IsNullOrWhiteSpace(propName))
{
propName = property.Name;
}
if (propName.IndexOf("[") < 0)
{
propName = "([" + propName + "])";
}
if (orderAttri.IsDescending)
{
propName += " desc";
}
order.Add(orderAttri.Sequence, propName);
}
}
if (order.Count > 0)
{
var list = order.Keys.ToList();
list.Sort();
foreach (var item in list)
{
query.OrderBy(order[item]);
}
}
else
{
query.OrderBy(primaryKeyColum + " asc");
}
}
EventHandler <QueryEventArgs> temp = BuildedQuery;
if (temp != null)
{
temp(this, new QueryEventArgs(tableName.Value, query));
}
var sql = query.ToString();
try
{
var p = db.Page <dynamic>(queryinfo.PageNumber, queryinfo.ItemsPerPage, query);
var result = new UIOMaticPagedResult
{
CurrentPage = p.CurrentPage,
ItemsPerPage = p.ItemsPerPage,
TotalItems = p.TotalItems,
TotalPages = p.TotalPages
};
var items = new List <object>();
foreach (dynamic item in p.Items)
{
// get settable public properties of the type
var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => x.GetSetMethod() != null);
// create an instance of the type
var obj = Activator.CreateInstance(currentType);
// set property values using reflection
var values = (IDictionary <string, object>)item;
foreach (var prop in props)
{
//UIOMaticIgnoreFromListViewAttribute
var IsIgnoreFromListView = prop.GetCustomAttributes().Where(x => x.GetType() == typeof(IgnoreAttribute));
if (IsIgnoreFromListView.Any())
{
continue;
}
var columnAttri =
prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));
var propName = prop.Name;
if (columnAttri.Any())
{
propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
}
if (string.IsNullOrWhiteSpace(propName))
{
propName = prop.Name;
}
prop.SetValue(obj, values[propName]);
}
items.Add(obj);
}
result.Items = items;
return(result);
}
catch (Exception)
{
return(null);
}
}