private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin)
{
if (filter.MilestoneStatuses.Count != 0)
{
query.Where("t.status", filter.MilestoneStatuses.First());
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where(Exp.Eq("t.responsible_id", filter.UserId));
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.ParticipantId.HasValue)
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("pt.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("pt.id", "ptr1.task_id"));
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString()));
query.LeftOuterJoin(TasksTable + " as pt", Exp.EqColumns("pt.milestone_id", "t.id") & Exp.EqColumns("pt.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
if (!isAdmin)
{
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = Exp.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false);
var canReadMilestones = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Eq("t.responsible_id", CurrentUserID);
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadMilestones));
}
return query;
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
String alias,
Guid responsibleID,
int categoryID,
bool? isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
var aliasPrefix = !String.IsNullOrEmpty(alias) ? alias + "." : "";
if (responsibleID != Guid.Empty)
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
if (entityID > 0)
switch (entityType)
{
case EntityType.Contact:
var isCompany = true;
using (var db = GetDb())
{
isCompany = db.ExecuteScalar<bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID)));
}
if (isCompany)
return WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy);
else
return WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy);
case EntityType.Person:
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In(aliasPrefix + "contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq(aliasPrefix + "entity_id", entityID) &
Exp.Eq(aliasPrefix + "entity_type", (int)entityType));
break;
}
if (isClosed.HasValue)
sqlQuery.Where(aliasPrefix + "is_closed", isClosed);
if (categoryID > 0)
sqlQuery.Where(Exp.Eq(aliasPrefix + "category_id", categoryID));
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Between(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
else if (fromDate != DateTime.MinValue)
sqlQuery.Where(Exp.Ge(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate)));
else if (toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Le(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
if (0 < from && from < int.MaxValue)
sqlQuery.SetFirstResult(from);
if (0 < count && count < int.MaxValue)
sqlQuery.SetMaxResults(count);
sqlQuery.OrderBy(aliasPrefix + "is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
{
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy(aliasPrefix + "title", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy(aliasPrefix + "deadline", orderBy.IsAsc)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy(aliasPrefix + "category_id", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.ContactManager:
sqlQuery.LeftOuterJoin("core_user u", Exp.EqColumns(aliasPrefix + "responsible_id", "u.id"))
.OrderBy("case when u.lastname is null or u.lastname = '' then 1 else 0 end, u.lastname", orderBy.IsAsc)
.OrderBy("case when u.firstname is null or u.firstname = '' then 1 else 0 end, u.firstname", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Contact:
sqlQuery.LeftOuterJoin("crm_contact c_tbl", Exp.EqColumns(aliasPrefix + "contact_id", "c_tbl.id"))
.OrderBy("case when c_tbl.display_name is null then 1 else 0 end, c_tbl.display_name", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
}
}
else
{
sqlQuery
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
}
return sqlQuery;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.MyProjects || filter.MyMilestones)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("t.project_id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
if (filter.Milestone.HasValue || filter.MyMilestones)
{
query.InnerJoin(MilestonesTable + " pm", Exp.EqColumns("pm.tenant_id", "t.tenant_id") & Exp.EqColumns("pm.project_id", "t.project_id"));
query.Where(Exp.EqColumns("pt.milestone_id", "pm.id"));
if (filter.Milestone.HasValue)
{
query.Where("pm.id", filter.Milestone);
}
else if (filter.MyMilestones)
{
query.Where(Exp.Gt("pm.id", 0));
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.person_id", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.person_id") & Exp.EqColumns("cug.tenant", "t.tenant_id"));
query.Where("cug.groupid", filter.DepartmentId);
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue) &&
!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Between("t.date", filter.FromDate, filter.ToDate));
}
if (filter.PaymentStatuses.Any())
{
query.Where(Exp.In("payment_status", filter.PaymentStatuses));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.note", filter.SearchText, SqlLike.AnyWhere));
}
if (checkAccess)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("p.id", "t.project_id"));
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("p.id", "t.project_id"));
if (!(filter.MyProjects || filter.MyMilestones))
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = !Exp.Eq("ppp.security", null) & Exp.Eq("ppp.removed", false);
var canReadTasks = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks);
var canReadMilestones = Exp.Eq("pt.milestone_id", 0) | !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Exists(new SqlQuery("projects_tasks_responsible ptr")
.Select("ptr.responsible_id")
.Where(Exp.EqColumns("pt.id", "ptr.task_id") &
Exp.EqColumns("ptr.tenant_id", "pt.tenant_id") &
Exp.Eq("ptr.responsible_id", CurrentUserID)));
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadTasks & canReadMilestones));
}
query.GroupBy("t.id");
return query;
}
private void CheckSecurity(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (checkAccess)
{
query.Where(Exp.Eq("p.private", false));
return;
}
if (isAdmin) return;
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = !Exp.Eq("ppp.security", null) & Exp.Eq("ppp.removed", false);
var canReadMilestones = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Eq("t.responsible_id", CurrentUserID);
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadMilestones));
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.MilestoneStatuses.Count != 0)
{
query.Where("t.status", filter.MilestoneStatuses.First());
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where(Exp.Eq("t.responsible_id", filter.UserId));
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (filter.ParticipantId.HasValue)
{
var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("pt.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("pt.id", "ptr1.task_id"));
existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString()));
query.LeftOuterJoin(TasksTable + " as pt", Exp.EqColumns("pt.milestone_id", "t.id") & Exp.EqColumns("pt.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsModule, FullTextSearch.ProjectsCommentsModule))
{
var mIds = FullTextSearch.Search(FullTextSearch.ProjectsMilestonesModule.Match(filter.SearchText));
query.Where(Exp.In("t.id", mIds));
}
else
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
}
CheckSecurity(query, filter, isAdmin, checkAccess);
return query;
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin)
{
query = CreateQueryFilterBase(query, filter, isAdmin);
if (filter.ParticipantId.HasValue && filter.ParticipantId == Guid.Empty)
{
query.Where(Exp.Eq("ptr.task_id", null));
}
if (filter.Milestone.HasValue)
{
query.Where("t.milestone_id", filter.Milestone);
}
else if (filter.MyMilestones)
{
if (!filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
query.Where(Exp.Gt("m.id", 0));
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Ge(GetSortFilter("deadline", true), TenantUtil.DateTimeFromUtc(filter.FromDate)));
}
if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Le(GetSortFilter("deadline", true), TenantUtil.DateTimeFromUtc(filter.ToDate)));
}
if (!isAdmin)
{
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = Exp.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false);
var canReadTasks = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks);
var canReadMilestones = Exp.Eq("t.milestone_id", 0) | !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var exists = new SqlQuery("projects_tasks_responsible ptr1").Select("ptr1.responsible_id").Where(Exp.EqColumns("t.id", "ptr1.task_id") & Exp.EqColumns("ptr1.tenant_id", "t.tenant_id") & Exp.Eq("ptr1.responsible_id", CurrentUserID));
var responsible = Exp.Exists(exists);
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadTasks & canReadMilestones));
}
return query;
}
private SqlQuery CreateQueryFilterCount(SqlQuery query, TaskFilter filter, bool isAdmin)
{
query = CreateQueryFilterBase(query, filter, isAdmin);
if (filter.Milestone.HasValue)
{
query.Where("t.milestone_id", filter.Milestone);
}
else if (filter.MyMilestones)
{
if (!filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("t.milestone_id", "m.id") & Exp.EqColumns("m.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existsMilestone));
}
if (filter.ParticipantId.HasValue && filter.ParticipantId == Guid.Empty)
{
var notExists = new SqlQuery(TasksResponsibleTable + " ptr").Select("ptr.responsible_id").Where(Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.tenant_id", Tenant));
query.Where(!Exp.Exists(notExists));
}
var hasFromDate = !filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue);
var hasToDate = !filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue);
if (hasFromDate && hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("m.id", "t.milestone_id") & Exp.EqColumns("m.tenant_id", "t.tenant_id") & Exp.Between("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)));
var expExists = Exp.Exists(existsMilestone) & Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"));
query.Where(Exp.Between("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)) | expExists);
}
else if (hasFromDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Ge("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)))
.Where(Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss")));
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate))
| Exp.Exists(existsMilestone));
}
else if (hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Le("m.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)))
.Where(!Exp.Eq("m.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss")))
.Where("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"));
query.Where(!Exp.Eq("t.deadline", DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss"))
& Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate))
| Exp.Exists(existsMilestone));
}
if (!isAdmin)
{
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = Exp.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false);
var canReadTasks = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks);
var canReadMilestones = Exp.Eq("t.milestone_id", 0) | !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Exists(new SqlQuery("projects_tasks_responsible ptr")
.Select("ptr.responsible_id")
.Where(Exp.EqColumns("t.id", "ptr.task_id") &
Exp.EqColumns("ptr.tenant_id", "t.tenant_id") &
Exp.Eq("ptr.responsible_id", CurrentUserID)));
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadTasks & canReadMilestones));
}
return query;
}