internal static SQLiteCommand SelectCalendarsCommand(ConnectionContextSQLite ctx)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
#if _NO_VIEWS_
@"SELECT
m.Id,
m.Name,
m.ClassName,
m.MarketName
FROM calendar m
WHERE 1 = 1 AND m.UserCreated = @user" );
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter("@user", DbType.Int32);
p.Value = 1; //TODO (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
#else
@"SELECT m.Id, m.Name, m.ClassName, m.MarketName
FROM calendar m
WHERE 1 = 1");
#endif
command.CommandText = sb.ToString();
return(command);
}
internal static List <ExchangeRate> GetXRates(ConnectionContextSQLite ctx, long?idXRate)
{
List <ExchangeRate> list = new List <ExchangeRate>();
var command = SqlHelperSQLite.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);
}
internal static SQLiteCommand LoginUser(ConnectionContextSQLite ctx, string name, string pass)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
SQLiteParameter p = new SQLiteParameter("@name", DbType.String);
p.Value = name;
command.Parameters.Add(p);
p = new SQLiteParameter("@pass", DbType.String);
p.Value = pass;
command.Parameters.Add(p);
// check if exists, check guid, set Enabled if ok
var sb = new StringBuilder(
@"SELECT
Id, Name, Pass, Guid, Status
FROM Users
WHERE Name = @name AND Pass = @pass"
);
/*
* if (user.g != Guid.Empty)
* {
* sb.Append(" AND Guid = @g");
* p = new SQLiteParameter("@g", DbType.String);
* p.Value = user.g.ToString("B");
* command.Parameters.Add(p);
* }
*/
command.CommandText = sb.ToString();
return(command);
}
internal static List <DayCounter> GetDayCounter(ConnectionContextSQLite ctx, long?idDC, string className)
{
List <DayCounter> list = new List <DayCounter>();
var command = SqlHelperSQLite.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 List <InflationIndex> GetInflationIndex(ConnectionContextSQLite ctx, long?idIndex)
{
List <InflationIndex> list = new List <InflationIndex>();
var command = SqlHelperSQLite.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 SQLiteCommand SelectInflationCurveFamilyCommand(ConnectionContextSQLite ctx, long? idInflationFamily, long? idCcy)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
#if _NO_VIEWS_
var sb = new StringBuilder(" SELECT * FROM InflationFamily WHERE 1 = 1 AND UserCreated = @user ");
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter(" @user ", DbType.Int32);
p.Value = 1; //TODO (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
#else
var sb = new StringBuilder(" SELECT * FROM vwInflationFamily WHERE 1 = 1 ");
#endif
if (idInflationFamily != null)
{
sb.Append(" AND Id = @id ");
p = new SQLiteParameter(" @id ", DbType.Int32);
p.Value = idInflationFamily;
command.Parameters.Add(p);
}
if (idCcy != null)
{
sb.Append(" AND CurrencyId = @idCcy ");
p = new SQLiteParameter(" @idCcy ", DbType.Int32);
p.Value = idCcy;
command.Parameters.Add(p);
}
command.CommandText = sb.ToString();
return command;
}
internal static List <Currency> GetCurrency(ConnectionContextSQLite ctx, long?idCurrency)
{
List <Currency> list = new List <Currency>();
var command = SqlHelperSQLite.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);
}
//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(ConnectionContextSQLite ctx, DateTime?settlementDate)
{
List <RateHistory> RateHistoryList = new List <RateHistory>();
var command = SqlHelperSQLite.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 SQLiteCommand AddEntryPointHistory(ConnectionContextSQLite ctx, EntryPoint ep)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
@"INSERT
into YcEntryHistory
(YcEntryId, Value, Date, UserCreated, IpCreated)
values
(@epId, @epVal, @epDate, @user, '127.0.0.1')"
);
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter("@user", DbType.Int32);
p.Value = (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
p = new SQLiteParameter("@epId", DbType.Int32);
p.Value = ep.Id;
command.Parameters.Add(p);
p = new SQLiteParameter("@epVal", DbType.Double);
p.Value = ep.epValue.Value;
command.Parameters.Add(p);
p = new SQLiteParameter("@epDate", DbType.DateTime);
p.Value = ep.epValue.Date;
command.Parameters.Add(p);
command.CommandText = sb.ToString();
return(command);
}
internal static SQLiteCommand GetAllEntryPoints(ConnectionContextSQLite ctx, bool isDemo)
{
var command = new SQLiteCommand(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 AND UserCreated = @user"
);
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter("@user", DbType.Int32);
p.Value = (isDemo || ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
command.CommandText = sb.ToString();
return(command);
}
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
}
internal static SQLiteCommand CreateUser(ConnectionContextSQLite ctx, ref UserAccounts.FCUser user)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
SQLiteParameter p = new SQLiteParameter("@name", DbType.String);
p.Value = user.name;
command.Parameters.Add(p);
p = new SQLiteParameter("@pass", DbType.String);
p.Value = user.pass;
command.Parameters.Add(p);
p = new SQLiteParameter("@guid", DbType.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 SQLiteCommand SelectBondsCommand(ConnectionContextSQLite ctx, long?idBond)
{
var command = new SQLiteCommand(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 AND b.UserCreated = @user"
);
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter("@user", DbType.Int32);
p.Value = 1; //TODO (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
#else
@"SELECT * FROM vwbond");
#endif
if (idBond != null)
{
sb.Append(" AND b.Id = @idBond ");
p = new SQLiteParameter("@idBond", DbType.Int32);
p.Value = idBond;
command.Parameters.Add(p);
}
command.CommandText = sb.ToString();
return(command);
}
internal static SQLiteCommand SelectDayCounterCommand(ConnectionContextSQLite ctx, long?idDC, string className)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder("SELECT * FROM DayCounter WHERE 1 = 1");
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter("@user", DbType.Int32);
p.Value = 1; //TODO (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
if (idDC != null)
{
sb.Append(" AND Id = @id");
p = new SQLiteParameter("@id", DbType.Int32);
p.Value = idDC.Value;
command.Parameters.Add(p);
}
if (!string.IsNullOrEmpty(className))
{
sb.Append(" AND ClassName = @cn");
p = new SQLiteParameter("@cn", DbType.String);
p.Value = className;
command.Parameters.Add(p);
}
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(ConnectionContextSQLite ctx,
long?idYc)
{
List <YieldCurveData> ycDesc = new List <YieldCurveData>();
var command = SqlHelperSQLite.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.GetInt32(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,
discountPointList = null
});
}
}
return(ycDesc);
}
// instruments used for callibration and valid start date and valid end date
internal static List <InflationCurveEntryData> GetInflationCurveEntryData(ConnectionContextSQLite ctx, long?idIc, DateTime?settlementDate)
{
List <InflationCurveEntryData> EntryDataList = new List <InflationCurveEntryData>();
var command = SqlHelperSQLite.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);
}
/// <summary>
/// Retrieves the list of all rates and their data (only for custom rates, for Quantlib rates -Class names)
/// </summary>
/// <param name="ctx">
/// The connection context
/// </param>
/// </returns>
public static Rate GetRate(long?idRate, ConnectionContextSQLite ctx)
{
if (idRate != null && Repository.RateDic.ContainsKey(idRate.Value))
{
return(Repository.RateDic[idRate.Value]);
}
//var ctx = new ConnectionContextSQLite();
return(GetRates(ctx, idRate)[0]);
}
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 ConnectionContextSQLite();
return(GetInflationBonds(ctx, idInflationBond)[0]);
}
/// <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, ConnectionContextSQLite ctx)
{
if (idBond != null && Repository.BondDic.ContainsKey(idBond.Value))
{
return(Repository.BondDic[idBond.Value]);
}
//var ctx = new ConnectionContextSQLite();
return(GetBonds(ctx, idBond)[0]);
}
internal static SQLiteCommand SelectInflationCurveDataCommand(ConnectionContextSQLite ctx, long? idIC/*, long? idYcFamily*/)
{
var command = new SQLiteCommand(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 AND yc.UserCreated = @user ");
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter(" @user ", DbType.Int32);
p.Value = 1; //TODO (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
#else
var sb = new StringBuilder(" SELECT * FROM vwInflationCurve WHERE 1 = 1 ");
#endif
if (idIC != null)
{
sb.Append(" AND Id = @idIC ");
p = new SQLiteParameter(" @idIC ", DbType.Int32);
p.Value = idIC;
command.Parameters.Add(p);
}
/*
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;
}
internal static InflationRate GetInflationRate(long?idRate)
{
if (idRate != null && Repository.InflationRateDic.ContainsKey(idRate.Value))
{
return(Repository.InflationRateDic[idRate.Value]);
}
var ctx = new ConnectionContextSQLite();
return(GetInflationRates(ctx, idRate)[0]);
}
internal static void UpdateEntryPoint(ConnectionContextSQLite ctx, EntryPoint ep)
{
var command = SqlHelperSQLite.UpdateEntryPoint(ctx, ep);
try
{
command.ExecuteNonQuery();
command.CommandText = "Commit";
command.ExecuteNonQuery();
}
catch (Exception ex)
{ }
}
internal static SQLiteCommand SelectXRatesCommand(ConnectionContextSQLite ctx, long?idXRate)
{
var command = new SQLiteCommand(null, ctx.connection, ctx.transaction);
var sb = new StringBuilder(
#if _NO_VIEWS_
@"SELECT
x.Id as Id,
x.Name as Name,
x.ClassName as ClassName,
x.DataProviderId as DataProviderId,
x.DataReference as DataReference,
x.SettlementDays as SettlementDays,
x.FixingPlace as FixingPlace,
x.PrimaryCurrency as PrimaryCurrencyCode,
x.SecondaryCurrency as SecondaryCurrencyCode
FROM ExchangeRate x
WHERE 1 = 1 AND x.UserCreated = @user"
);
// 1 == Demo User
SQLiteParameter p = new SQLiteParameter("@user", DbType.Int32);
p.Value = 1; //TODO (ctx.user == null ? 1 : ctx.user.id);
command.Parameters.Add(p);
#else
x.Id as Id,
x.Name as Name,
x.ClassName as ClassName,
x.DataProviderId as DataProviderId,
x.DataReference as DataReference,
x.SettlementDays as SettlementDays,
x.FixingPlace as FixingPlace,
x.PrimaryCurrency as PrimaryCurrencyCode,
x.SecondaryCurrency as SecondaryCurrencyCode
FROM vwExchangeRate x
WHERE 1 = 1 ");
#endif
if (idXRate != null)
{
sb.Append(" AND r.Id = @idXRate ");
p = new SQLiteParameter(" @idXRate ", DbType.Int32);
p.Value = idXRate;
command.Parameters.Add(p);
}
command.CommandText = sb.ToString();
return command;
}
internal static List <Rate> GetRates(ConnectionContextSQLite ctx, long?idRate)
{
List <Rate> list = new List <Rate>();
var command = SqlHelperSQLite.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);
}
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
}
public static List <InflationRate> GetInflationRates()
{
#if _LINQXML_
// return DataHelperLinqXml.GetInflationRates(null);
#elif _SQLITE_
using (var ctx = new ConnectionContextSQLite())
{
return(DataHelperSQLite.GetInflationRates(ctx, null));
}
#else // _MYSQL_
using (var ctx = new ConnectionContextMySQL())
{
return(DataHelper.GetInflationRates(ctx, null));
}
#endif
}
public static List <RateHistory> GetRateHistory(DateTime?settlementDate)
{
#if _LINQXML_
// return DataHelperLinqXml.GetRateHistory(settlementDate);
#elif _SQLITE_
using (var ctx = new ConnectionContextSQLite())
{
return(DataHelperSQLite.GetRateHistory(ctx, settlementDate));
}
#else // _MYSQL_
using (var ctx = new ConnectionContextMySQL())
{
return(DataHelper.GetRateHistory(ctx, settlementDate));
}
#endif
}
public static List <InflationIndex> GetInflationIndex(long?idIndex)
{
#if _LINQXML_
// return DataHelperLinqXml.GetInflationIndex(idIndex);
#elif _SQLITE_
using (var ctx = new ConnectionContextSQLite())
{
return(DataHelperSQLite.GetInflationIndex(ctx, idIndex));
}
#else // _MYSQL_
using (var ctx = new ConnectionContextMySQL())
{
return(DataHelper.GetInflationIndex(ctx, idIndex));
}
#endif
}
