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;
using VT.FW.DB.Dapper;

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

        #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> 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 (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and {nameof(RB_Member_User.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and {nameof(RB_Member_User.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }
            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})";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and {nameof(RB_Member_User.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and {nameof(RB_Member_User.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }

            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="dmodel"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetCompanyUserList(RB_Member_User_Extend dmodel)
        {
            string where = " 1=1 and ID<20 ";
            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})";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and {nameof(RB_Member_User.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and {nameof(RB_Member_User.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }

            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="dmodel"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetPageListForFX(int pageIndex, int pageSize, out long rowCount, RB_Member_User_Extend dmodel)
        {
            string where = " and d.Status=0 ";
            string where2 = "";
            if (dmodel.TenantId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
                where2 += $@" and u.{nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
                where2 += $@" and u.{nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.UserNameStr))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} in({dmodel.UserNameStr})";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and u.{nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and u.{nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (dmodel.IsDirect == 1)
            {
                //查找直属下级
                where += $@" and u.{nameof(RB_Member_User.SuperiorId)}={dmodel.Id}";
            }
            else
            {
                if (!string.IsNullOrEmpty(dmodel.UserIds))
                {
                    where += $@" and u.{nameof(RB_Member_User.Id)} in({dmodel.UserIds})";
                }
            }

            if (dmodel.FXGradeId > 0)
            {
                where += $@" and d.{nameof(RB_Distributor_Info.FXGradeId)}={dmodel.FXGradeId}";
            }

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT u.*,IFNULL(C.Income,0) AS Income,IFNULL(C.OrderNum,0) AS RealOrderNum
FROM RB_Member_User u INNER JOIN rb_distributor_info d on u.Id =d.UserId
     LEFT JOIN (
                   SELECT u.SuperiorId,COUNT(0) AS ReferralsNum 
                   FROM rb_member_user u 
                   WHERE 1=1 {1}  
                   GROUP BY u.SuperiorId
               )  AS B ON U.Id=B.SuperiorId
    LEFT JOIN (
				SELECT UserId,SUM(Income) as Income,count(0) as OrderNum FROM rb_goods_order 
				WHERE OrderStatus in (2,3,4,5,6) and Recycled=2  GROUP BY UserId
              ) AS C ON U.Id=C.UserId
WHERE 1=1 {0} 
", where, where2);
            string orderBy = " order by u.CreateDate desc ";
            if (dmodel.SortNum == 1)
            {
                orderBy = " order by u.CreateDate ASC ";
            }
            else if (dmodel.SortNum == 2)
            {
                orderBy = " order by B.ReferralsNum DESC ";
            }
            else if (dmodel.SortNum == 3)
            {
                orderBy = " order by B.ReferralsNum ASC ";
            }
            else if (dmodel.SortNum == 4)
            {
                orderBy = " order by IFNULL(C.Income,0) ASC ";
            }
            else if (dmodel.SortNum == 5)
            {
                orderBy = " order by IFNULL(C.Income,0) DESC ";
            }
            builder.AppendFormat(orderBy);

            return GetPage<RB_Member_User_Extend>(pageIndex, pageSize, out rowCount, builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetListExtRepository(RB_Member_User_Extend dmodel)
        {
            string where = " and d.Status=0 ";

            if (dmodel.TenantId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
                where += $@" and B.{nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";

            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
                where += $@" and B.{nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.UserNameStr))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} in({dmodel.UserNameStr})";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and u.{nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and u.{nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (!string.IsNullOrEmpty(dmodel.UserIds))
            {
                where += $@" and u.{nameof(RB_Member_User.Id)} in({dmodel.UserIds})";
            }
            if (dmodel.FXGradeId > 0)
            {
                where += $@" and d.{nameof(RB_Distributor_Info.FXGradeId)}={dmodel.FXGradeId}";
            }

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT u.*,B.Icon
FROM RB_Member_User u INNER JOIN rb_distributor_info d on u.Id =d.UserId
     LEFT JOIN rb_distributor_fxgrade AS B ON d.FXGradeId=B.Grade AND B.`Status`=0
WHERE 1=1 {0} 
", where);
            string orderBy = " order by u.CreateDate desc ";
            if (dmodel.SortNum == 1)
            {
                orderBy = " order by u.CreateDate ASC ";
            }
            else if (dmodel.SortNum == 2)
            {
                orderBy = " order by B.ReferralsNum DESC ";
            }
            else if (dmodel.SortNum == 3)
            {
                orderBy = " order by B.ReferralsNum ASC ";
            }
            builder.AppendFormat(orderBy);

            return Get<RB_Member_User_Extend>(builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取下线数量（粉象）
        /// </summary>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public int GetUserReferralsForFX(RB_Member_User_Extend dmodel)
        {
            string where = " 1=1 and d.Status=0 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.UserNameStr))
            {
                where += $@" and u.{nameof(RB_Member_User.Name)} in({dmodel.UserNameStr})";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and u.{nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and u.{nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and u.{nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (!string.IsNullOrEmpty(dmodel.UserIds))
            {
                where += $@" and u.{nameof(RB_Member_User.Id)} in({dmodel.UserIds})";
            }
            if (dmodel.FXGradeId > 0)
            {
                where += $@" and d.{nameof(RB_Distributor_Info.FXGradeId)}={dmodel.FXGradeId}";
            }
            if (!string.IsNullOrEmpty(dmodel.FXGradeIds))
            {
                where += $@" and d.{nameof(RB_Distributor_Info.FXGradeId)} in({dmodel.FXGradeIds})";
            }

            string sql = $@"
select count(0) as UserNum from RB_Member_User u
inner join rb_distributor_info d on u.Id =d.UserId 
where {where} 
";
            var obj = ExecuteScalar(sql);
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            else
            {
                return 0;
            }
        }

        /// <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}";
            }

            if (dmodel.SmallShopId > 0)
            {

                where += $@" and {nameof(RB_Member_User.SmallShopId)}={dmodel.SmallShopId}  ";

            }
            else
            {
                if (dmodel.IsZanYangUse == 1)
                {
                    where += $@" and {nameof(RB_Member_User.SmallShopId)}=0";
                }
            }

            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 (dmodel.SmallShopId > 0)
            {

                where += $@" and {nameof(RB_Member_User.SmallShopId)}={dmodel.SmallShopId}  ";

            }
            else
            {
                if (dmodel.IsZanYangUse == 1)
                {
                    where += $@" and {nameof(RB_Member_User.SmallShopId)}=0";
                }
            }



            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} ";
                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} ";
                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} ";
                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>
        /// 获取用户上级所有的id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetMemberParentIdStr(int id)
        {
            string sql = $@" select GetMemberParentrenList({id})";
            return ExecuteScalar(sql).ToString();
        }

        /// <summary>
        /// 获取用户下级所有的id (包括自己)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetMemberChildIdStr(int id)
        {
            string sql = $@" select GetMemberChildrenList_AllUser({id})";
            return ExecuteScalar(sql).ToString();
        }


        /// <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} ";
            }
            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} ";
            }
            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} ";
            }
            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();
        }

        #endregion

        #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)} = '{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

        #region 微店
        /// <summary>
        /// 获取微店所有的id (包括自己)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetSmallShopChildIdStr(int id)
        {

            string sql = $@" select GetSmallShopChildrenList_AllUser({id})";
            object result = ExecuteScalar(sql);
            if (result != null)
            {
                return result.ToString();
            }
            else return "-1";
        }

        #endregion

        #region 获取最上级用户
        /// <summary>
        /// 获取最上级用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int GetMemberFristParentren(int id)
        {
            string sql = $@" select GetMemberFristParentren({id})";
            var obj = ExecuteScalar(sql);
            if (obj != null)
            {
                return Convert.ToInt32(obj.ToString());
            }
            else
            {
                return 0;
            }
        }

        #endregion

        #region 其他模块

        /// <summary>
        /// 获取用户佣金
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetUserCommissionRepository(RB_Member_User_Extend query, bool IsGetCurrentMonth = false)
        {
            string where = "";
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    where += string.Format(" AND A.TenantId={0} ", query.TenantId);
                }
                if (query.MallBaseId > 0)
                {
                    where += string.Format(" AND A.MallBaseId={0} ", query.MallBaseId);
                }
                if (!string.IsNullOrWhiteSpace(query.UserIds))
                {
                    where += string.Format(" AND A.UserId IN({0}) ", query.UserIds);
                }
                if (query.Id > 0)
                {
                    where += string.Format(" AND A.UserId IN({0}) ", query.Id);
                }
            }
            if (IsGetCurrentMonth)
            {
                where += string.Format(" AND DATE_FORMAT(A.CreateDate, '%Y/%m')=DATE_FORMAT('{0}','%Y/%m') ", DateTime.Now.ToString("yyyy-MM-dd"));
            }
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT UserId AS Id,IsRemit,SUM(A.Commission) AS Commission 
FROM
( 
		SELECT A.UserId,SUM(A.Commission) AS Commission,A.IsRemit  
		FROM rb_goods_ordercommission AS A LEFT JOIN rb_goods_order AS B ON A.OrderId=B.OrderId
		WHERE B.OrderStatus<>7 AND A.OrderId NOT IN(select OrderId from rb_goods_orderaftersale where ReOrderStatus<>6) {0}
		GROUP BY A.UserId
		UNION ALL
 
		SELECT A.UserId,SUM(A.Commission) AS Commission,A.RemitStatus AS IsRemit
		FROM rb_goods_orderintroduction AS A LEFT JOIN rb_goods_order AS B ON A.OrderId=B.OrderId
		WHERE B.OrderStatus<>7 AND A.OrderId NOT IN(select OrderId from rb_goods_orderaftersale where ReOrderStatus<>6) {0}
		GROUP BY A.UserId,A.RemitStatus

		UNION ALL
		SELECT A.UserId,SUM(A.Commission) AS Commission,IsRemit
		FROM  rb_vipbuy_commission AS A
		WHERE  1=1 {0}
		GROUP BY A.UserId,A.IsRemit

		UNION ALL
		SELECT A.UserId,SUM(A.Commission) AS Commission,A.IsRemit
		FROM  rb_smallshops_commission AS A LEFT JOIN rb_goods_order AS B ON A.OrderId=B.OrderId
		WHERE B.OrderStatus<>7 AND A.OrderId NOT IN(select OrderId from rb_goods_orderaftersale where ReOrderStatus<>6) {0}
		GROUP BY A.UserId,A.IsRemit
) AS A GROUP BY UserId,IsRemit
", where);
            return Get<RB_Member_User_Extend>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取用户层级
        /// </summary>
        /// <param name="userId1"></param>
        /// <param name="userId2"></param>
        /// <returns></returns>
        public string GetMyUserHierarchicalRelationList(int userId1, int userId2)
        {
            string sql = $@"SELECT GetMemberParentrenList_MyRelation({userId1},'{userId2}')";
            var obj = ExecuteScalar(sql);
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }


        /// <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_V2(int pageIndex, int pageSize, out long count, RB_Member_User_Extend dmodel)
        {
            string where = $" 1=1 and {nameof(RB_Member_User.IsSettlement)}=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="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="dmodel">查询条件</param>
        /// <returns></returns>
        public List<RB_Member_MiaiUser_Extend> GetMiAiPageList(int pageIndex, int pageSize, out long rowCount, RB_Member_MiaiUser_Extend dmodel)
        {
            string where = " 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and t.{nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and t.{nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and t.{nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (dmodel.IsDistributor > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.IsDistributor)}={dmodel.IsDistributor}";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and t.{nameof(RB_Member_User.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and t.{nameof(RB_Member_User.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }

            if (dmodel.Age > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Age)} >={dmodel.Age}";
            }
            if (dmodel.EndAge > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Age)} <={dmodel.EndAge}";
            }
            if (dmodel.Height > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Height)} >={dmodel.Height}";
            }
            if (dmodel.EndHeight > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Height)} <={dmodel.EndHeight}";
            }
            if (dmodel.Weight > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Weight)} >={dmodel.Weight}";
            }
            if (dmodel.EndWeight > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Weight)} <={dmodel.EndWeight}";
            }

            if (dmodel.YearMoney > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.YearMoney)} >={dmodel.YearMoney}";
            }
            if (dmodel.EndYearMoney > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.YearMoney)} <={dmodel.EndYearMoney}";
            }

            if (dmodel.CarInfo.HasValue && dmodel.CarInfo.Value > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.CarInfo)} ={(int)dmodel.CarInfo}";
            }

            if (dmodel.HouseInfo.HasValue && dmodel.HouseInfo.Value > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.HouseInfo)} ={(int)dmodel.HouseInfo}";
            }

            if (dmodel.Marriage.HasValue && dmodel.Marriage.Value > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Marriage)} ={(int)dmodel.Marriage}";
            }

            if (dmodel.EducationType.HasValue && dmodel.EducationType.Value > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.EducationType)} ={(int)dmodel.EducationType}";
            }


            //if (dmodel.IsEmptyUserPhoto == 1)
            //{
            //    where += $@" and IFNULL(a.{nameof(RB_Member_User.Photo)},'')=''";
            //}
            //HK06-01新增查询条件
            if (dmodel.OpenId != null && !string.IsNullOrWhiteSpace(dmodel.OpenId))
            {
                where += $@" and t.{nameof(RB_Member_User.OpenId)} = '%{dmodel.OpenId}%'";
            }
            //if (dmodel.IsSelectCommpany == 1)
            //{
            //    where += $@" and a.{nameof(RB_Member_User.Id)} between 5 and 100 ";
            //}
            string orderBy = "t.CreateDate desc";
            //if (dmodel.MinUserId > 0)
            //{
            //    where += $@" and a.{nameof(RB_Member_User.Id)}>{dmodel.MinUserId}";
            //    orderBy = "a.Id asc";
            //}
            string sql = $@"
SELECT * from (SELECT	a.*,mu.`Name` as SuperiorName,b.WechatNo, b.Id as BaseInfoId,b.Album,b.Birthday,b.Height,b.IsFaTie,b.Weight,b.YearMoney,b.EducationType,b.Marriage,b.CarInfo,b.HouseInfo,(YEAR (curdate()) - YEAR (b.Birthday) - 1 + (	DATE_FORMAT(b.Birthday, '%m%d') <= (	DATE_FORMAT(curdate(), '%m%d')	)	)) Age
FROM RB_Member_User AS a LEFT JOIN rb_miai_baseinfo AS b ON a.Id = b.UserId LEFT JOIN rb_member_user as mu on a.SuperiorId=mu.Id) as t   where {where} order by {orderBy}
";
            return GetPage<RB_Member_MiaiUser_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_MiaiUser_Extend> GetMiAiList(RB_Member_MiaiUser_Extend dmodel)
        {
            string where = " 1=1 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrWhiteSpace(dmodel.UserIds))
            {
                where += $@" and t.{nameof(RB_Member_User.Id)} in ({dmodel.UserIds})";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and t.{nameof(RB_Member_User.Name)} like '%{dmodel.Name}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.AliasName))
            {
                where += $@" and t.{nameof(RB_Member_User.AliasName)} like '%{dmodel.AliasName}%'";
            }
            if (dmodel.Source > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.Source)} ={(int)dmodel.Source}";
            }
            if (!string.IsNullOrEmpty(dmodel.Moblie))
            {
                where += $@" and t.{nameof(RB_Member_User.Moblie)} like '%{dmodel.Moblie}%'";
            }
            if (dmodel.MemberGrade > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.MemberGrade)}={dmodel.MemberGrade}";
            }
            if (dmodel.SuperiorId > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.SuperiorId)}={dmodel.SuperiorId}";
            }
            if (dmodel.IsDistributor > 0)
            {
                where += $@" and t.{nameof(RB_Member_User.IsDistributor)}={dmodel.IsDistributor}";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and t.{nameof(RB_Member_User.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and t.{nameof(RB_Member_User.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }

            if (dmodel.Age > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Age)} >={dmodel.Age}";
            }
            if (dmodel.EndAge > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Age)} <={dmodel.EndAge}";
            }
            if (dmodel.Height > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Height)} >={dmodel.Height}";
            }
            if (dmodel.EndHeight > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Height)} <={dmodel.EndHeight}";
            }
            if (dmodel.Weight > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Weight)} >={dmodel.Weight}";
            }
            if (dmodel.EndWeight > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Weight)} <={dmodel.EndWeight}";
            }

            if (dmodel.YearMoney > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.YearMoney)} >={dmodel.YearMoney}";
            }
            if (dmodel.EndYearMoney > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.YearMoney)} <={dmodel.EndYearMoney}";
            }

            if (dmodel.CarInfo.HasValue && dmodel.CarInfo.Value > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.CarInfo)} ={(int)dmodel.CarInfo}";
            }

            if (dmodel.HouseInfo.HasValue && dmodel.HouseInfo.Value > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.HouseInfo)} ={(int)dmodel.HouseInfo}";
            }

            if (dmodel.Marriage.HasValue && dmodel.Marriage.Value > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.Marriage)} ={(int)dmodel.Marriage}";
            }

            if (dmodel.EducationType.HasValue && dmodel.EducationType.Value > 0)
            {
                where += $@" and t.{nameof(RB_Member_MiaiUser_Extend.EducationType)} ={(int)dmodel.EducationType}";
            }


            //if (dmodel.IsEmptyUserPhoto == 1)
            //{
            //    where += $@" and IFNULL(a.{nameof(RB_Member_User.Photo)},'')=''";
            //}
            //HK06-01新增查询条件
            if (dmodel.OpenId != null && !string.IsNullOrWhiteSpace(dmodel.OpenId))
            {
                where += $@" and t.{nameof(RB_Member_User.OpenId)} = '%{dmodel.OpenId}%'";
            }
            //if (dmodel.IsSelectCommpany == 1)
            //{
            //    where += $@" and a.{nameof(RB_Member_User.Id)} between 5 and 100 ";
            //}
            string orderBy = "t.CreateDate desc";
            //if (dmodel.MinUserId > 0)
            //{
            //    where += $@" and a.{nameof(RB_Member_User.Id)}>{dmodel.MinUserId}";
            //    orderBy = "a.Id asc";
            //}
            string sql = $@"
SELECT * from (SELECT	a.*, b.Album,b.Birthday,b.Height,b.Weight,b.YearMoney,b.EducationType,b.Marriage,b.CarInfo,b.HouseInfo,(YEAR (curdate()) - YEAR (b.Birthday) - 1 + (	DATE_FORMAT(b.Birthday, '%m%d') <= (	DATE_FORMAT(curdate(), '%m%d')	)	)) Age
FROM RB_Member_User AS a LEFT JOIN rb_miai_baseinfo AS b ON a.Id = b.UserId) as t   where {where} order by {orderBy}
";
            return Get<RB_Member_MiaiUser_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> GetEduPageList(int pageIndex, int pageSize, out long rowCount, RB_Member_User_Extend dmodel)
        {
            string 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 (dmodel.Id > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.Id)}={dmodel.Id}";
            }
            if (dmodel.EduSchoolId > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.EduSchoolId)}={dmodel.EduSchoolId}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and a.{nameof(RB_Member_User.Name)} 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 (dmodel.IsDistributor > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.IsDistributor)}={dmodel.IsDistributor}";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and a.{nameof(RB_Member_User.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and a.{nameof(RB_Member_User.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }
            if (dmodel.IsEmptyUserPhoto == 1)
            {
                where += $@" and IFNULL(a.{nameof(RB_Member_User.Photo)},'')=''";
            }
            //HK06-01新增查询条件
            if (dmodel.OpenId != null && !string.IsNullOrWhiteSpace(dmodel.OpenId))
            {
                where += $@" and a.{nameof(RB_Member_User.OpenId)} = '%{dmodel.OpenId}%'";
            }
            if (dmodel.IsSelectCommpany == 1)
            {
                where += $@" and a.{nameof(RB_Member_User.Id)} between 5 and 100 ";
            }
            string orderBy = " a.CreateDate desc";
            if (dmodel.MinUserId > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.Id)}>{dmodel.MinUserId}";
                orderBy = " a.Id asc";
            }

            string sql = $@"select a.*,b.StudentId  from RB_Member_User  as a LEFT JOIN rb_education_baseinfo as b on a.Id=b.UserId and b.`Status`=0 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> GetEduList( RB_Member_User_Extend dmodel)
        {
            string 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 (dmodel.Id > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.Id)}={dmodel.Id}";
            }
            if (!string.IsNullOrEmpty(dmodel.Name))
            {
                where += $@" and a.{nameof(RB_Member_User.Name)} 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 (dmodel.IsDistributor > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.IsDistributor)}={dmodel.IsDistributor}";
            }
            if (!string.IsNullOrEmpty(dmodel.StartTime))
            {
                where += $@" and a.{nameof(RB_Member_User.CreateDate)} >='{dmodel.StartTime}'";
            }
            if (!string.IsNullOrEmpty(dmodel.EndTime))
            {
                where += $@" and a.{nameof(RB_Member_User.CreateDate)} <='{dmodel.EndTime} 23:59:59'";
            }
            if (dmodel.IsEmptyUserPhoto == 1)
            {
                where += $@" and IFNULL(a.{nameof(RB_Member_User.Photo)},'')=''";
            }
            //HK06-01新增查询条件
            if (dmodel.OpenId != null && !string.IsNullOrWhiteSpace(dmodel.OpenId))
            {
                where += $@" and a.{nameof(RB_Member_User.OpenId)} = '%{dmodel.OpenId}%'";
            }
            if (dmodel.IsSelectCommpany == 1)
            {
                where += $@" and a.{nameof(RB_Member_User.Id)} between 5 and 100 ";
            }
            string orderBy = " a.CreateDate desc";
            if (dmodel.MinUserId > 0)
            {
                where += $@" and a.{nameof(RB_Member_User.Id)}>{dmodel.MinUserId}";
                orderBy = " a.Id asc";
            }

            string sql = $@"select a.*,b.StudentId  from RB_Member_User  as a LEFT JOIN rb_education_baseinfo as b on a.Id=b.UserId and b.`Status`=0 where {where} order by {orderBy} ";
            return Get<RB_Member_User_Extend>( sql).ToList();
        }
        #endregion

        #region 报表查询

        /// <summary>
        /// 清除用户下线数据
        /// </summary>
        /// <returns></returns>
        public bool DelUserReferrals()
        {
            string sql = $@"DELETE FROM rb_user_referrals";
            return Execute(sql) > 0;
        }

        /// <summary>
        /// 更新下线数据
        /// </summary>
        public void UpdateUserReferrals()
        {
            string sql = $@" CALL SetInitializeReferrals()";
            Execute(sql);
        }

        /// <summary>
        /// 获取下线列表
        /// </summary>
        /// <param name="mallbaseId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetNewReferralsList(int mallbaseId, string startTime, string endTime)
        {
            string sql = $@"SELECT tt.UserId AS Id,u1.`Name`,tt.Num AS ChildrenNum FROM(
SELECT t.UserId,COUNT(0) AS Num FROM rb_user_referrals t
INNER JOIN rb_member_user u on FIND_IN_SET(u.Id,t.DUserIds)
WHERE u.CreateDate>='{startTime}' AND u.CreateDate <='{endTime} 23:59:59' AND u.MallBaseId ={mallbaseId} 
GROUP BY t.UserId
)tt
LEFT JOIN rb_member_user u1 on tt.UserId = u1.Id
ORDER BY tt.Num DESC";
            return Get<RB_Member_User_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取佣金排名
        /// </summary>
        /// <param name="mallbaseId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetOrderCommissionList(int mallbaseId, string startTime, string endTime)
        {
            string sql = $@"SELECT u.Id,u.`Name`,i.TotalCommission AS TotalCommission,
IFNULL(tt.Num,0) + IFNULL(nt.Num,0) AS AllOrderNum,
IFNULL(tt.Income,0) + IFNULL(nt.Income,0) AS Commission 
 FROM rb_distributor_info i
INNER JOIN rb_member_user u on i.UserId = u.Id
LEFT JOIN(
SELECT t.UserId,COUNT(0) AS Num,SUM(t.Income) AS Income FROM(
SELECT oc.UserId,oc.OrderId,o.Income FROM rb_goods_ordercommission oc
LEFT JOIN rb_goods_order o on oc.OrderId = o.OrderId
WHERE o.OrderStatus <> 7 and o.Recycled=2 and o.`Status`=0 and o.MallBaseId ={mallbaseId} and o.CreateDate >= '{startTime}' AND o.CreateDate <= '{endTime} 23:59:59'
GROUP BY oc.UserId,oc.OrderId)t GROUP BY t.UserId
)tt on tt.UserId = u.Id
LEFT JOIN (
	SELECT o.UserId,COUNT(0) AS Num,SUM(o.Income) AS Income FROM rb_goods_order o 
LEFT JOIN rb_goods_ordercommission oc ON o.OrderId = oc.OrderId
WHERE o.OrderStatus <> 7 and o.Recycled=2 and o.`Status`=0 and o.MallBaseId ={mallbaseId} and o.CreateDate >= '{startTime}' AND o.CreateDate <= '{endTime} 23:59:59' AND oc.Id is NULL
GROUP BY o.UserId
) nt on nt.UserId = u.Id
WHERE u.SuperiorId BETWEEN 1 AND 20 
ORDER BY i.TotalCommission DESC;";
            return Get<RB_Member_User_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取vip购买列表
        /// </summary>
        /// <param name="mallbaseId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetVipBuyList(int mallbaseId, string startTime, string endTime)
        {
            string sql = $@"SELECT u.Id,u.`Name`,1 as AllOrderNum,v.Money AS Income FROM rb_vip_buy v
LEFT JOIN rb_member_user u on v.UserId = u.Id
 WHERE v.`Status`=0 AND v.MallBaseId ={mallbaseId} AND v.CreateDate >='{startTime}' AND v.CreateDate <='{endTime} 23:59:59'";
            return Get<RB_Member_User_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取新用户
        /// </summary>
        /// <param name="mallbaseId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<RB_Member_User_Extend> GetNewUserList(int mallbaseId, string startTime, string endTime)
        {
            string sql = $@"SELECT tt.UserId as Id,u.`Name`,u1.`Name` as PName1,u2.`Name` as PName2,u3.`Name` as PName3,o2.OrderId as ShopId FROM(
SELECT * FROM (
SELECT UserId,COUNT(0) as Count FROM rb_goods_order o WHERE o.OrderStatus <> 1 and o.OrderStatus <> 7 and o.Recycled=2 
and o.`Status`=0 and o.CreateDate >= '{startTime}' AND o.CreateDate < '{endTime} 23:59:59'
GROUP BY UserId)t 
WHERE t.Count =1 and (SELECT COUNT(0) AS Count1 FROM rb_goods_order o1 WHERE o1.UserId = t.UserId and o1.OrderStatus <> 1 
and o1.OrderStatus <> 7 and o1.Recycled=2 and o1.`Status`=0 and o1.MallBaseId ={mallbaseId} and o1.CreateDate < '{startTime}')=0
)tt 
LEFT JOIN rb_member_user u on tt.UserId = u.Id
LEFT JOIN rb_member_user u1 on u.SuperiorId = u1.Id
LEFT JOIN rb_member_user u2 on u1.SuperiorId = u2.Id
LEFT JOIN rb_member_user u3 on u2.SuperiorId = u3.Id
LEFT JOIN rb_goods_order o2 on o2.UserId = tt.UserId
WHERE o2.TenantId = 1 and o2.OrderStatus <> 1 and o2.OrderStatus <> 7 and o2.Recycled=2 and o2.`Status`=0 and o2.MallBaseId ={mallbaseId} and o2.CreateDate <='{endTime} 23:59:59'";
            return Get<RB_Member_User_Extend>(sql).ToList();
        }

        #endregion

        #region 订单统计

        /// <summary>
        /// 获取日期以前用户数量
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <param name="startTime"></param>
        /// <returns></returns>
        public int GetMemberUserTotalNumForDay(int tenantId, int mallBaseId, string startTime)
        {
            string sql = $@" select count(0) as count from RB_Member_User u
inner join rb_distributor_info d on u.Id = d.UserId
where u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and d.TenantId={tenantId} and d.Status =0 and d.AuditStatus =2 and u.CreateDate <='{startTime} 23:59:59'";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 获取日期截止付款用户数量
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <param name="startTime"></param>
        /// <returns></returns>
        public int GetMemberUserPayNumForDay(int tenantId, int mallBaseId, string startTime)
        {
            string sql = $@" SELECT count(0) FROM
(select u.Id from RB_Member_User u
LEFT JOIN rb_goods_order o on u.Id = o.UserId
where u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and u.CreateDate <='{startTime} 23:59:59' and o.PaymentTime is not null
GROUP BY u.Id)t ";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 获取日期当天复购人数
        /// </summary>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <param name="startTime"></param>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public int GetMemberReBuyNumForNowDay(int tenantId, int mallBaseId, string startTime, string userIds)
        {
            string sql = $@"SELECT count(0) FROM
(select u.Id from RB_Member_User u
LEFT JOIN rb_goods_order o on u.Id = o.UserId
 where u.TenantId={tenantId} and u.MallBaseId={mallBaseId} and o.OrderStatus <>7 and o.CreateDate <'{startTime}' and o.PaymentTime is not null and u.Id in({userIds})
GROUP BY u.Id)t";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }
        #endregion
    }
}
