public ActionResult InterogareTrei()
{
List <Interogare1> clienti = new List <Interogare1>();
SqlConnection connection = new SqlConnection("data source = DESKTOP-IBNRKP3\\SQLEXPRESS; initial catalog = BD_Project; integrated security = True");
SqlCommand cmd = new SqlCommand(@"
select top 1 COUNT(c.IdClient), cl.Nume, cl.Prenume, cl.IdClient from Comenzi c
inner join Client cl on cl.IdClient = c.IdClient
group by cl.IdClient, cl.Prenume, cl.Nume
order by COUNT(c.IdClient) desc", connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Interogare1 x = new Interogare1();
Client Y = new Client();
x.count = reader.GetInt32(0);
Y.Nume = reader.GetString(1);
Y.Prenume = reader.GetString(2);
Y.IdClient = reader.GetInt32(3);
x.client = Y;
clienti.Add(x);
}
reader.Close();
return(View(clienti));
}
public ActionResult InterogareUnu(List <Interogare1> x)
{
List <Interogare1> clienti = new List <Interogare1>();
SqlConnection connection = new SqlConnection("data source = DESKTOP-IBNRKP3\\SQLEXPRESS; initial catalog = BD_Project; integrated security = True");
SqlCommand cmd = new SqlCommand(@"
select c1.Nume, c1.Prenume, cl1.Pret from Client c1
inner join Comenzi cl1 on c1.IdClient = cl1.IdClient
where " + x[0].count + @" > (select count(*) from Client c2
inner join Comenzi cl2 on cl2.IdClient = c2.IdClient
where cl1.Pret < cl2.Pret) ", connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Interogare1 interogare = new Interogare1();
Client y = new Client();
Comenzi z = new Comenzi();
y.Nume = reader.GetString(0);
y.Prenume = reader.GetString(1);
z.Pret = reader.GetDecimal(2);
interogare.client = y;
interogare.comanda = z;
clienti.Add(interogare);
}
reader.Close();
return(View(clienti));
}
//clientii cu pretul comenzii > media tuturor comenzilor
public ActionResult InterogareDoi()
{
List <Interogare1> clienti = new List <Interogare1>();
SqlConnection connection = new SqlConnection("data source = DESKTOP-IBNRKP3\\SQLEXPRESS; initial catalog = BD_Project; integrated security = True");
SqlCommand cmd = new SqlCommand(@"
select cl.Nume, cl.Prenume, c.IdComenzi, c.TimpStart, c.TimpStop, c.Pret from Comenzi c
inner join Client cl on cl.IdClient = c.IdClient
where c.Pret > (select avg(Pret) from Comenzi )", connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Interogare1 x = new Interogare1();
Client y = new Client();
Comenzi z = new Comenzi();
y.Nume = reader.GetString(0);
y.Prenume = reader.GetString(1);
z.IdComenzi = reader.GetInt32(2);
z.TimpStart = reader.GetDateTime(3);
z.TimpStop = reader.GetDateTime(4);
z.Pret = reader.GetDecimal(5);
x.client = y;
x.comanda = z;
clienti.Add(x);
}
reader.Close();
return(View(clienti));
}
//top x clienti cu comenzile cele mai scumpe
public ActionResult InterogareUnu()
{
List <Interogare1> y = new List <Interogare1>();
Interogare1 z = new Interogare1();
y.Add(z);
return(View(y));
}
public ActionResult InterogareUnu()
{
List <Interogare1> clienti = new List <Interogare1>();
SqlConnection connection = new SqlConnection("data source = DESKTOP-IBNRKP3\\SQLEXPRESS; initial catalog = BD_Project; integrated security = True");
SqlCommand cmd = new SqlCommand(@"
select co.IdComenzi, c.Nume, c.Prenume, co.Pret from Client c
inner join Comenzi co on co.IdClient = c.IdClient
inner join Comenzi_Biciclete cb on cb.IdComenzi = co.IdComenzi
where co.Pret > 90
group by c.Nume, c.Prenume, co.Pret, co.IdComenzi", connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Interogare1 x = new Interogare1();
Comenzi y = new Comenzi();
Client z = new Client();
y.IdComenzi = reader.GetInt32(0);
z.Nume = reader.GetString(1);
z.Prenume = reader.GetString(2);
y.Pret = reader.GetDecimal(3);
x.client = z;
x.comanda = y;
clienti.Add(x);
}
reader.Close();
return(View(clienti));
}
