namespace GMLL00ImportadorXsd.DAO
{
    using System.Collections.ObjectModel;
    using System.Data;
    using System.Data.SqlClient;
    using Negocio;

    /// <summary>
    /// Clase que encapsula el acceso a la tabla PNETDESA.TPNETTAAUX.
    /// </summary>
    public sealed class TablasAuxiliares
    {
        private readonly string cadenaConexion;

        public TablasAuxiliares(string cadenaConexion)
        {
            this.cadenaConexion = cadenaConexion;
        }

        public void Insertar(TablaAuxiliar tablaAuxiliar)
        {
            using (var connection = new SqlConnection(this.cadenaConexion))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.CommandText = "PPNETTAAUXINSERTAR";

                    command.Parameters.AddRange(new[]
                                                {
                                                    new SqlParameter
                                                    {
                                                        Direction = ParameterDirection.Input,
                                                        ParameterName = "TDESCRC",
                                                        Size = 50,
                                                        SqlDbType = SqlDbType.VarChar,
                                                        Value = tablaAuxiliar.DescripcionEspaniol
                                                    },

                                                    new SqlParameter
                                                    {
                                                        Direction = ParameterDirection.Input,
                                                        ParameterName = "TDESCRE",
                                                        Size = 50,
                                                        SqlDbType = SqlDbType.VarChar,
                                                        Value = tablaAuxiliar.DescripcionEuskera
                                                    }
                                                });

                    command.AddParametrosControlErrores();

                    connection.Open();

                    command.ExecuteNonQuery();

                    command.CheckError();
                }
            }
        }

        public void Actualizar(TablaAuxiliar tablaAuxiliar)
        {
            using (var connection = new SqlConnection(this.cadenaConexion))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.CommandText = "PPNETTAAUXACTUALIZAR";

                    command.Parameters.AddRange(new[]
                                                    {
                                                        new SqlParameter
                                                            {
                                                                Direction = ParameterDirection.Input,
                                                                ParameterName = "TCODIGO",
                                                                SqlDbType = SqlDbType.Int,
                                                                Value = tablaAuxiliar.Codigo
                                                            },
                                                        new SqlParameter
                                                            {
                                                                Direction = ParameterDirection.Input,
                                                                ParameterName = "TDESCRC",
                                                                Size = 50,
                                                                SqlDbType = SqlDbType.VarChar,
                                                                Value = tablaAuxiliar.DescripcionEspaniol
                                                            },

                                                            new SqlParameter
                                                            {
                                                                Direction = ParameterDirection.Input,
                                                                ParameterName = "TDESCRE",
                                                                Size = 50,
                                                                SqlDbType = SqlDbType.VarChar,
                                                                Value = tablaAuxiliar.DescripcionEuskera
                                                            }
                });

                    command.AddParametrosControlErrores();

                    connection.Open();

                    command.ExecuteNonQuery();

                    command.CheckError();
                }
            }
        }

        public void Borrar(TablaAuxiliar tablaAuxiliar)
        {
            using (var connection = new SqlConnection(this.cadenaConexion))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.CommandText = "PPNETTAAUXBORRAR";

                    command.Parameters.Add(new SqlParameter
                    {
                        Direction = ParameterDirection.Input,
                        ParameterName = "TCODIGO",
                        SqlDbType = SqlDbType.Int,
                        Value = tablaAuxiliar.Codigo
                    });

                    command.AddParametrosControlErrores();

                    connection.Open();

                    command.ExecuteNonQuery();

                    command.CheckError();
                }
            }
        }

        /// <summary>
        /// Obtener Objetos de la tabla
        /// </summary>
        /// <param name="filtro">Filtro por campos de descripción en euskera y castellano</param>
        /// <returns></returns>
        public Collection<TablaAuxiliar> Recuperar(string filtro)
        {
            var tablasAuxiliares = new Collection<TablaAuxiliar>();

            using (var connection = new SqlConnection(this.cadenaConexion))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.CommandText = "PPNETTAAUXRECUPERAR";

                    command.Parameters.Add(new SqlParameter
                    {
                        Direction = ParameterDirection.Input,
                        ParameterName = "FILTRO",
                        Size = 50,
                        SqlDbType = SqlDbType.VarChar,
                        Value = filtro
                    });

                    command.AddParametrosControlErrores();

                    connection.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var tablaAuxiliar = new TablaAuxiliar
                            {
                                Codigo = reader.GetInt32(0),
                                DescripcionEspaniol = reader.GetString(1),
                                DescripcionEuskera = reader.GetString(2),
                            };

                            tablasAuxiliares.Add(tablaAuxiliar);
                        }
                    }

                    command.CheckError();
                }
            }

            return tablasAuxiliares;
        }
    }
}