public static List <Beer> GetBeers(Country co)
{
//list
List <Beer> list = new List <Beer>();
//query
string query = "select be_id, be_grd_alcoh,be_presentation,be_level_ferm,be_unitMeas,be_content,beer.br_code,cla_code,be_price,be_image from beer join brand on beer.br_code= brand.br_code join country on country.cn_code = brand.cn_code where country.cn_code=@CON";
//command
MySqlCommand command = new MySqlCommand(query);
command.Parameters.AddWithValue("@CON", co.Id);
//execute query
DataTable table = MySqlConnection.ExecuteQuery(command);
//iterate rows
foreach (DataRow row in table.Rows)
{
//read fields
int id = (int)row["be_id"];
double gradoalcohol = (double)row["be_grd_alcoh"];
PresentationType presentation = (PresentationType)(int)row["be_presentation"];
Fermentation fermentation = (Fermentation)(int)row["be_level_ferm"];
MeasurementUnit measurementUnit = (MeasurementUnit)row["be_unitMeas"];
double content = (double)row["be_content"];
Brand brand = new Brand((string)row["br_code"]);
Clasification clasification = new Clasification((string)row["cla_code"]);
double price = (double)row["be_price"];
string image = (string)row["be_image"];
//add country to list
list.Add(new Beer(id, gradoalcohol, presentation, fermentation, measurementUnit, content, brand, clasification, price, image));
}
//return list
return(list);
}
/// <summary>
/// Creates an object with data from the databas
/// </summary>
/// <param name="id">Country Id</param>
public Beer(int id)
{
//query
string query = "select be_id, be_grd_alcoh,be_presentation, be_level_ferm,be_unitMeas,be_content,br_code,cla_code,be_price from beer where be_id = @ID";
//command
MySqlCommand command = new MySqlCommand(query);
//parameters
command.Parameters.AddWithValue("@ID", id);
//execute query
DataTable table = MySqlConnection.ExecuteQuery(command);
//check if rows were found
if (table.Rows.Count > 0)
{
//read first and only row
DataRow row = table.Rows[0];
//read data
_id = (int)row["be_id"];
_gradoAlcohol = (double)row["be_grd_alcoh"];
_presentation = (PresentationType)(int)row["be_presentation"];
_fermentation = (Fermentation)row["be_nicel_ferm"];
_measurementUnit = (MeasurementUnit)row["be_unitMeas"];
_content = (double)row["be_content"];
_brand = (Brand)row["br_code"];
_clasification = (Clasification)row["cla_code"];
_price = (double)row["be_price"];
}
}
public Beer()
{
_id = 0;
_gradoAlcohol = 0;
_presentation = new PresentationType();
_fermentation = new Fermentation();
_measurementUnit = new MeasurementUnit();
_content = 0;
_brand = new Brand();
_clasification = new Clasification();
_price = 0;
}
/// <summary>
/// Creates an object with data from the arguments
/// </summary>
/// <param name="id"></param>
/// <param name="name"></param>
public Beer(int id, double gradoalcohol, PresentationType presentation, Fermentation fermentation, MeasurementUnit measurementUnit, double content, Brand brand, Clasification clasification, double price, string image)
{
_id = id;
_gradoAlcohol = gradoalcohol;
_presentation = presentation;
_fermentation = fermentation;
_measurementUnit = measurementUnit;
_content = content;
_brand = brand;
_clasification = clasification;
_price = price;
_image = image;
}
// GET Fermantiation
public List <Fermentation> get_FermentDB()
{
List <Fermentation> fermant_list = new List <Fermentation>();
SqlConnection con = null;
try
{
con = connect("DBConnectionString");
String query = "SELECT * FROM [Fermantation_2020]";
SqlCommand cmd = new SqlCommand(query, con);
cmd.CommandTimeout = 480; // enlarge T.O
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // the connection will close as reading completes
while (dr.Read())
{
Fermentation fr = new Fermentation();
fr.Batchid = Convert.ToInt32(dr["batch_id"]);
fr.Date = Convert.ToDateTime(dr["date"]);
fr.PressureChange = (float)dr["pressure_change"];
fr.Tank_pressure = (float)dr["pressure_tank"];
fr.Tank_temperature = (float)dr["tank_temperature"];
fr.Ferment = (float)dr["ferment"];
fr.Row_num = Convert.ToInt32(dr["index"]);
fermant_list.Add(fr);
}
return(fermant_list);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
/// <summary>
/// Crea un objeto cerveza a partir de sus registro de la base datos, filtrado
/// por su ID.
/// </summary>
/// <param name="id">Beer Id</param>
public Beer(int id)
{
// Cadena de Consulta
string query = "select be_id, be_grd_alcoh,be_presentation, be_level_ferm,"
+ "be_unitMeas,be_content,br_code,cla_code,be_price, be_image, be_level_ferm"
+ " from beer where be_id = @ID";
// Comando MySQL
MySqlCommand command = new MySqlCommand(query);
// Parametros Preparados
command.Parameters.AddWithValue("@ID", id);
// Ejecutamos Consulta
MySqlConnection connection = new MySqlConnection();
connection.ConnectionSource = new AppSettings();
// Guardamos la consulta en una tabla
DataTable table = connection.ExecuteQuery(command);
// Mostramos si la tabla tiene filas (no esta vacia)
if (table.Rows.Count > 0)
{
// Guardamos la primera fila que guarda la informacion
// de este objeto
DataRow row = table.Rows[0];
// Asignamos los datos de la fila a propiedades del objeto
// a crear
_id = (int)row["be_id"];
_gradoAlcohol = (double)row["be_grd_alcoh"];
_presentation = (PresentationType)(int)row["be_presentation"];
_fermentation = (Fermentation)(int)row["be_level_ferm"];
_measurementUnit = (MeasurementUnit)row["be_unitMeas"];
_content = (double)row["be_content"];
_image = (byte[])row["be_image"];
_brand = (new Brand((string)row["br_code"]));
_clasification = (new Clasification((string)row["cla_code"]));
_price = (double)row["be_price"];
}
}
/// <summary>
/// Obtener Cervezas
/// </summary>
/// <returns>Lista de todos los objetos cervezas que hay en la base de datos</returns>
public static List <Beer> GetAll()
{
// Lista para guardar las cervezas de la base de datos
List <Beer> list = new List <Beer>();
// Consulta
string query = "select be_id, be_grd_alcoh,be_presentation,be_level_ferm,be_unitMeas,be_content,br_code,cla_code,be_price,be_image from beer";
// Comando
MySqlCommand command = new MySqlCommand(query);
// Ejecutar Consulta
MySqlConnection connection = new MySqlConnection();
connection.ConnectionSource = new AppSettings();
// Tabla
DataTable table = connection.ExecuteQuery(command);
// Iteramos filas de la consulta para asignar valores
foreach (DataRow row in table.Rows)
{
// Asignamos valores
int id = (int)row["be_id"];
double gradoalcohol = (double)row["be_grd_alcoh"];
PresentationType presentation = (PresentationType)(int)row["be_presentation"];
Fermentation fermentation = (Fermentation)(int)row["be_level_ferm"];
MeasurementUnit measurementUnit = (MeasurementUnit)row["be_unitMeas"];
double content = (double)row["be_content"];
Brand brand = new Brand((string)row["br_code"]);
Clasification clasification = new Clasification((string)row["cla_code"]);
double price = (double)row["be_price"];
byte[] image = (byte[])row["be_image"];
// Agregar Cerveza a la lista
list.Add(new Beer(id, gradoalcohol, presentation, fermentation, measurementUnit, content, brand, clasification, price, image));
}
// Regresamos lista de cerveza
return(list);
}
public HttpResponseMessage Put([FromBody] Fermentation ferm)
{
int numEffected = 0;
try
{
numEffected = ferm.Update();
if (numEffected > 0)
{
return(Request.CreateResponse(HttpStatusCode.OK, numEffected));
}
else
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not Found"));
}
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message));
}
}
public HttpResponseMessage Delete([FromBody] string row_num) // row = row number in DB
{
int numEffected = 0;
Fermentation fer = new Fermentation();
try
{
numEffected = fer.delete_line(Convert.ToInt32(row_num));
if (numEffected > 0)
{
return(Request.CreateResponse(HttpStatusCode.OK, numEffected));
}
else
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Not Found"));
}
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message));
}
}
public static List <Beer> GetBeers(Brand br)
{
//list
List <Beer> list = new List <Beer>();
//query
string query = "select be_id, be_grd_alcoh,be_presentation,be_level_ferm,be_unitMeas,be_content,br_code,cla_code,be_price from beer where br_code=@BRD";
//command
MySqlCommand command = new MySqlCommand(query);
command.Parameters.AddWithValue("@BRD", br.Id);
//execute query
MySqlConnection connection = new MySqlConnection();
connection.ConnectionSource = new AppSettings();
DataTable table = connection.ExecuteQuery(command);
//iterate rows
foreach (DataRow row in table.Rows)
{
//read fields
int id = (int)row["be_id"];
double gradoalcohol = (double)row["be_grd_alcoh"];
PresentationType presentation = (PresentationType)(int)row["be_presentation"];
Fermentation fermentation = (Fermentation)(int)row["be_level_ferm"];
MeasurementUnit measurementUnit = (MeasurementUnit)row["be_unitMeas"];
double content = (double)row["be_content"];
Brand brand = new Brand((string)row["br_code"]);
Clasification clasification = new Clasification((string)row["cla_code"]);
double price = (double)row["be_price"];
byte[] image = (byte[])row["be_image"];
//add beer to list
list.Add(new Beer(id, gradoalcohol, presentation, fermentation, measurementUnit, content, brand, clasification, price, image));
}
//return list
return(list);
}
public int Post([FromBody] Fermentation fr)
{
return(fr.insert());
}
public List <Fermentation> Get()
{
Fermentation fermant = new Fermentation();
return(fermant.get_Fermant());
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
Beer beer = new Beer
{
Id = Guid.NewGuid().ToString(),
Name = "Aguila",
Tagline = "",
Description = "",
Abv = 0.0f,
Ibu = 0.0f,
TargetFG = 0.0f,
TargetOG = 0.0f,
Ebc = 0.0f,
Ph = 0.0f,
ImageUrl = "",
AttenuationLevel = 0.0f,
FirstBrewed = new DateTime(2007, 4, 1),
ContributedBy = "John Doe",
VolumeValue = 0.0f,
VolumeUnit = Unit.litres,
BoilVolumeValue = 0.0f,
BoilVolumeUnit = Unit.litres,
BrewerTips = "",
};
List <FoodDescription> foodDescription = new List <FoodDescription>
{
new FoodDescription {
Id = Guid.NewGuid().ToString(), BeerId = beer.Id, Description = "Lorem ipsum Dolor Sit Amet"
}
};
Ingredients ingredients = new Ingredients
{
Id = Guid.NewGuid().ToString(),
Yeast = "Blah Blah Blah"
};
List <Malt> malt = new List <Malt>
{
new Malt {
Id = Guid.NewGuid().ToString(), IngredientId = ingredients.Id, Name = "Derp", AmountValue = 0.0f, AmountUnit = Unit.grams
}
};
List <Hop> hops = new List <Hop>
{
new Hop {
Id = Guid.NewGuid().ToString(), IngredientId = ingredients.Id, Name = "Dorp", AmountValue = 0.0f, AmountUnit = Unit.grams, Add = "start", Attribute = "glitter"
}
};
Method method = new Method
{
Id = Guid.NewGuid().ToString(),
BeerId = beer.Id,
Twist = "Yaddah yaddah yaddah"
};
List <TempDuration> tempDuration = new List <TempDuration>
{
new TempDuration {
Id = Guid.NewGuid().ToString(), MethodId = method.Id, Duration = 4, TempValue = 1.0f, TempUnit = Unit.celsius
}
};
Fermentation fermentation = new Fermentation {
Id = Guid.NewGuid().ToString(), MethodId = method.Id, TempValue = 3.0f, TempUnit = Unit.celsius
};
modelBuilder.Entity <Beer>().HasData(new[] { beer });
modelBuilder.Entity <FoodDescription>().HasData(foodDescription);
modelBuilder.Entity <Ingredients>().HasData(new[] { ingredients });
modelBuilder.Entity <Malt>().HasData(malt);
modelBuilder.Entity <Hop>().HasData(hops);
modelBuilder.Entity <Method>().HasData(method);
modelBuilder.Entity <TempDuration>().HasData(tempDuration);
modelBuilder.Entity <Fermentation>().HasData(fermentation);
}
