public ICollection <Task> GetUsersTasks(string[] membersUIds, bool onlyOpen, bool includeUnassigned, string[] projectUIds = null)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
var tasks = context.Tasks.Include("TaskInfo");
if (includeUnassigned && projectUIds == null)
{
tasks = tasks.Where(t => membersUIds.Contains(t.TaskAssigneeUId) || (t.TaskAssigneeUId == null && membersUIds.Contains(t.TaskOwnerUId)));
}
else if (includeUnassigned && projectUIds != null)
{
tasks = tasks.Where(t => membersUIds.Contains(t.TaskAssigneeUId) || (t.TaskAssigneeUId == null && projectUIds.Contains(t.ProjectUId)));
}
else
{
tasks = tasks.Where(t => membersUIds.Contains(t.TaskAssigneeUId));
}
if (onlyOpen)
{
tasks = tasks.Where(t => t.Status < 2);
}
return(tasks.ToList());
}
}
public void CreateHourCosts(Project project, Project similarProject)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
// get similar costs
RoleHourCost[] similarCosts = null;
if(similarProject!=null)
similarCosts = GetHourCosts(similarProject.ProjectUId);
// fore each role at this project
foreach (Role r in project.Roles) {
// checks if there is a role with the same shortname at the similar project, and if so, uses its costs
Role similarRole = null;
string roleName = r.RoleShortName.ToLower();
if(similarProject!=null)
similarRole = similarProject.Roles.FirstOrDefault(sr => sr.RoleShortName.ToLower() == roleName);
RoleHourCost similarHourCost = null;
if(similarRole!=null && similarCosts!=null)
similarHourCost = similarCosts.FirstOrDefault(c => c.RoleUId == similarRole.RoleUId);
if(similarHourCost==null)
similarHourCost = new RoleHourCost() { Price = 0, Cost = 0 };
// only if role is new
RoleHourCost oldCost = context.RoleHourCosts.SingleOrDefault(h => h.RoleUId == r.RoleUId);
if (oldCost == null) {
RoleHourCost cost = new RoleHourCost() { RoleUId = r.RoleUId, ProjectUId = project.ProjectUId, Cost = similarHourCost.Cost, Price = similarHourCost.Price };
context.RoleHourCosts.AddObject(cost);
}
}
context.SaveChanges();
}
}
public ICollection <ScrumFactory.Project> GetAllProjects(string memberUId, System.DateTime startDate, System.DateTime endDate, string filter = null, int top = 0, int skip = 0)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
IQueryable <ScrumFactory.Project> projects = context.Projects.Where(p => p.CreateDate >= startDate && p.CreateDate <= endDate);
projects = RestrictProjectsFromMember(projects, memberUId);
projects = RestrictProjectsByFilter(projects, filter);
projects = projects.OrderByDescending(p => p.ProjectNumber);
if (top > 0)
{
projects = projects.Skip(skip).Take(top);
}
// include only the memberUId memberships at the projects
if (!string.IsNullOrEmpty(memberUId))
{
var projectsWithMemberships = projects.Select(p => new { Project = p, Memberships = p.Memberships.Where(m => m.MemberUId == memberUId && m.DayAllocation > 0 && m.IsActive == true) });
return(projectsWithMemberships.AsEnumerable().Select(p => p.Project).ToList <Project>());
}
return(projects.ToList());
}
}
public ICollection <ProjectMembership> GetActiveProjectsFromUsers(string[] memberUIds, bool includeProposals, bool includeSupport)
{
using (var context = new ScrumFactoryEntities(this.connectionString))
{
var membershipsQuery = context.ProjectMemberships.Include("Project.Sprints").Where(m =>
memberUIds.Contains(m.MemberUId) && m.IsActive == true && m.Project.IsSuspended == false);
if (includeProposals)
{
membershipsQuery = membershipsQuery.Where(m => m.Project.Status == (int)ProjectStatus.PROJECT_STARTED || m.Project.Status == (int)ProjectStatus.PROPOSAL_APPROVED || m.Project.Status == (int)ProjectStatus.PROJECT_SUPPORT || m.Project.Status == (int)ProjectStatus.PROPOSAL_CREATION);
}
else
{
membershipsQuery = membershipsQuery.Where(m => m.Project.Status == (int)ProjectStatus.PROJECT_STARTED || m.Project.Status == (int)ProjectStatus.PROPOSAL_APPROVED || m.Project.Status == (int)ProjectStatus.PROJECT_SUPPORT);
}
if (includeSupport)
{
membershipsQuery = membershipsQuery.Where(m => m.Project.ProjectType == (int)ProjectTypes.NORMAL_PROJECT || m.Project.ProjectType == (int)ProjectTypes.TICKET_PROJECT);
}
else
{
membershipsQuery = membershipsQuery.Where(m => m.Project.ProjectType == (int)ProjectTypes.NORMAL_PROJECT);
}
var memberships = membershipsQuery.ToArray();
// avoid recurisve XML
foreach (var m in memberships)
{
m.Project.Memberships = null;
}
return(memberships);
}
}
public void SavePokerCard(PokerCard card)
{
using (var context = new ScrumFactoryEntities(this.connectionString))
{
PokerCard oldCard = context.PokerCards.Where(c => c.MemberUId == card.MemberUId && c.BacklogItemUId == card.BacklogItemUId).SingleOrDefault();
if (oldCard == null)
{
card.VoteDate = System.DateTime.Now;
context.PokerCards.AddObject(card);
}
else
{
if (!card.Value.HasValue)
{
context.PokerCards.DeleteObject(oldCard);
var now = System.DateTime.Now.AddHours(-2);
var strNow = now.Year + "-" + now.Month + "-" + now.Day + " " + now.Hour + ":" + now.Minute + ":" + now.Second;
context.ExecuteStoreCommand("DELETE FROM factory.PokerCard WHERE VoteDate < '" + strNow + "'");
}
else
{
card.VoteDate = System.DateTime.Now;
context.ApplyCurrentValues <PokerCard>("PokerCard", card);
}
}
context.SaveChanges();
}
}
public void SaveMemberAvatar(MemberAvatar avatar)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
MemberProfile oldMember = GetMember(avatar.MemberUId);
MemberAvatar oldAvatar = GetMemberAvatar(avatar.MemberUId);
if (oldMember == null)
{
throw new System.Exception("member not found");
}
if (oldAvatar == null)
{
context.MembersAvatar.AddObject(avatar);
}
else
{
context.AttachTo("MembersAvatar", oldAvatar);
context.ApplyCurrentValues <MemberAvatar>("MembersAvatar", avatar);
}
context.SaveChanges();
}
}
public ICollection <Task> GetItemTasks(string backlogItemUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
var tasks = context.Tasks.Where(t => t.BacklogItemUId == backlogItemUId);
return(tasks.ToList());
}
}
public ICollection <ScrumFactory.Project> GetOpenProjects(string memberUId, string filter = null, int top = 0, int skip = 0)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
IQueryable <ScrumFactory.Project> projects = context.Projects.Where(p =>
p.Status == (short)ScrumFactory.ProjectStatus.PROJECT_STARTED ||
p.Status == (short)ScrumFactory.ProjectStatus.PROPOSAL_APPROVED ||
p.Status == (short)ScrumFactory.ProjectStatus.PROJECT_SUPPORT ||
p.Status == (short)ScrumFactory.ProjectStatus.PROPOSAL_CREATION)
.Where(p => p.IsSuspended == false);
projects = RestrictProjectsFromMember(projects, memberUId);
projects = RestrictProjectsByFilter(projects, filter);
projects = projects.OrderByDescending(p => p.ProjectNumber);
if (top > 0)
{
projects = projects.Skip(skip).Take(top);
}
// include only the memberUId memberships at the projects
if (!string.IsNullOrEmpty(memberUId))
{
var projectsWithMemberships = projects.Select(p => new { Project = p, Memberships = p.Memberships.Where(m => m.MemberUId == memberUId && m.DayAllocation > 0 && m.IsActive == true) });
return(projectsWithMemberships.AsEnumerable().Select(p => p.Project).ToList <Project>());
}
return(projects.ToList());
}
}
/// <summary>
/// Gets the entire backlog of a project from the database and its all its planned hours.
/// </summary>
/// <param name="projectUId">The projectUId</param>
/// <returns>A list of backlog items</returns>
public ICollection <ScrumFactory.BacklogItem> GetBacklog(string projectUId, int?planningNumber = null, System.DateTime fromDate = new System.DateTime(), System.DateTime untilDate = new System.DateTime())
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
// get all items from the backlog
IQueryable <BacklogItem> items =
context.BacklogItems.Where(i => i.ProjectUId == projectUId);
items = FilterBacklog(items, fromDate, untilDate);
// now selects the current planninh hours for the items
if (planningNumber.HasValue)
{
var itemsWithHours = items
.Select(
i => new {
BacklogItem = i,
PlannedHours = i.PlannedHours.Where(ih => ih.PlanningNumber == planningNumber)
});
return(itemsWithHours.AsEnumerable().Select(i => i.BacklogItem).OrderBy(i => i.SprintNumber).ThenBy(i => i.BusinessPriority).ThenBy(i => i.BacklogItemNumber).ToList <BacklogItem>());
}
else
{
return(context.BacklogItems.Include("PlannedHours").Where(i => i.ProjectUId == projectUId).AsEnumerable().OrderBy(i => i.SprintNumber).ThenBy(i => i.BusinessPriority).ThenBy(i => i.BacklogItemNumber).ToList());
}
}
}
public bool MemberHasPermissionAtProject(string memberUId, string projectUId, PermissionSets[] permissions)
{
short[] permissionsS = new short[permissions.Length];
for (int i = 0; i < permissions.Length; i++)
{
permissionsS[i] = (short)permissions[i];
}
using (var context = new ScrumFactoryEntities(this.connectionString)) {
// does not make sense
//if (context.Projects.Where(p => p.ProjectUId == projectUId && p.CreateBy == memberUId).Count() > 0)
// return true;
if (context.ProjectMemberships.Where(
pmr => pmr.MemberUId == memberUId &&
pmr.ProjectUId == projectUId &&
pmr.IsActive == true &&
permissionsS.Contains(pmr.Role.PermissionSet)).Count() > 0)
{
return(true);
}
}
return(false);
}
private void SaveBacklogItem(BacklogItem item, bool saveHours)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
SaveBacklogItem(context, item, saveHours);
context.SaveChanges();
}
}
public ICollection<Task> GetItemTasks(string backlogItemUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
var tasks = context.Tasks.Where(t => t.BacklogItemUId == backlogItemUId);
return tasks.ToList();
}
}
public MemberPerformance GetMemberPerformance(string memberUId)
{
MemberPerformance memberPerformance = new ScrumFactory.MemberPerformance() { MemberUId = memberUId };
System.DateTime today = System.DateTime.Today;
int thisYear = today.Year;
int thisMonth = today.Month;
using (var context = new ScrumFactoryEntities(this.connectionString)) {
IQueryable<Task> doneTasks = context.Tasks.Where(t => t.TaskAssigneeUId == memberUId && t.Status == 2);
IQueryable<Task> doneTasksThisMonth = doneTasks.Where(t=> t.EndDate!=null && t.EndDate.Value.Year==thisYear && t.EndDate.Value.Month==thisMonth);
memberPerformance.TasksDone = doneTasks.Count();
decimal? hours = doneTasks.Sum(t => (decimal?)t.EffectiveHours);
memberPerformance.TotalWorkedHours = hours.HasValue ? hours.Value : 0;
memberPerformance.BugsResolved = doneTasksThisMonth.Count(t => t.TaskType == 4);
memberPerformance.ImprovimentsDone = doneTasksThisMonth.Count(t => t.TaskType == 0);
memberPerformance.TasksDoneBeforePlanned = doneTasksThisMonth.Count(t => t.EffectiveHours < t.PlannedHours);
decimal? monthHours = doneTasksThisMonth.Sum(t => (decimal?)t.EffectiveHours);
memberPerformance.MonthWorkedHours = monthHours.HasValue ? monthHours.Value : 0;
}
return memberPerformance;
}
public MemberPerformance GetMemberPerformance(string memberUId)
{
MemberPerformance memberPerformance = new ScrumFactory.MemberPerformance()
{
MemberUId = memberUId
};
System.DateTime today = System.DateTime.Today;
int thisYear = today.Year;
int thisMonth = today.Month;
using (var context = new ScrumFactoryEntities(this.connectionString)) {
IQueryable <Task> doneTasks = context.Tasks.Where(t => t.TaskAssigneeUId == memberUId && t.Status == 2);
IQueryable <Task> doneTasksThisMonth = doneTasks.Where(t => t.EndDate != null && t.EndDate.Value.Year == thisYear && t.EndDate.Value.Month == thisMonth);
memberPerformance.TasksDone = doneTasks.Count();
decimal?hours = doneTasks.Sum(t => (decimal?)t.EffectiveHours);
memberPerformance.TotalWorkedHours = hours.HasValue ? hours.Value : 0;
memberPerformance.BugsResolved = doneTasksThisMonth.Count(t => t.TaskType == 4);
memberPerformance.ImprovimentsDone = doneTasksThisMonth.Count(t => t.TaskType == 0);
memberPerformance.TasksDoneBeforePlanned = doneTasksThisMonth.Count(t => t.EffectiveHours < t.PlannedHours);
decimal?monthHours = doneTasksThisMonth.Sum(t => (decimal?)t.EffectiveHours);
memberPerformance.MonthWorkedHours = monthHours.HasValue ? monthHours.Value : 0;
}
return(memberPerformance);
}
private void UpdateProposalFixedCosts(ScrumFactoryEntities context, Proposal proposal, Proposal oldProposal)
{
// make sure no proposal has null collections
if (proposal.FixedCosts == null)
{
proposal.FixedCosts = new List <ProposalFixedCost>();
}
if (oldProposal.FixedCosts == null)
{
oldProposal.FixedCosts = new List <ProposalFixedCost>();
}
ProposalFixedCost[] newCosts = new ProposalFixedCost[0];
newCosts = proposal.FixedCosts.Where(i => !oldProposal.FixedCosts.Any(oi => oi.ProposalFixedCostUId == i.ProposalFixedCostUId)).ToArray();
ProposalFixedCost[] deletedCosts = new ProposalFixedCost[0];
deletedCosts = oldProposal.FixedCosts.Where(oi => !proposal.FixedCosts.Any(i => i.ProposalFixedCostUId == oi.ProposalFixedCostUId)).ToArray();
ProposalFixedCost[] updatedCosts = new ProposalFixedCost[0];
updatedCosts = proposal.FixedCosts.Where(oi => oldProposal.FixedCosts.Any(i => i.ProposalFixedCostUId == oi.ProposalFixedCostUId)).ToArray();
// add/update/delete proposal items
foreach (ProposalFixedCost cost in newCosts)
{
context.ProposalFixedCosts.AddObject(cost);
}
foreach (ProposalFixedCost cost in deletedCosts)
{
context.ProposalFixedCosts.DeleteObject(cost);
}
foreach (ProposalFixedCost cost in updatedCosts)
{
context.ApplyCurrentValues <ProposalFixedCost>("ProposalFixedCosts", cost);
}
}
public bool CanDeleteProjectRole(string projectUId, string roleUId)
{
// can delete if there is any member with this role
Project project = GetProject(projectUId);
if (project.Memberships.Any(ms => ms.RoleUId == roleUId))
{
return(false);
}
// can delete if is the only scrum master role
if (project.Memberships.Count(ms => ms.Role.PermissionSet == (short)PermissionSets.SCRUM_MASTER && ms.RoleUId != roleUId) == 0)
{
return(false);
}
// can delete if there is any hour planned for this role
using (var context = new ScrumFactoryEntities(this.connectionString)) {
decimal?plannedHoursForThisRole = context.PlannedHours.Where(h => h.RoleUId == roleUId).Sum(h => h.Hours);
if (plannedHoursForThisRole == null || plannedHoursForThisRole == 0)
{
return(true);
}
}
return(false);
}
public Project GetLastSimilarProject(Project project, bool onlyWithProposals = false)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
// first tries to get the last project from same client
var projectQuery = context.Projects
.Include("Roles").Include("Sprints").Include("Memberships")
.Where(p => p.ClientName == project.ClientName && p.ProjectUId != project.ProjectUId);
if (onlyWithProposals)
{
projectQuery = projectQuery.Where(p => context.RoleHourCosts.Any(o => o.ProjectUId == p.ProjectUId && o.Price > 0));
}
Project similar = projectQuery.OrderByDescending(p => p.CreateDate).FirstOrDefault();
if (similar != null)
{
similar.FixRecursiveRelation();
return(similar);
}
// id there is no project from this client before, gets the last one of any client made by me
projectQuery = context.Projects
.Include("Roles").Include("Sprints").Include("Memberships")
.Where(p => p.ProjectUId != project.ProjectUId && p.CreateBy == project.CreateBy);
if (onlyWithProposals)
{
projectQuery = projectQuery.Where(p => context.RoleHourCosts.Any(o => o.ProjectUId == p.ProjectUId && o.Price > 0));
}
similar = projectQuery.OrderByDescending(p => p.CreateDate).FirstOrDefault();
if (similar != null)
{
similar.FixRecursiveRelation();
return(similar);
}
// id there is no project from this client before, gets the last one of any client
projectQuery = context.Projects
.Include("Roles").Include("Sprints").Include("Memberships")
.Where(p => p.ProjectUId != project.ProjectUId);
if (onlyWithProposals)
{
projectQuery = projectQuery.Where(p => context.RoleHourCosts.Any(o => o.ProjectUId == p.ProjectUId && o.Price > 0));
}
similar = projectQuery.OrderByDescending(p => p.CreateDate).FirstOrDefault();
if (similar != null)
{
similar.FixRecursiveRelation();
}
return(similar);
}
}
public Project GetProjectByNumber(int projectNumber)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
var project = context.Projects.Include("Roles").Include("Sprints").Include("Memberships").SingleOrDefault(p => p.ProjectNumber == projectNumber);
project.FixRecursiveRelation();
return(project);
}
}
public ICollection <PokerCard> GetPokerCards(string backlogItemUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString))
{
var minAgo = System.DateTime.Now.AddHours(-1);
return(context.PokerCards.Where(c => c.BacklogItemUId == backlogItemUId && c.VoteDate > minAgo).ToArray());
}
}
public ICollection <Task> GetUserOwnedTasks(string taskOwnerUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
var ownedtasks = context.Tasks.Include("TaskInfo").Where(t => t.TaskOwnerUId == taskOwnerUId && (t.TaskAssigneeUId != taskOwnerUId || t.TaskAssigneeUId == null) && t.Status != (short)TaskStatus.DONE_TASK && t.Status != (short)TaskStatus.CANCELED_TASK);
ownedtasks = ownedtasks.Where(t => context.Projects.Any(p => p.ProjectUId == t.ProjectUId && (p.Status == 3 || p.Status == 5))); // only running projects
return(ownedtasks.ToList());
}
}
public void DeleteSprint(string sprintUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
Sprint oldSprint = GetSprint(sprintUId);
context.AttachTo("Sprints", oldSprint);
context.DeleteObject(oldSprint);
context.SaveChanges();
}
}
public void DeleteProjectRole(string roleUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
Role oldRole = GetProjectRole(roleUId, true);
context.AttachTo("Roles", oldRole);
context.DeleteObject(oldRole);
context.SaveChanges();
}
}
public void DeleteProjectRole(string roleUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
Role oldRole = GetProjectRole(roleUId, true);
context.AttachTo("Roles", oldRole);
context.DeleteObject(oldRole);
context.SaveChanges();
}
}
public void DeleteSprint(string sprintUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
Sprint oldSprint = GetSprint(sprintUId);
context.AttachTo("Sprints", oldSprint);
context.DeleteObject(oldSprint);
context.SaveChanges();
}
}
public ScrumFactory.Project GetProject(string projectUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
context.ContextOptions.ProxyCreationEnabled = false;
var project = context.Projects.Include("Roles").Include("Sprints").Include("Memberships").SingleOrDefault(p => p.ProjectUId == projectUId);
project.FixRecursiveRelation();
return(project);
}
}
public void DeleteProjectConstraint(string constraintUId)
{
using (var context = new ScrumFactoryEntities(connectionString)) {
var constraint = context.ProjectConstraints.SingleOrDefault(c => c.ConstraintUId == constraintUId);
if (constraint == null)
return;
context.ProjectConstraints.DeleteObject(constraint);
context.SaveChanges();
}
}
public void SaveHourCosts(RoleHourCost[] costs)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
foreach (RoleHourCost cost in costs)
{
SaveHourCost(cost, context);
}
context.SaveChanges();
}
}
public void DeleteProjectConstraint(string constraintUId)
{
using (var context = new ScrumFactoryEntities(connectionString)) {
var constraint = context.ProjectConstraints.SingleOrDefault(c => c.ConstraintUId == constraintUId);
if (constraint == null)
return;
context.ProjectConstraints.DeleteObject(constraint);
context.SaveChanges();
}
}
public void RemoveArtifact(string artifactUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
Artifact artifact = context.Artifacts.SingleOrDefault(a => a.ArtifactUId == artifactUId);
if (artifact == null)
return;
context.Artifacts.DeleteObject(artifact);
context.SaveChanges();
};
}
public ICollection <ScrumFactory.Project> GetEngagedProjects(string memberUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
var projectsWithMemberships = context.Projects.
Where(p => p.Memberships.Any(m => m.MemberUId == memberUId && m.DayAllocation > 0 && m.IsActive == true)).
Select(p =>
new { Project = p, Memberships = p.Memberships.Where(m => m.MemberUId == memberUId && m.DayAllocation > 0 && m.IsActive == true) });
return(projectsWithMemberships.AsEnumerable().Select(p => p.Project).ToList <Project>());
}
}
public decimal GetTotalEffectiveHoursByItem(string backlogitemUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
decimal?hrs = context.Tasks.Where(t => t.BacklogItemUId == backlogitemUId).Sum(t => (decimal?)t.EffectiveHours);
if (!hrs.HasValue)
{
return(0);
}
return(hrs.Value);
}
}
public decimal GetTotalPointsDone(string projectUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
decimal?pts = context.BacklogItems.Where(i => i.Status == (short)BacklogItemStatus.ITEM_DONE && i.ProjectUId == projectUId).Sum(i => i.Size);
if (!pts.HasValue)
{
return(0);
}
return(pts.Value);
}
}
public decimal GetTotalBugHoursByProject(string projectUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
decimal?hours = context.Tasks.Where(t => t.ProjectUId == projectUId && (t.TaskType == (short)TaskTypes.BUG_TASK || t.TaskType == (short)TaskTypes.AFTER_DEPLOY_BUG_TASK)).Sum(t => (decimal?)t.EffectiveHours);
if (!hours.HasValue)
{
return(0);
}
return(hours.Value);
}
}
public void DeleteBacklogItem(string backlogItemUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
var item = context.BacklogItems.Include("PlannedHours").SingleOrDefault(b => b.BacklogItemUId == backlogItemUId);
if (item != null) {
context.DeleteObject(item);
context.SaveChanges();
}
}
}
private Role GetProjectRole(string roleUId, bool includePlannedHours)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
IQueryable <Role> roles = context.Roles;
if (includePlannedHours)
{
roles = roles.Include("PlannedHours");
}
return(roles.SingleOrDefault(r => r.RoleUId == roleUId));
}
}
public void RemoveMemberAvatar(string memberUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
MemberAvatar avatar = context.MembersAvatar.SingleOrDefault(a => a.MemberUId == memberUId);
if (avatar != null)
{
context.MembersAvatar.DeleteObject(avatar);
context.SaveChanges();
}
}
}
public int GetMemberDayAllocation(string memberUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
int?allocation = context.ProjectMemberships.Where(m => m.MemberUId == memberUId && m.IsActive).Sum(m => m.DayAllocation);
if (!allocation.HasValue)
{
allocation = 0;
}
return(allocation.Value);
}
}
public decimal GetTotalEffectiveHoursByProject(string projectUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
decimal?hours = context.Tasks.Where(t => t.ProjectUId == projectUId).Sum(t => (decimal?)t.EffectiveHours);
if (!hours.HasValue)
{
return(0);
}
return(hours.Value);
}
}
public AuthorizationInfo GetAuthorizationInfo(string token, int validPeriod = 0)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
if (validPeriod != 0) {
System.DateTime limit = System.DateTime.Now.AddHours(-validPeriod);
return context.AuthorizationInfos.Where(a => a.Token == token && a.IssueDate > limit).FirstOrDefault();
}
else {
return context.AuthorizationInfos.Where(a => a.Token == token).FirstOrDefault();
}
};
}
public void SaveProjectConstraint(ProjectConstraint constraint)
{
using (var context = new ScrumFactoryEntities(connectionString)) {
var old = context.ProjectConstraints.SingleOrDefault(c => c.ConstraintUId == constraint.ConstraintUId);
if (old != null) {
context.AttachTo("ProjectConstraints", old);
context.ApplyCurrentValues<ProjectConstraint>("ProjectConstraints", constraint);
}
else
context.ProjectConstraints.AddObject(constraint);
context.SaveChanges();
}
}
public double GetPointsFactor(string projectUId)
{
double factor = 1;
using (var context = new ScrumFactoryEntities(connectionString)) {
var constraints = context.ProjectConstraints.Where(c => c.ProjectUId == projectUId).ToList();
foreach (var constraint in constraints) {
if (constraint.AdjustPointFactor != 0) {
factor = factor * constraint.AdjustPointFactor;
}
}
}
return factor;
}
public MemberProfile GetMember(string memberUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
// need to get the memberships here in order to calc the DAYOCCUPATION property of the member
var memberWithAllocation = context.MembersProfile
.Where(m => m.MemberUId == memberUId)
.Select(m2 => new { MemberProfile = m2, Memberships = m2.Memberships.Where(ms => ms.DayAllocation > 0) });
MemberProfile member = memberWithAllocation.AsEnumerable().Select(m => m.MemberProfile).SingleOrDefault<MemberProfile>();
return member;
}
}
public bool DeleteBacklogItemGroup(string groupUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
var inUse = context.BacklogItems.Any(i => i.GroupUId == groupUId);
if (inUse)
return false;
var group = context.BacklogItemGroups.SingleOrDefault(g => g.GroupUId == groupUId);
context.BacklogItemGroups.DeleteObject(group);
context.SaveChanges();
return true;
}
}
public void DeleteItemSize(string itemSizeUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
ItemSize size = context.ItemSizes.Include("SizeIdealHours").SingleOrDefault(z => z.ItemSizeUId == itemSizeUId);
if (size == null)
return;
//if (size.SizeIdealHours != null) {
// foreach (SizeIdealHour h in size.SizeIdealHours)
// context.SizeIdealHours.DeleteObject(h);
//}
context.ItemSizes.DeleteObject(size);
context.SaveChanges();
}
}
public void DeleteTaskTag(string tagUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
// first un-mark tasks
var tasks = context.Tasks.Where(t => t.TagUId == tagUId);
foreach (Task task in tasks)
task.TagUId = null;
// now deletes the tag
var oldTag = context.TaskTags.SingleOrDefault(t => t.TagUId == tagUId);
context.TaskTags.DeleteObject(oldTag);
context.SaveChanges();
}
}
public void SaveArtifact(Artifact artifact)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
Artifact oldArtifact = context.Artifacts.SingleOrDefault(a => a.ArtifactUId == artifact.ArtifactUId);
if (oldArtifact == null)
context.Artifacts.AddObject(artifact);
else {
context.AttachTo("Artifacts", oldArtifact);
context.ApplyCurrentValues<Artifact>("Artifacts", artifact);
}
context.SaveChanges();
};
}
public void SaveAuthorizationInfo(AuthorizationInfo info)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
AuthorizationInfo oldInfo = context.AuthorizationInfos.Where(a => a.MemberUId == info.MemberUId).FirstOrDefault();
if (oldInfo == null)
context.AuthorizationInfos.AddObject(info);
else {
context.AttachTo("AuthorizationInfos", oldInfo);
oldInfo.Token = info.Token;
oldInfo.IssueDate = info.IssueDate;
}
context.SaveChanges();
};
}
public bool CanDeleteProjectRole(string projectUId, string roleUId)
{
// can delete if there is any member with this role
Project project = GetProject(projectUId);
if(project.Memberships.Any(ms => ms.RoleUId==roleUId))
return false;
// can delete if is the only scrum master role
if (project.Memberships.Count(ms => ms.Role.PermissionSet==(short)PermissionSets.SCRUM_MASTER && ms.RoleUId != roleUId)==0)
return false;
// can delete if there is any hour planned for this role
using (var context = new ScrumFactoryEntities(this.connectionString)) {
decimal? plannedHoursForThisRole = context.PlannedHours.Where(h => h.RoleUId == roleUId).Sum(h => h.Hours);
if (plannedHoursForThisRole == null || plannedHoursForThisRole == 0)
return true;
}
return false;
}
public decimal GetProjectBudget(string projectUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
decimal? budget = context.Proposals
.Where(p => p.ProjectUId == projectUId && p.ProposalStatus == (short)ProposalStatus.PROPOSAL_APPROVED)
.Sum(p => (decimal?) p.TotalValue);
if (!budget.HasValue)
budget = 0;
decimal? fixedCosts = context.Proposals
.Where(p => p.ProjectUId == projectUId && p.ProposalStatus == (short)ProposalStatus.PROPOSAL_APPROVED)
.Sum(p => (decimal?)p.FixedCosts.Where(f => f.RepassToClient==false).Sum(f => f.Cost));
if (!fixedCosts.HasValue)
fixedCosts = 0;
return (decimal) budget.Value - fixedCosts.Value;
}
}
public ICollection<ScrumFactory.BacklogItem> GetAllUnfinishedBacklogItems(string[] projectUIds)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
// get all unfinished items from the backlog
IQueryable<BacklogItem> items =
context.BacklogItems.Where(i => i.Status < 2 && projectUIds.Contains(i.ProjectUId));
// no selects the current planninh hours for the items
var itemsWithHours = items
.Select(
i => new {
BacklogItem = i,
PlannedHours = i.PlannedHours.Where(ih => ih.PlanningNumber == i.PlannedHours.Max(h => h.PlanningNumber))
});
return itemsWithHours.AsEnumerable().Select(i => i.BacklogItem).OrderBy(i => i.SprintNumber).ThenBy(i => i.BusinessPriority).ThenBy(i => i.BacklogItemNumber).ToList<BacklogItem>();
}
}
public ICollection<ScrumFactory.MemberProfile> GetAllMembers(string filter, int availability, string[] companies, bool activeOnly, string workingWithUId, int top)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
// members and membeeships with allocation
var filteredMembers = context.MembersProfile.Select(m =>
new { MemberProfile = m, Memberships = m.Memberships.Where(ms => ms.DayAllocation > 0) });
if (activeOnly)
filteredMembers = filteredMembers.Where(m => m.MemberProfile.IsActive == true);
if (companies!=null && companies.Length > 0)
filteredMembers = filteredMembers.Where(m => companies.Contains(m.MemberProfile.CompanyName));
if (!string.IsNullOrEmpty(workingWithUId))
filteredMembers = filteredMembers.Where(m =>
m.Memberships.Any(ms => context.ProjectMemberships.Any(mms => mms.MemberUId == workingWithUId && mms.ProjectUId == ms.ProjectUId && mms.IsActive))
&& m.MemberProfile.MemberUId!=workingWithUId);
if (!string.IsNullOrWhiteSpace(filter)) {
string[] tags = filter.Split(new char[] { ' ' }, System.StringSplitOptions.RemoveEmptyEntries);
filteredMembers = filteredMembers.Where(
m => tags.All(t =>
m.MemberProfile.TeamCode.StartsWith(t)
|| m.MemberProfile.FullName.Contains(t)
|| m.MemberProfile.EmailAccount.Contains(t)
|| m.MemberProfile.Skills.Contains(t)));
}
if(availability>0)
filteredMembers = filteredMembers.Where(
m => m.Memberships.Sum(ms => ms.DayAllocation) <= 4 - availability
|| m.Memberships.Sum(ms => ms.DayAllocation) == null);
if (top > 0)
filteredMembers = filteredMembers.Take(top);
return filteredMembers.AsEnumerable().Select(m => m.MemberProfile).ToList<MemberProfile>();
}
}
public ICollection<ScrumFactory.Project> GetAllProjects(string memberUId, System.DateTime startDate, System.DateTime endDate, string filter = null, int top = 0, int skip = 0)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
IQueryable<ScrumFactory.Project> projects = context.Projects.Where(p => p.CreateDate >= startDate && p.CreateDate <= endDate);
projects = RestrictProjectsFromMember(projects, memberUId);
projects = RestrictProjectsByFilter(projects, filter);
projects = projects.OrderByDescending(p => p.ProjectNumber);
if (top > 0) {
projects = projects.Skip(skip).Take(top);
}
// include only the memberUId memberships at the projects
if (!string.IsNullOrEmpty(memberUId)) {
var projectsWithMemberships = projects.Select(p => new { Project = p, Memberships = p.Memberships.Where(m => m.MemberUId == memberUId && m.DayAllocation > 0 && m.IsActive == true) });
return projectsWithMemberships.AsEnumerable().Select(p => p.Project).ToList<Project>();
}
return projects.ToList();
}
}
private void UpdateProposalClauses(ScrumFactoryEntities context, Proposal proposal, Proposal oldProposal)
{
while (oldProposal.Clauses.Count > 0)
context.ProposalClauses.DeleteObject(oldProposal.Clauses.First());
foreach (ProposalClause clause in proposal.Clauses)
context.ProposalClauses.AddObject(clause);
}
public void SaveHourCosts(RoleHourCost[] costs)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
foreach (RoleHourCost cost in costs)
SaveHourCost(cost, context);
context.SaveChanges();
}
}
public Proposal GetProjectProposal(string projectUId, string proposalUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
return context.Proposals.Include("Clauses").Include("Items").Include("FixedCosts").SingleOrDefault(p => p.ProposalUId == proposalUId);
}
}
public RoleHourCost[] GetHourCosts(string projectUId)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
return context.RoleHourCosts.Where(c => c.ProjectUId == projectUId).ToArray();
}
}
private void UpdateProposalItems(ScrumFactoryEntities context, Proposal proposal, Proposal oldProposal)
{
// get new items added at the proposal and the removed ones
ProposalItem[] newItems = new ProposalItem[0];
if (proposal.Items != null)
newItems = proposal.Items.Where(i => !oldProposal.Items.Any(oi => oi.BacklogItemUId == i.BacklogItemUId)).ToArray();
ProposalItem[] deletedItems = new ProposalItem[0];
if (oldProposal.Items != null)
deletedItems = oldProposal.Items.Where(oi => !proposal.Items.Any(i => i.BacklogItemUId == oi.BacklogItemUId)).ToArray();
// add and delete proposal items
foreach (ProposalItem item in newItems)
context.ProposalItems.AddObject(item);
foreach (ProposalItem item in deletedItems)
context.ProposalItems.DeleteObject(item);
}
private void UpdateProposalFixedCosts(ScrumFactoryEntities context, Proposal proposal, Proposal oldProposal)
{
// make sure no proposal has null collections
if (proposal.FixedCosts == null)
proposal.FixedCosts = new List<ProposalFixedCost>();
if (oldProposal.FixedCosts == null)
oldProposal.FixedCosts = new List<ProposalFixedCost>();
ProposalFixedCost[] newCosts = new ProposalFixedCost[0];
newCosts = proposal.FixedCosts.Where(i => !oldProposal.FixedCosts.Any(oi => oi.ProposalFixedCostUId == i.ProposalFixedCostUId)).ToArray();
ProposalFixedCost[] deletedCosts = new ProposalFixedCost[0];
deletedCosts = oldProposal.FixedCosts.Where(oi => !proposal.FixedCosts.Any(i => i.ProposalFixedCostUId == oi.ProposalFixedCostUId)).ToArray();
ProposalFixedCost[] updatedCosts = new ProposalFixedCost[0];
updatedCosts = proposal.FixedCosts.Where(oi => oldProposal.FixedCosts.Any(i => i.ProposalFixedCostUId == oi.ProposalFixedCostUId)).ToArray();
// add/update/delete proposal items
foreach (ProposalFixedCost cost in newCosts)
context.ProposalFixedCosts.AddObject(cost);
foreach (ProposalFixedCost cost in deletedCosts)
context.ProposalFixedCosts.DeleteObject(cost);
foreach (ProposalFixedCost cost in updatedCosts)
context.ApplyCurrentValues<ProposalFixedCost>("ProposalFixedCosts", cost);
}
private void SaveHourCost(RoleHourCost cost, ScrumFactoryEntities context)
{
RoleHourCost oldCost = context.RoleHourCosts.SingleOrDefault(c => c.RoleUId == cost.RoleUId);
if (oldCost == null) {
context.RoleHourCosts.AddObject(cost);
return;
}
context.AttachTo("RoleHourCosts", oldCost);
context.ApplyCurrentValues<RoleHourCost>("RoleHourCosts", cost);
}
public void SaveProposal(Proposal proposal)
{
using (var context = new ScrumFactoryEntities(this.connectionString)) {
Proposal oldProposal = GetProjectProposal(proposal.ProjectUId, proposal.ProposalUId);
using (TransactionScope scope = new TransactionScope()) {
// if its a new proposal
if (oldProposal == null) {
// proposals
context.Proposals.AddObject(proposal);
// items
foreach (ProposalItem pitem in proposal.Items)
context.ProposalItems.AddObject(pitem);
// clauses
if(proposal.Clauses!=null) {
foreach (ProposalClause clause in proposal.Clauses)
context.ProposalClauses.AddObject(clause);
}
}
// if is an old one
else {
context.AttachTo("Proposals", oldProposal);
context.ApplyCurrentValues<Proposal>("Proposals", proposal);
// if is approving a proposal, adds its XAML document
if (proposal.ProposalDocument != null)
context.ProposalDocuments.AddObject(proposal.ProposalDocument);
if (oldProposal.Items == null)
oldProposal.Items = new List<ProposalItem>();
if (proposal.Items == null)
proposal.Items = new List<ProposalItem>();
UpdateProposalItems(context, proposal, oldProposal);
UpdateProposalClauses(context, proposal, oldProposal);
UpdateProposalFixedCosts(context, proposal, oldProposal);
}
context.SaveChanges();
scope.Complete();
}
}
}