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();
}
}
public void ExecToDoOne()
{
//治具逾期提醒By'P'
using (var context = new SPPContext())
{
var sql = GetBatchSql();
//获取Maintenance_Type = 'P'的超期未保养治具列表
var list = context.Database.SqlQuery <FixtureBatchVM>(sql).ToList();
List <int> idList = new List <int>();
foreach (var item in list)
{
var dateStr = item.Last_Execution_Date.Value.ToString("yyyyMMdd");
var uidList = context.Fixture_Maintenance_Record.Where(m => m.Fixture_Maintenance_Profile_UID == item.Fixture_Maintenance_Profile_UID &&
m.Maintenance_Record_NO.Contains(dateStr) && m.Maintenance_Person_Number == null).Select(m => m.Fixture_M_UID).ToList();
idList.AddRange(uidList);
}
List <Fixture_Resume> ResumeList = new List <Fixture_Resume>();
var Fixture_M_List = context.Fixture_M.Where(m => idList.Contains(m.Fixture_M_UID)).ToList();
int i = 1;
foreach (var item in Fixture_M_List)
{
item.Status = StructConstants.FixtureStatus.StatusSix;
item.Modified_UID = ConstConstants.AdminUID;
item.Modified_Date = DateTime.Now;
var seq = i.ToString().PadLeft(4, '0');
var seqNo = string.Format("OP_{0}_{1}", DateTime.Now.ToString("yyyyMMdd"), seq);
ResumeList.Add(new Fixture_Resume {
Fixture_M_UID = item.Fixture_M_UID,
Data_Source = "6",
Resume_Date = item.Modified_Date,
Source_UID = item.Fixture_M_UID,
Source_NO = seqNo,
Resume_Notes = "週期保養逾時",
Modified_UID = ConstConstants.AdminUID,
Modified_Date = item.Modified_Date
});
i++;
}
context.Fixture_Resume.AddRange(ResumeList);
context.SaveChanges();
}
//治具逾期保养By'D'
using (var context = new SPPContext())
{
var sql = GetBatchSqlByD();
//获取Maintenance_Type = 'D'的超期未保养治具列表
var list = context.Database.SqlQuery <FixtureBatchVM>(sql).ToList();
List <int> idList = new List <int>();
foreach (var item in list)
{
var dateStr = item.Last_Execution_Date.Value.ToString("yyyyMMdd");
var uidList = context.Fixture_Maintenance_Record.Where(m => m.Fixture_Maintenance_Profile_UID == item.Fixture_Maintenance_Profile_UID &&
m.Maintenance_Record_NO.Contains(dateStr) && m.Maintenance_Person_Number == null).Select(m => m.Fixture_M_UID).ToList();
idList.AddRange(uidList);
}
List <Fixture_Resume> ResumeList = new List <Fixture_Resume>();
var Fixture_M_List = context.Fixture_M.Where(m => idList.Contains(m.Fixture_M_UID)).ToList();
int i = 1;
foreach (var item in Fixture_M_List)
{
item.Status = StructConstants.FixtureStatus.StatusSix;
item.Modified_UID = ConstConstants.AdminUID;
item.Modified_Date = DateTime.Now;
var seq = i.ToString().PadLeft(4, '0');
var seqNo = string.Format("OD_{0}_{1}", DateTime.Now.ToString("yyyyMMdd"), seq);
ResumeList.Add(new Fixture_Resume
{
Fixture_M_UID = item.Fixture_M_UID,
Data_Source = "6",
Resume_Date = item.Modified_Date,
Source_UID = item.Fixture_M_UID,
Source_NO = seqNo,
Resume_Notes = "週期保養逾時",
Modified_UID = ConstConstants.AdminUID,
Modified_Date = item.Modified_Date
});
i++;
}
context.Fixture_Resume.AddRange(ResumeList);
context.SaveChanges();
}
}
//查询邮件发送列表准备发送邮件
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);
}
}
}