public string GetBankListCodeByToAccountId(int?toAccountId)
{
if (toAccountId == null)
{
return(null);
}
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT BankListCode FROM dbo.BankAccounts WHERE ToAccount_ID = @toAccountId";
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("toAccountId", toAccountId));
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return(DbReadingUtility.NullableString(reader["BankListCode"]));
}
}
}
}
return(null);
}
public int GetRepurchaseOrderIdFromReissueOrderId(int reissueOrderId)
{
using (var connection = new SqlConnection(VMaRSConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT MatchingClientOrder_ID, PreviousClientOrder_ID FROM dbo.RSRepurchase WITH (NOLOCK) WHERE ClientOrder_ID = @reissueOrderId ";
command.Parameters.Add(new SqlParameter("@reissueOrderId", reissueOrderId));
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var matchingOrderId = DbReadingUtility.NullableInt(reader["MatchingClientOrder_ID"]);
var previousOrderId = DbReadingUtility.NullableInt(reader["PreviousClientOrder_ID"]);
return(matchingOrderId > 0 ? matchingOrderId : previousOrderId);
}
return(0);
}
}
}
}
private static void PopulateQuoteFromDataReader(Quote quote, SqlDataReader reader)
{
quote.Id = Convert.ToInt32(reader["Quote_ID"]);
quote.ExpirationTimeStamp = DbReadingUtility.TimeZoneAdjustedNullableDateTime(reader["SelectedDuration"]);
quote.RateRunId = DbReadingUtility.NullableInt((reader["RateRun_ID"]));
quote.ExpirationDurationInMinutes = (double)DbReadingUtility.NullableDecimal(reader["Duration"]);
quote.EffectiveMultiplier = DbReadingUtility.NullableDecimal(reader["ChildEffectiveMultiplier"]);
quote.MarkupType = MarkupTypeCache.Instance.GetById(Convert.ToInt32((reader["SpreadType_ID"])));
quote.ClientId = Convert.ToInt32(reader["Client_ID"]);
}
private static Order PopulateOrder(SqlDataReader reader, Order order)
{
order.Id = Convert.ToInt32(reader["ClientOrder_ID"]);
order.RelatedOrderId = DbReadingUtility.NullableInt(reader["RelatedClientOrder_ID"], null);
order.ClientId = Convert.ToInt32(reader["Client_ID"]);
order.SettlementCurrency = CurrencyCache.Instance.GetById(Convert.ToInt32(reader["SettlementCurrency_ID"]));
order.ConfirmationNumber = DbReadingUtility.NullableString(reader["ConfirmationNo"]).Trim();
order.OrderedAt = DbReadingUtility.NullableDateTime(reader["Ordered"]);
order.RueschStaffId = DbReadingUtility.NullableInt(reader["RueschStaff_ID"]);
order.ApplicationId = DbReadingUtility.NullableInt(reader["Application_ID"]);
order.OrderTypeDescription = OrderTypeDescriptionCache.Instance.GetById(Convert.ToInt32(reader["OrderType_ID"]));
return(order);
}
private FuturePaymentEventLog ConstructFuturePaymentEventLog(SqlDataReader reader)
{
return(new FuturePaymentEventLog
{
Id = Convert.ToInt32(reader["Id"]),
LineItemId = Convert.ToInt32(reader["OrderDetailId"]),
NewReleaseDate = Convert.ToDateTime(reader["NewReleaseDate"]),
Status = (RecordStatus)DbReadingUtility.NullableInt(reader["Processed"]),
ProcessedTime = DbReadingUtility.NullableDateTime(reader["ProcessedTime"], null),
CreatedOn = DbReadingUtility.NullableDateTime(reader["Initdt"], null),
UpdatedOn = DbReadingUtility.NullableDateTime(reader["Upddt"], null),
PreviousReleaseDate = DbReadingUtility.NullableDateTime(reader["PreviousReleaseDate"], null)
});
}
private LineItem CreateLineItem(SqlDataReader reader)
{
var orderType = OrderTypeCache.Instance.GetById(Convert.ToInt32(reader["OrderType_ID"]));
if (singleSidedOrders.Contains(orderType))
{
return new SingleSidedLineItem
{
BeneficiaryId = DbReadingUtility.NullableInt(reader["Beneficiary_ID"]),
DepositDueDate = DbReadingUtility.NullableDateTime(reader["DepositDueDate"])
}
}
;
return(new LineItem());
}
private VmsEventLog ConstructVmsEventLog(SqlDataReader reader)
{
return(new VmsEventLog
{
Id = Convert.ToInt32(reader["LOG_ID"]),
OrderId = Convert.ToInt32(reader["ClientOrder_ID"]),
LineItemId = Convert.ToInt32(reader["OrderDetail_ID"]),
NewOrderId = DbReadingUtility.NullableInt(reader["NewClientOrder_ID"], null),
NewLineItemId = DbReadingUtility.NullableInt(reader["NewOrderDetail_ID"], null),
ItemNumber = DbReadingUtility.NullableInt(reader["ITEM_NO"], null),
Event = DbReadingUtility.NullableString(reader["EVENT"]),
Status = (RecordStatus)DbReadingUtility.NullableInt(reader["PROCESSED"]),
CreatedBy = DbReadingUtility.NullableInt(reader["INITID"], null),
CreatedOn = DbReadingUtility.NullableDateTime(reader["INITDT"], null),
UpdatedBy = DbReadingUtility.NullableInt(reader["UPDID"], null),
UpdatedOn = DbReadingUtility.NullableDateTime(reader["UPDDT"], null)
});
}
public int GetOrderIdByNewOrderId(int newClientOrderId)
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText =
@"SELECT TOP 1 [ClientOrder_ID]
FROM [dbo].[VmsEventLog] WITH (NOLOCK)
WHERE [NewClientOrder_ID] = @newClientOrderId";
command.Parameters.AddWithValue("@newClientOrderId", newClientOrderId);
return(DbReadingUtility.NullableInt(command.ExecuteScalar()));
}
}
}
public WssFxTrade Save(WssFxTrade deal)
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (var command = new SqlCommand("dbo.SP_WSFX_To_NSTrans_Tables", connection)
{
CommandType = CommandType.StoredProcedure
})
{
command.Parameters.Add(new SqlParameter("UserId", deal.UserId));
command.Parameters.Add(new SqlParameter("RefNumber", deal.RefNumber));
command.Parameters.Add(new SqlParameter("RelatedRefNumber", deal.RelatedRefNumber));
command.Parameters.Add(new SqlParameter("DealType", deal.DealType));
command.Parameters.Add(new SqlParameter("ItemType", deal.ItemType));
command.Parameters.Add(new SqlParameter("BuyCcy", deal.BuyCcy));
command.Parameters.Add(new SqlParameter("SellCcy", deal.SellCcy));
command.Parameters.Add(new SqlParameter("BuyAmount", deal.BuyAmount));
command.Parameters.Add(new SqlParameter("SellAmount", deal.SellAmount));
command.Parameters.Add(new SqlParameter("TradeRate", deal.TradeRate));
command.Parameters.Add(new SqlParameter("CustNo", deal.CustNo));
command.Parameters.Add(new SqlParameter("EntryDate", deal.EntryDate));
command.Parameters.Add(new SqlParameter("TradeDate", deal.TradeDate));
command.Parameters.Add(new SqlParameter("ValueDate", deal.ValueDate));
command.Parameters.Add(new SqlParameter("MultDiv", deal.MultDiv));
command.Parameters.Add(new SqlParameter("Status", SqlDbType.Int)
{
Direction = ParameterDirection.Output
});
command.Parameters.Add(new SqlParameter("StatusMsg", SqlDbType.VarChar, 180)
{
Direction = ParameterDirection.Output
});
command.ExecuteNonQuery();
deal.Status = Convert.ToInt32(command.Parameters["Status"].Value);
deal.StatusMsg = DbReadingUtility.NullableString((command.Parameters["StatusMsg"].Value));
return(deal);
}
}
}
private static LineItemQuote GetLineItemQuoteFromDataReader(SqlDataReader reader)
{
var lineItemQuote = new LineItemQuote();
PopulateQuoteFromDataReader(lineItemQuote, reader);
var tradeCurrency = CurrencyCache.Instance.GetById(Convert.ToInt32(reader["Currency_ID"]));
var settlementCurrency = CurrencyCache.Instance.GetById(Convert.ToInt32(reader["SettlementCurrency_ID"]));
var rateConventionSpotRate = (RateConvention)Convert.ToInt32(reader["SpotRateIsPer"]);
var rateConventionClientRate = (RateConvention)Convert.ToInt32(reader["ClientRateIsPer"]);
var rateConventionForwardPoints = (RateConvention)Convert.ToInt32(reader["ForwardPointsIsPer"]);
if (rateConventionClientRate == RateConvention.Default)
{
rateConventionClientRate = rateConventionSpotRate;
}
var nDecIndirect = Convert.ToInt32(reader["SpotRateNDecPer"]);
var nDecDirect = Convert.ToInt32(reader["SpotRateNDecIn"]);
lineItemQuote.SpotRate = CreateRateFromUnmultipliedValue(rateConventionSpotRate, Convert.ToDecimal(reader["SpotRate"]),
settlementCurrency, tradeCurrency,
Convert.ToInt32(reader["RateMultiplier"]), nDecDirect, nDecIndirect);
lineItemQuote.ClientRate = CreateRateFromMultipliedValue(rateConventionClientRate, Convert.ToDecimal(reader["ClientRate"]),
settlementCurrency, tradeCurrency,
Convert.ToInt32(reader["RateMultiplier"]), nDecDirect, nDecIndirect);
lineItemQuote.ForwardPoints = CalculateForwardPoints(DbReadingUtility.NullableDecimal(reader["ForwardPoints"]),
rateConventionSpotRate, rateConventionForwardPoints, lineItemQuote);
lineItemQuote.MarkupPct = ConvertCctMarkupInTenthsOfPercentToMarkupInPercent(DbReadingUtility.NullableDecimal(reader["Spread"]));
lineItemQuote.StartValueDate = DbReadingUtility.NullableDateTime(reader["ForwardStartDate"]);
lineItemQuote.EndValueDate = DbReadingUtility.NullableDateTime(reader["ForwardEndDate"]);
return(lineItemQuote);
}
public DateTime?GetAgingReleaseDateByItemId(int itemId)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format("SELECT ReleaseDate FROM dbo.OrderDetailAging WHERE OrderDetail_ID = {0}", itemId);
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(DbReadingUtility.NullableDateTime(reader["ReleaseDate"]));
}
}
}
}
return(null);
}
private LineItem GetLineItemFromDataReader(SqlDataReader reader)
{
var lineItem = CreateLineItem(reader);
Currency settlementCurrency = CurrencyCache.Instance.GetById(Convert.ToInt32(reader["SettlementCurrency_ID"]));
Currency tradeCurrency = CurrencyCache.Instance.GetById(Convert.ToInt32(reader["Currency_ID"]));
lineItem.Id = Convert.ToInt32(reader["OrderDetail_ID"]);
lineItem.Number = Convert.ToInt32(reader["ItemNo"]);
lineItem.SettlementMoney = new Money(settlementCurrency,
Convert.ToDecimal(DbReadingUtility.NullableDecimal(reader["Extension"])));
lineItem.IsAmountInSettlementCurrency = Convert.ToBoolean(reader["ForeignAmountIsInSC"]);
lineItem.TradeMoney = new Money(tradeCurrency,
Convert.ToDecimal(DbReadingUtility.NullableDecimal(reader["ForeignAmount"])));
lineItem.TradeAmountNDec = Convert.ToInt32(DbReadingUtility.NullableInt(reader["ForeignAmt_NDec"]));
lineItem.ItemRateValue = Convert.ToDecimal(DbReadingUtility.NullableDecimal(reader["ItemRate"]));
lineItem.LineItemFundingMethod = LineItemFundingMethodCache.Instance.GetById(Convert.ToInt32(reader["FundedBy"]));
lineItem.QuoteId = DbReadingUtility.NullableInt(reader["Quote_ID"]);
lineItem.ContributingItemId = DbReadingUtility.NullableInt(reader["ContributingItemRelatedOrderDetail_ID"]);
if (lineItem is SingleSidedLineItem)
{
lineItem.ValueDate = ((SingleSidedLineItem)lineItem).DepositDueDate;
}
else
{
lineItem.ValueDate = DbReadingUtility.NullableDateTime(reader["RequestedValueDate"], null);
}
lineItem.WindowLength = DbReadingUtility.NullableInt(reader["WindowLength"]);
lineItem.WindowStartDate = DbReadingUtility.NullableDateTime(reader["ReleaseDate"], null);
lineItem.RelatedLineItemId = DbReadingUtility.NullableInt(reader["RelatedOrderDetail_ID"]);
lineItem.OptionContractId = DbReadingUtility.NullableString(reader["OptionContractId"]);
lineItem.OptionLeg = DbReadingUtility.NullableInt(reader["OptionLeg"]);
lineItem.LineItemType = ItemTypeCache.Instance.GetById(Convert.ToInt32(reader["ItemType_ID"]));
return(lineItem);
}
public Dictionary <int, Currency> GetAllCurrenciesDictionary()
{
var currenciesDictionary = new Dictionary <int, Currency>();
using (var connection = new SqlConnection(CrsConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = @"
SELECT [Currency_ID], [Code], [Description], [NDecValue], [RoundToNearestValue], [MinimumItemAmount], [MaximumItemAmount]
FROM dbo.Currency
ORDER BY Code ASC" ;
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var currency = new Currency(Convert.ToString(reader["Code"]))
{
//Status = CurrencyStatusCache.Instance.Find(Convert.ToInt32(reader["Status_ID"])),
//IsSettlementCurrency =
// (SettlementCurrencyStatusCache.Instance.GetId(SettlementCurrencyStatus.Enabled) == DbReadingUtility.NullableInt(reader["SettlementCurrencyStatusId"])),
Description = Convert.ToString(reader["Description"]),
NumberOfDecimals = Convert.ToInt32(reader["NDecValue"]),
RoundToNearestValue = DbReadingUtility.NullableDecimal(reader["RoundToNearestValue"]),
MinimumTransactionAmount = DbReadingUtility.NullableDecimal(reader["MinimumItemAmount"]),
MaximumTransactionAmount = DbReadingUtility.NullableDecimal(reader["MaximumItemAmount"])
};
currenciesDictionary.Add(Convert.ToInt32(reader["Currency_ID"]), currency);
}
}
}
}
return(currenciesDictionary);
}
private int GetLockinOrderIdByDisbursalOrderId(int disbursalOrderId, string connectionString)
{
var lockinOrderId = 0;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetLockinOrderIdByDisbursalOrderIdQueryString(connectionString), disbursalOrderId);
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
lockinOrderId = DbReadingUtility.NullableInt(reader["RelatedClientOrder_Id"]);
}
}
}
}
return(lockinOrderId);
}
private int GetLockinItemQuoteIdFromDisbursalByDB(int lockinOrderId, string connectionString)
{
var quoteId = 0;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetLockinItemQuoteIdQueryString(connectionString), lockinOrderId);
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
quoteId = DbReadingUtility.NullableInt(reader["Quote_ID"]);
}
}
}
}
return(quoteId);
}
private RelatedLineItemQuote GetRelatedLineItemQuoteId(int relatedLineItemId)
{
#if DEBUG
Console.WriteLine(" Read historical quoteId for lineItemId {0}", relatedLineItemId);
#endif
var result = new RelatedLineItemQuote {
QuoteId = 0, RelatedItemId = 0, ClientOrderId = 0
};
using (var connection = new SqlConnection(HistoryConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText =
string.Format(
"SELECT Quote_ID, RelatedOrderDetail_ID, ClientOrder_ID FROM dbo.TRRawDetail WHERE OrderDetail_ID = {0} AND FundedBy = 80",
relatedLineItemId);
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.QuoteId = DbReadingUtility.NullableInt(reader["Quote_ID"]);
result.RelatedItemId = DbReadingUtility.NullableInt(reader["RelatedOrderDetail_ID"]);
result.ClientOrderId = DbReadingUtility.NullableInt(reader["ClientOrder_ID"]);
}
}
}
}
if (result.QuoteId != 0 || result.RelatedItemId != 0)
{
return(result);
}
using (var connection = new SqlConnection(RueschlinkConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText =
string.Format(
"SELECT Quote_ID, RelatedOrderDetail_ID, ClientOrder_ID FROM dbo.OrderDetail WHERE OrderDetail_ID = {0} AND FundedBy = 80",
relatedLineItemId);
command.CommandType = CommandType.Text;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.QuoteId = DbReadingUtility.NullableInt(reader["Quote_ID"]);
result.RelatedItemId = DbReadingUtility.NullableInt(reader["RelatedOrderDetail_ID"]);
result.ClientOrderId = DbReadingUtility.NullableInt(reader["ClientOrder_ID"]);
}
}
}
}
return(result);
}
