public static void Add(SmtpQueue item)
{
string cons = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
StringBuilder query = new StringBuilder(@"INSERT INTO dbo.t_Smtp_Queue
( Title,
Status,
CreatedTime,
SendedTime,
Remark
)
VALUES ( @title,
@status,
@createdTime,
@sendedTime,
@remark
)");
using (SqlConnection conn = new SqlConnection(cons))
{
SqlCommand cmd = new SqlCommand(query.ToString(), conn);
cmd.Parameters.AddWithValue("@title", item.Title);
cmd.Parameters.AddWithValue("@status", item.Status);
cmd.Parameters.AddWithValue("@createdTime", item.CreatedTime);
cmd.Parameters.AddWithValue("@sendedTime", item.SendedTime);
cmd.Parameters.AddWithValue("@remark", item.Remark);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public static List <SmtpQueue> GetList()
{
string cons = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
List <SmtpQueue> result = new List <SmtpQueue>();
using (SqlConnection conn = new SqlConnection(cons))
{
string query = @"SELECT Id,
Title,
Status,
CreatedTime,
SendedTime,
Remark FROM t_Smtp_Queue";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SmtpQueue item = new SmtpQueue();
item.Id = Convert.ToInt32(reader["Id"]);
item.Title = Convert.ToString(reader["Title"]);
item.Status = Convert.ToInt32(reader["Status"]);
item.CreatedTime = Convert.ToDateTime(reader["CreatedTime"]);
item.SendedTime = Convert.ToDateTime(reader["SendedTime"]);
item.Remark = Convert.ToString(reader["Remark"]);
result.Add(item);
}
}
}
return(result);
}
public static SmtpQueue GetByTitle(string title)
{
string cons = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
SmtpQueue result = new SmtpQueue();
using (SqlConnection conn = new SqlConnection(cons))
{
string query = @"SELECT Id,
Title,
Status,
CreatedTime,
SendedTime,
Remark FROM t_Smtp_Queue WHERE Title=@title";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@title", title);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Id = Convert.ToInt32(reader["Id"]);
result.Title = Convert.ToString(reader["Title"]);
result.Status = Convert.ToInt32(reader["Status"]);
result.CreatedTime = Convert.ToDateTime(reader["CreatedTime"]);
result.SendedTime = Convert.ToDateTime(reader["SendedTime"]);
result.Remark = Convert.ToString(reader["Remark"]);
}
}
}
return(result);
}
public static void Edit(SmtpQueue item)
{
string cons = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
StringBuilder query = new StringBuilder(@"UPDATE dbo.t_Smtp_Queue
SET Title=@title,
Status=@status,
CreatedTime=@createdTime,
SendedTime=@sendedTime,
Remark=@remark WHERE Id=@id");
using (SqlConnection conn = new SqlConnection(cons))
{
SqlCommand cmd = new SqlCommand(query.ToString(), conn);
cmd.Parameters.AddWithValue("@title", item.Title);
cmd.Parameters.AddWithValue("@status", item.Status);
cmd.Parameters.AddWithValue("@createdTime", item.CreatedTime);
cmd.Parameters.AddWithValue("@sendedTime", item.SendedTime);
cmd.Parameters.AddWithValue("@remark", item.Remark);
cmd.Parameters.AddWithValue("@id", item.Id);
conn.Open();
cmd.ExecuteNonQuery();
}
}
private void TestingEnvironment()
{
List <SmtpQueue> smtpQueues = SmtpQueueBusiness.GetList();
SmtpQueue lastReport = smtpQueues.OrderBy(a => a.Id).LastOrDefault();
DateTime date = Convert.ToDateTime(DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy 00:00:00"));
string title = date.ToString("MM/dd/yyyy 23");
if (lastReport != null && lastReport.Title == title)
{
//已经生成了报表
}
else
{
string lastTitle = lastReport?.Title;
if (string.IsNullOrEmpty(lastTitle))
{
title = date.ToString("MM/dd/yyyy HH");
}
else
{
DateTime lastDate = DateTime.Parse(lastTitle.Contains(" ") ? lastTitle + ":00:00" : lastTitle);
date = lastDate.AddHours(1);
title = date.ToString("MM/dd/yyyy HH");
}
//// 每天9开始统计前一天的数据
//if (DateTime.Now > Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 09:00:00")))
//{
List <ZoneEntity> zoneEntities = ZoneBusiness.GetZoneList().Where(a => a.IfEnable).ToList();
if (zoneEntities != null && zoneEntities.Count > 0)
{
foreach (ZoneEntity zone in zoneEntities)
{
CreateActiveReportZoneTesting(zone, date);
}
//插入邮件发送队列
SmtpQueueBusiness.Add(new SmtpQueue
{
Title = title,
Status = 0,
CreatedTime = DateTime.Now,
SendedTime = DateTime.Now,
Remark = "",
});
}
}
//}
}
/// <summary>
/// 发送邮件
/// </summary>
public void MainQueueDoWork()
{
try
{
if (!isProcessing)
{
isProcessing = true;
List <SmtpQueue> smtpQueues = SmtpQueueBusiness.GetList();
List <SmtpQueue> subQueues = smtpQueues.Where(a => a.Status == 0).ToList();
if (subQueues != null && subQueues.Count > 0)
{
var configuration = GlobalConfigurationBusiness.GetConfigurationList().FirstOrDefault();
for (int i = 0; i < subQueues.Count; i++)
{
SmtpQueue smtp = subQueues[i];
string title = smtp.Title;
string bodys = GeneratedMail(title);
string smtpserver = ConstValues.emailsmtp;
bool enablessl = ConstValues.emailssl;
string userName = ConstValues.emailusername;
string pwd = Utils.AesDecrypt(ConstValues.emailpassword);
string nickName = ConstValues.emailnickname;
string strfrom = ConstValues.emailfrom;
string strto = configuration.EmailAddForWhiteList;
string subj = string.Format("IP Action Report {0}", title);
int port = ConstValues.emailport;
bool authentication = ConstValues.emailauthentication;
int timeout = ConstValues.emailtimeout;
Utils.SendMail(smtpserver, enablessl, userName, pwd, nickName, strfrom, strto, subj, bodys, port, authentication, timeout);
smtp.Status = 1;
SmtpQueueBusiness.Edit(smtp);
}
}
isProcessing = false;
}
}
catch (Exception e)
{
logService.Error(e.StackTrace);
isProcessing = false;
}
finally
{
}
}
private string GeneratedMail(string title)
{
StringBuilder mail = new StringBuilder();
SmtpQueue smtpQueue = SmtpQueueBusiness.GetByTitle(title);
if (smtpQueue != null && smtpQueue.Id > 0)
{
mail.AppendLine("<div id=\"mail\">");
List <ActionReport> actionReports = ActionReportBusiness.GetListByTitle(title);
List <ZoneEntity> zoneEntities = ZoneBusiness.GetZoneList().Where(a => a.IfEnable).ToList();
foreach (ZoneEntity zone in zoneEntities)
{
List <ActionReport> subActionReports = actionReports.Where(a => a.ZoneId == zone.ZoneId && a.Mode == "Action").ToList();
string body = CreateMainZone(zone.ZoneName, subActionReports);
mail.Append(body);
}
mail.AppendLine("</div>");
}
return(mail.ToString());
}
private void ProductionEnvironment()
{
List <SmtpQueue> smtpQueues = SmtpQueueBusiness.GetList();
SmtpQueue lastReport = smtpQueues.OrderBy(a => a.Id).LastOrDefault();
DateTime date = DateTime.Now.AddDays(-1);
string title = date.ToString("MM/dd/yyyy");
if (lastReport != null && lastReport.Title == title)
{
//已经生成了报表
}
else
{
//// 每天9开始统计前一天的数据
//if (DateTime.Now > Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 09:00:00")))
//{
List <ZoneEntity> zoneEntities = ZoneBusiness.GetZoneList().Where(a => a.IfEnable).ToList();
if (zoneEntities != null && zoneEntities.Count > 0)
{
foreach (ZoneEntity zone in zoneEntities)
{
CreateActiveReportZoneProduction(zone, date);
}
//插入邮件发送队列
SmtpQueueBusiness.Add(new SmtpQueue
{
Title = title,
Status = 0,
CreatedTime = DateTime.Now,
SendedTime = DateTime.Now,
Remark = "",
});
}
}
//}
}
public static void Edit(SmtpQueue item)
{
SmtpQueueAccess.Edit(item);
}
public static void Add(SmtpQueue item)
{
SmtpQueueAccess.Add(item);
}
