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 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>
        /// 出入境费用签证费用提示
        /// </summary>
        /// <param name="portType"></param>
        /// <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 visaData = await _sqlSugar.Queryable<Grp_VisaFeeInfo>().Where(it => it.IsDel == 0 && it.DiId == diId).ToListAsync();
            List<int> ids = visaData.Select (it => it.CountryVisaFeeId).ToList();
            var visaCountryDatas = await _sqlSugar.Queryable<Res_CountryFeeCost>().Where(it => ids.Contains(it.Id)).ToListAsync();

            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 = Convert.ToDecimal(countryData?.VisaPrice ?? 0.00M) * priceCoeff;
                decimal visaFeeTotal = _visaFee + _otherFee;
                decimal _agencyFee = 0;
                decimal _GrandBusinessAgencyFee = 0;
                decimal _PettyBusinessAgencyFee = 0;
                
                string remark1 = $"签证费:{_visaFee:#0.00}元、";
                foreach (var item in kvp.ToList())
                {
                    if (item.OBType == 1)
                    {
                        if (item.AgencyFee > 0)
                        {
                            remark1 += $@"大公务代办费:{item.AgencyFee:#0.00}元、";
                            _agencyFee += item.AgencyFee;
                            _GrandBusinessAgencyFee = item.AgencyFee * priceCoeff;
                        }
                    }
                    else if (item.OBType == 1)
                    {
                        if (item.AgencyFee > 0)
                        {
                            remark1 += $@"小公务代办费:{item.AgencyFee.ToString("#0.00")}元、";
                            _agencyFee += item.AgencyFee;
                            _PettyBusinessAgencyFee = item.AgencyFee * priceCoeff;
                        }
                    }
                }
                if (remark1.Length > 0) remark1 = remark1.Substring(0, remark1.Length - 1);

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

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

                feeTotal += visaFeeTotal;

                datas.Add(new
                {
                    Country = countryData?.VisaCountry ?? "",
                    visaFeeTotal,
                    VisaFee = countryData?.VisaPrice ?? 0.00M,
                    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);
        }
    }
}