public bool LoadKeys(ArtContext dbContext)
{
try
{
//Load only once
if (ApiKeyList == null)
{
ApiKeyList = dbContext.APIKeys.ToList();
}
if (ApiKeyList.Count == 0)
{
return(false); //keys not set up in DB
}
_log.LogInformation("Api keys loaded from database..");
}
catch (Exception e)
{
//Failed to load API keys from DB. Log exception
_log.LogError(e.Message);
}
if (ApiKeyList == null)
{
return(false);
}
return(true);
}
public ActionResult EditArts(EditModel model, HttpPostedFileBase upload, int Id)
{
if (upload != null)
{
string fileName = System.IO.Path.GetFileName(upload.FileName);
upload.SaveAs(Server.MapPath("~/Files/" + fileName));
int photoId;
using (ArtContext db1 = new ArtContext())
{
PhotoArt p1 = new PhotoArt {
PhotoName = fileName, Photo = Server.MapPath("~/ Files / " + fileName)
};
db1.PhotoArts.Add(p1);
db1.SaveChanges();
photoId = p1.Id;
ArtWork artWork = db1.ArtWorks.Where(p => p.Id == Id).FirstOrDefault();
artWork.PhotoArtId = photoId;
db1.SaveChanges();
}
}
else
{
db.Entry(model.ArtWorks).State = EntityState.Modified;
db.SaveChanges();
}
return(RedirectToAction("ListArts"));
}
public ActionResult Adhoc(AdhocModel model)
{
string sql = model.Sql;
string currentBuiltin = model.CurrentBuiltin;
model = new AdhocModel {
Sql = sql, Schema = schema, Builtins = builtins, CurrentBuiltin = currentBuiltin
};
try
{
if (sql.Trim().ToLower().StartsWith("select"))
{
var results = new List <List <string> >();
var rows = ArtContext.Query(sql);
bool first = true;
foreach (var row in rows)
{
if (first)
{
List <string> headers = new List <string>();
foreach (var column in (IDictionary <string, object>)row) // get column names
{
headers.Add(column.Key);
}
results.Add(headers);
first = false;
}
var values = new List <string>();
foreach (var column in (IDictionary <string, object>)row) // get column values
{
string value = column.Value == null ? "" : column.Value.ToString();
values.Add(value);
}
results.Add(values);
}
model.Results = results;
}
else
{
ArtContext.Execute(sql);
model.Results.Add(new List <string> {
"Query ran successfully."
});
}
}
catch (Exception ex)
{
model.Exception = ex.ToString();
}
return(View(model));
}
// workaround for an issue with OAuthWebSecurity.IsAuthenticatedWithOAuth
bool IsAuthenticatedWithOAuth()
{
// next line is commented out because IsAuthenticatedWithOAuth seems to always return false
// return OAuthWebSecurity.IsAuthenticatedWithOAuth;
// quick and easy workaround
var count = (int)ArtContext.Scalar("SELECT COUNT(UserId) FROM [webpages_OAuthMembership] WHERE UserId = @0", CurrentUser.Id);
return(count > 0);
}
public ActionResult Search(string term)
{
// NOTE: jQuery control requires that parameter be named 'term' and returned values be named 'label'
var label = new List <string>();
var items = ArtContext.Query("SELECT TOP 5 Title FROM [Product] WHERE Title Like @0 ORDER BY Title", term + "%");
foreach (var item in items)
{
string l = item.Title.ToString();
label.Add(l.Ellipsify(20));
}
return(Json(label, JsonRequestBehavior.AllowGet));
}
public async Task Invoke(HttpContext httpContext, ArtContext dbContext)
{
try
{
// Load API Keys from DB on first hit
if (!apiKeyRepo.LoadKeys(dbContext))
{
httpContext.Response.StatusCode = 503; //API key not loaded
string msg = "API Key Load error";
await httpContext.Response.WriteAsync(msg);
_log.LogInformation(msg);
return;
}
if (!httpContext.Request.Headers.Keys.Contains(apiKeyConst))
{
httpContext.Response.StatusCode = 400; //Bad Request
string msg = "API Key is missing";
await httpContext.Response.WriteAsync(msg);
_log.LogInformation(msg);
return;
}
else
{
if (!apiKeyRepo.CheckValidApiKey(httpContext.Request.Headers[apiKeyConst].ToString().Trim()))
{
httpContext.Response.StatusCode = 401; //UnAuthorized
string msg = "Invalid API Key";
await httpContext.Response.WriteAsync(msg);
_log.LogInformation(msg);
return;
}
}
await _next.Invoke(httpContext);
}
catch (Exception ex)
{
_log.LogError(ex.Message);
}
}
public ActionResult UploadPhoto(PhotoArt photo, HttpPostedFileBase upload)
{
if (upload != null)
{
string fileName = System.IO.Path.GetFileName(upload.FileName);
upload.SaveAs(Server.MapPath("~/Files/" + fileName));
using (ArtContext db = new ArtContext())
{
PhotoArt p1 = new PhotoArt {
PhotoName = fileName, Photo = Server.MapPath("~/ Files / " + fileName)
};
db.PhotoArts.Add(p1);
db.SaveChanges();
}
}
return(RedirectToAction("ListArts"));
}
public ActionResult EditArts(int Id)
{
EditModel model = new EditModel();
using (ArtContext db = new ArtContext())
{
model.ArtWorks = db.ArtWorks.FirstOrDefault(a => a.Id == Id);
model.Authors = db.Authors.ToList();
model.PhotoArt = db.PhotoArts.FirstOrDefault(a => a.Id == Id);
model.AuthorsCollection = db.Authors.ToList();
model.TypesCollection = db.Types.ToList();
model.GenresCollection = db.Genres.ToList();
model.TechniquesCollection = db.Techniques.ToList();
model.LocationsCollection = db.Locations.ToList();
model.PhotoArtCollection = db.PhotoArts.ToList();
}
return(View(model));
}
public ArtTypesController(ArtContext context)
{
_context = context;
}
public ArtistController(ArtContext context) => _context = context;
public ArtFinderController(ArtContext context, IConfiguration Configuration, ILogger <ArtFinderController> log)
{
_context = context;
_configuration = Configuration;
_log = log;
}
/// <summary>
/// Initializes a new instance of the <see cref="BaseRepository{TEntity}"/> class.
/// A default contructor.
/// Contrutor padrão.
/// </summary>
/// <param name="context">
/// The context to be used.
/// O contexto a ser utilizado.
/// </param>
public BaseRepository(ArtContext context)
{
this.Db = context;
this.DbSet = this.Db.Set <TEntity>();
}
public HomeController(ArtContext context)
{
_context = context;
}
public ArtByLocationController(ArtContext context, ILogger <ArtByLocationController> log)
{
_context = context;
_log = log;
}
public static void Initialize(IServiceProvider serviceProvider)
{
using (var context = new ArtContext(serviceProvider.GetRequiredService <DbContextOptions <ArtContext> >()))
{
if (!context.ArtTypes.Any())
{
context.ArtTypes.AddRange(
new ArtType
{
Type = "Painting"
},
new ArtType
{
Type = "Drawing"
},
new ArtType
{
Type = "Sculpture"
},
new ArtType
{
Type = "Plastic Art"
},
new ArtType
{
Type = "Decorative Art"
},
new ArtType
{
Type = "Visual Art"
}
);
context.SaveChanges();
}
if (!context.Artworks.Any())
{
context.Artworks.AddRange(
new Artwork
{
Name = "Red Dot",
Value = 12000m,
Finished = DateTime.Parse("2002-06-06"),
Description = "Painting of a large Red Dot on a white backgraound.",
ArtTypeID = (context.ArtTypes.Where(d => d.Type == "Painting").SingleOrDefault().ID)
},
new Artwork
{
Name = "Rossini Regal",
Value = 99000m,
Finished = DateTime.Parse("2009-12-06"),
Description = "Photograph of a regal horse.",
ArtTypeID = (context.ArtTypes.Where(d => d.Type == "Visual Art").SingleOrDefault().ID)
},
new Artwork
{
Name = "Love Sublime",
Value = 19.99m,
Finished = DateTime.Parse("2015-09-21"),
Description = "Soapstone Sculpture of woman's face gazing at an unknown figure.",
ArtTypeID = (context.ArtTypes.Where(d => d.Type == "Sculpture").SingleOrDefault().ID)
},
new Artwork
{
Name = "Igor Smashes",
Value = 750000.50m,
Finished = DateTime.Parse("1976-07-11"),
Description = "Abstract concept of smashed emotion done in crumpled paper.",
ArtTypeID = (context.ArtTypes.Where(d => d.Type == "Plastic Art").SingleOrDefault().ID)
});
context.SaveChanges();
}
}
}
//
// GET: /Dashboard/
public ActionResult Dashboard()
{
var model = new DashboardModel();
// lightweight query (only includes one column and data of first 4 months).
var users = ArtContext.Users.Query("SELECT SignupDate FROM [User] WHERE SignupDate < '2013/5/1' ORDER BY SignupDate");
// get date range to partition in weekly buckets
var min = new DateTime(2013, 1, 1);
var max = new DateTime(2013, 5, 1); // (DateTime)users.Max(u => u.SignupDate);
// create a bucket for each week
int weeks = (((max - min).Days + 7) / 7) + 1;
var buckets = Enumerable.Range(1, weeks).ToDictionary(w => w, w => 0);
// number of new users each week
foreach (var user in users)
{
buckets[WeekOfYear(user.SignupDate.Value)]++;
}
// create cumulative values
int runningTotal = 0;
for (int i = 1; i <= buckets.Count; i++)
{
runningTotal += buckets[i];
buckets[i] = runningTotal;
}
// flot formatting
var usersData = new StringBuilder(@"[{ ""label"": ""Users"", ""color"" : ""#b00"", ""data"": [");
var usersTicks = new StringBuilder("[");
int index = 1;
foreach (var bucket in buckets)
{
usersData.Append("[" + index + ", " + bucket.Value + "],");
usersTicks.Append("[" + index + @", """ + bucket.Key + @"""],");
index++;
}
usersData = usersData.Remove(usersData.Length - 1, 1).Append("] }]");
usersTicks = usersTicks.Remove(usersTicks.Length - 1, 1).Append("]");
model.UsersData = usersData.ToString();
model.UsersTicks = usersTicks.ToString();
// sales data
// lightweight query (only includes two columns and data of first 4 months).
var orders = ArtContext.Orders.Query("SELECT OrderDate, TotalPrice FROM [Order] WHERE OrderDate < '2013/5/1' ORDER BY OrderDate");
for (int i = 1; i <= buckets.Count; i++)
{
buckets[i] = 0; // clear buckets
}
// sales by week
foreach (var order in orders)
{
buckets[WeekOfYear(order.OrderDate.Value)] += (int)order.TotalPrice;
}
// create cumulative values
runningTotal = 0;
for (int i = 1; i <= buckets.Count; i++)
{
runningTotal += buckets[i];
buckets[i] = runningTotal;
}
// flot formatting
var salesData = new StringBuilder(@"[{ ""label"": ""Sales"", ""color"" : ""#090"", ""data"": [");
var salesTicks = new StringBuilder("[");
index = 1;
foreach (var bucket in buckets)
{
salesData.Append("[" + index + ", " + bucket.Value + "],");
salesTicks.Append("[" + index + @", """ + bucket.Key + @"""],");
index++;
}
salesData = salesData.Remove(salesData.Length - 1, 1).Append("] }]");
salesTicks = salesTicks.Remove(salesTicks.Length - 1, 1).Append("]");
model.SalesData = salesData.ToString();
model.SalesTicks = salesTicks.ToString();
// Demographics of users
// lightweight query (only includes two columns and data of first 4 months).
string sql = "SELECT Country, COUNT(Id) AS Number FROM [User] GROUP BY Country ORDER BY Number DESC";
var countries = ArtContext.Query(sql);
// flot formatting
var demographics = new StringBuilder("[");
foreach (var country in countries)
{
demographics.Append(@"{ ""label"": """ + country.Country + @""" , ""data"": " + country.Number + " },");
}
demographics = demographics.Remove(demographics.Length - 1, 1).Append("]");
model.Demographics = demographics.ToString();
return(View(model));
}
public UnitOfWork(ArtContext context)
{
this.context = context;
}
public ArtworkController(ArtContext context, ILogger <ArtworkController> log)
{
_context = context;
_log = log;
}
static AdhocController()
{
// build schema cache (only once)
schema = new Dictionary <string, List <AdhocColumn> >();
var sql = @"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT LIKE 'aspnet_%' AND TABLE_NAME NOT LIKE 'webpages_%'
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME" ;
var tables = ArtContext.Query(sql);
foreach (var table in tables)
{
var adhocColumns = new List <AdhocColumn>();
sql = @"SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '" + table.TABLE_NAME + @"'";
var columns = ArtContext.Query(sql);
foreach (var column in columns)
{
adhocColumns.Add(new AdhocColumn {
Name = column.COLUMN_NAME, DataType = column.DATA_TYPE
});
}
schema.Add(table.TABLE_NAME, adhocColumns);
}
builtins = new List <Builtin>();
builtins.Add(new Builtin
{
Id = "builtin1",
Text = "Record Counts",
Description = "Record counts for all tables in database",
Sql =
@"SELECT Artists = (SELECT COUNT(Id) FROM [Artist]),
Carts = (SELECT COUNT(Id) FROM [Cart]),
CartItems = (SELECT COUNT(Id) FROM [CartItem]),
Errors = (SELECT COUNT(Id) FROM [Error]),
Orders= (SELECT COUNT(Id) FROM [Order]),
OrderDetails= (SELECT COUNT(Id) FROM [OrderDetail]),
Products= (SELECT COUNT(Id) FROM [Product]),
Ratings= (SELECT COUNT(Id) FROM [Rating]),
Users = (SELECT COUNT(Id) FROM [User])"
});
builtins.Add(new Builtin
{
Id = "builtin2",
Text = "Users with Orders",
Description = "Display Users and all their orders",
Sql =
@"SELECT U.FirstName, U.LastName, U.Email, O.OrderDate,
'$' + CONVERT(varchar, CAST(O.TotalPrice AS Money), 1) AS Total,
O.OrderNumber
FROM [Order] O
JOIN [User] U ON O.UserId = U.Id
ORDER BY O.OrderDate DESC"
});
builtins.Add(new Builtin
{
Id = "builtin3",
Text = "Artists with Products",
Description = "Display Artists and all their arts works (i.e. products)",
Sql =
@"SELECT A.FirstName, A.LastName,
'$' + CONVERT(VARCHAR, CAST(P.Price AS Money), 1) AS Total,
P.Title
FROM [Artist] A JOIN [Product] P ON A.Id = P.ArtistId
ORDER BY A.LastName"
});
builtins.Add(new Builtin
{
Id = "builtin4",
Text = "Update Statistics",
Description = "Recompute totals and summary statistics in all relevant tables",
Sql =
@"UPDATE A SET TotalProducts = ISNULL(X.Total,0)
FROM [Artist] A
LEFT OUTER JOIN (SELECT ArtistId, COUNT(Id) AS Total
FROM [Product]
GROUP BY ArtistId) AS X
ON A.Id = X.ArtistId;
UPDATE C SET ItemCount = ISNULL(X.Total,0)
FROM [Cart] C
LEFT OUTER JOIN (SELECT CartId, COUNT(Id) AS Total
FROM [CartItem]
GROUP BY CartId) AS X
ON C.Id = X.CartId;
UPDATE O SET ItemCount = ISNULL(X.Total,0)
FROM [Order] O
LEFT OUTER JOIN (SELECT OrderId, COUNT(Id) AS Total
FROM [OrderDetail]
GROUP BY OrderId) AS X
ON O.Id = X.OrderId;
UPDATE P SET QuantitySold = ISNULL(X.QSold,0)
FROM [Product] P
LEFT OUTER JOIN (SELECT ProductId, SUM(Quantity) AS QSold
FROM [OrderDetail]
GROUP BY ProductId) AS X
ON P.Id = X.ProductId;
UPDATE U SET OrderCount = ISNULL(X.Total,0)
FROM [User] U
LEFT OUTER JOIN (SELECT UserId, COUNT(Id) AS Total
FROM [Order]
GROUP BY UserId) AS X
ON U.Id = X.UserId;"
});
}
public RestaurantSearchController(ArtContext context, IConfiguration Configuration, ILogger <RestaurantSearchController> log)
{
_context = context;
_configuration = Configuration;
_log = log;
}
public ArtDBRepo(ArtContext context)
{
this.context = context;
}
public UnidadeMedidaInitializer(ArtContext context)
{
this.context = context;
}
public ArtworksController(ArtContext context)
{
_context = context;
}
/// <summary>
/// Initializes a new instance of the <see cref="UnidadeMedidaRepository"/> class.
/// Construtor padrão de <see cref="UnidadeMedidaRepository"/>.
/// </summary>
/// <param name="context">
/// O contexto do repositório. Veja <see cref="ArtContext"/>.
/// </param>
public UnidadeMedidaRepository(ArtContext context)
: base(context)
{
}
