using AutoMapper; using MathNet.Numerics.Statistics.Mcmc; using NPOI.SS.Formula.Functions; using OASystem.Domain; using OASystem.Domain.Dtos.Groups; using OASystem.Domain.Entities.Groups; using OASystem.Domain.Entities.Resource; using OASystem.Domain.ViewModels.Groups; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace OASystem.Infrastructure.Repositories.Groups { public class InvitationOfficialActivitiesRepository : BaseRepository { private readonly IMapper _mapper; public InvitationOfficialActivitiesRepository(SqlSugarClient sqlSugar, IMapper mapper) : base(sqlSugar) { this._mapper = mapper; } /// /// 根据商邀费用Id查询C表和商邀资料及费用表 /// /// /// public async Task InvitationOfficialActivitiesById(InvitationOfficialActivitiesByIdDto dto) { Result result = new Result() { Code = -2, Msg = "程序错误" }; try { Grp_InvitationOfficialActivities grp_Invitation = _sqlSugar.Queryable().First(a => a.Id == dto.Id && a.IsDel == 0); Grp_CreditCardPayment grp_CreditCard = _sqlSugar.Queryable().First(a => a.CId == dto.Id && a.IsDel == 0); Res_InvitationOfficialActivityData res_InvitationOfficialActivityData = new Res_InvitationOfficialActivityData(); if (grp_Invitation!=null) { res_InvitationOfficialActivityData = _sqlSugar.Queryable().First(a =>a.Country==grp_Invitation.InviterArea && a.UnitName==grp_Invitation.Inviter && a.IsDel == 0); } var data = new { _Invitation = grp_Invitation, _InvitationData = res_InvitationOfficialActivityData, _CreditCard = grp_CreditCard, }; result = new Result() { Code = 0, Msg = "查询成功！", Data = data }; } catch (Exception) { result = new Result() { Code = -2, Msg = "程序错误" }; throw; } return result; } /// /// 根据团组id查询商邀费用数据 /// /// /// public async Task InvitationOfficialActivitiesList(InvitationOfficialActivitiesListDto dto) { Result result = new Result() { Code = -2, Msg = "未知错误" }; try { string sqlWhere = string.Empty; if (!string.IsNullOrWhiteSpace(dto.Inviter)) { sqlWhere += string.Format(@" And Inviter={0}", dto.Inviter); } if (!string.IsNullOrWhiteSpace(dto.StartInviteTime) && !string.IsNullOrWhiteSpace(dto.EndInviteTime)) { sqlWhere += string.Format(@" And i.InviteTime between '{0}' and '{1}'", dto.StartInviteTime, dto.EndInviteTime); } sqlWhere += string.Format(@" And i.DiId={0} And i.IsDel={1}", dto.DiId, 0); if (!string.IsNullOrEmpty(sqlWhere.Trim())) { Regex r = new Regex("And"); sqlWhere = r.Replace(sqlWhere, "Where", 1); } string sql = string.Format(@"select Id,DiId,InviterArea,Inviter,InviteTime,InviteCosts,(select name from Sys_SetData where id=i.Currency and IsDel={0}) as Currency,IsGoOfficaiaBussiness,(select IsAuditGM from Grp_CreditCardPayment where CTable=81 and CId=i.Id and IsDel={0}) as 'isAudit',Attachment from Grp_InvitationOfficialActivities i {1}", 0, sqlWhere); List _DecreasePayments = await _sqlSugar.SqlQueryable(sql).ToListAsync(); if (_DecreasePayments.Count != 0) { result = new Result() { Code = 0, Msg = "查询成功！", Data = _DecreasePayments }; } else { result = new Result() { Code = 0, Msg = "暂无数据！", Data = _DecreasePayments }; } } catch (Exception ex) { result = new Result() { Code = -2, Msg = "未知错误" }; } return result; } /// /// 商邀费用操作（Status:1.新增,2.修改） /// /// /// public async Task OpInvitationOfficialActivities(OpInvitationOfficialActivitiesDto dto) { Result result = new Result() { Code = -2, Msg = "程序错误！" }; BeginTran(); try { int id = 0; Grp_InvitationOfficialActivities grp_Invitation = _mapper.Map(dto); if (dto.Status == 1)//添加 { string selectSql = string.Format(@"select * from Grp_InvitationOfficialActivities where InviterArea='{0}' and Inviter='{1}' and DiId={2} and IsDel={3}" , dto.InviterArea, dto.Inviter, dto.DiId, 0); var _InvitationOfficialActivities = await _sqlSugar.SqlQueryable(selectSql).FirstAsync();//查询是否存在 if (_InvitationOfficialActivities != null) { return result = new Result() { Code = -1, Msg = "该客户已存在，请勿重复添加！" }; } else { id = await AddAsyncReturnId(grp_Invitation); if (id != 0)//修改或添加商邀资料 { Res_InvitationOfficialActivityData res_InvitationData = _mapper.Map(dto); res_InvitationData.Remark = ""; int DataID = 0; if (dto.DataId == 0) { DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync(); if (DataID != 0) { result = new Result() { Code = 0, Msg = "添加成功！" }; } else { RollbackTran(); result = new Result() { Code = -1, Msg = "添加失败！" }; } } else { res_InvitationData.Id = dto.DataId; DataID = await _sqlSugar.Updateable().Where(b => b.Id == res_InvitationData.Id).SetColumns(b => new Res_InvitationOfficialActivityData { Country = res_InvitationData.Country, UnitName = res_InvitationData.UnitName, Address = res_InvitationData.Address, Contact = res_InvitationData.Contact, Job = res_InvitationData.Job, Tel = res_InvitationData.Tel, Email = res_InvitationData.Email, OtherInformation = res_InvitationData.OtherInformation, Fax = res_InvitationData.Fax }).ExecuteCommandAsync(); } if (DataID != 0)//添加C表 { Grp_CreditCardPayment C = new Grp_CreditCardPayment(); C.PayDId = dto.PayDId; C.ConsumptionPatterns = ""; C.ConsumptionDate = ""; C.CTDId = 0; C.BankNo = ""; C.CardholderName = ""; C.PayMoney = dto.InviteCosts + dto.SendCost; C.PaymentCurrency = dto.Currency; //当天汇率 //if (!string.IsNullOrEmpty(hfRate.Value)) // C.DayRate = hfRate.Value; //else //C.DayRate = ""; C.CompanyBankNo = ""; C.OtherBankName = ""; C.OtherSideNo = ""; C.OtherSideName = ""; C.Remark = ""; C.CreateUserId = dto.CreateUserId; C.MFOperator = ""; C.MFOperatorDate = ""; C.IsAuditDM = 0; C.AuditDMOperate = 0; C.AuditDMDate = ""; C.IsAuditMF = 0; C.AuditMFOperate = 0; C.AuditMFDate = ""; C.IsAuditGM = 0; C.AuditGMOperate = 21; C.AuditGMDate = ""; C.IsPay = 0; C.DIId = dto.DiId; C.CId = id; C.CTable = 81; C.PayPercentage = 0; C.PayThenMoney = 0; C.PayPercentageOld = 0; C.PayThenMoneyOld = 0; C.UpdateDate = ""; C.Payee = dto.Payee; C.OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer; C.ExceedBudget = 0; //C.RMBPrice = 0.00f; //设置该团组的汇率 Grp_TeamRate _TeamRate = _sqlSugar.Queryable().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 81); if (_TeamRate != null) { if (dto.Currency == 49) { C.DayRate = _TeamRate.RateU; C.RMBPrice = C.PayMoney * Convert.ToDecimal(_TeamRate.RateU); //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateU); } else if (dto.Currency == 51) { C.DayRate = _TeamRate.RateE; C.RMBPrice = C.PayMoney * Convert.ToDecimal(_TeamRate.RateE); //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateE); } else { C.DayRate = "1"; C.RMBPrice = C.PayMoney; } } else { C.DayRate = "1"; C.RMBPrice = C.PayMoney; } int cId = await _sqlSugar.Insertable(C).ExecuteReturnIdentityAsync(); if (cId != 0) { result = new Result() { Code = 0, Msg = "添加成功！" }; } else { RollbackTran(); result = new Result() { Code = -1, Msg = "添加失败！" }; } } } } } else//修改 { bool res = await UpdateAsync(a => a.Id == grp_Invitation.Id, a => new Grp_InvitationOfficialActivities { DiId = dto.DiId, InviterArea = dto.InviterArea, Inviter = dto.Inviter, InviteTime = dto.InviteTime, Attachment = dto.Attachment, InviteCosts = dto.InviteCosts, Currency = dto.Currency, SendCost = dto.SendCost, IsGoOfficaiaBussiness = dto.IsGoOfficaiaBussiness, Remark = dto.Remark, }); if (res) { Res_InvitationOfficialActivityData res_InvitationData = _mapper.Map(dto); res_InvitationData.Remark = ""; int DataID = 0; if (dto.DataId == 0)///添加或修改商邀资料 { DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync(); if (DataID != 0) { result = new Result() { Code = 0, Msg = "添加成功！" }; } else { RollbackTran(); result = new Result() { Code = -1, Msg = "添加失败！" }; } } else { res_InvitationData.Id = dto.DataId; DataID = await _sqlSugar.Updateable().Where(b => b.Id == res_InvitationData.Id).SetColumns(b => new Res_InvitationOfficialActivityData { Country = res_InvitationData.Country, UnitName = res_InvitationData.UnitName, Address = res_InvitationData.Address, Contact = res_InvitationData.Contact, Job = res_InvitationData.Job, Tel = res_InvitationData.Tel, Email = res_InvitationData.Email, OtherInformation = res_InvitationData.OtherInformation, Fax = res_InvitationData.Fax }).ExecuteCommandAsync(); } if (DataID != 0)//修改C表 { Grp_CreditCardPayment grp_CreditCardPayment = _sqlSugar.Queryable().First(a => a.CId ==grp_Invitation.Id && a.IsDel == 0); if (grp_CreditCardPayment != null) { grp_CreditCardPayment.PayMoney = dto.InviteCosts + dto.SendCost; Grp_TeamRate _TeamRate = _sqlSugar.Queryable().First(a => a.DiId == grp_Invitation.DiId && a.IsDel == 0 && a.CTable == 81); if (_TeamRate != null) { if (grp_Invitation.Currency == 49) { grp_CreditCardPayment.DayRate = _TeamRate.RateU; grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney * Convert.ToDecimal(_TeamRate.RateU); //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateU); } else if (grp_Invitation.Currency == 51) { grp_CreditCardPayment.DayRate = _TeamRate.RateE; grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney * Convert.ToDecimal(_TeamRate.RateE); //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateE); } else { grp_CreditCardPayment.DayRate = "1"; grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney; } } else { grp_CreditCardPayment.DayRate = "1"; grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney; } int CTable = await _sqlSugar.Updateable().Where(a => a.Id == grp_CreditCardPayment.Id).SetColumns(a => new Grp_CreditCardPayment { PayDId = dto.PayDId, PayMoney = grp_CreditCardPayment.PayMoney, PaymentCurrency = grp_Invitation.Currency, Payee = dto.Payee, OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer, DayRate = grp_CreditCardPayment.DayRate, RMBPrice = grp_CreditCardPayment.RMBPrice, }).ExecuteCommandAsync(); if (CTable != 0) { result = new Result() { Code = 0, Msg = "修改成功！" }; } else { result = new Result() { Code = -1, Msg = "修改失败！" }; RollbackTran(); } } else { RollbackTran(); result = new Result() { Code = -1, Msg = "修改失败！" }; } } else { RollbackTran(); result = new Result() { Code = -1, Msg = "修改失败！" }; } } else { RollbackTran(); result = new Result() { Code = -1, Msg = "修改失败！" }; } } CommitTran(); } catch (Exception ex) { result = new Result() { Code = -2, Msg = "程序错误！" }; throw; } return result; } } }