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);
invitation.PayDId = grp_CreditCard.PayDId;
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;
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
{
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);
}
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<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;
}
}
ListViewBase<InvitationOfficialActivitiesView> rst = new ListViewBase<InvitationOfficialActivitiesView>();
rst.DataList = invitationOfficialActivities;
rst.DataCount = count;
rst.CurrPageIndex = dto.PageIndex;
rst.CurrPageSize = dto.PageSize;
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)
{
ListViewBase<InvitationOfficialActivitiesView> rst = new ListViewBase<InvitationOfficialActivitiesView>();
rst.DataList = _DecreasePayments;
rst.DataCount = _DecreasePayments.Count;
rst.CurrPageIndex = dto.PageIndex;
rst.CurrPageSize = dto.PageSize;
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 = "未知错误" };
}
}
/// <summary>
/// 商邀费用操作（Status:1.新增,2.修改）
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> OpInvitationOfficialActivities(OpInvitationOfficialActivitiesDto dto)
{
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);
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;
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 = 0;
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<Grp_TeamRate>().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 = 1M;
C.RMBPrice = C.PayMoney;
}
}
else
{
C.DayRate = 1M;
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<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);
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)
{
grp_CreditCardPayment.PayMoney = dto.InviteCosts + dto.SendCost;
Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == grp_Invitation.DiId && a.IsDel == 0 && 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 = 1M;
grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
}
}
else
{
grp_CreditCardPayment.DayRate = 1M;
grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
}
int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().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 = "修改失败！" };
}
}
CommitTran();
}
catch (Exception ex)
{
result = new Result() { Code = -2, Msg = "程序错误！" };
throw;
}
return result;
}
}
}