示例#1
0
        /// <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));
        }
示例#2
0
        /// <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);
            }
        }