public ActionResult GetAllUserRoles([FromBody] PagingClass paging)
{
string type = _globalSettings.CurrentUser.UserRoleName.FirstOrDefault();
if (type.Equals(UserRoleName.Producers))
{
var data = (from a in _userRoleRepository.GetAll(paging.pagination, paging.sort, paging.search, paging.search_operator, paging.filter).Where(x => x.Name == UserRoleName.TicketingUser || x.Name == UserRoleName.OrganizersUser)
select new
{
Id = a.Id,
Name = string.IsNullOrEmpty(_stringLocalizer.GetString(a.Name).Value) ? a.Name : _stringLocalizer.GetString(a.Name).Value,
}).Cast <object>().ToList();
return(Json(ReturnData(data)));
}
else
{
paging.filter = null;
var data = (from a in _userRoleRepository.GetAll(paging.pagination, paging.sort, paging.search, paging.search_operator, paging.filter)
select new
{
Id = a.Id,
Name = string.IsNullOrEmpty(_stringLocalizer.GetString(a.Name).Value) ? a.Name : _stringLocalizer.GetString(a.Name).Value,
}).Cast <object>().ToList();
return(Json(ReturnData(data)));
}
}
public IActionResult List([FromBody] PagingClass paging)
{
if (!_permissionUser.CheckAccess(PermissionsName.PushNotificationList))
{
return(AccessDeniedJson());
}
var notification = _pushNotificationRepositry.GetAll(paging.pagination, paging.sort, paging.search, paging.search_operator, paging.filter);
var data = _mapper.Map <List <PushNotification>, List <PushNotificationViewModel> >(notification.ToList());
data.ForEach(x =>
{
x.Child = (from a in _usersNotificationRepositry.GetByPushNotificationId(x.Id)
select new
{
UserName = a.UsersId != null ? _userRepository.GetById((int)a.UsersId).Name : _localization.GetString("Admin.UserNotFound"),
}).ToList();
});
var angularTable = new DataSourceAngular
{
data = data,
data_count = notification.TotalCount,
page_count = notification.TotalPages,
};
return(Json(angularTable));
}
public ActionResult GetAllUsers([FromBody] PagingClass paging)
{
var data = (from a in _userRepository.GetAllUsers(paging.pagination, paging.sort, paging.search, paging.search_operator, paging.filter)
select new
{
Id = a.Id,
Name = a.Name,
}).Cast <object>().ToList();
return(Json(ReturnData(data)));
}
public ActionResult GetAllUsersHasFCMToken([FromBody] PagingClass paging)
{
object all = new { Id = 1, Name = "All" };
var data = (from a in _userRepository.GetAll(paging.pagination, paging.sort, paging.search, paging.search_operator, paging.filter, UserRoleName.User).Where(x => x.FCMToken != null)
select new
{
Id = a.Id,
Name = a.Name,
}).Cast <object>().ToList();
data.Insert(0, all);
return(Json(ReturnData(data)));
}
public IActionResult List([FromBody] PagingClass paging)
{
if (!_permissionUser.CheckAccess(PermissionsName.SendEmailList))
{
return(AccessDeniedJson());
}
var angularTable = new DataSourceAngular
{
data = null,
data_count = 0,
page_count = 0,
};
return(Json(angularTable));
}
public IActionResult List([FromBody] PagingClass paging)
{
if (!_permissionUser.CheckAccess(PermissionsName.UserRoleList))
{
return(AccessDeniedJson());
}
var userRole = _userRoleRepository.GetAll(paging.pagination, paging.sort, paging.search, paging.search_operator, paging.filter);
var angularTable = new DataSourceAngular
{
data = userRole,
data_count = userRole.TotalCount,
page_count = userRole.TotalPages,
};
return(Json(angularTable));
}
public IActionResult List([FromBody] PagingClass paging, string type)
{
if (!_permissionUser.CheckAccess(PermissionsName.UserList))
{
return(AccessDeniedJson());
}
var users = _userRepository.GetAll(paging.pagination, paging.sort, paging.search, paging.search_operator, paging.filter, type);
List <UserViewModel> model = _mapper.Map <List <User>, List <UserViewModel> >(users.ToList());
if (_globalSettings.CurrentUser.UserRoleName.FirstOrDefault() == UserRoleName.Producers)
{
model = model.Where(x => x.Id == _globalSettings.CurrentUser.Id).ToList();
}
if (type == UserRoleName.Producers)
{
model.ForEach(c =>
{
c.Child = (from a in _userRepository.GetUserByProducerId(c.Id)
let s = _userRoleRepository.GetById(_userRoleMapRepository.GetAll().Where(x => x.UserId == a.Id).FirstOrDefault().UserRoleId)
select new
{
UserName = a.Name,
UserEmail = a.Email,
Status = a.Activation,
UserRoleStr = s != null ? s.Name : "",
UserId = a.Id,
}).ToList();
});
}
if (type == "both")
{
model = model.Where(a => a.ProducerId == _globalSettings.CurrentUser.Id).ToList();
}
var angularTable = new DataSourceAngular
{
data = model,
data_count = users.TotalCount,
page_count = users.TotalPages,
};
return(Json(angularTable));
}
public IActionResult List([FromBody] PagingClass paging)
{
//var localization = _stringLocalizer.GetAll();
var angularTable = new DataSourceAngular
{
data = (from a in _stringLocalizer.GetAll(paging.pagination, paging.sort, paging.search, paging.search_operator, paging.filter)
select new CustomLocalizeModel
{
Resource = a.Key,
Arabic = a.LocalizedValue["ar-AE"].ToString(),
English = a.LocalizedValue["en-US"].ToString()
}).ToList(),
data_count = 0,
page_count = 1,
};
//angularTable.data_count = angularTable.data.Count();
return(Json(angularTable));
}
/// <summary>
/// 根据当前页码、分页大小和查询条件获取数据
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">分页大小</param>
/// <param name="orderField">排序字段，默认为null</param>
/// <param name="orderDirection">排序方式：asc| desc，默认为asc</param>
/// <param name="condition">
/// <para>查询条件，默认为null，条件之间是“且”的关系</para>
/// <para>示例："Name|=|ab;Description|like|www"</para>
/// <para>相当于sql的：where Name = 'ab' and Description like '%www%'</para>
/// </param>
/// <param name="searchField">全文搜索限定字段，多个字段用英文逗号(,)隔开，默认为null：不限定</param>
/// <param name="searchText">全文搜索关键字，默认为null</param>
/// <returns></returns>
public virtual PagingClass GetDataByPagingAndSearch(
int pageIndex,
int pageSize,
string orderField = null,
string orderDirection = null,
string condition = null,
string searchField = null,
string searchText = null
)
{
System.Reflection.PropertyInfo[] PropInfoArr = typeof(T).GetProperties();
string[] PropNameArr = PropInfoArr.Select(x => x.Name).ToArray();
if (string.IsNullOrEmpty(orderField) && PropNameArr.Length > 0)
{
//将第一个字段作为默认排序字段
orderField = PropNameArr[0];
}
//处理全文搜索的条件
List <NLite.Dynamic.Filter> filtersList = new List <NLite.Dynamic.Filter>();
if (!string.IsNullOrEmpty(searchText))
{
//如果有限定全文搜索的字段，则对全文搜索进行限定
if (!string.IsNullOrEmpty(searchField))
{
//用逗号分割限定字段，在属性数组中取这些字段
string[] searchFieldArr = searchField.Split(',').Where(x => !string.IsNullOrEmpty(x)).ToArray();
PropInfoArr = PropInfoArr.Where(p => searchFieldArr.Contains(p.Name)).ToArray();
}
//将多个连续的空格替换为单个空格（包括全角空格）
searchText = Regex.Replace(searchText, @"[ ]+", " ", RegexOptions.IgnoreCase);
List <string> SearchTextList = searchText.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
for (int i = 0; i < SearchTextList.Count; i++)
{
Guid g = Guid.NewGuid();//or条件分组的唯一标识
foreach (System.Reflection.PropertyInfo pInfo in PropInfoArr)
{
NLite.Dynamic.Filter filter = new NLite.Dynamic.Filter();
filter.OrGroup = "{" + g.ToString("N") + "}";
filter.Field = pInfo.Name;
try
{
string TypeName = pInfo.PropertyType.FullName.ToLower();
//如果是数字类型
if (TypeName.Contains("int") ||
TypeName.Contains("double") ||
TypeName.Contains("float"))
{
filter.Operation = NLite.Dynamic.OperationType.Equal;
}
else
{
filter.Operation = NLite.Dynamic.OperationType.Contains;
}
//如果是可为空的泛型类型
if (pInfo.PropertyType.IsGenericType &&
pInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
{
//获取Nullable的基础类型
Type NullableType = Nullable.GetUnderlyingType(pInfo.PropertyType);
filter.Value = Convert.ChangeType(SearchTextList[i], NullableType);
}
else
{
filter.Value = Convert.ChangeType(SearchTextList[i], pInfo.PropertyType);
}
}
catch
{
continue;
}
filtersList.Add(filter);
}
}
}
if (!string.IsNullOrEmpty(condition))
{
filtersList.AddRange(GetNliteFilter(condition));
}
using (DbContext ctx = new DbContext(cfg))
{
QueryContext qctx = new QueryContext();
if (!string.IsNullOrEmpty(orderField))
{
qctx.Property = orderField.Trim();
qctx.SortOrder = (!string.IsNullOrEmpty(orderDirection) && orderDirection.ToLower() == "desc")
? NLite.SortOrder.Descending
: NLite.SortOrder.Ascending;
}
qctx.PageIndex = pageIndex < 1 ? 0 : pageIndex - 1;
qctx.PageSize = pageSize;
qctx.Data = filtersList;
IPagination <T> pagination = ctx.Set <T>().ToPagination <T>(qctx);
PagingClass Paging = new PagingClass();
Paging.PageIndex = (int)qctx.PageIndex + 1;
Paging.PageSize = pageSize;
Paging.TotalPageCount = (int)Math.Ceiling((double)pagination.TotalRowCount / pageSize);
Paging.TotalRowCount = pagination.TotalRowCount;
Paging.Data = pagination.ToList();
return(Paging);
}
}
/// <summary>
/// 对数据先分组，再分页
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">分页大小</param>
/// <param name="orderField">排序字段，若为null将由系统自动选择排序字段</param>
/// <param name="orderDirection">排序方式（ASC|DESC），若为null将使用升序（ASC）</param>
/// <param name="sqlWhereString">
/// <para>查询条件（sql语句where那一部分，不用包含where关键字）</para>
/// <para>例如：</para>
/// <para>ID > 45 and (PName like '%as%' or Quality = 'high')</para>
/// <para>请严格遵循Sql server 的sql语句规范</para></param>
/// <param name="fulltextSearchText">全文搜索的查询文本</param>
/// <param name="fulltextSearchFields">
/// <para>全文搜索限定字段（多个字段中间用英文逗号(,)分隔）</para>
/// <para>限定只在某些列中进行全文搜索</para>
/// <para>例如："PName,Quality"</para></param>
/// <returns></returns>
public virtual PagingClass GetDataByPageAndSearchWithGroupBy(
int pageIndex,
int pageSize,
string orderField = null,
string orderDirection = null,
string sqlWhereString = null,
string fulltextSearchText = null,
string fulltextSearchFields = null
)
{
System.Reflection.PropertyInfo[] PropInfoArr = typeof(T).GetProperties();
string[] PropNameArr = PropInfoArr.Select(x => x.Name).ToArray();
if (string.IsNullOrEmpty(orderField) && PropNameArr.Length > 0)
{
//将第一个字段作为默认排序字段
orderField = PropNameArr[0];
}
//Added by XiaoRui,排序
if (!string.IsNullOrEmpty(orderDirection))
{
orderDirection = (orderDirection.ToLower() == "desc") ? "desc" : "asc";
}
//拼接Sql语句
using (DbContext ctx = new DbContext(cfg))
{
string CurrentTableName = ctx.Set <T>().Mapping.TableName; //当前连接的表名
StringBuilder SBSql = new StringBuilder(); //完整的Sql语句
StringBuilder SBSqlWhere = new StringBuilder(); //where那一部分的sql语句
if (!string.IsNullOrEmpty(fulltextSearchText))
{
//先处理全文搜索的部分
//如果有限定全文搜索的字段，则对全文搜索进行限定
if (!string.IsNullOrEmpty(fulltextSearchFields))
{
//用逗号分割限定字段，将属性数组的元素限定为这些字段
string[] searchFieldArr = fulltextSearchFields.Split(',').Where(x => !string.IsNullOrEmpty(x)).ToArray();
PropInfoArr = PropInfoArr.Where(p => searchFieldArr.Contains(p.Name)).ToArray();
}
fulltextSearchText = fulltextSearchText.Replace(";", "").Replace("--", "").Replace("\'", "");
//将多个连续的空格替换为单个空格（包括全角空格）
fulltextSearchText = Regex.Replace(fulltextSearchText, @"[ ]+", " ", RegexOptions.IgnoreCase);
List <string> SearchTextList = fulltextSearchText.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
foreach (string str in SearchTextList)
{
SBSqlWhere.Append("and (");
StringBuilder sb = new StringBuilder();
foreach (PropertyInfo p in PropInfoArr)
{
sb.AppendFormat("or {0} like N'%{1}%'", p.Name, str);
}
sb.Remove(0, 3);//将最开始的or关键字和空格移除
SBSqlWhere.Append(sb);
SBSqlWhere.Append(")");
}
if (!string.IsNullOrEmpty(SBSqlWhere.ToString()))
{
//将最开始的and关键字和空格移除
SBSqlWhere = SBSqlWhere.Remove(0, 4);
//把整个放在括号里
SBSqlWhere.Insert(0, "and (");
SBSqlWhere.Append(")");
//SBSqlWhere.Insert(0, "where "); //在最开始添加where关键字
}
}
if (!string.IsNullOrEmpty(sqlWhereString))
{
//把整个放括号里
sqlWhereString = " and (" + sqlWhereString + ")";
SBSqlWhere.Append(sqlWhereString);
}
if (!string.IsNullOrEmpty(SBSqlWhere.ToString()))
{
//将最开始的and关键字和空格移除，并添加where关键字
SBSqlWhere.Remove(0, 4);
SBSqlWhere.Insert(0, "where ");
}
pageIndex = pageIndex < 1 ? 1 : pageIndex;
int StartRowIndex = (pageIndex - 1) * pageSize + 1;
int EndRowIndex = pageIndex * pageSize;
//用于GROUP BY 的字段
string keyItems = "MaterialId, MaterialCName, MaterialEName, Mode,UnitId, UnitCName, UnitEName, TotalQuantity,Location, " +
"LargeClassCName, LargeClassEName, MiddleClassCName, MiddleClassEName, SmallClassCName, SmallClassEName, ItemCName, ItemEName, MaterialDesc, Remark";
//拼装Sql语句
//SBSql.Append("select cnt.totalcnt,basetbl.* from (");
//SBSql.AppendFormat("select row_number() over(order by {0} {3}) as rowindex, {4} from {1} {2}",
// orderField, CurrentTableName, SBSqlWhere.ToString(), orderDirection == null ? "asc" : orderDirection,keyItems);
//SBSql.AppendFormat(" group by {0}) as basetbl,(",keyItems);
//SBSql.AppendFormat("select count(1) as totalcnt from {0} {1}", CurrentTableName, SBSqlWhere.ToString());
//SBSql.AppendFormat(" group by {0}) as cnt",keyItems);
//SBSql.AppendFormat(" where basetbl.rowindex >={0} and basetbl.rowindex <={1}", StartRowIndex, EndRowIndex);
SBSql.AppendFormat("select * from (select ROW_NUMBER() over(order by {0} {1}) as rowindex,(select COUNT(*) from (select MaterialId from {2} {3}" +
" group by {4} ) as t) as totalcnt,{4} from {2} {3} group by {4} ) as t1 where t1.rowindex >={5} and t1.rowindex <={6}",
orderField, orderDirection == null ? "asc" : orderDirection, CurrentTableName, SBSqlWhere.ToString(), keyItems, StartRowIndex, EndRowIndex);
//SBSql.AppendFormat("select cnt.totalcnt,basetbl.* from (select row_number() over(order by DeptMaterialID asc) as rowindex, {0}, 0 AS DeptQuantity " +
// " from uv_whDeptMaterial where (DeptId<>{1} group by {0})) as basetbl,(select count(1) as totalcnt from uv_whDeptMaterial where (DeptId<>{1} group by {0})) " +
// "as cnt where basetbl.rowindex >={2} and basetbl.rowindex <={3}", keyItems, sqlWhereString, StartRowIndex, EndRowIndex);
System.Data.DataTable ResultDT = ctx.DbHelper.ExecuteDataTable(SBSql.ToString());
//获得总记录数后，删除多余的列（totalcnt, rowindex）
int TotalRowCount = ResultDT.Rows.Count == 0 ? 0 : Convert.ToInt32(ResultDT.Rows[0]["totalcnt"]);
ResultDT.Columns.Remove("totalcnt");
ResultDT.Columns.Remove("rowindex");
PagingClass Paging = new PagingClass();
Paging.Data = ResultDT.ToGenericList <T>();
Paging.PageIndex = pageIndex;
Paging.PageSize = pageSize;
Paging.TotalPageCount = (int)Math.Ceiling((double)TotalRowCount / pageSize);
Paging.TotalRowCount = TotalRowCount;
if (Paging.PageIndex > Paging.TotalPageCount)
{
Paging.PageIndex = Paging.TotalPageCount;
}
return(Paging);
}
}
/// <summary>
/// 根据页码、分页大小和查询条件获取数据
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">分页大小</param>
/// <param name="orderField">排序字段，若为null将由系统自动选择排序字段</param>
/// <param name="orderDirection">排序方式（ASC|DESC），若为null将使用升序（ASC）</param>
/// <param name="sqlWhereString">
/// <para>查询条件（sql语句where那一部分，不用包含where关键字）</para>
/// <para>例如：</para>
/// <para>ID > 45 and (PName like '%as%' or Quality = 'high')</para>
/// <para>请严格遵循Sql server 的sql语句规范</para></param>
/// <param name="fulltextSearchText">全文搜索的查询文本</param>
/// <param name="fulltextSearchFields">
/// <para>全文搜索限定字段（多个字段中间用英文逗号(,)分隔）</para>
/// <para>限定只在某些列中进行全文搜索</para>
/// <para>例如："PName,Quality"</para></param>
/// <returns></returns>
public virtual PagingClass GetDataByPageAndSearch(
int pageIndex,
int pageSize,
string orderField = null,
string orderDirection = null,
string sqlWhereString = null,
string fulltextSearchText = null,
string fulltextSearchFields = null
)
{
System.Reflection.PropertyInfo[] PropInfoArr = typeof(T).GetProperties();
string[] PropNameArr = PropInfoArr.Select(x => x.Name).ToArray();
if (PropNameArr.Length == 0)
{
throw new ArgumentNullException("对象为空引用");
}
string[] orderDirectionArray = null;
if (!string.IsNullOrEmpty(orderDirection))
{
orderDirectionArray = orderDirection.Split(',');
}
string orderSQL = string.Empty;
if (string.IsNullOrEmpty(orderField))
{
//将第一个字段作为默认排序字段
orderField = PropNameArr[0];
if (orderDirectionArray == null)
{
orderSQL = string.Format(" {0} ASC ", orderField);
}
else
{
orderSQL = string.Format(" {0} {1} ", orderField, orderDirectionArray[0]);
}
}
else
{
StringBuilder orderBuilder = new StringBuilder();
string[] fieldArray = orderField.Split(',');
for (int i = 0; i < fieldArray.Length; i++)
{
string orderStr = "ASC";
if (orderDirectionArray != null && orderDirectionArray.Length > 0)
{
if (i < orderDirectionArray.Length)
{
orderStr = orderDirectionArray[i];
}
}
if (i == fieldArray.Length - 1)
{
orderBuilder.AppendFormat(" {0} {1} ", fieldArray[i], orderStr);
}
else
{
orderBuilder.AppendFormat(" {0} {1}, ", fieldArray[i], orderStr);
}
}
orderSQL = orderBuilder.ToString();
}
//拼接Sql语句
using (DbContext ctx = new DbContext(cfg))
{
string CurrentTableName = ctx.Set <T>().Mapping.TableName; //当前连接的表名
StringBuilder SBSql = new StringBuilder(); //完整的Sql语句
StringBuilder SBSqlWhere = new StringBuilder(); //where那一部分的sql语句
if (!string.IsNullOrEmpty(fulltextSearchText))
{
//先处理全文搜索的部分
//如果有限定全文搜索的字段，则对全文搜索进行限定
if (!string.IsNullOrEmpty(fulltextSearchFields))
{
//用逗号分割限定字段，将属性数组的元素限定为这些字段
string[] searchFieldArr = fulltextSearchFields.Split(',').Where(x => !string.IsNullOrEmpty(x)).ToArray();
PropInfoArr = PropInfoArr.Where(p => searchFieldArr.Contains(p.Name)).ToArray();
}
fulltextSearchText = fulltextSearchText.Replace(";", "").Replace("--", "").Replace("\'", "");
//将多个连续的空格替换为单个空格（包括全角空格）
fulltextSearchText = Regex.Replace(fulltextSearchText, @"[ ]+", " ", RegexOptions.IgnoreCase);
List <string> SearchTextList = fulltextSearchText.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
foreach (string str in SearchTextList)
{
SBSqlWhere.Append("and (");
StringBuilder sb = new StringBuilder();
foreach (PropertyInfo p in PropInfoArr)
{
sb.AppendFormat("or {0} like N'%{1}%'", p.Name, str);
}
sb.Remove(0, 3);//将最开始的or关键字和空格移除
SBSqlWhere.Append(sb);
SBSqlWhere.Append(")");
}
if (!string.IsNullOrEmpty(SBSqlWhere.ToString()))
{
//将最开始的and关键字和空格移除
SBSqlWhere = SBSqlWhere.Remove(0, 4);
//把整个放在括号里
SBSqlWhere.Insert(0, "and (");
SBSqlWhere.Append(")");
//SBSqlWhere.Insert(0, "where "); //在最开始添加where关键字
}
}
if (!string.IsNullOrEmpty(sqlWhereString))
{
//把整个放括号里
sqlWhereString = " and (" + sqlWhereString + ")";
SBSqlWhere.Append(sqlWhereString);
}
if (!string.IsNullOrEmpty(SBSqlWhere.ToString()))
{
//将最开始的and关键字和空格移除，并添加where关键字
SBSqlWhere.Remove(0, 4);
SBSqlWhere.Insert(0, "where ");
}
pageIndex = pageIndex < 1 ? 1 : pageIndex;
int StartRowIndex = (pageIndex - 1) * pageSize + 1;
int EndRowIndex = pageIndex * pageSize;
//拼装Sql语句
SBSql.Append("select cnt.totalcnt,basetbl.* from (");
SBSql.AppendFormat("select row_number() over(order by {0}) as rowindex, * from {1} {2}",
orderSQL, CurrentTableName, SBSqlWhere.ToString());
SBSql.Append(") as basetbl,(");
SBSql.AppendFormat("select count(1) as totalcnt from {0} {1}", CurrentTableName, SBSqlWhere.ToString());
SBSql.Append(") as cnt");
SBSql.AppendFormat(" where basetbl.rowindex >={0} and basetbl.rowindex <={1}", StartRowIndex, EndRowIndex);
System.Data.DataTable ResultDT = ctx.DbHelper.ExecuteDataTable(SBSql.ToString());
//获得总记录数后，删除多余的列（totalcnt, rowindex）
int TotalRowCount = ResultDT.Rows.Count == 0 ? 0 : Convert.ToInt32(ResultDT.Rows[0]["totalcnt"]);
ResultDT.Columns.Remove("totalcnt");
ResultDT.Columns.Remove("rowindex");
PagingClass Paging = new PagingClass();
Paging.Data = ResultDT.ToGenericList <T>();
Paging.PageIndex = pageIndex;
Paging.PageSize = pageSize;
Paging.TotalPageCount = (int)Math.Ceiling((double)TotalRowCount / pageSize);
Paging.TotalRowCount = TotalRowCount;
if (Paging.PageIndex > Paging.TotalPageCount)
{
Paging.PageIndex = Paging.TotalPageCount;
}
return(Paging);
}
}
/// <summary>
/// 根据查询条件获取数据
/// </summary>
/// <param name="orderField">排序字段，若为null将由系统自动选择排序字段</param>
/// <param name="orderDirection">排序方式（ASC|DESC），若为null将使用升序（ASC）</param>
/// <param name="sqlWhereString">
/// <para>查询条件（sql语句where那一部分，不用包含where关键字）</para>
/// <para>例如：</para>
/// <para>ID > 45 and (PName like '%as%' or Quality = 'high')</para>
/// <para>请严格遵循Sql server 的sql语句规范</para></param>
/// <param name="fulltextSearchText">全文搜索的查询文本</param>
/// <param name="fulltextSearchFields">
/// <para>全文搜索限定字段（多个字段中间用英文逗号(,)分隔）</para>
/// <para>限定只在某些列中进行全文搜索</para>
/// <para>例如："PName,Quality"</para></param>
/// <returns></returns>
public virtual PagingClass GetDataBySearch(
string orderField = null,
string orderDirection = null,
string sqlWhereString = null,
string fulltextSearchText = null,
string fulltextSearchFields = null
)
{
System.Reflection.PropertyInfo[] PropInfoArr = typeof(T).GetProperties();
string[] PropNameArr = PropInfoArr.Select(x => x.Name).ToArray();
if (string.IsNullOrEmpty(orderField) && PropNameArr.Length > 0)
{
//将第一个字段作为默认排序字段
orderField = PropNameArr[0];
}
//拼接Sql语句
using (DbContext ctx = new DbContext(cfg))
{
string CurrentTableName = ctx.Set <T>().Mapping.TableName; //当前连接的表名
StringBuilder SBSql = new StringBuilder(); //完整的Sql语句
StringBuilder SBSqlWhere = new StringBuilder(); //where那一部分的sql语句
if (!string.IsNullOrEmpty(fulltextSearchText))
{
//先处理全文搜索的部分
//如果有限定全文搜索的字段，则对全文搜索进行限定
if (!string.IsNullOrEmpty(fulltextSearchFields))
{
//用逗号分割限定字段，将属性数组的元素限定为这些字段
string[] searchFieldArr = fulltextSearchFields.Split(',').Where(x => !string.IsNullOrEmpty(x)).ToArray();
PropInfoArr = PropInfoArr.Where(p => searchFieldArr.Contains(p.Name)).ToArray();
}
fulltextSearchText = fulltextSearchText.Replace(";", "").Replace("--", "").Replace("\'", "");
//将多个连续的空格替换为单个空格（包括全角空格）
fulltextSearchText = Regex.Replace(fulltextSearchText, @"[ ]+", " ", RegexOptions.IgnoreCase);
List <string> SearchTextList = fulltextSearchText.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
foreach (string str in SearchTextList)
{
SBSqlWhere.Append("and (");
StringBuilder sb = new StringBuilder();
foreach (PropertyInfo p in PropInfoArr)
{
sb.AppendFormat("or {0} like N'%{1}%'", p.Name, str);
}
sb.Remove(0, 3);//将最开始的or关键字和空格移除
SBSqlWhere.Append(sb);
SBSqlWhere.Append(")");
}
if (!string.IsNullOrEmpty(SBSqlWhere.ToString()))
{
//将最开始的and关键字和空格移除
SBSqlWhere = SBSqlWhere.Remove(0, 4);
//把整个放在括号里
SBSqlWhere.Insert(0, "and (");
SBSqlWhere.Append(")");
//SBSqlWhere.Insert(0, "where "); //在最开始添加where关键字
}
}
if (!string.IsNullOrEmpty(sqlWhereString))
{
//把整个放括号里
sqlWhereString = " and (" + sqlWhereString + ")";
SBSqlWhere.Append(sqlWhereString);
}
if (!string.IsNullOrEmpty(SBSqlWhere.ToString()))
{
//将最开始的and关键字和空格移除，并添加where关键字
SBSqlWhere.Remove(0, 4);
SBSqlWhere.Insert(0, "where ");
}
//拼装Sql语句
SBSql.Append("select cnt.totalcnt,basetbl.* from (");
SBSql.AppendFormat("select row_number() over(order by {0}) as rowindex, * from {1} {2}",
orderField, CurrentTableName, SBSqlWhere.ToString());
SBSql.Append(") as basetbl,(");
SBSql.AppendFormat("select count(1) as totalcnt from {0} {1}", CurrentTableName, SBSqlWhere.ToString());
SBSql.Append(") as cnt");
System.Data.DataTable ResultDT = ctx.DbHelper.ExecuteDataTable(SBSql.ToString());
//获得总记录数后，删除多余的列（totalcnt, rowindex）
int TotalRowCount = ResultDT.Rows.Count == 0 ? 0 : Convert.ToInt32(ResultDT.Rows[0]["totalcnt"]);
ResultDT.Columns.Remove("totalcnt");
ResultDT.Columns.Remove("rowindex");
PagingClass Paging = new PagingClass();
Paging.Data = ResultDT.ToGenericList <T>();
Paging.TotalRowCount = TotalRowCount;
if (Paging.PageIndex > Paging.TotalPageCount)
{
Paging.PageIndex = Paging.TotalPageCount;
}
return(Paging);
}
}
