public DataTable DisponibilidadeAgenda(DateTime dtaIni, DateTime dtaFim, int profissional)
{
var dt = new DataTable();
DateTime dtaIniAux = dtaIni;
DateTime dtaFimAux = dtaFim;
//var cmdAux = new SqlCommand();
if (Conexao.conectarBanco())
{
TimeSpan hrIni;
TimeSpan hrFim;
string intervalo;
dt.Columns.Add("Data", typeof(DateTime));
dt.Columns.Add("DiaSemana", typeof(int));
dt.Columns.Add("NomeDiaSemana", typeof(string));
dt.Columns.Add("Horario", typeof(TimeSpan));
dt.Columns.Add("QtdVagas", typeof(int));
using (var cmd = new SqlCommand())
{
cmd.Connection = Conexao.con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT F.COD_FUNCIONARIO, F.NOM_FUNCIONARIO, CHA.DIA_SEMANA, " +
" CHA.DTA_INICIO_VIGENCIA, CHA.DTA_FIM_VIGENCIA, CHA.HR_INI_ATENDIMENTO, " +
" CHA.HR_FIM_ATENDIMENTO, CHA.IDF_ATIVA, CHA.QTD_VAGAS_PERMITIDAS, " +
" CHA.TMP_INTERVALO FROM TBL_FUNCIONARIO F, TBL_CONFIG_AGENDA_MEDICO CAM, " +
" TBL_CONFIG_HORARIO_AGENDA CHA WHERE CHA.COD_AGENDA_MEDICO = CAM.COD_AGENDA_MEDICO " +
" AND CAM.COD_FUNCIONARIO = F.COD_FUNCIONARIO AND CHA.DTA_INICIO_VIGENCIA <= @DTAINI " +
" AND CHA.DTA_FIM_VIGENCIA >= @DTAFIM AND CAM.COD_FUNCIONARIO = @COD_FUNCIONARIO ";
cmd.Parameters.AddWithValue("@DTAINI", dtaIni);
cmd.Parameters.AddWithValue("@DTAFIM", dtaFim);
cmd.Parameters.AddWithValue("@COD_FUNCIONARIO", profissional);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
dtaIniAux = dtaIni;
while (dtaIniAux <= dtaFimAux)
{
//int diaSemana = ;
//int j = (int)reader["DIA_SEMANA"];
if ((int)dtaIniAux.DayOfWeek + 1 == (int)reader["DIA_SEMANA"])
{
hrIni = (TimeSpan)reader["HR_INI_ATENDIMENTO"];
hrFim = (TimeSpan)reader["HR_FIM_ATENDIMENTO"];
intervalo = reader["TMP_INTERVALO"].ToString();
while (hrIni <= hrFim)
{
if (horarioDisponivel(dtaIniAux, hrIni, profissional))
{
DataRow row = dt.NewRow();
row["Data"] = dtaIniAux;
row["DiaSemana"] = (int)reader["DIA_SEMANA"];
row["NomeDiaSemana"] = NomeDiaSemana((int)reader["DIA_SEMANA"]);
row["Horario"] = hrIni;
row["QtdVagas"] = (int)reader["QTD_VAGAS_PERMITIDAS"];
dt.Rows.Add(row);
}
//MessageBox.Show(hrIni.ToString(), "Aviso");
hrIni += TimeSpan.Parse(intervalo);
}
}
dtaIniAux = dtaIniAux.AddDays(1);
}
}
cmd.Parameters.Clear();
}
dt.DefaultView.Sort = "Data";
}
}
return(dt);
}
