﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using AgoBasico;
using System.Data;

namespace AgoDAO
{
    public class DiaDA
    {
        public int InserirDia(int pDia, string pMes)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" INSERT INTO Dia (Dia, IDMes ) VALUES (@dia, @mes ) ");
                sql.Append(" SET @ID_DIA = SCOPE_IDENTITY() ");


                List<SqlParameter> parametros = new List<SqlParameter>();

                SqlParameter prm1 = new SqlParameter("@dia", pDia);
                parametros.Add(prm1);
                SqlParameter prm2 = new SqlParameter("@mes", pMes);
                parametros.Add(prm2);

                int retorno = ConexaoSQLServer.Conexao.ExecutarComandoRetornandoID(sql.ToString(), parametros.ToArray(), "@ID_DIA");

                return retorno;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<Dia> consultarDia(Dia pDia)
        {
            try
            {
                List<SqlParameter> parametros = new List<SqlParameter>();
                StringBuilder sql = new StringBuilder("SELECT ID, Dia, IDMes FROM Dia Where 1=1");
                if (pDia.ID != 0)
                {
                    sql.Append(" AND ID = @ID ");
                    parametros.Add(new SqlParameter("@ID", pDia.ID.ToString()));
                }
                if (pDia.D != 0)
                {
                    sql.Append(" AND Dia = @dia");
                    parametros.Add(new SqlParameter("@dia", pDia.D));
                }
                if (pDia.Mes != null)
                {
                    sql.Append(" AND IDMes = @mes");
                    parametros.Add(new SqlParameter("@mes", pDia.Mes.ID));
                }

                DataTable dt = ConexaoSQLServer.Conexao.ExecutarConsulta(sql.ToString(), parametros.ToArray());
                List<Dia> lista = new List<Dia>();
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        Dia d = new Dia();
                        d.Mes.ID = Convert.ToInt32(dt.Rows[i]["IDMes"].ToString());
                        d.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
                        d.D = Convert.ToInt32(dt.Rows[i]["Dia"]);
                        lista.Add(d);
                    }
                }
                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Dia consultarUmDia(Dia pDia)
        {
            try
            {
                Dia d = null;
                List<SqlParameter> parametros = new List<SqlParameter>();
                StringBuilder sql = new StringBuilder("SELECT ID, Dia, IDMes FROM Dia Where 1=1");
                sql.Append(" AND Dia = @dia");
                sql.Append(" AND IDMes = @mes");

                parametros.Add(new SqlParameter("@mes", pDia.Mes.ID));
                parametros.Add(new SqlParameter("@dia", pDia.D));

                DataTable dt = ConexaoSQLServer.Conexao.ExecutarConsulta(sql.ToString(), parametros.ToArray());

                if (dt.Rows.Count > 0)
                {
                    d = new Dia();
                    d.Mes.ID = Convert.ToInt32(dt.Rows[0]["IDMes"].ToString());
                    d.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
                    d.D = Convert.ToInt32(dt.Rows[0]["Dia"]);
                }

                return d;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
