public TeamDTO GetTeamByName(string name)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((TeamDTO)P1PObjectMapper.Convert(ctx.Teams.Single(t => t.Name.Equals(name)), typeof(TeamDTO)));
}
}
public List <ArticleDTO> Search(int projectId, string username, bool includePublished, int teamId)
{
ObjectResult <Article> articles;
List <ArticleDTO> dtoArticles = new List <ArticleDTO>();
using (P1PContext ctx = new P1PContext())
{
articles = ctx.SearchArticles(projectId, username, includePublished, teamId);
foreach (Article article in articles)
{
dtoArticles.Add(new ArticleDTO()
{
Id = article.Id,
Title = article.Title,
Content = article.Content,
CreatedBy = article.CreatedBy,
CreatedDate = article.CreatedDate,
ArticleStatus = new KeyValueDTO()
{
Id = article.ArticleStatus.Id, Name = article.ArticleStatus.Name
},
Project = new ProjectDTO()
{
Id = article.ProjectArticleXREFs.First().Project.Id, Name = article.ProjectArticleXREFs.First().Project.Name
}
});
}
}
return(dtoArticles);
}
public TeamDTO Get(int id)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((TeamDTO)P1PObjectMapper.Convert(ctx.Teams.Single(t => t.Id == id), typeof(TeamDTO)));
}
}
public List <AggregateDTO> AggregateSearch(int projectId, int status, int linkStrategy, bool onlyMine, string userName, Nullable <DateTime> startDate, Nullable <DateTime> endDate, bool includeNotInUse, int teamId, bool isActive)
{
List <AggregateDTO> aggregates = new List <AggregateDTO>();
ObjectResult <p1p.Data.sp_Links_Aggregates_Result2> aggregateResults;
using (P1PContext ctx = new P1PContext())
{
aggregateResults = ctx.sp_Links_Aggregates(projectId, status, linkStrategy, onlyMine, userName, startDate, endDate, includeNotInUse, teamId, isActive);
foreach (sp_Links_Aggregates_Result2 a in aggregateResults)
{
if (a.ProjectAggregate != null && a.ProjectAggregate > 0)
{
aggregates.Add(new AggregateDTO()
{
Name = a.Name,
Aggregate = (double)a.ProjectAggregate
});
}
}
}
return(aggregates);
}
public bool AddRecentTimeEntry(KronosTimeEntry kEntry)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
try
{
p1p.Data.TimeEntry thisEntry = ConvertToTimeEntry(kEntry);
if (thisEntry.StartTime < DateTime.Now.AddMonths(-1))
{
return(true);
}
List <p1p.Data.TimeEntry> dupCheck = ctx.TimeEntries.Where(t => t.StartTime == thisEntry.StartTime && t.EndTime == thisEntry.EndTime && t.UserId.Equals(thisEntry.UserId)).ToList <p1p.Data.TimeEntry>();
if (dupCheck.Count > 0)
{
return(true);
}
else
{
thisEntry.InsertDate = DateTime.Now;
ctx.TimeEntries.Add(thisEntry);
ctx.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
log.Error("Failed to add Time Entry: " + ex.Message + ex.Message + kEntry.toJSON());
return(false);
}
}
}
public void DeleteLink(int linkId)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
List <p1p.Data.Link> toDel = ctx.Links.Where(wl => wl.Id == linkId).ToList <p1p.Data.Link>();
for (int i = 0; i < toDel.Count; i++)
{
try
{
ctx.Links.Remove(toDel[i]);
ctx.SaveChanges();
}
catch (System.Data.Entity.Infrastructure.DbUpdateException ex)
{
if ("The DELETE statement conflicted with the REFERENCE constraint \"FK_dbo.Outreaches_dbo.Links_LinkId\". The conflict occurred in database \"p1pLinkTracker\", table \"dbo.Outreaches\", column 'LinkId'.\r\nThe statement has been terminated.".Equals(ex.InnerException.InnerException.Message))
{
throw new Exception("You can't delete this link because it has outreaches assigned to it.");
}
else
{
throw new Exception("Could not delete Link.");
}
}
}
}
}
public LinkDTO RequestActive(LinkDTO link, string userName)
{
p1p.Data.Link mdlLink = (p1p.Data.Link)P1PObjectMapper.Convert(link, typeof(p1p.Data.Link));
p1p.Data.Link match;
mdlLink.LastModifiedBy = userName;
mdlLink.DateLastModified = DateTime.Now;
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
match = ctx.Links.Single(l => link.Id == l.Id);
if (link.Article != null && link.Article.Id > 0)
{
p1p.Data.ProjectArticleXREF pax = ctx.ProjectArticleXREFs.Single(x => x.ArticleId == link.Article.Id && x.ProjectId == link.ProjectId);
mdlLink.ProjectArticleXREFId = pax.Id;
}
mdlLink.LinkStatusId = 14;
mdlLink.AcquiredBy = userName;
mdlLink.DatePublished = DateTime.Now;
ctx.Entry(match).CurrentValues.SetValues(mdlLink);
ctx.SaveChanges();
return((LinkDTO)P1PObjectMapper.Convert(match, typeof(LinkDTO)));
}
}
public LinkDTO UpdateLink(LinkDTO link, string userName)
{
p1p.Data.Link mdlLink = (p1p.Data.Link)P1PObjectMapper.Convert(link, typeof(p1p.Data.Link));
p1p.Data.Link match;
mdlLink.LastModifiedBy = userName;
mdlLink.DateLastModified = DateTime.Now;
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
match = ctx.Links.Single(l => link.Id == l.Id);
if (match.LinkStatusId != 10 && link.LinkStatus.Id == 10)
{
throw new Exception("You cannot set a link active through the 'UpdateLink' method.");
}
if (match.LinkStatusId != 14 && link.LinkStatus.Id == 14)
{
throw new Exception("You cannot set a link to 'Request Active' through the 'UpdateLink' method.");
}
if (link.Article != null && link.Article.Id > 0)
{
p1p.Data.ProjectArticleXREF pax = ctx.ProjectArticleXREFs.Single(x => x.ArticleId == link.Article.Id && x.ProjectId == link.ProjectId);
mdlLink.ProjectArticleXREFId = pax.Id;
}
ctx.Entry(match).CurrentValues.SetValues(mdlLink);
ctx.SaveChanges();
return((LinkDTO)P1PObjectMapper.Convert(match, typeof(LinkDTO)));
}
}
public LinkDTO Get(int linkId)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((LinkDTO)P1PObjectMapper.Convert(ctx.Links.Single(l => l.Id == linkId), typeof(LinkDTO)));
}
}
public static string GetUserIdByName(string firstName, string lastName)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return(ctx.Employees.Single(e => e.FirstName.Equals(firstName, StringComparison.OrdinalIgnoreCase) && e.LastName.Equals(lastName, StringComparison.OrdinalIgnoreCase)).Username);
}
}
public CustomerDTO Get(int id)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((CustomerDTO)P1PObjectMapper.Convert(ctx.Customers.Single(a => a.Id == id), typeof(CustomerDTO)));
}
}
public EmployeeDTO GetByUser(string User)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((EmployeeDTO)P1PObjectMapper.Convert(ctx.Employees.Single(e => e.Username.Equals(User)), typeof(EmployeeDTO)));
}
}
public ProjectDTO GetProjectByName(string name)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((ProjectDTO)P1PObjectMapper.Convert(ctx.Projects.Single(p => p.Name.Equals(name)), typeof(ProjectDTO)));
}
}
public void RemoveProject(int projectId)
{
p1p.Data.Project proj;
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
proj = ctx.Projects.Single(p => p.Id == projectId);
try
{
List <OrderTeamXREF> matchingxref = ctx.OrderTeamXREFs.Where(x => x.OrderId == projectId).ToList <OrderTeamXREF>();
if (matchingxref.Count > 0)
{
OrderTeamXREF xref = matchingxref[0];
ctx.OrderTeamXREFs.Remove(xref);
ctx.SaveChanges();
}
ctx.Projects.Remove(proj);
ctx.SaveChanges();
}
catch (System.Data.Entity.Infrastructure.DbUpdateException ex)
{
if ("The DELETE statement conflicted with the REFERENCE constraint \"FK_dbo.LandingPages_dbo.Projects_ProjectId\". The conflict occurred in database \"p1pLinkTracker\", table \"dbo.LandingPages\", column 'ProjectId'.\r\nThe statement has been terminated.".Equals(ex.InnerException.InnerException.Message))
{
throw new Exception("You can't delete a project that has targets assigned to it.");
}
else
{
throw new Exception("Could not delete Project.");
}
}
}
}
public void AddOutreach(OutreachDTO entry, string user)
{
p1p.Data.Link link;
p1p.Data.Link linkMatch;
entry.AddedBy = user;
entry.InsertDate = DateTime.Now;
p1p.Data.Outreach mdlOutreach = (p1p.Data.Outreach)P1PObjectMapper.Convert(entry, typeof(p1p.Data.Outreach));
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
if (entry.OutreachAction != null && ctx.OutreachActions.FirstOrDefault(oa => oa.Id == entry.OutreachAction.Id) != null)
{
linkMatch = ctx.Links.Single(l => entry.LinkId == l.Id);
link = linkMatch;
link.LastModifiedBy = user;
link.DateLastModified = DateTime.Now;
ctx.Outreaches.Add(mdlOutreach);
ctx.Entry(linkMatch).CurrentValues.SetValues(link);
ctx.SaveChanges();
}
else
{
throw new Exception("You must select an outreach action to save this outreach.");
}
}
}
public string GetUsernameByEmail(string email)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return(ctx.Employees.Single(e => e.Email.Equals(email)).Username);
}
}
public EmployeeDTO Get(int id)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((EmployeeDTO)P1PObjectMapper.Convert(ctx.Employees.Single(a => a.Id == id), typeof(EmployeeDTO)));
}
}
public CustomerDTO GetCustomerByUser(string user)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((CustomerDTO)P1PObjectMapper.Convert(ctx.Customers.Single(c => c.Username.Equals(user)), typeof(CustomerDTO)));
}
}
public ProjectDTO GetProject(int id)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((ProjectDTO)P1PObjectMapper.Convert(ctx.Projects.Single(p => p.Id == id), typeof(ProjectDTO)));
}
}
public List <ProjectDTO> GetAllActive()
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from p in ctx.Projects where p.IsActive orderby p.Name select p)
.AsEnumerable()
.Select(p => (ProjectDTO)P1PObjectMapper.Convert(p, typeof(ProjectDTO))).ToList <ProjectDTO>());
}
}
public List <TeamDTO> GetAll()
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from t in ctx.Teams select t)
.AsEnumerable()
.Select(t => (TeamDTO)P1PObjectMapper.Convert(t, typeof(TeamDTO))).ToList <TeamDTO>());
}
}
public List <CustomerDTO> GetAll()
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from c in ctx.Customers orderby c.BusinessName select c)
.AsEnumerable()
.Select(c => (CustomerDTO)P1PObjectMapper.Convert(c, typeof(CustomerDTO))).ToList <CustomerDTO>());
}
}
public List <EmployeeDTO> GetAll()
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from e in ctx.Employees select e)
.AsEnumerable()
.Select(e => (EmployeeDTO)P1PObjectMapper.Convert(e, typeof(EmployeeDTO))).ToList <EmployeeDTO>());
}
}
public List <KeyValueDTO> GetArticleStatuses()
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from s in ctx.ArticleStatuses select s)
.AsEnumerable()
.Select(s => (KeyValueDTO)P1PObjectMapper.Convert(s, typeof(KeyValueDTO))).ToList <KeyValueDTO>());
}
}
public List <OutreachDTO> GetAllOutreachForLink(int linkId)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from o in ctx.Outreaches where o.LinkId == linkId select o)
.AsEnumerable()
.Select(o => (OutreachDTO)P1PObjectMapper.Convert(o, typeof(OutreachDTO))).ToList <OutreachDTO>());
}
}
public List <ProjectDTO> GetProjectsForTeam(int teamId)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from o in ctx.OrderTeamXREFs where o.TeamId == teamId orderby o.Project.Name select o.Project)
.AsEnumerable()
.Select(p => (ProjectDTO)P1PObjectMapper.Convert(p, typeof(ProjectDTO))).ToList <ProjectDTO>());
}
}
public List <ProjectDTO> GetProjectsForAccount(int accountId)
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from p in ctx.Projects where p.CustomerId == accountId orderby p.Name select p)
.AsEnumerable()
.Select(p => (ProjectDTO)P1PObjectMapper.Convert(p, typeof(ProjectDTO))).ToList <ProjectDTO>());
}
}
public void UpdateProject(ProjectDTO item)
{
p1p.Data.Project mdlProject = (p1p.Data.Project)P1PObjectMapper.Convert(item, typeof(p1p.Data.Project));
p1p.Data.Project match;
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
match = ctx.Projects.Single(p => item.Id == p.Id);
if (match.IsActive && !mdlProject.IsActive)
{
List <Link> killLinks = ctx.Links.Where(l => l.ProjectId == item.Id &&
l.LinkStatusId != 5 &&
l.LinkStatusId != 8 &&
l.LinkStatusId != 9 &&
l.LinkStatusId != 10 &&
l.LinkStatusId != 11 &&
l.LinkStatusId != 12).ToList <Link>();
foreach (Link link in killLinks)
{
Link deadLink = link;
deadLink.LinkStatusId = 8;
ctx.Entry(link).CurrentValues.SetValues(deadLink);
}
ctx.SaveChanges();
}
ctx.Entry(match).CurrentValues.SetValues(mdlProject);
ctx.SaveChanges();
List <OrderTeamXREF> matchingxref = ctx.OrderTeamXREFs.Where(x => x.OrderId == item.Id).ToList <OrderTeamXREF>();
if (matchingxref.Count > 0)
{
DateTime InsertDate = DateTime.Now;
OrderTeamXREF xref = matchingxref[0];
ctx.OrderTeamXREFs.Remove(xref);
OrderTeamXREF newxref = new OrderTeamXREF()
{
TeamId = item.Team.Id, OrderId = item.Id, InsertDate = InsertDate
};
ctx.OrderTeamXREFs.Add(newxref);
ctx.SaveChanges();
}
else
{
if (item.Team.Id > 0)
{
DateTime InsertDate = DateTime.Now;
OrderTeamXREF newxref = new OrderTeamXREF()
{
TeamId = item.Team.Id, OrderId = item.Id, InsertDate = InsertDate
};
ctx.OrderTeamXREFs.Add(newxref);
ctx.SaveChanges();
}
}
}
}
public List <KeyValueDTO> GetOutreachTypes()
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from ot in ctx.OutreachTypes
select ot)
.AsEnumerable()
.Select(ot => (KeyValueDTO)P1PObjectMapper.Convert(ot, typeof(KeyValueDTO))).ToList <KeyValueDTO>());
}
}
public List <KeyValueDTO> GetSiteCategories()
{
using (p1p.Data.P1PContext ctx = new p1p.Data.P1PContext())
{
return((from sc in ctx.SiteCategories
select sc)
.AsEnumerable()
.Select(sc => (KeyValueDTO)P1PObjectMapper.Convert(sc, typeof(KeyValueDTO))).ToList <KeyValueDTO>());
}
}
