using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using Selenium;
using System.Threading;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Access = Microsoft.Office.Interop.Access;
using System.Data.OleDb;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.ComponentModel;
using System.Data;
namespace Lune_Automation_Unite
{
    //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
    // ---------- Database_Manager -------------
    // This is the main class of database managment, it handle Access and Excel manipulation
    //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
    public class Database_Manager
    {

        public Excel.Application process = new Excel.Application();
        public Excel.Workbook database_screen_excel;
        public MySqlConnection sql_connect;
        public DataSet conexaoDataSet;
        public MySqlCommand cmd;
        //public Access.Application process_access = new Access.Application();
        //public Access.AllDataAccessPages database_screen_access;
        //public Excel.Application xle = new Excel.Application();
        //public Excel.Workbook xlev;

        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Configuração de Variáveis -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//

        public string type;
        public string sql = "";
        public string sqlTable = "";
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Sets the database to Excel -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public void set_database_excel(string path, string table)
        {
            type = "Excel";
            database_screen_excel = process.Workbooks.Open(path);
            database_screen_excel.Sheets[table].Select();
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Sets the database to SQL -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public void set_database_mysql(string server, string database, string user, string password)
        {
            type = "MySQL";
            conexaoDataSet = new DataSet();
            sql_connect = new MySqlConnection("Server=" + server + "; Database=" + database + "; Uid=" + user + "; Pwd=" + password);
            sql_connect.Open();
            cmd = new MySqlCommand(sql, sql_connect);
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Sets the database to Access -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public void set_database_access(string path, string table)
        {
            type = "Access";
            //cria a conexão com o banco de dados
            OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:Selenium\teste.mdb");

            //cria o objeto command and armazena a consulta SQL
            OleDbCommand aCommand = new OleDbCommand("select * from Tabela1", aConnection);
            aConnection.Open();
            //cria o objeto datareader para fazer a conexao com a tabela
            OleDbDataReader aReader = aCommand.ExecuteReader();
            OleDbDataReader ObjReader = aCommand.ExecuteReader();
            while (aReader.Read())
            {
                aReader.GetString(1);
            }
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Sets the table -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public void change_tables(string table)
        // ok!!
        {
            switch (type)
            {
                case "Excel":
                    database_screen_excel.Sheets[table].Select();
                    break;
                case "MySQL":
                    sqlTable = table;
                    break;

            }
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- send_query - Sends a query to the Database -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public void send_query(string query)
        {
            cmd.CommandText = query;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Query não executada com sucesso, verificar a Query!");
            }

        }

        public void write_output(string output){
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Gets the sheet Name -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public string activeSheetName()
        {
            return database_screen_excel.ActiveSheet.Name.ToString();
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Writes in the Database (SQL) -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public void write_new_output(string output, string table, string fields, string values)
        {
            cmd.CommandText = "INSERT INTO " + table + " (" + fields + "); VALUES (" + values + ");";

            cmd.ExecuteNonQuery();
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Writes in the Database (Excel) -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public void write_output(string output, int row, int col)
        {
            database_screen_excel.Application.Cells[row, col].Value = output;
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Retreives data from the Database -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public string get_data(int row, int col)
        {
            if (database_screen_excel.Application.Cells[row, col].Value == null)
                return "";
            else
                return database_screen_excel.Application.Cells[row, col].Value.ToString();
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Closes connection to the database -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        public void close_database()
        {
            switch (type)
            {
                case "Excel":
                    database_screen_excel.Save();
                    database_screen_excel.Close();
                    break;
            }
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Saves Database Changes -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        internal void save_database()
        {
            database_screen_excel.Save();
        }
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        // ---------- Adds a new row to the database -------------
        //=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=//
        internal void addRow(int testCaserow)
        {
            database_screen_excel.Application.Cells[testCaserow, 1].EntireRow.Insert();
        }
    }
}