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