using betrace_carfi_erp_driver.Common;
using betrace_carfi_erp_driver.Components;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using static betrace_carfi_erp_driver.Components.Document;

namespace betrace_carfi_erp_driver
{
    class Database
    {
        private static int nLinesIntegrate = 50;

        public static DataTable GetArticlesToManualIntegrate()
        {
            DataTable articles = new DataTable();
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT TOP(@NLinesIntegrate) * FROM Erp_Integracao_Artigos WHERE intTentativas>0";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@NLinesIntegrate", nLinesIntegrate);
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(articles);
                connection.Close();
                adapter.Dispose();
            }
            return articles;
        }

        internal static Article GetIndividualArticleToManualIntegrate(int id)
        {
            Article article = new Article();
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT TOP(1) * FROM Erp_Integracao_Artigos WHERE intId=@Id";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@Id", id);
                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        article.Id = SqlReaderExtension.GetValue<int>(reader, "intId");
                        article.Abreviation = SqlReaderExtension.GetValue<string>(reader, "strAbreviatura");
                        article.ArticleCode = SqlReaderExtension.GetValue<string>(reader, "strCodArtigo");
                        article.ArticleName = SqlReaderExtension.GetValue<string>(reader, "strNomeArtigo");
                        article.ArticleType = SqlReaderExtension.GetValue<string>(reader, "strTipoArtigo");
                        article.BarCode = SqlReaderExtension.GetValue<string>(reader, "strCodBarras");
                        article.Deleted = SqlReaderExtension.GetValue<bool>(reader, "bitAnulado");
                        article.Devolution = SqlReaderExtension.GetValue<bool>(reader, "bitDevolucao");
                        article.Discount = SqlReaderExtension.GetValue<float>(reader, "fltDesconto");
                        article.Family = SqlReaderExtension.GetValue<string>(reader, "strFamilia");
                        article.GenerateLots = SqlReaderExtension.GetValue<bool>(reader, "bitGereLotes");
                        article.GenerateSerialNumbers = SqlReaderExtension.GetValue<bool>(reader, "bitGereNrsSerie");
                        article.ModificationDate = SqlReaderExtension.GetValue<DateTime>(reader, "dtmAlteracao");
                        article.MovStock = SqlReaderExtension.GetValue<bool>(reader, "bitMovStock");
                        article.Observations = SqlReaderExtension.GetValue<string>(reader, "strObservacoes");
                        article.Provider = SqlReaderExtension.GetValue<string>(reader, "strFornecedor");
                        article.Retention = SqlReaderExtension.GetValue<bool>(reader, "bitRetencao");
                        article.Taxes = SqlReaderExtension.GetValue<string>(reader, "strIVA");
                        article.UnityBuy = SqlReaderExtension.GetValue<string>(reader, "strUnidadeCompra");
                        article.UnityGlobal = SqlReaderExtension.GetValue<string>(reader, "strUnidade");
                        article.UnityIn = SqlReaderExtension.GetValue<string>(reader, "strUnidadeEntrada");
                        article.UnityOut = SqlReaderExtension.GetValue<string>(reader, "strUnidadeSaida");
                        article.UnitySale = SqlReaderExtension.GetValue<string>(reader, "strUnidadeVenda");
                        article.PVP1 = SqlReaderExtension.GetValue<double>(reader, "fltPVP1");
                        article.PVP2 = SqlReaderExtension.GetValue<double>(reader, "fltPVP2");
                        article.PVP3 = SqlReaderExtension.GetValue<double>(reader, "fltPVP3");
                        article.PVP4 = SqlReaderExtension.GetValue<double>(reader, "fltPVP4");
                        article.PVP5 = SqlReaderExtension.GetValue<double>(reader, "fltPVP5");
                        article.PVP6 = SqlReaderExtension.GetValue<double>(reader, "fltPVP6");

                    }
                    connection.Close();
                }
            }
            return article;
        }

        public static DataTable GetDocumentsToManualIntegrate()
        {
            DataTable documents = new DataTable();
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT TOP(@NLinesIntegrate) * FROM Erp_Integracao_Cab WHERE intTentativas > 0";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@NLinesIntegrate", nLinesIntegrate);
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(documents);
                connection.Close();
                adapter.Dispose();
            }
            return documents;
        }
        public static DataTable GetDocumentsLinesToManualIntegrate(int idCab)
        {
            DataTable documentsLines = new DataTable();
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT * FROM Erp_Integracao_Lin WHERE intIdCab = @idCab";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@IdCab", idCab);
                connection.Open();
                SqlDataAdapter adapterLines = new SqlDataAdapter(cmd);
                adapterLines.Fill(documentsLines);
                connection.Close();
                adapterLines.Dispose();
            }
            return documentsLines;
        }

        internal static Cab GetIndividualDocumentToManualIntegrate(int id)
        {
            Cab document = new Cab();
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT TOP(1) * FROM Erp_Integracao_Cab WHERE intId=@Id";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@Id", id);
                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        document.Id = SqlReaderExtension.GetValue<int>(reader, "intId");
                        document.TypeDoc = SqlReaderExtension.GetValue<string>(reader, "strTipoDoc");
                        document.Serie = SystemInfo.ERP_Exercise;
                        document.Date = SqlReaderExtension.GetValue<DateTime>(reader, "dtmDocumento");
                        document.DueDate = SqlReaderExtension.GetValue<DateTime>(reader, "dtmDocumento");
                        document.Entity = SqlReaderExtension.GetValue<string>(reader, "strCliente");
                        document.Article = SqlReaderExtension.GetValue<string>(reader, "strArtigo");
                        document.Quantity = SqlReaderExtension.GetValue<double>(reader, "fltQuantidade");
                        document.DocBeTrace = SqlReaderExtension.GetValue<string>(reader, "strDocBeTrace");
                        document.Observations = SqlReaderExtension.GetValue<string>(reader, "strObservacoes");
                    }

                    connection.Close();
                }
            }
            return document;
        }

        public static List<Article> GetArticlesToIntegrate()
        {
            List<Article> articles = new List<Article>();
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT TOP(@NLinesIntegrate) * FROM Erp_Integracao_Artigos WHERE intTentativas=0";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@NLinesIntegrate", nLinesIntegrate);
                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Article article = new Article();

                        article.Id = SqlReaderExtension.GetValue<int>(reader, "intId");
                        article.Abreviation = SqlReaderExtension.GetValue<string>(reader, "strAbreviatura");
                        article.ArticleCode = SqlReaderExtension.GetValue<string>(reader, "strCodArtigo");
                        article.ArticleName = SqlReaderExtension.GetValue<string>(reader, "strNomeArtigo");
                        article.ArticleType = SqlReaderExtension.GetValue<string>(reader, "strTipoArtigo");
                        article.BarCode = SqlReaderExtension.GetValue<string>(reader, "strCodBarras");
                        article.Deleted = SqlReaderExtension.GetValue<bool>(reader, "bitAnulado");
                        article.Devolution = SqlReaderExtension.GetValue<bool>(reader, "bitDevolucao");
                        article.Discount = SqlReaderExtension.GetValue<float>(reader, "fltDesconto");
                        article.Family = SqlReaderExtension.GetValue<string>(reader, "strFamilia");
                        article.GenerateLots = SqlReaderExtension.GetValue<bool>(reader, "bitGereLotes");
                        article.GenerateSerialNumbers = SqlReaderExtension.GetValue<bool>(reader, "bitGereNrsSerie");
                        article.ModificationDate = SqlReaderExtension.GetValue<DateTime>(reader, "dtmAlteracao");
                        article.MovStock = SqlReaderExtension.GetValue<bool>(reader, "bitMovStock");
                        article.Observations = SqlReaderExtension.GetValue<string>(reader, "strObservacoes");
                        article.Provider = SqlReaderExtension.GetValue<string>(reader, "strFornecedor");
                        article.Retention = SqlReaderExtension.GetValue<bool>(reader, "bitRetencao");
                        article.Taxes = SqlReaderExtension.GetValue<string>(reader, "strIVA");
                        article.UnityBuy = SqlReaderExtension.GetValue<string>(reader, "strUnidadeCompra");
                        article.UnityGlobal = SqlReaderExtension.GetValue<string>(reader, "strUnidade");
                        article.UnityIn = SqlReaderExtension.GetValue<string>(reader, "strUnidadeEntrada");
                        article.UnityOut = SqlReaderExtension.GetValue<string>(reader, "strUnidadeSaida");
                        article.UnitySale = SqlReaderExtension.GetValue<string>(reader, "strUnidadeVenda");
                        article.PVP1 = SqlReaderExtension.GetValue<double>(reader, "fltPVP1");
                        article.PVP2 = SqlReaderExtension.GetValue<double>(reader, "fltPVP2");
                        article.PVP3 = SqlReaderExtension.GetValue<double>(reader, "fltPVP3");
                        article.PVP4 = SqlReaderExtension.GetValue<double>(reader, "fltPVP4");
                        article.PVP5 = SqlReaderExtension.GetValue<double>(reader, "fltPVP5");
                        article.PVP6 = SqlReaderExtension.GetValue<double>(reader, "fltPVP6");
                        articles.Add(article);
                    }

                    connection.Close();
                }
            }
            return articles;
        }

        public static List<Cab> GetDocumentsToIntegrate()
        {
            List<Cab> documents = new List<Cab>();
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT TOP(@NLinesIntegrate) * FROM Erp_Integracao_Cab WHERE intTentativas=0";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@NLinesIntegrate", nLinesIntegrate);
                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Cab document = new Cab();

                        document.Id = SqlReaderExtension.GetValue<int>(reader, "intId");
                        document.TypeDoc = SqlReaderExtension.GetValue<string>(reader, "strTipoDoc");
                        document.Serie = SystemInfo.ERP_Exercise;
                        document.Date = SqlReaderExtension.GetValue<DateTime>(reader, "dtmDocumento");
                        document.DueDate = SqlReaderExtension.GetValue<DateTime>(reader, "dtmDocumento");
                        document.Entity = SqlReaderExtension.GetValue<string>(reader, "strCliente");
                        document.Article = SqlReaderExtension.GetValue<string>(reader, "strArtigo");
                        document.Quantity = SqlReaderExtension.GetValue<double>(reader, "fltQuantidade");
                        document.DocBeTrace = SqlReaderExtension.GetValue<string>(reader, "strDocBeTrace");
                        document.Observations = SqlReaderExtension.GetValue<string>(reader, "strObservacoes");

                        documents.Add(document);
                    }

                    connection.Close();
                }
            }
            return documents;
        }

        public static List<Lin> GetLinesToIntegrate(int id)
        {
            List<Lin> lines = new List<Lin>();
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT * FROM Erp_Integracao_Lin WHERE intIdCab = @IdCab";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@IdCab", id);
                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Lin line = new Lin();

                        line.Article = SqlReaderExtension.GetValue<string>(reader, "strCodArtigo");
                        line.Quantity = SqlReaderExtension.GetValue<double>(reader, "fltQuantidade");
                        line.Warehouse = SqlReaderExtension.GetValue<string>(reader, "strArmazem");
                        line.WarehouseTarget = SqlReaderExtension.GetValue<string>(reader, "strArmazemDestino");
                        line.Lot = SqlReaderExtension.GetValue<string>(reader, "strLote");
                        line.Price = SqlReaderExtension.GetValue<double>(reader, "fltPrecoUnitario");
                        line.Unity = SqlReaderExtension.GetValue<string>(reader, "strUnidade");
                        line.Discount = SqlReaderExtension.GetValue<double>(reader, "fltDesconto");
                        line.DocExterno = SqlReaderExtension.GetValue<string>(reader, "strDocExterno");
                        lines.Add(line);
                    }

                    connection.Close();
                }
            }
            return lines;
        }

        public static bool IncrementAttemptsDocuments(int id)
        {
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "UPDATE Erp_Integracao_Cab SET intTentativas = (COALESCE(intTentativas, 0) + 1) WHERE intId = @IdCab";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@IdCab", id);

                connection.Open();

                cmd.ExecuteNonQuery();

                connection.Close();
            }
            return true;
        }

        public static bool IncrementAttemptsArticles(int id)
        {
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "UPDATE Erp_Integracao_Artigos SET intTentativas = (COALESCE(intTentativas, 0) + 1) WHERE intId = @Id";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@Id", id);

                connection.Open();

                cmd.ExecuteNonQuery();

                connection.Close();
            }
            return true;
        }

        public static bool RemoveQueueDocuments(int id, string docNumber, bool discard = false)
        {
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SP_ERP_Integracao_Driver_RemoveDocumentForLog";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@IdCab", id);
                cmd.Parameters.AddWithValue("@Discard", discard);
                cmd.Parameters.AddWithValue("@DocNumber", docNumber);

                connection.Open();

                cmd.ExecuteNonQuery();

                connection.Close();
            }
            return true;
        }

        public static bool RemoveQueueArticles(int id, bool discard = false)
        {
            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SP_ERP_Integracao_Driver_RemoveArticleForLog";
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@Discard", discard);

                connection.Open();

                cmd.ExecuteNonQuery();

                connection.Close();
            }
            return true;
        }

        public static string PesquisaEmailEnvio()
        {
            string resultado = "";

            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT strEmailDestinatarios FROM Tbl_Emails WHERE strCodAcao = @texto";

                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@texto", "ErroIntegracaoDriver");
                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        resultado = SqlReaderExtension.GetValue<string>(reader, "strEmailDestinatarios");
                    }

                    connection.Close();
                }
            }
            return resultado;
        }

        public string GetArmazemERP(string Artigo)
        {
            string ArmazemErp = string.Empty;

            using (SqlConnection connection = new SqlConnection(SystemInfo.BeTrace_SQL_Database_ConnectionString))
            {
                string query = "SELECT [ArmazemSugestao] FROM [USR_RST3_VIEW_ERP_Artigos] WHERE Artigo = @Artigo";

                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@Artigo", Artigo);
                connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ArmazemErp = SqlReaderExtension.GetValue<string>(reader, "ArmazemSugestao");
                    }

                    connection.Close();
                }
            }

            return ArmazemErp;
        }

    }
}
