/// <summary>
/// 获取代理列表-分页
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public ActionResult GetAgentPage(string pagination, string sdate, string edate, string keyword, int id)
{
Pagination paginationobj = pagination.ToObject <Pagination>();
AgentDatas model = new AgentDatas();
model.pageIndex = paginationobj.page;
model.pageSize = paginationobj.rows;
model.totalCount = paginationobj.total;
model.startDate = sdate;
model.endDate = edate;
model.nickName = keyword;
model.higherAgent = id;
model.orderBy = paginationobj.sidx + " " + paginationobj.sord;
List <AgentDatas> list = this._agent.GetAgentPage(model);
var jsonData = new
{
rows = list,
total = list.Count > 0 ? list[0].total : 0,
page = paginationobj.page,
records = list.Count > 0 ? list[0].totalCount : 0
};
return(Success(jsonData));
}
/// <summary>
/// 查询代理用户
/// </summary>
/// <returns></returns>
public List <AgentDatas> GetAgentPage(AgentDatas model)
{
using (IDbConnection conn = DapperAdapter.MySQLOpenConnection(ConfigurationHelper.MySQLConnectionStr))
{
DynamicParameters param = new DynamicParameters();
StringBuilder sb = new StringBuilder();
//string column = "SELECT ar.*, u.nickName,inviteU.nickName as inviteUserName ,higherU.nickName as higherUName ,u.phoneNum,u.diamond,u.loginTime ";
string column = @"SELECT ar.*,u.wxId,u.nickName,inviteU.nickName as inviteUserName ,higherU.nickName as higherUName ,u.phoneNum,u.diamond,u.loginTime ,
((SELECT SUM(changedDiamond) from t_diamond_log WHERE updateTime >CONCAT(CURDATE(),' 00:00:00') and userid=ar.userid and changedType=4) -(SELECT SUM(changedDiamond) from t_diamond_log WHERE updateTime >CONCAT(CURDATE(),' 00:00:00') and userid=ar.userid and changedType=5)) as TodayClubCos,
((SELECT SUM(changedDiamond) from t_diamond_log WHERE updateTime >CONCAT(CURDATE(),' 00:00:00') and userid=ar.userid and changedType=1) -(SELECT SUM(changedDiamond) from t_diamond_log WHERE updateTime >CONCAT(CURDATE(),' 00:00:00') and userid=ar.userid and changedType=3)) as TodayCos,
(SELECT COUNT(*) from t_user WHERE registerTime >CONCAT(CURDATE(),' 00:00:00') and inviteCode =ar.userid) AS TodayAddUser,
(SELECT SUM(changedDiamond) from t_diamond_log where userid=ar.userid and changedType =10 and updateTime > CONCAT(CURDATE(),' 00:00:00')) as TodayUserDiamond,
(SELECT SUM(changedDiamond) from t_diamond_log where userid=ar.userid and changedType =10) as UserDiamond ,
(SELECT COUNT(*) from t_agent_relation where higherAgent=ar.userid) LowerUserCount";
sb.Append(column);
sb.Append(@"
from T_Agent_Relation ar
LEFT JOIN t_user u on u.userid = ar.userid
LEFT JOIN t_user higherU on higherU.userid = ar.higherAgent
LEFT JOIN t_user inviteU on inviteU.userid = ar.inviteUserId
where 1=1 ");
if (model.higherAgent > 0)
{
sb.Append("and higherAgent = @higherAgent");
param.Add("@higherAgent", model.higherAgent);
}
if (!string.IsNullOrEmpty(model.startDate))
{
sb.Append(" and createTime>=@startDate ");
param.Add("@startDate", model.startDate);
}
if (!string.IsNullOrEmpty(model.endDate))
{
sb.Append(" and createTime<=@endDate ");
param.Add("@endDate", model.endDate);
}
if (!string.IsNullOrEmpty(model.nickName))
{
sb.Append(" and u.nickName like @nickName ");
param.Add("@nickName", "%" + model.nickName + "%");
}
//获取总记录数
string sqlCount = sb.ToString().Replace(column, "select count(1) totalCount ");
if (!string.IsNullOrWhiteSpace(model.orderBy))
{
sb.Append(" order by " + model.orderBy);
}
else
{
sb.Append(" order by id desc");
}
//分页
if (model.pageIndex >= 0 && model.pageSize > 0)
{
sb.Append(" limit " + ((model.pageIndex - 1) * model.pageSize) + "," + model.pageSize);
}
//分页记录列表
var list = conn.Query <AgentDatas>(sb.ToString(), param).ToList();
if (list != null && list.Count() > 0)
{
//总记录数列表
dynamic identity = conn.Query(sqlCount, param).Single();
list[0].totalCount = Convert.ToInt64(identity.totalCount);
list[0].pageIndex = model.pageIndex;
list[0].pageSize = model.pageSize;
}
return(list);
}
}