using Newtonsoft.Json;
using OASystem.Domain;
using OASystem.Domain.Dtos;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Dtos.UserDto;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.CRM;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Repositories.System;
using Org.BouncyCastle.Asn1.Cms;
using Org.BouncyCastle.Utilities.Collections;
using Serilog;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace OASystem.Infrastructure.Repositories.Groups
{
/// <summary>
/// 接团信息
/// </summary>
public class DelegationInfoRepository : BaseRepository<Grp_DelegationInfo, DelegationInfoView>
{
private readonly SetDataRepository _setDataRepository;
private readonly UsersRepository _usersRepository;
//public readonly Logs _logs;
public DelegationInfoRepository(SqlSugarClient sqlSugar, SetDataRepository setDataRepository, UsersRepository usersRepository)
: base(sqlSugar)
{
this._setDataRepository = setDataRepository;
this._usersRepository = usersRepository;
}
#region 团组
/// <summary>
/// 获取接团信息 Page List
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetGroupPageList(GroupListPageDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
ListViewBase<DelegationPageListView> groupsInfoPageList = new ListViewBase<DelegationPageListView>()
{
ReceiveDt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
};
if (dto.PortType == 1 || dto.PortType == 2) //web
{
int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
int endIndex = startIndex + dto.PageSize - 1;
string sql = string.Format(@"Select * From(Select row_number() over (order by gdi.Id Desc) as RowNumber,
gdi.Id,SalesQuoteNo,TourCode,ssd.Id TeamTypeId, ssd.Name TeamType,ssd1.Id TeamLevId,
ssd1.Name TeamLev,TeamName,ClientName,ClientUnit,VisitDate,VisitDays,VisitPNumber,
su.CnName JietuanOperator,IsSure
From Grp_DelegationInfo gdi
Inner Join Sys_SetData ssd On gdi.TeamDid = ssd.Id
Inner Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
Left Join Sys_Users su On gdi.JietuanOperator = su.Id
Where gdi.IsDel = 0
)temp Where RowNumber Between {0} and {1}", startIndex, endIndex);
string sqlCount = string.Format(@"Select Count(1) as Count From Grp_DelegationInfo gdi
Inner Join Sys_SetData ssd On gdi.TeamDid = ssd.Id
Inner Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
Left Join Sys_Users su On gdi.JietuanOperator = su.Id
Where gdi.IsDel = 0 ");
List<DelegationPageListView> _DeleInfoList = await _sqlSugar.SqlQueryable<DelegationPageListView>(sql).ToListAsync();
DelegationPageCountView _DeleCount = await _sqlSugar.SqlQueryable<DelegationPageCountView>(sql).FirstAsync();
int count = _DeleCount.Count;
groupsInfoPageList = new ListViewBase<DelegationPageListView>
{
CurrPageIndex = dto.PageIndex,
CurrPageSize = dto.PageSize,
DataCount = count,
DataList = _DeleInfoList
};
result.Code = 0;
result.Msg = "成功!";
result.Data = groupsInfoPageList;
}
return result;
}
/// <summary>
/// 获取接团信息List
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetGroupList(GroupListDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
if (dto.PortType == 1 || dto.PortType == 2) //web
{
string sql = string.Format(@"Select gdi.Id,SalesQuoteNo,TourCode,ssd.Id TeamTypeId, ssd.Name TeamType,ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,ClientName,ClientUnit,VisitDate,VisitDays,VisitPNumber,su.CnName JietuanOperator,IsSure
From Grp_DelegationInfo gdi
Inner Join Sys_SetData ssd On gdi.TeamDid = ssd.Id
Inner Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
Left Join Sys_Users su On gdi.JietuanOperator = su.Id
Where gdi.IsDel = 0
Order By gdi.CreateTime Desc");
var _DelegationList = await _sqlSugar.SqlQueryable<DelegationListView>(sql).ToListAsync();
if (_DelegationList.Count > 0)
{
result.Code = 0;
result.Msg = "成功!";
result.Data = _DelegationList;
}
else
{
result.Msg = "暂无该团组信息";
}
}
return result;
}
/// <summary>
/// 获取接团信息Info
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetGroupInfo(GroupInfoDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
if (dto.PortType == 1 || dto.PortType == 2) //web
{
string sql = string.Format(@"Select Id,SalesQuoteNo,JietuanOperator,TeamLevSId,TeamDid,TeamName,ClientName,
ClientUnit,VisitCountry,VisitDate,VisitDays,VisitPNumber,TontractTime,
PayDay,PaymentMoney,VisitPurpose,SpecialNeeds,OtherNeeds,CGRWSPBMMC,CGRWSPWH,
ZZSCBMMC,ZZSCSPWH,Remark,TellPhone
From Grp_DelegationInfo Where Id = {0} And IsDel = 0", dto.Id);
var _DelegationInfo = await _sqlSugar.SqlQueryable<DelegationInfoWebView>(sql).FirstAsync();
if (_DelegationInfo != null)
{
result.Code = 0;
result.Msg = "成功!";
result.Data = _DelegationInfo;
}
else
{
result.Msg = "暂无该团组信息";
}
}
return result;
}
/// <summary>
/// 获取接团信息 编辑
/// 基础 数据源
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GroupEditBasicSource(GroupListDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
if (dto.PortType == 1 || dto.PortType == 2) //web
{
dynamic? teamTypeData1 = null;
string teamTypeSql = string.Format(@"Select Id,Name From Sys_SetData Where STid = {0} And IsDel = {1}", 10, 0);
var teamTypeData = await _sqlSugar.SqlQueryable<SetDataInfoView>(teamTypeSql).ToListAsync(); ; //团组分类 10
if (teamTypeData.Count > 0)
{
teamTypeData1 = teamTypeData;
}
dynamic? teamLevData1 = null;
string teamLevSql = string.Format(@"Select Id,Name From Sys_SetData Where STid = {0} And IsDel = {1}", 56, 0);
var teamLevData = await _sqlSugar.SqlQueryable<SetDataInfoView>(teamLevSql).ToListAsync(); ; //团组等级 56
if (teamLevData.Count > 0)
{
teamLevData1 = teamLevData;
}
dynamic? userData1 = null;
string userSql = string.Format(@"Select Id,CnName From Sys_Users Where IsDel = {0}", 0);
var userData = await _sqlSugar.SqlQueryable<UserNameView>(userSql).ToListAsync(); ;
if (userData.Count > 0)
{
userData1 = userData;
}
result.Code = 0;
result.Msg = "成功!";
result.Data = new
{
teamTypeData = teamTypeData1,
teamLevData = teamLevData1,
userData = userData1
};
}
}
catch (Exception)
{
throw;
}
return result;
}
/// <summary>
/// 团组信息操作
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GroupOperation(GroupOperationDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
if (dto.PortType == 1 || dto.PortType == 2) //web
{
if (dto.Status == 1) //添加
{
string selectSql = string.Format(@"Select * From Grp_DelegationInfo
Where IsDel = 0
And TeamName = '{0}'
And ClientName = '{1}'
And ClientUnit ='{2}'
And VisitDate ='{3}'",dto.TeamName, dto.ClientName, dto.ClientUnit, dto.VisitDate);
var selectGroupInfo = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(selectSql).FirstAsync();
if (selectGroupInfo != null)
{
result.Msg = "数据已存在,请勿重复添加!";
return result;
}
//string teamCodeSql = string.Format("Select SalesQuoteNo From Grp_DelegationInfo");
//var teamCodeItem = await _sqlSugar.SqlQueryable<TeamCodeView>(teamCodeSql).ToListAsync();
//string SalesQuoteNo = "";
//while (true)
//{
// SalesQuoteNo = Tools.CommonFun.GetRandomStr(6);
// if (!teamCodeItem.Equals(SalesQuoteNo))
// {
// break;
// }
//}
Grp_DelegationInfo delegationInfo = new Grp_DelegationInfo()
{
SalesQuoteNo = dto.SalesQuoteNo,
TeamLevSId = dto.TeamLevSId,
ClientName = dto.ClientName,
ClientUnit = dto.ClientUnit,
TeamName = dto.TeamName,
TeamDid = dto.TeamDid,
VisitDate = Convert.ToDateTime(dto.VisitDate),
VisitCountry = dto.VisitCountry,
VisitPurpose = dto.VisitPurpose,
VisitDays = dto.VisitDays,
VisitPNumber = dto.VisitPNumber,
SpecialNeeds = dto.SpecialNeeds,
OtherNeeds = dto.OtherNeeds,
Remark = dto.Remark,
JietuanOperator = dto.UserId,
TellPhone = dto.TellPhone,
CGRWSPBMMC = dto.CGRWSPBMMC,
CGRWSPWH = dto.CGRWSPWH,
ZZSCBMMC = dto.ZZSCBMMC,
ZZSCSPWH = dto.ZZSCSPWH,
TontractTime = Convert.ToDateTime(dto.TontractTime),
PaymentMoney = dto.PaymentMoney,
PayDay = dto.PayDay,
TourCode = "",
SalesDate = DateTime.Now,
IsState = 0, //默认团组未完成 0
JietuanTime = DateTime.Now,
IsDel = 0,
BudgetCost = 0.00M,
HotelComplain = 0,
OPComplain = 0,
OAComplain = 0,
AirComplain = 0,
VisaComplain = 0,
CreateUserId = dto.UserId,
CreateTime = DateTime.Now,
DeleteUserId = null,
DeleteTime = new DateTime(1990, 1, 1).ToString("yyyy-MM-dd HH:mm:ss")
};
Log.Information("添加："+ JsonConvert.SerializeObject(delegationInfo));
var addId = await _sqlSugar.Insertable(delegationInfo).ExecuteReturnIdentityAsync();
if (addId > 0)
{
result.Code = 0;
result.Msg = "添加成功!";
}
else
{
result.Msg = "添加失败!";
}
}
else if (dto.Status == 2) //修改
{
var updateStatus = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_DelegationInfo
{
SalesQuoteNo = dto.SalesQuoteNo,
TeamLevSId = dto.TeamLevSId,
ClientName = dto.ClientName,
ClientUnit = dto.ClientUnit,
TeamName = dto.TeamName,
TeamDid = dto.TeamDid,
VisitDate = Convert.ToDateTime(dto.VisitDate),
VisitCountry = dto.VisitCountry,
VisitPurpose = dto.VisitPurpose,
VisitDays = dto.VisitDays,
VisitPNumber = dto.VisitPNumber,
SpecialNeeds = dto.SpecialNeeds,
OtherNeeds = dto.OtherNeeds,
Remark = dto.Remark,
JietuanOperator = dto.UserId,
TellPhone = dto.TellPhone,
CGRWSPBMMC = dto.CGRWSPBMMC,
CGRWSPWH = dto.CGRWSPWH,
ZZSCBMMC = dto.ZZSCBMMC,
ZZSCSPWH = dto.ZZSCSPWH,
TontractTime = Convert.ToDateTime(dto.TontractTime),
PaymentMoney = dto.PaymentMoney,
PayDay = dto.PayDay
});
if (updateStatus)
{
result.Code = 0;
result.Msg = "修改成功!";
}
else
{
result.Msg = "修改失败!";
}
}
else if (dto.Status == 3) //删除
{
var deleteStatus = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_DelegationInfo
{
DeleteUserId = dto.UserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
IsDel = 1
});
if (deleteStatus)
{
result.Code = 0;
result.Msg = "删除成功!";
}
else
{
result.Msg = "删除失败!";
}
}
}
return result;
}
/// <summary>
/// 团组信息操作 - 删除
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GroupDel(GroupDelDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
if (dto.PortType == 1 || dto.PortType == 2) //web
{
var deleteStatus = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_DelegationInfo
{
DeleteUserId = dto.UserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
IsDel = 1
});
if (deleteStatus)
{
result.Code = 0;
result.Msg = "删除成功!";
}
else
{
result.Msg = "删除失败!";
}
}
return result;
}
/// <summary>
/// 获取销售报价号
/// </summary>
/// <returns></returns>
public async Task<Result> GetGroupSalesQuoteNo()
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
string teamCodeSql = string.Format("Select SalesQuoteNo From Grp_DelegationInfo");
var teamCodeItem = await _sqlSugar.SqlQueryable<SalesQuoteNoView>(teamCodeSql).ToListAsync();
string teamCode = "";
while (true)
{
teamCode = Tools.CommonFun.GetRandomAllStr(6);
if (!teamCodeItem.Equals(teamCode))
{
break;
}
}
result.Code = 0;
result.Msg = "成功!";
result.Data = teamCode;
return result;
}
/// <summary>
/// 团组删除
/// </summary>
/// <returns></returns>
public async Task<Result> GroupDeleteById()
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
string teamCodeSql = string.Format("Select TourCode From Grp_DelegationInfo");
var teamCodeItem = await _sqlSugar.SqlQueryable<TeamCodeView>(teamCodeSql).ToListAsync();
string teamCode = "";
while (true)
{
teamCode = Tools.CommonFun.GetRandomAllStr(6);
if (!teamCodeItem.Equals(teamCode))
{
break;
}
}
result.Code = 0;
result.Msg = "成功!";
result.Data = teamCode;
return result;
}
/// <summary>
/// 团组确认出团
/// </summary>
/// <returns></returns>
public async Task<Result> ConfirmationGroup(ConfirmationGroupDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
string teamCode = "";
if (dto.PortType == 1 || dto.PortType == 2) //web
{
string teamCodeSql = string.Format("Select TourCode From Grp_DelegationInfo");
var teamCodeItem = await _sqlSugar.SqlQueryable<TeamCodeView>(teamCodeSql).ToListAsync();
while (true)
{
teamCode = Tools.CommonFun.GetRandomAllStr(6);
if (!teamCodeItem.Equals(teamCode))
{
break;
}
}
var deleteStatus = await UpdateAsync(a => a.Id == dto.GroupId, a => new Grp_DelegationInfo
{
TourCode = teamCode,
GroupsOperator = dto.GroupsOperator,
GroupsTime = DateTime.Now
});
if (deleteStatus)
{
result.Code = 0;
result.Msg = "确认出团成功!";
}
else
{
result.Msg = "确认出团失败!";
}
}
result.Code = 0;
result.Msg = "成功!";
result.Data = teamCode;
return result;
}
/// <summary>
/// 获取接团名称List
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetGroupNameList(GroupNameDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
if (dto.PortType == 1 || dto.PortType == 2) //web
{
string sql = string.Format(@"Select Id,TeamName GroupName From Grp_DelegationInfo
Where TeamName != '' And IsDel = 0
Order By Id Desc");
var _groupNameList = await _sqlSugar.SqlQueryable<GroupNameView>(sql).ToListAsync();
if (_groupNameList.Count > 0)
{
result.Code = 0;
result.Msg = "成功!";
result.Data = _groupNameList;
}
else
{
result.Msg = "暂无团组信息";
}
}
return result;
}
/// <summary>
/// 获取接团名称List And 签证国别
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetGroupNameAndVisaNationality(GroupNameDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
if (dto.PortType == 1 || dto.PortType == 2) //web
{
string sql = string.Format(@"Select Id,TeamName GroupName From Grp_DelegationInfo
Where TeamName != '' And IsDel = 0
Order By Id Desc");
var _groupNameList = await _sqlSugar.SqlQueryable<GroupNameView>(sql).ToListAsync();
string visaSql = string.Format(@"Select Id,Name From Sys_SetData Where STid = 41 And IsDel = 0");
var _setDataList = await _sqlSugar.SqlQueryable<SetDataInfoView>(visaSql).ToListAsync();
var data = new {
groupNameData = _groupNameList,
visaNationalityData = _setDataList
};
result.Data = data;
result.Code = 0;
}
return result;
}
#endregion
#region 团组&签证
/// <summary>
/// 根据团组Id查询客户
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<Result> GetCrmByGroupId(ClientByGroupIdDto dto)
{
Result result = new Result() { Code = -2 };
if (dto.PortType == 1 || dto.PortType == 2)
{
string sql = string.Format(@"Select gdjc.GrpDCId grpId,cdc.Id,cdc.LastName+cdc.FirstName clientName,cdc.Tel,ccc.CertNo CerdNo
From Grp_DelegationJoinCustomer gdjc
Inner join Crm_DeleClient cdc On gdjc.CrmDCId = cdc.Id
Left Join Crm_CustomerCert ccc On ccc.SdId = 773 And cdc.Id = ccc.DcId
Where gdjc.GrpDCId = {0}", dto.GroupId);
var clientList = await _sqlSugar.SqlQueryable<CrmByGroupIdView>(sql).ToListAsync();
if (clientList.Count > 0)
{
result.Code = 0;
result.Msg = "成功!";
result.Data = clientList;
}
else
{
result.Msg = "暂无数据!";
}
}
return result;
}
#endregion
}
}