def get_photo(self, obj):
return get_photo_url(obj['photo'])
def _get_userstories_owners(project, queryset):
compiler = connection.ops.compiler(queryset.query.compiler)(queryset.query, connection, None)
queryset_where_tuple = queryset.query.where.as_sql(compiler, connection)
where = queryset_where_tuple[0]
where_params = queryset_where_tuple[1]
extra_sql = """
WITH "us_counters" AS(
SELECT DISTINCT "userstories_userstory"."owner_id" "owner_id",
"userstories_userstory"."id" "us_id"
FROM "userstories_userstory"
INNER JOIN "projects_project"
ON ("userstories_userstory"."project_id" = "projects_project"."id")
INNER JOIN "projects_userstorystatus"
ON ("userstories_userstory"."status_id" = "projects_userstorystatus"."id")
LEFT OUTER JOIN "epics_relateduserstory"
ON ("userstories_userstory"."id" = "epics_relateduserstory"."user_story_id")
LEFT OUTER JOIN "userstories_userstory_assigned_users"
ON "userstories_userstory"."id" = "userstories_userstory_assigned_users"."userstory_id"
WHERE {where}
),
"counters" AS (
SELECT "owner_id",
COUNT("owner_id")
FROM "us_counters"
GROUP BY "owner_id"
)
SELECT "projects_membership"."user_id" "user_id",
"users_user"."full_name",
"users_user"."username",
COALESCE("counters".count, 0) "count",
"users_user"."photo" "photo",
"users_user"."email" "email"
FROM "projects_membership"
LEFT OUTER JOIN "counters"
ON ("projects_membership"."user_id" = "counters"."owner_id")
INNER JOIN "users_user"
ON ("projects_membership"."user_id" = "users_user"."id")
WHERE "projects_membership"."project_id" = %s AND "projects_membership"."user_id" IS NOT NULL
-- System users
UNION
SELECT "users_user"."id" "user_id",
"users_user"."full_name" "full_name",
"users_user"."username" "username",
COALESCE("counters"."count", 0) "count",
NULL "photo",
NULL "email"
FROM "users_user"
LEFT OUTER JOIN "counters"
ON ("users_user"."id" = "counters"."owner_id")
WHERE ("users_user"."is_system" IS TRUE)
""".format(where=where)
with closing(connection.cursor()) as cursor:
cursor.execute(extra_sql, where_params + [project.id])
rows = cursor.fetchall()
result = []
for id, full_name, username, count, photo, email in rows:
if count > 0:
result.append({
"id": id,
"full_name": full_name or username or "",
"count": count,
"photo": get_photo_url(photo),
"big_photo": get_big_photo_url(photo),
"gravatar_id": get_gravatar_id(email) if email else None
})
return sorted(result, key=itemgetter("full_name"))
def get_photo(self, obj):
return get_photo_url(obj['photo'])
def _get_userstories_assigned_users(project, queryset):
compiler = connection.ops.compiler(queryset.query.compiler)(queryset.query, connection, None)
queryset_where_tuple = queryset.query.where.as_sql(compiler, connection)
where = queryset_where_tuple[0]
where_params = queryset_where_tuple[1]
extra_sql = """
WITH "us_counters" AS (
SELECT DISTINCT COALESCE("userstories_userstory_assigned_users"."user_id",
"userstories_userstory"."assigned_to_id") as "assigned_user_id",
"userstories_userstory"."id" "us_id"
FROM "userstories_userstory"
LEFT JOIN "userstories_userstory_assigned_users"
ON "userstories_userstory_assigned_users"."userstory_id" = "userstories_userstory"."id"
INNER JOIN "projects_project"
ON ("userstories_userstory"."project_id" = "projects_project"."id")
INNER JOIN "projects_userstorystatus"
ON ("userstories_userstory"."status_id" = "projects_userstorystatus"."id")
LEFT OUTER JOIN "epics_relateduserstory"
ON "userstories_userstory"."id" = "epics_relateduserstory"."user_story_id"
WHERE {where}
),
"counters" AS (
SELECT "assigned_user_id",
COUNT("assigned_user_id")
FROM "us_counters"
GROUP BY "assigned_user_id"
)
SELECT "projects_membership"."user_id" "user_id",
"users_user"."full_name" "full_name",
"users_user"."username" "username",
COALESCE("counters".count, 0) "count",
"users_user"."photo" "photo",
"users_user"."email" "email"
FROM "projects_membership"
LEFT OUTER JOIN "counters"
ON ("projects_membership"."user_id" = "counters"."assigned_user_id")
INNER JOIN "users_user"
ON ("projects_membership"."user_id" = "users_user"."id")
WHERE "projects_membership"."project_id" = %s AND "projects_membership"."user_id" IS NOT NULL
-- unassigned userstories
UNION
SELECT NULL "user_id",
NULL "full_name",
NULL "username",
count(coalesce("assigned_to_id", -1)) "count",
NULL "photo",
NULL "email"
FROM "userstories_userstory"
INNER JOIN "projects_project"
ON ("userstories_userstory"."project_id" = "projects_project"."id")
INNER JOIN "projects_userstorystatus"
ON ("userstories_userstory"."status_id" = "projects_userstorystatus"."id")
LEFT OUTER JOIN "epics_relateduserstory"
ON ("userstories_userstory"."id" = "epics_relateduserstory"."user_story_id")
WHERE {where} AND "userstories_userstory"."id" NOT IN (
SELECT "userstories_userstory_assigned_users"."userstory_id" FROM
"userstories_userstory_assigned_users"
) AND "userstories_userstory"."assigned_to_id" IS NULL
GROUP BY "username";
""".format(where=where)
with closing(connection.cursor()) as cursor:
cursor.execute(extra_sql, where_params + [project.id] + where_params)
rows = cursor.fetchall()
result = []
none_valued_added = False
for id, full_name, username, count, photo, email in rows:
result.append({
"id": id,
"full_name": full_name or username or "",
"count": count,
"photo": get_photo_url(photo),
"big_photo": get_big_photo_url(photo),
"gravatar_id": get_gravatar_id(email) if email else None
})
if id is None:
none_valued_added = True
# If there was no userstory with null assigned_to we manually add it
if not none_valued_added:
result.append({
"id": None,
"full_name": "",
"count": 0,
"photo": None,
"big_photo": None,
"gravatar_id": None
})
return sorted(result, key=itemgetter("full_name"))
def get_photo(self, obj):
return get_photo_url(obj["photo"])
