using Aspose.Cells;
using Aspose.Words;
using Aspose.Words.Layout;
using Aspose.Words.Saving;
using Microsoft.AspNetCore.SignalR;
using Microsoft.International.Converters.PinYinConverter;
using OASystem.API.OAMethodLib.APNs;
using OASystem.API.OAMethodLib.File;
using OASystem.API.OAMethodLib.Hub.HubClients;
using OASystem.API.OAMethodLib.Hub.Hubs;
using OASystem.API.OAMethodLib.JuHeAPI;
using OASystem.API.OAMethodLib.SignalR.Hubs;
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.JuHeExchangeRate;
using OASystem.Infrastructure.Repositories.CRM;
using OASystem.Infrastructure.Repositories.Groups;
using OASystem.Infrastructure.Repositories.Login;
using OfficeOpenXml;
using System.Data;
using System.IdentityModel.Tokens.Jwt;
using System.Security.Claims;
namespace OASystem.API.OAMethodLib
{
public static class GeneralMethod
{
//团组信息
private static readonly SqlSugarClient _sqlSugar = AutofacIocManager.Instance.GetService<SqlSugarClient>();
private static readonly DelegationInfoRepository _dirRep = AutofacIocManager.Instance.GetService<DelegationInfoRepository>();
private static readonly TeamRateRepository _teamRateRep = AutofacIocManager.Instance.GetService<TeamRateRepository>();
private static readonly IJuHeApiService _juHeApi = AutofacIocManager.Instance.GetService<IJuHeApiService>();
private static readonly SetDataRepository _setDataRep = AutofacIocManager.Instance.GetService<SetDataRepository>();
private static readonly TableOperationRecordRepository _tableOperationRecordRep = AutofacIocManager.Instance.GetService<TableOperationRecordRepository>();
private static readonly MessageRepository _messageRep = AutofacIocManager.Instance.GetService<MessageRepository>();
private static readonly IHubContext<ChatHub, IChatClient> _hubContext = AutofacIocManager.Instance.GetService<IHubContext<ChatHub, IChatClient>>();
private readonly static string[] weekdays = new string[] { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
#region 员工注册默认添加基础页面
/// <summary>
/// 默认职位权限
/// </summary>
public static bool DefaultPostAuth(int depId, int postId, int userId, int createUserId)
{
if (depId < 1) return false;
if (postId < 1) return false;
if (userId < 1) return false;
if (userId < 1) return false;
List<DefaultPostPageAuthView> _defaultData = AppSettingsHelper.Get<DefaultPostPageAuthView>("DefaultPostPageData");
if (_defaultData.Count < 1) return false;
var postJobDatas = _dirRep._sqlSugar.Queryable<Sys_JobPost>().Where(it => it.IsDel == 0).ToList();
List<int> pageData = new List<int>();
//添加公司公共页面
pageData.AddRange(_defaultData.Find(it => it.DepId == -1)?.PostPageAuths[0].PageIds ?? new List<int>());
//添加部门页面
var depPublicPageData = _defaultData.Find(it => it.DepId == depId);
var jobPublicPageData = postJobDatas.Find(it => it.DepId == depId && it.Id == postId);
if (depPublicPageData != null) //特殊部门
{
//公共页面
pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == -1)?.PageIds ?? new List<int>());
if (depId == 7) //国交部特殊处理
{
List<string> postNames = new List<string>() { "主管", "经理" };
//岗位页面
if (jobPublicPageData != null)
{
if (postNames.Contains(jobPublicPageData.JobName))
{
pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == 0)?.PageIds ?? new List<int>());
}
else
{
pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == postId)?.PageIds ?? new List<int>());
}
}
}
}
else //通用部门
{
pageData.AddRange(_defaultData.Find(it => it.DepId == 0)?.PostPageAuths[0].PageIds ?? new List<int>());
}
if (pageData.Count > 0)
{
//页面操作权限数据(添加修改等...)
var pageFunctionData = _dirRep._sqlSugar.Queryable<Sys_SystemMenuAndFunction>().Where(it => pageData.Contains(it.SmId) && it.IsDel == 0).ToList();
var defaultPageData = pageFunctionData.Select(it => new Sys_UserAuthority()
{
UId = userId,
SmId = it.SmId,
FId = it.FId,
CreateUserId = createUserId, //管理员
CreateTime = DateTime.Now,
IsDel = 0,
IsTemp = 1
}).ToList();
if (defaultPageData.Count > 0)
{
//移除每个页面的审核权限，审核权限需要单独开启
defaultPageData = defaultPageData.Where(it => it.FId != 12).ToList();
//添加页面操作权限
var s = _dirRep._sqlSugar.Fastest<Sys_UserAuthority>().PageSize(100000).BulkCopy(defaultPageData);
if (s > 0)
{
return true;
}
}
}
return false;
}
#endregion
#region 消息
/// <summary>
/// 消息 发布And 通知
/// </summary>
/// <param name="msgTypeEnum">
/// 消息类型
/// 1 公告通知
/// 2 团组流程管控通知
/// 3 团组业务操作通知
/// 4 团组费用审核消息
/// 5 团组签证进度通知
/// 6 团组任务进度通知
/// 7 日付申请通知
/// </param>
/// <param name="title">消息标题</param>
/// <param name="content">消息内容</param>
/// <param name="userIds"></param>
/// <param name="diId">团组id</param>
/// <param name="param">团组id</param>
/// <returns></returns>
public static async Task<bool> MessageIssueAndNotification(MessageTypeEnum msgTypeEnum, string title, string content, List<int> userIds, int diId = 0, string param = "")
{
MessageDto messageDto = new()
{
Type = msgTypeEnum,
IssuerId = 4,//管理员
DiId = diId,
Title = title,
Content = content,
ReleaseTime = DateTime.Now,
UIdList = userIds,
Param = param
};
var status = await _messageRep.AddMsg(messageDto);//添加消息
if (status)
{
//给在线在用户发送消息
var connIds = UserStore.OnlineUser.Where(it => userIds.Contains(it.UserId)).Select(it => it.ConnectionId).ToList();
var notificationTypeStr1 = GetMsgNotificationType(msgTypeEnum);
var notificationTypeStr = JsonConvert.SerializeObject(
new
{
UserIds = userIds,
Msg = $"您有一条{notificationTypeStr1}相关的消息!"
}
);
await _hubContext.Clients.Clients(connIds).ReceiveMessage($"您有一条{notificationTypeStr1}相关的消息!");
return true;
}
return false;
}
/// <summary>
/// 根据消息类型 获取 消息通知类型
/// </summary>
/// <returns></returns>
public static string GetMsgNotificationType(MessageTypeEnum msgTypeEnum)
{
int notificationType = 0;
string notificationStr = "";
List<NotificationTypeView> notificationTypeViews = AppSettingsHelper.Get<NotificationTypeView>("MessageNotificationType");
notificationType = notificationTypeViews.Where(it => it.MsgTypeIds.Contains((int)msgTypeEnum)).Select(it => it.TypeId).FirstOrDefault();
if (notificationType == 1021) notificationStr = "操作";
else if (notificationType == 1020) notificationStr = "任务";
return notificationStr;
}
#endregion
#region md5 加密
/// <summary>
/// MD5加密,和动网上的16/32位MD5加密结果相同,
/// 使用的UTF8编码
/// </summary>
/// <param name="source">待加密字串</param>
/// <param name="length">16或32值之一,其它则采用.net默认MD5加密算法</param>
/// <returns>加密后的字串</returns>
public static string Encrypt(string source, int length = 32)
{
if (string.IsNullOrWhiteSpace(source))
return string.Empty;
HashAlgorithm hashAlgorithm = CryptoConfig.CreateFromName("MD5") as HashAlgorithm;
byte[] bytes = Encoding.UTF8.GetBytes(source);
byte[] hashValue = hashAlgorithm.ComputeHash(bytes);
StringBuilder sb = new StringBuilder();
switch (length)
{
case 16://16位密文是32位密文的9到24位字符
for (int i = 4; i < 12; i++)
{
sb.Append(hashValue[i].ToString("x2"));
}
break;
case 32:
for (int i = 0; i < 16; i++)
{
sb.Append(hashValue[i].ToString("x2"));
}
break;
default:
for (int i = 0; i < hashValue.Length; i++)
{
sb.Append(hashValue[i].ToString("x2"));
}
break;
}
return sb.ToString();
}
#endregion
#region jwt
/// <summary>
/// 获取token
/// </summary>
/// <param name="_config"></param>
/// <param name="Number"></param>
/// <param name="exp"></param>
/// <returns></returns>
public static async Task<string> GetToken(IConfiguration _config, string Number, int uId, string uName, DateTime exp)
{
string userId = Guid.NewGuid().ToString().Replace("-", "");
var claims = new[] {
new Claim(ClaimTypes.NameIdentifier, uName),
new Claim(ClaimTypes.NameIdentifier, uId.ToString()),
new Claim(ClaimTypes.NameIdentifier, userId),
new Claim("Number",Number)
};
var key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(_config["JwtSecurityKey"]));
var creds = new SigningCredentials(key, SecurityAlgorithms.HmacSha256);
var token = new JwtSecurityToken(
issuer: "OASystem.com",
audience: "OASystem.com",
claims: claims,
expires: exp,
signingCredentials: creds);
var identity = new ClaimsIdentity(claims, "login");
var principal = new ClaimsPrincipal(identity);
// await _httpContext.SignInAsync (CookieAuthenticationDefaults.AuthenticationScheme, principal);
return new JwtSecurityTokenHandler().WriteToken(token);
}
#endregion
#region 数据类型转换
/// <summary>
/// object 转 Int
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static int GetInt(this object obj)
{
if (obj == null)
return 0;
var number = 0;
var reslut = Int32.TryParse(obj.ToString(), out number);
return number;
}
private static DateTime _dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
private static long _longTime = 621355968000000000;
private static int _samllTime = 10000000;
/// <summary>
/// 时间戳 转 datetime
/// </summary>
/// <param name="timeStamp"></param>
/// <returns></returns>
public static DateTime GetTimeSpmpToDate(this object timeStamp)
{
if (timeStamp == null) return _dateStart;
DateTime dateTime = new DateTime(_longTime + Convert.ToInt64(timeStamp) * _samllTime, DateTimeKind.Utc).ToLocalTime();
return dateTime;
}
#endregion
#region 用户页面操作功能 权限
/// <summary>
/// 用户页面操作功能(可使用)
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="PageId">页面Id</param>
/// <returns></returns>
public static async Task<PageFunAuthViewBase> PostUserPageFuncDatas(int userId, int PageId)
{
PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
List<UserPageFuncView> userPageFuncDatas = new List<UserPageFuncView>();
string sql = string.Format(@"Select ua.UId As UserId, u.CnName As UserName, pa.ModuleId,pa.ModuleName,pa.PageId,pa.PageName,pa.PageIsEnable,
pa.PagePhoneIsEnable,pa.FuncId,pa.FuncName,pa.FuncIsEnable
From Sys_UserAuthority ua
Left Join Sys_Users u On ua.UId = u.Id
Left Join (
Select sd.Id As ModuleId,sd.Name As ModuleName, smp.Id As PageId,smp.Name As PageName,smp.IsEnable As PageIsEnable,
smp.phoneIsEnable As PagePhoneIsEnable,pfp.Id As FuncId,pfp.FunctionName As FuncName,pfp.IsEnable As FuncIsEnable
From Sys_SystemMenuAndFunction smaf
Left Join Sys_SystemMenuPermission smp On smaf.SmId = smp.Id
Left Join Sys_SetData sd On sd.STid = 5 And smp.Mid = sd.Id
Left Join Sys_PageFunctionPermission pfp On smaf.FId = pfp.Id
Where smaf.IsDel = 0 And smp.IsDel = 0 And pfp.IsDel = 0 And sd.IsDel = 0 And smp.IsEnable = 1
) As pa On ua.SmId = pa.PageId And ua.FId = pa.FuncId
Where ua.IsDel = 0 And ua.UId = {0} And pa.PageId = {1}
Order By ModuleId,PageId,FuncId Asc", userId, PageId);
userPageFuncDatas = await _dirRep._sqlSugar.SqlQueryable<UserPageFuncView>(sql).ToListAsync();
if (userPageFuncDatas.Count <= 0)
{
return pageFunAuth;
}
UserPageFuncView userPageFunc = new UserPageFuncView();
//查询 1
userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 1).FirstOrDefault();
if (userPageFunc != null) pageFunAuth.CheckAuth = 1;
//删除 2
userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 2).FirstOrDefault();
if (userPageFunc != null) pageFunAuth.DeleteAuth = 1;
//编辑 3
userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 3).FirstOrDefault();
if (userPageFunc != null) pageFunAuth.EditAuth = 1;
//下载 4
userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 4).FirstOrDefault();
if (userPageFunc != null) pageFunAuth.FilesDownloadAuth = 1;
//上传 5
userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 5).FirstOrDefault();
if (userPageFunc != null) pageFunAuth.FilesUploadAuth = 1;
//添加 11
userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 11).FirstOrDefault();
if (userPageFunc != null) pageFunAuth.AddAuth = 1;
//审核 12
userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 12).FirstOrDefault();
if (userPageFunc != null) pageFunAuth.AuditAuth = 1;
return pageFunAuth;
}
#endregion
#region 业务模块 团组权限
/// <summary>
/// 业务模块 团组操作权限
/// 验证 并返回可操作的团
/// </summary>
/// <param name="diId">团组Id</param>
/// <param name="userId">用户Id</param>
/// <param name="CTable">业务模块Id</param>
/// <returns></returns>
public static async Task<Result> PostGroupOperationAuth(int diId, int userId, int CTable)
{
Result _result = new Result { Code = -1, Msg = "No Operation Authorty!" };
if (CTable < 1)
{
_result.Msg = "请填写正确的用户Id!";
return _result;
}
var data = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(it => it.DIId == diId && it.UId == userId && it.CTId == CTable && it.IsDel == 0).First();
if (data == null)
{
_result.Msg = "你没有本团下的该业务模块操作,请联系主管分配操作权限!";
}
else
{
_result.Code = 0;
}
return _result;
}
/// <summary>
/// 业务模块 团组操作权限
/// 返回可操作的团
/// </summary>
/// <param name="diId">团组Id</param>
/// <param name="userId">用户Id</param>
/// <param name="CTable">业务模块Id</param>
/// <returns></returns>
public static async Task<List<Web_ShareGroupInfoView>> PostOperationAuthReturnGroupInfosAsync(int userId, int CTable)
{
var shareGroupInfoViews = new List<Web_ShareGroupInfoView>();
var taskData = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(it => it.UId == userId && it.CTId == CTable && it.IsDel == 0).ToList();
if (taskData.Count < 1) return shareGroupInfoViews;
var diIds = string.Join(",", taskData.Select(it => it.DIId).ToList());
var sql = string.Format(@"Select Id,TeamName,TourCode,ClientName,VisitCountry,VisitStartDate,VisitEndDate,VisitDays,VisitPNumber
From Grp_DelegationInfo Where Id In({0}) And IsDel = 0 Order By Id Desc", diIds);
shareGroupInfoViews = await _dirRep._sqlSugar.SqlQueryable<Web_ShareGroupInfoView>(sql).ToListAsync();
return shareGroupInfoViews;
}
#endregion
#region 团组相关
#region 建团按国家默认添加汇率 / 默认权限分配
/// <summary>
/// 团组汇率
/// 建团时 添加基础汇率 CNY
/// 按国家 添加 默认币种
/// </summary>
/// <param name="userId"></param>
/// <param name="diId"></param>
/// <returns></returns>
public static async Task<Result> PostGroupRateAddInit(int userId, int diId)
{
Result result = new() { Code = -2 };
if (userId < 1)
{
result.Msg = string.Format(@"请传入正确的userId");
return result;
}
if (diId < 1)
{
result.Msg = string.Format(@"请传入正确的DiId");
return result;
}
//美元(USD):1.0000|欧元(EUR):1.0000|墨西哥比索(MXN):1.0000
string rateInit = string.Format(@"人民币(CNY):1.0000");
//var gropInfo = _dirRep._sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == diId).First();
//if (gropInfo == null)
//{
// result.Msg = $@"未查询到团组信息!";
// return result;
//}
var juheRateData = await _juHeApi.GetExchangeRateAsync();
if (juheRateData.Error_code != 0)
{
result.Msg = juheRateData.Reason;
return result;
}
List<ExchangeRateModel> exchangeRateModels = (List<ExchangeRateModel>)juheRateData.Result;
if (exchangeRateModels.Count <= 0)
{
result.Msg = $@"未查询到聚合接口汇率信息!";
return result;
}
if (exchangeRateModels.Count > 0)
{
var codes = _dirRep._sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();
for (int i = 0; i < exchangeRateModels.Count; i++)
{
string currencyName = exchangeRateModels[i].Name;
string code = "";
var currencyData = codes.Where(it => it.Remark == currencyName).FirstOrDefault();
if (currencyData != null)
{
code = currencyData.Name;
decimal currRate = 0.00M;
string MSellPri = exchangeRateModels[i].MSellPri;
if (!string.IsNullOrEmpty(MSellPri))
{
currRate = Convert.ToDecimal(MSellPri) / 100M;
}
rateInit += $@"|{currencyName}({code}):{currRate.ToString("#0.0000")}";
}
}
}
var cTableIds = _dirRep._sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 16).ToList();
if (cTableIds.Count < 1)
{
result.Msg = $@"未查询到操作模块信息!";
return result;
}
List<Grp_TeamRate> grp_TeamRates = new List<Grp_TeamRate>();
foreach (var item in cTableIds)
{
grp_TeamRates.Add(
new Grp_TeamRate() { DiId = diId, CTable = item.Id, Remark = rateInit, CreateUserId = userId }
);
}
if (grp_TeamRates.Count > 0)
{
var addId = _teamRateRep._sqlSugar.Insertable(grp_TeamRates).ExecuteCommand();
if (addId < 1)
{
result.Msg = string.Format(@"添加失败!");
return result;
}
}
result.Code = 0;
result.Msg = string.Format(@"操作成功!");
return result;
}
/// <summary>
/// 团组任务分配
/// 建团时 默认按照岗位分配权限
/// </summary>
/// <param name="userId"></param>
/// <param name="diId"></param>
/// <param name="companyId"></param>
/// <returns></returns>
public static async Task<Result> PostGroupAuthAddInit(int userId, int diId, int companyId = 2)
{
Result result = new() { Code = -2 };
if (userId < 0)
{
result.Msg = string.Format(@"请传入正确的userId");
return result;
}
if (companyId < 0)
{
result.Msg = string.Format(@"请传入正确的companyId");
return result;
}
if (diId < 0)
{
result.Msg = string.Format(@"请传入正确的DiId");
return result;
}
var usersData = await _teamRateRep._sqlSugar
.Queryable<Sys_Users>()
.LeftJoin<Sys_Department>((su, sd) => su.DepId == sd.Id && sd.IsDel == 0)
.LeftJoin<Sys_JobPost>((su, sd, sjp) => su.JobPostId == sjp.Id && sjp.IsDel == 0)
.Where(su => su.IsDel == 0)
.Select((su, sd, sjp) => new
{
su.DepId,
sd.DepName,
su.JobPostId,
sjp.JobName,
su.Id,
su.CnName
})
.ToListAsync();
/*
* 76 酒店预订 --> 国交部门 酒店,主管 岗位
* 77 行程 --> 国交部门 经理,主管 岗位
* 79 车/导游地接 --> 国交部门 OP,主管 岗位
* 80 签证 --> 国交部门 签证 岗位
* 81 邀请/公务活动 --> 国交部门 商邀 岗位
* 82 团组客户保险 --> 国交部 经理,主管 岗位
* 85 机票预订 --> 国交部门 机票 岗位
* 98 其他款项 --> 总经办部门 总经理 岗位/国交部门(ALL) 岗位/财务部门(ALL) 岗位/策划部门(ALL) 岗位/人事部门 采购 岗位
* 1015 超支费用,69 收款退还 --> 财务部门(ALL)岗位
* 1015 超支费用 --> 国交部(ALL)岗位
* 1081 文档下载 --> 国交部门 酒店,主管 岗位
*/
var groupsTaskAssignments = new List<Grp_GroupsTaskAssignment>();
// 76 酒店预订 --> 国交部门(7) 酒店(25)、主管(22) 岗位
groupsTaskAssignments.AddRange(
usersData
.Where(it => it.DepId == 7 && (it.JobPostId == 25 || it.JobPostId == 22))
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 76,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
// 77 行程 --> 国交部门(7) 经理(32),主管(22) 岗位
groupsTaskAssignments.AddRange(
usersData
.Where(it => it.DepId == 7 && (it.JobPostId == 22 || it.JobPostId == 32))
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 77,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
//79 车/导游地接 --> 国交部门(7) OP(28)、主管(22) 岗位
groupsTaskAssignments.AddRange(
usersData.Where(it => it.DepId == 7 && (it.JobPostId == 22 || it.JobPostId == 28))
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 79,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
//81 邀请/公务活动 --> 国交部门(7) 商邀(27) 岗位
groupsTaskAssignments.AddRange(
usersData.Where(it => it.DepId == 7 && it.JobPostId == 27)
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 81,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
//2024年8月26日16点27分 “邀请公务活动分配给王鸽(UserID：149)”
groupsTaskAssignments.Add(
new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 81,
UId = 149,
CreateUserId = userId
}
);
//80 签证 --> 国交部门(7) 签证(26) 岗位
groupsTaskAssignments.AddRange(
usersData
.Where(it => it.DepId == 7 && it.JobPostId == 26)
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 80,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
//82 团组客户保险 --> 国交部(7) 经理(32),主管(22) 岗位
//2024-06-25 默认将保险权限分配给签证岗。 签证(26) 岗位
List<int> insurancePositions = new List<int>() { 32, 22, 26 };
groupsTaskAssignments.AddRange(
usersData
.Where(it => it.DepId == 7 && insurancePositions.Contains(it.JobPostId))
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 82,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
//85 机票预订 --> 国交部门(7) 机票(24),主管(22) 岗位
groupsTaskAssignments.AddRange(
usersData
.Where(it => it.DepId == 7 && (it.JobPostId == 22 || it.JobPostId == 24))
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 85,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
//98 其他款项 --> 总经办部门(1) 总经理(1) 岗位/国交部门(7)(ALL) 岗位/财务部门(3)(ALL) 岗位/策划部门(5)(ALL) 岗位/人事部门(4) 采购(74) 岗位
var depIds = new List<int>() { 7, 3, 5 };
var jobIds = new List<int>() { 1, 74 };
groupsTaskAssignments.AddRange(
usersData
.Where(it => depIds.Contains(it.DepId) || jobIds.Contains(it.JobPostId))
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 98,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
//2024年8月26日16点27分 “其他款项分配给王鸽(UserID：149)”
groupsTaskAssignments.Add(new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 98,
UId = 149,
CreateUserId = userId
}
);
//1015 超支费用,285 收款退还 --> 财务部门(ALL)岗位
groupsTaskAssignments.AddRange(
usersData
.Where(it => it.DepId == 3)
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 1015,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
groupsTaskAssignments.AddRange(
usersData
.Where(it => it.DepId == 3)
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 285,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
//1015 超支费用 --> 国交部(ALL)岗位
groupsTaskAssignments.AddRange(
usersData
.Where(it => it.DepId == 7)
.Select(it => new Grp_GroupsTaskAssignment()
{
DIId = diId,
CTId = 1015,
UId = it.Id,
CreateUserId = userId
}).ToList()
);
if (groupsTaskAssignments.Count > 0)
{
var addId = await _teamRateRep._sqlSugar.Insertable(groupsTaskAssignments).ExecuteCommandAsync();
}
result.Code = 0;
result.Msg = string.Format(@"操作成功!");
return result;
}
#endregion
#region 员工是否是市场部人员
/// <summary>
/// 验证市场部客户人员
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static async Task<bool> IsMarketingStaff(int userId)
{
var userInfos = await _dirRep._sqlSugar
.Queryable<Sys_Users>()
.InnerJoin<Sys_Department>((u, d) => u.DepId == d.Id)
.Where((u, d) => u.IsDel == 0 && u.Id == userId && d.DepName.Contains("市场部"))
.ToListAsync();
if (userInfos.Count > 0) return true;
return false;
}
#endregion
#region 三公费用相关
/// <summary>
/// 保存操作前汇率
/// </summary>
/// <param name="currUserId"></param>
/// <returns></returns>
public static async Task<bool> RateRecordSave(int currUserId, int diId, string linkModule)
{
//var selectData = await _enterExitCostRep._sqlSugar.Queryable<Sys_ExchangeRateRecord>()
// .Where(x => Convert.ToDateTime(x.RateDateTime).ToString("yyyy-MM-dd").Equals(DateTime.Now.ToString("yyyy-MM-dd")))
// .FirstAsync();
//if (selectData != null) return false;
var _currencyRate = await _juHeApi.PostItemRateAsync(new string[] { });
string rateInfoStr = string.Empty;
var oaCurrencyData = await _sqlSugar.Queryable<Sys_SetData>()
.Where(x => x.IsDel == 0 && x.STid == 66)
.ToListAsync();
foreach (var rate in _currencyRate)
{
var oaCurrncy = oaCurrencyData.Find(x => x.Remark.Equals(rate.Name));
if (oaCurrncy == null) continue;
//美元(USD):7.5|
rateInfoStr += $"{rate.Name}({oaCurrncy.Name}):{rate.FSellPri}|";
}
if (string.IsNullOrEmpty(rateInfoStr)) return false;
var add = await _sqlSugar
.Insertable<Sys_ExchangeRateRecord>(new Sys_ExchangeRateRecord()
{
DiId = diId,
LinkModule = linkModule,
RateDateTime = DateTime.Now,
RateInfo = rateInfoStr,
CreateUserId = currUserId,
IsDel = 0
})
.ExecuteCommandAsync();
return add > 0 ? true : false;
}
/// <summary>
/// 三公费用导入收款账单数据
/// </summary>
/// <param name="groupId"></param>
/// <returns></returns>
public static async Task<List<Fin_ForeignReceivables>> ReceivablesImportFeeAsync(int groupId)
{
var data = new List<Fin_ForeignReceivables>();
var enterExitCosts = await _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == groupId).FirstAsync();
var dayAndCosts = await _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == groupId).ToListAsync();
var dayOtherCosts = await _sqlSugar.Queryable<Grp_DayOtherPrice>().Where(x => x.IsDel == 0 && x.Diid == groupId).ToListAsync(); //其他款项
if (enterExitCosts == null) return data;
//数据源
var stayData = dayAndCosts.Where(it => it.Type == 1).ToList(); //住宿费
var mealData = dayAndCosts.Where(it => it.Type == 2).ToList(); //伙食费
var miscellaneousData = dayAndCosts.Where(it => it.Type == 3).ToList(); //公杂费
var trainData = dayAndCosts.Where(it => it.Type == 4).ToList(); //培训费
//筛选 陪同人员 = 1
var groupClientList = await _sqlSugar.Queryable<Grp_TourClientList>()
.LeftJoin<Crm_DeleClient>((tcl, dc) => tcl.ClientId == dc.Id)
.LeftJoin<Crm_CustomerCompany>((tcl, dc, cc) => dc.CrmCompanyId == cc.Id)
.LeftJoin<Sys_SetData>((tcl, dc, cc, sd) => tcl.ShippingSpaceTypeId == sd.Id)
.Where(tcl => tcl.IsDel == 0 &&
tcl.DiId == groupId &&
tcl.IsAccompany == 1
)
.Select((tcl, dc, cc, sd) => new
{
DiId = tcl.DiId,
CompanyId = cc.Id,
CompanyName = cc.CompanyFullName,
ClienId = dc.Id,
ClientName = dc.FirstName + dc.LastName,
SpaceId = tcl.ShippingSpaceTypeId,
SpaceName = sd.Name
})
.ToListAsync();
if (groupClientList.Count < 1) return data;
decimal domesticFeeTotal = 0.00M, //境内费用
economyClassFeeTotal = 0.00M, //经济舱费用
businessClassFeeTotal = 0.00M, //公务舱费用
firstClassFeeTotal = 0.00M, //头等舱费用
stayFeeTotal = 0.00M, //住宿费
mealsFeeTotal = 0.00M, //餐食费
miscellaneousFeeTotal = 0.00M, //公杂费
trainFeeTotal = 0.00M, //培训费
otherPriceTotal = 0.00M; //其他款项
//境内费用(其他费用)
if (enterExitCosts.ChoiceOne == 1) domesticFeeTotal = enterExitCosts.InsidePay;
//住宿费
if (enterExitCosts.ChoiceThree == 1) stayFeeTotal = stayData.Sum(x => x.SubTotal);
//伙食费
if (enterExitCosts.ChoiceFour == 1) mealsFeeTotal = mealData.Sum(x => x.SubTotal);
//公杂费
if (enterExitCosts.ChoiceFive == 1) miscellaneousFeeTotal = miscellaneousData.Sum(x => x.SubTotal);
//培训费
if (enterExitCosts.ChoiceSix == 1) trainFeeTotal = trainData.Sum(x => x.SubTotal);
//其他款项
if (enterExitCosts.OtherExpenses_Checked == 1) otherPriceTotal = dayOtherCosts.Sum(x => x.SubTotal);
decimal otherFeeTotal = domesticFeeTotal + stayFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + trainFeeTotal + otherPriceTotal;
//国际旅费合计
//经济舱
if (enterExitCosts.SumJJC == 1) economyClassFeeTotal = enterExitCosts.OutsideJJPay + otherFeeTotal;
//公务舱
if (enterExitCosts.SumGWC == 1) businessClassFeeTotal = enterExitCosts.OutsideGWPay + otherFeeTotal;
//头等舱
if (enterExitCosts.SumTDC == 1) firstClassFeeTotal = enterExitCosts.OutsideTDPay + otherFeeTotal;
var groupClientListGroup = groupClientList.GroupBy(x => x.CompanyId);
foreach (var item in groupClientListGroup)
{
var companyName = item.FirstOrDefault().CompanyName;
var airTicketGroup = item.GroupBy(x => x.SpaceId);
foreach (var airTicket in airTicketGroup)
{
int quantity = airTicket.Count();
if (quantity > 0)
{
var price = 0.00M;
var spaceName = airTicket.FirstOrDefault()?.SpaceName ?? string.Empty;
if (spaceName.Equals("经济舱")) price = economyClassFeeTotal;
else if (spaceName.Equals("公务舱")) price = businessClassFeeTotal;
else if (spaceName.Equals("头等舱")) price = firstClassFeeTotal;
if (price > 0)
{
decimal itemTotal = price * quantity;
data.Add(new Fin_ForeignReceivables()
{
Id = 0,
CreateTime = DateTime.Now,
Diid = groupId,
PriceName = $"{companyName}-{spaceName}",
Price = price,
Count = quantity,
Unit = "人",
ItemSumPrice = itemTotal,
Currency = 836,
Rate = 1.0000M,
AddingWay = 2,
Remark = "由出入境费用导入费用",
});
}
}
}
}
return data;
}
#endregion
#endregion
#region 团组汇率
/// <summary>
/// 团组汇率
/// 获取板块 币种 及 汇率
/// 76 酒店预订 77 行程 79 车/导游地接
/// 80 签证 82 团组客户保险 85 机票预订
/// 98 其他款项 285 收款退还
/// </summary>
/// <param name="teamRateModels"></param>
/// <param name="cTable"></param>
/// <param name="currencyCode"></param>
/// <returns>
/// string
/// eg: CNY 1.0000
/// </returns>
public static async Task<string> PostGroupRateByCTableAndCurrency(List<TeamRateModelView> teamRateModels, int cTable, List<string> currencyCodes)
{
string str = "";
List<string> currencyRates = new List<string>();
TeamRateModelView hotelRateData = teamRateModels.Where(it => it.CTableId == cTable).FirstOrDefault();
if (hotelRateData != null)
{
var hotelRates = hotelRateData.TeamRates;
foreach (var item in currencyCodes)
{
if (!string.IsNullOrEmpty(item))
{
var hotelRateInfo = hotelRates.Where(it => it.CurrencyCode.Equals(item)).FirstOrDefault();
if (hotelRateInfo != null)
{
string str1 = string.Format("{0} {1}\r\n", hotelRateInfo.CurrencyCode, hotelRateInfo.Rate.ToString("#0.0000"));
currencyRates.Add(str1);
}
}
}
if (currencyRates != null || currencyRates.Count > 0)
{
currencyRates = currencyRates.Distinct().ToList();
foreach (var item in currencyRates)
{
str += item;
}
}
}
return str;
}
/// <summary>
/// 团组汇率 币种 Item (来源：团组汇率)
/// 根据 团组Id And 业务类型(CTable)Id
/// api处理CTable = 285,默认返回CNY
/// </summary>
/// <param name="portType"></param>
/// <param name="diId"></param>
/// <param name="cTable"></param>
/// <returns></returns>
public static async Task<TeamRateModelGeneralView> PostGroupTeamRateByDiIdAndCTableId(int portType, int diId, int cTable)
{
TeamRateModelGeneralView _view = new TeamRateModelGeneralView();
_view = await _teamRateRep.PostGroupTeamRateByDiIdAndCTableId(portType, diId, cTable);
return _view;
}
/// <summary>
/// 汇率备注拆分
/// (美元(USD):7.2370|日元(JPY):0.0499|欧元(EUR):8.3000|英镑(GBP):9.1996|港币(HKD):0.9291)
/// </summary>
/// <param name="rateRemark"></param>
/// <returns></returns>
public static async Task<List<TeamRateDescAddCurrencyIdView>> SplitExchangeRate(this string rateRemark)
{
List<TeamRateDescAddCurrencyIdView> _view = new List<TeamRateDescAddCurrencyIdView>();
List<SetDataInfoView> currencyDatas = new List<SetDataInfoView>();
#region 获取所有币种
string sql = string.Format(@"select * from Sys_SetData where STid = {0} and isdel = 0", 66);
var DBdata = _setDataRep.GetListBySqlWithNolock(sql);
if (DBdata == null || DBdata.Count == 0)
{
return _view;
}
currencyDatas = DBdata.Select(x => new SetDataInfoView
{
Name = x.Name,
Id = x.Id,
Remark = x.Remark,
}).ToList();
#endregion
#region 拆分remark里的汇率
if (string.IsNullOrEmpty(rateRemark))
{
return _view;
}
if (rateRemark.Contains("|"))
{
string[] currencyArr = rateRemark.Split("|");
foreach (string currency in currencyArr)
{
string[] currency1 = currency.Split(":");
string[] currency2 = currency1[0].Split("(");
string currencyCode = currency2[1].Replace(")", "").TrimEnd();
TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
{
CurrencyId = currencyDatas.Find(it => it.Name == currencyCode).Id,
CurrencyCode = currencyCode,
CurrencyName = currency2[0],
Rate = decimal.Parse(currency1[1]),
};
_view.Add(rateDescView);
}
}
else
{
try
{
string[] currency1 = rateRemark.Split(":");
string[] currency2 = currency1[0].Split("(");
string currencyCode = currency2[1].Replace(")", "").TrimEnd();
TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
{
CurrencyId = currencyDatas.Find(it => it.Name == currencyCode).Id,
CurrencyCode = currencyCode,
CurrencyName = currency2[0],
Rate = decimal.Parse(currency1[1]),
};
_view.Add(rateDescView);
}
catch (Exception)
{
return _view;
}
}
#endregion
return _view;
}
#endregion
#region 汉字转换拼音
private static Dictionary<int, List<string>> GetTotalPingYinDictionary(string text)
{
var chs = text.ToCharArray();
//记录每个汉字的全拼
Dictionary<int, List<string>> totalPingYinList = new Dictionary<int, List<string>>();
for (int i = 0; i < chs.Length; i++)
{
var pinyinList = new List<string>();
//是否是有效的汉字
if (ChineseChar.IsValidChar(chs[i]))
{
ChineseChar cc = new ChineseChar(chs[i]);
pinyinList = cc.Pinyins.Where(p => !string.IsNullOrWhiteSpace(p)).ToList();
}
else
{
pinyinList.Add(chs[i].ToString());
}
//去除声调，转小写
pinyinList = pinyinList.ConvertAll(p => Regex.Replace(p, @"\d", "").ToLower());
//去重
pinyinList = pinyinList.Where(p => !string.IsNullOrWhiteSpace(p)).Distinct().ToList();
if (pinyinList.Any())
{
totalPingYinList[i] = pinyinList;
}
}
return totalPingYinList;
}
/// <summary>
/// 获取汉语拼音全拼
/// </summary>
/// <param name="text">The string.</param>
/// <returns></returns>
public static List<string> GetTotalPingYin(this string text)
{
var result = new List<string>();
foreach (var pys in GetTotalPingYinDictionary(text))
{
var items = pys.Value;
if (result.Count <= 0)
{
result = items;
}
else
{
//全拼循环匹配
var newTotalPingYinList = new List<string>();
foreach (var totalPingYin in result)
{
newTotalPingYinList.AddRange(items.Select(item => totalPingYin + item));
}
newTotalPingYinList = newTotalPingYinList.Distinct().ToList();
result = newTotalPingYinList;
}
}
return result;
}
/// <summary>
/// 获取中文第一个拼音
/// </summary>
/// <param name="text"></param>
/// <param name="isUp"></param>
/// <returns></returns>
public static string GetTotalPingYinFirst(this string text, bool isUp = true)
{
var returnstr = string.Empty;
var enResult = text.GetTotalPingYin();
if (enResult.Count == 0)
{
returnstr = text;
}
else if (isUp)
{
returnstr = enResult[0].ToUpper();
}
else
{
returnstr = enResult[0].ToLower();
}
return returnstr;
}
/// <summary>
/// 获取汉语拼音首字母
/// </summary>
/// <param name="text"></param>
/// <returns></returns>
public static List<string> GetFirstPingYin(this string text)
{
var result = new List<string>();
foreach (var pys in GetTotalPingYinDictionary(text))
{
var items = pys.Value;
if (result.Count <= 0)
{
result = items.ConvertAll(p => p.Substring(0, 1)).Distinct().ToList();
}
else
{
//首字母循环匹配
var newFirstPingYinList = new List<string>();
foreach (var firstPingYin in result)
{
newFirstPingYinList.AddRange(items.Select(item => firstPingYin + item.Substring(0, 1)));
}
newFirstPingYinList = newFirstPingYinList.Distinct().ToList();
result = newFirstPingYinList;
}
}
return result;
}
#endregion
#region 新客户资料表 操作记录
/// <summary>
/// 新客户资料表
/// 操作记录添加
/// </summary>
/// <param name="portType"></param>
/// <param name="operationEnum"></param>
/// <param name="userId"></param>
/// <param name="dataId"></param>
/// <param name="remark"></param>
/// <returns></returns>
public static async Task<bool> NewClientOperationRecord(int portType, OperationEnum operationEnum, int userId, int dataId, string remark)
{
Crm_TableOperationRecord _TableOperationRecord = new Crm_TableOperationRecord()
{
TableName = "Crm_NewClientData",
PortType = portType,
OperationItem = operationEnum,
DataId = dataId,
CreateUserId = userId,
CreateTime = DateTime.Now,
Remark = remark
};
bool add = await _tableOperationRecordRep._Add(_TableOperationRecord);
if (add) return false;
return false;
}
#endregion
#region 金额转大写
/// <summary>
/// 金额转换为大写数字
/// <para>1、支持的最大数字：999999999999.99（玖仟玖佰玖拾玖亿玖仟玖佰玖拾玖万玖仟玖佰玖拾玖元玖角玖分）</para>
/// <para>2、小数点后最多支持两位</para>
/// </summary>
/// <param name="num">数值</param>
/// <returns></returns>
public static string ConvertCNYUpper(this decimal num)
{
if (num == 0)
{
return "零元";
}
// 解决327000000转换后缺少单位万的问题
return ConvertToChinese(num);
}
#region 实例
/// <summary>
/// 要转换的数字
/// </summary>
private static decimal j;
/// <summary>
///
/// </summary>
private static string[] NumChineseCharacter = new string[] { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" };
/// <summary>
/// 判断输入的数字是否大于double类型
/// </summary>
private static bool IsNumber
{
get
{
if (j > decimal.MaxValue || j <= 0)
return false;
else
return true;
}
}
/// <summary>
/// 数字转换成大写汉字主函数
/// </summary>
/// <returns>返回转换后的大写汉字</returns>
public static string ConvertToChinese(decimal m)
{
j = m;
string bb = "";
if (IsNumber)
{
string str = j.ToString();
string[] Num = str.Split('.');
if (Num.Length == 1)
{
bb = NumberString(Num[0]) + "元整";
bb = bb.Replace("零零", "零");
}
else
{
bb = NumberString(Num[0]) + "元";
bb += FloatString(Num[1]);
bb = bb.Replace("零零", "零");
}
}
else
{
throw new FormatException("你输入的数字格式不正确或不是数字!");
}
return bb;
}
/// <summary>
/// 小数位转换只支持两位的小数
/// </summary>
/// <param name="Num">转换的小数</param>
/// <returns>小数转换成汉字</returns>
private static string FloatString(string Num)
{
string cc = "";
if (Num.Length > 2)
{
throw new FormatException("小数位数过多.");
}
else
{
string bb = ConvertString(Num);
int len = bb.IndexOf("零");
if (len != 0)
{
bb = bb.Replace("零", "");
if (bb.Length == 1)
{
cc = bb.Substring(0, 1) + "角";
}
else
{
cc = bb.Substring(0, 1) + "角";
cc += bb.Substring(1, 1) + "分";
}
}
else
cc = bb + "分";
}
return cc;
}
/// <summary>
/// 判断数字位数以进行拆分转换
/// </summary>
/// <param name="Num">要进行拆分的数字</param>
/// <returns>转换成的汉字</returns>
private static string NumberString(string Num)
{
string bb = "";
if (Num.Length <= 4)
{
bb = Convert4(Num);
}
else if (Num.Length > 4 && Num.Length <= 8)
{
bb = Convert4(Num.Substring(0, Num.Length - 4)) + "万";
bb += Convert4(Num.Substring(Num.Length - 4, 4));
}
else if (Num.Length > 8 && Num.Length <= 12)
{
bb = Convert4(Num.Substring(0, Num.Length - 8)) + "亿";
if (Convert4(Num.Substring(Num.Length - 8, 4)) == "")
if (Convert4(Num.Substring(Num.Length - 4, 4)) != "")
bb += "零";
else
bb += "";
else
bb += Convert4(Num.Substring(Num.Length - 8, 4)) + "万";
bb += Convert4(Num.Substring(Num.Length - 4, 4));
}
else
{
throw new Exception("整数部分最多支持12位");
}
return bb;
}
/// <summary>
/// 四位数字的转换
/// </summary>
/// <param name="Num">准备转换的四位数字</param>
/// <returns>转换以后的汉字</returns>
private static string Convert4(string Num)
{
string bb = "";
if (Num.Length == 1)
{
bb = ConvertString(Num);
}
else if (Num.Length == 2)
{
bb = ConvertString(Num);
bb = Convert2(bb);
}
else if (Num.Length == 3)
{
bb = ConvertString(Num);
bb = Convert3(bb);
}
else
{
bb = ConvertString(Num);
string cc = "";
string len = bb.Substring(0, 4);
if (len != "零零零零")
{
len = bb.Substring(0, 3);
if (len != "零零零")
{
bb = bb.Replace("零零零", "");
if (bb.Length == 1)
{
bb = bb.Substring(0, 1) + "仟";
}
else
{
if (bb.Substring(0, 1) != "零" && bb.Substring(0, 2) != "零")
cc = bb.Substring(0, 1) + "仟";
else
cc = bb.Substring(0, 1);
bb = cc + Convert3(bb.Substring(1, 3));
}
}
else
{
bb = bb.Replace("零零零", "零");
}
}
else
{
bb = bb.Replace("零零零零", "");
}
}
return bb;
}
/// <summary>
/// 将数字转换成汉字
/// </summary>
/// <param name="Num">需要转换的数字</param>
/// <returns>转换后的汉字</returns>
private static string ConvertString(string Num)
{
string bb = "";
for (int i = 0; i < Num.Length; i++)
{
bb += NumChineseCharacter[int.Parse(Num.Substring(i, 1))];
}
return bb;
}
/// <summary>
/// 两位数字的转换
/// </summary>
/// <param name="Num">两位数字</param>
/// <returns>转换后的汉字</returns>
private static string Convert2(string Num)
{
string bb = ""; string cc = "";
string len = Num.Substring(0, 1);
if (len != "零")
{
bb = Num.Replace("零", "");
if (bb.Length == 1)
{
cc = bb.Substring(0, 1) + "拾";
}
else
{
cc = bb.Substring(0, 1) + "拾";
cc += bb.Substring(1, 1);
}
}
else
cc = Num;
return cc;
}
/// <summary>
/// 三位数字的转换
/// </summary>
/// <param name="Num">三位数字</param>
/// <returns>转换后的汉字</returns>
private static string Convert3(string Num)
{
string bb = ""; string cc = "";
string len = Num.Substring(0, 2);
if (len != "零零")
{
bb = Num.Replace("零零", "");
if (bb.Length == 1)
{
bb = bb.Substring(0, 1) + "佰";
}
else
{
if (bb.Substring(0, 1) != "零")
cc = bb.Substring(0, 1) + "佰";
else
cc = bb.Substring(0, 1);
bb = cc + Convert2(bb.Substring(1, 2));
}
}
else
{
bb = Num.Replace("零零", "零");
}
return bb;
}
#endregion
#endregion
#region 数字验证
/// <summary>
/// 验证数字字符串
/// </summary>
/// <param name="num"></param>
/// <returns></returns>
public static bool IsNumeric(this string numStr)
{
bool isNumeric = Regex.IsMatch(numStr, @"^\d+$");
if (isNumeric)
{
return true;
}
return false;
}
#endregion
#region 日期 格式转换
/// <summary>
/// 验证数字字符串
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static string ConvertToDatetime(this string str)
{
if (!string.IsNullOrEmpty(str))
{
DateTime currentDate = Convert.ToDateTime(str);
return $"{currentDate.Year}年{currentDate.Month}月{currentDate.Day}日";
}
return "";
}
#endregion
#region 月份/季度
/// <summary>
/// 获取月份对应的起始天数
/// </summary>
/// <param name="year"></param>
/// <returns></returns>
public static List<MonthInfo> GetMonthInfos(int year)
{
List<MonthInfo> months = new List<MonthInfo>();
if (year > 1)
{
for (int i = 1; i < 13; i++)
{
months.Add(new MonthInfo()
{
Month = i,
Days = new DaysInfo() { BeginDays = 1, EndDays = DateTime.DaysInMonth(year, i) }
});
}
}
return months;
}
/// <summary>
/// 获取季度对应的起始天数
/// </summary>
/// <param name="year"></param>
/// <returns></returns>
public static List<MonthInfo> GetQuarter(int year)
{
List<MonthInfo> months = new List<MonthInfo>();
months.Add(new MonthInfo() { Month = 1, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //1.1 - 3.31
months.Add(new MonthInfo() { Month = 2, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //4.1 - 6.30
months.Add(new MonthInfo() { Month = 3, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //7.1 - 9.31
months.Add(new MonthInfo() { Month = 4, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //10.1 - 12.31
return months;
}
public class MonthInfo
{
public int Month { get; set; }
public DaysInfo Days { get; set; }
}
public class DaysInfo
{
public int BeginDays { get; set; }
public int EndDays { get; set; }
}
#endregion
#region op行程单，黑屏幕代码
public static string GetCountryStandingTime(int diId)
{
DataTable datas = GetTableByBlackCode(diId);
string countryStr = "[黑屏代码未录入].";
if (datas.Rows.Count > 0)
{
var airDatas = from row in datas.AsEnumerable()
select new
{
Three = row.Field<string>("Three"),
Day = row.Field<string>("Day"),
ArrivedDate = row.Field<string>("ArrivedDate"),
};
//三字码信息
List<Res_ThreeCode> listcode = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(x => x.IsDel == 0).ToList();
//string countryStr = "";
int index = 0;
List<string> cityCodes = new List<string>();
//去掉开始和结束城市
foreach (var row in airDatas)
{
if (!string.IsNullOrEmpty(row.Three))
{
if (index == 0) cityCodes.Add(row.Three.Substring(3, 3)); //到达国家
else if (airDatas.Count() - 1 == index) cityCodes.Add(row.Three.Substring(0, 3)); //到达国家
else cityCodes.Add(row.Three.Substring(0, 3)); //到达国家
}
index++;
}
cityCodes = cityCodes.Distinct().ToList();
if (cityCodes.Count > 0) countryStr = "";
foreach (var item in cityCodes)
{
var airData = airDatas.Where(it => it.Three.Contains(item)).ToList();
string country = listcode.Find(it => it.Three.Equals(item))?.Country ?? "Unknown";
if (country.Equals("中国"))
{
country = listcode.Find(it => it.Three.Equals(item))?.City ?? "Unknown";
}
int days = 0;
if (airData.Count == 2)
{
DateTime arr_dt = Convert.ToDateTime(airData[0].ArrivedDate); //抵达时间
DateTime dep_dt = Convert.ToDateTime(airData[1].Day); //离开时间
//days = (dep_dt - arr_dt).Days;
//countryStr += $@"{country}停留{days}日、";
countryStr += $@"{country}停留 日、";
}
}
if (countryStr.Length > 0) countryStr = countryStr.Substring(0, countryStr.Length - 1);
}
return countryStr;
}
/// <summary>
///根据机票黑屏代码整理DataTable
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public static DataTable GetTableByBlackCode1(int diid)
{
//黑屏代码信息
List<Air_TicketBlackCode> listcode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.DiId == diid && x.IsDel == 0).ToList();
//测试数据为序号,航班号,起飞日期,三字码,起飞时刻,到达时刻,出发航站楼,到达航站楼,机型,飞行时间
//1.3U8391 TU17NOV CTUCAI 0220 0715 T1 T2 330 10H55M
DataTable dt = new DataTable();
dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
dt.Columns.Add("Date", typeof(string));//起飞日期
dt.Columns.Add("Three", typeof(string));//三字码
dt.Columns.Add("StartTime", typeof(string));//起飞时刻
dt.Columns.Add("EndTime", typeof(string));//到达时刻
dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
dt.Columns.Add("AirModel", typeof(string)); //机型
dt.Columns.Add("FlightTime", typeof(string));//飞行时间
dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据
dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
dt.Columns.Add("Error", typeof(string));//整理的到达日期
dt.Columns.Add("Sign", typeof(string));//标识：0表示为原生黑屏代码、1表示“+1”新增的黑屏代码
//判断是否录入黑屏代码
if (listcode.Count() == 0 || listcode == null)
{
dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "黑屏代码未录入!", null);
}
else
{
//读取单段黑屏代码
for (int i = 0; i < listcode.Count; i++)
{
//去除序号
string[] CodeList = Regex.Split(listcode[i].BlackCode, "\\d+\\.", RegexOptions.IgnoreCase);
//去除多余空格，方法一Linq扩展方法
CodeList = CodeList.Where(str => str != "").ToArray();
CodeList = CodeList.Where(str => str != " ").ToArray();
//年
int year = Convert.ToInt32(DateTime.Now.Year.ToString());
//读取单条黑屏代码
for (int j = 0; j < CodeList.Count(); j++)
{
//去除多余空格，方法二使用Split()方法进行分割,分割有一个选项是RemoveEmptyEntries
CodeList[j] = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty).TrimStart().TrimEnd();
string[] Info = CodeList[j].Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
//string[] Info = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty)
// .TrimStart().TrimEnd().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
//去除多余空格
Info = Info.Where(str => str != "").ToArray();
Info = Info.Where(str => str != " ").ToArray();
//判断黑屏代码是否正确拆分; 理应拆成9段
if (Info.Count() < 9)
{
dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "本团组第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误,请联系机票同事核对", null);
}
else
{
try
{
//月
int month = Convert.ToInt32(GetLonger(Info[1].Substring(4, 3)));
//日
int day = Convert.ToInt32(Info[1].Substring(2, 2));
#region 逐一比较月份,判断是否翻年;逐一比较三字码顶真,判断是否跑错机场
if (j > 0)
{
string[] Temp = CodeList[j - 1].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty)
.TrimStart().TrimEnd().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
Temp = Temp.Where(str => str != "").ToArray();
Temp = Temp.Where(str => str != " ").ToArray();
int monthTemp = Convert.ToInt32(GetLonger(Temp[1].Substring(4, 3)));
// 如果相邻月份之差小于0,则证明次一条年份需+1
if (month - monthTemp < 0)
{
year = year + 1;
}
//如果相邻代码三字码不顶真，提醒
string FootThree = Temp[2].Substring(3, 3);
string HeadThree = Info[2].Substring(0, 3);
if (FootThree != HeadThree)
{
//DelegationInfoService s = new DelegationInfoService();
//UsersService us = new UsersService();
//GroupsTaskAssignmentService gts = new GroupsTaskAssignmentService();
//77 行程
List<Grp_GroupsTaskAssignment> list2 = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(x => x.CTId == 77 && x.DIId == diid && x.IsDel == 0).ToList();
foreach (var temp in list2)
{
//if (temp.UId != 21)
//SendAndReturn(us.GetUsersByID(temp.UId).Email,
// "黑屏代码提醒",
// s.GetDelegationInfoByID(diid).TeamName + "的机票黑屏代码中，相邻航段的三字码不连续，请查看！");
}
//85 机票预订
List<Grp_GroupsTaskAssignment> list6 = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(x => x.CTId == 85 && x.DIId == diid && x.IsDel == 0).ToList();
foreach (var temp in list6)
{
//if (temp.UId != 21)
//SendAndReturn(us.GetUsersByID(temp.UId).Email,
// "黑屏代码提醒",
// s.GetDelegationInfoByID(diid).TeamName + "的机票黑屏代码中，相邻航段的三字码不连续，请查看！");
}
}
}
#endregion
#region 判断到达日期是否需要加1
if (Info[4].Contains("+"))
{
//日期+1
day = day + 1;
//判断是否进入下一月
if (day > Convert.ToInt32(GetDaysByMonth(Info[1].Substring(4, 3), year)))
{
day = day - Convert.ToInt32(GetDaysByMonth(Info[1].Substring(4, 3), year));
month = month + 1;
//判断是否进入下一年
if (month > 12)
{
month = month - 12;
year = year + 1;
}
}
//月份整理格式
string monthTemp = month.ToString();
if (month < 10)
{
monthTemp = "0" + monthTemp;
}
//日期整理格式
string daytemp = day.ToString();
if (day < 10)
{
daytemp = "0" + daytemp;
}
string temp = Info[4].Split('+')[0];
//添加起飞数据
dt.Rows.Add(Info[0],
Info[1],
Info[2],
Info[3],
temp,
Info[5],
Info[6],
Info[7],
Info[8],
year + "-" + GetLonger(Info[1].Substring(4, 3)) + "-" + Info[1].Substring(2, 2),
year + "-" + monthTemp + "-" + daytemp,
"",
"0");
//加1天，添加到达数据
dt.Rows.Add(Info[0],
Info[1].Replace(Info[1].Substring(2, 2), daytemp),
Info[2],
Info[3],
temp,
Info[5],
Info[6],
Info[7],
Info[8],
year + "-" + monthTemp + "-" + daytemp,
year + "-" + monthTemp + "-" + daytemp,
"",
"1");
}
else
{
//月份整理格式
string monthTemp = month.ToString();
if (month < 10)
{
monthTemp = "0" + monthTemp;
}
//日期整理格式
string daytemp = day.ToString();
if (day < 10)
{
daytemp = "0" + daytemp;
}
dt.Rows.Add(Info[0],
Info[1],
Info[2],
Info[3],
Info[4],
Info[5],
Info[6],
Info[7],
Info[8],
year + "-" + monthTemp + "-" + daytemp,
year + "-" + monthTemp + "-" + daytemp,
"",
"0");
}
#endregion
}
catch (Exception ex)
{
string exstr = ex.Message.ToString();
}
}
}
//排序
dt.DefaultView.Sort = "Day asc";
dt = dt.DefaultView.ToTable();
}
}
return dt;
}
private readonly static string[] excludeArr = new string[] { "[中转]", "[转机]" };
/// <summary>
///根据机票黑屏代码整理DataTable
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public static DataTable GetTableByBlackCode(int diid)
{
//黑屏代码信息
List<Air_TicketBlackCode> listcode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.DiId == diid && x.IsDel == 0).ToList();
//测试数据为序号,航班号,起飞日期,三字码,起飞时刻,到达时刻,出发航站楼,到达航站楼,机型,飞行时间
//1.3U8391 TU17NOV CTUCAI 0220 0715 T1 T2 330 10H55M
DataTable dt = new DataTable();
dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
dt.Columns.Add("Date", typeof(string));//起飞日期
dt.Columns.Add("Three", typeof(string));//三字码
dt.Columns.Add("StartTime", typeof(string));//起飞时刻
dt.Columns.Add("EndTime", typeof(string));//到达时刻
dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
dt.Columns.Add("AirModel", typeof(string)); //机型
dt.Columns.Add("FlightTime", typeof(string));//飞行时间
dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据
dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
dt.Columns.Add("Error", typeof(string));//整理的到达日期
dt.Columns.Add("Sign", typeof(string));//标识：0表示为原生黑屏代码、1表示“+1”新增的黑屏代码
//添加转机标识
dt.Columns.Add("isTransitShipment", typeof(bool));
//判断是否录入黑屏代码
if (listcode.Count() == 0 || listcode == null)
{
dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "黑屏代码未录入!", null, false);
}
else
{
//读取单段黑屏代码
for (int i = 0; i < listcode.Count; i++)
{
//去除序号
string[] CodeList = Regex.Split(listcode[i].BlackCode, "\\d+\\.", RegexOptions.IgnoreCase);
//去除多余空格，方法一Linq扩展方法
CodeList = CodeList.Where(str => !string.IsNullOrWhiteSpace(str)).ToArray();
//读取单条黑屏代码
for (int j = 0; j < CodeList.Count(); j++)
{
//去除多余空格，方法二使用Split()方法进行分割,分割有一个选项是RemoveEmptyEntries
CodeList[j] = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty).TrimStart().TrimEnd();
string[] Info = CodeList[j].Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
//去除多余空格
Info = Info.Where(str => !string.IsNullOrWhiteSpace(str)).ToArray();
//判断黑屏代码是否正确拆分; 理应拆成9段
if (Info.TakeWhile((x) =>
{
return !excludeArr.Contains(x);
}).Count() != 9)
{
dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "本团组第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误,请联系机票同事核对", null, false);
//MessageBoxEx.Show("第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误, 请联系机票同事核对");
return dt;
}
else
{
try
{
var monthEn = Info[1].Substring(4, 3);
//月
int month = Convert.ToInt32(GetLonger(monthEn));
//日
int day = Convert.ToInt32(Info[1].Substring(2, 2));
var time = new DateTime(DateTime.Now.Year, month, day); //
var isExist = Info.Contains("[中转]");
//dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
//dt.Columns.Add("Date", typeof(string));//起飞日期
//dt.Columns.Add("Three", typeof(string));//三字码
//dt.Columns.Add("StartTime", typeof(string));//起飞时刻
//dt.Columns.Add("EndTime", typeof(string));//到达时刻
//dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
//dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
//dt.Columns.Add("AirModel", typeof(string)); //机型
//dt.Columns.Add("FlightTime", typeof(string));//飞行时间
//dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据
//dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
//dt.Columns.Add("Error", typeof(string));//整理的到达日期
//dt.Columns.Add("Sign", typeof(string));//标识：0表示为原生黑屏代码、1表示“+1”新增的黑屏代码
dt.Rows.Add(Info[0],
Info[1],
Info[2],
Info[3],
Info[4],
Info[5],
Info[6],
Info[7],
Info[8],
time.ToString("yyyy-MM-dd"),
time.ToString("yyyy-MM-dd"),
"",
"0",
isExist
);
}
catch (Exception ex)
{
string exstr = ex.Message.ToString();
}
}
}
//排序
dt.DefaultView.Sort = "Day asc";
dt = dt.DefaultView.ToTable();
}
}
return dt;
}
/// <summary>
/// 根据星期,月份的缩写,转换成数字或者全称
/// 根据币种中文名称返回币种代码
/// 根据数字返回机型型号【2、3开头的就是空客，比如空客320，7开头的就是波音，比如波音777】
/// 20210903贾文滔
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public static string GetLonger(string temp)
{
string str = "";
switch (temp.ToUpper())
{
case "美元":
str = "USD";
break;
case "日元":
str = "JPY";
break;
case "英镑":
str = "GBP";
break;
case "欧元":
str = "EUR";
break;
case "港币":
str = "HKD";
break;
case "MO":
str = "星期一";
break;
case "TU":
str = "星期二";
break;
case "WE":
str = "星期三";
break;
case "TH":
str = "星期四";
break;
case "FR":
str = "星期五";
break;
case "SA":
str = "星期六";
break;
case "SU":
str = "星期天";
break;
case "JAN":
str = "01";
break;
case "FEB":
str = "02";
break;
case "MAR":
str = "03";
break;
case "APR":
str = "04";
break;
case "MAY":
str = "05";
break;
case "JUN":
str = "06";
break;
case "JUL":
str = "07";
break;
case "AUG":
str = "08";
break;
case "SEP":
str = "09";
break;
case "OCT":
str = "10";
break;
case "NOV":
str = "11";
break;
case "DEC":
str = "12";
break;
case "MONDAY":
str = "星期一";
break;
case "TUESDAY":
str = "星期二";
break;
case "WEDNESDAY":
str = "星期三";
break;
case "THURSDAY":
str = "星期四";
break;
case "FRIDAY":
str = "星期五";
break;
case "SATURDAY":
str = "星期六";
break;
case "SUNDAY":
str = "星期日";
break;
case "01":
str = "JAN";
break;
case "02":
str = "FEB";
break;
case "03":
str = "MAR";
break;
case "04":
str = "APR";
break;
case "05":
str = "MAY";
break;
case "06":
str = "JUN";
break;
case "07":
str = "JUL";
break;
case "08":
str = "AUG";
break;
case "09":
str = "SEP";
break;
case "10":
str = "OCT";
break;
case "11":
str = "NOV";
break;
case "12":
str = "DEC";
break;
case "2":
str = "空客A";
break;
case "3":
str = "空客A";
break;
case "7":
str = "波音";
break;
}
return str;
}
/// <summary>
/// 根据月份返回天数
/// </summary>
/// <param name="temp"></param>
/// <returns></returns>
public static string GetDaysByMonth(string Month, int year)
{
string str = "";
//判断是否是闰年
if (DateTime.IsLeapYear(year) == false)
{
switch (Month.ToUpper())
{
case "JAN":
str = "31";
break;
case "FEB":
str = "28";
break;
case "MAR":
str = "31";
break;
case "APR":
str = "30";
break;
case "MAY":
str = "31";
break;
case "JUN":
str = "30";
break;
case "JUL":
str = "31";
break;
case "AUG":
str = "31";
break;
case "SEP":
str = "30";
break;
case "OCT":
str = "31";
break;
case "NOV":
str = "30";
break;
case "DEC":
str = "31";
break;
case "01":
str = "31";
break;
case "02":
str = "28";
break;
case "03":
str = "31";
break;
case "04":
str = "30";
break;
case "05":
str = "31";
break;
case "06":
str = "30";
break;
case "07":
str = "31";
break;
case "08":
str = "31";
break;
case "09":
str = "30";
break;
case "10":
str = "31";
break;
case "11":
str = "30";
break;
case "12":
str = "31";
break;
}
}
else
{
switch (Month.ToUpper())
{
case "JAN":
str = "31";
break;
case "FEB":
str = "29";
break;
case "MAR":
str = "31";
break;
case "APR":
str = "30";
break;
case "MAY":
str = "31";
break;
case "JUN":
str = "30";
break;
case "JUL":
str = "31";
break;
case "AUG":
str = "31";
break;
case "SEP":
str = "30";
break;
case "OCT":
str = "31";
break;
case "NOV":
str = "30";
break;
case "DEC":
str = "31";
break;
case "01":
str = "31";
break;
case "02":
str = "29";
break;
case "03":
str = "31";
break;
case "04":
str = "30";
break;
case "05":
str = "31";
break;
case "06":
str = "30";
break;
case "07":
str = "31";
break;
case "08":
str = "31";
break;
case "09":
str = "30";
break;
case "10":
str = "31";
break;
case "11":
str = "30";
break;
case "12":
str = "31";
break;
}
}
return str;
}
/// <summary>
/// op行程单 团组 城市路径
/// </summary>
/// <param name="diid">团组Id</param>
/// <param name="separator">分隔符</param>
/// <returns></returns>
public static string GetGroupCityLine(int diid, string separator)
{
string city = string.Empty;
var blackCode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.IsDel == 0 && x.DiId == diid).ToList();
if (blackCode.Count > 0)
{
var black = blackCode.First();
black.BlackCode = black.BlackCode == null ? "" : black.BlackCode;
var blackSp = Regex.Split(black.BlackCode, "\\d+\\.", RegexOptions.IgnoreCase).Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
if (blackSp.Length > 0)
{
try
{
var cityArrCode = new List<string>(20);
foreach (var item in blackSp)
{
var itemSp = item.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
var IndexSelect = itemSp[2];
var cityArrCodeLength = cityArrCode.Count - 1;
var startCity = IndexSelect.Substring(0, 3);
if (cityArrCodeLength > 0)
{
var arrEndCity = cityArrCode[cityArrCodeLength];
if (arrEndCity != startCity)
{
cityArrCode.Add(startCity.ToUpper());
}
}
else
{
cityArrCode.Add(startCity.ToUpper());
}
var endCity = IndexSelect.Substring(3, 3);
cityArrCode.Add(endCity.ToUpper());
}
var cityThree = string.Empty;
cityArrCode.ForEach(x => cityThree += "'" + x + "',");
cityThree = cityThree.TrimEnd(',');
if (string.IsNullOrWhiteSpace(cityThree))
{
throw new Exception("error");
}
string sql = $"SELECT * FROM Res_ThreeCode rtc WHERE UPPER(rtc.Three) in ({cityThree}) ";
var cityArr = _dirRep._sqlSugar.SqlQueryable<Res_ThreeCode>(sql).ToList();
foreach (var item in cityArrCode)
{
var find = cityArr.Find(x => x.Three.ToUpper() == item.ToUpper());
if (find != null)
{
city += find.City + separator;
}
else
{
city += item + "[三字码未收录]" + separator;
}
}
city = city.TrimEnd(char.Parse(separator));
}
catch (Exception e)
{
city = "[黑屏代码格式不正确!]";
}
}
}
else city = "[未录入黑屏代码]";
return city;
}
/// <summary>
/// op行程单 团组 城市路径
/// </summary>
/// <param name="diid">团组Id</param>
/// <param name="separator">分隔符</param>
/// <returns></returns>
public static Dictionary<int, string> GetGroupCityLineItem(List<int> diids, string separator)
{
Dictionary<int, string> dicCitys = new Dictionary<int, string>();
var cityCodes = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList();
var blackCodes = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.IsDel == 0 && diids.Contains(x.DiId)).ToList();
foreach (var diid in diids)
{
string city = string.Empty;
var blackCode = blackCodes.Where(it => it.DiId == diid).ToList();
if (blackCode.Count > 0)
{
var black = blackCode.First();
black.BlackCode = black.BlackCode == null ? "" : black.BlackCode;
var blackSp = Regex.Split(black.BlackCode, "\\d+\\.", RegexOptions.IgnoreCase).Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
if (blackSp.Length > 0)
{
try
{
var cityArrCode = new List<string>(20);
foreach (var item in blackSp)
{
var itemSp = item.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
var IndexSelect = itemSp[2];
var cityArrCodeLength = cityArrCode.Count - 1;
var startCity = IndexSelect.Substring(0, 3);
if (cityArrCodeLength > 0)
{
var arrEndCity = cityArrCode[cityArrCodeLength];
if (arrEndCity != startCity)
{
cityArrCode.Add(startCity.ToUpper());
}
}
else
{
cityArrCode.Add(startCity.ToUpper());
}
var endCity = IndexSelect.Substring(3, 3);
cityArrCode.Add(endCity.ToUpper());
}
var cityThree = string.Empty;
cityArrCode.ForEach(x => cityThree += "'" + x + "',");
cityThree = cityThree.TrimEnd(',');
if (string.IsNullOrWhiteSpace(cityThree))
{
throw new Exception("error");
}
var cityArr = cityCodes.Where(it => cityThree.Contains(it.Three.ToUpper())).ToList();
foreach (var item in cityArrCode)
{
var find = cityArr.Find(x => x.Three.ToUpper() == item.ToUpper());
if (find != null)
{
city += find.City + separator;
}
else
{
city += item + "[三字码未收录]" + separator;
}
}
city = city.TrimEnd(char.Parse(separator));
}
catch (Exception e)
{
city = "[黑屏代码格式不正确!]";
}
}
}
else city = "[未录入黑屏代码]";
dicCitys.Add(diid, city);
}
return dicCitys;
}
/// <summary>
/// op行程单相关团组信息 含途径城市
/// </summary>
/// <returns></returns>
public static async Task<List<MateOpGroupPageListView>> MateOpGroupPageListRedis()
{
List<MateOpGroupPageListView> _views = new List<MateOpGroupPageListView>();
string viewStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("MateOpGroupPageList");//string 取
if (!string.IsNullOrEmpty(viewStr))
{
_views = JsonConvert.DeserializeObject<List<MateOpGroupPageListView>>(viewStr);
}
else
{
string sql = string.Format($@"Select Row_Number,Id,SalesQuoteNo,TourCode,TeamTypeId, TeamType,
TeamLevId,TeamLev,TeamName,ClientName,ClientUnit,
VisitDate,VisitDays,VisitPNumber,JietuanOperatorId,
JietuanOperator,IsSure,CreateTime,VisitCountry
From (
Select row_number() over(order by gdi.CreateTime Desc) as Row_Number,
gdi.Id,SalesQuoteNo,TourCode,ssd.Id TeamTypeId, ssd.Name TeamType,
ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,ClientName,ClientUnit,
VisitDate,VisitDays,VisitPNumber,JietuanOperator JietuanOperatorId,
su.CnName JietuanOperator,IsSure,gdi.CreateTime,gdi.VisitCountry
From Grp_DelegationInfo gdi
Left Join Sys_SetData ssd On gdi.TeamDid = ssd.Id
Left Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
Left Join Sys_Users su On gdi.JietuanOperator = su.Id
Where gdi.IsDel = 0
) temp");
_views = _dirRep._sqlSugar.SqlQueryable<MateOpGroupPageListView>(sql).ToList();
#region 处理所属部门
/*
* 1.sq 和 gyy 等显示 市场部
* 2.王鸽和主管及张总还有管理员号统一国交部
* 2-1. 4 管理员 ,21 张海麟
*/
List<int> userIds = _views.Select(it => it.JietuanOperatorId).ToList();
List<int> userIds1 = new List<int>() { 4, 21 };
var UserDepDatas = _dirRep._sqlSugar.Queryable<Sys_Users>()
.LeftJoin<Sys_Department>((u, d) => u.DepId == d.Id)
.Where(u => u.IsDel == 0 && userIds.Contains(u.Id))
.Select((u, d) => new { UserId = u.Id, DepName = userIds1.Contains(u.Id) ? "国交部" : d.DepName })
.ToList();
#endregion
_views.ForEach(it =>
{
it.RouteCity = GetGroupCityLine(it.Id, @"/");
it.Department = UserDepDatas.Find(it1 => it.JietuanOperatorId == it1.UserId)?.DepName ?? "Unknown";
});
TimeSpan ts = DateTime.Now.AddHours(2).TimeOfDay;
await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync<string>("MateOpGroupPageList", JsonConvert.SerializeObject(_views), ts);//string 存
}
return _views;
}
/// <summary>
/// 获取黑屏代码日期列表
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static List<string> GetTimeListByDataTable(DataTable dt)
{
DateTime datestart = Convert.ToDateTime(dt.Rows[0]["Day"].ToString());
DateTime dateend = Convert.ToDateTime(dt.Rows[dt.Rows.Count - 1]["ArrivedDate"].ToString());
List<string> timeList = new List<string>();
while (datestart <= dateend)
{
timeList.Add(datestart.ToString("yyyy-MM-dd"));
datestart = datestart.AddDays(1);
}
return timeList;
}
const decimal conversion = 1.61M;
/// <summary>
/// 简要行程
/// </summary>
/// <param name="diid"></param>
/// <returns></returns>
public static Result GetBriefStroke(int diid)
{
DataTable resultTable = GetTableByBlackCode(diid);
if (resultTable == null)
{
return new Result { Code = -1, Msg = "黑屏代码有误或黑屏代码未录入" };
}
if (resultTable.Rows.Count == 0 || string.IsNullOrWhiteSpace(resultTable.Rows[0][1].ToString()))
{
string msg = string.Empty;
if (resultTable.Rows.Count > 0)
{
msg = resultTable.Rows[0]["Error"].ToString();
}
return new Result { Code = -1, Msg = $"黑屏代码有误；{msg}" };
}
var timeArr = GetTimeListByDataTable(resultTable);
var threeCodes = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList(); //三字码
var airCompanys = _dirRep._sqlSugar.Queryable<Res_AirCompany>().Where(it => it.IsDel == 0).ToList(); //航司公司
var obDatas = _dirRep._sqlSugar.Queryable<Res_OfficialActivities>().Where(it => it.IsDel == 0 && it.DiId == diid).ToList(); //公务信息
var NewListTravel = new List<Grp_TravelList>();
var index = 0;
var stopCity = string.Empty;
foreach (var item in timeArr)
{
string trip = string.Empty;
string weekDay = string.Empty;
DateTime time = DateTime.Now;
if (DateTime.TryParse(item, out time))
{
weekDay = weekdays[(int)time.DayOfWeek];
}
else
{
weekDay = "日期格式不正确!";
}
var empty = "【未收入该三字码！请机票同事录入】";
var tabSelect = resultTable.Select(string.Format("Day = '{0}'", item));
var isMoreTraffic = false;
if (tabSelect.Length > 0)
{
isMoreTraffic = true;
var takeOffTime = DateTime.Parse(item);
var fallToTime = DateTime.Parse(item);
Res_ThreeCode start_Object = null;
Res_ThreeCode end_Object = null;
bool isTrade = false;
//air 处理
foreach (var tabRow in tabSelect)
{
takeOffTime = DateTime.Parse(item);
fallToTime = DateTime.Parse(item);
var takeOff = tabRow["StartTime"].ToString();
var fallTo = tabRow["EndTime"].ToString();
takeOffTime = takeOffTime.AddHours(int.Parse(takeOff.Substring(0, 2)));
takeOffTime = takeOffTime.AddMinutes(int.Parse(takeOff.Substring(2, 2)));
fallToTime = fallToTime.AddHours(int.Parse(fallTo.Substring(0, 2)));
fallToTime = fallToTime.AddMinutes(int.Parse(fallTo.Substring(2, 2)));
var threeCode = tabRow["Three"].ToString();
var start = threeCode.Substring(0, 3);
var end = threeCode.Substring(3, 3);
stopCity = end;
start_Object = threeCodes.Find(x => x.Three.ToUpper() == start.ToUpper());
end_Object = threeCodes.Find(x => x.Three.ToUpper() == end.ToUpper());
if (start_Object == null)
{
start_Object = new Res_ThreeCode()
{
AirPort = empty,
AirPort_En = empty,
City = empty,
Country = empty,
Four = empty,
Three = empty,
};
}
if (end_Object == null)
{
end_Object = new Res_ThreeCode()
{
AirPort = empty,
AirPort_En = empty,
City = empty,
Country = empty,
Four = empty,
Three = empty,
};
}
//航班号
string flightcode = tabRow["Fliagtcode"].ToString();
trip += $"{takeOffTime.ToString("HH:mm")}—{fallToTime.ToString("HH:mm")} {start_Object.City}—{end_Object.City} 航班号：{flightcode}\r\n";
}
//var airArrive = fallToTime; //航班落地时间
time = fallToTime.AddHours(1.5); //出机场一个半小时
string Time = string.Empty;
string Distance = string.Empty;
int GetGoogleResult = 0;
int GetDistResult = 0;
JObject Result = null;
try
{
Time = Result["routes"][0]["legs"][0]["duration"]["text"].ToString().Replace(" ", "").Trim();
Distance = Result["routes"][0]["legs"][0]["distance"]["text"].ToString().Replace(" ", "").Trim();
if (Time.Contains("hours"))
{
Time = Time.Replace("hours", "小时");
}
if (Time.Contains("hour"))
{
Time = Time.Replace("hour", "小时");
}
Time = Time.Replace("mins", "分钟");
if (Distance.Contains("mi"))
{
var distSp = Regex.Split(Distance, "mi");
if (distSp.Length > 0)
{
Distance = (decimal.Parse(distSp[0]) * conversion).ToString("#0.00") + " 公里";
}
}
if (Distance.Contains("km"))
{
Distance = Distance.Replace("km", "公里");
}
if (Time.Contains("小时"))
{
var xs = Regex.Split(Time, "小时");
var xsValue = int.Parse(xs[0]);
var fz = Regex.Split(xs[1], "分钟");
var fzValue = int.Parse(fz[0]);
GetGoogleResult = xsValue * 60;
GetGoogleResult += fzValue;
}
else if (Time.Contains("分钟"))
{
GetGoogleResult = int.Parse(Regex.Split(Time, "分钟")[0]);
}
if (Distance.Contains("公里"))
{
GetDistResult = (int)decimal.Parse(Regex.Split(Distance, "公里")[0]);
}
if (GetGoogleResult > 0 && GetGoogleResult % 5 != 0)
{
while (GetGoogleResult % 5 != 0)
{
GetGoogleResult++;
}
}
if (GetDistResult % 5 != 0 && GetDistResult > 0)
{
while (GetDistResult % 5 != 0)
{
GetDistResult++;
}
}
int H = (GetGoogleResult / 60);
int m = (GetGoogleResult % 60);
string TimeStr = string.Empty;
if (H != 0)
{
if (H < 10)
{
TimeStr += "0" + H + "小时";
}
else
{
TimeStr += H + "小时";
}
}
if (m < 10)
{
TimeStr += "0" + m + "分钟";
}
else
{
TimeStr += m + "分钟";
}
Time = TimeStr;
Distance = GetDistResult.ToString() + "公里";
}
catch (Exception ex)
{
Time = "未知！";
Distance = "未知！";
}
//trip += $"{time.ToString("HH:mm")} 搭乘专车前往市区（{end_Object.AirPort} - {end_Object.City}市区 路程{Distance}，耗时{Time}）";
//trip += $"{time.ToString("HH:mm")} 搭乘专车前往市区（{end_Object.AirPort} - {end_Object.City}市区）";
time = time.AddMinutes(GetGoogleResult); //到达市区时间
string tripDate = Convert.ToDateTime(item).ToString("M月d日");
if (index == timeArr.Count - 1)
{
NewListTravel.Add(new Grp_TravelList
{
CreateTime = DateTime.Now,
CreateUserId = 0,
Diid = diid,
Date = tripDate,
Trip = trip,
WeekDay = weekDay,
Days = index + 1,
Traffic_First = "飞机",
Traffic_Second = "汽车",
Diffgroup = 1,
Issel = 1,
IsDel = 0,
});
continue;
}
if (time.Day != fallToTime.Day) //超出一天
{
NewListTravel.Add(new Grp_TravelList
{
CreateTime = DateTime.Now,
CreateUserId = 0,
Diid = diid,
Date = tripDate,
Trip = trip,
WeekDay = weekDay,
Days = index + 1,
Traffic_First = "飞机",
Traffic_Second = "汽车",
Diffgroup = 1,
Issel = 1,
IsDel = 0,
});
continue;
}
var obInfo = obDatas.Find(it => Convert.ToDateTime(it.Date).ToString("M月d日").Equals(tripDate));
string obtime = "";
string obcontent = "";
if (obInfo != null)
{
obtime = obInfo.Time;
obcontent = $"拜访{obInfo.Client ?? "公务出访单位未录入"}{obInfo.Job ?? "公务出访联系人职务未录入"}{obInfo.Contact ?? "公务出访联系人职务未录入"}";
}
bool obTime_bool = DateTime.TryParse(obtime, out DateTime obTimeDt);
if (time.Hour < 9) // && (airArrive < new DateTime(airArrive.Year,airArrive.Month,airArrive.Day,6, 30, 0))
{
if (obTime_bool)
{
if (obTimeDt.Hour < 9)
{
trip += $"{obTimeDt.ToString("HH:mm")}—10:30 {obcontent}；\r\n";
}
}
else
{
trip += "09:00—10:30 公务活动；\r\n";
}
}
else if (time.Hour < 10)
{
if (obTime_bool)
{
if (obTimeDt.Hour < 10)
{
trip += $"{obTimeDt.ToString("HH:mm")}—12:00 {obcontent}；\r\n";
}
}
else
{
trip += "10:30—12:00 公务活动；\r\n";
}
}
if (time.Hour < 13)
{
//trip += $"\r\n{time.ToString("HH:mm")} 午餐于当地餐厅;";
}
if (time < new DateTime(time.Year, time.Month, time.Day, 14, 30, 0))
{
if (obTime_bool)
{
if (obTimeDt < new DateTime(time.Year, time.Month, time.Day, 14, 30, 0))
{
trip += $"{obTimeDt.ToString("HH:mm")}—16:00 {obcontent}；\r\n";
}
}
else
{
trip += "14:00—16:00 公务活动；\r\n";
}
}
else if (time.Hour < 16)
{
if (obTime_bool)
{
if (obTimeDt.Hour < 16)
{
trip += $"{obTimeDt.ToString("HH:mm")}—17:30 {obcontent}；\r\n";
}
}
else
{
trip += "16:00—17:30 公务活动；\r\n";
}
}
//if (time.Hour < 18)
//{
// trip += $"\r\n18:00 晚餐于当地餐厅;";
//}
//time = time.AddHours(1);
//trip += $"\r\n{time.ToString("HH:mm")} 搭乘专车前往酒店，抵达后办理入住；";
}
else
{
var end_Object = threeCodes.Find(x => x.Three.ToUpper() == stopCity.ToUpper());
if (end_Object == null)
{
end_Object = new Res_ThreeCode()
{
AirPort = empty,
AirPort_En = empty,
City = empty,
Country = empty,
Four = empty,
Three = empty,
};
}
//trip += $"{end_Object.City}\r\n";
// trip += @"08:00 早餐于酒店；
//09:00—10:30 公务活动；
//12:00 午餐于当地餐厅；
//14:00—16:00 公务活动；
//18:00 晚餐于当地餐厅；
//19:00 入住酒店休息；";
trip += "09:00—10:30 公务活动；\r\n14:00—16:00 公务活动；\r\n";
}
string[] traffic = new string[] { "飞机", "汽车" };
if (!isMoreTraffic)
{
traffic = new string[] { "汽车", "" };
}
NewListTravel.Add(new Grp_TravelList
{
CreateTime = DateTime.Now,
CreateUserId = 0,
Diid = diid,
Date = Convert.ToDateTime(item).ToString("M月d日"),
Trip = trip,
WeekDay = weekDay,
Days = index + 1,
Traffic_First = traffic[0],
Traffic_Second = traffic[1],
Diffgroup = 1,
Issel = 1,
IsDel = 0,
});
index++;
}
return new Result() { Code = 0, Msg = "获取成功!", Data = NewListTravel };
}
/// <summary>
/// 根据日期获取星期
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string GetWeek(this string dtStr)
{
string week = "";
bool isDt = DateTime.TryParse(dtStr, out DateTime dt);
if (isDt)
{
week = weekdays[(int)dt.DayOfWeek];
}
return week;
}
#endregion
#region 三公费用明细 文件下载
/// <summary>
/// 获取三公费用标准city
/// </summary>
/// <param name="placeData"></param>
/// <param name="nationalTravelFeeId"></param>
/// <returns></returns>
private static string GetEnterExitCostExportCity(List<Grp_NationalTravelFee> placeData, int nationalTravelFeeId)
{
string _city = string.Empty;
if (placeData.Count < 1) return _city;
var data = placeData.Find(it => it.Id == nationalTravelFeeId);
if (data == null) return _city;
string country = data.Country;
string city = data.City;
if (city.Contains("其他城市") || city.Contains("所有城市")) _city = $"{country}-{city}";
else _city = city;
return _city;
}
/// <summary>
/// 三公费用明细 文件下载
/// </summary>
/// <param name="diId"></param>
/// <param name="userId"></param>
/// <param name="CTable"></param>
/// <returns></returns>
public static async Task<JsonView> EnterExitCostDownload(EnterExitCostDownloadDto dto, string fileFormat = "pdf")
{
var _view = new JsonView() { Code = StatusCodes.Status204NoContent };
if (dto.DiId < 1)
{
_view.Msg = "请传入有效的DiId参数；";
return _view;
}
if (dto.ExportType < 1)
{
_view.Msg = MsgTips.Port;
return _view;
}
if (dto.SubTypeId < 1)
{
_view.Msg = @"请传入有效的SubTypeId参数；
1 明细表 --> 1005(默认明细表) 1006(因公出国（境）经费测算明细表) 1007(四川省商务厅出国经费财政先行审核表)
2 表格 --> 1008(派员单位出(境)任务和预算审批意见表) 1009(省级单位出(境)经费报销单)
3 团组成员名单 1 团组成员名单";
return _view;
}
try
{
var enterExitCosts = _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
var dayAndCosts = _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).ToList();
if (enterExitCosts == null)
{
_view.Msg = @"该团组未填写出入境费用；";
return _view;
}
//数据源
List<Grp_DayAndCost> dac1 = dayAndCosts.Where(it => it.Type == 1).ToList(); //住宿费
List<Grp_DayAndCost> dac2 = dayAndCosts.Where(it => it.Type == 2).ToList(); //伙食费
List<Grp_DayAndCost> dac3 = dayAndCosts.Where(it => it.Type == 3).ToList(); //公杂费
List<Grp_DayAndCost> dac4 = dayAndCosts.Where(it => it.Type == 4).ToList(); //培训费
var _CurrDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();
var _DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == dto.DiId).First();
var DeleClientList = _sqlSugar.Queryable<Grp_TourClientList>()
.LeftJoin<Crm_DeleClient>((tcl, dc) => tcl.ClientId == dc.Id && dc.IsDel == 0)
.LeftJoin<Crm_CustomerCompany>((tcl, dc, cc) => dc.CrmCompanyId == cc.Id && dc.IsDel == 0)
.Where((tcl, dc, cc) => tcl.IsDel == 0 && tcl.DiId == dto.DiId)
.Select((tcl, dc, cc) => new
{
Name = dc.LastName + dc.FirstName,
Sex = dc.Sex,
Birthday = dc.BirthDay,
Company = cc.CompanyFullName,
Job = dc.Job
})
.ToList();
var blackCode = _sqlSugar.Queryable<Air_TicketBlackCode>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
var threeCodes = _sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList();
var placeData = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();
var rateDatas = await enterExitCosts.CurrencyRemark.SplitExchangeRate();
_DelegationInfo.VisitCountry = _DelegationInfo.VisitCountry.Replace("|", "、");
if (dto.ExportType == 1) //明细表
{
if (dto.SubTypeId == 1005) //1005(默认明细表)
{
if (!fileFormat.Equals("pdf")) fileFormat = "docx";
//获取模板
string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/出入境费用表模板.docx");
//载入模板
Document doc = new Document(tempPath);
DocumentBuilder builder = new DocumentBuilder(doc);
//利用键值对存放数据
Dictionary<string, string> dic = new Dictionary<string, string>();
decimal stayFeeTotal = dayAndCosts.Where(it => it.Type == 1).Sum(it => it.SubTotal); // 住宿费
decimal mealsFeeTotal = dayAndCosts.Where(it => it.Type == 2).Sum(it => it.SubTotal); // 伙食费费
decimal miscellaneousFeeTotal = dayAndCosts.Where(it => it.Type == 3).Sum(it => it.SubTotal); // 公杂费
decimal tainFeeTotal = dayAndCosts.Where(it => it.Type == 4).Sum(it => it.SubTotal); // 培训费
decimal insidePayTotal = enterExitCosts.InsidePay;
string row1_1 = "";
if (enterExitCosts.Visa > 0)
{
//insidePayTotal += enterExitCosts.Visa;
row1_1 = $"签证费： {enterExitCosts.Visa.ToString("#0.00")} 人民币/人";
if (!string.IsNullOrEmpty(enterExitCosts.VisaRemark))
{
row1_1 += $"\t签证费用描述： {enterExitCosts.VisaRemark}";
}
}
string row1_2 = "";
if (enterExitCosts.YiMiao > 0)
{
//insidePayTotal += enterExitCosts.YiMiao;
row1_2 += $"疫苗费：{enterExitCosts.YiMiao.ToString("#0.00")} 人民币/人";
}
if (enterExitCosts.HeSuan > 0)
{
//insidePayTotal += enterExitCosts.HeSuan;
row1_2 += $"核酸检测费：{enterExitCosts.HeSuan.ToString("#0.00")} 人民币/人";
}
if (enterExitCosts.Service > 0)
{
//insidePayTotal += enterExitCosts.Service;
row1_2 += $"服务费：{enterExitCosts.Service.ToString("#0.00")} 人民币/人";
}
string row1_3 = "";
if (enterExitCosts.Safe > 0)
{
//insidePayTotal += enterExitCosts.Safe;
row1_3 += $"保险费：{enterExitCosts.Safe.ToString("#0.00")} 人民币/人";
}
if (enterExitCosts.Ticket > 0)
{
//insidePayTotal += enterExitCosts.Ticket;
row1_3 += $"参展门票：{enterExitCosts.Ticket.ToString("#0.00")} 人民币/人";
}
string row1 = "";
if (!string.IsNullOrEmpty(row1_1)) row1 += $"{row1_1}\r\n";
if (!string.IsNullOrEmpty(row1_2)) row1 += $"{row1_2}\r\n";
if (!string.IsNullOrEmpty(row1_3)) row1 += $"{row1_3}";
dic.Add("InsidePay", insidePayTotal.ToString("#0.00"));
dic.Add("Row1Str", row1);
dic.Add("OutsideJJ", enterExitCosts.OutsideJJPay.ToString("#0.00"));
dic.Add("OutsaideGW", enterExitCosts.OutsideGWPay.ToString("#0.00"));
dic.Add("AirJJ", enterExitCosts.AirJJ.ToString("#0.00"));
dic.Add("AirGW", enterExitCosts.AirGW.ToString("#0.00"));
dic.Add("CityTranffic", enterExitCosts.CityTranffic.ToString("#0.00"));
dic.Add("SubZS", stayFeeTotal.ToString("#0.00"));
dic.Add("SubHS", mealsFeeTotal.ToString("#0.00"));
string miscellaneousFeeTotalStr = miscellaneousFeeTotal.ToString("#0.00");
dic.Add("SubGZF", miscellaneousFeeTotalStr);
//dic.Add("SubPX", tainFeeTotal.ToString("#0.00"));
decimal subJJC = insidePayTotal + stayFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + tainFeeTotal + enterExitCosts.OutsideJJPay;
decimal subGWC = insidePayTotal + stayFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + tainFeeTotal + enterExitCosts.OutsideGWPay;
dic.Add("SubJJC", subJJC.ToString("#0.00"));
dic.Add("SubGWC", subGWC.ToString("#0.00"));
#region 填充word模板书签内容
foreach (var key in dic.Keys)
{
builder.MoveToBookmark(key);
builder.Write(dic[key]);
}
#endregion
#region 填充word表格内容
////获读取指定表格方法二
NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
for (int i = 0; i < dac1.Count; i++)
{
Grp_DayAndCost dac = dac1[i];
if (dac == null) continue;
builder.MoveToCell(0, i, 0, 0);
builder.Write("第" + dac.Days.ToString() + "晚：");
builder.MoveToCell(0, i, 1, 0);
//builder.Write(placeData.Find(it => it.Id == dac.NationalTravelFeeId)?.Country ?? "Unknown");
//builder.Write(dac.Place == null ? "" : dac.Place);
builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
builder.MoveToCell(0, i, 2, 0);
builder.Write("费用标准：");
string curr = "";
var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
if (currData != null)
{
curr = currData.Name;
}
builder.MoveToCell(0, i, 3, 0);
builder.Write(dac.Cost.ToString("#0.00") + curr);
builder.MoveToCell(0, i, 4, 0);
builder.Write("费用小计：");
builder.MoveToCell(0, i, 5, 0);
builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");
}
//删除多余行
while (table1.Rows.Count > dac1.Count)
{
table1.Rows.RemoveAt(dac1.Count);
}
Aspose.Words.Tables.Table table2 = allTables[1] as Aspose.Words.Tables.Table;
for (int i = 0; i < dac2.Count; i++)
{
Grp_DayAndCost dac = dac2[i];
if (dac == null) continue;
builder.MoveToCell(1, i, 0, 0);
builder.Write("第" + dac.Days.ToString() + "天：");
builder.MoveToCell(1, i, 1, 0);
builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
builder.MoveToCell(1, i, 2, 0);
builder.Write("费用标准：");
string curr = "";
var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
if (currData != null)
{
curr = currData.Name;
}
builder.MoveToCell(1, i, 3, 0);
builder.Write(dac.Cost.ToString("#0.00") + curr);
builder.MoveToCell(1, i, 4, 0);
builder.Write("费用小计：");
builder.MoveToCell(1, i, 5, 0);
builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");
}
//删除多余行
while (table2.Rows.Count > dac2.Count)
{
table2.Rows.RemoveAt(dac2.Count);
}
Aspose.Words.Tables.Table table3 = allTables[2] as Aspose.Words.Tables.Table;
for (int i = 0; i < dac3.Count; i++)
{
Grp_DayAndCost dac = dac3[i];
if (dac == null) continue;
builder.MoveToCell(2, i, 0, 0);
builder.Write("第" + dac.Days.ToString() + "天：");
builder.MoveToCell(2, i, 1, 0);
builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
builder.MoveToCell(2, i, 2, 0);
builder.Write("费用标准：");
string curr = "";
var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
if (currData != null)
{
curr = currData.Name;
}
builder.MoveToCell(2, i, 3, 0);
builder.Write(dac.Cost.ToString("#0.00") + curr);
builder.MoveToCell(2, i, 4, 0);
builder.Write("费用小计：");
builder.MoveToCell(2, i, 5, 0);
builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");
}
//删除多余行
while (table3.Rows.Count > dac3.Count)
{
table3.Rows.RemoveAt(dac3.Count);
}
#endregion
//文件名
//string strFileName = $"{_DelegationInfo.TeamName}出入境费用{Guid.NewGuid().ToString()}.{fileFormat}";
//string strFileName = $"{_DelegationInfo.TeamName}-出入境费用{Guid.NewGuid().ToString()}.{fileFormat}";
string strFileName = $"{_DelegationInfo.TeamName}{DateTime.UtcNow.ToString("yyyyMMddHHmmss")}.{fileFormat}";
AsposeHelper.removewatermark_v2180();
//doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
var saveFormat = Aspose.Words.SaveFormat.Docx;
if (fileFormat.Equals("pdf"))
{
//doc.LayoutOptions.IsShowComments = false;
doc.LayoutOptions.IsShowHiddenText = false;
doc.LayoutOptions.IsShowParagraphMarks = false;
doc.LayoutOptions.RevisionOptions.DeletedTextEffect = RevisionTextEffect.None;
doc.AcceptAllRevisions();
Aspose.Words.Saving.PdfSaveOptions options = new Aspose.Words.Saving.PdfSaveOptions();
options.Compliance = PdfCompliance.PdfA1a;
options.CreateNoteHyperlinks = true;
doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName, options);
//_view.Data = new { Url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName };
_view.Data = new { Url = strFileName };
_view.Msg = "成功";
return _view;
}
doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName, saveFormat);
_view.Data = new { Url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName };
_view.Msg = "成功";
return _view;
}
else if (dto.SubTypeId == 1006)//1006(因公出国（境）经费测算明细表)
{
//获取模板
string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/因公出国（境）经费测算明细表.docx");
//载入模板
Document doc = new Document(tempPath);
DocumentBuilder builder = new DocumentBuilder(doc);
Dictionary<string, string> dic = new Dictionary<string, string>();
if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
{
List<string> list = new List<string>();
try
{
var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
foreach (var item in spilitArr)
{
var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
var depCode = spDotandEmpty[2].Substring(0, 3);
var arrCode = spDotandEmpty[2].Substring(3, 3);
string depName = threeCodes.Find(it => it.Three.Equals(depCode)).City,
arrName = threeCodes.Find(it => it.Three.Equals(arrCode)).City;
list.Add(depName);
list.Add(arrName);
}
list = list.Distinct().ToList();
dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
}
catch (Exception)
{
dic.Add("ReturnCode", "行程录入不正确!");
}
}
else
{
dic.Add("ReturnCode", "未录入行程!");
}
dic.Add("ReturnCodeAir", dic["ReturnCode"]);
dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));
if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
{
TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
dic.Add("Day", sp.Days.ToString());
}
dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
//var Names = string.Join("、", DeleClientList.Select(it => it.Name).ToList()).TrimEnd('、');
//dic.Add("Names", Names);
NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
decimal dac1totalPrice = 0.00M;
int accommodationStartIndex = 6, foodandotherStartIndex = 22;
foreach (var dac in dac1)
{
if (dac.SubTotal == 0.00M)
{
continue;
}
//builder.MoveToCell(0, accommodationStartIndex, 0, 0);
//builder.Write(DeleClientList[i].LastName + DeleClientList[i].Name);
builder.MoveToCell(0, accommodationStartIndex, 1, 0);
builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
builder.MoveToCell(0, accommodationStartIndex, 2, 0);
string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknown";
builder.Write(currency);//币种
builder.MoveToCell(0, accommodationStartIndex, 3, 0);
builder.Write(dac.Cost.ToString("#0.00"));//标准
builder.MoveToCell(0, accommodationStartIndex, 4, 0);
builder.Write("");//人数
builder.MoveToCell(0, accommodationStartIndex, 5, 0);
builder.Write("");//天数
builder.MoveToCell(0, accommodationStartIndex, 6, 0);
builder.Write(dac.SubTotal.ToString("#0.00"));//小计
builder.MoveToCell(0, accommodationStartIndex, 7, 0);
decimal rate = 0.00M;
rate = rateDatas.Find(it => it.CurrencyName.Equals(currency))?.Rate ?? 0.00M;
builder.Write(rate.ToString("#0.0000"));//汇率
builder.MoveToCell(0, accommodationStartIndex, 8, 0);
decimal rbmPrice = dac.SubTotal;
builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
accommodationStartIndex++;
dac1totalPrice += Convert.ToDecimal(rbmPrice.ToString("#0.00"));
}
dic.Add("dac1totalPrice", dac1totalPrice.ToString("#0.00"));
for (int i = 21; i > (dac1.Count == 0 ? 1 : dac1.Count) + 6; i--)
{
table1.Rows.RemoveAt(i - 1);
foodandotherStartIndex--;
}
if (dac2.Count == dac3.Count)//国家 币种 金额
{
for (int i = 0; i < dac2.Count; i++)
{
dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
}
}
decimal dac2totalPrice = 0.00M;
foreach (var dac in dac2)
{
if (dac.SubTotal == 0)
{
continue;
}
builder.MoveToCell(0, foodandotherStartIndex, 1, 0);
builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
builder.MoveToCell(0, foodandotherStartIndex, 2, 0);
string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknown";
builder.Write(currency);//币种
builder.MoveToCell(0, foodandotherStartIndex, 3, 0);
builder.Write(dac.Cost.ToString("#0.00"));//标准
builder.MoveToCell(0, foodandotherStartIndex, 4, 0);
builder.Write("");//人数
builder.MoveToCell(0, foodandotherStartIndex, 5, 0);
builder.Write("");//天数
builder.MoveToCell(0, foodandotherStartIndex, 6, 0);
builder.Write(dac.SubTotal.ToString("#0.00"));//小计
builder.MoveToCell(0, foodandotherStartIndex, 7, 0);
decimal rate = 0.00M;
rate = rateDatas.Find(it => it.CurrencyName.Equals(currency))?.Rate ?? 0.00M;
builder.Write(rate.ToString("#0.0000"));//汇率
builder.MoveToCell(0, foodandotherStartIndex, 8, 0);
decimal rbmPrice = dac.SubTotal;
builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
foodandotherStartIndex++;
dac2totalPrice += Convert.ToDecimal(rbmPrice.ToString("#0.00"));
}
dic.Add("dac2totalPrice", dac2totalPrice.ToString("#0.00"));
for (int i = foodandotherStartIndex + (15 - dac2.Count); i > (dac2.Count == 0 ? 1 : 0) + foodandotherStartIndex; i--)
{
table1.Rows.RemoveAt(i - 1);
}
dic.Add("CityTranffic", enterExitCosts.CityTranffic.ToString("#0.00"));
string otherFeeStr = "";
if (enterExitCosts.Visa > 0) otherFeeStr += $"签证费： {enterExitCosts.Visa.ToString("#0.00")} 元，";
if (enterExitCosts.Safe > 0) otherFeeStr += $"保险费： {enterExitCosts.Safe.ToString("#0.00")} 元，";
if (enterExitCosts.Ticket > 0) otherFeeStr += $"参展门票费： {enterExitCosts.Ticket.ToString("#0.00")} 元，";
if (otherFeeStr.Length > 0)
{
otherFeeStr = otherFeeStr.Substring(0, otherFeeStr.Length - 1);
otherFeeStr = $"({otherFeeStr})";
dic.Add("OtherFeeStr", otherFeeStr);
}
//总计
decimal allPrice = dac1totalPrice + dac2totalPrice + enterExitCosts.Visa + enterExitCosts.Safe + enterExitCosts.Ticket;
//国际旅费
string outsideJJ = "";
string allPriceJJ = "";
if (enterExitCosts.SumJJC == 1)
{
outsideJJ = string.Format(@"经济舱:{0} 元/人", enterExitCosts.AirJJ.ToString("#0.00"));
allPriceJJ = string.Format(@"经济舱:{0} 元/人", (allPrice + enterExitCosts.OutsideJJPay).ToString("#0.00"));
}
string outsideGW = "";
string allPriceGW = "";
if (enterExitCosts.SumGWC == 1)
{
outsideGW = string.Format(@"公务舱:{0} 元/人", enterExitCosts.AirGW.ToString("#0.00"));
allPriceGW = string.Format(@"公务舱:{0} 元/人", (allPrice + enterExitCosts.OutsideGWPay).ToString("#0.00"));
}
if (enterExitCosts.SumJJC == 1 || enterExitCosts.SumGWC == 1)
{
string InTravelPriceStr = string.Format(@" ({0} {1})", outsideJJ, outsideGW);
dic.Add("InTravelPrice", InTravelPriceStr);
string FinalSumPriceStr = string.Format(@" ({0} {1})", allPriceJJ, allPriceGW);
dic.Add("FinalSumPrice", FinalSumPriceStr);
}
//dic.Add("VisaPay", enterExitCosts.Visa.ToString("#0.00"));
//dic.Add("SafePay", enterExitCosts.Safe.ToString("#0.00"));
//dic.Add("YiMiao", enterExitCosts.YiMiao.ToString("#0.00"));
foreach (var key in dic.Keys)
{
builder.MoveToBookmark(key);
builder.Write(dic[key]);
}
//模板文件名
string strFileName = $"{_DelegationInfo.TeamName}因公出国（境）经费测算明细表.docx";
doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
_view.Data = new { Url = url };
_view.Msg = "成功";
return _view;
}
else if (dto.SubTypeId == 1007) //1007(四川省商务厅出国经费财政先行审核表)
{
//获取模板
string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/四川省商务厅出国经费财政先行审核表.xls");
//载入模板
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(tempPath);
Dictionary<string, string> dic = new Dictionary<string, string>();
if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
{
List<string> list = new List<string>();
try
{
var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
foreach (var item in spilitArr)
{
var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
var depCode = spDotandEmpty[2].Substring(0, 3);
var arrCode = spDotandEmpty[2].Substring(3, 3);
string depName = threeCodes.Find(it => it.Three.Equals(depCode))?.City ?? "Unknwon",
arrName = threeCodes.Find(it => it.Three.Equals(arrCode))?.City ?? "Unknown";
list.Add(depName);
list.Add(arrName);
}
list = list.Distinct().ToList();
dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
}
catch (Exception)
{
dic.Add("ReturnCode", "行程录入不正确!");
}
}
else
{
dic.Add("ReturnCode", "未录入行程!");
}
dic.Add("ReturnCodeAir", dic["ReturnCode"]);
dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));
if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
{
TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
dic.Add("Day", sp.Days.ToString());
}
dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
var Names = string.Join("、", DeleClientList.Select(it => it.Name).ToList()).TrimEnd('、');
designer.SetDataSource("ClientUnit", _DelegationInfo.ClientUnit);
designer.SetDataSource("VisitCountry", _DelegationInfo.VisitCountry);
designer.SetDataSource("Group", _DelegationInfo.TeamName);
designer.SetDataSource("ClientUnitTitle", _DelegationInfo.TeamName);
designer.SetDataSource("Name", Names);
designer.SetDataSource("VisitStartDate", dic["VisitStartDate"] + "-" + dic["VisitEndDate"]);
designer.SetDataSource("Day", dic["Day"] + "天");
designer.SetDataSource("ReturnCode", dic["ReturnCode"]);
designer.SetDataSource("ReturnCodeAir", dic["ReturnCodeAir"]);
int startIndex = 10;
const int startIndexcopy = 10;
if (dac2.Count == dac3.Count)//国家 币种 金额
{
for (int i = 0; i < dac2.Count; i++)
{
dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
}
}
DataTable dtdac1 = new DataTable();
List<string> place = new List<string>();
dtdac1.Columns.AddRange(new DataColumn[] {
new DataColumn(){ ColumnName = "city"},
new DataColumn(){ ColumnName = "curr"},
new DataColumn(){ ColumnName = "criterion"},
new DataColumn(){ ColumnName = "number",DataType = typeof(int)},
new DataColumn(){ ColumnName = "day",DataType = typeof(int)},
new DataColumn(){ ColumnName = "cost", DataType = typeof(decimal)},
new DataColumn(){ ColumnName = "rate", DataType = typeof(decimal) },
new DataColumn(){ ColumnName = "costRMB", DataType = typeof(decimal) },
});
DataTable dtdac2 = new DataTable();
dtdac2.Columns.AddRange(new DataColumn[] {
new DataColumn(){ ColumnName = "city"},
new DataColumn(){ ColumnName = "curr"},
new DataColumn(){ ColumnName = "criterion"},
new DataColumn(){ ColumnName = "number",DataType = typeof(int)},
new DataColumn(){ ColumnName = "day",DataType = typeof(int)},
new DataColumn(){ ColumnName = "cost", DataType = typeof(decimal)},
new DataColumn(){ ColumnName = "rate", DataType = typeof(decimal) },
new DataColumn(){ ColumnName = "costRMB",DataType = typeof(decimal)},
});
dtdac1.TableName = "tb1";
dtdac2.TableName = "tb2";
decimal dac1totalPrice = 0.00M, dac2totalPrice = 0.00M;
foreach (var item in dac1)
{
item.Place = GetEnterExitCostExportCity(placeData, item.NationalTravelFeeId);
if (place.Contains(item.Place))
{
continue;
}
DataRow row = dtdac1.NewRow();
row["city"] = item.Place;
string currency = _CurrDatas.Find(it => it.Id == item.Currency)?.Remark ?? "Unknwon";
decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
row["curr"] = currency;
row["rate"] = rate.ToString("#0.0000");
row["criterion"] = item.Cost.ToString("#0.00");
row["number"] = 1;
row["day"] = dac1.FindAll(x => x.NationalTravelFeeId == item.NationalTravelFeeId).Count;
//row["costRMB"] = rbmPrice;
dtdac1.Rows.Add(row);
place.Add(item.Place);
}
place = new List<string>();
foreach (var item in dac2)
{
item.Place = GetEnterExitCostExportCity(placeData, item.NationalTravelFeeId);
if (place.Contains(item.Place))
{
continue;
}
DataRow row = dtdac2.NewRow();
row["city"] = item.Place;
string currency = _CurrDatas.Find(it => it.Id == item.Currency)?.Remark ?? "Unknwon";
decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
row["curr"] = currency;
row["rate"] = rate.ToString("#0.0000");
row["criterion"] = item.Cost.ToString("#0.00");
row["number"] = 1;
row["day"] = dac2.FindAll(x => x.NationalTravelFeeId == item.NationalTravelFeeId).Count;
//row["cost"] = item.SubTotal;
//row["costRMB"] = rbmPrice;
dtdac2.Rows.Add(row);
place.Add(item.Place);
//dac2totalPrice += rbmPrice;
}
dac1totalPrice = dac1.Sum(it => it.SubTotal);
dac2totalPrice = dac2.Sum(it => it.SubTotal);
designer.SetDataSource("dac1totalPrice", dac1totalPrice.ToString("#0.00"));
designer.SetDataSource("dac2totalPrice", dac2totalPrice);
designer.SetDataSource("cityTranffic", @$"其中：国外城市间机票费: {enterExitCosts.CityTranffic.ToString("#0.00")} 元");
designer.SetDataSource("sumCityTranffic", @$"{enterExitCosts.CityTranffic.ToString("#0.00")} ");
string cell4Str = $" 4.国际旅费：经济舱：{enterExitCosts.AirJJ.ToString("#0.00")} 元/人,公务舱：{enterExitCosts.AirGW.ToString("#0.00")} 元/人";
string cellStr = $" 5.其他费用（";
if (enterExitCosts.Visa > 0) cellStr += $"签证费：{enterExitCosts.Visa.ToString("#0.00")}元，";
if (enterExitCosts.YiMiao > 0) cellStr += $"疫苗费：{enterExitCosts.YiMiao.ToString("#0.00")}元，";
if (enterExitCosts.HeSuan > 0) cellStr += $"核酸费：{enterExitCosts.HeSuan.ToString("#0.00")}元，";
if (enterExitCosts.Safe > 0) cellStr += $"保险费：{enterExitCosts.Safe.ToString("#0.00")}元，";
if (enterExitCosts.Ticket > 0) cellStr += $"参展门票费：{enterExitCosts.Ticket.ToString("#0.00")}元，";
if (enterExitCosts.Service > 0) cellStr += $"服务费：{enterExitCosts.Service.ToString("#0.00")}元，";
if (cellStr.Length > 8)
{
cellStr = cellStr.Substring(0, cellStr.Length - 1);
}
cellStr += "）";
decimal otherFee = enterExitCosts.Visa + enterExitCosts.YiMiao + enterExitCosts.HeSuan + enterExitCosts.Safe + enterExitCosts.Ticket + enterExitCosts.Service;
decimal s = dac1totalPrice + dac2totalPrice + enterExitCosts.OutsideJJPay + enterExitCosts.OutsideGWPay + otherFee;
decimal pxFee = dac4.Sum(it => it.Cost);
decimal glvFee = enterExitCosts.OutsideJJPay + enterExitCosts.OutsideGWPay;
string celllastStr1 = "";
if (dac1totalPrice > 0) celllastStr1 += $"住宿费 {dac1totalPrice.ToString("#0.00")} 元";
if (dac2totalPrice > 0) celllastStr1 += $"，伙食费和公杂费 {dac2totalPrice.ToString("#0.00")} 元";
if (pxFee > 0) celllastStr1 += $"，培训费 {pxFee.ToString("#0.00")} 元";
celllastStr1 += $"，国际旅费 元";
if (otherFee > 0) celllastStr1 += $"，其他费用 {otherFee.ToString("#0.00")} 元";
string celllastStr = $" 经审核，{celllastStr1}，本次出国经费预算合计为 元。其中：市本级安排 。";
designer.SetDataSource("cell4Str", cell4Str);
designer.SetDataSource("cellStr", cellStr);
designer.SetDataSource("cellSum", (enterExitCosts.Visa + enterExitCosts.Safe).ToString("#0.00"));
designer.SetDataSource("cellSum4", (enterExitCosts.OutsideJJPay + enterExitCosts.OutsideGWPay).ToString("#0.00"));
designer.SetDataSource("celllastStr", celllastStr);
Workbook wb = designer.Workbook;
var sheet = wb.Worksheets[0];
//绑定datatable数据集
designer.SetDataSource(dtdac1);
designer.SetDataSource(dtdac2);
designer.Process();
var rowStart = dtdac1.Rows.Count;
while (rowStart > 0)
{
sheet.Cells[startIndex, 8].Formula = $"=G{startIndex + 1} * H{startIndex + 1}";
sheet.Cells[startIndex, 6].Formula = $"=E{startIndex + 1} * F{startIndex + 1} * D{startIndex + 1}";
startIndex++;
rowStart--;
}
sheet.Cells[startIndex, 8].Formula = $"=SUM(I{startIndexcopy + 1}: I{startIndex})";
startIndex += 1; //总计行
rowStart = dtdac2.Rows.Count;
while (rowStart > 0)
{
sheet.Cells[startIndex, 8].Formula = $"= G{startIndex + 1} * H{startIndex + 1}";
sheet.Cells[startIndex, 6].Formula = $"= E{startIndex + 1} * F{startIndex + 1} * D{startIndex + 1}";
startIndex++;
rowStart--;
}
sheet.Cells[startIndex, 8].Formula = $"=SUM(I{startIndexcopy + dtdac1.Rows.Count + 2}: I{startIndex})";
wb.CalculateFormula(true);
//模板文件名
string strFileName = $"{_DelegationInfo.TeamName}-四川省商务厅出国经费财政先行审核表{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls"; ;//$".xls";
designer.Workbook.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
_view.Data = new { Url = url };
_view.Msg = "成功";
return _view;
}
else if (dto.SubTypeId == 1066) //成都市因公临时出国任务和预算审批意见表(外专培训团专用)
{
//获取模板
string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/成都市因公临时出国任务和预算审批意见表.docx");
//载入模板
Document doc = new Document(tempPath);
DocumentBuilder builder = new DocumentBuilder(doc);
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("GroupName", _DelegationInfo.TeamName);
dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
string missionLeader = ""; //团负责人默认接团客户名单第一个人
string missionLeaderJob = "";//负责人job
int groupNumber = 0; //团人数
if (DeleClientList.Count > 0)
{
missionLeader = DeleClientList[0]?.Name ?? "";
missionLeaderJob = DeleClientList[0]?.Job ?? "";
}
dic.Add("MissionLeader", missionLeader); //团负责人
dic.Add("MissionLeaderJob", missionLeaderJob); //团负责人job
dic.Add("GroupNumber", _DelegationInfo.VisitPNumber.ToString()); //团人数
#region MyRegion
//if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
//{
// List<string> list = new List<string>();
// try
// {
// var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
// foreach (var item in spilitArr)
// {
// var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
// var depCode = spDotandEmpty[2].Substring(0, 3);
// var arrCode = spDotandEmpty[2].Substring(3, 3);
// string depName = threeCodes.Find(it => it.Three.Equals(depCode)).City,
// arrName = threeCodes.Find(it => it.Three.Equals(arrCode)).City;
// list.Add(depName);
// list.Add(arrName);
// }
// list = list.Distinct().ToList();
// dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
// }
// catch (Exception)
// {
// dic.Add("ReturnCode", "行程录入不正确!");
// }
//}
//else
//{
// dic.Add("ReturnCode", "未录入行程!");
//}
List<string> countrys = _dirRep.GroupSplitCountry(_DelegationInfo.VisitCountry);
dic.Add("ReturnCode", string.Join("、", countrys));
#endregion
//dic.Add("ReturnCodeAir", dic["ReturnCode"]);
//dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
//dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));
//if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
//{
// TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
// dic.Add("Day", sp.Days.ToString());
//}
dic.Add("Day", _DelegationInfo.VisitDays.ToString());
dic.Add("CultivateDay", dac4.Count.ToString()); //培训天数
// dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
//培训人员名单
int cultivateRowIndex = 7;
foreach (var item in DeleClientList)
{
builder.MoveToCell(0, cultivateRowIndex, 0, 0);
builder.Write(item.Name);
builder.MoveToCell(0, cultivateRowIndex, 1, 0);
builder.Write(item.Sex == 0 ? "男" : item.Sex == 1 ? "女" : "");
builder.MoveToCell(0, cultivateRowIndex, 2, 0);
string birthDay = "";
if (item.Birthday != null)
{
DateTime dt = Convert.ToDateTime(item.Birthday);
birthDay = $"{dt.Year}.{dt.Month}";
}
builder.Write(birthDay);
builder.MoveToCell(0, cultivateRowIndex, 3, 0);
builder.Write(item.Company);
builder.MoveToCell(0, cultivateRowIndex, 4, 0);
builder.Write(item.Job);
cultivateRowIndex++;
}
//删除多余行
//cultivateRowIndex -= 2;
int delRows = 10 + 7 - cultivateRowIndex;
if (delRows > 0)
{
for (int i = 0; i < delRows; i++)
{
table1.Rows.RemoveAt(cultivateRowIndex);
//cultivateRowIndex++;
}
}
decimal hotelFeeTotal = dac1.Sum(it => it.SubTotal);//住宿费
dic.Add("HotelFeeTotal", hotelFeeTotal.ToString("#0.00"));
decimal mealsFeeTotal = dac2.Sum(it => it.SubTotal);//伙食费
dic.Add("MealsFeeTotal", mealsFeeTotal.ToString("#0.00"));
decimal miscellaneousFeeTotal = dac3.Sum(it => it.SubTotal);//公杂费
dic.Add("MiscellaneousFeeTotal", miscellaneousFeeTotal.ToString("#0.00"));
decimal trainingFeeTotal = dac4.Sum(it => it.SubTotal);//培训费
dic.Add("TrainingFeeTotal", trainingFeeTotal.ToString("#0.00"));
decimal cityTranfficFeeToatal = enterExitCosts.CityTranffic; //城市区间交通费
dic.Add("CityTranfficFeeToatal", cityTranfficFeeToatal.ToString("#0.00"));//
//其他费用
decimal otherFeeTotal = enterExitCosts.Visa + enterExitCosts.Safe + enterExitCosts.Ticket + enterExitCosts.YiMiao + enterExitCosts.HeSuan + enterExitCosts.Service;
dic.Add("OtherFeeTotal", otherFeeTotal.ToString("#0.00"));
//其他费用合计
decimal _otherFeeTotal = hotelFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + trainingFeeTotal + cityTranfficFeeToatal + otherFeeTotal;
decimal _jjcFeeToatal = enterExitCosts.AirJJ + _otherFeeTotal; //经济舱
decimal _gwcFeeToatal = enterExitCosts.AirGW + _otherFeeTotal; //公务舱
//公务舱合计
//国际旅费
string outsideJJ = "";
string allPriceJJ = "";
if (enterExitCosts.SumJJC == 1 && enterExitCosts.SumGWC == 0)
{
dic.Add("AirFeeTotal", enterExitCosts.AirJJ.ToString("#0.00"));
dic.Add("FeeTotal", _jjcFeeToatal.ToString("#0.00"));
}
if (enterExitCosts.SumGWC == 1 && enterExitCosts.SumJJC == 0)
{
dic.Add("AirFeeTotal", enterExitCosts.AirGW.ToString("#0.00"));
dic.Add("FeeTotal", _gwcFeeToatal.ToString("#0.00"));
}
if (enterExitCosts.SumJJC == 1 && enterExitCosts.SumGWC == 1)
{
string airFeeTotalStr = string.Format(@$"经济舱:{enterExitCosts.AirJJ.ToString("#0.00")} 公务舱:{enterExitCosts.AirGW.ToString("#0.00")}");
dic.Add("AirFeeTotal", airFeeTotalStr);
string feeTotalStr = string.Format(@$"经济舱:{_jjcFeeToatal.ToString("#0.00")} 公务舱:{_gwcFeeToatal.ToString("#0.00")}");
dic.Add("FeeTotal", feeTotalStr);
}
foreach (var key in dic.Keys)
{
builder.MoveToBookmark(key);
builder.Write(dic[key]);
}
//模板文件名
string strFileName = $"{_DelegationInfo.TeamName}-成都市因公临时出国任务和预算审批意见表(外专培训团专用).docx";
doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
_view.Data = new { Url = url };
_view.Msg = "成功";
return _view;
}
}
else if (dto.ExportType == 2) //表格
{
//利用键值对存放数据
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));
TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
dic.Add("Day", sp.Days.ToString());
dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
if (dto.SubTypeId == 1008) //1008(派员单位出(境)任务和预算审批意见表)
{
//获取模板
string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/派员单位出(境)任务和预算审批意见表.docx");
//载入模板
Document doc = new Document(tempPath);
DocumentBuilder builder = new DocumentBuilder(doc);
dic.Add("TeamName", _DelegationInfo.TeamName);
dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
//dic.Add("TellPhone", _DelegationInfo.TellPhone);
string missionLeaderName = "",
missionLeaderJob = "";
if (DeleClientList.Count > 0)
{
missionLeaderName = DeleClientList[0].Name;
missionLeaderJob = DeleClientList[0].Job;
}
dic.Add("MissionLeaderName", missionLeaderName);
dic.Add("MissionLeaderJob", missionLeaderJob);
dic.Add("VisitPNumber", _DelegationInfo.VisitPNumber.ToString());
dic.Add("VisitPurpose", _DelegationInfo.VisitPurpose);
NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
int rowCount = 10;//总人数行
int startRowIndex = 7; //起始行
for (int i = 0; i < DeleClientList.Count; i++)
{
builder.MoveToCell(0, startRowIndex, 0, 0);
builder.Write(DeleClientList[i].Name); //出国人员姓名
builder.MoveToCell(0, startRowIndex, 1, 0);
string sex = DeleClientList[i].Sex == 0 ? "男" : DeleClientList[i].Sex == 1 ? "女" : "";
builder.Write(sex);//性别
builder.MoveToCell(0, startRowIndex, 2, 0);
builder.Write(Convert.ToDateTime(DeleClientList[i].Birthday).ToString("yyyy年MM月dd日"));//出生年月
builder.MoveToCell(0, startRowIndex, 3, 0);
builder.Write(DeleClientList[i].Company);//工作单位
builder.MoveToCell(0, startRowIndex, 4, 0);
builder.Write(DeleClientList[i].Job);//职务及级别
builder.MoveToCell(0, startRowIndex, 5, 0);
builder.Write("");//人员属性
builder.MoveToCell(0, startRowIndex, 6, 0);
builder.Write("");//上次出国时间
startRowIndex++;
}
int nullRow = rowCount - DeleClientList.Count;//空行
for (int i = 0; i < nullRow; i++)
{
table1.Rows.Remove(table1.Rows[startRowIndex]);
}
foreach (var key in dic.Keys)
{
builder.MoveToBookmark(key);
builder.Write(dic[key]);
}
//模板文件名
string strFileName = $"派员单位出(境)任务和预算审批意见表.docx";
doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
_view.Data = new { Url = url };
_view.Msg = "成功";
return _view;
}
else if (dto.SubTypeId == 1009)//1009(省级单位出(境)经费报销单)
{
//获取模板
string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/省级单位出(境)经费报销单.docx");
//载入模板
Document doc = new Document(tempPath);
DocumentBuilder builder = new DocumentBuilder(doc);
dic.Add("GroupClient", _DelegationInfo.ClientUnit);
var Names = string.Join("、", DeleClientList.Select(x => x.Name)).TrimEnd('、');
dic.Add("Names", Names);
int accommodationRows = 12, foodandotherRows = 12;
var Dac1currCn = dac1.GroupBy(x => x.Currency).Select(x => x.Key).ToList();
var Dac2currCn = dac2.GroupBy(x => x.Currency).Select(x => x.Key).ToList();
NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
int accommodationStartIndex = 6;
decimal dac1totalPrice = 0.00M;
foreach (var dac in dac1)
{
if (dac.SubTotal == 0)
{
continue;
}
//builder.MoveToCell(0, accommodationStartIndex, 0, 0);
//builder.Write(DeleClientList[i].LastName + DeleClientList[i].Name);
builder.MoveToCell(0, accommodationStartIndex, 1, 0);
builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));//城市
builder.MoveToCell(0, accommodationStartIndex, 2, 0);
string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknwon";
builder.Write(currency);//币种
builder.MoveToCell(0, accommodationStartIndex, 3, 0);
builder.Write(dac.Cost.ToString("#0.00"));//标准
builder.MoveToCell(0, accommodationStartIndex, 4, 0);
builder.Write("");//人数
builder.MoveToCell(0, accommodationStartIndex, 5, 0);
builder.Write("");//天数
builder.MoveToCell(0, accommodationStartIndex, 6, 0);
builder.Write(dac.SubTotal.ToString("#0.00"));//小计
builder.MoveToCell(0, accommodationStartIndex, 7, 0);
decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
builder.Write(rate.ToString("#0.0000"));//汇率
builder.MoveToCell(0, accommodationStartIndex, 8, 0);
decimal rbmPrice = rate * dac.SubTotal;
builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
accommodationStartIndex++;
dac1totalPrice += rbmPrice;
}
dic.Add("dac1totalPrice", dac1totalPrice.ToString("#0.00"));
builder.MoveToCell(0, accommodationStartIndex, 1, 0);
builder.Write("小计");
builder.MoveToCell(0, accommodationStartIndex, 8, 0);
builder.Write(dac1totalPrice.ToString("#0.00"));
accommodationStartIndex++;
int nullRow = accommodationRows - dac1.Count;
//删除空行
//if (nullRow > 0)
//{
// int rowIndex = accommodationStartIndex;
// for (int i = 0; i < nullRow; i++)
// {
// Row row = table1.Rows[rowIndex];
// row.Remove();
// rowIndex++;
// }
//}
if (dac2.Count == dac3.Count)//国家 币种 金额
{
for (int i = 0; i < dac2.Count; i++)
{
dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
}
}
int foodandotherStartIndex = 19;//
decimal dac2totalPrice = 0.00M;
foreach (var dac in dac2)
{
if (dac.SubTotal == 0)
{
continue;
}
//foodandotherStartIndex = 12;
builder.MoveToCell(0, foodandotherStartIndex, 1, 0);
builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));//城市
builder.MoveToCell(0, foodandotherStartIndex, 2, 0);
string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknwon";
builder.Write(currency);//币种
builder.MoveToCell(0, foodandotherStartIndex, 3, 0);
builder.Write(dac.Cost.ToString("#0.00"));//标准
builder.MoveToCell(0, foodandotherStartIndex, 4, 0);
builder.Write("");//人数
builder.MoveToCell(0, foodandotherStartIndex, 5, 0);
builder.Write("");//天数
builder.MoveToCell(0, foodandotherStartIndex, 6, 0);
builder.Write(dac.SubTotal.ToString("#0.00"));//小计
builder.MoveToCell(0, foodandotherStartIndex, 7, 0);
decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
builder.Write(rate.ToString("#0.0000"));//汇率
builder.MoveToCell(0, foodandotherStartIndex, 8, 0);
decimal rbmPrice = rate * dac.SubTotal;
builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
foodandotherStartIndex++;
dac2totalPrice += rbmPrice;
}
dic.Add("dac2totalPrice", dac2totalPrice.ToString("#0.00"));
//删除空行
if (dac2.Count < foodandotherRows)
{
//int nullRow = accommodationRows - dac2.Count;
//while (table2.Rows.Count > dac2.Count)
//{
// table2.Rows.RemoveAt(dac2.Count);
//}
}
dic.Add("CityTranffic", enterExitCosts.CityTranffic.ToString("#0.00"));
string otherFeeStr = "";
if (enterExitCosts.Visa > 0) otherFeeStr += $"签证费： {enterExitCosts.Visa.ToString("#0.00")} 元，";
if (enterExitCosts.Safe > 0) otherFeeStr += $"保险费： {enterExitCosts.Safe.ToString("#0.00")} 元，";
if (enterExitCosts.Ticket > 0) otherFeeStr += $"参展门票费： {enterExitCosts.Ticket.ToString("#0.00")} 元，";
if (otherFeeStr.Length > 0)
{
otherFeeStr = otherFeeStr.Substring(0, otherFeeStr.Length - 1);
otherFeeStr = $"({otherFeeStr})";
dic.Add("OtherFeeStr", otherFeeStr);
}
foreach (var key in dic.Keys)
{
builder.MoveToBookmark(key);
builder.Write(dic[key]);
}
//模板文件名
string strFileName = $"省级单位出(境)经费报销单.docx";
doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
_view.Data = new { Url = url };
_view.Msg = "成功";
return _view;
}
}
else if (dto.ExportType == 3)
{
if (dto.SubTypeId == 1) //团组成员名单
{
if (DeleClientList.Count < 1)
{
_view.Msg = "团组成员暂未录入!!!";
return _view;
}
//获取模板
string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/dwon_团组人员列表模板.doc");
//载入模板
Document doc = new Document(tempPath);
DocumentBuilder builder = new DocumentBuilder(doc);
//获取word里所有表格
NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
//获取所填表格的序数
Aspose.Words.Tables.Table tableOne = allTables[0] as Aspose.Words.Tables.Table;
var rowStart = tableOne.Rows[0]; //获取第1行
//循环赋值
for (int i = 0; i < DeleClientList.Count; i++)
{
builder.MoveToCell(0, i + 1, 0, 0);
builder.Write(DeleClientList[i].Name);
builder.MoveToCell(0, i + 1, 1, 0);
string sex = DeleClientList[i].Sex == 0 ? "男" : DeleClientList[i].Sex == 1 ? "女" : "";
builder.Write(sex);
builder.MoveToCell(0, i + 1, 2, 0);
builder.Write(Convert.ToDateTime(DeleClientList[i].Birthday).ToString("yyyy年MM月dd日"));
builder.MoveToCell(0, i + 1, 3, 0);
builder.Write(DeleClientList[i].Company);
builder.MoveToCell(0, i + 1, 4, 0);
builder.Write(DeleClientList[i].Job);
}
//删除多余行
while (tableOne.Rows.Count > DeleClientList.Count + 1)
{
tableOne.Rows.RemoveAt(DeleClientList.Count + 1);
}
string strFileName = $"{_DelegationInfo.TeamName}组团人员名单({DateTime.Now.ToString("yyyyMMddHHmmss")}).doc";
//C:/Server/File/OA2023/Office/Word/EnterExitCost/File/
//C:\Server\File\OA2023\Office\Word\EnterExitCost\File\
doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;
_view.Data = new { Url = url };
_view.Msg = "成功";
return _view;
}
}
}
catch (Exception ex)
{
_view.Code = StatusCodes.Status500InternalServerError;
_view.Msg = ex.Message;
return _view;
}
return _view;
}
#endregion
#region iOS推送通知
#endregion
}
}