public SupplyRequestDetail Get(RestaurantDatabaseSettings ctx, int supplyRequestId, int productId)
{
SupplyRequestDetail result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " +
$"{SupplyRequestDetail.ColumnNames.ProductId}, " +
$"{SupplyRequestDetail.ColumnNames.Count}, " +
$"{SupplyRequestDetail.ColumnNames.Active} " +
$"FROM {SupplyRequestDetail.TableName} " +
$"WHERE {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequestId} " +
$"AND {SupplyRequestDetail.ColumnNames.ProductId} = {productId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
result = new SupplyRequestDetail()
{
SupplyRequestId = Convert.ToInt32(reader[SupplyRequestDetail.ColumnNames.SupplyRequestId]),
ProductId = Convert.ToInt32(reader[SupplyRequestDetail.ColumnNames.ProductId]),
Count = Convert.ToDecimal(reader[SupplyRequestDetail.ColumnNames.Count]),
Active = Convert.ToBoolean(Convert.ToInt16(reader[SupplyRequestDetail.ColumnNames.Active].ToString()))
};
}
reader.Dispose();
}
return(result);
}
public IList <MeasurementUnit> Get(RestaurantDatabaseSettings ctx)
{
IList <MeasurementUnit> result = new List <MeasurementUnit>();
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"{MeasurementUnit.ColumnNames.Id}, " +
$"{MeasurementUnit.ColumnNames.Code}, " +
$"{MeasurementUnit.ColumnNames.Description} " +
$"FROM UnidadMedida";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(new MeasurementUnit()
{
Id = Convert.ToInt32(reader[MeasurementUnit.ColumnNames.Id]),
Code = reader[MeasurementUnit.ColumnNames.Code]?.ToString(),
Description = reader[MeasurementUnit.ColumnNames.Description]?.ToString()
});
}
reader.Dispose();
}
return(result);
}
public Product Edit(RestaurantDatabaseSettings ctx, int productId, Product product)
{
Product result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"UPDATE Producto " +
$"SET " +
$"{Product.ColumnNames.Name} = '{product.Name}', " +
$"{Product.ColumnNames.Details} = '{product.Details}', " +
$"{Product.ColumnNames.ProductTypeId} = {product.ProductTypeId}, " +
$"{Product.ColumnNames.Count} = {product.Count}, " +
$"{Product.ColumnNames.MeasurementUnitId} = {product.MeasurementUnitId}, " +
$"{Product.ColumnNames.Price} = {product.Price}, " +
$"{Product.ColumnNames.ProviderId} = {product.ProviderId} " +
$"WHERE {Product.ColumnNames.Id} = {productId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
result = product;
}
return(result);
}
public MeasurementUnit Get(RestaurantDatabaseSettings ctx, int measurementUnitId)
{
MeasurementUnit result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"{MeasurementUnit.ColumnNames.Id}, " +
$"{MeasurementUnit.ColumnNames.Code}, " +
$"{MeasurementUnit.ColumnNames.Description} " +
$"FROM UnidadMedida " +
$"WHERE {MeasurementUnit.ColumnNames.Id} = {measurementUnitId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
result = new MeasurementUnit()
{
Id = Convert.ToInt32(reader[MeasurementUnit.ColumnNames.Id]),
Code = reader[MeasurementUnit.ColumnNames.Code]?.ToString(),
Description = reader[MeasurementUnit.ColumnNames.Description]?.ToString()
};
}
reader.Dispose();
}
return(result);
}
public SupplyRequestDetail Add(RestaurantDatabaseSettings ctx, SupplyRequestDetail supplyRequestDetail)
{
SupplyRequestDetail result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
conn.Open();
string query = $"INSERT INTO {SupplyRequestDetail.TableName} (" +
$"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " +
$"{SupplyRequestDetail.ColumnNames.ProductId}, " +
$"{SupplyRequestDetail.ColumnNames.Count}, " +
$"{SupplyRequestDetail.ColumnNames.Active} " +
$") VALUES (" +
$"{supplyRequestDetail.SupplyRequestId}, " +
$"{supplyRequestDetail.ProductId}, " +
$"{supplyRequestDetail.Count}, " +
$"{1} " +
$")";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
result = supplyRequestDetail;
}
return(result);
}
public Booking Edit(RestaurantDatabaseSettings ctx, int bookingId, Booking booking)
{
Booking result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"UPDATE Reserva " +
$"SET " +
$"{Booking.ColumnNames.UserId} = {booking.UserId}, " +
$"{Booking.ColumnNames.TableId} = {booking.TableId}, " +
$"{Booking.ColumnNames.Date} = TO_DATE('{booking.Date:ddMMyyyyHHmm}', 'DDMMYYYYHH24MI'), " +
$"{Booking.ColumnNames.Diners} = {booking.Diners}, " +
$"{Booking.ColumnNames.Active} = {(booking.Active ? 1 : 0)} " +
$"WHERE {Booking.ColumnNames.Id} = {bookingId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
booking.Date = booking.Date.ToLocalTime();
result = booking;
}
return(result);
}
public SupplyRequestState GetState(RestaurantDatabaseSettings ctx, int supplyRequestStateId)
{
SupplyRequestState result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"{SupplyRequestState.ColumnNames.Id}, " +
$"{SupplyRequestState.ColumnNames.Description} " +
$"FROM {SupplyRequestState.TableName} sr " +
$"WHERE {SupplyRequestState.ColumnNames.Id} = {supplyRequestStateId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
result = new SupplyRequestState()
{
Id = Convert.ToInt32(reader[$"{SupplyRequestState.ColumnNames.Id}"]),
Description = reader[$"{SupplyRequestState.ColumnNames.Description}"]?.ToString()
};
}
reader.Dispose();
}
return(result);
}
public ProductType Get(RestaurantDatabaseSettings ctx, int productTypeId)
{
ProductType result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"{ProductType.ColumnNames.Id}, " +
$"{ProductType.ColumnNames.Description} " +
$"FROM TipoProducto " +
$"WHERE {ProductType.ColumnNames.Id} = {productTypeId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
result = new ProductType()
{
Id = Convert.ToInt32(reader[ProductType.ColumnNames.Id]),
Description = reader[ProductType.ColumnNames.Description]?.ToString()
};
}
reader.Dispose();
}
return(result);
}
public Recipe Add(RestaurantDatabaseSettings ctx, Recipe recipe)
{
Recipe result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
conn.Open();
OracleTransaction transaction = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
string query = $"INSERT INTO Receta (" +
$"{Recipe.ColumnNames.Name}, " +
$"{Recipe.ColumnNames.Price}, " +
$"{Recipe.ColumnNames.Details}, " +
$"{Recipe.ColumnNames.PreparationTime}, " +
$"{Recipe.ColumnNames.Active} " +
$") VALUES (" +
$"'{recipe.Name}', " +
$"{recipe.Price}, " +
$"'{recipe.Details}', " +
$"{recipe.PreparationTime}, " +
$"{1} " +
$") RETURNING {Recipe.ColumnNames.Id} INTO :{Recipe.ColumnNames.Id}";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OracleParameter()
{
ParameterName = $":{Recipe.ColumnNames.Id}",
OracleDbType = OracleDbType.Decimal,
Direction = System.Data.ParameterDirection.Output
});
try
{
cmd.ExecuteNonQuery();
recipe.Id = Convert.ToInt32(cmd.Parameters[$":{Recipe.ColumnNames.Id}"].Value.ToString());
if (recipe.RecipeDetails != null)
{
List <RecipeDetail> recipeDetails = new List <RecipeDetail>();
foreach (var recipeDetail in recipe.RecipeDetails)
{
RecipeDetail createdRecipeDetail = this.Add(conn, transaction, recipe.Id, recipeDetail);
recipeDetails.Add(createdRecipeDetail);
}
}
transaction.Commit();
result = recipe;
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
return(result);
}
IList <ProductType> IProductTypeService.Get(RestaurantDatabaseSettings ctx)
{
IList <ProductType> result = new List <ProductType>();
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"{ProductType.ColumnNames.Id}, " +
$"{ProductType.ColumnNames.Description} " +
$"FROM TipoProducto";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(new ProductType()
{
Id = Convert.ToInt32(reader[ProductType.ColumnNames.Id]),
Description = reader[ProductType.ColumnNames.Description]?.ToString()
});
}
reader.Dispose();
}
return(result);
}
public Table Get(RestaurantDatabaseSettings ctx, int tableId)
{
Table result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"m.{Table.ColumnNames.Id}, " +
$"m.{Table.ColumnNames.Number}, " +
$"m.{Table.ColumnNames.Capacity}, " +
$"m.{Table.ColumnNames.Description}, " +
$"m.{Table.ColumnNames.Active}, " +
$"m.{Table.ColumnNames.InUse}, " +
$"m.{Table.ColumnNames.UserId}, " +
$"u.{User.ColumnNames.RoleId} AS {User.TableName}{User.ColumnNames.RoleId}, " +
$"u.{User.ColumnNames.Name} AS {User.TableName}{User.ColumnNames.Name}, " +
$"u.{User.ColumnNames.LastName} AS {User.TableName}{User.ColumnNames.LastName}, " +
$"u.{User.ColumnNames.Email} AS {User.TableName}{User.ColumnNames.Email}, " +
$"u.{User.ColumnNames.Phone} AS {User.TableName}{User.ColumnNames.Phone}, " +
$"u.{User.ColumnNames.Address} AS {User.TableName}{User.ColumnNames.Address} " +
$"FROM {Table.TableName} m " +
$"LEFT JOIN {User.TableName} u ON u.{User.ColumnNames.Id} = m.{Table.ColumnNames.UserId} " +
$"WHERE m.{Table.ColumnNames.Id} = {tableId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
result = new Table()
{
Id = Convert.ToInt32(reader[Table.ColumnNames.Id]),
Number = Convert.ToInt32(reader[Table.ColumnNames.Number]),
Capacity = Convert.ToInt32(reader[Table.ColumnNames.Capacity]),
Description = reader[Table.ColumnNames.Description]?.ToString(),
Active = Convert.ToBoolean(reader[Table.ColumnNames.Active]),
InUse = Convert.ToBoolean(reader[Table.ColumnNames.InUse])
};
if (reader[Table.ColumnNames.UserId] != DBNull.Value)
{
result.UserId = Convert.ToInt32(reader[Table.ColumnNames.UserId]);
result.User = new User()
{
Id = (int)result.UserId,
Email = reader[$"{User.TableName}{User.ColumnNames.Email}"]?.ToString(),
Name = reader[$"{User.TableName}{User.ColumnNames.Name}"]?.ToString(),
LastName = reader[$"{User.TableName}{User.ColumnNames.LastName}"]?.ToString(),
Address = reader[$"{User.TableName}{User.ColumnNames.Address}"]?.ToString(),
Phone = reader[$"{User.TableName}{User.ColumnNames.Phone}"]?.ToString(),
RoleId = Convert.ToInt32(reader[$"{User.TableName}{User.ColumnNames.RoleId}"])
};
}
}
reader.Dispose();
}
return(result);
}
public Table Edit(RestaurantDatabaseSettings ctx, int tableId, Table table)
{
Table result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"UPDATE Mesa " +
$"SET {Table.ColumnNames.Number} = {table.Number}, " +
$"{Table.ColumnNames.Capacity} = {table.Capacity}, " +
$"{Table.ColumnNames.Description} = '{table.Description}', " +
$"{Table.ColumnNames.Active} = {(table.Active ? 1 : 0)}, " +
$"{Table.ColumnNames.InUse} = {(table.InUse ? 1 : 0)}, " +
$"{Table.ColumnNames.UserId} = :{Table.ColumnNames.UserId} " +
$"WHERE {Table.ColumnNames.Id} = {tableId}";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.Parameters.Add(new OracleParameter()
{
Value = table.UserId,
ParameterName = $":{Table.ColumnNames.UserId}",
DbType = System.Data.DbType.Int32
});
conn.Open();
cmd.ExecuteNonQuery();
result = table;
}
return(result);
}
public RecipeController(IRecipeService recipeService,
IProductService productService,
IOptions <RestaurantDatabaseSettings> databaseContext)
{
this.recipeService = recipeService;
this.productService = productService;
this.dbSettings = databaseContext.Value;
}
public TableController(ITableService authService,
IUserService userService,
IOptions <RestaurantDatabaseSettings> databaseContext)
{
this.tableService = authService;
this.userService = userService;
this.dbSettings = databaseContext.Value;
}
public UserController(IUserService userService,
IRoleService roleService,
IOptions <RestaurantDatabaseSettings> databaseContext)
{
this.userService = userService;
this.roleService = roleService;
this.dbSettings = databaseContext.Value;
}
public BookingController(IBookingService bookingService,
IUserService userService,
ITableService tableService,
IOptions <RestaurantDatabaseSettings> databaseContext)
{
this.bookingService = bookingService;
this.userService = userService;
this.tableService = tableService;
this.dbSettings = databaseContext.Value;
}
public ProductController(IProductService productService,
IMeasurementUnitService measurementUnitService,
IProductTypeService productTypeService,
IProviderService providerService,
IOptions <RestaurantDatabaseSettings> databaseContext)
{
this.productService = productService;
this.measurementUnitService = measurementUnitService;
this.productTypeService = productTypeService;
this.providerService = providerService;
this.dbSettings = databaseContext.Value;
}
public RestaurantDBContext(IOptions <RestaurantDatabaseSettings> options)
{
this._restaurantDatabaseSettings = options.Value;
MongoClientSettings clientSettings = MongoClientSettings.FromConnectionString(_restaurantDatabaseSettings.ConnectionString);
MongoClient client = new MongoClient(clientSettings);
if (client != null)
{
this.database = client.GetDatabase(_restaurantDatabaseSettings.DatabaseName.Trim());
}
//_restaurant = database.GetCollection<Restaurant>(settings.RestaurantCollectionName);
}
public Product Add(RestaurantDatabaseSettings ctx, Product product)
{
Product result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"INSERT INTO Producto (" +
$"{Product.ColumnNames.Name}, " +
$"{Product.ColumnNames.Details}, " +
$"{Product.ColumnNames.ProductTypeId}, " +
$"{Product.ColumnNames.Count}, " +
$"{Product.ColumnNames.MeasurementUnitId}, " +
$"{Product.ColumnNames.CostPrice}, " +
$"{Product.ColumnNames.SalePrice}, " +
$"{Product.ColumnNames.ProviderId}, " +
$"{Product.ColumnNames.Active} " +
$") VALUES (" +
$"'{product.Name}', " +
$"'{product.Details}', " +
$"{product.ProductTypeId}, " +
$"{product.Count}, " +
$"{product.MeasurementUnitId}, " +
$"{product.CostPrice}, " +
$":{Product.ColumnNames.SalePrice}, " +
$"{product.ProviderId}, " +
$"{1} " +
$") RETURNING {Product.ColumnNames.Id} INTO :{Product.ColumnNames.Id}";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.Parameters.Add(new OracleParameter()
{
Value = product.SalePrice,
ParameterName = $":{Product.ColumnNames.SalePrice}",
DbType = System.Data.DbType.Int32
});
cmd.Parameters.Add(new OracleParameter()
{
ParameterName = $":{Product.ColumnNames.Id}",
OracleDbType = OracleDbType.Decimal,
Direction = System.Data.ParameterDirection.Output
});
conn.Open();
cmd.ExecuteNonQuery();
product.Id = Convert.ToInt32(cmd.Parameters[$":{Product.ColumnNames.Id}"].Value.ToString());
result = product;
}
return(result);
}
public User Edit(RestaurantDatabaseSettings ctx, int userId, User user)
{
User result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"UPDATE Usuario " +
$"SET " +
$"{User.ColumnNames.RoleId} = {user.RoleId}, " +
$"{User.ColumnNames.Name} = '{user.Name}', " +
$"{User.ColumnNames.LastName} = '{user.LastName}', " +
$"{User.ColumnNames.Email} = '{user.Email}', " +
$"{User.ColumnNames.Password} = {(string.IsNullOrWhiteSpace(user.Password) ? User.ColumnNames.Password : $"{user.Password}")}, " +
public User SignIn(RestaurantDatabaseSettings dbSettings, string username, string password)
{
User user = null;
using (OracleConnection conn = new OracleConnection(dbSettings.ConnectionString))
{
string query = $"SELECT " +
$"u.{User.ColumnNames.Id}, " +
$"u.{User.ColumnNames.RoleId}, " +
$"u.{User.ColumnNames.Name}, " +
$"u.{User.ColumnNames.LastName}, " +
$"u.{User.ColumnNames.Email}, " +
$"u.{User.ColumnNames.Phone}, " +
$"u.{User.ColumnNames.Address}, " +
$"r.{Role.ColumnNames.Id} AS Role{Role.ColumnNames.Id}, " +
$"r.{Role.ColumnNames.Description} AS Role{Role.ColumnNames.Description} " +
$"FROM Usuario u " +
$"join Rol r on u.{User.ColumnNames.RoleId} = r.{Role.ColumnNames.Id} " +
$"WHERE LOWER({User.ColumnNames.Email}) = :username AND {User.ColumnNames.Password} = :password";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.Parameters.Add(new OracleParameter("username", username.ToLower()));
cmd.Parameters.Add(new OracleParameter("password", password));
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
user = new User()
{
Id = Convert.ToInt32(reader[User.ColumnNames.Id]),
Email = reader[User.ColumnNames.Email]?.ToString(),
Name = reader[User.ColumnNames.Name]?.ToString(),
LastName = reader[User.ColumnNames.LastName]?.ToString(),
Address = reader[User.ColumnNames.Address]?.ToString(),
Phone = reader[User.ColumnNames.Phone]?.ToString(),
RoleId = Convert.ToInt32(reader[User.ColumnNames.RoleId]),
Role = new Role()
{
Id = Convert.ToInt32(reader[$"Role{Role.ColumnNames.Id}"]),
Description = reader[$"Role{Role.ColumnNames.Description}"]?.ToString()
}
};
}
reader.Dispose();
}
return(user);
}
public PurchaseController(IPurchaseService purchaseService,
IOrderService orderService,
IFlowService flowService,
IUserService userService,
ITableService tableService,
IOptions <RestaurantDatabaseSettings> databaseContext)
{
this.purchaseService = purchaseService;
this.userService = userService;
this.flowService = flowService;
this.tableService = tableService;
this.dbSettings = databaseContext.Value;
this.orderService = orderService;
}
public SupplyRequestController(ISupplyRequestService supplyRequestService,
IProductService productService,
IProviderService providerService,
IMailService mailService,
IUserService userService,
IOptions <RestaurantDatabaseSettings> databaseContext)
{
this.supplyRequestService = supplyRequestService;
this.productService = productService;
this.providerService = providerService;
this.mailService = mailService;
this.userService = userService;
this.dbSettings = databaseContext.Value;
}
public User Add(RestaurantDatabaseSettings ctx, User user)
{
User result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"INSERT INTO Usuario (" +
$"{User.ColumnNames.RoleId}, " +
$"{User.ColumnNames.Name}, " +
$"{User.ColumnNames.LastName}, " +
$"{User.ColumnNames.Email}, " +
$"{User.ColumnNames.Phone}, " +
$"{User.ColumnNames.Address}, " +
$"{User.ColumnNames.Password}, " +
$"{User.ColumnNames.Active} " +
$") VALUES (" +
$"{user.RoleId}, " +
$"'{user.Name}', " +
$"'{user.LastName}', " +
$"'{user.Email}', " +
$"'{user.Phone}', " +
$"'{user.Address}', " +
$"'{user.Password}', " +
$"{1} " +
$") RETURNING {User.ColumnNames.Id} INTO :{User.ColumnNames.Id}";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.Parameters.Add(new OracleParameter()
{
ParameterName = $":{User.ColumnNames.Id}",
OracleDbType = OracleDbType.Decimal,
Direction = System.Data.ParameterDirection.Output
});
conn.Open();
cmd.ExecuteNonQuery();
user.Id = Convert.ToInt32(cmd.Parameters[$":{User.ColumnNames.Id}"].Value.ToString());
result = user;
}
return(result);
}
public bool Delete(RestaurantDatabaseSettings ctx, int tableId)
{
bool result = false;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"UPDATE Mesa " +
$"SET {Table.ColumnNames.Active} = 0 " +
$"WHERE {Table.ColumnNames.Id} = {tableId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
result = true;
}
return(result);
}
public bool Delete(RestaurantDatabaseSettings ctx, SupplyRequestDetail supplyRequestDetail)
{
bool result = false;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
conn.Open();
string query = $"DELETE FROM {SupplyRequestDetail.TableName} " +
$"WHERE {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequestDetail.SupplyRequestId} " +
$"AND {SupplyRequestDetail.ColumnNames.ProductId} = {supplyRequestDetail.ProductId}";
OracleCommand deleteCommand = new OracleCommand(query, conn);
deleteCommand.ExecuteNonQuery();
result = true;
}
return(result);
}
public Booking Add(RestaurantDatabaseSettings ctx, Booking booking)
{
Booking result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
booking.Code = $"{booking.UserId}{booking.TableId}{booking.Date:yyMMddHH}";
string query = $"INSERT INTO Reserva (" +
$"{Booking.ColumnNames.UserId}, " +
$"{Booking.ColumnNames.Code}, " +
$"{Booking.ColumnNames.TableId}, " +
$"{Booking.ColumnNames.Date}, " +
$"{Booking.ColumnNames.Diners}, " +
$"{Booking.ColumnNames.Active} " +
$") VALUES (" +
$"{booking.UserId}, " +
$"{booking.Code}, " +
$"{booking.TableId}, " +
$"TO_DATE('{booking.Date:ddMMyyyyHHmm}', 'DDMMYYYYHH24MI'), " +
$"{booking.Diners}, " +
$"{1} " +
$") RETURNING {Booking.ColumnNames.Id} INTO :{Booking.ColumnNames.Id}";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.Parameters.Add(new OracleParameter()
{
ParameterName = $":{Booking.ColumnNames.Id}",
OracleDbType = OracleDbType.Decimal,
Direction = System.Data.ParameterDirection.Output
});
conn.Open();
cmd.ExecuteNonQuery();
booking.Id = Convert.ToInt32(cmd.Parameters[$":{Booking.ColumnNames.Id}"].Value.ToString());
booking.Date = booking.Date.ToLocalTime();
result = booking;
}
return(result);
}
public IList <Recipe> Get(RestaurantDatabaseSettings ctx)
{
IList <Recipe> result = new List <Recipe>();
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"{Recipe.ColumnNames.Id}, " +
$"{Recipe.ColumnNames.Name}, " +
$"{Recipe.ColumnNames.Price}, " +
$"{Recipe.ColumnNames.Details}, " +
$"{Recipe.ColumnNames.PreparationTime}, " +
$"{Recipe.ColumnNames.Image}, " +
$"{Recipe.ColumnNames.Active} " +
$"FROM {Recipe.TableName} " +
$"WHERE {Recipe.ColumnNames.Active} = 1";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Recipe recipe = new Recipe()
{
Id = Convert.ToInt32(reader[Recipe.ColumnNames.Id]),
Name = reader[Recipe.ColumnNames.Name]?.ToString(),
Price = Convert.ToDecimal(reader[Recipe.ColumnNames.Price]),
Details = reader[Recipe.ColumnNames.Details]?.ToString(),
PreparationTime = Convert.ToDecimal(reader[Recipe.ColumnNames.PreparationTime]),
ImageBase64 = reader[Recipe.ColumnNames.Image]?.ToString(),
Active = Convert.ToBoolean(Convert.ToInt16(reader[Recipe.ColumnNames.Active].ToString()))
};
recipe.RecipeDetails = this.Get(conn, recipe).ToList();
result.Add(recipe);
}
reader.Dispose();
}
return(result);
}
public Table Add(RestaurantDatabaseSettings ctx, Table table)
{
Table result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"INSERT INTO Mesa " +
$"({Table.ColumnNames.Number}, " +
$"{Table.ColumnNames.Capacity}, " +
$"{Table.ColumnNames.Description}, " +
$"{Table.ColumnNames.Active}, " +
$"{Table.ColumnNames.InUse}) " +
$"VALUES " +
$"({table.Number}, " +
$"{table.Capacity}, " +
$"'{table.Description}', " +
$"{(table.Active ? 1 : 0)}, " +
$"0) " +
$"RETURNING {Table.ColumnNames.Id} INTO :{Table.ColumnNames.Id}";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.Parameters.Add(new OracleParameter()
{
ParameterName = $":{Table.ColumnNames.Id}",
OracleDbType = OracleDbType.Decimal,
Direction = System.Data.ParameterDirection.Output
});
conn.Open();
cmd.ExecuteNonQuery();
table.Id = Convert.ToInt32(cmd.Parameters[$":{Table.ColumnNames.Id}"].Value.ToString());
result = table;
}
return(result);
}
public Provider Edit(RestaurantDatabaseSettings ctx, int providerId, Provider provider)
{
Provider result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"UPDATE Proveedor " +
$"SET " +
$"{Provider.ColumnNames.Name} = '{provider.Name}', " +
$"{Provider.ColumnNames.Email} = '{provider.Email}', " +
$"{Provider.ColumnNames.Phone} = '{provider.Phone}', " +
$"{Provider.ColumnNames.Address} = '{provider.Address}' " +
$"WHERE {Provider.ColumnNames.Id} = {providerId}";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
result = provider;
}
return(result);
}
