OA2023/OASystem/OASystem.Infrastructure/Repositories/Groups/VisaFeeInfoRepository.cs

using AutoMapper;
using OASystem.Domain;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.Resource;
using OASystem.Infrastructure.Repositories.Resource;
namespace OASystem.Infrastructure.Repositories.Groups
{
    /// <summary>
    /// 团组签证费用详情 info
    /// 仓储
    /// </summary>
    public class VisaFeeInfoRepository : BaseRepository<Grp_VisaFeeInfo, VisaInfoView>
    {
        private readonly IMapper _mapper;
        private Result _result;
        private readonly CountryFeeRepository _countryFeeRep;
        private readonly DelegationInfoRepository _groupRep;

        public VisaFeeInfoRepository(SqlSugarClient sqlSugar, IMapper mapper, CountryFeeRepository countryFeeRep, DelegationInfoRepository groupRep)
            : base(sqlSugar)
        {
            _mapper = mapper;
            _result = new Result() { Code = -2, Msg = "操作失败!" };
            _countryFeeRep = countryFeeRep;
            _groupRep = groupRep;
        }


        /// <summary>
        /// Init
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<Result> Init()
        {
            var data = await _sqlSugar.Queryable<Res_CountryFeeCost>().Where(it => it.IsDel == 0).ToListAsync();
            _result.Code = 0;
            _result.Data = data;
            _result.Msg = "操作成功!";
            return _result;
        }

        /// <summary>
        /// List
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<JsonView> List(int portType, int diId)
        {
            if (diId < 0)
                return new JsonView() { Code = StatusCodes.Status400BadRequest, Msg = "请传入有效的DiId参数" };
            if (portType < 1 || portType > 3)
                return new JsonView() { Code = StatusCodes.Status400BadRequest, Msg = "请传入有效的portType参数" };

            //防SQL注入
            string sql = @"Select vfi.Id,vfi.IsChecked,cfc.VisaCountry AS Country,cfc.VisaPrice As VisaFee,
                   vfi.OBType,vfi.AgencyFee,vfi.OtherFee,vfi.Remark 
                   From Grp_VisaFeeInfo vfi
                   Left Join Res_CountryFeeCost cfc On vfi.CountryVisaFeeId = cfc.Id
                   Where vfi.Isdel = 0  And vfi.Diid = @diId";
            var data = await _sqlSugar.SqlQueryable<VisaFeeInfosView>(sql)
                .AddParameters(new { diId })
                .ToListAsync();

            if (data.Count == 0)
            {
                var groupInfo = await _sqlSugar.Queryable<Grp_DelegationInfo>()
                    .FirstAsync(x => x.IsDel == 0 && x.Id == diId);

                if (groupInfo == null)
                    return new JsonView() { Code = StatusCodes.Status200OK, Data = data, Msg = "团组信息不存在！" };

                var countrys = _groupRep.GroupSplitCountry(groupInfo.VisitCountry ?? "");
                if (countrys.Count > 0)
                {
                    var visaFeeCountrys = await _sqlSugar.Queryable<Res_VisaFeeStandard>()
                        .Where(x => x.IsDel == 0 && x.FeeType == 0 && countrys.Contains(x.Country))
                        .Select(x => new VisaFeeStandardInfoView
                        {
                            Id = x.Id,
                            Continent = x.Continent,
                            Country = x.Country,
                            FeeType = x.FeeType,
                        })
                        .ToListAsync();

                    if (visaFeeCountrys.Count > 0)
                    {
                        var visaFeeCountryIds = visaFeeCountrys.Select(x => x.Id).ToList();
                        var visaFeeDetails = await _sqlSugar.Queryable<Res_VisaFeeStandardDetails>()
                            .Where(x => x.IsDel == 0 && visaFeeCountryIds.Contains(x.ParentId))
                            .ToListAsync();

                        // 建立ParentId到Details的字典，避免多次Where查找
                        var detailsDict = visaFeeDetails
                            .GroupBy(x => x.ParentId)
                            .ToDictionary(g => g.Key, g => g.ToList());

                        foreach (var countryInfo in visaFeeCountrys)
                        {
                            if (detailsDict.TryGetValue(countryInfo.Id, out var details))
                            {
                                // 只做映射，无副作用可省略变量
                                countryInfo.VisaFees = _mapper.Map<List<VisaFeeStandardDetails>>(details);
                            }
                        }

                        var provCityDatas = await _groupRep.ProvinceCityBasicSource();
                        int provinceId = _groupRep.FindParentIdByChildId(provCityDatas, groupInfo.CityId) ?? 0;

                        foreach (var country in countrys)
                        {
                            var countryInfo = visaFeeCountrys.FirstOrDefault(x => x.Country == country);
                            if (countryInfo != null && countryInfo.VisaFees != null)
                            {
                                //如果未找到对应的城市则默认四川的费用标准
                                var cityVisaFeeInfo = countryInfo.VisaFees.FirstOrDefault(x => x.ProvinceId == provinceId)
                                    ?? countryInfo.VisaFees.FirstOrDefault(x => x.ProvinceId == 122); // 122 四川省ID

                                if (cityVisaFeeInfo != null)
                                {
                                    data.Add(new VisaFeeInfosView()
                                    {
                                        IsChecked = 0,
                                        Country = country,
                                        //大公务签证费用为0时，使用小公务签证费用
                                        VisaFee = cityVisaFeeInfo.LargeVisaPrice == 0.00M
                                                  ? cityVisaFeeInfo.SmallVisaPrice
                                                  : cityVisaFeeInfo.LargeVisaPrice,
                                        OBType = 1,
                                        AgencyFee = cityVisaFeeInfo.LargeAgencyFee
                                    });

                                    data.Add(new VisaFeeInfosView()
                                    {
                                        IsChecked = 0,
                                        Country = country,
                                        //小公务签证费用为0时，使用大公务签证费用
                                        VisaFee = cityVisaFeeInfo.SmallVisaPrice == 0.00M
                                                  ? cityVisaFeeInfo.LargeVisaPrice
                                                  : cityVisaFeeInfo.SmallVisaPrice,
                                        OBType = 2,
                                        AgencyFee = cityVisaFeeInfo.SmallAgencyFee
                                    });
                                    continue;
                                }
                            }
                            // 没有费用信息时补空行
                            data.Add(new VisaFeeInfosView()
                            {
                                IsChecked = 0,
                                Country = country,
                                OBType = 1,
                            });
                            data.Add(new VisaFeeInfosView()
                            {
                                IsChecked = 0,
                                Country = country,
                                OBType = 2,
                            });
                        }
                    }
                    AddDefaultRows(data, 10);
                }
                else
                {
                    AddDefaultRows(data, 10);
                }
            }
            else if (data.Count <= 10)
            {
                AddDefaultRows(data, 10);
            }

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

        /// <summary>
        /// 补齐默认行
        /// </summary>
        /// <param name="list"></param>
        /// <param name="totalRows"></param>
        private static void AddDefaultRows(List<VisaFeeInfosView> list, int totalRows)
        {
            int defaultRow = totalRows - list.Count;
            for (int i = 0; i < defaultRow; i++)
            {
                list.Add(new VisaFeeInfosView { Id = 0, OBType = 1 });
            }
        }

        /// <summary>
        /// List
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> Update(VisaFeeAddAndUpdateDto dto)
        {
            if (dto.VisaFeeInfos.Count < 1) return _result = new Result() { Code = -1, Msg = "请传入有效的签证费用集合参数" };
            if (dto.PortType < 1 || dto.PortType > 3) return _result = new Result() { Code = -1, Msg = "请传入有效的portType参数" };

            var visaInfos = new List<Grp_VisaFeeInfo>();

            BeginTran();
            //bool visaFeeUpdate = false;

            var countrys = dto.VisaFeeInfos.GroupBy(x => x.Country);

            foreach (var country in countrys)
            {
                int countryVisaFeeId = 0;
                decimal _grandBusinessAgencyFee = 0;
                decimal _pettyBusinessAgencyFee = 0;
                decimal _visaFee = 0;
                foreach (var item in country.ToList())
                {
                    _visaFee = item.VisaFee;
                    if (item.OBType == 1) _grandBusinessAgencyFee = item.AgencyFee;
                    else if (item.OBType == 2) _pettyBusinessAgencyFee = item.AgencyFee;
                }

                var info = await _countryFeeRep.InfoByCountryName(country.Key);
                if (info == null) //添加
                {
                    int addId = _sqlSugar.Insertable(
                        new Res_CountryFeeCost()
                        {
                            VisaCountry = country.Key,
                            VisaPrice = _visaFee,
                            GrandBusinessAgencyFee = _grandBusinessAgencyFee,
                            PettyBusinessAgencyFee = _pettyBusinessAgencyFee,
                            LastUpdateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                            CreateUserId = dto.OpUserId,
                        }).ExecuteReturnIdentity();
                    if (addId > 0) countryVisaFeeId = addId;
                }
                else //修改
                {
                    countryVisaFeeId = info.Id;
                    if (_visaFee != info.VisaPrice) //价格不同的时候执行修改
                    {
                        var _CountryFeeCost = new Res_CountryFeeCost()
                        {
                            Id = info.Id,
                            VisaPrice = _visaFee,
                            GrandBusinessAgencyFee = _grandBusinessAgencyFee,
                            PettyBusinessAgencyFee = _pettyBusinessAgencyFee,
                            LastUpdateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                        };
                        int update1 = _sqlSugar.Updateable(_CountryFeeCost).UpdateColumns(it => new { it.VisaPrice, it.LastUpdateTime }).WhereColumns(it => it.Id).ExecuteCommand();
                        //if (update1 > 0)
                        //{
                        //    visaFeeUpdate = true;
                        //}
                    }
                }

                foreach (var item in country.ToList())
                {
                    visaInfos.Add(new Grp_VisaFeeInfo()
                    {
                        Id = item.Id,
                        DiId = dto.DiId,
                        IsChecked = item.IsChecked,
                        CountryVisaFeeId = countryVisaFeeId,
                        OBType = item.OBType,
                        AgencyFee = item.AgencyFee,
                        OtherFee = item.OtherFee
                    });
                }
            }


            //执行删除
            var update = _sqlSugar.Updateable<Grp_VisaFeeInfo>().SetColumns(it => new Grp_VisaFeeInfo()
            {
                DeleteUserId = dto.OpUserId,
                DeleteTime = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"),
                IsDel = 1

            }).Where(it => it.DiId == dto.DiId).ExecuteCommand();

            var add = _sqlSugar.Insertable(visaInfos).ExecuteCommand();
            if (add > 0)
            {
                CommitTran();
                return new Result() { Code = 0, Msg = "操作成功!" };
            }

            RollbackTran();
            return _result;
        }

        /// <summary>
        /// 根据Country和城市Id获取签证费用标准详情
        /// </summary>
        /// <param name="countrys">国家名称</param>
        /// <param name="cityId">城市Id</param>
        /// <returns></returns>
        public async Task<List<VisaFeeStandardDetailsView>> VisaInfoByCountryAndCityId(List<string> countrys, int cityId)
        {
            if (countrys == null || countrys.Count == 0 || cityId < 1)
                return new List<VisaFeeStandardDetailsView>();

            var countryIds = await _sqlSugar.Queryable<Res_VisaFeeStandard>()
                .Where(it => it.IsDel == 0 && countrys.Contains(it.Country))
                .Select(it => it.Id)
                .ToListAsync();

            if (countryIds.Count == 0)
                return new List<VisaFeeStandardDetailsView>();

            var visaFeeStandardDetails = await _sqlSugar.Queryable<Res_VisaFeeStandardDetails>()
                .LeftJoin<Res_VisaFeeStandard>((x, y) => x.ProvinceId == y.Id)
                .LeftJoin<Sys_Cities>((x, y, z) => x.ProvinceId == z.Id && (z.Level == 1 || z.Level == 4))
                .Where((x, y, z) => x.IsDel == 0 && countryIds.Contains(x.ParentId))
                .Select((x, y, z) => new VisaFeeStandardDetailsView
                {
                    Id = x.Id,
                    ParentId = x.ParentId,
                    CountryName = y.Country,
                    ProvinceId = x.ProvinceId,
                    ProvinceName = z.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 = x.Remark,
                })
                .ToListAsync();

            // 默认四川省ID
            const int defaultProvinceId = 122;
            var visaFeeInfoDefaultList = visaFeeStandardDetails.Where(it => it.ProvinceId == defaultProvinceId).ToList();

            var provCityDatas = await _groupRep.ProvinceCityBasicSource();
            int provinceId = _groupRep.FindParentIdByChildId(provCityDatas, cityId) ?? 0;

            if (provinceId == defaultProvinceId)
                return visaFeeInfoDefaultList;

            // 其他省份优先返回对应省份数据，否则用四川数据
            //其他省份单项信息为空或者费用为0时，使用四川的签证费用信息
            var visaInfosGroup = visaFeeStandardDetails.GroupBy(x => x.ParentId);
            var resultList = new List<VisaFeeStandardDetailsView>();

            var VisaInfoOtherProvs = new List<VisaFeeStandardDetailsView>();
            foreach (var group in visaInfosGroup)
            {
                var scInfo = group.FirstOrDefault(x => x.ProvinceId == defaultProvinceId);
                var provInfo = group.FirstOrDefault(x => x.ProvinceId == provinceId);

                if (provInfo != null)
                {
                    //送签地址
                    if (!string.IsNullOrEmpty(provInfo.VisaAddress)) scInfo.VisaAddress = provInfo.VisaAddress;

                    //签证时间(工作日)
                    if (!string.IsNullOrEmpty(provInfo.VisaTime)) scInfo.VisaTime = provInfo.VisaTime;

                    //是否免签(大公务) 
                    if (provInfo.IsVisaExemptionLarge) scInfo.IsVisaExemptionLarge = true;

                    //签证费用(大公务)
                    if (provInfo.LargeVisaPrice > 0.00M) scInfo.LargeVisaPrice = provInfo.LargeVisaPrice;

                    //代办费(大公务)
                    if (provInfo.LargeAgencyFee > 0.00M) scInfo.LargeAgencyFee = provInfo.LargeAgencyFee;

                    //是否免签(小公务) 
                    if (provInfo.IsVisaExemptionSmall) scInfo.IsVisaExemptionSmall = true;

                    //签证费用(小公务)
                    if (provInfo.SmallVisaPrice > 0.00M) scInfo.SmallVisaPrice = provInfo.SmallVisaPrice;

                    //代办费(小公务)
                    if (provInfo.SmallAgencyFee > 0.00M) scInfo.SmallAgencyFee = provInfo.SmallAgencyFee;

                    //外办费用(普通)
                    if (provInfo.NormExtFee > 0.00M) scInfo.NormExtFee = provInfo.NormExtFee;

                    //外办费用(加急)
                    if (provInfo.UrgExtFee > 0.00M) scInfo.UrgExtFee = provInfo.UrgExtFee;

                    //签证是否加急
                    if (provInfo.IsUrgent) scInfo.IsUrgent = true;

                    //加急费用
                    if (provInfo.UrgentPrice > 0.00M) scInfo.UrgentPrice = provInfo.UrgentPrice;

                    //加急费用描述 
                    if (!string.IsNullOrEmpty(provInfo.UrgentPriceDesc)) scInfo.UrgentPriceDesc = provInfo.UrgentPriceDesc;

                    //备注 
                    if (!string.IsNullOrEmpty(provInfo.Remark)) scInfo.Remark = provInfo.Remark;

                    resultList.Add(scInfo);
                }
            }

            return resultList;
        }


        /// <summary>
        /// 出入境费用签证费用提示
        /// </summary>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<Result> EnterExitCostVisaTips(int diId)
        {
            if (diId < 1) return _result = new Result() { Code = -1, Msg = "请传入有效的DiId参数" };

            var groupInfo = await _sqlSugar.Queryable<Grp_DelegationInfo>()
               .FirstAsync(it => it.IsDel == 0 && it.Id == diId);

            if (groupInfo == null)
                return _result = new Result() { Code = -1, Msg = "团组信息不存在!" };

            var visaData = await _sqlSugar.Queryable<Grp_VisaFeeInfo>()
                .Where(it => it.IsDel == 0 && it.DiId == diId)
                .ToListAsync();

            var visaFeeIds = visaData.Select(x => x.Id).ToList();
            var visaCountryNames = await _sqlSugar.Queryable<Res_CountryFeeCost>()
                .Where(it => visaFeeIds.Contains(it.Id))
                .Select(it => it.VisaCountry)
                .ToListAsync();

            var visaCountryDatas = await VisaInfoByCountryAndCityId(visaCountryNames, groupInfo.CityId);

            //if (visaCountryDatas.Count == 0)
            //    return _result = new Result() { Code = -1, Msg = "签证费用信息不存在!" };

            var datas = new List<dynamic>();
            string remark = "";
            decimal feeTotal = 0.00M;

            visaData = visaData.Where(it => it.IsChecked == 1).ToList();
            var visaData1 = visaData.GroupBy(it => it.CountryVisaFeeId);

            //费用报价系数
            decimal priceCoeff = 1.00M;
            foreach (var kvp in visaData1)
            {
                var countryData = visaCountryDatas.Find(it => it.Id == kvp.Key);
                decimal _otherFee = kvp.FirstOrDefault()?.OtherFee * priceCoeff ?? 0;
                decimal _visaFee = 0.00M;
                decimal visaFeeTotal = 0.00M;
                decimal _agencyFee = 0.00M;
                decimal _GrandBusinessAgencyFee = 0.00M;
                decimal _PettyBusinessAgencyFee = 0.00M;

                string remark1 = "";
                foreach (var item in kvp.ToList())
                {
                    if (item.OBType == 1)
                    {
                        _visaFee = Convert.ToDecimal(countryData?.LargeVisaPrice ?? 0.00M) * priceCoeff;
                        remark1 = $"签证费:{_visaFee:#0.00}元、";
                        if (item.AgencyFee > 0)
                        {
                            remark1 += $@"大公务代办费:{item.AgencyFee:#0.00}元、";
                            _agencyFee += item.AgencyFee;
                            _GrandBusinessAgencyFee = item.AgencyFee * priceCoeff;
                        }
                    }
                    else if (item.OBType == 2)
                    {
                        _visaFee = Convert.ToDecimal(countryData?.SmallVisaPrice ?? 0.00M) * priceCoeff; 
                        remark1 = $"签证费:{_visaFee:#0.00}元、";
                        if (item.AgencyFee > 0)
                        {
                            remark1 += $@"小公务代办费:{item.AgencyFee:#0.00}元、";
                            _agencyFee += item.AgencyFee;
                            _PettyBusinessAgencyFee = item.AgencyFee * priceCoeff;
                        }
                    }
                    //_visaFee += _otherFee;
                }

                if (remark1.Length > 0) remark1 = remark1.Substring(0, remark1.Length - 1);

                if (_otherFee > 0) remark1 += $@"其他费用:{_otherFee:#0.00}元";

                visaFeeTotal += (_GrandBusinessAgencyFee + _PettyBusinessAgencyFee);
                remark += $@"{countryData?.CountryName ?? ""}:签证总费用:{visaFeeTotal}元/人 其中（{remark1}）;";

                feeTotal += visaFeeTotal;

                datas.Add(new
                {
                    Country = countryData?.CountryName ?? "",
                    visaFeeTotal,
                    VisaFee = _visaFee,
                    GrandBusinessAgencyFee = _GrandBusinessAgencyFee,
                    PettyBusinessAgencyFee = _PettyBusinessAgencyFee,
                    OtherFee = _otherFee
                });
            }

            _result.Code = 0;
            _result.Data = new
            {
                feeTotal,
                data = datas,
                remark
            };

            return _result;
        }

        /// <summary>
        /// 出入境费用草稿签证费用提示
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<(decimal, string)> EnterExitCostDraftVisaTips(string[] countrys)
        {
            var visaCountryDatas = await _sqlSugar.Queryable<Res_CountryFeeCost>().Where(it => countrys.Contains(it.VisaCountry)).ToListAsync();

            string remark = "";
            decimal feeTotal = 0.00M;

            //费用报价系数
            decimal priceCoeff = 1.00M;
            foreach (var kvp in visaCountryDatas)
            {
                var countryName = kvp?.VisaCountry ?? "";
                //免签
                if (kvp.IsVisaExemption == 0)
                {
                    remark += $@"{countryName}:免签;";
                    continue;
                }

                decimal visaFee = Convert.ToDecimal(kvp?.VisaPrice ?? 0.00M) * priceCoeff;

                decimal gbAgencyFee = kvp.GrandBusinessAgencyFee;
                decimal pbAgencyFee = kvp.PettyBusinessAgencyFee;

                string remark1 = $"签证费:{visaFee:#0.00}元、";
                if (gbAgencyFee > 0.00M)
                {
                    remark1 += $@"大公务代办费:{gbAgencyFee:#0.00}元、";
                    gbAgencyFee *= priceCoeff;
                }

                if (pbAgencyFee > 0.00M)
                {
                    remark1 += $@"小公务代办费:{pbAgencyFee:#0.00}元、";
                    pbAgencyFee *= priceCoeff;
                }

                if (remark1.Length > 0) remark1 = remark1.Substring(0, remark1.Length - 1);
                decimal visaFeeTotal = visaFee + gbAgencyFee + pbAgencyFee;

                remark += $@"{countryName}:签证总费用:{visaFeeTotal}元/人 其中（{remark1}）;";

                feeTotal += visaFeeTotal;

            }

            return (feeTotal, remark);
        }
    }
}