/// <summary> /// Intoarce comanda cu id-ul id_comanda /// </summary> private static Comanda getComanda(int id_comanda) { Comanda comanda = new Comanda(); SqlConnection comanda_connection = new SqlConnection(connection_string_); comanda_connection.Open(); SqlCommand comanda_command = new SqlCommand( @"select preparate.id_preparat, denumire_preparat, gramaj, path, pret, denumire_specific, tip_preparat, cantitate, data_adaugare from preparate_comanda join preparate on preparate_comanda.id_preparat=preparate.id_preparat join specific on preparate.id_specific=specific.id_specific where id_comanda = @id_comanda and preparate.id_preparat not in(65,66)", comanda_connection); comanda_command.Parameters.Add(new SqlParameter("@id_comanda", id_comanda)); SqlDataReader comanda_reader = comanda_command.ExecuteReader(); while (comanda_reader.Read()) { int id_preparat = comanda_reader.GetInt32(0); string denumire = comanda_reader.GetString(1); double gramaj = comanda_reader.GetDouble(2); string path = comanda_reader.GetString(3); double pret = comanda_reader.GetDouble(4); string specific = comanda_reader.GetString(5); string tip = comanda_reader.GetString(6); int cantiate = comanda_reader.GetInt32(7); DateTime data_adaugare = comanda_reader.GetDateTime(8); Preparat p = new Preparat(); p.Initialize(id_preparat, denumire, tip, pret, path, gramaj, specific, ingredientePreparat(id_preparat), data_adaugare); comanda.addItemComanda(new ItemComanda(p, cantiate)); } comanda_reader.Close(); comanda_connection.Close(); return comanda; }
/// <summary> /// Intoarce preparatele care au pretul in intervalul[0.7 * pret_mediu, 1.3 * pret_mediu] /// </summary> public static List<Preparat> preparateDupaPret(double pret_mediu) { List<Preparat> preparate = new List<Preparat>(); SqlConnection connection = new SqlConnection(connection_string_); connection.Open(); SqlCommand command = new SqlCommand( @"select id_preparat, denumire_preparat, pret, gramaj, denumire_specific, path, tip_preparat from preparate join specific on preparate.id_specific = specific.id_specific where pret <= 1.2*@pret_mediu and id_preparat not in (65,66)", connection); command.Parameters.Add(new SqlParameter("@pret_mediu",pret_mediu)); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int id = reader.GetInt32(0); string denumire = reader.GetString(1); double pret = reader.GetDouble(2); double gramaj = reader.GetDouble(3); string specific = reader.GetString(4); string path = reader.GetString(5); string tip = reader.GetString(6); Preparat preparat = new Preparat(); preparat.Initialize(id, denumire, tip, pret, path, gramaj, specific, ingredientePreparat(id)); preparate.Add(preparat); } reader.Close(); connection.Close(); return preparate; }
/// <summary> /// Intoarce cele mai comandate k preparate /// </summary> public static List<Preparat> topKPreparate(int k) { List<Preparat> preparate = new List<Preparat>(); SqlConnection connection = new SqlConnection(connection_string_); connection.Open(); SqlCommand command = new SqlCommand( @"select top " + k + " p.id_preparat, denumire_preparat, pret, gramaj, denumire_specific, path, tip_preparat, sum(cantitate) " + "from preparate as p join preparate_comanda as pc on pc.id_preparat=p.id_preparat " + "join specific as s on p.id_specific = s.id_specific " + "where p.id_preparat not in (65,66) "+ "group by p.id_preparat, denumire_preparat, pret, gramaj, denumire_specific, path, tip_preparat " + "order by sum(cantitate) desc", connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int id = reader.GetInt32(0); string denumire = reader.GetString(1); double pret = reader.GetDouble(2); double gramaj = reader.GetDouble(3); string specific = reader.GetString(4); string path = reader.GetString(5); string tip = reader.GetString(6); Preparat preparat = new Preparat(); preparat.Initialize(id, denumire, tip, pret, path, gramaj, specific, ingredientePreparat(id)); preparate.Add(preparat); } reader.Close(); connection.Close(); return preparate; }
/// <summary> /// Intoarce lista de preparate din BD, fara sa ia in considerare /// coloana "data_adaugare". /// </summary> public static List<Preparat> getPreparate() { List<Preparat> lista_preparate = new List<Preparat>(); SqlConnection db_connection_preparate = new SqlConnection(connection_string_); db_connection_preparate.Open(); SqlCommand fetch_preparate = new SqlCommand( @"select id_preparat, denumire_preparat, tip_preparat, path, gramaj, pret, denumire_specific from preparate join specific on ( preparate.id_specific = specific.id_specific) order by case tip_preparat when 'Mic dejun' then 1 when 'Pizza' then 2 when 'Paste' then 3 when 'Aperitiv' then 4 when 'Ciorba si supe' then 5 when 'Fructe de mare' then 6 when 'Peste' then 7 when 'Preparate din carne de pui' then 8 when 'Preparate din carne de porc' then 9 when 'Preparate din carne de vita' then 10 when 'Preparate din vanat' then 11 when 'Salate' then 12 when 'Desert' then 13 when 'Bauturi' then 14 end", db_connection_preparate); SqlDataReader data_reader_preparate = fetch_preparate.ExecuteReader(); // Cat timp se poate citi, citim pe rand atributele // fiecarui preparat din baza de date. while (data_reader_preparate.Read()) { int id = data_reader_preparate.GetInt32(0); string denumire = data_reader_preparate.GetString(1); string tip = data_reader_preparate.GetString(2); string path = null; if (!data_reader_preparate.IsDBNull(3)) { path = data_reader_preparate.GetString(3); } double gramaj = data_reader_preparate.GetDouble(4); double pret = data_reader_preparate.GetDouble(5); string specific = null; if (!data_reader_preparate.IsDBNull(6)) { specific = data_reader_preparate.GetString(6); } Preparat preparat = new Preparat(); preparat.Initialize(id, denumire, tip, pret, path, gramaj, specific, ingredientePreparat(id)); lista_preparate.Add(preparat); } data_reader_preparate.Close(); db_connection_preparate.Close(); return lista_preparate; }
/// <summary> /// Intoarce cele mai comandate k preparate din lista de specifice data ca parametru /// </summary> public static List<Preparat> topKPreparateSpecific(List<String> specifics, int k) { List<Preparat> preparate = new List<Preparat>(); string lista_specifice=""; int i = 1; foreach (var specific in specifics) { lista_specifice += "@specific"+i+","; i++; } lista_specifice = lista_specifice.Substring(0,lista_specifice.Length-1); SqlConnection connection = new SqlConnection(connection_string_); connection.Open(); SqlCommand command = new SqlCommand( @"select top "+k+" p.id_preparat, denumire_preparat, pret, gramaj, denumire_specific, path, tip_preparat, sum(cantitate) "+ "from preparate as p join preparate_comanda as pc on pc.id_preparat=p.id_preparat "+ "join specific as s on p.id_specific = s.id_specific "+ "where denumire_specific in ("+lista_specifice+") and p.id_preparat not in (65,66)"+ "group by p.id_preparat, denumire_preparat, pret, gramaj, denumire_specific, path, tip_preparat " + "order by sum(cantitate) desc", connection); i = 1; foreach (var specific in specifics) { command.Parameters.Add(new SqlParameter("@specific" + i, specific)); i++; } SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int id = reader.GetInt32(0); string denumire = reader.GetString(1); double pret = reader.GetDouble(2); double gramaj = reader.GetDouble(3); string specific = reader.GetString(4); string path = reader.GetString(5); string tip = reader.GetString(6); Preparat preparat = new Preparat(); preparat.Initialize(id,denumire,tip,pret,path,gramaj,specific,ingredientePreparat(id)); preparate.Add(preparat); } reader.Close(); connection.Close(); return preparate; }
public static Preparat getPreparat(int id_preparat) { Preparat preparat = null; SqlConnection db_connection_preparate = new SqlConnection(connection_string_); db_connection_preparate.Open(); SqlCommand fetch_preparat = new SqlCommand( @"select denumire_preparat, tip_preparat, path, gramaj, pret, denumire_specific from preparate join specific on ( preparate.id_specific = specific.id_specific) where id_preparat = @id_preparat", db_connection_preparate); fetch_preparat.Parameters.Add(new SqlParameter("@id_preparat", id_preparat)); SqlDataReader data_reader_preparat = fetch_preparat.ExecuteReader(); if (data_reader_preparat.Read()) { string denumire = data_reader_preparat.GetString(0); string tip = data_reader_preparat.GetString(1); string path = null; if (!data_reader_preparat.IsDBNull(2)) { path = data_reader_preparat.GetString(2); } double gramaj = data_reader_preparat.GetDouble(3); double pret = data_reader_preparat.GetDouble(4); string specific = null; if (!data_reader_preparat.IsDBNull(5)) { specific = data_reader_preparat.GetString(5); } preparat = new Preparat(); preparat.Initialize(id_preparat, denumire, tip, pret, path, gramaj, specific, ingredientePreparat(id_preparat)); } data_reader_preparat.Close(); db_connection_preparate.Close(); return preparat; }