using System;
using System.Collections.Generic;
using System.Text;
using Mall.Model.Entity.User;
using Mall.Model.Extend.User;
using System.Linq;
using Mall.Common.Enum.User;

namespace Mall.Repository.User
{
    /// <summary>
    /// 商户用户仓储层
    /// </summary>
    public class RB_Member_UserRepository : RepositoryBase<RB_Member_User>
    {

        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="dmodel">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetPageList(int pageIndex, int pageSize, out long rowCount, RB_Member_User_Extend dmodel)
        {
            string where = " 1=1 ";
            if (dmodel.TenantId > 0) {
                where += $@" and {nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0) {
                where += $@" and {nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0) {
                where += $@" and {nameof(RB_Member_User.Id)}={dmodel.Id}";
            }            
            if (!string.IsNullOrEmpty(dmodel.Name)) {
                where += $@" and {nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName)) {
                where += $@" and {nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0) {
                where += $@" and {nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie)) {
                where += $@" and {nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0) {
                where += $@" and {nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0) {
                where += $@" and {nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (dmodel.IsDistributor > 0) {
                where += $@" and {nameof(RB_Member_User.IsDistributor)}={dmodel.IsDistributor}";
            }
            if (dmodel.IsEmptyUserPhoto == 1) {
                where += $@" and IFNULL({nameof(RB_Member_User.Photo)},'')=''";
            }
            //HK06-01新增查询条件
            if (dmodel.OpenId != null && !string.IsNullOrWhiteSpace(dmodel.OpenId))
            {
                where += $@" and {nameof(RB_Member_User.OpenId)} = '%{dmodel.OpenId}%'";
            }
            if (dmodel.IsSelectCommpany == 1) {
                where += $@" and {nameof(RB_Member_User.Id)} between 5 and 100 ";
            }
            string orderBy = "CreateDate desc";
            if (dmodel.MinUserId > 0)
            {
                where += $@" and {nameof(RB_Member_User.Id)}>{dmodel.MinUserId}";
                orderBy = "Id asc";
            }

            string sql = $@"
select * from RB_Member_User where {where} order by {orderBy}
";
            return GetPage<RB_Member_User_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetList(RB_Member_User_Extend dmodel)
        {
            string where = " 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and {nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and {nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and {nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.UserNameStr)) 
            {
                where += $@" and {nameof(RB_Member_User.Name)} in({dmodel.UserNameStr})";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and {nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and {nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and {nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and {nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and {nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (!string.IsNullOrEmpty(dmodel.UserIds)) {
                where += $@" and {nameof(RB_Member_User.Id)} in({dmodel.UserIds})";
            }

            string sql = $@"
select * from RB_Member_User where {where} order by CreateDate desc
";
            return Get<RB_Member_User_Extend>(sql).ToList();
        }


        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="dmodel">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetPageListByKey(int pageIndex, int pageSize, out long rowCount, RB_Member_User_Extend dmodel)
        {
            string where = " 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and {nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and {nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and {nameof(RB_Member_User.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and {nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and ({nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'  or {nameof(RB_Member_User.Id)} like '%{dmodel.Id}%')";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and {nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and {nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and {nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and {nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            string sql = $@"
select * from RB_Member_User where {where} order by CreateDate desc
";
            return GetPage<RB_Member_User_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetListByPhoneOrAliasName(RB_Member_User_Extend dmodel)
        {
            string where = " 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and {nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and {nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
          
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and ({nameof(RB_Member_User.Name)} like '%{dmodel.AliasName}%' or {nameof(RB_Member_User.Moblie)} like '%{dmodel.AliasName}%' or {nameof(RB_Member_User.Id)} like '%{dmodel.AliasName}%')";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and {nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and ";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and {nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and {nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (!string.IsNullOrEmpty(dmodel.UserIds))
            {
                where += $@" and {nameof(RB_Member_User.Id)} in({dmodel.UserIds})";
            }

            string sql = $@"
select * from RB_Member_User where {where} order by CreateDate desc
";
            return Get<RB_Member_User_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取下拉列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetMemberUserDropDownList(int pageIndex, int pageSize, out long count, RB_Member_User_Extend dmodel)
        {
            string where = " 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and {nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and {nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and {nameof(RB_Member_User.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and {nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and {nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and {nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and {nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and {nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and {nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (dmodel.IsDistributor > 0) 
            {
                where += $@" and {nameof(RB_Member_User.IsDistributor)}={dmodel.IsDistributor}";
            }
            string sql = $@"select Id,Name,AliasName from RB_Member_User where {where} order by CreateDate desc";
            return GetPage<RB_Member_User_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 需更新该等级下的用户等级
        /// </summary>
        /// <param name="GradeId"></param>
        /// <param name="DefaultGId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        public bool UpdateMemberGrade(int GradeId, int DefaultGId, int tenantId, int mallBaseId)
        {
            string sql = $@" update RB_Member_User set MemberGrade={DefaultGId} where MemberGrade={GradeId} and TenantId={tenantId} and MallBaseId={mallBaseId}";
            return Execute(sql) > 0;
        }

        /// <summary>
        /// 获取下线数量
        /// </summary>
        /// <param name="uids"></param>
        /// <param name="tier"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetDistributorReferralsNum(string uids, int tier, int tenantId, int mallBaseId)
        {
            if (tier == 1)
            {
                string sql = $@"SELECT u.SuperiorId,COUNT(0) AS ReferralsNum FROM rb_member_user u 
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId in ({uids}) GROUP BY u.SuperiorId";
                return Get<RB_Member_User_Extend>(sql).ToList();
            }
            else if (tier == 2)
            {
                string sql = $@"SELECT u.SuperiorId,COUNT(0) AS ReferralsNum FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId in ({uids}) GROUP BY u.SuperiorId";
                return Get<RB_Member_User_Extend>(sql).ToList();
            }
            else if (tier == 3) {
                string sql = $@"SELECT u.SuperiorId,COUNT(0) AS ReferralsNum FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
INNER JOIN rb_member_user u2 on u1.Id=u2.SuperiorId
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId in ({uids}) GROUP BY u.SuperiorId";
                return Get<RB_Member_User_Extend>(sql).ToList();
            }
            return new List<RB_Member_User_Extend>();
        }

        /// <summary>
        /// 获取下线分销商数量
        /// </summary>
        /// <param name="uids"></param>
        /// <param name="tier"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public int GetDistributorReferralsNumTwo(int uid, int tier, int tenantId, int mallBaseId)
        {
            if (tier == 1)
            {
                string sql = $@"SELECT COUNT(0) AS ReferralsNum FROM rb_member_user u 
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId ={uid} and u.IsDistributor=1";
                var obj = ExecuteScalar(sql);
                if (obj != null) { return Convert.ToInt32(obj); } else { return 0; }
            }
            else if (tier == 2)
            {
                string sql = $@"SELECT COUNT(0) AS ReferralsNum FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId ={uid} and u1.IsDistributor=1";
                var obj = ExecuteScalar(sql);
                if (obj != null) { return Convert.ToInt32(obj); } else { return 0; }
            }
            else if (tier == 3)
            {
                string sql = $@"SELECT COUNT(0) AS ReferralsNum FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
INNER JOIN rb_member_user u2 on u1.Id=u2.SuperiorId
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId ={uid} and u2.IsDistributor=1";
                var obj = ExecuteScalar(sql);
                if (obj != null) { return Convert.ToInt32(obj); } else { return 0; }
            }
            return 0;
        }

        /// <summary>
        /// 获取七天后自动释放
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> AutoReleaseTheOffline(int tenantId, int mallBaseId)
        {
            string sql = $@"SELECT * FROM rb_member_user WHERE DownlineCondition=4 and IsDistributor = 0 and SuperiorId >0 AND DATE_ADD(CreateDate,INTERVAL 7 DAY) < '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'";
            return Get<RB_Member_User_Extend>(sql).ToList();
        }


        /// <summary>
        /// 获取下线分销商分页列表
        /// </summary>
        /// <param name="uids"></param>
        /// <param name="tier"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetDistributorReferralsPageList(int pageIndex,int pageSize,out long count, RB_Distributor_Info_Extend demodel)
        {
            string sql = "";
            if (demodel.GradeId == 1)
            {
                sql = $@"SELECT u.* FROM rb_member_user u 
WHERE u.TenantId={demodel.TenantId} and u.MallBaseId={demodel.MallBaseId} and u.SuperiorId ={demodel.UserId} and u.IsDistributor=1";
            }
            else if (demodel.GradeId == 2)
            {
                sql = $@"SELECT u1.* FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
WHERE u.TenantId={demodel.TenantId} and u.MallBaseId={demodel.MallBaseId} and u.SuperiorId ={demodel.UserId} and u1.IsDistributor=1";
            }
            else if (demodel.GradeId == 3)
            {
                sql = $@"SELECT u2.* FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
INNER JOIN rb_member_user u2 on u1.Id=u2.SuperiorId
WHERE u.TenantId={demodel.TenantId} and u.MallBaseId={demodel.MallBaseId} and u.SuperiorId ={demodel.UserId} and u2.IsDistributor=1";
            }
            return GetPage<RB_Member_User_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取下级用户列表
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="referralsType"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetDistributorReferralsPageList(int? userId, int? referralsType, int tenantId, int mallBaseId)
        {
            if (referralsType == 1)
            {
                string sql = $@"SELECT u.* FROM rb_member_user u 
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId ={userId} order by u.CreateDate asc";
                return Get<RB_Member_User_Extend>(sql).ToList();
            }
            else if (referralsType == 2)
            {
                string sql = $@"SELECT u1.* FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId ={userId} order by u1.CreateDate asc";
                return Get<RB_Member_User_Extend>(sql).ToList();
            }
            else if (referralsType == 3)
            {
                string sql = $@"SELECT u2.* FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
INNER JOIN rb_member_user u2 on u1.Id=u2.SuperiorId
WHERE u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.SuperiorId ={userId} order by u2.CreateDate asc";
                return Get<RB_Member_User_Extend>(sql).ToList();
            }
            return new List<RB_Member_User_Extend>();
        }

        /// <summary>
        /// 根据查询条件获取用户实体类
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public RB_Member_User_Extend GetMemberUserEntityRepository(RB_Member_User_Extend query)
        {
            string where = " 1=1 ";
            if (query.TenantId > 0)
            {
                where += $@" and {nameof(RB_Member_User.TenantId)}={query.TenantId}";
            }
            if (query.MallBaseId > 0)
            {
                where += $@" and {nameof(RB_Member_User.MallBaseId)}={query.MallBaseId}";
            }
            if (query.Id > 0)
            {
                where += $@" and {nameof(RB_Member_User.Id)}={query.Id}";
            }
            if (!string.IsNullOrEmpty(query.Name))
            {
                where += $@" and {nameof(RB_Member_User.Name)} like '%{query.Name}%'";
            }
            if (!string.IsNullOrEmpty(query.AliasName))
            {
                where += $@" and {nameof(RB_Member_User.AliasName)} like '%{query.AliasName}%'";
            }
            if (query.Source > 0)
            {
                where += $@" and {nameof(RB_Member_User.Source)} ={(int)query.Source}";
            }
            if (!string.IsNullOrEmpty(query.Moblie))
            {
                where += $@" and {nameof(RB_Member_User.Moblie)} like '%{query.Moblie}%'";
            }
            if (query.MemberGrade > 0)
            {
                where += $@" and {nameof(RB_Member_User.MemberGrade)}={query.MemberGrade}";
            }
            if (query.SuperiorId > 0)
            {
                where += $@" and {nameof(RB_Member_User.SuperiorId)}={query.SuperiorId}";
            }
            if (query.IsEmptyUserPhoto == 1)
            {
                where += $@" and IFNULL({nameof(RB_Member_User.Photo)},'')=''";
            }
            //HK06-01新增查询条件
            if (query.OpenId != null && !string.IsNullOrWhiteSpace(query.OpenId))
            {
                where += $@" and {nameof(RB_Member_User.OpenId)} LIKE '{query.OpenId}%'";
            }
            string sql = $@"select * from RB_Member_User where {where} ";
            return Get<RB_Member_User_Extend>(sql).FirstOrDefault();
        }



        #region 数据统计-分销排行
        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="dmodel">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetRankingListPage(int pageIndex, int pageSize, out long rowCount, RB_Member_User_Extend dmodel)
        {
            string where = " where 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and (a.{nameof(RB_Member_User.Name)} like '%{dmodel.Name}%' or a.{nameof(RB_Member_User.Id)} like '%{dmodel.Name}%')";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and a.{nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and a.{nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (string.IsNullOrWhiteSpace(dmodel.OrderBy))
            {
                dmodel.OrderBy = " Id asc";
            }
            string sql = $@"  select * from ( SELECT a.Id,a.Source,a.`Name`,a.AliasName,a.Photo, b.TotalCommission,b.Price,c.Commission,d.ChildrenNum,e.twoChildren,f.ThreeChildren,g.AllOrderNum,
(IFNULL(d.ChildrenNum,0)+IFNULL(e.twoChildren,0)+IFNULL(f.ThreeChildren,0)) as AllChildrenNum 
from rb_member_user as a 
LEFT JOIN (SELECT UserId,MAX(TotalCommission) as TotalCommission,SUM(IFNULL(TotalCommission,0)-IFNULL(CommissionWithdrawal,0)) as Price from rb_distributor_info  
GROUP BY UserId) as b on a.Id=b.UserId
LEFT JOIN 
(SELECT goc.UserId,SUM(Commission) as Commission from rb_goods_ordercommission as goc
 LEFT JOIN rb_goods_order go  on goc.OrderId=go.OrderId where go.OrderStatus!=7 GROUP BY goc.UserId) as c on a.Id=c.UserId
LEFT JOIN (SELECT SuperiorId,COUNT(SuperiorId)   as ChildrenNum  from rb_member_user GROUP BY SuperiorId) as d on d.SuperiorId=a.Id
LEFT JOIN (SELECT COUNT(u1.Id) as twoChildren,u.SuperiorId FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId  GROUP BY u.SuperiorId ) as e on e.SuperiorId=a.Id
LEFT JOIN (SELECT COUNT(u2.Id) as ThreeChildren,u.SuperiorId FROM rb_member_user u 
INNER JOIN rb_member_user u1 on u.Id=u1.SuperiorId
INNER JOIN rb_member_user u2 on u1.Id=u2.SuperiorId GROUP BY u.SuperiorId ) as f on f.SuperiorId=a.Id
LEFT JOIN (SELECT UserId,COUNT(OrderId) as AllOrderNum from rb_goods_ordercommission GROUP BY UserId) as g on g.UserId=a.Id   {where} ) as t
order by   {dmodel.OrderBy} ";
            return GetPage<RB_Member_User_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }


        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="dmodel">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetRankingList( RB_Member_User_Extend dmodel)
        {
            string where = " 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and {nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and {nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and {nameof(RB_Member_User.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and {nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and {nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and {nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and {nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and {nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and {nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (string.IsNullOrWhiteSpace(dmodel.OrderBy))
            {
                dmodel.OrderBy = " Id asc";
            }
            string sql = $@" select * from ( SELECT a.Id,a.Source,a.`Name`,a.AliasName,a.Photo, b.TotalCommission,b.Price,c.Commission,d.ChildrenNum,
(SELECT COUNT(*) from rb_member_user where FIND_IN_SET( Id ,(SELECT  GetMemberChildrenList(a.Id))))  as AllChildrenNum ,
(SELECT COUNT(*) from rb_goods_order where OrderStatus!=7 and  FIND_IN_SET( UserId ,(SELECT  GetMemberChildrenList(a.Id))))  as AllOrderNum 
from rb_member_user as a 
LEFT JOIN (SELECT UserId,MAX(TotalCommission) as TotalCommission,SUM(IFNULL(TotalCommission,0)-IFNULL(CommissionWithdrawal,0)) as Price from rb_distributor_info  GROUP BY UserId) as b on a.Id=b.UserId
LEFT JOIN 
(SELECT goc.UserId,SUM(Commission) as Commission from rb_goods_ordercommission as goc
 LEFT JOIN rb_goods_order go  on goc.OrderId=go.OrderId where go.OrderStatus!=7 GROUP BY goc.UserId) as c on a.Id=c.UserId
LEFT JOIN (SELECT SuperiorId,COUNT(SuperiorId) as ChildrenNum from rb_member_user GROUP BY SuperiorId) as d on d.SuperiorId=a.Id) as t order by {dmodel.OrderBy} ";
            return Get<RB_Member_User_Extend>(sql).ToList();
        }

        #endregion
    }
}
