[Quantproject-developers] QuantProject/b2_DataAccess/Tables Bars.cs, 1.4, 1.5
Brought to you by:
glauco_1
Menu
▾
▴
[Quantproject-developers] QuantProject/b2_DataAccess/Tables Bars.cs, 1.4, 1.5
|
From: Glauco S. <gla...@us...> - 2009-01-07 23:23:25
|
Update of /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv23410/b2_DataAccess/Tables Modified Files: Bars.cs Log Message: - the method public static void SetDataTable() has been overloaded to fill the DataTable with daily bars selected by a list of Time(s) - all the class' code has been reindentent Index: Bars.cs =================================================================== RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables/Bars.cs,v retrieving revision 1.4 retrieving revision 1.5 diff -C2 -d -r1.4 -r1.5 *** Bars.cs 4 Jan 2009 17:49:26 -0000 1.4 --- Bars.cs 7 Jan 2009 23:23:07 -0000 1.5 *************** *** 3,7 **** Bars.cs ! Copyright (C) 2008 Marco Milletti --- 3,7 ---- Bars.cs ! Copyright (C) 2008 Marco Milletti *************** *** 19,26 **** along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. ! */ using System; using System.Collections; using System.Data; using System.Text; --- 19,27 ---- along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. ! */ using System; using System.Collections; + using System.Collections.Generic; using System.Data; using System.Text; *************** *** 38,45 **** public class Bars { ! // these static fields provide field name in the database table // They are intended to be used through intellisense when necessary public static string TickerFieldName = "baTicker"; // Ticker cannot be simply used because ! public static string Exchange = "baExchange"; public static string DateTimeForOpen = "baDateTimeForOpen"; public static string IntervalFrameInSeconds = "baInterval"; --- 39,46 ---- public class Bars { ! // these static fields provide field name in the database table // They are intended to be used through intellisense when necessary public static string TickerFieldName = "baTicker"; // Ticker cannot be simply used because ! public static string Exchange = "baExchange"; public static string DateTimeForOpen = "baDateTimeForOpen"; public static string IntervalFrameInSeconds = "baInterval"; *************** *** 48,53 **** public static string Low = "baLow"; public static string Close = "baClose"; ! public static string Volume = "baVolume"; ! public Bars( string ticker) { --- 49,54 ---- public static string Low = "baLow"; public static string Close = "baClose"; ! public static string Volume = "baVolume"; ! public Bars( string ticker) { *************** *** 69,73 **** DataTable dataTable = SqlExecutor.GetDataTable( "select min(" + Bars.DateTimeForOpen + ") as minDate from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + "group by " + Bars.TickerFieldName + ")" ); --- 70,74 ---- DataTable dataTable = SqlExecutor.GetDataTable( "select min(" + Bars.DateTimeForOpen + ") as minDate from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + "group by " + Bars.TickerFieldName + ")" ); *************** *** 83,219 **** { DataTable dataTable = SqlExecutor.GetDataTable( ! "select * from bars where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + "order by " + Bars.DateTimeForOpen + " DESC"); return (DateTime)(dataTable.Rows[0][ Bars.DateTimeForOpen ]); } ! /// <summary> ! /// Returns the number of Bars for the given ticker ! /// </summary> ! /// <param name="ticker">ticker for which the number of bars has to be returned</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static int GetNumberOfBars( string ticker, int intervalFrameInSeconds ) ! { ! DataTable dataTable = SqlExecutor.GetDataTable( ! "select * from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " ); ! return dataTable.Rows.Count; ! } ! /// <summary> ! /// Returns the number of bars at which the given ticker has been effectively traded ! /// (volume > 0) ! /// </summary> ! /// <param name="ticker">ticker for which the number of bars has to be returned</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static int GetNumberOfBarsWithEffectiveTrades( string ticker, DateTime firstDateTime, ! DateTime lastDateTime, int intervalFrameInSeconds) ! { ! DataTable dataTable = SqlExecutor.GetDataTable( ! "select * from bars " + ! "WHERE " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + ! "and " + Bars.Volume + ">0" + " and " + Bars.DateTimeForOpen + " BETWEEN " + SQLBuilder.GetDateConstant(firstDateTime) + " " + ! "and " + SQLBuilder.GetDateConstant(lastDateTime) ); ! return dataTable.Rows.Count; ! } ! ! /// <summary> ! /// Returns the close for the given ticker at the specified date ! /// time ! /// </summary> ! /// <param name="ticker">ticker for which the close has to be returned</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static float GetClose( string ticker, DateTime dateTime, int intervalFrameInSeconds ) ! { ! DataTable dataTable = SqlExecutor.GetDataTable( ! "select " + Bars.Close + " from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + ! "and " + Bars.DateTimeForOpen + "=" + SQLBuilder.GetDateConstant(dateTime) ); ! return (float)dataTable.Rows[0][0]; ! } ! /// <summary> ! /// Returns the open for the given ticker at the specified date ! /// time ! /// </summary> ! /// <param name="ticker">ticker for which the raw open has to be returned</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static float GetOpen( string ticker, DateTime dateTime, int intervalFrameInSeconds ) ! { ! DataTable dataTable = SqlExecutor.GetDataTable( ! "select " + Bars.Open + " from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + ! "and " + Bars.DateTimeForOpen + "=" + SQLBuilder.GetDateConstant(dateTime) ); ! return (float)dataTable.Rows[0][0]; ! } ! ! ! // /// <summary> ! // /// It provides deletion of the quote from the table "quotes" for ! // /// the given ticker for a specified date ! // /// </summary> ! // public static void Delete( string ticker, DateTime dateOfTheQuoteToBeDeleted ) ! // { ! // try ! // { ! // SqlExecutor.ExecuteNonQuery("DELETE * FROM quotes " + ! // "WHERE quTicker ='" + ! // ticker + "' AND quDate =" + ! // SQLBuilder.GetDateConstant(dateOfTheQuoteToBeDeleted)); ! // } ! // catch(Exception ex) ! // { ! // string notUsed = ex.ToString(); ! // } ! // } ! // /// <summary> ! // /// It provides deletion of the quote from the table "quotes" for ! // /// the given ticker for the specified interval ! // /// </summary> ! // public static void Delete( string ticker, DateTime fromDate, ! // DateTime toDate) ! // { ! // try ! // { ! // SqlExecutor.ExecuteNonQuery("DELETE * FROM quotes " + ! // "WHERE quTicker ='" + ! // ticker + "' AND quDate >=" + ! // SQLBuilder.GetDateConstant(fromDate) + " " + ! // "AND quDate<=" + SQLBuilder.GetDateConstant(toDate)); ! // } ! // catch(Exception ex) ! // { ! // string notUsed = ex.ToString(); ! // } ! // } ! // /// <summary> ! // /// It provides addition of the given quote's values into table "quotes" ! // /// </summary> ! // public static void Add( string ticker, DateTime date, double open, ! // double high, double low, double close, ! // double volume, double adjustedClose) ! // { ! // try ! // { ! // SqlExecutor.ExecuteNonQuery("INSERT INTO quotes(quTicker, quDate, quOpen, " + ! // "quHigh, quLow, quClose, quVolume, quAdjustedClose) " + ! // "VALUES('" + ticker + "', " + SQLBuilder.GetDateConstant(date) + ", " + ! // open + ", " + high + ", " + low + ", " + close + ", " + ! // volume + ", " + adjustedClose + ")"); ! // } ! // catch(Exception ex) ! // { ! // string notUsed = ex.ToString(); ! // } ! // } ! ! /// <summary> /// returns the bars DataTable for the given ticker /// </summary> --- 84,220 ---- { DataTable dataTable = SqlExecutor.GetDataTable( ! "select * from bars where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + "order by " + Bars.DateTimeForOpen + " DESC"); return (DateTime)(dataTable.Rows[0][ Bars.DateTimeForOpen ]); } ! /// <summary> ! /// Returns the number of Bars for the given ticker ! /// </summary> ! /// <param name="ticker">ticker for which the number of bars has to be returned</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static int GetNumberOfBars( string ticker, int intervalFrameInSeconds ) ! { ! DataTable dataTable = SqlExecutor.GetDataTable( ! "select * from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " ); ! return dataTable.Rows.Count; ! } ! /// <summary> ! /// Returns the number of bars at which the given ticker has been effectively traded ! /// (volume > 0) ! /// </summary> ! /// <param name="ticker">ticker for which the number of bars has to be returned</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static int GetNumberOfBarsWithEffectiveTrades( string ticker, DateTime firstDateTime, ! DateTime lastDateTime, int intervalFrameInSeconds) ! { ! DataTable dataTable = SqlExecutor.GetDataTable( ! "select * from bars " + ! "WHERE " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + ! "and " + Bars.Volume + ">0" + " and " + Bars.DateTimeForOpen + " BETWEEN " + SQLBuilder.GetDateConstant(firstDateTime) + " " + ! "and " + SQLBuilder.GetDateConstant(lastDateTime) ); ! return dataTable.Rows.Count; ! } ! ! /// <summary> ! /// Returns the close for the given ticker at the specified date ! /// time ! /// </summary> ! /// <param name="ticker">ticker for which the close has to be returned</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static float GetClose( string ticker, DateTime dateTime, int intervalFrameInSeconds ) ! { ! DataTable dataTable = SqlExecutor.GetDataTable( ! "select " + Bars.Close + " from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + ! "and " + Bars.DateTimeForOpen + "=" + SQLBuilder.GetDateConstant(dateTime) ); ! return (float)dataTable.Rows[0][0]; ! } ! /// <summary> ! /// Returns the open for the given ticker at the specified date ! /// time ! /// </summary> ! /// <param name="ticker">ticker for which the raw open has to be returned</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static float GetOpen( string ticker, DateTime dateTime, int intervalFrameInSeconds ) ! { ! DataTable dataTable = SqlExecutor.GetDataTable( ! "select " + Bars.Open + " from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + ! "and " + Bars.DateTimeForOpen + "=" + SQLBuilder.GetDateConstant(dateTime) ); ! return (float)dataTable.Rows[0][0]; ! } ! ! ! // /// <summary> ! // /// It provides deletion of the quote from the table "quotes" for ! // /// the given ticker for a specified date ! // /// </summary> ! // public static void Delete( string ticker, DateTime dateOfTheQuoteToBeDeleted ) ! // { ! // try ! // { ! // SqlExecutor.ExecuteNonQuery("DELETE * FROM quotes " + ! // "WHERE quTicker ='" + ! // ticker + "' AND quDate =" + ! // SQLBuilder.GetDateConstant(dateOfTheQuoteToBeDeleted)); ! // } ! // catch(Exception ex) ! // { ! // string notUsed = ex.ToString(); ! // } ! // } ! // /// <summary> ! // /// It provides deletion of the quote from the table "quotes" for ! // /// the given ticker for the specified interval ! // /// </summary> ! // public static void Delete( string ticker, DateTime fromDate, ! // DateTime toDate) ! // { ! // try ! // { ! // SqlExecutor.ExecuteNonQuery("DELETE * FROM quotes " + ! // "WHERE quTicker ='" + ! // ticker + "' AND quDate >=" + ! // SQLBuilder.GetDateConstant(fromDate) + " " + ! // "AND quDate<=" + SQLBuilder.GetDateConstant(toDate)); ! // } ! // catch(Exception ex) ! // { ! // string notUsed = ex.ToString(); ! // } ! // } ! // /// <summary> ! // /// It provides addition of the given quote's values into table "quotes" ! // /// </summary> ! // public static void Add( string ticker, DateTime date, double open, ! // double high, double low, double close, ! // double volume, double adjustedClose) ! // { ! // try ! // { ! // SqlExecutor.ExecuteNonQuery("INSERT INTO quotes(quTicker, quDate, quOpen, " + ! // "quHigh, quLow, quClose, quVolume, quAdjustedClose) " + ! // "VALUES('" + ticker + "', " + SQLBuilder.GetDateConstant(date) + ", " + ! // open + ", " + high + ", " + low + ", " + close + ", " + ! // volume + ", " + adjustedClose + ")"); ! // } ! // catch(Exception ex) ! // { ! // string notUsed = ex.ToString(); ! // } ! // } ! ! /// <summary> /// returns the bars DataTable for the given ticker /// </summary> *************** *** 223,252 **** public static DataTable GetTickerBars( string ticker, int intervalFrameInSeconds ) { ! string sql = "select * from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + "order by " + Bars.DateTimeForOpen; return SqlExecutor.GetDataTable( sql ); } ! ! /// <summary> ! /// returns the bars DataTable for the given ticker ! /// </summary> ! /// <param name="ticker">ticker whose quotes are to be returned</param> ! /// <param name="firstBarDateTime">The first bar date time</param> ! /// <param name="lastBarDateTime">The last bar date time</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static DataTable GetTickerBars( string ticker, DateTime firstBarDateTime, ! DateTime lastBarDateTime, int intervalFrameInSeconds) ! { ! string sql = "select * from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + ! "and " + Bars.DateTimeForOpen + " between " + SQLBuilder.GetDateConstant(firstBarDateTime) + " " + ! "and " + SQLBuilder.GetDateConstant(lastBarDateTime) + " " + ! "order by " + Bars.DateTimeForOpen; ! return SqlExecutor.GetDataTable( sql ); ! } /// <summary> /// Returns the bars for the given instrument , since startDateTime to endDateTime --- 224,253 ---- public static DataTable GetTickerBars( string ticker, int intervalFrameInSeconds ) { ! string sql = "select * from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + "order by " + Bars.DateTimeForOpen; return SqlExecutor.GetDataTable( sql ); } ! ! /// <summary> ! /// returns the bars DataTable for the given ticker ! /// </summary> ! /// <param name="ticker">ticker whose quotes are to be returned</param> ! /// <param name="firstBarDateTime">The first bar date time</param> ! /// <param name="lastBarDateTime">The last bar date time</param> ! /// <param name="intervalFrameInSeconds">interval frame in seconds for the ticker's bars</param> ! /// <returns></returns> ! public static DataTable GetTickerBars( string ticker, DateTime firstBarDateTime, ! DateTime lastBarDateTime, int intervalFrameInSeconds) ! { ! string sql = "select * from bars " + ! "where " + Bars.TickerFieldName + "='" + ticker + "' and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + ! "and " + Bars.DateTimeForOpen + " between " + SQLBuilder.GetDateConstant(firstBarDateTime) + " " + ! "and " + SQLBuilder.GetDateConstant(lastBarDateTime) + " " + ! "order by " + Bars.DateTimeForOpen; ! return SqlExecutor.GetDataTable( sql ); ! } /// <summary> /// Returns the bars for the given instrument , since startDateTime to endDateTime *************** *** 258,262 **** /// <returns></returns> public static void SetDataTable( string tickerOrGroupID , DateTime startDateTime , DateTime endDateTime , ! DataTable dataTable, int intervalFrameInSeconds) { string sql; --- 259,263 ---- /// <returns></returns> public static void SetDataTable( string tickerOrGroupID , DateTime startDateTime , DateTime endDateTime , ! DataTable dataTable, int intervalFrameInSeconds) { string sql; *************** *** 264,268 **** sql = "select * from bars INNER JOIN tickers_tickerGroups ON " + "bars." + Bars.TickerFieldName + "=tickers_tickerGroups." + Tickers_tickerGroups.Ticker + " " + ! "where " + Tickers_tickerGroups.GroupID + "='" + tickerOrGroupID + "' and " + Bars.IntervalFrameInSeconds + "=" + intervalFrameInSeconds + " " + "and " + Bars.DateTimeForOpen + ">=" + SQLBuilder.GetDateTimeConstant( startDateTime ) + " " + --- 265,269 ---- sql = "select * from bars INNER JOIN tickers_tickerGroups ON " + "bars." + Bars.TickerFieldName + "=tickers_tickerGroups." + Tickers_tickerGroups.Ticker + " " + ! "where " + Tickers_tickerGroups.GroupID + "='" + tickerOrGroupID + "' and " + Bars.IntervalFrameInSeconds + "=" + intervalFrameInSeconds + " " + "and " + Bars.DateTimeForOpen + ">=" + SQLBuilder.GetDateTimeConstant( startDateTime ) + " " + *************** *** 271,275 **** else sql = "select * from bars " + ! "where " + Bars.TickerFieldName + "='" + tickerOrGroupID + "' and " + Bars.IntervalFrameInSeconds + "=" + intervalFrameInSeconds + " " + "and " + Bars.DateTimeForOpen + ">=" + SQLBuilder.GetDateTimeConstant( startDateTime ) + " " + --- 272,276 ---- else sql = "select * from bars " + ! "where " + Bars.TickerFieldName + "='" + tickerOrGroupID + "' and " + Bars.IntervalFrameInSeconds + "=" + intervalFrameInSeconds + " " + "and " + Bars.DateTimeForOpen + ">=" + SQLBuilder.GetDateTimeConstant( startDateTime ) + " " + *************** *** 291,300 **** foreach (string ticker in tickerCollection) if ( returnValue == "" ) ! // this is the first ticker to handle ! returnValue += setDataTable_getTickerListWhereClause_getSingleTickerWhereClause( ticker ); ! else ! // this is not the first ticker to handle ! returnValue += " or " + ! setDataTable_getTickerListWhereClause_getSingleTickerWhereClause( ticker ); return "( " + returnValue + " )"; } --- 292,301 ---- foreach (string ticker in tickerCollection) if ( returnValue == "" ) ! // this is the first ticker to handle ! returnValue += setDataTable_getTickerListWhereClause_getSingleTickerWhereClause( ticker ); ! else ! // this is not the first ticker to handle ! returnValue += " or " + ! setDataTable_getTickerListWhereClause_getSingleTickerWhereClause( ticker ); return "( " + returnValue + " )"; } *************** *** 311,315 **** string sql; sql = "select * from bars " + ! "where " + setDataTable_getTickerListWhereClause( tickerCollection ) + " and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + " and " + Bars.DateTimeForOpen + "=" + SQLBuilder.GetDateTimeConstant( dateTime ) + " " + --- 312,316 ---- string sql; sql = "select * from bars " + ! "where " + setDataTable_getTickerListWhereClause( tickerCollection ) + " and " + Bars.IntervalFrameInSeconds + "='" + intervalFrameInSeconds + "' " + " and " + Bars.DateTimeForOpen + "=" + SQLBuilder.GetDateTimeConstant( dateTime ) + " " + *************** *** 319,326 **** } #endregion ! #region SetDataTable - #region getSql private static string getSql( string ticker , DateTime firstDate , DateTime lastDate , --- 320,326 ---- } #endregion ! #region SetDataTable private static string getSql( string ticker , DateTime firstDate , DateTime lastDate , *************** *** 339,343 **** SQLBuilder.GetFilterForTime( "baDateTimeForOpen" , SqlComparisonOperator.GreaterThanOrEqual , ! firstBarOpenTimeInNewYorkTimeZone ) + // "(Format([baDateTimeForOpen],'hh:mm:ss')>='" + // this.getSqlTimeConstantForFirstDailyBar() + --- 339,343 ---- SQLBuilder.GetFilterForTime( "baDateTimeForOpen" , SqlComparisonOperator.GreaterThanOrEqual , ! firstBarOpenTimeInNewYorkTimeZone ) + // "(Format([baDateTimeForOpen],'hh:mm:ss')>='" + // this.getSqlTimeConstantForFirstDailyBar() + *************** *** 351,355 **** return sql; } - #endregion getSql /// <summary> --- 351,354 ---- *************** *** 378,381 **** --- 377,474 ---- + #region SetDataTable + + #region getSql + + #region getFilterForTimeList + + #region getFilterForTimeList_withTailToBeRemoved + private static string getFilterForTimeList( Time time ) + { + string filterForTimeList = + SQLBuilder.GetFilterForTime( + "baDateTimeForOpen" , SqlComparisonOperator.Equal , time ) + + " OR "; + return filterForTimeList; + } + private static string getFilterForTimeList_withTailToBeRemoved( + List<Time> dailyTimes ) + { + string filterForTimeList_withTailToBeRemoved = ""; + foreach ( Time time in dailyTimes ) + filterForTimeList_withTailToBeRemoved += + Bars.getFilterForTimeList( time ); + return filterForTimeList_withTailToBeRemoved; + } + #endregion getFilterForTimeList_withTailToBeRemoved + + private static string getFilterForTimeList( + List<Time> dailyTimes ) + { + // TO DO + // a "where ... in ..." statement could be used here, it would lead to a + // shorter Sql statement + string filterWithTailToBeRemoved = + Bars.getFilterForTimeList_withTailToBeRemoved( dailyTimes ); + string filterForTimeList = + filterWithTailToBeRemoved.Substring( + 0 , filterWithTailToBeRemoved.Length - " OR ".Length ); + return filterForTimeList; + } + #endregion getFilterForTimeList + + private static string getSql( + string ticker , DateTime firstDate , DateTime lastDate , + List<Time> dailyTimes , int intervalFrameInSeconds ) + { + string sql = + "select baDateTimeForOpen from bars " + + "where (baTicker='" + ticker + "') and " + + "(baInterval=" + intervalFrameInSeconds + ") and" + + "(baDateTimeForOpen>=" + + SQLBuilder.GetDateConstant( firstDate ) + ") and" + + "(baDateTimeForOpen<" + + SQLBuilder.GetDateConstant( lastDate.AddDays( 1 ) ) + + ") and (" + + Bars.getFilterForTimeList( dailyTimes ) + + // SQLBuilder.GetFilterForTime( + // "baDateTimeForOpen" , SqlComparisonOperator.GreaterThanOrEqual , + // firstBarOpenTimeInNewYorkTimeZone ) + + //// "(Format([baDateTimeForOpen],'hh:mm:ss')>='" + + //// this.getSqlTimeConstantForFirstDailyBar() + + // ") and (" + + // SQLBuilder.GetFilterForTime( + // "baDateTimeForOpen" , SqlComparisonOperator.LessThanOrEqual , + // lastBarOpenTimeInNewYorkTimeZone ) + + //// "(Format([baDateTimeForOpen],'hh:mm:ss')<='" + + //// this.getSqlTimeConstantForLastDailyBar() + + ");"; + return sql; + } + #endregion getSql + + /// <summary> + /// fills the parameter dataTable with all the daily bars at the given + /// daily times + /// </summary> + /// <param name="ticker"></param> + /// <param name="firstDate"></param> + /// <param name="lastDate"></param> + /// <param name="dailyTimes"></param> + /// <param name="dataTable"></param> + /// <param name="intervalFrameInSeconds"></param> + public static void SetDataTable( + string ticker , DateTime firstDate , DateTime lastDate , + List<Time> dailyTimes , + DataTable dataTable, int intervalFrameInSeconds ) + { + string sql = Bars.getSql( + ticker , firstDate , lastDate , + dailyTimes , + intervalFrameInSeconds ); + SqlExecutor.SetDataTable( sql , dataTable ); + } + #endregion SetDataTable + #region AddBar |
