[Quantproject-developers] QuantProject/b2_DataAccess/Tables Tickers.cs,1.4,1.5

[Marco M. <mi...@us...>]

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