using AutoMapper;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace OASystem.Infrastructure.Repositories.Financial
{
/// <summary>
/// 财务 - 团组已收款项
/// 雷怡 2023.08.16 15:24
/// </summary>
public class ProceedsReceivedRepository : BaseRepository<Fin_ProceedsReceived, Fin_ProceedsReceivedView>
{
private readonly IMapper _mapper;
/// <summary>
///
/// </summary>
/// <param name="sqlSugar"></param>
/// <param name="mapper"></param>
public ProceedsReceivedRepository(SqlSugarClient sqlSugar, IMapper mapper)
: base(sqlSugar)
{
_mapper = mapper;
}
/// <summary>
/// 根据diid查询团组已收款项 已关联应收款项
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public async Task<Result> GetGroupReceivedByDiid(int diid)
{
Result result = new() { Code = -2 };
string sql = string.Format(@"Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0}", diid);
var groupReceivablesList = await _sqlSugar.SqlQueryable<Fin_ProceedsReceivedView>(sql).ToListAsync();
result.Code = 0;
result.Msg = "查询成功!";
result.Data = groupReceivablesList;
return result;
}
/// <summary>
/// 根据diid 数组 查询团组已收款项 已关联应收款项
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public async Task<Result> GetGroupReceivedByDiids(int[] diids)
{
Result result = new() { Code = -2 };
var groupReceivablesList = await _sqlSugar.Queryable<Fin_ProceedsReceived>()
.Where(fr => fr.IsDel == 0 && diids.Contains(fr.Diid)).ToListAsync();
result.Code = 0;
result.Msg = "查询成功!";
result.Data = groupReceivablesList;
return result;
}
/// <summary>
/// 已收款项 已关联应收款项 删除
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> _Del(ProceedsReceivedDelDto dto)
{
Result result = new Result() { Code = -1, Msg = "程序错误!" };
var res = await _sqlSugar.Updateable<Fin_ProceedsReceived>()
.Where(it => it.Id == dto.Id)
.SetColumns(it => new Fin_ProceedsReceived()
{
IsDel = 1,
DeleteUserId = dto.UserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}
).ExecuteCommandAsync();
if (res > 0)
{
result.Msg = "删除成功！";
result.Code = 0;
}
else
{
result.Msg = "删除失败!";
}
return result;
}
/// <summary>
/// 已收款项 已关联应收款项
/// Add And Update
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public async Task<Result> PostAmountReceivedOperate(ProceedsReceivedDto dto)
{
Result result = new() { Code = -2 };
if (dto._ProceedsReceivedInfos.Count <= 0)
{
result.Msg = "已收款项没有信息，不能进行，添加或修改操作！！！";
return result;
}
int addCount = 0, updateCount = 0;
if (dto.PortType == 1)
{
List<Fin_ProceedsReceived> _ProceedsReceived = new List<Fin_ProceedsReceived>();
foreach (var item in dto._ProceedsReceivedInfos)
{
_ProceedsReceived.Add(new Fin_ProceedsReceived()
{
Diid = dto.DiId,
Id = item.Id,
SectionTime = item.SectionTime,
Price = item.Price,
Currency = item.Currency,
ReceivablesType = item.ReceivablesType,
Client = item.Client,
CustomerName = item.CustomerName,
CustomerTel = item.CustomerTel,
FID = item.FID,
CreateUserId = dto.UserId,
CreateTime = DateTime.Now,
Remark = item.Remark
});
}
if (_ProceedsReceived.Count > 0)
{
var x = _sqlSugar.Storageable(_ProceedsReceived).ToStorage();
addCount = x.AsInsertable.ExecuteCommand(); //不存在插入
updateCount = x.AsUpdateable.ExecuteCommand(); //存在更新
}
result.Code = 0;
result.Msg = string.Format(@"操作成功!添加：{0}条；更新：{1}；", addCount, updateCount);
}
return result;
}
/// <summary>
/// 已收款项 已关联应收款项
/// 分配已收款项至 应收项下
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public async Task<Result> PostAllocateAmountReceived(AllocateAmountReceivedDto dto)
{
Result result = new() { Code = -2 };
if (dto.SubIds.Count <= 0)
{
result.Msg = "请选择要添加的已收款项!";
return result;
}
if (dto.PortType == 1)
{
List<Fin_ProceedsReceived> _proceedsReceived = new List<Fin_ProceedsReceived>();
foreach (var id in dto.SubIds)
{
_proceedsReceived.Add(new Fin_ProceedsReceived() { Id = id, FID = dto.ParentId });
}
var res = await _sqlSugar.Updateable(_proceedsReceived).
WhereColumns(it => new { it.Id })
.UpdateColumns(it => new { it.FID })
.ExecuteCommandAsync();
result.Code = 0;
}
return result;
}
/// <summary>
/// 根据diid查询团组已收款项
/// 全字段
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public async Task<Result> PostAmountReceivedByDiId(int diId)
{
Result result = new() { Code = -2 };
string sql = string.Format(@"Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0}", diId);
var groupReceivablesList = await _sqlSugar.SqlQueryable<Fin_ProceedsReceivedView>(sql).ToListAsync();
result.Code = 0;
result.Msg = "查询成功!";
result.Data = groupReceivablesList;
return result;
}
/// <summary>
/// 根据diid查询团组已收款项
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public async Task<Result> PostAmountReceived(int diid)
{
Result result = new() { Code = -2 };
string sql = string.Format(@"Select Id,Diid,SectionTime,Price,Currency,ReceivablesType,Client,CustomerName,CustomerTel, Remark From Fin_ProceedsReceived
Where IsDel=0 And Diid={0}", diid);
var groupReceivablesList = await _sqlSugar.SqlQueryable<ProceedsReceived1View>(sql).ToListAsync();
result.Code = 0;
result.Msg = "查询成功!";
result.Data = groupReceivablesList;
return result;
}
/// <summary>
/// 已收款项
/// Add And Update
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public async Task<Result> PostAmountReceivedAddOrEditDto(AmountReceivedAddOrEditDto dto)
{
Result result = new() { Code = -2 };
if (dto._ProceedsReceivedInfos.Count <= 0)
{
result.Msg = "已收款项没有信息，不能进行，添加或修改操作！！！";
return result;
}
int addCount = 0, updateCount = 0;
if (dto.PortType == 1)
{
List<Fin_ProceedsReceived> _ProceedsReceived = new List<Fin_ProceedsReceived>();
foreach (var item in dto._ProceedsReceivedInfos)
{
_ProceedsReceived.Add(new Fin_ProceedsReceived()
{
Diid = dto.DiId,
Id = item.Id,
SectionTime = item.SectionTime,
Price = item.Price,
Currency = item.Currency,
ReceivablesType = item.ReceivablesType,
Client = item.Client,
CustomerName = item.CustomerName,
CustomerTel = item.CustomerTel,
FID = 0,
CreateUserId = dto.UserId,
CreateTime = DateTime.Now,
Remark = item.Remark
});
}
if (_ProceedsReceived.Count > 0)
{
var x = _sqlSugar.Storageable(_ProceedsReceived).ToStorage();
addCount = x.AsInsertable.ExecuteCommand(); //不存在插入
_sqlSugar.BeginTran();
foreach (var item in _ProceedsReceived)
{
bool res = await UpdateAsync<Fin_ProceedsReceived>(s => s.Id == item.Id, s => new Fin_ProceedsReceived
{
SectionTime = item.SectionTime,
Price = item.Price,
Currency = item.Currency,
ReceivablesType = item.ReceivablesType,
Client = item.Client,
CustomerName = item.CustomerName,
CustomerTel = item.CustomerTel,
Remark = item.Remark
});
if (!res)
{
_sqlSugar.RollbackTran();
break;
}
updateCount++;
}
_sqlSugar.CommitTran();
//updateCount = x.AsUpdateable.IgnoreColumns(p => new
//{
// p.SectionTime,
// p.Price,
// p.Currency,
// p.ReceivablesType,
// p.Client,
// p.CustomerName,
// p.CustomerTel,
// p.Remark
//}).ExecuteCommand(); //存在更新
}
result.Code = 0;
result.Msg = string.Format(@"操作成功!添加：{0}条；更新：{1}；", addCount, updateCount);
//修改团组状态
int diId = dto.DiId;
decimal sum_fr = 0M;
decimal sum_pr = 0M;
decimal sum_refund = 0M; //收款退还
decimal sum_extra = 0M; //超支费用
//1.应收
string sql_fr = string.Format(@" Select * From Fin_ForeignReceivables Where IsDel=0 And Diid={0} ", diId);
List<Fin_ForeignReceivables> list_fr = _sqlSugar.SqlQueryable<Fin_ForeignReceivables>(sql_fr).ToList();
sum_fr = list_fr.Sum(s => s.ItemSumPrice);
//2.已收
string sql_pr = string.Format(@" Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0} ", diId);
List<Fin_ProceedsReceived> list_pr = _sqlSugar.SqlQueryable<Fin_ProceedsReceived>(sql_pr).ToList();
sum_pr = list_pr.Sum(s => s.Price);
//3.收款退还
string sql_other = string.Format(@"Select * From Fin_PaymentRefundAndOtherMoney prao
Inner Join Grp_CreditCardPayment ccp On prao.Id = ccp.CId
Where ccp.CTable = 285 And ccp.IsPay = 1 And prao.IsDel = 0 And ccp.IsDel = 0 And prao.DiId = {0}", diId);
List<Grp_CreditCardPayment> list_other = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_other).ToList();
sum_refund = list_other.Sum(s => s.RMBPrice);
//4.超支
string sql_extra = string.Format(@" Select c.* From Fin_GroupExtraCost f
Inner join Grp_CreditCardPayment c On f.Id = c.CId
Where c.CTable = 1015 And c.IsPay = 1 And f.IsDel = 0 And c.IsDel = 0 And f.DiId = {0} ", diId);
List<Grp_CreditCardPayment> list_extra = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_extra).ToList();
sum_extra = list_extra.Sum(s => s.RMBPrice);
decimal balance = ((sum_fr + sum_extra) - (sum_pr - sum_refund));
if (balance <= 0)
{
bool res = await UpdateAsync<Grp_DelegationInfo>(s => s.Id == dto.DiId, s => new Grp_DelegationInfo
{
IsSure = 1
});
}
}
return result;
}
/// <summary>
/// 已收款项 删除
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> PostAmountReceivedDel(AmountReceivedDelDto dto)
{
Result result = new Result() { Code = -1, Msg = "程序错误!" };
var res = await _sqlSugar.Updateable<Fin_ProceedsReceived>()
.Where(it => it.Id == dto.Id)
.SetColumns(it => new Fin_ProceedsReceived()
{
IsDel = 1,
DeleteUserId = dto.UserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}
).ExecuteCommandAsync();
if (res > 0)
{
result.Msg = "删除成功！";
result.Code = 0;
}
else
{
result.Msg = "删除失败!";
}
return result;
}
}
}