//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); }