znyc.recruitment.admin.api/Znyc.Recruitment.Admin.Security.Core/Repositories/Recruitment/AdminUserRepository.cs

using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Znyc.Recruitment.Admin.Commons.IDbContext;
using Znyc.Recruitment.Admin.Commons.Repositories;
using Znyc.Recruitment.Admin.Security.Dtos;
using Znyc.Recruitment.Admin.Security.Entitys;
using Znyc.Recruitment.Admin.Security.IRepositories;

namespace Znyc.Recruitment.Admin.Security.Repositories
{
    /// <summary>
    ///     应用仓储实现
    /// </summary>
    public class AdminUserRepository : BaseRepository<AdminUserEntity, long>, IAdminUserRepository
    {
        public AdminUserRepository()
        {
        }

        public AdminUserRepository(IDbContextCore context) : base(context)
        {
        }

        /// <summary>
        ///     根据用户账号查询用户信息
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public async Task<AdminUserEntity> GetByUserName(string userName)
        {
            string sql = @"SELECT * FROM sys_adminuser t WHERE t.Account = @UserName";
            return await DapperConn.QueryFirstOrDefaultAsync<AdminUserEntity>(sql, new { UserName = userName });
        }

        /// <summary>
        ///     根据用户手机号码查询用户信息
        /// </summary>
        /// <param name="mobilephone">手机号码</param>
        /// <returns></returns>
        public async Task<AdminUserEntity> GetUserByMobilePhone(string mobilephone)
        {
            string sql = @"SELECT * FROM sys_adminuser t WHERE t.MobilePhone = @MobilePhone";
            return await DapperConn.QueryFirstOrDefaultAsync<AdminUserEntity>(sql, new { MobilePhone = mobilephone });
        }

        /// <summary>
        ///     根据Email、Account、手机号查询用户信息
        /// </summary>
        /// <param name="account">登录账号</param>
        /// <returns></returns>
        public async Task<AdminUserEntity> GetUserByLogin(string account)
        {
            string sql =
                @"SELECT * FROM sys_adminuser t WHERE (t.Account = @Account  or t.MobilePhone = @Account)";
            return await DapperConn.QueryFirstOrDefaultAsync<AdminUserEntity>(sql, new { Account = account });
        }

        /// <summary>
        ///     根据微信UnionId查询用户信息
        /// </summary>
        /// <param name="unionId">UnionId值</param>
        /// <returns></returns>
        public AdminUserEntity GetUserByUnionId(string unionId)
        {
            string sql = string.Format("select * from sys_adminuser where UnionId = '{0}'", unionId);
            return DapperConn.QueryFirstOrDefault<AdminUserEntity>(sql);
        }

        /// <summary>
        ///     根据第三方OpenId查询用户信息
        /// </summary>
        /// <param name="openIdType">第三方类型</param>
        /// <param name="openId">OpenId值</param>
        /// <returns></returns>
        public AdminUserEntity GetUserByOpenId(string openIdType, string openId)
        {
            string sql = string.Format(
                "select * from sys_adminuser as u join sys_adminuserOpenIds as o on u.Id = o.UserId and  o.OpenIdType = '{0}' and o.OpenId = '{1}'",
                openIdType, openId);
            return DapperConn.QueryFirstOrDefault<AdminUserEntity>(sql);
        }

        /// <summary>
        ///     根据userId查询用户信息
        /// </summary>
        /// <param name="openIdType">第三方类型</param>
        /// <param name="userId">userId</param>
        /// <returns></returns>
        public UserOpenIdsEntity GetUserOpenIdByuserId(string openIdType, long userId)
        {
            string sql = string.Format("select * from sys_adminuserOpenIds  where OpenIdType = '{0}' and UserId = '{1}'",
                openIdType, userId);
            return DapperConn.QueryFirstOrDefault<UserOpenIdsEntity>(sql);
        }

        /// <summary>
        ///     更新用户信息,第三方平台
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="userLogOnEntity"></param>
        /// <param name="trans"></param>
        public bool UpdateUserByOpenId(AdminUserEntity entity, AdminUserLogOnEntity userLogOnEntity,
            UserOpenIdsEntity userOpenIds,
            IDbTransaction trans = null)
        {
            DbContext.GetDbSet<AdminUserEntity>().Add(entity);
            DbContext.GetDbSet<UserOpenIdsEntity>().Add(userOpenIds);
            return DbContext.SaveChanges() > 0;
        }

        /// <summary>
        ///     分页得到所有用户用于关注
        /// </summary>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IEnumerable<UserAllListFocusOutPutDto> GetUserAllListFocusByPage(string currentpage,
            string pagesize, long userId)
        {
            string sqlRecord = "";
            string sql = "";

            long countNotIn = (long.Parse(currentpage) - 1) * long.Parse(pagesize);

            sqlRecord = @"select * from sys_adminuser where UserName <> '游客' and  ismember=1 ";

            sql = @"SELECT TOP " + pagesize +
                  @"
case when t2.Id is null then 'n'
else 'y' end as IfFocus ,
IsNull(t3.totalFocus,0) as TotalFocus,
t1.*
from
(select ISNULL(tin2.VipGrade,0) as VipGrade,
ISNULL(tin2.IsAuthentication,0) as IsAuthentication,
ISNULL(tin2.AuthenticationType,0) as AuthenticationType,
tin1.* from sys_adminuser tin1
left join sys_adminuserExtend tin2 on tin1.Id=tin2.UserId
where UserName <> '游客' and  ismember=1) t1
left join
(select * from sys_adminuserFocus where CreatedUserId='" + userId + @"') t2
on t1.id=t2.focususerid
left join
(select  top 100 percent focusUserID,count(*) as totalFocus from sys_adminuserFocus group by focusUserID order by totalfocus desc) t3
on t1.Id=t3.focusUserID

where t1.Id not in
(
select top " + countNotIn + @"
tt1.Id
from
(select ISNULL(tin2.VipGrade,0) as VipGrade,
ISNULL(tin2.IsAuthentication,0) as IsAuthentication,
ISNULL(tin2.AuthenticationType,0) as AuthenticationType,
tin1.* from sys_adminuser tin1
left join sys_adminuserExtend tin2 on tin1.Id=tin2.UserId
where UserName <> '游客' and  ismember=1) tt1
left join
(select * from sys_adminuserFocus where CreatedUserId='" + userId + @"') tt2
on tt1.id=tt2.focususerid
left join
(select  top 100 percent focusUserID,count(*) as totalFocus from sys_adminuserFocus group by focusUserID order by totalfocus desc) tt3
on tt1.Id=tt3.focusUserID

order by tt3.totalFocus desc
)

order by t3.totalFocus desc";

            List<UserAllListFocusOutPutDto> list = new List<UserAllListFocusOutPutDto>();

            IEnumerable<UserAllListFocusOutPutDto> infoOutputDto = DapperConn.Query<UserAllListFocusOutPutDto>(sql);

            //得到总记录数
            List<UserAllListFocusOutPutDto> recordCountList = DapperConn.Query<UserAllListFocusOutPutDto>(sqlRecord).AsList();

            list = infoOutputDto.AsList();
            for (int i = 0; i < list.Count; i++)
            {
                list[i].RecordCount = recordCountList.Count;
            }

            return list;
        }
    }
}