def get_gravatar_id(self, obj): return get_gravatar_id(obj['email'])
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_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_gravatar_id(self, obj): return get_gravatar_id(obj["email"])