//处理排序 "@order":"name-,id"查询按 name降序、id默认顺序 排序的User数组
private void ProcessOrder(string subtable, JObject values, ISugarQueryable <ExpandoObject> tb)
{
if (values["@order"].IsValue())
{
foreach (var item in values["@order"].ToString().Split(','))
{
string col = item.Replace("-", "").Replace("+", "");
if (IsCol(subtable, col))
{
if (item.EndsWith("-"))
{
tb.OrderBy($"{col} desc");
}
else if (item.EndsWith("+"))
{
tb.OrderBy($"{col} asc");
}
else
{
tb.OrderBy($"{col}");
}
}
}
}
}
public static PagedInfo <T> ToPage <T>(this ISugarQueryable <T> source, Expression <Func <T, bool> > where, PageParm parm)
{
source = source.Where(where);
var page = new PagedInfo <T>();
var total = source.Count();
page.TotalCount = total;
page.TotalPages = total / parm.PageSize;
if (total % parm.PageSize > 0)
{
page.TotalPages++;
}
page.PageSize = parm.PageSize;
page.PageIndex = parm.PageIndex;
//排序字段新增中括号（[]）防止命名为数据库内置字段报错
//新增多字段排序
if (!string.IsNullOrEmpty(parm.OrderBy))
{
string[] orderbys = parm.OrderBy.Split(',');
string[] sorts = parm.Sort.Split(',');
for (int i = 0; i < orderbys.Length; i++)
{
string sort = sorts.Length == orderbys.Length ? sorts[i] : "asc";
source.OrderBy($"[{orderbys[i]}] {(sort == "desc" ? "desc" : "asc")}");
}
}
page.DataSource = source.ToPageList(parm.PageIndex, parm.PageSize);
return(page);
}
/// <summary>
/// 获取用户集合，包含角色id
/// </summary>
/// <param name="name">用户名</param>
/// <param name="roleId">角色id</param>
/// <param name="pageSize">页大小</param>
/// <param name="pageIndex">页索引</param>
/// <returns></returns>
public ListResult <object> GetUsersRefRole(string name, int roleId, int pageSize, int pageIndex)
{
ListResult <object> result = new ListResult <object>
{
Result = new List <object>()
};
int totalRs = 0;
MenuManager MenuManager = new MenuManager();
ISugarQueryable <Enties.Users> u1 = MenuManager.Db.Queryable <Enties.Users>().WhereIF(!string.IsNullOrEmpty(name), u => SqlFunc.Contains(u.NickName, name) || SqlFunc.Contains(u.UserName, name));
ISugarQueryable <Enties.UserRole> ud1 = MenuManager.Db.Queryable <Enties.UserRole>().Where(ud => ud.RoleId == roleId);
var lt = MenuManager.Db.Queryable(u1, ud1, JoinType.Left, (j1, j2) => j1.UsersId == j2.UserId).OrderBy((j1, j2) => j2.RoleId, OrderByType.Desc).Select((j1, j2) => new { j1.UsersId, j1.UserName, j1.NickName, j2.RoleId }).ToPageList(pageIndex, pageSize, ref totalRs);
if (totalRs > 0)
{
result.Result = new List <object>();
foreach (var u in lt)
{
result.Result.Add(u);
}
}
result.PageSize = pageSize;
result.PageIndex = pageIndex;
result.Total = totalRs;
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="roleId"></param>
/// <returns></returns>
public async Task <ISugarQueryable <PermissionDto> > GetPermissionByRole(int roleId, string permissionType)
{
ISugarQueryable <PermissionDto> permissions = null;
if (permissionType == SysConst.MENU)
{
permissions = _db.Queryable <SysRole, SysRolePermission, SysPermission, SysPermissionMenu, SysMenu>((r, rp, p, pm, m) => new object[]
{
JoinType.Inner, r.Id == rp.RoleId,
JoinType.Inner, rp.PermissionId == p.Id,
JoinType.Inner, p.Id == pm.PermissionId,
JoinType.Inner, pm.MenuId == m.Id
}).Where((r, rp, p, pm, m) => r.Id == roleId && p.Type == permissionType).Select((r, rp, p, pm, m) => new PermissionDto
{
Id = p.Id,
ParentId = m.ParentId
});
}
else if (permissionType == SysConst.OPERATION)
{
permissions = _db.Queryable <SysRole, SysRolePermission, SysPermission, SysPermissionOperation, SysOperation>((r, rp, p, po, o) => new object[]
{
JoinType.Inner, r.Id == rp.RoleId,
JoinType.Inner, rp.PermissionId == p.Id,
JoinType.Inner, p.Id == po.PermissionId,
JoinType.Inner, po.OperationId == o.Id
}).Where((r, rp, p, po, o) => r.Id == roleId && p.Type == permissionType).Select((r, rp, p, po, o) => new PermissionDto
{
Id = p.Id,
ParentId = o.ParentId
});
}
return(await Task.Run(() => permissions));
}
/// <summary>
///
/// </summary>
/// <param name="queryable"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static IPagedResponseDataDto <T> ToPageList <T>(this ISugarQueryable <T> queryable, int pageIndex, int pageSize)
{
int total = 0;
var list = queryable.ToPageList(pageIndex, pageSize, ref total);
return(new PagedResponseDataDto <T>(list, total, pageIndex, pageSize));
}
public async Task <string> List([FromForm] PubParams.MesTaskBootstrapParams bootstrap)
{
ISugarQueryable <Wms_mestask> query = _client.Queryable <Wms_mestask>();
if (!string.IsNullOrWhiteSpace(bootstrap.search))
{
query = query.Where(x => x.WarehousingId.ToString().Contains(bootstrap.search) || x.BatchPlanId.Contains(bootstrap.search));
}
DateTime minDate;
if (!string.IsNullOrWhiteSpace(bootstrap.datemin) && DateTime.TryParse(bootstrap.datemin, out minDate))
{
query = query.Where(x => x.ModifiedDate >= minDate || x.CreateDate >= minDate);
}
DateTime maxDate;
if (!string.IsNullOrWhiteSpace(bootstrap.datemax) && DateTime.TryParse(bootstrap.datemax, out maxDate))
{
query = query.Where(x => x.ModifiedDate <= maxDate || x.CreateDate <= maxDate);
}
query = query.Sort <Wms_mestask>(new string[] { bootstrap.sort + " " + bootstrap.order });
//Order
RefAsync <int> totalCount = new RefAsync <int>();
List <Wms_mestask> result = await query.ToPageListAsync(bootstrap.pageIndex, bootstrap.limit, totalCount);
return(new PageGridData(result.ToArray(), totalCount.Value).JilToJson());
}
/// <summary>
/// 分页结果
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static async Task <IPagedResponseDataDto <T> > ToPageListAsync <T>(this ISugarQueryable <T> queryable, int pageIndex, int pageSize)
{
RefAsync <int> total = new RefAsync <int>();
var list = await queryable.ToPageListAsync(pageIndex, pageSize, total);
return(new PagedResponseDataDto <T>(list, total.Value, pageIndex, pageSize));
}
public ActionResult AjaxList(jQueryDataTableParamModel param)
{
ISugarQueryable <P_CommodityType> sqable = SugarFactory.GetInstance().Queryable <P_CommodityType>();
//获取总记录数
this.TotalRecordCount = sqable.Count();
this.PageSize = 15;
int allcount = this.TotalRecordCount;
List <P_CommodityType> CommodityAll = sqable.ToPageList(param.iDisplayStart / param.iDisplayLength + 1, this.PageSize, ref allcount);
//转化数据格式
var result = from m in CommodityAll
select new[] {
m.Type_Code.ToString(),
m.Type_Name,
};
//返回json数据
return(Json(
new
{
sEcho = param.sEcho,
iTotalRecords = this.TotalRecordCount,
iTotalDisplayRecords = this.TotalRecordCount,
aaData = result
}
));
}
/// <summary>
/// 对查询的命令进行自定义的属性扩展
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sugarQueryable"></param>
/// <returns></returns>
public static ISugarQueryable <T> QueryableHandleAttribute <T>(this ISugarQueryable <T> sugarQueryable) where T : class, new()
{
/*
* 这组反射一般可以拿到外边，通过传入的方式，这样可以提高数据库执行效率
* 但是放在这里，可以做到更好的自定义与扩展
*/
var propIgnores = PropertyExtension.GetPropertyByAttribute <T, IgnoreAttribute>()?.Select(x => x.Name);
var orderByAscs = PropertyExtension.GetPropertyByAttribute <T, OrderByAttribute>()?.Select(x => x.Name);
var orderByDescs = PropertyExtension.GetPropertyByAttribute <T, OrderByDescAttribute>()?.Select(x => x.Name);
//对标记忽略的字段进行过滤
if (propIgnores != null && propIgnores.Any())
{
sugarQueryable = sugarQueryable.IgnoreColumns(propIgnores.ToArray());
}
//按正序标记进行排序
if (orderByAscs != null && orderByAscs.Any())
{
sugarQueryable = sugarQueryable.OrderBy($" {string.Join(",", orderByAscs)} asc ");
}
//按倒序标记进行排序
if (orderByDescs != null && orderByDescs.Any())
{
sugarQueryable = sugarQueryable.OrderBy($" {string.Join(",", orderByDescs)} desc ");
}
return(sugarQueryable);
}
/// <summary>
/// 评论获得的赞
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private ISugarQueryable <VueUserPraize> sql_UserCommentPraize(QUserPraize query)
{
ISugarQueryable <VueUserPraize> prSql = Db.Queryable <EPraize_Comment, EComment_Res, EBookInfo>((p, c, b) => new object[]
{
JoinType.Inner, p.commentId == c.Id,
JoinType.Inner, c.parentRefCode == b.Code
})
.Where(p => p.userId == query.userId)
.OrderBy(p => p.praizeDate, OrderByType.Desc)
.Select((p, c, b) => new VueUserPraize
{
id = p.Id,
CreateDateTime = p.praizeDate,
code = c.Id.ToString(),
resCode = c.refCode,
commentId = c.Id,
bookCode = b.Code,
bookName = b.Title,
bookUrl = b.CoverUrl,
content = c.content,
});
return(prSql);
}
public async Task <List <TEntity> > QueryPageAsync(ISugarQueryable <TEntity> quey, Expression <Func <TEntity, bool> > whereExpression,
int intPageIndex = 0, int intPageSize = 20, string strOrderByFileds = null, bool isAsc = true)
{
return(await quey
.WhereIF(whereExpression != null, whereExpression)
.OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds)
.ToPageListAsync(intPageIndex, intPageSize));
}
protected virtual void ApplyQueryFilterList(ISugarQueryable <TEntity> queryable)
{
if (_filters[GetFilterIndex(nameof(ISoftDelete))].IsEnabled && GetInterfacesOfTEntity().Contains(typeof(ISoftDelete)))
{
var dbColumnName = Context.EntityMaintenance.GetDbColumnName <TEntity>(nameof(ISoftDelete.IsDeleted));
queryable = queryable.Where($"{dbColumnName} = 0");
}
}
/// <summary>
///
/// </summary>
/// <param name="isWhere"></param>
/// <param name="expression"></param>
/// <returns></returns>
public IWQueryable <T> WhereIF(bool isWhere, Expression <Func <T, bool> > expression)
{
if (isWhere)
{
_queryable = _queryable.Where(expression);
}
return(this);
}
/// <summary>
/// SqlSugar page
/// </summary>
/// <param name="query"></param>
/// <param name="currentPageNumber"></param>
/// <param name="pageSize"></param>
/// <param name="totalMemberCount"></param>
public SqlSugarPage(ISugarQueryable <T> query, int currentPageNumber, int pageSize, int totalMemberCount) : base()
{
var skip = (currentPageNumber - 1) * pageSize;
var state = new SqlSugarQueryState <T>(query, currentPageNumber, pageSize);
InitializeMetaInfo()(currentPageNumber)(pageSize)(totalMemberCount)(skip)();
base._initializeAction = InitializeMemberList()(state)(CurrentPageSize)(skip);
}
/// <summary>
///
/// </summary>
/// <param name="isWhere"></param>
/// <param name="expression"></param>
/// <returns></returns>
public IWQueryable <T> WhereIF(bool isWhere, string expression)
{
if (isWhere)
{
_queryable = _queryable.Where(expression);
}
return(this);
}
/// <summary>
///
/// </summary>
/// <param name="isOrderBy"></param>
/// <param name="expression"></param>
/// <param name="isAsc"></param>
/// <returns></returns>
public IWQueryable <T> OrderByIF(bool isOrderBy, Expression <Func <T, object> > expression, bool isAsc = true)
{
if (isOrderBy)
{
_queryable = _queryable.OrderBy(expression, isAsc ? OrderByType.Asc : OrderByType.Desc);
}
return(this);
}
public static ISugarQueryable <T, T2, T3, T4, T5, T6, T7> GetQueryable <T, T2, T3, T4, T5, T6, T7>(ConnectionConfig currentConnectionConfig)
{
string className = "Queryable";
className = GetClassName(currentConnectionConfig.DbType.ToString(), className);
ISugarQueryable <T, T2, T3, T4, T5, T6, T7> result = CreateInstance <T, T2, T3, T4, T5, T6, T7, ISugarQueryable <T, T2, T3, T4, T5, T6, T7> >(className);
return(result);
}
public static ISugarQueryable <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12> GetQueryable <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12>(ConnectionConfig currentConnectionConfig)
{
string className = "Queryable";
className = GetClassName(currentConnectionConfig.DbType, className);
ISugarQueryable <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12> result = CreateInstance <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, ISugarQueryable <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12> >(className);
return(result);
}
public ISugarQueryable <T, T2, T3> Queryable <T, T2, T3>(ISugarQueryable <T> joinQueryable1, ISugarQueryable <T2> joinQueryable2, ISugarQueryable <T3> joinQueryable3,
JoinType joinType1, Expression <Func <T, T2, T3, bool> > joinExpression1,
JoinType joinType2, Expression <Func <T, T2, T3, bool> > joinExpression2)
where T : class, new()
where T2 : class, new()
where T3 : class, new()
{
return(this.Context.Queryable(joinQueryable1, joinQueryable2, joinQueryable3, joinType1, joinExpression1, joinType2, joinExpression2).With(SqlWith.Null));
}
public static T Single <T>(this ISugarQueryable <T> queryable, Expression <Func <T, bool> > expression, T defaultValue) where T : class, new()
{
T result = queryable.Single(expression);
if (result == null)
{
return(defaultValue);
}
return(result);
}
public static T First <T>(this ISugarQueryable <T> queryable, T defaultValue)
{
T result = queryable.First();
if (result == null)
{
return(defaultValue);
}
return(result);
}
public static T First <T>(this ISugarQueryable <T> queryable, Expression <Func <T, bool> > expression, T defaultValue)
{
T result = queryable.First(expression);
if (result == null)
{
return(defaultValue);
}
return(result);
}
public ISugarQueryable <T> GetSugarQueryable(Expression <Func <T, bool> > filter = null)
{
ISugarQueryable <T> query = Db.Queryable <T>();
if (filter != null)
{
query = query.Where(filter);
}
return(query);
}
public static T Single <T>(this ISugarQueryable <T> queryable, T defaultValue) where T : class, new()
{
T result = queryable.Single();
if (result == null)
{
return(defaultValue);
}
return(result);
}
public ISugarQueryable <T> Queryable <T>(ISugarQueryable <T> queryable) where T : class, new()
{
var result = this.Context.Queryable <T>(queryable);
var QueryBuilder = queryable.QueryBuilder;
result.QueryBuilder.WhereIndex = QueryBuilder.WhereIndex++;
result.QueryBuilder.LambdaExpressions.ParameterIndex = QueryBuilder.LambdaExpressions.ParameterIndex++;
result.QueryBuilder.LambdaExpressions.Index = QueryBuilder.LambdaExpressions.Index++;
return(result);
}
public ActionResult AjaxClassDetailList(jQueryDataTableParamModel param)
{
ISugarQueryable <P_ConsumptionInfo, P_CommodityInfo, P_MerchantInfo> sqable = SugarFactory.GetInstance().Queryable <P_ConsumptionInfo, P_CommodityInfo, P_MerchantInfo>((ct, ci, mi) => new object[] { JoinType.Left, ct.Barcode == ci.BarCode, JoinType.Left, ct.Merchant_Code == mi.Merchant_Code });
if (!String.IsNullOrEmpty(Request.Params.Get("start_time")))
{
sqable.Where(ct => ct.Consumption_Time >= Convert.ToDateTime(Request.Params.Get("start_time")));
}
if (!String.IsNullOrEmpty(Request.Params.Get("end_time")))
{
sqable.Where(ct => ct.Consumption_Time <= Convert.ToDateTime(Request.Params.Get("end_time")));
}
if (!String.IsNullOrEmpty(Request.Params.Get("chname")))
{
sqable.Where(mi => mi.Merchant_Name.Contains(Request.Params.Get("chname")));
}
if (!String.IsNullOrEmpty(Request.Params.Get("barcode")))
{
sqable.Where(ct => ct.Barcode == Request.Params.Get("barcode"));
}
//获取总记录数
this.TotalRecordCount = sqable.Count();
this.PageSize = 15;
int allcount = this.TotalRecordCount;
List <P_ConsumptionInfo> ciAll = sqable.OrderBy(ct => ct.Consumption_Time, SqlSugar.OrderByType.Desc).Select((ct, ci, mi) => new P_ConsumptionInfo {
Barcode = ct.Barcode, Consumption_Money = ct.Consumption_Money, Consumption_Time = ct.Consumption_Time, Commodity_Name = ci.Commodity_Name, Merchant_Name = mi.Merchant_Name
}).ToPageList(param.iDisplayStart / param.iDisplayLength + 1, this.PageSize, ref allcount);
//转化数据格式
var result = from m in ciAll
select new[] {
m.Id.ToString(),
m.Merchant_Name,
m.Barcode,
m.Commodity_Name,
m.Consumption_Money.ToString(),
m.Consumption_Time.Value.ToString()
};
//返回json数据
return(Json(
new
{
sEcho = param.sEcho,
iTotalRecords = this.TotalRecordCount,
iTotalDisplayRecords = this.TotalRecordCount,
aaData = result
}
));
}
/// <summary>
/// 查询汇总条数
/// </summary>
/// <typeparam name="T">表实体</typeparam>
/// <param name="where">查询条件,如 "id=@id"</param>
/// <param name="parameters">条件参数，如new SugarParameter("@id",1) 或 new { id = 1 }</param>
/// <returns></returns>
public int GetCount <T>(string where, object parameters) where T : class, new()
{
ISugarQueryable <T> r = _db.Queryable <T>();
if (!string.IsNullOrEmpty(where))
{
r = r.Where(where, parameters);
}
return(r.Count());
}
/// <summary>
/// 查询列表，返回分页结果
/// </summary>
/// <param name="data">查询条件</param>
/// <param name="pageModel">分页实体</param>
/// <returns>返回结果</returns>
public List <DST_PATIENT_INFO> GetPageListByCondition(DST_PATIENT_INFO data, CustomPageModel pageModel)
{
int totalNum = 0;
int totalPage = 1;
string sql = string.Format(@"SELECT M.* FROM DST_PATIENT_INFO M WHERE 1=1");
if (!string.IsNullOrEmpty(data.SLIDE_ID))
{
sql += string.Format(" AND M.SLIDE_ID like '%{0}%'", data.SLIDE_ID);
}
if (!string.IsNullOrEmpty(data.NAME))
{
sql += string.Format(" AND M.name like '%{0}%'", data.NAME);
}
if (data.SAMPLING_DATE != null && data.SAMPLING_DATE.Value != DateTime.MinValue)
{
sql += string.Format(" AND date(M.SAMPLING_DATE) = '{0}'", data.SAMPLING_DATE.Value.ToString("yyyy-MM-dd"));
}
if (!string.IsNullOrEmpty(data.ITEM_NAME))
{
sql += string.Format(" AND M.ITEM_NAME='{0}'", data.ITEM_NAME);
}
if (!string.IsNullOrEmpty(data.SCAN_RESULT))
{
sql += string.Format(" AND M.SCAN_RESULT='{0}'", data.SCAN_RESULT);
}
if (data.SCAN_DATE_TIME != null && data.SCAN_DATE_TIME.Value != DateTime.MinValue)
{
sql += string.Format(" AND date(M.SCAN_DATE_TIME) = '{0}'", data.SCAN_DATE_TIME.Value.ToString("yyyy-MM-dd"));
}
if (null != data.MinAge)
{
sql += string.Format(" AND M.AGE >= {0}", data.MinAge);
}
if (null != data.MaxAge)
{
sql += string.Format(" AND M.AGE <= {0}", data.MaxAge);
}
ISugarQueryable <DST_PATIENT_INFO> queryable = simpleClient.AsSugarClient().SqlQueryable <DST_PATIENT_INFO>(sql);
List <DST_PATIENT_INFO> tmpList = queryable.ToPageList(pageModel.PageIndex, pageModel.PageSize, ref totalNum, ref totalPage);
pageModel.TotalNum = totalNum;
pageModel.TotalPage = totalPage;
return(tmpList);
}
/// <summary>
/// 查询单个实体
/// </summary>
/// <typeparam name="T">表实体</typeparam>
/// <param name="where">查询条件</param>
/// <returns></returns>
public int GetCount <T>(Expression <Func <T, bool> > where) where T : class, new()
{
ISugarQueryable <T> r = _db.Queryable <T>();
if (where != null)
{
r = r.Where(where);
}
return(r.Count());
}
public async Task <PageModel <TEntity> > QueryPageModel(ISugarQueryable <TEntity> sugarQueryable, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null)
{
RefAsync <int> totalCount = 0;
var list = await sugarQueryable.OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds).ToPageListAsync(intPageIndex, intPageSize, totalCount);
int pageCount = (Math.Ceiling(totalCount.ObjToDecimal() / intPageSize.ObjToDecimal())).ObjToInt();
return(new PageModel <TEntity>()
{
DataCount = totalCount, PageCount = pageCount, Page = intPageIndex, PageSize = intPageSize, Data = list
});
}
