[Quantproject-developers] QuantProject/b2_DataAccess/Tables Tickers_tickerGroups.cs, 1.8, 1.9 Valid
Brought to you by:
glauco_1
|
From: Glauco S. <gla...@us...> - 2008-12-30 00:15:47
|
Update of /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv17196/b2_DataAccess/Tables Modified Files: Tickers_tickerGroups.cs ValidatedTickers.cs Log Message: Now bot Access and MySql are supported Index: ValidatedTickers.cs =================================================================== RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables/ValidatedTickers.cs,v retrieving revision 1.5 retrieving revision 1.6 diff -C2 -d -r1.5 -r1.6 *** ValidatedTickers.cs 27 May 2004 16:47:49 -0000 1.5 --- ValidatedTickers.cs 30 Dec 2008 00:15:34 -0000 1.6 *************** *** 58,63 **** SqlExecutor.ExecuteNonQuery( "delete * from validatedTickers " + "where " + ValidatedTickers.Ticker + "='" + ticker + "'" ); ! OleDbSingleTableAdapter oleDbSingleTableAdapter = ! new OleDbSingleTableAdapter(); oleDbSingleTableAdapter.SetAdapter( "validatedTickers" ); oleDbSingleTableAdapter.DataTable.Rows.Add( oleDbSingleTableAdapter.DataTable.NewRow() ); --- 58,63 ---- SqlExecutor.ExecuteNonQuery( "delete * from validatedTickers " + "where " + ValidatedTickers.Ticker + "='" + ticker + "'" ); ! SingleTableDbDataAdapter oleDbSingleTableAdapter = ! new SingleTableDbDataAdapter(); oleDbSingleTableAdapter.SetAdapter( "validatedTickers" ); oleDbSingleTableAdapter.DataTable.Rows.Add( oleDbSingleTableAdapter.DataTable.NewRow() ); *************** *** 66,70 **** oleDbSingleTableAdapter.DataTable.Rows[ 0 ][ ValidatedTickers.EndDate ] = endDate; oleDbSingleTableAdapter.DataTable.Rows[ 0 ][ ValidatedTickers.HashValue ] = hashValue; ! oleDbSingleTableAdapter.OleDbDataAdapter.Update( oleDbSingleTableAdapter.DataTable ); } catch ( Exception ex ) --- 66,70 ---- oleDbSingleTableAdapter.DataTable.Rows[ 0 ][ ValidatedTickers.EndDate ] = endDate; oleDbSingleTableAdapter.DataTable.Rows[ 0 ][ ValidatedTickers.HashValue ] = hashValue; ! oleDbSingleTableAdapter.DbDataAdapter.Update( oleDbSingleTableAdapter.DataTable ); } catch ( Exception ex ) Index: Tickers_tickerGroups.cs =================================================================== RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables/Tickers_tickerGroups.cs,v retrieving revision 1.8 retrieving revision 1.9 diff -C2 -d -r1.8 -r1.9 *** Tickers_tickerGroups.cs 7 Apr 2008 20:46:43 -0000 1.8 --- Tickers_tickerGroups.cs 30 Dec 2008 00:15:34 -0000 1.9 *************** *** 3,7 **** Tickers_tickerGroups.cs ! Copyright (C) 2003 Marco Milletti --- 3,7 ---- Tickers_tickerGroups.cs ! Copyright (C) 2003 Marco Milletti *************** *** 19,29 **** 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.Data; using System.Windows.Forms; - using QuantProject.DataAccess; - namespace QuantProject.DataAccess.Tables --- 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.Data; using System.Windows.Forms; namespace QuantProject.DataAccess.Tables *************** *** 36,185 **** { ! // these static fields provide field name in the database table ! // They are intended to be used through intellisense when necessary ! public static string GroupID = "ttTgId"; ! public static string Ticker = "ttTiId"; ! public static string EventTypeFieldName = "ttEventType"; ! public static string EventDate = "ttEventDate"; ! public Tickers_tickerGroups() ! { ! ! } ! ! /// <summary> ! /// It fills the given dataTable with tickers belonging to a given group ! /// </summary> ! /// <param name="groupID">The groupID corresponding to the tickers whose ! /// values have to be stored in the given Data Table</param> ! /// <param name="dataTable">The dataTable where to store tickers</param> ! /// <returns></returns> ! public static void SetDataTable( string groupID , DataTable dataTable) ! { ! string sql; ! sql = "SELECT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.GroupID + "='" + ! groupID + "'"; ! SqlExecutor.SetDataTable( sql , dataTable ); ! } ! /// <summary> ! /// It provides deletion of the single ticker from the specified group ! /// </summary> ! public static void Delete( string tickerToDelete, ! string fromGroupID) ! { ! try ! { ! SqlExecutor.ExecuteNonQuery("DELETE * FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.Ticker + "='" + ! tickerToDelete + "' AND " + Tickers_tickerGroups.GroupID + "='" + ! fromGroupID + "'"); ! } ! catch(Exception ex) ! { ! MessageBox.Show(ex.ToString()); ! } ! } ! ! private static string insert_getEventTypeCode(EventType eventType) ! { ! string returnValue = "I";//default value for Exit eventType ! switch (eventType) ! { ! case EventType.Exit: ! returnValue = "O"; ! break; ! } ! return returnValue; ! ! } ! ! /// <summary> ! /// Adds a new row into tickers_tickerGroups ! /// </summary> ! public static void Add( string ticker, string groupId, EventType eventType, ! DateTime eventDate) ! { ! string eventTypeCode = insert_getEventTypeCode(eventType); ! SqlExecutor.ExecuteNonQuery("INSERT INTO tickers_tickerGroups(ttTiId, ttTgId, ttEventType, ttEventDate) " + ! "VALUES('" + ticker + "','" + groupId + "','" + eventTypeCode + ! "'," + SQLBuilder.GetDateConstant(eventDate)+ ")"); ! } ! /// <summary> ! /// It provides deletion of an entire group of tickers ! /// </summary> ! public static void Delete( string groupToDelete) ! { ! try ! { ! SqlExecutor.ExecuteNonQuery("DELETE * FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.GroupID + "='" + ! groupToDelete + "'"); ! } ! catch(Exception ex) ! { ! MessageBox.Show(ex.ToString()); ! } ! } ! ! /// <summary> ! /// It returns a table containing tickers of a given groupID ! /// </summary> ! public static DataTable GetTickers( string groupID) ! { ! /// TO DO use a join in order to return a table with tiTicker and company name ! string sql = "SELECT DISTINCT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.GroupID + "='" + ! groupID + "'"; return SqlExecutor.GetDataTable( sql ); ! } ! ! /// <summary> ! /// It returns a table containing tickers effectively contained ! /// in the given group at the given Date ! /// </summary> ! public static DataTable GetTickers( string groupID, DateTime date) ! { ! ! string sqlTickersAtTheGivenDate = "SELECT ttTiId AS TickerID FROM tickers_tickerGroups " + ! "WHERE ttTgId='" + groupID + "' AND " + ! "ttEventDate<=" + SQLBuilder.GetDateConstant(date) + " " + ! "GROUP BY ttTiId " + ! "HAVING Right(Max(Year([ttEventDate]) & " + ! "IIf(Month([ttEventDate])<10,'0' & Month([ttEventDate]),Month([ttEventDate])) & " + ! "IIf(Day([ttEventDate])<10,'0' & Day([ttEventDate]),Day([ttEventDate])) & " + ! "[ttEventType]),1)='I'"; ! ! return SqlExecutor.GetDataTable(sqlTickersAtTheGivenDate); ! } ! /* /// <summary> ! /// It returns a table containing all the tickers /// gained through a recursion through all the groups contained in the given groupID /// </summary> public static DataTable GetTickersWithRecursionInsideGroup( string groupID) { ! /// TO DO use a join in order to return a table with tiTicker and company name } ! */ ! ! /// <summary> ! /// It returns true if some tickers are grouped in the given groupID ! /// </summary> ! public static bool HasTickers( string groupID) ! { ! /// TO DO use a join in order to return a table with tiTicker and company name ! DataTable tickers = SqlExecutor.GetDataTable("SELECT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.GroupID + "='" + ! groupID + "'"); ! return tickers.Rows.Count > 0; ! } ! ! } } --- 34,232 ---- { ! // these static fields provide field name in the database table ! // They are intended to be used through intellisense when necessary ! public static string GroupID = "ttTgId"; ! public static string Ticker = "ttTiId"; ! public static string EventTypeFieldName = "ttEventType"; ! public static string EventDate = "ttEventDate"; ! public Tickers_tickerGroups() ! { ! ! } ! ! /// <summary> ! /// It fills the given dataTable with tickers belonging to a given group ! /// </summary> ! /// <param name="groupID">The groupID corresponding to the tickers whose ! /// values have to be stored in the given Data Table</param> ! /// <param name="dataTable">The dataTable where to store tickers</param> ! /// <returns></returns> ! public static void SetDataTable( string groupID , DataTable dataTable) ! { ! string sql; ! sql = "SELECT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.GroupID + "='" + ! groupID + "'"; ! SqlExecutor.SetDataTable( sql , dataTable ); ! } ! /// <summary> ! /// It provides deletion of the single ticker from the specified group ! /// </summary> ! public static void Delete( string tickerToDelete, ! string fromGroupID) ! { ! try ! { ! SqlExecutor.ExecuteNonQuery("DELETE * FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.Ticker + "='" + ! tickerToDelete + "' AND " + Tickers_tickerGroups.GroupID + "='" + ! fromGroupID + "'"); ! } ! catch(Exception ex) ! { ! MessageBox.Show(ex.ToString()); ! } ! } ! ! private static string insert_getEventTypeCode(EventType eventType) ! { ! string returnValue = "I";//default value for Exit eventType ! switch (eventType) ! { ! case EventType.Exit: ! returnValue = "O"; ! break; ! } ! return returnValue; ! ! } ! ! /// <summary> ! /// Adds a new row into tickers_tickerGroups ! /// </summary> ! public static void Add( string ticker, string groupId, EventType eventType, ! DateTime eventDate) ! { ! string eventTypeCode = insert_getEventTypeCode(eventType); ! SqlExecutor.ExecuteNonQuery("INSERT INTO tickers_tickerGroups(ttTiId, ttTgId, ttEventType, ttEventDate) " + ! "VALUES('" + ticker + "','" + groupId + "','" + eventTypeCode + ! "'," + SQLBuilder.GetDateConstant(eventDate)+ ")"); ! } ! /// <summary> ! /// It provides deletion of an entire group of tickers ! /// </summary> ! public static void Delete( string groupToDelete) ! { ! try ! { ! SqlExecutor.ExecuteNonQuery("DELETE * FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.GroupID + "='" + ! groupToDelete + "'"); ! } ! catch(Exception ex) ! { ! MessageBox.Show(ex.ToString()); ! } ! } ! ! /// <summary> ! /// It returns a table containing tickers of a given groupID ! /// </summary> ! public static DataTable GetTickers( string groupID) ! { ! /// TO DO use a join in order to return a table with tiTicker and company name ! string sql = "SELECT DISTINCT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.GroupID + "='" + ! groupID + "'"; return SqlExecutor.GetDataTable( sql ); ! } ! ! #region GetTickers ! ! #region getSqlForTickersAtGivenDate ! private static string getSqlForTickersAtGivenDateForAccess( string groupID, DateTime date ) ! { ! string sqlForTickersAtTheGivenDateForAccess = ! "SELECT ttTiId AS TickerID FROM tickers_tickerGroups " + ! "WHERE ttTgId='" + groupID + "' AND " + ! "ttEventDate<=" + SQLBuilder.GetDateConstant(date) + " " + ! "GROUP BY ttTiId " + ! "HAVING Right(Max(Year([ttEventDate]) & " + ! "IIf(Month([ttEventDate])<10,'0' & Month([ttEventDate]),Month([ttEventDate])) & " + ! "IIf(Day([ttEventDate])<10,'0' & Day([ttEventDate]),Day([ttEventDate])) & " + ! "[ttEventType]),1)='I'"; ! return sqlForTickersAtTheGivenDateForAccess; ! } ! private static string getSqlForTickersAtGivenDateForMySql( string groupID, DateTime date ) ! { ! string sqlForTickersAtTheGivenDateForMySql = ! "SELECT ttTiId AS TickerID " + ! "FROM tickers_tickerGroups " + ! "WHERE ttTgId='" + groupID + "' AND " + ! "ttEventDate<=" + SQLBuilder.GetDateConstant(date) + " " + ! "GROUP BY ttTiId " + ! "HAVING Right(Max(concat( " + ! "Year(ttEventDate) , " + ! "If(Month(ttEventDate)<10,'0' & Month(ttEventDate),Month(ttEventDate)) , " + ! "If(Day(ttEventDate)<10,'0' & Day(ttEventDate),Day(ttEventDate)) , " + ! "ttEventType " + ! ")),1)='I'"; ! return sqlForTickersAtTheGivenDateForMySql; ! } ! private static string getSqlForTickersAtGivenDate( string groupID, DateTime date ) ! { ! string sqlForTickersAtGivenDate = null; ! switch ( ConnectionProvider.DbType ) ! { ! case DbType.Access: ! sqlForTickersAtGivenDate = ! Tickers_tickerGroups.getSqlForTickersAtGivenDateForAccess( ! groupID , date ); ! break; ! case DbType.MySql: ! sqlForTickersAtGivenDate = ! Tickers_tickerGroups.getSqlForTickersAtGivenDateForMySql( ! groupID , date ); ! break; ! default: ! throw new Exception( ! "Unknown database type. Complete the switch statement, please" ); ! } ! return sqlForTickersAtGivenDate; ! } ! #endregion getSqlForTickersAtGivenDate ! ! /// <summary> ! /// It returns a table containing tickers effectively contained ! /// in the given group at the given Date ! /// </summary> ! public static DataTable GetTickers( string groupID, DateTime date) ! { ! string sqlForTickersAtTheGivenDate = ! Tickers_tickerGroups.getSqlForTickersAtGivenDate( groupID , date ); ! return SqlExecutor.GetDataTable(sqlForTickersAtTheGivenDate); ! } ! #endregion GetTickers ! ! /* /// <summary> ! /// It returns a table containing all the tickers /// gained through a recursion through all the groups contained in the given groupID /// </summary> public static DataTable GetTickersWithRecursionInsideGroup( string groupID) { ! /// TO DO use a join in order to return a table with tiTicker and company name } ! */ ! ! /// <summary> ! /// It returns true if some tickers are grouped in the given groupID ! /// </summary> ! public static bool HasTickers( string groupID) ! { ! /// TO DO use a join in order to return a table with tiTicker and company name ! DataTable tickers = SqlExecutor.GetDataTable("SELECT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " + ! "WHERE " + Tickers_tickerGroups.GroupID + "='" + ! groupID + "'"); ! return tickers.Rows.Count > 0; ! } ! ! } } |