Thread: [Quantproject-developers] QuantProject/b2_DataAccess/Tables Tickers.cs,1.4,1.5
Brought to you by:
glauco_1
|
From: Marco M. <mi...@us...> - 2005-09-11 15:38:53
|
Update of /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv18997/b2_DataAccess/Tables Modified Files: Tickers.cs Log Message: Added method GetFilteredTickers on overload for filtering tickers (for the TickerViewer object). Index: Tickers.cs =================================================================== RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables/Tickers.cs,v retrieving revision 1.4 retrieving revision 1.5 diff -C2 -d -r1.4 -r1.5 *** Tickers.cs 20 Jan 2005 19:13:33 -0000 1.4 --- Tickers.cs 11 Sep 2005 15:38:44 -0000 1.5 *************** *** 83,86 **** --- 83,105 ---- } + public static DataTable GetTableOfFilteredTickers(string tickerSymbolIsLike, + string tickerCompanyNameIsLike, + string firstOperatorInHavingStatement, + DateTime firstQuoteDate, + string secondOperatorInHavingStatement, + DateTime lastQuoteDate, + string groupID) + { + string sqlSelectString = Tickers.buildSqlSelectString(tickerSymbolIsLike, + tickerCompanyNameIsLike, + firstOperatorInHavingStatement, + firstQuoteDate, + secondOperatorInHavingStatement, + lastQuoteDate, + groupID); + return SqlExecutor.GetDataTable(sqlSelectString); + } + + #region buildSqlSelectString private static string buildSqlSelectString(string tickerSymbolIsLike, *************** *** 97,100 **** --- 116,148 ---- } + private static string buildSqlSelectString_getHavingStatement(string firstOperatorInHavingStatement, + string secondOperatorInHavingStatement, + DateTime firstQuoteDate, + DateTime lastQuoteDate) + { + string returnValue; + if(firstOperatorInHavingStatement == "" && + secondOperatorInHavingStatement == "") + returnValue = ""; + else if(firstOperatorInHavingStatement == "" && + secondOperatorInHavingStatement != "") + returnValue = "HAVING Max(quotes.quDate)" + secondOperatorInHavingStatement + + SQLBuilder.GetDateConstant(lastQuoteDate); + else if(firstOperatorInHavingStatement != "" && + secondOperatorInHavingStatement == "") + returnValue = "HAVING Min(quotes.quDate)" + firstOperatorInHavingStatement + + SQLBuilder.GetDateConstant(firstQuoteDate); + else + { + if(firstQuoteDate.CompareTo(lastQuoteDate)>0) + throw new Exception("Last Date can't be previous of First date!"); + returnValue = "HAVING Min(quotes.quDate)" + firstOperatorInHavingStatement + + SQLBuilder.GetDateConstant(firstQuoteDate) + + "AND Max(quotes.quDate)" + secondOperatorInHavingStatement + + SQLBuilder.GetDateConstant(lastQuoteDate); + } + return returnValue; + } + private static string buildSqlSelectString(string tickerSymbolIsLike, string tickerCompanyNameIsLike, *************** *** 105,110 **** { string sqlSelectString = ""; ! if(firstQuoteDate.CompareTo(lastQuoteDate)>0) ! throw new Exception("Last Date can't be previous of First date!"); sqlSelectString = "SELECT tiTicker, tiCompanyName, " + "Min(quotes.quDate) AS FirstQuote, Max(quotes.quDate) AS LastQuote, Count(quotes.quDate) AS NumberOfQuotes " + --- 153,157 ---- { string sqlSelectString = ""; ! sqlSelectString = "SELECT tiTicker, tiCompanyName, " + "Min(quotes.quDate) AS FirstQuote, Max(quotes.quDate) AS LastQuote, Count(quotes.quDate) AS NumberOfQuotes " + *************** *** 115,126 **** tickerCompanyNameIsLike + "' " + "GROUP BY tickers.tiTicker, tickers.tiCompanyName " + ! "HAVING Min(quotes.quDate)" + firstOperatorInHavingStatement + ! SQLBuilder.GetDateConstant(firstQuoteDate) + ! "AND Max(quotes.quDate)" + secondOperatorInHavingStatement + ! SQLBuilder.GetDateConstant(lastQuoteDate); return sqlSelectString; } ! #endregion --- 162,208 ---- tickerCompanyNameIsLike + "' " + "GROUP BY tickers.tiTicker, tickers.tiCompanyName " + ! buildSqlSelectString_getHavingStatement(firstOperatorInHavingStatement, ! secondOperatorInHavingStatement, ! firstQuoteDate, lastQuoteDate); return sqlSelectString; } ! private static string buildSqlSelectString(string tickerSymbolIsLike, ! string tickerCompanyNameIsLike, ! string firstOperatorInHavingStatement, ! DateTime firstQuoteDate, ! string secondOperatorInHavingStatement, ! DateTime lastQuoteDate, ! string groupID) ! { ! string sqlSelectString = ""; ! if(groupID == "") ! //no group has been selected ! { ! sqlSelectString = buildSqlSelectString(tickerSymbolIsLike, ! tickerCompanyNameIsLike, ! firstOperatorInHavingStatement, ! firstQuoteDate, ! secondOperatorInHavingStatement, ! lastQuoteDate); ! } ! else ! { ! sqlSelectString = "SELECT tiTicker, tiCompanyName, " + ! "Min(quotes.quDate) AS FirstQuote, Max(quotes.quDate) AS LastQuote, Count(quotes.quDate) AS NumberOfQuotes " + ! "FROM (tickers_tickerGroups LEFT JOIN tickers ON tickers_tickerGroups.ttTiId = tickers.tiTicker) LEFT JOIN quotes ON tickers.tiTicker = quotes.quTicker " + ! "WHERE tiTicker LIKE '" + ! tickerSymbolIsLike + "' " + ! "AND tiCompanyName LIKE '" + ! tickerCompanyNameIsLike + "' " + ! "AND ttTgId ='" + ! groupID + "' " + ! "GROUP BY tickers.tiTicker, tickers.tiCompanyName " + ! buildSqlSelectString_getHavingStatement(firstOperatorInHavingStatement, ! secondOperatorInHavingStatement, ! firstQuoteDate, lastQuoteDate); ! } ! return sqlSelectString; ! } #endregion |