using Coscine.Database.DataModel;
using Coscine.Database.ReturnObjects;
using Coscine.Database.Util;
using LinqKit;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text.RegularExpressions;
namespace Coscine.Database.Models
{
public class ProjectModel : DatabaseModel<Project>
{
public bool IsDeleted(Guid id)
{
return DatabaseConnection.ConnectToDatabase((db) =>
{
return
(from tableEntry in GetITableFromDatabase(db)
where tableEntry.Id == id
&& tableEntry.Deleted
select tableEntry).Count() == 1;
});
}
public override Project GetById(Guid id)
{
var expression = GetIdFromObject();
return DatabaseConnection.ConnectToDatabase((db) =>
{
return
(from tableEntry in GetITableFromDatabase(db).AsExpandable()
where expression.Invoke(tableEntry) == id
&& !tableEntry.Deleted
select tableEntry).FirstOrDefault();
});
}
public Project GetBySlug(String slug)
{
return GetWhere((tableEntry) => tableEntry.Slug == slug);
}
public Project GetByIdIncludingDeleted(Guid id)
{
var expression = GetIdFromObject();
return DatabaseConnection.ConnectToDatabase((db) =>
{
return
(from tableEntry in GetITableFromDatabase(db).AsExpandable()
where expression.Invoke(tableEntry) == id
select tableEntry).FirstOrDefault();
});
}
public override Project GetWhere(Expression<Func<Project, bool>> whereClause)
{
return DatabaseConnection.ConnectToDatabase((db) =>
{
return
(from tableEntry in GetITableFromDatabase(db).AsExpandable()
where whereClause.Invoke(tableEntry)
&& !tableEntry.Deleted
select tableEntry).FirstOrDefault();
});
}
public override IEnumerable<Project> GetAll()
{
return DatabaseConnection.ConnectToDatabase((db) =>
{
return
(from tableEntry in GetITableFromDatabase(db)
where !tableEntry.Deleted
select tableEntry).ToList();
});
}
public override IEnumerable<Project> GetAllWhere(Expression<Func<Project, bool>> whereClause)
{
return DatabaseConnection.ConnectToDatabase((db) =>
{
return
(from tableEntry in GetITableFromDatabase(db).AsExpandable()
where whereClause.Invoke(tableEntry)
&& !tableEntry.Deleted
select tableEntry).ToList();
});
}
public override int Update(Project databaseObject)
{
if (!databaseObject.Deleted)
{
return DatabaseConnection.ConnectToDatabase((db) =>
{
return (int)db.Update(databaseObject).State;
});
}
else
{
return 0;
}
}
public override int Delete(Project databaseObject)
{
databaseObject.Deleted = true;
return DatabaseConnection.ConnectToDatabase((db) => (int)db.Update(databaseObject).State);
}
public int HardDelete(Project databaseObject)
{
return DatabaseConnection.ConnectToDatabase((db) => (int)db.Remove(databaseObject).State);
}
public int HardDelete(Expression<Func<Project, bool>> whereClause)
{
return DatabaseConnection.ConnectToDatabase((db) =>
{
return (int)db.Remove(from tableEntry in GetITableFromDatabase(db).AsExpandable()
where whereClause.Invoke(tableEntry)
select tableEntry).State;
});
}
public Project StoreFromObject(ProjectObject projectObject, User user, IEnumerable<ProjectQuota> defaultProjectQuotas)
{
if (!projectObject.Disciplines.Any() || !projectObject.Organizations.Any())
{
throw new ArgumentException("Discipline and Institute are necessary!");
}
Project project = new Project()
{
Description = projectObject.Description,
DisplayName = projectObject.DisplayName,
StartDate = projectObject.StartDate,
EndDate = projectObject.EndDate,
Keywords = projectObject.Keywords,
ProjectName = projectObject.ProjectName,
PrincipleInvestigators = projectObject.PrincipleInvestigators,
GrantId = projectObject.GrantId,
Slug = GenerateSlug(projectObject),
VisibilityId = projectObject.Visibility.Id,
// DateCreated is skipped here. Value set automatically by the database.
Creator = user.Id,
};
Insert(project);
try
{
SetDisciplines(project, projectObject.Disciplines);
SetOrganizations(project, projectObject.Organizations);
SetQuotas(project, defaultProjectQuotas);
}
catch (Exception)
{
HardDelete(project);
throw;
}
SetOwner(project, user);
return project;
}
public List<OrganizationCountObject> GetProjectCountByOrganization()
{
return DatabaseConnection.ConnectToDatabase((db) =>
{
return (from p in db.Projects
join pi in db.ProjectInstitutes on p.Id equals pi.ProjectId into joinedPi
from jpi in joinedPi.DefaultIfEmpty()
where !p.Deleted
group jpi by jpi.OrganizationUrl into g
select new OrganizationCountObject(g.Key, g.Count())).ToList();
});
}
private string GenerateSlug(ProjectObject projectObject)
{
// create slug for project
var slug = projectObject.DisplayName;
slug = slug.ToLower();
slug = Regex.Replace(slug, @"[\s-]+", "-");
slug = Regex.Replace(slug, "[^a-z0-9-]*|", "");
slug = Regex.Replace(slug, "^-|-$", "");
Random r = new Random();
int rInt = r.Next(0, 9000000) + 1000000;
string fullSlug = "" + rInt;
if (slug.Length >= 7)
{
rInt = r.Next(0, 9000) + 1000;
fullSlug = slug;
}
if (GetBySlug(fullSlug) != null)
{
if (slug.Length >= 7)
{
fullSlug = slug + "-";
}
while (GetBySlug(fullSlug + rInt) != null)
{
rInt++;
}
fullSlug += rInt;
}
return fullSlug;
}
private void SetDisciplines(Project project, IEnumerable<DisciplineObject> disciplines)
{
ProjectDisciplineModel projectDisciplineModel = new ProjectDisciplineModel();
foreach (var oldDiscipline in projectDisciplineModel.GetAllWhere((projectDiscipline) => projectDiscipline.ProjectId == project.Id))
{
projectDisciplineModel.Delete(oldDiscipline);
}
foreach (var discipline in disciplines)
{
projectDisciplineModel.Insert(new ProjectDiscipline()
{
ProjectId = project.Id,
DisciplineId = discipline.Id
});
}
}
private void SetOrganizations(Project project, IEnumerable<OrganizationObject> organizations)
{
ProjectInstituteModel projectInstituteModel = new ProjectInstituteModel();
foreach (var oldInstitute in projectInstituteModel.GetAllWhere((projectInstitute) => projectInstitute.ProjectId == project.Id))
{
projectInstituteModel.Delete(oldInstitute);
}
foreach (var organization in organizations)
{
projectInstituteModel.Insert(new ProjectInstitute()
{
ProjectId = project.Id,
OrganizationUrl = organization.Url,
});
}
}
public void SetQuotas(Project project, IEnumerable<ProjectQuota> defaultProjectQuotas)
{
ProjectQuotaModel projectQuotaModel = new ProjectQuotaModel();
ResourceTypeModel resourceTypeModel = new ResourceTypeModel();
foreach (var resourceType in resourceTypeModel.GetAll())
{
int quota = 0;
int maxQuota = 0;
var tDefaultQuotas = defaultProjectQuotas.Where(x => x.ResourceTypeId == resourceType.Id);
if (tDefaultQuotas.Any())
{
quota = tDefaultQuotas.First().Quota;
maxQuota = tDefaultQuotas.First().MaxQuota;
}
projectQuotaModel.Insert(new ProjectQuota
{
ProjectId = project.Id,
ResourceTypeId = resourceType.Id,
Quota = quota,
MaxQuota = maxQuota
});
}
}
public ProjectRole SetOwner(Project project, User user)
{
ProjectRoleModel projectRoleModel = new ProjectRoleModel();
ProjectRole projectRole = new ProjectRole()
{
RelationId = Guid.NewGuid(),
ProjectId = project.Id,
UserId = user.Id,
RoleId = new RoleModel().GetWhere((x) => x.DisplayName == "Owner").Id
};
projectRoleModel.Insert(projectRole);
return projectRole;
}
public bool HasAccess(User user, Guid projectId, params string[] allowedAccess)
{
return HasAccess(user, GetById(projectId), allowedAccess);
}
public bool HasAccess(User user, Project project, params string[] allowedAccess)
{
ProjectRoleModel projectRoleModel = new ProjectRoleModel();
allowedAccess = allowedAccess.Select(x => x.ToLower().Trim()).ToArray();
IEnumerable<ProjectRole> projectRoles = projectRoleModel.GetAllWhere(
(projectRoleRelation) => projectRoleRelation.ProjectId == project.Id &&
projectRoleRelation.UserId == user.Id &&
allowedAccess.Contains(projectRoleRelation.Role.DisplayName.ToLower()));
return projectRoles.Any();
}
private IEnumerable<Project> GetWithAccess(User user, string[] allowedAccess, Func<IEnumerable<Guid>, IEnumerable<Project>> filter)
{
ProjectRoleModel projectRoleModel = new ProjectRoleModel();
allowedAccess = allowedAccess.Select(x => x.ToLower().Trim()).ToArray();
var allUserProjectRoles = projectRoleModel.GetAllWhere((projectRoleRelation) => projectRoleRelation.UserId == user.Id &&
allowedAccess.Contains(projectRoleRelation.Role.DisplayName.ToLower()));
var allowedProjectIds = allUserProjectRoles.Select((projectRole) => projectRole.ProjectId);
var allowedProjects = filter.Invoke(allowedProjectIds);
return allowedProjects.ToList();
}
public IEnumerable<Project> GetWithAccess(User user, params string[] allowedAccess)
{
return GetWithAccess(user, allowedAccess, (allowedProjectIds) => GetAllWhere((project) => allowedProjectIds.Contains(project.Id)));
}
public IEnumerable<Project> GetTopLevelWithAccess(User user, params string[] allowedAccess)
{
return GetWithAccess(user, allowedAccess, (_) => GetAllWhere((project) =>
(
// all accessible projects that have no parents
(project.SubProjectSubProjectNavigations.Count == 0)
|| // all accessible projects that have no accessible parents
(
project.SubProjectSubProjectNavigations.All(
(parentProjects) =>
parentProjects.Project.ProjectRoles.All((projectRole) => projectRole.UserId != user.Id)
)
)
)
&& project.ProjectRoles.Any((projectRole) => projectRole.UserId == user.Id))
);
}
public void AddResource(Project project, Resource resource)
{
ProjectResourceModel projectResourceModel = new ProjectResourceModel();
if (projectResourceModel.GetAllWhere((projectResource) => projectResource.ResourceId == resource.Id && projectResource.ProjectId == project.Id).Any())
{
throw new InvalidOperationException("Resource is already assigned to project!");
}
ProjectResource newProjectResource = new ProjectResource
{
ProjectId = project.Id,
ResourceId = resource.Id
};
projectResourceModel.Insert(newProjectResource);
}
public int UpdateByObject(Project project, ProjectObject projectObject)
{
if (!projectObject.Disciplines.Any() || !projectObject.Organizations.Any())
{
throw new ArgumentException("Discipline and Institute are necessary!");
}
project.Description = projectObject.Description;
project.DisplayName = projectObject.DisplayName;
project.StartDate = projectObject.StartDate;
project.EndDate = projectObject.EndDate;
project.Keywords = projectObject.Keywords;
project.ProjectName = projectObject.ProjectName;
project.PrincipleInvestigators = projectObject.PrincipleInvestigators;
project.GrantId = projectObject.GrantId;
SetDisciplines(project, projectObject.Disciplines);
SetOrganizations(project, projectObject.Organizations);
project.VisibilityId = projectObject.Visibility.Id;
// Project creator can not be altered after creation
// Project DateCreated can not be altered after creation
return Update(project);
}
public ProjectObject CreateReturnObjectFromDatabaseObject(Project project)
{
return CreateReturnObjectFromDatabaseObject(project, new Guid());
}
public ProjectObject CreateReturnObjectFromDatabaseObject(Project project, Guid parentId)
{
IEnumerable<DisciplineObject> disciplines = new List<DisciplineObject>();
DisciplineModel disciplineModel = new DisciplineModel();
disciplines = disciplineModel.GetAllWhere((discipline) => (from relation in discipline.ProjectDisciplines where relation.ProjectId == project.Id select relation).Any())
.Select((discipline) => new DisciplineObject(discipline.Id, discipline.Url, discipline.DisplayNameDe, discipline.DisplayNameEn));
IEnumerable<OrganizationObject> organizations = new List<OrganizationObject>();
ProjectInstituteModel projectInstituteModel = new ProjectInstituteModel();
organizations = projectInstituteModel.GetAllWhere((projectInstitute) => (projectInstitute.ProjectId == project.Id))
.Select((projectInstitute) => new OrganizationObject(projectInstitute.OrganizationUrl, projectInstitute.OrganizationUrl));
if (project.Visibility == null && project.VisibilityId.HasValue)
{
VisibilityModel visibilityModel = new VisibilityModel();
project.Visibility = visibilityModel.GetById(project.VisibilityId.Value);
}
return new ProjectObject(project.Id,
project.Description,
project.DisplayName,
project.StartDate,
project.EndDate,
project.Keywords,
project.ProjectName, project.PrincipleInvestigators, project.GrantId,
disciplines,
organizations,
project.Visibility == null ? null : new VisibilityObject(project.Visibility.Id, project.Visibility.DisplayName),
project.Slug,
project.DateCreated,
parentId,
project.Creator,
project.Deleted
);
}
public List<Project> ListToRootProject(Project project, User user)
{
List<Project> projectList = new List<Project>
{
project
};
ProjectRoleModel projectRoleModel = new ProjectRoleModel();
var currentProject = project;
IEnumerable<Project> list;
bool continueLoop = true;
do
{
list = GetAllWhere((dbProject) => (from subProject in dbProject.SubProjectProjects
where subProject.SubProjectId == currentProject.Id
&& !subProject.Project.Deleted
select subProject).Any());
if (list.Any())
{
currentProject = list.First();
bool authorized = true;
if (user != null)
{
authorized = projectRoleModel.GetAllWhere((dbProjectRole) =>
dbProjectRole.UserId == user.Id
&& dbProjectRole.ProjectId == currentProject.Id).Any();
}
if (projectList.Contains(currentProject) || !authorized)
{
continueLoop = false;
}
else
{
projectList.Add(currentProject);
}
}
else
{
continueLoop = false;
}
} while (continueLoop);
return projectList;
}
public override Expression<Func<Project, Guid>> GetIdFromObject()
{
return databaseObject => databaseObject.Id;
}
public override Microsoft.EntityFrameworkCore.DbSet<Project> GetITableFromDatabase(CoscineDB db)
{
return db.Projects;
}
public override void SetObjectId(Project databaseObject, Guid id)
{
databaseObject.Id = id;
}
public string GetMetadataCompleteness(ProjectObject projectObject)
{
var maxCount = 0;
var counted = 0;
var projectProperties = typeof(ProjectObject).GetProperties();
foreach (var property in projectProperties)
{
if (property == null
|| property.PropertyType == typeof(Guid)
|| property.Name == "Slug")
{
continue;
}
maxCount++;
if (property.PropertyType == typeof(string)
&& property.GetValue(projectObject) != null
&& !string.IsNullOrEmpty(property.GetValue(projectObject).ToString()))
{
counted++;
}
else if (property.PropertyType == typeof(DateTime)
&& property.GetValue(projectObject) != null)
{
counted++;
}
else if (property.PropertyType == typeof(IEnumerable<DisciplineObject>)
&& property.GetValue(projectObject) != null
&& ((IEnumerable<DisciplineObject>)property.GetValue(projectObject)).Any())
{
counted++;
}
else if (property.PropertyType == typeof(IEnumerable<OrganizationObject>)
&& property.GetValue(projectObject) != null
&& ((IEnumerable<OrganizationObject>)property.GetValue(projectObject)).Any())
{
counted++;
}
else if (property.PropertyType == typeof(VisibilityObject)
&& property.GetValue(projectObject) != null)
{
counted++;
}
}
return $"{counted}/{maxCount}";
}
}
}