public static EmailTemplateModel GetEmailTemplateByTemplateCode(string TempCode, string CultureId)
{
SqlCommand cmd;
EmailTemplateModel Model = null;
SqlDataReader Reader = null;
SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CentralConnection"].ConnectionString);
try
{
dbConnection.Open();
cmd = new SqlCommand("SP_GetEmailTemplateBy_TemplateCode", dbConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TemplateCode", TempCode);
cmd.Parameters.AddWithValue("@CultureId", CultureId);
Reader = cmd.ExecuteReader();
if (Reader.Read() == true)
{
Model = new EmailTemplateModel();
Model.TemplateCode = TempCode;
Model.Name = Reader["Name"].ToString();
Model.Subject = Reader["Subject"].ToString();
Model.Body = Reader["Body"].ToString();
}
}
finally
{
if (Reader != null)
{
Reader.Close();
}
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
}
return(Model);
}
public void SendOfferEmail(dynamic ApplicationContext, IJobExecutionContext ExecutionContext)
{
bool WriteEventLog = true;
SqlCommand cmd;
SqlDataReader DataReader = null;
List <OfferEmailModel> emailList = new List <OfferEmailModel>();
OfferEmailModel eModel;
string LogName = ConfigurationManager.AppSettings["ServiceLog"];
string SourceName = ConfigurationManager.AppSettings["OfferEmailSource"];
string ConnectionString = ConfigurationManager.ConnectionStrings["CentralConnection"].ConnectionString;
EventLog ServiceLog = (EventLog)ExecutionContext.MergedJobDataMap["ServiceLog"];
string InitMessage = string.Format("Initiating to run offer email algorithm.\nALGORITHM: {0}\nDB: {1}",
ApplicationContext.ProcedureName, ConnectionString);
ServiceLog.WriteEntry(InitMessage, EventLogEntryType.Warning, 45);
SqlConnection dbConnection = new SqlConnection(ConnectionString);
try
{
dbConnection.Open();
cmd = new SqlCommand(ApplicationContext.ProcedureName, dbConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
DataReader = cmd.ExecuteReader();
Log.AppendLine("Procedure ran. Reading list of eligible translators.");
while (DataReader.Read() == true)
{
eModel = new OfferEmailModel();
eModel.OrderID = DataReader["OrderID"] == DBNull.Value ? (Guid?)null : Guid.Parse(DataReader["OrderID"].ToString());
eModel.OrderNo = DataReader["OrderNo"].ToString();
eModel.TranslatorID = DataReader["TranslatorID"] == DBNull.Value ? (Guid?)null : Guid.Parse(DataReader["TranslatorID"].ToString());
eModel.EmailTo = DataReader["EmailAddress"].ToString();
eModel.TranslationType = Convert.ToInt32(DataReader["TranslationType"] == DBNull.Value ? "0" : DataReader["TranslationType"].ToString());
eModel.DeliveryLevelName = DataReader["DeliveryLevelName"].ToString();
eModel.OfferLogID = DataReader["OfferLogID"] == DBNull.Value ? (Guid?)null : Guid.Parse(DataReader["OfferLogID"].ToString());
eModel.LogCommand = DataReader["LogCommand"].ToString();
emailList.Add(eModel);
Log.AppendFormat("--> Order: {0} Log: {2} Translator: {1}.", eModel.OrderNo, eModel.EmailTo, eModel.LogCommand);
Log.AppendLine();
}
}
catch (Exception ex)
{
string message = Utility.DeepestExceptionMessage(ex);
Utility.SetErrorLog(null, SourceName, message);
Log.AppendFormat("Error while reading translator information list: {0}", message);
Log.AppendLine();
goto END;
}
finally
{
if (DataReader != null)
{
DataReader.Close();
}
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
}
if (emailList.Count == 0)
{
Log.AppendLine("No eligible translator was found to send offer email.");
WriteEventLog = false;
goto END;
}
StringBuilder Builder = new StringBuilder();
OrderFilter FilterOrder = new OrderFilter();
FilterOrder.cultureId = ApplicationContext.CultureID;
FilterOrder.ApplicationId = ApplicationContext.ApplicationID;
Log.AppendFormat("Sending email to {0} translator(s).", emailList.Count);
Log.AppendLine();
foreach (OfferEmailModel model in emailList)
{
bool SendStatus = false;
string ErrorMessage = null;
OrderWebModel OrderModel = null;
FilterOrder.orderNo = model.OrderNo;
try
{
OrderModel = DataAccess.GetOrderDetailsById(FilterOrder);
string Code = null;
string Comment = string.Empty;
if (model.DeliveryLevelName == "Light")
{
Code = "04003";
Comment = CommentLightData[ApplicationContext.CultureID];
}
else if (model.DeliveryLevelName == "Expert")
{
Code = "04012";
Comment = CommentExpertData[ApplicationContext.CultureID];
}
else if (model.DeliveryLevelName == "Business")
{
Code = "04013";
Comment = CommentBusinessData[ApplicationContext.CultureID];
}
else
{
continue;
}
if (model.TranslationType == 2 || model.TranslationType == 5) // Type is Appointed or Appointed-Native-Check
{
Code = "04004";
}
string OrderTitle = string.Format("{0}->{1} {2} {3} {4}", OrderModel.SourceLanguage, OrderModel.TargetLanguage, OrderModel.TranslationTypeName, OrderModel.TranslationFieldName, OrderModel.DeliveryPlan);
int CharCount = (int)(OrderModel.CountType == 1 ? OrderModel.WordCount : OrderModel.CharacterCount);
string CountType = OrderModel.CountType == 1 ? "words" : "characters";
EmailTemplateModel Template = DataAccess.GetEmailTemplateByTemplateCode(Code, ApplicationContext.CultureID);
TranslatorPaymentQueryModel TrPaymentModel = new TranslatorPaymentQueryModel();
TrPaymentModel.OrderNo = model.OrderNo;
TrPaymentModel.TranslatorID = model.TranslatorID.Value;
DataAccess.GetTranslatorPaymentAmount(TrPaymentModel);
decimal PaymentAmount_AU, PaymentAmount_JP;
if (TrPaymentModel.ReturnValue == 0)
{
DataAccess.DirectCurrencyConversion(OrderModel.CurrencyID.Value, 3, OrderModel.OrderDate.Value, TrPaymentModel.TranslatorPayment, out PaymentAmount_JP);
DataAccess.DirectCurrencyConversion(OrderModel.CurrencyID.Value, 7, OrderModel.OrderDate.Value, TrPaymentModel.TranslatorPayment, out PaymentAmount_AU);
}
else
{
PaymentAmount_JP = PaymentAmount_AU = 0.0M;
}
Builder.Clear();
Builder.Append(Template.Body);
Builder.Replace("[SECTION-1]", "");
Builder.Replace("[SECTION-3]", Section3Data[ApplicationContext.CultureID]);
Builder.Replace("%txt01%", OrderTitle);
Builder.Replace("%txt02%", OrderModel.SourceLanguage);
Builder.Replace("%txt03%", string.Format("{0} {1}", CharCount.ToString(), CountType));
Builder.Replace("%txt04%", OrderModel.DeliveryPlan);
Builder.Replace("%txt06%", OrderModel.ClientNo);
Builder.Replace("%txt07%", OrderModel.OrderNo);
Builder.Replace("%txt08%", Comment);
Builder.Replace("%txt09%", OrderModel.MenuScript.Substring(0, OrderModel.MenuScript.Length / 3));
Builder.Replace("%txt10%", string.Format("{0} AUD ({1} JPY)", PaymentAmount_AU.ToString(), PaymentAmount_JP.ToString()));
Builder.Replace("%txtDeliveryLevel%", model.DeliveryLevelName);
try
{
SendStatus = Utility.SendEmail(model.EmailTo, null, null, Template.Subject, Builder.ToString(), null, false);
ErrorMessage = null;
Log.AppendFormat("--> Order: {0}, Stat: SENT {1}.", OrderModel.OrderNo, model.EmailTo);
Log.AppendLine();
}
catch (Exception ex)
{
ErrorMessage = Utility.DeepestExceptionMessage(ex);
Log.AppendFormat("--> Order: {0}, Stat: NOT SENT {1} Error: {2}.", OrderModel.OrderNo, model.EmailTo, ErrorMessage);
Log.AppendLine();
}
if (model.LogCommand == "INSERT")
{
SqlDataRecord Record = new SqlDataRecord(Col_NewLogEntry);
Record.SetValue(Record.GetOrdinal("OrderID"), model.OrderID);
Record.SetValue(Record.GetOrdinal("TranslatorID"), model.TranslatorID);
Record.SetValue(Record.GetOrdinal("Status"), (SendStatus == true ? "SENT" : "NOT SENT"));
if (ErrorMessage == null)
{
Record.SetDBNull(Record.GetOrdinal("ErrorMessage"));
}
else
{
Record.SetValue(Record.GetOrdinal("ErrorMessage"), ErrorMessage);
}
NewLogList.Add(Record);
}
else if (model.LogCommand == "UPDATE")
{
SqlDataRecord Record = new SqlDataRecord(Col_OldLogEntry);
Record.SetValue(Record.GetOrdinal("ID"), model.OfferLogID);
Record.SetValue(Record.GetOrdinal("Status"), SendStatus);
if (ErrorMessage == null)
{
Record.SetDBNull(Record.GetOrdinal("ErrorMessage"));
}
else
{
Record.SetValue(Record.GetOrdinal("ErrorMessage"), ErrorMessage);
}
ModifyLogList.Add(Record);
}
}
catch (Exception ex)
{
string message = Utility.DeepestExceptionMessage(ex);
Utility.SetErrorLog(null, SourceName, message);
Log.AppendFormat("Error while accessing database: {0}", message);
Log.AppendLine();
}
}
Log.AppendLine("Updating offer email log data.");
try
{
dbConnection.Open();
cmd = new SqlCommand("SP_LogOfferEmailInfo_TVP", dbConnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter ParamNewLog = new SqlParameter("@LOG_NEW", SqlDbType.Structured);
ParamNewLog.Direction = ParameterDirection.Input;
ParamNewLog.TypeName = "TYPE_NEW_EMAILLOG";
ParamNewLog.Value = NewLogList.Count == 0 ? null : NewLogList;
SqlParameter ParamModifyLog = new SqlParameter("@LOG_MODIFY", SqlDbType.Structured);
ParamModifyLog.Direction = ParameterDirection.Input;
ParamModifyLog.TypeName = "TYPE_MODIFY_EMAILLOG";
ParamModifyLog.Value = ModifyLogList.Count == 0 ? null : ModifyLogList;
cmd.Parameters.Add(ParamNewLog);
cmd.Parameters.Add(ParamModifyLog);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string message = Utility.DeepestExceptionMessage(ex);
Utility.SetErrorLog(null, SourceName, message);
Log.AppendFormat("Error while updating log data: {0}", message);
Log.AppendLine();
}
finally
{
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
}
END:
NewLogList.Clear();
ModifyLogList.Clear();
Log.AppendLine("Process finished.");
if (WriteEventLog == true)
{
ServiceLog.WriteEntry(Log.ToString(), EventLogEntryType.Information, 100);
}
return;
}