using System.Collections.Generic;
using System.Data.SqlClient;

using Baza_Reklam.Classes.Model;

namespace Baza_Reklam.Classes.Repositories
{
    public class ModuleRepository : Repository<Module>
    {
        public ModuleRepository(string connectionString)
            : base(connectionString)
        {
        }

        public Module FindByName(string name, int sectionId)
        {
            string query =
                "SELECT M.Id, M.MOD_TYP, CAST(M.MOD_SZER AS decimal(9,2)), CAST(M.MOD_WYS AS decimal(9,2)), M.Cena, M.CENA_MIN, M.opis FROM dbo.[NAZWY MODUŁÓW] M INNER JOIN dbo.SekcjeModuly SM ON SM.ModulId=M.Id INNER JOIN dbo.Grzbiety S ON S.Id=SM.SekcjaId WHERE S.Id={0} AND M.MOD_TYP='{1}'";
            query = string.Format(query, sectionId, name);

            Module module = null;
            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);
                _reader = _command.ExecuteReader();
                if (_reader != null)
                {
                    if (_reader.Read())
                    {
                        module = new Module(_reader.GetInt32(0), _reader.GetString(1));
                        if (!_reader.IsDBNull(2))
                            module.Width = _reader.GetDecimal(2);
                        if (!_reader.IsDBNull(3))
                            module.Height = _reader.GetDecimal(3);
                        if (!_reader.IsDBNull(4))
                            module.Price = _reader.GetDecimal(4);
                        if (!_reader.IsDBNull(5))
                            module.MinPrice = _reader.GetDecimal(5);

                        module.Description = _reader.IsDBNull(6) ? string.Empty : _reader.GetString(6);
                    }
                }
            }
            finally
            {
                CleanUp();

            }
            return module;
        }


        public List<Module> FindAllActiveBySection(Section section)
        {
            string query = "SELECT M.Id, M.MOD_TYP, CAST(M.MOD_SZER AS decimal(9,2)), CAST(M.MOD_WYS AS decimal(9,2)), M.Cena, M.CENA_MIN, M.opis FROM dbo.[NAZWY MODUŁÓW] M INNER JOIN dbo.SekcjeModuly SM ON SM.ModulId=M.Id INNER JOIN dbo.Grzbiety S ON S.Id=SM.SekcjaId WHERE S.Id={0} AND S.Active=1 AND M.Active=1";
            query = string.Format(query, section.Id);

            List<Module> modules = new List<Module>();

            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);
                _reader = _command.ExecuteReader();
                if (_reader != null)
                {
                    while (_reader.Read())
                    {
                        Module module = new Module(_reader.GetInt32(0), _reader.GetString(1));
                        if (!_reader.IsDBNull(2))
                            module.Width = _reader.GetDecimal(2);
                        if (!_reader.IsDBNull(3))
                            module.Height = _reader.GetDecimal(3);
                        if (!_reader.IsDBNull(4))
                            module.Price = _reader.GetDecimal(4);
                        if (!_reader.IsDBNull(5))
                            module.MinPrice = _reader.GetDecimal(5);

                        module.Description   =  _reader.IsDBNull(6) ? string.Empty : _reader.GetString(6);
                        module.Section = section;

                        modules.Add(module);
                    }
                }
            }
            finally
            {
                CleanUp();

            }
            return modules;
        }
    }
}
