//------------------------------------------------------------------------------
// written by: Shivangi C
// assisted by: NA
// debugged by:Shivangi C
//
// summary: This file has the Data Access layer functions of the 
//          bussiness layer which has all the queries fired to the data base 
//          depending of the functionality requested by the user
//          
//------------------------------------------------------------------------------


using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Data.SqlClient;
using System.Data.ProviderBase;
using MySql.Data.MySqlClient;

namespace SFSDataAccessLayer
{
    /// <summary>
    /// This class is the basic class for database access whose functions are called by
    /// the webservice StockInfo.asmx through an object of this class
    /// </summary>
    public class StockInfoDAL
    {
        //connection stringto connect to DB
        public const string STR_CONNECTION_STRING = "DATA SOURCE=128.6.29.169;DATABASE=group804;User ID=group804;PASSWORD=EE#rt18";
               
        //
        /// <summary>
        /// This method creates a connection to the data base
        /// </summary>
        /// <param name="strConn"></param>
        /// <returns></returns>
        private MySqlConnection CreateConnection(string strConn)
        {
            MySqlConnection conn = new MySqlConnection(strConn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        /// <summary>
        /// This method closes a connection to the data base
        /// </summary>
        /// <param name="connObj"></param>
        private void CloseConnection(MySqlConnection connObj)
        {
            if (connObj.State == ConnectionState.Open)
            {
                connObj.Close();
            }
            connObj.Dispose();
        }

        /// <summary>
        /// This function queries the 'stocks' table from the database
        /// </summary>
        /// <param name="stockID"></param>
        /// <returns></returns>
        public DataSet GetStockPriceInfoDAL(string stockID)
        {
            // The output of this fuction will Be
            // stockname,curent price, today's high and 's low

            DataTable dtStck = new DataTable();
            dtStck.TableName = "StockInfo";
            //create db connection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            // create the query
            MySqlCommand cmd = new MySqlCommand("select current_value as 'Current Market Value', change_points as 'Up/Down', opening_value as 'Opening Price', days_high as 'Day High', days_low as 'Day Low', date as 'Date', time as 'Last Updated' from stocks where stock_name = '" + stockID + "'", objConn);
            MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd);
            // Fire the query and get the output in a data table
            sqlDA.Fill(dtStck);
            DataSet dsdata = new DataSet();
            dsdata.Tables.Add(dtStck);
            cmd.Dispose();
            //close db connection
            CloseConnection(objConn);
            return dsdata;
        }

        /// <summary>
        /// This function gives the
        /// Company name, last year Revenue, last year profit,
        /// last qarter revenue and current year quarter revenues
        /// </summary>
        /// <param name="stockID"></param>
        /// <returns></returns>
        public DataSet GetFinancialInfoDAL(string stockID)
        {
            
            DataTable dtStck = new DataTable();
            dtStck.TableName = "StockInfo";
            // create db connection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            // create the query
            MySqlCommand cmd = new MySqlCommand("select L_q1_rev as 'LastYear Q1 Revenue', L_q2_prof as 'LastYear Q1 Profit' , L_q2_rev as 'LastYear Q2 Revenue', L_q2_prof as 'LastYear Q2 Profit' , L_q3_rev as 'LastYear Q3 Revenue', L_q3_prof as 'LastYear Q3 Profit' ,L_q4_rev as 'LastYear Q4 Revenue', L_q4_prof as 'LastYear Q4 Profit' , q1_rev as 'Current Q1 Revenue', q1_prof as 'Current Q1 Profit', q2_rev as 'Current Q2 Revenue', q2_prof as 'Current Q2 Profit', q3_rev as 'Current Q3 Revenue', q3_prof as 'Current Q3 Profit', q4_rev as 'Current Q4 Revenue', q4_prof as 'Current Q4 Profit' from Financial_info where Stock_ID = '" + stockID + "'", objConn);
            MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd);
            // Fire the query and get the output in a data table 
            sqlDA.Fill(dtStck);
            DataSet dsdata = new DataSet();
            dsdata.Tables.Add(dtStck);
            cmd.Dispose();
            //close db connection
            CloseConnection(objConn);
            return dsdata;
        }

       /// <summary>
       /// This function queries the Stock infor table to return all the competitve 
       /// stock in the same sector
       /// </summary>
       /// <param name="stockID"></param>
       /// <returns></returns>
        public DataSet GetCompetitiveStocksDAL(string stockID)
        {
            //Returns comma seperated company names based on the 
            // stock price percentage increase equivalent to the given name
            DataTable dtStck = new DataTable();
            dtStck.TableName = "StockInfo";
            // create db connection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            // create the query
            MySqlCommand cmd =new MySqlCommand("select sector_id from StockInfo where Stock_ID = '" + stockID + "'",objConn);
            // fire a partial query to use it for the next query
            int sectorId = (int)cmd.ExecuteScalar();
            // create the next query
            cmd = new MySqlCommand("select Stock_ID, Name, current_value as 'Current Price' from StockInfo, stocks where sector_id = '" + sectorId + "' and Stock_ID != '" + stockID + "' and stocks.stock_name=StockInfo.Stock_ID", objConn);
            
            MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd);
            // Fire the query and get the output in a data table
            sqlDA.Fill(dtStck);
            DataSet dsdata = new DataSet();
            dsdata.Tables.Add(dtStck);
            cmd.Dispose();
            //close db connection
            CloseConnection(objConn);
            return dsdata;
        }

        /// <summary>
        /// This fucntion queries the Day Price table to get all the prices of the selected stock
        /// at every one hour interval from 10 am to 4 pm when the stock exchange is functional
        /// </summary>
        /// <param name="stockID"></param>
        /// <returns></returns>
        public DataSet GetCurrentDayPerformanceDAL(string stockID)
        {
            // returns hourly prices for the day
            // create db connection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            DataTable dtPerf = new DataTable();
            // create the query
            MySqlCommand cmd = new MySqlCommand("select Date, 10AM, 11AM, 12PM, 1PM, 2PM, 3PM, 4PM from DayPrice where stock_name = '" + stockID + "'", objConn);
            MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd);
            // Fire the query and get the output in a data table
            sqlDA.Fill(dtPerf);
            DataSet dsdata = new DataSet();
            dsdata.Tables.Add(dtPerf);
            cmd.Dispose();
            //close db connection
            CloseConnection(objConn);
            return dsdata;
        }

        /// <summary>
        /// This function queries the Month Price table to get data for the month for the selected stock
        /// </summary>
        /// <param name="stockID"></param>
        /// <param name="dayRangeCode"></param>
        /// <returns></returns>
        public DataSet GetStockPerformanceDAL(string stockID, int dayRangeCode)
        {
            // returns current or Last months data
            // create db connection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            DataTable dtPerf = new DataTable();
            // create the query
            MySqlCommand cmd = new MySqlCommand("select date as 'Date', closing_price as 'Closing Price', opening_price as 'Opening Price', day_high as 'Day High', day_low as 'Day Low' from MonthPrice where stock_name = '"+ stockID + "'",objConn);
            MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd);
            // Fire the query and get the output in a data table
            sqlDA.Fill(dtPerf);
            DataSet dsdata = new DataSet();
            dsdata.Tables.Add(dtPerf);
            cmd.Dispose();
            //close db connection
            CloseConnection(objConn);
            return dsdata;
        
        }

        /// <summary>
        /// This function queries data from Month price table to to predict the next day value
        /// </summary>
        /// <param name="stockID"></param>
        /// <param name="termcode"> this is the period short term - 0/ long term -1</param>
        /// <returns></returns>
        public decimal[] GetNextSpeculativePriceDAL(string stockID, int termcode)
        {
            // Returns predicted price, time period, %change of price
            DataTable dtStck = new DataTable();
            decimal[] priceArray;
            dtStck.TableName = "StockInfo";
            //create db connection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            MySqlCommand cmd;
            if (termcode == 0)
            {
                // create the query
                cmd = new MySqlCommand("select closing_price from MonthPrice where stock_name  = '" + stockID + "' AND SUBDATE(CURDATE(),INTERVAL 14 DAY) <= date", objConn);
                //cmd = new MySqlCommand("select closing_price from MonthPrice where stock_name  = '" + stockID + "' AND SUBDATE(SUBDATE(CURDATE(),INTERVAL -1 DAY),INTERVAL 14 DAY) <= date and date < '2008-05-07'", objConn);
               
            }
            else
            {
                // create the query
                cmd = new MySqlCommand("select closing_price from MonthPrice where stock_name = '" + stockID + "' AND SUBDATE(CURDATE(),INTERVAL 60 DAY) <= date", objConn);
                
            }
            MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd);
            // Fire the query and get the output in a data table
            sqlDA.Fill(dtStck);
            priceArray = new decimal[dtStck.Rows.Count];

            // Fill data returned by the query into an array and give it to the web service method
            for (int i = 0; i < dtStck.Rows.Count;i++ )
            {
                priceArray[i] = Convert.ToDecimal(dtStck.Rows[i][0].ToString());
            }
            // term code zero indicates short term prediction
            if (termcode != 0)
            {
                int arrlen = (int)(dtStck.Rows.Count/5);
                decimal[] longTermPriceArray = new decimal[arrlen];
                for (int i = 0; i < arrlen;i++)
                {
                    longTermPriceArray[i] = priceArray[i*5];
                }
                priceArray = new decimal[arrlen];
                priceArray = longTermPriceArray;
            }

            cmd.Dispose();
            //close db connection
            CloseConnection(objConn);
            return priceArray;
        }
        /// <summary>
        /// This function is used to get the trading tips , It in turn calls 
        /// GetNextSpeculativePriceDAL method
        /// </summary>
        /// <param name="stockId"></param>
        /// <param name="termCode"></param>
        /// <returns></returns>
        public decimal[] GetTradingTipsDAL(string stockId, int termCode)
        {
            // Gives trading tips
            return GetNextSpeculativePriceDAL(stockId, termCode);
        }
        /// <summary>
        /// This is the function that queries the StockInfo table and find stocks depending on the search 
        /// string
        /// </summary>
        /// <param name="companyName"></param>
        /// <returns></returns>
        public DataSet FindStockDAL(string companyName)
        {
            // will return the names of the matching companyName
            //c reate db connection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            DataTable dtPerf = new DataTable();
            // create the query
            MySqlCommand cmd = new MySqlCommand("select name, stock_id from StockInfo where name like '%" + companyName + "%' order by name", objConn);
            MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd);
            // Fire the query and get the output in a data table
            sqlDA.Fill(dtPerf);
            DataSet dsdata = new DataSet();
            dsdata.Tables.Add(dtPerf);
            cmd.Dispose();
            //close db connection
            CloseConnection(objConn);
            return dsdata;
        }

        /// <summary>
        /// This is the functionalilty for registering the user
        /// </summary>
        /// <param name="firstName"></param>
        /// <param name="lastName"></param>
        /// <param name="userId"></param>
        /// <param name="pwd"></param>
        /// <param name="email"></param>
        /// <returns></returns>
        public int RegisterUser(string firstName, string lastName, string userId, string pwd, string email)
        {
            //create db connection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            // create the query
            MySqlCommand cmd = new MySqlCommand(" select count(1) from User_info where userid = '" + userId+ "'" , objConn);
            int cnt = (int)cmd.ExecuteScalar();
            if (cnt == 0)
            {
                // create the query
                cmd = new MySqlCommand("insert into User_info values('" + firstName + "','" + lastName + "','" + userId + "','" + pwd + "','" + email + "')", objConn);

                try
                {
                    // Fire the query and get the output 
                    cmd.ExecuteNonQuery();
                }

                catch (Exception ex)
                {
                    return 0;
                }
                finally
                {
                    cmd.Dispose();
                    //close db connection
                    CloseConnection(objConn);
                }
                return 1;
            }
            else
                return 2;
        }

        /// <summary>
        /// This method implements the functionality for login user
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public int LoginUser(string userName, string password)
        {
            // create db conenction
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            // create the query
            MySqlCommand cmd = new MySqlCommand(" select count(1) from User_info where userid = '" + userName + "' and password = '" + password + "'", objConn);
            // Fire the query and get the output
            int cnt = (int)cmd.ExecuteScalar();
            if (cnt == 0)
            {
                cmd.Dispose();
                //close db connection
                CloseConnection(objConn);
                return 0;
            }
            else
            {
                cmd.Dispose();
                //close db connection
                CloseConnection(objConn);
                return 1;
            }
            
        }

        /// <summary>
        /// This method queries the Stock Info table to get the basic information about the stock selected
        /// like date os establishment , sector name , traded volume etc
        /// </summary>
        /// <param name="stockID"></param>
        /// <returns></returns>
        public DataSet GetGeneralStockInfoDAL(string stockID)
        {
            //Returns comma seperated company names based on the 
            // stock price percentage increase equivalent to the given name
            DataTable dtStck = new DataTable();
            dtStck.TableName = "StockInfo";
            // create db conection
            MySqlConnection objConn = CreateConnection(STR_CONNECTION_STRING);
            // create the query
            MySqlCommand cmd = new MySqlCommand("select Name,Estd_Yr,Units,Sector_name,current_value from StockInfo inner join stocks on (StockInfo.stock_id = stocks.stock_name) inner join Sectors on (StockInfo.Sector_id = Sectors.Sector_id) where stock_id = '" + stockID + "'" , objConn);
            MySqlDataAdapter sqlDA = new MySqlDataAdapter(cmd);
            // Fire the query and get the output in a data table
            sqlDA.Fill(dtStck);
            DataSet dsdata = new DataSet();
            dsdata.Tables.Add(dtStck);
            cmd.Dispose();
            //close db connection
            CloseConnection(objConn);
            return dsdata;
        }
    }
} 
