// *****************************************************************
// **** Constructors ****
// *****************************************************************
//
//
//
#endregion//Constructors
#region Public Methods
// *****************************************************************
// **** Public Methods ****
// *****************************************************************
public override string GetQuery(DatabaseInfo dataBase)
{
TableInfo.InstrumentsTableInfo instrumentTable = dataBase.Instruments;
TableInfo.ExchangesTableInfo exchangeTable = dataBase.Exchanges;
// Create a instrument expiry code.
string expiryCode;
if (!UV.Lib.Utilities.QTMath.TryConvertMonthYearToCodeY(InstrumentName.SeriesName, out expiryCode))
{
return(string.Empty);
}
// TODO: Create the fields we want
string desiredFields = "*";//string.Format("{0}", instrumentTable.InstrumentID );
// Create the query.
StringBuilder query = new StringBuilder();
query.AppendFormat("SELECT {0} FROM {1} ", desiredFields, dataBase.Instruments.TableNameFull);
query.AppendFormat("WHERE {0} in (select {1} from {2} where {3} =\'{4}\') and {5} =\'{6}\' and {7} =\'{8}\'",
instrumentTable.ExchangeID, // 0
instrumentTable.ExchangeID, // 1
exchangeTable.TableNameFull, // 2
exchangeTable.ExchangeNameTT, // 3
InstrumentName.Product.Exchange, // 4
instrumentTable.Product, // 5
InstrumentName.Product.ProductName, // 6
instrumentTable.ExpirySymbol, // 7
expiryCode); // 8
query.Append(";");
return(query.ToString());
}// GetQuery();
//
protected string GetQueryForData(DatabaseInfo dataBase)
{
TableInfo.InstrumentsTableInfo instrumentTable = dataBase.Instruments;
TableInfo.ExchangesTableInfo exchangeTable = dataBase.Exchanges;
TableInfo.BarsTableInfo barsTable = dataBase.Bars;
// Create a instrument expiry code.
string expiryCode;
if (!UV.Lib.Utilities.QTMath.TryConvertMonthYearToCodeY(InstrumentName.SeriesName, out expiryCode))
{
return(string.Empty);
}
// Create sub selection string to get instr ID from InstrumentName.
StringBuilder subQuery = new StringBuilder();
int instrSqlId = m_InstrumentQuery.Results[0].InstrumentId;
subQuery.AppendFormat("{0}", instrSqlId);
// Create the final query.
string desiredFields = "*";
StringBuilder query = new StringBuilder();
query.AppendFormat("SELECT {0} FROM {1}", desiredFields, barsTable.TableNameFull);
query.AppendFormat(" WHERE {0} = ({1})", barsTable.InstrumentID, subQuery);
if (this.StartDate > DateTime.MinValue)
{
int timestamp = (int)QTMath.DateTimeToEpoch(this.StartDate);
string s = timestamp.ToString();
query.AppendFormat(" AND {0} >= {1}", barsTable.TimeStamp, s);
}
if (this.EndDate < DateTime.MaxValue)
{
int timestamp = (int)QTMath.DateTimeToEpoch(this.EndDate);
string s = timestamp.ToString();
query.AppendFormat(" AND {0} <= {1}", barsTable.TimeStamp, s);
}
query.AppendFormat(" AND {0} = 1", barsTable.SessionCode); // currently we only want sessionCode = 1 for when products are trading
query.AppendFormat(" ORDER BY {0}", barsTable.TimeStamp);
if (this.StartDate == DateTime.MinValue)
{ // since our date time is unnasigned, lets reverse the order of our timestamps for this query so we get the proper number of rows
query.AppendFormat(" DESC");
m_IsReverseOrder = true;
}
if (this.MaxRows > 0)
{
query.AppendFormat(" LIMIT {0}", this.MaxRows);
}
query.Append(";");
// Exit
return(query.ToString());
}// GetQuery();
// *****************************************************************
// **** Constructors ****
// *****************************************************************
//
//
//
#endregion//Constructors
#region Public Methods
// *****************************************************************
// **** Public Methods ****
// *****************************************************************
public override string GetQuery(DatabaseInfo dataBase)
{
TableInfo.InstrumentsTableInfo instrumentTable = dataBase.Instruments;
TableInfo.ExchangesTableInfo exchangeTable = dataBase.Exchanges;
// TODO: Create the fields we want
string desiredFields = "*";//string.Format("{0}", instrumentTable.InstrumentID );
// Create the query.
StringBuilder query = new StringBuilder();
query.AppendFormat("SELECT {0} FROM {1} ", desiredFields, dataBase.Instruments.TableNameFull);
if (string.IsNullOrEmpty(this.InstrumentName.SeriesName) == false)
{ // User has supplied a specific InstrumentName
// Create a instrument expiry code.
string expiryCode;
if (!UV.Lib.Utilities.QTMath.TryConvertMonthYearToCodeY(this.InstrumentName.SeriesName, out expiryCode))
{
return(string.Empty);
}
// NOTE : Currently this has to be future product type! //
query.AppendFormat("WHERE {0} in (select {1} from {2} where {3} =\'{4}\') and {5} =\'{6}\' and {7} =\'{8}\' and {9} = \'{10}\'",
instrumentTable.ExchangeID, // 0
instrumentTable.ExchangeID, // 1
exchangeTable.TableNameFull, // 2
exchangeTable.ExchangeNameTT, // 3
InstrumentName.Product.Exchange, // 4
instrumentTable.Product, // 5
InstrumentName.Product.ProductName, // 6
instrumentTable.ExpirySymbol, // 7
expiryCode, // 8
instrumentTable.ProdType, //9
InstrumentName.Product.Type.ToString().ToLower()); // 10
}
else
{ // Sometimes, the instrument name does not contain series name. This means the user wants to get all the instruments for that product.
query.AppendFormat("WHERE {0} in (select {1} from {2} where {3} =\'{4}\') and {5} =\'{6}\'",
instrumentTable.ExchangeID, // 0
instrumentTable.ExchangeID, // 1
exchangeTable.TableNameFull, // 2
exchangeTable.ExchangeNameTT, // 3
InstrumentName.Product.Exchange, // 4
instrumentTable.Product, // 5
InstrumentName.Product.ProductName); // 6
}
query.Append(";");
return(query.ToString());
}// GetQuery();
}// GetQuery();
//
//
//
//
/// <summary>
///
/// </summary>
/// <param name="dbInfo"></param>
/// <param name="values"></param>
/// <param name="fieldNames"></param>
public override QueryStatus AcceptData(DatabaseInfo dbInfo, List <object> values, List <string> fieldNames)
{
TableInfo.InstrumentsTableInfo table = dbInfo.Instruments;
// dummy vars
//int n = 0;
ProductTypes type;
InstrumentInfoItem item = null;
//List<InstrumentInfoItem> itemList = new List<InstrumentInfoItem>();
DateTime lastTradeDate = DateTime.MaxValue;
//
// Extract data
//
int ptr = 0;
while (ptr < values.Count)
{
int fieldPtr = ptr % fieldNames.Count;
if (fieldPtr == 0)
{ // We are starting to load a new object.
item = new InstrumentInfoItem(); // now create the next item to write to.
this.Results.Add(item); // save it into our list.
}
//
// Fill elements of item.
//
if (values[ptr] != null)
{
string key = fieldNames[fieldPtr];
// Identifiers
if (key == table.InstrumentID)
{
item.InstrumentId = (int)values[ptr];
}
else if (key == table.ExchangeID)
{
item.ExchangeId = (int)values[ptr];
}
else if (key == table.ProdFamilyID)
{
item.ProdFamilyId = (int)values[ptr];
}
else if (key == table.ExpirySymbol)
{
item.Expiry = values[ptr].ToString();
}
else if (key == table.Product)
{
item.ProductName = values[ptr].ToString();
}
else if (key == table.ProdType)
{
if (Enum.TryParse <ProductTypes>(values[ptr].ToString(), true, out type))
{
item.Type = type;
}
else if (values[ptr].ToString().ToUpper().Contains("SPREAD"))
{
item.Type = ProductTypes.Spread;
}
}
else if (key == table.SpreadComposition)
{
item.SpreadComposition = values[ptr].ToString();
}
// specs
else if (key == table.LastTradeDate && DateTime.TryParse(values[ptr].ToString(), out lastTradeDate))
{
item.LastTradeDate = lastTradeDate;
}
else if (key == table.Currency)
{
item.Currency = values[ptr].ToString();
}
else if (key == table.unitTT)
{
item.UnitValue = (double)values[ptr];
}
else if (key == table.tickTT)
{
item.TickValue = (double)values[ptr];
}
else if (key == table.calendarTickTT)
{
item.CalendarTickValue = (double)values[ptr];
}
else if (key == table.HedgeOptions)
{
item.HedgeOptions = values[ptr].ToString();
}
else if (key == table.InstrumentName)
{
item.InstrumentNameDatabase = values[ptr].ToString();
}
else if (key == table.InstrumentNameTT)
{
item.InstrumentNameTT = values[ptr].ToString();
}
}
ptr++;
}//wend ptr
// Exit;
return(QueryStatus.Completed);
}//
