import java.io.*;
import java.net.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.util.Date;

public class StockOracle
{
	private class StockOracles
	{
		int    m_iID;
		String m_strName;
                int    m_iDaysBack;
		int    m_iMonDaysBack;
   
                StockOracles(int iID, String strName, int iDaysBack, int iMonDaysBack)
                {
			m_iID = iID;
			m_strName = strName;
			m_iDaysBack = iDaysBack;
			m_iMonDaysBack = iMonDaysBack;
                }

		public String toString()
		{
			String strLine;
			strLine  = "ID=";
 			strLine += m_iID;
			strLine += "  Name=";
			strLine += m_strName;
			strLine += "  Days Back=";
			strLine += m_iDaysBack;
			strLine += "  Mon Days Back=";
			strLine += m_iMonDaysBack;
			return(strLine);
		}

		public String getName()
		{
			return(m_strName);
		}

		public int getID()
		{
			return(m_iID);
		}

		public int getDaysBack()
		{
			return(m_iDaysBack);
		}
		
		public int getMonDaysBack()
		{
			return(m_iMonDaysBack);
		}

	}

	private Connection myConnection;

	public void OpenStockDatabase()
	{
		try 
		{
			// Load the Oracle JDBC driver
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

			// Connect to the database
			// You can put a database name after the @ sign in the connection URL.
			myConnection = DriverManager.getConnection ("jdbc:oracle:oci8:@STOCKS", "Stocks", "Grabber#3");
		} 
		catch(SQLException e) 
		{
			System.err.println("Ook! (Opening the Database failed!");
			// If the creation of the socket fails, 
			// nothing needs to be cleaned up.
		}
	}

	public void CloseStockDatabase()
	{
		try 
		{
			// Close the connection
			myConnection.close();   
		} 
		catch(SQLException e) 
		{
			System.err.println("Ook! (Closing the Database failed!");
			// If the creation of the socket fails, 
			// nothing needs to be cleaned up.
		}
	}

	public void RemoveSelections()
	{
		try 
		{
			String strLine;
			String strSQL;

			strSQL  = " delete from OracleSelections";

			// Create a Statement
			Statement stmt = myConnection.createStatement ();

			ResultSet rset = stmt.executeQuery (strSQL);

			// Close the RseultSet
			rset.close();

			// Close the Statement
			stmt.close();
		}
		catch(SQLException e) 
		{
			System.err.print("Ook! (The Remove Selections failed)! ");
			System.err.println(e);
			// If the creation of the socket fails, 
			// nothing needs to be cleaned up.
		}
	}

	public void InsertSelection(int iID, String strPick)
	{
		try 
		{
			String strLine;
			String strSQL;

			strSQL  = "insert into OracleSelections values(";
			strSQL += iID;
			strSQL += ",'";
			strSQL += strPick.trim();
			strSQL += "')";

//			System.out.println(strSQL);

			// Create a Statement
			Statement stmt = myConnection.createStatement ();

			ResultSet rset = stmt.executeQuery (strSQL);

			// Close the RseultSet
			rset.close();

			// Close the Statement
			stmt.close();
		}
		catch(SQLException e) 
		{
			System.err.print("Ook! (The Insert Selection failed)! ");
			System.err.println(e);
			// If the creation of the socket fails, 
			// nothing needs to be cleaned up.
		}
	}


	public void RunOracle(StockOracles curOracle, FileWriter myFileWriter, boolean bMonday)
	{
		int i;
		try 
		{
			String strLine;
			String strSQL;

			myFileWriter.write("The ");
			myFileWriter.write(curOracle.getName());
			myFileWriter.write(" says:\n");
			myFileWriter.write("Ticker\t  Close\t\t  Highs\t\t Percent\n");

			strSQL  = " select 	a.ticker,";
			strSQL += " to_char(a.closeprice,'$990.00'),";
			strSQL += " to_char(b.Theavg,'$990.00'),";
			strSQL += " to_char(b.Theavg/a.closeprice,'0.0000') TheImportant";
			strSQL += " from ";
			strSQL += " (select ticker, ";
			strSQL += " closeprice ";
			strSQL += " from 	stockhistory a,";
			strSQL += " stockdates b";
			strSQL += " where 	to_char(a.quotedate,'yyyy/mm/dd') = ";
			strSQL += " to_char(b.curdate,'yyyy/mm/dd')) a,";
			strSQL += " (select ticker, ";
			strSQL += " avg(highprice) Theavg ";
			strSQL += " from 	stockhistory,";
			strSQL += " stockdates";
			strSQL += " where	to_char(quotedate,'yyyy/mm/dd') >=";
			strSQL += " to_char(curdate-";

			if(true == bMonday)
			{
				strSQL += curOracle.getMonDaysBack();
			}
			else
			{
				strSQL += curOracle.getDaysBack();
			}


			strSQL += " ,'yyyy/mm/dd')";
			strSQL += " and to_char(quotedate,'yyyy/mm/dd') <= to_char(curdate,'yyyy/mm/dd')";
			strSQL += " group 	by ticker) b";
			strSQL += " where a.ticker = b.ticker";
			strSQL += " order by TheImportant desc";

//			System.out.println(strSQL);

			// Create a Statement
			Statement stmt = myConnection.createStatement ();

			ResultSet rset = stmt.executeQuery (strSQL);

			// Iterate through the result
			i = 0;
			while (rset.next ())
			{
				i++;
				if(i > 5)
				{
					break;
				}
				strLine  = rset.getString(1);
 				strLine += "\t";
 				strLine += rset.getString(2);
 				strLine += "\t";
 				strLine += rset.getString(3);
 				strLine += "\t";
 				strLine += rset.getString(4);
 				strLine += "\n";
				System.out.print(strLine);
				myFileWriter.write(strLine);

				if(1 == i)
				{
					InsertSelection(curOracle.getID(),rset.getString(1));
				}
			}

			myFileWriter.write("\n\n");
			System.out.print("\n\n");

			// Close the RseultSet
			rset.close();

			// Close the Statement
			stmt.close();
		}
		catch(SQLException e) 
		{
			System.err.print("Ook! (The GetTickers failed)! ");
			System.err.println(e);
			// If the creation of the socket fails, 
			// nothing needs to be cleaned up.
		}
		catch(IOException e)
		{
			System.err.print("Ook! (The FileWriter thingy failed!");
			System.err.println(e);
		}
	}


	public int GetDayOfWeek()
	{
		Integer iDayOfWeek = new Integer(0);
		try 
		{
			String strLine;
			String strSQL;

			strSQL  = " select 	to_char(curdate,'D')";
			strSQL += " from stockdates";

			//System.out.println(strSQL);

			// Create a Statement
			Statement stmt = myConnection.createStatement ();

			ResultSet rset = stmt.executeQuery (strSQL);

			// Iterate through the result
			while (rset.next ())
			{
				iDayOfWeek = new Integer(rset.getString(1));
			}

			// Close the RseultSet
			rset.close();

			// Close the Statement
			stmt.close();
		}
		catch(SQLException e) 
		{
			System.err.print("Ook! (The GetDayOfWeek failed)! ");
			System.err.println(e);
			// If the creation of the socket fails, 
			// nothing needs to be cleaned up.
		}
		return(iDayOfWeek.intValue());
	}


	public Vector GetOracles()
	{
		Vector myVector = new Vector();

		try 
		{
			String strLine;
			String strSQL;
			Integer iID;
			Integer iDaysBack;
			Integer iMonDaysBack;

			strSQL  = " select 	ORACLEID,";
			strSQL += " ORACLENAME,";
			strSQL += " DAYBACK,";
			strSQL += " MONDAYBACK";
			strSQL += " FROM STOCKORACLES";
			strSQL += " Order By ORACLEID";

			// Create a Statement
			Statement stmt = myConnection.createStatement ();

			ResultSet rset = stmt.executeQuery (strSQL);

			// Iterate through the result
			while (rset.next ())
			{
				iID = new Integer(rset.getString(1));
				iDaysBack = new Integer(rset.getString(3));
				iMonDaysBack = new Integer(rset.getString(4));

				StockOracles myStockOracles = new StockOracles(	iID.intValue(), 
										rset.getString(2), 
										iDaysBack.intValue(), 
										iMonDaysBack.intValue());
				myVector.addElement(myStockOracles); 
			}

			// Close the RseultSet
			rset.close();

			// Close the Statement
			stmt.close();
		}
		catch(SQLException e) 
		{
			System.err.print("Ook! (The GetTickers failed)! ");
			System.err.println(e);
			// If the creation of the socket fails, 
			// nothing needs to be cleaned up.
		}
		return(myVector);
	}


	public void Predict() 
	{
		//String StockResult;
		//int start,end;
		//String StockLine;
		String strDate;
		//Vector vecTickers;
		//String strTicker;
		//String strTickerLine;
		//int    iTickerNumber;
		boolean   bMonday;
		Vector vecOracles;
		int    iDayOfWeek;
		
		try
		{

			OpenStockDatabase();

			iDayOfWeek = GetDayOfWeek();
			if(2 == iDayOfWeek)
			{
				bMonday = true;
			}
			else
			{
				bMonday = false;
			}

			System.out.print("Day of the Week=");
			System.out.println(iDayOfWeek);
			vecOracles = GetOracles();
			RemoveSelections();

			FileWriter myFileWriter = new FileWriter("TodaysNews.txt");

			while(!vecOracles.isEmpty())
			{
				StockOracles myStockOracles = (StockOracles)vecOracles.elementAt(0);
				vecOracles.removeElementAt(0);
				System.out.println(myStockOracles);
				RunOracle(myStockOracles, myFileWriter,bMonday);
			}

			myFileWriter.flush();
			myFileWriter.close();

			CloseStockDatabase();
		}
		catch(IOException e)
		{
			System.err.print("Ook! (The FileWriter thingy failed!");
			System.err.println(e);
		}
	}

	public static void main( String[] args ) 
	{
		StockOracle myStockOracle = new StockOracle();
		myStockOracle.Predict();
	} // end of main()

} // end of class StockOracle