/// <summary>
/// 获取T_BM_CustomerContact数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TBMCustomerContactQueryModel> > > GetDetailListAsync(int requestObject)
{
try
{
//查询结果集对象
List <TBMCustomerContactQueryModel> queryData = null;
//总记录数
RefAsync <int> totalNumber = -1;
var query = _db.Instance.Queryable <TBMCustomerContactDbModel>();
//执行查询
queryData = await query.Select((t) => new TBMCustomerContactQueryModel
{
ID = t.ID,
CustomerId = t.CustomerId,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
Priority = t.Priority,
})
.Where(t => t.CustomerId == requestObject)
.ToListAsync();
//返回执行结果
return(ResponseUtil <List <TBMCustomerContactQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TBMCustomerContactQueryModel> > .FailResult(null, ex.Message));
}
}
public async Task <ModelPager <VueMsgInfoNotification> > queryUserReply(QMsgUser query)
{
ModelPager <VueMsgInfoNotification> result = new ModelPager <VueMsgInfoNotification>(query.pageIndex, query.pageSize);
var mainSql = Db.Queryable <EMsgInfo_ReplyRes, EMsgContent_ReplyRes>((m, c) => new object[]
{
JoinType.Left, m.resCode == c.ResCode
})
.Where((m, c) => m.ReceiveUserId == query.userId)
.OrderBy((m, c) => m.CreatedDateTime, OrderByType.Desc)
.Select((m, c) => new VueMsgInfoNotification
{
dbDateTime = m.CreatedDateTime,
senderHeaderUrl = m.SendHeaderUrl,
senderId = m.SendUserId,
senderName = m.SendName,
bookCode = c.BookCode,
bookUrl = c.BookUrl,
resCode = c.ResCode,
resName = c.ResName,
commentId = m.CommentId,
replyId = m.ReplyId,
receiveContent = m.ReceiveContent,
NotificationStatus = m.NotificationStatus,
msgId = m.Id
});
RefAsync <int> totalNumber = new RefAsync <int>();
result.datas = await mainSql.ToPageListAsync(query.pageIndex, query.pageSize, totalNumber);
result.totalCount = totalNumber;
return(result);
}
public async Task <PagedList <OperationLogResult> > GetUserOperationLogAsync(int userId, OperationLogDtoParameters parameters)
{
if (parameters == null)
{
throw new ArgumentNullException(nameof(parameters));
}
RefAsync <int> totalNumber = 0;
var logs = await _dbContext.Db.Queryable <KzLog, KzLogDetail, KzUser>((l, d, u) => new object[]
{
JoinType.Left, l.DetailId == d.DetailId, JoinType.Left, l.UserId == u.UserId
}).Where((l, d, u) => l.UserId == userId && l.OperationTime > parameters.StartDateTime && l.OperationTime < parameters.EndDateTime)
.Select((l, d, u) => new OperationLogResult
{
UserId = l.UserId,
Username = u.Username,
Module = l.Module,
LogType = l.LogType,
OperationTime = l.OperationTime,
IPAddress = l.IPAddress,
Details = d.Details
})
.ToPageListAsync(parameters.PageNumber, parameters.PageSize, totalNumber);
return(new PagedList <OperationLogResult>(logs, (int)totalNumber, parameters.PageNumber, parameters.PageSize));
}
/// <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 async Task <IList <UserLatestSelectEntity> > QueryLatestSelect(UserQueryModel model)
{
var query = _dbContext.Db.Queryable <UserLatestSelectEntity, UserEntity, OrganizeEntity>((x, y, z) => new JoinQueryInfos(JoinType.Inner, x.RelationId == y.Id && y.IsDel == false, JoinType.Left, y.OrganizeId == z.Id && z.IsDel == false))
.Where((x, y, z) => x.UserId == _dbContext.LoginInfo.UserId && x.IsDel == false);
query.WhereIF(!string.IsNullOrEmpty(model.OrganizeName), (x, y, z) => z.OrganizeFullName.Contains(model.OrganizeName));
query.WhereIF(!string.IsNullOrEmpty(model.UserCode), (x, y, z) => y.UserCode.Contains(model.UserCode));
query.WhereIF(!string.IsNullOrEmpty(model.UserName), (x, y, z) => y.UserName.Contains(model.UserName));
query.WhereIF(!string.IsNullOrEmpty(model.Keywords), (x, y, z) => y.UserCode.Contains(model.Keywords) || y.UserName.Contains(model.Keywords) || y.OrganizeFullName.Contains(model.Keywords));
query.Select((x, y, z) => new UserLatestSelectEntity()
{
Id = y.Id.SelectAll(),
OrganizeFullName = z.OrganizeFullName
});
query.OrderBy((x, y, z) => x.Timestamp, OrderByType.Desc);
RefAsync <int> totalCount = 0;
var data = await query.ToPageListAsync(model.PageIndex, model.PageSize, totalCount);
model.TotalCount = totalCount;
return(data);
}
/// <summary>
/// 获取T_WM_ProductionDetail数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TWMProductionDetailQueryModel> > > GetDetailListAsync(int requestObject)
{
try
{
//查询结果集对象
List <TWMProductionDetailQueryModel> queryData = null;
//总记录数
RefAsync <int> totalNumber = -1;
var query = _db.Instance.Queryable <TWMProductionDetailDbModel>();
//执行查询
queryData = await query.Select((t) => new TWMProductionDetailQueryModel
{
ID = t.ID,
MainId = t.MainId,
MaterialId = t.MaterialId,
WarehouseId = t.WarehouseId,
ActualNum = t.ActualNum,
UnitPrice = t.UnitPrice,
Amount = t.Amount,
Remark = t.Remark,
})
.Where(t => t.MainId == requestObject)
.ToListAsync();
//返回执行结果
return(ResponseUtil <List <TWMProductionDetailQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TWMProductionDetailQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_WM_InventoryDetail数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TWMInventoryDetailQueryModel> > > GetDetailListAsync(int requestObject)
{
try
{
//查询结果集对象
List <TWMInventoryDetailQueryModel> queryData = null;
//总记录数
RefAsync <int> totalNumber = -1;
var query = _db.Instance.Queryable <TWMInventoryDetailDbModel, TBMMaterialFileDbModel, TBMDictionaryDbModel,
TBMDictionaryDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel, TBMWarehouseFileDbModel>(
(t, t0, t1, t2, t3, t4, t5) => new object[]
{
JoinType.Left, t.MaterialId == t0.ID,
JoinType.Left, t0.MaterialTypeId == t1.ID,
JoinType.Left, t0.ColorId == t2.ID,
JoinType.Left, t0.BaseUnitId == t3.ID,
JoinType.Left, t0.WarehouseUnitId == t4.ID,
JoinType.Left, t.WarehouseId == t5.ID
}
);
//执行查询
queryData = await query.Select(
(t, t0, t1, t2, t3, t4, t5) => new TWMInventoryDetailQueryModel
{
ID = t.ID,
MainId = t.MainId,
MaterialId = t.MaterialId,
MaterialCode = t0.MaterialCode,
MaterialName = t0.MaterialName,
MaterialTypeId = t0.MaterialTypeId,
MaterialTypeName = t1.DicValue,
ColorId = t0.ColorId,
ColorName = t2.DicValue,
Spec = t0.Spec,
BaseUnitId = t0.BaseUnitId,
BaseUnitName = t3.DicValue,
WarehouseUnitId = t0.WarehouseUnitId,
WarehouseUnitName = SqlFunc.IsNullOrEmpty(t4.ID) ? t3.DicValue : t4.DicValue,
WarehouseRate = t0.WarehouseRate,
WarehouseId = t.WarehouseId,
WarehouseName = t5.WarehouseName,
AccountNum = t.AccountNum,
ActualNum = t.ActualNum,
ProfitNum = t.ProfitNum,
DeficitNum = t.DeficitNum,
Remark = t.Remark,
})
.Where(t => t.MainId == requestObject)
.ToListAsync();
//返回执行结果
return(ResponseUtil <List <TWMInventoryDetailQueryModel> > .SuccessResult(queryData, queryData.Count()));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TWMInventoryDetailQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取分页列表【Linq表达式条件，页码，每页条数】
/// </summary>
/// <typeparam name="TEntity">数据源类型</typeparam>
/// <param name="db"></param>
/// <param name="whereExp">Linq表达式条件</param>
/// <param name="pageIndex">页码（从0开始）</param>
/// <param name="pageSize">每页条数</param>
/// <returns></returns>
public async Task <PagedList <TEntity> > GetPageList(Expression <Func <TEntity, bool> > whereExp, int pageIndex, int pageSize)
{
RefAsync <int> count = 0;
var result = await dbContext.Queryable <TEntity>().Where(whereExp).ToPageListAsync(pageIndex, pageSize, count);
return(new PagedList <TEntity>(result, pageIndex, pageSize, count));
}
/// <summary>
/// 获取T_TenantPermissions数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TTenantPermissionsModel, List <TTenantPermissionsModel> > > GetAsync(RequestObject <TTenantPermissionsModel> requestObject)
{
try
{
List <TTenantPermissionsModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TTenantPermissionsModel, TTenantsModel, TPMMenusDbModel, TUsersModel, TSMCompanyDbModel>(
(t, t0, t2, t3, t4) => new object[]
{
JoinType.Left, t.TenantId == t0.ID,
JoinType.Left, t.MenuId == t2.Id,
JoinType.Left, t.CreateId == t3.Id,
JoinType.Left, t0.ID == t4.CompanyInfoId
});
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where($"t.{p}"));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//设置多表查询返回实体类
query.Select((t, t0, t2, t3, t4) => new TTenantPermissionsModel
{
Id = t.Id,
TenantId = t.TenantId,
TTenantsTenantName = t4.CompanyName,
MenuId = t.MenuId,
TMenusMenuName = t2.MenuName,
ButtonIds = t.ButtonIds,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
TUsersUserName = t3.UserName,
MenuParentId = t2.ParentID
});
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TTenantPermissionsModel, List <TTenantPermissionsModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TTenantPermissionsModel, List <TTenantPermissionsModel> > .FailResult(requestObject, null, ex.Message));
}
}
public async Task <ModelPager <VueUserBook> > queryUserBook(QUserBook query)
{
ModelPager <VueUserBook> result = new ModelPager <VueUserBook>(query.pageIndex, query.pageSize);
var mainSql = Db.Queryable <EUserBook, EBookInfo>((ub, b) => new object[]
{
JoinType.Inner, ub.bookCode == b.Code
})
.Where(ub => ub.userId == query.userId)
.OrderBy(ub => ub.createdDateTime, OrderByType.Desc)
.Select((ub, b) => new VueUserBook
{
id = ub.Id,
Code = b.Code,
CoverUrl = b.CoverUrl,
Name = b.Title,
ResourceCount = b.ResoureNum,
Score = b.Score,
CreateDateTime = ub.createdDateTime,
});
RefAsync <int> totalNumber = new RefAsync <int>();
result.datas = await mainSql.ToPageListAsync(query.pageIndex, query.pageSize, totalNumber);
result.totalCount = totalNumber;
return(result);
}
/// <summary>
/// 获取分页列表【Sugar表达式条件，排序，页码，每页条数】
/// </summary>
/// <param name="db"></param>
/// <param name="conditionals">Sugar条件表达式集合</param>
/// <param name="orderExp">排序表达式</param>
/// <param name="orderType">排序类型</param>
/// <param name="pageIndex">页码（从0开始）</param>
/// <param name="pageSize">每页条数</param>
/// <returns></returns>
public async Task <PagedList <TEntity> > GetPageList(List <IConditionalModel> conditionals, Expression <Func <TEntity, object> > orderExp, OrderByType orderType, int pageIndex, int pageSize)
{
RefAsync <int> count = 0;
var result = await dbContext.Queryable <TEntity>().Where(conditionals).OrderBy(orderExp, orderType).ToPageListAsync(pageIndex, pageSize, count);
return(new PagedList <TEntity>(result, pageIndex, pageSize, count));
}
/// <summary>
/// 获取医院列表
/// </summary>
/// <param name="page"></param>
/// <param name="row"></param>
/// <returns></returns>
public async Task <(IEnumerable <Hospital>, int)> GetHospitalListAsync(int page, int rows, string codeOrName, HospitalLevel?level)
{
var exp = Expressionable.Create <Hospital>();
if (!string.IsNullOrEmpty(codeOrName))
{
exp.And(h => h.name.Contains(codeOrName) || h.code == codeOrName);
}
if (level != null && Enum.IsDefined(typeof(HospitalLevel), level))
{
exp.And(h => h.level == level);
}
var lambda = exp.ToExpression();
var count = new RefAsync <int>(0);
var hospitalList = await _db.Queryable <Hospital>().Where(lambda).OrderBy(h => h.id).ToPageListAsync(page, rows, count);
if (hospitalList.Any())
{
var codes = hospitalList.Select(h => h.code).ToArray();
var tags = ExecuteSelectQuery <Tag>(@"
select
ht.hospitalCode,
t.id,t.name
from HospitalTag ht
left join Tag t on ht.tagId=t.id
where ht.hospitalCode in (@codes)", new { codes });
foreach (var hospital in hospitalList)
{
hospital.Tags = tags.Where(t => t.hospitalCode == hospital.code);
}
}
return(hospitalList, count.Value);
}
public async Task <IList <UserEntity> > QueryBySameOrg(UserQueryModel model)
{
var query = _dbContext.Db.Queryable <UserEntity>()
.LeftJoin <OrganizeEntity>((x, y) => x.OrganizeId == y.Id && y.IsDel == false)
.Where((x, y) => x.OrganizeId == _dbContext.LoginInfo.OrganizeId && x.IsDel == false);
query.WhereIF(!string.IsNullOrEmpty(model.OrganizeName), (x, y) => y.OrganizeFullName.Contains(model.OrganizeName));
query.WhereIF(!string.IsNullOrEmpty(model.UserCode), (x, y) => x.UserCode.Contains(model.UserCode));
query.WhereIF(!string.IsNullOrEmpty(model.UserName), (x, y) => x.UserName.Contains(model.UserName));
query.WhereIF(!string.IsNullOrEmpty(model.Keywords), (x, y) => x.UserCode.Contains(model.Keywords) || x.UserName.Contains(model.Keywords) || y.OrganizeFullName.Contains(model.Keywords));
query.Select((x, y) => new UserEntity()
{
Id = x.Id.SelectAll(),
OrganizeFullName = y.OrganizeFullName
});
query.OrderBy((x, y) => x.CreatedTime, OrderByType.Desc);
RefAsync <int> totalCount = 0;
var data = await query.ToPageListAsync(model.PageIndex, model.PageSize, totalCount);
model.TotalCount = totalCount;
return(data);
}
/// <summary>
/// 获取分页列表【Sugar表达式条件，页码，每页条数】
/// </summary>
/// <typeparam name="TEntity">数据源类型</typeparam>
/// <param name="db"></param>
/// <param name="conditionals">Sugar条件表达式集合</param>
/// <param name="pageIndex">页码（从0开始）</param>
/// <param name="pageSize">每页条数</param>
/// <returns></returns>
public async Task <PagedList <TEntity> > GetPageList(List <IConditionalModel> conditionals, int pageIndex, int pageSize)
{
RefAsync <int> count = 0;
var result = await dbContext.Queryable <TEntity>().Where(conditionals).ToPageListAsync(pageIndex, pageSize, count);
return(new PagedList <TEntity>(result, pageIndex, pageSize, count));
}
static void Main(string[] args)
{
var db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = "Server=localhost;Database=testsugar;Uid=root;Pwd=123456;CharSet=UTF8",
DbType = DbType.MySql,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true
});
db.DbMaintenance.CreateDatabase();
db.CodeFirst.InitTables(typeof(Test));
if (!db.Queryable <Test>().Any())
{
db.Insertable(new Test()
{
Name = "sss"
}).ExecuteCommand();
}
#region 这个是好的
var ss = db.Queryable <Test>().Where(x => x.Id == 1).GroupBy(x => x.Name).Select(x => x.Name).ToPageListAsync(1, 10);
ss.Wait();
var ssRes = ss.Result;
#endregion
//=======================================================
#region SqlSugarException: Queryable<String> Error ,String is invalid , need is a class,and can new().
RefAsync <int> total = 0;
var task = db.Queryable <Test>().Where(x => x.Id == 1).GroupBy(x => x.Name).Select(x => x.Name).ToPageListAsync(1, 10, total);
task.Wait();
var res = task.Result;
#endregion
}
public async override Task <List <BlogNews> > QueryAsync(int page, int size, RefAsync <int> total)
{
return(await base.Context.Queryable <BlogNews>()
.Mapper(c => c.WriterInfo, c => c.WriterId, c => c.WriterInfo.Id)
.Mapper(c => c.TypeInfo, c => c.TypeId, c => c.TypeInfo.Id)
.ToPageListAsync(page, size, total));
}
/// <summary>
/// 根据角色ID 获取菜单树
/// </summary>
/// <param name="requestObject"></param>
/// <returns></returns>
public async Task <ResponseObject <TRolePermissionsModel, List <MenuViewModel> > > LoadMenuByRoles(RequestObject <TRolePermissionsModel> requestObject)
{
try
{
List <MenuViewModel> queryData = null;//查询结果集对象
List <TButtonsModel> buttonsModels = _db.Instance.Queryable <TButtonsModel>().ToList();
RefAsync <int> totalNumber = -1;//总记录数
var query = _db.Instance.Queryable <TRolePermissionsModel, TRolesModel, TPMMenusDbModel>(
(t, t0, t2) => new object[]
{
JoinType.Inner, t.RoleId == t0.Id,
JoinType.Inner, t.MenuId == t2.Id
});
if (requestObject.QueryConditions == null || requestObject.QueryConditions.Count() == 0)
{
ResponseUtil <TRolePermissionsModel, List <MenuViewModel> > .FailResult(requestObject, null, "查询条件必须含有roleid");
}
else
{
if (!requestObject.QueryConditions.Any(p => p.Column.ToLower() == "roleid"))
{
ResponseUtil <TRolePermissionsModel, List <MenuViewModel> > .FailResult(requestObject, null, "查询条件必须含有roleid");
}
else
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where($"t.{p}"));
}
}
//获取菜单
var memu = query.Select((t, t0, t2) => new { buttons = t.ButtonIds, memu = t2 }).ToList();
var menuDictoray = memu.ToDictionary(p => p.memu.Id, p => p.buttons);
List <int> allMenuIDS = new List <int>();
foreach (var item in memu)
{
var ids = item.memu.LogicPath.Split('.').Select(p => Convert.ToInt32(p)).ToList();
allMenuIDS.AddRange(ids);
}
allMenuIDS = allMenuIDS.Distinct().ToList();
var allDisplayNodes = await _db.Instance.Queryable <TPMMenusDbModel>().Where(p => allMenuIDS.Contains(p.Id) && p.Status == true).ToListAsync(); //所有需要展示的菜单
var data = GetMenuTree(allDisplayNodes, menuDictoray, buttonsModels, -1);
return(ResponseUtil <TRolePermissionsModel, List <MenuViewModel> > .SuccessResult(requestObject, data, totalNumber));
}
catch (Exception ex)
{
return(ResponseUtil <TRolePermissionsModel, List <MenuViewModel> > .FailResult(requestObject, null, ex.Message));
}
}
public async Task <ModelPager <VueUserCommReply> > queryUserCommReply(QUserCommReply query)
{
ModelPager <VueUserCommReply> result = new ModelPager <VueUserCommReply>(query.pageIndex, query.pageSize);
var mainSql = Db.Queryable <ECommentReply_Res, EComment_Res, EUserInfo, EBookInfo>((r, c, cu, b) => new object[]
{
JoinType.Inner, r.commentId == c.Id,
JoinType.Inner, r.authorId == cu.Id,
JoinType.Inner, c.parentRefCode == b.Code,
})
.Where((r, c, cu, b) => r.authorId == query.userId)
.OrderBy(r => r.CreateDateTime, OrderByType.Desc)
.Select((r, c, cu, b) => new VueUserCommReply
{
bookCode = b.Code,
bookName = b.Title,
resCode = c.refCode,
bookCoverUrl = b.CoverUrl,
commentId = c.Id,
commentAuthorId = c.authorId,
commentAuthor = cu.NickName,
pCommentDateTime = c.CreateDateTime,
commentContent = c.content,
replyId = r.Id,
replyContent = r.content,
replyTarget = r.replyName,
pReplyDateTime = r.CreateDateTime,
});
RefAsync <int> totalNumber = new RefAsync <int>();
result.datas = await mainSql.ToPageListAsync(query.pageIndex, query.pageSize, totalNumber);
result.totalCount = totalNumber;
return(result);
}
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>
/// 获取T_Users数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TUsersModel, List <TUsersModel> > > GetAsync(RequestObject <TUsersModel> requestObject)
{
try
{
List <TUsersModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TUsersModel, TRolesModel>(
(t, t0) => new object[]
{
JoinType.Left, t.RoleId == t0.Id,
});
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where($"t.{p}"));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//设置多表查询返回实体类
query.Select((t, t0) => new TUsersModel
{
Id = t.Id,
UserName = t.UserName,
LoginName = t.LoginName,
LoginPwd = t.LoginPwd,
Salt = t.Salt,
RoleId = t.RoleId,
TRolesRoleName = t0.RoleName,
Status = t.Status,
CreateTime = t.CreateTime,
CreateId = t.CreateId
});
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.ToListAsync();
}
queryData.ForEach(x => { x.LoginPwd = EncryptUtil.DeAESbyKey(x.LoginPwd, Encoding.UTF8, x.Salt); });
//返回执行结果
return(ResponseUtil <TUsersModel, List <TUsersModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TUsersModel, List <TUsersModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
/// Gets entities with given predicate,page & sort params.
/// </summary>
/// <param name="param">page & sort</param>
/// <param name="predicate">A condition to filter entities</param>
/// <param name="groupBy">groupBy</param>
/// <param name="select">columns to be selected</param>
/// <param name="totalCount">total count</param>
/// <returns></returns>
public async Task <List <TResult> > Query <TResult>(GridParams param,
Expression <Func <TEntity, bool> > predicate,
Expression <Func <TEntity, object> > groupBy,
Expression <Func <TEntity, TResult> > select,
RefAsync <int> totalCount)
{
return(await Db.Queryable <TEntity>().Where(predicate).GroupBy(groupBy).Select(select).ToPageListAsync(param.PageNum, param.PageSize, totalCount));
}
public ModelPager <RBookList> GetBookListPager(QBookList query)
{
ModelPager <RBookList> result = new ModelPager <RBookList>();
result.pageIndex = query.pageIndex;
result.pageSize = query.pageSize;
RefAsync <int> totalNumber = new RefAsync <int>();
if (query.QueryType == QBookList_Type.Tag)
{
result.datas = _bookDb.GetBookListByTag(query.pageIndex, query.pageSize, query.Code, totalNumber).Result;
result.totalCount = totalNumber;
return(result);
}
else if (query.QueryType == QBookList_Type.Section)
{
ESection section = _sectionRepository.GetByKey(query.Code).Result;
if (section != null)
{
if (section.SectionType == SectionType.Column)
{
result.datas = _bookDb.GetBookListBySection_ST(query.pageIndex, query.pageSize, query.Code, totalNumber).Result;
result.totalCount = totalNumber;
return(result);
}
else
{
if (section.Code == WebSectionCode.NewExpress)
{
result.datas = _bookDb.GetBookListBySection_DT(query.pageIndex, query.pageSize, query.Code, totalNumber).Result;
result.totalCount = totalNumber;
return(result);
}
else if (section.Code == WebSectionCode.ResDownLoad)
{
result.datas = _bookDb.GetBookListBySection_Resource(query.pageIndex, query.pageSize, totalNumber).Result;
result.totalCount = totalNumber;
return(result);
}
else if (section.Code == WebSectionCode.HighScore)
{
result.datas = _bookDb.GetBookListBySection_HighScroe(query.pageIndex, query.pageSize, totalNumber, defaultTop: query.HighScoreTop).Result;
if (totalNumber > query.HighScoreTop)
{
totalNumber = new RefAsync <int>(query.HighScoreTop);
}
result.totalCount = totalNumber;
return(result);
}
}
}
}
return(null);
// return _bookDb.GetSimpleBookPager(pageIndex, pageSize, bookSection).Result;
}
private static void q3()
{
RefAsync <int> total = 0;
var count = Db.Queryable <Order>().Count();
Task t = Db.Queryable <Order>().ToJsonPageAsync(1, 2, total);
t.Wait();
UValidate.Check(count, total.Value, "QueryableAsync");
}
/// <summary>
/// 获取T_Bulletin数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TBulletinModel, List <TBulletinModel> > > GetAsync(RequestObject <TBulletinModel> requestObject)
{
try
{
List <TBulletinModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TBulletinModel, TUsersModel>(
(t, t0) => new object[]
{
JoinType.Left, t.Publisher == t0.Id,
});
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var expressionList = SqlSugarUtil.GetQueryExpressions(requestObject.QueryConditions);
expressionList.ForEach(p => query.Where($"t.{p}"));
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
requestObject.OrderByConditions.ForEach(p => query.OrderBy($"{p.Column} {p.Condition}"));
}
//设置多表查询返回实体类
query.Select((t, t0) => new TBulletinModel
{
Id = t.Id,
BulletinTitle = t.BulletinTitle,
BulletinContent = t.BulletinContent,
PublishDate = t.PublishDate,
Publisher = t.Publisher,
TUsersUserName = t0.UserName,
Receivers = t.Receivers,
RePublish = t.RePublish,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
});
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TBulletinModel, List <TBulletinModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TBulletinModel, List <TBulletinModel> > .FailResult(requestObject, null, ex.Message));
}
}
public static ApiResult Success(dynamic data, RefAsync <int> total)
{
return(new ApiResult
{
Code = 200,
Data = data,
Msg = "操作成功",
Total = total
});
}
public async Task <PageModelDynamic> GetDataAsync()
{
RefAsync <int> total = 0;
var data = await _db.Queryable <tb_tests>()
.ToPageListAsync(1, 10, total);
return(new PageModelDynamic
{
data = data,
});
}
public async Task <PagedList <KzUser> > GetAllUsersAsync(UserDtoParameters parameters)
{
if (parameters == null)
{
throw new ArgumentNullException(nameof(parameters));
}
RefAsync <int> totalNumber = 0;
var users = await _dbContext.Db.Queryable <KzUser>().ToPageListAsync(parameters.PageNumber, parameters.PageSize, totalNumber);
return(new PagedList <KzUser>(users, (int)totalNumber, parameters.PageNumber, parameters.PageSize));
}
/// <summary>
/// 根据条件查询分页数据
/// </summary>
/// <param name="predicate"></param>
/// <param name="orderBy"></param>
/// <param name="pageIndex">当前页面索引</param>
/// <param name="pageSize">分布大小</param>
/// <returns></returns>
public async Task <IPageList <T> > QueryPageAsync(Expression <Func <T, bool> > predicate, string orderBy = "",
int pageIndex = 1, int pageSize = 20)
{
RefAsync <int> totalCount = 0;
var page = await _dbClient.Queryable <T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
.WhereIF(predicate != null, predicate).ToPageListAsync(pageIndex, pageSize, totalCount);
var list = new PageList <T>(page, pageIndex, pageSize, totalCount);
return(list);
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="para"></param>
/// <returns></returns>
public async Task <Pager <T> > QueryPageAsync <T>(Para <T> para) where T : class, new()
{
RefAsync <int> totalCount = 0;
var list = await GetQueryByPara <T>(para).ToPageListAsync(para.PageIndex, para.PageSize, totalCount);
int pageCount = (Math.Ceiling(totalCount.ObjToDecimal() / para.PageSize.ObjToDecimal())).ObjToInt();
return(new Pager <T>()
{
TotalCount = totalCount, TotalPages = pageCount, PageIndex = para.PageIndex, PageSize = para.PageSize, Datas = list
});
}
/// <summary>
/// 多表分页查询
/// 根据自定义的实体对象，返回实体对象集合分页数据
/// </summary>
/// <typeparam name="T1">实体1</typeparam>
/// <typeparam name="T2">实体2</typeparam>
/// <typeparam name="T3">实体3</typeparam>
/// <typeparam name="T4">实体4</typeparam>
/// <typeparam name="T5">实体5</typeparam>
/// <typeparam name="T6">实体6</typeparam>
/// <typeparam name="T7">实体7</typeparam>
/// <typeparam name="T8">实体8</typeparam>
/// <typeparam name="TResult">返回实体对象集合分页数据</typeparam>
/// <param name="joinExpression"></param>
/// <param name="selectExpression"></param>
/// <param name="pageInfo"></param>
/// <param name="whereExpression"></param>
/// <param name="orderMuch"></param>
/// <returns></returns>
public async Task <List <TResult> > QueryPageMuchEntityAsync <T1, T2, T3, T4, T5, T6, T7, T8, TResult>(Expression <Func <T1, T2, T3, T4, T5, T6, T7, T8, JoinQueryInfos> > joinExpression, TResult selectExpression, SqlSugarPageInfo pageInfo, Expression <Func <T1, T2, T3, T4, T5, T6, T7, T8, bool> > whereExpression = null, List <OrderByClause> orderMuch = null) where T1 : class, new()
{
var query = db.Queryable(joinExpression).WhereIF(whereExpression != null, whereExpression).OrderByIF(orderMuch != null, ParseOrderBy(orderMuch)).Select <TResult>();
RefAsync <int> totalCount = 0;
var result = await query.ToPageListAsync(pageInfo.PageIndex, pageInfo.PageSize, totalCount);
pageInfo.TotalCount = totalCount;
return(result);
}
