/// <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 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 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;
}
public override bool Equals(Object p)
{
if (p == null)
return false;
Preparat prep = p as Preparat;
if (this.id_ == prep.id_)
return true;
return false;
}
public bool continePreparat(Preparat preparat)
{
foreach (var comanda in ListaComenzi)
{
if (comanda.ListaItem[preparat.Id] != null)
{
return(true);
}
}
return(false);
}
/// <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;
}
/// <summary>
///Insereaza un nou preparat in baza de date
/// </summary>
public static int insertPreparat(Preparat preparat)
{
SqlConnection insert_preparat_connection = new SqlConnection(connection_string_);
insert_preparat_connection.Open();
SqlCommand insert_preparat_command = new SqlCommand("insert into preparate values(@denumire_preparat, @path, @tip_preparat, @gramaj, @pret, @id_specific, @data_adaugare); SELECT SCOPE_IDENTITY();",
insert_preparat_connection);
insert_preparat_command.Parameters.Add(new SqlParameter(@"denumire_preparat", preparat.Denumire));
if (preparat.PathImagine == null)
insert_preparat_command.Parameters.Add(new SqlParameter(@"path", (object)DBNull.Value));
else
insert_preparat_command.Parameters.Add(new SqlParameter(@"path", preparat.PathImagine));
insert_preparat_command.Parameters.Add(new SqlParameter(@"tip_preparat", preparat.Tip));
insert_preparat_command.Parameters.Add(new SqlParameter(@"gramaj", preparat.Gramaj));
insert_preparat_command.Parameters.Add(new SqlParameter(@"pret", preparat.Pret));
insert_preparat_command.Parameters.Add(new SqlParameter(@"id_specific", getSpecificId(preparat.Specific)));
insert_preparat_command.Parameters.Add(new SqlParameter(@"data_adaugare", preparat.DataAdaugare));
int id_preparat = Convert.ToInt32(insert_preparat_command.ExecuteScalar());
insert_preparat_connection.Close();
return id_preparat;
}
public static List<Preparat> getPotriviriPreparat(Preparat preparat)
{
List<Preparat> lista_preparate = new List<Preparat>();
SqlConnection db_connection_potrivire = new SqlConnection(connection_string_);
db_connection_potrivire.Open();
SqlCommand fetch_potrivire = new SqlCommand(
@"select id_preparat1
from potrivire
where id_preparat2 = @id_preparat
union
select id_preparat2
from potrivire
where id_preparat1 = @id_preparat",
/* @"select case
when id_preparat1 = "+preparat.Id+" then id_preparat2 "+
"when id_preparat2 = " + preparat.Id + " then id_preparat1 " +
" end "+
" from potrivire",*/
//where id_preparat1 = @id_preparat or id_preparat2 = @id_preparat" ,
db_connection_potrivire);
fetch_potrivire.Parameters.Add(new SqlParameter("@id_preparat", preparat.Id));
SqlDataReader data_reader_potrivire = fetch_potrivire.ExecuteReader();
while (data_reader_potrivire.Read())
{
int id_preparat_potrivit = data_reader_potrivire.GetInt32(0);
Preparat preparat_potrivit = getPreparat(id_preparat_potrivit);
if (preparat_potrivit != null)
lista_preparate.Add(preparat_potrivit);
}
data_reader_potrivire.Close();
db_connection_potrivire.Close();
return lista_preparate;
}
// Constructorul necesita doar un preparat. Cantitatea poate fi
// omisa aici si setata apoi prin intermediul setCantitate.
public ItemComanda(Preparat preparat, int cantitate = 0)
{
preparat_ = preparat;
cantitate_ = cantitate;
pret_ = preparat_.Pret * cantitate_;
}