private List <SqlDataRecord> BuildSqlRecordList(CurrencyModel DataModel)
{
string CurrencyCode;
decimal CurrencyRate;
PropertyInfo[] PublicProps;
Type ModelType;
SqlDataRecord Record;
SqlMetaData[] Columns;
SqlMetaData C_Code, C_Rate;
List <SqlDataRecord> CurrencyTable = new List <SqlDataRecord>();
C_Code = new SqlMetaData("Code", SqlDbType.NVarChar, 5);
C_Rate = new SqlMetaData("Rate", SqlDbType.Decimal, 18, 2);
Columns = new SqlMetaData[] { C_Code, C_Rate };
ModelType = typeof(CurrencyRateModel);
PublicProps = ModelType.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
foreach (PropertyInfo Property in PublicProps)
{
CurrencyCode = Property.Name.ToString();
CurrencyRate = Convert.ToDecimal(Property.GetValue(DataModel.quotes).ToString());
Record = new SqlDataRecord(Columns);
Record.SetValue(Record.GetOrdinal("Code"), CurrencyCode.Substring(3));
Record.SetValue(Record.GetOrdinal("Rate"), Math.Round(CurrencyRate, 2));
CurrencyTable.Add(Record);
}
Record = new SqlDataRecord(Columns);
Record.SetValue(Record.GetOrdinal("Code"), DataModel.source);
Record.SetValue(Record.GetOrdinal("Rate"), 1.00M);
CurrencyTable.Add(Record);
return(CurrencyTable);
}
public void GetOrdinal_ThrowsOutOfRange_IfNameIsNotAColumn()
{
SqlMetaData[] metaData = new SqlMetaData[]
{
new SqlMetaData("col1", SqlDbType.NVarChar, 50),
new SqlMetaData("col2", SqlDbType.Int)
};
SqlDataRecord record = new SqlDataRecord(metaData);
Assert.Throws <IndexOutOfRangeException>(() => record.GetOrdinal("outofrange"));
Assert.Throws <IndexOutOfRangeException>(() => record.GetOrdinal("col1 "));
}
internal void WriteTo(SqlDataRecord record, object row)
{
var ordinal = record.GetOrdinal(ColumnName);
var value = _accessor[row, PropertyName];
var valueToWrite = ToDatabase(value);
record.SetValue(ordinal, valueToWrite);
}
public void GetOrdinal_ReturnsIndexOfColumn_CaseInsensitive()
{
SqlMetaData[] metaData = new SqlMetaData[]
{
new SqlMetaData("col1", SqlDbType.NVarChar, 50),
new SqlMetaData("col2", SqlDbType.Int)
};
SqlDataRecord record = new SqlDataRecord(metaData);
Assert.Equal(1, record.GetOrdinal("Col2"));
}
public void GetOrdinal_ThrowsAgumentNull_IfNameIsNull()
{
SqlMetaData[] metaData = new SqlMetaData[]
{
new SqlMetaData("col1", SqlDbType.NVarChar, 50),
new SqlMetaData("col2", SqlDbType.Int)
};
SqlDataRecord record = new SqlDataRecord(metaData);
Assert.Throws <ArgumentNullException>(() => record.GetOrdinal(null));
}
/// <summary>
/// To TableType <see cref="TableType.Company"/>
/// </summary>
/// <param name="companies">Collection of data to change</param>
/// <returns>List of <see cref="SqlDataRecord"/> objects</returns>
public static List <SqlDataRecord> ToTableTypeCompanies(this IList <Company> companies)
{
if (companies == null || companies.Count == 0)
{
return(null);
}
var returnList = new List <SqlDataRecord>();
foreach (var company in companies)
{
var sqlDataRecord = new SqlDataRecord(
new SqlMetaData("Name", SqlDbType.NVarChar, 400),
new SqlMetaData("BusinessCode", SqlDbType.NVarChar, 50),
new SqlMetaData("VatNo", SqlDbType.NVarChar, 200),
new SqlMetaData("Status", SqlDbType.VarChar, 20),
new SqlMetaData("FullAddress", SqlDbType.NVarChar, 1024),
new SqlMetaData("Url", SqlDbType.VarChar, 200)
);
sqlDataRecord.SetSqlString(sqlDataRecord.GetOrdinal("Name"), company.CompanyName);
sqlDataRecord.SetSqlString(sqlDataRecord.GetOrdinal("BusinessCode"), company.BusinessCode);
sqlDataRecord.SetSqlString(sqlDataRecord.GetOrdinal("VatNo"), company.VatNo);
sqlDataRecord.SetSqlString(sqlDataRecord.GetOrdinal("Status"), company.Status.ToString());
sqlDataRecord.SetSqlString(sqlDataRecord.GetOrdinal("FullAddress"), company.CompanyAddress.FullAddress);
sqlDataRecord.SetSqlString(sqlDataRecord.GetOrdinal("Url"), company.UrlOfAriregister);
returnList.Add(sqlDataRecord);
}
return(returnList);
}
/// <summary>
/// Gets the ordinal.
/// </summary>
/// <param name="record">The record.</param>
/// <param name="fieldName">Name of the field.</param>
/// <returns>System.Int32.</returns>
/// <exception cref="System.ArgumentNullException">record
/// or
/// fieldName</exception>
private static int GetOrdinal(SqlDataRecord record, string fieldName)
{
if (record == null)
{
throw new ArgumentNullException("record");
}
if (String.IsNullOrWhiteSpace(fieldName))
{
throw new ArgumentNullException("fieldName");
}
int ordinal = record.GetOrdinal(fieldName);
return(ordinal);
}
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;
}
/// <summary>
/// Returns an enumerator that iterates through the collection.
/// </summary>
/// <returns>A <see cref="T:System.Collections.Generic.IEnumerator`1" /> that can be used to iterate through the collection.</returns>
public IEnumerator <SqlDataRecord> GetEnumerator()
{
if (_data == null || !_data.Any())
{
yield break;
}
PropertyInfo[] properties = _importType.GetProperties();
StringComparer comparer = StringComparer.InvariantCultureIgnoreCase;
this._validator = this._validator ?? new ObjectValidator();
bool?isDynamicType = null;
int errorColumnOrdinal = -1;
var sqlMetaArray = _sqlMetadata.ToArray();
if (_sqlMetadata.Any(x => comparer.Equals(x.Name, _errorColumn)))
{
SqlDataRecord tempRecord = new SqlDataRecord(sqlMetaArray);
errorColumnOrdinal = tempRecord.GetOrdinal(_errorColumn); //will cause an exception if it does not exist, hence the any check
tempRecord = null;
}
foreach (dynamic row in _data)
{
_rowIndex++;
SqlDataRecord record = new SqlDataRecord(sqlMetaArray);
List <string> errors = new List <string>();
//check the first object to see if it is a dynamic type as we dont need to run it throught the object mapper in that case
if (!isDynamicType.HasValue)
{
isDynamicType = FileIOHelpers.IsDynamicType(row);
}
T rowObj = default(T);
if (isDynamicType.Value)
{
try
{
rowObj = FileIOUtilities.MapObject <T>(row, _rowIndex, _validator, _fileValuesMapper, ref errors);
}
catch (Exception ex)
{
errors.Add(ex.ToString());
}
}
else
{
rowObj = row;
}
try
{
//built in data annotation validation
this._validator.TryValidate(rowObj, ref errors);
//custom validation
if (_customValidator != null)
{
_customValidator.Invoke(rowObj, ref errors);
}
}
catch (Exception ex)
{
errors.Add(ex.ToString());
}
ISqlRecordMapper mapperObj = null;
//if they provide a custom mapper use that one over the interface.
if (this._customSqlMapper != null)
{
this._customSqlMapper.Invoke(rowObj, record, _rowIndex, errors);
}
else if ((mapperObj = rowObj as ISqlRecordMapper) != null)
{
mapperObj.MapSqlRecord(record, _rowIndex, errors);
}
else //last ditch effort, hopefully they don't rely on this
{
object val;
//try to set the rows from the metadata, and the properties
foreach (SqlMetaData metaData in _sqlMetadata)
{
string name = metaData.Name;
val = null;
if (!comparer.Equals(name, _errorColumn))
{
var prop = properties.FirstOrDefault(x => comparer.Equals(x.Name, name));
if (prop != null && (val = prop.GetValue(rowObj, null)) != null)
{
record.SetValue(record.GetOrdinal(name), val);
}
}
}
//if an error column is defined, set the import errors
if (errorColumnOrdinal != -1 && errors.Count != 0)
{
string errorMessage = FileIOHelpers.ErrorsToXml(errors, _rowIndex);
record.SetString(errorColumnOrdinal, errorMessage);
}
}
yield return(record);
}
}