//this method adds Purchase Order email to the database
public void AddPurchaseOrderEmail(OrderSendEmail purchaseOrderEmail)
{
//insert
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"INSERT INTO OrderSendEmail(PurchaseOrderId, NoOfTry, Status, CreatedDate, SendDate,FileNames,SupplierName,EmailBody,OrderType,IsOrderNow)
VALUES(@purchaseOrderId, @noOfTry, @status,CURRENT_TIMESTAMP,@sendDate,@fileNames,@supplierName,@emailBody,@orderType,@IsOrderNow)";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.Parameters.Add("@purchaseOrderId", SqlDbType.Int).Value = purchaseOrderEmail.PurchaseOrderId;
cmd.Parameters.Add("@NoOfTry", SqlDbType.Int).Value = purchaseOrderEmail.NoOfTry;
cmd.Parameters.Add("@status", SqlDbType.Int).Value = purchaseOrderEmail.Status;
cmd.Parameters.Add("@sendDate", SqlDbType.DateTime).Value = purchaseOrderEmail.SendDate;
cmd.Parameters.Add("@supplierName", SqlDbType.VarChar).Value = purchaseOrderEmail.SupplierName;
cmd.Parameters.Add("@fileNames", SqlDbType.NVarChar).Value = purchaseOrderEmail.FileNames;
cmd.Parameters.Add("@emailBody", SqlDbType.NVarChar).Value = purchaseOrderEmail.EmailBody;
cmd.Parameters.Add("@orderType", SqlDbType.NVarChar).Value = purchaseOrderEmail.OrderType;
cmd.Parameters.Add("@IsOrderNow", SqlDbType.Bit).Value = purchaseOrderEmail.IsOrderNow;
cmd.CommandText = strSqlContactStmt;
cmd.ExecuteNonQuery();
}
conn.Close();
}
public IList <OrderSendEmail> GetOrderSendEmail(int status)
{
var listObj = new List <OrderSendEmail>();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"SELECT Id,PurchaseOrderId,NoOfTry,ResultMessage,SendDate ,SupplierName,FileNames,EmailBody ,OrderType from OrderSendEmail where Status=@status";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Parameters.AddWithValue("@status", SqlDbType.Int).Value = status;
cmd.CommandText = strSqlContactStmt;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var obj = new OrderSendEmail();
obj.Id = Convert.ToInt32(reader["Id"]);
obj.PurchaseOrderId = Convert.ToInt32(reader["PurchaseOrderId"]);
if (reader["ResultMessage"] != DBNull.Value)
{
obj.ResultMessage = reader["ResultMessage"].ToString();
}
obj.SupplierName = reader["SupplierName"].ToString();
obj.FileNames = reader["FileNames"].ToString();
if (reader["EmailBody"] != DBNull.Value)
{
obj.EmailBody = reader["EmailBody"].ToString();
}
if (reader["OrderType"] != DBNull.Value)
{
obj.OrderType = reader["OrderType"].ToString();
}
obj.NoOfTry = Convert.ToInt32(reader["NoOfTry"]);
obj.Status = status;
obj.SendDate = Convert.ToDateTime(reader["SendDate"]);
listObj.Add(obj);
}
}
}
conn.Close();
return(listObj);
}
protected void UpdateStatusSendEmail(bool result, OrderSendEmail obj)
{
if (result)
{
var resuMessage = "Email successfully sent";
var status = (int)StatusEmailSupplier.SuccessfullySent;
UpdateStatus(status, obj.Id, resuMessage);
}
else
{
var resuMessage = "Email send failed";
var noTry = obj.NoOfTry + 1;
if (noTry <= 3)
{
UpdatePurchaseOrderMessageAndTryEmail(obj.Id, resuMessage, noTry);
}
else
{
var resuMessageFailed = "Email send permanently failed ";
var status = (int)StatusEmailSupplier.Failed;
UpdateStatus(status, obj.Id, resuMessageFailed);
}
}
}
public bool SendEmailPurchaseOrder(OrderSendEmail obj)
{
var supplierDetails = GetSupplierEmails(obj.SupplierName, 1);
ILog _logger = LogManager.GetLogger(typeof(OrderSendEmailDAL));
var xeroInVoice = getXeroDTSID(obj.PurchaseOrderId);
var body = "Hi , <br/> Please process the attached order.";
var subject = "Deltone Solutions Order " + xeroInVoice;
var contactID = Convert.ToInt32(getContactID(obj.PurchaseOrderId));
var companyName = GetCompanybyContactID(contactID);
if (supplierDetails.Count() > 0)
{
var toAddress = supplierDetails[0].SupplierEmailAddress;
var ccAddress = "";
// var toAddress = "*****@*****.**";
foreach (var item in supplierDetails.Skip(1))
{
if (ccAddress == "")
{
ccAddress = item.SupplierEmailAddress;
}
else
{
ccAddress = ccAddress + "," + item.SupplierEmailAddress;
}
}
var bodyMessageCom = " <br/> Delivery Type : " + obj.OrderType;
body = body + bodyMessageCom;
if (!string.IsNullOrEmpty(obj.EmailBody))
{
body = body + " <br/> Instructions : " + obj.EmailBody;
}
var bccAddress = DelToneCommonSettings.bccInfoAddress;
var fromName = DelToneCommonSettings.fromName;
var fromAddress = DelToneCommonSettings.fromAddress;
var invoiceFilePath = DelToneCommonSettings.fileInvoicePath;
var invoiceAusJetPath = DelToneCommonSettings.fileCSVPath;
var files = obj.FileNames.Split(',');
var attachements = new List <Attachment>();
var comFilePath = "";
var invoiceFileName = obj.SupplierName + "-" + xeroInVoice + "-" + companyName + ".pdf";
var invoiceFile = invoiceFilePath + invoiceFileName;
if (File.Exists(invoiceFile))
{
System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(invoiceFile);
attachment.Name = invoiceFileName;
attachements.Add(attachment);
}
if (obj.SupplierName == "Ausjet")
{
var csvFile = "AUSJET - Order " + xeroInVoice + ".csv";
comFilePath = invoiceAusJetPath + csvFile;
if (File.Exists(comFilePath))
{
System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(comFilePath);
attachment.Name = csvFile;
attachements.Add(attachment);
}
}
//foreach (var file in files)
//{
// var fIem = file;
// if (obj.SupplierName == "Ausjet")
// {
// comFilePath = invoiceFilePath + fIem;
// if (File.Exists(comFilePath))
// {
// System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(comFilePath);
// attachment.Name = fIem;
// attachements.Add(attachment);
// }
// else
// {
// comFilePath = invoiceAusJetPath + fIem;
// if (File.Exists(comFilePath))
// {
// System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(comFilePath);
// attachment.Name = fIem;
// attachements.Add(attachment);
// }
// }
// }
// else
// {
// comFilePath = invoiceFilePath + fIem;
// if (File.Exists(comFilePath))
// {
// System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(comFilePath);
// attachment.Name = fIem;
// attachements.Add(attachment);
// }
// }
//}
var sendEmail = new EmailSender();
try
{
var resultSend = sendEmail.SendEmail(fromAddress, fromName, toAddress, ccAddress, bccAddress, subject, body, true, attachements);
if (resultSend)
{
_logger.Info(" Email Sent Success :" + toAddress + " Order Id: " + obj.PurchaseOrderId + " Xero Dts: " + xeroInVoice);
}
else
{
_logger.Info(" Email Sent Error : " + toAddress + " Order Id: " + obj.PurchaseOrderId + " Xero Dts: " + xeroInVoice);
}
return(resultSend);
}
catch (Exception ex)
{
_logger.Error(" Error Occurred :" + ex + " Email Sent "
+ toAddress + " Order Id: " + obj.PurchaseOrderId + " Xero Dts: " + xeroInVoice);
return(false);
}
}
return(false);
}