// GET: Queries/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(BadRequest());
}
Query query = db.Queries
.Include(q => q.CreatedBy)
.Include(q => q.LastEditedBy)
.First(q => q.QueryID == id);
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == query.DatabaseConnectionID);
if (database == null)
{
return(NotFound());
}
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (PermissionMgr.UserCanViewQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
ViewBag.UserCanModifyQueries = PermissionMgr.UserCanModifyQuery(userPermissions, database) || database.OrganisationId == CurrentUser.OrganisationId;
return(View(query));
}
public ActionResult DeleteConfirmed(int id)
{
Query query = db.Queries.Find(id);
if (query == null)
{
return(NotFound());
}
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == query.DatabaseConnectionID);
if (database == null)
{
return(NotFound());
}
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (PermissionMgr.UserCanModifyQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
db.Queries.Remove(query);
db.SaveChanges();
return(RedirectToAction("Details", "Home", new { id = database.DatabaseConnectionID }));
}
public ActionResult Convert(int id)
{
Query dbQuery = db.Queries.Find(id);
if (dbQuery == null || dbQuery.IsSimpleQuery == false)
{
return(NotFound());
}
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == dbQuery.DatabaseConnectionID);
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (database == null)
{
return(NotFound());
}
if (PermissionMgr.UserCanModifyQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
dbQuery.IsSimpleQuery = false;
db.SaveChanges();
return(RedirectToAction("Edit", "Queries", new { id = id }));
}
// GET: Queries/Delete/5
public ActionResult Delete(int?id)
{
if (id == null)
{
return(BadRequest());
}
Query query = db.Queries.Find(id.Value);
if (query == null)
{
return(NotFound());
}
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == query.DatabaseConnectionID);
if (database == null)
{
return(NotFound());
}
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (PermissionMgr.UserCanModifyQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
return(View(query));
}
// GET: Simple/Edit/5
public ActionResult Edit(int id)
{
Query query = db.Queries.Find(id);
if (query == null || query.IsSimpleQuery == false)
{
return(NotFound());
}
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == query.DatabaseConnectionID);
if (database == null)
{
return(NotFound());
}
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (PermissionMgr.UserCanModifyQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
ViewBag.DatabaseConnectionID = database.DatabaseConnectionID;
ViewBag.DatabaseName = database.Name;
return(View(query));
}
public ActionResult Edit(Query query)
{
Query dbQuery = db.Queries.Find(query.QueryID);
if (dbQuery == null || dbQuery.IsSimpleQuery == false)
{
return(NotFound());
}
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == dbQuery.DatabaseConnectionID);
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (database == null)
{
return(NotFound());
}
if (PermissionMgr.UserCanModifyQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
if (ModelState.IsValid)
{
dbQuery.Name = query.Name;
dbQuery.LastEditedBy = CurrentUser;
dbQuery.LastEditedOn = DateTime.Now;
dbQuery.QueryDefinition = query.QueryDefinition;
dbQuery.Description = query.Description;
db.Queries.Update(dbQuery);
db.SaveChanges();
return(RedirectToAction("Details", "Queries", new { id = query.QueryID }));
}
ViewBag.DatabaseConnectionID = database.DatabaseConnectionID;
ViewBag.DatabaseName = database.Name;
return(View(query));
}
public ActionResult Create(Query query)
{
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == query.DatabaseConnectionID);
if (database == null)
{
return(NotFound());
}
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (PermissionMgr.UserCanModifyQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
//TODO: MUST CHANGE
//if (ModelState.IsValid)
// {
query.CreatedBy = CurrentUser;
query.LastEditedBy = CurrentUser;
query.CreatedOn = DateTime.Now;
query.LastEditedOn = DateTime.Now;
query.IsSimpleQuery = true;
db.Queries.Add(query);
db.SaveChanges();
return(RedirectToAction("Details", "Queries", new { id = query.QueryID }));
//}
ViewBag.DatabaseConnectionID = database.DatabaseConnectionID;
ViewBag.DatabaseName = database.Name;
return(View(query));
}
// GET: Queries/Create
public ActionResult Create(int connectionId = 0)
{
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == connectionId);
if (database == null)
{
return(NotFound());
}
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (PermissionMgr.UserCanModifyQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
ViewBag.DatabaseConnectionID = connectionId;
ViewBag.DatabaseName = database.Name;
return(View());
}
// GET: Queries/Create
public ActionResult Create(int connectionId)
{
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == connectionId);
if (database == null)
{
return(NotFound());
}
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (PermissionMgr.UserCanModifyQuery(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
ViewBag.DatabaseConnectionID = connectionId;
ViewBag.DatabaseName = database.Name;
int i = 1;
Query viewModel = new Query
{
DatabaseConnectionID = connectionId,
Name = "Report " + i++
};
var existingQueries = db.Queries.Where(q => q.DatabaseConnectionID == connectionId).ToList();
while (existingQueries.Any(q => string.Compare(q.Name, viewModel.Name, true) == 0))
{
viewModel.Name = "Report " + i++;
}
return(View(viewModel));
}
// GET: DatabaseConnections/Details/5
public ActionResult Details(int id)
{
var database = db.DatabaseConnections.FirstOrDefault(dc => dc.DatabaseConnectionID == id);
if (database == null)
{
return(NotFound());
}
var userPermissions = db.UserDatabaseConnections
.Where(uc => uc.ApplicationUserID == CurrentUser.Id)
.ToList();
if (PermissionMgr.UserCanViewDatabase(userPermissions, database) == false && database.OrganisationId != CurrentUser.OrganisationId)
{
return(NotFound());
}
ViewBag.UserIsOrganisationAdmin = database.OrganisationId == CurrentUser.OrganisationId;
ViewBag.UserCanModifyQueries = PermissionMgr.UserCanModifyQuery(userPermissions, database) || database.OrganisationId == CurrentUser.OrganisationId;
ViewBag.UserCanModifyDatabase = PermissionMgr.UserCanModifyDatabase(userPermissions, database) || database.OrganisationId == CurrentUser.OrganisationId;
ViewBag.UserCanManageDatabaseAccess = PermissionMgr.UserCanManageDatabaseAccess(userPermissions, database) || database.OrganisationId == CurrentUser.OrganisationId;
ViewBag.UserCanDeleteDatabase = PermissionMgr.UserCanDeleteDatabase(userPermissions, database) || database.OrganisationId == CurrentUser.OrganisationId;
var viewModel = new DatabaseConnectionDetailsViewModel
{
DatabaseConnectionID = database.DatabaseConnectionID,
Name = database.Name,
Type = database.Type,
Server = database.Server,
Port = database.Port,
Username = database.Username,
DatabaseName = database.DatabaseName,
Description = database.Description,
UseSsh = database.UseSsh,
SshServer = database.SshServer,
SshPort = database.SshPort,
SshUsername = database.SshUsername,
AccessUsers = new List <UserDatabaseConnection>()
};
List <DatabaseConnectionQueriesDetailsViewModel> viewQueries = new List <DatabaseConnectionQueriesDetailsViewModel>();
var queries = db.Queries
.Include(q => q.CreatedBy)
.Include(q => q.LastEditedBy)
.Where(q => q.DatabaseConnectionID == database.DatabaseConnectionID);
foreach (var query in queries)
{
DatabaseConnectionQueriesDetailsViewModel queryView = new DatabaseConnectionQueriesDetailsViewModel();
queryView.QueryID = query.QueryID;
queryView.Name = query.Name;
queryView.Description = query.Description;
queryView.IsSimpleQuery = query.IsSimpleQuery;
queryView.CreatedBy = query.CreatedBy;
queryView.CreatedOn = query.CreatedOn;
queryView.LastEditedBy = query.LastEditedBy;
queryView.LastEditedOn = query.LastEditedOn;
viewQueries.Add(queryView);
}
viewModel.SavedQueries = viewQueries.OrderByDescending(q => q.LastEditedOn);
if (database.Organisation != null)
{
viewModel.OrganisationName = database.Organisation.OrganisationName;
}
else
{
viewModel.OrganisationName = "None";
}
if (ViewBag.UserCanModifyDatabase)
{
// Don't allow users to manage themselves, things get strange very quickly
var accessUsers = db.UserDatabaseConnections
.Where(u => u.DatabaseConnection.DatabaseConnectionID == database.DatabaseConnectionID && u.ApplicationUserID != CurrentUser.Id)
.ToList();
foreach (var accessUser in accessUsers)
{
viewModel.AccessUsers.Add(new UserDatabaseConnection
{
UserDatabaseConnectionID = accessUser.UserDatabaseConnectionID,
DatabaseConnectionID = accessUser.DatabaseConnectionID,
ApplicationUser = accessUser.ApplicationUser,
Type = accessUser.Type,
CreatedBy = accessUser.CreatedBy,
CreatedOn = accessUser.CreatedOn,
InviteEmail = accessUser.InviteEmail,
});
}
}
return(View(viewModel));
}