def get_episodes_for_project(project_id, only_assigned=False):
"""
Retrieve all episodes related to given project.
"""
if only_assigned:
Sequence = aliased(Entity, name="sequence")
Shot = aliased(Entity, name="shot")
Asset = aliased(Entity, name="asset")
query = (
Entity.query.join(Sequence, Entity.id == Sequence.parent_id)
.join(Shot, Sequence.id == Shot.parent_id)
.join(Task, Shot.id == Task.entity_id)
.filter(Entity.project_id == project_id)
.filter(user_service.build_assignee_filter())
)
shot_episodes = fields.serialize_models(query.all())
shot_episode_ids = {episode["id"]: True for episode in shot_episodes}
query = (
Entity.query.join(Asset, Entity.id == Asset.source_id)
.join(Task, Asset.id == Task.entity_id)
.filter(Entity.project_id == project_id)
.filter(user_service.build_assignee_filter())
)
asset_episodes = fields.serialize_models(query.all())
result = shot_episodes
for episode in asset_episodes:
if episode["id"] not in shot_episode_ids:
result.append(episode)
return result
else:
return entities_service.get_entities_for_project(
project_id, get_episode_type()["id"], "Episode"
)
def _get_episode_counts(project_id, only_assigned=False):
Sequence = aliased(Entity, name="sequence")
Episode = aliased(Entity, name="episode")
query = (Task.query.with_entities(
Task.project_id,
Episode.id,
Task.task_type_id,
Task.task_status_id,
TaskStatus.short_name,
TaskStatus.color,
).filter(Task.project_id == project_id).join(
Project, Project.id == Task.project_id).join(
TaskStatus, TaskStatus.id == Task.task_status_id).join(
Entity, Entity.id == Task.entity_id).join(
Sequence, Sequence.id == Entity.parent_id).join(
Episode, Episode.id == Sequence.parent_id).group_by(
Task.project_id,
Episode.id,
Task.task_type_id,
Task.task_status_id,
TaskStatus.short_name,
TaskStatus.color,
).add_columns(func.count(Task.id)).add_columns(
func.sum(Entity.nb_frames)))
if only_assigned:
query = query.filter(user_service.build_assignee_filter())
return query.all()
def get_full_assets(criterions={}):
"""
Get all assets for given criterions with additional informations: project
name and asset type name.
"""
assigned_to = False
if "assigned_to" in criterions:
assigned_to = True
del criterions["assigned_to"]
query = (Entity.query.filter_by(**criterions).filter(
build_asset_type_filter()).join(Project, EntityType).add_columns(
Project.name,
EntityType.name).order_by(Project.name, EntityType.name,
Entity.name))
if assigned_to:
query = query.outerjoin(Task)
query = query.filter(user_service.build_assignee_filter())
data = query.all()
assets = []
for (asset_model, project_name, asset_type_name) in data:
asset = asset_model.serialize(obj_type="Asset")
asset["project_name"] = project_name
asset["asset_type_name"] = asset_type_name
assets.append(asset)
return assets
def get_edits(criterions={}):
"""
Get all edits for given criterions.
"""
edit_type = get_edit_type()
criterions["entity_type_id"] = edit_type["id"]
is_only_assignation = "assigned_to" in criterions
if is_only_assignation:
del criterions["assigned_to"]
query = Entity.query
query = query_utils.apply_criterions_to_db_query(Entity, query, criterions)
query = query.join(Project).add_columns(Project.name).order_by(Entity.name)
if is_only_assignation:
query = query.outerjoin(Task, Task.entity_id == Entity.id)
query = query.filter(user_service.build_assignee_filter())
try:
data = query.all()
except StatementError: # Occurs when an id is not properly formatted
raise WrongIdFormatException
edits = []
for (edit_model, project_name) in data:
edit = edit_model.serialize(obj_type="Edit")
edit["project_name"] = project_name
edits.append(edit)
return edits
def get_scenes(criterions={}):
"""
Get all scenes for given criterions.
"""
scene_type = get_scene_type()
criterions["entity_type_id"] = scene_type["id"]
Sequence = aliased(Entity, name="sequence")
if "assigned_to" in criterions:
query = query.outerjoin(Task)
query = query.filter(user_service.build_assignee_filter())
del criterions["assigned_to"]
query = Entity.query
query = query_utils.apply_criterions_to_db_query(Entity, query, criterions)
query = (
query.join(Project)
.join(Sequence, Sequence.id == Entity.parent_id)
.add_columns(Project.name)
.add_columns(Sequence.name)
)
try:
data = query.all()
except StatementError: # Occurs when an id is not properly formatted
raise WrongIdFormatException
scenes = []
for (scene_model, project_name, sequence_name) in data:
scene = scene_model.serialize(obj_type="Scene")
scene["project_name"] = project_name
scene["sequence_name"] = sequence_name
scenes.append(scene)
return scenes
def add_project_permission_filter(self, query):
if permissions.has_vendor_permissions():
query = query.filter(user_service.build_assignee_filter())
elif not permissions.has_admin_permissions():
query = query.join(Project) \
.filter(user_service.build_related_projects_filter())
return query
def get_sequences_for_episode(episode_id, only_assigned=False):
"""
Retrieve all sequences related to given episode.
"""
if only_assigned:
Shot = aliased(Entity, name="shot")
query = (Entity.query.join(Shot, Entity.id == Shot.parent_id).join(
Task, Shot.id == Task.entity_id).filter(
Entity.parent_id == episode_id).filter(
user_service.build_assignee_filter()))
return fields.serialize_models(query.all())
else:
return get_episodes({"parent_id": episode_id})
def get_assets(criterions={}):
"""
Get all assets for given criterions.
"""
query = Entity.query.filter(build_asset_type_filter())
assigned_to = False
if "assigned_to" in criterions:
assigned_to = True
del criterions["assigned_to"]
query = query_utils.apply_criterions_to_db_query(Entity, query, criterions)
if assigned_to:
query = query.outerjoin(Task)
query = query.filter(user_service.build_assignee_filter())
result = query.all()
return EntityType.serialize_list(result, obj_type="Asset")
def get_sequences_for_project(project_id, only_assigned=False):
"""
Retrieve all sequences related to given project.
"""
if only_assigned:
Shot = aliased(Entity, name="shot")
query = (Entity.query.join(Shot, Entity.id == Shot.parent_id).join(
Task, Shot.id == Task.entity_id).filter(
Entity.project_id == project_id).filter(
user_service.build_assignee_filter()))
return fields.serialize_models(query.all())
else:
return entities_service.get_entities_for_project(
project_id,
get_sequence_type()["id"], "Sequence")
def get_entities_for_project(project_id,
entity_type_id,
obj_type="Entity",
only_assigned=False):
"""
Retrieve all entities related to given project of which entity is entity
type.
"""
from zou.app.services import user_service
query = (Entity.query.filter(
Entity.entity_type_id == entity_type_id).filter(
Entity.project_id == project_id).order_by(Entity.name))
if only_assigned:
query = query \
.outerjoin(Task) \
.filter(user_service.build_assignee_filter())
result = query.all()
return Entity.serialize_list(result, obj_type=obj_type)
def _get_retake_stats_query(project_id, only_assigned):
Sequence = aliased(Entity, name="sequence")
Episode = aliased(Entity, name="episode")
query = (Task.query.with_entities(
Episode.id,
Entity.nb_frames,
Task.task_type_id,
Task.retake_count,
TaskStatus.is_done,
TaskStatus.is_retake,
).join(Project, Project.id == Task.project_id).join(
Entity, Entity.id == Task.entity_id).join(
Sequence, Sequence.id == Entity.parent_id).join(
Episode, Episode.id == Sequence.parent_id).join(
TaskStatus, TaskStatus.id == Task.task_status_id).filter(
Project.id == project_id))
if only_assigned:
query = query.filter(user_service.build_assignee_filter())
return query
def get_episode_stats_for_project(project_id, only_assigned=False):
"""
Retrieve number of tasks by status, task_types and episodes
for given project.
"""
Sequence = aliased(Entity, name="sequence")
Episode = aliased(Entity, name="episode")
query = (
Task.query.with_entities(
Task.project_id,
Episode.id,
Task.task_type_id,
Task.task_status_id,
TaskStatus.short_name,
TaskStatus.color,
)
.filter(Task.project_id == project_id)
.join(Project, Project.id == Task.project_id)
.join(TaskStatus, TaskStatus.id == Task.task_status_id)
.join(Entity, Entity.id == Task.entity_id)
.join(Sequence, Sequence.id == Entity.parent_id)
.join(Episode, Episode.id == Sequence.parent_id)
.group_by(
Task.project_id,
Episode.id,
Task.task_type_id,
Task.task_status_id,
TaskStatus.short_name,
TaskStatus.color,
)
.add_columns(func.count(Task.id))
.add_columns(func.sum(Entity.nb_frames))
)
if only_assigned:
query = query.filter(user_service.build_assignee_filter())
results = {}
for data in query.all():
add_entry_to_stats(results, *data)
add_entry_to_all_stats(results, *data)
return results
def get_assets(criterions={}):
"""
Get all assets for given criterions.
"""
query = Entity.query.filter(build_asset_type_filter())
assigned_to = False
episode_id = None
if "assigned_to" in criterions:
assigned_to = True
del criterions["assigned_to"]
if "episode_id" in criterions:
episode_id = criterions["episode_id"]
del criterions["episode_id"]
query = query_utils.apply_criterions_to_db_query(Entity, query, criterions)
if assigned_to:
query = query.outerjoin(Task)
query = query.filter(user_service.build_assignee_filter())
if episode_id is not None:
# Filter based on main episode.
query = query.filter(Entity.source_id == episode_id)
result = query.all()
# Filter based on episode casting.
query = (
Entity.query.join(
EntityLink, EntityLink.entity_out_id == Entity.id
)
.filter(EntityLink.entity_in_id == episode_id)
.filter(build_asset_type_filter())
)
query = query_utils.apply_criterions_to_db_query(
Entity, query, criterions
)
# Add non duplicated assets to the list.
result += [a for a in query.all() if a.source_id != episode_id]
else:
result = query.all()
return EntityType.serialize_list(result, obj_type="Asset")
def all_entries(self, query=None, relations=False):
"""
If the user has at least manager permissions, return all previews.
If he's a vendor, return only previews for the tasks he's assigned to.
If he's an artist, return only previews for projects he's a part of.
"""
if query is None:
query = self.model.query
if permissions.has_admin_permissions():
pass
elif permissions.has_vendor_permissions():
query = (PreviewFile.query.join(Task).filter(
user_service.build_assignee_filter()).filter(
user_service.build_open_project_filter()).filter(
Task.id == PreviewFile.task_id))
else:
query = (PreviewFile.query.join(Task, Project).filter(
user_service.build_related_projects_filter()).filter(
user_service.build_open_project_filter()))
previews = query.all()
return self.model.serialize_list(previews, relations=relations)
def get_shots_and_tasks(criterions={}):
"""
Get all shots for given criterions with related tasks for each shot.
"""
shot_type = get_shot_type()
shot_map = {}
task_map = {}
Sequence = aliased(Entity, name="sequence")
Episode = aliased(Entity, name="episode")
query = (
Entity.query.join(Project)
.join(Sequence, Sequence.id == Entity.parent_id)
.outerjoin(Episode, Episode.id == Sequence.parent_id)
.outerjoin(Task, Task.entity_id == Entity.id)
.outerjoin(assignees_table)
.add_columns(
Episode.name,
Episode.id,
Sequence.name,
Sequence.id,
Task.id,
Task.task_type_id,
Task.task_status_id,
Task.priority,
Task.estimation,
Task.duration,
Task.retake_count,
Task.real_start_date,
Task.end_date,
Task.start_date,
Task.due_date,
Task.last_comment_date,
assignees_table.columns.person,
Project.id,
Project.name,
)
.filter(Entity.entity_type_id == shot_type["id"])
)
if "id" in criterions:
query = query.filter(Entity.id == criterions["id"])
if "project_id" in criterions:
query = query.filter(Entity.project_id == criterions["project_id"])
if "episode_id" in criterions:
query = query.filter(Sequence.parent_id == criterions["episode_id"])
if "assigned_to" in criterions:
query = query.filter(user_service.build_assignee_filter())
del criterions["assigned_to"]
for (
shot,
episode_name,
episode_id,
sequence_name,
sequence_id,
task_id,
task_type_id,
task_status_id,
task_priority,
task_estimation,
task_duration,
task_retake_count,
task_real_start_date,
task_end_date,
task_start_date,
task_due_date,
task_last_comment_date,
person_id,
project_id,
project_name,
) in query.all():
shot_id = str(shot.id)
shot.data = shot.data or {}
if shot_id not in shot_map:
shot_map[shot_id] = fields.serialize_dict(
{
"canceled": shot.canceled,
"data": shot.data,
"description": shot.description,
"entity_type_id": shot.entity_type_id,
"episode_id": episode_id,
"episode_name": episode_name or "",
"fps": shot.data.get("fps", None),
"frame_in": shot.data.get("frame_in", None),
"frame_out": shot.data.get("frame_out", None),
"id": shot.id,
"name": shot.name,
"nb_frames": shot.nb_frames,
"parent_id": shot.parent_id,
"preview_file_id": shot.preview_file_id or None,
"project_id": project_id,
"project_name": project_name,
"sequence_id": sequence_id,
"sequence_name": sequence_name,
"source_id": shot.source_id,
"tasks": [],
"type": "Shot",
}
)
if task_id is not None:
if task_id not in task_map:
task_dict = fields.serialize_dict(
{
"id": task_id,
"entity_id": shot_id,
"task_status_id": task_status_id,
"task_type_id": task_type_id,
"priority": task_priority or 0,
"estimation": task_estimation,
"duration": task_duration,
"retake_count": task_retake_count,
"real_start_date": task_real_start_date,
"end_date": task_end_date,
"start_date": task_start_date,
"due_date": task_due_date,
"last_comment_date": task_last_comment_date,
"assignees": [],
}
)
task_map[task_id] = task_dict
shot_dict = shot_map[shot_id]
shot_dict["tasks"].append(task_dict)
if person_id:
task_map[task_id]["assignees"].append(str(person_id))
return list(shot_map.values())
def get_assets_and_tasks(criterions={}, page=1):
"""
Get all assets for given criterions with related tasks for each asset.
"""
asset_map = {}
task_map = {}
query = (Entity.query.filter(build_asset_type_filter()).join(
EntityType).outerjoin(Task).outerjoin(assignees_table).add_columns(
EntityType.name,
Task.id,
Task.task_type_id,
Task.task_status_id,
Task.priority,
Task.estimation,
Task.duration,
Task.retake_count,
Task.real_start_date,
Task.end_date,
Task.start_date,
Task.due_date,
Task.last_comment_date,
assignees_table.columns.person,
).order_by(EntityType.name, Entity.name))
if "id" in criterions:
query = query.filter(Entity.id == criterions["id"])
if "project_id" in criterions:
query = query.filter(Entity.project_id == criterions["project_id"])
if "episode_id" in criterions:
if criterions["episode_id"] == "main":
query = query.filter(Entity.source_id == None)
elif criterions["episode_id"] != "all":
query = query.filter(Entity.source_id == criterions["episode_id"])
if "assigned_to" in criterions:
query = query.filter(user_service.build_assignee_filter())
del criterions["assigned_to"]
for (
asset,
entity_type_name,
task_id,
task_type_id,
task_status_id,
task_priority,
task_estimation,
task_duration,
task_retake_count,
task_real_start_date,
task_end_date,
task_start_date,
task_due_date,
task_last_comment_date,
person_id,
) in query.all():
if asset.source_id is None:
source_id = ""
else:
source_id = str(asset.source_id)
if asset.id not in asset_map:
asset_map[asset.id] = {
"id": str(asset.id),
"name": asset.name,
"preview_file_id": str(asset.preview_file_id or ""),
"description": asset.description,
"asset_type_name": entity_type_name,
"asset_type_id": str(asset.entity_type_id),
"canceled": asset.canceled,
"episode_id": source_id,
"data": fields.serialize_value(asset.data),
"tasks": [],
}
if task_id is not None:
if task_id not in task_map:
task_dict = {
"id":
str(task_id),
"entity_id":
str(asset.id),
"task_status_id":
str(task_status_id),
"task_type_id":
str(task_type_id),
"priority":
task_priority or 0,
"estimation":
task_estimation,
"duration":
task_duration,
"retake_count":
task_retake_count,
"real_start_date":
fields.serialize_value(task_real_start_date),
"end_date":
fields.serialize_value(task_end_date),
"start_date":
fields.serialize_value(task_start_date),
"due_date":
fields.serialize_value(task_due_date),
"last_comment_date":
fields.serialize_value(task_last_comment_date),
"assignees": [],
}
task_map[task_id] = task_dict
asset_dict = asset_map[asset.id]
asset_dict["tasks"].append(task_dict)
if person_id:
task_map[task_id]["assignees"].append(str(person_id))
return list(asset_map.values())
def get_assets_and_tasks(criterions={}, page=1, with_episode_ids=False):
"""
Get all assets for given criterions with related tasks for each asset.
"""
asset_map = {}
task_map = {}
Episode = aliased(Entity, name="episode")
query = (
Entity.query.filter(build_asset_type_filter())
.join(EntityType, Entity.entity_type_id == EntityType.id)
.outerjoin(Task)
.outerjoin(assignees_table)
)
tasks_query = query.add_columns(
EntityType.name,
Task.id,
Task.task_type_id,
Task.task_status_id,
Task.priority,
Task.estimation,
Task.duration,
Task.retake_count,
Task.real_start_date,
Task.end_date,
Task.start_date,
Task.due_date,
Task.last_comment_date,
assignees_table.columns.person,
).order_by(EntityType.name, Entity.name)
if "id" in criterions:
tasks_query = tasks_query.filter(Entity.id == criterions["id"])
if "project_id" in criterions:
tasks_query = tasks_query.filter(
Entity.project_id == criterions["project_id"]
)
if "episode_id" in criterions:
episode_id = criterions["episode_id"]
if episode_id == "main":
tasks_query = tasks_query.filter(Entity.source_id == None)
elif episode_id != "all":
tasks_query = tasks_query.outerjoin(
EntityLink, EntityLink.entity_out_id == Entity.id
)
tasks_query = tasks_query.filter(
or_(
Entity.source_id == episode_id,
EntityLink.entity_in_id == episode_id,
)
)
if "assigned_to" in criterions:
tasks_query = tasks_query.filter(user_service.build_assignee_filter())
del criterions["assigned_to"]
cast_in_episode_ids = {}
if "project_id" in criterions or with_episode_ids:
episode_links_query = (
EntityLink.query.join(
Episode, EntityLink.entity_in_id == Episode.id
)
.join(EntityType, EntityType.id == Episode.entity_type_id)
.filter(EntityType.name == "Episode")
.order_by(Episode.name)
)
if "project_id" in criterions:
episode_links_query = episode_links_query.filter(
Episode.project_id == criterions["project_id"]
)
for link in episode_links_query.all():
if str(link.entity_out_id) not in cast_in_episode_ids:
cast_in_episode_ids[str(link.entity_out_id)] = []
cast_in_episode_ids[str(link.entity_out_id)].append(
str(link.entity_in_id)
)
for (
asset,
entity_type_name,
task_id,
task_type_id,
task_status_id,
task_priority,
task_estimation,
task_duration,
task_retake_count,
task_real_start_date,
task_end_date,
task_start_date,
task_due_date,
task_last_comment_date,
person_id,
) in tasks_query.all():
if asset.source_id is None:
source_id = ""
else:
source_id = str(asset.source_id)
asset_id = str(asset.id)
if asset_id not in asset_map:
asset_map[asset_id] = {
"id": asset_id,
"name": asset.name,
"preview_file_id": str(asset.preview_file_id or ""),
"description": asset.description,
"asset_type_name": entity_type_name,
"asset_type_id": str(asset.entity_type_id),
"canceled": asset.canceled,
"ready_for": str(asset.ready_for),
"episode_id": source_id,
"casting_episode_ids": cast_in_episode_ids.get(asset_id, []),
"is_casting_standby": asset.is_casting_standby,
"data": fields.serialize_value(asset.data),
"tasks": [],
}
if task_id is not None:
if task_id not in task_map:
task_dict = {
"id": str(task_id),
"entity_id": asset_id,
"task_status_id": str(task_status_id),
"task_type_id": str(task_type_id),
"priority": task_priority or 0,
"estimation": task_estimation,
"duration": task_duration,
"retake_count": task_retake_count,
"real_start_date": fields.serialize_value(
task_real_start_date
),
"end_date": fields.serialize_value(task_end_date),
"start_date": fields.serialize_value(task_start_date),
"due_date": fields.serialize_value(task_due_date),
"last_comment_date": fields.serialize_value(
task_last_comment_date
),
"assignees": [],
}
task_map[task_id] = task_dict
asset_dict = asset_map[asset_id]
asset_dict["tasks"].append(task_dict)
if person_id:
task_map[task_id]["assignees"].append(str(person_id))
return list(asset_map.values())
