using AutoMapper;
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.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.QiYeWeChat;
using OASystem.Domain.ViewModels.Resource;
using OASystem.Infrastructure.Repositories.System;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Intrinsics.Arm;
using System.Text;
using System.Threading.Tasks;
namespace OASystem.Infrastructure.Repositories.Groups
{
public class DecreasePaymentsRepository : BaseRepository<Grp_DecreasePayments, Grp_DecreasePayments>
{
private readonly IMapper _mapper;
private readonly TeamRateRepository _teamRateRep;
public DecreasePaymentsRepository(SqlSugarClient sqlSugar, IMapper mapper, TeamRateRepository teamRateRep)
: base(sqlSugar)
{
this._mapper = mapper;
_teamRateRep = teamRateRep;
}
/// <summary>
/// 根据团组Id查询数据
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> DecreasePaymentsList(DecreasePaymentsListDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
//string UserId = "0";
//List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a => a.DIId == dto.DiId && a.IsDel == 0 && a.CTId == 98).ToList();
//UserId = string.Join(",", gtaUIdList.Select(it => it.UId).ToList());
//string sql = string.Format(@"select Id,DiId,PriceName,Price,(select name from Sys_SetData where id=s.Currency) as Currency,FilePath,
// (select CnName from Sys_Users where Id=s.CreateUserId) as 'CreateUserName',CreateTime,(select IsAuditGM from
// Grp_CreditCardPayment where CTable=98 and CId=s.Id and DiId = {0} and IsDel=0) as 'isAudit' from Grp_DecreasePayments s
// where DIID={0} and IsDel=0 and s.CreateUserId in ({1}) ", dto.DiId, dto.UserId);
string sqlWhere = "";
if (dto.IsPaySign != -1) {
sqlWhere += string.Format(@" And ccp.IsPay = {0} ", dto.IsPaySign);
}
//雷怡 2024-5-8 15:14 新增View字段 CreateTime
string sql = string.Format(@" Select gdp.Id,gdp.DiId,gdp.PriceName,gdp.Price,sd1.[Name] as Currency,gdp.FilePath,su.CnName as CreateUserName,
ccp.IsAuditGM as isAudit,ccp.IsPay,gdp.CreateTime
From Grp_DecreasePayments as gdp With(Nolock) Left Join Grp_CreditCardPayment as ccp With(Nolock) On gdp.Id = ccp.CId
Left Join Sys_SetData as sd1 On gdp.Currency = sd1.Id
Left Join Sys_Users as su On gdp.CreateUserId = su.Id
Where gdp.DiId = {0} And ccp.CTable = 98 {2} And ccp.IsDel = 0 And gdp.IsDel = 0 And gdp.CreateUserId in ({1}) ", dto.DiId, dto.UserId, sqlWhere);
List<DecreasePaymentsView> _DecreasePayments = await _sqlSugar.SqlQueryable<DecreasePaymentsView>(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 = ex.Message };
}
return result;
}
public async Task<Result> DecreasePaymentsSelect(DecreasePaymentsDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
#region 团组下拉框
List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == dto.CTId).ToList();
string DiId = "";
List<DecreasePaymentGroupView> _Delegations = new List<DecreasePaymentGroupView>();
if (grp_GroupsTaskAssignment.Count > 0)
{
foreach (var item in grp_GroupsTaskAssignment)
{
DiId += item.DIId + ",";
}
if (DiId != "0")
{
DiId = DiId.Substring(0, DiId.Length - 1);
}
string sql = string.Format($@"Select
di.Id,
di.TeamName,
di.ClientUnit,
di.ClientName,
di.TourCode,
di.TeamDid,
sd.[Name] As TeamTypeName,
di.VisitCountry,
di.VisitStartDate,
di.VisitEndDate,
di.VisitDays,
di.VisitPNumber,
di.CreateTime
From Grp_DelegationInfo di With(NoLock)
Left Join Sys_SetData sd On di.TeamDid = sd.Id
Where di.Id in({DiId}) and di.IsDel=0
Order By di.CreateTime Desc");
//DecreasePaymentGroupView
_Delegations = _sqlSugar.SqlQueryable<DecreasePaymentGroupView>(sql).ToList();
}
#endregion
#region 其他下拉框查询
//支付方式
List<Sys_SetData> Payment = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 14 && a.IsDel == 0).ToList();
List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);
//供应商 supplier
List<Sys_SetData> supplier = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 21 && a.IsDel == 0).ToList();
List<SetDataInfoView> _supplier = _mapper.Map<List<SetDataInfoView>>(supplier);
if (_supplier.Count > 0)
{
var d1 = _supplier.Find(it => it.Name.Equals("策划 公司"));
if (d1 != null) _supplier.Remove(d1);
var d2 = _supplier.Find(it => it.Name.Equals("礼仪"));
if (d2 != null) _supplier.Remove(d2);
var d3 = _supplier.Find(it => it.Name.Equals("日常办公用品"));
if (d3 != null) _supplier.Remove(d3);
var d4 = _supplier.Find(it => it.Name.Equals("其他"));
if (d4 != null)
{
_supplier.Remove(d4);
_supplier.Add(d4);
}
}
List<dynamic> _supplierArea = new List<dynamic>() {
new { id=0,name = "未选择" },
new { id=1,name = "国内" },
new { id=2,name = "国外" },
};
#endregion
var data = new
{
payment = _Payment,
groupName = _Delegations,
supplier = _supplier,
supplierArea = _supplierArea
};
return result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
}
public async Task<Result> OpDecreasePayments(DecreasePaymentsOpDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
BeginTran();
try
{
int id = 0;
Grp_DecreasePayments grp_Decrease = _mapper.Map<Grp_DecreasePayments>(dto);
//处理费用总计
if (grp_Decrease.FeeTotal == 0.00M)
{
grp_Decrease.FeeTotal = grp_Decrease.Price * grp_Decrease.Price;
}
List<TeamRateDescAddCurrencyIdView> teamRates = await _teamRateRep.PostGroupTeamRateItemByDiIdAndCTableId(1, dto.DiId, 98);
if (dto.Status == 1)//添加
{
string selectSql = string.Format(@"select * from Grp_DecreasePayments where PriceName='{0}' and IsDel={1} and DiId={2}"
, dto.PriceName, 0, dto.DiId);
var DecreasePayments = await _sqlSugar.SqlQueryable<Grp_DecreasePayments>(selectSql).FirstAsync();//查询是否存在
if (DecreasePayments != null)
{
return new Result() { Code = -1, Msg = "该数据已存在，请勿重复添加！" };
}
else//不存在，可添加
{
id = await AddAsyncReturnId(grp_Decrease);
if (id < 1)
{
RollbackTran();
return new Result() { Code = -1, Msg = "添加失败！" };
}
//进行C表添加
Grp_CreditCardPayment C = new Grp_CreditCardPayment();
C.PayDId = dto.PayDId;
C.ConsumptionPatterns = "";
C.ConsumptionDate = "";
C.CTDId = 0;
C.BankNo = "";
C.CardholderName = "";
C.PayMoney = grp_Decrease.FeeTotal;
C.PaymentCurrency = grp_Decrease.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 = grp_Decrease.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 = "";
if (C.PayDId == 72) C.IsPay = 1;
else C.IsPay = 0;
C.DIId = grp_Decrease.DiId;
C.CId = id;
C.CTable = 98;
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;
//C.RMBPrice = 0.00f;
//设置该团组的汇率
TeamRateDescAddCurrencyIdView teamRate = teamRates.Where(it => it.CurrencyId == grp_Decrease.Currency).FirstOrDefault();
//Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == grp_Decrease.DiId && a.IsDel == 0 && a.CTable == 98);
if (teamRate != null)
{
if (teamRate.CurrencyCode.Equals("CNY"))
{
C.DayRate = 1.0000M;
C.RMBPrice = C.PayMoney;
}
else
{
C.DayRate = teamRate.Rate;
C.RMBPrice = C.PayMoney * C.DayRate;
}
}
else
{
RollbackTran();
return new Result() { Code = -1, Msg = $"添加失败！团组汇率未设置{_sqlSugar.Queryable<Sys_SetData>().First(it => it.IsDel == 0 && it.Id == grp_Decrease.Currency)?.Name ?? ""}该币种汇率!" };
}
int cId = await _sqlSugar.Insertable(C).ExecuteReturnIdentityAsync();
if (cId != 0)
{
CommitTran();
var data = new { ccpId = cId, sign = 1 };
return new Result() { Code = 0, Msg = "添加成功！", Data = data };
}
else
{
RollbackTran();
return new Result() { Code = -1, Msg = "添加失败！" };
}
}
}
else if (dto.Status == 2)
{
bool res = await UpdateAsync(a => a.Id == grp_Decrease.Id, a => new Grp_DecreasePayments
{
DiId = grp_Decrease.DiId,
SupplierArea = grp_Decrease.SupplierArea,
SupplierTypeId = grp_Decrease.SupplierTypeId,
PriceName = grp_Decrease.PriceName,
Price = grp_Decrease.Price,
Quantity = grp_Decrease.Quantity,
FeeTotal = grp_Decrease.FeeTotal,
Currency = grp_Decrease.Currency,
FilePath = grp_Decrease.FilePath,
Remark = grp_Decrease.Remark,
});
if (res)
{
Grp_CreditCardPayment grp_CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == grp_Decrease.Id && a.CTable == 98 && a.CId == grp_Decrease.Id && a.IsDel == 0);
if (grp_CreditCardPayment != null)
{
//设置该团组的汇率
TeamRateDescAddCurrencyIdView teamRate = teamRates.Where(it => it.CurrencyId == grp_Decrease.Currency).FirstOrDefault();
if (teamRate != null)
{
if (teamRate.CurrencyCode.Equals("CNY"))
{
grp_CreditCardPayment.DayRate = 1.0000M;
grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
}
else
{
grp_CreditCardPayment.DayRate = teamRate.Rate;
grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney * grp_CreditCardPayment.DayRate;
}
}
else
{
RollbackTran();
return new Result() { Code = -1, Msg = "修改失败！" };
}
int ispay = 0;
if (dto.PayDId == 72) ispay = 1;
int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
.Where(a => a.Id == grp_CreditCardPayment.Id)
.SetColumns(a => new Grp_CreditCardPayment
{
PayDId = dto.PayDId,
IsPay = ispay,
PayMoney = grp_Decrease.FeeTotal,
PaymentCurrency = grp_Decrease.Currency,
Payee = dto.Payee,
OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer,
DayRate = grp_CreditCardPayment.DayRate,
RMBPrice = grp_CreditCardPayment.RMBPrice,
}).ExecuteCommandAsync();
if (CTable > 0)
{
CommitTran();
var data = new { ccpId = grp_CreditCardPayment.Id, sign = 2 };
return new Result() { Code = 0, Msg = "修改成功！", Data = data };
}
else
{
RollbackTran();
return new Result() { Code = -1, Msg = "修改失败！" };
}
}
else
{
RollbackTran();
return new Result() { Code = -1, Msg = "修改失败！" };
}
}
else
{
RollbackTran();
return new Result() { Code = -1, Msg = "修改失败！" };
}
}
}
catch (Exception ex)
{
RollbackTran();
return new Result() { Code = -2, Msg = ex.Message };
}
return result;
}
public async Task<Result> PostGroupNameAndEasy(DecreasePaymentsDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
#region 团组下拉框
List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == dto.CTId).ToList();
string DiId = "";
foreach (var item in grp_GroupsTaskAssignment)
{
DiId += item.DIId + ",";
}
if (!string.IsNullOrWhiteSpace(DiId))
{
DiId = DiId.Substring(0, DiId.Length - 1);
}
else
{
DiId = "0";
}
string sql = string.Format(@"select Id,TourCode,TeamName,ClientName,VisitStartDate,VisitEndDate,VisitCountry,VisitDays,VisitPNumber from Grp_DelegationInfo where Id in({0}) and IsDel={1}", DiId, 0);
List<DelegationEasyView> grp_Delegations = _sqlSugar.SqlQueryable<DelegationEasyView>(sql).ToList();
if (grp_Delegations.Count != 0)
{
int count = grp_Delegations.Count;
float totalPage = (float)count / dto.PageSize;//总页数
if (totalPage == 0) totalPage = 1;
else totalPage = (int)Math.Ceiling((double)totalPage);
List<DelegationEasyView> delegationEasyViews = new List<DelegationEasyView>();
for (int i = 0; i < dto.PageSize; i++)
{
var RowIndex = i + (dto.PageIndex - 1) * dto.PageSize;
if (RowIndex < grp_Delegations.Count)
{
delegationEasyViews.Add(grp_Delegations[RowIndex]);
}
else
{
break;
}
}
ListViewBase<DelegationEasyView> rst = new ListViewBase<DelegationEasyView>();
rst.DataList = delegationEasyViews;
rst.DataCount = count;
rst.CurrPageIndex = dto.PageIndex;
rst.CurrPageSize = dto.PageSize;
return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };
}
else
{
ListViewBase<DelegationEasyView> rst = new ListViewBase<DelegationEasyView>();
rst.DataList = new List<DelegationEasyView>();
return result = new Result() { Code = 0, Msg = "暂无数据！", Data = rst };
}
#endregion
}
catch (Exception ex)
{
return result = new Result() { Code = -2, Msg = "程序错误" };
throw;
}
}
/// <summary>
/// 根据团组增减款项表Id查询数据
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public async Task<Result> QueryDecreasePaymentsById(DecreasePaymentsByIdDto dto)
{
Result result = new Result() { Code = -2, Msg = "程序错误" };
try
{
Grp_DecreasePayments grp_Decrease = _sqlSugar.Queryable<Grp_DecreasePayments>().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 == 98);
var data = new
{
_Decrease = grp_Decrease,
_CreditCard = grp_CreditCard,
};
result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
}
catch (Exception ex)
{
result = new Result() { Code = -2, Msg = "程序错误" };
throw;
}
return result;
}
/// <summary>
/// Del
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public async Task<Result> _Del(int id, int userId)
{
Result result = new Result() { Code = -2, Msg = "删除失败！" };
_sqlSugar.BeginTran();
var del = await _sqlSugar.Updateable<Grp_DecreasePayments>()
.SetColumns(it => new Grp_DecreasePayments()
{
IsDel = 1,
DeleteUserId = userId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}).Where(it => it.Id == id)
.ExecuteCommandAsync();
if (del > 0)
{
var del1 = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
.SetColumns(a => new Grp_CreditCardPayment()
{
IsDel = 1,
DeleteUserId = userId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
})
.Where(a => a.CId == id && a.CTable == 98)
.ExecuteCommandAsync();
if (del1 > 0)
{
_sqlSugar.CommitTran();
result.Code = 0;
result.Msg = "删除成功!";
return result;
}
}
_sqlSugar.RollbackTran();
return result;
}
}
}