using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace AdvertsingProfitControl
{
    /// <summary>
    /// Deprecated!
    /// </summary>
    class RetrieveFromDatabase
    {
        DatabaseTracker gDatabaseLocation = new DatabaseTracker();
        LogConsole console = LogConsole.GetStaticInstance;
        private string gConnectionString;

        public RetrieveFromDatabase() 
        {
            //First thing, get the connection string from the DatabaseTracker.
            gConnectionString = gDatabaseLocation.DatabaseConnectionString;
        }


        private DataTable ExecuteQuery(OleDbCommand sqlCommand)
        {
            DataTable table = new DataTable();
            
            try
            {
                sqlCommand.Connection.Open();
                OleDbDataAdapter a = new OleDbDataAdapter(sqlCommand);
                a.Fill(table);
            }
            catch (Exception e)
            {
                
                console.WriteToLog(LogConsole.Level.Error, "Error attempting to retrieve database from the database.");
                console.WriteToLog(LogConsole.Level.Debug, "Retrieval error: " + e.Message);
            }
            finally
            {
                sqlCommand.Connection.Close();
            }

            return table;
        }

        public string RetrieveMostRecentDateId()
        {
            string dateID = "";
            string selectString = "SELECT WeekEnding.ID FROM WeekEnding WHERE WeekEnding.EndOfWeekDate = (SELECT MAX(WeekEnding.EndOfWeekDate) FROM WeekEnding)";
            DataTable table = new DataTable();
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Connection = connection;
            connection.Open();
            var reader = command.ExecuteReader();
            while (reader != null && reader.Read())
            {
                dateID = reader[0].ToString();
            }
            connection.Close();

            return dateID;
        }

        public string RetrieveAdItemId(string adItem)
        {
            string ID = "-1";
            DataTable table = new DataTable();
            string selectString = "SELECT AdItem.ID FROM AdItem WHERE AdItem.AdItem = @ID";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Parameters.AddWithValue("@ID", adItem);
            table = ExecuteQuery(command);
            if (table.Rows[0][0].ToString() != "")
            {
                ID = table.Rows[0][0].ToString();
            }
            return ID;
        }

        public string RetrieveDateIdByDateString(string dateString)
        {
            string dateID = "-1";
            DataTable table = new DataTable();
            string selectString = "SELECT WeekEnding.ID FROM WeekEnding WHERE WeekEnding.EndOfWeekDate = @dateString";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Parameters.AddWithValue("@dateString", dateString);
            table = ExecuteQuery(command);
            try
            {            
                if (table.Rows[0][0].ToString() != "")
                {
                    dateID = table.Rows[0][0].ToString();
                }

            }
            catch (Exception e)
            {
                dateID = "-1";
            }

            return dateID;
        }

        public string RetrieveComments(string dateId)
        {
            string comments;
            DataTable table = new DataTable();
            string selectString = "SELECT Comment.Comment FROM Comment WHERE Comment.FK_DateID = @dateString";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Parameters.AddWithValue("@dateString", dateId);
            table = ExecuteQuery(command);

            if (table.Rows.Count == 0)
            {
                DateTime time = Convert.ToDateTime(RetrieveDateStringByID(dateId));
                comments = "No comments to display for " + time.ToLongDateString() + ".";
            }
            else
            {
                comments = table.Rows[0][0].ToString();
            }

            return comments;
        }

        public string RetrieveDateStringByID(string dateID)
        {
            string dateString = "";
            DataTable table = new DataTable();
            string selectString = "SELECT WeekEnding.EndOfWeekDate FROM WeekEnding WHERE WeekEnding.ID = dateID ORDER BY EndOfWeekDate ASC";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Parameters.AddWithValue("dateID", dateID);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            command.Connection = connection;
            connection.Open();
            adapter.Fill(table);
            connection.Close();
            table = ExecuteQuery(command);
            dateString = table.Rows[0][0].ToString();

            return dateString;
        }

        public string RetrieveGroupIDByString(string groupName)
        {
            string groupID = "";
            string sqlCommand = "SELECT GroupCategory.ID FROM GroupCategory WHERE GroupCategory.GroupDescription = groupString";
            var connection = new OleDbConnection(gConnectionString);
            var command = new OleDbCommand(sqlCommand, connection);
            command.Parameters.AddWithValue("groupString", groupName);
            try
            {
                var reader = command.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    groupID = reader[0].ToString();
                }
            }
            catch (Exception e)
            {
                return "";
            }
            finally
            {
                connection.Close();
            }

            return groupID;
        }

        public List<string> ReturnGroupNameList()
        {
            string sqlCommand = "SELECT GroupCategory.GroupDescription FROM GroupCategory WHERE NOT GroupDescription = 'HeaderRow' AND NOT GroupDescription = 'MemberRow'";
            var connection = new OleDbConnection(gConnectionString);
            var command = new OleDbCommand(sqlCommand, connection);
            List<string> groupNames = new List<string>();
            try
            {
                connection.Open();
                var reader = command.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    groupNames.Add(reader[0].ToString());
                }
            }
            catch (Exception e)
            {
                return groupNames;
            }
            finally
            {
                connection.Close();
            }

            return groupNames;
        }

        public AutoCompleteStringCollection GetAdItemsSuggestionList()
        {
            AutoCompleteStringCollection autoCompleteList = new AutoCompleteStringCollection();
            var command = "SELECT AdItem.AdItem FROM AdItem ORDER BY AdItem.AdItem ASC";

            DataTable table = new DataTable();
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand commandObject = new OleDbCommand(command, connection);
            commandObject.Connection.Open();
            OleDbDataAdapter a = new OleDbDataAdapter(commandObject);
            a.Fill(table);
            commandObject.Connection.Close();
            int i = 0;
            foreach (DataRow row in table.Rows)
            {
                if (row[0].ToString() != "")
                {
                    autoCompleteList.Add(row[0].ToString());
                }

                i++;
            }
            return autoCompleteList;
        }

        public AutoCompleteStringCollection GetSupplierSuggestionList()
        {
            AutoCompleteStringCollection autoCompleteList = new AutoCompleteStringCollection();
            string command = "SELECT SupplierName FROM Supplier ORDER BY SupplierName ASC";

            DataTable table = new DataTable();
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand commandObject = new OleDbCommand(command, connection);
            commandObject.Connection.Open();
            OleDbDataAdapter a = new OleDbDataAdapter(commandObject);
            a.Fill(table);
            commandObject.Connection.Close();
            int i = 0;
            foreach (DataRow row in table.Rows)
            {
                if (row[0].ToString() != "")
                {
                    autoCompleteList.Add(row[0].ToString());
                }

                i++;
            }
            return autoCompleteList;
        }
        public DataTable ReturnProjectionsTable(string dateID)
        {
            DataTable dataTable = new DataTable();
            string sqlCommand = "SELECT AdItem.AdItem, APC.ProjectionSold, APC.ProjectionSalePrice, APC.ProjectionTotalSales, APC.ProjectionCost, APC.ProjectionProfitReturn, APC.ProjectionTotalProfitReturn, FK_GroupID FROM (AdItem INNER JOIN APC ON AdItem.ID = APC.FK_AdItemID) WHERE FK_DateID = DateID";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(sqlCommand, connection);
            command.Parameters.Add(new OleDbParameter("DateID", dateID));
            dataTable = ExecuteQuery(command);

            return dataTable;
        }

        public DataTable ReturnActualSales(string dateID)
        {
            DataTable dataTable = new DataTable();
            string selectString = "SELECT AdItem.AdItem, APC.ActualSold, APC.ActualSalePrice, APC.ActualTotalSales, APC.ActualCost, APC.ActualProfitReturn, APC.ActualTotalProfitReturn, FK_GroupID FROM (AdItem INNER JOIN APC ON AdItem.ID = APC.FK_AdItemID) WHERE FK_DateID = DateID";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Parameters.Add(new OleDbParameter("DateID", dateID));
            dataTable = ExecuteQuery(command);
            return dataTable;
        }

        public DataTable ReturnInventoryTable(string dateID)
        {
            DataTable dataTable = new DataTable();
            string selectString = "SELECT AdItem.AdItem, APC.BeginingInventory, APC.Received, APC.TotalInventory, APC.EndingInventory, FK_GroupID FROM (AdItem INNER JOIN APC ON AdItem.ID = APC.FK_AdItemID) WHERE FK_DateID = DateID";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Parameters.Add(new OleDbParameter("DateID", dateID));
            dataTable = ExecuteQuery(command);
            return dataTable;
        }

        public DataTable ReturnGroupIDTable(string dateID)
        {
            DataTable dataTable = new DataTable();
            string[] groupNameCollection;
            string selectString = "SELECT APC.FK_GroupID FROM APC WHERE FK_DateID = DateID";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Parameters.Add(new OleDbParameter("DateID", dateID));

            try
            {

            }
            catch (Exception e)
            {

            }
            finally
            {
                connection.Close();
            }

            return dataTable;
        }

        public string ReturnGroupNameFromGroupId(string id)
        {
            string name = "";
            string selectString = "SELECT GroupDescription FROM GroupCategory WHERE ID = ?";
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            OleDbCommand command = new OleDbCommand(selectString, connection);
            command.Parameters.AddWithValue("GroupID", id);

            try
            {
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    name = reader[0].ToString();
                }
            }
            catch (Exception e)
            {

            }
            finally
            {
                connection.Close();
            }

            return name;
        }

        public DataTable ReturnInvoiceTable(string dateID)
        {
            DataTable dataTable = new DataTable();
            OleDbCommand oleDbCommand = new OleDbCommand();
            oleDbCommand.CommandText = "SELECT FK_Supplier, InvoiceNumber, InvoiceDate, InvoiceNetAmountAtCost, InvoiceNetAmount, InvoiceNote FROM Invoice WHERE FK_DateID = ?";
            OleDbConnection oleDbConnection = new OleDbConnection(gConnectionString);
            oleDbCommand.Connection = oleDbConnection;
            oleDbCommand.Parameters.AddWithValue("DateID", dateID);
            try
            {
                OleDbDataAdapter adapater = new OleDbDataAdapter(oleDbCommand);
                int index = 0;
                oleDbCommand.Connection.Open();
                adapater.Fill(dataTable);
                string[] supplierNames = new string[dataTable.Rows.Count];
                foreach (DataRow row in dataTable.Rows)
                {
                    supplierNames[index] = RetrieveSupplierNameByID(row[0].ToString());
                    index++;
                }
                dataTable.Columns.RemoveAt(0);
                dataTable.Columns.Add("SupplierName");
                dataTable.Columns[5].SetOrdinal(0);
                dataTable.Columns[0].DataType = typeof (string);
                index = 0;
                foreach (string name in supplierNames)
                {
                    dataTable.Rows[index][0] = name;
                    index++;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                oleDbCommand.Connection.Close();
            }

            return dataTable;
        }
        /// <summary>
        /// Infrastructure
        /// </summary>
        /// <param name="supplierID"></param>
        /// <returns></returns>
        private string RetrieveSupplierNameByID(string supplierID)
        {
            string supplierName = "";
            OleDbCommand oleDbCommand = new OleDbCommand();
            oleDbCommand.CommandText = "SELECT SupplierName FROM Supplier WHERE ID = ?";
            oleDbCommand.Parameters.AddWithValue("ID", supplierID);
            OleDbConnection oleDbConnection = new OleDbConnection(gConnectionString);
            oleDbCommand.Connection = oleDbConnection;
            oleDbCommand.Connection.Open();
            OleDbDataReader reader = oleDbCommand.ExecuteReader();

            while (reader != null && reader.Read())
            {
                supplierName = reader[0].ToString();
            }
            oleDbConnection.Close();
            return supplierName;
        }

        public DataTable ReturnWeeklySalesFromDateID(string dateID)
        {
            DataTable dataTable = new DataTable();
            OleDbCommand oleDbCommand = new OleDbCommand();
            oleDbCommand.CommandText = "SELECT Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, TotalSales FROM WeeklySales WHERE FK_DateID = ?";
            oleDbCommand.Parameters.AddWithValue("dateID", dateID);
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            oleDbCommand.Connection = connection;
            OleDbDataAdapter a = new OleDbDataAdapter(oleDbCommand);
            try
            {
                oleDbCommand.Connection.Open();
                a.Fill(dataTable);
            }
            catch (Exception e)
            {
                console.WriteToLog(LogConsole.Level.Error, e.Message);
            }
            finally
            {

                oleDbCommand.Connection.Close();
            }

            return dataTable;
        }
        
        public DataTable ReturnWeekEndingTable()
        {
            var weekendingDatesTeable = new DataTable();
            var oleDbCommand = new OleDbCommand {CommandText = "SELECT EndOfWeekDate FROM WeekEnding ORDER BY EndOfWeekDate ASC"};
            var connection = new OleDbConnection(gConnectionString);
            oleDbCommand.Connection = connection;

            try
            {
                var a = new OleDbDataAdapter(oleDbCommand);
                a.Fill(weekendingDatesTeable);
            }
            catch (Exception e)
            {

            }
            finally
            {
                connection.Close();  
            }

            return weekendingDatesTeable;

        }

        public List<string> ReturnUniqueYearsList()
        {
            var datesList = new List<string>();
            var uniqueDateSelection = new OleDbCommand();
            uniqueDateSelection.CommandText =
                "SELECT Distinct YEAR(EndOfWeekDate) FROM WeekEnding";
            var connection = new OleDbConnection(gConnectionString);
            uniqueDateSelection.Connection = connection;

            try
            {
                connection.Open();
                var reader = uniqueDateSelection.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    datesList.Add(reader[0].ToString());
                }
            }
            catch (OleDbException e)
            {
                console.WriteToLog(LogConsole.Level.Error, "An error has occurred trying to retrieve the list of dates.");
                console.WriteToLog(LogConsole.Level.Debug, e.Message);
            }
            finally
            {
                connection.Close();
            }

            return datesList;
        }
        /// <summary>
        /// Returns a list of months available based on the year supplied.
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public List<string> ReturnUniqueMonthsList(string year)
        {
            var datesList = new List<string>();
            var uniqueDateSelection = new OleDbCommand();
            uniqueDateSelection.CommandText =
                "SELECT Distinct MONTH(EndOfWeekDate) FROM WeekEnding WHERE YEAR(EndOfWeekDate) = YEAR(#" + year + "#)";
            var connection = new OleDbConnection(gConnectionString);
            uniqueDateSelection.Connection = connection;

            try
            {
                connection.Open();
                var reader = uniqueDateSelection.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    datesList.Add(reader[0].ToString());
                }

                
            }
            catch (OleDbException e)
            {
                console.WriteToLog(LogConsole.Level.Error, "An error has occurred trying to retrieve the list of dates.");
                console.WriteToLog(LogConsole.Level.Debug, e.Message);
            }
            finally
            {
                connection.Close();
            }

            return datesList;
        } 

        public DateTime ReturnMostRecentDateString()
        {
            DateTime dateString = new DateTime();
            var oleDbCommand = new OleDbCommand();
            oleDbCommand.CommandText = "SELECT WeekEnding.EndOfWeekDate FROM WeekEnding WHERE WeekEnding.EndOfWeekDate = (SELECT MAX(WeekEnding.EndOfWeekDate) FROM WeekEnding)";
            var connection = new OleDbConnection(gConnectionString);
            oleDbCommand.Connection = connection;

            try
            {
                connection.Open();
                var reader = oleDbCommand.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    dateString = (DateTime) reader[0];
                }
            }
            catch (OleDbException e)
            {
                console.WriteToLog(LogConsole.Level.Error, "An error has occurred trying to retrieve the most recent date string.");
                console.WriteToLog(LogConsole.Level.Debug, e.Message);
            }
            finally
            {
                connection.Close();
            }

            return dateString;
        }

        /// <summary>
        /// Pattern Must Be: MM/*/YYYY
        /// </summary>
        /// <param name="dateString"></param>
        /// <returns></returns>
        public DataTable ReturnDateStringsLike(string dateString)
        {
            //Grabbing the year supplied and the last years dates:
            //SELECT EndOfWeekDate FROM WeekEnding WHERE YEAR(DATEADD('yyyy', -1, #" + dateString + "#)) = YEAR(EndOFWeekDate) OR YEAR(#" + dateString + "#) = YEAR(EndOfWeekDate) ORDER BY EndOfWeekDate ASC
            DataTable table = new DataTable();
            OleDbCommand oleDbCommand = new OleDbCommand();
            oleDbCommand.CommandText = "SELECT EndOfWeekDate FROM WeekEnding WHERE YEAR(#" + dateString + "#) = YEAR(EndOFWeekDate) ORDER BY EndOfWeekDate ASC";
            //oleDbCommand.Parameters.AddWithValue("datePattern", dateString);
            OleDbConnection connection = new OleDbConnection(gConnectionString);
            oleDbCommand.Connection = connection;
            connection.Open();
            try
            {
                OleDbDataAdapter a = new OleDbDataAdapter(oleDbCommand);
                a.Fill(table);
            }
            catch (Exception e)
            {

            }
            finally
            {
                connection.Close();
            }

            return table;
        }
    }
}