[Quantproject-developers] QuantProject/b2_DataAccess/Tables EventType.cs,NONE,1.1 Tickers_tickerGroups.cs,1.5,1.6

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

Update of /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv3761/b2_DataAccess/Tables

Modified Files:
	Tickers_tickerGroups.cs 
Added Files:
	EventType.cs 
Log Message:
Modified database's structure. New fields added to tickers_tickerGroups table.

GetTableOfSelectedTickers in SelectorByGroup now selects tickers belonging to the specified group at a given date, when SelectorByGroup has been instantiated with a particular date.


--- NEW FILE: EventType.cs ---
/*
QuantProject - Quantitative Finance Library

EventType.cs
Copyright (C) 2004 
Marco Milletti

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
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;

namespace QuantProject.DataAccess.Tables
{
  /// <summary>
  /// Enum used by tickers_tickerGroups object for ttEventType values 
  /// </summary>
  public enum EventType
  {
    Entry,
    Exit
  }
}

Index: Tickers_tickerGroups.cs
===================================================================
RCS file: /cvsroot/quantproject/QuantProject/b2_DataAccess/Tables/Tickers_tickerGroups.cs,v
retrieving revision 1.5
retrieving revision 1.6
diff -C2 -d -r1.5 -r1.6
*** Tickers_tickerGroups.cs	5 Sep 2004 13:49:31 -0000	1.5
--- Tickers_tickerGroups.cs	27 Aug 2005 19:20:26 -0000	1.6
***************
*** 40,43 ****
--- 40,45 ----
      public static string GroupID = "ttTgId";
      public static string Ticker = "ttTiId";
+     public static string EventTypeFieldName = "ttEventType";
+     public static string EventDate = "ttEventDate";
  
      public Tickers_tickerGroups()
***************
*** 80,83 ****
--- 82,110 ----
        }
      }
+     
+     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>
***************
*** 104,111 ****
      {
        /// TO DO use a join in order to return a table with tiTicker and company name  
!       return SqlExecutor.GetDataTable("SELECT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " +
            "WHERE " + Tickers_tickerGroups.GroupID + "='" +
            groupID + "'");
      }
      /*
      /// <summary>
--- 131,185 ----
      {
        /// TO DO use a join in order to return a table with tiTicker and company name  
!       return SqlExecutor.GetDataTable("SELECT DISTINCT " + Tickers_tickerGroups.Ticker + " FROM tickers_tickerGroups " +
            "WHERE " + Tickers_tickerGroups.GroupID + "='" +
            groupID + "'");
      }
+     
+     private static void getTickers_createView(string viewName,
+                                               string sqlStatement)
+     {
+       try
+       {	
+       	string sqlForViewCreation = "CREATE VIEW " + viewName + " AS " + sqlStatement;
+       	SqlExecutor.ExecuteNonQuery(sqlForViewCreation);
+       }
+       catch(Exception ex)
+       {
+       	ex = ex;
+       }
+     }
+     
+     /// <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 sqlLastEntries = "SELECT tickers_tickerGroups.ttTgId AS GroupIDEntries, " +
+                               "tickers_tickerGroups.ttTiId AS TickerIDEntries, Max(tickers_tickerGroups.ttEventDate) " +
+                                "AS MaxEntryDate FROM tickers_tickerGroups WHERE " +
+                               "tickers_tickerGroups.ttEventType='I' " +
+                               "GROUP BY tickers_tickerGroups.ttTgId, tickers_tickerGroups.ttTiId";
+       getTickers_createView("Entries", sqlLastEntries);
+       
+       string sqlLastExits = "SELECT tickers_tickerGroups.ttTgId AS GroupIDExits, " +
+                             "tickers_tickerGroups.ttTiId AS TickerIDExits, Max(tickers_tickerGroups.ttEventDate) " +
+                             "AS MaxExitDate FROM tickers_tickerGroups WHERE " +
+                             "tickers_tickerGroups.ttEventType='O' " +
+                             "GROUP BY tickers_tickerGroups.ttTgId, tickers_tickerGroups.ttTiId";
+       
+       getTickers_createView("Exits", sqlLastExits);
+       
+       string sqlTickersAtTheGivenDate = "SELECT GroupIDEntries, " +
+       									"TickerIDEntries FROM Entries LEFT JOIN Exits " + 
+       									"ON (Entries.GroupIDEntries = Exits.GroupIDExits) AND " +
+       									"(Entries.TickerIDEntries = Exits.TickerIDExits) WHERE " +
+       									"GroupIDEntries ='" + groupID + "' AND " +
+       									"MaxEntryDate<=" + SQLBuilder.GetDateConstant(date) + " AND " +
+       									"(MaxExitDate Is Null OR MaxEntryDate>Exits.MaxExitDate)";
+      
+       return SqlExecutor.GetDataTable(sqlTickersAtTheGivenDate);
+     }
      /*
      /// <summary>