internal static List <DayCounter> GetDayCounter(ConnectionContextMySQL ctx, long?idDC, string className)
{
List <DayCounter> list = new List <DayCounter>();
var command = SqlHelper.SelectDayCounterCommand(ctx, idDC, className);
using (var reader = command.ExecuteReader())
{
//Repository.DayCounterDic.Clear();
while (reader.Read())
{
var r = new DayCounter
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? null : reader.GetString(reader.GetOrdinal("Name")),
ClassName = reader.IsDBNull(reader.GetOrdinal("ClassName")) ? null : reader.GetString(reader.GetOrdinal("ClassName"))
};
string param = reader.IsDBNull(reader.GetOrdinal("ClassNameParam")) ? null : reader.GetString(reader.GetOrdinal("ClassNameParam"));
if (!string.IsNullOrEmpty(param))
{
r.ClassName += ("::" + param);
}
list.Add(r);
//Repository.DayCounterDic[r.Id] = r;
}
}
return(list);
}
internal static bool ConfirmUser(ConnectionContextMySQL ctx, UserAccounts.FCUser user)
{
UserAccounts.FCUser tmp = CheckSignInUser(ctx, user);
if (tmp == null ||
tmp.g == Guid.Empty ||
tmp.status != UserAccounts.FCUser.eStatus.eDisabled
)
{
return(false);
}
// creates or confirms user
var command = SqlHelper.ConfirmUser(ctx, user);
try
{
command.ExecuteNonQuery();
command.CommandText = "Commit";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
return(false);
}
return(true);
}
internal static List <ExchangeRate> GetXRates(ConnectionContextMySQL ctx, long?idXRate)
{
List <ExchangeRate> list = new List <ExchangeRate>();
var command = SqlHelper.SelectXRatesCommand(ctx, idXRate);
using (var reader = command.ExecuteReader())
{
//Repository.XRateDic.Clear();
while (reader.Read())
{
ExchangeRate tmp = new ExchangeRate
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? null : reader.GetString(reader.GetOrdinal("Name")),
ClassName = reader.IsDBNull(reader.GetOrdinal("ClassName")) ? null : reader.GetString(reader.GetOrdinal("ClassName")),
DataProviderId = reader.IsDBNull(reader.GetOrdinal("DataProviderId")) ? 0 : reader.GetInt32(reader.GetOrdinal("DataProviderId")),
DataReference = reader.IsDBNull(reader.GetOrdinal("DataReference")) ? null : reader.GetString(reader.GetOrdinal("DataReference")),
SettlementDays = reader.IsDBNull(reader.GetOrdinal("SettlementDays")) ? 0 : reader.GetInt32(reader.GetOrdinal("SettlementDays")),
FixingPlace = reader.IsDBNull(reader.GetOrdinal("FixingPlace")) ? null : reader.GetString(reader.GetOrdinal("FixingPlace")), //??? temporary solution. fixing place in database is id whereas here we are looking for quantlibclassname of calendar
PrimaryCurrencyId = reader.IsDBNull(reader.GetOrdinal("PrimaryCurrencyCode")) ? 0 : reader.GetInt32(reader.GetOrdinal("PrimaryCurrencyCode")),
SecondaryCurrencyId = reader.IsDBNull(reader.GetOrdinal("SecondaryCurrencyCode")) ? 0 : reader.GetInt32(reader.GetOrdinal("SecondaryCurrencyCode"))
};
list.Add(tmp);
//Repository.XRateDic[tmp.Id] = tmp;
}
}
return(list);
}
public static FCUser CreateUser(string n, string p)
{
FCUser newUser = new FCUser {
name = n, pass = p, status = FCUser.eStatus.eDisabled, g = Guid.NewGuid()
};
// creates or confirms user
#if _LINQXML_
DataHelperLinqXml.AddEntryPointHistory(epl);
#elif _SQLITE_
using (var ctx = new ConnectionContextSQLite())
{
if (!DataHelperSQLite.CreateUser(ctx, newUser))
{
return(null);
}
return(newUser);
}
#else // _MYSQL_
using (var ctx = new ConnectionContextMySQL())
{
return(DataHelper.CreateUser(ctx, user));
}
#endif
}
//pair of params can be added later - typy (bond, rate etc) and instrument id
//note that id is not unique as rate history contains different types of instruments from different tables (ids are unique only within one table)
internal static List <RateHistory> GetRateHistory(ConnectionContextMySQL ctx, DateTime?settlementDate)
{
List <RateHistory> RateHistoryList = new List <RateHistory>();
var command = SqlHelper.SelectRateHistoryCommand(ctx, null, settlementDate);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var d = new RateHistory
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Type = reader.GetString(reader.GetOrdinal("Type")),
RateId = reader.GetInt32(reader.GetOrdinal("RateId")),
Date = reader.GetDateTime(reader.GetOrdinal("Date")),
High = reader.IsDBNull(reader.GetOrdinal("High")) ? -1 : reader.GetDouble(reader.GetOrdinal("High")),
Low = reader.IsDBNull(reader.GetOrdinal("Low")) ? -1 : reader.GetDouble(reader.GetOrdinal("Low")),
Open = reader.IsDBNull(reader.GetOrdinal("Open")) ? -1 : reader.GetDouble(reader.GetOrdinal("Open")),
Close = reader.IsDBNull(reader.GetOrdinal("Close")) ? -1 : reader.GetDouble(reader.GetOrdinal("Close")),
Theoretical = reader.IsDBNull(reader.GetOrdinal("Theoretical")) ? -1 : reader.GetDouble(reader.GetOrdinal("Theoretical"))
};
RateHistoryList.Add(d);
}
}
return(RateHistoryList);
}
internal static MySqlCommand SelectInflationIndexCommand(ConnectionContextMySQL ctx, long? idIndex)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
#if _NO_VIEWS_
@" SELECT
b.Id,
b.Name,
b.ClassName,
b.Type,
b.DataProviderId,
b.DataReference,
b.CurrencyId
FROM inflationIndex b
WHERE 1 = 1 "
);
#else
@" SELECT * FROM vwinflationindex ");
#endif
if (idIndex != null)
{
sb.Append(" AND b.Id = @idIndex ");
command.Parameters.Add(new MySqlParameter(" @idIndex ", MySqlDbType.Int32)).Value = idIndex;
}
command.CommandText = sb.ToString();
return command;
}
internal static MySqlCommand ConfirmUser(ConnectionContextMySQL ctx, UserAccounts.FCUser user)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
MySqlParameter p = new MySqlParameter("@name", MySqlDbType.String);
p.Value = user.name;
command.Parameters.Add(p);
p = new MySqlParameter("@pass", MySqlDbType.String);
p.Value = user.pass;
command.Parameters.Add(p);
p = new MySqlParameter("@guid", MySqlDbType.String);
p.Value = user.g.ToString("B");
command.Parameters.Add(p);
// check if exists, check guid, set Enabled if ok
var sb = new StringBuilder(
@"UPDATE Users SET Status = 1
WHERE
Name = @name AND Pass = @pass AND Guid = @guid"
);
command.CommandText = sb.ToString();
return(command);
}
internal static List <Currency> GetCurrency(ConnectionContextMySQL ctx, long?idCurrency)
{
List <Currency> list = new List <Currency>();
var command = SqlHelper.SelectCurrencyCommand(ctx, idCurrency);
using (var reader = command.ExecuteReader())
{
//Repository.CurrencyDic.Clear();
while (reader.Read())
{
var r = new Currency
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
ClassName = reader.IsDBNull(reader.GetOrdinal("ClassName")) ? null : reader.GetString(reader.GetOrdinal("ClassName")),
Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? null : reader.GetString(reader.GetOrdinal("Name")),
Code = reader.IsDBNull(reader.GetOrdinal("Code")) ? null : reader.GetString(reader.GetOrdinal("Code")),
NumericCode = reader.IsDBNull(reader.GetOrdinal("NumericCode")) ? 0 : reader.GetInt32(reader.GetOrdinal("NumericCode")),
FractionsPerUnit = reader.IsDBNull(reader.GetOrdinal("FractionsPerUnit")) ? 0 : reader.GetInt32(reader.GetOrdinal("FractionsPerUnit")),
FractionSymbol = reader.IsDBNull(reader.GetOrdinal("FractionSymbol")) ? null : reader.GetString(reader.GetOrdinal("FractionSymbol")),
Symbol = reader.IsDBNull(reader.GetOrdinal("Symbol")) ? null : reader.GetString(reader.GetOrdinal("Symbol"))
};
list.Add(r);
//Repository.CurrencyDic[r.Id] = r;
}
}
return(list);
}
internal static MySqlCommand CheckSignInUser(ConnectionContextMySQL ctx, UserAccounts.FCUser user)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
MySqlParameter p = new MySqlParameter("@name", MySqlDbType.String);
p.Value = user.name;
command.Parameters.Add(p);
p = new MySqlParameter("@pass", MySqlDbType.String);
p.Value = user.pass;
command.Parameters.Add(p);
// check if exists, check guid, set Enabled if ok
var sb = new StringBuilder(
@"SELECT
Name, Pass, Guid, Status
FROM Users
WHERE Name = @name AND Pass = @pass"
);
if (user.g != Guid.Empty)
{
sb.Append(" AND Guid = @g");
p = new MySqlParameter("@g", MySqlDbType.String);
p.Value = user.g.ToString("B");
command.Parameters.Add(p);
}
command.CommandText = sb.ToString();
return(command);
}
//
// DataFeed
//
internal static MySqlCommand GetAllEntryPoints(ConnectionContextMySQL ctx)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
@"SELECT
Id,
Type,
RateId,
YieldCurveId,
Length,
TimeUnit,
DateStart,
DateFinish,
DataProviderId,
DataReference,
UserCreated,
IpCreated
FROM YcEntry
WHERE 1 == 1"
);
command.CommandText = sb.ToString();
return(command);
}
//
// UserAccounts
//
internal static MySqlCommand CreateUser(ConnectionContextMySQL ctx, UserAccounts.FCUser user)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
MySqlParameter p = new MySqlParameter("@name", MySqlDbType.String);
p.Value = user.name;
command.Parameters.Add(p);
p = new MySqlParameter("@pass", MySqlDbType.String);
p.Value = user.pass;
command.Parameters.Add(p);
p = new MySqlParameter("@guid", MySqlDbType.String);
p.Value = user.g.ToString("B");
command.Parameters.Add(p);
// if not exists - create as !Enabled
var sb = new StringBuilder(
@"INSERT
into Users
(Name, Pass, Guid, Status, UserCreated, IpCreated)
values
(@name, @pass, @guid, 0, 1, '192.168.0.0')" // 0 == disabled
);
command.CommandText = sb.ToString();
return(command);
}
internal static MySqlCommand GetEntryPointByRateIdType(ConnectionContextMySQL ctx, EntryPoint ep)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
@"SELECT
Id,
Type
FROM YcEntry
WHERE 1 == 1"
);
if (ep != null) // otherwise return ALL entry point's ids
{
sb.Append(@" RateId = @rateId AND Type = @type");
MySqlParameter p = new MySqlParameter("@rateId", MySqlDbType.Int32);
p.Value = ep.Instrument.Id;
command.Parameters.Add(p);
p = new MySqlParameter("@type", MySqlDbType.String);
p.Value = ep.Type;
command.Parameters.Add(p);
}
command.CommandText = sb.ToString();
return(command);
}
internal static MySqlCommand AddEntryPointHistory(ConnectionContextMySQL ctx, EntryPoint ep)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
@"INSERT
into YcEntryHistory
(YcEntryId, Value, Date)
values
(@epId, @epVal, @epDate)"
);
MySqlParameter p = new MySqlParameter("@epId", MySqlDbType.Int32);
p.Value = ep.Id;
command.Parameters.Add(p);
p = new MySqlParameter("@epVal", MySqlDbType.Double);
p.Value = ep.epValue.Value;
command.Parameters.Add(p);
p = new MySqlParameter("@epDate", MySqlDbType.DateTime);
p.Value = ep.epValue.Date;
command.Parameters.Add(p);
command.CommandText = sb.ToString();
return(command);
}
internal static List <InflationIndex> GetInflationIndex(ConnectionContextMySQL ctx, long?idIndex)
{
List <InflationIndex> list = new List <InflationIndex>();
var command = SqlHelper.SelectInflationIndexCommand(ctx, idIndex);
using (var reader = command.ExecuteReader())
{
//Repository.BondDic.Clear();
while (reader.Read())
{
InflationIndex b = new InflationIndex
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? null : reader.GetString(reader.GetOrdinal("Name")),
ClassName = reader.IsDBNull(reader.GetOrdinal("ClassName")) ? null : reader.GetString(reader.GetOrdinal("ClassName")),
Type = reader.GetString(reader.GetOrdinal("Type")),
DataProviderId = reader.IsDBNull(reader.GetOrdinal("DataProviderId")) ? 0 : reader.GetInt32(reader.GetOrdinal("DataProviderId")),
DataReference = reader.IsDBNull(reader.GetOrdinal("DataReference")) ? null : reader.GetString(reader.GetOrdinal("DataReference")),
IdCcy = reader.IsDBNull(reader.GetOrdinal("CurrencyId")) ? 0 : reader.GetInt32(reader.GetOrdinal("CurrencyId")),
};
list.Add(b);
}
}
return(list);
}
internal static MySqlCommand SelectRateHistoryCommand(ConnectionContextMySQL ctx,
long?RateId,
DateTime?settlementDate)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
#if _NO_VIEWS_
@"SELECT
xh.Id as Id,
xh.Type as Type,
xh.RateId as RateId,
xh.Date as Date,
xh.High as High,
xh.Low as Low,
xh.Open as Open,
xh.Close as Close,
xh.Theoretical as Theoretical,
xh.UserCreated as UserCreated,
xh.IpCreated as IpCreated
FROM ratehistory xh
WHERE 1 = 1"
);
#else
@"SELECT
xh.Id as Id,
xh.Type as Type,
xh.RateId as RateId,
xh.Date as Date,
xh.Close as Value,
FROM vwratehistory xh
WHERE 1 = 1");
#endif
if (RateId != null)
{
sb.Append(" AND xh.RateId = @RateId ");
command.Parameters.Add(new MySqlParameter("@RateId", MySqlDbType.Int32)).Value = RateId;
}
if (settlementDate != null)
{
#if _NO_VIEWS_
sb.Append(@" AND xh.Date <= @date order by xh.Date desc");
#else
sb.Append(@" AND xh.date =
(select max(xh2.Date)
FROM vwratehistory xh2
WHERE xh2.Date <= @date AND xh2.RateId = xh.Id)" );
#endif
command.Parameters.Add(new MySqlParameter("@date", MySqlDbType.Date)).Value = settlementDate;
}
sb.Append(" order by xh.RateId asc, xh.Date desc");
command.CommandText = sb.ToString();
return(command);
}
// instruments used for callibration and valid start date and valid end date
internal static List <InflationCurveEntryData> GetInflationCurveEntryData(ConnectionContextMySQL ctx, long?idIc, DateTime?settlementDate)
{
List <InflationCurveEntryData> EntryDataList = new List <InflationCurveEntryData>();
var command = SqlHelper.SelectInflationCurveEntryDataCommand(ctx, idIc, settlementDate);
using (var reader = command.ExecuteReader())
{
// Dictionary<long, ValuePair> entryPointDic = new Dictionary<long, ValuePair>();
while (reader.Read())
{
var d = new InflationCurveEntryData
{
InflationCurveId = reader.GetInt32(reader.GetOrdinal("InflationCurveId")),
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Type = reader.GetString(reader.GetOrdinal("Type")),
Enabled = true, // default
ValidDateBegin = reader.GetDateTime(reader.GetOrdinal("ValidDateStart")),
ValidDateEnd = reader.GetDateTime(reader.GetOrdinal("ValidDateEnd")),
};
if (d.Type.ToLower() == "inflationbond")
{
d.Instrument = (InflationBond)GetInflationBond(reader.GetInt32(reader.GetOrdinal("RateId")));
}
else if (d.Type.ToLower() == "swap")
{
d.Instrument = (InflationRate)GetInflationRate(reader.GetInt32(reader.GetOrdinal("RateId")));
}
/* else if (d.Type.ToLower() == "bond")
* {
* d.Instrument = (Instrument)GetBond(reader.GetInt32(reader.GetOrdinal("RateId")));
* }
* else
* {
* d.Instrument = (Instrument)GetRate(reader.GetInt32(reader.GetOrdinal("RateId")));
* } */
// if (((d.Type == "inflationbond") || (d.Type == "bond")) && (d.Instrument as Bond).MaturityDate <= settlementDate)
// continue; // skip if bond and maturity date is not in future
if ((d.Type == "inflationbond") && (d.Instrument as InflationBond).MaturityDate <= settlementDate)
{
// if ((d.Type == "inflationbond") && (d.Instrument as Bond).MaturityDate <= settlementDate)
continue; // skip if bond and maturity date is not in future
}
EntryDataList.Add(d);
}
}
EntryDataList.Sort(new InflationCurveEntryDataCompare());
return(EntryDataList);
}
internal static InflationBond GetInflationBond(long?idInflationBond)
// internal static Bond GetInflationBond(long? idInflationBond)
{
// if (idInflationBond != null && Repository.inf.BondDic.ContainsKey(idBond.Value))
// return Repository.BondDic[idBond.Value];
var ctx = new ConnectionContextMySQL();
return(GetInflationBonds(ctx, idInflationBond)[0]);
}
internal static MySqlCommand SelectExchangeRateHistoryCommand(ConnectionContextMySQL ctx,
long? RateId,
DateTime? settlementDate)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
#if _NO_VIEWS_
@" SELECT
xh.Id as Id,
xh.RateId as RateId,
xh.Date as Date,
xh.Close as Value,
CASE WHEN xe.DataProviderId IS NOT NULL THEN(xe.DataProviderId) ELSE(0) END as DataProviderId,
CASE WHEN xe.DataReference IS NOT NULL THEN(xe.DataReference) ELSE('') END as DataReference
FROM exchangeratehistory xh
JOIN exchangerate xe ON xe.Id = xh.RateId
WHERE 1 = 1 "
);
#else
@" SELECT
xh.Id as Id,
xh.RateId as RateId,
xh.Date as Date,
xh.Close as Value,
CASE WHEN xe.DataProviderId IS NOT NULL THEN(xe.DataProviderId) ELSE(0) END as DataProviderId,
CASE WHEN xe.DataReference IS NOT NULL THEN(xe.DataReference) ELSE('') END as DataReference
FROM vwexchangeratehistory xh
JOIN vwexchangerate xe ON xe.Id = xh.RateId
WHERE 1 = 1 ");
#endif
if (RateId != null)
{
sb.Append(" AND xh.RateId = @RateId ");
command.Parameters.Add(new MySqlParameter(" @RateId ", MySqlDbType.Int32)).Value = RateId;
}
if (settlementDate != null)
{
#if _NO_VIEWS_
sb.Append(@" AND xh.Date <= @date order by xh.Date desc ");
#else
sb.Append(@" AND xh.date =
(select max(xh2.Date)
FROM vwexchangeratehistory xh2
WHERE xh2.Date <= @date AND xh2.RateId = xh.Id) ");
#endif
command.Parameters.Add(new MySqlParameter(" @date ", MySqlDbType.Date)).Value = settlementDate;
}
sb.Append(" order by xh.RateId asc, xh.Date desc ");
command.CommandText = sb.ToString();
return command;
}
/// <summary>
/// Retrieves the list of yield curve data
/// </summary>
/// <param name="ctx">
/// The connection context
/// </param>
/// <param name="idYc">
/// The yield curve id
/// </param>
/// <param name="idYc">
/// The date
/// </param>
/// <returns>
/// List of yield curve data
/// </returns>
///
// ??? obsolete method
internal static List <YieldCurveData> GetYieldCurveData(ConnectionContextMySQL ctx,
long?idYc)
{
List <YieldCurveData> ycDesc = new List <YieldCurveData>();
var command = SqlHelper.SelectYieldCurveDataCommand(ctx, idYc);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var ycs = new YieldCurveSetting
{
ZcBasisId = reader.GetInt32(reader.GetOrdinal("ZcBasisId")),
ZcCompounding = reader.GetString(reader.GetOrdinal("ZcCompounding")),
ZcFrequency = reader.GetString(reader.GetOrdinal("ZcFrequency")),
FrwCompounding = reader.GetString(reader.GetOrdinal("FrwCompounding")),
ifZc = reader.GetBoolean(reader.GetOrdinal("ifZC")),
FrwBasisId = reader.GetInt32(reader.GetOrdinal("FrwBasisId")),
ZCColor = reader.IsDBNull(reader.GetOrdinal("ZCColor")) ? null : reader.GetString(reader.GetOrdinal("ZCColor")),
FrwFrequency = reader.GetString(reader.GetOrdinal("FrwFrequency")),
FrwTermBase = reader.GetString(reader.GetOrdinal("FrwTermBase")),
FrwTerm = reader.IsDBNull(reader.GetOrdinal("FrwTerm")) ? -1 : reader.GetInt32(reader.GetOrdinal("FrwTerm")),
FrwColor = reader.IsDBNull(reader.GetOrdinal("FrwColor")) ? null : reader.GetString(reader.GetOrdinal("FrwColor")),
ifFrw = reader.GetBoolean(reader.GetOrdinal("ifFrw")),
SpreadType = reader.IsDBNull(reader.GetOrdinal("SpreadType")) ? -1 : reader.GetInt32(reader.GetOrdinal("SpreadType")),
SpreadSize = reader.IsDBNull(reader.GetOrdinal("SpreadSize")) ? -1 : reader.GetDouble(reader.GetOrdinal("SpreadSize")),
SpreadFamily = reader.IsDBNull(reader.GetOrdinal("SpreadFamily")) ? -1 : reader.GetInt32(reader.GetOrdinal("SpreadFamily"))
};
Calendar cal = new Calendar
{
Id = reader.IsDBNull(reader.GetOrdinal("calId")) ? -1 : reader.GetInt32(reader.GetOrdinal("calId")),
ClassName = reader.IsDBNull(reader.GetOrdinal("calClassName")) ? null : reader.GetString(reader.GetOrdinal("calClassName")),
MarketName = reader.IsDBNull(reader.GetOrdinal("calMarketName")) ? null : reader.GetString(reader.GetOrdinal("calMarketName"))
};
ycs.Calendar = cal;
ycDesc.Add(new YieldCurveData
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Family = reader.GetString(reader.GetOrdinal("Family")),
CurrencyId = reader.IsDBNull(reader.GetOrdinal("CurrencyId")) ? -1 : reader.GetInt32(reader.GetOrdinal("CurrencyId")),
settings = ycs,
entryPointList = null
});
}
}
return(ycDesc);
}
/// <summary>
/// Retrieves the list of all bonds and their data
/// </summary>
/// <param name="ctx">
/// The connection context
/// </param>
/// </returns>
public static Bond GetBond(long?idBond)
{
if (idBond != null && Repository.BondDic.ContainsKey(idBond.Value))
{
return(Repository.BondDic[idBond.Value]);
}
var ctx = new ConnectionContextMySQL();
return(GetBonds(ctx, idBond)[0]);
}
internal static void UpdateEntryPoint(ConnectionContextMySQL ctx, EntryPoint ep)
{
var command = SqlHelper.UpdateEntryPoint(ctx, ep);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{ }
}
internal static InflationRate GetInflationRate(long?idRate)
{
if (idRate != null && Repository.InflationRateDic.ContainsKey(idRate.Value))
{
return(Repository.InflationRateDic[idRate.Value]);
}
var ctx = new ConnectionContextMySQL();
return(GetInflationRates(ctx, idRate)[0]);
}
internal static MySqlCommand SelectInflationCurveEntryDataCommand(ConnectionContextMySQL ctx, long? idInflationCurve, DateTime? settlementDate)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
#if _NO_VIEWS_
@" SELECT
ice.InflationCurveId as InflationCurveId,
ice.Id as Id,
CASE WHEN ice.Type IS NOT NULL THEN(ice.Type) ELSE('') END as Type,
ice.RateId as RateId,
ice.DateStart as ValidDateStart,
ice.DateFinish as ValidDateEnd
FROM InflationcurveEntry ice
WHERE 1 = 1 "
);
#else
@" SELECT
yce.Id, yce.YieldCurveId, yce.Type, yce.Length, yce.TimeUnit, yce.RateId,
yceh.Date, yceh.Value
FROM vwYcEntryHistory yceh
JOIN vwYcEntry yce ON yceh.YcEntryId = yce.Id
WHERE 1 = 1 ");
#endif
if (idInflationCurve != null)
{
sb.Append(" AND ice.InflationCurveId = @idInflationCurve ");
command.Parameters.Add(new MySqlParameter(" @idInflationCurve ", MySqlDbType.Int32)).Value = idInflationCurve;
}
if (settlementDate != null)
{
#if _NO_VIEWS_
//String d = ((DateTime)settlementDate).ToString(" yyyy - MM - dd ");
sb.Append(@" AND yce.DateStart <= @date AND yce.DateFinish > @date ");
//string app = @" AND ice.DateStart <= '" + d + "' AND ice.DateFinish > '" + d + "' ";
//sb.Append(app);
#else
#endif
command.Parameters.Add(new MySqlParameter(" @date ", MySqlDbType.Date)).Value = settlementDate;
// command.Parameters.Add(new MySqlParameter(" @date ", MySqlDbType.Date)).Value = d;
}
sb.Append(" order by ice.InflationCurveId asc ");
command.CommandText = sb.ToString();
return command;
}
internal static void AddEntryPointHistory(ConnectionContextMySQL ctx, List <EntryPoint> epl)
{
foreach (var ep in epl)
{
var command = SqlHelper.AddEntryPointHistory(ctx, ep);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{ }
}
}
internal static List <Rate> GetRates(ConnectionContextMySQL ctx, long?idRate)
{
List <Rate> list = new List <Rate>();
var command = SqlHelper.SelectRatesCommand(ctx, idRate);
using (var reader = command.ExecuteReader())
{
//Repository.RateDic.Clear();
while (reader.Read())
{
Rate tmp = new Rate
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? null : reader.GetString(reader.GetOrdinal("Name")),
ClassName = reader.GetString(reader.GetOrdinal("ClassName")),
Type = reader.GetString(reader.GetOrdinal("Type")),
DataProviderId = reader.IsDBNull(reader.GetOrdinal("DataProviderId")) ? 0 : reader.GetInt32(reader.GetOrdinal("DataProviderId")),
DataReference = reader.IsDBNull(reader.GetOrdinal("DataReference")) ? null : reader.GetString(reader.GetOrdinal("DataReference")),
IdCcy = reader.IsDBNull(reader.GetOrdinal("CurrencyId")) ? 0 : reader.GetInt32(reader.GetOrdinal("CurrencyId")),
Duration = reader.IsDBNull(reader.GetOrdinal("Length")) ? 0 : reader.GetInt32(reader.GetOrdinal("Length")),
TimeUnit = reader.IsDBNull(reader.GetOrdinal("TimeUnit")) ? null : reader.GetString(reader.GetOrdinal("TimeUnit")),
Accuracy = reader.IsDBNull(reader.GetOrdinal("Accuracy")) ? 0 : reader.GetInt32(reader.GetOrdinal("Accuracy")),
Spread = reader.IsDBNull(reader.GetOrdinal("Spread")) ? 0 : reader.GetDouble(reader.GetOrdinal("Spread")),
SettlementDays = reader.IsDBNull(reader.GetOrdinal("SettlementDays")) ? 0 : reader.GetInt32(reader.GetOrdinal("SettlementDays")),
FixingPlace = reader.IsDBNull(reader.GetOrdinal("FixingPlace")) ? null : reader.GetString(reader.GetOrdinal("FixingPlace")), //??? temporary solution. fixing place in database is id whereas here we are looking for quantlibclassname of calendar
IdIndex = reader.IsDBNull(reader.GetOrdinal("IndexId")) ? 0 : reader.GetInt32(reader.GetOrdinal("IndexId")),
BasisId = reader.IsDBNull(reader.GetOrdinal("BasisId")) ? -1 : reader.GetInt32(reader.GetOrdinal("BasisId")),
Frequency = reader.IsDBNull(reader.GetOrdinal("Frequency")) ? null : reader.GetString(reader.GetOrdinal("Frequency")),
Compounding = reader.IsDBNull(reader.GetOrdinal("Compounding")) ? null : reader.GetString(reader.GetOrdinal("Compounding")),
IndexDuration = reader.IsDBNull(reader.GetOrdinal("IndexLength")) ? 0 : reader.GetInt32(reader.GetOrdinal("IndexLength")),
IndexTimeUnit = reader.IsDBNull(reader.GetOrdinal("IndexTimeUnit")) ? null : reader.GetString(reader.GetOrdinal("IndexTimeUnit")),
IndexName = reader.IsDBNull(reader.GetOrdinal("IndexName")) ? null : reader.GetString(reader.GetOrdinal("IndexName")),
ClassNameIndex = reader.IsDBNull(reader.GetOrdinal("IndexClassName")) ? null : reader.GetString(reader.GetOrdinal("IndexClassName")),
BasisIndexId = reader.IsDBNull(reader.GetOrdinal("IndexBasisId")) ? -1 : reader.GetInt32(reader.GetOrdinal("IndexBasisId")),
FrequencyIndex = reader.IsDBNull(reader.GetOrdinal("IndexFrequency")) ? null : reader.GetString(reader.GetOrdinal("IndexFrequency")),
CompoundingIndex = reader.IsDBNull(reader.GetOrdinal("IndexCompounding")) ? null : reader.GetString(reader.GetOrdinal("IndexCompounding")),
};
list.Add(tmp);
//Repository.RateDic[tmp.Id] = tmp;
}
}
return(list);
}
internal static MySqlCommand SelectBondsCommand(ConnectionContextMySQL ctx, long?idBond)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
#if _NO_VIEWS_
@"SELECT
b.Id,
b.Name,
b.Type,
b.DataProviderId,
b.DataReference,
b.CurrencyId,
-- b.DateGenerationId,
-- b.CouponFrequencyId,
b.Coupon,
-- b.CouponConventionId,
b.CouponBasisId,
b.Redemption,
b.Nominal,
-- b.CalendarId,
b.SettlementDays,
b.IssueDate,
b.MaturityDate,
-- b.YieldCurveId,
-- b.UserCreated,
-- b.IpCreated,
CASE WHEN c.Name IS NOT NULL THEN (c.Name) ELSE ('') END as DateGeneration,
CASE WHEN d.Name IS NOT NULL THEN (d.Name) ELSE ('') END as CouponFrequency,
CASE WHEN e.Name IS NOT NULL THEN (e.Name) ELSE ('') END as CouponConvention
-- CASE WHEN f.Name IS NOT NULL THEN (f.Name) ELSE ('') END as CouponBasis
FROM bond b
LEFT JOIN EnumDateGeneration c ON c.Id = b.DateGenerationId
LEFT JOIN EnumFrequency d ON d.Id = b.CouponFrequencyId
LEFT JOIN EnumBusinessDayConvention e ON e.Id = b.CouponConventionId
-- LEFT JOIN EnumBasis f ON f.Id = b.CouponBasisId
WHERE 1 = 1"
);
#else
@"SELECT * FROM vwbond");
#endif
if (idBond != null)
{
sb.Append(" AND b.Id = @idBond ");
command.Parameters.Add(new MySqlParameter("@idBond", MySqlDbType.Int32)).Value = idBond;
}
command.CommandText = sb.ToString();
return(command);
}
internal static MySqlCommand SelectInflationCurveDataCommand(ConnectionContextMySQL ctx, long? idIC/*, long? idYcFamily*/)
{
var command = new MySqlCommand(null, ctx.connection, ctx.transaction);
#if _NO_VIEWS_
var sb = new StringBuilder(
@" SELECT
yc.Id as Id,
CASE WHEN c.Name IS NOT NULL THEN(c.Name) ELSE('') END as ZcCompounding,
CASE WHEN f.Name IS NOT NULL THEN(f.Name) ELSE('') END as ZcFrequency,
yc.ifZC as ifZc,
yc.ZcColor as ZcColor,
yc.ZcBasisId as ZcBasisId,
CASE WHEN yc.InflationIndex IS NOT NULL THEN(yc.InflationIndex) ELSE(0) END as InflationIndex,
yc.ifIndex as ifIndex,
yc.IndexColor as IndexColor,
yc.Name as Name,
CASE WHEN ycf.Name IS NOT NULL THEN(ycf.Name) ELSE('') END as Family,
CASE WHEN ycf.CurrencyId IS NOT NULL THEN(ccy.Id) ELSE(0) END as CurrencyId
FROM InflationCurve yc
LEFT JOIN EnumCompounding c ON c.Id = yc.ZcCompoundingId
LEFT JOIN EnumFrequency f ON f.Id = yc.ZcFrequencyId
LEFT JOIN InflationFamily ycf ON ycf.Id = yc.FamilyId
LEFT JOIN Currency ccy ON ccy.Id = ycf.CurrencyId
WHERE 1 = 1 ");
#else
var sb = new StringBuilder(" SELECT * FROM vwInflationCurve WHERE 1 = 1 ");
#endif
if (idIC != null)
{
sb.Append(" AND Id = @idIC ");
command.Parameters.Add(new MySqlParameter(" @idIC ", MySqlDbType.Int32)).Value = idIC;
}
/*
if (idYcFamily != null)
{
sb.Append(" AND FamilyId = @idYcFamily ");
command.Parameters.Add(new MySqlParameter(" @idYcFamily ", MySqlDbType.Int32)).Value = (int)idYcFamily;
}
*/
command.CommandText = sb.ToString();
return command;
}
public static List <EntryPoint> GetYieldCurveEntryPoint(long?idYc, DateTime?settlementDate)
{
#if _LINQXML_
return(DataHelperLinqXml.GetYieldCurveEntryData(idYc, settlementDate));
#elif _SQLITE_
using (var ctx = new ConnectionContextSQLite())
{
return(DataHelperSQLite.GetYieldCurveEntryPoint(ctx, idYc, settlementDate));
}
#else // _MYSQL_
using (var ctx = new ConnectionContextMySQL())
{
return(DataHelper.GetYieldCurveEntryData(ctx, idYc, settlementDate));
}
#endif
}
public static List <YieldCurveData> GetYieldCurveData(long?idYc)
{
#if _LINQXML_
return(DataHelperLinqXml.GetYieldCurveData(idYc));
#elif _SQLITE_
using (var ctx = new ConnectionContextSQLite())
{
return(DataHelperSQLite.GetYieldCurveData(ctx, idYc));
}
#else // _MYSQL_
using (var ctx = new ConnectionContextMySQL())
{
return(DataHelper.GetYieldCurveData(ctx, idYc));
}
#endif
}
public static List <YieldCurveFamily> GetYieldCurveFamily(long?idYcFamily, long?idCurrency)
{
#if _LINQXML_
return(DataHelperLinqXml.GetYieldCurveFamily(idYcFamily, idCurrency));
#elif _SQLITE_
using (var ctx = new ConnectionContextSQLite())
{
return(DataHelperSQLite.GetYieldCurveFamily(ctx, idYcFamily, idCurrency));
}
#else // _MYSQL
using (var ctx = new ConnectionContextMySQL())
{
return(DataHelper.GetYieldCurveFamily(ctx, idYcFamily, idCurrency));
}
#endif
}
