public string GetPageMember(dynamic requestData)
{
string query = JsonConvert.SerializeObject(requestData);
RequestMemberData model = JsonConvert.DeserializeObject <RequestMemberData>(query);
int beginPage = (model.IndexPage - 1) * model.PageSize + 1;
int endPage = model.IndexPage * model.PageSize;
var nestSql = SqlParamHelper.Builder
.Append("select rm.*, ISNULL(mid.MemberActiveState,2) AS MemberActiveState, ROW_NUMBER() over(order by rm.MemberId desc) as num from RegistMember as rm")
.Append("LEFT JOIN (SELECT m.MemberId,CASE WHEN DATEDIFF(DAY,MAX(m.CreatDate),GETDATE()) < 20 THEN 1 ELSE 2 END AS MemberActiveState FROM MemberIntegralDetail AS m GROUP BY m.MemberId) AS mid ON mid.MemberId = rm.MemberId");
if (model.SalemanId > 0)
{
nestSql.Append(" inner join RegistCode as rc on rc.MemberId = rm.MemberId");
nestSql.Where(" rc.SalemanId = " + model.SalemanId + "");
}
if (model.Identity == 1)
{
nestSql.Where(" rm.RecommendId = 0");
}
else if (model.Identity == 2)
{
nestSql.Where(" rm.RecommendId > 0");
}
if (model.CodeState == 1)
{
nestSql.Where(" rm.MemberCode is not null");
}
else if (model.CodeState == 0)
{
nestSql.Where(" rm.MemberCode is null or rm.MemberCode = ''");
}
if (!string.IsNullOrEmpty(model.SearchInfo))
{
nestSql.Where(" rm.HotelName like '%" + model.SearchInfo + "%' or rm.MemberName like '%" + model.SearchInfo + "%' or rm.MemberTelePhone like '%" + model.SearchInfo + "%'");
}
if (!string.IsNullOrEmpty(model.RegistBeginTime.ToString()) && !string.IsNullOrEmpty(model.RegistEndTime.ToString()))
{
nestSql.Where(" rm.RegistDate >= '" + model.RegistBeginTime + "' and rm.RegistDate <= '" + model.RegistEndTime + "'");
}
nestSql.Where(" rm.IsEnable = 0");
var sql = SqlParamHelper.Builder
.Append("select * from (" + nestSql.SQL + ") as member")
.Append("where member.num >= " + beginPage + " and member.num <= " + endPage + "");
var q = dataContext.ExecuteDataTable(CommandType.Text, sql.SQL, sql.Arguments);
#region 计算总页数
var countSql = string.Format("select Count(*) from(" + nestSql.SQL + ")as memberCount");
var count = dataContext.ExecuteScalar(CommandType.Text, countSql);
//int row = Convert.ToInt32(count);
//int pageCount = model.PageSize;
//int sum = (row - 1) / pageCount + 1;//这样就计算好了页码数量，逢1进1
#endregion
return("{ \"Count\":\"" + count + "\",\"data\":" + JsonConvert.SerializeObject(q) + "}");
}
public string GetMemberListBySaleman(dynamic requestData)
{
string query = JsonConvert.SerializeObject(requestData);
RequestMemberData model = JsonConvert.DeserializeObject <RequestMemberData>(query);
int beginPage = (model.IndexPage - 1) * (model.PageSize + 1);
int endPage = model.IndexPage * model.PageSize;
if (model.SalemanId == 0)
{
return("request parameter error");
}
var nestSql = SqlParamHelper.Builder
.Append("select *, ROW_NUMBER() over(order by MemberId desc) as num from RegistMember as rm");
if (model.Identity == 1)
{
nestSql.Where(" rm.MemberId not in (select MemberId from RegistCode)");
}
else if (model.Identity == 2)
{
nestSql.Where(" rm.MemberId in (select MemberId from RegistCode)");
}
if (!string.IsNullOrEmpty(model.SearchInfo))
{
nestSql.Where(" rm.HotelName like '%" + model.SearchInfo + "%' or rs.Name like '%" + model.SearchInfo + "%' or rs.Telephone like '%" + model.SearchInfo + "%'");
}
if (!string.IsNullOrEmpty(model.RegistBeginTime.ToString()) && !string.IsNullOrEmpty(model.RegistEndTime.ToString()))
{
nestSql.Where(" rm.RegistDate >= '" + model.RegistBeginTime + "' and rm.RegistDate <= '" + model.RegistEndTime + "'");
}
nestSql.Where(" rm.IsEnable = 0");
var sql = SqlParamHelper.Builder
.Append("select * from (" + nestSql.SQL + ") as memeber")
.Append("where memeber.num >= " + beginPage + " and memeber.num <= " + endPage + "");
var result = dataContext.ExecuteDataTable(CommandType.Text, sql.SQL);
return(JsonConvert.SerializeObject(result));
}