/// <summary>
/// 查询栏目
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public QueryResponse Query(QueryRequest request)
{
var response = new QueryResponse();
try
{
int total = 0;
var list = _systemNavbarRepository.FindPage <SystemNavbarPo>(
e => e.IsValid == 1 && (e.Name.Contains(request.Name) || SqlFunc.IsNullOrEmpty(request.Name)),
o => o.Sort,
request.PageIndex, request.PageSize, ref total);
response.SystemNavbarList = list.Select(e => e.As <SystemNavbarDto>()).ToList();
response.TotalCount = total;
}
catch (Exception ex)
{
response.IsSuccess = false;
response.MessageCode = "-1";
response.MessageText = ex.ToString();
LogManager.LogicLogger.ErrorFormat("分页查询系统栏目出错：{0}", new { request, err = ex.ToString() }.ToJson());
}
return(response);
}
/// <summary>
/// 盘点单审核
/// </summary>
/// <param name="request"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <bool> > InventoryAuditAsync(RequestPut <TWMInventoryAuditModel> request, CurrentUser currentUser)
{
try
{
var flag = await _db.Instance.Updateable <TWMInventoryMainDbModel>()
.SetColumns(p => new TWMInventoryMainDbModel
{
AuditId = currentUser.UserID,
AuditStatus = request.PostData.AuditStatus,
AuditTime = DateTime.Now
})
.Where(p => p.ID == request.PostData.ID && (SqlFunc.IsNullOrEmpty(p.AuditStatus) || p.AuditStatus != 2))
.ExecuteCommandAsync() > 0;
//盘点单审核通过后，生成盘盈入库单或盘亏入库单
if (flag && request.PostData.AuditStatus == 2)
{
CreateProfitOrDeficitOrder(request.PostData.ID, currentUser);
}
return(flag
? ResponseUtil <bool> .SuccessResult(true)
: ResponseUtil <bool> .FailResult(false, "审核数据失败，该数据可能已审核"));
}
catch (Exception ex)
{
return(ResponseUtil <bool> .FailResult(false, $"审核数据发生异常{Environment.NewLine}{ex.Message}"));
}
}
private static void EasyExamples()
{
Console.WriteLine("");
Console.WriteLine("#### Examples Start ####");
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Order>().ToList();
var getAll2 = db.Queryable <Order>().Where(it => it.CreateTime.Day >= DateTime.Now.Date.Day).ToList();
var getOrderBy = db.Queryable <Order>().OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy2 = db.Queryable <Order>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy3 = db.Queryable <Order>().OrderBy(it => new { it.Name, it.Id }).ToList();
var getRandom = db.Queryable <Order>().OrderBy(it => SqlFunc.GetRandom()).First();
var getByPrimaryKey = db.Queryable <Order>().InSingle(2);
var getSingleOrDefault = db.Queryable <Order>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Order>().First();
var getByWhere = db.Queryable <Order>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Order>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Order>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var getByFuns2 = db.Queryable <Order>().GroupBy(it => it.Name).Select(it => SqlFunc.AggregateDistinctCount(it.Price)).ToList();
var dp = DateTime.Now;
var test05 = db.Queryable <Order>().Where(it => it.CreateTime.Month == dp.Month).ToList();
var fromatList = db.Queryable <Order>().Select(it => it.CreateTime.ToString("%Y-%m")).ToList();
var test06 = db.Queryable <Order>().Where(it => it.CreateTime.Date.Day >= DateTime.Now.Date.Day).ToList();
var test07 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Day, Convert.ToDateTime("2021-1-1"), Convert.ToDateTime("2021-1-12"))).ToList();
var q1 = db.Queryable <Order>().Take(1);
var q2 = db.Queryable <Order>().Take(2);
var test02 = db.Union(q1, q2).ToList();
Console.WriteLine("#### Examples End ####");
}
/// <summary>
/// 查询应用授权列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public QueryAppIdAuthResponse QueryAppIdAuth(QueryAppIdAuthRequest request)
{
var response = new QueryAppIdAuthResponse();
try
{
int total = 0;
var list = _appIdAuthTokenRepository.FindPage <AppIdAuthPo>(
e => e.IsValid == 1 &&
(SqlFunc.IsNullOrEmpty(request.ServiceAppId) || e.AppId.Contains(request.ServiceAppId)) &&
(SqlFunc.IsNullOrEmpty(request.AuthToken) || e.AuthToken.Contains(request.AuthToken)),
o => o.Id,
request.PageIndex, request.PageSize, ref total, OrderByType.Desc);
response.EntityList = list.Select(e => e.As <AppIdAuthDto>()).ToList();
response.TotalCount = total;
}
catch (Exception ex)
{
response.IsSuccess = false;
response.MessageCode = "-1";
response.MessageText = ex.Message;
LogManager.LogicLogger.ErrorFormat("查询应用授权列表出错：{0}", new { request, err = ex.ToString() }.ToJson());
}
return(response);
}
private static void EasyExamples()
{
Console.WriteLine("");
Console.WriteLine("#### Examples Start ####");
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Order>().ToList();
var getOrderBy = db.Queryable <Order>().OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy2 = db.Queryable <Order>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy3 = db.Queryable <Order>().OrderBy(it => new { it.Name, it.Id }).ToList();
var getRandom = db.Queryable <Order>().OrderBy(it => SqlFunc.GetRandom()).First();
var getByPrimaryKey = db.Queryable <Order>().InSingle(2);
var getSingleOrDefault = db.Queryable <Order>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Order>().First();
var getByWhere = db.Queryable <Order>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Order>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Order>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var getByFuns2 = db.Queryable <Order>().GroupBy(it => it.Name).Select(it => SqlFunc.AggregateDistinctCount(it.Price)).ToList();
var btime = Convert.ToDateTime("2021-1-1");
var etime = Convert.ToDateTime("2022-1-12");
var test01 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Year, btime, etime)).ToList();
var test02 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Day, btime, etime)).ToList();
var test03 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Month, btime, etime)).ToList();
var test04 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Second, DateTime.Now, DateTime.Now.AddMinutes(2))).ToList();
var q1 = db.Queryable <Order>().Take(1);
var q2 = db.Queryable <Order>().Take(2);
var test05 = db.UnionAll(q1, q2).ToList();
Console.WriteLine("#### Examples End ####");
Console.WriteLine("#### Examples End ####");
}
/// <summary>
/// 根据用户名查询用户列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public QueryUserInfoByNameResponse QueryUserInfoByName(QueryUserInfoByNameRequest request)
{
var response = new QueryUserInfoByNameResponse();
try
{
var list = _userInfoRepository.FindWhere <UserInfoPo>(e =>
e.IsValid == 1 &&
(SqlFunc.IsNullOrEmpty(request.Name) || e.LoginName.Contains(request.Name) || e.Email.Contains(request.Name)));
if (list != null)
{
response.EntityList = list.Select(e => e.As <UserInfoDto>()).ToList();
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.MessageCode = "-1";
response.MessageText = ex.Message;
LogManager.LogicLogger.ErrorFormat(new { request, err = ex.ToString() }.ToJson());
}
return(response);
}
private static void EasyExamples()
{
Console.WriteLine("");
Console.WriteLine("#### Examples Start ####");
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Order>().Where(it => SqlFunc.EqualsNull(it.Name, null)).ToList();
var getOrderBy = db.Queryable <Order>().OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy2 = db.Queryable <Order>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy3 = db.Queryable <Order>().OrderBy(it => new { it.Name, it.Id }).ToList();
var getRandom = db.Queryable <Order>().OrderBy(it => SqlFunc.GetRandom()).First();
var getByPrimaryKey = db.Queryable <Order>().InSingle(2);
var getSingleOrDefault = db.Queryable <Order>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Order>().First();
var getByWhere = db.Queryable <Order>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Order>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Order>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var getByFuns2 = db.Queryable <Order>().GroupBy(it => it.Name).Select(it => SqlFunc.AggregateDistinctCount(it.Price)).ToList();
var getDicionary = db.Queryable <Order>().ToDictionary(it => it.Id, it => it.Name);
var getDicionaryList = db.Queryable <Order>().ToDictionaryList();
var getTest = db.Queryable <Order>().Where(it => string.IsNullOrWhiteSpace(it.Name)).ToList();
var test01 = db.Queryable <Order>().PartitionBy(it => it.Id).ToList();
var q1 = db.Queryable <Order>().Take(1);
var q2 = db.Queryable <Order>().Take(2);
var test02 = db.Union(q1, q2).ToList();
var test03 = db.Queryable <Order>().Take(1).ToList();
Console.WriteLine("#### Examples End ####");
}
/// <summary>
/// 分页查询应用项目
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public QueryAppProjectResponse QueryAppProject(QueryAppProjectRequest request)
{
var response = new QueryAppProjectResponse();
try
{
int total = 0;
var list = _appProjectRepository.FindPage <AppProjectPo>(
e => e.IsValid == 1 &&
(SqlFunc.IsNullOrEmpty(request.ProjectName) || e.ProjectName.Contains(request.ProjectName)) &&
(SqlFunc.IsNullOrEmpty(request.ApplicationId) || e.ApplicationId.Contains(request.ApplicationId)),
o => o.Id, request.PageIndex, request.PageSize, ref total, OrderByType.Desc);
if (list != null)
{
response.EntityList = list.Select(e => e.As <AppProjectDto>()).ToList();
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.MessageCode = "-1";
response.MessageText = ex.Message;
LogManager.LogicLogger.ErrorFormat("");
}
return(response);
}
/// <summary>
/// 查询用户信息
/// </summary>
/// <param name="account">用户名</param>
/// <param name="sex">性别（-1：全部；0:女；1：男）</param>
/// <param name="pagesize">页面大小</param>
/// <param name="total">合计</param>
/// <param name="departmentid">部门Id</param>
/// <param name="pageindex">页面索引</param>
/// <returns></returns>
public List <UserInfoDto> QueryUserInfo(string account, int?sex, int departmentid, int pageindex, int pagesize, ref int total)
{
var list = CurrentDbContext.Queryable <UserInfoPo, UserDepartmentJoinPo, DepartmentPo>((ui, udj, d) => new object[]
{
JoinType.Left, (ui.Id == udj.UserId && udj.IsValid == 1),
JoinType.Left, (udj.DepartmentId == d.Id && d.IsValid == 1)
}).Where((ui, udj, d) =>
ui.IsValid == 1 &&
(SqlFunc.IsNullOrEmpty(account) || ui.LoginName.Contains(account) || ui.Email.Contains(account)) &&
(sex == -1 || ui.Sex == sex) &&
(departmentid == 0 || udj.DepartmentId == departmentid)
).OrderBy((ui, udj) => ui.Id, OrderByType.Desc)
.Select((ui, udj, d) => new UserInfoDto
{
Id = ui.Id,
LoginName = ui.LoginName,
Email = ui.Email,
Password = ui.Password,
Name = ui.Name,
Sex = ui.Sex,
HeadimgUrl = ui.HeadimgUrl,
Mobile = ui.Mobile,
CreateUserId = ui.CreateUserId,
CreateTime = ui.CreateTime,
UpdateUserId = ui.UpdateUserId,
UpdateTime = ui.UpdateTime,
Remark = ui.Remark,
IsValid = ui.IsValid,
DepartmentId = udj.DepartmentId,
DepartementName = d.Name
}).ToPageList(pageindex, pagesize, ref total);
return(list);
}
public static void Easy()
{
var db = GetInstance();
var getAll = db.Queryable <Student>().ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
}
/// <summary>
/// 回复分页
/// </summary>
/// <param name="pageindex">当前页</param>
/// <param name="pagesize">没有显示条数</param>
/// <param name="rootid">评论ID</param>
/// <param name="aid">文章ID</param>
/// <returns></returns>
public Tuple <List <ReplyDto>, int> ReplyList(int pageindex, int pagesize, string rootid, string aid)
{
int count = 0;
List <ReplyDto> reply = Db.Queryable <LeavemsgInfo, QQUserinfo, QQUserinfo>((m, u1, u2) =>
new object[] { JoinType.Left, m.FromUId == u1.UserId, JoinType.Left, m.ToUId == u2.UserId }
).Where(m => m.RootId == rootid && m.DeleteMark == false)
.WhereIF(SqlFunc.IsNullOrEmpty(aid), m => SqlFunc.IsNullOrEmpty(m.ArticleId))
.WhereIF(!SqlFunc.IsNullOrEmpty(aid), m => m.ArticleId == aid)
.OrderBy(m => m.ParentId, OrderByType.Asc)
.OrderBy(m => m.MsgId, OrderByType.Asc)
.OrderBy(m => m.CreatorTime, OrderByType.Asc)
.Select((m, u1, u2) => new ReplyDto()
{
MsgId = m.MsgId,
FromUId = m.FromUId,
FromNikeName = u1.NikeName,
FromImage = u1.Image40,
ToUId = m.ToUId,
ToNikeName = u2.NikeName,
ToImage = u2.Image40,
Content = m.Content,
Adscription = m.Adscription,
IsMaster = u1.IsMaster,
CreatorTime = m.CreatorTime
})
.ToPageList(pageindex, pagesize, ref count);
int page = (int)Math.Ceiling(count * 1d / pagesize);
return(Tuple.Create(reply, page));
}
/// <summary>
/// 获取供应商采购金额分析
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <List <PurchaseSupplierAmountCountModel> > > GetPurchaseSupplierAmountCount(CurrentUser currentUser)
{
try
{
var today = await _db.Instance.Queryable <TPSMPurchaseOrderDetailDbModel, TPSMPurchaseOrderMainDbModel, TBMSupplierFileDbModel>(
(t1, t2, t3) => new object[]
{
JoinType.Left, t1.MainId == t2.ID,
JoinType.Left, t1.SupplierId == t3.ID
}
).Where((t1, t2, t3) => t2.CompanyId == currentUser.CompanyID &&
t2.OrderDate >= Convert.ToDateTime($"{DateTime.Now.ToString("yyyy-MM")}-01") &&
t2.OrderDate < Convert.ToDateTime($"{DateTime.Now.AddMonths(1).ToString("yyyy-MM")}-01")
)
.GroupBy((t1, t2, t3) => t3.SupplierName)
.Where((t1, t2, t3) => !SqlFunc.IsNullOrEmpty(t3.SupplierName))
.Select((t1, t2, t3) => new PurchaseSupplierAmountCountModel {
Value = SqlFunc.AggregateSum(t1.PurchaseAmount), Name = t3.SupplierName
})
.ToListAsync();
return(ResponseUtil <List <PurchaseSupplierAmountCountModel> > .SuccessResult(today));
}
catch (Exception ex)
{
return(ResponseUtil <List <PurchaseSupplierAmountCountModel> >
.FailResult(null, $"统计采购订单金额发生异常{System.Environment.NewLine} {ex.Message}"));
}
}
/// <summary>
/// 查询用户Id授权
/// </summary>
/// <param name="username">用户名</param>
/// <param name="appid">应用Id</param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <UserAppIdAuthDto> QueryUserAppId(string username, string appid, int pageindex, int pagesize, ref int total)
{
var list = CurrentDbContext.Queryable <UserAppIdAuthPo, UserInfoPo>((t1, t2) => new object[]
{
JoinType.Left, t1.UserId == t2.Id
})
.Where((t1, t2) =>
t1.IsValid == 1 && t2.IsValid == 1 &&
(SqlFunc.IsNullOrEmpty(username) || t2.LoginName.Contains(username) || t2.Name.Contains(username)) &&
(SqlFunc.IsNullOrEmpty(appid) || t1.AppId.Contains(appid)))
.OrderBy((t1, t2) => t1.Id, OrderByType.Desc)
.Select((t1, t2) => new UserAppIdAuthDto
{
Id = t1.Id,
AppId = t1.AppId,
CreateTime = t1.CreateTime,
CreateUserId = t1.CreateUserId,
IsValid = t1.IsValid,
Remark = t1.Remark,
UpdateTime = t1.UpdateTime,
UpdateUserId = t1.UpdateUserId,
UserId = t1.UserId,
LoginName = t2.LoginName,
Name = t2.Name,
Email = t2.Email
}).ToPageList(pageindex, pagesize, ref total);
return(list);
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
}
/// <summary>
/// 分页查询用户角色
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public QueryUserRoleResponse QueryUserRole(QueryUserRoleRequest request)
{
var response = new QueryUserRoleResponse();
try
{
int total = 0;
var list = _userRoleRepository.FindPage <UserRolePo>(
e => e.IsValid == 1 && (SqlFunc.IsNullOrEmpty(request.Name) || e.Name.Contains(request.Name)),
o => o.Id, request.PageIndex, request.PageSize, ref total, OrderByType.Desc);
response.List = list.Select(e => e.As <UserRoleDto>()).ToList();
response.TotalCount = total;
}
catch (Exception ex)
{
response.IsSuccess = false;
response.MessageCode = "-1";
response.MessageText = ex.ToString();
LogManager.LogicLogger.ErrorFormat("分页查询角色出错：{0}", new { request, err = ex.ToString() }.ToJson());
}
return(response);
}
/// <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>
/// 是否存在没有打包的
/// </summary>
/// <param name="order_code"></param>
/// <returns></returns>
public bool IsOutBillcodeCount(string order_code)
{
return(Common.Config.StartSqlSugar <bool>((db) =>
{
return db.Queryable <pmw_billcode>()
.Any(a => SqlFunc.IsNullToInt(a.is_packed) == 0 && SqlFunc.IsNullOrEmpty(a.packed_billcode));
}));
}
/// <summary>
/// 主播工时 分页信息
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public List <HourModel> GetHourDetailsPage(PageParm parm, ref int totalCount, ref decimal sumAmount, ref decimal sumDuration)
{
var result = new List <HourModel>();
try
{
if (parm == null)
{
parm = new PageParm();
}
Dictionary <string, object> dic = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(parm.where))
{
dic = JsonConvert.DeserializeObject <Dictionary <string, object> >(parm.where);
}
using (var db = GetSqlSugarDB(DbConnType.QPVideoAnchorDB))
{
var query = db.Queryable <SysAnchorLiveRecordEntity, SysAnchor, SysShopAnchorEntity>((it, st, ot) => new object[] {
JoinType.Left, it.aid == st.id,
JoinType.Left, st.id == ot.AnchorID
})
.Where(it => it.ontime >= Convert.ToDateTime(dic["startTime"]) && it.ontime < Convert.ToDateTime(dic["endTime"]))
.WhereIF(dic.ContainsKey("seqid") && !string.IsNullOrEmpty(dic["seqid"].ToString()), it => it.seqid == dic["seqid"].ToString())
.WhereIF(dic.ContainsKey("isLive") && Convert.ToInt32(dic["isLive"]) == 1, it => SqlFunc.IsNullOrEmpty(it.uptime))
.WhereIF(dic.ContainsKey("isLive") && Convert.ToInt32(dic["isLive"]) == 0, it => !SqlFunc.IsNullOrEmpty(it.uptime))
.WhereIF(dic.ContainsKey("Name") && !string.IsNullOrEmpty(dic["Name"].ToString()), (it, st) => st.anchorName.Contains(dic["Name"].ToString()) || st.nickName.Contains(dic["Name"].ToString()))
.WhereIF(dic.ContainsKey("ShopID") && Convert.ToInt32(dic["ShopID"]) != -1, (it, st, ot) => ot.ShopID == Convert.ToInt32(dic["ShopID"]))
; //缓存30秒
var sumReuslt = query.Clone().Select((it, st) => new { amount = SqlFunc.AggregateSum(it.amount), duration = SqlFunc.AggregateSum(it.livetime) }).WithCache(30).First();
sumAmount = sumReuslt.amount;
sumDuration = sumReuslt.duration;
return(query
.Select((it, st, ot) => new HourModel
{
seqid = it.seqid,
AnchorName = st.anchorName,
NickName = st.nickName,
begintime = it.ontime,
endtime = it.uptime,
duration = it.livetime,
islive = SqlFunc.IIF(SqlFunc.IsNullOrEmpty(it.uptime), 1, 0),
flvurl = it.flvurl,
status = it.status
})
.WithCache(30)
.OrderBy(" it.ontime desc")
.ToPageList(parm.page, parm.limit, ref totalCount));
}
}
catch (Exception ex)
{
new LogLogic().Write(Level.Error, "主播工时 分页信息", ex.Message, ex.StackTrace);
}
return(result);
}
private async Task <ResponseObject <List <TMMPurchaseApplyDetailQueryModel> > > GetDetailList(int requestObject)
{
try
{
//查询结果集对象
List <TMMPurchaseApplyDetailQueryModel> queryData = null;
//总记录数
RefAsync <int> totalNumber = -1;
var query = _db.Instance.Queryable <TMMPurchaseApplyDetailDbModel, TBMMaterialFileDbModel, TBMDictionaryDbModel,
TBMDictionaryDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel>(
(t, t0, t1, t2, t3, t4) => new object[]
{
JoinType.Left, t.MaterialId == t0.ID,
JoinType.Left, t0.BaseUnitId == t1.ID,
JoinType.Left, t0.ProduceUnitId == t2.ID,
JoinType.Left, t0.PurchaseUnitId == t3.ID,
JoinType.Left, t0.ColorId == t4.ID
});
//执行查询
queryData = await query
.Select((t, t0, t1, t2, t3, t4) => new TMMPurchaseApplyDetailQueryModel
{
ID = t.ID,
MainId = t.MainId,
MaterialId = t.MaterialId,
ApplyNum = t.ApplyNum,
TransNum = t.TransNum,
MaterialCode = t0.MaterialCode,
MaterialName = t0.MaterialName,
Spec = t0.Spec,
BaseUnitId = t0.BaseUnitId,
BaseUnitName = t1.DicValue,
ProduceUnitId = t0.ProduceUnitId,
ProduceUnitName = SqlFunc.IsNullOrEmpty(t2.ID) ? t1.DicValue : t2.DicValue,
ProduceRate = t0.ProduceRate,
PurchaseUnitId = t0.PurchaseUnitId,
PurchaseUnitName = SqlFunc.IsNullOrEmpty(t3.ID) ? t1.DicValue : t3.DicValue,
PurchaseRate = t0.PurchaseRate,
ColorName = t4.DicValue
})
.Where(t => t.MainId == requestObject)
.ToListAsync();
//返回执行结果
return(ResponseUtil <List <TMMPurchaseApplyDetailQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TMMPurchaseApplyDetailQueryModel> > .FailResult(null, ex.Message));
}
}
private void StringIsNullOrEmpty()
{
Expression <Func <Student, bool> > exp = it => it.Id > 2 || SqlFunc.IsNullOrEmpty(it.Id);;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( [Id] > @Id0 ) OR ( [Id]='' OR [Id] IS NULL ))", new List <SugarParameter>()
{
new SugarParameter("@Id0", 2)
}, "StringIsNullOrEmpty error");
}
private void StringIsNullOrEmpty5()
{
WhereConst.name = "xx";
Expression <Func <Student, bool> > exp = it => !SqlFunc.IsNullOrEmpty(WhereConst.name);;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "NOT( @MethodConst0='' OR @MethodConst0 IS NULL )", new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", "xx")
}, "StringIsNullOrEmpty5 error");
}
private void StringIsNullOrEmpty2()
{
Expression <Func <Student, bool> > exp = it => 2 == it.Id || SqlFunc.IsNullOrEmpty(true);;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( @Id0 = [Id] ) OR ( @MethodConst1='' OR @MethodConst1 IS NULL ))", new List <SugarParameter>()
{
new SugarParameter("@MethodConst1", true),
new SugarParameter("@Id0", 2)
}, "StringIsNullOrEmpty2 error");
}
public List <OwnerDto> List(OwnerQueryFilter filter)
{
if (filter == null)
{
return(null);
}
using (var db = DBManager.GetInstance())
{
var q = db.Queryable <T_Owner>();
if (!string.IsNullOrWhiteSpace(filter.Keywords))
{
q = q.Where(w => w.Name.Contains(filter.Keywords));
}
if (!string.IsNullOrWhiteSpace(filter.Phone))
{
q = q.Where(w => SqlFunc.StartsWith(w.Phone, filter.Phone));
}
if (!string.IsNullOrWhiteSpace(filter.CardNo))
{
q = q.Where(w => SqlFunc.StartsWith(w.CardNo, filter.CardNo));
}
if (filter.IsDeleted.HasValue && filter.IsDeleted.Value)
{
q = q.Where(w => !SqlFunc.IsNullOrEmpty(w.DeletedTime));
}
else
{
//默认排除掉删除项目
q = q.Where(w => SqlFunc.IsNullOrEmpty(w.DeletedTime));
}
if (filter.OwnerIds != null && filter.OwnerIds.Count > 0)
{
q = q.Where(w => filter.OwnerIds.Contains(w.Id.Value));
}
int total = 0;
var data = q.ToPageList(filter.PageIndex, filter.PageSize, ref total).MapToList <OwnerDto>();
filter.TotalCount = total;
return(data);
}
}
private void StringIsNullOrEmpty3()
{
int a = 1;
Expression <Func <Student, bool> > exp = it => 2 == it.Id || SqlFunc.IsNullOrEmpty(a);;
OracleExpressionContext expContext = new OracleExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( :Id0 = \"ID\" ) OR ( :MethodConst1='' OR :MethodConst1 IS NULL ))", new List <SugarParameter>()
{
new SugarParameter(":MethodConst1", 1),
new SugarParameter(":Id0", 2)
}, "StringIsNullOrEmpty3 error");
}
private void StringIsNullOrEmpty4()
{
WhereConst.name = "xx";
Expression <Func <Student, bool> > exp = it => 2 == it.Id || SqlFunc.IsNullOrEmpty(WhereConst.name);;
MySqlExpressionContext expContext = new MySqlExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(value, pars, "(( @Id0 = `Id` ) OR ( @MethodConst1='' OR @MethodConst1 IS NULL ))", new List <SugarParameter>()
{
new SugarParameter("@MethodConst1", "xx"),
new SugarParameter("@Id0", 2)
}, "StringIsNullOrEmpty4 error");
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().ToList();
var getTop2 = db.Queryable <Student>().Take(2).ToList();//TOP2
var getLike = db.Queryable <Student>().Where(it => it.Name.Contains("a")).ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 2).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Sum(it => it.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var asCount = db.Queryable <object>().AS("student").Count();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var unionAll = db.UnionAll <Student>(db.Queryable <Student>(), db.Queryable <Student>());
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
}
private static void EasyExamples()
{
Console.WriteLine("");
Console.WriteLine("#### Examples Start ####");
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Order>().ToList();
var getOrderBy = db.Queryable <Order>().OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy2 = db.Queryable <Order>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy3 = db.Queryable <Order>().OrderBy(it => new { it.Name, it.Id }).ToList();
var getRandom = db.Queryable <Order>().OrderBy(it => SqlFunc.GetRandom()).First();
var getByPrimaryKey = db.Queryable <Order>().InSingle(2);
var getSingleOrDefault = db.Queryable <Order>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Order>().First();
var getByWhere = db.Queryable <Order>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Order>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Order>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var getByFuns2 = db.Queryable <Order>().GroupBy(it => it.Name).Select(it => SqlFunc.AggregateDistinctCount(it.Price)).ToList();
var test1 = db.UnionAll(
db.Queryable <Order>().Select(it => new ViewOrder()
{
CustomName = SqlFunc.MergeString(it.Name, "/", it.Name)
}),
db.Queryable <Order>().Select(it => new ViewOrder()
{
CustomName = SqlFunc.MergeString(it.Name, "/", it.Name)
})
).ToList();
var test2 = db.Queryable <Order>().Select <ViewOrder>().ToList();
var test3 = db.Queryable <Order>().Select(it => new Order()
{
CreateTime = SqlFunc.GetDate()
}).ToList();
var btime = Convert.ToDateTime("2021-2-1");
var etime = Convert.ToDateTime("2022-1-12");
var test01 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Year, btime, etime)).ToList();
var test02 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Day, btime, etime)).ToList();
var test03 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Month, btime, etime)).ToList();
var test04 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Second, DateTime.Now, DateTime.Now.AddMinutes(2))).ToList();
var test05 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Minute, DateTime.Now, DateTime.Now.AddMinutes(21))).ToList();
var test06 = db.Queryable <Order>().Select(it => SqlFunc.DateDiff(DateType.Hour, DateTime.Now, DateTime.Now.AddHours(3))).ToList();
var test07 = db.Queryable <Order>().Select(it => it.CreateTime.DayOfWeek.ToString()).ToList();
var test08 = db.Queryable <Order>().Select(it => it.CreateTime.AddDays(1)).ToList();
Console.WriteLine("#### Examples End ####");
}
/// <summary>
///
/// </summary>
/// <param name="requestImUserName">用户名</param>
/// <param name="requestUserId">用户标示</param>
/// <param name="requestUserType">用户类型(1:内部用户，2：会员用户；3：第三方用户；4：临时用户)</param>
/// <param name="requestGroupId">组标识</param>
/// <param name="pageSize">页面大小</param>
/// <param name="total">合计</param>
/// <param name="pageIndex">页面索引</param>
/// <returns></returns>
public List <ImUserInfoPo> QueryImUserInfo(string requestImUserName, string requestUserId, int?requestUserType, Guid?requestGroupId, int pageIndex, int pageSize, out int total)
{
List <ImUserInfoPo> list = new List <ImUserInfoPo>();
var totalcount = 0;
if (requestGroupId == null)
{
#region 根据用户信息查询
var datalist = CurrentDbContext.Queryable <ImUserInfoPo>().With(SqlWith.NoLock).Where(e =>
e.IsValid == 1 &&
(SqlFunc.IsNullOrEmpty(requestImUserName) || e.ImUserName.Contains(requestImUserName)) &&
(SqlFunc.IsNullOrEmpty(requestUserId) || e.UserId == requestUserId) &&
(SqlFunc.IsNullOrEmpty(requestUserType) || e.UserType == requestUserType))
.OrderBy(e => e.CreateTime)
.ToPageList(pageIndex, pageSize, ref totalcount);
if (datalist != null)
{
list = datalist;
}
#endregion
}
else
{
#region 在组内查询用户信息
var datalist = CurrentDbContext.Queryable <ImUserInfoPo, UserGroupPo>((t1, t2) => new object[]
{
JoinType.Left, t1.ImUserId == t2.ImUserId
}).Where((t1, t2) =>
t2.GroupId == requestGroupId.Value &&
t1.IsValid == 1 && t2.IsValid == 1 &&
(SqlFunc.IsNullOrEmpty(requestImUserName) || t1.ImUserName.Contains(requestImUserName)) &&
(SqlFunc.IsNullOrEmpty(requestUserId) || t1.UserId == requestUserId) &&
(SqlFunc.IsNullOrEmpty(requestUserType) || t1.UserType == requestUserType)
).With(SqlWith.NoLock).OrderBy((t1, t2) => t2.CreateTime).Select((t1, t2) => t1).ToPageList(pageIndex, pageSize, ref totalcount);
#endregion
if (datalist != null)
{
list = datalist;
}
}
total = totalcount;
return(list);
}
/// <summary>
/// 获取物料
/// </summary>
/// <param name="PackageID"></param>
/// <param name="ColorSolutionID"></param>
/// <returns></returns>
public TBMMaterialFileDbModel GetMaterialFileByPackageColor(int PackageID, int?ColorSolutionID)
{
if (ColorSolutionID != null)
{
var resutl = _db.Instance.Queryable <TMMColorSolutionMainDbModel, TBMMaterialFileDbModel>((t, t1) => new object[] {
JoinType.Inner, t.ID == t1.ColorSolutionID && t.PackageId == t1.PackageID
}).Where((t, t1) => t.PackageId == PackageID && t.ID == ColorSolutionID).Select((t, t1) => t1).First();
return(resutl);
}
else
{
var resutl = _db.Instance.Queryable <TMMColorSolutionMainDbModel, TBMMaterialFileDbModel>((t, t1) => new object[] {
JoinType.Inner, t.PackageId == t1.PackageID
}).Where((t, t1) => t.PackageId == PackageID && SqlFunc.IsNullOrEmpty(t1.ColorSolutionID)).Select((t, t1) => t1).First();
return(resutl);
}
}
/// <summary>
/// 文章列表分页
/// </summary>
/// <param name="key">关键词</param>
/// <param name="id">栏目id或表情id</param>
/// <param name="type">1：栏目id；2：表情id</param>
/// <param name="sort">排序</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示的条数</param>
/// <returns></returns>
public Tuple <List <ArticleDto>, int> ArticleList(string key, string id, int type, string sort, int pageIndex, int pageSize)
{
int total = 0;
var query = Db.Queryable <ArticleInfo>().Where(a => a.Visible == true);
if (!string.IsNullOrEmpty(key))
{
query.Where(a => a.Title.Contains(key) ||
SqlFunc.Subqueryable <TagsInfo>().Where(t => t.TagName.ToLower().Contains(key.ToLower())).Any() ||
SqlFunc.Subqueryable <CategoryInfo>().Where(ci => ci.CategoryName.ToLower().Contains(key.ToLower())).Any()
);
}
if (!string.IsNullOrWhiteSpace(id))
{
if (type == 1)
{
query.Where(a => SqlFunc.Subqueryable <ArticleCategory>().Where(ac => ac.ArticleId == a.ArticleId && ac.CategoryId == id).Any());
}
else
{
query.Where(a => SqlFunc.Subqueryable <ArticleTags>().Where(at => at.ArticleId == a.ArticleId && at.TagsId == id).Any());
}
}
if (!string.IsNullOrWhiteSpace(sort))
{
query.OrderBy(sort);
}
List <ArticleDto> ad = query.Select <ArticleDto>().Mapper((it, cache) =>
{
Db.QueryFilter.Clear();
var tids = cache.Get(list =>
{
var ids = list.Select(i => it.ArticleId).ToList();
return(Db.Queryable <ArticleTags>().Where(c => ids.Contains(c.ArticleId)).ToList().Select(f => f.TagsId));
});
it.Tags = Db.Queryable <TagsInfo>().Where(c => c.DeleteMark == false && c.EnabledMark == true && tids.Contains(c.TagId)).Select <TagDto>().ToList();
it.MsgTimes = Db.Queryable <LeavemsgInfo>().Where(msg => SqlFunc.IsNullOrEmpty(msg.ToUId) && msg.ArticleId == it.ArticleId).Count();
}).ToPageList(pageIndex, pageSize, ref total);
return(Tuple.Create(ad, total));
}