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[]> 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;
}