using AutoMapper;
using NPOI.SS.Formula.Functions;
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.Infrastructure.Repositories.System;
using OASystem.Infrastructure.Tools;
using Org.BouncyCastle.Asn1.Cms;
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 VisaPriceRepository : BaseRepository<Grp_VisaInfo, VisaPriceDto>
{
private readonly IMapper _mapper;
public VisaPriceRepository(SqlSugarClient sqlSugar,IMapper mapper)
: base(sqlSugar)
{
_mapper=mapper;
}
/// <summary>
/// 根据diid查询签证费用列表
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> PostVisaByDiId(VisaPriceDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
if (dto.PortType==0 ||string.IsNullOrWhiteSpace(dto.PortType.ToString()))
{
return result = new Result() { Code = -1, Msg = "请传入PortType参数，请求端口分类 1 Web 2 Android 3 IOS" };
}
try
{
#region SQL条件拼接
string sqlWhere = string.Empty;
if (!string.IsNullOrWhiteSpace(dto.VisaClient))
{
sqlWhere += string.Format(@" And v.VisaClient like '%{0}%'", dto.VisaClient);
}
sqlWhere += string.Format(@"And v.DIId={0} and v.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 == 80).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 v.CreateUserId in ({0})", UserId);
if (!string.IsNullOrEmpty(sqlWhere.Trim()))
{
Regex r = new Regex("And");
sqlWhere = r.Replace(sqlWhere, "Where", 1);
}
#endregion
int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
int endIndex = startIndex + dto.PageSize - 1;
if (dto.PortType==1)
{
string sql = string.Format(@"select *,(select IsAuditGM from Grp_CreditCardPayment where CTable=80 and CId=v.Id and IsDel=0) IsAuditGM,
(select Name from Sys_SetData where isdel=0 and v.VisaCurrency=Id) VisaCurrencyStr from
Grp_VisaInfo v {0} order by CreateTime desc",sqlWhere);
List<VisaInfoView> infoViews = _sqlSugar.SqlQueryable<VisaInfoView>(sql).ToList();
var parseIntValue = 0;
var CrmIds = (from item in infoViews
where item.VisaClient.Contains(",") || int.TryParse(item.VisaClient, out parseIntValue)
select item.VisaClient).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 (VisaInfoView view in infoViews)
{
if (view.IsAuditGM == 0) view.IsAuditGMStr = "未审核";
else if (view.IsAuditGM == 1) view.IsAuditGMStr = "已通过";
else if (view.IsAuditGM == 2) view.IsAuditGMStr = "未通过";
else if (view.IsAuditGM == 3) view.IsAuditGMStr = "自动审核";
{
if (view.VisaClient.Contains(","))
{
var splitArr = view.VisaClient.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 += " ";
}
}
if (!string.IsNullOrWhiteSpace(name))
{
view.VisaClient = name.TrimEnd();
}
continue;
}
}
if (int.TryParse(view.VisaClient, out parseIntValue))
{
var name = string.Empty;
var clientObject = clietArr.Find(x => x.Id == parseIntValue);
name += (clientObject?.LastName + clientObject?.FirstName);
if (!string.IsNullOrWhiteSpace(name))
{
view.VisaClient = name;
}
continue;
}
}
return result = new Result() { Code = 0, Msg = "查询成功！", Data = infoViews };
}
else if(dto.PortType==2 || dto.PortType==3)
{
string sql = string.Format(@"Select * From (
Select row_number() over (order by v.Id Desc) as RowNumber,v.*,c.IsAuditGM,s.Name as VisaCurrencyStr
From Grp_VisaInfo v
left Join Grp_CreditCardPayment c On v.Id = c.CId and CTable=80 and c.isdel=0
Left Join Sys_SetData s On v.VisaCurrency = s.Id {0}
) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);
List<VisaInfoView> infoViews = _sqlSugar.SqlQueryable<VisaInfoView>(sql).ToList();
var parseIntValue = 0;
var CrmIds = (from item in infoViews
where item.VisaClient.Contains(",") || int.TryParse(item.VisaClient, out parseIntValue)
select item.VisaClient).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 (VisaInfoView view in infoViews)
{
if (view.IsAuditGM == 0) view.IsAuditGMStr = "未审核";
else if (view.IsAuditGM == 1) view.IsAuditGMStr = "已通过";
else if (view.IsAuditGM == 2) view.IsAuditGMStr = "未通过";
else if (view.IsAuditGM == 3) view.IsAuditGMStr = "自动审核";
{
if (view.VisaClient.Contains(","))
{
var splitArr = view.VisaClient.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))
{
view.VisaClient = name.TrimEnd();
}
continue;
}
}
if (int.TryParse(view.VisaClient, out parseIntValue))
{
var name = string.Empty;
var clientObject = clietArr.Find(x => x.Id == parseIntValue);
name += (clientObject?.LastName + clientObject?.FirstName);
if (!string.IsNullOrWhiteSpace(name))
{
view.VisaClient = name;
}
continue;
}
}
string CountSql = string.Format(@"Select COUNT(1) as Count From (
Select v.*,c.IsAuditGM,s.Name as VisaCurrencyStr
From Grp_VisaInfo v
left Join Grp_CreditCardPayment c On v.Id = c.CId and CTable=80 and c.isdel=0
Left Join Sys_SetData s On v.VisaCurrency = s.Id {0}
) temp", sqlWhere);
DataCount dataCount = _sqlSugar.SqlQueryable<DataCount>(CountSql).First();
if (dataCount!=null)
{
int count = dataCount.Count;
float totalPage = (float)count / dto.PageSize;//总页数
if (totalPage == 0) totalPage = 1;
else totalPage = (int)Math.Ceiling((double)totalPage);
ListViewBase<VisaInfoView> rst = new ListViewBase<VisaInfoView>();
rst.DataList = infoViews;
rst.DataCount = count;
rst.CurrPageIndex = dto.PageIndex;
rst.CurrPageSize = dto.PageSize;
return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };
}
}
}
catch (Exception)
{
return result = new Result() { Code = -2, Msg = "未知错误" };
throw;
}
return result;
}
/// <summary>
/// 根据签证费用Id查询单条数据及c表数据
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> PostVisaById(PostVisaByIdDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
Grp_VisaInfo _VisaInfo = _sqlSugar.Queryable<Grp_VisaInfo>().First(a => a.Id == dto.Id && a.IsDel==0);
Grp_CreditCardPayment _CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a=>a.CId==dto.Id && a.IsDel==0 && a.CTable==80);
var data = new
{
VisaInfo = _VisaInfo,
CreditCardPayment = _CreditCardPayment
};
return result = new Result() { Code = 0, Msg = "暂无数据！", Data = data };
}
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> OpVisaPrice(OpVisaPriceDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
BeginTran();
try
{
int id = 0;
Grp_VisaInfo grp_Visa1 = _mapper.Map<Grp_VisaInfo>(dto);
Grp_CreditCardPayment c = _mapper.Map<Grp_CreditCardPayment>(dto);
c.PayMoney = dto.VisaPrice;
c.PaymentCurrency = dto.VisaCurrency;
c.Remark = dto.CRemark;
c.PayPercentage = 100;
c.CTable = 80;
c.CId = dto.Id;
c.IsAuditGM = 0;
if (c.PayDId == 72)
{
c.IsPay = 1;
}
c.RMBPrice = c.PayMoney;
c.DayRate = 1;
Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 80);
List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
if (_TeamRate != null)
{
Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == c.PaymentCurrency);
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)//添加
{
Grp_VisaInfo grp_Visa =_sqlSugar.Queryable<Grp_VisaInfo>().First(a=>a.IsDel==0 && a.VisaCurrency==dto.VisaCurrency && a.VisaPrice==dto.VisaPrice && a.VisaClient==dto.VisaClient);
if (grp_Visa!=null)
{
return result = new Result() { Code = -1, Msg = "该笔费用已存在，请勿重复添加！" };
}
else
{
id = await AddAsyncReturnId(grp_Visa1);
if (id!=0)
{
c.CId = id;
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
{
RollbackTran();
result = new Result() { Code = -1, Msg = "添加失败，请稍后重试！" };
}
}
}
else if (dto.Status==2)//修改
{
bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_VisaInfo
{
VisaClient = grp_Visa1.VisaClient,
VisaPrice = grp_Visa1.VisaPrice,
VisaCurrency = grp_Visa1.VisaCurrency,
IsThird = grp_Visa1.IsThird,
PassengerType = grp_Visa1.PassengerType,
VisaNumber = grp_Visa1.VisaNumber,
VisaFreeNumber = grp_Visa1.VisaFreeNumber,
Remark = dto.Remark,
});
if (res)
{
int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == grp_Visa1.Id && a.CTable==80).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,
}).ExecuteCommandAsync();
result = new Result() { Code = 0, Msg = "修改成功！" };
}
else
{
RollbackTran();
result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
}
}
CommitTran();
}
catch (Exception ex)
{
result = new Result() { Code = -2, Msg = "未知错误" };
}
return result;
}
}
}