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