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>
    {

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



        #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


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