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 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 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 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));
}
public List <Tuple <Guid, int, int> > GetByFilterCountForReport(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var query = new SqlQuery(MilestonesTable + " t")
.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.Select("t.create_by", "t.project_id")
.Where("t.tenant_id", Tenant)
.Where(Exp.Between("t.create_on", filter.GetFromDate(), filter.GetToDate()));
if (filter.HasUserId)
{
query.Where(Exp.In("t.create_by", filter.GetUserIds()));
filter.UserId = Guid.Empty;
filter.DepartmentId = Guid.Empty;
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
var queryCount = new SqlQuery()
.SelectCount()
.Select("t1.create_by", "t1.project_id")
.GroupBy("create_by", "project_id")
.From(query, "t1");
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 override IEnumerable <object[]> BuildReport(TaskFilter filter)
{
filter.FromDate = filter.GetFromDate(true);
filter.ToDate = filter.GetToDate(true);
var taskTime = Global.EngineFactory.GetTimeTrackingEngine().GetByFilter(filter).Select(r => new object[] { r.Person, r.Task.Project.ID, r.Task.Project.Title, r.Task.ID, r.Task.Title, r.Hours, 0, r.PaymentStatus });
if (filter.ViewType == 0)
{
taskTime = taskTime.GroupBy(r => (Guid)r[0], (a, b) =>
{
var enumerable = b as IList <object[]> ?? b.ToList();
var data = (object[])enumerable.First().Clone();
data[5] = enumerable.Sum(c => (float)c[5]);
data[6] = enumerable.Where(r => (PaymentStatus)r[7] == PaymentStatus.Billed).Sum(c => (float)c[5]);
return(data);
});
}
if (filter.ViewType == 1)
{
taskTime = taskTime.Select(r =>
{
if ((PaymentStatus)r[7] == PaymentStatus.Billed)
{
r[6] = r[5];
}
return(r);
});
}
taskTime = AddUserInfo(taskTime, 0);
taskTime = taskTime.OrderBy(r => CoreContext.UserManager.GetUsers((Guid)r[0]), UserInfoComparer.Default);
return(taskTime);
}
public Dictionary <Guid, int> GetByFilterCountForReport(TaskFilter filter, bool isAdmin, bool checkAccess)
{
using (var db = new DbManager(DatabaseId))
{
var query = new SqlQuery(MessagesTable + " t")
.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.Select("t.create_by")
.Where("t.tenant_id", Tenant)
.Where(Exp.Between("t.create_on", filter.GetFromDate(), filter.GetToDate()));
if (filter.HasUserId)
{
query.Where(Exp.In("t.create_by", filter.GetUserIds()));
filter.UserId = Guid.Empty;
filter.DepartmentId = Guid.Empty;
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
var queryCount = new SqlQuery()
.SelectCount()
.Select("t1.create_by")
.GroupBy(2)
.From(query, "t1");
return(db.ExecuteList(queryCount).ToDictionary(a => Guid.Parse((string)a[1]), b => Convert.ToInt32(b[0])));
}
}
public List <Tuple <Guid, int> > GetByFilterAverageTime(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var query = new SqlQuery(ProjectsTable + " p")
.Select("ROUND(AVG(TIME_TO_SEC(TIMEDIFF(p.status_changed, p.create_on))/60/60))", "pp.participant_id")
.InnerJoin(ParticipantTable + " pp", Exp.EqColumns("p.tenant_id", "pp.tenant") & Exp.EqColumns("p.id", "pp.project_id"))
.Where("p.tenant_id", Tenant)
.Where(Exp.Eq("p.status", 1));
if (filter.GetFromDate() != DateTime.MinValue && filter.GetToDate() != DateTime.MaxValue)
{
query.Where(Exp.Between("p.create_on", filter.GetFromDate(), filter.GetToDate()));
query.Where(Exp.Between("p.status_changed", filter.GetFromDate(), filter.GetToDate()));
}
query.GroupBy("pp.participant_id");
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
return(Db.ExecuteList(query).ConvertAll(a => new Tuple <Guid, int>(Guid.Parse((string)a[1]), Convert.ToInt32(a[0]))));
}
public IList <object[]> BuildUsersActivityReport(TaskFilter filter)
{
/* query:
* select u, sum(case t when 't' then c else 0 end), sum(case t when 'm' then c else 0 end), sum(case t when 'd' then c else 0 end)
* from (
* select create_by u, 't' t, count(*) c from projects_tasks where tenant_id = 0 group by 1, 2
* union all
* select create_by u, 'm' t, count(*) c from projects_milestones where tenant_id = 0 group by 1, 2
* union all
* select create_by u, 'd' t, count(*) c from projects_messages where tenant_id = 0 group by 1, 2) s
* group by 1
*/
var where = Exp.Eq("tenant_id", Tenant) & Exp.Between("create_on", filter.GetFromDate(true), filter.GetToDate(true));
if (filter.HasUserId)
{
where &= Exp.In("create_by", filter.GetUserIds());
}
var subq = new SqlQuery(TasksTable).Select("create_by u").Select("'t'").Select("count(*) c").Where(where).GroupBy(1, 2)
.UnionAll(new SqlQuery(MilestonesTable).Select("create_by u").Select("'m'").Select("count(*) c").Where(where).GroupBy(1, 2))
.UnionAll(new SqlQuery(MessagesTable).Select("create_by u").Select("'d'").Select("count(*) c").Where(where).GroupBy(1, 2));
var q = new SqlQuery()
.Select("u")
.SelectSum("case t when 't' then c else 0 end")
.SelectSum("case t when 'm' then c else 0 end")
.SelectSum("case t when 'd' then c else 0 end")
.From(subq, "s")
.GroupBy(1);
using (var db = new DbManager(DatabaseId))
{
return(db.ExecuteList(q)
.Select(r => new { UserId = new Guid((string)r[0]), Tasks = Convert.ToInt32(r[1]), Milestones = Convert.ToInt32(r[2]), Messages = Convert.ToInt32(r[3]) })
.Select(r => new object[] { r.UserId, r.Tasks, r.Milestones, r.Messages, 0, r.Tasks + r.Milestones + r.Messages })
.ToList());
}
}
public override IEnumerable <object[]> BuildDocbuilderReport(TaskFilter filter)
{
filter.FromDate = filter.GetFromDate(true);
filter.ToDate = filter.GetToDate(true);
using (var scope = DIHelper.Resolve())
{
var factory = scope.Resolve <EngineFactory>();
IEnumerable <object[]> taskTime = new List <object[]>();
switch (filter.ViewType)
{
case 0:
taskTime = factory.TimeTrackingEngine.GetByFilter(filter)
.Select(r =>
new object[]
{
CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false),
r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : ""
});
taskTime = taskTime.GroupBy(r => (string)r[0], (a, b) =>
{
var enumerable = b as IList <object[]> ?? b.ToList();
var data = (object[])enumerable.First().Clone();
data[1] = enumerable.Sum(c => (float)c[1]);
data[2] = enumerable.Where(r => (PaymentStatus)r[3] == PaymentStatus.Billed).Sum(c => (float)c[1]);
return(data);
});
return(taskTime.OrderBy(r => (string)r[0]));
case 1:
taskTime = factory.TimeTrackingEngine.GetByFilter(filter)
.Select(r =>
new object[]
{
CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false),
r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : "",
r.Task.Project, r.Task.Title
});
taskTime = taskTime.Select(r =>
{
if ((PaymentStatus)r[3] == PaymentStatus.Billed)
{
r[2] = r[1];
}
return(r);
});
var users = taskTime.GroupBy(x => x[0]).Select(x => new object[] { x.Key, x.ToList()
.Select(r => new object[] { r[1], r[2], r[3], r[4], r[5], r[6] }) });
var result = new List <object[]>();
foreach (var user in users) // user = [string, []]
{
var userTasks = (IEnumerable <object[]>)user[1];
var groupedUserTasks = userTasks.GroupBy(x => x[4]).Select(r => new object[] {
new object[] { ((Project)r.Key).Title, ProjectsCommonResource.Status + ": " + LocalizedEnumConverter.ConvertToString(((Project)r.Key).Status),
ProjectResource.ProjectLeader + ": " + CoreContext.UserManager.GetUsers(((Project)r.Key).Responsible).DisplayUserName(false),
TaskResource.CreatingDate + ": " + ((Project)r.Key).CreateOn.ToString("d"),
((Project)r.Key).Description != "" ? ProjectsCommonResource.Description + ": " + ((Project)r.Key).Description : "" }, r.ToList().Select(y => new object[] { y[0], y[1], y[2], y[3], y[5] })
});
result.Add(new object[] { user[0], groupedUserTasks });
}
return(result);
case 2:
taskTime = factory.TimeTrackingEngine.GetByFilter(filter)
.Select(r =>
new object[]
{
CoreContext.UserManager.GetUsers(r.Person).DisplayUserName(false),
r.Hours, 0, r.PaymentStatus, r.PaymentStatus == PaymentStatus.NotChargeable ? "+" : "",
r.Task.Project.Title
});
return(taskTime.GroupBy(x => (string)x[5]).Select(x => new object[] { x.Key, x.GroupBy(r => (string)r[0], (a, b) =>
{
var enumerable = b as IList <object[]> ?? b.ToList();
var data = (object[])enumerable.First().Clone();
data[1] = enumerable.Sum(c => (float)c[1]);
data[2] = enumerable.Where(r => (PaymentStatus)r[3] == PaymentStatus.Billed).Sum(c => (float)c[1]);
return data;
}) }).OrderBy(x => (string)x[0]));
default:
throw new Exception(ProjectsCommonResource.NoData);
}
}
}
