/// <summary>
/// 分页查询会员信息
/// </summary>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="memberQueryInfo"></param>
/// <returns></returns>
public async Task <VMResult <PageMemberInfo> > PageGetMemberInfo(int currentPage, int pageSize,
MemberQueryInfo memberQueryInfo)
{
VMResult <PageMemberInfo> r = new VMResult <PageMemberInfo>();
try
{
using (var conn = DapperHelper.CreateConnection())
{
StringBuilder strSql = new StringBuilder();
StringBuilder strPageSql = new StringBuilder();
strSql.Append(@"SELECT * FROM Member m where 1=1");
if (!string.IsNullOrWhiteSpace(memberQueryInfo.UserName))
{
strSql.Append(" and m.Name like'%" + memberQueryInfo.UserName + "%'");
}
if (!string.IsNullOrWhiteSpace(memberQueryInfo.CardNumber))
{
strSql.Append(" and m.CardNum = '" + memberQueryInfo.CardNumber + "'");
}
if (memberQueryInfo.PayTypeID != 0)
{
strSql.Append(" and m.PayType = '" + memberQueryInfo.PayTypeID + "'");
}
if (memberQueryInfo.Birthday != null)
{
strSql.Append(" and m.Birthday='" + memberQueryInfo.Birthday + "'");
}
//总条数
int TotalCount = (await conn.QueryAsync <MemberInfo>(strSql.ToString())).Count();
//开始条数
int startNumber = (currentPage - 1) * pageSize;
strPageSql.Append(@"SELECT DATA.* FROM (" + strSql + ") DATA ORDER BY 1 OFFSET " + startNumber +
"ROWS FETCH NEXT " + pageSize + "ROWS ONLY");
var value = await conn.QueryAsync <MemberInfo>(strPageSql.ToString());
if (value != null)
{
r.Data.MemberInfos = value;
r.Data.total = TotalCount;
r.ResultCode = 0;
}
else
{
r.ResultMsg = "分页查询失败";
}
}
}
catch (Exception ex)
{
r.ResultMsg = ex.Message;
}
return(r);
}
public async Task <string> PageGetMemberInfo(string info)
{
PageMemberQueryInfo pageMemberQueryInfo = new PageMemberQueryInfo();
MemberQueryInfo memberQueryInfo = new MemberQueryInfo();
if (!string.IsNullOrWhiteSpace(info))
{
pageMemberQueryInfo = JsonConvert.DeserializeObject <PageMemberQueryInfo>(info);
}
if (pageMemberQueryInfo.memberQueryInfo != null)
{
memberQueryInfo = pageMemberQueryInfo.memberQueryInfo;
}
MemberDal dal = new MemberDal();
var result = await dal.PageGetMemberInfo(pageMemberQueryInfo.CurrentPage, pageMemberQueryInfo.PageSize,
memberQueryInfo);
if (result.ResultCode != 0)
{
return(JsonConvert.SerializeObject(new
{
statue = "fail",
message = result.ResultMsg,
date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}));
}
return(JsonConvert.SerializeObject(new
{
status = "success",
data = result.Data,
date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}));
}
/// <summary>
/// 根据查询条件查询
/// </summary>
/// <param name="memberQueryInfo"></param>
/// <returns></returns>
public async Task <VMResult <PageMemberInfo> > GetMemberInfo(MemberQueryInfo memberQueryInfo)
{
VMResult <PageMemberInfo> r = new VMResult <PageMemberInfo>();
try
{
using (var conn = DapperHelper.CreateConnection())
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT * FROM Member m where 1=1");
if (!string.IsNullOrWhiteSpace(memberQueryInfo.UserName))
{
strSql.Append(" and m.Name like'%" + memberQueryInfo.UserName + "%'");
}
if (!string.IsNullOrWhiteSpace(memberQueryInfo.CardNumber))
{
strSql.Append(" and m.CardNum = '" + memberQueryInfo.CardNumber + "'");
}
if (memberQueryInfo.PayTypeID != 0)
{
strSql.Append(" and m.PayType = '" + memberQueryInfo.PayTypeID + "'");
}
if (memberQueryInfo.Birthday != null)
{
strSql.Append(" and m.Birthday='" + memberQueryInfo.Birthday + "'");
}
var value = await conn.QueryAsync <MemberInfo>(strSql.ToString());
if (value != null)
{
r.Data.MemberInfos = value;
r.Data.total = value.ToList().Count;;
r.ResultCode = 0;
}
else
{
r.ResultMsg = "查询用户信息为空!";
}
}
}
catch (Exception ex)
{
r.ResultMsg = ex.Message;
}
return(r);
}
