public bool ApplyForProject(int researcherId, int projectId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
Project project = ctx.Projects.FirstOrDefault(p => p.ProjectId == projectId);
User user = ctx.Users.FirstOrDefault(u => u.UserId == researcherId);
int noOfMatches = project.Expertises.Select(ex => ex.ExpertiseId).ToList().Intersect(user.ResearcherExpertises.Select(ex => ex.ExpertiseId).ToList()).Count();
string matchScore = string.Format("{0}/{1}", noOfMatches, project.Expertises.Count);
if (ctx.ResearcherApprovals.Any(appr => appr.ResearcherId == researcherId && appr.ProjectId == projectId))
{
return(false);
}
else
{
ResearcherApproval ra = new ResearcherApproval();
ra.ApprovalStatusId = Constants.APPROVAL_STS_APPLIED;
ra.ProjectId = projectId;
ra.ResearcherId = researcherId;
ra.HasResearcherApplied = true;
ra.ExpertiseMatchScore = matchScore;
ctx.ResearcherApprovals.Add(ra);
ctx.SaveChanges();
return(true);
}
}
}
List <ProjectDto> GetProjectsForResearcher(int researcherId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
//get all availabilities
List <ResearcherAvailability> availabilities = ctx.ResearcherAvailabilities.Where(r => r.ResearcherId == researcherId).ToList();
//filter out that are already taken
for (int i = 0; i < availabilities.Count; i++)
{
if (availabilities[i].ResearcherApprovals != null && availabilities[i].ResearcherApprovals.Any(appr => appr.ApprovalStatusId == Constants.APPROVAL_STS_APPROVED || appr.ApprovalStatusId == Constants.APPROVAL_STS_NEEDINFO || appr.ApprovalStatusId == Constants.APPROVAL_STS_BACKFORREV))
{
availabilities.Remove(availabilities[i]);
}
}
//get all projects
List <Project> projects = ctx.Projects.Where(p => p.IsPublished == true && p.Approved == true).ToList();
//filter out that doesnt fall in availability range.
for (int j = 0; j < projects.Count; j++)
{
if (!availabilities.Any(a => projects[j].StartDate <= a.StartDate && projects[j].EndDate >= a.EndDate))
{
projects.Remove(projects[j]);
}
}
List <ProjectDto> projectDto = (from p in projects select new ProjectDto {
ProjectId = p.ProjectId, StartDate = p.StartDate, EndDate = p.EndDate, ProjectName = p.ProjectName, Description = p.Description, ApprovedBy = p.ApprovedBy, ApprovedDate = p.ApprovedDate, Approved = p.Approved, IsPublished = p.IsPublished
}).ToList();
return(projectDto);
}
}
public List <ResearcherApprovalDto> GetResearcherApprovalStatus(int researcherId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
var query = from ra in ctx.ResearcherApprovals where ra.ResearcherId == researcherId select new ResearcherApprovalDto {
ResearcherId = ra.ResearcherId, ApprovalStatus = ra.ApprovalStatus.Status, ProjectName = ra.Project.ProjectName, InfoRequested = ra.InfoRequested
};
return(query.ToList());
}
}
public bool SaveInformationRequested(int availabilityId, int projectId, string informationRequested)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
ResearcherApproval approval = ctx.ResearcherApprovals.FirstOrDefault(ra => ra.ResearcherAvailabilityId == availabilityId && ra.ProjectId == projectId);
approval.InfoRequested = informationRequested;
ctx.SaveChanges();
return(true);
}
}
public bool SaveInformationRequested(int researcherId, int projectId, string informationRequested)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
ResearcherApproval approval = ctx.ResearcherApprovals.FirstOrDefault(ra => ra.ResearcherId == researcherId && ra.ProjectId == projectId);
approval.InfoRequested = informationRequested;
approval.ApprovalStatusId = Constants.APPROVAL_STS_APPLIED;
ctx.SaveChanges();
return(true);
}
}
public List <ProjectDto> GetProjects(int researcherId)
{
List <ProjectDto> projectDtos = new List <ProjectDto>();
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
DataAccess.User user = ctx.Users.First(u => u.UserId == researcherId);
List <ResearcherAvailability> availabilities = user.ResearcherAvailabilities.ToList();
//get all projects
List <Project> projects = ctx.Projects.Where(p => p.IsPublished == true && p.Approved == true).ToList();
for (int projIndex = 0; projIndex < projects.Count; projIndex++)
{
int projectId = projects[projIndex].ProjectId;
List <ResearcherApproval> approvals = ctx.ResearcherApprovals.Where(a => a.ResearcherId == researcherId && a.ProjectId == projectId).ToList();
if (projects[projIndex].State.Equals(user.State))
{
continue;
}
bool hasAvailabilityMatch = false;
DataAccess.ResearcherApproval researcherApproval = null;
foreach (ResearcherAvailability availability in availabilities)
{
if (availability.Month >= projects[projIndex].StartDate.Month && availability.Month <= projects[projIndex].EndDate.Month)
{
hasAvailabilityMatch = true;
}
}
if (!hasAvailabilityMatch)
{
continue;
}
researcherApproval = ctx.ResearcherApprovals.Where(ra => ra.ProjectId == projectId && ra.ResearcherId == researcherId).FirstOrDefault();
ProjectDto projectDto = new ProjectDto();
projectDto.ProjectId = projects[projIndex].ProjectId;
projectDto.ProjectName = projects[projIndex].ProjectName;
projectDto.Description = projects[projIndex].Description;
projectDto.State = projects[projIndex].State;
projectDto.StartDate = projects[projIndex].StartDate;
projectDto.EndDate = projects[projIndex].EndDate;
projectDto.Approved = projects[projIndex].Approved;
projectDto.Status = (researcherApproval != null ? researcherApproval.ApprovalStatus.Status : "Available");
projectDto.InfoRequested = (researcherApproval != null ? researcherApproval.InfoRequested : string.Empty);
projectDtos.Add(projectDto);
}
}
return(projectDtos);
}
public List <ResearcherAvailabilityDto> GetResearcherAvailability(int researcherId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
var query = from a in ctx.ResearcherAvailabilities select new ResearcherAvailabilityDto()
{
AvailabilityId = a.AvailabilityId, ResearcherId = a.ResearcherId, StartDate = a.StartDate, EndDate = a.EndDate
};
return(query.ToList());
}
}
public List <ExpertiseDto> GetAllExpertise()
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
var query = from e in ctx.Expertises select new ExpertiseDto()
{
ExpertiseId = e.ExpertiseId, ExpertiseName = e.ExpertiseName
};
return(query.ToList());
}
}
public int AddResearcherExpertise(int researcherId, int expertiseId, string affilicatedOrgName)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
DataAccess.ResearcherExpertise re = new DataAccess.ResearcherExpertise();
re.ResearcherId = researcherId;
re.ExpertiseId = expertiseId;
re.AffiliatedOrgName = affilicatedOrgName;
ctx.ResearcherExpertises.Add(re);
ctx.SaveChanges();
return(re.ResearchExpertiseId);
}
}
public List <ResearcherExpertiseDto> GetResearcherExpertise(int researcherId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
var query = from re in ctx.ResearcherExpertises where re.ResearcherId == researcherId select new ResearcherExpertiseDto()
{
ResearchExpertiseId = re.ResearchExpertiseId, ResearcherId = re.ResearcherId, ExpertiseId = re.ExpertiseId, Expertise = new ExpertiseDto()
{
ExpertiseId = re.Expertise.ExpertiseId, ExpertiseName = re.Expertise.ExpertiseName
}, AffiliatedOrgName = re.AffiliatedOrgName
};
return(query.ToList());
}
}
public int AddResearcherAvailability(int researcherId, DateTime startDate, DateTime endDate)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
DataAccess.ResearcherAvailability ra = new DataAccess.ResearcherAvailability();
ra.ResearcherId = researcherId;
ra.StartDate = startDate;
ra.EndDate = endDate;
ctx.ResearcherAvailabilities.Add(ra);
ctx.SaveChanges();
return(ra.AvailabilityId);
}
}
public bool DeleteResearcherExpertise(int researcherExpertiseId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
DataAccess.ResearcherExpertise re = ctx.ResearcherExpertises.FirstOrDefault(r => r.ResearchExpertiseId == researcherExpertiseId);
if (re == null)
{
return(false);
}
else
{
ctx.ResearcherExpertises.Remove(re);
ctx.SaveChanges();
return(true);
}
}
}
public bool DeleteResearcherAvailability(int availabilityId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
DataAccess.ResearcherAvailability ra = ctx.ResearcherAvailabilities.FirstOrDefault(r => r.AvailabilityId == availabilityId);
if (ra == null)
{
return(false);
}
else
{
ctx.ResearcherAvailabilities.Remove(ra);
ctx.SaveChanges();
return(true);
}
}
}
public bool UpdateResearcherAvailability(int availabilityId, DateTime startDate, DateTime endDate)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
DataAccess.ResearcherAvailability ra = ctx.ResearcherAvailabilities.FirstOrDefault(r => r.AvailabilityId == availabilityId);
if (ra == null)
{
return(false);
}
else
{
ra.StartDate = startDate;
ra.EndDate = endDate;
return(true);
}
}
}
public bool UpdateResearcherExpertise(int researcherExpertiseId, int expertiseId, string affilicatedOrgName)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
DataAccess.ResearcherExpertise re = ctx.ResearcherExpertises.FirstOrDefault(r => r.ResearchExpertiseId == researcherExpertiseId);
if (re == null)
{
return(false);
}
else
{
re.ExpertiseId = expertiseId;
re.AffiliatedOrgName = affilicatedOrgName;
return(true);
}
}
}
public bool ApplyForProject(int researcherAvailabilityId, int projectId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
if (ctx.ResearcherApprovals.Any(appr => appr.ResearcherAvailabilityId == researcherAvailabilityId && (appr.ApprovalStatusId == Constants.APPROVAL_STS_APPROVED || appr.ApprovalStatusId == Constants.APPROVAL_STS_NEEDINFO || appr.ApprovalStatusId == Constants.APPROVAL_STS_BACKFORREV)))
{
return(false);
}
else
{
ResearcherApproval ra = new ResearcherApproval();
ra.ApprovalStatusId = Constants.APPROVAL_STS_NOT_STARTED;
ra.ProjectId = projectId;
ra.ResearcherAvailabilityId = researcherAvailabilityId;
ra.ResearcherId = ctx.ResearcherAvailabilities.First(r => r.AvailabilityId == researcherAvailabilityId).ResearcherId;
ctx.ResearcherApprovals.Add(ra);
ctx.SaveChanges();
return(true);
}
}
}
public ProjectDto GetProjectById(int projectId, int researcherId)
{
using (ScheduleExEntities ctx = new ScheduleExEntities())
{
Project project = ctx.Projects.First(p => p.ProjectId == projectId);
ResearcherApproval researcherApproval = ctx.ResearcherApprovals.Where(ra => ra.ProjectId == projectId && ra.ResearcherId == researcherId).FirstOrDefault();
ProjectDto projectDto = new ProjectDto();
projectDto.ProjectId = project.ProjectId;
projectDto.ProjectName = project.ProjectName;
projectDto.Description = project.Description;
projectDto.State = project.State;
projectDto.StartDate = project.StartDate;
projectDto.EndDate = project.EndDate;
projectDto.Approved = project.Approved;
projectDto.Status = (researcherApproval != null ? researcherApproval.ApprovalStatus.Status : "Available");
projectDto.InfoRequested = (researcherApproval != null ? researcherApproval.InfoRequested : string.Empty);
return(projectDto);
}
}