/// <summary>
/// 执行定时任务
/// </summary>
/// <param name="task">定时任务</param>
/// <param name="executeDate">执行时间</param>
public void Execute(ScheduledTaskModel task, DateTime executeDate)
{
if (task == null)
{
Log.Error("无效的定时任务。");
throw new InvalidOperationException("无效的定时任务。");
}
using (var dbContext = new MissionskyOAEntities())
{
var sql = @"IF EXISTS(SELECT 1 FROM tempdb..SYSOBJECTS WHERE Id = OBJECT_ID('tempdb..##TUser'))
DROP TABLE ##TUser
SELECT DISTINCT O.UserId INTO ##TUser FROM [Order] O
INNER JOIN OrderDet D ON O.Id = D.OrderId
--INNER JOIN [User] U ON O.UserId = U.Id AND ISNULL(U.TodayStatus, 1) = 1
WHERE DATEDIFF(DAY, CAST(StartDate AS NVARCHAR) + ' ' + CAST(StartTime AS NVARCHAR), GETDATE()) >= 0
AND DATEDIFF(DAY, CAST(EndDate AS NVARCHAR) + ' ' + CAST(EndTime AS NVARCHAR), GETDATE()) <= 0 --今天请假
AND ISNULL(O.OrderType, 0) IN (1, 2, 3, 5, 6, 7, 8) --年假，病假，调休，婚假，产假，丧假，事假
SELECT ',' + EnglishName FROM [User] WHERE Id IN (SELECT UserId FROM ##TUser) AND ISNULL(TodayStatus, 1) != 3 FOR XML PATH (''); --需要更新今日状态
UPDATE [User] SET TodayStatus = 3 WHERE Id IN (SELECT UserId FROM ##TUser) AND ISNULL(TodayStatus, 1) != 3; --更新今日状态为请假状态
UPDATE [User] SET TodayStatus = 1 WHERE Id NOT IN (SELECT UserId FROM ##TUser) AND ISNULL(TodayStatus, 1) = 3; --请假状态改为正常状态
IF EXISTS(SELECT 1 FROM tempdb..SYSOBJECTS WHERE Id = OBJECT_ID('tempdb..##TUser'))
DROP TABLE ##TUser" ;
//执行定时间任务
var content = string.Empty; //详细内容
var data = dbContext.Database.SqlQuery <string>(sql);
if (data != null)
{
content = data.FirstOrDefault();
if (!string.IsNullOrEmpty(content))
{
content = content.Substring(1);
}
}
//更新定时间任务
ScheduledTaskService.UpdateTask(dbContext, task.Id, executeDate, content);
//更新数据库
dbContext.SaveChanges();
//Log.Info(string.Format("定时任务执行成功: {0}。", task.Name));
}
}
/// <summary>
/// 执行定时任务
/// </summary>
/// <param name="task">定时任务</param>
/// <param name="executeDate">执行时间</param>
public void Execute(ScheduledTaskModel task, DateTime executeDate)
{
if (task == null)
{
Log.Error("无效的定时任务。");
throw new InvalidOperationException("无效的定时任务。");
}
using (var dbContext = new MissionskyOAEntities())
{
var sql = @"SELECT * FROM MeetingCalendar
WHERE DATEDIFF(DAY, CAST(StartDate AS NVARCHAR), GETDATE()) = 0 OR DATEDIFF(DAY, CAST(EndDate AS NVARCHAR), GETDATE()) = 0
ORDER BY StartDate DESC, StartTime DESC;";
//当天开始或结束的会议
var entities = dbContext.MeetingCalendars.SqlQuery(sql).ToList();
//定时任务详情
var content = string.Empty;
//当天开始或结束的会议，发送消息通知
entities.ForEach(meeting =>
{
if (CanNotify(meeting.ToModel(), MeetingNotificationType.StartNotification)) //会议开始通知
{
Notify(dbContext, meeting.ToModel(), MeetingNotificationType.StartNotification);
content = string.Format("{0},{1}", content, meeting.Title);
}
if (CanNotify(meeting.ToModel(), MeetingNotificationType.EndNotification)) //会议结束通知
{
Notify(dbContext, meeting.ToModel(), MeetingNotificationType.EndNotification);
content = string.Format("{0},{1}", content, meeting.Title);
}
});
//更新定时间任务
content = string.IsNullOrEmpty(content) ? content : content.Substring(1);
ScheduledTaskService.UpdateTask(dbContext, task.Id, executeDate, content);
//更新数据库
dbContext.SaveChanges();
//移出过期的通知记录
RemoveExpiredHistory();
//Log.Info(string.Format("定时任务执行成功: {0}。", task.Name));
}
}
/// <summary>
/// 执行定时任务
/// </summary>
/// <param name="task">定时任务</param>
/// <param name="executeDate">执行时间</param>
public void Execute(ScheduledTaskModel task, DateTime executeDate)
{
if (task == null)
{
Log.Error("无效的定时任务。");
throw new InvalidOperationException("无效的定时任务。");
}
using (var dbContext = new MissionskyOAEntities())
{
DateTime summaryDate = DateTime.Now.Date; //统计日期
//统计当前日期后3个月的考勤详细
do
{
var parameters = new []
{
new SqlParameter()
{
DbType = System.Data.DbType.Date,
ParameterName = "@date",
Value = summaryDate
}
};
dbContext.Database.ExecuteSqlCommand("exec p_summaryAttendanceDetail @date", parameters);
summaryDate = summaryDate.AddMonths(-1); //统计成功
} while (((DateTime.Now.Year - summaryDate.Year) * 12 + DateTime.Now.Month) - summaryDate.Month < 3);
//更新定时间任务
ScheduledTaskService.UpdateTask(dbContext, task.Id, executeDate, string.Format("统计{0}-{1}及前2个月的考勤详细成功。", DateTime.Now.Year, DateTime.Now.Month));
//更新数据库
dbContext.SaveChanges();
//Log.Info(string.Format("定时任务执行成功: {0}。", task.Name));
}
}
public void UpdateTask([FromBody] ScheduledTaskDTO task)
{
var user = userService.GetOrCreateUser((ClaimsIdentity)User.Identity);
scheduledTaskService.UpdateTask(user, task);
}
/// <summary>
/// 执行定时任务
/// </summary>
/// <param name="task">定时任务</param>
/// <param name="executeDate">执行时间</param>
public void Execute(ScheduledTaskModel task, DateTime executeDate)
{
if (task == null)
{
Log.Error("无效的定时任务。");
throw new InvalidOperationException("无效的定时任务。");
}
using (var dbContext = new MissionskyOAEntities())
{
#region 推送消息
var sql = @"SELECT * FROM ExpenseAuditHistory
WHERE [Status]= {0} AND [CurrentAudit]={1}
;" ;
//推送消息
var notification = new NotificationModel()
{
CreatedUserId = 0,
//MessageType = NotificationType.PushMessage,
MessageType = NotificationType.Email,
BusinessType = BusinessType.Approving,
Title = "及时提交报销申请资料",
MessagePrams = "test",
Scope = NotificationScope.User,
CreatedTime = DateTime.Now
};
var financialUserEntity = dbContext.Users.FirstOrDefault(it => it.Email != null && it.Email.ToLower().Contains(Global.FinancialEmail));
var entities =
dbContext.ExpenseAuditHistories.SqlQuery(string.Format(sql, (int)OrderStatus.Approved, financialUserEntity.Id)).ToList(); //当前需要提交报销纸质资料的的报销
var users = dbContext.Users.ToList(); //所有用户
var content = string.Empty; //详细内容
entities.ForEach(expenseItem =>
{
var user = users.FirstOrDefault(it => it.Id == expenseItem.ExpenseMain.ApplyUserId);
if (user != null)
{
notification.Target = user.Email;
notification.TargetUserIds = new List <int> {
user.Id
};
notification.MessageContent = "您的报销已经成功审批，请及时到财务提交报销申请纸质资料。";
NotificationService.Add(notification, Global.IsProduction); //消息推送
content = string.Format("{0},{1},报销纸质资料)", content, user.EnglishName);
}
});
if (!string.IsNullOrEmpty(content)) //详细内容
{
content = content.Substring(1);
}
#endregion
//更新定时间任务
ScheduledTaskService.UpdateTask(dbContext, task.Id, executeDate, content);
//更新数据库
dbContext.SaveChanges();
//Log.Info(string.Format("定时任务执行成功: {0}。", task.Name));
}
}
/// <summary>
/// 执行定时任务
/// </summary>
/// <param name="task">定时任务</param>
/// <param name="executeDate">执行时间</param>
public void Execute(ScheduledTaskModel task, DateTime executeDate)
{
if (task == null)
{
Log.Error("无效的定时任务。");
throw new InvalidOperationException("无效的定时任务。");
}
using (var dbContext = new MissionskyOAEntities())
{
#region 推送消息
var sql = @"SELECT * FROM BookBorrow
WHERE ISNULL([Status], 0) = {0} --借阅中
AND DATEDIFF(DAY, ReturnDate, GETDATE()) >= 0 --已到预计归还日期
;" ;
//推送消息
var notification = new NotificationModel()
{
CreatedUserId = 0,
//MessageType = NotificationType.PushMessage,
MessageType = NotificationType.Email,
BusinessType = BusinessType.Approving,
Title = "用户归还图书通知",
MessagePrams = "test",
Scope = NotificationScope.User,
CreatedTime = DateTime.Now
};
var entities =
dbContext.BookBorrows.SqlQuery(string.Format(sql, (int)UserBorrowStatus.Borrowing)).ToList(); //当前需要归还的借阅图书
var users = dbContext.Users.ToList(); //所有用户
var content = string.Empty; //详细内容
entities.ForEach(borrow =>
{
var user = users.FirstOrDefault(it => it.Id == borrow.UserId);
var book = dbContext.Books.FirstOrDefault(it => it.Id == borrow.BookId);
if (user != null && book != null)
{
notification.Target = user.Email;
notification.TargetUserIds = new List <int> {
user.Id
};
notification.MessageContent = string.Format("请及时归还您借阅的图书《{0}》。", book.Name);
NotificationService.Add(notification, Global.IsProduction); //消息推送
content = string.Format("{0},{1}({2})", content, user.EnglishName, book.Name);
}
});
if (!string.IsNullOrEmpty(content)) //详细内容
{
content = content.Substring(1);
}
#endregion
//更新定时间任务
ScheduledTaskService.UpdateTask(dbContext, task.Id, executeDate, content);
//更新数据库
dbContext.SaveChanges();
//Log.Info(string.Format("定时任务执行成功: {0}。", task.Name));
}
}
/// <summary>
/// 执行定时任务
/// </summary>
/// <param name="task">定时任务</param>
/// <param name="executeDate">执行时间</param>
public void Execute(ScheduledTaskModel task, DateTime executeDate)
{
if (task == null)
{
Log.Error("无效的定时任务。");
throw new InvalidOperationException("无效的定时任务。");
}
using (var dbContext = new MissionskyOAEntities())
{
#region 推送消息
var sql = @"SELECT * FROM BookBorrow
WHERE ISNULL([Status], 0) = {0} --借阅中
AND DATEDIFF(DAY, ReturnDate, GETDATE()) >= 0 --已到预计归还日期
;" ;
//推送消息
var notification = new NotificationModel()
{
CreatedUserId = 0,
MessageType = NotificationType.PushMessage,
BusinessType = BusinessType.Approving,
Title = "Missionsky OA Notification",
MessagePrams = "test",
Scope = NotificationScope.User,
CreatedTime = DateTime.Now
};
var entities =
dbContext.BookBorrows.SqlQuery(string.Format(sql, (int)UserBorrowStatus.Borrowing)).ToList();
//当前需要归还的借阅图书
entities.ForEach(borrow =>
{
var user = dbContext.Users.FirstOrDefault(it => it.Id == borrow.UserId);
var book = dbContext.Books.FirstOrDefault(it => it.Id == borrow.BookId);
if (user != null && book != null)
{
notification.Target = user.Email;
notification.TargetUserIds = new List <int> {
user.Id
};
notification.MessageContent = string.Format("请及时归还您借阅的图书《{0}》。", book.Name);
NotificationService.Add(notification, Global.IsProduction); //消息推送
}
});
#endregion
//更新定时任务
var history = new ScheduledTaskHistoryModel()
{
TaskId = task.Id,
Result = true,
Desc = task.Name,
CreatedTime = executeDate
};
ScheduledTaskService.UpdateTask(dbContext, history);
dbContext.SaveChanges();
Log.Info(string.Format("定时任务执行成功: {0}。", task.Name));
}
}