/// <summary>
/// 批量更新Job启动状态
/// </summary>
/// <param name="Ids"></param>
/// <param name="model"></param>
/// <returns></returns>
public bool UpdateSchedulerConfigStatus(string Ids, SchedulerConfigModel model)
{
StringBuilder strSql = new StringBuilder();
DynamicParameters dp = new DynamicParameters();
strSql.Append(" UPDATE SchedulerConfig SET ");
strSql.Append(" IsEnable=@IsEnable, ");
strSql.Append(" Status=@Status, ");
strSql.Append(" LastUpdateUser=@LastUpdateUser, ");
strSql.Append(" LastUpdateTime=@LastUpdateTime ");
strSql.Append(" WHERE ID IN (" + Ids + ") ");
//禁用
if (model.IsEnable == 0)
{
strSql.Append(" AND IsEnable=1 ");
}
//启用
if (model.IsEnable == 1)
{
strSql.Append(" AND IsEnable=0 ");
}
dp.Add("@IsEnable", model.IsEnable, DbType.Int32, ParameterDirection.Input);
dp.Add("@Status", model.Status, DbType.Int32, ParameterDirection.Input);
dp.Add("@LastUpdateTime", model.LastUpdateTime, DbType.DateTime, ParameterDirection.Input);
dp.Add("@LastUpdateUser", model.LastUpdateUser, DbType.String, ParameterDirection.Input);
using (IDbConnection connection = new SqlConnection(DBConnectionStringConfig.Default.JMGGConnectionString))
{
return(connection.Execute(strSql.ToString(), dp) > 0);
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool UpdateSchedulerConfig(SchedulerConfigModel model)
{
StringBuilder strSql = new StringBuilder();
DynamicParameters dp = new DynamicParameters();
strSql.Append(" UPDATE SchedulerConfig SET ");
strSql.Append(" JobGroup=@JobGroup, ");
strSql.Append(" JobName=@JobName, ");
strSql.Append(" RequestType=@RequestType, ");
strSql.Append(" RequestUrl=@RequestUrl, ");
strSql.Append(" CronTab=@CronTab, ");
strSql.Append(" JobRemark=@JobRemark, ");
strSql.Append(" LastUpdateTime=@LastUpdateTime, ");
strSql.Append(" LastUpdateUser=@LastUpdateUser, ");
strSql.Append(" StartTime=@StartTime, ");
strSql.Append(" EndTime=@EndTime, ");
strSql.Append(" Status=@Status ");
strSql.Append(" WHERE ID=@ID ");
dp.Add("@JobGroup", model.JobGroup, DbType.String, ParameterDirection.Input);
dp.Add("@JobName", model.JobName, DbType.String, ParameterDirection.Input);
dp.Add("@RequestType", model.RequestType, DbType.Int32, ParameterDirection.Input);
dp.Add("@RequestUrl", model.RequestUrl, DbType.String, ParameterDirection.Input);
dp.Add("@CronTab", model.CronTab, DbType.String, ParameterDirection.Input);
dp.Add("@JobRemark", model.JobRemark, DbType.String, ParameterDirection.Input);
dp.Add("@LastUpdateTime", model.LastUpdateTime, DbType.DateTime, ParameterDirection.Input);
dp.Add("@LastUpdateUser", model.LastUpdateUser, DbType.String, ParameterDirection.Input);
dp.Add("@StartTime", model.StartTime, DbType.String, ParameterDirection.Input);
dp.Add("@EndTime", model.EndTime, DbType.String, ParameterDirection.Input);
dp.Add("@ID", model.ID, DbType.Int32, ParameterDirection.Input);
dp.Add("@Status", model.Status, DbType.Int32, ParameterDirection.Input);
using (IDbConnection connection = new SqlConnection(DBConnectionStringConfig.Default.JMGGConnectionString))
{
return(connection.Execute(strSql.ToString(), dp) > 0);
}
}
public static List <SchedulerConfigModel> GetSchedulerRecord(int type)
{
List <SchedulerConfigModel> list = new List <SchedulerConfigModel>();
SchedulerConfigModel model = null;
SqlConnection con = SqlHelper.GetConnection();
StringBuilder sb = new StringBuilder();
//查询所有启动的数据
if (type == (int)JobEnum.DataType.EnableData)
{
sb.Append("select * from SchedulerConfig WITH(NOLOCK) where IsEnable=1 and Status=0 ");
}
//查询所有变动数据[status:1新增 2更新 3删除]
else if (type == (int)JobEnum.DataType.UpdateData)
{
sb.Append(" select * from SchedulerConfig WITH(NOLOCK) where IsEnable=1 and Status in (1,2) ");
sb.Append(" Union all ");
sb.Append(" select * from SchedulerConfig WITH(NOLOCK) where IsEnable=0 and Status in (3) ");
}
DataSet ds = SqlHelper.ExecuteDataset(con, CommandType.Text, sb.ToString());
if (ds != null)
{
foreach (DataRow col in ds.Tables[0].Rows)
{
model = new SchedulerConfigModel();
model.ID = Convert.ToInt32(col["ID"]);
model.JobName = col["JobName"].ToString();
model.JobGroup = col["JobGroup"].ToString();
model.StartTime = Convert.ToDateTime(col["StartTime"]);
if (!string.IsNullOrEmpty(col["EndTime"].ToString()))
{
model.EndTime = Convert.ToDateTime(col["EndTime"]);
}
else
{
model.EndTime = null;
}
model.CronTab = col["CronTab"].ToString();
model.RequestUrl = col["RequestUrl"].ToString();
model.RequestType = Convert.ToInt32(col["RequestType"]);
model.Status = Convert.ToInt32(col["Status"]);
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="QueryCondition"></param>
/// <returns></returns>
public List <SchedulerConfigModel> QueryListByConditionPage(SchedulerConfigModel QueryScheduler)
{
using (IDbConnection connection = new SqlConnection(DBConnectionStringConfig.Default.JMGGConnectionString))
{
DynamicParameters dp = new DynamicParameters();
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append(" SELECT * ");
sqlQuery.Append(" FROM SchedulerConfig rclf WITH(NOLOCK) WHERE 1=1 ");
if (!string.IsNullOrEmpty(QueryScheduler.JobGroup))
{
sqlQuery.Append(" AND rclf.JobGroup=@JobGroup ");
dp.Add("@JobGroup", QueryScheduler.JobGroup, DbType.String, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(QueryScheduler.JobName))
{
sqlQuery.Append(" AND rclf.JobName=@JobName ");
dp.Add("@JobName", QueryScheduler.JobName, DbType.String, ParameterDirection.Input);
}
if (QueryScheduler.RequestType == 1 || QueryScheduler.RequestType == 2)
{
sqlQuery.Append(" AND rclf.RequestType=@RequestType ");
dp.Add("@RequestType", QueryScheduler.RequestType, DbType.Int32, ParameterDirection.Input);
}
if (QueryScheduler.IsEnable == 0 || QueryScheduler.IsEnable == 1)
{
sqlQuery.Append(" AND rclf.IsEnable=@IsEnable ");
dp.Add("@IsEnable", QueryScheduler.IsEnable, DbType.Int32, ParameterDirection.Input);
}
if (QueryScheduler.StartTime != DateTime.MinValue && QueryScheduler.StartTime != null)
{
sqlQuery.Append(" AND rclf.CreateTime>=@StartTime ");
dp.Add("@StartTime", QueryScheduler.StartTime, DbType.DateTime, ParameterDirection.Input);
}
if (QueryScheduler.EndTime != DateTime.MinValue && QueryScheduler.EndTime != null)
{
sqlQuery.Append(" AND rclf.CreateTime<=@EndTime ");
dp.Add("@EndTime", Convert.ToDateTime(Convert.ToDateTime(QueryScheduler.EndTime).ToString("yyyy-MM-dd") + " 23:59:59"), DbType.DateTime, ParameterDirection.Input);
}
sqlQuery.Append(" ORDER BY rclf.ID DESC ");
sqlQuery.Append(" OFFSET (@PageSize * (@PageIndex -1)) ROWS FETCH NEXT @PageSize ROWS ONLY ");
dp.Add("@PageSize", QueryScheduler.PageSize, DbType.Int32, ParameterDirection.Input);
dp.Add("@PageIndex", QueryScheduler.PageIndex, DbType.Int32, ParameterDirection.Input);
return(connection.Query <SchedulerConfigModel>(sqlQuery.ToString(), dp).ToList());
}
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddSchedulerConfig(SchedulerConfigModel model)
{
using (IDbConnection connection = new SqlConnection(DBConnectionStringConfig.Default.JMGGConnectionString))
{
StringBuilder sb = new StringBuilder();
sb.Append(" INSERT INTO SchedulerConfig ");
sb.Append(" (JobGroup,JobName,RequestType,RequestUrl,CronTab,JobRemark,CreateUser, ");
sb.Append(" LastUpdateUser,IsEnable,Status, StartTime, EndTime) ");
sb.Append(" VALUES ");
sb.Append(" (@JobGroup,@JobName,@RequestType,@RequestUrl,@CronTab,@JobRemark,@CreateUser,@LastUpdateUser,");
sb.Append(" @IsEnable,@Status,@StartTime,@EndTime); ");
sb.Append(" SELECT CAST(SCOPE_IDENTITY() AS INT) ");
sb.Append(" ");
model.ID = connection.Query <int>(sb.ToString(), model).Single();
return(model.ID);
}
}
/// <summary>
/// 获取总记录数
/// </summary>
/// <param name="QueryCondition"></param>
/// <returns></returns>
public int GetTotalJobCount(SchedulerConfigModel QueryScheduler)
{
using (IDbConnection connection = new SqlConnection(DBConnectionStringConfig.Default.JMGGConnectionString))
{
DynamicParameters dp = new DynamicParameters();
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append(" SELECT COUNT(1) FROM SchedulerConfig rclf WITH(NOLOCK) WHERE 1=1 ");
if (!string.IsNullOrEmpty(QueryScheduler.JobGroup))
{
sqlQuery.Append(" AND rclf.JobGroup=@JobGroup ");
dp.Add("@JobGroup", QueryScheduler.JobGroup, DbType.String, ParameterDirection.Input);
}
if (!string.IsNullOrEmpty(QueryScheduler.JobName))
{
sqlQuery.Append(" AND rclf.JobName=@JobName ");
dp.Add("@JobName", QueryScheduler.JobName, DbType.String, ParameterDirection.Input);
}
if (QueryScheduler.RequestType == 1 || QueryScheduler.RequestType == 2)
{
sqlQuery.Append(" AND rclf.RequestType=@RequestType ");
dp.Add("@RequestType", QueryScheduler.RequestType, DbType.Int32, ParameterDirection.Input);
}
if (QueryScheduler.IsEnable == 0 || QueryScheduler.IsEnable == 1)
{
sqlQuery.Append(" AND rclf.IsEnable=@IsEnable ");
dp.Add("@IsEnable", QueryScheduler.IsEnable, DbType.Int32, ParameterDirection.Input);
}
if (QueryScheduler.StartTime != DateTime.MinValue && QueryScheduler.StartTime != null)
{
sqlQuery.Append(" AND rclf.CreateTime>=@StartTime ");
dp.Add("@StartTime", QueryScheduler.StartTime, DbType.String, ParameterDirection.Input);
}
if (QueryScheduler.EndTime != DateTime.MinValue && QueryScheduler.EndTime != null)
{
sqlQuery.Append(" AND rclf.CreateTime<=@EndTime ");
dp.Add("@EndTime", Convert.ToDateTime(Convert.ToDateTime(QueryScheduler.EndTime).ToString("yyyy-MM-dd") + " 23:59:59"), DbType.String, ParameterDirection.Input);
}
return(connection.ExecuteScalar <int>(sqlQuery.ToString(), dp));
}
}
/// <summary>
/// 批量更新job启动状态
/// </summary>
/// <param name="context"></param>
public string UpdateSchedulerConfigStatus()
{
try
{
var Ids = string.IsNullOrEmpty(Request["Ids"]) ? string.Empty : Request["Ids"].ToString();
var IsEnable = string.IsNullOrEmpty(Request["IsEnable"]) ? -1 : Convert.ToInt32(Request["IsEnable"]);
var Name = string.IsNullOrEmpty(Request["Name"]) ? string.Empty : Request["Name"].ToString();
if (!string.IsNullOrEmpty(Ids) && IsEnable >= 0)
{
SchedulerConfigModel SchedulerConfigModel = new SchedulerConfigModel();
SchedulerConfigModel.LastUpdateTime = DateTime.Now;
SchedulerConfigModel.LastUpdateUser = Name;
SchedulerConfigModel.IsEnable = IsEnable;
SchedulerConfigModel.Status = IsEnable == 0 ? 3 : 1;
var flag = bll.UpdateSchedulerConfigStatus(Ids, SchedulerConfigModel);
if (flag)
{
return(JsonConvert.SerializeObject(new { returnMsg = "更新成功", status = "true" }));
}
else
{
return(JsonConvert.SerializeObject(new { returnMsg = "更新失败", status = "false" }));
}
}
else
{
return(JsonConvert.SerializeObject(new { returnMsg = "更新失败", status = "false" }));
}
}
catch (Exception ex)
{
LogWriter.error($"批量更新job启动状态异常{ex.Message + ex.StackTrace}");
return(JsonConvert.SerializeObject(new { returnMsg = "更新异常", status = "false" }));
}
}
/// <summary>
/// 执行Job方法
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
private bool ExecuteWebApi(SchedulerConfigModel list)
{
try
{
var flag = true;
SchedulerHelper scheduler = new SchedulerHelper();
SchedulerRequest request = new SchedulerRequest();
request.JobName = list.JobName;
request.JobGroup = list.JobGroup;
request.TriggerName = list.JobName + "Trigger";
request.TriggerGroupName = list.JobGroup + "Trigger";
request.CronTab = list.CronTab;
request.StartTime = list.StartTime;
if (list.EndTime != null)
{
request.EndTime = list.EndTime;
}
else
{
request.EndTime = null;
}
request.RequestType = list.RequestType;
request.RequestUrl = list.RequestUrl;
var json = JsonConvert.SerializeObject(request);
DateTimeOffset?end = null;
if (request.EndTime != null)
{
end = DateTime.SpecifyKind(Convert.ToDateTime(request.EndTime), DateTimeKind.Local);
if (request.EndTime < DateTime.Now)
{
flag = false;
}
}
if (flag)
{
IJobDetail jobDetail = JobBuilder.Create <JobMethod>()
.WithIdentity(request.JobName, request.JobGroup)
.UsingJobData("jobJson", json)
.Build();
CronTriggerImpl tigger = (CronTriggerImpl)TriggerBuilder.Create()
.WithIdentity(request.TriggerName, request.TriggerGroupName)
.WithCronSchedule(request.CronTab)
.ForJob(request.JobName, request.JobGroup)
.StartNow()
.EndAt(end)
.Build();
DateTimeOffset dt = scheduler.scheduleJob(jobDetail, tigger);
LogWriter.info("[" + (request.RequestType == (int)JobEnum.RequestType.Get ? "GET" : "POST") + "]Job-" + request.JobName + "已启动,定时运行时间为:" + Convert.ToDateTime(dt.ToString()) + ",请求地址:" + request.RequestUrl);
}
else
{
LogWriter.info("[" + (request.RequestType == (int)JobEnum.RequestType.Get ? "GET" : "POST") + "]Job-" + request.JobName + ",当前时间超出此Job设置的执行结束时间,该Job已停止执行,请求地址:" + request.RequestUrl);
}
return(true);
}
catch (Exception ex)
{
LogWriter.error("Job-" + list.JobName + "执行异常,请求地址:" + list.RequestUrl + ",异常信息:" + ex.ToString());
//插入日志
SchedulerRequest request = new SchedulerRequest();
request.JobGroup = list.JobGroup;
request.JobName = list.JobName;
request.RequestType = list.RequestType;
request.RequestUrl = list.RequestUrl;
request.ReturnMsg = "Job-Exception: " + (ex.ToString().Length > 490 ? ex.ToString().Substring(0, 490) : ex.ToString());
request.LogStatus = (int)JobEnum.LogStatus.ExceptInfo;
SchedulerConfigQuery.InsertLog(request);
//数据状态更新为0,防止重复执行
SchedulerConfigQuery.UpdateStatus(list.ID);
return(false);
}
}
/// <summary>
/// 批量更新Job启动状态
/// </summary>
/// <param name="Ids"></param>
/// <param name="model"></param>
/// <returns></returns>
public bool UpdateSchedulerConfigStatus(string Ids, SchedulerConfigModel model)
{
return schedulerDal.UpdateSchedulerConfigStatus(Ids, model);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool UpdateSchedulerConfig(SchedulerConfigModel model)
{
return schedulerDal.UpdateSchedulerConfig(model);
}
/// <summary>
/// 新增数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddSchedulerConfig(SchedulerConfigModel model)
{
return schedulerDal.AddSchedulerConfig(model);
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="QueryScheduler"></param>
/// <returns></returns>
public List<SchedulerConfigModel> QueryListByConditionPage(SchedulerConfigModel QueryScheduler)
{
return schedulerDal.QueryListByConditionPage(QueryScheduler);
}
/// <summary>
/// 获取总记录数
/// </summary>
/// <param name="QueryScheduler"></param>
/// <returns></returns>
public int GetTotalJobCount(SchedulerConfigModel QueryScheduler)
{
return schedulerDal.GetTotalJobCount(QueryScheduler);
}
private bool ExecuteWebApi(SchedulerConfigModel list)
{
try
{
var flag = true;
SchedulerHelper scheduler = new SchedulerHelper();
SchedulerRequest request = new SchedulerRequest();
request.JobName = list.JobName;
request.JobGroup = list.JobGroup;
request.TriggerName = list.JobName + "Trigger";
request.TriggerGroupName = list.JobGroup + "Trigger";
request.CronTab = list.CronTab;
request.StartTime = list.StartTime;
if (list.EndTime != null)
{
request.EndTime = list.EndTime;
}
else
{
request.EndTime = null;
}
request.RequestType = list.RequestType;
request.RequestUrl = list.RequestUrl;
var json = JsonConvert.SerializeObject(request);
DateTimeOffset?end = null;
if (request.EndTime != null)
{
end = DateTime.SpecifyKind(Convert.ToDateTime(request.EndTime), DateTimeKind.Local);
if (request.EndTime < DateTime.Now)
{
flag = false;
}
}
if (flag)
{
IJobDetail jobDetail = JobBuilder.Create <FileJob>()
.WithIdentity(request.JobName, request.JobGroup)
.UsingJobData("jobJson", json)
.Build();
CronTriggerImpl tigger = (CronTriggerImpl)TriggerBuilder.Create()
.WithIdentity(request.TriggerName, request.TriggerGroupName)
.WithCronSchedule(request.CronTab)
.ForJob(request.JobName, request.JobGroup)
.StartNow()
.EndAt(end)
.Build();
DateTimeOffset dt = scheduler.scheduleJob(jobDetail, tigger);
}
else
{
}
return(true);
}
catch (Exception ex)
{
//插入日志
SchedulerRequest request = new SchedulerRequest();
request.JobGroup = list.JobGroup;
request.JobName = list.JobName;
request.RequestType = list.RequestType;
request.RequestUrl = list.RequestUrl;
request.ReturnMsg = "Job-Exception: " + (ex.ToString().Length > 450 ? ex.ToString().Substring(0, 450) : ex.ToString());
request.LogStatus = (int)JobEnum.LogStatus.ExceptInfo;
SchedulerConfigDB.InsertLog(request);
//数据状态更新为0,防止重复执行
SchedulerConfigDB.UpdateStatus(list.ID);
return(false);
}
}
