using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data.SqlClient;
using BangBang.DAO.model;

namespace BangBang.DAO.controller
{
    public class CtrlObstaculo : DAO
    {
        #region "CRUD

        public void inserir(Obstaculo obstaculo)
        {
            String sql = "INSERT INTO OBSTACULO (NOME, CAMINHO, N_TIROS) VALUES (@NOME, @CAMINHO, @N_TIROS);";

            command = new SqlCommand(sql, conectar());

            par = new SqlParameter("@NOME", obstaculo.nome);
            par.SqlDbType = System.Data.SqlDbType.VarChar;
            command.Parameters.Add(par);

            par = new SqlParameter("@CAMINHO", obstaculo.caminho);
            par.SqlDbType = System.Data.SqlDbType.VarChar;
            command.Parameters.Add(par);

            par = new SqlParameter("@N_TIROS", obstaculo.nTiros);
            par.SqlDbType = System.Data.SqlDbType.Int;
            command.Parameters.Add(par);

            command.ExecuteNonQuery();
        }

        public void alterar(Obstaculo obstaculo)
        {
            String sql = "UPDATE OBSTACULO SET NOME = @NOME, CAMINHO = @CAMINHO, N_TIROS = @N_TIROS "
                + "WHERE COD = @COD";

            command = new SqlCommand(sql, conectar());

            par = new SqlParameter("@COD", obstaculo.cod);
            par.SqlDbType = System.Data.SqlDbType.Int;
            command.Parameters.Add(par);

            par = new SqlParameter("@NOME", obstaculo.nome);
            par.SqlDbType = System.Data.SqlDbType.VarChar;
            command.Parameters.Add(par);

            par = new SqlParameter("@CAMINHO", obstaculo.caminho);
            par.SqlDbType = System.Data.SqlDbType.VarChar;
            command.Parameters.Add(par);

            par = new SqlParameter("@N_TIROS", obstaculo.nTiros);
            par.SqlDbType = System.Data.SqlDbType.Int;
            command.Parameters.Add(par);

            command.ExecuteNonQuery();
        }

        public void excluir(Obstaculo obstaculo)
        {
            String sql = "DELETE FROM OBSTACULO WHERE COD = @COD;";

            command = new SqlCommand(sql, conectar());

            par = new SqlParameter("@COD", obstaculo.cod);
            par.SqlDbType = System.Data.SqlDbType.Int;
            command.Parameters.Add(par);

            command.ExecuteNonQuery();
        }
        #endregion

        #region "Consultas
        public Obstaculo buscarPorCod(int cod)
        {
            String sql = "SELECT COD, NOME, CAMINHO, N_TIROS FROM OBSTACULO WHERE COD = @COD;";
            command = new SqlCommand(sql, conectar());

            par = new SqlParameter("@COD", cod);
            par.SqlDbType = System.Data.SqlDbType.Int;
            command.Parameters.Add(par);

            reader = command.ExecuteReader();

            Obstaculo o;

            if (reader.Read())
            {
                o = new Obstaculo();
                o.cod = Int32.Parse(reader.GetValue(reader.GetOrdinal("COD")).ToString());
                o.nome = reader.GetValue(reader.GetOrdinal("NOME")).ToString();
                o.caminho = reader.GetValue(reader.GetOrdinal("CAMINHO")).ToString();
                o.nTiros = Int32.Parse(reader.GetValue(reader.GetOrdinal("N_TIROS")).ToString());
                return o;
            }
            return null;
        }

        public List<Obstaculo> listar()
        {
            String sql = "SELECT COD, NOME, CAMINHO, N_TIROS FROM OBSTACULO ORDER BY NOME;";
            command = new SqlCommand(sql, conectar());

            reader = command.ExecuteReader();


            Obstaculo o;
            List<Obstaculo> obstaculos = new List<Obstaculo>();

            while (reader.Read())
            {
                o = new Obstaculo();
                o.cod = Int32.Parse(reader.GetValue(reader.GetOrdinal("COD")).ToString());
                o.nome = reader.GetValue(reader.GetOrdinal("NOME")).ToString();
                o.caminho = reader.GetValue(reader.GetOrdinal("CAMINHO")).ToString();
                o.nTiros = Int32.Parse(reader.GetValue(reader.GetOrdinal("N_TIROS")).ToString());
                obstaculos.Add(o);
            }
            return obstaculos;
        }

        public List<Obstaculo> sortear()
        {
            String sql = "SELECT TOP (5) COD, NOME, CAMINHO, N_TIROS FROM OBSTACULO ORDER BY NEWID();";
            command = new SqlCommand(sql, conectar());

            reader = command.ExecuteReader();


            Obstaculo o;
            List<Obstaculo> obstaculos = new List<Obstaculo>();

            while (reader.Read())
            {
                o = new Obstaculo();
                o.cod = Int32.Parse(reader.GetValue(reader.GetOrdinal("COD")).ToString());
                o.nome = reader.GetValue(reader.GetOrdinal("NOME")).ToString();
                o.caminho = reader.GetValue(reader.GetOrdinal("CAMINHO")).ToString();
                o.nTiros = Int32.Parse(reader.GetValue(reader.GetOrdinal("N_TIROS")).ToString());
                obstaculos.Add(o);
            }
            return obstaculos;
        }

        #endregion
    }
}