/// <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);
}
