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

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

namespace Znyc.Recruitment.Admin.Security.Repositories
{
    public class RegionRepository : BaseRepository<RegionEntity, long>, IRegionRepository
    {
        public RegionRepository()
        {
        }

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

        /// <summary>
        ///     ¸ù¾ÝareaId²éѯ¹¤×÷³ÇÊÐ
        /// </summary>
        /// <param name="areaId"></param>
        /// <returns></returns>
        public async Task<string> GetRegionNameByAreaId(long areaId, long cityId, long provinceId)
        {
            string sql = "";
            if (areaId != 0)
            {
                sql = string.Format(
                    @"select group_concat(`r3`.`Name`,' ',`r2`.`Name`, ' ',`r1`.`Name` separator ',')  AS `GROUP_CONCAT(r1.``Name``,' ',r2.``Name``, ' ', r1.``Name`` )`
                                    from Znyc.`sys_region` `r1`
                                    left join Znyc.`sys_region` `r2` on((`r2`.`RegionId` = `r1`.`ParentId`))
									left join Znyc.`sys_region` `r3` on((`r3`.`RegionId` = `r2`.`ParentId`))
                                   where (`r1`.`RegionId` in ({0}))", areaId);
                return await DapperConn.QueryFirstOrDefaultAsync<string>(sql);
            }

            if (cityId != 0)
            {
                sql = string.Format(
                    @"select group_concat( `r2`.`Name`, ' ',`r1`.`Name` separator ',')  AS `GROUP_CONCAT(r2.``Name``,' ',r1.``Name``, ' ' )`
                                    from Znyc.`sys_region` `r1`
                                    left join Znyc.`sys_region` `r2` on((`r2`.`RegionId` = `r1`.`ParentId`))
                                   where (`r1`.`RegionId` in ({0}))", cityId);
                return await DapperConn.QueryFirstOrDefaultAsync<string>(sql);
            }

            sql = string.Format(@"SELECT `Name` FROM Znyc.sys_region WHERE Id={0}", provinceId);
            return await DapperConn.QueryFirstOrDefaultAsync<string>(sql);
        }

        /// <summary>
        ///     ¸ù¾ÝcityId²éѯ¹¤×÷³ÇÊÐ
        /// </summary>
        /// <param name="areaId"></param>
        /// <returns></returns>
        public async Task<string> GetRegionNameByCityId(string cityId)
        {
            string sql = string.Format(
                @"select group_concat( `r2`.`Name`, ' ',`r1`.`Name` separator ',')  AS `GROUP_CONCAT(r2.``Name``,' ',r1.``Name``, ' ' )`
                                    from Znyc.`sys_region` `r1`
                                    left join Znyc.`sys_region` `r2` on((`r2`.`RegionId` = `r1`.`ParentId`))
                                   where (`r1`.`RegionId` in ({0}))", cityId);
            return await DapperConn.QueryFirstOrDefaultAsync<string>(sql);
        }
    }
}