public static OrderWebModel GetOrderDetailsById(OrderFilter filter)
{
SqlCommand cmd;
SqlDataReader dataReader;
List <OrderWebModel> _orders = new List <OrderWebModel>();
SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CentralConnection"].ConnectionString);
try
{
dbConnection.Open();
cmd = new SqlCommand("SP_GetOrderDetailsBy_Id", dbConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CultureId", filter.cultureId);
cmd.Parameters.AddWithValue("@ApplicationId", filter.ApplicationId);
cmd.Parameters.AddWithValue("@TranslationType", filter.translationType);
cmd.Parameters.AddWithValue("@OrderId", filter.orderId);
cmd.Parameters.AddWithValue("@OrderNo", filter.orderNo);
cmd.Parameters.AddWithValue("@SourceLangID", filter.srcLangId);
cmd.Parameters.AddWithValue("@TargetLangID", filter.trgLangId);
cmd.Parameters.AddWithValue("@SpecialFieldID", filter.specialFieldId);
cmd.Parameters.AddWithValue("@ClientID", filter.clientId);
cmd.Parameters.AddWithValue("@TranslatorID", filter.translatorId);
cmd.Parameters.AddWithValue("@OrderStatus", filter.orderStatus);
cmd.Parameters.AddWithValue("@StartDate", filter.startDate);
cmd.Parameters.AddWithValue("@EndDate", filter.endDate);
dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
OrderWebModel order = new OrderWebModel();
order.ID = Guid.Parse(dataReader["ID"].ToString());
order.DeliveryPlanID = Convert.ToInt64(dataReader["DeliveryPlanID"].ToString());
order.ClientID = Guid.Parse(dataReader["ClientId"].ToString());
order.ClientNo = dataReader["ClientNo"].ToString();
order.ApplicationName = dataReader["ApplicationName"].ToString();
order.AssignedTranslatorID = dataReader["AssignedTranslatorID"] == DBNull.Value ? (Guid?)null : Guid.Parse(dataReader["AssignedTranslatorID"].ToString());
order.CommentToTranslator = dataReader["CommentToTranslator"].ToString();
order.DeliveryComment = dataReader["DeliveryComment"].ToString();
order.CompanyNotes = dataReader["CompanyNotes"].ToString();
order.CommentToBcause = dataReader["CommentToBcause"].ToString();
order.CurrencyCode = dataReader["CurrencyCode"].ToString();
order.CurrencySymbol = dataReader["CurrencySymbol"].ToString();
order.CurrencyName = dataReader["CurrencyName"].ToString();
order.CompletionDate = dataReader["CompletionDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dataReader["CompletionDate"]);
order.CurrencyID = Convert.ToInt64(dataReader["CurrencyID"].ToString());
order.DeliveryDate = dataReader["DeliveryDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dataReader["DeliveryDate"]);
order.DeliveryLevelName = dataReader["DeliveryLevelName"].ToString();
order.StartDate = dataReader["StartDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dataReader["StartDate"]);
order.EndDate = dataReader["EndDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dataReader["EndDate"]);
order.RequestDate = dataReader["RequestDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dataReader["RequestDate"]);
order.OrderStatus = dataReader["OrderStatus"] == DBNull.Value ? (int?)null : Convert.ToInt32(dataReader["OrderStatus"]);
order.DeliveryPlan = dataReader["DeliveryPlan"].ToString();
order.DeliveryTime = dataReader["DeliveryTime"] == DBNull.Value ? (decimal?)null : Convert.ToDecimal(dataReader["DeliveryTime"].ToString());
order.DeliveryType = dataReader["DeliveryType"] == DBNull.Value ? (int?)null : Convert.ToInt32(dataReader["DeliveryType"].ToString());
order.InvoiceNo = dataReader["InvoiceNo"].ToString();
order.TranslationType = (TranslationType)Convert.ToInt32(dataReader["TranslationType"].ToString());
order.TranslationTypeName = order.TranslationType.ToString();
order.PaymentAmount = Convert.ToDecimal(dataReader["PaymentAmount"].ToString());
order.TranslatorFee = dataReader["TranslatorFee"] == DBNull.Value ? (decimal?)null : Convert.ToDecimal(dataReader["TranslatorFee"].ToString());;
order.EstimatedPrice = Convert.ToDecimal(dataReader["EstimatedPrice"].ToString());
order.UnitPrice = Convert.ToDecimal(dataReader["UnitPrice"].ToString());
order.Discount = Convert.ToDecimal(dataReader["Discount"].ToString());
order.PriceAfterDiscount = Convert.ToDecimal(dataReader["PriceAfterDiscount"].ToString());
order.ConsumptionTax = Convert.ToDecimal(dataReader["ConsumptionTax"].ToString());
order.OrderDate = dataReader["OrderDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dataReader["OrderDate"]);
order.OrderId = Convert.ToInt64(dataReader["OrderID"].ToString());
order.OrderNo = dataReader["OrderNo"].ToString();
order.TranslatorNo = dataReader["TranslatorNo"] == DBNull.Value ? (long?)null : Convert.ToInt64(dataReader["TranslatorNo"].ToString());
order.OrderStatusName = dataReader["OrderStatusName"].ToString();
order.TranslatorName = dataReader["FirstName"].ToString() + " " + dataReader["MiddleName"].ToString() + " " + dataReader["LastName"].ToString();
order.MenuScript = dataReader["MenuScript"].ToString();
order.WordCount = Convert.ToInt64(dataReader["WordCount"].ToString());
order.CountType = dataReader["CountType"] == DBNull.Value ? (int?)null : Convert.ToInt32(dataReader["CountType"].ToString());
order.CharacterCount = Convert.ToInt64(dataReader["CharacterCount"].ToString());
order.TargetLanguageID = Guid.Parse(dataReader["TargetLanguageID"].ToString());
order.SourceLanguageID = Guid.Parse(dataReader["SourceLanguageID"].ToString());
order.TranslationFieldID = Guid.Parse(dataReader["TranslationFieldID"].ToString());
order.TargetLanguage = dataReader["TargetLanguage"].ToString();
order.SourceLanguage = dataReader["SourceLanguage"].ToString();
order.EvaluationScore = Convert.ToDecimal(dataReader["EvaluationScore"].ToString());
order.EvaluationComment = dataReader["EvaluationComment"].ToString();
order.TranslationFieldName = dataReader["TranslationFieldName"].ToString();
order.SubSpecialityFieldID = dataReader["SubSpecialityFieldID"] == DBNull.Value ? (Guid?)null : Guid.Parse(dataReader["SubSpecialityFieldID"].ToString());
order.SubSpecialityFieldName = dataReader["SubSpecialityFieldName"].ToString();
order.PaymentDate = dataReader["PaymentDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dataReader["PaymentDate"].ToString());
_orders.Add(order);
}
dataReader.Close();
}
finally
{
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
}
return(_orders.FirstOrDefault());
}
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;
}
