/// <summary> /// 执行分页查询 /// </summary> /// <param name="pWhereConditions">筛选条件</param> /// <param name="pOrderBys">排序</param> /// <param name="pPageSize">每页的记录数</param> /// <param name="pCurrentPageIndex">以0开始的当前页码</param> /// <returns></returns> public PagedQueryResult <CSConversationEntity> PagedQuery(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pPageSize, int pCurrentPageIndex) { //组织SQL StringBuilder pagedSql = new StringBuilder(); StringBuilder totalCountSql = new StringBuilder(); //分页SQL pagedSql.AppendFormat("select * from (select row_number()over( order by "); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { if (item != null) { pagedSql.AppendFormat(" {0} {1},", StringUtils.WrapperSQLServerObject(item.FieldName), item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } } pagedSql.Remove(pagedSql.Length - 1, 1); } else { pagedSql.AppendFormat(" [CSConversationID] desc"); //默认为主键值倒序 } //取员工的头像***** pagedSql.AppendFormat(@") as ___rn,*, (case when IsCs=1 then isnull(( select top 1 ImageURL from ObjectImages where ObjectId=PersonID and IsDelete=0 order by CreateTime desc),'') else '' end ) as UserHeadUrl ,(select top 1 HeadImageUrl from CSConversation x where IsCS=0 and CSMessageID=a.CSMessageID order by x.CreateTime desc ) as VipHeadImage ,(select top 1 person from CSConversation x where IsCS=0 and CSMessageID=a.CSMessageID order by x.CreateTime desc ) as VipName ,(select MemberID from CSMessage x where x.CSMessageID=a.CSMessageID ) as VipID from [CSConversation] a where isdelete=0 "); //总记录数SQL totalCountSql.AppendFormat("select count(1) from [CSConversation] where isdelete=0 "); //过滤条件 if (pWhereConditions != null) { foreach (var item in pWhereConditions) { if (item != null) { pagedSql.AppendFormat(" and {0}", item.GetExpression()); totalCountSql.AppendFormat(" and {0}", item.GetExpression()); } } } pagedSql.AppendFormat(") as A "); //取指定页的数据 pagedSql.AppendFormat(" where ___rn >{0} and ___rn <={1}", pPageSize * (pCurrentPageIndex - 1), pPageSize * (pCurrentPageIndex)); //执行语句并返回结果 PagedQueryResult <CSConversationEntity> result = new PagedQueryResult <CSConversationEntity>(); string rootUrl = ConfigurationManager.AppSettings["website_url"].Trim(); // rootUrl = !rootUrl.EndsWith("/") ? rootUrl + "/" : rootUrl; List <CSConversationEntity> list = new List <CSConversationEntity>(); using (SqlDataReader rdr = this.SQLHelper.ExecuteReader(pagedSql.ToString())) { while (rdr.Read()) { CSConversationEntity m; this.Load(rdr, out m); if (!string.IsNullOrEmpty(m.UserHeadUrl)) { m.UserHeadUrl = rootUrl + m.UserHeadUrl;//加上员工的头像 //因为一条记录就记载着一句话(会员或者客服的) // 所以可以把 // m.HeadImageUrl = rootUrl + m.UserHeadUrl } list.Add(m); } } result.Entities = list.ToArray(); int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(totalCountSql.ToString())); //计算总行数 result.RowCount = totalCount; int remainder = 0; result.PageCount = Math.DivRem(totalCount, pPageSize, out remainder); if (remainder > 0) { result.PageCount++; } return(result); }
/// <summary> /// 执行分页查询 /// </summary> /// <param name="pWhereConditions">筛选条件</param> /// <param name="pOrderBys">排序</param> /// <param name="pPageSize">每页的记录数</param> /// <param name="pCurrentPageIndex">以0开始的当前页码</param> /// <returns></returns> public PagedQueryResult <VipItemMappingEntity> PagedQuery(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pPageSize, int pCurrentPageIndex) { //组织SQL StringBuilder pagedSql = new StringBuilder(); StringBuilder totalCountSql = new StringBuilder(); //分页SQL pagedSql.AppendFormat("select * from (select row_number()over( order by "); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { if (item != null) { pagedSql.AppendFormat(" {0} {1},", StringUtils.WrapperSQLServerObject(item.FieldName), item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } } pagedSql.Remove(pagedSql.Length - 1, 1); } else { pagedSql.AppendFormat(" [VipItemId] desc"); //默认为主键值倒序 } pagedSql.AppendFormat(") as ___rn,* from [VipItemMapping] where isdelete=0 "); //总记录数SQL totalCountSql.AppendFormat("select count(1) from [VipItemMapping] where isdelete=0 "); //过滤条件 if (pWhereConditions != null) { foreach (var item in pWhereConditions) { if (item != null) { pagedSql.AppendFormat(" and {0}", item.GetExpression()); totalCountSql.AppendFormat(" and {0}", item.GetExpression()); } } } pagedSql.AppendFormat(") as A "); //取指定页的数据 pagedSql.AppendFormat(" where ___rn >{0} and ___rn <={1}", pPageSize * (pCurrentPageIndex - 1), pPageSize * (pCurrentPageIndex)); //执行语句并返回结果 PagedQueryResult <VipItemMappingEntity> result = new PagedQueryResult <VipItemMappingEntity>(); List <VipItemMappingEntity> list = new List <VipItemMappingEntity>(); using (SqlDataReader rdr = this.SQLHelper.ExecuteReader(pagedSql.ToString())) { while (rdr.Read()) { VipItemMappingEntity m; this.Load(rdr, out m); list.Add(m); } } result.Entities = list.ToArray(); int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(totalCountSql.ToString())); //计算总行数 result.RowCount = totalCount; int remainder = 0; result.PageCount = Math.DivRem(totalCount, pPageSize, out remainder); if (remainder > 0) { result.PageCount++; } return(result); }
/// <summary> /// 执行分页查询 /// </summary> /// <param name="pWhereConditions">筛选条件</param> /// <param name="pOrderBys">排序</param> /// <param name="pPageSize">每页的记录数</param> /// <param name="pCurrentPageIndex">以0开始的当前页码</param> /// <returns></returns> public PagedQueryResult <T_SalesReturnEntity> PagedQuery(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pPageSize, int pCurrentPageIndex) { //组织SQL StringBuilder pagedSql = new StringBuilder(); StringBuilder totalCountSql = new StringBuilder(); string sql = ""; //如果是后台账户登录,就取登录账户的信息能看到的门店信息 if (this.CurrentUserInfo != null && !string.IsNullOrEmpty(this.CurrentUserInfo.UserID) && !string.IsNullOrEmpty(this.CurrentUserInfo.ClientID)) { pagedSql.AppendFormat(@"DECLARE @AllUnit NVARCHAR(200) CREATE TABLE #UnitSET (UnitID NVARCHAR(100)) INSERT #UnitSET (UnitID) SELECT DISTINCT R.UnitID FROM T_User_Role UR CROSS APPLY dbo.FnGetUnitList ('{0}',UR.unit_id,205) R WHERE user_id='{1}' ---根据账户的角色去查角色对应的 所有门店unit_id ", this.CurrentUserInfo.ClientID, this.CurrentUserInfo.UserID); totalCountSql.AppendFormat(@"DECLARE @AllUnit NVARCHAR(200) CREATE TABLE #UnitSET (UnitID NVARCHAR(100)) INSERT #UnitSET (UnitID) SELECT DISTINCT R.UnitID FROM T_User_Role UR CROSS APPLY dbo.FnGetUnitList ('{0}',UR.unit_id,205) R WHERE user_id='{1}' ---根据账户的角色去查角色对应的 所有门店unit_id ", this.CurrentUserInfo.ClientID, this.CurrentUserInfo.UserID); } //分页SQL pagedSql.AppendFormat("select * from (select row_number()over( order by "); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { if (item != null) { pagedSql.AppendFormat(" {0} {1},", StringUtils.WrapperSQLServerObject(item.FieldName), item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } } pagedSql.Remove(pagedSql.Length - 1, 1); } else { pagedSql.AppendFormat(" [SalesReturnID] desc"); //默认为主键值倒序 } pagedSql.AppendFormat(@") as ___rn,r.*,t.paymentcenter_id,p.Payment_Type_Name from [T_SalesReturn] as r left join t_inout as t on r.OrderID=t.order_id left join T_Payment_Type as p on t.pay_id=p.Payment_Type_Id and p.IsDelete=0 "); if (this.CurrentUserInfo != null && !string.IsNullOrEmpty(this.CurrentUserInfo.UserID) && !string.IsNullOrEmpty(this.CurrentUserInfo.ClientID)) { pagedSql.AppendFormat(@" left join #UnitSET b on (t.purchase_unit_id=b.UnitID or t.sales_unit_id=b.UnitID) "); } pagedSql.AppendFormat(@" where 1=1 and r.isdelete=0 "); //总记录数SQL totalCountSql.AppendFormat(@"select count(1) from [T_SalesReturn] as r left join t_inout as t on r.OrderID=t.order_id left join T_Payment_Type as p on t.pay_id=p.Payment_Type_Id and p.IsDelete=0 "); if (this.CurrentUserInfo != null && !string.IsNullOrEmpty(this.CurrentUserInfo.UserID) && !string.IsNullOrEmpty(this.CurrentUserInfo.ClientID)) { totalCountSql.AppendFormat(@" left join #UnitSET b on (t.purchase_unit_id=b.UnitID or t.sales_unit_id=b.UnitID) "); } totalCountSql.AppendFormat(@" where 1=1 and r.isdelete=0 "); //过滤条件 if (pWhereConditions != null) { foreach (var item in pWhereConditions) { if (item != null) { pagedSql.AppendFormat(" and {0}", item.GetExpression()); totalCountSql.AppendFormat(" and {0}", item.GetExpression());//分别加上条件 } } } pagedSql.AppendFormat(") as A "); //取指定页的数据 pagedSql.AppendFormat(" where ___rn >{0} and ___rn <={1}", pPageSize * (pCurrentPageIndex - 1), pPageSize * (pCurrentPageIndex)); //删除临时表 if (this.CurrentUserInfo != null && !string.IsNullOrEmpty(this.CurrentUserInfo.UserID) && !string.IsNullOrEmpty(this.CurrentUserInfo.ClientID)) { pagedSql.AppendFormat(" drop table #UnitSET;"); totalCountSql.AppendFormat(" drop table #UnitSET;"); } //执行语句并返回结果 PagedQueryResult <T_SalesReturnEntity> result = new PagedQueryResult <T_SalesReturnEntity>(); List <T_SalesReturnEntity> list = new List <T_SalesReturnEntity>(); using (SqlDataReader rdr = this.SQLHelper.ExecuteReader(pagedSql.ToString())) { while (rdr.Read()) { T_SalesReturnEntity m; this.Load(rdr, out m); if (rdr["paymentcenter_id"] != DBNull.Value) { m.paymentcenterId = Convert.ToString(rdr["paymentcenter_id"]); } if (rdr["Payment_Type_Name"] != DBNull.Value) { m.PayTypeName = Convert.ToString(rdr["Payment_Type_Name"]); } list.Add(m); } } result.Entities = list.ToArray(); int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(totalCountSql.ToString())); //计算总行数 result.RowCount = totalCount; int remainder = 0; result.PageCount = Math.DivRem(totalCount, pPageSize, out remainder); if (remainder > 0) { result.PageCount++; } return(result); }
public virtual async Task <PagedQueryResult> GetPageDescending( int pageNumber, int pageSize, string logLevel = "", CancellationToken cancellationToken = default(CancellationToken) ) { cancellationToken.ThrowIfCancellationRequested(); ThrowIfDisposed(); var pathToFolder = await pathResolver.ResolvePath(options.ProjectId).ConfigureAwait(false); var result = new PagedQueryResult(); if (!Directory.Exists(pathToFolder)) { return(result); } if (!string.IsNullOrWhiteSpace(logLevel)) { pathToFolder = Path.Combine(pathToFolder, logLevel); if (!Directory.Exists(pathToFolder)) { return(result); } } int offset = (pageSize * pageNumber) - pageSize; int skipped = 0; int added = 0; var dir = new DirectoryInfo(pathToFolder); foreach (FileInfo file in dir.GetFiles( "*" + serializer.ExpectedFileExtension, SearchOption.AllDirectories).OrderByDescending(f => f.CreationTimeUtc) ) { if (offset > 0) { if (skipped < offset) { skipped += 1; result.TotalItems += 1; continue; } } if (added >= pageSize) { result.TotalItems += 1; continue; } var key = Path.GetFileNameWithoutExtension(file.Name); var obj = LoadObject(file.FullName, key); result.Items.Add(obj); added += 1; result.TotalItems += 1; } return(result); }
private async Task <AmpMessage> SearchUsersAsync(IServiceProvider serviceProvider, AmpMessage req) { var rsp = AmpMessage.CreateResponseMessage(req.ServiceId, req.MessageId); var message = Encoding.UTF8.GetString(req.Data); var logger = serviceProvider.GetRequiredService <ILoggerFactory>().CreateLogger <UserService>(); logger.LogInformation($"ServiceId: {req.ServiceId}, MessageId: {req.MessageId}, Data: {message}"); var dbContext = serviceProvider.GetRequiredService <IDbContext>(); var options = serviceProvider.GetRequiredService <AdminOptions>(); if (!options.AllowAnonymousUserQuery) { return(rsp); } var dto = JsonConvert.DeserializeObject <PagedQueryUserDto>(message); Expression <Func <User, bool> > where = null; dto.Q = string.IsNullOrWhiteSpace(dto.Q) ? null : dto.Q.Trim(); dto.Group = string.IsNullOrWhiteSpace(dto.Group) ? null : dto.Group.Trim(); if (!string.IsNullOrWhiteSpace(dto.Q)) { where = u => (u.FirstName + u.LastName).Contains(dto.Q) || (u.LastName + u.FirstName).Contains(dto.Q) || u.Email.Contains(dto.Q) || u.PhoneNumber.Contains(dto.Q); } if (!string.IsNullOrWhiteSpace(dto.Group)) { if (where == null) { where = u => u.Group == dto.Group.Trim(); } else { where = where.AndAlso(u => u.Group == dto.Group.Trim()); } } if (dto.Titles != null && dto.Titles.Length > 0) { if (where == null) { where = u => dto.Titles.Contains(u.Title); } else { where = where.AndAlso(u => dto.Titles.Contains(u.Title)); } } if (dto.Roles == null || dto.Roles.Length == 0) { var output = await dbContext.Users.OrderByDescending(u => u.CreationTime).PagedQueryAsync(dto, where); var result = new PagedQueryResult { Page = output.Page, Size = output.Size, Total = output.Total, Result = output.Result.Select(u => new { u.Id, Name = String.CompareOrdinal(u.LastName, u.FirstName), u.Title, u.Email, Mobile = u.PhoneNumber, u.OfficePhone, u.Group, u.Level, u.UserName, u.IsDeleted }).ToList() }; rsp.Data = Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(result)); } else { var roleIds = await dbContext.Roles.Where(r => dto.Roles.Contains(r.Name)).Select(r => r.Id) .ToListAsync(); var result = await dbContext.Users.OrderByDescending(u => u.CreationTime).Join(dbContext.UserRoles, u => u.Id, ur => ur.UserId, (u, ur) => new TmpUser { Id = u.Id, Name = string.Concat(u.LastName, u.FirstName), Title = u.Title, Email = u.Email, Mobile = u.PhoneNumber, OfficePhone = u.OfficePhone, Group = u.Group, Level = u.Level, RoleId = ur.RoleId, UserName = u.UserName, CreationTime = u.CreationTime, IsDeleted = u.IsDeleted }).Where(t => roleIds.Contains(t.RoleId) && (string.IsNullOrWhiteSpace(dto.Q) || t.Name.Contains(dto.Q) || t.Email.Contains(dto.Q) || t.Mobile.Contains(dto.Q)) && (string.IsNullOrWhiteSpace(dto.Group) || t.Group == dto.Group) && (dto.Titles == null || dto.Titles.Length == 0 || dto.Titles.Contains(t.Title))).Distinct() .PagedQueryAsync(dto); var output = new PagedQueryResult { Total = result.Total, Size = result.Size, Page = result.Page, Result = result.Result.Select(r => new { r.Id, r.Name, r.Title, r.Email, r.Mobile, r.OfficePhone, r.Group, r.Level, r.UserName, r.IsDeleted }) }; rsp.Data = Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(output)); } return(rsp); }
/// <summary> /// 根据分销商名称执行分页查询 /// </summary> /// <param name="pWhereConditions">筛选条件</param> /// <param name="pOrderBys">排序</param> /// <param name="pPageSize">每页的记录数</param> /// <param name="pCurrentPageIndex">以0开始的当前页码</param> /// <returns></returns> public PagedQueryResult <VipWithdrawDepositApplyEntity> PagedQueryByRetailName(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pPageSize, int pCurrentPageIndex) { //组织SQL StringBuilder pagedSql = new StringBuilder(); StringBuilder totalCountSql = new StringBuilder(); //分页SQL pagedSql.AppendFormat("select * from (select row_number()over( order by "); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { if (item != null) { pagedSql.AppendFormat(" {0} {1},", StringUtils.WrapperSQLServerObject(item.FieldName), item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } } pagedSql.Remove(pagedSql.Length - 1, 1); } else { pagedSql.AppendFormat(" [ApplyID] desc"); //默认为主键值倒序 } pagedSql.AppendFormat(@") as ___rn,a.*,u.RetailTraderName,CardNo,BankName,AccountName FROM VipWithdrawDepositApply a INNER JOIN dbo.RetailTrader u ON u.RetailTraderID=a.VipID left join VipBank d on a.vipbankid=d.vipbankid left join bank e on d.bankid=e.bankid where 1=1 and a.isdelete=0 "); //总记录数SQL totalCountSql.AppendFormat("select count(1) FROM VipWithdrawDepositApply a INNER JOIN dbo.RetailTrader u ON u.RetailTraderID=a.VipID where 1=1 and a.isdelete=0 "); //过滤条件 if (pWhereConditions != null) { foreach (var item in pWhereConditions) { if (item != null) { pagedSql.AppendFormat(" and {0}", item.GetExpression()); totalCountSql.AppendFormat(" and {0}", item.GetExpression()); } } } pagedSql.AppendFormat(") as A "); //取指定页的数据 pagedSql.AppendFormat(" where ___rn >{0} and ___rn <={1}", pPageSize * (pCurrentPageIndex - 1), pPageSize * (pCurrentPageIndex)); //执行语句并返回结果 PagedQueryResult <VipWithdrawDepositApplyEntity> result = new PagedQueryResult <VipWithdrawDepositApplyEntity>(); List <VipWithdrawDepositApplyEntity> list = new List <VipWithdrawDepositApplyEntity>(); using (SqlDataReader rdr = this.SQLHelper.ExecuteReader(pagedSql.ToString())) { while (rdr.Read()) { VipWithdrawDepositApplyEntity m; this.Load(rdr, out m); if (rdr["RetailTraderName"] != DBNull.Value) { m.VipName = Convert.ToString(rdr["RetailTraderName"]); } if (rdr["CardNo"] != DBNull.Value) { m.CardNo = Convert.ToString(rdr["CardNo"]); } if (rdr["AccountName"] != DBNull.Value) { m.AccountName = Convert.ToString(rdr["AccountName"]); } list.Add(m); } } result.Entities = list.ToArray(); int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(totalCountSql.ToString())); //计算总行数 result.RowCount = totalCount; int remainder = 0; result.PageCount = Math.DivRem(totalCount, pPageSize, out remainder); if (remainder > 0) { result.PageCount++; } return(result); }
protected override void AjaxRequest(HttpContext pContext) { var loggingSessionInfo = new SessionManager().CurrentUserLoginInfo; //登录状态信息 //var form = Request("param").DeserializeJSONTo<GetTSuperRetailTraderConfigRP>(); int PageIndex = Convert.ToInt32(pContext.Request.QueryString["PageIndex"]); int PageSize = Convert.ToInt32(pContext.Request.QueryString["PageSizes"]); string SuperRetailTraderName = pContext.Request.QueryString["SuperRetailTraderName"] + ""; string SuperRetailTraderFrom = pContext.Request.QueryString["SuperRetailTraderFrom"] + ""; string JoinSatrtTime = pContext.Request.QueryString["JoinSatrtTime"] + ""; string JoinEndTime = pContext.Request.QueryString["JoinEndTime"] + ""; List <IWhereCondition> pWhereConditions = new List <IWhereCondition>() { new EqualsCondition() { FieldName = "a.CustomerId", Value = loggingSessionInfo.ClientID }, new EqualsCondition() { FieldName = "a.IsDelete", Value = 0 }, new EqualsCondition() { FieldName = "a.Status", Value = "10" } }; if (!String.IsNullOrEmpty(SuperRetailTraderName)) { pWhereConditions.Add(new EqualsCondition() { FieldName = "a.SuperRetailTraderName", Value = SuperRetailTraderName }); } if (!String.IsNullOrEmpty(SuperRetailTraderFrom)) { pWhereConditions.Add(new EqualsCondition() { FieldName = "a.SuperRetailTraderFrom", Value = SuperRetailTraderFrom }); } if (!String.IsNullOrEmpty(JoinSatrtTime)) { pWhereConditions.Add(new EqualsCondition() { FieldName = "a.JoinSatrtTime", Value = JoinSatrtTime }); } if (!String.IsNullOrEmpty(JoinEndTime)) { pWhereConditions.Add(new EqualsCondition() { FieldName = "a.JoinEndTime", Value = JoinEndTime }); } T_SuperRetailTraderBLL bll = new T_SuperRetailTraderBLL(loggingSessionInfo); PagedQueryResult <T_SuperRetailTraderEntity> models = bll.FindListByCustomerId(pWhereConditions.ToArray(), null, PageIndex, PageSize, loggingSessionInfo.ClientID); try { #region 获取信息 //var param = Request("param").DeserializeJSONTo<dynamic>(); #endregion string MapUrl = pContext.Server.MapPath(@"~/Framework/Upload/" + DateTime.Now.ToString("yyyy.MM.dd.HH.mm.ss.ms") + ".xls"); Aspose.Cells.License lic = new Aspose.Cells.License(); lic.SetLicense("Aspose.Total.lic"); Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; Cells cells = sheet.Cells;//单元格 #region //为标题设置样式 Style styleTitle = workbook.Styles[workbook.Styles.Add()]; //新增样式 styleTitle.HorizontalAlignment = TextAlignmentType.Center; //文字居中 styleTitle.Font.Name = "宋体"; //文字字体 styleTitle.Font.Size = 18; //文字大小 styleTitle.Font.IsBold = true; //粗体 //样式2 Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 14; //文字大小 style2.Font.IsBold = true; //粗体 style2.IsTextWrapped = true; //单元格内容自动换行 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 12; //文字大小 style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; #endregion //生成行1 标题行 cells.Merge(0, 0, 1, 9); //合并单元格 cells[0, 0].PutValue("分销商列表"); //填写内容 cells[0, 0].SetStyle(styleTitle); cells.SetRowHeight(0, 38); List <SuperRetailTraderListInfo> lst = models.Entities.Select(m => new SuperRetailTraderListInfo() { JoinTime = Convert.ToDateTime(m.JoinTime).ToString("yyyy-MM-dd"), NumberOffline = m.NumberOffline, OrderCount = m.OrderCount, SuperRetailTraderFrom = m.SuperRetailTraderFrom, SuperRetailTraderName = m.SuperRetailTraderName, SuperRetailTraderPhone = m.SuperRetailTraderPhone, WithdrawCount = m.WithdrawCount, WithdrawTotalMoney = m.WithdrawTotalMoney }).ToList(); //生成行2 列名行 for (int i = 0; i < 8; i++) { cells.SetColumnWidth(i, 30); } #region 列明 cells[1, 0].PutValue("分销商姓名"); cells[1, 0].SetStyle(style2); cells.SetRowHeight(1, 25); cells[1, 1].PutValue("分销商手机号码"); cells[1, 1].SetStyle(style2); cells.SetRowHeight(1, 25); cells[1, 2].PutValue("分销商来源"); cells[1, 2].SetStyle(style2); cells.SetRowHeight(1, 25); cells[1, 3].PutValue("分销商下线人数"); cells[1, 3].SetStyle(style2); cells.SetRowHeight(1, 25); cells[1, 4].PutValue("分销商订单总数"); cells[1, 4].SetStyle(style2); cells[1, 5].PutValue("分销商提现次数"); cells[1, 5].SetStyle(style2); cells[1, 6].PutValue("分销商提现总金额"); cells[1, 6].SetStyle(style2); cells[1, 7].PutValue("加盟时间"); cells.SetRowHeight(1, 25); #endregion #region 生成数据行 for (int i = 0; i < lst.Count; i++) { cells[2 + i, 0].PutValue(lst[i].SuperRetailTraderName); cells[2 + i, 0].SetStyle(style3); cells[2 + i, 1].PutValue(lst[i].SuperRetailTraderPhone); cells[2 + i, 1].SetStyle(style3); cells[2 + i, 2].PutValue(lst[i].SuperRetailTraderFrom); cells[2 + i, 2].SetStyle(style3); cells[2 + i, 3].PutValue(lst[i].NumberOffline); cells[2 + i, 3].SetStyle(style3); cells[2 + i, 4].PutValue(lst[i].OrderCount); cells[2 + i, 4].SetStyle(style3); cells[2 + i, 5].PutValue(lst[i].WithdrawCount); cells[2 + i, 5].SetStyle(style3); cells[2 + i, 6].PutValue(lst[i].WithdrawTotalMoney); cells[2 + i, 6].SetStyle(style3); cells[2 + i, 7].PutValue(lst[i].JoinTime); cells[2 + i, 7].SetStyle(style3); cells.SetRowHeight(2 + i, 24); } #endregion workbook.Save(MapUrl); Utils.OutputExcel(pContext, MapUrl);//输出Excel文件 } catch (Exception ex) { throw (ex); } }
public async Task <dynamic> QueryAsync(string keyword, string source, int page, int limit) { if (!(User.IsInRole("cerberus-admin") || User.IsInRole("admin"))) { var role1 = $"{source.Trim()}-admin"; var role2 = $"{source.Trim()}-user-admin"; if (!(User.IsInRole(role1) || User.IsInRole(role2))) { return(StatusCode(401)); } } PagedQueryResult <User> result; if (string.IsNullOrWhiteSpace(keyword)) { if (string.IsNullOrWhiteSpace(source)) { result = await _dbContext.Users.Include(x => x.UserClaims) .PagedQueryAsync(page, limit, x => x.IsDeleted == false, new OrderCondition <User, DateTimeOffset?>(x => x.LastModificationTime, true)); } else { result = await _dbContext.Users.Include(x => x.UserClaims).PagedQueryAsync(page, limit, x => x.IsDeleted == false && x.Source == source.Trim(), new OrderCondition <User, DateTimeOffset?>(x => x.LastModificationTime, true)); } } else { keyword = keyword.Trim(); if (string.IsNullOrWhiteSpace(source)) { result = await _dbContext.Users.Include(x => x.UserClaims).PagedQueryAsync(page, limit, x => x.IsDeleted == false && (x.UserName.Contains(keyword) || x.Email.Contains(keyword) || x.PhoneNumber.Contains(keyword)), new OrderCondition <User, DateTimeOffset?>(x => x.LastModificationTime, true)); } else { source = source.Trim(); result = await _dbContext.Users.Include(x => x.UserClaims).PagedQueryAsync(page, limit, x => x.Source == source && x.IsDeleted == false && (x.UserName.Contains(keyword) || x.Email.Contains(keyword) || x.PhoneNumber.Contains(keyword)), new OrderCondition <User, DateTimeOffset?>(x => x.LastModificationTime, true)); } } var output = new PagedQueryResult <ListUserDTO>() { Page = result.Page, Limit = result.Limit, Count = result.Count, Data = new List <ListUserDTO>() }; foreach (var user in result.Data) { var familyName = user.UserClaims.FirstOrDefault(x => x.ClaimType == JwtRegisteredClaimNames.FamilyName); var givenName = user.UserClaims.FirstOrDefault(x => x.ClaimType == JwtRegisteredClaimNames.GivenName); var title = user.UserClaims.FirstOrDefault(x => x.ClaimType == "title"); var company = user.UserClaims.FirstOrDefault(x => x.ClaimType == "company"); output.Data.Add(new ListUserDTO { Id = user.Id, Email = user.Email, PhoneNumber = user.PhoneNumber, Source = user.Source, FamilyName = familyName == null ? string.Empty : familyName.ClaimValue, GivenName = givenName == null ? string.Empty : givenName.ClaimValue, UserName = user.UserName, Title = title == null ? string.Empty : title.ClaimValue, Company = company == null ? string.Empty : company.ClaimValue, Enabled = user.Enabled }); } return(output); }
/// <summary> /// 获取分销商Sku列表 /// </summary> /// <param name="itemName"></param> /// <param name="status"></param> /// <returns></returns> public PagedQueryResult <T_SuperRetailTraderSkuMappingEntity> GetSuperRetailTraderSkuList(string itemCategoryId, string itemName, int status, int pageSize, int pageIndex) { List <T_SuperRetailTraderSkuMappingEntity> list = new List <T_SuperRetailTraderSkuMappingEntity>(); StringBuilder pagedSql = new StringBuilder(); StringBuilder totalCountSql = new StringBuilder(); //临时表 pagedSql.AppendFormat(@"Create Table Temp (ItemId varchar(50))"); pagedSql.AppendFormat(@"insert into Temp select ItemId from ( select distinct dense_rank() over(order by createtime desc) as rownum, ItemId from ( select a.ItemId, a.createtime from T_SuperRetailTraderItemMapping a inner join vw_item_Detail b on a.ItemId = b.item_id and b.status = 1 inner join T_SuperRetailTraderSkuMapping x on x.ItemId = a.ItemId "); if (!string.IsNullOrEmpty(itemCategoryId)) { pagedSql.AppendFormat(" inner join [dbo].[fnGetChildCategoryByID](@ItemCategoryId,1) c on c.CategoryID = b.item_category_id "); } pagedSql.AppendFormat(@" where a.CustomerId = @CustomerId and a.IsDelete = 0 "); if (!string.IsNullOrEmpty(itemName)) { pagedSql.AppendFormat(" and b.item_name like @ItemName "); } if (status == 10 || status == 90) { pagedSql.AppendFormat(" and x.Status = @Status"); } pagedSql.AppendFormat(@" Group by a.ItemId, a.createtime) z "); pagedSql.AppendFormat(@" ) x where x.rownum > {0} and x.rownum <= {1} ", pageSize * (pageIndex - 1), pageSize * pageIndex); pagedSql.AppendFormat(@" select distinct a.item_id as ItemId, d.item_name as ItemName, a.sku_id as SkuId, a.prop_1_detail_name as PropName1, a.prop_2_detail_name as PropName2, a.SalesPrice, b.DistributerStock, b.DistributerCostPrice, b.DistributerPrice, b.Status, b.SalesQty from vw_Sku_Detail a inner join T_SuperRetailTraderSkuMapping b on a.Sku_id = b.SkuId and b.CustomerId = @CustomerId and b.IsDelete = 0 inner join vw_item_detail d on d.item_id = a.item_id and d.CustomerId = @CustomerId and d.status = 1 inner join temp x on a.item_id = x.ItemId "); // pagedSql.AppendFormat(" and d.rownum > {0} and d.rownum <= {1}", pageSize * (pageIndex - 1), pageSize * pageIndex); pagedSql.AppendFormat(@" where a.CustomerId = @CustomerId and a.status = 1 "); if (status == 10 || status == 90) { pagedSql.AppendFormat(" and b.Status = @Status"); } if (!string.IsNullOrEmpty(itemName)) { pagedSql.AppendFormat(" and d.item_name like @ItemName"); } pagedSql.AppendFormat(@" drop table Temp "); //计算行数 totalCountSql.AppendFormat(@" select Count(1) from T_SuperRetailTraderItemMapping a inner join vw_item_Detail b on a.ItemId = b.item_id and b.status = 1"); if (!string.IsNullOrEmpty(itemCategoryId)) { totalCountSql.AppendFormat(" inner join [dbo].[fnGetChildCategoryByID](@ItemCategoryId,1) c on c.CategoryID = b.item_category_id "); } totalCountSql.AppendFormat(@" where a.CustomerId = @CustomerId and a.IsDelete = 0 "); if (!string.IsNullOrEmpty(itemName)) { totalCountSql.AppendFormat(" and b.item_name like @ItemName "); } if (status == 10 || status == 90) { totalCountSql.AppendFormat(" and a.Status = @Status"); } SqlParameter[] parameters = { new SqlParameter("@CustomerId", SqlDbType.VarChar), new SqlParameter("@Status", SqlDbType.Int), new SqlParameter(@"ItemName", SqlDbType.NVarChar), new SqlParameter("@ItemCategoryId", SqlDbType.VarChar) }; parameters[0].Value = CurrentUserInfo.ClientID; parameters[1].Value = status; parameters[2].Value = "%" + itemName + "%"; parameters[3].Value = itemCategoryId; PagedQueryResult <T_SuperRetailTraderSkuMappingEntity> result = new PagedQueryResult <T_SuperRetailTraderSkuMappingEntity>(); using (SqlDataReader rdr = SQLHelper.ExecuteReader(CommandType.Text, pagedSql.ToString(), parameters)) { while (rdr.Read()) { T_SuperRetailTraderSkuMappingEntity m = new T_SuperRetailTraderSkuMappingEntity(); if (rdr["ItemId"] != DBNull.Value) { m.ItemId = rdr["ItemId"].ToString(); } if (rdr["ItemName"] != DBNull.Value) { m.ItemName = rdr["ItemName"].ToString(); } if (rdr["SkuId"] != DBNull.Value) { m.SkuId = rdr["SkuId"].ToString(); } if (rdr["PropName1"] != DBNull.Value) { m.PropName1 = rdr["PropName1"].ToString(); } if (rdr["PropName2"] != DBNull.Value) { m.PropName2 = rdr["PropName2"].ToString(); } if (rdr["SalesPrice"] != DBNull.Value) { m.SalesPrice = Convert.ToDecimal(rdr["SalesPrice"]); } if (rdr["DistributerStock"] != DBNull.Value) { m.DistributerStock = Convert.ToInt32(rdr["DistributerStock"]); } if (rdr["DistributerCostPrice"] != DBNull.Value) { m.DistributerCostPrice = Convert.ToDecimal(rdr["DistributerCostPrice"]); } if (rdr["DistributerPrice"] != DBNull.Value) { m.DistributerPrice = Convert.ToDecimal(rdr["DistributerPrice"]); } if (rdr["Status"] != DBNull.Value) { m.Status = Convert.ToInt32(rdr["Status"]); } if (rdr["SalesQty"] != DBNull.Value) { m.SalesQty = Convert.ToInt32(rdr["SalesQty"]); } list.Add(m); } } result.Entities = list.ToArray(); int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(CommandType.Text, totalCountSql.ToString(), parameters)); //计算总行数 result.RowCount = totalCount; int remainder = 0; result.PageCount = Math.DivRem(totalCount, pageSize, out remainder); if (remainder > 0) { result.PageCount++; } return(result); }
/// <summary> /// 获取商品Sku /// </summary> /// <param name="itemName"></param> /// <param name="itemCategoryId"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <returns></returns> public PagedQueryResult <T_SuperRetailTraderSkuMappingEntity> GetSkuList(string itemName, string itemCategoryId, int pageSize, int pageIndex) { List <T_SuperRetailTraderSkuMappingEntity> list = new List <T_SuperRetailTraderSkuMappingEntity>(); StringBuilder pagedSql = new StringBuilder(); StringBuilder totalCountSql = new StringBuilder(); //临时表 pagedSql.AppendFormat(@"Create Table #Temp (ItemId varchar(50))"); pagedSql.AppendFormat(@"Insert into #Temp select ItemId from ( Select distinct dense_rank() over(order by y.createtime desc) as rownum,* from ( select a.item_id as ItemId ,a.create_time as createtime from vw_item_detail a left join vw_sku_detail b on a.item_id = b.item_id and b.CustomerId = @CustomerId and b.status = 1 left join T_SuperRetailTraderSkuMapping d on b.sku_id = d.SkuId and d.CustomerId = @CustomerId and d.IsDelete = 0 "); if (!string.IsNullOrEmpty(itemCategoryId)) { pagedSql.AppendFormat(" inner join [dbo].[fnGetChildCategoryByID](@ItemCategoryId,1) c on c.CategoryID = a.item_category_id "); } pagedSql.AppendFormat(@" where a.CustomerId = @CustomerId and a.status = 1 and d.SkuId is null and a.item_category_id <> '-1' "); if (!string.IsNullOrEmpty(itemName)) { pagedSql.AppendFormat(" and a.item_name like @ItemName "); } pagedSql.AppendFormat(@" group by a.item_id,a.create_time ) y ) x where x.rownum > {0} and x.rownum <= {1} ", pageSize * (pageIndex - 1), pageSize * pageIndex); //记录总条数 totalCountSql.AppendFormat(@" select Count(1) from( select a.item_id from vw_item_detail a "); totalCountSql.AppendFormat(@" left join vw_sku_detail b on a.item_id = b.item_id and b.CustomerId = @CustomerId and b.status = 1 "); totalCountSql.AppendFormat(@" left join T_SuperRetailTraderSkuMapping d on b.sku_id = d.SkuId and d.CustomerId = @CustomerId and d.IsDelete = 0");; if (!string.IsNullOrEmpty(itemCategoryId)) { totalCountSql.AppendFormat(" inner join [dbo].[fnGetChildCategoryByID](@ItemCategoryId,1) c on c.CategoryID = a.item_category_id "); } totalCountSql.AppendFormat(@" where a.CustomerId = @CustomerId and a.status = 1 and d.SkuId is null and a.item_category_id <> '-1' "); if (!string.IsNullOrEmpty(itemName)) { totalCountSql.AppendFormat(" and a.item_name like @ItemName "); } totalCountSql.AppendFormat(@" group by a.item_id ) x"); //查询 pagedSql.AppendFormat(@"select * from ( select distinct a.item_id as ItemId, a.sku_id as SkuId, a.prop_1_detail_name as PropName1, a.prop_2_detail_name as PropName2, a.SalesPrice from vw_Sku_Detail a left join T_SuperRetailTraderSkuMapping b on a.Sku_id = b.SkuId and b.CustomerId = @CustomerId and b.IsDelete = 0 inner join vw_item_detail x on x.item_id = a.item_id and x.CustomerId = @CustomerId and x.status = 1 inner join #temp d on a.item_id = d.ItemId "); if (!string.IsNullOrEmpty(itemName)) { pagedSql.AppendFormat(" and x.item_name like @ItemName "); } if (!string.IsNullOrEmpty(itemCategoryId)) { pagedSql.AppendFormat(" inner join [dbo].[fnGetChildCategoryByID](@ItemCategoryId,1) c on c.CategoryID = x.item_category_id "); } pagedSql.AppendFormat(" where a.CustomerId = @CustomerId and a.status = 1 and b.itemid is null ) y"); pagedSql.AppendFormat(" drop table #temp"); SqlParameter[] parameters = { new SqlParameter("@CustomerId", SqlDbType.VarChar), new SqlParameter("@ItemCategoryId", SqlDbType.VarChar), new SqlParameter("@ItemName", SqlDbType.NVarChar), }; parameters[0].Value = CurrentUserInfo.ClientID; parameters[1].Value = itemCategoryId; parameters[2].Value = "%" + itemName + "%"; PagedQueryResult <T_SuperRetailTraderSkuMappingEntity> result = new PagedQueryResult <T_SuperRetailTraderSkuMappingEntity>(); using (SqlDataReader rdr = SQLHelper.ExecuteReader(CommandType.Text, pagedSql.ToString(), parameters)) { while (rdr.Read()) { T_SuperRetailTraderSkuMappingEntity m = new T_SuperRetailTraderSkuMappingEntity(); if (rdr["ItemId"] != DBNull.Value) { m.ItemId = rdr["ItemId"].ToString(); } if (rdr["SkuId"] != DBNull.Value) { m.SkuId = rdr["SkuId"].ToString(); } if (rdr["PropName1"] != DBNull.Value) { m.PropName1 = rdr["PropName1"].ToString(); } if (rdr["PropName2"] != DBNull.Value) { m.PropName2 = rdr["PropName2"].ToString(); } if (rdr["SalesPrice"] != DBNull.Value) { m.SalesPrice = Convert.ToDecimal(rdr["SalesPrice"]); } list.Add(m); } } result.Entities = list.ToArray(); int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(CommandType.Text, totalCountSql.ToString(), parameters)); //计算总行数 result.RowCount = totalCount; int remainder = 0; result.PageCount = Math.DivRem(totalCount, pageSize, out remainder); if (remainder > 0) { result.PageCount++; } return(result); }
protected override GetSuperRetailTraderListRD ProcessRequest(DTO.Base.APIRequest <GetTSuperRetailTraderConfigRP> pRequest) { var parameter = pRequest.Parameters; var loggingSessionInfo = new SessionManager().CurrentUserLoginInfo; //登录状态信息 var rd = new GetSuperRetailTraderListRD(); T_SuperRetailTraderBLL bll = new T_SuperRetailTraderBLL(loggingSessionInfo); List <IWhereCondition> complexCondition = new List <IWhereCondition>(); complexCondition.Add(new EqualsCondition() { FieldName = "a.Status", Value = "10" }); complexCondition.Add(new EqualsCondition() { FieldName = "a.IsDelete", Value = 0 }); if (!String.IsNullOrEmpty(parameter.SuperRetailTraderFrom)) { complexCondition.Add(new EqualsCondition() { FieldName = "a.SuperRetailTraderFrom", Value = parameter.SuperRetailTraderFrom }); } if (!String.IsNullOrEmpty(parameter.SuperRetailTraderName)) { complexCondition.Add(new LikeCondition() { FieldName = "a.SuperRetailTraderName", Value = "%" + parameter.SuperRetailTraderName + "%" }); } if (!String.IsNullOrEmpty(parameter.JoinSatrtTime)) { complexCondition.Add(new DirectCondition("a.JoinTime>= '" + parameter.JoinSatrtTime + "' ")); } if (!String.IsNullOrEmpty(parameter.JoinEndTime)) { complexCondition.Add(new DirectCondition("a.JoinTime<= '" + parameter.JoinEndTime + "' ")); } if (!String.IsNullOrEmpty(parameter.SuperRetailTraderID.ToString())) { if (parameter.IsFlag == 1) //特殊处理方式 { complexCondition.Add(new DirectCondition("a.JoinTime< '" + DateTime.Now.ToString("yyyy-MM-dd") + "' AND a.JoinTime>='" + DateTime.Now.AddDays(-31).ToString("yyyy-MM-dd") + "'")); } complexCondition.Add(new EqualsCondition() { FieldName = "a.HigheSuperRetailTraderID", Value = parameter.SuperRetailTraderID }); } else { complexCondition.Add(new EqualsCondition() { FieldName = "a.CustomerId", Value = loggingSessionInfo.ClientID }); } PagedQueryResult <T_SuperRetailTraderEntity> models = bll.FindListByCustomerId(complexCondition.ToArray(), null, parameter.PageIndex, parameter.PageSize, loggingSessionInfo.ClientID); rd.SuperRetailTraderList = models.Entities.Select(m => new SuperRetailTraderListInfo() { JoinTime = Convert.ToDateTime(m.JoinTime).ToString("yyyy-MM-dd HH:mm:ss"), NumberOffline = m.NumberOffline, OrderCount = m.OrderCount, SuperRetailTraderFrom = m.SuperRetailTraderFrom, SuperRetailTraderName = m.SuperRetailTraderName, SuperRetailTraderPhone = m.SuperRetailTraderPhone, WithdrawCount = m.WithdrawCount, WithdrawTotalMoney = m.WithdrawTotalMoney, SuperRetailTraderID = m.SuperRetailTraderID }).ToList(); rd.TotalCount = models.RowCount; rd.TotalPages = models.PageCount; return(rd); }
public PagedQueryResult <EstablishmentName> Handle(EstablishmentNamesByKeyword query) { if (query == null) { throw new ArgumentNullException("query"); } switch (query.KeywordMatchStrategy) { case StringMatchStrategy.StartsWith: { var startsWithResults = _entities.Query <EstablishmentName>() .EagerLoad(_entities, query.EagerLoad) .Where(x => x.Text.StartsWith(query.Keyword) || (x.AsciiEquivalent != null && x.AsciiEquivalent.StartsWith(query.Keyword))) .OrderBy(query.OrderBy) ; var pagedResults = new PagedQueryResult <EstablishmentName>(startsWithResults, query.PageSize, query.PageNumber); return(pagedResults); } case StringMatchStrategy.Contains: { var containsResults = _entities.Query <EstablishmentName>() .EagerLoad(_entities, query.EagerLoad) .Where(x => x.Text.Contains(query.Keyword) || (x.AsciiEquivalent != null && x.AsciiEquivalent.Contains(query.Keyword))) .OrderBy(query.OrderBy) ; var pagedResults = new PagedQueryResult <EstablishmentName>(containsResults, query.PageSize, query.PageNumber); return(pagedResults); } case StringMatchStrategy.Equals: { var results = _entities.Query <EstablishmentName>() .EagerLoad(_entities, query.EagerLoad) .Where(x => x.Text.Equals(query.Keyword) || (x.AsciiEquivalent != null && x.AsciiEquivalent.Equals(query.Keyword))) .OrderBy(query.OrderBy) ; var pagedResults = new PagedQueryResult <EstablishmentName>(results, query.PageSize, query.PageNumber); return(pagedResults); } default: { var startsWithResults = _entities.Query <EstablishmentName>() .Where(x => x.Text.StartsWith(query.Keyword) || (x.AsciiEquivalent != null && x.AsciiEquivalent.StartsWith(query.Keyword))) .Select(x => new EntityRanker <EstablishmentName> { Entity = x, Rank = 1, }) ; var containsResults = _entities.Query <EstablishmentName>() .Where(x => !startsWithResults.Select(y => y.Entity.RevisionId).Contains(x.RevisionId)) .Where(x => x.Text.Contains(query.Keyword) || (x.AsciiEquivalent != null && x.AsciiEquivalent.Contains(query.Keyword))) .Select(x => new EntityRanker <EstablishmentName> { Entity = x, Rank = 2, }) ; var rankedResults = startsWithResults.Concat(containsResults).OrderBy(x => x.Rank); if (query.OrderBy.Any()) { foreach (var thenBy in query.OrderBy) { var expression = LinqPropertyChain.Chain <EntityRanker <EstablishmentName>, object, EstablishmentName>(x => x.Entity, thenBy.Key); rankedResults = thenBy.Value == OrderByDirection.Ascending ? rankedResults.ThenBy(expression) : rankedResults.ThenByDescending(expression); } } var orderedResults = rankedResults.Select(x => x.Entity).EagerLoad(_entities, query.EagerLoad); var pagedResults = new PagedQueryResult <EstablishmentName>(orderedResults, query.PageSize, query.PageNumber); return(pagedResults); } } }
/// <summary> /// 获取分销商信息{注意成为分销商之前的信息要默认过滤掉} /// </summary> /// <param name="customerId">商户条件</param> /// <param name="PageIndex">当前页</param> /// <param name="PageSize">页数</param> /// <param name="HigheSuperRetailTraderID">父节点信息</param> /// <returns></returns> public PagedQueryResult <T_SuperRetailTraderEntity> FindListByCustomerId(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int PageIndex, int PageSize, string customerId) { #region 组织SQL StringBuilder pagedSql = new StringBuilder(); StringBuilder totalCountSql = new StringBuilder(); pagedSql.Append(" SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY "); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { if (item != null) { pagedSql.AppendFormat(" {0} {1},", StringUtils.WrapperSQLServerObject(item.FieldName), item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } } pagedSql.Remove(pagedSql.Length - 1, 1); } else { pagedSql.AppendFormat(" a.JoinTime desc"); //默认为主键值倒序 } pagedSql.Append(@") AS RowNumber,a.*, WithdrawCount=(SELECT COUNT(*) FROM VipWithdrawDepositApply WHERE VipId=a.SuperRetailTraderFromId), OrderCount=ISNULL((SELECT COUNT(*) FROM T_inout WHERE sales_user=cast(a.SuperRetailTraderID AS VARCHAR(500))),0), WithdrawTotalMoney=ISNULL((SELECT SUM(Amount) FROM VipWithdrawDepositApply WHERE status=3 AND VipId=cast(a.SuperRetailTraderID AS VARCHAR(500))),0), NumberOffline=ISNULL((SELECT COUNT(*) FROM T_SuperRetailTrader WHERE HigheSuperRetailTraderID=a.SuperRetailTraderID AND Status='10'),0) from T_SuperRetailTrader as a "); SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("@CustomerId", customerId) }; pagedSql.Append(" Where 1=1 "); #region 记录总数sql totalCountSql.Append("select count(1) from T_SuperRetailTrader as a "); totalCountSql.Append("Where 1=1 "); #endregion //过滤条件 if (pWhereConditions != null) { foreach (var item in pWhereConditions) { if (item != null) { pagedSql.AppendFormat(" and {0}", item.GetExpression()); totalCountSql.AppendFormat(" and {0}", item.GetExpression()); } } } pagedSql.AppendFormat(") as h where RowNumber >{0}*({1}-1) AND RowNumber<={0}*{1} ", PageSize, PageIndex); #endregion Loggers.Debug(new DebugLogInfo() { Message = pagedSql.ToString() }); #region 执行,转换实体,分页属性赋值 PagedQueryResult <T_SuperRetailTraderEntity> result = new PagedQueryResult <T_SuperRetailTraderEntity>(); List <T_SuperRetailTraderEntity> list = new List <T_SuperRetailTraderEntity>(); using (SqlDataReader rdr = this.SQLHelper.ExecuteReader(CommandType.Text, pagedSql.ToString(), parameter)) { while (rdr.Read()) { T_SuperRetailTraderEntity m; this.Load(rdr, out m); list.Add(m); if (rdr["OrderCount"] != DBNull.Value) { m.OrderCount = Convert.ToInt32(rdr["OrderCount"] == null ? "0" : rdr["OrderCount"]); } if (rdr["WithdrawCount"] != DBNull.Value) { m.WithdrawCount = Convert.ToInt32(rdr["WithdrawCount"] == null ? "0" : rdr["WithdrawCount"]); } if (rdr["WithdrawTotalMoney"] != DBNull.Value) { m.WithdrawTotalMoney = Convert.ToDecimal(rdr["WithdrawTotalMoney"] == null ? "0" : rdr["WithdrawTotalMoney"]); } if (rdr["NumberOffline"] != DBNull.Value) { m.NumberOffline = Convert.ToInt32(rdr["NumberOffline"] == null ? "0" : rdr["NumberOffline"]); } } } result.Entities = list.ToArray(); int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(totalCountSql.ToString())); //计算总行数 result.RowCount = totalCount; int remainder = 0; result.PageCount = Math.DivRem(totalCount, PageSize, out remainder); if (remainder > 0) { result.PageCount++; } #endregion return(result); }
public IActionResult Index(string username, string password) { PagedQueryResult <Category> category = this.client.ExecuteAsync(new QueryCommand <Category>()).Result; return(this.Content(category.Results.Count.ToString())); }
/// <summary> /// 执行分页查询 /// </summary> /// <param name="pWhereConditions">筛选条件</param> /// <param name="pOrderBys">排序</param> /// <param name="pPageSize">每页的记录数</param> /// <param name="pCurrentPageIndex">以0开始的当前页码</param> /// <returns></returns> public PagedQueryResult <T_RefundOrderEntity> PagedQuery(IWhereCondition[] pWhereConditions, OrderBy[] pOrderBys, int pPageSize, int pCurrentPageIndex) { //组织SQL StringBuilder pagedSql = new StringBuilder(); StringBuilder totalCountSql = new StringBuilder(); string sql = string.Format(@" DECLARE @AllUnit NVARCHAR(200) CREATE TABLE #UnitSET (UnitID NVARCHAR(100)) INSERT #UnitSET (UnitID) SELECT DISTINCT R.UnitID FROM T_User_Role UR CROSS APPLY dbo.FnGetUnitList ('{0}',UR.unit_id,205) R WHERE user_id='{1}' ---根据账户的角色去查角色对应的 所有门店unit_id ", CurrentUserInfo.CurrentLoggingManager.Customer_Id, CurrentUserInfo.UserID); pagedSql.AppendFormat(sql); totalCountSql.AppendFormat(sql); //分页SQL pagedSql.AppendFormat("select * from (select row_number()over( order by "); if (pOrderBys != null && pOrderBys.Length > 0) { foreach (var item in pOrderBys) { if (item != null) { pagedSql.AppendFormat(" {0} {1},", StringUtils.WrapperSQLServerObject(item.FieldName), item.Direction == OrderByDirections.Asc ? "asc" : "desc"); } } pagedSql.Remove(pagedSql.Length - 1, 1); } else { pagedSql.AppendFormat(" [RefundID] desc"); //默认为主键值倒序 } pagedSql.AppendFormat(@") as ___rn,a.*,b.VipName,t.paymentcenter_id,p.Payment_Type_Name,t.order_no from [T_RefundOrder] as a inner join #UnitSET as u on u.UnitId = a.UnitId left join t_inout as t on a.OrderID=t.order_id left join T_Payment_Type as p on t.pay_id=p.Payment_Type_Id and p.IsDelete=0 left join Vip as b on a.VipID=b.VIPID and b.IsDelete=0 where 1=1 and a.isdelete=0 "); //总记录数SQL totalCountSql.AppendFormat(@"select count(1) from [T_RefundOrder] as a inner join #UnitSET as u on u.UnitId = a.UnitId left join t_inout as t on a.OrderID=t.order_id left join T_Payment_Type as p on t.pay_id=p.Payment_Type_Id and p.IsDelete=0 left join Vip as b on a.VipID=b.VIPID and b.IsDelete=0 where 1=1 and a.isdelete=0 "); //过滤条件 if (pWhereConditions != null) { foreach (var item in pWhereConditions) { if (item != null) { pagedSql.AppendFormat(" and {0}", item.GetExpression()); totalCountSql.AppendFormat(" and {0}", item.GetExpression()); } } } pagedSql.AppendFormat(") as A "); //取指定页的数据 pagedSql.AppendFormat(" where ___rn >{0} and ___rn <={1}", pPageSize * (pCurrentPageIndex - 1), pPageSize * (pCurrentPageIndex)); pagedSql.AppendFormat(" DROP Table #UnitSET "); totalCountSql.AppendFormat(" DROP Table #UnitSET "); //执行语句并返回结果 PagedQueryResult <T_RefundOrderEntity> result = new PagedQueryResult <T_RefundOrderEntity>(); List <T_RefundOrderEntity> list = new List <T_RefundOrderEntity>(); using (SqlDataReader rdr = this.SQLHelper.ExecuteReader(pagedSql.ToString())) { while (rdr.Read()) { T_RefundOrderEntity m; this.Load(rdr, out m); if (rdr["Payment_Type_Name"] != DBNull.Value) { m.PayTypeName = Convert.ToString(rdr["Payment_Type_Name"]); } if (rdr["paymentcenter_id"] != DBNull.Value) { m.PayOrderID = Convert.ToString(rdr["paymentcenter_id"]); } if (rdr["VipName"] != DBNull.Value) { m.VipName = Convert.ToString(rdr["VipName"]); } list.Add(m); } } result.Entities = list.ToArray(); int totalCount = Convert.ToInt32(this.SQLHelper.ExecuteScalar(totalCountSql.ToString())); //计算总行数 result.RowCount = totalCount; int remainder = 0; result.PageCount = Math.DivRem(totalCount, pPageSize, out remainder); if (remainder > 0) { result.PageCount++; } return(result); }