using AutoMapper;
using NPOI.POIFS.Crypt.Dsig;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.QiYeWeChat;
using OASystem.Infrastructure.Tools;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace OASystem.Infrastructure.Repositories.Groups
{
public class CustomersRepository : BaseRepository<Grp_Customers, Grp_Ommission>
{
private readonly IMapper _mapper;
private readonly TeamRateRepository _teamRateRep;
public CustomersRepository(SqlSugarClient sqlSugar, IMapper mapper, TeamRateRepository teamRateRep)
: base(sqlSugar)
{
_mapper = mapper;
_teamRateRep = teamRateRep;
}
/// <summary>
/// 根据团组Id查询保险费用列表
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> CustomersByDiId(CustomersByDiIdDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
string UserId = "";
List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a => a.DIId == dto.DiId && a.IsDel == 0 && a.CTId == 82).ToList();
foreach (Grp_GroupsTaskAssignment gta in gtaUIdList)
UserId += gta.UId + ",";
if (!string.IsNullOrWhiteSpace(UserId))
{
UserId = UserId.Substring(0, UserId.Length - 1);
}
else
{
UserId = "0";
}
string sqlWhere = string.Format(@"Where h.DiId={0} and h.IsDel={1} And h.CreateUserId in ({2})", dto.DiId, 0, UserId);
int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
int endIndex = startIndex + dto.PageSize - 1;
if (dto.PortType == 1)
{
string sql = string.Format(@"select h.Id,h.ClientName,InsuranceCosts,Currency,s.Name as CurrencyStr,Attachment,c.OrbitalPrivateTransfer,
u.CnName,c.IsAuditGM,s1.Name As 'PayName', CASE c.IsPay when 0 then '未付款' when 1 then '已付款' ELSE '未付款' END as 'IsPayStr'
From Grp_Customers h
Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=82 and c.isdel=0
Left Join Sys_SetData s on h.Currency=s.Id
Left Join Sys_Users u on u.Id=h.CreateUserId
Left Join Sys_SetData s1 on c.PayDId = s1.Id {0}
Order by c.IsAuditGM,c.PayPercentage,h.CreateTime desc", sqlWhere);
List<CustomersView> Customers = _sqlSugar.SqlQueryable<CustomersView>(sql).ToList();
var parseIntValue = 0;
var CrmIds = (from item in Customers
where item.ClientName.Contains(",") || int.TryParse(item.ClientName, out parseIntValue)
select item.ClientName).ToList();
var strCrmIds = string.Join(',', CrmIds);
List<Crm_DeleClient> clietArr = new List<Crm_DeleClient>();
if (!string.IsNullOrWhiteSpace(strCrmIds))
{
sql = string.Format("SELECT * FROM Crm_DeleClient cdc where id in ({0}) and IsDel = 0", strCrmIds);
clietArr = _sqlSugar.SqlQueryable<Crm_DeleClient>(sql).ToList();
}
foreach (var item in Customers)
{
if (item.IsAuditGM == 0) item.IsAuditGMStr = "未审核";
else if (item.IsAuditGM == 1) item.IsAuditGMStr = "已通过";
else if (item.IsAuditGM == 2) item.IsAuditGMStr = "未通过";
else if (item.IsAuditGM == 3) item.IsAuditGMStr = "自动审核";
if (item.OrbitalPrivateTransfer == 0) item.OrbitalPrivateTransferStr = "公转";
else if (item.OrbitalPrivateTransfer == 1) item.OrbitalPrivateTransferStr = "私转";
if (item.ClientName.Contains(","))
{
var splitArr = item.ClientName.Split(',');
var name = string.Empty;
foreach (var client in splitArr)
{
if (int.TryParse(client, out parseIntValue))
{
var clientObject = clietArr.Find(x => x.Id == parseIntValue);
name += (clientObject?.LastName + clientObject?.FirstName + " ");
}
}
if (!string.IsNullOrWhiteSpace(name))
{
item.ClientName = name;
}
continue;
}
if (int.TryParse(item.ClientName, out parseIntValue))
{
var name = string.Empty;
var clientObject = clietArr.Find(x => x.Id == parseIntValue);
name += (clientObject?.LastName + clientObject?.FirstName);
if (!string.IsNullOrWhiteSpace(name))
{
item.ClientName = name;
}
continue;
}
}
if (!string.IsNullOrWhiteSpace(dto.ClientName))
{
Customers = Customers.Where(x => x.ClientName.Contains(dto.ClientName)).ToList();
}
return result = new Result() { Code = 0, Msg = "查询成功！", Data = Customers };
}
else if (dto.PortType == 2 || dto.PortType == 3)
{
string sql = string.Format(@"Select * From (
Select row_number() over (order by c.IsAuditGM,c.PayPercentage,h.CreateTime desc) as RowNumber,h.Id,h.ClientName,InsuranceCosts,
Currency,s.Name as CurrencyStr,Attachment,c.OrbitalPrivateTransfer,u.CnName,c.IsAuditGM,s1.Name As 'PayName'
From Grp_Customers h
Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=82 and c.isdel=0
Left Join Sys_SetData s on h.Currency=s.Id
Left Join Sys_Users u on u.Id=h.CreateUserId
Left Join Sys_SetData s1 on c.PayDId = s1.Id {0}
) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);
List<CustomersView> Customers = _sqlSugar.SqlQueryable<CustomersView>(sql).ToList();
var parseIntValue = 0;
var CrmIds = (from item in Customers
where item.ClientName.Contains(",") || int.TryParse(item.ClientName, out parseIntValue)
select item.ClientName).ToList();
var strCrmIds = string.Join(',', CrmIds);
List<Crm_DeleClient> clietArr = new List<Crm_DeleClient>();
if (!string.IsNullOrWhiteSpace(strCrmIds))
{
sql = string.Format("SELECT * FROM Crm_DeleClient cdc where id in ({0}) and IsDel = 0", strCrmIds);
clietArr = _sqlSugar.SqlQueryable<Crm_DeleClient>(sql).ToList();
}
foreach (var item in Customers)
{
if (item.IsAuditGM == 0) item.IsAuditGMStr = "未审核";
else if (item.IsAuditGM == 1) item.IsAuditGMStr = "已通过";
else if (item.IsAuditGM == 2) item.IsAuditGMStr = "未通过";
else if (item.IsAuditGM == 3) item.IsAuditGMStr = "自动审核";
if (item.OrbitalPrivateTransfer == 0) item.OrbitalPrivateTransferStr = "公转";
else if (item.OrbitalPrivateTransfer == 1) item.OrbitalPrivateTransferStr = "私转";
if (item.ClientName.Contains(","))
{
var splitArr = item.ClientName.Split(',');
var name = string.Empty;
var i = 0;
while (i < splitArr.Count())
{
if (int.TryParse(splitArr[i], out parseIntValue))
{
var clientObject = clietArr.Find(x => x.Id == parseIntValue);
name += (clientObject?.LastName + clientObject?.FirstName);
}
i++;
if (i < splitArr.Count())
{
name += "[*TuT*]";
}
}
if (!string.IsNullOrWhiteSpace(name))
{
item.ClientName = name.TrimEnd();
}
continue;
}
if (int.TryParse(item.ClientName, out parseIntValue))
{
var name = string.Empty;
var clientObject = clietArr.Find(x => x.Id == parseIntValue);
name += (clientObject?.LastName + clientObject?.FirstName);
if (!string.IsNullOrWhiteSpace(name))
{
item.ClientName = name;
}
continue;
}
}
string CountSql = string.Format(@"Select COUNT(1) as Count From (
select h.Id,h.ClientName,InsuranceCosts,Currency,s.Name as CurrencyStr,Attachment,c.OrbitalPrivateTransfer,u.CnName,c.IsAuditGM
From Grp_Customers h
Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=82 and c.isdel=0
left Join Sys_SetData s on h.Currency=s.Id
left Join Sys_Users u on u.Id=h.CreateUserId {0}
) temp", sqlWhere);
DataCount dataCount = _sqlSugar.SqlQueryable<DataCount>(CountSql).First();
int count = dataCount.Count;
float totalPage = (float)count / dto.PageSize;//总页数
if (totalPage == 0) totalPage = 1;
else totalPage = (int)Math.Ceiling((double)totalPage);
ListViewBase<CustomersView> rst = new ListViewBase<CustomersView>();
rst.DataList = Customers;
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 = -2, Msg = "请传入PortType参数,1 Web 2 Android 3 IOS" };
}
}
public async Task<Result> CustomersById(CustomersByIdDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
Grp_Customers grp_Customers = _sqlSugar.Queryable<Grp_Customers>().First(a => a.Id == dto.Id && a.IsDel == 0);
CustomersByIdView customersById = _mapper.Map<CustomersByIdView>(grp_Customers);
if (customersById != null)
{
Grp_CreditCardPayment creditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 82);
CreditCardView creditCardView = _mapper.Map<CreditCardView>(creditCardPayment);
Grp_InsuranceCost Iid = _sqlSugar.Queryable<Grp_InsuranceCost>().First(a => a.IsDel == 0 && a.Id == customersById.Iid);
if (Iid != null)
{
customersById.IidStr = Iid.GName;
string CountSql = string.Format(@"select sum(InsuranceCosts) as CountCost from Grp_Customers where isdel=0 and Iid=" + customersById.Iid);
DataCountCost dataCount = _sqlSugar.SqlQueryable<DataCountCost>(CountSql).First();
customersById.InsuranceBalance = Math.Round(Iid.RechargeCost - dataCount.CountCost, 2);
}
Sys_SetData Currency = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == customersById.Currency);
if (Currency != null) customersById.CurrencyStr = Currency.Name;
Sys_SetData PayDId = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == creditCardView.PayDId);
if (PayDId != null) creditCardView.PayDIdStr = PayDId.Name;
Sys_SetData CTDId = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == creditCardView.CTDId);
if (CTDId != null) creditCardView.CTDIdStr = CTDId.Name;
creditCardView.OrbitalPrivateTransferStr = creditCardView.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
Sys_SetData PaymentCurrency = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == creditCardView.PaymentCurrency);
if (PaymentCurrency != null) creditCardView.PaymentCurrencyStr = PaymentCurrency.Name;
var data = new
{
customers = customersById,
creditCard = creditCardView
};
return result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
}
var dataN = new
{
customers = new CustomersView(),
creditCard = new CreditCardView()
};
return result = new Result() { Code = 0, Msg = "暂无数据！", Data = dataN };
}
catch (Exception ex)
{
return result = new Result() { Code = -2, Msg = "未知错误" };
throw;
}
}
public async Task<Result> CustomersInitialize(CustomersInitializeDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
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);
//卡类型
List<Sys_SetData> BankCard = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 15 && a.IsDel == 0).ToList();
List<SetDataCurrencyInfoView> _BankCard = _mapper.Map<List<SetDataCurrencyInfoView>>(BankCard);
//保险名称
List<Grp_InsuranceCost> _InsuranceCosts = _sqlSugar.Queryable<Grp_InsuranceCost>().Where(a => a.IsDel == 0).ToList();
List<InsuranceCostView> insuranceCostViews = new List<InsuranceCostView>();
foreach (var item in _InsuranceCosts)
{
InsuranceCostView costView = new InsuranceCostView();
costView.Id = item.Id;
costView.GName = item.GName;
costView.Balance = 0.00M;
string CountSql = string.Format(@"select sum(InsuranceCosts) as CountCost from Grp_Customers where isdel=0 and Iid=" + item.Id);
DataCountCost dataCount = _sqlSugar.SqlQueryable<DataCountCost>(CountSql).First();
costView.Balance = Math.Round(item.RechargeCost - dataCount.CountCost, 2);
insuranceCostViews.Add(costView);
}
//币种
GeneralTeamRateInfoDto PostGroupTeamRatedto = new GeneralTeamRateInfoDto();
PostGroupTeamRatedto.DiId = dto.DiId;
PostGroupTeamRatedto.CTable = 82;
PostGroupTeamRatedto.PortType = dto.PortType;
var _teamRate = await _teamRateRep.PostGroupTeamRateItemByDiIdAndCTableId(dto.PortType, dto.DiId, 82);
var data = new
{
Payment = _Payment,
CurrencyList = _teamRate,
BankCard = _BankCard,
insuranceCost = insuranceCostViews,
};
return result = new Result() { Code = 0, Msg = "查询成功", Data = data };
}
catch (Exception ex)
{
return result = new Result() { Code = -2, Msg = "未知错误" };
throw;
}
}
public async Task<Result> OpCustomers(OpCustomersDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
BeginTran();
int id = dto.Id;
Grp_Customers cus = _mapper.Map<Grp_Customers>(dto);
Grp_CreditCardPayment c = _mapper.Map<Grp_CreditCardPayment>(dto);
c.Remark = dto.CRemark;
c.PayPercentage = 100;
c.CTable = 82;
c.CId = id;
c.IsAuditGM = 3;
c.PayMoney = cus.InsuranceCosts;
c.PaymentCurrency = cus.Currency;
//if (c.PayDId == 72) c.IsPay = 1;
//else c.IsPay = 0;
c.RMBPrice = cus.InsuranceCosts;
c.DayRate = 1;
Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 82);
List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
if (_TeamRate != null)
{
Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == cus.Currency);
if (_SetData != null)
{
currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
if (CurrencyRate != null)
{
c.RMBPrice = c.PayMoney * Convert.ToDecimal(CurrencyRate.Rate);
c.DayRate = CurrencyRate.Rate;
}
}
}
if (dto.Status == 1)//添加
{
var customers = _sqlSugar.Queryable<Grp_Customers>()
.First(a => a.DiId == dto.DiId &&
a.IsDel == 0 &&
a.ClientName == dto.ClientName &&
a.InsuranceCosts == dto.InsuranceCosts &&
a.Currency == dto.Currency
);
if (customers != null)
{
return result = new Result() { Code = -1, Msg = "该笔费用已存在，请勿重复添加！" };
}
else
{
id = await AddAsyncReturnId(cus);
if (id != 0)
{
c.CId = id;
int cId = await _sqlSugar.Insertable(c).ExecuteReturnIdentityAsync();
if (cId != 0)
{
var data = new { ccpId = cId, sign = 1, dataId = id };
result = new Result() { Code = 0, Msg = "添加成功！", Data = data };
}
else
{
RollbackTran();
result = new Result() { Code = -1, Msg = "添加失败！" };
}
}
else
{
RollbackTran();
result = new Result() { Code = -1, Msg = "添加失败，请稍后重试！" };
}
}
}
else if (dto.Status == 2)//修改
{
bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_Customers
{
Iid = cus.Iid,
ClientName = cus.ClientName,
InsuranceCosts = cus.InsuranceCosts,
Currency = cus.Currency,
Attachment = cus.Attachment,
Remark = cus.Remark
});
if (res)
{
int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>()
.Where(a => a.CId == cus.Id && a.CTable == 82)
.SetColumns(a => new Grp_CreditCardPayment
{
PayDId = dto.PayDId,
PayMoney = c.PayMoney,
PaymentCurrency = c.PaymentCurrency,
Payee = c.Payee,
OrbitalPrivateTransfer = c.OrbitalPrivateTransfer,
DayRate = c.DayRate,
RMBPrice = c.RMBPrice,
ConsumptionPatterns = c.ConsumptionPatterns,
ConsumptionDate = c.ConsumptionDate,
CTDId = c.CTDId,
CompanyBankNo = c.CompanyBankNo,
OtherBankName = c.OtherBankName,
OtherSideNo = c.OtherSideNo,
OtherSideName = c.OtherSideName,
BankNo = c.BankNo,
CardholderName = c.CardholderName,
Remark = c.Remark,
IsAuditGM = c.IsAuditGM,
})
.ExecuteCommandAsync();
if (CTable == 0)
{
result = new Result() { Code = -1, Msg = "修改失败！" };
RollbackTran();
}
else
{
Grp_CreditCardPayment ccp = Query<Grp_CreditCardPayment>(a => a.CId == cus.Id && a.CTable == 82).First();
var data = new { ccpId = ccp.Id, sign = 2, dataId = dto.Id };
result = new Result() { Code = 0, Msg = "修改成功！", Data = data };
}
}
else
{
RollbackTran();
result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
}
}
CommitTran();
return result;
}
}
}