本文整理汇总了Python中stalker.db.DBSession.connection方法的典型用法代码示例。如果您正苦于以下问题:Python DBSession.connection方法的具体用法?Python DBSession.connection怎么用?Python DBSession.connection使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类stalker.db.DBSession
的用法示例。
在下文中一共展示了DBSession.connection方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: get_shots_children_task_type
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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
示例2: get_assets_type_task_types
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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
示例3: get_entity_notes
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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
示例4: get_project_sequences_count
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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]
示例5: get_shots_count
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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]
示例6: get_entity_references_count
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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]
示例7: get_task_reviewers
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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
示例8: get_assets_types
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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
示例9: get_project_tasks_today
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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]))
#.........这里部分代码省略.........
示例10: get_entity_references
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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
示例11: get_assets
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
#.........这里部分代码省略.........
"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]]= []
示例12: get_users_count
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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]
示例13: get_time_logs
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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
示例14: get_users
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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'
#.........这里部分代码省略.........
示例15: get_reviews
# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
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