using AutoMapper;
using MathNet.Numerics.Statistics.Mcmc;
using Newtonsoft.Json;
using NPOI.SS.Formula.Eval;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.QiYeWeChat;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using static NPOI.HSSF.Util.HSSFColor;
namespace OASystem.Infrastructure.Repositories.Groups
{
public class InvitationOfficialActivitiesRepository : BaseRepository<Grp_InvitationOfficialActivities, InvitationOfficialActivitiesListDto>
{
private readonly IMapper _mapper;
public InvitationOfficialActivitiesRepository(SqlSugarClient sqlSugar, IMapper mapper) :
base(sqlSugar)
{
this._mapper = mapper;
}
/// <summary>
/// 根据商邀费用Id查询C表和商邀资料及费用表
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> InvitationOfficialActivitiesById(InvitationOfficialActivitiesByIdDto dto)
{
Result result = new Result() { Code = -2, Msg = "程序错误" };
try
{
var grp_Invitation = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>().First(a => a.Id == dto.Id && a.IsDel == 0);
Grp_CreditCardPayment grp_CreditCard = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable==81);
Res_InvitationOfficialActivityData res_InvitationOfficialActivityData = new Res_InvitationOfficialActivityData();
if (grp_Invitation!=null)
{
var exists = new EncryptionMatching
{
Inviter = grp_Invitation.Inviter,
InviterArea = grp_Invitation.InviterArea,
};
EncryptionProcessor.EncryptProperties(exists);
if (grp_Invitation.DataId > 0)
{
res_InvitationOfficialActivityData = _sqlSugar.Queryable<Res_InvitationOfficialActivityData>()
.First(a => a.Id == grp_Invitation.DataId && a.IsDel == 0);
}
else
{
res_InvitationOfficialActivityData = _sqlSugar.Queryable<Res_InvitationOfficialActivityData>()
.First(a => a.Country == exists.InviterArea && a.UnitName == exists.Inviter && a.IsDel == 0);
}
EncryptionProcessor.DecryptProperties(res_InvitationOfficialActivityData);
if (dto.PortType == 1)
{
var grp_Invitation1 = _mapper.Map<Grp_InvitationOfficialActivitiesView>(grp_Invitation);
var data = new
{
_Invitation = grp_Invitation1,
_InvitationData = res_InvitationOfficialActivityData,
_CreditCard = grp_CreditCard,
};
result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
}
else
{
var invitation = _mapper.Map<InvitationOfficialActivitiesByIdView>(grp_Invitation);
res_InvitationOfficialActivityData ??= new Res_InvitationOfficialActivityData();
invitation.ConsumptionPatterns = grp_CreditCard.ConsumptionPatterns;
invitation.ConsumptionDate = grp_CreditCard.ConsumptionDate;
invitation.PayDId = grp_CreditCard.PayDId;
invitation.ConsumptionPatterns = grp_CreditCard.ConsumptionPatterns;
invitation.ConsumptionDate = grp_CreditCard.ConsumptionDate;
invitation.BankNo = grp_CreditCard.BankNo;
invitation.CardholderName = grp_CreditCard.CardholderName;
invitation.CompanyBankNo = grp_CreditCard.CompanyBankNo;
invitation.OtherBankName = grp_CreditCard.OtherBankName;
invitation.OtherSideNo = grp_CreditCard.OtherSideNo;
invitation.OtherSideName = grp_CreditCard.OtherSideName;
invitation.PayMoney = grp_CreditCard.PayMoney;
invitation.PaymentCurrency = grp_CreditCard.PaymentCurrency;
invitation.OrbitalPrivateTransfer = grp_CreditCard.OrbitalPrivateTransfer;
invitation.Payee = grp_CreditCard.Payee;
invitation.Address = res_InvitationOfficialActivityData.Address;
invitation.Contact = res_InvitationOfficialActivityData.Contact;
invitation.Job = res_InvitationOfficialActivityData.Job;
invitation.Tel = res_InvitationOfficialActivityData.Tel;
invitation.Email = res_InvitationOfficialActivityData.Email;
invitation.Fax = res_InvitationOfficialActivityData.Fax;
invitation.OtherInformation = res_InvitationOfficialActivityData.Remark;
invitation.CTDId = grp_CreditCard.CTDId;
result = new Result() { Code = 0, Msg = "查询成功！", Data = invitation };
}
}
else
{
if (dto.PortType == 1)
{
var data = new
{
_Invitation = grp_Invitation,
_InvitationData = res_InvitationOfficialActivityData,
_CreditCard = grp_CreditCard,
};
result = new Result() { Code = 0, Msg = "暂无数据！", Data = data };
}
else
{
InvitationOfficialActivitiesByIdView invitation = _mapper.Map<InvitationOfficialActivitiesByIdView>(grp_Invitation);
result = new Result() { Code = 0, Msg = "暂无数据！", Data = invitation };
}
}
}
catch (Exception)
{
result = new Result() { Code = -2, Msg = "程序错误" };
throw;
}
return result;
}
/// <summary>
/// 根据团组id查询商邀费用数据
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> InvitationOfficialActivitiesList(InvitationOfficialActivitiesListDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
ListViewBase<InvitationOfficialActivitiesView> rst = new ListViewBase<InvitationOfficialActivitiesView>() { ReceiveDt=DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") };
string sqlWhere = string.Empty;
if (!string.IsNullOrWhiteSpace(dto.Inviter))
{
sqlWhere += string.Format(@" And Inviter like '%{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);
string UserId = "";
List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a => a.DIId == dto.DiId && a.IsDel == 0 && a.CTId == 81).ToList();
foreach (Grp_GroupsTaskAssignment gta in gtaUIdList)
UserId += gta.UId + ",";
if (!string.IsNullOrWhiteSpace(UserId))
{
UserId = UserId.Substring(0, UserId.Length - 1);
}
else
{
UserId = "0";
}
sqlWhere += string.Format(@" And i.CreateUserId in ({0})", UserId);
//雷怡 2024-05-08 16:19 更改默认值 IsPaySign = -1
if (dto.IsPaySign != -1) {
sqlWhere += string.Format(@" And ccp.IsPay = {0} ", dto.IsPaySign);
}
if (!string.IsNullOrEmpty(sqlWhere.Trim()))
{
Regex r = new Regex("And");
sqlWhere = r.Replace(sqlWhere, "Where", 1);
}
//10011
//string sql = string.Format(@"Select Id,DiId,InviterArea,Inviter,InviteTime,
// InviteCost,(Select Name From Sys_SetData Where id=i.InviteCurrency And IsDel=0) As InviteCurrency,
// SendCost,(Select Name From Sys_SetData Where id=i.SendCurrency And IsDel=0) As SendCurrency,
// EventsCost,(Select Name From Sys_SetData Where id=i.EventsCurrency And IsDel=0) As EventsCurrency,
// TranslateCost,(Select Name From Sys_SetData Where id=i.TranslateCurrency And IsDel=0) As TranslateCurrency,
// IsGoOfficaiaBussiness,
// (Select PayMoney From Grp_CreditCardPayment Where CTable=81 And CId=i.Id And IsDel=0) As PayMoney,
// (Select (Select Name From Sys_SetData Where Id =ccp.PaymentCurrency And IsDel=0 ) As PaymentCurrency
// From Grp_CreditCardPayment ccp Where CTable=81 And CId=i.Id And IsDel=0) As PaymentCurrency,
// (Select IsAuditGM From Grp_CreditCardPayment Where CTable=81 And CId=i.Id And IsDel=0) As IsAudit,
// Attachment From Grp_InvitationOfficialActivities i {0} Order By i.id Desc", sqlWhere);
string sql = string.Format(@" Select i.Id,i.DiId,i.InviterArea,i.Inviter,i.InviteTime,i.InviteCost,sd1.[Name] as InviteCurrency
,i.SendCost,sd2.[Name] as SendCurrency,i.EventsCost,sd3.[Name] as EventsCurrency,i.TranslateCost,sd4.[Name] as TranslateCurrency
,i.IsGoOfficaiaBussiness,ccp.PayMoney,sd5.[Name] as PaymentCurrency,IsAuditGM as IsAudit,i.Attachment,ccp.IsPay
From Grp_InvitationOfficialActivities as i With(Nolock) Inner Join Grp_CreditCardPayment as ccp With(Nolock) On i.Id = ccp.CId
Left Join Sys_SetData as sd1 On i.InviteCurrency = sd1.Id
Left Join Sys_SetData as sd2 On i.SendCurrency = sd2.Id
Left Join Sys_SetData as sd3 On i.EventsCurrency = sd3.Id
Left Join Sys_SetData as sd4 On i.TranslateCurrency = sd4.Id
Left Join Sys_SetData as sd5 On ccp.PaymentCurrency = sd5.Id
{0} And ccp.CTable = 81
Order By i.id Desc ", sqlWhere);
List<InvitationOfficialActivitiesView> _DecreasePayments = await _sqlSugar.SqlQueryable<InvitationOfficialActivitiesView>(sql).ToListAsync();
if (_DecreasePayments.Count != 0)
{
if (dto.PageIndex != 0 && dto.PageSize != 0)
{
int count = _DecreasePayments.Count;
float totalPage = (float)count / dto.PageSize;//总页数
if (totalPage == 0) totalPage = 1;
else totalPage = (int)Math.Ceiling((double)totalPage);
List<InvitationOfficialActivitiesView> invitationOfficialActivities = new List<InvitationOfficialActivitiesView>();
for (int i = 0; i < dto.PageSize; i++)
{
var RowIndex = i + (dto.PageIndex - 1) * dto.PageSize;
if (RowIndex < _DecreasePayments.Count)
{
invitationOfficialActivities.Add(_DecreasePayments[RowIndex]);
}
else
{
break;
}
}
rst.DataList = invitationOfficialActivities;
rst.DataCount = count;
rst.CurrPageIndex = dto.PageIndex;
rst.CurrPageSize = dto.PageSize;
rst.ReturnDt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };
}
else
{
return result = new Result() { Code = 0, Msg = "查询成功！", Data = _DecreasePayments };
}
}
else
{
if (dto.PageIndex != 0 && dto.PageSize != 0)
{
rst.DataList = _DecreasePayments;
rst.DataCount = _DecreasePayments.Count;
rst.CurrPageIndex = dto.PageIndex;
rst.CurrPageSize = dto.PageSize;
rst.ReturnDt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
return result = new Result() { Code = 0, Msg = "暂无数据！", Data = rst };
}
else
{
return result = new Result() { Code = 0, Msg = "暂无数据！", Data = _DecreasePayments };
}
}
}
catch (Exception ex)
{
return result = new Result() { Code = -2, Msg = ex.Message };
}
}
/// <summary>
/// Info Page Basic Data
/// </summary>
/// <returns></returns>
public async Task<Result> _InitBasicData()
{
Result result = new Result() { Code = -2, Msg = "程序错误!" };
var setDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
//支付方式
var _payTypeDatas = setDatas.Where(it => it.STid == 14).Select(it => new { Id = it.Id, Name = it.Name, Remark = it.Remark }).ToList();
///转账标识
var _costIdentificationDatas = new List<dynamic>()
{
new { Id = 0,Name = "公转" },
new { Id = 1,Name = "私转" }
};
//卡类型
var _cardTypeDatas = setDatas.Where(it => it.STid == 15).Select(it => new SetDataCurrencyInfoView { Id = it.Id, Name = it.Name, Remark = it.Remark }).ToList();
var data = new {
PayTypeData = _payTypeDatas,
CostIdentificationData = _costIdentificationDatas,
CardTypeData = _cardTypeDatas
};
result.Code = 0;
result.Data = data;
return result;
}
/// <summary>
/// 商邀费用操作（Status:1.新增,2.修改）
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> OpInvitationOfficialActivities(OpInvitationOfficialActivitiesDto dto, Func<int, int, int, Task<Result>> fn)
{
Result result = new Result() { Code = -2, Msg = "程序错误！" };
BeginTran();
string filePath = string.Empty;
if (dto.Attachment.Length > 0)
{
filePath = JsonConvert.SerializeObject(dto.Attachment);
}
int id = 0;
Grp_InvitationOfficialActivities grp_Invitation = _mapper.Map<Grp_InvitationOfficialActivities>(dto);
grp_Invitation.Attachment = filePath;
if (dto.Status == 1)//添加
{
#region 添加资料
Res_InvitationOfficialActivityData res_InvitationData = _mapper.Map<Res_InvitationOfficialActivityData>(dto);
res_InvitationData.Country = dto.InviterArea;
res_InvitationData.UnitName = dto.Inviter;
res_InvitationData.Delegation = dto.DiId.ToString();
res_InvitationData.Remark = dto.OtherInformation;
EncryptionProcessor.EncryptProperties(res_InvitationData);
int DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync();
grp_Invitation.DataId = DataID;
#endregion
id = await AddAsyncReturnId(grp_Invitation);
#region C表数据添加
Grp_CreditCardPayment C = new Grp_CreditCardPayment();
C.PayDId = dto.PayDId;
if (C.PayDId == 72) C.IsPay = 1;
else C.IsPay = 0;
C.ConsumptionPatterns = dto.ConsumptionPatterns;
C.ConsumptionDate = dto.ConsumptionDate;
C.CTDId = dto.CTDId;
C.BankNo = dto.BankNo;
C.CardholderName = dto.CardholderName;
C.PayMoney = dto.PayMoney;
C.PaymentCurrency = dto.PaymentCurrency;
C.CompanyBankNo = dto.CompanyBankNo;
C.OtherBankName = dto.OtherBankName;
C.OtherSideNo = dto.OtherSideNo;
C.OtherSideName = dto.OtherSideName;
C.Remark = "";
C.CreateUserId = dto.CreateUserId;
C.MFOperator = 0;
C.MFOperatorDate = "";
C.IsAuditDM = 0;
C.AuditDMOperate = 0;
C.AuditDMDate = "";
C.IsAuditMF = 0;
C.AuditMFOperate = 0;
C.AuditMFDate = "";
C.IsAuditGM = 0;
C.AuditGMOperate = 0;
C.AuditGMDate = "";
C.DIId = dto.DiId;
C.CId = id;
C.CTable = 81;
C.PayPercentage = 100;
C.PayThenMoney = 0;
C.PayPercentageOld = 0;
C.PayThenMoneyOld = 0;
C.UpdateDate = "";
C.Payee = dto.Payee;
C.OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer;
C.ExceedBudget = 0;
//获取新汇率 int diId,int CId, int currencyId
var rate = await fn(dto.DiId, 81, dto.InviteCurrency);
if (rate.Code == 0)
{
var rateInfo = (rate.Data as CurrencyInfo);
if (rateInfo is not null)
{
C.DayRate = rateInfo.Rate;
C.RMBPrice = rateInfo.Rate * C.PayMoney;
}
else
{
C.DayRate = 1;
C.RMBPrice = C.PayMoney;
}
}
int cId = await _sqlSugar.Insertable(C).ExecuteReturnIdentityAsync();
#endregion
var data = new { ccpId = cId, sign = 1 };
if (cId != 0)
{
result = new Result() { Code = 0, Msg = "添加成功！", Data = data };
}
else
{
RollbackTran();
result = new Result() { Code = -1, Msg = "添加失败！" };
}
}
else//修改
{
var thisTime = new DateTime();
DateTime.TryParse(dto.InviteTime, out thisTime);
bool res = await UpdateAsync(a => a.Id == grp_Invitation.Id, a => new Grp_InvitationOfficialActivities
{
DiId = dto.DiId,
InviterArea = dto.InviterArea,
Inviter = dto.Inviter,
InviteTime = thisTime,
Attachment = filePath,
InviteCost = dto.InviteCost,
InviteCurrency = dto.InviteCurrency,
SendCost = dto.SendCost,
SendCurrency = dto.SendCurrency,
EventsCost = dto.EventsCost,
EventsCurrency = dto.EventsCurrency,
TranslateCost = dto.TranslateCost,
TranslateCurrency = dto.TranslateCurrency,
IsGoOfficaiaBussiness = dto.IsGoOfficaiaBussiness,
Remark = dto.Remark,
});
if (res)
{
Res_InvitationOfficialActivityData res_InvitationData = _mapper.Map<Res_InvitationOfficialActivityData>(dto);
res_InvitationData.Country = dto.InviterArea;
res_InvitationData.UnitName = dto.Inviter;
res_InvitationData.Delegation = dto.DiId.ToString();
res_InvitationData.Remark = dto.OtherInformation;
EncryptionProcessor.EncryptProperties(res_InvitationData);
Res_InvitationOfficialActivityData ifNullUp = null;
var InvitationOfficialActivities_DB = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>()
.First(x => x.Id == grp_Invitation.Id && x.IsDel == 0);
if (InvitationOfficialActivities_DB.DataId > 0)
{
ifNullUp = _sqlSugar.Queryable<Res_InvitationOfficialActivityData>()
.First(x => x.Id == InvitationOfficialActivities_DB.DataId && x.IsDel == 0);
}
else
{
ifNullUp = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().FirstAsync
(a => a.Country == res_InvitationData.Country && a.City == res_InvitationData.City && a.UnitName == res_InvitationData.UnitName && a.IsDel == 0);
}
if (ifNullUp == null)///添加或修改商邀资料
{
int 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
{
int CTable = await _sqlSugar.Updateable<Res_InvitationOfficialActivityData>()
.Where(a => a.Id == ifNullUp.Id)
.SetColumns(a => new Res_InvitationOfficialActivityData
{
UnitName = res_InvitationData.UnitName,
Country = res_InvitationData.Country,
Contact = res_InvitationData.Contact,
Tel = res_InvitationData.Tel,
Job = res_InvitationData.Job,
Email = res_InvitationData.Email,
Fax = res_InvitationData.Fax,
Address = res_InvitationData.Address,
Remark = res_InvitationData.Remark,
})
.ExecuteCommandAsync();
}
Grp_CreditCardPayment grp_CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == grp_Invitation.Id && a.CTable == 81 && a.IsDel == 0);
if (grp_CreditCardPayment != null)
{
if (dto.PayDId == 72) grp_CreditCardPayment.IsPay = 1;
else grp_CreditCardPayment.IsPay = 0;
grp_CreditCardPayment.PayMoney = dto.PayMoney;
grp_CreditCardPayment.PaymentCurrency = dto.PaymentCurrency;
grp_CreditCardPayment.Payee = dto.Payee;
grp_CreditCardPayment.OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer;
//获取新汇率 int diId,int CId, int currencyId
var rate = await fn(dto.DiId, 81, dto.InviteCurrency);
grp_CreditCardPayment.DayRate = 1;
grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
if (rate.Code == 0)
{
var rateInfo = (rate.Data as CurrencyInfo);
if (rateInfo is not null)
{
grp_CreditCardPayment.DayRate = rateInfo.Rate;
grp_CreditCardPayment.RMBPrice = rateInfo.Rate * grp_CreditCardPayment.PayMoney;
}
}
int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
.Where(a => a.Id == grp_CreditCardPayment.Id)
.SetColumns(a => new Grp_CreditCardPayment
{
ConsumptionPatterns = dto.ConsumptionPatterns,
ConsumptionDate = dto.ConsumptionDate,
PayDId = dto.PayDId,
IsPay = grp_CreditCardPayment.IsPay,
CTDId = dto.CTDId,
BankNo = dto.BankNo,
CardholderName = dto.CardholderName,
CompanyBankNo = dto.CompanyBankNo,
OtherBankName = dto.OtherBankName,
OtherSideNo = dto.OtherSideNo,
OtherSideName = dto.OtherSideName,
PayMoney = grp_CreditCardPayment.PayMoney,
PaymentCurrency = grp_CreditCardPayment.PaymentCurrency,
Payee = grp_CreditCardPayment.Payee,
OrbitalPrivateTransfer = grp_CreditCardPayment.OrbitalPrivateTransfer,
DayRate = grp_CreditCardPayment.DayRate,
RMBPrice = grp_CreditCardPayment.RMBPrice,
}).ExecuteCommandAsync();
if (CTable != 0)
{
var data = new { ccpId = grp_CreditCardPayment.Id, sign = 2 };
result = new Result() { Code = 0, Msg = "修改成功！", Data = data };
}
else
{
result = new Result() { Code = -1, Msg = "修改失败！" };
RollbackTran();
}
}
else
{
RollbackTran();
result = new Result() { Code = -1, Msg = "修改失败！" };
}
}
else
{
RollbackTran();
result = new Result() { Code = -1, Msg = "修改失败！" };
}
}
CommitTran();
return result;
}
}
}