def get_shots_children_task_type(request):
"""returns the Task Types defined under the Shot container
"""
sql_query = """select
"SimpleEntities".id as type_id,
"SimpleEntities".name as type_name
from "SimpleEntities"
join "SimpleEntities" as "Task_SimpleEntities" on "SimpleEntities".id = "Task_SimpleEntities".type_id
join "Tasks" on "Task_SimpleEntities".id = "Tasks".id
join "Shots" on "Tasks".parent_id = "Shots".id
group by "SimpleEntities".id, "SimpleEntities".name
order by "SimpleEntities".name"""
result = DBSession.connection().execute(sql_query)
return_data = [{"id": r[0], "name": r[1]} for r in result.fetchall()]
content_range = "%s-%s/%s"
type_count = len(return_data)
content_range = content_range % (0, type_count - 1, type_count)
logger.debug("content_range : %s" % content_range)
resp = Response(json_body=return_data)
resp.content_range = content_range
return resp
def get_assets_type_task_types(request):
"""returns the Task Types defined under the Asset container
"""
type_id = request.matchdict.get('t_id', None)
logger.debug('type_id %s'% type_id)
sql_query = """select
"SimpleEntities".id as type_id,
"SimpleEntities".name as type_name
from "SimpleEntities"
join "SimpleEntities" as "Task_SimpleEntities" on "SimpleEntities".id = "Task_SimpleEntities".type_id
join "Tasks" on "Task_SimpleEntities".id = "Tasks".id
join "Assets" on "Tasks".parent_id = "Assets".id
join "SimpleEntities" as "Assets_SimpleEntities" on "Assets_SimpleEntities".id = "Assets".id
%(where_condition)s
group by "SimpleEntities".id, "SimpleEntities".name
order by "SimpleEntities".name"""
where_condition = ''
if type_id:
where_condition = 'where "Assets_SimpleEntities".type_id = %(type_id)s'%{'type_id': type_id}
sql_query = sql_query %{'where_condition':where_condition}
result = DBSession.connection().execute(sql_query)
return_data = [
{
'id': r[0],
'name': r[1]
}
for r in result.fetchall()
]
content_range = '%s-%s/%s'
type_count = len(return_data)
content_range = content_range % (0, type_count - 1, type_count)
logger.debug('content_range : %s' % content_range)
resp = Response(
json_body=return_data
)
resp.content_range = content_range
return resp
def get_entity_notes(request):
"""RESTful version of getting all notes of a task
"""
logger.debug('get_entity_notes is running')
entity_id = request.matchdict.get('id', -1)
entity = Entity.query.filter(Entity.id == entity_id).first()
if not entity:
transaction.abort()
return Response('There is no entity with id: %s' % entity_id, 500)
sql_query = """select "User_SimpleEntities".id as user_id,
"User_SimpleEntities".name,
"Users_Thumbnail_Links".full_path,
"Notes_SimpleEntities".id as note_id,
"Notes_SimpleEntities".description,
"Notes_SimpleEntities".date_created,
"Notes_Types_SimpleEntities".id,
"Notes_Types_SimpleEntities".name,
"Notes_Types_SimpleEntities".html_class
from "Notes"
join "SimpleEntities" as "Notes_SimpleEntities" on "Notes_SimpleEntities".id = "Notes".id
left outer join "SimpleEntities" as "Notes_Types_SimpleEntities" on "Notes_Types_SimpleEntities".id = "Notes_SimpleEntities".type_id
join "SimpleEntities" as "User_SimpleEntities" on "Notes_SimpleEntities".created_by_id = "User_SimpleEntities".id
join "Links" as "Users_Thumbnail_Links" on "Users_Thumbnail_Links".id = "User_SimpleEntities".thumbnail_id
where "Notes".entity_id = %(entity_id)s
order by "Notes_SimpleEntities".date_created desc"""
sql_query = sql_query % {'entity_id': entity_id}
result = DBSession.connection().execute(sql_query)
return_data = [
{
'created_by_id': r[0],
'created_by_name': r[1],
'created_by_thumbnail': r[2],
'note_id': r[3],
'content': r[4],
'created_date': milliseconds_since_epoch(r[5]),
'note_type_id': r[6],
'note_type_name': r[7],
'note_type_color': r[8]
}
for r in result.fetchall()
]
return return_data
def get_project_sequences_count(request):
"""returns the count of sequences in a project
"""
project_id = request.matchdict.get('id', -1)
sql_query = """select
count(1)
from "Sequences"
join "Tasks" on "Sequences".id = "Tasks".id
where "Tasks".project_id = %s""" % project_id
return DBSession.connection().execute(sql_query).fetchone()[0]
def do_db_setup():
"""the common routing for setting up the database
"""
from sqlalchemy.exc import UnboundExecutionError
from stalker import db
from stalker.db import DBSession
DBSession.remove()
DBSession.close()
try:
DBSession.connection()
logger.debug('already connected, not creating any new connections')
except UnboundExecutionError:
# no connection do setup
logger.debug('doing a new connection with NullPool')
from stalker import defaults
from sqlalchemy.pool import NullPool
settings = defaults.database_engine_settings
settings['sqlalchemy.poolclass'] = NullPool
db.setup(settings)
def get_shots_count(request):
"""returns the count of Shots in the given Project
"""
project_id = request.matchdict.get("id", -1)
sql_query = (
"""select
count(1)
from "Shots"
join "Tasks" on "Shots".id = "Tasks".id
where "Tasks".project_id = %s"""
% project_id
)
return DBSession.connection().execute(sql_query).fetchone()[0]
def get_entity_references_count(request):
"""called when the count of references to Project/Task/Asset/Shot/Sequence
is requested
"""
entity_id = request.matchdict.get('id', -1)
entity = Entity.query.filter(Entity.id == entity_id).first()
logger.debug('asking references for entity: %s' % entity)
# using Raw SQL queries here to fasten things up quite a bit and also do
# some fancy queries like getting all the references of tasks of a project
# also with their tags
sql_query = """
select count(*) from (
select
"Links".id
from "Task_References"
join (
with recursive parent_ids(id, parent_id, project_id) as (
select task.id, task.parent_id, task.project_id from "Tasks" task
union all
select task.id, parent.parent_id, task.project_id
from "Tasks" task, parent_ids parent
where task.parent_id = parent.id
)
select
distinct parent_ids.id as id --, coalesce(parent_ids.parent_id, parent_ids.project_id) as parent_id
from parent_ids
where parent_ids.id = %(id)s or parent_ids.parent_id = %(id)s or parent_ids.project_id = %(id)s -- show also children references
group by parent_ids.id, parent_id, project_id
order by parent_ids.id
) as child_tasks on child_tasks.id = "Task_References".task_id
join "Links" on "Task_References".link_id = "Links".id
join "SimpleEntities" on "Links".id = "SimpleEntities".id
join "Links" as "Thumbnails" on "SimpleEntities".thumbnail_id = "Thumbnails".id
join "Entity_Tags" on "Links".id = "Entity_Tags".entity_id
join "Tags" on "Entity_Tags".tag_id = "Tags".id
join "SimpleEntities" as "SimpleEntities_Tags" on "Tags".id = "SimpleEntities_Tags".id
join "SimpleEntities" as "SimpleEntities_Tasks" on "Task_References".task_id = "SimpleEntities_Tasks".id
group by "Links".id
) as data
""" % {'id': entity_id}
result = DBSession.connection().execute(sql_query)
return result.fetchone()[0]
def get_task_reviewers(request):
"""RESTful version of getting all reviews of a task
"""
logger.debug('get_task_reviewers is running')
task_id = request.matchdict.get('id', -1)
task = Task.query.filter(Task.id == task_id).first()
if not task:
transaction.abort()
return Response('There is no task with id: %s' % task_id, 500)
sql_query = """
select
"Reviewers".name as reviewers_name,
"Reviewers".id as reviewers_id
from "Reviews"
join "Tasks" as "Review_Tasks" on "Review_Tasks".id = "Reviews".task_id
join "SimpleEntities" as "Reviewers" on "Reviewers".id = "Reviews".reviewer_id
%(where_conditions)s
group by "Reviewers".id, "Reviewers".name
"""
where_conditions = """where "Review_Tasks".id = %(task_id)s""" % {
'task_id': task.id
}
logger.debug('where_conditions %s ' % where_conditions)
sql_query = sql_query % {'where_conditions': where_conditions}
result = DBSession.connection().execute(sql_query)
return_data = [
{
'reviewer_name': r[0],
'reviewer_id': r[1]
}
for r in result.fetchall()
]
return return_data
def get_assets_types(request):
"""returns the Asset Types
"""
sql_query ="""select
"Assets_Types_SimpleEntities".id,
"Assets_Types_SimpleEntities".name
from "Assets"
join "SimpleEntities" as "Assets_SimpleEntities" on "Assets_SimpleEntities".id = "Assets".id
join "SimpleEntities" as "Assets_Types_SimpleEntities" on "Assets_Types_SimpleEntities".id = "Assets_SimpleEntities".type_id
group by
"Assets_Types_SimpleEntities".name,
"Assets_Types_SimpleEntities".id
order by "Assets_Types_SimpleEntities".name
"""
result = DBSession.connection().execute(sql_query)
return_data = [
{
'asset_type_id': r[0],
'asset_type_name': r[1]
}
for r in result.fetchall()
]
content_range = '%s-%s/%s'
type_count = len(return_data)
content_range = content_range % (0, type_count - 1, type_count)
logger.debug('content_range : %s' % content_range)
resp = Response(
json_body=return_data
)
resp.content_range = content_range
return resp
def get_project_tasks_today(request):
"""returns the project lead as a json data
"""
project_id = request.matchdict.get('id', -1)
action = request.matchdict.get('action', -1)
today = datetime.date.today()
start = datetime.time(0, 0)
end = datetime.time(23, 59, 59)
start_of_today = datetime.datetime.combine(today, start)
end_of_today = datetime.datetime.combine(today, end)
start = time.time()
sql_query = """select "Tasks".id,
"SimpleEntities".name,
array_agg(distinct("SimpleEntities_Resource".id)),
array_agg(distinct("SimpleEntities_Resource".name)),
"SimpleEntities_Status".name,
"SimpleEntities_Status".html_class,
(coalesce("Task_TimeLogs".duration, 0.0))::float /
("Tasks".schedule_timing * (case "Tasks".schedule_unit
when 'min' then 60
when 'h' then %(working_seconds_per_hour)s
when 'd' then %(working_seconds_per_day)s
when 'w' then %(working_seconds_per_week)s
when 'm' then %(working_seconds_per_month)s
when 'y' then %(working_seconds_per_year)s
else 0
end)) * 100.0 as percent_complete
from "Tasks"
join "SimpleEntities" on "Tasks".id = "SimpleEntities".id
join "Task_Resources" on "Tasks".id = "Task_Resources".task_id
join "SimpleEntities" as "SimpleEntities_Resource" on "Task_Resources".resource_id = "SimpleEntities_Resource".id
join "Statuses" on "Tasks".status_id = "Statuses".id
join "SimpleEntities" as "SimpleEntities_Status" on "Statuses".id = "SimpleEntities_Status".id
left outer join (
select
"TimeLogs".task_id,
extract(epoch from sum("TimeLogs".end::timestamp AT TIME ZONE 'UTC' - "TimeLogs".start::timestamp AT TIME ZONE 'UTC')) as duration
from "TimeLogs"
group by task_id
) as "Task_TimeLogs" on "Task_TimeLogs".task_id = "Tasks".id
left outer join "TimeLogs" on "Tasks".id = "TimeLogs".task_id
"""
if action == 'progress':
sql_query += """where
"Tasks".computed_start::timestamp AT TIME ZONE 'UTC' < '%(end_of_today)s' and
"Tasks".computed_end::timestamp AT TIME ZONE 'UTC' > '%(start_of_today)s'"""
elif action == 'end':
sql_query += """where
"Tasks".computed_end::timestamp AT TIME ZONE 'UTC' > '%(start_of_today)s' and
"Tasks".computed_end::timestamp AT TIME ZONE 'UTC' <= '%(end_of_today)s'
"""
sql_query += """ and "Tasks".project_id = %(project_id)s
group by "Tasks".id,
"SimpleEntities".name,
"SimpleEntities_Status".name,
"SimpleEntities_Status".html_class,
"Task_TimeLogs".duration,
"Tasks".schedule_timing,
"Tasks".schedule_unit
"""
studio = Studio.query.first()
assert isinstance(studio, Studio)
ws_per_hour = 3600
ws_per_day = studio.daily_working_hours * ws_per_hour
ws_per_week = studio.weekly_working_days * ws_per_day
ws_per_month = ws_per_week * 4
ws_per_year = studio.yearly_working_days * ws_per_day
sql_query = sql_query % {
'project_id': project_id,
'start_of_today': start_of_today.strftime('%Y-%m-%d %H:%M:%S'),
'end_of_today': end_of_today.strftime('%Y-%m-%d %H:%M:%S'),
'working_seconds_per_hour': ws_per_hour,
'working_seconds_per_day': ws_per_day,
'working_seconds_per_week': ws_per_week,
'working_seconds_per_month': ws_per_month,
'working_seconds_per_year': ws_per_year
}
logger.debug('sql_query : %s' % sql_query)
result = DBSession.connection().execute(sql_query)
data = [
{
'task_id': r[0],
'task_name': r[1],
'resources': [
'<a href="/users/%(id)s/view">%(name)s</a>' % {
'id': r[2][i],
'name': r[3][i]
} for i in range(len(r[2]))
],
'status': r[4],
'status_color': r[5],
'percent_complete': r[6]
} for r in result.fetchall()
]
end = time.time()
logger.debug('%s rows took : %s seconds' % (len(data), (end - start)))
return data
def get_entity_references(request):
"""called when the references to Project/Task/Asset/Shot/Sequence is
requested
"""
entity_id = request.matchdict.get('id', -1)
entity = Entity.query.filter(Entity.id == entity_id).first()
logger.debug('asking references for entity: %s' % entity)
offset = request.params.get('offset')
limit = request.params.get('limit')
# using Raw SQL queries here to fasten things up quite a bit and also do
# some fancy queries like getting all the references of tasks of a project
# also with their tags
sql_query = """
-- select all links assigned to a project tasks or assigned to a task and its children
select
"Links".id,
"Links".full_path,
"Links".original_filename,
"Thumbnails".full_path as "thumbnail_full_path",
array_agg("SimpleEntities_Tags".name) as tags,
array_agg("Task_References".task_id) as entity_id,
array_agg("SimpleEntities_Tasks".name) as task_name,
array_agg("SimpleEntities_Tasks".entity_type) as entity_type
from "Task_References"
join (
with recursive parent_ids(id, parent_id, project_id) as (
select task.id, task.parent_id, task.project_id from "Tasks" task
union all
select task.id, parent.parent_id, task.project_id
from "Tasks" task, parent_ids parent
where task.parent_id = parent.id
)
select
distinct parent_ids.id as id
from parent_ids
where parent_ids.id = %(id)s or parent_ids.parent_id = %(id)s or parent_ids.project_id = %(id)s -- show also children references
group by parent_ids.id, parent_id, project_id
order by parent_ids.id
) as child_tasks on child_tasks.id = "Task_References".task_id
join "Links" on "Task_References".link_id = "Links".id
join "SimpleEntities" on "Links".id = "SimpleEntities".id
join "Links" as "Thumbnails" on "SimpleEntities".thumbnail_id = "Thumbnails".id
join "Entity_Tags" on "Links".id = "Entity_Tags".entity_id
join "Tags" on "Entity_Tags".tag_id = "Tags".id
join "SimpleEntities" as "SimpleEntities_Tags" on "Tags".id = "SimpleEntities_Tags".id
join "SimpleEntities" as "SimpleEntities_Tasks" on "Task_References".task_id = "SimpleEntities_Tasks".id
group by "Links".id, "Thumbnails".full_path, "Links".full_path,
"Links".original_filename
order by "Links".id
""" % {'id': entity_id}
if offset and limit:
sql_query += "offset %s limit %s" % (offset, limit)
time_time = time.time
db_start = time_time()
result = DBSession.connection().execute(sql_query)
db_end = time_time()
db_time = db_end - db_start
python_start = time_time()
return_val = [
{
'id': r[0],
'full_path': r[1],
'original_filename': r[2],
'thumbnail_full_path': r[3],
'tags': r[4],
'entity_ids': r[5],
'entity_names': r[6],
'entity_types': r[7]
} for r in result.fetchall()
]
python_end = time_time()
python_time = python_end - python_start
logger.debug('get_entity_references took: %s seconds for %s rows' %
(python_end - db_start , len(return_val)))
return return_val
def get_assets(request):
"""returns all the Assets of a given Project
"""
logger.debug('*** get_assets method starts ***')
project_id = request.matchdict.get('id', -1)
asset_type_id = request.params.get('asset_type_id', None)
asset_id = request.params.get('entity_id', None)
sql_query = """
select
"Assets".id as asset_id,
"Asset_SimpleEntities".name as asset_name,
"Asset_SimpleEntities".description as asset_description,
"Links".full_path as asset_full_path,
"Distinct_Asset_Statuses".asset_status_code as asset_status_code,
"Distinct_Asset_Statuses".asset_status_html_class as asset_status_html_class,
array_agg("Distinct_Asset_Task_Types".type_name) as type_name,
array_agg("Tasks".id) as task_id,
array_agg("Task_SimpleEntities".name) as task_name,
array_agg("Task_Statuses".code) as status_code,
array_agg("Task_Statuses_SimpleEntities".html_class) as status_html_class,
array_agg(coalesce(
-- for parent tasks
(case "Tasks".schedule_seconds
when 0 then 0
else "Tasks".total_logged_seconds::float / "Tasks".schedule_seconds * 100
end
),
-- for child tasks we need to count the total seconds of related TimeLogs
(coalesce("Task_TimeLogs".duration, 0.0))::float /
("Tasks".schedule_timing * (case "Tasks".schedule_unit
when 'min' then 60
when 'h' then 3600
when 'd' then 32400
when 'w' then 147600
when 'm' then 590400
when 'y' then 7696277
else 0
end)) * 100.0
)) as percent_complete,
"Assets_Types_SimpleEntities".name as asset_type_name
from "Tasks"
join "Assets" on "Assets".id = "Tasks".parent_id
join "SimpleEntities" as "Asset_SimpleEntities" on "Assets".id = "Asset_SimpleEntities".id
join "SimpleEntities" as "Task_SimpleEntities" on "Tasks".id = "Task_SimpleEntities".id
left join "Types" as "Assets_Types" on "Assets_Types".id = "Asset_SimpleEntities".type_id
join "SimpleEntities" as "Assets_Types_SimpleEntities" on "Assets_Types_SimpleEntities".id = "Assets_Types".id
left join "Links" on "Asset_SimpleEntities".thumbnail_id = "Links".id
join(
select
"Assets".id as asset_id,
"Statuses".code as asset_status_code,
"SimpleEntities".html_class as asset_status_html_class
from "Tasks"
join "Assets" on "Assets".id = "Tasks".id
join "Statuses" on "Statuses".id = "Tasks".status_id
join "SimpleEntities" on "SimpleEntities".id = "Statuses".id
)as "Distinct_Asset_Statuses" on "Assets".id = "Distinct_Asset_Statuses".asset_id
left join (
select
"SimpleEntities".id as type_id,
"SimpleEntities".name as type_name
from "SimpleEntities"
join "SimpleEntities" as "Task_SimpleEntities" on "SimpleEntities".id = "Task_SimpleEntities".type_id
join "Tasks" on "Task_SimpleEntities".id = "Tasks".id
join "Assets" on "Tasks".parent_id = "Assets".id
group by "SimpleEntities".id, "SimpleEntities".name
order by "SimpleEntities".id
) as "Distinct_Asset_Task_Types" on "Task_SimpleEntities".type_id = "Distinct_Asset_Task_Types".type_id
join "Statuses" as "Task_Statuses" on "Tasks".status_id = "Task_Statuses".id
join "SimpleEntities" as "Task_Statuses_SimpleEntities" on "Task_Statuses_SimpleEntities".id = "Tasks".status_id
left outer join (
select
"TimeLogs".task_id,
extract(epoch from sum("TimeLogs".end::timestamp AT TIME ZONE 'UTC' - "TimeLogs".start::timestamp AT TIME ZONE 'UTC')) as duration
from "TimeLogs"
group by task_id
) as "Task_TimeLogs" on "Task_TimeLogs".task_id = "Tasks".id
where "Tasks".project_id = %(project_id)s %(where_conditions)s
group by
"Assets".id,
"Asset_SimpleEntities".name,
"Asset_SimpleEntities".description,
"Links".full_path,
"Distinct_Asset_Statuses".asset_status_code,
"Distinct_Asset_Statuses".asset_status_html_class,
"Assets_Types_SimpleEntities".name
order by "Asset_SimpleEntities".name
"""
where_conditions = ''
if asset_type_id:
where_conditions = """and "Assets_Types_SimpleEntities".id = %(asset_type_id)s""" %({'asset_type_id':asset_type_id})
if asset_id:
where_conditions = """and "Assets".id = %(asset_id)s""" %({'asset_id':asset_id})
sql_query = sql_query % {'where_conditions':where_conditions, 'project_id':project_id}
update_asset_permission = \
PermissionChecker(request)('Update_Asset')
delete_asset_permission = \
PermissionChecker(request)('Delete_Asset')
result = DBSession.connection().execute(sql_query)
return_data = []
for r in result.fetchall():
r_data = {
'id': r[0],
'name': r[1],
'description': r[2],
'thumbnail_full_path': r[3] if r[3] else None,
'status': r[4],
'status_color': r[5],
'update_asset_action': '/tasks/%s/update/dialog' % r[0]
if update_asset_permission else None,
'delete_asset_action': '/tasks/%s/delete/dialog' % r[0]
if delete_asset_permission else None
}
task_types_names = r[6]
task_ids = r[7]
task_names = r[8]
task_statuses = r[9]
task_percent_complete = r[11]
logger.debug('task_types_names %s ' % task_types_names)
r_data['nulls'] = []
for index1 in range(len(task_types_names)):
if task_types_names[index1]:
r_data[task_types_names[index1]]= []
for index in range(len(task_types_names)):
if task_types_names[index]:
r_data[task_types_names[index]].append([task_ids[index], task_names[index], task_statuses[index],task_percent_complete[index]])
else:
r_data['nulls'].append(
[task_ids[index], task_names[index], task_statuses[index],
task_percent_complete[index]]
)
return_data.append(r_data)
# data = [
# {
# 'id': r[0],
# 'name': r[1],
# 'code': r[2],
# 'description': r[3],
# 'type_id': r[4],
# 'type_name': r[5],
# 'date_created': r[6],
# 'created_by_id': r[7],
# 'created_by_name': r[8],
# 'thumbnail_full_path': r[9],
# 'status': r[10],
# 'status_color': r[11],
# 'percent_complete': r[12]
# } for r in result.fetchall()
# ]
return return_data
def get_users_count(request):
"""returns all users or one particular user from database
"""
# if there is a simple flag, just return ids and names and login
#simple = request.params.get('simple')
# if there is an id it is probably a project
entity_id = request.matchdict.get('id')
entity_type = None
if entity_id:
sql_query = \
'select entity_type from "SimpleEntities" where id=%s' % entity_id
data = DBSession.connection().execute(sql_query).fetchone()
entity_type = data[0] if data else None
logger.debug('entity_id : %s' % entity_id)
logger.debug('entity_type: %s' % entity_type)
if entity_id and entity_type not in ['Project', 'Department', 'Group', 'Task', 'User']:
# there is no entity_type for that entity
return []
start = time.time()
sql_query = """select
count("Users".id)
from "SimpleEntities"
join "Users" on "SimpleEntities".id = "Users".id
left outer join (
select
uid,
array_agg(did) as dep_ids,
array_agg(name) as dep_names
from "User_Departments"
join "SimpleEntities" on "User_Departments".did = "SimpleEntities".id
group by uid
) as user_departments on user_departments.uid = "Users".id
left outer join (
select
uid,
array_agg(gid) as group_ids,
array_agg(name) as group_names
from "User_Groups"
join "SimpleEntities" on "User_Groups".gid = "SimpleEntities".id
group by uid
) as user_groups on user_groups.uid = "Users".id
left outer join (
select resource_id, count(task_id) as task_count from "Task_Resources" group by resource_id
) as tasks on tasks.resource_id = "Users".id
left outer join (
select
owner_id,
count("Tickets".id) as ticket_count
from "Tickets"
join "SimpleEntities" on "Tickets".status_id = "SimpleEntities".id
where "SimpleEntities".name = 'New'
group by owner_id, name
) as tickets on tickets.owner_id = "Users".id
left outer join "Links" on "SimpleEntities".thumbnail_id = "Links".id
"""
if entity_type == "Project":
sql_query += """join "Project_Users" on "Users".id = "Project_Users".user_id
where "Project_Users".project_id = %(id)s
""" % {'id': entity_id}
elif entity_type == "Department":
sql_query += """join "User_Departments" on "Users".id = "User_Departments".uid
where "User_Departments".did = %(id)s
""" % {'id': entity_id}
elif entity_type == "Group":
sql_query += """join "User_Groups" on "Users".id = "User_Groups".uid
where "User_Groups".gid = %(id)s
""" % {'id': entity_id}
elif entity_type == "Task":
sql_query += """join "Task_Resources" on "Users".id = "Task_Resources".resource_id
where "Task_Resources".task_id = %(id)s
""" % {'id': entity_id}
elif entity_type == "User":
sql_query += 'where "Users".id = %s' % entity_id
return DBSession.connection().execute(sql_query).fetchone()[0]
def get_time_logs(request):
"""returns all the Shots of the given Project
"""
logger.debug('get_time_logs is running')
entity_id = request.matchdict.get('id', -1)
logger.debug('entity_id : %s' % entity_id)
data = DBSession.connection().execute(
'select entity_type from "SimpleEntities" where id=%s' % entity_id
).fetchone()
entity_type = None
if len(data):
entity_type = data[0]
logger.debug('entity_type : %s' % entity_type)
sql_query = """select
"TimeLogs".id,
"TimeLogs".task_id,
"SimpleEntities_Task".name,
"SimpleEntities_Status".name,
parent_names.parent_name,
"TimeLogs".resource_id,
"SimpleEntities_Resource".name,
extract(epoch from "TimeLogs".end::timestamp AT TIME ZONE 'UTC' - "TimeLogs".start::timestamp AT TIME ZONE 'UTC') as total_seconds,
extract(epoch from "TimeLogs".start::timestamp AT TIME ZONE 'UTC') * 1000 as start,
extract(epoch from "TimeLogs".end::timestamp AT TIME ZONE 'UTC') * 1000 as end
from "TimeLogs"
join "Tasks" on "TimeLogs".task_id = "Tasks".id
join "SimpleEntities" as "SimpleEntities_Task" on "Tasks".id = "SimpleEntities_Task".id
join "SimpleEntities" as "SimpleEntities_Status" on "Tasks".status_id = "SimpleEntities_Status".id
join "SimpleEntities" as "SimpleEntities_Resource" on "TimeLogs".resource_id = "SimpleEntities_Resource".id
join (
select
parent_data.id,
"SimpleEntities".name,
array_to_string(array_agg(
case
when "SimpleEntities_parent".entity_type = 'Project'
then "Projects".code
else "SimpleEntities_parent".name
end),
' | '
) as parent_name
from (
with recursive parent_ids(id, parent_id, n) as (
select task.id, coalesce(task.parent_id, task.project_id), 0
from "Tasks" task
union
select task.id, parent.parent_id, parent.n + 1
from "Tasks" task, parent_ids parent
where task.parent_id = parent.id
)
select
parent_ids.id, parent_id as parent_id, parent_ids.n
from parent_ids
order by id, parent_ids.n desc
) as parent_data
join "SimpleEntities" on "SimpleEntities".id = parent_data.id
join "SimpleEntities" as "SimpleEntities_parent" on "SimpleEntities_parent".id = parent_data.parent_id
left outer join "Projects" on parent_data.parent_id = "Projects".id
group by parent_data.id, "SimpleEntities".name
) as parent_names on "TimeLogs".task_id = parent_names.id
"""
if entity_type == u'User':
sql_query += 'where "TimeLogs".resource_id = %s' % entity_id
elif entity_type == u'Task':
sql_query += 'where "TimeLogs".task_id = %s' % entity_id
elif entity_type is None:
return []
result = DBSession.connection().execute(sql_query)
start = time.time()
data = [
{
'id': r[0],
'entity_type': 'timelogs',
'task_id': r[1],
'task_name': r[2],
'task_status': r[3],
'parent_name': r[4],
'resource_id': r[5],
'resource_name': r[6],
'duration': r[7],
'start': r[8],
'end': r[9],
'className': 'label-important',
'allDay': '0'
} for r in result.fetchall()
]
end = time.time()
logger.debug('get_entity_time_logs took: %s seconds' % (end - start))
return data
def get_users(request):
"""returns all users or one particular user from database
"""
# if there is a simple flag, just return ids and names and login
#simple = request.params.get('simple')
# if there is an id it is probably a project
entity_id = request.matchdict.get('id')
entity_type = None
update_user_permission = PermissionChecker(request)('Update_User')
delete_user_permission = PermissionChecker(request)('Delete_User')
delete_user_action ='/users/%(id)s/delete/dialog'
if entity_id:
sql_query = \
'select entity_type from "SimpleEntities" where id=%s' % entity_id
data = DBSession.connection().execute(sql_query).fetchone()
entity_type = data[0] if data else None
delete_user_action ='/entities/%(id)s/%(entity_id)s/remove/dialog'
logger.debug('entity_id : %s' % entity_id)
logger.debug('entity_type: %s' % entity_type)
if entity_id and entity_type not in ['Project', 'Department', 'Group', 'Task', 'User']:
# there is no entity_type for that entity
return []
start = time.time()
sql_query = """select
"Users".id,
"SimpleEntities".name,
"Users".login,
"Users".email,
user_departments."dep_ids",
user_departments."dep_names",
user_groups."group_ids",
user_groups."group_names",
tasks.task_count,
tickets.ticket_count,
"Links".full_path
from "SimpleEntities"
join "Users" on "SimpleEntities".id = "Users".id
left outer join (
select
uid,
array_agg(did) as dep_ids,
array_agg(name) as dep_names
from "User_Departments"
join "SimpleEntities" on "User_Departments".did = "SimpleEntities".id
group by uid
) as user_departments on user_departments.uid = "Users".id
left outer join (
select
uid,
array_agg(gid) as group_ids,
array_agg(name) as group_names
from "User_Groups"
join "SimpleEntities" on "User_Groups".gid = "SimpleEntities".id
group by uid
) as user_groups on user_groups.uid = "Users".id
left outer join (
select resource_id, count(task_id) as task_count from "Task_Resources" group by resource_id
) as tasks on tasks.resource_id = "Users".id
left outer join (
select
owner_id,
count("Tickets".id) as ticket_count
from "Tickets"
join "SimpleEntities" on "Tickets".status_id = "SimpleEntities".id
where "SimpleEntities".name = 'New'
group by owner_id, name
) as tickets on tickets.owner_id = "Users".id
left outer join "Links" on "SimpleEntities".thumbnail_id = "Links".id
"""
if entity_type == "Project":
sql_query += """join "Project_Users" on "Users".id = "Project_Users".user_id
where "Project_Users".project_id = %(id)s
""" % {'id': entity_id}
elif entity_type == "Department":
sql_query += """join "User_Departments" on "Users".id = "User_Departments".uid
where "User_Departments".did = %(id)s
""" % {'id': entity_id}
elif entity_type == "Group":
sql_query += """join "User_Groups" on "Users".id = "User_Groups".uid
where "User_Groups".gid = %(id)s
""" % {'id': entity_id}
elif entity_type == "Task":
sql_query += """join "Task_Resources" on "Users".id = "Task_Resources".resource_id
where "Task_Resources".task_id = %(id)s
""" % {'id': entity_id}
elif entity_type == "User":
sql_query += 'where "Users".id = %s' % entity_id
sql_query += 'order by "SimpleEntities".name'
result = DBSession.connection().execute(sql_query)
data = [
{
'id': r[0],
'name': r[1],
'login': r[2],
'email': r[3],
'departments': [
{
'id': r[4][i],
'name': r[5][i]
} for i, a in enumerate(r[4])
] if r[4] else [],
'groups': [
{
'id': r[6],
'name': r[7]
} for i in range(len(r[6]))
] if r[6] else [],
'tasksCount': r[8] or 0,
'ticketsCount': r[9] or 0,
'thumbnail_full_path': r[10] if r[10] else None,
'update_user_action':'/users/%s/update/dialog' % r[0] if update_user_permission else None,
'delete_user_action':delete_user_action % {'id':r[0],'entity_id':entity_id} if delete_user_permission else None
} for r in result.fetchall()
]
end = time.time()
logger.debug('get_users took : %s seconds for %s rows' %
((end - start), len(data)))
return data
def get_reviews(request, where_conditions):
"""TODO: add docstring
"""
logger.debug('get_reviews is running')
logged_in_user = get_logged_in_user(request)
sql_query = """
select
"Reviews".review_number as review_number,
"Reviews".id as review_id,
"Reviews_Statuses".code as review_status_code,
"Statuses_Simple_Entities".name as review_status_name,
"Statuses_Simple_Entities".html_class as review_status_color,
"Reviews".task_id as task_id,
"ParentTasks".parent_names as task_name,
"Review_Tasks".review_number as task_review_number,
"Reviews".reviewer_id as reviewer_id,
"Reviewers_SimpleEntities".name as reviewer_name,
"Reviewers_SimpleEntities_Links".full_path as reviewer_thumbnail_path,
array_agg("Reviewer_Departments_SimpleEntities".name) as reviewer_departments,
extract(epoch from"Reviews_Simple_Entities".date_created::timestamp AT TIME ZONE 'UTC') * 1000 as date_created
from "Reviews"
join "SimpleEntities" as "Reviews_Simple_Entities" on "Reviews_Simple_Entities".id = "Reviews".id
join "Tasks" as "Review_Tasks" on "Review_Tasks".id = "Reviews".task_id
join "Statuses" as "Reviews_Statuses" on "Reviews_Statuses".id = "Reviews".status_id
join "SimpleEntities" as "Statuses_Simple_Entities" on "Statuses_Simple_Entities".id = "Reviews".status_id
join "SimpleEntities" as "Reviewers_SimpleEntities" on "Reviewers_SimpleEntities".id = "Reviews".reviewer_id
join "User_Departments" as "Reviewers_Departments" on "Reviewers_Departments".uid = "Reviews".reviewer_id
join "SimpleEntities" as "Reviewer_Departments_SimpleEntities" on "Reviewer_Departments_SimpleEntities".id = "Reviewers_Departments".did
left join (%(tasks_hierarchical_name_table)s) as "ParentTasks" on "Review_Tasks".id = "ParentTasks".id
left outer join "Links" as "Reviewers_SimpleEntities_Links" on "Reviewers_SimpleEntities_Links".id = "Reviewers_SimpleEntities".thumbnail_id
%(where_conditions)s
group by
"Reviews".review_number,
"Reviews".id,
"Reviews_Statuses".code,
"Reviews_Simple_Entities".date_created,
"Statuses_Simple_Entities".name,
"Statuses_Simple_Entities".html_class,
"Reviews".task_id,
"ParentTasks".parent_names,
"Review_Tasks".review_number,
"Reviews".reviewer_id,
"Reviewers_SimpleEntities".name,
"Reviewers_SimpleEntities_Links".full_path
order by "Reviews_Simple_Entities".date_created desc
"""
logger.debug('where_conditions: %s ' % where_conditions)
sql_query = sql_query % {'where_conditions': where_conditions, 'tasks_hierarchical_name_table': query_of_tasks_hierarchical_name_table()}
result = DBSession.connection().execute(sql_query)
return_data = [
{
'review_number': r[0],
'review_id': r[1],
'review_status_code': r[2],
'review_status_name': r[3],
'review_status_color': r[4],
'task_id': r[5],
'task_name': r[6],
'task_review_number': r[7],
'reviewer_id': r[8],
'reviewer_name': r[9],
'reviewer_thumbnail_full_path':r[10],
'reviewer_department':r[11],
'date_created':r[12],
'is_reviewer':'1' if logged_in_user.id == r[8] else None
}
for r in result.fetchall()
]
return return_data
def get_resources(request):
"""returns Users for Resource View
"""
# TODO: This is a very ugly function, please define the borders and use cases correctly and then clean it
start = time.time()
# return users for now
# /resources/
# /resources/26/
resource_id = request.matchdict.get('id')
logger.debug('resource_id: %s' % resource_id)
parent_id = request.params.get('parent_id')
logger.debug('parent_id: %s' % parent_id)
execute = DBSession.connection().execute
entity_type = None
if resource_id:
# get the entity type of that resource
data = execute('select entity_type from "SimpleEntities" where id=%s' %
resource_id).fetchone()
if data:
entity_type = data[0]
else:
return []
else:
# default to User
entity_type = "User"
logger.debug('entity_type : %s' % entity_type)
# get resource details plus time logs
if not parent_id:
if entity_type == 'Department':
resource_sql_query = """select
"SimpleEntities".id,
"SimpleEntities".name,
"SimpleEntities".entity_type,
count(*) as resource_count
from "SimpleEntities"
join "User_Departments" on "User_Departments".did = "SimpleEntities".id
where "SimpleEntities".entity_type = '%s'
""" % entity_type
elif entity_type == 'User':
resource_sql_query = """select
"SimpleEntities".id,
"SimpleEntities".name,
"SimpleEntities".entity_type,
1 as resource_count
from "SimpleEntities"
where "SimpleEntities".entity_type = '%s'
""" % entity_type
elif entity_type in ['Studio', 'Project']:
resource_sql_query = """select
"SimpleEntities".id,
"SimpleEntities".name,
"SimpleEntities".entity_type,
count(*) as resource_count
from "SimpleEntities"
join "User_Departments" on "User_Departments".did = "SimpleEntities".id
"""
if resource_id and entity_type not in ["Studio", "Project"]:
resource_sql_query += "and id=%s group by id, name, entity_type order by name" % resource_id
else:
resource_sql_query += "group by id, name, entity_type order by name"
# if the given entity is a Department return all the time logs of the
# users of that department
time_log_query = """select
"TimeLogs".id,
"TimeLogs".task_id,
extract(epoch from "TimeLogs".start::timestamp AT TIME ZONE 'UTC') * 1000 as start,
extract(epoch from "TimeLogs".end::timestamp AT TIME ZONE 'UTC') * 1000 as end
from "TimeLogs"
"""
tasks_query = """select
"Tasks".id,
extract(epoch from "Tasks".computed_start::timestamp AT TIME ZONE 'UTC') * 1000 as start,
extract(epoch from "Tasks".computed_end::timestamp AT TIME ZONE 'UTC') * 1000 as end
from "Tasks"
"""
has_children = False
if entity_type == "User":
time_log_query += "where resource_id = %s"
tasks_query += """join "Task_Resources" on "Tasks".id = "Task_Resources".task_id
where not (
exists (
select 1
from (
select "Tasks".parent_id
from "SimpleEntities"
join "Tasks" on "SimpleEntities".id = "Tasks".id
) AS all_tasks
where all_tasks.parent_id = "Tasks".id
)
) and resource_id = %s
"""
has_children = False
elif entity_type in ["Department", "Studio"]:
time_log_query += """
join "User_Departments" on "User_Departments".uid = "TimeLogs".resource_id
where did = %s"""
tasks_query += """join "Task_Resources" on "Tasks".id = "Task_Resources".task_id
join "User_Departments" on "Task_Resources".resource_id = "User_Departments".uid
where not (
exists (
select 1
from (
select "Tasks".parent_id
from "SimpleEntities"
join "Tasks" on "SimpleEntities".id = "Tasks".id
) AS all_tasks
where all_tasks.parent_id = "Tasks".id
)
)
and did = %s
group by "Tasks".id, "Tasks".start, "Tasks".end, "Tasks".computed_start, "Tasks".computed_end
order by start
"""
has_children = True
elif entity_type == "Project":
# the resource is a Project return all the project tasks and
# return all the time logs of the users in that project
time_log_query += """
join "User_Departments" on "User_Departments".uid = "TimeLogs".resource_id
-- where did = %s
"""
tasks_query += """
-- select all the leaf tasks of the users of a specific Project
select
"Tasks".id,
extract(epoch from "Tasks".computed_start::timestamp AT TIME ZONE 'UTC') * 1000 as start,
extract(epoch from "Tasks".computed_end::timestamp AT TIME ZONE 'UTC') * 1000 as end
from "Tasks"
where not (
exists (
select 1
from (
select "Tasks".parent_id
from "SimpleEntities"
join "Tasks" on "SimpleEntities".id = "Tasks".id
) AS all_tasks
where all_tasks.parent_id = "Tasks".id
)
) and project_id = %s
group by id, start, "end", "Tasks".computed_start, "Tasks".computed_end
order by start
"""
has_children = True
else:
# return departments ??? should also return Groups, Project etc.
# that contains users
resource_sql_query = """select
"Users".id,
"SimpleEntities".name,
"SimpleEntities".entity_type,
1 as resource_count
from "Users"
join "SimpleEntities" on "SimpleEntities".id = "Users".id
join "User_Departments" on "User_Departments".uid = "Users".id
join "Departments" on "User_Departments".did = "Departments".id
where "Departments".id = %s
order by name
""" % parent_id
time_log_query = """select
"TimeLogs".id,
"TimeLogs".task_id,
extract(epoch from "TimeLogs".start::timestamp AT TIME ZONE 'UTC') * 1000 as start,
extract(epoch from "TimeLogs".end::timestamp AT TIME ZONE 'UTC') * 1000 as end
from "TimeLogs"
where resource_id = %s
"""
tasks_query = """select
"Tasks".id,
extract(epoch from "Tasks".computed_start::timestamp AT TIME ZONE 'UTC') * 1000 as start,
extract(epoch from "Tasks".computed_end::timestamp AT TIME ZONE 'UTC') * 1000 as end
from "Tasks"
join "Task_Resources" on "Tasks".id = "Task_Resources".task_id
where
not (
exists (
select 1
from (
select "Tasks".parent_id
from "SimpleEntities"
join "Tasks" on "SimpleEntities".id = "Tasks".id
) AS all_tasks
where all_tasks.parent_id = "Tasks".id
)
) and resource_id = %s
"""
has_children = False
logger.debug('resource_sql_query : %s' % resource_sql_query)
logger.debug('time_log_query : %s' % time_log_query)
logger.debug('tasks_sql_query : %s' % tasks_query)
resources_result = execute(resource_sql_query).fetchall()
logger.debug('resources_result : %s' % resources_result)
link = '/%s/%s/view' % (entity_type.lower(), '%s')
data = [
{
'id': rr[0],
'name': rr[1],
'type': rr[2],
'resource_count': rr[3],
'hasChildren': has_children,
'link': link % rr[0],
'time_logs': [
{
'id': tr[0],
'task_id': tr[1],
'start': tr[2],
'end': tr[3]
} for tr in execute(time_log_query % rr[0]).fetchall()
],
'tasks': [
{
'id': tr[0],
'start': tr[1],
'end': tr[2]
} for tr in execute(tasks_query % rr[0]).fetchall()
]
} for rr in resources_result
]
end = time.time()
logger.debug('get_resources took : %s seconds' % (end - start))
data_count = len(data)
content_range = '%s-%s/%s' % (0, data_count - 1, data_count)
resp = Response(
json_body=data
)
resp.content_range = content_range
return resp
def get_shots(request):
"""returns all the Shots of the given Project
"""
entity_id = request.matchdict.get("id", -1)
entity = Entity.query.filter_by(id=entity_id).first()
shot_id = request.params.get("entity_id", None)
logger.debug("get_shots function starts : ")
sql_query = """select
"Shots".id as shot_id,
"Shot_SimpleEntities".name as shot_name,
"Shot_SimpleEntities".description as shot_description,
"Links".full_path as shot_full_path,
"Distinct_Shot_Statuses".shot_status_code as shot_status_code,
"Distinct_Shot_Statuses".shot_status_html_class as shot_status_html_class,
array_agg("Distinct_Shot_Task_Types".type_name) as type_name,
array_agg("Tasks".id) as task_id,
array_agg("Task_SimpleEntities".name) as task_name,
array_agg("Task_Statuses".code) as status_code,
array_agg("Task_Statuses_SimpleEntities".html_class) as status_html_class,
array_agg(coalesce(
-- for parent tasks
(case "Tasks".schedule_seconds
when 0 then 0
else "Tasks".total_logged_seconds::float / "Tasks".schedule_seconds * 100
end
),
-- for child tasks we need to count the total seconds of related TimeLogs
(coalesce("Task_TimeLogs".duration, 0.0))::float /
("Tasks".schedule_timing * (case "Tasks".schedule_unit
when 'min' then 60
when 'h' then 3600
when 'd' then 32400
when 'w' then 147600
when 'm' then 590400
when 'y' then 7696277
else 0
end)) * 100.0
)) as percent_complete,
"Shot_Sequences".sequence_id as sequence_id,
"Shot_Sequences_SimpleEntities".name as sequence_name
from "Tasks"
join "Shots" on "Shots".id = "Tasks".parent_id
join "SimpleEntities" as "Shot_SimpleEntities" on "Shots".id = "Shot_SimpleEntities".id
join "SimpleEntities" as "Task_SimpleEntities" on "Tasks".id = "Task_SimpleEntities".id
left join "Links" on "Shot_SimpleEntities".thumbnail_id = "Links".id
join(
select
"Shots".id as shot_id,
"Statuses".code as shot_status_code,
"SimpleEntities".html_class as shot_status_html_class
from "Tasks"
join "Shots" on "Shots".id = "Tasks".id
join "Statuses" on "Statuses".id = "Tasks".status_id
join "SimpleEntities" on "SimpleEntities".id = "Statuses".id
)as "Distinct_Shot_Statuses" on "Shots".id = "Distinct_Shot_Statuses".shot_id
left join (
select
"SimpleEntities".id as type_id,
"SimpleEntities".name as type_name
from "SimpleEntities"
join "SimpleEntities" as "Task_SimpleEntities" on "SimpleEntities".id = "Task_SimpleEntities".type_id
join "Tasks" on "Task_SimpleEntities".id = "Tasks".id
join "Shots" on "Tasks".parent_id = "Shots".id
group by "SimpleEntities".id, "SimpleEntities".name
order by "SimpleEntities".id
) as "Distinct_Shot_Task_Types" on "Task_SimpleEntities".type_id = "Distinct_Shot_Task_Types".type_id
join "Statuses" as "Task_Statuses" on "Tasks".status_id = "Task_Statuses".id
join "SimpleEntities" as "Task_Statuses_SimpleEntities" on "Task_Statuses_SimpleEntities".id = "Tasks".status_id
left join "Shot_Sequences" on "Shot_Sequences".shot_id = "Shots".id
left join "SimpleEntities" as "Shot_Sequences_SimpleEntities" on "Shot_Sequences_SimpleEntities".id = "Shot_Sequences".sequence_id
left outer join (
select
"TimeLogs".task_id,
extract(epoch from sum("TimeLogs".end::timestamp AT TIME ZONE 'UTC' - "TimeLogs".start::timestamp AT TIME ZONE 'UTC')) as duration
from "TimeLogs"
group by task_id
) as "Task_TimeLogs" on "Task_TimeLogs".task_id = "Tasks".id
%(where_condition)s
group by
"Shots".id,
"Shot_SimpleEntities".name,
"Shot_SimpleEntities".description,
"Links".full_path,
"Distinct_Shot_Statuses".shot_status_code,
"Distinct_Shot_Statuses".shot_status_html_class,
"Shot_Sequences".sequence_id,
"Shot_Sequences_SimpleEntities".name
order by "Shot_SimpleEntities".name
"""
# set the content range to prevent JSONRest Store to query the data twice
content_range = "%s-%s/%s"
where_condition = ""
if entity.entity_type == "Sequence":
where_condition = 'where "Shot_Sequences".sequence_id = %s' % entity_id
elif entity.entity_type == "Project":
where_condition = ""
if shot_id:
where_condition = 'where "Shots".id = %(shot_id)s' % ({"shot_id": shot_id})
update_shot_permission = PermissionChecker(request)("Update_Shot")
delete_shot_permission = PermissionChecker(request)("Delete_Shot")
sql_query = sql_query % {"where_condition": where_condition}
logger.debug("entity_id : %s" % entity_id)
# convert to dgrid format right here in place
result = DBSession.connection().execute(sql_query)
return_data = []
for r in result.fetchall():
r_data = {
"id": r[0],
"name": r[1],
"description": r[2],
"thumbnail_full_path": r[3] if r[3] else None,
"status": r[4],
"status_color": r[5],
"sequence_id": r[12],
"sequence_name": r[13],
"update_shot_action": "/tasks/%s/update/dialog" % r[0] if update_shot_permission else None,
"delete_shot_action": "/tasks/%s/delete/dialog" % r[0] if delete_shot_permission else None,
}
task_types_names = r[6]
task_ids = r[7]
task_names = r[8]
task_statuses = r[9]
task_statuses_color = r[10]
task_percent_complete = r[11]
logger.debug("task_types_names %s " % task_types_names)
r_data["nulls"] = []
for index1 in range(len(task_types_names)):
if task_types_names[index1]:
r_data[task_types_names[index1]] = []
for index in range(len(task_types_names)):
logger.debug("task_types_names[index]; %s " % task_types_names[index])
if task_types_names[index]:
r_data[task_types_names[index]].append(
[task_ids[index], task_names[index], task_statuses[index], task_percent_complete[index]]
)
else:
r_data["nulls"].append(
[task_ids[index], task_names[index], task_statuses[index], task_percent_complete[index]]
)
return_data.append(r_data)
shot_count = len(return_data)
content_range = content_range % (0, shot_count - 1, shot_count)
resp = Response(json_body=return_data)
resp.content_range = content_range
return resp
def get_tickets(request):
"""returns all the tickets related to an entity or not
"""
entity_id = request.matchdict.get('id')
#entity = Entity.query.filter_by(id=entity_id).first()
entity_type = None
if entity_id:
# get the entity type
sql_query = \
'select entity_type from "SimpleEntities" where id=%s' % entity_id
data = DBSession.connection().execute(sql_query).fetchone()
entity_type = data[0] if data else None
logger.debug('entity_id : %s' % entity_id)
logger.debug('entity_type: %s' % entity_type)
sql_query = """select
"SimpleEntities_Ticket".id,
"SimpleEntities_Ticket".name,
"Tickets".number,
"Tickets".summary,
"Tickets".project_id,
"SimpleEntities_Project".name as project_name,
"Tickets".owner_id as owner_id,
"SimpleEntities_Owner".name as owner_name,
"SimpleEntities_Ticket".date_created,
"SimpleEntities_Ticket".date_updated,
"SimpleEntities_Ticket".created_by_id,
"SimpleEntities_CreatedBy".name as created_by_name,
"SimpleEntities_Ticket".updated_by_id,
"SimpleEntities_UpdatedBy".name as updated_by_name,
"SimpleEntities_Status".name as status_name,
"Tickets".priority,
"SimpleEntities_Type".name as type_name
from "Tickets"
join "SimpleEntities" as "SimpleEntities_Ticket" on "Tickets".id = "SimpleEntities_Ticket".id
join "SimpleEntities" as "SimpleEntities_Project" on "Tickets".project_id = "SimpleEntities_Project".id
left outer join "SimpleEntities" as "SimpleEntities_Owner" on "Tickets".owner_id = "SimpleEntities_Owner".id
left outer join "SimpleEntities" as "SimpleEntities_CreatedBy" on "SimpleEntities_Ticket".created_by_id = "SimpleEntities_CreatedBy".id
left outer join "SimpleEntities" as "SimpleEntities_UpdatedBy" on "SimpleEntities_Ticket".updated_by_id = "SimpleEntities_UpdatedBy".id
join "SimpleEntities" as "SimpleEntities_Status" on "Tickets".status_id = "SimpleEntities_Status".id
left outer join "SimpleEntities" as "SimpleEntities_Type" on "SimpleEntities_Ticket".type_id = "SimpleEntities_Type".id
"""
if entity_type:
if entity_type == u"Project":
sql_query += """where "Tickets".project_id = %s""" % entity_id
elif entity_type == u"User":
sql_query += """where "Tickets".owner_id = %s""" % entity_id
else:
sql_query += \
"""join "Ticket_SimpleEntities" on
"Tickets".id = "Ticket_SimpleEntities".ticket_id
where "Ticket_SimpleEntities".simple_entity_id = %s
""" % entity_id
sql_query += 'order by "Tickets".number'
start = time.time()
result = DBSession.connection().execute(sql_query)
data = [
{
'id': r[0],
'name': r[1],
'number': r[2],
'summary': r[3],
'project_id': r[4],
'project_name': r[5],
'owner_id': r[6],
'owner_name': r[7],
'date_created': milliseconds_since_epoch(r[8]),
'date_updated': milliseconds_since_epoch(r[9]),
'created_by_id': r[10],
'created_by_name': r[11],
'updated_by_id': r[12],
'updated_by_name': r[13],
'status': r[14],
'priority': r[15],
'type': r[16]
} for r in result.fetchall()
]
end = time.time()
logger.debug('get_entity_tickets took : %s seconds for %s rows' % (
end - start, len(data)))
return data
