public IHttpActionResult PostSupplyRequest([FromBody] SupplyRequestJson supplyRequestJson)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
// Add Supply Request
SupplyRequest request = new SupplyRequest()
{
tenantId = supplyRequestJson.TenantId,
active = true
};
db.SupplyRequests.Add(request);
// Add Supply Request Maps
foreach (var item in supplyRequestJson.RequestSuppliesMaps)
{
RequestSuppliesMap map = new RequestSuppliesMap
{
suppliesRequestId = request.supplyRequestId,
supplyId = item.SupplyId
};
db.RequestSuppliesMaps.Add(map);
}
db.SaveChanges();
return(CreatedAtRoute("DefaultApi", new { id = request.supplyRequestId }, request));
}
public IActionResult Post([FromBody] SupplyRequest supplyRequest)
{
IActionResult result;
try
{
// always create a new supply request as created
supplyRequest.StateId = (int)Enums.SupplyRequestState.Created;
var created = this.supplyRequestService.Add(this.dbSettings, supplyRequest);
if (created.SupplyRequestDetails != null && created.SupplyRequestDetails.Any())
{
var products = this.productService.Get(this.dbSettings).Where(x => x.Active).ToList();
foreach (var supplyRequestDetail in created.SupplyRequestDetails)
{
supplyRequestDetail.Product = products.FirstOrDefault(x => x.Id == supplyRequestDetail.ProductId);
}
}
created.State = this.supplyRequestService.GetState(this.dbSettings, created.StateId);
created.Provider = this.providerService.Get(this.dbSettings, created.ProviderId);
result = Ok(created.Map(this.dbSettings, true));
}
catch (Exception ex)
{
result = BadRequest(ex.Message);
}
return(result);
}
public IHttpActionResult PutSupplyRequest(int id, [FromBody] SupplyRequest supplyRequest)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != supplyRequest.supplyRequestId)
{
return(BadRequest());
}
db.Entry(supplyRequest).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!SupplyRequestExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(StatusCode(HttpStatusCode.NoContent));
}
public async Task <IActionResult> Edit(int id, [Bind("SupplyRequestId,Status,StatusComments,OrderItemsID,RequestOriginId,RequestType,ClientSupplierID,Owner,CreatedBy,CreateTime,UpdatedBy,UpdatedTime")] SupplyRequest supplyRequest)
{
if (id != supplyRequest.SupplyRequestId)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(supplyRequest);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!SupplyRequestExists(supplyRequest.SupplyRequestId))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
ViewData["ClientSupplierID"] = new SelectList(_context.ClientSuppliers, "ClientSupplierID", "ClientSupplierName");
ViewData["OrderItems"] = new SelectList(_context.OrderItems, "OrderItemsID", "OrderItemsName");
ViewData["Owner"] = new SelectList(_context.OwnerNames, "OwnerId", "OwnerName");
ViewData["RequestType"] = new SelectList(_context.RequestTypes, "RequestTypeId", "RequestTypeDescription");
ViewData["Status"] = new SelectList(_context.Statuses, "StatusId", "StatusDescription");
ViewData["RequestOrigin"] = new SelectList(_context.RequestOrigins, "RequestOriginId", "RequestOriginDescription");
return(View(supplyRequest));
}
private IList <SupplyRequestDetail> Get(OracleConnection connection, SupplyRequest supplyRequest)
{
IList <SupplyRequestDetail> result = new List <SupplyRequestDetail>();
string query = $"SELECT " +
$"{SupplyRequestDetail.ColumnNames.SupplyRequestId}, " +
$"{SupplyRequestDetail.ColumnNames.ProductId}, " +
$"{SupplyRequestDetail.ColumnNames.Count}, " +
$"{SupplyRequestDetail.ColumnNames.Active} " +
$"FROM {SupplyRequestDetail.TableName} " +
$"WHERE {SupplyRequestDetail.ColumnNames.Active} = 1 " +
$"AND {SupplyRequestDetail.ColumnNames.SupplyRequestId} = {supplyRequest.Id}";
OracleCommand cmd = new OracleCommand(query, connection);
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
SupplyRequestDetail supplyRequestDetail = 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()))
};
result.Add(supplyRequestDetail);
}
reader.Dispose();
return(result);
}
public async Task <IActionResult> Edit(int id, [Bind("Id,DealerId,CarId,Count,State")] SupplyRequest supplyRequest)
{
if (id != supplyRequest.Id)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(supplyRequest);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!SupplyRequestExists(supplyRequest.Id))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
ViewData["CarId"] = new SelectList(_context.Cars, "Id", "Id", supplyRequest.CarId);
ViewData["DealerId"] = new SelectList(_context.Dealers, "Id", "Id", supplyRequest.DealerId);
return(View(supplyRequest));
}
public async Task <IActionResult> Create([Bind("Id,DealerId,CarId,Count,State")] SupplyRequest supplyRequest)
{
if (ModelState.IsValid)
{
_context.Add(supplyRequest);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
ViewData["CarId"] = new SelectList(_context.Cars, "Id", "Name", supplyRequest.CarId);
ViewData["DealerId"] = new SelectList(_context.Dealers, "Id", "Name", supplyRequest.DealerId);
return(View(supplyRequest));
}
public IHttpActionResult DeleteSupplyRequest(int id)
{
SupplyRequest supplyRequest = db.SupplyRequests.Find(id);
if (supplyRequest == null)
{
return(NotFound());
}
db.SupplyRequests.Remove(supplyRequest);
db.SaveChanges();
return(Ok(supplyRequest));
}
public async Task <IActionResult> Create([Bind("SupplyRequestId,Status,StatusComments,OrderItems,SupplyRequestOrigin,RequestType,Client,Owner,CreatedBy,CreateTime,UpdatedBy,UpdatedTime")] SupplyRequest supplyRequest)
{
if (ModelState.IsValid)
{
_context.Add(supplyRequest);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
ViewData["ClientSupplierID"] = new SelectList(_context.ClientSuppliers, "ClientSupplierID", "ClientSupplierName");
ViewData["OrderItems"] = new SelectList(_context.OrderItems, "OrderItemsID", "OrderItemsName");
ViewData["Owner"] = new SelectList(_context.OwnerNames, "OwnerId", "OwnerName");
ViewData["RequestType"] = new SelectList(_context.RequestTypes, "RequestTypeId", "RequestTypeDescription");
ViewData["Status"] = new SelectList(_context.Statuses, "StatusId", "StatusDescription");
ViewData["RequestOrigin"] = new SelectList(_context.RequestOrigins, "RequestOriginId", "RequestOriginDescription");
return(View(supplyRequest));
}
//Get one basic table
// DEFAULT CRUD
public SupplyRequestMapper GetSupplyRequest(int supplyRequestId)
{
SupplyRequest content = db.SupplyRequests.FirstOrDefault(i => i.supplyRequestId == supplyRequestId);
if (content == null)
{
return(null);
}
else
{
SupplyRequestMapper request = new SupplyRequestMapper
{
SupplyRequestId = content.supplyRequestId,
TenantId = content.tenantId ?? default(int),
Active = content.active ?? default(bool)
};
return(request);
}
}
public IActionResult Put([FromRoute(Name = "id")] int supplyRequestId, [FromBody] SupplyRequest supplyRequest)
{
IActionResult result;
try
{
bool sendMail = false;
if (supplyRequest.StateId == (int)Enums.SupplyRequestState.Sended)
{
var bdSupplyRequest = this.supplyRequestService.Get(this.dbSettings, supplyRequestId);
if (bdSupplyRequest.StateId == (int)Enums.SupplyRequestState.Created)
{
sendMail = true;
}
}
var edited = this.supplyRequestService.Edit(this.dbSettings, supplyRequestId, supplyRequest);
edited.SupplyRequestDetails = this.supplyRequestService.Get(this.dbSettings, edited).ToList();
if (edited.SupplyRequestDetails != null && edited.SupplyRequestDetails.Any())
{
var products = this.productService.Get(this.dbSettings).Where(x => x.Active).ToList();
foreach (var supplyRequestDetail in edited.SupplyRequestDetails)
{
supplyRequestDetail.Product = products.FirstOrDefault(x => x.Id == supplyRequestDetail.ProductId);
}
}
edited.State = this.supplyRequestService.GetState(this.dbSettings, edited.StateId);
edited.Provider = this.providerService.Get(this.dbSettings, edited.ProviderId);
if (sendMail)
{
// insert here the logic of the email
StringBuilder sb = new StringBuilder();
if (edited.SupplyRequestDetails != null && edited.SupplyRequestDetails.Any())
{
sb.AppendLine($"<ul>");
foreach (var detail in edited.SupplyRequestDetails)
{
sb.AppendLine($"<li><b>{detail.Count} x</b> {detail.Product.Name}</li>");
}
sb.AppendLine($"</ul>");
}
this.mailService.SendMail(edited.Provider.Email,
$"Orden de compra: {string.Format(new FormatterHelper(), "{0:G}", edited.Code)}",
$"Estimado: <b>{edited.Provider.Name}</b> <br/><br/> Hemos emitido la siguiente orden de compra con código: <b>{string.Format(new FormatterHelper(), "{0:G}", edited.Code)}</b>. La cual contiene el siguiente detalle: <br/><br/> {sb} <br/><br/> Se despide atentamente <b>Restaurant Siglo XXI</b>.");
}
result = Ok(edited.Map(this.dbSettings, true));
}
catch (Exception ex)
{
result = BadRequest(ex.Message);
}
return(result);
}
public static SupplyItem MapFromRequest(this SupplyRequest request)
=> new SupplyItem()
{
ProductId = request.Product.Id,
Product = request.Product
};
public SupplyRequest GetByCode(RestaurantDatabaseSettings ctx, string supplyRequestCode)
{
SupplyRequest result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
string query = $"SELECT " +
$"sr.{SupplyRequest.ColumnNames.Id}, " +
$"sr.{SupplyRequest.ColumnNames.Code}, " +
$"sr.{SupplyRequest.ColumnNames.ProviderId}, " +
$"sr.{SupplyRequest.ColumnNames.StateId}, " +
$"sr.{SupplyRequest.ColumnNames.CreationDate}, " +
$"sr.{SupplyRequest.ColumnNames.Active}, " +
$"p.{Provider.ColumnNames.Id} AS {Provider.TableName}{Provider.ColumnNames.Id}, " +
$"p.{Provider.ColumnNames.Name} AS {Provider.TableName}{Provider.ColumnNames.Name}, " +
$"p.{Provider.ColumnNames.Email} AS {Provider.TableName}{Provider.ColumnNames.Email}, " +
$"p.{Provider.ColumnNames.Phone} AS {Provider.TableName}{Provider.ColumnNames.Phone}, " +
$"p.{Provider.ColumnNames.Address} AS {Provider.TableName}{Provider.ColumnNames.Address}, " +
$"p.{Provider.ColumnNames.Active} AS {Provider.TableName}{Provider.ColumnNames.Active}, " +
$"sre.{SupplyRequestState.ColumnNames.Id} AS {SupplyRequestState.TableName}{SupplyRequestState.ColumnNames.Id}, " +
$"sre.{SupplyRequestState.ColumnNames.Description} AS {SupplyRequestState.TableName}{SupplyRequestState.ColumnNames.Description} " +
$"FROM {SupplyRequest.TableName} sr " +
$"JOIN {Provider.TableName} p on p.{Provider.ColumnNames.Id} = {SupplyRequest.ColumnNames.ProviderId} " +
$"JOIN {SupplyRequestState.TableName} sre on sre.{SupplyRequestState.ColumnNames.Id} = {SupplyRequest.ColumnNames.StateId} " +
$"WHERE {SupplyRequest.ColumnNames.Code} = '{supplyRequestCode}'";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
result = new SupplyRequest()
{
Id = Convert.ToInt32(reader[$"{SupplyRequest.ColumnNames.Id}"]),
Code = reader[$"{SupplyRequest.ColumnNames.Code}"]?.ToString(),
ProviderId = Convert.ToInt32(reader[$"{SupplyRequest.ColumnNames.ProviderId}"]),
StateId = Convert.ToInt32(reader[$"{SupplyRequest.ColumnNames.StateId}"]),
CreationDate = Convert.ToDateTime(reader[$"{SupplyRequest.ColumnNames.CreationDate}"]),
Active = Convert.ToBoolean(Convert.ToInt16(reader[$"{SupplyRequest.ColumnNames.Active}"].ToString())),
Provider = new Provider()
{
Id = Convert.ToInt32(reader[$"{Provider.TableName}{Provider.ColumnNames.Id}"]),
Email = reader[$"{Provider.TableName}{Provider.ColumnNames.Email}"]?.ToString(),
Name = reader[$"{Provider.TableName}{Provider.ColumnNames.Name}"]?.ToString(),
Address = reader[$"{Provider.TableName}{Provider.ColumnNames.Address}"]?.ToString(),
Phone = reader[$"{Provider.TableName}{Provider.ColumnNames.Phone}"]?.ToString(),
Active = Convert.ToBoolean(Convert.ToInt16(reader[$"{Provider.TableName}{Provider.ColumnNames.Active}"].ToString()))
},
State = new SupplyRequestState()
{
Id = Convert.ToInt32(reader[$"{SupplyRequestState.TableName}{SupplyRequestState.ColumnNames.Id}"]),
Description = reader[$"{SupplyRequestState.TableName}{SupplyRequestState.ColumnNames.Description}"]?.ToString()
}
};
result.SupplyRequestDetails = this.Get(conn, result).ToList();
}
reader.Dispose();
}
return(result);
}
public SupplyRequest Edit(RestaurantDatabaseSettings ctx, int supplyRequestId, SupplyRequest supplyRequest)
{
SupplyRequest result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
conn.Open();
OracleTransaction transaction = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
string query = $"UPDATE {SupplyRequest.TableName} " +
$"SET " +
$"{SupplyRequest.ColumnNames.ProviderId} = {supplyRequest.ProviderId}, " +
$"{SupplyRequest.ColumnNames.StateId} = {supplyRequest.StateId} " +
$"WHERE {SupplyRequest.ColumnNames.Id} = {supplyRequestId}";
OracleCommand cmd = new OracleCommand(query, conn);
cmd.Transaction = transaction;
try
{
cmd.ExecuteNonQuery();
// check supplyRequest details
if (supplyRequest.SupplyRequestDetails != null)
{
if (supplyRequest.SupplyRequestDetails.Any(x => !x.Active))
{
// delete inactive supplyRequest details
foreach (var deletedSupplyRequestDetail in supplyRequest.SupplyRequestDetails.Where(x => !x.Active))
{
this.Delete(conn, transaction, deletedSupplyRequestDetail);
}
}
// get bd supplyRequest details to compare if exist or not
List <SupplyRequestDetail> currentSupplyRequestDetails = Get(conn, supplyRequest).ToList();
// edit active supplyRequest details
foreach (var editedSupplyRequestDetail in supplyRequest.SupplyRequestDetails.Where(x => x.Active && currentSupplyRequestDetails.Any(y => x.SupplyRequestId == y.SupplyRequestId && x.ProductId == y.ProductId)))
{
this.Edit(conn, transaction, editedSupplyRequestDetail);
}
// create new supplyRequest details
foreach (var createdRecipeDetail in supplyRequest.SupplyRequestDetails.Where(x => x.Active && !currentSupplyRequestDetails.Any(y => x.SupplyRequestId == y.SupplyRequestId && x.ProductId == y.ProductId)))
{
this.Add(conn, transaction, supplyRequestId, createdRecipeDetail);
}
foreach (var deletedSupplyRequestDetail in currentSupplyRequestDetails.Where(x => !supplyRequest.SupplyRequestDetails.Any(y => x.SupplyRequestId == y.SupplyRequestId && x.ProductId == y.ProductId)))
{
this.Delete(conn, transaction, deletedSupplyRequestDetail);
}
}
transaction.Commit();
result = supplyRequest;
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
return(result);
}
public SupplyRequest Add(RestaurantDatabaseSettings ctx, SupplyRequest supplyRequest)
{
SupplyRequest result = null;
using (OracleConnection conn = new OracleConnection(ctx.ConnectionString))
{
conn.Open();
OracleTransaction transaction = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
supplyRequest.CreationDate = DateTime.Now;
supplyRequest.Code = $"{supplyRequest.ProviderId}{supplyRequest.CreationDate:yyMMddHHmmss}";
string query = $"INSERT INTO {SupplyRequest.TableName} (" +
$"{SupplyRequest.ColumnNames.Code}, " +
$"{SupplyRequest.ColumnNames.ProviderId}, " +
$"{SupplyRequest.ColumnNames.StateId}, " +
$"{SupplyRequest.ColumnNames.CreationDate}, " +
$"{SupplyRequest.ColumnNames.Active} " +
$") VALUES (" +
$"'{supplyRequest.Code}', " +
$"{supplyRequest.ProviderId}, " +
$"{supplyRequest.StateId}, " +
$"TO_DATE('{supplyRequest.CreationDate:ddMMyyyyHHmm}', 'DDMMYYYYHH24MI'), " +
$"{1} " +
$") RETURNING {SupplyRequest.ColumnNames.Id} INTO :{SupplyRequest.ColumnNames.Id}";
OracleCommand cmd = new OracleCommand(query, conn)
{
Transaction = transaction
};
cmd.Parameters.Add(new OracleParameter()
{
ParameterName = $":{SupplyRequest.ColumnNames.Id}",
OracleDbType = OracleDbType.Decimal,
Direction = System.Data.ParameterDirection.Output
});
try
{
cmd.ExecuteNonQuery();
supplyRequest.Id = Convert.ToInt32(cmd.Parameters[$":{SupplyRequest.ColumnNames.Id}"].Value.ToString());
if (supplyRequest.SupplyRequestDetails != null)
{
List <SupplyRequestDetail> recipeDetails = new List <SupplyRequestDetail>();
foreach (var supplyRequestDetail in supplyRequest.SupplyRequestDetails)
{
SupplyRequestDetail createdRecipeDetail = this.Add(conn, transaction, supplyRequest.Id, supplyRequestDetail);
recipeDetails.Add(createdRecipeDetail);
}
}
transaction.Commit();
result = supplyRequest;
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
return(result);
}
