前言
我司是客户MES(生产制造执行系统）供应商。甲方公司有IT和运维，为了减少客制化开发成本，IT会自行开发程序、接口等，希望我司能够接入IT开发的程序，由甲方自行配置定义调度方式 频率等。
本文分为成品界面展示、技术栈、数据库表设计、代码分享与讲解四个章节，篇幅较长会分多次更新，感兴趣的朋友可以点下收藏订阅持续阅读。
有任何问题欢迎评论、探讨指正
一、成品界面展示
任务分组：用于收纳任务，便于查看
任务清单：查看任务状态、执行状况
创建任务，目前支持5种类型
HttpRequest：发送http请求
AutoReport*：自动化报表，功能非常强大，可以根据SQL循环发送邮件，邮件全字段动态，{{}} 写SQL，支持附件，根据SQL导出（CSV,EXCEL两种格式）
可执行程序
dll类库
脚本（目前支持cmd,python,js）
日志
二、技术栈
前端：VUE3 + element ui
后端：.net 6 + furion(底层框架) + sqlsugar(国产orm) + hangfire + MailKit(邮件依赖) + MiniExcel(excel导出依赖)
数据库：Oracle 11g
三、数据库设计
正常情况来说，在注册hangfire服务启动程序后会自动创建相应的表，但特殊情况或使用量比较少的hangfire orm依赖可能在这一步会出问题，这个时候就手动建表，另外，本文的表在hangfire原生表中做了很多扩展。
hf_server：记录hangfire服务器的信息（如使用默认配置，每次启动程序就是一个新的serverid）
hf_set：这张表非常重要！红框内是在原生的基础上新增的字段。此表用于记录创建的每个任务，
如调用RecurringJob.AddOrUpdate后，如果key+value不存在就会在这张表中新增一行。
hf_hash：任务创建后会在这张表中添加参数 下一次触发时间、创建时间、Cron表达式等等
hf_job：作业表，RecurringJob(循环任务)设定后每次执行都会在这张表中增加一条记录
hf_job_parameter：作业参数表
hf_job_state：作业状态表，作业每一次执行的状态（Enqueued 入队列，Processing 执行中，Failed 失败，Successed 成功，Deleted 已删除）都会被记录此表
auto_task_group：任务分组表 本文扩展表，原生表没有。
auto_task_group_rel：任务分组&hf_set关系表，本文扩展表，原生表没有。
sys_mail_sender_cfg：发件邮箱配置表，本文扩展表，原生表没有。当任务类型为autoreport(自动化报表)，且勾选邮件时，读取此配置表发送邮件，支持设置多个发件邮箱
四、代码分享与讲解
代码结构
AutoTaskService：自动任务核心服务
Dtos文件夹：存放数据传输对象
Job：作业具体实现代码
AutoTaskFilter(定时任务过滤器)
非常重要，因为UI可以设定任务是否启用，hangfire是不支持设置暂停任务或触发器的。
通过此过滤器在作业执行前后拦截，判断是否可以执行作业，不允许执行的情况下将Canceled属性设置为false
public class AutoTaskFilter : JobFilterAttribute, IServerFilter
{
public AutoTaskFilter()
{
}
/// <summary>
/// 执行前
/// </summary>
/// <param name="filterContext"></param>
public void OnPerforming(PerformingContext filterContext)
{
var job = filterContext.BackgroundJob.Job;
var recurringJobId = job.Args.First().ToString();
var logger = App.GetService<ILogger>();
var db = App.GetService<ISqlSugarClient>();
var hfSet = db.Queryable<HfSet>().Single(set => set.Value == recurringJobId);
var currTime = DateTime.Now;
if (hfSet?.StartTime > DateTime.Now)
{
filterContext.Canceled = true;
logger.Information($"任务[{recurringJobId}]未达到执行开始时间[{hfSet.StartTime}],取消执行");
return;
}
if (hfSet?.EndTime < DateTime.Now)
{
filterContext.Canceled = true;
logger.Information($"任务[{recurringJobId}]已超过执行结束时间[{hfSet.StartTime}],取消执行");
return;
}
if (hfSet.Status != EHfSetStatus.启用)
{
filterContext.Canceled = true;
logger.Information($"任务[{recurringJobId}]当前状态为[{hfSet.Status}],取消执行");
return;
}
logger.Information($"任务[{recurringJobId}]开始执行,传入参数:" + Environment.NewLine + job.Args[1]);
}
/// <summary>
/// 执行后
/// </summary>
/// <param name="filterContext"></param>
public void OnPerformed(PerformedContext filterContext)
{
lock (objLock)
{
var job = filterContext.BackgroundJob.Job;
var recurringJobId = job.Args.First().ToString();
var logger = App.GetService<ILogger>();
logger.Information($"任务[{recurringJobId}]执行完成");
}
}
}添加定时任务
需要注意的是，本文对hf_set做了扩展，需要另外写代码更新扩展字段以及添加分组和het_set关系
/// <summary>
/// 添加任务 cron不写会生成一个默认每天0点的触发器
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task AddJob(AddJobRequest request)
{
//检查Job是否存在 名称为key
var any = await _db.Queryable<HfSet>().AnyAsync(set => set.Value == request.JobName);
if (any) throw Oops.Oh($"定时任务[{request.JobName}]已存在");
var cron = string.IsNullOrEmpty(request.Cron) ? _defaultCron : request.Cron;
IAutoTaskJob job = GetJobService(request.JobType);
if (job is null) throw Oops.Oh($"任务类型[{request.JobType}]暂未实现");
RecurringJob.AddOrUpdate(request.JobName, () => job.Execute(request.JobName, request.Param), cron, new RecurringJobOptions()
{
TimeZone = _chinaTimeZone,
});
var hfSet = await _db.Queryable<HfSet>().SingleAsync(set => set.Value == request.JobName);
// 更新扩展字段 所有事务都处理完后再更新任务
try
{
_db.Ado.BeginTran();
hfSet.JobType = request.JobType;
hfSet.Status = request.JobStatus;
hfSet.Param = request.Param;
hfSet.StartTime = request.StartTime;
hfSet.EndTime = request.EndTime;
await _db.Updateable(hfSet).ExecuteCommandAsync();
// 添加分组 任务关系
var rel = new AutoTaskGroupRel()
{
HfSetId = hfSet.Id,
GroupId = request.GroupId
};
await _autoTaskGroupRel.AddAsync(rel);
_db.Ado.CommitTran();
}
catch (Exception)
{
_db.Ado.RollbackTran();
throw;
}
} public class AddJobRequest
{
public string JobName { get; set; }
public EJobType JobType { get; set; }
public EHfSetStatus JobStatus { get; set; }
public DateTime? StartTime { get; set; }
public DateTime? EndTime { get; set; }
public string Cron { get; set; }
public string Param { get; set; }
public long GroupId { get; set; }
}
public enum EHfSetStatus
{
停止,
启用
}
public enum EJobType
{
HttpRequest,
AutoReport,
Exe,
Dll,
Script
} private IAutoTaskJob GetJobService(EJobType jobType)
{
IAutoTaskJob job;
switch (jobType)
{
case EJobType.HttpRequest:
job = App.GetService<HttpRequestJobService>();
break;
case EJobType.AutoReport:
job = App.GetService<AutoReportJobService>();
break;
case EJobType.Exe:
job = App.GetService<ExeJobService>();
break;
case EJobType.Dll:
job = App.GetService<DllJobService>();
break;
case EJobType.Script:
job = App.GetService<ScriptJobService>();
break;
default:
throw Oops.Oh($"未定义的任务类型[{jobType}]");
}
return job;
} public interface IAutoTaskJob
{
void Execute(string recurringJobId, string param);
}修改定时任务
/// <summary>
/// 修改任务
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task UpdateJob(UpdateJobRequest request)
{
var hfSet = await _db.Queryable<HfSet>().SingleAsync(set => set.Id == request.JobId);
_ = hfSet ?? throw Oops.Oh($"当前任务不存在，请刷新");
var cron = string.IsNullOrEmpty(request.Cron) ? _defaultCron : request.Cron;
IAutoTaskJob job = GetJobService(request.JobType);
RecurringJob.AddOrUpdate(hfSet.Value, () => job.Execute(hfSet.Value, request.Param), cron, new RecurringJobOptions()
{
TimeZone = _chinaTimeZone
});
// 更新扩展字段 所有事务都处理完后再更新任务
try
{
_db.Ado.BeginTran();
hfSet.JobType = request.JobType;
hfSet.Status = request.JobStatus;
hfSet.Param = request.Param;
hfSet.StartTime = request.StartTime;
hfSet.EndTime = request.EndTime;
await _db.Updateable(hfSet).ExecuteCommandAsync();
_db.Ado.CommitTran();
}
catch (Exception)
{
_db.Ado.RollbackTran();
throw;
}
} public class UpdateJobRequest
{
public int JobId { get; set; }
public EJobType JobType { get; set; }
public EHfSetStatus JobStatus { get; set; }
public string Cron { get; set; }
public DateTime? StartTime { get; set; }
public DateTime? EndTime { get; set; }
public string Param { get; set; }
public long GroupId { get; set; }
}新增修改触发器
/// <summary>
/// 新增修改触发器
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task AddOrUpdateTrigger(AddOrTriggerRequest request)
{
var hfSet = await _db.Queryable<HfSet>().SingleAsync(set => set.Id == request.JobId);
IAutoTaskJob job = GetJobService(hfSet.JobType.Value);
RecurringJob.AddOrUpdate(hfSet.Value, () => job.Execute(hfSet.Value, hfSet.Param), request.Cron, new RecurringJobOptions()
{
TimeZone = _chinaTimeZone,
});
}删除任务
/// <summary>
/// 删除任务
/// </summary>
/// <param name="hfSetId"></param>
/// <returns></returns>
[UnitOfWork]
public async Task DeleteJob(int hfSetId)
{
var hfSet = await _db.Queryable<HfSet>().SingleAsync(set => set.Id == hfSetId);
if (hfSet is null) return;
// 移除任务分组关系
await _autoTaskGroupRel.DeleteAsync(r => r.HfSetId == hfSet.Id);
RecurringJob.RemoveIfExists(hfSet.Value);
}停止任务
/// <summary>
/// 暂停任务
/// </summary>
/// <param name="hfSetId"></param>
/// <returns></returns>
public async Task PauseJob(int hfSetId)
{
// 拿到hfSet对象
var hfSet = await _db.Queryable<HfSet>().SingleAsync(set => set.Id == hfSetId);
if (hfSet.Status != EHfSetStatus.启用) throw Oops.Oh($"当前任务状态为[{hfSet.Status}]，无法停止");
// 修改hfSet状态
hfSet.Status = EHfSetStatus.停止;
await _db.Updateable(hfSet).ExecuteCommandAsync();
}恢复任务
/// <summary>
/// 恢复任务
/// </summary>
/// <param name="hfSetId"></param>
/// <returns></returns>
//[UnitOfWork]
public async Task RecoverJob(int hfSetId)
{
// 拿到hfSet对象
var hfSet = await _db.Queryable<HfSet>().SingleAsync(set => set.Id == hfSetId);
if (hfSet.Status != EHfSetStatus.停止) throw Oops.Oh($"当前任务状态为[{hfSet.Status}]，无法启用");
//修改hfSet状态
hfSet.Status = EHfSetStatus.启用;
await _db.Updateable(hfSet).ExecuteCommandAsync();
}立刻触发任务
/// <summary>
/// 立刻触发
/// </summary>
/// <param name="hfSetId"></param>
/// <returns></returns>
public async Task TriggerNow(int hfSetId)
{
var hfSet = await _db.Queryable<HfSet>().SingleAsync(set => set.Id == hfSetId);
RecurringJob.TriggerJob(hfSet.Value);
}获取日志
由hangfire底层表拼接得到的viewmodel
public async Task<List<GetLogResponse>> GetLog(int hfSetId)
{
var hfSet = await _db.Queryable<HfSet>().SingleAsync(set => set.Id == hfSetId);
_ = hfSet ?? throw Oops.Oh("当前任务不存在");
var jobParamQueryable = _db.SqlQueryable<HfJobParameter>("select * from hf_job_parameter")
.Where($"name = 'RecurringJobId'")
.Where($"to_char(value) = '\"{hfSet.Value}\"'")
.OrderByDescending(jp => jp.Id)
.Take(100);
// 只取前100条日志
var jobParamList = await jobParamQueryable.ToListAsync();
var jobIds = jobParamQueryable.Select(jp => jp.JobId).ToList().Distinct();
var jobQueryable = _db.Queryable<HfJob>().Where(jp => jobIds.Contains(jp.Id));
var jobList = await jobQueryable.ToListAsync();
var jobStateIds = jobQueryable.Select(j => j.StateId).ToList();
var jobStateQueryable = _db.Queryable<HfJobState>().Where(js => jobStateIds.Contains(js.Id));
var jobStateList = await jobStateQueryable.ToListAsync();
var logRespList = new List<GetLogResponse>();
foreach (var jobState in jobStateList.OrderByDescending(js => js.CreatedAt))
{
var job = jobList.Single(j => j.Id == jobState.JobId);
var jobInvocationDataJObj = JObject.Parse(job.InvocationData);
var jobStateDataJObj = JObject.Parse(jobState.Data);
var logResp = new GetLogResponse()
{
HfSetId = hfSetId,
JobName = hfSet.Value,
Namespace = jobInvocationDataJObj["Type"]?.ToString(),
Method = jobInvocationDataJObj["Method"]?.ToString(),
ParameterTypes = jobInvocationDataJObj["ParameterTypes"]?.ToString(),
Arguments = job.Arguments,
CreateAt = jobState.CreatedAt?.AddHours(8),
Description = jobState.Reason,
State = jobState.Name,
ExceptionType = jobStateDataJObj["ExceptionType"]?.ToString(),
ExceptionMessage = jobStateDataJObj["ExceptionMessage"]?.ToString(),
ExceptionDetails = jobStateDataJObj["ExceptionDetails"]?.ToString()
};
logRespList.Add(logResp);
}
return logRespList;
} public class GetLogResponse
{
public int HfSetId { get; set; }
public int JobId { get; set; }
public string JobName { get; set; }
public string Namespace { get; set; }
public string Method { get; set; }
public string ParameterTypes { get; set; }
public string Arguments { get; set; }
public DateTime? CreateAt { get; set; }
public string State { get; set; }
public string Description { get; set; }
public string ExceptionType { get; set; }
public string ExceptionMessage { get; set; }
public string ExceptionDetails { get; set; }
任务实现服务
HttpRequestJobService
[AutomaticRetry(Attempts = 0)]
public class HttpRequestJobService : IAutoTaskJob, ITransient
{
public HttpRequestJobService()
{
}
public void Execute(string recurringJobId, string param)
{
var httpRequestJobParam = JsonConvert.DeserializeObject<HttpRequestJobParam>(param);
using var httpClient = new HttpClient();
//if (httpRequestJobParam.HtppMethod == EHttpMethod.Get && !string.IsNullOrEmpty(httpRequestJobParam.RequestBody))
var request = new HttpRequestMessage();
switch (httpRequestJobParam.HttpMethod)
{
case EHttpMethod.Get:
request.Method = HttpMethod.Get;
break;
case EHttpMethod.Post:
{
request.Method = HttpMethod.Post;
var content = new StringContent(httpRequestJobParam.RequestBody, Encoding.UTF8, "application/json");
request.Content = content;
}
break;
default:
break;
}
request.RequestUri = new Uri(httpRequestJobParam.Url);
if (httpRequestJobParam.Headers?.Count > 0)
{
foreach (var keyValuePairs in httpRequestJobParam.Headers)
{
request.Headers.Add(keyValuePairs.Key, keyValuePairs.Value);
}
}
var resp = httpClient.SendAsync(request).Result;
if (!resp.IsSuccessStatusCode)
throw new Exception($"request error,status code[{resp.StatusCode}],message -> {resp.ReasonPhrase}");
}
} public class HttpRequestJobParam
{
public string Url { get; set; }
public EHttpMethod HttpMethod { get; set; }
public string RequestBody { get; set; }
public Dictionary<string, string> Headers { get; set; }
}
public enum EHttpMethod
{
Get,
Post
}AutoReportJobService
自动报表类型的任务实现比较复杂，有疑问欢迎提问，ftp部分的逻辑由于还没写完暂时留空，博主完成后会发布更新
[AutomaticRetry(Attempts = 0)]
public class AutoReportJobService : IAutoTaskJob, ITransient, IDynamicApiController
{
public AutoReportJobService()
{
}
public void Execute(string recurringJobId, string param)
{
var autoReportJobParam = JsonConvert.DeserializeObject<AutoReportJobParam>(param);
//var loopItems = ExecuteSQL<string>(autoReportJobParam.MailConfig)
var loopSQL = autoReportJobParam.LoopSQL;
var mailConfig = autoReportJobParam.MailConfig;
var ftpConfig = autoReportJobParam.FTPConfig;
var mailAttachmentsConfig = autoReportJobParam.MailConfig.AttachmentsConfig;
var getSQLPattern = "\\{\\{(.*?)\\}\\}";
if (!string.IsNullOrEmpty(loopSQL))
{
var dt = ExecuteSQL(loopSQL, null);
foreach (DataRow dr in dt.Rows)
{
if (mailConfig is not null)
{
// 抄送人
var cc = mailConfig.CC;
var ccSQL = GetSQL(cc, getSQLPattern);
if (ccSQL is not null)
{
// 遍历执行SQL语句拿到结果
foreach (var item in ccSQL)
{
var paramsDic = new Dictionary<string, object>();
var sqlParams = item.GetSQLParams('@');
foreach (var sqlParam in sqlParams)
{
paramsDic[$"@{sqlParam}"] = dr[sqlParam];
}
var ccList = ExecuteSQL<string>(item.Replace("{{", "").Replace("}}", ""), paramsDic);
cc = cc.Replace($"{item}", string.Join(',', ccList));
}
}
// 收件人
var to = mailConfig.To;
var toSQL = GetSQL(to, getSQLPattern);
if (toSQL is not null)
{
// 遍历执行SQL语句拿到结果
foreach (var item in toSQL)
{
Dictionary<string, object> paramsDic = new Dictionary<string, object>();
var sqlParams = item.GetSQLParams('@');
foreach (var sqlParam in sqlParams)
{
paramsDic[$"@{sqlParam}"] = dr[sqlParam];
}
var toList = ExecuteSQL<string>(item.Replace("{{", "").Replace("}}", ""), paramsDic);
to = to.Replace($"{item}", string.Join(',', toList));
}
}
// 主题
var subject = mailConfig.Subject;
var subjectSQL = GetSQL(subject, getSQLPattern);
if (subjectSQL is not null)
{
// 遍历执行SQL语句拿到结果
foreach (var item in subjectSQL)
{
Dictionary<string, object> paramsDic = new Dictionary<string, object>();
var sqlParams = item.GetSQLParams('@');
foreach (var sqlParam in sqlParams)
{
paramsDic[$"@{sqlParam}"] = dr[sqlParam];
}
var subjectTemp = ExecuteSQL<string>(item.Replace("{{", "").Replace("}}", ""), paramsDic).FirstOrDefault();
subject = subject.Replace($"{item}", subjectTemp);
}
}
// 正文
var body = mailConfig.Body;
var bodySQL = GetSQL(body, getSQLPattern);
if (bodySQL is not null)
{
// 遍历执行SQL语句拿到结果
foreach (var item in bodySQL)
{
Dictionary<string, object> paramsDic = new Dictionary<string, object>();
var sqlParams = item.GetSQLParams('@');
foreach (var sqlParam in sqlParams)
{
paramsDic[$"@{sqlParam}"] = dr[sqlParam];
}
var bodyTemp = ExecuteSQL<string>(item.Replace("{{", "").Replace("}}", ""), paramsDic).FirstOrDefault();
body = body.Replace($"{item}", bodyTemp);
}
}
// 附件
var attachments = new List<IFormFile>();
if (mailAttachmentsConfig != null)
{
foreach (var attachmentConfig in mailAttachmentsConfig)
{
var attachmentSQL = attachmentConfig.SQL;
var attachmentContent = new DataTable();
if (attachmentSQL is null) throw new NullReferenceException("附件导出SQL不可为空");
var paramsDic = new Dictionary<string, object>();
var sqlParams = attachmentSQL.GetSQLParams('@');
foreach (var sqlParam in sqlParams)
{
paramsDic[$"@{sqlParam}"] = dr[sqlParam];
}
attachmentContent = ExecuteSQL(attachmentSQL, paramsDic);
// 附件内容
// 文件名称(附件名)
var fileName = attachmentConfig.FileName;
var fileNameSQL = GetSQL(fileName, getSQLPattern);
if (fileNameSQL is not null)
{
// 遍历执行SQL语句拿到结果
foreach (var item in fileNameSQL)
{
paramsDic.Clear();
sqlParams = item.GetSQLParams('@');
foreach (var sqlParam in sqlParams)
{
paramsDic[$"@{sqlParam}"] = dr[sqlParam];
}
var fileNameTemp = ExecuteSQL<string>(item.Replace("{{", "").Replace("}}", ""), paramsDic).FirstOrDefault();
fileName = fileName.Replace($"{item}", fileNameTemp);
}
}
switch (attachmentConfig.FileType)
{
case EAutoReportJobMailAttachmentsFileType.xlsx:
{
var memoryStream = new MemoryStream();
memoryStream.SaveAs(value: attachmentContent, excelType: ExcelType.XLSX);
var fileExt = '.' + EAutoReportJobMailAttachmentsFileType.xlsx.ToString();
fileName = string.Concat(fileName, fileExt);
var formFile = new FormFile(memoryStream, 0, memoryStream.Length, attachmentConfig.FileName, fileName);
attachments.Add(formFile);
}
break;
case EAutoReportJobMailAttachmentsFileType.csv:
{
var memoryStream = new MemoryStream();
memoryStream.SaveAs(value: attachmentContent, excelType: ExcelType.CSV);
var fileExt = '.' + EAutoReportJobMailAttachmentsFileType.csv.ToString();
fileName = string.Concat(fileName, fileExt);
var formFile = new FormFile(memoryStream, 0, memoryStream.Length, attachmentConfig.FileName, fileName);
attachments.Add(formFile);
}
break;
default:
throw new Exception($"不支持的文件类型[{attachmentConfig.FileType}]");
}
}
}
var mailSendRequest = new MailSendRequest()
{
To = to.Split(',').ToList(),
CC = cc.Split(',').ToList(),
Subject = subject,
Body = body,
IsBodyHtml = mailConfig.IsBodyHtml,
Attachments = attachments
};
var mailService = App.GetService<MailService>();
mailService.Send(mailSendRequest).Wait();
}
if (ftpConfig is not null)
{
}
}
}
}
/// <summary>
/// 根据传入字符串抓取SQL语句
/// </summary>
/// <param name="str">字符串</param>
/// <param name="pattern">正则表达式</param>
/// <returns></returns>
private List<string> GetSQL(string str, string pattern)
{
var regex = new Regex(pattern);
var matchCollection = regex.Matches(str);
if (matchCollection.Count <= 0)
return null;
var result = new List<string>();
foreach (Match item in matchCollection)
{
result.Add(item.Value);
}
return result;
}
private List<T> ExecuteSQL<T>(string sql, Dictionary<string, object> sqlParams)
{
var _db = App.GetService<ISqlSugarClient>();
var sugarParams = new List<SugarParameter>();
if (sqlParams != null)
{
foreach (var key in sqlParams.Keys)
{
sugarParams.Add(new SugarParameter(key, sqlParams[key]));
}
}
return _db.Ado.SqlQuery<T>(sql, sugarParams);
}
private DataTable ExecuteSQL(string sql, Dictionary<string, object> sqlParams)
{
var _db = App.GetService<ISqlSugarClient>();
var sugarParams = new List<SugarParameter>();
if (sqlParams != null)
{
foreach (var key in sqlParams.Keys)
{
sugarParams.Add(new SugarParameter(key, sqlParams[key]));
}
}
return _db.Ado.GetDataTable(sql, sugarParams);
}
}public static IEnumerable<string> GetSQLParams(this string sql, char pattern)
{
bool inString = false;
List<string> paramsList = new();
StringBuilder paramName = new();
for (int i = 0; i < sql.Length; i++)
{
// Toggle inString flag when we encounter a single quote
if (sql[i] == '\'')
{
inString = !inString;
}
// If we are not in a string and we find a colon that could be a parameter start
if (!inString && sql[i] == pattern && i + 1 < sql.Length)
{
// Skip any whitespace after the colon
int j = i + 1;
while (j < sql.Length && char.IsWhiteSpace(sql[j]))
{
j++;
}
// Now j points to the first non-whitespace character after the colon
if (j < sql.Length && (char.IsLetter(sql[j]) || char.IsDigit(sql[j]) || sql[j] == '_'))
{
paramName.Clear();
// Start from the first valid character of the parameter name
i = j;
// Keep appending the characters to paramName until we hit a non-valid character
while (i < sql.Length && (char.IsLetterOrDigit(sql[i]) || sql[i] == '_'))
{
paramName.Append(sql[i]);
i++;
}
// Trim the collected parameter name and add to the list if it's not empty
string trimmedParamName = paramName.ToString().Trim();
if (!string.IsNullOrEmpty(trimmedParamName))
{
paramsList.Add(trimmedParamName);
}
}
}
}
return paramsList.Distinct(); // Use Distinct() to ensure unique parameter names
} public class AutoReportJobParam
{
public string LoopSQL { get; set; }
public AutoReportJobMailConfig MailConfig { get; set; }
public AutoReportJobFTPConfig FTPConfig { get; set; }
}
public class AutoReportJobMailConfig
{
public string Subject { get; set; }
public string Body { get; set; }
public string To { get; set; }
public string CC { get; set; }
public bool IsBodyHtml { get; set; }
public List<AutoReportJobMailAttachments> AttachmentsConfig { get; set; }
}
public class AutoReportJobMailAttachments
{
public string FileName { get; set; }
public string SQL { get; set; }
public EAutoReportJobMailAttachmentsFileType FileType { get; set; }
}
public enum EAutoReportJobMailAttachmentsFileType
{
xlsx,
csv
}
public class AutoReportJobFTPConfig
{
public string Host { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public int Port { get; set; }
public string RemotePath { get; set; }
}邮件服务
博主这里为了方便客户自行配型将发件配置集成在数据库了，不需要可以使用配置文件静态读取
需要注意的是寻找发件邮箱算法，如不适用可以自行更改
找到可发件邮箱（当前时间-上一次发送时间）>= 发送间隔
有可用的发件邮箱时：使用距离当前时间最久的邮箱发送
无可用发件邮箱时：找到间隔距离当前时间最近的一个邮箱，等待剩余等待间隔后发送
[AllowAnonymous]
[ApiDescriptionSettings("NMCSoftAdmin", Tag = "邮件服务", IgnoreApi = false)]
public class MailService : ISingleton, IDynamicApiController
{
private readonly ISqlSugarClient _db;
private readonly ILogger _logger;
private readonly IBaseService<SysMailSenderCfg> _sysMailSenderCfg;
private readonly object _lock = new object();
private static readonly SemaphoreSlim Semaphore = new SemaphoreSlim(1, 1);
public MailService(ISqlSugarClient db,
ILogger logger,
IBaseService<SysMailSenderCfg> sysMailSenderCfg)
{
_db = db;
_logger = logger;
_sysMailSenderCfg = sysMailSenderCfg;
var cfgCnt = _sysMailSenderCfg.Query(t => t.Enable == 1).Count();
if (cfgCnt == 0) throw Oops.Oh("未找到发件邮箱配置，请检查是否维护！");
}
/// <summary>
/// 发送邮件
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task Send(MailSendRequest request)
{
await Semaphore.WaitAsync(); // 等待获取锁
try
{
var mailSenderCfgs = _sysMailSenderCfg.Query(t => t.Enable == 1).ToList();
if (mailSenderCfgs.Count == 0)
{
throw new Exception("没有可用的发件邮箱。");
}
var currentTime = DateTime.Now;
// 选择满足发送条件的发件邮箱
var availableSenderCfgs = mailSenderCfgs
.Where(cfg => CanSendMail(cfg, currentTime))
.ToList();
if (availableSenderCfgs.Any())
{
// 如果有可以立即发送的邮箱，选择距离当前时间最久的
var availableSenderCfg = availableSenderCfgs
.OrderByDescending(cfg => (currentTime - cfg.LastTime.GetValueOrDefault(DateTime.MinValue)).TotalMilliseconds)
.First();
await Send(request, availableSenderCfg);
}
else
{
// 如果没有满足条件的发件邮箱，选择间隔最近的一个发件邮箱，等待其达到发送间隔
var nextSenderCfg = mailSenderCfgs
.Select(cfg =>
{
var nextAvailableTime = cfg.LastTime.GetValueOrDefault(DateTime.MinValue).AddMilliseconds(cfg.Interval);
var timeToWait = (nextAvailableTime - currentTime).TotalMilliseconds;
return new { Config = cfg, TimeToWait = timeToWait };
})
.Where(x => x.TimeToWait > 0)
.OrderBy(x => x.TimeToWait)
.FirstOrDefault();
await Task.Delay((int)nextSenderCfg.TimeToWait);
await Send(request, nextSenderCfg.Config);
}
}
catch (Exception ex)
{
_logger.Error($"邮件发送异常->{ex.Message}");
}
finally
{
Semaphore.Release(); // 释放锁
}
}
private async Task Send(MailSendRequest request, SysMailSenderCfg mailSenderCfg)
{
var mailMessage = new MimeMessage();
// 发件服务器
mailMessage.From.Add(MailboxAddress.Parse(mailSenderCfg.Username));
// 主题
mailMessage.Subject = request.Subject;
// 收件人
foreach (var toItem in request.To)
{
try
{
mailMessage.To.Add(MailboxAddress.Parse(toItem));
}
catch (Exception ex)
{
_logger.Error($"收件邮箱[{toItem}]解析异常->{ex.Message}");
}
}
// 抄送人
foreach (var ccItem in request.CC)
{
try
{
mailMessage.Cc.Add(MailboxAddress.Parse(ccItem));
}
catch (Exception ex)
{
_logger.Error($"抄送邮箱[{ccItem}]解析异常->{ex.Message}");
}
}
// 构建邮件正文
var builder = new BodyBuilder { HtmlBody = request.Body };
foreach (var attachment in request.Attachments)
{
var stream = attachment.OpenReadStream();
builder.Attachments.Add(attachment.FileName, stream);
//mailMessage.Attachments.add(new Attachment(stream, attachment.FileName));
}
mailMessage.Body = builder.ToMessageBody();
// 发送邮件
using var smtpClient = new SmtpClient();
// 连接到SMTP服务器
await smtpClient.ConnectAsync(mailSenderCfg.Host, mailSenderCfg.Port, Convert.ToBoolean(mailSenderCfg.EnableSsl));
// SMTP服务器身份验证
await smtpClient.AuthenticateAsync(mailSenderCfg.Username, mailSenderCfg.Password);
// 发送邮件
await smtpClient.SendAsync(mailMessage);
// 断开SMTP服务器连接
await smtpClient.DisconnectAsync(true);
// 更新最后发送时间
mailSenderCfg.LastTime = DateTime.Now;
await _sysMailSenderCfg.UpdateAsync(mailSenderCfg);
}
private bool CanSendMail(SysMailSenderCfg mailSenderCfg, DateTime currentTime)
{
if (mailSenderCfg.LastTime is null)
{
return true;
}
var intervalTime = (currentTime - mailSenderCfg.LastTime.Value).TotalMilliseconds;
// 判断上一次发送时间和当前时间的间隔是否满足配置的发送间隔
return intervalTime >= mailSenderCfg.Interval;
}
} public class MailSendRequest
{
/// <summary>
/// 邮件主题
/// </summary>
public string Subject { get; set; }
/// <summary>
/// 正文
/// </summary>
public string Body { get; set; }
/// <summary>
/// 收件人
/// </summary>
public List<string> To { get; set; } = new();
/// <summary>
/// 抄送
/// </summary>
public List<string> CC { get; set; } = new();
/// <summary>
///
/// </summary>
public bool IsBodyHtml { get; set; } = true;
/// <summary>
/// 附件
/// </summary>
public List<IFormFile> Attachments { get; set; } = new();
}ExeJobService
[AutomaticRetry(Attempts = 0)]
public class ExeJobService : IAutoTaskJob, ITransient, IDynamicApiController
{
public ExeJobService()
{
}
public void Execute(string recurringJobId, string param)
{
var exeJobParam = JsonConvert.DeserializeObject<ExeJobParam>(param);
var startInfo = new ProcessStartInfo
{
FileName = exeJobParam.Path,
Arguments = exeJobParam.Arguments,
UseShellExecute = false,
CreateNoWindow = true
};
using var process = Process.Start(startInfo);
if (process is null)
throw new Exception("start process error");
}
} public class ExeJobParam
{
public string Path { get; set; }
public string Arguments { get; set; }
}ScriptJobService
[AutomaticRetry(Attempts = 0)]
public class ScriptJobService : IAutoTaskJob, ITransient, IDynamicApiController
{
public ScriptJobService()
{
}
public void Execute(string recurringJobId, string param)
{
var scriptJobParam = JsonConvert.DeserializeObject<ExeJobParam>(param);
// 文件名
var fileName = Path.GetFileName(scriptJobParam.Path);
// 扩展名
var ext = Path.GetExtension(scriptJobParam.Path);
var executer = string.Empty;
var arguments = string.Empty;
switch (ext.ToLower())
{
case ".bat":
{
executer = "cmd.exe";
arguments = $"/c \"{scriptJobParam.Path}\" {scriptJobParam.Arguments}";
}
break;
case ".py":
{
executer = "python";
arguments = $" {scriptJobParam.Path}\" {scriptJobParam.Arguments}";
}
break;
case ".js":
{
executer = "node";
arguments = $" {scriptJobParam.Path}\" {scriptJobParam.Arguments}";
}
break;
default:
break;
}
ExecuteProcessAsync(executer, arguments);
}
private void ExecuteProcessAsync(string executer, string arguments)
{
var processStartInfo = new ProcessStartInfo
{
FileName = executer,
Arguments = arguments,
UseShellExecute = false,
CreateNoWindow = false,
WorkingDirectory = Path.GetDirectoryName(arguments)
};
using var process = new Process { StartInfo = processStartInfo };
process.Start();
}
} public class ScriptJobParam
{
public string Path { get; set; }
public string Param { get; set; }
}
扫一扫
1954
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
