Beispiel #1
0
        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());
        }
Beispiel #2
0
        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;
        }