static async Task <List <ItemInventoryType> > Decrement(string sku)
{
List <ItemInventoryType> inventoryInfo = new List <ItemInventoryType>();
try
{
string updateSql = $"UPDATE Inventory SET Quantity = i.Quantity - 1 FROM Inventory as i where i.sku = '{sku}'";
var selectSql = $"SELECT Sku, Quantity, Modified FROM Inventory WHERE Sku = '{sku}'";
string sqlConnectionString = Environment.GetEnvironmentVariable("sqlConnectionString");
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
await conn.OpenAsync();
using (SqlCommand cmd = new SqlCommand(updateSql, conn))
{
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = selectSql;
var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
if (await reader.ReadAsync())
{
var inventoryItem = new ItemInventoryType
{
Sku = reader.GetString(0),
Quantity = reader.GetInt32(1),
Modified = reader.GetDateTime(2)
};
inventoryInfo.Add(inventoryItem);
reader.Close();
}
}
}
}
}
catch (Exception)
{
// Do some logging here
}
return(inventoryInfo);
}
public static async Task <IActionResult> RunGetInventoryForSKU(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "inventory")] HttpRequest req,
ILogger log)
{
try
{
List <ItemInventoryType> inventoryInfo = new List <ItemInventoryType>();
// going to assume only 1 sku at a time
string sku = req.Query["skus"];
string sqlConnectionString = Environment.GetEnvironmentVariable("sqlConnectionString");
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
await conn.OpenAsync();
var sql = $"SELECT Sku, Quantity, Modified FROM Inventory WHERE Sku = '{sku}'";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
if (await reader.ReadAsync())
{
var inventoryItem = new ItemInventoryType
{
Sku = reader.GetString(0),
Quantity = reader.GetInt32(1),
Modified = reader.GetDateTime(2)
};
inventoryInfo.Add(inventoryItem);
reader.Close();
}
}
else
{
reader.Close();
// Just add a dummy one for next time
string insertSQL = $"INSERT INTO Inventory (Sku, Quantity, Modified) VALUES ('{sku}', 12, GETDATE())";
cmd.CommandText = insertSQL;
await cmd.ExecuteNonQueryAsync();
var inventoryItem = new ItemInventoryType
{
Sku = sku,
Quantity = 12,
Modified = DateTime.Now
};
inventoryInfo.Add(inventoryItem);
}
}
}
return(new OkObjectResult(inventoryInfo));
}
catch (Exception ex)
{
log.LogError(ex, "Inventory Finder");
return(new StatusCodeResult(500));
}
}