public void InsertBatchLog(Batch_Log newLog)
{
using (var context = new SPPContext())
{
context.Batch_Log.Add(newLog);
context.SaveChanges();
}
}
public void InsertLogInfo(string flag, bool sendFlag, string errorInfo)
{
using (var context = new SPPContext())
{
Batch_Log newLog = new Batch_Log();
newLog.Batch_Date = DateTime.Now;
if (sendFlag)
{
SetSuccessItem(flag, newLog);
}
else
{
SetFailedItem(flag, newLog, errorInfo);
}
context.Batch_Log.Add(newLog);
context.SaveChanges();
}
}
private void SetFailedItem(string flag, Batch_Log newLog, string errorInfo)
{
switch (flag)
{
case ConstConstants.FixtureMaintenanceBatch:
newLog.Batch_Name = ConstConstants.FixtureMaintenanceBatch;
newLog.Batch_Status = false;
newLog.Batch_Desc = errorInfo;
break;
case ConstConstants.FixtureNotMaintenanceBatch: //邮件发送成功文本跟失败文本不一样
newLog.Batch_Name = ConstConstants.FixtureNotMaintenanceBatch;
newLog.Batch_Status = false;
newLog.Batch_Desc = errorInfo;
break;
case ConstConstants.FixtureMaintenance: //邮件发送成功文本跟失败文本不一样
newLog.Batch_Name = ConstConstants.FixtureMaintenance;
newLog.Batch_Status = true;
newLog.Batch_Desc = errorInfo;
break;
}
}
private void SetSuccessItem(string flag, Batch_Log newLog)
{
switch (flag)
{
case ConstConstants.FixtureMaintenanceBatch:
newLog.Batch_Name = ConstConstants.FixtureMaintenanceBatch;
newLog.Batch_Status = true;
newLog.Batch_Desc = ConstConstants.FixtureMaintenanceBatch_Success;
break;
case ConstConstants.FixtureNotMaintenanceBatch: //邮件发送成功文本跟失败文本不一样
newLog.Batch_Name = ConstConstants.FixtureNotMaintenanceBatch;
newLog.Batch_Status = true;
newLog.Batch_Desc = ConstConstants.FixtureNotMaintenanceBatch_Success;
break;
case ConstConstants.FixtureMaintenance: //邮件发送成功文本跟失败文本不一样
newLog.Batch_Name = ConstConstants.FixtureMaintenance;
newLog.Batch_Status = true;
newLog.Batch_Desc = ConstConstants.FixtureMaintenance_Success;
break;
}
}
//查询邮件发送列表准备发送邮件
public void ExecSendEmail()
{
var Excetion_Email_UID = Convert.ToInt32(ConfigurationManager.AppSettings["Excetion_Email_UID"]);
var functionName = StructConstants.BatchModuleName.EmailFunctionName;
List <BatchExecVM> matchList = new List <BatchExecVM>();
using (var context = new SPPContext())
{
try
{
string strSql = @"SELECT B.*,C.Function_Name FROM dbo.System_Module A
JOIN dbo.System_Schedule B ON B.System_Module_UID = A.System_Module_UID
JOIN dbo.System_Function C ON B.Function_UID = C.Function_UID
WHERE A.Is_Enable = 1 AND B.Is_Enable = 1 AND C.Function_Name = '{0}'";
strSql = string.Format(strSql, functionName);
var list = context.Database.SqlQuery <BatchExecVM>(strSql).ToList();
#region 第一步：查询System_Schedule将时间相匹配的数据进行更新
StringBuilder sb = new StringBuilder();
var nowDate = DateTime.Now;
DateTime? nextDate = null;
foreach (var item in list)
{
Excetion_Email_UID = item.System_Schedule_UID;
//Next_Execution_Date若为当前时间，则执行
if (
item.Next_Execution_Date.Year == nowDate.Year &&
item.Next_Execution_Date.Month == nowDate.Month &&
item.Next_Execution_Date.Day == nowDate.Day &&
item.Next_Execution_Date.Hour == nowDate.Hour &&
item.Next_Execution_Date.Minute == nowDate.Minute
)
{
//var strTimeList = item.Exec_Moment.Split(',').ToList();
//将list<string>转换为list<int>
//var intIdList = strTimeList.Select<string, int>(x => Convert.ToInt32(x)).ToList();
matchList.Add(item);
switch (item.Cycle_Unit)
{
case "H": //按小时
nextDate = GetHourTime(item, nowDate);
break;
case "W": //按周
nextDate = GetWeekDay(item, nowDate);
break;
case "M": //按月
nextDate = GetMonthDay(item, nowDate);
break;
}
//更新下次执行时间Next_Execution_Date
var strUpdate = @"UPDATE dbo.System_Schedule SET Last_Execution_Date = GETDATE(),
Next_Execution_Date = '{1}',
Modified_Date = GETDATE(),
Modified_UID = 99999
WHERE System_Schedule_UID = {0}; ";
strUpdate = string.Format(strUpdate, item.System_Schedule_UID, nextDate);
sb.AppendLine(strUpdate);
}
}
if (sb.Length > 0)
{
context.Database.ExecuteSqlCommand(sb.ToString());
}
#endregion
#region 第二步：发送邮件通知
//发送三天之内的邮件
//ISNULL(Reservation_Date,Modified_Date),GETDATE()) <= 3 防止预订的时间到了程序还没跑到
var strExecSql = @"SELECT * FROM dbo.System_Email_M WHERE (Is_Send = 0 OR Is_Send IS NULL)
AND (DATEDIFF(DAY,ISNULL(Reservation_Date,Modified_Date),GETDATE()) <= 3)
AND GETDATE() >= ISNULL(Reservation_Date,Modified_Date) ";
//上面的查询语句条件不能加System_Schedule_UID，因为邮件执行的排程外键不是发邮件的外键
var vmList = context.Database.SqlQuery <BatchMailVM>(strExecSql).ToList();
if (vmList.Count() > 0)
{
foreach (var item in vmList)
{
var IsSuccess = SendMail(item);
var entity = context.System_Email_M.Find(item.System_Email_M_UID);
if (IsSuccess)
{
entity.Is_Send = true;
entity.Send_Time = nowDate;
//执行完毕后写入日志
Batch_Log newLog = new Batch_Log
{
System_Schedule_UID = item.System_Schedule_UID,
Batch_Name = StructConstants.BatchLog.Email_Module_Success,
Batch_Status = true,
Batch_Desc = StructConstants.BatchLog.Email_Module_Success,
Batch_Date = DateTime.Now
};
InsertBatchLog(newLog);
}
}
context.SaveChanges();
}
#endregion
}
catch (Exception ex)
{
InsertExceptionBatchLog(Excetion_Email_UID, ex.Message);
}
}
}
public void InsertExceptionBatchLog(int Excetion_Email_UID, string errorInfo)
{
using (var context = new SPPContext())
{
//发生异常后，需要发送邮件通知对应的系统人员或角色人员
var excetionSql = @"SELECT A.System_PIC_UIDs,B.Users_PIC_UIDs,B.Role_UIDs,C.Function_Name FROM dbo.System_Module A
JOIN dbo.System_Schedule B ON B.System_Module_UID = A.System_Module_UID
JOIN dbo.System_Function C ON B.Function_UID = C.Function_UID
WHERE B.System_Schedule_UID = {0}";
excetionSql = string.Format(excetionSql, Excetion_Email_UID);
var item = context.Database.SqlQuery <BatchExecVM>(excetionSql).First();
List <int> AccountUIDList = new List <int>();
var sysUIDList = item.System_PIC_UIDs.Split(',').ToList();
var intSysUIdList = sysUIDList.Select <string, int>(x => Convert.ToInt32(x)).ToList();
AccountUIDList.AddRange(intSysUIdList);
if (!string.IsNullOrEmpty(item.Users_PIC_UIDs))
{
var userUIDList = item.Users_PIC_UIDs.Split(',').ToList();
var intUserUIdList = userUIDList.Select <string, int>(x => Convert.ToInt32(x)).ToList();
AccountUIDList.AddRange(intUserUIdList);
}
if (!string.IsNullOrEmpty(item.Role_UIDs))
{
var roleUIDList = item.Role_UIDs.Split(',').ToList();
var intRoleUIDList = roleUIDList.Select <string, int>(x => Convert.ToInt32(x)).ToList();
var roleUserUIDList = context.System_User_Role.Where(m => intRoleUIDList.Contains(m.Role_UID)).Select(m => m.Account_UID).ToList();
AccountUIDList.AddRange(roleUserUIDList);
}
AccountUIDList = AccountUIDList.Distinct().ToList();
//var emailList = context.System_Users.Where(m => !string.IsNullOrEmpty(m.Email) && AccountUIDList.Contains(m.Account_UID)).Select(m => m.Email).ToList();
//System_Email_M emailItem = new System_Email_M();
//emailItem.System_Schedule_UID = Excetion_Email_UID;
//emailItem.Subject = StructConstants.BatchLog.Email_Module_Failed;
//emailItem.Body = item.Function_Name + "出现错误，请联系系统管理员";
//emailItem.Email_From = StructConstants.Email_From.PIS_Email_From;
//emailItem.Email_To = string.Join(",", emailList);
//emailItem.Email_To_UIDs = string.Join(",", AccountUIDList);
//emailItem.Is_Send = false;
//emailItem.Email_Type = 1;
//emailItem.Modified_UID = ConstConstants.AdminUID;
//emailItem.Modified_Date = DateTime.Now;
//context.System_Email_M.Add(emailItem);
//context.SaveChanges();
Batch_Log newLog = new Batch_Log
{
System_Schedule_UID = Excetion_Email_UID,
Batch_Name = item.Function_Name + "执行失败",
Batch_Status = false,
Batch_Desc = errorInfo,
Batch_Date = DateTime.Now
};
InsertBatchLog(newLog);
}
}
public List <BatchExecVM> ExecBatch()
{
var Excetion_Email_UID = Convert.ToInt32(ConfigurationManager.AppSettings["Excetion_Email_UID"]);
var functionName = StructConstants.BatchModuleName.EmailFunctionName;
List <BatchExecVM> matchList = new List <BatchExecVM>();
using (var context = new SPPContext())
{
try
{
string strSql = @"SELECT B.*,C.Function_Name FROM dbo.System_Module A
JOIN dbo.System_Schedule B ON B.System_Module_UID = A.System_Module_UID
JOIN dbo.System_Function C ON B.Function_UID = C.Function_UID
WHERE A.Is_Enable = 1 AND B.Is_Enable = 1 AND C.Function_Name != '{0}'";
strSql = string.Format(strSql, functionName);
var list = context.Database.SqlQuery <BatchExecVM>(strSql).ToList();
//进行时间点对比
StringBuilder sb = new StringBuilder();
var nowDate = DateTime.Now;
DateTime? nextDate = null;
foreach (var item in list)
{
Excetion_Email_UID = item.System_Schedule_UID;
//年月日时分对比,因为WindowsService间隔是每分钟一次，所以不会有误差
if (
item.Next_Execution_Date.Year == nowDate.Year &&
item.Next_Execution_Date.Month == nowDate.Month &&
item.Next_Execution_Date.Day == nowDate.Day &&
item.Next_Execution_Date.Hour == nowDate.Hour &&
item.Next_Execution_Date.Minute == nowDate.Minute
)
{
matchList.Add(item);
if (item.Exec_Moment.ToUpper() == "Month_End".ToUpper())
{
var execTimeList = item.Exec_Time.Split(',').ToList();
var nowHour = nowDate.Hour;
var nowMinute = nowDate.Minute;
for (int i = 0; i < execTimeList.Count(); i++)
{
var timeList = execTimeList[i].Split(':').ToList();
var execTimeHour = Convert.ToInt32(timeList[0]);
var execTimeMinute = Convert.ToInt32(timeList[1]);
//如果当前的时间点不是最后执行的时间点
if (nowHour == execTimeHour && nowMinute == execTimeMinute && (i != execTimeList.Count() - 1))
{
var nextTimeList = execTimeList[i + 1].Split(':').ToList();
var nextExecTimeHour = Convert.ToInt32(nextTimeList[0]);
var nextExecTimeMinute = Convert.ToInt32(nextTimeList[1]);
nextDate = item.Next_Execution_Date.Date.AddHours(nextExecTimeHour).AddMinutes(nextExecTimeMinute);
break;
}
//如果当前的时间点是最后执行的时间点
if (nowHour == execTimeHour && nowMinute == execTimeMinute && (i == execTimeList.Count() - 1))
{
var firstTimeList = execTimeList[0].Split(':').ToList();
var firstExecTimeHour = Convert.ToInt32(firstTimeList[0]);
var firstExecTimeMinute = Convert.ToInt32(firstTimeList[1]);
//下个月的第一天
nextDate = item.Next_Execution_Date.AddDays(1);
//下个月的最后一天
nextDate = nextDate.Value.Date.AddDays(1 - nextDate.Value.Day).AddMonths(1).AddDays(-1).AddHours(firstExecTimeHour).AddMinutes(firstExecTimeMinute);
break;
}
}
//下个月的第一天
//nextDate = item.Next_Execution_Date.AddDays(1);
//下个月的最后一天
//nextDate = nextDate.Value.AddDays(1 - nextDate.Value.Day).AddMonths(1).AddDays(-1);
}
else
{
//更新System_Schedule表的数据
switch (item.Cycle_Unit)
{
case "H": //按小时
nextDate = emailRepository.GetHourTime(item, nowDate);
break;
case "W": //按周
nextDate = emailRepository.GetWeekDay(item, nowDate);
break;
case "M": //按月
nextDate = emailRepository.GetMonthDay(item, nowDate);
break;
}
}
//更新下次执行日期Next_Execution_Date
var strUpdate = @"UPDATE dbo.System_Schedule SET Last_Execution_Date = GETDATE(),
Next_Execution_Date = '{1}',
Modified_Date = GETDATE(),
Modified_UID = 99999
WHERE System_Schedule_UID = {0}; ";
strUpdate = string.Format(strUpdate, item.System_Schedule_UID, nextDate);
sb.AppendLine(strUpdate);
}
}
if (sb.Length > 0)
{
context.Database.ExecuteSqlCommand(sb.ToString());
}
foreach (var item in matchList)
{
//执行完毕后写入日志
Batch_Log newLog = new Batch_Log
{
System_Schedule_UID = item.System_Schedule_UID,
Batch_Name = item.Function_Name + "执行成功",
Batch_Status = true,
Batch_Desc = item.Function_Name + "执行成功",
Batch_Date = DateTime.Now
};
emailRepository.InsertBatchLog(newLog);
}
}
catch (Exception ex)
{
emailRepository.InsertExceptionBatchLog(Excetion_Email_UID, ex.Message);
}
return(matchList);
}
}
