public List <Tuple <Guid, int, int> > GetByFilterAverageTime(TaskFilter filter, bool isAdmin, bool checkAccess)
{
filter = (TaskFilter)filter.Clone();
var query = new SqlQuery(TasksTable + " t")
.Select("ROUND(AVG(TIME_TO_SEC(TIMEDIFF(t.status_changed, t.create_on))/60/60))", "tr.responsible_id, t.project_id")
.InnerJoin(TasksResponsibleTable + " tr", Exp.EqColumns("t.tenant_id", "tr.tenant_id") & Exp.EqColumns("t.id", "tr.task_id"))
.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.Where("t.tenant_id", Tenant)
.Where(Exp.Eq("t.status", 2))
.Where(Exp.Between("t.status_changed", filter.GetFromDate(), filter.GetToDate()));
if (filter.GetFromDate() != DateTime.MinValue && filter.GetToDate() != DateTime.MaxValue)
{
query.Where((Exp.Between("t.create_on", filter.GetFromDate(), filter.GetToDate()) & !Exp.Eq("t.start_date", DateTime.MinValue)) | Exp.Between("t.start_date", filter.GetFromDate(), filter.GetToDate()));
}
if (filter.HasUserId)
{
query.Where(Exp.In("tr.responsible_id", filter.GetUserIds()));
filter.UserId = Guid.Empty;
filter.DepartmentId = Guid.Empty;
}
query = CreateQueryFilterCount(query, filter, isAdmin, checkAccess);
query.GroupBy("tr.responsible_id", "project_id");
return(Db.ExecuteList(query).ConvertAll(a => new Tuple <Guid, int, int>(Guid.Parse((string)a[1]), Convert.ToInt32(a[2]), Convert.ToInt32(a[0]))));
}
public List <Tuple <Guid, int, int> > GetByFilterCountForReport(TaskFilter filter, bool isAdmin, bool checkAccess)
{
filter = (TaskFilter)filter.Clone();
var query = new SqlQuery(TasksTable + " t")
.InnerJoin(TasksResponsibleTable + " tr", Exp.EqColumns("t.tenant_id", "tr.tenant_id") & Exp.EqColumns("t.id", "tr.task_id"))
.Select("tr.responsible_id", "t.project_id")
.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.Where("t.tenant_id", Tenant);
if (filter.GetFromDate() != DateTime.MinValue && filter.GetToDate() != DateTime.MaxValue)
{
query.Where((Exp.Between("t.create_on", filter.GetFromDate(), filter.GetToDate()) & !Exp.Eq("t.start_date", DateTime.MinValue)) | Exp.Between("t.start_date", filter.GetFromDate(), filter.GetToDate()));
}
if (filter.HasUserId)
{
query.Where(Exp.In("tr.responsible_id", filter.GetUserIds()));
filter.UserId = Guid.Empty;
filter.DepartmentId = Guid.Empty;
}
query = CreateQueryFilterCount(query, filter, isAdmin, checkAccess);
var queryCount = new SqlQuery()
.SelectCount()
.Select("t1.responsible_id", "t1.project_id")
.From(query, "t1")
.GroupBy("responsible_id", "project_id");
return(Db.ExecuteList(queryCount).ConvertAll(b => new Tuple <Guid, int, int>(Guid.Parse((string)b[1]), Convert.ToInt32(b[2]), Convert.ToInt32(b[0]))));
}
public int GetByFilterCountForReport(TaskFilter filter, bool isAdmin, bool checkAccess)
{
filter = (TaskFilter)filter.Clone();
var query = new SqlQuery(ProjectsTable + " p")
.Select("p.id")
.Where("p.tenant_id", Tenant);
if (filter.ProjectStatuses.Contains(ProjectStatus.Closed) || filter.ProjectStatuses.Contains(ProjectStatus.Paused))
{
query.Where(Exp.Between("p.status_changed", filter.GetFromDate(), filter.GetToDate()) & Exp.Eq("p.status", filter.ProjectStatuses[0]));
filter.ProjectStatuses.Clear();
}
if (filter.GetFromDate() != DateTime.MinValue && filter.GetToDate() != DateTime.MaxValue)
{
query.Where(Exp.Between("p.create_on", filter.GetFromDate(), filter.GetToDate()));
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
query.GroupBy("p.id");
var queryCount = new SqlQuery().SelectCount().From(query, "t1");
return(Db.ExecuteScalar <int>(queryCount));
}
public object[] GetByFilterCountForReport(TaskFilter filter)
{
var cloneFilter = (TaskFilter)filter.Clone();
var open = 0;
var paused = 0;
var closed = 0;
if (!filter.ProjectStatuses.Any() || filter.ProjectStatuses.Contains(ProjectStatus.Open))
{
cloneFilter.ProjectStatuses = new List <ProjectStatus> {
ProjectStatus.Open
};
open = DaoFactory.ProjectDao.GetByFilterCountForReport(cloneFilter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled);
}
if (!filter.ProjectStatuses.Any() || filter.ProjectStatuses.Contains(ProjectStatus.Paused))
{
cloneFilter.ProjectStatuses = new List <ProjectStatus> {
ProjectStatus.Paused
};
paused = DaoFactory.ProjectDao.GetByFilterCountForReport(cloneFilter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled);
}
if (!filter.ProjectStatuses.Any() || filter.ProjectStatuses.Contains(ProjectStatus.Closed))
{
cloneFilter.ProjectStatuses = new List <ProjectStatus> {
ProjectStatus.Closed
};
closed = DaoFactory.ProjectDao.GetByFilterCountForReport(cloneFilter, ProjectSecurity.CurrentUserAdministrator, ProjectSecurity.IsPrivateDisabled);
}
return(new object[3] {
open, paused, closed
});
}
public List <Project> GetByFilterForReport(TaskFilter filter, bool isAdmin, bool checkAccess)
{
filter = (TaskFilter)filter.Clone();
Exp exp = null;
if (filter.ProjectStatuses.Contains(ProjectStatus.Closed))
{
filter.ProjectStatuses.Remove(ProjectStatus.Closed);
exp = Exp.Between("p.status_changed", filter.GetFromDate(), filter.GetToDate()) & Exp.Eq("p.status", 1) | Exp.In("p.status", filter.ProjectStatuses);
filter.ProjectStatuses.Clear();
}
;
var query = CreateQueryForFilter(filter, isAdmin, checkAccess);
query.Where(Exp.Between("p.create_on", filter.GetFromDate(), filter.GetToDate()));
query.Where(Exp.Eq("p.status", 0) | (Exp.Between("p.status_changed", filter.GetFromDate(), filter.GetToDate())));
if (exp != null)
{
query.Where(exp);
}
return(Db.ExecuteList(query).ConvertAll(ToProjectFull));
}
