/// <summary>
/// Checks Name
/// </summary>
/// <param name="viewModel">Injected <see cref="AddProvincia"/></param>
/// <returns>Instance of <see cref="Task{Provincia}"/></returns>
public async Task <Provincia> CheckName(UpdateProvincia @viewModel)
{
Provincia @provincia = await Context.Provincia
.AsNoTracking()
.TagWith("CheckName")
.FirstOrDefaultAsync(x => x.Name == @viewModel.Name && x.Id != @viewModel.Id);
if (@provincia != null)
{
// Log
string @logData = @provincia.GetType().Name
+ " with Name "
+ provincia.Name
+ " was already found at "
+ DateTime.Now.ToShortTimeString();
Logger.WriteGetItemFoundLog(@logData);
throw new Exception(provincia.GetType().Name
+ " with Name "
+ @viewModel.Name
+ " already exists");
}
return(@provincia);
}
/// <summary>
/// Updates Provincia
/// </summary>
/// <param name="viewModel">Injected <see cref="UpdateProvincia"/></param>
/// <returns>Instance of <see cref="Task{ViewProvincia}"/></returns>
public async Task <ViewProvincia> UpdateProvincia(UpdateProvincia @viewModel)
{
await CheckName(@viewModel);
Provincia @provincia = await FindProvinciaById(@viewModel.Id);
@provincia.Name = @viewModel.Name;
@provincia.ImageUri = @viewModel.ImageUri;
try
{
Context.Provincia.Update(@provincia);
await Context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
await CheckName(@viewModel);
}
// Log
string @logData = @provincia.GetType().Name
+ " with Id "
+ @provincia.Id
+ " was modified at "
+ DateTime.Now.ToShortTimeString();
Logger.WriteUpdateItemLog(@logData);
return(Mapper.Map <ViewProvincia>(@provincia));
}
/// <summary>
/// Removes Provincia By Id
/// </summary>
/// <param name="id">Injected <see cref="int"/></param>
/// <returns>Instance of <see cref="Task"/></returns>
public async Task RemoveProvinciaById(int id)
{
try
{
Provincia @provincia = await FindProvinciaById(id);
Context.Provincia.Remove(@provincia);
await Context.SaveChangesAsync();
// Log
string @logData = @provincia.GetType().Name
+ " with Id "
+ @provincia.Id
+ " was removed at "
+ DateTime.Now.ToShortTimeString();
Logger.WriteDeleteItemLog(@logData);
}
catch (DbUpdateConcurrencyException)
{
await FindProvinciaById(id);
}
}
public async Task <ActionResult <IEnumerable <Provincia> > > Get(string filter = "", string range = "", string sort = "")
{
Connection conex = new Connection();
SqlConnection connection = new SqlConnection(conex.connectionString);
string sql = "SELECT * FROM Provincia";
var t = new Provincia();
if (!string.IsNullOrEmpty(filter))
{
var filterVal = (JObject)JsonConvert.DeserializeObject(filter);
int i = 0;
foreach (var f in filterVal)
{
var valueArr = f.Value.ToArray();
var valueString = valueArr[0].Value <string>();
if (t.GetType().GetProperty(f.Key.ToUpper()).PropertyType == typeof(string))
{
if (i == 0)
{
sql += $" where {f.Key.ToUpper()} = '{valueString}'";
}
else
{
sql += $" OR where {f.Key.ToUpper()} = '{valueString}'";
}
}
else
{
if (i == 0)
{
sql += $" where {f.Key.ToUpper()} = {valueString}";
}
else
{
sql += $" OR where {f.Key.ToUpper()} = {valueString}";
}
}
i += 1;
}
}
if (!string.IsNullOrEmpty(sort))
{
var sortVal = JsonConvert.DeserializeObject <List <string> >(sort);
var condition = sortVal.First();
var order = sortVal.Last() == "ASC" ? "" : "DESC";
sql += $" ORDER BY {condition} {order}";
}
var from = 0;
var to = 0;
if (!string.IsNullOrEmpty(range))
{
var rangeVal = JsonConvert.DeserializeObject <List <int> >(range);
from = rangeVal.First();
to = rangeVal.Last();
sql += $" OFFSET {from} ROWS FETCH NEXT {to - from + 1} ROWS ONLY";
}
sql += ";";
Console.WriteLine("Last SQL", sql);
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.CommandType = CommandType.Text;
SqlDataReader reader;
connection.Open();
List <Provincia> provincias = new List <Provincia>();
try
{
reader = await cmd.ExecuteReaderAsync();
Provincia provincia;
while (reader.Read())
{
provincia = new Provincia();
provincia.ID = int.Parse(reader[0].ToString());
provincia.Nombre = reader[1].ToString();
provincias.Add(provincia);
}
}
catch (Exception ex)
{
return(StatusCode(500, ex.Message));
}
finally
{
connection.Close();
}
var count = provincias.Count();
Response.Headers.Add("Access-Control-Expose-Headers", "Content-Range");
Response.Headers.Add("Content-Range", $"{typeof(Provincia).Name.ToLower()} {from}-{to}/{count}");
return(provincias);
}