znyc.cloudcar.admin.api/Znyc.Cloudcar.Admin.Security.Core/Repositories/Recruitment/UserRepository.cs

using Dapper;
using System.Collections.Generic;
using System.Threading.Tasks;
using Znyc.Cloudcar.Admin.Commons.Enums;
using Znyc.Cloudcar.Admin.Commons.IDbContext;
using Znyc.Cloudcar.Admin.Commons.Pages;
using Znyc.Cloudcar.Admin.Commons.Repositories;
using Znyc.Cloudcar.Admin.Security.Dtos;
using Znyc.Cloudcar.Admin.Security.Entitys;
using Znyc.Cloudcar.Admin.Security.IRepositories;

namespace Znyc.Cloudcar.Admin.Security.Repositories
{
    /// <summary>
    /// </summary>
    public class UserRepository : BaseRepository<UserEntity, long>, IUserRepository
    {
        /// <summary>
        /// </summary>
        public UserRepository()
        {
        }

        public UserRepository(IDbContextCore dbContext) : base(dbContext)
        {
        }

        /// <summary>
        ///     ¸ù¾ÝÓû§UnionId²éѯÓû§ÐÅÏ¢
        /// </summary>
        /// <param name="unionId"></param>
        /// <returns></returns>
        public async Task<UserEntity> GetUserByUnionId(string unionId)
        {
            string sql = @"SELECT * FROM `user` t WHERE t.UnionId = @UnionId";
            return await DapperConn.QueryFirstOrDefaultAsync<UserEntity>(sql, new { UnionId = unionId });
        }

        public async Task<List<UserEntity>> FindUserWithPagerAsync(SearchUserModel search, PagerInfo info)
        {
            bool order = search.Order == "asc" ? false : true;
            string strwhere = "";
            if (search.CertificationState >= 0 && search.CertificationState != (int)UserCertificationEnum.None)
            {
                strwhere = $" AND user_certification.`State`={search.CertificationState}";
            }
            if (search.CertificationState == (int)UserCertificationEnum.None)
            {
                strwhere = $" AND `user`.`IsRealAuthentication`=0";
            }
            if (!string.IsNullOrEmpty(search.Keywords))
            {
                strwhere += string.Format(" and (`user`.UserName like '%{0}%'or `user`.Phone like '%{0}%')", search.Keywords);
            }

            if (search.State > 0)
            {
                strwhere += string.Format(" and `user`.State={0}", search.State);
            }

            if (!string.IsNullOrEmpty(search.StartTime))
            {
                strwhere += " and `user`.CreatedTime >='" + search.StartTime + " 00:00:00'";
            }

            if (!string.IsNullOrEmpty(search.EndTime))
            {
                strwhere += " and `user`.CreatedTime <='" + search.EndTime + " 23:59:59'";
            }

            string countSQL = $"select count(`user`.Id) as Total from `user` " +
                $"LEFT JOIN user_certification ON `user`.Id = user_certification.UserId " +
                $"Where `user`.IsDeleted = 0 {strwhere};";
            int minRow = search.PageSize * (search.CurrenetPageIndex - 1);
            string sql = $"select  *  from `user` where Id IN(select t.Id from (select `user`.Id from `user` " +
                $"LEFT JOIN user_certification ON `user`.Id = user_certification.UserId " +
                $"Where `user`.IsDeleted = 0 {strwhere} )as t)  " +
                $"order by `user`.{search.Sort} {search.Order} limit {minRow},{search.PageSize}; ";


            SqlMapper.GridReader reader = await DapperConnRead.QueryMultipleAsync(countSQL + sql);
            info.RecordCount = reader.ReadFirst<int>();
            List<UserEntity> list = reader.Read<UserEntity>().AsList();
            return list;
        }
    }
}