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 { /// /// 团组签证费用详情 info /// 仓储 /// public class VisaFeeInfoRepository:BaseRepository { 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; } /// /// Init /// /// /// /// public async Task Init() { var data = await _sqlSugar.Queryable().Where(it => it.IsDel == 0).ToListAsync(); _result.Code = 0; _result.Data = data; _result.Msg = "操作成功!"; return _result; } /// /// List /// /// /// /// public async Task 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(sql) .AddParameters(new { diId }) .ToListAsync(); if (data.Count == 0) { var groupInfo = await _sqlSugar.Queryable() .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() .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() .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>(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 = "操作成功!" }; } /// /// 补齐默认行 /// /// /// private void AddDefaultRows(List list, int totalRows) { int defaultRow = totalRows - list.Count; for (int i = 0; i < defaultRow; i++) { list.Add(new VisaFeeInfosView { Id = 0, OBType = 1 }); } } /// /// List /// /// /// public async Task 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(); 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().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; } /// /// 出入境费用签证费用提示 /// /// /// /// public async Task EnterExitCostVisaTips(int diId) { if (diId < 1) return _result = new Result() { Code = -1, Msg = "请传入有效的DiId参数" }; var visaData = await _sqlSugar.Queryable().Where(it => it.IsDel == 0 && it.DiId == diId).ToListAsync(); List ids = visaData.Select (it => it.CountryVisaFeeId).ToList(); var visaCountryDatas = await _sqlSugar.Queryable().Where(it => ids.Contains(it.Id)).ToListAsync(); var datas = new List(); 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; } /// /// 出入境费用草稿签证费用提示 /// /// /// /// public async Task<(decimal,string)> EnterExitCostDraftVisaTips(string[] countrys) { var visaCountryDatas = await _sqlSugar.Queryable().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); } } }