using AutoMapper;
using MathNet.Numerics.Statistics.Mcmc;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
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;
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
{
Grp_InvitationOfficialActivities 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)
{
res_InvitationOfficialActivityData = _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().First(a =>a.Country==grp_Invitation.InviterArea && a.UnitName==grp_Invitation.Inviter && a.IsDel == 0);
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);
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);
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);
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();
try
{
int id = 0;
Grp_InvitationOfficialActivities grp_Invitation = _mapper.Map<Grp_InvitationOfficialActivities>(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<Grp_InvitationOfficialActivities>(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<Res_InvitationOfficialActivityData>(dto);
res_InvitationData.Country = dto.InviterArea;
res_InvitationData.UnitName = dto.Inviter;
res_InvitationData.Delegation = dto.DiId.ToString();
Res_InvitationOfficialActivityData 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)///添加或修改商邀资料
{
res_InvitationData.Remark = dto.OtherInformation;
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
{
Contact = dto.Contact,
Tel = dto.Tel,
Email = dto.Email,
Fax = dto.Fax,
Address = dto.Address,
Remark = dto.OtherInformation,
}).ExecuteCommandAsync();
}
Grp_CreditCardPayment C = new Grp_CreditCardPayment();
C.PayDId = dto.PayDId;
if (C.PayDId == 72)
{
C.IsPay = 1;
}
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.IsPay = 0;
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();
if (cId != 0)
{
result = new Result() { Code = 0, Msg = "添加成功！" };
}
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 = dto.Attachment,
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_InvitationOfficialActivityData 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)///添加或修改商邀资料
{
res_InvitationData.Remark = dto.OtherInformation;
res_InvitationData.Delegation = dto.DiId.ToString();
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
{
Contact = dto.Contact,
Tel = dto.Tel,
Email = dto.Email,
Fax = dto.Fax,
Address = dto.Address,
Remark = dto.OtherInformation,
}).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;
}
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)
{
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 = "修改失败！" };
}
}
CommitTran();
}
catch (Exception ex)
{
result = new Result() { Code = -2, Msg = "程序错误！" };
throw;
}
return result;
}
}
}