public List <InterventieConsultatie> GetInterventiiVechiMedic(int medicId)
{
this.conn.Open();
SqlCommand cmd = new SqlCommand("SELECT *, (SELECT C2.Descriere FROM Consultatii C2 WHERE I.ConsultatieID = C2.ID ), (SELECT C1.Data FROM Consultatii C1 WHERE I.ConsultatieID = C1.ID )" +
"FROM Interventii I WHERE I.ConsultatieID IN (SELECT C.ID FROM Consultatii C WHERE C.MedicID = @medicId) AND I.Data < @data ORDER BY I.Data DESC", conn);
cmd.Parameters.AddWithValue("@medicId", medicId);
cmd.Parameters.AddWithValue("@data", DateTime.Now);
using (SqlDataReader reader = cmd.ExecuteReader())
{
var interventii = new List <InterventieConsultatie>();
while (reader.Read())
{
var p = new InterventieConsultatie
{
ID = reader.GetInt32(0),
Nume = reader.GetString(1),
Descriere = reader.GetString(2),
Data = reader.GetDateTime(3).Date,
Pret = reader.IsDBNull(4)? 0 :reader.GetInt32(4),
ConsultatieID = reader.GetInt32(5),
DescriereConsultatie = reader.GetString(6),
DataConsultatie = reader.GetDateTime(7).Date
};
interventii.Add(p);
}
return(interventii);
}
}
public List <InterventieConsultatie> GetInterventiiNoiMedic(int medicId)
{
this.conn.Open();
SqlCommand cmd = new SqlCommand("SELECT I.ID, I.Nume, I.Descriere, I.Data, I.Pret , I.ConsultatieID, C.Descriere, C.Data " +
"FROM Medici M, Consultatii C, Interventii I WHERE M.ID = C.MedicID AND C.ID = I.ConsultatieID AND M.ID = @medicId AND I.Data >= @data ORDER BY I.Data", conn);
cmd.Parameters.AddWithValue("@medicId", medicId);
cmd.Parameters.AddWithValue("@data", DateTime.Now);
using (SqlDataReader reader = cmd.ExecuteReader())
{
var interventii = new List <InterventieConsultatie>();
while (reader.Read())
{
var p = new InterventieConsultatie
{
ID = reader.GetInt32(0),
Nume = reader.GetString(1),
Descriere = reader.GetString(2),
Data = reader.GetDateTime(3).Date,
Pret = reader.GetInt32(4),
ConsultatieID = reader.GetInt32(5),
DescriereConsultatie = reader.GetString(6),
DataConsultatie = reader.GetDateTime(7).Date
};
interventii.Add(p);
}
return(interventii);
}
}
