OA2023/OASystem/OASystem.Infrastructure/Repositories/Resource/CountryFeeRepository.cs

using AutoMapper;
using MathNet.Numerics.Distributions;
using OASystem.Domain.Dtos.Resource;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.Resource;

namespace OASystem.Infrastructure.Repositories.Resource
{
    public class CountryFeeRepository : BaseRepository<Res_CountryFeeCost, CountryFeeCostView>
    {
        private readonly IMapper _mapper;
        public CountryFeeRepository(SqlSugarClient sqlSugar, IMapper mapper) : base(sqlSugar)
        {
            _mapper = mapper;
        }

        public async Task<Res_CountryFeeCost> InfoByCountryName(string countryName)
        {
            if (string.IsNullOrEmpty(countryName)) return null;
            return await _sqlSugar.Queryable<Res_CountryFeeCost>()
                .FirstAsync(it => it.VisaCountry == countryName);
        }


        public async Task<JsonView> OperationCountryFeeCost(OperationCountryFeeCostDto dto)
        {
            var result = new JsonView() { Code = StatusCodes.Status400BadRequest, Msg = "未知错误" };

            var countryFeeCost = _mapper.Map<Res_CountryFeeCost>(dto);

            countryFeeCost.LastUpdateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            if (dto.Status == 1)//添加
            {
                var exists = await _sqlSugar.Queryable<Res_CountryFeeCost>()
                    .AnyAsync(x => x.IsDel == 0 &&
                                   x.VisaFeeType == dto.VisaFeeType &&
                                   x.VisaContinent == dto.VisaContinent &&
                                   x.VisaCountry == dto.VisaCountry);

                if (exists)
                {
                    result.Msg = "该国家已存在，请勿重复添加！";
                    return result;
                }
                else//不存在，可添加
                {

                    int id = await AddAsyncReturnId(countryFeeCost);
                    if (id == 0)
                    {
                        result.Msg = "添加失败！";
                        return result;
                    }
                    result.Code = StatusCodes.Status200OK;
                    result.Msg = "添加成功！";
                }
            }
            else if (dto.Status == 2)//修改
            {
                var update = await _sqlSugar
                    .Updateable(countryFeeCost)
                    .IgnoreColumns(it => new { it.Id, it.DeleteUserId, it.DeleteTime, it.CreateUserId, it.CreateTime, it.IsDel })
                    .Where(it => it.Id == countryFeeCost.Id)
                    .ExecuteCommandAsync();
                //bool res = await UpdateAsync(a => a.Id == dto.Id, a => _CountryFeeCost);
                if (update < 1)
                {
                    result.Msg = "修改失败！";
                    return result;
                }
                result.Code = StatusCodes.Status200OK;
                result.Msg = "修改成功！";
            }
            else
            {
                result.Msg = "请传入Status参数，1添加 2修改！";
            }

            return result;
        }


        #region New

        /// <summary>
        /// Page List Async
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="feeType"></param>
        /// <param name="countryName"></param>
        /// <returns></returns>
        public async Task<JsonView> PageListAsync(VisaFeeStandardListDto dto)
        {
            int pageIndex = dto.PageIndex <= 0 ? 1 : dto.PageIndex;
            int pageSize = dto.PageSize <= 0 ? 10 : dto.PageSize;
            int feeType = dto.VisaFeeType < 0 ? -1 : dto.VisaFeeType; // -1:全部 0:因公 1:因私
            string countryName = dto.CountryName?.Trim() ?? string.Empty;

            var query = _sqlSugar.Queryable<Res_VisaFeeStandard>()
                .LeftJoin<Sys_Users>((x, y) => x.LastUpdateUserId == y.Id)
                .Where((x, y) => x.IsDel == 0 && x.FeeType == feeType)
                .WhereIF(!string.IsNullOrEmpty(countryName), x => x.Country.Contains(countryName) || countryName.Contains(x.Country))
                .OrderByDescending((x, y) => x.LastUpdateTime)
                .Select((x, y) => new VisaFeeStandardListView
                {
                    Id = x.Id,
                    Continent = x.Continent,
                    Country = x.Country,
                    FeeType = x.FeeType,
                    LastUpdateUserName = y.CnName,
                    LastUpdateTime = x.LastUpdateTime
                });

            RefAsync<int> total = 0;
            var pageList = await query.ToPageListAsync(pageIndex, pageSize, total);

            if (!pageList.Any())
            {
                return new JsonView
                {
                    Code = StatusCodes.Status200OK,
                    Data = pageList,
                    Count = total,
                    Msg = "暂无数据!"
                };
            }

            var ids = pageList.Select(x => x.Id).ToList();
            var detailsList = await _sqlSugar.Queryable<Res_VisaFeeStandardDetails>()
                 .LeftJoin<Sys_Cities>((x, y) => x.ProvinceId == y.Id && (y.Level == 1 || y.Level == 4))
                 .Where((x, y) => ids.Contains( x.ParentId ) && x.IsDel == 0)
                 .Select((x, y) => new VisaFeeStandardDetails
                 {
                     Id = x.Id,
                     ParentId = x.ParentId,
                     ProvinceId = x.ProvinceId,
                     ProvinceName = y.Name_CN,
                     VisaAddress = x.VisaAddress,
                     IsVisaOnArrival = x.IsVisaOnArrival,
                     IsElectronicSign = x.IsElectronicSign,
                     VisaTime = x.VisaTime,
                     IsVisaExemptionLarge = x.IsVisaExemptionLarge,
                     LargeVisaPrice = x.LargeVisaPrice,
                     LargeAgencyFee = x.LargeAgencyFee,
                     IsVisaExemptionSmall = x.IsVisaExemptionSmall,
                     SmallVisaPrice = x.SmallVisaPrice,
                     SmallAgencyFee = x.SmallAgencyFee,
                     NormExtFee = x.NormExtFee,
                     UrgExtFee = x.UrgExtFee,
                     IsUrgent = x.IsUrgent,
                     UrgentTime = x.UrgentTime,
                     UrgentPrice = x.UrgentPrice,
                     UrgentPriceDesc = x.UrgentPriceDesc,
                     Remark = y.Remark,
                 })
                 .ToListAsync();

            var specifiedOrder = new List<string> { "四川", "重庆", "贵州", "云南" };

            foreach (var item in pageList)
            {
                var provinceDetails = detailsList.Where(x => x.ParentId == item.Id).ToList();
                if (provinceDetails.Any())
                    provinceDetails = VisaFeeStandardDetails.SortByProvinces(provinceDetails, specifiedOrder);
                item.VisaFees = provinceDetails;
            }

            return new JsonView
            {
                Code = StatusCodes.Status200OK,
                Data = pageList,
                Count = total,
                Msg = "操作成功!"
            };
        }

        /// <summary>
        /// info Async
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<JsonView> InfoAsync(int id)
        {
            var view = await Query<Res_VisaFeeStandard>(x => x.Id == id)
                .Select(x => new VisaFeeStandardInfoView()
                {
                    Id = x.Id,
                    Continent = x.Continent,
                    Country = x.Country,
                    FeeType = x.FeeType,
                })
                .FirstAsync();
            if (view == null)
            {
                return new JsonView
                {
                    Code = StatusCodes.Status200OK,
                    Msg = "暂无数据!",
                    Data = view
                };
            }

            var detailsList = await _sqlSugar.Queryable<Res_VisaFeeStandardDetails>()
                .LeftJoin<Sys_Cities>((x, y) => x.ProvinceId == y.Id && (y.Level == 1 || y.Level == 4))
                .Where((x, y) => x.ParentId == view.Id && x.IsDel == 0)
                .Select((x, y) => new VisaFeeStandardDetails {
                    Id = x.Id,
                    ParentId = x.ParentId,
                    ProvinceId = x.ProvinceId,
                    ProvinceName = y.Name_CN,
                    VisaAddress = x.VisaAddress,
                    IsVisaOnArrival = x.IsVisaOnArrival,
                    IsElectronicSign = x.IsElectronicSign,
                    VisaTime = x.VisaTime,
                    IsVisaExemptionLarge = x.IsVisaExemptionLarge,
                    LargeVisaPrice = x.LargeVisaPrice,
                    LargeAgencyFee = x.LargeAgencyFee,
                    IsVisaExemptionSmall = x.IsVisaExemptionSmall,
                    SmallVisaPrice = x.SmallVisaPrice,
                    SmallAgencyFee = x.SmallAgencyFee,
                    NormExtFee = x.NormExtFee,
                    UrgExtFee = x.UrgExtFee,
                    IsUrgent = x.IsUrgent,
                    UrgentTime = x.UrgentTime,
                    UrgentPrice = x.UrgentPrice,
                    UrgentPriceDesc = x.UrgentPriceDesc,
                    Remark = y.Remark,
                })
                .ToListAsync();

            // 指定的省份顺序
            var specifiedOrder = new List<string> { "四川", "重庆", "贵州", "云南" };
            if (detailsList.Any()) 
                detailsList = VisaFeeStandardDetails.SortByProvinces(detailsList, specifiedOrder);

            view.VisaFees = detailsList;

            return new JsonView
            {
                Code = StatusCodes.Status200OK,
                Msg = "操作成功!",
                Data = view
            };
        }

        /// <summary>
        /// Save Async
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<JsonView> SaveAsync(VisaFeeStandardSaveDto dto)
        {
            var now = DateTime.Now;
            var standardInfo = _mapper.Map<Res_VisaFeeStandard>(dto);
            standardInfo.LastUpdateTime = now;
            standardInfo.LastUpdateUserId = dto.CurrUserId;
            standardInfo.CreateTime = now;
            standardInfo.CreateUserId = dto.CurrUserId;

            // 指定的省份顺序
            var specifiedOrder = new List<string> { "四川", "重庆", "贵州", "云南" };
            if (dto.VisaFees.Any())
                dto.VisaFees = VisaFeeStandardDetails.SortByProvinces(dto.VisaFees, specifiedOrder);

            var standardDetails = _mapper.Map<List<Res_VisaFeeStandardDetails>>(dto.VisaFees);
            standardDetails.ForEach(x =>
            {
                x.CreateUserId = dto.CurrUserId;
                x.CreateTime = now;
            });

            string msg = string.Empty;
            _sqlSugar.BeginTran();
            try
            {
                if (standardInfo.Id < 1) // 添加
                {
                    var insertId = await _sqlSugar.Insertable(standardInfo).ExecuteReturnIdentityAsync();
                    if (insertId < 1)
                    {
                        _sqlSugar.RollbackTran();
                        return new JsonView { Code = StatusCodes.Status400BadRequest, Msg = "添加失败!" };
                    }

                    standardDetails.ForEach(x => x.ParentId = insertId);
                    var detailsResult = await _sqlSugar.Insertable(standardDetails).ExecuteCommandAsync();
                    if (detailsResult < 1)
                    {
                        _sqlSugar.RollbackTran();
                        return new JsonView { Code = StatusCodes.Status400BadRequest, Msg = "添加失败!" };
                    }
                    msg = "添加成功!";
                }
                else // 修改
                {
                    var updStatus = await _sqlSugar.Updateable(standardInfo)
                        .IgnoreColumns(x => new { x.IsDel, x.CreateUserId, x.CreateTime, x.DeleteUserId, x.DeleteTime })
                        .ExecuteCommandAsync();

                    if (updStatus < 1)
                    {
                        _sqlSugar.RollbackTran();
                        return new JsonView { Code = StatusCodes.Status400BadRequest, Msg = "修改失败!" };
                    }

                    await _sqlSugar.Deleteable<Res_VisaFeeStandardDetails>()
                        .Where(x => x.ParentId == standardInfo.Id)
                        .ExecuteCommandAsync();

                    standardDetails.ForEach(x => x.ParentId = standardInfo.Id);
                    var detailsResult = await _sqlSugar.Insertable(standardDetails).ExecuteCommandAsync();
                    if (detailsResult < 1)
                    {
                        _sqlSugar.RollbackTran();
                        return new JsonView { Code = StatusCodes.Status400BadRequest, Msg = "修改失败!" };
                    }
                    msg = "修改成功!";
                }

                _sqlSugar.CommitTran();
                return new JsonView { Code = StatusCodes.Status200OK, Msg = msg };
            }
            catch(Exception ex)
            {
                _sqlSugar.RollbackTran();
                msg = ex.Message;
            }
            return new JsonView { Code = StatusCodes.Status400BadRequest, Msg = msg };
        }

        /// <summary>
        /// SoftDel Async
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<JsonView> SoftDelAsync(int userId, int id)
        {
            _sqlSugar.BeginTran();
            try
            {
                var nowString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                // 主表软删除
                var delStatus = await _sqlSugar.Updateable<Res_VisaFeeStandard>()
                    .SetColumns(x => x.DeleteUserId == userId)
                    .SetColumns(x => x.DeleteTime == nowString)
                    .SetColumns(x => x.IsDel == 1)
                    .Where(x => x.Id == id)
                    .ExecuteCommandAsync();
                if (delStatus < 1)
                {
                    _sqlSugar.RollbackTran();
                    return new JsonView { Code = StatusCodes.Status400BadRequest, Msg = "删除失败!" };
                }

                // 子表软删除
                var detailsDelStatus = await _sqlSugar.Updateable<Res_VisaFeeStandardDetails>()
                    .SetColumns(x => x.DeleteUserId == userId)
                    .SetColumns(x => x.DeleteTime == nowString)
                    .SetColumns(x => x.IsDel == 1)
                    .Where(x => x.ParentId == id)
                    .ExecuteCommandAsync();

                if (detailsDelStatus < 1)
                {
                    _sqlSugar.RollbackTran();
                    return new JsonView { Code = StatusCodes.Status400BadRequest, Msg = "删除失败!" };
                }

                _sqlSugar.CommitTran();
                return new JsonView { Code = StatusCodes.Status200OK, Msg = "操作成功!" };
            }
            catch (Exception ex)
            {
                _sqlSugar.RollbackTran();
                return new JsonView { Code = StatusCodes.Status400BadRequest, Msg = ex.Message };
            }
        }


        #endregion
    }
}